water/server/plugin/saiadmin/db/20260902_add_rental_user_wallet_log.sql

-- 余额充值账本与权限，可重复执行。

CREATE TABLE IF NOT EXISTS `sa_rental_user_wallet_log` (
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '日志ID',
    `user_id`        BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户ID',
    `balance_type`   VARCHAR(16) NOT NULL DEFAULT 'wallet' COMMENT '账户类型 wallet/card/card_gift',
    `direction`      TINYINT NOT NULL DEFAULT 1 COMMENT '方向:1加 -1减',
    `amount`         DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '本次变动(带符号)',
    `before_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '变动前余额',
    `after_balance`  DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '变动后余额',
    `source`         VARCHAR(32) NOT NULL DEFAULT 'admin_recharge' COMMENT '来源 admin_recharge/admin_adjust/...',
    `remark`         VARCHAR(255) NOT NULL DEFAULT '' COMMENT '备注',
    `operator_id`    INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '操作管理员ID',
    `operator_name`  VARCHAR(64) NOT NULL DEFAULT '' COMMENT '操作管理员名',
    `created_at`     DATETIME NOT NULL COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_rental_wallet_log_user` (`user_id`, `created_at`),
    KEY `idx_rental_wallet_log_type` (`balance_type`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='小程序租借用户资金流水';

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 user_menu.`id`, '用户余额充值', 'RentalUserRecharge', 'core:user:list:recharge', 3,
       NULL, NULL, 'POST', NULL, 100,
       NULL, 2, 2, 2, 2, 2, 0, NULL, 1,
       '管理员对当前可见小程序租借用户的钱包余额进行加款或扣款', 1, 1, NOW(), NOW(), NULL
FROM `sa_system_menu` user_menu
WHERE user_menu.`code` = 'UserList' AND user_menu.`delete_time` IS NULL
  AND NOT EXISTS (SELECT 1 FROM `sa_system_menu` WHERE `slug` = 'core:user:list:recharge' AND `delete_time` IS NULL);

UPDATE `sa_system_menu`
SET `name` = '用户余额充值', `method` = 'POST',
    `remark` = '管理员对当前可见小程序租借用户的钱包余额进行加款或扣款', `update_time` = NOW()
WHERE `slug` = 'core:user:list:recharge' AND `delete_time` IS NULL;

-- 已有用户列表权限的角色自动获得余额充值权限。
INSERT INTO `sa_system_role_menu` (`role_id`, `menu_id`)
SELECT DISTINCT source_role.`role_id`, target_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` = 'UserList'
       AND source_menu.`delete_time` IS NULL
INNER JOIN `sa_system_menu` target_menu
        ON target_menu.`slug` = 'core:user:list:recharge'
       AND target_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` = target_menu.`id`
WHERE existing_role.`id` IS NULL;