koprogo_api/infrastructure/database/repositories/
budget_repository_impl.rs

1use crate::application::dto::PageRequest;
2use crate::application::error::AppError;
3use crate::application::ports::{BudgetRepository, BudgetStatsResponse, BudgetVarianceResponse};
4use crate::domain::entities::{Budget, BudgetStatus, ExpenseCategory};
5use async_trait::async_trait;
6use chrono::Datelike;
7use rust_decimal::Decimal;
8use rust_decimal_macros::dec;
9use sqlx::postgres::PgRow;
10use sqlx::{PgPool, Row};
11use uuid::Uuid;
12
13pub struct PostgresBudgetRepository {
14    pool: PgPool,
15}
16
17/// Budget SELECT columns. Monetary columns are NUMERIC → lus directement en
18/// `Decimal` (cf. ADR-0007, Story H11). Seul l'enum statut est casté en texte.
19const BUDGET_COLUMNS: &str = r#"
20    id, acp_id, organization_id, building_id, fiscal_year,
21    ordinary_budget,
22    extraordinary_budget,
23    total_budget,
24    status::text as status_text,
25    submitted_date, approved_date, approved_by_meeting_id,
26    monthly_provision_amount,
27    notes, created_at, updated_at
28"#;
29
30impl PostgresBudgetRepository {
31    pub fn new(pool: PgPool) -> Self {
32        Self { pool }
33    }
34
35    /// Helper: Convert database row to Budget entity
36    fn row_to_budget(&self, row: PgRow) -> Budget {
37        let status_str: String = row.get("status_text");
38        let status = match status_str.as_str() {
39            "draft" => BudgetStatus::Draft,
40            "submitted" => BudgetStatus::Submitted,
41            "approved" => BudgetStatus::Approved,
42            "rejected" => BudgetStatus::Rejected,
43            "archived" => BudgetStatus::Archived,
44            _ => BudgetStatus::Draft,
45        };
46
47        Budget {
48            id: row.get("id"),
49            acp_id: row.get("acp_id"),
50            organization_id: row.get("organization_id"),
51            building_id: row.get("building_id"),
52            fiscal_year: row.get("fiscal_year"),
53            ordinary_budget: row.get("ordinary_budget"),
54            extraordinary_budget: row.get("extraordinary_budget"),
55            total_budget: row.get("total_budget"),
56            status,
57            submitted_date: row.get("submitted_date"),
58            approved_date: row.get("approved_date"),
59            approved_by_meeting_id: row.get("approved_by_meeting_id"),
60            monthly_provision_amount: row.get("monthly_provision_amount"),
61            notes: row.get("notes"),
62            created_at: row.get("created_at"),
63            updated_at: row.get("updated_at"),
64        }
65    }
66}
67
68#[async_trait]
69impl BudgetRepository for PostgresBudgetRepository {
70    async fn create(&self, budget: &Budget) -> Result<Budget, AppError> {
71        let status_str = match budget.status {
72            BudgetStatus::Draft => "draft",
73            BudgetStatus::Submitted => "submitted",
74            BudgetStatus::Approved => "approved",
75            BudgetStatus::Rejected => "rejected",
76            BudgetStatus::Archived => "archived",
77        };
78
79        let row = sqlx::query(
80            r#"
81            INSERT INTO budgets (
82                id, acp_id, organization_id, building_id, fiscal_year,
83                ordinary_budget, extraordinary_budget, total_budget,
84                status, submitted_date, approved_date, approved_by_meeting_id,
85                monthly_provision_amount, notes,
86                created_at, updated_at
87            ) VALUES (
88                $1, $2, $3, $4, $5, $6, $7, $8, $9::budget_status,
89                $10, $11, $12, $13, $14, $15, $16
90            )
91            RETURNING id, acp_id, organization_id, building_id, fiscal_year,
92                ordinary_budget,
93                extraordinary_budget,
94                total_budget,
95                status::text as status_text,
96                submitted_date, approved_date, approved_by_meeting_id,
97                monthly_provision_amount,
98                notes, created_at, updated_at
99            "#,
100        )
101        .bind(budget.id)
102        .bind(budget.acp_id)
103        .bind(budget.organization_id)
104        .bind(budget.building_id)
105        .bind(budget.fiscal_year)
106        .bind(budget.ordinary_budget)
107        .bind(budget.extraordinary_budget)
108        .bind(budget.total_budget)
109        .bind(status_str)
110        .bind(budget.submitted_date)
111        .bind(budget.approved_date)
112        .bind(budget.approved_by_meeting_id)
113        .bind(budget.monthly_provision_amount)
114        .bind(&budget.notes)
115        .bind(budget.created_at)
116        .bind(budget.updated_at)
117        .fetch_one(&self.pool)
118        .await
119        .map_err(|e| format!("Failed to create budget: {}", e))?;
120
121        Ok(self.row_to_budget(row))
122    }
123
124    async fn find_by_id(&self, id: Uuid) -> Result<Option<Budget>, AppError> {
125        let sql = format!("SELECT {} FROM budgets WHERE id = $1", BUDGET_COLUMNS);
126        let result = sqlx::query(&sql)
127            .bind(id)
128            .fetch_optional(&self.pool)
129            .await
130            .map_err(|e| format!("Failed to find budget: {}", e))?;
131
132        Ok(result.map(|row| self.row_to_budget(row)))
133    }
134
135    async fn find_by_building_and_fiscal_year(
136        &self,
137        building_id: Uuid,
138        fiscal_year: i32,
139    ) -> Result<Option<Budget>, AppError> {
140        let sql = format!(
141            "SELECT {} FROM budgets WHERE building_id = $1 AND fiscal_year = $2",
142            BUDGET_COLUMNS
143        );
144        let result = sqlx::query(&sql)
145            .bind(building_id)
146            .bind(fiscal_year)
147            .fetch_optional(&self.pool)
148            .await
149            .map_err(|e| format!("Failed to find budget: {}", e))?;
150
151        Ok(result.map(|row| self.row_to_budget(row)))
152    }
153
154    async fn find_by_building(&self, building_id: Uuid) -> Result<Vec<Budget>, AppError> {
155        let sql = format!(
156            "SELECT {} FROM budgets WHERE building_id = $1 ORDER BY fiscal_year DESC",
157            BUDGET_COLUMNS
158        );
159        let rows = sqlx::query(&sql)
160            .bind(building_id)
161            .fetch_all(&self.pool)
162            .await
163            .map_err(|e| format!("Failed to find budgets: {}", e))?;
164
165        Ok(rows
166            .into_iter()
167            .map(|row| self.row_to_budget(row))
168            .collect())
169    }
170
171    async fn find_active_by_building(&self, building_id: Uuid) -> Result<Option<Budget>, AppError> {
172        let sql = format!("SELECT {} FROM budgets WHERE building_id = $1 AND status = 'approved' ORDER BY fiscal_year DESC LIMIT 1", BUDGET_COLUMNS);
173        let result = sqlx::query(&sql)
174            .bind(building_id)
175            .fetch_optional(&self.pool)
176            .await
177            .map_err(|e| format!("Failed to find active budget: {}", e))?;
178
179        Ok(result.map(|row| self.row_to_budget(row)))
180    }
181
182    async fn find_by_fiscal_year(
183        &self,
184        organization_id: Uuid,
185        fiscal_year: i32,
186    ) -> Result<Vec<Budget>, AppError> {
187        let sql = format!("SELECT {} FROM budgets WHERE acp_id IN (SELECT id FROM acps WHERE organization_id = $1) AND fiscal_year = $2 ORDER BY created_at DESC", BUDGET_COLUMNS);
188        let rows = sqlx::query(&sql)
189            .bind(organization_id)
190            .bind(fiscal_year)
191            .fetch_all(&self.pool)
192            .await
193            .map_err(|e| format!("Failed to find budgets: {}", e))?;
194
195        Ok(rows
196            .into_iter()
197            .map(|row| self.row_to_budget(row))
198            .collect())
199    }
200
201    async fn find_by_status(
202        &self,
203        organization_id: Uuid,
204        status: BudgetStatus,
205    ) -> Result<Vec<Budget>, AppError> {
206        let status_str = match status {
207            BudgetStatus::Draft => "draft",
208            BudgetStatus::Submitted => "submitted",
209            BudgetStatus::Approved => "approved",
210            BudgetStatus::Rejected => "rejected",
211            BudgetStatus::Archived => "archived",
212        };
213
214        let sql = format!("SELECT {} FROM budgets WHERE acp_id IN (SELECT id FROM acps WHERE organization_id = $1) AND status = $2::budget_status ORDER BY created_at DESC", BUDGET_COLUMNS);
215        let rows = sqlx::query(&sql)
216            .bind(organization_id)
217            .bind(status_str)
218            .fetch_all(&self.pool)
219            .await
220            .map_err(|e| format!("Failed to find budgets: {}", e))?;
221
222        Ok(rows
223            .into_iter()
224            .map(|row| self.row_to_budget(row))
225            .collect())
226    }
227
228    async fn find_all_paginated(
229        &self,
230        page_request: &PageRequest,
231        organization_id: Option<Uuid>,
232        building_id: Option<Uuid>,
233        status: Option<BudgetStatus>,
234    ) -> Result<(Vec<Budget>, i64), AppError> {
235        let offset = (page_request.page - 1) * page_request.per_page;
236
237        // Build dynamic query
238        let mut query = format!("SELECT {} FROM budgets WHERE 1=1", BUDGET_COLUMNS);
239        let mut count_query = String::from("SELECT COUNT(*) as count FROM budgets WHERE 1=1");
240
241        let mut bind_index = 1;
242        let mut bindings: Vec<String> = Vec::new();
243
244        if let Some(org_id) = organization_id {
245            query.push_str(&format!(
246                " AND acp_id IN (SELECT id FROM acps WHERE organization_id = ${}::uuid)",
247                bind_index
248            ));
249            count_query.push_str(&format!(
250                " AND acp_id IN (SELECT id FROM acps WHERE organization_id = ${}::uuid)",
251                bind_index
252            ));
253            bindings.push(org_id.to_string());
254            bind_index += 1;
255        }
256
257        if let Some(bldg_id) = building_id {
258            query.push_str(&format!(" AND building_id = ${}::uuid", bind_index));
259            count_query.push_str(&format!(" AND building_id = ${}::uuid", bind_index));
260            bindings.push(bldg_id.to_string());
261            bind_index += 1;
262        }
263
264        if let Some(s) = status {
265            let status_str = match s {
266                BudgetStatus::Draft => "draft",
267                BudgetStatus::Submitted => "submitted",
268                BudgetStatus::Approved => "approved",
269                BudgetStatus::Rejected => "rejected",
270                BudgetStatus::Archived => "archived",
271            };
272            query.push_str(&format!(" AND status = ${}::budget_status", bind_index));
273            count_query.push_str(&format!(" AND status = ${}::budget_status", bind_index));
274            bindings.push(status_str.to_string());
275            bind_index += 1;
276        }
277
278        query.push_str(" ORDER BY fiscal_year DESC, created_at DESC");
279        query.push_str(&format!(
280            " LIMIT ${} OFFSET ${}",
281            bind_index,
282            bind_index + 1
283        ));
284
285        // Execute count query
286        let mut count_q = sqlx::query(&count_query);
287        for binding in &bindings {
288            count_q = count_q.bind(binding);
289        }
290        let count_row = count_q
291            .fetch_one(&self.pool)
292            .await
293            .map_err(|e| format!("Failed to count budgets: {}", e))?;
294        let total: i64 = count_row.get("count");
295
296        // Execute main query
297        let mut main_q = sqlx::query(&query);
298        for binding in &bindings {
299            main_q = main_q.bind(binding);
300        }
301        main_q = main_q.bind(page_request.per_page as i64);
302        main_q = main_q.bind(offset as i64);
303
304        let rows = main_q
305            .fetch_all(&self.pool)
306            .await
307            .map_err(|e| format!("Failed to fetch budgets: {}", e))?;
308
309        let budgets = rows
310            .into_iter()
311            .map(|row| self.row_to_budget(row))
312            .collect();
313
314        Ok((budgets, total))
315    }
316
317    async fn update(&self, budget: &Budget) -> Result<Budget, AppError> {
318        let status_str = match budget.status {
319            BudgetStatus::Draft => "draft",
320            BudgetStatus::Submitted => "submitted",
321            BudgetStatus::Approved => "approved",
322            BudgetStatus::Rejected => "rejected",
323            BudgetStatus::Archived => "archived",
324        };
325
326        let row = sqlx::query(
327            r#"
328            UPDATE budgets SET
329                ordinary_budget = $2,
330                extraordinary_budget = $3,
331                total_budget = $4,
332                status = $5::budget_status,
333                submitted_date = $6,
334                approved_date = $7,
335                approved_by_meeting_id = $8,
336                monthly_provision_amount = $9,
337                notes = $10,
338                updated_at = $11
339            WHERE id = $1
340            RETURNING id, acp_id, organization_id, building_id, fiscal_year,
341                ordinary_budget,
342                extraordinary_budget,
343                total_budget,
344                status::text as status_text,
345                submitted_date, approved_date, approved_by_meeting_id,
346                monthly_provision_amount,
347                notes, created_at, updated_at
348            "#,
349        )
350        .bind(budget.id)
351        .bind(budget.ordinary_budget)
352        .bind(budget.extraordinary_budget)
353        .bind(budget.total_budget)
354        .bind(status_str)
355        .bind(budget.submitted_date)
356        .bind(budget.approved_date)
357        .bind(budget.approved_by_meeting_id)
358        .bind(budget.monthly_provision_amount)
359        .bind(&budget.notes)
360        .bind(budget.updated_at)
361        .fetch_one(&self.pool)
362        .await
363        .map_err(|e| format!("Failed to update budget: {}", e))?;
364
365        Ok(self.row_to_budget(row))
366    }
367
368    async fn delete(&self, id: Uuid) -> Result<bool, AppError> {
369        let result = sqlx::query("DELETE FROM budgets WHERE id = $1")
370            .bind(id)
371            .execute(&self.pool)
372            .await
373            .map_err(|e| format!("Failed to delete budget: {}", e))?;
374
375        Ok(result.rows_affected() > 0)
376    }
377
378    async fn get_stats(&self, organization_id: Uuid) -> Result<BudgetStatsResponse, AppError> {
379        let row = sqlx::query(
380            r#"
381            SELECT
382                COUNT(*) as total_budgets,
383                COUNT(*) FILTER (WHERE status = 'draft') as draft_count,
384                COUNT(*) FILTER (WHERE status = 'submitted') as submitted_count,
385                COUNT(*) FILTER (WHERE status = 'approved') as approved_count,
386                COUNT(*) FILTER (WHERE status = 'rejected') as rejected_count,
387                COUNT(*) FILTER (WHERE status = 'archived') as archived_count,
388                COALESCE(AVG(total_budget), 0) as average_total_budget,
389                COALESCE(AVG(monthly_provision_amount), 0) as average_monthly_provision
390            FROM budgets
391            WHERE acp_id IN (SELECT id FROM acps WHERE organization_id = $1)
392            "#,
393        )
394        .bind(organization_id)
395        .fetch_one(&self.pool)
396        .await
397        .map_err(|e| format!("Failed to get budget stats: {}", e))?;
398
399        Ok(BudgetStatsResponse {
400            total_budgets: row.get("total_budgets"),
401            draft_count: row.get("draft_count"),
402            submitted_count: row.get("submitted_count"),
403            approved_count: row.get("approved_count"),
404            rejected_count: row.get("rejected_count"),
405            archived_count: row.get("archived_count"),
406            average_total_budget: row.get("average_total_budget"),
407            average_monthly_provision: row.get("average_monthly_provision"),
408        })
409    }
410
411    async fn get_variance(
412        &self,
413        budget_id: Uuid,
414    ) -> Result<Option<BudgetVarianceResponse>, AppError> {
415        // First, get the budget
416        let budget = match self.find_by_id(budget_id).await? {
417            Some(b) => b,
418            None => return Ok(None),
419        };
420
421        // Issue #661 — l'analyse de variance reste en `Decimal` de bout en bout.
422        // Le commentaire précédent justifiait une conversion `to_f64()` « à la
423        // frontière du reporting » : c'était précisément l'aller-retour
424        // Decimal→f64 que l'ADR-0008 §A interdit sur un montant, et il
425        // s'appliquait ici à des charges de copropriété.
426        let budget_ordinary = budget.ordinary_budget;
427        let budget_extraordinary = budget.extraordinary_budget;
428        let budget_total = budget.total_budget;
429
430        // Get actual expenses for this budget's fiscal year and building
431        let fiscal_year_start = format!("{}-01-01", budget.fiscal_year);
432        let fiscal_year_end = format!("{}-12-31", budget.fiscal_year);
433
434        // `category::text` — et non `category` nu.
435        //
436        // La colonne est de type énuméré PostgreSQL `expense_category`. La lue
437        // en `String` sans transtypage échoue au décodage, et `row.get()`
438        // PANIQUE sur cette erreur : le worker actix meurt et l'appelant reçoit
439        // un 502. Le défaut ne se voyait qu'une fois l'immeuble doté d'au moins
440        // une dépense correspondant au filtre — sans ligne, la boucle ne
441        // s'exécute pas et la route répond 200 avec un réalisé à zéro.
442        //
443        // Le transtypage explicite est le motif déjà suivi ailleurs dans le
444        // dépôt (`notice_repository_impl`, `expense_repository_impl`).
445        //
446        // ── Ce que « réalisé » compte ──────────────────────────────────────
447        //
448        // Le filtre portait sur `payment_status = 'paid'` : seul le décaissé.
449        // Le grand livre, lui, comptabilise la charge à l'APPROBATION de la
450        // facture (écriture ACH : D 6xxxxx / C 440). Les deux rapports
451        // divergeaient donc sur le même engagement — mesuré le 2026-09-02 :
452        // grand livre 2 420 € de charges, suivi budgétaire 0 consommé.
453        //
454        // Pour un syndic, la question utile est « combien du budget voté est
455        // déjà engagé ? », pas « combien est sorti de la banque ». Une facture
456        // approuvée sera réclamée aux copropriétaires qu'elle soit payée ou
457        // non. Le réalisé s'aligne donc sur ce que le grand livre enregistre,
458        // en excluant les factures annulées.
459        let expense_rows = sqlx::query(
460            r#"
461            SELECT
462                category::text AS category,
463                COALESCE(SUM(amount), 0) as total_amount
464            FROM expenses
465            WHERE building_id = $1
466              AND expense_date >= $2::date
467              AND expense_date <= $3::date
468              AND approval_status = 'approved'
469              AND payment_status <> 'cancelled'
470            GROUP BY category
471            "#,
472        )
473        .bind(budget.building_id)
474        .bind(fiscal_year_start)
475        .bind(fiscal_year_end)
476        .fetch_all(&self.pool)
477        .await
478        .map_err(|e| format!("Failed to get expenses: {}", e))?;
479
480        let mut actual_ordinary = Decimal::ZERO;
481        let mut actual_extraordinary = Decimal::ZERO;
482        let mut overrun_categories = Vec::new();
483
484        for row in expense_rows {
485            // `try_get` et non `get` : ce dernier panique sur une erreur de
486            // décodage au lieu de la remonter.
487            let category_str: String = row.try_get("category")?;
488            let amount: Decimal = row.try_get("total_amount")?;
489
490            let category = match category_str.as_str() {
491                "utilities" => ExpenseCategory::Utilities,
492                "maintenance" => ExpenseCategory::Maintenance,
493                "repairs" => ExpenseCategory::Repairs,
494                "insurance" => ExpenseCategory::Insurance,
495                "cleaning" => ExpenseCategory::Cleaning,
496                "administration" => ExpenseCategory::Administration,
497                "works" => ExpenseCategory::Works,
498                _ => ExpenseCategory::Other,
499            };
500
501            match category {
502                ExpenseCategory::Works => actual_extraordinary += amount,
503                _ => actual_ordinary += amount,
504            }
505        }
506
507        let actual_total = actual_ordinary + actual_extraordinary;
508
509        // Calculate variances
510        let variance_ordinary = budget_ordinary - actual_ordinary;
511        let variance_extraordinary = budget_extraordinary - actual_extraordinary;
512        let variance_total = budget_total - actual_total;
513
514        // Calculate variance percentages
515        let pct = |variance: Decimal, budget: Decimal| -> Decimal {
516            if budget > Decimal::ZERO {
517                (variance / budget) * dec!(100)
518            } else {
519                Decimal::ZERO
520            }
521        };
522        let variance_ordinary_pct = pct(variance_ordinary, budget_ordinary);
523        let variance_extraordinary_pct = pct(variance_extraordinary, budget_extraordinary);
524        let variance_total_pct = pct(variance_total, budget_total);
525
526        // Check for overruns (> 10%) — seuil comparé en Decimal (#661)
527        const OVERRUN_THRESHOLD_PCT: Decimal = dec!(-10);
528        let has_overruns = variance_ordinary_pct < OVERRUN_THRESHOLD_PCT
529            || variance_extraordinary_pct < OVERRUN_THRESHOLD_PCT
530            || variance_total_pct < OVERRUN_THRESHOLD_PCT;
531
532        if variance_ordinary_pct < OVERRUN_THRESHOLD_PCT {
533            overrun_categories.push("Ordinary charges".to_string());
534        }
535        if variance_extraordinary_pct < OVERRUN_THRESHOLD_PCT {
536            overrun_categories.push("Extraordinary charges".to_string());
537        }
538
539        // Calculate months elapsed (simplified - uses current month)
540        let now = chrono::Utc::now();
541        let months_elapsed = if now.year() == budget.fiscal_year {
542            now.month() as i32
543        } else if now.year() > budget.fiscal_year {
544            12
545        } else {
546            0
547        };
548
549        // Project year-end total (linear projection)
550        let projected_year_end_total = if months_elapsed > 0 {
551            (actual_total / Decimal::from(months_elapsed)) * dec!(12)
552        } else {
553            Decimal::ZERO
554        };
555
556        Ok(Some(BudgetVarianceResponse {
557            budget_id: budget.id,
558            fiscal_year: budget.fiscal_year,
559            building_id: budget.building_id,
560            budgeted_ordinary: budget_ordinary,
561            budgeted_extraordinary: budget_extraordinary,
562            budgeted_total: budget_total,
563            actual_ordinary,
564            actual_extraordinary,
565            actual_total,
566            variance_ordinary,
567            variance_extraordinary,
568            variance_total,
569            variance_ordinary_pct,
570            variance_extraordinary_pct,
571            variance_total_pct,
572            has_overruns,
573            overrun_categories,
574            months_elapsed,
575            projected_year_end_total,
576        }))
577    }
578}
koprogo_api/infrastructure/database/repositories/budget_repository_impl.rs

koprogo_api/infrastructure/database/repositories/
budget_repository_impl.rs
