water/server/plugin/saiadmin/db/20260821_add_mini_program_settings.sql

-- ============================================================
-- 小程序系统设置 → 系统设置
-- 日期: 2026-08-21
-- 说明: 新增系统设置单例配置及读写权限
-- ============================================================

CREATE TABLE IF NOT EXISTS `sa_mini_program_settings` (
    `id`          INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '编号，固定为 1',
    `settings`    MEDIUMTEXT       NOT NULL                COMMENT '小程序系统设置 JSON',
    `created_by`  INT(11)          NULL                    COMMENT '创建者',
    `updated_by`  INT(11)          NULL                    COMMENT '更新者',
    `create_time` DATETIME(0)      NULL                    COMMENT '创建时间',
    `update_time` DATETIME(0)      NULL                    COMMENT '更新时间',
    `delete_time` DATETIME(0)      NULL                    COMMENT '删除时间',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='小程序系统设置';

SET @mini_program_settings_menu_id = (
    SELECT `id` FROM `sa_system_menu`
    WHERE `code` = 'MiniProgramSettings' AND `delete_time` IS NULL
    ORDER BY `id` DESC LIMIT 1
);

INSERT INTO `sa_system_menu` (`parent_id`, `name`, `code`, `slug`, `type`, `method`, `sort`, `is_iframe`, `is_keep_alive`, `is_hidden`, `is_fixed_tab`, `is_full_page`, `generate_id`, `status`, `created_by`, `updated_by`, `create_time`, `update_time`)
SELECT @mini_program_settings_menu_id, '读取小程序系统设置', 'MiniProgramSettingsRead', 'core:miniProgram:settings:read', 3, 'GET', 100, 2, 2, 2, 2, 2, 0, 1, 1, 1, NOW(), NOW()
WHERE @mini_program_settings_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:settings:read' AND `delete_time` IS NULL);

INSERT INTO `sa_system_menu` (`parent_id`, `name`, `code`, `slug`, `type`, `method`, `sort`, `is_iframe`, `is_keep_alive`, `is_hidden`, `is_fixed_tab`, `is_full_page`, `generate_id`, `status`, `created_by`, `updated_by`, `create_time`, `update_time`)
SELECT @mini_program_settings_menu_id, '保存小程序系统设置', 'MiniProgramSettingsUpdate', 'core:miniProgram:settings:update', 3, 'PUT', 90, 2, 2, 2, 2, 2, 0, 1, 1, 1, NOW(), NOW()
WHERE @mini_program_settings_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:settings:update' AND `delete_time` IS NULL);

INSERT INTO `sa_system_role_menu` (`role_id`, `menu_id`)
SELECT DISTINCT parent_role.`role_id`, permission_menu.`id`
FROM `sa_system_role_menu` parent_role
INNER JOIN `sa_system_menu` parent_menu ON parent_menu.`id` = parent_role.`menu_id` AND parent_menu.`code` = 'MiniProgramSettings' AND parent_menu.`delete_time` IS NULL
INNER JOIN `sa_system_menu` permission_menu ON permission_menu.`parent_id` = parent_menu.`id` AND permission_menu.`slug` LIKE 'core:miniProgram:settings:%' AND permission_menu.`delete_time` IS NULL
LEFT JOIN `sa_system_role_menu` existing_role ON existing_role.`role_id` = parent_role.`role_id` AND existing_role.`menu_id` = permission_menu.`id`
WHERE existing_role.`id` IS NULL;