stateset_db/sqlite/
store_credits.rs

1//! SQLite implementation of store credit repository
2
3use super::{
4    map_db_error, parse_datetime_row, parse_decimal_row, parse_enum_row, parse_uuid_row,
5    with_immediate_transaction,
6};
7use chrono::Utc;
8use r2d2::Pool;
9use r2d2_sqlite::SqliteConnectionManager;
10use rust_decimal::Decimal;
11use stateset_core::{
12    AdjustStoreCredit, CommerceError, CreateStoreCredit, Result, StoreCredit, StoreCreditFilter,
13    StoreCreditId, StoreCreditRepository, StoreCreditStatus, StoreCreditTransaction,
14    StoreCreditTransactionId,
15};
16
17#[derive(Debug)]
18pub struct SqliteStoreCreditRepository {
19    pool: Pool<SqliteConnectionManager>,
20}
21
22impl SqliteStoreCreditRepository {
23    #[must_use]
24    pub const fn new(pool: Pool<SqliteConnectionManager>) -> Self {
25        Self { pool }
26    }
27
28    fn conn(&self) -> Result<r2d2::PooledConnection<SqliteConnectionManager>> {
29        self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))
30    }
31
32    fn row_to_store_credit(row: &rusqlite::Row<'_>) -> rusqlite::Result<StoreCredit> {
33        Ok(StoreCredit {
34            id: parse_uuid_row(&row.get::<_, String>("id")?, "store_credit", "id")?.into(),
35            customer_id: parse_uuid_row(
36                &row.get::<_, String>("customer_id")?,
37                "store_credit",
38                "customer_id",
39            )?
40            .into(),
41            original_balance: parse_decimal_row(
42                &row.get::<_, String>("original_balance")?,
43                "store_credit",
44                "original_balance",
45            )?,
46            current_balance: parse_decimal_row(
47                &row.get::<_, String>("current_balance")?,
48                "store_credit",
49                "current_balance",
50            )?,
51            currency: parse_enum_row(
52                &row.get::<_, String>("currency")?,
53                "store_credit",
54                "currency",
55            )?,
56            status: parse_enum_row(&row.get::<_, String>("status")?, "store_credit", "status")?,
57            reason: parse_enum_row(&row.get::<_, String>("reason")?, "store_credit", "reason")?,
58            reference_id: row.get("reference_id")?,
59            note: row.get("note")?,
60            expires_at: {
61                let raw: Option<String> = row.get("expires_at")?;
62                match raw {
63                    Some(ref s) if !s.is_empty() => {
64                        Some(parse_datetime_row(s, "store_credit", "expires_at")?)
65                    }
66                    _ => None,
67                }
68            },
69            created_at: parse_datetime_row(
70                &row.get::<_, String>("created_at")?,
71                "store_credit",
72                "created_at",
73            )?,
74            updated_at: parse_datetime_row(
75                &row.get::<_, String>("updated_at")?,
76                "store_credit",
77                "updated_at",
78            )?,
79        })
80    }
81
82    fn row_to_transaction(row: &rusqlite::Row<'_>) -> rusqlite::Result<StoreCreditTransaction> {
83        Ok(StoreCreditTransaction {
84            id: parse_uuid_row(&row.get::<_, String>("id")?, "store_credit_txn", "id")?.into(),
85            store_credit_id: parse_uuid_row(
86                &row.get::<_, String>("store_credit_id")?,
87                "store_credit_txn",
88                "store_credit_id",
89            )?
90            .into(),
91            amount: parse_decimal_row(
92                &row.get::<_, String>("amount")?,
93                "store_credit_txn",
94                "amount",
95            )?,
96            balance_after: parse_decimal_row(
97                &row.get::<_, String>("balance_after")?,
98                "store_credit_txn",
99                "balance_after",
100            )?,
101            transaction_type: parse_enum_row(
102                &row.get::<_, String>("transaction_type")?,
103                "store_credit_txn",
104                "transaction_type",
105            )?,
106            reference_id: row.get("reference_id")?,
107            created_at: parse_datetime_row(
108                &row.get::<_, String>("created_at")?,
109                "store_credit_txn",
110                "created_at",
111            )?,
112        })
113    }
114}
115
116impl StoreCreditRepository for SqliteStoreCreditRepository {
117    fn create(&self, input: CreateStoreCredit) -> Result<StoreCredit> {
118        // Reject non-positive issuance up front (the Postgres schema enforces this
119        // with a CHECK; SQLite has no such constraint and would otherwise mint a
120        // zero/negative-balance credit plus a bogus negative issue transaction).
121        if input.amount <= Decimal::ZERO {
122            return Err(CommerceError::ValidationError(
123                "Store credit amount must be positive".to_string(),
124            ));
125        }
126
127        let id = StoreCreditId::new();
128        let now = Utc::now();
129        let id_str = id.to_string();
130        let now_str = now.to_rfc3339();
131
132        with_immediate_transaction(&self.pool, |tx| {
133            tx.execute(
134                "INSERT INTO store_credits (id, customer_id, original_balance, current_balance, currency, status, reason, reference_id, note, expires_at, created_at, updated_at)
135                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
136                rusqlite::params![
137                    &id_str,
138                    input.customer_id.to_string(),
139                    input.amount.to_string(),
140                    input.amount.to_string(),
141                    input.currency.to_string(),
142                    "active",
143                    input.reason.to_string(),
144                    &input.reference_id,
145                    &input.note,
146                    input.expires_at.map(|dt| dt.to_rfc3339()),
147                    &now_str,
148                    &now_str,
149                ],
150            )?;
151
152            // Record the initial issue transaction
153            let txn_id = StoreCreditTransactionId::new();
154            tx.execute(
155                "INSERT INTO store_credit_transactions (id, store_credit_id, amount, balance_after, transaction_type, reference_id, created_at)
156                 VALUES (?, ?, ?, ?, ?, ?, ?)",
157                rusqlite::params![
158                    txn_id.to_string(),
159                    &id_str,
160                    input.amount.to_string(),
161                    input.amount.to_string(),
162                    "issue",
163                    &input.reference_id,
164                    &now_str,
165                ],
166            )?;
167
168            tx.query_row(
169                "SELECT * FROM store_credits WHERE id = ?",
170                [&id_str],
171                Self::row_to_store_credit,
172            )
173        })
174    }
175
176    fn get(&self, id: StoreCreditId) -> Result<Option<StoreCredit>> {
177        let conn = self.conn()?;
178        match conn.query_row(
179            "SELECT * FROM store_credits WHERE id = ?",
180            [id.to_string()],
181            Self::row_to_store_credit,
182        ) {
183            Ok(sc) => Ok(Some(sc)),
184            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
185            Err(e) => Err(map_db_error(e)),
186        }
187    }
188
189    fn list(&self, filter: StoreCreditFilter) -> Result<Vec<StoreCredit>> {
190        let conn = self.conn()?;
191        let mut sql = "SELECT * FROM store_credits WHERE 1=1".to_string();
192        let mut params: Vec<Box<dyn rusqlite::types::ToSql>> = vec![];
193
194        if let Some(customer_id) = filter.customer_id {
195            sql.push_str(" AND customer_id = ?");
196            params.push(Box::new(customer_id.to_string()));
197        }
198        if let Some(status) = filter.status {
199            sql.push_str(" AND status = ?");
200            params.push(Box::new(status.to_string()));
201        }
202        if let Some(reason) = filter.reason {
203            sql.push_str(" AND reason = ?");
204            params.push(Box::new(reason.to_string()));
205        }
206
207        sql.push_str(" ORDER BY created_at DESC");
208
209        // SQLite rejects OFFSET without LIMIT, so use `LIMIT -1` (unbounded) when
210        // only an offset is set.
211        match (filter.limit, filter.offset) {
212            (Some(limit), Some(offset)) => sql.push_str(&format!(" LIMIT {limit} OFFSET {offset}")),
213            (Some(limit), None) => sql.push_str(&format!(" LIMIT {limit}")),
214            (None, Some(offset)) => sql.push_str(&format!(" LIMIT -1 OFFSET {offset}")),
215            (None, None) => {}
216        }
217
218        let param_refs: Vec<&dyn rusqlite::types::ToSql> =
219            params.iter().map(|p| p.as_ref()).collect();
220        let mut stmt = conn.prepare(&sql).map_err(map_db_error)?;
221        let rows = stmt
222            .query_map(param_refs.as_slice(), Self::row_to_store_credit)
223            .map_err(map_db_error)?
224            .collect::<std::result::Result<Vec<_>, _>>()
225            .map_err(map_db_error)?;
226        Ok(rows)
227    }
228
229    fn adjust(&self, id: StoreCreditId, input: AdjustStoreCredit) -> Result<StoreCredit> {
230        let id_str = id.to_string();
231        let now_str = Utc::now().to_rfc3339();
232
233        with_immediate_transaction(&self.pool, |tx| {
234            let (current_balance_str, status_str): (String, String) = tx.query_row(
235                "SELECT current_balance, status FROM store_credits WHERE id = ?",
236                [&id_str],
237                |row| Ok((row.get(0)?, row.get(1)?)),
238            )?;
239
240            let status: StoreCreditStatus = parse_enum_row(&status_str, "store_credit", "status")?;
241            if matches!(status, StoreCreditStatus::Voided | StoreCreditStatus::Expired) {
242                return Err(rusqlite::Error::ToSqlConversionFailure(Box::new(
243                    CommerceError::ValidationError(
244                        "Cannot adjust a voided or expired store credit".to_string(),
245                    ),
246                )));
247            }
248
249            let current_balance =
250                parse_decimal_row(&current_balance_str, "store_credit", "current_balance")?;
251            let new_balance = current_balance + input.amount;
252
253            if new_balance < Decimal::ZERO {
254                return Err(rusqlite::Error::ToSqlConversionFailure(Box::new(
255                    CommerceError::ValidationError(
256                        "Adjustment would result in negative balance".to_string(),
257                    ),
258                )));
259            }
260
261            let status = if new_balance == Decimal::ZERO { "depleted" } else { "active" };
262
263            tx.execute(
264                "UPDATE store_credits SET current_balance = ?, status = ?, updated_at = ? WHERE id = ?",
265                rusqlite::params![new_balance.to_string(), status, &now_str, &id_str],
266            )?;
267
268            let txn_id = StoreCreditTransactionId::new();
269            tx.execute(
270                "INSERT INTO store_credit_transactions (id, store_credit_id, amount, balance_after, transaction_type, reference_id, created_at)
271                 VALUES (?, ?, ?, ?, ?, ?, ?)",
272                rusqlite::params![
273                    txn_id.to_string(),
274                    &id_str,
275                    input.amount.to_string(),
276                    new_balance.to_string(),
277                    "adjust",
278                    &input.reference_id,
279                    &now_str,
280                ],
281            )?;
282
283            tx.query_row(
284                "SELECT * FROM store_credits WHERE id = ?",
285                [&id_str],
286                Self::row_to_store_credit,
287            )
288        })
289    }
290
291    fn apply(
292        &self,
293        id: StoreCreditId,
294        amount: Decimal,
295        reference_id: Option<String>,
296    ) -> Result<StoreCreditTransaction> {
297        if amount <= Decimal::ZERO {
298            return Err(CommerceError::ValidationError(
299                "Apply amount must be positive".to_string(),
300            ));
301        }
302
303        let id_str = id.to_string();
304        let now = Utc::now();
305        let now_str = now.to_rfc3339();
306
307        with_immediate_transaction(&self.pool, |tx| {
308            let (current_balance_str, status_str, expires_at_raw): (
309                String,
310                String,
311                Option<String>,
312            ) = tx.query_row(
313                "SELECT current_balance, status, expires_at FROM store_credits WHERE id = ?",
314                [&id_str],
315                |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?)),
316            )?;
317
318            let status: StoreCreditStatus = parse_enum_row(&status_str, "store_credit", "status")?;
319            if status != StoreCreditStatus::Active {
320                return Err(rusqlite::Error::ToSqlConversionFailure(Box::new(
321                    CommerceError::ValidationError("Store credit is not active".to_string()),
322                )));
323            }
324
325            let expires_at = match expires_at_raw {
326                Some(s) if !s.is_empty() => {
327                    Some(parse_datetime_row(&s, "store_credit", "expires_at")?)
328                }
329                _ => None,
330            };
331            if expires_at.is_some_and(|exp| exp < now) {
332                return Err(rusqlite::Error::ToSqlConversionFailure(Box::new(
333                    CommerceError::ValidationError("Store credit has expired".to_string()),
334                )));
335            }
336
337            let current_balance =
338                parse_decimal_row(&current_balance_str, "store_credit", "current_balance")?;
339
340            if current_balance < amount {
341                return Err(rusqlite::Error::ToSqlConversionFailure(Box::new(
342                    CommerceError::ValidationError("Insufficient store credit balance".to_string()),
343                )));
344            }
345
346            let new_balance = current_balance - amount;
347            let status = if new_balance == Decimal::ZERO { "depleted" } else { "active" };
348
349            tx.execute(
350                "UPDATE store_credits SET current_balance = ?, status = ?, updated_at = ? WHERE id = ?",
351                rusqlite::params![new_balance.to_string(), status, &now_str, &id_str],
352            )?;
353
354            let txn_id = StoreCreditTransactionId::new();
355            let txn_id_str = txn_id.to_string();
356            let debit_amount = -amount;
357
358            tx.execute(
359                "INSERT INTO store_credit_transactions (id, store_credit_id, amount, balance_after, transaction_type, reference_id, created_at)
360                 VALUES (?, ?, ?, ?, ?, ?, ?)",
361                rusqlite::params![
362                    &txn_id_str,
363                    &id_str,
364                    debit_amount.to_string(),
365                    new_balance.to_string(),
366                    "apply",
367                    &reference_id,
368                    &now_str,
369                ],
370            )?;
371
372            tx.query_row(
373                "SELECT * FROM store_credit_transactions WHERE id = ?",
374                [&txn_id_str],
375                Self::row_to_transaction,
376            )
377        })
378    }
379
380    fn get_transactions(
381        &self,
382        store_credit_id: StoreCreditId,
383    ) -> Result<Vec<StoreCreditTransaction>> {
384        let conn = self.conn()?;
385        let mut stmt = conn
386            .prepare(
387                "SELECT * FROM store_credit_transactions WHERE store_credit_id = ? ORDER BY created_at DESC",
388            )
389            .map_err(map_db_error)?;
390        let rows = stmt
391            .query_map([store_credit_id.to_string()], Self::row_to_transaction)
392            .map_err(map_db_error)?
393            .collect::<std::result::Result<Vec<_>, _>>()
394            .map_err(map_db_error)?;
395        Ok(rows)
396    }
397}
398
399#[cfg(test)]
400mod tests {
401    use super::*;
402    use crate::DatabaseConfig;
403    use crate::sqlite::SqliteDatabase;
404    use rust_decimal_macros::dec;
405    use stateset_core::{CurrencyCode, CustomerId, StoreCreditReason, StoreCreditTransactionType};
406
407    fn test_db() -> SqliteDatabase {
408        let db = SqliteDatabase::new(&DatabaseConfig::in_memory()).expect("in-memory db");
409        let conn = db.conn().expect("conn");
410        conn.execute_batch(
411            "CREATE TABLE IF NOT EXISTS store_credits (
412                id TEXT PRIMARY KEY,
413                customer_id TEXT NOT NULL,
414                original_balance TEXT NOT NULL,
415                current_balance TEXT NOT NULL,
416                currency TEXT NOT NULL DEFAULT 'USD',
417                status TEXT NOT NULL DEFAULT 'active',
418                reason TEXT NOT NULL DEFAULT 'return',
419                reference_id TEXT,
420                note TEXT,
421                expires_at TEXT,
422                created_at TEXT NOT NULL DEFAULT (datetime('now')),
423                updated_at TEXT NOT NULL DEFAULT (datetime('now'))
424            );
425            CREATE TABLE IF NOT EXISTS store_credit_transactions (
426                id TEXT PRIMARY KEY,
427                store_credit_id TEXT NOT NULL,
428                amount TEXT NOT NULL,
429                balance_after TEXT NOT NULL,
430                transaction_type TEXT NOT NULL,
431                reference_id TEXT,
432                created_at TEXT NOT NULL DEFAULT (datetime('now')),
433                FOREIGN KEY (store_credit_id) REFERENCES store_credits(id)
434            );",
435        )
436        .expect("create tables");
437        db
438    }
439
440    fn test_repo() -> SqliteStoreCreditRepository {
441        SqliteStoreCreditRepository::new(test_db().pool().clone())
442    }
443
444    fn create_credit(repo: &SqliteStoreCreditRepository, amount: Decimal) -> StoreCredit {
445        repo.create(CreateStoreCredit {
446            customer_id: CustomerId::new(),
447            amount,
448            currency: CurrencyCode::USD,
449            reason: StoreCreditReason::Return,
450            reference_id: None,
451            note: None,
452            expires_at: None,
453        })
454        .expect("create")
455    }
456
457    #[test]
458    fn create_rejects_non_positive_amount() {
459        let repo = test_repo();
460        for bad in [dec!(-50.00), dec!(0)] {
461            let err = repo
462                .create(CreateStoreCredit {
463                    customer_id: CustomerId::new(),
464                    amount: bad,
465                    currency: CurrencyCode::USD,
466                    reason: StoreCreditReason::Return,
467                    reference_id: None,
468                    note: None,
469                    expires_at: None,
470                })
471                .expect_err("non-positive store credit amount must be rejected");
472            assert!(matches!(err, CommerceError::ValidationError(_)), "amount {bad}: got {err:?}");
473        }
474    }
475
476    #[test]
477    fn create_and_get_store_credit() {
478        let repo = test_repo();
479        let customer_id = CustomerId::new();
480        let sc = repo
481            .create(CreateStoreCredit {
482                customer_id,
483                amount: dec!(50.00),
484                currency: CurrencyCode::USD,
485                reason: StoreCreditReason::Return,
486                reference_id: Some("RET-001".into()),
487                note: None,
488                expires_at: None,
489            })
490            .expect("create");
491
492        assert_eq!(sc.original_balance, dec!(50.00));
493        assert_eq!(sc.current_balance, dec!(50.00));
494        assert_eq!(sc.customer_id, customer_id);
495
496        let fetched = repo.get(sc.id).expect("get").expect("found");
497        assert_eq!(fetched.id, sc.id);
498        assert_eq!(fetched.original_balance, dec!(50.00));
499    }
500
501    #[test]
502    fn apply_credit_and_get_transactions() {
503        let repo = test_repo();
504        let sc = repo
505            .create(CreateStoreCredit {
506                customer_id: CustomerId::new(),
507                amount: dec!(100.00),
508                currency: CurrencyCode::USD,
509                reason: StoreCreditReason::Compensation,
510                reference_id: None,
511                note: Some("Compensation credit".into()),
512                expires_at: None,
513            })
514            .expect("create");
515
516        let txn = repo.apply(sc.id, dec!(30.00), Some("ORD-123".into())).expect("apply");
517        assert_eq!(txn.amount, dec!(-30.00));
518        assert_eq!(txn.balance_after, dec!(70.00));
519        assert_eq!(txn.transaction_type, StoreCreditTransactionType::Apply);
520
521        let updated = repo.get(sc.id).expect("get").expect("found");
522        assert_eq!(updated.current_balance, dec!(70.00));
523
524        let txns = repo.get_transactions(sc.id).expect("transactions");
525        // Should have initial issue + apply = 2 transactions
526        assert_eq!(txns.len(), 2);
527    }
528
529    #[test]
530    fn concurrent_applies_cannot_overspend() {
531        use std::sync::{Arc, Barrier};
532        use std::thread;
533
534        let db = Arc::new(test_db());
535        let repo = SqliteStoreCreditRepository::new(db.pool().clone());
536        let sc = create_credit(&repo, dec!(50.00));
537
538        let thread_count = 10;
539        let barrier = Arc::new(Barrier::new(thread_count));
540        let mut handles = Vec::new();
541        for _ in 0..thread_count {
542            let db = Arc::clone(&db);
543            let barrier = Arc::clone(&barrier);
544            let credit_id = sc.id;
545            handles.push(thread::spawn(move || {
546                let repo = SqliteStoreCreditRepository::new(db.pool().clone());
547                barrier.wait();
548                repo.apply(credit_id, dec!(30.00), None)
549            }));
550        }
551
552        let results: Vec<_> = handles.into_iter().map(|h| h.join().expect("thread")).collect();
553        let successes = results.iter().filter(|r| r.is_ok()).count();
554        // The $50 credit can fund at most one $30 apply — the safety invariant is
555        // that no more than one succeeds (never overspent). Under extreme lock
556        // contention the sole winner can fail with a retryable "table is locked"
557        // error, so zero successes is acceptable; two or more would be a bug.
558        assert!(successes <= 1, "store credit overspent under concurrency: {results:?}");
559
560        let fetched = repo.get(sc.id).expect("get").expect("found");
561        assert_eq!(
562            fetched.current_balance,
563            dec!(50.00) - dec!(30.00) * Decimal::from(successes as u64),
564            "balance must reflect exactly the successful apply: {results:?}"
565        );
566    }
567
568    #[test]
569    fn apply_rejects_nonpositive_amount() {
570        let repo = test_repo();
571        let sc = create_credit(&repo, dec!(50.00));
572
573        // A negative apply must not mint balance.
574        assert!(repo.apply(sc.id, Decimal::ZERO, None).is_err());
575        assert!(repo.apply(sc.id, dec!(-10.00), None).is_err());
576
577        let fetched = repo.get(sc.id).expect("get").expect("found");
578        assert_eq!(fetched.current_balance, dec!(50.00));
579    }
580
581    #[test]
582    fn apply_rejects_date_expired_credit() {
583        let repo = test_repo();
584        let sc = repo
585            .create(CreateStoreCredit {
586                customer_id: CustomerId::new(),
587                amount: dec!(50.00),
588                currency: CurrencyCode::USD,
589                reason: StoreCreditReason::Return,
590                reference_id: None,
591                note: None,
592                expires_at: Some(Utc::now() - chrono::Duration::days(1)),
593            })
594            .expect("create");
595
596        // Status is still 'active' — only the expiry date has passed.
597        assert!(repo.apply(sc.id, dec!(10.00), None).is_err());
598
599        let fetched = repo.get(sc.id).expect("get").expect("found");
600        assert_eq!(fetched.current_balance, dec!(50.00));
601    }
602
603    #[test]
604    fn apply_rejects_voided_credit() {
605        let db = test_db();
606        let repo = SqliteStoreCreditRepository::new(db.pool().clone());
607        let sc = create_credit(&repo, dec!(50.00));
608
609        db.conn()
610            .expect("conn")
611            .execute("UPDATE store_credits SET status = 'voided' WHERE id = ?", [sc.id.to_string()])
612            .expect("void");
613
614        assert!(repo.apply(sc.id, dec!(10.00), None).is_err());
615
616        let fetched = repo.get(sc.id).expect("get").expect("found");
617        assert_eq!(fetched.current_balance, dec!(50.00));
618    }
619
620    #[test]
621    fn adjust_rejects_voided_credit() {
622        let db = test_db();
623        let repo = SqliteStoreCreditRepository::new(db.pool().clone());
624        let sc = create_credit(&repo, dec!(50.00));
625
626        db.conn()
627            .expect("conn")
628            .execute("UPDATE store_credits SET status = 'voided' WHERE id = ?", [sc.id.to_string()])
629            .expect("void");
630
631        // Adjusting a voided credit must not silently resurrect it.
632        assert!(
633            repo.adjust(
634                sc.id,
635                AdjustStoreCredit { amount: dec!(10.00), note: None, reference_id: None }
636            )
637            .is_err()
638        );
639
640        let fetched = repo.get(sc.id).expect("get").expect("found");
641        assert_eq!(fetched.status.to_string(), "voided");
642        assert_eq!(fetched.current_balance, dec!(50.00));
643    }
644}
stateset_db/sqlite/store_credits.rs

stateset_db/sqlite/
store_credits.rs
