Skip to main content

max / makenotwork

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