stateset_db/sqlite/
payment_obligations.rs

1//! SQLite implementation of the payment obligation repository
2
3use super::{
4    map_db_error, parse_date_row, parse_datetime_row, parse_decimal_row, parse_enum_row,
5    parse_json_row, parse_uuid_opt_row, parse_uuid_row, with_immediate_transaction,
6};
7use chrono::{NaiveDate, Utc};
8use r2d2::Pool;
9use r2d2_sqlite::SqliteConnectionManager;
10use rust_decimal::Decimal;
11use stateset_core::{
12    CommerceError, CreatePaymentObligation, CurrencyCode, PaymentObligation,
13    PaymentObligationDashboard, PaymentObligationFilter, PaymentObligationId,
14    PaymentObligationRepository, PaymentObligationStatus, Result,
15};
16
17#[derive(Debug)]
18pub struct SqlitePaymentObligationRepository {
19    pool: Pool<SqliteConnectionManager>,
20}
21
22impl SqlitePaymentObligationRepository {
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_obligation(row: &rusqlite::Row<'_>) -> rusqlite::Result<PaymentObligation> {
33        let linked_json: String = row.get("linked_bill_ids")?;
34        Ok(PaymentObligation {
35            id: parse_uuid_row(&row.get::<_, String>("id")?, "payment_obligation", "id")?.into(),
36            number: row.get("number")?,
37            supplier_id: parse_uuid_row(
38                &row.get::<_, String>("supplier_id")?,
39                "payment_obligation",
40                "supplier_id",
41            )?,
42            purchase_order_id: parse_uuid_opt_row(
43                row.get::<_, Option<String>>("purchase_order_id")?,
44                "payment_obligation",
45                "purchase_order_id",
46            )?,
47            amount: parse_decimal_row(
48                &row.get::<_, String>("amount")?,
49                "payment_obligation",
50                "amount",
51            )?,
52            amount_paid: parse_decimal_row(
53                &row.get::<_, String>("amount_paid")?,
54                "payment_obligation",
55                "amount_paid",
56            )?,
57            currency: parse_enum_row::<CurrencyCode>(
58                &row.get::<_, String>("currency")?,
59                "payment_obligation",
60                "currency",
61            )?,
62            due_date: parse_date_row(
63                &row.get::<_, String>("due_date")?,
64                "payment_obligation",
65                "due_date",
66            )?,
67            status: parse_enum_row::<PaymentObligationStatus>(
68                &row.get::<_, String>("status")?,
69                "payment_obligation",
70                "status",
71            )?,
72            linked_bill_ids: parse_json_row(&linked_json, "payment_obligation", "linked_bill_ids")?,
73            notes: row.get("notes")?,
74            created_at: parse_datetime_row(
75                &row.get::<_, String>("created_at")?,
76                "payment_obligation",
77                "created_at",
78            )?,
79            updated_at: parse_datetime_row(
80                &row.get::<_, String>("updated_at")?,
81                "payment_obligation",
82                "updated_at",
83            )?,
84        })
85    }
86
87    fn fetch(tx: &rusqlite::Connection, id: &str) -> rusqlite::Result<PaymentObligation> {
88        tx.query_row(
89            "SELECT * FROM payment_obligations WHERE id = ?",
90            [id],
91            Self::row_to_obligation,
92        )
93    }
94
95    fn json_err(e: serde_json::Error) -> rusqlite::Error {
96        rusqlite::Error::ToSqlConversionFailure(Box::new(CommerceError::DatabaseError(
97            e.to_string(),
98        )))
99    }
100}
101
102impl PaymentObligationRepository for SqlitePaymentObligationRepository {
103    fn create(&self, input: CreatePaymentObligation) -> Result<PaymentObligation> {
104        if input.amount <= Decimal::ZERO {
105            return Err(CommerceError::ValidationError(
106                "payment obligation amount must be positive".into(),
107            ));
108        }
109        let id = PaymentObligationId::new();
110        let id_str = id.to_string();
111        let now_str = Utc::now().to_rfc3339();
112        let number = format!("OBL-{}", &id_str[..8]);
113        let currency = input.currency.unwrap_or(CurrencyCode::USD);
114        with_immediate_transaction(&self.pool, |tx| {
115            tx.execute(
116                "INSERT INTO payment_obligations (id, number, supplier_id, purchase_order_id, amount, amount_paid, currency, due_date, status, linked_bill_ids, notes, created_at, updated_at)
117                 VALUES (?, ?, ?, ?, ?, '0', ?, ?, 'pending', '[]', ?, ?, ?)",
118                rusqlite::params![
119                    &id_str,
120                    &number,
121                    input.supplier_id.to_string(),
122                    input.purchase_order_id.map(|p| p.to_string()),
123                    input.amount.to_string(),
124                    currency.to_string(),
125                    input.due_date.to_string(),
126                    &input.notes,
127                    &now_str,
128                    &now_str,
129                ],
130            )?;
131            Self::fetch(tx, &id_str)
132        })
133    }
134
135    fn get(&self, id: PaymentObligationId) -> Result<Option<PaymentObligation>> {
136        let conn = self.conn()?;
137        match Self::fetch(&conn, &id.to_string()) {
138            Ok(o) => Ok(Some(o)),
139            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
140            Err(e) => Err(map_db_error(e)),
141        }
142    }
143
144    fn list(&self, filter: PaymentObligationFilter) -> Result<Vec<PaymentObligation>> {
145        let conn = self.conn()?;
146        let mut sql = "SELECT * FROM payment_obligations WHERE 1=1".to_string();
147        let mut params: Vec<Box<dyn rusqlite::types::ToSql>> = vec![];
148        if let Some(supplier) = filter.supplier_id {
149            sql.push_str(" AND supplier_id = ?");
150            params.push(Box::new(supplier.to_string()));
151        }
152        if let Some(status) = filter.status {
153            sql.push_str(" AND status = ?");
154            params.push(Box::new(status.to_string()));
155        }
156        if let Some(due) = filter.due_before {
157            sql.push_str(" AND due_date <= ?");
158            params.push(Box::new(due.to_string()));
159        }
160        sql.push_str(" ORDER BY due_date ASC");
161        crate::sqlite::append_limit_offset(&mut sql, filter.limit, filter.offset);
162        let param_refs: Vec<&dyn rusqlite::types::ToSql> =
163            params.iter().map(|p| p.as_ref()).collect();
164        let mut stmt = conn.prepare(&sql).map_err(map_db_error)?;
165        let rows = stmt
166            .query_map(param_refs.as_slice(), Self::row_to_obligation)
167            .map_err(map_db_error)?
168            .collect::<std::result::Result<Vec<_>, _>>()
169            .map_err(map_db_error)?;
170        Ok(rows)
171    }
172
173    fn record_payment(
174        &self,
175        id: PaymentObligationId,
176        amount: Decimal,
177    ) -> Result<PaymentObligation> {
178        if amount <= Decimal::ZERO {
179            return Err(CommerceError::ValidationError("payment amount must be positive".into()));
180        }
181        let id_str = id.to_string();
182        let now = Utc::now().to_rfc3339();
183        with_immediate_transaction(&self.pool, |tx| {
184            let mut obligation = Self::fetch(tx, &id_str)?;
185            if obligation.status == PaymentObligationStatus::Cancelled {
186                return Err(rusqlite::Error::ToSqlConversionFailure(Box::new(
187                    CommerceError::Conflict("cannot pay a cancelled obligation".into()),
188                )));
189            }
190            if amount > obligation.outstanding() {
191                return Err(rusqlite::Error::ToSqlConversionFailure(Box::new(
192                    CommerceError::ValidationError(format!(
193                        "payment {amount} exceeds outstanding balance {}",
194                        obligation.outstanding()
195                    )),
196                )));
197            }
198            obligation.amount_paid += amount;
199            let new_status = obligation.derive_status();
200            tx.execute(
201                "UPDATE payment_obligations SET amount_paid = ?, status = ?, updated_at = ? WHERE id = ?",
202                rusqlite::params![
203                    obligation.amount_paid.to_string(),
204                    new_status.to_string(),
205                    &now,
206                    &id_str
207                ],
208            )?;
209            Self::fetch(tx, &id_str)
210        })
211    }
212
213    fn set_status(
214        &self,
215        id: PaymentObligationId,
216        status: PaymentObligationStatus,
217    ) -> Result<PaymentObligation> {
218        let id_str = id.to_string();
219        let now = Utc::now().to_rfc3339();
220        with_immediate_transaction(&self.pool, |tx| {
221            tx.execute(
222                "UPDATE payment_obligations SET status = ?, updated_at = ? WHERE id = ?",
223                rusqlite::params![status.to_string(), &now, &id_str],
224            )?;
225            Self::fetch(tx, &id_str)
226        })
227    }
228
229    fn link_bill(&self, id: PaymentObligationId, bill_id: uuid::Uuid) -> Result<PaymentObligation> {
230        let id_str = id.to_string();
231        let now = Utc::now().to_rfc3339();
232        with_immediate_transaction(&self.pool, |tx| {
233            let current: String = tx.query_row(
234                "SELECT linked_bill_ids FROM payment_obligations WHERE id = ?",
235                [&id_str],
236                |r| r.get(0),
237            )?;
238            let mut ids: Vec<uuid::Uuid> =
239                serde_json::from_str(&current).map_err(Self::json_err)?;
240            if !ids.contains(&bill_id) {
241                ids.push(bill_id);
242            }
243            let json = serde_json::to_string(&ids).map_err(Self::json_err)?;
244            tx.execute(
245                "UPDATE payment_obligations SET linked_bill_ids = ?, updated_at = ? WHERE id = ?",
246                rusqlite::params![&json, &now, &id_str],
247            )?;
248            Self::fetch(tx, &id_str)
249        })
250    }
251
252    fn dashboard(&self, today: NaiveDate) -> Result<PaymentObligationDashboard> {
253        // Open (non-terminal) obligations only.
254        let open = self.list(PaymentObligationFilter {
255            status: Some(PaymentObligationStatus::Pending),
256            ..Default::default()
257        })?;
258        let scheduled = self.list(PaymentObligationFilter {
259            status: Some(PaymentObligationStatus::Scheduled),
260            ..Default::default()
261        })?;
262        let partial = self.list(PaymentObligationFilter {
263            status: Some(PaymentObligationStatus::PartiallyPaid),
264            ..Default::default()
265        })?;
266
267        let mut dash = PaymentObligationDashboard::default();
268        for o in open.iter().chain(scheduled.iter()).chain(partial.iter()) {
269            dash.open_count += 1;
270            dash.total_outstanding += o.outstanding();
271            if o.is_overdue(today) {
272                dash.overdue_count += 1;
273                dash.overdue_amount += o.outstanding();
274            }
275        }
276        Ok(dash)
277    }
278}
279
280#[cfg(test)]
281mod tests {
282    use super::*;
283    use crate::DatabaseConfig;
284    use crate::sqlite::SqliteDatabase;
285    use rust_decimal_macros::dec;
286    use uuid::Uuid;
287
288    fn day(y: i32, m: u32, d: u32) -> NaiveDate {
289        NaiveDate::from_ymd_opt(y, m, d).unwrap()
290    }
291
292    fn test_repo() -> SqlitePaymentObligationRepository {
293        let db = SqliteDatabase::new(&DatabaseConfig::in_memory()).expect("in-memory db");
294        SqlitePaymentObligationRepository::new(db.pool().clone())
295    }
296
297    fn new_obl(
298        repo: &SqlitePaymentObligationRepository,
299        amount: Decimal,
300        due: NaiveDate,
301    ) -> PaymentObligation {
302        repo.create(CreatePaymentObligation {
303            supplier_id: Uuid::new_v4(),
304            purchase_order_id: None,
305            amount,
306            currency: Some(CurrencyCode::USD),
307            due_date: due,
308            notes: None,
309        })
310        .expect("create obligation")
311    }
312
313    #[test]
314    fn record_payment_progresses_status() {
315        let repo = test_repo();
316        let o = new_obl(&repo, dec!(100), day(2026, 7, 1));
317        let after = repo.record_payment(o.id, dec!(40)).expect("pay");
318        assert_eq!(after.status, PaymentObligationStatus::PartiallyPaid);
319        assert_eq!(after.outstanding(), dec!(60));
320        let after = repo.record_payment(o.id, dec!(60)).expect("pay rest");
321        assert_eq!(after.status, PaymentObligationStatus::Paid);
322        assert_eq!(after.outstanding(), dec!(0));
323    }
324
325    #[test]
326    fn rejects_overpayment() {
327        let repo = test_repo();
328        let o = new_obl(&repo, dec!(100), day(2026, 7, 1));
329        // Cannot pay more than the full amount in one shot.
330        assert!(repo.record_payment(o.id, dec!(150)).is_err());
331        // Partial payment, then an overpayment of the remainder is rejected.
332        repo.record_payment(o.id, dec!(60)).expect("partial");
333        assert!(repo.record_payment(o.id, dec!(50)).is_err());
334        // Exact remaining balance still succeeds.
335        let done = repo.record_payment(o.id, dec!(40)).expect("pay rest");
336        assert_eq!(done.status, PaymentObligationStatus::Paid);
337        assert_eq!(done.outstanding(), dec!(0));
338    }
339
340    #[test]
341    fn link_bill_idempotent() {
342        let repo = test_repo();
343        let o = new_obl(&repo, dec!(100), day(2026, 7, 1));
344        let bill = Uuid::new_v4();
345        let after = repo.link_bill(o.id, bill).expect("link");
346        assert_eq!(after.linked_bill_ids, vec![bill]);
347        let again = repo.link_bill(o.id, bill).expect("link again");
348        assert_eq!(again.linked_bill_ids.len(), 1);
349    }
350
351    #[test]
352    fn dashboard_counts_outstanding_and_overdue() {
353        let repo = test_repo();
354        let today = day(2026, 6, 15);
355        new_obl(&repo, dec!(100), day(2026, 6, 1)); // overdue
356        new_obl(&repo, dec!(50), day(2026, 7, 1)); // future
357        let paid = new_obl(&repo, dec!(200), day(2026, 6, 1));
358        repo.record_payment(paid.id, dec!(200)).expect("pay"); // paid → excluded
359
360        let dash = repo.dashboard(today).expect("dashboard");
361        assert_eq!(dash.open_count, 2);
362        assert_eq!(dash.total_outstanding, dec!(150));
363        assert_eq!(dash.overdue_count, 1);
364        assert_eq!(dash.overdue_amount, dec!(100));
365    }
366
367    #[test]
368    fn cancel_blocks_payment() {
369        let repo = test_repo();
370        let o = new_obl(&repo, dec!(100), day(2026, 7, 1));
371        repo.set_status(o.id, PaymentObligationStatus::Cancelled).expect("cancel");
372        assert!(repo.record_payment(o.id, dec!(10)).is_err());
373    }
374}
stateset_db/sqlite/payment_obligations.rs

stateset_db/sqlite/
payment_obligations.rs
