headless_lms_models/
study_registry_student_number_conflicts.rs

1//! Student numbers a registrar reported that could not become a link because another stood in the way.
2
3use crate::prelude::*;
4use crate::verified_student_numbers::StudentNumberVerificationMethod;
5
6/// A conflict that still holds: the account does not hold the reported number.
7#[derive(Debug, Clone)]
8pub struct UnresolvedStudyRegistryConflict {
9    pub id: Uuid,
10    pub created_at: DateTime<Utc>,
11    pub user_id: Uuid,
12    pub user_email: Option<String>,
13    pub first_name: Option<String>,
14    pub last_name: Option<String>,
15    pub reported_student_number: DbSecret,
16    pub course_id: Uuid,
17    pub course_name: String,
18    pub conflicting_link_user_id: Uuid,
19    pub conflicting_link_user_email: Option<String>,
20    pub conflicting_link_student_number: DbSecret,
21    pub conflicting_link_verified_via: StudentNumberVerificationMethod,
22}
23
24/// How many conflicts still hold.
25pub async fn count_unresolved(conn: &mut PgConnection) -> ModelResult<i64> {
26    let count = sqlx::query_scalar!(
27        r#"
28SELECT COUNT(*) AS "count!"
29FROM study_registry_student_number_conflicts c
30WHERE c.deleted_at IS NULL
31  AND c.dismissed_at IS NULL
32  AND NOT EXISTS (
33    SELECT 1
34    FROM verified_student_numbers vsn
35    WHERE vsn.user_id = c.user_id
36      AND vsn.student_number = c.student_number
37      AND vsn.deleted_at IS NULL
38  )
39        "#
40    )
41    .fetch_one(conn)
42    .await?;
43    Ok(count)
44}
45
46/// Conflicts that still hold, newest first.
47pub async fn get_unresolved(
48    conn: &mut PgConnection,
49    limit: i64,
50) -> ModelResult<Vec<UnresolvedStudyRegistryConflict>> {
51    let rows = sqlx::query_as!(
52        UnresolvedStudyRegistryConflict,
53        r#"
54SELECT c.id,
55  c.created_at,
56  c.user_id,
57  ud.email AS "user_email?",
58  ud.first_name AS "first_name?",
59  ud.last_name AS "last_name?",
60  c.student_number AS reported_student_number,
61  r.course_id,
62  co.name AS course_name,
63  vsn.user_id AS conflicting_link_user_id,
64  holder.email AS "conflicting_link_user_email?",
65  vsn.student_number AS conflicting_link_student_number,
66  vsn.verified_via AS conflicting_link_verified_via
67FROM study_registry_student_number_conflicts c
68  JOIN course_module_completion_registered_to_study_registries r ON r.id = c.registered_completion_id
69  JOIN courses co ON co.id = r.course_id
70  JOIN verified_student_numbers vsn ON vsn.id = c.conflicting_verified_student_number_id
71  LEFT JOIN user_details ud ON ud.user_id = c.user_id
72  LEFT JOIN user_details holder ON holder.user_id = vsn.user_id
73WHERE c.deleted_at IS NULL
74  AND c.dismissed_at IS NULL
75  AND NOT EXISTS (
76    SELECT 1
77    FROM verified_student_numbers held
78    WHERE held.user_id = c.user_id
79      AND held.student_number = c.student_number
80      AND held.deleted_at IS NULL
81  )
82ORDER BY c.created_at DESC,
83  c.id
84LIMIT $1
85        "#,
86        limit,
87    )
88    .fetch_all(conn)
89    .await?;
90    Ok(rows)
91}
92
93/// Records that a registrar-reported link could not take its Sisu person because `blocking_link_id`,
94/// another account's live link, already holds that person. Nothing is recorded unless `link_id`'s
95/// number is still the latest one a registrar reported for its account, or once an admin has
96/// dismissed the same conflict.
97pub async fn record_person_conflict(
98    conn: &mut PgConnection,
99    link_id: Uuid,
100    blocking_link_id: Uuid,
101) -> ModelResult<()> {
102    sqlx::query!(
103        r#"
104INSERT INTO study_registry_student_number_conflicts (
105    user_id,
106    student_number,
107    registered_completion_id,
108    conflicting_verified_student_number_id
109  )
110SELECT reported.user_id,
111  reported.student_number,
112  reported.registered_completion_id,
113  $2
114FROM verified_student_numbers link
115  JOIN study_registry_reported_student_numbers reported ON reported.user_id = link.user_id
116  AND reported.student_number = link.student_number
117WHERE link.id = $1
118  AND NOT EXISTS (
119    SELECT 1
120    FROM study_registry_student_number_conflicts dismissed
121    WHERE dismissed.user_id = reported.user_id
122      AND dismissed.student_number = reported.student_number
123      AND dismissed.dismissed_at IS NOT NULL
124      AND dismissed.deleted_at IS NULL
125  )
126ON CONFLICT DO NOTHING
127        "#,
128        link_id,
129        blocking_link_id,
130    )
131    .execute(conn)
132    .await?;
133    Ok(())
134}
135
136/// Takes a conflict off the list for good: the same account and number are not recorded again.
137/// Returns false when it was already dismissed or is gone.
138pub async fn dismiss(conn: &mut PgConnection, id: Uuid) -> ModelResult<bool> {
139    let res = sqlx::query!(
140        r#"
141UPDATE study_registry_student_number_conflicts
142SET dismissed_at = now()
143WHERE id = $1
144  AND deleted_at IS NULL
145  AND dismissed_at IS NULL
146        "#,
147        id,
148    )
149    .execute(conn)
150    .await?;
151    Ok(res.rows_affected() > 0)
152}
headless_lms_models/study_registry_student_number_conflicts.rs

headless_lms_models/
study_registry_student_number_conflicts.rs
