Skip to main content

max / makenotwork

9.3 KB · 354 lines History Blame Raw
1 //! Issue tracker queries: issues, comments, labels.
2
3 use sqlx::PgPool;
4
5 use super::models::{
6 DbIssue, DbIssueComment, DbIssueCommentWithAuthor, DbIssueWithMeta,
7 };
8 use super::{GitRepoId, IssueId, IssueStatus, UserId};
9 use crate::error::Result;
10
11 // ── Issues ──
12
13 /// Create a new issue, assigning the next sequential number for the repo.
14 /// Retries once on unique constraint violation (concurrent insert race).
15 #[tracing::instrument(skip_all)]
16 pub async fn create_issue(
17 pool: &PgPool,
18 repo_id: GitRepoId,
19 author_id: UserId,
20 title: &str,
21 body_md: &str,
22 body_html: &str,
23 ) -> Result<DbIssue> {
24 let result = try_create_issue(pool, repo_id, author_id, title, body_md, body_html).await;
25 match result {
26 Ok(issue) => Ok(issue),
27 Err(_) => {
28 // Retry once on conflict (concurrent number assignment)
29 try_create_issue(pool, repo_id, author_id, title, body_md, body_html).await
30 }
31 }
32 }
33
34 async fn try_create_issue(
35 pool: &PgPool,
36 repo_id: GitRepoId,
37 author_id: UserId,
38 title: &str,
39 body_md: &str,
40 body_html: &str,
41 ) -> Result<DbIssue> {
42 let issue = sqlx::query_as::<_, DbIssue>(
43 r#"
44 INSERT INTO issues (repo_id, number, author_user_id, title, body_markdown, body_html)
45 VALUES ($1, (SELECT COALESCE(MAX(number), 0) + 1 FROM issues WHERE repo_id = $1), $2, $3, $4, $5)
46 RETURNING *
47 "#,
48 )
49 .bind(repo_id)
50 .bind(author_id)
51 .bind(title)
52 .bind(body_md)
53 .bind(body_html)
54 .fetch_one(pool)
55 .await?;
56
57 Ok(issue)
58 }
59
60 /// Get a single issue by its primary key.
61 #[tracing::instrument(skip_all)]
62 pub async fn get_issue_by_id(pool: &PgPool, issue_id: IssueId) -> Result<Option<DbIssue>> {
63 let issue = sqlx::query_as::<_, DbIssue>("SELECT * FROM issues WHERE id = $1")
64 .bind(issue_id)
65 .fetch_optional(pool)
66 .await?;
67 Ok(issue)
68 }
69
70 /// Get a single issue by its repo-scoped number.
71 #[tracing::instrument(skip_all)]
72 pub async fn get_issue_by_number(
73 pool: &PgPool,
74 repo_id: GitRepoId,
75 number: i32,
76 ) -> Result<Option<DbIssue>> {
77 let issue = sqlx::query_as::<_, DbIssue>(
78 "SELECT * FROM issues WHERE repo_id = $1 AND number = $2",
79 )
80 .bind(repo_id)
81 .bind(number)
82 .fetch_optional(pool)
83 .await?;
84
85 Ok(issue)
86 }
87
88 /// List issues with author username and comment count. Returns (issues, total_count).
89 #[tracing::instrument(skip_all)]
90 pub async fn list_issues(
91 pool: &PgPool,
92 repo_id: GitRepoId,
93 status: Option<IssueStatus>,
94 search: Option<&str>,
95 page: i64,
96 per_page: i64,
97 ) -> Result<(Vec<DbIssueWithMeta>, i64)> {
98 let offset = (page - 1) * per_page;
99 let status_str = status.map(|s| s.to_string());
100 let search_pattern = search.map(|s| {
101 format!("%{}%", s.replace('\\', "\\\\").replace('%', "\\%").replace('_', "\\_"))
102 });
103
104 let issues = sqlx::query_as::<_, DbIssueWithMeta>(
105 r#"
106 SELECT
107 i.id, i.repo_id, i.number, i.author_user_id, i.title,
108 i.status, i.created_at, i.updated_at,
109 u.username AS author_username,
110 (SELECT COUNT(*) FROM issue_comments WHERE issue_id = i.id) AS comment_count
111 FROM issues i
112 JOIN users u ON u.id = i.author_user_id
113 WHERE i.repo_id = $1
114 AND ($2::TEXT IS NULL OR i.status = $2)
115 AND ($3::TEXT IS NULL OR i.title ILIKE $3)
116 ORDER BY i.created_at DESC
117 LIMIT $4 OFFSET $5
118 "#,
119 )
120 .bind(repo_id)
121 .bind(&status_str)
122 .bind(&search_pattern)
123 .bind(per_page)
124 .bind(offset)
125 .fetch_all(pool)
126 .await?;
127
128 let total: i64 = sqlx::query_scalar(
129 r#"
130 SELECT COUNT(*)
131 FROM issues
132 WHERE repo_id = $1
133 AND ($2::TEXT IS NULL OR status = $2)
134 AND ($3::TEXT IS NULL OR title ILIKE $3)
135 "#,
136 )
137 .bind(repo_id)
138 .bind(&status_str)
139 .bind(&search_pattern)
140 .fetch_one(pool)
141 .await?;
142
143 Ok((issues, total))
144 }
145
146 /// Update an issue's title and body.
147 #[tracing::instrument(skip_all)]
148 pub async fn update_issue(
149 pool: &PgPool,
150 issue_id: IssueId,
151 title: &str,
152 body_md: &str,
153 body_html: &str,
154 ) -> Result<()> {
155 sqlx::query(
156 "UPDATE issues SET title = $2, body_markdown = $3, body_html = $4, updated_at = NOW() WHERE id = $1",
157 )
158 .bind(issue_id)
159 .bind(title)
160 .bind(body_md)
161 .bind(body_html)
162 .execute(pool)
163 .await?;
164
165 Ok(())
166 }
167
168 /// Update an issue's status (open/closed).
169 #[tracing::instrument(skip_all)]
170 pub async fn update_issue_status(
171 pool: &PgPool,
172 issue_id: IssueId,
173 status: IssueStatus,
174 ) -> Result<()> {
175 sqlx::query("UPDATE issues SET status = $2, updated_at = NOW() WHERE id = $1")
176 .bind(issue_id)
177 .bind(status)
178 .execute(pool)
179 .await?;
180
181 Ok(())
182 }
183
184 /// Get (open_count, closed_count) for a repo.
185 #[tracing::instrument(skip_all)]
186 pub async fn get_issue_counts(pool: &PgPool, repo_id: GitRepoId) -> Result<(i64, i64)> {
187 let open: i64 = sqlx::query_scalar(
188 "SELECT COUNT(*) FROM issues WHERE repo_id = $1 AND status = 'open'",
189 )
190 .bind(repo_id)
191 .fetch_one(pool)
192 .await?;
193
194 let closed: i64 = sqlx::query_scalar(
195 "SELECT COUNT(*) FROM issues WHERE repo_id = $1 AND status = 'closed'",
196 )
197 .bind(repo_id)
198 .fetch_one(pool)
199 .await?;
200
201 Ok((open, closed))
202 }
203
204 // ── Comments ──
205
206 /// Add a comment to an issue.
207 #[tracing::instrument(skip_all)]
208 pub async fn create_comment(
209 pool: &PgPool,
210 issue_id: IssueId,
211 author_id: UserId,
212 body_md: &str,
213 body_html: &str,
214 ) -> Result<DbIssueComment> {
215 let comment = sqlx::query_as::<_, DbIssueComment>(
216 r#"
217 INSERT INTO issue_comments (issue_id, author_user_id, body_markdown, body_html)
218 VALUES ($1, $2, $3, $4)
219 RETURNING *
220 "#,
221 )
222 .bind(issue_id)
223 .bind(author_id)
224 .bind(body_md)
225 .bind(body_html)
226 .fetch_one(pool)
227 .await?;
228
229 // Touch the issue's updated_at
230 sqlx::query("UPDATE issues SET updated_at = NOW() WHERE id = $1")
231 .bind(issue_id)
232 .execute(pool)
233 .await?;
234
235 Ok(comment)
236 }
237
238 /// List all comments on an issue with author usernames.
239 #[tracing::instrument(skip_all)]
240 pub async fn list_comments(
241 pool: &PgPool,
242 issue_id: IssueId,
243 ) -> Result<Vec<DbIssueCommentWithAuthor>> {
244 let comments = sqlx::query_as::<_, DbIssueCommentWithAuthor>(
245 r#"
246 SELECT c.id, c.issue_id, c.author_user_id, c.body_markdown, c.body_html, c.created_at,
247 u.username AS author_username
248 FROM issue_comments c
249 JOIN users u ON u.id = c.author_user_id
250 WHERE c.issue_id = $1
251 ORDER BY c.created_at ASC
252 "#,
253 )
254 .bind(issue_id)
255 .fetch_all(pool)
256 .await?;
257
258 Ok(comments)
259 }
260
261 // ── Issue participants (for email notifications) ──
262
263 /// Get all distinct participant user IDs for an issue (author + all comment authors).
264 #[tracing::instrument(skip_all)]
265 pub async fn get_issue_participants(pool: &PgPool, issue_id: IssueId) -> Result<Vec<UserId>> {
266 let ids = sqlx::query_scalar::<_, UserId>(
267 r#"
268 SELECT DISTINCT author_user_id
269 FROM (
270 SELECT author_user_id FROM issues WHERE id = $1
271 UNION
272 SELECT author_user_id FROM issue_comments WHERE issue_id = $1
273 ) AS participants
274 "#,
275 )
276 .bind(issue_id)
277 .fetch_all(pool)
278 .await?;
279
280 Ok(ids)
281 }
282
283 /// Record the Multithreaded forum thread that mirrors this issue. Best-effort:
284 /// log and proceed on DB error so the issue itself isn't lost.
285 #[tracing::instrument(skip_all)]
286 pub async fn set_mt_thread_id(
287 pool: &PgPool,
288 issue_id: IssueId,
289 mt_thread_id: uuid::Uuid,
290 ) -> Result<()> {
291 sqlx::query("UPDATE issues SET mt_thread_id = $2 WHERE id = $1")
292 .bind(issue_id)
293 .bind(mt_thread_id)
294 .execute(pool)
295 .await?;
296 Ok(())
297 }
298
299 /// Fetch the MT thread linked to an issue, if any.
300 #[tracing::instrument(skip_all)]
301 pub async fn get_mt_thread_id(
302 pool: &PgPool,
303 issue_id: IssueId,
304 ) -> Result<Option<uuid::Uuid>> {
305 let row: Option<(Option<uuid::Uuid>,)> = sqlx::query_as(
306 "SELECT mt_thread_id FROM issues WHERE id = $1",
307 )
308 .bind(issue_id)
309 .fetch_optional(pool)
310 .await?;
311 Ok(row.and_then(|r| r.0))
312 }
313
314 // ── Issue message ID mapping (for email threading) ──
315
316 /// Store a mapping from an email Message-ID to an issue.
317 #[tracing::instrument(skip_all)]
318 pub async fn insert_issue_message_id(
319 pool: &PgPool,
320 message_id: &str,
321 issue_id: IssueId,
322 ) -> Result<()> {
323 sqlx::query(
324 "INSERT INTO issue_message_ids (message_id, issue_id) VALUES ($1, $2) ON CONFLICT DO NOTHING",
325 )
326 .bind(message_id)
327 .bind(issue_id)
328 .execute(pool)
329 .await?;
330
331 Ok(())
332 }
333
334 /// Look up an issue ID by any of the given email Message-IDs.
335 #[tracing::instrument(skip_all)]
336 pub async fn get_issue_id_by_any_message_id(
337 pool: &PgPool,
338 message_ids: &[&str],
339 ) -> Result<Option<IssueId>> {
340 if message_ids.is_empty() {
341 return Ok(None);
342 }
343
344 let ids: Vec<String> = message_ids.iter().map(|s| s.to_string()).collect();
345 let issue_id = sqlx::query_scalar::<_, IssueId>(
346 "SELECT issue_id FROM issue_message_ids WHERE message_id = ANY($1) LIMIT 1",
347 )
348 .bind(&ids)
349 .fetch_optional(pool)
350 .await?;
351
352 Ok(issue_id)
353 }
354