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

-- ============================================================
-- 小程序系统设置 → 微信公众号配置
-- 日期: 2026-08-21
-- 说明: 新增公众号单例配置表、切换真实组件并补齐接口权限
-- ============================================================

CREATE TABLE IF NOT EXISTS `sa_wechat_official_account` (
    `id`                    INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '编号，固定为 1',
    `logo`                  VARCHAR(500)     NOT NULL DEFAULT ''     COMMENT '公众号 Logo',
    `account_name`          VARCHAR(64)      NOT NULL DEFAULT ''     COMMENT '公众号名称',
    `description`           VARCHAR(255)     NOT NULL DEFAULT ''     COMMENT '公众号说明',
    `follow_message`        VARCHAR(255)     NOT NULL DEFAULT ''     COMMENT '关注引导语',
    `follow_url`            VARCHAR(500)     NOT NULL DEFAULT ''     COMMENT '关注链接',
    `follow_enabled`        TINYINT(1)       NOT NULL DEFAULT 0      COMMENT '是否开启关注',
    `appid`                 VARCHAR(64)      NOT NULL DEFAULT ''     COMMENT '公众号 APPID',
    `original_id`           VARCHAR(64)      NOT NULL DEFAULT ''     COMMENT '公众号原始 ID',
    `appsecret`             VARCHAR(128)     NOT NULL DEFAULT ''     COMMENT '公众号 AppSecret',
    `server_url`            VARCHAR(500)     NOT NULL DEFAULT ''     COMMENT '微信服务器地址',
    `token`                 VARCHAR(128)     NOT NULL DEFAULT ''     COMMENT '消息校验令牌',
    `encoding_aes_key`      VARCHAR(128)     NOT NULL DEFAULT ''     COMMENT '消息加解密密钥',
    `encryption_mode`       TINYINT(1)       NOT NULL DEFAULT 1      COMMENT '加密模式：1明文 2兼容 3安全',
    `free_recharge_times`   INT(11) UNSIGNED NOT NULL DEFAULT 0      COMMENT '免费充值次数',
    `follow_free_recharge`  TINYINT(1)       NOT NULL DEFAULT 0      COMMENT '关注后免费充值',
    `all_merchant_follow`   TINYINT(1)       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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='微信公众号配置';

UPDATE `sa_system_menu`
SET `component` = '/mini-program/wechat-official-account',
    `remark` = '微信公众号基础信息与接口配置',
    `update_time` = NOW()
WHERE `code` = 'WechatOfficialAccount' AND `delete_time` IS NULL;

SET @official_account_menu_id = (
    SELECT `id` FROM `sa_system_menu`
    WHERE `code` = 'WechatOfficialAccount' 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 @official_account_menu_id, '读取微信公众号配置', 'WechatOfficialAccountRead', 'core:miniProgram:officialAccount:read', 3, NULL, NULL, 'GET', NULL, 100,
       NULL, 2, 2, 2, 2, 2, 0, NULL, 1, NULL, 1, 1, NOW(), NOW(), NULL
WHERE @official_account_menu_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:officialAccount: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 @official_account_menu_id, '保存微信公众号配置', 'WechatOfficialAccountUpdate', 'core:miniProgram:officialAccount:update', 3, NULL, NULL, 'PUT', NULL, 90,
       NULL, 2, 2, 2, 2, 2, 0, NULL, 1, NULL, 1, 1, NOW(), NOW(), NULL
WHERE @official_account_menu_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:officialAccount: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` = 'WechatOfficialAccount'
       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:officialAccount:%'
       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;