systemprompt_users/repository/user/
find.rs1use 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_email(&self, email: &str) -> Result<Option<User>> {
35 let email = crate::models::normalise_email(email);
36 let deleted_status = UserStatus::Deleted.as_str();
37 let row = 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 email = $1 AND status != $2
44 "#,
45 email,
46 deleted_status
47 )
48 .fetch_optional(&*self.pool)
49 .await?
50 .map(User::try_from)
51 .transpose()?;
52
53 Ok(row)
54 }
55
56 pub async fn find_by_name(&self, name: &str) -> Result<Option<User>> {
57 let deleted_status = UserStatus::Deleted.as_str();
58 let row = sqlx::query_as!(
59 UserRow,
60 r#"
61 SELECT id, name, email, full_name, display_name, status, email_verified,
62 roles, avatar_url, is_bot, is_scanner, created_at, updated_at, account_kind
63 FROM users
64 WHERE name = $1 AND status != $2
65 ORDER BY created_at ASC
66 LIMIT 1
67 "#,
68 name,
69 deleted_status
70 )
71 .fetch_optional(&*self.pool)
72 .await?
73 .map(User::try_from)
74 .transpose()?;
75
76 Ok(row)
77 }
78
79 pub async fn find_by_reference_any_status(&self, reference: &str) -> Result<Option<User>> {
80 let row = sqlx::query_as!(
81 UserRow,
82 r#"
83 SELECT id, name, email, full_name, display_name, status, email_verified,
84 roles, avatar_url, is_bot, is_scanner, created_at, updated_at, account_kind
85 FROM users
86 WHERE id = $1 OR email = $1 OR name = $1
87 ORDER BY (id = $1) DESC, (email = $1) DESC, created_at ASC
88 LIMIT 1
89 "#,
90 reference
91 )
92 .fetch_optional(&*self.pool)
93 .await?
94 .map(User::try_from)
95 .transpose()?;
96
97 Ok(row)
98 }
99
100 pub async fn list_by_role(&self, role: UserRole) -> Result<Vec<User>> {
101 let deleted_status = UserStatus::Deleted.as_str();
102 let rows = sqlx::query_as!(
103 UserRow,
104 r#"
105 SELECT id, name, email, full_name, display_name, status, email_verified,
106 roles, avatar_url, is_bot, is_scanner, created_at, updated_at, account_kind
107 FROM users
108 WHERE $1 = ANY(roles) AND status != $2
109 ORDER BY created_at DESC
110 "#,
111 role.as_str(),
112 deleted_status
113 )
114 .fetch_all(&*self.pool)
115 .await?
116 .into_iter()
117 .map(User::try_from)
118 .collect::<Result<Vec<_>>>()?;
119
120 Ok(rows)
121 }
122
123 pub async fn list_active_ids_with_role(&self, role: &str, limit: i64) -> Result<Vec<UserId>> {
124 let active = UserStatus::Active.as_str();
125 Ok(sqlx::query_scalar!(
126 r#"SELECT id AS "id: UserId" FROM users
127 WHERE $1 = ANY(roles) AND status = $2
128 ORDER BY id LIMIT $3"#,
129 role,
130 active,
131 limit.max(1),
132 )
133 .fetch_all(&*self.pool)
134 .await?)
135 }
136
137 pub async fn find_first_user(&self) -> Result<Option<User>> {
138 let deleted_status = UserStatus::Deleted.as_str();
139 let row = sqlx::query_as!(
140 UserRow,
141 r#"
142 SELECT id, name, email, full_name, display_name, status, email_verified,
143 roles, avatar_url, is_bot, is_scanner, created_at, updated_at, account_kind
144 FROM users
145 WHERE status != $1
146 ORDER BY created_at ASC
147 LIMIT 1
148 "#,
149 deleted_status
150 )
151 .fetch_optional(&*self.pool)
152 .await?
153 .map(User::try_from)
154 .transpose()?;
155
156 Ok(row)
157 }
158
159 pub async fn find_first_admin(&self) -> Result<Option<User>> {
160 let deleted_status = UserStatus::Deleted.as_str();
161 let admin_role = UserRole::Admin.as_str();
162 let row = sqlx::query_as!(
163 UserRow,
164 r#"
165 SELECT id, name, email, full_name, display_name, status, email_verified,
166 roles, avatar_url, is_bot, is_scanner, created_at, updated_at, account_kind
167 FROM users
168 WHERE $1 = ANY(roles) AND status != $2
169 ORDER BY created_at ASC
170 LIMIT 1
171 "#,
172 admin_role,
173 deleted_status
174 )
175 .fetch_optional(&*self.pool)
176 .await?
177 .map(User::try_from)
178 .transpose()?;
179
180 Ok(row)
181 }
182
183 pub async fn find_authenticated_user(&self, user_id: &UserId) -> Result<Option<User>> {
184 let active_status = UserStatus::Active.as_str();
185 let row = sqlx::query_as!(
186 UserRow,
187 r#"
188 SELECT id, name, email, full_name, display_name, status, email_verified,
189 roles, avatar_url, is_bot, is_scanner, created_at, updated_at, account_kind
190 FROM users
191 WHERE id = $1 AND status = $2
192 "#,
193 user_id.as_str(),
194 active_status
195 )
196 .fetch_optional(&*self.pool)
197 .await?
198 .map(User::try_from)
199 .transpose()?;
200
201 Ok(row)
202 }
203}
204
205impl UserRepository {
206 pub async fn missing_ids(&self, candidates: &[UserId]) -> Result<Vec<UserId>> {
207 let ids: Vec<String> = candidates.iter().map(ToString::to_string).collect();
208 let rows = sqlx::query_scalar!(
209 r#"
210 SELECT candidate AS "candidate!"
211 FROM UNNEST($1::text[]) AS candidate
212 WHERE NOT EXISTS (SELECT 1 FROM users WHERE users.id = candidate)
213 "#,
214 &ids[..]
215 )
216 .fetch_all(&*self.pool)
217 .await?;
218 Ok(rows.into_iter().map(UserId::new).collect())
219 }
220}