<?php
require_once __DIR__ . '/config/database.php';

/**
 * Database Migration Script
 *
 * This script handles database schema changes for the application.
 * Run this script after updating the application to ensure database compatibility.
 */
class Migrations {
    private $db;
    private $dbType;

    public function __construct() {
        try {
            $database = new Database();
            $this->db = $database->getConnection();
            $this->dbType = $_ENV['DB_TYPE'] ?? 'sqlite';

            // Set PDO to throw exceptions on error
            $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (Exception $e) {
            $this->outputError('Database connection error: ' . $e->getMessage());
            exit(1);
        }
    }

    /**
     * Run all migrations
     */
    public function run() {
        $this->outputInfo('Starting database migrations...');

        try {
            // Run migrations based on database type
            if ($this->dbType === 'mysql') {
                $this->runMySQLMigrations();
            } else {
                $this->runSQLiteMigrations();
            }

            $this->outputSuccess('All migrations completed successfully!');
        } catch (Exception $e) {
            $this->outputError('Migration error: ' . $e->getMessage());
            exit(1);
        }
    }

    /**
     * Run MySQL migrations
     */
    private function runMySQLMigrations() {
        $this->outputInfo('Running MySQL migrations...');

        // Add sort_order column to categories table
        $this->addSortOrderToCategories();

        // Add gamezop_games count to site_settings
        $this->addGamezopGamesCountToStats();

        // Add Google OAuth support
        $this->addGoogleOAuthSupport();

        // Add coins system tables
        $this->addCoinsSystemTables();
    }

    /**
     * Run SQLite migrations
     */
    private function runSQLiteMigrations() {
        $this->outputInfo('Running SQLite migrations...');

        // Add sort_order column to categories table
        $this->addSortOrderToCategories();

        // Add gamezop_games count to site_settings
        $this->addGamezopGamesCountToStats();

        // Add Google OAuth support
        $this->addGoogleOAuthSupport();

        // Add coins system tables
        $this->addCoinsSystemTables();
    }

    /**
     * Add Gamezop games count to site_settings
     */
    private function addGamezopGamesCountToStats() {
        $this->outputInfo('Checking for Gamezop games count in site_settings...');

        try {
            // Check if the column exists in the games table
            $sourceExists = false;

            if ($this->dbType === 'mysql') {
                $stmt = $this->db->query("SHOW COLUMNS FROM games");
                $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
                foreach ($columns as $column) {
                    if ($column['Field'] === 'source') {
                        $sourceExists = true;
                        break;
                    }
                }
            } else {
                $stmt = $this->db->query("PRAGMA table_info(games)");
                $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
                foreach ($columns as $column) {
                    if ($column['name'] === 'source') {
                        $sourceExists = true;
                        break;
                    }
                }
            }

            if (!$sourceExists) {
                $this->outputInfo('The source column does not exist in the games table. Adding it...');

                if ($this->dbType === 'mysql') {
                    $this->db->exec("ALTER TABLE games ADD COLUMN source VARCHAR(255)");
                } else {
                    $this->db->exec("ALTER TABLE games ADD COLUMN source TEXT");
                }

                $this->outputSuccess('Added source column to games table');
            }

            $this->outputSuccess('Gamezop games count check completed');
        } catch (PDOException $e) {
            $this->outputError('Error checking Gamezop games count: ' . $e->getMessage());
        }
    }

    /**
     * Add sort_order column to categories table if it doesn't exist
     */
    private function addSortOrderToCategories() {
        $this->outputInfo('Checking categories table for sort_order column...');

        try {
            // Check if sort_order column exists in categories table
            $sortOrderExists = false;

            if ($this->dbType === 'mysql') {
                $stmt = $this->db->query("SHOW COLUMNS FROM categories");
                $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
                foreach ($columns as $column) {
                    if ($column['Field'] === 'sort_order') {
                        $sortOrderExists = true;
                        break;
                    }
                }
            } else {
                $stmt = $this->db->query("PRAGMA table_info(categories)");
                $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
                foreach ($columns as $column) {
                    if ($column['name'] === 'sort_order') {
                        $sortOrderExists = true;
                        break;
                    }
                }
            }

            if (!$sortOrderExists) {
                $this->outputInfo('Adding sort_order column to categories table...');

                if ($this->dbType === 'mysql') {
                    $this->db->exec("ALTER TABLE categories ADD COLUMN sort_order INT DEFAULT 0");
                } else {
                    $this->db->exec("ALTER TABLE categories ADD COLUMN sort_order INTEGER DEFAULT 0");
                }

                $this->outputSuccess('Added sort_order column to categories table');
            } else {
                $this->outputInfo('sort_order column already exists in categories table');
            }

            $this->outputSuccess('Categories table sort_order check completed');
        } catch (PDOException $e) {
            $this->outputError('Error checking/adding sort_order column to categories table: ' . $e->getMessage());
        }
    }

    /**
     * Output info message
     */
    private function outputInfo($message) {
        echo "\033[0;34m[INFO]\033[0m $message\n";
    }

    /**
     * Output success message
     */
    private function outputSuccess($message) {
        echo "\033[0;32m[SUCCESS]\033[0m $message\n";
    }

    /**
     * Add Google OAuth support
     */
    private function addGoogleOAuthSupport() {
        $this->outputInfo('Adding Google OAuth support...');

        try {
            // Check if google_id column exists in users table
            $googleIdExists = false;

            if ($this->dbType === 'mysql') {
                $stmt = $this->db->query("SHOW COLUMNS FROM users");
                $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
                foreach ($columns as $column) {
                    if ($column['Field'] === 'google_id') {
                        $googleIdExists = true;
                        break;
                    }
                }
            } else {
                $stmt = $this->db->query("PRAGMA table_info(users)");
                $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
                foreach ($columns as $column) {
                    if ($column['name'] === 'google_id') {
                        $googleIdExists = true;
                        break;
                    }
                }
            }

            if (!$googleIdExists) {
                $this->outputInfo('Adding google_id column to users table...');

                if ($this->dbType === 'mysql') {
                    $this->db->exec("ALTER TABLE users ADD COLUMN google_id VARCHAR(255)");
                    // Add unique index in a separate statement
                    $this->db->exec("CREATE UNIQUE INDEX idx_users_google_id ON users(google_id)");
                } else {
                    // SQLite doesn't support adding UNIQUE constraint in ALTER TABLE
                    $this->db->exec("ALTER TABLE users ADD COLUMN google_id TEXT");

                    // Create a new table with the UNIQUE constraint
                    $this->db->exec("CREATE TABLE IF NOT EXISTS users_new (
                        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,
                        google_id TEXT UNIQUE,
                        created_at TIMESTAMP,
                        updated_at TIMESTAMP
                    )");

                    // Copy data from the old table to the new one
                    $this->db->exec("INSERT INTO users_new SELECT * FROM users");

                    // Drop the old table
                    $this->db->exec("DROP TABLE users");

                    // Rename the new table to the original name
                    $this->db->exec("ALTER TABLE users_new RENAME TO users");
                }

                $this->outputSuccess('Added google_id column to users table');
            }

            // Check if oauth_providers table exists
            $oauthTableExists = false;

            if ($this->dbType === 'mysql') {
                $stmt = $this->db->query("SHOW TABLES LIKE 'oauth_providers'");
                $oauthTableExists = $stmt->rowCount() > 0;
            } else {
                $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='oauth_providers'");
                $oauthTableExists = $stmt->fetchColumn() !== false;
            }

            if (!$oauthTableExists) {
                $this->outputInfo('Creating oauth_providers table...');

                if ($this->dbType === 'mysql') {
                    $this->db->exec("CREATE TABLE oauth_providers (
                        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                        provider VARCHAR(50) NOT NULL,
                        provider_id VARCHAR(255) NOT NULL,
                        user_id INT UNSIGNED NOT NULL,
                        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(provider, provider_id)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
                } else {
                    $this->db->exec("CREATE TABLE oauth_providers (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        provider TEXT NOT NULL,
                        provider_id TEXT NOT NULL,
                        user_id INTEGER NOT NULL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                        UNIQUE(provider, provider_id)
                    )");
                }

                $this->outputSuccess('Created oauth_providers table');
            }

            $this->outputSuccess('Google OAuth support added successfully');
        } catch (PDOException $e) {
            $this->outputError('Error adding Google OAuth support: ' . $e->getMessage());
        }
    }

    /**
     * Add coins system tables
     */
    private function addCoinsSystemTables() {
        $this->outputInfo('Adding coins system tables...');

        try {
            $currentTimestamp = date('Y-m-d H:i:s');

            // Check and create user_coins table
            $userCoinsExists = false;
            if ($this->dbType === 'mysql') {
                $stmt = $this->db->query("SHOW TABLES LIKE 'user_coins'");
                $userCoinsExists = $stmt->rowCount() > 0;
            } else {
                $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='user_coins'");
                $userCoinsExists = $stmt->fetchColumn() !== false;
            }

            if (!$userCoinsExists) {
                $this->outputInfo('Creating user_coins table...');

                if ($this->dbType === 'mysql') {
                    $this->db->exec("CREATE TABLE user_coins (
                        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                        user_id INT UNSIGNED NOT NULL,
                        balance INT NOT NULL DEFAULT 0,
                        total_earned INT NOT NULL 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");
                } else {
                    $this->db->exec("CREATE TABLE user_coins (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        user_id INTEGER NOT NULL,
                        balance INTEGER NOT NULL DEFAULT 0,
                        total_earned INTEGER NOT NULL DEFAULT 0,
                        created_at TIMESTAMP,
                        updated_at TIMESTAMP,
                        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                        UNIQUE(user_id)
                    )");
                }

                $this->outputSuccess('Created user_coins table');
            }

            // Check and create coin_transactions table
            $coinTransactionsExists = false;
            if ($this->dbType === 'mysql') {
                $stmt = $this->db->query("SHOW TABLES LIKE 'coin_transactions'");
                $coinTransactionsExists = $stmt->rowCount() > 0;
            } else {
                $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='coin_transactions'");
                $coinTransactionsExists = $stmt->fetchColumn() !== false;
            }

            if (!$coinTransactionsExists) {
                $this->outputInfo('Creating coin_transactions table...');

                if ($this->dbType === 'mysql') {
                    $this->db->exec("CREATE TABLE 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");
                } else {
                    $this->db->exec("CREATE TABLE 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
                    )");
                }

                $this->outputSuccess('Created coin_transactions table');
            }

            // Check and create withdrawal_requests table
            $withdrawalRequestsExists = false;
            if ($this->dbType === 'mysql') {
                $stmt = $this->db->query("SHOW TABLES LIKE 'withdrawal_requests'");
                $withdrawalRequestsExists = $stmt->rowCount() > 0;
            } else {
                $stmt = $this->db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='withdrawal_requests'");
                $withdrawalRequestsExists = $stmt->fetchColumn() !== false;
            }

            if (!$withdrawalRequestsExists) {
                $this->outputInfo('Creating withdrawal_requests table...');

                if ($this->dbType === 'mysql') {
                    $this->db->exec("CREATE TABLE 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(255),
                        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");
                } else {
                    $this->db->exec("CREATE TABLE 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
                    )");
                }

                $this->outputSuccess('Created withdrawal_requests table');
            } else {
                // Check if existing withdrawal_requests table has the amount column
                $this->outputInfo('Checking withdrawal_requests table structure...');
                
                $hasAmountColumn = false;
                if ($this->dbType === 'mysql') {
                    $stmt = $this->db->query("SHOW COLUMNS FROM withdrawal_requests");
                    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
                    foreach ($columns as $column) {
                        if ($column['Field'] === 'amount') {
                            $hasAmountColumn = true;
                            break;
                        }
                    }
                } else {
                    $stmt = $this->db->query("PRAGMA table_info(withdrawal_requests)");
                    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
                    foreach ($columns as $column) {
                        if ($column['name'] === 'amount') {
                            $hasAmountColumn = true;
                            break;
                        }
                    }
                }

                if (!$hasAmountColumn) {
                    $this->outputInfo('Adding missing amount column to withdrawal_requests table...');
                    
                    if ($this->dbType === 'mysql') {
                        $this->db->exec("ALTER TABLE withdrawal_requests ADD COLUMN amount INT NOT NULL DEFAULT 0");
                    } else {
                        $this->db->exec("ALTER TABLE withdrawal_requests ADD COLUMN amount INTEGER NOT NULL DEFAULT 0");
                    }
                    
                    $this->outputSuccess('Added amount column to withdrawal_requests table');
                }
            }

            $this->outputSuccess('Coins system tables added successfully');
        } catch (PDOException $e) {
            $this->outputError('Error adding coins system tables: ' . $e->getMessage());
        }
    }

    /**
     * Output error message
     */
    private function outputError($message) {
        echo "\033[0;31m[ERROR]\033[0m $message\n";
    }
}

// Run migrations
$migrations = new Migrations();
$migrations->run();

echo "\nMigrations completed. You can now use the application.\n";
