koprogo_api/infrastructure/database/repositories/
payment_repository_impl.rs

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

koprogo_api/infrastructure/database/repositories/
payment_repository_impl.rs
