zkxw/docs/sql/grant_wechat_notify_menu_to_all_roles.sql

-- 将「开启微信通知」(OA007) 授予全部未删除角色
-- 前置：已执行 docs/sql/mobile_menu_oa_wechat_notify.sql（菜单须已存在）
-- 幂等：可重复执行；已有有效授权跳过；软删则恢复
-- 说明：
--   1) 用户变量显式 utf8mb4_unicode_ci，避免 collation 混用报错 1267
--   2) 主键用 MD5(role_id|menu_id)，避免 INSERT…SELECT 中 UUID() 只算一次导致重复主键

SET @menu_id = CONVERT('MINI000000000000000000000000OA007' USING utf8mb4) COLLATE utf8mb4_unicode_ci;
SET @tenant_no = CONVERT('zkxw' USING utf8mb4) COLLATE utf8mb4_unicode_ci;

-- 1) 恢复历史上已软删的授权（按 role_id + menu_id）
UPDATE sys_role_menu rm
INNER JOIN sys_role r
  ON r.id COLLATE utf8mb4_unicode_ci = rm.role_id COLLATE utf8mb4_unicode_ci
 AND r.del_flag = 0
 AND r.tenant_no COLLATE utf8mb4_unicode_ci = @tenant_no
SET
  rm.del_flag = 0,
  rm.update_time = NOW(),
  rm.remark = 'grant wechat-notify to all roles'
WHERE rm.menu_id COLLATE utf8mb4_unicode_ci = @menu_id
  AND rm.tenant_no COLLATE utf8mb4_unicode_ci = @tenant_no
  AND rm.del_flag = 1;

-- 2) 为尚未关联该菜单的角色新增授权
INSERT INTO sys_role_menu (
  id, role_id, menu_id, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT
  UPPER(MD5(CONCAT(r.id, '|', @menu_id))),
  r.id,
  @menu_id,
  @tenant_no,
  0,
  'grant wechat-notify to all roles',
  'admin',
  '',
  NOW(),
  NULL,
  NOW(),
  0
FROM sys_role r
WHERE r.del_flag = 0
  AND r.tenant_no COLLATE utf8mb4_unicode_ci = @tenant_no
  AND EXISTS (
    SELECT 1 FROM sys_menu m
    WHERE m.id COLLATE utf8mb4_unicode_ci = @menu_id
      AND m.del_flag = 0
  )
  AND NOT EXISTS (
    SELECT 1 FROM sys_role_menu rm
    WHERE rm.role_id COLLATE utf8mb4_unicode_ci = r.id COLLATE utf8mb4_unicode_ci
      AND rm.menu_id COLLATE utf8mb4_unicode_ci = @menu_id
      AND rm.tenant_no COLLATE utf8mb4_unicode_ci = @tenant_no
      AND rm.del_flag = 0
  );