<?php
/**
 * add.php — Database Migrations for Global Multi-Country Support
 * Run this file once to update database schema and insert static countries metadata.
 */
header('Content-Type: text/plain');

require_once __DIR__ . '/config.php';

if (!isset($pdo)) {
    die("Error: PDO connection not set in config.php\n");
}

try {
    echo "Starting database migrations...\n";

    // 1. Create countries table
    echo "Creating 'countries' table...\n";
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS `countries` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `name` VARCHAR(100) NOT NULL,
            `country_code` VARCHAR(15) NOT NULL UNIQUE,
            `currency` VARCHAR(10) NOT NULL,
            `currency_symbol` VARCHAR(10) NOT NULL,
            `exchange_rate` DECIMAL(12, 6) NOT NULL DEFAULT 1.000000,
            `flag` VARCHAR(10) NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ");

    // 2. Insert default countries
    echo "Inserting country defaults (Pakistan, India, Indonesia, African Countries, Global)...\n";
    $stmt = $pdo->prepare("
        INSERT INTO `countries` (`name`, `country_code`, `currency`, `currency_symbol`, `exchange_rate`, `flag`)
        VALUES (?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE 
            `currency` = VALUES(`currency`),
            `currency_symbol` = VALUES(`currency_symbol`),
            `exchange_rate` = VALUES(`exchange_rate`),
            `flag` = VALUES(`flag`)
    ");
    $stmt->execute(['Pakistan', '+92', 'PKR', 'Rs', 1.000000, 'pk']);
    $stmt->execute(['India', '+91', 'INR', '₹', 0.340000, 'in']);
    $stmt->execute(['Indonesia', '+62', 'USD', '$', 0.003600, 'id']);
    $stmt->execute(['Nigeria', '+234', 'USD', '$', 0.003600, 'ng']);
    $stmt->execute(['South Africa', '+27', 'USD', '$', 0.003600, 'za']);
    $stmt->execute(['Kenya', '+254', 'USD', '$', 0.003600, 'ke']);
    $stmt->execute(['Egypt', '+20', 'USD', '$', 0.003600, 'eg']);
    $stmt->execute(['Ghana', '+233', 'USD', '$', 0.003600, 'gh']);
    $stmt->execute(['Global', 'other', 'USD', '$', 0.003600, 'us']);

    // 3. Alter users table
    echo "Altering 'users' table (adding country_code and currency columns)...\n";
    
    // Check if column already exists to prevent query errors
    $cols = $pdo->query("DESCRIBE `users`")->fetchAll(PDO::FETCH_COLUMN);
    
    if (!in_array('country_code', $cols)) {
        $pdo->exec("ALTER TABLE `users` ADD COLUMN `country_code` VARCHAR(15) DEFAULT '+92' AFTER `number`");
        echo "Added 'country_code' column.\n";
    }
    
    if (!in_array('currency', $cols)) {
        $pdo->exec("ALTER TABLE `users` ADD COLUMN `currency` VARCHAR(10) DEFAULT 'PKR' AFTER `country_code`");
        echo "Added 'currency' column.\n";
    }

    // 4. Alter withdrawal_accounts table
    echo "Altering 'withdrawal_accounts' table (adding upi_id and usdt_address columns)...\n";
    $cols_wa = $pdo->query("DESCRIBE `withdrawal_accounts`")->fetchAll(PDO::FETCH_COLUMN);

    if (!in_array('upi_id', $cols_wa)) {
        $pdo->exec("ALTER TABLE `withdrawal_accounts` ADD COLUMN `upi_id` VARCHAR(255) DEFAULT NULL");
        echo "Added 'upi_id' column.\n";
    }

    if (!in_array('usdt_address', $cols_wa)) {
        $pdo->exec("ALTER TABLE `withdrawal_accounts` ADD COLUMN `usdt_address` VARCHAR(255) DEFAULT NULL");
        echo "Added 'usdt_address' column.\n";
    }

    // Add unique index constraints on upi_id and usdt_address if not already set
    echo "Applying unique constraints to upi_id and usdt_address...\n";
    
    // Drop existing indexes to prevent duplicates before adding them
    try { $pdo->exec("ALTER TABLE `withdrawal_accounts` DROP INDEX `idx_wa_upi_id`"); } catch(Exception $e){}
    try { $pdo->exec("ALTER TABLE `withdrawal_accounts` DROP INDEX `idx_wa_usdt_address`"); } catch(Exception $e){}

    $pdo->exec("ALTER TABLE `withdrawal_accounts` ADD UNIQUE INDEX `idx_wa_upi_id` (`upi_id`)");
    $pdo->exec("ALTER TABLE `withdrawal_accounts` ADD UNIQUE INDEX `idx_wa_usdt_address` (`usdt_address`)");
    echo "Successfully applied unique constraint indexes.\n";

    // 5. Backfill existing data
    echo "Backfilling existing users to +92 and PKR defaults...\n";
    $pdo->exec("UPDATE `users` SET `country_code` = '+92', `currency` = 'PKR' WHERE `country_code` IS NULL OR `country_code` = ''");

    echo "\nMigrations completed successfully! You can safely delete add.php now.\n";

} catch (Exception $e) {
    die("\nMigration Error: " . $e->getMessage() . "\n");
}
?>
