1use crate::application::ports::BoardMemberRepository;
2use crate::domain::entities::{BoardMember, BoardPosition};
3use crate::infrastructure::database::pool::DbPool;
4use async_trait::async_trait;
5use sqlx::Row;
6use uuid::Uuid;
7
8pub struct PostgresBoardMemberRepository {
9 pool: DbPool,
10}
11
12impl PostgresBoardMemberRepository {
13 pub fn new(pool: DbPool) -> Self {
14 Self { pool }
15 }
16}
17
18#[async_trait]
19impl BoardMemberRepository for PostgresBoardMemberRepository {
20 async fn create(&self, board_member: &BoardMember) -> Result<BoardMember, String> {
21 let position_str = match board_member.position {
22 BoardPosition::President => "president",
23 BoardPosition::Treasurer => "treasurer",
24 BoardPosition::Member => "member",
25 };
26
27 let organization_id: Uuid =
29 sqlx::query_scalar("SELECT organization_id FROM buildings WHERE id = $1")
30 .bind(board_member.building_id)
31 .fetch_one(&self.pool)
32 .await
33 .map_err(|e| format!("Failed to get building organization: {}", e))?;
34
35 sqlx::query(
36 r#"
37 INSERT INTO board_members (id, owner_id, building_id, organization_id, position, mandate_start, mandate_end, elected_by_meeting_id, is_active, created_at, updated_at)
38 VALUES ($1, $2, $3, $4, $5::board_position, $6, $7, $8, $9, $10, $11)
39 "#,
40 )
41 .bind(board_member.id)
42 .bind(board_member.owner_id)
43 .bind(board_member.building_id)
44 .bind(organization_id)
45 .bind(position_str)
46 .bind(board_member.mandate_start)
47 .bind(board_member.mandate_end)
48 .bind(board_member.elected_by_meeting_id)
49 .bind(true) .bind(board_member.created_at)
51 .bind(board_member.updated_at)
52 .execute(&self.pool)
53 .await
54 .map_err(|e| format!("Database error: {}", e))?;
55
56 Ok(board_member.clone())
57 }
58
59 async fn find_by_id(&self, id: Uuid) -> Result<Option<BoardMember>, String> {
60 let row = sqlx::query(
61 r#"
62 SELECT id, owner_id, building_id, position::TEXT as position, mandate_start, mandate_end, elected_by_meeting_id, created_at, updated_at
63 FROM board_members
64 WHERE id = $1
65 "#,
66 )
67 .bind(id)
68 .fetch_optional(&self.pool)
69 .await
70 .map_err(|e| format!("Database error: {}", e))?;
71
72 Ok(row.map(|row| {
73 let position_str: String = row.get("position");
74 let position = match position_str.as_str() {
75 "president" => BoardPosition::President,
76 "treasurer" => BoardPosition::Treasurer,
77 _ => BoardPosition::Member,
78 };
79
80 BoardMember {
81 id: row.get("id"),
82 owner_id: row.get("owner_id"),
83 building_id: row.get("building_id"),
84 position,
85 mandate_start: row.get("mandate_start"),
86 mandate_end: row.get("mandate_end"),
87 elected_by_meeting_id: row.get("elected_by_meeting_id"),
88 created_at: row.get("created_at"),
89 updated_at: row.get("updated_at"),
90 }
91 }))
92 }
93
94 async fn find_by_building(&self, building_id: Uuid) -> Result<Vec<BoardMember>, String> {
95 let rows = sqlx::query(
96 r#"
97 SELECT id, owner_id, building_id, position::TEXT as position, mandate_start, mandate_end, elected_by_meeting_id, created_at, updated_at
98 FROM board_members
99 WHERE building_id = $1
100 ORDER BY created_at DESC
101 "#,
102 )
103 .bind(building_id)
104 .fetch_all(&self.pool)
105 .await
106 .map_err(|e| format!("Database error: {}", e))?;
107
108 Ok(rows
109 .into_iter()
110 .map(|row| {
111 let position_str: String = row.get("position");
112 let position = match position_str.as_str() {
113 "president" => BoardPosition::President,
114 "treasurer" => BoardPosition::Treasurer,
115 _ => BoardPosition::Member,
116 };
117
118 BoardMember {
119 id: row.get("id"),
120 owner_id: row.get("owner_id"),
121 building_id: row.get("building_id"),
122 position,
123 mandate_start: row.get("mandate_start"),
124 mandate_end: row.get("mandate_end"),
125 elected_by_meeting_id: row.get("elected_by_meeting_id"),
126 created_at: row.get("created_at"),
127 updated_at: row.get("updated_at"),
128 }
129 })
130 .collect())
131 }
132
133 async fn find_active_by_building(&self, building_id: Uuid) -> Result<Vec<BoardMember>, String> {
134 let rows = sqlx::query(
135 r#"
136 SELECT id, owner_id, building_id, position::TEXT as position, mandate_start, mandate_end, elected_by_meeting_id, created_at, updated_at
137 FROM board_members
138 WHERE building_id = $1 AND is_active = true AND mandate_end > CURRENT_TIMESTAMP
139 ORDER BY mandate_start DESC
140 "#,
141 )
142 .bind(building_id)
143 .fetch_all(&self.pool)
144 .await
145 .map_err(|e| format!("Database error: {}", e))?;
146
147 Ok(rows
148 .into_iter()
149 .map(|row| {
150 let position_str: String = row.get("position");
151 let position = match position_str.as_str() {
152 "president" => BoardPosition::President,
153 "treasurer" => BoardPosition::Treasurer,
154 _ => BoardPosition::Member,
155 };
156
157 BoardMember {
158 id: row.get("id"),
159 owner_id: row.get("owner_id"),
160 building_id: row.get("building_id"),
161 position,
162 mandate_start: row.get("mandate_start"),
163 mandate_end: row.get("mandate_end"),
164 elected_by_meeting_id: row.get("elected_by_meeting_id"),
165 created_at: row.get("created_at"),
166 updated_at: row.get("updated_at"),
167 }
168 })
169 .collect())
170 }
171
172 async fn find_expiring_soon(
173 &self,
174 building_id: Uuid,
175 days_threshold: i32,
176 ) -> Result<Vec<BoardMember>, String> {
177 let rows = sqlx::query(
178 r#"
179 SELECT id, owner_id, building_id, position::TEXT as position, mandate_start, mandate_end, elected_by_meeting_id, created_at, updated_at
180 FROM board_members
181 WHERE building_id = $1
182 AND mandate_end > CURRENT_TIMESTAMP
183 AND mandate_end <= (CURRENT_TIMESTAMP + INTERVAL '1 day' * $2)
184 ORDER BY mandate_end ASC
185 "#,
186 )
187 .bind(building_id)
188 .bind(days_threshold)
189 .fetch_all(&self.pool)
190 .await
191 .map_err(|e| format!("Database error: {}", e))?;
192
193 Ok(rows
194 .into_iter()
195 .map(|row| {
196 let position_str: String = row.get("position");
197 let position = match position_str.as_str() {
198 "president" => BoardPosition::President,
199 "treasurer" => BoardPosition::Treasurer,
200 _ => BoardPosition::Member,
201 };
202
203 BoardMember {
204 id: row.get("id"),
205 owner_id: row.get("owner_id"),
206 building_id: row.get("building_id"),
207 position,
208 mandate_start: row.get("mandate_start"),
209 mandate_end: row.get("mandate_end"),
210 elected_by_meeting_id: row.get("elected_by_meeting_id"),
211 created_at: row.get("created_at"),
212 updated_at: row.get("updated_at"),
213 }
214 })
215 .collect())
216 }
217
218 async fn find_by_owner(&self, owner_id: Uuid) -> Result<Vec<BoardMember>, String> {
219 let rows = sqlx::query(
220 r#"
221 SELECT id, owner_id, building_id, position::TEXT as position, mandate_start, mandate_end, elected_by_meeting_id, created_at, updated_at
222 FROM board_members
223 WHERE owner_id = $1
224 ORDER BY mandate_start DESC
225 "#,
226 )
227 .bind(owner_id)
228 .fetch_all(&self.pool)
229 .await
230 .map_err(|e| format!("Database error: {}", e))?;
231
232 Ok(rows
233 .into_iter()
234 .map(|row| {
235 let position_str: String = row.get("position");
236 let position = match position_str.as_str() {
237 "president" => BoardPosition::President,
238 "treasurer" => BoardPosition::Treasurer,
239 _ => BoardPosition::Member,
240 };
241
242 BoardMember {
243 id: row.get("id"),
244 owner_id: row.get("owner_id"),
245 building_id: row.get("building_id"),
246 position,
247 mandate_start: row.get("mandate_start"),
248 mandate_end: row.get("mandate_end"),
249 elected_by_meeting_id: row.get("elected_by_meeting_id"),
250 created_at: row.get("created_at"),
251 updated_at: row.get("updated_at"),
252 }
253 })
254 .collect())
255 }
256
257 async fn find_by_owner_and_building(
258 &self,
259 owner_id: Uuid,
260 building_id: Uuid,
261 ) -> Result<Option<BoardMember>, String> {
262 let row = sqlx::query(
263 r#"
264 SELECT id, owner_id, building_id, position::TEXT as position, mandate_start, mandate_end, elected_by_meeting_id, created_at, updated_at
265 FROM board_members
266 WHERE owner_id = $1
267 AND building_id = $2
268 ORDER BY mandate_end DESC
269 LIMIT 1
270 "#,
271 )
272 .bind(owner_id)
273 .bind(building_id)
274 .fetch_optional(&self.pool)
275 .await
276 .map_err(|e| format!("Database error: {}", e))?;
277
278 Ok(row.map(|row| {
279 let position_str: String = row.get("position");
280 let position = match position_str.as_str() {
281 "president" => BoardPosition::President,
282 "treasurer" => BoardPosition::Treasurer,
283 _ => BoardPosition::Member,
284 };
285
286 BoardMember {
287 id: row.get("id"),
288 owner_id: row.get("owner_id"),
289 building_id: row.get("building_id"),
290 position,
291 mandate_start: row.get("mandate_start"),
292 mandate_end: row.get("mandate_end"),
293 elected_by_meeting_id: row.get("elected_by_meeting_id"),
294 created_at: row.get("created_at"),
295 updated_at: row.get("updated_at"),
296 }
297 }))
298 }
299
300 async fn has_active_mandate(&self, owner_id: Uuid, building_id: Uuid) -> Result<bool, String> {
301 let count: i64 = sqlx::query_scalar(
302 r#"
303 SELECT COUNT(*)
304 FROM board_members
305 WHERE owner_id = $1
306 AND building_id = $2
307 AND mandate_end > CURRENT_TIMESTAMP
308 "#,
309 )
310 .bind(owner_id)
311 .bind(building_id)
312 .fetch_one(&self.pool)
313 .await
314 .map_err(|e| format!("Database error: {}", e))?;
315
316 Ok(count > 0)
317 }
318
319 async fn update(&self, board_member: &BoardMember) -> Result<BoardMember, String> {
320 let position_str = match board_member.position {
321 BoardPosition::President => "president",
322 BoardPosition::Treasurer => "treasurer",
323 BoardPosition::Member => "member",
324 };
325
326 sqlx::query(
327 r#"
328 UPDATE board_members
329 SET position = $1::board_position,
330 mandate_start = $2,
331 mandate_end = $3,
332 elected_by_meeting_id = $4,
333 updated_at = $5
334 WHERE id = $6
335 "#,
336 )
337 .bind(position_str)
338 .bind(board_member.mandate_start)
339 .bind(board_member.mandate_end)
340 .bind(board_member.elected_by_meeting_id)
341 .bind(board_member.updated_at)
342 .bind(board_member.id)
343 .execute(&self.pool)
344 .await
345 .map_err(|e| format!("Database error: {}", e))?;
346
347 Ok(board_member.clone())
348 }
349
350 async fn delete(&self, id: Uuid) -> Result<bool, String> {
351 let result = sqlx::query(
352 r#"
353 DELETE FROM board_members
354 WHERE id = $1
355 "#,
356 )
357 .bind(id)
358 .execute(&self.pool)
359 .await
360 .map_err(|e| format!("Database error: {}", e))?;
361
362 Ok(result.rows_affected() > 0)
363 }
364
365 async fn count_active_by_building(&self, building_id: Uuid) -> Result<i64, String> {
366 let count: i64 = sqlx::query_scalar(
367 r#"
368 SELECT COUNT(*)
369 FROM board_members
370 WHERE building_id = $1 AND mandate_end > CURRENT_TIMESTAMP
371 "#,
372 )
373 .bind(building_id)
374 .fetch_one(&self.pool)
375 .await
376 .map_err(|e| format!("Database error: {}", e))?;
377
378 Ok(count)
379 }
380}