lingyue-zhibao/database/migrations/001_cleanup_old_tables.sql

-- ============================================================
-- 清理旧表结构，统一使用新表
-- 执行时间: 2026-02-12
-- ============================================================

-- 1. 删除旧的 sys_* 表（保留数据到新表后再删除）
-- 注意：按照外键依赖顺序删除

-- 1.1 删除关联表
DROP TABLE IF EXISTS sys_role_permissions CASCADE;
DROP TABLE IF EXISTS sys_user_roles CASCADE;

-- 1.2 删除权限和角色表
DROP TABLE IF EXISTS sys_permissions CASCADE;
DROP TABLE IF EXISTS sys_roles CASCADE;

-- 1.3 删除会话和日志表
DROP TABLE IF EXISTS sys_sessions CASCADE;
DROP TABLE IF EXISTS sys_login_logs CASCADE;
DROP TABLE IF EXISTS sys_operation_logs CASCADE;

-- 1.4 删除系统配置表
DROP TABLE IF EXISTS sys_configs CASCADE;
DROP TABLE IF EXISTS sys_dict_items CASCADE;
DROP TABLE IF EXISTS sys_dict_types CASCADE;
DROP TABLE IF EXISTS sys_files CASCADE;
DROP TABLE IF EXISTS sys_tasks CASCADE;

-- 1.5 删除旧用户表
DROP TABLE IF EXISTS sys_users CASCADE;

-- 2. 确保新表 users 有完整的管理员数据
INSERT INTO users (
    id, 
    username, 
    email, 
    password_hash, 
    display_name, 
    role, 
    is_active
) VALUES (
    '1',
    'admin',
    'admin@lingyue.com',
    '$2a$10$0AUCG2mG7a6JXErOTI.Pg.Q/R04plOXvc.TDMeWzwwZQ23ZmrtJxC',
    '管理员',
    'admin',
    true
) ON CONFLICT (username) DO UPDATE SET
    password_hash = EXCLUDED.password_hash,
    email = EXCLUDED.email,
    display_name = EXCLUDED.display_name,
    role = EXCLUDED.role;

-- 3. 验证清理结果
SELECT 'Cleanup completed. Remaining sys_* tables:' as message;
SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE 'sys_%';

SELECT 'Users table data:' as message;
SELECT id, username, email, display_name, role, is_active FROM users;