headless_lms_models/credit_registrations/
admin_view.rs

1//! The ledger as the admin explorer and the reconciliation detectors read it, across courses.
2
3use std::collections::BTreeMap;
4
5use super::registration::{CreditRegistration, is_waiting_for_enrolment};
6use super::state::{CreditRegistrationErrorCode, CreditRegistrationState, ResubmissionFacts};
7use super::teacher_view::search_pattern_of;
8use crate::credit_registration_enrolment_routes::CreditRegistrationEnrolmentRoute;
9use crate::library::credit_registration::enrolment_check_schedule::EnrolmentCheckSource;
10use crate::library::credit_registration::timeline::{
11    Engagement, StepMatch, TimelinePosition, TimelineStep,
12};
13use crate::library::credit_registration::{CreditRegistrationPendingReason, PendingPreconditions};
14use crate::prelude::*;
15use crate::verified_student_numbers::StudentNumberVerificationMethod;
16
17/// One ledger row as an admin sees it: every identifier support needs to answer "what happened to
18/// this student", across courses.
19///
20/// Not the study registry's own error text: it is written for an integrator, may name a person and
21/// is untranslated. The error code and the scrubbed call bodies stand in for it.
22#[derive(Debug, Clone)]
23pub struct AdminCreditRegistration {
24    pub id: Uuid,
25    pub created_at: DateTime<Utc>,
26    pub user_id: Uuid,
27    pub first_name: Option<String>,
28    pub last_name: Option<String>,
29    /// In full: the admin view exists to resolve support cases, which starts from the address.
30    pub email: Option<String>,
31    pub course_id: Uuid,
32    pub course_name: String,
33    pub course_module_id: Uuid,
34    pub course_module_name: Option<String>,
35    pub course_instance_id: Uuid,
36    pub course_module_completion_id: Uuid,
37    pub completion_date: DateTime<Utc>,
38    pub state: CreditRegistrationState,
39    pub state_entered_at: DateTime<Utc>,
40    pub state_changed_at: DateTime<Utc>,
41    pub phase_started_at: DateTime<Utc>,
42    pub error_code: Option<CreditRegistrationErrorCode>,
43    pub needs_admin_attention: bool,
44    pub next_attempt_at: DateTime<Utc>,
45    pub last_attempt_at: Option<DateTime<Utc>>,
46    pub submitted_at: Option<DateTime<Utc>>,
47    pub registered_at: Option<DateTime<Utc>>,
48    pub terminal_at: Option<DateTime<Utc>>,
49    /// Frozen on the row when it left `checking_enrolment`, so it is what we actually sent.
50    pub student_number: Option<DbSecret>,
51    pub sisu_person_id: Option<DbSecret>,
52    pub uh_course_code: Option<String>,
53    pub selected_enrolment_id: Option<String>,
54    pub grade_scale_id: Option<String>,
55    pub grade_id: Option<String>,
56    pub credits: Option<f32>,
57    pub submitted_attainment_id: Option<String>,
58    pub sisu_attainment_id: Option<String>,
59    pub submit_retry_count: i32,
60    pub verify_attempt_count: i32,
61    pub attempt_number: i32,
62    pub superseded_by_id: Option<Uuid>,
63    /// The account's live link now, which may differ from the number frozen on the row.
64    pub verified_student_number: Option<DbSecret>,
65    pub verified_student_number_at: Option<DateTime<Utc>>,
66    pub verified_student_number_via: Option<StudentNumberVerificationMethod>,
67    pub resubmit_not_before: Option<DateTime<Utc>>,
68    pub partially_registered_at: Option<DateTime<Utc>>,
69    pub not_registered_reimport_count: i32,
70    pub no_usable_enrolment_since: Option<DateTime<Utc>>,
71    pub enrolment_checked_at: Option<DateTime<Utc>>,
72    pub enrolment_check_anchor_at: Option<DateTime<Utc>>,
73    pub enrolment_check_due_at: Option<DateTime<Utc>>,
74    pub enrolment_checks_stopped_at: Option<DateTime<Utc>>,
75    pub completion_eligible: bool,
76    pub has_verified_student_number: bool,
77    pub course_code_allowed: bool,
78    /// The student's latest "I have enrolled" press for the completion.
79    pub pressed_at: Option<DateTime<Utc>>,
80    pub enrolment_route: Option<CreditRegistrationEnrolmentRoute>,
81    pub last_visited_at: Option<DateTime<Utc>>,
82    /// The page's total row count, so a caller can read it off the first row instead of a second
83    /// query.
84    pub total_count: i64,
85}
86
87impl AdminCreditRegistration {
88    /// What decides whether a human may move this row; see [`ResubmissionFacts`].
89    pub fn resubmission_facts(&self) -> ResubmissionFacts {
90        ResubmissionFacts {
91            state: self.state,
92            is_superseded: self.superseded_by_id.is_some(),
93            error_code: self.error_code,
94            resubmit_not_before: self.resubmit_not_before,
95            submitted_at: self.submitted_at,
96        }
97    }
98
99    /// See [`is_waiting_for_enrolment`].
100    pub fn is_waiting_for_enrolment(&self) -> bool {
101        is_waiting_for_enrolment(
102            self.state,
103            self.enrolment_check_anchor_at,
104            self.no_usable_enrolment_since,
105        )
106    }
107
108    /// What a `pending` row is waiting on, as the row's precondition flags say.
109    pub fn preconditions(&self) -> PendingPreconditions {
110        PendingPreconditions {
111            completion_eligible: self.completion_eligible,
112            has_verified_student_number: self.has_verified_student_number,
113            course_code_allowed: self.course_code_allowed,
114        }
115    }
116
117    /// Where the row stands on the admin timeline.
118    pub fn position(&self) -> TimelinePosition {
119        TimelinePosition::of(
120            self.state,
121            self.preconditions(),
122            self.selected_enrolment_id.is_some(),
123            Engagement::of(self.pressed_at, self.last_visited_at),
124        )
125    }
126
127    /// When the row entered its current phase. A row held for its course code moved there from
128    /// waiting for a student number when the number was linked, which changed no state.
129    pub fn phase_started_at(&self) -> DateTime<Utc> {
130        match self.position().step {
131            TimelineStep::HeldForCourseCode => self
132                .verified_student_number_at
133                .map_or(self.phase_started_at, |linked_at| {
134                    linked_at.max(self.phase_started_at)
135                }),
136            _ => self.phase_started_at,
137        }
138    }
139
140    /// What this row is waiting on, or `None` where it is not waiting at all: outside `pending` the
141    /// preconditions say nothing about why the row is where it is.
142    pub fn pending_reason(&self) -> Option<CreditRegistrationPendingReason> {
143        (self.state == CreditRegistrationState::Pending)
144            .then(|| self.preconditions().reason())
145            .flatten()
146    }
147}
148
149/// How the explorer orders a page. Descending only: an ops table is read newest-worst first.
150#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
151pub enum AdminCreditRegistrationSort {
152    #[default]
153    LastActivity,
154    Created,
155    TimeInState,
156    Attempts,
157}
158
159impl AdminCreditRegistrationSort {
160    /// Bound into the query's `ORDER BY` as a `text` parameter.
161    fn as_str(self) -> &'static str {
162        match self {
163            Self::LastActivity => "last_activity",
164            Self::Created => "created",
165            Self::TimeInState => "time_in_state",
166            Self::Attempts => "attempts",
167        }
168    }
169}
170
171/// The narrowings the admin explorer applies, all of them in SQL.
172#[derive(Debug, Clone, Default)]
173pub struct AdminCreditRegistrationFilters<'a> {
174    pub states: Option<&'a [CreditRegistrationState]>,
175    pub error_codes: Option<&'a [CreditRegistrationErrorCode]>,
176    pub course_id: Option<Uuid>,
177    pub course_module_id: Option<Uuid>,
178    pub user_id: Option<Uuid>,
179    pub student_number: Option<&'a str>,
180    /// Rows at one of these timeline steps. Empty means no narrowing. A row waiting for a student
181    /// number matches only if it completed since `account_linking_since`, as the linking page and
182    /// [`count_by_step_and_engagement`] count them.
183    pub steps: &'a [TimelineStep],
184    /// Rows with one of these engagements, at steps that wait on the student. Empty means no
185    /// narrowing.
186    pub engagements: &'a [Engagement],
187    /// Leaves out not-started rows at steps that wait on the student.
188    pub hide_not_started: bool,
189    pub account_linking_since: Option<DateTime<Utc>>,
190    /// Listed first, in the sort order among themselves.
191    pub first_ids: Option<&'a [Uuid]>,
192    pub submitted_after: Option<DateTime<Utc>>,
193    pub submitted_before: Option<DateTime<Utc>>,
194    /// Matched against the student's name and email, either student number, the attainment ids and
195    /// the stored error text. Searching that text is not rendering it.
196    pub search: Option<&'a str>,
197    /// A uuid typed into the search box: a registration, a user or a completion id. Ambiguous by
198    /// design, for a human's paste. A caller that already knows which single field it means should
199    /// use `id` or `course_module_completion_id` instead, not this plus a Rust-side filter.
200    pub search_id: Option<Uuid>,
201    /// Exactly one registration.
202    pub id: Option<Uuid>,
203    /// Every attempt against one completion.
204    pub course_module_completion_id: Option<Uuid>,
205    /// An exact set of rows, for a caller that already knows which ones it wants.
206    pub credit_registration_ids: Option<&'a [Uuid]>,
207    /// Off by default, or a course that regrades shows two rows per student.
208    pub include_superseded: bool,
209}
210
211/// The query behind [`get_admin_facing`]. `total_count` is computed before the limit, so every row
212/// of a page carries the count of the whole filtered ledger.
213async fn admin_facing_page(
214    conn: &mut PgConnection,
215    filters: &AdminCreditRegistrationFilters<'_>,
216    sort: AdminCreditRegistrationSort,
217    limit: i64,
218    offset: i64,
219) -> ModelResult<Vec<AdminCreditRegistration>> {
220    let search_pattern = filters.search.map(search_pattern_of);
221    let user_search = crate::user_details::UserListSearch::new(filters.search.unwrap_or_default());
222    let step_match = StepMatch::all();
223    let res = sqlx::query_as!(
224        AdminCreditRegistration,
225        r#"
226SELECT cr.id,
227  cr.created_at,
228  cr.user_id,
229  ud.first_name AS "first_name?",
230  ud.last_name AS "last_name?",
231  ud.email AS "email?",
232  cr.course_id,
233  c.name AS course_name,
234  cr.course_module_id,
235  cm.name AS course_module_name,
236  cr.course_instance_id,
237  cr.course_module_completion_id,
238  cmc.completion_date,
239  cr.state,
240  cr.state_entered_at,
241  cr.state_changed_at,
242  cr.phase_started_at,
243  cr.error_code AS "error_code?",
244  cr.needs_admin_attention,
245  cr.next_attempt_at,
246  cr.last_attempt_at,
247  cr.submitted_at,
248  cr.registered_at,
249  cr.terminal_at,
250  cr.student_number,
251  cr.sisu_person_id,
252  cr.uh_course_code,
253  cr.selected_enrolment_id,
254  cr.grade_scale_id,
255  cr.grade_id,
256  cr.credits,
257  cr.submitted_attainment_id,
258  cr.sisu_attainment_id,
259  cr.submit_retry_count,
260  cr.verify_attempt_count,
261  cr.attempt_number,
262  cr.superseded_by_id,
263  vsn.student_number AS "verified_student_number?",
264  vsn.verified_at AS "verified_student_number_at?",
265  vsn.verified_via AS "verified_student_number_via?",
266  cr.resubmit_not_before,
267  cr.partially_registered_at,
268  cr.not_registered_reimport_count,
269  cr.no_usable_enrolment_since,
270  cr.enrolment_checked_at,
271  cr.enrolment_check_anchor_at,
272  cr.enrolment_check_due_at,
273  cr.enrolment_checks_stopped_at,
274  p.completion_eligible AS "completion_eligible!",
275  p.has_verified_student_number AS "has_verified_student_number!",
276  p.course_code_allowed AS "course_code_allowed!",
277  route.enrolment_confirmed_at AS "pressed_at?",
278  route.route AS "enrolment_route?: CreditRegistrationEnrolmentRoute",
279  sig.last_visited_at AS "last_visited_at?",
280  COUNT(*) OVER () AS "total_count!"
281FROM credit_registrations cr
282  JOIN courses c ON c.id = cr.course_id
283  JOIN course_modules cm ON cm.id = cr.course_module_id
284  JOIN course_module_completions cmc ON cmc.id = cr.course_module_completion_id
285  JOIN credit_registration_preconditions p ON p.credit_registration_id = cr.id
286  LEFT JOIN user_details ud ON ud.user_id = cr.user_id
287  LEFT JOIN verified_student_numbers vsn ON vsn.user_id = cr.user_id
288  AND vsn.deleted_at IS NULL
289  LEFT JOIN credit_registration_enrolment_routes route ON route.course_module_completion_id = cr.course_module_completion_id
290  AND route.deleted_at IS NULL
291  LEFT JOIN credit_registration_enrolment_check_signals sig ON sig.course_module_completion_id = cr.course_module_completion_id
292  AND sig.deleted_at IS NULL
293  -- The step and engagement are computed exactly as in count_by_step_and_engagement, so a list
294  -- filtered to a step and engagement holds as many rows as that function counts.
295  LEFT JOIN LATERAL (
296    SELECT m.step
297    FROM UNNEST(
298        $19::credit_registration_state [],
299        $20::boolean [],
300        $21::boolean [],
301        $22::boolean [],
302        $23::boolean [],
303        $24::credit_registration_timeline_step []
304      ) AS m(
305        state,
306        completion_eligible,
307        has_verified_student_number,
308        course_code_allowed,
309        enrolment_resolved,
310        step
311      )
312    WHERE m.state = cr.state
313      AND m.completion_eligible = p.completion_eligible
314      AND m.has_verified_student_number = p.has_verified_student_number
315      AND m.course_code_allowed = p.course_code_allowed
316      AND m.enrolment_resolved = (cr.selected_enrolment_id IS NOT NULL)
317  ) ts ON TRUE
318  CROSS JOIN LATERAL (
319    SELECT CASE
320        WHEN ts.step = ANY($25::credit_registration_timeline_step []) THEN CASE
321          WHEN route.enrolment_confirmed_at IS NOT NULL THEN 'pressed'::credit_registration_engagement
322          WHEN sig.last_visited_at IS NOT NULL THEN 'visited'::credit_registration_engagement
323          ELSE 'not_started'::credit_registration_engagement
324        END
325      END AS engagement,
326      ts.step = 'waiting_for_student_number'
327      AND cmc.completion_date < $29::timestamptz AS is_before_linking
328  ) eng
329WHERE cr.deleted_at IS NULL
330  AND ($1::bool OR cr.superseded_by_id IS NULL)
331  AND (
332    $2::credit_registration_state [] IS NULL
333    OR cr.state = ANY($2)
334  )
335  AND (
336    $3::credit_registration_error_code [] IS NULL
337    OR cr.error_code = ANY($3)
338  )
339  AND ($4::uuid IS NULL OR cr.course_id = $4)
340  AND ($5::uuid IS NULL OR cr.course_module_id = $5)
341  AND ($6::uuid IS NULL OR cr.user_id = $6)
342  AND (
343    $7::text IS NULL
344    OR cr.student_number = $7
345    OR vsn.student_number = $7
346  )
347  AND (
348    CARDINALITY($8::credit_registration_timeline_step []) = 0
349    OR (
350      ts.step = ANY($8)
351      AND eng.is_before_linking IS NOT TRUE
352    )
353  )
354  AND (
355    CARDINALITY($26::credit_registration_engagement []) = 0
356    OR eng.engagement = ANY($26)
357  )
358  AND (
359    NOT $27::bool
360    OR eng.engagement IS DISTINCT FROM 'not_started'
361  )
362  AND ($9::timestamptz IS NULL OR cr.submitted_at >= $9)
363  AND ($10::timestamptz IS NULL OR cr.submitted_at <= $10)
364  AND (
365    $11::text IS NULL
366    OR (ud.name_search_helper || ' ' || ud.email_search_helper) LIKE ALL ($30::text [])
367    OR word_similarity($31::text, ud.name_search_helper || ' ' || ud.email_search_helper) >= 0.5
368    OR LOWER(cr.student_number) LIKE '%' || $11 || '%' ESCAPE '\'
369    OR LOWER(vsn.student_number) LIKE '%' || $11 || '%' ESCAPE '\'
370    OR LOWER(cr.submitted_attainment_id) LIKE '%' || $11 || '%' ESCAPE '\'
371    OR LOWER(cr.sisu_attainment_id) LIKE '%' || $11 || '%' ESCAPE '\'
372    OR LOWER(cr.error_message) LIKE '%' || $11 || '%' ESCAPE '\'
373  )
374  AND (
375    $12::uuid IS NULL
376    OR cr.id = $12
377    OR cr.user_id = $12
378    OR cr.course_module_completion_id = $12
379  )
380  AND (
381    $13::uuid [] IS NULL
382    OR cr.id = ANY($13)
383  )
384  AND ($17::uuid IS NULL OR cr.id = $17)
385  AND (
386    $18::uuid IS NULL
387    OR cr.course_module_completion_id = $18
388  )
389ORDER BY COALESCE(cr.id = ANY($28::uuid []), FALSE) DESC,
390  CASE
391    WHEN $14::text = 'attempts' THEN cr.submit_retry_count + cr.verify_attempt_count
392  END DESC NULLS LAST,
393  CASE $14::text
394    WHEN 'created' THEN cr.created_at
395    WHEN 'time_in_state' THEN cr.state_changed_at
396    ELSE COALESCE(cr.last_attempt_at, cr.state_entered_at)
397  END DESC,
398  cr.id
399LIMIT $15 OFFSET $16
400        "#,
401        filters.include_superseded,
402        filters.states as Option<&[CreditRegistrationState]>,
403        filters.error_codes as Option<&[CreditRegistrationErrorCode]>,
404        filters.course_id,
405        filters.course_module_id,
406        filters.user_id,
407        filters.student_number,
408        filters.steps as &[TimelineStep],
409        filters.submitted_after,
410        filters.submitted_before,
411        search_pattern.as_deref(),
412        filters.search_id,
413        filters.credit_registration_ids as Option<&[Uuid]>,
414        sort.as_str(),
415        limit,
416        offset,
417        filters.id,
418        filters.course_module_completion_id,
419        &step_match.states as &[CreditRegistrationState],
420        &step_match.completion_eligible,
421        &step_match.has_verified_student_number,
422        &step_match.course_code_allowed,
423        &step_match.enrolment_resolved,
424        &step_match.steps as &[TimelineStep],
425        &TimelineStep::ENGAGEMENT_STEPS as &[TimelineStep],
426        filters.engagements as &[Engagement],
427        filters.hide_not_started,
428        filters.first_ids as Option<&[Uuid]>,
429        filters.account_linking_since,
430        &user_search.word_patterns,
431        user_search.misspelling_term.as_deref(),
432    )
433    .fetch_all(conn)
434    .await?;
435    Ok(res)
436}
437
438/// A page of the ledger for the admin explorer, cross-course.
439pub async fn get_admin_facing(
440    conn: &mut PgConnection,
441    filters: &AdminCreditRegistrationFilters<'_>,
442    sort: AdminCreditRegistrationSort,
443    limit: i64,
444    offset: i64,
445) -> ModelResult<Vec<AdminCreditRegistration>> {
446    admin_facing_page(conn, filters, sort, limit, offset).await
447}
448
449/// Live rows of one module at one step, with one engagement where the step waits on the student.
450#[derive(Debug, Clone, PartialEq)]
451pub struct StepCount {
452    pub course_module_id: Uuid,
453    pub step: TimelineStep,
454    pub engagement: Option<Engagement>,
455    pub count: i64,
456}
457
458/// Live rows per module, step and engagement: the overview's "Where registrations stand", the
459/// Courses tab's per-module table and the daily snapshot.
460///
461/// Counts exactly what [`get_admin_facing`] lists filtered to the same step, engagement and module,
462/// superseded rows left out: a row waiting for a student number is counted only if it completed
463/// since `account_linking_since`.
464pub async fn count_by_step_and_engagement(
465    conn: &mut PgConnection,
466    account_linking_since: Option<DateTime<Utc>>,
467) -> ModelResult<Vec<StepCount>> {
468    let step_match = StepMatch::all();
469    let res = sqlx::query_as!(
470        StepCount,
471        r#"
472SELECT cr.course_module_id,
473  ts.step AS "step!: TimelineStep",
474  eng.engagement AS "engagement?: Engagement",
475  COUNT(*) AS "count!"
476FROM credit_registrations cr
477  JOIN course_module_completions cmc ON cmc.id = cr.course_module_completion_id
478  JOIN credit_registration_preconditions p ON p.credit_registration_id = cr.id
479  LEFT JOIN credit_registration_enrolment_routes route ON route.course_module_completion_id = cr.course_module_completion_id
480  AND route.deleted_at IS NULL
481  LEFT JOIN credit_registration_enrolment_check_signals sig ON sig.course_module_completion_id = cr.course_module_completion_id
482  AND sig.deleted_at IS NULL
483  -- Computed exactly as in admin_facing_page; see there.
484  JOIN LATERAL (
485    SELECT m.step
486    FROM UNNEST(
487        $1::credit_registration_state [],
488        $2::boolean [],
489        $3::boolean [],
490        $4::boolean [],
491        $5::boolean [],
492        $6::credit_registration_timeline_step []
493      ) AS m(
494        state,
495        completion_eligible,
496        has_verified_student_number,
497        course_code_allowed,
498        enrolment_resolved,
499        step
500      )
501    WHERE m.state = cr.state
502      AND m.completion_eligible = p.completion_eligible
503      AND m.has_verified_student_number = p.has_verified_student_number
504      AND m.course_code_allowed = p.course_code_allowed
505      AND m.enrolment_resolved = (cr.selected_enrolment_id IS NOT NULL)
506  ) ts ON TRUE
507  CROSS JOIN LATERAL (
508    SELECT CASE
509        WHEN ts.step = ANY($7::credit_registration_timeline_step []) THEN CASE
510          WHEN route.enrolment_confirmed_at IS NOT NULL THEN 'pressed'::credit_registration_engagement
511          WHEN sig.last_visited_at IS NOT NULL THEN 'visited'::credit_registration_engagement
512          ELSE 'not_started'::credit_registration_engagement
513        END
514      END AS engagement,
515      ts.step = 'waiting_for_student_number'
516      AND cmc.completion_date < $8::timestamptz AS is_before_linking
517  ) eng
518WHERE cr.deleted_at IS NULL
519  AND cr.superseded_by_id IS NULL
520  AND eng.is_before_linking IS NOT TRUE
521GROUP BY cr.course_module_id,
522  ts.step,
523  eng.engagement
524        "#,
525        &step_match.states as &[CreditRegistrationState],
526        &step_match.completion_eligible,
527        &step_match.has_verified_student_number,
528        &step_match.course_code_allowed,
529        &step_match.enrolment_resolved,
530        &step_match.steps as &[TimelineStep],
531        &TimelineStep::ENGAGEMENT_STEPS as &[TimelineStep],
532        account_linking_since,
533    )
534    .fetch_all(conn)
535    .await?;
536    Ok(res)
537}
538
539/// [`count_by_step_and_engagement`]'s rows summed over modules, in timeline order.
540pub fn sum_step_counts_over_modules<'a>(
541    rows: impl IntoIterator<Item = &'a StepCount>,
542) -> BTreeMap<(TimelineStep, Option<Engagement>), i64> {
543    let mut totals = BTreeMap::new();
544    for row in rows {
545        *totals.entry((row.step, row.engagement)).or_insert(0) += row.count;
546    }
547    totals
548}
549
550/// Live rows in each of the given states, newest activity first within each state, for the
551/// Reconciliation lists. `limit_per_state` caps every state independently, via `ROW_NUMBER`, so one
552/// state with many rows cannot crowd another out of a shared `LIMIT`.
553pub async fn get_live_by_states(
554    conn: &mut PgConnection,
555    states: &[CreditRegistrationState],
556    limit_per_state: i64,
557) -> ModelResult<Vec<CreditRegistration>> {
558    let res = sqlx::query_as!(
559        CreditRegistration,
560        r#"
561SELECT cr.*
562FROM credit_registrations cr
563  JOIN (
564    SELECT id,
565      ROW_NUMBER() OVER (
566        PARTITION BY state
567        ORDER BY state_entered_at DESC
568      ) AS rn
569    FROM credit_registrations
570    WHERE state = ANY($1)
571      AND superseded_by_id IS NULL
572      AND deleted_at IS NULL
573  ) ranked ON ranked.id = cr.id
574WHERE ranked.rn <= $2
575ORDER BY cr.state_entered_at DESC
576        "#,
577        states as &[CreditRegistrationState],
578        limit_per_state,
579    )
580    .fetch_all(conn)
581    .await?;
582    Ok(res)
583}
584
585/// What the student did and when, around one registration's completion, for the admin timeline.
586#[derive(Debug, Clone, PartialEq)]
587pub struct AdminRegistrationJourney {
588    /// When they first picked any language version of the course.
589    pub course_started_at: Option<DateTime<Utc>>,
590    /// When the completion's page signals were first recorded: the first visit, unless a check
591    /// request came first.
592    pub first_visited_at: Option<DateTime<Utc>>,
593    pub last_visited_at: Option<DateTime<Utc>>,
594    pub last_check_requested_at: Option<DateTime<Utc>>,
595    pub check_request_source: Option<EnrolmentCheckSource>,
596    pub enrolment_route: Option<CreditRegistrationEnrolmentRoute>,
597    pub pressed_at: Option<DateTime<Utc>>,
598    /// Sisu's own enrolment time, from the latest enrolment check that found it.
599    pub checked_enrolled_at: Option<DateTime<Utc>>,
600    /// Sisu's own enrolment time as the selected enrolment's lookup answer gave it, unparsed.
601    pub selected_enrolment_date_time: Option<String>,
602}
603
604/// The journey around `credit_registration_id`'s completion. `None` for no such live row.
605pub async fn get_registration_journey(
606    conn: &mut PgConnection,
607    credit_registration_id: Uuid,
608) -> ModelResult<Option<AdminRegistrationJourney>> {
609    let res = sqlx::query_as!(
610        AdminRegistrationJourney,
611        r#"
612SELECT ucs.created_at AS "course_started_at?",
613  CASE
614    WHEN sig.last_visited_at IS NOT NULL THEN LEAST(sig.created_at, sig.last_visited_at)
615  END AS "first_visited_at?",
616  sig.last_visited_at AS "last_visited_at?",
617  sig.last_check_requested_at AS "last_check_requested_at?",
618  sig.check_request_source AS "check_request_source?: EnrolmentCheckSource",
619  route.route AS "enrolment_route?: CreditRegistrationEnrolmentRoute",
620  route.enrolment_confirmed_at AS "pressed_at?",
621  (
622    SELECT o.enrolled_at
623    FROM credit_registration_enrolment_check_outcomes o
624      JOIN credit_registrations attempt ON attempt.id = o.credit_registration_id
625    WHERE attempt.course_module_completion_id = cr.course_module_completion_id
626      AND o.is_enrolment_found
627      AND o.enrolled_at IS NOT NULL
628    ORDER BY o.checked_at DESC
629    LIMIT 1
630  ) AS "checked_enrolled_at?",
631  (
632    SELECT found.value #>> '{}'
633    FROM credit_registration_events e
634      CROSS JOIN LATERAL jsonb_path_query_first(
635        e.details,
636        'lax $.** ? (@.id == $id).enrolmentDateTime',
637        jsonb_build_object('id', cr.selected_enrolment_id)
638      ) AS found(value)
639    WHERE e.credit_registration_id = cr.id
640      AND e.deleted_at IS NULL
641      AND cr.selected_enrolment_id IS NOT NULL
642      AND found.value IS NOT NULL
643    ORDER BY e.created_at DESC
644    LIMIT 1
645  ) AS "selected_enrolment_date_time?"
646FROM credit_registrations cr
647  JOIN courses c ON c.id = cr.course_id
648  LEFT JOIN user_course_settings ucs ON ucs.user_id = cr.user_id
649  AND ucs.course_language_group_id = c.course_language_group_id
650  AND ucs.deleted_at IS NULL
651  LEFT JOIN credit_registration_enrolment_check_signals sig ON sig.course_module_completion_id = cr.course_module_completion_id
652  AND sig.deleted_at IS NULL
653  LEFT JOIN credit_registration_enrolment_routes route ON route.course_module_completion_id = cr.course_module_completion_id
654  AND route.deleted_at IS NULL
655WHERE cr.id = $1
656  AND cr.deleted_at IS NULL
657        "#,
658        credit_registration_id,
659    )
660    .fetch_optional(conn)
661    .await?;
662    Ok(res)
663}
headless_lms_models/credit_registrations/admin_view.rs

headless_lms_models/credit_registrations/
admin_view.rs
