headless_lms_models/library/

students_view.rs

//! Contains helper functions needed for student view
use crate::chapters::{self, ChapterAvailability, DatabaseChapter, UserChapterProgress};
use crate::credit_registrations::CreditRegistrationState;
use crate::library::credit_registration::{StageMatch, StudentFacingCreditRegistrationStatus};
use crate::prelude::*;
use crate::user_chapter_locking_statuses::UserChapterLockingStatus;
use chrono::{DateTime, Utc};
use utoipa::ToSchema;

/// One row of the paginated student identity list (one row per distinct enrolled user).
#[derive(Clone, PartialEq, Deserialize, Serialize, sqlx::FromRow, ToSchema)]

pub struct CourseStudentListRow {
    pub user_id: Uuid,
    pub first_name: Option<String>,
    pub last_name: Option<String>,
    pub email: Option<String>,
    /// Names of the non-deleted course instances the user is enrolled in for this course.
    pub course_instances: Vec<String>,
    /// Whether the user has any enrollment into a non-deleted instance. Separates the unnamed default
    /// instance (true) from a since-deleted instance (false) when `course_instances` is empty.
    pub has_active_instance: bool,
}

/// A page of the student identity list plus the total number of pages for the current filters.
#[derive(Clone, PartialEq, Deserialize, Serialize, ToSchema)]

pub struct StudentsListPage {
    pub data: Vec<CourseStudentListRow>,
    pub total_pages: u32,
}

/// Escapes the `LIKE`/`ILIKE` metacharacters `\`, `%` and `_` so a search string is matched
/// literally (used together with `ESCAPE '\'` in the query).
pub fn escape_like_pattern(input: &str) -> String {
    input
        .replace('\\', "\\\\")
        .replace('%', "\\%")
        .replace('_', "\\_")
}

/// Grade filter values accepted by [`get_course_students_page`], beyond a literal numeric grade
/// string (the sis-0-5 scale, `"0"`..`"5"`).
pub const GRADE_FILTER_NOT_COMPLETED: &str = "not_completed";
pub const GRADE_FILTER_PASSED: &str = "passed";
pub const GRADE_FILTER_FAILED: &str = "failed";

/// Returns a filtered, sorted, paginated page of the course's enrolled users (identity only).
///
/// `sort_column` (`last_name` | `first_name` | `email` | `total_points`) and `sort_direction` are
/// narrowed to fixed literals and bound as parameters, never interpolated from raw input. `search`
/// matches names and emails as [`crate::user_details::UserListSearch`] describes (every word, in any order,
/// or a near match for a typo), plus an exact user-id match when it parses as a UUID.
/// `course_instance_id` narrows to a single instance.
///
/// `module_id` + `grade` together narrow to students whose *latest* completion of that module matches:
/// a numeric grade string (sis-0-5 scale), [`GRADE_FILTER_PASSED`]/[`GRADE_FILTER_FAILED`] (the
/// sis-hyv-hyl scale, i.e. `grade IS NULL`), or [`GRADE_FILTER_NOT_COMPLETED`] (no completion row at
/// all). A numerically graded module's completions never match `passed`/`failed` -- those only ever
/// apply to modules that use the pass/fail scale, mirroring how `CompletionsTab` renders the grade
/// column (a numeric grade takes precedence over passed/failed). `grade` is ignored unless `module_id`
/// is also set.
///
/// `registration_stages` narrows to students holding at least one live credit registration at one of
/// those stages, within `course_instance_id` and `module_id` where either is given. Empty means no
/// narrowing.
#[allow(clippy::too_many_arguments)]
pub async fn get_course_students_page(
    conn: &mut PgConnection,
    course_id: Uuid,
    pagination: Pagination,
    search: Option<&str>,
    sort_column: Option<&str>,
    sort_direction: Option<&str>,
    course_instance_id: Option<Uuid>,
    module_id: Option<Uuid>,
    grade: Option<&str>,
    registration_stages: &[StudentFacingCreditRegistrationStatus],
) -> ModelResult<StudentsListPage> {
    let stages = StageMatch::of(registration_stages);
    // Empty/blank search behaves like no search.
    let search = search.map(str::trim).filter(|s| !s.is_empty());
    let user_id_exact = search.and_then(|s| Uuid::parse_str(s).ok());
    // The helper columns are lowercased generated columns, so lowercase the term and escape the LIKE
    // metacharacters (matched literally via `ESCAPE '\'`).
    let search_pattern = search.map(|s| escape_like_pattern(&s.to_lowercase()));
    let user_search = crate::user_details::UserListSearch::new(search.unwrap_or_default());
    // A `grade` without a `module_id` has nothing to scope it to, so it is dropped rather than
    // matched against every module.
    let grade_filter = module_id.and(grade);

    // Both the sort column and the direction are narrowed to a fixed literal here, so the query can
    // bind them and stay one offline-checked shape instead of being built by string formatting.
    let sort_column = match sort_column {
        Some("first_name") => "first_name",
        Some("email") => "email",
        Some("total_points") => "total_points",
        _ => "last_name",
    };
    let sort_direction = match sort_direction {
        Some("desc") | Some("DESC") => "desc",
        _ => "asc",
    };

    let total_count = sqlx::query_scalar!(
        r#"
SELECT COUNT(*) AS "count!"
FROM (
  SELECT u.id
  FROM course_instance_enrollments cie
    JOIN users u ON u.id = cie.user_id
    LEFT JOIN user_details ud ON ud.user_id = u.id
    LEFT JOIN LATERAL (
      SELECT cmc.grade, cmc.passed
      FROM course_module_completions cmc
      WHERE cmc.user_id = u.id
        AND cmc.course_id = $1
        AND cmc.course_module_id = $5
        AND cmc.deleted_at IS NULL
      ORDER BY cmc.completion_date DESC
      LIMIT 1
    ) gm ON $5::uuid IS NOT NULL
  WHERE cie.course_id = $1
    AND cie.deleted_at IS NULL
    AND u.deleted_at IS NULL
    AND ($2::uuid IS NULL OR cie.course_instance_id = $2)
    AND (
      $3::text IS NULL
      OR (ud.name_search_helper || ' ' || ud.email_search_helper) LIKE ALL ($12::text [])
      OR word_similarity($13::text, ud.name_search_helper || ' ' || ud.email_search_helper) >= 0.5
      OR ($4::uuid IS NOT NULL AND u.id = $4)
    )
    AND (
      $6::text IS NULL
      OR ($6 = 'not_completed' AND gm.grade IS NULL AND gm.passed IS NULL)
      OR ($6 = 'passed' AND gm.grade IS NULL AND gm.passed = true)
      OR ($6 = 'failed' AND gm.grade IS NULL AND gm.passed = false)
      OR ($6 ~ '^[0-9]+$' AND gm.grade = $6::int)
    )
    AND (
      CARDINALITY($7::credit_registration_state []) = 0
      OR EXISTS (
        SELECT 1
        FROM credit_registrations cr
          JOIN credit_registration_preconditions p ON p.credit_registration_id = cr.id
          JOIN UNNEST(
              $7::credit_registration_state [],
              $8::boolean [],
              $9::boolean [],
              $10::boolean [],
              $11::boolean []
            ) AS stage(
              state,
              completion_eligible,
              has_verified_student_number,
              course_code_allowed,
              enrolment_resolved
            )
            ON stage.state = cr.state
            AND stage.completion_eligible = p.completion_eligible
            AND stage.has_verified_student_number = p.has_verified_student_number
            AND stage.course_code_allowed = p.course_code_allowed
            AND stage.enrolment_resolved = (cr.selected_enrolment_id IS NOT NULL)
        WHERE cr.user_id = u.id
          AND cr.course_id = $1
          AND cr.superseded_by_id IS NULL
          AND cr.deleted_at IS NULL
          AND ($2::uuid IS NULL OR cr.course_instance_id = $2)
          AND ($5::uuid IS NULL OR cr.course_module_id = $5)
      )
    )
  GROUP BY u.id
) t
        "#,
        course_id,
        course_instance_id,
        search_pattern.as_deref(),
        user_id_exact,
        module_id,
        grade_filter,
        &stages.states as &[CreditRegistrationState],
        &stages.completion_eligible as &[bool],
        &stages.has_verified_student_number as &[bool],
        &stages.course_code_allowed as &[bool],
        &stages.enrolment_resolved as &[bool],
        &user_search.word_patterns,
        user_search.misspelling_term.as_deref(),
    )
    .fetch_one(&mut *conn)
    .await?;

    // Each sort key appears twice, once per direction, and yields NULL in every row that the bound
    // column and direction do not select -- an all-NULL key orders nothing, which is what lets one
    // fixed ORDER BY stand in for the eight column/direction combinations. `u.id` breaks ties so
    // paging over equal sort keys (duplicate/NULL names, duplicate emails) never skips or repeats a
    // student.
    let data = sqlx::query_as!(
        CourseStudentListRow,
        r#"
SELECT
  u.id AS "user_id!",
  ud.first_name AS "first_name?",
  ud.last_name AS "last_name?",
  ud.email AS "email?",
  COALESCE(
    array_agg(DISTINCT ci.name) FILTER (WHERE ci.name IS NOT NULL),
    ARRAY[]::text[]
  ) AS "course_instances!: Vec<String>",
  COALESCE(bool_or(ci.id IS NOT NULL), false) AS "has_active_instance!"
FROM course_instance_enrollments cie
  JOIN users u ON u.id = cie.user_id
  LEFT JOIN user_details ud ON ud.user_id = u.id
  LEFT JOIN course_instances ci
    ON ci.id = cie.course_instance_id
   AND ci.deleted_at IS NULL
  LEFT JOIN LATERAL (
    SELECT cmc.grade, cmc.passed
    FROM course_module_completions cmc
    WHERE cmc.user_id = u.id
      AND cmc.course_id = $1
      AND cmc.course_module_id = $7
      AND cmc.deleted_at IS NULL
    ORDER BY cmc.completion_date DESC
    LIMIT 1
  ) gm ON $7::uuid IS NOT NULL
  LEFT JOIN (
    SELECT ues.user_id, COALESCE(SUM(ues.score_given), 0)::double precision AS total_points
    FROM user_exercise_states ues
      JOIN exercises ex ON ex.id = ues.exercise_id
    WHERE ues.course_id = $1
      AND ues.deleted_at IS NULL
      AND ex.deleted_at IS NULL
    GROUP BY ues.user_id
  ) points ON points.user_id = u.id
WHERE cie.course_id = $1
  AND cie.deleted_at IS NULL
  AND u.deleted_at IS NULL
  AND ($4::uuid IS NULL OR cie.course_instance_id = $4)
  AND (
    $2::text IS NULL
    OR (ud.name_search_helper || ' ' || ud.email_search_helper) LIKE ALL ($16::text [])
    OR word_similarity($17::text, ud.name_search_helper || ' ' || ud.email_search_helper) >= 0.5
    OR ($3::uuid IS NOT NULL AND u.id = $3)
  )
  AND (
    $8::text IS NULL
    OR ($8 = 'not_completed' AND gm.grade IS NULL AND gm.passed IS NULL)
    OR ($8 = 'passed' AND gm.grade IS NULL AND gm.passed = true)
    OR ($8 = 'failed' AND gm.grade IS NULL AND gm.passed = false)
    OR ($8 ~ '^[0-9]+$' AND gm.grade = $8::int)
  )
  AND (
    CARDINALITY($11::credit_registration_state []) = 0
    OR EXISTS (
      SELECT 1
      FROM credit_registrations cr
        JOIN credit_registration_preconditions p ON p.credit_registration_id = cr.id
        JOIN UNNEST(
            $11::credit_registration_state [],
            $12::boolean [],
            $13::boolean [],
            $14::boolean [],
            $15::boolean []
          ) AS stage(
            state,
            completion_eligible,
            has_verified_student_number,
            course_code_allowed,
            enrolment_resolved
          )
          ON stage.state = cr.state
          AND stage.completion_eligible = p.completion_eligible
          AND stage.has_verified_student_number = p.has_verified_student_number
          AND stage.course_code_allowed = p.course_code_allowed
          AND stage.enrolment_resolved = (cr.selected_enrolment_id IS NOT NULL)
      WHERE cr.user_id = u.id
        AND cr.course_id = $1
        AND cr.superseded_by_id IS NULL
        AND cr.deleted_at IS NULL
        AND ($4::uuid IS NULL OR cr.course_instance_id = $4)
        AND ($7::uuid IS NULL OR cr.course_module_id = $7)
    )
  )
GROUP BY u.id, ud.first_name, ud.last_name, ud.email
ORDER BY
  CASE
    WHEN $9 = 'total_points' AND $10 = 'asc' THEN COALESCE(MAX(points.total_points), 0)
  END ASC NULLS LAST,
  CASE
    WHEN $9 = 'total_points' AND $10 = 'desc' THEN COALESCE(MAX(points.total_points), 0)
  END DESC NULLS LAST,
  CASE
    WHEN $10 <> 'asc' THEN NULL
    WHEN $9 = 'first_name' THEN LOWER(TRIM(ud.first_name))
    WHEN $9 = 'email' THEN LOWER(ud.email)
    WHEN $9 = 'last_name' THEN LOWER(TRIM(ud.last_name))
  END ASC NULLS LAST,
  CASE
    WHEN $10 <> 'desc' THEN NULL
    WHEN $9 = 'first_name' THEN LOWER(TRIM(ud.first_name))
    WHEN $9 = 'email' THEN LOWER(ud.email)
    WHEN $9 = 'last_name' THEN LOWER(TRIM(ud.last_name))
  END DESC NULLS LAST,
  CASE
    WHEN $9 = 'first_name' THEN LOWER(TRIM(ud.last_name))
    WHEN $9 = 'last_name' THEN LOWER(TRIM(ud.first_name))
  END ASC NULLS LAST,
  u.id ASC
LIMIT $5 OFFSET $6
        "#,
        course_id,
        search_pattern.as_deref(),
        user_id_exact,
        course_instance_id,
        pagination.limit(),
        pagination.offset(),
        module_id,
        grade_filter,
        sort_column,
        sort_direction,
        &stages.states as &[CreditRegistrationState],
        &stages.completion_eligible as &[bool],
        &stages.has_verified_student_number as &[bool],
        &stages.course_code_allowed as &[bool],
        &stages.enrolment_resolved as &[bool],
        &user_search.word_patterns,
        user_search.misspelling_term.as_deref(),
    )
    .fetch_all(&mut *conn)
    .await?;

    Ok(StudentsListPage {
        data,
        total_pages: pagination.total_pages(total_count as u32),
    })
}

#[derive(Clone, PartialEq, Deserialize, Serialize, sqlx::FromRow, ToSchema)]

pub struct CompletionGridRow {
    pub user_id: Uuid,
    pub module_id: Uuid, // stable key for pivoting (module names are not unique)
    pub module: Option<String>, // empty/default row can be None
    pub grade: Option<i32>, // raw numeric grade, if any
    pub passed: Option<bool>, // pass/fail when there is no numeric grade
    pub registered: bool, // registered to a study registry
    pub needs_to_be_reviewed: bool,
}

/// Returns student × module completion rows for the given users, keyed by `user_id`.
pub async fn get_completions_grid_for_users(
    conn: &mut PgConnection,
    course_id: Uuid,
    user_ids: &[Uuid],
) -> ModelResult<Vec<CompletionGridRow>> {
    let rows = sqlx::query_as!(
        CompletionGridRow,
        r#"
WITH modules AS (
  SELECT id AS module_id, name AS module_name, order_number
  FROM course_modules
  WHERE course_id = $1
    AND deleted_at IS NULL
),
targets AS (
  SELECT DISTINCT user_id
  FROM course_instance_enrollments
  WHERE course_id = $1
    AND deleted_at IS NULL
    AND user_id = ANY($2::uuid[])
),
latest_cmc AS (
  SELECT DISTINCT ON (cmc.user_id, cmc.course_module_id)
    cmc.id,
    cmc.user_id,
    cmc.course_module_id,
    cmc.grade,
    cmc.passed,
    cmc.completion_date,
    cmc.needs_to_be_reviewed
  FROM course_module_completions cmc
  WHERE cmc.course_id = $1
    AND cmc.deleted_at IS NULL
    AND cmc.user_id = ANY($2::uuid[])
  ORDER BY cmc.user_id, cmc.course_module_id, cmc.completion_date DESC
),
cmcr AS (
  SELECT course_module_completion_id
  FROM course_module_completion_registered_to_study_registries
  WHERE course_id = $1
    AND deleted_at IS NULL
)
SELECT
  e.user_id AS "user_id!",
  m.module_id AS "module_id!",
  m.module_name AS "module?",
  r.grade AS "grade?",
  r.passed AS "passed?",
  (r.id IS NOT NULL AND r.id IN (SELECT course_module_completion_id FROM cmcr)) AS "registered!",
  COALESCE(r.needs_to_be_reviewed, false) AS "needs_to_be_reviewed!"
FROM modules m
CROSS JOIN targets e
LEFT JOIN latest_cmc r
  ON r.user_id = e.user_id
 AND r.course_module_id = m.module_id
ORDER BY m.order_number, e.user_id
        "#,
        course_id,
        user_ids
    )
    .fetch_all(&mut *conn)
    .await?;

    Ok(rows)
}

#[derive(Clone, PartialEq, Deserialize, Serialize, sqlx::FromRow, ToSchema)]

pub struct CertificateGridRow {
    pub user_id: Uuid,
    pub date_issued: Option<DateTime<Utc>>,
    pub verification_id: Option<String>,
    pub certificate_id: Option<Uuid>,
    pub name_on_certificate: Option<String>,
}

/// Returns the latest course certificate (if any) for each of the given users, keyed by `user_id`.
pub async fn get_certificates_grid_for_users(
    conn: &mut PgConnection,
    course_id: Uuid,
    user_ids: &[Uuid],
) -> ModelResult<Vec<CertificateGridRow>> {
    let rows = sqlx::query_as!(
        CertificateGridRow,
        r#"
WITH targets AS (
  SELECT DISTINCT user_id
  FROM course_instance_enrollments
  WHERE course_id = $1
    AND deleted_at IS NULL
    AND user_id = ANY($2::uuid[])
),
user_certs AS (
  -- one latest certificate per user for this course
  SELECT DISTINCT ON (gc.user_id)
    gc.user_id,
    gc.id,
    gc.created_at AS latest_issued_at,
    gc.verification_id,
    gc.name_on_certificate
  FROM generated_certificates gc
  JOIN certificate_configuration_to_requirements cctr
    ON gc.certificate_configuration_id = cctr.certificate_configuration_id
   AND cctr.deleted_at IS NULL
  JOIN course_modules cm
    ON cm.id = cctr.course_module_id
   AND cm.deleted_at IS NULL
  WHERE cm.course_id = $1
    AND gc.deleted_at IS NULL
    AND gc.user_id = ANY($2::uuid[])
  ORDER BY gc.user_id, gc.created_at DESC
)
SELECT
  e.user_id AS "user_id!",
  uc.latest_issued_at AS "date_issued?",
  uc.verification_id AS "verification_id?",
  uc.id AS "certificate_id?",
  uc.name_on_certificate AS "name_on_certificate?"
FROM targets e
LEFT JOIN user_certs uc ON uc.user_id = e.user_id
        "#,
        course_id,
        user_ids
    )
    .fetch_all(&mut *conn)
    .await?;

    Ok(rows)
}

/// Course-level progress structure for the Progress tab. Does not depend on which students are on
/// the current page, so it is fetched once and cached per course (not per identity page).
#[derive(Clone, PartialEq, Deserialize, Serialize, ToSchema)]

pub struct CourseStudentsProgressStructure {
    pub chapter_locking_enabled: bool,
    pub chapters: Vec<DatabaseChapter>,
    pub chapter_availability: Vec<ChapterAvailability>,
}

/// Per-user progress detail for the Progress tab, scoped to the requested `user_ids`.
#[derive(Clone, PartialEq, Deserialize, Serialize, ToSchema)]

pub struct CourseStudentsProgressUsers {
    pub user_chapter_progress: Vec<UserChapterProgress>,
    pub user_chapter_locking_statuses: Vec<UserChapterLockingStatus>,
}

/// Returns the course-level chapter structure shared by every page of the Progress tab.
pub async fn get_progress_structure(
    conn: &mut PgConnection,
    course_id: Uuid,
) -> ModelResult<CourseStudentsProgressStructure> {
    let course = crate::courses::get_course(conn, course_id).await?;
    let chapters = crate::chapters::get_course_chapters(conn, course_id).await?;
    let chapter_availability = chapters::fetch_chapter_availability(conn, course_id).await?;

    Ok(CourseStudentsProgressStructure {
        chapter_locking_enabled: course.chapter_locking_enabled,
        chapters,
        chapter_availability,
    })
}

/// Returns per-user chapter progress and locking statuses for the given `user_ids`.
pub async fn get_progress_for_users(
    conn: &mut PgConnection,
    course_id: Uuid,
    user_ids: &[Uuid],
) -> ModelResult<CourseStudentsProgressUsers> {
    let course = crate::courses::get_course(conn, course_id).await?;
    let user_chapter_progress =
        chapters::fetch_user_chapter_progress(conn, course_id, Some(user_ids)).await?;
    let user_chapter_locking_statuses =
        crate::user_chapter_locking_statuses::get_for_users_and_course(conn, user_ids, &course)
            .await?;

    Ok(CourseStudentsProgressUsers {
        user_chapter_progress,
        user_chapter_locking_statuses,
    })
}