-- 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, 'Agent' 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, 'Agent' 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, 'Agent' 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, 'Agent' 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, 'Agent' 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);
|
