Skip to main content

max / makenotwork

8.4 KB · 255 lines History Blame Raw
1 use super::{DateTime, PgPool, SortColumn, SortOrder, Utc, Uuid};
2
3 #[derive(sqlx::FromRow)]
4 pub struct ThreadWithMeta {
5 pub id: Uuid,
6 pub title: String,
7 pub author_name: String,
8 pub author_username: String,
9 pub reply_count: i64,
10 pub last_activity_at: DateTime<Utc>,
11 pub pinned: bool,
12 pub locked: bool,
13 }
14
15 #[derive(sqlx::FromRow)]
16 pub struct ThreadWithBreadcrumb {
17 pub id: Uuid,
18 pub title: String,
19 pub locked: bool,
20 pub pinned: bool,
21 pub author_id: Uuid,
22 pub community_id: Uuid,
23 pub community_name: String,
24 pub community_slug: String,
25 pub category_name: String,
26 pub category_slug: String,
27 }
28
29 /// Look up a thread by its external reference (e.g., "mnw:item:uuid").
30 #[tracing::instrument(skip_all)]
31 pub async fn get_thread_by_external_ref(
32 pool: &PgPool,
33 external_ref: &str,
34 ) -> Result<Option<(Uuid,)>, sqlx::Error> {
35 sqlx::query!(
36 "SELECT id FROM threads WHERE external_ref = $1",
37 external_ref,
38 )
39 .fetch_optional(pool)
40 .await
41 .map(|opt| opt.map(|r| (r.id,)))
42 }
43
44 /// Get thread stats: post count and last activity timestamp.
45 #[tracing::instrument(skip_all)]
46 #[allow(clippy::type_complexity)]
47 pub async fn get_thread_stats(
48 pool: &PgPool,
49 thread_id: Uuid,
50 ) -> Result<Option<(i64, Option<DateTime<Utc>>)>, sqlx::Error> {
51 // This count surfaces on the MNW server (the internal /stats endpoint), which
52 // can't render mt's tombstones, so it must be the canonical *active* count,
53 // excluding mod-removed and soft-deleted posts (matching the m029 trigger and
54 // the profile tallies). Contrast `count_posts_in_thread`, which deliberately
55 // counts tombstones because the in-app thread list renders them.
56 sqlx::query!(
57 r#"SELECT COUNT(p.id) AS "count!", MAX(p.created_at) AS "max_created: chrono::DateTime<chrono::Utc>"
58 FROM posts p
59 WHERE p.thread_id = $1
60 AND p.removed_at IS NULL
61 AND p.deleted_at IS NULL"#,
62 thread_id,
63 )
64 .fetch_optional(pool)
65 .await
66 .map(|opt| opt.map(|r| (r.count, r.max_created)))
67 }
68
69 #[tracing::instrument(skip_all)]
70 pub async fn list_threads_in_category_paginated(
71 pool: &PgPool,
72 community_slug: &str,
73 category_slug: &str,
74 limit: i64,
75 offset: i64,
76 ) -> Result<Vec<ThreadWithMeta>, sqlx::Error> {
77 sqlx::query_as!(
78 ThreadWithMeta,
79 r#"SELECT t.id, t.title,
80 COALESCE(u.display_name, u.username) AS "author_name!",
81 u.username AS author_username,
82 GREATEST(t.post_count - 1, 0)::BIGINT AS "reply_count!",
83 t.last_activity_at AS "last_activity_at: chrono::DateTime<chrono::Utc>",
84 t.pinned, t.locked
85 FROM threads t
86 JOIN categories c ON c.id = t.category_id
87 JOIN communities co ON co.id = c.community_id
88 JOIN users u ON u.mnw_account_id = t.author_id
89 WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL
90 ORDER BY t.pinned DESC, t.last_activity_at DESC
91 LIMIT $3 OFFSET $4"#,
92 community_slug,
93 category_slug,
94 limit,
95 offset,
96 )
97 .fetch_all(pool)
98 .await
99 }
100
101 /// List threads in a category, sorted, optionally filtered to one tag.
102 ///
103 /// Pinned threads always sort first. `sort`/`order` are typed enums, so the
104 /// `ORDER BY` clause is chosen from an exhaustive match of fixed literals (no
105 /// user string reaches the SQL); when `tag_slug` is `Some`, an extra join
106 /// restricts the results to threads carrying that tag in the same community.
107 #[tracing::instrument(skip_all)]
108 #[allow(clippy::too_many_arguments)]
109 pub async fn list_threads_in_category_sorted(
110 pool: &PgPool,
111 community_slug: &str,
112 category_slug: &str,
113 sort: SortColumn,
114 order: SortOrder,
115 limit: i64,
116 offset: i64,
117 tag_slug: Option<&str>,
118 ) -> Result<Vec<ThreadWithMeta>, sqlx::Error> {
119 let order_clause = match (sort, order) {
120 (SortColumn::Replies, SortOrder::Asc) => {
121 "ORDER BY t.pinned DESC, reply_count ASC, t.last_activity_at DESC"
122 }
123 (SortColumn::Replies, SortOrder::Desc) => {
124 "ORDER BY t.pinned DESC, reply_count DESC, t.last_activity_at DESC"
125 }
126 (SortColumn::Activity, SortOrder::Asc) => "ORDER BY t.pinned DESC, t.last_activity_at ASC",
127 (SortColumn::Activity, SortOrder::Desc) => {
128 "ORDER BY t.pinned DESC, t.last_activity_at DESC"
129 }
130 };
131
132 // When a tag filter is present it binds as $3, pushing limit/offset to $4/$5.
133 let (tag_join, limit_ph, offset_ph) = if tag_slug.is_some() {
134 (
135 "JOIN thread_tags tt ON tt.thread_id = t.id \
136 JOIN tags tg ON tg.id = tt.tag_id AND tg.slug = $3 AND tg.community_id = co.id",
137 "$4",
138 "$5",
139 )
140 } else {
141 ("", "$3", "$4")
142 };
143
144 let query = format!(
145 "SELECT t.id, t.title,
146 COALESCE(u.display_name, u.username) AS author_name,
147 u.username AS author_username,
148 GREATEST(t.post_count - 1, 0)::BIGINT AS reply_count,
149 t.last_activity_at,
150 t.pinned, t.locked
151 FROM threads t
152 JOIN categories c ON c.id = t.category_id
153 JOIN communities co ON co.id = c.community_id
154 JOIN users u ON u.mnw_account_id = t.author_id
155 {tag_join}
156 WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL
157 {order_clause}
158 LIMIT {limit_ph} OFFSET {offset_ph}"
159 );
160
161 // runtime-checked: dynamic SQL (cannot use compile-time macro)
162 let mut q = sqlx::query_as::<_, ThreadWithMeta>(&query)
163 .bind(community_slug)
164 .bind(category_slug);
165 if let Some(tag) = tag_slug {
166 q = q.bind(tag);
167 }
168 q.bind(limit).bind(offset).fetch_all(pool).await
169 }
170
171 #[tracing::instrument(skip_all)]
172 pub async fn count_threads_in_category(
173 pool: &PgPool,
174 community_slug: &str,
175 category_slug: &str,
176 ) -> Result<i64, sqlx::Error> {
177 sqlx::query_scalar!(
178 r#"SELECT COUNT(*) AS "count!"
179 FROM threads t
180 JOIN categories c ON c.id = t.category_id
181 JOIN communities co ON co.id = c.community_id
182 WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL"#,
183 community_slug,
184 category_slug,
185 )
186 .fetch_one(pool)
187 .await
188 }
189
190 #[tracing::instrument(skip_all)]
191 pub async fn get_thread_with_breadcrumb(
192 pool: &PgPool,
193 thread_id: Uuid,
194 ) -> Result<Option<super::Unscoped<ThreadWithBreadcrumb>>, sqlx::Error> {
195 sqlx::query_as!(
196 ThreadWithBreadcrumb,
197 r#"SELECT t.id, t.title, t.locked, t.pinned, t.author_id,
198 co.id AS community_id,
199 co.name AS community_name, co.slug AS community_slug,
200 c.name AS category_name, c.slug AS category_slug
201 FROM threads t
202 JOIN categories c ON c.id = t.category_id
203 JOIN communities co ON co.id = c.community_id
204 WHERE t.id = $1 AND t.deleted_at IS NULL"#,
205 thread_id,
206 )
207 .fetch_optional(pool)
208 .await
209 .map(|opt| {
210 opt.map(|row| {
211 let community_id = row.community_id;
212 super::Unscoped::new(row, community_id)
213 })
214 })
215 }
216
217 /// Count threads in a category, optionally filtered by tag slug.
218 #[tracing::instrument(skip_all)]
219 pub async fn count_threads_in_category_filtered(
220 pool: &PgPool,
221 community_slug: &str,
222 category_slug: &str,
223 tag_slug: Option<&str>,
224 ) -> Result<i64, sqlx::Error> {
225 if let Some(tag) = tag_slug {
226 sqlx::query_scalar!(
227 r#"SELECT COUNT(DISTINCT t.id) AS "count!"
228 FROM threads t
229 JOIN categories c ON c.id = t.category_id
230 JOIN communities co ON co.id = c.community_id
231 JOIN thread_tags tt ON tt.thread_id = t.id
232 JOIN tags tg ON tg.id = tt.tag_id AND tg.slug = $3 AND tg.community_id = co.id
233 WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL"#,
234 community_slug,
235 category_slug,
236 tag,
237 )
238 .fetch_one(pool)
239 .await
240 } else {
241 count_threads_in_category(pool, community_slug, category_slug).await
242 }
243 }
244
245 /// Check if a thread exists by ID.
246 #[tracing::instrument(skip_all)]
247 pub async fn thread_exists(pool: &PgPool, thread_id: Uuid) -> Result<bool, sqlx::Error> {
248 sqlx::query_scalar!(
249 r#"SELECT EXISTS(SELECT 1 FROM threads WHERE id = $1) AS "exists!""#,
250 thread_id,
251 )
252 .fetch_one(pool)
253 .await
254 }
255