USE cattletrade;

-- 为角色分配销售概览菜单权限
-- 注意：需要根据实际情况修改 role_id

-- 1. 查询销售概览菜单ID（包括主菜单和按钮权限）
SET @salesoverview_menu_id = (SELECT id FROM sys_menu WHERE name = '销售概览' AND type = 1 AND is_delete = 0 LIMIT 1);
SET @salesoverview_add_id = (SELECT id FROM sys_menu WHERE name = '新增' AND parent_id = @salesoverview_menu_id AND type = 2 AND is_delete = 0 LIMIT 1);
SET @salesoverview_edit_id = (SELECT id FROM sys_menu WHERE name = '编辑' AND parent_id = @salesoverview_menu_id AND type = 2 AND is_delete = 0 LIMIT 1);
SET @salesoverview_delete_id = (SELECT id FROM sys_menu WHERE name = '删除' AND parent_id = @salesoverview_menu_id AND type = 2 AND is_delete = 0 LIMIT 1);
SET @salesoverview_calculate_id = (SELECT id FROM sys_menu WHERE name = '计算统计数据' AND parent_id = @salesoverview_menu_id AND type = 2 AND is_delete = 0 LIMIT 1);

-- 显示查询到的菜单ID（用于调试）
SELECT 
    @salesoverview_menu_id AS '主菜单ID',
    @salesoverview_add_id AS '新增按钮ID',
    @salesoverview_edit_id AS '编辑按钮ID',
    @salesoverview_delete_id AS '删除按钮ID',
    @salesoverview_calculate_id AS '计算按钮ID';

-- 2. 查询所有角色ID（用于批量分配）
-- SELECT id, name FROM sys_role WHERE is_delete = 0;

-- 3. 为指定角色分配销售概览菜单权限
-- 方式1：为单个角色分配（需要替换 @role_id 为实际角色ID）
-- SET @role_id = 1;  -- 替换为实际角色ID，例如：超级管理员角色ID

-- 删除该角色原有的销售概览权限（如果存在）
-- DELETE FROM sys_role_menu 
-- WHERE role_id = @role_id 
--   AND menu_id IN (@salesoverview_menu_id, @salesoverview_add_id, @salesoverview_edit_id, @salesoverview_delete_id, @salesoverview_calculate_id);

-- 插入新的权限
-- INSERT INTO sys_role_menu (role_id, menu_id)
-- VALUES 
--     (@role_id, @salesoverview_menu_id),
--     (@role_id, @salesoverview_add_id),
--     (@role_id, @salesoverview_edit_id),
--     (@role_id, @salesoverview_delete_id),
--     (@role_id, @salesoverview_calculate_id)
-- ON DUPLICATE KEY UPDATE role_id = role_id;  -- 如果已存在则忽略

-- 方式2：为所有角色分配销售概览菜单权限（谨慎使用）
-- INSERT INTO sys_role_menu (role_id, menu_id)
-- SELECT r.id, @salesoverview_menu_id
-- FROM sys_role r
-- WHERE r.is_delete = 0
--   AND NOT EXISTS (
--       SELECT 1 FROM sys_role_menu rm 
--       WHERE rm.role_id = r.id AND rm.menu_id = @salesoverview_menu_id
--   );

-- INSERT INTO sys_role_menu (role_id, menu_id)
-- SELECT r.id, @salesoverview_add_id
-- FROM sys_role r
-- WHERE r.is_delete = 0
--   AND NOT EXISTS (
--       SELECT 1 FROM sys_role_menu rm 
--       WHERE rm.role_id = r.id AND rm.menu_id = @salesoverview_add_id
--   );

-- INSERT INTO sys_role_menu (role_id, menu_id)
-- SELECT r.id, @salesoverview_edit_id
-- FROM sys_role r
-- WHERE r.is_delete = 0
--   AND NOT EXISTS (
--       SELECT 1 FROM sys_role_menu rm 
--       WHERE rm.role_id = r.id AND rm.menu_id = @salesoverview_edit_id
--   );

-- INSERT INTO sys_role_menu (role_id, menu_id)
-- SELECT r.id, @salesoverview_delete_id
-- FROM sys_role r
-- WHERE r.is_delete = 0
--   AND NOT EXISTS (
--       SELECT 1 FROM sys_role_menu rm 
--       WHERE rm.role_id = r.id AND rm.menu_id = @salesoverview_delete_id
--   );

-- INSERT INTO sys_role_menu (role_id, menu_id)
-- SELECT r.id, @salesoverview_calculate_id
-- FROM sys_role r
-- WHERE r.is_delete = 0
--   AND NOT EXISTS (
--       SELECT 1 FROM sys_role_menu rm 
--       WHERE rm.role_id = r.id AND rm.menu_id = @salesoverview_calculate_id
--   );

-- 方式3：为超级管理员角色分配（推荐使用）
-- 假设超级管理员角色ID为1（需要根据实际情况调整）
-- 如果角色ID不是1，请先查询：SELECT id, name FROM sys_role WHERE name LIKE '%管理员%' OR name LIKE '%admin%';

-- 为角色ID=1分配销售概览菜单权限（请根据实际情况修改角色ID）
INSERT INTO sys_role_menu (role_id, menu_id)
SELECT 1, @salesoverview_menu_id
WHERE NOT EXISTS (
    SELECT 1 FROM sys_role_menu rm 
    WHERE rm.role_id = 1 AND rm.menu_id = @salesoverview_menu_id
)
AND @salesoverview_menu_id IS NOT NULL;

INSERT INTO sys_role_menu (role_id, menu_id)
SELECT 1, @salesoverview_add_id
WHERE NOT EXISTS (
    SELECT 1 FROM sys_role_menu rm 
    WHERE rm.role_id = 1 AND rm.menu_id = @salesoverview_add_id
)
AND @salesoverview_add_id IS NOT NULL;

INSERT INTO sys_role_menu (role_id, menu_id)
SELECT 1, @salesoverview_edit_id
WHERE NOT EXISTS (
    SELECT 1 FROM sys_role_menu rm 
    WHERE rm.role_id = 1 AND rm.menu_id = @salesoverview_edit_id
)
AND @salesoverview_edit_id IS NOT NULL;

INSERT INTO sys_role_menu (role_id, menu_id)
SELECT 1, @salesoverview_delete_id
WHERE NOT EXISTS (
    SELECT 1 FROM sys_role_menu rm 
    WHERE rm.role_id = 1 AND rm.menu_id = @salesoverview_delete_id
)
AND @salesoverview_delete_id IS NOT NULL;

INSERT INTO sys_role_menu (role_id, menu_id)
SELECT 1, @salesoverview_calculate_id
WHERE NOT EXISTS (
    SELECT 1 FROM sys_role_menu rm 
    WHERE rm.role_id = 1 AND rm.menu_id = @salesoverview_calculate_id
)
AND @salesoverview_calculate_id IS NOT NULL;

-- 4. 验证权限分配结果
SELECT 
    rm.role_id,
    r.name AS role_name,
    rm.menu_id,
    m.name AS menu_name,
    m.type AS menu_type
FROM sys_role_menu rm
INNER JOIN sys_role r ON rm.role_id = r.id
INNER JOIN sys_menu m ON rm.menu_id = m.id
WHERE m.name = '销售概览' OR m.parent_id = (SELECT id FROM sys_menu WHERE name = '销售概览' AND type = 1 AND is_delete = 0 LIMIT 1)
ORDER BY rm.role_id, m.type, m.sort;

