-- AI 全量迁移聚合脚本
-- 该文件聚合了原有全部 AI 相关迁移 SQL。
-- 若已执行过部分脚本，可重复执行；各段脚本均保持幂等或兼容更新逻辑。

-- >>> 20260311_ai_param.sql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `sys_ai_param` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `uuid` varchar(255) DEFAULT NULL COMMENT '编号',
  `name` varchar(255) DEFAULT NULL COMMENT '名称',
  `model_code` varchar(255) DEFAULT NULL COMMENT '模型编码',
  `provider` varchar(255) DEFAULT NULL COMMENT '供应商',
  `chat_url` varchar(512) DEFAULT NULL COMMENT '聊天地址',
  `api_key` varchar(512) DEFAULT NULL COMMENT 'API密钥',
  `model_name` varchar(255) DEFAULT NULL COMMENT '模型名称',
  `system_prompt` text COMMENT '系统提示词',
  `max_context_messages` int(11) DEFAULT NULL COMMENT '上下文轮数',
  `default_flag` int(1) NOT NULL DEFAULT '0' COMMENT '默认模型{1:是,0:否}',
  `sort` int(11) DEFAULT NULL COMMENT '排序',
  `status` int(1) NOT NULL DEFAULT '1' COMMENT '状态{1:正常,0:冻结}',
  `deleted` int(1) NOT NULL DEFAULT '0' COMMENT '是否删除{1:是,0:否}',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户[sys_tenant]',
  `create_by` bigint(20) DEFAULT NULL COMMENT '添加人员[sys_user]',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '修改人员[sys_user]',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  `memo` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_ai_param_model_code` (`model_code`),
  KEY `idx_ai_param_deleted_code` (`deleted`,`model_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sys_ai_param`
(`uuid`, `name`, `model_code`, `provider`, `chat_url`, `api_key`, `model_name`, `system_prompt`, `max_context_messages`, `default_flag`, `sort`, `status`, `deleted`, `tenant_id`, `create_by`, `create_time`, `update_by`, `update_time`, `memo`)
SELECT '6702082748514305', '通用助手', 'mock-general', 'mock', NULL, NULL, 'mock-general', '你是WMS系统内的智能助手，回答时优先保持准确、简洁，并结合上下文帮助用户理解仓储业务。', 12, 1, 1, 1, 0, 1, 2, NOW(), 2, NOW(), '默认演示模型'
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1
    FROM `sys_ai_param`
    WHERE `model_code` = 'mock-general'
);

INSERT INTO `sys_ai_param`
(`uuid`, `name`, `model_code`, `provider`, `chat_url`, `api_key`, `model_name`, `system_prompt`, `max_context_messages`, `default_flag`, `sort`, `status`, `deleted`, `tenant_id`, `create_by`, `create_time`, `update_by`, `update_time`, `memo`)
SELECT '6702082748514306', '创意助手', 'mock-creative', 'mock', NULL, NULL, 'mock-creative', '你是WMS系统内的智能助手，回答时可以更灵活地组织表达，但结论必须准确。', 12, 0, 2, 1, 0, 1, 2, NOW(), 2, NOW(), '演示创意模型'
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1
    FROM `sys_ai_param`
    WHERE `model_code` = 'mock-creative'
);

SET @ai_parent_menu_id := (
    SELECT `id`
    FROM `sys_menu`
    WHERE `component` = 'aiParam'
    LIMIT 1
);

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'menu.aiParam', 1, 'menu.system', '1', 'menu.system', '/system/aiParam', 'aiParam', NULL, NULL, 0, NULL, 'SmartToy', 9, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL
WHERE @ai_parent_menu_id IS NULL;

SET @ai_parent_menu_id := COALESCE(
    @ai_parent_menu_id,
    LAST_INSERT_ID(),
    (
        SELECT `id`
        FROM `sys_menu`
        WHERE `component` = 'aiParam'
        LIMIT 1
    )
);

SET @ai_query_menu_id := (
    SELECT `id`
    FROM `sys_menu`
    WHERE `parent_id` = @ai_parent_menu_id
      AND `name` = 'Query AiParam'
    LIMIT 1
);

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Query AiParam', @ai_parent_menu_id, NULL, CONCAT('1,', @ai_parent_menu_id), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiParam:list', NULL, 0, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL
WHERE @ai_query_menu_id IS NULL;

SET @ai_query_menu_id := COALESCE(
    @ai_query_menu_id,
    LAST_INSERT_ID(),
    (
        SELECT `id`
        FROM `sys_menu`
        WHERE `parent_id` = @ai_parent_menu_id
          AND `name` = 'Query AiParam'
        LIMIT 1
    )
);

SET @ai_create_menu_id := (
    SELECT `id`
    FROM `sys_menu`
    WHERE `parent_id` = @ai_parent_menu_id
      AND `name` = 'Create AiParam'
    LIMIT 1
);

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Create AiParam', @ai_parent_menu_id, NULL, CONCAT('1,', @ai_parent_menu_id), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiParam:save', NULL, 1, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL
WHERE @ai_create_menu_id IS NULL;

SET @ai_create_menu_id := COALESCE(
    @ai_create_menu_id,
    LAST_INSERT_ID(),
    (
        SELECT `id`
        FROM `sys_menu`
        WHERE `parent_id` = @ai_parent_menu_id
          AND `name` = 'Create AiParam'
        LIMIT 1
    )
);

SET @ai_update_menu_id := (
    SELECT `id`
    FROM `sys_menu`
    WHERE `parent_id` = @ai_parent_menu_id
      AND `name` = 'Update AiParam'
    LIMIT 1
);

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Update AiParam', @ai_parent_menu_id, NULL, CONCAT('1,', @ai_parent_menu_id), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiParam:update', NULL, 2, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL
WHERE @ai_update_menu_id IS NULL;

SET @ai_update_menu_id := COALESCE(
    @ai_update_menu_id,
    LAST_INSERT_ID(),
    (
        SELECT `id`
        FROM `sys_menu`
        WHERE `parent_id` = @ai_parent_menu_id
          AND `name` = 'Update AiParam'
        LIMIT 1
    )
);

SET @ai_delete_menu_id := (
    SELECT `id`
    FROM `sys_menu`
    WHERE `parent_id` = @ai_parent_menu_id
      AND `name` = 'Delete AiParam'
    LIMIT 1
);

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Delete AiParam', @ai_parent_menu_id, NULL, CONCAT('1,', @ai_parent_menu_id), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiParam:remove', NULL, 3, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL
WHERE @ai_delete_menu_id IS NULL;

SET @ai_delete_menu_id := COALESCE(
    @ai_delete_menu_id,
    LAST_INSERT_ID(),
    (
        SELECT `id`
        FROM `sys_menu`
        WHERE `parent_id` = @ai_parent_menu_id
          AND `name` = 'Delete AiParam'
        LIMIT 1
    )
);

SET @ai_export_menu_id := (
    SELECT `id`
    FROM `sys_menu`
    WHERE `parent_id` = @ai_parent_menu_id
      AND `name` = 'Export AiParam'
    LIMIT 1
);

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Export AiParam', @ai_parent_menu_id, NULL, CONCAT('1,', @ai_parent_menu_id), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiParam:list', NULL, 4, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL
WHERE @ai_export_menu_id IS NULL;

SET @ai_export_menu_id := COALESCE(
    @ai_export_menu_id,
    LAST_INSERT_ID(),
    (
        SELECT `id`
        FROM `sys_menu`
        WHERE `parent_id` = @ai_parent_menu_id
          AND `name` = 'Export AiParam'
        LIMIT 1
    )
);

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, @ai_parent_menu_id
FROM DUAL
WHERE @ai_parent_menu_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM `sys_role_menu`
      WHERE `role_id` = 1
        AND `menu_id` = @ai_parent_menu_id
  );

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, @ai_query_menu_id
FROM DUAL
WHERE @ai_query_menu_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM `sys_role_menu`
      WHERE `role_id` = 1
        AND `menu_id` = @ai_query_menu_id
  );

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, @ai_create_menu_id
FROM DUAL
WHERE @ai_create_menu_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM `sys_role_menu`
      WHERE `role_id` = 1
        AND `menu_id` = @ai_create_menu_id
  );

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, @ai_update_menu_id
FROM DUAL
WHERE @ai_update_menu_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM `sys_role_menu`
      WHERE `role_id` = 1
        AND `menu_id` = @ai_update_menu_id
  );

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, @ai_delete_menu_id
FROM DUAL
WHERE @ai_delete_menu_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM `sys_role_menu`
      WHERE `role_id` = 1
        AND `menu_id` = @ai_delete_menu_id
  );

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, @ai_export_menu_id
FROM DUAL
WHERE @ai_export_menu_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM `sys_role_menu`
      WHERE `role_id` = 1
        AND `menu_id` = @ai_export_menu_id
  );

SET FOREIGN_KEY_CHECKS = 1;

-- >>> 20260311_ai_param_menu.sql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

SET @ai_parent_menu_id := (
    SELECT `id`
    FROM `sys_menu`
    WHERE `component` = 'aiParam'
    LIMIT 1
);

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'menu.aiParam', 1, 'menu.system', '1', 'menu.system', '/system/aiParam', 'aiParam', NULL, NULL, 0, NULL, 'SmartToy', 9, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL
WHERE @ai_parent_menu_id IS NULL;

SET @ai_parent_menu_id := COALESCE(
    @ai_parent_menu_id,
    LAST_INSERT_ID(),
    (
        SELECT `id`
        FROM `sys_menu`
        WHERE `component` = 'aiParam'
        LIMIT 1
    )
);

SET @ai_query_menu_id := (
    SELECT `id`
    FROM `sys_menu`
    WHERE `parent_id` = @ai_parent_menu_id
      AND `name` = 'Query AiParam'
    LIMIT 1
);

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Query AiParam', @ai_parent_menu_id, NULL, CONCAT('1,', @ai_parent_menu_id), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiParam:list', NULL, 0, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL
WHERE @ai_query_menu_id IS NULL;

SET @ai_query_menu_id := COALESCE(
    @ai_query_menu_id,
    LAST_INSERT_ID(),
    (
        SELECT `id`
        FROM `sys_menu`
        WHERE `parent_id` = @ai_parent_menu_id
          AND `name` = 'Query AiParam'
        LIMIT 1
    )
);

SET @ai_create_menu_id := (
    SELECT `id`
    FROM `sys_menu`
    WHERE `parent_id` = @ai_parent_menu_id
      AND `name` = 'Create AiParam'
    LIMIT 1
);

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Create AiParam', @ai_parent_menu_id, NULL, CONCAT('1,', @ai_parent_menu_id), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiParam:save', NULL, 1, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL
WHERE @ai_create_menu_id IS NULL;

SET @ai_create_menu_id := COALESCE(
    @ai_create_menu_id,
    LAST_INSERT_ID(),
    (
        SELECT `id`
        FROM `sys_menu`
        WHERE `parent_id` = @ai_parent_menu_id
          AND `name` = 'Create AiParam'
        LIMIT 1
    )
);

SET @ai_update_menu_id := (
    SELECT `id`
    FROM `sys_menu`
    WHERE `parent_id` = @ai_parent_menu_id
      AND `name` = 'Update AiParam'
    LIMIT 1
);

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Update AiParam', @ai_parent_menu_id, NULL, CONCAT('1,', @ai_parent_menu_id), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiParam:update', NULL, 2, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL
WHERE @ai_update_menu_id IS NULL;

SET @ai_update_menu_id := COALESCE(
    @ai_update_menu_id,
    LAST_INSERT_ID(),
    (
        SELECT `id`
        FROM `sys_menu`
        WHERE `parent_id` = @ai_parent_menu_id
          AND `name` = 'Update AiParam'
        LIMIT 1
    )
);

SET @ai_delete_menu_id := (
    SELECT `id`
    FROM `sys_menu`
    WHERE `parent_id` = @ai_parent_menu_id
      AND `name` = 'Delete AiParam'
    LIMIT 1
);

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Delete AiParam', @ai_parent_menu_id, NULL, CONCAT('1,', @ai_parent_menu_id), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiParam:remove', NULL, 3, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL
WHERE @ai_delete_menu_id IS NULL;

SET @ai_delete_menu_id := COALESCE(
    @ai_delete_menu_id,
    LAST_INSERT_ID(),
    (
        SELECT `id`
        FROM `sys_menu`
        WHERE `parent_id` = @ai_parent_menu_id
          AND `name` = 'Delete AiParam'
        LIMIT 1
    )
);

SET @ai_export_menu_id := (
    SELECT `id`
    FROM `sys_menu`
    WHERE `parent_id` = @ai_parent_menu_id
      AND `name` = 'Export AiParam'
    LIMIT 1
);

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Export AiParam', @ai_parent_menu_id, NULL, CONCAT('1,', @ai_parent_menu_id), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiParam:list', NULL, 4, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL
WHERE @ai_export_menu_id IS NULL;

SET @ai_export_menu_id := COALESCE(
    @ai_export_menu_id,
    LAST_INSERT_ID(),
    (
        SELECT `id`
        FROM `sys_menu`
        WHERE `parent_id` = @ai_parent_menu_id
          AND `name` = 'Export AiParam'
        LIMIT 1
    )
);

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, @ai_parent_menu_id
FROM DUAL
WHERE @ai_parent_menu_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM `sys_role_menu`
      WHERE `role_id` = 1
        AND `menu_id` = @ai_parent_menu_id
  );

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, @ai_query_menu_id
FROM DUAL
WHERE @ai_query_menu_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM `sys_role_menu`
      WHERE `role_id` = 1
        AND `menu_id` = @ai_query_menu_id
  );

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, @ai_create_menu_id
FROM DUAL
WHERE @ai_create_menu_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM `sys_role_menu`
      WHERE `role_id` = 1
        AND `menu_id` = @ai_create_menu_id
  );

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, @ai_update_menu_id
FROM DUAL
WHERE @ai_update_menu_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM `sys_role_menu`
      WHERE `role_id` = 1
        AND `menu_id` = @ai_update_menu_id
  );

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, @ai_delete_menu_id
FROM DUAL
WHERE @ai_delete_menu_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM `sys_role_menu`
      WHERE `role_id` = 1
        AND `menu_id` = @ai_delete_menu_id
  );

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, @ai_export_menu_id
FROM DUAL
WHERE @ai_export_menu_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM `sys_role_menu`
      WHERE `role_id` = 1
        AND `menu_id` = @ai_export_menu_id
  );

SET FOREIGN_KEY_CHECKS = 1;

-- >>> 20260316_ai_chat_storage.sql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `sys_ai_chat_session` (
  `id` varchar(64) NOT NULL COMMENT '会话ID',
  `title` varchar(255) DEFAULT NULL COMMENT '会话标题',
  `model_code` varchar(128) DEFAULT NULL COMMENT '模型编码',
  `last_message` varchar(255) DEFAULT NULL COMMENT '最近消息摘要',
  `last_message_at` datetime DEFAULT NULL COMMENT '最近消息时间',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户ID',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  `status` int(1) NOT NULL DEFAULT '1' COMMENT '状态{1:正常,0:冻结}',
  `deleted` int(1) NOT NULL DEFAULT '0' COMMENT '是否删除{1:是,0:否}',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_ai_chat_session_owner` (`tenant_id`,`user_id`,`deleted`),
  KEY `idx_ai_chat_session_update` (`update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI会话表';

CREATE TABLE IF NOT EXISTS `sys_ai_chat_message` (
  `id` varchar(64) NOT NULL COMMENT '消息ID',
  `session_id` varchar(64) NOT NULL COMMENT '会话ID',
  `role` varchar(32) DEFAULT NULL COMMENT '角色',
  `content` longtext COMMENT '消息内容',
  `model_code` varchar(128) DEFAULT NULL COMMENT '模型编码',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户ID',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  `status` int(1) NOT NULL DEFAULT '1' COMMENT '状态{1:正常,0:冻结}',
  `deleted` int(1) NOT NULL DEFAULT '0' COMMENT '是否删除{1:是,0:否}',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_ai_chat_message_session` (`session_id`,`deleted`,`create_time`),
  KEY `idx_ai_chat_message_owner` (`tenant_id`,`user_id`,`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI消息表';

SET FOREIGN_KEY_CHECKS = 1;

-- >>> 20260316_ai_phase2.sql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `sys_ai_prompt_template` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `uuid` varchar(64) DEFAULT NULL COMMENT '编号',
  `scene_code` varchar(64) NOT NULL COMMENT '场景编码',
  `template_name` varchar(255) DEFAULT NULL COMMENT '模板名称',
  `base_prompt` longtext COMMENT '基础提示词',
  `tool_prompt` longtext COMMENT '工具提示词',
  `output_prompt` longtext COMMENT '输出提示词',
  `version_no` int(11) DEFAULT NULL COMMENT '版本号',
  `published_flag` int(1) NOT NULL DEFAULT '0' COMMENT '已发布{1:是,0:否}',
  `status` int(1) NOT NULL DEFAULT '1' COMMENT '状态{1:正常,0:冻结}',
  `deleted` int(1) NOT NULL DEFAULT '0' COMMENT '是否删除{1:是,0:否}',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户ID',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `memo` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_ai_prompt_scene` (`tenant_id`,`scene_code`,`published_flag`,`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI提示词模板';

CREATE TABLE IF NOT EXISTS `sys_ai_diagnosis_record` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `diagnosis_no` varchar(64) DEFAULT NULL COMMENT '诊断编号',
  `session_id` varchar(64) DEFAULT NULL COMMENT '会话ID',
  `scene_code` varchar(64) DEFAULT NULL COMMENT '场景编码',
  `question` longtext COMMENT '问题',
  `conclusion` longtext COMMENT '结论',
  `tool_summary` longtext COMMENT '工具摘要',
  `model_code` varchar(128) DEFAULT NULL COMMENT '模型编码',
  `result` int(1) NOT NULL DEFAULT '2' COMMENT '结果{2:运行中,1:成功,0:失败}',
  `err` varchar(1000) DEFAULT NULL COMMENT '错误信息',
  `start_time` datetime DEFAULT NULL COMMENT '开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '结束时间',
  `spend_time` bigint(20) DEFAULT NULL COMMENT '耗时毫秒',
  `status` int(1) NOT NULL DEFAULT '1' COMMENT '状态{1:正常,0:冻结}',
  `deleted` int(1) NOT NULL DEFAULT '0' COMMENT '是否删除{1:是,0:否}',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户ID',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_ai_diag_owner` (`tenant_id`,`scene_code`,`deleted`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI诊断记录';

CREATE TABLE IF NOT EXISTS `sys_ai_call_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `session_id` varchar(64) DEFAULT NULL COMMENT '会话ID',
  `diagnosis_id` bigint(20) DEFAULT NULL COMMENT '诊断记录ID',
  `route_code` varchar(64) DEFAULT NULL COMMENT '路由编码',
  `model_code` varchar(128) DEFAULT NULL COMMENT '模型编码',
  `attempt_no` int(11) DEFAULT NULL COMMENT '尝试序号',
  `request_time` datetime DEFAULT NULL COMMENT '请求时间',
  `response_time` datetime DEFAULT NULL COMMENT '响应时间',
  `spend_time` bigint(20) DEFAULT NULL COMMENT '耗时毫秒',
  `result` int(1) NOT NULL DEFAULT '0' COMMENT '结果{1:成功,0:失败}',
  `err` varchar(1000) DEFAULT NULL COMMENT '错误信息',
  `status` int(1) NOT NULL DEFAULT '1' COMMENT '状态{1:正常,0:冻结}',
  `deleted` int(1) NOT NULL DEFAULT '0' COMMENT '是否删除{1:是,0:否}',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户ID',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_ai_call_owner` (`tenant_id`,`route_code`,`deleted`,`create_time`),
  KEY `idx_ai_call_diagnosis` (`diagnosis_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI调用日志';

CREATE TABLE IF NOT EXISTS `sys_ai_model_route` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `uuid` varchar(64) DEFAULT NULL COMMENT '编号',
  `route_code` varchar(64) NOT NULL COMMENT '路由编码',
  `model_code` varchar(128) NOT NULL COMMENT '模型编码',
  `priority` int(11) NOT NULL DEFAULT '1' COMMENT '优先级',
  `cooldown_until` datetime DEFAULT NULL COMMENT '冷却截止时间',
  `fail_count` int(11) NOT NULL DEFAULT '0' COMMENT '失败次数',
  `success_count` int(11) NOT NULL DEFAULT '0' COMMENT '成功次数',
  `status` int(1) NOT NULL DEFAULT '1' COMMENT '状态{1:启用,0:停用}',
  `deleted` int(1) NOT NULL DEFAULT '0' COMMENT '是否删除{1:是,0:否}',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户ID',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `memo` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_ai_route_code` (`tenant_id`,`route_code`,`status`,`deleted`,`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI模型路由';

INSERT INTO `sys_ai_prompt_template`
(`uuid`, `scene_code`, `template_name`, `base_prompt`, `tool_prompt`, `output_prompt`, `version_no`, `published_flag`, `status`, `deleted`, `tenant_id`, `create_by`, `create_time`, `update_by`, `update_time`, `memo`)
SELECT '6702082748514310', 'general_chat', '通用对话默认模板', '你是WMS系统内的智能助手，回答时优先保持准确、简洁，并结合上下文帮助用户理解仓储业务。', '如存在可用上下文，请优先引用实时数据回答。', '回答请直接给出结论，必要时补充说明。', 1, 1, 1, 0, 1, 2, NOW(), 2, NOW(), '默认模板'
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM `sys_ai_prompt_template` WHERE `tenant_id` = 1 AND `scene_code` = 'general_chat' AND `version_no` = 1
);

INSERT INTO `sys_ai_prompt_template`
(`uuid`, `scene_code`, `template_name`, `base_prompt`, `tool_prompt`, `output_prompt`, `version_no`, `published_flag`, `status`, `deleted`, `tenant_id`, `create_by`, `create_time`, `update_by`, `update_time`, `memo`)
SELECT '6702082748514311', 'system_diagnose', '系统诊断默认模板', '你是一名资深WMS智能诊断助手，目标是结合当前系统上下文对仓库运行情况做巡检分析。', '请先汇总库存、任务、设备站点、异常日志与AI调用失败信息，再判断是否存在异常。', '请按“问题概述、关键证据、可能原因、建议动作、风险评估”的结构输出。', 1, 1, 1, 0, 1, 2, NOW(), 2, NOW(), '默认模板'
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM `sys_ai_prompt_template` WHERE `tenant_id` = 1 AND `scene_code` = 'system_diagnose' AND `version_no` = 1
);

INSERT INTO `sys_ai_model_route`
(`uuid`, `route_code`, `model_code`, `priority`, `cooldown_until`, `fail_count`, `success_count`, `status`, `deleted`, `tenant_id`, `create_by`, `create_time`, `update_by`, `update_time`, `memo`)
SELECT '6702082748514312', 'general_chat', 'deepseek-ai/DeepSeek-V3.2', 1, NULL, 0, 0, 1, 0, 1, 2, NOW(), 2, NOW(), '默认通用对话模型'
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM `sys_ai_model_route` WHERE `tenant_id` = 1 AND `route_code` = 'general_chat' AND `model_code` = 'deepseek-ai/DeepSeek-V3.2'
);

INSERT INTO `sys_ai_model_route`
(`uuid`, `route_code`, `model_code`, `priority`, `cooldown_until`, `fail_count`, `success_count`, `status`, `deleted`, `tenant_id`, `create_by`, `create_time`, `update_by`, `update_time`, `memo`)
SELECT '6702082748514314', 'system_diagnose', 'deepseek-ai/DeepSeek-V3.2', 1, NULL, 0, 0, 1, 0, 1, 2, NOW(), 2, NOW(), '默认诊断模型'
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM `sys_ai_model_route` WHERE `tenant_id` = 1 AND `route_code` = 'system_diagnose' AND `model_code` = 'deepseek-ai/DeepSeek-V3.2'
);

SET @system_menu_ai_prompt := (
    SELECT `id` FROM `sys_menu` WHERE `component` = 'aiPrompt' LIMIT 1
);
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'menu.aiPrompt', 1, 'menu.system', '1', 'menu.system', '/system/aiPrompt', 'aiPrompt', NULL, NULL, 0, NULL, 'Psychology', 10, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE @system_menu_ai_prompt IS NULL;
SET @system_menu_ai_prompt := COALESCE(@system_menu_ai_prompt, LAST_INSERT_ID(), (SELECT `id` FROM `sys_menu` WHERE `component` = 'aiPrompt' LIMIT 1));

SET @system_menu_ai_diagnosis := (
    SELECT `id` FROM `sys_menu` WHERE `component` = 'aiDiagnosis' LIMIT 1
);
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'menu.aiDiagnosis', 1, 'menu.system', '1', 'menu.system', '/system/aiDiagnosis', 'aiDiagnosis', NULL, NULL, 0, NULL, 'FactCheck', 11, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE @system_menu_ai_diagnosis IS NULL;
SET @system_menu_ai_diagnosis := COALESCE(@system_menu_ai_diagnosis, LAST_INSERT_ID(), (SELECT `id` FROM `sys_menu` WHERE `component` = 'aiDiagnosis' LIMIT 1));

SET @system_menu_ai_call_log := (
    SELECT `id` FROM `sys_menu` WHERE `component` = 'aiCallLog' LIMIT 1
);
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'menu.aiCallLog', 1, 'menu.system', '1', 'menu.system', '/system/aiCallLog', 'aiCallLog', NULL, NULL, 0, NULL, 'History', 12, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE @system_menu_ai_call_log IS NULL;
SET @system_menu_ai_call_log := COALESCE(@system_menu_ai_call_log, LAST_INSERT_ID(), (SELECT `id` FROM `sys_menu` WHERE `component` = 'aiCallLog' LIMIT 1));

SET @system_menu_ai_route := (
    SELECT `id` FROM `sys_menu` WHERE `component` = 'aiRoute' LIMIT 1
);
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'menu.aiRoute', 1, 'menu.system', '1', 'menu.system', '/system/aiRoute', 'aiRoute', NULL, NULL, 0, NULL, 'Route', 13, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE @system_menu_ai_route IS NULL;
SET @system_menu_ai_route := COALESCE(@system_menu_ai_route, LAST_INSERT_ID(), (SELECT `id` FROM `sys_menu` WHERE `component` = 'aiRoute' LIMIT 1));

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Query AiPrompt', @system_menu_ai_prompt, NULL, CONCAT('1,', @system_menu_ai_prompt), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiPrompt:list', NULL, 0, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_prompt AND `authority` = 'system:aiPrompt:list');
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Create AiPrompt', @system_menu_ai_prompt, NULL, CONCAT('1,', @system_menu_ai_prompt), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiPrompt:save', NULL, 1, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_prompt AND `authority` = 'system:aiPrompt:save');
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Update AiPrompt', @system_menu_ai_prompt, NULL, CONCAT('1,', @system_menu_ai_prompt), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiPrompt:update', NULL, 2, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_prompt AND `authority` = 'system:aiPrompt:update');
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Delete AiPrompt', @system_menu_ai_prompt, NULL, CONCAT('1,', @system_menu_ai_prompt), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiPrompt:remove', NULL, 3, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_prompt AND `authority` = 'system:aiPrompt:remove');
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Publish AiPrompt', @system_menu_ai_prompt, NULL, CONCAT('1,', @system_menu_ai_prompt), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiPrompt:publish', NULL, 4, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_prompt AND `authority` = 'system:aiPrompt:publish');

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Query AiDiagnosis', @system_menu_ai_diagnosis, NULL, CONCAT('1,', @system_menu_ai_diagnosis), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiDiagnosis:list', NULL, 0, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_diagnosis AND `authority` = 'system:aiDiagnosis:list');

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Query AiCallLog', @system_menu_ai_call_log, NULL, CONCAT('1,', @system_menu_ai_call_log), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiCallLog:list', NULL, 0, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_call_log AND `authority` = 'system:aiCallLog:list');

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Query AiRoute', @system_menu_ai_route, NULL, CONCAT('1,', @system_menu_ai_route), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiRoute:list', NULL, 0, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_route AND `authority` = 'system:aiRoute:list');
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Create AiRoute', @system_menu_ai_route, NULL, CONCAT('1,', @system_menu_ai_route), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiRoute:save', NULL, 1, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_route AND `authority` = 'system:aiRoute:save');
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Update AiRoute', @system_menu_ai_route, NULL, CONCAT('1,', @system_menu_ai_route), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiRoute:update', NULL, 2, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_route AND `authority` = 'system:aiRoute:update');
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Delete AiRoute', @system_menu_ai_route, NULL, CONCAT('1,', @system_menu_ai_route), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiRoute:remove', NULL, 3, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_route AND `authority` = 'system:aiRoute:remove');

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, `id` FROM `sys_menu`
WHERE `authority` IN (
    'system:aiPrompt:list', 'system:aiPrompt:save', 'system:aiPrompt:update', 'system:aiPrompt:remove', 'system:aiPrompt:publish',
    'system:aiDiagnosis:list', 'system:aiCallLog:list',
    'system:aiRoute:list', 'system:aiRoute:save', 'system:aiRoute:update', 'system:aiRoute:remove'
)
AND NOT EXISTS (
    SELECT 1 FROM `sys_role_menu`
    WHERE `role_id` = 1 AND `menu_id` = `sys_menu`.`id`
);

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, `id` FROM `sys_menu`
WHERE `component` IN ('aiPrompt', 'aiDiagnosis', 'aiCallLog', 'aiRoute')
AND NOT EXISTS (
    SELECT 1 FROM `sys_role_menu`
    WHERE `role_id` = 1 AND `menu_id` = `sys_menu`.`id`
);

SET FOREIGN_KEY_CHECKS = 1;

-- >>> 20260316_ai_phase3.sql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `sys_ai_diagnostic_tool_config` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `uuid` varchar(64) DEFAULT NULL COMMENT '编号',
  `scene_code` varchar(64) NOT NULL COMMENT '场景编码',
  `tool_code` varchar(64) NOT NULL COMMENT '工具编码',
  `tool_name` varchar(128) DEFAULT NULL COMMENT '工具名称',
  `enabled_flag` int(1) NOT NULL DEFAULT '1' COMMENT '启用{1:是,0:否}',
  `priority` int(11) NOT NULL DEFAULT '1' COMMENT '优先级',
  `tool_prompt` longtext COMMENT '工具提示词',
  `status` int(1) NOT NULL DEFAULT '1' COMMENT '状态{1:正常,0:冻结}',
  `deleted` int(1) NOT NULL DEFAULT '0' COMMENT '是否删除{1:是,0:否}',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户ID',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `memo` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_ai_tool_scene` (`tenant_id`,`scene_code`,`status`,`deleted`,`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI诊断工具配置';

CREATE TABLE IF NOT EXISTS `sys_ai_prompt_publish_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `prompt_template_id` bigint(20) DEFAULT NULL COMMENT 'Prompt模板ID',
  `scene_code` varchar(64) DEFAULT NULL COMMENT '场景编码',
  `template_name` varchar(255) DEFAULT NULL COMMENT '模板名称',
  `version_no` int(11) DEFAULT NULL COMMENT '版本号',
  `action_type` varchar(64) DEFAULT NULL COMMENT '动作类型',
  `action_desc` varchar(255) DEFAULT NULL COMMENT '动作描述',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户ID',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_ai_prompt_publish_log` (`tenant_id`,`scene_code`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI提示词发布日志';

SET @sql := IF(
  EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sys_ai_diagnosis_record' AND COLUMN_NAME = 'report_title'),
  'SELECT 1',
  'ALTER TABLE `sys_ai_diagnosis_record` ADD COLUMN `report_title` varchar(255) DEFAULT NULL COMMENT ''报告标题'' AFTER `conclusion`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sys_ai_diagnosis_record' AND COLUMN_NAME = 'executive_summary'),
  'SELECT 1',
  'ALTER TABLE `sys_ai_diagnosis_record` ADD COLUMN `executive_summary` longtext COMMENT ''执行摘要'' AFTER `report_title`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sys_ai_diagnosis_record' AND COLUMN_NAME = 'evidence_summary'),
  'SELECT 1',
  'ALTER TABLE `sys_ai_diagnosis_record` ADD COLUMN `evidence_summary` longtext COMMENT ''证据摘要'' AFTER `executive_summary`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sys_ai_diagnosis_record' AND COLUMN_NAME = 'action_summary'),
  'SELECT 1',
  'ALTER TABLE `sys_ai_diagnosis_record` ADD COLUMN `action_summary` longtext COMMENT ''建议动作'' AFTER `evidence_summary`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sys_ai_diagnosis_record' AND COLUMN_NAME = 'risk_summary'),
  'SELECT 1',
  'ALTER TABLE `sys_ai_diagnosis_record` ADD COLUMN `risk_summary` longtext COMMENT ''风险评估'' AFTER `action_summary`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sys_ai_diagnosis_record' AND COLUMN_NAME = 'report_markdown'),
  'SELECT 1',
  'ALTER TABLE `sys_ai_diagnosis_record` ADD COLUMN `report_markdown` longtext COMMENT ''报告Markdown'' AFTER `risk_summary`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

INSERT INTO `sys_ai_diagnostic_tool_config`
(`uuid`, `scene_code`, `tool_code`, `tool_name`, `enabled_flag`, `priority`, `tool_prompt`, `status`, `deleted`, `tenant_id`, `create_by`, `create_time`, `update_by`, `update_time`, `memo`)
SELECT '6702082748514315', 'system_diagnose', 'warehouse_summary', '库存摘要', 1, 10, '结合库存摘要判断库位状态、库存结构与重点物料分布。', 1, 0, 1, 2, NOW(), 2, NOW(), '默认诊断工具'
FROM DUAL WHERE NOT EXISTS (
  SELECT 1 FROM `sys_ai_diagnostic_tool_config` WHERE `tenant_id` = 1 AND `scene_code` = 'system_diagnose' AND `tool_code` = 'warehouse_summary'
);

INSERT INTO `sys_ai_diagnostic_tool_config`
(`uuid`, `scene_code`, `tool_code`, `tool_name`, `enabled_flag`, `priority`, `tool_prompt`, `status`, `deleted`, `tenant_id`, `create_by`, `create_time`, `update_by`, `update_time`, `memo`)
SELECT '6702082748514316', 'system_diagnose', 'task_summary', '任务摘要', 1, 20, '结合任务摘要识别积压、异常状态和最近变更任务。', 1, 0, 1, 2, NOW(), 2, NOW(), '默认诊断工具'
FROM DUAL WHERE NOT EXISTS (
  SELECT 1 FROM `sys_ai_diagnostic_tool_config` WHERE `tenant_id` = 1 AND `scene_code` = 'system_diagnose' AND `tool_code` = 'task_summary'
);

INSERT INTO `sys_ai_diagnostic_tool_config`
(`uuid`, `scene_code`, `tool_code`, `tool_name`, `enabled_flag`, `priority`, `tool_prompt`, `status`, `deleted`, `tenant_id`, `create_by`, `create_time`, `update_by`, `update_time`, `memo`)
SELECT '6702082748514317', 'system_diagnose', 'device_site_summary', '设备站点摘要', 1, 30, '结合设备站点摘要判断设备状态、巷道分布和最近更新站点。', 1, 0, 1, 2, NOW(), 2, NOW(), '默认诊断工具'
FROM DUAL WHERE NOT EXISTS (
  SELECT 1 FROM `sys_ai_diagnostic_tool_config` WHERE `tenant_id` = 1 AND `scene_code` = 'system_diagnose' AND `tool_code` = 'device_site_summary'
);

INSERT INTO `sys_ai_diagnostic_tool_config`
(`uuid`, `scene_code`, `tool_code`, `tool_name`, `enabled_flag`, `priority`, `tool_prompt`, `status`, `deleted`, `tenant_id`, `create_by`, `create_time`, `update_by`, `update_time`, `memo`)
SELECT '6702082748514318', 'system_diagnose', 'operation_record', '异常操作日志', 1, 40, '重点识别最近失败操作和高频异常。', 1, 0, 1, 2, NOW(), 2, NOW(), '默认诊断工具'
FROM DUAL WHERE NOT EXISTS (
  SELECT 1 FROM `sys_ai_diagnostic_tool_config` WHERE `tenant_id` = 1 AND `scene_code` = 'system_diagnose' AND `tool_code` = 'operation_record'
);

INSERT INTO `sys_ai_diagnostic_tool_config`
(`uuid`, `scene_code`, `tool_code`, `tool_name`, `enabled_flag`, `priority`, `tool_prompt`, `status`, `deleted`, `tenant_id`, `create_by`, `create_time`, `update_by`, `update_time`, `memo`)
SELECT '6702082748514319', 'system_diagnose', 'ai_call_failure', 'AI调用失败', 1, 50, '重点识别最近 AI 调用失败的模型、错误类型和时间窗口。', 1, 0, 1, 2, NOW(), 2, NOW(), '默认诊断工具'
FROM DUAL WHERE NOT EXISTS (
  SELECT 1 FROM `sys_ai_diagnostic_tool_config` WHERE `tenant_id` = 1 AND `scene_code` = 'system_diagnose' AND `tool_code` = 'ai_call_failure'
);

SET @system_menu_ai_tool_config := (
    SELECT `id` FROM `sys_menu` WHERE `component` = 'aiToolConfig' LIMIT 1
);
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'menu.aiToolConfig', 1, 'menu.system', '1', 'menu.system', '/system/aiToolConfig', 'aiToolConfig', NULL, NULL, 0, NULL, 'BuildCircle', 14, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE @system_menu_ai_tool_config IS NULL;
SET @system_menu_ai_tool_config := COALESCE(@system_menu_ai_tool_config, LAST_INSERT_ID(), (SELECT `id` FROM `sys_menu` WHERE `component` = 'aiToolConfig' LIMIT 1));

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Query AiToolConfig', @system_menu_ai_tool_config, NULL, CONCAT('1,', @system_menu_ai_tool_config), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiToolConfig:list', NULL, 0, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_tool_config AND `authority` = 'system:aiToolConfig:list');
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Create AiToolConfig', @system_menu_ai_tool_config, NULL, CONCAT('1,', @system_menu_ai_tool_config), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiToolConfig:save', NULL, 1, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_tool_config AND `authority` = 'system:aiToolConfig:save');
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Update AiToolConfig', @system_menu_ai_tool_config, NULL, CONCAT('1,', @system_menu_ai_tool_config), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiToolConfig:update', NULL, 2, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_tool_config AND `authority` = 'system:aiToolConfig:update');
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Delete AiToolConfig', @system_menu_ai_tool_config, NULL, CONCAT('1,', @system_menu_ai_tool_config), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiToolConfig:remove', NULL, 3, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_tool_config AND `authority` = 'system:aiToolConfig:remove');

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, `id` FROM `sys_menu`
WHERE `authority` IN ('system:aiToolConfig:list', 'system:aiToolConfig:save', 'system:aiToolConfig:update', 'system:aiToolConfig:remove')
AND NOT EXISTS (
    SELECT 1 FROM `sys_role_menu`
    WHERE `role_id` = 1 AND `menu_id` = `sys_menu`.`id`
);

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, `id` FROM `sys_menu`
WHERE `component` IN ('aiToolConfig')
AND NOT EXISTS (
    SELECT 1 FROM `sys_role_menu`
    WHERE `role_id` = 1 AND `menu_id` = `sys_menu`.`id`
);

SET FOREIGN_KEY_CHECKS = 1;

-- >>> 20260316_ai_phase4.sql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `sys_ai_diagnosis_plan` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `uuid` varchar(64) DEFAULT NULL COMMENT '编号',
  `plan_name` varchar(255) DEFAULT NULL COMMENT '计划名称',
  `scene_code` varchar(64) DEFAULT NULL COMMENT '场景编码',
  `cron_expr` varchar(128) DEFAULT NULL COMMENT 'Cron表达式',
  `prompt` longtext COMMENT '巡检提示词',
  `preferred_model_code` varchar(128) DEFAULT NULL COMMENT '优先模型编码',
  `running_flag` int(1) NOT NULL DEFAULT '0' COMMENT '运行中{1:是,0:否}',
  `last_result` int(1) DEFAULT NULL COMMENT '上次结果{2:运行中,1:成功,0:失败}',
  `last_diagnosis_id` bigint(20) DEFAULT NULL COMMENT '上次诊断记录ID',
  `last_run_time` datetime DEFAULT NULL COMMENT '上次运行时间',
  `next_run_time` datetime DEFAULT NULL COMMENT '下次运行时间',
  `last_message` varchar(1000) DEFAULT NULL COMMENT '最近消息',
  `status` int(1) NOT NULL DEFAULT '1' COMMENT '状态{1:正常,0:冻结}',
  `deleted` int(1) NOT NULL DEFAULT '0' COMMENT '是否删除{1:是,0:否}',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户ID',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `memo` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_ai_diag_plan_due` (`tenant_id`,`status`,`running_flag`,`next_run_time`,`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI诊断巡检计划';

SET @system_menu_ai_plan := (
    SELECT `id` FROM `sys_menu` WHERE `component` = 'aiDiagnosisPlan' LIMIT 1
);
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'menu.aiDiagnosisPlan', 1, 'menu.system', '1', 'menu.system', '/system/aiDiagnosisPlan', 'aiDiagnosisPlan', NULL, NULL, 0, NULL, 'Schedule', 14, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE @system_menu_ai_plan IS NULL;
SET @system_menu_ai_plan := COALESCE(@system_menu_ai_plan, LAST_INSERT_ID(), (SELECT `id` FROM `sys_menu` WHERE `component` = 'aiDiagnosisPlan' LIMIT 1));

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Query AiDiagnosisPlan', @system_menu_ai_plan, NULL, CONCAT('1,', @system_menu_ai_plan), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiDiagnosisPlan:list', NULL, 0, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_plan AND `authority` = 'system:aiDiagnosisPlan:list');
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Create AiDiagnosisPlan', @system_menu_ai_plan, NULL, CONCAT('1,', @system_menu_ai_plan), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiDiagnosisPlan:save', NULL, 1, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_plan AND `authority` = 'system:aiDiagnosisPlan:save');
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Update AiDiagnosisPlan', @system_menu_ai_plan, NULL, CONCAT('1,', @system_menu_ai_plan), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiDiagnosisPlan:update', NULL, 2, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_plan AND `authority` = 'system:aiDiagnosisPlan:update');
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Delete AiDiagnosisPlan', @system_menu_ai_plan, NULL, CONCAT('1,', @system_menu_ai_plan), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiDiagnosisPlan:remove', NULL, 3, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_plan AND `authority` = 'system:aiDiagnosisPlan:remove');

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, `id` FROM `sys_menu`
WHERE `authority` IN (
    'system:aiDiagnosisPlan:list', 'system:aiDiagnosisPlan:save', 'system:aiDiagnosisPlan:update', 'system:aiDiagnosisPlan:remove'
)
AND NOT EXISTS (
    SELECT 1 FROM `sys_role_menu`
    WHERE `role_id` = 1 AND `menu_id` = `sys_menu`.`id`
);

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, `id` FROM `sys_menu`
WHERE `component` IN ('aiDiagnosisPlan')
AND NOT EXISTS (
    SELECT 1 FROM `sys_role_menu`
    WHERE `role_id` = 1 AND `menu_id` = `sys_menu`.`id`
);

SET FOREIGN_KEY_CHECKS = 1;

-- >>> 20260316_ai_phase5.sql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `sys_ai_mcp_mount` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `uuid` varchar(64) DEFAULT NULL COMMENT '编号',
  `name` varchar(255) DEFAULT NULL COMMENT '名称',
  `mount_code` varchar(128) DEFAULT NULL COMMENT '挂载编码',
  `transport_type` varchar(32) DEFAULT NULL COMMENT '传输类型',
  `url` varchar(512) DEFAULT NULL COMMENT '地址',
  `enabled_flag` int(1) NOT NULL DEFAULT '1' COMMENT '启用{1:是,0:否}',
  `timeout_ms` int(11) DEFAULT NULL COMMENT '超时毫秒',
  `last_test_result` int(1) DEFAULT NULL COMMENT '上次测试结果{1:成功,0:失败}',
  `last_test_time` datetime DEFAULT NULL COMMENT '上次测试时间',
  `last_test_message` varchar(1000) DEFAULT NULL COMMENT '上次测试消息',
  `last_tool_count` int(11) DEFAULT NULL COMMENT '上次工具数',
  `status` int(1) NOT NULL DEFAULT '1' COMMENT '状态{1:正常,0:冻结}',
  `deleted` int(1) NOT NULL DEFAULT '0' COMMENT '是否删除{1:是,0:否}',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户ID',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `memo` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_ai_mcp_mount` (`tenant_id`,`mount_code`,`status`,`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI MCP挂载';

INSERT INTO `sys_ai_mcp_mount`
(`uuid`, `name`, `mount_code`, `transport_type`, `url`, `enabled_flag`, `timeout_ms`, `status`, `deleted`, `tenant_id`, `create_by`, `create_time`, `update_by`, `update_time`, `memo`)
SELECT '6702082748514325', 'WMS本地MCP', 'wms_local', 'INTERNAL', '/ai/mcp', 1, 10000, 1, 0, 1, 2, NOW(), 2, NOW(), '默认挂载当前 WMS AI 内置工具集合'
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM `sys_ai_mcp_mount` WHERE `tenant_id` = 1 AND `mount_code` = 'wms_local'
);

SET @system_menu_ai_mcp_mount := (
    SELECT `id` FROM `sys_menu` WHERE `component` = 'aiMcpMount' LIMIT 1
);
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'menu.aiMcpMount', 1, 'menu.system', '1', 'menu.system', '/system/aiMcpMount', 'aiMcpMount', NULL, NULL, 0, NULL, 'Hub', 15, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE @system_menu_ai_mcp_mount IS NULL;
SET @system_menu_ai_mcp_mount := COALESCE(@system_menu_ai_mcp_mount, LAST_INSERT_ID(), (SELECT `id` FROM `sys_menu` WHERE `component` = 'aiMcpMount' LIMIT 1));

INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Query AiMcpMount', @system_menu_ai_mcp_mount, NULL, CONCAT('1,', @system_menu_ai_mcp_mount), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiMcpMount:list', NULL, 0, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_mcp_mount AND `authority` = 'system:aiMcpMount:list');
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Create AiMcpMount', @system_menu_ai_mcp_mount, NULL, CONCAT('1,', @system_menu_ai_mcp_mount), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiMcpMount:save', NULL, 1, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_mcp_mount AND `authority` = 'system:aiMcpMount:save');
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Update AiMcpMount', @system_menu_ai_mcp_mount, NULL, CONCAT('1,', @system_menu_ai_mcp_mount), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiMcpMount:update', NULL, 2, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_mcp_mount AND `authority` = 'system:aiMcpMount:update');
INSERT INTO `sys_menu`
(`name`, `parent_id`, `parent_name`, `path`, `path_name`, `route`, `component`, `brief`, `code`, `type`, `authority`, `icon`, `sort`, `meta`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'Delete AiMcpMount', @system_menu_ai_mcp_mount, NULL, CONCAT('1,', @system_menu_ai_mcp_mount), NULL, NULL, NULL, NULL, NULL, 1, 'system:aiMcpMount:remove', NULL, 3, NULL, 1, 1, 0, NOW(), 2, NOW(), 2, NULL
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `parent_id` = @system_menu_ai_mcp_mount AND `authority` = 'system:aiMcpMount:remove');

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, `id` FROM `sys_menu`
WHERE `authority` IN (
    'system:aiMcpMount:list', 'system:aiMcpMount:save', 'system:aiMcpMount:update', 'system:aiMcpMount:remove'
)
AND NOT EXISTS (
    SELECT 1 FROM `sys_role_menu`
    WHERE `role_id` = 1 AND `menu_id` = `sys_menu`.`id`
);

INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT 1, `id` FROM `sys_menu`
WHERE `component` IN ('aiMcpMount')
AND NOT EXISTS (
    SELECT 1 FROM `sys_role_menu`
    WHERE `role_id` = 1 AND `menu_id` = `sys_menu`.`id`
);

SET FOREIGN_KEY_CHECKS = 1;

-- >>> 20260316_ai_phase6_mcp_console.sql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

SET @table_schema = DATABASE();
SET @add_auth_type_sql = (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE `sys_ai_mcp_mount` ADD COLUMN `auth_type` varchar(32) DEFAULT ''NONE'' COMMENT ''认证方式'' AFTER `url`',
        'SELECT 1'
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @table_schema
      AND TABLE_NAME = 'sys_ai_mcp_mount'
      AND COLUMN_NAME = 'auth_type'
);
PREPARE stmt FROM @add_auth_type_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @add_auth_value_sql = (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE `sys_ai_mcp_mount` ADD COLUMN `auth_value` varchar(512) DEFAULT NULL COMMENT ''认证信息'' AFTER `auth_type`',
        'SELECT 1'
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @table_schema
      AND TABLE_NAME = 'sys_ai_mcp_mount'
      AND COLUMN_NAME = 'auth_value'
);
PREPARE stmt FROM @add_auth_value_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @add_usage_scope_sql = (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE `sys_ai_mcp_mount` ADD COLUMN `usage_scope` varchar(32) DEFAULT ''DIAGNOSE_ONLY'' COMMENT ''用途范围'' AFTER `auth_value`',
        'SELECT 1'
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @table_schema
      AND TABLE_NAME = 'sys_ai_mcp_mount'
      AND COLUMN_NAME = 'usage_scope'
);
PREPARE stmt FROM @add_usage_scope_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `sys_ai_mcp_mount`
SET `auth_type` = COALESCE(NULLIF(`auth_type`, ''), 'NONE'),
    `usage_scope` = CASE
        WHEN `transport_type` = 'INTERNAL' THEN 'CHAT_AND_DIAGNOSE'
        ELSE COALESCE(NULLIF(`usage_scope`, ''), 'DIAGNOSE_ONLY')
    END
WHERE 1 = 1;

SET FOREIGN_KEY_CHECKS = 1;

-- >>> 20260316_ai_phase7_tool_usage_scope.sql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

SET @table_schema = DATABASE();
SET @add_usage_scope_sql = (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE `sys_ai_diagnostic_tool_config` ADD COLUMN `usage_scope` varchar(32) DEFAULT ''DIAGNOSE_ONLY'' COMMENT ''用途范围'' AFTER `tool_prompt`',
        'SELECT 1'
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @table_schema
      AND TABLE_NAME = 'sys_ai_diagnostic_tool_config'
      AND COLUMN_NAME = 'usage_scope'
);
PREPARE stmt FROM @add_usage_scope_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `sys_ai_diagnostic_tool_config`
SET `usage_scope` = CASE
    WHEN `enabled_flag` IS NOT NULL AND `enabled_flag` <> 1 THEN 'DISABLED'
    WHEN `scene_code` IS NULL OR TRIM(`scene_code`) = '' THEN 'CHAT_AND_DIAGNOSE'
    ELSE 'DIAGNOSE_ONLY'
END
WHERE `usage_scope` IS NULL OR TRIM(`usage_scope`) = '';

SET FOREIGN_KEY_CHECKS = 1;