<?php
namespace EasyChat\Model;

use EasyChat\Library\AppClock;
use EasyChat\Library\CustomerContactService;
use EasyChat\Library\CustomerMergeService;
use EasyChat\Library\CustomerProfileDetector;
use EasyChat\Library\Http\ExternalHttpClient;
use EasyChat\Library\Log;
use EasyChat\Library\Messenger\ContactAvatarService;
use EasyChat\Library\Messenger\CommerceContextService;
use EasyChat\Library\Messenger\ChannelCatalog;

class Customer extends Base
{
    private $detector;

    public function __construct($registry)
    {
        parent::__construct($registry);
        $this->detector = new CustomerProfileDetector();
    }

    public function ensureCustomerForChat(int $chat_id): int
    {
        $chat = $this->getChat($chat_id);
        if (!$chat) {
            return 0;
        }

        $customer_id = (int) ($chat['customer_id'] ?? 0);
        if ($customer_id > 0 && $this->getCustomer($customer_id)) {
            $resolved_customer_id = $this->resolveMergedCustomerId($customer_id);
            if ($resolved_customer_id > 0 && $resolved_customer_id !== $customer_id) {
                $customer_id = $resolved_customer_id;
                $this->linkChat($chat_id, $customer_id);
            }
            $this->syncCustomerFromChat($customer_id, $chat);
            $this->ensureMessengerContactProfile($customer_id, $chat);
            $this->linkRelatedChatsToCustomer($customer_id, $chat);
            return $customer_id;
        }

        $customer_id = $this->findCustomerByMessengerContact($chat);
        if ($customer_id <= 0) {
            $customer_id = $this->findCustomerByContactIdentity($chat);
        }
        if ($customer_id <= 0) {
            $customer_id = $this->createCustomerFromChat($chat);
        }
        if ($customer_id > 0) {
            $resolved_customer_id = $this->resolveMergedCustomerId($customer_id);
            if ($resolved_customer_id > 0) {
                $customer_id = $resolved_customer_id;
            }
        }

        if ($customer_id > 0) {
            $this->linkChat($chat_id, $customer_id);
            $this->linkRelatedChatsToCustomer($customer_id, $chat);
            $this->syncCustomerFromChat($customer_id, $chat);
            $this->ensureMessengerContactProfile($customer_id, $chat);
        }

        return $customer_id;
    }

    public function panelForChat(int $chat_id, bool $refresh_commerce = false): array
    {
        $customer_id = $this->ensureCustomerForChat($chat_id);
        if ($customer_id <= 0) {
            return [];
        }

        $customer = $this->getCustomer($customer_id);
        if (!$customer) {
            return [];
        }

        $chat = $this->getChat($chat_id);
        if (!$chat) {
            return [];
        }

        $model_setting = new Setting($this->registry);
        $show_tags = (string)$model_setting->get('chat_dialog_tags_status', '1') === '1';
        $show_notes = (string)$model_setting->get('chat_dialog_notes_status', '1') === '1';

        $profiles = $this->profiles($customer_id, $chat);
        $linked_accounts = $this->linkedAccounts($customer_id, $chat);
        $visible_linked_accounts = $this->visibleLinkedAccounts($profiles, $linked_accounts);
        $tags = $show_tags ? $this->tagsForChat($customer_id, $chat_id) : [];
        $notes = $show_notes ? $this->notes($customer_id, $chat_id) : [];
        $recent_chats_total = $this->recentChatsTotal($customer_id, $chat);
        $recent_chats = $this->recentChats($customer_id, $chat_id, $chat);
        $reply_capability = (new \EasyChat\Library\Messenger\ReplyCapability($this->registry))->forChatId($chat_id);
         
         
        $commerce = (new \EasyChat\Library\Commerce\CustomerCommerceContextService($this->registry))->forCustomer($customer_id, 3, $refresh_commerce);
        return [
            'customer' => $this->customerPayload($customer, $chat),
            'info' => $this->infoPayload($customer, $chat, $chat_id) + ['reply_capability' => $reply_capability],
            'reply_capability' => $reply_capability,
            'profiles' => $profiles,
            'linked_accounts' => $visible_linked_accounts,
            'contact_points' => $this->contactPoints($customer_id),
            'detected_profiles' => $this->detectedProfilesForChat($chat_id, $customer_id),
            'merge_candidates' => $this->mergeCandidatesFromRecent($customer_id, $recent_chats),
            'show_tags' => $show_tags,
            'show_notes' => $show_notes,
            'dialog_tags_enabled' => $show_tags,
            'dialog_notes_enabled' => $show_notes,
            'tags' => $tags,
            'notes' => $notes,
            'recent_chats' => $recent_chats,
            'recent_chats_total' => $recent_chats_total,
            'summary' => $this->summaryPayload($customer, $profiles, $visible_linked_accounts, $tags, $notes),
            'commerce' => $commerce,
        ];
    }

    public function profiles(int $customer_id, array $chat = []): array
    {
        $query = $this->db->query(
            "SELECT *
             FROM `{$this->prefix}customer_profiles`
             WHERE `customer_id` = " . (int) $customer_id . "
             ORDER BY FIELD(`provider`, 'telegram', 'whatsapp-personal', 'avito', 'youla', 'max', 'vk'), `profile_id` ASC"
        );

        $profiles = [];
        foreach ($query->rows as $row) {
            $profiles[] = $this->profilePayload($row, $chat);
        }
        return $profiles;
    }

    public function linkedAccounts(int $customer_id, array $chat = []): array
    {
        $join = "LEFT JOIN `{$this->prefix}messenger_contacts` mc ON mc.`contact_id` = s.`external_contact_id`";
        $where = "s.`customer_id` = " . (int) $customer_id;
        $identity_where = $this->relatedChatIdentityWhereSql($chat);
        if ($identity_where !== '') {
            $where = "(" . $where . " OR (" . $identity_where . "))";
        }
        $suppressed_hashes = $this->profileSuppressionHashSet($customer_id);

        $query = $this->db->query(
            "SELECT s.`chat_id`,
                    s.`customer_name`,
                    s.`source_provider`,
                    s.`date_modified`,
                    mc.`contact_id`,
                    mc.`provider`,
                    mc.`channel_id`,
                    mc.`display_name`,
                    mc.`username`,
                    mc.`profile_url`,
                    mc.`customer_identity_hash`,
                    mc.`last_seen_at`
             FROM `{$this->prefix}sessions` s
             {$join}
             WHERE {$where}
               AND s.`source_provider` IN ('telegram', 'whatsapp-personal', 'avito', 'youla', 'max-bot', 'max', 'vk')
             ORDER BY s.`date_modified` DESC, s.`chat_id` DESC
             LIMIT 300"
        );

        $accounts = [];
        foreach ($query->rows as $row) {
            $provider = (string) ($row['provider'] ?: $row['source_provider']);
            if ($provider === '') {
                continue;
            }

            $identity_hash = trim((string) ($row['customer_identity_hash'] ?? ''));
            $contact_id = (int) ($row['contact_id'] ?? 0);
            if ($identity_hash !== '') {
                $key = $provider . '|identity|' . (int) ($row['channel_id'] ?? 0) . '|' . $identity_hash;
            } elseif ($contact_id > 0) {
                $key = $provider . '|contact|' . $contact_id;
            } else {
                $key = $provider . '|chat|' . (int) $row['chat_id'];
            }

            $profile_url = trim((string) ($row['profile_url'] ?? ''));
            $normalized = $profile_url !== '' ? $this->detector->normalizeUrl($profile_url) : null;
            $account_profile_url = '';
            $account_profile_hash = '';
            $profile_suppressed = false;
            if ($normalized && $normalized['provider'] === $provider) {
                $profile_url = $normalized['profile_url'];
                $profile_hash = $normalized['profile_url_hash'];
                $account_profile_url = $profile_url;
                $account_profile_hash = $profile_hash;
                $profile_suppressed = isset($suppressed_hashes[$provider . '|' . $profile_hash]);
                if ($profile_suppressed) {
                    $profile_url = '';
                    $profile_hash = '';
                }
            } else {
                $profile_url = '';
                $profile_hash = '';
            }

            if (!isset($accounts[$key])) {
                $display_name = trim((string) ($row['display_name'] ?: $row['customer_name']));
                $accounts[$key] = [
                    'provider' => $provider,
                    'provider_label' => $this->sourceLabel($provider),
                    'messenger_contact_id' => $contact_id,
                    'display_name' => $this->limitPlain($display_name, 255),
                    'username' => $this->limitPlain((string) ($row['username'] ?? ''), 80),
                    'profile_url' => $profile_url,
                    'profile_url_hash' => $profile_hash,
                    'account_profile_url' => $account_profile_url,
                    'account_profile_url_hash' => $account_profile_hash,
                    'has_profile_url' => $profile_url !== '' ? 1 : 0,
                    'profile_suppressed' => $profile_suppressed ? 1 : 0,
                    'source_label' => $this->sourceLabel($provider),
                    'chat_count' => 0,
                    'last_seen' => '',
                ];
            }
            if ($profile_suppressed) {
                $accounts[$key]['profile_suppressed'] = 1;
                $accounts[$key]['profile_url'] = '';
                $accounts[$key]['profile_url_hash'] = '';
                $accounts[$key]['has_profile_url'] = 0;
                if ($account_profile_url !== '') {
                    $accounts[$key]['account_profile_url'] = $account_profile_url;
                    $accounts[$key]['account_profile_url_hash'] = $account_profile_hash;
                }
            }

            $accounts[$key]['chat_count']++;
            $last_seen = (string) ($row['last_seen_at'] ?: $row['date_modified']);
            if ($last_seen !== '' && ($accounts[$key]['last_seen'] === '' || strcmp($last_seen, $accounts[$key]['last_seen']) > 0)) {
                $accounts[$key]['last_seen'] = $last_seen;
            }
            if ($accounts[$key]['display_name'] === '' && trim((string) $row['customer_name']) !== '') {
                $accounts[$key]['display_name'] = $this->limitPlain((string) $row['customer_name'], 255);
            }
        }

        return array_values($accounts);
    }

    public function addProfileFromChat(int $chat_id, string $url, int $manager_id = 0, int $message_id = 0, string $source = 'operator'): array
    {
        $normalized = $this->detector->normalizeUrl($url);
        if (!$normalized) {
            return ['success' => false, 'error' => 'profile_url_invalid'];
        }

        $customer_id = $this->ensureCustomerForChat($chat_id);
        if ($customer_id <= 0) {
            return ['success' => false, 'error' => 'chat_not_found'];
        }

        $existing = $this->findProfileByProviderHash($normalized['provider'], $normalized['profile_url_hash']);
        if ($existing && (int) $existing['customer_id'] !== $customer_id) {
            $conflict = (new CustomerMergeService($this->registry))->profileConflictPayload($customer_id, $existing, $normalized['profile_url']);
            if ($conflict) {
                return ['success' => false, 'error' => 'profile_conflict', 'code' => 'profile_conflict', 'conflict' => $conflict];
            }
            return ['success' => false, 'error' => 'profile_linked_to_another_customer'];
        }

        $now = $this->now();
        if ($existing) {
            $this->db->query(
                "UPDATE `{$this->prefix}customer_profiles`
                 SET `source` = '" . $this->db->escape($this->normalizeProfileSource($source)) . "',
                     `message_id` = " . (int) $message_id . ",
                     `added_by` = " . (int) $manager_id . ",
                     `date_modified` = '" . $this->db->escape($now) . "'
                 WHERE `profile_id` = " . (int) $existing['profile_id']
            );
            $this->clearProfileSuppression($customer_id, $normalized['provider'], $normalized['profile_url_hash']);
            return ['success' => true, 'profile' => $this->profilePayload($this->getProfile((int) $existing['profile_id']))];
        }

        $this->db->query(
            "INSERT INTO `{$this->prefix}customer_profiles`
             SET `customer_id` = " . (int) $customer_id . ",
                 `provider` = '" . $this->db->escape($normalized['provider']) . "',
                 `profile_url` = '" . $this->db->escape($normalized['profile_url']) . "',
                 `profile_url_hash` = '" . $this->db->escape($normalized['profile_url_hash']) . "',
                 `username` = '" . $this->db->escape($normalized['username']) . "',
                 `external_user_id` = '',
                 `messenger_contact_id` = NULL,
                 `source` = '" . $this->db->escape($this->normalizeProfileSource($source)) . "',
                 `confidence` = 100,
                 `added_by` = " . (int) $manager_id . ",
                 `message_id` = " . (int) $message_id . ",
                 `date_added` = '" . $this->db->escape($now) . "',
                 `date_modified` = '" . $this->db->escape($now) . "'"
        );

        $profile_id = (int) $this->db->getLastId();
        $this->clearProfileSuppression($customer_id, $normalized['provider'], $normalized['profile_url_hash']);
        $this->touchCustomer($customer_id);
        return ['success' => true, 'profile' => $this->profilePayload($this->getProfile($profile_id))];
    }

    public function removeProfileFromChat(
        int $chat_id,
        int $profile_id,
        int $manager_id = 0,
        string $reason_code = 'wrong_customer',
        string $reason_note = '',
        bool $suppress_candidate = true
    ): array {
        if ($profile_id <= 0) {
            return ['success' => false, 'error' => 'profile_not_found'];
        }

        $customer_id = $this->ensureCustomerForChat($chat_id);
        if ($customer_id <= 0) {
            return ['success' => false, 'error' => 'chat_not_found'];
        }
        $chat = $this->getChat($chat_id);

        $reason_code = $this->normalizeProfileUnlinkReason($reason_code);
        $reason_note = $this->limitPlain($reason_note, 255);
        $now = $this->now();

        try {
            $this->db->query('START TRANSACTION');
            $profile = $this->lockProfileForUpdate($profile_id);
            if (!$profile) {
                $this->db->query('COMMIT');
                return ['success' => true, 'already_removed' => 1];
            }
            if ((int) $profile['customer_id'] !== (int) $customer_id) {
                $this->db->query('ROLLBACK');
                return ['success' => false, 'error' => 'profile_unlink_denied'];
            }
            if ($this->isOwnChatProfile($profile, $chat)) {
                $this->db->query('ROLLBACK');
                return ['success' => false, 'error' => 'profile_unlink_own_profile_denied'];
            }

            $detach_plan = $this->profileChannelDetachPlan($customer_id, $profile, $now);
            $snapshot = [
                'profile' => $profile,
                'profile_payload' => $this->profilePayload($profile, $chat),
                'suppress_candidate' => $suppress_candidate ? 1 : 0,
                'detached_channel' => $detach_plan,
            ];
            $snapshot_json = json_encode($snapshot, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
            if ($snapshot_json === false) {
                $snapshot_json = '{}';
            }

            $this->db->query(
                "INSERT INTO `{$this->prefix}customer_profile_events`
                 SET `event_type` = 'unlinked',
                     `customer_id` = " . (int) $customer_id . ",
                     `profile_id` = " . (int) $profile_id . ",
                     `provider` = '" . $this->db->escape((string) $profile['provider']) . "',
                     `profile_url` = '" . $this->db->escape((string) $profile['profile_url']) . "',
                     `profile_url_hash` = '" . $this->db->escape((string) $profile['profile_url_hash']) . "',
                     `username` = '" . $this->db->escape((string) ($profile['username'] ?? '')) . "',
                     `messenger_contact_id` = " . (int) ($profile['messenger_contact_id'] ?? 0) . ",
                     `manager_id` = " . (int) $manager_id . ",
                     `chat_id` = " . (int) $chat_id . ",
                     `message_id` = " . (int) ($profile['message_id'] ?? 0) . ",
                     `reason_code` = '" . $this->db->escape($reason_code) . "',
                     `reason_note` = '" . $this->db->escape($reason_note) . "',
                     `snapshot_json` = '" . $this->db->escape($snapshot_json) . "',
                     `date_added` = '" . $this->db->escape($now) . "'"
            );

            $detached_customer_id = (int) ($detach_plan['destination_customer_id'] ?? 0);
            if ($detached_customer_id > 0) {
                $this->applyProfileChannelDetachPlan($customer_id, $detached_customer_id, $detach_plan, $now);
                $this->db->query(
                    "UPDATE `{$this->prefix}customer_profiles`
                     SET `customer_id` = " . (int) $detached_customer_id . ",
                         `date_modified` = '" . $this->db->escape($now) . "'
                     WHERE `profile_id` = " . (int) $profile_id . "
                       AND `customer_id` = " . (int) $customer_id
                );
            } else {
                $this->db->query(
                    "DELETE FROM `{$this->prefix}customer_profiles`
                     WHERE `profile_id` = " . (int) $profile_id . "
                       AND `customer_id` = " . (int) $customer_id
                );
            }
            if ((int) $this->db->countAffected() < 1) {
                throw new \RuntimeException('profile_delete_failed');
            }

            if ($suppress_candidate) {
                $this->upsertProfileSuppression($customer_id, $profile, $manager_id, $reason_code, $now);
            }

            $this->touchCustomer($customer_id);
            $this->db->query('COMMIT');
        } catch (\Throwable $e) {
            try {
                $this->db->query('ROLLBACK');
            } catch (\Throwable $rollback_error) {
                $this->logProfileUnlinkFailure($rollback_error, [
                    'stage' => 'rollback',
                    'chat_id' => $chat_id,
                    'profile_id' => $profile_id,
                    'customer_id' => $customer_id,
                ]);
            }
            $this->logProfileUnlinkFailure($e, [
                'stage' => 'unlink',
                'chat_id' => $chat_id,
                'profile_id' => $profile_id,
                'customer_id' => $customer_id,
                'manager_id' => $manager_id,
                'reason_code' => $reason_code,
            ]);
            return ['success' => false, 'error' => 'profile_unlink_failed'];
        }

        return ['success' => true];
    }

    public function profileSuppressionHashSet(int $customer_id): array
    {
        if ($customer_id <= 0) {
            return [];
        }

        try {
            $query = $this->db->query(
                "SELECT `provider`, `profile_url_hash`
                 FROM `{$this->prefix}customer_profile_suppressions`
                 WHERE `customer_id` = " . (int) $customer_id
            );
        } catch (\Throwable $e) {
             
             
            return [];
        }

        $hashes = [];
        foreach ($query->rows as $row) {
            $provider = trim((string) ($row['provider'] ?? ''));
            $hash = trim((string) ($row['profile_url_hash'] ?? ''));
            if ($provider !== '' && $hash !== '') {
                $hashes[$provider . '|' . $hash] = true;
            }
        }
        return $hashes;
    }

    public function profileCandidateSkipHashSet(int $chat_id, int $customer_id): array
    {
        if ($customer_id <= 0) {
            return [];
        }

        $hashes = [];
        foreach ($this->profiles($customer_id) as $profile) {
            $provider = trim((string) ($profile['provider'] ?? ''));
            $hash = trim((string) ($profile['profile_url_hash'] ?? ''));
            if ($provider !== '' && $hash !== '') {
                $hashes[$provider . '|' . $hash] = true;
            }
        }
        foreach ($this->profileSuppressionHashSet($customer_id) as $key => $unused) {
            $hashes[$key] = true;
        }

        $chat = $chat_id > 0 ? $this->getChat($chat_id) : [];
        foreach ($this->linkedAccounts($customer_id, $chat) as $account) {
            $provider = trim((string) ($account['provider'] ?? ''));
            if ($provider === '') {
                continue;
            }
            foreach (['profile_url_hash', 'account_profile_url_hash'] as $field) {
                $hash = trim((string) ($account[$field] ?? ''));
                if ($hash !== '') {
                    $hashes[$provider . '|' . $hash] = true;
                }
            }
        }

        return $hashes;
    }

    public function hideProfileCandidateForChat(int $chat_id, string $url, int $manager_id = 0, int $message_id = 0): array
    {
        $normalized = $this->detector->normalizeUrl($url);
        if (!$normalized) {
            return ['success' => false, 'error' => 'profile_url_invalid'];
        }

        $customer_id = $this->ensureCustomerForChat($chat_id);
        if ($customer_id <= 0) {
            return ['success' => false, 'error' => 'chat_not_found'];
        }

        $now = $this->now();
        $profile = [
            'provider' => $normalized['provider'],
            'profile_url' => $normalized['profile_url'],
            'profile_url_hash' => $normalized['profile_url_hash'],
            'username' => $normalized['username'],
            'message_id' => max(0, (int) $message_id),
        ];

        try {
            $this->upsertProfileSuppression($customer_id, $profile, $manager_id, 'candidate_hidden', $now);
            $snapshot_json = json_encode([
                'profile' => $profile,
                'source' => 'detected_profile_candidate',
            ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
            if ($snapshot_json === false) {
                $snapshot_json = '{}';
            }
            $this->db->query(
                "INSERT INTO `{$this->prefix}customer_profile_events`
                 SET `event_type` = 'candidate_hidden',
                     `customer_id` = " . (int) $customer_id . ",
                     `profile_id` = 0,
                     `provider` = '" . $this->db->escape($normalized['provider']) . "',
                     `profile_url` = '" . $this->db->escape($normalized['profile_url']) . "',
                     `profile_url_hash` = '" . $this->db->escape($normalized['profile_url_hash']) . "',
                     `username` = '" . $this->db->escape($normalized['username']) . "',
                     `messenger_contact_id` = 0,
                     `manager_id` = " . (int) $manager_id . ",
                     `chat_id` = " . (int) $chat_id . ",
                     `message_id` = " . max(0, (int) $message_id) . ",
                     `reason_code` = 'candidate_hidden',
                     `reason_note` = '',
                     `snapshot_json` = '" . $this->db->escape($snapshot_json) . "',
                     `date_added` = '" . $this->db->escape($now) . "'"
            );
        } catch (\Throwable $e) {
            $this->logProfileUnlinkFailure($e, [
                'stage' => 'hide_candidate',
                'chat_id' => $chat_id,
                'customer_id' => $customer_id,
                'manager_id' => $manager_id,
            ]);
            return ['success' => false, 'error' => 'profile_candidate_hide_failed'];
        }

        return ['success' => true];
    }

    private function profileChannelDetachPlan(int $customer_id, array $profile, string $now): array
    {
        $sessions = $this->profileLinkedSessionRows($customer_id, $profile);
        if (!$sessions) {
            return [];
        }

        $destination_customer_id = $this->profileDetachDestinationCustomerId($customer_id, $profile, $sessions, $now);
        if ($destination_customer_id <= 0 || $destination_customer_id === $customer_id) {
            return [];
        }

        $chat_ids = [];
        foreach ($sessions as $session) {
            $chat_id = (int) ($session['chat_id'] ?? 0);
            if ($chat_id > 0) {
                $chat_ids[$chat_id] = $chat_id;
            }
        }
        if (!$chat_ids) {
            return [];
        }

        return [
            'destination_customer_id' => $destination_customer_id,
            'chat_ids' => array_values($chat_ids),
            'sessions' => $sessions,
        ];
    }

    private function profileLinkedSessionRows(int $customer_id, array $profile): array
    {
        $provider = trim((string) ($profile['provider'] ?? ''));
        if ($customer_id <= 0 || $provider === '') {
            return [];
        }

        $conditions = [];
        $contact_id = (int) ($profile['messenger_contact_id'] ?? 0);
        if ($contact_id > 0) {
            $conditions[] = "mc.`contact_id` = " . (int) $contact_id;
            $identity = $this->messengerContactIdentity($contact_id);
            if ($identity) {
                $conditions[] = "(mc.`provider` = '" . $this->db->escape($identity['provider']) . "'
                    AND mc.`channel_id` = " . (int) $identity['channel_id'] . "
                    AND mc.`customer_identity_hash` = '" . $this->db->escape($identity['customer_identity_hash']) . "')";
            }
        }

        $profile_url = trim((string) ($profile['profile_url'] ?? ''));
        if ($profile_url !== '') {
            $conditions[] = "(mc.`provider` = '" . $this->db->escape($provider) . "'
                AND mc.`profile_url` = '" . $this->db->escape($profile_url) . "')";
        }

        if (!$conditions) {
            return [];
        }

        $query = $this->db->query(
            "SELECT DISTINCT s.`chat_id`,
                    s.`customer_id`,
                    s.`customer_name`,
                    s.`source_provider`,
                    s.`date_modified`,
                    s.`external_contact_id`,
                    mc.`contact_id`,
                    mc.`provider`,
                    mc.`channel_id`,
                    mc.`display_name`,
                    mc.`username`,
                    mc.`profile_url`,
                    mc.`customer_identity_hash`
             FROM `{$this->prefix}sessions` s
             INNER JOIN `{$this->prefix}messenger_contacts` mc ON mc.`contact_id` = s.`external_contact_id`
             WHERE s.`customer_id` = " . (int) $customer_id . "
               AND (" . implode(' OR ', $conditions) . ")
             ORDER BY s.`date_modified` DESC, s.`chat_id` DESC
             FOR UPDATE"
        );

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

    private function messengerContactIdentity(int $contact_id): array
    {
        if ($contact_id <= 0) {
            return [];
        }

        $contact = (new Messenger($this->registry))->getContactById($contact_id);
        if (!$contact) {
            return [];
        }

        return (new Messenger($this->registry))->customerIdentityLookup($contact);
    }

    private function profileDetachDestinationCustomerId(int $customer_id, array $profile, array $sessions, string $now): int
    {
        $source_customer_id = $this->profileMergeSourceCustomerId($customer_id, $profile);
        if ($source_customer_id > 0 && $source_customer_id !== $customer_id && $this->getCustomer($source_customer_id)) {
            return $source_customer_id;
        }

        $first_session = $sessions[0] ?? [];
        if (!$first_session) {
            return 0;
        }

        return $this->createCustomerFromChat($first_session);
    }

    private function profileMergeSourceCustomerId(int $customer_id, array $profile): int
    {
        $profile_url = trim((string) ($profile['profile_url'] ?? ''));
        if ($customer_id <= 0 || $profile_url === '') {
            return 0;
        }

        try {
            $query = $this->db->query(
                "SELECT `source_customer_id`
                 FROM `{$this->prefix}customer_merge_audit`
                 WHERE `target_customer_id` = " . (int) $customer_id . "
                   AND `profile_url` = '" . $this->db->escape($profile_url) . "'
                 ORDER BY `merge_id` DESC
                 LIMIT 1"
            );
        } catch (\Throwable $e) {
            return 0;
        }

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

    private function applyProfileChannelDetachPlan(int $customer_id, int $detached_customer_id, array $plan, string $now): void
    {
        $chat_ids = $this->sanitizeIdList($plan['chat_ids'] ?? []);
        if ($customer_id <= 0 || $detached_customer_id <= 0 || !$chat_ids) {
            return;
        }
        $chat_id_sql = implode(',', $chat_ids);

         
         
        $this->activateDetachedCustomer($detached_customer_id, $customer_id, $now);
        $this->db->query(
            "UPDATE `{$this->prefix}sessions`
             SET `customer_id` = " . (int) $detached_customer_id . "
             WHERE `customer_id` = " . (int) $customer_id . "
               AND `chat_id` IN (" . $chat_id_sql . ")"
        );

        $this->moveDetachedNotes($customer_id, $detached_customer_id, $chat_id_sql, $now);
        $this->moveDetachedTagLinks($customer_id, $detached_customer_id, $chat_id_sql);
        $this->moveDetachedContactPoints($customer_id, $detached_customer_id, $chat_id_sql, $now);
        $this->ensurePrimaryContactPoints($detached_customer_id);
        $this->ensurePrimaryContactPoints($customer_id);
        $this->touchCustomer($detached_customer_id);
    }

    private function activateDetachedCustomer(int $detached_customer_id, int $old_customer_id, string $now): void
    {
        $customer = $this->getCustomer($detached_customer_id);
        if (!$customer) {
            return;
        }

        $updates = ["`date_modified` = '" . $this->db->escape($now) . "'"];
        if ((int) ($customer['merged_into_customer_id'] ?? 0) === (int) $old_customer_id) {
            $updates[] = "`merged_into_customer_id` = NULL";
            $updates[] = "`merged_at` = NULL";
            $updates[] = "`merged_by` = 0";
        }

        $this->db->query(
            "UPDATE `{$this->prefix}customers`
             SET " . implode(', ', $updates) . "
             WHERE `customer_id` = " . (int) $detached_customer_id
        );
    }

    private function moveDetachedNotes(int $customer_id, int $detached_customer_id, string $chat_id_sql, string $now): void
    {
        $this->db->query(
            "UPDATE `{$this->prefix}customer_notes`
             SET `customer_id` = " . (int) $detached_customer_id . ",
                 `date_modified` = '" . $this->db->escape($now) . "'
             WHERE `customer_id` = " . (int) $customer_id . "
               AND `chat_id` IN (" . $chat_id_sql . ")"
        );
    }

    private function moveDetachedTagLinks(int $customer_id, int $detached_customer_id, string $chat_id_sql): void
    {
        $this->db->query(
            "UPDATE `{$this->prefix}customer_tag_links` target_link
             JOIN `{$this->prefix}customer_tag_links` source_link
                ON source_link.`customer_id` = " . (int) $customer_id . "
               AND source_link.`chat_id` IN (" . $chat_id_sql . ")
               AND target_link.`customer_id` = " . (int) $detached_customer_id . "
               AND target_link.`chat_id` = source_link.`chat_id`
               AND target_link.`tag_id` = source_link.`tag_id`
             SET target_link.`date_added` = LEAST(target_link.`date_added`, source_link.`date_added`)"
        );
        $this->db->query(
            "INSERT INTO `{$this->prefix}customer_tag_links`
                (`customer_id`, `chat_id`, `tag_id`, `date_added`)
             SELECT " . (int) $detached_customer_id . ", source_link.`chat_id`, source_link.`tag_id`, source_link.`date_added`
             FROM `{$this->prefix}customer_tag_links` source_link
             LEFT JOIN `{$this->prefix}customer_tag_links` target_link
                ON target_link.`customer_id` = " . (int) $detached_customer_id . "
               AND target_link.`chat_id` = source_link.`chat_id`
               AND target_link.`tag_id` = source_link.`tag_id`
             WHERE source_link.`customer_id` = " . (int) $customer_id . "
               AND source_link.`chat_id` IN (" . $chat_id_sql . ")
               AND target_link.`id` IS NULL"
        );
        $this->db->query(
            "DELETE FROM `{$this->prefix}customer_tag_links`
             WHERE `customer_id` = " . (int) $customer_id . "
               AND `chat_id` IN (" . $chat_id_sql . ")"
        );
    }

    private function moveDetachedContactPoints(int $customer_id, int $detached_customer_id, string $chat_id_sql, string $now): void
    {
        $this->db->query(
            "UPDATE `{$this->prefix}customer_contact_points` target_cp
             JOIN `{$this->prefix}customer_contact_points` source_cp
                ON source_cp.`customer_id` = " . (int) $customer_id . "
               AND source_cp.`source_chat_id` IN (" . $chat_id_sql . ")
               AND target_cp.`customer_id` = " . (int) $detached_customer_id . "
               AND target_cp.`type` = source_cp.`type`
               AND target_cp.`value_hash` = source_cp.`value_hash`
             SET target_cp.`status` = " . $this->contactPointStatusMergeSql('target_cp.`status`', 'source_cp.`status`') . ",
                 target_cp.`last_seen_at` = GREATEST(IFNULL(target_cp.`last_seen_at`, target_cp.`date_modified`), IFNULL(source_cp.`last_seen_at`, source_cp.`date_modified`)),
                 target_cp.`date_modified` = '" . $this->db->escape($now) . "'"
        );
        $this->db->query(
            "INSERT INTO `{$this->prefix}customer_contact_points`
                (`customer_id`, `type`, `value`, `value_hash`, `source`, `source_customer_id`,
                 `is_primary`, `status`, `source_chat_id`, `source_message_id`, `added_by`,
                 `date_added`, `date_modified`, `last_seen_at`)
             SELECT " . (int) $detached_customer_id . ",
                    source_cp.`type`,
                    source_cp.`value`,
                    source_cp.`value_hash`,
                    source_cp.`source`,
                    source_cp.`source_customer_id`,
                    0,
                    source_cp.`status`,
                    source_cp.`source_chat_id`,
                    source_cp.`source_message_id`,
                    source_cp.`added_by`,
                    source_cp.`date_added`,
                    '" . $this->db->escape($now) . "',
                    IFNULL(source_cp.`last_seen_at`, source_cp.`date_modified`)
             FROM `{$this->prefix}customer_contact_points` source_cp
             LEFT JOIN `{$this->prefix}customer_contact_points` target_cp
                ON target_cp.`customer_id` = " . (int) $detached_customer_id . "
               AND target_cp.`type` = source_cp.`type`
               AND target_cp.`value_hash` = source_cp.`value_hash`
             WHERE source_cp.`customer_id` = " . (int) $customer_id . "
               AND source_cp.`source_chat_id` IN (" . $chat_id_sql . ")
               AND target_cp.`contact_point_id` IS NULL"
        );
        $this->db->query(
            "DELETE FROM `{$this->prefix}customer_contact_points`
             WHERE `customer_id` = " . (int) $customer_id . "
               AND `source_chat_id` IN (" . $chat_id_sql . ")"
        );
    }

    private function ensurePrimaryContactPoints(int $customer_id): void
    {
        if ($customer_id <= 0) {
            return;
        }

        foreach (['email', 'phone'] as $type) {
            $primary = $this->db->query(
                "SELECT `contact_point_id`
                 FROM `{$this->prefix}customer_contact_points`
                 WHERE `customer_id` = " . (int) $customer_id . "
                   AND `type` = '" . $this->db->escape($type) . "'
                   AND `is_primary` = 1
                   AND `status` <> 'archived'
                 LIMIT 1"
            );
            if ($primary && $primary->num_rows) {
                continue;
            }

            $candidate = $this->db->query(
                "SELECT `contact_point_id`
                 FROM `{$this->prefix}customer_contact_points`
                 WHERE `customer_id` = " . (int) $customer_id . "
                   AND `type` = '" . $this->db->escape($type) . "'
                   AND `status` <> 'archived'
                 ORDER BY FIELD(`status`, 'operator_confirmed', 'auto_detected', 'archived'),
                          `date_modified` DESC,
                          `contact_point_id` ASC
                 LIMIT 1"
            );
            if ($candidate && $candidate->num_rows) {
                $this->db->query(
                    "UPDATE `{$this->prefix}customer_contact_points`
                     SET `is_primary` = 1
                     WHERE `contact_point_id` = " . (int) $candidate->row['contact_point_id']
                );
            }
        }
    }

    private function contactPointStatusMergeSql(string $current, string $incoming): string
    {
        return "CASE
            WHEN {$current} = 'operator_confirmed' THEN {$current}
            WHEN {$incoming} = 'operator_confirmed' THEN {$incoming}
            WHEN {$current} = 'archived' AND {$incoming} <> 'archived' THEN {$incoming}
            ELSE {$current}
        END";
    }

    private function sanitizeIdList(array $ids): array
    {
        $result = [];
        foreach ($ids as $id) {
            $id = (int) $id;
            if ($id > 0) {
                $result[$id] = $id;
            }
        }
        return array_values($result);
    }

    public function addNoteForChat(int $chat_id, string $text, int $manager_id = 0): array
    {
        $text = $this->cleanNoteText($text);
        if ($text === '') {
            return ['success' => false, 'error' => 'note_empty'];
        }

        $customer_id = $this->ensureCustomerForChat($chat_id);
        if ($customer_id <= 0) {
            return ['success' => false, 'error' => 'chat_not_found'];
        }

        $now = $this->now();
        $this->db->query(
            "INSERT INTO `{$this->prefix}customer_notes`
             SET `customer_id` = " . (int) $customer_id . ",
                 `chat_id` = " . (int) $chat_id . ",
                 `manager_id` = " . (int) $manager_id . ",
                 `note_type` = 'chat',
                 `text` = '" . $this->db->escape($text) . "',
                 `date_added` = '" . $this->db->escape($now) . "',
                 `date_modified` = '" . $this->db->escape($now) . "'"
        );

        $note_id = (int) $this->db->getLastId();
        $this->touchCustomer($customer_id);
        return ['success' => true, 'note' => $this->notePayload($this->getNote($note_id))];
    }

    public function deleteNoteForChat(int $chat_id, int $note_id): array
    {
        $customer_id = $this->ensureCustomerForChat($chat_id);
        if ($customer_id <= 0) {
            return ['success' => false, 'error' => 'chat_not_found'];
        }

        $note = $this->getNote($note_id);
        if (!$note) {
            return ['success' => false, 'error' => 'note_not_found'];
        }

        if (!$this->noteBelongsToChat($note, $customer_id, $chat_id)) {
            return ['success' => false, 'error' => 'note_delete_denied'];
        }

         
         
        $this->db->query(
            "DELETE FROM `{$this->prefix}customer_notes`
             WHERE `note_id` = " . (int) $note_id . "
               AND `customer_id` = " . (int) $customer_id . "
               AND (`chat_id` = " . (int) $chat_id . " OR `chat_id` = 0)"
        );
        if ((int) $this->db->countAffected() < 1) {
            return ['success' => false, 'error' => 'note_delete_denied'];
        }

        $this->touchCustomer($customer_id);
        return ['success' => true];
    }

    public function setNotePinnedForChat(int $chat_id, int $note_id, bool $pinned, int $manager_id = 0): array
    {
        $customer_id = $this->ensureCustomerForChat($chat_id);
        if ($customer_id <= 0) {
            return ['success' => false, 'error' => 'chat_not_found'];
        }

        $note = $this->getNote($note_id);
        if (!$note) {
            return ['success' => false, 'error' => 'note_not_found'];
        }
        if (!$this->noteBelongsToChat($note, $customer_id, $chat_id)) {
            return ['success' => false, 'error' => 'note_pin_denied'];
        }

        $now = $this->now();
        $this->db->query(
            "UPDATE `{$this->prefix}customer_notes`
             SET `is_pinned` = " . ($pinned ? 1 : 0) . ",
                 `pinned_at` = " . ($pinned ? "'" . $this->db->escape($now) . "'" : "NULL") . ",
                 `pinned_by` = " . ($pinned ? (int) $manager_id : 0) . ",
                 `date_modified` = '" . $this->db->escape($now) . "'
             WHERE `note_id` = " . (int) $note_id . "
               AND `customer_id` = " . (int) $customer_id . "
               AND (`chat_id` = " . (int) $chat_id . " OR `chat_id` = 0)"
        );

        $this->touchCustomer($customer_id);
        return ['success' => true];
    }

    public function addTagForChat(int $chat_id, string $label): array
    {
        $label = $this->cleanTagLabel($label);
        if ($label === '') {
            return ['success' => false, 'error' => 'tag_empty'];
        }

        $customer_id = $this->ensureCustomerForChat($chat_id);
        if ($customer_id <= 0) {
            return ['success' => false, 'error' => 'chat_not_found'];
        }

        $tag_id = $this->ensureTag($label);
        $now = $this->now();
        $this->db->query(
            "INSERT INTO `{$this->prefix}customer_tag_links`
                (`customer_id`, `chat_id`, `tag_id`, `date_added`)
             VALUES
                (" . (int) $customer_id . ", " . (int) $chat_id . ", " . (int) $tag_id . ", '" . $this->db->escape($now) . "')
             ON DUPLICATE KEY UPDATE `date_added` = `date_added`"
        );
        $this->touchCustomer($customer_id);

        return ['success' => true, 'tags' => $this->tagsForChat($customer_id, $chat_id)];
    }

    public function removeTagForChat(int $chat_id, int $tag_id): array
    {
        $customer_id = $this->ensureCustomerForChat($chat_id);
        if ($customer_id <= 0) {
            return ['success' => false, 'error' => 'chat_not_found'];
        }
        $this->db->query(
            "DELETE FROM `{$this->prefix}customer_tag_links`
             WHERE `customer_id` = " . (int) $customer_id . "
               AND `chat_id` = " . (int) $chat_id . "
               AND `tag_id` = " . (int) $tag_id
        );
        $this->touchCustomer($customer_id);
        return ['success' => true, 'tags' => $this->tagsForChat($customer_id, $chat_id)];
    }

    public function promptContextForChat(int $chat_id): string
    {
        $customer_id = $this->ensureCustomerForChat($chat_id);
        if ($customer_id <= 0) {
            return '';
        }

        $profiles = $this->profiles($customer_id);
        $linked_accounts = $this->linkedAccounts($customer_id, $this->getChat($chat_id));
        $contacts = (new CustomerContactService($this->registry))->confirmedContextContacts($customer_id);
        $tags = $this->tagsForChat($customer_id, $chat_id);
        $notes = $this->notes($customer_id, $chat_id, 3);
        $lines = [];

        if (!empty($contacts)) {
            $items = [];
            foreach ($contacts as $contact) {
                $label = (string) $contact['type'] === 'phone' ? 'Телефон' : 'Email';
                $prefix = !empty($contact['is_primary']) ? 'основной ' : '';
                $items[] = $prefix . $label . ': ' . (string) $contact['value'];
            }
            $lines[] = 'Подтвержденные контакты клиента: ' . implode('; ', $items);
        }

        if (!empty($profiles)) {
            $items = [];
            foreach (array_slice($profiles, 0, 6) as $profile) {
                $items[] = $profile['provider_label'] . ': ' . $profile['profile_url'];
            }
            $lines[] = 'Подтвержденные профили клиента: ' . implode('; ', $items);
        }

        if (!empty($linked_accounts)) {
            $items = [];
            foreach (array_slice($linked_accounts, 0, 6) as $account) {
                $label = (string) ($account['provider_label'] ?? $account['provider'] ?? '');
                $name = trim((string) ($account['display_name'] ?? ''));
                $username = trim((string) ($account['username'] ?? ''));
                 
                 
                $parts = array_filter([$label, $name, $username !== '' ? '@' . $username : '']);
                if ($parts) {
                    $items[] = implode(' ', $parts);
                }
            }
            if ($items) {
                $lines[] = 'Связанные аккаунты каналов: ' . implode('; ', $items);
            }
        }

        if (!empty($tags)) {
            $labels = array_map(function ($tag) {
                return (string) $tag['label'];
            }, array_slice($tags, 0, 8));
            $lines[] = 'Теги этого диалога: ' . implode(', ', $labels);
        }

        if (!empty($notes)) {
            $note_lines = [];
            foreach ($notes as $note) {
                $note_lines[] = '- ' . $this->limitPlain((string) $note['text'], 240);
            }
            $lines[] = "Заметки оператора:\n" . implode("\n", $note_lines);
        }

         
         
        $commerce_context = (new \EasyChat\Library\Commerce\CustomerCommerceContextService($this->registry))->promptForCustomer($customer_id);
        if ($commerce_context !== '') {
            $lines[] = $commerce_context;
        }

        return $this->limitPlain(implode("\n", $lines), 2600);
    }

    private function detectedProfilesForChat(int $chat_id, int $customer_id): array
    {
        $existing_hashes = $this->profileCandidateSkipHashSet($chat_id, $customer_id);

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

        $detected = [];
        $seen = [];
        foreach ($query->rows as $row) {
            foreach ($this->detector->detect(urldecode((string) $row['text'])) as $profile) {
                $key = $profile['provider'] . '|' . $profile['profile_url_hash'];
                if (isset($seen[$key]) || isset($existing_hashes[$key])) {
                    continue;
                }
                $seen[$key] = true;
                $existing = $this->findProfileByProviderHash($profile['provider'], $profile['profile_url_hash']);
                $conflict = [];
                if ($existing && (int) $existing['customer_id'] !== (int) $customer_id) {
                    $conflict = (new CustomerMergeService($this->registry))->profileConflictPayload($customer_id, $existing, $profile['profile_url']);
                }
                $detected[] = [
                    'provider' => $profile['provider'],
                    'provider_label' => $profile['provider_label'],
                    'profile_url' => $profile['profile_url'],
                    'profile_url_hash' => $profile['profile_url_hash'],
                    'username' => $profile['username'],
                    'message_id' => (int) $row['message_id'],
                    'date_added' => (string) $row['date_added'],
                    'conflict' => $conflict,
                ];
            }
        }

        return array_values($detected);
    }

    private function ensureMessengerContactProfile(int $customer_id, array $chat): void
    {
        $contact_id = (int) ($chat['external_contact_id'] ?? 0);
        if ($contact_id <= 0) {
            return;
        }

        $contact = (new Messenger($this->registry))->getContactById($contact_id);
        if (!$contact) {
            return;
        }

        $profile_url = trim((string) ($contact['profile_url'] ?? ''));
        $normalized = $this->detector->normalizeUrl($profile_url);
        if (!$normalized) {
            return;
        }
        if ($this->isProfileSuppressed($customer_id, $normalized['provider'], $normalized['profile_url_hash'])) {
            $own_profile = [
                'provider' => $normalized['provider'],
                'profile_url_hash' => $normalized['profile_url_hash'],
                'messenger_contact_id' => $contact_id,
            ];
            if (!$this->isOwnChatProfile($own_profile, $chat)) {
                return;
            }
            $this->clearProfileSuppression($customer_id, $normalized['provider'], $normalized['profile_url_hash']);
        }

        $existing = $this->findProfileByProviderHash($normalized['provider'], $normalized['profile_url_hash']);
        if ($existing && (int) $existing['customer_id'] !== $customer_id) {
            return;
        }

        $now = $this->now();
        if ($existing) {
            $this->db->query(
                "UPDATE `{$this->prefix}customer_profiles`
                 SET `messenger_contact_id` = " . (int) $contact_id . ",
                     `external_user_id` = '" . $this->db->escape((string) ($contact['external_user_id'] ?? '')) . "',
                     `username` = '" . $this->db->escape($normalized['username'] ?: (string) ($contact['username'] ?? '')) . "',
                     `date_modified` = '" . $this->db->escape($now) . "'
                 WHERE `profile_id` = " . (int) $existing['profile_id']
            );
            return;
        }

        $this->db->query(
            "INSERT INTO `{$this->prefix}customer_profiles`
             SET `customer_id` = " . (int) $customer_id . ",
                 `provider` = '" . $this->db->escape($normalized['provider']) . "',
                 `profile_url` = '" . $this->db->escape($normalized['profile_url']) . "',
                 `profile_url_hash` = '" . $this->db->escape($normalized['profile_url_hash']) . "',
                 `username` = '" . $this->db->escape($normalized['username'] ?: (string) ($contact['username'] ?? '')) . "',
                 `external_user_id` = '" . $this->db->escape((string) ($contact['external_user_id'] ?? '')) . "',
                 `messenger_contact_id` = " . (int) $contact_id . ",
                 `source` = 'messenger',
                 `confidence` = 100,
                 `added_by` = 0,
                 `message_id` = 0,
                 `date_added` = '" . $this->db->escape($now) . "',
                 `date_modified` = '" . $this->db->escape($now) . "'"
        );
    }

    private function findCustomerByMessengerContact(array $chat): int
    {
        $contact_id = (int) ($chat['external_contact_id'] ?? 0);
        if ($contact_id <= 0) {
            return 0;
        }

        $query = $this->db->query(
            "SELECT `customer_id`
             FROM `{$this->prefix}customer_profiles`
             WHERE `messenger_contact_id` = " . (int) $contact_id . "
             LIMIT 1"
        );
        return isset($query->row['customer_id']) ? (int) $query->row['customer_id'] : 0;
    }

    private function findCustomerByContactIdentity(array $chat): int
    {
        $contact_id = (int) ($chat['external_contact_id'] ?? 0);
        if ($contact_id <= 0) {
            return 0;
        }

        $model_messenger = new Messenger($this->registry);
        $contact = $model_messenger->getContactById($contact_id);
        if (!$contact) {
            return 0;
        }

        $identity = $model_messenger->customerIdentityLookup($contact);
        if (!$identity) {
            return 0;
        }

        $query = $this->db->query(
            "SELECT s.`customer_id`, MAX(s.`date_modified`) AS last_seen
             FROM `{$this->prefix}sessions` s
             INNER JOIN `{$this->prefix}messenger_contacts` mc ON mc.`contact_id` = s.`external_contact_id`
             WHERE mc.`provider` = '" . $this->db->escape($identity['provider']) . "'
               AND mc.`channel_id` = " . (int) $identity['channel_id'] . "
               AND mc.`customer_identity_hash` = '" . $this->db->escape($identity['customer_identity_hash']) . "'
               AND s.`customer_id` IS NOT NULL
               AND s.`customer_id` > 0
             GROUP BY s.`customer_id`
             ORDER BY last_seen DESC, `customer_id` DESC
             LIMIT 1"
        );
        if ($query && $query->num_rows && (int) $query->row['customer_id'] > 0) {
            return (int) $query->row['customer_id'];
        }

        $query = $this->db->query(
            "SELECT p.`customer_id`, MAX(p.`date_modified`) AS last_seen
             FROM `{$this->prefix}customer_profiles` p
             INNER JOIN `{$this->prefix}messenger_contacts` mc ON mc.`contact_id` = p.`messenger_contact_id`
             WHERE mc.`provider` = '" . $this->db->escape($identity['provider']) . "'
               AND mc.`channel_id` = " . (int) $identity['channel_id'] . "
               AND mc.`customer_identity_hash` = '" . $this->db->escape($identity['customer_identity_hash']) . "'
               AND p.`customer_id` > 0
             GROUP BY p.`customer_id`
             ORDER BY last_seen DESC, `customer_id` DESC
             LIMIT 1"
        );

        return ($query && $query->num_rows) ? (int) $query->row['customer_id'] : 0;
    }

    private function linkRelatedChatsToCustomer(int $customer_id, array $chat): void
    {
        $contact_id = (int) ($chat['external_contact_id'] ?? 0);
        if ($customer_id <= 0 || $contact_id <= 0) {
            return;
        }

        $model_messenger = new Messenger($this->registry);
        $contact = $model_messenger->getContactById($contact_id);
        if (!$contact) {
            return;
        }

        $identity = $model_messenger->customerIdentityLookup($contact);
        if (!$identity) {
            return;
        }

         
         
         
         
        $this->db->query(
            "UPDATE `{$this->prefix}sessions` s
             INNER JOIN `{$this->prefix}messenger_contacts` mc ON mc.`contact_id` = s.`external_contact_id`
             SET s.`customer_id` = " . (int) $customer_id . "
             WHERE mc.`provider` = '" . $this->db->escape($identity['provider']) . "'
               AND mc.`channel_id` = " . (int) $identity['channel_id'] . "
               AND mc.`customer_identity_hash` = '" . $this->db->escape($identity['customer_identity_hash']) . "'
               AND (s.`customer_id` IS NULL OR s.`customer_id` = 0 OR s.`customer_id` = " . (int) $customer_id . ")"
        );
    }

    private function createCustomerFromChat(array $chat): int
    {
        $now = $this->now();
        $this->db->query(
            "INSERT INTO `{$this->prefix}customers`
             SET `display_name` = '" . $this->db->escape($this->cleanName((string) ($chat['customer_name'] ?? ''))) . "',
                 `date_added` = '" . $this->db->escape($now) . "',
                 `date_modified` = '" . $this->db->escape($now) . "'"
        );
        return (int) $this->db->getLastId();
    }

    private function syncCustomerFromChat(int $customer_id, array $chat): void
    {
        $customer = $this->getCustomer($customer_id);
        if (!$customer) {
            return;
        }

        $updates = [];
        $name = $this->cleanName((string) ($chat['customer_name'] ?? ''));
        if ($name !== '' && ($this->looksEmptyName((string) $customer['display_name']) || !$this->looksEmptyName($name))) {
            $updates[] = "`display_name` = '" . $this->db->escape($name) . "'";
        }

        if ($updates) {
            $updates[] = "`date_modified` = '" . $this->db->escape($this->now()) . "'";
            $this->db->query(
                "UPDATE `{$this->prefix}customers`
                 SET " . implode(', ', $updates) . "
                 WHERE `customer_id` = " . (int) $customer_id
            );
        }
    }

    private function recentChats(int $customer_id, int $current_chat_id, array $chat = []): array
    {
        $where = "s.`customer_id` = " . (int) $customer_id;
        $identity_where = $this->relatedChatIdentityWhereSql($chat);
        if ($identity_where !== '') {
            $where = "(" . $where . " OR (" . $identity_where . "))";
        }

        $query = $this->db->query(
            "SELECT DISTINCT s.`chat_id`,
                    s.`customer_id`,
                    s.`customer_name`,
                    s.`source_provider`,
                    s.`source_channel_id`,
                    s.`date_modified`,
                    s.`is_archived`,
                    mc.`metadata_json` AS `contact_metadata_json`
             FROM `{$this->prefix}sessions` s
             LEFT JOIN `{$this->prefix}messenger_contacts` mc ON mc.`contact_id` = s.`external_contact_id`
             WHERE {$where}
             ORDER BY s.`date_modified` DESC, s.`chat_id` DESC
             LIMIT 100"
        );

        $rows = [];
        $commerce_contexts = (new CommerceContextService($this->registry))->contextsForChatRows($query->rows, 'index/commerceImage');
        foreach ($query->rows as $row) {
            $row_customer_id = (int) ($row['customer_id'] ?? 0);
            $chat_id = (int) $row['chat_id'];
            $rows[] = [
                'chat_id' => $chat_id,
                'customer_name' => (string) $row['customer_name'],
                'source_provider' => (string) $row['source_provider'],
                'source_label' => $this->sourceLabel((string) $row['source_provider']),
                'date_modified' => (string) $row['date_modified'],
                'is_archived' => (int) $row['is_archived'],
                'commerce_context' => $commerce_contexts[$chat_id] ?? null,
            ];
        }
        return $rows;
    }

    private function recentChatsTotal(int $customer_id, array $chat = []): int
    {
        $join = '';
        $where = "s.`customer_id` = " . (int) $customer_id;
        $identity_where = $this->relatedChatIdentityWhereSql($chat);
        if ($identity_where !== '') {
            $join = "LEFT JOIN `{$this->prefix}messenger_contacts` mc ON mc.`contact_id` = s.`external_contact_id`";
            $where = "(" . $where . " OR (" . $identity_where . "))";
        }

        $query = $this->db->query(
            "SELECT COUNT(DISTINCT s.`chat_id`) AS total
             FROM `{$this->prefix}sessions` s
             {$join}
             WHERE {$where}"
        );

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

    private function relatedChatIdentityWhereSql(array $chat): string
    {
        $contact_id = (int) ($chat['external_contact_id'] ?? 0);
        if ($contact_id <= 0) {
            return '';
        }

        $model_messenger = new Messenger($this->registry);
        $contact = $model_messenger->getContactById($contact_id);
        if (!$contact) {
            return '';
        }

        $identity = $model_messenger->customerIdentityLookup($contact);
        if (!$identity) {
            return '';
        }

        return "mc.`provider` = '" . $this->db->escape($identity['provider']) . "'
               AND mc.`channel_id` = " . (int) $identity['channel_id'] . "
               AND mc.`customer_identity_hash` = '" . $this->db->escape($identity['customer_identity_hash']) . "'";
    }

    private function notes(int $customer_id, int $chat_id, int $limit = 12): array
    {
        $query = $this->db->query(
            "SELECT n.*, m.name AS manager_name, pm.name AS pinned_by_name
             FROM `{$this->prefix}customer_notes` n
             LEFT JOIN `{$this->prefix}managers` m ON m.id = n.manager_id
             LEFT JOIN `{$this->prefix}managers` pm ON pm.id = n.pinned_by
             WHERE n.`customer_id` = " . (int) $customer_id . "
               AND (n.`chat_id` = " . (int) $chat_id . " OR n.`chat_id` = 0)
             ORDER BY n.`is_pinned` DESC, n.`pinned_at` DESC, n.`date_added` DESC, n.`note_id` DESC
             LIMIT " . max(1, min(30, (int) $limit))
        );

        $notes = [];
        foreach ($query->rows as $row) {
            $notes[] = $this->notePayload($row);
        }
        return $notes;
    }

    private function tagsForChat(int $customer_id, int $chat_id): array
    {
        $query = $this->db->query(
            "SELECT t.*
             FROM `{$this->prefix}customer_tag_links` l
             INNER JOIN `{$this->prefix}customer_tags` t ON t.tag_id = l.tag_id
             WHERE l.`customer_id` = " . (int) $customer_id . "
               AND l.`chat_id` = " . (int) $chat_id . "
             ORDER BY t.`label` ASC"
        );
        $tags = [];
        foreach ($query->rows as $row) {
            $tags[] = [
                'tag_id' => (int) $row['tag_id'],
                'label' => (string) $row['label'],
                'color' => (string) $row['color'],
            ];
        }
        return $tags;
    }

    private function ensureTag(string $label): int
    {
        $hash = hash('sha256', mb_strtolower($label, 'UTF-8'));
        $query = $this->db->query(
            "SELECT `tag_id`
             FROM `{$this->prefix}customer_tags`
             WHERE `label_hash` = '" . $this->db->escape($hash) . "'
             LIMIT 1"
        );
        if ($query && $query->num_rows) {
            return (int) $query->row['tag_id'];
        }

        $now = $this->now();
        $this->db->query(
            "INSERT INTO `{$this->prefix}customer_tags`
             SET `label` = '" . $this->db->escape($label) . "',
                 `label_hash` = '" . $this->db->escape($hash) . "',
                 `color` = '" . $this->db->escape($this->tagColor($label)) . "',
                 `date_added` = '" . $this->db->escape($now) . "'"
        );
        return (int) $this->db->getLastId();
    }

    private function getChat(int $chat_id): array
    {
        $query = $this->db->query("SELECT * FROM `{$this->prefix}sessions` WHERE `chat_id` = " . (int) $chat_id . " LIMIT 1");
        return $query->row ?: [];
    }

    private function getCustomer(int $customer_id): array
    {
        $query = $this->db->query("SELECT * FROM `{$this->prefix}customers` WHERE `customer_id` = " . (int) $customer_id . " LIMIT 1");
        return $query->row ?: [];
    }

    private function resolveMergedCustomerId(int $customer_id): int
    {
        $seen = [];
        while ($customer_id > 0 && !isset($seen[$customer_id])) {
            $seen[$customer_id] = true;
            $customer = $this->getCustomer($customer_id);
            if (!$customer) {
                return 0;
            }
            $next_id = (int) ($customer['merged_into_customer_id'] ?? 0);
            if ($next_id <= 0) {
                return $customer_id;
            }
            $customer_id = $next_id;
        }
        return 0;
    }

    private function getProfile(int $profile_id): array
    {
        $query = $this->db->query("SELECT * FROM `{$this->prefix}customer_profiles` WHERE `profile_id` = " . (int) $profile_id . " LIMIT 1");
        return $query->row ?: [];
    }

    private function getNote(int $note_id): array
    {
        $query = $this->db->query("SELECT n.*, m.name AS manager_name, pm.name AS pinned_by_name FROM `{$this->prefix}customer_notes` n LEFT JOIN `{$this->prefix}managers` m ON m.id = n.manager_id LEFT JOIN `{$this->prefix}managers` pm ON pm.id = n.pinned_by WHERE n.`note_id` = " . (int) $note_id . " LIMIT 1");
        return $query->row ?: [];
    }

    private function noteBelongsToChat(array $note, int $customer_id, int $chat_id): bool
    {
        if ((int) ($note['customer_id'] ?? 0) !== (int) $customer_id) {
            return false;
        }
        $note_chat_id = (int) ($note['chat_id'] ?? 0);
        return $note_chat_id === 0 || $note_chat_id === (int) $chat_id;
    }

    private function findProfileByProviderHash(string $provider, string $hash): array
    {
        $query = $this->db->query(
            "SELECT *
             FROM `{$this->prefix}customer_profiles`
             WHERE `provider` = '" . $this->db->escape($provider) . "'
               AND `profile_url_hash` = '" . $this->db->escape($hash) . "'
             LIMIT 1"
        );
        return $query->row ?: [];
    }

    private function lockProfileForUpdate(int $profile_id): array
    {
        $query = $this->db->query(
            "SELECT *
             FROM `{$this->prefix}customer_profiles`
             WHERE `profile_id` = " . (int) $profile_id . "
             LIMIT 1
             FOR UPDATE"
        );
        return $query->row ?: [];
    }

    private function isProfileSuppressed(int $customer_id, string $provider, string $hash): bool
    {
        if ($customer_id <= 0 || $provider === '' || $hash === '') {
            return false;
        }

        try {
            $query = $this->db->query(
                "SELECT `suppression_id`
                 FROM `{$this->prefix}customer_profile_suppressions`
                 WHERE `customer_id` = " . (int) $customer_id . "
                   AND `provider` = '" . $this->db->escape($provider) . "'
                   AND `profile_url_hash` = '" . $this->db->escape($hash) . "'
                 LIMIT 1"
            );
        } catch (\Throwable $e) {
            return false;
        }
        return $query && $query->num_rows > 0;
    }

    private function clearProfileSuppression(int $customer_id, string $provider, string $hash): void
    {
        if ($customer_id <= 0 || $provider === '' || $hash === '') {
            return;
        }

        try {
            $this->db->query(
                "DELETE FROM `{$this->prefix}customer_profile_suppressions`
                 WHERE `customer_id` = " . (int) $customer_id . "
                   AND `provider` = '" . $this->db->escape($provider) . "'
                   AND `profile_url_hash` = '" . $this->db->escape($hash) . "'"
            );
        } catch (\Throwable $e) {
             
             
        }
    }

    private function upsertProfileSuppression(int $customer_id, array $profile, int $manager_id, string $reason_code, string $now): void
    {
        $this->db->query(
            "INSERT INTO `{$this->prefix}customer_profile_suppressions`
             SET `customer_id` = " . (int) $customer_id . ",
                 `provider` = '" . $this->db->escape((string) $profile['provider']) . "',
                 `profile_url` = '" . $this->db->escape((string) $profile['profile_url']) . "',
                 `profile_url_hash` = '" . $this->db->escape((string) $profile['profile_url_hash']) . "',
                 `reason_code` = '" . $this->db->escape($reason_code) . "',
                 `manager_id` = " . (int) $manager_id . ",
                 `source_message_id` = " . (int) ($profile['message_id'] ?? 0) . ",
                 `date_added` = '" . $this->db->escape($now) . "',
                 `date_modified` = '" . $this->db->escape($now) . "'
             ON DUPLICATE KEY UPDATE
                 `profile_url` = VALUES(`profile_url`),
                 `reason_code` = VALUES(`reason_code`),
                 `manager_id` = VALUES(`manager_id`),
                 `source_message_id` = VALUES(`source_message_id`),
                 `date_modified` = VALUES(`date_modified`)"
        );
    }

    private function linkChat(int $chat_id, int $customer_id): void
    {
         
         
         
        $this->db->query(
            "UPDATE `{$this->prefix}sessions`
             SET `customer_id` = " . (int) $customer_id . "
             WHERE `chat_id` = " . (int) $chat_id
        );
    }

    private function touchCustomer(int $customer_id): void
    {
        $this->db->query(
            "UPDATE `{$this->prefix}customers`
             SET `date_modified` = '" . $this->db->escape($this->now()) . "'
             WHERE `customer_id` = " . (int) $customer_id
        );
    }

    private function customerPayload(array $row, array $chat = []): array
    {
        $avatar = $this->avatarPayload($chat);
        return [
            'customer_id' => (int) $row['customer_id'],
            'display_name' => (string) $row['display_name'],
            'avatar_url' => $avatar['avatar_url'],
            'avatar_version' => $avatar['avatar_version'],
            'merged_into_customer_id' => (int) ($row['merged_into_customer_id'] ?? 0),
            'date_added' => (string) $row['date_added'],
            'date_modified' => (string) $row['date_modified'],
        ];
    }

    private function avatarPayload(array $chat): array
    {
        $payload = ['avatar_url' => '', 'avatar_version' => ''];
        $contact_id = (int) ($chat['external_contact_id'] ?? 0);
        if ($contact_id <= 0) {
            return $payload;
        }

        $query = $this->db->query(
            "SELECT *
             FROM `{$this->prefix}messenger_contacts`
             WHERE `contact_id` = " . (int) $contact_id . "
             LIMIT 1"
        );
        if (!$query || !$query->num_rows) {
            return $payload;
        }

        $service = new ContactAvatarService($this->registry);
        return [
            'avatar_url' => $service->avatarUrlFromFields($query->row, 'index/contactAvatar'),
            'avatar_version' => $service->avatarVersionFromFields($query->row),
        ];
    }

    private function infoPayload(array $customer, array $chat, int $chat_id): array
    {
        $primary = (new CustomerContactService($this->registry))->primaryContactsForCustomer((int) $customer['customer_id']);
        $ip_payload = $this->customerIpPayload($chat_id);
        return [
            'primary_email' => $primary['email'],
            'primary_phone' => $primary['phone'],
            'created' => (string) ($chat['date_added'] ?? ''),
            'source_provider' => (string) ($chat['source_provider'] ?? 'widget'),
            'source_label' => $this->sourceLabel((string) ($chat['source_provider'] ?? 'widget')),
            'ips' => $ip_payload['ips'],
            'ip_items' => $ip_payload['ip_items'],
            'ip_summary' => $ip_payload['ip_summary'],
        ];
    }

    private function customerIpPayload(int $chat_id): array
    {
        $items = $this->customerIpItems($chat_id);
        return [
            'ips' => array_values(array_map(function (array $item): string {
                return (string) $item['label'];
            }, $items)),
            'ip_items' => $items,
            'ip_summary' => $this->customerIpSummary($items),
        ];
    }

    private function customerIpItems(int $chat_id): array
    {
        $query = $this->db->query(
            "SELECT `ip`,
                    COUNT(*) AS `message_count`,
                    MAX(`message_id`) AS `last_message_id`,
                    MAX(`date_added`) AS `last_seen`
             FROM `{$this->prefix}messages`
             WHERE `chat_id` = " . (int) $chat_id . "
               AND `author` = 1
               AND `ip` <> ''
             GROUP BY `ip`
             ORDER BY `last_message_id` DESC"
        );

        $dadata_token = trim((string) (new Setting($this->registry))->get('chat_dadata_token', ''));
        $items = [];
        foreach ($query->rows as $row) {
            $ip = trim((string) ($row['ip'] ?? ''));
            if ($ip === '' || filter_var($ip, FILTER_VALIDATE_IP) === false || !$this->canLookupIpGeo($ip)) {
                continue;
            }

            $geo = $this->customerIpGeo($ip, $dadata_token);
            $location = (string) ($geo['location'] ?? '');
            $items[] = [
                'ip' => $ip,
                'location' => $location,
                'label' => $location !== '' ? $ip . ' · ' . $location : $ip,
                'is_public' => true,
                'last_seen' => (string) ($row['last_seen'] ?? ''),
                'count' => (int) ($row['message_count'] ?? 0),
                'geo_status' => (string) ($geo['status'] ?? 'unknown'),
            ];

            if (count($items) >= 5) {
                break;
            }
        }

        return $items;
    }

    private function customerIpSummary(array $items): string
    {
        if (empty($items)) {
            return '';
        }

        $first = $items[0];
        $summary = (string) ($first['label'] ?? $first['ip'] ?? '');
        $extra_count = count($items) - 1;
        if ($extra_count > 0) {
            $summary .= ' +' . $extra_count;
        }

        return $summary;
    }

    private function customerIpGeo(string $ip, string $dadata_token): array
    {
        if (!$this->canLookupIpGeo($ip)) {
            return ['location' => '', 'status' => 'private'];
        }

        if ($dadata_token === '') {
            return ['location' => '', 'status' => 'token_missing'];
        }

        $cached = $this->customerIpGeoCacheGet($ip);
        if ($cached !== null) {
            return $cached;
        }

        $geo = $this->lookupDadataIpLocation($ip, $dadata_token);
        $status = (string) ($geo['status'] ?? 'failed');
        $location = (string) ($geo['location'] ?? '');
        $ttl = $status === 'ok' ? 7 * 86400 : 86400;
        $this->customerIpGeoCacheSet($ip, $location, $status, $ttl);

        return ['location' => $location, 'status' => $status];
    }

    private function canLookupIpGeo(string $ip): bool
    {
        return filter_var(
            $ip,
            FILTER_VALIDATE_IP,
            FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE
        ) !== false;
    }

    private function customerIpGeoCacheGet(string $ip): ?array
    {
        $ip_hash = hash('sha256', $ip);
        $now = $this->now();
        $query = $this->db->query(
            "SELECT `location`, `status`, `expires_at`
             FROM `{$this->prefix}customer_ip_geo_cache`
             WHERE `ip_hash` = '" . $this->db->escape($ip_hash) . "'
             LIMIT 1"
        );

        if (!$query || !$query->num_rows) {
            return null;
        }

        if ((string) ($query->row['expires_at'] ?? '') <= $now) {
            return null;
        }

        return [
            'location' => (string) ($query->row['location'] ?? ''),
            'status' => (string) ($query->row['status'] ?? 'unknown'),
        ];
    }

    private function customerIpGeoCacheSet(string $ip, string $location, string $status, int $ttl_seconds): void
    {
        $allowed_statuses = ['ok', 'not_found', 'failed'];
        if (!in_array($status, $allowed_statuses, true)) {
            $status = 'failed';
        }

        $checked_at = $this->now();
        $expires_at = date('Y-m-d H:i:s', strtotime($checked_at) + max(60, $ttl_seconds));
        $this->db->query(
            "INSERT INTO `{$this->prefix}customer_ip_geo_cache`
                (`ip_hash`, `ip`, `location`, `status`, `checked_at`, `expires_at`)
             VALUES (
                '" . $this->db->escape(hash('sha256', $ip)) . "',
                '" . $this->db->escape($ip) . "',
                '" . $this->db->escape($this->limitPlain($location, 160)) . "',
                '" . $this->db->escape($status) . "',
                '" . $this->db->escape($checked_at) . "',
                '" . $this->db->escape($expires_at) . "'
             )
             ON DUPLICATE KEY UPDATE
                `ip` = VALUES(`ip`),
                `location` = VALUES(`location`),
                `status` = VALUES(`status`),
                `checked_at` = VALUES(`checked_at`),
                `expires_at` = VALUES(`expires_at`)"
        );
    }

    private function lookupDadataIpLocation(string $ip, string $dadata_token): array
    {
        $curl_options = [];
        if (defined('CURLOPT_TIMEOUT_MS')) {
            $curl_options[CURLOPT_TIMEOUT_MS] = 600;
        }

        $response = (new ExternalHttpClient($this->registry))->request(
            'GET',
            'https://suggestions.dadata.ru/suggestions/api/4_1/rs/iplocate/address?ip=' . rawurlencode($ip),
            [
                'headers' => [
                    'Accept: application/json',
                    'Authorization: Token ' . $dadata_token,
                ],
                'timeout' => 1,
                'curl_options' => $curl_options,
                'trust_profile' => 'dadata',
            ]
        );

        if (empty($response['transport_ok']) || empty($response['raw'])) {
            return ['location' => '', 'status' => 'failed'];
        }
        $http_code = (int) ($response['http_code'] ?? 0);
        if ($http_code < 200 || $http_code >= 300) {
            return ['location' => '', 'status' => 'failed'];
        }

        $payload = json_decode((string) $response['raw'], true);
        if (!is_array($payload) || empty($payload['location']['data']) || !is_array($payload['location']['data'])) {
            return ['location' => '', 'status' => 'not_found'];
        }

        $location = $this->dadataLocationLabel($payload['location']['data']);
        return [
            'location' => $location,
            'status' => $location !== '' ? 'ok' : 'not_found',
        ];
    }

    private function dadataLocationLabel(array $location): string
    {
        $city = $this->dadataLocationPart((string) ($location['city'] ?? ''));
        if ($city === '') {
            $city = $this->dadataLocationPart((string) ($location['settlement'] ?? ''));
        }

        $region = $this->dadataLocationPart((string) ($location['region_with_type'] ?? ''));
        if ($region === '') {
            $region = $this->dadataLocationPart((string) ($location['region'] ?? ''));
        }

        $parts = [];
        if ($city !== '') {
            $parts[] = $city;
        }
        if ($region !== '' && !$this->sameDadataLocationPart($region, $city)) {
            $parts[] = $region;
        }

        return implode(', ', $parts);
    }

    private function sameDadataLocationPart(string $left, string $right): bool
    {
        if ($left === '' || $right === '') {
            return false;
        }

        $left = mb_strtolower($left, 'UTF-8');
        $right = mb_strtolower($right, 'UTF-8');
        return mb_strpos($left, $right, 0, 'UTF-8') !== false
            || mb_strpos($right, $left, 0, 'UTF-8') !== false;
    }

    private function dadataLocationPart(string $value): string
    {
        return $this->limitPlain($value, 80);
    }

    private function contactPoints(int $customer_id): array
    {
        return (new CustomerContactService($this->registry))->contactPointsForCustomer($customer_id);
    }

    private function mergeCandidatesFromRecent(int $customer_id, array $recent_chats): array
    {
         
         
         
         
        return [];
    }

    private function profilePayload(array $row, array $chat = []): array
    {
        $unlink_allowed = true;
        $unlink_block_reason = '';
        if ($this->isOwnChatProfile($row, $chat)) {
            $unlink_allowed = false;
            $unlink_block_reason = 'own_chat_profile';
        }

        return [
            'profile_id' => (int) ($row['profile_id'] ?? 0),
            'provider' => (string) ($row['provider'] ?? ''),
            'provider_label' => $this->detector->providerLabel((string) ($row['provider'] ?? '')),
            'profile_url' => (string) ($row['profile_url'] ?? ''),
            'profile_url_hash' => (string) ($row['profile_url_hash'] ?? ''),
            'username' => (string) ($row['username'] ?? ''),
            'source' => (string) ($row['source'] ?? ''),
            'messenger_contact_id' => (int) ($row['messenger_contact_id'] ?? 0),
            'unlink_allowed' => $unlink_allowed ? 1 : 0,
            'unlink_block_reason' => $unlink_block_reason,
            'date_added' => (string) ($row['date_added'] ?? ''),
        ];
    }

    private function isOwnChatProfile(array $profile, array $chat): bool
    {
        if (empty($profile) || empty($chat)) {
            return false;
        }

        $contact_id = (int) ($chat['external_contact_id'] ?? 0);
        if ($contact_id <= 0) {
            return false;
        }

        $profile_contact_id = (int) ($profile['messenger_contact_id'] ?? 0);
        if ($profile_contact_id > 0 && $profile_contact_id === $contact_id) {
            return true;
        }

        $identity = $this->currentChatProfileIdentity($chat);
        if (!$identity) {
            return false;
        }

        return (string) ($profile['provider'] ?? '') === $identity['provider']
            && (string) ($profile['profile_url_hash'] ?? '') === $identity['profile_url_hash'];
    }

    private function currentChatProfileIdentity(array $chat): array
    {
        $contact_id = (int) ($chat['external_contact_id'] ?? 0);
        if ($contact_id <= 0) {
            return [];
        }

        $contact = (new Messenger($this->registry))->getContactById($contact_id);
        if (!$contact) {
            return [];
        }

        $normalized = $this->detector->normalizeUrl((string) ($contact['profile_url'] ?? ''));
        if (!$normalized) {
            return [];
        }

        return [
            'provider' => $normalized['provider'],
            'profile_url_hash' => $normalized['profile_url_hash'],
        ];
    }

    private function notePayload(array $row): array
    {
        return [
            'note_id' => (int) ($row['note_id'] ?? 0),
            'customer_id' => (int) ($row['customer_id'] ?? 0),
            'chat_id' => (int) ($row['chat_id'] ?? 0),
            'manager_id' => (int) ($row['manager_id'] ?? 0),
            'manager_name' => (string) ($row['manager_name'] ?? ''),
            'text' => (string) ($row['text'] ?? ''),
            'is_pinned' => (int) ($row['is_pinned'] ?? 0),
            'pinned_at' => (string) ($row['pinned_at'] ?? ''),
            'pinned_by' => (int) ($row['pinned_by'] ?? 0),
            'pinned_by_name' => (string) ($row['pinned_by_name'] ?? ''),
            'date_added' => (string) ($row['date_added'] ?? ''),
        ];
    }

    private function summaryPayload(array $customer, array $profiles, array $linked_accounts, array $tags, array $notes): array
    {
        $parts = [];
        $profile_total = count($profiles) + count($this->visibleLinkedAccounts($profiles, $linked_accounts));
        if ($profile_total > 0) {
            $parts[] = $profile_total . ' проф./акк.';
        }
        if (!empty($tags)) {
            $parts[] = count($tags) . ' тег.';
        }
        if (!empty($notes)) {
            $parts[] = count($notes) . ' замет.';
        }
        return [
            'title' => $customer['display_name'] ?: 'Клиент',
            'meta' => $parts ? implode(' · ', $parts) : 'Карточка пока пустая',
        ];
    }

    private function visibleLinkedAccounts(array $profiles, array $linked_accounts): array
    {
        $profile_hashes = [];
        $profile_contacts = [];
        foreach ($profiles as $profile) {
            $provider = trim((string) ($profile['provider'] ?? ''));
            $hash = trim((string) ($profile['profile_url_hash'] ?? ''));
            if ($provider !== '' && $hash !== '') {
                $profile_hashes[$provider . '|' . $hash] = true;
            }
            $contact_id = (int) ($profile['messenger_contact_id'] ?? 0);
            if ($provider !== '' && $contact_id > 0) {
                $profile_contacts[$provider . '|' . $contact_id] = true;
            }
        }

        return array_values(array_filter($linked_accounts, function ($account) use ($profile_hashes, $profile_contacts) {
            $provider = trim((string) ($account['provider'] ?? ''));
            $contact_id = (int) ($account['messenger_contact_id'] ?? 0);
            if ($provider !== '' && $contact_id > 0 && isset($profile_contacts[$provider . '|' . $contact_id])) {
                return false;
            }
            foreach (['profile_url_hash', 'account_profile_url_hash'] as $field) {
                $hash = trim((string) ($account[$field] ?? ''));
                if ($hash !== '' && isset($profile_hashes[$provider . '|' . $hash])) {
                    return false;
                }
            }
            return true;
        }));
    }

    private function cleanName(string $value): string
    {
        return $this->limitPlain($value, 255);
    }

    private function cleanNoteText(string $value): string
    {
        return $this->limitPlain($value, 2000);
    }

    private function cleanTagLabel(string $value): string
    {
        $value = preg_replace('/\s+/u', ' ', trim($value));
        return mb_substr((string) $value, 0, 80, 'UTF-8');
    }

    private function limitPlain(string $value, int $limit): string
    {
        $value = trim(strip_tags($value));
        $value = preg_replace('/[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]/u', '', $value);
        return mb_substr((string) $value, 0, $limit, 'UTF-8');
    }

    private function looksEmptyName(string $value): bool
    {
        $value = trim(mb_strtolower($value, 'UTF-8'));
        return $value === '' || $value === 'гость' || $value === 'guest' || $value === 'покупатель';
    }

    private function normalizeProfileSource(string $source): string
    {
        $source = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', $source));
        return in_array($source, ['operator', 'message', 'messenger', 'android', 'manual'], true) ? $source : 'operator';
    }

    private function normalizeProfileUnlinkReason(string $reason): string
    {
        $reason = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', $reason));
        return in_array($reason, ['wrong_customer', 'outdated', 'duplicate', 'privacy_request', 'manual'], true)
            ? $reason
            : 'wrong_customer';
    }

    private function logProfileUnlinkFailure(\Throwable $e, array $context): void
    {
        try {
            (new Log('customer_profile_unlink'))->write([
                'message' => $e->getMessage(),
                'code' => $e->getCode(),
                'file' => basename($e->getFile()),
                'line' => $e->getLine(),
                'context' => $context,
            ]);
        } catch (\Throwable $ignored) {
             
        }
    }

    private function tagColor(string $label): string
    {
        $palette = ['#DBEAFE', '#DCFCE7', '#FEF3C7', '#FCE7F3', '#EDE9FE', '#FEE2E2'];
        return $palette[abs(crc32($label)) % count($palette)];
    }

    private function sourceLabel(string $provider): string
    {
        return ChannelCatalog::label($provider === '' ? 'widget' : $provider, true);
    }

    private function now(): string
    {
        return (new AppClock($this->registry))->nowSql();
    }
}
