-- =============================================================================
-- 2026-09-30 公司管理员角色（company_admin）：角色创建、默认菜单、账号绑定迁移
-- -----------------------------------------------------------------------------
-- 前置说明：
--   1) 本项目 sys_menu 表无 del_flag 列，脚本中不使用逻辑删除条件
--   2) 全部语句幂等，可重复执行
--   3) 车队管理菜单树为新建（M/C/F）；卡片、白名单仅补齐缺失按钮
--   4) company_admin 默认菜单 = 车队管理 + VIN码管理 + 卡片管理（不含刷卡充电订单、
--      卡片收支明细子页）+ 白名单用户（含全部操作按钮），白名单挂载在「用户管理」下
--   5) 旧 company 角色不物理删除，迁移绑定后停用（status=1）
--   6) 【重要】后端登录下发的权限集合取自 sys_menu.path（SysMenuMapper.selectMenuPermsByUserId
--      查询的是 m.path），本项目约定「菜单 path = 前端 accessKey = 接口权限键」：
--        M 目录 path = 前端目录 accessKey（如 /fleet）；
--        C 列表页 path = 前端页面 accessKey（如 /fleet/list）；
--        F 按钮 path = 该按钮权限键（如 /fleet/add；沿用若依默认的 '#' 会导致权限键丢失）。
--      本脚本执行后请继续执行 ChargingPile/sql/20260930_company_admin_menu_path_fix.sql，
--      修正历史菜单 path（列表页 'list'、按钮 '#'、卡片管理目录 '/card'）。
-- =============================================================================
SET NAMES utf8mb4;

-- -----------------------------------------------------------------------------
-- 1. 车队管理菜单树（前端路由 /fleet -> /fleet/list；页面 accessKey=/fleet）
-- -----------------------------------------------------------------------------
-- 1.1 车队管理（目录 M）
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT '车队管理', 0, 21, '/fleet', NULL, NULL, 1, 0, 'M', '0', '0', '/fleet', 'peoples', 'admin', NOW(), '公司管理员默认菜单'
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_type = 'M' AND path = '/fleet');

-- 1.2 车队列表（菜单 C）
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT '车队列表', m.menu_id, 1, '/fleet/list', 'fleet', NULL, 1, 0, 'C', '0', '0', '/fleet/list', 'list', 'admin', NOW(), '公司管理员默认菜单'
FROM sys_menu m
WHERE m.menu_type = 'M' AND m.path = '/fleet'
  AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_type = 'C' AND perms = '/fleet/list');

-- 1.3 车队按钮（F）：与前端 accessKey 一致
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT '车队新增', c.menu_id, 1, '#', NULL, NULL, 1, 0, 'F', '0', '0', '/fleet/add', '#', 'admin', NOW(), '公司管理员默认菜单'
FROM sys_menu c
WHERE c.menu_type = 'C' AND c.perms = '/fleet/list'
  AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = '/fleet/add');

INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT '车队编辑', c.menu_id, 2, '#', NULL, NULL, 1, 0, 'F', '0', '0', '/fleet/edit', '#', 'admin', NOW(), '公司管理员默认菜单'
FROM sys_menu c
WHERE c.menu_type = 'C' AND c.perms = '/fleet/list'
  AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = '/fleet/edit');

INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT '车队删除', c.menu_id, 3, '#', NULL, NULL, 1, 0, 'F', '0', '0', '/fleet/delete', '#', 'admin', NOW(), '公司管理员默认菜单'
FROM sys_menu c
WHERE c.menu_type = 'C' AND c.perms = '/fleet/list'
  AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = '/fleet/delete');

INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT '车队启停', c.menu_id, 4, '#', NULL, NULL, 1, 0, 'F', '0', '0', '/fleet/status', '#', 'admin', NOW(), '公司管理员默认菜单'
FROM sys_menu c
WHERE c.menu_type = 'C' AND c.perms = '/fleet/list'
  AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = '/fleet/status');

INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT '车队导入', c.menu_id, 5, '#', NULL, NULL, 1, 0, 'F', '0', '0', '/fleet/import', '#', 'admin', NOW(), '公司管理员默认菜单'
FROM sys_menu c
WHERE c.menu_type = 'C' AND c.perms = '/fleet/list'
  AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = '/fleet/import');

INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT '车队导出', c.menu_id, 6, '#', NULL, NULL, 1, 0, 'F', '0', '0', '/fleet/export', '#', 'admin', NOW(), '公司管理员默认菜单'
FROM sys_menu c
WHERE c.menu_type = 'C' AND c.perms = '/fleet/list'
  AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = '/fleet/export');

-- -----------------------------------------------------------------------------
-- 2. 卡片管理：补齐解绑按钮（挂到卡片管理列表菜单 301 下）
-- -----------------------------------------------------------------------------
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT '解绑', c.menu_id, 9, '#', NULL, NULL, 1, 0, 'F', '0', '0', '/chargingCard/unbind', '#', 'admin', NOW(), '公司管理员默认菜单'
FROM sys_menu c
WHERE c.menu_type = 'C' AND c.perms = '/chargingCard/list'
  AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = '/chargingCard/unbind');

-- -----------------------------------------------------------------------------
-- 3. 白名单用户：补齐解绑、额度分配按钮（挂到白名单用户菜单 237 下）
-- -----------------------------------------------------------------------------
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT '解绑', c.menu_id, 8, '#', NULL, NULL, 1, 0, 'F', '0', '0', '/appUserWhitelist/unbind', '#', 'admin', NOW(), '公司管理员默认菜单'
FROM sys_menu c
WHERE c.menu_type = 'C' AND c.perms = '/appUserWhitelist/list'
  AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = '/appUserWhitelist/unbind');

INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
SELECT '额度分配', c.menu_id, 9, '#', NULL, NULL, 1, 0, 'F', '0', '0', '/appUserWhitelist/allocate', '#', 'admin', NOW(), '公司管理员默认菜单'
FROM sys_menu c
WHERE c.menu_type = 'C' AND c.perms = '/appUserWhitelist/list'
  AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = '/appUserWhitelist/allocate');

-- -----------------------------------------------------------------------------
-- 4. 创建公司管理员角色 company_admin（role_key/role_name 双重判重）
-- -----------------------------------------------------------------------------
INSERT INTO sys_role (role_name, role_key, data_scope, status, del_flag, create_by, create_time, remark)
SELECT '公司管理员', 'company_admin', '1', '0', '0', 'admin', NOW(), '系统内置角色'
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM sys_role WHERE del_flag = '0' AND (role_key = 'company_admin' OR role_name = '公司管理员'));

-- -----------------------------------------------------------------------------
-- 5. company_admin 默认菜单绑定（只补不删，卡片模块排除收支明细子页）
-- -----------------------------------------------------------------------------
INSERT INTO sys_role_menu (role_id, menu_id)
SELECT r.role_id, m.menu_id
FROM sys_role r
JOIN sys_menu m ON (
        m.perms LIKE '/fleet%' OR m.path LIKE '/fleet%'
     OR m.perms LIKE '/vinManagement%'
     OR (m.perms LIKE '/chargingCard%' AND m.perms NOT LIKE '/chargingCardBalanceDetail%')
     OR m.perms LIKE '/appUserWhitelist%'
)
WHERE r.del_flag = '0' AND r.role_key = 'company_admin'
  AND NOT EXISTS (SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = m.menu_id);

-- 5.1 白名单菜单挂在「用户管理」(/appUser) 下，补全其父级菜单保证菜单树可见
INSERT INTO sys_role_menu (role_id, menu_id)
SELECT r.role_id, c.parent_id
FROM sys_role r
JOIN sys_menu c ON c.menu_type = 'C' AND c.perms = '/appUserWhitelist/list'
WHERE r.del_flag = '0' AND r.role_key = 'company_admin'
  AND c.parent_id IS NOT NULL AND c.parent_id > 0
  AND NOT EXISTS (SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = c.parent_id);

-- -----------------------------------------------------------------------------
-- 6. 超级管理员（admin）同步新增菜单，保证平台可见并可分配
-- -----------------------------------------------------------------------------
INSERT INTO sys_role_menu (role_id, menu_id)
SELECT r.role_id, m.menu_id
FROM sys_role r
JOIN sys_menu m ON (
        m.path LIKE '/fleet%' OR m.perms LIKE '/fleet%'
     OR m.perms IN ('/chargingCard/unbind', '/appUserWhitelist/unbind', '/appUserWhitelist/allocate')
)
WHERE r.del_flag = '0' AND r.role_key = 'admin'
  AND NOT EXISTS (SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = m.menu_id);

-- -----------------------------------------------------------------------------
-- 7. 公司账号（roleType=5）迁移绑定 company_admin（保留原 company 绑定，双角色无冲突）
-- -----------------------------------------------------------------------------
INSERT INTO sys_user_role (user_id, role_id)
SELECT u.user_id, r.role_id
FROM sys_user u
JOIN sys_role r ON r.role_key = 'company_admin' AND r.del_flag = '0'
WHERE u.roleType = 5 AND u.del_flag = '0' AND u.objectId IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM sys_user_role ur WHERE ur.user_id = u.user_id AND ur.role_id = r.role_id);

-- -----------------------------------------------------------------------------
-- 8. 旧 company 角色停用（保留记录，不物理删除）
-- -----------------------------------------------------------------------------
UPDATE sys_role SET status = '1' WHERE role_key = 'company' AND del_flag = '0' AND status <> '1';

-- =============================================================================
-- 回查语句（执行后人工核对，不参与脚本执行）
-- =============================================================================
-- 8.1 角色
--   SELECT role_id, role_name, role_key, status, del_flag FROM sys_role WHERE role_key IN ('company', 'company_admin');
-- 8.2 车队菜单树
--   SELECT menu_id, parent_id, menu_name, path, component, perms, menu_type FROM sys_menu WHERE path LIKE '/fleet%' OR perms LIKE '/fleet%' ORDER BY menu_id;
-- 8.3 新增按钮
--   SELECT menu_id, parent_id, menu_name, perms FROM sys_menu WHERE perms IN ('/chargingCard/unbind', '/appUserWhitelist/unbind', '/appUserWhitelist/allocate');
-- 8.4 company_admin 绑定数量（测试库实测 40 条：车队 8 + VIN 10 + 卡片 11（历史存在 304/305 两个同权限"额度分配"按钮）+ 白名单 10 + 用户管理祖先 1）
--   SELECT COUNT(*) FROM sys_role_menu rm JOIN sys_role r ON r.role_id = rm.role_id WHERE r.role_key = 'company_admin';
-- 8.5 公司账号绑定
--   SELECT u.user_id, u.user_name, u.roleType, u.objectId, r.role_key FROM sys_user u
--     JOIN sys_user_role ur ON ur.user_id = u.user_id JOIN sys_role r ON r.role_id = ur.role_id
--     WHERE u.roleType = 5 AND u.delFlag = '0';
-- 8.6 确认未把收支明细/刷卡订单绑给 company_admin（应为 0）
--   SELECT COUNT(*) FROM sys_role_menu rm JOIN sys_role r ON r.role_id = rm.role_id JOIN sys_menu m ON m.menu_id = rm.menu_id
--     WHERE r.role_key = 'company_admin' AND (m.perms LIKE '/chargingCardBalanceDetail%' OR m.perms LIKE '/cardChargingOrder%');