Skip to main content

max / makenotwork

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