-- 修复员工表中的无效角色代码
-- 将无效的角色代码替换为有效的角色代码

-- 查看当前员工表中的角色代码分布
SELECT role_id, COUNT(*) as count FROM t_employee WHERE state = 1 GROUP BY role_id;

-- 查看有效的角色代码
SELECT code, name FROM t_role WHERE state = 1;

-- 修复无效的角色代码
-- MANAGER -> SUPER_ADMIN (管理员)
UPDATE t_employee SET role_id = 'SUPER_ADMIN' WHERE role_id = 'MANAGER' AND state = 1;

-- EMPLOYEE -> AUDITOR (平台工作人员)
UPDATE t_employee SET role_id = 'AUDITOR' WHERE role_id = 'EMPLOYEE' AND state = 1;

-- ADMIN -> SUPER_ADMIN (超级管理员)
UPDATE t_employee SET role_id = 'SUPER_ADMIN' WHERE role_id = 'ADMIN' AND state = 1;

-- 验证修复结果
SELECT role_id, COUNT(*) as count FROM t_employee WHERE state = 1 GROUP BY role_id;

-- 检查是否还有无效的角色代码
SELECT e.id, e.name, e.role_id 
FROM t_employee e 
LEFT JOIN t_role r ON e.role_id = r.code AND r.state = 1
WHERE e.state = 1 AND r.code IS NULL;