<?php
/**
 * WebTrader/CRM Schema Migration
 * Ejecuta creación/alteración de tablas necesarias para sincronización de KPIs, operaciones y datos.
 */

session_start();
header('Content-Type: application/json');

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

try {
    if (!$pdo) { throw new Exception('Conexión de BD no disponible'); }

    // Nota: DDL en MySQL hace commit implícito; evitar transacciones

    // Tabla de cuentas de trading
    $pdo->exec("CREATE TABLE IF NOT EXISTS trading_accounts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        client_id INT NOT NULL,
        account_number VARCHAR(32) NOT NULL UNIQUE,
        account_type VARCHAR(32) NOT NULL DEFAULT 'standard',
        currency VARCHAR(8) NOT NULL DEFAULT 'USD',
        leverage INT NOT NULL DEFAULT 100,
        balance DECIMAL(18,2) NOT NULL DEFAULT 0.00,
        equity DECIMAL(18,2) NOT NULL DEFAULT 0.00,
        margin DECIMAL(18,2) NOT NULL DEFAULT 0.00,
        free_margin DECIMAL(18,2) NOT NULL DEFAULT 0.00,
        margin_level DECIMAL(9,2) NOT NULL DEFAULT 0.00,
        status VARCHAR(16) NOT NULL DEFAULT 'active',
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NULL,
        INDEX idx_ta_client (client_id),
        INDEX idx_ta_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // Tabla de instrumentos
    $pdo->exec("CREATE TABLE IF NOT EXISTS trading_instruments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        symbol VARCHAR(32) NOT NULL UNIQUE,
        name VARCHAR(64) NULL,
        category VARCHAR(32) NOT NULL DEFAULT 'forex',
        contract_size INT NOT NULL DEFAULT 100000,
        min_volume DECIMAL(10,2) NOT NULL DEFAULT 0.01,
        max_volume DECIMAL(10,2) NOT NULL DEFAULT 100.00,
        volume_step DECIMAL(10,4) NOT NULL DEFAULT 0.01,
        margin_rate DECIMAL(10,6) NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        updated_at DATETIME NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // Tabla de posiciones
    $pdo->exec("CREATE TABLE IF NOT EXISTS trading_positions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        account_id INT NOT NULL,
        symbol VARCHAR(32) NOT NULL,
        type VARCHAR(8) NOT NULL,
        volume DECIMAL(10,2) NOT NULL,
        open_price DECIMAL(18,6) NOT NULL,
        stop_loss DECIMAL(18,6) NULL,
        take_profit DECIMAL(18,6) NULL,
        margin DECIMAL(18,2) NOT NULL DEFAULT 0.00,
        profit DECIMAL(18,2) NOT NULL DEFAULT 0.00,
        current_price DECIMAL(18,6) NULL,
        close_price DECIMAL(18,6) NULL,
        close_time DATETIME NULL,
        commission DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
        swap DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
        order_id INT NULL,
        comment VARCHAR(255) NULL,
        status VARCHAR(16) NOT NULL DEFAULT 'open',
        opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        closed_at DATETIME NULL,
        INDEX idx_tp_account (account_id),
        INDEX idx_tp_symbol (symbol),
        INDEX idx_tp_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // Asegurar columnas faltantes en trading_positions
    $tpRequired = [
        'margin DECIMAL(18,2) NOT NULL DEFAULT 0.00',
        'profit DECIMAL(18,2) NOT NULL DEFAULT 0.00',
        'current_price DECIMAL(18,6) NULL',
        'close_price DECIMAL(18,6) NULL',
        'close_time DATETIME NULL',
        'commission DECIMAL(18,4) NOT NULL DEFAULT 0.0000',
        'swap DECIMAL(18,4) NOT NULL DEFAULT 0.0000',
        'order_id INT NULL'
    ];
    try {
        $dbName = $pdo->query('SELECT DATABASE()')->fetchColumn();
        $colsStmt = $pdo->prepare("SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema = ? AND table_name = 'trading_positions'");
        $colsStmt->execute([$dbName]);
        $existing = array_map('strtolower', array_column($colsStmt->fetchAll(PDO::FETCH_ASSOC), 'COLUMN_NAME'));
        foreach ($tpRequired as $def) {
            [$col] = explode(' ', $def, 2);
            $col = strtolower($col);
            if (!in_array($col, $existing, true)) {
                try { $pdo->exec("ALTER TABLE trading_positions ADD COLUMN $def"); } catch (Throwable $e) {}
            }
        }
    } catch (Throwable $e) {}

    // Tabla de órdenes
    $pdo->exec("CREATE TABLE IF NOT EXISTS trading_orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        account_id INT NOT NULL,
        symbol VARCHAR(32) NOT NULL,
        type VARCHAR(16) NOT NULL,
        volume DECIMAL(10,2) NOT NULL,
        price DECIMAL(18,6) NOT NULL,
        stop_loss DECIMAL(18,6) NULL,
        take_profit DECIMAL(18,6) NULL,
        comment VARCHAR(255) NULL,
        status VARCHAR(16) NOT NULL DEFAULT 'pending',
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NULL,
        INDEX idx_to_account (account_id),
        INDEX idx_to_symbol (symbol),
        INDEX idx_to_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // Tabla de transacciones (depósitos/retiros)
    $pdo->exec("CREATE TABLE IF NOT EXISTS account_transactions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        account_id INT NOT NULL,
        type ENUM('deposit','withdraw') NOT NULL,
        amount DECIMAL(18,2) NOT NULL,
        currency VARCHAR(8) NOT NULL DEFAULT 'USD',
        status VARCHAR(16) NOT NULL DEFAULT 'processed',
        method VARCHAR(32) NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_at_account (account_id),
        INDEX idx_at_type (type),
        INDEX idx_at_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // Historial de trading (aperturas/cierres)
    $pdo->exec("CREATE TABLE IF NOT EXISTS trading_history (
        id INT AUTO_INCREMENT PRIMARY KEY,
        account_id INT NOT NULL,
        position_id INT NOT NULL,
        symbol VARCHAR(32) NOT NULL,
        type VARCHAR(8) NOT NULL,
        volume DECIMAL(10,2) NOT NULL,
        open_price DECIMAL(18,6) NOT NULL,
        close_price DECIMAL(18,6) NULL,
        pnl DECIMAL(18,2) NULL,
        commission DECIMAL(18,2) NULL,
        opened_at DATETIME NULL,
        closed_at DATETIME NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_th_account (account_id),
        INDEX idx_th_position (position_id),
        INDEX idx_th_symbol (symbol)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // Transacciones del CRM (ajustes por P&L)
    $pdo->exec("CREATE TABLE IF NOT EXISTS client_transactions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        client_id INT NOT NULL,
        type VARCHAR(32) NOT NULL,
        amount DECIMAL(18,2) NOT NULL,
        currency VARCHAR(8) NOT NULL DEFAULT 'USD',
        reference VARCHAR(64) NULL,
        notes VARCHAR(255) NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_ct_client (client_id),
        INDEX idx_ct_type (type)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // Asegurar columnas faltantes en trading_accounts
    $ensureCols = [
        'margin DECIMAL(18,2) NOT NULL DEFAULT 0.00',
        'equity DECIMAL(18,2) NOT NULL DEFAULT 0.00',
        'free_margin DECIMAL(18,2) NOT NULL DEFAULT 0.00',
        'margin_level DECIMAL(9,2) NOT NULL DEFAULT 0.00'
    ];
    foreach ($ensureCols as $def) {
        [$col] = explode(' ', $def, 2);
        try {
            $pdo->exec("ALTER TABLE trading_accounts ADD COLUMN $def");
        } catch (Throwable $e) { /* existe */ }
    }

    // Índices recomendados (idempotentes con captura de errores)
    try { $pdo->exec("ALTER TABLE trading_accounts ADD INDEX idx_ta_client_acc (client_id, account_number)"); } catch (Throwable $__) {}
    try { $pdo->exec("ALTER TABLE trading_positions ADD INDEX idx_tp_account_status (account_id, status)"); } catch (Throwable $__) {}
    try { $pdo->exec("ALTER TABLE trading_orders ADD INDEX idx_to_account_status (account_id, status)"); } catch (Throwable $__) {}
    try { $pdo->exec("ALTER TABLE payment_attempts ADD INDEX idx_pa_client_acc_status (client_id, account_number, status)"); } catch (Throwable $__) {}

    echo json_encode(['success' => true, 'message' => 'Migración ejecutada en spin2pay_profixcrm']);
} catch (Exception $e) {
    // Sin rollback para DDL
    http_response_code(500);
    echo json_encode(['success' => false, 'error' => $e->getMessage()]);
}
?>
