HaniBlindBox/server/HoneyBox/scripts/seed_diamond_management_menus.sql

-- =============================================
-- 钻石商城模块菜单初始化脚本
-- 用于在后台管理系统中添加钻石商城相关菜单
-- =============================================

-- 注意：执行此脚本前请确保：
-- 1. 已存在超级管理员角色 (Code = 'super_admin')
-- 2. 数据库中已有基础菜单数据

-- 声明变量
DECLARE @DiamondMenuId INT;
DECLARE @BusinessMenuId INT;
DECLARE @SuperAdminRoleId INT;

-- 获取超级管理员角色ID
SELECT @SuperAdminRoleId = Id FROM roles WHERE Code = 'super_admin';

-- 获取业务管理目录ID（如果存在）
SELECT @BusinessMenuId = Id FROM menus WHERE Path = '/business' AND ParentId = 0;

-- 如果业务管理目录不存在，创建它
IF @BusinessMenuId IS NULL
BEGIN
    INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
    VALUES (0, N'业务管理', '/business', 'Layout', 'Operation', 1, NULL, 30, 1, 0, 0, GETDATE());
    
    SET @BusinessMenuId = SCOPE_IDENTITY();
    PRINT N'创建业务管理目录，ID: ' + CAST(@BusinessMenuId AS VARCHAR);
END
ELSE
BEGIN
    PRINT N'业务管理目录已存在，ID: ' + CAST(@BusinessMenuId AS VARCHAR);
END

-- =============================================
-- 1. 创建钻石商城目录
-- =============================================
IF NOT EXISTS (SELECT 1 FROM menus WHERE Path = '/business/diamond')
BEGIN
    INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
    VALUES (@BusinessMenuId, N'钻石商城', '/business/diamond', NULL, 'Money', 1, NULL, 50, 1, 0, 0, GETDATE());
    
    SET @DiamondMenuId = SCOPE_IDENTITY();
    PRINT N'创建钻石商城目录，ID: ' + CAST(@DiamondMenuId AS VARCHAR);
END
ELSE
BEGIN
    SELECT @DiamondMenuId = Id FROM menus WHERE Path = '/business/diamond';
    PRINT N'钻石商城目录已存在，ID: ' + CAST(@DiamondMenuId AS VARCHAR);
END

-- =============================================
-- 2. 创建钻石商城子菜单
-- =============================================

-- 2.1 钻石商品管理
IF NOT EXISTS (SELECT 1 FROM menus WHERE Path = '/business/diamond/list')
BEGIN
    INSERT INTO menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
    VALUES (@DiamondMenuId, N'钻石商品', '/business/diamond/list', 'business/diamond/list', 'Diamond', 2, 'diamond:list', 1, 1, 0, 1, GETDATE());
    PRINT N'创建钻石商品菜单';
END

-- =============================================
-- 3. 添加钻石商城相关权限
-- =============================================

-- 钻石商品管理权限
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'diamond:list')
    INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'钻石商品列表', 'diamond:list', N'钻石商城', GETDATE());
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'diamond:add')
    INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'新增钻石商品', 'diamond:add', N'钻石商城', GETDATE());
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'diamond:edit')
    INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'编辑钻石商品', 'diamond:edit', N'钻石商城', GETDATE());
IF NOT EXISTS (SELECT 1 FROM permissions WHERE Code = 'diamond:delete')
    INSERT INTO permissions (Name, Code, Module, CreatedAt) VALUES (N'删除钻石商品', 'diamond:delete', N'钻石商城', GETDATE());

-- =============================================
-- 4. 为超级管理员角色分配新菜单和权限
-- =============================================
IF @SuperAdminRoleId IS NOT NULL
BEGIN
    -- 分配业务管理目录
    IF NOT EXISTS (SELECT 1 FROM role_menus WHERE RoleId = @SuperAdminRoleId AND MenuId = @BusinessMenuId)
    BEGIN
        INSERT INTO role_menus (RoleId, MenuId) VALUES (@SuperAdminRoleId, @BusinessMenuId);
        PRINT N'为超级管理员分配业务管理目录';
    END
    
    -- 分配钻石商城目录
    IF NOT EXISTS (SELECT 1 FROM role_menus WHERE RoleId = @SuperAdminRoleId AND MenuId = @DiamondMenuId)
    BEGIN
        INSERT INTO role_menus (RoleId, MenuId) VALUES (@SuperAdminRoleId, @DiamondMenuId);
        PRINT N'为超级管理员分配钻石商城目录';
    END
    
    -- 分配所有新创建的子菜单
    INSERT INTO role_menus (RoleId, MenuId)
    SELECT @SuperAdminRoleId, m.Id
    FROM menus m
    WHERE m.ParentId = @DiamondMenuId
    AND NOT EXISTS (SELECT 1 FROM role_menus rm WHERE rm.RoleId = @SuperAdminRoleId AND rm.MenuId = m.Id);
    
    PRINT N'为超级管理员分配钻石商城子菜单';
    
    -- 分配新增的权限
    INSERT INTO role_permissions (RoleId, PermissionId)
    SELECT @SuperAdminRoleId, p.Id
    FROM permissions p
    WHERE p.Code IN ('diamond:list', 'diamond:add', 'diamond:edit', 'diamond:delete')
    AND NOT EXISTS (
        SELECT 1 FROM role_permissions rp 
        WHERE rp.RoleId = @SuperAdminRoleId AND rp.PermissionId = p.Id
    );
    
    PRINT N'为超级管理员分配钻石商城权限';
END

-- =============================================
-- 5. 验证结果
-- =============================================
PRINT N'';
PRINT N'========== 菜单创建结果 ==========';
SELECT 
    m.Id,
    m.ParentId,
    m.Name,
    m.Path,
    m.Component,
    m.MenuType,
    m.Permission,
    m.SortOrder
FROM menus m
WHERE m.Path LIKE '/business/diamond%'
ORDER BY m.ParentId, m.SortOrder;

PRINT N'';
PRINT N'========== 权限创建结果 ==========';
SELECT 
    p.Id,
    p.Name,
    p.Code,
    p.Module
FROM permissions p
WHERE p.Code LIKE 'diamond:%'
ORDER BY p.Code;

PRINT N'';
PRINT N'========== 角色菜单分配结果 ==========';
SELECT 
    r.Name AS RoleName,
    m.Name AS MenuName,
    m.Path
FROM role_menus rm
INNER JOIN roles r ON rm.RoleId = r.Id
INNER JOIN menus m ON rm.MenuId = m.Id
WHERE m.Path LIKE '/business/diamond%'
ORDER BY r.Name, m.Path;

PRINT N'';
PRINT N'========== 角色权限分配结果 ==========';
SELECT 
    r.Name AS RoleName,
    p.Name AS PermissionName,
    p.Code
FROM role_permissions rp
INNER JOIN roles r ON rp.RoleId = r.Id
INNER JOIN permissions p ON rp.PermissionId = p.Id
WHERE p.Code LIKE 'diamond:%'
ORDER BY r.Name, p.Code;

PRINT N'';
PRINT N'钻石商城模块菜单初始化完成';