<?php
namespace EasyChat\Model;

use EasyChat\Library\Logger;
use EasyChat\Library\AppClock;
use EasyChat\Library\ResponseTimeMetrics;
use EasyChat\Library\Crm\CrmSyncService;

class Message extends Base {

    public function getLastMessage($chat_id) {
        $query = $this->db->query("SELECT * FROM `{$this->prefix}messages` WHERE `chat_id` = " . (int)$chat_id . " ORDER BY `date_added` DESC, `message_id` DESC LIMIT 1");
        return $query->row;
    }

    public function getLastCustomerMessage($chat_id) {
        $query = $this->db->query("SELECT * FROM `{$this->prefix}messages` WHERE `chat_id` = " . (int)$chat_id . " AND `author` = 1 ORDER BY `date_added` DESC, `message_id` DESC LIMIT 1");
        return $query->row;
    }

    public function getMessageById($message_id) {
        $query = $this->db->query("SELECT * FROM `{$this->prefix}messages` WHERE `message_id` = " . (int)$message_id . " LIMIT 1");
        return $query->row;
    }

    public function getRecentHistory($chat_id, $limit = 10, $after_message_id = 0) {
        $query = $this->db->query("SELECT * FROM (
            SELECT * FROM `{$this->prefix}messages` 
            WHERE `chat_id` = " . (int)$chat_id . "
              AND `message_id` > " . (int)$after_message_id . "
              AND `message_kind` NOT IN ('conversation_start_notice', 'messenger_system_event')
            ORDER BY `date_added` DESC, `message_id` DESC LIMIT " . (int)$limit . "
        ) tmp ORDER BY `date_added` ASC, `message_id` ASC");
        return $query->rows;
    }

    public function getMessagesForUser($chat_id, $limit = 50, $last_message_id = 0) {
        if ($last_message_id > 0) {
            $query = $this->db->query("SELECT m.*, 
                CASE 
                    WHEN m.author = 3 THEN 'ИИ Консультант'
                    WHEN m.manager_name != '' THEN m.manager_name
                    ELSE COALESCE(mgr.name, 'Оператор')
                END AS manager_name,
                mgr.image AS manager_image
                FROM `{$this->prefix}messages` m 
                LEFT JOIN `{$this->prefix}managers` mgr ON m.manager_id = mgr.id 
                WHERE m.chat_id = " . (int)$chat_id . " AND m.message_id > " . (int)$last_message_id . " 
                ORDER BY m.message_id ASC");
            return $query->rows;
        }

        $query = $this->db->query("SELECT sub.*, 
            CASE 
                WHEN sub.author = 3 THEN 'ИИ Консультант'
                WHEN sub.manager_name != '' THEN sub.manager_name
                ELSE COALESCE(mgr.name, 'Оператор')
            END AS manager_name,
            mgr.image AS manager_image
            FROM (
                SELECT * FROM `{$this->prefix}messages` 
                WHERE `chat_id` = " . (int)$chat_id . " 
                ORDER BY `date_added` DESC, `message_id` DESC LIMIT " . (int)$limit . "
            ) sub LEFT JOIN `{$this->prefix}managers` mgr ON sub.manager_id = mgr.id ORDER BY sub.date_added ASC, sub.message_id ASC");
        return $query->rows;
    }

    public function getInitialChatHistory($chat_id, $limit = 80) {
        $limit = max(1, min(500, (int)$limit));
         
         
         
         
        $query = $this->db->query("SELECT sub.*,
            CASE
                WHEN sub.author = 3 THEN 'ИИ Консультант'
                WHEN sub.manager_name != '' THEN sub.manager_name
                ELSE COALESCE(mgr.name, 'Оператор')
            END AS manager_name,
            mgr.image AS manager_image
            FROM (
                SELECT *
                FROM `{$this->prefix}messages`
                WHERE `chat_id` = " . (int)$chat_id . "
                ORDER BY `date_added` DESC, `message_id` DESC
                LIMIT " . $limit . "
            ) sub LEFT JOIN `{$this->prefix}managers` mgr ON sub.manager_id = mgr.id
            ORDER BY sub.date_added ASC, sub.message_id ASC");
        return $query->rows;
    }

    public function getChatHistoryBefore($chat_id, $limit = 80, $before_message_id = 0) {
        $limit = max(1, min(500, (int)$limit));
        $before_message_id = (int)$before_message_id;
        if ($before_message_id <= 0) {
            return [];
        }

        $query = $this->db->query("SELECT sub.*,
            CASE
                WHEN sub.author = 3 THEN 'ИИ Консультант'
                WHEN sub.manager_name != '' THEN sub.manager_name
                ELSE COALESCE(mgr.name, 'Оператор')
            END AS manager_name,
            mgr.image AS manager_image
            FROM (
                SELECT *
                FROM `{$this->prefix}messages`
                WHERE `chat_id` = " . (int)$chat_id . "
                  AND `message_id` < " . $before_message_id . "
                ORDER BY `date_added` DESC, `message_id` DESC
                LIMIT " . $limit . "
            ) sub LEFT JOIN `{$this->prefix}managers` mgr ON sub.manager_id = mgr.id
            ORDER BY sub.date_added ASC, sub.message_id ASC");
        return $query->rows;
    }

    




    public function markAsReadByUser($chat_id, $source = 'confirmed') {
        $now = $this->db->escape((new AppClock($this->registry))->nowSql());
        $source = $this->db->escape(substr(preg_replace('/[^a-z0-9_-]/i', '', (string)$source), 0, 24));
        $this->db->query("UPDATE `{$this->prefix}messages`
            SET `status_user` = 1,
                `customer_read_at` = COALESCE(`customer_read_at`, '{$now}'),
                `customer_read_source` = IF(`customer_read_source` = '', '{$source}', `customer_read_source`),
                `date_modified` = '{$now}'
            WHERE `chat_id` = " . (int)$chat_id . " AND `author` != 1 AND `status_user` = 0");
        if ((int)$this->db->countAffected() > 0) {
            $this->touchChat((int)$chat_id);
        }
    }

    public function getUnreadCountForUser($chat_id) {
         
         
         
        $query = $this->db->query(
            "SELECT COUNT(*) AS total
             FROM `{$this->prefix}messages`
             WHERE `chat_id` = " . (int)$chat_id . "
               AND `author` != 1
               AND `status_user` = 0"
        );

        return isset($query->row['total']) ? (int)$query->row['total'] : 0;
    }

    public function markAsReadByAdmin($chat_id) {
        $now = $this->db->escape((new AppClock($this->registry))->nowSql());
        $this->db->query("UPDATE `{$this->prefix}messages`
            SET `status_admin` = 1, `date_modified` = '{$now}'
            WHERE `chat_id` = " . (int)$chat_id . "
              AND (`author` = 1 OR `message_kind` = 'conversation_start_notice')
              AND `status_admin` = 0");
        $affected = (int)$this->db->countAffected();
        if ($affected > 0) {
            $this->touchChat((int)$chat_id);
        }
        return $affected;
    }

    







    public function markAsReadByAdminIds(int $chat_id, array $message_ids): array {
        $message_ids = array_values(array_unique(array_filter(array_map('intval', $message_ids), function ($id) {
            return $id > 0;
        })));
        $message_ids = array_slice($message_ids, 0, 100);
        if ($chat_id <= 0 || !$message_ids) {
            return ['affected' => 0, 'remaining' => 0, 'message_ids' => []];
        }

        $now = $this->db->escape((new AppClock($this->registry))->nowSql());
        $this->db->query(
            "UPDATE `{$this->prefix}messages`
             SET `status_admin` = 1, `date_modified` = '{$now}'
             WHERE `chat_id` = " . (int)$chat_id . "
               AND `message_id` IN (" . implode(',', $message_ids) . ")
               AND (`author` = 1 OR `message_kind` = 'conversation_start_notice')
               AND `status_admin` = 0"
        );
        $affected = (int)$this->db->countAffected();

        $remaining_query = $this->db->query(
            "SELECT COUNT(*) AS total
             FROM `{$this->prefix}messages`
             WHERE `chat_id` = " . (int)$chat_id . "
               AND (`author` = 1 OR `message_kind` = 'conversation_start_notice')
               AND `status_admin` = 0"
        );

        return [
            'affected' => $affected,
            'remaining' => (int)($remaining_query->row['total'] ?? 0),
            'message_ids' => $message_ids,
        ];
    }

    






    public function markChatsAsReadByAdmin(array $chat_ids): array {
        $ids = [];
        foreach (array_slice($chat_ids, 0, 200) as $chat_id) {
            $chat_id = (int)$chat_id;
            if ($chat_id > 0) {
                $ids[$chat_id] = $chat_id;
            }
        }
        if (!$ids) {
            return ['chat_ids' => [], 'messages' => 0];
        }

        $query = $this->db->query(
            "SELECT `chat_id` FROM `{$this->prefix}sessions`
             WHERE `chat_id` IN (" . implode(',', $ids) . ")"
        );
        $existing = [];
        foreach ((array)$query->rows as $row) {
            $chat_id = (int)($row['chat_id'] ?? 0);
            if ($chat_id > 0) {
                $existing[$chat_id] = $chat_id;
            }
        }
        if (!$existing) {
            return ['chat_ids' => [], 'messages' => 0];
        }

        $id_sql = implode(',', $existing);
        $now = $this->db->escape((new AppClock($this->registry))->nowSql());
        $this->db->query('START TRANSACTION');
        try {
            $this->db->query(
                "UPDATE `{$this->prefix}messages`
                 SET `status_admin` = 1, `date_modified` = '{$now}'
                 WHERE `chat_id` IN ({$id_sql})
                   AND (`author` = 1 OR `message_kind` = 'conversation_start_notice')
                   AND `status_admin` = 0"
            );
            $affected = (int)$this->db->countAffected();
            if ($affected > 0) {
                $this->db->query(
                    "UPDATE `{$this->prefix}sessions`
                     SET `date_modified` = '{$now}'
                     WHERE `chat_id` IN ({$id_sql})"
                );
            }
            $this->db->query('COMMIT');
        } catch (\Throwable $e) {
            $this->db->query('ROLLBACK');
            throw $e;
        }

        return ['chat_ids' => array_values($existing), 'messages' => $affected];
    }

    public function addMessage($data) {
        $chat_id      = (int)$data['chat_id'];
        $text         = $data['text'];
        $image        = $data['image'] ?? '';
        $author       = (int)$data['author'];
        $manager_id   = isset($data['manager_id']) ? (int)$data['manager_id'] : 'NULL';
        $manager_name = $data['manager_name'] ?? '';
        $status_user  = (int)($data['status_user'] ?? 0);
        $status_admin = (int)($data['status_admin'] ?? 0);
        $ip           = $data['ip'] ?? '';
        $source_provider = isset($data['source_provider']) ? preg_replace('/[^a-z0-9_\-]/i', '', (string)$data['source_provider']) : 'widget';
        $source_provider = $source_provider !== '' ? strtolower($source_provider) : 'widget';
        $source_channel_id = isset($data['source_channel_id']) ? (int)$data['source_channel_id'] : 0;
        $external_message_id = (string)($data['external_message_id'] ?? '');
        $message_kind = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', (string)($data['message_kind'] ?? 'normal')));
        $message_kind = $message_kind !== '' ? mb_substr($message_kind, 0, 32) : 'normal';
        $date_added = $this->normalizeDate((string)($data['date_added'] ?? ''));
         
         
         
        $now = (new AppClock($this->registry))->nowSql();
        $date_added_sql = $date_added !== '' ? "'" . $this->db->escape($date_added) . "'" : "'" . $this->db->escape($now) . "'";
        $date_modified_sql = "'" . $this->db->escape($now) . "'";
        $touch_chat = !array_key_exists('touch_chat', $data) || (bool)$data['touch_chat'];
        $skip_metrics = !empty($data['skip_metrics']);

        $manager_val = ($manager_id !== 'NULL' && $manager_id > 0) ? $manager_id : 'NULL';

        $this->db->query("INSERT INTO `{$this->prefix}messages` SET 
            `chat_id` = {$chat_id},
            `author` = {$author},
            `manager_id` = {$manager_val},
            `manager_name` = '" . $this->db->escape($manager_name) . "',
            `text` = '" . $this->db->escape($text) . "',
            `image` = '" . $this->db->escape($image) . "',
            `status_user` = {$status_user},
            `status_admin` = {$status_admin},
            `ip` = '" . $this->db->escape($ip) . "',
            `source_provider` = '" . $this->db->escape($source_provider) . "',
            `source_channel_id` = " . ($source_channel_id > 0 ? (int)$source_channel_id : 'NULL') . ",
            `external_message_id` = '" . $this->db->escape($external_message_id) . "',
            `message_kind` = '" . $this->db->escape($message_kind) . "',
            `date_added` = {$date_added_sql},
            `date_modified` = {$date_modified_sql}");

        $message_id = (int)$this->db->getLastId();
        if ($touch_chat) {
            $this->touchChat($chat_id);
        }

         
         
         
        if ($author === 2 && $manager_val !== 'NULL' && (int)$manager_val > 0) {
            try {
                (new ChatAssignment($this->registry))->assign(
                    $chat_id,
                    (int)$manager_val,
                    (int)$manager_val,
                    'reply'
                );
            } catch (\Throwable $e) {
                Logger::error(
                    'Chat auto-assignment failed after message #' . $message_id . ': ' . $e->getMessage(),
                    'chat-assignment.log'
                );
            }
        }

        if (!$skip_metrics) {
            try {
                (new ResponseTimeMetrics($this->registry))->recordMessage($message_id);
            } catch (\Throwable $e) {
                Logger::error('Response time metrics failed for message #' . $message_id . ': ' . $e->getMessage(), 'analytics.log');
            }
        }

         
         
         
        if ($author === 1 && $touch_chat && $message_kind === 'normal') {
            try {
                (new CrmSyncService($this->registry))->enqueueIncoming($chat_id, $message_id);
            } catch (\Throwable $e) {
                Logger::error('CRM enqueue failed for message #' . $message_id . ': ' . $e->getMessage(), 'crm.log');
            }
        }

        return $message_id;
    }

    public function findByExternalMessageId(string $provider, int $channel_id, string $external_message_id): array {
        $provider = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', $provider));
        $external_message_id = trim($external_message_id);
        if ($provider === '' || $channel_id <= 0 || $external_message_id === '') {
            return [];
        }

        $query = $this->db->query(
            "SELECT *
             FROM `{$this->prefix}messages`
             WHERE `source_provider` = '" . $this->db->escape($provider) . "'
               AND `source_channel_id` = " . (int)$channel_id . "
               AND `external_message_id` = '" . $this->db->escape($external_message_id) . "'
             LIMIT 1"
        );

        return $query->row ?: [];
    }

     
    public function setImageIfEmpty(int $message_id, string $filename): bool {
        $filename = basename(trim($filename));
        if ($message_id <= 0 || $filename === '') {
            return false;
        }
        $this->db->query(
            "UPDATE `{$this->prefix}messages`
             SET `image` = '" . $this->db->escape($filename) . "', `date_modified` = NOW()
             WHERE `message_id` = {$message_id} AND (`image` = '' OR `image` IS NULL)"
        );
        return (int)$this->db->countAffected() > 0;
    }

    public function findProviderEchoMessages(string $provider, int $channel_id, string $external_message_id, int $message_id): array {
        $provider = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', $provider));
        $external_message_id = trim($external_message_id);
        if ($provider === '' || $channel_id <= 0 || $external_message_id === '' || $message_id <= 0) {
            return [];
        }

        $query = $this->db->query(
            "SELECT `message_id`, `chat_id`
             FROM `{$this->prefix}messages`
             WHERE `source_provider` = '" . $this->db->escape($provider) . "'
               AND `source_channel_id` = " . (int)$channel_id . "
               AND `external_message_id` = '" . $this->db->escape($external_message_id) . "'
               AND `message_id` != " . (int)$message_id
        );
        return $query->rows ?: [];
    }

    public function mergeProviderEchoMessage(int $echo_message_id, int $message_id): void {
        if ($echo_message_id <= 0 || $message_id <= 0 || $echo_message_id === $message_id) {
            return;
        }

        $this->db->query('START TRANSACTION');
        try {
            $this->db->query(
                "UPDATE `{$this->prefix}messenger_deliveries`
                 SET `message_id` = " . (int)$message_id . ", `date_modified` = NOW()
                 WHERE `message_id` = " . (int)$echo_message_id
            );
            $this->db->query(
                "DELETE FROM `{$this->prefix}messenger_message_contexts`
                 WHERE `message_id` = " . (int)$echo_message_id
            );
            $this->db->query(
                "DELETE FROM `{$this->prefix}messages`
                 WHERE `message_id` = " . (int)$echo_message_id
            );
            $this->db->query('COMMIT');
        } catch (\Throwable $e) {
            $this->db->query('ROLLBACK');
            throw $e;
        }
    }

    







    public function findExistingExternalMessageIds(string $provider, int $channel_id, array $external_message_ids): array {
        $provider = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', $provider));
        if ($provider === '' || $channel_id <= 0 || !$external_message_ids) {
            return [];
        }

        $ids = [];
        foreach (array_slice($external_message_ids, 0, 100) as $external_message_id) {
            $external_message_id = trim((string)$external_message_id);
            if ($external_message_id === '') {
                continue;
            }
            $ids[$external_message_id] = "'" . $this->db->escape($external_message_id) . "'";
        }
        if (!$ids) {
            return [];
        }

        $query = $this->db->query(
            "SELECT `message_id`, `chat_id`, `external_message_id`, `author`, `text`, `image`, `date_added`
             FROM `{$this->prefix}messages`
             WHERE `source_provider` = '" . $this->db->escape($provider) . "'
               AND `source_channel_id` = " . (int)$channel_id . "
               AND `external_message_id` IN (" . implode(',', array_values($ids)) . ")"
        );

        $existing = [];
        foreach ((array)$query->rows as $row) {
            $external_message_id = (string)($row['external_message_id'] ?? '');
            if ($external_message_id !== '') {
                $existing[$external_message_id] = $row;
            }
        }

        return $existing;
    }

    public function findByExternalMessageIdInChat(int $chat_id, string $external_message_id): array {
        $external_message_id = trim($external_message_id);
        if ($chat_id <= 0 || $external_message_id === '') {
            return [];
        }

        $query = $this->db->query(
            "SELECT *
             FROM `{$this->prefix}messages`
             WHERE `chat_id` = " . (int)$chat_id . "
               AND `external_message_id` = '" . $this->db->escape($external_message_id) . "'
             LIMIT 1"
        );

        return $query->row ?: [];
    }

    public function getRecentIncomingExternalMessages(int $chat_id, string $provider, int $channel_id, int $limit = 5): array {
        $provider = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', $provider));
        $limit = max(1, min(20, (int)$limit));
        if ($chat_id <= 0 || $provider === '' || $channel_id <= 0) {
            return [];
        }

        $query = $this->db->query(
            "SELECT *
             FROM `{$this->prefix}messages`
             WHERE `chat_id` = " . (int)$chat_id . "
               AND `author` = 1
               AND `source_provider` = '" . $this->db->escape($provider) . "'
               AND `source_channel_id` = " . (int)$channel_id . "
             ORDER BY `date_added` DESC, `message_id` DESC
             LIMIT " . (int)$limit
        );

        return $query->rows ?: [];
    }

    public function updateIncomingExternalMessageRevision($message_id, array $data): void {
        $message_id = (int)$message_id;
        if ($message_id <= 0) {
            return;
        }

        $provider = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', (string)($data['source_provider'] ?? '')));
        $channel_id = (int)($data['source_channel_id'] ?? 0);
        $date_added = $this->normalizeDate((string)($data['date_added'] ?? ''));
        $now = (new AppClock($this->registry))->nowSql();

        $parts = [
            "`text` = '" . $this->db->escape((string)($data['text'] ?? '')) . "'",
            "`image` = '" . $this->db->escape((string)($data['image'] ?? '')) . "'",
            "`external_message_id` = '" . $this->db->escape((string)($data['external_message_id'] ?? '')) . "'",
            "`date_modified` = '" . $this->db->escape($now) . "'",
        ];

        if ($provider !== '') {
            $parts[] = "`source_provider` = '" . $this->db->escape($provider) . "'";
        }
        if ($channel_id > 0) {
            $parts[] = "`source_channel_id` = " . $channel_id;
        }
        if ($date_added !== '') {
            $parts[] = "`date_added` = '" . $this->db->escape($date_added) . "'";
        }

        $this->db->query(
            "UPDATE `{$this->prefix}messages`
             SET " . implode(', ', $parts) . "
             WHERE `message_id` = " . $message_id . "
               AND `author` = 1"
        );

        $message = $this->getMessageById($message_id);
        if ($message && isset($message['chat_id'])) {
            $this->touchChat((int)$message['chat_id']);
        }
    }

    






    public function updateMutableMarketplaceMessage(int $message_id, array $data): bool {
        $provider = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', (string)($data['source_provider'] ?? '')));
        $channel_id = (int)($data['source_channel_id'] ?? 0);
        $external_message_id = trim((string)($data['external_message_id'] ?? ''));
        if ($message_id <= 0 || $provider !== 'wildberries' || $channel_id <= 0 || $external_message_id === '') {
            return false;
        }

        $text = (string)($data['text'] ?? '');
        $now = (new AppClock($this->registry))->nowSql();
        $parts = [
            "`text` = '" . $this->db->escape($text) . "'",
            "`status_user` = 0",
            "`status_admin` = 1",
            "`date_modified` = '" . $this->db->escape($now) . "'",
        ];
        $date_added = !empty($data['update_event_date'])
            ? $this->normalizeDate((string)($data['date_added'] ?? ''))
            : '';
        if ($date_added !== '') {
            $parts[] = "`date_added` = '" . $this->db->escape($date_added) . "'";
        }

        $this->db->query(
            "UPDATE `{$this->prefix}messages`
             SET " . implode(', ', $parts) . "
             WHERE `message_id` = " . $message_id . "
               AND `author` = 2
               AND `source_provider` = '" . $this->db->escape($provider) . "'
               AND `source_channel_id` = " . $channel_id . "
               AND `external_message_id` = '" . $this->db->escape($external_message_id) . "'
               AND (
                    `text` != '" . $this->db->escape($text) . "'
                    OR `status_user` != 0
                    OR `status_admin` != 1"
                    . ($date_added !== ''
                        ? " OR `date_added` != '" . $this->db->escape($date_added) . "'"
                        : '') . "
               )"
        );
        return (int)$this->db->countAffected() > 0;
    }

    




    public function promoteExternalMessageToSystemEvent(int $message_id, array $data): bool {
        $provider = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', (string)($data['source_provider'] ?? '')));
        $channel_id = (int)($data['source_channel_id'] ?? 0);
        if ($message_id <= 0 || $provider !== 'max' || $channel_id <= 0) {
            return false;
        }

        $date_added = $this->normalizeDate((string)($data['date_added'] ?? ''));
        $now = (new AppClock($this->registry))->nowSql();
        $text = $this->db->escape((string)($data['text'] ?? ''));
        $parts = [
            "`author` = 4",
            "`manager_id` = NULL",
            "`manager_name` = ''",
            "`text` = '" . $text . "'",
            "`image` = ''",
            "`message_kind` = 'messenger_system_event'",
            "`status_user` = 1",
            "`status_admin` = 1",
            "`date_modified` = '" . $this->db->escape($now) . "'",
        ];
        if ($date_added !== '') {
            $parts[] = "`date_added` = '" . $this->db->escape($date_added) . "'";
        }

        $this->db->query(
            "UPDATE `{$this->prefix}messages`
             SET " . implode(', ', $parts) . "
             WHERE `message_id` = " . $message_id . "
               AND `source_provider` = 'max'
               AND `source_channel_id` = " . $channel_id . "
               AND `author` IN (1, 4)
               AND (
                   `author` != 4
                   OR `message_kind` != 'messenger_system_event'
                   OR `text` != '" . $text . "'
                   OR `image` != ''
                   OR `status_user` != 1
                   OR `status_admin` != 1
               )"
        );

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

    public function updateExternalMessageSource($message_id, string $provider, int $channel_id): void {
        $provider = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', $provider));
        if ((int)$message_id <= 0 || $provider === '' || $channel_id <= 0) {
            return;
        }

        $this->db->query(
            "UPDATE `{$this->prefix}messages`
             SET `source_provider` = '" . $this->db->escape($provider) . "',
                 `source_channel_id` = " . (int)$channel_id . ",
                 `date_modified` = '" . $this->db->escape((new AppClock($this->registry))->nowSql()) . "'
             WHERE `message_id` = " . (int)$message_id
        );
    }

    public function updateExternalMessageId($message_id, $external_message_id, string $provider = '', int $channel_id = 0) {
        $parts = [
            "`external_message_id` = '" . $this->db->escape((string)$external_message_id) . "'",
            "`date_modified` = '" . $this->db->escape((new AppClock($this->registry))->nowSql()) . "'",
        ];

        $provider = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', $provider));
        if ($provider !== '' && $channel_id > 0) {
            $parts[] = "`source_provider` = '" . $this->db->escape($provider) . "'";
            $parts[] = "`source_channel_id` = " . (int)$channel_id;
        }

        $this->db->query(
            "UPDATE `{$this->prefix}messages`
             SET " . implode(', ', $parts) . "
             WHERE `message_id` = " . (int)$message_id
        );
    }

    public function getChatHistory($chat_id, $limit = 0, $since_message_id = 0, $updated_after = '') {
        $where = "m.chat_id = " . (int)$chat_id;
        $since_message_id = (int)$since_message_id;
        $updated_after = trim((string)$updated_after);
        if ($since_message_id > 0 && $updated_after !== '') {
             
             
             
             
            $where .= " AND (m.message_id > " . $since_message_id . " OR m.date_modified >= '" . $this->db->escape($updated_after) . "')";
        } elseif ($since_message_id > 0) {
            $where .= " AND m.message_id > " . $since_message_id;
        } elseif ($updated_after !== '') {
            $where .= " AND m.date_modified >= '" . $this->db->escape($updated_after) . "'";
        }

        $limit_sql = (int)$limit > 0 ? " LIMIT " . (int)$limit : "";

        $query = $this->db->query("SELECT m.*, 
            CASE 
                WHEN m.author = 3 THEN 'ИИ Консультант'
                WHEN m.manager_name != '' THEN m.manager_name
                ELSE COALESCE(mgr.name, 'Оператор')
            END AS manager_name,
            mgr.image AS manager_image
            FROM `{$this->prefix}messages` m
            LEFT JOIN `{$this->prefix}managers` mgr ON m.manager_id = mgr.id
            WHERE " . $where . "
            ORDER BY m.date_added ASC, m.message_id ASC" . $limit_sql);
        return $query->rows;
    }

    private function touchChat($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);
    }

    private function normalizeDate(string $date): string {
        $date = trim($date);
        if ($date === '' || !preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $date)) {
            return '';
        }
        return (new AppClock($this->registry))->sqlToTimestamp($date) !== false ? $date : '';
    }

    public function getUnreadCount() {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM `{$this->prefix}messages` WHERE `status_admin` = 0 AND (`author` = 1 OR `message_kind` = 'conversation_start_notice')");
        return (int)($query->row['total'] ?? 0);
    }

    public function getUnreadCountForManager($manager_id) {
        $manager_id = max(0, (int)$manager_id);
        $query = $this->db->query(
            "SELECT COUNT(*) AS total
             FROM `{$this->prefix}messages` m
             INNER JOIN `{$this->prefix}sessions` s ON s.`chat_id` = m.`chat_id`
             WHERE m.`status_admin` = 0
               AND (m.`author` = 1 OR m.`message_kind` = 'conversation_start_notice')
               AND 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`
                    )
               )"
        );
        return (int)($query->row['total'] ?? 0);
    }
}
