CREATE TABLE IF NOT EXISTS chat_messages (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  message_id CHAR(36) NOT NULL COMMENT '消息UUID，幂等键',
  thread_id CHAR(36) NOT NULL COMMENT '前端会话UUID',
  trace_id CHAR(36) NOT NULL COMMENT '本轮Agent链路UUID',
  parent_message_id CHAR(36) NULL COMMENT '关联的用户消息UUID',
  user_id BIGINT NOT NULL COMMENT '后台用户ID(sys_user.id)',
  role VARCHAR(16) NOT NULL COMMENT 'user或assistant',
  content LONGTEXT NOT NULL COMMENT '前端渲染内容',
  status VARCHAR(16) NOT NULL DEFAULT 'completed' COMMENT 'completed/failed/interrupted',
  occurred_at DATETIME(3) NOT NULL COMMENT '消息在Python侧产生时间',
  create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '入库时间',
  UNIQUE KEY uk_chat_message_id (message_id),
  KEY idx_chat_user_thread_time (user_id, thread_id, occurred_at, id),
  KEY idx_chat_trace (trace_id),
  CONSTRAINT chk_chat_role CHECK (role IN ('user', 'assistant')),
  CONSTRAINT chk_chat_status CHECK (status IN ('completed', 'failed', 'interrupted'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Agent前端聊天消息';

CREATE TABLE IF NOT EXISTS agent_intent_log (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  intent_log_id CHAR(36) NOT NULL COMMENT '对外意图日志UUID',
  trace_id CHAR(36) NOT NULL COMMENT '本轮Agent链路UUID',
  thread_id CHAR(36) NOT NULL COMMENT '前端会话UUID',
  message_id CHAR(36) NOT NULL COMMENT '关联用户消息UUID',
  user_id BIGINT NOT NULL COMMENT '后台用户ID(sys_user.id)',
  intent VARCHAR(32) NOT NULL COMMENT 'chitchat/order_query',
  confidence DECIMAL(5,4) NOT NULL COMMENT '置信度0到1',
  classifier_source VARCHAR(32) NOT NULL COMMENT 'LLM/RULE_FALLBACK',
  model_name VARCHAR(128) NULL COMMENT '分类模型名称',
  duration_ms BIGINT NULL COMMENT '分类耗时毫秒',
  status VARCHAR(16) NOT NULL DEFAULT 'success' COMMENT 'success/failed',
  error_message VARCHAR(1000) NULL COMMENT '失败摘要，不保存用户原文',
  create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  UNIQUE KEY uk_agent_intent_log_id (intent_log_id),
  UNIQUE KEY uk_agent_intent_message_user (message_id, user_id),
  KEY idx_agent_intent_trace (trace_id),
  KEY idx_agent_intent_thread_time (user_id, thread_id, create_time),
  CONSTRAINT chk_agent_intent CHECK (intent IN ('chitchat', 'order_query')),
  CONSTRAINT chk_agent_intent_source CHECK (classifier_source IN ('LLM', 'RULE_FALLBACK')),
  CONSTRAINT chk_agent_intent_confidence CHECK (confidence >= 0 AND confidence <= 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Agent意图识别日志';