Skip to main content

max / makenotwork

9.7 KB · 344 lines History Blame Raw
1 //! Creator waitlist and wave management queries.
2
3 use sqlx::{PgPool, Postgres};
4
5 use super::enums::{SelectionMethod, WaitlistStatus};
6 use super::models::*;
7 use super::{CreatorWaveId, UserId, WaitlistEntryId};
8 use crate::error::Result;
9
10 /// Submit a new creator waitlist application.
11 #[tracing::instrument(skip_all)]
12 pub async fn create_waitlist_entry(pool: &PgPool, user_id: UserId, pitch: &str) -> Result<DbWaitlistEntry> {
13 let entry = sqlx::query_as::<_, DbWaitlistEntry>(
14 r#"
15 INSERT INTO creator_waitlist (user_id, pitch)
16 VALUES ($1, $2)
17 RETURNING *
18 "#,
19 )
20 .bind(user_id)
21 .bind(pitch)
22 .fetch_one(pool)
23 .await?;
24
25 Ok(entry)
26 }
27
28 /// Fetch a user's waitlist entry. Returns `None` if they haven't applied.
29 #[tracing::instrument(skip_all)]
30 pub async fn get_waitlist_entry_by_user(pool: &PgPool, user_id: UserId) -> Result<Option<DbWaitlistEntry>> {
31 let entry = sqlx::query_as::<_, DbWaitlistEntry>(
32 "SELECT * FROM creator_waitlist WHERE user_id = $1",
33 )
34 .bind(user_id)
35 .fetch_optional(pool)
36 .await?;
37
38 Ok(entry)
39 }
40
41 /// Create a waitlist entry for an invited user (no pitch required).
42 #[tracing::instrument(skip_all)]
43 pub async fn create_invited_waitlist_entry(
44 pool: &PgPool,
45 user_id: UserId,
46 invited_by_user_id: UserId,
47 ) -> Result<DbWaitlistEntry> {
48 let entry = sqlx::query_as::<_, DbWaitlistEntry>(
49 r#"
50 INSERT INTO creator_waitlist (user_id, pitch, selection_method, invited_by_user_id)
51 VALUES ($1, NULL, 'invited', $2)
52 RETURNING *
53 "#,
54 )
55 .bind(user_id)
56 .bind(invited_by_user_id)
57 .fetch_one(pool)
58 .await?;
59
60 Ok(entry)
61 }
62
63 /// List waitlist entries for the admin dashboard, optionally filtered by status.
64 #[tracing::instrument(skip_all)]
65 pub async fn get_admin_waitlist(pool: &PgPool, status_filter: Option<&str>) -> Result<Vec<DbAdminWaitlistRow>> {
66 let rows = if let Some(status) = status_filter {
67 sqlx::query_as::<_, DbAdminWaitlistRow>(
68 r#"
69 SELECT cw.id, cw.user_id, cw.pitch, cw.status, cw.selection_method,
70 cw.admin_note, cw.created_at, cw.reviewed_at,
71 u.username, u.email, u.email_verified,
72 cw.invited_by_user_id,
73 inv.username AS invited_by_username
74 FROM creator_waitlist cw
75 JOIN users u ON u.id = cw.user_id
76 LEFT JOIN users inv ON inv.id = cw.invited_by_user_id
77 WHERE cw.status = $1
78 ORDER BY cw.created_at DESC
79 LIMIT 1000
80 "#,
81 )
82 .bind(status)
83 .fetch_all(pool)
84 .await?
85 } else {
86 sqlx::query_as::<_, DbAdminWaitlistRow>(
87 r#"
88 SELECT cw.id, cw.user_id, cw.pitch, cw.status, cw.selection_method,
89 cw.admin_note, cw.created_at, cw.reviewed_at,
90 u.username, u.email, u.email_verified,
91 cw.invited_by_user_id,
92 inv.username AS invited_by_username
93 FROM creator_waitlist cw
94 JOIN users u ON u.id = cw.user_id
95 LEFT JOIN users inv ON inv.id = cw.invited_by_user_id
96 ORDER BY cw.created_at DESC
97 LIMIT 1000
98 "#,
99 )
100 .fetch_all(pool)
101 .await?
102 };
103
104 Ok(rows)
105 }
106
107 /// Update a waitlist entry's status, selection method, and wave assignment.
108 #[tracing::instrument(skip_all)]
109 pub async fn update_waitlist_status(
110 pool: &PgPool,
111 id: WaitlistEntryId,
112 status: WaitlistStatus,
113 method: Option<SelectionMethod>,
114 wave_id: Option<CreatorWaveId>,
115 ) -> Result<DbWaitlistEntry> {
116 let entry = sqlx::query_as::<_, DbWaitlistEntry>(
117 r#"
118 UPDATE creator_waitlist
119 SET status = $2, selection_method = $3, wave_id = $4, reviewed_at = NOW()
120 WHERE id = $1
121 RETURNING *
122 "#,
123 )
124 .bind(id)
125 .bind(status)
126 .bind(method)
127 .bind(wave_id)
128 .fetch_one(pool)
129 .await?;
130
131 Ok(entry)
132 }
133
134 /// Grant a user the ability to create projects (set `can_create_projects = true`).
135 #[tracing::instrument(skip_all)]
136 pub async fn grant_creator_access<'e, E>(executor: E, user_id: UserId) -> Result<()>
137 where
138 E: sqlx::Executor<'e, Database = Postgres>,
139 {
140 sqlx::query("UPDATE users SET can_create_projects = true WHERE id = $1")
141 .bind(user_id)
142 .execute(executor)
143 .await?;
144
145 Ok(())
146 }
147
148 /// Record a new creator wave with its selection counts.
149 #[tracing::instrument(skip_all)]
150 pub async fn create_wave<'e, E>(
151 executor: E,
152 wave_number: i32,
153 hand_picked: i32,
154 lottery: i32,
155 eligible: i32,
156 note: Option<&str>,
157 ) -> Result<DbCreatorWave>
158 where
159 E: sqlx::Executor<'e, Database = Postgres>,
160 {
161 let wave = sqlx::query_as::<_, DbCreatorWave>(
162 r#"
163 INSERT INTO creator_waves (wave_number, hand_picked_count, lottery_count, total_eligible, note)
164 VALUES ($1, $2, $3, $4, $5)
165 RETURNING *
166 "#,
167 )
168 .bind(wave_number)
169 .bind(hand_picked)
170 .bind(lottery)
171 .bind(eligible)
172 .bind(note)
173 .fetch_one(executor)
174 .await?;
175
176 Ok(wave)
177 }
178
179 /// List all creator waves, newest first.
180 #[tracing::instrument(skip_all)]
181 pub async fn get_all_waves(pool: &PgPool) -> Result<Vec<DbCreatorWave>> {
182 let waves = sqlx::query_as::<_, DbCreatorWave>(
183 "SELECT * FROM creator_waves ORDER BY wave_number DESC LIMIT 500",
184 )
185 .fetch_all(pool)
186 .await?;
187
188 Ok(waves)
189 }
190
191 /// Get the next sequential wave number (max + 1).
192 #[tracing::instrument(skip_all)]
193 pub async fn get_next_wave_number<'e, E>(executor: E) -> Result<i32>
194 where
195 E: sqlx::Executor<'e, Database = Postgres>,
196 {
197 let next: (i32,) = sqlx::query_as(
198 "SELECT COALESCE(MAX(wave_number), 0) + 1 FROM creator_waves",
199 )
200 .fetch_one(executor)
201 .await?;
202
203 Ok(next.0)
204 }
205
206 /// Count pending waitlist entries with verified emails (eligible for lottery).
207 #[tracing::instrument(skip_all)]
208 pub async fn get_lottery_eligible_count<'e, E>(executor: E) -> Result<i64>
209 where
210 E: sqlx::Executor<'e, Database = Postgres>,
211 {
212 let count: (i64,) = sqlx::query_as(
213 r#"
214 SELECT COUNT(*) FROM creator_waitlist cw
215 JOIN users u ON u.id = cw.user_id
216 WHERE cw.status = 'pending' AND u.email_verified = true
217 "#,
218 )
219 .fetch_one(executor)
220 .await?;
221
222 Ok(count.0)
223 }
224
225 /// Run lottery: randomly select `count` eligible entries, mark them approved with wave_id.
226 /// Returns the selected entries.
227 #[tracing::instrument(skip_all)]
228 pub async fn run_lottery<'e, E>(executor: E, wave_id: CreatorWaveId, count: i32) -> Result<Vec<DbWaitlistEntry>>
229 where
230 E: sqlx::Executor<'e, Database = Postgres>,
231 {
232 let entries = sqlx::query_as::<_, DbWaitlistEntry>(
233 r#"
234 UPDATE creator_waitlist
235 SET status = 'approved', selection_method = 'lottery',
236 wave_id = $1, reviewed_at = NOW()
237 WHERE id IN (
238 SELECT cw.id FROM creator_waitlist cw
239 JOIN users u ON u.id = cw.user_id
240 WHERE cw.status = 'pending' AND u.email_verified = true
241 ORDER BY random() LIMIT $2
242 )
243 RETURNING *
244 "#,
245 )
246 .bind(wave_id)
247 .bind(count)
248 .fetch_all(executor)
249 .await?;
250
251 Ok(entries)
252 }
253
254 /// Get aggregate waitlist counts grouped by status.
255 #[tracing::instrument(skip_all)]
256 pub async fn get_waitlist_stats(pool: &PgPool) -> Result<DbWaitlistStats> {
257 let stats: DbWaitlistStats = sqlx::query_as(
258 r#"
259 SELECT
260 COUNT(*) FILTER (WHERE status = 'pending') as pending,
261 COUNT(*) FILTER (WHERE status = 'approved') as approved,
262 COUNT(*) FILTER (WHERE status = 'spam') as spam
263 FROM creator_waitlist
264 "#,
265 )
266 .fetch_one(pool)
267 .await?;
268
269 Ok(stats)
270 }
271
272 /// Count users who have been granted creator access.
273 #[tracing::instrument(skip_all)]
274 pub async fn count_active_creators(pool: &PgPool) -> Result<i64> {
275 let count: (i64,) = sqlx::query_as(
276 "SELECT COUNT(*) FROM users WHERE can_create_projects = true",
277 )
278 .fetch_one(pool)
279 .await?;
280
281 Ok(count.0)
282 }
283
284 /// Count waitlist entries still in "pending" status.
285 #[tracing::instrument(skip_all)]
286 pub async fn count_waitlist_pending(pool: &PgPool) -> Result<i64> {
287 let count: (i64,) = sqlx::query_as(
288 "SELECT COUNT(*) FROM creator_waitlist WHERE status = 'pending'",
289 )
290 .fetch_one(pool)
291 .await?;
292
293 Ok(count.0)
294 }
295
296 /// Assign wave_id to hand-picked entries that don't have one yet
297 #[tracing::instrument(skip_all)]
298 pub async fn assign_wave_to_handpicks<'e, E>(executor: E, wave_id: CreatorWaveId) -> Result<i64>
299 where
300 E: sqlx::Executor<'e, Database = Postgres>,
301 {
302 let result = sqlx::query(
303 r#"
304 UPDATE creator_waitlist
305 SET wave_id = $1
306 WHERE status = 'approved' AND selection_method = 'hand_picked' AND wave_id IS NULL
307 "#,
308 )
309 .bind(wave_id)
310 .execute(executor)
311 .await?;
312
313 Ok(result.rows_affected() as i64)
314 }
315
316 /// Grant creator access to multiple users in a single query (batch).
317 #[tracing::instrument(skip_all)]
318 pub async fn grant_creator_access_batch<'e, E>(executor: E, user_ids: &[UserId]) -> Result<()>
319 where
320 E: sqlx::Executor<'e, Database = Postgres>,
321 {
322 sqlx::query("UPDATE users SET can_create_projects = true WHERE id = ANY($1)")
323 .bind(user_ids)
324 .execute(executor)
325 .await?;
326
327 Ok(())
328 }
329
330 /// Count hand-picked entries not yet assigned to a wave
331 #[tracing::instrument(skip_all)]
332 pub async fn count_unassigned_handpicks<'e, E>(executor: E) -> Result<i64>
333 where
334 E: sqlx::Executor<'e, Database = Postgres>,
335 {
336 let count: (i64,) = sqlx::query_as(
337 "SELECT COUNT(*) FROM creator_waitlist WHERE status = 'approved' AND selection_method = 'hand_picked' AND wave_id IS NULL",
338 )
339 .fetch_one(executor)
340 .await?;
341
342 Ok(count.0)
343 }
344