-- 015_skill_analytics.sql
-- P2-2: 技能执行日志云端表 + 事件离线队列
-- 对应客户端 SQLite 表：skill_execution_logs, skill_event_queue

-- ========== 技能执行日志表 ==========
CREATE TABLE IF NOT EXISTS `tw_skill_execution_logs` (
  `id` VARCHAR(36) NOT NULL COMMENT '本地记录 UUID',
  `client_event_id` VARCHAR(255) NOT NULL COMMENT '幂等去重 key（client 生成）',
  `conversation_id` VARCHAR(255) NOT NULL,
  `avatar_id` VARCHAR(255) DEFAULT NULL,
  `sub_skill_id` VARCHAR(255) DEFAULT NULL,
  `skill_name` VARCHAR(255) NOT NULL,
  `step_index` INT DEFAULT NULL,

  -- 执行状态
  `status` VARCHAR(20) NOT NULL DEFAULT 'pending'
    COMMENT 'pending/running/completed/failed/skipped/timeout',
  `started_at` BIGINT NOT NULL,
  `duration_ms` INT DEFAULT NULL,

  -- 结构化错误信息
  `error_code` VARCHAR(50) DEFAULT NULL
    COMMENT 'LLM_TIMEOUT/LLM_RATE_LIMIT/TOOL_ERROR/NETWORK/USER_CANCEL/DEPTH_LIMIT/EMPTY_RESPONSE/CONTEXT_OVERFLOW/CYCLE_DETECTED/UNKNOWN',
  `error_message` TEXT DEFAULT NULL,

  -- 工具调用统计
  `tool_call_count` INT DEFAULT 0,
  `tool_names` JSON DEFAULT NULL COMMENT '工具名列表',

  -- 上下文指纹
  `context_hash` VARCHAR(64) DEFAULT NULL,

  -- 嵌套追踪（委派场景）
  `parent_execution_id` VARCHAR(36) DEFAULT NULL,
  `delegate_depth` INT DEFAULT 0,

  -- 匹配元数据
  `match_score` DOUBLE DEFAULT NULL,
  `match_type` VARCHAR(50) DEFAULT NULL COMMENT 'trigger/keyword/condition/alias',
  `matched_terms` JSON DEFAULT NULL,

  -- 事件类型（来自客户端事件管线）
  `event_type` VARCHAR(50) DEFAULT NULL
    COMMENT 'skill_routed/skill_invoked/skill_completed/skill_failed/delegate_started/delegate_completed/delegate_failed',
  `execution_context` VARCHAR(20) DEFAULT 'inline' COMMENT 'inline/fork/delegate',

  -- 终端标识
  `terminal_id` VARCHAR(255) DEFAULT NULL COMMENT '上报终端设备 ID',

  `created_at` BIGINT NOT NULL,
  `updated_at` BIGINT NOT NULL DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000),

  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_client_event_id` (`client_event_id`),
  KEY `idx_skill_name_status` (`skill_name`, `status`),
  KEY `idx_avatar_created` (`avatar_id`, `created_at`),
  KEY `idx_conversation_created` (`conversation_id`, `created_at`),
  KEY `idx_created` (`created_at`),
  KEY `idx_error_code` (`error_code`),
  KEY `idx_event_type` (`event_type`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='P2-2: 技能执行日志（云端聚合）';

-- ========== 事件离线队列（备用，通常客户端本地处理）==========
CREATE TABLE IF NOT EXISTS `tw_skill_event_queue` (
  `id` VARCHAR(36) NOT NULL,
  `event_type` VARCHAR(50) NOT NULL,
  `payload` JSON NOT NULL,
  `terminal_id` VARCHAR(255) DEFAULT NULL,
  `created_at` BIGINT NOT NULL,
  `sync_status` VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/processing/done/failed',
  `retry_count` INT DEFAULT 0,
  `last_error` TEXT DEFAULT NULL,

  PRIMARY KEY (`id`),
  KEY `idx_sync_status_created` (`sync_status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='P2-2: 事件离线队列（备用）';
