-- PREVIEW — no deletes. Works local + staging (resolves user by email).

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;

SELECT @user_id AS resolved_user_id, @user_email AS email_lookup;

SELECT id, name, email FROM users WHERE id = @user_id;

SELECT id, user_id, course_id, course_version_id, status
FROM lms_course_enrollments
WHERE user_id = @user_id AND course_id = @course_id;

SELECT
    qa.id,
    qa.lesson_id,
    qa.lesson_uid,
    qa.attempt_number,
    qa.status,
    qa.score,
    qa.correct_count,
    qa.started_at,
    qa.completed_at,
    cl.title,
    cl.type
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
ORDER BY qa.lesson_id, qa.attempt_number, qa.id;

SELECT qa.id, qa.lesson_id, qa.status, qa.attempt_number
FROM lms_quiz_attempts qa
WHERE qa.user_id = @user_id
  AND qa.course_id = @course_id
  AND qa.status = 'in_progress';