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 INSERT INTO expenses (id, organization_id, building_id, category, description, amount, expense_date, payment_status, supplier, invoice_number, account_code, 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, $13)
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.account_code)
57 .bind(expense.created_at)
58 .bind(expense.updated_at)
59 .execute(&self.pool)
60 .await
61 .map_err(|e| format!("Database error: {}", e))?;
62
63 Ok(expense.clone())
64 }
65
66 async fn find_by_id(&self, id: Uuid) -> Result<Option<Expense>, String> {
67 let row = sqlx::query(
68 r#"
69 SELECT id, organization_id, building_id,
70 category::text AS category, description, amount, expense_date,
71 payment_status::text AS payment_status, approval_status::text AS approval_status,
72 submitted_at, approved_by, approved_at, rejection_reason, paid_date,
73 supplier, invoice_number, account_code, created_at, updated_at
74 FROM expenses
75 WHERE id = $1
76 "#,
77 )
78 .bind(id)
79 .fetch_optional(&self.pool)
80 .await
81 .map_err(|e| format!("Database error: {}", e))?;
82
83 Ok(row.map(|row| {
84 let category_str: String = row.get("category");
85 let category = match category_str.as_str() {
86 "maintenance" => ExpenseCategory::Maintenance,
87 "repairs" => ExpenseCategory::Repairs,
88 "insurance" => ExpenseCategory::Insurance,
89 "utilities" => ExpenseCategory::Utilities,
90 "cleaning" => ExpenseCategory::Cleaning,
91 "administration" => ExpenseCategory::Administration,
92 "works" => ExpenseCategory::Works,
93 _ => ExpenseCategory::Other,
94 };
95
96 let status_str: String = row.get("payment_status");
97 let payment_status = match status_str.as_str() {
98 "paid" => PaymentStatus::Paid,
99 "overdue" => PaymentStatus::Overdue,
100 "cancelled" => PaymentStatus::Cancelled,
101 _ => PaymentStatus::Pending,
102 };
103
104 let approval_status_str: String = row.get("approval_status");
105 let approval_status = match approval_status_str.as_str() {
106 "pending_approval" => ApprovalStatus::PendingApproval,
107 "approved" => ApprovalStatus::Approved,
108 "rejected" => ApprovalStatus::Rejected,
109 _ => ApprovalStatus::Draft,
110 };
111
112 Expense {
113 id: row.get("id"),
114 organization_id: row.get("organization_id"),
115 building_id: row.get("building_id"),
116 category,
117 description: row.get("description"),
118 amount: row.get("amount"),
119 amount_excl_vat: None,
120 vat_rate: None,
121 vat_amount: None,
122 amount_incl_vat: Some(row.get("amount")),
123 expense_date: row.get("expense_date"),
124 invoice_date: None,
125 due_date: None,
126 paid_date: row.try_get("paid_date").ok(),
127 approval_status,
128 submitted_at: row.try_get("submitted_at").ok(),
129 approved_by: row.try_get("approved_by").ok(),
130 approved_at: row.try_get("approved_at").ok(),
131 rejection_reason: row.try_get("rejection_reason").ok(),
132 payment_status,
133 supplier: row.get("supplier"),
134 invoice_number: row.get("invoice_number"),
135 account_code: row.get("account_code"),
136 created_at: row.get("created_at"),
137 updated_at: row.get("updated_at"),
138 }
139 }))
140 }
141
142 async fn find_by_building(&self, building_id: Uuid) -> Result<Vec<Expense>, String> {
143 let rows = sqlx::query(
144 r#"
145 SELECT id, organization_id, building_id,
146 category::text AS category, description, amount, expense_date,
147 payment_status::text AS payment_status, approval_status::text AS approval_status,
148 submitted_at, approved_by, approved_at, rejection_reason, paid_date,
149 supplier, invoice_number, account_code, created_at, updated_at
150 FROM expenses
151 WHERE building_id = $1
152 ORDER BY expense_date DESC
153 "#,
154 )
155 .bind(building_id)
156 .fetch_all(&self.pool)
157 .await
158 .map_err(|e| format!("Database error: {}", e))?;
159
160 Ok(rows
161 .iter()
162 .map(|row| {
163 let category_str: String = row.get("category");
164 let category = match category_str.as_str() {
165 "maintenance" => ExpenseCategory::Maintenance,
166 "repairs" => ExpenseCategory::Repairs,
167 "insurance" => ExpenseCategory::Insurance,
168 "utilities" => ExpenseCategory::Utilities,
169 "cleaning" => ExpenseCategory::Cleaning,
170 "administration" => ExpenseCategory::Administration,
171 "works" => ExpenseCategory::Works,
172 _ => ExpenseCategory::Other,
173 };
174
175 let status_str: String = row.get("payment_status");
176 let payment_status = match status_str.as_str() {
177 "paid" => PaymentStatus::Paid,
178 "overdue" => PaymentStatus::Overdue,
179 "cancelled" => PaymentStatus::Cancelled,
180 _ => PaymentStatus::Pending,
181 };
182
183 let approval_status_str: String = row.get("approval_status");
184 let approval_status = match approval_status_str.as_str() {
185 "pending_approval" => ApprovalStatus::PendingApproval,
186 "approved" => ApprovalStatus::Approved,
187 "rejected" => ApprovalStatus::Rejected,
188 _ => ApprovalStatus::Draft,
189 };
190
191 Expense {
192 id: row.get("id"),
193 organization_id: row.get("organization_id"),
194 building_id: row.get("building_id"),
195 category,
196 description: row.get("description"),
197 amount: row.get("amount"),
198 amount_excl_vat: None,
199 vat_rate: None,
200 vat_amount: None,
201 amount_incl_vat: Some(row.get("amount")),
202 expense_date: row.get("expense_date"),
203 invoice_date: None,
204 due_date: None,
205 paid_date: row.try_get("paid_date").ok(),
206 approval_status,
207 submitted_at: row.try_get("submitted_at").ok(),
208 approved_by: row.try_get("approved_by").ok(),
209 approved_at: row.try_get("approved_at").ok(),
210 rejection_reason: row.try_get("rejection_reason").ok(),
211 payment_status,
212 supplier: row.get("supplier"),
213 invoice_number: row.get("invoice_number"),
214 account_code: row.get("account_code"),
215 created_at: row.get("created_at"),
216 updated_at: row.get("updated_at"),
217 }
218 })
219 .collect())
220 }
221
222 async fn find_all_paginated(
223 &self,
224 page_request: &PageRequest,
225 filters: &ExpenseFilters,
226 ) -> Result<(Vec<Expense>, i64), String> {
227 page_request.validate()?;
229
230 let mut where_clauses = Vec::new();
232 let mut param_count = 0;
233
234 if filters.building_id.is_some() {
235 param_count += 1;
236 where_clauses.push(format!("building_id = ${}", param_count));
237 }
238
239 if filters.category.is_some() {
240 param_count += 1;
241 where_clauses.push(format!("category = ${}", param_count));
242 }
243
244 if filters.status.is_some() {
245 param_count += 1;
246 where_clauses.push(format!("payment_status = ${}", param_count));
247 }
248
249 if filters.date_from.is_some() {
250 param_count += 1;
251 where_clauses.push(format!("expense_date >= ${}", param_count));
252 }
253
254 if filters.date_to.is_some() {
255 param_count += 1;
256 where_clauses.push(format!("expense_date <= ${}", param_count));
257 }
258
259 if filters.min_amount.is_some() {
260 param_count += 1;
261 where_clauses.push(format!("amount >= ${}", param_count));
262 }
263
264 if filters.max_amount.is_some() {
265 param_count += 1;
266 where_clauses.push(format!("amount <= ${}", param_count));
267 }
268
269 let where_clause = if where_clauses.is_empty() {
270 String::new()
271 } else {
272 format!("WHERE {}", where_clauses.join(" AND "))
273 };
274
275 let allowed_columns = ["expense_date", "amount", "created_at", "payment_status"];
277 let sort_column = page_request.sort_by.as_deref().unwrap_or("expense_date");
278
279 if !allowed_columns.contains(&sort_column) {
280 return Err(format!("Invalid sort column: {}", sort_column));
281 }
282
283 let count_query = format!("SELECT COUNT(*) FROM expenses {}", where_clause);
285 let mut count_query = sqlx::query_scalar::<_, i64>(&count_query);
286
287 if let Some(building_id) = filters.building_id {
288 count_query = count_query.bind(building_id);
289 }
290 if let Some(category) = &filters.category {
291 count_query = count_query.bind(category);
292 }
293 if let Some(status) = &filters.status {
294 count_query = count_query.bind(status);
295 }
296 if let Some(date_from) = filters.date_from {
297 count_query = count_query.bind(date_from);
298 }
299 if let Some(date_to) = filters.date_to {
300 count_query = count_query.bind(date_to);
301 }
302 if let Some(min_amount) = filters.min_amount {
303 count_query = count_query.bind(min_amount);
304 }
305 if let Some(max_amount) = filters.max_amount {
306 count_query = count_query.bind(max_amount);
307 }
308
309 let total_items = count_query
310 .fetch_one(&self.pool)
311 .await
312 .map_err(|e| format!("Database error: {}", e))?;
313
314 param_count += 1;
316 let limit_param = param_count;
317 param_count += 1;
318 let offset_param = param_count;
319
320 let data_query = format!(
321 "SELECT 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, created_at, updated_at \
322 FROM expenses {} ORDER BY {} {} LIMIT ${} OFFSET ${}",
323 where_clause,
324 sort_column,
325 page_request.order.to_sql(),
326 limit_param,
327 offset_param
328 );
329
330 let mut data_query = sqlx::query(&data_query);
331
332 if let Some(building_id) = filters.building_id {
333 data_query = data_query.bind(building_id);
334 }
335 if let Some(category) = &filters.category {
336 data_query = data_query.bind(category);
337 }
338 if let Some(status) = &filters.status {
339 data_query = data_query.bind(status);
340 }
341 if let Some(date_from) = filters.date_from {
342 data_query = data_query.bind(date_from);
343 }
344 if let Some(date_to) = filters.date_to {
345 data_query = data_query.bind(date_to);
346 }
347 if let Some(min_amount) = filters.min_amount {
348 data_query = data_query.bind(min_amount);
349 }
350 if let Some(max_amount) = filters.max_amount {
351 data_query = data_query.bind(max_amount);
352 }
353
354 data_query = data_query
355 .bind(page_request.limit())
356 .bind(page_request.offset());
357
358 let rows = data_query
359 .fetch_all(&self.pool)
360 .await
361 .map_err(|e| format!("Database error: {}", e))?;
362
363 let expenses: Vec<Expense> = rows
364 .iter()
365 .map(|row| {
366 let category_str: String = row.get("category");
367 let category = match category_str.as_str() {
368 "maintenance" => ExpenseCategory::Maintenance,
369 "repairs" => ExpenseCategory::Repairs,
370 "insurance" => ExpenseCategory::Insurance,
371 "utilities" => ExpenseCategory::Utilities,
372 "cleaning" => ExpenseCategory::Cleaning,
373 "administration" => ExpenseCategory::Administration,
374 "works" => ExpenseCategory::Works,
375 _ => ExpenseCategory::Other,
376 };
377
378 let status_str: String = row.get("payment_status");
379 let payment_status = match status_str.as_str() {
380 "paid" => PaymentStatus::Paid,
381 "overdue" => PaymentStatus::Overdue,
382 "cancelled" => PaymentStatus::Cancelled,
383 _ => PaymentStatus::Pending,
384 };
385
386 let approval_status_str: String = row.get("approval_status");
387 let approval_status = match approval_status_str.as_str() {
388 "pending_approval" => ApprovalStatus::PendingApproval,
389 "approved" => ApprovalStatus::Approved,
390 "rejected" => ApprovalStatus::Rejected,
391 _ => ApprovalStatus::Draft,
392 };
393
394 Expense {
395 id: row.get("id"),
396 organization_id: row.get("organization_id"),
397 building_id: row.get("building_id"),
398 category,
399 description: row.get("description"),
400 amount: row.get("amount"),
401 amount_excl_vat: None,
402 vat_rate: None,
403 vat_amount: None,
404 amount_incl_vat: Some(row.get("amount")),
405 expense_date: row.get("expense_date"),
406 invoice_date: None,
407 due_date: None,
408 paid_date: row.try_get("paid_date").ok(),
409 approval_status,
410 submitted_at: row.try_get("submitted_at").ok(),
411 approved_by: row.try_get("approved_by").ok(),
412 approved_at: row.try_get("approved_at").ok(),
413 rejection_reason: row.try_get("rejection_reason").ok(),
414 payment_status,
415 supplier: row.get("supplier"),
416 invoice_number: row.get("invoice_number"),
417 account_code: row.get("account_code"),
418 created_at: row.get("created_at"),
419 updated_at: row.get("updated_at"),
420 }
421 })
422 .collect();
423
424 Ok((expenses, total_items))
425 }
426
427 async fn update(&self, expense: &Expense) -> Result<Expense, String> {
428 let payment_status_str = match expense.payment_status {
429 PaymentStatus::Pending => "pending",
430 PaymentStatus::Paid => "paid",
431 PaymentStatus::Overdue => "overdue",
432 PaymentStatus::Cancelled => "cancelled",
433 };
434
435 let approval_status_str = match expense.approval_status {
436 ApprovalStatus::Draft => "draft",
437 ApprovalStatus::PendingApproval => "pending_approval",
438 ApprovalStatus::Approved => "approved",
439 ApprovalStatus::Rejected => "rejected",
440 };
441
442 sqlx::query(
443 r#"
444 UPDATE expenses
445 SET
446 payment_status = CAST($2 AS payment_status),
447 approval_status = CAST($3 AS approval_status),
448 submitted_at = $4,
449 approved_by = $5,
450 approved_at = $6,
451 rejection_reason = $7,
452 paid_date = $8,
453 updated_at = $9
454 WHERE id = $1
455 "#,
456 )
457 .bind(expense.id)
458 .bind(payment_status_str)
459 .bind(approval_status_str)
460 .bind(expense.submitted_at)
461 .bind(expense.approved_by)
462 .bind(expense.approved_at)
463 .bind(expense.rejection_reason.as_deref())
464 .bind(expense.paid_date)
465 .bind(expense.updated_at)
466 .execute(&self.pool)
467 .await
468 .map_err(|e| format!("Database error: {}", e))?;
469
470 Ok(expense.clone())
471 }
472
473 async fn delete(&self, id: Uuid) -> Result<bool, String> {
474 let result = sqlx::query("DELETE FROM expenses WHERE id = $1")
475 .bind(id)
476 .execute(&self.pool)
477 .await
478 .map_err(|e| format!("Database error: {}", e))?;
479
480 Ok(result.rows_affected() > 0)
481 }
482}