-- ============================================================================
-- HRM 权限码对齐：补齐后端 @PreAuthorize 实际校验的权限码菜单，并授予「人资专员」
--
-- 问题：2026-08-12 生产库把 system_menu 里 HRM 按钮的权限码改成了前端细粒度码
--       （hrm:attendance-holiday:query、hrm:salary-calculation:calculate 等），
--       但后端 Controller 校验的是粗粒度码（hrm:attendance:query、hrm:salary:query 等），
--       角色权限只从菜单表派生 → 非超管角色打开考勤/薪酬/社保/调岗/离职/加班页面
--       一律 403「没有该操作权限」。超管走绕过校验，所以一直没暴露。
--
-- 做法：不动前端细粒度码，补一套后端实际使用的权限码按钮菜单（type=3），
--       再把这些菜单授予「人资专员」角色。幂等，可重复执行。
--       另外 HRM 各页面的「部门」下拉调用 /system/dept/list（校验 system:dept:query），
--       HR 角色默认没有系统管理权限，一并授予已有的「部门查询」按钮（菜单 1017）。
--
-- 已于 2026-10-30 在九鸿生产库（42.63.70.90:9002/ruoyi-vue-pro）执行并验证。
-- ============================================================================

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------------
-- 一、新增权限码菜单（不写死 id，按 permission 判重）
-- ---------------------------------------------------------------------------
INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '考勤查询', 'hrm:attendance:query', 3, 10, 6011, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:attendance:query' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '考勤导出', 'hrm:attendance:export', 3, 11, 6011, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:attendance:export' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '打卡（接口）', 'hrm:attendance:clock', 3, 12, 6011, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:attendance:clock' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '考勤异常提交', 'hrm:attendance:submit', 3, 10, 6012, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:attendance:submit' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '考勤新增', 'hrm:attendance:create', 3, 10, 6013, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:attendance:create' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '考勤修改', 'hrm:attendance:update', 3, 11, 6013, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:attendance:update' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '考勤删除', 'hrm:attendance:delete', 3, 12, 6013, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:attendance:delete' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '考勤统计生成（接口）', 'hrm:attendance:calculate', 3, 10, 6015, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:attendance:calculate' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '请假导出', 'hrm:leave:export', 3, 10, 6003, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:leave:export' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '调岗查询', 'hrm:transfer:query', 3, 10, 6004, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:transfer:query' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '调岗导出', 'hrm:transfer:export', 3, 11, 6004, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:transfer:export' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '离职查询', 'hrm:resignation:query', 3, 10, 6005, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:resignation:query' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '离职导出', 'hrm:resignation:export', 3, 11, 6005, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:resignation:export' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '加班查询', 'hrm:overtime:query', 3, 10, 1065211, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:overtime:query' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '合同导入', 'hrm:employee-contract:import', 3, 10, 1063105, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:employee-contract:import' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '薪资查询', 'hrm:salary:query', 3, 10, 6021, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:salary:query' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '薪资核算执行', 'hrm:salary:calculate', 3, 11, 6021, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:salary:calculate' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '薪资核算确认', 'hrm:salary:confirm', 3, 12, 6021, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:salary:confirm' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '薪资删除', 'hrm:salary:delete', 3, 13, 6021, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:salary:delete' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '薪资导出', 'hrm:salary:export', 3, 14, 6021, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:salary:export' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '薪资结构查询', 'hrm:salary-structure:query', 3, 15, 6021, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:salary-structure:query' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '薪资结构新增', 'hrm:salary-structure:create', 3, 16, 6021, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:salary-structure:create' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '薪资结构修改', 'hrm:salary-structure:update', 3, 17, 6021, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:salary-structure:update' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '薪资结构删除', 'hrm:salary-structure:delete', 3, 18, 6021, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:salary-structure:delete' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '发放生成', 'hrm:salary:generate', 3, 10, 6022, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:salary:generate' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '发放（接口）', 'hrm:salary:pay', 3, 11, 6022, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:salary:pay' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '社保查询', 'hrm:social-security:query', 3, 10, 6023, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:social-security:query' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '社保新增', 'hrm:social-security:create', 3, 11, 6023, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:social-security:create' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '社保修改', 'hrm:social-security:update', 3, 12, 6023, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:social-security:update' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '社保删除', 'hrm:social-security:delete', 3, 13, 6023, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:social-security:delete' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '个税查询', 'hrm:tax-rate:query', 3, 10, 6024, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:tax-rate:query' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '个税新增', 'hrm:tax-rate:create', 3, 11, 6024, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:tax-rate:create' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '个税修改', 'hrm:tax-rate:update', 3, 12, 6024, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:tax-rate:update' AND deleted = b'0');

INSERT INTO system_menu (name, permission, type, sort, parent_id, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
SELECT '个税删除', 'hrm:tax-rate:delete', 3, 13, 6024, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE permission = 'hrm:tax-rate:delete' AND deleted = b'0');

-- ---------------------------------------------------------------------------
-- 二、授予角色（按角色名匹配，避免不同库 role_id 不一致）
-- ---------------------------------------------------------------------------
INSERT INTO system_role_menu (role_id, menu_id, creator, create_time, updater, update_time, deleted)
SELECT r.id, m.id, '1', NOW(), '1', NOW(), b'0'
FROM system_role r
JOIN system_menu m ON m.permission IN (
    'hrm:attendance:query',
    'hrm:attendance:export',
    'hrm:attendance:clock',
    'hrm:attendance:submit',
    'hrm:attendance:create',
    'hrm:attendance:update',
    'hrm:attendance:delete',
    'hrm:attendance:calculate',
    'hrm:leave:export',
    'hrm:transfer:query',
    'hrm:transfer:export',
    'hrm:resignation:query',
    'hrm:resignation:export',
    'hrm:overtime:query',
    'hrm:employee-contract:import',
    'hrm:salary:query',
    'hrm:salary:calculate',
    'hrm:salary:confirm',
    'hrm:salary:delete',
    'hrm:salary:export',
    'hrm:salary-structure:query',
    'hrm:salary-structure:create',
    'hrm:salary-structure:update',
    'hrm:salary-structure:delete',
    'hrm:salary:generate',
    'hrm:salary:pay',
    'hrm:social-security:query',
    'hrm:social-security:create',
    'hrm:social-security:update',
    'hrm:social-security:delete',
    'hrm:tax-rate:query',
    'hrm:tax-rate:create',
    'hrm:tax-rate:update',
    'hrm:tax-rate:delete',
    -- 员工管理/考勤/排班/社保/薪酬等页面的「部门」下拉调用 /system/dept/list，
    -- 该接口校验 system:dept:query（菜单 1017「部门查询」，前端菜单树不展示按钮，不会多出菜单）
    'system:dept:query'
  )
WHERE r.name = '人资专员'
  AND r.deleted = b'0'
  AND m.deleted = b'0'
  AND NOT EXISTS (
    SELECT 1 FROM system_role_menu rm
    WHERE rm.role_id = r.id AND rm.menu_id = m.id AND rm.deleted = b'0'
  );

-- ---------------------------------------------------------------------------
-- 三、校验：应能查出 34 条
-- ---------------------------------------------------------------------------
-- SELECT m.id, m.name, m.permission, m.parent_id
-- FROM system_menu m
-- WHERE m.permission IN (
--     'hrm:attendance:query',
--         'hrm:attendance:export',
--         'hrm:attendance:clock',
--         'hrm:attendance:submit',
--         'hrm:attendance:create',
--         'hrm:attendance:update',
--         'hrm:attendance:delete',
--         'hrm:attendance:calculate',
--         'hrm:leave:export',
--         'hrm:transfer:query',
--         'hrm:transfer:export',
--         'hrm:resignation:query',
--         'hrm:resignation:export',
--         'hrm:overtime:query',
--         'hrm:employee-contract:import',
--         'hrm:salary:query',
--         'hrm:salary:calculate',
--         'hrm:salary:confirm',
--         'hrm:salary:delete',
--         'hrm:salary:export',
--         'hrm:salary-structure:query',
--         'hrm:salary-structure:create',
--         'hrm:salary-structure:update',
--         'hrm:salary-structure:delete',
--         'hrm:salary:generate',
--         'hrm:salary:pay',
--         'hrm:social-security:query',
--         'hrm:social-security:create',
--         'hrm:social-security:update',
--         'hrm:social-security:delete',
--         'hrm:tax-rate:query',
--         'hrm:tax-rate:create',
--         'hrm:tax-rate:update',
--         'hrm:tax-rate:delete'
--   ) AND m.deleted = b'0'
-- ORDER BY m.parent_id, m.id;