koprogo_api/infrastructure/database/repositories/
expense_repository_impl.rs

1use crate::application::dto::{ExpenseFilters, PageRequest};
2use crate::application::ports::ExpenseRepository;
3use crate::domain::entities::{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            INSERT INTO expenses (id, organization_id, building_id, category, description, amount, expense_date, payment_status, supplier, invoice_number, created_at, updated_at)
43            VALUES ($1, $2, $3, CAST($4 AS expense_category), $5, $6, $7, CAST($8 AS payment_status), $9, $10, $11, $12)
44            "#,
45        )
46        .bind(expense.id)
47        .bind(expense.organization_id)
48        .bind(expense.building_id)
49        .bind(category_str)
50        .bind(&expense.description)
51        .bind(expense.amount)
52        .bind(expense.expense_date)
53        .bind(status_str)
54        .bind(&expense.supplier)
55        .bind(&expense.invoice_number)
56        .bind(expense.created_at)
57        .bind(expense.updated_at)
58        .execute(&self.pool)
59        .await
60        .map_err(|e| format!("Database error: {}", e))?;
61
62        Ok(expense.clone())
63    }
64
65    async fn find_by_id(&self, id: Uuid) -> Result<Option<Expense>, String> {
66        let row = sqlx::query(
67            r#"
68            SELECT id, organization_id, building_id,
69                   category::text AS category, description, amount, expense_date,
70                   payment_status::text AS payment_status, supplier, invoice_number, created_at, updated_at
71            FROM expenses
72            WHERE id = $1
73            "#,
74        )
75        .bind(id)
76        .fetch_optional(&self.pool)
77        .await
78        .map_err(|e| format!("Database error: {}", e))?;
79
80        Ok(row.map(|row| {
81            let category_str: String = row.get("category");
82            let category = match category_str.as_str() {
83                "maintenance" => ExpenseCategory::Maintenance,
84                "repairs" => ExpenseCategory::Repairs,
85                "insurance" => ExpenseCategory::Insurance,
86                "utilities" => ExpenseCategory::Utilities,
87                "cleaning" => ExpenseCategory::Cleaning,
88                "administration" => ExpenseCategory::Administration,
89                "works" => ExpenseCategory::Works,
90                _ => ExpenseCategory::Other,
91            };
92
93            let status_str: String = row.get("payment_status");
94            let payment_status = match status_str.as_str() {
95                "paid" => PaymentStatus::Paid,
96                "overdue" => PaymentStatus::Overdue,
97                "cancelled" => PaymentStatus::Cancelled,
98                _ => PaymentStatus::Pending,
99            };
100
101            Expense {
102                id: row.get("id"),
103                organization_id: row.get("organization_id"),
104                building_id: row.get("building_id"),
105                category,
106                description: row.get("description"),
107                amount: row.get("amount"),
108                expense_date: row.get("expense_date"),
109                payment_status,
110                supplier: row.get("supplier"),
111                invoice_number: row.get("invoice_number"),
112                created_at: row.get("created_at"),
113                updated_at: row.get("updated_at"),
114            }
115        }))
116    }
117
118    async fn find_by_building(&self, building_id: Uuid) -> Result<Vec<Expense>, String> {
119        let rows = sqlx::query(
120            r#"
121            SELECT id, organization_id, building_id,
122                   category::text AS category, description, amount, expense_date,
123                   payment_status::text AS payment_status, supplier, invoice_number, created_at, updated_at
124            FROM expenses
125            WHERE building_id = $1
126            ORDER BY expense_date DESC
127            "#,
128        )
129        .bind(building_id)
130        .fetch_all(&self.pool)
131        .await
132        .map_err(|e| format!("Database error: {}", e))?;
133
134        Ok(rows
135            .iter()
136            .map(|row| {
137                let category_str: String = row.get("category");
138                let category = match category_str.as_str() {
139                    "maintenance" => ExpenseCategory::Maintenance,
140                    "repairs" => ExpenseCategory::Repairs,
141                    "insurance" => ExpenseCategory::Insurance,
142                    "utilities" => ExpenseCategory::Utilities,
143                    "cleaning" => ExpenseCategory::Cleaning,
144                    "administration" => ExpenseCategory::Administration,
145                    "works" => ExpenseCategory::Works,
146                    _ => ExpenseCategory::Other,
147                };
148
149                let status_str: String = row.get("payment_status");
150                let payment_status = match status_str.as_str() {
151                    "paid" => PaymentStatus::Paid,
152                    "overdue" => PaymentStatus::Overdue,
153                    "cancelled" => PaymentStatus::Cancelled,
154                    _ => PaymentStatus::Pending,
155                };
156
157                Expense {
158                    id: row.get("id"),
159                    organization_id: row.get("organization_id"),
160                    building_id: row.get("building_id"),
161                    category,
162                    description: row.get("description"),
163                    amount: row.get("amount"),
164                    expense_date: row.get("expense_date"),
165                    payment_status,
166                    supplier: row.get("supplier"),
167                    invoice_number: row.get("invoice_number"),
168                    created_at: row.get("created_at"),
169                    updated_at: row.get("updated_at"),
170                }
171            })
172            .collect())
173    }
174
175    async fn find_all_paginated(
176        &self,
177        page_request: &PageRequest,
178        filters: &ExpenseFilters,
179    ) -> Result<(Vec<Expense>, i64), String> {
180        // Validate page request
181        page_request.validate()?;
182
183        // Build WHERE clause dynamically
184        let mut where_clauses = Vec::new();
185        let mut param_count = 0;
186
187        if filters.building_id.is_some() {
188            param_count += 1;
189            where_clauses.push(format!("building_id = ${}", param_count));
190        }
191
192        if filters.category.is_some() {
193            param_count += 1;
194            where_clauses.push(format!("category = ${}", param_count));
195        }
196
197        if filters.status.is_some() {
198            param_count += 1;
199            where_clauses.push(format!("payment_status = ${}", param_count));
200        }
201
202        if filters.date_from.is_some() {
203            param_count += 1;
204            where_clauses.push(format!("expense_date >= ${}", param_count));
205        }
206
207        if filters.date_to.is_some() {
208            param_count += 1;
209            where_clauses.push(format!("expense_date <= ${}", param_count));
210        }
211
212        if filters.min_amount.is_some() {
213            param_count += 1;
214            where_clauses.push(format!("amount >= ${}", param_count));
215        }
216
217        if filters.max_amount.is_some() {
218            param_count += 1;
219            where_clauses.push(format!("amount <= ${}", param_count));
220        }
221
222        let where_clause = if where_clauses.is_empty() {
223            String::new()
224        } else {
225            format!("WHERE {}", where_clauses.join(" AND "))
226        };
227
228        // Validate sort column (whitelist)
229        let allowed_columns = ["expense_date", "amount", "created_at", "payment_status"];
230        let sort_column = page_request.sort_by.as_deref().unwrap_or("expense_date");
231
232        if !allowed_columns.contains(&sort_column) {
233            return Err(format!("Invalid sort column: {}", sort_column));
234        }
235
236        // Count total items
237        let count_query = format!("SELECT COUNT(*) FROM expenses {}", where_clause);
238        let mut count_query = sqlx::query_scalar::<_, i64>(&count_query);
239
240        if let Some(building_id) = filters.building_id {
241            count_query = count_query.bind(building_id);
242        }
243        if let Some(category) = &filters.category {
244            count_query = count_query.bind(category);
245        }
246        if let Some(status) = &filters.status {
247            count_query = count_query.bind(status);
248        }
249        if let Some(date_from) = filters.date_from {
250            count_query = count_query.bind(date_from);
251        }
252        if let Some(date_to) = filters.date_to {
253            count_query = count_query.bind(date_to);
254        }
255        if let Some(min_amount) = filters.min_amount {
256            count_query = count_query.bind(min_amount);
257        }
258        if let Some(max_amount) = filters.max_amount {
259            count_query = count_query.bind(max_amount);
260        }
261
262        let total_items = count_query
263            .fetch_one(&self.pool)
264            .await
265            .map_err(|e| format!("Database error: {}", e))?;
266
267        // Fetch paginated data
268        param_count += 1;
269        let limit_param = param_count;
270        param_count += 1;
271        let offset_param = param_count;
272
273        let data_query = format!(
274            "SELECT id, organization_id, building_id, category::text AS category, description, amount, expense_date, payment_status::text AS payment_status, supplier, invoice_number, created_at, updated_at \
275             FROM expenses {} ORDER BY {} {} LIMIT ${} OFFSET ${}",
276            where_clause,
277            sort_column,
278            page_request.order.to_sql(),
279            limit_param,
280            offset_param
281        );
282
283        let mut data_query = sqlx::query(&data_query);
284
285        if let Some(building_id) = filters.building_id {
286            data_query = data_query.bind(building_id);
287        }
288        if let Some(category) = &filters.category {
289            data_query = data_query.bind(category);
290        }
291        if let Some(status) = &filters.status {
292            data_query = data_query.bind(status);
293        }
294        if let Some(date_from) = filters.date_from {
295            data_query = data_query.bind(date_from);
296        }
297        if let Some(date_to) = filters.date_to {
298            data_query = data_query.bind(date_to);
299        }
300        if let Some(min_amount) = filters.min_amount {
301            data_query = data_query.bind(min_amount);
302        }
303        if let Some(max_amount) = filters.max_amount {
304            data_query = data_query.bind(max_amount);
305        }
306
307        data_query = data_query
308            .bind(page_request.limit())
309            .bind(page_request.offset());
310
311        let rows = data_query
312            .fetch_all(&self.pool)
313            .await
314            .map_err(|e| format!("Database error: {}", e))?;
315
316        let expenses: Vec<Expense> = rows
317            .iter()
318            .map(|row| {
319                let category_str: String = row.get("category");
320                let category = match category_str.as_str() {
321                    "maintenance" => ExpenseCategory::Maintenance,
322                    "repairs" => ExpenseCategory::Repairs,
323                    "insurance" => ExpenseCategory::Insurance,
324                    "utilities" => ExpenseCategory::Utilities,
325                    "cleaning" => ExpenseCategory::Cleaning,
326                    "administration" => ExpenseCategory::Administration,
327                    "works" => ExpenseCategory::Works,
328                    _ => ExpenseCategory::Other,
329                };
330
331                let status_str: String = row.get("payment_status");
332                let payment_status = match status_str.as_str() {
333                    "paid" => PaymentStatus::Paid,
334                    "overdue" => PaymentStatus::Overdue,
335                    "cancelled" => PaymentStatus::Cancelled,
336                    _ => PaymentStatus::Pending,
337                };
338
339                Expense {
340                    id: row.get("id"),
341                    organization_id: row.get("organization_id"),
342                    building_id: row.get("building_id"),
343                    category,
344                    description: row.get("description"),
345                    amount: row.get("amount"),
346                    expense_date: row.get("expense_date"),
347                    payment_status,
348                    supplier: row.get("supplier"),
349                    invoice_number: row.get("invoice_number"),
350                    created_at: row.get("created_at"),
351                    updated_at: row.get("updated_at"),
352                }
353            })
354            .collect();
355
356        Ok((expenses, total_items))
357    }
358
359    async fn update(&self, expense: &Expense) -> Result<Expense, String> {
360        let status_str = match expense.payment_status {
361            PaymentStatus::Pending => "pending",
362            PaymentStatus::Paid => "paid",
363            PaymentStatus::Overdue => "overdue",
364            PaymentStatus::Cancelled => "cancelled",
365        };
366
367        sqlx::query(
368            r#"
369            UPDATE expenses
370            SET payment_status = $2, updated_at = $3
371            WHERE id = $1
372            "#,
373        )
374        .bind(expense.id)
375        .bind(status_str)
376        .bind(expense.updated_at)
377        .execute(&self.pool)
378        .await
379        .map_err(|e| format!("Database error: {}", e))?;
380
381        Ok(expense.clone())
382    }
383
384    async fn delete(&self, id: Uuid) -> Result<bool, String> {
385        let result = sqlx::query("DELETE FROM expenses WHERE id = $1")
386            .bind(id)
387            .execute(&self.pool)
388            .await
389            .map_err(|e| format!("Database error: {}", e))?;
390
391        Ok(result.rows_affected() > 0)
392    }
393}
koprogo_api/infrastructure/database/repositories/expense_repository_impl.rs

koprogo_api/infrastructure/database/repositories/
expense_repository_impl.rs
