headless_lms_models/
users.rs

1use crate::prelude::*;
2use utoipa::ToSchema;
3
4#[derive(Debug, Clone, PartialEq, Serialize, Deserialize, ToSchema)]
5
6pub struct User {
7    pub id: Uuid,
8    pub created_at: DateTime<Utc>,
9    pub updated_at: DateTime<Utc>,
10    pub deleted_at: Option<DateTime<Utc>>,
11    pub upstream_id: Option<i32>,
12    pub email_domain: Option<String>,
13}
14
15/// The domain part of an address, as stored in `users.email_domain`.
16///
17/// Every writer of `user_details.email` must use this, or the derived column drifts silently.
18pub fn email_domain_from_email(email: &str) -> Option<&str> {
19    email.trim().split('@').next_back()
20}
21
22pub async fn insert(
23    conn: &mut PgConnection,
24    pkey_policy: PKeyPolicy<Uuid>,
25    email: &str,
26    first_name: Option<&str>,
27    last_name: Option<&str>,
28) -> ModelResult<Uuid> {
29    let mut tx = conn.begin().await?;
30    let email_domain = email_domain_from_email(email);
31    let res = sqlx::query!(
32        "
33INSERT INTO users (id, email_domain)
34VALUES ($1, $2)
35RETURNING *
36",
37        pkey_policy.into_uuid(),
38        email_domain
39    )
40    .fetch_one(&mut *tx)
41    .await?;
42
43    let _res2 = sqlx::query!(
44        "
45INSERT INTO user_details (user_id, email, first_name, last_name)
46VALUES ($1, $2, $3, $4)
47",
48        res.id,
49        email,
50        first_name,
51        last_name
52    )
53    .execute(&mut *tx)
54    .await?;
55    tx.commit().await?;
56    Ok(res.id)
57}
58
59pub async fn insert_with_upstream_id_and_moocfi_id(
60    conn: &mut PgConnection,
61    email: &str,
62    first_name: Option<&str>,
63    last_name: Option<&str>,
64    upstream_id: i32,
65    moocfi_id: Uuid,
66) -> ModelResult<User> {
67    info!("The user is not in the database yet, inserting");
68    let email_domain = email_domain_from_email(email);
69    let mut tx = conn.begin().await?;
70    let user = sqlx::query_as!(
71        User,
72        r#"
73INSERT INTO
74  users (id, upstream_id, email_domain)
75VALUES ($1, $2, $3)
76RETURNING *;
77          "#,
78        moocfi_id,
79        upstream_id,
80        email_domain
81    )
82    .fetch_one(&mut *tx)
83    .await?;
84
85    let _res2 = sqlx::query!(
86        "
87INSERT INTO user_details (user_id, email, first_name, last_name)
88VALUES ($1, $2, $3, $4)
89",
90        user.id,
91        email,
92        first_name,
93        last_name
94    )
95    .execute(&mut *tx)
96    .await?;
97    tx.commit().await?;
98    Ok(user)
99}
100
101/// Looks up a user by email (case-insensitive) using the `lower(email)` index on `user_details`.
102pub async fn get_by_email(conn: &mut PgConnection, email: &str) -> ModelResult<User> {
103    let user = sqlx::query_as!(
104        User,
105        "
106SELECT users.*
107FROM user_details
108JOIN users ON (user_details.user_id = users.id)
109WHERE lower(user_details.email) = lower($1)
110        ",
111        email
112    )
113    .fetch_one(conn)
114    .await?;
115    Ok(user)
116}
117
118pub async fn get_by_id(conn: &mut PgConnection, id: Uuid) -> ModelResult<User> {
119    let user = sqlx::query_as!(
120        User,
121        "
122SELECT *
123FROM users
124WHERE id = $1
125        ",
126        id
127    )
128    .fetch_one(conn)
129    .await?;
130    Ok(user)
131}
132
133pub async fn find_by_upstream_id(
134    conn: &mut PgConnection,
135    upstream_id: i32,
136) -> ModelResult<Option<User>> {
137    let user = sqlx::query_as!(
138        User,
139        "SELECT * FROM users WHERE upstream_id = $1 AND deleted_at IS NULL",
140        upstream_id
141    )
142    .fetch_optional(conn)
143    .await?;
144    Ok(user)
145}
146
147/// Includes all users who have returned an exercise on a course course instance
148pub async fn get_all_user_ids_with_user_exercise_states_on_course(
149    conn: &mut PgConnection,
150    course_id: Uuid,
151) -> ModelResult<Vec<Uuid>> {
152    let res = sqlx::query!(
153        "
154SELECT DISTINCT user_id
155FROM user_exercise_states
156WHERE course_id = $1
157  AND deleted_at IS NULL
158        ",
159        course_id
160    )
161    .map(|x| x.user_id)
162    .fetch_all(conn)
163    .await?;
164    Ok(res)
165}
166
167pub async fn get_users_by_course_instance_enrollment(
168    conn: &mut PgConnection,
169    course_instance_id: Uuid,
170) -> ModelResult<Vec<User>> {
171    let res = sqlx::query_as!(
172        User,
173        "
174SELECT *
175FROM users
176WHERE id IN (
177    SELECT user_id
178    FROM course_instance_enrollments
179    WHERE course_instance_id = $1
180      AND deleted_at IS NULL
181  )
182",
183        course_instance_id,
184    )
185    .fetch_all(&mut *conn)
186    .await?;
187    Ok(res)
188}
189
190pub async fn get_users_ids_in_db_from_upstream_ids(
191    conn: &mut PgConnection,
192    upstream_ids: &[i32],
193) -> ModelResult<Vec<Uuid>> {
194    let res = sqlx::query!(
195        "
196SELECT *
197FROM users
198WHERE upstream_id IN (
199    SELECT UNNEST($1::integer [])
200  )
201AND deleted_at IS NULL
202",
203        upstream_ids,
204    )
205    .fetch_all(&mut *conn)
206    .await?;
207    Ok(res.iter().map(|x| x.id).collect::<Vec<_>>())
208}
209
210/// Points the account with this upstream id at a new address, keeping `users.email_domain` in step.
211///
212/// The `clear_email_verification` trigger drops proof of the old address as part of the update; the
213/// returned local user id lets the caller mail a fresh link.
214pub async fn update_email_for_user(
215    conn: &mut PgConnection,
216    upstream_id: &i32,
217    new_email: String,
218) -> ModelResult<Uuid> {
219    info!("Updating user (Upstream id: {upstream_id})");
220    let mut tx = conn.begin().await?;
221
222    let user = sqlx::query_as!(
223        User,
224        "SELECT * FROM users WHERE upstream_id = $1 AND deleted_at IS NULL",
225        upstream_id
226    )
227    .fetch_one(&mut *tx)
228    .await?;
229
230    sqlx::query!(
231        "UPDATE user_details SET email = $1 WHERE user_id = $2",
232        new_email,
233        user.id,
234    )
235    .execute(&mut *tx)
236    .await?;
237
238    let email_domain = email_domain_from_email(&new_email);
239    sqlx::query!(
240        "UPDATE users SET email_domain = $1 WHERE id = $2",
241        email_domain,
242        user.id,
243    )
244    .execute(&mut *tx)
245    .await?;
246
247    tx.commit().await?;
248
249    info!("Email change succeeded");
250    Ok(user.id)
251}
252
253pub async fn delete_user(conn: &mut PgConnection, id: Uuid) -> ModelResult<()> {
254    info!("Deleting user {id}");
255    let mut tx = conn.begin().await?;
256    crate::email_deliveries::soft_delete_unsent_retryable_deliveries_for_user(&mut tx, id).await?;
257    sqlx::query!("DELETE FROM user_details WHERE user_id = $1", id,)
258        .execute(&mut *tx)
259        .await?;
260    sqlx::query!("DELETE FROM user_passwords WHERE user_id = $1", id,)
261        .execute(&mut *tx)
262        .await?;
263    sqlx::query!(
264        "UPDATE users set deleted_at = now() WHERE id = $1 AND deleted_at IS NULL",
265        id,
266    )
267    .execute(&mut *tx)
268    .await?;
269    sqlx::query!(
270        "UPDATE roles set deleted_at = now() WHERE user_id = $1 AND deleted_at IS NULL",
271        id,
272    )
273    .execute(&mut *tx)
274    .await?;
275    tx.commit().await?;
276    info!("Deletion succeeded");
277    Ok(())
278}
headless_lms_models/users.rs

headless_lms_models/
users.rs
