---

WITH StudentGrades AS (
    -- 1. Сначала собираем статистику по КАЖДОМУ ученику
    SELECT 
        s.id AS student_id,
        s.class_id,
        MIN(g.grade) AS min_grade, -- Самая плохая оценка ученика
        MAX(g.grade) AS max_grade  -- Самая лучшая оценка
    FROM Students s
    JOIN Grades g ON s.id = g.student_id
    GROUP BY s.id, s.class_id
),
ClassStats AS (
    -- 2. Группируем учеников по классам и считаем категории
    SELECT 
        class_id,
        COUNT(student_id) AS total_students,
        -- Только 5 (Отличники)
        SUM(CASE WHEN min_grade = 5 THEN 1 ELSE 0 END) AS only_5,
        -- Только 4 и 5 (Хорошисты)
        SUM(CASE WHEN min_grade = 4 THEN 1 ELSE 0 END) AS only_4_5,
        -- Есть хотя бы одна 3
        SUM(CASE WHEN min_grade = 3 THEN 1 ELSE 0 END) AS has_3,
        -- Есть хотя бы одна 2
        SUM(CASE WHEN min_grade = 2 THEN 1 ELSE 0 END) AS has_2
    FROM StudentGrades
    GROUP BY class_id
)
-- 3. Выводим итоговый отчет по классам
SELECT 
    c.class_name AS [Класс],
    t.full_name AS [Классный руководитель],
    COALESCE(cs.total_students, 0) AS [Всего учеников],
    COALESCE(ROUND(AVG(CAST(g.grade AS FLOAT)), 2), 0.00) AS [Средний балл],
    COALESCE(cs.only_5, 0) AS [Только на 5],
    COALESCE(cs.only_4_5, 0) AS [Только на 4 и 5],
    COALESCE(cs.has_3, 0) AS [Есть хотя бы одна 3],
    COALESCE(cs.has_2, 0) AS [Есть хотя бы одна 2]
FROM Classes c
LEFT JOIN Teachers t ON c.homeroom_teacher_id = t.id
LEFT JOIN ClassStats cs ON c.id = cs.class_id
LEFT JOIN Students s ON c.id = s.class_id
LEFT JOIN Grades g ON s.id = g.student_id
GROUP BY 
    c.class_name, 
    t.full_name, 
    cs.total_students, 
    cs.only_5, 
    cs.only_4_5, 
    cs.has_3, 
    cs.has_2
ORDER BY c.class_name; -- Порядок от младших к старшим