-- 查询当前数据库中的班级结构
-- 检查已有的学校、年级、班级数据

-- 1. 查看现有的学校数据
SELECT id, name, type FROM public.ak_schools ORDER BY name;

-- 2. 查看现有的年级数据  
SELECT 
    g.id, 
    g.name as grade_name,
    s.name as school_name
FROM public.ak_grades g
LEFT JOIN public.ak_schools s ON g.school_id = s.id
ORDER BY s.name, g.name;

-- 3. 查看现有的班级数据
SELECT 
    c.id as class_id,
    c.name as class_name,
    g.name as grade_name,
    s.name as school_name,
    COUNT(u.id) as student_count
FROM public.ak_classes c
LEFT JOIN public.ak_grades g ON c.grade_id = g.id
LEFT JOIN public.ak_schools s ON g.school_id = s.id
LEFT JOIN public.ak_users u ON u.class_id = c.id AND u.role = 'student'
GROUP BY c.id, c.name, g.name, s.name
ORDER BY s.name, g.name, c.name;

-- 4. 查看学生的班级分布情况
SELECT 
    COALESCE(c.name, '未分配班级') as class_name,
    COUNT(u.id) as student_count,
    array_agg(u.username) as students
FROM public.ak_users u
LEFT JOIN public.ak_classes c ON u.class_id = c.id
WHERE u.role = 'student'
GROUP BY c.name
ORDER BY student_count DESC;

-- 5. 查看教师-班级关系（如果表存在）
SELECT 
    u.username as teacher_name,
    c.name as class_name,
    g.name as grade_name,
    s.name as school_name
FROM public.ak_teacher_roles tr
JOIN public.ak_users u ON tr.user_id = u.id
JOIN public.ak_classes c ON tr.class_id = c.id
LEFT JOIN public.ak_grades g ON c.grade_id = g.id
LEFT JOIN public.ak_schools s ON g.school_id = s.id
ORDER BY u.username, c.name;

-- 6. 检查是否有学生未分配班级
SELECT 
    COUNT(*) as total_students,
    COUNT(CASE WHEN class_id IS NOT NULL THEN 1 END) as assigned_students,
    COUNT(CASE WHEN class_id IS NULL THEN 1 END) as unassigned_students
FROM public.ak_users 
WHERE role = 'student';