Skip to main content

max / makenotwork

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