Skip to main content

max / makenotwork

4.4 KB · 155 lines History Blame Raw
1 //! Report queries: create, list, resolve, and count user-submitted reports.
2
3 use sqlx::PgPool;
4 use uuid::Uuid;
5
6 use super::id_types::*;
7 use super::models::*;
8 use super::enums::*;
9 use crate::error::Result;
10
11 /// Create a new report.
12 #[tracing::instrument(skip_all)]
13 pub async fn create_report(
14 pool: &PgPool,
15 reporter_id: UserId,
16 target_type: ReportTargetType,
17 target_id: Uuid,
18 report_type: ReportType,
19 reason: &str,
20 ) -> Result<DbReport> {
21 let report = sqlx::query_as::<_, DbReport>(
22 r#"
23 INSERT INTO reports (reporter_user_id, target_type, target_id, report_type, reason)
24 VALUES ($1, $2, $3, $4, $5)
25 RETURNING id, reporter_user_id, target_type, target_id, report_type, reason,
26 status, admin_notes, resolved_by, created_at, resolved_at
27 "#,
28 )
29 .bind(reporter_id)
30 .bind(target_type)
31 .bind(target_id)
32 .bind(report_type)
33 .bind(reason)
34 .fetch_one(pool)
35 .await?;
36
37 Ok(report)
38 }
39
40 /// Get reports for the admin queue, joined with reporter, target, and owner info.
41 ///
42 /// Uses two sub-queries (one for projects, one for items) to resolve target details,
43 /// then UNIONs and filters. This avoids N+1 queries.
44 #[tracing::instrument(skip_all)]
45 pub async fn get_admin_reports(
46 pool: &PgPool,
47 status_filter: Option<&str>,
48 limit: i64,
49 offset: i64,
50 ) -> Result<Vec<DbAdminReportRow>> {
51 let rows = sqlx::query_as::<_, DbAdminReportRow>(
52 r#"
53 SELECT
54 r.id,
55 u.username AS reporter_username,
56 r.target_type,
57 COALESCE(
58 CASE WHEN r.target_type = 'project' THEN p.title END,
59 CASE WHEN r.target_type = 'item' THEN i.title END,
60 '(deleted)'
61 ) AS target_title,
62 COALESCE(
63 CASE WHEN r.target_type = 'project' THEN p.slug::TEXT END,
64 CASE WHEN r.target_type = 'item' THEN r.target_id::TEXT END,
65 ''
66 ) AS target_slug_or_id,
67 COALESCE(
68 CASE WHEN r.target_type = 'project' THEN pu.username END,
69 CASE WHEN r.target_type = 'item' THEN iu.username END,
70 '(unknown)'
71 ) AS target_owner,
72 r.report_type,
73 r.reason,
74 r.status,
75 r.admin_notes,
76 r.created_at,
77 r.resolved_at
78 FROM reports r
79 JOIN users u ON u.id = r.reporter_user_id
80 LEFT JOIN projects p ON r.target_type = 'project' AND p.id = r.target_id
81 LEFT JOIN users pu ON p.user_id = pu.id
82 LEFT JOIN items i ON r.target_type = 'item' AND i.id = r.target_id
83 LEFT JOIN projects ip ON i.project_id = ip.id
84 LEFT JOIN users iu ON ip.user_id = iu.id
85 WHERE ($1::TEXT IS NULL OR r.status = $1)
86 ORDER BY r.created_at DESC
87 LIMIT $2 OFFSET $3
88 "#,
89 )
90 .bind(status_filter)
91 .bind(limit)
92 .bind(offset)
93 .fetch_all(pool)
94 .await?;
95
96 Ok(rows)
97 }
98
99 /// Get aggregate report stats.
100 #[tracing::instrument(skip_all)]
101 pub async fn get_report_stats(pool: &PgPool) -> Result<DbReportStats> {
102 let stats = sqlx::query_as::<_, DbReportStats>(
103 r#"
104 SELECT
105 COUNT(*) FILTER (WHERE status = 'open') AS open,
106 COUNT(*) FILTER (WHERE status = 'resolved') AS resolved,
107 COUNT(*) FILTER (WHERE status = 'dismissed') AS dismissed
108 FROM reports
109 "#,
110 )
111 .fetch_one(pool)
112 .await?;
113
114 Ok(stats)
115 }
116
117 /// Resolve or dismiss a report.
118 #[tracing::instrument(skip_all)]
119 pub async fn resolve_report(
120 pool: &PgPool,
121 id: ReportId,
122 status: ReportStatus,
123 admin_notes: &str,
124 resolved_by: UserId,
125 ) -> Result<()> {
126 sqlx::query(
127 r#"
128 UPDATE reports
129 SET status = $2, admin_notes = $3, resolved_by = $4, resolved_at = NOW()
130 WHERE id = $1
131 "#,
132 )
133 .bind(id)
134 .bind(status)
135 .bind(admin_notes)
136 .bind(resolved_by)
137 .execute(pool)
138 .await?;
139
140 Ok(())
141 }
142
143 /// Count reports from a specific user in the last 24 hours (rate limiting).
144 #[tracing::instrument(skip_all)]
145 pub async fn count_recent_reports_by_user(pool: &PgPool, user_id: UserId) -> Result<i64> {
146 let row: (i64,) = sqlx::query_as(
147 "SELECT COUNT(*) FROM reports WHERE reporter_user_id = $1 AND created_at > NOW() - INTERVAL '24 hours'",
148 )
149 .bind(user_id)
150 .fetch_one(pool)
151 .await?;
152
153 Ok(row.0)
154 }
155