/*
 * ----------------------------------------------------------------
 * 迁移脚本: 07_20260812_ai_bots.sql
 * 目的:
 *   AI 机器人功能:
 *   1. users 表增加 is_bot 列(机器人复用 users 表作为虚拟用户,
 *      消息/群成员/会话等现有链路零改造);
 *   2. 新建 ai_configs 表(AI 提供商配置,全局一条,仅管理员 id=1 维护);
 *   3. 新建 ai_bots 表(机器人定义:名称/头像/角色设定,关联虚拟用户ID)。
 *
 * 执行方式:
 *   mysql -u root -p nl_im_plus < migrations/07_20260812_ai_bots.sql
 *
 * 回滚方式:
 *   ALTER TABLE `users` DROP COLUMN `is_bot`;
 *   DROP TABLE IF EXISTS `ai_configs`;
 *   DROP TABLE IF EXISTS `ai_bots`;
 *
 * 本脚本可重复执行:列/表已存在时跳过,不会报错。
 * ----------------------------------------------------------------
 */

SET NAMES utf8mb4;

-- 请按实际库名修改
USE `nl_im_plus`;

-- ============================================================
-- 1. users 表增加 is_bot 列(已存在则跳过)
-- ============================================================
SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'users'
    AND COLUMN_NAME = 'is_bot'
);
SET @sql = IF(
  @col_exists = 0,
  'ALTER TABLE `users` ADD COLUMN `is_bot` tinyint(1) NOT NULL DEFAULT 0 COMMENT ''是否AI机器人'' AFTER `moment_cover`',
  'SELECT ''is_bot column already exists'' AS notice'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================
-- 2. ai_configs AI提供商配置表
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_configs` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `provider` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '提供商标识(openai/deepseek/dashscope/moonshot/ollama/custom)',
  `base_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'API基础地址(空用默认)',
  `api_key` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'API密钥',
  `model` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '模型名称',
  `enabled` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否启用',
  `created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime(3) NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'AI提供商配置表(全局一条,仅管理员维护)' ROW_FORMAT = Dynamic;

-- ============================================================
-- 3. ai_bots 机器人定义表
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_bots` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '关联虚拟用户ID(users.id, bot_前缀)',
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '机器人名称(群聊@名称触发)',
  `avatar` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '机器人头像',
  `role_prompt` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '角色设定(system提示词)',
  `enabled` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否启用',
  `created_at` datetime(3) NULL DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime(3) NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_ai_bots_user_id`(`user_id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'AI机器人定义表' ROW_FORMAT = Dynamic;

SELECT '07_20260812_ai_bots migration finished' AS result;