-- ============================================================
-- 修复学生表的 dept_id 字段
-- 
-- 问题：学生创建时未根据所属学校设置 dept_id，导致数据权限过滤失效
-- 解决：根据学生所属学校的 dept_id 更新学生的 dept_id
-- ============================================================

-- 查看需要修复的数据
SELECT 
    s.student_id,
    s.student_name,
    s.school_id,
    s.dept_id AS student_dept_id,
    sc.school_name,
    sc.dept_id AS school_dept_id
FROM pg_student s
LEFT JOIN pg_school sc ON s.school_id = sc.school_id
WHERE s.del_flag = '0' 
  AND (s.dept_id IS NULL OR s.dept_id != sc.dept_id);

-- 执行修复：将学生的 dept_id 更新为所属学校的 dept_id
UPDATE pg_student s
INNER JOIN pg_school sc ON s.school_id = sc.school_id
SET s.dept_id = sc.dept_id
WHERE s.del_flag = '0' 
  AND sc.dept_id IS NOT NULL
  AND (s.dept_id IS NULL OR s.dept_id != sc.dept_id);

-- 验证修复结果
SELECT 
    COUNT(*) AS '修复后仍有问题的记录数'
FROM pg_student s
LEFT JOIN pg_school sc ON s.school_id = sc.school_id
WHERE s.del_flag = '0' 
  AND sc.dept_id IS NOT NULL
  AND (s.dept_id IS NULL OR s.dept_id != sc.dept_id);