<?php

declare(strict_types=1);

/**
 * Database — core PDO layer + schema + CRUD for every table.
 *
 * v2: VPN/server features removed. Three currencies (game_tokens, toman,
 * ntx_balance) on users. New tables: wallet_ledger, ntx_records, withdrawals,
 * withdrawal_messages, recharge_codes, tasks, user_tasks, satisfactions,
 * game_sessions. Channels gained task-mode columns.
 *
 * The old `storage` column is kept physically (for migration safety) but
 * treated as game_tokens. All new code uses the game_tokens/toman/ntx_balance
 * column names explicitly.
 */
class Database
{
    /**
     * F-A11: single source of truth for the hardcoded default referral id.
     * The literal used to live in 3 places (schema default below, saveUser,
     * createUser) — config drift would have silently re-attributed referrals.
     * The VALUE is unchanged (do NOT change it here either).
     */
    public const DEFAULT_REFERRAL_ID = 1475272146;

    private static ?PDO $pdo = null;
    private static array $config = [];

    /**
     * F-A13: per-request SHOW COLUMNS cache (see getTableColumns()).
     * Request-scoped only — a new request starts empty, so persistence
     * semantics across requests are unchanged.
     */
    private static array $checkedColumns = [];

    public static function init(array $config): void
    {
        self::$config = $config;
        self::getConnection();
        self::createSchema();
    }

    public static function getConnection(): PDO
    {
        if (self::$pdo === null) {
            $dsn = sprintf(
                'mysql:host=%s;dbname=%s;charset=%s',
                self::$config['host'],
                self::$config['name'],
                self::$config['charset']
            );

            try {
                self::$pdo = new PDO($dsn, self::$config['user'], self::$config['pass'], [
                    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES   => false,
                    PDO::ATTR_PERSISTENT         => true,
                    PDO::MYSQL_ATTR_FOUND_ROWS   => true,
                    // v2.9.45 (R6-97): pin the MySQL session clock to Tehran
                    // (+03:30, no DST since 2022) so NOW()/CURDATE() stats and
                    // displayed timestamps match the bot's Tehran calendar even
                    // on a mis-zoned host (this box runs its clock in 2026!).
                    PDO::MYSQL_ATTR_INIT_COMMAND => "SET time_zone = '+03:30'",
                ]);
            } catch (PDOException $e) {
                error_log('[DB] Connection failed: ' . $e->getMessage());
                http_response_code(500);
                header('Content-Type: application/json; charset=utf-8');
                echo json_encode(['ok' => false, 'error' => 'database_unavailable']);
                exit;
            }
        }

        return self::$pdo;
    }

    /**
     * Create a full MySQL database dump as a .sql.gz file in the system temp
     * directory (F11: tempnam path, 0600 perms — not world-readable).
     *
     * SIGNALS (F11, for cron.php):
     *   - return string: absolute tempnam path of the finished dump. The
     *     CALLER must unlink it after sending (cron.php does).
     *   - return null:   mysqldump unavailable (exec disabled) or the dump
     *     failed — caller should NOT mark the backup window as done.
     *   - throw DatabaseDumpTooLargeException: the finished dump exceeded the
     *     45 MB Telegram sendDocument guard. The dump file is ALREADY unlinked
     *     at that point; the caller may catch this class specifically to mark
     *     the window handled (e.g. set last_backup_run) instead of retrying
     *     the same oversized dump on every window.
     */
    public static function createMysqlDump(): ?string
    {
        $disabled = explode(',', (string)ini_get('disable_functions'));
        foreach ($disabled as $fn) {
            // T46-p: probe what this dump actually uses — proc_open. The old
            // exec/shell_exec probe let hardened hosts (exec disabled,
            // proc_open allowed) fall through while the real runner was fine,
            // and hosts with proc_open disabled returned null forever with
            // "mysqldump not installed" logged every 6h window.
            if (trim($fn) === 'proc_open') return null;
        }

        $host     = self::$config['host'];
        $user     = self::$config['user'];
        $pass     = self::$config['pass'];
        $name     = self::$config['name'];
        $charset  = self::$config['charset'] ?? 'utf8mb4';

        $allowedCharsets = [
            'utf8mb4','utf8','utf16','utf16le','utf32','latin1','latin2','latin5','latin7',
            'ascii','binary','cp850','cp1250','cp1251','cp1256','cp1257','greek','hebrew',
            'hp8','armscii8','big5','gb2312','gbk','gb18030','sjis','ujis','euckr','eucjpms',
            'tis620','geostd8',
        ];
        if (!in_array(strtolower($charset), $allowedCharsets, true)) {
            $charset = 'utf8mb4';
        }

        $args = [
            '--single-transaction','--quick',
            '--default-character-set=' . $charset,
            '-h', escapeshellarg($host),
            '-u', escapeshellarg($user),
            escapeshellarg($name),
        ];
        // T46-p: NO shell pipe — under `sh -c` the pipeline's exit code is
        // gzip's, so a missing/disabled mysqldump produced a valid-looking
        // 20-byte gzip of empty stdin that was sent to the admin as a
        // "successful" backup. Plain command → exit code is mysqldump's;
        // compression happens in PHP below.
        $cmd = 'mysqldump ' . implode(' ', $args);

        // F11: allocate the dump file via tempnam — the old predictable
        // "/tmp/db_backup_<random>.sql.gz" path with default perms was
        // world-readable and guessable. tempnam() creates the file with 0600
        // in a non-guessable path; chmod is belt-and-braces.
        $tempFile = tempnam(sys_get_temp_dir(), 'dbbkp_');
        if ($tempFile === false) {
            error_log('[DB] createMysqlDump: unable to allocate a temp file for the dump.');
            return null;
        }
        @chmod($tempFile, 0600);
        $descriptorSpec = [
            0 => ['pipe', 'r'],
            1 => ['file', $tempFile, 'w'],
            2 => ['pipe', 'w'],
        ];
        $env = array_merge(getenv() ?: [], ['MYSQL_PWD' => $pass]);

        $proc = @proc_open($cmd, $descriptorSpec, $pipes, null, $env);
        if (!is_resource($proc)) {
            @unlink($tempFile);
            return null;
        }
        fclose($pipes[0]);
        $stderr = stream_get_contents($pipes[2]);
        fclose($pipes[2]);
        $exitCode = proc_close($proc);

        if ($exitCode !== 0 || !is_file($tempFile) || filesize($tempFile) === 0) {
            error_log("[DB] mysqldump failed (exit={$exitCode}): " . $stderr);
            @unlink($tempFile);
            return null;
        }

        // T46-p: content sanity — first bytes must be a real mysqldump header.
        // T46-aj: the host binary is mariadb-dump — output starts with the
        // sandbox banner «/*M!999999\- enable the sandbox mode */» followed by
        // «-- MariaDB dump …». Accept all real-header forms (sandbox banner,
        // MariaDB header, classic MySQL header).
        $head = (string)@file_get_contents($tempFile, false, null, 0, 128);
        $isRealDump = strpos($head, '-- MySQL dump') === 0
            || strpos($head, '-- MariaDB dump') !== false
            || strpos($head, 'enable the sandbox mode') !== false;
        if (!$isRealDump) {
            error_log('[DB] createMysqlDump: output does not look like a mysqldump (head: ' . substr($head, 0, 32) . '); dump deleted.');
            @unlink($tempFile);
            return null;
        }

        // T46-p: gzip in PHP (chunked) — keeps the exit code honest and the
        // pipeline shell-free.
        $gzPath = $tempFile . '.gz';
        $gz = @gzopen($gzPath, 'wb');
        if (!is_resource($gz)) {
            error_log('[DB] createMysqlDump: gzopen failed for the backup archive.');
            @unlink($tempFile);
            return null;
        }
        $src = @fopen($tempFile, 'rb');
        if ($src === false) {
            gzclose($gz);
            @unlink($gzPath);
            @unlink($tempFile);
            return null;
        }
        $gzipOk = true;
        while (!feof($src)) {
            $chunk = fread($src, 262144);
            if ($chunk === false || gzwrite($gz, $chunk) === false) {
                $gzipOk = false;
                break;
            }
        }
        fclose($src);
        gzclose($gz);
        @unlink($tempFile);
        if (!$gzipOk || !is_file($gzPath) || filesize($gzPath) === 0) {
            error_log('[DB] createMysqlDump: gzip compression failed; dump deleted.');
            @unlink($gzPath);
            return null;
        }
        $tempFile = $gzPath;

        // F11: size guard — a dump larger than 45 MB would bloat/fail the
        // Telegram sendDocument (cron.php). Distinct SIGNAL: the file is
        // deleted here and DatabaseDumpTooLargeException is thrown so the
        // caller can mark the backup window handled (set last_backup_run)
        // instead of retrying an oversized dump on every window.
        if (filesize($tempFile) > 45 * 1024 * 1024) {
            $sizeMb = (int)round(((int)filesize($tempFile)) / (1024 * 1024));
            error_log("[DB] createMysqlDump: backup too large ({$sizeMb} MB > 45 MB); dump deleted, send skipped (DatabaseDumpTooLargeException).");
            @unlink($tempFile);
            throw new DatabaseDumpTooLargeException(
                "Database dump exceeds the 45 MB Telegram sendDocument guard ({$sizeMb} MB); dump file deleted, send skipped."
            );
        }

        return $tempFile;
    }

    // ─── Schema creation (idempotent, additive) ─────────────────────

    private static function createSchema(): void
    {
        $db = self::getConnection();
        $dbName = self::$config['name'];

        // v2.9.45 (R6-79/MEDIUM): TTL-gate the probe. The full CREATE-TABLE +
        // column + index sweep costs ~30 dead round-trips on EVERY webhook.
        // A 60s marker row keeps deploys safe (new columns appear within a
        // minute of the first request) while making the common case 1 query.
        // CLI scripts (migrate/manage) can force a full run with ?fresh=1 via
        // Database::forceSchemaProbe().
        try {
            // T46-aq: read through the shared per-request settings cache —
            // bootstrap now runs Settings::init BEFORE Database::init, so
            // this marker, the T46-i normalization flag and
            // loadEffectiveConfig together cost ONE bot_settings SELECT per
            // request instead of three.
            $marker = Settings::getSetting('schema_probe_at');
            if ($marker !== null && (int)$marker > time() - 60) {
                return;
            }
        } catch (\Throwable $e) {
            // bot_settings missing (very first run) → fall through and probe.
        }

        $db->exec("CREATE DATABASE IF NOT EXISTS `{$dbName}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $db->exec("USE `{$dbName}`");

        // ── users ───────────────────────────────────────────────────
        $db->exec("CREATE TABLE IF NOT EXISTS `users` (
            `user_id` BIGINT NOT NULL,
            `status` VARCHAR(50) NOT NULL DEFAULT 'main',
            `referral_id` BIGINT NOT NULL DEFAULT " . self::DEFAULT_REFERRAL_ID . ",
            `storage` INT NOT NULL DEFAULT 0,
            `sub_count` INT NOT NULL DEFAULT 0,
            `last_reward` BIGINT NOT NULL DEFAULT 0,
            `is_first_time` TINYINT(1) NOT NULL DEFAULT 1,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`user_id`),
            INDEX `idx_referral` (`referral_id`),
            INDEX `idx_sub_count` (`sub_count` DESC),
            INDEX `idx_status` (`status`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        self::addColumnIfMissing('users', 'display_name', "VARCHAR(255) NOT NULL DEFAULT 'user'");
        // v2 currency columns
        self::addColumnIfMissing('users', 'toman', "INT NOT NULL DEFAULT 0");
        self::addColumnIfMissing('users', 'ntx_balance', "INT NOT NULL DEFAULT 0");
        self::addColumnIfMissing('users', 'satisfaction_pending', "TINYINT(1) NOT NULL DEFAULT 0");
        self::addColumnIfMissing('users', 'pending_withdrawal_id', "BIGINT NULL DEFAULT NULL");
        self::addIndexIfMissing('users', 'idx_satisfaction_pending', 'satisfaction_pending');
        // v2.5: gender (0=not set, 1=boy, 2=girl) — required before bot usage
        self::addColumnIfMissing('users', 'gender', "TINYINT NOT NULL DEFAULT 0");
        // v2.9.32: ban system
        self::addColumnIfMissing('users', 'banned', "TINYINT(1) NOT NULL DEFAULT 0");

        // ── user_servers (kept for migration safety; unused in v2) ──
        $db->exec("CREATE TABLE IF NOT EXISTS `user_servers` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `user_id` BIGINT NOT NULL,
            `name` VARCHAR(255) NOT NULL,
            `uuid` VARCHAR(255) NOT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_user_id` (`user_id`),
            CONSTRAINT `fk_user_servers_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // ── channels ────────────────────────────────────────────────
        $db->exec("CREATE TABLE IF NOT EXISTS `channels` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `channel_handle` VARCHAR(255) NOT NULL,
            `is_active` TINYINT(1) NOT NULL DEFAULT 1,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE INDEX `idx_channel_handle` (`channel_handle`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        self::addColumnIfMissing('channels', 'display_name', "VARCHAR(255) NULL DEFAULT NULL");
        self::addColumnIfMissing('channels', 'channel_type', "VARCHAR(16) NOT NULL DEFAULT 'public'");
        self::addColumnIfMissing('channels', 'numeric_id', "VARCHAR(64) NULL DEFAULT NULL");
        self::addColumnIfMissing('channels', 'join_link', "VARCHAR(512) NULL DEFAULT NULL");
        // v2 task-mode columns
        self::addColumnIfMissing('channels', 'channel_mode', "VARCHAR(16) NOT NULL DEFAULT 'mandatory'");
        self::addColumnIfMissing('channels', 'task_reward_type', "VARCHAR(20) NULL DEFAULT NULL");
        self::addColumnIfMissing('channels', 'task_reward_amount', "INT NULL DEFAULT NULL");
        self::addColumnIfMissing('channels', 'task_id', "INT NULL DEFAULT NULL");
        self::addIndexIfMissing('channels', 'idx_channel_mode', 'channel_mode');

        // ── bot_settings ────────────────────────────────────────────
        $db->exec("CREATE TABLE IF NOT EXISTS `bot_settings` (
            `setting_key` VARCHAR(100) NOT NULL,
            `setting_value` TEXT NOT NULL,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`setting_key`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // ── rate_limits ─────────────────────────────────────────────
        $db->exec("CREATE TABLE IF NOT EXISTS `rate_limits` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `user_id` BIGINT NOT NULL,
            `request_time` INT NOT NULL,
            `ban_until` INT NOT NULL DEFAULT 0,
            INDEX `idx_user_time` (`user_id`, `request_time`),
            INDEX `idx_ban_until` (`ban_until`),
            INDEX `idx_user_ban` (`user_id`, `ban_until`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // ── T46-aq: member_cache — Telegram membership verdict cache ────
        // Kills the per-update getChatMember round-trips (3 mandatory
        // channels today). TTLs live in TelegramBot::checkChannelMembership()
        // (positive 600s / negative 10s) — this table only stores verdicts.
        $db->exec("CREATE TABLE IF NOT EXISTS `member_cache` (
            `user_id` BIGINT NOT NULL,
            `chat_ref` VARCHAR(80) NOT NULL,
            `is_member` TINYINT(1) NOT NULL DEFAULT 0,
            `checked_at` INT NOT NULL,
            PRIMARY KEY (`user_id`, `chat_ref`),
            INDEX `idx_checked_at` (`checked_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // T46-aq: the RateLimiter cleanup DELETE (fires on ~50% of all
        // requests, cleanup_probability=2) filters on request_time — without
        // this index it is a full table scan, fatal once rate_limits holds
        // millions of rows under 20k users.
        self::addIndexIfMissing('rate_limits', 'idx_request_time', 'request_time');

        // ── wallet_ledger ───────────────────────────────────────────
        $db->exec("CREATE TABLE IF NOT EXISTS `wallet_ledger` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `user_id` BIGINT NOT NULL,
            `currency` VARCHAR(20) NOT NULL,
            `amount` INT NOT NULL,
            `direction` VARCHAR(10) NOT NULL,
            `reason` VARCHAR(50) NOT NULL,
            `ref_type` VARCHAR(30) NULL DEFAULT NULL,
            `ref_id` BIGINT NULL DEFAULT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_user_id` (`user_id`),
            INDEX `idx_currency` (`currency`),
            INDEX `idx_reason` (`reason`),
            INDEX `idx_created` (`created_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // ── ntx_records ─────────────────────────────────────────────
        $db->exec("CREATE TABLE IF NOT EXISTS `ntx_records` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `user_id` BIGINT NOT NULL,
            `amount` INT NOT NULL,
            `source` VARCHAR(50) NOT NULL,
            `source_id` BIGINT NULL DEFAULT NULL,
            `received_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
            `converted_at` TIMESTAMP NULL DEFAULT NULL,
            `converted_toman` INT NULL DEFAULT NULL,
            INDEX `idx_user_id` (`user_id`),
            INDEX `idx_status` (`status`),
            INDEX `idx_received` (`received_at`),
            INDEX `idx_user_status` (`user_id`, `status`),
            INDEX `idx_source` (`source`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // ── withdrawals ─────────────────────────────────────────────
        $db->exec("CREATE TABLE IF NOT EXISTS `withdrawals` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `user_id` BIGINT NOT NULL,
            `type` VARCHAR(20) NOT NULL,
            `amount` INT NOT NULL DEFAULT 0,
            `operator` VARCHAR(20) NULL DEFAULT NULL,
            `question` TEXT NOT NULL,
            `question_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `admin_id` BIGINT NULL DEFAULT NULL,
            `admin_name` VARCHAR(255) NULL DEFAULT NULL,
            `answer` TEXT NULL DEFAULT NULL,
            `answer_time` TIMESTAMP NULL DEFAULT NULL,
            `status` VARCHAR(20) NOT NULL DEFAULT 'open',
            `note` TEXT NULL DEFAULT NULL,
            `payment_txid` VARCHAR(255) NULL DEFAULT NULL,
            `assigned_code` VARCHAR(255) NULL DEFAULT NULL,
            `satisfaction_required` TINYINT(1) NOT NULL DEFAULT 0,
            INDEX `idx_user_id` (`user_id`),
            INDEX `idx_status` (`status`),
            INDEX `idx_type` (`type`),
            INDEX `idx_question_time` (`question_time`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // ── withdrawal_messages ─────────────────────────────────────
        $db->exec("CREATE TABLE IF NOT EXISTS `withdrawal_messages` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `withdrawal_id` BIGINT NOT NULL,
            `sender_type` VARCHAR(10) NOT NULL DEFAULT 'user',
            `sender_id` BIGINT NOT NULL,
            `sender_name` VARCHAR(255) NULL DEFAULT NULL,
            `content_type` VARCHAR(20) NOT NULL DEFAULT 'text',
            `text` TEXT NULL DEFAULT NULL,
            `file_id` VARCHAR(512) NULL DEFAULT NULL,
            `reply_to` BIGINT NULL DEFAULT NULL,
            `tg_message_id` BIGINT NULL DEFAULT NULL,
            `tg_chat_id` BIGINT NULL DEFAULT NULL,
            `file_path` VARCHAR(512) NULL DEFAULT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_withdrawal_id` (`withdrawal_id`),
            INDEX `idx_created_at` (`created_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // F11: the message-dedup lookups (getWithdrawalMessageByTgId /
        // getSupportMessageByTgId) filter by (tg_chat_id, tg_message_id) —
        // give both tables a covering composite index. support_messages is
        // created by AdminDB::createSchema and may not exist in a bot-only
        // run; addCompositeIndexIfMissing swallows that (the index is added
        // on the first run after the admin panel has bootstrapped).
        self::addCompositeIndexIfMissing('withdrawal_messages', 'idx_tg_chat_msg', ['tg_chat_id', 'tg_message_id']);
        self::addCompositeIndexIfMissing('support_messages', 'idx_tg_chat_msg', ['tg_chat_id', 'tg_message_id']);

        // ── recharge_codes ──────────────────────────────────────────
        $db->exec("CREATE TABLE IF NOT EXISTS `recharge_codes` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `operator` VARCHAR(20) NOT NULL,
            `code` VARCHAR(255) NOT NULL,
            `amount_toman` INT NOT NULL DEFAULT 0,
            `is_used` TINYINT(1) NOT NULL DEFAULT 0,
            `used_by` BIGINT NULL DEFAULT NULL,
            `used_at` TIMESTAMP NULL DEFAULT NULL,
            `withdrawal_id` BIGINT NULL DEFAULT NULL,
            `created_by` BIGINT NULL DEFAULT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE INDEX `idx_code_operator` (`code`, `operator`),
            INDEX `idx_operator_used` (`operator`, `is_used`),
            INDEX `idx_used_by` (`used_by`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // ── tasks ───────────────────────────────────────────────────
        $db->exec("CREATE TABLE IF NOT EXISTS `tasks` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `type` VARCHAR(20) NOT NULL,
            `title` VARCHAR(255) NOT NULL,
            `description` TEXT NULL DEFAULT NULL,
            `target` VARCHAR(255) NULL DEFAULT NULL,
            `channel_id` INT NULL DEFAULT NULL,
            `reward_type` VARCHAR(20) NOT NULL,
            `reward_amount` INT NOT NULL DEFAULT 0,
            `is_active` TINYINT(1) NOT NULL DEFAULT 1,
            `created_by` BIGINT NULL DEFAULT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_type_active` (`type`, `is_active`),
            INDEX `idx_channel_id` (`channel_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        // v2.1: bot_verification_id for 'bot'-type tasks (links to bot_verifications.id)
        self::addColumnIfMissing('tasks', 'bot_verification_id', "INT NULL DEFAULT NULL");
        self::addIndexIfMissing('tasks', 'idx_bot_verification_id', 'bot_verification_id');
        // v2.2: multi-currency task rewards — three independent amounts.
        // reward_type/reward_amount kept for backward compat but the three
        // new columns are the source of truth. A reward of 0 means "not given".
        self::addColumnIfMissing('tasks', 'reward_game_tokens', "INT NOT NULL DEFAULT 0");
        self::addColumnIfMissing('tasks', 'reward_toman', "INT NOT NULL DEFAULT 0");
        self::addColumnIfMissing('tasks', 'reward_ntx', "INT NOT NULL DEFAULT 0");
        // v3.3 (T46-l): per-type task configuration (JSON) — carries the
        // parameters of the configurable task types added in T46-l
        // (group_add/group_msgs/race/referrals/play_games/win_games):
        // count, race action/hours/place/race_key and the race start anchor.
        self::addColumnIfMissing('tasks', 'task_config', "TEXT NULL DEFAULT NULL");

        // v3.3 (T46-l): task progress-event dedupe. One row per counted
        // progress unit; the UNIQUE(event_key) index makes every counting
        // hook idempotent, so webhook redelivery can never double-count.
        //   event keys used:
        //     gadd:<taskId>:<adderId>:<addedId>    — group_add progress
        //     gmsg:<taskId>:<chatId>:<msgId>       — group_msgs progress
        //     gaddr:<raceKey>:<adderId>:<addedId>  — race(add) progress
        //     racefin:<raceKey>:<finisherId>       — race finisher marker
        $db->exec("CREATE TABLE IF NOT EXISTS `task_events` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `task_id` INT NOT NULL,
            `user_id` BIGINT NOT NULL DEFAULT 0,
            `event_key` VARCHAR(191) NOT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE INDEX `idx_event_key` (`event_key`),
            INDEX `idx_task_user` (`task_id`, `user_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // ── user_tasks ──────────────────────────────────────────────
        $db->exec("CREATE TABLE IF NOT EXISTS `user_tasks` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `user_id` BIGINT NOT NULL,
            `task_id` INT NOT NULL,
            `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
            `answer_text` TEXT NULL DEFAULT NULL,
            `answer_file_id` VARCHAR(512) NULL DEFAULT NULL,
            `admin_id` BIGINT NULL DEFAULT NULL,
            `reviewed_at` TIMESTAMP NULL DEFAULT NULL,
            `reward_granted` TINYINT(1) NOT NULL DEFAULT 0,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `completed_at` TIMESTAMP NULL DEFAULT NULL,
            UNIQUE INDEX `idx_user_task` (`user_id`, `task_id`),
            INDEX `idx_task_id` (`task_id`),
            INDEX `idx_status` (`status`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // ── satisfactions ───────────────────────────────────────────
        $db->exec("CREATE TABLE IF NOT EXISTS `satisfactions` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `user_id` BIGINT NOT NULL,
            `withdrawal_id` BIGINT NOT NULL,
            `content_type` VARCHAR(20) NOT NULL DEFAULT 'text',
            `text` TEXT NULL DEFAULT NULL,
            `file_id` VARCHAR(512) NULL DEFAULT NULL,
            `file_path` VARCHAR(512) NULL DEFAULT NULL,
            `tg_message_id` BIGINT NULL DEFAULT NULL,
            `tg_chat_id` BIGINT NULL DEFAULT NULL,
            `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
            `admin_comment` TEXT NULL DEFAULT NULL,
            `admin_id` BIGINT NULL DEFAULT NULL,
            `reviewed_at` TIMESTAMP NULL DEFAULT NULL,
            `channel_message_id` BIGINT NULL DEFAULT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_user_id` (`user_id`),
            INDEX `idx_withdrawal_id` (`withdrawal_id`),
            INDEX `idx_status` (`status`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // ── game_sessions ───────────────────────────────────────────
        $db->exec("CREATE TABLE IF NOT EXISTS `game_sessions` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `game_type` VARCHAR(30) NOT NULL DEFAULT 'tictactoe',
            `mode` VARCHAR(20) NOT NULL,
            `player1_id` BIGINT NOT NULL,
            `player2_id` BIGINT NULL DEFAULT NULL,
            `group_id` BIGINT NULL DEFAULT NULL,
            `inline_msg_id` BIGINT NULL DEFAULT NULL,
            `inline_chat_id` BIGINT NULL DEFAULT NULL,
            `board_state` JSON NULL DEFAULT NULL,
            `current_player` TINYINT NOT NULL DEFAULT 1,
            `status` VARCHAR(20) NOT NULL DEFAULT 'waiting',
            `winner_id` BIGINT NULL DEFAULT NULL,
            `reward_type` VARCHAR(20) NULL DEFAULT NULL,
            `reward_amount` INT NOT NULL DEFAULT 0,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX `idx_player1` (`player1_id`),
            INDEX `idx_player2` (`player2_id`),
            INDEX `idx_group_id` (`group_id`),
            INDEX `idx_status` (`status`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        self::addColumnIfMissing('game_sessions', 'p1_board_msg_id', "BIGINT NULL DEFAULT NULL");
        self::addColumnIfMissing('game_sessions', 'p2_board_msg_id', "BIGINT NULL DEFAULT NULL");
        // v2.5: matchmaking gender preference (0=any, 1=boy, 2=girl)
        self::addColumnIfMissing('game_sessions', 'match_gender', "TINYINT NOT NULL DEFAULT 0");
        // v2.5: cancel request flow — which player requested cancel (0=none, 1=p1, 2=p2)
        self::addColumnIfMissing('game_sessions', 'cancel_requested_by', "TINYINT NOT NULL DEFAULT 0");
        // v2.9: last activity timestamp (for inactivity timeout)
        self::addColumnIfMissing('game_sessions', 'last_activity', "INT NOT NULL DEFAULT 0");
        // v2.9.2: multi-round support — total rounds to play (default 1 = single round)
        self::addColumnIfMissing('game_sessions', 'total_rounds', "INT NOT NULL DEFAULT 1");
        self::addColumnIfMissing('game_sessions', 'current_round', "INT NOT NULL DEFAULT 1");
        self::addColumnIfMissing('game_sessions', 'p1_round_wins', "INT NOT NULL DEFAULT 0");
        self::addColumnIfMissing('game_sessions', 'p2_round_wins', "INT NOT NULL DEFAULT 0");

        // v2.9.33: Multi-player game_players table for Truth or Dare
        self::createGamePlayersTable();

        // ── T46-m: Mafia voice game tables ──────────────────────────
        // mafia_games: one row per mafia game (state machine).
        $db->exec("CREATE TABLE IF NOT EXISTS `mafia_games` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `group_id` BIGINT NOT NULL,
            `creator_id` BIGINT NOT NULL DEFAULT 0,
            `status` VARCHAR(24) NOT NULL DEFAULT 'lobby',
            `round` INT NOT NULL DEFAULT 0,
            `lobby_msg_id` BIGINT NULL DEFAULT NULL,
            `board_msg_id` BIGINT NULL DEFAULT NULL,
            `config` TEXT NULL DEFAULT NULL,
            `phase_until` INT NOT NULL DEFAULT 0,
            `result` VARCHAR(12) NULL DEFAULT NULL,
            `stake` INT NOT NULL DEFAULT 0,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX `idx_group_status` (`group_id`, `status`),
            INDEX `idx_status` (`status`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // mafia_players: joiners with role + per-game flags. The UNIQUE index
        // makes the join path idempotent against webhook redelivery.
        $db->exec("CREATE TABLE IF NOT EXISTS `mafia_players` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `game_id` BIGINT NOT NULL,
            `user_id` BIGINT NOT NULL,
            `display_name` VARCHAR(191) NOT NULL DEFAULT '',
            `role` VARCHAR(16) NOT NULL DEFAULT 'citizen',
            `alive` TINYINT(1) NOT NULL DEFAULT 1,
            `stake_paid` TINYINT(1) NOT NULL DEFAULT 0,
            `paid_out` TINYINT(1) NOT NULL DEFAULT 0,
            `doctor_self_left` TINYINT NOT NULL DEFAULT 1,
            `pro_shot_used` TINYINT(1) NOT NULL DEFAULT 0,
            `role_pm_sent` TINYINT(1) NOT NULL DEFAULT 0,
            UNIQUE INDEX `idx_game_user` (`game_id`, `user_id`),
            INDEX `idx_game_role` (`game_id`, `role`, `alive`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // mafia_actions: one row per (game, round, actor, action). The UNIQUE
        // index makes every vote/save/check/shoot idempotent — upserts via
        // ON DUPLICATE KEY UPDATE keep the latest target.
        $db->exec("CREATE TABLE IF NOT EXISTS `mafia_actions` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `game_id` BIGINT NOT NULL,
            `round` INT NOT NULL,
            `actor_id` BIGINT NOT NULL,
            `action` VARCHAR(16) NOT NULL,
            `target_id` BIGINT NOT NULL DEFAULT 0,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE INDEX `idx_unique_action` (`game_id`, `round`, `actor_id`, `action`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // ── T46-ae (round 46): player meta — likes/MVP + challenges ──
        // mafia_likes: one like per voter per finished game (changeable
        // until the MVP resolves). The UNIQUE index makes re-taps and
        // webhook redelivery a no-op upsert.
        $db->exec("CREATE TABLE IF NOT EXISTS `mafia_likes` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `game_id` BIGINT NOT NULL,
            `voter_id` BIGINT NOT NULL,
            `target_id` BIGINT NOT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE INDEX `idx_like_voter` (`game_id`, `voter_id`),
            INDEX `idx_like_target` (`game_id`, `target_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // mafia_challenges: accepted challenges only (rows are created by
        // the player's explicit «قبول می‌کنم» tap; MafiaMeta::bump is
        // UPDATE-only so progress never accrues for unaccepted challenges).
        $db->exec("CREATE TABLE IF NOT EXISTS `mafia_challenges` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `user_id` BIGINT NOT NULL,
            `ch_key` VARCHAR(32) NOT NULL,
            `progress` INT NOT NULL DEFAULT 0,
            `goal` INT NOT NULL DEFAULT 1,
            `completed` TINYINT(1) NOT NULL DEFAULT 0,
            `rewarded` TINYINT(1) NOT NULL DEFAULT 0,
            `accepted_at` INT NOT NULL DEFAULT 0,
            `completed_at` INT NOT NULL DEFAULT 0,
            UNIQUE INDEX `idx_ch_user_key` (`user_id`, `ch_key`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // MVP stamp + the persisted end-board text (the MVP line is appended
        // to the SAME board message later, never a second group message).
        self::addColumnIfMissing('mafia_games', 'mvp_user_id', "BIGINT NULL DEFAULT NULL");
        self::addColumnIfMissing('mafia_games', 'mvp_likes', "INT NOT NULL DEFAULT 0");
        self::addColumnIfMissing('mafia_games', 'result_board', "MEDIUMTEXT NULL DEFAULT NULL");

        // mtproto_jobs: command queue consumed by mtproto/worker.php (the
        // MadelineProto userbot daemon). The webhook-side bot only INSERTs;
        // the worker claims jobs with a guarded UPDATE.
        $db->exec("CREATE TABLE IF NOT EXISTS `mtproto_jobs` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `job_type` VARCHAR(24) NOT NULL,
            `payload` TEXT NULL DEFAULT NULL,
            `status` VARCHAR(12) NOT NULL DEFAULT 'queued',
            `result` TEXT NULL DEFAULT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `executed_at` TIMESTAMP NULL DEFAULT NULL,
            INDEX `idx_status` (`status`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // ── Admin-side tables (created by AdminDB::createSchema) ────
        // admins, audit_log, event_log, support_tickets, support_messages,
        // broadcasts, api_keys, api_logs — left to AdminDB.

        // ── bot_verifications (v2.1: previous-bot start check) ──────
        $db->exec("CREATE TABLE IF NOT EXISTS `bot_verifications` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `bot_username` VARCHAR(255) NOT NULL,
            `api_url` VARCHAR(512) NOT NULL,
            `api_key` VARCHAR(255) NOT NULL,
            `mode` VARCHAR(16) NOT NULL DEFAULT 'mandatory',
            `task_reward_type` VARCHAR(20) NULL DEFAULT NULL,
            `task_reward_amount` INT NULL DEFAULT NULL,
            `task_id` INT NULL DEFAULT NULL,
            `is_active` TINYINT(1) NOT NULL DEFAULT 1,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_mode` (`mode`),
            INDEX `idx_active` (`is_active`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    // v2.9.45 (R6-79): refresh the TTL marker AFTER a successful full probe.
        try {
            $db->prepare("INSERT INTO `bot_settings` (`setting_key`, `setting_value`) VALUES ('schema_probe_at', :v)
                ON DUPLICATE KEY UPDATE `setting_value` = :v2")
               ->execute([':v' => (string)time(), ':v2' => (string)time()]);
        } catch (\Throwable $e) {
            error_log('[createSchema] marker write failed: ' . $e->getMessage());
        }
    }

    /**
     * F-A13: request-scoped SHOW COLUMNS probe. createSchema() probes the
     * same tables ~30 times on EVERY request (Database::init → createSchema)
     * and getActiveChannels()/support readers re-probe `channels` several
     * times per call — each probe used to be a full SHOW COLUMNS round-trip.
     * The cache lives for the CURRENT REQUEST ONLY (a new request starts
     * empty): no persistence semantics change across requests. A successful
     * ADD COLUMN appends to the cached list so later probes in the same
     * request still see it; a failed probe is NOT cached (next call re-probes,
     * exactly like the uncached code).
     */
    private static function getTableColumns(string $table): ?array
    {
        if (array_key_exists($table, self::$checkedColumns)) {
            return self::$checkedColumns[$table];
        }
        try {
            $cols = self::getConnection()->query("SHOW COLUMNS FROM `{$table}`")->fetchAll(PDO::FETCH_COLUMN);
        } catch (\Throwable $e) {
            // Probe failed (table not created yet / server hiccup) — do NOT
            // cache the failure; the next call re-probes as before.
            return null;
        }
        self::$checkedColumns[$table] = $cols;
        return $cols;
    }

    private static function addColumnIfMissing(string $table, string $column, string $definition): void
    {
        $db = self::getConnection();
        try {
            $cols = self::getTableColumns($table);
            if ($cols === null) {
                throw new \RuntimeException("SHOW COLUMNS FROM `{$table}` failed");
            }
            if (!in_array($column, $cols, true)) {
                $db->exec("ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}");
                error_log("[DB] Added column {$table}.{$column}");
                // F-A13: keep the request-scoped cache truthful after the ALTER.
                self::$checkedColumns[$table][] = $column;
            }
        } catch (\Throwable $e) {
            error_log("addColumnIfMissing({$table}.{$column}) failed: " . $e->getMessage());
        }
    }

    private static function addIndexIfMissing(string $table, string $indexName, string $column): void
    {
        $db = self::getConnection();
        try {
            $stmt = $db->prepare("SHOW INDEX FROM `{$table}` WHERE Key_name = ?");
            $stmt->execute([$indexName]);
            if ($stmt->fetch() === false) {
                $db->exec("ALTER TABLE `{$table}` ADD INDEX `{$indexName}` (`{$column}`)");
            }
        } catch (\Throwable $e) {
            // index may already exist silently
        }
    }

    /**
     * F11: composite (multi-column) variant of addIndexIfMissing. Used for
     * the (tg_chat_id, tg_message_id) message-dedup indexes; silently skips
     * when the table does not exist yet (support_messages is AdminDB-owned).
     */
    private static function addCompositeIndexIfMissing(string $table, string $indexName, array $columns): void
    {
        $db = self::getConnection();
        foreach ($columns as $c) {
            if (!preg_match('/^[a-z_][a-z0-9_]*$/i', (string)$c)) {
                throw new \InvalidArgumentException("Invalid index column: {$c}");
            }
        }
        $cols = implode('`, `', array_map('strval', $columns));
        try {
            $stmt = $db->prepare("SHOW INDEX FROM `{$table}` WHERE Key_name = ?");
            $stmt->execute([$indexName]);
            if ($stmt->fetch() === false) {
                $db->exec("ALTER TABLE `{$table}` ADD INDEX `{$indexName}` (`{$cols}`)");
            }
        } catch (\Throwable $e) {
            // table not created yet (AdminDB-owned) or index already present
        }
    }

    public static function columnExists(string $table, string $column): bool
    {
        // F-A13: served from the per-request SHOW COLUMNS cache.
        $cols = self::getTableColumns($table);
        return $cols !== null && in_array($column, $cols, true);
    }

    // ─── User Methods ──────────────────────────────────────────────

    /**
     * Persist a user's row. v2: persists currency balances + satisfaction
     * state. The user_servers table is no longer touched (VPN removed).
     *
     * INVARIANT: balance columns (storage / toman / ntx_balance) are NOT
     * written on UPDATE — balances may only ever change through the atomic
     * Database::adjustBalance() (via Wallet::credit/debit). Most callers hold
     * a stale user snapshot; persisting balances here would silently revert
     * concurrent Wallet operations. Balances ARE included when the INSERT
     * creates a brand-new user row (default 0). The only legitimate exception
     * is the JSON→MySQL import (migrate.php), which passes $persistBalances=true.
     */
    public static function saveUser(int $userId, array $userData, bool $persistBalances = false): void
    {
        $db = self::getConnection();

        $stmt = $db->prepare("INSERT INTO `users` (
                `user_id`, `status`, `referral_id`, `storage`, `toman`, `ntx_balance`,
                `sub_count`, `last_reward`, `is_first_time`, `display_name`,
                `satisfaction_pending`, `pending_withdrawal_id`, `gender`, `banned`
            ) VALUES (
                :user_id, :status, :referral_id, :storage, :toman, :ntx_balance,
                :sub_count, :last_reward, :is_first_time, :display_name,
                :satisfaction_pending, :pending_withdrawal_id, :gender, :banned
            )
            ON DUPLICATE KEY UPDATE
                `status` = VALUES(`status`),
                `referral_id` = VALUES(`referral_id`),
                `sub_count` = VALUES(`sub_count`),
                `last_reward` = VALUES(`last_reward`),
                `is_first_time` = VALUES(`is_first_time`),
                `satisfaction_pending` = VALUES(`satisfaction_pending`),
                `pending_withdrawal_id` = VALUES(`pending_withdrawal_id`),
                `gender` = VALUES(`gender`),
                `banned` = VALUES(`banned`)"
            // Legacy mode (JSON import only): also persist the balance columns.
            . ($persistBalances ? ",
                `storage` = VALUES(`storage`),
                `toman` = VALUES(`toman`),
                `ntx_balance` = VALUES(`ntx_balance`)" : "")
            . (isset($userData['displayName']) ? ", `display_name` = VALUES(`display_name`)" : ""));

        $stmt->execute([
            ':user_id'       => $userId,
            ':status'        => $userData['status'] ?? 'main',
            ':referral_id'   => $userData['referralId'] ?? self::DEFAULT_REFERRAL_ID,
            ':storage'       => $userData['gameTokens'] ?? $userData['storage'] ?? 0,
            ':toman'         => $userData['toman'] ?? 0,
            ':ntx_balance'   => $userData['ntxBalance'] ?? 0,
            ':sub_count'     => $userData['sub_count'] ?? 0,
            ':last_reward'   => $userData['last_reward'] ?? 0,
            ':is_first_time' => ($userData['isFirstTime'] ?? true) ? 1 : 0,
            ':display_name'  => $userData['displayName'] ?? 'user',
            ':satisfaction_pending' => !empty($userData['satisfactionPending']) ? 1 : 0,
            ':pending_withdrawal_id' => $userData['pendingWithdrawalId'] ?? null,
            ':gender'        => $userData['gender'] ?? 0,
            ':banned'        => !empty($userData['banned']) ? 1 : 0,
        ]);
    }

    public static function loadUser(int $userId): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `users` WHERE `user_id` = :user_id LIMIT 1");
        $stmt->execute([':user_id' => $userId]);
        $row = $stmt->fetch();
        if (!$row) return null;

        return [
            'status'              => $row['status'],
            'referralId'          => (int)$row['referral_id'],
            'gameTokens'          => (int)$row['storage'],   // storage == game_tokens
            'storage'             => (int)$row['storage'],    // alias for backward compat
            'toman'               => (int)$row['toman'],
            'ntxBalance'          => (int)$row['ntx_balance'],
            'sub_count'           => (int)$row['sub_count'],
            'last_reward'         => (int)$row['last_reward'],
            'isFirstTime'         => (bool)$row['is_first_time'],
            'displayName'         => $row['display_name'] ?? 'user',
            'satisfactionPending' => (bool)$row['satisfaction_pending'],
            'pendingWithdrawalId' => $row['pending_withdrawal_id'] !== null ? (int)$row['pending_withdrawal_id'] : null,
            'gender'              => (int)($row['gender'] ?? 0),
            'banned'              => (bool)($row['banned'] ?? 0),
        ];
    }

    /**
     * F6 (lost-update family): targeted update of a SMALL set of user flag
     * columns. Unlike saveUser() — which persists a whole (stale) snapshot —
     * this touches ONLY the listed columns, so concurrent balance changes or
     * flag writes by other requests are never clobbered. Use it for flag
     * writes (satisfaction gate, pending-withdrawal pointer, gender, status,
     * first-time flag) instead of load→saveUser round-trips.
     *
     * Column whitelist (EXACTLY): satisfaction_pending, pending_withdrawal_id,
     * is_first_time, gender, status. Anything else throws.
     *
     * @param array<string,int|string|bool|null> $fields column => value
     * @return bool Always true on success (rowCount 0 — e.g. the same values
     *         written again — counts as OK; a DB error throws).
     */
    public static function updateUserColumns(int $userId, array $fields): bool
    {
        // v2.9.44 (T19-5): display_name added — full-row saveUser() round-trips
        // a stale snapshot and can resurrect satisfaction_pending/banned flags
        // an admin just cleared (or unban a banned user). Name updates must be
        // targeted single-column writes.
        $allowed = ['satisfaction_pending', 'pending_withdrawal_id', 'is_first_time', 'gender', 'status', 'display_name'];
        $sets = [];
        $params = [':uid' => $userId];
        $i = 0;
        foreach ($fields as $col => $value) {
            if (!in_array($col, $allowed, true)) {
                throw new \InvalidArgumentException("Invalid user column for targeted update: {$col}");
            }
            $ph = ':col' . $i++;
            $sets[] = "`{$col}` = {$ph}";
            $params[$ph] = is_bool($value) ? ((int)$value) : $value;
        }
        if ($sets === []) {
            return true;
        }
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `users` SET " . implode(', ', $sets) . " WHERE `user_id` = :uid");
        $stmt->execute($params);
        return true;
    }

    /**
     * F7: atomic counter increment in a single statement (`col = col + :by`)
     * so concurrent saveUser() snapshots can no longer revert the counter.
     * Column whitelist (EXACTLY): sub_count.
     *
     * @return bool true when a row was incremented, false for unknown user.
     */
    public static function incrementUserColumn(int $userId, string $column, int $by = 1): bool
    {
        $allowed = ['sub_count'];
        if (!in_array($column, $allowed, true)) {
            throw new \InvalidArgumentException("Invalid counter column: {$column}");
        }
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `users` SET `{$column}` = `{$column}` + :by WHERE `user_id` = :uid");
        $stmt->execute([':by' => $by, ':uid' => $userId]);
        return $stmt->rowCount() > 0;
    }

    /**
     * F8: create a brand-new user row — a plain INSERT with NO upsert, for
     * duplicate-webhook hardening on registration.
     *
     * CONTRACT (for MessageHandler::registerNewUser and any other caller):
     * returns true ONLY for the request that actually created the row; on a
     * duplicate user_id (PDO SQLSTATE 23000 / MySQL 1062) it returns FALSE —
     * use that to gate one-time side effects (referral payment, first-time
     * flags). Other PDO failures are logged and return false as well
     * (registration resilience — the webhook must degrade to a graceful
     * "welcome back / bail" path instead of crashing).
     *
     * $data accepts the same camelCase keys saveUser() maps (user_id,
     * status, referralId, isFirstTime, displayName, satisfactionPending,
     * pendingWithdrawalId, gender, banned, sub_count, last_reward; 'userId'
     * is accepted as an alias for 'user_id'). Balances always start at 0 —
     * they may only ever change through adjustBalance/Wallet.
     *
     * @param array<string,mixed> $data
     */
    public static function createUser(array $data): bool
    {
        $userId = (int)($data['user_id'] ?? $data['userId'] ?? 0);
        if ($userId <= 0) {
            throw new \InvalidArgumentException('Database::createUser: $data must contain a positive user_id.');
        }

        $db = self::getConnection();
        try {
            $stmt = $db->prepare("INSERT INTO `users` (
                    `user_id`, `status`, `referral_id`, `storage`, `toman`, `ntx_balance`,
                    `sub_count`, `last_reward`, `is_first_time`, `display_name`,
                    `satisfaction_pending`, `pending_withdrawal_id`, `gender`, `banned`
                ) VALUES (
                    :user_id, :status, :referral_id, 0, 0, 0,
                    :sub_count, :last_reward, :is_first_time, :display_name,
                    :satisfaction_pending, :pending_withdrawal_id, :gender, :banned
                )");
            $stmt->execute([
                ':user_id'       => $userId,
                ':status'        => $data['status'] ?? 'main',
                ':referral_id'   => $data['referralId'] ?? self::DEFAULT_REFERRAL_ID,
                ':sub_count'     => $data['sub_count'] ?? 0,
                ':last_reward'   => $data['last_reward'] ?? 0,
                ':is_first_time' => ($data['isFirstTime'] ?? true) ? 1 : 0,
                ':display_name'  => $data['displayName'] ?? 'user',
                ':satisfaction_pending' => !empty($data['satisfactionPending']) ? 1 : 0,
                ':pending_withdrawal_id' => $data['pendingWithdrawalId'] ?? null,
                ':gender'        => $data['gender'] ?? 0,
                ':banned'        => !empty($data['banned']) ? 1 : 0,
            ]);
        } catch (PDOException $e) {
            if ($e->getCode() === '23000') {
                // 23000 = integrity constraint violation (duplicate user_id).
                return false;
            }
            // F8: any other PDO failure (deadlock, server gone away, …) is
            // logged and reported as "not created" — registration must fail
            // gracefully instead of crashing the webhook; the caller treats
            // false as "row not visible / not mine", never as success.
            error_log('[DB] createUser failed for user ' . $userId . ': ' . $e->getMessage());
            return false;
        }
        return true;
    }

    /**
     * F13: atomic claim of the DAILY-REWARD window. Consumes the reward day
     * in ONE guarded UPDATE so two taps (or two webhook deliveries of the
     * same tap) can never both pass the cooldown check and both credit.
     *
     * The UPDATE only fires while the stored last_reward snapshot the caller
     * loaded still matches — the loser of the race matches 0 rows and gets
     * false (no second payout). The `IS NULL` disjunct is defensive: the
     * schema keeps last_reward BIGINT NOT NULL DEFAULT 0, but migrated rows
     * or future schema drift must not wedge the claim.
     *
     * CONTRACT (CallbackHandler::finishReward): pass the loadUser() snapshot
     * value as $expectedLastReward (int per loadUser's cast, null accepted)
     * and time() as $now; true ⇔ THIS request won the day.
     *
     * @param int|string|null $expectedLastReward the last_reward value the
     *        caller based its "cooldown elapsed" decision on (the loadUser
     *        snapshot; fresh rows hold 0).
     */
    public static function claimDailyReward(int $userId, int|string|null $expectedLastReward, int $now): bool
    {
        $db = self::getConnection();
        // v2.9.48 (R10): HY093 fix — with PDO::ATTR_EMULATE_PREPARES=false the
        // MySQL native driver rejects a named placeholder used TWICE in one
        // statement ("Invalid parameter number"), so EVERY daily-reward claim
        // died on this UPDATE and the user got «خطایی رخ داد» (logged
        // [CALLBACK] dispatch error: SQLSTATE[HY093]). Bind the same value
        // under two distinct names instead.
        $stmt = $db->prepare("UPDATE `users` SET `last_reward` = :now
            WHERE `user_id` = :uid
              AND (`last_reward` = :expected OR (`last_reward` IS NULL AND :expected2 IS NULL))");
        $stmt->execute([
            ':now'       => $now,
            ':uid'       => $userId,
            ':expected'  => $expectedLastReward,
            ':expected2' => $expectedLastReward,
        ]);
        return $stmt->rowCount() === 1;
    }
    /**
     * v2.9.45 (R6-62/L2): roll the daily-reward cooldown back to its previous
     * value after a failed credit so the user does not lose their day.
     * (last_reward is a unix timestamp; NULL/0 = no cooldown set.)
     */
    public static function restoreDailyReward(int $userId, int|string|null $previousValue): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `users` SET `last_reward` = :lr WHERE `user_id` = :uid");
        $stmt->execute([':lr' => ($previousValue !== null && (int)$previousValue > 0) ? (int)$previousValue : 0, ':uid' => $userId]);
    }


    /**
     * F-A4 (contract for agent 11-b): atomic consume of the one-time
     * first-start flag. Sets is_first_time = 0 ONLY while it is still 1 and
     * returns true iff THIS request consumed it (rowCount === 1). The loser
     * of a concurrent /start race gets false and must NOT pay the referral
     * (the old blind "isFirstTime=false; saveUser()" clear-points let two
     * concurrent deliveries both pass the flag check and both pay).
     *
     * CONTRACT (MessageHandler): call INSTEAD of the saveUser clear-points —
     * pay Invite::processNewUserReferral ONLY when this returns true
     * (late-pay site ~277-284 and registerNewUser ~434-437).
     */
    public static function consumeFirstTimeFlag(int $userId): bool
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `users` SET `is_first_time` = 0
            WHERE `user_id` = :uid AND `is_first_time` = 1");
        $stmt->execute([':uid' => $userId]);
        return $stmt->rowCount() === 1;
    }

    public static function getTopReferrals(int $limit = 10, ?int $excludeUserId = null): array
    {
        $db = self::getConnection();
        // v2.9.45 (R6-94/LOW): banned users excluded + stable tie-breaker
        // (sub_count DESC, user_id ASC) so ranking doesn't shuffle per request.
        if ($excludeUserId !== null) {
            $stmt = $db->prepare("SELECT `user_id`, `sub_count` FROM `users` WHERE `user_id` != :excl AND `sub_count` > 0 AND `banned` = 0 ORDER BY `sub_count` DESC, `user_id` ASC LIMIT :limit");
            $stmt->bindValue(':excl', $excludeUserId, PDO::PARAM_INT);
        } else {
            $stmt = $db->prepare("SELECT `user_id`, `sub_count` FROM `users` WHERE `sub_count` > 0 AND `banned` = 0 ORDER BY `sub_count` DESC, `user_id` ASC LIMIT :limit");
        }
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public static function getAllUserIds(): array
    {
        $db = self::getConnection();
        return $db->query("SELECT `user_id` FROM `users`")->fetchAll(PDO::FETCH_COLUMN);
    }

    public static function getUsersWithoutRewardToday(int $todayStart): array
    {
        $db = self::getConnection();
        // F-A10: banned users must never receive the daily-reward reminder.
        $stmt = $db->prepare("SELECT `user_id` FROM `users` WHERE `last_reward` < :ts AND `banned` = 0");
        $stmt->execute([':ts' => $todayStart]);
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }

    /**
     * Increment a currency balance atomically.
     *
     * Positive delta: upsert (creates the user row if missing) — always
     * succeeds and returns the new balance.
     *
     * Negative delta: guarded atomic UPDATE (`col = col + delta WHERE
     * col + delta >= 0`) so concurrent webhook requests can never double-spend
     * or drive a balance below zero. Returns the new balance, or false when
     * the guard rejected the update (insufficient funds, or the user row does
     * not exist).
     *
     * @return int|false New balance, or false on rejected negative delta.
     */
    public static function adjustBalance(int $userId, string $column, int $delta): int|false
    {
        $db = self::getConnection();
        // Whitelist column names to prevent SQL injection.
        $allowed = ['storage', 'toman', 'ntx_balance'];
        if (!in_array($column, $allowed, true)) {
            throw new \InvalidArgumentException("Invalid currency column: {$column}");
        }

        if ($delta < 0) {
            // Guarded atomic decrement: applies only when the result stays >= 0.
            $stmt = $db->prepare("UPDATE `users` SET `{$column}` = `{$column}` + :delta
                WHERE `user_id` = :uid AND `{$column}` + :delta2 >= 0");
            $stmt->execute([':delta' => $delta, ':uid' => $userId, ':delta2' => $delta]);
            if ($stmt->rowCount() === 0) {
                return false; // insufficient funds or unknown user
            }
        } else {
            // Positive delta (or zero): upsert — creates the row if missing.
            $stmt = $db->prepare("INSERT INTO `users` (`user_id`, `{$column}`) VALUES (:uid, :delta)
                ON DUPLICATE KEY UPDATE `{$column}` = `{$column}` + :delta2");
            $stmt->execute([':uid' => $userId, ':delta' => $delta, ':delta2' => $delta]);
        }

        // Read back the new balance.
        $stmt = $db->prepare("SELECT `{$column}` FROM `users` WHERE `user_id` = :uid");
        $stmt->execute([':uid' => $userId]);
        $row = $stmt->fetch();
        return $row ? (int)$row[$column] : 0;
    }

    /** Set a currency balance to an absolute value (used by admin). */
    public static function setBalance(int $userId, string $column, int $value): void
    {
        $db = self::getConnection();
        $allowed = ['storage', 'toman', 'ntx_balance'];
        if (!in_array($column, $allowed, true)) {
            throw new \InvalidArgumentException("Invalid currency column: {$column}");
        }
        $stmt = $db->prepare("UPDATE `users` SET `{$column}` = :val WHERE `user_id` = :uid");
        $stmt->execute([':val' => $value, ':uid' => $userId]);
    }

    // ─── Channel Methods ───────────────────────────────────────────

    public static function getActiveChannels(): array
    {
        $db = self::getConnection();
        $hasNewCols = self::columnExists('channels', 'display_name')
                   && self::columnExists('channels', 'channel_type')
                   && self::columnExists('channels', 'numeric_id')
                   && self::columnExists('channels', 'join_link');

        if ($hasNewCols) {
            $hasModeCol = self::columnExists('channels', 'channel_mode');
            $modeCol = $hasModeCol ? '`channel_mode`' : "'mandatory' AS channel_mode";
            $taskCols = self::columnExists('channels', 'task_reward_type') ? '`task_reward_type`, `task_reward_amount`, `task_id`' : "NULL AS task_reward_type, NULL AS task_reward_amount, NULL AS task_id";
            $stmt = $db->query("SELECT `id`, `channel_handle`, `display_name`, `channel_type`, `numeric_id`, `join_link`, `is_active`, {$modeCol}, {$taskCols} FROM `channels` WHERE `is_active` = 1 ORDER BY `id`");
            return $stmt->fetchAll();
        }
        $stmt = $db->query("SELECT `channel_handle` FROM `channels` WHERE `is_active` = 1 ORDER BY `id`");
        $rows = [];
        foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $handle) {
            $rows[] = [
                'channel_handle' => $handle,
                'display_name'   => null,
                'channel_type'   => 'public',
                'numeric_id'     => null,
                'join_link'      => null,
                'channel_mode'   => 'mandatory',
                'task_reward_type' => null,
                'task_reward_amount' => null,
                'task_id' => null,
            ];
        }
        return $rows;
    }

    /** Return only mandatory-join channels (the gate for bot usage). */
    public static function getMandatoryChannels(): array
    {
        $all = self::getActiveChannels();
        return array_values(array_filter($all, fn($c) => ($c['channel_mode'] ?? 'mandatory') === 'mandatory'));
    }

    /** Return only task-mode channels (optional, reward-granting). */
    public static function getTaskChannels(): array
    {
        $all = self::getActiveChannels();
        return array_values(array_filter($all, fn($c) => ($c['channel_mode'] ?? 'mandatory') === 'task'));
    }

    public static function getChannelById(int $id): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `channels` WHERE `id` = :id LIMIT 1");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function getChannelByHandle(string $handle): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `channels` WHERE `channel_handle` = :h LIMIT 1");
        $stmt->execute([':h' => $handle]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function addChannel(string $channelHandle, ?string $displayName = null, string $channelType = 'public', ?string $numericId = null, ?string $joinLink = null, string $channelMode = 'mandatory', ?string $taskRewardType = null, ?int $taskRewardAmount = null): int
    {
        $db = self::getConnection();
        $hasNewCols = self::columnExists('channels', 'display_name');
        $hasModeCol = self::columnExists('channels', 'channel_mode');
        if ($hasNewCols && $hasModeCol) {
            $stmt = $db->prepare("INSERT INTO `channels` (`channel_handle`, `display_name`, `channel_type`, `numeric_id`, `join_link`, `is_active`, `channel_mode`, `task_reward_type`, `task_reward_amount`)
                VALUES (:handle, :name, :type, :num, :link, 1, :mode, :trt, :tra)
                ON DUPLICATE KEY UPDATE
                    `display_name` = VALUES(`display_name`),
                    `channel_type` = VALUES(`channel_type`),
                    `numeric_id`   = VALUES(`numeric_id`),
                    `join_link`    = VALUES(`join_link`),
                    `is_active`    = 1,
                    `channel_mode` = VALUES(`channel_mode`),
                    `task_reward_type` = VALUES(`task_reward_type`),
                    `task_reward_amount` = VALUES(`task_reward_amount`)");
            $stmt->execute([
                ':handle' => $channelHandle, ':name' => $displayName, ':type' => $channelType,
                ':num' => $numericId, ':link' => $joinLink, ':mode' => $channelMode,
                ':trt' => $taskRewardType, ':tra' => $taskRewardAmount,
            ]);
            return (int)$db->lastInsertId();
        }
        $stmt = $db->prepare("INSERT IGNORE INTO `channels` (`channel_handle`) VALUES (:handle)");
        $stmt->execute([':handle' => $channelHandle]);
        return (int)$db->lastInsertId();
    }

    public static function updateChannelMode(int $channelId, string $mode, ?string $rewardType = null, ?int $rewardAmount = null): void
    {
        $db = self::getConnection();
        if (!self::columnExists('channels', 'channel_mode')) return;
        $stmt = $db->prepare("UPDATE `channels` SET `channel_mode` = :mode, `task_reward_type` = :rt, `task_reward_amount` = :ra WHERE `id` = :id");
        $stmt->execute([':mode' => $mode, ':rt' => $rewardType, ':ra' => $rewardAmount, ':id' => $channelId]);
    }

    public static function linkChannelTask(int $channelId, ?int $taskId): void
    {
        if (!self::columnExists('channels', 'task_id')) return;
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `channels` SET `task_id` = :tid WHERE `id` = :id");
        $stmt->execute([':tid' => $taskId, ':id' => $channelId]);
    }

    public static function removeChannel(string $channelHandle): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("DELETE FROM `channels` WHERE `channel_handle` = :handle");
        $stmt->execute([':handle' => $channelHandle]);
    }

    public static function setChannelActive(string $channelHandle, bool $active): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `channels` SET `is_active` = :active WHERE `channel_handle` = :handle");
        $stmt->execute([':active' => $active ? 1 : 0, ':handle' => $channelHandle]);
    }

    public static function getAllChannelsList(): array
    {
        $db = self::getConnection();
        return $db->query("SELECT * FROM `channels` ORDER BY `id`")->fetchAll();
    }

    // ─── Bot Verifications (v2.1: previous-bot start check) ─────────

    public static function addBotVerification(string $botUsername, string $apiUrl, string $apiKey, string $mode = 'mandatory', ?string $rewardType = null, ?int $rewardAmount = null): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("INSERT INTO `bot_verifications` (`bot_username`, `api_url`, `api_key`, `mode`, `task_reward_type`, `task_reward_amount`)
            VALUES (:bu, :au, :ak, :mode, :rt, :ra)");
        $stmt->execute([
            ':bu' => $botUsername, ':au' => $apiUrl, ':ak' => $apiKey,
            ':mode' => $mode, ':rt' => $rewardType, ':ra' => $rewardAmount,
        ]);
        return (int)$db->lastInsertId();
    }

    public static function getBotVerification(int $id): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `bot_verifications` WHERE `id` = :id LIMIT 1");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function getAllBotVerifications(): array
    {
        $db = self::getConnection();
        return $db->query("SELECT * FROM `bot_verifications` ORDER BY `id`")->fetchAll();
    }

    public static function getMandatoryBotVerifications(): array
    {
        $db = self::getConnection();
        return $db->query("SELECT * FROM `bot_verifications` WHERE `is_active` = 1 AND `mode` = 'mandatory' ORDER BY `id`")->fetchAll();
    }

    public static function getTaskBotVerifications(): array
    {
        $db = self::getConnection();
        return $db->query("SELECT * FROM `bot_verifications` WHERE `is_active` = 1 AND `mode` = 'task' ORDER BY `id`")->fetchAll();
    }

    public static function updateBotVerification(int $id, string $botUsername, string $apiUrl, string $apiKey, string $mode, ?string $rewardType, ?int $rewardAmount): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `bot_verifications` SET `bot_username` = :bu, `api_url` = :au, `api_key` = :ak, `mode` = :mode, `task_reward_type` = :rt, `task_reward_amount` = :ra WHERE `id` = :id");
        $stmt->execute([
            ':bu' => $botUsername, ':au' => $apiUrl, ':ak' => $apiKey,
            ':mode' => $mode, ':rt' => $rewardType, ':ra' => $rewardAmount, ':id' => $id,
        ]);
    }

    public static function setBotVerificationActive(int $id, bool $active): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `bot_verifications` SET `is_active` = :a WHERE `id` = :id");
        $stmt->execute([':a' => $active ? 1 : 0, ':id' => $id]);
    }

    public static function deleteBotVerification(int $id): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("DELETE FROM `bot_verifications` WHERE `id` = :id");
        $stmt->execute([':id' => $id]);
    }

    public static function linkBotVerificationTask(int $botVerificationId, ?int $taskId): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `bot_verifications` SET `task_id` = :tid WHERE `id` = :id");
        $stmt->execute([':tid' => $taskId, ':id' => $botVerificationId]);
    }

    // ─── Bot Settings Methods ──────────────────────────────────────

    public static function getSetting(string $key, ?string $default = null): ?string
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT `setting_value` FROM `bot_settings` WHERE `setting_key` = :key LIMIT 1");
        $stmt->execute([':key' => $key]);
        $row = $stmt->fetch();
        return $row ? $row['setting_value'] : $default;
    }

    public static function setSetting(string $key, string $value): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("INSERT INTO `bot_settings` (`setting_key`, `setting_value`) VALUES (:key, :val) ON DUPLICATE KEY UPDATE `setting_value` = VALUES(`setting_value`)");
        $stmt->execute([':key' => $key, ':val' => $value]);
    }

    public static function deleteSetting(string $key): void
    {
        $db = self::getConnection();
        $db->prepare("DELETE FROM `bot_settings` WHERE `setting_key` = :key")->execute([':key' => $key]);
    }

    public static function getAllSettings(): array
    {
        $db = self::getConnection();
        $stmt = $db->query("SELECT `setting_key`, `setting_value` FROM `bot_settings`");
        $rows = $stmt->fetchAll();
        $out = [];
        foreach ($rows as $row) {
            $out[$row['setting_key']] = $row['setting_value'];
        }
        return $out;
    }

    // ─── Broadcasts ─────────────────────────────────────────────────

    public static function createBroadcast(int $adminId, string $adminName, string $text, string $contentType = 'text', ?string $fileId = null, ?string $caption = null): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("INSERT INTO `broadcasts` (`admin_id`, `admin_name`, `text`, `content_type`, `file_id`, `caption`) VALUES (:aid, :aname, :text, :ct, :fid, :cap)");
        $stmt->execute([':aid' => $adminId, ':aname' => $adminName, ':text' => $text, ':ct' => $contentType, ':fid' => $fileId, ':cap' => $caption]);
        return (int)$db->lastInsertId();
    }

    public static function updateBroadcastMessageIds(int $broadcastId, array $messageIds, int $totalSent): void
    {
        $db = self::getConnection();
        $db->prepare("UPDATE `broadcasts` SET `message_ids` = :mids, `total_sent` = :ts WHERE `id` = :id")
           ->execute([':mids' => json_encode($messageIds), ':ts' => $totalSent, ':id' => $broadcastId]);
    }

    public static function getBroadcasts(int $limit = 50): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `broadcasts` ORDER BY `created_at` DESC LIMIT :limit");
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public static function getBroadcast(int $id): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `broadcasts` WHERE `id` = :id LIMIT 1");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function deleteBroadcast(int $id): void
    {
        $db = self::getConnection();
        $db->prepare("DELETE FROM `broadcasts` WHERE `id` = :id")->execute([':id' => $id]);
    }

    public static function logEvent(string $eventType, ?int $userId = null, ?array $data = null): void
    {
        try {
            $db = self::getConnection();
            $db->prepare("INSERT INTO `event_log` (`event_type`, `user_id`, `data`) VALUES (:type, :uid, :data)")
               ->execute([':type' => $eventType, ':uid' => $userId, ':data' => $data ? json_encode($data) : null]);
        } catch (\Throwable $e) {
            error_log("logEvent failed: " . $e->getMessage());
        }
    }

    public static function getAdminsWithPermission(string $permission): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT `user_id`, `display_name`, `role`, `permissions`, `is_active` FROM `admins` WHERE `is_active` = 1");
        $stmt->execute();
        $result = [];
        foreach ($stmt->fetchAll() as $row) {
            if ($row['role'] === 'owner') {
                $result[] = ['user_id' => (int)$row['user_id'], 'display_name' => $row['display_name']];
            } else {
                $perms = $row['permissions'] ? json_decode($row['permissions'], true) : [];
                if (is_array($perms) && in_array($permission, $perms, true)) {
                    $result[] = ['user_id' => (int)$row['user_id'], 'display_name' => $row['display_name']];
                }
            }
        }
        return $result;
    }

    // ─── Wallet Ledger ──────────────────────────────────────────────

    public static function addWalletEntry(int $userId, string $currency, int $amount, string $direction, string $reason, ?string $refType = null, ?int $refId = null): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("INSERT INTO `wallet_ledger` (`user_id`, `currency`, `amount`, `direction`, `reason`, `ref_type`, `ref_id`)
            VALUES (:uid, :cur, :amt, :dir, :rsn, :rt, :ri)");
        $stmt->execute([
            ':uid' => $userId, ':cur' => $currency, ':amt' => abs($amount),
            ':dir' => $direction, ':rsn' => $reason, ':rt' => $refType, ':ri' => $refId,
        ]);
        return (int)$db->lastInsertId();
    }

    public static function getWalletHistory(int $userId, int $limit = 50): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `wallet_ledger` WHERE `user_id` = :uid ORDER BY `id` DESC LIMIT :limit");
        $stmt->bindValue(':uid', $userId, PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * v3.3 (T46): referral earnings summary for the «کسب درآمد رفرال» hub.
     * Sums the user's CREDITED referral payouts from wallet_ledger grouped
     * by currency (Invite::payReferrer writes reason='referral_toman' for
     * cash invites and reason='referral_token' for token invites).
     *
     * @return array<string,array{total:int,count:int}> e.g.
     *         ['toman' => ['total' => 21000, 'count' => 3],
     *          'game_token' => ['total' => 40, 'count' => 4]]
     */
    public static function getReferralEarnings(int $userId): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT `currency`, SUM(`amount`) AS `total`, COUNT(*) AS `cnt`
            FROM `wallet_ledger`
            WHERE `user_id` = :uid AND `direction` = 'credit'
              AND `reason` IN ('referral_toman', 'referral_token')
            GROUP BY `currency`");
        $stmt->bindValue(':uid', $userId, PDO::PARAM_INT);
        $stmt->execute();
        $out = [];
        foreach ($stmt->fetchAll() as $row) {
            $out[(string)$row['currency']] = [
                'total' => (int)$row['total'],
                'count' => (int)$row['cnt'],
            ];
        }
        return $out;
    }

    // ─── NTX Records ────────────────────────────────────────────────

    public static function createNtxRecord(int $userId, int $amount, string $source, ?int $sourceId = null): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("INSERT INTO `ntx_records` (`user_id`, `amount`, `source`, `source_id`) VALUES (:uid, :amt, :src, :sid)");
        $stmt->execute([':uid' => $userId, ':amt' => $amount, ':src' => $source, ':sid' => $sourceId]);
        return (int)$db->lastInsertId();
    }

    public static function getNtxRecords(int $userId, int $limit = 100): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `ntx_records` WHERE `user_id` = :uid ORDER BY `id` DESC LIMIT :limit");
        $stmt->bindValue(':uid', $userId, PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /** Return all NTX records for a user whose 24h lock has expired and are still pending. */
    public static function getConvertibleNtxRecords(int $userId, int $lockSeconds): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `ntx_records`
            WHERE `user_id` = :uid AND `status` = 'pending'
              AND UNIX_TIMESTAMP(`received_at`) <= (UNIX_TIMESTAMP(NOW()) - :lock)
            ORDER BY `received_at` ASC");
        $stmt->bindValue(':uid', $userId, PDO::PARAM_INT);
        $stmt->bindValue(':lock', $lockSeconds, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * Atomically claim a pending NTX record as 'converted'.
     *
     * The UPDATE is guarded with `AND status = 'pending'` so only ONE of two
     * concurrent conversion requests (user tap vs cron) can win. Returns true
     * when THIS call performed the transition (rowCount === 1); callers must
     * credit toman / debit ntx ONLY on true — otherwise the same record is
     * converted twice.
     */
    public static function markNtxConverted(int $recordId, int $convertedToman): bool
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `ntx_records` SET `status` = 'converted', `converted_at` = NOW(), `converted_toman` = :ct WHERE `id` = :id AND `status` = 'pending'");
        $stmt->execute([':ct' => $convertedToman, ':id' => $recordId]);
        return $stmt->rowCount() === 1;
    }

    /**
     * F10: undo a WON markNtxConverted claim when the matching ntx debit
     * failed (NtxLedger::convertAll / convertOne residual): restores the
     * record to 'pending' and clears the conversion stamp so it becomes
     * convertible again later. Guarded on status='converted'; returns true
     * only when THIS call reverted it.
     */
    public static function revertNtxConversion(int $recordId): bool
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `ntx_records` SET `status` = 'pending', `converted_at` = NULL, `converted_toman` = NULL
            WHERE `id` = :id AND `status` = 'converted'");
        $stmt->execute([':id' => $recordId]);
        return $stmt->rowCount() === 1;
    }

    public static function getNtxRecord(int $recordId): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `ntx_records` WHERE `id` = :id LIMIT 1");
        $stmt->execute([':id' => $recordId]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    /**
     * v2.9.3: Return all distinct user ids that have at least one 'pending'
     * ntx_records row. Used by the automatic NTX→toman conversion cron job
     * (NtxLedger::convertAllUsers) so we only iterate over users who actually
     * have pending records instead of the entire users table.
     *
     * @return list<int>
     */
    public static function getUsersWithPendingNtx(int $lockSeconds = 86400): array
    {
        $db = self::getConnection();
        // v2.9.45 (R6-84/LOW): only users with an actually-MATURE pending
        // record — the unfiltered query visited everyone with ANY pending
        // record (including still-locked ones) every minute. The lock window
        // is the ADMIN-CONFIGURED value (default 24h), passed by the caller.
        $lockSeconds = max(0, $lockSeconds);
        $stmt = $db->prepare("SELECT DISTINCT `user_id` FROM `ntx_records`
            WHERE `status` = 'pending' AND UNIX_TIMESTAMP(`received_at`) <= (UNIX_TIMESTAMP(NOW()) - :lock)");
        $stmt->execute([':lock' => $lockSeconds]);
        $rows = $stmt->fetchAll();
        $out = [];
        foreach ($rows as $r) {
            $out[] = (int)$r['user_id'];
        }
        return $out;
    }

    // ─── Withdrawals (mirror of support tickets) ────────────────────

    public static function createWithdrawal(int $userId, string $type, string $question, int $amount = 0, ?string $operator = null, ?string $displayName = null, ?string $adminName = null, ?int $adminId = null, bool $withInitialMessage = true): int
    {
        $db = self::getConnection();
        $name = $displayName ?? self::loadUserDisplayName($userId);
        if ($adminName !== null && $adminId !== null) {
            $stmt = $db->prepare("INSERT INTO `withdrawals` (`user_id`, `type`, `amount`, `operator`, `question`, `admin_name`, `admin_id`, `status`) VALUES (:uid, :type, :amt, :op, :q, :aname, :aid, 'claimed')");
            $stmt->execute([':uid' => $userId, ':type' => $type, ':amt' => $amount, ':op' => $operator, ':q' => $question, ':aname' => $adminName, ':aid' => $adminId]);
        } else {
            $stmt = $db->prepare("INSERT INTO `withdrawals` (`user_id`, `type`, `amount`, `operator`, `question`) VALUES (:uid, :type, :amt, :op, :q)");
            $stmt->execute([':uid' => $userId, ':type' => $type, ':amt' => $amount, ':op' => $operator, ':q' => $question]);
        }
        $withdrawalId = (int)$db->lastInsertId();
        if ($withInitialMessage) {
            $senderType = ($adminName !== null && $adminId !== null) ? 'admin' : 'user';
            $senderId = ($senderType === 'admin') ? $adminId : $userId;
            $senderName = ($senderType === 'admin') ? $adminName : $name;
            self::addWithdrawalMessage($withdrawalId, $senderType, $senderId, $senderName, 'text', $question);
        }
        return $withdrawalId;
    }

    public static function getWithdrawal(int $id): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `withdrawals` WHERE `id` = :id LIMIT 1");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function getAllWithdrawals(int $limit = 100): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `withdrawals` ORDER BY `question_time` DESC LIMIT :limit");
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public static function getActiveWithdrawalsForUser(int $userId): array
    {
        $db = self::getConnection();
        // T46-as: 'paid' (payout fully settled after satisfaction) is terminal
        // and must not keep the row in the user's active set/menu forever.
        $stmt = $db->prepare("SELECT * FROM `withdrawals` WHERE `user_id` = :uid AND `status` NOT IN ('closed','rejected','paid') ORDER BY `id` DESC");
        $stmt->execute([':uid' => $userId]);
        return $stmt->fetchAll();
    }

    public static function claimWithdrawal(int $id, int $adminId, string $adminName): bool
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `withdrawals` SET `status` = 'claimed', `admin_id` = :aid, `admin_name` = :aname WHERE `id` = :id AND `status` = 'open'");
        $stmt->execute([':aid' => $adminId, ':aname' => $adminName, ':id' => $id]);
        return $stmt->rowCount() > 0;
    }

    public static function answerWithdrawal(int $id, string $answer): void
    {
        $db = self::getConnection();
        // T46-p: guarded transition — 'answered' must never flip an
        // approved/rejected/closed row back into the closable pool (an
        // answered card/stars row is user-closable and refunds the hold →
        // refund-after-payout). Admin replies racing an approve/reject now
        // lose cleanly instead of corrupting the state machine.
        $stmt = $db->prepare("UPDATE `withdrawals` SET `status` = 'answered', `answer` = :ans, `answer_time` = NOW() WHERE `id` = :id AND `status` IN ('open','claimed','answered')");
        $stmt->execute([':ans' => $answer, ':id' => $id]);
        if ($stmt->rowCount() === 0) {
            error_log('[Database] answerWithdrawal: withdrawal #' . $id . ' is not in an open/claimed/answered state; answer dropped.');
        }
    }

    public static function approveWithdrawal(int $id, ?string $txid = null, ?string $assignedCode = null, ?array $fromStatuses = null): bool
    {
        // T46-p: guarded transition. Default = only non-terminal rows
        // (open/claimed) can be approved — a rejected (already-refunded) or
        // closed row can no longer be flipped to approved. Callers that stamp
        // details AFTER their own guarded open→approved transition pass
        // fromStatuses ['approved'] explicitly. Returns whether the row was
        // actually transitioned/stamped.
        $from = $fromStatuses ?? ['open', 'claimed'];
        // T46-as FIX: all-named IN-marks. The old '?'-mix with :tx/:ac/:id in
        // ONE statement threw HY093 at prepare() on this host's native
        // prepares (EMULATE_PREPARES=0) - every panel approve AND the charge
        // auto-approve (RechargeCode::assign) fataled AFTER the guarded
        // transition, leaving naked-'approved' rows: no stamps, no user
        // notify, no satisfaction unblock chain.
        $inMarks = [];
        $inParams = [];
        foreach (array_values($from) as $idx => $stVal) {
            $key = ':s' . $idx;
            $inMarks[] = $key;
            $inParams[$key] = $stVal;
        }
        $in = implode(',', $inMarks);
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `withdrawals` SET `status` = 'approved', `satisfaction_required` = 1, `payment_txid` = :tx, `assigned_code` = :ac, `answer_time` = NOW() WHERE `id` = :id AND `status` IN ($in)");
        $stmt->execute(array_merge([':tx' => $txid, ':ac' => $assignedCode, ':id' => $id], $inParams));
        $applied = $stmt->rowCount() > 0;
        if (!$applied) {
            error_log('[Database] approveWithdrawal: withdrawal #' . $id . ' is not in state ' . implode('/', $from) . '; approve dropped.');
        }
        return $applied;
    }

    public static function rejectWithdrawal(int $id, ?string $note = null): void
    {
        $db = self::getConnection();
        // T46-p: stamp-only primitive — the guarded open/claimed/answered →
        // rejected transition lives in Withdrawal::reject(); this can no
        // longer move a live row to rejected on its own.
        $stmt = $db->prepare("UPDATE `withdrawals` SET `status` = 'rejected', `note` = :note, `answer_time` = NOW() WHERE `id` = :id AND `status` = 'rejected'");
        $stmt->execute([':note' => $note, ':id' => $id]);
    }

    /**
     * Guarded status transition for a withdrawal row: sets the status to
     * $toStatus only when the row is currently in one of $fromStatuses.
     * Returns the number of rows transitioned (0 or 1) — 0 means another
     * actor (admin approve/reject, user close) already moved the row and the
     * caller MUST abort its payment/refund/side effects.
     */
    public static function transitionWithdrawalStatus(int $id, array $fromStatuses, string $toStatus): int
    {
        if ($fromStatuses === [] || $toStatus === '') {
            return 0;
        }
        $db = self::getConnection();
        $placeholders = implode(', ', array_fill(0, count($fromStatuses), '?'));
        $stmt = $db->prepare("UPDATE `withdrawals` SET `status` = ? WHERE `id` = ? AND `status` IN ({$placeholders})");
        $stmt->execute(array_merge([$toStatus, $id], array_values($fromStatuses)));
        return $stmt->rowCount();
    }

    public static function closeWithdrawal(int $id): void
    {
        $db = self::getConnection();
        $db->prepare("UPDATE `withdrawals` SET `status` = 'closed' WHERE `id` = :id")->execute([':id' => $id]);
    }

    /**
     * F4: reopen a withdrawal the USER closed. Guarded — only 'closed' rows
     * may be reopened. The old unconditional UPDATE also reopened
     * 'rejected'/'approved'/'paid' rows: a rejected row had ALREADY been
     * refunded, so reopen+approve became a double-payout path. Returns the
     * number of rows reopened (0 = not reopenable; callers must treat that
     * as a no-op, never as success).
     */
    public static function reopenWithdrawal(int $id, int $adminId = 0, string $adminName = ''): int
    {
        $db = self::getConnection();
        if ($adminId > 0) {
            $stmt = $db->prepare("UPDATE `withdrawals` SET `status` = 'claimed', `admin_id` = :aid, `admin_name` = :aname
                WHERE `id` = :id AND `status` IN ('closed')");
            $stmt->execute([':aid' => $adminId, ':aname' => $adminName, ':id' => $id]);
        } else {
            $stmt = $db->prepare("UPDATE `withdrawals` SET `status` = 'open' WHERE `id` = :id AND `status` IN ('closed')");
            $stmt->execute([':id' => $id]);
        }
        return $stmt->rowCount();
    }

    public static function deleteWithdrawal(int $id): void
    {
        $db = self::getConnection();
        $db->prepare("DELETE FROM `withdrawal_messages` WHERE `withdrawal_id` = :tid")->execute([':tid' => $id]);
        $db->prepare("DELETE FROM `withdrawals` WHERE `id` = :tid")->execute([':tid' => $id]);
    }

    public static function addWithdrawalMessage(int $withdrawalId, string $senderType, int $senderId, ?string $senderName, string $contentType, ?string $text, ?string $fileId = null, ?int $replyTo = null, ?int $tgMessageId = null, ?int $tgChatId = null, ?string $filePath = null): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("INSERT INTO `withdrawal_messages` (`withdrawal_id`, `sender_type`, `sender_id`, `sender_name`, `content_type`, `text`, `file_id`, `reply_to`, `tg_message_id`, `tg_chat_id`, `file_path`)
            VALUES (:wid, :st, :sid, :sname, :ct, :txt, :fid, :rto, :tmsg, :tchat, :fpath)");
        $stmt->execute([
            ':wid' => $withdrawalId, ':st' => $senderType, ':sid' => $senderId, ':sname' => $senderName,
            ':ct' => $contentType, ':txt' => $text, ':fid' => $fileId, ':rto' => $replyTo,
            ':tmsg' => $tgMessageId, ':tchat' => $tgChatId, ':fpath' => $filePath,
        ]);
        return (int)$db->lastInsertId();
    }

    public static function updateWithdrawalMessageTgId(int $msgId, int $tgMessageId, int $tgChatId): void
    {
        $db = self::getConnection();
        $db->prepare("UPDATE `withdrawal_messages` SET `tg_message_id` = :tmsg, `tg_chat_id` = :tchat WHERE `id` = :id")
           ->execute([':tmsg' => $tgMessageId, ':tchat' => $tgChatId, ':id' => $msgId]);
    }

    public static function getWithdrawalMessagesWithTgId(int $withdrawalId, int $tgChatId): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `withdrawal_messages` WHERE `withdrawal_id` = :wid AND `tg_message_id` IS NOT NULL AND `tg_chat_id` = :tchat AND `tg_message_id` > 0 ORDER BY `id` ASC");
        $stmt->execute([':wid' => $withdrawalId, ':tchat' => $tgChatId]);
        return $stmt->fetchAll();
    }

    public static function getWithdrawalMessageByTgId(int $tgMessageId, int $tgChatId): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `withdrawal_messages` WHERE `tg_message_id` = :tmsg AND `tg_chat_id` = :tchat LIMIT 1");
        $stmt->execute([':tmsg' => $tgMessageId, ':tchat' => $tgChatId]);
        $row = $stmt->fetch();
        return $row === false ? null : $row;
    }

    public static function getWithdrawalMessages(int $withdrawalId): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `withdrawal_messages` WHERE `withdrawal_id` = :wid ORDER BY `created_at` ASC, `id` ASC");
        $stmt->execute([':wid' => $withdrawalId]);
        return $stmt->fetchAll();
    }

    public static function getWithdrawalFilePaths(int $withdrawalId): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT `file_path` FROM `withdrawal_messages` WHERE `withdrawal_id` = :wid AND `file_path` IS NOT NULL AND `file_path` != ''");
        $stmt->execute([':wid' => $withdrawalId]);
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }

    // ─── Recharge Codes ─────────────────────────────────────────────

    public static function addRechargeCode(string $operator, string $code, int $amountToman = 0, ?int $createdBy = null): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("INSERT IGNORE INTO `recharge_codes` (`operator`, `code`, `amount_toman`, `created_by`) VALUES (:op, :code, :amt, :cb)");
        $stmt->execute([':op' => $operator, ':code' => $code, ':amt' => $amountToman, ':cb' => $createdBy]);
        return (int)$db->lastInsertId();
    }

    public static function getRechargeCode(int $id): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `recharge_codes` WHERE `id` = :id LIMIT 1");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function getAllRechargeCodes(int $limit = 200, int $offset = 0, ?string $operatorFilter = null, ?bool $usedFilter = null): array
    {
        $db = self::getConnection();
        $sql = "SELECT * FROM `recharge_codes`";
        $where = [];
        $params = [];
        if ($operatorFilter !== null) { $where[] = "`operator` = :op"; $params[':op'] = $operatorFilter; }
        if ($usedFilter !== null) { $where[] = "`is_used` = :u"; $params[':u'] = $usedFilter ? 1 : 0; }
        if ($where) $sql .= " WHERE " . implode(' AND ', $where);
        $sql .= " ORDER BY `id` DESC LIMIT :lim OFFSET :off";
        $stmt = $db->prepare($sql);
        foreach ($params as $k => $v) $stmt->bindValue($k, $v);
        $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':off', $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public static function countRechargeCodes(?string $operatorFilter = null, ?bool $usedFilter = null): int
    {
        $db = self::getConnection();
        $sql = "SELECT COUNT(*) FROM `recharge_codes`";
        $where = [];
        $params = [];
        if ($operatorFilter !== null) { $where[] = "`operator` = :op"; $params[':op'] = $operatorFilter; }
        if ($usedFilter !== null) { $where[] = "`is_used` = :u"; $params[':u'] = $usedFilter ? 1 : 0; }
        if ($where) $sql .= " WHERE " . implode(' AND ', $where);
        $stmt = $db->prepare($sql);
        foreach ($params as $k => $v) $stmt->bindValue($k, $v);
        $stmt->execute();
        return (int)$stmt->fetchColumn();
    }

    /** Atomically claim an unused recharge code for an operator. Returns the code row or null. */
    public static function claimRechargeCode(string $operator): ?array
    {
        $db = self::getConnection();
        // SELECT ... FOR UPDATE would need a transaction; use an atomic UPDATE instead.
        $stmt = $db->prepare("SELECT `id`, `code`, `amount_toman` FROM `recharge_codes` WHERE `operator` = :op AND `is_used` = 0 ORDER BY `id` ASC LIMIT 1");
        $stmt->execute([':op' => $operator]);
        $row = $stmt->fetch();
        if (!$row) return null;
        $upd = $db->prepare("UPDATE `recharge_codes` SET `is_used` = 1 WHERE `id` = :id AND `is_used` = 0");
        $upd->execute([':id' => $row['id']]);
        if ($upd->rowCount() === 0) {
            // someone else grabbed it between SELECT and UPDATE — retry once
            return self::claimRechargeCode($operator);
        }
        return $row;
    }

    /** Claim an unused recharge code for an operator with a SPECIFIC amount_toman. */
    public static function claimRechargeCodeByAmount(string $operator, int $amountToman): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT `id`, `code`, `amount_toman` FROM `recharge_codes` WHERE `operator` = :op AND `amount_toman` = :amt AND `is_used` = 0 ORDER BY `id` ASC LIMIT 1");
        $stmt->execute([':op' => $operator, ':amt' => $amountToman]);
        $row = $stmt->fetch();
        if (!$row) return null;
        $upd = $db->prepare("UPDATE `recharge_codes` SET `is_used` = 1 WHERE `id` = :id AND `is_used` = 0");
        $upd->execute([':id' => $row['id']]);
        if ($upd->rowCount() === 0) {
            return self::claimRechargeCodeByAmount($operator, $amountToman);
        }
        return $row;
    }

    /** Return distinct available (unused) amount_toman values for an operator, sorted ascending. */
    public static function getAvailableDenominations(string $operator): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT DISTINCT `amount_toman` FROM `recharge_codes` WHERE `operator` = :op AND `is_used` = 0 AND `amount_toman` > 0 ORDER BY `amount_toman` ASC");
        $stmt->execute([':op' => $operator]);
        return array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN));
    }

    public static function markRechargeCodeUsed(int $codeId, int $userId, int $withdrawalId): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `recharge_codes` SET `is_used` = 1, `used_by` = :uid, `used_at` = NOW(), `withdrawal_id` = :wid WHERE `id` = :id");
        $stmt->execute([':uid' => $userId, ':wid' => $withdrawalId, ':id' => $codeId]);
    }

    /**
     * F5: best-effort release of a claimed-but-unassigned recharge code.
     * Withdrawal::createChargeRequest claims a code BEFORE inserting the
     * withdrawal row; if that INSERT throws, the claim must not be burned —
     * the caller calls this, then rethrows. Guarded on is_used=1 AND on
     * used_by/withdrawal_id still NULL, so a code that was already properly
     * assigned to a user (markRechargeCodeUsed) can never be released by
     * accident.
     *
     * @return bool true when a claimed code row was actually released.
     */
    public static function unclaimRechargeCode(string $code): bool
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `recharge_codes` SET `is_used` = 0, `used_by` = NULL, `used_at` = NULL, `withdrawal_id` = NULL
            WHERE `code` = :code AND `is_used` = 1 AND `used_by` IS NULL AND `withdrawal_id` IS NULL");
        $stmt->execute([':code' => $code]);
        return $stmt->rowCount() > 0;
    }

    public static function updateRechargeCode(int $id, string $operator, string $code, int $amountToman): bool
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `recharge_codes` SET `operator` = :op, `code` = :code, `amount_toman` = :amt WHERE `id` = :id AND `is_used` = 0");
        $stmt->execute([':op' => $operator, ':code' => $code, ':amt' => $amountToman, ':id' => $id]);
        return $stmt->rowCount() > 0;
    }

    public static function deleteRechargeCode(int $id): void
    {
        $db = self::getConnection();
        $db->prepare("DELETE FROM `recharge_codes` WHERE `id` = :id AND `is_used` = 0")->execute([':id' => $id]);
    }

    public static function getRechargeCodesByUser(int $userId): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT rc.*, w.type AS wtype, w.id AS wid FROM `recharge_codes` rc LEFT JOIN `withdrawals` w ON w.id = rc.withdrawal_id WHERE rc.`used_by` = :uid ORDER BY rc.`used_at` DESC");
        $stmt->execute([':uid' => $userId]);
        return $stmt->fetchAll();
    }

    // ─── Tasks ──────────────────────────────────────────────────────

    public static function createTask(string $type, string $title, ?string $description, ?string $target, ?int $channelId, string $rewardType, int $rewardAmount, ?int $createdBy, int $rewardGameTokens = 0, int $rewardToman = 0, int $rewardNtx = 0): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("INSERT INTO `tasks` (`type`, `title`, `description`, `target`, `channel_id`, `reward_type`, `reward_amount`, `reward_game_tokens`, `reward_toman`, `reward_ntx`, `created_by`)
            VALUES (:type, :title, :desc, :target, :cid, :rt, :ra, :rgt, :rto, :rnx, :cb)");
        $stmt->execute([
            ':type' => $type, ':title' => $title, ':desc' => $description, ':target' => $target,
            ':cid' => $channelId, ':rt' => $rewardType, ':ra' => $rewardAmount,
            ':rgt' => $rewardGameTokens, ':rto' => $rewardToman, ':rnx' => $rewardNtx,
            ':cb' => $createdBy,
        ]);
        return (int)$db->lastInsertId();
    }

    public static function getTask(int $id): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `tasks` WHERE `id` = :id LIMIT 1");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function getActiveTasks(): array
    {
        $db = self::getConnection();
        return $db->query("SELECT * FROM `tasks` WHERE `is_active` = 1 ORDER BY `id` ASC")->fetchAll();
    }

    public static function getAllTasks(): array
    {
        $db = self::getConnection();
        return $db->query("SELECT * FROM `tasks` ORDER BY `id` DESC")->fetchAll();
    }

    public static function updateTask(int $id, string $title, ?string $description, string $rewardType, int $rewardAmount, bool $isActive, int $rewardGameTokens = 0, int $rewardToman = 0, int $rewardNtx = 0): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `tasks` SET `title` = :t, `description` = :d, `reward_type` = :rt, `reward_amount` = :ra, `reward_game_tokens` = :rgt, `reward_toman` = :rto, `reward_ntx` = :rnx, `is_active` = :ia WHERE `id` = :id");
        $stmt->execute([':t' => $title, ':d' => $description, ':rt' => $rewardType, ':ra' => $rewardAmount, ':rgt' => $rewardGameTokens, ':rto' => $rewardToman, ':rnx' => $rewardNtx, ':ia' => $isActive ? 1 : 0, ':id' => $id]);
    }

    public static function deleteTask(int $id): void
    {
        $db = self::getConnection();
        $db->prepare("DELETE FROM `tasks` WHERE `id` = :id")->execute([':id' => $id]);
        $db->prepare("DELETE FROM `user_tasks` WHERE `task_id` = :id")->execute([':id' => $id]);
    }

    public static function getUserTask(int $userId, int $taskId): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `user_tasks` WHERE `user_id` = :uid AND `task_id` = :tid LIMIT 1");
        $stmt->execute([':uid' => $userId, ':tid' => $taskId]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function createUserTask(int $userId, int $taskId, string $status = 'pending', ?string $answerText = null, ?string $answerFileId = null): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("INSERT INTO `user_tasks` (`user_id`, `task_id`, `status`, `answer_text`, `answer_file_id`)
            VALUES (:uid, :tid, :st, :at, :af)
            ON DUPLICATE KEY UPDATE `status` = VALUES(`status`), `answer_text` = VALUES(`answer_text`), `answer_file_id` = VALUES(`answer_file_id`)");
        $stmt->execute([':uid' => $userId, ':tid' => $taskId, ':st' => $status, ':at' => $answerText, ':af' => $answerFileId]);
        return (int)$db->lastInsertId();
    }

    /**
     * F2: idempotently ensure an 'in_progress' user_tasks row exists for a
     * channel/bot task. Uses INSERT IGNORE against the (user_id, task_id)
     * UNIQUE index so an EXISTING row — pending, in_progress, or completed —
     * is never modified: a late-arriving duplicate start tap must not reset
     * a completed row (that would re-arm the guarded completeUserTask and
     * double-pay the reward, which is exactly why plain createUserTask —
     * whose ON DUPLICATE overwrites status — is NOT used here).
     *
     * @return bool true only when THIS call created the row.
     */
    public static function ensureUserTaskStarted(int $userId, int $taskId): bool
    {
        $db = self::getConnection();
        $stmt = $db->prepare("INSERT IGNORE INTO `user_tasks` (`user_id`, `task_id`, `status`) VALUES (:uid, :tid, 'in_progress')");
        $stmt->execute([':uid' => $userId, ':tid' => $taskId]);
        return $stmt->rowCount() === 1;
    }

    /**
     * F2: guarded completion of a user task — the row moves to 'completed'
     * (with completed_at + reward_granted stamped by the same atomic UPDATE)
     * only while it is not completed yet. Returns the number of rows
     * transitioned (0 or 1): the caller MUST grant the reward only on 1.
     * Previously this UPDATE was unguarded and channel/bot tasks never had a
     * user_tasks row at all, so it silently matched 0 rows while the reward
     * was granted on EVERY verify tap (endless payout).
     */
    public static function completeUserTask(int $userId, int $taskId): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `user_tasks` SET `status` = 'completed', `completed_at` = NOW(), `reward_granted` = 1
            WHERE `user_id` = :uid AND `task_id` = :tid AND `status` <> 'completed'");
        $stmt->execute([':uid' => $userId, ':tid' => $taskId]);
        return $stmt->rowCount();
    }

    /**
     * F2: guarded admin review of a pending text-task answer. The row only
     * transitions while its status is still 'pending' — a second admin click
     * (or a retried request) now matches 0 rows instead of re-paying.
     * reward_granted is stamped together with the guarded approval, keeping
     * the exactly-once semantics. Returns the number of rows transitioned
     * (0 or 1); on 0 the caller MUST NOT grant the reward / notify.
     */
    public static function reviewUserTask(int $userTaskId, bool $approved, ?int $adminId, ?string $reason = null): int
    {
        $db = self::getConnection();
        if ($approved) {
            $stmt = $db->prepare("UPDATE `user_tasks` SET `status` = 'completed', `reviewed_at` = NOW(), `admin_id` = :aid, `reward_granted` = 1, `completed_at` = NOW()
                WHERE `id` = :id AND `status` = 'pending'");
        } else {
            // rejected: reset so the user can try again
            $stmt = $db->prepare("UPDATE `user_tasks` SET `status` = 'rejected', `reviewed_at` = NOW(), `admin_id` = :aid
                WHERE `id` = :id AND `status` = 'pending'");
        }
        $stmt->execute([':aid' => $adminId, ':id' => $userTaskId]);
        return $stmt->rowCount();
    }

    public static function getPendingTextTasks(): array
    {
        $db = self::getConnection();
        // F-A1: the multi-currency reward columns were missing here too, so
        // the admin panel rendered the legacy (wrong) reward for text tasks
        // configured with the new columns.
        // T46-l: manual-type confirmations (user tapped «تایید انجام تسک»)
        // flow through the SAME pending-review pipeline as text answers.
        return $db->query("SELECT ut.*, t.title, t.type AS task_type, t.reward_type, t.reward_amount, t.reward_game_tokens, t.reward_toman, t.reward_ntx, u.display_name
            FROM `user_tasks` ut
            JOIN `tasks` t ON t.id = ut.task_id
            JOIN `users` u ON u.user_id = ut.user_id
            WHERE ut.`status` = 'pending' AND t.`type` IN ('text', 'manual')
            ORDER BY ut.`id` ASC")->fetchAll();
    }

    public static function getUserTaskById(int $id): ?array
    {
        $db = self::getConnection();
        // F-A1: t.reward_game_tokens/reward_toman/reward_ntx were missing from
        // this SELECT, so TaskSystem::grantTaskReward (which reads exactly
        // those keys) fell back to the legacy reward_amount/reward_type pair —
        // a text task configured with the new multi-currency columns paid
        // NOTHING (or the wrong legacy amount) on approval.
        $stmt = $db->prepare("SELECT ut.*, t.title, t.type, t.reward_type, t.reward_amount, t.reward_game_tokens, t.reward_toman, t.reward_ntx, t.description
            FROM `user_tasks` ut JOIN `tasks` t ON t.id = ut.task_id WHERE ut.`id` = :id LIMIT 1");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function getCompletedTasksForUser(int $userId): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT ut.*, t.title FROM `user_tasks` ut JOIN `tasks` t ON t.id = ut.task_id WHERE ut.`user_id` = :uid AND ut.`status` = 'completed' ORDER BY ut.`id` DESC");
        $stmt->execute([':uid' => $userId]);
        return $stmt->fetchAll();
    }

    // ─── T46-l: configurable task types ─────────────────────────────

    /**
     * T46-l: persist tasks.task_config (JSON string or NULL).
     */
    public static function updateTaskConfig(int $taskId, ?string $json): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `tasks` SET `task_config` = :cfg WHERE `id` = :id");
        $stmt->execute([':cfg' => $json, ':id' => $taskId]);
    }

    /**
     * T46-l: active tasks of the given types (order by id asc).
     */
    public static function getActiveTasksByTypes(array $types): array
    {
        if (empty($types)) return [];
        $types = array_values(array_filter(array_map('strval', $types)));
        if (empty($types)) return [];
        $marks = implode(',', array_fill(0, count($types), '?'));
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `tasks` WHERE `is_active` = 1 AND `type` IN ($marks) ORDER BY `id` ASC");
        $stmt->execute($types);
        return $stmt->fetchAll();
    }

    /**
     * T46-l: active group-side tasks (group_add / group_msgs / race) whose
     * target channel matches the chat — by numeric id (private groups) or by
     * @username (public groups). Deduped by task id (both matches possible).
     */
    public static function getGroupTasksForChat(string $chatId, string $handle): array
    {
        $db = self::getConnection();
        // :hnd is guarded with <> '' so a private group (no username) can
        // never accidentally match a public channel row with an empty handle.
        // HY093 discipline: PDO named placeholders must be UNIQUE — the
        // handle value is bound once per placeholder (:hnd1 / :hnd2).
        $stmt = $db->prepare("SELECT t.* FROM `tasks` t
            JOIN `channels` c ON c.id = t.channel_id
            WHERE t.`is_active` = 1 AND t.`type` IN ('group_add','group_msgs','race')
              AND (c.`numeric_id` = :cid OR (:hnd1 <> '' AND c.`channel_type` = 'public' AND c.`channel_handle` = :hnd2))
            ORDER BY t.`id` ASC");
        $stmt->execute([':cid' => $chatId, ':hnd1' => $handle, ':hnd2' => $handle]);
        $out = [];
        foreach ($stmt->fetchAll() as $row) {
            $out[(int)$row['id']] = $row;
        }
        return array_values($out);
    }

    /**
     * T46-l: record one progress event. Returns true ONLY when THIS call
     * inserted the row (INSERT IGNORE against the UNIQUE event_key) — the
     * caller counts progress just once per real-world event.
     */
    public static function recordTaskEvent(int $taskId, int $userId, string $eventKey): bool
    {
        $db = self::getConnection();
        $stmt = $db->prepare("INSERT IGNORE INTO `task_events` (`task_id`, `user_id`, `event_key`) VALUES (:tid, :uid, :key)");
        $stmt->execute([':tid' => $taskId, ':uid' => $userId, ':key' => $eventKey]);
        return $stmt->rowCount() === 1;
    }

    /**
     * T46-l: number of the user's counted progress events for a task.
     */
    public static function countTaskEvents(int $taskId, int $userId): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT COUNT(*) AS c FROM `task_events` WHERE `task_id` = :tid AND `user_id` = :uid");
        $stmt->execute([':tid' => $taskId, ':uid' => $userId]);
        return (int)($stmt->fetch()['c'] ?? 0);
    }

    /**
     * T46-l: number of events whose key starts with the given prefix for the
     * given user (race(add) progress is keyed per race, across place-tasks).
     */
    public static function countTaskEventsByPrefix(string $prefix, int $userId): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT COUNT(*) AS c FROM `task_events` WHERE `event_key` LIKE :pfx AND `user_id` = :uid");
        $stmt->execute([':pfx' => $prefix . '%', ':uid' => $userId]);
        return (int)($stmt->fetch()['c'] ?? 0);
    }

    /**
     * T46-l: number of race finishers (distinct users who hold a
     * racefin:<raceKey>:<uid> marker) — the ordinal source for podium places.
     */
    public static function countRaceFinishers(string $raceKey): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT COUNT(*) AS c FROM `task_events` WHERE `event_key` LIKE :pfx");
        $stmt->execute([':pfx' => 'racefin:' . $raceKey . ':%']);
        return (int)($stmt->fetch()['c'] ?? 0);
    }

    /**
     * T46-l: finished games the user took part in (2-player sessions as
     * p1/p2 plus multi-player game_players rows), deduped per session.
     */
    public static function countFinishedGames(int $userId): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT COUNT(DISTINCT s.id) AS c FROM `game_sessions` s
            LEFT JOIN `game_players` gp ON gp.session_id = s.id AND gp.user_id = :uid
            WHERE s.status = 'finished' AND (s.player1_id = :uid2 OR s.player2_id = :uid3 OR gp.user_id IS NOT NULL)");
        $stmt->execute([':uid' => $userId, ':uid2' => $userId, ':uid3' => $userId]);
        return (int)($stmt->fetch()['c'] ?? 0);
    }

    /**
     * T46-l: finished games the user WON (winner_id history).
     */
    public static function countWonGames(int $userId): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT COUNT(*) AS c FROM `game_sessions` WHERE `winner_id` = :uid AND `status` = 'finished'");
        $stmt->execute([':uid' => $userId]);
        return (int)($stmt->fetch()['c'] ?? 0);
    }

    /**
     * T46-l: the user's referral (subscriber) count — the same metric the
     * league uses, read straight from users.sub_count.
     */
    public static function getReferralCount(int $userId): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT `sub_count` FROM `users` WHERE `user_id` = :uid LIMIT 1");
        $stmt->execute([':uid' => $userId]);
        $row = $stmt->fetch();
        return $row !== false ? (int)$row['sub_count'] : 0;
    }

    /**
     * T46-l: drop all progress events of a task (called on task delete).
     */
    public static function deleteTaskEvents(int $taskId): void
    {
        $db = self::getConnection();
        $db->prepare("DELETE FROM `task_events` WHERE `task_id` = :tid")->execute([':tid' => $taskId]);
    }

    // ─── Satisfactions ──────────────────────────────────────────────

    public static function createSatisfaction(int $userId, int $withdrawalId, string $contentType, ?string $text, ?string $fileId, ?string $filePath, ?int $tgMessageId, ?int $tgChatId): int
    {
        $db = self::getConnection();
        // v2.9.44 (T19-6): atomic dedup — the caller's hasPendingSatisfaction()
        // check is check-then-act; two concurrent submissions (or a webhook
        // redelivery racing the first worker) both passed it and created two
        // pending rows (double admin notifications + a duplicate channel post
        // after the first approval). INSERT … SELECT … WHERE NOT EXISTS makes
        // the dedup atomic; returns 0 when a pending row already exists.
        $stmt = $db->prepare("INSERT INTO `satisfactions` (`user_id`, `withdrawal_id`, `content_type`, `text`, `file_id`, `file_path`, `tg_message_id`, `tg_chat_id`)
            SELECT :uid, :wid, :ct, :txt, :fid, :fp, :tmsg, :tchat
            WHERE NOT EXISTS (
                SELECT 1 FROM `satisfactions`
                WHERE `user_id` = :uid2 AND `status` = 'pending'
            )");
        $stmt->execute([
            ':uid' => $userId, ':wid' => $withdrawalId, ':ct' => $contentType, ':txt' => $text,
            ':fid' => $fileId, ':fp' => $filePath, ':tmsg' => $tgMessageId, ':tchat' => $tgChatId,
            ':uid2' => $userId,
        ]);
        if ($stmt->rowCount() === 0) {
            return 0; // a pending row already won the race
        }
        return (int)$db->lastInsertId();
    }

    /**
     * F9: true when the user already has a satisfaction row awaiting review
     * (status='pending'). Used by Satisfaction::receive() to dedup — a
     * second submission while one is pending must not insert a duplicate
     * row and re-notify the admins. A 'rejected' row does NOT count: after
     * a rejection the user must (and can) submit a new satisfaction.
     */
    public static function hasPendingSatisfaction(int $userId): bool
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT `id` FROM `satisfactions` WHERE `user_id` = :uid AND `status` = 'pending' LIMIT 1");
        $stmt->execute([':uid' => $userId]);
        return $stmt->fetch() !== false;
    }

    public static function getSatisfaction(int $id): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `satisfactions` WHERE `id` = :id LIMIT 1");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function getPendingSatisfactions(): array
    {
        $db = self::getConnection();
        // F-A5: LEFT JOIN — deleting the withdrawal (admin panel) or a user
        // cascade must not hide a blocked user's submission (receive() keeps
        // accepting rows, so an invisible pending row = permanently blocked
        // user with no admin-visible evidence). Consumers already null-coalesce
        // wtype ('—' in the panel, '' in the API), so no SQL COALESCE is added:
        // coalescing in SQL would CHANGE the API payload (empty string would
        // become the em-dash placeholder) for rows whose withdrawal exists.
        return $db->query("SELECT s.*, u.display_name, w.type AS wtype, w.payment_txid
            FROM `satisfactions` s
            JOIN `users` u ON u.user_id = s.user_id
            LEFT JOIN `withdrawals` w ON w.id = s.withdrawal_id
            WHERE s.`status` = 'pending'
            ORDER BY s.`id` ASC")->fetchAll();
    }

    public static function getAllSatisfactions(int $limit = 100): array
    {
        $db = self::getConnection();
        // F-A5: LEFT JOIN — see getPendingSatisfactions().
        $stmt = $db->prepare("SELECT s.*, u.display_name, w.type AS wtype, w.payment_txid
            FROM `satisfactions` s
            JOIN `users` u ON u.user_id = s.user_id
            LEFT JOIN `withdrawals` w ON w.id = s.withdrawal_id
            ORDER BY s.`id` DESC LIMIT :lim");
        $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * F9: GUARDED claim of a pending satisfaction for approval — sets
     * status='approved' (stamping admin_id / admin_comment / reviewed_at)
     * ONLY while the row is still 'pending', and returns the number of rows
     * transitioned (0 or 1). The winner is the only request allowed to
     * forward to the channel / unblock the user (kills the double-forward
     * race). The channel_message_id is NOT known yet at claim time — stamp
     * it after the forward with setSatisfactionChannelMessage(). If the
     * forward fails after a won claim, the caller must call
     * revertSatisfactionClaim() so the row stays retryable.
     */
    public static function approveSatisfaction(int $id, ?int $adminId, ?string $comment): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `satisfactions` SET `status` = 'approved', `admin_id` = :aid, `admin_comment` = :cmt, `reviewed_at` = NOW()
            WHERE `id` = :id AND `status` = 'pending'");
        $stmt->execute([':aid' => $adminId, ':cmt' => $comment, ':id' => $id]);
        return $stmt->rowCount();
    }

    /**
     * F9: stamp the channel message id after a successful forward (the
     * guarded approveSatisfaction claim runs BEFORE the forward, so the id
     * only exists afterwards). Only applies to an 'approved' row.
     */
    public static function setSatisfactionChannelMessage(int $id, int $channelMessageId): bool
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `satisfactions` SET `channel_message_id` = :cmi WHERE `id` = :id AND `status` = 'approved'");
        $stmt->execute([':cmi' => $channelMessageId, ':id' => $id]);
        return true;
    }

    /**
     * F9: release a won approveSatisfaction claim when the channel forward
     * failed — restores the row to 'pending' (and clears the review stamp)
     * so the admin can simply retry. Guarded on status='approved'.
     */
    public static function revertSatisfactionClaim(int $id): bool
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `satisfactions` SET `status` = 'pending', `admin_id` = NULL, `admin_comment` = NULL, `reviewed_at` = NULL, `channel_message_id` = NULL
            WHERE `id` = :id AND `status` = 'approved'");
        $stmt->execute([':id' => $id]);
        return $stmt->rowCount() === 1;
    }

    /**
     * F-A8: GUARDED rejection — mirrors approveSatisfaction(): the row only
     * transitions while it is still 'pending' and returns the number of rows
     * transitioned (0 or 1). The old unguarded UPDATE let reject-after-approve
     * flip an approved row to 'rejected' and re-send the rejection + prompt to
     * an already-unblocked user (state corruption; repeated rejects re-spammed
     * the user). On 0 the caller MUST NOT log/notify (Satisfaction::reject
     * early-returns with the existing 'already_reviewed' mapping).
     */
    public static function rejectSatisfaction(int $id, ?int $adminId, ?string $comment): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `satisfactions` SET `status` = 'rejected', `admin_id` = :aid, `admin_comment` = :cmt, `reviewed_at` = NOW()
            WHERE `id` = :id AND `status` = 'pending'");
        $stmt->execute([':aid' => $adminId, ':cmt' => $comment, ':id' => $id]);
        return $stmt->rowCount();
    }

    public static function getSatisfactionFilePaths(int $satisfactionId): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT `file_path` FROM `satisfactions` WHERE `id` = :id AND `file_path` IS NOT NULL AND `file_path` != ''");
        $stmt->execute([':id' => $satisfactionId]);
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }

    // ─── Game Sessions ──────────────────────────────────────────────

    public static function createGameSession(string $gameType, string $mode, int $player1Id, ?int $groupId, ?string $rewardType, int $rewardAmount, int $matchGender = 0, int $totalRounds = 1): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("INSERT INTO `game_sessions` (`game_type`, `mode`, `player1_id`, `group_id`, `reward_type`, `reward_amount`, `board_state`, `status`, `match_gender`, `total_rounds`, `current_round`, `p1_round_wins`, `p2_round_wins`)
            VALUES (:gt, :mode, :p1, :gid, :rt, :ra, :bs, 'waiting', :mg, :tr, 1, 0, 0)");
        $stmt->execute([
            ':gt' => $gameType, ':mode' => $mode, ':p1' => $player1Id, ':gid' => $groupId,
            ':rt' => $rewardType, ':ra' => $rewardAmount, ':bs' => json_encode(['cells' => [0,0,0,0,0,0,0,0,0]]),
            ':mg' => $matchGender, ':tr' => max(1, $totalRounds),
        ]);
        return (int)$db->lastInsertId();
    }

    public static function getGameSession(int $id): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `game_sessions` WHERE `id` = :id LIMIT 1");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        if (!$row) return null;
        $row['board_state'] = json_decode($row['board_state'], true);
        return $row;
    }

    /**
     * Update a game session row. The optional $guards array (F1) turns the
     * update into an ATOMIC conditional claim: for each guard column an
     * `AND {col} = ?` predicate is appended and the number of matched rows
     * is returned. Game::startAnonymous / Game::joinSession pass
     * ['status' => 'waiting'] and treat a 0 return as "lost the join race" —
     * two users tapping join at the same moment can never both enter (and
     * both be debited) since only one UPDATE can match.
     *
     * Guard columns are whitelisted (status, player1_id, player2_id,
     * group_id, mode, game_type) — anything else throws; the $fields
     * whitelist is unchanged (incl. reward_type/reward_amount).
     *
     * @param array<string,mixed> $fields
     * @param array<string,mixed> $guards e.g. ['status' => 'waiting']
     * @return int rowCount (rows matched) — 0 = guard rejected / nothing set.
     */
    public static function updateGameSession(int $id, array $fields, array $guards = []): int
    {
        $db = self::getConnection();
        // reward_type / reward_amount are real game_sessions columns set by
        // the inline friends flow (CallbackHandler::handleInlineStart) —
        // without them in the whitelist the entry fee was silently dropped.
        $allowed = ['player2_id', 'group_id', 'inline_msg_id', 'inline_chat_id', 'p1_board_msg_id', 'p2_board_msg_id', 'board_state', 'current_player', 'status', 'winner_id', 'reward_type', 'reward_amount', 'match_gender', 'cancel_requested_by', 'last_activity', 'total_rounds', 'current_round', 'p1_round_wins', 'p2_round_wins'];
        // v2.9.44 (T20-1): current_player added — TTT handleMove's persist
        // claim can now include ['current_player' => $playerNumber] so two
        // concurrent taps of the SAME move cannot both persist (the second
        // claim loses after the first flipped the turn) and cannot both reach
        // the per-round winner credit.
        // v2.9.45 (R6-4): cancel_requested_by added — the TOD cancel-request
        // flow (R6 reject/take-back branches) clears the marker with a
        // CAS-style guard ['cancel_requested_by' => <expected>] so two
        // racing taps can never double-clear a re-issued request.
        // v2.9.45 (R6-46): current_round added — TTT handleMove pins the
        // round-advance claim on it so a duplicate finishing tap (same player,
        // same round) can never advance twice and double-pay the round fee.
        $guardAllowed = ['status', 'player1_id', 'player2_id', 'group_id', 'mode', 'game_type', 'current_player', 'cancel_requested_by', 'current_round'];
        $sets = [];
        $params = [':id' => $id];
        foreach ($fields as $k => $v) {
            if (in_array($k, $allowed, true)) {
                $sets[] = "`{$k}` = :{$k}";
                $params[":{$k}"] = ($k === 'board_state') ? json_encode($v) : $v;
            }
        }
        if (!$sets) return 0;
        $sql = "UPDATE `game_sessions` SET " . implode(', ', $sets) . " WHERE `id` = :id";
        foreach ($guards as $gk => $gv) {
            if (!in_array($gk, $guardAllowed, true)) {
                throw new \InvalidArgumentException("Invalid game session guard column: {$gk}");
            }
            $sql .= " AND `{$gk}` = :guard_{$gk}";
            $params[":guard_{$gk}"] = $gv;
        }
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }

    /**
     * Find a compatible waiting anonymous session for gender-based matchmaking.
     *
     * Gender matching logic:
     *   - Each waiting session has match_gender (0=any, 1=boy, 2=girl) = what P1 wants
     *   - The joiner has their own gender (1=boy, 2=girl) and their own preference
     *   - Match is valid when:
     *     a) P1's preference matches joiner's gender (or P1 wants "any")
     *     b) Joiner's preference matches P1's gender (or joiner wants "any")
     *
     * @param int $excludeUserId  The joiner's user id (don't match with self)
     * @param int $joinerGender   Joiner's gender (1=boy, 2=girl)
     * @param int $joinerPref     Joiner's preference (0=any, 1=boy, 2=girl)
     * @param string|null $gameType v3.0 (T37): restrict matching to ONE game
     *                              type (TicTacToe parity for every QuickGame);
     *                              null keeps the legacy 'tictactoe' behavior.
     */
    public static function findWaitingAnonymousSession(int $excludeUserId, int $joinerGender = 0, int $joinerPref = 0, ?string $gameType = null): ?array
    {
        $db = self::getConnection();
        // Get all waiting anonymous sessions, then filter in PHP for gender compatibility.
        // vNEXT (14-a/T1): the matchmaker is game-type restricted — anonymous TOD
        // sessions (created by startTOD with a penalty fee in reward_amount) were
        // previously offered to TTT joiners, bricking the game after the join.
        // v3.0 (T37): $gameType selects the bucket (default tictactoe).
        $type = $gameType ?? 'tictactoe';
        $stmt = $db->prepare("SELECT gs.*, u.gender AS p1_gender FROM `game_sessions` gs
            JOIN `users` u ON u.user_id = gs.player1_id
            WHERE gs.`mode` = 'anonymous' AND gs.`status` = 'waiting' AND gs.`game_type` = :gt AND gs.`player1_id` != :uid
            ORDER BY gs.`id` ASC LIMIT 100");
        $stmt->execute([':uid' => $excludeUserId, ':gt' => $type]);
        $rows = $stmt->fetchAll();
        foreach ($rows as $row) {
            $p1Gender = (int)($row['p1_gender'] ?? 0);
            $p1Pref = (int)($row['match_gender'] ?? 0);
            // Check P1's preference vs joiner's gender
            if ($p1Pref !== 0 && $p1Pref !== $joinerGender) continue;
            // Check joiner's preference vs P1's gender
            if ($joinerPref !== 0 && $joinerPref !== $p1Gender) continue;
            // Compatible!
            $row['board_state'] = json_decode($row['board_state'], true);
            return $row;
        }
        return null;
    }

    /**
     * v2.9.45 (R6-2): Anonymous TOD matchmaking — mirror of
     * findWaitingAnonymousSession but for game_type = 'truthordare'.
     *
     * A waiting anonymous TOD session is compatible when:
     *   a) P1's match_gender matches the joiner's gender (or P1 wants "any")
     *   b) Joiner's preference matches P1's gender (or joiner wants "any")
     *   c) board_state.category === $category
     *   d) reward_amount (penalty token) === $fee
     *   e) total_rounds (max free changes) === $maxChanges
     *
     * The returned row has board_state decoded (like getGameSession).
     */
    public static function findWaitingAnonymousTODSession(int $excludeUserId, int $joinerGender = 0, int $joinerPref = 0, string $category = 'normal', int $fee = 0, int $maxChanges = 0): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT gs.*, u.gender AS p1_gender FROM `game_sessions` gs
            JOIN `users` u ON u.user_id = gs.player1_id
            WHERE gs.`mode` = 'anonymous' AND gs.`status` = 'waiting' AND gs.`game_type` = 'truthordare' AND gs.`player1_id` != :uid
            ORDER BY gs.`id` ASC LIMIT 100");
        $stmt->execute([':uid' => $excludeUserId]);
        $rows = $stmt->fetchAll();
        foreach ($rows as $row) {
            $row['board_state'] = json_decode($row['board_state'], true);
            $rowBoard = is_array($row['board_state']) ? $row['board_state'] : [];
            // Setting filter: category / penalty fee / max free changes must match exactly
            if ((string)($rowBoard['category'] ?? 'normal') !== $category) continue;
            if ((int)($row['reward_amount'] ?? 0) !== $fee) continue;
            if ((int)($row['total_rounds'] ?? 0) !== $maxChanges) continue;
            // Gender compatibility (two-sided) — same rules as findWaitingAnonymousSession
            $p1Gender = (int)($row['p1_gender'] ?? 0);
            $p1Pref = (int)($row['match_gender'] ?? 0);
            if ($p1Pref !== 0 && $p1Pref !== $joinerGender) continue;
            if ($joinerPref !== 0 && $joinerPref !== $p1Gender) continue;
            return $row;
        }
        return null;
    }

    /** Count how many waiting anonymous sessions are ahead of this one (for queue position). */
    public static function countWaitingAnonymousSessions(int $sessionId): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT COUNT(*) FROM `game_sessions` WHERE `mode` = 'anonymous' AND `status` = 'waiting' AND `id` < :sid");
        $stmt->execute([':sid' => $sessionId]);
        return (int)$stmt->fetchColumn();
    }

    /**
     * vNEXT (14-a/T1): count waiting anonymous games of ONE game type —
     * for the viewer's info line so it can say how many games are open.
     *
     * Visibility contract (mirrors getAllWaitingAnonymousSessions, which the
     * viewer list is built from): that list excludes the viewer's own sessions
     * (player1_id != viewer). Pass $excludeUserId = the viewer's id to make the
     * count match the list exactly; omit it for a global count.
     *
     * @param string $gameType      'tictactoe' | 'truthordare' (exact game_sessions.game_type value)
     * @param int|null $excludeUserId optional viewer id whose own sessions are excluded
     */
    public static function countWaitingAnonymousGames(string $gameType, ?int $excludeUserId = null): int
    {
        $db = self::getConnection();
        $sql = "SELECT COUNT(*) FROM `game_sessions` WHERE `mode` = 'anonymous' AND `status` = 'waiting' AND `game_type` = :gt";
        $params = [':gt' => $gameType];
        if ($excludeUserId !== null) {
            $sql .= " AND `player1_id` != :uid";
            $params[':uid'] = $excludeUserId;
        }
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    /**
     * v3.0 (T37): count ALL waiting anonymous games (any type) — one query
     * for the «دیدن بازی‌ها» badge across the 42-type catalog.
     */
    public static function countAllWaitingAnonymousGames(?int $excludeUserId = null): int
    {
        $db = self::getConnection();
        $sql = "SELECT COUNT(*) FROM `game_sessions` WHERE `mode` = 'anonymous' AND `status` = 'waiting'";
        $params = [];
        if ($excludeUserId !== null) {
            $sql .= " AND `player1_id` != :uid";
            $params[':uid'] = $excludeUserId;
        }
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    /**
     * Get all waiting anonymous sessions (for the game viewer).
     * F-A12: hard LIMIT 50 — the viewer keyboard renders one button per row,
     * and an unbounded queue (free games make flooding cheap) grew the inline
     * keyboard past Telegram's limits → the viewer edit 400ed for everyone.
     * v3.2 (T42-e) OWNER DIRECTIVE: ordered NEWEST → OLDEST (the queue shows
     * the games users just sent at the top; was ASC = oldest first).
     */
    public static function getAllWaitingAnonymousSessions(int $excludeUserId): array
    {
        $db = self::getConnection();
        // v2.9.44 (T13-4): BOTH game types are listed again. Previously only
        // 'tictactoe' — waiting TOD sessions were hidden because the viewer's
        // TTT join path (joinSession) bricked them. The join path is now
        // TOD-aware (CallbackHandler::joinGameFromViewer routes truthordare
        // through Game::handleTODAction('join')) and joinSession hard-refuses
        // truthordare, so TOD games can safely appear in the list.
        $stmt = $db->prepare("SELECT * FROM `game_sessions` WHERE `mode` = 'anonymous' AND `status` = 'waiting' AND `player1_id` != :uid ORDER BY `id` DESC LIMIT 50");
        $stmt->execute([':uid' => $excludeUserId]);
        $rows = $stmt->fetchAll();
        foreach ($rows as &$row) {
            $row['board_state'] = json_decode($row['board_state'], true);
        }
        return $rows;
    }

    /** Set a user's gender. */
    public static function setUserGender(int $userId, int $gender): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `users` SET `gender` = :g WHERE `user_id` = :uid");
        $stmt->execute([':g' => $gender, ':uid' => $userId]);
    }

    // v2.9.32: Ban/unban system
    public static function banUser(int $userId): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `users` SET `banned` = 1 WHERE `user_id` = :uid");
        $stmt->execute([':uid' => $userId]);
    }

    public static function unbanUser(int $userId): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `users` SET `banned` = 0 WHERE `user_id` = :uid");
        $stmt->execute([':uid' => $userId]);
    }

    public static function isUserBanned(int $userId): bool
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT `banned` FROM `users` WHERE `user_id` = :uid LIMIT 1");
        $stmt->execute([':uid' => $userId]);
        $row = $stmt->fetch();
        return $row !== false && (int)$row['banned'] === 1;
    }

    // v2.9.32: Get count of active/waiting games
    public static function getActiveGamesCount(): int
    {
        $db = self::getConnection();
        return (int)$db->query("SELECT COUNT(*) FROM `game_sessions` WHERE `status` IN ('active', 'waiting')")->fetchColumn();
    }

    // v2.9.33: Multi-player game_players table for Truth or Dare
    public static function createGamePlayersTable(): void
    {
        $db = self::getConnection();
        $db->exec("CREATE TABLE IF NOT EXISTS `game_players` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `session_id` BIGINT NOT NULL,
            `user_id` BIGINT NOT NULL,
            `joined_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `is_active` TINYINT(1) NOT NULL DEFAULT 1,
            `changes_used` INT NOT NULL DEFAULT 0,
            `last_activity` INT NOT NULL DEFAULT 0,
            UNIQUE KEY `uniq_session_user` (`session_id`, `user_id`),
            INDEX `idx_session` (`session_id`),
            INDEX `idx_user` (`user_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // v2.9.35: TOD questions table (admin-managed + imported defaults).
        // Two categories: 'normal' (عادی) and 'couple' (کاپلی).
        // is_default: 0 = admin-added (custom), 1 = imported from JSON (default).
        $db->exec("CREATE TABLE IF NOT EXISTS `tod_questions` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `type` VARCHAR(10) NOT NULL DEFAULT 'truth',
            `gender` VARCHAR(10) NOT NULL DEFAULT 'boys',
            `category` VARCHAR(10) NOT NULL DEFAULT 'normal',
            `question` TEXT NOT NULL,
            `is_default` TINYINT(1) NOT NULL DEFAULT 0,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_type_gender` (`type`, `gender`),
            INDEX `idx_cat_type_gender` (`category`, `type`, `gender`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // Migration for existing installs: add new columns/index if missing (idempotent).
        try { $db->exec("ALTER TABLE `tod_questions` ADD COLUMN `category` VARCHAR(10) NOT NULL DEFAULT 'normal' AFTER `gender`"); } catch (\Throwable $e) {}
        try { $db->exec("ALTER TABLE `tod_questions` ADD COLUMN `is_default` TINYINT(1) NOT NULL DEFAULT 0 AFTER `question`"); } catch (\Throwable $e) {}
        try { $db->exec("ALTER TABLE `tod_questions` ADD INDEX `idx_cat_type_gender` (`category`, `type`, `gender`)"); } catch (\Throwable $e) {}

        // v2.9.34: Track used questions per user (prevent repeats)
        $db->exec("CREATE TABLE IF NOT EXISTS `tod_used_questions` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `user_id` BIGINT NOT NULL,
            `question_key` VARCHAR(50) NOT NULL,
            `question_id` INT NOT NULL,
            `source` VARCHAR(20) NOT NULL DEFAULT 'file',
            `used_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY `uniq_user_question` (`user_id`, `question_key`, `question_id`, `source`),
            INDEX `idx_user` (`user_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }

    // v2.9.35: TOD question management (with category + is_default).
    // Categories: 'normal' (عادی) and 'couple' (کاپلی).
    // is_default: 0 = admin-added (custom), 1 = imported from JSON (default).

    public static function addTODQuestion(string $type, string $gender, string $question, string $category = 'normal', int $isDefault = 0): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("INSERT INTO `tod_questions` (`type`, `gender`, `category`, `question`, `is_default`) VALUES (:t, :g, :c, :q, :d)");
        $stmt->execute([':t' => $type, ':g' => $gender, ':c' => $category, ':q' => $question, ':d' => $isDefault]);
        return (int)$db->lastInsertId();
    }

    public static function deleteTODQuestion(int $id): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("DELETE FROM `tod_questions` WHERE `id` = :id");
        $stmt->execute([':id' => $id]);
        // v2.9.44 (T19-7): drop stale exclusions — re-imports allocate NEW
        // auto-increment ids, so orphaned used-rows would only bloat the
        // per-user NOT IN subquery on every pick.
        $stmt2 = $db->prepare("DELETE FROM `tod_used_questions` WHERE `question_id` = :id AND `source` = 'db'");
        $stmt2->execute([':id' => $id]);
    }

    public static function getTODQuestion(int $id): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `tod_questions` WHERE `id` = :id");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        return $row === false ? null : $row;
    }

    public static function updateTODQuestion(int $id, string $question): bool
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `tod_questions` SET `question` = :q WHERE `id` = :id");
        $stmt->execute([':q' => $question, ':id' => $id]);
        return $stmt->rowCount() > 0;
    }

    /**
     * List questions with filters. isDefault: -1=all, 0=custom, 1=default.
     */
    public static function getTODQuestions(string $type = '', string $gender = '', string $category = '', int $isDefault = -1, string $search = '', int $limit = 0, int $offset = 0): array
    {
        $db = self::getConnection();
        $sql = "SELECT * FROM `tod_questions`";
        $params = [];
        $where = [];
        if ($type !== '') { $where[] = "`type` = :t"; $params[':t'] = $type; }
        if ($gender !== '') { $where[] = "`gender` = :g"; $params[':g'] = $gender; }
        if ($category !== '') { $where[] = "`category` = :c"; $params[':c'] = $category; }
        if ($isDefault !== -1) { $where[] = "`is_default` = :d"; $params[':d'] = $isDefault; }
        if ($search !== '') { $where[] = "`question` LIKE :s"; $params[':s'] = '%' . $search . '%'; }
        if (!empty($where)) $sql .= " WHERE " . implode(' AND ', $where);
        $sql .= " ORDER BY `id` DESC";
        if ($limit > 0) $sql .= " LIMIT " . (int)$limit . " OFFSET " . (int)$offset;
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public static function countTODQuestions(string $type = '', string $gender = '', string $category = '', int $isDefault = -1, string $search = ''): int
    {
        $db = self::getConnection();
        $sql = "SELECT COUNT(*) FROM `tod_questions`";
        $params = [];
        $where = [];
        if ($type !== '') { $where[] = "`type` = :t"; $params[':t'] = $type; }
        if ($gender !== '') { $where[] = "`gender` = :g"; $params[':g'] = $gender; }
        if ($category !== '') { $where[] = "`category` = :c"; $params[':c'] = $category; }
        if ($isDefault !== -1) { $where[] = "`is_default` = :d"; $params[':d'] = $isDefault; }
        if ($search !== '') { $where[] = "`question` LIKE :s"; $params[':s'] = '%' . $search . '%'; }
        if (!empty($where)) $sql .= " WHERE " . implode(' AND ', $where);
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    /**
     * v2.9.35: Pick one random, non-repeated question from the DB for a given
     * category/type/gender, excluding IDs the user has already seen.
     * Returns ['id'=>..,'question'=>..,'source'=>'db'] or null if none available.
     */
    public static function getRandomTODQuestionFromDB(string $type, string $gender, string $category, int $userId): ?array
    {
        $db = self::getConnection();
        $questionKey = $category . '_' . $type . '_' . $gender;

        if ($userId > 0) {
            $sql = "SELECT q.`id`, q.`question` FROM `tod_questions` q
                    WHERE q.`category` = :c AND q.`type` = :t AND q.`gender` = :g
                      AND q.`id` NOT IN (
                          SELECT `question_id` FROM `tod_used_questions`
                          WHERE `user_id` = :uid AND `question_key` = :qk AND `source` = 'db'
                      )
                    ORDER BY RAND() LIMIT 1";
            $stmt = $db->prepare($sql);
            $stmt->execute([':c' => $category, ':t' => $type, ':g' => $gender, ':uid' => $userId, ':qk' => $questionKey]);
        } else {
            $sql = "SELECT `id`, `question` FROM `tod_questions`
                    WHERE `category` = :c AND `type` = :t AND `gender` = :g
                    ORDER BY RAND() LIMIT 1";
            $stmt = $db->prepare($sql);
            $stmt->execute([':c' => $category, ':t' => $type, ':g' => $gender]);
        }
        $row = $stmt->fetch();
        if ($row === false) return null;
        return ['id' => (int)$row['id'], 'question' => (string)$row['question'], 'source' => 'db'];
    }

    /**
     * v2.9.35: Count default (is_default=1) questions in the DB (for import status).
     */
    public static function countDefaultTODQuestions(): int
    {
        $db = self::getConnection();
        $stmt = $db->query("SELECT COUNT(*) FROM `tod_questions` WHERE `is_default` = 1");
        return (int)$stmt->fetchColumn();
    }

    /**
     * v2.9.35: Delete all default questions (used before re-import).
     */
    public static function deleteAllDefaultTODQuestions(): int
    {
        $db = self::getConnection();
        $deleted = (int)$db->exec("DELETE FROM `tod_questions` WHERE `is_default` = 1");
        // v2.9.44 (T19-7): sweep the matching used-question exclusions too.
        $db->exec("DELETE FROM `tod_used_questions` WHERE `source` = 'db'");
        return $deleted;
    }

    /**
     * v2.9.35: Insert a batch of default questions in one transaction.
     * Each row: ['category'=>..,'type'=>..,'gender'=>..,'question'=>..]
     * Returns the number of rows inserted.
     */
    public static function importTODDefaultsBatch(array $rows): int
    {
        if (empty($rows)) return 0;
        $db = self::getConnection();
        $db->beginTransaction();
        try {
            $stmt = $db->prepare("INSERT INTO `tod_questions` (`type`, `gender`, `category`, `question`, `is_default`) VALUES (:t, :g, :c, :q, 1)");
            $n = 0;
            foreach ($rows as $r) {
                $stmt->execute([
                    ':t' => $r['type'],
                    ':g' => $r['gender'],
                    ':c' => $r['category'],
                    ':q' => $r['question'],
                ]);
                $n++;
            }
            $db->commit();
            return $n;
        } catch (\Throwable $e) {
            $db->rollBack();
            error_log('[TOD import] batch failed: ' . $e->getMessage());
            return 0;
        }
    }

    // v2.9.34: Track used questions to prevent repeats
    public static function markQuestionUsed(int $userId, string $questionKey, int $questionId, string $source = 'file'): void
    {
        $db = self::getConnection();
        try {
            $stmt = $db->prepare("INSERT IGNORE INTO `tod_used_questions` (`user_id`, `question_key`, `question_id`, `source`) VALUES (:uid, :qk, :qid, :src)");
            $stmt->execute([':uid' => $userId, ':qk' => $questionKey, ':qid' => $questionId, ':src' => $source]);
        } catch (\Throwable $e) {
            // Ignore duplicates
        }
    }

    public static function getUsedQuestionIds(int $userId, string $questionKey, string $source = ''): array
    {
        $db = self::getConnection();
        if ($source !== '') {
            $stmt = $db->prepare("SELECT `question_id` FROM `tod_used_questions` WHERE `user_id` = :uid AND `question_key` = :qk AND `source` = :src");
            $stmt->execute([':uid' => $userId, ':qk' => $questionKey, ':src' => $source]);
        } else {
            $stmt = $db->prepare("SELECT `question_id` FROM `tod_used_questions` WHERE `user_id` = :uid AND `question_key` = :qk");
            $stmt->execute([':uid' => $userId, ':qk' => $questionKey]);
        }
        $ids = [];
        while ($row = $stmt->fetch()) {
            $ids[] = (int)$row['question_id'];
        }
        return $ids;
    }

    public static function resetUsedQuestions(int $userId): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("DELETE FROM `tod_used_questions` WHERE `user_id` = :uid");
        $stmt->execute([':uid' => $userId]);
    }

    // v2.9.44 (T16-14): bucket-exists check is served by the EXISTING
    // Database::countTODQuestions(type, gender, category) — do NOT redeclare it.

    public static function addGamePlayer(int $sessionId, int $userId): bool
    {
        $db = self::getConnection();
        try {
            // v2.9.44 (T13-7): UPSERT — previously INSERT IGNORE, which meant a
            // player whose row existed with is_active = 0 (left the queue via
            // خروج از صف, or was removed by the 10-min inactivity sweep) could
            // NEVER re-join: the INSERT was silently ignored and the caller
            // still reported success. ON DUPLICATE KEY UPDATE reactivates the
            // existing row. Without a unique key this degrades to a plain
            // INSERT (same as before).
            // v2.9.45 (R6-3): also reset changes_used = 0 — with the R6
            // waiting-list rework a swept/removed player legitimately rejoins
            // LATER (fresh seat in a new or restarted queue), so the old
            // penalty counter must not carry over.
            $now = time();
            $stmt = $db->prepare("INSERT INTO `game_players` (`session_id`, `user_id`, `last_activity`) VALUES (:sid, :uid, :now) ON DUPLICATE KEY UPDATE `is_active` = 1, `changes_used` = 0, `last_activity` = :now2");
            $stmt->execute([':sid' => $sessionId, ':uid' => $userId, ':now' => $now, ':now2' => $now]);
            return true;
        } catch (\Throwable $e) {
            return false;
        }
    }

    /**
     * v2.9.45 (R6-41/HIGH-1): ATOMIC capped seat-take for strict-2-player
     * anonymous TOD. The old read-check-add sequence had an interleaving where
     * two concurrent joiners both passed the cap check and both seated — a
     * "phantom 3rd player" that bricked the cancel flow (requestCancel needs
     * exactly 2 active players) and left the queue stuck forever.
     *
     * This single INSERT..SELECT only succeeds while the session is STILL
     * 'waiting' AND fewer than $cap ACTIVE players are seated. It degrades to
     * the plain upsert behavior for the rejoin case (existing row for this
     * user) — MariaDB evaluates the guard as part of the same statement, so
     * no interleaving can exceed the cap.
     *
     * @return bool true = seat taken (or reclaimed), false = session started
     *              or seat capacity exhausted (caller re-reads to tell them
     *              apart and alerts accordingly).
     */
    public static function addGamePlayerCapped(int $sessionId, int $userId, int $cap = 2): bool
    {
        $db = self::getConnection();
        try {
            $now = time();
            $stmt = $db->prepare(
                "INSERT INTO `game_players` (`session_id`, `user_id`, `last_activity`, `is_active`, `changes_used`) "
                . "SELECT :sid, :uid, :now, 1, 0 FROM (SELECT 1) AS _seed "
                . "WHERE EXISTS (SELECT 1 FROM `game_sessions` WHERE `id` = :sid2 AND `status` = 'waiting') "
                . "AND (SELECT COUNT(*) FROM `game_players` WHERE `session_id` = :sid3 AND `is_active` = 1) < :cap "
                . "ON DUPLICATE KEY UPDATE `is_active` = 1, `changes_used` = 0, `last_activity` = :now2"
            );
            $stmt->execute([
                ':sid' => $sessionId, ':uid' => $userId, ':now' => $now,
                ':sid2' => $sessionId, ':sid3' => $sessionId,
                ':cap' => $cap, ':now2' => $now,
            ]);
            return $stmt->rowCount() > 0;
        } catch (\Throwable $e) {
            error_log('[addGamePlayerCapped] sid=' . $sessionId . ' uid=' . $userId . ' — ' . $e->getMessage());
            return false;
        }
    }

    /**
     * v2.9.45 (R6-56/LOW): bulk-deactivate game_players rows belonging to
     * ended (finished/cancelled/orphaned) sessions. The rows are inert for
     * gameplay (turn rotation reads active players per-session) but used to
     * pile up forever. Runs inside the cron game sweeps — one cheap UPDATE.
     */
    public static function deactivatePlayersOfEndedSessions(): void
    {
        try {
            $db = self::getConnection();
            // v2.9.45 (R6-81): one-time index on is_active — the per-minute
            // reclaim UPDATE used to full-scan game_players forever.
            try {
                $idx = $db->query("SHOW INDEX FROM `game_players` WHERE `Key_name` = 'idx_gp_active'")->fetchAll();
                if (!$idx) {
                    $db->exec("ALTER TABLE `game_players` ADD INDEX `idx_gp_active` (`is_active`)");
                }
            } catch (\Throwable $e) {
                // index creation is best-effort (may race a parallel worker)
            }
            $db->prepare("UPDATE `game_players` gp LEFT JOIN `game_sessions` gs ON gs.`id` = gp.`session_id` SET gp.`is_active` = 0 WHERE gp.`is_active` = 1 AND (gs.`id` IS NULL OR gs.`status` IN ('finished','cancelled'))")->execute();
        } catch (\Throwable $e) {
            error_log('[deactivatePlayersOfEndedSessions] ' . $e->getMessage());
        }
    }

    /**
     * v2.9.45 (R6-80): batched display-name lookup for list renderers
     * (game viewer). Returns userId => displayName (only real names).
     */
    public static function loadUsersDisplayNames(array $userIds): array
    {
        $userIds = array_values(array_unique(array_filter(array_map('intval', $userIds))));
        if (!$userIds) return [];
        $db = self::getConnection();
        $placeholders = implode(',', array_fill(0, count($userIds), '?'));
        try {
            $stmt = $db->prepare("SELECT `user_id`, `display_name` FROM `users` WHERE `user_id` IN ({$placeholders})");
            $stmt->execute($userIds);
            $map = [];
            foreach ($stmt->fetchAll() as $row) {
                $n = (string)($row['display_name'] ?? '');
                if ($n !== '' && $n !== 'user') {
                    $map[(int)$row['user_id']] = $n;
                }
            }
            return $map;
        } catch (\Throwable $e) {
            error_log('[loadUsersDisplayNames] ' . $e->getMessage());
            return [];
        }
    }

    public static function removeGamePlayer(int $sessionId, int $userId): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `game_players` SET `is_active` = 0 WHERE `session_id` = :sid AND `user_id` = :uid");
        $stmt->execute([':sid' => $sessionId, ':uid' => $userId]);
    }

    public static function getGamePlayers(int $sessionId): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `game_players` WHERE `session_id` = :sid ORDER BY `id` ASC");
        $stmt->execute([':sid' => $sessionId]);
        return $stmt->fetchAll();
    }

    public static function getActiveGamePlayers(int $sessionId): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `game_players` WHERE `session_id` = :sid AND `is_active` = 1 ORDER BY `id` ASC");
        $stmt->execute([':sid' => $sessionId]);
        return $stmt->fetchAll();
    }

    public static function updateGamePlayerActivity(int $sessionId, int $userId): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `game_players` SET `last_activity` = :now WHERE `session_id` = :sid AND `user_id` = :uid");
        $stmt->execute([':now' => time(), ':sid' => $sessionId, ':uid' => $userId]);
    }

    public static function incrementPlayerChanges(int $sessionId, int $userId): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `game_players` SET `changes_used` = `changes_used` + 1 WHERE `session_id` = :sid AND `user_id` = :uid");
        $stmt->execute([':sid' => $sessionId, ':uid' => $userId]);
        // Return new value
        $stmt2 = $db->prepare("SELECT `changes_used` FROM `game_players` WHERE `session_id` = :sid AND `user_id` = :uid LIMIT 1");
        $stmt2->execute([':sid' => $sessionId, ':uid' => $userId]);
        return (int)$stmt2->fetchColumn();
    }

    /**
     * v2.9.44 (T16-12): ATOMIC read-and-increment of changes_used.
     * LAST_INSERT_ID(expr) sets the connection-local value AND applies expr, so
     * the returned number is the exact row value THIS caller consumed — two
     * concurrent taps get distinct consecutive values instead of both deciding
     * from the same stale read (which double-charged the penalty). Returns 0
     * when the player row does not exist (removed mid-flight).
     */
    public static function atomicIncrementPlayerChanges(int $sessionId, int $userId): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `game_players` SET `changes_used` = LAST_INSERT_ID(`changes_used` + 1) WHERE `session_id` = :sid AND `user_id` = :uid");
        $stmt->execute([':sid' => $sessionId, ':uid' => $userId]);
        if ($stmt->rowCount() === 0) {
            return 0; // no such player row
        }
        return (int)$db->query("SELECT LAST_INSERT_ID()")->fetchColumn();
    }

    /**
     * v2.9.44 (T16-12): rollback counterpart for atomicIncrementPlayerChanges —
     * used when the over-limit penalty debit fails after the change was
     * atomically consumed.
     */
    public static function decrementPlayerChanges(int $sessionId, int $userId): void
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `game_players` SET `changes_used` = `changes_used` - 1 WHERE `session_id` = :sid AND `user_id` = :uid AND `changes_used` > 0");
        $stmt->execute([':sid' => $sessionId, ':uid' => $userId]);
    }

    public static function getPlayerChanges(int $sessionId, int $userId): int
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT `changes_used` FROM `game_players` WHERE `session_id` = :sid AND `user_id` = :uid LIMIT 1");
        $stmt->execute([':sid' => $sessionId, ':uid' => $userId]);
        return (int)$stmt->fetchColumn();
    }

    public static function isPlayerInGame(int $sessionId, int $userId): bool
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT 1 FROM `game_players` WHERE `session_id` = :sid AND `user_id` = :uid AND `is_active` = 1 LIMIT 1");
        $stmt->execute([':sid' => $sessionId, ':uid' => $userId]);
        return $stmt->fetch() !== false;
    }

    public static function getActiveGameSessionForUser(int $userId, ?string $mode = null, bool $activeOnly = false): ?array
    {
        // v2.9.45 (R6-47/MEDIUM): game_players membership now counts too —
        // anonymous-TOD joiners live ONLY in game_players (player2_id stays
        // NULL), so without the EXISTS clause a user inside an ACTIVE TOD
        // passed every start/join gate and could run a second concurrent game.
        // T46-p: $activeOnly narrows the scan to status='active' — the text
        // routers must never let a newer WAITING queue shadow a live game.
        $statusSql = $activeOnly ? "`status` = 'active'" : "`status` IN ('waiting','active')";
        $db = self::getConnection();
        $memberShip = " OR EXISTS (SELECT 1 FROM `game_players` gp WHERE gp.`session_id` = game_sessions.`id` AND gp.`user_id` = :uid3 AND gp.`is_active` = 1)";
        if ($mode !== null) {
            $stmt = $db->prepare("SELECT * FROM `game_sessions` WHERE ((`player1_id` = :uid OR `player2_id` = :uid2{$memberShip}) AND {$statusSql} AND `mode` = :mode) ORDER BY `id` DESC LIMIT 1");
            $stmt->execute([':uid' => $userId, ':uid2' => $userId, ':uid3' => $userId, ':mode' => $mode]);
        } else {
            $stmt = $db->prepare("SELECT * FROM `game_sessions` WHERE ((`player1_id` = :uid OR `player2_id` = :uid2{$memberShip}) AND {$statusSql}) ORDER BY `id` DESC LIMIT 1");
            $stmt->execute([':uid' => $userId, ':uid2' => $userId, ':uid3' => $userId]);
        }
        $row = $stmt->fetch();
        if (!$row) return null;
        $row['board_state'] = json_decode($row['board_state'], true);
        return $row;
    }

    /**
     * v2.9.45 (R6-1): ACTIVE-only variant of getActiveGameSessionForUser.
     *
     * Waiting-list rework: a user may sit in MANY waiting lists at once
     * (TTT + TOD + group...). Only a game that is actually 'active' locks
     * the user out of starting/joining other games — a never-started
     * waiting session must not block everything (the old
     * status IN ('waiting','active') query did exactly that).
     * Callers that still need the old both-status behavior keep using
     * getActiveGameSessionForUser (e.g. resumeGame).
     */
    public static function getActiveOnlySessionForUser(int $userId, ?string $mode = null): ?array
    {
        // v2.9.45 (R6-47/MEDIUM): same game_players membership visibility as
        // getActiveGameSessionForUser (active-only variant).
        $db = self::getConnection();
        $memberShip = " OR EXISTS (SELECT 1 FROM `game_players` gp WHERE gp.`session_id` = game_sessions.`id` AND gp.`user_id` = :uid3 AND gp.`is_active` = 1)";
        if ($mode !== null) {
            $stmt = $db->prepare("SELECT * FROM `game_sessions` WHERE ((`player1_id` = :uid OR `player2_id` = :uid2{$memberShip}) AND `status` = 'active' AND `mode` = :mode) ORDER BY `id` DESC LIMIT 1");
            $stmt->execute([':uid' => $userId, ':uid2' => $userId, ':uid3' => $userId, ':mode' => $mode]);
        } else {
            $stmt = $db->prepare("SELECT * FROM `game_sessions` WHERE ((`player1_id` = :uid OR `player2_id` = :uid2{$memberShip}) AND `status` = 'active') ORDER BY `id` DESC LIMIT 1");
            $stmt->execute([':uid' => $userId, ':uid2' => $userId, ':uid3' => $userId]);
        }
        $row = $stmt->fetch();
        if (!$row) return null;
        $row['board_state'] = json_decode($row['board_state'], true);
        return $row;
    }

    /**
     * v3.3 (T43): TRACK-aware ACTIVE-only lookup — owner directive.
     *
     * The game world is split into two PARALLEL tracks:
     *   'anonymous' → mode = 'anonymous' (anonymous PV matchmaking)
     *   'normal'    → every other mode (friends / inline / group)
     * A user may hold ONE active game per track and BOTH tracks at once:
     * being inside a group/inline game must never block an anonymous game
     * and vice versa («بازی حالت ناشناس جداست»). Joining a game only checks
     * the track the JOINED game belongs to.
     *
     * `mode` is VARCHAR(20) NOT NULL (schema L455), so `!= 'anonymous'`
     * safely covers friends/group and any legacy value.
     */
    public static function getActiveOnlySessionInTrack(int $userId, string $track): ?array
    {
        $db = self::getConnection();
        $memberShip = " OR EXISTS (SELECT 1 FROM `game_players` gp WHERE gp.`session_id` = game_sessions.`id` AND gp.`user_id` = :uid3 AND gp.`is_active` = 1)";
        // $track is an internal whitelist ('anonymous'|'normal') — safe to inline.
        $modeSql = ($track === 'anonymous') ? "`mode` = 'anonymous'" : "`mode` != 'anonymous'";
        $stmt = $db->prepare("SELECT * FROM `game_sessions` WHERE ((`player1_id` = :uid OR `player2_id` = :uid2{$memberShip}) AND `status` = 'active' AND {$modeSql}) ORDER BY `id` DESC LIMIT 1");
        $stmt->execute([':uid' => $userId, ':uid2' => $userId, ':uid3' => $userId]);
        $row = $stmt->fetch();
        if (!$row) return null;
        $row['board_state'] = json_decode($row['board_state'], true);
        return $row;
    }

    public static function deleteGameSession(int $id): void
    {
        $db = self::getConnection();
        $db->prepare("DELETE FROM `game_sessions` WHERE `id` = :id")->execute([':id' => $id]);
    }

    // ─── Support Tickets (preserved from v1, unchanged) ─────────────

    public static function createSupportTicket(int $userId, string $question, ?string $displayName = null, ?string $adminName = null, ?int $adminId = null, bool $withInitialMessage = true): int
    {
        $db = self::getConnection();
        $name = $displayName ?? self::loadUserDisplayName($userId);
        if ($adminName !== null && $adminId !== null) {
            $stmt = $db->prepare("INSERT INTO `support_tickets` (`user_id`, `question`, `admin_name`, `admin_id`, `status`) VALUES (:uid, :q, :aname, :aid, 'claimed')");
            $stmt->execute([':uid' => $userId, ':q' => $question, ':aname' => $adminName, ':aid' => $adminId]);
        } else {
            $stmt = $db->prepare("INSERT INTO `support_tickets` (`user_id`, `question`) VALUES (:uid, :q)");
            $stmt->execute([':uid' => $userId, ':q' => $question]);
        }
        $ticketId = (int)$db->lastInsertId();
        if ($withInitialMessage) {
            $senderType = ($adminName !== null && $adminId !== null) ? 'admin' : 'user';
            $senderId = ($senderType === 'admin') ? $adminId : $userId;
            $senderName = ($senderType === 'admin') ? $adminName : $name;
            self::addSupportMessage($ticketId, $senderType, $senderId, $senderName, 'text', $question, null, null);
        }
        return $ticketId;
    }

    private static function loadUserDisplayName(int $userId): string
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT `display_name` FROM `users` WHERE `user_id` = :uid LIMIT 1");
        $stmt->execute([':uid' => $userId]);
        $row = $stmt->fetch();
        return $row ? ($row['display_name'] ?? 'user') : 'user';
    }

    public static function getTicket(int $ticketId): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `support_tickets` WHERE `id` = :id LIMIT 1");
        $stmt->execute([':id' => $ticketId]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function claimTicket(int $ticketId, int $adminId, string $adminName): bool
    {
        $db = self::getConnection();
        $stmt = $db->prepare("UPDATE `support_tickets` SET `status` = 'claimed', `admin_id` = :aid, `admin_name` = :aname WHERE `id` = :id AND `status` = 'open'");
        $stmt->execute([':aid' => $adminId, ':aname' => $adminName, ':id' => $ticketId]);
        return $stmt->rowCount() > 0;
    }

    public static function answerTicket(int $ticketId, string $answer): void
    {
        $db = self::getConnection();
        $db->prepare("UPDATE `support_tickets` SET `status` = 'answered', `answer` = :ans, `answer_time` = NOW() WHERE `id` = :id")
           ->execute([':ans' => $answer, ':id' => $ticketId]);
    }

    public static function closeTicket(int $ticketId): void
    {
        $db = self::getConnection();
        $db->prepare("UPDATE `support_tickets` SET `status` = 'closed' WHERE `id` = :id")->execute([':id' => $ticketId]);
    }

    public static function reopenTicket(int $ticketId, int $adminId = 0, string $adminName = ''): void
    {
        $db = self::getConnection();
        if ($adminId > 0) {
            $db->prepare("UPDATE `support_tickets` SET `status` = 'claimed', `admin_id` = :aid, `admin_name` = :aname WHERE `id` = :id")
               ->execute([':aid' => $adminId, ':aname' => $adminName, ':id' => $ticketId]);
        } else {
            $db->prepare("UPDATE `support_tickets` SET `status` = 'open' WHERE `id` = :id")->execute([':id' => $ticketId]);
        }
    }

    public static function getOpenTickets(): array
    {
        $db = self::getConnection();
        return $db->query("SELECT * FROM `support_tickets` WHERE `status` != 'closed' ORDER BY `question_time` DESC")->fetchAll();
    }

    public static function getActiveTicketsForUser(int $userId): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `support_tickets` WHERE `user_id` = :uid AND `status` != 'closed' ORDER BY `id` DESC");
        $stmt->execute([':uid' => $userId]);
        return $stmt->fetchAll();
    }

    public static function deleteTicket(int $ticketId): void
    {
        $db = self::getConnection();
        $db->prepare("DELETE FROM `support_messages` WHERE `ticket_id` = :tid")->execute([':tid' => $ticketId]);
        $db->prepare("DELETE FROM `support_tickets` WHERE `id` = :tid")->execute([':tid' => $ticketId]);
    }

    public static function getTicketFilePaths(int $ticketId): array
    {
        $db = self::getConnection();
        if (!self::columnExists('support_messages', 'file_path')) return [];
        $stmt = $db->prepare("SELECT `file_path` FROM `support_messages` WHERE `ticket_id` = :tid AND `file_path` IS NOT NULL AND `file_path` != ''");
        $stmt->execute([':tid' => $ticketId]);
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }

    public static function getAllTickets(int $limit = 100): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `support_tickets` ORDER BY `question_time` DESC LIMIT :limit");
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public static function addSupportMessage(int $ticketId, string $senderType, int $senderId, ?string $senderName, string $contentType, ?string $text, ?string $fileId = null, ?int $replyTo = null, ?int $tgMessageId = null, ?int $tgChatId = null, ?string $filePath = null): int
    {
        $db = self::getConnection();
        $hasFilePath = self::columnExists('support_messages', 'file_path');
        if ($hasFilePath) {
            $stmt = $db->prepare("INSERT INTO `support_messages` (`ticket_id`, `sender_type`, `sender_id`, `sender_name`, `content_type`, `text`, `file_id`, `reply_to`, `tg_message_id`, `tg_chat_id`, `file_path`)
                VALUES (:tid, :st, :sid, :sname, :ct, :txt, :fid, :rto, :tmsg, :tchat, :fpath)");
            $stmt->execute([
                ':tid' => $ticketId, ':st' => $senderType, ':sid' => $senderId, ':sname' => $senderName,
                ':ct' => $contentType, ':txt' => $text, ':fid' => $fileId, ':rto' => $replyTo,
                ':tmsg' => $tgMessageId, ':tchat' => $tgChatId, ':fpath' => $filePath,
            ]);
        } else {
            $stmt = $db->prepare("INSERT INTO `support_messages` (`ticket_id`, `sender_type`, `sender_id`, `sender_name`, `content_type`, `text`, `file_id`, `reply_to`, `tg_message_id`, `tg_chat_id`)
                VALUES (:tid, :st, :sid, :sname, :ct, :txt, :fid, :rto, :tmsg, :tchat)");
            $stmt->execute([
                ':tid' => $ticketId, ':st' => $senderType, ':sid' => $senderId, ':sname' => $senderName,
                ':ct' => $contentType, ':txt' => $text, ':fid' => $fileId, ':rto' => $replyTo,
                ':tmsg' => $tgMessageId, ':tchat' => $tgChatId,
            ]);
        }
        return (int)$db->lastInsertId();
    }

    public static function updateSupportMessageTgId(int $msgId, int $tgMessageId, int $tgChatId): void
    {
        $db = self::getConnection();
        $db->prepare("UPDATE `support_messages` SET `tg_message_id` = :tmsg, `tg_chat_id` = :tchat WHERE `id` = :id")
           ->execute([':tmsg' => $tgMessageId, ':tchat' => $tgChatId, ':id' => $msgId]);
    }

    public static function getSupportMessagesWithTgId(int $ticketId, int $tgChatId): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `support_messages` WHERE `ticket_id` = :tid AND `tg_message_id` IS NOT NULL AND `tg_chat_id` = :tchat AND `tg_message_id` > 0 ORDER BY `id` ASC");
        $stmt->execute([':tid' => $ticketId, ':tchat' => $tgChatId]);
        return $stmt->fetchAll();
    }

    public static function getSupportMessageByTgId(int $tgMessageId, int $tgChatId): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `support_messages` WHERE `tg_message_id` = :tmsg AND `tg_chat_id` = :tchat LIMIT 1");
        $stmt->execute([':tmsg' => $tgMessageId, ':tchat' => $tgChatId]);
        $row = $stmt->fetch();
        return $row === false ? null : $row;
    }

    public static function getSupportMessages(int $ticketId): array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `support_messages` WHERE `ticket_id` = :tid ORDER BY `created_at` ASC, `id` ASC");
        $stmt->execute([':tid' => $ticketId]);
        return $stmt->fetchAll();
    }

    public static function getSupportMessage(int $messageId): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `support_messages` WHERE `id` = :id LIMIT 1");
        $stmt->execute([':id' => $messageId]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function getActiveTicketForUser(int $userId): ?array
    {
        $db = self::getConnection();
        $stmt = $db->prepare("SELECT * FROM `support_tickets` WHERE `user_id` = :uid AND `status` IN ('open', 'claimed') ORDER BY `id` DESC LIMIT 1");
        $stmt->execute([':uid' => $userId]);
        $row = $stmt->fetch();
        return $row ?: null;
    }
}

/**
 * F11: thrown by Database::createMysqlDump() when the finished dump exceeds
 * the 45 MB Telegram sendDocument guard (the dump file is already unlinked
 * when this is thrown). cron.php's existing catch (\Throwable) handles it
 * safely today; it may catch this class specifically to mark the backup
 * window as handled (set last_backup_run) instead of retrying the same
 * oversized dump every window.
 */
class DatabaseDumpTooLargeException extends \RuntimeException
{
}