headless_lms_models/

course_module_completions.rs

use std::borrow::Borrow;
use std::collections::{HashMap, HashSet};

use futures::Stream;
use utoipa::ToSchema;

use crate::{
    error::missing_model_error, prelude::*, study_registry_registrars::StudyRegistryRegistrar,
};

#[derive(Debug, Clone, PartialEq, Eq, Deserialize, Serialize, ToSchema)]

pub struct CourseModuleCompletion {
    pub id: Uuid,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
    pub deleted_at: Option<DateTime<Utc>>,
    pub course_id: Uuid,
    pub course_module_id: Uuid,
    pub user_id: Uuid,
    pub completion_date: DateTime<Utc>,
    pub completion_registration_attempt_date: Option<DateTime<Utc>>,
    pub completion_language: String,
    pub eligible_for_ects: bool,
    pub email: String,
    pub grade: Option<i32>,
    pub passed: bool,
    pub prerequisite_modules_completed: bool,
    pub completion_granter_user_id: Option<Uuid>,
    pub needs_to_be_reviewed: bool,
    /// Whether the push path owns this completion. See the column comment.
    pub register_credits_via_suotar: bool,
}

#[derive(Clone, PartialEq, Deserialize, Serialize)]
pub enum CourseModuleCompletionGranter {
    Automatic,
    User(Uuid),
}

impl CourseModuleCompletionGranter {
    fn to_database_field(&self) -> Option<Uuid> {
        match self {
            CourseModuleCompletionGranter::Automatic => None,
            CourseModuleCompletionGranter::User(user_id) => Some(*user_id),
        }
    }
}

#[derive(Clone, PartialEq, Deserialize, Serialize)]

pub struct NewCourseModuleCompletion {
    pub course_id: Uuid,
    pub course_module_id: Uuid,
    pub user_id: Uuid,
    pub completion_date: DateTime<Utc>,
    pub completion_registration_attempt_date: Option<DateTime<Utc>>,
    pub completion_language: String,
    pub eligible_for_ects: bool,
    pub email: String,
    pub grade: Option<i32>,
    pub passed: bool,
}

pub async fn insert(
    conn: &mut PgConnection,
    pkey_policy: PKeyPolicy<Uuid>,
    new_course_module_completion: &NewCourseModuleCompletion,
    completion_granter: CourseModuleCompletionGranter,
) -> ModelResult<CourseModuleCompletion> {
    let res = sqlx::query_as!(
        CourseModuleCompletion,
        "
INSERT INTO course_module_completions (
    id,
    course_id,
    course_module_id,
    user_id,
    completion_date,
    completion_registration_attempt_date,
    completion_language,
    eligible_for_ects,
    email,
    grade,
    passed,
    completion_granter_user_id,
    register_credits_via_suotar
  )
VALUES (
    $1,
    $2,
    $3,
    $4,
    $5,
    $6,
    $7,
    $8,
    $9,
    $10,
    $11,
    $12,
    -- Decided here rather than by the caller: the flag is what keeps the two registration paths
    -- from both claiming a completion, and a caller that forgot it would hand the row to neither.
    (
      SELECT cm.deleted_at IS NULL
        AND cm.enable_credit_registration_via_suotar
        AND cm.register_eligible_new_completions_via_suotar
        AND EXISTS (
          SELECT 1
          FROM verified_student_numbers vsn
          WHERE vsn.user_id = $4
            AND vsn.deleted_at IS NULL
        )
      FROM course_modules cm
      WHERE cm.id = $3
    )
  )
RETURNING *
        ",
        pkey_policy.into_uuid(),
        new_course_module_completion.course_id,
        new_course_module_completion.course_module_id,
        new_course_module_completion.user_id,
        new_course_module_completion.completion_date,
        new_course_module_completion.completion_registration_attempt_date,
        new_course_module_completion.completion_language,
        new_course_module_completion.eligible_for_ects,
        new_course_module_completion.email,
        new_course_module_completion.grade,
        new_course_module_completion.passed,
        completion_granter.to_database_field(),
    )
    .fetch_one(conn)
    .await?;
    Ok(res)
}

#[derive(Debug, Clone)]
pub struct NewCourseModuleCompletionSeed {
    pub course_id: Uuid,
    pub course_module_id: Uuid,
    pub user_id: Uuid,
    pub completion_date: Option<DateTime<Utc>>,
    pub completion_language: Option<String>,
    pub eligible_for_ects: Option<bool>,
    pub email: Option<String>,
    pub grade: Option<i32>,
    pub passed: Option<bool>,
    pub prerequisite_modules_completed: Option<bool>,
    pub needs_to_be_reviewed: Option<bool>,
}

pub async fn insert_seed_row(
    conn: &mut PgConnection,
    seed: &NewCourseModuleCompletionSeed,
) -> ModelResult<Uuid> {
    let res = sqlx::query!(
        r#"
        INSERT INTO course_module_completions (
            course_id,
            course_module_id,
            user_id,
            completion_date,
            completion_language,
            eligible_for_ects,
            email,
            grade,
            passed,
            prerequisite_modules_completed,
            needs_to_be_reviewed,
            register_credits_via_suotar
        )
        VALUES (
            $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,
            (
              SELECT cm.deleted_at IS NULL
                AND cm.enable_credit_registration_via_suotar
                AND cm.register_eligible_new_completions_via_suotar
                AND EXISTS (
                  SELECT 1
                  FROM verified_student_numbers vsn
                  WHERE vsn.user_id = $3
                    AND vsn.deleted_at IS NULL
                )
              FROM course_modules cm
              WHERE cm.id = $2
            )
        )
        RETURNING id
        "#,
        seed.course_id,
        seed.course_module_id,
        seed.user_id,
        seed.completion_date,
        seed.completion_language.as_deref(),
        seed.eligible_for_ects,
        seed.email.as_deref(),
        seed.grade,
        seed.passed,
        seed.prerequisite_modules_completed,
        seed.needs_to_be_reviewed,
    )
    .fetch_one(conn)
    .await?;

    Ok(res.id)
}

pub async fn get_by_id(conn: &mut PgConnection, id: Uuid) -> ModelResult<CourseModuleCompletion> {
    let res = sqlx::query_as!(
        CourseModuleCompletion,
        r#"
SELECT *
FROM course_module_completions
WHERE id = $1
  AND deleted_at IS NULL
        "#,
        id,
    )
    .fetch_one(conn)
    .await?;
    Ok(res)
}

/// Also returns soft deleted completions so that we can make sure the process does not crash if a completion is deleted before we get it back from the study registry.
pub async fn get_by_ids(
    conn: &mut PgConnection,
    ids: &[Uuid],
) -> ModelResult<Vec<CourseModuleCompletion>> {
    let res = sqlx::query_as!(
        CourseModuleCompletion,
        "
SELECT *
FROM course_module_completions
WHERE id = ANY($1)
        ",
        ids,
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

pub async fn get_by_ids_as_map(
    conn: &mut PgConnection,
    ids: &[Uuid],
) -> ModelResult<HashMap<Uuid, CourseModuleCompletion>> {
    let res = get_by_ids(conn, ids)
        .await?
        .into_iter()
        .map(|x| (x.id, x))
        .collect();
    Ok(res)
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]

pub struct CourseModuleCompletionWithRegistrationInfo {
    /// When the student has attempted to register the completion.
    pub completion_registration_attempt_date: Option<DateTime<Utc>>,
    /// ID of the course module.
    pub course_module_id: Uuid,
    /// When the record was created
    pub created_at: DateTime<Utc>,
    /// Grade that the student received for the completion.
    pub grade: Option<i32>,
    /// Whether or not the student is eligible for credit for the completion.
    pub passed: bool,
    /// Whether or not the student is qualified for credit based on other modules in the course.
    pub prerequisite_modules_completed: bool,
    /// Whether or not the completion has been registered to a study registry.
    pub registered: bool,
    /// Whether or not the completion needs to be reviewed by the teacher.
    pub needs_to_be_reviewed: bool,
    /// ID of the user for the completion.
    pub user_id: Uuid,
    // When the user completed the course
    pub completion_date: DateTime<Utc>,
}

/// Gets summaries for all completions on the given course instance.
pub async fn get_all_with_registration_information_by_course_instance_id(
    conn: &mut PgConnection,
    course_instance_id: Uuid,
    course_id: Uuid,
) -> ModelResult<Vec<CourseModuleCompletionWithRegistrationInfo>> {
    let res = sqlx::query_as!(
        CourseModuleCompletionWithRegistrationInfo,
        r#"
SELECT completions.completion_registration_attempt_date,
  completions.course_module_id,
  completions.created_at,
  completions.grade,
  completions.passed,
  completions.prerequisite_modules_completed,
  (registered.id IS NOT NULL) AS "registered!",
  completions.needs_to_be_reviewed,
  completions.user_id,
  completions.completion_date
FROM course_module_completions completions
  LEFT JOIN course_module_completion_registered_to_study_registries registered ON (
    completions.id = registered.course_module_completion_id
  )
  JOIN user_course_settings settings ON (
    completions.user_id = settings.user_id
    AND settings.current_course_id = completions.course_id
  )
WHERE settings.current_course_instance_id = $1
  AND completions.deleted_at IS NULL
  AND registered.deleted_at IS NULL
  AND settings.deleted_at IS NULL
  AND settings.current_course_id = $2
        "#,
        course_instance_id,
        course_id
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

/// Gets all module completions for the user on a course. There can be multiple modules
/// in a single course, so the result is a `Vec`.
pub async fn get_all_by_course_id_and_user_id(
    conn: &mut PgConnection,
    course_id: Uuid,
    user_id: Uuid,
) -> ModelResult<Vec<CourseModuleCompletion>> {
    let res = sqlx::query_as!(
        CourseModuleCompletion,
        "
SELECT *
FROM course_module_completions
WHERE course_id = $1
  AND user_id = $2
  AND deleted_at IS NULL
        ",
        course_id,
        user_id,
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

pub async fn get_all_by_user_id(
    conn: &mut PgConnection,
    user_id: Uuid,
) -> ModelResult<Vec<CourseModuleCompletion>> {
    let res = sqlx::query_as!(
        CourseModuleCompletion,
        "
SELECT *
FROM course_module_completions
WHERE user_id = $1
  AND deleted_at IS NULL
        ",
        user_id,
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

pub async fn get_all_by_user_id_and_course_module_id(
    conn: &mut PgConnection,
    user_id: Uuid,
    course_module_id: Uuid,
) -> ModelResult<Vec<CourseModuleCompletion>> {
    let res = sqlx::query_as!(
        CourseModuleCompletion,
        "
SELECT *
FROM course_module_completions
WHERE user_id = $1
  AND course_module_id = $2
  AND deleted_at IS NULL
        ",
        user_id,
        course_module_id,
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

pub async fn get_all_by_course_module_and_user_ids(
    conn: &mut PgConnection,
    course_module_id: Uuid,
    user_id: Uuid,
) -> ModelResult<Vec<CourseModuleCompletion>> {
    let res = sqlx::query_as!(
        CourseModuleCompletion,
        "
SELECT *
FROM course_module_completions
WHERE course_module_id = $1
  AND user_id = $2
  AND deleted_at IS NULL
        ",
        course_module_id,
        user_id,
    )
    .fetch_all(conn)
    .await?;
    Ok(res)
}

/// Gets latest created completion for the given user on the specified course module.
pub async fn get_latest_by_course_and_user_ids(
    conn: &mut PgConnection,
    course_module_id: Uuid,
    user_id: Uuid,
) -> ModelResult<CourseModuleCompletion> {
    let res = sqlx::query_as!(
        CourseModuleCompletion,
        "
SELECT *
FROM course_module_completions
WHERE course_module_id = $1
  AND user_id = $2
  AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 1
        ",
        course_module_id,
        user_id,
    )
    .fetch_one(conn)
    .await?;
    Ok(res)
}

pub async fn get_best_completion_by_user_and_course_module_id(
    conn: &mut PgConnection,
    user_id: Uuid,
    course_module_id: Uuid,
) -> ModelResult<Option<CourseModuleCompletion>> {
    let completions = sqlx::query_as!(
        CourseModuleCompletion,
        r#"
SELECT *
FROM course_module_completions
WHERE user_id = $1
  AND course_module_id = $2
  AND deleted_at IS NULL
        "#,
        user_id,
        course_module_id,
    )
    .fetch_all(conn)
    .await?;

    Ok(select_best_completion(completions))
}

/// Finds the best grade
pub fn select_best_completion<C: Borrow<CourseModuleCompletion>>(
    completions: impl IntoIterator<Item = C>,
) -> Option<C> {
    // Passed outranks not passed before grades are compared: ranking by grade alone let a failed
    // graded completion beat a passed pass/fail one, so a failure was reported as the best result.
    // `created_at` and `id` only break ties, so two equally good completions resolve to the newest
    // one instead of to whichever order the caller's query happened to return.
    completions.into_iter().max_by_key(|completion| {
        let completion = completion.borrow();
        (
            completion.passed,
            completion.grade.unwrap_or(0),
            completion.created_at,
            completion.id,
        )
    })
}

/// Which of `ids` the push path will create a credit registration for, now or on its next
/// materialise tick: those in `credit_registration_eligible_completions`.
pub async fn get_credit_registration_expected_ids(
    conn: &mut PgConnection,
    ids: &[Uuid],
) -> ModelResult<HashSet<Uuid>> {
    let res = sqlx::query_scalar!(
        r#"
SELECT course_module_completion_id AS "course_module_completion_id!"
FROM credit_registration_eligible_completions
WHERE course_module_completion_id = ANY($1)
        "#,
        ids,
    )
    .fetch_all(conn)
    .await?;
    Ok(res.into_iter().collect())
}

/// The completion that decides which credit registration flow a student gets for one module: one
/// opted in to `register_credits_via_suotar` outranks the rest, then the newest wins. While the push
/// path owns a completion, the student must not be sent to the old flow for another.
///
/// Pass completions awaiting review too: leaving them out could switch the flow and so reveal the
/// flag. Not [`select_best_completion`], which picks the result shown to the student.
pub fn select_registration_completion<C: Borrow<CourseModuleCompletion>>(
    completions: impl IntoIterator<Item = C>,
) -> Option<C> {
    completions.into_iter().max_by_key(|completion| {
        let completion = completion.borrow();
        (
            completion.register_credits_via_suotar,
            completion.created_at,
            completion.id,
        )
    })
}

/// [`select_registration_completion`] over the user's completions of the module. Errors with
/// `RecordNotFound` if there are none.
pub async fn get_registration_completion_by_user_and_course_module_id(
    conn: &mut PgConnection,
    user_id: Uuid,
    course_module_id: Uuid,
) -> ModelResult<CourseModuleCompletion> {
    let completions =
        get_all_by_course_module_and_user_ids(conn, course_module_id, user_id).await?;
    select_registration_completion(completions).ok_or_else(missing_model_error(
        ModelErrorType::RecordNotFound,
        "The user has no completion for this course module.".to_string(),
    ))
}

/// Get the number of students that have completed the course
pub async fn get_count_of_distinct_completors_by_course_id(
    conn: &mut PgConnection,
    course_id: Uuid,
) -> ModelResult<i64> {
    let res = sqlx::query!(
        "
SELECT COUNT(DISTINCT user_id) as count
FROM course_module_completions
WHERE course_id = $1
  AND deleted_at IS NULL
",
        course_id,
    )
    .fetch_one(conn)
    .await?;
    Ok(res.count.unwrap_or(0))
}

/// Gets automatically granted course module completion for the given user on the specified course.
/// This entry is quaranteed to be unique in database by the index
/// `course_module_automatic_completion_uniqueness`.
pub async fn get_automatic_completion_by_course_module_course_and_user_ids(
    conn: &mut PgConnection,
    course_module_id: Uuid,
    course_id: Uuid,
    user_id: Uuid,
) -> ModelResult<CourseModuleCompletion> {
    let res = sqlx::query_as!(
        CourseModuleCompletion,
        "
SELECT *
FROM course_module_completions
WHERE course_module_id = $1
  AND course_id = $2
  AND user_id = $3
  AND completion_granter_user_id IS NULL
  AND deleted_at IS NULL
        ",
        course_module_id,
        course_id,
        user_id,
    )
    .fetch_one(conn)
    .await?;
    Ok(res)
}

/// True if the user has at least one non-deleted, teacher-granted (manual) completion in the
/// course. A manual completion means a teacher vouched for the student, which exempts them from
/// automatic cheating suspicion for the whole course.
pub async fn user_has_manual_completion_in_course(
    conn: &mut PgConnection,
    user_id: Uuid,
    course_id: Uuid,
) -> ModelResult<bool> {
    let res = sqlx::query!(
        r#"
SELECT EXISTS (
  SELECT 1
  FROM course_module_completions
  WHERE user_id = $1
    AND course_id = $2
    AND completion_granter_user_id IS NOT NULL
    AND deleted_at IS NULL
) AS "exists!"
        "#,
        user_id,
        course_id,
    )
    .fetch_one(conn)
    .await?;
    Ok(res.exists)
}

pub async fn update_completion_registration_attempt_date(
    conn: &mut PgConnection,
    id: Uuid,
    completion_registration_attempt_date: DateTime<Utc>,
) -> ModelResult<bool> {
    let res = sqlx::query!(
        "
UPDATE course_module_completions
SET completion_registration_attempt_date = $1
WHERE id = $2
  AND deleted_at IS NULL
        ",
        Some(completion_registration_attempt_date),
        id,
    )
    .execute(conn)
    .await?;
    Ok(res.rows_affected() > 0)
}

/// Rewrites a completion's grade in place, so a test can regrade one.
///
/// Exists only for test setup, behind the mock study registry's control surface: a teacher regrade
/// goes through the manual completion flow, which writes a *new* completion row rather than editing
/// this one, and there is no product path that edits a completion's grade.
pub async fn set_grade_for_testing(
    conn: &mut PgConnection,
    id: Uuid,
    grade: Option<i32>,
    passed: Option<bool>,
) -> ModelResult<()> {
    sqlx::query!(
        "
UPDATE course_module_completions
SET grade = $2,
  passed = COALESCE($3, passed)
WHERE id = $1
  AND deleted_at IS NULL
        ",
        id,
        grade,
        passed,
    )
    .execute(conn)
    .await?;
    Ok(())
}

pub async fn update_prerequisite_modules_completed(
    conn: &mut PgConnection,
    id: Uuid,
    prerequisite_modules_completed: bool,
) -> ModelResult<bool> {
    let res = sqlx::query!(
        "
UPDATE course_module_completions SET prerequisite_modules_completed = $1
WHERE id = $2 AND deleted_at IS NULL
    ",
        prerequisite_modules_completed,
        id
    )
    .execute(conn)
    .await?;
    Ok(res.rows_affected() > 0)
}

pub async fn update_needs_to_be_reviewed(
    conn: &mut PgConnection,
    id: Uuid,
    needs_to_be_reviewed: bool,
) -> ModelResult<bool> {
    let res = sqlx::query!(
        "
UPDATE course_module_completions SET needs_to_be_reviewed = $1
WHERE id = $2 AND deleted_at IS NULL
        ",
        needs_to_be_reviewed,
        id
    )
    .execute(conn)
    .await?;
    Ok(res.rows_affected() > 0)
}

pub async fn update_needs_to_be_reviewed_by_course_and_user_ids(
    conn: &mut PgConnection,
    course_id: Uuid,
    user_id: Uuid,
    needs_to_be_reviewed: bool,
) -> ModelResult<bool> {
    let res = sqlx::query!(
        "
UPDATE course_module_completions SET needs_to_be_reviewed = $1
WHERE course_id = $2 AND user_id = $3 AND deleted_at IS NULL
        ",
        needs_to_be_reviewed,
        course_id,
        user_id,
    )
    .execute(conn)
    .await?;
    Ok(res.rows_affected() > 0)
}

/// Checks whether the user has any completions for the given course module on the specified
/// course module.
pub async fn user_has_completed_course_module(
    conn: &mut PgConnection,
    user_id: Uuid,
    course_module_id: Uuid,
) -> ModelResult<bool> {
    let res = get_all_by_course_module_and_user_ids(conn, course_module_id, user_id).await?;
    Ok(!res.is_empty())
}

/// Completion in the form that is recognized by authorized third party study registry registrars.
#[derive(Clone, PartialEq, Deserialize, Serialize)]

pub struct StudyRegistryCompletion {
    /// The date when the student completed the course. The value of this field is the date that will
    /// end up in the user's study registry as the completion date. If the completion is created
    /// automatically, it is the date when the student passed the completion thresholds. If the teacher
    /// creates these completions manually, the teacher inputs this value. Usually the teacher would in
    /// this case input the date of the exam.
    pub completion_date: DateTime<Utc>,
    /// The language used in the completion of the course.
    pub completion_language: String,
    /// Date when the student opened the form to register their credits to the open university.
    pub completion_registration_attempt_date: Option<DateTime<Utc>>,
    /// Email at the time of completing the course. Used to match the student to the data that they will
    /// fill to the open university and it will remain unchanged in the event of email change because
    /// changing this would break the matching.
    pub email: String,
    /// The grade to be passed to the study registry. Uses the sisu format. See the struct documentation for details.
    pub grade: StudyRegistryGrade,
    /// ID of the completion.
    pub id: Uuid,
    /// User id in courses.mooc.fi for received registered completions.
    pub user_id: Uuid,
    /// Tier of the completion. Currently always null. Historically used for example to distinguish between
    /// intermediate and advanced versions of the Building AI course.
    pub tier: Option<i32>,
}

impl From<CourseModuleCompletion> for StudyRegistryCompletion {
    fn from(completion: CourseModuleCompletion) -> Self {
        Self {
            completion_date: completion.completion_date,
            completion_language: completion.completion_language,
            completion_registration_attempt_date: completion.completion_registration_attempt_date,
            email: completion.email,
            grade: StudyRegistryGrade::new(completion.passed, completion.grade),
            id: completion.id,
            user_id: completion.user_id,
            tier: None,
        }
    }
}

impl StudyRegistryCompletion {
    pub fn normalize_language_code(&mut self) {
        match self.completion_language.as_str() {
            "en" => self.completion_language = "en-GB".to_string(),
            "fi" => self.completion_language = "fi-FI".to_string(),
            "sv" => self.completion_language = "sv-SE".to_string(),
            _ => {}
        }
    }
}

/// Grading object that maps the system grading information to Sisu's grading scales.
///
/// Currently only `sis-0-5` and `sis-hyv-hyl` scales are supported in the system.
///
/// All grading scales can be found from <https://sis-helsinki-test.funidata.fi/api/graphql> using
/// the following query:
///
/// ```graphql
/// query {
///   grade_scales {
///     id
///     name {
///       fi
///       en
///       sv
///     }
///     grades {
///       name {
///         fi
///         en
///         sv
///       }
///       passed
///       localId
///       abbreviation {
///         fi
///         en
///         sv
///       }
///     }
///     abbreviation {
///       fi
///       en
///       sv
///     }
///   }
/// }
/// ```
#[derive(Clone, PartialEq, Deserialize, Serialize)]

pub struct StudyRegistryGrade {
    pub scale: String,
    pub grade: String,
}

impl StudyRegistryGrade {
    pub fn new(passed: bool, grade: Option<i32>) -> Self {
        match grade {
            Some(grade) => Self {
                scale: "sis-0-5".to_string(),
                grade: grade.to_string(),
            },
            None => Self {
                scale: "sis-hyv-hyl".to_string(),
                grade: if passed {
                    "1".to_string()
                } else {
                    "0".to_string()
                },
            },
        }
    }
}
/// Streams completions.
///
/// If no_completions_registered_by_this_study_registry_registrar is None, then all completions are streamed.
pub fn stream_by_course_module_id<'a>(
    conn: &'a mut PgConnection,
    course_module_ids: &'a [Uuid],
    no_completions_registered_by_this_study_registry_registrar: &'a Option<StudyRegistryRegistrar>,
) -> impl Stream<Item = sqlx::Result<StudyRegistryCompletion>> + Send + 'a {
    // If this is none, we're using a null uuid, which will never match anything. Therefore, no completions will be filtered out.
    let study_module_registrar_id = no_completions_registered_by_this_study_registry_registrar
        .clone()
        .map(|o| o.id)
        .unwrap_or(Uuid::nil());

    sqlx::query_as!(
        CourseModuleCompletion,
        r#"
SELECT *
FROM course_module_completions
WHERE course_module_id = ANY($1)
  AND prerequisite_modules_completed
  AND eligible_for_ects IS TRUE
  -- Completions still awaiting suspected-cheater review are withheld from study-registry
  -- registration until a teacher dismisses or confirms them.
  AND needs_to_be_reviewed = FALSE
  AND deleted_at IS NULL
  -- Completions on the push path are registered by us; letting the registry pull them too would put
  -- a second attainment on the student's transcript. Per student and module, not per module: a
  -- module can be switched on while students with no flagged completion stay the pull path's, and
  -- a flagged completion takes its siblings along, since any of them registers the same credit.
  AND NOT EXISTS (
    SELECT 1
    FROM course_module_completions sibling
    WHERE sibling.user_id = course_module_completions.user_id
      AND sibling.course_module_id = course_module_completions.course_module_id
      AND sibling.register_credits_via_suotar
      AND sibling.deleted_at IS NULL
  )
  -- Belt and braces behind the flag above: once the push path has sent anything for the student and
  -- module, it stays out for good, since re-registering would double the attainment on a real
  -- transcript.
  AND NOT EXISTS (
    SELECT 1
    FROM credit_registrations cr
    WHERE cr.user_id = course_module_completions.user_id
      AND cr.course_module_id = course_module_completions.course_module_id
      AND cr.submitted_at IS NOT NULL
      AND cr.deleted_at IS NULL
  )
  AND id NOT IN (
    SELECT course_module_completion_id
    FROM course_module_completion_registered_to_study_registries
    WHERE course_module_id = ANY($1)
      AND (
        study_registry_registrar_id = $2
        -- Our own rows count as already registered too: what the push path put in the registry is
        -- in it whichever registrar's export this is.
        OR study_registry_registrar_id IS NULL
      )
      AND deleted_at IS NULL
  )
        "#,
        course_module_ids,
        study_module_registrar_id,
    )
    .map(StudyRegistryCompletion::from)
    .fetch(conn)
}

pub async fn delete(conn: &mut PgConnection, id: Uuid) -> ModelResult<()> {
    sqlx::query!(
        "

UPDATE course_module_completions
SET deleted_at = now()
WHERE id = $1
AND deleted_at IS NULL
        ",
        id,
    )
    .execute(conn)
    .await?;
    Ok(())
}

pub async fn find_existing(
    conn: &mut PgConnection,
    course_id: Uuid,
    course_module_id: Uuid,
    user_id: Uuid,
) -> ModelResult<Uuid> {
    let row = sqlx::query!(
        r#"
        SELECT id
        FROM course_module_completions
        WHERE course_id = $1
          AND course_module_id = $2
          AND user_id = $3
          AND completion_granter_user_id IS NULL
          AND deleted_at IS NULL
        "#,
        course_id,
        course_module_id,
        user_id,
    )
    .fetch_one(conn)
    .await?;

    Ok(row.id)
}