emoon-backend/script/sql/ai-terminal-mvp.sql

-- ============================================================
-- 医梦 AI 中台 统一入口客户端 MVP 数据库迁移
-- 版本: 2026-05-31
-- 原则: 不 DROP 现有表，仅 ALTER 补字段 + CREATE 新表
-- ============================================================

-- -----------------------------------------------------------
-- 1. 新表：设备注册中心
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_device_registry (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  device_id VARCHAR(64) NOT NULL,
  project_id VARCHAR(64) NOT NULL,
  hospital_id VARCHAR(64) NOT NULL,
  device_type VARCHAR(64) NOT NULL,
  management_mode VARCHAR(32) NOT NULL DEFAULT 'emoon_managed',
  vendor VARCHAR(64),
  model VARCHAR(128),
  location_json JSON,
  capabilities_json JSON,
  status VARCHAR(32) NOT NULL DEFAULT 'pending',
  admission_level VARCHAR(8) NOT NULL DEFAULT 'B',
  client_version VARCHAR(64),
  last_heartbeat DATETIME,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_device_id (device_id),
  KEY idx_project_hospital (project_id, hospital_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- 2. 新表：设备场景绑定
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_device_scene_binding (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  device_id VARCHAR(64) NOT NULL,
  scene_code VARCHAR(64) NOT NULL,
  ui_template VARCHAR(64) NOT NULL,
  agent_bindings_json JSON NOT NULL,
  tool_scopes_json JSON,
  card_scopes_json JSON,
  status VARCHAR(32) NOT NULL DEFAULT 'enabled',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_device_scene (device_id, scene_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- 3. 新表：设备事件日志
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_device_event (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  event_id VARCHAR(96) NOT NULL,
  device_id VARCHAR(64) NOT NULL,
  project_id VARCHAR(64) NOT NULL COMMENT '项目标识，用于多租户隔离和审计',
  event_type VARCHAR(64) NOT NULL,
  event_payload JSON,
  trace_id VARCHAR(96),
  occurred_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_device_event (device_id, event_id),
  KEY idx_device_time (device_id, occurred_at),
  KEY idx_project (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- 4. 新表：设备命令
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_device_command (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  command_id VARCHAR(96) NOT NULL,
  device_id VARCHAR(64) NOT NULL,
  command_type VARCHAR(64) NOT NULL,
  payload_json JSON NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
  expire_at DATETIME NOT NULL,
  ack_at DATETIME,
  ack_result_json JSON,
  trace_id VARCHAR(96),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_command_id (command_id),
  KEY idx_device_status (device_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- 5. 新表：会话消息
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_conversation_message (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  message_id VARCHAR(96) NOT NULL,
  conversation_id VARCHAR(96) NOT NULL,
  role VARCHAR(32) NOT NULL,
  content TEXT,
  event_json JSON,
  trace_id VARCHAR(96),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_message_id (message_id),
  KEY idx_conv_time (conversation_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- 6. 新表：任务实例
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_task_instance (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  task_id VARCHAR(96) NOT NULL,
  project_id VARCHAR(64) NOT NULL,
  conversation_id VARCHAR(96) NOT NULL,
  task_type VARCHAR(64) NOT NULL,
  status VARCHAR(32) NOT NULL,
  current_step VARCHAR(64) NOT NULL,
  agent_code VARCHAR(64),
  context_json JSON,
  patient_id VARCHAR(96),
  device_id VARCHAR(64),
  trace_id VARCHAR(96),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_task_id (task_id),
  KEY idx_project (project_id),
  KEY idx_conv_status (conversation_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- 7. 新表：文件对象
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_file_object (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  file_id VARCHAR(96) NOT NULL,
  project_id VARCHAR(64) NOT NULL,
  hospital_id VARCHAR(64),
  device_id VARCHAR(64),
  business_type VARCHAR(64) NOT NULL,
  sha256 VARCHAR(128),
  storage_path VARCHAR(512) NOT NULL,
  retention_policy VARCHAR(32) NOT NULL,
  sensitive_level VARCHAR(32) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'available',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_file_id (file_id),
  KEY idx_project_type (project_id, business_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- 7.5 新表：卡片动作日志（幂等审计）
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_card_action_log (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  action_id VARCHAR(96) NOT NULL,
  card_instance_id VARCHAR(96) NOT NULL,
  action_name VARCHAR(64) NOT NULL,
  idempotency_key VARCHAR(128) NOT NULL,
  action_payload_json JSON,
  status VARCHAR(32) NOT NULL DEFAULT 'submitted',
  trace_id VARCHAR(96),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_idempotency (idempotency_key),
  KEY idx_card_action (card_instance_id, action_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- 8. 存量表 ai_conversation 补字段
--    现有实体: com.emoon.mcp.domain.AiConversation
--    已有字段: id, projectId, agentId, conversationId, status 等
-- -----------------------------------------------------------
DROP PROCEDURE IF EXISTS emoon_add_column_if_absent;
DELIMITER //
CREATE PROCEDURE emoon_add_column_if_absent(
  IN p_table_name VARCHAR(128),
  IN p_column_name VARCHAR(128),
  IN p_ddl TEXT
)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = p_table_name
      AND column_name = p_column_name
  ) THEN
    SET @sql = p_ddl;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS emoon_add_index_if_absent;
DELIMITER //
CREATE PROCEDURE emoon_add_index_if_absent(
  IN p_table_name VARCHAR(128),
  IN p_index_name VARCHAR(128),
  IN p_ddl TEXT
)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = p_table_name
      AND index_name = p_index_name
  ) THEN
    SET @sql = p_ddl;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END//
DELIMITER ;

CALL emoon_add_column_if_absent('ai_conversation', 'hospital_id',
  'ALTER TABLE ai_conversation ADD COLUMN hospital_id VARCHAR(64) NULL COMMENT ''医院标识''');
CALL emoon_add_column_if_absent('ai_conversation', 'device_id',
  'ALTER TABLE ai_conversation ADD COLUMN device_id VARCHAR(64) NULL COMMENT ''统一入口终端设备ID''');
CALL emoon_add_column_if_absent('ai_conversation', 'client_message_id',
  'ALTER TABLE ai_conversation ADD COLUMN client_message_id VARCHAR(96) NULL COMMENT ''客户端消息幂等ID''');
CALL emoon_add_index_if_absent('ai_conversation', 'idx_ai_conversation_device',
  'CREATE INDEX idx_ai_conversation_device ON ai_conversation(device_id)');

-- -----------------------------------------------------------
-- 9. 存量表 ai_card_instance 补字段
--    现有实体: com.emoon.mcp.domain.AiCardInstance
--    已有字段: id, conversationId, cardKey, instanceId, status 等
-- -----------------------------------------------------------
CALL emoon_add_column_if_absent('ai_card_instance', 'task_id',
  'ALTER TABLE ai_card_instance ADD COLUMN task_id VARCHAR(96) NULL COMMENT ''统一入口终端任务ID''');
CALL emoon_add_column_if_absent('ai_card_instance', 'trace_id',
  'ALTER TABLE ai_card_instance ADD COLUMN trace_id VARCHAR(96) NULL COMMENT ''链路追踪ID''');
CALL emoon_add_index_if_absent('ai_card_instance', 'idx_ai_card_instance_task',
  'CREATE INDEX idx_ai_card_instance_task ON ai_card_instance(task_id)');

-- -----------------------------------------------------------
-- 10. 存量表 ai_card_definition 补索引
--     现有实体: com.emoon.system.domain.AiCardDefinition
--     已有字段: cardKey, version, name, schemaJson, actionsJson, status, isSystem, isLatest
-- -----------------------------------------------------------
CALL emoon_add_index_if_absent('ai_card_definition', 'idx_ai_card_def_key',
  'CREATE INDEX idx_ai_card_def_key ON ai_card_definition(card_key)');

DROP PROCEDURE IF EXISTS emoon_add_column_if_absent;
DROP PROCEDURE IF EXISTS emoon_add_index_if_absent;

-- -----------------------------------------------------------
-- 11. Seed 数据：核心卡片定义
-- -----------------------------------------------------------
INSERT INTO ai_card_definition(card_key, version, name, schema_json, actions_json, status, is_system, is_latest)
VALUES
('department-selection', '1.0', '科室选择', JSON_OBJECT('required', JSON_ARRAY('departments')), JSON_ARRAY(JSON_OBJECT('actionName', 'select_department')), '0', '1', TRUE),
('doctor-selection', '1.0', '医生选择', JSON_OBJECT('required', JSON_ARRAY('doctors')), JSON_ARRAY(JSON_OBJECT('actionName', 'select_doctor')), '0', '1', TRUE),
('time-slot-selection', '1.0', '号源选择', JSON_OBJECT('required', JSON_ARRAY('slots')), JSON_ARRAY(JSON_OBJECT('actionName', 'select_time_slot')), '0', '1', TRUE),
('confirm-appointment', '1.0', '挂号确认', JSON_OBJECT('required', JSON_ARRAY('summary')), JSON_ARRAY(JSON_OBJECT('actionName', 'confirm_appointment', 'requiredConfirm', TRUE)), '0', '1', TRUE),
('appointment-success', '1.0', '挂号成功', JSON_OBJECT('required', JSON_ARRAY('appointmentNo')), JSON_ARRAY(), '0', '1', TRUE),
('route-card', '1.0', '路线导航', JSON_OBJECT('required', JSON_ARRAY('routeText')), JSON_ARRAY(JSON_OBJECT('actionName', 'start_navigation')), '0', '1', TRUE),
('tongue-capture', '1.0', '舌象采集', JSON_OBJECT('required', JSON_ARRAY('uploadField')), JSON_ARRAY(JSON_OBJECT('actionName', 'submit_tongue_image')), '0', '1', TRUE),
('tongue-diagnosis-result', '1.0', '舌诊结果', JSON_OBJECT('required', JSON_ARRAY('summary')), JSON_ARRAY(), '0', '1', TRUE)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  schema_json = VALUES(schema_json),
  actions_json = VALUES(actions_json),
  status = VALUES(status),
  is_system = VALUES(is_system),
  is_latest = VALUES(is_latest);

-- -----------------------------------------------------------
-- 12. Seed 数据：自助机设备和场景
-- -----------------------------------------------------------
INSERT IGNORE INTO ai_device_registry(device_id, project_id, hospital_id, device_type, status, admission_level, capabilities_json)
VALUES ('EMOON-KIOSK-001', 'hospital_demo', 'H001', 'self_service_kiosk', 'activated', 'A',
        JSON_ARRAY('touch','camera','id_card_ocr','file_upload'));

INSERT IGNORE INTO ai_device_scene_binding(device_id, scene_code, ui_template, agent_bindings_json, card_scopes_json)
VALUES ('EMOON-KIOSK-001', 'outpatient_kiosk', 'kiosk_home_v1',
        JSON_OBJECT('defaultAgent','opd-guide-agent','allowedAgents',JSON_ARRAY('opd-guide-agent','opd-triage-agent','opd-registration-agent','tongue-diagnosis-agent')),
        JSON_ARRAY('department-selection','doctor-selection','time-slot-selection','confirm-appointment','appointment-success','tongue-capture','tongue-diagnosis-result'));

-- -----------------------------------------------------------
-- 迁移补丁：为已执行旧版脚本的环境补 project_id
-- 如果列不存在则添加，存在则跳过（MySQL 5.7+ 不支持 IF NOT EXISTS 语法，
-- 使用 information_schema 预检）
-- -----------------------------------------------------------
SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'ai_device_event'
      AND COLUMN_NAME = 'project_id');

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE ai_device_event ADD COLUMN project_id VARCHAR(64) NOT NULL DEFAULT '''' COMMENT ''项目标识'' AFTER device_id',
    'SELECT ''ai_device_event.project_id already exists, skipping'' AS msg');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 补索引（幂等：MySQL 8.0+ CREATE INDEX IF NOT EXISTS，低版本靠 PREPARE 预检）
SET @idx_exists = (SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'ai_device_event'
      AND INDEX_NAME = 'idx_project');

SET @sql_idx = IF(@idx_exists = 0,
    'CREATE INDEX idx_project ON ai_device_event(project_id)',
    'SELECT ''ai_device_event.idx_project already exists, skipping'' AS msg');

PREPARE stmt_idx FROM @sql_idx;
EXECUTE stmt_idx;
DEALLOCATE PREPARE stmt_idx;