Skip to main content

max / makenotwork

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