water/server/plugin/saiadmin/db/20260822_add_merchant_withdrawal_setting.sql

-- ============================================================
-- 商户可提现金额更新配置
-- 日期: 2026-08-22
-- 来源: 旧项目 xs_admin_user.crond_money_type / crond_once_unit_day
-- 说明: 可重复执行，不修改现有商户的默认行为。
-- ============================================================

SET NAMES utf8mb4;

SET @has_withdraw_mode = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'sa_merchant'
      AND COLUMN_NAME = 'withdraw_update_mode'
);
SET @add_withdraw_mode_sql = IF(
    @has_withdraw_mode = 0,
    'ALTER TABLE `sa_merchant` ADD COLUMN `withdraw_update_mode` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT ''可提现金额更新：0实时、1定时'' AFTER `withdrawable_amount`',
    'SELECT 1'
);
PREPARE add_withdraw_mode_stmt FROM @add_withdraw_mode_sql;
EXECUTE add_withdraw_mode_stmt;
DEALLOCATE PREPARE add_withdraw_mode_stmt;

SET @has_withdraw_day = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'sa_merchant'
      AND COLUMN_NAME = 'withdraw_update_day'
);
SET @add_withdraw_day_sql = IF(
    @has_withdraw_day = 0,
    'ALTER TABLE `sa_merchant` ADD COLUMN `withdraw_update_day` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT ''定时更新日期：1-31，0为每月最后一天'' AFTER `withdraw_update_mode`',
    'SELECT 1'
);
PREPARE add_withdraw_day_stmt FROM @add_withdraw_day_sql;
EXECUTE add_withdraw_day_stmt;
DEALLOCATE PREPARE add_withdraw_day_stmt;

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, permission_data.`remark`, 1, 1, NOW(), NOW(), NULL
FROM `sa_system_menu` parent_menu
INNER JOIN (
    SELECT '商户提现更新配置' AS `name`, 'MerchantWithdrawalSettingRead' AS `code`, 'core:merchant:withdrawalSetting:read' AS `slug`, 'GET' AS `method`, 45 AS `sort`, '读取有权访问商户的提现更新设置' AS `remark`
    UNION ALL SELECT '保存商户提现更新配置', 'MerchantWithdrawalSettingUpdate', 'core:merchant:withdrawalSetting:update', 'PUT', 44, '保存实时或每月定时更新方式'
) permission_data
LEFT JOIN `sa_system_menu` existing_menu
       ON existing_menu.`slug` = permission_data.`slug`
      AND existing_menu.`delete_time` IS NULL
WHERE parent_menu.`code` = 'Merchant'
  AND parent_menu.`delete_time` IS NULL
  AND existing_menu.`id` IS NULL;

INSERT INTO `sa_system_role_menu` (`role_id`, `menu_id`)
SELECT DISTINCT source_role.`role_id`, permission_menu.`id`
FROM `sa_system_role_menu` source_role
INNER JOIN `sa_system_menu` source_menu
        ON source_menu.`id` = source_role.`menu_id`
       AND source_menu.`code` IN ('MyMerchant', 'SubordinateMerchant')
       AND source_menu.`delete_time` IS NULL
INNER JOIN `sa_system_menu` merchant_menu
        ON merchant_menu.`code` = 'Merchant'
       AND merchant_menu.`delete_time` IS NULL
INNER JOIN `sa_system_menu` permission_menu
        ON permission_menu.`parent_id` = merchant_menu.`id`
       AND permission_menu.`slug` IN (
           'core:merchant:withdrawalSetting:read',
           'core:merchant:withdrawalSetting:update'
       )
       AND permission_menu.`delete_time` IS NULL
LEFT JOIN `sa_system_role_menu` existing_role
       ON existing_role.`role_id` = source_role.`role_id`
      AND existing_role.`menu_id` = permission_menu.`id`
WHERE existing_role.`id` IS NULL;