<?php
/**
 * Script para crear la tabla de clientes y un cliente de prueba
 */

require_once 'database/connection.php';

try {
    $db = getDB();
    
    echo "Creando tabla de clientes...\n";
    
    // Crear tabla de clientes
    $createClientsTable = "
    CREATE TABLE IF NOT EXISTS clients (
        id INT AUTO_INCREMENT PRIMARY KEY,
        lead_id INT NULL,
        email VARCHAR(100) NOT NULL UNIQUE,
        first_name VARCHAR(50) NOT NULL,
        last_name VARCHAR(50) NOT NULL,
        phone VARCHAR(20),
        status ENUM('active','inactive','suspended') DEFAULT 'active',
        password_hash VARCHAR(255) NOT NULL,
        desk_id INT NULL,
        balance DECIMAL(18,2) DEFAULT 0,
        equity DECIMAL(18,2) DEFAULT 0,
        margin_percent DECIMAL(7,4) DEFAULT 0,
        pnl DECIMAL(18,2) DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_email (email),
        INDEX idx_status (status),
        INDEX idx_clients_desk (desk_id)
    )";
    
    $db->execute($createClientsTable);
    echo "✓ Tabla 'clients' creada\n";
    
    // Crear tabla de cuentas de clientes
    $createAccountsTable = "
    CREATE TABLE IF NOT EXISTS client_accounts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        client_id INT NOT NULL,
        account_type VARCHAR(50) NOT NULL,
        account_number VARCHAR(100) NOT NULL,
        currency VARCHAR(10) DEFAULT 'USD',
        balance DECIMAL(18,2) DEFAULT 0,
        provider VARCHAR(100) NULL,
        status ENUM('active','inactive') DEFAULT 'active',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
        UNIQUE KEY unique_client_account (client_id, account_number)
    )";
    
    $db->execute($createAccountsTable);
    echo "✓ Tabla 'client_accounts' creada\n";
    
    // Crear tabla de depósitos
    $createDepositsTable = "
    CREATE TABLE IF NOT EXISTS client_deposits (
        id INT AUTO_INCREMENT PRIMARY KEY,
        client_id INT NOT NULL,
        amount DECIMAL(18,2) NOT NULL,
        currency VARCHAR(10) DEFAULT 'USD',
        method VARCHAR(50) NOT NULL,
        reference VARCHAR(100) NULL,
        status ENUM('pending','confirmed','failed') DEFAULT 'confirmed',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
        INDEX idx_client_id (client_id),
        INDEX idx_created_at (created_at)
    )";
    
    $db->execute($createDepositsTable);
    echo "✓ Tabla 'client_deposits' creada\n";
    
    // Crear tabla de transacciones
    $createTransactionsTable = "
    CREATE TABLE IF NOT EXISTS client_transactions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        client_id INT NOT NULL,
        type ENUM('deposit','withdrawal','adjustment') NOT NULL,
        amount DECIMAL(18,2) NOT NULL,
        currency VARCHAR(10) DEFAULT 'USD',
        balance_after DECIMAL(18,2) NULL,
        reference VARCHAR(100) NULL,
        notes TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
        INDEX idx_client_id (client_id),
        INDEX idx_type (type),
        INDEX idx_created_at (created_at)
    )";
    
    $db->execute($createTransactionsTable);
    echo "✓ Tabla 'client_transactions' creada\n";
    
    echo "\n✅ Todas las tablas de clientes creadas exitosamente!\n";
    
    // Ahora crear el cliente de prueba
    echo "\nCreando cliente de prueba...\n";
    
    $clientData = [
        'email' => 'cliente.demo@webtrader.com',
        'first_name' => 'Carlos',
        'last_name' => 'Mendoza',
        'phone' => '+34 666 777 888',
        'status' => 'active',
        'password_hash' => password_hash('demo123', PASSWORD_DEFAULT)
    ];
    
    // Verificar si el cliente ya existe
    $checkStmt = $db->select("SELECT id FROM clients WHERE email = ?", [$clientData['email']]);
    
    if (!empty($checkStmt)) {
        echo "El cliente ya existe. Actualizando...\n";
        $clientId = $checkStmt[0]['id'];
        
        $db->execute("
            UPDATE clients 
            SET first_name = ?, last_name = ?, phone = ?, status = ?, password_hash = ?, updated_at = NOW()
            WHERE email = ?
        ", [
            $clientData['first_name'],
            $clientData['last_name'], 
            $clientData['phone'],
            $clientData['status'],
            $clientData['password_hash'],
            $clientData['email']
        ]);
    } else {
        // Insertar nuevo cliente usando execute
        $insertQuery = "
            INSERT INTO clients (email, first_name, last_name, phone, status, password_hash, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW())
        ";
        
        $db->execute($insertQuery, [
            $clientData['email'],
            $clientData['first_name'],
            $clientData['last_name'],
            $clientData['phone'],
            $clientData['status'],
            $clientData['password_hash']
        ]);
        
        $clientId = $db->lastInsertId();
        echo "Cliente creado con ID: $clientId\n";
    }
    
    // Crear cuenta de ejemplo
    $accountData = [
        'client_id' => $clientId,
        'account_type' => 'Standard',
        'account_number' => 'STD-' . str_pad($clientId, 6, '0', STR_PAD_LEFT) . '-001',
        'currency' => 'USD',
        'balance' => 15750.50,
        'provider' => 'MetaTrader 4',
        'status' => 'active'
    ];
    
    // Limpiar cuentas existentes
    $db->execute("DELETE FROM client_accounts WHERE client_id = ?", [$clientId]);
    
    $insertAccountQuery = "
        INSERT INTO client_accounts (client_id, account_type, account_number, currency, balance, provider, status, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
    ";
    
    $db->execute($insertAccountQuery, [
        $accountData['client_id'],
        $accountData['account_type'],
        $accountData['account_number'],
        $accountData['currency'],
        $accountData['balance'],
        $accountData['provider'],
        $accountData['status']
    ]);
    
    echo "Cuenta creada: {$accountData['account_number']} - {$accountData['balance']} {$accountData['currency']}\n";
    
    echo "\n🎉 ¡Cliente de prueba creado exitosamente!\n";
    echo "📧 Email: {$clientData['email']}\n";
    echo "🔑 Password: demo123\n";
    echo "🆔 Client ID: $clientId\n";
    echo "\nPuedes usar estas credenciales para acceder al WebTrader.\n";
    echo "URL: http://localhost:8081/modules/client_portal/login.php\n";
    
} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
    echo "Trace: " . $e->getTraceAsString() . "\n";
}
?>