huimv-baqing/sql/biz_consult_session.sql

-- 问诊会话（兽医在线接诊 / AI诊断 / 专家问诊共用）
CREATE TABLE IF NOT EXISTS `biz_consult_session` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `consult_type` tinyint(4) NOT NULL COMMENT '1兽医在线2AI3专家',
  `asker_user_id` bigint(20) NOT NULL COMMENT '问诊人/提问人用户ID',
  `asker_name` varchar(64) NOT NULL COMMENT '问诊人姓名',
  `receiver_user_id` bigint(20) DEFAULT NULL COMMENT '接诊人用户ID',
  `receiver_provider_id` bigint(20) DEFAULT NULL COMMENT '兽医/专家资源ID',
  `receiver_name` varchar(64) DEFAULT NULL COMMENT '接诊人姓名',
  `session_title` varchar(200) DEFAULT NULL COMMENT '问题/问答标题',
  `real_session_id` varchar(64) DEFAULT NULL COMMENT '实际会话ID(session_id)',
  `last_message_time` datetime DEFAULT NULL COMMENT '最后消息时间',
  `last_message_preview` varchar(500) DEFAULT NULL COMMENT '列表消息摘要',
  `vet_visible` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1兽医可见0牧民删除后隐藏',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_vet_session` (`consult_type`,`asker_user_id`,`receiver_provider_id`),
  KEY `idx_receiver` (`consult_type`,`receiver_user_id`,`receiver_provider_id`,`last_message_time`),
  KEY `idx_asker` (`consult_type`,`asker_user_id`,`last_message_time`),
  KEY `idx_vet_list` (`consult_type`,`vet_visible`,`receiver_provider_id`,`last_message_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='问诊会话';