<?php
/**
 * Database Connection
 * Establishes connection to the MySQL database
 */

require_once __DIR__ . '/includes/Env.php';
require_once __DIR__ . '/includes/Database.php';

// Load env and define legacy constants for backward compatibility
TonEnv::load();
if (!defined('DB_HOST')) define('DB_HOST', TonEnv::get('DB_HOST', 'localhost'));
if (!defined('DB_NAME')) define('DB_NAME', TonEnv::get('DB_NAME', 'tonsui_test'));
if (!defined('DB_USER')) define('DB_USER', TonEnv::get('DB_USER', 'root'));
if (!defined('DB_PASS')) define('DB_PASS', TonEnv::get('DB_PASS', ''));
if (!defined('DB_PORT')) define('DB_PORT', TonEnv::get('DB_PORT', '3306'));

try {
    // Use centralized Database singleton
    $db = Database::getInstance();
    $pdo = $db->getConnection();

    // Create missing tables if needed
    function ensureCriticalTablesExist() {
        global $pdo;

        // Check for critical tables
        $tables = [
            'admin_logs',
            'kyc_form_fields',
            'kyc_requests',
            // Core tables needed by deposit/transactions flows
            'transactions',
            'security_logs',
            'payment_methods'
        ];

        foreach ($tables as $table) {
            try {
                $stmt = $pdo->query("SHOW TABLES LIKE '{$table}'");
                if ($stmt->rowCount() === 0) {
                    // Table doesn't exist, create it based on name
                    switch($table) {
                        case 'kyc_form_fields':
                            $pdo->exec("CREATE TABLE IF NOT EXISTS kyc_form_fields (
                                id INT AUTO_INCREMENT PRIMARY KEY,
                                type VARCHAR(50) NOT NULL,
                                label VARCHAR(255) NOT NULL,
                                required BOOLEAN DEFAULT TRUE,
                                options TEXT NULL,
                                field_order INT NOT NULL DEFAULT 0,
                                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                            )");
                            break;
                        case 'admin_logs':
                            $pdo->exec("CREATE TABLE IF NOT EXISTS admin_logs (
                                id INT AUTO_INCREMENT PRIMARY KEY,
                                admin_id INT NOT NULL,
                                action VARCHAR(100) NOT NULL,
                                details TEXT NULL,
                                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                            )");
                            break;
                        case 'security_logs':
                            $pdo->exec("CREATE TABLE IF NOT EXISTS security_logs (
                                id INT AUTO_INCREMENT PRIMARY KEY,
                                user_id INT NULL,
                                event_type VARCHAR(100) NULL,
                                action VARCHAR(100) NULL,
                                ip_address VARCHAR(64) NULL,
                                user_agent VARCHAR(255) NULL,
                                details TEXT NULL,
                                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                            break;
                        case 'transactions':
                            $pdo->exec("CREATE TABLE IF NOT EXISTS transactions (
                                id INT AUTO_INCREMENT PRIMARY KEY,
                                user_id INT NOT NULL,
                                type VARCHAR(50) NOT NULL,
                                amount DECIMAL(20,8) NOT NULL DEFAULT 0,
                                status VARCHAR(32) NOT NULL DEFAULT 'pending',
                                details TEXT NULL,
                                reference VARCHAR(100) NULL,
                                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                                INDEX (user_id), INDEX (type), INDEX (status)
                            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                            break;
                        case 'payment_methods':
                            $pdo->exec("CREATE TABLE IF NOT EXISTS payment_methods (
                                id INT AUTO_INCREMENT PRIMARY KEY,
                                name VARCHAR(100) NOT NULL,
                                type VARCHAR(32) NOT NULL DEFAULT 'other',
                                currency VARCHAR(16) NOT NULL DEFAULT 'USD',
                                status VARCHAR(32) NOT NULL DEFAULT 'active',
                                min_withdrawal DECIMAL(20,8) NOT NULL DEFAULT 0,
                                details LONGTEXT NULL,
                                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
                            break;
                        case 'kyc_requests':
                            $pdo->exec("CREATE TABLE IF NOT EXISTS kyc_requests (
                                id INT AUTO_INCREMENT PRIMARY KEY,
                                user_id INT NOT NULL,
                                status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
                                data TEXT NOT NULL,
                                admin_id INT NULL,
                                admin_notes TEXT NULL,
                                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                            )");
                            break;
                    }
                }
            } catch (Exception $e) {
                // Log error but continue
                error_log("Error checking/creating table {$table}: " . $e->getMessage());
            }
        }
    }

    // Control auto-migration via env (default: off in production)
    $autoMigrate = null;
    if (class_exists('TonEnv')) { $autoMigrate = TonEnv::get('DB_AUTO_MIGRATE', '0'); }
    $doMigrate = ($autoMigrate === '1' || strtolower((string)$autoMigrate) === 'true');

    // Ensure critical tables exist (dev/staging recommended)
    if ($doMigrate) {
        ensureCriticalTablesExist();
    }

    // Adjust schemas for compatibility across variants (dev/staging recommended)
    if ($doMigrate) {
        try {
            $stmt = $pdo->query("SHOW TABLES LIKE 'transactions'");
            if ($stmt->rowCount() > 0) {
                // details
                try {
                    $c = $pdo->query("SHOW COLUMNS FROM transactions LIKE 'details'");
                    if ($c->rowCount() === 0) {
                        $pdo->exec("ALTER TABLE transactions ADD COLUMN details TEXT NULL AFTER status");
                    }
                } catch (Exception $e) { /* ignore */ }
                // reference
                try {
                    $c = $pdo->query("SHOW COLUMNS FROM transactions LIKE 'reference'");
                    if ($c->rowCount() === 0) {
                        $pdo->exec("ALTER TABLE transactions ADD COLUMN reference VARCHAR(100) NULL AFTER details");
                    }
                } catch (Exception $e) { /* ignore */ }
                // type
                try {
                    $c = $pdo->query("SHOW COLUMNS FROM transactions LIKE 'type'");
                    if ($c->rowCount() === 0) {
                        $pdo->exec("ALTER TABLE transactions ADD COLUMN type VARCHAR(50) NOT NULL DEFAULT 'deposit' AFTER user_id");
                    }
                } catch (Exception $e) { /* ignore */ }
                // status
                try {
                    $c = $pdo->query("SHOW COLUMNS FROM transactions LIKE 'status'");
                    if ($c->rowCount() === 0) {
                        $pdo->exec("ALTER TABLE transactions ADD COLUMN status VARCHAR(32) NOT NULL DEFAULT 'pending' AFTER amount");
                    }
                } catch (Exception $e) { /* ignore */ }
                // amount
                try {
                    $c = $pdo->query("SHOW COLUMNS FROM transactions LIKE 'amount'");
                    if ($c->rowCount() === 0) {
                        $pdo->exec("ALTER TABLE transactions ADD COLUMN amount DECIMAL(20,8) NOT NULL DEFAULT 0 AFTER type");
                    }
                } catch (Exception $e) { /* ignore */ }
                // created_at
                try {
                    $c = $pdo->query("SHOW COLUMNS FROM transactions LIKE 'created_at'");
                    if ($c->rowCount() === 0) {
                        $pdo->exec("ALTER TABLE transactions ADD COLUMN created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP");
                    }
                } catch (Exception $e) { /* ignore */ }
                // updated_at
                try {
                    $c = $pdo->query("SHOW COLUMNS FROM transactions LIKE 'updated_at'");
                    if ($c->rowCount() === 0) {
                        $pdo->exec("ALTER TABLE transactions ADD COLUMN updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
                    }
                } catch (Exception $e) { /* ignore */ }
            }
        } catch (Exception $e) {
            // Log but don't interrupt request
            error_log('Schema adjust failure (transactions): ' . $e->getMessage());
        }

        // Ensure payment_methods has type and details columns
        try {
            $stmt = $pdo->query("SHOW TABLES LIKE 'payment_methods'");
            if ($stmt->rowCount() > 0) {
                try {
                    $c = $pdo->query("SHOW COLUMNS FROM payment_methods LIKE 'type'");
                    if ($c->rowCount() === 0) {
                        $pdo->exec("ALTER TABLE payment_methods ADD COLUMN type VARCHAR(32) NOT NULL DEFAULT 'other' AFTER name");
                    }
                } catch (Exception $e) { /* ignore */ }
                try {
                    $c = $pdo->query("SHOW COLUMNS FROM payment_methods LIKE 'details'");
                    if ($c->rowCount() === 0) {
                        $pdo->exec("ALTER TABLE payment_methods ADD COLUMN details LONGTEXT NULL AFTER min_withdrawal");
                    }
                } catch (Exception $e) { /* ignore */ }
            }
        } catch (Exception $e) {
            error_log('Schema adjust failure (payment_methods): ' . $e->getMessage());
        }
    }

} catch (Exception $e) {
    // Log database connection error
    error_log('Database connection failed: ' . $e->getMessage());

    // Return JSON error if this is an API endpoint
    if (isset($_SERVER['REQUEST_URI']) && strpos($_SERVER['REQUEST_URI'], '.php') !== false) {
        header('Content-Type: application/json');
        http_response_code(500);
        echo json_encode([
            'success' => false,
            'error' => 'Database connection failed',
            'details' => [
                'message' => $e->getMessage(),
                'host' => DB_HOST,
                'database' => DB_NAME
            ]
        ]);
        exit;
    }

    throw $e;
}