-- ==========================================
-- SQL Query: Get total submitted attempts for quizzes with quiz_settings_type = 29
-- from courses in courseApiMapping (1559, 1742, 563, 595, 1084, 346)
-- ==========================================

-- 1. Query tổng số lượt làm bài (tổng hợp tất cả)

SELECT 
    seh.student_score_id,
    COUNT(seh.id) AS total_attempts,
    COUNT(DISTINCT q.id) AS total_quizzes
FROM api_moodle c
INNER JOIN api_moodle s ON s.parent_id = c.id AND s.moodle_type = 'section'
INNER JOIN api_moodle q ON q.parent_id = s.id 
    AND q.moodle_type = 'quiz' 
    AND q.quiz_settings_type = 29
LEFT JOIN student_exam_histories seh ON seh.quiz_id = q.moodle_id
    AND seh.created_at >= '2026-01-01 00:00:00'
    AND seh.created_at <= NOW()
WHERE c.moodle_type = 'course'
    AND c.id IN (1559, 1742, 563, 595, 1084, 346)
GROUP BY seh.student_score_id;

-- ==========================================
-- SQL Query: Get unique users who submitted (DISTINCT users)
-- ==========================================

-- 6. Query tổng số user unique submit (tổng hợp tất cả)
-- Lưu ý: Sử dụng INNER JOIN với students để chỉ đếm các user có trong bảng students
SELECT 
    COUNT(DISTINCT seh.student_id) AS total_unique_users,
    COUNT(seh.id) AS total_attempts,
    COUNT(DISTINCT q.id) AS total_quizzes
FROM api_moodle c
INNER JOIN api_moodle s ON s.parent_id = c.id AND s.moodle_type = 'section'
INNER JOIN api_moodle q ON q.parent_id = s.id 
    AND q.moodle_type = 'quiz' 
    AND q.quiz_settings_type = 29
INNER JOIN student_exam_histories seh ON seh.quiz_id = q.moodle_id
    AND seh.created_at >= '2026-01-01 00:00:00'
    AND seh.created_at <= NOW()
INNER JOIN students st ON st.id = seh.student_id
WHERE c.moodle_type = 'course'
    AND c.id IN (1559, 1742, 563, 595, 1084, 346);

-- ==========================================
-- SQL Query: Get started but not submitted attempts (using student_score)
-- Số lượng bài làm user đã ấn vào làm nhưng chưa submit
-- ==========================================

-- 11. Query tổng số bài làm đã bắt đầu nhưng chưa submit (tổng hợp tất cả)
SELECT 
    COUNT(DISTINCT ss.id) AS total_started_not_submitted,
    COUNT(DISTINCT ss.student_id) AS unique_users_started,
    COUNT(DISTINCT q.id) AS total_quizzes
FROM api_moodle c
INNER JOIN api_moodle s ON s.parent_id = c.id AND s.moodle_type = 'section'
INNER JOIN api_moodle q ON q.parent_id = s.id 
    AND q.moodle_type = 'quiz' 
    AND q.quiz_settings_type = 29
INNER JOIN student_score ss ON ss.quiz_id = q.moodle_id
    AND ss.created_at >= '2026-01-01 00:00:00'
    AND ss.created_at <= NOW()
LEFT JOIN student_exam_histories seh ON seh.student_score_id = ss.id
WHERE c.moodle_type = 'course'
    AND c.id IN (1559, 1742, 563, 595, 1084, 346)
    AND seh.id IS NULL;

-- ==========================================
-- SQL Query: Get average score by course
-- Điểm trung bình theo course
-- ==========================================

-- 16. Query điểm trung bình theo từng course (1559, 1742, 563, 595, 1084, 346)
SELECT 
    c.id AS course_id,
    c.moodle_id AS course_moodle_id,
    c.moodle_name AS course_name,
    COUNT(DISTINCT q.id) AS total_quizzes,
    ROUND(AVG(CASE WHEN seh.lms_score IS NOT NULL AND seh.lms_score != '' THEN CAST(seh.lms_score AS DECIMAL(10,2)) END), 2) AS average_score
FROM api_moodle c
INNER JOIN api_moodle s ON s.parent_id = c.id AND s.moodle_type = 'section'
INNER JOIN api_moodle q ON q.parent_id = s.id 
    AND q.moodle_type = 'quiz' 
    AND q.quiz_settings_type = 29
LEFT JOIN student_exam_histories seh ON seh.quiz_id = q.moodle_id
    AND seh.created_at >= '2026-01-01 00:00:00'
    AND seh.created_at <= NOW()
WHERE c.moodle_type = 'course'
    AND c.id = 1559
GROUP BY c.id, c.moodle_id, c.moodle_name
ORDER BY c.moodle_id;

-- ==========================================
-- SQL Query: Get user information and all scores by course
-- Lấy thông tin user và điểm số tất cả lượt làm của user đó theo từng khóa
-- ==========================================

-- 2. Query thông tin user và tất cả các lượt làm bài (chi tiết từng lượt)
SELECT 
    c.id AS course_id,
    c.moodle_id AS course_moodle_id,
    c.moodle_name AS course_name,
    st.id AS student_id,
    st.name AS student_name,
    st.email AS student_email,
    st.username AS student_username,
    q.id AS quiz_id,
    q.moodle_id AS quiz_moodle_id,
    q.moodle_name AS quiz_name,
    seh.id AS attempt_id,
    seh.student_score_id,
    seh.lms_score,
    seh.ems_score,
    seh.created_at AS submitted_at,
    seh.updated_at AS updated_at
FROM api_moodle c
INNER JOIN api_moodle s ON s.parent_id = c.id AND s.moodle_type = 'section'
INNER JOIN api_moodle q ON q.parent_id = s.id 
    AND q.moodle_type = 'quiz' 
    AND q.quiz_settings_type = 29
INNER JOIN student_exam_histories seh ON seh.quiz_id = q.moodle_id
    AND seh.created_at >= '2026-01-01 00:00:00'
    AND seh.created_at <= NOW()
INNER JOIN students st ON st.id = seh.student_id
WHERE c.moodle_type = 'course'
    AND c.id IN (1559, 1742, 563, 595, 1084, 346)
GROUP BY seh.student_score_id
ORDER BY c.moodle_id, st.name, seh.created_at DESC;

-- ==========================================
-- SQL Query: Get homework completion rate (%)
-- Tỷ lệ học viên có làm BTVN (%)
-- Theo tuần / tháng
-- Công thức: Số user làm ≥ 1 BTVN / Tổng user được giao BTVN (bảng zeus_session_completions)
-- ==========================================

-- 20. Query tỷ lệ học viên làm BTVN theo tuần (tổng hợp tất cả)
-- Lưu ý: BTVN là các quiz có parent_id là section có section_type = 2
--       Tổng user được giao BTVN = DISTINCT student_id từ zeus_session_completions JOIN với quiz thuộc section có section_type = 2
--       Số user làm ≥ 1 BTVN = DISTINCT student_id từ student_exam_histories JOIN với quiz thuộc section có section_type = 2
SELECT 
    YEARWEEK(zsc.created_at, 1) AS year_week,
    DATE_FORMAT(MIN(zsc.created_at), '%Y-%m-%d') AS week_start_date,
    DATE_FORMAT(MAX(zsc.created_at), '%Y-%m-%d') AS week_end_date,
    COUNT(DISTINCT zsc.student_id) AS total_users_assigned_homework,
    COUNT(DISTINCT seh.student_id) AS users_completed_homework,
    CASE 
        WHEN COUNT(DISTINCT zsc.student_id) > 0 
        THEN ROUND((COUNT(DISTINCT seh.student_id) * 100.0 / COUNT(DISTINCT zsc.student_id)), 2)
        ELSE 0 
    END AS completion_rate_percentage
FROM zeus_session_completions zsc
-- Lưu ý: section_id và course_id trong zeus_session_completions là moodle_id, không phải id
INNER JOIN api_moodle s ON s.moodle_id = zsc.section_id AND s.moodle_type = 'section'
    AND s.section_type = 2
INNER JOIN api_moodle c ON c.moodle_id = zsc.course_id AND c.moodle_type = 'course'
    AND c.id IN (1559, 1742, 563, 595, 1084, 346)
INNER JOIN api_moodle q ON q.parent_id = s.id 
    AND q.moodle_type = 'quiz'
LEFT JOIN student_exam_histories seh ON seh.quiz_id = q.moodle_id
    AND seh.student_id = zsc.student_id
    AND seh.created_at >= DATE_SUB(zsc.created_at, INTERVAL 7 DAY)
    AND seh.created_at <= DATE_ADD(zsc.created_at, INTERVAL 7 DAY)
WHERE zsc.created_at >= '2026-01-01 00:00:00'
    AND zsc.created_at <= NOW()
GROUP BY YEARWEEK(zsc.created_at, 1)
ORDER BY year_week DESC;

-- ==========================================
-- SQL Query: Get average time to complete homework (BTVN)
-- Thời gian trung bình làm BTVN (chỉ lấy các loại bài tập thuộc section BTVN)
-- BTVN là các quiz có parent_id là section có section_type = 2
-- Thời gian = student_exam_histories.created_at - student_score.created_at
-- ==========================================

-- 30. Query thời gian trung bình làm BTVN (tổng hợp tất cả)
-- Thời gian tính bằng phút (minutes)
-- Lưu ý: id_history bảng student_history_study_time là field idHistoryContest bảng student_exam_histories
-- Cần join thêm id_bai_kiem_tra và student_id để đảm bảo chính xác khi id_history có thể bị sai định dạng bigInt cũ
SELECT 
    COUNT(DISTINCT shst.id_history) AS total_attempts,
    COUNT(DISTINCT shst.student_id) AS total_students,
    COUNT(DISTINCT q.id) AS total_quizzes,
    ROUND(AVG(shst.total_minutes), 2) AS average_time_minutes,
    CONCAT(
        FLOOR(AVG(shst.total_minutes)), ' phút ',
        ROUND((AVG(shst.total_minutes) - FLOOR(AVG(shst.total_minutes))) * 60), ' giây'
    ) AS average_time_formatted,
    MIN(shst.total_minutes) AS min_time_minutes,
    MAX(shst.total_minutes) AS max_time_minutes
FROM api_moodle c
INNER JOIN api_moodle s ON s.parent_id = c.id AND s.moodle_type = 'section'
    AND s.section_type = 2
INNER JOIN api_moodle q ON q.parent_id = s.id 
    AND q.moodle_type = 'quiz'
-- Sử dụng student_history_study_time chứa dữ liệu đã được tracker xử lý (heartbeat + rounding rule PHP)
INNER JOIN student_exam_histories seh ON seh.quiz_id = q.moodle_id
INNER JOIN student_history_study_time shst ON shst.id_bai_kiem_tra = seh.idbaikiemtra
    AND seh.student_id = shst.student_id
WHERE c.moodle_type = 'course'
    AND c.id IN (1559, 1742, 563, 595, 1084, 346)
    AND shst.status = 'completed'
    AND shst.completed_at >= '2026-01-01 00:00:00'
    AND shst.completed_at <= NOW();


-- SELECT 
--     COUNT(main_data.seh_id) AS total_attempts, 
--     COUNT(DISTINCT main_data.student_id) AS total_students, 
--     ROUND(AVG(shst.total_minutes), 2) AS average_time_minutes
-- FROM (
--     -- Bước 1: Lấy danh sách định danh chuẩn từ Course 845
--     -- Đưa idHistoryContest về kiểu String để chuẩn bị so khớp
--     SELECT 
--         seh.id AS seh_id,
--         CAST(seh.idHistoryContest AS CHAR) AS join_id, 
--         seh.student_id
--     FROM api_moodle c
--     INNER JOIN api_moodle s ON s.parent_id = c.id AND s.moodle_type = 'section'
--     INNER JOIN api_moodle q ON q.parent_id = s.id 
--         AND q.moodle_type = 'quiz' 
--         AND q.quiz_settings_type = 29
--     INNER JOIN student_exam_histories seh ON seh.quiz_id = q.moodle_id
--     WHERE c.moodle_type = 'course'
--         AND c.id = 845
--         AND seh.created_at BETWEEN '2026-01-30 00:00:00' AND '2026-01-30 23:59:59'
-- ) AS main_data
-- -- Bước 2: Join với bảng study_time bằng cách convert id_history về cùng kiểu String
-- INNER JOIN student_history_study_time shst 
--     ON CAST(shst.id_history AS CHAR) = main_data.join_id
-- WHERE shst.status = 'completed';

-- ==========================================
-- SQL Query: Get failure rate (tỷ lệ phải làm lại / bị fail)
-- Tỷ lệ phải làm lại / bị fail theo từng khóa
-- Công thức: Số bài không đạt (score_status = 0) / Tổng số bài submit thành công (score_status IS NOT NULL)
-- BTVN là các quiz có parent_id là section có section_type = 2
-- ==========================================

-- 38. Query tỷ lệ fail theo từng course
-- Lưu ý: Sử dụng DISTINCT student_score_id để mỗi lần làm bài chỉ tính một lần
SELECT 
    c.id AS course_id,
    c.moodle_id AS course_moodle_id,
    c.moodle_name AS course_name,
    COUNT(DISTINCT CASE WHEN attempt_status.score_status = 0 THEN attempt_status.student_score_id END) AS failed_attempts,
    COUNT(DISTINCT CASE WHEN attempt_status.score_status = 1 THEN attempt_status.student_score_id END) AS passed_attempts,
    COUNT(DISTINCT CASE WHEN attempt_status.score_status IS NOT NULL THEN attempt_status.student_score_id END) AS total_submitted_attempts,
    COUNT(DISTINCT attempt_status.student_score_id) AS total_attempts,
    CASE 
        WHEN COUNT(DISTINCT attempt_status.student_score_id) > 0 
        THEN ROUND((COUNT(DISTINCT CASE WHEN attempt_status.score_status = 0 THEN attempt_status.student_score_id END) * 100.0 / 
                   COUNT(DISTINCT attempt_status.student_score_id)), 2)
        ELSE 0 
    END AS failure_rate_percentage,
    CASE 
        WHEN COUNT(DISTINCT attempt_status.student_score_id) > 0 
        THEN ROUND((COUNT(DISTINCT CASE WHEN attempt_status.score_status = 1 THEN attempt_status.student_score_id END) * 100.0 / 
                   COUNT(DISTINCT attempt_status.student_score_id)), 2)
        ELSE 0 
    END AS pass_rate_percentage
FROM (
    SELECT 
        seh.student_score_id,
        seh.student_id,
        q.id AS quiz_id,
        q.parent_id AS section_id,
        -- Lấy score_status từ record có skill = NULL (overall score) hoặc record đầu tiên nếu không có
        COALESCE(
            MAX(CASE WHEN seh.skill IS NULL THEN seh.score_status END),
            MIN(seh.score_status)
        ) AS score_status
    FROM api_moodle c
    INNER JOIN api_moodle s ON s.parent_id = c.id AND s.moodle_type = 'section'
        AND s.section_type = 2
    INNER JOIN api_moodle q ON q.parent_id = s.id 
        AND q.moodle_type = 'quiz'
    INNER JOIN student_exam_histories seh ON seh.quiz_id = q.moodle_id
        AND seh.created_at >= '2026-01-01 00:00:00'
        AND seh.created_at <= NOW()
        AND seh.student_score_id IS NOT NULL
    WHERE c.moodle_type = 'course'
        AND c.id IN (1559, 1742, 563, 595, 1084, 346)
    GROUP BY seh.student_score_id, seh.student_id, q.id, q.parent_id
) AS attempt_status
INNER JOIN api_moodle q ON q.id = attempt_status.quiz_id
INNER JOIN api_moodle s ON s.id = attempt_status.section_id
INNER JOIN api_moodle c ON c.id = s.parent_id
GROUP BY c.id, c.moodle_id, c.moodle_name
ORDER BY c.moodle_id;