1use crate::application::ports::ConvocationRepository;
2use crate::domain::entities::{Convocation, ConvocationStatus, ConvocationType};
3use async_trait::async_trait;
4use chrono::{DateTime, Duration, Utc};
5use sqlx::PgPool;
6use uuid::Uuid;
7
8pub struct PostgresConvocationRepository {
10 pool: PgPool,
11}
12
13impl PostgresConvocationRepository {
14 pub fn new(pool: PgPool) -> Self {
15 Self { pool }
16 }
17
18 fn convocation_type_to_db(meeting_type: &ConvocationType) -> &'static str {
20 meeting_type.to_db_string()
21 }
22
23 fn convocation_type_from_db(s: &str) -> Result<ConvocationType, String> {
25 ConvocationType::from_db_string(s)
26 }
27
28 fn status_to_db(status: &ConvocationStatus) -> &'static str {
30 status.to_db_string()
31 }
32
33 fn status_from_db(s: &str) -> Result<ConvocationStatus, String> {
35 ConvocationStatus::from_db_string(s)
36 }
37}
38
39#[async_trait]
40impl ConvocationRepository for PostgresConvocationRepository {
41 async fn create(&self, convocation: &Convocation) -> Result<Convocation, String> {
42 let meeting_type_str = Self::convocation_type_to_db(&convocation.meeting_type);
43 let status_str = Self::status_to_db(&convocation.status);
44
45 let row = sqlx::query!(
46 r#"
47 INSERT INTO convocations (
48 id, acp_id, organization_id, building_id, meeting_id, meeting_type, meeting_date,
49 status, minimum_send_date, actual_send_date, scheduled_send_date,
50 pdf_file_path, language, total_recipients, opened_count,
51 will_attend_count, will_not_attend_count, reminder_sent_at,
52 first_meeting_id, no_quorum_required, created_at, updated_at, created_by
53 )
54 VALUES ($1, $2, $3, $4, $5, $6::TEXT::convocation_type, $7, $8::TEXT::convocation_status, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23)
55 RETURNING id, acp_id, organization_id, building_id, meeting_id, meeting_type::text AS "meeting_type!", meeting_date,
56 status::text AS "status!", minimum_send_date, actual_send_date, scheduled_send_date,
57 pdf_file_path, language, total_recipients, opened_count,
58 will_attend_count, will_not_attend_count, reminder_sent_at,
59 first_meeting_id, no_quorum_required, created_at, updated_at, created_by
60 "#,
61 convocation.id,
62 convocation.acp_id,
63 convocation.organization_id,
64 convocation.building_id,
65 convocation.meeting_id,
66 meeting_type_str,
67 convocation.meeting_date,
68 status_str,
69 convocation.minimum_send_date,
70 convocation.actual_send_date,
71 convocation.scheduled_send_date,
72 convocation.pdf_file_path,
73 convocation.language,
74 convocation.total_recipients,
75 convocation.opened_count,
76 convocation.will_attend_count,
77 convocation.will_not_attend_count,
78 convocation.reminder_sent_at,
79 convocation.first_meeting_id,
80 convocation.no_quorum_required,
81 convocation.created_at,
82 convocation.updated_at,
83 convocation.created_by,
84 )
85 .fetch_one(&self.pool)
86 .await
87 .map_err(|e| format!("Failed to create convocation: {}", e))?;
88
89 Ok(Convocation {
90 id: row.id,
91 acp_id: row.acp_id,
92 organization_id: row.organization_id,
93 building_id: row.building_id,
94 meeting_id: row.meeting_id,
95 meeting_type: Self::convocation_type_from_db(&row.meeting_type)?,
96 meeting_date: row.meeting_date,
97 status: Self::status_from_db(&row.status)?,
98 minimum_send_date: row.minimum_send_date,
99 actual_send_date: row.actual_send_date,
100 scheduled_send_date: row.scheduled_send_date,
101 pdf_file_path: row.pdf_file_path,
102 language: row.language,
103 total_recipients: row.total_recipients,
104 opened_count: row.opened_count,
105 will_attend_count: row.will_attend_count,
106 will_not_attend_count: row.will_not_attend_count,
107 reminder_sent_at: row.reminder_sent_at,
108 first_meeting_id: row.first_meeting_id,
109 no_quorum_required: row.no_quorum_required,
110 created_at: row.created_at,
111 updated_at: row.updated_at,
112 created_by: row.created_by,
113 })
114 }
115
116 async fn find_by_id(&self, id: Uuid) -> Result<Option<Convocation>, String> {
117 let row = sqlx::query!(
118 r#"
119 SELECT id, acp_id, organization_id, building_id, meeting_id, meeting_type::text AS "meeting_type!", meeting_date,
120 status::text AS "status!", minimum_send_date, actual_send_date, scheduled_send_date,
121 pdf_file_path, language, total_recipients, opened_count,
122 will_attend_count, will_not_attend_count, reminder_sent_at,
123 first_meeting_id, no_quorum_required, created_at, updated_at, created_by
124 FROM convocations
125 WHERE id = $1
126 "#,
127 id
128 )
129 .fetch_optional(&self.pool)
130 .await
131 .map_err(|e| format!("Failed to find convocation by id: {}", e))?;
132
133 match row {
134 Some(row) => Ok(Some(Convocation {
135 id: row.id,
136 acp_id: row.acp_id,
137 organization_id: row.organization_id,
138 building_id: row.building_id,
139 meeting_id: row.meeting_id,
140 meeting_type: Self::convocation_type_from_db(&row.meeting_type)?,
141 meeting_date: row.meeting_date,
142 status: Self::status_from_db(&row.status)?,
143 minimum_send_date: row.minimum_send_date,
144 actual_send_date: row.actual_send_date,
145 scheduled_send_date: row.scheduled_send_date,
146 pdf_file_path: row.pdf_file_path,
147 language: row.language,
148 total_recipients: row.total_recipients,
149 opened_count: row.opened_count,
150 will_attend_count: row.will_attend_count,
151 will_not_attend_count: row.will_not_attend_count,
152 reminder_sent_at: row.reminder_sent_at,
153 first_meeting_id: row.first_meeting_id,
154 no_quorum_required: row.no_quorum_required,
155 created_at: row.created_at,
156 updated_at: row.updated_at,
157 created_by: row.created_by,
158 })),
159 None => Ok(None),
160 }
161 }
162
163 async fn find_by_meeting_id(&self, meeting_id: Uuid) -> Result<Option<Convocation>, String> {
164 let row = sqlx::query!(
165 r#"
166 SELECT id, acp_id, organization_id, building_id, meeting_id, meeting_type::text AS "meeting_type!", meeting_date,
167 status::text AS "status!", minimum_send_date, actual_send_date, scheduled_send_date,
168 pdf_file_path, language, total_recipients, opened_count,
169 will_attend_count, will_not_attend_count, reminder_sent_at,
170 first_meeting_id, no_quorum_required, created_at, updated_at, created_by
171 FROM convocations
172 WHERE meeting_id = $1
173 "#,
174 meeting_id
175 )
176 .fetch_optional(&self.pool)
177 .await
178 .map_err(|e| format!("Failed to find convocation by meeting_id: {}", e))?;
179
180 match row {
181 Some(row) => Ok(Some(Convocation {
182 id: row.id,
183 acp_id: row.acp_id,
184 organization_id: row.organization_id,
185 building_id: row.building_id,
186 meeting_id: row.meeting_id,
187 meeting_type: Self::convocation_type_from_db(&row.meeting_type)?,
188 meeting_date: row.meeting_date,
189 status: Self::status_from_db(&row.status)?,
190 minimum_send_date: row.minimum_send_date,
191 actual_send_date: row.actual_send_date,
192 scheduled_send_date: row.scheduled_send_date,
193 pdf_file_path: row.pdf_file_path,
194 language: row.language,
195 total_recipients: row.total_recipients,
196 opened_count: row.opened_count,
197 will_attend_count: row.will_attend_count,
198 will_not_attend_count: row.will_not_attend_count,
199 reminder_sent_at: row.reminder_sent_at,
200 first_meeting_id: row.first_meeting_id,
201 no_quorum_required: row.no_quorum_required,
202 created_at: row.created_at,
203 updated_at: row.updated_at,
204 created_by: row.created_by,
205 })),
206 None => Ok(None),
207 }
208 }
209
210 async fn find_by_building(&self, building_id: Uuid) -> Result<Vec<Convocation>, String> {
211 let rows = sqlx::query!(
212 r#"
213 SELECT id, acp_id, organization_id, building_id, meeting_id, meeting_type::text AS "meeting_type!", meeting_date,
214 status::text AS "status!", minimum_send_date, actual_send_date, scheduled_send_date,
215 pdf_file_path, language, total_recipients, opened_count,
216 will_attend_count, will_not_attend_count, reminder_sent_at,
217 first_meeting_id, no_quorum_required, created_at, updated_at, created_by
218 FROM convocations
219 WHERE building_id = $1
220 ORDER BY meeting_date DESC
221 "#,
222 building_id
223 )
224 .fetch_all(&self.pool)
225 .await
226 .map_err(|e| format!("Failed to find convocations by building: {}", e))?;
227
228 rows.into_iter()
229 .map(|row| {
230 Ok(Convocation {
231 id: row.id,
232 acp_id: row.acp_id,
233 organization_id: row.organization_id,
234 building_id: row.building_id,
235 meeting_id: row.meeting_id,
236 meeting_type: Self::convocation_type_from_db(&row.meeting_type)?,
237 meeting_date: row.meeting_date,
238 status: Self::status_from_db(&row.status)?,
239 minimum_send_date: row.minimum_send_date,
240 actual_send_date: row.actual_send_date,
241 scheduled_send_date: row.scheduled_send_date,
242 pdf_file_path: row.pdf_file_path,
243 language: row.language,
244 total_recipients: row.total_recipients,
245 opened_count: row.opened_count,
246 will_attend_count: row.will_attend_count,
247 will_not_attend_count: row.will_not_attend_count,
248 reminder_sent_at: row.reminder_sent_at,
249 first_meeting_id: row.first_meeting_id,
250 no_quorum_required: row.no_quorum_required,
251 created_at: row.created_at,
252 updated_at: row.updated_at,
253 created_by: row.created_by,
254 })
255 })
256 .collect()
257 }
258
259 async fn find_by_organization(
260 &self,
261 organization_id: Uuid,
262 ) -> Result<Vec<Convocation>, String> {
263 let rows = sqlx::query!(
264 r#"
265 SELECT id, acp_id, organization_id, building_id, meeting_id, meeting_type::text AS "meeting_type!", meeting_date,
266 status::text AS "status!", minimum_send_date, actual_send_date, scheduled_send_date,
267 pdf_file_path, language, total_recipients, opened_count,
268 will_attend_count, will_not_attend_count, reminder_sent_at,
269 first_meeting_id, no_quorum_required, created_at, updated_at, created_by
270 FROM convocations
271 WHERE acp_id IN (SELECT id FROM acps WHERE organization_id = $1)
272 ORDER BY meeting_date DESC
273 "#,
274 organization_id
275 )
276 .fetch_all(&self.pool)
277 .await
278 .map_err(|e| format!("Failed to find convocations by organization: {}", e))?;
279
280 rows.into_iter()
281 .map(|row| {
282 Ok(Convocation {
283 id: row.id,
284 acp_id: row.acp_id,
285 organization_id: row.organization_id,
286 building_id: row.building_id,
287 meeting_id: row.meeting_id,
288 meeting_type: Self::convocation_type_from_db(&row.meeting_type)?,
289 meeting_date: row.meeting_date,
290 status: Self::status_from_db(&row.status)?,
291 minimum_send_date: row.minimum_send_date,
292 actual_send_date: row.actual_send_date,
293 scheduled_send_date: row.scheduled_send_date,
294 pdf_file_path: row.pdf_file_path,
295 language: row.language,
296 total_recipients: row.total_recipients,
297 opened_count: row.opened_count,
298 will_attend_count: row.will_attend_count,
299 will_not_attend_count: row.will_not_attend_count,
300 reminder_sent_at: row.reminder_sent_at,
301 first_meeting_id: row.first_meeting_id,
302 no_quorum_required: row.no_quorum_required,
303 created_at: row.created_at,
304 updated_at: row.updated_at,
305 created_by: row.created_by,
306 })
307 })
308 .collect()
309 }
310
311 async fn find_by_status(
312 &self,
313 organization_id: Uuid,
314 status: ConvocationStatus,
315 ) -> Result<Vec<Convocation>, String> {
316 let status_str = Self::status_to_db(&status);
317
318 let rows = sqlx::query!(
319 r#"
320 SELECT id, acp_id, organization_id, building_id, meeting_id, meeting_type::text AS "meeting_type!", meeting_date,
321 status::text AS "status!", minimum_send_date, actual_send_date, scheduled_send_date,
322 pdf_file_path, language, total_recipients, opened_count,
323 will_attend_count, will_not_attend_count, reminder_sent_at,
324 first_meeting_id, no_quorum_required, created_at, updated_at, created_by
325 FROM convocations
326 WHERE acp_id IN (SELECT id FROM acps WHERE organization_id = $1) AND status = $2::TEXT::convocation_status
327 ORDER BY meeting_date DESC
328 "#,
329 organization_id,
330 status_str
331 )
332 .fetch_all(&self.pool)
333 .await
334 .map_err(|e| format!("Failed to find convocations by status: {}", e))?;
335
336 rows.into_iter()
337 .map(|row| {
338 Ok(Convocation {
339 id: row.id,
340 acp_id: row.acp_id,
341 organization_id: row.organization_id,
342 building_id: row.building_id,
343 meeting_id: row.meeting_id,
344 meeting_type: Self::convocation_type_from_db(&row.meeting_type)?,
345 meeting_date: row.meeting_date,
346 status: Self::status_from_db(&row.status)?,
347 minimum_send_date: row.minimum_send_date,
348 actual_send_date: row.actual_send_date,
349 scheduled_send_date: row.scheduled_send_date,
350 pdf_file_path: row.pdf_file_path,
351 language: row.language,
352 total_recipients: row.total_recipients,
353 opened_count: row.opened_count,
354 will_attend_count: row.will_attend_count,
355 will_not_attend_count: row.will_not_attend_count,
356 reminder_sent_at: row.reminder_sent_at,
357 first_meeting_id: row.first_meeting_id,
358 no_quorum_required: row.no_quorum_required,
359 created_at: row.created_at,
360 updated_at: row.updated_at,
361 created_by: row.created_by,
362 })
363 })
364 .collect()
365 }
366
367 async fn find_pending_scheduled(&self, now: DateTime<Utc>) -> Result<Vec<Convocation>, String> {
368 let rows = sqlx::query!(
369 r#"
370 SELECT id, acp_id, organization_id, building_id, meeting_id, meeting_type::text AS "meeting_type!", meeting_date,
371 status::text AS "status!", minimum_send_date, actual_send_date, scheduled_send_date,
372 pdf_file_path, language, total_recipients, opened_count,
373 will_attend_count, will_not_attend_count, reminder_sent_at,
374 first_meeting_id, no_quorum_required, created_at, updated_at, created_by
375 FROM convocations
376 WHERE status = 'scheduled'
377 AND scheduled_send_date IS NOT NULL
378 AND scheduled_send_date <= $1
379 ORDER BY scheduled_send_date ASC
380 "#,
381 now
382 )
383 .fetch_all(&self.pool)
384 .await
385 .map_err(|e| format!("Failed to find pending scheduled convocations: {}", e))?;
386
387 rows.into_iter()
388 .map(|row| {
389 Ok(Convocation {
390 id: row.id,
391 acp_id: row.acp_id,
392 organization_id: row.organization_id,
393 building_id: row.building_id,
394 meeting_id: row.meeting_id,
395 meeting_type: Self::convocation_type_from_db(&row.meeting_type)?,
396 meeting_date: row.meeting_date,
397 status: Self::status_from_db(&row.status)?,
398 minimum_send_date: row.minimum_send_date,
399 actual_send_date: row.actual_send_date,
400 scheduled_send_date: row.scheduled_send_date,
401 pdf_file_path: row.pdf_file_path,
402 language: row.language,
403 total_recipients: row.total_recipients,
404 opened_count: row.opened_count,
405 will_attend_count: row.will_attend_count,
406 will_not_attend_count: row.will_not_attend_count,
407 reminder_sent_at: row.reminder_sent_at,
408 first_meeting_id: row.first_meeting_id,
409 no_quorum_required: row.no_quorum_required,
410 created_at: row.created_at,
411 updated_at: row.updated_at,
412 created_by: row.created_by,
413 })
414 })
415 .collect()
416 }
417
418 async fn find_needing_reminder(&self, now: DateTime<Utc>) -> Result<Vec<Convocation>, String> {
419 let three_days_from_now = now + Duration::days(3);
424
425 let rows = sqlx::query!(
426 r#"
427 SELECT id, acp_id, organization_id, building_id, meeting_id, meeting_type::text AS "meeting_type!", meeting_date,
428 status::text AS "status!", minimum_send_date, actual_send_date, scheduled_send_date,
429 pdf_file_path, language, total_recipients, opened_count,
430 will_attend_count, will_not_attend_count, reminder_sent_at,
431 first_meeting_id, no_quorum_required, created_at, updated_at, created_by
432 FROM convocations
433 WHERE status = 'sent'
434 AND meeting_date >= $1
435 AND meeting_date <= $2
436 AND reminder_sent_at IS NULL
437 ORDER BY meeting_date ASC
438 "#,
439 now,
440 three_days_from_now
441 )
442 .fetch_all(&self.pool)
443 .await
444 .map_err(|e| format!("Failed to find convocations needing reminder: {}", e))?;
445
446 rows.into_iter()
447 .map(|row| {
448 Ok(Convocation {
449 id: row.id,
450 acp_id: row.acp_id,
451 organization_id: row.organization_id,
452 building_id: row.building_id,
453 meeting_id: row.meeting_id,
454 meeting_type: Self::convocation_type_from_db(&row.meeting_type)?,
455 meeting_date: row.meeting_date,
456 status: Self::status_from_db(&row.status)?,
457 minimum_send_date: row.minimum_send_date,
458 actual_send_date: row.actual_send_date,
459 scheduled_send_date: row.scheduled_send_date,
460 pdf_file_path: row.pdf_file_path,
461 language: row.language,
462 total_recipients: row.total_recipients,
463 opened_count: row.opened_count,
464 will_attend_count: row.will_attend_count,
465 will_not_attend_count: row.will_not_attend_count,
466 reminder_sent_at: row.reminder_sent_at,
467 first_meeting_id: row.first_meeting_id,
468 no_quorum_required: row.no_quorum_required,
469 created_at: row.created_at,
470 updated_at: row.updated_at,
471 created_by: row.created_by,
472 })
473 })
474 .collect()
475 }
476
477 async fn update(&self, convocation: &Convocation) -> Result<Convocation, String> {
478 let meeting_type_str = Self::convocation_type_to_db(&convocation.meeting_type);
479 let status_str = Self::status_to_db(&convocation.status);
480
481 let row = sqlx::query!(
482 r#"
483 UPDATE convocations
484 SET organization_id = $2, building_id = $3, meeting_id = $4, meeting_type = $5::TEXT::convocation_type, meeting_date = $6,
485 status = $7::TEXT::convocation_status, minimum_send_date = $8, actual_send_date = $9, scheduled_send_date = $10,
486 pdf_file_path = $11, language = $12, total_recipients = $13, opened_count = $14,
487 will_attend_count = $15, will_not_attend_count = $16, reminder_sent_at = $17,
488 first_meeting_id = $18, no_quorum_required = $19, updated_at = $20
489 WHERE id = $1
490 RETURNING id, acp_id, organization_id, building_id, meeting_id, meeting_type::text AS "meeting_type!", meeting_date,
491 status::text AS "status!", minimum_send_date, actual_send_date, scheduled_send_date,
492 pdf_file_path, language, total_recipients, opened_count,
493 will_attend_count, will_not_attend_count, reminder_sent_at,
494 first_meeting_id, no_quorum_required, created_at, updated_at, created_by
495 "#,
496 convocation.id,
497 convocation.organization_id,
498 convocation.building_id,
499 convocation.meeting_id,
500 meeting_type_str,
501 convocation.meeting_date,
502 status_str,
503 convocation.minimum_send_date,
504 convocation.actual_send_date,
505 convocation.scheduled_send_date,
506 convocation.pdf_file_path,
507 convocation.language,
508 convocation.total_recipients,
509 convocation.opened_count,
510 convocation.will_attend_count,
511 convocation.will_not_attend_count,
512 convocation.reminder_sent_at,
513 convocation.first_meeting_id,
514 convocation.no_quorum_required,
515 convocation.updated_at,
516 )
517 .fetch_one(&self.pool)
518 .await
519 .map_err(|e| format!("Failed to update convocation: {}", e))?;
520
521 Ok(Convocation {
522 id: row.id,
523 acp_id: row.acp_id,
524 organization_id: row.organization_id,
525 building_id: row.building_id,
526 meeting_id: row.meeting_id,
527 meeting_type: Self::convocation_type_from_db(&row.meeting_type)?,
528 meeting_date: row.meeting_date,
529 status: Self::status_from_db(&row.status)?,
530 minimum_send_date: row.minimum_send_date,
531 actual_send_date: row.actual_send_date,
532 scheduled_send_date: row.scheduled_send_date,
533 pdf_file_path: row.pdf_file_path,
534 language: row.language,
535 total_recipients: row.total_recipients,
536 opened_count: row.opened_count,
537 will_attend_count: row.will_attend_count,
538 will_not_attend_count: row.will_not_attend_count,
539 reminder_sent_at: row.reminder_sent_at,
540 first_meeting_id: row.first_meeting_id,
541 no_quorum_required: row.no_quorum_required,
542 created_at: row.created_at,
543 updated_at: row.updated_at,
544 created_by: row.created_by,
545 })
546 }
547
548 async fn delete(&self, id: Uuid) -> Result<bool, String> {
549 let result = sqlx::query!(
550 r#"
551 DELETE FROM convocations
552 WHERE id = $1
553 "#,
554 id
555 )
556 .execute(&self.pool)
557 .await
558 .map_err(|e| format!("Failed to delete convocation: {}", e))?;
559
560 Ok(result.rows_affected() > 0)
561 }
562
563 async fn count_by_building(&self, building_id: Uuid) -> Result<i64, String> {
564 let row = sqlx::query!(
565 r#"
566 SELECT COUNT(*) AS "count!"
567 FROM convocations
568 WHERE building_id = $1
569 "#,
570 building_id
571 )
572 .fetch_one(&self.pool)
573 .await
574 .map_err(|e| format!("Failed to count convocations by building: {}", e))?;
575
576 Ok(row.count)
577 }
578
579 async fn count_by_status(
580 &self,
581 organization_id: Uuid,
582 status: ConvocationStatus,
583 ) -> Result<i64, String> {
584 let status_str = Self::status_to_db(&status);
585
586 let row = sqlx::query!(
587 r#"
588 SELECT COUNT(*) AS "count!"
589 FROM convocations
590 WHERE acp_id IN (SELECT id FROM acps WHERE organization_id = $1) AND status = $2::TEXT::convocation_status
591 "#,
592 organization_id,
593 status_str
594 )
595 .fetch_one(&self.pool)
596 .await
597 .map_err(|e| format!("Failed to count convocations by status: {}", e))?;
598
599 Ok(row.count)
600 }
601}