-- EXECUTE — Brigitte / course 7 quiz attempt reset (local or staging).
-- Run preview script first.

SET @user_email        := 'barakatbrigitte02@gmail.com';
SET @course_id         := 7;
SET @course_version_id := 77;
SET @lesson_rrc_mcq    := 2499;
SET @lesson_ch_qcm     := 2501;

SELECT @user_id := id FROM users WHERE email = @user_email LIMIT 1;

START TRANSACTION;

DELETE FROM lms_quiz_attempts
WHERE user_id = @user_id
  AND course_id = @course_id
  AND lesson_id IN (@lesson_rrc_mcq, @lesson_ch_qcm);

DELETE qa
FROM lms_quiz_attempts qa
INNER JOIN lms_course_lessons cl ON cl.id = qa.lesson_id
WHERE qa.user_id = @user_id
  AND qa.course_id = @course_id
  AND cl.type = 'mock_exam'
  AND (
      cl.title LIKE '%Mock Exam 01%'
      OR cl.title_fr LIKE '%Examen blanc de citoyenneté 01%'
      OR cl.title LIKE '%Citizenship Mock Exam 01%'
  );

DELETE FROM lms_quiz_attempts
WHERE user_id = @user_id
  AND course_id = @course_id
  AND status = 'in_progress';

COMMIT;

SELECT
    qa.id,
    qa.lesson_id,
    qa.attempt_number,
    qa.status,
    cl.title
FROM lms_quiz_attempts qa
LEFT JOIN lms_course_lessons cl ON cl.id = qa.lesson_id
WHERE qa.user_id = @user_id
  AND qa.course_id = @course_id
  AND (
      qa.lesson_id IN (@lesson_rrc_mcq, @lesson_ch_qcm)
      OR cl.type = 'mock_exam'
  )
ORDER BY qa.lesson_id, qa.attempt_number;