water/server/plugin/saiadmin/db/20260820_add_mini_program_version.sql

-- ============================================================
-- 小程序系统设置 → 版本管理 完整迁移
-- 日期: 2026-08-20
-- 幂等: 所有 DDL / DML 均可重复执行
-- ============================================================

-- 1) 建业务表 -----------------------------------------
CREATE TABLE IF NOT EXISTS `sa_mini_program_version` (
    `id`              INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '编号',
    `version_no`      VARCHAR(32)      NOT NULL                COMMENT '版本号(如 1.0.0)',
    `version_name`    VARCHAR(64)      NOT NULL                COMMENT '版本名称',
    `platform`        VARCHAR(32)      NOT NULL                COMMENT '平台:wechat/alipay/wechat-h5/wechat-official',
    `channel`         VARCHAR(16)      NOT NULL DEFAULT 'prod' COMMENT '渠道:dev/test/prod',
    `download_url`    VARCHAR(500)     NOT NULL                COMMENT '下载地址 / 二维码内容',
    `force_update`    TINYINT(1)       NOT NULL DEFAULT 2      COMMENT '是否强制更新:1是 2否',
    `release_notes`   TEXT             NULL                    COMMENT '发布说明',
    `status`          TINYINT(1)       NOT NULL DEFAULT 1      COMMENT '状态:1启用 2禁用',
    `sort`            INT(11)          NOT NULL DEFAULT 0      COMMENT '排序',
    `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`),
    UNIQUE KEY `uk_version_no` (`version_no`, `platform`, `channel`, `delete_time`),
    KEY `idx_platform_channel` (`platform`, `channel`, `status`),
    KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='小程序版本管理';

-- 2) 写菜单(type=2 子菜单) ----------------------------
-- 父级「小程序系统设置」目录在历史迁移中已存在(path='/mini-program')
INSERT INTO `sa_system_menu` (
    `parent_id`, `name`, `code`, `slug`, `type`, `path`, `component`, `method`, `icon`, `sort`,
    `link_url`, `is_iframe`, `is_keep_alive`, `is_hidden`, `is_fixed_tab`, `is_full_page`,
    `generate_id`, `generate_key`, `status`, `remark`, `created_by`, `updated_by`, `create_time`, `update_time`, `delete_time`
)
SELECT
    (SELECT id FROM `sa_system_menu` WHERE `path` = '/mini-program' AND `delete_time` IS NULL LIMIT 1),
    CONVERT(0xE78988E69CACE7AEA1E79086 USING utf8mb4),  -- 版本管理
    'MiniProgramVersion',
    NULL,
    2,                                                 -- type=2 菜单
    'version',                                         -- 子路径(无前导 \)
    '\\mini-program\\version',                          -- 组件路径(用 \)
    NULL,
    'ri:apps-2-line',
    60,
    NULL, 2, 2, 2, 2, 2,
    0, NULL, 1, '小程序客户端版本管理', 1, 1, NOW(), NOW(), NULL
WHERE NOT EXISTS (
    SELECT 1 FROM `sa_system_menu`
    WHERE `path` = 'version' AND `delete_time` IS NULL
      AND `parent_id` = (SELECT id FROM `sa_system_menu` WHERE `path` = '/mini-program' AND `delete_time` IS NULL LIMIT 1)
);

-- 3) 写 5 个权限点(type=3 按钮) ------------------------
-- 父级 id 仍取「版本管理」菜单(用 component 定位,避免多 menu 干扰)
SET @parent_id = (
    SELECT id FROM `sa_system_menu`
    WHERE `component` = '\\mini-program\\version' AND `delete_time` IS NULL
    ORDER BY id DESC LIMIT 1
);

-- 列表
INSERT INTO `sa_system_menu` (
    `parent_id`, `name`, `code`, `slug`, `type`, `path`, `component`, `method`, `icon`, `sort`,
    `link_url`, `is_iframe`, `is_keep_alive`, `is_hidden`, `is_fixed_tab`, `is_full_page`,
    `generate_id`, `generate_key`, `status`, `remark`, `created_by`, `updated_by`, `create_time`, `update_time`, `delete_time`
)
SELECT @parent_id, CONVERT(0xE78988E69CACE7AEA1E79086E587ADE8A1A8 USING utf8mb4), 'MiniProgramVersionIndex', 'core:miniProgram:version:index', 3, NULL, NULL, NULL, 'GET', NULL, 0, NULL, 2, 2, 2, 2, 2, 0, NULL, 1, NULL, 1, 1, NOW(), NOW(), NULL
WHERE @parent_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:version:index' AND `delete_time` IS NULL);

-- 详情
INSERT INTO `sa_system_menu` (
    `parent_id`, `name`, `code`, `slug`, `type`, `path`, `component`, `method`, `icon`, `sort`,
    `link_url`, `is_iframe`, `is_keep_alive`, `is_hidden`, `is_fixed_tab`, `is_full_page`,
    `generate_id`, `generate_key`, `status`, `remark`, `created_by`, `updated_by`, `create_time`, `update_time`, `delete_time`
)
SELECT @parent_id, CONVERT(0xE78988E69CACE7AEA1E79086E8AFA6E68385 USING utf8mb4), 'MiniProgramVersionRead', 'core:miniProgram:version:read', 3, NULL, NULL, NULL, 'GET', NULL, 0, NULL, 2, 2, 2, 2, 2, 0, NULL, 1, NULL, 1, 1, NOW(), NOW(), NULL
WHERE @parent_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:version:read' AND `delete_time` IS NULL);

-- 新增
INSERT INTO `sa_system_menu` (
    `parent_id`, `name`, `code`, `slug`, `type`, `path`, `component`, `method`, `icon`, `sort`,
    `link_url`, `is_iframe`, `is_keep_alive`, `is_hidden`, `is_fixed_tab`, `is_full_page`,
    `generate_id`, `generate_key`, `status`, `remark`, `created_by`, `updated_by`, `create_time`, `update_time`, `delete_time`
)
SELECT @parent_id, CONVERT(0xE78988E69CACE7AEA1E79086E696B0E5A29E USING utf8mb4), 'MiniProgramVersionSave', 'core:miniProgram:version:save', 3, NULL, NULL, NULL, 'POST', NULL, 0, NULL, 2, 2, 2, 2, 2, 0, NULL, 1, NULL, 1, 1, NOW(), NOW(), NULL
WHERE @parent_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:version:save' AND `delete_time` IS NULL);

-- 编辑
INSERT INTO `sa_system_menu` (
    `parent_id`, `name`, `code`, `slug`, `type`, `path`, `component`, `method`, `icon`, `sort`,
    `link_url`, `is_iframe`, `is_keep_alive`, `is_hidden`, `is_fixed_tab`, `is_full_page`,
    `generate_id`, `generate_key`, `status`, `remark`, `created_by`, `updated_by`, `create_time`, `update_time`, `delete_time`
)
SELECT @parent_id, CONVERT(0xE78988E69CACE7AEA1E79086E7BC96E8BE91 USING utf8mb4), 'MiniProgramVersionUpdate', 'core:miniProgram:version:update', 3, NULL, NULL, NULL, 'PUT', NULL, 0, NULL, 2, 2, 2, 2, 2, 0, NULL, 1, NULL, 1, 1, NOW(), NOW(), NULL
WHERE @parent_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:version:update' AND `delete_time` IS NULL);

-- 删除
INSERT INTO `sa_system_menu` (
    `parent_id`, `name`, `code`, `slug`, `type`, `path`, `component`, `method`, `icon`, `sort`,
    `link_url`, `is_iframe`, `is_keep_alive`, `is_hidden`, `is_fixed_tab`, `is_full_page`,
    `generate_id`, `generate_key`, `status`, `remark`, `created_by`, `updated_by`, `create_time`, `update_time`, `delete_time`
)
SELECT @parent_id, CONVERT(0xE78988E69CACE7AEA1E79086E588A0E999A4 USING utf8mb4), 'MiniProgramVersionDestroy', 'core:miniProgram:version:destroy', 3, NULL, NULL, NULL, 'DELETE', NULL, 0, NULL, 2, 2, 2, 2, 2, 0, NULL, 1, NULL, 1, 1, NOW(), NOW(), NULL
WHERE @parent_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:version:destroy' AND `delete_time` IS NULL);