stateset_db/sqlite/
stock_snapshots.rs

1//! SQLite implementation of the stock snapshot repository
2
3use super::{
4    map_db_error, parse_datetime_row, parse_decimal_row, parse_uuid_row, with_immediate_transaction,
5};
6use chrono::Utc;
7use r2d2::Pool;
8use r2d2_sqlite::SqliteConnectionManager;
9use stateset_core::{
10    CaptureStockSnapshot, CommerceError, Result, StockSnapshot, StockSnapshotFilter,
11    StockSnapshotId, StockSnapshotLine, StockSnapshotLineId, StockSnapshotRepository,
12};
13
14#[derive(Debug)]
15pub struct SqliteStockSnapshotRepository {
16    pool: Pool<SqliteConnectionManager>,
17}
18
19impl SqliteStockSnapshotRepository {
20    #[must_use]
21    pub const fn new(pool: Pool<SqliteConnectionManager>) -> Self {
22        Self { pool }
23    }
24
25    fn conn(&self) -> Result<r2d2::PooledConnection<SqliteConnectionManager>> {
26        self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))
27    }
28
29    fn row_to_line(row: &rusqlite::Row<'_>) -> rusqlite::Result<StockSnapshotLine> {
30        Ok(StockSnapshotLine {
31            id: parse_uuid_row(&row.get::<_, String>("id")?, "stock_snapshot_line", "id")?.into(),
32            stock_snapshot_id: parse_uuid_row(
33                &row.get::<_, String>("stock_snapshot_id")?,
34                "stock_snapshot_line",
35                "stock_snapshot_id",
36            )?
37            .into(),
38            product_id: parse_uuid_row(
39                &row.get::<_, String>("product_id")?,
40                "stock_snapshot_line",
41                "product_id",
42            )?
43            .into(),
44            sku: row.get("sku")?,
45            quantity_on_hand: parse_decimal_row(
46                &row.get::<_, String>("quantity_on_hand")?,
47                "stock_snapshot_line",
48                "quantity_on_hand",
49            )?,
50            quantity_available: parse_decimal_row(
51                &row.get::<_, String>("quantity_available")?,
52                "stock_snapshot_line",
53                "quantity_available",
54            )?,
55            location: row.get("location")?,
56        })
57    }
58
59    fn row_to_head(row: &rusqlite::Row<'_>) -> rusqlite::Result<StockSnapshot> {
60        Ok(StockSnapshot {
61            id: parse_uuid_row(&row.get::<_, String>("id")?, "stock_snapshot", "id")?.into(),
62            label: row.get("label")?,
63            total_skus: row.get::<_, i64>("total_skus")? as u64,
64            total_units: parse_decimal_row(
65                &row.get::<_, String>("total_units")?,
66                "stock_snapshot",
67                "total_units",
68            )?,
69            lines: Vec::new(),
70            captured_at: parse_datetime_row(
71                &row.get::<_, String>("captured_at")?,
72                "stock_snapshot",
73                "captured_at",
74            )?,
75        })
76    }
77
78    fn load_lines(
79        conn: &rusqlite::Connection,
80        id: &str,
81    ) -> rusqlite::Result<Vec<StockSnapshotLine>> {
82        let mut stmt = conn.prepare(
83            "SELECT * FROM stock_snapshot_lines WHERE stock_snapshot_id = ? ORDER BY sku",
84        )?;
85        stmt.query_map([id], Self::row_to_line)?.collect()
86    }
87
88    fn load_full(conn: &rusqlite::Connection, id: &str) -> rusqlite::Result<StockSnapshot> {
89        let mut head =
90            conn.query_row("SELECT * FROM stock_snapshots WHERE id = ?", [id], Self::row_to_head)?;
91        head.lines = Self::load_lines(conn, id)?;
92        Ok(head)
93    }
94}
95
96impl StockSnapshotRepository for SqliteStockSnapshotRepository {
97    fn capture(&self, input: CaptureStockSnapshot) -> Result<StockSnapshot> {
98        if input.lines.is_empty() {
99            return Err(CommerceError::ValidationError(
100                "a stock snapshot requires at least one line".into(),
101            ));
102        }
103        let id = StockSnapshotId::new();
104        let id_str = id.to_string();
105        let now = Utc::now().to_rfc3339();
106        let total_skus = input.total_skus();
107        let total_units = input.total_units();
108        with_immediate_transaction(&self.pool, |tx| {
109            tx.execute(
110                "INSERT INTO stock_snapshots (id, label, total_skus, total_units, captured_at)
111                 VALUES (?, ?, ?, ?, ?)",
112                rusqlite::params![
113                    &id_str,
114                    &input.label,
115                    total_skus as i64,
116                    total_units.to_string(),
117                    &now,
118                ],
119            )?;
120            for line in &input.lines {
121                tx.execute(
122                    "INSERT INTO stock_snapshot_lines (id, stock_snapshot_id, product_id, sku, quantity_on_hand, quantity_available, location)
123                     VALUES (?, ?, ?, ?, ?, ?, ?)",
124                    rusqlite::params![
125                        StockSnapshotLineId::new().to_string(),
126                        &id_str,
127                        line.product_id.to_string(),
128                        &line.sku,
129                        line.quantity_on_hand.to_string(),
130                        line.quantity_available.to_string(),
131                        &line.location,
132                    ],
133                )?;
134            }
135            Self::load_full(tx, &id_str)
136        })
137    }
138
139    fn get(&self, id: StockSnapshotId) -> Result<Option<StockSnapshot>> {
140        let conn = self.conn()?;
141        match Self::load_full(&conn, &id.to_string()) {
142            Ok(s) => Ok(Some(s)),
143            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
144            Err(e) => Err(map_db_error(e)),
145        }
146    }
147
148    fn latest(&self) -> Result<Option<StockSnapshot>> {
149        let conn = self.conn()?;
150        let id: Option<String> = conn
151            .query_row(
152                "SELECT id FROM stock_snapshots ORDER BY captured_at DESC LIMIT 1",
153                [],
154                |r| r.get(0),
155            )
156            .ok();
157        match id {
158            Some(id) => Self::load_full(&conn, &id).map(Some).map_err(map_db_error),
159            None => Ok(None),
160        }
161    }
162
163    fn list(&self, filter: StockSnapshotFilter) -> Result<Vec<StockSnapshot>> {
164        let conn = self.conn()?;
165        let mut sql = "SELECT * FROM stock_snapshots ORDER BY captured_at DESC".to_string();
166        crate::sqlite::append_limit_offset(&mut sql, filter.limit, filter.offset);
167        let mut stmt = conn.prepare(&sql).map_err(map_db_error)?;
168        // Header-level listing; lines omitted for brevity (fetch via get()).
169        let rows = stmt
170            .query_map([], Self::row_to_head)
171            .map_err(map_db_error)?
172            .collect::<std::result::Result<Vec<_>, _>>()
173            .map_err(map_db_error)?;
174        Ok(rows)
175    }
176
177    fn delete(&self, id: StockSnapshotId) -> Result<()> {
178        let id_str = id.to_string();
179        with_immediate_transaction(&self.pool, |tx| {
180            tx.execute("DELETE FROM stock_snapshot_lines WHERE stock_snapshot_id = ?", [&id_str])?;
181            tx.execute("DELETE FROM stock_snapshots WHERE id = ?", [&id_str])?;
182            Ok(())
183        })
184    }
185}
186
187#[cfg(test)]
188mod tests {
189    use super::*;
190    use crate::DatabaseConfig;
191    use crate::sqlite::SqliteDatabase;
192    use rust_decimal_macros::dec;
193    use stateset_core::{CaptureStockLine, ProductId};
194
195    fn test_repo() -> SqliteStockSnapshotRepository {
196        let db = SqliteDatabase::new(&DatabaseConfig::in_memory()).expect("in-memory db");
197        SqliteStockSnapshotRepository::new(db.pool().clone())
198    }
199
200    fn capture(repo: &SqliteStockSnapshotRepository) -> StockSnapshot {
201        repo.capture(CaptureStockSnapshot {
202            label: Some("EOM".into()),
203            lines: vec![
204                CaptureStockLine {
205                    product_id: ProductId::new(),
206                    sku: "SKU-1".into(),
207                    quantity_on_hand: dec!(10),
208                    quantity_available: dec!(8),
209                    location: Some("MAIN".into()),
210                },
211                CaptureStockLine {
212                    product_id: ProductId::new(),
213                    sku: "SKU-2".into(),
214                    quantity_on_hand: dec!(5),
215                    quantity_available: dec!(5),
216                    location: None,
217                },
218            ],
219        })
220        .expect("capture")
221    }
222
223    #[test]
224    fn capture_computes_totals_and_lines() {
225        let repo = test_repo();
226        let s = capture(&repo);
227        assert_eq!(s.total_skus, 2);
228        assert_eq!(s.total_units, dec!(15));
229        assert_eq!(s.lines.len(), 2);
230        assert_eq!(s.total_available(), dec!(13));
231    }
232
233    #[test]
234    fn capture_rejects_empty() {
235        let repo = test_repo();
236        assert!(repo.capture(CaptureStockSnapshot { label: None, lines: vec![] }).is_err());
237    }
238
239    #[test]
240    fn latest_returns_most_recent() {
241        let repo = test_repo();
242        capture(&repo);
243        let second = capture(&repo);
244        let latest = repo.latest().expect("latest").expect("found");
245        assert_eq!(latest.id, second.id);
246        assert_eq!(latest.lines.len(), 2);
247    }
248
249    #[test]
250    fn get_and_delete_cascades() {
251        let repo = test_repo();
252        let s = capture(&repo);
253        assert!(repo.get(s.id).expect("get").is_some());
254        repo.delete(s.id).expect("delete");
255        assert!(repo.get(s.id).expect("get").is_none());
256    }
257
258    #[test]
259    fn list_header_level() {
260        let repo = test_repo();
261        capture(&repo);
262        capture(&repo);
263        let all = repo.list(StockSnapshotFilter::default()).expect("list");
264        assert_eq!(all.len(), 2);
265    }
266}
stateset_db/sqlite/stock_snapshots.rs

stateset_db/sqlite/
stock_snapshots.rs
