<?php
namespace EasyChat\Model;

class MessageContext extends Base {

    public static function tableSql($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}messenger_message_contexts` (
            `context_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `provider` VARCHAR(32) NOT NULL DEFAULT '',
            `channel_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `chat_id` INT(11) NOT NULL,
            `message_id` INT(11) NOT NULL,
            `context_type` VARCHAR(32) NOT NULL DEFAULT 'avito_item',
            `external_chat_id` VARCHAR(128) NOT NULL DEFAULT '',
            `external_user_id` VARCHAR(128) NOT NULL DEFAULT '',
            `external_message_id` VARCHAR(128) NOT NULL DEFAULT '',
            `item_id` VARCHAR(64) NOT NULL DEFAULT '',
            `chat_type` VARCHAR(16) NOT NULL DEFAULT '',
            `title` VARCHAR(255) NOT NULL DEFAULT '',
            `url` VARCHAR(768) NOT NULL DEFAULT '',
            `price_string` VARCHAR(128) NOT NULL DEFAULT '',
            `image_url` VARCHAR(768) NOT NULL DEFAULT '',
            `status_id` VARCHAR(32) NOT NULL DEFAULT '',
            `snapshot_json` TEXT NOT NULL,
            `is_marker` TINYINT(1) NOT NULL DEFAULT 0,
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            UNIQUE KEY `uniq_message_context` (`message_id`, `context_type`),
            KEY `idx_chat_context` (`chat_id`, `context_type`, `message_id`),
            KEY `idx_provider_item` (`provider`, `item_id`),
            KEY `idx_provider_channel_item_message` (`provider`, `channel_id`, `item_id`, `message_id`),
            KEY `idx_external_message` (`channel_id`, `external_message_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    }

    public function upsertAvitoItemContext(array $data): array {
        return $this->upsertMarketplaceItemContext('avito', 'avito_item', $data);
    }

    public function upsertYoulaItemContext(array $data): array {
        return $this->upsertMarketplaceItemContext('youla', 'youla_item', $data);
    }

    public function upsertMarketplaceItemContext(string $provider, string $context_type, array $data): array {
        $provider = $this->normalizeProvider($provider);
        $context_type = $this->normalizeContextType($context_type);
        $chat_id = (int)($data['chat_id'] ?? 0);
        $message_id = (int)($data['message_id'] ?? 0);
        $item_id = $this->limit((string)($data['item_id'] ?? ''), 64);
        $snapshot = is_array($data['snapshot'] ?? null) ? $data['snapshot'] : [];
        if ($item_id === '') {
            $item_id = $this->limit((string)($snapshot['id'] ?? $snapshot['item_id'] ?? ''), 64);
        }
        if ($provider === '' || $context_type === '' || $chat_id <= 0 || $message_id <= 0 || $item_id === '') {
            return [];
        }

        $title = $this->limit((string)($data['title'] ?? $snapshot['title'] ?? ''), 255);
        $url = $this->limit((string)($data['url'] ?? $snapshot['url'] ?? $snapshot['item_url'] ?? ''), 768);
        $price = $this->limit((string)($data['price_string'] ?? $snapshot['price_string'] ?? ''), 128);
        $image_url = $this->limit((string)($data['image_url'] ?? $snapshot['image_url'] ?? ''), 768);
        if ($image_url === '' && isset($snapshot['images']['main']) && is_array($snapshot['images']['main'])) {
            $image_url = $this->limit($this->largestImageUrl($snapshot['images']['main']), 768);
        }
        $status_id = $this->limit((string)($data['status_id'] ?? $snapshot['status_id'] ?? ''), 32);

        $snapshot = array_filter([
            'id' => $item_id,
            'title' => $title,
            'url' => $url,
            'price_string' => $price,
            'image_url' => $image_url,
            'status_id' => $status_id,
            'raw' => $snapshot,
        ], function ($value) {
            return $value !== '' && $value !== [] && $value !== null;
        });

        $existing = $this->getByMessageId($message_id, $context_type);
        $is_marker = $existing ? (int)$existing['is_marker'] : $this->isNewMarketplaceMarker($chat_id, $message_id, $item_id, $context_type);

        $fields = [
            'provider' => $provider,
            'channel_id' => (int)($data['channel_id'] ?? 0),
            'chat_id' => $chat_id,
            'message_id' => $message_id,
            'context_type' => $context_type,
            'external_chat_id' => $this->limit((string)($data['external_chat_id'] ?? ''), 128),
            'external_user_id' => $this->limit((string)($data['external_user_id'] ?? ''), 128),
            'external_message_id' => $this->limit((string)($data['external_message_id'] ?? ''), 128),
            'item_id' => $item_id,
            'chat_type' => $this->limit((string)($data['chat_type'] ?? ''), 16),
            'title' => $title,
            'url' => $url,
            'price_string' => $price,
            'image_url' => $image_url,
            'status_id' => $status_id,
            'snapshot_json' => json_encode($snapshot, JSON_UNESCAPED_UNICODE),
            'is_marker' => $is_marker,
        ];

        if ($existing) {
            $parts = [];
            foreach ($fields as $key => $value) {
                if (in_array($key, ['chat_id', 'message_id'], true)) {
                    $parts[] = "`{$key}` = " . (int)$value;
                } else {
                    $parts[] = "`{$key}` = '" . $this->db->escape((string)$value) . "'";
                }
            }
            $parts[] = "`date_modified` = NOW()";
            $this->db->query("UPDATE `{$this->prefix}messenger_message_contexts` SET " . implode(', ', $parts) . " WHERE `context_id` = " . (int)$existing['context_id']);
        } else {
            $this->db->query(
                "INSERT INTO `{$this->prefix}messenger_message_contexts`
                 SET `provider` = '" . $this->db->escape($fields['provider']) . "',
                     `channel_id` = " . (int)$fields['channel_id'] . ",
                     `chat_id` = " . (int)$fields['chat_id'] . ",
                     `message_id` = " . (int)$fields['message_id'] . ",
                     `context_type` = '" . $this->db->escape($fields['context_type']) . "',
                     `external_chat_id` = '" . $this->db->escape($fields['external_chat_id']) . "',
                     `external_user_id` = '" . $this->db->escape($fields['external_user_id']) . "',
                     `external_message_id` = '" . $this->db->escape($fields['external_message_id']) . "',
                     `item_id` = '" . $this->db->escape($fields['item_id']) . "',
                     `chat_type` = '" . $this->db->escape($fields['chat_type']) . "',
                     `title` = '" . $this->db->escape($fields['title']) . "',
                     `url` = '" . $this->db->escape($fields['url']) . "',
                     `price_string` = '" . $this->db->escape($fields['price_string']) . "',
                     `image_url` = '" . $this->db->escape($fields['image_url']) . "',
                     `status_id` = '" . $this->db->escape($fields['status_id']) . "',
                     `snapshot_json` = '" . $this->db->escape($fields['snapshot_json']) . "',
                     `is_marker` = " . (int)$fields['is_marker'] . ",
                     `date_added` = NOW(),
                     `date_modified` = NOW()"
            );
        }

        return $this->getByMessageId($message_id, $context_type);
    }

    public function upsertAvitoGeneralContext(array $data): array {
        $chat_id = (int)($data['chat_id'] ?? 0);
        $message_id = (int)($data['message_id'] ?? 0);
        if ($chat_id <= 0 || $message_id <= 0) {
            return [];
        }

        $snapshot = is_array($data['snapshot'] ?? null) ? $data['snapshot'] : [];
        $title = $this->limit((string)($data['title'] ?? $snapshot['title'] ?? 'Общий запрос Авито'), 255);
        $url = $this->limit((string)($data['url'] ?? $snapshot['url'] ?? ''), 768);
        $chat_type = $this->limit((string)($data['chat_type'] ?? $snapshot['chat_type'] ?? 'u2u'), 16);

        $snapshot = array_filter([
            'context_kind' => 'general',
            'chat_type' => $chat_type,
            'title' => $title,
            'url' => $url,
        ], function ($value) {
            return $value !== '' && $value !== [] && $value !== null;
        });

        $existing = $this->getByMessageId($message_id, 'avito_general');
        $fields = [
            'provider' => 'avito',
            'channel_id' => (int)($data['channel_id'] ?? 0),
            'chat_id' => $chat_id,
            'message_id' => $message_id,
            'context_type' => 'avito_general',
            'external_chat_id' => $this->limit((string)($data['external_chat_id'] ?? ''), 128),
            'external_user_id' => $this->limit((string)($data['external_user_id'] ?? ''), 128),
            'external_message_id' => $this->limit((string)($data['external_message_id'] ?? ''), 128),
            'item_id' => '',
            'chat_type' => $chat_type,
            'title' => $title,
            'url' => $url,
            'price_string' => '',
            'image_url' => '',
            'status_id' => '',
            'snapshot_json' => json_encode($snapshot, JSON_UNESCAPED_UNICODE),
            'is_marker' => $existing ? (int)$existing['is_marker'] : $this->isNewGeneralMarker($chat_id, $message_id),
        ];

        if ($existing) {
            $parts = [];
            foreach ($fields as $key => $value) {
                if (in_array($key, ['chat_id', 'message_id'], true)) {
                    $parts[] = "`{$key}` = " . (int)$value;
                } else {
                    $parts[] = "`{$key}` = '" . $this->db->escape((string)$value) . "'";
                }
            }
            $parts[] = "`date_modified` = NOW()";
            $this->db->query("UPDATE `{$this->prefix}messenger_message_contexts` SET " . implode(', ', $parts) . " WHERE `context_id` = " . (int)$existing['context_id']);
        } else {
            $this->db->query(
                "INSERT INTO `{$this->prefix}messenger_message_contexts`
                 SET `provider` = '" . $this->db->escape($fields['provider']) . "',
                     `channel_id` = " . (int)$fields['channel_id'] . ",
                     `chat_id` = " . (int)$fields['chat_id'] . ",
                     `message_id` = " . (int)$fields['message_id'] . ",
                     `context_type` = '" . $this->db->escape($fields['context_type']) . "',
                     `external_chat_id` = '" . $this->db->escape($fields['external_chat_id']) . "',
                     `external_user_id` = '" . $this->db->escape($fields['external_user_id']) . "',
                     `external_message_id` = '" . $this->db->escape($fields['external_message_id']) . "',
                     `item_id` = '',
                     `chat_type` = '" . $this->db->escape($fields['chat_type']) . "',
                     `title` = '" . $this->db->escape($fields['title']) . "',
                     `url` = '" . $this->db->escape($fields['url']) . "',
                     `price_string` = '',
                     `image_url` = '',
                     `status_id` = '',
                     `snapshot_json` = '" . $this->db->escape($fields['snapshot_json']) . "',
                     `is_marker` = " . (int)$fields['is_marker'] . ",
                     `date_added` = NOW(),
                     `date_modified` = NOW()"
            );
        }

        return $this->getByMessageId($message_id, 'avito_general');
    }

    public function getByMessageId($message_id, string $context_type = 'avito_item'): array {
        $query = $this->db->query(
            "SELECT *
             FROM `{$this->prefix}messenger_message_contexts`
             WHERE `message_id` = " . (int)$message_id . "
               AND `context_type` = '" . $this->db->escape($context_type) . "'
             LIMIT 1"
        );
        return $query->row ?: [];
    }

    public function upsertMaxMessageContext(array $data): array {
        return $this->upsertMessengerMessageContext('max', $data);
    }

    public function upsertVkMessageContext(array $data): array {
        return $this->upsertMessengerMessageContext('vk', $data);
    }

    public static function messengerContextTypes(): array {
        return ['max_message', 'whatsapp_personal_message', 'vk_message', 'telegram_message', 'max_bot_message'];
    }

    




    public function upsertMessengerMessageContext(string $provider, array $data): array {
        $provider = $this->normalizeProvider($provider);
        $context_types = [
            'max' => 'max_message',
            'whatsapp-personal' => 'whatsapp_personal_message',
            'vk' => 'vk_message',
            'telegram' => 'telegram_message',
            'max-bot' => 'max_bot_message',
        ];
        if (!isset($context_types[$provider])) {
            return [];
        }
        $context_type = $context_types[$provider];
        $chat_id = (int)($data['chat_id'] ?? 0);
        $message_id = (int)($data['message_id'] ?? 0);
        if ($chat_id <= 0 || $message_id <= 0) {
            return [];
        }

        $sender_name = $this->limit((string)($data['sender_display_name'] ?? ''), 120);
        $chat_name = $this->limit((string)($data['chat_display_name'] ?? ''), 120);
        $forward_source_name = $this->limit((string)($data['forward_source_name'] ?? ''), 120);
        $forward_source_type = $this->limit($this->normalizeContextType((string)($data['forward_source_type'] ?? '')), 32);
        $forwarded = !empty($data['forwarded']);
        $system_event_type = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', (string)($data['system_event_type'] ?? '')));
        if (!in_array($system_event_type, ['max_member_joined', 'max_member_left', 'max_group_created', 'max_group_updated'], true)) {
            $system_event_type = '';
        }
        $system_event_actor_names = [];
        if ($system_event_type !== '' && is_array($data['system_event_actor_names'] ?? null)) {
            foreach (array_slice($data['system_event_actor_names'], 0, 4) as $actor_name) {
                $actor_name = $this->limit((string)$actor_name, 120);
                if ($actor_name !== '') {
                    $system_event_actor_names[$actor_name] = $actor_name;
                }
            }
            $system_event_actor_names = array_values($system_event_actor_names);
        }
         
         
        $system_event_actor_count = $system_event_type !== ''
            ? min(10000, max(count($system_event_actor_names), (int)($data['system_event_actor_count'] ?? 0)))
            : 0;
        $sender_resolution_status = strtolower(trim((string)($data['sender_resolution_status'] ?? '')));
        if ($sender_name !== '') {
            $sender_resolution_status = 'resolved';
        } elseif ($sender_resolution_status !== 'provider_unavailable') {
            $sender_resolution_status = '';
        }
        if ($sender_name === '' && $chat_name === '' && !$forwarded && $sender_resolution_status === '' && $system_event_type === '') {
            return [];
        }

        $snapshot = [
            'context_kind' => 'messenger_message',
            'sender_display_name' => $sender_name,
            'chat_display_name' => $chat_name,
            'forwarded' => $forwarded ? 1 : 0,
            'forward_source_name' => $forward_source_name,
            'forward_source_type' => $forward_source_type,
            'sender_resolution_status' => $sender_resolution_status,
            'system_event_type' => $system_event_type,
            'system_event_actor_names' => $system_event_actor_names,
            'system_event_actor_count' => $system_event_actor_count,
        ];
        $fields = [
            'provider' => $provider,
            'channel_id' => (int)($data['channel_id'] ?? 0),
            'chat_id' => $chat_id,
            'message_id' => $message_id,
            'context_type' => $context_type,
            'external_chat_id' => $this->limit((string)($data['external_chat_id'] ?? ''), 128),
            'external_user_id' => $this->limit((string)($data['external_user_id'] ?? ''), 128),
            'external_message_id' => $this->limit((string)($data['external_message_id'] ?? ''), 128),
            'item_id' => '',
            'chat_type' => $this->limit(strtoupper((string)($data['chat_type'] ?? '')), 16),
            'title' => $sender_name,
            'url' => '',
            'price_string' => '',
            'image_url' => '',
            'status_id' => $forwarded ? 'forwarded' : '',
            'snapshot_json' => json_encode($snapshot, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
            'is_marker' => 0,
        ];
        if (!is_string($fields['snapshot_json'])) {
            $fields['snapshot_json'] = '{}';
        }

        $existing = $this->getByMessageId($message_id, $context_type);
        if ($existing) {
            $changed = false;
            foreach ($fields as $key => $value) {
                if ((string)($existing[$key] ?? '') !== (string)$value) {
                    $changed = true;
                    break;
                }
            }
            if (!$changed) {
                 
                 
                 
                $this->publishParentContextRevision($message_id, (string)($existing['date_modified'] ?? ''));
                return $existing;
            }

            $parts = [];
            foreach ($fields as $key => $value) {
                if (in_array($key, ['channel_id', 'chat_id', 'message_id', 'is_marker'], true)) {
                    $parts[] = "`{$key}` = " . (int)$value;
                } else {
                    $parts[] = "`{$key}` = '" . $this->db->escape((string)$value) . "'";
                }
            }
            $parts[] = "`date_modified` = NOW()";
            $this->db->query("UPDATE `{$this->prefix}messenger_message_contexts` SET " . implode(', ', $parts) . " WHERE `context_id` = " . (int)$existing['context_id']);
        } else {
            $this->db->query(
                "INSERT INTO `{$this->prefix}messenger_message_contexts`
                 SET `provider` = '" . $this->db->escape($fields['provider']) . "',
                     `channel_id` = " . (int)$fields['channel_id'] . ",
                     `chat_id` = " . (int)$fields['chat_id'] . ",
                     `message_id` = " . (int)$fields['message_id'] . ",
                     `context_type` = '" . $this->db->escape($fields['context_type']) . "',
                     `external_chat_id` = '" . $this->db->escape($fields['external_chat_id']) . "',
                     `external_user_id` = '" . $this->db->escape($fields['external_user_id']) . "',
                     `external_message_id` = '" . $this->db->escape($fields['external_message_id']) . "',
                     `item_id` = '',
                     `chat_type` = '" . $this->db->escape($fields['chat_type']) . "',
                     `title` = '" . $this->db->escape($fields['title']) . "',
                     `url` = '',
                     `price_string` = '',
                     `image_url` = '',
                     `status_id` = '" . $this->db->escape($fields['status_id']) . "',
                     `snapshot_json` = '" . $this->db->escape($fields['snapshot_json']) . "',
                     `is_marker` = 0,
                     `date_added` = NOW(),
                     `date_modified` = NOW()"
            );
        }

        $stored = $this->getByMessageId($message_id, $context_type);
        if ($stored) {
            $this->publishParentContextRevision($message_id, (string)($stored['date_modified'] ?? ''));
        }
        return $stored;
    }

    






    private function publishParentContextRevision(int $message_id, string $context_modified): void {
        if ($message_id <= 0 || trim($context_modified) === '') {
            return;
        }

        $context_modified = $this->db->escape($context_modified);
        $this->db->query(
            "UPDATE `{$this->prefix}messages`
             SET `date_modified` = NOW()
             WHERE `message_id` = " . (int)$message_id . "
               AND (`date_modified` IS NULL OR `date_modified` < '{$context_modified}')"
        );
    }

    public function hasRecentMessagesWithoutContext(int $chat_id, string $context_type, int $limit = 40): bool {
        $chat_id = max(0, $chat_id);
        $context_type = $this->normalizeContextType($context_type);
        $limit = max(1, min(100, $limit));
        if ($chat_id <= 0 || $context_type === '') {
            return false;
        }

        $query = $this->db->query(
            "SELECT recent.`message_id`
             FROM (
                 SELECT `message_id`
                 FROM `{$this->prefix}messages`
                 WHERE `chat_id` = " . $chat_id . "
                   AND `source_provider` = 'max'
                 ORDER BY `message_id` DESC
                 LIMIT " . $limit . "
             ) recent
             LEFT JOIN `{$this->prefix}messenger_message_contexts` ctx
                    ON ctx.`message_id` = recent.`message_id`
                   AND ctx.`context_type` = '" . $this->db->escape($context_type) . "'
             WHERE ctx.`context_id` IS NULL
             LIMIT 1"
        );
        return !empty($query->row);
    }

    public function getRecentMessagesWithoutContext(int $chat_id, string $context_type, int $limit = 40): array {
        $chat_id = max(0, $chat_id);
        $context_type = $this->normalizeContextType($context_type);
        $limit = max(1, min(100, $limit));
        if ($chat_id <= 0 || $context_type === '') {
            return [];
        }

        $query = $this->db->query(
            "SELECT recent.`message_id`, recent.`external_message_id`
             FROM (
                 SELECT `message_id`, `external_message_id`
                 FROM `{$this->prefix}messages`
                 WHERE `chat_id` = " . $chat_id . "
                   AND `source_provider` = 'max'
                 ORDER BY `message_id` DESC
                 LIMIT " . $limit . "
             ) recent
             LEFT JOIN `{$this->prefix}messenger_message_contexts` ctx
                    ON ctx.`message_id` = recent.`message_id`
                   AND ctx.`context_type` = '" . $this->db->escape($context_type) . "'
             WHERE ctx.`context_id` IS NULL
               AND recent.`external_message_id` <> ''
             ORDER BY recent.`message_id` ASC"
        );
        return $query->rows ?: [];
    }

    public function listRecentChatsWithoutContext(
        string $provider,
        int $channel_id,
        string $context_type,
        int $per_chat_limit = 40,
        int $limit = 8
    ): array {
        $provider = $this->normalizeProvider($provider);
        $context_type = $this->normalizeContextType($context_type);
        $channel_id = max(0, $channel_id);
        $per_chat_limit = max(1, min(100, $per_chat_limit));
        $limit = max(1, min(50, $limit));
        if ($provider === '' || $channel_id <= 0 || $context_type === '') {
            return [];
        }

        $provider_e = $this->db->escape($provider);
        $context_type_e = $this->db->escape($context_type);
        $query = $this->db->query(
            "SELECT s.`chat_id`, mc.`external_chat_id`
             FROM `{$this->prefix}sessions` s
             INNER JOIN `{$this->prefix}messenger_contacts` mc
                     ON mc.`contact_id` = s.`external_contact_id`
             WHERE s.`source_provider` = '{$provider_e}'
               AND mc.`provider` = '{$provider_e}'
               AND mc.`channel_id` = " . $channel_id . "
               AND mc.`external_chat_id` <> ''
               AND (
                    mc.`metadata_json` LIKE '%\"chat_type\":\"CHAT\"%'
                    OR mc.`metadata_json` LIKE '%\"chat_type\":\"CHANNEL\"%'
               )
               AND EXISTS (
                    SELECT 1
                    FROM `{$this->prefix}messages` missing
                    LEFT JOIN `{$this->prefix}messenger_message_contexts` ctx
                           ON ctx.`message_id` = missing.`message_id`
                          AND ctx.`context_type` = '{$context_type_e}'
                    WHERE missing.`chat_id` = s.`chat_id`
                      AND missing.`source_provider` = '{$provider_e}'
                      AND ctx.`context_id` IS NULL
                      AND (
                          SELECT COUNT(*)
                          FROM `{$this->prefix}messages` newer
                          WHERE newer.`chat_id` = missing.`chat_id`
                            AND newer.`source_provider` = '{$provider_e}'
                            AND newer.`message_id` > missing.`message_id`
                      ) < " . $per_chat_limit . "
                    LIMIT 1
               )
             ORDER BY mc.`last_seen_at` DESC, s.`chat_id` DESC
             LIMIT " . $limit
        );
        return $query->rows ?: [];
    }

     
    public function upsertOfficialSupportContext(int $chat_id, int $message_id, array $metadata): array {
        if ($chat_id <= 0 || $message_id <= 0) return [];
        $sources = [];
        foreach (array_slice(is_array($metadata['sources'] ?? null) ? $metadata['sources'] : [], 0, 4) as $source) {
            if (!is_array($source)) continue;
            $url = trim((string)($source['url'] ?? ''));
            $parts = parse_url($url);
            if (!$this->isSafeOfficialDocsUrl($parts)) continue;
            $sources[] = [
                'id' => preg_match('/^S[1-4]$/D', (string)($source['id'] ?? '')) ? (string)$source['id'] : 'S' . (count($sources) + 1),
                'title' => $this->limit((string)($source['title'] ?? 'Документация EasyChat'), 160),
                'section' => $this->limit((string)($source['section'] ?? ''), 160),
                'url' => $this->limit($url, 768),
            ];
        }
        $state = in_array((string)($metadata['state'] ?? ''), ['answer', 'clarify', 'handoff'], true)
            ? (string)$metadata['state'] : 'answer';
        $handoff = is_array($metadata['handoff'] ?? null) && !empty($metadata['handoff']['show'])
            ? [
                'show' => true,
                'telegram_url' => $this->safeSupportUrl((string)($metadata['handoff']['telegram_url'] ?? ''), ['t.me']),
                'max_url' => $this->safeSupportUrl((string)($metadata['handoff']['max_url'] ?? ''), ['max.ru']),
            ]
            : ['show' => false, 'telegram_url' => '', 'max_url' => ''];
        $snapshot = json_encode([
            'context_kind' => 'official_support',
            'state' => $state,
            'sources' => $sources,
            'handoff' => $handoff,
        ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        $existing = $this->getByMessageId($message_id, 'official_support');
        if ($existing) {
            $this->db->query(
                "UPDATE `{$this->prefix}messenger_message_contexts`
                 SET `snapshot_json` = '" . $this->db->escape($snapshot) . "', `date_modified` = NOW()
                 WHERE `context_id` = " . (int)$existing['context_id']
            );
        } else {
            $this->db->query(
                "INSERT INTO `{$this->prefix}messenger_message_contexts`
                 SET `provider` = 'widget', `channel_id` = 0,
                     `chat_id` = " . $chat_id . ", `message_id` = " . $message_id . ",
                     `context_type` = 'official_support', `external_chat_id` = '',
                     `external_user_id` = '', `external_message_id` = '', `item_id` = '',
                     `chat_type` = 'support', `title` = '', `url` = '', `price_string` = '',
                     `image_url` = '', `status_id` = '',
                     `snapshot_json` = '" . $this->db->escape($snapshot) . "', `is_marker` = 0,
                     `date_added` = NOW(), `date_modified` = NOW()"
            );
        }
        return $this->getByMessageId($message_id, 'official_support');
    }

    public function getOfficialSupportContextsByMessageIds(array $message_ids): array {
        $ids = array_values(array_unique(array_filter(array_map('intval', $message_ids))));
        if (!$ids) return [];
        $query = $this->db->query(
            "SELECT `message_id`, `snapshot_json`
             FROM `{$this->prefix}messenger_message_contexts`
             WHERE `message_id` IN (" . implode(',', $ids) . ")
               AND `context_type` = 'official_support'"
        );
        $result = [];
        foreach ($query->rows as $row) {
            $snapshot = json_decode((string)$row['snapshot_json'], true);
            if (!is_array($snapshot)) continue;
            $sources = [];
            foreach (array_slice(is_array($snapshot['sources'] ?? null) ? $snapshot['sources'] : [], 0, 4) as $source) {
                if (!is_array($source)) continue;
                $url = trim((string)($source['url'] ?? ''));
                $parts = parse_url($url);
                if (!$this->isSafeOfficialDocsUrl($parts)) continue;
                $sources[] = [
                    'id' => preg_match('/^S[1-4]$/D', (string)($source['id'] ?? '')) ? (string)$source['id'] : 'S' . (count($sources) + 1),
                    'title' => $this->limit((string)($source['title'] ?? 'Документация EasyChat'), 160),
                    'section' => $this->limit((string)($source['section'] ?? ''), 160),
                    'url' => $this->limit($url, 768),
                ];
            }
            $handoff = is_array($snapshot['handoff'] ?? null) ? $snapshot['handoff'] : [];
            $result[(int)$row['message_id']] = [
                'state' => in_array((string)($snapshot['state'] ?? ''), ['answer', 'clarify', 'handoff'], true) ? (string)$snapshot['state'] : 'answer',
                'sources' => $sources,
                'handoff' => [
                    'show' => !empty($handoff['show']),
                    'telegram_url' => $this->safeSupportUrl((string)($handoff['telegram_url'] ?? ''), ['t.me']),
                    'max_url' => $this->safeSupportUrl((string)($handoff['max_url'] ?? ''), ['max.ru']),
                ],
            ];
        }
        return $result;
    }

    public function countRecentUnresolvedOfficialSupport(int $chat_id, int $limit = 2): int {
        if ($chat_id <= 0) return 0;
        $limit = max(1, min(3, $limit));
        $query = $this->db->query(
            "SELECT `snapshot_json` FROM `{$this->prefix}messenger_message_contexts`
             WHERE `chat_id` = {$chat_id} AND `context_type` = 'official_support'
             ORDER BY `message_id` DESC LIMIT {$limit}"
        );
        $count = 0;
        foreach (($query && is_array($query->rows)) ? $query->rows : [] as $row) {
            $snapshot = json_decode((string)($row['snapshot_json'] ?? ''), true);
            $state = is_array($snapshot) ? (string)($snapshot['state'] ?? '') : '';
            if (!in_array($state, ['clarify', 'handoff'], true)) break;
            $count++;
        }
        return $count;
    }

    private function isSafeOfficialDocsUrl($parts): bool {
        return is_array($parts)
            && strtolower((string)($parts['scheme'] ?? '')) === 'https'
            && strtolower((string)($parts['host'] ?? '')) === 'docs.easychat24.ru'
            && !isset($parts['user'])
            && !isset($parts['pass'])
            && (!isset($parts['port']) || (int)$parts['port'] === 443)
            && !isset($parts['query']);
    }

    public function upsertSitePageContext(array $data): array {
        $chat_id = (int)($data['chat_id'] ?? 0);
        $message_id = (int)($data['message_id'] ?? 0);
        if ($chat_id <= 0 || $message_id <= 0) {
            return [];
        }

        $snapshot = is_array($data['snapshot'] ?? null) ? $data['snapshot'] : [];
        $page = is_array($snapshot['page'] ?? null) ? $snapshot['page'] : [];
        $cms = is_array($snapshot['cms'] ?? null) ? $snapshot['cms'] : [];

        $page_type = $this->normalizePageType((string)($data['page_type'] ?? $page['type'] ?? 'page'));
        $page_id = $this->limit((string)($data['page_id'] ?? $page['id'] ?? ''), 64);
        $title = $this->limit((string)($data['title'] ?? $page['title'] ?? $snapshot['title'] ?? ''), 255);
        $url = $this->limit((string)($data['url'] ?? $page['url'] ?? $snapshot['url'] ?? ''), 768);
        $canonical_url = $this->limit((string)($data['canonical_url'] ?? $page['canonical_url'] ?? ''), 768);
        $cms_provider = $this->limit($this->normalizeCmsProvider((string)($data['cms_provider'] ?? $cms['provider'] ?? '')), 32);

        if ($title === '' && $url === '' && $page_id === '') {
            return [];
        }

        $normalized_snapshot = array_filter([
            'context_kind' => 'site_page',
            'locale' => (string)($data['locale'] ?? $snapshot['locale'] ?? '') === 'en' ? 'en' : 'ru',
            'page' => array_filter([
                'type' => $page_type,
                'id' => $page_id,
                'title' => $title,
                'url' => $url,
                'canonical_url' => $canonical_url,
            ], function ($value) {
                return $value !== '' && $value !== [] && $value !== null;
            }),
            'cms' => array_filter([
                'provider' => $cms_provider,
                'schema_version' => $this->limit((string)($data['schema_version'] ?? $cms['schema_version'] ?? ''), 16),
            ], function ($value) {
                return $value !== '' && $value !== [] && $value !== null;
            }),
            'captured_at' => $this->limit((string)($data['captured_at'] ?? $snapshot['captured_at'] ?? ''), 64),
            'raw' => $snapshot,
        ], function ($value) {
            return $value !== '' && $value !== [] && $value !== null;
        });

        $existing = $this->getByMessageId($message_id, 'site_page');
        $fields = [
            'provider' => 'widget',
            'channel_id' => 0,
            'chat_id' => $chat_id,
            'message_id' => $message_id,
            'context_type' => 'site_page',
            'external_chat_id' => '',
            'external_user_id' => '',
            'external_message_id' => '',
            'item_id' => $page_id,
            'chat_type' => $page_type,
            'title' => $title,
            'url' => $url,
            'price_string' => '',
            'image_url' => '',
            'status_id' => '',
            'snapshot_json' => json_encode($normalized_snapshot, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
            'is_marker' => $existing ? (int)$existing['is_marker'] : $this->isNewSitePageMarker($chat_id, $message_id, $page_type, $page_id, $url),
        ];

        if ($existing) {
            $parts = [];
            foreach ($fields as $key => $value) {
                if (in_array($key, ['chat_id', 'message_id', 'channel_id', 'is_marker'], true)) {
                    $parts[] = "`{$key}` = " . (int)$value;
                } else {
                    $parts[] = "`{$key}` = '" . $this->db->escape((string)$value) . "'";
                }
            }
            $parts[] = "`date_modified` = NOW()";
            $this->db->query("UPDATE `{$this->prefix}messenger_message_contexts` SET " . implode(', ', $parts) . " WHERE `context_id` = " . (int)$existing['context_id']);
        } else {
            $this->db->query(
                "INSERT INTO `{$this->prefix}messenger_message_contexts`
                 SET `provider` = 'widget',
                     `channel_id` = 0,
                     `chat_id` = " . (int)$fields['chat_id'] . ",
                     `message_id` = " . (int)$fields['message_id'] . ",
                     `context_type` = 'site_page',
                     `external_chat_id` = '',
                     `external_user_id` = '',
                     `external_message_id` = '',
                     `item_id` = '" . $this->db->escape($fields['item_id']) . "',
                     `chat_type` = '" . $this->db->escape($fields['chat_type']) . "',
                     `title` = '" . $this->db->escape($fields['title']) . "',
                     `url` = '" . $this->db->escape($fields['url']) . "',
                     `price_string` = '',
                     `image_url` = '',
                     `status_id` = '',
                     `snapshot_json` = '" . $this->db->escape($fields['snapshot_json']) . "',
                     `is_marker` = " . (int)$fields['is_marker'] . ",
                     `date_added` = NOW(),
                     `date_modified` = NOW()"
            );
        }

        return $this->getByMessageId($message_id, 'site_page');
    }

    public function getByMessageIds(array $message_ids, array $context_types = []): array {
        $ids = array_values(array_unique(array_filter(array_map('intval', $message_ids))));
        if (!$ids) {
            return [];
        }
        $type_sql = '';
        if ($context_types) {
            $types = [];
            foreach ($context_types as $type) {
                $type = trim((string)$type);
                if ($type !== '') {
                    $types[] = "'" . $this->db->escape($type) . "'";
                }
            }
            if ($types) {
                $type_sql = " AND `context_type` IN (" . implode(',', $types) . ")";
            }
        }
        $query = $this->db->query(
            "SELECT *
             FROM `{$this->prefix}messenger_message_contexts`
             WHERE `message_id` IN (" . implode(',', $ids) . ")
             {$type_sql}
             ORDER BY `message_id` ASC, `context_id` ASC"
        );
        $result = [];
        foreach ($query->rows as $row) {
            $result[(int)$row['message_id']] = $this->payload($row);
        }
        return $result;
    }

    public function getCurrentSiteContext($chat_id): array {
        $query = $this->db->query(
            "SELECT c.*
             FROM `{$this->prefix}messenger_message_contexts` c
             LEFT JOIN `{$this->prefix}messages` m ON m.`message_id` = c.`message_id`
             WHERE c.`chat_id` = " . (int)$chat_id . "
               AND c.`context_type` = 'site_page'
             ORDER BY COALESCE(m.`date_added`, c.`date_added`) DESC, c.`message_id` DESC
             LIMIT 1"
        );
        return $query->row ? $this->payload($query->row) : [];
    }

    public function getRecentSiteContextsForChat($chat_id, int $limit = 4): array {
        $limit = max(1, min(8, $limit));
        $query = $this->db->query(
            "SELECT c.*
             FROM `{$this->prefix}messenger_message_contexts` c
             LEFT JOIN `{$this->prefix}messages` m ON m.`message_id` = c.`message_id`
             WHERE c.`chat_id` = " . (int)$chat_id . "
               AND c.`context_type` = 'site_page'
             ORDER BY COALESCE(m.`date_added`, c.`date_added`) DESC, c.`message_id` DESC
             LIMIT " . (int)($limit * 3)
        );

        $seen = [];
        $result = [];
        foreach ($query->rows as $row) {
            $payload = $this->payload($row);
            $key = $this->sitePageFingerprint(
                (string)($payload['page_type'] ?? ''),
                (string)($payload['page_id'] ?? ''),
                (string)($payload['url'] ?? '')
            );
            if (isset($seen[$key])) {
                continue;
            }
            $seen[$key] = true;
            $result[] = $payload;
            if (count($result) >= $limit) {
                break;
            }
        }
        return $result;
    }

    public function getCurrentAvitoItem($chat_id): array {
        return $this->getCurrentMarketplaceItem($chat_id, ['avito_item']);
    }

    public function getCurrentMarketplaceItem($chat_id, array $context_types = ['avito_item', 'youla_item', 'wildberries_item', 'ozon_item', 'yandex_market_item']): array {
        $types_sql = $this->contextTypesSql($context_types);
        if ($types_sql === '') {
            return [];
        }
        $query = $this->db->query(
            "SELECT c.*
             FROM `{$this->prefix}messenger_message_contexts` c
             LEFT JOIN `{$this->prefix}messages` m ON m.`message_id` = c.`message_id`
             WHERE c.`chat_id` = " . (int)$chat_id . "
               AND c.`context_type` IN (" . $types_sql . ")
               AND c.`item_id` != ''
             ORDER BY COALESCE(m.`date_added`, c.`date_added`) DESC, c.`message_id` DESC
             LIMIT 1"
        );
        return $query->row ? $this->payload($query->row) : [];
    }

    public function getCurrentAvitoContext($chat_id): array {
        return $this->getCurrentMarketplaceContext($chat_id, ['avito_item', 'avito_general']);
    }

    public function getCurrentMarketplaceContext($chat_id, array $context_types = ['avito_item', 'avito_general', 'youla_item', 'wildberries_item', 'ozon_item', 'yandex_market_item']): array {
        $chat_id = (int)$chat_id;
        if ($chat_id <= 0) {
            return [];
        }
        $contexts = $this->getCurrentMarketplaceContextsByChatIds([$chat_id], $context_types);
        return $contexts[$chat_id] ?? [];
    }

    public function getCurrentAvitoContextsByChatIds(array $chat_ids): array {
        return $this->getCurrentMarketplaceContextsByChatIds($chat_ids, ['avito_item', 'avito_general']);
    }

    public function getCurrentMarketplaceContextsByChatIds(array $chat_ids, array $context_types = ['avito_item', 'avito_general', 'youla_item', 'wildberries_item', 'ozon_item', 'yandex_market_item']): array {
        $ids = array_values(array_unique(array_filter(array_map('intval', $chat_ids))));
        if (!$ids) {
            return [];
        }
        $types_sql = $this->contextTypesSql($context_types);
        if ($types_sql === '') {
            return [];
        }

        $query = $this->db->query(
            "SELECT c.*,
                    CASE WHEN c.`title` = '' THEN COALESCE(i.`title`, '') ELSE c.`title` END AS `resolved_title`,
                    CASE WHEN c.`url` = '' THEN COALESCE(i.`url`, '') ELSE c.`url` END AS `resolved_url`,
                    CASE WHEN c.`price_string` = '' THEN COALESCE(i.`price_string`, '') ELSE c.`price_string` END AS `resolved_price_string`,
                    CASE WHEN c.`image_url` = '' THEN COALESCE(i.`image_url`, '') ELSE c.`image_url` END AS `resolved_image_url`,
                    CASE WHEN c.`status_id` = '' THEN COALESCE(i.`status_id`, '') ELSE c.`status_id` END AS `resolved_status_id`
             FROM `{$this->prefix}messenger_message_contexts` c
             LEFT JOIN `{$this->prefix}messages` m ON m.`message_id` = c.`message_id`
             LEFT JOIN `{$this->prefix}messenger_item_context_cache` i
                    ON i.`provider` = c.`provider`
                   AND i.`channel_id` = c.`channel_id`
                   AND i.`item_id` = c.`item_id`
             WHERE c.`chat_id` IN (" . implode(',', $ids) . ")
               AND c.`context_type` IN (" . $types_sql . ")
             ORDER BY c.`chat_id` ASC,
                      COALESCE(m.`date_added`, c.`date_added`) DESC,
                      c.`message_id` DESC,
                      c.`context_id` DESC"
        );

        $result = [];
        foreach ($query->rows as $row) {
            $chat_id = (int)($row['chat_id'] ?? 0);
            if ($chat_id <= 0 || isset($result[$chat_id])) {
                continue;
            }
            $result[$chat_id] = $this->payload($row);
        }
        return $result;
    }

    private function isNewMarker(int $chat_id, int $message_id, string $item_id): int {
        return $this->isNewMarketplaceMarker($chat_id, $message_id, $item_id, 'avito_item');
    }

    private function isNewMarketplaceMarker(int $chat_id, int $message_id, string $item_id, string $context_type): int {
        $context_type = $this->normalizeContextType($context_type);
        if ($context_type === '') {
            return 1;
        }
        $message_query = $this->db->query(
            "SELECT `date_added`
             FROM `{$this->prefix}messages`
             WHERE `message_id` = " . (int)$message_id . "
             LIMIT 1"
        );
        $message_date = (string)($message_query->row['date_added'] ?? '');
        $date_condition = $message_date !== ''
            ? "(m.`date_added` < '" . $this->db->escape($message_date) . "' OR (m.`date_added` = '" . $this->db->escape($message_date) . "' AND c.`message_id` < " . (int)$message_id . "))"
            : "c.`message_id` < " . (int)$message_id;

        $query = $this->db->query(
            "SELECT c.`item_id`
             FROM `{$this->prefix}messenger_message_contexts` c
             LEFT JOIN `{$this->prefix}messages` m ON m.`message_id` = c.`message_id`
             WHERE c.`chat_id` = " . (int)$chat_id . "
               AND c.`context_type` = '" . $this->db->escape($context_type) . "'
               AND c.`item_id` != ''
               AND " . $date_condition . "
             ORDER BY COALESCE(m.`date_added`, c.`date_added`) DESC, c.`message_id` DESC
             LIMIT 1"
        );
        if (!$query->row) {
            return 1;
        }
        return (string)$query->row['item_id'] !== $item_id ? 1 : 0;
    }

    private function isNewGeneralMarker(int $chat_id, int $message_id): int {
        $message_query = $this->db->query(
            "SELECT `date_added`
             FROM `{$this->prefix}messages`
             WHERE `message_id` = " . (int)$message_id . "
             LIMIT 1"
        );
        $message_date = (string)($message_query->row['date_added'] ?? '');
        $date_condition = $message_date !== ''
            ? "(m.`date_added` < '" . $this->db->escape($message_date) . "' OR (m.`date_added` = '" . $this->db->escape($message_date) . "' AND c.`message_id` < " . (int)$message_id . "))"
            : "c.`message_id` < " . (int)$message_id;

        $query = $this->db->query(
            "SELECT c.`context_type`
             FROM `{$this->prefix}messenger_message_contexts` c
             LEFT JOIN `{$this->prefix}messages` m ON m.`message_id` = c.`message_id`
             WHERE c.`chat_id` = " . (int)$chat_id . "
               AND c.`context_type` IN ('avito_item', 'avito_general')
               AND " . $date_condition . "
             ORDER BY COALESCE(m.`date_added`, c.`date_added`) DESC, c.`message_id` DESC
             LIMIT 1"
        );
        if (!$query->row) {
            return 1;
        }
        return (string)$query->row['context_type'] !== 'avito_general' ? 1 : 0;
    }

    private function isNewSitePageMarker(int $chat_id, int $message_id, string $page_type, string $page_id, string $url): int {
        $message_query = $this->db->query(
            "SELECT `date_added`
             FROM `{$this->prefix}messages`
             WHERE `message_id` = " . (int)$message_id . "
             LIMIT 1"
        );
        $message_date = (string)($message_query->row['date_added'] ?? '');
        $date_condition = $message_date !== ''
            ? "(m.`date_added` < '" . $this->db->escape($message_date) . "' OR (m.`date_added` = '" . $this->db->escape($message_date) . "' AND c.`message_id` < " . (int)$message_id . "))"
            : "c.`message_id` < " . (int)$message_id;

        $query = $this->db->query(
            "SELECT c.`chat_type`, c.`item_id`, c.`url`
             FROM `{$this->prefix}messenger_message_contexts` c
             LEFT JOIN `{$this->prefix}messages` m ON m.`message_id` = c.`message_id`
             WHERE c.`chat_id` = " . (int)$chat_id . "
               AND c.`context_type` = 'site_page'
               AND " . $date_condition . "
             ORDER BY COALESCE(m.`date_added`, c.`date_added`) DESC, c.`message_id` DESC
             LIMIT 1"
        );
        if (!$query->row) {
            return 1;
        }
        $previous = $this->sitePageFingerprint((string)$query->row['chat_type'], (string)$query->row['item_id'], (string)$query->row['url']);
        $current = $this->sitePageFingerprint($page_type, $page_id, $url);
        return $previous !== $current ? 1 : 0;
    }

    private function payload(array $row): array {
        $context_type = (string)($row['context_type'] ?? '');
        $snapshot = json_decode((string)($row['snapshot_json'] ?? ''), true);
        $snapshot = is_array($snapshot) ? $snapshot : [];
        if (in_array($context_type, self::messengerContextTypes(), true)) {
            $provider = (string)($row['provider'] ?? '');
            return [
                'type' => $context_type,
                'context_kind' => 'messenger_message',
                'provider' => $provider,
                'message_id' => (int)($row['message_id'] ?? 0),
                'chat_type' => (string)($row['chat_type'] ?? ''),
                'sender_display_name' => (string)($snapshot['sender_display_name'] ?? ''),
                'chat_display_name' => (string)($snapshot['chat_display_name'] ?? ''),
                'forwarded' => !empty($snapshot['forwarded']),
                'forward_source_name' => (string)($snapshot['forward_source_name'] ?? ''),
                'forward_source_type' => (string)($snapshot['forward_source_type'] ?? ''),
                'sender_resolution_status' => (string)($snapshot['sender_resolution_status'] ?? ''),
                'system_event_type' => (string)($snapshot['system_event_type'] ?? ''),
                'system_event_actor_names' => array_values(array_filter(
                    is_array($snapshot['system_event_actor_names'] ?? null)
                        ? array_slice($snapshot['system_event_actor_names'], 0, 4)
                        : [],
                    'is_string'
                )),
                'system_event_actor_count' => min(10000, max(
                    0,
                    (int)($snapshot['system_event_actor_count'] ?? 0)
                )),
            ];
        }
        $page = is_array($snapshot['page'] ?? null) ? $snapshot['page'] : [];
        $cms = is_array($snapshot['cms'] ?? null) ? $snapshot['cms'] : [];
        $payload = [
            'type' => $context_type,
            'context_kind' => $context_type === 'site_page' ? 'page' : ($context_type === 'avito_general' ? 'general' : 'item'),
            'provider' => (string)($row['provider'] ?? ''),
            'channel_id' => (int)($row['channel_id'] ?? 0),
            'message_id' => (int)($row['message_id'] ?? 0),
            'external_chat_id' => (string)($row['external_chat_id'] ?? ''),
            'external_user_id' => (string)($row['external_user_id'] ?? ''),
            'external_message_id' => (string)($row['external_message_id'] ?? ''),
            'item_id' => (string)($row['item_id'] ?? ''),
            'chat_type' => (string)($row['chat_type'] ?? ''),
            'title' => (string)($row['resolved_title'] ?? $row['title'] ?? ''),
            'url' => (string)($row['resolved_url'] ?? $row['url'] ?? ''),
            'price_string' => (string)($row['resolved_price_string'] ?? $row['price_string'] ?? ''),
            'image_url' => (string)($row['resolved_image_url'] ?? $row['image_url'] ?? ''),
            'status_id' => (string)($row['resolved_status_id'] ?? $row['status_id'] ?? ''),
            'is_marker' => (int)($row['is_marker'] ?? 0),
            'date_modified' => (string)($row['date_modified'] ?? ''),
        ];
        if ($context_type === 'site_page') {
            $payload['page_type'] = (string)($page['type'] ?? $row['chat_type'] ?? '');
            $payload['page_id'] = (string)($page['id'] ?? $row['item_id'] ?? '');
            $payload['canonical_url'] = (string)($page['canonical_url'] ?? '');
            $payload['cms_provider'] = (string)($cms['provider'] ?? '');
            $payload['schema_version'] = (string)($cms['schema_version'] ?? '');
            $payload['snapshot'] = $snapshot;
        }
        return $payload;
    }

    private function largestImageUrl(array $sizes): string {
        $best_url = '';
        $best_area = 0;
        foreach ($sizes as $size => $url) {
            if (!is_string($url) || trim($url) === '') {
                continue;
            }
            $area = 0;
            if (preg_match('/^(\d+)x(\d+)$/', (string)$size, $matches)) {
                $area = (int)$matches[1] * (int)$matches[2];
            }
            if ($area >= $best_area) {
                $best_area = $area;
                $best_url = trim($url);
            }
        }
        return $best_url;
    }

    private function limit(string $value, int $limit): string {
        $value = trim($value);
        if (function_exists('mb_strlen') && function_exists('mb_substr')) {
            return \mb_strlen($value, 'UTF-8') > $limit ? \mb_substr($value, 0, $limit, 'UTF-8') : $value;
        }
        return strlen($value) > $limit ? substr($value, 0, $limit) : $value;
    }

    private function contextTypesSql(array $context_types): string {
        $types = [];
        foreach ($context_types as $type) {
            $type = $this->normalizeContextType((string)$type);
            if ($type !== '') {
                $types[] = "'" . $this->db->escape($type) . "'";
            }
        }
        return implode(',', array_values(array_unique($types)));
    }

    private function normalizeProvider(string $value): string {
        return strtolower(preg_replace('/[^a-z0-9_\-]/i', '', trim($value)));
    }

    private function normalizeContextType(string $value): string {
        return strtolower(preg_replace('/[^a-z0-9_\-]/i', '', trim($value)));
    }

    private function normalizePageType(string $value): string {
        $value = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', trim($value)));
        return in_array($value, ['product', 'category', 'information', 'page'], true) ? $value : 'page';
    }

    private function normalizeCmsProvider(string $value): string {
        return strtolower(preg_replace('/[^a-z0-9_\-]/i', '', trim($value)));
    }

    private function sitePageFingerprint(string $page_type, string $page_id, string $url): string {
        $page_type = $this->normalizePageType($page_type);
        $page_id = trim($page_id);
        $url = trim($url);
        return $page_type . '|' . $page_id . '|' . $url;
    }

    private function safeSupportUrl(string $url, array $hosts): string {
        $parts = parse_url(trim($url));
        if (!$parts || strtolower((string)($parts['scheme'] ?? '')) !== 'https') return '';
        return in_array(strtolower((string)($parts['host'] ?? '')), $hosts, true) ? $this->limit($url, 768) : '';
    }
}
