1use crate::application::dto::etat_date_dto::EtatDateStatsResponse;
2use crate::application::dto::PageRequest;
3use crate::application::ports::EtatDateRepository;
4use crate::domain::entities::{EtatDate, EtatDateLanguage, EtatDateStatus};
5use crate::infrastructure::database::pool::DbPool;
6use async_trait::async_trait;
7
8use sqlx::Row;
9use uuid::Uuid;
10
11pub struct PostgresEtatDateRepository {
12 pool: DbPool,
13}
14
15impl PostgresEtatDateRepository {
16 pub fn new(pool: DbPool) -> Self {
17 Self { pool }
18 }
19}
20
21#[async_trait]
22impl EtatDateRepository for PostgresEtatDateRepository {
23 async fn create(&self, etat_date: &EtatDate) -> Result<EtatDate, String> {
24 let status_str = match etat_date.status {
25 EtatDateStatus::Requested => "requested",
26 EtatDateStatus::InProgress => "in_progress",
27 EtatDateStatus::Generated => "generated",
28 EtatDateStatus::Delivered => "delivered",
29 EtatDateStatus::Expired => "expired",
30 };
31
32 let language_str = match etat_date.language {
33 EtatDateLanguage::Fr => "fr",
34 EtatDateLanguage::Nl => "nl",
35 EtatDateLanguage::De => "de",
36 };
37
38 sqlx::query(
39 r#"
40 INSERT INTO etats_dates (
41 id, organization_id, building_id, unit_id,
42 reference_date, requested_date, generated_date, delivered_date,
43 status, language, reference_number,
44 notary_name, notary_email, notary_phone,
45 building_name, building_address, unit_number, unit_floor, unit_area,
46 ordinary_charges_quota, extraordinary_charges_quota,
47 owner_balance, arrears_amount, monthly_provision_amount,
48 total_balance, approved_works_unpaid,
49 additional_data, pdf_file_path,
50 created_at, updated_at
51 )
52 VALUES (
53 $1, $2, $3, $4,
54 $5, $6, $7, $8,
55 CAST($9 AS etat_date_status), CAST($10 AS etat_date_language), $11,
56 $12, $13, $14,
57 $15, $16, $17, $18, $19,
58 $20, $21,
59 $22, $23, $24,
60 $25, $26,
61 $27, $28,
62 $29, $30
63 )
64 "#,
65 )
66 .bind(etat_date.id)
67 .bind(etat_date.organization_id)
68 .bind(etat_date.building_id)
69 .bind(etat_date.unit_id)
70 .bind(etat_date.reference_date)
71 .bind(etat_date.requested_date)
72 .bind(etat_date.generated_date)
73 .bind(etat_date.delivered_date)
74 .bind(status_str)
75 .bind(language_str)
76 .bind(&etat_date.reference_number)
77 .bind(&etat_date.notary_name)
78 .bind(&etat_date.notary_email)
79 .bind(&etat_date.notary_phone)
80 .bind(&etat_date.building_name)
81 .bind(&etat_date.building_address)
82 .bind(&etat_date.unit_number)
83 .bind(&etat_date.unit_floor)
84 .bind(etat_date.unit_area)
85 .bind(etat_date.ordinary_charges_quota)
86 .bind(etat_date.extraordinary_charges_quota)
87 .bind(etat_date.owner_balance)
88 .bind(etat_date.arrears_amount)
89 .bind(etat_date.monthly_provision_amount)
90 .bind(etat_date.total_balance)
91 .bind(etat_date.approved_works_unpaid)
92 .bind(&etat_date.additional_data)
93 .bind(&etat_date.pdf_file_path)
94 .bind(etat_date.created_at)
95 .bind(etat_date.updated_at)
96 .execute(&self.pool)
97 .await
98 .map_err(|e| format!("Database error: {}", e))?;
99
100 Ok(etat_date.clone())
101 }
102
103 async fn find_by_id(&self, id: Uuid) -> Result<Option<EtatDate>, String> {
104 let row = sqlx::query(
105 r#"
106 SELECT
107 id, organization_id, building_id, unit_id,
108 reference_date, requested_date, generated_date, delivered_date,
109 status::text AS status, language::text AS language, reference_number,
110 notary_name, notary_email, notary_phone,
111 building_name, building_address, unit_number, unit_floor, unit_area,
112 ordinary_charges_quota, extraordinary_charges_quota,
113 owner_balance, arrears_amount, monthly_provision_amount,
114 total_balance, approved_works_unpaid,
115 additional_data, pdf_file_path,
116 created_at, updated_at
117 FROM etats_dates
118 WHERE id = $1
119 "#,
120 )
121 .bind(id)
122 .fetch_optional(&self.pool)
123 .await
124 .map_err(|e| format!("Database error: {}", e))?;
125
126 Ok(row.map(|row| self.row_to_etat_date(row)))
127 }
128
129 async fn find_by_reference_number(
130 &self,
131 reference_number: &str,
132 ) -> Result<Option<EtatDate>, String> {
133 let row = sqlx::query(
134 r#"
135 SELECT
136 id, organization_id, building_id, unit_id,
137 reference_date, requested_date, generated_date, delivered_date,
138 status::text AS status, language::text AS language, reference_number,
139 notary_name, notary_email, notary_phone,
140 building_name, building_address, unit_number, unit_floor, unit_area,
141 ordinary_charges_quota, extraordinary_charges_quota,
142 owner_balance, arrears_amount, monthly_provision_amount,
143 total_balance, approved_works_unpaid,
144 additional_data, pdf_file_path,
145 created_at, updated_at
146 FROM etats_dates
147 WHERE reference_number = $1
148 "#,
149 )
150 .bind(reference_number)
151 .fetch_optional(&self.pool)
152 .await
153 .map_err(|e| format!("Database error: {}", e))?;
154
155 Ok(row.map(|row| self.row_to_etat_date(row)))
156 }
157
158 async fn find_by_unit(&self, unit_id: Uuid) -> Result<Vec<EtatDate>, String> {
159 let rows = sqlx::query(
160 r#"
161 SELECT
162 id, organization_id, building_id, unit_id,
163 reference_date, requested_date, generated_date, delivered_date,
164 status::text AS status, language::text AS language, reference_number,
165 notary_name, notary_email, notary_phone,
166 building_name, building_address, unit_number, unit_floor, unit_area,
167 ordinary_charges_quota, extraordinary_charges_quota,
168 owner_balance, arrears_amount, monthly_provision_amount,
169 total_balance, approved_works_unpaid,
170 additional_data, pdf_file_path,
171 created_at, updated_at
172 FROM etats_dates
173 WHERE unit_id = $1
174 ORDER BY requested_date DESC
175 "#,
176 )
177 .bind(unit_id)
178 .fetch_all(&self.pool)
179 .await
180 .map_err(|e| format!("Database error: {}", e))?;
181
182 Ok(rows
183 .into_iter()
184 .map(|row| self.row_to_etat_date(row))
185 .collect())
186 }
187
188 async fn find_by_building(&self, building_id: Uuid) -> Result<Vec<EtatDate>, String> {
189 let rows = sqlx::query(
190 r#"
191 SELECT
192 id, organization_id, building_id, unit_id,
193 reference_date, requested_date, generated_date, delivered_date,
194 status::text AS status, language::text AS language, reference_number,
195 notary_name, notary_email, notary_phone,
196 building_name, building_address, unit_number, unit_floor, unit_area,
197 ordinary_charges_quota, extraordinary_charges_quota,
198 owner_balance, arrears_amount, monthly_provision_amount,
199 total_balance, approved_works_unpaid,
200 additional_data, pdf_file_path,
201 created_at, updated_at
202 FROM etats_dates
203 WHERE building_id = $1
204 ORDER BY requested_date DESC
205 "#,
206 )
207 .bind(building_id)
208 .fetch_all(&self.pool)
209 .await
210 .map_err(|e| format!("Database error: {}", e))?;
211
212 Ok(rows
213 .into_iter()
214 .map(|row| self.row_to_etat_date(row))
215 .collect())
216 }
217
218 async fn find_all_paginated(
219 &self,
220 page_request: &PageRequest,
221 organization_id: Option<Uuid>,
222 status: Option<EtatDateStatus>,
223 ) -> Result<(Vec<EtatDate>, i64), String> {
224 let offset = (page_request.page - 1) * page_request.per_page;
225
226 let mut query_str = String::from(
227 r#"
228 SELECT
229 id, organization_id, building_id, unit_id,
230 reference_date, requested_date, generated_date, delivered_date,
231 status::text AS status, language::text AS language, reference_number,
232 notary_name, notary_email, notary_phone,
233 building_name, building_address, unit_number, unit_floor, unit_area,
234 ordinary_charges_quota, extraordinary_charges_quota,
235 owner_balance, arrears_amount, monthly_provision_amount,
236 total_balance, approved_works_unpaid,
237 additional_data, pdf_file_path,
238 created_at, updated_at
239 FROM etats_dates
240 WHERE 1=1
241 "#,
242 );
243
244 let mut count_query_str = String::from("SELECT COUNT(*) FROM etats_dates WHERE 1=1");
245
246 if organization_id.is_some() {
247 query_str.push_str(" AND organization_id = $1");
248 count_query_str.push_str(" AND organization_id = $1");
249 }
250
251 if status.is_some() {
252 let param_num = if organization_id.is_some() {
253 "$2"
254 } else {
255 "$1"
256 };
257 query_str.push_str(&format!(
258 " AND status = CAST({} AS etat_date_status)",
259 param_num
260 ));
261 count_query_str.push_str(&format!(
262 " AND status = CAST({} AS etat_date_status)",
263 param_num
264 ));
265 }
266
267 query_str.push_str(" ORDER BY requested_date DESC");
268
269 let param_num_offset = match (organization_id.is_some(), status.is_some()) {
270 (true, true) => "$3",
271 (true, false) | (false, true) => "$2",
272 (false, false) => "$1",
273 };
274 let param_num_limit = match (organization_id.is_some(), status.is_some()) {
275 (true, true) => "$4",
276 (true, false) | (false, true) => "$3",
277 (false, false) => "$2",
278 };
279
280 query_str.push_str(&format!(
281 " OFFSET {} LIMIT {}",
282 param_num_offset, param_num_limit
283 ));
284
285 let mut query = sqlx::query(&query_str);
287 let mut count_query = sqlx::query_scalar::<_, i64>(&count_query_str);
288
289 if let Some(org_id) = organization_id {
290 query = query.bind(org_id);
291 count_query = count_query.bind(org_id);
292 }
293
294 if let Some(st) = &status {
295 let status_str = match st {
296 EtatDateStatus::Requested => "requested",
297 EtatDateStatus::InProgress => "in_progress",
298 EtatDateStatus::Generated => "generated",
299 EtatDateStatus::Delivered => "delivered",
300 EtatDateStatus::Expired => "expired",
301 };
302 query = query.bind(status_str);
303 count_query = count_query.bind(status_str);
304 }
305
306 query = query.bind(offset as i64).bind(page_request.per_page as i64);
307
308 let rows = query
309 .fetch_all(&self.pool)
310 .await
311 .map_err(|e| format!("Database error: {}", e))?;
312
313 let total = count_query
314 .fetch_one(&self.pool)
315 .await
316 .map_err(|e| format!("Database error: {}", e))?;
317
318 let etats = rows
319 .into_iter()
320 .map(|row| self.row_to_etat_date(row))
321 .collect();
322
323 Ok((etats, total))
324 }
325
326 async fn find_overdue(&self, organization_id: Uuid) -> Result<Vec<EtatDate>, String> {
327 let rows = sqlx::query(
328 r#"
329 SELECT
330 id, organization_id, building_id, unit_id,
331 reference_date, requested_date, generated_date, delivered_date,
332 status::text AS status, language::text AS language, reference_number,
333 notary_name, notary_email, notary_phone,
334 building_name, building_address, unit_number, unit_floor, unit_area,
335 ordinary_charges_quota, extraordinary_charges_quota,
336 owner_balance, arrears_amount, monthly_provision_amount,
337 total_balance, approved_works_unpaid,
338 additional_data, pdf_file_path,
339 created_at, updated_at
340 FROM etats_dates
341 WHERE organization_id = $1
342 AND status IN ('requested', 'in_progress')
343 AND requested_date < NOW() - INTERVAL '10 days'
344 ORDER BY requested_date ASC
345 "#,
346 )
347 .bind(organization_id)
348 .fetch_all(&self.pool)
349 .await
350 .map_err(|e| format!("Database error: {}", e))?;
351
352 Ok(rows
353 .into_iter()
354 .map(|row| self.row_to_etat_date(row))
355 .collect())
356 }
357
358 async fn find_expired(&self, organization_id: Uuid) -> Result<Vec<EtatDate>, String> {
359 let rows = sqlx::query(
360 r#"
361 SELECT
362 id, organization_id, building_id, unit_id,
363 reference_date, requested_date, generated_date, delivered_date,
364 status::text AS status, language::text AS language, reference_number,
365 notary_name, notary_email, notary_phone,
366 building_name, building_address, unit_number, unit_floor, unit_area,
367 ordinary_charges_quota, extraordinary_charges_quota,
368 owner_balance, arrears_amount, monthly_provision_amount,
369 total_balance, approved_works_unpaid,
370 additional_data, pdf_file_path,
371 created_at, updated_at
372 FROM etats_dates
373 WHERE organization_id = $1
374 AND reference_date < NOW() - INTERVAL '90 days'
375 ORDER BY reference_date ASC
376 "#,
377 )
378 .bind(organization_id)
379 .fetch_all(&self.pool)
380 .await
381 .map_err(|e| format!("Database error: {}", e))?;
382
383 Ok(rows
384 .into_iter()
385 .map(|row| self.row_to_etat_date(row))
386 .collect())
387 }
388
389 async fn update(&self, etat_date: &EtatDate) -> Result<EtatDate, String> {
390 let status_str = match etat_date.status {
391 EtatDateStatus::Requested => "requested",
392 EtatDateStatus::InProgress => "in_progress",
393 EtatDateStatus::Generated => "generated",
394 EtatDateStatus::Delivered => "delivered",
395 EtatDateStatus::Expired => "expired",
396 };
397
398 sqlx::query(
399 r#"
400 UPDATE etats_dates
401 SET generated_date = $1,
402 delivered_date = $2,
403 status = CAST($3 AS etat_date_status),
404 owner_balance = $4,
405 arrears_amount = $5,
406 monthly_provision_amount = $6,
407 total_balance = $7,
408 approved_works_unpaid = $8,
409 additional_data = $9,
410 pdf_file_path = $10,
411 updated_at = $11
412 WHERE id = $12
413 "#,
414 )
415 .bind(etat_date.generated_date)
416 .bind(etat_date.delivered_date)
417 .bind(status_str)
418 .bind(etat_date.owner_balance)
419 .bind(etat_date.arrears_amount)
420 .bind(etat_date.monthly_provision_amount)
421 .bind(etat_date.total_balance)
422 .bind(etat_date.approved_works_unpaid)
423 .bind(&etat_date.additional_data)
424 .bind(&etat_date.pdf_file_path)
425 .bind(etat_date.updated_at)
426 .bind(etat_date.id)
427 .execute(&self.pool)
428 .await
429 .map_err(|e| format!("Database error: {}", e))?;
430
431 Ok(etat_date.clone())
432 }
433
434 async fn delete(&self, id: Uuid) -> Result<bool, String> {
435 let result = sqlx::query("DELETE FROM etats_dates WHERE id = $1")
436 .bind(id)
437 .execute(&self.pool)
438 .await
439 .map_err(|e| format!("Database error: {}", e))?;
440
441 Ok(result.rows_affected() > 0)
442 }
443
444 async fn get_stats(&self, organization_id: Uuid) -> Result<EtatDateStatsResponse, String> {
445 let stats = sqlx::query(
446 r#"
447 SELECT
448 COUNT(*) as total_requests,
449 COUNT(*) FILTER (WHERE status = 'requested') as requested_count,
450 COUNT(*) FILTER (WHERE status = 'in_progress') as in_progress_count,
451 COUNT(*) FILTER (WHERE status = 'generated') as generated_count,
452 COUNT(*) FILTER (WHERE status = 'delivered') as delivered_count,
453 COUNT(*) FILTER (WHERE reference_date < NOW() - INTERVAL '90 days') as expired_count,
454 COUNT(*) FILTER (WHERE status IN ('requested', 'in_progress') AND requested_date < NOW() - INTERVAL '10 days') as overdue_count,
455 COALESCE(AVG(EXTRACT(EPOCH FROM (COALESCE(generated_date, NOW()) - requested_date)) / 86400), 0) as avg_processing_days
456 FROM etats_dates
457 WHERE organization_id = $1
458 "#,
459 )
460 .bind(organization_id)
461 .fetch_one(&self.pool)
462 .await
463 .map_err(|e| format!("Database error: {}", e))?;
464
465 Ok(EtatDateStatsResponse {
466 total_requests: stats.get("total_requests"),
467 requested_count: stats.get("requested_count"),
468 in_progress_count: stats.get("in_progress_count"),
469 generated_count: stats.get("generated_count"),
470 delivered_count: stats.get("delivered_count"),
471 expired_count: stats.get("expired_count"),
472 overdue_count: stats.get("overdue_count"),
473 average_processing_days: stats.get("avg_processing_days"),
474 })
475 }
476
477 async fn count_by_status(
478 &self,
479 organization_id: Uuid,
480 status: EtatDateStatus,
481 ) -> Result<i64, String> {
482 let status_str = match status {
483 EtatDateStatus::Requested => "requested",
484 EtatDateStatus::InProgress => "in_progress",
485 EtatDateStatus::Generated => "generated",
486 EtatDateStatus::Delivered => "delivered",
487 EtatDateStatus::Expired => "expired",
488 };
489
490 let count = sqlx::query_scalar::<_, i64>(
491 "SELECT COUNT(*) FROM etats_dates WHERE organization_id = $1 AND status = CAST($2 AS etat_date_status)",
492 )
493 .bind(organization_id)
494 .bind(status_str)
495 .fetch_one(&self.pool)
496 .await
497 .map_err(|e| format!("Database error: {}", e))?;
498
499 Ok(count)
500 }
501}
502
503impl PostgresEtatDateRepository {
504 fn row_to_etat_date(&self, row: sqlx::postgres::PgRow) -> EtatDate {
505 let status_str: String = row.get("status");
506 let status = match status_str.as_str() {
507 "in_progress" => EtatDateStatus::InProgress,
508 "generated" => EtatDateStatus::Generated,
509 "delivered" => EtatDateStatus::Delivered,
510 "expired" => EtatDateStatus::Expired,
511 _ => EtatDateStatus::Requested,
512 };
513
514 let language_str: String = row.get("language");
515 let language = match language_str.as_str() {
516 "nl" => EtatDateLanguage::Nl,
517 "de" => EtatDateLanguage::De,
518 _ => EtatDateLanguage::Fr,
519 };
520
521 EtatDate {
522 id: row.get("id"),
523 organization_id: row.get("organization_id"),
524 building_id: row.get("building_id"),
525 unit_id: row.get("unit_id"),
526 reference_date: row.get("reference_date"),
527 requested_date: row.get("requested_date"),
528 generated_date: row.get("generated_date"),
529 delivered_date: row.get("delivered_date"),
530 status,
531 language,
532 reference_number: row.get("reference_number"),
533 notary_name: row.get("notary_name"),
534 notary_email: row.get("notary_email"),
535 notary_phone: row.get("notary_phone"),
536 building_name: row.get("building_name"),
537 building_address: row.get("building_address"),
538 unit_number: row.get("unit_number"),
539 unit_floor: row.get("unit_floor"),
540 unit_area: row.get("unit_area"),
541 ordinary_charges_quota: row.get("ordinary_charges_quota"),
542 extraordinary_charges_quota: row.get("extraordinary_charges_quota"),
543 owner_balance: row.get("owner_balance"),
544 arrears_amount: row.get("arrears_amount"),
545 monthly_provision_amount: row.get("monthly_provision_amount"),
546 total_balance: row.get("total_balance"),
547 approved_works_unpaid: row.get("approved_works_unpaid"),
548 additional_data: row.get("additional_data"),
549 pdf_file_path: row.get("pdf_file_path"),
550 created_at: row.get("created_at"),
551 updated_at: row.get("updated_at"),
552 }
553 }
554}