koprogo_api/infrastructure/database/repositories/
budget_repository_impl.rs

1use crate::application::dto::PageRequest;
2use crate::application::ports::{BudgetRepository, BudgetStatsResponse, BudgetVarianceResponse};
3use crate::domain::entities::{Budget, BudgetStatus, ExpenseCategory};
4use async_trait::async_trait;
5use chrono::Datelike;
6use sqlx::postgres::PgRow;
7use sqlx::{PgPool, Row};
8use uuid::Uuid;
9
10pub struct PostgresBudgetRepository {
11    pool: PgPool,
12}
13
14/// Budget SELECT columns with casts for enums and decimals
15const BUDGET_COLUMNS: &str = r#"
16    id, organization_id, building_id, fiscal_year,
17    ordinary_budget::float8 as ordinary_budget,
18    extraordinary_budget::float8 as extraordinary_budget,
19    total_budget::float8 as total_budget,
20    status::text as status_text,
21    submitted_date, approved_date, approved_by_meeting_id,
22    monthly_provision_amount::float8 as monthly_provision_amount,
23    notes, created_at, updated_at
24"#;
25
26impl PostgresBudgetRepository {
27    pub fn new(pool: PgPool) -> Self {
28        Self { pool }
29    }
30
31    /// Helper: Convert database row to Budget entity
32    fn row_to_budget(&self, row: PgRow) -> Budget {
33        let status_str: String = row.get("status_text");
34        let status = match status_str.as_str() {
35            "draft" => BudgetStatus::Draft,
36            "submitted" => BudgetStatus::Submitted,
37            "approved" => BudgetStatus::Approved,
38            "rejected" => BudgetStatus::Rejected,
39            "archived" => BudgetStatus::Archived,
40            _ => BudgetStatus::Draft,
41        };
42
43        Budget {
44            id: row.get("id"),
45            organization_id: row.get("organization_id"),
46            building_id: row.get("building_id"),
47            fiscal_year: row.get("fiscal_year"),
48            ordinary_budget: row.get("ordinary_budget"),
49            extraordinary_budget: row.get("extraordinary_budget"),
50            total_budget: row.get("total_budget"),
51            status,
52            submitted_date: row.get("submitted_date"),
53            approved_date: row.get("approved_date"),
54            approved_by_meeting_id: row.get("approved_by_meeting_id"),
55            monthly_provision_amount: row.get("monthly_provision_amount"),
56            notes: row.get("notes"),
57            created_at: row.get("created_at"),
58            updated_at: row.get("updated_at"),
59        }
60    }
61}
62
63#[async_trait]
64impl BudgetRepository for PostgresBudgetRepository {
65    async fn create(&self, budget: &Budget) -> Result<Budget, String> {
66        let status_str = match budget.status {
67            BudgetStatus::Draft => "draft",
68            BudgetStatus::Submitted => "submitted",
69            BudgetStatus::Approved => "approved",
70            BudgetStatus::Rejected => "rejected",
71            BudgetStatus::Archived => "archived",
72        };
73
74        let row = sqlx::query(
75            r#"
76            INSERT INTO budgets (
77                id, organization_id, building_id, fiscal_year,
78                ordinary_budget, extraordinary_budget, total_budget,
79                status, submitted_date, approved_date, approved_by_meeting_id,
80                monthly_provision_amount, notes,
81                created_at, updated_at
82            ) VALUES (
83                $1, $2, $3, $4, $5, $6, $7, $8::budget_status,
84                $9, $10, $11, $12, $13, $14, $15
85            )
86            RETURNING id, organization_id, building_id, fiscal_year,
87                ordinary_budget::float8 as ordinary_budget,
88                extraordinary_budget::float8 as extraordinary_budget,
89                total_budget::float8 as total_budget,
90                status::text as status_text,
91                submitted_date, approved_date, approved_by_meeting_id,
92                monthly_provision_amount::float8 as monthly_provision_amount,
93                notes, created_at, updated_at
94            "#,
95        )
96        .bind(budget.id)
97        .bind(budget.organization_id)
98        .bind(budget.building_id)
99        .bind(budget.fiscal_year)
100        .bind(budget.ordinary_budget)
101        .bind(budget.extraordinary_budget)
102        .bind(budget.total_budget)
103        .bind(status_str)
104        .bind(budget.submitted_date)
105        .bind(budget.approved_date)
106        .bind(budget.approved_by_meeting_id)
107        .bind(budget.monthly_provision_amount)
108        .bind(&budget.notes)
109        .bind(budget.created_at)
110        .bind(budget.updated_at)
111        .fetch_one(&self.pool)
112        .await
113        .map_err(|e| format!("Failed to create budget: {}", e))?;
114
115        Ok(self.row_to_budget(row))
116    }
117
118    async fn find_by_id(&self, id: Uuid) -> Result<Option<Budget>, String> {
119        let sql = format!("SELECT {} FROM budgets WHERE id = $1", BUDGET_COLUMNS);
120        let result = sqlx::query(&sql)
121            .bind(id)
122            .fetch_optional(&self.pool)
123            .await
124            .map_err(|e| format!("Failed to find budget: {}", e))?;
125
126        Ok(result.map(|row| self.row_to_budget(row)))
127    }
128
129    async fn find_by_building_and_fiscal_year(
130        &self,
131        building_id: Uuid,
132        fiscal_year: i32,
133    ) -> Result<Option<Budget>, String> {
134        let sql = format!(
135            "SELECT {} FROM budgets WHERE building_id = $1 AND fiscal_year = $2",
136            BUDGET_COLUMNS
137        );
138        let result = sqlx::query(&sql)
139            .bind(building_id)
140            .bind(fiscal_year)
141            .fetch_optional(&self.pool)
142            .await
143            .map_err(|e| format!("Failed to find budget: {}", e))?;
144
145        Ok(result.map(|row| self.row_to_budget(row)))
146    }
147
148    async fn find_by_building(&self, building_id: Uuid) -> Result<Vec<Budget>, String> {
149        let sql = format!(
150            "SELECT {} FROM budgets WHERE building_id = $1 ORDER BY fiscal_year DESC",
151            BUDGET_COLUMNS
152        );
153        let rows = sqlx::query(&sql)
154            .bind(building_id)
155            .fetch_all(&self.pool)
156            .await
157            .map_err(|e| format!("Failed to find budgets: {}", e))?;
158
159        Ok(rows
160            .into_iter()
161            .map(|row| self.row_to_budget(row))
162            .collect())
163    }
164
165    async fn find_active_by_building(&self, building_id: Uuid) -> Result<Option<Budget>, String> {
166        let sql = format!("SELECT {} FROM budgets WHERE building_id = $1 AND status = 'approved' ORDER BY fiscal_year DESC LIMIT 1", BUDGET_COLUMNS);
167        let result = sqlx::query(&sql)
168            .bind(building_id)
169            .fetch_optional(&self.pool)
170            .await
171            .map_err(|e| format!("Failed to find active budget: {}", e))?;
172
173        Ok(result.map(|row| self.row_to_budget(row)))
174    }
175
176    async fn find_by_fiscal_year(
177        &self,
178        organization_id: Uuid,
179        fiscal_year: i32,
180    ) -> Result<Vec<Budget>, String> {
181        let sql = format!("SELECT {} FROM budgets WHERE organization_id = $1 AND fiscal_year = $2 ORDER BY created_at DESC", BUDGET_COLUMNS);
182        let rows = sqlx::query(&sql)
183            .bind(organization_id)
184            .bind(fiscal_year)
185            .fetch_all(&self.pool)
186            .await
187            .map_err(|e| format!("Failed to find budgets: {}", e))?;
188
189        Ok(rows
190            .into_iter()
191            .map(|row| self.row_to_budget(row))
192            .collect())
193    }
194
195    async fn find_by_status(
196        &self,
197        organization_id: Uuid,
198        status: BudgetStatus,
199    ) -> Result<Vec<Budget>, String> {
200        let status_str = match status {
201            BudgetStatus::Draft => "draft",
202            BudgetStatus::Submitted => "submitted",
203            BudgetStatus::Approved => "approved",
204            BudgetStatus::Rejected => "rejected",
205            BudgetStatus::Archived => "archived",
206        };
207
208        let sql = format!("SELECT {} FROM budgets WHERE organization_id = $1 AND status = $2::budget_status ORDER BY created_at DESC", BUDGET_COLUMNS);
209        let rows = sqlx::query(&sql)
210            .bind(organization_id)
211            .bind(status_str)
212            .fetch_all(&self.pool)
213            .await
214            .map_err(|e| format!("Failed to find budgets: {}", e))?;
215
216        Ok(rows
217            .into_iter()
218            .map(|row| self.row_to_budget(row))
219            .collect())
220    }
221
222    async fn find_all_paginated(
223        &self,
224        page_request: &PageRequest,
225        organization_id: Option<Uuid>,
226        building_id: Option<Uuid>,
227        status: Option<BudgetStatus>,
228    ) -> Result<(Vec<Budget>, i64), String> {
229        let offset = (page_request.page - 1) * page_request.per_page;
230
231        // Build dynamic query
232        let mut query = format!("SELECT {} FROM budgets WHERE 1=1", BUDGET_COLUMNS);
233        let mut count_query = String::from("SELECT COUNT(*) as count FROM budgets WHERE 1=1");
234
235        let mut bind_index = 1;
236        let mut bindings: Vec<String> = Vec::new();
237
238        if let Some(org_id) = organization_id {
239            query.push_str(&format!(" AND organization_id = ${}::uuid", bind_index));
240            count_query.push_str(&format!(" AND organization_id = ${}::uuid", bind_index));
241            bindings.push(org_id.to_string());
242            bind_index += 1;
243        }
244
245        if let Some(bldg_id) = building_id {
246            query.push_str(&format!(" AND building_id = ${}::uuid", bind_index));
247            count_query.push_str(&format!(" AND building_id = ${}::uuid", bind_index));
248            bindings.push(bldg_id.to_string());
249            bind_index += 1;
250        }
251
252        if let Some(s) = status {
253            let status_str = match s {
254                BudgetStatus::Draft => "draft",
255                BudgetStatus::Submitted => "submitted",
256                BudgetStatus::Approved => "approved",
257                BudgetStatus::Rejected => "rejected",
258                BudgetStatus::Archived => "archived",
259            };
260            query.push_str(&format!(" AND status = ${}::budget_status", bind_index));
261            count_query.push_str(&format!(" AND status = ${}::budget_status", bind_index));
262            bindings.push(status_str.to_string());
263            bind_index += 1;
264        }
265
266        query.push_str(" ORDER BY fiscal_year DESC, created_at DESC");
267        query.push_str(&format!(
268            " LIMIT ${} OFFSET ${}",
269            bind_index,
270            bind_index + 1
271        ));
272
273        // Execute count query
274        let mut count_q = sqlx::query(&count_query);
275        for binding in &bindings {
276            count_q = count_q.bind(binding);
277        }
278        let count_row = count_q
279            .fetch_one(&self.pool)
280            .await
281            .map_err(|e| format!("Failed to count budgets: {}", e))?;
282        let total: i64 = count_row.get("count");
283
284        // Execute main query
285        let mut main_q = sqlx::query(&query);
286        for binding in &bindings {
287            main_q = main_q.bind(binding);
288        }
289        main_q = main_q.bind(page_request.per_page as i64);
290        main_q = main_q.bind(offset as i64);
291
292        let rows = main_q
293            .fetch_all(&self.pool)
294            .await
295            .map_err(|e| format!("Failed to fetch budgets: {}", e))?;
296
297        let budgets = rows
298            .into_iter()
299            .map(|row| self.row_to_budget(row))
300            .collect();
301
302        Ok((budgets, total))
303    }
304
305    async fn update(&self, budget: &Budget) -> Result<Budget, String> {
306        let status_str = match budget.status {
307            BudgetStatus::Draft => "draft",
308            BudgetStatus::Submitted => "submitted",
309            BudgetStatus::Approved => "approved",
310            BudgetStatus::Rejected => "rejected",
311            BudgetStatus::Archived => "archived",
312        };
313
314        let row = sqlx::query(
315            r#"
316            UPDATE budgets SET
317                ordinary_budget = $2,
318                extraordinary_budget = $3,
319                total_budget = $4,
320                status = $5::budget_status,
321                submitted_date = $6,
322                approved_date = $7,
323                approved_by_meeting_id = $8,
324                monthly_provision_amount = $9,
325                notes = $10,
326                updated_at = $11
327            WHERE id = $1
328            RETURNING id, organization_id, building_id, fiscal_year,
329                ordinary_budget::float8 as ordinary_budget,
330                extraordinary_budget::float8 as extraordinary_budget,
331                total_budget::float8 as total_budget,
332                status::text as status_text,
333                submitted_date, approved_date, approved_by_meeting_id,
334                monthly_provision_amount::float8 as monthly_provision_amount,
335                notes, created_at, updated_at
336            "#,
337        )
338        .bind(budget.id)
339        .bind(budget.ordinary_budget)
340        .bind(budget.extraordinary_budget)
341        .bind(budget.total_budget)
342        .bind(status_str)
343        .bind(budget.submitted_date)
344        .bind(budget.approved_date)
345        .bind(budget.approved_by_meeting_id)
346        .bind(budget.monthly_provision_amount)
347        .bind(&budget.notes)
348        .bind(budget.updated_at)
349        .fetch_one(&self.pool)
350        .await
351        .map_err(|e| format!("Failed to update budget: {}", e))?;
352
353        Ok(self.row_to_budget(row))
354    }
355
356    async fn delete(&self, id: Uuid) -> Result<bool, String> {
357        let result = sqlx::query("DELETE FROM budgets WHERE id = $1")
358            .bind(id)
359            .execute(&self.pool)
360            .await
361            .map_err(|e| format!("Failed to delete budget: {}", e))?;
362
363        Ok(result.rows_affected() > 0)
364    }
365
366    async fn get_stats(&self, organization_id: Uuid) -> Result<BudgetStatsResponse, String> {
367        let row = sqlx::query(
368            r#"
369            SELECT
370                COUNT(*) as total_budgets,
371                COUNT(*) FILTER (WHERE status = 'draft') as draft_count,
372                COUNT(*) FILTER (WHERE status = 'submitted') as submitted_count,
373                COUNT(*) FILTER (WHERE status = 'approved') as approved_count,
374                COUNT(*) FILTER (WHERE status = 'rejected') as rejected_count,
375                COUNT(*) FILTER (WHERE status = 'archived') as archived_count,
376                COALESCE(AVG(total_budget), 0)::float8 as average_total_budget,
377                COALESCE(AVG(monthly_provision_amount), 0)::float8 as average_monthly_provision
378            FROM budgets
379            WHERE organization_id = $1
380            "#,
381        )
382        .bind(organization_id)
383        .fetch_one(&self.pool)
384        .await
385        .map_err(|e| format!("Failed to get budget stats: {}", e))?;
386
387        Ok(BudgetStatsResponse {
388            total_budgets: row.get("total_budgets"),
389            draft_count: row.get("draft_count"),
390            submitted_count: row.get("submitted_count"),
391            approved_count: row.get("approved_count"),
392            rejected_count: row.get("rejected_count"),
393            archived_count: row.get("archived_count"),
394            average_total_budget: row.get("average_total_budget"),
395            average_monthly_provision: row.get("average_monthly_provision"),
396        })
397    }
398
399    async fn get_variance(
400        &self,
401        budget_id: Uuid,
402    ) -> Result<Option<BudgetVarianceResponse>, String> {
403        // First, get the budget
404        let budget = match self.find_by_id(budget_id).await? {
405            Some(b) => b,
406            None => return Ok(None),
407        };
408
409        // Get actual expenses for this budget's fiscal year and building
410        let fiscal_year_start = format!("{}-01-01", budget.fiscal_year);
411        let fiscal_year_end = format!("{}-12-31", budget.fiscal_year);
412
413        let expense_rows = sqlx::query(
414            r#"
415            SELECT
416                category,
417                COALESCE(SUM(amount), 0) as total_amount
418            FROM expenses
419            WHERE building_id = $1
420              AND expense_date >= $2::date
421              AND expense_date <= $3::date
422              AND payment_status = 'paid'
423            GROUP BY category
424            "#,
425        )
426        .bind(budget.building_id)
427        .bind(fiscal_year_start)
428        .bind(fiscal_year_end)
429        .fetch_all(&self.pool)
430        .await
431        .map_err(|e| format!("Failed to get expenses: {}", e))?;
432
433        let mut actual_ordinary = 0.0;
434        let mut actual_extraordinary = 0.0;
435        let mut overrun_categories = Vec::new();
436
437        for row in expense_rows {
438            let category_str: String = row.get("category");
439            let amount: f64 = row.get("total_amount");
440
441            let category = match category_str.as_str() {
442                "utilities" => ExpenseCategory::Utilities,
443                "maintenance" => ExpenseCategory::Maintenance,
444                "repairs" => ExpenseCategory::Repairs,
445                "insurance" => ExpenseCategory::Insurance,
446                "cleaning" => ExpenseCategory::Cleaning,
447                "administration" => ExpenseCategory::Administration,
448                "works" => ExpenseCategory::Works,
449                _ => ExpenseCategory::Other,
450            };
451
452            match category {
453                ExpenseCategory::Works => actual_extraordinary += amount,
454                _ => actual_ordinary += amount,
455            }
456        }
457
458        let actual_total = actual_ordinary + actual_extraordinary;
459
460        // Calculate variances
461        let variance_ordinary = budget.ordinary_budget - actual_ordinary;
462        let variance_extraordinary = budget.extraordinary_budget - actual_extraordinary;
463        let variance_total = budget.total_budget - actual_total;
464
465        // Calculate variance percentages
466        let variance_ordinary_pct = if budget.ordinary_budget > 0.0 {
467            (variance_ordinary / budget.ordinary_budget) * 100.0
468        } else {
469            0.0
470        };
471
472        let variance_extraordinary_pct = if budget.extraordinary_budget > 0.0 {
473            (variance_extraordinary / budget.extraordinary_budget) * 100.0
474        } else {
475            0.0
476        };
477
478        let variance_total_pct = if budget.total_budget > 0.0 {
479            (variance_total / budget.total_budget) * 100.0
480        } else {
481            0.0
482        };
483
484        // Check for overruns (> 10%)
485        let has_overruns = variance_ordinary_pct < -10.0
486            || variance_extraordinary_pct < -10.0
487            || variance_total_pct < -10.0;
488
489        if variance_ordinary_pct < -10.0 {
490            overrun_categories.push("Ordinary charges".to_string());
491        }
492        if variance_extraordinary_pct < -10.0 {
493            overrun_categories.push("Extraordinary charges".to_string());
494        }
495
496        // Calculate months elapsed (simplified - uses current month)
497        let now = chrono::Utc::now();
498        let months_elapsed = if now.year() == budget.fiscal_year {
499            now.month() as i32
500        } else if now.year() > budget.fiscal_year {
501            12
502        } else {
503            0
504        };
505
506        // Project year-end total (linear projection)
507        let projected_year_end_total = if months_elapsed > 0 {
508            (actual_total / months_elapsed as f64) * 12.0
509        } else {
510            0.0
511        };
512
513        Ok(Some(BudgetVarianceResponse {
514            budget_id: budget.id,
515            fiscal_year: budget.fiscal_year,
516            building_id: budget.building_id,
517            budgeted_ordinary: budget.ordinary_budget,
518            budgeted_extraordinary: budget.extraordinary_budget,
519            budgeted_total: budget.total_budget,
520            actual_ordinary,
521            actual_extraordinary,
522            actual_total,
523            variance_ordinary,
524            variance_extraordinary,
525            variance_total,
526            variance_ordinary_pct,
527            variance_extraordinary_pct,
528            variance_total_pct,
529            has_overruns,
530            overrun_categories,
531            months_elapsed,
532            projected_year_end_total,
533        }))
534    }
535}
koprogo_api/infrastructure/database/repositories/budget_repository_impl.rs

koprogo_api/infrastructure/database/repositories/
budget_repository_impl.rs
