<?php
declare(strict_types=1);

function mocker_db_path(): string
{
    return dirname(__DIR__) . '/data/users.sqlite';
}

function mocker_db(): PDO
{
    static $pdo = null;
    if ($pdo instanceof PDO) {
        mocker_migrate($pdo);
        return $pdo;
    }
    $path = mocker_db_path();
    $dir = dirname($path);
    if (!is_dir($dir)) {
        if (!@mkdir($dir, 0755, true) && !is_dir($dir)) {
            throw new RuntimeException('data klasörü oluşturulamadı.');
        }
    }
    
    $conn = new PDO('sqlite:' . $path, null, null, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
    $conn->exec('PRAGMA foreign_keys = ON');
    mocker_migrate($conn);
    mocker_ensure_demo_user($conn);
    $pdo = $conn;
    return $pdo;
}

function mocker_should_skip_demo_user(): bool
{
    $marker = dirname(mocker_db_path()) . DIRECTORY_SEPARATOR . '.skip_demo_user';

    return is_file($marker);
}

function mocker_ensure_demo_user(PDO $pdo): void
{
    if (mocker_should_skip_demo_user()) {
        return;
    }
    $username = 'testuser';
    $email = 'test@mocker.local';
    $st = $pdo->prepare('SELECT 1 FROM users WHERE username = :u OR email = :e LIMIT 1');
    $st->execute([':u' => $username, ':e' => $email]);
    if ($st->fetch()) {
        return;
    }
    $password = 'Test1234!';
    $hash = password_hash($password, PASSWORD_DEFAULT);
    $now = time();
    $coins = 150;
    try {
        $ins = $pdo->prepare(
            'INSERT INTO users (username, email, password_hash, coins, created_at, last_login_at, last_seen_at, register_ip, last_login_ip) VALUES (:u, :e, :p, :c, :t, :t, :t2, NULL, NULL)'
        );
        $ins->execute([
            ':u' => $username,
            ':e' => $email,
            ':p' => $hash,
            ':c' => $coins,
            ':t' => $now,
            ':t2' => $now,
        ]);
    } catch (PDOException $e) {
        if ($e->getCode() === '23000' || str_contains($e->getMessage(), 'UNIQUE')) {
            return;
        }
        throw $e;
    }
}

function mocker_migrate(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL COLLATE NOCASE,
            email TEXT NOT NULL COLLATE NOCASE,
            password_hash TEXT NOT NULL,
            coins INTEGER NOT NULL DEFAULT 0,
            created_at INTEGER NOT NULL
        )'
    );
    $pdo->exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_users_username ON users(username)');
    $pdo->exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email)');

    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS purchases (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            item_id TEXT NOT NULL,
            item_title TEXT NOT NULL,
            price INTEGER NOT NULL,
            status TEXT NOT NULL DEFAULT \'pending\',
            created_at INTEGER NOT NULL,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )'
    );
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_purchases_user ON purchases(user_id, created_at)');
    mocker_migrate_purchases_extra_columns($pdo);
    mocker_migrate_wheel_spins($pdo);
    mocker_migrate_complaints_table($pdo);
    mocker_migrate_affiliate_requests_table($pdo);
    mocker_migrate_user_sponsor_accounts($pdo);
    mocker_migrate_users_extra_columns($pdo);
    mocker_migrate_user_crypto_wallets($pdo);
    mocker_migrate_admin_operators($pdo);
    mocker_migrate_admin_master_twofa($pdo);
    mocker_migrate_telegram_users($pdo);
    mocker_migrate_telegram_bot_subscribers($pdo);
    mocker_migrate_telegram_auth_pending($pdo);
    mocker_migrate_blackjack_state($pdo);
    mocker_migrate_blackjack_rounds($pdo);
    mocker_migrate_slot_rounds($pdo);
    mocker_migrate_roulette_rounds($pdo);
    mocker_migrate_promo_codes($pdo);
    mocker_migrate_admin_audit_log($pdo);
    mocker_migrate_chat_messages($pdo);
    mocker_migrate_chat_moderation($pdo);
    mocker_migrate_telegram_invites($pdo);
    mocker_migrate_special_odds($pdo);
    require_once __DIR__ . '/ticket_lib.php';
    mocker_migrate_tickets($pdo);
    require_once __DIR__ . '/news_lib.php';
    mocker_migrate_site_news($pdo);
    require_once __DIR__ . '/notifications_lib.php';
    mocker_migrate_site_notifications($pdo);
    require_once __DIR__ . '/click_analytics_lib.php';
    mocker_migrate_site_click_events($pdo);
}

function mocker_migrate_chat_messages(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS chat_messages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            body TEXT NOT NULL,
            created_at INTEGER NOT NULL,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )'
    );
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_chat_messages_user_created ON chat_messages(user_id, created_at)');
}

function mocker_migrate_chat_moderation(PDO $pdo): void
{
    $cols = $pdo->query('PRAGMA table_info(chat_messages)')->fetchAll(PDO::FETCH_ASSOC);
    $names = [];
    foreach ($cols as $c) {
        $names[(string) ($c['name'] ?? '')] = true;
    }
    if (!isset($names['deleted_at'])) {
        $pdo->exec('ALTER TABLE chat_messages ADD COLUMN deleted_at INTEGER');
    }
    if (!isset($names['deleted_by_operator_id'])) {
        $pdo->exec('ALTER TABLE chat_messages ADD COLUMN deleted_by_operator_id INTEGER');
    }
    if (!isset($names['posted_by_operator_id'])) {
        $pdo->exec('ALTER TABLE chat_messages ADD COLUMN posted_by_operator_id INTEGER');
    }

    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS chat_mutes (
            user_id INTEGER PRIMARY KEY,
            muted_until INTEGER NOT NULL,
            created_at INTEGER NOT NULL,
            created_by_operator_id INTEGER,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )'
    );
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_chat_mutes_until ON chat_mutes(muted_until)');

    mocker_chat_system_user_id($pdo);
}

function mocker_chat_system_user_id(PDO $pdo): int
{
    static $cached = null;
    if ($cached !== null) {
        return $cached;
    }
    $st = $pdo->query('SELECT id FROM users WHERE username = \'__chat_system\' COLLATE NOCASE LIMIT 1');
    $row = $st->fetch(PDO::FETCH_ASSOC);
    if ($row) {
        $cached = (int) $row['id'];

        return $cached;
    }
    $now = time();
    $hash = password_hash(bin2hex(random_bytes(24)), PASSWORD_DEFAULT);
    $ins = $pdo->prepare(
        'INSERT INTO users (username, email, password_hash, coins, created_at) VALUES (:u, :e, :h, 0, :t)'
    );
    $ins->execute([
        ':u' => '__chat_system',
        ':e' => '__chat_system@internal.local',
        ':h' => $hash,
        ':t' => $now,
    ]);
    $cached = (int) $pdo->lastInsertId();

    return $cached;
}

function mocker_chat_management_label(): string
{
    $al = strtolower((string) ($_SERVER['HTTP_ACCEPT_LANGUAGE'] ?? ''));
    if (str_starts_with($al, 'en')) {
        return 'Moderator';
    }

    return 'Moderatör';
}

function mocker_chat_row_int(array $row, string ...$keys): int
{
    foreach ($keys as $k) {
        if (array_key_exists($k, $row) && $row[$k] !== null && $row[$k] !== '') {
            return (int) $row[$k];
        }
    }
    foreach ($row as $k => $v) {
        if ($v === null || $v === '') {
            continue;
        }
        $kStr = (string) $k;
        foreach ($keys as $want) {
            if (strcasecmp($kStr, $want) === 0) {
                return (int) $v;
            }
        }
    }

    return 0;
}

function mocker_chat_row_string(array $row, string $key, string $default = ''): string
{
    if (array_key_exists($key, $row) && $row[$key] !== null) {
        return (string) $row[$key];
    }
    foreach ($row as $k => $v) {
        if ($v !== null && strcasecmp((string) $k, $key) === 0) {
            return (string) $v;
        }
    }

    return $default;
}

function mocker_chat_row_nullable_int(array $row, string ...$keys): ?int
{
    foreach ($keys as $k) {
        if (!array_key_exists($k, $row) || $row[$k] === null || $row[$k] === '') {
            continue;
        }

        return (int) $row[$k];
    }
    foreach ($row as $k => $v) {
        if ($v === null || $v === '') {
            continue;
        }
        $kStr = (string) $k;
        foreach ($keys as $want) {
            if (strcasecmp($kStr, $want) === 0) {
                return (int) $v;
            }
        }
    }

    return null;
}

function mocker_chat_display_name(PDO $pdo, array $row): string
{
    $username = (string) ($row['username'] ?? '');
    if (strcasecmp($username, '__chat_system') === 0) {
        $username = '';
    }
    $postedBy = isset($row['posted_by_operator_id']) ? (int) $row['posted_by_operator_id'] : 0;
    if ($postedBy > 0) {
        $st = $pdo->prepare('SELECT display_name, username FROM admin_operators WHERE id = :id LIMIT 1');
        $st->execute([':id' => $postedBy]);
        $op = $st->fetch(PDO::FETCH_ASSOC);
        if ($op) {
            $dn = trim((string) ($op['display_name'] ?? ''));
            if ($dn !== '') {
                return $dn;
            }
            $ou = trim((string) ($op['username'] ?? ''));
            if ($ou !== '') {
                return $ou;
            }
        }

        return mocker_chat_management_label();
    }
    $uid = isset($row['user_id']) ? (int) $row['user_id'] : 0;
    $sid = mocker_chat_system_user_id($pdo);
    if ($uid > 0 && $uid === $sid) {
        return mocker_chat_management_label();
    }

    return $username !== '' ? $username : '?';
}

function mocker_migrate_admin_audit_log(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS admin_audit_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            created_at INTEGER NOT NULL,
            actor_kind TEXT NOT NULL,
            actor_operator_id INTEGER,
            actor_username TEXT NOT NULL,
            actor_display_name TEXT,
            action TEXT NOT NULL,
            target_user_id INTEGER,
            details_json TEXT NOT NULL,
            ip TEXT
        )'
    );
    $cols = $pdo->query('PRAGMA table_info(admin_audit_log)')->fetchAll(PDO::FETCH_ASSOC);
    $has = [];
    foreach ($cols as $c) {
        $name = isset($c['name']) ? (string) $c['name'] : '';
        if ($name !== '') {
            $has[strtolower($name)] = true;
        }
    }
    if (!isset($has['request_path'])) {
        $pdo->exec("ALTER TABLE admin_audit_log ADD COLUMN request_path TEXT NOT NULL DEFAULT ''");
    }
    if (!isset($has['request_method'])) {
        $pdo->exec("ALTER TABLE admin_audit_log ADD COLUMN request_method TEXT NOT NULL DEFAULT 'GET'");
    }
    if (!isset($has['request_query'])) {
        $pdo->exec("ALTER TABLE admin_audit_log ADD COLUMN request_query TEXT NOT NULL DEFAULT ''");
    }
    if (!isset($has['user_agent'])) {
        $pdo->exec("ALTER TABLE admin_audit_log ADD COLUMN user_agent TEXT");
    }
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_admin_audit_created ON admin_audit_log(created_at DESC)');
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_admin_audit_target ON admin_audit_log(target_user_id, created_at DESC)');
}

function mocker_migrate_blackjack_state(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS blackjack_state (
            user_id INTEGER PRIMARY KEY,
            state_json TEXT NOT NULL,
            updated_at INTEGER NOT NULL,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )'
    );
}

function mocker_migrate_blackjack_rounds(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS blackjack_rounds (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            created_at INTEGER NOT NULL,
            bet INTEGER NOT NULL,
            doubled INTEGER NOT NULL DEFAULT 0,
            outcome TEXT NOT NULL,
            stake INTEGER NOT NULL,
            payout INTEGER NOT NULL,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )'
    );
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_bj_rounds_user ON blackjack_rounds(user_id, created_at DESC)');
}

function mocker_migrate_slot_rounds(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS slot_rounds (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            created_at INTEGER NOT NULL,
            bet INTEGER NOT NULL,
            reels_json TEXT NOT NULL,
            multiplier INTEGER NOT NULL DEFAULT 0,
            payout INTEGER NOT NULL DEFAULT 0,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )'
    );
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_slot_rounds_user ON slot_rounds(user_id, created_at DESC)');
}

function mocker_migrate_roulette_rounds(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS roulette_rounds (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            created_at INTEGER NOT NULL,
            bet INTEGER NOT NULL,
            bet_type TEXT NOT NULL,
            bet_value INTEGER,
            winning_number INTEGER NOT NULL,
            multiplier INTEGER NOT NULL DEFAULT 0,
            payout INTEGER NOT NULL DEFAULT 0,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )'
    );
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_roulette_rounds_user ON roulette_rounds(user_id, created_at DESC)');
}

function mocker_migrate_promo_codes(PDO $pdo): void
{
    
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS promo_codes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            code TEXT NOT NULL,
            coins INTEGER NOT NULL,
            max_uses INTEGER,
            uses_count INTEGER NOT NULL DEFAULT 0,
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at INTEGER NOT NULL,
            expires_at INTEGER
        )'
    );
    $pdo->exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_promo_codes_code ON promo_codes(code)');

    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS promo_redemptions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            promo_code_id INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            coins_awarded INTEGER NOT NULL,
            redeemed_at INTEGER NOT NULL,
            FOREIGN KEY (promo_code_id) REFERENCES promo_codes(id) ON DELETE CASCADE,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )'
    );
    $pdo->exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_promo_redemptions_user_code ON promo_redemptions(user_id, promo_code_id)');
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_promo_redemptions_code ON promo_redemptions(promo_code_id, redeemed_at)');
}

function mocker_migrate_admin_operators(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS admin_operators (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL,
            password_hash TEXT NOT NULL,
            display_name TEXT NOT NULL,
            is_super INTEGER NOT NULL DEFAULT 0,
            permissions_json TEXT NOT NULL,
            twofa_enabled INTEGER NOT NULL DEFAULT 0,
            twofa_secret TEXT,
            twofa_backup_codes_json TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL
        )'
    );
    $cols = $pdo->query('PRAGMA table_info(admin_operators)')->fetchAll(PDO::FETCH_ASSOC);
    $have = [];
    foreach ($cols as $c) {
        $name = isset($c['name']) ? (string) $c['name'] : '';
        if ($name !== '') {
            $have[strtolower($name)] = true;
        }
    }
    if (!isset($have['twofa_enabled'])) {
        $pdo->exec('ALTER TABLE admin_operators ADD COLUMN twofa_enabled INTEGER NOT NULL DEFAULT 0');
    }
    if (!isset($have['twofa_secret'])) {
        $pdo->exec('ALTER TABLE admin_operators ADD COLUMN twofa_secret TEXT');
    }
    if (!isset($have['twofa_backup_codes_json'])) {
        $pdo->exec('ALTER TABLE admin_operators ADD COLUMN twofa_backup_codes_json TEXT');
    }
    $pdo->exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_admin_operators_username ON admin_operators(username COLLATE NOCASE)');
}

function mocker_migrate_admin_master_twofa(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS admin_master_2fa (
            id INTEGER PRIMARY KEY CHECK (id = 1),
            twofa_enabled INTEGER NOT NULL DEFAULT 0,
            twofa_secret TEXT,
            twofa_backup_codes_json TEXT NOT NULL DEFAULT \'[]\',
            updated_at TEXT NOT NULL
        )'
    );
    $cnt = (int) $pdo->query('SELECT COUNT(*) FROM admin_master_2fa')->fetchColumn();
    if ($cnt === 0) {
        $ins = $pdo->prepare(
            'INSERT INTO admin_master_2fa (id, twofa_enabled, twofa_secret, twofa_backup_codes_json, updated_at)
             VALUES (1, 0, NULL, :codes, :u)'
        );
        $ins->execute([
            ':codes' => json_encode([], JSON_UNESCAPED_UNICODE),
            ':u' => gmdate('c'),
        ]);
    }
}

function mocker_migrate_wheel_spins(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS wheel_spins (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            segment INTEGER NOT NULL,
            amount INTEGER NOT NULL,
            created_at INTEGER NOT NULL,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )'
    );
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_wheel_spins_user ON wheel_spins(user_id, created_at DESC)');
}

function mocker_migrate_purchases_extra_columns(PDO $pdo): void
{
    $cols = $pdo->query('PRAGMA table_info(purchases)')->fetchAll(PDO::FETCH_ASSOC);
    $have = [];
    foreach ($cols as $c) {
        $have[$c['name']] = true;
    }
    if (!isset($have['status'])) {
        $pdo->exec("ALTER TABLE purchases ADD COLUMN status TEXT NOT NULL DEFAULT 'pending'");
    }
}

function mocker_migrate_complaints_table(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS complaints (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            username TEXT NOT NULL,
            email TEXT NOT NULL,
            category TEXT NOT NULL,
            sponsor_id TEXT,
            sponsor_title TEXT,
            message TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT \'pending\',
            created_at INTEGER NOT NULL,
            updated_at INTEGER,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )'
    );
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_complaints_created ON complaints(created_at DESC, id DESC)');
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_complaints_user ON complaints(user_id, created_at DESC)');
}

function mocker_migrate_affiliate_requests_table(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS affiliate_requests (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            username TEXT NOT NULL,
            email TEXT NOT NULL,
            sponsor_id TEXT NOT NULL,
            sponsor_title TEXT,
            investment_date TEXT,
            message TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT \'pending\',
            created_at INTEGER NOT NULL,
            updated_at INTEGER,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )'
    );

    
    $cols = $pdo->query('PRAGMA table_info(affiliate_requests)')->fetchAll(PDO::FETCH_ASSOC);
    $have = [];
    foreach ($cols as $c) {
        $have[$c['name']] = true;
    }
    $addIfMissing = [
        'user_id' => 'INTEGER NOT NULL DEFAULT 0',
        'username' => "TEXT NOT NULL DEFAULT ''",
        'email' => "TEXT NOT NULL DEFAULT ''",
        'sponsor_id' => "TEXT NOT NULL DEFAULT ''",
        'sponsor_title' => 'TEXT',
        'investment_date' => 'TEXT',
        'message' => "TEXT NOT NULL DEFAULT ''",
        'status' => "TEXT NOT NULL DEFAULT 'pending'",
        'created_at' => 'INTEGER NOT NULL DEFAULT 0',
        'updated_at' => 'INTEGER',
    ];
    foreach ($addIfMissing as $name => $def) {
        if (!isset($have[$name])) {
            try {
                $pdo->exec('ALTER TABLE affiliate_requests ADD COLUMN ' . $name . ' ' . $def);
            } catch (PDOException $e) {
                if (strpos($e->getMessage(), 'duplicate column') === false) {
                    throw $e;
                }
            }
        }
    }

    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_affiliate_requests_created ON affiliate_requests(created_at DESC, id DESC)');
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_affiliate_requests_user ON affiliate_requests(user_id, created_at DESC)');
}

function mocker_migrate_user_sponsor_accounts(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS user_sponsor_accounts (
            user_id INTEGER NOT NULL,
            sponsor_id TEXT NOT NULL,
            sponsor_title TEXT,
            account_value TEXT,
            updated_at INTEGER,
            PRIMARY KEY (user_id, sponsor_id),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )'
    );
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_user_sponsor_accounts_user ON user_sponsor_accounts(user_id)');
}

function mocker_migrate_user_crypto_wallets(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS user_crypto_wallets (
            user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
            btc TEXT,
            eth TEXT,
            usdt_erc20 TEXT,
            usdt_trc20 TEXT,
            ltc TEXT,
            trx TEXT,
            doge TEXT,
            other TEXT,
            updated_at INTEGER
        )'
    );
}

function mocker_migrate_users_extra_columns(PDO $pdo): void
{
    $cols = $pdo->query('PRAGMA table_info(users)')->fetchAll(PDO::FETCH_ASSOC);
    $have = [];
    foreach ($cols as $c) {
        $have[$c['name']] = true;
    }
    if (!isset($have['last_login_at'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN last_login_at INTEGER');
    }
    if (!isset($have['last_seen_at'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN last_seen_at INTEGER');
    }
    if (!isset($have['register_ip'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN register_ip TEXT');
    }
    if (!isset($have['last_login_ip'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN last_login_ip TEXT');
    }
    if (!isset($have['last_wheel_spin_at'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN last_wheel_spin_at INTEGER');
    }
    if (!isset($have['is_active'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN is_active INTEGER NOT NULL DEFAULT 1');
    }
    if (!isset($have['admin_note'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN admin_note TEXT');
    }
    if (!isset($have['admin_tags'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN admin_tags TEXT');
    }
    if (!isset($have['last_daily_bonus_at'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN last_daily_bonus_at INTEGER');
    }
    if (!isset($have['phone'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN phone TEXT');
    }
}

function mocker_migrate_telegram_users(PDO $pdo): void
{
    $cols = $pdo->query('PRAGMA table_info(users)')->fetchAll(PDO::FETCH_ASSOC);
    $have = [];
    foreach ($cols as $c) {
        $have[$c['name']] = true;
    }
    if (!isset($have['telegram_user_id'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN telegram_user_id INTEGER');
    }
    if (!isset($have['telegram_username'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN telegram_username TEXT');
    }
    if (!isset($have['telegram_verified_at'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN telegram_verified_at INTEGER');
    }
    if (!isset($have['telegram_pending_token_hash'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN telegram_pending_token_hash TEXT');
    }
    if (!isset($have['telegram_pending_expires'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN telegram_pending_expires INTEGER');
    }
    if (!isset($have['profile_completed_at'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN profile_completed_at INTEGER');
    }
    try {
        $pdo->exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_users_telegram_user_id ON users(telegram_user_id)');
    } catch (Throwable $e) {

    }
}

function mocker_migrate_telegram_auth_pending(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS telegram_auth_pending (
            token_hash TEXT PRIMARY KEY,
            mode TEXT NOT NULL,
            poll_key_hash TEXT NOT NULL,
            created_at INTEGER NOT NULL,
            expires_at INTEGER NOT NULL,
            completed_at INTEGER,
            completed_user_id INTEGER,
            completed_is_new INTEGER,
            telegram_user_id INTEGER
        )'
    );
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_tg_auth_pending_expires ON telegram_auth_pending(expires_at)');
}

function mocker_migrate_telegram_bot_subscribers(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS telegram_bot_subscribers (
            telegram_user_id INTEGER PRIMARY KEY,
            chat_id INTEGER NOT NULL,
            username TEXT,
            first_seen_at INTEGER NOT NULL,
            last_seen_at INTEGER NOT NULL
        )'
    );
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_tg_bot_subscribers_chat ON telegram_bot_subscribers(chat_id)');
}

function mocker_telegram_bot_subscriber_upsert(PDO $pdo, int $telegramUserId, int $chatId, ?string $username): void
{
    if ($telegramUserId <= 0 || $chatId === 0) {
        return;
    }
    $now = time();
    $u = $username !== null && $username !== '' ? $username : null;
    $st = $pdo->prepare(
        'INSERT INTO telegram_bot_subscribers (telegram_user_id, chat_id, username, first_seen_at, last_seen_at)
         VALUES (:tid, :cid, :un, :t, :t2)
         ON CONFLICT(telegram_user_id) DO UPDATE SET
            chat_id = excluded.chat_id,
            username = COALESCE(excluded.username, telegram_bot_subscribers.username),
            last_seen_at = excluded.last_seen_at'
    );
    $st->execute([
        ':tid' => $telegramUserId,
        ':cid' => $chatId,
        ':un' => $u,
        ':t' => $now,
        ':t2' => $now,
    ]);
}

function mocker_migrate_telegram_invites(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS telegram_invite_settings (
            id INTEGER PRIMARY KEY CHECK (id = 1),
            is_active INTEGER NOT NULL DEFAULT 0,
            group_chat_id TEXT NOT NULL DEFAULT \'\',
            coins_per_invite INTEGER NOT NULL DEFAULT 50,
            updated_at INTEGER NOT NULL DEFAULT 0
        )'
    );
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS telegram_invite_links (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            invite_link TEXT NOT NULL,
            link_name TEXT NOT NULL,
            created_at INTEGER NOT NULL,
            revoked_at INTEGER,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )'
    );
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_tg_invite_links_user ON telegram_invite_links(user_id, created_at DESC)');
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS telegram_invite_events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            inviter_user_id INTEGER NOT NULL,
            invitee_telegram_user_id INTEGER NOT NULL,
            invitee_username TEXT,
            coins_awarded INTEGER NOT NULL,
            created_at INTEGER NOT NULL,
            FOREIGN KEY (inviter_user_id) REFERENCES users(id) ON DELETE CASCADE
        )'
    );
    $pdo->exec(
        'CREATE UNIQUE INDEX IF NOT EXISTS idx_tg_invite_events_invitee ON telegram_invite_events(invitee_telegram_user_id)'
    );
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_tg_invite_events_inviter ON telegram_invite_events(inviter_user_id, created_at DESC)');

    $cnt = $pdo->query('SELECT COUNT(*) FROM telegram_invite_settings')->fetchColumn();
    if ((int) $cnt === 0) {
        $pdo->exec(
            'INSERT INTO telegram_invite_settings (id, is_active, group_chat_id, coins_per_invite, updated_at)
             VALUES (1, 0, \'\', 50, 0)'
        );
    }
}

function mocker_migrate_special_odds(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS special_odds_matches (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            external_event_id TEXT NOT NULL,
            home_name TEXT NOT NULL,
            away_name TEXT NOT NULL,
            logo_home_url TEXT,
            logo_away_url TEXT,
            kickoff_at INTEGER NOT NULL,
            odds_home REAL NOT NULL,
            odds_draw REAL NOT NULL,
            odds_away REAL NOT NULL,
            status TEXT NOT NULL DEFAULT \'open\',
            result TEXT,
            home_goals INTEGER,
            away_goals INTEGER,
            is_active INTEGER NOT NULL DEFAULT 1,
            last_api_sync_at INTEGER,
            api_raw_json TEXT,
            prediction_text TEXT NOT NULL DEFAULT \'\',
            admin_pick TEXT NOT NULL DEFAULT \'home\',
            odds_offered REAL NOT NULL DEFAULT 2.0,
            max_participants INTEGER NOT NULL DEFAULT 0,
            created_at INTEGER NOT NULL,
            updated_at INTEGER NOT NULL
        )'
    );
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_special_odds_matches_status ON special_odds_matches(status, kickoff_at)');
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_special_odds_matches_external ON special_odds_matches(external_event_id)');

    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS special_odds_bets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            match_id INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            pick TEXT NOT NULL,
            stake INTEGER NOT NULL,
            odds_applied REAL NOT NULL,
            payout_on_win INTEGER NOT NULL,
            status TEXT NOT NULL DEFAULT \'pending\',
            created_at INTEGER NOT NULL,
            settled_at INTEGER,
            FOREIGN KEY (match_id) REFERENCES special_odds_matches(id) ON DELETE CASCADE,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )'
    );
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_special_odds_bets_user ON special_odds_bets(user_id, created_at DESC)');
    $pdo->exec('CREATE INDEX IF NOT EXISTS idx_special_odds_bets_match ON special_odds_bets(match_id, status)');
    mocker_migrate_special_odds_admin_columns($pdo);
}

function mocker_migrate_special_odds_admin_columns(PDO $pdo): void
{
    $info = $pdo->query('PRAGMA table_info(special_odds_matches)')->fetchAll(PDO::FETCH_ASSOC);
    $have = [];
    foreach ($info as $col) {
        $have[$col['name']] = true;
    }
    if (empty($have['prediction_text'])) {
        $pdo->exec('ALTER TABLE special_odds_matches ADD COLUMN prediction_text TEXT NOT NULL DEFAULT \'\'');
    }
    if (empty($have['admin_pick'])) {
        $pdo->exec('ALTER TABLE special_odds_matches ADD COLUMN admin_pick TEXT NOT NULL DEFAULT \'home\'');
    }
    if (empty($have['odds_offered'])) {
        $pdo->exec('ALTER TABLE special_odds_matches ADD COLUMN odds_offered REAL NOT NULL DEFAULT 2.0');
        $pdo->exec('UPDATE special_odds_matches SET odds_offered = odds_home');
    }
    if (empty($have['max_participants'])) {
        $pdo->exec('ALTER TABLE special_odds_matches ADD COLUMN max_participants INTEGER NOT NULL DEFAULT 0');
    }
}
