headless_lms_models/
credit_registration_daily_snapshots.rs

1//! Daily queue depth per ledger state.
2//!
3//! The ledger holds current state only, so a row that passed through a state in an hour leaves no
4//! depth trace. Aggregates only: anything per-person belongs in the ledger.
5use chrono::NaiveDate;
6use utoipa::ToSchema;
7
8use crate::credit_registrations::CreditRegistrationState;
9use crate::prelude::*;
10
11#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]
12pub struct CreditRegistrationDailySnapshot {
13    pub id: Uuid,
14    pub created_at: DateTime<Utc>,
15    pub updated_at: DateTime<Utc>,
16    pub deleted_at: Option<DateTime<Utc>>,
17    pub snapshot_date: NaiveDate,
18    pub state: CreditRegistrationState,
19    pub count: i32,
20    pub entered_count: i32,
21    pub left_count: i32,
22}
23
24#[derive(Debug, Clone, PartialEq)]
25pub struct DailyStateCounts {
26    pub state: CreditRegistrationState,
27    pub count: i32,
28    pub entered_count: i32,
29    pub left_count: i32,
30}
31
32/// One row per state, whether or not anything is in it: a state missing from a day would read as a
33/// gap in the chart rather than as an empty queue.
34///
35/// `count` is the depth right now, so this has to be called on the day it describes.
36/// `entered_count`/`left_count` come from the transitions inside `[day_start, day_end)`, which the
37/// caller passes explicitly: `snapshot_date` alone would make the day boundary depend on the
38/// database's timezone.
39pub async fn count_states_for_day(
40    conn: &mut PgConnection,
41    day_start: DateTime<Utc>,
42    day_end: DateTime<Utc>,
43) -> ModelResult<Vec<DailyStateCounts>> {
44    let states = CreditRegistrationState::ALL.to_vec();
45    let res = sqlx::query_as!(
46        DailyStateCounts,
47        r#"
48WITH depth AS (
49  SELECT state,
50    COUNT(*)::int AS count
51  FROM credit_registrations
52  WHERE deleted_at IS NULL
53    -- As in `credit_registrations::count_by_state`, which feeds the funnel and the alerts off the
54    -- same states: counting replaced attempts here would make the two disagree on every course
55    -- that regrades.
56    AND superseded_by_id IS NULL
57  GROUP BY state
58),
59-- A transition writes one event carrying both ends, so entering and leaving are the same rows read
60-- from either side. A self-transition is neither.
61flow AS (
62  SELECT from_state,
63    to_state
64  FROM credit_registration_events
65  WHERE deleted_at IS NULL
66    AND created_at >= $2
67    AND created_at < $3
68    AND from_state IS DISTINCT FROM to_state
69)
70SELECT s.state AS "state!: CreditRegistrationState",
71  COALESCE(depth.count, 0) AS "count!",
72  (
73    SELECT COUNT(*)::int
74    FROM flow
75    WHERE flow.to_state = s.state
76  ) AS "entered_count!",
77  (
78    SELECT COUNT(*)::int
79    FROM flow
80    WHERE flow.from_state = s.state
81  ) AS "left_count!"
82FROM UNNEST($1::credit_registration_state []) AS s(state)
83  LEFT JOIN depth ON depth.state = s.state
84        "#,
85        &states as &[CreditRegistrationState],
86        day_start,
87        day_end,
88    )
89    .fetch_all(conn)
90    .await?;
91    Ok(res)
92}
93
94/// Writes one day's counts. Idempotent, so a re-run cannot double-count.
95pub async fn write_snapshot_for_date(
96    conn: &mut PgConnection,
97    snapshot_date: NaiveDate,
98    counts: &[DailyStateCounts],
99) -> ModelResult<()> {
100    for row in counts {
101        sqlx::query!(
102            r#"
103INSERT INTO credit_registration_daily_snapshots (
104    snapshot_date,
105    state,
106    count,
107    entered_count,
108    left_count
109  )
110VALUES ($1, $2, $3, $4, $5) ON CONFLICT (snapshot_date, state, deleted_at) DO
111UPDATE
112SET count = $3,
113  entered_count = $4,
114  left_count = $5
115            "#,
116            snapshot_date,
117            row.state as CreditRegistrationState,
118            row.count,
119            row.entered_count,
120            row.left_count,
121        )
122        .execute(&mut *conn)
123        .await?;
124    }
125    Ok(())
126}
127
128pub async fn get_between(
129    conn: &mut PgConnection,
130    from: NaiveDate,
131    to: NaiveDate,
132) -> ModelResult<Vec<CreditRegistrationDailySnapshot>> {
133    let res = sqlx::query_as!(
134        CreditRegistrationDailySnapshot,
135        r#"
136SELECT *
137FROM credit_registration_daily_snapshots
138WHERE snapshot_date BETWEEN $1 AND $2
139  AND deleted_at IS NULL
140ORDER BY snapshot_date,
141  state
142        "#,
143        from,
144        to,
145    )
146    .fetch_all(conn)
147    .await?;
148    Ok(res)
149}
150
151pub async fn get_series_for_state(
152    conn: &mut PgConnection,
153    state: CreditRegistrationState,
154    from: NaiveDate,
155    to: NaiveDate,
156) -> ModelResult<Vec<CreditRegistrationDailySnapshot>> {
157    let res = sqlx::query_as!(
158        CreditRegistrationDailySnapshot,
159        r#"
160SELECT *
161FROM credit_registration_daily_snapshots
162WHERE state = $1
163  AND snapshot_date BETWEEN $2 AND $3
164  AND deleted_at IS NULL
165ORDER BY snapshot_date
166        "#,
167        state as CreditRegistrationState,
168        from,
169        to,
170    )
171    .fetch_all(conn)
172    .await?;
173    Ok(res)
174}
headless_lms_models/credit_registration_daily_snapshots.rs

headless_lms_models/
credit_registration_daily_snapshots.rs
