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 stages = StageMatch::of(filters.stages);
231    let res = sqlx::query_as!(
232        TeacherCreditRegistration,
233        r#"
234SELECT cr.id,
235  cr.user_id,
236  ud.first_name AS "first_name?",
237  ud.last_name AS "last_name?",
238  ud.email AS "email?",
239  cr.course_id,
240  cr.course_module_id,
241  cm.name AS course_module_name,
242  cr.course_instance_id,
243  cr.course_module_completion_id,
244  cmc.completion_date,
245  cr.state,
246  cr.state_entered_at,
247  cr.error_code AS "error_code?",
248  cr.needs_admin_attention,
249  cr.next_attempt_at,
250  cr.registered_at,
251  cr.sisu_attainment_id,
252  cr.grade_id,
253  cr.credits,
254  cr.attempt_number,
255  cr.superseded_by_id,
256  cr.submitted_at,
257  cr.resubmit_not_before,
258  vsn.student_number AS "student_number?",
259  vsn.verified_at AS "student_number_verified_at?",
260  vsn.verified_via AS "student_number_verified_via?",
261  vsn.sisu_person_id AS "sisu_person_id?",
262  cr.selected_enrolment_id IS NOT NULL AS "enrolment_resolved!",
263  COALESCE(
264    cr.selected_enrolment_realisation_name->>'fi',
265    cr.selected_enrolment_realisation_name->>'en',
266    cr.selected_enrolment_realisation_name->>'sv'
267  ) AS "enrolment_realisation_name?",
268  cr.enrolment_checked_at,
269  cr.enrolment_check_requested_at,
270  p.completion_eligible AS "completion_eligible!",
271  p.course_code_allowed AS "course_code_allowed!",
272  COUNT(*) OVER () AS "total_count!"
273FROM credit_registrations cr
274  JOIN course_modules cm ON cm.id = cr.course_module_id AND cm.deleted_at IS NULL
275  JOIN course_module_completions cmc ON cmc.id = cr.course_module_completion_id AND cmc.deleted_at IS NULL
276  JOIN credit_registration_preconditions p ON p.credit_registration_id = cr.id
277  LEFT JOIN user_details ud ON ud.user_id = cr.user_id
278  LEFT JOIN verified_student_numbers vsn ON vsn.user_id = cr.user_id
279  AND vsn.deleted_at IS NULL
280WHERE cr.deleted_at IS NULL
281  AND ($1::uuid IS NULL OR cr.course_id = $1)
282  AND ($2::uuid IS NULL OR cr.id = $2)
283  AND ($3::uuid [] IS NULL OR cr.user_id = ANY($3))
284  AND (
285    $4::credit_registration_state IS NULL
286    OR cr.state = $4
287  )
288  AND (
289    $5::text IS NULL
290    OR ud.name_search_helper LIKE '%' || $5 || '%' ESCAPE '\'
291    OR ud.email_search_helper LIKE '%' || $5 || '%' ESCAPE '\'
292    OR LOWER(vsn.student_number) LIKE '%' || $5 || '%' ESCAPE '\'
293  )
294  AND ($6::uuid IS NULL OR cr.course_instance_id = $6)
295  AND ($7::uuid IS NULL OR cr.course_module_completion_id = $7)
296  AND (
297    CARDINALITY($10::credit_registration_state []) = 0
298    OR EXISTS (
299      SELECT 1
300      FROM UNNEST(
301          $10::credit_registration_state [],
302          $11::boolean [],
303          $12::boolean [],
304          $13::boolean [],
305          $14::boolean []
306        ) AS stage(
307          state,
308          completion_eligible,
309          has_verified_student_number,
310          course_code_allowed,
311          enrolment_resolved
312        )
313      WHERE stage.state = cr.state
314        AND stage.completion_eligible = p.completion_eligible
315        AND stage.has_verified_student_number = (vsn.student_number IS NOT NULL)
316        AND stage.course_code_allowed = p.course_code_allowed
317        AND stage.enrolment_resolved = (cr.selected_enrolment_id IS NOT NULL)
318    )
319  )
320ORDER BY cmc.completion_date DESC,
321  cr.attempt_number DESC,
322  cr.id
323LIMIT $8 OFFSET $9
324        "#,
325        course_id,
326        filters.id,
327        filters.user_ids,
328        filters.state as Option<CreditRegistrationState>,
329        search_pattern.as_deref(),
330        filters.course_instance_id,
331        filters.course_module_completion_id,
332        limit,
333        offset,
334        &stages.states as &[CreditRegistrationState],
335        &stages.completion_eligible as &[bool],
336        &stages.has_verified_student_number as &[bool],
337        &stages.course_code_allowed as &[bool],
338        &stages.enrolment_resolved as &[bool],
339    )
340    .fetch_all(conn)
341    .await?;
342    Ok(res)
343}
344
345/// The course's ledger rows as the teacher surfaces show them, newest completion first.
346pub async fn get_teacher_facing_by_course_id(
347    conn: &mut PgConnection,
348    course_id: Uuid,
349    filters: &TeacherCreditRegistrationFilters<'_>,
350    limit: i64,
351    offset: i64,
352) -> ModelResult<Vec<TeacherCreditRegistration>> {
353    teacher_facing_page(conn, Some(course_id), filters, limit, offset).await
354}
355
356/// How many rows [`get_teacher_facing_by_course_id`] would return without a page limit.
357pub async fn count_teacher_facing_by_course_id(
358    conn: &mut PgConnection,
359    course_id: Uuid,
360    filters: &TeacherCreditRegistrationFilters<'_>,
361) -> ModelResult<i64> {
362    let rows = teacher_facing_page(conn, Some(course_id), filters, 1, 0).await?;
363    Ok(rows.first().map_or(0, |row| row.total_count))
364}
365
366/// Lowercased and with metacharacters escaped, so a search for `%` matches a literal one.
367pub(super) fn search_pattern_of(search: &str) -> String {
368    escape_like_pattern(&search.to_lowercase())
369}
370
371/// One row for a teacher surface, by id. `None` when no such live row exists.
372pub async fn get_teacher_facing_by_id(
373    conn: &mut PgConnection,
374    id: Uuid,
375) -> ModelResult<Option<TeacherCreditRegistration>> {
376    let rows = teacher_facing_page(
377        conn,
378        None,
379        &TeacherCreditRegistrationFilters {
380            id: Some(id),
381            ..TeacherCreditRegistrationFilters::default()
382        },
383        1,
384        0,
385    )
386    .await?;
387    Ok(rows.into_iter().next())
388}
389
390/// Every attempt for the same completion as `row`, that one included, newest attempt first.
391pub async fn get_teacher_facing_attempts_for_completion(
392    conn: &mut PgConnection,
393    row: &TeacherCreditRegistration,
394) -> ModelResult<Vec<TeacherCreditRegistration>> {
395    get_teacher_facing_by_course_id(
396        conn,
397        row.course_id,
398        &TeacherCreditRegistrationFilters {
399            user_ids: Some(&[row.user_id]),
400            course_module_completion_id: Some(row.course_module_completion_id),
401            ..TeacherCreditRegistrationFilters::default()
402        },
403        i64::MAX,
404        0,
405    )
406    .await
407}
headless_lms_models/credit_registrations/teacher_view.rs

headless_lms_models/credit_registrations/
teacher_view.rs
