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

-- ============================================================
-- 小程序系统设置 → 模板消息 / 分享设置 / 默认设置
-- 日期: 2026-08-21
-- 说明: 新增三个真实页面所需的数据表、默认数据、菜单组件和接口权限
-- ============================================================

CREATE TABLE IF NOT EXISTS `sa_mini_program_template_message` (
    `id`           INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '编号',
    `code`         VARCHAR(64)      NOT NULL                COMMENT '业务消息编码',
    `title`        VARCHAR(100)     NOT NULL                COMMENT '标题',
    `usage_detail` VARCHAR(255)     NOT NULL DEFAULT ''     COMMENT '使用详情',
    `keywords`     VARCHAR(500)     NOT NULL DEFAULT ''     COMMENT '关键词',
    `template_id`  VARCHAR(128)     NOT NULL DEFAULT ''     COMMENT '微信模板 ID',
    `appid`        VARCHAR(64)      NOT NULL DEFAULT ''     COMMENT '微信小程序 APPID',
    `type`         VARCHAR(32)      NOT NULL DEFAULT '一次性订阅' 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_template_message_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='小程序模板消息设置';

CREATE TABLE IF NOT EXISTS `sa_mini_program_share_settings` (
    `id`           INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '编号',
    `share_name`   VARCHAR(100)     NOT NULL                COMMENT '分享名称',
    `share_title`  VARCHAR(255)     NOT NULL                COMMENT '分享标题',
    `share_image`  VARCHAR(500)     NOT NULL DEFAULT ''     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='小程序分享设置';

CREATE TABLE IF NOT EXISTS `sa_mini_program_default_settings` (
    `id`                INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '编号',
    `device_type`       VARCHAR(64)      NOT NULL                COMMENT '设备类型',
    `device_name`       VARCHAR(100)     NOT NULL                COMMENT '设备计费名称',
    `free_minutes`      INT(11) UNSIGNED NOT NULL DEFAULT 0      COMMENT '免费时长（分钟）',
    `overdue_deposit`   DECIMAL(12,2)    NOT NULL DEFAULT 15.00  COMMENT '超时订单押金',
    `min_deposit`       DECIMAL(12,2)    NOT NULL DEFAULT 3.00   COMMENT '最低押金',
    `max_deposit`       DECIMAL(12,2)    NOT NULL DEFAULT 30.00  COMMENT '最高押金',
    `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_default_setting_device_type` (`device_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='小程序默认计费设置';

-- 优先使用已经绑定的微信小程序 APPID；本地尚未绑定时使用参考页面中的 APPID。
SET @template_default_appid = (
    SELECT `appid` FROM `sa_wechat_mini_program`
    WHERE `delete_time` IS NULL AND `appid` <> ''
    ORDER BY `id` ASC LIMIT 1
);
SET @template_default_appid = COALESCE(NULLIF(@template_default_appid, ''), 'wx6b15e8b6653a0f79');

INSERT INTO `sa_mini_program_template_message`
    (`code`, `title`, `usage_detail`, `keywords`, `template_id`, `appid`, `type`, `created_by`, `updated_by`, `create_time`, `update_time`)
SELECT 'withdraw_apply', '提现申请通知', '提现到账通知', '提现金额，提现状态，到账类型，', '', @template_default_appid, '一次性订阅', 1, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM `sa_mini_program_template_message` WHERE `code` = 'withdraw_apply');

INSERT INTO `sa_mini_program_template_message`
    (`code`, `title`, `usage_detail`, `keywords`, `template_id`, `appid`, `type`, `created_by`, `updated_by`, `create_time`, `update_time`)
SELECT 'deposit_refund', '退款成功通知', '押金退还通知', '应退金额，备注，', '', @template_default_appid, '一次性订阅', 1, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM `sa_mini_program_template_message` WHERE `code` = 'deposit_refund');

INSERT INTO `sa_mini_program_template_message`
    (`code`, `title`, `usage_detail`, `keywords`, `template_id`, `appid`, `type`, `created_by`, `updated_by`, `create_time`, `update_time`)
SELECT 'rental_return', '租借归还通知', '租借归还通知', '归还地点，订单号，产生费用，收费标准，归还时间，', '', @template_default_appid, '一次性订阅', 1, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM `sa_mini_program_template_message` WHERE `code` = 'rental_return');

INSERT INTO `sa_mini_program_default_settings`
    (`device_type`, `device_name`, `free_minutes`, `overdue_deposit`, `min_deposit`, `max_deposit`, `sort`, `created_by`, `updated_by`, `create_time`, `update_time`)
SELECT 'vehicle_charger', '车载充电器计费', 0, 15.00, 3.00, 30.00, 10, 1, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM `sa_mini_program_default_settings` WHERE `device_type` = 'vehicle_charger');

INSERT INTO `sa_mini_program_default_settings`
    (`device_type`, `device_name`, `free_minutes`, `overdue_deposit`, `min_deposit`, `max_deposit`, `sort`, `created_by`, `updated_by`, `create_time`, `update_time`)
SELECT 'karaoke_device', 'K歌宝计费', 0, 15.00, 3.00, 30.00, 20, 1, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM `sa_mini_program_default_settings` WHERE `device_type` = 'karaoke_device');

INSERT INTO `sa_mini_program_default_settings`
    (`device_type`, `device_name`, `free_minutes`, `overdue_deposit`, `min_deposit`, `max_deposit`, `sort`, `created_by`, `updated_by`, `create_time`, `update_time`)
SELECT 'bluetooth_cable', '蓝牙线充计费', 0, 15.00, 3.00, 30.00, 30, 1, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM `sa_mini_program_default_settings` WHERE `device_type` = 'bluetooth_cable');

INSERT INTO `sa_mini_program_default_settings`
    (`device_type`, `device_name`, `free_minutes`, `overdue_deposit`, `min_deposit`, `max_deposit`, `sort`, `created_by`, `updated_by`, `create_time`, `update_time`)
SELECT 'power_bank', '充电宝计费', 0, 15.00, 3.00, 30.00, 40, 1, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM `sa_mini_program_default_settings` WHERE `device_type` = 'power_bank');

INSERT INTO `sa_mini_program_default_settings`
    (`device_type`, `device_name`, `free_minutes`, `overdue_deposit`, `min_deposit`, `max_deposit`, `sort`, `created_by`, `updated_by`, `create_time`, `update_time`)
SELECT 'password_cable', '密码线充计费', 0, 15.00, 3.00, 30.00, 50, 1, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM `sa_mini_program_default_settings` WHERE `device_type` = 'password_cable');

INSERT INTO `sa_mini_program_default_settings`
    (`device_type`, `device_name`, `free_minutes`, `overdue_deposit`, `min_deposit`, `max_deposit`, `sort`, `created_by`, `updated_by`, `create_time`, `update_time`)
SELECT 'password_bluetooth_cable', '密码蓝牙线充计费', 0, 15.00, 3.00, 30.00, 60, 1, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM `sa_mini_program_default_settings` WHERE `device_type` = 'password_bluetooth_cable');

-- 将三个占位菜单切换到真实组件。
UPDATE `sa_system_menu`
SET `component` = CASE `code`
        WHEN 'MiniProgramTemplateMessage' THEN '/mini-program/template-message'
        WHEN 'MiniProgramShareSettings' THEN '/mini-program/share-settings'
        WHEN 'MiniProgramDefaultSettings' THEN '/mini-program/default-settings'
    END,
    `remark` = CASE `code`
        WHEN 'MiniProgramTemplateMessage' THEN '小程序模板消息设置'
        WHEN 'MiniProgramShareSettings' THEN '小程序分享设置'
        WHEN 'MiniProgramDefaultSettings' THEN '小程序默认计费设置'
    END,
    `update_time` = NOW()
WHERE `code` IN ('MiniProgramTemplateMessage', 'MiniProgramShareSettings', 'MiniProgramDefaultSettings')
  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_menu.`id`, permission_data.`name`, permission_data.`code`, permission_data.`slug`, 3,
       NULL, NULL, permission_data.`method`, NULL, permission_data.`sort`, NULL, 2, 2, 2, 2, 2,
       0, NULL, 1, NULL, 1, 1, NOW(), NOW(), NULL
FROM `sa_system_menu` parent_menu
INNER JOIN (
    SELECT 'MiniProgramTemplateMessage' AS `parent_code`, '读取模板消息' AS `name`, 'MiniProgramTemplateMessageIndex' AS `code`, 'core:miniProgram:templateMessage:index' AS `slug`, 'GET' AS `method`, 100 AS `sort`
    UNION ALL
    SELECT 'MiniProgramTemplateMessage', '设置模板消息', 'MiniProgramTemplateMessageUpdate', 'core:miniProgram:templateMessage:update', 'PUT', 90
    UNION ALL
    SELECT 'MiniProgramShareSettings', '读取分享设置', 'MiniProgramShareSettingsIndex', 'core:miniProgram:shareSettings:index', 'GET', 100
    UNION ALL
    SELECT 'MiniProgramShareSettings', '设置分享内容', 'MiniProgramShareSettingsUpdate', 'core:miniProgram:shareSettings:update', 'PUT', 90
    UNION ALL
    SELECT 'MiniProgramDefaultSettings', '读取默认设置', 'MiniProgramDefaultSettingsIndex', 'core:miniProgram:defaultSettings:index', 'GET', 100
    UNION ALL
    SELECT 'MiniProgramDefaultSettings', '保存默认设置', 'MiniProgramDefaultSettingsUpdate', 'core:miniProgram:defaultSettings:update', 'PUT', 90
) permission_data ON permission_data.`parent_code` = parent_menu.`code`
LEFT JOIN `sa_system_menu` existing_menu
       ON existing_menu.`slug` = permission_data.`slug` AND existing_menu.`delete_time` IS NULL
WHERE parent_menu.`delete_time` IS NULL
  AND existing_menu.`id` 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` IN ('MiniProgramTemplateMessage', 'MiniProgramShareSettings', 'MiniProgramDefaultSettings')
       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.`type` = 3
       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;