<?php
namespace EasyChat\Model;

use EasyChat\Library\AppClock;
use EasyChat\Library\Logger;
use EasyChat\Library\ResponseTimeMetrics;
use EasyChat\Library\Messenger\Marketplace\MarketplaceText;
use EasyChat\Library\Messenger\MessageReadReceiptService;

class Session extends Base {

    public function getSessionBySid($session_id) {
        $query = $this->db->query("SELECT * FROM `{$this->prefix}sessions` WHERE `session_id` = '" . $this->db->escape($session_id) . "' LIMIT 1");
        return $query->row;
    }

    public function getSessionById($chat_id) {
        $query = $this->db->query(
            "SELECT s.*,
                    mt.`conversation_kind` AS `marketplace_conversation_kind`,
                    mt.`state` AS `marketplace_state`,
                    mt.`capabilities_json` AS `marketplace_capabilities_json`,
                    mt.`external_thread_id` AS `marketplace_external_thread_id`
             FROM `{$this->prefix}sessions` s
             LEFT JOIN `{$this->prefix}marketplace_threads` mt ON mt.`chat_id` = s.`chat_id`
             WHERE s.`chat_id` = " . (int)$chat_id . " LIMIT 1"
        );
        return $query->row;
    }

    public function createSession($session_id, $customer_name = '', array $meta = []) {
        $source_provider = isset($meta['source_provider']) ? preg_replace('/[^a-z0-9_\-]/i', '', (string)$meta['source_provider']) : 'widget';
        $source_provider = $source_provider !== '' ? strtolower($source_provider) : 'widget';
        $source_channel_id = isset($meta['source_channel_id']) ? (int)$meta['source_channel_id'] : 0;
        $conversation_kind = isset($meta['conversation_kind']) ? $this->normalizeConversationKind($meta['conversation_kind']) : 'chat';
        $external_thread_id = isset($meta['external_thread_id'])
            ? MarketplaceText::truncate(trim((string)$meta['external_thread_id']), 191)
            : '';
        $external_contact_id = isset($meta['external_contact_id']) ? (int)$meta['external_contact_id'] : 0;
        $read_receipt_capability = (string)($meta['read_receipt_capability']
            ?? MessageReadReceiptService::capability($source_provider, $conversation_kind));
        if (!in_array($read_receipt_capability, [
            MessageReadReceiptService::CAPABILITY_UNSUPPORTED,
            MessageReadReceiptService::CAPABILITY_WIDGET_VISIBLE,
            MessageReadReceiptService::CAPABILITY_MAX_DIALOG,
            MessageReadReceiptService::CAPABILITY_WHATSAPP_DIALOG,
        ], true)) {
            $read_receipt_capability = MessageReadReceiptService::CAPABILITY_UNSUPPORTED;
        }
        $now = $this->db->escape((new AppClock($this->registry))->nowSql());

        $this->db->query("INSERT INTO `{$this->prefix}sessions` SET 
            `session_id` = '" . $this->db->escape($session_id) . "', 
            `customer_name` = '" . $this->db->escape($customer_name) . "', 
            `source_provider` = '" . $this->db->escape($source_provider) . "',
            `source_channel_id` = " . ($source_channel_id > 0 ? (int)$source_channel_id : 'NULL') . ",
            `conversation_kind` = '" . $this->db->escape($conversation_kind) . "',
            `external_thread_id` = '" . $this->db->escape($external_thread_id) . "',
            `external_contact_id` = " . ($external_contact_id > 0 ? (int)$external_contact_id : 'NULL') . ",
            `read_receipt_capability` = '" . $this->db->escape($read_receipt_capability) . "',
            `date_added` = '{$now}',
            `date_modified` = '{$now}'");
        return $this->db->getLastId();
    }

    public function updateSession($chat_id, $data, $touch_modified = true) {
        $update_parts = [];
        foreach ($data as $key => $val) {
            $update_parts[] = "`" . $this->db->escape($key) . "` = '" . $this->db->escape($val) . "'";
        }
        if ($update_parts) {
            $touch_sql = $touch_modified
                ? ", `date_modified` = '" . $this->db->escape((new AppClock($this->registry))->nowSql()) . "'"
                : '';
            $this->db->query("UPDATE `{$this->prefix}sessions` SET " . implode(', ', $update_parts) . $touch_sql . " WHERE `chat_id` = " . (int)$chat_id);
        }
    }

    public function updateModifiedDate($chat_id) {
        $this->db->query("UPDATE `{$this->prefix}sessions` SET `date_modified` = '" . $this->db->escape((new AppClock($this->registry))->nowSql()) . "' WHERE `chat_id` = " . (int)$chat_id);
    }

    



    public function updateModifiedDateFromMessages($chat_id) {
        $chat_id = (int)$chat_id;
        if ($chat_id <= 0) return;
        $this->db->query(
            "UPDATE `{$this->prefix}sessions`
             SET `date_modified` = COALESCE(
                    (SELECT MAX(m.`date_added`) FROM `{$this->prefix}messages` m WHERE m.`chat_id` = {$chat_id}),
                    `date_modified`
                 )
             WHERE `chat_id` = {$chat_id}"
        );
    }

    public function beginConversation($chat_id, $boundary_message_id) {
        $now = $this->db->escape((new AppClock($this->registry))->nowSql());
        $this->db->query(
            "UPDATE `{$this->prefix}sessions`
             SET `conversation_start_message_id` = " . max(0, (int)$boundary_message_id) . ",
                 `conversation_started_at` = '{$now}',
                 `is_archived` = 0,
                 `ai_processing_message_id` = 0,
                 `ai_processing_until` = 0,
                 `ai_typing_expire` = 0,
                 `ai_last_attempt_message_id` = 0,
                 `ai_last_attempt_status` = '',
                 `ai_last_error` = '',
                 `date_modified` = '{$now}'
             WHERE `chat_id` = " . (int)$chat_id
        );
    }

    public function acquireAiProcessingLock($chat_id, $message_id, $lease_seconds = 90) {
        $chat_id = (int)$chat_id;
        $message_id = (int)$message_id;
        $lease_seconds = max(30, min(180, (int)$lease_seconds));
        if ($chat_id <= 0 || $message_id <= 0) {
            return false;
        }

         
         
        $this->db->query(
            "UPDATE `{$this->prefix}sessions`
             SET `ai_processing_message_id` = " . $message_id . ",
                 `ai_processing_until` = UNIX_TIMESTAMP(NOW()) + " . $lease_seconds . ",
                 `ai_typing_expire` = UNIX_TIMESTAMP(NOW()) + " . min(30, $lease_seconds) . "
             WHERE `chat_id` = " . $chat_id . "
               AND (`ai_processing_until` <= UNIX_TIMESTAMP(NOW()) OR `ai_processing_message_id` = 0)
               AND NOT (
                   `ai_last_attempt_message_id` = " . $message_id . "
                   AND `ai_last_attempt_status` IN ('success', 'empty', 'error')
               )"
        );

        return (int)$this->db->countAffected() > 0;
    }

    public function releaseAiProcessingLock($chat_id, $message_id, $status, $error = '') {
        $chat_id = (int)$chat_id;
        $message_id = (int)$message_id;
        $status = preg_replace('/[^a-z_]/i', '', (string)$status);
        $status = $status !== '' ? strtolower($status) : 'error';
        $error = mb_substr(trim((string)$error), 0, 255);

        $this->db->query(
            "UPDATE `{$this->prefix}sessions`
             SET `ai_processing_message_id` = 0,
                 `ai_processing_until` = 0,
                 `ai_typing_expire` = 0,
                 `ai_last_attempt_message_id` = " . $message_id . ",
                 `ai_last_attempt_status` = '" . $this->db->escape($status) . "',
                 `ai_last_error` = '" . $this->db->escape($error) . "',
                 `ai_last_attempt_at` = '" . $this->db->escape((new AppClock($this->registry))->nowSql()) . "'
             WHERE `chat_id` = " . $chat_id . "
               AND `ai_processing_message_id` = " . $message_id
        );
    }

    public function hasCompletedAiAttempt($chat_id, $message_id) {
        $query = $this->db->query(
            "SELECT `ai_last_attempt_status`
             FROM `{$this->prefix}sessions`
             WHERE `chat_id` = " . (int)$chat_id . "
               AND `ai_last_attempt_message_id` = " . (int)$message_id . "
               AND `ai_last_attempt_status` IN ('success', 'empty', 'error')
             LIMIT 1"
        );

        return $query && $query->num_rows > 0;
    }

    public function getChats($start = 0, $limit = 20, $archived = false, $updated_after = '', $source_provider = '', $scope = '', $manager_id = 0, $operator_id = 0, $search_query = '') {
        $where = $this->buildChatScopeWhere($scope, $manager_id, $operator_id, 's', $archived);
        $updated_after = trim((string)$updated_after);
        if ($updated_after !== '') {
            $where .= " AND s.date_modified > '" . $this->db->escape($updated_after) . "'";
        }
        $source_provider = $this->normalizeSourceFilter($source_provider);
        if ($source_provider !== '') {
            $where .= " AND s.source_provider = '" . $this->db->escape($source_provider) . "'";
        }
        $where .= $this->buildChatSearchWhere($search_query, 's');
        $order_by = $updated_after !== ''
            ? "s.date_modified ASC, s.chat_id ASC"
            : "unread_count DESC, lm.date_added DESC, s.date_modified DESC";

        $query = $this->db->query(
            "SELECT s.*,
                mc.avatar_file,
                mc.avatar_mime,
                mc.avatar_source_hash,
                mc.avatar_updated_at,
                mc.external_user_id AS contact_external_user_id,
                mc.external_chat_id AS contact_external_chat_id,
                mc.metadata_json AS contact_metadata_json,
                mt.conversation_kind AS marketplace_conversation_kind,
                mt.state AS marketplace_state,
                mt.capabilities_json AS marketplace_capabilities_json,
                mt.external_thread_id AS marketplace_external_thread_id,
                COALESCE(mu.unread_count, 0) AS unread_count,
                lm.text AS last_message_enc,
                lm.image AS last_image,
                lm.author AS last_author,
                lm.manager_name AS last_manager_name,
                lm.manager_id AS last_manager_id,
                lm.date_added AS last_date
             FROM `{$this->prefix}sessions` s
             LEFT JOIN `{$this->prefix}messenger_contacts` mc ON mc.contact_id = s.external_contact_id
             LEFT JOIN `{$this->prefix}marketplace_threads` mt ON mt.chat_id = s.chat_id
             LEFT JOIN (
                SELECT m_unread.chat_id, COUNT(*) AS unread_count
                FROM `{$this->prefix}messages` m_unread
                WHERE m_unread.status_admin = 0
                  AND (m_unread.author = 1 OR m_unread.message_kind = 'conversation_start_notice')
                GROUP BY m_unread.chat_id
             ) mu ON mu.chat_id = s.chat_id
             LEFT JOIN `{$this->prefix}messages` lm ON lm.message_id = (
                SELECT m_last.message_id
                FROM `{$this->prefix}messages` m_last FORCE INDEX (`idx_chat_last_message_lookup`)
                WHERE m_last.chat_id = s.chat_id
                  AND m_last.message_kind != 'messenger_system_event'
                ORDER BY m_last.date_added DESC, m_last.message_id DESC
                LIMIT 1
             )
             WHERE " . $where . "
             ORDER BY " . $order_by . "
             LIMIT " . (int)$start . ", " . (int)$limit
        );
        return $query->rows;
    }

    




    public function wasRecentlyAnsweredByManager($chat_id, $manager_id, $max_age_seconds = 180): bool {
        $chat_id = max(0, (int)$chat_id);
        $manager_id = max(0, (int)$manager_id);
        $max_age_seconds = max(30, min(600, (int)$max_age_seconds));
        if ($chat_id <= 0 || $manager_id <= 0) {
            return false;
        }

        $cutoff = (new AppClock($this->registry))->timestampToSql(time() - $max_age_seconds);
        if ($cutoff === '') {
            return false;
        }

        $query = $this->db->query(
            "SELECT m.`author`, m.`manager_id`, m.`date_added`
             FROM `{$this->prefix}messages` m FORCE INDEX (`idx_chat_last_message_lookup`)
             WHERE m.`chat_id` = " . $chat_id . "
               AND m.`message_kind` != 'messenger_system_event'
             ORDER BY m.`date_added` DESC, m.`message_id` DESC
             LIMIT 1"
        );
        $row = is_array($query->row ?? null) ? $query->row : [];

        return (int)($row['author'] ?? 0) === 2
            && (int)($row['manager_id'] ?? 0) === $manager_id
            && (string)($row['date_added'] ?? '') >= $cutoff;
    }

    






    public function getOldestAwaitingReplyCandidates(
        $scope,
        $manager_id,
        $operator_id = 0,
        $source_provider = '',
        $search_query = '',
        $exclude_chat_id = 0,
        $limit = 200
    ) {
        $scope = strtolower(trim((string)$scope));
        if (!in_array($scope, ['unassigned', 'mine', 'all'], true)) {
            return [];
        }

        $where = $this->buildChatScopeWhere($scope, (int)$manager_id, (int)$operator_id, 's', false);
        $source_provider = $this->normalizeSourceFilter($source_provider);
        if ($source_provider !== '') {
            $where .= " AND s.source_provider = '" . $this->db->escape($source_provider) . "'";
        }
        $where .= $this->buildChatSearchWhere($search_query, 's');

        $exclude_chat_id = max(0, (int)$exclude_chat_id);
        if ($exclude_chat_id > 0) {
            $where .= ' AND s.chat_id != ' . $exclude_chat_id;
        }

        $limit = max(1, min(200, (int)$limit));
        $query = $this->db->query(
            "SELECT s.*,
                    mc.metadata_json AS contact_metadata_json,
                    mt.conversation_kind AS marketplace_conversation_kind,
                    mt.state AS marketplace_state,
                    mt.capabilities_json AS marketplace_capabilities_json,
                    mt.external_thread_id AS marketplace_external_thread_id,
                    lm.author AS last_author,
                    lm.date_added AS last_date
             FROM `{$this->prefix}sessions` s
             LEFT JOIN `{$this->prefix}messenger_contacts` mc ON mc.contact_id = s.external_contact_id
             LEFT JOIN `{$this->prefix}marketplace_threads` mt ON mt.chat_id = s.chat_id
             INNER JOIN `{$this->prefix}messages` lm ON lm.message_id = (
                SELECT m_last.message_id
                FROM `{$this->prefix}messages` m_last FORCE INDEX (`idx_chat_last_message_lookup`)
                WHERE m_last.chat_id = s.chat_id
                  AND m_last.message_kind != 'messenger_system_event'
                ORDER BY m_last.date_added DESC, m_last.message_id DESC
                LIMIT 1
             )
             WHERE " . $where . "
               AND s.is_blocked = 0
               AND lm.author = 1
             ORDER BY lm.date_added ASC, s.chat_id ASC
             LIMIT " . $limit
        );

        return $query->rows;
    }

     
    public function getChatIdsForDeletion($archived = false, $source_provider = '', $scope = '', $manager_id = 0, $operator_id = 0): array {
        $where = $this->buildChatScopeWhere($scope, $manager_id, $operator_id, 's', $archived);
        $source_provider = $this->normalizeSourceFilter($source_provider);
        if ($source_provider !== '') {
            $where .= " AND s.`source_provider` = '" . $this->db->escape($source_provider) . "'";
        }
        $query = $this->db->query(
            "SELECT s.`chat_id` FROM `{$this->prefix}sessions` s
             WHERE {$where} ORDER BY `chat_id` ASC"
        );
        $ids = [];
        foreach ($query->rows as $row) {
            $chat_id = (int)($row['chat_id'] ?? 0);
            if ($chat_id > 0) $ids[] = $chat_id;
        }
        return $ids;
    }

    public function getTotalChats($archived = false, $source_provider = '', $scope = '', $manager_id = 0, $operator_id = 0, $search_query = '') {
        $source_provider = $this->normalizeSourceFilter($source_provider);
        $where = $this->buildChatScopeWhere($scope, $manager_id, $operator_id, 's', $archived);
        if ($source_provider !== '') {
            $where .= " AND s.`source_provider` = '" . $this->db->escape($source_provider) . "'";
        }
        $where .= $this->buildChatSearchWhere($search_query, 's');
        $query = $this->db->query("SELECT COUNT(*) AS total FROM `{$this->prefix}sessions` s WHERE " . $where);
        return (int)$query->row['total'];
    }

    public function getSourceProviderCounts($archived = false, $scope = '', $manager_id = 0, $operator_id = 0, $search_query = ''): array {
        $where = $this->buildChatScopeWhere($scope, $manager_id, $operator_id, 's', $archived);
        $where .= $this->buildChatSearchWhere($search_query, 's');
        $query = $this->db->query(
            "SELECT s.`source_provider`, COUNT(*) AS total
             FROM `{$this->prefix}sessions` s
             WHERE " . $where . "
             GROUP BY s.`source_provider`"
        );

        $counts = ['all' => 0];
        foreach ($query->rows as $row) {
            $provider = $this->normalizeSourceFilter($row['source_provider'] ?? '');
            if ($provider === '') {
                $provider = 'widget';
            }
            $count = (int)($row['total'] ?? 0);
            $counts[$provider] = ($counts[$provider] ?? 0) + $count;
            $counts['all'] += $count;
        }
        return $counts;
    }

    



    private function buildChatSearchWhere($search_query, $alias): string {
        $search_query = trim((string)$search_query);
        $search_query = preg_replace('/[\x00-\x1F\x7F]+/u', ' ', $search_query) ?: '';
        $search_query = preg_replace('/\s+/u', ' ', $search_query) ?: '';
        $search_query = mb_substr($search_query, 0, 100);
        if ($search_query === '') {
            return '';
        }

        $alias = preg_replace('/[^a-z]/i', '', (string)$alias);
        $alias = $alias !== '' ? $alias : 's';
         
         
        $like = str_replace(['=', '%', '_'], ['==', '=%', '=_'], $search_query);
        $like = $this->db->escape('%' . $like . '%');

        return " AND (
            {$alias}.`customer_name` LIKE '{$like}' ESCAPE '='
            OR EXISTS (
                SELECT 1
                FROM `{$this->prefix}customer_contact_points` search_cp
                WHERE search_cp.`customer_id` = {$alias}.`customer_id`
                  AND search_cp.`value` LIKE '{$like}' ESCAPE '='
            )
        )";
    }

    


    public function getQueueCounts($manager_id): array {
        $manager_id = max(0, (int)$manager_id);
        $query = $this->db->query(
            "SELECT
                SUM(CASE WHEN s.`is_archived` = 0 AND NOT EXISTS (
                    SELECT 1
                    FROM `{$this->prefix}chat_operator_assignments` qa
                    INNER JOIN `{$this->prefix}managers` qm
                        ON qm.`id` = qa.`manager_id` AND qm.`status` = 1
                    WHERE qa.`chat_id` = s.`chat_id`
                ) THEN 1 ELSE 0 END) AS `unassigned`,
                SUM(CASE WHEN s.`is_archived` = 0 AND EXISTS (
                    SELECT 1
                    FROM `{$this->prefix}chat_operator_assignments` qa
                    INNER JOIN `{$this->prefix}managers` qm
                        ON qm.`id` = qa.`manager_id` AND qm.`status` = 1
                    WHERE qa.`chat_id` = s.`chat_id`
                      AND qa.`manager_id` = " . $manager_id . "
                ) THEN 1 ELSE 0 END) AS `mine`,
                SUM(CASE WHEN s.`is_archived` = 0 THEN 1 ELSE 0 END) AS `all`,
                SUM(CASE WHEN s.`is_archived` = 1 THEN 1 ELSE 0 END) AS `archive`
             FROM `{$this->prefix}sessions` s"
        );
        $row = $query->row ?: [];
        return [
            'unassigned' => (int)($row['unassigned'] ?? 0),
            'mine' => (int)($row['mine'] ?? 0),
            'all' => (int)($row['all'] ?? 0),
            'archive' => (int)($row['archive'] ?? 0),
        ];
    }

    private function buildChatScopeWhere($scope, $manager_id, $operator_id, $alias, $legacy_archived): string {
        $scope = strtolower((string)$scope);
        if (!in_array($scope, ['unassigned', 'mine', 'all', 'archive', 'sync'], true)) {
            $scope = $legacy_archived ? 'archive' : 'all';
        }
        $alias = preg_replace('/[^a-z]/i', '', (string)$alias);
        $alias = $alias !== '' ? $alias : 's';
        $manager_id = max(0, (int)$manager_id);
        $operator_id = max(0, (int)$operator_id);

        $effective_exists =
            "EXISTS (
                SELECT 1
                FROM `{$this->prefix}chat_operator_assignments` scope_a
                INNER JOIN `{$this->prefix}managers` scope_m
                    ON scope_m.`id` = scope_a.`manager_id` AND scope_m.`status` = 1
                WHERE scope_a.`chat_id` = {$alias}.`chat_id`";

        if ($scope === 'unassigned') {
            $where = "{$alias}.`is_archived` = 0 AND NOT " . $effective_exists . ")";
        } elseif ($scope === 'mine') {
            $where = "{$alias}.`is_archived` = 0 AND " . $effective_exists
                . " AND scope_a.`manager_id` = " . $manager_id . ")";
        } elseif ($scope === 'archive') {
            $where = "{$alias}.`is_archived` = 1";
        } elseif ($scope === 'sync') {
             
             
             
            $where = '1 = 1';
        } else {
            $where = "{$alias}.`is_archived` = 0";
        }

        if ($operator_id > 0 && in_array($scope, ['all', 'archive'], true)) {
            $where .=
                " AND EXISTS (
                    SELECT 1
                    FROM `{$this->prefix}chat_operator_assignments` filter_a
                    INNER JOIN `{$this->prefix}managers` filter_m
                        ON filter_m.`id` = filter_a.`manager_id` AND filter_m.`status` = 1
                    WHERE filter_a.`chat_id` = {$alias}.`chat_id`
                      AND filter_a.`manager_id` = " . $operator_id . "
                )";
        }

        return $where;
    }

    public function deleteSession($chat_id) {
        $chat_id = (int)$chat_id;
        if ($chat_id <= 0) {
            return;
        }

        $this->recordExternalChatDeletion([$chat_id]);
        $this->recordChatDeletion($chat_id);
        $this->unlinkMessengerContactsFromChats([$chat_id]);
        $this->subtractResponseTimeMetrics([$chat_id]);
        $this->db->query("DELETE FROM `{$this->prefix}messenger_message_contexts` WHERE `chat_id` = " . $chat_id);
        $this->db->query("DELETE FROM `{$this->prefix}message_attachments` WHERE `chat_id` = " . $chat_id);
        $this->db->query("DELETE FROM `{$this->prefix}messages` WHERE `chat_id` = " . $chat_id);
        $this->db->query("DELETE FROM `{$this->prefix}sessions` WHERE `chat_id` = " . $chat_id);
         
         
        $this->db->query("DELETE FROM `{$this->prefix}chat_operator_assignments` WHERE `chat_id` = " . $chat_id);
        try {
            $this->db->query("DELETE FROM `{$this->prefix}operator_message_drafts` WHERE `chat_id` = " . $chat_id);
        } catch (\Throwable $ignored) {}
    }

    





    public function lockChatIdsForDeletion(array $chat_ids): array {
        $clean_ids = [];
        foreach ($chat_ids as $chat_id) {
            $chat_id = (int)$chat_id;
            if ($chat_id > 0) $clean_ids[$chat_id] = $chat_id;
        }
        if (!$clean_ids) return [];

        $query = $this->db->query(
            "SELECT `chat_id` FROM `{$this->prefix}sessions`
             WHERE `chat_id` IN (" . implode(',', $clean_ids) . ")
             ORDER BY `chat_id` ASC FOR UPDATE"
        );
        $locked_ids = [];
        foreach ($query->rows as $row) {
            $chat_id = (int)($row['chat_id'] ?? 0);
            if ($chat_id > 0) $locked_ids[] = $chat_id;
        }
        return $locked_ids;
    }

    public function deleteSessions(array $chat_ids) {
        $clean_ids = [];
        foreach ($chat_ids as $chat_id) {
            $chat_id = (int)$chat_id;
            if ($chat_id > 0) {
                $clean_ids[$chat_id] = $chat_id;
            }
        }

        if ($clean_ids) {
            $ids_str = implode(',', $clean_ids);
            $this->recordExternalChatDeletion($clean_ids);
            foreach ($clean_ids as $chat_id) {
                $this->recordChatDeletion($chat_id);
            }
            $this->unlinkMessengerContactsFromChats($clean_ids);
            $this->subtractResponseTimeMetrics($clean_ids);
            $this->db->query("DELETE FROM `{$this->prefix}messenger_message_contexts` WHERE `chat_id` IN ({$ids_str})");
            $this->db->query("DELETE FROM `{$this->prefix}message_attachments` WHERE `chat_id` IN ({$ids_str})");
            $this->db->query("DELETE FROM `{$this->prefix}messages` WHERE `chat_id` IN ({$ids_str})");
            $this->db->query("DELETE FROM `{$this->prefix}sessions` WHERE `chat_id` IN ({$ids_str})");
            $this->db->query("DELETE FROM `{$this->prefix}chat_operator_assignments` WHERE `chat_id` IN ({$ids_str})");
            try {
                $this->db->query("DELETE FROM `{$this->prefix}operator_message_drafts` WHERE `chat_id` IN ({$ids_str})");
            } catch (\Throwable $ignored) {}
        }
    }

    private function recordExternalChatDeletion(array $chat_ids): void {
        $clean_ids = [];
        foreach ($chat_ids as $chat_id) {
            $chat_id = (int)$chat_id;
            if ($chat_id > 0) {
                $clean_ids[$chat_id] = $chat_id;
            }
        }
        if (!$clean_ids) {
            return;
        }

        $now = $this->db->escape((new AppClock($this->registry))->nowSql());
         
         
         
        $this->db->query(
            "INSERT INTO `{$this->prefix}messenger_chat_deletions`
                (`channel_id`, `provider`, `external_chat_id`, `deleted_at`, `date_modified`)
             SELECT `channel_id`, `provider`, `external_chat_id`, '{$now}', '{$now}'
             FROM `{$this->prefix}messenger_contacts`
             WHERE `chat_id` IN (" . implode(',', $clean_ids) . ")
               AND `external_chat_id` <> ''
             ON DUPLICATE KEY UPDATE `provider` = VALUES(`provider`),
                                     `deleted_at` = VALUES(`deleted_at`),
                                     `date_modified` = VALUES(`date_modified`)"
        );
    }

    private function subtractResponseTimeMetrics(array $chat_ids): void {
        try {
            (new ResponseTimeMetrics($this->registry))->subtractChats($chat_ids);
        } catch (\Throwable $e) {
            Logger::error('Response time metrics deletion cleanup failed: ' . $e->getMessage(), 'analytics.log');
        }
    }

    private function unlinkMessengerContactsFromChats(array $chat_ids): void {
        $clean_ids = [];
        foreach ($chat_ids as $chat_id) {
            $chat_id = (int)$chat_id;
            if ($chat_id > 0) {
                $clean_ids[$chat_id] = $chat_id;
            }
        }

        if (!$clean_ids) {
            return;
        }

         
         
        $this->db->query(
            "UPDATE `{$this->prefix}messenger_contacts`
             SET `chat_id` = NULL
             WHERE `chat_id` IN (" . implode(',', $clean_ids) . ")"
        );
        $this->db->query(
            "UPDATE `{$this->prefix}marketplace_threads`
             SET `chat_id` = NULL, `date_modified` = NOW()
             WHERE `chat_id` IN (" . implode(',', $clean_ids) . ")"
        );
    }

    public function getDeletedChatIdsSince($updated_after, $source_provider = '') {
        $updated_after = trim((string)$updated_after);
        if ($updated_after === '') {
            return [];
        }
        $source_provider = $this->normalizeSourceFilter($source_provider);
        $source_sql = $source_provider !== ''
            ? " AND `source_provider` = '" . $this->db->escape($source_provider) . "'"
            : '';

        $query = $this->db->query(
            "SELECT `chat_id`
             FROM `{$this->prefix}chat_sync_deletions`
             WHERE `deleted_at` > '" . $this->db->escape($updated_after) . "'" . $source_sql . "
             ORDER BY `deleted_at` ASC, `chat_id` ASC"
        );

        $ids = [];
        foreach ($query->rows as $row) {
            $ids[] = (int)$row['chat_id'];
        }
        return $ids;
    }

    private function normalizeSourceFilter($source_provider): string {
        $source_provider = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', (string)$source_provider));
        if ($source_provider === '' || $source_provider === 'all') {
            return '';
        }
        return $source_provider;
    }

    private function normalizeConversationKind($kind): string {
        $kind = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', (string)$kind));
        return in_array($kind, ['buyer_chat', 'question', 'review', 'return_claim'], true)
            ? $kind
            : 'chat';
    }

    private function recordChatDeletion($chat_id) {
        $chat_id = (int)$chat_id;
        if ($chat_id <= 0) {
            return;
        }

        $now = $this->db->escape((new AppClock($this->registry))->nowSql());
         
         
        $this->db->query(
            "INSERT INTO `{$this->prefix}chat_sync_deletions` (`chat_id`, `source_provider`, `deleted_at`)
             SELECT `chat_id`, `source_provider`, '{$now}'
             FROM `{$this->prefix}sessions`
             WHERE `chat_id` = " . $chat_id . "
             ON DUPLICATE KEY UPDATE `source_provider` = VALUES(`source_provider`),
                                     `deleted_at` = VALUES(`deleted_at`)"
        );
    }

    public function getUnreadChats($manager_id = 0) {
        $manager_id = max(0, (int)$manager_id);
        $query = $this->db->query(
            "SELECT s.chat_id, mu.unread_count
             FROM `{$this->prefix}sessions` s
             INNER JOIN (
                SELECT m_unread.chat_id, COUNT(*) AS unread_count
                FROM `{$this->prefix}messages` m_unread
                WHERE m_unread.status_admin = 0
                  AND (m_unread.author = 1 OR m_unread.message_kind = 'conversation_start_notice')
                GROUP BY m_unread.chat_id
             ) mu ON mu.chat_id = s.chat_id
             WHERE s.is_archived = 0
               AND (
                    EXISTS (
                        SELECT 1
                        FROM `{$this->prefix}chat_operator_assignments` ua
                        INNER JOIN `{$this->prefix}managers` um
                            ON um.`id` = ua.`manager_id` AND um.`status` = 1
                        WHERE ua.`chat_id` = s.`chat_id`
                          AND ua.`manager_id` = " . $manager_id . "
                    )
                    OR NOT EXISTS (
                        SELECT 1
                        FROM `{$this->prefix}chat_operator_assignments` ua
                        INNER JOIN `{$this->prefix}managers` um
                            ON um.`id` = ua.`manager_id` AND um.`status` = 1
                        WHERE ua.`chat_id` = s.`chat_id`
                    )
               )
             ORDER BY mu.unread_count DESC"
        );
        return $query->rows;
    }
}
