ylrz_saas_pharmacy/java/sql/lobster_replay_archive_phase2.sql

-- Phase 2: replay cold archive table + hot buffer index
-- Run on tenant DB when upgrading lobster learning module

CREATE TABLE IF NOT EXISTS `lobster_learning_replay_archive` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `company_id` bigint NOT NULL COMMENT 'company id',
    `instance_id` bigint DEFAULT NULL,
    `node_code` varchar(100) DEFAULT NULL,
    `customer_message` text,
    `ai_reply` text,
    `quality_score` double DEFAULT NULL,
    `create_time` datetime DEFAULT NULL,
    `archived_time` datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_company_archived` (`company_id`, `archived_time`),
    KEY `idx_company_quality` (`company_id`, `quality_score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='lobster replay cold archive';

ALTER TABLE `lobster_learning_replay_buffer`
    ADD INDEX `idx_company_quality_time` (`company_id`, `quality_score`, `create_time`);