-- ===========================================================================
-- [LCMS] Lấy danh sách học viên + section_avg_score + section_completion_state
-- Tương đương get_section_student_scores.sql nhưng dùng toàn bộ bảng lcms_*
--
-- Mapping bảng:
--   zeus_session_completions → lcms_user_assignments  (usrasi_*)
--   api_moodle               → lcms_courses            (cou_*)
--   students                 → lcms_students           (stu_*)
--   student_score            → lcms_student_scores     (stusco_*)
--   user_quiz_grade          → lcms_user_quiz_grades   (usrqgr_*)
--
-- Input:
--   :section_moodle_id  → moodle_id của section (giống bảng gốc, lưu ở cou_moodle_id)
--   :course_moodle_id   → moodle_id của course  (giống bảng gốc, lưu ở cou_moodle_id)
--   :zeus_id            → zeus_id học viên (optional, lcms_students.stu_user_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)
--    Lookup qua cou_moodle_id thay vì cou_id → nhận moodle_id trực tiếp
SectionInfo AS (
    SELECT
        cou_id             AS section_pk_id,
        cou_moodle_id      AS section_moodle_id,
        cou_name           AS section_name,
        cou_section_type   AS section_type
    FROM lcms_courses
    WHERE cou_moodle_id = 280          -- :section_moodle_id
      AND cou_type      = 'section'
    LIMIT 1
),

-- 2. Thông tin course (pk id + tên)
CourseInfo AS (
    SELECT
        cou_id   AS course_pk_id,
        cou_name AS course_name
    FROM lcms_courses
    WHERE cou_moodle_id = 39           -- :course_moodle_id
      AND cou_type      = 'course'
    LIMIT 1
),

-- 3. Danh sách học viên đã có trong section này + completion state + assigned_at
--    usrasi_section_id / usrasi_course_id lưu cou_id (pk nội bộ).
--    Cross-join SectionInfo + CourseInfo (mỗi CTE 1 row) rồi filter bằng WHERE
--    → cấu trúc giống bảng gốc: WHERE section_id = :x AND course_id = :y
SectionStudents AS (
    SELECT
        ua.usrasi_student_id                AS student_id,
        MIN(ua.usrasi_created_at)           AS assigned_at,
        MIN(ua.usrasi_completion_state)     AS section_completion_state
    FROM lcms_user_assignments ua
    JOIN SectionInfo si ON 1 = 1
    JOIN CourseInfo  ci ON 1 = 1
    WHERE ua.usrasi_section_id = si.section_pk_id   -- :section_moodle_id
      AND ua.usrasi_course_id  = ci.course_pk_id    -- :course_moodle_id
    GROUP BY ua.usrasi_student_id
),

-- 4. Tổng số quiz visible thuộc section
--    cou_parent_id của quiz trỏ đến cou_id của section (pk nội bộ)
QuizCount AS (
    SELECT COUNT(*) AS total_quizzes
    FROM lcms_courses am
    JOIN SectionInfo si ON am.cou_parent_id = si.section_pk_id
    WHERE am.cou_type = 'quiz'
),

-- 5. Điểm cao nhất mỗi học viên cho từng quiz trong section
--    Nguồn điểm 1: lcms_student_scores.stusco_overall_score  (LMS cũ)
--    Nguồn điểm 2: lcms_user_quiz_grades.usrqgr_score        (LMS mới / Speakup)
MaxScores AS (
    SELECT
        ss_sub.student_id,
        am_quiz.cou_id          AS quiz_cou_id,
        MAX(GREATEST(
            CASE
                WHEN stusco.stusco_overall_score IS NULL
                  OR stusco.stusco_overall_score = ''
                  OR stusco.stusco_overall_score = '-1'
                THEN -1
                ELSE CAST(stusco.stusco_overall_score AS DECIMAL(10,2))
            END,
            COALESCE(CAST(uqg.usrqgr_score AS DECIMAL(10,2)), -1)
        )) AS highest_score
    FROM SectionStudents ss_sub
    JOIN SectionInfo si ON 1 = 1               -- cross-join 1 row
    JOIN lcms_students stu
        ON stu.stu_id = ss_sub.student_id
    JOIN lcms_courses am_quiz
        ON am_quiz.cou_parent_id = si.section_pk_id
        AND am_quiz.cou_type     = 'quiz'
    LEFT JOIN lcms_student_scores stusco
        ON stusco.stusco_student_id = ss_sub.student_id
        AND stusco.stusco_course_id = am_quiz.cou_id
    -- usrqgr_user_id đã được mapping thẳng sang stu_id
    LEFT JOIN lcms_user_quiz_grades uqg
        ON uqg.usrqgr_user_id  = ss_sub.student_id
        AND uqg.usrqgr_quiz_id = am_quiz.cou_id
    GROUP BY ss_sub.student_id, am_quiz.cou_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: đã làm đủ số quiz (done_quizzes >= total_quizzes)
SectionAvg AS (
    SELECT
        ms.student_id,
        ROUND(AVG(ms.highest_score), 2) AS avg_score,
        COUNT(ms.quiz_cou_id)           AS done_quizzes
    FROM MaxScores ms
    GROUP BY ms.student_id
)

-- ===========================================================================
-- FINAL SELECT
-- ===========================================================================
SELECT
    stu.stu_id                              AS student_id,
    stu.stu_name                            AS student_name,
    stu.stu_email                           AS student_email,
    stu.stu_user_id                         AS student_zeus_id,   -- stu_user_id = 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 lcms_students stu   ON stu.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.stu_user_id = 375           -- :zeus_id (optional — bỏ comment nếu cần lọc)

ORDER BY stu.stu_name ASC;
