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

-- ============================================================
-- 小程序系统设置 → 微信小程序绑定
-- 日期: 2026-08-20
-- 说明: 建表、替换占位组件并补齐接口权限
-- ============================================================
CREATE TABLE IF NOT EXISTS `sa_wechat_mini_program` (
    `id`                    INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '编号',
    `partner_id`            INT(11) UNSIGNED NOT NULL                COMMENT '合伙人用户 ID',
    `partner_nickname`      VARCHAR(64)      NOT NULL DEFAULT ''     COMMENT '合伙人昵称快照',
    `partner_account`       VARCHAR(64)      NOT NULL DEFAULT ''     COMMENT '合伙人账号快照',
    `partner_name`          VARCHAR(64)      NOT NULL DEFAULT ''     COMMENT '合伙人名称快照',
    `mini_program_name`     VARCHAR(64)      NOT NULL                COMMENT '小程序名称',
    `appid`                 VARCHAR(64)      NOT NULL                COMMENT '小程序 APPID',
    `appsecret`             VARCHAR(128)     NOT NULL                COMMENT '小程序 AppSecret',
    `original_id`           VARCHAR(64)      NOT NULL                COMMENT '小程序原始 ID',
    `mchid`                 VARCHAR(32)      NOT NULL                COMMENT '微信支付商户号',
    `api_key`               VARCHAR(128)     NOT NULL                COMMENT '微信支付 API 密钥',
    `apiv3_key`             VARCHAR(128)     NOT NULL                COMMENT '微信支付 APIV3 密钥',
    `apiclient_cert`        MEDIUMTEXT       NOT NULL                COMMENT '支付证书',
    `apiclient_key`         MEDIUMTEXT       NOT NULL                COMMENT '支付证书私钥',
    `certificate_serial_no` VARCHAR(128)     NOT NULL                COMMENT '支付 API 证书序列号',
    `service_id`            VARCHAR(64)      NOT NULL                COMMENT '微信支付分账 ID',
    `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`),
    KEY `idx_partner_id` (`partner_id`, `delete_time`),
    KEY `idx_appid` (`appid`, `delete_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='微信小程序绑定配置';

-- 将原「微信小程序」占位菜单切换到真实列表/编辑组件。
UPDATE `sa_system_menu`
SET `component` = '/mini-program/wechat',
    `remark` = '微信小程序绑定与支付配置',
    `update_time` = NOW()
WHERE `code` = 'WechatMiniProgram' AND `delete_time` IS NULL;

SET @wechat_menu_id = (
    SELECT `id` FROM `sa_system_menu`
    WHERE `code` = 'WechatMiniProgram' 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 @wechat_menu_id, '微信小程序列表', 'WechatMiniProgramIndex', 'core:miniProgram:wechat:index', 3, NULL, NULL, 'GET', NULL, 100,
       NULL, 2, 2, 2, 2, 2, 0, NULL, 1, NULL, 1, 1, NOW(), NOW(), NULL
WHERE @wechat_menu_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:wechat: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 @wechat_menu_id, '微信小程序详情', 'WechatMiniProgramRead', 'core:miniProgram:wechat:read', 3, NULL, NULL, 'GET', NULL, 90,
       NULL, 2, 2, 2, 2, 2, 0, NULL, 1, NULL, 1, 1, NOW(), NOW(), NULL
WHERE @wechat_menu_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:wechat: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 @wechat_menu_id, '绑定微信小程序', 'WechatMiniProgramSave', 'core:miniProgram:wechat:save', 3, NULL, NULL, 'POST', NULL, 80,
       NULL, 2, 2, 2, 2, 2, 0, NULL, 1, NULL, 1, 1, NOW(), NOW(), NULL
WHERE @wechat_menu_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:wechat: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 @wechat_menu_id, '编辑微信小程序', 'WechatMiniProgramUpdate', 'core:miniProgram:wechat:update', 3, NULL, NULL, 'PUT', NULL, 70,
       NULL, 2, 2, 2, 2, 2, 0, NULL, 1, NULL, 1, 1, NOW(), NOW(), NULL
WHERE @wechat_menu_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:wechat: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 @wechat_menu_id, '删除微信小程序', 'WechatMiniProgramDestroy', 'core:miniProgram:wechat:destroy', 3, NULL, NULL, 'DELETE', NULL, 60,
       NULL, 2, 2, 2, 2, 2, 0, NULL, 1, NULL, 1, 1, NOW(), NOW(), NULL
WHERE @wechat_menu_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:wechat:destroy' AND `delete_time` IS NULL);