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

koprogo_api/infrastructure/database/repositories/
budget_repository_impl.rs
