-- name: CountPublishedExamPrepPracticesInLesson :one
SELECT
    count(*)::int AS n
FROM
    exam_prep.lesson_practices p
    INNER JOIN question_sets qs ON qs.id = p.question_set_id
WHERE
    p.unit_module_lesson_id = $1
    AND qs.set_type = 'PRACTICE'
    AND qs.status = 'PUBLISHED'
    AND p.publish_status = 'PUBLISHED';

-- name: CountUserCompletedPublishedExamPrepPracticesInLesson :one
SELECT
    count(*)::int AS n
FROM
    exam_prep.lesson_practices p
    INNER JOIN question_sets qs ON qs.id = p.question_set_id
    INNER JOIN user_practice_progress upp ON upp.question_set_id = p.question_set_id
WHERE
    p.unit_module_lesson_id = $1
    AND upp.user_id = $2
    AND upp.completed_at IS NOT NULL
    AND qs.set_type = 'PRACTICE'
    AND qs.status = 'PUBLISHED'
    AND p.publish_status = 'PUBLISHED';

-- name: CountPublishedExamPrepPracticesInModule :one
SELECT
    count(*)::int AS n
FROM
    exam_prep.lesson_practices p
    INNER JOIN exam_prep.unit_module_lessons l ON l.id = p.unit_module_lesson_id
    INNER JOIN question_sets qs ON qs.id = p.question_set_id
WHERE
    l.unit_module_id = $1
    AND qs.set_type = 'PRACTICE'
    AND qs.status = 'PUBLISHED'
    AND p.publish_status = 'PUBLISHED';

-- name: CountUserCompletedPublishedExamPrepPracticesInModule :one
SELECT
    count(*)::int AS n
FROM
    exam_prep.lesson_practices p
    INNER JOIN exam_prep.unit_module_lessons l ON l.id = p.unit_module_lesson_id
    INNER JOIN question_sets qs ON qs.id = p.question_set_id
    INNER JOIN user_practice_progress upp ON upp.question_set_id = p.question_set_id
WHERE
    l.unit_module_id = $1
    AND upp.user_id = $2
    AND upp.completed_at IS NOT NULL
    AND qs.set_type = 'PRACTICE'
    AND qs.status = 'PUBLISHED'
    AND p.publish_status = 'PUBLISHED';

-- name: CountPublishedExamPrepPracticesInUnit :one
SELECT
    count(*)::int AS n
FROM
    exam_prep.lesson_practices p
    INNER JOIN exam_prep.unit_module_lessons l ON l.id = p.unit_module_lesson_id
    INNER JOIN exam_prep.unit_modules m ON m.id = l.unit_module_id
    INNER JOIN question_sets qs ON qs.id = p.question_set_id
WHERE
    m.unit_id = $1
    AND qs.set_type = 'PRACTICE'
    AND qs.status = 'PUBLISHED'
    AND p.publish_status = 'PUBLISHED';

-- name: CountUserCompletedPublishedExamPrepPracticesInUnit :one
SELECT
    count(*)::int AS n
FROM
    exam_prep.lesson_practices p
    INNER JOIN exam_prep.unit_module_lessons l ON l.id = p.unit_module_lesson_id
    INNER JOIN exam_prep.unit_modules m ON m.id = l.unit_module_id
    INNER JOIN question_sets qs ON qs.id = p.question_set_id
    INNER JOIN user_practice_progress upp ON upp.question_set_id = p.question_set_id
WHERE
    m.unit_id = $1
    AND upp.user_id = $2
    AND upp.completed_at IS NOT NULL
    AND qs.set_type = 'PRACTICE'
    AND qs.status = 'PUBLISHED'
    AND p.publish_status = 'PUBLISHED';

-- name: CountPublishedExamPrepPracticesInCatalogCourse :one
SELECT
    count(*)::int AS n
FROM
    exam_prep.lesson_practices p
    INNER JOIN exam_prep.unit_module_lessons l ON l.id = p.unit_module_lesson_id
    INNER JOIN exam_prep.unit_modules m ON m.id = l.unit_module_id
    INNER JOIN exam_prep.units u ON u.id = m.unit_id
    INNER JOIN question_sets qs ON qs.id = p.question_set_id
WHERE
    u.catalog_course_id = $1
    AND qs.set_type = 'PRACTICE'
    AND qs.status = 'PUBLISHED'
    AND p.publish_status = 'PUBLISHED';

-- name: CountUserCompletedPublishedExamPrepPracticesInCatalogCourse :one
SELECT
    count(*)::int AS n
FROM
    exam_prep.lesson_practices p
    INNER JOIN exam_prep.unit_module_lessons l ON l.id = p.unit_module_lesson_id
    INNER JOIN exam_prep.unit_modules m ON m.id = l.unit_module_id
    INNER JOIN exam_prep.units u ON u.id = m.unit_id
    INNER JOIN question_sets qs ON qs.id = p.question_set_id
    INNER JOIN user_practice_progress upp ON upp.question_set_id = p.question_set_id
WHERE
    u.catalog_course_id = $1
    AND upp.user_id = $2
    AND upp.completed_at IS NOT NULL
    AND qs.set_type = 'PRACTICE'
    AND qs.status = 'PUBLISHED'
    AND p.publish_status = 'PUBLISHED';