Skip to main content

max / makenotwork

5.6 KB · 174 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.removed_at IS NULL
54 AND p.deleted_at IS NULL
55 AND t.deleted_at IS NULL) AS "post_count!",
56 (SELECT COUNT(*) FROM post_endorsements pe
57 JOIN posts p ON p.id = pe.post_id
58 JOIN threads t ON t.id = p.thread_id
59 JOIN categories c ON c.id = t.category_id
60 WHERE p.author_id = u.mnw_account_id
61 AND c.community_id = co.id
62 AND p.removed_at IS NULL
63 AND p.deleted_at IS NULL
64 AND t.deleted_at IS NULL) AS "endorsement_count!"
65 FROM users u
66 JOIN memberships m ON m.user_id = u.mnw_account_id
67 JOIN communities co ON co.id = m.community_id
68 WHERE co.slug = $1 AND u.username = $2"#,
69 community_slug,
70 username,
71 )
72 .fetch_optional(pool)
73 .await
74 }
75
76 #[derive(sqlx::FromRow)]
77 pub struct UserActivityRow {
78 pub thread_id: Uuid,
79 pub thread_title: String,
80 pub category_name: String,
81 pub category_slug: String,
82 pub post_created_at: DateTime<Utc>,
83 pub is_thread_author: bool,
84 }
85
86 /// Fetch a user's recent activity (posts) within a community.
87 #[tracing::instrument(skip_all)]
88 pub async fn get_user_activity_in_community(
89 pool: &PgPool,
90 community_id: Uuid,
91 user_id: Uuid,
92 limit: i64,
93 ) -> Result<Vec<UserActivityRow>, sqlx::Error> {
94 sqlx::query_as!(
95 UserActivityRow,
96 r#"SELECT t.id AS thread_id,
97 t.title AS thread_title,
98 c.name AS category_name,
99 c.slug AS category_slug,
100 p.created_at AS "post_created_at: chrono::DateTime<chrono::Utc>",
101 (t.author_id = $2) AS "is_thread_author!"
102 FROM posts p
103 JOIN threads t ON t.id = p.thread_id
104 JOIN categories c ON c.id = t.category_id
105 WHERE c.community_id = $1
106 AND p.author_id = $2
107 AND p.removed_at IS NULL
108 AND p.deleted_at IS NULL
109 AND t.deleted_at IS NULL
110 ORDER BY p.created_at DESC
111 LIMIT $3"#,
112 community_id,
113 user_id,
114 limit,
115 )
116 .fetch_all(pool)
117 .await
118 }
119
120 #[derive(sqlx::FromRow, serde::Serialize)]
121 pub struct UserMembershipSummary {
122 pub community_name: String,
123 pub community_slug: String,
124 pub role: CommunityRole,
125 pub joined_at: DateTime<Utc>,
126 pub post_count: i64,
127 }
128
129 /// Fetch all community memberships for a user with post counts.
130 #[tracing::instrument(skip_all)]
131 pub async fn get_user_membership_summary(
132 pool: &PgPool,
133 user_id: Uuid,
134 ) -> Result<Vec<UserMembershipSummary>, sqlx::Error> {
135 sqlx::query_as!(
136 UserMembershipSummary,
137 r#"SELECT co.name AS community_name,
138 co.slug AS community_slug,
139 m.role AS "role: CommunityRole",
140 m.joined_at AS "joined_at: chrono::DateTime<chrono::Utc>",
141 (SELECT COUNT(*) FROM posts p
142 JOIN threads t ON t.id = p.thread_id
143 JOIN categories c ON c.id = t.category_id
144 WHERE p.author_id = $1
145 AND c.community_id = co.id
146 AND p.removed_at IS NULL
147 AND p.deleted_at IS NULL
148 AND t.deleted_at IS NULL) AS "post_count!"
149 FROM memberships m
150 JOIN communities co ON co.id = m.community_id
151 WHERE m.user_id = $1
152 AND co.suspended_at IS NULL
153 ORDER BY co.name"#,
154 user_id,
155 )
156 .fetch_all(pool)
157 .await
158 }
159
160 /// Fetch a user's saved signature, or `None` if the user row is absent.
161 #[tracing::instrument(skip_all)]
162 pub async fn get_user_signature(
163 pool: &PgPool,
164 user_id: Uuid,
165 ) -> Result<Option<UserSignature>, sqlx::Error> {
166 let row = sqlx::query!(
167 "SELECT signature_markdown, signature_html FROM users WHERE mnw_account_id = $1",
168 user_id,
169 )
170 .fetch_optional(pool)
171 .await?;
172 Ok(row.map(|r| (r.signature_markdown, r.signature_html)))
173 }
174