Skip to main content

max / makenotwork

11.4 KB · 340 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 pub struct DeletedThreadRow {
30 pub id: Uuid,
31 pub title: String,
32 pub category_slug: String,
33 pub author_username: String,
34 pub deleted_at: DateTime<Utc>,
35 /// The thread was deleted by removing its opening post, so restoring it
36 /// brings that post back too. False when a mod deleted the thread directly
37 /// and the opening post was left alone.
38 pub op_removed: bool,
39 }
40
41 /// List a community's soft-deleted threads, newest deletion first.
42 ///
43 /// The only reader of `threads` that wants `deleted_at IS NOT NULL`: every
44 /// other loader filters deleted threads out, which is exactly why they need a
45 /// surface of their own to be restored from. Capped by `limit` like the other
46 /// moderation reads so a community with a long deletion history cannot make one
47 /// page load materialize an unbounded set.
48 #[tracing::instrument(skip_all)]
49 pub async fn list_deleted_threads(
50 pool: &PgPool,
51 community_id: Uuid,
52 limit: i64,
53 ) -> Result<Vec<DeletedThreadRow>, sqlx::Error> {
54 sqlx::query_as!(
55 DeletedThreadRow,
56 r#"SELECT t.id, t.title,
57 c.slug AS category_slug,
58 u.username AS author_username,
59 t.deleted_at AS "deleted_at!: chrono::DateTime<chrono::Utc>",
60 EXISTS (
61 SELECT 1 FROM posts op
62 WHERE op.thread_id = t.id
63 AND op.removed_at IS NOT NULL
64 AND op.id = (
65 SELECT id FROM posts
66 WHERE thread_id = t.id
67 ORDER BY created_at ASC, id ASC
68 LIMIT 1
69 )
70 ) AS "op_removed!"
71 FROM threads t
72 JOIN categories c ON c.id = t.category_id
73 JOIN users u ON u.mnw_account_id = t.author_id
74 WHERE c.community_id = $1 AND t.deleted_at IS NOT NULL
75 ORDER BY t.deleted_at DESC
76 LIMIT $2"#,
77 community_id,
78 limit,
79 )
80 .fetch_all(pool)
81 .await
82 }
83
84 pub struct DeletedThreadTarget {
85 pub author_id: Uuid,
86 pub category_slug: String,
87 }
88
89 /// Resolve a soft-deleted thread within a community, for the restore handler.
90 ///
91 /// The scoping is structural, same idea as `get_post_body_markdown_in_community`:
92 /// the community id is part of the lookup, so there is no unscoped by-id variant
93 /// a caller could reach for and skip the check with. `deleted_at IS NOT NULL` is
94 /// deliberate here, this is the one path that only wants deleted rows, and it
95 /// makes restoring an already-live thread a 404 rather than a silent no-op.
96 #[tracing::instrument(skip_all)]
97 pub async fn get_deleted_thread_in_community(
98 pool: &PgPool,
99 thread_id: Uuid,
100 community_id: Uuid,
101 ) -> Result<Option<DeletedThreadTarget>, sqlx::Error> {
102 sqlx::query_as!(
103 DeletedThreadTarget,
104 "SELECT t.author_id, c.slug AS category_slug
105 FROM threads t
106 JOIN categories c ON c.id = t.category_id
107 WHERE t.id = $1 AND c.community_id = $2 AND t.deleted_at IS NOT NULL",
108 thread_id,
109 community_id,
110 )
111 .fetch_optional(pool)
112 .await
113 }
114
115 /// Look up a thread by its external reference (e.g., "mnw:item:uuid").
116 #[tracing::instrument(skip_all)]
117 pub async fn get_thread_by_external_ref(
118 pool: &PgPool,
119 external_ref: &str,
120 ) -> Result<Option<(Uuid,)>, sqlx::Error> {
121 sqlx::query!(
122 "SELECT id FROM threads WHERE external_ref = $1",
123 external_ref,
124 )
125 .fetch_optional(pool)
126 .await
127 .map(|opt| opt.map(|r| (r.id,)))
128 }
129
130 /// Get thread stats: post count and last activity timestamp.
131 #[tracing::instrument(skip_all)]
132 #[allow(clippy::type_complexity)]
133 pub async fn get_thread_stats(
134 pool: &PgPool,
135 thread_id: Uuid,
136 ) -> Result<Option<(i64, Option<DateTime<Utc>>)>, sqlx::Error> {
137 // This count surfaces on the MNW server (the internal /stats endpoint), which
138 // can't render mt's tombstones, so it must be the canonical *active* count,
139 // excluding mod-removed and soft-deleted posts (matching the m029 trigger and
140 // the profile tallies). Contrast `count_posts_in_thread`, which deliberately
141 // counts tombstones because the in-app thread list renders them.
142 sqlx::query!(
143 r#"SELECT COUNT(p.id) AS "count!", MAX(p.created_at) AS "max_created: chrono::DateTime<chrono::Utc>"
144 FROM posts p
145 WHERE p.thread_id = $1
146 AND p.is_active"#,
147 thread_id,
148 )
149 .fetch_optional(pool)
150 .await
151 .map(|opt| opt.map(|r| (r.count, r.max_created)))
152 }
153
154 #[tracing::instrument(skip_all)]
155 pub async fn list_threads_in_category_paginated(
156 pool: &PgPool,
157 community_slug: &str,
158 category_slug: &str,
159 limit: i64,
160 offset: i64,
161 ) -> Result<Vec<ThreadWithMeta>, sqlx::Error> {
162 sqlx::query_as!(
163 ThreadWithMeta,
164 r#"SELECT t.id, t.title,
165 COALESCE(u.display_name, u.username) AS "author_name!",
166 u.username AS author_username,
167 GREATEST(t.post_count - 1, 0)::BIGINT AS "reply_count!",
168 t.last_activity_at AS "last_activity_at: chrono::DateTime<chrono::Utc>",
169 t.pinned, t.locked
170 FROM threads t
171 JOIN categories c ON c.id = t.category_id
172 JOIN communities co ON co.id = c.community_id
173 JOIN users u ON u.mnw_account_id = t.author_id
174 WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL
175 ORDER BY t.pinned DESC, t.last_activity_at DESC
176 LIMIT $3 OFFSET $4"#,
177 community_slug,
178 category_slug,
179 limit,
180 offset,
181 )
182 .fetch_all(pool)
183 .await
184 }
185
186 /// List threads in a category, sorted, optionally filtered to one tag.
187 ///
188 /// Pinned threads always sort first. `sort`/`order` are typed enums, so the
189 /// `ORDER BY` clause is chosen from an exhaustive match of fixed literals (no
190 /// user string reaches the SQL); when `tag_slug` is `Some`, an extra join
191 /// restricts the results to threads carrying that tag in the same community.
192 #[tracing::instrument(skip_all)]
193 #[allow(clippy::too_many_arguments)]
194 pub async fn list_threads_in_category_sorted(
195 pool: &PgPool,
196 community_slug: &str,
197 category_slug: &str,
198 sort: SortColumn,
199 order: SortOrder,
200 limit: i64,
201 offset: i64,
202 tag_slug: Option<&str>,
203 ) -> Result<Vec<ThreadWithMeta>, sqlx::Error> {
204 let order_clause = match (sort, order) {
205 (SortColumn::Replies, SortOrder::Asc) => {
206 "ORDER BY t.pinned DESC, reply_count ASC, t.last_activity_at DESC"
207 }
208 (SortColumn::Replies, SortOrder::Desc) => {
209 "ORDER BY t.pinned DESC, reply_count DESC, t.last_activity_at DESC"
210 }
211 (SortColumn::Activity, SortOrder::Asc) => "ORDER BY t.pinned DESC, t.last_activity_at ASC",
212 (SortColumn::Activity, SortOrder::Desc) => {
213 "ORDER BY t.pinned DESC, t.last_activity_at DESC"
214 }
215 };
216
217 // When a tag filter is present it binds as $3, pushing limit/offset to $4/$5.
218 let (tag_join, limit_ph, offset_ph) = if tag_slug.is_some() {
219 (
220 "JOIN thread_tags tt ON tt.thread_id = t.id \
221 JOIN tags tg ON tg.id = tt.tag_id AND tg.slug = $3 AND tg.community_id = co.id",
222 "$4",
223 "$5",
224 )
225 } else {
226 ("", "$3", "$4")
227 };
228
229 let query = format!(
230 "SELECT t.id, t.title,
231 COALESCE(u.display_name, u.username) AS author_name,
232 u.username AS author_username,
233 GREATEST(t.post_count - 1, 0)::BIGINT AS reply_count,
234 t.last_activity_at,
235 t.pinned, t.locked
236 FROM threads t
237 JOIN categories c ON c.id = t.category_id
238 JOIN communities co ON co.id = c.community_id
239 JOIN users u ON u.mnw_account_id = t.author_id
240 {tag_join}
241 WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL
242 {order_clause}
243 LIMIT {limit_ph} OFFSET {offset_ph}"
244 );
245
246 // runtime-checked: dynamic SQL (cannot use compile-time macro)
247 let mut q = sqlx::query_as::<_, ThreadWithMeta>(&query)
248 .bind(community_slug)
249 .bind(category_slug);
250 if let Some(tag) = tag_slug {
251 q = q.bind(tag);
252 }
253 q.bind(limit).bind(offset).fetch_all(pool).await
254 }
255
256 #[tracing::instrument(skip_all)]
257 pub async fn count_threads_in_category(
258 pool: &PgPool,
259 community_slug: &str,
260 category_slug: &str,
261 ) -> Result<i64, sqlx::Error> {
262 sqlx::query_scalar!(
263 r#"SELECT COUNT(*) AS "count!"
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 WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL"#,
268 community_slug,
269 category_slug,
270 )
271 .fetch_one(pool)
272 .await
273 }
274
275 #[tracing::instrument(skip_all)]
276 pub async fn get_thread_with_breadcrumb(
277 pool: &PgPool,
278 thread_id: Uuid,
279 ) -> Result<Option<super::Unscoped<ThreadWithBreadcrumb>>, sqlx::Error> {
280 sqlx::query_as!(
281 ThreadWithBreadcrumb,
282 r#"SELECT t.id, t.title, t.locked, t.pinned, t.author_id,
283 co.id AS community_id,
284 co.name AS community_name, co.slug AS community_slug,
285 c.name AS category_name, c.slug AS category_slug
286 FROM threads t
287 JOIN categories c ON c.id = t.category_id
288 JOIN communities co ON co.id = c.community_id
289 WHERE t.id = $1 AND t.deleted_at IS NULL"#,
290 thread_id,
291 )
292 .fetch_optional(pool)
293 .await
294 .map(|opt| {
295 opt.map(|row| {
296 let community_id = row.community_id;
297 super::Unscoped::new(row, community_id)
298 })
299 })
300 }
301
302 /// Count threads in a category, optionally filtered by tag slug.
303 #[tracing::instrument(skip_all)]
304 pub async fn count_threads_in_category_filtered(
305 pool: &PgPool,
306 community_slug: &str,
307 category_slug: &str,
308 tag_slug: Option<&str>,
309 ) -> Result<i64, sqlx::Error> {
310 if let Some(tag) = tag_slug {
311 sqlx::query_scalar!(
312 r#"SELECT COUNT(DISTINCT t.id) AS "count!"
313 FROM threads t
314 JOIN categories c ON c.id = t.category_id
315 JOIN communities co ON co.id = c.community_id
316 JOIN thread_tags tt ON tt.thread_id = t.id
317 JOIN tags tg ON tg.id = tt.tag_id AND tg.slug = $3 AND tg.community_id = co.id
318 WHERE co.slug = $1 AND c.slug = $2 AND t.deleted_at IS NULL"#,
319 community_slug,
320 category_slug,
321 tag,
322 )
323 .fetch_one(pool)
324 .await
325 } else {
326 count_threads_in_category(pool, community_slug, category_slug).await
327 }
328 }
329
330 /// Check if a thread exists by ID.
331 #[tracing::instrument(skip_all)]
332 pub async fn thread_exists(pool: &PgPool, thread_id: Uuid) -> Result<bool, sqlx::Error> {
333 sqlx::query_scalar!(
334 r#"SELECT EXISTS(SELECT 1 FROM threads WHERE id = $1) AS "exists!""#,
335 thread_id,
336 )
337 .fetch_one(pool)
338 .await
339 }
340