<?php
// Load environment variables
require_once __DIR__ . '/../vendor/autoload.php';

$dotenv = Dotenv\Dotenv::createImmutable(__DIR__ . '/..');
$dotenv->load();

class Database {
    private $db;
    private $dbType;

    public function __construct() {
        try {
            // Determine database type from environment variable
            $this->dbType = $_ENV['DB_TYPE'] ?? 'sqlite';

            if ($this->dbType === 'mysql') {
                // MySQL connection
                $host = $_ENV['DB_HOST'] ?? 'localhost';
                $port = $_ENV['DB_PORT'] ?? '3306';
                $dbname = $_ENV['DB_NAME'] ?? 'tenytec1_genixgame';
                $username = $_ENV['DB_USER'] ?? 'tenytec1_genixgame';
                $password = $_ENV['DB_PASSWORD'] ?? 'B2@@3#iNG##@23@gOg';

                // Standard host/port connection (works for cPanel and most hosting environments)
                $dsn = "mysql:host={$host};port={$port};dbname={$dbname};charset=utf8mb4";

                $this->db = new PDO($dsn, $username, $password, [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false,
                    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
                ]);

                // Create tables if they don't exist
                $this->createTablesIfNotExist();

                // Check and update existing tables if needed
                $this->checkAndUpdateTables();
            } else {
                // SQLite connection (default)
                $dbPath = __DIR__ . '/../games.db';
                $createTables = !file_exists($dbPath);

                $this->db = new PDO('sqlite:' . $dbPath);
                $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                $this->db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

                // Create tables if they don't exist
                $this->createTablesIfNotExist();

                // Check and update existing tables if needed
                $this->checkAndUpdateTables();
            }
        } catch (PDOException $e) {
            error_log('Database connection error: ' . $e->getMessage());
            throw new Exception('Database connection error: ' . $e->getMessage());
        }
    }

    /**
     * Create database tables if they don't exist
     */
    private function createTablesIfNotExist() {
        try {
            $currentTimestamp = date('Y-m-d H:i:s');

            // Create tables based on database type
            if ($this->dbType === 'mysql') {
                // MySQL tables
                $this->createMySQLTables($currentTimestamp);
            } else {
                // SQLite tables
                $this->createSQLiteTables($currentTimestamp);
            }

            error_log('Database tables created successfully');
        } catch (PDOException $e) {
            error_log('Error creating database tables: ' . $e->getMessage());
            throw new Exception('Error creating database tables: ' . $e->getMessage());
        }
    }

    /**
     * Create tables for SQLite database
     *
     * @param string $currentTimestamp Current timestamp
     */
    private function createSQLiteTables($currentTimestamp) {
        // Create categories table
        $this->db->exec("CREATE TABLE IF NOT EXISTS categories (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            slug TEXT NOT NULL UNIQUE,
            sort_order INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )");

        // Create games table
        $this->db->exec("CREATE TABLE IF NOT EXISTS games (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            description TEXT,
            thumbnail TEXT,
            embed_code TEXT,
            source TEXT,
            external_id TEXT,
            category_id INTEGER,
            slug TEXT NOT NULL UNIQUE,
            featured INTEGER DEFAULT 0,
            active INTEGER DEFAULT 1,
            views INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
        )");

        // Create users table for both admin and regular users
        $this->db->exec("CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            password TEXT NOT NULL,
            email TEXT UNIQUE,
            role TEXT DEFAULT 'user',
            name TEXT,
            profile_image TEXT,
            is_active INTEGER DEFAULT 1,
            email_verified INTEGER DEFAULT 0,
            verification_token TEXT,
            created_at TIMESTAMP,
            updated_at TIMESTAMP
        )");

        // Check if users table exists and has the updated_at column
        $hasUpdatedAtColumn = $this->columnExists('users', 'updated_at');

        // Set timestamps for existing users (only if table has data)
        try {
            if ($hasUpdatedAtColumn) {
                $this->db->exec("UPDATE users SET created_at = '$currentTimestamp', updated_at = '$currentTimestamp' WHERE created_at IS NULL OR created_at = ''");
            } else {
                $this->db->exec("UPDATE users SET created_at = '$currentTimestamp' WHERE created_at IS NULL OR created_at = ''");
            }
        } catch (PDOException $e) {
            // Ignore errors for non-existent columns
        }

        // Create password_resets table for forgot password functionality
        $this->db->exec("CREATE TABLE IF NOT EXISTS password_resets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT NOT NULL,
            token TEXT NOT NULL,
            created_at TIMESTAMP,
            expires_at TIMESTAMP
        )");

        // Create game_plays table to track user game history
        $this->db->exec("CREATE TABLE IF NOT EXISTS game_plays (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            game_id INTEGER NOT NULL,
            play_time INTEGER DEFAULT 0,
            created_at TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE
        )");

        // Set timestamps for existing game plays (only if table has data)
        try {
            $this->db->exec("UPDATE game_plays SET created_at = '$currentTimestamp' WHERE created_at IS NULL OR created_at = ''");
        } catch (PDOException $e) {
            // Ignore errors for non-existent tables or columns
        }

        // Create game_favorites table to track user favorite games
        $this->db->exec("CREATE TABLE IF NOT EXISTS game_favorites (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            game_id INTEGER NOT NULL,
            created_at TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
            UNIQUE(user_id, game_id)
        )");

        // Set timestamps for existing favorites (only if table has data)
        try {
            $this->db->exec("UPDATE game_favorites SET created_at = '$currentTimestamp' WHERE created_at IS NULL OR created_at = ''");
        } catch (PDOException $e) {
            // Ignore errors for non-existent tables or columns
        }

        // Create user_coins table to track user coin balance
        $this->db->exec("CREATE TABLE IF NOT EXISTS user_coins (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            balance INTEGER DEFAULT 0,
            total_earned INTEGER DEFAULT 0,
            created_at TIMESTAMP,
            updated_at TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            UNIQUE(user_id)
        )");

        // Set timestamps for existing user_coins (only if table exists and has data)
        try {
            $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='user_coins'");
            if ($stmt->fetchColumn()) {
                $this->db->exec("UPDATE user_coins SET created_at = '$currentTimestamp', updated_at = '$currentTimestamp' WHERE created_at IS NULL OR created_at = ''");
            }
        } catch (PDOException $e) {
            // Ignore errors for non-existent tables
        }

        // Create coin_transactions table to track coin earning and spending
        $this->db->exec("CREATE TABLE IF NOT EXISTS coin_transactions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            type TEXT NOT NULL,
            amount INTEGER NOT NULL,
            description TEXT,
            game_id INTEGER,
            play_time INTEGER DEFAULT 0,
            created_at TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE SET NULL
        )");

        // Set timestamps for existing coin_transactions (only if table exists and has data)
        try {
            $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='coin_transactions'");
            if ($stmt->fetchColumn()) {
                $this->db->exec("UPDATE coin_transactions SET created_at = '$currentTimestamp' WHERE created_at IS NULL OR created_at = ''");
            }
        } catch (PDOException $e) {
            // Ignore errors for non-existent tables
        }

        // Create withdrawal_requests table to track withdrawal requests
        $this->db->exec("CREATE TABLE IF NOT EXISTS withdrawal_requests (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            amount INTEGER NOT NULL,
            status TEXT DEFAULT 'pending',
            payment_method TEXT,
            payment_details TEXT,
            admin_notes TEXT,
            created_at TIMESTAMP,
            updated_at TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )");

        // Set timestamps for existing withdrawal_requests (only if table exists and has data)
        try {
            $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='withdrawal_requests'");
            if ($stmt->fetchColumn()) {
                $this->db->exec("UPDATE withdrawal_requests SET created_at = '$currentTimestamp', updated_at = '$currentTimestamp' WHERE created_at IS NULL OR created_at = ''");
            }
        } catch (PDOException $e) {
            // Ignore errors for non-existent tables
        }

        // Create settings table
        $this->db->exec("CREATE TABLE IF NOT EXISTS settings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            setting_key TEXT NOT NULL UNIQUE,
            setting_value TEXT,
            created_at TIMESTAMP,
            updated_at TIMESTAMP
        )");

        // Check if settings table exists and has the updated_at column
        $hasUpdatedAtColumn = $this->columnExists('settings', 'updated_at');

        // Set timestamps for existing settings (only if table has data)
        try {
            if ($hasUpdatedAtColumn) {
                $this->db->exec("UPDATE settings SET created_at = '$currentTimestamp', updated_at = '$currentTimestamp' WHERE created_at IS NULL OR created_at = ''");
            } else {
                $this->db->exec("UPDATE settings SET created_at = '$currentTimestamp' WHERE created_at IS NULL OR created_at = ''");
            }
        } catch (PDOException $e) {
            // Ignore errors for non-existent columns
        }

        // Insert default settings if settings table is empty
        $this->insertDefaultSettings($currentTimestamp);
        
        // Update categories with sort_order = 0 to NULL for random ordering
        try {
            $this->db->exec("UPDATE categories SET sort_order = NULL WHERE sort_order = 0");
        } catch (PDOException $e) {
            // Ignore errors if categories table doesn't exist yet
        }
    }

    /**
     * Create tables for MySQL database
     *
     * @param string $currentTimestamp Current timestamp
     */
    private function createMySQLTables($currentTimestamp) {
        // Create categories table
        $this->db->exec("CREATE TABLE IF NOT EXISTS categories (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            slug VARCHAR(255) NOT NULL UNIQUE,
            sort_order INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // Create games table
        $this->db->exec("CREATE TABLE IF NOT EXISTS games (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            description TEXT,
            thumbnail VARCHAR(255),
            embed_code TEXT,
            source VARCHAR(255),
            external_id VARCHAR(255),
            category_id INT UNSIGNED,
            slug VARCHAR(255) NOT NULL UNIQUE,
            featured TINYINT DEFAULT 0,
            active TINYINT DEFAULT 1,
            views INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // Create users table for both admin and regular users
        $this->db->exec("CREATE TABLE IF NOT EXISTS users (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(255) NOT NULL UNIQUE,
            password VARCHAR(255) NOT NULL,
            email VARCHAR(255) UNIQUE,
            role VARCHAR(50) DEFAULT 'user',
            name VARCHAR(255),
            profile_image VARCHAR(255),
            is_active TINYINT DEFAULT 1,
            email_verified TINYINT DEFAULT 0,
            verification_token VARCHAR(255),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // Create password_resets table for forgot password functionality
        $this->db->exec("CREATE TABLE IF NOT EXISTS password_resets (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            email VARCHAR(255) NOT NULL,
            token VARCHAR(255) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            expires_at TIMESTAMP NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // Create game_plays table to track user game history
        $this->db->exec("CREATE TABLE IF NOT EXISTS game_plays (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            user_id INT UNSIGNED NOT NULL,
            game_id INT UNSIGNED NOT NULL,
            play_time INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // Create game_favorites table to track user favorite games
        $this->db->exec("CREATE TABLE IF NOT EXISTS game_favorites (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            user_id INT UNSIGNED NOT NULL,
            game_id INT UNSIGNED NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
            UNIQUE(user_id, game_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // Create user_coins table to track user coin balance
        $this->db->exec("CREATE TABLE IF NOT EXISTS user_coins (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            user_id INT UNSIGNED NOT NULL,
            balance INT DEFAULT 0,
            total_earned INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            UNIQUE(user_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // Create coin_transactions table to track coin earning and spending
        $this->db->exec("CREATE TABLE IF NOT EXISTS coin_transactions (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            user_id INT UNSIGNED NOT NULL,
            type VARCHAR(50) NOT NULL,
            amount INT NOT NULL,
            description TEXT,
            game_id INT UNSIGNED,
            play_time INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // Create withdrawal_requests table to track withdrawal requests
        $this->db->exec("CREATE TABLE IF NOT EXISTS withdrawal_requests (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            user_id INT UNSIGNED NOT NULL,
            amount INT NOT NULL,
            status VARCHAR(50) DEFAULT 'pending',
            payment_method VARCHAR(100),
            payment_details TEXT,
            admin_notes TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // Create settings table
        $this->db->exec("CREATE TABLE IF NOT EXISTS settings (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            setting_key VARCHAR(255) NOT NULL UNIQUE,
            setting_value TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // Insert default settings if settings table is empty
        $this->insertDefaultSettings($currentTimestamp);
        
        // Update categories with sort_order = 0 to NULL for random ordering
        try {
            $this->db->exec("UPDATE categories SET sort_order = NULL WHERE sort_order = 0");
        } catch (PDOException $e) {
            // Ignore errors if categories table doesn't exist yet
        }
    }

    /**
     * Insert default settings if settings table is empty
     *
     * @param string $currentTimestamp Current timestamp
     */
    private function insertDefaultSettings($currentTimestamp) {
        // Check if settings table is empty
        $stmt = $this->db->query("SELECT COUNT(*) FROM settings");
        if ($stmt->fetchColumn() == 0) {
            $this->db->exec("INSERT INTO settings (setting_key, setting_value, created_at, updated_at) VALUES
                            ('site_name', 'Game Portal', '$currentTimestamp', '$currentTimestamp'),
                            ('site_description', 'Play free online games', '$currentTimestamp', '$currentTimestamp'),
                            ('theme_color', '#4f46e5', '$currentTimestamp', '$currentTimestamp'),
                            ('logo_path', '', '$currentTimestamp', '$currentTimestamp'),
                            ('favicon_path', '', '$currentTimestamp', '$currentTimestamp'),
                            ('games_per_page', '12', '$currentTimestamp', '$currentTimestamp'),
                            ('featured_games', '6', '$currentTimestamp', '$currentTimestamp'),
                            ('show_leaderboard', '1', '$currentTimestamp', '$currentTimestamp'),
                            ('leaderboard_limit', '10', '$currentTimestamp', '$currentTimestamp'),
                            ('category_games_per_page', '12', '$currentTimestamp', '$currentTimestamp'),
                            ('categories_per_page', '12', '$currentTimestamp', '$currentTimestamp'),
                            ('homepage_display_type', 'hero', '$currentTimestamp', '$currentTimestamp'),
                            ('smtp_host', '', '$currentTimestamp', '$currentTimestamp'),
                            ('smtp_port', '', '$currentTimestamp', '$currentTimestamp'),
                            ('smtp_username', '', '$currentTimestamp', '$currentTimestamp'),
                            ('smtp_password', '', '$currentTimestamp', '$currentTimestamp'),
                            ('smtp_from_email', '', '$currentTimestamp', '$currentTimestamp'),
                            ('smtp_from_name', 'Game Portal', '$currentTimestamp', '$currentTimestamp')");

            error_log('Default settings created');
        }
    }

    public function getConnection() {
        return $this->db;
    }

    public function query($sql, $params = []) {
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }

    public function fetchAll($sql, $params = []) {
        return $this->query($sql, $params)->fetchAll();
    }

    public function fetch($sql, $params = []) {
        return $this->query($sql, $params)->fetch();
    }

    public function fetchColumn($sql, $params = []) {
        return $this->query($sql, $params)->fetchColumn();
    }

    /**
     * Check and update existing tables if needed
     */
    private function checkAndUpdateTables() {
        try {
            if ($this->dbType === 'mysql') {
                // MySQL table updates
                $this->checkAndUpdateMySQLTables();
            } else {
                // SQLite table updates
                $this->checkAndUpdateSQLiteTables();
            }
        } catch (PDOException $e) {
            error_log('Error updating tables: ' . $e->getMessage());
            throw new Exception('Error updating tables: ' . $e->getMessage());
        }
    }

    /**
     * Check and update SQLite tables if needed
     */
    private function checkAndUpdateSQLiteTables() {
        // Check users table for missing columns
        $this->checkAndUpdateSQLiteUsersTable();

        // Check if game_plays table exists, create if not
        $this->checkAndCreateSQLiteGamePlaysTable();

        // Check if password_resets table exists, create if not
        $this->checkAndCreateSQLitePasswordResetsTable();
    }

    /**
     * Check and update MySQL tables if needed
     */
    private function checkAndUpdateMySQLTables() {
        // Check users table for missing columns
        $this->checkAndUpdateMySQLUsersTable();

        // Check if game_plays table exists, create if not
        $this->checkAndCreateMySQLGamePlaysTable();

        // Check if password_resets table exists, create if not
        $this->checkAndCreateMySQLPasswordResetsTable();
    }

    /**
     * Check and update SQLite users table if needed
     */
    private function checkAndUpdateSQLiteUsersTable() {
        // Get current columns in users table
        $stmt = $this->db->query("PRAGMA table_info(users)");
        $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $columnNames = array_column($columns, 'name');

        // Check for missing columns and add them
        // SQLite doesn't allow adding columns with non-constant defaults like CURRENT_TIMESTAMP
        // So we'll add them without defaults and then update them
        $requiredColumns = [
            'name' => 'TEXT',
            'profile_image' => 'TEXT',
            'is_active' => 'INTEGER DEFAULT 1',
            'email_verified' => 'INTEGER DEFAULT 0',
            'verification_token' => 'TEXT',
            'updated_at' => 'TIMESTAMP'
        ];

        $columnsAdded = false;

        foreach ($requiredColumns as $column => $type) {
            if (!in_array($column, $columnNames)) {
                try {
                    $this->db->exec("ALTER TABLE users ADD COLUMN $column $type");
                    $columnsAdded = true;
                    error_log("Added missing column '$column' to users table");
                } catch (PDOException $e) {
                    error_log("Error adding column '$column': " . $e->getMessage());
                }
            }
        }

        // If we added the updated_at column, set it to current timestamp
        if ($columnsAdded && !in_array('updated_at', $columnNames)) {
            $currentTimestamp = date('Y-m-d H:i:s');
            $this->db->exec("UPDATE users SET updated_at = '$currentTimestamp'");
        }

        // Check if role column has default value 'admin', update to 'user'
        $stmt = $this->db->query("SELECT sql FROM sqlite_master WHERE type='table' AND name='users'");
        $tableDefinition = $stmt->fetchColumn();

        if (strpos($tableDefinition, "role TEXT DEFAULT 'admin'") !== false) {
            // We can't directly alter column default in SQLite, so we need to update existing rows
            $this->db->exec("UPDATE users SET role = 'user' WHERE role = 'admin'");
            error_log("Updated default role for all users to 'user'");
        }
    }

    /**
     * Check and update MySQL users table if needed
     */
    private function checkAndUpdateMySQLUsersTable() {
        // Get current columns in users table
        $stmt = $this->db->query("SHOW COLUMNS FROM users");
        $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $columnNames = array_column($columns, 'Field');

        // Check for missing columns and add them
        $requiredColumns = [
            'name' => 'VARCHAR(255)',
            'profile_image' => 'VARCHAR(255)',
            'is_active' => 'TINYINT DEFAULT 1',
            'email_verified' => 'TINYINT DEFAULT 0',
            'verification_token' => 'VARCHAR(255)',
            'updated_at' => 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'
        ];

        foreach ($requiredColumns as $column => $type) {
            if (!in_array($column, $columnNames)) {
                try {
                    $this->db->exec("ALTER TABLE users ADD COLUMN $column $type");
                    error_log("Added missing column '$column' to users table");
                } catch (PDOException $e) {
                    error_log("Error adding column '$column': " . $e->getMessage());
                }
            }
        }

        // Check if role column has default value 'admin', update to 'user'
        foreach ($columns as $col) {
            if ($col['Field'] === 'role' && strpos($col['Default'], 'admin') !== false) {
                $this->db->exec("ALTER TABLE users ALTER COLUMN role SET DEFAULT 'user'");
                $this->db->exec("UPDATE users SET role = 'user' WHERE role = 'admin'");
                error_log("Updated default role for all users to 'user'");
                break;
            }
        }
    }

    /**
     * Check and create SQLite game_plays table if it doesn't exist
     */
    private function checkAndCreateSQLiteGamePlaysTable() {
        $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='game_plays'");
        if (!$stmt->fetchColumn()) {
            try {
                // Create table without CURRENT_TIMESTAMP default
                $this->db->exec("CREATE TABLE game_plays (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    game_id INTEGER NOT NULL,
                    play_time INTEGER DEFAULT 0,
                    created_at TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                    FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE
                )");

                // Set default timestamp for existing rows
                $currentTimestamp = date('Y-m-d H:i:s');
                $this->db->exec("UPDATE game_plays SET created_at = '$currentTimestamp'");

                error_log("Created game_plays table");
            } catch (PDOException $e) {
                error_log("Error creating game_plays table: " . $e->getMessage());
            }
        }

        // Check if game_favorites table exists
        $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='game_favorites'");
        if (!$stmt->fetchColumn()) {
            try {
                // Create table without CURRENT_TIMESTAMP default
                $this->db->exec("CREATE TABLE game_favorites (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    game_id INTEGER NOT NULL,
                    created_at TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                    FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
                    UNIQUE(user_id, game_id)
                )");

                // Set default timestamp for existing rows
                $currentTimestamp = date('Y-m-d H:i:s');
                $this->db->exec("UPDATE game_favorites SET created_at = '$currentTimestamp'");

                error_log("Created game_favorites table");
            } catch (PDOException $e) {
                error_log("Error creating game_favorites table: " . $e->getMessage());
            }
        }
    }

    /**
     * Check and create MySQL game_plays table if it doesn't exist
     */
    private function checkAndCreateMySQLGamePlaysTable() {
        // In MySQL, we use CREATE TABLE IF NOT EXISTS in createMySQLTables
        // This is just a placeholder in case we need specific updates
    }

    /**
     * Check and create MySQL game_favorites table if it doesn't exist
     */
    private function checkAndCreateMySQLGameFavoritesTable() {
        // In MySQL, we use CREATE TABLE IF NOT EXISTS in createMySQLTables
        // This is just a placeholder in case we need specific updates
    }

    /**
     * Check and create SQLite password_resets table if it doesn't exist
     */
    private function checkAndCreateSQLitePasswordResetsTable() {
        $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='password_resets'");
        if (!$stmt->fetchColumn()) {
            try {
                // Create table without CURRENT_TIMESTAMP default
                $this->db->exec("CREATE TABLE password_resets (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    email TEXT NOT NULL,
                    token TEXT NOT NULL,
                    created_at TIMESTAMP,
                    expires_at TIMESTAMP
                )");

                error_log("Created password_resets table");
            } catch (PDOException $e) {
                error_log("Error creating password_resets table: " . $e->getMessage());
            }
        }
    }

    /**
     * Check and create MySQL password_resets table if it doesn't exist
     */
    private function checkAndCreateMySQLPasswordResetsTable() {
        // In MySQL, we use CREATE TABLE IF NOT EXISTS in createMySQLTables
        // This is just a placeholder in case we need specific updates
    }

    /**
     * Check if a column exists in a table
     *
     * @param string $table Table name
     * @param string $column Column name
     * @return bool True if column exists, false otherwise
     */
    private function columnExists($table, $column) {
        try {
            if ($this->dbType === 'mysql') {
                // MySQL column check
                $stmt = $this->db->query("SHOW COLUMNS FROM `$table` LIKE '$column'");
                return $stmt->rowCount() > 0;
            } else {
                // SQLite column check
                // First check if the table exists
                $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='$table'");
                if (!$stmt->fetchColumn()) {
                    return false;
                }

                // Then check if the column exists in the table
                $stmt = $this->db->query("PRAGMA table_info($table)");
                $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);

                foreach ($columns as $col) {
                    if ($col['name'] === $column) {
                        return true;
                    }
                }

                return false;
            }
        } catch (PDOException $e) {
            error_log("Error checking if column exists: " . $e->getMessage());
            return false;
        }
    }
}