<?php
namespace EasyChat\Library;

use EasyChat\Library\Ai\AiResponseTimingPolicy;
use EasyChat\Library\Ai\PromptDefaults;
use EasyChat\Library\Catalog\CmsCatalogCapability;
use EasyChat\Library\Install\InstallationAttributionReporter;
use EasyChat\Library\Messenger\MaxConversationIdentity;
use EasyChat\Library\Messenger\ReplyCapability;
use EasyChat\Library\Security\PasswordCredentialService;

class InstallService {
    const SETTINGS_KEY_MAX_LENGTH = 191;

    public static function testConnection(array $params) {
        $host = self::stringValue($params, 'host');
        $port = self::stringValue($params, 'port', '3306');
        $user = self::stringValue($params, 'user');
        $password = self::stringValue($params, 'password');
        $database = self::stringValue($params, 'database');

        if ($host === '' || $user === '' || $database === '') {
            return array('success' => false, 'error' => 'Заполните обязательные поля (Хост, Пользователь, Имя БД)');
        }

        $connection = self::connectMysqli($host, $user, $password, $database, (int)$port);
        if (!$connection['success']) {
            return array('success' => false, 'error' => 'Ошибка подключения: ' . $connection['error']);
        }

        $conn = $connection['conn'];
        $conn->close();

        return array('success' => true);
    }

    public static function install(array $params) {
        $host = self::stringValue($params, 'host');
        $port = self::stringValue($params, 'port', '3306');
        $user = self::stringValue($params, 'user');
        $password = self::stringValue($params, 'password');
        $database = self::stringValue($params, 'database');
        $prefix = self::normalizePrefix(self::stringValue($params, 'prefix', 'easychat_'));
        $admin_login = self::stringValue($params, 'admin_login');
        $admin_pass = self::stringValue($params, 'admin_password');
        $app_path = rtrim(self::stringValue($params, 'app_path', defined('SITE_PATH') ? SITE_PATH : dirname(__DIR__)), '/\\');
        $runtime_path = rtrim(self::stringValue($params, 'runtime_path', $app_path), '/\\');
        $write_config = self::stringValue($params, 'write_config', '1') !== '0';
        $skip_schema = self::stringValue($params, 'skip_schema', '0') === '1';
        $version = self::stringValue($params, 'version', self::detectVersion($app_path));
        $edition = self::stringValue($params, 'edition', 'standalone');
        $cms_cron_mode = self::stringValue($params, 'cms_cron_mode', 'standalone');
        $cms_type = self::normalizeCmsType(self::stringValue($params, 'cms_type', self::cmsTypeForEdition($edition)));
        $cms_prefix = self::normalizeCmsPrefix(self::stringValue($params, 'cms_prefix', ''));
        $admin_locale = I18n::normalizeLocale(self::stringValue($params, 'admin_locale', 'ru'));
        $widget_locale = I18n::normalizeLocale(self::stringValue($params, 'widget_locale', $admin_locale));
        $locale_source = self::stringValue($params, 'locale_source', 'manual') === 'cms' ? 'cms' : 'manual';
        $install_id = 'inst_' . bin2hex(random_bytes(16));
        $install_secret = 'instsec_' . bin2hex(random_bytes(32));

        if ($host === '' || $user === '' || $database === '' || $admin_login === '' || $admin_pass === '') {
            return array('success' => false, 'error' => 'Заполните все обязательные поля формы');
        }

        if (!preg_match('/^[a-zA-Z0-9_]+$/', $prefix)) {
            return array('success' => false, 'error' => 'Префикс таблиц может содержать только латинские буквы, цифры и underscore');
        }

        $consent = InstallationConsent::fromInstallParams($params, $edition . '_installer');
        if (empty($consent['success'])) {
            return array('success' => false, 'error' => (string)$consent['error']);
        }

         
         
         
        $runtime_ready = self::ensureRuntimeDirs($runtime_path);
        if (empty($runtime_ready['success'])) {
            return $runtime_ready;
        }

        $install_mutex = self::acquireInstallMutex($runtime_path);
        if (empty($install_mutex['success'])) {
            return array('success' => false, 'error' => (string)$install_mutex['error']);
        }
        $install_mutex_handle = $install_mutex['handle'];

        try {
            $config_path = $app_path . DIRECTORY_SEPARATOR . 'config.php';
            if ($write_config && is_file($config_path)) {
                $existing_config = SafeRuntime::readFile($config_path);
                $config_guard = self::validateExistingConfigConnection($existing_config, array(
                    'host' => $host,
                    'port' => $port,
                    'user' => $user,
                    'password' => $password,
                    'database' => $database,
                    'prefix' => $prefix,
                ));
                if (empty($config_guard['success'])) {
                    return $config_guard;
                }
            }

            $connection = self::connectMysqli($host, $user, $password, $database, (int)$port);
            if (!$connection['success']) {
                return array('success' => false, 'error' => 'Не удалось подключиться к БД: ' . $connection['error']);
            }

            $conn = $connection['conn'];
            $conn->set_charset('utf8mb4');

            if (!$skip_schema) {
                $queries = self::schemaQueries($prefix);
                foreach ($queries as $sql) {
                    if (!$conn->query($sql)) {
                        $error = $conn->error;
                        $conn->close();
                        return array('success' => false, 'error' => 'Ошибка создания таблицы: ' . $error);
                    }
                }
            }

            $result = self::createAdmin($conn, $prefix, $admin_login, $admin_pass);
            if (!$result['success']) {
                $conn->close();
                return $result;
            }

            $onboarding_settings = self::firstLaunchConsentSettings($params, $edition);
            $widget_base_domain = self::widgetBaseDomainFromParams($params);
            $admin_created = !empty($result['created']);
            $result = self::saveDefaultSettings($conn, $prefix, $install_id, $install_secret, $admin_pass, $admin_locale, $widget_locale, $locale_source, $cms_type, $cms_prefix, array_merge($consent['settings'], $onboarding_settings), $widget_base_domain, $admin_created);
            if (!$result['success']) {
                $conn->close();
                return $result;
            }

            if ($write_config) {
                $config_content = self::buildConfig(array(
                    'host' => $host,
                    'port' => $port,
                    'user' => $user,
                    'password' => $password,
                    'database' => $database,
                    'prefix' => $prefix,
                    'admin_login' => $admin_login,
                    'admin_password' => $admin_pass,
                    'edition' => $edition,
                    'cms_cron_mode' => $cms_cron_mode,
                ));

                if (!is_file($config_path) && !SafeRuntime::writeFileAtomic($config_path, $config_content, 0640)) {
                    $conn->close();
                    return array('success' => false, 'error' => 'Не удалось создать config.php. Пожалуйста, предоставьте EasyChat права на запись.');
                }

                if (!is_file($config_path)) {
                    $conn->close();
                    return array('success' => false, 'error' => 'Не удалось создать config.php. Пожалуйста, предоставьте EasyChat права на запись.');
                }

                if (function_exists('opcache_invalidate')) {
                    SafeRuntime::call(function () use ($config_path) {
                        opcache_invalidate($config_path, true);
                        return true;
                    }, false);
                }
            }

            $gift_result = EasyTokensGiftProvisioner::provisionWithMysqli($conn, $prefix, array(
                'claim_easytokens_gift' => self::stringValue($params, 'claim_easytokens_gift', '0'),
                'admin_email' => self::stringValue($params, 'admin_email'),
                'edition' => $edition,
                'version' => $version,
                'site_url' => self::stringValue($params, 'site_url', defined('HTTP_SERVER') ? HTTP_SERVER : ''),
            ));
            if (($onboarding_settings['chat_first_launch_consent_required'] ?? '0') !== '1'
                && self::truthy(self::stringValue($params, 'claim_easytokens_gift', '0'))
                && !empty($gift_result['success'])
            ) {
                $key_query = $conn->query(
                    "SELECT `value` FROM `{$prefix}settings` WHERE `key` = 'chat_ai_key' LIMIT 1"
                );
                $installed_ai_key = $key_query && $key_query->num_rows
                    ? trim((string)($key_query->fetch_assoc()['value'] ?? ''))
                    : '';
                if ($installed_ai_key !== '') {
                    foreach (array('chat_ai_status', 'chat_ai_suggestions_status') as $ai_setting_key) {
                        $stmt = $conn->prepare(
                            "INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES (?, '1')
                             ON DUPLICATE KEY UPDATE `value` = '1'"
                        );
                        if ($stmt) {
                            $stmt->bind_param('s', $ai_setting_key);
                            $stmt->execute();
                            $stmt->close();
                        }
                    }
                }
            }
            $attribution_result = InstallationAttributionReporter::reportWithMysqli($conn, $prefix, array(
                'install_code' => self::stringValue($params, 'install_code'),
                'easychat_services_consent' => self::stringValue($params, 'easychat_services_consent', '0'),
                'edition' => $edition,
                'cms' => $cms_type,
                'version' => $version,
                'site_url' => self::stringValue($params, 'site_url', defined('HTTP_SERVER') ? HTTP_SERVER : ''),
            ));

            $conn->close();

            return array(
                'success' => true,
                'gift' => $gift_result,
                'attribution' => $attribution_result,
                'admin_created' => $admin_created,
            );
        } finally {
            self::releaseInstallMutex($install_mutex_handle);
        }
    }

    public static function ensureRuntimeSchema($db, $prefix) {
        if (!$db || $prefix === '') {
            return;
        }

        try {
            self::ensureModerationTokensTable($db, $prefix);
            self::ensureCrmSchemaOnce($db, $prefix);
            self::ensureCrmMultiProviderSchemaOnce($db, $prefix);
            self::ensureMessengerSchemaOnce($db, $prefix);
            self::ensureMessengerProviderIncidentSchemaOnce($db, $prefix);
            self::ensureEmailTransportSchemaOnce($db, $prefix);
            self::ensureMessageAttachmentIdentitySchemaOnce($db, $prefix);
            self::ensureMaxIdentityRepairSchemaOnce($db, $prefix);
            self::ensureMaxChatTypeProjectionRepairOnce($db, $prefix);
            self::ensureMarketplaceSchemaOnce($db, $prefix);
            self::ensureMarketplaceThreadHashSchemaOnce($db, $prefix);
            self::ensureMarketplaceActionResourceSchemaOnce($db, $prefix);
            self::ensureMarketplaceSyncHintSchemaOnce($db, $prefix);
            self::ensureMarketplaceReliabilitySchemaOnce($db, $prefix);
            self::ensureConversationStartSchemaOnce($db, $prefix);
            self::ensureExternalChatDeletionSchemaOnce($db, $prefix);
            self::ensureMaxBotProviderRenameOnce($db, $prefix);
            self::ensurePersonalMaxRetiredOnce($db, $prefix);
            self::ensureWhatsAppPersonalSchemaOnce($db, $prefix);
            self::ensureContactAvatarSchemaOnce($db, $prefix);
            self::ensureContactAvatarIdentitySchemaOnce($db, $prefix);
            self::ensureContactAvatarFileWidthOnce($db, $prefix);
            self::ensureCustomerIdentitySchemaOnce($db, $prefix);
            self::ensureAvitoMessageContextSchemaOnce($db, $prefix);
            self::ensureMessengerItemContextCacheSchemaOnce($db, $prefix);
            self::ensureMarketplaceItemContextProjectionOnce($db, $prefix);
            self::ensureAiProcessingSchemaOnce($db, $prefix);
            self::ensureWidgetUnreadSchemaOnce($db, $prefix);
            self::ensureCustomerPanelSchemaOnce($db, $prefix);
            self::ensureCommerceIdentitySchemaOnce($db, $prefix);
            self::ensureCmsVisitorIdentitySchemaOnce($db, $prefix);
             
             
             
            self::ensureOperatorProfileAndReadReceiptSchemaOnce($db, $prefix);
            self::ensureCustomerProfileUnlinkSchemaOnce($db, $prefix);
            self::ensureCustomerNotesPinnedSchemaOnce($db, $prefix);
            self::ensureCustomerMergeSchemaOnce($db, $prefix);
            self::ensureCustomerContactPointCrmSchemaOnce($db, $prefix);
            self::ensureCustomerIpGeoCacheSchemaOnce($db, $prefix);
            self::ensureBlockedSchemaOnce($db, $prefix);
            self::ensureMessageManagerNameSchemaOnce($db, $prefix);
            self::ensureChatAssignmentsSchemaOnce($db, $prefix);
            self::ensureChatAssignmentPermissionsSchemaOnce($db, $prefix);
            self::ensureOperatorDraftSchemaOnce($db, $prefix);
            self::ensureAndroidSyncSchemaOnce($db, $prefix);
            self::ensureDirectMessageIdempotencySchemaOnce($db, $prefix);
            self::ensureChatDeletionSourceSchemaOnce($db, $prefix);
            self::ensureAdminPollingSchemaOnce($db, $prefix);
            self::ensureChatListPerformanceSchemaOnce($db, $prefix);
            self::ensureChatListFilterSchemaOnce($db, $prefix);
            self::ensureAdminOpenChatPerformanceSchemaOnce($db, $prefix);
            self::ensureResponseTimeMetricsSchemaOnce($db, $prefix);
            self::ensureResponseTimeMetricsCleanupOnce($db, $prefix);
            self::ensureAppSessionHealthSchemaOnce($db, $prefix);
            self::ensureAndroidDeviceVersionSchemaOnce($db, $prefix);
            self::ensureQuickRepliesSchemaOnce($db, $prefix);
            self::ensureAiSuggestionsSchemaOnce($db, $prefix);
            self::ensureCopilotGhostDefaultOnce($db, $prefix);
            self::ensureAdminHelpSchemaOnce($db, $prefix);
            self::ensureAdminHelpLaunchOnce($db, $prefix);
            self::ensureWidgetOperatorVisibilitySchemaOnce($db, $prefix);
            self::ensureAppPairingSchemaOnce($db, $prefix);
            self::ensurePasskeySchemaOnce($db, $prefix);
            self::ensureOutboundContactSchemaOnce($db, $prefix);
            self::ensureOutboundContactReliabilitySchemaOnce($db, $prefix);
            self::ensureSmsInboundSchemaOnce($db, $prefix);
            self::ensureSmsDiagnosticsSchemaOnce($db, $prefix);
            self::ensureSmsCarrierEvidenceSchemaOnce($db, $prefix);
            self::ensureSmsReliabilitySchemaOnce($db, $prefix);
            self::ensureSmsInboundHealthSchemaOnce($db, $prefix);
            self::ensureAppointmentRemindersRemovedOnce($db, $prefix);
            self::ensureSmsReceiptSchemaOnce($db, $prefix);
             
             
             
            self::ensureAndroidDeviceIdentitySchemaOnce($db, $prefix);
            self::ensureScheduledMessagesSchemaOnce($db, $prefix);
            self::ensureRuntimeTimezoneSetting($db, $prefix);
            self::ensureCentralGatewayApiSetting($db, $prefix);
            self::ensureAiResponseTimingSettings($db, $prefix);
            self::ensureAiVisualPromptMigration($db, $prefix);
            self::ensureWidgetBaseDomainSetting($db, $prefix);
            self::ensureCatalogKnowledgeSchemaOnce($db, $prefix);
            self::ensureCatalogSettingsPersistenceOnce($db, $prefix);
            self::ensureCatalogPublicUrlSchemaOnce($db, $prefix);
            self::enqueueVkCallbackMessageReplyRepairOnce($db, $prefix);

            $migration_key = 'schema_migration_20260524_managers_presence';
            $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
            if ($query && $query->num_rows) {
                return;
            }

            self::ensureColumn($db, $prefix . 'managers', 'image', "`image` VARCHAR(255) DEFAULT NULL");
            self::ensureColumn($db, $prefix . 'managers', 'role', "`role` VARCHAR(16) NOT NULL DEFAULT 'manager'");
            self::ensureColumn($db, $prefix . 'managers', 'permission_delete_chats', "`permission_delete_chats` INT(1) NOT NULL DEFAULT 0");
            self::ensureColumn($db, $prefix . 'managers', 'ip', "`ip` VARCHAR(45) DEFAULT NULL");
            self::ensureColumn($db, $prefix . 'managers', 'last_active', "`last_active` DATETIME DEFAULT NULL");

            $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
        } catch (Exception $e) {
             
            throw $e;
        }
    }

    private static function ensureCrmSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260809_crm_integrations_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key`='" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) return;
        foreach (self::crmSchemaQueries($prefix) as $sql) $db->query($sql);
        $db->query("INSERT INTO `{$prefix}settings` (`key`,`value`) VALUES ('" . $db->escape($migration_key) . "','1') ON DUPLICATE KEY UPDATE `value`='1'");
    }

    



    private static function ensureEmailTransportSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260811_email_transport_v2';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key`='" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) return;

        foreach (self::emailTransportSchemaQueries($prefix) as $sql) $db->query($sql);

         
         
        $channels = $db->query("SELECT `channel_id`,`settings_json` FROM `{$prefix}messenger_channels` WHERE `provider`='email'");
        foreach ((array)($channels->rows ?? array()) as $channel) {
            $settings = json_decode((string)$channel['settings_json'], true);
            $settings = is_array($settings) ? $settings : array();
            $channel_id = (int)$channel['channel_id'];
            $initialized = !empty($settings['imap_cursor_initialized']) ? 1 : 0;
            $last_uid = max(0, (int)($settings['imap_last_uid'] ?? 0));
            $uidvalidity = max(0, (int)($settings['imap_uidvalidity'] ?? 0));
            $transport = preg_replace('/[^a-z0-9_\-]/i', '', (string)($settings['outbound_transport'] ?? 'direct_smtp'));
            if ($transport === '') $transport = 'direct_smtp';
            $db->query("INSERT INTO `{$prefix}email_channel_state` SET
                `channel_id`={$channel_id}, `config_revision`=1,
                `imap_cursor_initialized`={$initialized}, `imap_last_uid`={$last_uid},
                `imap_uidvalidity`={$uidvalidity},
                `selected_outbound_transport`='" . $db->escape($transport) . "',
                `inbound_status`='stale', `outbound_status`='stale',
                `deliverability_status`='not_checked', `diagnostics_json`='{}',
                `date_modified`=NOW()
                ON DUPLICATE KEY UPDATE `channel_id`=VALUES(`channel_id`)");
        }

        $db->query("INSERT INTO `{$prefix}settings` (`key`,`value`) VALUES ('" . $db->escape($migration_key) . "','1') ON DUPLICATE KEY UPDATE `value`='1'");
    }

    private static function emailTransportSchemaQueries($prefix) {
        return array(
            "CREATE TABLE IF NOT EXISTS `{$prefix}email_channel_state` (
                `channel_id` INT UNSIGNED NOT NULL PRIMARY KEY,
                `config_revision` INT UNSIGNED NOT NULL DEFAULT 1,
                `imap_cursor_initialized` TINYINT(1) NOT NULL DEFAULT 0,
                `imap_last_uid` BIGINT UNSIGNED NOT NULL DEFAULT 0,
                `imap_uidvalidity` BIGINT UNSIGNED NOT NULL DEFAULT 0,
                `selected_outbound_transport` VARCHAR(32) NOT NULL DEFAULT 'direct_smtp',
                `inbound_status` VARCHAR(24) NOT NULL DEFAULT 'not_checked',
                `inbound_code` VARCHAR(64) NOT NULL DEFAULT '',
                `inbound_checked_at` DATETIME DEFAULT NULL,
                `outbound_status` VARCHAR(24) NOT NULL DEFAULT 'not_checked',
                `outbound_code` VARCHAR(64) NOT NULL DEFAULT '',
                `outbound_checked_at` DATETIME DEFAULT NULL,
                `deliverability_status` VARCHAR(24) NOT NULL DEFAULT 'not_checked',
                `deliverability_code` VARCHAR(64) NOT NULL DEFAULT '',
                `deliverability_checked_at` DATETIME DEFAULT NULL,
                `diagnostics_json` TEXT NOT NULL,
                `date_modified` DATETIME NOT NULL,
                KEY `idx_email_health` (`inbound_status`,`outbound_status`,`date_modified`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            "CREATE TABLE IF NOT EXISTS `{$prefix}email_connection_checks` (
                `check_id` CHAR(32) NOT NULL PRIMARY KEY,
                `channel_id` INT UNSIGNED NULL DEFAULT NULL,
                `config_fingerprint` CHAR(64) NOT NULL,
                `activation_ready` TINYINT(1) NOT NULL DEFAULT 0,
                `result_json` TEXT NOT NULL,
                `expires_at` DATETIME NOT NULL,
                `date_added` DATETIME NOT NULL,
                KEY `idx_email_check_expiry` (`expires_at`),
                KEY `idx_email_check_channel` (`channel_id`,`date_added`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            "CREATE TABLE IF NOT EXISTS `{$prefix}email_delivery_evidence` (
                `delivery_id` INT UNSIGNED NOT NULL PRIMARY KEY,
                `transport_code` VARCHAR(32) NOT NULL,
                `idempotency_key` CHAR(64) NOT NULL,
                `provider_message_id` VARCHAR(128) NOT NULL DEFAULT '',
                `provider_status` VARCHAR(32) NOT NULL DEFAULT 'pending',
                `outcome_certainty` ENUM('known','unknown') NOT NULL DEFAULT 'known',
                `evidence_json` TEXT NOT NULL,
                `last_checked_at` DATETIME DEFAULT NULL,
                `date_modified` DATETIME NOT NULL,
                UNIQUE KEY `uniq_email_idempotency` (`idempotency_key`),
                KEY `idx_email_provider_message` (`transport_code`,`provider_message_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            "CREATE TABLE IF NOT EXISTS `{$prefix}email_thread_index` (
                `thread_index_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `channel_id` INT UNSIGNED NOT NULL,
                `chat_id` INT(11) NOT NULL,
                `message_id` INT(11) NULL DEFAULT NULL,
                `mail_message_id` VARCHAR(512) NOT NULL,
                `mail_message_hash` CHAR(64) NOT NULL,
                `date_added` DATETIME NOT NULL,
                UNIQUE KEY `uniq_email_message_hash` (`channel_id`,`mail_message_hash`),
                KEY `idx_email_thread_chat` (`channel_id`,`chat_id`,`date_added`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        );
    }

    private static function crmSchemaQueries($prefix) {
        return array(
            "CREATE TABLE IF NOT EXISTS `{$prefix}crm_connections` (
                `connection_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `provider` VARCHAR(32) NOT NULL,
                `name` VARCHAR(128) NOT NULL DEFAULT '',
                `status` ENUM('disabled','active') NOT NULL DEFAULT 'disabled',
                `credentials_ciphertext` MEDIUMTEXT NOT NULL,
                `settings_json` TEXT NOT NULL,
                `last_check_at` DATETIME DEFAULT NULL,
                `last_success_at` DATETIME DEFAULT NULL,
                `last_error` VARCHAR(255) NOT NULL DEFAULT '',
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                UNIQUE KEY `uniq_crm_provider_name` (`provider`,`name`),
                INDEX `idx_crm_status` (`status`,`provider`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            "CREATE TABLE IF NOT EXISTS `{$prefix}crm_conversation_links` (
                `link_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `connection_id` INT UNSIGNED NOT NULL,
                `chat_id` INT(11) NOT NULL,
                `entity_type` VARCHAR(24) NOT NULL,
                `external_id` VARCHAR(64) NOT NULL,
                `external_url` VARCHAR(512) NOT NULL DEFAULT '',
                `last_message_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                UNIQUE KEY `uniq_crm_chat` (`connection_id`,`chat_id`),
                INDEX `idx_crm_external` (`connection_id`,`entity_type`,`external_id`),
                INDEX `idx_crm_link_modified` (`date_modified`,`link_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        );
    }

    private static function ensureCrmMultiProviderSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260809_crm_multi_provider_v2';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key`='" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) return;
        self::ensureColumn($db, $prefix . 'crm_conversation_links', 'external_contact_id', "`external_contact_id` VARCHAR(64) NOT NULL DEFAULT '' AFTER `external_id`");
        self::ensureColumn($db, $prefix . 'crm_conversation_links', 'origin_key', "`origin_key` VARCHAR(64) NOT NULL DEFAULT '' AFTER `external_contact_id`");
        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}crm_message_deliveries` (
            `delivery_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `connection_id` INT UNSIGNED NOT NULL,
            `chat_id` INT(11) NOT NULL,
            `message_id` INT UNSIGNED NOT NULL,
            `operation` VARCHAR(32) NOT NULL,
            `state` ENUM('inflight','uncertain','succeeded','failed','ambiguous') NOT NULL DEFAULT 'inflight',
            `idempotency_marker` VARCHAR(64) NOT NULL DEFAULT '',
            `payload_hash` CHAR(64) NOT NULL DEFAULT '',
            `external_operation_id` VARCHAR(64) NOT NULL DEFAULT '',
            `attempts` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            `last_http_status` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            `last_error` VARCHAR(500) NOT NULL DEFAULT '',
            `finished_at` DATETIME DEFAULT NULL,
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            UNIQUE KEY `uniq_crm_delivery` (`connection_id`,`chat_id`,`message_id`,`operation`),
            INDEX `idx_crm_delivery_state` (`state`,`date_modified`,`delivery_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}crm_rate_limits` (
            `connection_id` INT UNSIGNED NOT NULL PRIMARY KEY,
            `next_allowed_at_ms` BIGINT UNSIGNED NOT NULL DEFAULT 0,
            `date_modified` DATETIME NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        $db->query("INSERT INTO `{$prefix}settings` (`key`,`value`) VALUES ('" . $db->escape($migration_key) . "','1') ON DUPLICATE KEY UPDATE `value`='1'");
    }

    







    private static function ensureOperatorProfileAndReadReceiptSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260731_operator_profile_read_receipts_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureColumn(
            $db,
            $prefix . 'sessions',
            'read_receipt_capability',
            "`read_receipt_capability` VARCHAR(24) NOT NULL DEFAULT 'unsupported' AFTER `external_contact_id`"
        );
        self::ensureColumn(
            $db,
            $prefix . 'messages',
            'customer_read_at',
            "`customer_read_at` DATETIME DEFAULT NULL AFTER `status_user`"
        );
        self::ensureColumn(
            $db,
            $prefix . 'messages',
            'customer_read_source',
            "`customer_read_source` VARCHAR(24) NOT NULL DEFAULT '' AFTER `customer_read_at`"
        );
        self::ensureColumn(
            $db,
            $prefix . 'messenger_deliveries',
            'provider_cursor',
            "`provider_cursor` BIGINT UNSIGNED DEFAULT NULL AFTER `provider_occurred_at`"
        );
        self::ensureColumn(
            $db,
            $prefix . 'customer_contact_points',
            'verification_source',
            "`verification_source` VARCHAR(32) NOT NULL DEFAULT '' AFTER `status`"
        );
        self::ensureColumn(
            $db,
            $prefix . 'customer_contact_points',
            'verified_at',
            "`verified_at` DATETIME DEFAULT NULL AFTER `verification_source`"
        );

        self::ensureIndex(
            $db,
            $prefix . 'messages',
            'idx_customer_read_projection',
            "`chat_id`, `author`, `status_user`, `message_id`"
        );
        self::ensureIndex(
            $db,
            $prefix . 'messenger_deliveries',
            'idx_provider_read_cursor',
            "`channel_id`, `direction`, `provider_cursor`, `message_id`"
        );

        $db->query(self::authRateLimitSchemaQuery($prefix));
        $db->query(self::messageReadCheckpointSchemaQuery($prefix));
        $db->query(
            "UPDATE `{$prefix}sessions`
             SET `read_receipt_capability` = 'widget_visible'
             WHERE `source_provider` = 'widget'
               AND `read_receipt_capability` = 'unsupported'"
        );

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    






    private static function ensureWhatsAppPersonalSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260801_whatsapp_personal_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}whatsapp_personal_identities` (
            `identity_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `channel_id` INT UNSIGNED NOT NULL,
            `identity_jid` VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
            `phone_jid` VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
            `lid_jid` VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
            `display_name` VARCHAR(191) NOT NULL DEFAULT '',
            `avatar_url` VARCHAR(1024) NOT NULL DEFAULT '',
            `evidence` VARCHAR(32) NOT NULL DEFAULT '',
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            UNIQUE KEY `uniq_whatsapp_identity` (`channel_id`, `identity_jid`),
            KEY `idx_whatsapp_phone` (`channel_id`, `phone_jid`),
            KEY `idx_whatsapp_lid` (`channel_id`, `lid_jid`),
            KEY `idx_whatsapp_modified` (`channel_id`, `date_modified`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    





    private static function ensureMaxChatTypeProjectionRepairOnce($db, $prefix) {
        $migration_key = 'data_migration_20260803_max_chat_type_projection_v1';
        $state_query = $db->query(
            "SELECT `value` FROM `{$prefix}settings`
             WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1"
        );
        $state = $state_query && $state_query->num_rows
            ? trim((string)($state_query->row['value'] ?? ''))
            : '';
        if ($state === 'done') {
            return;
        }

        $cursor = ctype_digit($state) ? max(0, (int)$state) : 0;
        $limit = 200;
        $contacts = $db->query(
            "SELECT `contact_id`, `metadata_json`
             FROM `{$prefix}messenger_contacts`
             WHERE `provider` = 'max'
               AND `contact_id` > " . $cursor . "
             ORDER BY `contact_id` ASC
             LIMIT " . $limit
        );
        $rows = $contacts && isset($contacts->rows) && is_array($contacts->rows) ? $contacts->rows : [];
        $next_cursor = $cursor;

        $db->query('START TRANSACTION');
        try {
            foreach ($rows as $contact) {
                $contact_id = (int)($contact['contact_id'] ?? 0);
                $next_cursor = max($next_cursor, $contact_id);
                if ($contact_id <= 0) {
                    continue;
                }

                $metadata = json_decode((string)($contact['metadata_json'] ?? ''), true);
                $metadata = is_array($metadata) ? $metadata : [];
                $chat_type = MaxConversationIdentity::chatTypeFromMetadata($metadata);
                $repaired = array_merge($metadata, ReplyCapability::metadataForMaxChatType($chat_type));
                if ($repaired === $metadata) {
                    continue;
                }

                $encoded = json_encode($repaired, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
                if (!is_string($encoded)) {
                    continue;
                }
                $db->query(
                    "UPDATE `{$prefix}messenger_contacts`
                     SET `metadata_json` = '" . $db->escape($encoded) . "'
                     WHERE `contact_id` = " . $contact_id . "
                       AND `provider` = 'max'"
                );
            }

            $next_state = count($rows) < $limit ? 'done' : (string)$next_cursor;
            $db->query(
                "INSERT INTO `{$prefix}settings` (`key`, `value`)
                 VALUES ('" . $db->escape($migration_key) . "', '" . $db->escape($next_state) . "')
                 ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)"
            );
            $db->query('COMMIT');
        } catch (\Throwable $e) {
            try {
                $db->query('ROLLBACK');
            } catch (\Throwable $rollback_error) {
                 
            }
            throw $e;
        }
    }

    private static function authRateLimitSchemaQuery($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}auth_rate_limits` (
            `subject_hash` CHAR(64) NOT NULL PRIMARY KEY,
            `action` VARCHAR(32) NOT NULL,
            `attempts` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            `window_started_at` DATETIME NOT NULL,
            `blocked_until` DATETIME DEFAULT NULL,
            `date_modified` DATETIME NOT NULL,
            INDEX `idx_auth_rate_blocked` (`blocked_until`, `action`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function messageReadCheckpointSchemaQuery($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}message_read_checkpoints` (
            `checkpoint_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `provider` VARCHAR(24) NOT NULL,
            `channel_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `chat_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `external_chat_hash` CHAR(64) NOT NULL,
            `reader_hash` CHAR(64) NOT NULL,
            `cursor_type` VARCHAR(24) NOT NULL,
            `cursor_value` BIGINT UNSIGNED NOT NULL DEFAULT 0,
            `observed_at` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            UNIQUE KEY `uniq_read_checkpoint` (`provider`, `channel_id`, `external_chat_hash`, `reader_hash`),
            INDEX `idx_read_checkpoint_chat` (`chat_id`, `provider`, `cursor_value`),
            INDEX `idx_read_checkpoint_modified` (`date_modified`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function ensureRuntimeTimezoneSetting($db, $prefix) {
        $key = AppClock::SETTING_TIMEZONE;
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($key) . "' LIMIT 1");
        if ($query && $query->num_rows && trim((string)($query->row['value'] ?? '')) !== '') {
            return;
        }

        $timezone = self::detectRuntimeTimezone($db);
        if ($timezone === '') {
            return;
        }

         
         
        $db->query(
            "INSERT INTO `{$prefix}settings` (`key`, `value`)
             VALUES ('" . $db->escape($key) . "', '" . $db->escape($timezone) . "')
             ON DUPLICATE KEY UPDATE `value` = IF(`value` = '', VALUES(`value`), `value`)"
        );
    }

    private static function ensureAiVisualPromptMigration($db, $prefix) {
        $migration_key = 'data_migration_20260725_ai_visual_prompt_v3';
        $migration = $db->query(
            "SELECT `value` FROM `{$prefix}settings`
             WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1"
        );
        if ($migration && $migration->num_rows) {
            return;
        }

        $prompt = $db->query(
            "SELECT `value` FROM `{$prefix}settings`
             WHERE `key` = 'chat_ai_prompt' LIMIT 1"
        );
        $stored_prompt = $prompt && $prompt->num_rows
            ? (string)($prompt->row['value'] ?? '')
            : '';
        $upgraded_prompt = PromptDefaults::upgradeLegacyVisionInstructions($stored_prompt);
        if (!hash_equals($stored_prompt, $upgraded_prompt)) {
             
             
             
            $backup_key = 'chat_ai_prompt_backup_20260725_visual_v3';
            $db->query(
                "INSERT INTO `{$prefix}settings` (`key`, `value`)
                 VALUES (
                    '" . $db->escape($backup_key) . "',
                    '" . $db->escape($stored_prompt) . "'
                 )
                 ON DUPLICATE KEY UPDATE `value` = `value`"
            );
            $db->query(
                "UPDATE `{$prefix}settings`
                 SET `value` = '" . $db->escape($upgraded_prompt) . "'
                 WHERE `key` = 'chat_ai_prompt'"
            );
        }

        $db->query(
            "INSERT INTO `{$prefix}settings` (`key`, `value`)
             VALUES ('" . $db->escape($migration_key) . "', '1')
             ON DUPLICATE KEY UPDATE `value` = '1'"
        );
    }

    private static function ensureCentralGatewayApiSetting($db, $prefix) {
        $key = 'chat_central_api';
        $gateway = EasyTokensGiftProvisioner::defaultGatewayApi();

         
         
         
        $db->query(
            "INSERT INTO `{$prefix}settings` (`key`, `value`)
             VALUES ('" . $db->escape($key) . "', '" . $db->escape($gateway) . "')
             ON DUPLICATE KEY UPDATE `value` = IF(TRIM(`value`) = '', VALUES(`value`), `value`)"
        );
    }

    private static function ensureAiResponseTimingSettings($db, $prefix) {
         
         
         
        $db->query(
            "INSERT INTO `{$prefix}settings` (`key`, `value`)
             VALUES ('" . $db->escape(AiResponseTimingPolicy::SETTING_MODE) . "', '" . $db->escape(AiResponseTimingPolicy::MODE_STRICT_DELAY) . "')
             ON DUPLICATE KEY UPDATE `value` = `value`"
        );
        $db->query(
            "INSERT INTO `{$prefix}settings` (`key`, `value`)
             VALUES ('" . $db->escape(AiResponseTimingPolicy::SETTING_OFFLINE_DELAY_SECONDS) . "', '" . (int)AiResponseTimingPolicy::DEFAULT_OFFLINE_DELAY_SECONDS . "')
             ON DUPLICATE KEY UPDATE `value` = `value`"
        );
    }

    private static function ensureWidgetBaseDomainSetting($db, $prefix) {
        $key = WidgetDomainPolicy::SETTING_BASE_DOMAIN;
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($key) . "' LIMIT 1");
        if ($query && $query->num_rows && WidgetDomainPolicy::isAllowedBaseDomain((string)($query->row['value'] ?? ''))) {
            return;
        }

        $base_domain = WidgetDomainPolicy::installHost();
        if (!WidgetDomainPolicy::isAllowedBaseDomain($base_domain)) {
            return;
        }

         
         
        if ($query && $query->num_rows) {
            $db->query("UPDATE `{$prefix}settings` SET `value` = '" . $db->escape($base_domain) . "' WHERE `key` = '" . $db->escape($key) . "'");
            return;
        }

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($key) . "', '" . $db->escape($base_domain) . "')");
    }

    private static function ensureCatalogKnowledgeSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260710_catalog_knowledge_sync';
        $migration = $db->query(
            "SELECT `value` FROM `{$prefix}settings`
             WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1"
        );
        if ($migration && $migration->num_rows) {
            return;
        }

        $db->query(
            "CREATE TABLE IF NOT EXISTS `{$prefix}knowledge` (
                `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `title` VARCHAR(512) NOT NULL DEFAULT '',
                `content` MEDIUMTEXT NOT NULL,
                `section_id` TINYINT UNSIGNED NOT NULL DEFAULT 1,
                `item_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `content_hash` CHAR(64) NOT NULL DEFAULT '',
                `sync_generation` CHAR(32) NOT NULL DEFAULT '',
                `vector_id` INT UNSIGNED NULL DEFAULT NULL,
                `status` ENUM('pending','indexing','active','disabled','failed') NOT NULL DEFAULT 'pending',
                `error` TEXT NULL DEFAULT NULL,
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                INDEX `idx_section` (`section_id`),
                INDEX `idx_status` (`status`),
                INDEX `idx_section_item` (`section_id`, `item_id`),
                INDEX `idx_section_generation` (`section_id`, `sync_generation`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        );

        self::ensureColumn($db, $prefix . 'knowledge', 'item_id', "`item_id` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `section_id`");
        self::ensureColumn($db, $prefix . 'knowledge', 'content_hash', "`content_hash` CHAR(64) NOT NULL DEFAULT '' AFTER `item_id`");
        self::ensureColumn($db, $prefix . 'knowledge', 'sync_generation', "`sync_generation` CHAR(32) NOT NULL DEFAULT '' AFTER `content_hash`");
        self::ensureIndex($db, $prefix . 'knowledge', 'idx_section_item', "`section_id`, `item_id`");
        self::ensureIndex($db, $prefix . 'knowledge', 'idx_section_generation', "`section_id`, `sync_generation`");

         
         
         
        if (self::columnExists($db, $prefix . 'knowledge', 'source_id')) {
            $db->query(
                "UPDATE `{$prefix}knowledge`
                 SET `item_id` = `source_id`
                 WHERE `item_id` = 0 AND `source_id` IS NOT NULL AND `source_id` > 0"
            );
        }

        foreach (array(
            'chat_cms_availability_policy' => 'smart',
            'chat_cms_show_exact_quantity' => '0',
            'chat_cms_semantic_enabled' => '0',
            'chat_cms_semantic_auto_enabled' => '0',
            'chat_cms_semantic_auto_opt_out' => '0',
            'chat_cms_search_auto_opt_out' => '0',
            'chat_cms_search_recommendation_dismissed' => '0',
            'chat_settings_revision' => '0',
        ) as $key => $value) {
            $db->query(
                "INSERT INTO `{$prefix}settings` (`key`, `value`)
                 VALUES ('" . $db->escape($key) . "', '" . $db->escape($value) . "')
                 ON DUPLICATE KEY UPDATE `value` = `value`"
            );
        }

        $db->query(
            "INSERT INTO `{$prefix}settings` (`key`, `value`)
             VALUES ('" . $db->escape($migration_key) . "', '1')
             ON DUPLICATE KEY UPDATE `value` = '1'"
        );
    }

    private static function ensureCatalogPublicUrlSchemaOnce($db, $prefix) {
        $db->query(self::catalogPublicUrlTableSql($prefix));
    }

    private static function ensureCatalogSettingsPersistenceOnce($db, $prefix) {
        $migration_key = 'data_migration_20260729_catalog_settings_persistence_v1';
        $migration = $db->query(
            "SELECT `value` FROM `{$prefix}settings`
             WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1"
        );
        if ($migration && $migration->num_rows) {
            return;
        }

        $search = $db->query(
            "SELECT `value` FROM `{$prefix}settings`
             WHERE `key` = 'chat_productsearch' LIMIT 1"
        );
        $search_enabled = $search && $search->num_rows
            && (int)($search->row['value'] ?? 0) > 0;
        $opt_out = $search_enabled ? '0' : '1';

         
         
         
        $db->query('START TRANSACTION');
        try {
            foreach (array(
                'chat_cms_search_auto_opt_out' => $opt_out,
                'chat_settings_revision' => '0',
            ) as $key => $value) {
                $db->query(
                    "INSERT INTO `{$prefix}settings` (`key`, `value`)
                     VALUES ('" . $db->escape($key) . "', '" . $db->escape($value) . "')
                     ON DUPLICATE KEY UPDATE `value` = `value`"
                );
            }
            if (!$search_enabled) {
                $db->query(
                    "INSERT INTO `{$prefix}settings` (`key`, `value`)
                     VALUES ('chat_cms_semantic_auto_opt_out', '1')
                     ON DUPLICATE KEY UPDATE `value` = '1'"
                );
            }
            $db->query(
                "INSERT INTO `{$prefix}settings` (`key`, `value`)
                 VALUES ('" . $db->escape($migration_key) . "', '1')
                 ON DUPLICATE KEY UPDATE `value` = '1'"
            );
            $db->query('COMMIT');
        } catch (\Throwable $e) {
            try {
                $db->query('ROLLBACK');
            } catch (\Throwable $rollback_error) {
                 
            }
            throw $e;
        }
    }

    private static function catalogPublicUrlTableSql($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}catalog_url_cache` (
            `cache_key` CHAR(64) NOT NULL PRIMARY KEY,
            `cms_type` VARCHAR(24) NOT NULL DEFAULT '',
            `resource_type` VARCHAR(24) NOT NULL DEFAULT '',
            `resource_id` VARCHAR(64) NOT NULL DEFAULT '',
            `origin_hash` CHAR(64) NOT NULL,
            `source_url` VARCHAR(2048) NOT NULL,
            `source_hash` CHAR(64) NOT NULL,
            `canonical_url` VARCHAR(2048) NOT NULL DEFAULT '',
            `status` ENUM('pending','verified','failed') NOT NULL DEFAULT 'pending',
            `http_code` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            `error_code` VARCHAR(64) NOT NULL DEFAULT '',
            `checked_at` INT UNSIGNED NOT NULL DEFAULT 0,
            `expires_at` INT UNSIGNED NOT NULL DEFAULT 0,
            INDEX `idx_expiry` (`status`, `expires_at`),
            INDEX `idx_resource` (`cms_type`, `resource_type`, `resource_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function enqueueVkCallbackMessageReplyRepairOnce($db, $prefix) {
        $migration_key = 'data_migration_20260705_vk_message_reply_callback_repair';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureQueueTable($db, $prefix);

        $channels = $db->query(
            "SELECT `channel_id`
             FROM `{$prefix}messenger_channels`
             WHERE `provider` = 'vk'
               AND `status` = 'active'
               AND TRIM(`credentials_json`) != ''
             ORDER BY `channel_id` ASC"
        );

        $now = time();
        foreach ($channels->rows as $channel) {
            $channel_id = (int)($channel['channel_id'] ?? 0);
            if ($channel_id <= 0) {
                continue;
            }

            $payload = json_encode([
                'channel_id' => $channel_id,
                'reason' => 'upgrade_message_reply',
            ], JSON_UNESCAPED_UNICODE);
            $needle = '%"channel_id":' . $channel_id . '%';
            $existing = $db->query(
                "SELECT `id`
                 FROM `{$prefix}queue`
                 WHERE `job_type` = 'messenger_vk_callback_repair'
                   AND `status` IN ('pending','processing')
                   AND `payload` LIKE '" . $db->escape($needle) . "'
                 LIMIT 1"
            );
            if ($existing && $existing->num_rows) {
                continue;
            }

             
             
             
            $db->query(
                "INSERT INTO `{$prefix}queue`
                    (`job_type`, `payload`, `status`, `attempts`, `max_attempts`, `priority`, `available_at`, `locked_at`, `lock_token`, `created_at`, `error`)
                 VALUES
                    ('messenger_vk_callback_repair', '" . $db->escape($payload) . "', 'pending', 0, 5, 10, {$now}, NULL, NULL, {$now}, NULL)"
            );
        }

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function detectRuntimeTimezone($db): string {
        $candidates = [
            defined('EASYCHAT_TIMEZONE') ? (string)EASYCHAT_TIMEZONE : '',
        ];

        try {
            $query = $db->query('SELECT @@global.time_zone AS global_time_zone');
            $global_timezone = trim((string)($query->row['global_time_zone'] ?? ''));
            if ($global_timezone !== '' && strtoupper($global_timezone) !== 'SYSTEM') {
                $candidates[] = $global_timezone;
            }
        } catch (\Throwable $e) {
        }

        $php_timezone = date_default_timezone_get();
        if (strtoupper((string)$php_timezone) !== 'UTC') {
            $candidates[] = (string)$php_timezone;
        }

        foreach ($candidates as $candidate) {
            if (self::isValidTimezoneName((string)$candidate)) {
                return trim((string)$candidate);
            }
        }

        try {
            $query = $db->query('SELECT TIMESTAMPDIFF(SECOND, UTC_TIMESTAMP(), NOW()) AS offset_seconds');
            $offset = isset($query->row['offset_seconds']) ? (int)$query->row['offset_seconds'] : 0;
            if ($offset !== 0 && abs($offset) <= 14 * 3600) {
                $sign = $offset < 0 ? '-' : '+';
                $abs = abs($offset);
                $timezone = sprintf('%s%02d:%02d', $sign, intdiv($abs, 3600), intdiv($abs % 3600, 60));
                if (self::isValidTimezoneName($timezone)) {
                    return $timezone;
                }
            }
        } catch (\Throwable $e) {
        }

        return '';
    }

    private static function isValidTimezoneName(string $timezone): bool {
        $timezone = trim($timezone);
        if ($timezone === '') {
            return false;
        }

        try {
            new \DateTimeZone($timezone);
            return true;
        } catch (\Throwable $e) {
            return false;
        }
    }

    private static function ensureModerationTokensTable($db, $prefix) {
        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}moderation_tokens` (
            `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `public_id` VARCHAR(64) NOT NULL,
            `token_hash` CHAR(64) NOT NULL,
            `purpose` VARCHAR(64) NOT NULL,
            `version_code` INT(11) NOT NULL DEFAULT 0,
            `expires_at` DATETIME NOT NULL,
            `used_at` DATETIME DEFAULT NULL,
            `created_at` DATETIME NOT NULL,
            `used_by_fcm_hash` CHAR(64) DEFAULT NULL,
            `last_error` VARCHAR(255) DEFAULT NULL,
            UNIQUE KEY `uniq_public_id` (`public_id`),
            UNIQUE KEY `uniq_token_hash` (`token_hash`),
            KEY `idx_purpose_expires` (`purpose`, `expires_at`),
            KEY `idx_used_at` (`used_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }

    private static function ensureAppPairingSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260615_app_pairing_tokens';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureAppPairingSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureAppPairingSchema($db, $prefix) {
        $db->query(\EasyChat\Model\AppPairing::tableSql($prefix));
    }

    private static function ensureAppSessionHealthSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260614_app_session_fcm_health';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureAppSessionHealthSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureAppSessionHealthSchema($db, $prefix) {
        self::ensureColumn($db, $prefix . 'app_session', 'fcm_token_hash', "`fcm_token_hash` CHAR(64) DEFAULT NULL AFTER `fcm_token`");
        self::ensureColumn($db, $prefix . 'app_session', 'fcm_token_synced_at', "`fcm_token_synced_at` DATETIME DEFAULT NULL AFTER `fcm_token_hash`");
        self::ensureColumn($db, $prefix . 'app_session', 'last_push_at', "`last_push_at` DATETIME DEFAULT NULL AFTER `date_added`");
        self::ensureColumn($db, $prefix . 'app_session', 'last_push_error', "`last_push_error` VARCHAR(128) NOT NULL DEFAULT '' AFTER `last_push_at`");
        self::ensureColumn($db, $prefix . 'app_session', 'last_push_error_at', "`last_push_error_at` DATETIME DEFAULT NULL AFTER `last_push_error`");
        self::ensureIndex($db, $prefix . 'app_session', 'idx_fcm_token_hash', "`fcm_token_hash`");
        self::ensureIndex($db, $prefix . 'app_session', 'idx_fcm_health', "`fcm_token_synced_at`, `last_push_error_at`");

         
         
        $db->query("UPDATE `{$prefix}app_session`
            SET `fcm_token_hash` = SHA2(`fcm_token`, 256)
            WHERE `fcm_token` IS NOT NULL
              AND `fcm_token` != ''
              AND (`fcm_token_hash` IS NULL OR `fcm_token_hash` = '')");
    }

    private static function ensureAndroidDeviceVersionSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260717_android_device_versions';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureColumn($db, $prefix . 'app_session', 'app_version_code', "`app_version_code` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `manager_id`");
        self::ensureColumn($db, $prefix . 'app_session', 'app_version_name', "`app_version_name` VARCHAR(32) NOT NULL DEFAULT '' AFTER `app_version_code`");
        self::ensureColumn($db, $prefix . 'app_session', 'device_label', "`device_label` VARCHAR(120) NOT NULL DEFAULT '' AFTER `app_version_name`");
        self::ensureColumn($db, $prefix . 'app_session', 'device_last_seen_at', "`device_last_seen_at` DATETIME DEFAULT NULL AFTER `device_label`");
        self::ensureIndex($db, $prefix . 'app_session', 'idx_app_device_version', "`manager_id`, `app_version_code`, `device_last_seen_at`");
        self::ensureManagerNoticeStateSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureAndroidDeviceIdentitySchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260724_android_device_identity_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureAppDeviceSchema($db, $prefix);
        $db->query(\EasyChat\Model\AppDevice::eventTableSql($prefix));
        self::ensureColumn($db, $prefix . 'app_session', 'app_device_id', "`app_device_id` INT UNSIGNED DEFAULT NULL AFTER `manager_id`");
        self::ensureColumn($db, $prefix . 'app_session', 'access_token_hash', "`access_token_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL AFTER `session_token`");
        self::ensureColumn($db, $prefix . 'app_session', 'refresh_token_hash', "`refresh_token_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL AFTER `access_token_hash`");
        self::ensureColumn($db, $prefix . 'app_session', 'token_family', "`token_family` CHAR(32) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL AFTER `refresh_token_hash`");
        self::ensureColumn($db, $prefix . 'app_session', 'access_expires_at', "`access_expires_at` DATETIME DEFAULT NULL AFTER `token_family`");
        self::ensureColumn($db, $prefix . 'app_session', 'refresh_expires_at', "`refresh_expires_at` DATETIME DEFAULT NULL AFTER `access_expires_at`");
        self::ensureColumn($db, $prefix . 'app_session', 'absolute_expires_at', "`absolute_expires_at` DATETIME DEFAULT NULL AFTER `refresh_expires_at`");
        self::ensureColumn($db, $prefix . 'app_session', 'last_used_at', "`last_used_at` DATETIME DEFAULT NULL AFTER `device_last_seen_at`");
        self::ensureColumn($db, $prefix . 'app_session', 'revoked_at', "`revoked_at` DATETIME DEFAULT NULL AFTER `last_used_at`");
        self::ensureColumn($db, $prefix . 'app_session', 'revoke_reason', "`revoke_reason` VARCHAR(64) NOT NULL DEFAULT '' AFTER `revoked_at`");
        self::ensureIndex($db, $prefix . 'app_session', 'idx_app_session_device_active', "`app_device_id`, `revoked_at`, `access_expires_at`");
        self::ensureUniqueIndex($db, $prefix . 'app_session', 'uniq_app_access_hash', "`access_token_hash`");
        self::ensureUniqueIndex($db, $prefix . 'app_session', 'uniq_app_refresh_hash', "`refresh_token_hash`");
        self::ensureIndex($db, $prefix . 'app_session', 'idx_app_token_family', "`token_family`, `revoked_at`");

        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}app_refresh_token_history` (
            `history_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `session_id` INT(11) NOT NULL,
            `token_family` CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
            `token_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
            `used_at` DATETIME NOT NULL,
            `expires_at` DATETIME NOT NULL,
            `replacement_bundle` TEXT DEFAULT NULL,
            UNIQUE KEY `uniq_app_refresh_history_hash` (`token_hash`),
            KEY `idx_app_refresh_history_family` (`token_family`, `used_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        self::ensureColumn($db, $prefix . 'app_refresh_token_history', 'replacement_bundle', "`replacement_bundle` TEXT DEFAULT NULL AFTER `expires_at`");

        self::ensureColumn($db, $prefix . 'sms_devices', 'app_device_id', "`app_device_id` INT UNSIGNED DEFAULT NULL AFTER `device_id`");
        self::ensureUniqueIndex($db, $prefix . 'sms_devices', 'uniq_sms_app_device', "`app_device_id`");
        self::ensureIndex($db, $prefix . 'sms_devices', 'idx_sms_app_device_manager', "`app_device_id`, `manager_id`, `status`");
        self::ensureColumn($db, $prefix . 'sms_jobs', 'app_device_id', "`app_device_id` INT UNSIGNED DEFAULT NULL AFTER `device_id`");
        self::ensureIndex($db, $prefix . 'sms_jobs', 'idx_sms_job_app_device', "`app_device_id`, `status`, `job_id`");

         
         
         
        $legacy = $db->query(
            "SELECT s.`session_id`, s.`manager_id`, s.`device_label`, s.`app_version_code`,
                    s.`app_version_name`, s.`device_last_seen_at`, s.`date_added`,
                    d.`android_sdk`, d.`device_manufacturer`, d.`device_model`
             FROM `{$prefix}app_session` s
             LEFT JOIN `{$prefix}sms_devices` d ON d.`app_session_id` = s.`session_id`
             WHERE s.`app_device_id` IS NULL"
        );
        foreach (($legacy && $legacy->rows) ? $legacy->rows : [] as $row) {
            $public_id = bin2hex(random_bytes(16));
            $first_seen = trim((string)$row['date_added']) ?: date('Y-m-d H:i:s');
            $last_seen = trim((string)$row['device_last_seen_at']) ?: $first_seen;
            $db->query(
                "INSERT INTO `{$prefix}app_devices` SET
                    `public_id` = '" . $db->escape($public_id) . "',
                    `installation_hash` = NULL,
                    `manager_id` = " . (int)$row['manager_id'] . ",
                    `device_label` = '" . $db->escape((string)($row['device_label'] ?: 'Android')) . "',
                    `manufacturer` = '" . $db->escape((string)($row['device_manufacturer'] ?? '')) . "',
                    `device_model` = '" . $db->escape((string)($row['device_model'] ?? '')) . "',
                    `android_sdk` = " . (int)($row['android_sdk'] ?? 0) . ",
                    `app_version_code` = " . (int)$row['app_version_code'] . ",
                    `app_version_name` = '" . $db->escape((string)$row['app_version_name']) . "',
                    `identity_status` = 'legacy',
                    `status` = 'active',
                    `first_seen_at` = '" . $db->escape($first_seen) . "',
                    `last_seen_at` = '" . $db->escape($last_seen) . "',
                    `last_paired_at` = '" . $db->escape($first_seen) . "',
                    `date_modified` = NOW()"
            );
            $device_id = (int)$db->getLastId();
            $db->query("UPDATE `{$prefix}app_session` SET `app_device_id` = {$device_id} WHERE `session_id` = " . (int)$row['session_id']);
            $db->query("UPDATE `{$prefix}sms_devices` SET `app_device_id` = {$device_id} WHERE `app_session_id` = " . (int)$row['session_id']);
            $db->query("UPDATE `{$prefix}sms_jobs` SET `app_device_id` = {$device_id} WHERE `app_session_id` = " . (int)$row['session_id']);
        }

         
         
        $orphans = $db->query(
            "SELECT d.* FROM `{$prefix}sms_devices` d
             LEFT JOIN `{$prefix}app_session` s ON s.`session_id` = d.`app_session_id`
             WHERE s.`session_id` IS NULL AND d.`app_device_id` IS NULL"
        );
        foreach (($orphans && $orphans->rows) ? $orphans->rows : [] as $row) {
            $public_id = bin2hex(random_bytes(16));
            $first_seen = trim((string)$row['date_added']) ?: date('Y-m-d H:i:s');
            $last_seen = trim((string)$row['last_seen_at']) ?: $first_seen;
            $db->query(
                "INSERT INTO `{$prefix}app_devices` SET
                    `public_id` = '" . $db->escape($public_id) . "',
                    `installation_hash` = NULL,
                    `manager_id` = " . (int)$row['manager_id'] . ",
                    `device_label` = '" . $db->escape((string)($row['device_label'] ?: 'Android')) . "',
                    `manufacturer` = '" . $db->escape((string)($row['device_manufacturer'] ?? '')) . "',
                    `device_model` = '" . $db->escape((string)($row['device_model'] ?? '')) . "',
                    `android_sdk` = " . (int)($row['android_sdk'] ?? 0) . ",
                    `app_version_name` = '" . $db->escape((string)$row['app_version']) . "',
                    `identity_status` = 'legacy',
                    `status` = 'history',
                    `first_seen_at` = '" . $db->escape($first_seen) . "',
                    `last_seen_at` = '" . $db->escape($last_seen) . "',
                    `last_paired_at` = '" . $db->escape($first_seen) . "',
                    `revoked_at` = '" . $db->escape($last_seen) . "',
                    `revoke_reason` = 'orphaned_legacy_session',
                    `date_modified` = NOW()"
            );
            $device_id = (int)$db->getLastId();
            $db->query("UPDATE `{$prefix}sms_devices` SET `app_device_id` = {$device_id} WHERE `device_id` = " . (int)$row['device_id']);
            $db->query("UPDATE `{$prefix}sms_jobs` SET `app_device_id` = {$device_id} WHERE `device_id` = " . (int)$row['device_id']);
        }

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureManagerNoticeStateSchema($db, $prefix) {
        $manager_engine = self::tableEngine($db, $prefix . 'managers');
        $with_foreign_key = $manager_engine === '' || $manager_engine === 'INNODB';

        try {
            $db->query(self::managerNoticeStateTableSql($prefix, $with_foreign_key));
        } catch (\Throwable $e) {
            if (!$with_foreign_key) {
                throw $e;
            }

             
             
             
            $db->query(self::managerNoticeStateTableSql($prefix, false));
        }
    }

    private static function ensureAppDeviceSchema($db, $prefix) {
        $manager_engine = self::tableEngine($db, $prefix . 'managers');
        $with_foreign_key = $manager_engine === '' || $manager_engine === 'INNODB';

        try {
            $db->query(\EasyChat\Model\AppDevice::tableSql($prefix, $with_foreign_key));
        } catch (\Throwable $e) {
            if (!$with_foreign_key) {
                throw $e;
            }

             
             
             
            $db->query(\EasyChat\Model\AppDevice::tableSql($prefix, false));
        }
    }

    private static function managerNoticeStateTableSql($prefix, $with_foreign_key = true) {
        $foreign_key = $with_foreign_key
            ? ",
            FOREIGN KEY (`manager_id`) REFERENCES `{$prefix}managers` (`id`) ON DELETE CASCADE"
            : "";

        return "CREATE TABLE IF NOT EXISTS `{$prefix}manager_notice_state` (
            `state_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `manager_id` INT(11) NOT NULL,
            `notice_key` VARCHAR(64) NOT NULL,
            `subject` VARCHAR(64) NOT NULL DEFAULT '',
            `snoozed_until` DATETIME DEFAULT NULL,
            `dismissed_at` DATETIME DEFAULT NULL,
            `updated_at` DATETIME NOT NULL,
            UNIQUE KEY `uniq_manager_notice` (`manager_id`, `notice_key`),
            KEY `idx_notice_snooze` (`notice_key`, `snoozed_until`){$foreign_key}
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function ensureOutboundContactSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260706_outbound_contact_sms';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureOutboundContactSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureOutboundContactSchema($db, $prefix) {
        $db->query(self::outboundContactRequestsTableSql($prefix));
        $db->query(self::smsDevicesTableSql($prefix));
        $db->query(self::smsJobsTableSql($prefix));
    }

    private static function ensureOutboundContactReliabilitySchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260722_outbound_contact_reliability';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) return;

        $db->query(self::outboundContactRequestsTableSql($prefix));
        self::ensureColumn($db, $prefix . 'outbound_contact_requests', 'client_request_id', "`client_request_id` VARCHAR(80) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL AFTER `public_id`");
        self::ensureColumn($db, $prefix . 'outbound_contact_requests', 'payload_hash', "`payload_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '' AFTER `client_request_id`");
        self::ensureUniqueIndex($db, $prefix . 'outbound_contact_requests', 'uniq_outbound_client_request', "`manager_id`, `client_request_id`");
        self::ensureIndex($db, $prefix . 'outbound_contact_requests', 'idx_outbound_payload_active', "`channel`, `payload_hash`, `status`, `date_added`");
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureSmsInboundSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260707_sms_inbound';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureSmsInboundSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureSmsInboundSchema($db, $prefix) {
        $db->query(self::smsDevicesTableSql($prefix));
        self::ensureColumn($db, $prefix . 'sms_devices', 'inbound_admin_requested', "`inbound_admin_requested` TINYINT(1) NOT NULL DEFAULT 0 AFTER `status`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'inbound_operator_consent', "`inbound_operator_consent` VARCHAR(16) NOT NULL DEFAULT 'none' AFTER `inbound_admin_requested`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'inbound_android_permission', "`inbound_android_permission` VARCHAR(16) NOT NULL DEFAULT 'unknown' AFTER `inbound_operator_consent`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'inbound_status', "`inbound_status` VARCHAR(32) NOT NULL DEFAULT 'not_requested' AFTER `inbound_android_permission`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'inbound_requested_at', "`inbound_requested_at` DATETIME DEFAULT NULL AFTER `requested_at`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'inbound_responded_at', "`inbound_responded_at` DATETIME DEFAULT NULL AFTER `responded_at`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'last_inbound_at', "`last_inbound_at` DATETIME DEFAULT NULL AFTER `last_used_at`");
        self::ensureIndex($db, $prefix . 'sms_devices', 'idx_sms_inbound_ready', "`inbound_status`, `inbound_admin_requested`, `inbound_operator_consent`, `inbound_android_permission`, `last_inbound_at`");
    }

    private static function ensureSmsDiagnosticsSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260721_sms_diagnostics';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        $db->query(self::smsJobsTableSql($prefix));
        self::ensureColumn($db, $prefix . 'sms_jobs', 'reservation_key', "`reservation_key` VARCHAR(96) NOT NULL DEFAULT '' AFTER `attempt_count`");
        self::ensureColumn($db, $prefix . 'sms_jobs', 'last_stage', "`last_stage` VARCHAR(40) NOT NULL DEFAULT 'job_created' AFTER `last_error`");
        self::ensureColumn($db, $prefix . 'sms_jobs', 'last_stage_code', "`last_stage_code` VARCHAR(64) NOT NULL DEFAULT '' AFTER `last_stage`");
        self::ensureColumn($db, $prefix . 'sms_jobs', 'last_stage_at', "`last_stage_at` DATETIME DEFAULT NULL AFTER `last_stage_code`");
        self::ensureColumn($db, $prefix . 'sms_jobs', 'delivery_status', "`delivery_status` VARCHAR(24) NOT NULL DEFAULT 'not_requested' AFTER `last_stage_at`");
        self::ensureColumn($db, $prefix . 'sms_jobs', 'parts_total', "`parts_total` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `delivery_status`");
        self::ensureColumn($db, $prefix . 'sms_jobs', 'parts_delivered', "`parts_delivered` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `parts_total`");
        self::ensureColumn($db, $prefix . 'sms_jobs', 'parts_delivery_failed', "`parts_delivery_failed` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `parts_delivered`");
        self::ensureColumn($db, $prefix . 'sms_jobs', 'delivered_at', "`delivered_at` DATETIME DEFAULT NULL AFTER `completed_at`");
        $db->query(self::smsJobEventsTableSql($prefix));
        self::ensureIndex($db, $prefix . 'sms_jobs', 'idx_sms_job_device_recent', "`device_id`, `date_added`");

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureSmsReliabilitySchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260722_sms_reliability_v2';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        $db->query(self::smsDevicesTableSql($prefix));
        self::ensureColumn($db, $prefix . 'sms_devices', 'android_sdk', "`android_sdk` SMALLINT UNSIGNED NOT NULL DEFAULT 0 AFTER `app_version`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'device_manufacturer', "`device_manufacturer` VARCHAR(80) NOT NULL DEFAULT '' AFTER `android_sdk`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'device_model', "`device_model` VARCHAR(80) NOT NULL DEFAULT '' AFTER `device_manufacturer`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'reliability_status', "`reliability_status` VARCHAR(16) NOT NULL DEFAULT 'unknown' AFTER `device_model`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'battery_unrestricted', "`battery_unrestricted` TINYINT(1) NOT NULL DEFAULT 0 AFTER `reliability_status`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'background_restricted', "`background_restricted` TINYINT(1) NOT NULL DEFAULT 0 AFTER `battery_unrestricted`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'notifications_allowed', "`notifications_allowed` TINYINT(1) NOT NULL DEFAULT 0 AFTER `background_restricted`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'fcm_synced', "`fcm_synced` TINYINT(1) NOT NULL DEFAULT 0 AFTER `notifications_allowed`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'reliability_checked_at', "`reliability_checked_at` DATETIME DEFAULT NULL AFTER `fcm_synced`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'default_sms_subscription_id', "`default_sms_subscription_id` INT NOT NULL DEFAULT -1 AFTER `fcm_synced`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'last_inbound_subscription_id', "`last_inbound_subscription_id` INT NOT NULL DEFAULT -1 AFTER `last_inbound_at`");

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureSmsInboundHealthSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260722_sms_inbound_health_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        $db->query(self::smsDevicesTableSql($prefix));
        self::ensureColumn($db, $prefix . 'sms_devices', 'inbound_sim_mode', "`inbound_sim_mode` VARCHAR(16) NOT NULL DEFAULT 'default_sim' AFTER `inbound_status`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'inbound_health_status', "`inbound_health_status` VARCHAR(16) NOT NULL DEFAULT 'unknown' AFTER `inbound_sim_mode`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'inbound_last_error_code', "`inbound_last_error_code` VARCHAR(64) NOT NULL DEFAULT '' AFTER `inbound_health_status`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'inbound_last_error_at', "`inbound_last_error_at` DATETIME DEFAULT NULL AFTER `inbound_last_error_code`");
        self::ensureColumn($db, $prefix . 'sms_devices', 'inbound_pending_count', "`inbound_pending_count` SMALLINT UNSIGNED NOT NULL DEFAULT 0 AFTER `inbound_last_error_at`");

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

     
    private static function ensureSmsCarrierEvidenceSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260722_sms_carrier_evidence_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) return;

        $db->query(self::smsJobEventsTableSql($prefix));
        self::ensureColumn($db, $prefix . 'sms_job_events', 'carrier_attempt', "`carrier_attempt` SMALLINT UNSIGNED DEFAULT NULL AFTER `client_event_id`");
        self::ensureColumn($db, $prefix . 'sms_job_events', 'carrier_part_index', "`carrier_part_index` SMALLINT UNSIGNED DEFAULT NULL AFTER `carrier_attempt`");
        self::ensureUniqueIndex(
            $db,
            $prefix . 'sms_job_events',
            'uniq_sms_carrier_part',
            "`job_id`, `carrier_attempt`, `carrier_part_index`"
        );

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

     
    private static function ensureAppointmentRemindersRemovedOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260723_appointment_reminders_removed_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) return;

         
        self::ensureQueueTable($db, $prefix);
        $db->query("DELETE FROM `{$prefix}queue` WHERE `job_type` = 'appointment_sms_dispatch'");
        $db->query(self::smsJobsTableSql($prefix));
        if (self::columnExists($db, $prefix . 'sms_jobs', 'schedule_id')) {
            $db->query("DELETE FROM `{$prefix}sms_jobs` WHERE `schedule_id` IS NOT NULL");
            self::dropIndexIfExists($db, $prefix . 'sms_jobs', 'uniq_sms_job_schedule');
            self::dropColumnIfExists($db, $prefix . 'sms_jobs', 'schedule_id');
        }

        $db->query("DROP TABLE IF EXISTS `{$prefix}appointment_reminder_alerts`");
        $db->query("DROP TABLE IF EXISTS `{$prefix}appointment_reminder_events`");
        $db->query("DROP TABLE IF EXISTS `{$prefix}appointment_sms_reminders`");
        $db->query("DROP TABLE IF EXISTS `{$prefix}customer_appointments`");
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

     
    private static function ensureSmsReceiptSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260722_sms_job_receipts_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) return;

        $db->query(self::smsJobsTableSql($prefix));
        self::ensureColumn($db, $prefix . 'sms_jobs', 'receipt_token_hash', "`receipt_token_hash` CHAR(64) NOT NULL DEFAULT '' AFTER `reservation_key`");
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

     
    private static function ensureScheduledMessagesSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260723_scheduled_messages_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) return;

        $db->query(\EasyChat\Model\ScheduledMessage::tableSql($prefix));
        $db->query(\EasyChat\Model\ScheduledMessage::eventTableSql($prefix));
        $db->query(
            "INSERT INTO `{$prefix}settings` (`key`, `value`)
             VALUES ('" . $db->escape($migration_key) . "', '1')
             ON DUPLICATE KEY UPDATE `value` = '1'"
        );
    }

    private static function outboundContactRequestsTableSql($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}outbound_contact_requests` (
            `request_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `public_id` VARCHAR(64) NOT NULL,
            `client_request_id` VARCHAR(80) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
            `payload_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
            `channel` VARCHAR(16) NOT NULL,
            `status` VARCHAR(32) NOT NULL DEFAULT 'new',
            `phone_e164` VARCHAR(32) NOT NULL,
            `phone_hash` CHAR(64) NOT NULL,
            `customer_name` VARCHAR(255) NOT NULL DEFAULT '',
            `reason` VARCHAR(64) NOT NULL DEFAULT 'manual',
            `message_text` MEDIUMTEXT NOT NULL,
            `manager_id` INT(11) NOT NULL DEFAULT 0,
            `chat_id` INT(11) DEFAULT NULL,
            `messenger_contact_id` INT UNSIGNED DEFAULT NULL,
            `message_id` INT(11) DEFAULT NULL,
            `delivery_id` INT UNSIGNED DEFAULT NULL,
            `app_session_id` INT(11) DEFAULT NULL,
            `attempt_count` INT UNSIGNED NOT NULL DEFAULT 0,
            `last_error` VARCHAR(255) NOT NULL DEFAULT '',
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            UNIQUE KEY `uniq_outbound_public_id` (`public_id`),
            UNIQUE KEY `uniq_outbound_client_request` (`manager_id`, `client_request_id`),
            KEY `idx_outbound_status` (`channel`, `status`, `date_added`),
            KEY `idx_outbound_payload_active` (`channel`, `payload_hash`, `status`, `date_added`),
            KEY `idx_outbound_phone` (`channel`, `phone_hash`, `date_added`),
            KEY `idx_outbound_manager` (`manager_id`, `date_added`),
            KEY `idx_outbound_chat` (`chat_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function smsDevicesTableSql($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}sms_devices` (
            `device_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `app_device_id` INT UNSIGNED DEFAULT NULL,
            `app_session_id` INT(11) NOT NULL,
            `manager_id` INT(11) NOT NULL,
            `device_label` VARCHAR(120) NOT NULL DEFAULT 'Android',
            `app_version` VARCHAR(32) NOT NULL DEFAULT '',
            `android_sdk` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            `device_manufacturer` VARCHAR(80) NOT NULL DEFAULT '',
            `device_model` VARCHAR(80) NOT NULL DEFAULT '',
            `reliability_status` VARCHAR(16) NOT NULL DEFAULT 'unknown',
            `battery_unrestricted` TINYINT(1) NOT NULL DEFAULT 0,
            `background_restricted` TINYINT(1) NOT NULL DEFAULT 0,
            `notifications_allowed` TINYINT(1) NOT NULL DEFAULT 0,
            `fcm_synced` TINYINT(1) NOT NULL DEFAULT 0,
            `default_sms_subscription_id` INT NOT NULL DEFAULT -1,
            `reliability_checked_at` DATETIME DEFAULT NULL,
            `admin_requested` TINYINT(1) NOT NULL DEFAULT 0,
            `operator_consent` VARCHAR(16) NOT NULL DEFAULT 'none',
            `android_permission` VARCHAR(16) NOT NULL DEFAULT 'unknown',
            `status` VARCHAR(32) NOT NULL DEFAULT 'not_requested',
            `inbound_admin_requested` TINYINT(1) NOT NULL DEFAULT 0,
            `inbound_operator_consent` VARCHAR(16) NOT NULL DEFAULT 'none',
            `inbound_android_permission` VARCHAR(16) NOT NULL DEFAULT 'unknown',
            `inbound_status` VARCHAR(32) NOT NULL DEFAULT 'not_requested',
            `inbound_sim_mode` VARCHAR(16) NOT NULL DEFAULT 'default_sim',
            `inbound_health_status` VARCHAR(16) NOT NULL DEFAULT 'unknown',
            `inbound_last_error_code` VARCHAR(64) NOT NULL DEFAULT '',
            `inbound_last_error_at` DATETIME DEFAULT NULL,
            `inbound_pending_count` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            `sent_today` INT UNSIGNED NOT NULL DEFAULT 0,
            `requested_at` DATETIME DEFAULT NULL,
            `inbound_requested_at` DATETIME DEFAULT NULL,
            `responded_at` DATETIME DEFAULT NULL,
            `inbound_responded_at` DATETIME DEFAULT NULL,
            `last_seen_at` DATETIME DEFAULT NULL,
            `last_used_at` DATETIME DEFAULT NULL,
            `last_inbound_at` DATETIME DEFAULT NULL,
            `last_inbound_subscription_id` INT NOT NULL DEFAULT -1,
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            UNIQUE KEY `uniq_sms_app_session` (`app_session_id`),
            UNIQUE KEY `uniq_sms_app_device` (`app_device_id`),
            KEY `idx_sms_app_device_manager` (`app_device_id`, `manager_id`, `status`),
            KEY `idx_sms_ready` (`status`, `admin_requested`, `operator_consent`, `android_permission`, `last_used_at`),
            KEY `idx_sms_inbound_ready` (`inbound_status`, `inbound_admin_requested`, `inbound_operator_consent`, `inbound_android_permission`, `last_inbound_at`),
            KEY `idx_sms_manager` (`manager_id`, `status`, `last_seen_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function smsJobsTableSql($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}sms_jobs` (
            `job_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `public_id` VARCHAR(64) NOT NULL,
            `device_id` INT UNSIGNED NOT NULL,
            `app_device_id` INT UNSIGNED DEFAULT NULL,
            `app_session_id` INT(11) NOT NULL,
            `manager_id` INT(11) NOT NULL DEFAULT 0,
            `chat_id` INT(11) NOT NULL DEFAULT 0,
            `message_id` INT(11) NOT NULL DEFAULT 0,
            `delivery_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `phone_e164` VARCHAR(32) NOT NULL,
            `message_text` MEDIUMTEXT NOT NULL,
            `status` VARCHAR(32) NOT NULL DEFAULT 'pending',
            `attempt_count` INT UNSIGNED NOT NULL DEFAULT 0,
            `reservation_key` VARCHAR(96) NOT NULL DEFAULT '',
            `receipt_token_hash` CHAR(64) NOT NULL DEFAULT '',
            `last_error` VARCHAR(255) NOT NULL DEFAULT '',
            `last_stage` VARCHAR(40) NOT NULL DEFAULT 'job_created',
            `last_stage_code` VARCHAR(64) NOT NULL DEFAULT '',
            `last_stage_at` DATETIME DEFAULT NULL,
            `delivery_status` VARCHAR(24) NOT NULL DEFAULT 'not_requested',
            `parts_total` INT UNSIGNED NOT NULL DEFAULT 0,
            `parts_delivered` INT UNSIGNED NOT NULL DEFAULT 0,
            `parts_delivery_failed` INT UNSIGNED NOT NULL DEFAULT 0,
            `reserved_at` DATETIME DEFAULT NULL,
            `completed_at` DATETIME DEFAULT NULL,
            `delivered_at` DATETIME DEFAULT NULL,
            `expires_at` DATETIME DEFAULT NULL,
            `push_attempt_count` TINYINT UNSIGNED NOT NULL DEFAULT 0,
            `last_push_at` DATETIME DEFAULT NULL,
            `next_push_at` DATETIME DEFAULT NULL,
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            UNIQUE KEY `uniq_sms_job_public_id` (`public_id`),
            KEY `idx_sms_job_session` (`app_session_id`, `status`, `job_id`),
            KEY `idx_sms_job_app_device` (`app_device_id`, `status`, `job_id`),
            KEY `idx_sms_job_message` (`message_id`),
            KEY `idx_sms_job_status` (`status`, `date_added`),
            KEY `idx_sms_job_device_recent` (`device_id`, `date_added`),
            KEY `idx_sms_job_expiry` (`status`, `expires_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function smsJobEventsTableSql($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}sms_job_events` (
            `event_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `job_id` INT UNSIGNED NOT NULL,
            `device_id` INT UNSIGNED NOT NULL,
            `client_event_id` VARCHAR(96) NOT NULL,
            `carrier_attempt` SMALLINT UNSIGNED DEFAULT NULL,
            `carrier_part_index` SMALLINT UNSIGNED DEFAULT NULL,
            `stage` VARCHAR(40) NOT NULL,
            `level` VARCHAR(16) NOT NULL DEFAULT 'info',
            `code` VARCHAR(64) NOT NULL DEFAULT '',
            `context_json` TEXT NOT NULL,
            `client_time_ms` BIGINT UNSIGNED NOT NULL DEFAULT 0,
            `date_added` DATETIME NOT NULL,
            UNIQUE KEY `uniq_sms_job_client_event` (`job_id`, `client_event_id`),
            UNIQUE KEY `uniq_sms_carrier_part` (`job_id`, `carrier_attempt`, `carrier_part_index`),
            KEY `idx_sms_event_job` (`job_id`, `event_id`),
            KEY `idx_sms_event_device` (`device_id`, `event_id`),
            KEY `idx_sms_event_date` (`date_added`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function ensureQuickRepliesSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260614_quick_replies_v2';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureQuickRepliesSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureQuickRepliesSchema($db, $prefix) {
        $manager_engine = self::tableEngine($db, $prefix . 'managers');
        $with_foreign_key = $manager_engine === '' || $manager_engine === 'INNODB';

        try {
            $db->query(self::quickRepliesTableSql($prefix, $with_foreign_key));
        } catch (\Throwable $e) {
            if (!$with_foreign_key) {
                throw $e;
            }

             
             
             
            $db->query(self::quickRepliesTableSql($prefix, false));
        }
    }

    private static function quickRepliesTableSql($prefix, $with_foreign_key) {
        $foreign_key = $with_foreign_key
            ? ",
                FOREIGN KEY (`manager_id`) REFERENCES `{$prefix}managers` (`id`) ON DELETE CASCADE"
            : "";

        return "CREATE TABLE IF NOT EXISTS `{$prefix}quick_replies` (
                `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `scope` VARCHAR(16) NOT NULL DEFAULT 'personal',
                `manager_id` INT(11) DEFAULT NULL,
                `title` VARCHAR(80) NOT NULL DEFAULT '',
                `text` MEDIUMTEXT NOT NULL,
                `sort_order` INT(11) NOT NULL DEFAULT 0,
                `is_active` TINYINT(1) NOT NULL DEFAULT 1,
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                INDEX `idx_scope_manager_active` (`scope`, `manager_id`, `is_active`, `sort_order`){$foreign_key}
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function ensureAiSuggestionsSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260614_ai_suggested_replies';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureAiSuggestionsSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

     
    private static function ensureCopilotGhostDefaultOnce($db, $prefix) {
        $migration_key = 'data_migration_20260819_copilot_ghost_default_easytokens_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) return;

        $provider = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = 'chat_ai_provider' LIMIT 1");
        $ai_status = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = 'chat_ai_status' LIMIT 1");
        $ai_key = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = 'chat_ai_key' LIMIT 1");
        $provider_name = strtolower(trim((string)($provider->row['value'] ?? '')));
        $enabled = (string)($ai_status->row['value'] ?? '') === '1' || trim((string)($ai_key->row['value'] ?? '')) !== '';
        if ($provider_name === 'easytokens' && $enabled) {
            $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('chat_ai_copilot_ghost_status', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
        }
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    




    private static function ensureAdminHelpSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260805_admin_help_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if (!$query || !$query->num_rows) {
            foreach (\EasyChat\Model\AdminHelpSession::schemaQueries($prefix) as $sql) {
                $db->query($sql);
            }
            $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
        }
         
        self::ensureIndex($db, $prefix . 'admin_help_messages', 'idx_admin_help_rate_installation', '`role`, `created_at`');
        self::ensureColumn($db, $prefix . 'admin_help_messages', 'feedback', '`feedback` TINYINT NULL DEFAULT NULL AFTER `ip_hash`');
        self::ensureColumn($db, $prefix . 'admin_help_messages', 'feedback_at', '`feedback_at` DATETIME NULL DEFAULT NULL AFTER `feedback`');
    }

     
    private static function ensureAdminHelpLaunchOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260806_admin_help_launch';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) return;

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('chat_admin_help_status', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureAiSuggestionsSchema($db, $prefix) {
        self::ensureColumn($db, $prefix . 'managers', 'ai_suggestions_mode', "`ai_suggestions_mode` VARCHAR(16) NOT NULL DEFAULT 'inherit' AFTER `permission_delete_chats`");
        self::ensureIndex($db, $prefix . 'managers', 'idx_ai_suggestions_mode', "`ai_suggestions_mode`");
        $db->query(self::aiSuggestionCacheTableSql($prefix));
    }

    private static function aiSuggestionCacheTableSql($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}ai_suggestion_cache` (
                `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `manager_id` INT(11) NOT NULL,
                `chat_id` INT(11) NOT NULL,
                `message_id` INT(11) NOT NULL DEFAULT 0,
                `skill` VARCHAR(32) NOT NULL DEFAULT 'support',
                `request_hash` CHAR(64) NOT NULL,
                `suggestions_json` MEDIUMTEXT NOT NULL,
                `source_count` INT(11) NOT NULL DEFAULT 0,
                `status` VARCHAR(16) NOT NULL DEFAULT 'success',
                `error_code` VARCHAR(64) NOT NULL DEFAULT '',
                `created_at` DATETIME NOT NULL,
                `expires_at` DATETIME NOT NULL,
                UNIQUE KEY `uniq_suggestion_context` (`manager_id`, `chat_id`, `message_id`, `skill`, `request_hash`),
                KEY `idx_rate_limit` (`manager_id`, `created_at`),
                KEY `idx_expire` (`expires_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function tableEngine($db, $table) {
        try {
            $result = $db->query("SHOW TABLE STATUS LIKE '" . $db->escape($table) . "'");
            if ($result && !empty($result->row['Engine'])) {
                return strtoupper((string)$result->row['Engine']);
            }
        } catch (\Throwable $e) {
        }

        return '';
    }

    private static function ensureMessengerSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260607_messenger_channels';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureMessengerSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureMessengerProviderIncidentSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260813_messenger_provider_incidents_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }
        $db->query(self::messengerProviderIncidentSchemaQuery($prefix));
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function messengerProviderIncidentSchemaQuery($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}messenger_provider_incidents` (
            `incident_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `provider` VARCHAR(32) NOT NULL,
            `channel_id` INT UNSIGNED NOT NULL,
            `contact_id` INT UNSIGNED NULL DEFAULT NULL,
            `chat_id` INT(11) NULL DEFAULT NULL,
            `incident_code` VARCHAR(64) NOT NULL,
            `scope_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
            `status` ENUM('active','resolved') NOT NULL DEFAULT 'active',
            `http_code` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            `first_seen_at` DATETIME NOT NULL,
            `last_seen_at` DATETIME NOT NULL,
            `resolved_at` DATETIME DEFAULT NULL,
            `occurrence_count` INT UNSIGNED NOT NULL DEFAULT 1,
            UNIQUE KEY `uniq_provider_incident_scope` (`provider`, `channel_id`, `incident_code`, `scope_hash`),
            KEY `idx_provider_active` (`provider`, `status`, `last_seen_at`),
            KEY `idx_contact` (`contact_id`),
            KEY `idx_chat` (`chat_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    





    private static function ensureMessageAttachmentIdentitySchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260802_message_attachment_identity_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        $table = $prefix . 'message_attachments';
        $safe_table = str_replace('`', '``', $table);
         
         
        self::ensureColumn($db, $table, 'file_name_hash', "`file_name_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL AFTER `file_name`");
        $db->query(
            "UPDATE `{$safe_table}`
             SET `file_name_hash` = SHA2(`file_name`, 256)
             WHERE `file_name_hash` IS NULL OR `file_name_hash` = ''"
        );
        $db->query(
            "DELETE duplicate_attachment
             FROM `{$safe_table}` duplicate_attachment
             INNER JOIN `{$safe_table}` canonical_attachment
                ON canonical_attachment.`message_id` = duplicate_attachment.`message_id`
               AND canonical_attachment.`file_name_hash` = duplicate_attachment.`file_name_hash`
               AND canonical_attachment.`id` < duplicate_attachment.`id`"
        );
        self::ensureUniqueIndex($db, $table, 'uniq_message_file', "`message_id`, `file_name_hash`");

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureMessengerSchema($db, $prefix) {
        foreach (self::messengerSchemaQueries($prefix) as $sql) {
            $db->query($sql);
        }

        self::ensureColumn($db, $prefix . 'sessions', 'source_provider', "`source_provider` VARCHAR(32) NOT NULL DEFAULT 'widget'");
        self::ensureColumn($db, $prefix . 'sessions', 'source_channel_id', "`source_channel_id` INT UNSIGNED NULL DEFAULT NULL");
        self::ensureColumn($db, $prefix . 'sessions', 'external_contact_id', "`external_contact_id` INT UNSIGNED NULL DEFAULT NULL");
        self::ensureColumn($db, $prefix . 'messages', 'source_provider', "`source_provider` VARCHAR(32) NOT NULL DEFAULT 'widget'");
        self::ensureColumn($db, $prefix . 'messages', 'source_channel_id', "`source_channel_id` INT UNSIGNED NULL DEFAULT NULL");
        self::ensureColumn($db, $prefix . 'messages', 'external_message_id', "`external_message_id` VARCHAR(128) NOT NULL DEFAULT ''");

        self::ensureIndex($db, $prefix . 'sessions', 'idx_source_provider', "`source_provider`, `source_channel_id`");
        self::ensureIndex($db, $prefix . 'sessions', 'idx_archive_source_modified', "`is_archived`, `source_provider`, `date_modified`");
        self::ensureIndex($db, $prefix . 'messages', 'idx_source_provider', "`source_provider`, `source_channel_id`");
    }

    






    private static function ensureMarketplaceSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260714_marketplace_inbox_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        foreach (self::marketplaceSchemaQueries($prefix) as $sql) {
            $db->query($sql);
        }

        self::ensureColumn($db, $prefix . 'messenger_channels', 'external_account_id', "`external_account_id` VARCHAR(128) NOT NULL DEFAULT '' AFTER `provider`");
        self::ensureColumn($db, $prefix . 'messenger_channels', 'auth_type', "`auth_type` VARCHAR(32) NOT NULL DEFAULT 'token' AFTER `external_account_id`");
         
         
         
        self::ensureColumn($db, $prefix . 'messenger_channels', 'capabilities_json', "`capabilities_json` TEXT NULL AFTER `settings_json`");
        self::ensureIndex($db, $prefix . 'messenger_channels', 'idx_provider_account', "`provider`, `external_account_id`");

        self::ensureColumn($db, $prefix . 'sessions', 'conversation_kind', "`conversation_kind` VARCHAR(32) NOT NULL DEFAULT 'chat' AFTER `source_channel_id`");
        self::ensureColumn($db, $prefix . 'sessions', 'external_thread_id', "`external_thread_id` VARCHAR(191) NOT NULL DEFAULT '' AFTER `conversation_kind`");
        self::ensureIndex($db, $prefix . 'sessions', 'idx_marketplace_conversation', "`source_provider`, `conversation_kind`, `date_modified`");
         
         
         
        self::ensureIndex($db, $prefix . 'sessions', 'idx_marketplace_thread', "`source_channel_id`, `external_thread_id`(180)");

        self::ensureColumn($db, $prefix . 'message_attachments', 'delivery_status', "`delivery_status` VARCHAR(24) NOT NULL DEFAULT '' AFTER `external_attachment_id`");
        self::ensureColumn($db, $prefix . 'message_attachments', 'delivery_error', "`delivery_error` VARCHAR(255) NOT NULL DEFAULT '' AFTER `delivery_status`");

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function marketplaceSchemaQueries($prefix) {
        return array(
            "CREATE TABLE IF NOT EXISTS `{$prefix}messenger_channel_streams` (
                `stream_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `channel_id` INT UNSIGNED NOT NULL,
                `stream` VARCHAR(32) NOT NULL,
                `status` VARCHAR(24) NOT NULL DEFAULT 'enabled',
                `cursor_json` MEDIUMTEXT NOT NULL,
                `lease_token` CHAR(64) NOT NULL DEFAULT '',
                `lease_until` DATETIME DEFAULT NULL,
                `next_run_at` DATETIME DEFAULT NULL,
                `last_started_at` DATETIME DEFAULT NULL,
                `last_success_at` DATETIME DEFAULT NULL,
                `last_error_at` DATETIME DEFAULT NULL,
                `last_error` VARCHAR(512) NOT NULL DEFAULT '',
                `failure_count` INT UNSIGNED NOT NULL DEFAULT 0,
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                UNIQUE KEY `uniq_channel_stream` (`channel_id`, `stream`),
                KEY `idx_due_streams` (`status`, `next_run_at`, `lease_until`),
                KEY `idx_channel_status` (`channel_id`, `status`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}marketplace_threads` (
                `thread_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `channel_id` INT UNSIGNED NOT NULL,
                `provider` VARCHAR(32) NOT NULL,
                `conversation_kind` VARCHAR(32) NOT NULL,
                `external_thread_id` VARCHAR(191) NOT NULL,
                `external_thread_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
                `external_chat_id` VARCHAR(191) NOT NULL DEFAULT '',
                `chat_id` INT(11) NULL DEFAULT NULL,
                `state` VARCHAR(32) NOT NULL DEFAULT 'open',
                `reply_secret` TEXT NOT NULL,
                `item_id` VARCHAR(128) NOT NULL DEFAULT '',
                `order_id` VARCHAR(128) NOT NULL DEFAULT '',
                `capabilities_json` TEXT NULL,
                `snapshot_json` MEDIUMTEXT NOT NULL,
                `sync_revision` BIGINT UNSIGNED NOT NULL DEFAULT 0,
                `last_external_at` DATETIME DEFAULT NULL,
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                UNIQUE KEY `uniq_marketplace_thread_hash` (`channel_id`, `conversation_kind`, `external_thread_hash`),
                KEY `idx_provider_state` (`provider`, `state`, `date_modified`),
                KEY `idx_chat` (`chat_id`),
                KEY `idx_item` (`provider`, `item_id`),
                KEY `idx_channel_order` (`channel_id`, `conversation_kind`, `order_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}marketplace_actions` (
                `action_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `channel_id` INT UNSIGNED NOT NULL,
                `thread_id` INT UNSIGNED NULL DEFAULT NULL,
                `provider` VARCHAR(32) NOT NULL,
                `conversation_kind` VARCHAR(32) NOT NULL,
                `action` VARCHAR(64) NOT NULL,
                `resource_key` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
                `idempotency_key` CHAR(64) NOT NULL,
                `thread_revision` BIGINT UNSIGNED NOT NULL DEFAULT 0,
                `request_json` MEDIUMTEXT NOT NULL,
                `response_json` MEDIUMTEXT NOT NULL,
                `status` VARCHAR(24) NOT NULL DEFAULT 'pending',
                `error` VARCHAR(512) NOT NULL DEFAULT '',
                `manager_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                UNIQUE KEY `uniq_marketplace_action` (`channel_id`, `idempotency_key`),
                KEY `idx_thread_date` (`thread_id`, `date_added`),
                KEY `idx_action_resource` (`channel_id`, `action`, `resource_key`, `status`),
                KEY `idx_provider_status` (`provider`, `status`, `date_modified`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            self::marketplaceSyncHintSchemaQuery($prefix),

            self::marketplaceRateBudgetSchemaQuery($prefix)
        );
    }

    private static function marketplaceRateBudgetSchemaQuery($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}marketplace_rate_budgets` (
                `budget_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `provider` VARCHAR(32) NOT NULL,
                `account_scope_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
                `credential_class` VARCHAR(24) NOT NULL,
                `api_group` VARCHAR(32) NOT NULL,
                `last_stream` VARCHAR(32) NOT NULL DEFAULT '',
                `blocked_until` DATETIME DEFAULT NULL,
                `reservation_count` INT UNSIGNED NOT NULL DEFAULT 0,
                `throttle_count` INT UNSIGNED NOT NULL DEFAULT 0,
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                UNIQUE KEY `uniq_marketplace_rate_budget`
                    (`provider`, `account_scope_hash`, `credential_class`, `api_group`),
                KEY `idx_marketplace_rate_blocked` (`blocked_until`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    }

    private static function marketplaceSyncHintSchemaQuery($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}marketplace_sync_hints` (
                `hint_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `channel_id` INT UNSIGNED NOT NULL,
                `stream` VARCHAR(32) NOT NULL,
                `entity_type` VARCHAR(32) NOT NULL,
                `entity_id` VARCHAR(191) NOT NULL,
                `entity_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
                `payload_json` TEXT NULL,
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                UNIQUE KEY `uniq_marketplace_sync_hint` (`channel_id`, `stream`, `entity_type`, `entity_hash`),
                KEY `idx_marketplace_sync_hint_stream` (`channel_id`, `stream`, `hint_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    }

    






    private static function ensureMarketplaceThreadHashSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260715_marketplace_thread_hash_v2';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureColumn(
            $db,
            $prefix . 'marketplace_threads',
            'external_thread_hash',
            "`external_thread_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '' AFTER `external_thread_id`"
        );
        self::ensureColumn(
            $db,
            $prefix . 'marketplace_threads',
            'sync_revision',
            "`sync_revision` BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER `snapshot_json`"
        );
        self::ensureColumn(
            $db,
            $prefix . 'marketplace_actions',
            'thread_revision',
            "`thread_revision` BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER `idempotency_key`"
        );
        $db->query(
            "UPDATE `{$prefix}marketplace_threads`
             SET `external_thread_hash` = SHA2(`external_thread_id`, 256)
             WHERE `external_thread_hash` = ''"
        );
        self::ensureUniqueIndex(
            $db,
            $prefix . 'marketplace_threads',
            'uniq_marketplace_thread_hash',
            "`channel_id`, `conversation_kind`, `external_thread_hash`"
        );
        self::dropIndexIfExists($db, $prefix . 'marketplace_threads', 'uniq_marketplace_thread');

         
         
        self::dropIndexIfExists($db, $prefix . 'sessions', 'idx_marketplace_thread');
        self::ensureIndex(
            $db,
            $prefix . 'sessions',
            'idx_marketplace_thread',
            "`source_channel_id`, `external_thread_id`(180)"
        );

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    




    private static function ensureMarketplaceActionResourceSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260715_marketplace_action_resource_v3';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureColumn(
            $db,
            $prefix . 'marketplace_actions',
            'resource_key',
            "`resource_key` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '' AFTER `action`"
        );
        self::ensureIndex(
            $db,
            $prefix . 'marketplace_actions',
            'idx_action_resource',
            "`channel_id`, `action`, `resource_key`, `status`"
        );
        self::ensureIndex(
            $db,
            $prefix . 'marketplace_threads',
            'idx_channel_order',
            "`channel_id`, `conversation_kind`, `order_id`"
        );

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    




    private static function ensureMarketplaceSyncHintSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260725_marketplace_sync_hints_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        $db->query(self::marketplaceSyncHintSchemaQuery($prefix));
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    



    private static function ensureMarketplaceReliabilitySchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260728_marketplace_reliability_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        $db->query(self::marketplaceRateBudgetSchemaQuery($prefix));
        self::ensureColumn(
            $db,
            $prefix . 'marketplace_rate_budgets',
            'last_stream',
            "`last_stream` VARCHAR(32) NOT NULL DEFAULT '' AFTER `api_group`"
        );
        self::ensureColumn(
            $db,
            $prefix . 'messenger_deliveries',
            'provider_occurred_at',
            "`provider_occurred_at` DATETIME DEFAULT NULL AFTER `payload_hash`"
        );
        self::ensureColumn(
            $db,
            $prefix . 'messenger_deliveries',
            'ingest_mode',
            "`ingest_mode` VARCHAR(24) NOT NULL DEFAULT 'live' AFTER `provider_occurred_at`"
        );
        $db->query(
            "UPDATE `{$prefix}messenger_channels`
             SET `auth_type` = 'api_token'
             WHERE `provider` = 'wildberries' AND `auth_type` = 'personal_token'"
        );
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    




    private static function ensureWidgetOperatorVisibilitySchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260728_widget_operator_visibility_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureColumn(
            $db,
            $prefix . 'managers',
            'show_in_widget_header',
            "`show_in_widget_header` INT(1) NOT NULL DEFAULT 1 AFTER `ai_suggestions_mode`"
        );
        self::ensureIndex(
            $db,
            $prefix . 'managers',
            'idx_widget_header',
            "`status`, `show_in_widget_header`, `last_active`, `id`"
        );
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureConversationStartSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260712_conversation_starts';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        $db->query(self::conversationStartSchemaQuery($prefix));
        self::ensureColumn($db, $prefix . 'sessions', 'conversation_start_message_id', "`conversation_start_message_id` INT(11) NOT NULL DEFAULT 0");
        self::ensureColumn($db, $prefix . 'sessions', 'conversation_started_at', "`conversation_started_at` DATETIME DEFAULT NULL");
        self::ensureColumn($db, $prefix . 'messages', 'message_kind', "`message_kind` VARCHAR(32) NOT NULL DEFAULT 'normal'");
        self::ensureIndex($db, $prefix . 'sessions', 'idx_conversation_start', "`conversation_start_message_id`, `chat_id`");
        self::ensureIndex($db, $prefix . 'messages', 'idx_chat_kind_message', "`chat_id`, `message_kind`, `message_id`");
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function conversationStartSchemaQuery($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}messenger_conversation_starts` (
            `start_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `channel_id` INT UNSIGNED NOT NULL,
            `contact_id` INT UNSIGNED NULL DEFAULT NULL,
            `chat_id` INT(11) NULL DEFAULT NULL,
            `provider` VARCHAR(32) NOT NULL,
            `dedupe_key` VARCHAR(128) NOT NULL,
            `start_payload` VARCHAR(128) NOT NULL DEFAULT '',
            `boundary_message_id` INT(11) NOT NULL DEFAULT 0,
            `greeting_message_id` INT(11) NOT NULL DEFAULT 0,
            `notice_message_id` INT(11) NOT NULL DEFAULT 0,
            `status` ENUM('processing','greeted','notified','silent','failed','ignored') NOT NULL DEFAULT 'processing',
            `processing_until` DATETIME DEFAULT NULL,
            `attempts` TINYINT UNSIGNED NOT NULL DEFAULT 1,
            `last_error` VARCHAR(512) NOT NULL DEFAULT '',
            `event_at` DATETIME DEFAULT NULL,
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            UNIQUE KEY `uniq_channel_start` (`channel_id`, `dedupe_key`),
            KEY `idx_chat_date` (`chat_id`, `date_added`),
            KEY `idx_status_lease` (`status`, `processing_until`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    }

    private static function ensureExternalChatDeletionSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260712_external_chat_deletions';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        $db->query(self::externalChatDeletionSchemaQuery($prefix));
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function externalChatDeletionSchemaQuery($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}messenger_chat_deletions` (
            `deletion_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `channel_id` INT UNSIGNED NOT NULL,
            `provider` VARCHAR(32) NOT NULL,
            `external_chat_id` VARCHAR(128) NOT NULL,
            `deleted_at` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            UNIQUE KEY `uniq_channel_external_chat` (`channel_id`, `external_chat_id`),
            KEY `idx_provider_deleted` (`provider`, `deleted_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    }

    private static function ensureMaxBotProviderRenameOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260705_max_bot_provider_rename';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        foreach ([
            [$prefix . 'messenger_channels', 'provider'],
            [$prefix . 'messenger_contacts', 'provider'],
            [$prefix . 'sessions', 'source_provider'],
            [$prefix . 'messages', 'source_provider'],
            [$prefix . 'message_attachments', 'provider'],
        ] as $target) {
            [$table, $column] = $target;
            if (self::columnExists($db, $table, $column)) {
                $db->query("UPDATE `{$table}` SET `{$column}` = 'max-bot' WHERE `{$column}` = 'max'");
            }
        }

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    



    private static function ensurePersonalMaxRetiredOnce($db, $prefix) {
        $migration_key = 'data_migration_20260828_personal_max_retired_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        $db->query(
            "UPDATE `{$prefix}messenger_channels`
             SET `status` = 'disabled', `credentials_json` = '{}',
                 `settings_json` = '{}', `webhook_url` = '', `last_error` = ''
             WHERE `provider` = 'max'"
        );
        $db->query(
            "DELETE FROM `{$prefix}settings`
             WHERE `key` IN (
                'chat_max_personal_status', 'chat_max_logging',
                'chat_max_logging_until_ts', 'chat_max_cron_heartbeat_history'
             )"
        );
        $db->query(
            "INSERT INTO `{$prefix}settings` (`key`, `value`)
             VALUES ('" . $db->escape($migration_key) . "', '1')
             ON DUPLICATE KEY UPDATE `value` = '1'"
        );
    }

    private static function ensureMaxIdentityRepairSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260729_max_identity_repair_fairness';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureColumn(
            $db,
            $prefix . 'messenger_contacts',
            'identity_repair_checked_at',
            "`identity_repair_checked_at` DATETIME DEFAULT NULL AFTER `avatar_last_error`"
        );
        self::ensureIndex(
            $db,
            $prefix . 'messenger_contacts',
            'idx_identity_repair',
            "`channel_id`, `provider`, `identity_repair_checked_at`, `contact_id`"
        );
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureContactAvatarSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260618_contact_avatars';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureContactAvatarSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureContactAvatarSchema($db, $prefix) {
        self::ensureColumn($db, $prefix . 'messenger_contacts', 'avatar_file', "`avatar_file` VARCHAR(128) NOT NULL DEFAULT '' AFTER `profile_url`");
        $db->query("ALTER TABLE `{$prefix}messenger_contacts` MODIFY COLUMN `avatar_file` VARCHAR(128) NOT NULL DEFAULT ''");
        self::ensureColumn($db, $prefix . 'messenger_contacts', 'avatar_mime', "`avatar_mime` VARCHAR(64) NOT NULL DEFAULT '' AFTER `avatar_file`");
        self::ensureColumn($db, $prefix . 'messenger_contacts', 'avatar_source_hash', "`avatar_source_hash` CHAR(64) NOT NULL DEFAULT '' AFTER `avatar_mime`");
        self::ensureColumn($db, $prefix . 'messenger_contacts', 'avatar_updated_at', "`avatar_updated_at` DATETIME DEFAULT NULL AFTER `avatar_source_hash`");
        self::ensureColumn($db, $prefix . 'messenger_contacts', 'avatar_checked_at', "`avatar_checked_at` DATETIME DEFAULT NULL AFTER `avatar_updated_at`");
        self::ensureColumn($db, $prefix . 'messenger_contacts', 'avatar_last_error', "`avatar_last_error` VARCHAR(128) NOT NULL DEFAULT '' AFTER `avatar_checked_at`");
        self::ensureIndex($db, $prefix . 'messenger_contacts', 'idx_avatar_file', "`avatar_file`");
    }

    private static function ensureContactAvatarIdentitySchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260730_contact_avatar_identity_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureColumn(
            $db,
            $prefix . 'messenger_contacts',
            'avatar_identity_fingerprint',
            "`avatar_identity_fingerprint` CHAR(64) NOT NULL DEFAULT '' AFTER `avatar_source_hash`"
        );
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureContactAvatarFileWidthOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260618_contact_avatar_file_width';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureColumn($db, $prefix . 'messenger_contacts', 'avatar_file', "`avatar_file` VARCHAR(128) NOT NULL DEFAULT '' AFTER `profile_url`");
        $db->query("ALTER TABLE `{$prefix}messenger_contacts` MODIFY COLUMN `avatar_file` VARCHAR(128) NOT NULL DEFAULT ''");
        self::ensureIndex($db, $prefix . 'messenger_contacts', 'idx_avatar_file', "`avatar_file`");

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureCustomerIdentitySchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260623_customer_identity_hash';
        self::ensureCustomerIdentitySchema($db, $prefix);

        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::backfillCustomerIdentityHashes($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureCommerceIdentitySchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260813_commerce_identity_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key`='" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) return;
        $db->query(self::commerceIdentitySchemaQuery($prefix));
        $db->query("INSERT INTO `{$prefix}settings` (`key`,`value`) VALUES ('" . $db->escape($migration_key) . "','1') ON DUPLICATE KEY UPDATE `value`='1'");
    }

    private static function ensureCmsVisitorIdentitySchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260817_cms_visitor_identity_v1';
        $db->query(self::cmsVisitorIdentitySchemaQuery($prefix));
        self::ensureColumn($db, $prefix . 'sessions', 'cms_identity_id', "`cms_identity_id` INT UNSIGNED NULL DEFAULT NULL AFTER `customer_id`");
        self::ensureColumn($db, $prefix . 'sessions', 'cms_identity_bound_at', "`cms_identity_bound_at` DATETIME NULL DEFAULT NULL AFTER `cms_identity_id`");
        self::ensureIndex($db, $prefix . 'sessions', 'idx_cms_identity', "`cms_identity_id`, `chat_id`");
        $db->query("INSERT INTO `{$prefix}settings` (`key`,`value`) VALUES ('" . $db->escape($migration_key) . "','1') ON DUPLICATE KEY UPDATE `value`='1'");
    }

    private static function cmsVisitorIdentitySchemaQuery($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}cms_customer_identities` (
            `identity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `customer_id` INT UNSIGNED NOT NULL,
            `provider` VARCHAR(32) NOT NULL,
            `scope_hash` CHAR(64) NOT NULL,
            `subject_hash` CHAR(64) NOT NULL,
            `subject_ciphertext` VARCHAR(512) NOT NULL,
            `assurance` VARCHAR(24) NOT NULL DEFAULT 'cms_session',
            `last_seen_at` DATETIME NOT NULL,
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            UNIQUE KEY `uniq_cms_subject` (`provider`,`scope_hash`,`subject_hash`),
            INDEX `idx_cms_customer` (`customer_id`,`last_seen_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    }

    private static function commerceIdentitySchemaQuery($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}commerce_customer_identities` (
            `identity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `customer_id` INT UNSIGNED NOT NULL,
            `provider` VARCHAR(24) NOT NULL,
            `scope_hash` CHAR(64) NOT NULL,
            `external_customer_id` VARCHAR(64) NOT NULL,
            `external_kind` VARCHAR(32) NOT NULL,
            `evidence` VARCHAR(24) NOT NULL DEFAULT '',
            `confidence` VARCHAR(24) NOT NULL DEFAULT 'unique_exact',
            `email_hash` CHAR(64) NOT NULL DEFAULT '',
            `phone_hash` CHAR(64) NOT NULL DEFAULT '',
            `verified_at` DATETIME DEFAULT NULL,
            `last_seen_at` DATETIME NOT NULL,
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            UNIQUE KEY `uniq_customer_provider_scope` (`customer_id`,`provider`,`scope_hash`),
            INDEX `idx_external_identity` (`provider`,`scope_hash`,`external_customer_id`),
            INDEX `idx_email_identity` (`provider`,`scope_hash`,`email_hash`),
            INDEX `idx_phone_identity` (`provider`,`scope_hash`,`phone_hash`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    }

    private static function ensureCustomerIdentitySchema($db, $prefix) {
        self::ensureColumn($db, $prefix . 'messenger_contacts', 'customer_identity_hash', "`customer_identity_hash` CHAR(64) NOT NULL DEFAULT '' AFTER `metadata_json`");
        self::ensureIndex($db, $prefix . 'messenger_contacts', 'idx_customer_identity', "`provider`, `channel_id`, `customer_identity_hash`, `chat_id`");
    }

    private static function backfillCustomerIdentityHashes($db, $prefix) {
        $resolver = new CustomerIdentityResolver();
        $last_id = 0;

        while (true) {
            $query = $db->query(
                "SELECT `contact_id`, `channel_id`, `provider`, `external_user_id`, `metadata_json`, `customer_identity_hash`
                 FROM `{$prefix}messenger_contacts`
                 WHERE `contact_id` > " . (int)$last_id . "
                 ORDER BY `contact_id` ASC
                 LIMIT 500"
            );

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

            foreach ($query->rows as $row) {
                $last_id = max($last_id, (int)$row['contact_id']);
                if ((string)($row['customer_identity_hash'] ?? '') !== '') {
                    continue;
                }

                $hash = $resolver->hashForMessengerContact($row);
                if ($hash === '') {
                    continue;
                }

                $db->query(
                    "UPDATE `{$prefix}messenger_contacts`
                     SET `customer_identity_hash` = '" . $db->escape($hash) . "'
                     WHERE `contact_id` = " . (int)$row['contact_id']
                );
            }
        }
    }

    private static function ensureAvitoMessageContextSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260620_avito_message_contexts';
        self::ensureAvitoMessageContextSchema($db, $prefix);

        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureAvitoMessageContextSchema($db, $prefix) {
        $db->query(\EasyChat\Model\MessageContext::tableSql($prefix));

         
        self::ensureIndex($db, $prefix . 'messages', 'idx_external_message_lookup', "`source_provider`, `source_channel_id`, `external_message_id`");
        self::ensureIndex($db, $prefix . 'messenger_deliveries', 'idx_channel_external_message', "`channel_id`, `external_message_id`");
    }

    private static function ensureMessengerItemContextCacheSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260620_messenger_item_context_cache';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureMessengerItemContextCacheSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureMessengerItemContextCacheSchema($db, $prefix) {
        $db->query(\EasyChat\Model\MessengerItemContext::tableSql($prefix));
    }

    private static function ensureMarketplaceItemContextProjectionOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260825_marketplace_item_context_projection';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

         
         
        self::ensureIndex(
            $db,
            $prefix . 'messenger_message_contexts',
            'idx_provider_channel_item_message',
            "`provider`, `channel_id`, `item_id`, `message_id`"
        );
        $result = $db->query(\EasyChat\Model\MessengerItemContext::projectionBackfillSql($prefix));
        if ($result === false) {
            return;
        }

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureMessageManagerNameSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260613_messages_manager_name';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureColumn($db, $prefix . 'messages', 'manager_name', "`manager_name` VARCHAR(255) NOT NULL DEFAULT ''");

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureChatAssignmentsSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260729_chat_operator_assignments_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        $db->query(self::chatAssignmentsSchemaQuery($prefix));

         
         
         
        $db->query(
            "INSERT INTO `{$prefix}chat_operator_assignments`
                (`chat_id`, `manager_id`, `assigned_by_manager_id`, `assignment_source`,
                 `assigned_at`, `last_activity_at`)
             SELECT m.`chat_id`, m.`manager_id`, m.`manager_id`, 'history',
                    MIN(m.`date_added`), MAX(m.`date_added`)
             FROM `{$prefix}messages` m
             INNER JOIN `{$prefix}sessions` s ON s.`chat_id` = m.`chat_id`
             INNER JOIN `{$prefix}managers` manager ON manager.`id` = m.`manager_id`
             WHERE m.`author` = 2 AND m.`manager_id` IS NOT NULL AND m.`manager_id` > 0
             GROUP BY m.`chat_id`, m.`manager_id`
             ON DUPLICATE KEY UPDATE
                `last_activity_at` = GREATEST(`last_activity_at`, VALUES(`last_activity_at`))"
        );

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureChatAssignmentPermissionsSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260730_chat_assignment_permissions_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

         
         
         
        self::ensureColumn(
            $db,
            $prefix . 'managers',
            'permission_manage_chat_assignments',
            "`permission_manage_chat_assignments` TINYINT(1) NOT NULL DEFAULT 0 AFTER `permission_delete_chats`"
        );

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function chatAssignmentsSchemaQuery($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}chat_operator_assignments` (
            `chat_id` INT(11) NOT NULL,
            `manager_id` INT(11) NOT NULL,
            `assigned_by_manager_id` INT(11) NOT NULL DEFAULT 0,
            `assignment_source` VARCHAR(24) NOT NULL DEFAULT 'manual',
            `assigned_at` DATETIME NOT NULL,
            `last_activity_at` DATETIME NOT NULL,
            PRIMARY KEY (`chat_id`, `manager_id`),
            KEY `idx_manager_chat` (`manager_id`, `chat_id`),
            KEY `idx_chat_activity` (`chat_id`, `last_activity_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function ensureOperatorDraftSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260803_operator_message_drafts_v1';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) return;
        $db->query(\EasyChat\Model\OperatorDraft::tableSql($prefix));
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureAndroidSyncSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260614_android_incremental_sync';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureAndroidSyncSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureDirectMessageIdempotencySchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260722_direct_message_idempotency_v2';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

         
         
        self::ensureQueueTable($db, $prefix);
        $db->query(self::directMessageIdempotencySchemaQuery($prefix));
        self::ensureIndex($db, $prefix . 'message_send_requests', 'idx_message_send_retention', "`status`, `date_modified`");
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function directMessageIdempotencySchemaQuery($prefix) {
        return "CREATE TABLE IF NOT EXISTS `{$prefix}message_send_requests` (
            `request_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `request_scope` VARCHAR(96) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
            `client_request_id` VARCHAR(80) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
            `payload_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
            `chat_id` INT(11) NOT NULL,
            `manager_id` INT(11) NOT NULL,
            `message_id` INT(11) NOT NULL DEFAULT 0,
            `status` ENUM('processing','completed') NOT NULL DEFAULT 'processing',
            `processing_token_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
            `processing_until` DATETIME NULL DEFAULT NULL,
            `result_json` TEXT NOT NULL,
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            UNIQUE KEY `uniq_message_send_request` (`request_scope`, `client_request_id`),
            KEY `idx_message_send_lease` (`status`, `processing_until`),
            KEY `idx_message_send_retention` (`status`, `date_modified`),
            KEY `idx_message_send_message` (`message_id`),
            KEY `idx_message_send_chat` (`chat_id`, `date_added`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function ensureAndroidSyncSchema($db, $prefix) {
        self::ensureColumn($db, $prefix . 'messages', 'date_modified', "`date_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `date_added`");
        self::ensureIndex($db, $prefix . 'messages', 'idx_android_message_sync', "`chat_id`, `message_id`, `date_modified`");
        self::ensureIndex($db, $prefix . 'sessions', 'idx_android_chat_sync', "`is_archived`, `date_modified`, `chat_id`");

        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}chat_sync_deletions` (
            `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `chat_id` INT(11) NOT NULL,
            `deleted_at` DATETIME NOT NULL,
            UNIQUE KEY `uniq_chat_id` (`chat_id`),
            INDEX `idx_deleted_at` (`deleted_at`, `chat_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $db->query("UPDATE `{$prefix}messages`
            SET `date_modified` = `date_added`
            WHERE `date_modified` IS NULL
               OR `date_modified` = '0000-00-00 00:00:00'");
    }

    private static function ensureChatDeletionSourceSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260712_chat_deletion_source';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureColumn($db, $prefix . 'chat_sync_deletions', 'source_provider', "`source_provider` VARCHAR(32) NOT NULL DEFAULT '' AFTER `chat_id`");
        self::ensureIndex($db, $prefix . 'chat_sync_deletions', 'idx_source_deleted', "`source_provider`, `deleted_at`, `chat_id`");
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureAdminPollingSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260617_admin_polling_indexes';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureAdminPollingSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureAdminPollingSchema($db, $prefix) {
        self::ensureIndex($db, $prefix . 'messages', 'idx_admin_unread', "`status_admin`, `author`");
        self::ensureIndex($db, $prefix . 'messages', 'idx_admin_unread_chat', "`chat_id`, `status_admin`, `author`");
    }

    private static function ensureChatListPerformanceSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260622_chat_list_performance_indexes';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureChatListPerformanceSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureChatListPerformanceSchema($db, $prefix) {
        self::ensureIndex($db, $prefix . 'messages', 'idx_chat_last_message_lookup', "`chat_id`, `date_added`, `message_id`");
        self::ensureIndex($db, $prefix . 'messages', 'idx_admin_unread_global', "`status_admin`, `author`, `chat_id`");
    }

    private static function ensureChatListFilterSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260622_chat_list_filter_indexes';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureChatListFilterSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureChatListFilterSchema($db, $prefix) {
        self::ensureIndex($db, $prefix . 'sessions', 'idx_archive_source_modified', "`is_archived`, `source_provider`, `date_modified`");
    }

    private static function ensureAdminOpenChatPerformanceSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260623_admin_open_chat_performance_indexes';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureAdminOpenChatPerformanceSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureAdminOpenChatPerformanceSchema($db, $prefix) {
        self::ensureIndex($db, $prefix . 'messages', 'idx_chat_message_id', "`chat_id`, `message_id`");
    }

    private static function ensureResponseTimeMetricsSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260617_response_time_metrics';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureResponseTimeMetricsSchema($db, $prefix);
        self::queueResponseTimeMetricsBackfill($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureResponseTimeMetricsSchema($db, $prefix) {
        self::ensureColumn($db, $prefix . 'messages', 'first_response_message_id', "`first_response_message_id` INT(11) NOT NULL DEFAULT 0 AFTER `date_modified`");
        self::ensureColumn($db, $prefix . 'messages', 'first_response_seconds', "`first_response_seconds` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `first_response_message_id`");
        self::ensureColumn($db, $prefix . 'messages', 'first_response_author', "`first_response_author` TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER `first_response_seconds`");
        self::ensureColumn($db, $prefix . 'messages', 'first_response_manager_id', "`first_response_manager_id` INT(11) NOT NULL DEFAULT 0 AFTER `first_response_author`");
        self::ensureColumn($db, $prefix . 'messages', 'first_response_recorded_at', "`first_response_recorded_at` DATETIME DEFAULT NULL AFTER `first_response_manager_id`");
        self::ensureColumn($db, $prefix . 'messages', 'response_metric_checked_at', "`response_metric_checked_at` DATETIME DEFAULT NULL AFTER `first_response_recorded_at`");
        self::ensureColumn($db, $prefix . 'messages', 'response_metric_is_first_customer', "`response_metric_is_first_customer` TINYINT(1) NOT NULL DEFAULT 0 AFTER `response_metric_checked_at`");
        self::ensureColumn($db, $prefix . 'messages', 'response_metric_human_frt_seconds', "`response_metric_human_frt_seconds` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `response_metric_is_first_customer`");
        self::ensureColumn($db, $prefix . 'messages', 'response_metric_human_frt_recorded_at', "`response_metric_human_frt_recorded_at` DATETIME DEFAULT NULL AFTER `response_metric_human_frt_seconds`");

        self::ensureIndex($db, $prefix . 'messages', 'idx_response_time_lookup', "`chat_id`, `author`, `date_added`");
        self::ensureIndex($db, $prefix . 'messages', 'idx_response_metric_pending', "`chat_id`, `author`, `first_response_message_id`, `response_metric_checked_at`, `date_added`, `message_id`");

        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}response_time_summary` (
            `metric_key` VARCHAR(64) NOT NULL,
            `entity_id` INT(11) NOT NULL DEFAULT 0,
            `response_count` INT UNSIGNED NOT NULL DEFAULT 0,
            `response_seconds_sum` BIGINT UNSIGNED NOT NULL DEFAULT 0,
            `updated_at` DATETIME NOT NULL,
            PRIMARY KEY (`metric_key`, `entity_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('response_time_metrics_backfill_status', 'pending') ON DUPLICATE KEY UPDATE `value` = IF(`value` = 'done', `value`, 'pending')");
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('response_time_metrics_backfill_cursor', '0') ON DUPLICATE KEY UPDATE `value` = `value`");
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('response_time_metrics_backfill_updated', '" . time() . "') ON DUPLICATE KEY UPDATE `value` = `value`");
    }

    private static function ensureResponseTimeMetricsCleanupOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260617_response_time_metrics_cleanup';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

         
         
         
         
        self::dropIndexIfExists($db, $prefix . 'messages', 'idx_response_customer');
        self::dropIndexIfExists($db, $prefix . 'messages', 'idx_response_metric_recorded');
        self::dropIndexIfExists($db, $prefix . 'messages', 'idx_response_metric_human');

        $db->query("DELETE FROM `{$prefix}settings` WHERE `key` IN ('chat_avg_response_time', 'chat_avg_response_time_updated')");
        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function queueResponseTimeMetricsBackfill($db, $prefix) {
        self::ensureQueueTable($db, $prefix);

        $existing = $db->query(
            "SELECT `id`
             FROM `{$prefix}queue`
             WHERE `job_type` = 'response_time_metrics_backfill'
               AND `status` IN ('pending', 'processing', 'done')
             LIMIT 1"
        );
        if ($existing && $existing->num_rows) {
            return;
        }

        $payload = $db->escape(json_encode(array('last_message_id' => 0, 'batch_size' => 500), JSON_UNESCAPED_UNICODE));
        $now = time();
        $db->query(
            "INSERT INTO `{$prefix}queue`
                (`job_type`, `payload`, `status`, `attempts`, `max_attempts`, `available_at`, `locked_at`, `lock_token`, `created_at`, `error`)
             VALUES
                ('response_time_metrics_backfill', '{$payload}', 'pending', 0, 3, {$now}, NULL, NULL, {$now}, NULL)"
        );
    }

    private static function ensureQueueTable($db, $prefix) {
        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}queue` (
            `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `job_type` VARCHAR(64) NOT NULL,
            `payload` TEXT NOT NULL,
            `dedupe_key` VARCHAR(191) NULL DEFAULT NULL,
            `status` ENUM('pending','processing','done','failed') NOT NULL DEFAULT 'pending',
            `attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0,
            `max_attempts` TINYINT UNSIGNED NOT NULL DEFAULT 3,
            `priority` TINYINT UNSIGNED NOT NULL DEFAULT 0,
            `available_at` INT UNSIGNED NOT NULL,
            `locked_at` INT UNSIGNED NULL DEFAULT NULL,
            `lock_token` CHAR(32) NULL DEFAULT NULL,
            `created_at` INT UNSIGNED NOT NULL,
            `finished_at` INT UNSIGNED NULL DEFAULT NULL,
            `error` TEXT NULL DEFAULT NULL,
            INDEX `idx_status_available` (`status`, `available_at`),
            INDEX `idx_status_priority` (`status`, `priority`, `available_at`),
            INDEX `idx_status_finished` (`status`, `finished_at`),
            UNIQUE KEY `uq_active_dedupe` (`dedupe_key`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        self::ensureColumn($db, $prefix . 'queue', 'priority', "`priority` TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER `max_attempts`");
        self::ensureColumn($db, $prefix . 'queue', 'dedupe_key', "`dedupe_key` VARCHAR(191) NULL DEFAULT NULL AFTER `payload`");
        self::ensureColumn($db, $prefix . 'queue', 'finished_at', "`finished_at` INT UNSIGNED NULL DEFAULT NULL AFTER `created_at`");
        self::ensureIndex($db, $prefix . 'queue', 'idx_status_priority', "`status`, `priority`, `available_at`");
        self::ensureIndex($db, $prefix . 'queue', 'idx_status_finished', "`status`, `finished_at`");
        self::ensureUniqueIndex($db, $prefix . 'queue', 'uq_active_dedupe', "`dedupe_key`");
    }

    private static function ensureAiProcessingSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260607_ai_processing_lock';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureAiProcessingSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureAiProcessingSchema($db, $prefix) {
        self::ensureColumn($db, $prefix . 'sessions', 'ai_processing_message_id', "`ai_processing_message_id` INT(11) NOT NULL DEFAULT 0");
        self::ensureColumn($db, $prefix . 'sessions', 'ai_processing_until', "`ai_processing_until` INT(11) NOT NULL DEFAULT 0");
        self::ensureColumn($db, $prefix . 'sessions', 'ai_last_attempt_message_id', "`ai_last_attempt_message_id` INT(11) NOT NULL DEFAULT 0");
        self::ensureColumn($db, $prefix . 'sessions', 'ai_last_attempt_status', "`ai_last_attempt_status` VARCHAR(16) NOT NULL DEFAULT ''");
        self::ensureColumn($db, $prefix . 'sessions', 'ai_last_error', "`ai_last_error` VARCHAR(255) NOT NULL DEFAULT ''");
        self::ensureColumn($db, $prefix . 'sessions', 'ai_last_attempt_at', "`ai_last_attempt_at` DATETIME DEFAULT NULL");

        self::ensureIndex($db, $prefix . 'sessions', 'idx_ai_processing', "`ai_processing_until`, `ai_processing_message_id`");
        self::ensureIndex($db, $prefix . 'sessions', 'idx_ai_last_attempt', "`ai_last_attempt_message_id`, `ai_last_attempt_status`");
    }

    private static function ensureWidgetUnreadSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260608_widget_unread_count';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureWidgetUnreadSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureWidgetUnreadSchema($db, $prefix) {
        self::ensureIndex($db, $prefix . 'messages', 'idx_widget_unread_user', "`chat_id`, `status_user`, `author`");
    }

    private static function ensureCustomerPanelSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260623_customer_panel';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureCustomerPanelSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureCustomerPanelSchema($db, $prefix) {
        self::ensureColumn($db, $prefix . 'sessions', 'customer_id', "`customer_id` INT UNSIGNED NULL DEFAULT NULL AFTER `chat_id`");
        self::ensureIndex($db, $prefix . 'sessions', 'idx_customer_id', "`customer_id`, `date_modified`");

        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}customers` (
            `customer_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `display_name` VARCHAR(255) NOT NULL DEFAULT '',
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            INDEX `idx_customer_modified` (`date_modified`, `customer_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}customer_profiles` (
            `profile_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `customer_id` INT UNSIGNED NOT NULL,
            `provider` VARCHAR(32) NOT NULL,
            `profile_url` VARCHAR(512) NOT NULL,
            `profile_url_hash` CHAR(64) NOT NULL,
            `username` VARCHAR(80) NOT NULL DEFAULT '',
            `external_user_id` VARCHAR(128) NOT NULL DEFAULT '',
            `messenger_contact_id` INT UNSIGNED NULL DEFAULT NULL,
            `source` VARCHAR(24) NOT NULL DEFAULT 'operator',
            `confidence` TINYINT UNSIGNED NOT NULL DEFAULT 100,
            `added_by` INT UNSIGNED NOT NULL DEFAULT 0,
            `message_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            UNIQUE KEY `uniq_provider_profile_hash` (`provider`, `profile_url_hash`),
            INDEX `idx_customer_provider` (`customer_id`, `provider`),
            INDEX `idx_messenger_contact` (`messenger_contact_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        self::ensureCustomerProfileUnlinkSchema($db, $prefix);

        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}customer_notes` (
            `note_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `customer_id` INT UNSIGNED NOT NULL,
            `chat_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `manager_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `note_type` VARCHAR(16) NOT NULL DEFAULT 'chat',
            `text` TEXT NOT NULL,
            `is_pinned` TINYINT(1) NOT NULL DEFAULT 0,
            `pinned_at` DATETIME NULL DEFAULT NULL,
            `pinned_by` INT UNSIGNED NOT NULL DEFAULT 0,
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            INDEX `idx_customer_chat_notes` (`customer_id`, `chat_id`, `date_added`),
            INDEX `idx_customer_chat_pinned` (`customer_id`, `chat_id`, `is_pinned`, `pinned_at`, `date_added`),
            INDEX `idx_manager_notes` (`manager_id`, `date_added`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}customer_tags` (
            `tag_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `label` VARCHAR(80) NOT NULL,
            `label_hash` CHAR(64) NOT NULL,
            `color` CHAR(7) NOT NULL DEFAULT '#DBEAFE',
            `date_added` DATETIME NOT NULL,
            UNIQUE KEY `uniq_label_hash` (`label_hash`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}customer_tag_links` (
            `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `customer_id` INT UNSIGNED NOT NULL,
            `chat_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `tag_id` INT UNSIGNED NOT NULL,
            `date_added` DATETIME NOT NULL,
            UNIQUE KEY `uniq_customer_chat_tag` (`customer_id`, `chat_id`, `tag_id`),
            INDEX `idx_tag_customer` (`tag_id`, `customer_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }

    private static function ensureCustomerIpGeoCacheSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260625_customer_ip_geo_cache';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureCustomerIpGeoCacheSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureCustomerIpGeoCacheSchema($db, $prefix) {
        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}customer_ip_geo_cache` (
            `ip_hash` CHAR(64) NOT NULL PRIMARY KEY,
            `ip` VARCHAR(45) NOT NULL,
            `location` VARCHAR(160) NOT NULL DEFAULT '',
            `status` VARCHAR(24) NOT NULL DEFAULT 'pending',
            `checked_at` DATETIME NOT NULL,
            `expires_at` DATETIME NOT NULL,
            INDEX `idx_expires_at` (`expires_at`),
            INDEX `idx_status_checked` (`status`, `checked_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }

    private static function ensureCustomerProfileUnlinkSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260624_customer_profile_unlink';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureCustomerProfileUnlinkSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureCustomerProfileUnlinkSchema($db, $prefix) {
        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}customer_profile_events` (
            `event_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `event_type` VARCHAR(24) NOT NULL,
            `customer_id` INT UNSIGNED NOT NULL,
            `profile_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `provider` VARCHAR(32) NOT NULL,
            `profile_url` VARCHAR(512) NOT NULL,
            `profile_url_hash` CHAR(64) NOT NULL,
            `username` VARCHAR(80) NOT NULL DEFAULT '',
            `messenger_contact_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `manager_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `chat_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `message_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `reason_code` VARCHAR(32) NOT NULL DEFAULT '',
            `reason_note` VARCHAR(255) NOT NULL DEFAULT '',
            `snapshot_json` MEDIUMTEXT NOT NULL,
            `date_added` DATETIME NOT NULL,
            INDEX `idx_customer_event` (`customer_id`, `date_added`),
            INDEX `idx_profile_hash` (`provider`, `profile_url_hash`, `date_added`),
            INDEX `idx_chat_profile` (`chat_id`, `provider`, `profile_url_hash`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}customer_profile_suppressions` (
            `suppression_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `customer_id` INT UNSIGNED NOT NULL,
            `provider` VARCHAR(32) NOT NULL,
            `profile_url` VARCHAR(512) NOT NULL,
            `profile_url_hash` CHAR(64) NOT NULL,
            `reason_code` VARCHAR(32) NOT NULL DEFAULT '',
            `manager_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `source_message_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            UNIQUE KEY `uniq_customer_profile_suppression` (`customer_id`, `provider`, `profile_url_hash`),
            INDEX `idx_profile_hash` (`provider`, `profile_url_hash`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }

    private static function ensureCustomerNotesPinnedSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260624_customer_notes_pinned';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureCustomerNotesPinnedSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureCustomerNotesPinnedSchema($db, $prefix) {
        self::ensureCustomerPanelSchema($db, $prefix);

        self::ensureColumn($db, $prefix . 'customer_notes', 'is_pinned', "`is_pinned` TINYINT(1) NOT NULL DEFAULT 0 AFTER `text`");
        self::ensureColumn($db, $prefix . 'customer_notes', 'pinned_at', "`pinned_at` DATETIME NULL DEFAULT NULL AFTER `is_pinned`");
        self::ensureColumn($db, $prefix . 'customer_notes', 'pinned_by', "`pinned_by` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `pinned_at`");
        self::ensureIndex($db, $prefix . 'customer_notes', 'idx_customer_chat_pinned', "`customer_id`, `chat_id`, `is_pinned`, `pinned_at`, `date_added`");
    }

    private static function ensureCustomerMergeSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260623_customer_merge_crm';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureCustomerMergeSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureCustomerMergeSchema($db, $prefix) {
        self::ensureColumn($db, $prefix . 'customers', 'merged_into_customer_id', "`merged_into_customer_id` INT UNSIGNED NULL DEFAULT NULL AFTER `date_modified`");
        self::ensureColumn($db, $prefix . 'customers', 'merged_at', "`merged_at` DATETIME NULL DEFAULT NULL AFTER `merged_into_customer_id`");
        self::ensureColumn($db, $prefix . 'customers', 'merged_by', "`merged_by` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `merged_at`");
        self::ensureIndex($db, $prefix . 'customers', 'idx_customer_merged_into', "`merged_into_customer_id`, `customer_id`");

        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}customer_contact_points` (
            `contact_point_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `customer_id` INT UNSIGNED NOT NULL,
            `type` VARCHAR(16) NOT NULL,
            `value` VARCHAR(255) NOT NULL,
            `value_hash` CHAR(64) NOT NULL,
            `source` VARCHAR(32) NOT NULL DEFAULT '',
            `source_customer_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `is_primary` TINYINT(1) NOT NULL DEFAULT 0,
            `status` VARCHAR(24) NOT NULL DEFAULT 'auto_detected',
            `verification_source` VARCHAR(32) NOT NULL DEFAULT '',
            `verified_at` DATETIME DEFAULT NULL,
            `source_chat_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `source_message_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `added_by` INT UNSIGNED NOT NULL DEFAULT 0,
            `date_added` DATETIME NOT NULL,
            `date_modified` DATETIME NOT NULL,
            `last_seen_at` DATETIME DEFAULT NULL,
            UNIQUE KEY `uniq_customer_contact_point` (`customer_id`, `type`, `value_hash`),
            INDEX `idx_contact_value` (`type`, `value_hash`),
            INDEX `idx_contact_customer` (`customer_id`, `type`),
            INDEX `idx_contact_primary` (`customer_id`, `type`, `is_primary`, `status`),
            INDEX `idx_contact_source_chat` (`source_chat_id`, `source_message_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}customer_merge_audit` (
            `merge_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `target_customer_id` INT UNSIGNED NOT NULL,
            `source_customer_id` INT UNSIGNED NOT NULL,
            `manager_id` INT UNSIGNED NOT NULL DEFAULT 0,
            `reason` VARCHAR(32) NOT NULL DEFAULT '',
            `profile_url` VARCHAR(512) NOT NULL DEFAULT '',
            `snapshot_json` MEDIUMTEXT NOT NULL,
            `date_added` DATETIME NOT NULL,
            INDEX `idx_target_date` (`target_customer_id`, `date_added`),
            INDEX `idx_source_date` (`source_customer_id`, `date_added`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }

    private static function ensureCustomerContactPointCrmSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260624_customer_contact_points_crm';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureCustomerContactPointCrmSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureCustomerContactPointCrmSchema($db, $prefix) {
        self::ensureCustomerPanelSchema($db, $prefix);
        self::ensureCustomerMergeSchema($db, $prefix);

        self::ensureColumn($db, $prefix . 'customer_contact_points', 'is_primary', "`is_primary` TINYINT(1) NOT NULL DEFAULT 0 AFTER `source_customer_id`");
        self::ensureColumn($db, $prefix . 'customer_contact_points', 'status', "`status` VARCHAR(24) NOT NULL DEFAULT 'auto_detected' AFTER `is_primary`");
        self::ensureColumn($db, $prefix . 'customer_contact_points', 'source_chat_id', "`source_chat_id` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `status`");
        self::ensureColumn($db, $prefix . 'customer_contact_points', 'source_message_id', "`source_message_id` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `source_chat_id`");
        self::ensureColumn($db, $prefix . 'customer_contact_points', 'added_by', "`added_by` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `source_message_id`");
        self::ensureColumn($db, $prefix . 'customer_contact_points', 'last_seen_at', "`last_seen_at` DATETIME DEFAULT NULL AFTER `date_modified`");

        if (self::columnExists($db, $prefix . 'customer_contact_points', 'confirmed')) {
            $db->query(
                "UPDATE `{$prefix}customer_contact_points`
                 SET `status` = CASE WHEN `confirmed` = 1 THEN 'operator_confirmed' ELSE `status` END,
                     `last_seen_at` = IFNULL(`last_seen_at`, `date_modified`)"
            );
        }

        self::backfillCustomerContactsFromLegacyColumns($db, $prefix);
        self::ensurePrimaryContacts($db, $prefix);

        self::ensureIndex($db, $prefix . 'customer_contact_points', 'idx_contact_primary', "`customer_id`, `type`, `is_primary`, `status`");
        self::ensureIndex($db, $prefix . 'customer_contact_points', 'idx_contact_source_chat', "`source_chat_id`, `source_message_id`");

        self::dropColumnIfExists($db, $prefix . 'customer_contact_points', 'confirmed');
        self::dropColumnIfExists($db, $prefix . 'customers', 'email');
        self::dropColumnIfExists($db, $prefix . 'customers', 'phone');
        self::dropColumnIfExists($db, $prefix . 'sessions', 'email');
        self::dropColumnIfExists($db, $prefix . 'sessions', 'phone');
        self::dropColumnIfExists($db, $prefix . 'sessions', 'contacts_confirmed');
    }

    private static function ensureBlockedSchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260609_blocked_chats';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensureBlockedSchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensureBlockedSchema($db, $prefix) {
        self::ensureColumn($db, $prefix . 'sessions', 'is_blocked', "`is_blocked` TINYINT(1) NOT NULL DEFAULT 0");
        self::ensureIndex($db, $prefix . 'sessions', 'idx_is_blocked', "`is_blocked`");
    }

    private static function ensurePasskeySchemaOnce($db, $prefix) {
        $migration_key = 'schema_migration_20260626_passkeys';
        $query = $db->query("SELECT `value` FROM `{$prefix}settings` WHERE `key` = '" . $db->escape($migration_key) . "' LIMIT 1");
        if ($query && $query->num_rows) {
            return;
        }

        self::ensurePasskeySchema($db, $prefix);

        $db->query("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES ('" . $db->escape($migration_key) . "', '1') ON DUPLICATE KEY UPDATE `value` = '1'");
    }

    private static function ensurePasskeySchema($db, $prefix) {
        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}manager_passkey_credentials` (
            `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `manager_id` INT(11) NOT NULL,
            `credential_id_hash` CHAR(64) NOT NULL,
            `credential_id` TEXT NOT NULL,
            `public_key_cose` TEXT NOT NULL,
            `public_key_alg` INT NOT NULL DEFAULT 0,
            `sign_count` BIGINT UNSIGNED NOT NULL DEFAULT 0,
            `transports` VARCHAR(255) NOT NULL DEFAULT '',
            `device_label` VARCHAR(128) NOT NULL DEFAULT '',
            `user_agent_hash` CHAR(64) NOT NULL DEFAULT '',
            `created_at` DATETIME NOT NULL,
            `last_used_at` DATETIME DEFAULT NULL,
            `revoked_at` DATETIME DEFAULT NULL,
            UNIQUE KEY `uniq_credential_hash` (`credential_id_hash`),
            INDEX `idx_manager_active` (`manager_id`, `revoked_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

        $db->query("CREATE TABLE IF NOT EXISTS `{$prefix}passkey_challenges` (
            `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `challenge_hash` CHAR(64) NOT NULL,
            `purpose` VARCHAR(32) NOT NULL,
            `manager_id` INT(11) NOT NULL DEFAULT 0,
            `session_id_hash` CHAR(64) NOT NULL DEFAULT '',
            `ip_hash` CHAR(64) NOT NULL DEFAULT '',
            `user_agent_hash` CHAR(64) NOT NULL DEFAULT '',
            `expires_at` DATETIME NOT NULL,
            `used_at` DATETIME DEFAULT NULL,
            `created_at` DATETIME NOT NULL,
            UNIQUE KEY `uniq_challenge_hash` (`challenge_hash`),
            INDEX `idx_purpose_expires` (`purpose`, `expires_at`),
            INDEX `idx_manager_purpose` (`manager_id`, `purpose`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }

    private static function ensureColumn($db, $table, $column, $definition) {
        $query = $db->query("SHOW COLUMNS FROM `" . str_replace('`', '``', $table) . "` LIKE '" . $db->escape($column) . "'");
        if ($query && $query->num_rows) {
            return;
        }

        $db->query("ALTER TABLE `" . str_replace('`', '``', $table) . "` ADD COLUMN {$definition}");
    }

    private static function columnExists($db, $table, $column) {
        $query = $db->query("SHOW COLUMNS FROM `" . str_replace('`', '``', $table) . "` LIKE '" . $db->escape($column) . "'");
        return $query && $query->num_rows > 0;
    }

    private static function ensureIndex($db, $table, $index, $columns) {
        $safe_table = str_replace('`', '``', $table);
        $query = $db->query("SHOW INDEX FROM `{$safe_table}` WHERE `Key_name` = '" . $db->escape($index) . "'");
        if ($query && $query->num_rows) {
            return;
        }

        $db->query("ALTER TABLE `{$safe_table}` ADD INDEX `" . str_replace('`', '``', $index) . "` ({$columns})");
    }

    private static function ensureUniqueIndex($db, $table, $index, $columns) {
        $safe_table = str_replace('`', '``', $table);
        $query = $db->query("SHOW INDEX FROM `{$safe_table}` WHERE `Key_name` = '" . $db->escape($index) . "'");
        if ($query && $query->num_rows) {
            return;
        }

        $db->query("ALTER TABLE `{$safe_table}` ADD UNIQUE INDEX `" . str_replace('`', '``', $index) . "` ({$columns})");
    }

    private static function dropIndexIfExists($db, $table, $index) {
        $safe_table = str_replace('`', '``', $table);
        $safe_index = str_replace('`', '``', $index);
        $query = $db->query("SHOW INDEX FROM `{$safe_table}` WHERE `Key_name` = '" . $db->escape($index) . "'");
        if (!$query || !$query->num_rows) {
            return;
        }

        $db->query("ALTER TABLE `{$safe_table}` DROP INDEX `{$safe_index}`");
    }

    private static function dropColumnIfExists($db, $table, $column) {
        if (!self::columnExists($db, $table, $column)) {
            return;
        }

        $safe_table = str_replace('`', '``', $table);
        $safe_column = str_replace('`', '``', $column);
        $db->query("ALTER TABLE `{$safe_table}` DROP COLUMN `{$safe_column}`");
    }

    private static function backfillCustomerContactsFromLegacyColumns($db, $prefix) {
        $customer_has_email = self::columnExists($db, $prefix . 'customers', 'email');
        $customer_has_phone = self::columnExists($db, $prefix . 'customers', 'phone');
        if ($customer_has_email || $customer_has_phone) {
            $fields = ['`customer_id`', '`date_added`', '`date_modified`'];
            $where = [];
            if ($customer_has_email) {
                $fields[] = '`email`';
                $where[] = "`email` <> ''";
            }
            if ($customer_has_phone) {
                $fields[] = '`phone`';
                $where[] = "`phone` <> ''";
            }

            $query = $db->query(
                "SELECT " . implode(', ', $fields) . "
                 FROM `{$prefix}customers`
                 WHERE " . implode(' OR ', $where)
            );
            foreach ($query->rows as $row) {
                $customer_id = (int)$row['customer_id'];
                $date = self::legacyContactDate($row);
                if ($customer_has_email) {
                    self::backfillContactPoint($db, $prefix, $customer_id, 'email', (string)($row['email'] ?? ''), 'legacy_customer', 0, 0, 0, 'operator_confirmed', $date);
                }
                if ($customer_has_phone) {
                    self::backfillContactPoint($db, $prefix, $customer_id, 'phone', (string)($row['phone'] ?? ''), 'legacy_customer', 0, 0, 0, 'operator_confirmed', $date);
                }
            }
        }

        $session_has_email = self::columnExists($db, $prefix . 'sessions', 'email');
        $session_has_phone = self::columnExists($db, $prefix . 'sessions', 'phone');
        if (!$session_has_email && !$session_has_phone) {
            return;
        }

        $session_has_confirmed = self::columnExists($db, $prefix . 'sessions', 'contacts_confirmed');
        $fields = ['`chat_id`', '`customer_id`', '`customer_name`', '`date_added`', '`date_modified`'];
        $where = [];
        if ($session_has_email) {
            $fields[] = '`email`';
            $where[] = "`email` <> ''";
        }
        if ($session_has_phone) {
            $fields[] = '`phone`';
            $where[] = "`phone` <> ''";
        }
        if ($session_has_confirmed) {
            $fields[] = '`contacts_confirmed`';
        }

        $query = $db->query(
            "SELECT " . implode(', ', $fields) . "
             FROM `{$prefix}sessions`
             WHERE " . implode(' OR ', $where)
        );
        foreach ($query->rows as $row) {
            $chat_id = (int)$row['chat_id'];
            $customer_id = (int)($row['customer_id'] ?? 0);
            if ($customer_id <= 0 || !self::customerExists($db, $prefix, $customer_id)) {
                $customer_id = self::createCustomerForLegacySession($db, $prefix, $row);
                if ($customer_id > 0) {
                    $db->query(
                        "UPDATE `{$prefix}sessions`
                         SET `customer_id` = " . (int)$customer_id . "
                         WHERE `chat_id` = " . (int)$chat_id
                    );
                }
            }
            if ($customer_id <= 0) {
                continue;
            }

            $status = ($session_has_confirmed && (int)($row['contacts_confirmed'] ?? 0) === 1)
                ? 'operator_confirmed'
                : 'auto_detected';
            $date = self::legacyContactDate($row);
            if ($session_has_email) {
                self::backfillContactPoint($db, $prefix, $customer_id, 'email', (string)($row['email'] ?? ''), 'legacy_session', $chat_id, 0, 0, $status, $date);
            }
            if ($session_has_phone) {
                self::backfillContactPoint($db, $prefix, $customer_id, 'phone', (string)($row['phone'] ?? ''), 'legacy_session', $chat_id, 0, 0, $status, $date);
            }
        }
    }

    private static function createCustomerForLegacySession($db, $prefix, array $row) {
        $date = self::legacyContactDate($row);
        $name = self::cleanLegacyName((string)($row['customer_name'] ?? ''));
        $db->query(
            "INSERT INTO `{$prefix}customers`
             SET `display_name` = '" . $db->escape($name) . "',
                 `date_added` = '" . $db->escape($date) . "',
                 `date_modified` = '" . $db->escape($date) . "'"
        );
        return (int)$db->getLastId();
    }

    private static function customerExists($db, $prefix, $customer_id) {
        $query = $db->query(
            "SELECT `customer_id`
             FROM `{$prefix}customers`
             WHERE `customer_id` = " . (int)$customer_id . "
             LIMIT 1"
        );
        return $query && $query->num_rows > 0;
    }

    private static function backfillContactPoint($db, $prefix, $customer_id, $type, $value, $source, $source_chat_id, $source_message_id, $added_by, $status, $date) {
        $type = self::normalizeLegacyContactType($type);
        $value = self::normalizeLegacyContactValue($type, $value);
        if ((int)$customer_id <= 0 || $type === '' || $value === '') {
            return;
        }

        $status = self::normalizeLegacyContactStatus($status);
        $hash = hash('sha256', $type . '|' . mb_strtolower($value, 'UTF-8'));
        $primary = !self::hasPrimaryContact($db, $prefix, (int)$customer_id, $type);
        if ($primary) {
            $db->query(
                "UPDATE `{$prefix}customer_contact_points`
                 SET `is_primary` = 0
                 WHERE `customer_id` = " . (int)$customer_id . "
                   AND `type` = '" . $db->escape($type) . "'
                   AND `status` <> 'archived'"
            );
        }

        $db->query(
            "INSERT INTO `{$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`)
             VALUES
                (" . (int)$customer_id . ",
                 '" . $db->escape($type) . "',
                 '" . $db->escape($value) . "',
                 '" . $db->escape($hash) . "',
                 '" . $db->escape(self::cleanLegacySource($source)) . "',
                 0,
                 " . ($primary ? 1 : 0) . ",
                 '" . $db->escape($status) . "',
                 " . max(0, (int)$source_chat_id) . ",
                 " . max(0, (int)$source_message_id) . ",
                 " . max(0, (int)$added_by) . ",
                 '" . $db->escape($date) . "',
                 '" . $db->escape($date) . "',
                 '" . $db->escape($date) . "')
             ON DUPLICATE KEY UPDATE
                `value` = VALUES(`value`),
                `is_primary` = GREATEST(`{$prefix}customer_contact_points`.`is_primary`, VALUES(`is_primary`)),
                `status` = " . self::legacyStatusMergeSql("`{$prefix}customer_contact_points`.`status`", "VALUES(`status`)") . ",
                `source_chat_id` = IF(VALUES(`source_chat_id`) > 0, VALUES(`source_chat_id`), `{$prefix}customer_contact_points`.`source_chat_id`),
                `source_message_id` = IF(VALUES(`source_message_id`) > 0, VALUES(`source_message_id`), `{$prefix}customer_contact_points`.`source_message_id`),
                `date_modified` = VALUES(`date_modified`),
                `last_seen_at` = VALUES(`last_seen_at`)"
        );
    }

    private static function ensurePrimaryContacts($db, $prefix) {
        $query = $db->query(
            "SELECT DISTINCT `customer_id`, `type`
             FROM `{$prefix}customer_contact_points`
             WHERE `status` <> 'archived'"
        );
        foreach ($query->rows as $row) {
            $customer_id = (int)$row['customer_id'];
            $type = self::normalizeLegacyContactType((string)$row['type']);
            if ($customer_id <= 0 || $type === '' || self::hasPrimaryContact($db, $prefix, $customer_id, $type)) {
                continue;
            }
            $candidate = $db->query(
                "SELECT `contact_point_id`
                 FROM `{$prefix}customer_contact_points`
                 WHERE `customer_id` = " . (int)$customer_id . "
                   AND `type` = '" . $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) {
                $db->query(
                    "UPDATE `{$prefix}customer_contact_points`
                     SET `is_primary` = 1
                     WHERE `contact_point_id` = " . (int)$candidate->row['contact_point_id']
                );
            }
        }
    }

    private static function hasPrimaryContact($db, $prefix, $customer_id, $type) {
        $query = $db->query(
            "SELECT `contact_point_id`
             FROM `{$prefix}customer_contact_points`
             WHERE `customer_id` = " . (int)$customer_id . "
               AND `type` = '" . $db->escape($type) . "'
               AND `is_primary` = 1
               AND `status` <> 'archived'
             LIMIT 1"
        );
        return $query && $query->num_rows > 0;
    }

    private static function normalizeLegacyContactType($type) {
        $type = strtolower(trim((string)$type));
        return in_array($type, ['email', 'phone'], true) ? $type : '';
    }

    private static function normalizeLegacyContactValue($type, $value) {
        if ($type === 'email') {
            $value = mb_strtolower(trim((string)$value), 'UTF-8');
            return filter_var($value, FILTER_VALIDATE_EMAIL) ? mb_substr($value, 0, 255, 'UTF-8') : '';
        }
        if ($type === 'phone') {
            $value = preg_replace('/[^\d+]/u', '', trim((string)$value));
            if ($value === '') {
                return '';
            }
            if (strpos($value, '+') > 0) {
                $value = str_replace('+', '', $value);
            }
            return mb_substr((string)$value, 0, 32, 'UTF-8');
        }
        return '';
    }

    private static function normalizeLegacyContactStatus($status) {
        return in_array($status, ['auto_detected', 'operator_confirmed', 'archived'], true) ? $status : 'auto_detected';
    }

    private static function cleanLegacySource($source) {
        $source = strtolower(preg_replace('/[^a-z0-9_\-]/i', '', (string)$source));
        return mb_substr($source, 0, 32, 'UTF-8');
    }

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

    private static function legacyContactDate(array $row) {
        $date = trim((string)($row['date_modified'] ?? ''));
        if ($date === '' || $date === '0000-00-00 00:00:00') {
            $date = trim((string)($row['date_added'] ?? ''));
        }
        return ($date !== '' && $date !== '0000-00-00 00:00:00') ? $date : date('Y-m-d H:i:s');
    }

    private static function legacyStatusMergeSql($current, $incoming) {
        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 static function messengerSchemaQueries($prefix) {
        return array(
            "CREATE TABLE IF NOT EXISTS `{$prefix}messenger_channels` (
                `channel_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `provider` VARCHAR(32) NOT NULL,
                `external_account_id` VARCHAR(128) NOT NULL DEFAULT '',
                `auth_type` VARCHAR(32) NOT NULL DEFAULT 'token',
                `title` VARCHAR(128) NOT NULL DEFAULT '',
                `status` ENUM('disabled','active','error') NOT NULL DEFAULT 'disabled',
                `public_id` VARCHAR(64) NOT NULL,
                `credentials_json` MEDIUMTEXT NOT NULL,
                `settings_json` TEXT NOT NULL,
                `capabilities_json` TEXT NULL,
                `bot_user_id` VARCHAR(128) NOT NULL DEFAULT '',
                `bot_name` VARCHAR(255) NOT NULL DEFAULT '',
                `bot_username` VARCHAR(255) NOT NULL DEFAULT '',
                `webhook_url` TEXT NULL,
                `webhook_synced_at` DATETIME DEFAULT NULL,
                `last_inbound_at` DATETIME DEFAULT NULL,
                `last_outbound_at` DATETIME DEFAULT NULL,
                `last_error` VARCHAR(512) NOT NULL DEFAULT '',
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                UNIQUE KEY `uniq_provider_public` (`provider`, `public_id`),
                KEY `idx_provider_status` (`provider`, `status`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}messenger_contacts` (
                `contact_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `channel_id` INT UNSIGNED NOT NULL,
                `provider` VARCHAR(32) NOT NULL,
                `external_user_id` VARCHAR(128) NOT NULL,
                `external_chat_id` VARCHAR(128) NOT NULL DEFAULT '',
                `display_name` VARCHAR(255) NOT NULL DEFAULT '',
                `username` VARCHAR(255) NOT NULL DEFAULT '',
                `profile_url` TEXT NULL,
                `avatar_file` VARCHAR(128) NOT NULL DEFAULT '',
                `avatar_mime` VARCHAR(64) NOT NULL DEFAULT '',
                `avatar_source_hash` CHAR(64) NOT NULL DEFAULT '',
                `avatar_identity_fingerprint` CHAR(64) NOT NULL DEFAULT '',
                `avatar_updated_at` DATETIME DEFAULT NULL,
                `avatar_checked_at` DATETIME DEFAULT NULL,
                `avatar_last_error` VARCHAR(128) NOT NULL DEFAULT '',
                `identity_repair_checked_at` DATETIME DEFAULT NULL,
                `chat_id` INT(11) NULL DEFAULT NULL,
                `metadata_json` TEXT NOT NULL,
                `customer_identity_hash` CHAR(64) NOT NULL DEFAULT '',
                `last_seen_at` DATETIME DEFAULT NULL,
                `date_added` DATETIME NOT NULL,
                UNIQUE KEY `uniq_channel_user` (`channel_id`, `external_user_id`),
                KEY `idx_chat` (`chat_id`),
                KEY `idx_provider` (`provider`),
                KEY `idx_customer_identity` (`provider`, `channel_id`, `customer_identity_hash`, `chat_id`),
                KEY `idx_avatar_file` (`avatar_file`),
                KEY `idx_identity_repair` (`channel_id`, `provider`, `identity_repair_checked_at`, `contact_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            self::messengerProviderIncidentSchemaQuery($prefix),

            "CREATE TABLE IF NOT EXISTS `{$prefix}messenger_deliveries` (
                `delivery_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `channel_id` INT UNSIGNED NOT NULL,
                `contact_id` INT UNSIGNED NULL DEFAULT NULL,
                `message_id` INT(11) NULL DEFAULT NULL,
                `direction` ENUM('in','out') NOT NULL DEFAULT 'in',
                `external_message_id` VARCHAR(128) NOT NULL DEFAULT '',
                `dedupe_key` VARCHAR(128) NOT NULL,
                `payload_hash` CHAR(64) NOT NULL DEFAULT '',
                `provider_occurred_at` DATETIME DEFAULT NULL,
                `provider_cursor` BIGINT UNSIGNED DEFAULT NULL,
                `ingest_mode` VARCHAR(24) NOT NULL DEFAULT 'live',
                `status` ENUM('pending','received','sent','failed','duplicate','ignored') NOT NULL DEFAULT 'pending',
                `attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0,
                `last_error` VARCHAR(512) NOT NULL DEFAULT '',
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                UNIQUE KEY `uniq_channel_dedupe` (`channel_id`, `dedupe_key`),
                KEY `idx_message` (`message_id`),
                KEY `idx_channel_external_message` (`channel_id`, `external_message_id`),
                KEY `idx_provider_read_cursor` (`channel_id`, `direction`, `provider_cursor`, `message_id`),
                KEY `idx_status` (`status`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            self::externalChatDeletionSchemaQuery($prefix),

            self::conversationStartSchemaQuery($prefix),

            "CREATE TABLE IF NOT EXISTS `{$prefix}message_attachments` (
                `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `message_id` INT(11) NOT NULL,
                `chat_id` INT(11) NOT NULL,
                `file_name` VARCHAR(255) NOT NULL,
                `file_name_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
                `mime_type` VARCHAR(128) NOT NULL DEFAULT '',
                `file_size` INT UNSIGNED NOT NULL DEFAULT 0,
                `provider` VARCHAR(32) NOT NULL DEFAULT '',
                `external_attachment_id` VARCHAR(128) NOT NULL DEFAULT '',
                `delivery_status` VARCHAR(24) NOT NULL DEFAULT '',
                `delivery_error` VARCHAR(255) NOT NULL DEFAULT '',
                `meta_json` TEXT NOT NULL,
                `date_added` DATETIME NOT NULL,
                UNIQUE KEY `uniq_message_file` (`message_id`, `file_name_hash`),
                KEY `idx_message` (`message_id`),
                KEY `idx_chat` (`chat_id`),
                KEY `idx_provider` (`provider`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;"
        );
    }

    public static function ensureRuntimeProtection($app_path = '') {
        $app_path = rtrim((string)$app_path, '/\\');
         
         
         
        $uploads = $app_path !== ''
            ? $app_path . DIRECTORY_SEPARATOR . 'uploads'
            : (defined('DIR_IMAGE') ? rtrim(DIR_IMAGE, '/\\') : '');
        $logs = $app_path !== ''
            ? $app_path . DIRECTORY_SEPARATOR . 'log'
            : (defined('DIR_LOGS') ? rtrim(DIR_LOGS, '/\\') : '');

        $ready = true;
        if ($uploads !== '') {
            $ready = self::ensureProtectedRuntimeDir($uploads, self::runtimeUploadsHtaccess(), 0770) && $ready;
        }
        if ($logs !== '') {
            $ready = self::ensureProtectedRuntimeDir($logs, self::runtimeDenyAllHtaccess(), 0770) && $ready;
            $ready = self::ensureProtectedRuntimeDir($logs . DIRECTORY_SEPARATOR . 'sessions', self::runtimeDenyAllHtaccess(), 0700) && $ready;
            $ready = self::ensureProtectedRuntimeDir($logs . DIRECTORY_SEPARATOR . 'session-revocations', self::runtimeDenyAllHtaccess(), 0700) && $ready;
        }

        return $ready;
    }

    public static function schemaQueries($prefix) {
        return array_merge(array(
            "CREATE TABLE IF NOT EXISTS `{$prefix}settings` (
                `key` VARCHAR(" . self::SETTINGS_KEY_MAX_LENGTH . ") NOT NULL PRIMARY KEY,
                `value` TEXT NOT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            ...self::crmSchemaQueries($prefix),

            "CREATE TABLE IF NOT EXISTS `{$prefix}managers` (
                `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `username` VARCHAR(64) NOT NULL UNIQUE,
                `password` VARCHAR(255) NOT NULL,
                `salt` VARCHAR(64) NOT NULL,
                `name` VARCHAR(128) NOT NULL,
                `image` VARCHAR(255) DEFAULT NULL,
                `role` VARCHAR(16) NOT NULL DEFAULT 'manager',
                `permission_delete_chats` INT(1) NOT NULL DEFAULT 0,
                `permission_manage_chat_assignments` TINYINT(1) NOT NULL DEFAULT 0,
                `ai_suggestions_mode` VARCHAR(16) NOT NULL DEFAULT 'inherit',
                `show_in_widget_header` INT(1) NOT NULL DEFAULT 1,
                `status` INT(1) NOT NULL DEFAULT 1,
                `ip` VARCHAR(45) DEFAULT NULL,
                `last_active` DATETIME DEFAULT NULL,
                `date_added` DATETIME NOT NULL,
                INDEX `idx_widget_header` (`status`, `show_in_widget_header`, `last_active`, `id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}manager_passkey_credentials` (
                `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `manager_id` INT(11) NOT NULL,
                `credential_id_hash` CHAR(64) NOT NULL,
                `credential_id` TEXT NOT NULL,
                `public_key_cose` TEXT NOT NULL,
                `public_key_alg` INT NOT NULL DEFAULT 0,
                `sign_count` BIGINT UNSIGNED NOT NULL DEFAULT 0,
                `transports` VARCHAR(255) NOT NULL DEFAULT '',
                `device_label` VARCHAR(128) NOT NULL DEFAULT '',
                `user_agent_hash` CHAR(64) NOT NULL DEFAULT '',
                `created_at` DATETIME NOT NULL,
                `last_used_at` DATETIME DEFAULT NULL,
                `revoked_at` DATETIME DEFAULT NULL,
                UNIQUE KEY `uniq_credential_hash` (`credential_id_hash`),
                INDEX `idx_manager_active` (`manager_id`, `revoked_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}passkey_challenges` (
                `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `challenge_hash` CHAR(64) NOT NULL,
                `purpose` VARCHAR(32) NOT NULL,
                `manager_id` INT(11) NOT NULL DEFAULT 0,
                `session_id_hash` CHAR(64) NOT NULL DEFAULT '',
                `ip_hash` CHAR(64) NOT NULL DEFAULT '',
                `user_agent_hash` CHAR(64) NOT NULL DEFAULT '',
                `expires_at` DATETIME NOT NULL,
                `used_at` DATETIME DEFAULT NULL,
                `created_at` DATETIME NOT NULL,
                UNIQUE KEY `uniq_challenge_hash` (`challenge_hash`),
                INDEX `idx_purpose_expires` (`purpose`, `expires_at`),
                INDEX `idx_manager_purpose` (`manager_id`, `purpose`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}sessions` (
                `chat_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `customer_id` INT UNSIGNED NULL DEFAULT NULL,
                `cms_identity_id` INT UNSIGNED NULL DEFAULT NULL,
                `cms_identity_bound_at` DATETIME NULL DEFAULT NULL,
                `session_id` VARCHAR(32) NOT NULL,
                `customer_name` VARCHAR(255) NOT NULL DEFAULT '',
                `dialog_telegram_id` BIGINT(20) DEFAULT NULL,
                `source_provider` VARCHAR(32) NOT NULL DEFAULT 'widget',
                `source_channel_id` INT UNSIGNED NULL DEFAULT NULL,
                `conversation_kind` VARCHAR(32) NOT NULL DEFAULT 'chat',
                `external_thread_id` VARCHAR(191) NOT NULL DEFAULT '',
                `external_contact_id` INT UNSIGNED NULL DEFAULT NULL,
                `read_receipt_capability` VARCHAR(24) NOT NULL DEFAULT 'unsupported',
                `conversation_start_message_id` INT(11) NOT NULL DEFAULT 0,
                `conversation_started_at` DATETIME DEFAULT NULL,
                `is_archived` INT(1) NOT NULL DEFAULT 0,
                `operator_typing_expire` INT(11) NOT NULL DEFAULT 0,
                `ai_typing_expire` INT(11) NOT NULL DEFAULT 0,
                `ai_processing_message_id` INT(11) NOT NULL DEFAULT 0,
                `ai_processing_until` INT(11) NOT NULL DEFAULT 0,
                `ai_last_attempt_message_id` INT(11) NOT NULL DEFAULT 0,
                `ai_last_attempt_status` VARCHAR(16) NOT NULL DEFAULT '',
                `ai_last_error` VARCHAR(255) NOT NULL DEFAULT '',
                `ai_last_attempt_at` DATETIME DEFAULT NULL,
                `operator_typing_name` VARCHAR(255) NULL DEFAULT NULL,
                `operator_typing_image` VARCHAR(255) NULL DEFAULT NULL,
                `is_blocked` TINYINT(1) NOT NULL DEFAULT 0,
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                INDEX (`session_id`),
                INDEX `idx_customer_id` (`customer_id`, `date_modified`),
                INDEX `idx_cms_identity` (`cms_identity_id`, `chat_id`),
                INDEX (`dialog_telegram_id`),
                INDEX `idx_is_blocked` (`is_blocked`),
                INDEX `idx_source_provider` (`source_provider`, `source_channel_id`),
                INDEX `idx_archive_source_modified` (`is_archived`, `source_provider`, `date_modified`),
                INDEX `idx_marketplace_conversation` (`source_provider`, `conversation_kind`, `date_modified`),
                INDEX `idx_marketplace_thread` (`source_channel_id`, `external_thread_id`(180)),
                INDEX `idx_android_chat_sync` (`is_archived`, `date_modified`, `chat_id`),
                INDEX `idx_ai_processing` (`ai_processing_until`, `ai_processing_message_id`),
                INDEX `idx_ai_last_attempt` (`ai_last_attempt_message_id`, `ai_last_attempt_status`),
                INDEX `idx_conversation_start` (`conversation_start_message_id`, `chat_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}messages` (
                `message_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `chat_id` INT(11) NOT NULL,
                `author` INT(1) NOT NULL,
                `manager_id` INT(11) DEFAULT NULL,
                `manager_name` VARCHAR(255) NOT NULL DEFAULT '',
                `text` TEXT NOT NULL,
                `image` VARCHAR(2048) NOT NULL DEFAULT '',
                `status_user` INT(1) NOT NULL DEFAULT 0,
                `customer_read_at` DATETIME DEFAULT NULL,
                `customer_read_source` VARCHAR(24) NOT NULL DEFAULT '',
                `status_admin` INT(1) NOT NULL DEFAULT 0,
                `ip` VARCHAR(40) NOT NULL DEFAULT '',
                `message_telegram_id` BIGINT(20) DEFAULT NULL,
                `source_provider` VARCHAR(32) NOT NULL DEFAULT 'widget',
                `source_channel_id` INT UNSIGNED NULL DEFAULT NULL,
                `external_message_id` VARCHAR(128) NOT NULL DEFAULT '',
                `message_kind` VARCHAR(32) NOT NULL DEFAULT 'normal',
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                `first_response_message_id` INT(11) NOT NULL DEFAULT 0,
                `first_response_seconds` INT UNSIGNED NOT NULL DEFAULT 0,
                `first_response_author` TINYINT UNSIGNED NOT NULL DEFAULT 0,
                `first_response_manager_id` INT(11) NOT NULL DEFAULT 0,
                `first_response_recorded_at` DATETIME DEFAULT NULL,
                `response_metric_checked_at` DATETIME DEFAULT NULL,
                `response_metric_is_first_customer` TINYINT(1) NOT NULL DEFAULT 0,
                `response_metric_human_frt_seconds` INT UNSIGNED NOT NULL DEFAULT 0,
                `response_metric_human_frt_recorded_at` DATETIME DEFAULT NULL,
                INDEX (`chat_id`),
                INDEX (`message_telegram_id`),
                INDEX `idx_source_provider` (`source_provider`, `source_channel_id`),
                INDEX `idx_external_message_lookup` (`source_provider`, `source_channel_id`, `external_message_id`),
                INDEX `idx_widget_unread_user` (`chat_id`, `status_user`, `author`),
                INDEX `idx_customer_read_projection` (`chat_id`, `author`, `status_user`, `message_id`),
                INDEX `idx_admin_unread` (`status_admin`, `author`),
                INDEX `idx_admin_unread_chat` (`chat_id`, `status_admin`, `author`),
                INDEX `idx_admin_unread_global` (`status_admin`, `author`, `chat_id`),
                INDEX `idx_chat_message_id` (`chat_id`, `message_id`),
                INDEX `idx_chat_kind_message` (`chat_id`, `message_kind`, `message_id`),
                INDEX `idx_chat_last_message_lookup` (`chat_id`, `date_added`, `message_id`),
                INDEX `idx_response_time_lookup` (`chat_id`, `author`, `date_added`),
                INDEX `idx_android_message_sync` (`chat_id`, `message_id`, `date_modified`),
                INDEX `idx_response_metric_pending` (`chat_id`, `author`, `first_response_message_id`, `response_metric_checked_at`, `date_added`, `message_id`),
                FOREIGN KEY (`chat_id`) REFERENCES `{$prefix}sessions` (`chat_id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            self::authRateLimitSchemaQuery($prefix),

            ...\EasyChat\Model\AdminHelpSession::schemaQueries($prefix),

            self::messageReadCheckpointSchemaQuery($prefix),

            self::chatAssignmentsSchemaQuery($prefix),

            self::directMessageIdempotencySchemaQuery($prefix),

            "CREATE TABLE IF NOT EXISTS `{$prefix}response_time_summary` (
                `metric_key` VARCHAR(64) NOT NULL,
                `entity_id` INT(11) NOT NULL DEFAULT 0,
                `response_count` INT UNSIGNED NOT NULL DEFAULT 0,
                `response_seconds_sum` BIGINT UNSIGNED NOT NULL DEFAULT 0,
                `updated_at` DATETIME NOT NULL,
                PRIMARY KEY (`metric_key`, `entity_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}chat_sync_deletions` (
                `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `chat_id` INT(11) NOT NULL,
                `source_provider` VARCHAR(32) NOT NULL DEFAULT '',
                `deleted_at` DATETIME NOT NULL,
                UNIQUE KEY `uniq_chat_id` (`chat_id`),
                INDEX `idx_deleted_at` (`deleted_at`, `chat_id`),
                INDEX `idx_source_deleted` (`source_provider`, `deleted_at`, `chat_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}customers` (
                `customer_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `display_name` VARCHAR(255) NOT NULL DEFAULT '',
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                INDEX `idx_customer_modified` (`date_modified`, `customer_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}customer_profiles` (
                `profile_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `customer_id` INT UNSIGNED NOT NULL,
                `provider` VARCHAR(32) NOT NULL,
                `profile_url` VARCHAR(512) NOT NULL,
                `profile_url_hash` CHAR(64) NOT NULL,
                `username` VARCHAR(80) NOT NULL DEFAULT '',
                `external_user_id` VARCHAR(128) NOT NULL DEFAULT '',
                `messenger_contact_id` INT UNSIGNED NULL DEFAULT NULL,
                `source` VARCHAR(24) NOT NULL DEFAULT 'operator',
                `confidence` TINYINT UNSIGNED NOT NULL DEFAULT 100,
                `added_by` INT UNSIGNED NOT NULL DEFAULT 0,
                `message_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                UNIQUE KEY `uniq_provider_profile_hash` (`provider`, `profile_url_hash`),
                INDEX `idx_customer_provider` (`customer_id`, `provider`),
                INDEX `idx_messenger_contact` (`messenger_contact_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}customer_profile_events` (
                `event_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `event_type` VARCHAR(24) NOT NULL,
                `customer_id` INT UNSIGNED NOT NULL,
                `profile_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `provider` VARCHAR(32) NOT NULL,
                `profile_url` VARCHAR(512) NOT NULL,
                `profile_url_hash` CHAR(64) NOT NULL,
                `username` VARCHAR(80) NOT NULL DEFAULT '',
                `messenger_contact_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `manager_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `chat_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `message_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `reason_code` VARCHAR(32) NOT NULL DEFAULT '',
                `reason_note` VARCHAR(255) NOT NULL DEFAULT '',
                `snapshot_json` MEDIUMTEXT NOT NULL,
                `date_added` DATETIME NOT NULL,
                INDEX `idx_customer_event` (`customer_id`, `date_added`),
                INDEX `idx_profile_hash` (`provider`, `profile_url_hash`, `date_added`),
                INDEX `idx_chat_profile` (`chat_id`, `provider`, `profile_url_hash`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}customer_profile_suppressions` (
                `suppression_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `customer_id` INT UNSIGNED NOT NULL,
                `provider` VARCHAR(32) NOT NULL,
                `profile_url` VARCHAR(512) NOT NULL,
                `profile_url_hash` CHAR(64) NOT NULL,
                `reason_code` VARCHAR(32) NOT NULL DEFAULT '',
                `manager_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `source_message_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                UNIQUE KEY `uniq_customer_profile_suppression` (`customer_id`, `provider`, `profile_url_hash`),
                INDEX `idx_profile_hash` (`provider`, `profile_url_hash`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}customer_notes` (
                `note_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `customer_id` INT UNSIGNED NOT NULL,
                `chat_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `manager_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `note_type` VARCHAR(16) NOT NULL DEFAULT 'chat',
                `text` TEXT NOT NULL,
                `is_pinned` TINYINT(1) NOT NULL DEFAULT 0,
                `pinned_at` DATETIME NULL DEFAULT NULL,
                `pinned_by` INT UNSIGNED NOT NULL DEFAULT 0,
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                INDEX `idx_customer_chat_notes` (`customer_id`, `chat_id`, `date_added`),
                INDEX `idx_customer_chat_pinned` (`customer_id`, `chat_id`, `is_pinned`, `pinned_at`, `date_added`),
                INDEX `idx_manager_notes` (`manager_id`, `date_added`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}customer_tags` (
                `tag_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `label` VARCHAR(80) NOT NULL,
                `label_hash` CHAR(64) NOT NULL,
                `color` CHAR(7) NOT NULL DEFAULT '#DBEAFE',
                `date_added` DATETIME NOT NULL,
                UNIQUE KEY `uniq_label_hash` (`label_hash`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}customer_tag_links` (
                `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `customer_id` INT UNSIGNED NOT NULL,
                `chat_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `tag_id` INT UNSIGNED NOT NULL,
                `date_added` DATETIME NOT NULL,
                UNIQUE KEY `uniq_customer_chat_tag` (`customer_id`, `chat_id`, `tag_id`),
                INDEX `idx_tag_customer` (`tag_id`, `customer_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}customer_ip_geo_cache` (
                `ip_hash` CHAR(64) NOT NULL PRIMARY KEY,
                `ip` VARCHAR(45) NOT NULL,
                `location` VARCHAR(160) NOT NULL DEFAULT '',
                `status` VARCHAR(24) NOT NULL DEFAULT 'pending',
                `checked_at` DATETIME NOT NULL,
                `expires_at` DATETIME NOT NULL,
                INDEX `idx_expires_at` (`expires_at`),
                INDEX `idx_status_checked` (`status`, `checked_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}customer_contact_points` (
                `contact_point_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `customer_id` INT UNSIGNED NOT NULL,
                `type` VARCHAR(16) NOT NULL,
                `value` VARCHAR(255) NOT NULL,
                `value_hash` CHAR(64) NOT NULL,
                `source` VARCHAR(32) NOT NULL DEFAULT '',
                `source_customer_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `is_primary` TINYINT(1) NOT NULL DEFAULT 0,
                `status` VARCHAR(24) NOT NULL DEFAULT 'auto_detected',
                `verification_source` VARCHAR(32) NOT NULL DEFAULT '',
                `verified_at` DATETIME DEFAULT NULL,
                `source_chat_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `source_message_id` INT UNSIGNED NOT NULL DEFAULT 0,
                `added_by` INT UNSIGNED NOT NULL DEFAULT 0,
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                `last_seen_at` DATETIME DEFAULT NULL,
                UNIQUE KEY `uniq_customer_contact_point` (`customer_id`, `type`, `value_hash`),
                INDEX `idx_contact_value` (`type`, `value_hash`),
                INDEX `idx_contact_customer` (`customer_id`, `type`),
                INDEX `idx_contact_primary` (`customer_id`, `type`, `is_primary`, `status`),
                INDEX `idx_contact_source_chat` (`source_chat_id`, `source_message_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            self::commerceIdentitySchemaQuery($prefix),

            self::cmsVisitorIdentitySchemaQuery($prefix),

            "CREATE TABLE IF NOT EXISTS `{$prefix}reply` (
                `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `manager_id` INT(11) DEFAULT NULL,
                `json` MEDIUMTEXT NOT NULL,
                FOREIGN KEY (`manager_id`) REFERENCES `{$prefix}managers` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}quick_replies` (
                `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `scope` VARCHAR(16) NOT NULL DEFAULT 'personal',
                `manager_id` INT(11) DEFAULT NULL,
                `title` VARCHAR(80) NOT NULL DEFAULT '',
                `text` MEDIUMTEXT NOT NULL,
                `sort_order` INT(11) NOT NULL DEFAULT 0,
                `is_active` TINYINT(1) NOT NULL DEFAULT 1,
                `date_added` DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                INDEX `idx_scope_manager_active` (`scope`, `manager_id`, `is_active`, `sort_order`),
                FOREIGN KEY (`manager_id`) REFERENCES `{$prefix}managers` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}app_session` (
                `session_id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `session_token` VARCHAR(255) NOT NULL,
                `access_token_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
                `refresh_token_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
                `token_family` CHAR(32) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
                `access_expires_at` DATETIME DEFAULT NULL,
                `refresh_expires_at` DATETIME DEFAULT NULL,
                `absolute_expires_at` DATETIME DEFAULT NULL,
                `fcm_token` TEXT DEFAULT NULL,
                `fcm_token_hash` CHAR(64) DEFAULT NULL,
                `fcm_token_synced_at` DATETIME DEFAULT NULL,
                `manager_id` INT(11) NOT NULL,
                `app_device_id` INT UNSIGNED DEFAULT NULL,
                `app_version_code` INT UNSIGNED NOT NULL DEFAULT 0,
                `app_version_name` VARCHAR(32) NOT NULL DEFAULT '',
                `device_label` VARCHAR(120) NOT NULL DEFAULT '',
                `device_last_seen_at` DATETIME DEFAULT NULL,
                `last_used_at` DATETIME DEFAULT NULL,
                `revoked_at` DATETIME DEFAULT NULL,
                `revoke_reason` VARCHAR(64) NOT NULL DEFAULT '',
                `date_added` DATETIME NOT NULL,
                `last_push_at` DATETIME DEFAULT NULL,
                `last_push_error` VARCHAR(128) NOT NULL DEFAULT '',
                `last_push_error_at` DATETIME DEFAULT NULL,
                INDEX `idx_fcm_token_hash` (`fcm_token_hash`),
                INDEX `idx_fcm_health` (`fcm_token_synced_at`, `last_push_error_at`),
                INDEX `idx_app_device_version` (`manager_id`, `app_version_code`, `device_last_seen_at`),
                UNIQUE KEY `uniq_app_access_hash` (`access_token_hash`),
                UNIQUE KEY `uniq_app_refresh_hash` (`refresh_token_hash`),
                KEY `idx_app_session_device_active` (`app_device_id`, `revoked_at`, `access_expires_at`),
                KEY `idx_app_token_family` (`token_family`, `revoked_at`),
                FOREIGN KEY (`manager_id`) REFERENCES `{$prefix}managers` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            \EasyChat\Model\AppDevice::tableSql($prefix) . ";",

            \EasyChat\Model\AppDevice::eventTableSql($prefix) . ";",

            "CREATE TABLE IF NOT EXISTS `{$prefix}app_refresh_token_history` (
                `history_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `session_id` INT(11) NOT NULL,
                `token_family` CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
                `token_hash` CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
                `used_at` DATETIME NOT NULL,
                `expires_at` DATETIME NOT NULL,
                `replacement_bundle` TEXT DEFAULT NULL,
                UNIQUE KEY `uniq_app_refresh_history_hash` (`token_hash`),
                KEY `idx_app_refresh_history_family` (`token_family`, `used_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            self::managerNoticeStateTableSql($prefix) . ";",

            self::outboundContactRequestsTableSql($prefix) . ";",

            self::smsDevicesTableSql($prefix) . ";",

            self::smsJobsTableSql($prefix) . ";",

            self::smsJobEventsTableSql($prefix) . ";",

            self::aiSuggestionCacheTableSql($prefix) . ";",

            "CREATE TABLE IF NOT EXISTS `{$prefix}moderation_tokens` (
                `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `public_id` VARCHAR(64) NOT NULL,
                `token_hash` CHAR(64) NOT NULL,
                `purpose` VARCHAR(64) NOT NULL,
                `version_code` INT(11) NOT NULL DEFAULT 0,
                `expires_at` DATETIME NOT NULL,
                `used_at` DATETIME DEFAULT NULL,
                `created_at` DATETIME NOT NULL,
                `used_by_fcm_hash` CHAR(64) DEFAULT NULL,
                `last_error` VARCHAR(255) DEFAULT NULL,
                UNIQUE KEY `uniq_public_id` (`public_id`),
                UNIQUE KEY `uniq_token_hash` (`token_hash`),
                KEY `idx_purpose_expires` (`purpose`, `expires_at`),
                KEY `idx_used_at` (`used_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            \EasyChat\Model\AppPairing::tableSql($prefix) . ";",

            \EasyChat\Model\MessageContext::tableSql($prefix),

            \EasyChat\Model\MessengerItemContext::tableSql($prefix),

            \EasyChat\Model\ScheduledMessage::tableSql($prefix),

            \EasyChat\Model\ScheduledMessage::eventTableSql($prefix),

            "CREATE TABLE IF NOT EXISTS `{$prefix}queue` (
                `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `job_type`     VARCHAR(64) NOT NULL,
                `payload`      TEXT NOT NULL,
                `dedupe_key`   VARCHAR(191) NULL DEFAULT NULL,
                `status`       ENUM('pending','processing','done','failed') NOT NULL DEFAULT 'pending',
                `attempts`     TINYINT UNSIGNED NOT NULL DEFAULT 0,
                `max_attempts` TINYINT UNSIGNED NOT NULL DEFAULT 3,
                `priority`     TINYINT UNSIGNED NOT NULL DEFAULT 0,
                `available_at` INT UNSIGNED NOT NULL,
                `locked_at`    INT UNSIGNED NULL DEFAULT NULL,
                `lock_token`   CHAR(32) NULL DEFAULT NULL,
                `created_at`   INT UNSIGNED NOT NULL,
                `finished_at`  INT UNSIGNED NULL DEFAULT NULL,
                `error`        TEXT NULL DEFAULT NULL,
                INDEX `idx_status_available` (`status`, `available_at`),
                INDEX `idx_status_priority` (`status`, `priority`, `available_at`),
                INDEX `idx_status_finished` (`status`, `finished_at`),
                UNIQUE KEY `uq_active_dedupe` (`dedupe_key`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            "CREATE TABLE IF NOT EXISTS `{$prefix}knowledge` (
                `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `title`         VARCHAR(512) NOT NULL DEFAULT '',
                `content`       MEDIUMTEXT NOT NULL,
                `section_id`    TINYINT UNSIGNED NOT NULL DEFAULT 1,
                `item_id`       INT UNSIGNED NOT NULL DEFAULT 0,
                `content_hash`  CHAR(64) NOT NULL DEFAULT '',
                `sync_generation` CHAR(32) NOT NULL DEFAULT '',
                `vector_id`     INT UNSIGNED NULL DEFAULT NULL,
                `status`        ENUM('pending','indexing','active','disabled','failed') NOT NULL DEFAULT 'pending',
                `error`         TEXT NULL DEFAULT NULL,
                `date_added`    DATETIME NOT NULL,
                `date_modified` DATETIME NOT NULL,
                INDEX `idx_section` (`section_id`),
                INDEX `idx_status`  (`status`),
                INDEX `idx_section_item` (`section_id`, `item_id`),
                INDEX `idx_section_generation` (`section_id`, `sync_generation`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",

            self::catalogPublicUrlTableSql($prefix) . ";",
        ), self::messengerSchemaQueries($prefix), self::marketplaceSchemaQueries($prefix));
    }

    private static function createAdmin($conn, $prefix, $admin_login, $admin_pass) {
        $existing = $conn->query("SELECT `id`, `role`, `status` FROM `{$prefix}managers` WHERE `id` = 1 LIMIT 1");
        if (!$existing) {
            return array('success' => false, 'error' => 'Не удалось безопасно проверить существующего администратора EasyChat. Данные и пароль не изменены.');
        }
        if ($existing->num_rows > 0) {
            $admin = $existing->fetch_assoc();
            $existing->free();
            if ((string)($admin['role'] ?? '') !== 'admin' || (int)($admin['status'] ?? 0) !== 1) {
                return array('success' => false, 'error' => 'Существующая учетная запись администратора EasyChat требует безопасного восстановления. Пароль и роль не были изменены.');
            }

             
             
            return array('success' => true, 'created' => false);
        }
        $existing->free();

        $salt = bin2hex(random_bytes(16));
        $hashed_pass = (new PasswordCredentialService())->hash($admin_pass);
        $admin_name = 'Администратор';

        $stmt = $conn->prepare("INSERT INTO `{$prefix}managers` (id, username, password, salt, name, role, status, date_added) VALUES (1, ?, ?, ?, ?, 'admin', 1, NOW())");
        if (!$stmt) {
            return array('success' => false, 'error' => 'Ошибка подготовки запроса администратора: ' . $conn->error);
        }

        $stmt->bind_param('ssss', $admin_login, $hashed_pass, $salt, $admin_name);
        if (!$stmt->execute()) {
            $error = $stmt->error;
            $stmt->close();
            return array('success' => false, 'error' => 'Ошибка добавления/обновления администратора: ' . $error);
        }

        $stmt->close();

        return array('success' => true, 'created' => true);
    }

    public static function normalizeCmsLocale($locale) {
        return I18n::normalizeLocale($locale);
    }

    public static function syncCmsLocale(array $params) {
        $host = self::stringValue($params, 'host');
        $port = self::stringValue($params, 'port', '3306');
        $user = self::stringValue($params, 'user');
        $password = self::stringValue($params, 'password');
        $database = self::stringValue($params, 'database');
        $prefix = self::normalizePrefix(self::stringValue($params, 'prefix', 'easychat_'));

        if ($host === '' || $user === '' || $database === '') {
            return array('success' => false, 'error' => 'Заполните обязательные параметры подключения');
        }

        if (!preg_match('/^[a-zA-Z0-9_]+$/', $prefix)) {
            return array('success' => false, 'error' => 'Префикс таблиц может содержать только латинские буквы, цифры и underscore');
        }

        $connection = self::connectMysqli($host, $user, $password, $database, (int)$port);
        if (!$connection['success']) {
            return array('success' => false, 'error' => 'Не удалось подключиться к БД: ' . $connection['error']);
        }

        $conn = $connection['conn'];
        $conn->set_charset('utf8mb4');
        $result = self::syncCmsLocaleWithMysqli(
            $conn,
            $prefix,
            self::stringValue($params, 'admin_locale', 'ru'),
            self::stringValue($params, 'widget_locale', self::stringValue($params, 'admin_locale', 'ru')),
            self::stringValue($params, 'locale_source', 'cms')
        );
        $conn->close();

        return $result;
    }

    public static function syncCmsLocaleWithMysqli($conn, $prefix, $admin_locale, $widget_locale = '', $locale_source = 'cms') {
        $prefix = self::normalizePrefix((string)$prefix);
        if (!$conn instanceof \mysqli || !preg_match('/^[a-zA-Z0-9_]+$/', $prefix)) {
            return array('success' => false, 'error' => 'Некорректные параметры синхронизации языка');
        }

        $table = $prefix . 'settings';
        $table_like = $conn->real_escape_string($table);
        $exists = $conn->query("SHOW TABLES LIKE '" . $table_like . "'");
        if (!$exists || $exists->num_rows === 0) {
            if ($exists) {
                $exists->free();
            }
            return array('success' => true, 'skipped' => 'missing_settings_table');
        }
        $exists->free();

        $manual_key = 'chat_locale_manually_selected';
        $stmt = $conn->prepare("SELECT `value` FROM `{$table}` WHERE `key` = ? LIMIT 1");
        if (!$stmt) {
            return array('success' => false, 'error' => 'Ошибка подготовки проверки языка: ' . $conn->error);
        }
        $stmt->bind_param('s', $manual_key);
        if (!$stmt->execute()) {
            $error = $stmt->error;
            $stmt->close();
            return array('success' => false, 'error' => 'Ошибка проверки языка: ' . $error);
        }
        $manual_value = '';
        $stmt->bind_result($manual_value);
        if (!$stmt->fetch()) {
            $manual_value = '';
        }
        $stmt->close();

        if ($manual_value === '1') {
            return array('success' => true, 'skipped' => 'manual_locale');
        }

        $admin_locale = I18n::normalizeLocale($admin_locale);
        $widget_locale = I18n::normalizeLocale($widget_locale !== '' ? $widget_locale : $admin_locale);
        $locale_source = $locale_source === 'cms' ? 'cms' : 'manual';

        foreach (array(
            'chat_admin_locale' => $admin_locale,
            'chat_widget_locale' => $widget_locale,
            'chat_widget_locale_auto' => '1',
            'chat_locale_source' => $locale_source,
            'chat_locale_manually_selected' => '0',
        ) as $key => $value) {
            $saved = self::saveSetting($conn, $prefix, $key, $value);
            if (empty($saved['success'])) {
                return $saved;
            }
        }

        return array('success' => true, 'locale' => $admin_locale);
    }

    private static function saveDefaultSettings($conn, $prefix, $install_id, $install_secret, $admin_pass, $admin_locale = 'ru', $widget_locale = 'ru', $locale_source = 'manual', $cms_type = 'none', $cms_prefix = '', array $consent_settings = array(), $widget_base_domain = '', $admin_created = true) {
        $admin_locale = I18n::normalizeLocale($admin_locale);
        $widget_locale = I18n::normalizeLocale($widget_locale);
        $locale_source = $locale_source === 'cms' ? 'cms' : 'manual';
        $cms_type = self::normalizeCmsType($cms_type);
        $cms_prefix = self::normalizeCmsPrefix($cms_prefix);
        $catalog_capability = CmsCatalogCapability::inspectMysqli($conn, $cms_type, $cms_prefix);
        $catalog_ready = !empty($catalog_capability['ready']);
        $defaults = array(
            'chat_status' => '1',
            'chat_widget_color' => '#6366f1',
            'chat_on_accent_mode' => 'auto',
            'chat_widget_theme' => 'light',
            'chat_widget_title' => 'Задайте вопрос специалисту',
            'chat_admin_locale' => $admin_locale,
            'chat_widget_locale' => $widget_locale,
            'chat_widget_locale_auto' => '1',
            'chat_locale_source' => $locale_source,
            'chat_locale_manually_selected' => '0',
            'chat_ai_provider' => 'easytokens',
            'chat_ai_model' => 'easylite',
            'chat_ai_status' => '0',
             
             
            'chat_admin_help_status' => '1',
            AiResponseTimingPolicy::SETTING_MODE => AiResponseTimingPolicy::MODE_STRICT_DELAY,
            AiResponseTimingPolicy::SETTING_OFFLINE_DELAY_SECONDS => (string)AiResponseTimingPolicy::DEFAULT_OFFLINE_DELAY_SECONDS,
            'chat_ai_live_typing' => '1',
            'chat_voice_transcription_status' => '1',
            'chat_ai_suggestions_status' => '0',
             
             
            'chat_ai_copilot_ghost_status' => '1',
            'chat_ai_suggestions_default_mode' => 'on',
            'chat_ai_suggestions_use_knowledge' => '1',
            'chat_ai_suggestions_context_limit' => '12',
            'chat_ai_avito_item_context_status' => '1',
            'chat_ai_youla_item_context_status' => '1',
            'chat_ai_avito_rag_mode' => 'auto',
            'chat_ai_avito_tools_mode' => 'auto',
            'chat_ai_avito_alternatives_mode' => 'on_request',
            'chat_avito_logging' => '0',
            'chat_youla_logging' => '0',
            'chat_messenger_avatars_status' => '1',
            'chat_runtime_timezone' => strtoupper((string)date_default_timezone_get()) !== 'UTC' ? (string)date_default_timezone_get() : '',
            'chat_app_master_token' => bin2hex(random_bytes(16)),
            'chat_install_id' => $install_id,
            'chat_install_secret' => $install_secret,
            'chat_central_api' => EasyTokensGiftProvisioner::defaultGatewayApi(),
            'chat_widget_base_domain' => WidgetDomainPolicy::isAllowedBaseDomain((string)$widget_base_domain) ? WidgetDomainPolicy::normalizeHost((string)$widget_base_domain) : WidgetDomainPolicy::installHost(),
            'chat_cms_type' => $cms_type,
            'chat_cms_prefix' => $cms_prefix,
            'chat_cms_search_limit' => '5',
            'chat_productsearch' => $catalog_ready ? '2' : '0',
            'chat_cms_availability_policy' => 'smart',
            'chat_cms_show_exact_quantity' => '0',
            'chat_cms_semantic_enabled' => '0',
            'chat_cms_index_products' => '0',
            'chat_cms_index_pages' => '0',
            'chat_cms_semantic_auto_opt_out' => '0',
            'chat_cms_search_auto_opt_out' => '0',
            'chat_cms_search_recommendation_dismissed' => '0',
            'chat_settings_revision' => '0',
            'chat_easytokens_gift_status' => '',
            'chat_easytokens_gift_error' => '',
            'chat_easytokens_gift_issued_at' => '',
            'chat_easytokens_gift_tokens' => '',
            'chat_easytokens_gift_email' => '',
            'chat_easytokens_support_key' => '',
            'chat_marketing_install_code' => '',
            'chat_marketing_attribution_status' => '',
            'chat_marketing_attribution_error' => '',
            'chat_marketing_attribution_linked_at' => '',
            'admin_password_customized' => $admin_created ? '0' : '1',
        );

        if ($admin_created) {
            $defaults['admin_initial_password_hash'] = hash('sha256', (string)$admin_pass);
        }

        foreach ($consent_settings as $key => $value) {
            $defaults[$key] = (string)$value;
        }

        foreach ($defaults as $key => $value) {
             
             
            $result = self::saveSettingIfMissing($conn, $prefix, $key, $value);
            if (!$result['success']) {
                return $result;
            }
        }

        return self::saveSettingIfMissing($conn, $prefix, 'chat_ai_prompt', PromptDefaults::current());
    }

    private static function widgetBaseDomainFromParams(array $params): string {
        $candidates = array(
            self::stringValue($params, 'site_url', ''),
            defined('HTTP_SERVER') ? (string)HTTP_SERVER : '',
            WidgetDomainPolicy::installHost(),
        );

        foreach ($candidates as $candidate) {
            $host = WidgetDomainPolicy::normalizeHost((string)$candidate);
            if (WidgetDomainPolicy::isAllowedBaseDomain($host)) {
                return $host;
            }
        }

        return '';
    }

    private static function firstLaunchConsentSettings(array $params, $edition) {
        $explicit_required = self::stringValue($params, 'first_launch_consent_required', '');
        $is_required = $explicit_required !== ''
            ? self::truthy($explicit_required)
            : self::isCmsEdition($edition);

        $admin_email = strtolower(trim(self::stringValue($params, 'admin_email', '')));
        if ($admin_email !== '' && !filter_var($admin_email, FILTER_VALIDATE_EMAIL)) {
            $admin_email = '';
        }

        return array(
            'chat_first_launch_consent_required' => $is_required ? '1' : '0',
            'chat_first_launch_consent_completed' => $is_required ? '0' : '1',
            'chat_first_launch_consent_completed_at' => '',
            'chat_first_launch_consent_source' => '',
            'chat_first_launch_admin_email' => $admin_email,
        );
    }

    private static function isCmsEdition($edition) {
        return in_array((string)$edition, array(
            'opencart',
            'wordpress',
            'wordpress_org',
            'bitrix',
            'webasyst_store',
        ), true);
    }

    private static function cmsTypeForEdition($edition) {
        switch ((string)$edition) {
            case 'opencart':
                return 'opencart';
            case 'wordpress':
            case 'wordpress_org':
                return 'wordpress';
            case 'bitrix':
                return 'bitrix';
            case 'webasyst_store':
                return 'webasyst';
            default:
                return 'none';
        }
    }

    private static function normalizeCmsType($cms_type) {
        $cms_type = strtolower(trim((string)$cms_type));
        return in_array($cms_type, array('opencart', 'wordpress', 'bitrix', 'webasyst'), true)
            ? $cms_type
            : 'none';
    }

    private static function normalizeCmsPrefix($prefix) {
        $prefix = trim((string)$prefix);
        return preg_match('/^[A-Za-z0-9_]*$/', $prefix) ? $prefix : '';
    }

    private static function truthy($value) {
        return in_array(strtolower(trim((string)$value)), array('1', 'true', 'yes', 'on'), true);
    }

    private static function saveSetting($conn, $prefix, $key, $value) {
        if (strlen((string)$key) > self::SETTINGS_KEY_MAX_LENGTH) {
            return array(
                'success' => false,
                'error' => 'Ключ настройки слишком длинный для совместимости со старой MariaDB/MySQL',
            );
        }

        $stmt = $conn->prepare("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES (?, ?) ON DUPLICATE KEY UPDATE `value` = ?");
        if (!$stmt) {
            return array('success' => false, 'error' => 'Ошибка подготовки запроса настройки: ' . $conn->error);
        }

        $stmt->bind_param('sss', $key, $value, $value);
        if (!$stmt->execute()) {
            $error = $stmt->error;
            $stmt->close();
            return array('success' => false, 'error' => 'Ошибка сохранения настройки: ' . $error);
        }

        $stmt->close();

        return array('success' => true);
    }

    private static function saveSettingIfMissing($conn, $prefix, $key, $value) {
        if (strlen((string)$key) > self::SETTINGS_KEY_MAX_LENGTH) {
            return array(
                'success' => false,
                'error' => 'Ключ настройки слишком длинный для совместимости со старой MariaDB/MySQL',
            );
        }

         
         
         
         
        $stmt = $conn->prepare("INSERT INTO `{$prefix}settings` (`key`, `value`) VALUES (?, ?) ON DUPLICATE KEY UPDATE `value` = `value`");
        if (!$stmt) {
            return array('success' => false, 'error' => 'Ошибка подготовки безопасного добавления настройки');
        }

        $key = (string)$key;
        $value = (string)$value;
        $stmt->bind_param('ss', $key, $value);
        if (!$stmt->execute()) {
            $stmt->close();
            return array('success' => false, 'error' => 'Ошибка безопасного добавления настройки');
        }

        $stmt->close();
        return array('success' => true);
    }

    




    private static function acquireInstallMutex($runtime_path) {
        $log_dir = rtrim((string)$runtime_path, '/\\') . DIRECTORY_SEPARATOR . 'log';
        if (!is_dir($log_dir) || is_link($log_dir)) {
            return array(
                'success' => false,
                'error' => 'EasyChat не может открыть защищенную блокировку установки. Проверьте права runtime-каталога и повторите попытку.',
            );
        }

        $lock_path = $log_dir . DIRECTORY_SEPARATOR . 'easychat-install.lock';
        if (is_link($lock_path) || is_dir($lock_path)) {
            return array(
                'success' => false,
                'error' => 'Файл блокировки установки EasyChat небезопасен. Установка остановлена до изменения базы данных.',
            );
        }

        $handle = false;
        if (!file_exists($lock_path)) {
             
             
            $handle = SafeRuntime::call(function () use ($lock_path) {
                $previous_umask = umask(0077);
                try {
                    return fopen($lock_path, 'x+b');
                } finally {
                    umask($previous_umask);
                }
            }, false);
        }
        if (!is_resource($handle) && !is_link($lock_path) && !is_dir($lock_path)) {
             
             
             
            $handle = SafeRuntime::call(function () use ($lock_path) {
                return fopen($lock_path, 'r+b');
            }, false);
        }
        if (!is_resource($handle)) {
            return array(
                'success' => false,
                'error' => 'EasyChat не может открыть защищенную блокировку установки. Проверьте права runtime-каталога и повторите попытку.',
            );
        }

        if (!self::isSamePrivateInstallMutexFile($lock_path, $handle)) {
            SafeRuntime::call(function () use ($handle) {
                return fclose($handle);
            }, false);
            return array(
                'success' => false,
                'error' => 'Файл блокировки установки EasyChat небезопасен. Установка остановлена до изменения базы данных.',
            );
        }

        $locked = (bool)SafeRuntime::call(function () use ($handle) {
            return flock($handle, LOCK_EX | LOCK_NB);
        }, false);
        if (!$locked) {
            $still_same = self::isSamePrivateInstallMutexFile($lock_path, $handle);
            SafeRuntime::call(function () use ($handle) {
                return fclose($handle);
            }, false);
            return array(
                'success' => false,
                'error' => $still_same
                    ? 'Установка EasyChat уже выполняется. Подождите несколько секунд и повторите попытку.'
                    : 'Файл блокировки установки EasyChat изменился во время проверки. Установка остановлена до изменения базы данных.',
            );
        }

         
         
        if (!self::isSamePrivateInstallMutexFile($lock_path, $handle)) {
            self::releaseInstallMutex($handle);
            return array(
                'success' => false,
                'error' => 'Файл блокировки установки EasyChat изменился во время проверки. Установка остановлена до изменения базы данных.',
            );
        }

        return array('success' => true, 'handle' => $handle, 'path' => $lock_path);
    }

    private static function isSamePrivateInstallMutexFile($lock_path, $handle) {
        if (!is_resource($handle)) {
            return false;
        }

        clearstatcache(true, $lock_path);
        $path_stat = SafeRuntime::call(function () use ($lock_path) {
            return lstat($lock_path);
        }, false);
        $handle_stat = SafeRuntime::call(function () use ($handle) {
            return fstat($handle);
        }, false);
        $required_stat_keys = array('mode', 'nlink', 'dev', 'ino');
        if (!is_array($path_stat) || !is_array($handle_stat)) {
            return false;
        }
        foreach ($required_stat_keys as $stat_key) {
            if (!array_key_exists($stat_key, $path_stat) || !array_key_exists($stat_key, $handle_stat)) {
                return false;
            }
        }

        return (((int)$path_stat['mode'] & 0170000) === 0100000)
            && (((int)$handle_stat['mode'] & 0170000) === 0100000)
            && (((int)$path_stat['mode'] & 0777) === 0600)
            && (((int)$handle_stat['mode'] & 0777) === 0600)
            && (int)$path_stat['nlink'] === 1
            && (int)$handle_stat['nlink'] === 1
            && $path_stat['dev'] === $handle_stat['dev']
            && $path_stat['ino'] === $handle_stat['ino'];
    }

    private static function releaseInstallMutex($handle) {
        if (!is_resource($handle)) {
            return;
        }
        SafeRuntime::call(function () use ($handle) {
            return flock($handle, LOCK_UN);
        }, false);
        SafeRuntime::call(function () use ($handle) {
            return fclose($handle);
        }, false);
    }

    private static function validateExistingConfigConnection($config_source, array $expected) {
        if (!is_string($config_source)) {
            return array(
                'success' => false,
                'error' => 'Существующий config.php недоступен или неполон. Он не был перезаписан, чтобы не повредить данные; восстановите файл из резервной копии.',
            );
        }

        $parsed = self::parseConfigConnectionLiterals($config_source);
        if (empty($parsed['success'])) {
            return array(
                'success' => false,
                'error' => 'Существующий config.php нельзя безопасно проверить. Он не был выполнен или перезаписан; восстановите целый файл из резервной копии.',
            );
        }

        $connection_matches = true;
        foreach (array('host', 'port', 'user', 'password', 'database', 'prefix') as $key) {
            $actual_value = (string)$parsed['connection'][$key];
            $expected_value = isset($expected[$key]) ? (string)$expected[$key] : '';
             
             
            $field_matches = hash_equals($actual_value, $expected_value);
            $connection_matches = $field_matches && $connection_matches;
        }
        if (!$connection_matches) {
            return array(
                'success' => false,
                'error' => 'Параметры повторной установки не совпадают с действующим config.php. Существующая установка и база данных не были изменены.',
            );
        }

        return array('success' => true);
    }

    




    private static function parseConfigConnectionLiterals($config_source) {
        $tracked_names = array(
            'DB_HOSTNAME' => true,
            'DB_PORT' => true,
            'DB_USERNAME' => true,
            'DB_PASSWORD' => true,
            'DB_DATABASE' => true,
            'EASYCHAT_DB_PREFIX' => true,
            'DB_PREFIX' => true,
        );
        $values = array();
        $seen = array();
        $invalid = array();

        try {
            $tokens = token_get_all((string)$config_source, TOKEN_PARSE);
        } catch (\Throwable $e) {
            return array('success' => false);
        }

        $brace_depth = 0;
        $token_count = count($tokens);
        for ($i = 0; $i < $token_count; $i++) {
            $token = $tokens[$i];
            if ($token === '{') {
                $brace_depth++;
                continue;
            }
            if ($token === '}') {
                $brace_depth = max(0, $brace_depth - 1);
                continue;
            }
            if (is_array($token) && $token[0] === T_NAMESPACE) {
                 
                 
                return array('success' => false);
            }
            if (!is_array($token) || $token[0] !== T_STRING || strtolower($token[1]) !== 'define') {
                continue;
            }

            $open_index = self::nextConfigTokenIndex($tokens, $i + 1);
            if ($open_index === null || $tokens[$open_index] !== '(') {
                continue;
            }
            $name_index = self::nextConfigTokenIndex($tokens, $open_index + 1);
            if ($name_index === null || !is_array($tokens[$name_index]) || $tokens[$name_index][0] !== T_CONSTANT_ENCAPSED_STRING) {
                continue;
            }
            $decoded_name = self::decodeConfigStringLiteral($tokens[$name_index][1]);
            if (empty($decoded_name['success'])) {
                continue;
            }
            $name = (string)$decoded_name['value'];
            if (!isset($tracked_names[$name])) {
                continue;
            }

            $seen[$name] = isset($seen[$name]) ? $seen[$name] + 1 : 1;
            if ($seen[$name] > 1 || $brace_depth !== 0 || !self::isTopLevelConfigDefine($tokens, $i)) {
                $invalid[$name] = true;
                continue;
            }

            $comma_index = self::nextConfigTokenIndex($tokens, $name_index + 1);
            if ($comma_index === null || $tokens[$comma_index] !== ',') {
                $invalid[$name] = true;
                continue;
            }

            $literal = self::parseConfigLiteralExpression($tokens, $comma_index + 1);
            if (empty($literal['success'])) {
                $invalid[$name] = true;
                continue;
            }

            $values[$name] = (string)$literal['value'];
            $i = (int)$literal['close_index'];
        }

        foreach (array('DB_HOSTNAME', 'DB_PORT', 'DB_USERNAME', 'DB_PASSWORD', 'DB_DATABASE') as $required_name) {
            if (!array_key_exists($required_name, $values) || !empty($invalid[$required_name])) {
                return array('success' => false);
            }
        }

         
         
        $canonical_prefix_declared = !empty($seen['EASYCHAT_DB_PREFIX']);
        $prefix_name = $canonical_prefix_declared ? 'EASYCHAT_DB_PREFIX' : 'DB_PREFIX';
        if (!array_key_exists($prefix_name, $values) || !empty($invalid[$prefix_name])) {
            return array('success' => false);
        }

        return array(
            'success' => true,
            'connection' => array(
                'host' => $values['DB_HOSTNAME'],
                'port' => $values['DB_PORT'],
                'user' => $values['DB_USERNAME'],
                'password' => $values['DB_PASSWORD'],
                'database' => $values['DB_DATABASE'],
                'prefix' => $values[$prefix_name],
            ),
        );
    }

    private static function isTopLevelConfigDefine(array $tokens, $define_index) {
        $previous_index = self::previousConfigTokenIndex($tokens, $define_index - 1);
        if ($previous_index === null) {
            return true;
        }

        $previous = $tokens[$previous_index];
        if (is_array($previous) && $previous[0] === T_OPEN_TAG) {
            return true;
        }
        if ($previous === ';') {
            return true;
        }

         
         
        if ((is_array($previous) && $previous[0] === T_NS_SEPARATOR) || $previous === '\\') {
            $before_separator = self::previousConfigTokenIndex($tokens, $previous_index - 1);
            if ($before_separator === null) {
                return true;
            }
            $before = $tokens[$before_separator];
            return $before === ';' || (is_array($before) && $before[0] === T_OPEN_TAG);
        }

        return false;
    }

    private static function parseConfigLiteralExpression(array $tokens, $start_index) {
        $index = self::nextConfigTokenIndex($tokens, $start_index);
        $value = '';
        $has_piece = false;

        while ($index !== null) {
            $piece = self::parseConfigLiteralToken($tokens[$index]);
            if (empty($piece['success'])) {
                return array('success' => false);
            }
            $value .= (string)$piece['value'];
            $has_piece = true;

            $next_index = self::nextConfigTokenIndex($tokens, $index + 1);
            if ($next_index === null) {
                return array('success' => false);
            }
            if ($tokens[$next_index] === ')') {
                $after_close = self::nextConfigTokenIndex($tokens, $next_index + 1);
                if ($after_close !== null
                    && $tokens[$after_close] !== ';'
                    && !(is_array($tokens[$after_close]) && $tokens[$after_close][0] === T_CLOSE_TAG)
                ) {
                    return array('success' => false);
                }
                return array(
                    'success' => $has_piece,
                    'value' => $value,
                    'close_index' => $next_index,
                );
            }
            if ($tokens[$next_index] !== '.') {
                 
                 
                return array('success' => false);
            }

            $index = self::nextConfigTokenIndex($tokens, $next_index + 1);
        }

        return array('success' => false);
    }

    private static function parseConfigLiteralToken($token) {
        if (is_array($token) && $token[0] === T_CONSTANT_ENCAPSED_STRING) {
            return self::decodeConfigStringLiteral($token[1]);
        }
        if (is_array($token)
            && $token[0] === T_LNUMBER
            && preg_match('/^(?:0|[1-9][0-9]*)$/D', $token[1])
        ) {
            return array('success' => true, 'value' => $token[1]);
        }

        return array('success' => false);
    }

    private static function decodeConfigStringLiteral($literal) {
        $literal = (string)$literal;
        $length = strlen($literal);
        if ($length < 2) {
            return array('success' => false);
        }

        $quote = $literal[0];
        if (($quote !== "'" && $quote !== '"') || $literal[$length - 1] !== $quote) {
            return array('success' => false);
        }

        $body = substr($literal, 1, -1);
        $decoded = '';
        $body_length = strlen($body);
        for ($i = 0; $i < $body_length; $i++) {
            $char = $body[$i];
            if ($char !== '\\') {
                $decoded .= $char;
                continue;
            }
            if ($i + 1 >= $body_length) {
                return array('success' => false);
            }

            $next = $body[++$i];
            if ($quote === "'") {
                $decoded .= ($next === "'" || $next === '\\') ? $next : '\\' . $next;
                continue;
            }

            $simple_escapes = array(
                'n' => "\n",
                'r' => "\r",
                't' => "\t",
                'v' => "\v",
                'e' => chr(27),
                'f' => "\f",
                '\\' => '\\',
                '$' => '$',
                '"' => '"',
            );
            if (array_key_exists($next, $simple_escapes)) {
                $decoded .= $simple_escapes[$next];
                continue;
            }
            if ($next >= '0' && $next <= '7') {
                $octal = $next;
                for ($offset = 0; $offset < 2 && $i + 1 < $body_length; $offset++) {
                    $candidate = $body[$i + 1];
                    if ($candidate < '0' || $candidate > '7') {
                        break;
                    }
                    $octal .= $candidate;
                    $i++;
                }
                $decoded .= chr(octdec($octal) & 0xff);
                continue;
            }
            if ($next === 'x' || $next === 'X') {
                $hex = '';
                for ($offset = 0; $offset < 2 && $i + 1 < $body_length; $offset++) {
                    $candidate = $body[$i + 1];
                    if (!ctype_xdigit($candidate)) {
                        break;
                    }
                    $hex .= $candidate;
                    $i++;
                }
                if ($hex !== '') {
                    $decoded .= chr(hexdec($hex));
                    continue;
                }
            }

             
             
             
            if ($next === 'u' && $i + 1 < $body_length && $body[$i + 1] === '{') {
                return array('success' => false);
            }
            $decoded .= '\\' . $next;
        }

        return array('success' => true, 'value' => $decoded);
    }

    private static function nextConfigTokenIndex(array $tokens, $start_index) {
        $token_count = count($tokens);
        for ($i = (int)$start_index; $i < $token_count; $i++) {
            if (!self::isConfigTriviaToken($tokens[$i])) {
                return $i;
            }
        }
        return null;
    }

    private static function previousConfigTokenIndex(array $tokens, $start_index) {
        for ($i = (int)$start_index; $i >= 0; $i--) {
            if (!self::isConfigTriviaToken($tokens[$i])) {
                return $i;
            }
        }
        return null;
    }

    private static function isConfigTriviaToken($token) {
        return is_array($token) && in_array($token[0], array(T_WHITESPACE, T_COMMENT, T_DOC_COMMENT), true);
    }

    private static function buildConfig(array $data) {
         
         
        $host = var_export((string)$data['host'], true);
        $port = var_export((string)$data['port'], true);
        $user = var_export((string)$data['user'], true);
        $password = var_export((string)$data['password'], true);
        $database = var_export((string)$data['database'], true);
        $prefix = var_export((string)$data['prefix'], true);
        $admin_login = var_export((string)$data['admin_login'], true);
        $admin_password = var_export((string)$data['admin_password'], true);
        $cron_secret = var_export(bin2hex(random_bytes(16)), true);
        $edition = var_export((string)(isset($data['edition']) ? $data['edition'] : 'standalone'), true);
        $cms_cron_mode = var_export((string)(isset($data['cms_cron_mode']) ? $data['cms_cron_mode'] : 'standalone'), true);

        return "<?php
// Динамическая конфигурация EasyChat

if (!defined('DS')) {
    define('DS', DIRECTORY_SEPARATOR);
}
define('SITE_PATH', __DIR__);
define('DIR_LIBRARY', SITE_PATH . DS . 'library');
define('DIR_LOGS', SITE_PATH . DS . 'log');
define('EASYCHAT_AUTOLOAD_CACHE_DIR', SITE_PATH . DS . 'log');
define('DIR_IMAGE', SITE_PATH . DS . 'uploads' . DS);
define('DIR_AVATARS', SITE_PATH . DS . 'images' . DS . 'avatars' . DS);

require_once DIR_LIBRARY . DS . 'request_context.php';
require_once DIR_LIBRARY . DS . 'url_helper.php';

define('PROTOCOL', easychat_public_scheme() . '://');
define('HTTP_SERVER', easychat_public_base_url());
define('HTTP_AVATARS', HTTP_SERVER . 'images/avatars/');
define('EASYCHAT_ASSET_BASE_URL', HTTP_SERVER);

// Подключение к базе данных
define('DB_DRIVER', 'mysqli');
define('DB_HOSTNAME', " . $host . ");
define('DB_USERNAME', " . $user . ");
define('DB_PASSWORD', " . $password . ");
define('DB_DATABASE', " . $database . ");
define('DB_PORT', " . $port . ");
define('EASYCHAT_DB_PREFIX', " . $prefix . ");
if (!defined('DB_PREFIX')) {
    define('DB_PREFIX', EASYCHAT_DB_PREFIX);
}
define('VECTOR_STORAGE', EASYCHAT_DB_PREFIX . 'vectors');

// Учетные данные главного администратора
define('ADMIN_USERNAME', " . $admin_login . ");
define('ADMIN_PASSWORD', " . $admin_password . ");

// Secret token for HTTP-based CRON calls (keep this private)
define('CRON_SECRET', " . $cron_secret . ");

// Demo stand only: prefill operator login form with manager/manager.
define('EASYCHAT_DEMO_LOGIN_PREFILL', false);

// CMS adapter metadata. Standalone/direct packages keep standalone cron by default.
define('EASYCHAT_CMS_EDITION', " . $edition . ");
define('EASYCHAT_CMS_CRON_MODE', " . $cms_cron_mode . ");
";
    }

    private static function ensureRuntimeDirs($app_path) {
        $dirs = array(
            $app_path . DIRECTORY_SEPARATOR . 'uploads' => 0770,
            $app_path . DIRECTORY_SEPARATOR . 'images' . DIRECTORY_SEPARATOR . 'avatars' => 0755,
            $app_path . DIRECTORY_SEPARATOR . 'log' => 0770,
            $app_path . DIRECTORY_SEPARATOR . 'log' . DIRECTORY_SEPARATOR . 'sessions' => 0700,
            $app_path . DIRECTORY_SEPARATOR . 'log' . DIRECTORY_SEPARATOR . 'session-revocations' => 0700,
        );

        foreach ($dirs as $dir => $mode) {
            if (!file_exists($dir)) {
                if (!SafeRuntime::mkdir($dir, $mode, true)) {
                    return array('success' => false, 'error' => 'EasyChat не может подготовить защищенные runtime-каталоги. Проверьте права на запись и повторите установку.');
                }
            }
            if (!is_dir($dir) || !is_writable($dir)) {
                return array('success' => false, 'error' => 'EasyChat не может записывать runtime-данные. Проверьте права на каталоги EasyChat и повторите установку.');
            }
        }

        if (!self::ensureRuntimeProtection($app_path)) {
            return array('success' => false, 'error' => 'EasyChat не может защитить runtime-каталоги или сохранить в них данные. Проверьте права на запись и повторите установку.');
        }

        return array('success' => true);
    }

    private static function ensureProtectedRuntimeDir($dir, $htaccess_content, $mode = 0770) {
        if ($dir === '') {
            return false;
        }
        if (is_link($dir)) {
            return false;
        }
        if (!is_dir($dir)) {
            SafeRuntime::mkdir($dir, $mode, true);
        }
        if (!is_dir($dir) || is_link($dir)) {
            return false;
        }

         
         
         
        $current_mode = SafeRuntime::permissions($dir);
        if ($current_mode !== null) {
             
             
            $hardened_mode = $current_mode & $mode;
            if ($hardened_mode !== $current_mode) {
                if (!SafeRuntime::chmod($dir, $hardened_mode)) {
                    return false;
                }
            }
        }
        clearstatcache(true, $dir);
        $effective_mode = SafeRuntime::permissions($dir);
        if ($effective_mode === null || ($effective_mode & ~$mode) !== 0 || !is_writable($dir)) {
            return false;
        }

         
         
         
         
         
        $htaccess = rtrim($dir, '/\\') . DIRECTORY_SEPARATOR . '.htaccess';
        $current = is_file($htaccess) ? SafeRuntime::readFile($htaccess) : false;
        if ($current !== $htaccess_content) {
            if (!SafeRuntime::writeFileAtomic($htaccess, $htaccess_content, 0640)) {
                return false;
            }
        }

        return is_file($htaccess);
    }

    private static function runtimeDenyAllHtaccess() {
        return "Options -Indexes\n" .
            "<IfModule mod_authz_core.c>\n" .
            "    Require all denied\n" .
            "</IfModule>\n" .
            "<IfModule !mod_authz_core.c>\n" .
            "    Deny from all\n" .
            "</IfModule>\n";
    }

    private static function runtimeUploadsHtaccess() {
        return self::runtimeDenyAllHtaccess() .
            "<FilesMatch \"(?i)\\.(php|phtml|php3|php4|php5|php6|php7|php8|phar|pl|py|jsp|asp|sh|cgi)$\">\n" .
            "    Order Allow,Deny\n" .
            "    Deny from all\n" .
            "</FilesMatch>\n" .
            "RemoveHandler .php .phtml .php3 .php4 .php5 .php6 .php7 .php8 .phar .pl .py .jsp .asp .sh .cgi\n" .
            "php_flag engine off\n";
    }

    private static function detectVersion($app_path) {
        $version_file = rtrim($app_path, '/\\') . DIRECTORY_SEPARATOR . 'version.php';
        if (is_file($version_file)) {
            $source = file_get_contents($version_file);
            if (preg_match("/define\\('EASYCHAT_VERSION',\\s*'([^']+)'\\);/", $source, $matches) && self::isEasyChatVersion($matches[1])) {
                return $matches[1];
            }
        }

        if (defined('EASYCHAT_VERSION') && self::isEasyChatVersion(EASYCHAT_VERSION)) {
            return EASYCHAT_VERSION;
        }

         
        if (defined('VERSION') && self::isEasyChatVersion(VERSION)) {
            return VERSION;
        }

        return '1.0.0';
    }

    private static function isEasyChatVersion($version) {
        return is_string($version) && preg_match('/^[0-9]+\\.[0-9]+\\.[0-9]+(?:[-+][A-Za-z0-9._-]+)?$/', $version);
    }

    private static function normalizePrefix($prefix) {
        $prefix = trim($prefix);
        return $prefix === '' ? 'easychat_' : $prefix;
    }

    private static function stringValue(array $params, $key, $default = '') {
        return isset($params[$key]) ? trim((string)$params[$key]) : $default;
    }

    private static function connectMysqli($host, $user, $password, $database, $port) {
        $captured_error = '';
        $conn = SafeRuntime::call(function () use ($host, $user, $password, $database, $port) {
            return new \mysqli($host, $user, $password, $database, (int)$port);
        }, false);

        if (!$conn instanceof \mysqli) {
            return array('success' => false, 'conn' => null, 'error' => 'mysqli connection failed');
        }

        if ($conn->connect_error) {
            $captured_error = $conn->connect_error;
            $conn->close();
            return array('success' => false, 'conn' => null, 'error' => $captured_error);
        }

        return array('success' => true, 'conn' => $conn, 'error' => '');
    }
}
