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

class Database {
    private static $instance = null;
    private $pdo;
    private function __construct() {
        try {
            $this->pdo = new PDO(
                "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=utf8mb4",
                DB_USER, DB_PASS,
                [
                    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES   => false,
                ]
            );
        } catch (PDOException $e) {
            http_response_code(500);
            die(json_encode(['success'=>false,'error'=>'Database connection failed.']));
        }
    }
    public static function getInstance(): self {
        if (!self::$instance) self::$instance = new self();
        return self::$instance;
    }
    public function getConnection(): PDO { return $this->pdo; }
}

function db(): PDO { return Database::getInstance()->getConnection(); }

// Check if a column exists — MySQL 5.x compatible
function colExists(PDO $pdo, string $table, string $col): bool {
    return (bool)$pdo->query("SHOW COLUMNS FROM `$table` LIKE '$col'")->fetch();
}

function initDB(): void {
    $pdo = db();

    // Each table in its own exec() — PDO only runs one statement per call
    $pdo->exec("CREATE TABLE IF NOT EXISTS users (
        id             INT AUTO_INCREMENT PRIMARY KEY,
        username       VARCHAR(50) UNIQUE NOT NULL,
        password       VARCHAR(255) NOT NULL,
        alias          VARCHAR(100) NOT NULL,
        email          VARCHAR(150) UNIQUE,
        email_verified TINYINT(1) DEFAULT 0,
        tokens         DECIMAL(10,2) DEFAULT 0,
        is_admin       TINYINT(1) DEFAULT 0,
        status         ENUM('active','suspended') DEFAULT 'active',
        created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
        last_login     DATETIME
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS pending_registrations (
        id          INT AUTO_INCREMENT PRIMARY KEY,
        username    VARCHAR(50) NOT NULL,
        password    VARCHAR(255) NOT NULL,
        alias       VARCHAR(100) NOT NULL,
        email       VARCHAR(150) NOT NULL,
        token       VARCHAR(64) UNIQUE NOT NULL,
        referred_by INT DEFAULT NULL,
        expires_at  DATETIME NOT NULL,
        created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS token_purchases (
        id                INT AUTO_INCREMENT PRIMARY KEY,
        user_id           INT NOT NULL,
        tokens            INT NOT NULL,
        amount_cents      INT NOT NULL,
        payment_method    VARCHAR(20) DEFAULT 'card',
        square_payment_id VARCHAR(255),
        platform_id       VARCHAR(50),
        game_alias        VARCHAR(100),
        player_name       VARCHAR(100),
        billing_name      VARCHAR(160),
        billing_address   VARCHAR(200),
        billing_city      VARCHAR(80),
        billing_state     VARCHAR(2),
        billing_zip       VARCHAR(10),
        billing_email     VARCHAR(150),
        is_custom         TINYINT(1) DEFAULT 0,
        failure_reason    TEXT,
        card_brand        VARCHAR(30),
        card_last4        VARCHAR(4),
        receipt_url       VARCHAR(512),
        status            ENUM('pending','completed','failed') DEFAULT 'pending',
        admin_note        TEXT,
        created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS cashout_requests (
        id          INT AUTO_INCREMENT PRIMARY KEY,
        user_id     INT NOT NULL,
        platform_id VARCHAR(50) NOT NULL,
        alias       VARCHAR(100) NOT NULL,
        tokens      DECIMAL(10,2) NOT NULL,
        status      ENUM('pending','approved','rejected') DEFAULT 'pending',
        admin_note  TEXT,
        created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
        resolved_at DATETIME,
        FOREIGN KEY (user_id) REFERENCES users(id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS platform_accounts (
        id              INT AUTO_INCREMENT PRIMARY KEY,
        user_id         INT NOT NULL,
        platform_slug   VARCHAR(50) NOT NULL,
        requested_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
        status          ENUM('pending','approved','denied','deleted') DEFAULT 'pending',
        platform_username VARCHAR(100),
        platform_password VARCHAR(200),
        admin_note      VARCHAR(300),
        resolved_at     DATETIME,
        admin_id        INT,
        UNIQUE KEY uq_user_platform (user_id, platform_slug),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS admin_payout_settings (
        id              INT AUTO_INCREMENT PRIMARY KEY,
        method_key      VARCHAR(50) UNIQUE NOT NULL,
        label           VARCHAR(100) NOT NULL,
        method_type     ENUM('manual','square_gift_card') DEFAULT 'manual',
        is_enabled      TINYINT(1) DEFAULT 1,
        handle          VARCHAR(200),
        instructions    TEXT,
        sort_order      INT DEFAULT 0
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS cashout_transactions (
        id              INT AUTO_INCREMENT PRIMARY KEY,
        cashout_id      INT NOT NULL,
        admin_id        INT NOT NULL,
        payout_method   VARCHAR(50) NOT NULL,
        payout_type     ENUM('manual','square_gift_card') DEFAULT 'manual',
        amount_cents    INT NOT NULL,
        square_txn_id   VARCHAR(200),
        gift_card_gan   VARCHAR(100),
        gift_card_balance INT,
        note            TEXT,
        status          ENUM('pending','completed','failed') DEFAULT 'pending',
        created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (cashout_id) REFERENCES cashout_requests(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS activity_log (
        id            INT AUTO_INCREMENT PRIMARY KEY,
        user_id       INT,
        admin_id      INT,
        action        VARCHAR(120) NOT NULL,
        category      VARCHAR(40) DEFAULT 'general',
        entity_type   VARCHAR(40),
        entity_id     INT,
        detail        TEXT,
        old_value     TEXT,
        new_value     TEXT,
        ip            VARCHAR(45),
        user_agent    VARCHAR(300),
        page          VARCHAR(200),
        session_id    VARCHAR(64),
        severity      ENUM('info','warning','critical') DEFAULT 'info',
        created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_created  (created_at),
        INDEX idx_user     (user_id),
        INDEX idx_admin    (admin_id),
        INDEX idx_category (category),
        INDEX idx_severity (severity)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS broadcasts (
        id           INT AUTO_INCREMENT PRIMARY KEY,
        admin_id     INT NOT NULL,
        subject      VARCHAR(200) NOT NULL,
        message      TEXT NOT NULL,
        target       ENUM('all','verified','unverified','admins') DEFAULT 'all',
        sent_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (admin_id) REFERENCES users(id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS broadcast_reads (
        id           INT AUTO_INCREMENT PRIMARY KEY,
        broadcast_id INT NOT NULL,
        user_id      INT NOT NULL,
        read_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_br (broadcast_id, user_id),
        FOREIGN KEY (broadcast_id) REFERENCES broadcasts(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id)      REFERENCES users(id)      ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS broadcast_replies (
        id           INT AUTO_INCREMENT PRIMARY KEY,
        broadcast_id INT NOT NULL,
        user_id      INT NOT NULL,
        message      TEXT NOT NULL,
        created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (broadcast_id) REFERENCES broadcasts(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id)      REFERENCES users(id)      ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS cashout_method_types (
        id          INT AUTO_INCREMENT PRIMARY KEY,
        slug        VARCHAR(50) UNIQUE NOT NULL,
        label       VARCHAR(100) NOT NULL,
        icon        VARCHAR(10) DEFAULT '💰',
        description VARCHAR(200),
        is_active   TINYINT(1) DEFAULT 1,
        sort_order  INT DEFAULT 0,
        created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS payout_methods (
        id              INT AUTO_INCREMENT PRIMARY KEY,
        user_id         INT NOT NULL,
        method_type     VARCHAR(50) NOT NULL,
        label           VARCHAR(100) NOT NULL,
        account_handle  VARCHAR(200) NOT NULL,
        is_default      TINYINT(1) DEFAULT 0,
        created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // Add payout fields to cashout_requests if not present
    try {
        $cols = array_column($pdo->query("SHOW COLUMNS FROM cashout_requests")->fetchAll(), 'Field');
        if (!in_array('payout_method_type', $cols)) {
            $pdo->exec("ALTER TABLE cashout_requests ADD COLUMN payout_method_type VARCHAR(50) AFTER alias");
            $pdo->exec("ALTER TABLE cashout_requests ADD COLUMN payout_handle VARCHAR(200) AFTER payout_method_type");
        }
        if (!in_array('sent_note', $cols)) {
            $pdo->exec("ALTER TABLE cashout_requests ADD COLUMN sent_note TEXT AFTER admin_note");
        }
        $pdo->exec("ALTER TABLE cashout_requests MODIFY COLUMN status ENUM('pending','approved','sent','rejected','deleted') DEFAULT 'pending'");
    } catch (Exception $e) { /* ignore — columns may already exist */ }

    $pdo->exec("CREATE TABLE IF NOT EXISTS saved_billing (
        id             INT AUTO_INCREMENT PRIMARY KEY,
        user_id        INT UNIQUE NOT NULL,
        first_name     VARCHAR(80),
        last_name      VARCHAR(80),
        email          VARCHAR(150),
        address        VARCHAR(200),
        city           VARCHAR(80),
        state          VARCHAR(2),
        zip            VARCHAR(10),
        card_brand     VARCHAR(30),
        card_last4     VARCHAR(4),
        card_exp_month VARCHAR(2),
        card_exp_year  VARCHAR(4),
        sq_card_id     VARCHAR(255),
        updated_at     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS platforms (
        id           INT AUTO_INCREMENT PRIMARY KEY,
        slug         VARCHAR(50) UNIQUE NOT NULL,
        name         VARCHAR(100) NOT NULL,
        player_url   VARCHAR(500) NOT NULL,
        console_url  VARCHAR(500),
        color        VARCHAR(20) DEFAULT '#f0c040',
        icon_path    VARCHAR(200),
        is_active    TINYINT(1) DEFAULT 1,
        sort_order   INT DEFAULT 0,
        created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS payment_settings (
        id          INT AUTO_INCREMENT PRIMARY KEY,
        method_key  VARCHAR(50) UNIQUE NOT NULL,
        label       VARCHAR(100) NOT NULL,
        is_enabled  TINYINT(1) DEFAULT 1,
        handle      VARCHAR(200),
        instructions TEXT,
        sort_order  INT DEFAULT 0,
        updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS game_aliases (
        id          INT AUTO_INCREMENT PRIMARY KEY,
        user_id     INT NOT NULL,
        platform_slug VARCHAR(50) NOT NULL,
        alias       VARCHAR(100) NOT NULL,
        updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_user_platform (user_id, platform_slug),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    
    // Track whether user has seen the post-verify onboarding
    try {
        if (!colExists($pdo,'users','onboarding_done')) {
            $pdo->exec("ALTER TABLE users ADD COLUMN onboarding_done TINYINT(1) DEFAULT 0");
        }
    } catch (Exception $e) {}

    $pdo->exec("CREATE TABLE IF NOT EXISTS chat_messages (
        id         INT AUTO_INCREMENT PRIMARY KEY,
        user_id    INT NOT NULL,
        sender     ENUM('user','admin') NOT NULL,
        message    TEXT NOT NULL,
        is_read    TINYINT(1) DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // Add missing columns to existing tables — MySQL 5.x compatible (no IF NOT EXISTS)
    $addCols = [
        ['token_purchases', 'billing_name',    "VARCHAR(160)",         'player_name'],
        ['token_purchases', 'billing_address', "VARCHAR(200)",         'billing_name'],
        ['token_purchases', 'billing_city',    "VARCHAR(80)",          'billing_address'],
        ['token_purchases', 'billing_state',   "VARCHAR(2)",           'billing_city'],
        ['token_purchases', 'billing_zip',     "VARCHAR(10)",          'billing_state'],
        ['token_purchases', 'billing_email',   "VARCHAR(150)",         'billing_zip'],
        ['token_purchases', 'is_custom',       "TINYINT(1) DEFAULT 0", 'billing_email'],
        ['token_purchases', 'failure_reason',  "TEXT",                 'is_custom'],
        ['token_purchases', 'card_brand',      "VARCHAR(30)",          'failure_reason'],
        ['token_purchases', 'card_last4',      "VARCHAR(4)",           'card_brand'],
        ['token_purchases', 'receipt_url',     "VARCHAR(512)",         'card_last4'],
        ['token_purchases', 'admin_note',      "TEXT",                 'status'],
        ['users',           'email_verified',  "TINYINT(1) DEFAULT 0", 'email'],
    ];

    foreach ($addCols as [$tbl, $col, $def, $after]) {
        if (!colExists($pdo, $tbl, $col)) {
            try {
                $pdo->exec("ALTER TABLE `$tbl` ADD COLUMN `$col` $def AFTER `$after`");
            } catch (Exception $e) { /* ignore — concurrent init */ }
        }
    }
}

try { initDB(); } catch (Exception $e) { /* already initialised */ }


// Seed admin_payout_settings if empty
try {
    if (db()->query("SELECT COUNT(*) FROM admin_payout_settings")->fetchColumn() == 0) {
        $seeds = [
            ['venmo',         'Venmo',         'manual',           1, '@your-venmo',  'Send via Venmo app'],
            ['cashapp',       'Cash App',       'manual',           1, '$yourcashtag', 'Send via Cash App'],
            ['zelle',         'Zelle',          'manual',           1, 'your@email',   'Send via Zelle'],
            ['chime',         'Chime',          'manual',           1, '',             'Send via Chime'],
            ['square_gift',   'Square Gift Card','square_gift_card',1, '',             'Instant Square gift card — player redeems anywhere Square is accepted'],
        ];
        $st = db()->prepare("INSERT IGNORE INTO admin_payout_settings (method_key,label,method_type,is_enabled,handle,instructions,sort_order) VALUES (?,?,?,?,?,?,?)");
        foreach ($seeds as $i => $s) { $st->execute([$s[0],$s[1],$s[2],$s[3],$s[4],$s[5],$i]); }
    }
} catch(Exception $e) {}

// ─── REFERRAL TABLES ──────────────────────────────────────
try {
    db()->exec("CREATE TABLE IF NOT EXISTS referral_tiers (
        id              INT AUTO_INCREMENT PRIMARY KEY,
        name            VARCHAR(100) NOT NULL,
        min_referrals   INT NOT NULL DEFAULT 1,
        tokens_per_ref  DECIMAL(10,2) NOT NULL DEFAULT 10,
        bonus_tokens    DECIMAL(10,2) NOT NULL DEFAULT 0,
        description     VARCHAR(300),
        is_active       TINYINT(1) DEFAULT 1,
        sort_order      INT DEFAULT 0,
        created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    db()->exec("CREATE TABLE IF NOT EXISTS referrals (
        id              INT AUTO_INCREMENT PRIMARY KEY,
        referrer_id     INT NOT NULL,
        referred_id     INT NOT NULL UNIQUE,
        tier_id         INT,
        status          ENUM('pending','verified','denied','deleted') DEFAULT 'pending',
        tokens_awarded  DECIMAL(10,2) DEFAULT 0,
        admin_id        INT,
        admin_note      VARCHAR(300),
        created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
        resolved_at     DATETIME,
        FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    db()->exec("CREATE TABLE IF NOT EXISTS referral_social_shares (
        id              INT AUTO_INCREMENT PRIMARY KEY,
        user_id         INT NOT NULL,
        platform        VARCHAR(50) NOT NULL,
        bonus_tokens    DECIMAL(10,2) DEFAULT 0,
        status          ENUM('pending','approved','denied') DEFAULT 'pending',
        admin_id        INT,
        created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
        resolved_at     DATETIME,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
} catch(Exception $e) {}

// Seed default referral tiers
try {
    if (db()->query("SELECT COUNT(*) FROM referral_tiers")->fetchColumn() == 0) {
        $seeds = [
            ['Bronze Referrer',  1,  5, 0,  'Earn 5 tokens for each verified referral',              1, 0],
            ['Silver Referrer',  5,  8, 25, 'Earn 8 tokens per referral + 25 bonus at 5 referrals',  1, 1],
            ['Gold Referrer',    10, 10, 100,'Earn 10 tokens per referral + 100 bonus at 10 referrals',1, 2],
            ['Elite Referrer',   25, 15, 250,'Earn 15 tokens per referral + 250 bonus at 25 referrals',1, 3],
        ];
        $st = db()->prepare("INSERT INTO referral_tiers (name,min_referrals,tokens_per_ref,bonus_tokens,description,is_active,sort_order) VALUES (?,?,?,?,?,?,?)");
        foreach ($seeds as $s) $st->execute($s);
    }
} catch(Exception $e) {}

// Add referral_code to users if missing
try {
    $cols = array_column(db()->query("SHOW COLUMNS FROM users")->fetchAll(), 'Field');
    if (!in_array('referral_code', $cols)) {
        db()->exec("ALTER TABLE users ADD COLUMN referral_code VARCHAR(20) UNIQUE");
        // Generate codes for existing users
        $users = db()->query("SELECT id FROM users WHERE referral_code IS NULL")->fetchAll();
        $upd = db()->prepare("UPDATE users SET referral_code=? WHERE id=?");
        foreach ($users as $u) {
            $code = strtoupper(substr(md5($u['id'].uniqid()),0,8));
            $upd->execute([$code, $u['id']]);
        }
    }
} catch(Exception $e) {}

// Add referred_by to users if missing  
try {
    $cols = array_column(db()->query("SHOW COLUMNS FROM users")->fetchAll(), 'Field');
    if (!in_array('referred_by', $cols)) {
        db()->exec("ALTER TABLE users ADD COLUMN referred_by INT DEFAULT NULL");
    }
} catch(Exception $e) {}

// Add card payment_settings row if missing
try {
    $cardCount = db()->query("SELECT COUNT(*) FROM payment_settings WHERE method_key='card'")->fetchColumn();
    if ($cardCount == 0) {
        db()->exec("INSERT IGNORE INTO payment_settings (method_key,label,handle,instructions,is_enabled,sort_order) VALUES ('card','Credit / Debit Card','','Processed via Square',1,-1)");
    }
} catch(Exception $e) {}

// Expand activity_log if columns missing
try {
    $alCols = array_column(db()->query("SHOW COLUMNS FROM activity_log")->fetchAll(), 'Field');
    $alAdd = [
        'category'   => "ALTER TABLE activity_log ADD COLUMN category VARCHAR(40) DEFAULT 'general' AFTER action",
        'old_value'  => "ALTER TABLE activity_log ADD COLUMN old_value TEXT AFTER detail",
        'new_value'  => "ALTER TABLE activity_log ADD COLUMN new_value TEXT AFTER old_value",
        'user_agent' => "ALTER TABLE activity_log ADD COLUMN user_agent VARCHAR(300) AFTER ip",
        'page'       => "ALTER TABLE activity_log ADD COLUMN page VARCHAR(200) AFTER user_agent",
        'session_id' => "ALTER TABLE activity_log ADD COLUMN session_id VARCHAR(64) AFTER page",
        'severity'   => "ALTER TABLE activity_log ADD COLUMN severity ENUM('info','warning','critical') DEFAULT 'info' AFTER session_id",
    ];
    foreach ($alAdd as $col => $sql) {
        if (!in_array($col, $alCols)) db()->exec($sql);
    }
    // Change detail to TEXT if it's VARCHAR
    $detailType = '';
    foreach (db()->query("SHOW COLUMNS FROM activity_log")->fetchAll() as $col) {
        if ($col['Field'] === 'detail') $detailType = $col['Type'];
    }
    if (stripos($detailType, 'varchar') !== false) {
        db()->exec("ALTER TABLE activity_log MODIFY COLUMN detail TEXT");
    }
} catch(Exception $e) {}

// Add platform_onboarding_done to users if not exists
try {
    $cols = array_column(db()->query("SHOW COLUMNS FROM users")->fetchAll(), 'Field');
    if (!in_array('platform_onboarding_done', $cols)) {
        db()->exec("ALTER TABLE users ADD COLUMN platform_onboarding_done TINYINT(1) DEFAULT 0");
    }
} catch(Exception $e){}

// App version table
try {
    db()->exec("CREATE TABLE IF NOT EXISTS app_version (
        id         INT AUTO_INCREMENT PRIMARY KEY,
        version    VARCHAR(20) NOT NULL,
        notes      TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    // Seed initial version if empty
    if (db()->query("SELECT COUNT(*) FROM app_version")->fetchColumn() == 0) {
        db()->exec("INSERT INTO app_version (version, notes) VALUES ('1.0.0', 'Initial release')");
    }
} catch(Exception $e) {}

// Seed cashout_method_types if empty
try {
    $cmtCount = db()->query("SELECT COUNT(*) FROM cashout_method_types")->fetchColumn();
    if ($cmtCount == 0) {
        $types = [
            ['venmo',   'Venmo',         '💙', 'Send via Venmo username',      1, 0],
            ['cashapp', 'Cash App',      '💚', 'Send via Cash App $cashtag',   1, 1],
            ['zelle',   'Zelle',         '💜', 'Send via Zelle phone or email', 1, 2],
            ['chime',   'Chime',         '🟢', 'Send via Chime account',       1, 3],
            ['bank',    'Bank Transfer', '🏦', 'Direct bank transfer',         1, 4],
            ['other',   'Other',         '💰', 'Other payment method',          1, 5],
        ];
        $stmt = db()->prepare("INSERT IGNORE INTO cashout_method_types (slug,label,icon,description,is_active,sort_order) VALUES (?,?,?,?,?,?)");
        foreach ($types as $t) $stmt->execute($t);
    }
} catch (Exception $e) { /* ignore */ }

// Seed payment_settings from config if empty
try {
    $pmtCount = db()->query("SELECT COUNT(*) FROM payment_settings")->fetchColumn();
    if ($pmtCount == 0) {
        $methods = [
            ['card',    'Credit / Debit Card', '', 'Processed via Square', 1, -1],
            ['venmo',   'Venmo',    PAY_VENMO,   'Send payment via Venmo',   1, 0],
            ['chime',   'Chime',    PAY_CHIME,   'Send payment via Chime',   1, 1],
            ['cashapp', 'Cash App', PAY_CASHAPP, 'Send payment via Cash App',1, 2],
            ['zelle',   'Zelle',    PAY_ZELLE,   'Send payment via Zelle',   1, 3],
        ];
        $stmt = db()->prepare("INSERT IGNORE INTO payment_settings (method_key,label,handle,instructions,is_enabled,sort_order) VALUES (?,?,?,?,1,?)");
        foreach ($methods as $m) {
            $stmt->execute([$m[0],$m[1],$m[2],$m[3],$m[5]]);
        }
    }
} catch (Exception $e) { /* ignore */ }

// Seed platforms table from config if empty
try {
    $count = db()->query("SELECT COUNT(*) FROM platforms")->fetchColumn();
    if ($count == 0) {
        $platforms = json_decode(PLATFORMS, true);
        $stmt = db()->prepare("INSERT IGNORE INTO platforms (slug,name,player_url,color,sort_order) VALUES (?,?,?,?,?)");
        foreach ($platforms as $i => $p) {
            $stmt->execute([$p['id'], $p['name'], $p['url'], $p['color'], $i]);
        }
    }
} catch (Exception $e) { /* ignore */ }

// Always ensure admin accounts are email-verified
try {
    if (colExists(db(), 'users', 'email_verified')) {
        db()->exec("UPDATE users SET email_verified=1 WHERE is_admin=1 AND email_verified=0");
    }
} catch (Exception $e) { /* ignore */ }