-- ================================================================
-- SQL: Bổ sung index cho HomeworkQuizReport
-- Chạy thủ công trên production database
-- Dùng IF NOT EXISTS để an toàn khi chạy lại nhiều lần
-- ================================================================

-- 1. zeus_session_completions
ALTER TABLE zeus_session_completions
    ADD INDEX IF NOT EXISTS idx_zsc_stu_sec_state (student_id, section_id, completion_state),
    ADD INDEX IF NOT EXISTS idx_zsc_stu_course (student_id, course_id),
    ADD INDEX IF NOT EXISTS idx_zsc_course_section (course_id, section_id),
    ADD INDEX IF NOT EXISTS idx_zsc_created_at (created_at);

-- 2. api_moodle
ALTER TABLE api_moodle
    ADD INDEX IF NOT EXISTS idx_am_type_moodleid (moodle_type, moodle_id),
    ADD INDEX IF NOT EXISTS idx_am_type_moodleid_vis (moodle_type, moodle_id, visible),
    ADD INDEX IF NOT EXISTS idx_am_type_parentid_vis (moodle_type, parent_id, visible),
    ADD INDEX IF NOT EXISTS idx_am_type_sectype_vis (moodle_type, section_type, visible);

-- 3. students
ALTER TABLE students
    ADD INDEX IF NOT EXISTS idx_stu_user_id (user_id),
    ADD INDEX IF NOT EXISTS idx_stu_zeus_id (zeus_id);

-- 4. student_score
ALTER TABLE student_score
    ADD INDEX IF NOT EXISTS idx_ss_student_quiz (student_id, quiz_id),
    ADD INDEX IF NOT EXISTS idx_ss_student_quiz_score (student_id, quiz_id, overrall_score),
    ADD INDEX IF NOT EXISTS idx_ss_quiz_id (quiz_id),
    ADD INDEX IF NOT EXISTS idx_ss_student_status (student_id, score_status);

-- 5. user_quiz_grade
ALTER TABLE user_quiz_grade
    ADD INDEX IF NOT EXISTS idx_uqg_userid_quizid (user_id, quiz_id),
    ADD INDEX IF NOT EXISTS idx_uqg_userid_quizid_score (user_id, quiz_id, score),
    ADD INDEX IF NOT EXISTS idx_uqg_quiz_id (quiz_id);

-- 6. student_exam_histories
ALTER TABLE student_exam_histories
    ADD INDEX IF NOT EXISTS idx_seh_score_id (student_score_id),
    ADD INDEX IF NOT EXISTS idx_seh_score_id_created (student_score_id, created_at);
