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