Skip to main content

max / makenotwork

12.4 KB · 382 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!("{}T{:02}:00:00Z", date, hour)
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(pool: &PgPool, follower_id: UserId, target_type: &str, target_id: uuid::Uuid, created_at: &str) {
69 sqlx::query(
70 r#"
71 INSERT INTO follows (follower_id, target_type, target_id, created_at)
72 VALUES ($1, $2, $3, $4::timestamptz)
73 "#,
74 )
75 .bind(follower_id)
76 .bind(target_type)
77 .bind(target_id)
78 .bind(created_at)
79 .execute(pool)
80 .await
81 .expect("insert follow");
82 }
83
84 /// Create a seller user, project, and item via direct SQL. Returns (seller_id, project_id, item_id).
85 async fn setup_seller_with_item(h: &mut TestHarness, suffix: &str) -> (UserId, ProjectId, ItemId) {
86 let seller_id = h.signup(
87 &format!("aseller{suffix}"),
88 &format!("aseller{suffix}@test.com"),
89 "password123",
90 ).await;
91 h.grant_creator(seller_id).await;
92
93 let project_id = ProjectId::new();
94 let item_id = ItemId::new();
95
96 sqlx::query("INSERT INTO projects (id, user_id, slug, title) VALUES ($1, $2, $3, 'Test Project')")
97 .bind(project_id)
98 .bind(seller_id)
99 .bind(format!("proj-{suffix}"))
100 .execute(&h.db)
101 .await
102 .unwrap();
103
104 sqlx::query("INSERT INTO items (id, project_id, title, price_cents, item_type, slug) VALUES ($1, $2, 'Test Item', 1000, 'digital', 'test-item-' || $3)")
105 .bind(item_id)
106 .bind(project_id)
107 .bind(suffix)
108 .execute(&h.db)
109 .await
110 .unwrap();
111
112 (seller_id, project_id, item_id)
113 }
114
115 #[tokio::test]
116 async fn revenue_timeseries_buckets_by_day() {
117 let mut h = TestHarness::new().await;
118 let (seller_id, _, item_id) = setup_seller_with_item(&mut h, "ts1").await;
119
120 // Insert 3 transactions across 2 days (all within 30-day window)
121 let day_a = days_ago_at(2, 12);
122 let day_a2 = days_ago_at(2, 14);
123 let day_b = days_ago_at(3, 10);
124 insert_transaction(&h.db, seller_id, item_id, 1000, &day_a).await;
125 insert_transaction(&h.db, seller_id, item_id, 2000, &day_a2).await;
126 insert_transaction(&h.db, seller_id, item_id, 500, &day_b).await;
127
128 // Timeseries bucketed by day, user scope, 30d range
129 let rows: Vec<(chrono::DateTime<chrono::Utc>, i64, i64)> = sqlx::query_as(
130 r#"
131 SELECT
132 date_trunc('day', completed_at) AS bucket,
133 COALESCE(SUM(amount_cents), 0),
134 COUNT(*)
135 FROM transactions
136 WHERE seller_id = $1
137 AND status = 'completed'
138 AND completed_at >= NOW() - INTERVAL '30 days'
139 GROUP BY bucket
140 ORDER BY bucket
141 LIMIT 500
142 "#,
143 )
144 .bind(seller_id)
145 .fetch_all(&h.db)
146 .await
147 .unwrap();
148
149 assert_eq!(rows.len(), 2, "Expected 2 daily buckets, got {}", rows.len());
150
151 // Earlier day: 1 sale, 500 cents
152 assert_eq!(rows[0].1, 500);
153 assert_eq!(rows[0].2, 1);
154
155 // Later day: 2 sales, 3000 cents
156 assert_eq!(rows[1].1, 3000);
157 assert_eq!(rows[1].2, 2);
158 }
159
160 #[tokio::test]
161 async fn revenue_timeseries_item_and_project_scope() {
162 let mut h = TestHarness::new().await;
163
164 let seller_id = h.signup("aseller_scope", "aseller_scope@test.com", "password123").await;
165 h.grant_creator(seller_id).await;
166
167 let project_id = ProjectId::new();
168 let item_a = ItemId::new();
169 let item_b = ItemId::new();
170
171 sqlx::query("INSERT INTO projects (id, user_id, slug, title) VALUES ($1, $2, 'scope-test', 'Scope Test')")
172 .bind(project_id)
173 .bind(seller_id)
174 .execute(&h.db)
175 .await
176 .unwrap();
177
178 sqlx::query("INSERT INTO items (id, project_id, title, price_cents, item_type, slug) VALUES ($1, $2, 'Item A', 1000, 'digital', 'item-a')")
179 .bind(item_a)
180 .bind(project_id)
181 .execute(&h.db)
182 .await
183 .unwrap();
184
185 sqlx::query("INSERT INTO items (id, project_id, title, price_cents, item_type, slug) VALUES ($1, $2, 'Item B', 2000, 'digital', 'item-b')")
186 .bind(item_b)
187 .bind(project_id)
188 .execute(&h.db)
189 .await
190 .unwrap();
191
192 // Transactions: item_a on day_b and day_a, item_b on day_a only
193 let day_a = days_ago_at(2, 12);
194 let day_a2 = days_ago_at(2, 13);
195 let day_b = days_ago_at(3, 12);
196 insert_transaction(&h.db, seller_id, item_a, 1000, &day_b).await;
197 insert_transaction(&h.db, seller_id, item_a, 1000, &day_a).await;
198 insert_transaction(&h.db, seller_id, item_b, 2000, &day_a2).await;
199
200 // Item scope: only item_a
201 let rows: Vec<(chrono::DateTime<chrono::Utc>, i64, i64)> = sqlx::query_as(
202 r#"
203 SELECT
204 date_trunc('day', completed_at) AS bucket,
205 COALESCE(SUM(amount_cents), 0),
206 COUNT(*)
207 FROM transactions
208 WHERE seller_id = $1
209 AND item_id = $2
210 AND status = 'completed'
211 AND completed_at >= NOW() - INTERVAL '30 days'
212 GROUP BY bucket
213 ORDER BY bucket
214 LIMIT 500
215 "#,
216 )
217 .bind(seller_id)
218 .bind(item_a)
219 .fetch_all(&h.db)
220 .await
221 .unwrap();
222
223 assert_eq!(rows.len(), 2, "Item A should have 2 daily buckets");
224 assert_eq!(rows[0].1, 1000);
225 assert_eq!(rows[1].1, 1000);
226
227 // Project scope: both items
228 let rows: Vec<(chrono::DateTime<chrono::Utc>, i64, i64)> = sqlx::query_as(
229 r#"
230 SELECT
231 date_trunc('day', t.completed_at) AS bucket,
232 COALESCE(SUM(t.amount_cents), 0),
233 COUNT(*)
234 FROM transactions t
235 WHERE t.seller_id = $1
236 AND t.item_id IN (SELECT id FROM items WHERE project_id = $2)
237 AND t.status = 'completed'
238 AND t.completed_at >= NOW() - INTERVAL '30 days'
239 GROUP BY bucket
240 ORDER BY bucket
241 LIMIT 500
242 "#,
243 )
244 .bind(seller_id)
245 .bind(project_id)
246 .fetch_all(&h.db)
247 .await
248 .unwrap();
249
250 assert_eq!(rows.len(), 2, "Project scope should have 2 daily buckets");
251 assert_eq!(rows[0].1, 1000, "Earlier day: only item_a");
252 assert_eq!(rows[1].1, 3000, "Later day: item_a + item_b");
253 assert_eq!(rows[1].2, 2, "Later day: 2 sales total");
254 }
255
256 #[tokio::test]
257 async fn period_comparison_current_vs_previous() {
258 let mut h = TestHarness::new().await;
259 let (seller_id, _, item_id) = setup_seller_with_item(&mut h, "cmp1").await;
260
261 // Current period (within last 7 days): 2 sales, 3000 cents
262 insert_transaction(&h.db, seller_id, item_id, 1000, &days_ago_at(2, 12)).await;
263 insert_transaction(&h.db, seller_id, item_id, 2000, &days_ago_at(3, 12)).await;
264
265 // Previous period (8-14 days ago): 1 sale, 500 cents
266 insert_transaction(&h.db, seller_id, item_id, 500, &days_ago_at(10, 12)).await;
267
268 // Period comparison with FILTER, user scope, 7d
269 let row: (i64, i64, i64, i64) = sqlx::query_as(
270 r#"
271 SELECT
272 COALESCE(SUM(amount_cents) FILTER (WHERE completed_at >= NOW() - INTERVAL '7 days'), 0),
273 COUNT(*) FILTER (WHERE completed_at >= NOW() - INTERVAL '7 days'),
274 COALESCE(SUM(amount_cents) FILTER (WHERE completed_at < NOW() - INTERVAL '7 days'), 0),
275 COUNT(*) FILTER (WHERE completed_at < NOW() - INTERVAL '7 days')
276 FROM transactions
277 WHERE seller_id = $1
278 AND status = 'completed'
279 AND completed_at >= NOW() - INTERVAL '7 days' * 2
280 "#,
281 )
282 .bind(seller_id)
283 .fetch_one(&h.db)
284 .await
285 .unwrap();
286
287 assert_eq!(row.0, 3000, "Current revenue");
288 assert_eq!(row.1, 2, "Current sales");
289 assert_eq!(row.2, 500, "Previous revenue");
290 assert_eq!(row.3, 1, "Previous sales");
291 }
292
293 #[tokio::test]
294 async fn follower_comparison() {
295 let mut h = TestHarness::new().await;
296
297 let seller_id = h.signup("aseller_fol", "aseller_fol@test.com", "password123").await;
298 h.grant_creator(seller_id).await;
299
300 // Create follower users via direct SQL
301 let fan1 = create_buyer(&h.db).await;
302 let fan2 = create_buyer(&h.db).await;
303 let fan3 = create_buyer(&h.db).await;
304
305 let seller_uuid: uuid::Uuid = seller_id.into();
306
307 // Current period follows (within last 30 days)
308 insert_follow(&h.db, fan1, "user", seller_uuid, &days_ago_at(3, 12)).await;
309 insert_follow(&h.db, fan2, "user", seller_uuid, &days_ago_at(5, 12)).await;
310
311 // Previous period follow (31-60 days ago)
312 insert_follow(&h.db, fan3, "user", seller_uuid, &days_ago_at(35, 12)).await;
313
314 // Follower comparison, user scope, 30d
315 let row: (i64, i64) = sqlx::query_as(
316 r#"
317 SELECT
318 COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '30 days'),
319 COUNT(*) FILTER (WHERE created_at < NOW() - INTERVAL '30 days')
320 FROM follows
321 WHERE target_type = $1
322 AND target_id = $2
323 AND created_at >= NOW() - INTERVAL '30 days' * 2
324 "#,
325 )
326 .bind("user")
327 .bind(seller_uuid)
328 .fetch_one(&h.db)
329 .await
330 .unwrap();
331
332 assert_eq!(row.0, 2, "Current period: 2 new followers");
333 assert_eq!(row.1, 1, "Previous period: 1 follower");
334 }
335
336 #[tokio::test]
337 async fn timeseries_empty_returns_no_rows() {
338 let mut h = TestHarness::new().await;
339 let seller_id = h.signup("aseller_empty", "aseller_empty@test.com", "password123").await;
340
341 // No transactions — timeseries should be empty
342 let rows: Vec<(chrono::DateTime<chrono::Utc>, i64, i64)> = sqlx::query_as(
343 r#"
344 SELECT
345 date_trunc('day', completed_at) AS bucket,
346 COALESCE(SUM(amount_cents), 0),
347 COUNT(*)
348 FROM transactions
349 WHERE seller_id = $1
350 AND status = 'completed'
351 AND completed_at >= NOW() - INTERVAL '30 days'
352 GROUP BY bucket
353 ORDER BY bucket
354 LIMIT 500
355 "#,
356 )
357 .bind(seller_id)
358 .fetch_all(&h.db)
359 .await
360 .unwrap();
361
362 assert!(rows.is_empty(), "Empty seller should have no buckets");
363
364 // All-time aggregate should return zeros
365 let row: (i64, i64) = sqlx::query_as(
366 r#"
367 SELECT
368 COALESCE(SUM(amount_cents), 0),
369 COUNT(*)
370 FROM transactions
371 WHERE seller_id = $1 AND status = 'completed'
372 "#,
373 )
374 .bind(seller_id)
375 .fetch_one(&h.db)
376 .await
377 .unwrap();
378
379 assert_eq!(row.0, 0, "Revenue should be 0");
380 assert_eq!(row.1, 0, "Sales should be 0");
381 }
382