-- 清空数据库所有表数据脚本
-- 适配 PostgreSQL 17

-- 开始事务
BEGIN;

-- 禁用触发器
SET session_replication_role = 'off';

-- 清空所有表数据（按依赖关系顺序）
TRUNCATE TABLE sys_operation_log CASCADE;
TRUNCATE TABLE sys_login_log CASCADE;
TRUNCATE TABLE sys_exception_log CASCADE;
TRUNCATE TABLE sys_notice CASCADE;
TRUNCATE TABLE sys_file CASCADE;
TRUNCATE TABLE sys_config CASCADE;
TRUNCATE TABLE sys_dict_data CASCADE;
TRUNCATE TABLE sys_dict_type CASCADE;
TRUNCATE TABLE sys_role_menu CASCADE;
TRUNCATE TABLE sys_role_permission CASCADE;
TRUNCATE TABLE user_role CASCADE;
TRUNCATE TABLE sys_user CASCADE;
TRUNCATE TABLE sys_role CASCADE;
TRUNCATE TABLE sys_permission CASCADE;
TRUNCATE TABLE sys_menu CASCADE;

-- 重置序列
ALTER SEQUENCE sys_user_id_seq RESTART WITH 1;
ALTER SEQUENCE sys_role_id_seq RESTART WITH 1;
ALTER SEQUENCE sys_permission_id_seq RESTART WITH 1;
ALTER SEQUENCE sys_menu_id_seq RESTART WITH 1;
ALTER SEQUENCE sys_operation_log_id_seq RESTART WITH 1;
ALTER SEQUENCE sys_login_log_id_seq RESTART WITH 1;
ALTER SEQUENCE sys_exception_log_id_seq RESTART WITH 1;
ALTER SEQUENCE sys_notice_id_seq RESTART WITH 1;
ALTER SEQUENCE sys_file_id_seq RESTART WITH 1;
ALTER SEQUENCE sys_config_id_seq RESTART WITH 1;
ALTER SEQUENCE sys_dict_type_id_seq RESTART WITH 1;
ALTER SEQUENCE sys_dict_data_id_seq RESTART WITH 1;
ALTER SEQUENCE user_role_id_seq RESTART WITH 1;
ALTER SEQUENCE sys_role_menu_id_seq RESTART WITH 1;
ALTER SEQUENCE sys_role_permission_id_seq RESTART WITH 1;

-- 插入初始管理员用户（密码：admin123）
INSERT INTO sys_user (username, password, email, phone, nickname, status, role_id, create_by, update_by)
VALUES ('admin', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9lBOsl7iAt6Z5EH', 'admin@example.com', '13800138000', '系统管理员', 1, 1, 'system', 'system');

-- 插入初始角色
INSERT INTO sys_role (role_name, role_key, role_sort, status, create_by, update_by)
VALUES ('超级管理员', 'admin', 1, 1, 'system', 'system');

INSERT INTO sys_role (role_name, role_key, role_sort, status, create_by, update_by)
VALUES ('普通用户', 'user', 2, 1, 'system', 'system');

-- 更新管理员用户的角色关联
INSERT INTO user_role (user_id, role_id, created_by)
SELECT u.id, r.id, 'system'
FROM sys_user u, sys_role r
WHERE u.username = 'admin' AND r.role_key = 'admin';

-- 插入基础菜单
INSERT INTO sys_menu (menu_name, parent_id, order_num, menu_type, perms, component, status, create_by, update_by)
VALUES 
('系统管理', 0, 1, 'M', NULL, NULL, 1, 'system', 'system'),
('用户管理', 1, 1, 'C', 'sys:user:list', 'system/user/index', 1, 'system', 'system'),
('角色管理', 1, 2, 'C', 'sys:role:list', 'system/role/index', 1, 'system', 'system'),
('菜单管理', 1, 3, 'C', 'sys:menu:list', 'system/menu/index', 1, 'system', 'system');

-- 插入基础权限
INSERT INTO sys_permission (permission_name, permission_code, resource, action, description, status, create_by, update_by)
VALUES 
('用户查看', 'sys:user:view', '/api/users', 'GET', '查看用户列表', 1, 'system', 'system'),
('用户新增', 'sys:user:add', '/api/users', 'POST', '新增用户', 1, 'system', 'system'),
('用户编辑', 'sys:user:edit', '/api/users/*', 'PUT', '编辑用户', 1, 'system', 'system'),
('用户删除', 'sys:user:delete', '/api/users/*', 'DELETE', '删除用户', 1, 'system', 'system'),
('角色查看', 'sys:role:view', '/api/roles', 'GET', '查看角色列表', 1, 'system', 'system'),
('角色新增', 'sys:role:add', '/api/roles', 'POST', '新增角色', 1, 'system', 'system'),
('角色编辑', 'sys:role:edit', '/api/roles/*', 'PUT', '编辑角色', 1, 'system', 'system'),
('角色删除', 'sys:role:delete', '/api/roles/*', 'DELETE', '删除角色', 1, 'system', 'system');

-- 为管理员角色分配所有权限
INSERT INTO sys_role_permission (role_id, permission_id, create_by, update_by)
SELECT r.id, p.id, 'system', 'system'
FROM sys_role r, sys_permission p
WHERE r.role_key = 'admin';

-- 为管理员角色分配所有菜单
INSERT INTO sys_role_menu (role_id, menu_id, create_by, update_by)
SELECT r.id, m.id, 'system', 'system'
FROM sys_role r, sys_menu m
WHERE r.role_key = 'admin';

-- 启用触发器
SET session_replication_role = 'origin';

-- 提交事务
COMMIT;