Skip to main content

max / makenotwork

12.5 KB · 403 lines History Blame Raw
1 //! Analytics query verification: insert transactions + follows via SQL,
2 //! then run the same aggregate queries used by db/analytics.rs to confirm
3 //! they produce correct results against real PostgreSQL.
4
5 use crate::harness::TestHarness;
6 use makenotwork::db::{ItemId, ProjectId, UserId};
7 use sqlx::PgPool;
8 use std::sync::atomic::{AtomicU32, Ordering};
9
10 /// Monotonic counter for unique buyer usernames across all tests.
11 static BUYER_COUNTER: AtomicU32 = AtomicU32::new(0);
12
13 /// Returns an ISO 8601 timestamp for `days` days ago at the given `hour` (UTC).
14 /// Uses date-only arithmetic so two calls with the same `days` but different
15 /// `hour` values always land on the same calendar day.
16 fn days_ago_at(days: u32, hour: u32) -> String {
17 let date = (chrono::Utc::now() - chrono::Duration::days(days as i64)).date_naive();
18 format!("{date}T{hour:02}:00:00Z")
19 }
20
21 /// Create a unique buyer user via direct SQL. Avoids the partial unique index
22 /// on transactions(buyer_id, item_id) WHERE status = 'completed' by giving
23 /// each transaction its own buyer.
24 async fn create_buyer(pool: &PgPool) -> UserId {
25 let n = BUYER_COUNTER.fetch_add(1, Ordering::Relaxed);
26 let id = UserId::new();
27 sqlx::query(
28 "INSERT INTO users (id, username, email, password_hash) VALUES ($1, $2, $3, 'not-a-real-hash')",
29 )
30 .bind(id)
31 .bind(format!("abuyer{n}"))
32 .bind(format!("abuyer{n}@test.com"))
33 .execute(pool)
34 .await
35 .expect("create buyer");
36 id
37 }
38
39 /// Insert a completed transaction with a specific completed_at timestamp.
40 async fn insert_transaction(
41 pool: &PgPool,
42 seller_id: UserId,
43 item_id: ItemId,
44 amount_cents: i32,
45 completed_at: &str,
46 ) {
47 let buyer_id = create_buyer(pool).await;
48 sqlx::query(
49 r"
50 INSERT INTO transactions
51 (buyer_id, seller_id, item_id, amount_cents, platform_fee_cents,
52 stripe_checkout_session_id, status, completed_at, item_title, seller_username, share_contact)
53 VALUES ($1, $2, $3, $4, 0, $5, 'completed', $6::timestamptz, 'Test', 'seller', false)
54 ",
55 )
56 .bind(buyer_id)
57 .bind(seller_id)
58 .bind(item_id)
59 .bind(amount_cents)
60 .bind(format!("test-{buyer_id}-{completed_at}"))
61 .bind(completed_at)
62 .execute(pool)
63 .await
64 .expect("insert transaction");
65 }
66
67 /// Insert a follow with a specific created_at timestamp.
68 async fn insert_follow(
69 pool: &PgPool,
70 follower_id: UserId,
71 target_type: &str,
72 target_id: uuid::Uuid,
73 created_at: &str,
74 ) {
75 sqlx::query(
76 r"
77 INSERT INTO follows (follower_id, target_type, target_id, created_at)
78 VALUES ($1, $2, $3, $4::timestamptz)
79 ",
80 )
81 .bind(follower_id)
82 .bind(target_type)
83 .bind(target_id)
84 .bind(created_at)
85 .execute(pool)
86 .await
87 .expect("insert follow");
88 }
89
90 /// Create a seller user, project, and item via direct SQL. Returns (seller_id, project_id, item_id).
91 async fn setup_seller_with_item(h: &mut TestHarness, suffix: &str) -> (UserId, ProjectId, ItemId) {
92 let seller_id = h
93 .signup(
94 &format!("aseller{suffix}"),
95 &format!("aseller{suffix}@test.com"),
96 "password123",
97 )
98 .await;
99 h.grant_creator(seller_id).await;
100
101 let project_id = ProjectId::new();
102 let item_id = ItemId::new();
103
104 sqlx::query(
105 "INSERT INTO projects (id, user_id, slug, title) VALUES ($1, $2, $3, 'Test Project')",
106 )
107 .bind(project_id)
108 .bind(seller_id)
109 .bind(format!("proj-{suffix}"))
110 .execute(&h.db)
111 .await
112 .unwrap();
113
114 sqlx::query("INSERT INTO items (id, project_id, title, price_cents, item_type, slug) VALUES ($1, $2, 'Test Item', 1000, 'digital', 'test-item-' || $3)")
115 .bind(item_id)
116 .bind(project_id)
117 .bind(suffix)
118 .execute(&h.db)
119 .await
120 .unwrap();
121
122 (seller_id, project_id, item_id)
123 }
124
125 #[tokio::test]
126 async fn revenue_timeseries_buckets_by_day() {
127 let mut h = TestHarness::new().await;
128 let (seller_id, _, item_id) = setup_seller_with_item(&mut h, "ts1").await;
129
130 // Insert 3 transactions across 2 days (all within 30-day window)
131 let day_a = days_ago_at(2, 12);
132 let day_a2 = days_ago_at(2, 14);
133 let day_b = days_ago_at(3, 10);
134 insert_transaction(&h.db, seller_id, item_id, 1000, &day_a).await;
135 insert_transaction(&h.db, seller_id, item_id, 2000, &day_a2).await;
136 insert_transaction(&h.db, seller_id, item_id, 500, &day_b).await;
137
138 // Timeseries bucketed by day, user scope, 30d range
139 let rows: Vec<(chrono::DateTime<chrono::Utc>, i64, i64)> = sqlx::query_as(
140 r"
141 SELECT
142 date_trunc('day', completed_at) AS bucket,
143 COALESCE(SUM(amount_cents), 0),
144 COUNT(*)
145 FROM transactions
146 WHERE seller_id = $1
147 AND status = 'completed'
148 AND completed_at >= NOW() - INTERVAL '30 days'
149 GROUP BY bucket
150 ORDER BY bucket
151 LIMIT 500
152 ",
153 )
154 .bind(seller_id)
155 .fetch_all(&h.db)
156 .await
157 .unwrap();
158
159 assert_eq!(
160 rows.len(),
161 2,
162 "Expected 2 daily buckets, got {}",
163 rows.len()
164 );
165
166 // Earlier day: 1 sale, 500 cents
167 assert_eq!(rows[0].1, 500);
168 assert_eq!(rows[0].2, 1);
169
170 // Later day: 2 sales, 3000 cents
171 assert_eq!(rows[1].1, 3000);
172 assert_eq!(rows[1].2, 2);
173 }
174
175 #[tokio::test]
176 async fn revenue_timeseries_item_and_project_scope() {
177 let mut h = TestHarness::new().await;
178
179 let seller_id = h
180 .signup("aseller_scope", "aseller_scope@test.com", "password123")
181 .await;
182 h.grant_creator(seller_id).await;
183
184 let project_id = ProjectId::new();
185 let item_a = ItemId::new();
186 let item_b = ItemId::new();
187
188 sqlx::query("INSERT INTO projects (id, user_id, slug, title) VALUES ($1, $2, 'scope-test', 'Scope Test')")
189 .bind(project_id)
190 .bind(seller_id)
191 .execute(&h.db)
192 .await
193 .unwrap();
194
195 sqlx::query("INSERT INTO items (id, project_id, title, price_cents, item_type, slug) VALUES ($1, $2, 'Item A', 1000, 'digital', 'item-a')")
196 .bind(item_a)
197 .bind(project_id)
198 .execute(&h.db)
199 .await
200 .unwrap();
201
202 sqlx::query("INSERT INTO items (id, project_id, title, price_cents, item_type, slug) VALUES ($1, $2, 'Item B', 2000, 'digital', 'item-b')")
203 .bind(item_b)
204 .bind(project_id)
205 .execute(&h.db)
206 .await
207 .unwrap();
208
209 // Transactions: item_a on day_b and day_a, item_b on day_a only
210 let day_a = days_ago_at(2, 12);
211 let day_a2 = days_ago_at(2, 13);
212 let day_b = days_ago_at(3, 12);
213 insert_transaction(&h.db, seller_id, item_a, 1000, &day_b).await;
214 insert_transaction(&h.db, seller_id, item_a, 1000, &day_a).await;
215 insert_transaction(&h.db, seller_id, item_b, 2000, &day_a2).await;
216
217 // Item scope: only item_a
218 let rows: Vec<(chrono::DateTime<chrono::Utc>, i64, i64)> = sqlx::query_as(
219 r"
220 SELECT
221 date_trunc('day', completed_at) AS bucket,
222 COALESCE(SUM(amount_cents), 0),
223 COUNT(*)
224 FROM transactions
225 WHERE seller_id = $1
226 AND item_id = $2
227 AND status = 'completed'
228 AND completed_at >= NOW() - INTERVAL '30 days'
229 GROUP BY bucket
230 ORDER BY bucket
231 LIMIT 500
232 ",
233 )
234 .bind(seller_id)
235 .bind(item_a)
236 .fetch_all(&h.db)
237 .await
238 .unwrap();
239
240 assert_eq!(rows.len(), 2, "Item A should have 2 daily buckets");
241 assert_eq!(rows[0].1, 1000);
242 assert_eq!(rows[1].1, 1000);
243
244 // Project scope: both items
245 let rows: Vec<(chrono::DateTime<chrono::Utc>, i64, i64)> = sqlx::query_as(
246 r"
247 SELECT
248 date_trunc('day', t.completed_at) AS bucket,
249 COALESCE(SUM(t.amount_cents), 0),
250 COUNT(*)
251 FROM transactions t
252 WHERE t.seller_id = $1
253 AND t.item_id IN (SELECT id FROM items WHERE project_id = $2)
254 AND t.status = 'completed'
255 AND t.completed_at >= NOW() - INTERVAL '30 days'
256 GROUP BY bucket
257 ORDER BY bucket
258 LIMIT 500
259 ",
260 )
261 .bind(seller_id)
262 .bind(project_id)
263 .fetch_all(&h.db)
264 .await
265 .unwrap();
266
267 assert_eq!(rows.len(), 2, "Project scope should have 2 daily buckets");
268 assert_eq!(rows[0].1, 1000, "Earlier day: only item_a");
269 assert_eq!(rows[1].1, 3000, "Later day: item_a + item_b");
270 assert_eq!(rows[1].2, 2, "Later day: 2 sales total");
271 }
272
273 #[tokio::test]
274 async fn period_comparison_current_vs_previous() {
275 let mut h = TestHarness::new().await;
276 let (seller_id, _, item_id) = setup_seller_with_item(&mut h, "cmp1").await;
277
278 // Current period (within last 7 days): 2 sales, 3000 cents
279 insert_transaction(&h.db, seller_id, item_id, 1000, &days_ago_at(2, 12)).await;
280 insert_transaction(&h.db, seller_id, item_id, 2000, &days_ago_at(3, 12)).await;
281
282 // Previous period (8-14 days ago): 1 sale, 500 cents
283 insert_transaction(&h.db, seller_id, item_id, 500, &days_ago_at(10, 12)).await;
284
285 // Period comparison with FILTER, user scope, 7d
286 let row: (i64, i64, i64, i64) = sqlx::query_as(
287 r"
288 SELECT
289 COALESCE(SUM(amount_cents) FILTER (WHERE completed_at >= NOW() - INTERVAL '7 days'), 0),
290 COUNT(*) FILTER (WHERE completed_at >= NOW() - INTERVAL '7 days'),
291 COALESCE(SUM(amount_cents) FILTER (WHERE completed_at < NOW() - INTERVAL '7 days'), 0),
292 COUNT(*) FILTER (WHERE completed_at < NOW() - INTERVAL '7 days')
293 FROM transactions
294 WHERE seller_id = $1
295 AND status = 'completed'
296 AND completed_at >= NOW() - INTERVAL '7 days' * 2
297 ",
298 )
299 .bind(seller_id)
300 .fetch_one(&h.db)
301 .await
302 .unwrap();
303
304 assert_eq!(row.0, 3000, "Current revenue");
305 assert_eq!(row.1, 2, "Current sales");
306 assert_eq!(row.2, 500, "Previous revenue");
307 assert_eq!(row.3, 1, "Previous sales");
308 }
309
310 #[tokio::test]
311 async fn follower_comparison() {
312 let mut h = TestHarness::new().await;
313
314 let seller_id = h
315 .signup("aseller_fol", "aseller_fol@test.com", "password123")
316 .await;
317 h.grant_creator(seller_id).await;
318
319 // Create follower users via direct SQL
320 let fan1 = create_buyer(&h.db).await;
321 let fan2 = create_buyer(&h.db).await;
322 let fan3 = create_buyer(&h.db).await;
323
324 let seller_uuid: uuid::Uuid = seller_id.into();
325
326 // Current period follows (within last 30 days)
327 insert_follow(&h.db, fan1, "user", seller_uuid, &days_ago_at(3, 12)).await;
328 insert_follow(&h.db, fan2, "user", seller_uuid, &days_ago_at(5, 12)).await;
329
330 // Previous period follow (31-60 days ago)
331 insert_follow(&h.db, fan3, "user", seller_uuid, &days_ago_at(35, 12)).await;
332
333 // Follower comparison, user scope, 30d
334 let row: (i64, i64) = sqlx::query_as(
335 r"
336 SELECT
337 COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '30 days'),
338 COUNT(*) FILTER (WHERE created_at < NOW() - INTERVAL '30 days')
339 FROM follows
340 WHERE target_type = $1
341 AND target_id = $2
342 AND created_at >= NOW() - INTERVAL '30 days' * 2
343 ",
344 )
345 .bind("user")
346 .bind(seller_uuid)
347 .fetch_one(&h.db)
348 .await
349 .unwrap();
350
351 assert_eq!(row.0, 2, "Current period: 2 new followers");
352 assert_eq!(row.1, 1, "Previous period: 1 follower");
353 }
354
355 #[tokio::test]
356 async fn timeseries_empty_returns_no_rows() {
357 let mut h = TestHarness::new().await;
358 let seller_id = h
359 .signup("aseller_empty", "aseller_empty@test.com", "password123")
360 .await;
361
362 // No transactions, timeseries should be empty
363 let rows: Vec<(chrono::DateTime<chrono::Utc>, i64, i64)> = sqlx::query_as(
364 r"
365 SELECT
366 date_trunc('day', completed_at) AS bucket,
367 COALESCE(SUM(amount_cents), 0),
368 COUNT(*)
369 FROM transactions
370 WHERE seller_id = $1
371 AND status = 'completed'
372 AND completed_at >= NOW() - INTERVAL '30 days'
373 GROUP BY bucket
374 ORDER BY bucket
375 LIMIT 500
376 ",
377 )
378 .bind(seller_id)
379 .fetch_all(&h.db)
380 .await
381 .unwrap();
382
383 assert!(rows.is_empty(), "Empty seller should have no buckets");
384
385 // All-time aggregate should return zeros
386 let row: (i64, i64) = sqlx::query_as(
387 r"
388 SELECT
389 COALESCE(SUM(amount_cents), 0),
390 COUNT(*)
391 FROM transactions
392 WHERE seller_id = $1 AND status = 'completed'
393 ",
394 )
395 .bind(seller_id)
396 .fetch_one(&h.db)
397 .await
398 .unwrap();
399
400 assert_eq!(row.0, 0, "Revenue should be 0");
401 assert_eq!(row.1, 0, "Sales should be 0");
402 }
403