<?php
/**
 * Script para desplegar migración realtime del WebTrader
 * Ejecuta database/webtrader_realtime.sql usando la conexión del proyecto
 */

require_once __DIR__ . '/database/connection.php';

function applySqlFile($db, $sqlFile)
{
    if (!file_exists($sqlFile)) {
        throw new Exception("No se encontró el archivo: " . $sqlFile);
    }
    $sql = file_get_contents($sqlFile);
    if ($sql === false || strlen(trim($sql)) === 0) {
        throw new Exception("No se pudo leer contenido de " . basename($sqlFile));
    }

    // Separar statements respetando cambios de DELIMITER (e.g., // en triggers)
    $statements = [];
    $buffer = '';
    $delimiter = ';';
    $lines = preg_split('/\r?\n/', $sql);
    foreach ($lines as $line) {
        $trim = trim($line);
        if (stripos($trim, 'DELIMITER') === 0) {
            $parts = preg_split('/\s+/', $trim);
            $delimiter = isset($parts[1]) ? $parts[1] : ';';
            continue;
        }
        $buffer .= $line . "\n";
        $rtrimBuffer = rtrim($buffer);
        if ($delimiter !== '' && substr($rtrimBuffer, -strlen($delimiter)) === $delimiter) {
            $stmt = rtrim(substr($rtrimBuffer, 0, -strlen($delimiter)));
            if ($stmt !== '') {
                $statements[] = $stmt;
            }
            $buffer = '';
        }
    }
    $rest = trim($buffer);
    if ($rest !== '') { $statements[] = $rest; }

    $executed = 0;
    foreach ($statements as $statement) {
        try {
            if ($statement === '' || preg_match('/^\s*--/', $statement)) { continue; }
            $db->execute($statement);
            $executed++;
            echo "✓ Ejecutado: " . substr(preg_replace('/\s+/', ' ', $statement), 0, 80) . "...\n";
        } catch (Exception $e) {
            $msg = $e->getMessage();
            if (stripos($msg, 'already exists') !== false || stripos($msg, 'Duplicate') !== false) {
                echo "⚠ Ya existía: " . substr(preg_replace('/\s+/', ' ', $statement), 0, 80) . "...\n";
                continue;
            }
            echo "❗ Error ejecutando statement: " . $msg . "\n";
        }
    }

    return $executed;
}

try {
    $db = getDB();
    echo "Desplegando validación y migraciones del WebTrader...\n";

    // 1) Asegurar columnas con la migración incremental
    $colsFile = __DIR__ . '/database/update_webtrader_columns.sql';
    if (file_exists($colsFile)) {
        echo "\n▶ Aplicando columnas incrementales...\n";
        applySqlFile($db, $colsFile);
    } else {
        echo "⚠ No se encontró update_webtrader_columns.sql, continuando...\n";
    }

    // 2) Verificar y añadir llaves foráneas críticas si faltan
    echo "\n▶ Verificando llaves foráneas críticas...\n";
    $fkChecks = [
        // trading_positions -> trading_orders(order_id)
        [ 'table' => 'trading_positions', 'column' => 'order_id', 'ref_table' => 'trading_orders', 'ref_column' => 'id', 'constraint' => 'fk_trading_positions_order', 'delete' => 'SET NULL' ],
        // trading_positions -> trading_accounts(account_id)
        [ 'table' => 'trading_positions', 'column' => 'account_id', 'ref_table' => 'trading_accounts', 'ref_column' => 'id', 'constraint' => 'fk_trading_positions_account', 'delete' => 'CASCADE' ],
        // trading_history -> trading_positions(position_id)
        [ 'table' => 'trading_history', 'column' => 'position_id', 'ref_table' => 'trading_positions', 'ref_column' => 'id', 'constraint' => 'fk_trading_history_position', 'delete' => 'SET NULL' ],
        // trading_history -> trading_orders(order_id)
        [ 'table' => 'trading_history', 'column' => 'order_id', 'ref_table' => 'trading_orders', 'ref_column' => 'id', 'constraint' => 'fk_trading_history_order', 'delete' => 'SET NULL' ],
        // trading_history -> trading_accounts(account_id)
        [ 'table' => 'trading_history', 'column' => 'account_id', 'ref_table' => 'trading_accounts', 'ref_column' => 'id', 'constraint' => 'fk_trading_history_account', 'delete' => 'CASCADE' ],
    ];

    foreach ($fkChecks as $fk) {
        $sqlCheck = "SELECT COUNT(*) AS cnt FROM information_schema.KEY_COLUMN_USAGE 
                     WHERE TABLE_SCHEMA = DATABASE() 
                       AND TABLE_NAME = :table 
                       AND COLUMN_NAME = :column 
                       AND REFERENCED_TABLE_NAME = :ref_table ";
        $row = $db->query($sqlCheck, [
            ':table' => $fk['table'],
            ':column' => $fk['column'],
            ':ref_table' => $fk['ref_table']
        ])->fetch();
        $exists = (int)($row['cnt'] ?? 0) > 0;
        if ($exists) {
            echo "• FK existente: {$fk['table']}.{$fk['column']} -> {$fk['ref_table']}.{$fk['ref_column']}\n";
            continue;
        }
        // Añadir FK si falta
        $onDelete = $fk['delete'];
        $addSql = "ALTER TABLE {$fk['table']} 
                   ADD CONSTRAINT {$fk['constraint']} 
                   FOREIGN KEY ({$fk['column']}) 
                   REFERENCES {$fk['ref_table']}({$fk['ref_column']}) 
                   ON DELETE {$onDelete} ON UPDATE CASCADE";
        try {
            $db->execute($addSql);
            echo "✓ FK añadida: {$fk['constraint']}\n";
        } catch (Exception $e) {
            $msg = $e->getMessage();
            if (stripos($msg, 'Duplicate') !== false || stripos($msg, 'already exists') !== false) {
                echo "⚠ FK ya existía con otro nombre en {$fk['table']}\n";
            } else {
                echo "❗ Error añadiendo FK {$fk['constraint']}: {$msg}\n";
            }
        }
    }

    // 3) Reaplicar triggers/events realtime
    echo "\n▶ Reaplicando migración realtime (triggers y eventos)...\n";
    $rtFile = __DIR__ . '/database/webtrader_realtime.sql';
    $executed = applySqlFile($db, $rtFile);
    echo "\n✅ Validación completa. Statements ejecutados: $executed\n";
} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
}
?>
