-- ===========================================================================
-- Lấy danh sách học viên + section_avg_score + section_completion_state
-- theo section_id (moodle_id) và course_id (moodle_id) truyền vào.
--
-- Input:
--   :section_moodle_id  → moodle_id của section (zeus_session_completions.section_id)
--   :course_moodle_id   → moodle_id của course  (zeus_session_completions.course_id)
--   :zeus_id            → zeus_id của học viên (optional, students.zeus_id)
--
-- Output mỗi row:
--   student_id, student_name, student_email, student_zeus_id,
--   course_name, section_name, section_type, section_type_name,
--   assigned_at, section_completion_state, section_avg_score
-- ===========================================================================

WITH

-- 1. Thông tin section (pk id, tên, loại)
SectionInfo AS (
    SELECT
        id             AS section_pk_id,
        moodle_id      AS section_moodle_id,   -- dùng để filter zeus_session_completions.section_id
        moodle_name    AS section_name,
        section_type
    FROM api_moodle
    WHERE moodle_id   = 280          -- :section_moodle_id
      AND moodle_type = 'section'
    LIMIT 1
),

-- 2. Thông tin course (tên)
CourseInfo AS (
    SELECT
        moodle_id  AS course_pk_id,   -- = zeus_session_completions.course_id
        moodle_name AS course_name
    FROM api_moodle
    WHERE moodle_id   = 39          -- :course_moodle_id
      AND moodle_type = 'course'
    LIMIT 1
),

-- 3. Danh sách học viên đã có trong section này + completion state + assigned_at
--    (MIN(completion_state): nếu bất kỳ record nào != 1 → section chưa hoàn thành)
SectionStudents AS (
    SELECT
        ua.student_id,
        MIN(ua.created_at)       AS assigned_at,
        MIN(ua.completion_state) AS section_completion_state
    FROM zeus_session_completions ua
    JOIN SectionInfo si ON 1 = 1
    JOIN CourseInfo  ci ON 1 = 1
    WHERE ua.section_id = si.section_moodle_id   -- :section_moodle_id
      AND ua.course_id  = ci.course_pk_id        -- :course_moodle_id
    GROUP BY ua.student_id
),

-- 4. Tổng số quiz visible thuộc section
QuizCount AS (
    SELECT COUNT(*) AS total_quizzes
    FROM api_moodle am
    JOIN SectionInfo si ON am.parent_id = si.section_pk_id
    WHERE am.moodle_type = 'quiz'
      AND am.visible = 1
),

-- 5. Điểm cao nhất mỗi học viên cho từng quiz trong section
--    Logic giống getReportDetailsData:
--    highest_score = MAX(GREATEST(student_score.overrall_score, user_quiz_grade.score))
--    Chỉ lấy quiz có điểm >= 0 (đã làm)
MaxScores AS (
    SELECT
        ss_sub.student_id,
        am_quiz.moodle_id   AS quiz_id,
        MAX(GREATEST(
            CASE
                WHEN ss.overrall_score IS NULL
                  OR ss.overrall_score = ''
                  OR ss.overrall_score = '-1'
                THEN -1
                ELSE CAST(ss.overrall_score AS DECIMAL(10,2))
            END,
            COALESCE(CAST(uqg.score AS DECIMAL(10,2)), -1)
        )) AS highest_score
    FROM SectionStudents ss_sub
    JOIN SectionInfo si ON 1 = 1      -- cross-join 1 row
    JOIN students stu
        ON stu.id = ss_sub.student_id
    JOIN api_moodle am_quiz
        ON am_quiz.parent_id   = si.section_pk_id
        AND am_quiz.moodle_type = 'quiz'
        AND am_quiz.visible     = 1
    LEFT JOIN student_score ss
        ON ss.student_id = ss_sub.student_id
        AND ss.quiz_id   = am_quiz.moodle_id
    LEFT JOIN user_quiz_grade uqg
        ON uqg.user_id = stu.user_id
        AND uqg.quiz_id = am_quiz.id
    GROUP BY ss_sub.student_id, am_quiz.moodle_id
    HAVING highest_score >= 0         -- chỉ tính quiz đã làm
),

-- 6. Tính avg_score của section cho từng học viên
--    Điều kiện: phải làm ĐỦ số quiz (done_quizzes >= total_quizzes)
--    → giống logic PHP: if ($stuScores->count() >= $totQ) avg = round(avg, 2)
SectionAvg AS (
    SELECT
        ms.student_id,
        ROUND(AVG(ms.highest_score), 2) AS avg_score,
COUNT(ms.quiz_id)               AS done_quizzes
    FROM MaxScores ms
    GROUP BY ms.student_id
)

-- ===========================================================================
-- FINAL SELECT
-- ===========================================================================
SELECT
    stu.id                              AS student_id,
    stu.name                            AS student_name,
    stu.email                           AS student_email,
    stu.zeus_id                         AS student_zeus_id,

    ci.course_name,
    si.section_name,
    si.section_type,
    CASE si.section_type
        WHEN 2 THEN 'BTVN'
        ELSE        'BKT'
    END                                 AS section_type_name,

    ss_sub.assigned_at,

    -- 1 = hoàn thành, 0 = chưa hoàn thành
    CASE WHEN ss_sub.section_completion_state = 1 THEN 1 ELSE 0 END
                                        AS section_completion_state,

    -- NULL nếu chưa làm đủ quiz, ngược lại là điểm trung bình
    CASE
        WHEN qc.total_quizzes = 0          THEN NULL
        WHEN sa.done_quizzes >= qc.total_quizzes THEN sa.avg_score
        ELSE NULL
    END                                 AS section_avg_score

FROM SectionStudents ss_sub
JOIN students stu        ON stu.id = ss_sub.student_id
JOIN SectionInfo si      ON 1 = 1
JOIN CourseInfo ci       ON 1 = 1
CROSS JOIN QuizCount qc
LEFT JOIN SectionAvg sa  ON sa.student_id = ss_sub.student_id

WHERE ss_sub.section_completion_state = 1   -- chỉ lấy section đã hoàn thành
AND stu.zeus_id = 375  

ORDER BY stu.name ASC;
