shanxia2.0/docs/sql/migrations/2026-10-17-mini-crm-customer-webview-menus.sql

-- 小程序 CRM 客户管理 H5 入口。仅新增/恢复移动菜单及相应角色绑定，不修改 Web 菜单。
SET NAMES utf8mb4;
START TRANSACTION;

INSERT INTO sys_menu (
  id, menu_name, parent_id, menu_type, path, component, perms,
  is_frame, visible, status, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
VALUES
  ('MINI_CRM_ROOT', 'CRM管理', '1', 'M', '/mini/crm', NULL, NULL,
   1, '1', '1', 'shanxia2.0', 2, '小程序CRM管理', 'system', '', NOW(), NULL, NOW(), 0),
  ('MINI_CRM_CUSTOMER', '客户管理', 'MINI_CRM_ROOT', 'C', 'mp-webview', NULL, 'mini:crm:customer:list',
   1, '1', '1', 'shanxia2.0', 6, '小程序 WebView CRM 客户管理', 'system', '', NOW(), NULL, NOW(), 0)
ON DUPLICATE KEY UPDATE del_flag = 0, update_time = NOW();

-- 根据当前 Web 客户菜单权限查找角色，不依赖历史菜单 ID；补齐全部祖先。
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
  MD5(CONCAT(r.id, '|', target.menu_id, '|mini-crm-customer-webview|v1')),
  r.id, target.menu_id, r.tenant_no, 0, '小程序客户管理：随 Web crm:customer:list 补绑',
  'system', '', NOW(), NULL, NOW(), 0
FROM sys_role r
CROSS JOIN (
  SELECT '1' AS menu_id UNION ALL SELECT 'MINI_CRM_ROOT' UNION ALL SELECT 'MINI_CRM_CUSTOMER'
) target
WHERE r.del_flag = 0
  AND EXISTS (
    SELECT 1 FROM sys_role_menu rm
    JOIN sys_menu m ON m.id = rm.menu_id AND m.del_flag = 0
    WHERE rm.role_id = r.id AND rm.del_flag = 0 AND m.perms = 'crm:customer:list'
  )
  AND NOT EXISTS (
    SELECT 1 FROM sys_role_menu existing
    WHERE existing.role_id = r.id AND existing.menu_id = target.menu_id AND existing.del_flag = 0
  )
ON DUPLICATE KEY UPDATE del_flag = 0, update_time = NOW();

COMMIT;