SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `sys_ai_param` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) NOT NULL COMMENT '名称',
  `provider_type` varchar(64) NOT NULL COMMENT '提供方类型',
  `base_url` varchar(500) NOT NULL COMMENT '基础地址',
  `api_key` varchar(1024) NOT NULL COMMENT 'API Key',
  `model` varchar(255) NOT NULL COMMENT '模型',
  `temperature` decimal(10,4) DEFAULT NULL COMMENT 'temperature',
  `top_p` decimal(10,4) DEFAULT NULL COMMENT 'topP',
  `max_tokens` int(11) DEFAULT NULL COMMENT '最大Token',
  `timeout_ms` int(11) DEFAULT NULL COMMENT '超时时间',
  `streaming_enabled` tinyint(1) DEFAULT '1' COMMENT '是否启用流式响应',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户',
  `status` int(11) DEFAULT '1' COMMENT '状态',
  `deleted` int(11) DEFAULT '0' COMMENT '删除标记',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人',
  `memo` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_sys_ai_param_tenant_status` (`tenant_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI 参数配置';

CREATE TABLE IF NOT EXISTS `sys_ai_prompt` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) NOT NULL COMMENT '名称',
  `code` varchar(128) NOT NULL COMMENT '编码',
  `scene` varchar(128) DEFAULT NULL COMMENT '场景',
  `system_prompt` text COMMENT '系统 Prompt',
  `user_prompt_template` text COMMENT '用户 Prompt 模板',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户',
  `status` int(11) DEFAULT '1' COMMENT '状态',
  `deleted` int(11) DEFAULT '0' COMMENT '删除标记',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人',
  `memo` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sys_ai_prompt_code_tenant` (`tenant_id`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI Prompt 配置';

CREATE TABLE IF NOT EXISTS `sys_ai_mcp_mount` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) NOT NULL COMMENT '名称',
  `transport_type` varchar(64) NOT NULL COMMENT '传输类型',
  `builtin_code` varchar(128) DEFAULT NULL COMMENT '内置 MCP 编码',
  `server_url` varchar(500) DEFAULT NULL COMMENT '服务地址',
  `endpoint` varchar(255) DEFAULT NULL COMMENT 'SSE Endpoint',
  `command` varchar(500) DEFAULT NULL COMMENT '本地命令',
  `args_json` text COMMENT '命令参数 JSON',
  `env_json` text COMMENT '环境变量 JSON',
  `headers_json` text COMMENT '请求头 JSON',
  `request_timeout_ms` int(11) DEFAULT NULL COMMENT '请求超时时间',
  `sort` int(11) DEFAULT '0' COMMENT '排序',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户',
  `status` int(11) DEFAULT '1' COMMENT '状态',
  `deleted` int(11) DEFAULT '0' COMMENT '删除标记',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人',
  `memo` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_sys_ai_mcp_mount_tenant_status` (`tenant_id`,`status`,`sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI MCP 挂载配置';

CREATE TABLE IF NOT EXISTS `sys_ai_chat_session` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `title` varchar(255) NOT NULL COMMENT '会话标题',
  `prompt_code` varchar(128) NOT NULL COMMENT 'Prompt 编码',
  `user_id` bigint(20) NOT NULL COMMENT '用户 ID',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户',
  `last_message_time` datetime DEFAULT NULL COMMENT '最后消息时间',
  `pinned` tinyint(1) DEFAULT '0' COMMENT '是否置顶',
  `status` int(11) DEFAULT '1' COMMENT '状态',
  `deleted` int(11) DEFAULT '0' COMMENT '删除标记',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人',
  PRIMARY KEY (`id`),
  KEY `idx_sys_ai_chat_session_user_prompt` (`tenant_id`,`user_id`,`prompt_code`,`last_message_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI 对话会话';

CREATE TABLE IF NOT EXISTS `sys_ai_chat_message` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `session_id` bigint(20) NOT NULL COMMENT '会话 ID',
  `seq_no` int(11) NOT NULL COMMENT '消息序号',
  `role` varchar(32) NOT NULL COMMENT '消息角色',
  `content` longtext COMMENT '消息内容',
  `user_id` bigint(20) NOT NULL COMMENT '用户 ID',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户',
  `deleted` int(11) DEFAULT '0' COMMENT '删除标记',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  PRIMARY KEY (`id`),
  KEY `idx_sys_ai_chat_message_session_seq` (`session_id`,`seq_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI 对话消息';

SET @builtin_code_exists := (
  SELECT COUNT(1)
  FROM `information_schema`.`COLUMNS`
  WHERE `TABLE_SCHEMA` = DATABASE()
    AND `TABLE_NAME` = 'sys_ai_mcp_mount'
    AND `COLUMN_NAME` = 'builtin_code'
);
SET @builtin_code_sql := IF(
  @builtin_code_exists = 0,
  'ALTER TABLE `sys_ai_mcp_mount` ADD COLUMN `builtin_code` varchar(128) DEFAULT NULL COMMENT ''内置 MCP 编码'' AFTER `transport_type`',
  'SELECT 1'
);
PREPARE builtin_code_stmt FROM @builtin_code_sql;
EXECUTE builtin_code_stmt;
DEALLOCATE PREPARE builtin_code_stmt;

SET @chat_session_pinned_exists := (
  SELECT COUNT(1)
  FROM `information_schema`.`COLUMNS`
  WHERE `TABLE_SCHEMA` = DATABASE()
    AND `TABLE_NAME` = 'sys_ai_chat_session'
    AND `COLUMN_NAME` = 'pinned'
);
SET @chat_session_pinned_sql := IF(
  @chat_session_pinned_exists = 0,
  'ALTER TABLE `sys_ai_chat_session` ADD COLUMN `pinned` tinyint(1) DEFAULT ''0'' COMMENT ''是否置顶'' AFTER `last_message_time`',
  'SELECT 1'
);
PREPARE chat_session_pinned_stmt FROM @chat_session_pinned_sql;
EXECUTE chat_session_pinned_stmt;
DEALLOCATE PREPARE chat_session_pinned_stmt;

BEGIN;
INSERT INTO `sys_ai_prompt`
(`id`, `name`, `code`, `scene`, `system_prompt`, `user_prompt_template`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
VALUES
(1, '首页默认助手', 'home.default', 'home', '你是 RSF 系统的 AI 助手，请结合当前上下文为用户提供准确、简洁、可执行的帮助。', '请基于当前页面上下文回答用户问题：{{input}}', 1, 1, 0, '2026-03-18 19:00:00', 2, '2026-03-18 19:00:00', 2, '首页默认 Prompt')
ON DUPLICATE KEY UPDATE
`name` = VALUES(`name`),
`scene` = VALUES(`scene`),
`system_prompt` = VALUES(`system_prompt`),
`user_prompt_template` = VALUES(`user_prompt_template`),
`status` = VALUES(`status`),
`deleted` = VALUES(`deleted`),
`update_time` = VALUES(`update_time`),
`update_by` = VALUES(`update_by`),
`memo` = VALUES(`memo`);

INSERT INTO `sys_ai_mcp_mount`
(`name`, `transport_type`, `builtin_code`, `request_timeout_ms`, `sort`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'RSF WMS 内置 MCP', 'BUILTIN', 'RSF_WMS', 60000, 0, 1, 1, 0, '2026-03-19 10:00:00', 2, '2026-03-19 10:00:00', 2, '内置 WMS 查询与任务工具'
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_ai_mcp_mount`
  WHERE `tenant_id` = 1 AND `transport_type` = 'BUILTIN' AND `builtin_code` = 'RSF_WMS' AND `deleted` = 0
);

INSERT INTO `sys_ai_mcp_mount`
(`name`, `transport_type`, `builtin_code`, `request_timeout_ms`, `sort`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'RSF WMS 库存作业内置 MCP', 'BUILTIN', 'RSF_WMS_STOCK', 60000, 1, 1, 0, 0, '2026-03-19 10:00:00', 2, '2026-03-19 10:00:00', 2, '内置库存查询和站点查询工具'
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_ai_mcp_mount`
  WHERE `tenant_id` = 1 AND `transport_type` = 'BUILTIN' AND `builtin_code` = 'RSF_WMS_STOCK' AND `deleted` = 0
);

INSERT INTO `sys_ai_mcp_mount`
(`name`, `transport_type`, `builtin_code`, `request_timeout_ms`, `sort`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'RSF WMS 任务查询内置 MCP', 'BUILTIN', 'RSF_WMS_TASK', 60000, 2, 1, 0, 0, '2026-03-19 10:00:00', 2, '2026-03-19 10:00:00', 2, '内置任务列表与任务详情查询工具'
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_ai_mcp_mount`
  WHERE `tenant_id` = 1 AND `transport_type` = 'BUILTIN' AND `builtin_code` = 'RSF_WMS_TASK' AND `deleted` = 0
);

INSERT INTO `sys_ai_mcp_mount`
(`name`, `transport_type`, `builtin_code`, `request_timeout_ms`, `sort`, `tenant_id`, `status`, `deleted`, `create_time`, `create_by`, `update_time`, `update_by`, `memo`)
SELECT 'RSF WMS 基础资料内置 MCP', 'BUILTIN', 'RSF_WMS_BASE', 60000, 3, 1, 0, 0, '2026-03-19 10:00:00', 2, '2026-03-19 10:00:00', 2, '内置仓库、基础站点和字典数据查询工具'
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_ai_mcp_mount`
  WHERE `tenant_id` = 1 AND `transport_type` = 'BUILTIN' AND `builtin_code` = 'RSF_WMS_BASE' AND `deleted` = 0
);

INSERT INTO `sys_menu`
(`id`, `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`)
VALUES
(5301, 'menu.aiParam', 1, 'menu.system', '1', 'menu.system', '/system/aiParam', 'aiParam', NULL, NULL, 0, NULL, 'SmartToy', 11, NULL, 1, 1, 0, '2026-03-18 19:00:00', 2, '2026-03-18 19:00:00', 2, NULL),
(5302, 'Query AI Param', 5301, NULL, '1,5301', NULL, NULL, NULL, NULL, NULL, 1, 'system:aiParam:list', NULL, 0, NULL, 1, 1, 0, '2026-03-18 19:00:00', 2, '2026-03-18 19:00:00', 2, NULL),
(5303, 'Create AI Param', 5301, NULL, '1,5301', NULL, NULL, NULL, NULL, NULL, 1, 'system:aiParam:save', NULL, 1, NULL, 1, 1, 0, '2026-03-18 19:00:00', 2, '2026-03-18 19:00:00', 2, NULL),
(5304, 'Update AI Param', 5301, NULL, '1,5301', NULL, NULL, NULL, NULL, NULL, 1, 'system:aiParam:update', NULL, 2, NULL, 1, 1, 0, '2026-03-18 19:00:00', 2, '2026-03-18 19:00:00', 2, NULL),
(5305, 'Delete AI Param', 5301, NULL, '1,5301', NULL, NULL, NULL, NULL, NULL, 1, 'system:aiParam:remove', NULL, 3, NULL, 1, 1, 0, '2026-03-18 19:00:00', 2, '2026-03-18 19:00:00', 2, NULL),
(5306, 'menu.aiPrompt', 1, 'menu.system', '1', 'menu.system', '/system/aiPrompt', 'aiPrompt', NULL, NULL, 0, NULL, 'PsychologyAlt', 12, NULL, 1, 1, 0, '2026-03-18 19:00:00', 2, '2026-03-18 19:00:00', 2, NULL),
(5307, 'Query AI Prompt', 5306, NULL, '1,5306', NULL, NULL, NULL, NULL, NULL, 1, 'system:aiPrompt:list', NULL, 0, NULL, 1, 1, 0, '2026-03-18 19:00:00', 2, '2026-03-18 19:00:00', 2, NULL),
(5308, 'Create AI Prompt', 5306, NULL, '1,5306', NULL, NULL, NULL, NULL, NULL, 1, 'system:aiPrompt:save', NULL, 1, NULL, 1, 1, 0, '2026-03-18 19:00:00', 2, '2026-03-18 19:00:00', 2, NULL),
(5309, 'Update AI Prompt', 5306, NULL, '1,5306', NULL, NULL, NULL, NULL, NULL, 1, 'system:aiPrompt:update', NULL, 2, NULL, 1, 1, 0, '2026-03-18 19:00:00', 2, '2026-03-18 19:00:00', 2, NULL),
(5310, 'Delete AI Prompt', 5306, NULL, '1,5306', NULL, NULL, NULL, NULL, NULL, 1, 'system:aiPrompt:remove', NULL, 3, NULL, 1, 1, 0, '2026-03-18 19:00:00', 2, '2026-03-18 19:00:00', 2, NULL),
(5311, 'menu.aiMcpMount', 1, 'menu.system', '1', 'menu.system', '/system/aiMcpMount', 'aiMcpMount', NULL, NULL, 0, NULL, 'Cable', 13, NULL, 1, 1, 0, '2026-03-18 19:00:00', 2, '2026-03-18 19:00:00', 2, NULL),
(5312, 'Query AI MCP Mount', 5311, NULL, '1,5311', NULL, NULL, NULL, NULL, NULL, 1, 'system:aiMcpMount:list', NULL, 0, NULL, 1, 1, 0, '2026-03-18 19:00:00', 2, '2026-03-18 19:00:00', 2, NULL),
(5313, 'Create AI MCP Mount', 5311, NULL, '1,5311', NULL, NULL, NULL, NULL, NULL, 1, 'system:aiMcpMount:save', NULL, 1, NULL, 1, 1, 0, '2026-03-18 19:00:00', 2, '2026-03-18 19:00:00', 2, NULL),
(5314, 'Update AI MCP Mount', 5311, NULL, '1,5311', NULL, NULL, NULL, NULL, NULL, 1, 'system:aiMcpMount:update', NULL, 2, NULL, 1, 1, 0, '2026-03-18 19:00:00', 2, '2026-03-18 19:00:00', 2, NULL),
(5315, 'Delete AI MCP Mount', 5311, NULL, '1,5311', NULL, NULL, NULL, NULL, NULL, 1, 'system:aiMcpMount:remove', NULL, 3, NULL, 1, 1, 0, '2026-03-18 19:00:00', 2, '2026-03-18 19:00:00', 2, NULL)
ON DUPLICATE KEY UPDATE
`name` = VALUES(`name`),
`parent_id` = VALUES(`parent_id`),
`parent_name` = VALUES(`parent_name`),
`path` = VALUES(`path`),
`path_name` = VALUES(`path_name`),
`route` = VALUES(`route`),
`component` = VALUES(`component`),
`authority` = VALUES(`authority`),
`icon` = VALUES(`icon`),
`sort` = VALUES(`sort`),
`tenant_id` = VALUES(`tenant_id`),
`status` = VALUES(`status`),
`deleted` = VALUES(`deleted`),
`update_time` = VALUES(`update_time`),
`update_by` = VALUES(`update_by`);

INSERT INTO `sys_role_menu` (`id`, `role_id`, `menu_id`)
VALUES
(5301, 1, 5301),
(5302, 1, 5302),
(5303, 1, 5303),
(5304, 1, 5304),
(5305, 1, 5305),
(5306, 1, 5306),
(5307, 1, 5307),
(5308, 1, 5308),
(5309, 1, 5309),
(5310, 1, 5310),
(5311, 1, 5311),
(5312, 1, 5312),
(5313, 1, 5313),
(5314, 1, 5314),
(5315, 1, 5315)
ON DUPLICATE KEY UPDATE
`role_id` = VALUES(`role_id`),
`menu_id` = VALUES(`menu_id`);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;