Skip to main content

max / multithreaded

45.4 KB · 1504 lines History Blame Raw
1 //! Database read queries — projection structs and SQL.
2
3 use chrono::{DateTime, Utc};
4 use sqlx::PgPool;
5 use uuid::Uuid;
6
7 // ============================================================================
8 // Projection structs — shaped for templates, not domain models
9 // ============================================================================
10
11 #[derive(sqlx::FromRow)]
12 pub struct CommunityRow {
13 pub id: Uuid,
14 pub name: String,
15 pub slug: String,
16 pub description: Option<String>,
17 pub suspended_at: Option<DateTime<Utc>>,
18 pub auto_hide_threshold: Option<i32>,
19 }
20
21 #[derive(sqlx::FromRow)]
22 pub struct CategoryWithCount {
23 pub name: String,
24 pub slug: String,
25 pub description: Option<String>,
26 pub thread_count: i64,
27 }
28
29 #[derive(sqlx::FromRow)]
30 pub struct CategoryRow {
31 pub name: String,
32 pub slug: String,
33 }
34
35 #[derive(sqlx::FromRow)]
36 pub struct ThreadWithMeta {
37 pub id: Uuid,
38 pub title: String,
39 pub author_name: String,
40 pub author_username: String,
41 pub reply_count: i64,
42 pub last_activity_at: DateTime<Utc>,
43 pub pinned: bool,
44 pub locked: bool,
45 }
46
47 #[derive(sqlx::FromRow)]
48 pub struct ThreadWithBreadcrumb {
49 pub id: Uuid,
50 pub title: String,
51 pub locked: bool,
52 pub pinned: bool,
53 pub author_id: Uuid,
54 pub community_id: Uuid,
55 pub community_name: String,
56 pub community_slug: String,
57 pub category_name: String,
58 pub category_slug: String,
59 }
60
61 #[derive(sqlx::FromRow)]
62 pub struct PostWithAuthor {
63 pub id: Uuid,
64 pub author_id: Uuid,
65 pub author_name: String,
66 pub author_username: String,
67 pub body_html: String,
68 pub created_at: DateTime<Utc>,
69 pub edited_at: Option<DateTime<Utc>>,
70 pub deleted_at: Option<DateTime<Utc>>,
71 pub removed_at: Option<DateTime<Utc>>,
72 }
73
74 #[derive(sqlx::FromRow)]
75 pub struct FootnoteWithAuthor {
76 pub id: Uuid,
77 pub post_id: Uuid,
78 pub author_id: Uuid,
79 pub author_name: String,
80 pub author_username: String,
81 pub body_html: String,
82 pub created_at: DateTime<Utc>,
83 }
84
85 #[derive(sqlx::FromRow)]
86 pub struct PostForEdit {
87 pub id: Uuid,
88 pub author_id: Uuid,
89 pub body_markdown: String,
90 pub created_at: DateTime<Utc>,
91 pub deleted_at: Option<DateTime<Utc>>,
92 pub thread_id: Uuid,
93 pub thread_title: String,
94 pub community_name: String,
95 pub community_slug: String,
96 pub community_id: Uuid,
97 pub category_name: String,
98 pub category_slug: String,
99 }
100
101 /// A category row with its ID for internal API lookups.
102 #[derive(sqlx::FromRow)]
103 pub struct CategoryIdRow {
104 pub id: Uuid,
105 pub name: String,
106 pub slug: String,
107 }
108
109 /// Look up a category by community_id and category slug. Returns the category ID.
110 #[tracing::instrument(skip_all)]
111 pub async fn get_category_by_community_and_slug(
112 pool: &PgPool,
113 community_id: Uuid,
114 category_slug: &str,
115 ) -> Result<Option<CategoryIdRow>, sqlx::Error> {
116 sqlx::query_as::<_, CategoryIdRow>(
117 "SELECT id, name, slug FROM categories WHERE community_id = $1 AND slug = $2",
118 )
119 .bind(community_id)
120 .bind(category_slug)
121 .fetch_optional(pool)
122 .await
123 }
124
125 /// Look up a thread by its external reference (e.g., "mnw:item:uuid").
126 #[tracing::instrument(skip_all)]
127 pub async fn get_thread_by_external_ref(
128 pool: &PgPool,
129 external_ref: &str,
130 ) -> Result<Option<(Uuid,)>, sqlx::Error> {
131 sqlx::query_as::<_, (Uuid,)>(
132 "SELECT id FROM threads WHERE external_ref = $1",
133 )
134 .bind(external_ref)
135 .fetch_optional(pool)
136 .await
137 }
138
139 /// Get thread stats: post count and last activity timestamp.
140 #[tracing::instrument(skip_all)]
141 #[allow(clippy::type_complexity)]
142 pub async fn get_thread_stats(
143 pool: &PgPool,
144 thread_id: Uuid,
145 ) -> Result<Option<(i64, Option<DateTime<Utc>>)>, sqlx::Error> {
146 sqlx::query_as::<_, (i64, Option<DateTime<Utc>>)>(
147 "SELECT COUNT(p.id), MAX(p.created_at)
148 FROM posts p
149 WHERE p.thread_id = $1",
150 )
151 .bind(thread_id)
152 .fetch_optional(pool)
153 .await
154 }
155
156 // ============================================================================
157 // Queries
158 // ============================================================================
159
160 #[derive(sqlx::FromRow)]
161 pub struct CommunityListRow {
162 pub name: String,
163 pub slug: String,
164 pub description: Option<String>,
165 pub category_count: i64,
166 pub thread_count: i64,
167 }
168
169 /// List non-suspended communities with category and thread counts (paginated).
170 #[tracing::instrument(skip_all)]
171 pub async fn list_communities(pool: &PgPool, limit: i64, offset: i64) -> Result<Vec<CommunityListRow>, sqlx::Error> {
172 sqlx::query_as::<_, CommunityListRow>(
173 "SELECT co.name, co.slug, co.description,
174 COUNT(DISTINCT c.id) AS category_count,
175 COUNT(DISTINCT t.id) AS thread_count
176 FROM communities co
177 LEFT JOIN categories c ON c.community_id = co.id
178 LEFT JOIN threads t ON t.category_id = c.id
179 WHERE co.suspended_at IS NULL
180 GROUP BY co.id
181 ORDER BY co.name
182 LIMIT $1 OFFSET $2",
183 )
184 .bind(limit)
185 .bind(offset)
186 .fetch_all(pool)
187 .await
188 }
189
190 /// Count non-suspended communities.
191 #[tracing::instrument(skip_all)]
192 pub async fn count_communities(pool: &PgPool) -> Result<i64, sqlx::Error> {
193 sqlx::query_scalar("SELECT COUNT(*) FROM communities WHERE suspended_at IS NULL")
194 .fetch_one(pool)
195 .await
196 }
197
198 #[tracing::instrument(skip_all)]
199 pub async fn get_community_by_slug(
200 pool: &PgPool,
201 slug: &str,
202 ) -> Result<Option<CommunityRow>, sqlx::Error> {
203 sqlx::query_as::<_, CommunityRow>(
204 "SELECT id, name, slug, description, suspended_at, auto_hide_threshold FROM communities WHERE slug = $1",
205 )
206 .bind(slug)
207 .fetch_optional(pool)
208 .await
209 }
210
211 #[tracing::instrument(skip_all)]
212 pub async fn list_categories_with_counts(
213 pool: &PgPool,
214 community_slug: &str,
215 ) -> Result<Vec<CategoryWithCount>, sqlx::Error> {
216 sqlx::query_as::<_, CategoryWithCount>(
217 "SELECT c.name, c.slug, c.description,
218 COUNT(t.id) AS thread_count
219 FROM categories c
220 JOIN communities co ON co.id = c.community_id
221 LEFT JOIN threads t ON t.category_id = c.id AND t.deleted_at IS NULL
222 WHERE co.slug = $1
223 GROUP BY c.id, c.name, c.slug, c.description, c.sort_order
224 ORDER BY c.sort_order",
225 )
226 .bind(community_slug)
227 .fetch_all(pool)
228 .await
229 }
230
231 #[tracing::instrument(skip_all)]
232 pub async fn get_category_by_slugs(
233 pool: &PgPool,
234 community_slug: &str,
235 category_slug: &str,
236 ) -> Result<Option<CategoryRow>, sqlx::Error> {
237 sqlx::query_as::<_, CategoryRow>(
238 "SELECT c.name, c.slug
239 FROM categories c
240 JOIN communities co ON co.id = c.community_id
241 WHERE co.slug = $1 AND c.slug = $2",
242 )
243 .bind(community_slug)
244 .bind(category_slug)
245 .fetch_optional(pool)
246 .await
247 }
248
249 #[tracing::instrument(skip_all)]
250 pub async fn list_threads_in_category_paginated(
251 pool: &PgPool,
252 community_slug: &str,
253 category_slug: &str,
254 limit: i64,
255 offset: i64,
256 ) -> Result<Vec<ThreadWithMeta>, sqlx::Error> {
257 sqlx::query_as::<_, ThreadWithMeta>(
258 "SELECT t.id, t.title,
259 COALESCE(u.display_name, u.username) AS author_name,
260 u.username AS author_username,
261 (COUNT(p.id) - 1) AS reply_count,
262 t.last_activity_at,
263 t.pinned, t.locked
264 FROM threads t
265 JOIN categories c ON c.id = t.category_id
266 JOIN communities co ON co.id = c.community_id
267 JOIN users u ON u.mnw_account_id = t.author_id
268 LEFT JOIN posts p ON p.thread_id = t.id
269 WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL
270 GROUP BY t.id, t.title, u.display_name, u.username,
271 t.last_activity_at, t.pinned, t.locked
272 ORDER BY t.pinned DESC, t.last_activity_at DESC
273 LIMIT $3 OFFSET $4",
274 )
275 .bind(community_slug)
276 .bind(category_slug)
277 .bind(limit)
278 .bind(offset)
279 .fetch_all(pool)
280 .await
281 }
282
283 /// List threads with sorting. `sort` must be "replies" or "activity".
284 /// `order` must be "asc" or "desc". Pinned threads always sort first.
285 #[tracing::instrument(skip_all)]
286 pub async fn list_threads_in_category_sorted(
287 pool: &PgPool,
288 community_slug: &str,
289 category_slug: &str,
290 sort: &str,
291 order: &str,
292 limit: i64,
293 offset: i64,
294 ) -> Result<Vec<ThreadWithMeta>, sqlx::Error> {
295 let order_clause = match (sort, order) {
296 ("replies", "asc") => "ORDER BY t.pinned DESC, reply_count ASC, t.last_activity_at DESC",
297 ("replies", _) => "ORDER BY t.pinned DESC, reply_count DESC, t.last_activity_at DESC",
298 (_, "asc") => "ORDER BY t.pinned DESC, t.last_activity_at ASC",
299 _ => "ORDER BY t.pinned DESC, t.last_activity_at DESC",
300 };
301
302 let query = format!(
303 "SELECT t.id, t.title,
304 COALESCE(u.display_name, u.username) AS author_name,
305 u.username AS author_username,
306 (COUNT(p.id) - 1) AS reply_count,
307 t.last_activity_at,
308 t.pinned, t.locked
309 FROM threads t
310 JOIN categories c ON c.id = t.category_id
311 JOIN communities co ON co.id = c.community_id
312 JOIN users u ON u.mnw_account_id = t.author_id
313 LEFT JOIN posts p ON p.thread_id = t.id
314 WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL
315 GROUP BY t.id, t.title, u.display_name, u.username,
316 t.last_activity_at, t.pinned, t.locked
317 {order_clause}
318 LIMIT $3 OFFSET $4"
319 );
320
321 sqlx::query_as::<_, ThreadWithMeta>(&query)
322 .bind(community_slug)
323 .bind(category_slug)
324 .bind(limit)
325 .bind(offset)
326 .fetch_all(pool)
327 .await
328 }
329
330 #[tracing::instrument(skip_all)]
331 pub async fn count_threads_in_category(
332 pool: &PgPool,
333 community_slug: &str,
334 category_slug: &str,
335 ) -> Result<i64, sqlx::Error> {
336 sqlx::query_scalar(
337 "SELECT COUNT(*)
338 FROM threads t
339 JOIN categories c ON c.id = t.category_id
340 JOIN communities co ON co.id = c.community_id
341 WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL",
342 )
343 .bind(community_slug)
344 .bind(category_slug)
345 .fetch_one(pool)
346 .await
347 }
348
349 #[tracing::instrument(skip_all)]
350 pub async fn get_thread_with_breadcrumb(
351 pool: &PgPool,
352 thread_id: Uuid,
353 ) -> Result<Option<ThreadWithBreadcrumb>, sqlx::Error> {
354 sqlx::query_as::<_, ThreadWithBreadcrumb>(
355 "SELECT t.id, t.title, t.locked, t.pinned, t.author_id,
356 co.id AS community_id,
357 co.name AS community_name, co.slug AS community_slug,
358 c.name AS category_name, c.slug AS category_slug
359 FROM threads t
360 JOIN categories c ON c.id = t.category_id
361 JOIN communities co ON co.id = c.community_id
362 WHERE t.id = $1",
363 )
364 .bind(thread_id)
365 .fetch_optional(pool)
366 .await
367 }
368
369 #[tracing::instrument(skip_all)]
370 pub async fn list_posts_in_thread(
371 pool: &PgPool,
372 thread_id: Uuid,
373 ) -> Result<Vec<PostWithAuthor>, sqlx::Error> {
374 sqlx::query_as::<_, PostWithAuthor>(
375 "SELECT p.id, p.author_id,
376 COALESCE(u.display_name, u.username) AS author_name,
377 u.username AS author_username,
378 p.body_html, p.created_at, p.edited_at, p.deleted_at,
379 p.removed_at
380 FROM posts p
381 JOIN users u ON u.mnw_account_id = p.author_id
382 WHERE p.thread_id = $1
383 ORDER BY p.created_at",
384 )
385 .bind(thread_id)
386 .fetch_all(pool)
387 .await
388 }
389
390 #[tracing::instrument(skip_all)]
391 pub async fn list_posts_in_thread_paginated(
392 pool: &PgPool,
393 thread_id: Uuid,
394 limit: i64,
395 offset: i64,
396 ) -> Result<Vec<PostWithAuthor>, sqlx::Error> {
397 sqlx::query_as::<_, PostWithAuthor>(
398 "SELECT p.id, p.author_id,
399 COALESCE(u.display_name, u.username) AS author_name,
400 u.username AS author_username,
401 p.body_html, p.created_at, p.edited_at, p.deleted_at,
402 p.removed_at
403 FROM posts p
404 JOIN users u ON u.mnw_account_id = p.author_id
405 WHERE p.thread_id = $1
406 ORDER BY p.created_at
407 LIMIT $2 OFFSET $3",
408 )
409 .bind(thread_id)
410 .bind(limit)
411 .bind(offset)
412 .fetch_all(pool)
413 .await
414 }
415
416 #[tracing::instrument(skip_all)]
417 pub async fn count_posts_in_thread(
418 pool: &PgPool,
419 thread_id: Uuid,
420 ) -> Result<i64, sqlx::Error> {
421 sqlx::query_scalar(
422 "SELECT COUNT(*) FROM posts WHERE thread_id = $1",
423 )
424 .bind(thread_id)
425 .fetch_one(pool)
426 .await
427 }
428
429 /// Count posts + footnotes by a user in the last N seconds (for per-user rate limiting).
430 #[tracing::instrument(skip_all)]
431 pub async fn count_recent_posts_by_user(
432 pool: &PgPool,
433 user_id: Uuid,
434 seconds: i64,
435 ) -> Result<i64, sqlx::Error> {
436 sqlx::query_scalar(
437 "SELECT (SELECT COUNT(*) FROM posts WHERE author_id = $1 AND created_at > NOW() - make_interval(secs => $2))
438 + (SELECT COUNT(*) FROM post_footnotes WHERE author_id = $1 AND created_at > NOW() - make_interval(secs => $2))",
439 )
440 .bind(user_id)
441 .bind(seconds as f64)
442 .fetch_one(pool)
443 .await
444 }
445
446 #[tracing::instrument(skip_all)]
447 pub async fn get_post_for_edit(
448 pool: &PgPool,
449 post_id: Uuid,
450 ) -> Result<Option<PostForEdit>, sqlx::Error> {
451 sqlx::query_as::<_, PostForEdit>(
452 "SELECT p.id, p.author_id, p.body_markdown, p.created_at, p.deleted_at,
453 p.thread_id, t.title AS thread_title,
454 co.name AS community_name, co.slug AS community_slug,
455 co.id AS community_id,
456 c.name AS category_name, c.slug AS category_slug
457 FROM posts p
458 JOIN threads t ON t.id = p.thread_id
459 JOIN categories c ON c.id = t.category_id
460 JOIN communities co ON co.id = c.community_id
461 WHERE p.id = $1",
462 )
463 .bind(post_id)
464 .fetch_optional(pool)
465 .await
466 }
467
468 #[tracing::instrument(skip_all)]
469 pub async fn get_user_role(
470 pool: &PgPool,
471 user_id: Uuid,
472 community_id: Uuid,
473 ) -> Result<Option<String>, sqlx::Error> {
474 let row: Option<(String,)> = sqlx::query_as(
475 "SELECT role FROM memberships WHERE user_id = $1 AND community_id = $2",
476 )
477 .bind(user_id)
478 .bind(community_id)
479 .fetch_optional(pool)
480 .await?;
481 Ok(row.map(|r| r.0))
482 }
483
484 #[derive(sqlx::FromRow)]
485 pub struct CategoryForSettings {
486 pub id: Uuid,
487 pub name: String,
488 pub slug: String,
489 pub description: Option<String>,
490 pub sort_order: i32,
491 }
492
493 #[tracing::instrument(skip_all)]
494 pub async fn list_categories_for_settings(
495 pool: &PgPool,
496 community_id: Uuid,
497 ) -> Result<Vec<CategoryForSettings>, sqlx::Error> {
498 sqlx::query_as::<_, CategoryForSettings>(
499 "SELECT id, name, slug, description, sort_order
500 FROM categories
501 WHERE community_id = $1
502 ORDER BY sort_order",
503 )
504 .bind(community_id)
505 .fetch_all(pool)
506 .await
507 }
508
509 #[derive(sqlx::FromRow)]
510 pub struct MemberRow {
511 pub username: String,
512 pub display_name: Option<String>,
513 pub role: String,
514 pub joined_at: DateTime<Utc>,
515 }
516
517 #[tracing::instrument(skip_all)]
518 pub async fn list_community_members(
519 pool: &PgPool,
520 community_id: Uuid,
521 ) -> Result<Vec<MemberRow>, sqlx::Error> {
522 sqlx::query_as::<_, MemberRow>(
523 "SELECT u.username,
524 u.display_name,
525 m.role,
526 m.joined_at
527 FROM memberships m
528 JOIN users u ON u.mnw_account_id = m.user_id
529 WHERE m.community_id = $1
530 ORDER BY
531 CASE m.role
532 WHEN 'owner' THEN 0
533 WHEN 'moderator' THEN 1
534 WHEN 'member' THEN 2
535 ELSE 3
536 END,
537 m.joined_at",
538 )
539 .bind(community_id)
540 .fetch_all(pool)
541 .await
542 }
543
544 #[tracing::instrument(skip_all)]
545 pub async fn get_category_by_id(
546 pool: &PgPool,
547 category_id: Uuid,
548 ) -> Result<Option<CategoryForSettings>, sqlx::Error> {
549 sqlx::query_as::<_, CategoryForSettings>(
550 "SELECT id, name, slug, description, sort_order
551 FROM categories
552 WHERE id = $1",
553 )
554 .bind(category_id)
555 .fetch_optional(pool)
556 .await
557 }
558
559 // ============================================================================
560 // Footnote queries
561 // ============================================================================
562
563 /// Batch-fetch footnotes for a set of post IDs, joined with author info.
564 #[tracing::instrument(skip_all)]
565 pub async fn list_footnotes_for_posts(
566 pool: &PgPool,
567 post_ids: &[Uuid],
568 ) -> Result<Vec<FootnoteWithAuthor>, sqlx::Error> {
569 sqlx::query_as::<_, FootnoteWithAuthor>(
570 "SELECT f.id, f.post_id, f.author_id,
571 COALESCE(u.display_name, u.username) AS author_name,
572 u.username AS author_username,
573 f.body_html, f.created_at
574 FROM post_footnotes f
575 JOIN users u ON u.mnw_account_id = f.author_id
576 WHERE f.post_id = ANY($1)
577 ORDER BY f.created_at",
578 )
579 .bind(post_ids)
580 .fetch_all(pool)
581 .await
582 }
583
584 /// Count footnotes on a specific post.
585 #[tracing::instrument(skip_all)]
586 pub async fn count_footnotes_for_post(
587 pool: &PgPool,
588 post_id: Uuid,
589 ) -> Result<i64, sqlx::Error> {
590 sqlx::query_scalar("SELECT COUNT(*) FROM post_footnotes WHERE post_id = $1")
591 .bind(post_id)
592 .fetch_one(pool)
593 .await
594 }
595
596 /// Fetch a post's author_id and body_markdown for quote verification.
597 #[tracing::instrument(skip_all)]
598 pub async fn get_post_body_markdown(
599 pool: &PgPool,
600 post_id: Uuid,
601 ) -> Result<Option<(Uuid, String)>, sqlx::Error> {
602 let row: Option<(Uuid, String)> = sqlx::query_as(
603 "SELECT author_id, body_markdown FROM posts WHERE id = $1",
604 )
605 .bind(post_id)
606 .fetch_optional(pool)
607 .await?;
608 Ok(row)
609 }
610
611 // ============================================================================
612 // Ban / mute queries
613 // ============================================================================
614
615 /// Check if a user is platform-suspended (by admin).
616 #[tracing::instrument(skip_all)]
617 pub async fn is_user_suspended(
618 pool: &PgPool,
619 user_id: Uuid,
620 ) -> Result<bool, sqlx::Error> {
621 let count: i64 = sqlx::query_scalar(
622 "SELECT COUNT(*) FROM users WHERE mnw_account_id = $1 AND suspended_at IS NOT NULL",
623 )
624 .bind(user_id)
625 .fetch_one(pool)
626 .await?;
627 Ok(count > 0)
628 }
629
630 /// Check if user has an active ban in a community.
631 #[tracing::instrument(skip_all)]
632 pub async fn is_user_banned(
633 pool: &PgPool,
634 community_id: Uuid,
635 user_id: Uuid,
636 ) -> Result<bool, sqlx::Error> {
637 let count: i64 = sqlx::query_scalar(
638 "SELECT COUNT(*) FROM community_bans
639 WHERE community_id = $1 AND user_id = $2 AND ban_type = 'ban'
640 AND (expires_at IS NULL OR expires_at > now())",
641 )
642 .bind(community_id)
643 .bind(user_id)
644 .fetch_one(pool)
645 .await?;
646 Ok(count > 0)
647 }
648
649 /// Check if user has an active mute in a community.
650 #[tracing::instrument(skip_all)]
651 pub async fn is_user_muted(
652 pool: &PgPool,
653 community_id: Uuid,
654 user_id: Uuid,
655 ) -> Result<bool, sqlx::Error> {
656 let count: i64 = sqlx::query_scalar(
657 "SELECT COUNT(*) FROM community_bans
658 WHERE community_id = $1 AND user_id = $2 AND ban_type = 'mute'
659 AND (expires_at IS NULL OR expires_at > now())",
660 )
661 .bind(community_id)
662 .bind(user_id)
663 .fetch_one(pool)
664 .await?;
665 Ok(count > 0)
666 }
667
668 #[derive(sqlx::FromRow)]
669 pub struct CommunityBanRow {
670 pub id: Uuid,
671 pub user_id: Uuid,
672 pub username: String,
673 pub display_name: Option<String>,
674 pub ban_type: String,
675 pub reason: Option<String>,
676 pub expires_at: Option<DateTime<Utc>>,
677 pub created_at: DateTime<Utc>,
678 pub banned_by_username: String,
679 }
680
681 /// List all active bans and mutes in a community.
682 #[tracing::instrument(skip_all)]
683 pub async fn list_community_bans(
684 pool: &PgPool,
685 community_id: Uuid,
686 ) -> Result<Vec<CommunityBanRow>, sqlx::Error> {
687 sqlx::query_as::<_, CommunityBanRow>(
688 "SELECT cb.id, cb.user_id,
689 u.username, u.display_name,
690 cb.ban_type, cb.reason, cb.expires_at, cb.created_at,
691 actor.username AS banned_by_username
692 FROM community_bans cb
693 JOIN users u ON u.mnw_account_id = cb.user_id
694 JOIN users actor ON actor.mnw_account_id = cb.banned_by
695 WHERE cb.community_id = $1
696 AND (cb.expires_at IS NULL OR cb.expires_at > now())
697 ORDER BY cb.created_at DESC",
698 )
699 .bind(community_id)
700 .fetch_all(pool)
701 .await
702 }
703
704 #[derive(sqlx::FromRow)]
705 pub struct ModLogEntry {
706 pub id: Uuid,
707 pub actor_username: String,
708 pub action: String,
709 pub target_username: Option<String>,
710 pub reason: Option<String>,
711 pub created_at: DateTime<Utc>,
712 }
713
714 /// List mod log entries for a community, paginated, newest first.
715 #[tracing::instrument(skip_all)]
716 pub async fn list_mod_log(
717 pool: &PgPool,
718 community_id: Uuid,
719 limit: i64,
720 offset: i64,
721 ) -> Result<Vec<ModLogEntry>, sqlx::Error> {
722 sqlx::query_as::<_, ModLogEntry>(
723 "SELECT ml.id,
724 actor.username AS actor_username,
725 ml.action,
726 target.username AS target_username,
727 ml.reason,
728 ml.created_at
729 FROM mod_log ml
730 JOIN users actor ON actor.mnw_account_id = ml.actor_id
731 LEFT JOIN users target ON target.mnw_account_id = ml.target_user
732 WHERE ml.community_id = $1
733 ORDER BY ml.created_at DESC
734 LIMIT $2 OFFSET $3",
735 )
736 .bind(community_id)
737 .bind(limit)
738 .bind(offset)
739 .fetch_all(pool)
740 .await
741 }
742
743 /// Count mod log entries for a community.
744 #[tracing::instrument(skip_all)]
745 pub async fn count_mod_log(
746 pool: &PgPool,
747 community_id: Uuid,
748 ) -> Result<i64, sqlx::Error> {
749 sqlx::query_scalar(
750 "SELECT COUNT(*) FROM mod_log WHERE community_id = $1",
751 )
752 .bind(community_id)
753 .fetch_one(pool)
754 .await
755 }
756
757 /// Look up a user's UUID by username.
758 #[tracing::instrument(skip_all)]
759 pub async fn get_user_by_username(
760 pool: &PgPool,
761 username: &str,
762 ) -> Result<Option<Uuid>, sqlx::Error> {
763 let row: Option<(Uuid,)> = sqlx::query_as(
764 "SELECT mnw_account_id FROM users WHERE username = $1",
765 )
766 .bind(username)
767 .fetch_optional(pool)
768 .await?;
769 Ok(row.map(|r| r.0))
770 }
771
772 // ============================================================================
773 // User profile queries
774 // ============================================================================
775
776 #[derive(sqlx::FromRow)]
777 pub struct UserProfileRow {
778 pub user_id: Uuid,
779 pub username: String,
780 pub display_name: Option<String>,
781 pub avatar_url: Option<String>,
782 pub role: String,
783 pub joined_at: DateTime<Utc>,
784 pub post_count: i64,
785 pub endorsement_count: i64,
786 }
787
788 /// Fetch a user's profile within a specific community.
789 /// Returns None if the user is not a member of the community.
790 #[tracing::instrument(skip_all)]
791 pub async fn get_user_profile_in_community(
792 pool: &PgPool,
793 community_slug: &str,
794 username: &str,
795 ) -> Result<Option<UserProfileRow>, sqlx::Error> {
796 sqlx::query_as::<_, UserProfileRow>(
797 "SELECT u.mnw_account_id AS user_id,
798 u.username,
799 u.display_name,
800 u.avatar_url,
801 m.role,
802 m.joined_at,
803 (SELECT COUNT(*) FROM posts p
804 JOIN threads t ON t.id = p.thread_id
805 JOIN categories c ON c.id = t.category_id
806 WHERE p.author_id = u.mnw_account_id
807 AND c.community_id = co.id
808 AND p.deleted_at IS NULL
809 AND t.deleted_at IS NULL) AS post_count,
810 (SELECT COUNT(*) FROM post_endorsements pe
811 JOIN posts p ON p.id = pe.post_id
812 JOIN threads t ON t.id = p.thread_id
813 JOIN categories c ON c.id = t.category_id
814 WHERE p.author_id = u.mnw_account_id
815 AND c.community_id = co.id
816 AND p.deleted_at IS NULL
817 AND t.deleted_at IS NULL) AS endorsement_count
818 FROM users u
819 JOIN memberships m ON m.user_id = u.mnw_account_id
820 JOIN communities co ON co.id = m.community_id
821 WHERE co.slug = $1 AND u.username = $2",
822 )
823 .bind(community_slug)
824 .bind(username)
825 .fetch_optional(pool)
826 .await
827 }
828
829 #[derive(sqlx::FromRow)]
830 pub struct UserActivityRow {
831 pub thread_id: Uuid,
832 pub thread_title: String,
833 pub category_name: String,
834 pub category_slug: String,
835 pub post_created_at: DateTime<Utc>,
836 pub is_thread_author: bool,
837 }
838
839 /// Fetch a user's recent activity (posts) within a community.
840 #[tracing::instrument(skip_all)]
841 pub async fn get_user_activity_in_community(
842 pool: &PgPool,
843 community_id: Uuid,
844 user_id: Uuid,
845 limit: i64,
846 ) -> Result<Vec<UserActivityRow>, sqlx::Error> {
847 sqlx::query_as::<_, UserActivityRow>(
848 "SELECT t.id AS thread_id,
849 t.title AS thread_title,
850 c.name AS category_name,
851 c.slug AS category_slug,
852 p.created_at AS post_created_at,
853 (t.author_id = $2) AS is_thread_author
854 FROM posts p
855 JOIN threads t ON t.id = p.thread_id
856 JOIN categories c ON c.id = t.category_id
857 WHERE c.community_id = $1
858 AND p.author_id = $2
859 AND p.deleted_at IS NULL
860 AND t.deleted_at IS NULL
861 ORDER BY p.created_at DESC
862 LIMIT $3",
863 )
864 .bind(community_id)
865 .bind(user_id)
866 .bind(limit)
867 .fetch_all(pool)
868 .await
869 }
870
871 #[derive(sqlx::FromRow, serde::Serialize)]
872 pub struct UserMembershipSummary {
873 pub community_name: String,
874 pub community_slug: String,
875 pub role: String,
876 pub joined_at: DateTime<Utc>,
877 pub post_count: i64,
878 }
879
880 /// Fetch all community memberships for a user with post counts.
881 #[tracing::instrument(skip_all)]
882 pub async fn get_user_membership_summary(
883 pool: &PgPool,
884 user_id: Uuid,
885 ) -> Result<Vec<UserMembershipSummary>, sqlx::Error> {
886 sqlx::query_as::<_, UserMembershipSummary>(
887 "SELECT co.name AS community_name,
888 co.slug AS community_slug,
889 m.role,
890 m.joined_at,
891 (SELECT COUNT(*) FROM posts p
892 JOIN threads t ON t.id = p.thread_id
893 JOIN categories c ON c.id = t.category_id
894 WHERE p.author_id = $1
895 AND c.community_id = co.id
896 AND p.deleted_at IS NULL
897 AND t.deleted_at IS NULL) AS post_count
898 FROM memberships m
899 JOIN communities co ON co.id = m.community_id
900 WHERE m.user_id = $1
901 AND co.suspended_at IS NULL
902 ORDER BY co.name",
903 )
904 .bind(user_id)
905 .fetch_all(pool)
906 .await
907 }
908
909 // ============================================================================
910 // Admin queries
911 // ============================================================================
912
913 #[derive(sqlx::FromRow)]
914 pub struct AdminCommunityRow {
915 pub id: Uuid,
916 pub name: String,
917 pub slug: String,
918 pub suspended_at: Option<DateTime<Utc>>,
919 pub suspension_reason: Option<String>,
920 }
921
922 /// List all communities for the admin dashboard.
923 #[tracing::instrument(skip_all)]
924 pub async fn list_all_communities(
925 pool: &PgPool,
926 ) -> Result<Vec<AdminCommunityRow>, sqlx::Error> {
927 sqlx::query_as::<_, AdminCommunityRow>(
928 "SELECT id, name, slug, suspended_at, suspension_reason
929 FROM communities ORDER BY name",
930 )
931 .fetch_all(pool)
932 .await
933 }
934
935 #[derive(sqlx::FromRow)]
936 pub struct AdminUserRow {
937 pub id: Uuid,
938 pub username: String,
939 pub display_name: Option<String>,
940 pub suspended_at: Option<DateTime<Utc>>,
941 pub suspension_reason: Option<String>,
942 }
943
944 /// Search users by username prefix for the admin dashboard.
945 #[tracing::instrument(skip_all)]
946 pub async fn search_users(
947 pool: &PgPool,
948 query: &str,
949 ) -> Result<Vec<AdminUserRow>, sqlx::Error> {
950 let escaped = query
951 .replace('\\', "\\\\")
952 .replace('%', "\\%")
953 .replace('_', "\\_");
954 sqlx::query_as::<_, AdminUserRow>(
955 "SELECT mnw_account_id AS id, username, display_name, suspended_at, suspension_reason
956 FROM users
957 WHERE username ILIKE $1 ESCAPE '\\'
958 ORDER BY username
959 LIMIT 50",
960 )
961 .bind(format!("{escaped}%"))
962 .fetch_all(pool)
963 .await
964 }
965
966 // ============================================================================
967 // Link preview queries
968 // ============================================================================
969
970 #[derive(sqlx::FromRow)]
971 pub struct LinkPreviewRow {
972 pub post_id: Uuid,
973 pub url: String,
974 pub title: Option<String>,
975 pub description: Option<String>,
976 }
977
978 /// Batch-fetch link previews for a set of post IDs.
979 #[tracing::instrument(skip_all)]
980 pub async fn list_link_previews_for_posts(
981 pool: &PgPool,
982 post_ids: &[Uuid],
983 ) -> Result<Vec<LinkPreviewRow>, sqlx::Error> {
984 sqlx::query_as::<_, LinkPreviewRow>(
985 "SELECT post_id, url, title, description
986 FROM link_previews
987 WHERE post_id = ANY($1)
988 ORDER BY fetched_at",
989 )
990 .bind(post_ids)
991 .fetch_all(pool)
992 .await
993 }
994
995 // ============================================================================
996 // Mention queries
997 // ============================================================================
998
999 /// Batch-check which threads have at least one mention of the given user.
1000 #[tracing::instrument(skip_all)]
1001 pub async fn get_threads_with_mentions_for_user(
1002 pool: &PgPool,
1003 user_id: Uuid,
1004 thread_ids: &[Uuid],
1005 ) -> Result<Vec<Uuid>, sqlx::Error> {
1006 if thread_ids.is_empty() {
1007 return Ok(Vec::new());
1008 }
1009 sqlx::query_scalar(
1010 "SELECT DISTINCT p.thread_id
1011 FROM post_mentions pm
1012 JOIN posts p ON p.id = pm.post_id
1013 WHERE pm.mentioned_user_id = $1
1014 AND p.thread_id = ANY($2)",
1015 )
1016 .bind(user_id)
1017 .bind(thread_ids)
1018 .fetch_all(pool)
1019 .await
1020 }
1021
1022 /// Resolve usernames to user IDs, filtered to community members.
1023 #[tracing::instrument(skip_all)]
1024 pub async fn resolve_usernames_in_community(
1025 pool: &PgPool,
1026 community_id: Uuid,
1027 usernames: &[String],
1028 ) -> Result<std::collections::HashMap<String, Uuid>, sqlx::Error> {
1029 if usernames.is_empty() {
1030 return Ok(std::collections::HashMap::new());
1031 }
1032 let rows: Vec<(String, Uuid)> = sqlx::query_as(
1033 "SELECT u.username, u.mnw_account_id
1034 FROM users u
1035 WHERE u.username = ANY($1)
1036 AND u.mnw_account_id IN (SELECT user_id FROM memberships WHERE community_id = $2)",
1037 )
1038 .bind(usernames)
1039 .bind(community_id)
1040 .fetch_all(pool)
1041 .await?;
1042 Ok(rows.into_iter().collect())
1043 }
1044
1045 // ============================================================================
1046 // Endorsement queries
1047 // ============================================================================
1048
1049 #[derive(sqlx::FromRow)]
1050 pub struct EndorsementRow {
1051 pub post_id: Uuid,
1052 pub endorser_id: Uuid,
1053 }
1054
1055 // ============================================================================
1056 // Tracked thread queries
1057 // ============================================================================
1058
1059 /// Check if a user is tracking a specific thread.
1060 #[tracing::instrument(skip_all)]
1061 pub async fn is_thread_tracked(
1062 pool: &PgPool,
1063 user_id: Uuid,
1064 thread_id: Uuid,
1065 ) -> Result<bool, sqlx::Error> {
1066 let count: i64 = sqlx::query_scalar(
1067 "SELECT COUNT(*) FROM tracked_threads WHERE user_id = $1 AND thread_id = $2",
1068 )
1069 .bind(user_id)
1070 .bind(thread_id)
1071 .fetch_one(pool)
1072 .await?;
1073 Ok(count > 0)
1074 }
1075
1076 #[derive(sqlx::FromRow)]
1077 pub struct TrackedThreadRow {
1078 pub thread_id: Uuid,
1079 pub thread_title: String,
1080 pub community_name: String,
1081 pub community_slug: String,
1082 pub category_slug: String,
1083 pub unread_count: i64,
1084 pub has_mention: bool,
1085 pub tracked_at: DateTime<Utc>,
1086 }
1087
1088 /// List a user's tracked threads with unread post counts.
1089 #[tracing::instrument(skip_all)]
1090 pub async fn list_tracked_threads(
1091 pool: &PgPool,
1092 user_id: Uuid,
1093 ) -> Result<Vec<TrackedThreadRow>, sqlx::Error> {
1094 sqlx::query_as::<_, TrackedThreadRow>(
1095 "SELECT tt.thread_id,
1096 t.title AS thread_title,
1097 co.name AS community_name,
1098 co.slug AS community_slug,
1099 cat.slug AS category_slug,
1100 (SELECT COUNT(*) FROM posts p
1101 WHERE p.thread_id = tt.thread_id
1102 AND (tt.last_read_post_id IS NULL OR p.created_at > (
1103 SELECT created_at FROM posts WHERE id = tt.last_read_post_id
1104 ))
1105 ) AS unread_count,
1106 EXISTS (
1107 SELECT 1 FROM post_mentions pm
1108 JOIN posts p ON p.id = pm.post_id
1109 WHERE pm.mentioned_user_id = tt.user_id
1110 AND p.thread_id = tt.thread_id
1111 ) AS has_mention,
1112 tt.tracked_at
1113 FROM tracked_threads tt
1114 JOIN threads t ON t.id = tt.thread_id
1115 JOIN categories cat ON cat.id = t.category_id
1116 JOIN communities co ON co.id = cat.community_id
1117 WHERE tt.user_id = $1 AND t.deleted_at IS NULL AND co.suspended_at IS NULL
1118 ORDER BY t.last_activity_at DESC",
1119 )
1120 .bind(user_id)
1121 .fetch_all(pool)
1122 .await
1123 }
1124
1125 // ============================================================================
1126 // Tag queries
1127 // ============================================================================
1128
1129 #[derive(sqlx::FromRow)]
1130 pub struct TagRow {
1131 pub id: Uuid,
1132 pub name: String,
1133 pub slug: String,
1134 }
1135
1136 /// List all tags for a community.
1137 #[tracing::instrument(skip_all)]
1138 pub async fn list_tags_for_community(
1139 pool: &PgPool,
1140 community_id: Uuid,
1141 ) -> Result<Vec<TagRow>, sqlx::Error> {
1142 sqlx::query_as::<_, TagRow>(
1143 "SELECT id, name, slug FROM tags WHERE community_id = $1 ORDER BY name",
1144 )
1145 .bind(community_id)
1146 .fetch_all(pool)
1147 .await
1148 }
1149
1150 #[derive(sqlx::FromRow)]
1151 pub struct ThreadTagRow {
1152 pub thread_id: Uuid,
1153 pub tag_name: String,
1154 pub tag_slug: String,
1155 }
1156
1157 /// Batch-fetch tags for a set of thread IDs.
1158 #[tracing::instrument(skip_all)]
1159 pub async fn list_tags_for_threads(
1160 pool: &PgPool,
1161 thread_ids: &[Uuid],
1162 ) -> Result<Vec<ThreadTagRow>, sqlx::Error> {
1163 sqlx::query_as::<_, ThreadTagRow>(
1164 "SELECT tt.thread_id, t.name AS tag_name, t.slug AS tag_slug
1165 FROM thread_tags tt
1166 JOIN tags t ON t.id = tt.tag_id
1167 WHERE tt.thread_id = ANY($1)
1168 ORDER BY t.name",
1169 )
1170 .bind(thread_ids)
1171 .fetch_all(pool)
1172 .await
1173 }
1174
1175 /// Count threads in a category, optionally filtered by tag slug.
1176 #[tracing::instrument(skip_all)]
1177 pub async fn count_threads_in_category_filtered(
1178 pool: &PgPool,
1179 community_slug: &str,
1180 category_slug: &str,
1181 tag_slug: Option<&str>,
1182 ) -> Result<i64, sqlx::Error> {
1183 if let Some(tag) = tag_slug {
1184 sqlx::query_scalar(
1185 "SELECT COUNT(DISTINCT t.id)
1186 FROM threads t
1187 JOIN categories c ON c.id = t.category_id
1188 JOIN communities co ON co.id = c.community_id
1189 JOIN thread_tags tt ON tt.thread_id = t.id
1190 JOIN tags tg ON tg.id = tt.tag_id AND tg.slug = $3 AND tg.community_id = co.id
1191 WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL",
1192 )
1193 .bind(community_slug)
1194 .bind(category_slug)
1195 .bind(tag)
1196 .fetch_one(pool)
1197 .await
1198 } else {
1199 count_threads_in_category(pool, community_slug, category_slug).await
1200 }
1201 }
1202
1203 /// List threads with sorting, optionally filtered by tag slug.
1204 #[tracing::instrument(skip_all)]
1205 #[allow(clippy::too_many_arguments)]
1206 pub async fn list_threads_in_category_sorted_filtered(
1207 pool: &PgPool,
1208 community_slug: &str,
1209 category_slug: &str,
1210 sort: &str,
1211 order: &str,
1212 limit: i64,
1213 offset: i64,
1214 tag_slug: Option<&str>,
1215 ) -> Result<Vec<ThreadWithMeta>, sqlx::Error> {
1216 if tag_slug.is_none() {
1217 return list_threads_in_category_sorted(pool, community_slug, category_slug, sort, order, limit, offset).await;
1218 }
1219
1220 let tag = tag_slug.unwrap();
1221 let order_clause = match (sort, order) {
1222 ("replies", "asc") => "ORDER BY t.pinned DESC, reply_count ASC, t.last_activity_at DESC",
1223 ("replies", _) => "ORDER BY t.pinned DESC, reply_count DESC, t.last_activity_at DESC",
1224 (_, "asc") => "ORDER BY t.pinned DESC, t.last_activity_at ASC",
1225 _ => "ORDER BY t.pinned DESC, t.last_activity_at DESC",
1226 };
1227
1228 let query = format!(
1229 "SELECT t.id, t.title,
1230 COALESCE(u.display_name, u.username) AS author_name,
1231 u.username AS author_username,
1232 (COUNT(p.id) - 1) AS reply_count,
1233 t.last_activity_at,
1234 t.pinned, t.locked
1235 FROM threads t
1236 JOIN categories c ON c.id = t.category_id
1237 JOIN communities co ON co.id = c.community_id
1238 JOIN users u ON u.mnw_account_id = t.author_id
1239 LEFT JOIN posts p ON p.thread_id = t.id
1240 JOIN thread_tags tt ON tt.thread_id = t.id
1241 JOIN tags tg ON tg.id = tt.tag_id AND tg.slug = $3 AND tg.community_id = co.id
1242 WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL
1243 GROUP BY t.id, t.title, u.display_name, u.username,
1244 t.last_activity_at, t.pinned, t.locked
1245 {order_clause}
1246 LIMIT $4 OFFSET $5"
1247 );
1248
1249 sqlx::query_as::<_, ThreadWithMeta>(&query)
1250 .bind(community_slug)
1251 .bind(category_slug)
1252 .bind(tag)
1253 .bind(limit)
1254 .bind(offset)
1255 .fetch_all(pool)
1256 .await
1257 }
1258
1259 // ============================================================================
1260 // Flag queries
1261 // ============================================================================
1262
1263 #[derive(sqlx::FromRow)]
1264 pub struct PendingFlagRow {
1265 pub flag_id: Uuid,
1266 pub post_id: Uuid,
1267 pub thread_id: Uuid,
1268 pub thread_title: String,
1269 pub category_slug: String,
1270 pub flagger_username: String,
1271 pub reason: String,
1272 pub detail: Option<String>,
1273 pub created_at: DateTime<Utc>,
1274 }
1275
1276 /// List unresolved flags in a community, newest first.
1277 #[tracing::instrument(skip_all)]
1278 pub async fn list_pending_flags(
1279 pool: &PgPool,
1280 community_id: Uuid,
1281 ) -> Result<Vec<PendingFlagRow>, sqlx::Error> {
1282 sqlx::query_as::<_, PendingFlagRow>(
1283 "SELECT f.id AS flag_id, f.post_id, p.thread_id,
1284 t.title AS thread_title,
1285 cat.slug AS category_slug,
1286 u.username AS flagger_username,
1287 f.reason, f.detail, f.created_at
1288 FROM post_flags f
1289 JOIN posts p ON p.id = f.post_id
1290 JOIN threads t ON t.id = p.thread_id
1291 JOIN categories cat ON cat.id = t.category_id
1292 JOIN users u ON u.mnw_account_id = f.flagger_id
1293 WHERE cat.community_id = $1 AND f.resolved_at IS NULL
1294 ORDER BY f.created_at DESC",
1295 )
1296 .bind(community_id)
1297 .fetch_all(pool)
1298 .await
1299 }
1300
1301 /// Check if a user has already flagged a specific post.
1302 #[tracing::instrument(skip_all)]
1303 pub async fn has_user_flagged_post(
1304 pool: &PgPool,
1305 post_id: Uuid,
1306 flagger_id: Uuid,
1307 ) -> Result<bool, sqlx::Error> {
1308 let count: i64 = sqlx::query_scalar(
1309 "SELECT COUNT(*) FROM post_flags WHERE post_id = $1 AND flagger_id = $2",
1310 )
1311 .bind(post_id)
1312 .bind(flagger_id)
1313 .fetch_one(pool)
1314 .await?;
1315 Ok(count > 0)
1316 }
1317
1318
1319 // ============================================================================
1320 // Endorsement queries
1321 // ============================================================================
1322
1323 // ============================================================================
1324 // Search queries
1325 // ============================================================================
1326
1327 #[derive(sqlx::FromRow)]
1328 pub struct SearchResultRow {
1329 pub thread_id: Uuid,
1330 pub thread_title: String,
1331 pub author_username: String,
1332 pub community_name: String,
1333 pub community_slug: String,
1334 pub category_name: String,
1335 pub category_slug: String,
1336 pub snippet: String,
1337 pub last_activity_at: DateTime<Utc>,
1338 pub rank: f64,
1339 }
1340
1341 /// Full-text search across threads and posts. Combines tsvector ranking with
1342 /// trigram similarity for typo tolerance. Title matches ranked above body.
1343 /// Optionally scoped to a single community by slug.
1344 #[tracing::instrument(skip_all)]
1345 pub async fn search_threads(
1346 pool: &PgPool,
1347 query: &str,
1348 community_slug: Option<&str>,
1349 limit: i64,
1350 ) -> Result<Vec<SearchResultRow>, sqlx::Error> {
1351 sqlx::query_as::<_, SearchResultRow>(
1352 "WITH thread_matches AS (
1353 SELECT t.id AS thread_id,
1354 t.title AS thread_title,
1355 COALESCE(u.display_name, u.username) AS author_username,
1356 co.name AS community_name,
1357 co.slug AS community_slug,
1358 c.name AS category_name,
1359 c.slug AS category_slug,
1360 LEFT(t.title, 200) AS snippet,
1361 t.last_activity_at,
1362 (ts_rank(t.search_tsv, websearch_to_tsquery('english', $1)) * 2.0
1363 + similarity(t.title, $2)) AS rank
1364 FROM threads t
1365 JOIN categories c ON c.id = t.category_id
1366 JOIN communities co ON co.id = c.community_id
1367 JOIN users u ON u.mnw_account_id = t.author_id
1368 WHERE t.deleted_at IS NULL
1369 AND co.suspended_at IS NULL
1370 AND (t.search_tsv @@ websearch_to_tsquery('english', $1)
1371 OR similarity(t.title, $2) > 0.1)
1372 AND ($3::text IS NULL OR co.slug = $3)
1373 ),
1374 post_matches AS (
1375 SELECT DISTINCT ON (t.id)
1376 t.id AS thread_id,
1377 t.title AS thread_title,
1378 COALESCE(pu.display_name, pu.username) AS author_username,
1379 co.name AS community_name,
1380 co.slug AS community_slug,
1381 c.name AS category_name,
1382 c.slug AS category_slug,
1383 LEFT(p.body_markdown, 200) AS snippet,
1384 t.last_activity_at,
1385 ts_rank(p.search_tsv, websearch_to_tsquery('english', $1)) AS rank
1386 FROM posts p
1387 JOIN threads t ON t.id = p.thread_id
1388 JOIN categories c ON c.id = t.category_id
1389 JOIN communities co ON co.id = c.community_id
1390 JOIN users pu ON pu.mnw_account_id = t.author_id
1391 WHERE t.deleted_at IS NULL
1392 AND co.suspended_at IS NULL
1393 AND p.removed_at IS NULL
1394 AND p.search_tsv @@ websearch_to_tsquery('english', $1)
1395 AND ($3::text IS NULL OR co.slug = $3)
1396 AND t.id NOT IN (SELECT thread_id FROM thread_matches)
1397 ORDER BY t.id, ts_rank(p.search_tsv, websearch_to_tsquery('english', $1)) DESC
1398 )
1399 SELECT * FROM thread_matches
1400 UNION ALL
1401 SELECT * FROM post_matches
1402 ORDER BY rank DESC, last_activity_at DESC
1403 LIMIT $4",
1404 )
1405 .bind(query)
1406 .bind(query)
1407 .bind(community_slug)
1408 .bind(limit)
1409 .fetch_all(pool)
1410 .await
1411 }
1412
1413 // ============================================================================
1414 // Endorsement read queries
1415 // ============================================================================
1416
1417 /// Batch-fetch endorsements for a set of post IDs.
1418 #[tracing::instrument(skip_all)]
1419 pub async fn list_endorsements_for_posts(
1420 pool: &PgPool,
1421 post_ids: &[Uuid],
1422 ) -> Result<Vec<EndorsementRow>, sqlx::Error> {
1423 sqlx::query_as::<_, EndorsementRow>(
1424 "SELECT post_id, endorser_id FROM post_endorsements WHERE post_id = ANY($1)",
1425 )
1426 .bind(post_ids)
1427 .fetch_all(pool)
1428 .await
1429 }
1430
1431 // ============================================================================
1432 // Image queries
1433 // ============================================================================
1434
1435 #[derive(sqlx::FromRow)]
1436 pub struct ImageRow {
1437 pub id: Uuid,
1438 pub s3_key: String,
1439 pub content_type: String,
1440 pub removed_at: Option<DateTime<Utc>>,
1441 }
1442
1443 /// Fetch an image by ID (for serving).
1444 #[tracing::instrument(skip_all)]
1445 pub async fn get_image(
1446 pool: &PgPool,
1447 image_id: Uuid,
1448 ) -> Result<Option<ImageRow>, sqlx::Error> {
1449 sqlx::query_as::<_, ImageRow>(
1450 "SELECT id, s3_key, content_type, removed_at FROM images WHERE id = $1",
1451 )
1452 .bind(image_id)
1453 .fetch_optional(pool)
1454 .await
1455 }
1456
1457 /// Get the maximum sort_order among categories in a community. Returns -1 if no categories exist.
1458 #[tracing::instrument(skip_all)]
1459 pub async fn get_max_category_order(
1460 pool: &PgPool,
1461 community_id: Uuid,
1462 ) -> Result<i32, sqlx::Error> {
1463 let row: (Option<i32>,) = sqlx::query_as(
1464 "SELECT MAX(sort_order) FROM categories WHERE community_id = $1",
1465 )
1466 .bind(community_id)
1467 .fetch_one(pool)
1468 .await?;
1469 Ok(row.0.unwrap_or(-1))
1470 }
1471
1472 /// Check if a thread exists by ID.
1473 #[tracing::instrument(skip_all)]
1474 pub async fn thread_exists(
1475 pool: &PgPool,
1476 thread_id: Uuid,
1477 ) -> Result<bool, sqlx::Error> {
1478 let count: i64 = sqlx::query_scalar(
1479 "SELECT COUNT(*) FROM threads WHERE id = $1",
1480 )
1481 .bind(thread_id)
1482 .fetch_one(pool)
1483 .await?;
1484 Ok(count > 0)
1485 }
1486
1487 /// Count images uploaded by a user in the last N seconds (rate limiting).
1488 #[tracing::instrument(skip_all)]
1489 pub async fn count_recent_uploads_by_user(
1490 pool: &PgPool,
1491 user_id: Uuid,
1492 window_secs: i64,
1493 ) -> Result<i64, sqlx::Error> {
1494 sqlx::query_scalar(
1495 "SELECT COUNT(*) FROM images
1496 WHERE uploader_id = $1
1497 AND created_at > now() - make_interval(secs => $2::float8)",
1498 )
1499 .bind(user_id)
1500 .bind(window_secs as f64)
1501 .fetch_one(pool)
1502 .await
1503 }
1504