koprogo_api/infrastructure/database/repositories/
expense_repository_impl.rs

1use crate::application::dto::{ExpenseFilters, PageRequest};
2use crate::application::ports::ExpenseRepository;
3use crate::domain::entities::{ApprovalStatus, Expense, ExpenseCategory, PaymentStatus};
4use crate::infrastructure::database::pool::DbPool;
5use async_trait::async_trait;
6use sqlx::Row;
7use uuid::Uuid;
8
9pub struct PostgresExpenseRepository {
10    pool: DbPool,
11}
12
13impl PostgresExpenseRepository {
14    pub fn new(pool: DbPool) -> Self {
15        Self { pool }
16    }
17}
18
19#[async_trait]
20impl ExpenseRepository for PostgresExpenseRepository {
21    async fn create(&self, expense: &Expense) -> Result<Expense, String> {
22        let category_str = match expense.category {
23            ExpenseCategory::Maintenance => "maintenance",
24            ExpenseCategory::Repairs => "repairs",
25            ExpenseCategory::Insurance => "insurance",
26            ExpenseCategory::Utilities => "utilities",
27            ExpenseCategory::Cleaning => "cleaning",
28            ExpenseCategory::Administration => "administration",
29            ExpenseCategory::Works => "works",
30            ExpenseCategory::Other => "other",
31        };
32
33        let status_str = match expense.payment_status {
34            PaymentStatus::Pending => "pending",
35            PaymentStatus::Paid => "paid",
36            PaymentStatus::Overdue => "overdue",
37            PaymentStatus::Cancelled => "cancelled",
38        };
39
40        sqlx::query(
41            r#"
42            -- `amount_excl_vat`, `vat_rate`, `vat_amount`, `amount_incl_vat`,
43            -- `invoice_date` et `due_date` existent depuis la migration
44            -- `20251104000001_enrich_expenses_invoice_workflow` (novembre 2025)
45            -- mais n'étaient NI écrites ici, NI relues plus bas — le mapping
46            -- de lecture les forçait à `None`.
47            --
48            -- Toute dépense enregistrée depuis lors a donc ces six colonnes à
49            -- NULL. C'est la cause de fond des constats F12 (« échéance
50            -- affiche - ») et F20 (« seul le TTC est affiché ») du rapport du
51            -- 2026-09-01 : corriger le DTO d'entrée, le constructeur et le DTO
52            -- de sortie ne changeait rien tant que cette couche-ci perdait la
53            -- donnée.
54            INSERT INTO expenses (id, acp_id, organization_id, building_id, category, description, amount, expense_date, payment_status, supplier, invoice_number, account_code, contractor_report_id, created_at, updated_at,
55                                  amount_excl_vat, vat_rate, vat_amount, amount_incl_vat, invoice_date, due_date)
56            VALUES ($1, $2, $3, $4, CAST($5 AS expense_category), $6, $7, $8, CAST($9 AS payment_status), $10, $11, $12, $13, $14, $15,
57                    $16, $17, $18, $19, $20, $21)
58            "#,
59        )
60        .bind(expense.id)
61        .bind(expense.acp_id)
62        .bind(expense.organization_id)
63        .bind(expense.building_id)
64        .bind(category_str)
65        .bind(&expense.description)
66        .bind(expense.amount)
67        .bind(expense.expense_date)
68        .bind(status_str)
69        .bind(&expense.supplier)
70        .bind(&expense.invoice_number)
71        .bind(&expense.account_code)
72        .bind(expense.contractor_report_id)
73        .bind(expense.created_at)
74        .bind(expense.updated_at)
75        .bind(expense.amount_excl_vat)
76        .bind(expense.vat_rate)
77        .bind(expense.vat_amount)
78        .bind(expense.amount_incl_vat)
79        .bind(expense.invoice_date)
80        .bind(expense.due_date)
81        .execute(&self.pool)
82        .await
83        .map_err(|e| format!("Database error: {}", e))?;
84
85        Ok(expense.clone())
86    }
87
88    async fn enregistrer_lignes_de_facture(
89        &self,
90        expense_id: Uuid,
91        lignes: &[crate::application::ports::expense_repository::LigneDeFacture],
92    ) -> Result<(), String> {
93        if lignes.is_empty() {
94            return Ok(());
95        }
96
97        // Les montants dérivés sont recalculés ici, à partir de la quantité,
98        // du prix et du taux. Les accepter du client permettrait à une
99        // facture d'afficher un total qui ne découle pas de ses lignes.
100        for ligne in lignes {
101            sqlx::query(
102                r#"
103                INSERT INTO invoice_line_items
104                    (id, expense_id, description, quantity, unit_price,
105                     amount_excl_vat, vat_rate, vat_amount, amount_incl_vat, created_at)
106                VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW())
107                "#,
108            )
109            .bind(Uuid::new_v4())
110            .bind(expense_id)
111            .bind(&ligne.description)
112            .bind(ligne.quantity)
113            .bind(ligne.unit_price)
114            .bind(ligne.montant_hors_tva())
115            .bind(ligne.vat_rate)
116            .bind(ligne.montant_tva())
117            .bind(ligne.montant_tva_comprise())
118            .execute(&self.pool)
119            .await
120            .map_err(|e| format!("Database error inserting invoice line: {e}"))?;
121        }
122        Ok(())
123    }
124
125    async fn find_by_id(&self, id: Uuid) -> Result<Option<Expense>, String> {
126        let row = sqlx::query(
127            r#"
128            SELECT id, acp_id, organization_id, building_id,
129                   category::text AS category, description, amount, expense_date,
130                   payment_status::text AS payment_status, approval_status::text AS approval_status,
131                   submitted_at, approved_by, approved_at, rejection_reason, paid_date,
132                   supplier, invoice_number, account_code, contractor_report_id, created_at, updated_at,
133                   amount_excl_vat, vat_rate, vat_amount, amount_incl_vat, invoice_date, due_date
134            FROM expenses
135            WHERE id = $1
136            "#,
137        )
138        .bind(id)
139        .fetch_optional(&self.pool)
140        .await
141        .map_err(|e| format!("Database error: {}", e))?;
142
143        Ok(row.map(|row| {
144            let category_str: String = row.get("category");
145            let category = match category_str.as_str() {
146                "maintenance" => ExpenseCategory::Maintenance,
147                "repairs" => ExpenseCategory::Repairs,
148                "insurance" => ExpenseCategory::Insurance,
149                "utilities" => ExpenseCategory::Utilities,
150                "cleaning" => ExpenseCategory::Cleaning,
151                "administration" => ExpenseCategory::Administration,
152                "works" => ExpenseCategory::Works,
153                _ => ExpenseCategory::Other,
154            };
155
156            let status_str: String = row.get("payment_status");
157            let payment_status = match status_str.as_str() {
158                "paid" => PaymentStatus::Paid,
159                "overdue" => PaymentStatus::Overdue,
160                "cancelled" => PaymentStatus::Cancelled,
161                _ => PaymentStatus::Pending,
162            };
163
164            let approval_status_str: String = row.get("approval_status");
165            let approval_status = match approval_status_str.as_str() {
166                "pending_approval" => ApprovalStatus::PendingApproval,
167                "approved" => ApprovalStatus::Approved,
168                "rejected" => ApprovalStatus::Rejected,
169                _ => ApprovalStatus::Draft,
170            };
171
172            Expense {
173                id: row.get("id"),
174                acp_id: row.get("acp_id"),
175                organization_id: row.get("organization_id"),
176                building_id: row.get("building_id"),
177                category,
178                description: row.get("description"),
179                amount: row.get("amount"),
180                amount_excl_vat: row.try_get("amount_excl_vat").ok().flatten(),
181                vat_rate: row.try_get("vat_rate").ok().flatten(),
182                vat_amount: row.try_get("vat_amount").ok().flatten(),
183                // Repli sur le TTC : les dépenses antérieures au câblage de
184                // ces colonnes ont `amount_incl_vat` à NULL alors que `amount`
185                // porte bien le TTC.
186                amount_incl_vat: row
187                    .try_get("amount_incl_vat")
188                    .ok()
189                    .flatten()
190                    .or_else(|| Some(row.get("amount"))),
191                expense_date: row.get("expense_date"),
192                invoice_date: row.try_get("invoice_date").ok().flatten(),
193                due_date: row.try_get("due_date").ok().flatten(),
194                paid_date: row.try_get("paid_date").ok(),
195                approval_status,
196                submitted_at: row.try_get("submitted_at").ok(),
197                approved_by: row.try_get("approved_by").ok(),
198                approved_at: row.try_get("approved_at").ok(),
199                rejection_reason: row.try_get("rejection_reason").ok(),
200                payment_status,
201                supplier: row.get("supplier"),
202                invoice_number: row.get("invoice_number"),
203                account_code: row.get("account_code"),
204                contractor_report_id: row.try_get("contractor_report_id").ok(),
205                created_at: row.get("created_at"),
206                updated_at: row.get("updated_at"),
207            }
208        }))
209    }
210
211    async fn find_by_building(&self, building_id: Uuid) -> Result<Vec<Expense>, String> {
212        let rows = sqlx::query(
213            r#"
214            SELECT id, acp_id, organization_id, building_id,
215                   category::text AS category, description, amount, expense_date,
216                   payment_status::text AS payment_status, approval_status::text AS approval_status,
217                   submitted_at, approved_by, approved_at, rejection_reason, paid_date,
218                   supplier, invoice_number, account_code, contractor_report_id, created_at, updated_at,
219                   amount_excl_vat, vat_rate, vat_amount, amount_incl_vat, invoice_date, due_date
220            FROM expenses
221            WHERE building_id = $1
222            ORDER BY expense_date DESC
223            "#,
224        )
225        .bind(building_id)
226        .fetch_all(&self.pool)
227        .await
228        .map_err(|e| format!("Database error: {}", e))?;
229
230        Ok(rows
231            .iter()
232            .map(|row| {
233                let category_str: String = row.get("category");
234                let category = match category_str.as_str() {
235                    "maintenance" => ExpenseCategory::Maintenance,
236                    "repairs" => ExpenseCategory::Repairs,
237                    "insurance" => ExpenseCategory::Insurance,
238                    "utilities" => ExpenseCategory::Utilities,
239                    "cleaning" => ExpenseCategory::Cleaning,
240                    "administration" => ExpenseCategory::Administration,
241                    "works" => ExpenseCategory::Works,
242                    _ => ExpenseCategory::Other,
243                };
244
245                let status_str: String = row.get("payment_status");
246                let payment_status = match status_str.as_str() {
247                    "paid" => PaymentStatus::Paid,
248                    "overdue" => PaymentStatus::Overdue,
249                    "cancelled" => PaymentStatus::Cancelled,
250                    _ => PaymentStatus::Pending,
251                };
252
253                let approval_status_str: String = row.get("approval_status");
254                let approval_status = match approval_status_str.as_str() {
255                    "pending_approval" => ApprovalStatus::PendingApproval,
256                    "approved" => ApprovalStatus::Approved,
257                    "rejected" => ApprovalStatus::Rejected,
258                    _ => ApprovalStatus::Draft,
259                };
260
261                Expense {
262                    id: row.get("id"),
263                    acp_id: row.get("acp_id"),
264                    organization_id: row.get("organization_id"),
265                    building_id: row.get("building_id"),
266                    category,
267                    description: row.get("description"),
268                    amount: row.get("amount"),
269                    amount_excl_vat: row.try_get("amount_excl_vat").ok().flatten(),
270                    vat_rate: row.try_get("vat_rate").ok().flatten(),
271                    vat_amount: row.try_get("vat_amount").ok().flatten(),
272                    amount_incl_vat: row
273                        .try_get("amount_incl_vat")
274                        .ok()
275                        .flatten()
276                        .or_else(|| Some(row.get("amount"))),
277                    expense_date: row.get("expense_date"),
278                    invoice_date: row.try_get("invoice_date").ok().flatten(),
279                    due_date: row.try_get("due_date").ok().flatten(),
280                    paid_date: row.try_get("paid_date").ok(),
281                    approval_status,
282                    submitted_at: row.try_get("submitted_at").ok(),
283                    approved_by: row.try_get("approved_by").ok(),
284                    approved_at: row.try_get("approved_at").ok(),
285                    rejection_reason: row.try_get("rejection_reason").ok(),
286                    payment_status,
287                    supplier: row.get("supplier"),
288                    invoice_number: row.get("invoice_number"),
289                    account_code: row.get("account_code"),
290                    contractor_report_id: row.try_get("contractor_report_id").ok(),
291                    created_at: row.get("created_at"),
292                    updated_at: row.get("updated_at"),
293                }
294            })
295            .collect())
296    }
297
298    async fn find_all_paginated(
299        &self,
300        page_request: &PageRequest,
301        filters: &ExpenseFilters,
302    ) -> Result<(Vec<Expense>, i64), String> {
303        // Validate page request
304        page_request.validate()?;
305
306        // Build WHERE clause dynamically
307        let mut where_clauses = Vec::new();
308        let mut param_count = 0;
309
310        // Portée par ACP GÉRÉE, pas par estampille de saisie.
311        //
312        // Le filtre portait sur `expenses.organization_id` — le cabinet qui a
313        // encodé la charge. Une copropriété change pourtant de syndic au gré
314        // des assemblées générales, et sa comptabilité lui appartient.
315        // Filtrer sur l'estampille produisait deux effets opposés, mesurés le
316        // 2026-09-02 sur une passation réelle :
317        //
318        //   le syndic ENTRANT ne voyait rien de l'historique — ni grand livre,
319        //     ni budget, ni appels de fonds — alors que l'Art. 3.94 § 1er lui
320        //     impose de transmettre les décomptes des deux derniers exercices ;
321        //   le syndic SORTANT continuait de tout voir, y compris les noms,
322        //     adresses et arriérés des copropriétaires, sans plus aucune base
323        //     légale pour les traiter.
324        //
325        // `filters.organization_id` désigne désormais le SYNDIC QUI DEMANDE,
326        // et la clause traduit la vraie question : « cette charge relève-t-elle
327        // d'une ACP dont ce cabinet a la gestion ? ».
328        if filters.organization_id.is_some() {
329            param_count += 1;
330            where_clauses.push(format!(
331                "acp_id IN (SELECT id FROM acps WHERE organization_id = ${})",
332                param_count
333            ));
334        }
335
336        if filters.building_id.is_some() {
337            param_count += 1;
338            where_clauses.push(format!("building_id = ${}", param_count));
339        }
340
341        if filters.category.is_some() {
342            param_count += 1;
343            where_clauses.push(format!("category = ${}", param_count));
344        }
345
346        if filters.status.is_some() {
347            param_count += 1;
348            where_clauses.push(format!("payment_status = ${}", param_count));
349        }
350
351        if filters.date_from.is_some() {
352            param_count += 1;
353            where_clauses.push(format!("expense_date >= ${}", param_count));
354        }
355
356        if filters.date_to.is_some() {
357            param_count += 1;
358            where_clauses.push(format!("expense_date <= ${}", param_count));
359        }
360
361        if filters.min_amount.is_some() {
362            param_count += 1;
363            where_clauses.push(format!("amount >= ${}", param_count));
364        }
365
366        if filters.max_amount.is_some() {
367            param_count += 1;
368            where_clauses.push(format!("amount <= ${}", param_count));
369        }
370
371        if filters.approval_status.is_some() {
372            param_count += 1;
373            where_clauses.push(format!("approval_status::text = ${}", param_count));
374        }
375
376        let where_clause = if where_clauses.is_empty() {
377            String::new()
378        } else {
379            format!("WHERE {}", where_clauses.join(" AND "))
380        };
381
382        // Validate sort column (whitelist)
383        let allowed_columns = ["expense_date", "amount", "created_at", "payment_status"];
384        let sort_column = page_request.sort_by.as_deref().unwrap_or("expense_date");
385
386        if !allowed_columns.contains(&sort_column) {
387            return Err(format!("Invalid sort column: {}", sort_column));
388        }
389
390        // Count total items
391        let count_query = format!("SELECT COUNT(*) FROM expenses {}", where_clause);
392        let mut count_query = sqlx::query_scalar::<_, i64>(&count_query);
393
394        if let Some(organization_id) = filters.organization_id {
395            count_query = count_query.bind(organization_id);
396        }
397        if let Some(building_id) = filters.building_id {
398            count_query = count_query.bind(building_id);
399        }
400        if let Some(category) = &filters.category {
401            count_query = count_query.bind(category);
402        }
403        if let Some(status) = &filters.status {
404            count_query = count_query.bind(status);
405        }
406        if let Some(date_from) = filters.date_from {
407            count_query = count_query.bind(date_from);
408        }
409        if let Some(date_to) = filters.date_to {
410            count_query = count_query.bind(date_to);
411        }
412        if let Some(min_amount) = filters.min_amount {
413            count_query = count_query.bind(min_amount);
414        }
415        if let Some(max_amount) = filters.max_amount {
416            count_query = count_query.bind(max_amount);
417        }
418        if let Some(ref approval_status) = filters.approval_status {
419            let status_str = match approval_status {
420                ApprovalStatus::Draft => "draft",
421                ApprovalStatus::PendingApproval => "pending_approval",
422                ApprovalStatus::Approved => "approved",
423                ApprovalStatus::Rejected => "rejected",
424            };
425            count_query = count_query.bind(status_str);
426        }
427
428        let total_items = count_query
429            .fetch_one(&self.pool)
430            .await
431            .map_err(|e| format!("Database error: {}", e))?;
432
433        // Fetch paginated data
434        param_count += 1;
435        let limit_param = param_count;
436        param_count += 1;
437        let offset_param = param_count;
438
439        let data_query = format!(
440            "SELECT id, acp_id, organization_id, building_id, category::text AS category, description, amount, expense_date, payment_status::text AS payment_status, approval_status::text AS approval_status, submitted_at, approved_by, approved_at, rejection_reason, paid_date, supplier, invoice_number, account_code, contractor_report_id, created_at, updated_at, amount_excl_vat, vat_rate, vat_amount, amount_incl_vat, invoice_date, due_date \
441             FROM expenses {} ORDER BY {} {} LIMIT ${} OFFSET ${}",
442            where_clause,
443            sort_column,
444            page_request.order.to_sql(),
445            limit_param,
446            offset_param
447        );
448
449        let mut data_query = sqlx::query(&data_query);
450
451        if let Some(organization_id) = filters.organization_id {
452            data_query = data_query.bind(organization_id);
453        }
454        if let Some(building_id) = filters.building_id {
455            data_query = data_query.bind(building_id);
456        }
457        if let Some(category) = &filters.category {
458            data_query = data_query.bind(category);
459        }
460        if let Some(status) = &filters.status {
461            data_query = data_query.bind(status);
462        }
463        if let Some(date_from) = filters.date_from {
464            data_query = data_query.bind(date_from);
465        }
466        if let Some(date_to) = filters.date_to {
467            data_query = data_query.bind(date_to);
468        }
469        if let Some(min_amount) = filters.min_amount {
470            data_query = data_query.bind(min_amount);
471        }
472        if let Some(max_amount) = filters.max_amount {
473            data_query = data_query.bind(max_amount);
474        }
475        if let Some(ref approval_status) = filters.approval_status {
476            let status_str = match approval_status {
477                ApprovalStatus::Draft => "draft",
478                ApprovalStatus::PendingApproval => "pending_approval",
479                ApprovalStatus::Approved => "approved",
480                ApprovalStatus::Rejected => "rejected",
481            };
482            data_query = data_query.bind(status_str);
483        }
484
485        data_query = data_query
486            .bind(page_request.limit())
487            .bind(page_request.offset());
488
489        let rows = data_query
490            .fetch_all(&self.pool)
491            .await
492            .map_err(|e| format!("Database error: {}", e))?;
493
494        let expenses: Vec<Expense> = rows
495            .iter()
496            .map(|row| {
497                let category_str: String = row.get("category");
498                let category = match category_str.as_str() {
499                    "maintenance" => ExpenseCategory::Maintenance,
500                    "repairs" => ExpenseCategory::Repairs,
501                    "insurance" => ExpenseCategory::Insurance,
502                    "utilities" => ExpenseCategory::Utilities,
503                    "cleaning" => ExpenseCategory::Cleaning,
504                    "administration" => ExpenseCategory::Administration,
505                    "works" => ExpenseCategory::Works,
506                    _ => ExpenseCategory::Other,
507                };
508
509                let status_str: String = row.get("payment_status");
510                let payment_status = match status_str.as_str() {
511                    "paid" => PaymentStatus::Paid,
512                    "overdue" => PaymentStatus::Overdue,
513                    "cancelled" => PaymentStatus::Cancelled,
514                    _ => PaymentStatus::Pending,
515                };
516
517                let approval_status_str: String = row.get("approval_status");
518                let approval_status = match approval_status_str.as_str() {
519                    "pending_approval" => ApprovalStatus::PendingApproval,
520                    "approved" => ApprovalStatus::Approved,
521                    "rejected" => ApprovalStatus::Rejected,
522                    _ => ApprovalStatus::Draft,
523                };
524
525                Expense {
526                    id: row.get("id"),
527                    acp_id: row.get("acp_id"),
528                    organization_id: row.get("organization_id"),
529                    building_id: row.get("building_id"),
530                    category,
531                    description: row.get("description"),
532                    amount: row.get("amount"),
533                    amount_excl_vat: row.try_get("amount_excl_vat").ok().flatten(),
534                    vat_rate: row.try_get("vat_rate").ok().flatten(),
535                    vat_amount: row.try_get("vat_amount").ok().flatten(),
536                    amount_incl_vat: row
537                        .try_get("amount_incl_vat")
538                        .ok()
539                        .flatten()
540                        .or_else(|| Some(row.get("amount"))),
541                    expense_date: row.get("expense_date"),
542                    invoice_date: row.try_get("invoice_date").ok().flatten(),
543                    due_date: row.try_get("due_date").ok().flatten(),
544                    paid_date: row.try_get("paid_date").ok(),
545                    approval_status,
546                    submitted_at: row.try_get("submitted_at").ok(),
547                    approved_by: row.try_get("approved_by").ok(),
548                    approved_at: row.try_get("approved_at").ok(),
549                    rejection_reason: row.try_get("rejection_reason").ok(),
550                    payment_status,
551                    supplier: row.get("supplier"),
552                    invoice_number: row.get("invoice_number"),
553                    account_code: row.get("account_code"),
554                    contractor_report_id: row.try_get("contractor_report_id").ok(),
555                    created_at: row.get("created_at"),
556                    updated_at: row.get("updated_at"),
557                }
558            })
559            .collect();
560
561        Ok((expenses, total_items))
562    }
563
564    async fn update(&self, expense: &Expense) -> Result<Expense, String> {
565        let payment_status_str = match expense.payment_status {
566            PaymentStatus::Pending => "pending",
567            PaymentStatus::Paid => "paid",
568            PaymentStatus::Overdue => "overdue",
569            PaymentStatus::Cancelled => "cancelled",
570        };
571
572        let approval_status_str = match expense.approval_status {
573            ApprovalStatus::Draft => "draft",
574            ApprovalStatus::PendingApproval => "pending_approval",
575            ApprovalStatus::Approved => "approved",
576            ApprovalStatus::Rejected => "rejected",
577        };
578
579        sqlx::query(
580            r#"
581            UPDATE expenses
582            SET
583                payment_status = CAST($2 AS payment_status),
584                approval_status = CAST($3 AS approval_status),
585                submitted_at = $4,
586                approved_by = $5,
587                approved_at = $6,
588                rejection_reason = $7,
589                paid_date = $8,
590                updated_at = $9
591            WHERE id = $1
592            "#,
593        )
594        .bind(expense.id)
595        .bind(payment_status_str)
596        .bind(approval_status_str)
597        .bind(expense.submitted_at)
598        .bind(expense.approved_by)
599        .bind(expense.approved_at)
600        .bind(expense.rejection_reason.as_deref())
601        .bind(expense.paid_date)
602        .bind(expense.updated_at)
603        .execute(&self.pool)
604        .await
605        .map_err(|e| format!("Database error: {}", e))?;
606
607        Ok(expense.clone())
608    }
609
610    async fn delete(&self, id: Uuid) -> Result<bool, String> {
611        let result = sqlx::query("DELETE FROM expenses WHERE id = $1")
612            .bind(id)
613            .execute(&self.pool)
614            .await
615            .map_err(|e| format!("Database error: {}", e))?;
616
617        Ok(result.rows_affected() > 0)
618    }
619}
koprogo_api/infrastructure/database/repositories/expense_repository_impl.rs

koprogo_api/infrastructure/database/repositories/
expense_repository_impl.rs
