storlead-sasa-platform/java/storlead-system/sql/app_id_increment.sql

-- =============================================================================
-- 应用域 app_id 增量脚本（MySQL 5.7+）
-- 说明：
--   1. 新建 sys_app 应用主表，并与历史 magic number（10/20/30…）对齐
--   2. 为业务表增量添加 app_id 字段（可重复执行）
--   3. 可选：回填历史数据默认归属「销售」应用 app_id=20
-- 执行前请备份；生产环境建议先在测试库验证
-- =============================================================================

SET NAMES utf8mb4;

-- -----------------------------------------------------------------------------
-- 1. 应用主表 sys_app
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sys_app` (
    `id`           BIGINT(20)   NOT NULL AUTO_INCREMENT COMMENT '主键，与 menu.app_id 等 FK 对齐',
    `app_code`     VARCHAR(64)  NOT NULL COMMENT '应用编码，全局唯一',
    `app_name`     VARCHAR(128) NOT NULL COMMENT '应用名称',
    `app_title`    VARCHAR(128)          DEFAULT NULL COMMENT '前台标题',
    `icon`         VARCHAR(255)          DEFAULT NULL COMMENT '图标',
    `home_path`    VARCHAR(255)          DEFAULT NULL COMMENT '默认首页路由',
    `owner_by`     BIGINT(20)            DEFAULT NULL,
    `create_by`    BIGINT(20)            DEFAULT NULL,
    `update_by`    BIGINT(20)            DEFAULT NULL,
    `create_time`  DATETIME              DEFAULT NULL,
    `update_time`  DATETIME              DEFAULT NULL,
    `is_delete`    TINYINT(1)            DEFAULT 0 COMMENT '0未删除 1已删除',
    `enabled`      TINYINT(1)            DEFAULT 1 COMMENT '1启用 0禁用',
    `sort`         INT(11)               DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_sys_app_app_code` (`app_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统应用主数据';

-- -----------------------------------------------------------------------------
-- 2. 安全加列存储过程（列已存在则跳过）
-- -----------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS `proc_add_column_if_not_exists`;
DELIMITER $$
CREATE PROCEDURE `proc_add_column_if_not_exists`(
    IN p_table  VARCHAR(64),
    IN p_column VARCHAR(64),
    IN p_ddl    TEXT
)
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
          AND COLUMN_NAME = p_column
    ) THEN
        SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN ', p_ddl);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$
DELIMITER ;

-- menu（多数环境已有 app_id，此处兜底）
CALL proc_add_column_if_not_exists(
    'menu',
    'app_id',
    '`app_id` BIGINT(20) NULL COMMENT ''所属应用 ID，对应 sys_app.id'' AFTER `pid`'
);

-- 角色
CALL proc_add_column_if_not_exists(
    'role',
    'app_id',
    '`app_id` BIGINT(20) NULL COMMENT ''所属应用 ID，对应 sys_app.id'' AFTER `remark`'
);

-- AI 提示词模板
CALL proc_add_column_if_not_exists(
    'agent_prompt_template',
    'app_id',
    '`app_id` BIGINT(20) NULL COMMENT ''所属应用 ID，对应 sys_app.id'' AFTER `template_code`'
);

-- 站内消息记录
CALL proc_add_column_if_not_exists(
    'inside_message_record',
    'app_id',
    '`app_id` BIGINT(20) NULL COMMENT ''所属应用 ID，对应 sys_app.id'' AFTER `title`'
);

-- 消息模板事件组
CALL proc_add_column_if_not_exists(
    'message_template_event_group',
    'app_id',
    '`app_id` BIGINT(20) NULL COMMENT ''所属应用 ID，对应 sys_app.id'' AFTER `template_service_name`'
);

-- 消息模板明细
CALL proc_add_column_if_not_exists(
    'message_template_event_detail',
    'app_id',
    '`app_id` BIGINT(20) NULL COMMENT ''所属应用 ID，对应 sys_app.id'' AFTER `template_event_id`'
);

DROP PROCEDURE IF EXISTS `proc_add_column_if_not_exists`;

-- -----------------------------------------------------------------------------
-- 3. 索引（不存在则创建）
-- -----------------------------------------------------------------------------
-- role
SET @idx_exists := (
    SELECT COUNT(1) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'role' AND INDEX_NAME = 'idx_role_app_id'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE `role` ADD INDEX `idx_role_app_id` (`app_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- agent_prompt_template
SET @idx_exists := (
    SELECT COUNT(1) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'agent_prompt_template' AND INDEX_NAME = 'idx_agent_prompt_template_app_id'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE `agent_prompt_template` ADD INDEX `idx_agent_prompt_template_app_id` (`app_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- inside_message_record
SET @idx_exists := (
    SELECT COUNT(1) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inside_message_record' AND INDEX_NAME = 'idx_inside_message_record_app_id'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE `inside_message_record` ADD INDEX `idx_inside_message_record_app_id` (`app_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- message_template_event_group
SET @idx_exists := (
    SELECT COUNT(1) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'message_template_event_group' AND INDEX_NAME = 'idx_msg_tpl_group_app_id'
);
SET @sql := IF(@idx_exists = 0, 'ALTER TABLE `message_template_event_group` ADD INDEX `idx_msg_tpl_group_app_id` (`app_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- -----------------------------------------------------------------------------
-- 4. 初始化 sys_app（与历史 magic number 对齐，id 固定便于 menu.app_id 引用）
-- -----------------------------------------------------------------------------
SET @OLD_SQL_MODE = @@SQL_MODE;
SET SESSION sql_mode = CONCAT(IFNULL(@@SQL_MODE, ''), ',NO_AUTO_VALUE_ON_ZERO');

INSERT INTO `sys_app` (`id`, `app_code`, `app_name`, `app_title`, `home_path`, `enabled`, `sort`, `is_delete`, `create_time`)
VALUES
    (0,  'main',      '主应用',     '主应用',     NULL, 1, 0,  0, NOW()),
    (10, 'trade',     '智能贸易',   '智能贸易',   NULL, 1, 10, 0, NOW()),
    (20, 'sales',     '销售',       '销售',       NULL, 1, 20, 0, NOW()),
    (30, 'otr',       'OTR',        'OTR',        NULL, 1, 30, 0, NOW()),
    (40, 'payroll',   '工资',       '工资',       NULL, 1, 40, 0, NOW()),
    (50, 'project',   '项目',       '项目',       NULL, 1, 50, 0, NOW()),
    (60, 'warehouse', '领存平台',   '领存平台',   NULL, 1, 60, 0, NOW())
ON DUPLICATE KEY UPDATE
    `app_name`   = VALUES(`app_name`),
    `app_title`  = VALUES(`app_title`),
    `enabled`    = VALUES(`enabled`),
    `sort`       = VALUES(`sort`),
    `update_time`= NOW();

SET SESSION sql_mode = @OLD_SQL_MODE;

-- -----------------------------------------------------------------------------
-- 5. 历史数据回填（按需执行，默认归到销售 app_id=20）
--    若 menu 已有 app_id 可不执行第一条
-- -----------------------------------------------------------------------------
-- UPDATE `menu` SET `app_id` = 20 WHERE `app_id` IS NULL;

UPDATE `inside_message_record`
SET `app_id` = 20
WHERE `app_id` IS NULL;

UPDATE `message_template_event_group`
SET `app_id` = 20
WHERE `app_id` IS NULL;

UPDATE `message_template_event_detail` d
INNER JOIN `message_template_event_group` g ON g.id = d.template_event_id
SET d.`app_id` = g.`app_id`
WHERE d.`app_id` IS NULL AND g.`app_id` IS NOT NULL;

-- 角色、提示词模板：无历史 app 归属时可默认 20，或按业务自行 UPDATE
-- UPDATE `role` SET `app_id` = 20 WHERE `app_id` IS NULL;
-- UPDATE `agent_prompt_template` SET `app_id` = 20 WHERE `app_id` IS NULL;

-- =============================================================================
-- 验证
-- SELECT id, app_code, app_name FROM sys_app ORDER BY sort;
-- SHOW COLUMNS FROM inside_message_record LIKE 'app_id';
-- =============================================================================