HaniBlindBox/server/HoneyBox/scripts/seed_goods_management_menus.sql

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

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

-- 声明变量
DECLARE @GoodsMenuId INT;
DECLARE @SuperAdminRoleId INT;

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

-- =============================================
-- 1. 创建商品管理目录（顶级菜单）
-- =============================================
IF NOT EXISTS (SELECT 1 FROM Menus WHERE Path = '/business/goods')
BEGIN
    INSERT INTO Menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
    VALUES (0, N'商品管理', '/business/goods', 'Layout', 'Goods', 1, NULL, 20, 1, 0, 0, GETDATE());
    
    SET @GoodsMenuId = SCOPE_IDENTITY();
    PRINT N'创建商品管理目录，ID: ' + CAST(@GoodsMenuId AS VARCHAR);
END
ELSE
BEGIN
    SELECT @GoodsMenuId = Id FROM Menus WHERE Path = '/business/goods';
    -- 确保是顶级菜单
    UPDATE Menus SET ParentId = 0, Component = 'Layout' WHERE Id = @GoodsMenuId AND ParentId <> 0;
    PRINT N'商品管理目录已存在，ID: ' + CAST(@GoodsMenuId AS VARCHAR);
END

-- =============================================
-- 2. 创建商品管理子菜单
-- =============================================

-- 2.1 盒子管理
IF NOT EXISTS (SELECT 1 FROM Menus WHERE Path = '/business/goods/list')
BEGIN
    INSERT INTO Menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
    VALUES (@GoodsMenuId, N'盒子管理', '/business/goods/list', 'business/goods/index', 'Box', 2, 'goods:list', 1, 1, 0, 1, GETDATE());
    PRINT N'创建盒子管理菜单';
END

-- 2.2 盒子类型管理
IF NOT EXISTS (SELECT 1 FROM Menus WHERE Path = '/business/goods/type')
BEGIN
    INSERT INTO Menus (ParentId, Name, Path, Component, Icon, MenuType, Permission, SortOrder, Status, IsExternal, IsCache, CreatedAt)
    VALUES (@GoodsMenuId, N'盒子类型管理', '/business/goods/type', 'business/goods/type', 'Collection', 2, 'goods:type', 2, 1, 0, 1, GETDATE());
    PRINT N'创建盒子类型管理菜单';
END

-- =============================================
-- 3. 添加商品管理相关权限（如果不存在）
-- =============================================

-- 盒子类型管理权限
IF NOT EXISTS (SELECT 1 FROM Permissions WHERE Code = 'goods:type')
    INSERT INTO Permissions (Name, Code, Module, CreatedAt) VALUES (N'盒子类型管理', 'goods:type', N'商品管理', GETDATE());
IF NOT EXISTS (SELECT 1 FROM Permissions WHERE Code = 'goods:type-create')
    INSERT INTO Permissions (Name, Code, Module, CreatedAt) VALUES (N'创建盒子类型', 'goods:type-create', N'商品管理', GETDATE());
IF NOT EXISTS (SELECT 1 FROM Permissions WHERE Code = 'goods:type-edit')
    INSERT INTO Permissions (Name, Code, Module, CreatedAt) VALUES (N'编辑盒子类型', 'goods:type-edit', N'商品管理', GETDATE());
IF NOT EXISTS (SELECT 1 FROM Permissions WHERE Code = 'goods:type-delete')
    INSERT INTO Permissions (Name, Code, Module, CreatedAt) VALUES (N'删除盒子类型', 'goods:type-delete', N'商品管理', GETDATE());

-- 盒子操作权限
IF NOT EXISTS (SELECT 1 FROM Permissions WHERE Code = 'goods:copy')
    INSERT INTO Permissions (Name, Code, Module, CreatedAt) VALUES (N'复制盒子', 'goods:copy', N'商品管理', GETDATE());
IF NOT EXISTS (SELECT 1 FROM Permissions WHERE Code = 'goods:clear-lottery')
    INSERT INTO Permissions (Name, Code, Module, CreatedAt) VALUES (N'清空抽奖', 'goods:clear-lottery', N'商品管理', GETDATE());
IF NOT EXISTS (SELECT 1 FROM Permissions WHERE Code = 'goods:extend')
    INSERT INTO Permissions (Name, Code, Module, CreatedAt) VALUES (N'扩展设置', 'goods:extend', N'商品管理', GETDATE());

-- 奖品管理权限
IF NOT EXISTS (SELECT 1 FROM Permissions WHERE Code = 'goods:prize-list')
    INSERT INTO Permissions (Name, Code, Module, CreatedAt) VALUES (N'奖品列表', 'goods:prize-list', N'商品管理', GETDATE());
IF NOT EXISTS (SELECT 1 FROM Permissions WHERE Code = 'goods:prize-create')
    INSERT INTO Permissions (Name, Code, Module, CreatedAt) VALUES (N'创建奖品', 'goods:prize-create', N'商品管理', GETDATE());
IF NOT EXISTS (SELECT 1 FROM Permissions WHERE Code = 'goods:prize-edit')
    INSERT INTO Permissions (Name, Code, Module, CreatedAt) VALUES (N'编辑奖品', 'goods:prize-edit', N'商品管理', GETDATE());
IF NOT EXISTS (SELECT 1 FROM Permissions WHERE Code = 'goods:prize-delete')
    INSERT INTO Permissions (Name, Code, Module, CreatedAt) VALUES (N'删除奖品', 'goods:prize-delete', N'商品管理', GETDATE());

-- =============================================
-- 4. 为超级管理员角色分配新菜单
-- =============================================
IF @SuperAdminRoleId IS NOT NULL
BEGIN
    -- 分配商品管理目录
    IF NOT EXISTS (SELECT 1 FROM RoleMenus WHERE RoleId = @SuperAdminRoleId AND MenuId = @GoodsMenuId)
    BEGIN
        INSERT INTO RoleMenus (RoleId, MenuId) VALUES (@SuperAdminRoleId, @GoodsMenuId);
        PRINT N'为超级管理员分配商品管理目录';
    END
    
    -- 分配所有新创建的子菜单
    INSERT INTO RoleMenus (RoleId, MenuId)
    SELECT @SuperAdminRoleId, m.Id
    FROM Menus m
    WHERE m.ParentId = @GoodsMenuId
    AND NOT EXISTS (SELECT 1 FROM RoleMenus rm WHERE rm.RoleId = @SuperAdminRoleId AND rm.MenuId = m.Id);
    
    PRINT N'为超级管理员分配商品管理子菜单';
    
    -- 分配新增的权限
    INSERT INTO RolePermissions (RoleId, PermissionId)
    SELECT @SuperAdminRoleId, p.Id
    FROM Permissions p
    WHERE p.Code IN (
        'goods:type', 'goods:type-create', 'goods:type-edit', 'goods:type-delete',
        'goods:copy', 'goods:clear-lottery', 'goods:extend',
        'goods:prize-list', 'goods:prize-create', 'goods:prize-edit', 'goods:prize-delete'
    )
    AND NOT EXISTS (
        SELECT 1 FROM RolePermissions 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/goods%'
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 'goods:%'
ORDER BY p.Code;

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

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

PRINT N'';
PRINT N'商品管理模块菜单初始化完成';