max / makenotwork

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