1use systemprompt_identifiers::UserId;
7
8use crate::error::Result;
9use crate::models::{User, UserRole, UserRow, UserStatus};
10use crate::repository::UserRepository;
11
12impl UserRepository {
13 pub async fn find_by_id(&self, id: &UserId) -> Result<Option<User>> {
14 let deleted_status = UserStatus::Deleted.as_str();
15 let row = sqlx::query_as!(
16 UserRow,
17 r#"
18 SELECT id, name, email, full_name, display_name, status, email_verified,
19 roles, avatar_url, is_bot, is_scanner, created_at, updated_at, account_kind
20 FROM users
21 WHERE id = $1 AND status != $2
22 "#,
23 id.as_str(),
24 deleted_status
25 )
26 .fetch_optional(&*self.pool)
27 .await?
28 .map(User::try_from)
29 .transpose()?;
30
31 Ok(row)
32 }
33
34 pub async fn find_by_ids(&self, ids: &[UserId]) -> Result<Vec<User>> {
35 let deleted_status = UserStatus::Deleted.as_str();
36 let ids: Vec<String> = ids.iter().map(|id| id.as_str().to_owned()).collect();
37 let rows = sqlx::query_as!(
38 UserRow,
39 r#"
40 SELECT id, name, email, full_name, display_name, status, email_verified,
41 roles, avatar_url, is_bot, is_scanner, created_at, updated_at, account_kind
42 FROM users
43 WHERE id = ANY($1::text[]) AND status != $2
44 "#,
45 &ids,
46 deleted_status
47 )
48 .fetch_all(&*self.pool)
49 .await?;
50 rows.into_iter().map(User::try_from).collect()
51 }
52
53 pub async fn find_by_email(&self, email: &str) -> Result<Option<User>> {
54 let email = crate::models::normalise_email(email);
55 let deleted_status = UserStatus::Deleted.as_str();
56 let row = sqlx::query_as!(
57 UserRow,
58 r#"
59 SELECT id, name, email, full_name, display_name, status, email_verified,
60 roles, avatar_url, is_bot, is_scanner, created_at, updated_at, account_kind
61 FROM users
62 WHERE email = $1 AND status != $2
63 "#,
64 email,
65 deleted_status
66 )
67 .fetch_optional(&*self.pool)
68 .await?
69 .map(User::try_from)
70 .transpose()?;
71
72 Ok(row)
73 }
74
75 pub async fn find_by_name(&self, name: &str) -> Result<Option<User>> {
76 let deleted_status = UserStatus::Deleted.as_str();
77 let row = sqlx::query_as!(
78 UserRow,
79 r#"
80 SELECT id, name, email, full_name, display_name, status, email_verified,
81 roles, avatar_url, is_bot, is_scanner, created_at, updated_at, account_kind
82 FROM users
83 WHERE name = $1 AND status != $2
84 ORDER BY created_at ASC
85 LIMIT 1
86 "#,
87 name,
88 deleted_status
89 )
90 .fetch_optional(&*self.pool)
91 .await?
92 .map(User::try_from)
93 .transpose()?;
94
95 Ok(row)
96 }
97
98 pub async fn find_by_reference_any_status(&self, reference: &str) -> Result<Option<User>> {
99 let row = sqlx::query_as!(
100 UserRow,
101 r#"
102 SELECT id, name, email, full_name, display_name, status, email_verified,
103 roles, avatar_url, is_bot, is_scanner, created_at, updated_at, account_kind
104 FROM users
105 WHERE id = $1 OR email = $1 OR name = $1
106 ORDER BY (id = $1) DESC, (email = $1) DESC, created_at ASC
107 LIMIT 1
108 "#,
109 reference
110 )
111 .fetch_optional(&*self.pool)
112 .await?
113 .map(User::try_from)
114 .transpose()?;
115
116 Ok(row)
117 }
118
119 pub async fn list_by_role(&self, role: UserRole) -> Result<Vec<User>> {
120 let deleted_status = UserStatus::Deleted.as_str();
121 let rows = sqlx::query_as!(
122 UserRow,
123 r#"
124 SELECT id, name, email, full_name, display_name, status, email_verified,
125 roles, avatar_url, is_bot, is_scanner, created_at, updated_at, account_kind
126 FROM users
127 WHERE $1 = ANY(roles) AND status != $2
128 ORDER BY created_at DESC
129 "#,
130 role.as_str(),
131 deleted_status
132 )
133 .fetch_all(&*self.pool)
134 .await?
135 .into_iter()
136 .map(User::try_from)
137 .collect::<Result<Vec<_>>>()?;
138
139 Ok(rows)
140 }
141
142 pub async fn list_active_ids_with_role(&self, role: &str, limit: i64) -> Result<Vec<UserId>> {
143 let active = UserStatus::Active.as_str();
144 Ok(sqlx::query_scalar!(
145 r#"SELECT id AS "id: UserId" FROM users
146 WHERE roles @> ARRAY[$1]::TEXT[] AND status = $2
147 ORDER BY id LIMIT $3"#,
148 role,
149 active,
150 limit.max(1),
151 )
152 .fetch_all(&*self.pool)
153 .await?)
154 }
155
156 pub async fn is_active_with_role(&self, user: &UserId, role: &str) -> Result<bool> {
157 let active = UserStatus::Active.as_str();
158 Ok(sqlx::query_scalar!(
159 r#"SELECT EXISTS(
160 SELECT 1 FROM users
161 WHERE id = $1 AND roles @> ARRAY[$2]::TEXT[] AND status = $3
162 ) AS "exists!""#,
163 user.as_str(),
164 role,
165 active,
166 )
167 .fetch_one(&*self.pool)
168 .await?)
169 }
170
171 pub async fn list_active_ids(&self, limit: i64) -> Result<Vec<UserId>> {
172 let active = UserStatus::Active.as_str();
173 Ok(sqlx::query_scalar!(
174 r#"SELECT id AS "id: UserId" FROM users
175 WHERE status = $1
176 ORDER BY id LIMIT $2"#,
177 active,
178 limit.max(1),
179 )
180 .fetch_all(&*self.pool)
181 .await?)
182 }
183
184 pub async fn find_first_user(&self) -> Result<Option<User>> {
185 let deleted_status = UserStatus::Deleted.as_str();
186 let row = sqlx::query_as!(
187 UserRow,
188 r#"
189 SELECT id, name, email, full_name, display_name, status, email_verified,
190 roles, avatar_url, is_bot, is_scanner, created_at, updated_at, account_kind
191 FROM users
192 WHERE status != $1
193 ORDER BY created_at ASC
194 LIMIT 1
195 "#,
196 deleted_status
197 )
198 .fetch_optional(&*self.pool)
199 .await?
200 .map(User::try_from)
201 .transpose()?;
202
203 Ok(row)
204 }
205
206 pub async fn find_first_admin(&self) -> Result<Option<User>> {
207 let deleted_status = UserStatus::Deleted.as_str();
208 let admin_role = UserRole::Admin.as_str();
209 let row = sqlx::query_as!(
210 UserRow,
211 r#"
212 SELECT id, name, email, full_name, display_name, status, email_verified,
213 roles, avatar_url, is_bot, is_scanner, created_at, updated_at, account_kind
214 FROM users
215 WHERE $1 = ANY(roles) AND status != $2
216 ORDER BY created_at ASC
217 LIMIT 1
218 "#,
219 admin_role,
220 deleted_status
221 )
222 .fetch_optional(&*self.pool)
223 .await?
224 .map(User::try_from)
225 .transpose()?;
226
227 Ok(row)
228 }
229
230 pub async fn find_authenticated_user(&self, user_id: &UserId) -> Result<Option<User>> {
231 let active_status = UserStatus::Active.as_str();
232 let row = sqlx::query_as!(
233 UserRow,
234 r#"
235 SELECT id, name, email, full_name, display_name, status, email_verified,
236 roles, avatar_url, is_bot, is_scanner, created_at, updated_at, account_kind
237 FROM users
238 WHERE id = $1 AND status = $2
239 "#,
240 user_id.as_str(),
241 active_status
242 )
243 .fetch_optional(&*self.pool)
244 .await?
245 .map(User::try_from)
246 .transpose()?;
247
248 Ok(row)
249 }
250}
251
252impl UserRepository {
253 pub async fn missing_ids(&self, candidates: &[UserId]) -> Result<Vec<UserId>> {
254 let ids: Vec<String> = candidates.iter().map(ToString::to_string).collect();
255 let rows = sqlx::query_scalar!(
256 r#"
257 SELECT candidate AS "candidate!"
258 FROM UNNEST($1::text[]) AS candidate
259 WHERE NOT EXISTS (SELECT 1 FROM users WHERE users.id = candidate)
260 "#,
261 &ids[..]
262 )
263 .fetch_all(&*self.pool)
264 .await?;
265 Ok(rows.into_iter().map(UserId::new).collect())
266 }
267}