ylrz_saas_his_scrm/sql/lobster_engine_evolution.sql

-- =============================================
-- 龙虾引擎 - 消息触达抽象层 & 闭环学习系统
-- =============================================

-- 1. 进化交互日志表
CREATE TABLE IF NOT EXISTS `lobster_evolution_log` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `company_id` bigint NOT NULL COMMENT '公司ID',
  `workflow_id` bigint NOT NULL COMMENT '工作流ID',
  `instance_id` bigint DEFAULT NULL COMMENT '实例ID',
  `contact_id` bigint DEFAULT NULL COMMENT '联系人ID',
  `channel_type` varchar(20) DEFAULT 'QW' COMMENT '通道类型: QW/WX/IM',
  `node_code` varchar(100) DEFAULT NULL COMMENT '节点编码',
  `sent_message` text COMMENT '发送的消息',
  `customer_reply` text COMMENT '客户回复',
  `outcome` varchar(50) DEFAULT NULL COMMENT '交互结果: purchase/inquiry/complaint/positive/negative/schedule/other',
  `variables` json DEFAULT NULL COMMENT '变量快照',
  `duration_ms` bigint DEFAULT NULL COMMENT '响应时长(毫秒)',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_company_workflow` (`company_id`, `workflow_id`),
  KEY `idx_instance` (`instance_id`),
  KEY `idx_node_code` (`node_code`),
  KEY `idx_outcome` (`outcome`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='龙虾引擎-进化交互日志';

-- 2. 进化优化建议表
CREATE TABLE IF NOT EXISTS `lobster_evolution_suggestion` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `company_id` bigint NOT NULL COMMENT '公司ID',
  `workflow_id` bigint NOT NULL COMMENT '工作流ID',
  `node_code` varchar(100) DEFAULT NULL COMMENT '节点编码',
  `suggestion_type` varchar(50) DEFAULT 'message_optimize' COMMENT '建议类型: message_optimize/timing_adjust/channel_switch',
  `current_content` text COMMENT '当前内容',
  `suggested_content` text COMMENT '建议内容',
  `confidence` double DEFAULT NULL COMMENT '置信度',
  `reason` text COMMENT '优化原因',
  `metrics` json DEFAULT NULL COMMENT '相关指标',
  `status` tinyint DEFAULT 0 COMMENT '0待处理 1已应用 2已忽略',
  `apply_time` datetime DEFAULT NULL COMMENT '应用时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_company_workflow` (`company_id`, `workflow_id`),
  KEY `idx_status` (`status`),
  KEY `idx_node_code` (`node_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='龙虾引擎-进化优化建议';

-- 3. 心跳注册表
CREATE TABLE IF NOT EXISTS `lobster_heartbeat_registry` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `instance_id` bigint NOT NULL COMMENT '工作流实例ID',
  `company_id` bigint NOT NULL COMMENT '公司ID',
  `workflow_id` bigint NOT NULL COMMENT '工作流ID',
  `contact_id` bigint DEFAULT NULL COMMENT '联系人ID',
  `channel_type` varchar(20) DEFAULT 'QW' COMMENT '通道类型',
  `interval_ms` bigint DEFAULT 300000 COMMENT '心跳间隔(毫秒)',
  `timeout_ms` bigint DEFAULT 86400000 COMMENT '超时时间(毫秒)',
  `max_retries` int DEFAULT 3 COMMENT '最大重试次数',
  `auto_execute` tinyint DEFAULT 1 COMMENT '是否自动执行',
  `status` varchar(20) DEFAULT 'ACTIVE' COMMENT 'ACTIVE/INACTIVE',
  `last_heartbeat` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '最后心跳时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_instance_id` (`instance_id`),
  KEY `idx_company` (`company_id`),
  KEY `idx_status` (`status`),
  KEY `idx_last_heartbeat` (`last_heartbeat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='龙虾引擎-心跳注册表';

-- 4. 消息触达日志表
CREATE TABLE IF NOT EXISTS `lobster_message_delivery_log` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `company_id` bigint NOT NULL COMMENT '公司ID',
  `instance_id` bigint DEFAULT NULL COMMENT '工作流实例ID',
  `workflow_id` bigint DEFAULT NULL COMMENT '工作流ID',
  `contact_id` bigint DEFAULT NULL COMMENT '联系人ID',
  `channel_type` varchar(20) NOT NULL COMMENT '通道类型: QW/WX/IM',
  `channel_user_id` varchar(200) DEFAULT NULL COMMENT '通道用户ID',
  `content` text COMMENT '消息内容',
  `msg_type` int DEFAULT 1 COMMENT '消息类型: 1文本 2图片 3语音',
  `channel_msg_id` varchar(200) DEFAULT NULL COMMENT '通道消息ID',
  `success` tinyint DEFAULT 0 COMMENT '是否成功: 0否 1是',
  `error_msg` varchar(500) DEFAULT NULL COMMENT '错误信息',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_company` (`company_id`),
  KEY `idx_instance` (`instance_id`),
  KEY `idx_channel_type` (`channel_type`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='龙虾引擎-消息触达日志';

-- 5. 联系人统一视图(便于跨通道查询)
CREATE TABLE IF NOT EXISTS `lobster_unified_contact` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `company_id` bigint NOT NULL COMMENT '公司ID',
  `contact_id` bigint NOT NULL COMMENT '原始联系人ID',
  `contact_name` varchar(200) DEFAULT NULL COMMENT '联系人名称',
  `contact_phone` varchar(50) DEFAULT NULL COMMENT '联系人电话',
  `channel_type` varchar(20) NOT NULL COMMENT '通道类型: QW/WX/IM',
  `channel_user_id` varchar(200) DEFAULT NULL COMMENT '通道用户ID',
  `extra` json DEFAULT NULL COMMENT '扩展信息',
  `del_flag` tinyint DEFAULT 0,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_contact_channel` (`company_id`, `contact_id`, `channel_type`),
  KEY `idx_company` (`company_id`),
  KEY `idx_channel_type` (`channel_type`),
  KEY `idx_channel_user` (`channel_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='龙虾引擎-统一联系人';

-- 6. 工作流实例表增加通道类型字段
ALTER TABLE `lobster_workflow_instance` ADD COLUMN `channel_type` varchar(20) DEFAULT 'QW' COMMENT '消息通道类型: QW/WX/IM' AFTER `contact_name`;

-- 7. 工作流节点表增加通道配置
ALTER TABLE `company_workflow_lobster_node` ADD COLUMN `channel_config` json DEFAULT NULL COMMENT '通道配置(可覆盖工作流默认通道)' AFTER `node_config`;

-- 8. 为已有表添加索引
CREATE INDEX IF NOT EXISTS `idx_evolution_company_node` ON `lobster_evolution_log` (`company_id`, `node_code`);
CREATE INDEX IF NOT EXISTS `idx_heartbeat_active` ON `lobster_heartbeat_registry` (`status`, `last_heartbeat`);