-- 【腾讯文档】报告说明接入AI数据表设计
-- https://docs.qq.com/mind/DTG9nSHdoUWtUYVFQ?mode=mind

-- 1. ai_providers（API接口管理表）
-- 作用： 维护 AI 提供商，如 deepseek、GPT-4，并支持动态切换。
CREATE TABLE `ai_providers` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `name` VARCHAR(255) NOT NULL COMMENT '接口名称',
    `api_url` VARCHAR(500) NOT NULL COMMENT 'API URL',
    `api_key` VARCHAR(500) NOT NULL COMMENT 'API Key（加密存储）',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT '是否启用',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `created_by` BIGINT NULL COMMENT '创建人',
    `updated_by` BIGINT NULL COMMENT '更新人',
    PRIMARY KEY (`id`)
);

-- 2. ai_sensitive_words（敏感词管理表）
-- 作用： 维护违禁词，防止用户输入违规内容。
CREATE TABLE `ai_sensitive_words` (
      `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
      `word` VARCHAR(255) NOT NULL COMMENT '敏感词',
      `is_active` BOOLEAN DEFAULT TRUE COMMENT '是否启用',
      `replacement` VARCHAR(255) NULL COMMENT '替换词（可选）',
      `is_blocked` BOOLEAN DEFAULT TRUE COMMENT '是否禁止请求 AI（TRUE=拦截）',
      `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
      `created_by` BIGINT NULL COMMENT '创建人',
      `updated_by` BIGINT NULL COMMENT '更新人',
      PRIMARY KEY (`id`)
);

-- 3. ai_suggested_keywords（联想词管理表）
-- 作用： 记录关键词与其联想词，便于自动匹配。
-- 维护关键词的双向关系，并关联唯一答案。
-- ✅ 支持逻辑：
-- 当用户输入 “机器学习”，先查 ai_suggested_keywords，发现它关联 “人工智能”。
-- 如果 AI 之前已回答过“人工智能”，直接返回数据库答案。
-- 默认只联想一次，如果两次都来自联想，不再请求 AI。
CREATE TABLE `ai_suggested_keywords` (
     `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
     `keyword1` VARCHAR(255) NOT NULL COMMENT '关键词A',
     `keyword2` VARCHAR(255) NOT NULL COMMENT '关键词B（与A相互关联）',
     `answer_id` BIGINT NOT NULL COMMENT '关联的答案ID',
     `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
     `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
     `created_by` BIGINT NULL COMMENT '创建人',
     `updated_by` BIGINT NULL COMMENT '更新人',
     PRIMARY KEY (`id`),
     FOREIGN KEY (`answer_id`) REFERENCES `ai_answers`(`id`) ON DELETE CASCADE
);

-- 4. ai_answers（答案存储表）
-- 作用： 存储唯一问题的唯一答案，避免冗余。
CREATE TABLE `ai_answers` (
      `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
      `question` TEXT NOT NULL COMMENT '标准化问题',
      `answer` TEXT NOT NULL COMMENT 'AI 生成的或用户维护的答案',
      `is_user_editable` BOOLEAN DEFAULT FALSE COMMENT '是否用户可编辑',
      `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
      `created_by` BIGINT NULL COMMENT '创建人',
      `updated_by` BIGINT NULL COMMENT '更新人',
      PRIMARY KEY (`id`)
);

-- 5. ai_settings（用户设置表）
-- 作用： 记录用户 AI 偏好和免责协议状态。
CREATE TABLE `ai_settings` (
       `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
       `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
       `use_ai_memory` BOOLEAN DEFAULT TRUE COMMENT '是否开启记忆功能（全局默认）',
       `agreed_disclaimer` BOOLEAN DEFAULT FALSE COMMENT '是否同意免责协议',
       `identity_keywords` TEXT COMMENT '身份关键词（从用户会话提取）',
       `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
       `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
       `created_by` BIGINT NULL COMMENT '创建人',
       `updated_by` BIGINT NULL COMMENT '更新人',
       PRIMARY KEY (`id`),
       INDEX (`user_id`),  -- 外键列添加索引，提升查询性能
       FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. ai_chat_sessions（会话主表）
-- 作用： 记录用户的对话，支持 项目/报告关联，管理记忆功能。并归属到特定的 AI 提供商。
-- ✅ 确保对话受限于项目/报告
-- 如果 project_id 存在 → 对话仅属于该项目
-- 如果 report_id 存在 → 对话仅属于该报告
--
-- ✅ 新增 ai_provider_id
-- 记录该对话使用的 AI（DeepSeek、GPT-4）
-- 支持 AI 切换（不同会话可使用不同 AI）
CREATE TABLE `ai_chat_sessions` (
        `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
        `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
        `session_id` VARCHAR(64) NOT NULL COMMENT '会话唯一标识',
        `ai_provider_id` BIGINT NOT NULL COMMENT '调用的 AI 提供商',
        `memory_enabled` BOOLEAN DEFAULT TRUE COMMENT '是否开启记忆（会话级别）',
        `project_id` BIGINT NULL COMMENT '关联的项目ID',
        `report_id` BIGINT NULL COMMENT '关联的报告ID',
        `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        `created_by` BIGINT NULL COMMENT '创建人',
        `updated_by` BIGINT NULL COMMENT '更新人',
        PRIMARY KEY (`id`),
        FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE,
        FOREIGN KEY (`ai_provider_id`) REFERENCES `ai_providers`(`id`) ON DELETE CASCADE
);

-- 7. ai_messages（消息表）
-- 作用： 记录对话内容，支持 联想词匹配 & 记忆功能。
-- ✅ 逻辑优化
-- is_suggested_match = TRUE → 说明该问题是从联想词中选出的，不会被记忆
-- 两次问题都 is_suggested_match = TRUE → 直接返回数据库答案，不请求 AI
CREATE TABLE `ai_messages` (
       `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
       `session_id` BIGINT NOT NULL COMMENT '会话ID',
       `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
       `message` TEXT NOT NULL COMMENT '用户输入的内容',
       `response` TEXT COMMENT 'AI的回复',
       `is_memory_saved` BOOLEAN DEFAULT FALSE COMMENT '是否作为记忆存储',
       `is_suggested_match` BOOLEAN DEFAULT FALSE COMMENT '是否来自联想匹配',
       `answer_id` BIGINT NULL COMMENT '关联的答案ID（如果该消息生成了可记忆的答案）',
       `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
       `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
       `created_by` BIGINT NULL COMMENT '创建人',
       `updated_by` BIGINT NULL COMMENT '更新人',
       PRIMARY KEY (`id`),
       FOREIGN KEY (`session_id`) REFERENCES `ai_chat_sessions`(`id`) ON DELETE CASCADE,
       FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE,
       FOREIGN KEY (`answer_id`) REFERENCES `ai_answers`(`id`) ON DELETE SET NULL
);

-- 8. ai_usage_logs（AI 调用记录）
-- 作用： 记录 AI Token 消耗及成本。
CREATE TABLE `ai_usage_logs` (
     `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
     `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
     `session_id` BIGINT NOT NULL COMMENT '会话ID',
     `ai_provider_id` BIGINT NOT NULL COMMENT '调用的 AI 接口 ID',
     `request_text` TEXT NOT NULL COMMENT '请求的内容',
     `response_text` TEXT COMMENT 'AI 返回的内容',
     `tokens_used` INT DEFAULT 0 COMMENT '消耗的 Token 数',
     `cost_usd` DECIMAL(10,5) DEFAULT 0.00000 COMMENT '本次请求的成本（美元）',
     `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '请求时间',
     `created_by` BIGINT NULL COMMENT '创建人',
     PRIMARY KEY (`id`),
     FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE,
     FOREIGN KEY (`session_id`) REFERENCES `ai_chat_sessions`(`id`) ON DELETE CASCADE,
     FOREIGN KEY (`ai_provider_id`) REFERENCES `ai_providers`(`id`) ON DELETE CASCADE
);

-- 9.ai_user_token_usage（用户 Token 总消耗表）
-- 作用： 记录 用户在不同 AI 提供商的总 Token 消耗，避免查询时重复计算。
-- 每个用户在每个 AI 提供商下 只占 一条记录。
-- 避免查询时扫描 ai_usage_logs，提高效率。
-- 实时更新 total_tokens_used，保证 Token 统计准确。
CREATE TABLE `ai_user_token_usage` (
       `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
       `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
       `ai_provider_id` BIGINT NOT NULL COMMENT 'AI 提供商 ID',
       `total_tokens_used` BIGINT DEFAULT 0 COMMENT '累计消耗的 Token 数',
       `total_cost_usd` DECIMAL(10,5) DEFAULT 0.00000 COMMENT '累计消耗的成本（美元）',
       `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新日期',
       `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
       PRIMARY KEY (`id`),
       UNIQUE KEY `user_ai_provider` (`user_id`, `ai_provider_id`),
       FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE,
       FOREIGN KEY (`ai_provider_id`) REFERENCES `ai_providers`(`id`) ON DELETE CASCADE
);

-- 触发器 update_ai_user_token_usage（自动更新 Token 统计）
-- 作用：
-- 每次 AI 交互后，自动更新 user_token_usage
-- 保证用户总消耗的 Token 数实时同步
-- 自动更新 total_tokens_used，保证数据实时同步。
-- 如果 user_token_usage 记录不存在，则自动创建新记录。
DELIMITER //
CREATE TRIGGER update_ai_user_token_usage
    AFTER INSERT ON ai_usage_logs
    FOR EACH ROW
BEGIN
    INSERT INTO ai_user_token_usage (user_id, ai_provider_id, total_tokens_used, total_cost_usd, updated_at, created_at)
    VALUES (NEW.user_id, NEW.ai_provider_id, NEW.tokens_used, NEW.cost_usd, NOW(), NOW())
    ON DUPLICATE KEY UPDATE
         total_tokens_used = total_tokens_used + NEW.tokens_used,
         total_cost_usd = total_cost_usd + NEW.cost_usd,
         updated_at = NOW();
END;
//
DELIMITER ;

-- 备用，用来生成模型的
-- ./yii gii/model --db=db --generateLabelsFromComments=1 --modelClass=Aiproviders --ns=app\\modules\\api\\modules\\v1\\models --tableName=ai_providers --color=1
-- ./yii gii/model --db=db --generateLabelsFromComments=1 --modelClass=Aisensitivewords --ns=app\\modules\\api\\modules\\v1\\models --tableName=ai_sensitive_words --color=1
-- ./yii gii/model --db=db --generateLabelsFromComments=1 --modelClass=Aisuggestedkeywords --ns=app\\modules\\api\\modules\\v1\\models --tableName=ai_suggested_keywords --color=1
-- ./yii gii/model --db=db --generateLabelsFromComments=1 --modelClass=Aianswers --ns=app\\modules\\api\\modules\\v1\\models --tableName=ai_answers --color=1
-- ./yii gii/model --db=db --generateLabelsFromComments=1 --modelClass=Aisettings --ns=app\\modules\\api\\modules\\v1\\models --tableName=ai_settings --color=1
-- ./yii gii/model --db=db --generateLabelsFromComments=1 --modelClass=Aichatsessions --ns=app\\modules\\api\\modules\\v1\\models --tableName=ai_chat_sessions --color=1
-- ./yii gii/model --db=db --generateLabelsFromComments=1 --modelClass=Aimessages --ns=app\\modules\\api\\modules\\v1\\models --tableName=ai_messages --color=1
-- ./yii gii/model --db=db --generateLabelsFromComments=1 --modelClass=Aiusagelogs --ns=app\\modules\\api\\modules\\v1\\models --tableName=ai_usage_logs --color=1
-- ./yii gii/model --db=db --generateLabelsFromComments=1 --modelClass=Aiusertokenusage --ns=app\\modules\\api\\modules\\v1\\models --tableName=ai_user_token_usage --color=1