-- Novalon管理系统普通用户角色和数据
-- 版本: V10
-- 描述: 创建普通用户角色并分配权限

-- 插入普通用户角色
INSERT INTO sys_role (role_name, role_key, role_sort, status, create_by, update_by) 
VALUES ('普通用户', 'user', 2, 1, 'system', 'system')
ON CONFLICT (role_key) DO UPDATE SET 
    role_name = EXCLUDED.role_name,
    role_sort = EXCLUDED.role_sort,
    status = EXCLUDED.status;

-- 为普通用户分配基本权限（查看个人信息、修改密码等）
-- 注意：这里只分配基本权限，不包含管理功能权限
INSERT INTO sys_permission (permission_name, permission_key, permission_type, parent_id, path, component, icon, sort, status, create_by, update_by)
VALUES 
('个人中心', 'profile', 'MENU', 0, '/profile', 'views/profile/index', 'user', 1, 1, 'system', 'system'),
('个人信息', 'profile:info', 'BUTTON', (SELECT id FROM sys_permission WHERE permission_key = 'profile'), '', '', '', 1, 1, 'system', 'system'),
('修改密码', 'profile:password', 'BUTTON', (SELECT id FROM sys_permission WHERE permission_key = 'profile'), '', '', '', 2, 1, 'system', 'system')
ON CONFLICT (permission_key) DO NOTHING;

-- 为普通用户角色分配权限
INSERT INTO sys_role_permission (role_id, permission_id, create_by, update_by)
SELECT 
    r.id as role_id,
    p.id as permission_id,
    'system' as create_by,
    'system' as update_by
FROM sys_role r
CROSS JOIN sys_permission p
WHERE r.role_key = 'user' 
    AND p.permission_key IN ('profile', 'profile:info', 'profile:password')
ON CONFLICT DO NOTHING;

-- 将测试用户分配给普通用户角色
INSERT INTO user_role (user_id, role_id, create_by, update_by)
SELECT 
    u.id as user_id,
    r.id as role_id,
    'system' as create_by,
    'system' as update_by
FROM sys_user u
CROSS JOIN sys_role r
WHERE u.username = 'user' AND r.role_key = 'user'
ON CONFLICT DO NOTHING;

-- 重置序列值
SELECT setval('sys_role_id_seq', (SELECT COALESCE(MAX(id), 1) FROM sys_role));
SELECT setval('sys_permission_id_seq', (SELECT COALESCE(MAX(id), 1) FROM sys_permission));
SELECT setval('sys_role_permission_id_seq', (SELECT COALESCE(MAX(id), 1) FROM sys_role_permission));
SELECT setval('user_role_id_seq', (SELECT COALESCE(MAX(id), 1) FROM user_role));
