1use crate::application::ports::{PaymentRepository, PaymentStats};
2use crate::domain::entities::{Payment, PaymentMethodType, TransactionStatus};
3use async_trait::async_trait;
4use sqlx::PgPool;
5use uuid::Uuid;
6
7pub struct PostgresPaymentRepository {
9 pool: PgPool,
10}
11
12impl PostgresPaymentRepository {
13 pub fn new(pool: PgPool) -> Self {
14 Self { pool }
15 }
16
17 fn status_to_db(status: &TransactionStatus) -> &'static str {
19 match status {
20 TransactionStatus::Pending => "pending",
21 TransactionStatus::Processing => "processing",
22 TransactionStatus::RequiresAction => "requires_action",
23 TransactionStatus::Succeeded => "succeeded",
24 TransactionStatus::Failed => "failed",
25 TransactionStatus::Cancelled => "cancelled",
26 TransactionStatus::Refunded => "refunded",
27 }
28 }
29
30 fn status_from_db(s: &str) -> Result<TransactionStatus, String> {
32 match s {
33 "pending" => Ok(TransactionStatus::Pending),
34 "processing" => Ok(TransactionStatus::Processing),
35 "requires_action" => Ok(TransactionStatus::RequiresAction),
36 "succeeded" => Ok(TransactionStatus::Succeeded),
37 "failed" => Ok(TransactionStatus::Failed),
38 "cancelled" => Ok(TransactionStatus::Cancelled),
39 "refunded" => Ok(TransactionStatus::Refunded),
40 _ => Err(format!("Invalid transaction status: {}", s)),
41 }
42 }
43
44 fn method_type_to_db(method_type: &PaymentMethodType) -> &'static str {
46 match method_type {
47 PaymentMethodType::Card => "card",
48 PaymentMethodType::SepaDebit => "sepa_debit",
49 PaymentMethodType::BankTransfer => "bank_transfer",
50 PaymentMethodType::Cash => "cash",
51 }
52 }
53
54 fn method_type_from_db(s: &str) -> Result<PaymentMethodType, String> {
56 match s {
57 "card" => Ok(PaymentMethodType::Card),
58 "sepa_debit" => Ok(PaymentMethodType::SepaDebit),
59 "bank_transfer" => Ok(PaymentMethodType::BankTransfer),
60 "cash" => Ok(PaymentMethodType::Cash),
61 _ => Err(format!("Invalid payment method type: {}", s)),
62 }
63 }
64}
65
66#[async_trait]
67impl PaymentRepository for PostgresPaymentRepository {
68 async fn create(&self, payment: &Payment) -> Result<Payment, String> {
69 let status_str = Self::status_to_db(&payment.status);
70 let method_type_str = Self::method_type_to_db(&payment.payment_method_type);
71
72 let row = sqlx::query!(
73 r#"
74 INSERT INTO payments (
75 id, organization_id, building_id, owner_id, expense_id, contribution_id,
76 amount_cents, currency, status, payment_method_type,
77 stripe_payment_intent_id, stripe_customer_id, payment_method_id,
78 idempotency_key, description, metadata, failure_reason,
79 refunded_amount_cents, succeeded_at, failed_at, cancelled_at,
80 created_at, updated_at
81 )
82 VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9::TEXT::transaction_status, $10::TEXT::payment_method_type,
83 $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23)
84 RETURNING id, organization_id, building_id, owner_id, expense_id, contribution_id,
85 amount_cents, currency, status AS "status: String",
86 payment_method_type AS "payment_method_type: String",
87 stripe_payment_intent_id, stripe_customer_id, payment_method_id,
88 idempotency_key, description, metadata, failure_reason,
89 refunded_amount_cents, succeeded_at, failed_at, cancelled_at,
90 created_at, updated_at
91 "#,
92 payment.id,
93 payment.organization_id,
94 payment.building_id,
95 payment.owner_id,
96 payment.expense_id,
97 payment.contribution_id,
98 payment.amount_cents,
99 &payment.currency,
100 status_str,
101 method_type_str,
102 payment.stripe_payment_intent_id.as_deref(),
103 payment.stripe_customer_id.as_deref(),
104 payment.payment_method_id,
105 &payment.idempotency_key,
106 payment.description.as_deref(),
107 payment.metadata.as_deref().and_then(|s| serde_json::from_str::<serde_json::Value>(s).ok()),
108 payment.failure_reason.as_deref(),
109 payment.refunded_amount_cents,
110 payment.succeeded_at,
111 payment.failed_at,
112 payment.cancelled_at,
113 payment.created_at,
114 payment.updated_at,
115 )
116 .fetch_one(&self.pool)
117 .await
118 .map_err(|e| format!("Failed to create payment: {}", e))?;
119
120 Ok(Payment {
121 id: row.id,
122 organization_id: row.organization_id,
123 building_id: row.building_id,
124 owner_id: row.owner_id,
125 expense_id: row.expense_id,
126 contribution_id: row.contribution_id,
127 amount_cents: row.amount_cents,
128 currency: row.currency,
129 status: Self::status_from_db(&row.status)?,
130 payment_method_type: Self::method_type_from_db(&row.payment_method_type)?,
131 stripe_payment_intent_id: row.stripe_payment_intent_id,
132 stripe_customer_id: row.stripe_customer_id,
133 payment_method_id: row.payment_method_id,
134 idempotency_key: row.idempotency_key,
135 description: row.description,
136 metadata: row.metadata.map(|v: serde_json::Value| v.to_string()),
137 failure_reason: row.failure_reason,
138 refunded_amount_cents: row.refunded_amount_cents,
139 succeeded_at: row.succeeded_at,
140 failed_at: row.failed_at,
141 cancelled_at: row.cancelled_at,
142 created_at: row.created_at,
143 updated_at: row.updated_at,
144 })
145 }
146
147 async fn find_by_id(&self, id: Uuid) -> Result<Option<Payment>, String> {
148 let row = sqlx::query!(
149 r#"
150 SELECT id, organization_id, building_id, owner_id, expense_id, contribution_id,
151 amount_cents, currency, status AS "status: String",
152 payment_method_type AS "payment_method_type: String",
153 stripe_payment_intent_id, stripe_customer_id, payment_method_id,
154 idempotency_key, description, metadata, failure_reason,
155 refunded_amount_cents, succeeded_at, failed_at, cancelled_at,
156 created_at, updated_at
157 FROM payments
158 WHERE id = $1
159 "#,
160 id
161 )
162 .fetch_optional(&self.pool)
163 .await
164 .map_err(|e| format!("Failed to find payment: {}", e))?;
165
166 match row {
167 Some(row) => Ok(Some(Payment {
168 id: row.id,
169 organization_id: row.organization_id,
170 building_id: row.building_id,
171 owner_id: row.owner_id,
172 expense_id: row.expense_id,
173 contribution_id: row.contribution_id,
174 amount_cents: row.amount_cents,
175 currency: row.currency,
176 status: Self::status_from_db(&row.status)?,
177 payment_method_type: Self::method_type_from_db(&row.payment_method_type)?,
178 stripe_payment_intent_id: row.stripe_payment_intent_id,
179 stripe_customer_id: row.stripe_customer_id,
180 payment_method_id: row.payment_method_id,
181 idempotency_key: row.idempotency_key,
182 description: row.description,
183 metadata: row.metadata.map(|v: serde_json::Value| v.to_string()),
184 failure_reason: row.failure_reason,
185 refunded_amount_cents: row.refunded_amount_cents,
186 succeeded_at: row.succeeded_at,
187 failed_at: row.failed_at,
188 cancelled_at: row.cancelled_at,
189 created_at: row.created_at,
190 updated_at: row.updated_at,
191 })),
192 None => Ok(None),
193 }
194 }
195
196 async fn find_by_stripe_payment_intent_id(
197 &self,
198 stripe_payment_intent_id: &str,
199 ) -> Result<Option<Payment>, String> {
200 let row = sqlx::query!(
201 r#"
202 SELECT id, organization_id, building_id, owner_id, expense_id, contribution_id,
203 amount_cents, currency, status AS "status: String",
204 payment_method_type AS "payment_method_type: String",
205 stripe_payment_intent_id, stripe_customer_id, payment_method_id,
206 idempotency_key, description, metadata, failure_reason,
207 refunded_amount_cents, succeeded_at, failed_at, cancelled_at,
208 created_at, updated_at
209 FROM payments
210 WHERE stripe_payment_intent_id = $1
211 "#,
212 stripe_payment_intent_id
213 )
214 .fetch_optional(&self.pool)
215 .await
216 .map_err(|e| format!("Failed to find payment by Stripe payment intent: {}", e))?;
217
218 match row {
219 Some(row) => Ok(Some(Payment {
220 id: row.id,
221 organization_id: row.organization_id,
222 building_id: row.building_id,
223 owner_id: row.owner_id,
224 expense_id: row.expense_id,
225 contribution_id: row.contribution_id,
226 amount_cents: row.amount_cents,
227 currency: row.currency,
228 status: Self::status_from_db(&row.status)?,
229 payment_method_type: Self::method_type_from_db(&row.payment_method_type)?,
230 stripe_payment_intent_id: row.stripe_payment_intent_id,
231 stripe_customer_id: row.stripe_customer_id,
232 payment_method_id: row.payment_method_id,
233 idempotency_key: row.idempotency_key,
234 description: row.description,
235 metadata: row.metadata.map(|v: serde_json::Value| v.to_string()),
236 failure_reason: row.failure_reason,
237 refunded_amount_cents: row.refunded_amount_cents,
238 succeeded_at: row.succeeded_at,
239 failed_at: row.failed_at,
240 cancelled_at: row.cancelled_at,
241 created_at: row.created_at,
242 updated_at: row.updated_at,
243 })),
244 None => Ok(None),
245 }
246 }
247
248 async fn find_by_idempotency_key(
249 &self,
250 organization_id: Uuid,
251 idempotency_key: &str,
252 ) -> Result<Option<Payment>, String> {
253 let row = sqlx::query!(
254 r#"
255 SELECT id, organization_id, building_id, owner_id, expense_id, contribution_id,
256 amount_cents, currency, status AS "status: String",
257 payment_method_type AS "payment_method_type: String",
258 stripe_payment_intent_id, stripe_customer_id, payment_method_id,
259 idempotency_key, description, metadata, failure_reason,
260 refunded_amount_cents, succeeded_at, failed_at, cancelled_at,
261 created_at, updated_at
262 FROM payments
263 WHERE organization_id = $1 AND idempotency_key = $2
264 "#,
265 organization_id,
266 idempotency_key
267 )
268 .fetch_optional(&self.pool)
269 .await
270 .map_err(|e| format!("Failed to find payment by idempotency key: {}", e))?;
271
272 match row {
273 Some(row) => Ok(Some(Payment {
274 id: row.id,
275 organization_id: row.organization_id,
276 building_id: row.building_id,
277 owner_id: row.owner_id,
278 expense_id: row.expense_id,
279 contribution_id: row.contribution_id,
280 amount_cents: row.amount_cents,
281 currency: row.currency,
282 status: Self::status_from_db(&row.status)?,
283 payment_method_type: Self::method_type_from_db(&row.payment_method_type)?,
284 stripe_payment_intent_id: row.stripe_payment_intent_id,
285 stripe_customer_id: row.stripe_customer_id,
286 payment_method_id: row.payment_method_id,
287 idempotency_key: row.idempotency_key,
288 description: row.description,
289 metadata: row.metadata.map(|v: serde_json::Value| v.to_string()),
290 failure_reason: row.failure_reason,
291 refunded_amount_cents: row.refunded_amount_cents,
292 succeeded_at: row.succeeded_at,
293 failed_at: row.failed_at,
294 cancelled_at: row.cancelled_at,
295 created_at: row.created_at,
296 updated_at: row.updated_at,
297 })),
298 None => Ok(None),
299 }
300 }
301
302 async fn find_by_owner(&self, owner_id: Uuid) -> Result<Vec<Payment>, String> {
303 let rows = sqlx::query!(
304 r#"
305 SELECT id, organization_id, building_id, owner_id, expense_id, contribution_id,
306 amount_cents, currency, status AS "status: String",
307 payment_method_type AS "payment_method_type: String",
308 stripe_payment_intent_id, stripe_customer_id, payment_method_id,
309 idempotency_key, description, metadata, failure_reason,
310 refunded_amount_cents, succeeded_at, failed_at, cancelled_at,
311 created_at, updated_at
312 FROM payments
313 WHERE owner_id = $1
314 ORDER BY created_at DESC
315 "#,
316 owner_id
317 )
318 .fetch_all(&self.pool)
319 .await
320 .map_err(|e| format!("Failed to find payments by owner: {}", e))?;
321
322 rows.into_iter()
323 .map(|row| {
324 Ok(Payment {
325 id: row.id,
326 organization_id: row.organization_id,
327 building_id: row.building_id,
328 owner_id: row.owner_id,
329 expense_id: row.expense_id,
330 contribution_id: row.contribution_id,
331 amount_cents: row.amount_cents,
332 currency: row.currency,
333 status: Self::status_from_db(&row.status)?,
334 payment_method_type: Self::method_type_from_db(&row.payment_method_type)?,
335 stripe_payment_intent_id: row.stripe_payment_intent_id,
336 stripe_customer_id: row.stripe_customer_id,
337 payment_method_id: row.payment_method_id,
338 idempotency_key: row.idempotency_key,
339 description: row.description,
340 metadata: row.metadata.map(|v: serde_json::Value| v.to_string()),
341 failure_reason: row.failure_reason,
342 refunded_amount_cents: row.refunded_amount_cents,
343 succeeded_at: row.succeeded_at,
344 failed_at: row.failed_at,
345 cancelled_at: row.cancelled_at,
346 created_at: row.created_at,
347 updated_at: row.updated_at,
348 })
349 })
350 .collect()
351 }
352
353 async fn find_by_building(&self, building_id: Uuid) -> Result<Vec<Payment>, String> {
354 let rows = sqlx::query!(
355 r#"
356 SELECT id, organization_id, building_id, owner_id, expense_id, contribution_id,
357 amount_cents, currency, status AS "status: String",
358 payment_method_type AS "payment_method_type: String",
359 stripe_payment_intent_id, stripe_customer_id, payment_method_id,
360 idempotency_key, description, metadata, failure_reason,
361 refunded_amount_cents, succeeded_at, failed_at, cancelled_at,
362 created_at, updated_at
363 FROM payments
364 WHERE building_id = $1
365 ORDER BY created_at DESC
366 "#,
367 building_id
368 )
369 .fetch_all(&self.pool)
370 .await
371 .map_err(|e| format!("Failed to find payments by building: {}", e))?;
372
373 rows.into_iter()
374 .map(|row| {
375 Ok(Payment {
376 id: row.id,
377 organization_id: row.organization_id,
378 building_id: row.building_id,
379 owner_id: row.owner_id,
380 expense_id: row.expense_id,
381 contribution_id: row.contribution_id,
382 amount_cents: row.amount_cents,
383 currency: row.currency,
384 status: Self::status_from_db(&row.status)?,
385 payment_method_type: Self::method_type_from_db(&row.payment_method_type)?,
386 stripe_payment_intent_id: row.stripe_payment_intent_id,
387 stripe_customer_id: row.stripe_customer_id,
388 payment_method_id: row.payment_method_id,
389 idempotency_key: row.idempotency_key,
390 description: row.description,
391 metadata: row.metadata.map(|v: serde_json::Value| v.to_string()),
392 failure_reason: row.failure_reason,
393 refunded_amount_cents: row.refunded_amount_cents,
394 succeeded_at: row.succeeded_at,
395 failed_at: row.failed_at,
396 cancelled_at: row.cancelled_at,
397 created_at: row.created_at,
398 updated_at: row.updated_at,
399 })
400 })
401 .collect()
402 }
403
404 async fn find_by_expense(&self, expense_id: Uuid) -> Result<Vec<Payment>, String> {
405 let rows = sqlx::query!(
406 r#"
407 SELECT id, organization_id, building_id, owner_id, expense_id, contribution_id,
408 amount_cents, currency, status AS "status: String",
409 payment_method_type AS "payment_method_type: String",
410 stripe_payment_intent_id, stripe_customer_id, payment_method_id,
411 idempotency_key, description, metadata, failure_reason,
412 refunded_amount_cents, succeeded_at, failed_at, cancelled_at,
413 created_at, updated_at
414 FROM payments
415 WHERE expense_id = $1
416 ORDER BY created_at DESC
417 "#,
418 expense_id
419 )
420 .fetch_all(&self.pool)
421 .await
422 .map_err(|e| format!("Failed to find payments by expense: {}", e))?;
423
424 rows.into_iter()
425 .map(|row| {
426 Ok(Payment {
427 id: row.id,
428 organization_id: row.organization_id,
429 building_id: row.building_id,
430 owner_id: row.owner_id,
431 expense_id: row.expense_id,
432 contribution_id: row.contribution_id,
433 amount_cents: row.amount_cents,
434 currency: row.currency,
435 status: Self::status_from_db(&row.status)?,
436 payment_method_type: Self::method_type_from_db(&row.payment_method_type)?,
437 stripe_payment_intent_id: row.stripe_payment_intent_id,
438 stripe_customer_id: row.stripe_customer_id,
439 payment_method_id: row.payment_method_id,
440 idempotency_key: row.idempotency_key,
441 description: row.description,
442 metadata: row.metadata.map(|v: serde_json::Value| v.to_string()),
443 failure_reason: row.failure_reason,
444 refunded_amount_cents: row.refunded_amount_cents,
445 succeeded_at: row.succeeded_at,
446 failed_at: row.failed_at,
447 cancelled_at: row.cancelled_at,
448 created_at: row.created_at,
449 updated_at: row.updated_at,
450 })
451 })
452 .collect()
453 }
454
455 async fn find_by_organization(&self, organization_id: Uuid) -> Result<Vec<Payment>, String> {
456 let rows = sqlx::query!(
457 r#"
458 SELECT id, organization_id, building_id, owner_id, expense_id, contribution_id,
459 amount_cents, currency, status AS "status: String",
460 payment_method_type AS "payment_method_type: String",
461 stripe_payment_intent_id, stripe_customer_id, payment_method_id,
462 idempotency_key, description, metadata, failure_reason,
463 refunded_amount_cents, succeeded_at, failed_at, cancelled_at,
464 created_at, updated_at
465 FROM payments
466 WHERE organization_id = $1
467 ORDER BY created_at DESC
468 "#,
469 organization_id
470 )
471 .fetch_all(&self.pool)
472 .await
473 .map_err(|e| format!("Failed to find payments by organization: {}", e))?;
474
475 rows.into_iter()
476 .map(|row| {
477 Ok(Payment {
478 id: row.id,
479 organization_id: row.organization_id,
480 building_id: row.building_id,
481 owner_id: row.owner_id,
482 expense_id: row.expense_id,
483 contribution_id: row.contribution_id,
484 amount_cents: row.amount_cents,
485 currency: row.currency,
486 status: Self::status_from_db(&row.status)?,
487 payment_method_type: Self::method_type_from_db(&row.payment_method_type)?,
488 stripe_payment_intent_id: row.stripe_payment_intent_id,
489 stripe_customer_id: row.stripe_customer_id,
490 payment_method_id: row.payment_method_id,
491 idempotency_key: row.idempotency_key,
492 description: row.description,
493 metadata: row.metadata.map(|v: serde_json::Value| v.to_string()),
494 failure_reason: row.failure_reason,
495 refunded_amount_cents: row.refunded_amount_cents,
496 succeeded_at: row.succeeded_at,
497 failed_at: row.failed_at,
498 cancelled_at: row.cancelled_at,
499 created_at: row.created_at,
500 updated_at: row.updated_at,
501 })
502 })
503 .collect()
504 }
505
506 async fn find_by_status(
507 &self,
508 organization_id: Uuid,
509 status: TransactionStatus,
510 ) -> Result<Vec<Payment>, String> {
511 let status_str = Self::status_to_db(&status);
512
513 let rows = sqlx::query!(
514 r#"
515 SELECT id, organization_id, building_id, owner_id, expense_id, contribution_id,
516 amount_cents, currency, status AS "status: String",
517 payment_method_type AS "payment_method_type: String",
518 stripe_payment_intent_id, stripe_customer_id, payment_method_id,
519 idempotency_key, description, metadata, failure_reason,
520 refunded_amount_cents, succeeded_at, failed_at, cancelled_at,
521 created_at, updated_at
522 FROM payments
523 WHERE organization_id = $1 AND status = $2::TEXT::transaction_status
524 ORDER BY created_at DESC
525 "#,
526 organization_id,
527 status_str
528 )
529 .fetch_all(&self.pool)
530 .await
531 .map_err(|e| format!("Failed to find payments by status: {}", e))?;
532
533 rows.into_iter()
534 .map(|row| {
535 Ok(Payment {
536 id: row.id,
537 organization_id: row.organization_id,
538 building_id: row.building_id,
539 owner_id: row.owner_id,
540 expense_id: row.expense_id,
541 contribution_id: row.contribution_id,
542 amount_cents: row.amount_cents,
543 currency: row.currency,
544 status: Self::status_from_db(&row.status)?,
545 payment_method_type: Self::method_type_from_db(&row.payment_method_type)?,
546 stripe_payment_intent_id: row.stripe_payment_intent_id,
547 stripe_customer_id: row.stripe_customer_id,
548 payment_method_id: row.payment_method_id,
549 idempotency_key: row.idempotency_key,
550 description: row.description,
551 metadata: row.metadata.map(|v: serde_json::Value| v.to_string()),
552 failure_reason: row.failure_reason,
553 refunded_amount_cents: row.refunded_amount_cents,
554 succeeded_at: row.succeeded_at,
555 failed_at: row.failed_at,
556 cancelled_at: row.cancelled_at,
557 created_at: row.created_at,
558 updated_at: row.updated_at,
559 })
560 })
561 .collect()
562 }
563
564 async fn find_by_building_and_status(
565 &self,
566 building_id: Uuid,
567 status: TransactionStatus,
568 ) -> Result<Vec<Payment>, String> {
569 let status_str = Self::status_to_db(&status);
570
571 let rows = sqlx::query!(
572 r#"
573 SELECT id, organization_id, building_id, owner_id, expense_id, contribution_id,
574 amount_cents, currency, status AS "status: String",
575 payment_method_type AS "payment_method_type: String",
576 stripe_payment_intent_id, stripe_customer_id, payment_method_id,
577 idempotency_key, description, metadata, failure_reason,
578 refunded_amount_cents, succeeded_at, failed_at, cancelled_at,
579 created_at, updated_at
580 FROM payments
581 WHERE building_id = $1 AND status = $2::TEXT::transaction_status
582 ORDER BY created_at DESC
583 "#,
584 building_id,
585 status_str
586 )
587 .fetch_all(&self.pool)
588 .await
589 .map_err(|e| format!("Failed to find payments by building and status: {}", e))?;
590
591 rows.into_iter()
592 .map(|row| {
593 Ok(Payment {
594 id: row.id,
595 organization_id: row.organization_id,
596 building_id: row.building_id,
597 owner_id: row.owner_id,
598 expense_id: row.expense_id,
599 contribution_id: row.contribution_id,
600 amount_cents: row.amount_cents,
601 currency: row.currency,
602 status: Self::status_from_db(&row.status)?,
603 payment_method_type: Self::method_type_from_db(&row.payment_method_type)?,
604 stripe_payment_intent_id: row.stripe_payment_intent_id,
605 stripe_customer_id: row.stripe_customer_id,
606 payment_method_id: row.payment_method_id,
607 idempotency_key: row.idempotency_key,
608 description: row.description,
609 metadata: row.metadata.map(|v: serde_json::Value| v.to_string()),
610 failure_reason: row.failure_reason,
611 refunded_amount_cents: row.refunded_amount_cents,
612 succeeded_at: row.succeeded_at,
613 failed_at: row.failed_at,
614 cancelled_at: row.cancelled_at,
615 created_at: row.created_at,
616 updated_at: row.updated_at,
617 })
618 })
619 .collect()
620 }
621
622 async fn find_pending(&self, organization_id: Uuid) -> Result<Vec<Payment>, String> {
623 self.find_by_status(organization_id, TransactionStatus::Pending)
624 .await
625 }
626
627 async fn find_failed(&self, organization_id: Uuid) -> Result<Vec<Payment>, String> {
628 self.find_by_status(organization_id, TransactionStatus::Failed)
629 .await
630 }
631
632 async fn update(&self, payment: &Payment) -> Result<Payment, String> {
633 let status_str = Self::status_to_db(&payment.status);
634 let method_type_str = Self::method_type_to_db(&payment.payment_method_type);
635
636 let row = sqlx::query!(
637 r#"
638 UPDATE payments
639 SET organization_id = $2,
640 building_id = $3,
641 owner_id = $4,
642 expense_id = $5,
643 contribution_id = $6,
644 amount_cents = $7,
645 currency = $8,
646 status = $9::TEXT::transaction_status,
647 payment_method_type = $10::TEXT::payment_method_type,
648 stripe_payment_intent_id = $11,
649 stripe_customer_id = $12,
650 payment_method_id = $13,
651 idempotency_key = $14,
652 description = $15,
653 metadata = $16,
654 failure_reason = $17,
655 refunded_amount_cents = $18,
656 succeeded_at = $19,
657 failed_at = $20,
658 cancelled_at = $21,
659 updated_at = $22
660 WHERE id = $1
661 RETURNING id, organization_id, building_id, owner_id, expense_id, contribution_id,
662 amount_cents, currency, status AS "status: String",
663 payment_method_type AS "payment_method_type: String",
664 stripe_payment_intent_id, stripe_customer_id, payment_method_id,
665 idempotency_key, description, metadata, failure_reason,
666 refunded_amount_cents, succeeded_at, failed_at, cancelled_at,
667 created_at, updated_at
668 "#,
669 payment.id,
670 payment.organization_id,
671 payment.building_id,
672 payment.owner_id,
673 payment.expense_id,
674 payment.contribution_id,
675 payment.amount_cents,
676 &payment.currency,
677 status_str,
678 method_type_str,
679 payment.stripe_payment_intent_id.as_deref(),
680 payment.stripe_customer_id.as_deref(),
681 payment.payment_method_id,
682 &payment.idempotency_key,
683 payment.description.as_deref(),
684 payment
685 .metadata
686 .as_deref()
687 .and_then(|s| serde_json::from_str::<serde_json::Value>(s).ok()),
688 payment.failure_reason.as_deref(),
689 payment.refunded_amount_cents,
690 payment.succeeded_at,
691 payment.failed_at,
692 payment.cancelled_at,
693 payment.updated_at,
694 )
695 .fetch_one(&self.pool)
696 .await
697 .map_err(|e| format!("Failed to update payment: {}", e))?;
698
699 Ok(Payment {
700 id: row.id,
701 organization_id: row.organization_id,
702 building_id: row.building_id,
703 owner_id: row.owner_id,
704 expense_id: row.expense_id,
705 contribution_id: row.contribution_id,
706 amount_cents: row.amount_cents,
707 currency: row.currency,
708 status: Self::status_from_db(&row.status)?,
709 payment_method_type: Self::method_type_from_db(&row.payment_method_type)?,
710 stripe_payment_intent_id: row.stripe_payment_intent_id,
711 stripe_customer_id: row.stripe_customer_id,
712 payment_method_id: row.payment_method_id,
713 idempotency_key: row.idempotency_key,
714 description: row.description,
715 metadata: row.metadata.map(|v: serde_json::Value| v.to_string()),
716 failure_reason: row.failure_reason,
717 refunded_amount_cents: row.refunded_amount_cents,
718 succeeded_at: row.succeeded_at,
719 failed_at: row.failed_at,
720 cancelled_at: row.cancelled_at,
721 created_at: row.created_at,
722 updated_at: row.updated_at,
723 })
724 }
725
726 async fn delete(&self, id: Uuid) -> Result<bool, String> {
727 let result = sqlx::query!("DELETE FROM payments WHERE id = $1", id)
728 .execute(&self.pool)
729 .await
730 .map_err(|e| format!("Failed to delete payment: {}", e))?;
731
732 Ok(result.rows_affected() > 0)
733 }
734
735 async fn get_total_paid_for_expense(&self, expense_id: Uuid) -> Result<i64, String> {
736 let row = sqlx::query!(
737 r#"
738 SELECT COALESCE(SUM(amount_cents - refunded_amount_cents), 0)::BIGINT AS "total!"
739 FROM payments
740 WHERE expense_id = $1 AND status = 'succeeded'
741 "#,
742 expense_id
743 )
744 .fetch_one(&self.pool)
745 .await
746 .map_err(|e| format!("Failed to get total paid for expense: {}", e))?;
747
748 Ok(row.total)
749 }
750
751 async fn get_total_paid_by_owner(&self, owner_id: Uuid) -> Result<i64, String> {
752 let row = sqlx::query!(
753 r#"
754 SELECT COALESCE(SUM(amount_cents - refunded_amount_cents), 0)::BIGINT AS "total!"
755 FROM payments
756 WHERE owner_id = $1 AND status = 'succeeded'
757 "#,
758 owner_id
759 )
760 .fetch_one(&self.pool)
761 .await
762 .map_err(|e| format!("Failed to get total paid by owner: {}", e))?;
763
764 Ok(row.total)
765 }
766
767 async fn get_total_paid_for_building(&self, building_id: Uuid) -> Result<i64, String> {
768 let row = sqlx::query!(
769 r#"
770 SELECT COALESCE(SUM(amount_cents - refunded_amount_cents), 0)::BIGINT AS "total!"
771 FROM payments
772 WHERE building_id = $1 AND status = 'succeeded'
773 "#,
774 building_id
775 )
776 .fetch_one(&self.pool)
777 .await
778 .map_err(|e| format!("Failed to get total paid for building: {}", e))?;
779
780 Ok(row.total)
781 }
782
783 async fn get_owner_payment_stats(&self, owner_id: Uuid) -> Result<PaymentStats, String> {
784 let row = sqlx::query!(
785 r#"
786 SELECT
787 COUNT(*) AS "total_count!",
788 COUNT(*) FILTER (WHERE status = 'succeeded') AS "succeeded_count!",
789 COUNT(*) FILTER (WHERE status = 'failed') AS "failed_count!",
790 COUNT(*) FILTER (WHERE status = 'pending') AS "pending_count!",
791 COALESCE(SUM(amount_cents)::BIGINT, 0) AS "total_amount_cents!",
792 COALESCE((SUM(amount_cents) FILTER (WHERE status = 'succeeded'))::BIGINT, 0) AS "total_succeeded_cents!",
793 COALESCE(SUM(refunded_amount_cents)::BIGINT, 0) AS "total_refunded_cents!"
794 FROM payments
795 WHERE owner_id = $1
796 "#,
797 owner_id
798 )
799 .fetch_one(&self.pool)
800 .await
801 .map_err(|e| format!("Failed to get owner payment stats: {}", e))?;
802
803 Ok(PaymentStats {
804 total_count: row.total_count,
805 succeeded_count: row.succeeded_count,
806 failed_count: row.failed_count,
807 pending_count: row.pending_count,
808 total_amount_cents: row.total_amount_cents,
809 total_succeeded_cents: row.total_succeeded_cents,
810 total_refunded_cents: row.total_refunded_cents,
811 net_amount_cents: row.total_succeeded_cents - row.total_refunded_cents,
812 })
813 }
814
815 async fn get_building_payment_stats(&self, building_id: Uuid) -> Result<PaymentStats, String> {
816 let row = sqlx::query!(
817 r#"
818 SELECT
819 COUNT(*) AS "total_count!",
820 COUNT(*) FILTER (WHERE status = 'succeeded') AS "succeeded_count!",
821 COUNT(*) FILTER (WHERE status = 'failed') AS "failed_count!",
822 COUNT(*) FILTER (WHERE status = 'pending') AS "pending_count!",
823 COALESCE(SUM(amount_cents)::BIGINT, 0) AS "total_amount_cents!",
824 COALESCE((SUM(amount_cents) FILTER (WHERE status = 'succeeded'))::BIGINT, 0) AS "total_succeeded_cents!",
825 COALESCE(SUM(refunded_amount_cents)::BIGINT, 0) AS "total_refunded_cents!"
826 FROM payments
827 WHERE building_id = $1
828 "#,
829 building_id
830 )
831 .fetch_one(&self.pool)
832 .await
833 .map_err(|e| format!("Failed to get building payment stats: {}", e))?;
834
835 Ok(PaymentStats {
836 total_count: row.total_count,
837 succeeded_count: row.succeeded_count,
838 failed_count: row.failed_count,
839 pending_count: row.pending_count,
840 total_amount_cents: row.total_amount_cents,
841 total_succeeded_cents: row.total_succeeded_cents,
842 total_refunded_cents: row.total_refunded_cents,
843 net_amount_cents: row.total_succeeded_cents - row.total_refunded_cents,
844 })
845 }
846}