Skip to main content

max / makenotwork

14.7 KB · 471 lines History Blame Raw
1 //! Analytics queries: time-bucketed revenue and period-over-period comparisons.
2
3 use chrono::{DateTime, Datelike, Utc};
4 use sqlx::PgPool;
5 use uuid::Uuid;
6
7 use super::{Cents, FollowTargetType, ItemId, ProjectId, UserId};
8 use crate::error::Result;
9
10 /// Time range for analytics queries.
11 pub enum TimeRange {
12 Days7,
13 Days30,
14 Days90,
15 All,
16 }
17
18 impl std::str::FromStr for TimeRange {
19 type Err = ();
20 fn from_str(s: &str) -> std::result::Result<Self, ()> {
21 match s {
22 "7d" => Ok(Self::Days7),
23 "30d" => Ok(Self::Days30),
24 "90d" => Ok(Self::Days90),
25 "all" => Ok(Self::All),
26 _ => Err(()),
27 }
28 }
29 }
30
31 impl std::fmt::Display for TimeRange {
32 fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
33 match self {
34 Self::Days7 => f.write_str("7d"),
35 Self::Days30 => f.write_str("30d"),
36 Self::Days90 => f.write_str("90d"),
37 Self::All => f.write_str("all"),
38 }
39 }
40 }
41
42 impl TimeRange {
43 /// SQL interval string for the current period, or `None` for All.
44 ///
45 /// INVARIANT: These values are interpolated into SQL via format!. They MUST be
46 /// compile-time constants with no user input. The exhaustive match ensures
47 /// new variants require explicit SQL strings.
48 pub(crate) fn interval_sql(&self) -> Option<&'static str> {
49 match self {
50 Self::Days7 => Some("7 days"),
51 Self::Days30 => Some("30 days"),
52 Self::Days90 => Some("90 days"),
53 Self::All => None,
54 }
55 }
56
57 /// SQL date_trunc bucket size: day for short ranges, week for 90d, month for All.
58 ///
59 /// SAFETY: Interpolated into SQL via format!. Must be compile-time constants.
60 pub(crate) fn bucket_sql(&self) -> &'static str {
61 match self {
62 Self::Days7 | Self::Days30 => "day",
63 Self::Days90 => "week",
64 Self::All => "month",
65 }
66 }
67 }
68
69 /// A single time bucket in a revenue timeseries.
70 pub(crate) struct TimeBucket {
71 pub label: String,
72 pub revenue_cents: Cents,
73 pub sales_count: i64,
74 }
75
76 /// Period-over-period comparison data for stat cards.
77 pub(crate) struct PeriodComparison {
78 pub current_revenue_cents: Cents,
79 pub previous_revenue_cents: Cents,
80 pub current_sales: i64,
81 pub previous_sales: i64,
82 pub current_followers: i64,
83 pub previous_followers: i64,
84 }
85
86 impl PeriodComparison {
87 /// Percentage change in revenue, e.g. `("+42%", true)`. None if no previous data.
88 #[tracing::instrument(skip_all)]
89 pub(crate) fn revenue_change(&self) -> Option<(String, bool)> {
90 pct_change(
91 self.current_revenue_cents.as_i64(),
92 self.previous_revenue_cents.as_i64(),
93 )
94 }
95
96 /// Percentage change in sales count.
97 #[tracing::instrument(skip_all)]
98 pub(crate) fn sales_change(&self) -> Option<(String, bool)> {
99 pct_change(self.current_sales, self.previous_sales)
100 }
101
102 /// Percentage change in follower count.
103 #[tracing::instrument(skip_all)]
104 pub(crate) fn followers_change(&self) -> Option<(String, bool)> {
105 pct_change(self.current_followers, self.previous_followers)
106 }
107 }
108
109 /// Compute percentage change text. Returns None when previous is zero.
110 pub(crate) fn pct_change(current: i64, previous: i64) -> Option<(String, bool)> {
111 if previous == 0 {
112 return None;
113 }
114 let pct = ((current - previous) as f64 / previous as f64 * 100.0).round() as i64;
115 let is_positive = pct >= 0;
116 let text = if is_positive {
117 format!("+{pct}%")
118 } else {
119 format!("{pct}%")
120 };
121 Some((text, is_positive))
122 }
123
124 /// Format a bucket timestamp into a human-readable label.
125 pub(crate) fn format_bucket_label(dt: &DateTime<Utc>, range: &TimeRange) -> String {
126 match range {
127 TimeRange::Days7 | TimeRange::Days30 => dt.format("%b %-d").to_string(),
128 TimeRange::Days90 => format!("Week {}", dt.iso_week().week()),
129 TimeRange::All => dt.format("%b %Y").to_string(),
130 }
131 }
132
133 // ── Scope-aware query building ──
134
135 /// Scope determines the WHERE clause and bind parameters for transaction queries.
136 enum Scope {
137 Item(ItemId),
138 Project(ProjectId),
139 User,
140 }
141
142 impl Scope {
143 fn from_ids(item_id: Option<ItemId>, project_id: Option<ProjectId>) -> Self {
144 match (item_id, project_id) {
145 (Some(iid), _) => Scope::Item(iid),
146 (None, Some(pid)) => Scope::Project(pid),
147 (None, None) => Scope::User,
148 }
149 }
150
151 /// WHERE clause fragment (assumes seller_id is $1).
152 fn where_clause(&self) -> &'static str {
153 match self {
154 Scope::Item(_) => "seller_id = $1 AND item_id = $2 AND status = 'completed'",
155 Scope::Project(_) => {
156 "t.seller_id = $1 AND t.item_id IN (SELECT id FROM items WHERE project_id = $2) AND t.status = 'completed'"
157 }
158 Scope::User => "seller_id = $1 AND status = 'completed'",
159 }
160 }
161
162 /// Table alias prefix: "t." for project scope (uses subquery), empty for others.
163 fn table_prefix(&self) -> &'static str {
164 match self {
165 Scope::Project(_) => "t.",
166 _ => "",
167 }
168 }
169
170 /// Table alias: "transactions t" for project scope, "transactions" for others.
171 fn table_name(&self) -> &'static str {
172 match self {
173 Scope::Project(_) => "transactions t",
174 _ => "transactions",
175 }
176 }
177
178 /// Bind the scope-specific parameter ($2) if applicable.
179 fn bind_scope<'q, O>(
180 &self,
181 query: sqlx::query::QueryAs<'q, sqlx::Postgres, O, sqlx::postgres::PgArguments>,
182 ) -> sqlx::query::QueryAs<'q, sqlx::Postgres, O, sqlx::postgres::PgArguments> {
183 match self {
184 Scope::Item(iid) => query.bind(*iid),
185 Scope::Project(pid) => query.bind(*pid),
186 Scope::User => query,
187 }
188 }
189 }
190
191 /// Fetch time-bucketed revenue data for a seller, optionally filtered by project or item.
192 #[tracing::instrument(skip_all)]
193 pub(crate) async fn get_revenue_timeseries(
194 pool: &PgPool,
195 seller_id: UserId,
196 project_id: Option<ProjectId>,
197 item_id: Option<ItemId>,
198 range: &TimeRange,
199 ) -> Result<Vec<TimeBucket>> {
200 let bucket = range.bucket_sql();
201 let scope = Scope::from_ids(item_id, project_id);
202 let prefix = scope.table_prefix();
203 let table = scope.table_name();
204 let where_clause = scope.where_clause();
205
206 let time_filter = match range.interval_sql() {
207 Some(interval) => format!(" AND {prefix}completed_at >= NOW() - INTERVAL '{interval}'"),
208 None => String::new(),
209 };
210
211 let sql = format!(
212 r"
213 SELECT
214 date_trunc('{bucket}', {prefix}completed_at) AS bucket,
215 COALESCE(SUM({prefix}amount_cents), 0)::BIGINT,
216 COUNT(*)
217 FROM {table}
218 WHERE {where_clause}{time_filter}
219 GROUP BY bucket
220 ORDER BY bucket
221 LIMIT 500
222 "
223 );
224
225 let q = sqlx::query_as::<_, (DateTime<Utc>, i64, i64)>(&sql).bind(seller_id);
226 let rows = scope.bind_scope(q).fetch_all(pool).await?;
227
228 let buckets = rows
229 .into_iter()
230 .map(|(dt, revenue, count)| TimeBucket {
231 label: format_bucket_label(&dt, range),
232 revenue_cents: Cents::new(revenue),
233 sales_count: count,
234 })
235 .collect();
236
237 Ok(buckets)
238 }
239
240 /// Fetch period-over-period comparison data for stat cards.
241 ///
242 /// Compares the current period against the previous period of the same length.
243 /// For `TimeRange::All`, previous values are zero (no comparison possible).
244 #[tracing::instrument(skip_all)]
245 pub(crate) async fn get_period_comparison(
246 pool: &PgPool,
247 seller_id: UserId,
248 project_id: Option<ProjectId>,
249 item_id: Option<ItemId>,
250 range: &TimeRange,
251 ) -> Result<PeriodComparison> {
252 let (current_revenue, prev_revenue, current_sales, prev_sales) =
253 get_transaction_comparison(pool, seller_id, project_id, item_id, range).await?;
254
255 let (current_followers, prev_followers) =
256 get_follower_comparison(pool, seller_id, project_id, item_id, range).await?;
257
258 Ok(PeriodComparison {
259 current_revenue_cents: Cents::new(current_revenue),
260 previous_revenue_cents: Cents::new(prev_revenue),
261 current_sales,
262 previous_sales: prev_sales,
263 current_followers,
264 previous_followers: prev_followers,
265 })
266 }
267
268 /// Transaction revenue/sales comparison using FILTER (WHERE ...) conditional aggregation.
269 async fn get_transaction_comparison(
270 pool: &PgPool,
271 seller_id: UserId,
272 project_id: Option<ProjectId>,
273 item_id: Option<ItemId>,
274 range: &TimeRange,
275 ) -> Result<(i64, i64, i64, i64)> {
276 let scope = Scope::from_ids(item_id, project_id);
277 let prefix = scope.table_prefix();
278 let table = scope.table_name();
279 let where_clause = scope.where_clause();
280
281 let Some(interval) = range.interval_sql() else {
282 // All time: just sum everything, no previous period
283 let sql = format!(
284 r"
285 SELECT
286 COALESCE(SUM({prefix}amount_cents), 0)::BIGINT,
287 COUNT(*)
288 FROM {table}
289 WHERE {where_clause}
290 "
291 );
292 let q = sqlx::query_as::<_, (i64, i64)>(&sql).bind(seller_id);
293 let row = scope.bind_scope(q).fetch_one(pool).await?;
294 return Ok((row.0, 0, row.1, 0));
295 };
296
297 // Current vs previous period using FILTER
298 let sql = format!(
299 r"
300 SELECT
301 COALESCE(SUM({prefix}amount_cents) FILTER (WHERE {prefix}completed_at >= NOW() - INTERVAL '{interval}'), 0)::BIGINT,
302 COUNT(*) FILTER (WHERE {prefix}completed_at >= NOW() - INTERVAL '{interval}'),
303 COALESCE(SUM({prefix}amount_cents) FILTER (WHERE {prefix}completed_at < NOW() - INTERVAL '{interval}'), 0)::BIGINT,
304 COUNT(*) FILTER (WHERE {prefix}completed_at < NOW() - INTERVAL '{interval}')
305 FROM {table}
306 WHERE {where_clause}
307 AND {prefix}completed_at >= NOW() - INTERVAL '{interval}' * 2
308 "
309 );
310
311 let q = sqlx::query_as::<_, (i64, i64, i64, i64)>(&sql).bind(seller_id);
312 let row = scope.bind_scope(q).fetch_one(pool).await?;
313
314 Ok((row.0, row.2, row.1, row.3))
315 }
316
317 /// Follower delta comparison. Users and projects have followers; items do not.
318 async fn get_follower_comparison(
319 pool: &PgPool,
320 seller_id: UserId,
321 project_id: Option<ProjectId>,
322 item_id: Option<ItemId>,
323 range: &TimeRange,
324 ) -> Result<(i64, i64)> {
325 // Items don't have followers
326 if item_id.is_some() {
327 return Ok((0, 0));
328 }
329
330 let (target_type, target_id): (FollowTargetType, Uuid) = match project_id {
331 Some(pid) => (FollowTargetType::Project, pid.into()),
332 None => (FollowTargetType::User, seller_id.into()),
333 };
334
335 let Some(interval) = range.interval_sql() else {
336 // All time: just total count, no previous
337 let row: (i64,) = sqlx::query_as(
338 "SELECT COUNT(*) FROM follows WHERE target_type = $1 AND target_id = $2",
339 )
340 .bind(target_type)
341 .bind(target_id)
342 .fetch_one(pool)
343 .await?;
344 return Ok((row.0, 0));
345 };
346
347 let row: (i64, i64) = sqlx::query_as(&format!(
348 r"
349 SELECT
350 COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '{interval}'),
351 COUNT(*) FILTER (WHERE created_at < NOW() - INTERVAL '{interval}')
352 FROM follows
353 WHERE target_type = $1
354 AND target_id = $2
355 AND created_at >= NOW() - INTERVAL '{interval}' * 2
356 "
357 ))
358 .bind(target_type)
359 .bind(target_id)
360 .fetch_one(pool)
361 .await?;
362
363 Ok((row.0, row.1))
364 }
365
366 #[cfg(test)]
367 mod tests {
368 use super::*;
369
370 #[test]
371 fn time_range_from_str() {
372 assert!(matches!("7d".parse::<TimeRange>(), Ok(TimeRange::Days7)));
373 assert!(matches!("30d".parse::<TimeRange>(), Ok(TimeRange::Days30)));
374 assert!(matches!("90d".parse::<TimeRange>(), Ok(TimeRange::Days90)));
375 assert!(matches!("all".parse::<TimeRange>(), Ok(TimeRange::All)));
376 assert!("bad".parse::<TimeRange>().is_err());
377 }
378
379 #[test]
380 fn time_range_display_roundtrip() {
381 for s in ["7d", "30d", "90d", "all"] {
382 let range: TimeRange = s.parse().unwrap();
383 assert_eq!(range.to_string(), s);
384 }
385 }
386
387 #[test]
388 fn time_range_interval_sql() {
389 assert_eq!(TimeRange::Days7.interval_sql(), Some("7 days"));
390 assert_eq!(TimeRange::Days30.interval_sql(), Some("30 days"));
391 assert_eq!(TimeRange::Days90.interval_sql(), Some("90 days"));
392 assert_eq!(TimeRange::All.interval_sql(), None);
393 }
394
395 #[test]
396 fn time_range_bucket_sql() {
397 assert_eq!(TimeRange::Days7.bucket_sql(), "day");
398 assert_eq!(TimeRange::Days30.bucket_sql(), "day");
399 assert_eq!(TimeRange::Days90.bucket_sql(), "week");
400 assert_eq!(TimeRange::All.bucket_sql(), "month");
401 }
402
403 #[test]
404 fn pct_change_positive() {
405 let (text, positive) = pct_change(142, 100).unwrap();
406 assert_eq!(text, "+42%");
407 assert!(positive);
408 }
409
410 #[test]
411 fn pct_change_negative() {
412 let (text, positive) = pct_change(50, 100).unwrap();
413 assert_eq!(text, "-50%");
414 assert!(!positive);
415 }
416
417 #[test]
418 fn pct_change_zero_previous() {
419 assert!(pct_change(100, 0).is_none());
420 }
421
422 #[test]
423 fn pct_change_no_change() {
424 let (text, positive) = pct_change(100, 100).unwrap();
425 assert_eq!(text, "+0%");
426 assert!(positive);
427 }
428
429 #[test]
430 fn format_label_day() {
431 let dt = "2026-03-01T00:00:00Z".parse::<DateTime<Utc>>().unwrap();
432 assert_eq!(format_bucket_label(&dt, &TimeRange::Days7), "Mar 1");
433 assert_eq!(format_bucket_label(&dt, &TimeRange::Days30), "Mar 1");
434 }
435
436 #[test]
437 fn format_label_week() {
438 let dt = "2026-03-01T00:00:00Z".parse::<DateTime<Utc>>().unwrap();
439 let label = format_bucket_label(&dt, &TimeRange::Days90);
440 assert!(label.starts_with("Week "));
441 }
442
443 #[test]
444 fn format_label_month() {
445 let dt = "2026-01-01T00:00:00Z".parse::<DateTime<Utc>>().unwrap();
446 assert_eq!(format_bucket_label(&dt, &TimeRange::All), "Jan 2026");
447 }
448
449 #[test]
450 fn period_comparison_helpers() {
451 let pc = PeriodComparison {
452 current_revenue_cents: Cents::new(200),
453 previous_revenue_cents: Cents::new(100),
454 current_sales: 10,
455 previous_sales: 20,
456 current_followers: 50,
457 previous_followers: 0,
458 };
459
460 let (rev_text, rev_pos) = pc.revenue_change().unwrap();
461 assert_eq!(rev_text, "+100%");
462 assert!(rev_pos);
463
464 let (sales_text, sales_pos) = pc.sales_change().unwrap();
465 assert_eq!(sales_text, "-50%");
466 assert!(!sales_pos);
467
468 assert!(pc.followers_change().is_none());
469 }
470 }
471