<?php
namespace EasyChat\Model;

use EasyChat\Library\CustomerIdentityResolver;
use EasyChat\Library\Messenger\MaxConversationIdentity;
use EasyChat\Library\Messenger\Marketplace\MarketplaceProviderCatalog;
use EasyChat\Library\Messenger\Marketplace\MarketplaceText;

class Messenger extends Base {

    public function listChannels(array $providers = []): array {
        $where = '';
        if ($providers) {
            $safe = [];
            foreach ($providers as $provider) {
                $provider = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', (string)$provider));
                if ($provider !== '') {
                    $safe[$provider] = "'" . $this->db->escape($provider) . "'";
                }
            }
            if ($safe) {
                $where = ' WHERE `provider` IN (' . implode(',', $safe) . ')';
            }
        }
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}messenger_channels`{$where}
             ORDER BY `provider` ASC, `title` ASC, `channel_id` ASC"
        );
        return $query->rows ?: [];
    }

    public function getChannelByProvider($provider) {
        $provider_e = $this->db->escape($provider);
        $query = $this->db->query("SELECT * FROM `{$this->prefix}messenger_channels` WHERE `provider` = '{$provider_e}' ORDER BY `channel_id` ASC LIMIT 1");
        return $query->row;
    }

    public function getChannelByProviderAndAccount($provider, $external_account_id) {
        $provider = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', (string)$provider));
        $external_account_id = trim((string)$external_account_id);
        if ($provider === '' || $external_account_id === '') {
            return [];
        }
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}messenger_channels`
             WHERE `provider` = '" . $this->db->escape($provider) . "'
               AND `external_account_id` = '" . $this->db->escape($external_account_id) . "'
             ORDER BY `channel_id` ASC LIMIT 1"
        );
        return $query->row;
    }

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

    public function getChannelByPublicId($provider, $public_id) {
        $provider_e = $this->db->escape($provider);
        $public_id_e = $this->db->escape($public_id);
        $query = $this->db->query("SELECT * FROM `{$this->prefix}messenger_channels` WHERE `provider` = '{$provider_e}' AND `public_id` = '{$public_id_e}' LIMIT 1");
        return $query->row;
    }

    public function ensureChannel($provider, $title) {
        $channel = $this->getChannelByProvider($provider);
        if ($channel) {
            return $channel;
        }

        $provider_e = $this->db->escape($provider);
        $title_e = $this->db->escape($title);
        $public_id = 'ch_' . bin2hex(random_bytes(12));
        $public_id_e = $this->db->escape($public_id);
        $this->db->query(
            "INSERT INTO `{$this->prefix}messenger_channels`
             SET `provider` = '{$provider_e}',
                 `title` = '{$title_e}',
                 `status` = 'disabled',
                 `public_id` = '{$public_id_e}',
                 `credentials_json` = '',
                 `settings_json` = '{}',
                 `date_added` = NOW(),
                 `date_modified` = NOW()"
        );
        return $this->getChannelByProvider($provider);
    }

    






    public function ensureMarketplaceChannel($provider, $external_account_id, $title, $auth_type = 'token') {
        $provider = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', (string)$provider));
        $external_account_id = MarketplaceText::truncate(trim((string)$external_account_id), 128);
        $title = MarketplaceText::truncate(trim((string)$title), 128);
        $auth_type = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', (string)$auth_type));
        if (!MarketplaceProviderCatalog::has($provider) || $external_account_id === '') {
            return [];
        }

        $lock_name = 'easychat_marketplace_' . substr(hash('sha256', $provider . ':' . $external_account_id), 0, 40);
        $lock_query = $this->db->query("SELECT GET_LOCK('" . $this->db->escape($lock_name) . "', 3) AS `locked`");
        if ((int)($lock_query->row['locked'] ?? 0) !== 1) {
             
             
             
            return $this->getChannelByProviderAndAccount($provider, $external_account_id);
        }
        try {
            $channel = $this->getChannelByProviderAndAccount($provider, $external_account_id);
            if ($channel) {
                return $channel;
            }

             
             
             
            $public_id = 'ch_' . bin2hex(random_bytes(24));
            $this->db->query(
                "INSERT INTO `{$this->prefix}messenger_channels` SET
                    `provider` = '" . $this->db->escape($provider) . "',
                    `external_account_id` = '" . $this->db->escape($external_account_id) . "',
                    `auth_type` = '" . $this->db->escape($auth_type !== '' ? $auth_type : 'token') . "',
                    `title` = '" . $this->db->escape($title !== '' ? $title : strtoupper($provider)) . "',
                    `status` = 'disabled',
                    `public_id` = '" . $this->db->escape($public_id) . "',
                    `credentials_json` = '',
                    `settings_json` = '{}',
                    `capabilities_json` = '{}',
                    `date_added` = NOW(), `date_modified` = NOW()"
            );
            return $this->getChannelByProviderAndAccount($provider, $external_account_id);
        } finally {
            $this->db->query("SELECT RELEASE_LOCK('" . $this->db->escape($lock_name) . "')");
        }
    }

    public function updateChannel($channel_id, array $data): void {
        $parts = [];
        foreach ($data as $key => $value) {
            if ($value === null) {
                $parts[] = "`" . $this->db->escape($key) . "` = NULL";
            } else {
                $parts[] = "`" . $this->db->escape($key) . "` = '" . $this->db->escape((string)$value) . "'";
            }
        }
        if (!$parts) {
            return;
        }
        $parts[] = "`date_modified` = NOW()";
        $this->db->query("UPDATE `{$this->prefix}messenger_channels` SET " . implode(', ', $parts) . " WHERE `channel_id` = " . (int)$channel_id);
    }

    public function findContact($channel_id, $external_user_id) {
        $external_e = $this->db->escape($external_user_id);
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}messenger_contacts`
             WHERE `channel_id` = " . (int)$channel_id . " AND `external_user_id` = '{$external_e}'
             LIMIT 1"
        );
        return $query->row;
    }

    public function findContactByExternalUserIds($channel_id, array $external_user_ids) {
        $values = [];
        foreach ($external_user_ids as $external_user_id) {
            $external_user_id = trim((string)$external_user_id);
            if ($external_user_id !== '') {
                $values[$external_user_id] = "'" . $this->db->escape($external_user_id) . "'";
            }
        }
        if ((int)$channel_id <= 0 || !$values) {
            return [];
        }

        $ordered_values = array_values($values);
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}messenger_contacts`
             WHERE `channel_id` = " . (int)$channel_id . "
               AND `external_user_id` IN (" . implode(',', $ordered_values) . ")
             ORDER BY FIELD(`external_user_id`, " . implode(',', $ordered_values) . ")
             LIMIT 1"
        );
        return $query->row ?: [];
    }

    public function findContactByExternalChat($channel_id, $external_chat_id) {
        $external_chat_id = trim((string)$external_chat_id);
        if ($external_chat_id === '') {
            return [];
        }

        $external_e = $this->db->escape($external_chat_id);
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}messenger_contacts`
             WHERE `channel_id` = " . (int)$channel_id . " AND `external_chat_id` = '{$external_e}'
             ORDER BY `contact_id` DESC
             LIMIT 1"
        );
        return $query->row;
    }

    public function getExternalChatDeletion($channel_id, $external_chat_id) {
        $external_chat_id = trim((string)$external_chat_id);
        if ((int)$channel_id <= 0 || $external_chat_id === '') {
            return [];
        }
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}messenger_chat_deletions`
             WHERE `channel_id` = " . (int)$channel_id . "
               AND `external_chat_id` = '" . $this->db->escape($external_chat_id) . "'
             LIMIT 1"
        );
        return $query->row;
    }

    public function getExternalChatDeletionBoundaries($channel_id, array $external_chat_ids): array {
        $ids = [];
        foreach ($external_chat_ids as $external_chat_id) {
            $external_chat_id = trim((string)$external_chat_id);
            if ($external_chat_id !== '') {
                $ids[$external_chat_id] = "'" . $this->db->escape($external_chat_id) . "'";
            }
        }
        if ((int)$channel_id <= 0 || !$ids) {
            return [];
        }
        $query = $this->db->query(
            "SELECT `external_chat_id`, `deleted_at`
             FROM `{$this->prefix}messenger_chat_deletions`
             WHERE `channel_id` = " . (int)$channel_id . "
               AND `external_chat_id` IN (" . implode(',', $ids) . ")"
        );
        $boundaries = [];
        foreach ($query->rows as $row) {
            $boundaries[(string)$row['external_chat_id']] = (string)$row['deleted_at'];
        }
        return $boundaries;
    }

    public function upsertContact($channel_id, $provider, $external_user_id, $external_chat_id, $display_name, $username, $profile_url, $chat_id = 0, array $metadata = [], $match_external_chat = false) {
        $contact = $this->findContact($channel_id, $external_user_id);
        if (!$contact && $match_external_chat) {
            $contact = $this->findContactByExternalChat($channel_id, $external_chat_id);
        }
        $provider_e = $this->db->escape($provider);
        $external_user_e = $this->db->escape($external_user_id);
        $external_chat_e = $this->db->escape($external_chat_id);
        $display_name_e = $this->db->escape($display_name);
        $username_e = $this->db->escape($username);
        $profile_url_e = $this->db->escape($profile_url);
        $metadata_e = $this->db->escape(json_encode($metadata, JSON_UNESCAPED_UNICODE));
        $customer_identity_hash = (new CustomerIdentityResolver())->hashForContactParts((string)$provider, (int)$channel_id, (string)$external_user_id, $metadata);
        $customer_identity_hash_e = $this->db->escape($customer_identity_hash);

        if ($contact) {
            $chat_sql = $chat_id > 0 ? ", `chat_id` = " . (int)$chat_id : "";
            $this->db->query(
                "UPDATE `{$this->prefix}messenger_contacts`
                 SET `external_user_id` = '{$external_user_e}',
                     `external_chat_id` = '{$external_chat_e}',
                     `display_name` = '{$display_name_e}',
                     `username` = '{$username_e}',
                     `profile_url` = '{$profile_url_e}',
                     `metadata_json` = '{$metadata_e}',
                     `customer_identity_hash` = '{$customer_identity_hash_e}',
                     `last_seen_at` = NOW()
                     {$chat_sql}
                 WHERE `contact_id` = " . (int)$contact['contact_id']
            );
            return $this->getContactById((int)$contact['contact_id']);
        }

        $chat_sql = $chat_id > 0 ? (string)(int)$chat_id : 'NULL';
        $this->db->query(
            "INSERT INTO `{$this->prefix}messenger_contacts`
             SET `channel_id` = " . (int)$channel_id . ",
                 `provider` = '{$provider_e}',
                 `external_user_id` = '{$external_user_e}',
                 `external_chat_id` = '{$external_chat_e}',
                 `display_name` = '{$display_name_e}',
                 `username` = '{$username_e}',
                 `profile_url` = '{$profile_url_e}',
                 `chat_id` = {$chat_sql},
                 `metadata_json` = '{$metadata_e}',
                 `customer_identity_hash` = '{$customer_identity_hash_e}',
                 `last_seen_at` = NOW(),
                 `date_added` = NOW()"
        );
        return $this->findContact($channel_id, $external_user_id);
    }

    public function relatedContactsByCustomerIdentity(array $contact, int $limit = 200): array {
        $identity = $this->customerIdentityLookup($contact);
        if (!$identity) {
            return [];
        }

        $query = $this->db->query(
            "SELECT *
             FROM `{$this->prefix}messenger_contacts`
             WHERE `provider` = '" . $this->db->escape($identity['provider']) . "'
               AND `channel_id` = " . (int)$identity['channel_id'] . "
               AND `customer_identity_hash` = '" . $this->db->escape($identity['customer_identity_hash']) . "'
             ORDER BY `last_seen_at` DESC, `contact_id` DESC
             LIMIT " . max(1, min(500, (int)$limit))
        );

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

    public function customerIdentityLookup(array $contact): array {
        $provider = strtolower((string)($contact['provider'] ?? ''));
        $channel_id = (int)($contact['channel_id'] ?? 0);
        if ($provider === '' || $channel_id <= 0) {
            return [];
        }

        $identity_hash = (string)($contact['customer_identity_hash'] ?? '');
        if ($identity_hash === '') {
            $identity_hash = (new CustomerIdentityResolver())->hashForMessengerContact($contact);
            if ($identity_hash !== '' && !empty($contact['contact_id'])) {
                $this->db->query(
                    "UPDATE `{$this->prefix}messenger_contacts`
                     SET `customer_identity_hash` = '" . $this->db->escape($identity_hash) . "'
                     WHERE `contact_id` = " . (int)$contact['contact_id']
                );
            }
        }

        if ($identity_hash === '') {
            return [];
        }

        return [
            'provider' => $provider,
            'channel_id' => $channel_id,
            'customer_identity_hash' => $identity_hash,
        ];
    }

    public function linkContactToChat($contact_id, $chat_id): void {
        $this->db->query(
            "UPDATE `{$this->prefix}messenger_contacts`
             SET `chat_id` = " . (int)$chat_id . "
             WHERE `contact_id` = " . (int)$contact_id
        );
    }

    public function clearContactChat($contact_id): void {
        $contact_id = (int)$contact_id;
        if ($contact_id <= 0) {
            return;
        }

        $this->db->query(
            "UPDATE `{$this->prefix}messenger_contacts`
             SET `chat_id` = NULL
             WHERE `contact_id` = " . $contact_id
        );
    }

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

    public function listContactsNeedingDisplayNameRepair($channel_id, $provider, $limit = 50): array {
        $provider_e = $this->db->escape((string)$provider);
        $limit = max(1, min(200, (int)$limit));
        $identity_version = (int)MaxConversationIdentity::VERSION;
        $query = $this->db->query(
            "SELECT *
             FROM `{$this->prefix}messenger_contacts`
             WHERE `channel_id` = " . (int)$channel_id . "
               AND `provider` = '{$provider_e}'
               AND (
                    `display_name` = ''
                    OR `display_name` = 'MAX'
                    OR `display_name` = 'MAX клиент'
                    OR `display_name` REGEXP '^MAX ([0-9]+|[0-9A-Fa-f]{6})$'
                    OR COALESCE(`metadata_json`, '') NOT LIKE '%\"identity_version\":" . $identity_version . "%'
               )
             ORDER BY (`identity_repair_checked_at` IS NULL) DESC,
                      `identity_repair_checked_at` ASC,
                      `contact_id` ASC
             LIMIT " . $limit
        );
        return $query->rows ?: [];
    }

    public function updateContactDisplayName($contact_id, $display_name): void {
        $contact_id = (int)$contact_id;
        $display_name = trim((string)$display_name);
        if ($contact_id <= 0 || $display_name === '') {
            return;
        }

        $this->db->query(
            "UPDATE `{$this->prefix}messenger_contacts`
             SET `display_name` = '" . $this->db->escape($display_name) . "',
                 `last_seen_at` = NOW()
             WHERE `contact_id` = " . $contact_id
        );
    }

    public function markContactDisplayNameRepairChecked($contact_id): void {
        $contact_id = (int)$contact_id;
        if ($contact_id <= 0) {
            return;
        }
        $this->db->query(
            "UPDATE `{$this->prefix}messenger_contacts`
             SET `identity_repair_checked_at` = NOW()
             WHERE `contact_id` = " . $contact_id
        );
    }

    public function updateContactIdentity($contact_id, $display_name, array $metadata): void {
        $contact_id = (int)$contact_id;
        $display_name = trim((string)$display_name);
        if ($contact_id <= 0 || $display_name === '') {
            return;
        }

        $metadata_json = json_encode($metadata, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        if (!is_string($metadata_json)) {
            $metadata_json = '{}';
        }
        $this->db->query(
            "UPDATE `{$this->prefix}messenger_contacts`
             SET `display_name` = '" . $this->db->escape($display_name) . "',
                 `metadata_json` = '" . $this->db->escape($metadata_json) . "',
                 `last_seen_at` = NOW()
             WHERE `contact_id` = " . $contact_id
        );
    }

    public function getContactByAvatarFile($file) {
        $file = basename(trim((string)$file));
        if ($file === '') {
            return [];
        }
        $file_e = $this->db->escape($file);
        $query = $this->db->query("SELECT * FROM `{$this->prefix}messenger_contacts` WHERE `avatar_file` = '{$file_e}' LIMIT 1");
        return $query->row;
    }

    public function updateContactAvatar($contact_id, array $data): bool {
        $contact_id = (int)$contact_id;
        if ($contact_id <= 0) {
            return false;
        }

        $avatar_file_raw = basename((string)($data['avatar_file'] ?? ''));
        $avatar_file = $this->db->escape($avatar_file_raw);
        $avatar_mime = $this->db->escape(substr((string)($data['avatar_mime'] ?? ''), 0, 64));
        $source_hash = $this->db->escape(substr((string)($data['avatar_source_hash'] ?? ''), 0, 64));
        $identity_fingerprint = $this->db->escape(substr((string)($data['avatar_identity_fingerprint'] ?? ''), 0, 64));
        $expected_identity_fingerprint = substr((string)($data['expected_identity_fingerprint'] ?? ''), 0, 64);
        $last_error = $this->db->escape(substr((string)($data['avatar_last_error'] ?? ''), 0, 128));
        $identity_where = '';
        if ($expected_identity_fingerprint !== '') {
            $identity_needle = '%"identity_fingerprint":"' . $expected_identity_fingerprint . '"%';
            $identity_where = " AND `metadata_json` LIKE '" . $this->db->escape($identity_needle) . "'";
        }

        $this->db->query(
            "UPDATE `{$this->prefix}messenger_contacts`
             SET `avatar_file` = '{$avatar_file}',
                 `avatar_mime` = '{$avatar_mime}',
                 `avatar_source_hash` = '{$source_hash}',
                 `avatar_identity_fingerprint` = '{$identity_fingerprint}',
                 `avatar_updated_at` = NOW(),
                 `avatar_checked_at` = NOW(),
                 `avatar_last_error` = '{$last_error}'
             WHERE `contact_id` = " . $contact_id . $identity_where
        );
        if ((int)$this->db->countAffected() > 0) {
            return true;
        }

         
         
         
        $current = $this->getContactById($contact_id);
        if (!$current || basename((string)($current['avatar_file'] ?? '')) !== $avatar_file_raw) {
            return false;
        }
        if ($expected_identity_fingerprint === '') {
            return true;
        }
        $metadata = json_decode((string)($current['metadata_json'] ?? ''), true);
        $actual_identity_fingerprint = is_array($metadata)
            ? (string)($metadata['identity_fingerprint'] ?? '')
            : '';
        return strlen($actual_identity_fingerprint) === 64
            && hash_equals($expected_identity_fingerprint, $actual_identity_fingerprint);
    }

    public function resetContactAvatarForIdentity($contact_id, $expected_file, $expected_identity_fingerprint): bool {
        $contact_id = (int)$contact_id;
        if ($contact_id <= 0) {
            return false;
        }
        $expected_file = basename((string)$expected_file);
        $expected_identity_fingerprint = substr((string)$expected_identity_fingerprint, 0, 64);

        $this->db->query(
            "UPDATE `{$this->prefix}messenger_contacts`
             SET `avatar_file` = '',
                 `avatar_mime` = '',
                 `avatar_source_hash` = '',
                 `avatar_identity_fingerprint` = '',
                 `avatar_updated_at` = NULL,
                 `avatar_checked_at` = NULL,
                 `avatar_last_error` = ''
             WHERE `contact_id` = " . $contact_id . "
               AND `avatar_file` = '" . $this->db->escape($expected_file) . "'
               AND `avatar_identity_fingerprint` = '" . $this->db->escape($expected_identity_fingerprint) . "'"
        );
        return (int)$this->db->countAffected() > 0;
    }

    public function markContactAvatarChecked($contact_id, $source_hash, $error, $expected_identity_fingerprint = ''): void {
        $contact_id = (int)$contact_id;
        if ($contact_id <= 0) {
            return;
        }
        $source_hash = substr((string)$source_hash, 0, 64);
        $error = substr((string)$error, 0, 128);
        $expected_identity_fingerprint = substr((string)$expected_identity_fingerprint, 0, 64);
        $source_sql = $source_hash !== '' ? "`avatar_source_hash` = '" . $this->db->escape($source_hash) . "'," : '';
        $identity_where = '';
        if ($expected_identity_fingerprint !== '') {
            $identity_needle = '%"identity_fingerprint":"' . $expected_identity_fingerprint . '"%';
            $identity_where = " AND `metadata_json` LIKE '" . $this->db->escape($identity_needle) . "'";
        }
        $this->db->query(
            "UPDATE `{$this->prefix}messenger_contacts`
             SET {$source_sql}
                 `avatar_checked_at` = NOW(),
                 `avatar_last_error` = '" . $this->db->escape($error) . "'
             WHERE `contact_id` = " . $contact_id . $identity_where
        );
    }

    public function getContactByChat($channel_id, $chat_id) {
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}messenger_contacts`
             WHERE `channel_id` = " . (int)$channel_id . " AND `chat_id` = " . (int)$chat_id . "
             ORDER BY `contact_id` DESC
             LIMIT 1"
        );
        return $query->row;
    }

    



    public function listSmsDialogPhoneNumbers(int $limit = 20001): array {
        $limit = max(1, min(50001, $limit));
        $query = $this->db->query(
            "SELECT c.`external_user_id`, MIN(c.`contact_id`) AS `first_contact_id`
             FROM `{$this->prefix}messenger_contacts` c
             INNER JOIN `{$this->prefix}messenger_channels` ch
                     ON ch.`channel_id` = c.`channel_id` AND ch.`provider` = 'sms'
             INNER JOIN `{$this->prefix}sessions` s
                     ON s.`chat_id` = c.`chat_id` AND s.`source_provider` = 'sms'
             WHERE c.`provider` = 'sms'
               AND c.`chat_id` IS NOT NULL
               AND c.`chat_id` > 0
               AND c.`external_user_id` != ''
             GROUP BY c.`external_user_id`
             ORDER BY `first_contact_id` ASC
             LIMIT " . (int)$limit
        );

        return array_values(array_map(static function (array $row): string {
            return (string)($row['external_user_id'] ?? '');
        }, $query->rows ?: []));
    }

    public function findDeliveryByDedupe($channel_id, $dedupe_key) {
        $dedupe_e = $this->db->escape($dedupe_key);
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}messenger_deliveries`
             WHERE `channel_id` = " . (int)$channel_id . " AND `dedupe_key` = '{$dedupe_e}'
             LIMIT 1"
        );
        return $query->row;
    }

    public function findDeliveryByExternalMessageId($channel_id, $external_message_id) {
        $external_message_id = trim((string)$external_message_id);
        if ((int)$channel_id <= 0 || $external_message_id === '') {
            return [];
        }
        $external_e = $this->db->escape($external_message_id);
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}messenger_deliveries`
             WHERE `channel_id` = " . (int)$channel_id . "
               AND `external_message_id` = '{$external_e}'
               AND `status` = 'sent'
             ORDER BY `delivery_id` ASC
             LIMIT 1"
        );
        return $query->row;
    }

    






    public function findWildberriesDispatchedMessageByCorrelation(
        int $channel_id,
        int $contact_id,
        array $correlation_ids
    ): array {
        if ($channel_id <= 0 || $contact_id <= 0) {
            return [];
        }
        $ids = [];
        foreach ($correlation_ids as $correlation_id) {
            $correlation_id = trim((string)$correlation_id);
            if ($correlation_id !== '') {
                $ids[$correlation_id] = "'" . $this->db->escape($correlation_id) . "'";
            }
        }
        if (!$ids) {
            return [];
        }

        $query = $this->db->query(
            "SELECT d.`delivery_id`, d.`message_id`, m.`chat_id`, m.`external_message_id`
             FROM `{$this->prefix}messenger_deliveries` d
             INNER JOIN `{$this->prefix}messages` m ON m.`message_id` = d.`message_id`
             WHERE d.`channel_id` = " . $channel_id . "
               AND d.`contact_id` = " . $contact_id . "
               AND d.`direction` = 'out'
               AND d.`status` = 'sent'
               AND d.`dedupe_key` = CONCAT('out_', d.`message_id`)
               AND m.`author` IN (2, 3)
               AND (
                   d.`external_message_id` IN (" . implode(',', $ids) . ")
                   OR m.`external_message_id` IN (" . implode(',', $ids) . ")
               )
             ORDER BY d.`delivery_id` DESC
             LIMIT 2"
        );
        $rows = $query->rows ?: [];
         
        return count($rows) === 1 ? $rows[0] : [];
    }

    






    public function findYandexMarketEchoCandidates(int $channel_id, int $contact_id, int $limit = 12): array {
        if ($channel_id <= 0 || $contact_id <= 0) {
            return [];
        }
        $limit = max(1, min(30, $limit));
        $query = $this->db->query(
            "SELECT d.`delivery_id`, d.`contact_id`, d.`message_id`, d.`date_added` AS `delivery_date`,
                    m.`chat_id`, m.`text`, m.`external_message_id`, m.`date_added` AS `message_date`
             FROM `{$this->prefix}messenger_deliveries` d
             INNER JOIN `{$this->prefix}messages` m ON m.`message_id` = d.`message_id`
             WHERE d.`channel_id` = " . $channel_id . "
               AND d.`contact_id` = " . $contact_id . "
               AND d.`direction` = 'out'
               AND d.`status` = 'sent'
               AND d.`last_error` = 'awaiting_provider_echo'
               AND d.`dedupe_key` = CONCAT('out_', d.`message_id`)
               AND d.`date_added` >= DATE_SUB(NOW(), INTERVAL 1 DAY)
             ORDER BY d.`delivery_id` DESC
             LIMIT " . $limit
        );
        $candidates = $query->rows ?: [];
        if (!$candidates) {
            return [];
        }
        $message_ids = array_values(array_unique(array_map(static function (array $row): int {
            return (int)($row['message_id'] ?? 0);
        }, $candidates)));
        $attachments = [];
        if ($message_ids) {
            $attachment_query = $this->db->query(
                "SELECT `message_id`, `file_name`, `file_size`, `meta_json`
                 FROM `{$this->prefix}message_attachments`
                 WHERE `message_id` IN (" . implode(',', $message_ids) . ")
                 ORDER BY `id` ASC"
            );
            foreach ($attachment_query->rows ?: [] as $attachment) {
                $message_id = (int)($attachment['message_id'] ?? 0);
                $attachments[$message_id][] = $attachment;
            }
        }
        foreach ($candidates as &$candidate) {
            $candidate['attachments'] = $attachments[(int)($candidate['message_id'] ?? 0)] ?? [];
        }
        unset($candidate);
        return $candidates;
    }

    





    public function countYandexMarketEchoParts(int $channel_id, int $message_id): int {
        if ($channel_id <= 0 || $message_id <= 0) {
            return 0;
        }
        $query = $this->db->query(
            "SELECT COUNT(*) AS `total`
             FROM `{$this->prefix}messenger_deliveries`
             WHERE `channel_id` = " . $channel_id . "
               AND `message_id` = " . $message_id . "
               AND `direction` = 'out'
               AND `status` = 'sent'
               AND LEFT(`dedupe_key`, 17) = 'provider-message:'
               AND `external_message_id` <> ''"
        );
        return max(0, (int)($query->row['total'] ?? 0));
    }

    



    public function getOutboundDeliveryStatesByMessageIds(array $message_ids): array {
        $ids = [];
        foreach ($message_ids as $message_id) {
            $message_id = (int)$message_id;
            if ($message_id > 0) {
                $ids[$message_id] = $message_id;
            }
        }
        if (!$ids) {
            return [];
        }

        $query = $this->db->query(
            "SELECT d.`message_id`, d.`dedupe_key`, d.`status`, d.`last_error`, d.`date_modified`, c.`provider`
             FROM `{$this->prefix}messenger_deliveries` d
             LEFT JOIN `{$this->prefix}messenger_channels` c ON c.`channel_id` = d.`channel_id`
             WHERE d.`direction` = 'out'
               AND d.`message_id` IN (" . implode(',', array_values($ids)) . ")
             ORDER BY d.`delivery_id` ASC"
        );

        $states = [];
        foreach ($query->rows ?: [] as $row) {
            $message_id = (int)($row['message_id'] ?? 0);
            if ($message_id <= 0 || (string)($row['dedupe_key'] ?? '') !== 'out_' . $message_id) {
                continue;
            }
            $status = strtolower((string)($row['status'] ?? ''));
            if (!in_array($status, ['pending', 'sent', 'failed'], true)) {
                continue;
            }
            $provider = strtolower((string)($row['provider'] ?? ''));
            $states[$message_id] = [
                'status' => $status,
                'provider' => $provider,
                'updated_at' => (string)($row['date_modified'] ?? ''),
            ];
            if ($provider === 'sms') {
                 
                 
                $safe_sms_codes = [
                    'sms_phone_invalid',
                    'sms_phone_rate_limited',
                    'sms_device_missing',
                    'sms_text_empty',
                    'sms_attachments_unsupported',
                    'trial_limit_exceeded',
                    'sms_dispatch_busy',
                    'sms_dispatch_storage_failed',
                    'sms_segment_limit_exceeded',
                    'appointment_cancelled',
                    'reminder_cancelled',
                    'cancelled_with_chat',
                    'deadline_expired',
                    'deadline_expired_after_reservation',
                ];
                $sms_code = strtolower(trim((string)($row['last_error'] ?? '')));
                if (in_array($sms_code, $safe_sms_codes, true)) {
                    $states[$message_id]['sms_code'] = $sms_code;
                }
            }
        }

        $sms_query = $this->db->query(
            "SELECT `job_id`, `message_id`, `status`, `last_error`, `last_stage`, `last_stage_code`,
                    `last_stage_at`, `delivery_status`, `reserved_at`, `date_added`, `date_modified`
             FROM `{$this->prefix}sms_jobs`
             WHERE `message_id` IN (" . implode(',', array_values($ids)) . ")
             ORDER BY `job_id` ASC"
        );
        foreach ($sms_query->rows ?: [] as $job) {
            $message_id = (int)($job['message_id'] ?? 0);
            if ($message_id <= 0) {
                continue;
            }
            $job_status = strtolower((string)($job['status'] ?? 'pending'));
            $delivery_status = strtolower((string)($job['delivery_status'] ?? 'not_requested'));
            $last_stage = strtolower((string)($job['last_stage'] ?? ''));
            $last_stage_code = strtolower((string)($job['last_stage_code'] ?? ''));
            if ($last_stage_code === '') $last_stage_code = strtolower((string)($job['last_error'] ?? ''));
            $wait_started_at = (string)(!empty($job['reserved_at']) ? $job['reserved_at'] : ($job['date_added'] ?? ''));
            $wait_started_ts = strtotime($wait_started_at);
            if (
                in_array($job_status, ['pending', 'reserved'], true)
                && in_array($last_stage, ['job_created', 'push_accepted', 'job_reserved'], true)
                && $wait_started_ts
                && time() - $wait_started_ts >= 120
            ) {
                 
                 
                $last_stage_code = 'phone_response_timeout';
            }
            $display_status = in_array($job_status, ['cancelled', 'expired', 'failed', 'permission_missing', 'sim_missing'], true)
                ? 'failed'
                : ($job_status === 'sent' ? 'sent' : 'pending');
            if (in_array($last_stage, ['push_failed', 'domain_mismatch', 'permission_missing', 'device_unavailable', 'license_blocked', 'modem_failed', 'carrier_delivery_failed'], true)) {
                $display_status = 'failed';
            } elseif ($last_stage === 'modem_accepted' || $last_stage_code === 'completion_pending_sent') {
                $display_status = 'sent';
            } elseif ($last_stage_code === 'completion_pending_failed') {
                $display_status = 'failed';
            }
            if ($delivery_status === 'delivered') {
                $display_status = 'delivered';
            } elseif ($delivery_status === 'failed') {
                $display_status = 'failed';
            }
            $states[$message_id] = [
                'status' => $display_status,
                'provider' => 'sms',
                'updated_at' => (string)($job['date_modified'] ?? ''),
                'sms_job_id' => (int)$job['job_id'],
                'sms_stage' => $last_stage,
                'sms_code' => $last_stage_code,
                'sms_delivery_status' => $delivery_status,
            ];
        }
        return $states;
    }

    public function createDelivery(array $data): int {
        $channel_id = (int)($data['channel_id'] ?? 0);
        $contact_id = isset($data['contact_id']) ? (int)$data['contact_id'] : 0;
        $message_id = isset($data['message_id']) ? (int)$data['message_id'] : 0;
        $direction = $this->db->escape((string)($data['direction'] ?? 'in'));
        $external_message_id = $this->db->escape((string)($data['external_message_id'] ?? ''));
        $dedupe_key = $this->db->escape((string)($data['dedupe_key'] ?? ''));
        $payload_hash = $this->db->escape((string)($data['payload_hash'] ?? ''));
        $provider_occurred_at = trim((string)($data['provider_occurred_at'] ?? ''));
        if (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $provider_occurred_at) !== 1) {
            $provider_occurred_at = '';
        }
        $provider_cursor = isset($data['provider_cursor']) && is_numeric($data['provider_cursor'])
            ? max(0, (int)$data['provider_cursor'])
            : 0;
        $ingest_mode = strtolower((string)($data['ingest_mode'] ?? 'live'));
        if (!in_array($ingest_mode, ['live', 'initial_history', 'reconciliation'], true)) {
            $ingest_mode = 'live';
        }
        $status = $this->db->escape((string)($data['status'] ?? 'pending'));
        $last_error = $this->db->escape((string)($data['last_error'] ?? ''));

        $this->db->query(
            "INSERT INTO `{$this->prefix}messenger_deliveries`
             SET `channel_id` = {$channel_id},
                 `contact_id` = " . ($contact_id > 0 ? $contact_id : 'NULL') . ",
                 `message_id` = " . ($message_id > 0 ? $message_id : 'NULL') . ",
                 `direction` = '{$direction}',
                 `external_message_id` = '{$external_message_id}',
                 `dedupe_key` = '{$dedupe_key}',
                 `payload_hash` = '{$payload_hash}',
                 `provider_occurred_at` = " . ($provider_occurred_at !== '' ? "'" . $this->db->escape($provider_occurred_at) . "'" : 'NULL') . ",
                 `provider_cursor` = " . ($provider_cursor > 0 ? $provider_cursor : 'NULL') . ",
                 `ingest_mode` = '" . $this->db->escape($ingest_mode) . "',
                 `status` = '{$status}',
                 `last_error` = '{$last_error}',
                 `date_added` = NOW(),
                 `date_modified` = NOW()"
        );
        return (int)$this->db->getLastId();
    }

     
    public function claimConversationStart(array $data): int {
        $channel_id = (int)($data['channel_id'] ?? 0);
        $provider = trim((string)($data['provider'] ?? ''));
        $dedupe_key = trim((string)($data['dedupe_key'] ?? ''));
        if ($channel_id <= 0 || $provider === '' || $dedupe_key === '') {
            return 0;
        }
        $event_at = trim((string)($data['event_at'] ?? ''));
        $this->db->query("INSERT IGNORE INTO `{$this->prefix}messenger_conversation_starts` SET
            `channel_id` = {$channel_id},
            `provider` = '" . $this->db->escape($provider) . "',
            `dedupe_key` = '" . $this->db->escape(mb_substr($dedupe_key, 0, 128)) . "',
            `start_payload` = '" . $this->db->escape(mb_substr((string)($data['start_payload'] ?? ''), 0, 128)) . "',
            `status` = 'processing', `processing_until` = DATE_ADD(NOW(), INTERVAL 90 SECOND),
            `event_at` = " . ($event_at !== '' ? "'" . $this->db->escape($event_at) . "'" : 'NULL') . ",
            `date_added` = NOW(), `date_modified` = NOW()");
        return (int)$this->db->countAffected() > 0 ? (int)$this->db->getLastId() : 0;
    }

    public function finishConversationStart(int $start_id, array $data): void {
        if ($start_id <= 0) return;
        $numeric = ['contact_id', 'chat_id', 'boundary_message_id', 'greeting_message_id', 'notice_message_id'];
        $parts = [];
        foreach (array_merge($numeric, ['status', 'last_error']) as $key) {
            if (!array_key_exists($key, $data)) continue;
            $parts[] = in_array($key, $numeric, true)
                ? "`{$key}` = " . max(0, (int)$data[$key])
                : "`{$key}` = '" . $this->db->escape((string)$data[$key]) . "'";
        }
        $parts[] = "`processing_until` = NULL";
        $parts[] = "`date_modified` = NOW()";
        $this->db->query("UPDATE `{$this->prefix}messenger_conversation_starts` SET " . implode(', ', $parts) . " WHERE `start_id` = " . $start_id);
    }

    




    public function rememberSentProviderMessageId(array $data): void {
        $channel_id = (int)($data['channel_id'] ?? 0);
        $external_message_id = trim((string)($data['external_message_id'] ?? ''));
        $dedupe_key = trim((string)($data['dedupe_key'] ?? ''));
        if ($channel_id <= 0 || $external_message_id === '' || $dedupe_key === '') {
            return;
        }

        $contact_id = (int)($data['contact_id'] ?? 0);
        $message_id = (int)($data['message_id'] ?? 0);
        $external_e = $this->db->escape($external_message_id);
        $dedupe_e = $this->db->escape($dedupe_key);
        $payload_hash_e = $this->db->escape((string)($data['payload_hash'] ?? ''));
        $this->db->query(
            "INSERT INTO `{$this->prefix}messenger_deliveries`
             SET `channel_id` = {$channel_id},
                 `contact_id` = " . ($contact_id > 0 ? $contact_id : 'NULL') . ",
                 `message_id` = " . ($message_id > 0 ? $message_id : 'NULL') . ",
                 `direction` = 'out',
                 `external_message_id` = '{$external_e}',
                 `dedupe_key` = '{$dedupe_e}',
                 `payload_hash` = '{$payload_hash_e}',
                 `status` = 'sent',
                 `last_error` = '',
                 `date_added` = NOW(),
                 `date_modified` = NOW()
             ON DUPLICATE KEY UPDATE
                 `message_id` = VALUES(`message_id`),
                 `external_message_id` = VALUES(`external_message_id`),
                 `status` = 'sent',
                 `last_error` = '',
                 `date_modified` = NOW()"
        );
    }

    public function updateDelivery($delivery_id, array $data): void {
        $parts = [];
        $allowed = ['attempts', 'provider_cursor', 'status', 'external_message_id', 'last_error'];
        foreach ($data as $key => $value) {
            if (!in_array($key, $allowed, true)) {
                continue;
            }
            if ($key === 'attempts') {
                $parts[] = "`attempts` = " . max(0, (int)$value);
                continue;
            }
            if ($key === 'provider_cursor') {
                $parts[] = "`provider_cursor` = " . ((int)$value > 0 ? (int)$value : 'NULL');
                continue;
            }
            $parts[] = "`" . $this->db->escape($key) . "` = '" . $this->db->escape((string)$value) . "'";
        }
        if (!$parts) {
            return;
        }
        $parts[] = "`date_modified` = NOW()";
        $this->db->query("UPDATE `{$this->prefix}messenger_deliveries` SET " . implode(', ', $parts) . " WHERE `delivery_id` = " . (int)$delivery_id);

         
         
         
        if (array_key_exists('status', $data)) {
            $this->db->query(
                "UPDATE `{$this->prefix}messages`
                 SET `date_modified` = NOW()
                 WHERE `message_id` = (
                     SELECT d.`message_id`
                     FROM `{$this->prefix}messenger_deliveries` d
                     WHERE d.`delivery_id` = " . (int)$delivery_id . "
                     LIMIT 1
                 )"
            );
        }
    }

     
    public function updateSmsDeliveryMonotonic(
        int $delivery_id,
        string $status,
        string $last_error = '',
        bool $confirmed_delivered = false
    ): void {
        if ($delivery_id <= 0) return;
        $status = in_array($status, ['pending', 'sent', 'failed'], true) ? $status : 'pending';
        $status_e = $this->db->escape($status);
        $error_e = $this->db->escape((string)preg_replace('/[^a-z0-9._-]/i', '_', substr($last_error, 0, 64)));
        $allow_delivery_repair = $confirmed_delivered && $status === 'sent' ? 1 : 0;
        $preserve = "((`status` = 'failed' AND {$allow_delivery_repair} = 0) OR (`status` = 'sent' AND '{$status_e}' = 'pending'))";
         
         
        $this->db->query(
            "UPDATE `{$this->prefix}messenger_deliveries`
             SET `last_error` = IF({$preserve}, `last_error`, '{$error_e}'),
                 `status` = IF({$preserve}, `status`, '{$status_e}'),
                 `date_modified` = NOW()
             WHERE `delivery_id` = " . (int)$delivery_id
        );
        $this->db->query(
            "UPDATE `{$this->prefix}messages`
             SET `date_modified` = NOW()
             WHERE `message_id` = (
                 SELECT d.`message_id` FROM `{$this->prefix}messenger_deliveries` d
                 WHERE d.`delivery_id` = " . (int)$delivery_id . " LIMIT 1
             )"
        );
    }
}
