-- ===========================================================================
-- SQL Equivalent of: getReportDetailsData(Request $request)
-- HomeworkQuizReportController.php
--
-- Tham số thay thế (thay bằng giá trị thực khi chạy):
--   :course_ids  → danh sách course moodle_id, e.g. (101, 202)
--   :student_ids → danh sách student id nội bộ, e.g. (1, 2, 3)
--   :zeus_id     → zeus_id (optional), resolve trước thành student_id
--   :section_types → danh sách loại section, default (2, 3)
--   :start_date  → YYYY-MM-DD (optional)
--   :end_date    → YYYY-MM-DD (optional)
--   :detail_course   → moodle_id khóa học filter Box 3 (optional)
--   :detail_type     → section_type filter Box 3 (optional)
--   :detail_status   → 1 = completed, 0 = not completed (optional)
--   :detail_section  → LIKE tên section (optional)
--   :detail_keyword  → LIKE tên/moodle_id quiz (optional)
--   :detail_zeus_id  → zeus_id học viên filter Box 3 (optional)
--   :page, :per_page → phân trang (default per_page = 20)
-- ===========================================================================


-- ===========================================================================
-- BƯỚC 0 (optional): Resolve zeus_id → student_id
-- Chạy trước khi chạy các query chính nếu có zeus_id filter
-- ===========================================================================
/*
SELECT id AS student_id
FROM students
WHERE zeus_id IN (:zeus_id);
-- Kết quả gộp vào :student_ids ở trên
*/


-- ===========================================================================
-- QUERY 1 (SECTION-LEVEL PAGINATED):
-- Tương đương $sectionQuery->paginate(...)
-- Lấy danh sách (student, section) theo các điều kiện filter.
-- ===========================================================================

-- ---- 1A. ĐẾM TỔNG (cho pagination) ----
SELECT COUNT(*) AS total_rows
FROM (
    SELECT
        ua.student_id,
        cs.moodle_id AS section_id
    FROM zeus_session_completions AS ua
    -- Join section
    JOIN api_moodle AS cs
        ON ua.section_id = cs.moodle_id
        AND cs.moodle_type = 'section'
    -- Join course
    JOIN api_moodle AS cp
        ON ua.course_id = cp.moodle_id
        AND cp.moodle_type = 'course'
    -- Join student (inner join để loại rác)
    JOIN students AS stu
        ON stu.id = ua.student_id

    WHERE cs.section_type IN (2, 3)       -- :section_types
      AND cs.visible = 1

      -- Chỉ lấy Section có ít nhất 1 quiz visible
      AND EXISTS (
          SELECT 1
          FROM api_moodle AS ca
          WHERE ca.parent_id = cs.id
            AND ca.moodle_type = 'quiz'
            AND ca.visible = 1
      )

      -- === FILTER CHÍNH ===
      AND ua.course_id IN (101, 202)        -- :course_ids  (bỏ nếu không có)
      AND ua.student_id IN (1, 2, 3)        -- :student_ids (bỏ nếu không có)
      AND DATE(ua.created_at) >= '2024-01-01'   -- :start_date (bỏ nếu không có)
      AND DATE(ua.created_at) <= '2024-12-31'   -- :end_date   (bỏ nếu không có)

      -- === FILTER BOX 3 ===
      AND cp.moodle_id = :detail_course     -- bỏ nếu không có
      AND cs.section_type = :detail_type    -- bỏ nếu không có
      AND ua.completion_state = 1           -- nếu :detail_status = 1
      -- AND ua.completion_state != 1       -- nếu :detail_status = 0
      AND cs.moodle_name LIKE '%:detail_section%'   -- bỏ nếu không có
      AND stu.zeus_id = :detail_zeus_id     -- bỏ nếu không có
      AND EXISTS (
          SELECT 1
          FROM api_moodle AS ca
          WHERE ca.parent_id = cs.id
            AND ca.moodle_type = 'quiz'
            AND ca.visible = 1
            AND (ca.moodle_name LIKE '%:detail_keyword%' OR ca.moodle_id LIKE '%:detail_keyword%')
      )  -- bỏ nếu :detail_keyword không có

    GROUP BY
        stu.name, stu.email, stu.zeus_id,
        ua.student_id,
        cp.moodle_name, cp.moodle_id,
        cs.moodle_id, cs.moodle_name, cs.section_type
) AS counted;


-- ---- 1B. LẤY DỮ LIỆU (1 page) ----
-- Thay LIMIT/OFFSET theo per_page và page
-- OFFSET = (page - 1) * per_page
SELECT
    stu.name          AS student_name,
    stu.email         AS student_email,
    stu.zeus_id       AS student_zeus_id,
    ua.student_id     AS student_id,
    cp.moodle_name    AS course_name,
    cp.moodle_id      AS course_id,
    cs.moodle_id      AS section_id,
    cs.id             AS section_pk_id,
    cs.moodle_name    AS section_name,
    cs.section_type,
    MIN(ua.created_at) AS assigned_at
FROM zeus_session_completions AS ua
JOIN api_moodle AS cs
    ON ua.section_id = cs.moodle_id
    AND cs.moodle_type = 'section'
JOIN api_moodle AS cp
    ON ua.course_id = cp.moodle_id
    AND cp.moodle_type = 'course'
JOIN students AS stu
    ON stu.id = ua.student_id

WHERE cs.section_type IN (2, 3)
  AND cs.visible = 1
  AND EXISTS (
      SELECT 1 FROM api_moodle AS ca
      WHERE ca.parent_id = cs.id
        AND ca.moodle_type = 'quiz'
        AND ca.visible = 1
  )
  -- === FILTER CHÍNH ===
  AND ua.course_id IN (101, 202)
  AND ua.student_id IN (1, 2, 3)
  AND DATE(ua.created_at) >= '2024-01-01'
  AND DATE(ua.created_at) <= '2024-12-31'
  -- === FILTER BOX 3 (tùy chọn) ===
  -- AND cp.moodle_id = :detail_course
  -- AND cs.section_type = :detail_type
  -- AND ua.completion_state = 1   (nếu detail_status = 1)
  -- AND cs.moodle_name LIKE '%keyword%'
  -- AND stu.zeus_id = :detail_zeus_id
  -- AND EXISTS (SELECT 1 FROM api_moodle AS ca WHERE ca.parent_id = cs.id AND ca.moodle_type='quiz' AND ca.visible=1 AND ca.moodle_name LIKE '%keyword%')

GROUP BY
    stu.name, stu.email, stu.zeus_id,
    ua.student_id,
    cp.moodle_name, cp.moodle_id,
    cs.moodle_id, cs.moodle_name, cs.section_type,
    cs.id
ORDER BY cs.id ASC, stu.name ASC
LIMIT 20 OFFSET 0;  -- per_page=20, page=1 → OFFSET=0


-- ===========================================================================
-- QUERY 2: SECTION COMPLETION STATE
-- Tương đương $completionStates (dùng student_id & section_id từ page trên)
-- Input: danh sách student_id và section_id từ trang hiện tại
-- ===========================================================================
SELECT
    student_id,
    section_id,
    MIN(completion_state) AS min_state
FROM zeus_session_completions
WHERE student_id IN (/* :studentIdsPage */)
  AND section_id  IN (/* :sectionIdsPage */)
GROUP BY student_id, section_id;


-- ===========================================================================
-- QUERY 3: ĐẾM SỐ QUIZ VISIBLE PER SECTION
-- Tương đương $quizzesCount
-- Input: danh sách section_pk_id (cs.id) từ trang hiện tại
-- ===========================================================================
SELECT
    parent_id AS section_pk_id,
    COUNT(*)  AS total_quizzes
FROM api_moodle
WHERE moodle_type = 'quiz'
  AND visible = 1
  AND parent_id IN (/* :sectionPkIdsPage */)
GROUP BY parent_id;


-- ===========================================================================
-- QUERY 4: ĐIỂM CAO NHẤT MỖI QUIZ CHO MỖI HỌC VIÊN
-- Tương đương $maxScores
-- Dùng để tính avg_score cho từng section (chỉ tính nếu đủ số quiz)
-- ===========================================================================
SELECT
    ua.student_id                   AS student_id,
    c.parent_id                     AS section_pk_id,
    c.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 zeus_session_completions AS ua
JOIN students AS stu
    ON stu.id = ua.student_id
JOIN api_moodle AS cs
    ON ua.section_id = cs.moodle_id
    AND cs.moodle_type = 'section'
JOIN api_moodle AS c
    ON c.parent_id = cs.id
    AND c.moodle_type = 'quiz'
LEFT JOIN student_score AS ss
    ON ss.student_id = ua.student_id
    AND ss.quiz_id = c.moodle_id
LEFT JOIN user_quiz_grade AS uqg
    ON uqg.user_id = stu.user_id
    AND uqg.quiz_id = c.id
WHERE c.visible = 1
  AND ua.student_id IN (/* :studentIdsPage */)
  AND c.parent_id   IN (/* :sectionPkIdsPage */)
GROUP BY ua.student_id, c.parent_id, c.moodle_id
HAVING highest_score >= 0;


-- ===========================================================================
-- QUERY 5: CHI TIẾT ACTIVITY (QUIZ) PER STUDENT PER SECTION
-- Tương đương $activityRows
-- ===========================================================================
SELECT
    ua.student_id                   AS student_id,
    cs.moodle_id                    AS section_id,
    ca.moodle_id                    AS activity_id,
    ca.moodle_name                  AS activity_name,
    MAX(ss.score_status)            AS max_status,
    MAX(seh.created_at)             AS last_submitted,
    MAX(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)                            AS max_ss_score,
    MAX(COALESCE(CAST(uqg.score AS DECIMAL(10,2)), -1)) AS max_uqg_score
FROM zeus_session_completions AS ua
JOIN api_moodle AS cs
    ON ua.section_id = cs.moodle_id
    AND cs.moodle_type = 'section'
JOIN api_moodle AS ca
    ON ca.parent_id = cs.id
JOIN students AS stu
    ON stu.id = ua.student_id
LEFT JOIN student_score AS ss
    ON ss.student_id = ua.student_id
    AND ss.quiz_id = ca.moodle_id
LEFT JOIN student_exam_histories AS seh
    ON seh.student_score_id = ss.id
LEFT JOIN user_quiz_grade AS uqg
    ON uqg.user_id = stu.user_id
    AND uqg.quiz_id = ca.id
WHERE ua.student_id IN (/* :studentIdsPage */)
  AND ua.section_id  IN (/* :sectionIdsPage */)
  AND ca.moodle_type = 'quiz'
  AND ca.visible = 1
GROUP BY ua.student_id, cs.moodle_id, ca.moodle_id, ca.moodle_name
ORDER BY ca.moodle_id ASC;


-- ===========================================================================
-- POST-PROCESSING (PHP side, không thể chuyển thành SQL thuần):
--
-- Từ kết quả Query 4 + Query 3, PHP tính avg_score cho từng (student, section):
--   - Lọc maxScores theo (student_id, section_pk_id)
--   - Nếu count(quiz làm) >= total_quizzes của section → avg = AVG(highest_score)
--   - Ngược lại → avg = NULL
--
-- Từ kết quả Query 5, PHP gán nhãn completion_status cho từng activity:
--   max_score = max(max_ss_score, max_uqg_score)
--   is_migrated = (max_score >= 0 AND max_score == max_uqg_score AND max_uqg_score > max_ss_score)
--   if max_score == -1 → 'Chưa làm'
--   else if max_score >= 5 → 'Đạt'
--   else if max_status == 1 → 'Đạt' else 'Chưa đạt'
--
-- Kết quả cuối ghép: section row + sectionStats + activities list
-- ===========================================================================
