-- Migración: Eventos en tiempo real para WebTrader
-- Crea una cola de eventos y triggers que insertan eventos al cambiar órdenes, posiciones y cuentas

-- Tabla de eventos para broadcasting en tiempo real
CREATE TABLE IF NOT EXISTS webtrader_events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NULL,
    account_number VARCHAR(50) NULL,
    entity_type ENUM('account','order','position','price','alert','margin_call','config') NOT NULL,
    entity_id INT NULL,
    event_type ENUM('created','updated','deleted','filled','cancelled','rejected','expired','opened','closed','margin_call','stop_out','price_tick','balance_change') NOT NULL,
    payload JSON NULL,
    processed TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP NULL,
    INDEX idx_processed_created (processed, created_at),
    INDEX idx_account_type (account_id, entity_type),
    INDEX idx_entity (entity_type, entity_id),
    FOREIGN KEY (account_id) REFERENCES trading_accounts(id) ON DELETE SET NULL
);

DELIMITER //

-- Trigger: Insert evento cuando se crea una orden
DROP TRIGGER IF EXISTS trg_order_after_insert;
CREATE TRIGGER trg_order_after_insert
AFTER INSERT ON trading_orders
FOR EACH ROW
BEGIN
    INSERT INTO webtrader_events (account_id, account_number, entity_type, entity_id, event_type, payload, created_at)
    SELECT 
        NEW.account_id,
        ta.account_number,
        'order',
        NEW.id,
        'created',
        JSON_OBJECT(
            'id', NEW.id,
            'status', NEW.status,
            'symbol', NEW.symbol,
            'type', NEW.type,
            'direction', NEW.direction,
            'volume', NEW.volume,
            'price', NEW.price,
            'stop_loss', NEW.stop_loss,
            'take_profit', NEW.take_profit
        ),
        NOW()
    FROM trading_accounts ta WHERE ta.id = NEW.account_id;
END//

-- Trigger: Insert evento cuando se actualiza una orden
DROP TRIGGER IF EXISTS trg_order_after_update;
CREATE TRIGGER trg_order_after_update
AFTER UPDATE ON trading_orders
FOR EACH ROW
BEGIN
    DECLARE v_event VARCHAR(20);
    IF NEW.status <> OLD.status THEN
        SET v_event = CASE NEW.status
            WHEN 'filled' THEN 'filled'
            WHEN 'cancelled' THEN 'cancelled'
            WHEN 'rejected' THEN 'rejected'
            WHEN 'expired' THEN 'expired'
            ELSE 'updated'
        END;
    ELSE
        SET v_event = 'updated';
    END IF;
    INSERT INTO webtrader_events (account_id, account_number, entity_type, entity_id, event_type, payload, created_at)
    SELECT 
        NEW.account_id,
        ta.account_number,
        'order',
        NEW.id,
        v_event,
        JSON_OBJECT(
            'id', NEW.id,
            'status', NEW.status,
            'symbol', NEW.symbol,
            'type', NEW.type,
            'direction', NEW.direction,
            'volume', NEW.volume,
            'price', NEW.price,
            'fill_price', NEW.fill_price,
            'fill_time', NEW.fill_time
        ),
        NOW()
    FROM trading_accounts ta WHERE ta.id = NEW.account_id;
END//

-- Trigger: Insert evento cuando se elimina una orden
DROP TRIGGER IF EXISTS trg_order_after_delete;
CREATE TRIGGER trg_order_after_delete
AFTER DELETE ON trading_orders
FOR EACH ROW
BEGIN
    INSERT INTO webtrader_events (account_id, account_number, entity_type, entity_id, event_type, payload, created_at)
    SELECT 
        OLD.account_id,
        ta.account_number,
        'order',
        OLD.id,
        'deleted',
        JSON_OBJECT('id', OLD.id, 'status', OLD.status, 'symbol', OLD.symbol),
        NOW()
    FROM trading_accounts ta WHERE ta.id = OLD.account_id;
END//

-- Trigger: Recalcular equity/margen y emitir evento al crear posición
DROP TRIGGER IF EXISTS trg_position_after_insert;
CREATE TRIGGER trg_position_after_insert
AFTER INSERT ON trading_positions
FOR EACH ROW
BEGIN
    -- Recalcular métricas de cuenta
    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;

    SELECT COALESCE(SUM(profit), 0) INTO total_profit
    FROM trading_positions WHERE account_id = NEW.account_id AND status = 'open';

    SELECT COALESCE(SUM(margin), 0) INTO total_margin
    FROM trading_positions WHERE account_id = NEW.account_id AND status = 'open';

    SELECT balance INTO account_balance FROM trading_accounts WHERE id = NEW.account_id;

    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;

    -- Emitir evento de posición abierta
    INSERT INTO webtrader_events (account_id, account_number, entity_type, entity_id, event_type, payload, created_at)
    SELECT 
        NEW.account_id,
        ta.account_number,
        'position',
        NEW.id,
        'opened',
        JSON_OBJECT(
            'id', NEW.id,
            'symbol', NEW.symbol,
            'type', NEW.type,
            'volume', NEW.volume,
            'open_price', NEW.open_price
        ),
        NOW()
    FROM trading_accounts ta WHERE ta.id = NEW.account_id;
END//

-- Trigger: Recalcular equity/margen y emitir evento al actualizar posición
DROP TRIGGER IF EXISTS trg_position_after_update;
CREATE TRIGGER trg_position_after_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;
    DECLARE v_event VARCHAR(20);

    SELECT COALESCE(SUM(profit), 0) INTO total_profit
    FROM trading_positions WHERE account_id = NEW.account_id AND status = 'open';

    SELECT COALESCE(SUM(margin), 0) INTO total_margin
    FROM trading_positions WHERE account_id = NEW.account_id AND status = 'open';

    SELECT balance INTO account_balance FROM trading_accounts WHERE id = NEW.account_id;

    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;

    SET v_event = CASE WHEN NEW.status <> OLD.status AND NEW.status = 'closed' THEN 'closed' ELSE 'updated' END;

    INSERT INTO webtrader_events (account_id, account_number, entity_type, entity_id, event_type, payload, created_at)
    SELECT 
        NEW.account_id,
        ta.account_number,
        'position',
        NEW.id,
        v_event,
        JSON_OBJECT(
            'id', NEW.id,
            'symbol', NEW.symbol,
            'type', NEW.type,
            'volume', NEW.volume,
            'open_price', NEW.open_price,
            'current_price', NEW.current_price,
            'profit', NEW.profit,
            'close_price', NEW.close_price,
            'close_time', NEW.close_time
        ),
        NOW()
    FROM trading_accounts ta WHERE ta.id = NEW.account_id;
END//

-- Trigger: Recalcular equity/margen y emitir evento al borrar posición
DROP TRIGGER IF EXISTS trg_position_after_delete;
CREATE TRIGGER trg_position_after_delete
AFTER DELETE 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;

    SELECT COALESCE(SUM(profit), 0) INTO total_profit
    FROM trading_positions WHERE account_id = OLD.account_id AND status = 'open';

    SELECT COALESCE(SUM(margin), 0) INTO total_margin
    FROM trading_positions WHERE account_id = OLD.account_id AND status = 'open';

    SELECT balance INTO account_balance FROM trading_accounts WHERE id = OLD.account_id;

    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 = OLD.account_id;

    INSERT INTO webtrader_events (account_id, account_number, entity_type, entity_id, event_type, payload, created_at)
    SELECT 
        OLD.account_id,
        ta.account_number,
        'position',
        OLD.id,
        'deleted',
        JSON_OBJECT('id', OLD.id, 'symbol', OLD.symbol, 'type', OLD.type, 'volume', OLD.volume),
        NOW()
    FROM trading_accounts ta WHERE ta.id = OLD.account_id;
END//

-- Trigger: Emitir evento al actualizar métricas de cuenta
DROP TRIGGER IF EXISTS trg_account_after_update;
CREATE TRIGGER trg_account_after_update
AFTER UPDATE ON trading_accounts
FOR EACH ROW
BEGIN
    INSERT INTO webtrader_events (account_id, account_number, entity_type, entity_id, event_type, payload, created_at)
    VALUES (
        NEW.id,
        NEW.account_number,
        'account',
        NEW.id,
        'updated',
        JSON_OBJECT(
            'balance', NEW.balance,
            'equity', NEW.equity,
            'margin', NEW.margin,
            'free_margin', NEW.free_margin,
            'margin_level', NEW.margin_level
        ),
        NOW()
    );
END//

-- Tabla: transacciones de cuenta (depósitos, retiros, ajustes, PnL realizado)
CREATE TABLE IF NOT EXISTS account_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL,
    account_number VARCHAR(50) NOT NULL,
    type ENUM('deposit','withdrawal','adjustment','realized_pnl','commission','swap') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    balance_before DECIMAL(15,2) NOT NULL,
    balance_after DECIMAL(15,2) NOT NULL,
    reference VARCHAR(100) NULL,
    metadata JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES trading_accounts(id) ON DELETE CASCADE,
    INDEX idx_account_type_date (account_id, type, created_at)
);

-- Trigger: registrar cambios de balance como transacción y evento
DROP TRIGGER IF EXISTS trg_account_balance_change;
CREATE TRIGGER trg_account_balance_change
AFTER UPDATE ON trading_accounts
FOR EACH ROW
BEGIN
    DECLARE v_amount DECIMAL(15,2);
    IF NEW.balance <> OLD.balance THEN
        SET v_amount = NEW.balance - OLD.balance;

        INSERT INTO account_transactions (
            account_id, account_number, type, amount, balance_before, balance_after, reference, metadata
        ) VALUES (
            NEW.id,
            NEW.account_number,
            CASE WHEN v_amount > 0 THEN 'adjustment' ELSE 'adjustment' END,
            v_amount,
            OLD.balance,
            NEW.balance,
            'balance_update',
            JSON_OBJECT('source','system','reason','account_update_trigger')
        );

        INSERT INTO webtrader_events (account_id, account_number, entity_type, entity_id, event_type, payload, created_at)
        VALUES (
            NEW.id,
            NEW.account_number,
            'account',
            NEW.id,
            'balance_change',
            JSON_OBJECT('delta', v_amount, 'balance', NEW.balance, 'equity', NEW.equity, 'free_margin', NEW.free_margin),
            NOW()
        );
    END IF;
END//

-- Trigger: mover posición cerrada a historial de trading
DROP TRIGGER IF EXISTS trg_position_to_history_after_update;
CREATE TRIGGER trg_position_to_history_after_update
AFTER UPDATE ON trading_positions
FOR EACH ROW
BEGIN
    IF NEW.status = 'closed' AND OLD.status <> 'closed' THEN
        INSERT INTO trading_history (
            account_id, position_id, order_id, symbol, type, volume, open_price, close_price, profit, commission, swap, duration, comment, opened_at, closed_at
        ) VALUES (
            NEW.account_id,
            NEW.id,
            NEW.order_id,
            NEW.symbol,
            NEW.type,
            NEW.volume,
            NEW.open_price,
            NEW.close_price,
            COALESCE(NEW.profit, 0.00),
            COALESCE(NEW.commission, 0.0000),
            COALESCE(NEW.swap, 0.0000),
            TIMESTAMPDIFF(SECOND, NEW.opened_at, NEW.close_time),
            NEW.comment,
            NEW.opened_at,
            NEW.close_time
        );
    END IF;
END//

-- Trigger: actualizar KPIs diarios al insertar en historial
DROP TRIGGER IF EXISTS trg_history_update_stats;
CREATE TRIGGER trg_history_update_stats
AFTER INSERT ON trading_history
FOR EACH ROW
BEGIN
    DECLARE v_total_profit DECIMAL(15,2);
    DECLARE v_total_loss DECIMAL(15,2);
    DECLARE v_win INT;
    DECLARE v_loss INT;
    DECLARE v_largest_win DECIMAL(15,2);
    DECLARE v_largest_loss DECIMAL(15,2);

    SET v_total_profit = CASE WHEN NEW.profit > 0 THEN NEW.profit ELSE 0 END;
    SET v_total_loss   = CASE WHEN NEW.profit < 0 THEN ABS(NEW.profit) ELSE 0 END;
    SET v_win = CASE WHEN NEW.profit > 0 THEN 1 ELSE 0 END;
    SET v_loss = CASE WHEN NEW.profit < 0 THEN 1 ELSE 0 END;
    SET v_largest_win = CASE WHEN NEW.profit > 0 THEN NEW.profit ELSE 0 END;
    SET v_largest_loss = CASE WHEN NEW.profit < 0 THEN ABS(NEW.profit) ELSE 0 END;

    INSERT INTO trading_stats (
        account_id, period_start, period_end, total_trades, winning_trades, losing_trades, total_profit, total_loss, net_profit, win_rate, profit_factor, largest_win, largest_loss, created_at
    ) VALUES (
        NEW.account_id, CURDATE(), CURDATE(), 1, v_win, v_loss, v_total_profit, v_total_loss,
        v_total_profit - v_total_loss,
        CASE WHEN (v_win + v_loss) > 0 THEN (v_win / (v_win + v_loss)) * 100 ELSE 0 END,
        CASE WHEN v_total_loss > 0 THEN v_total_profit / v_total_loss ELSE 0 END,
        v_largest_win,
        v_largest_loss,
        NOW()
    ) ON DUPLICATE KEY UPDATE
        total_trades = total_trades + 1,
        winning_trades = winning_trades + v_win,
        losing_trades = losing_trades + v_loss,
        total_profit = total_profit + v_total_profit,
        total_loss = total_loss + v_total_loss,
        net_profit = (total_profit + v_total_profit) - (total_loss + v_total_loss),
        win_rate = CASE WHEN (winning_trades + v_win + losing_trades + v_loss) > 0 THEN ((winning_trades + v_win) / (winning_trades + v_win + losing_trades + v_loss)) * 100 ELSE 0 END,
        profit_factor = CASE WHEN (total_loss + v_total_loss) > 0 THEN (total_profit + v_total_profit) / (total_loss + v_total_loss) ELSE 0 END,
        largest_win = GREATEST(largest_win, v_largest_win),
        largest_loss = GREATEST(largest_loss, v_largest_loss);

    -- Emitir evento KPI actualizado
    INSERT INTO webtrader_events (account_id, account_number, entity_type, entity_id, event_type, payload, created_at)
    SELECT 
        ta.id,
        ta.account_number,
        'account',
        ta.id,
        'kpi_updated',
        JSON_OBJECT('period', CURDATE(), 'last_trade_profit', NEW.profit),
        NOW()
    FROM trading_accounts ta WHERE ta.id = NEW.account_id;
END//

DELIMITER ;
