Skip to main content

max / makenotwork

6.4 KB · 210 lines History Blame Raw
1 use super::{DateTime, PgPool, Result, UserId, Utc};
2
3 /// A buyer's email and display name for platform notifications (e.g. creator departure).
4 ///
5 /// Unlike [`DbContactRow`], this includes ALL buyers regardless of contact sharing
6 /// preference, because platform-initiated notifications (content removal warnings)
7 /// are distinct from creator-initiated contact.
8 #[derive(sqlx::FromRow)]
9 pub struct BuyerNotificationRow {
10 pub email: String,
11 pub display_name: Option<String>,
12 }
13
14 /// Get unique buyers who purchased from a seller, for platform notifications.
15 ///
16 /// This bypasses `share_contact` and `contact_revocations` because the notification
17 /// is sent by the platform (not the creator) to warn buyers about content removal.
18 ///
19 /// Capped at `limit` rows to bound memory + outbound email volume on creators
20 /// with very large historical buyer pools. The caller is responsible for
21 /// noting when the cap is hit (returned slice length == limit).
22 #[tracing::instrument(skip_all)]
23 pub async fn get_all_buyers_for_seller(
24 pool: &PgPool,
25 seller_id: UserId,
26 limit: i64,
27 ) -> Result<Vec<BuyerNotificationRow>> {
28 let rows = sqlx::query_as!(
29 BuyerNotificationRow,
30 r#"
31 SELECT DISTINCT u.email, u.display_name
32 FROM transactions t
33 JOIN users u ON u.id = t.buyer_id
34 WHERE t.seller_id = $1
35 AND t.status = 'completed'
36 AND t.buyer_id IS NOT NULL
37 LIMIT $2
38 "#,
39 seller_id as UserId,
40 limit,
41 )
42 .fetch_all(pool)
43 .await?;
44
45 Ok(rows)
46 }
47
48 /// A contact row: a buyer who opted to share their email with the seller.
49 #[derive(sqlx::FromRow)]
50 pub struct DbContactRow {
51 pub username: String,
52 pub email: String,
53 pub total_purchases: i64,
54 pub total_spent_cents: i64,
55 pub last_purchase_at: DateTime<Utc>,
56 }
57
58 /// A creator the fan has actively shared contact info with (no revocation).
59 #[derive(sqlx::FromRow)]
60 pub struct SharedCreatorRow {
61 pub seller_id: UserId,
62 pub username: String,
63 pub display_name: Option<String>,
64 }
65
66 /// Get unique contacts for a seller: buyers who opted in to share their email.
67 ///
68 /// Aggregates across all completed transactions where `share_contact = true`,
69 /// returning one row per buyer with purchase stats. Excludes buyers who have
70 /// revoked contact sharing.
71 #[tracing::instrument(skip_all)]
72 pub async fn get_seller_contacts(pool: &PgPool, seller_id: UserId) -> Result<Vec<DbContactRow>> {
73 let rows = sqlx::query_as!(
74 DbContactRow,
75 r#"
76 SELECT
77 u.username,
78 u.email,
79 COUNT(*) AS "total_purchases!",
80 COALESCE(SUM(t.amount_cents), 0)::BIGINT AS "total_spent_cents!",
81 MAX(t.completed_at) AS "last_purchase_at!: chrono::DateTime<chrono::Utc>"
82 FROM transactions t
83 JOIN users u ON u.id = t.buyer_id
84 WHERE t.seller_id = $1
85 AND t.status = 'completed'
86 AND t.share_contact = true
87 AND NOT EXISTS (
88 SELECT 1 FROM contact_revocations cr
89 WHERE cr.buyer_id = t.buyer_id AND cr.seller_id = t.seller_id
90 )
91 GROUP BY t.buyer_id, u.username, u.email
92 ORDER BY MAX(t.completed_at) DESC
93 LIMIT 500
94 "#,
95 seller_id as UserId,
96 )
97 .fetch_all(pool)
98 .await?;
99
100 Ok(rows)
101 }
102
103 /// One page of a seller's sharing-opted-in contacts for CSV export, newest
104 /// first. Paginated so the contacts export streams in bounded batches like its
105 /// sibling exports instead of buffering one capped query into a single `String`
106 /// (ultra-fuzz Run 6 R6-Perf-M2). `(MAX(completed_at), buyer_id)` ordering is
107 /// stable so OFFSET batches don't reorder.
108 #[tracing::instrument(skip_all)]
109 pub async fn get_seller_contacts_page(
110 pool: &PgPool,
111 seller_id: UserId,
112 limit: i64,
113 offset: i64,
114 ) -> Result<Vec<DbContactRow>> {
115 let rows = sqlx::query_as::<_, DbContactRow>(
116 r"
117 SELECT
118 u.username,
119 u.email,
120 COUNT(*) AS total_purchases,
121 COALESCE(SUM(t.amount_cents), 0)::BIGINT AS total_spent_cents,
122 MAX(t.completed_at) AS last_purchase_at
123 FROM transactions t
124 JOIN users u ON u.id = t.buyer_id
125 WHERE t.seller_id = $1
126 AND t.status = 'completed'
127 AND t.share_contact = true
128 AND NOT EXISTS (
129 SELECT 1 FROM contact_revocations cr
130 WHERE cr.buyer_id = t.buyer_id AND cr.seller_id = t.seller_id
131 )
132 GROUP BY t.buyer_id, u.username, u.email
133 ORDER BY MAX(t.completed_at) DESC, t.buyer_id
134 LIMIT $2 OFFSET $3
135 ",
136 )
137 .bind(seller_id)
138 .bind(limit)
139 .bind(offset)
140 .fetch_all(pool)
141 .await?;
142
143 Ok(rows)
144 }
145
146 /// Record a contact revocation (fan withdraws email sharing from a creator).
147 ///
148 /// Idempotent: does nothing if already revoked.
149 #[tracing::instrument(skip_all)]
150 pub async fn revoke_contact_sharing(
151 pool: &PgPool,
152 buyer_id: UserId,
153 seller_id: UserId,
154 ) -> Result<()> {
155 sqlx::query!(
156 "INSERT INTO contact_revocations (buyer_id, seller_id) VALUES ($1, $2) ON CONFLICT DO NOTHING",
157 buyer_id as UserId,
158 seller_id as UserId,
159 )
160 .execute(pool)
161 .await?;
162
163 Ok(())
164 }
165
166 /// Clear a contact revocation (fan re-shares on a new purchase).
167 #[tracing::instrument(skip_all)]
168 pub async fn clear_contact_revocation(
169 pool: &PgPool,
170 buyer_id: UserId,
171 seller_id: UserId,
172 ) -> Result<()> {
173 sqlx::query!(
174 "DELETE FROM contact_revocations WHERE buyer_id = $1 AND seller_id = $2",
175 buyer_id as UserId,
176 seller_id as UserId,
177 )
178 .execute(pool)
179 .await?;
180
181 Ok(())
182 }
183
184 /// Get creators the fan has actively shared contact info with (excluding revoked).
185 #[tracing::instrument(skip_all)]
186 pub async fn get_shared_creators(pool: &PgPool, buyer_id: UserId) -> Result<Vec<SharedCreatorRow>> {
187 let rows = sqlx::query_as!(
188 SharedCreatorRow,
189 r#"
190 SELECT DISTINCT t.seller_id AS "seller_id!: UserId", u.username, u.display_name
191 FROM transactions t
192 JOIN users u ON u.id = t.seller_id
193 WHERE t.buyer_id = $1
194 AND t.status = 'completed'
195 AND t.share_contact = true
196 AND NOT EXISTS (
197 SELECT 1 FROM contact_revocations cr
198 WHERE cr.buyer_id = t.buyer_id AND cr.seller_id = t.seller_id
199 )
200 ORDER BY u.username
201 LIMIT 500
202 "#,
203 buyer_id as UserId,
204 )
205 .fetch_all(pool)
206 .await?;
207
208 Ok(rows)
209 }
210