<?php

declare(strict_types=1);

class AdminDB
{
    public static function createSchema(): void
    {
        $db = Database::getConnection();

        $db->exec("CREATE TABLE IF NOT EXISTS `admins` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `user_id` BIGINT NOT NULL,
            `username` VARCHAR(255) DEFAULT NULL,
            `display_name` VARCHAR(255) NOT NULL,
            `role` ENUM('owner', 'admin') NOT NULL DEFAULT 'admin',
            `permissions` JSON DEFAULT NULL,
            `added_by` BIGINT DEFAULT NULL,
            `is_active` TINYINT(1) NOT NULL DEFAULT 1,
            `last_login` INT DEFAULT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE INDEX `idx_user_id` (`user_id`),
            INDEX `idx_role` (`role`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // Additive: password_hash column for password-based auth.
        self::addAdminColumnIfMissing('password_hash', "VARCHAR(255) NULL DEFAULT NULL");
        // SECURITY NOTE: the legacy `password_plain` column is kept for backward
        // compatibility with databases that already have it, but we NO LONGER
        // store plaintext passwords in it — setAdminPassword/addAdmin now write
        // an empty string. The super-owner "view password" feature has been
        // removed (it required storing recoverable plaintext credentials, which
        // is a critical vulnerability if the DB is ever leaked). Existing rows
        // are wiped on the next password set/reset.
        self::addAdminColumnIfMissing('password_plain', "VARCHAR(255) NULL DEFAULT NULL");

        $db->exec("CREATE TABLE IF NOT EXISTS `audit_log` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `admin_id` BIGINT NOT NULL,
            `action` VARCHAR(100) NOT NULL,
            `target_type` VARCHAR(50) DEFAULT NULL,
            `target_id` VARCHAR(255) DEFAULT NULL,
            `details` TEXT DEFAULT NULL,
            `ip_address` VARCHAR(45) DEFAULT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_admin_id` (`admin_id`),
            INDEX `idx_action` (`action`),
            INDEX `idx_created` (`created_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $db->exec("CREATE TABLE IF NOT EXISTS `event_log` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `event_type` VARCHAR(50) NOT NULL,
            `user_id` BIGINT DEFAULT NULL,
            `data` JSON DEFAULT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_event_type` (`event_type`),
            INDEX `idx_user_id` (`user_id`),
            INDEX `idx_created` (`created_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $db->exec("CREATE TABLE IF NOT EXISTS `support_tickets` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `user_id` BIGINT NOT NULL,
            `question` TEXT NOT NULL,
            `question_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `admin_id` BIGINT DEFAULT NULL,
            `admin_name` VARCHAR(255) DEFAULT NULL,
            `answer` TEXT DEFAULT NULL,
            `answer_time` TIMESTAMP NULL DEFAULT NULL,
            `status` VARCHAR(20) NOT NULL DEFAULT 'open',
            INDEX `idx_user_id` (`user_id`),
            INDEX `idx_status` (`status`),
            INDEX `idx_question_time` (`question_time`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // Conversation messages table for multi-message ticket threads.
        $db->exec("CREATE TABLE IF NOT EXISTS `support_messages` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `ticket_id` BIGINT NOT NULL,
            `sender_type` VARCHAR(10) NOT NULL DEFAULT 'user',
            `sender_id` BIGINT NOT NULL,
            `sender_name` VARCHAR(255) DEFAULT NULL,
            `content_type` VARCHAR(20) NOT NULL DEFAULT 'text',
            `text` TEXT DEFAULT NULL,
            `file_id` VARCHAR(512) DEFAULT NULL,
            `reply_to` BIGINT DEFAULT NULL,
            `tg_message_id` BIGINT DEFAULT NULL,
            `tg_chat_id` BIGINT DEFAULT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_ticket_id` (`ticket_id`),
            INDEX `idx_created_at` (`created_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // Broadcast messages table — stores sent broadcasts so they can be deleted later.
        $db->exec("CREATE TABLE IF NOT EXISTS `broadcasts` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `admin_id` BIGINT NOT NULL,
            `admin_name` VARCHAR(255) DEFAULT NULL,
            `text` TEXT NOT NULL,
            `content_type` VARCHAR(20) NOT NULL DEFAULT 'text',
            `file_id` VARCHAR(512) DEFAULT NULL,
            `caption` TEXT DEFAULT NULL,
            `message_ids` LONGTEXT DEFAULT NULL,
            `total_sent` INT DEFAULT 0,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_admin_id` (`admin_id`),
            INDEX `idx_created_at` (`created_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // Additive columns for existing tables
        self::addAdminColumnIfMissing2('support_messages', 'tg_message_id', "BIGINT DEFAULT NULL");
        self::addAdminColumnIfMissing2('support_messages', 'tg_chat_id', "BIGINT DEFAULT NULL");
        // file_path: relative path (from project root) to an admin-uploaded file
        // stored on the HOST — so the admin panel can display it without depending
        // on Telegram's file_id (which expires after ~1 hour). When a ticket is
        // deleted, the file on the host is deleted too.
        self::addAdminColumnIfMissing2('support_messages', 'file_path', "VARCHAR(512) DEFAULT NULL");

        // ── REST API tables ──────────────────────────────────────────────
        // api_keys: API keys for external/programmatic access to the admin API.
        // The key itself is NEVER stored in plaintext — only its SHA-256 hash
        // (so a DB leak cannot reveal usable keys). The first 12 chars are
        // stored as `key_prefix` so the admin panel can show a hint like
        // "ntrx_abc1..." without revealing the full key.
        $db->exec("CREATE TABLE IF NOT EXISTS `api_keys` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `key_hash` CHAR(64) NOT NULL,
            `key_prefix` VARCHAR(20) NOT NULL,
            `name` VARCHAR(255) NOT NULL,
            `admin_id` BIGINT NOT NULL,
            `permissions` JSON DEFAULT NULL,
            `is_active` TINYINT(1) NOT NULL DEFAULT 1,
            `expires_at` TIMESTAMP NULL DEFAULT NULL,
            `last_used_at` TIMESTAMP NULL DEFAULT NULL,
            `last_ip_address` VARCHAR(45) DEFAULT NULL,
            `total_requests` BIGINT NOT NULL DEFAULT 0,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE INDEX `idx_key_hash` (`key_hash`),
            INDEX `idx_admin_id` (`admin_id`),
            INDEX `idx_prefix` (`key_prefix`),
            INDEX `idx_active` (`is_active`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // api_logs: audit log of every API call. Used for:
        //   1. Security forensics (who called what, when, from which IP)
        //   2. Rate limiting (count recent requests per key / IP)
        //   3. Usage analytics (most popular endpoints, response times)
        $db->exec("CREATE TABLE IF NOT EXISTS `api_logs` (
            `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `api_key_id` INT DEFAULT NULL,
            `admin_id` BIGINT DEFAULT NULL,
            `endpoint` VARCHAR(255) NOT NULL,
            `method` VARCHAR(10) NOT NULL,
            `params` TEXT DEFAULT NULL,
            `ip_address` VARCHAR(45) DEFAULT NULL,
            `user_agent` VARCHAR(255) DEFAULT NULL,
            `response_code` INT DEFAULT NULL,
            `response_time_ms` INT DEFAULT NULL,
            `error_message` TEXT DEFAULT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_api_key_id` (`api_key_id`),
            INDEX `idx_admin_id` (`admin_id`),
            INDEX `idx_endpoint` (`endpoint`),
            INDEX `idx_created` (`created_at`),
            INDEX `idx_ip` (`ip_address`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        // admin_login_throttle: DB-backed brute-force throttle for the admin
        // panel login. One row per failed attempt (ip_hash = sha256 of the
        // client IP, so raw IPs are never stored). Replaces the old
        // $_SESSION-backed counters, which an attacker could reset simply by
        // dropping their cookies between attempts.
        $db->exec("CREATE TABLE IF NOT EXISTS `admin_login_throttle` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `ip_hash` CHAR(64) NOT NULL,
            `username` VARCHAR(191) NOT NULL,
            `attempted_at` INT NOT NULL,
            INDEX `idx_ip_time` (`ip_hash`, `attempted_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }

    // ─── API Keys Methods ──────────────────────────────────────────

    /**
     * Create a new API key. Returns the FULL plaintext key (only shown once!)
     * plus the DB row id. The key format is: ntrx_<32 hex chars>.
     *
     * The plaintext key is returned to the caller so they can show it to the
     * admin ONCE. We only store the SHA-256 hash in the DB — so even a full
     * DB dump cannot be used to authenticate API calls.
     */
    public static function createApiKey(int $adminId, string $name, array $permissions = [], ?string $expiresAt = null): array
    {
        $db = Database::getConnection();

        // Generate a 32-byte random key. random_bytes is CSPRNG (cryptographically
        // secure) — NOT predictable like mt_rand.
        $rawBytes = random_bytes(24); // 24 bytes → 48 hex chars
        $plainKey = 'ntrx_' . bin2hex($rawBytes);
        $keyHash = hash('sha256', $plainKey);
        // Store first 12 chars of the hex part as a hint prefix (after ntrx_).
        $keyPrefix = 'ntrx_' . substr(bin2hex($rawBytes), 0, 8);

        $stmt = $db->prepare("INSERT INTO `api_keys` (`key_hash`, `key_prefix`, `name`, `admin_id`, `permissions`, `expires_at`)
            VALUES (:hash, :prefix, :name, :admin, :perms, :exp)");
        $stmt->execute([
            ':hash'   => $keyHash,
            ':prefix' => $keyPrefix,
            ':name'   => $name,
            ':admin'  => $adminId,
            ':perms'  => json_encode($permissions),
            ':exp'    => $expiresAt,
        ]);

        return [
            'id'       => (int)$db->lastInsertId(),
            'key'      => $plainKey,   // only returned ONCE here
            'prefix'   => $keyPrefix,
            'name'     => $name,
        ];
    }

    /**
     * Look up an API key by its plaintext value. Returns the key row (with
     * permissions decoded) or null if not found / inactive / expired.
     *
     * This is the AUTHENTICATION step for API calls: the caller sends the
     * plaintext key in the X-API-Key header, we hash it and look up the hash.
     */
    public static function findApiKey(string $plainKey): ?array
    {
        if ($plainKey === '' || !str_starts_with($plainKey, 'ntrx_')) return null;

        $db = Database::getConnection();
        $hash = hash('sha256', $plainKey);
        $stmt = $db->prepare("SELECT * FROM `api_keys` WHERE `key_hash` = :hash AND `is_active` = 1 LIMIT 1");
        $stmt->execute([':hash' => $hash]);
        $row = $stmt->fetch();
        if (!$row) return null;

        // Check expiry
        if ($row['expires_at'] !== null && strtotime($row['expires_at']) < time()) {
            return null;
        }

        $row['permissions'] = $row['permissions'] ? json_decode($row['permissions'], true) : [];
        return $row;
    }

    /** Update last_used_at + IP + total_requests for an API key. */
    public static function touchApiKey(int $keyId, string $ipAddress): void
    {
        $db = Database::getConnection();
        $db->prepare("UPDATE `api_keys` SET `last_used_at` = NOW(), `last_ip_address` = :ip, `total_requests` = `total_requests` + 1 WHERE `id` = :id")
           ->execute([':ip' => $ipAddress, ':id' => $keyId]);
    }

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

    public static function getAllApiKeys(): array
    {
        $db = Database::getConnection();
        $rows = $db->query("SELECT k.*, a.display_name AS admin_name FROM `api_keys` k LEFT JOIN `admins` a ON a.user_id = k.admin_id ORDER BY k.created_at DESC")->fetchAll();
        foreach ($rows as &$row) {
            $row['permissions'] = $row['permissions'] ? json_decode($row['permissions'], true) : [];
        }
        return $rows;
    }

    public static function updateApiKey(int $id, array $data): void
    {
        $db = Database::getConnection();
        $fields = [];
        $params = [':id' => $id];
        foreach (['name', 'is_active'] as $f) {
            if (isset($data[$f])) {
                $fields[] = "`{$f}` = :{$f}";
                $params[":{$f}"] = $data[$f];
            }
        }
        if (isset($data['permissions'])) {
            $fields[] = "`permissions` = :permissions";
            $params[':permissions'] = json_encode($data['permissions']);
        }
        if (isset($data['expires_at'])) {
            $fields[] = "`expires_at` = :expires_at";
            $params[':expires_at'] = $data['expires_at'] ?: null;
        }
        if (empty($fields)) return;
        $db->prepare("UPDATE `api_keys` SET " . implode(', ', $fields) . " WHERE `id` = :id")->execute($params);
    }

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

    // ─── API Audit Log Methods ─────────────────────────────────────

    public static function logApiCall(?int $apiKeyId, ?int $adminId, string $endpoint, string $method, ?string $params, string $ip, ?string $userAgent, int $responseCode, int $responseTimeMs, ?string $error = null): void
    {
        try {
            $db = Database::getConnection();
            $db->prepare("INSERT INTO `api_logs` (`api_key_id`, `admin_id`, `endpoint`, `method`, `params`, `ip_address`, `user_agent`, `response_code`, `response_time_ms`, `error_message`)
                VALUES (:kid, :aid, :ep, :m, :p, :ip, :ua, :rc, :rt, :err)")
               ->execute([
                   ':kid'  => $apiKeyId,
                   ':aid'  => $adminId,
                   ':ep'   => $endpoint,
                   ':m'    => $method,
                   ':p'    => $params,
                   ':ip'   => $ip,
                   ':ua'   => $userAgent,
                   ':rc'   => $responseCode,
                   ':rt'   => $responseTimeMs,
                   ':err'  => $error,
               ]);
        } catch (\Throwable $e) {
            error_log("logApiCall failed: " . $e->getMessage());
        }
    }

    /**
     * Count API calls from a given IP in the last $window seconds.
     * Used for rate limiting (per-IP, even without an API key).
     */
    public static function countRecentApiCallsByIp(string $ip, int $window): int
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT COUNT(*) FROM `api_logs` WHERE `ip_address` = :ip AND `created_at` >= (NOW() - INTERVAL :w SECOND)");
        $stmt->bindValue(':ip', $ip);
        $stmt->bindValue(':w', $window, PDO::PARAM_INT);
        $stmt->execute();
        return (int)$stmt->fetchColumn();
    }

    /** Count API calls from a given API key in the last $window seconds. */
    public static function countRecentApiCallsByKey(int $keyId, int $window): int
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT COUNT(*) FROM `api_logs` WHERE `api_key_id` = :kid AND `created_at` >= (NOW() - INTERVAL :w SECOND)");
        $stmt->bindValue(':kid', $keyId, PDO::PARAM_INT);
        $stmt->bindValue(':w', $window, PDO::PARAM_INT);
        $stmt->execute();
        return (int)$stmt->fetchColumn();
    }

    public static function getApiLogStats(): array
    {
        $db = Database::getConnection();
        $total = (int)$db->query("SELECT COUNT(*) FROM `api_logs`")->fetchColumn();
        $last24h = (int)$db->query("SELECT COUNT(*) FROM `api_logs` WHERE `created_at` >= (NOW() - INTERVAL 24 HOUR)")->fetchColumn();
        $errors24h = (int)$db->query("SELECT COUNT(*) FROM `api_logs` WHERE `created_at` >= (NOW() - INTERVAL 24 HOUR) AND `response_code` >= 400")->fetchColumn();
        $activeKeys = (int)$db->query("SELECT COUNT(*) FROM `api_keys` WHERE `is_active` = 1")->fetchColumn();
        $totalKeys = (int)$db->query("SELECT COUNT(*) FROM `api_keys`")->fetchColumn();
        return [
            'total_calls'   => $total,
            'calls_24h'     => $last24h,
            'errors_24h'    => $errors24h,
            'active_keys'   => $activeKeys,
            'total_keys'    => $totalKeys,
        ];
    }

    public static function getRecentApiLogs(int $limit = 100): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT l.*, k.key_prefix, k.name AS key_name, a.display_name AS admin_name
            FROM `api_logs` l
            LEFT JOIN `api_keys` k ON k.id = l.api_key_id
            LEFT JOIN `admins` a ON a.user_id = l.admin_id
            ORDER BY l.created_at DESC LIMIT :limit");
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    private static function addAdminColumnIfMissing2(string $table, string $column, string $definition): void
    {
        $db = Database::getConnection();
        try {
            $cols = $db->query("SHOW COLUMNS FROM `{$table}`")->fetchAll(PDO::FETCH_COLUMN);
            if (!in_array($column, $cols, true)) {
                $db->exec("ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}");
            }
        } catch (\Throwable $e) {
            error_log("addColumnIfMissing({$table}.{$column}) failed: " . $e->getMessage());
        }
    }

    private static function addAdminColumnIfMissing(string $column, string $definition): void
    {
        $db = Database::getConnection();
        try {
            $cols = $db->query("SHOW COLUMNS FROM `admins`")->fetchAll(PDO::FETCH_COLUMN);
            if (!in_array($column, $cols, true)) {
                $db->exec("ALTER TABLE `admins` ADD COLUMN `{$column}` {$definition}");
            }
        } catch (\Throwable $e) {
            error_log("addAdminColumnIfMissing(admins.{$column}) failed: " . $e->getMessage());
        }
    }

    // ─── Admin Methods ─────────────────────────────────────────────

    public static function getAdmin(int $userId): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT * FROM `admins` WHERE `user_id` = :uid LIMIT 1");
        $stmt->execute([':uid' => $userId]);
        $row = $stmt->fetch();
        if (!$row) return null;
        $row['permissions'] = $row['permissions'] ? json_decode($row['permissions'], true) : [];
        return $row;
    }

    public static function getAllAdmins(): array
    {
        $db = Database::getConnection();
        $stmt = $db->query("SELECT * FROM `admins` ORDER BY `role` DESC, `created_at` ASC");
        $rows = $stmt->fetchAll();
        foreach ($rows as &$row) {
            $row['permissions'] = $row['permissions'] ? json_decode($row['permissions'], true) : [];
        }
        return $rows;
    }

    public static function addAdmin(int $userId, string $displayName, string $role, ?string $username, int $addedBy, array $permissions = [], ?string $passwordHash = null, ?string $passwordPlain = null): void
    {
        $db = Database::getConnection();
        // SECURITY: password_plain is NEVER populated — we only keep the bcrypt
        // hash. The $passwordPlain argument is accepted for call-site
        // compatibility but ignored (forced to empty string).
        $passwordPlain = '';
        $stmt = $db->prepare("INSERT INTO `admins` (`user_id`, `username`, `display_name`, `role`, `permissions`, `added_by`, `password_hash`, `password_plain`)
            VALUES (:uid, :username, :name, :role, :perms, :added_by, :phash, :pplain)
            ON DUPLICATE KEY UPDATE `username` = VALUES(`username`), `display_name` = VALUES(`display_name`), `role` = VALUES(`role`), `permissions` = VALUES(`permissions`), `is_active` = 1, `password_plain` = ''" . ($passwordHash !== null ? ", `password_hash` = VALUES(`password_hash`)" : ""));
        $stmt->execute([
            ':uid'      => $userId,
            ':username' => $username,
            ':name'     => $displayName,
            ':role'     => $role,
            ':perms'    => json_encode($permissions),
            ':added_by' => $addedBy,
            ':phash'    => $passwordHash,
            ':pplain'   => $passwordPlain,
        ]);
    }

    /** Set/replace an admin's password. Stores ONLY a bcrypt hash; the legacy
     *  plaintext column is explicitly wiped so any old plaintext is purged. */
    public static function setAdminPassword(int $userId, string $password): void
    {
        $db = Database::getConnection();
        $hash = password_hash($password, PASSWORD_BCRYPT);
        $db->prepare("UPDATE `admins` SET `password_hash` = :phash, `password_plain` = '' WHERE `user_id` = :uid")
           ->execute([':phash' => $hash, ':uid' => $userId]);
    }

    /**
     * @deprecated The plaintext-password retrieval feature has been removed
     *  for security. This method always returns null. Callers should reset a
     *  forgotten password via setAdminPassword() with a freshly-generated
     *  random value instead of trying to read the old one back.
     */
    public static function getAdminPassword(int $userId): ?string
    {
        return null;
    }

    /** Verify a password against the stored hash. */
    public static function verifyAdminPassword(int $userId, string $password): bool
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT `password_hash` FROM `admins` WHERE `user_id` = :uid LIMIT 1");
        $stmt->execute([':uid' => $userId]);
        $row = $stmt->fetch();
        if (!$row || empty($row['password_hash'])) return false;
        return password_verify($password, $row['password_hash']);
    }

    /** Find an admin by display_name (used for password login). */
    public static function findAdminByName(string $displayName): ?array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT * FROM `admins` WHERE `display_name` = :name AND `is_active` = 1 LIMIT 1");
        $stmt->execute([':name' => $displayName]);
        $row = $stmt->fetch();
        if (!$row) return null;
        $row['permissions'] = $row['permissions'] ? json_decode($row['permissions'], true) : [];
        return $row;
    }

    public static function updateAdmin(int $userId, array $data): void
    {
        $db = Database::getConnection();
        $fields = [];
        $params = [':uid' => $userId];

        foreach (['display_name', 'username', 'role', 'is_active'] as $field) {
            if (isset($data[$field])) {
                $fields[] = "`{$field}` = :{$field}";
                $params[":{$field}"] = $data[$field];
            }
        }
        if (isset($data['permissions'])) {
            $fields[] = "`permissions` = :permissions";
            $params[':permissions'] = json_encode($data['permissions']);
        }

        if (empty($fields)) return;

        $db->prepare("UPDATE `admins` SET " . implode(', ', $fields) . " WHERE `user_id` = :uid")->execute($params);
    }

    public static function removeAdmin(int $userId): void
    {
        $db = Database::getConnection();
        $db->prepare("DELETE FROM `admins` WHERE `user_id` = :uid AND `role` != 'owner'")->execute([':uid' => $userId]);
    }

    public static function updateLastLogin(int $userId): void
    {
        $db = Database::getConnection();
        $db->prepare("UPDATE `admins` SET `last_login` = UNIX_TIMESTAMP() WHERE `user_id` = :uid")->execute([':uid' => $userId]);

        // SECURITY: updateLastLogin is only called on a SUCCESSFUL login
        // (AdminAuth::login / AdminAuth::loginWithPassword) — clear this
        // admin's failed-attempt rows for the current IP so a legitimate
        // login after a few typos doesn't leave the operator near the
        // brute-force throttle limit.
        try {
            $admin = self::getAdmin($userId);
            $username = $admin !== null ? strtolower(trim((string)$admin['display_name'])) : '';
            if ($username !== '') {
                // Same CF-aware IP attribution as the throttle check/record —
                // otherwise the clear would no-op behind Cloudflare.
                self::clearLoginThrottle(self::clientIpHash(), $username);
            }
        } catch (\Throwable $e) {
            error_log('clearLoginThrottle after successful login failed: ' . $e->getMessage());
        }
    }

    // ─── Audit Log ─────────────────────────────────────────────────

    public static function logAction(int $adminId, string $action, ?string $targetType = null, ?string $targetId = null, ?string $details = null): void
    {
        $db = Database::getConnection();
        $db->prepare("INSERT INTO `audit_log` (`admin_id`, `action`, `target_type`, `target_id`, `details`, `ip_address`)
            VALUES (:admin, :action, :target_type, :target_id, :details, :ip)")
           ->execute([
               ':admin'      => $adminId,
               ':action'     => $action,
               ':target_type'=> $targetType,
               ':target_id'  => $targetId,
               ':details'    => $details,
               ':ip'         => $_SERVER['REMOTE_ADDR'] ?? null,
           ]);
    }

    public static function getAuditLog(int $limit = 100, int $offset = 0): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT a.*, ad.display_name AS admin_name FROM `audit_log` a LEFT JOIN `admins` ad ON ad.user_id = a.admin_id ORDER BY a.created_at DESC LIMIT :limit OFFSET :offset");
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public static function getAuditLogCount(): int
    {
        $db = Database::getConnection();
        return (int)$db->query("SELECT COUNT(*) FROM `audit_log`")->fetchColumn();
    }

    // ─── Event Log (Analytics) ─────────────────────────────────────

    public static function logEvent(string $eventType, ?int $userId = null, ?array $data = null): void
    {
        $db = Database::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]);
    }

    // ─── Statistics ────────────────────────────────────────────────

    public static function getStats(): array
    {
        $db = Database::getConnection();

        $maxCoins = (int)(Settings::getSetting('stats_max_tokens') ?? Settings::getSetting('stats_max_coins') ?? 10000);
        // The main admin (admin_chat_id from config) is EXCLUDED from all stats.
        $adminId = (int)($GLOBALS['config']['referral']['admin_chat_id'] ?? 0);
        $excl = "AND `user_id` != {$adminId}";

        // Exclude: admin + users with more than $maxCoins
        $totalUsers = (int)$db->query("SELECT COUNT(*) FROM `users` WHERE `storage` <= {$maxCoins} {$excl}")->fetchColumn();
        $activeUsers = (int)$db->query("SELECT COUNT(*) FROM `users` WHERE `updated_at` >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND `storage` <= {$maxCoins} {$excl}")->fetchColumn();
        $newUsersToday = (int)$db->query("SELECT COUNT(*) FROM `users` WHERE `created_at` >= CURDATE() {$excl}")->fetchColumn();
        $newUsersWeek = (int)$db->query("SELECT COUNT(*) FROM `users` WHERE `created_at` >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) {$excl}")->fetchColumn();
        $newUsersMonth = (int)$db->query("SELECT COUNT(*) FROM `users` WHERE `created_at` >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) {$excl}")->fetchColumn();
        $totalServers = (int)$db->query("SELECT COUNT(*) FROM `user_servers` WHERE `user_id` != {$adminId}")->fetchColumn();
        $totalStorage = (int)$db->query("SELECT COALESCE(SUM(`storage`), 0) FROM `users` WHERE `storage` <= {$maxCoins} {$excl}")->fetchColumn();
        $totalSubs = (int)$db->query("SELECT COALESCE(SUM(`sub_count`), 0) FROM `users` WHERE `storage` <= {$maxCoins} {$excl}")->fetchColumn();
        $totalChannels = (int)$db->query("SELECT COUNT(*) FROM `channels` WHERE `is_active` = 1")->fetchColumn();
        $bannedUsers = (int)$db->query("SELECT COUNT(DISTINCT `user_id`) FROM `rate_limits` WHERE `ban_until` > UNIX_TIMESTAMP() AND `user_id` != {$adminId}")->fetchColumn();
        // v2.9.45 (R6-92): count the PERMANENT panel bans (users.banned) — the
        // dashboard «کاربران مسدود» previously counted temp rate-limit rows.
        $totalRateLimits = 0;
        try {
            $totalRateLimits = (int)$db->query("SELECT COUNT(*) FROM `users` WHERE `banned` = 1")->fetchColumn();
        } catch (\Throwable $e) {
            $totalRateLimits = (int)$db->query("SELECT COUNT(*) FROM `rate_limits` WHERE `ban_until` > 0 AND `user_id` != {$adminId}")->fetchColumn();
        }

        $totalAllocatedGb = 0;
        if (Database::columnExists('user_servers', 'gb')) {
            $totalAllocatedGb = (int)$db->query("SELECT COALESCE(SUM(`gb`), 0) FROM `user_servers` WHERE `user_id` != {$adminId}")->fetchColumn();
        }

        // Support ticket stats — open = not answered and not closed
        $totalTickets = 0;
        $answeredTickets = 0;
        $openTickets = 0;
        try {
            $totalTickets = (int)$db->query("SELECT COUNT(*) FROM `support_tickets`")->fetchColumn();
            $answeredTickets = (int)$db->query("SELECT COUNT(*) FROM `support_tickets` WHERE `status` = 'answered'")->fetchColumn();
            $openTickets = (int)$db->query("SELECT COUNT(*) FROM `support_tickets` WHERE `status` != 'closed'")->fetchColumn();
        } catch (\Throwable $e) {
            // Table might not exist yet — ignore.
        }

        return [
            'total_users'     => $totalUsers,
            'active_users'    => $activeUsers,
            'new_today'       => $newUsersToday,
            'new_week'        => $newUsersWeek,
            'new_month'       => $newUsersMonth,
            'total_servers'   => $totalServers,
            'total_storage'   => $totalStorage,
            'total_allocated_gb' => $totalAllocatedGb,
            'total_subs'      => $totalSubs,
            'total_channels'  => $totalChannels,
            'banned_users'    => $bannedUsers,
            'total_rate_limits' => $totalRateLimits,
            'stats_max_coins' => $maxCoins,
            'total_tickets'   => $totalTickets,
            'answered_tickets' => $answeredTickets,
            'open_tickets'    => $openTickets,
        ];
    }

    public static function getUserGrowthData(int $days = 30): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT DATE(`created_at`) AS day, COUNT(*) AS cnt FROM `users` WHERE `created_at` >= DATE_SUB(CURDATE(), INTERVAL :days DAY) GROUP BY DATE(`created_at`) ORDER BY day ASC");
        $stmt->bindValue(':days', $days, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public static function getCommandUsageData(int $days = 30): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT `event_type`, COUNT(*) AS cnt FROM `event_log` WHERE `created_at` >= DATE_SUB(NOW(), INTERVAL :days DAY) GROUP BY `event_type` ORDER BY cnt DESC");
        $stmt->bindValue(':days', $days, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public static function getHourlyActivity(): array
    {
        $db = Database::getConnection();
        $stmt = $db->query("SELECT HOUR(`created_at`) AS hour, COUNT(*) AS cnt FROM `event_log` WHERE `created_at` >= DATE_SUB(NOW(), INTERVAL 24 HOUR) GROUP BY HOUR(`created_at`) ORDER BY hour ASC");
        return $stmt->fetchAll();
    }

    public static function getTopReferrers(int $limit = 10): array
    {
        $db = Database::getConnection();
        $adminId = (int)($GLOBALS['config']['referral']['admin_chat_id'] ?? 0);
        $stmt = $db->prepare("SELECT `user_id`, `sub_count` FROM `users` WHERE `sub_count` > 0 AND `user_id` != {$adminId} ORDER BY `sub_count` DESC LIMIT :limit");
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public static function getStorageDistribution(): array
    {
        $db = Database::getConnection();
        $maxCoins = (int)(Settings::getSetting('stats_max_tokens') ?? Settings::getSetting('stats_max_coins') ?? 10000);
        $adminId = (int)($GLOBALS['config']['referral']['admin_chat_id'] ?? 0);
        $stmt = $db->query("SELECT
            SUM(CASE WHEN `storage` = 0 THEN 1 ELSE 0 END) AS zero,
            SUM(CASE WHEN `storage` BETWEEN 1 AND 3 THEN 1 ELSE 0 END) AS low,
            SUM(CASE WHEN `storage` BETWEEN 4 AND 10 THEN 1 ELSE 0 END) AS medium,
            SUM(CASE WHEN `storage` > 10 AND `storage` <= {$maxCoins} THEN 1 ELSE 0 END) AS high
        FROM `users` WHERE `storage` <= {$maxCoins} AND `user_id` != {$adminId}");
        return $stmt->fetch();
    }

    public static function getStatusDistribution(): array
    {
        $db = Database::getConnection();
        $adminId = (int)($GLOBALS['config']['referral']['admin_chat_id'] ?? 0);
        $stmt = $db->query("SELECT `status`, COUNT(*) AS cnt FROM `users` WHERE `user_id` != {$adminId} GROUP BY `status` ORDER BY cnt DESC");
        return $stmt->fetchAll();
    }

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

    public static function searchUsers(string $query): array
    {
        $db = Database::getConnection();
        // Resilient: if the display_name column hasn't been added yet, fall
        // back to searching only user_id and status (avoids "Unknown column" → HTTP 500).
        $hasDisplayName = Database::columnExists('users', 'display_name');
        $like = '%' . $query . '%';

        if ($hasDisplayName) {
            // Use three separate placeholders (:q1, :q2, :q3) because PDO with
            // ATTR_EMULATE_PREPARES=false (native prepared statements) does NOT
            // allow the same named placeholder to be used more than once.
            $sql = "SELECT u.*, (SELECT COUNT(*) FROM `user_servers` s WHERE s.user_id = u.user_id) AS server_count
                FROM `users` u
                WHERE CAST(`u`.`user_id` AS CHAR) LIKE :q1 OR `u`.`status` LIKE :q2 OR `u`.`display_name` LIKE :q3
                ORDER BY `u`.`user_id` LIMIT 50";
            $stmt = $db->prepare($sql);
            $stmt->execute([':q1' => $like, ':q2' => $like, ':q3' => $like]);
        } else {
            $sql = "SELECT u.*, (SELECT COUNT(*) FROM `user_servers` s WHERE s.user_id = u.user_id) AS server_count
                FROM `users` u
                WHERE CAST(`u`.`user_id` AS CHAR) LIKE :q1 OR `u`.`status` LIKE :q2
                ORDER BY `u`.`user_id` LIMIT 50";
            $stmt = $db->prepare($sql);
            $stmt->execute([':q1' => $like, ':q2' => $like]);
        }
        return $stmt->fetchAll();
    }

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

        // Ensure display_name exists in the row (fallback for old schema).
        if (!isset($user['display_name'])) {
            $user['display_name'] = 'user';
        }

        $stmt2 = $db->prepare("SELECT * FROM `user_servers` WHERE `user_id` = :uid");
        $stmt2->execute([':uid' => $userId]);
        $user['servers'] = $stmt2->fetchAll();

        $stmt3 = $db->prepare("SELECT * FROM `event_log` WHERE `user_id` = :uid ORDER BY `created_at` DESC LIMIT 20");
        $stmt3->execute([':uid' => $userId]);
        $user['recent_events'] = $stmt3->fetchAll();

        $stmt4 = $db->prepare("SELECT * FROM `rate_limits` WHERE `user_id` = :uid ORDER BY `request_time` DESC LIMIT 20");
        $stmt4->execute([':uid' => $userId]);
        $user['rate_limits'] = $stmt4->fetchAll();

        return $user;
    }

    /**
     * Delete a user and ALL their dependent rows in one transaction.
     *
     * The schema has no FK CASCADE between users and most child tables (the
     * old API docblock claim was wrong — see the audit), so the graph is
     * cleaned up manually. Order matters: the message tables have NO user_id
     * column (they hang off ticket_id / withdrawal_id + sender_id) so they are
     * deleted via subquery BEFORE their parent rows; the users row goes LAST.
     *
     * On failure the transaction is rolled back, the error is logged and the
     * exception is rethrown so callers can show a real error instead of
     * flashing a success that never happened.
     */
    public static function deleteUser(int $userId): void
    {
        $db = Database::getConnection();
        try {
            $db->beginTransaction();

            // Child tables that carry a user_id column.
            foreach (['user_servers', 'wallet_ledger', 'ntx_records', 'satisfactions',
                      'user_tasks', 'rate_limits'] as $table) {
                $db->prepare("DELETE FROM `{$table}` WHERE `user_id` = :uid")->execute([':uid' => $userId]);
            }

            // Message tables (no user_id): delete via the parent id, plus the
            // user's own sender rows as a defensive sweep. NOTE: native prepared
            // statements (ATTR_EMULATE_PREPARES=false) reject a named placeholder
            // used twice — hence :uid / :uid2.
            $db->prepare("DELETE FROM `withdrawal_messages` WHERE `withdrawal_id` IN
                (SELECT `id` FROM `withdrawals` WHERE `user_id` = :uid) OR `sender_id` = :uid2")
               ->execute([':uid' => $userId, ':uid2' => $userId]);
            $db->prepare("DELETE FROM `support_messages` WHERE `ticket_id` IN
                (SELECT `id` FROM `support_tickets` WHERE `user_id` = :uid) OR `sender_id` = :uid2")
               ->execute([':uid' => $userId, ':uid2' => $userId]);

            // Parent rows.
            $db->prepare("DELETE FROM `withdrawals` WHERE `user_id` = :uid")->execute([':uid' => $userId]);
            $db->prepare("DELETE FROM `support_tickets` WHERE `user_id` = :uid")->execute([':uid' => $userId]);

            // Recharge codes keep their used/is_used history — just detach them
            // from the vanishing user so the column never dangles.
            $db->prepare("UPDATE `recharge_codes` SET `used_by` = NULL WHERE `used_by` = :uid")
               ->execute([':uid' => $userId]);

            // Cancel any live game the user is part of (waiting/active only —
            // finished/cancelled sessions are history and stay). TOD participants
            // hang off game_players, hence the IN subquery (different table → no
            // MySQL 1093 restriction).
            // v2.9.45 (R6-60/M3): refund the SURVIVING opponent of an ACTIVE
            // tictactoe session for its uncompleted rounds BEFORE the bulk
            // cancel — the deleted user's departure used to burn the opponent's
            // prepaid fee×(total_rounds − completed rounds).
            $activeTtt = $db->prepare("SELECT * FROM `game_sessions` WHERE `status` = 'active' AND `game_type` = 'tictactoe' AND (`player1_id` = :uid OR `player2_id` = :uid2)");
            $activeTtt->execute([':uid' => $userId, ':uid2' => $userId]);
            foreach ($activeTtt->fetchAll() as $live) {
                $survivor = ((int)$live['player1_id'] === $userId)
                    ? (int)$live['player2_id']
                    : (int)$live['player1_id'];
                $unplayed = max(0, (int)($live['total_rounds'] ?? 1)
                    - (int)($live['p1_round_wins'] ?? 0) - (int)($live['p2_round_wins'] ?? 0));
                $refundAmt = ((int)($live['reward_amount'] ?? 0)) * $unplayed;
                if ($survivor > 0 && $refundAmt > 0 && $survivor !== $userId) {
                    try {
                        Wallet::credit($survivor, (string)($live['reward_type'] ?? 'ntx'), $refundAmt, 'game_opponent_deleted_refund', 'game_session', (int)$live['id']);
                    } catch (\Throwable $e) {
                        error_log('[deleteUser] survivor refund failed sid=' . $live['id'] . ': ' . $e->getMessage());
                    }
                }
            }
            $db->prepare("UPDATE `game_sessions` SET `status` = 'cancelled'
                WHERE `status` IN ('waiting', 'active')
                  AND (`player1_id` = :uid OR `player2_id` = :uid2
                       OR `id` IN (SELECT `session_id` FROM `game_players` WHERE `user_id` = :uid3))")
               ->execute([':uid' => $userId, ':uid2' => $userId, ':uid3' => $userId]);
            $db->prepare("DELETE FROM `game_players` WHERE `user_id` = :uid")->execute([':uid' => $userId]);

            // The user row itself — last, after every reference is gone.
            $db->prepare("DELETE FROM `users` WHERE `user_id` = :uid")->execute([':uid' => $userId]);

            $db->commit();
        } catch (\Throwable $e) {
            if ($db->inTransaction()) {
                $db->rollBack();
            }
            error_log("[AdminDB] deleteUser({$userId}) failed, rolled back: " . $e->getMessage());
            throw $e;
        }
    }

    public static function updateUserStorage(int $userId, int $storage): void
    {
        $db = Database::getConnection();
        $db->prepare("UPDATE `users` SET `storage` = :storage WHERE `user_id` = :uid")->execute([':storage' => $storage, ':uid' => $userId]);
    }

    /**
     * Ban a user (sets users.banned = 1). The bot checks this flag on every
     * request and refuses to interact with banned users.
     */
    public static function banUser(int $userId): void
    {
        Database::banUser($userId);
    }

    /**
     * Lift a previously-applied ban (sets users.banned = 0).
     */
    public static function unbanUser(int $userId): void
    {
        Database::unbanUser($userId);
    }

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

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

    // ─── Login Throttle (DB-backed) ────────────────────────────────

    /**
     * SHA-256 of the CLIENT IP for throttle attribution (check/record/clear
     * all use this so the hashed value is consistent across login paths).
     *
     * Behind Cloudflare the edge puts the original client IP in
     * HTTP_CF_CONNECTING_IP — use it when it is a syntactically valid IP
     * (filter_var), otherwise fall back to REMOTE_ADDR. Without this, every
     * visitor shares an edge IP and the throttle would lock out everyone
     * after one attacker's failures.
     *
     * NOTE: this header is only trustworthy while the origin is exclusively
     * reachable through Cloudflare; a client hitting the origin directly can
     * spoof it (documented residual risk, same as ApiAuth::clientIp).
     */
    public static function clientIpHash(): string
    {
        // v2.9.45 (R6-64/HIGH): shared trusted-IP resolver (CF headers trusted
        // only behind a real Cloudflare edge — see trusted_client_ip()).
        $ip = function_exists('trusted_client_ip') ? trusted_client_ip() : ($_SERVER['REMOTE_ADDR'] ?? '0.0.0.0');
        return hash('sha256', $ip);
    }

    /**
     * Check the login brute-force throttle for an (ip_hash, username) pair.
     * Counts failed attempts inside the moving $windowSeconds window.
     *
     * @return bool true = BLOCKED (>= $maxAttempts recent failures), false = allowed.
     */
    public static function checkLoginThrottle(string $ipHash, string $username, int $windowSeconds = 300, int $maxAttempts = 5): bool
    {
        $db = Database::getConnection();
        $cutoff = time() - max(1, $windowSeconds);
        $stmt = $db->prepare("SELECT COUNT(*) FROM `admin_login_throttle`
            WHERE `ip_hash` = :ip AND `username` = :u AND `attempted_at` > :cutoff");
        $stmt->execute([
            ':ip'     => $ipHash,
            ':u'      => $username,
            ':cutoff' => $cutoff,
        ]);
        if ((int)$stmt->fetchColumn() >= $maxAttempts) {
            return true;
        }
        // v2.9.45 (R6-66): GLOBAL per-username counter — the (ip,username)
        // pair alone allowed password-spraying N usernames from one IP
        // (5 tries × N usernames per window). 3× the per-IP budget applies
        // to the username across ALL sources.
        $stmt2 = $db->prepare("SELECT COUNT(*) FROM `admin_login_throttle`
            WHERE `username` = :u2 AND `attempted_at` > :cutoff2");
        $stmt2->execute([':u2' => $username, ':cutoff2' => $cutoff]);
        return (int)$stmt2->fetchColumn() >= (3 * max(1, $maxAttempts));
    }

    /**
     * Record one failed login attempt for the throttle. Also prunes this
     * IP's rows older than 24h so the table stays bounded per attacker.
     */
    public static function recordLoginAttempt(string $ipHash, string $username): void
    {
        $db = Database::getConnection();
        $db->prepare("INSERT INTO `admin_login_throttle` (`ip_hash`, `username`, `attempted_at`) VALUES (:ip, :u, :t)")
           ->execute([':ip' => $ipHash, ':u' => $username, ':t' => time()]);
        $db->prepare("DELETE FROM `admin_login_throttle` WHERE `ip_hash` = :ip AND `attempted_at` < :cutoff")
           ->execute([':ip' => $ipHash, ':cutoff' => time() - 86400]);
    }

    /**
     * Clear all failed-attempt rows for an (ip_hash, username) pair.
     * Called after a successful login (see updateLastLogin).
     */
    public static function clearLoginThrottle(string $ipHash, string $username): void
    {
        $db = Database::getConnection();
        $db->prepare("DELETE FROM `admin_login_throttle` WHERE `ip_hash` = :ip AND `username` = :u")
           ->execute([':ip' => $ipHash, ':u' => $username]);
    }

    // ─── Support Ticket Counters ───────────────────────────────────

    /**
     * Count non-closed support tickets (status != 'closed' — the exact
     * semantics the notification bell used when it fetched
     * Database::getAllTickets(100) and filtered in PHP). One COUNT(*) query
     * replaces the 100-row fetch that used to run on every panel page.
     */
    public static function countOpenTickets(): int
    {
        $db = Database::getConnection();
        return (int)$db->query("SELECT COUNT(*) FROM `support_tickets` WHERE `status` != 'closed'")->fetchColumn();
    }

    /**
     * Recent non-closed tickets for the notification-bell dropdown
     * (bounded — the old code pulled up to 100 full rows per page view).
     */
    public static function getRecentOpenTickets(int $limit = 8): array
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT * FROM `support_tickets` WHERE `status` != 'closed' ORDER BY `question_time` DESC LIMIT :limit");
        $stmt->bindValue(':limit', max(1, $limit), PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }
}