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

-- ============================================================
-- 小程序系统设置 → 支付宝小程序
-- 日期: 2026-08-21
-- 说明: 新增支付宝小程序配置表、真实组件及接口权限
-- ============================================================

CREATE TABLE IF NOT EXISTS `sa_alipay_mini_program` (
    `id`                    INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '编号',
    `partner_id`            INT(11) UNSIGNED NOT NULL                COMMENT '合伙人用户 ID',
    `mini_program_name`     VARCHAR(64)      NOT NULL                COMMENT '支付宝小程序名称',
    `appid`                 VARCHAR(64)      NOT NULL                COMMENT '支付宝小程序 APPID',
    `app_private_key`       MEDIUMTEXT       NOT NULL                COMMENT '应用私钥',
    `alipay_public_key`     MEDIUMTEXT       NOT NULL                COMMENT '支付宝公钥',
    `aes_key`               VARCHAR(128)     NOT NULL DEFAULT ''     COMMENT '接口内容加密密钥',
    `credit_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/alipay',
    `remark` = '支付宝小程序与信用服务配置',
    `update_time` = NOW()
WHERE `code` = 'AlipayMiniProgram' AND `delete_time` IS NULL;

SET @alipay_menu_id = (
    SELECT `id` FROM `sa_system_menu`
    WHERE `code` = 'AlipayMiniProgram' 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 @alipay_menu_id, '支付宝小程序列表', 'AlipayMiniProgramIndex', 'core:miniProgram:alipay:index', 3, 'GET', 100, 2, 2, 2, 2, 2, 0, 1, 1, 1, NOW(), NOW()
WHERE @alipay_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:alipay:index' 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 @alipay_menu_id, '支付宝小程序详情', 'AlipayMiniProgramRead', 'core:miniProgram:alipay:read', 3, 'GET', 90, 2, 2, 2, 2, 2, 0, 1, 1, 1, NOW(), NOW()
WHERE @alipay_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:alipay: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 @alipay_menu_id, '绑定支付宝小程序', 'AlipayMiniProgramSave', 'core:miniProgram:alipay:save', 3, 'POST', 80, 2, 2, 2, 2, 2, 0, 1, 1, 1, NOW(), NOW()
WHERE @alipay_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:alipay:save' 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 @alipay_menu_id, '编辑支付宝小程序', 'AlipayMiniProgramUpdate', 'core:miniProgram:alipay:update', 3, 'PUT', 70, 2, 2, 2, 2, 2, 0, 1, 1, 1, NOW(), NOW()
WHERE @alipay_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:alipay:update' 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 @alipay_menu_id, '删除支付宝小程序', 'AlipayMiniProgramDestroy', 'core:miniProgram:alipay:destroy', 3, 'DELETE', 60, 2, 2, 2, 2, 2, 0, 1, 1, 1, NOW(), NOW()
WHERE @alipay_menu_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:miniProgram:alipay:destroy' 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` = 'AlipayMiniProgram' 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:alipay:%' 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;