-- Esquema de base de datos para WebTrader
-- Incluye todas las tablas necesarias para trading completo

-- Tabla de tipos de cuenta configurables
CREATE TABLE IF NOT EXISTS account_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    display_name VARCHAR(100) NOT NULL,
    description TEXT,
    min_deposit DECIMAL(15,2) DEFAULT 0.00,
    max_leverage INT DEFAULT 100,
    commission_rate DECIMAL(5,4) DEFAULT 0.0000,
    swap_rate DECIMAL(5,4) DEFAULT 0.0000,
    margin_call_level INT DEFAULT 100,
    stop_out_level INT DEFAULT 50,
    max_positions INT DEFAULT 100,
    allowed_instruments TEXT, -- JSON array de instrumentos permitidos
    features JSON, -- Características especiales (copy_trading, etc.)
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insertar tipos de cuenta por defecto
INSERT INTO account_types (name, display_name, description, min_deposit, max_leverage, commission_rate, margin_call_level, stop_out_level, allowed_instruments, features) VALUES
('demo', 'Cuenta Demo', 'Cuenta de práctica con dinero virtual', 0.00, 500, 0.0000, 100, 50, '["forex", "cfds", "crypto", "indices", "commodities"]', '{"unlimited_balance": true, "reset_balance": true}'),
('standard', 'Cuenta Estándar', 'Cuenta real para traders principiantes', 100.00, 100, 0.0010, 100, 50, '["forex", "cfds", "indices"]', '{"demo_available": true}'),
('premium', 'Cuenta Premium', 'Cuenta con mejores condiciones de trading', 1000.00, 200, 0.0008, 80, 30, '["forex", "cfds", "crypto", "indices", "commodities"]', '{"priority_support": true, "advanced_charts": true}'),
('vip', 'Cuenta VIP', 'Cuenta para traders profesionales', 10000.00, 500, 0.0005, 50, 20, '["forex", "cfds", "crypto", "indices", "commodities", "stocks"]', '{"dedicated_manager": true, "api_access": true, "copy_trading": true}');

-- Tabla de cuentas de trading
CREATE TABLE IF NOT EXISTS trading_accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    account_type VARCHAR(50) NOT NULL,
    account_number VARCHAR(50) UNIQUE NOT NULL,
    balance DECIMAL(15,2) DEFAULT 0.00,
    equity DECIMAL(15,2) DEFAULT 0.00,
    margin DECIMAL(15,2) DEFAULT 0.00,
    free_margin DECIMAL(15,2) DEFAULT 0.00,
    margin_level DECIMAL(8,2) DEFAULT 0.00,
    leverage INT DEFAULT 100,
    currency VARCHAR(3) DEFAULT 'USD',
    server VARCHAR(50) DEFAULT 'main',
    status ENUM('active', 'inactive', 'suspended', 'closed') DEFAULT 'active',
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (account_type) REFERENCES account_types(name) ON UPDATE CASCADE
);

-- Tabla de instrumentos financieros
CREATE TABLE IF NOT EXISTS trading_instruments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    symbol VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    category ENUM('forex', 'cfds', 'crypto', 'indices', 'commodities', 'stocks') NOT NULL,
    base_currency VARCHAR(3),
    quote_currency VARCHAR(3),
    pip_size DECIMAL(10,8) DEFAULT 0.0001,
    pip_value DECIMAL(10,4) DEFAULT 1.0000,
    min_volume DECIMAL(10,2) DEFAULT 0.01,
    max_volume DECIMAL(10,2) DEFAULT 100.00,
    volume_step DECIMAL(10,2) DEFAULT 0.01,
    contract_size INT DEFAULT 100000,
    margin_rate DECIMAL(8,4) DEFAULT 0.0100, -- 1% margin
    commission DECIMAL(8,4) DEFAULT 0.0000,
    swap_long DECIMAL(8,4) DEFAULT 0.0000,
    swap_short DECIMAL(8,4) DEFAULT 0.0000,
    spread_type ENUM('fixed', 'variable') DEFAULT 'variable',
    spread_value DECIMAL(8,4) DEFAULT 0.0000,
    is_active BOOLEAN DEFAULT TRUE,
    trading_hours JSON, -- Horarios de trading
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insertar instrumentos por defecto
INSERT INTO trading_instruments (symbol, name, category, base_currency, quote_currency, pip_size, contract_size, margin_rate, spread_value) VALUES
-- Forex Major Pairs
('EURUSD', 'Euro vs US Dollar', 'forex', 'EUR', 'USD', 0.0001, 100000, 0.0333, 0.0001),
('GBPUSD', 'British Pound vs US Dollar', 'forex', 'GBP', 'USD', 0.0001, 100000, 0.0333, 0.0002),
('USDJPY', 'US Dollar vs Japanese Yen', 'forex', 'USD', 'JPY', 0.01, 100000, 0.0333, 0.01),
('USDCHF', 'US Dollar vs Swiss Franc', 'forex', 'USD', 'CHF', 0.0001, 100000, 0.0333, 0.0002),
('AUDUSD', 'Australian Dollar vs US Dollar', 'forex', 'AUD', 'USD', 0.0001, 100000, 0.0333, 0.0002),
('USDCAD', 'US Dollar vs Canadian Dollar', 'forex', 'USD', 'CAD', 0.0001, 100000, 0.0333, 0.0002),
-- Crypto
('BTCUSD', 'Bitcoin vs US Dollar', 'crypto', 'BTC', 'USD', 0.01, 1, 0.0500, 0.50),
('ETHUSD', 'Ethereum vs US Dollar', 'crypto', 'ETH', 'USD', 0.01, 1, 0.0500, 0.30),
-- Indices
('US30', 'Dow Jones Industrial Average', 'indices', NULL, 'USD', 0.01, 1, 0.0100, 0.50),
('SPX500', 'S&P 500 Index', 'indices', NULL, 'USD', 0.01, 1, 0.0100, 0.40),
('NAS100', 'NASDAQ 100 Index', 'indices', NULL, 'USD', 0.01, 1, 0.0100, 0.60);

-- Tabla de precios en tiempo real
CREATE TABLE IF NOT EXISTS market_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    symbol VARCHAR(20) NOT NULL,
    bid DECIMAL(12,6) NOT NULL,
    ask DECIMAL(12,6) NOT NULL,
    last DECIMAL(12,6),
    volume DECIMAL(15,2) DEFAULT 0.00,
    high DECIMAL(12,6),
    low DECIMAL(12,6),
    open DECIMAL(12,6),
    close DECIMAL(12,6),
    change_value DECIMAL(12,6) DEFAULT 0.000000,
    change_percent DECIMAL(8,4) DEFAULT 0.0000,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (symbol) REFERENCES trading_instruments(symbol) ON UPDATE CASCADE,
    INDEX idx_symbol_timestamp (symbol, timestamp)
);

-- Tabla de órdenes de trading
CREATE TABLE IF NOT EXISTS trading_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL,
    symbol VARCHAR(20) NOT NULL,
    type ENUM('market', 'limit', 'stop', 'stop_limit', 'trailing_stop') NOT NULL,
    direction ENUM('buy', 'sell') NOT NULL,
    volume DECIMAL(10,2) NOT NULL,
    price DECIMAL(12,6), -- NULL para market orders
    stop_loss DECIMAL(12,6),
    take_profit DECIMAL(12,6),
    trailing_amount DECIMAL(12,6), -- Para trailing stops
    status ENUM('pending', 'filled', 'cancelled', 'rejected', 'expired') DEFAULT 'pending',
    fill_price DECIMAL(12,6),
    fill_time TIMESTAMP NULL,
    commission DECIMAL(10,4) DEFAULT 0.0000,
    swap DECIMAL(10,4) DEFAULT 0.0000,
    comment TEXT,
    expiry_time TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES trading_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (symbol) REFERENCES trading_instruments(symbol) ON UPDATE CASCADE,
    INDEX idx_account_status (account_id, status),
    INDEX idx_symbol_status (symbol, status)
);

-- Tabla de posiciones abiertas
CREATE TABLE IF NOT EXISTS trading_positions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL,
    order_id INT, -- Orden que abrió la posición
    symbol VARCHAR(20) NOT NULL,
    type ENUM('buy', 'sell') NOT NULL,
    volume DECIMAL(10,2) NOT NULL,
    open_price DECIMAL(12,6) NOT NULL,
    current_price DECIMAL(12,6),
    stop_loss DECIMAL(12,6),
    take_profit DECIMAL(12,6),
    profit DECIMAL(15,2) DEFAULT 0.00,
    commission DECIMAL(10,4) DEFAULT 0.0000,
    swap DECIMAL(10,4) DEFAULT 0.0000,
    margin DECIMAL(15,2) DEFAULT 0.00,
    status ENUM('open', 'closed') DEFAULT 'open',
    close_price DECIMAL(12,6),
    close_time TIMESTAMP NULL,
    comment TEXT,
    opened_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES trading_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES trading_orders(id) ON DELETE SET NULL,
    FOREIGN KEY (symbol) REFERENCES trading_instruments(symbol) ON UPDATE CASCADE,
    INDEX idx_account_status (account_id, status),
    INDEX idx_symbol_status (symbol, status)
);

-- Tabla de historial de transacciones
CREATE TABLE IF NOT EXISTS trading_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL,
    position_id INT,
    order_id INT,
    symbol VARCHAR(20) NOT NULL,
    type ENUM('buy', 'sell') NOT NULL,
    volume DECIMAL(10,2) NOT NULL,
    open_price DECIMAL(12,6) NOT NULL,
    close_price DECIMAL(12,6) NOT NULL,
    profit DECIMAL(15,2) NOT NULL,
    commission DECIMAL(10,4) DEFAULT 0.0000,
    swap DECIMAL(10,4) DEFAULT 0.0000,
    duration INT, -- Duración en segundos
    comment TEXT,
    opened_at TIMESTAMP NOT NULL,
    closed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES trading_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (position_id) REFERENCES trading_positions(id) ON DELETE SET NULL,
    FOREIGN KEY (order_id) REFERENCES trading_orders(id) ON DELETE SET NULL,
    FOREIGN KEY (symbol) REFERENCES trading_instruments(symbol) ON UPDATE CASCADE,
    INDEX idx_account_date (account_id, closed_at),
    INDEX idx_symbol_date (symbol, closed_at)
);

-- Tabla de alertas de precio
CREATE TABLE IF NOT EXISTS price_alerts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    symbol VARCHAR(20) NOT NULL,
    condition_type ENUM('above', 'below', 'cross_up', 'cross_down') NOT NULL,
    target_price DECIMAL(12,6) NOT NULL,
    current_price DECIMAL(12,6),
    message TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    triggered_at TIMESTAMP NULL,
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (symbol) REFERENCES trading_instruments(symbol) ON UPDATE CASCADE,
    INDEX idx_user_active (user_id, is_active),
    INDEX idx_symbol_active (symbol, is_active)
);

-- Tabla de margin calls
CREATE TABLE IF NOT EXISTS margin_calls (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL,
    margin_level DECIMAL(8,2) NOT NULL,
    equity DECIMAL(15,2) NOT NULL,
    margin DECIMAL(15,2) NOT NULL,
    free_margin DECIMAL(15,2) NOT NULL,
    status ENUM('warning', 'margin_call', 'stop_out', 'resolved') NOT NULL,
    message TEXT,
    resolved_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES trading_accounts(id) ON DELETE CASCADE,
    INDEX idx_account_status (account_id, status),
    INDEX idx_created_at (created_at)
);

-- Tabla de configuración del webtrader
CREATE TABLE IF NOT EXISTS webtrader_config (
    id INT PRIMARY KEY AUTO_INCREMENT,
    key_name VARCHAR(100) NOT NULL UNIQUE,
    value TEXT,
    description TEXT,
    type ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
    is_public BOOLEAN DEFAULT FALSE, -- Si es visible para usuarios
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insertar configuración por defecto
INSERT INTO webtrader_config (key_name, value, description, type, is_public) VALUES
('finage_api_key', '', 'API Key de Finage para datos en tiempo real', 'string', FALSE),
('finage_base_url', 'https://api.finage.co.uk', 'URL base de la API de Finage', 'string', FALSE),
('default_leverage', '100', 'Apalancamiento por defecto para nuevas cuentas', 'number', TRUE),
('max_leverage', '500', 'Apalancamiento máximo permitido', 'number', TRUE),
('margin_call_level', '100', 'Nivel de margin call por defecto (%)', 'number', TRUE),
('stop_out_level', '50', 'Nivel de stop out por defecto (%)', 'number', TRUE),
('trading_enabled', 'true', 'Si el trading está habilitado globalmente', 'boolean', TRUE),
('demo_balance', '10000', 'Balance inicial para cuentas demo', 'number', TRUE),
('commission_rate', '0.001', 'Comisión por defecto (0.1%)', 'number', TRUE),
('websocket_enabled', 'true', 'Si WebSocket está habilitado para precios en tiempo real', 'boolean', FALSE),
('allowed_symbols', '["EURUSD","GBPUSD","USDJPY","BTCUSD","ETHUSD","US30","SPX500"]', 'Símbolos permitidos por defecto', 'json', TRUE);

-- Tabla de watchlists personalizadas
CREATE TABLE IF NOT EXISTS user_watchlists (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    symbols JSON, -- Array de símbolos
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_default (user_id, is_default)
);

-- Tabla de estadísticas de trading
CREATE TABLE IF NOT EXISTS trading_stats (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    total_trades INT DEFAULT 0,
    winning_trades INT DEFAULT 0,
    losing_trades INT DEFAULT 0,
    total_profit DECIMAL(15,2) DEFAULT 0.00,
    total_loss DECIMAL(15,2) DEFAULT 0.00,
    net_profit DECIMAL(15,2) DEFAULT 0.00,
    win_rate DECIMAL(5,2) DEFAULT 0.00,
    profit_factor DECIMAL(8,4) DEFAULT 0.0000,
    max_drawdown DECIMAL(15,2) DEFAULT 0.00,
    avg_win DECIMAL(15,2) DEFAULT 0.00,
    avg_loss DECIMAL(15,2) DEFAULT 0.00,
    largest_win DECIMAL(15,2) DEFAULT 0.00,
    largest_loss DECIMAL(15,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES trading_accounts(id) ON DELETE CASCADE,
    UNIQUE KEY unique_account_period (account_id, period_start, period_end)
);

-- Triggers para actualizar equity y margin automáticamente
DELIMITER //

CREATE TRIGGER update_account_equity_after_position_update
AFTER UPDATE ON trading_positions
FOR EACH ROW
BEGIN
    DECLARE total_profit DECIMAL(15,2) DEFAULT 0.00;
    DECLARE total_margin DECIMAL(15,2) DEFAULT 0.00;
    DECLARE account_balance DECIMAL(15,2) DEFAULT 0.00;
    
    -- Calcular profit total de posiciones abiertas
    SELECT COALESCE(SUM(profit), 0) INTO total_profit
    FROM trading_positions 
    WHERE account_id = NEW.account_id AND status = 'open';
    
    -- Calcular margin total usado
    SELECT COALESCE(SUM(margin), 0) INTO total_margin
    FROM trading_positions 
    WHERE account_id = NEW.account_id AND status = 'open';
    
    -- Obtener balance actual
    SELECT balance INTO account_balance
    FROM trading_accounts 
    WHERE id = NEW.account_id;
    
    -- Actualizar cuenta
    UPDATE trading_accounts 
    SET 
        equity = account_balance + total_profit,
        margin = total_margin,
        free_margin = (account_balance + total_profit) - total_margin,
        margin_level = CASE 
            WHEN total_margin > 0 THEN ((account_balance + total_profit) / total_margin) * 100
            ELSE 0 
        END,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = NEW.account_id;
END//

DELIMITER ;

-- Crear índices adicionales para optimización
CREATE INDEX idx_market_data_symbol_time ON market_data(symbol, timestamp DESC);
CREATE INDEX idx_trading_orders_account_time ON trading_orders(account_id, created_at DESC);
CREATE INDEX idx_trading_positions_account_time ON trading_positions(account_id, opened_at DESC);
CREATE INDEX idx_trading_history_account_time ON trading_history(account_id, closed_at DESC);