yhlxj/docs/sql/20261021-coupon-exchange-permissions.sql

-- Explicit comparison collation avoids MySQL 1267 across database/session defaults.
-- 优惠券 + 商品兑换码权限。请在应用数据库执行；重复串行执行不会重复插入。
-- 如自动识别失败/存在同名菜单，请将下面两个 NULL 改成实际父菜单 id。
-- 不创建前端路由，不改已有权限，不给普通角色扩权。
SET @coupon_parent_override = NULL;
SET @exchange_parent_override = NULL;
-- 自动识别用户列表；匹配不唯一时填写实际用户列表菜单 ID。
SET @balance_parent_override = NULL;
SET @balance_parent = COALESCE(@balance_parent_override, (
    SELECT CASE WHEN COUNT(*) = 1 THEN MIN(id) ELSE NULL END
    FROM cms_menu WHERE type = 'menu' AND (title = '用户列表' OR path = '/userManage/user')
));

SET @coupon_parent = COALESCE(@coupon_parent_override, (
    SELECT CASE WHEN COUNT(*) = 1 THEN MIN(id) ELSE NULL END
    FROM cms_menu WHERE type <> 'button' AND (title = '优惠券' OR menu_name = '优惠券')
));
SET @exchange_parent = COALESCE(@exchange_parent_override, (
    SELECT CASE WHEN COUNT(*) = 1 THEN MIN(id) ELSE NULL END
    FROM cms_menu WHERE type <> 'button' AND (title = '兑换码' OR menu_name = '兑换码')
));

DROP TEMPORARY TABLE IF EXISTS tmp_coupon_exchange_permissions;
CREATE TEMPORARY TABLE tmp_coupon_exchange_permissions (
    title VARCHAR(64) NOT NULL,
    perms VARCHAR(64) NOT NULL PRIMARY KEY,
    parent_id BIGINT NULL,
    sort_num INT NOT NULL
);
INSERT INTO tmp_coupon_exchange_permissions VALUES
('优惠券查询', 'coupon:view', @coupon_parent, 1),
('优惠券新增', 'coupon:add', @coupon_parent, 2),
('优惠券编辑及配置', 'coupon:edit', @coupon_parent, 3),
('优惠券删除', 'coupon:delete', @coupon_parent, 4),
('优惠券发放', 'coupon:send', @coupon_parent, 5),
('兑换码查询', 'exchangeCode:view', @exchange_parent, 1),
('兑换码生成', 'exchangeCode:add', @exchange_parent, 2),
('兑换码导出', 'exchangeCode:export', @exchange_parent, 3),
('兑换码退款重发', 'exchangeCode:refund', @exchange_parent, 4),
('增加用户余额', 'userBalance:add', @balance_parent, 1),
('扣除用户余额', 'userBalance:deduct', @balance_parent, 2),
('余额操作记录', 'userBalance:view', @balance_parent, 3);

START TRANSACTION;
INSERT INTO cms_menu
    (title, menu_name, perms, type, path, order_by, parent_id, created_time, update_time)
SELECT p.title, p.perms, p.perms, 'button', '', p.sort_num, p.parent_id, NOW(), NOW()
FROM tmp_coupon_exchange_permissions p
INNER JOIN cms_menu parent ON parent.id = p.parent_id AND parent.type <> 'button'
WHERE NOT EXISTS (SELECT 1 FROM cms_menu existing WHERE existing.perms = CONVERT(p.perms USING utf8mb4) COLLATE utf8mb4_unicode_ci);

-- 只授权名称精确为 root 的角色；其他角色在后台角色管理中显式授权。
INSERT INTO cms_role_menu (role_id, menu_id, created_time, update_time)
SELECT r.id, m.id, NOW(), NOW()
FROM cms_role r
INNER JOIN cms_menu m ON 1 = 1
INNER JOIN tmp_coupon_exchange_permissions p ON CONVERT(p.perms USING utf8mb4) COLLATE utf8mb4_unicode_ci = m.perms
WHERE r.role_name = 'root'
  AND NOT EXISTS (
      SELECT 1 FROM cms_role_menu existing
      WHERE existing.role_id = r.id AND existing.menu_id = m.id
  );
COMMIT;

-- 检查结果：12 个权限均应有 menu_id；缺失时指定上面的父菜单 id 再执行。
SELECT p.perms, m.id AS menu_id, m.parent_id,
       CASE WHEN m.id IS NULL THEN 'MISSING: set parent override and rerun' ELSE 'OK' END AS result
FROM tmp_coupon_exchange_permissions p
LEFT JOIN cms_menu m ON m.perms = CONVERT(p.perms USING utf8mb4) COLLATE utf8mb4_unicode_ci
ORDER BY p.perms;

SELECT r.id AS role_id, r.role_name, m.perms
FROM cms_role_menu rm
INNER JOIN cms_role r ON r.id = rm.role_id
INNER JOIN cms_menu m ON m.id = rm.menu_id
INNER JOIN tmp_coupon_exchange_permissions p ON CONVERT(p.perms USING utf8mb4) COLLATE utf8mb4_unicode_ci = m.perms
WHERE r.role_name = 'root'
ORDER BY m.perms;

DROP TEMPORARY TABLE tmp_coupon_exchange_permissions;
-- 执行后通过后台重新保存受影响角色权限以触发缓存刷新，再重新登录验证。
-- 本文件不自动连接生产库，也不删除异常优惠券或历史数据。