headless_lms_models/credit_registrations/
teacher_view.rs

1//! The ledger as a course's teacher surfaces show it, and what a teacher's bulk retry may move.
2
3use super::state::{CreditRegistrationErrorCode, CreditRegistrationState, ResubmissionFacts};
4use crate::library::credit_registration::{
5    PendingPreconditions, StageMatch, StudentFacingCreditRegistrationStatus,
6};
7use crate::library::students_view::escape_like_pattern;
8use crate::prelude::*;
9use crate::verified_student_numbers::StudentNumberVerificationMethod;
10
11/// The course's live rows a bulk retry can actually move: failed for good, and not held open by
12/// Suotar. Oldest first, capped by `limit`.
13///
14/// Deliberately only these. A row a retry always refuses keeps matching for as long as it exists,
15/// so letting one into the batch would spend a slot of the cap on it forever: a course holding
16/// `limit` of them could never retry anything again. [`count_submission_uncertain_by_course_id`] is
17/// what reports them.
18pub async fn get_retryable_ids_by_course_id(
19    conn: &mut PgConnection,
20    course_id: Uuid,
21    limit: i64,
22) -> ModelResult<Vec<Uuid>> {
23    let res = sqlx::query_scalar!(
24        r#"
25SELECT id
26FROM credit_registrations cr
27WHERE cr.course_id = $1
28  AND cr.state = 'failed_permanent'
29  AND (
30    cr.resubmit_not_before IS NULL
31    OR cr.resubmit_not_before <= now()
32  )
33  AND cr.superseded_by_id IS NULL
34  AND cr.deleted_at IS NULL
35ORDER BY cr.state_entered_at
36LIMIT $2
37        "#,
38        course_id,
39        limit,
40    )
41    .fetch_all(conn)
42    .await?;
43    Ok(res)
44}
45
46/// How many of a course's live rows a bulk retry has to refuse, all for the one remaining reason:
47/// the submission may have landed, so only a human may move that row.
48///
49/// Counts the whole course, not a capped window: these are the rows
50/// [`get_retryable_ids_by_course_id`] leaves out, and a teacher clicking again will never work
51/// through them.
52pub async fn count_submission_uncertain_by_course_id(
53    conn: &mut PgConnection,
54    course_id: Uuid,
55) -> ModelResult<i64> {
56    let count = sqlx::query_scalar!(
57        r#"
58SELECT COUNT(*) AS "count!"
59FROM credit_registrations cr
60WHERE cr.course_id = $1
61  AND cr.state = 'submission_uncertain'
62  AND cr.superseded_by_id IS NULL
63  AND cr.deleted_at IS NULL
64        "#,
65        course_id,
66    )
67    .fetch_one(conn)
68    .await?;
69    Ok(count)
70}
71
72/// Live rows of one course grouped by module and state, with the preconditions a `pending` row is
73/// waiting on and how many of each group the pipeline handed to support.
74///
75/// The preconditions travel with the group so a caller can classify it via
76/// [`crate::library::credit_registration::StudentFacingCreditRegistrationStatus::of`] instead of
77/// reimplementing that mapping in SQL — keeping per-module columns and per-row badges in sync.
78#[derive(Debug, Clone, PartialEq)]
79pub struct CourseModuleStateCount {
80    pub course_module_id: Uuid,
81    pub state: CreditRegistrationState,
82    pub completion_eligible: bool,
83    pub has_verified_student_number: bool,
84    pub course_code_allowed: bool,
85    pub enrolment_resolved: bool,
86    pub count: i64,
87    /// Of `count`, how many carry the pipeline's flag.
88    pub needs_admin_attention_count: i64,
89}
90
91/// The course's live rows per module and state, narrowed to one instance where the caller names
92/// one.
93pub async fn count_by_module_and_state_for_course(
94    conn: &mut PgConnection,
95    course_id: Uuid,
96    course_instance_id: Option<Uuid>,
97) -> ModelResult<Vec<CourseModuleStateCount>> {
98    let res = sqlx::query_as!(
99        CourseModuleStateCount,
100        r#"
101SELECT cr.course_module_id,
102  cr.state,
103  p.completion_eligible AS "completion_eligible!",
104  p.has_verified_student_number AS "has_verified_student_number!",
105  p.course_code_allowed AS "course_code_allowed!",
106  cr.selected_enrolment_id IS NOT NULL AS "enrolment_resolved!",
107  COUNT(*) AS "count!",
108  COUNT(*) FILTER (
109    WHERE cr.needs_admin_attention
110  ) AS "needs_admin_attention_count!"
111FROM credit_registrations cr
112  JOIN credit_registration_preconditions p ON p.credit_registration_id = cr.id
113WHERE cr.course_id = $1
114  AND (
115    $2::uuid IS NULL
116    OR cr.course_instance_id = $2
117  )
118  AND cr.superseded_by_id IS NULL
119  AND cr.deleted_at IS NULL
120GROUP BY cr.course_module_id,
121  cr.state,
122  p.completion_eligible,
123  p.has_verified_student_number,
124  p.course_code_allowed,
125  (cr.selected_enrolment_id IS NOT NULL)
126        "#,
127        course_id,
128        course_instance_id,
129    )
130    .fetch_all(conn)
131    .await?;
132    Ok(res)
133}
134
135/// One ledger row as a teacher sees it: the raw state, the student's identity and the unmasked
136/// verified student number, but never the study registry's own error text.
137#[derive(Debug, Clone)]
138pub struct TeacherCreditRegistration {
139    pub id: Uuid,
140    pub user_id: Uuid,
141    pub first_name: Option<String>,
142    pub last_name: Option<String>,
143    pub email: Option<String>,
144    pub course_id: Uuid,
145    pub course_module_id: Uuid,
146    pub course_module_name: Option<String>,
147    pub course_instance_id: Uuid,
148    pub course_module_completion_id: Uuid,
149    pub completion_date: DateTime<Utc>,
150    pub state: CreditRegistrationState,
151    pub state_entered_at: DateTime<Utc>,
152    pub error_code: Option<CreditRegistrationErrorCode>,
153    pub needs_admin_attention: bool,
154    pub next_attempt_at: DateTime<Utc>,
155    pub registered_at: Option<DateTime<Utc>>,
156    pub sisu_attainment_id: Option<String>,
157    pub grade_id: Option<String>,
158    pub credits: Option<f32>,
159    pub attempt_number: i32,
160    pub superseded_by_id: Option<Uuid>,
161    pub submitted_at: Option<DateTime<Utc>>,
162    pub resubmit_not_before: Option<DateTime<Utc>>,
163    /// Live only: a soft-deleted link is no longer a number we hold for this student.
164    pub student_number: Option<DbSecret>,
165    pub student_number_verified_at: Option<DateTime<Utc>>,
166    pub student_number_verified_via: Option<StudentNumberVerificationMethod>,
167    /// Needed to find the account's linking mails, which are keyed on the Sisu person.
168    pub sisu_person_id: Option<DbSecret>,
169    pub enrolment_resolved: bool,
170    pub enrolment_realisation_name: Option<String>,
171    pub enrolment_checked_at: Option<DateTime<Utc>>,
172    pub enrolment_check_requested_at: Option<DateTime<Utc>>,
173    pub completion_eligible: bool,
174    pub course_code_allowed: bool,
175    /// The page's total row count, so a caller can read it off the first row instead of a second
176    /// query.
177    pub total_count: i64,
178}
179
180impl TeacherCreditRegistration {
181    /// What decides whether a human may move this row; see [`ResubmissionFacts`].
182    pub fn resubmission_facts(&self) -> ResubmissionFacts {
183        ResubmissionFacts {
184            state: self.state,
185            is_superseded: self.superseded_by_id.is_some(),
186            error_code: self.error_code,
187            resubmit_not_before: self.resubmit_not_before,
188            submitted_at: self.submitted_at,
189        }
190    }
191
192    /// What a `pending` row is waiting on. The linked number is the row's own `student_number`,
193    /// which is the live link rather than the one a submitted payload froze.
194    pub fn preconditions(&self) -> PendingPreconditions {
195        PendingPreconditions {
196            completion_eligible: self.completion_eligible,
197            has_verified_student_number: self.student_number.is_some(),
198            course_code_allowed: self.course_code_allowed,
199        }
200    }
201}
202
203/// The optional narrowings a teacher surface applies, all of them in SQL.
204#[derive(Debug, Clone, Default)]
205pub struct TeacherCreditRegistrationFilters<'a> {
206    pub id: Option<Uuid>,
207    pub user_ids: Option<&'a [Uuid]>,
208    pub state: Option<CreditRegistrationState>,
209    /// Rows whose student-facing stage is one of these. Empty means no narrowing. The stage set the
210    /// teacher surfaces filter by, so a row this returns always carries a badge the filter named.
211    pub stages: &'a [StudentFacingCreditRegistrationStatus],
212    /// Matched against the student's name, email or verified student number.
213    pub search: Option<&'a str>,
214    pub course_instance_id: Option<Uuid>,
215    /// Narrows to every attempt of one completion.
216    pub course_module_completion_id: Option<Uuid>,
217}
218
219/// The one query behind every teacher-facing read, so a filter wired into a page cannot be missed
220/// in its count. `total_count` is computed before the limit, which is why the count reads it with
221/// `limit = 1`.
222async fn teacher_facing_page(
223    conn: &mut PgConnection,
224    course_id: Option<Uuid>,
225    filters: &TeacherCreditRegistrationFilters<'_>,
226    limit: i64,
227    offset: i64,
228) -> ModelResult<Vec<TeacherCreditRegistration>> {
229    let search_pattern = filters.search.map(search_pattern_of);
230    let user_search = crate::user_details::UserListSearch::new(filters.search.unwrap_or_default());
231    let stages = StageMatch::of(filters.stages);
232    let res = sqlx::query_as!(
233        TeacherCreditRegistration,
234        r#"
235SELECT cr.id,
236  cr.user_id,
237  ud.first_name AS "first_name?",
238  ud.last_name AS "last_name?",
239  ud.email AS "email?",
240  cr.course_id,
241  cr.course_module_id,
242  cm.name AS course_module_name,
243  cr.course_instance_id,
244  cr.course_module_completion_id,
245  cmc.completion_date,
246  cr.state,
247  cr.state_entered_at,
248  cr.error_code AS "error_code?",
249  cr.needs_admin_attention,
250  cr.next_attempt_at,
251  cr.registered_at,
252  cr.sisu_attainment_id,
253  cr.grade_id,
254  cr.credits,
255  cr.attempt_number,
256  cr.superseded_by_id,
257  cr.submitted_at,
258  cr.resubmit_not_before,
259  vsn.student_number AS "student_number?",
260  vsn.verified_at AS "student_number_verified_at?",
261  vsn.verified_via AS "student_number_verified_via?",
262  vsn.sisu_person_id AS "sisu_person_id?",
263  cr.selected_enrolment_id IS NOT NULL AS "enrolment_resolved!",
264  COALESCE(
265    cr.selected_enrolment_realisation_name->>'fi',
266    cr.selected_enrolment_realisation_name->>'en',
267    cr.selected_enrolment_realisation_name->>'sv'
268  ) AS "enrolment_realisation_name?",
269  cr.enrolment_checked_at,
270  cr.enrolment_check_requested_at,
271  p.completion_eligible AS "completion_eligible!",
272  p.course_code_allowed AS "course_code_allowed!",
273  COUNT(*) OVER () AS "total_count!"
274FROM credit_registrations cr
275  JOIN course_modules cm ON cm.id = cr.course_module_id AND cm.deleted_at IS NULL
276  JOIN course_module_completions cmc ON cmc.id = cr.course_module_completion_id AND cmc.deleted_at IS NULL
277  JOIN credit_registration_preconditions p ON p.credit_registration_id = cr.id
278  LEFT JOIN user_details ud ON ud.user_id = cr.user_id
279  LEFT JOIN verified_student_numbers vsn ON vsn.user_id = cr.user_id
280  AND vsn.deleted_at IS NULL
281WHERE cr.deleted_at IS NULL
282  AND ($1::uuid IS NULL OR cr.course_id = $1)
283  AND ($2::uuid IS NULL OR cr.id = $2)
284  AND ($3::uuid [] IS NULL OR cr.user_id = ANY($3))
285  AND (
286    $4::credit_registration_state IS NULL
287    OR cr.state = $4
288  )
289  AND (
290    $5::text IS NULL
291    OR (ud.name_search_helper || ' ' || ud.email_search_helper) LIKE ALL ($15::text [])
292    OR word_similarity($16::text, ud.name_search_helper || ' ' || ud.email_search_helper) >= 0.5
293    OR LOWER(vsn.student_number) LIKE '%' || $5 || '%' ESCAPE '\'
294  )
295  AND ($6::uuid IS NULL OR cr.course_instance_id = $6)
296  AND ($7::uuid IS NULL OR cr.course_module_completion_id = $7)
297  AND (
298    CARDINALITY($10::credit_registration_state []) = 0
299    OR EXISTS (
300      SELECT 1
301      FROM UNNEST(
302          $10::credit_registration_state [],
303          $11::boolean [],
304          $12::boolean [],
305          $13::boolean [],
306          $14::boolean []
307        ) AS stage(
308          state,
309          completion_eligible,
310          has_verified_student_number,
311          course_code_allowed,
312          enrolment_resolved
313        )
314      WHERE stage.state = cr.state
315        AND stage.completion_eligible = p.completion_eligible
316        AND stage.has_verified_student_number = (vsn.student_number IS NOT NULL)
317        AND stage.course_code_allowed = p.course_code_allowed
318        AND stage.enrolment_resolved = (cr.selected_enrolment_id IS NOT NULL)
319    )
320  )
321ORDER BY cmc.completion_date DESC,
322  cr.attempt_number DESC,
323  cr.id
324LIMIT $8 OFFSET $9
325        "#,
326        course_id,
327        filters.id,
328        filters.user_ids,
329        filters.state as Option<CreditRegistrationState>,
330        search_pattern.as_deref(),
331        filters.course_instance_id,
332        filters.course_module_completion_id,
333        limit,
334        offset,
335        &stages.states as &[CreditRegistrationState],
336        &stages.completion_eligible as &[bool],
337        &stages.has_verified_student_number as &[bool],
338        &stages.course_code_allowed as &[bool],
339        &stages.enrolment_resolved as &[bool],
340        &user_search.word_patterns,
341        user_search.misspelling_term.as_deref(),
342    )
343    .fetch_all(conn)
344    .await?;
345    Ok(res)
346}
347
348/// The course's ledger rows as the teacher surfaces show them, newest completion first.
349pub async fn get_teacher_facing_by_course_id(
350    conn: &mut PgConnection,
351    course_id: Uuid,
352    filters: &TeacherCreditRegistrationFilters<'_>,
353    limit: i64,
354    offset: i64,
355) -> ModelResult<Vec<TeacherCreditRegistration>> {
356    teacher_facing_page(conn, Some(course_id), filters, limit, offset).await
357}
358
359/// How many rows [`get_teacher_facing_by_course_id`] would return without a page limit.
360pub async fn count_teacher_facing_by_course_id(
361    conn: &mut PgConnection,
362    course_id: Uuid,
363    filters: &TeacherCreditRegistrationFilters<'_>,
364) -> ModelResult<i64> {
365    let rows = teacher_facing_page(conn, Some(course_id), filters, 1, 0).await?;
366    Ok(rows.first().map_or(0, |row| row.total_count))
367}
368
369/// Lowercased and with metacharacters escaped, so a search for `%` matches a literal one.
370pub(super) fn search_pattern_of(search: &str) -> String {
371    escape_like_pattern(&search.to_lowercase())
372}
373
374/// One row for a teacher surface, by id. `None` when no such live row exists.
375pub async fn get_teacher_facing_by_id(
376    conn: &mut PgConnection,
377    id: Uuid,
378) -> ModelResult<Option<TeacherCreditRegistration>> {
379    let rows = teacher_facing_page(
380        conn,
381        None,
382        &TeacherCreditRegistrationFilters {
383            id: Some(id),
384            ..TeacherCreditRegistrationFilters::default()
385        },
386        1,
387        0,
388    )
389    .await?;
390    Ok(rows.into_iter().next())
391}
392
393/// Every attempt for the same completion as `row`, that one included, newest attempt first.
394pub async fn get_teacher_facing_attempts_for_completion(
395    conn: &mut PgConnection,
396    row: &TeacherCreditRegistration,
397) -> ModelResult<Vec<TeacherCreditRegistration>> {
398    get_teacher_facing_by_course_id(
399        conn,
400        row.course_id,
401        &TeacherCreditRegistrationFilters {
402            user_ids: Some(&[row.user_id]),
403            course_module_completion_id: Some(row.course_module_completion_id),
404            ..TeacherCreditRegistrationFilters::default()
405        },
406        i64::MAX,
407        0,
408    )
409    .await
410}
headless_lms_models/credit_registrations/teacher_view.rs

headless_lms_models/credit_registrations/
teacher_view.rs
