eduepos-group/docs/inspect-permission.sql

-- Menu and button permissions for Store Inspection & Assessment.
-- Run this script in database `eduepos-plat`.
--
-- 结构：独立顶级模块「巡检与考核」(sys_model INSPECT)，其下 5 个子菜单：
--   考核指标配置  /admin/inspect
--   巡检计划管理  /admin/inspect-plan
--   巡检任务执行  /admin/inspect-task
--   整改流程管理  /admin/inspect-rect
--   考核结果排名  /admin/inspect-assess
-- 说明：侧边栏菜单来自登录时按角色查询的 SYS_USER_MENU_V（基于 sys_role），
-- 必须同时把菜单/按钮授权给角色（sys_role / sys_role_menu_button）才能显示。
-- 脚本幂等，可重复执行。

-- 1. Create the INSPECT model (top-level module group) if not exists.
INSERT INTO sys_model (MODEL_CODE, MODEL_NAME, STATUS, CREATE_DATE, RMK)
SELECT 'INSPECT', '巡检与考核', 1, NOW(), 'el-icon-s-platform'
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM sys_model WHERE MODEL_CODE = 'INSPECT');

-- 2. Re-point the existing /admin/inspect menu as the first sub-menu under INSPECT.
UPDATE sys_menu
SET MODEL_CODE = 'INSPECT',
    MENU_NAME = '考核指标配置',
    LEVEL = 2,
    FATHER_ID = NULL,
    ORDER_KEY = 1,
    STATUS = 1
WHERE URL = '/admin/inspect';

-- 3. Insert the remaining four sub-menus under INSPECT (skip if already exists).
INSERT INTO sys_menu (MODEL_CODE, MENU_NAME, URL, LEVEL, ORDER_KEY, FATHER_ID, STATUS, CREATE_DATE)
SELECT 'INSPECT', '巡检计划管理', '/admin/inspect-plan', 2, 2, NULL, 1, NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE URL = '/admin/inspect-plan');

INSERT INTO sys_menu (MODEL_CODE, MENU_NAME, URL, LEVEL, ORDER_KEY, FATHER_ID, STATUS, CREATE_DATE)
SELECT 'INSPECT', '巡检任务执行', '/admin/inspect-task', 2, 3, NULL, 1, NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE URL = '/admin/inspect-task');

INSERT INTO sys_menu (MODEL_CODE, MENU_NAME, URL, LEVEL, ORDER_KEY, FATHER_ID, STATUS, CREATE_DATE)
SELECT 'INSPECT', '整改流程管理', '/admin/inspect-rect', 2, 4, NULL, 1, NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE URL = '/admin/inspect-rect');

INSERT INTO sys_menu (MODEL_CODE, MENU_NAME, URL, LEVEL, ORDER_KEY, FATHER_ID, STATUS, CREATE_DATE)
SELECT 'INSPECT', '考核结果排名', '/admin/inspect-assess', 2, 5, NULL, 1, NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE URL = '/admin/inspect-assess');

-- 3.5 Clean up stray buttons left on the re-pointed indicator menu from an earlier broad grant.
DELETE FROM sys_role_menu_button
WHERE MENU_ID = (SELECT MENU_ID FROM sys_menu WHERE URL = '/admin/inspect')
  AND BUTTON_CODE NOT IN ('add', 'edit', 'delete', 'weight');

DELETE FROM sys_menu_button
WHERE MENU_ID = (SELECT MENU_ID FROM sys_menu WHERE URL = '/admin/inspect')
  AND BUTTON_CODE NOT IN ('add', 'edit', 'delete', 'weight');

-- 4. Register button permissions for each menu.
INSERT INTO sys_menu_button (MENU_ID, BUTTON_CODE)
SELECT m.MENU_ID, b.BUTTON_CODE
FROM sys_menu m
JOIN (
    SELECT '/admin/inspect' AS url, 'add' AS button_code
    UNION ALL SELECT '/admin/inspect', 'edit'
    UNION ALL SELECT '/admin/inspect', 'delete'
    UNION ALL SELECT '/admin/inspect', 'weight'
    UNION ALL SELECT '/admin/inspect-plan', 'add'
    UNION ALL SELECT '/admin/inspect-plan', 'edit'
    UNION ALL SELECT '/admin/inspect-plan', 'delete'
    UNION ALL SELECT '/admin/inspect-plan', 'publish'
    UNION ALL SELECT '/admin/inspect-plan', 'pause'
    UNION ALL SELECT '/admin/inspect-plan', 'finish'
    UNION ALL SELECT '/admin/inspect-plan', 'generate'
    UNION ALL SELECT '/admin/inspect-task', 'execute'
    UNION ALL SELECT '/admin/inspect-task', 'submit'
    UNION ALL SELECT '/admin/inspect-task', 'cancel'
    UNION ALL SELECT '/admin/inspect-rect', 'recheck'
    UNION ALL SELECT '/admin/inspect-rect', 'urge'
    UNION ALL SELECT '/admin/inspect-rect', 'batch'
    UNION ALL SELECT '/admin/inspect-assess', 'export'
    UNION ALL SELECT '/admin/inspect-assess', 'trigger'
) b ON b.url = m.URL
WHERE NOT EXISTS (
    SELECT 1 FROM sys_menu_button exists_button
    WHERE exists_button.MENU_ID = m.MENU_ID
      AND exists_button.BUTTON_CODE = b.BUTTON_CODE
);

-- 5. Grant all five inspect menus to every role that already has the store management menu.
INSERT INTO sys_role (CHARACTOR_ID, MENU_ID)
SELECT
    old_role.CHARACTOR_ID,
    new_menu.MENU_ID
FROM sys_role old_role
JOIN sys_menu old_menu ON old_menu.MENU_ID = old_role.MENU_ID
JOIN sys_menu new_menu ON new_menu.URL IN ('/admin/inspect', '/admin/inspect-plan', '/admin/inspect-task', '/admin/inspect-rect', '/admin/inspect-assess')
WHERE old_menu.URL = '/admin/storemanage'
  AND NOT EXISTS (
      SELECT 1 FROM sys_role exists_role
      WHERE exists_role.CHARACTOR_ID = old_role.CHARACTOR_ID
        AND exists_role.MENU_ID = new_menu.MENU_ID
  );

-- 6. Fallback: if NO role has the store management menu, grant the five inspect menus to all roles.
INSERT INTO sys_role (CHARACTOR_ID, MENU_ID)
SELECT
    all_role.CHARACTOR_ID,
    new_menu.MENU_ID
FROM (SELECT DISTINCT CHARACTOR_ID FROM sys_role) all_role
JOIN sys_menu new_menu ON new_menu.URL IN ('/admin/inspect', '/admin/inspect-plan', '/admin/inspect-task', '/admin/inspect-rect', '/admin/inspect-assess')
WHERE (SELECT COUNT(*) FROM sys_role r JOIN sys_menu m ON m.MENU_ID = r.MENU_ID WHERE m.URL = '/admin/storemanage') = 0
  AND NOT EXISTS (
      SELECT 1 FROM sys_role exists_role
      WHERE exists_role.CHARACTOR_ID = all_role.CHARACTOR_ID
        AND exists_role.MENU_ID = new_menu.MENU_ID
  );

-- 7. Grant matching button permissions to the roles that got the inspect menus.
INSERT INTO sys_role_menu_button (MENU_ID, ROLE_ID, BUTTON_CODE)
SELECT
    new_menu.MENU_ID,
    role.CHARACTOR_ID,
    button.BUTTON_CODE
FROM sys_role role
JOIN sys_menu new_menu ON new_menu.URL IN ('/admin/inspect', '/admin/inspect-plan', '/admin/inspect-task', '/admin/inspect-rect', '/admin/inspect-assess')
JOIN sys_menu_button button ON button.MENU_ID = new_menu.MENU_ID
WHERE role.MENU_ID = new_menu.MENU_ID
  AND NOT EXISTS (
      SELECT 1 FROM sys_role_menu_button exists_button
      WHERE exists_button.MENU_ID = new_menu.MENU_ID
        AND exists_button.ROLE_ID = role.CHARACTOR_ID
        AND exists_button.BUTTON_CODE = button.BUTTON_CODE
  );

-- 8. Verify model, menus, buttons and granted roles.
SELECT mo.MODEL_CODE, mo.MODEL_NAME, m.MENU_ID, m.MENU_NAME, m.URL, m.ORDER_KEY, b.BUTTON_CODE, r.CHARACTOR_ID
FROM sys_model mo
LEFT JOIN sys_menu m ON m.MODEL_CODE = mo.MODEL_CODE
LEFT JOIN sys_menu_button b ON b.MENU_ID = m.MENU_ID
LEFT JOIN sys_role r ON r.MENU_ID = m.MENU_ID
WHERE mo.MODEL_CODE = 'INSPECT'
ORDER BY m.ORDER_KEY, b.BUTTON_CODE, r.CHARACTOR_ID;