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