<?php

namespace Bot;

class Database
{
    private \mysqli $conn;
    public $users; 
    public $channels;
    public $joinRequests;
    public $settings;
    
    // Store connection params for reconnection
    private string $host;
    private string $user;
    private string $pass;
    private string $name;

    public function __construct(string $host, string $user, string $pass, string $name)
    {
        $this->host = $host;
        $this->user = $user;
        $this->pass = $pass;
        $this->name = $name;
        
        $this->connect();
        
        // Ensure we have a valid connection
        $this->ensureConnection();
        
        // Ensure greeting_locks table exists
        $this->ensureGreetingLocksTable();
    }
    
    /**
     * Connect to database with retry logic
     */
    private function connect(int $maxRetries = 3): void
    {
        $lastError = null;
        
        for ($attempt = 1; $attempt <= $maxRetries; $attempt++) {
            try {
                $this->conn = @new \mysqli($this->host, $this->user, $this->pass, $this->name);
                
                if ($this->conn->connect_error) {
                    $lastError = $this->conn->connect_error;
                    if ($attempt < $maxRetries) {
                        usleep(100000 * $attempt); // Exponential backoff: 100ms, 200ms, 300ms
                        continue;
                    }
                } else {
                    // Connection successful
                    $this->conn->set_charset("utf8mb4");
                    
                    // Set connection options for robustness
                    $this->conn->options(MYSQLI_OPT_CONNECT_TIMEOUT, 10);
                    
                    return;
                }
            } catch (\Exception $e) {
                $lastError = $e->getMessage();
                if ($attempt < $maxRetries) {
                    usleep(100000 * $attempt);
                    continue;
                }
            }
        }
        
        // All retries failed
        error_log("Database connection failed after $maxRetries attempts: $lastError");
        throw new \Exception("Database connection failed: $lastError");
    }
    
    /**
     * Ensure connection is alive, reconnect if needed
     * Call this before critical operations in long-running scripts
     */
    public function ensureConnection(): void
    {
        if (!$this->conn || !@$this->conn->ping()) {
            error_log("Database connection lost, reconnecting...");
            $this->connect();
        }
    }
    
    /**
     * Execute a query with automatic reconnection on failure
     */
    private function executeWithRetry(\mysqli_stmt $stmt): bool
    {
        try {
            $result = $stmt->execute();
            if (!$result && $this->conn->errno == 2006) { // MySQL server has gone away
                $this->ensureConnection();
                return $stmt->execute();
            }
            return $result;
        } catch (\Exception $e) {
            if (strpos($e->getMessage(), 'gone away') !== false) {
                $this->ensureConnection();
                return $stmt->execute();
            }
            throw $e;
        }
    }

    // --- User Methods ---
    public function getUser($userId): ?array
    {
        $stmt = $this->conn->prepare("SELECT * FROM users WHERE user_id = ?");
        $stmt->bind_param("s", $userId);
        $stmt->execute();
        $result = $stmt->get_result();
        return $result->fetch_assoc();
    }

    public function saveUser($userId, string $firstName, ?string $username = null): void
    {
        $stmt = $this->conn->prepare("INSERT INTO users (user_id, first_name, username) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE first_name = VALUES(first_name), username = VALUES(username)");
        $stmt->bind_param("sss", $userId, $firstName, $username);
        $stmt->execute();
    }
    
    public function markUserStarted($userId): void
    {
        // Mark user as having started the bot (for broadcasts)
        $stmt = $this->conn->prepare("UPDATE users SET has_started = 1 WHERE user_id = ?");
        $stmt->bind_param("s", $userId);
        $stmt->execute();
    }
    
    public function hasUserStarted(int $userId): bool
    {
        $stmt = $this->conn->prepare("SELECT has_started FROM users WHERE user_id = ?");
        $stmt->bind_param("i", $userId);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        return $result && $result['has_started'] == 1;
    }
    
    public function getPendingJoinRequestsForUser(int $userId): array
    {
        $stmt = $this->conn->prepare("SELECT jr.*, c.title as channel_title FROM join_requests jr LEFT JOIN channels c ON jr.chat_id = c.chat_id WHERE jr.user_id = ? AND jr.status = 'pending_start'");
        $stmt->bind_param("s", $userId);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    
    public function setJoinRequestPendingStart($chatId, $userId): void
    {
        $stmt = $this->conn->prepare("UPDATE join_requests SET status = 'pending_start' WHERE chat_id = ? AND user_id = ? AND status = 'pending' ORDER BY id DESC LIMIT 1");
        $stmt->bind_param("ss", $chatId, $userId);
        $stmt->execute();
    }

    // --- Channel Methods ---
    public function addChannel(int $chatId, int $ownerId, string $title, string $chatType = 'channel'): void
    {
        // Insert Channel
        $stmt = $this->conn->prepare("INSERT INTO channels (chat_id, owner_id, title) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE title = VALUES(title), owner_id = VALUES(owner_id)");
        $stmt->bind_param("sis", $chatId, $ownerId, $title);
        $stmt->execute();

        // Initialize Default Settings if new
        $defaults = [
            'app_accept_enabled' => '1',
            'auto_approve' => '1',
            'defer_time' => '0',
            'use_captcha' => '0',
            'accept_interact' => '0',
            'chat_type' => $chatType  // Store chat type (channel, group, supergroup)
        ];
        
        foreach ($defaults as $key => $val) {
             // We use INSERT IGNORE to only set defaults if not exists
             $stmt = $this->conn->prepare("INSERT IGNORE INTO channel_settings (chat_id, setting_key, setting_value) VALUES (?, ?, ?)");
             $stmt->bind_param("iss", $chatId, $key, $val);
             $stmt->execute();
        }
    }

    public function getUserChannels(int $ownerId): array
    {
        $stmt = $this->conn->prepare("SELECT * FROM channels WHERE owner_id = ?");
        $stmt->bind_param("s", $ownerId);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }

    public function getChannel(int $chatId): ?array {
        $stmt = $this->conn->prepare("SELECT * FROM channels WHERE chat_id = ?");
        $stmt->bind_param("s", $chatId);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }

    public function removeChannel(int $chatId): bool {
        // Delete related data first (using prepared statements)
        $stmt = $this->conn->prepare("DELETE FROM channel_settings WHERE chat_id = ?");
        $stmt->bind_param("s", $chatId);
        $stmt->execute();
        
        $stmt = $this->conn->prepare("DELETE FROM channel_messages WHERE chat_id = ?");
        $stmt->bind_param("s", $chatId);
        $stmt->execute();
        
        $stmt = $this->conn->prepare("DELETE FROM join_requests WHERE chat_id = ?");
        $stmt->bind_param("s", $chatId);
        $stmt->execute();
        
        $stmt = $this->conn->prepare("DELETE FROM scheduled_actions WHERE chat_id = ?");
        $stmt->bind_param("s", $chatId);
        $stmt->execute();
        
        // Delete the channel
        $stmt = $this->conn->prepare("DELETE FROM channels WHERE chat_id = ?");
        $stmt->bind_param("s", $chatId);
        return $stmt->execute();
    }

    public function getPendingJoinRequests(int $chatId): array {
        $stmt = $this->conn->prepare("SELECT * FROM join_requests WHERE chat_id = ? AND status = 'pending' ORDER BY id DESC");
        $stmt->bind_param("s", $chatId);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }

    // --- Settings Methods ---
    public function getChannelSetting(int $chatId, string $key, $default = null)
    {
        $stmt = $this->conn->prepare("SELECT setting_value FROM channel_settings WHERE chat_id = ? AND setting_key = ?");
        $stmt->bind_param("ss", $chatId, $key);
        $stmt->execute();
        $res = $stmt->get_result();
        if ($row = $res->fetch_assoc()) {
            // value is stored as string/text, try to cast basic types
            // For simple boolean/int flags, this is enough. 
            // For JSON or arrays, we'd need more logic.
            // Based on usage: '1'/'0' for bools, digits for ints.
            return $row['setting_value'];
        }
        return $default;
    }

    public function setChannelSetting(int $chatId, string $key, $value): void
    {
        // Normalize boolean to 1/0
        if (is_bool($value)) $value = $value ? '1' : '0';
        
        $stmt = $this->conn->prepare("INSERT INTO channel_settings (chat_id, setting_key, setting_value) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)");
        $stmt->bind_param("iss", $chatId, $key, $value);
        $stmt->execute();
    }
    
    // --- Join Requests ---
    public function logJoinRequest($chatId, $userId) {
        // Use upsert to prevent duplicate pending entries for same user/chat combination
        $stmt = $this->conn->prepare(
            "INSERT INTO join_requests (chat_id, user_id, status) VALUES (?, ?, 'pending') 
             ON DUPLICATE KEY UPDATE status = 'pending', created_at = CURRENT_TIMESTAMP"
        );
        $stmt->bind_param("ss", $chatId, $userId);
        $stmt->execute();
    }
    
    public function deferJoinRequest($chatId, $userId, $processAfter) {
         $stmt = $this->conn->prepare("UPDATE join_requests SET status = 'deferred', process_after = ? WHERE chat_id = ? AND user_id = ? AND status = 'pending' ORDER BY id DESC LIMIT 1");
         $stmt->bind_param("iss", $processAfter, $chatId, $userId);
         $stmt->execute();
    }
    
    public function approveJoinRequest($chatId, $userId) {
         // Delete any existing approved entry for this chat/user to avoid unique constraint violation
         $stmt = $this->conn->prepare("DELETE FROM join_requests WHERE chat_id = ? AND user_id = ? AND status = 'approved'");
         $stmt->bind_param("ss", $chatId, $userId);
         $stmt->execute();
         
         // Now update the most recent pending/deferred request to approved
         $stmt = $this->conn->prepare("UPDATE join_requests SET status = 'approved', approved_at = NOW() WHERE chat_id = ? AND user_id = ? AND status IN ('pending', 'deferred', 'pending_start') ORDER BY id DESC LIMIT 1");
         $stmt->bind_param("ss", $chatId, $userId);
         $stmt->execute();
    }

    public function declineJoinRequest($chatId, $userId) {
         // Delete any existing declined entry for this chat/user to avoid unique constraint violation
         $stmt = $this->conn->prepare("DELETE FROM join_requests WHERE chat_id = ? AND user_id = ? AND status = 'declined'");
         $stmt->bind_param("ss", $chatId, $userId);
         $stmt->execute();
         
         // Update the most recent pending request to declined
         $stmt = $this->conn->prepare("UPDATE join_requests SET status = 'declined' WHERE chat_id = ? AND user_id = ? AND status IN ('pending', 'pending_start') ORDER BY id DESC LIMIT 1");
         $stmt->bind_param("ss", $chatId, $userId);
         $stmt->execute();
    }
    
    // --- Broadcast/Cron ---
    public function queueBroadcast($targetType, $targetId, $text) {
         $stmt = $this->conn->prepare("INSERT INTO broadcast_queue (target_type, target_id, message_text, status) VALUES (?, ?, ?, 'pending')");
         $stmt->bind_param("sis", $targetType, $targetId, $text);
         $stmt->execute();
    }
    
    public function getPendingBroadcasts() {
        return $this->conn->query("SELECT * FROM broadcast_queue WHERE status = 'pending'")->fetch_all(MYSQLI_ASSOC);
    }
    
    public function completeBroadcast($id) {
        $stmt = $this->conn->prepare("UPDATE broadcast_queue SET status = 'completed', completed_at = NOW(), locked_at = NULL WHERE id = ?");
        $stmt->bind_param("i", $id);
        $stmt->execute();
    }
    
    public function getAllUsers() {
        // Use generator or unbuffered query for large datasets in prod, but fetch_all ok for basic usage
        return $this->conn->query("SELECT user_id FROM users");
    }
    
    public function getAllChannels() {
        return $this->conn->query("SELECT chat_id FROM channels"); 
    }
    
    public function getDeferredRequests($timestamp) {
        $stmt = $this->conn->prepare("SELECT * FROM join_requests WHERE status = 'deferred' AND process_after <= ?");
        $stmt->bind_param("i", $timestamp);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }

    // --- User State (for Inputs) ---
    public function setUserState($userId, $state, $data = null) {
        $dataStr = $data ? json_encode($data) : null;
        $stmt = $this->conn->prepare("INSERT INTO user_states (user_id, state, data) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE state = VALUES(state), data = VALUES(data)");
        $stmt->bind_param("sss", $userId, $state, $dataStr);
        $stmt->execute();
    }

    public function getUserState($userId) {
        $stmt = $this->conn->prepare("SELECT * FROM user_states WHERE user_id = ?");
        $stmt->bind_param("s", $userId);
        $stmt->execute();
        $res = $stmt->get_result()->fetch_assoc();
        if ($res && $res['data']) {
            $res['data'] = json_decode($res['data'], true);
        }
        return $res;
    }

    public function clearUserState($userId) {
        $stmt = $this->conn->prepare("DELETE FROM user_states WHERE user_id = ?");
        $stmt->bind_param("s", $userId);
        $stmt->execute();
    }
    
    public function clearAllUserStates() {
        $this->conn->query("TRUNCATE TABLE user_states");
    }

    // --- Advanced Greetings/Farewells ---
    public function addChannelMessage($chatId, $type, $data) {
        $stmt = $this->conn->prepare("INSERT INTO channel_messages (chat_id, type, language, content_type, file_id, text_content, buttons, defer_seconds, delete_seconds) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
        $buttons = json_encode($data['buttons'] ?? []);
        $defer = $data['defer_seconds'] ?? 0;
        $delete = $data['delete_seconds'] ?? 0;
        $lang = $data['language'] ?? 'all';
        $cType = $data['content_type'] ?? 'text';
        $fId = $data['file_id'] ?? null;
        $txt = $data['text_content'] ?? null;
        
        $stmt->bind_param("sssssssii", $chatId, $type, $lang, $cType, $fId, $txt, $buttons, $defer, $delete);
        $stmt->execute();
        return $stmt->insert_id;
    }

    public function getChannelMessages($chatId, $type) {
        $stmt = $this->conn->prepare("SELECT * FROM channel_messages WHERE chat_id = ? AND type = ?");
        $stmt->bind_param("ss", $chatId, $type);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    
    public function getChannelMessage($id) {
        $stmt = $this->conn->prepare("SELECT * FROM channel_messages WHERE id = ?");
        $stmt->bind_param("i", $id);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }
    
    public function updateChannelMessage($id, $data) {
        $updates = [];
        $types = "";
        $values = [];
        
        if (isset($data['language'])) { $updates[] = "language=?"; $types .= "s"; $values[] = $data['language']; }
        if (isset($data['content_type'])) { $updates[] = "content_type=?"; $types .= "s"; $values[] = $data['content_type']; }
        if (isset($data['file_id'])) { $updates[] = "file_id=?"; $types .= "s"; $values[] = $data['file_id']; }
        if (isset($data['text_content'])) { $updates[] = "text_content=?"; $types .= "s"; $values[] = $data['text_content']; }
        
        // Revised buttons handling: Allow clearing if key exists
        if (array_key_exists('buttons', $data)) {
             $updates[] = "buttons=?";
             $types .= "s";
             // If empty array, save as empty string or "[]". 
             // Logic in Bot.php handles "[]" or empty string check.
             // Lets save as "[]" for consistency with json_encode.
             $values[] = json_encode($data['buttons']);
        }
        
        if (isset($data['defer_seconds'])) { $updates[] = "defer_seconds=?"; $types .= "i"; $values[] = $data['defer_seconds']; }
        if (isset($data['delete_seconds'])) { $updates[] = "delete_seconds=?"; $types .= "i"; $values[] = $data['delete_seconds']; }
        
        if (empty($updates)) return false;
        
        $sql = "UPDATE channel_messages SET " . implode(", ", $updates) . " WHERE id=?";
        $types .= "i";
        $values[] = $id;
        
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param($types, ...$values);
        $res = $stmt->execute();
        
        if (!$res) {
            error_log("Database Update Error: " . $stmt->error);
        }
        return $res;
    }

    public function deleteChannelMessage($id) {
        $stmt = $this->conn->prepare("DELETE FROM channel_messages WHERE id = ?");
        $stmt->bind_param("i", $id);
        return $stmt->execute();
    }

    // --- Scheduled Actions ---
    public function scheduleAction($chatId, $templateId, $type, $processAt, $payload) {
        $stmt = $this->conn->prepare("INSERT INTO scheduled_actions (chat_id, message_template_id, action_type, process_at, payload) VALUES (?, ?, ?, ?, ?)");
        $pl = json_encode($payload);
        $stmt->bind_param("iisss", $chatId, $templateId, $type, $processAt, $pl);
        return $stmt->execute();
    }
    
    public function scheduleDeleteAction($chatId, $messageId, $processAt) {
         $stmt = $this->conn->prepare("INSERT INTO scheduled_actions (chat_id, action_type, process_at, sent_message_id) VALUES (?, 'delete', ?, ?)");
         $stmt->bind_param("sii", $chatId, $processAt, $messageId);
         return $stmt->execute();
    }

    public function getPendingActions($limit = 20) {
        $now = time();
        $stmt = $this->conn->prepare("SELECT * FROM scheduled_actions WHERE process_at <= ? ORDER BY process_at ASC LIMIT ?");
        $stmt->bind_param("ii", $now, $limit);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    
    public function completeAction($id) {
        $stmt = $this->conn->prepare("DELETE FROM scheduled_actions WHERE id = ?");
        $stmt->bind_param("i", $id);
        return $stmt->execute();
    }

    /**
     * Ensure the greeting_locks table exists (auto-create if missing)
     */
    private function ensureGreetingLocksTable(): void {
        $this->conn->query("
            CREATE TABLE IF NOT EXISTS greeting_locks (
                chat_id BIGINT NOT NULL,
                user_id BIGINT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (chat_id, user_id)
            )
        ");
    }

    /**
     * Check if a greeting lock exists (read-only, no side effects).
     * Returns true if already locked (greeting was already sent).
     */
    public function hasGreetingLock($chatId, $userId): bool {
        $stmt = $this->conn->prepare("SELECT 1 FROM greeting_locks WHERE chat_id = ? AND user_id = ?");
        $stmt->bind_param("ss", $chatId, $userId);
        $stmt->execute();
        return $stmt->get_result()->num_rows > 0;
    }

    /**
     * Set the greeting lock (call AFTER greeting is successfully sent).
     */
    /**
     * Set the greeting lock.
     * Returns true if lock was ACQUIRED (row inserted).
     * Returns false if lock ALREADY EXISTED.
     */
    public function setGreetingLock($chatId, $userId): bool {
        $stmt = $this->conn->prepare("INSERT IGNORE INTO greeting_locks (chat_id, user_id) VALUES (?, ?)");
        $stmt->bind_param("ss", $chatId, $userId);
        $stmt->execute();
        return $stmt->affected_rows > 0;
    }

    /**
     * Delete the greeting lock (call when user leaves/kicked so they can be greeted again if they rejoin).
     */
    public function deleteGreetingLock($chatId, $userId): void {
        $stmt = $this->conn->prepare("DELETE FROM greeting_locks WHERE chat_id = ? AND user_id = ?");
        $stmt->bind_param("ss", $chatId, $userId);
        $stmt->execute();
    }

    /**
     * Remove expired greeting locks (older than 60 seconds)
     * Called from cron.php
     */
    public function cleanupGreetingLocks(): void {
        $this->conn->query("DELETE FROM greeting_locks WHERE created_at < NOW() - INTERVAL 60 SECOND");
    }

    // ============================
    // ADMIN PANEL ENHANCED METHODS
    // ============================
    
    // --- Statistics ---
    public function getUserCount(): int {
        $result = $this->conn->query("SELECT COUNT(*) as cnt FROM users");
        return (int)$result->fetch_assoc()['cnt'];
    }
    
    public function getChannelCount(): int {
        $result = $this->conn->query("SELECT COUNT(*) as cnt FROM channels");
        return (int)$result->fetch_assoc()['cnt'];
    }
    
    public function getStats(): array {
        $stats = [];
        $stats['users'] = $this->getUserCount();
        $stats['channels'] = $this->getChannelCount();
        
        // Join request stats
        $result = $this->conn->query("SELECT status, COUNT(*) as cnt FROM join_requests GROUP BY status");
        $stats['requests'] = ['pending' => 0, 'approved' => 0, 'declined' => 0, 'deferred' => 0];
        while ($row = $result->fetch_assoc()) {
            $stats['requests'][$row['status']] = (int)$row['cnt'];
        }
        
        // Broadcast stats
        $result = $this->conn->query("SELECT COUNT(*) as cnt FROM broadcast_queue");
        $stats['broadcasts'] = (int)$result->fetch_assoc()['cnt'];
        
        // Banned users count
        $result = $this->conn->query("SELECT COUNT(*) as cnt FROM users WHERE is_banned = 1");
        $stats['banned'] = (int)$result->fetch_assoc()['cnt'];
        
        return $stats;
    }
    
    // --- Ban/Unban ---
    public function banUser(int $userId, int $bannedBy, ?string $reason = null): bool {
        // Update users table
        $stmt = $this->conn->prepare("UPDATE users SET is_banned = 1 WHERE user_id = ?");
        $stmt->bind_param("i", $userId);
        $stmt->execute();
        
        // Log to banned_users table
        $stmt = $this->conn->prepare("INSERT INTO banned_users (user_id, banned_by, reason) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE banned_by = VALUES(banned_by), reason = VALUES(reason), banned_at = NOW()");
        $stmt->bind_param("iis", $userId, $bannedBy, $reason);
        return $stmt->execute();
    }
    
    public function unbanUser(int $userId): bool {
        // Update users table
        $stmt = $this->conn->prepare("UPDATE users SET is_banned = 0 WHERE user_id = ?");
        $stmt->bind_param("i", $userId);
        $stmt->execute();
        
        // Remove from banned_users
        $stmt = $this->conn->prepare("DELETE FROM banned_users WHERE user_id = ?");
        $stmt->bind_param("i", $userId);
        return $stmt->execute();
    }
    
    public function isUserBanned(int $userId): bool {
        $stmt = $this->conn->prepare("SELECT is_banned FROM users WHERE user_id = ?");
        $stmt->bind_param("i", $userId);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        return $result && $result['is_banned'] == 1;
    }
    
    public function getBannedUsers(int $limit = 20, int $offset = 0): array {
        $stmt = $this->conn->prepare("SELECT b.*, u.first_name, u.username FROM banned_users b LEFT JOIN users u ON b.user_id = u.user_id ORDER BY b.banned_at DESC LIMIT ? OFFSET ?");
        $stmt->bind_param("ii", $limit, $offset);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    
    public function searchUser(string $query): ?array {
        // Try numeric ID first
        if (is_numeric($query)) {
            $stmt = $this->conn->prepare("SELECT * FROM users WHERE user_id = ?");
            $id = (int)$query;
            $stmt->bind_param("i", $id);
            $stmt->execute();
            $result = $stmt->get_result()->fetch_assoc();
            if ($result) return $result;
        }
        
        // Try username (strip @ if present)
        $username = ltrim($query, '@');
        $stmt = $this->conn->prepare("SELECT * FROM users WHERE username LIKE ?");
        $like = "%$username%";
        $stmt->bind_param("s", $like);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }
    
    // --- Global Settings ---
    public function getGlobalSetting(string $key, $default = null) {
        $stmt = $this->conn->prepare("SELECT setting_value FROM global_settings WHERE setting_key = ?");
        $stmt->bind_param("s", $key);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        return $result ? $result['setting_value'] : $default;
    }
    
    public function setGlobalSetting(string $key, $value): bool {
        if (is_bool($value)) $value = $value ? '1' : '0';
        $stmt = $this->conn->prepare("INSERT INTO global_settings (setting_key, setting_value) VALUES (?, ?) ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)");
        $stmt->bind_param("ss", $key, $value);
        return $stmt->execute();
    }
    
    // --- Enhanced Broadcast ---
    public function queueBroadcastEnhanced(string $targetType, ?int $targetId, ?string $text, string $contentType = 'text', ?string $fileId = null, ?array $buttons = null): int {
        // Count targets
        $totalTargets = 0;
        if ($targetType === 'all_users') {
            $totalTargets = $this->getUserCount();
        } elseif ($targetType === 'all_channels') {
            $totalTargets = $this->getChannelCount();
        } else {
            $totalTargets = 1;
        }
        
        $buttonsJson = $buttons ? json_encode($buttons) : null;
        $dbText = $text ?? '';
        
        $stmt = $this->conn->prepare("INSERT INTO broadcast_queue (target_type, target_id, message_text, content_type, file_id, total_targets, sent_count, failed_count, buttons, status) VALUES (?, ?, ?, ?, ?, ?, 0, 0, ?, 'pending')");
        $stmt->bind_param("sisssss", $targetType, $targetId, $dbText, $contentType, $fileId, $totalTargets, $buttonsJson);
        $stmt->execute();
        return $stmt->insert_id;
    }
    
    public function updateBroadcastProgress(int $id, int $sent, int $failed, int $lastProcessedId = 0): bool {
        $stmt = $this->conn->prepare("UPDATE broadcast_queue SET sent_count = ?, failed_count = ?, last_processed_id = ?, locked_at = CURRENT_TIMESTAMP, status = 'processing' WHERE id = ?");
        $stmt->bind_param("iiii", $sent, $failed, $lastProcessedId, $id);
        return $stmt->execute();
    }

    public function acquireBroadcastLock(int $id): bool {
        // Lock for 60 seconds. Success if status is pending OR (processing AND lock expired)
        $stmt = $this->conn->prepare("
            UPDATE broadcast_queue 
            SET locked_at = CURRENT_TIMESTAMP, status = 'processing' 
            WHERE id = ? AND (
                status = 'pending' OR 
                (status = 'processing' AND locked_at < NOW() - INTERVAL 60 SECOND)
            )
        ");
        $stmt->bind_param("i", $id);
        $stmt->execute();
        return $stmt->affected_rows > 0;
    }

    public function releaseBroadcastLock(int $id): bool {
        $stmt = $this->conn->prepare("UPDATE broadcast_queue SET locked_at = NULL WHERE id = ?");
        $stmt->bind_param("i", $id);
        return $stmt->execute();
    }
    
    public function getBroadcastById(int $id): ?array {
        $stmt = $this->conn->prepare("SELECT * FROM broadcast_queue WHERE id = ?");
        $stmt->bind_param("i", $id);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }
    
    public function getActiveBroadcast(): ?array {
        // Find one that is pending OR processing (even if locked, we just want info)
        // But for processing worker, we want one that is NOT locked or expired lock
        $result = $this->conn->query("SELECT * FROM broadcast_queue WHERE status IN ('pending', 'processing') ORDER BY id DESC LIMIT 1");
        return $result->fetch_assoc();
    }

    // Corrected Implementation of Batch Fetch
    public function getBroadcastBatch(string $targetType, int $lastId, int $limit): array {
         if ($targetType === 'all_users') {
             // Fetch id (cursor), user_id (target), first_name, username (personalization)
             $stmt = $this->conn->prepare("SELECT id, user_id, first_name, username FROM users WHERE is_banned = 0 AND id > ? ORDER BY id ASC LIMIT ?");
             $stmt->bind_param("ii", $lastId, $limit);
             $stmt->execute();
             return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
         } elseif ($targetType === 'all_channels') {
             $stmt = $this->conn->prepare("SELECT id, chat_id, title FROM channels WHERE id > ? ORDER BY id ASC LIMIT ?");
             $stmt->bind_param("ii", $lastId, $limit);
             $stmt->execute();
             return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
         }
         return [];
    }
    
    public function getAllUsersUnbuffered() {
        // For large-scale broadcast - returns mysqli_result for iteration
        return $this->conn->query("SELECT user_id FROM users WHERE is_banned = 0", MYSQLI_USE_RESULT);
    }
    
    public function getAllChannelsUnbuffered() {
        return $this->conn->query("SELECT chat_id FROM channels", MYSQLI_USE_RESULT);
    }
    
    /**
     * Get all users with full data for personalized broadcasts (buffered)
     */
    public function getAllUsersWithData(): array {
        $result = $this->conn->query("SELECT user_id, first_name, username FROM users WHERE is_banned = 0");
        return $result->fetch_all(MYSQLI_ASSOC);
    }
    
    /**
     * Get all channels as buffered array
     */
    public function getAllChannelsBuffered(): array {
        $result = $this->conn->query("SELECT chat_id, title FROM channels");
        return $result->fetch_all(MYSQLI_ASSOC);
    }
    
    /**
     * Get channels with details for admin panel (paginated)
     */
    public function getAllChannelsWithDetails(int $limit = 100, int $offset = 0): array {
        $stmt = $this->conn->prepare(
            "SELECT chat_id, owner_id, title, created_at FROM channels ORDER BY created_at DESC LIMIT ? OFFSET ?"
        );
        $stmt->bind_param("ii", $limit, $offset);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    
    /**
     * Get total channel count
     */
    public function getTotalChannelCount(): int {
        $result = $this->conn->query("SELECT COUNT(*) as cnt FROM channels");
        return (int)$result->fetch_assoc()['cnt'];
    }
    // === LOCKING MECHANISM ===

    public function acquireLock($limitName, $timeout = 0) {
        $stmt = $this->conn->prepare("SELECT GET_LOCK(?, ?)");
        $stmt->bind_param("si", $limitName, $timeout);
        $stmt->execute();
        $res = $stmt->get_result();
        $row = $res->fetch_row();
        return $row && $row[0] == 1;
    }

    public function releaseLock($limitName) {
        $stmt = $this->conn->prepare("SELECT RELEASE_LOCK(?)");
        $stmt->bind_param("s", $limitName);
        $stmt->execute();
    }


}
