-- AI数据分析报告
CREATE TABLE IF NOT EXISTS sys_ai_data_analysis_report (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  period_type VARCHAR(20) NOT NULL COMMENT 'TODAY/YESTERDAY/THIS_WEEK/THIS_MONTH',
  period_start DATETIME COMMENT '分析周期开始时间',
  period_end DATETIME COMMENT '分析周期结束时间',
  trigger_type VARCHAR(20) NOT NULL COMMENT 'auto/manual',
  status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'pending/running/success/failed',
  summary TEXT COMMENT 'LLM生成的自然语言分析报告',
  structured_data LONGTEXT COMMENT 'JSON格式的结构化分析数据',
  llm_call_count INT DEFAULT 0,
  prompt_tokens INT DEFAULT 0,
  completion_tokens INT DEFAULT 0,
  total_tokens INT DEFAULT 0,
  error_message VARCHAR(1024),
  local_file_path VARCHAR(512) COMMENT '本地存储文件路径',
  upload_status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/uploaded/failed/skipped',
  create_time DATETIME NOT NULL,
  finish_time DATETIME,
  INDEX idx_period_type (period_type),
  INDEX idx_trigger_type (trigger_type),
  INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI数据分析报告';

-- AI数据分析报告上传日志
CREATE TABLE IF NOT EXISTS sys_ai_data_analysis_upload_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  report_id BIGINT NOT NULL COMMENT '关联报告ID',
  upload_url VARCHAR(512),
  request_body TEXT,
  response_body TEXT,
  http_status INT,
  result VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'success/failed',
  error_message VARCHAR(1024),
  retry_count INT DEFAULT 0,
  create_time DATETIME NOT NULL,
  INDEX idx_report_id (report_id),
  INDEX idx_result (result)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据分析报告上传日志';

-- 配置项
INSERT INTO sys_config(name, code, value, type, status, select_type)
SELECT 'AI数据分析功能开关', 'aiDataAnalysisEnabled', '0', 1, 1, 'develop' FROM dual
WHERE NOT EXISTS (SELECT 1 FROM sys_config WHERE code = 'aiDataAnalysisEnabled');

INSERT INTO sys_config(name, code, value, type, status, select_type)
SELECT 'AI数据分析定时周期', 'aiDataAnalysisScheduledPeriods', 'YESTERDAY', 1, 1, 'develop' FROM dual
WHERE NOT EXISTS (SELECT 1 FROM sys_config WHERE code = 'aiDataAnalysisScheduledPeriods');

INSERT INTO sys_config(name, code, value, type, status, select_type)
SELECT 'AI数据分析定时Cron', 'aiDataAnalysisCron', '0 0 1 * * ?', 1, 1, 'develop' FROM dual
WHERE NOT EXISTS (SELECT 1 FROM sys_config WHERE code = 'aiDataAnalysisCron');

INSERT INTO sys_config(name, code, value, type, status, select_type)
SELECT 'AI数据分析上传地址', 'aiDataAnalysisUploadUrl', '', 1, 1, 'develop' FROM dual
WHERE NOT EXISTS (SELECT 1 FROM sys_config WHERE code = 'aiDataAnalysisUploadUrl');

INSERT INTO sys_config(name, code, value, type, status, select_type)
SELECT 'AI数据分析上传开关', 'aiDataAnalysisUploadEnabled', '0', 1, 1, 'develop' FROM dual
WHERE NOT EXISTS (SELECT 1 FROM sys_config WHERE code = 'aiDataAnalysisUploadEnabled');

-- 菜单：AI管理 -> AI数据分析
-- 执行后请在"角色授权"里给对应角色勾选 AI管理 -> AI数据分析。
SET @ai_manage_id := COALESCE(
  (
    SELECT id
    FROM sys_resource
    WHERE code = 'aiManage' AND level = 1
    ORDER BY id
    LIMIT 1
  ),
  (
    SELECT id
    FROM sys_resource
    WHERE name = 'AI管理' AND level = 1
    ORDER BY id
    LIMIT 1
  )
);

INSERT INTO sys_resource(code, name, resource_id, level, sort, status)
SELECT 'ai/data_analysis.html', 'AI数据分析', @ai_manage_id, 2, 5, 1
FROM dual
WHERE @ai_manage_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM sys_resource
    WHERE code = 'ai/data_analysis.html' AND level = 2
  );

UPDATE sys_resource
SET name = 'AI数据分析',
    resource_id = @ai_manage_id,
    level = 2,
    sort = 5,
    status = 1
WHERE code = 'ai/data_analysis.html' AND level = 2;

SET @ai_data_analysis_id := (
  SELECT id
  FROM sys_resource
  WHERE code = 'ai/data_analysis.html' AND level = 2
  ORDER BY id
  LIMIT 1
);

INSERT INTO sys_resource(code, name, resource_id, level, sort, status)
SELECT 'ai/data_analysis.html#view', '查看', @ai_data_analysis_id, 3, 1, 1
FROM dual
WHERE @ai_data_analysis_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM sys_resource
    WHERE code = 'ai/data_analysis.html#view' AND level = 3
  );

UPDATE sys_resource
SET name = '查看',
    resource_id = @ai_data_analysis_id,
    level = 3,
    sort = 1,
    status = 1
WHERE code = 'ai/data_analysis.html#view' AND level = 3;

-- 验证菜单创建结果
SELECT id, code, name, resource_id, level, sort, status
FROM sys_resource
WHERE code IN (
  'ai/data_analysis.html',
  'ai/data_analysis.html#view'
)
ORDER BY level, sort, id;

-- AI累计Token使用统计（独立存储，不受历史记录删除影响）
CREATE TABLE IF NOT EXISTS sys_ai_token_usage (
  id INT PRIMARY KEY DEFAULT 1,
  prompt_tokens BIGINT NOT NULL DEFAULT 0,
  completion_tokens BIGINT NOT NULL DEFAULT 0,
  total_tokens BIGINT NOT NULL DEFAULT 0,
  llm_call_count BIGINT NOT NULL DEFAULT 0,
  update_time DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI累计Token使用统计';

INSERT INTO sys_ai_token_usage (id, prompt_tokens, completion_tokens, total_tokens, llm_call_count, update_time)
SELECT 1, 0, 0, 0, 0, NOW() FROM dual
WHERE NOT EXISTS (SELECT 1 FROM sys_ai_token_usage WHERE id = 1);