Skip to main content

max / makenotwork

5.4 KB · 170 lines History Blame Raw
1 use super::{CommunityRole, DateTime, PgPool, Utc, Uuid};
2
3 /// A user's saved signature: `(markdown, html)`. Either field may be `NULL`.
4 pub type UserSignature = (Option<String>, Option<String>);
5
6 /// Look up a user's UUID by username.
7 #[tracing::instrument(skip_all)]
8 pub async fn get_user_by_username(
9 pool: &PgPool,
10 username: &str,
11 ) -> Result<Option<Uuid>, sqlx::Error> {
12 sqlx::query_scalar!(
13 "SELECT mnw_account_id FROM users WHERE username = $1",
14 username,
15 )
16 .fetch_optional(pool)
17 .await
18 }
19
20 #[derive(sqlx::FromRow)]
21 pub struct UserProfileRow {
22 pub user_id: Uuid,
23 pub username: String,
24 pub display_name: Option<String>,
25 pub avatar_url: Option<String>,
26 pub role: CommunityRole,
27 pub joined_at: DateTime<Utc>,
28 pub post_count: i64,
29 pub endorsement_count: i64,
30 }
31
32 /// Fetch a user's profile within a specific community.
33 /// Returns None if the user is not a member of the community.
34 #[tracing::instrument(skip_all)]
35 pub async fn get_user_profile_in_community(
36 pool: &PgPool,
37 community_slug: &str,
38 username: &str,
39 ) -> Result<Option<UserProfileRow>, sqlx::Error> {
40 sqlx::query_as!(
41 UserProfileRow,
42 r#"SELECT u.mnw_account_id AS user_id,
43 u.username,
44 u.display_name,
45 u.avatar_url,
46 m.role AS "role: CommunityRole",
47 m.joined_at AS "joined_at: chrono::DateTime<chrono::Utc>",
48 (SELECT COUNT(*) FROM posts p
49 JOIN threads t ON t.id = p.thread_id
50 JOIN categories c ON c.id = t.category_id
51 WHERE p.author_id = u.mnw_account_id
52 AND c.community_id = co.id
53 AND p.is_active
54 AND t.deleted_at IS NULL) AS "post_count!",
55 (SELECT COUNT(*) FROM post_endorsements pe
56 JOIN posts p ON p.id = pe.post_id
57 JOIN threads t ON t.id = p.thread_id
58 JOIN categories c ON c.id = t.category_id
59 WHERE p.author_id = u.mnw_account_id
60 AND c.community_id = co.id
61 AND p.is_active
62 AND t.deleted_at IS NULL) AS "endorsement_count!"
63 FROM users u
64 JOIN memberships m ON m.user_id = u.mnw_account_id
65 JOIN communities co ON co.id = m.community_id
66 WHERE co.slug = $1 AND u.username = $2"#,
67 community_slug,
68 username,
69 )
70 .fetch_optional(pool)
71 .await
72 }
73
74 #[derive(sqlx::FromRow)]
75 pub struct UserActivityRow {
76 pub thread_id: Uuid,
77 pub thread_title: String,
78 pub category_name: String,
79 pub category_slug: String,
80 pub post_created_at: DateTime<Utc>,
81 pub is_thread_author: bool,
82 }
83
84 /// Fetch a user's recent activity (posts) within a community.
85 #[tracing::instrument(skip_all)]
86 pub async fn get_user_activity_in_community(
87 pool: &PgPool,
88 community_id: Uuid,
89 user_id: Uuid,
90 limit: i64,
91 ) -> Result<Vec<UserActivityRow>, sqlx::Error> {
92 sqlx::query_as!(
93 UserActivityRow,
94 r#"SELECT t.id AS thread_id,
95 t.title AS thread_title,
96 c.name AS category_name,
97 c.slug AS category_slug,
98 p.created_at AS "post_created_at: chrono::DateTime<chrono::Utc>",
99 (t.author_id = $2) AS "is_thread_author!"
100 FROM posts p
101 JOIN threads t ON t.id = p.thread_id
102 JOIN categories c ON c.id = t.category_id
103 WHERE c.community_id = $1
104 AND p.author_id = $2
105 AND p.is_active
106 AND t.deleted_at IS NULL
107 ORDER BY p.created_at DESC
108 LIMIT $3"#,
109 community_id,
110 user_id,
111 limit,
112 )
113 .fetch_all(pool)
114 .await
115 }
116
117 #[derive(sqlx::FromRow, serde::Serialize)]
118 pub struct UserMembershipSummary {
119 pub community_name: String,
120 pub community_slug: String,
121 pub role: CommunityRole,
122 pub joined_at: DateTime<Utc>,
123 pub post_count: i64,
124 }
125
126 /// Fetch all community memberships for a user with post counts.
127 #[tracing::instrument(skip_all)]
128 pub async fn get_user_membership_summary(
129 pool: &PgPool,
130 user_id: Uuid,
131 ) -> Result<Vec<UserMembershipSummary>, sqlx::Error> {
132 sqlx::query_as!(
133 UserMembershipSummary,
134 r#"SELECT co.name AS community_name,
135 co.slug AS community_slug,
136 m.role AS "role: CommunityRole",
137 m.joined_at AS "joined_at: chrono::DateTime<chrono::Utc>",
138 (SELECT COUNT(*) FROM posts p
139 JOIN threads t ON t.id = p.thread_id
140 JOIN categories c ON c.id = t.category_id
141 WHERE p.author_id = $1
142 AND c.community_id = co.id
143 AND p.is_active
144 AND t.deleted_at IS NULL) AS "post_count!"
145 FROM memberships m
146 JOIN communities co ON co.id = m.community_id
147 WHERE m.user_id = $1
148 AND co.suspended_at IS NULL
149 ORDER BY co.name"#,
150 user_id,
151 )
152 .fetch_all(pool)
153 .await
154 }
155
156 /// Fetch a user's saved signature, or `None` if the user row is absent.
157 #[tracing::instrument(skip_all)]
158 pub async fn get_user_signature(
159 pool: &PgPool,
160 user_id: Uuid,
161 ) -> Result<Option<UserSignature>, sqlx::Error> {
162 let row = sqlx::query!(
163 "SELECT signature_markdown, signature_html FROM users WHERE mnw_account_id = $1",
164 user_id,
165 )
166 .fetch_optional(pool)
167 .await?;
168 Ok(row.map(|r| (r.signature_markdown, r.signature_html)))
169 }
170