max / makenotwork

5.3 KB · 147 lines History Blame Raw

1	use super::{
2	Cents, ClaimToken, DbTransaction, DownloadToken, ItemId, PgPool, ProjectId, PromoCodeId,
3	Result, TransactionId, UserId,
4	};
5
6	/// Sum completed revenue and count sales for all items in a project.
7	///
8	/// Returns `(total_revenue_cents, total_sales)`. Only completed transactions
9	/// are counted; pending, failed, and refunded are excluded.
10	#[tracing::instrument(skip_all)]
11	pub async fn get_revenue_by_project(pool: &PgPool, project_id: ProjectId) -> Result<(i64, i64)> {
12	let row = sqlx::query!(
13	r#"
14	SELECT
15	COALESCE(SUM(t.amount_cents), 0)::BIGINT AS "total!",
16	COUNT(*) AS "count!"
17	FROM transactions t
18	JOIN items i ON t.item_id = i.id
19	WHERE i.project_id = $1
20	AND t.status = 'completed'
21	"#,
22	project_id as ProjectId,
23	)
24	.fetch_one(pool)
25	.await?;
26
27	Ok((row.total, row.count))
28	}
29
30	/// Revenue per project for a given seller, returned as (project_id, title, revenue_cents).
31	/// Single query replaces N+1 loop in dashboard analytics.
32	#[tracing::instrument(skip_all)]
33	pub async fn get_revenue_by_user_projects(
34	pool: &PgPool,
35	user_id: UserId,
36	) -> Result<Vec<(ProjectId, String, i64)>> {
37	let rows = sqlx::query!(
38	r#"
39	SELECT p.id AS "id: ProjectId", p.title, COALESCE(SUM(t.amount_cents), 0)::BIGINT AS "revenue!"
40	FROM projects p
41	LEFT JOIN items i ON i.project_id = p.id
42	LEFT JOIN transactions t ON t.item_id = i.id AND t.status = 'completed'
43	WHERE p.user_id = $1
44	GROUP BY p.id, p.title
45	HAVING COALESCE(SUM(t.amount_cents), 0) > 0
46	ORDER BY COALESCE(SUM(t.amount_cents), 0) DESC
47	"#,
48	user_id as UserId,
49	)
50	.fetch_all(pool)
51	.await?;
52
53	Ok(rows
54	.into_iter()
55	.map(\|r\| (r.id, r.title, r.revenue))
56	.collect())
57	}
58
59	/// Revenue and sales per project for a seller within a time range.
60	///
61	/// Used for the cross-project comparison table on the user analytics tab.
62	#[tracing::instrument(skip_all)]
63	pub async fn get_revenue_by_user_projects_in_range(
64	pool: &PgPool,
65	user_id: UserId,
66	range: &crate::db::analytics::TimeRange,
67	) -> Result<Vec<(ProjectId, String, i64, i64)>> {
68	let time_filter = match range.interval_sql() {
69	Some(interval) => format!(" AND t.completed_at >= NOW() - INTERVAL '{interval}'"),
70	None => String::new(),
71	};
72
73	let sql = format!(
74	r"
75	SELECT p.id, p.title,
76	COALESCE(SUM(t.amount_cents), 0)::BIGINT,
77	COUNT(t.id)::BIGINT
78	FROM projects p
79	LEFT JOIN items i ON i.project_id = p.id
80	LEFT JOIN transactions t ON t.item_id = i.id AND t.status = 'completed'{time_filter}
81	WHERE p.user_id = $1
82	GROUP BY p.id, p.title
83	ORDER BY COALESCE(SUM(t.amount_cents), 0) DESC
84	"
85	);
86
87	// runtime-checked: dynamically-built SQL string, the `time_filter` clause is
88	// conditionally interpolated via `format!`, so the query text isn't a literal
89	// and can't be compile-checked by the macro.
90	let rows: Vec<(ProjectId, String, i64, i64)> =
91	sqlx::query_as(&sql).bind(user_id).fetch_all(pool).await?;
92
93	Ok(rows)
94	}
95
96	/// Platform-wide revenue stats: total completed revenue, completed count, refunded count.
97	#[tracing::instrument(skip_all)]
98	pub async fn get_platform_revenue_stats(pool: &PgPool) -> Result<(i64, i64, i64)> {
99	let row = sqlx::query!(
100	r#"
101	SELECT
102	COALESCE(SUM(CASE WHEN status = 'completed' THEN amount_cents ELSE 0 END), 0)::BIGINT AS "revenue!",
103	COALESCE(SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END), 0)::BIGINT AS "completed!",
104	COALESCE(SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END), 0)::BIGINT AS "refunded!"
105	FROM transactions
106	"#,
107	)
108	.fetch_one(pool)
109	.await?;
110
111	Ok((row.revenue, row.completed, row.refunded))
112	}
113
114	/// Completed and refunded sales for a specific item, for the item dashboard Sales tab.
115	#[tracing::instrument(skip_all)]
116	pub async fn get_sales_by_item(
117	pool: &PgPool,
118	item_id: ItemId,
119	seller_id: UserId,
120	) -> Result<Vec<DbTransaction>> {
121	let rows = sqlx::query_as!(
122	DbTransaction,
123	r#"
124	SELECT
125	id AS "id: TransactionId", buyer_id AS "buyer_id: UserId", seller_id AS "seller_id: UserId",
126	item_id AS "item_id: ItemId", amount_cents AS "amount_cents: Cents", platform_fee_cents AS "platform_fee_cents: Cents",
127	currency, status AS "status: crate::db::TransactionStatus", stripe_payment_intent_id, stripe_checkout_session_id,
128	created_at AS "created_at: chrono::DateTime<chrono::Utc>", completed_at AS "completed_at: chrono::DateTime<chrono::Utc>",
129	item_title, seller_username, share_contact, project_id AS "project_id: ProjectId",
130	parent_transaction_id AS "parent_transaction_id: TransactionId", promo_code_id AS "promo_code_id: PromoCodeId",
131	guest_email, claim_token AS "claim_token: ClaimToken", claimed_by AS "claimed_by: UserId",
132	download_token AS "download_token: DownloadToken"
133	FROM transactions
134	WHERE item_id = $1 AND seller_id = $2
135	AND status IN ('completed', 'refunded')
136	ORDER BY created_at DESC
137	LIMIT 200
138	"#,
139	item_id as ItemId,
140	seller_id as UserId,
141	)
142	.fetch_all(pool)
143	.await?;
144
145	Ok(rows)
146	}
147