stateset_db/sqlite/
wishlists.rs

1//! SQLite implementation of wishlist repository
2
3use super::{map_db_error, parse_datetime_row, parse_uuid_row, with_immediate_transaction};
4use chrono::Utc;
5use r2d2::Pool;
6use r2d2_sqlite::SqliteConnectionManager;
7use stateset_core::{
8    AddWishlistItem, CommerceError, CreateWishlist, ProductId, Result, UpdateWishlist, Wishlist,
9    WishlistFilter, WishlistId, WishlistItem, WishlistRepository,
10};
11
12#[derive(Debug)]
13pub struct SqliteWishlistRepository {
14    pool: Pool<SqliteConnectionManager>,
15}
16
17impl SqliteWishlistRepository {
18    #[must_use]
19    pub const fn new(pool: Pool<SqliteConnectionManager>) -> Self {
20        Self { pool }
21    }
22
23    fn conn(&self) -> Result<r2d2::PooledConnection<SqliteConnectionManager>> {
24        self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))
25    }
26
27    fn row_to_wishlist(row: &rusqlite::Row<'_>) -> rusqlite::Result<Wishlist> {
28        Ok(Wishlist {
29            id: parse_uuid_row(&row.get::<_, String>("id")?, "wishlist", "id")?.into(),
30            customer_id: parse_uuid_row(
31                &row.get::<_, String>("customer_id")?,
32                "wishlist",
33                "customer_id",
34            )?
35            .into(),
36            name: row.get("name")?,
37            is_public: row.get::<_, i32>("is_public")? != 0,
38            items: Vec::new(),
39            created_at: parse_datetime_row(
40                &row.get::<_, String>("created_at")?,
41                "wishlist",
42                "created_at",
43            )?,
44            updated_at: parse_datetime_row(
45                &row.get::<_, String>("updated_at")?,
46                "wishlist",
47                "updated_at",
48            )?,
49        })
50    }
51
52    fn row_to_item(row: &rusqlite::Row<'_>) -> rusqlite::Result<WishlistItem> {
53        Ok(WishlistItem {
54            product_id: parse_uuid_row(
55                &row.get::<_, String>("product_id")?,
56                "wishlist_item",
57                "product_id",
58            )?
59            .into(),
60            variant_id: row.get("variant_id")?,
61            added_at: parse_datetime_row(
62                &row.get::<_, String>("added_at")?,
63                "wishlist_item",
64                "added_at",
65            )?,
66            note: row.get("notes")?,
67            quantity: row.get::<_, i64>("quantity")? as u32,
68            priority: row.get("priority")?,
69        })
70    }
71
72    fn load_items(
73        conn: &rusqlite::Connection,
74        wishlist_id: &str,
75    ) -> rusqlite::Result<Vec<WishlistItem>> {
76        let mut stmt =
77            conn.prepare("SELECT * FROM wishlist_items WHERE wishlist_id = ? ORDER BY added_at")?;
78        let items = stmt
79            .query_map([wishlist_id], Self::row_to_item)?
80            .collect::<std::result::Result<Vec<_>, _>>()?;
81        Ok(items)
82    }
83
84    fn load_items_batch(
85        conn: &rusqlite::Connection,
86        ids: &[String],
87    ) -> rusqlite::Result<std::collections::HashMap<String, Vec<WishlistItem>>> {
88        let mut map: std::collections::HashMap<String, Vec<WishlistItem>> =
89            std::collections::HashMap::with_capacity(ids.len());
90        for chunk in ids.chunks(500) {
91            let placeholders = super::build_in_clause(chunk.len());
92            let sql = format!(
93                "SELECT * FROM wishlist_items WHERE wishlist_id IN ({placeholders}) ORDER BY added_at"
94            );
95            let mut stmt = conn.prepare(&sql)?;
96            let param_refs: Vec<&dyn rusqlite::types::ToSql> =
97                chunk.iter().map(|s| s as &dyn rusqlite::types::ToSql).collect();
98            let rows = stmt.query_map(param_refs.as_slice(), |row| {
99                let parent: String = row.get("wishlist_id")?;
100                Ok((parent, Self::row_to_item(row)?))
101            })?;
102            for row in rows {
103                let (parent, item) = row?;
104                map.entry(parent).or_default().push(item);
105            }
106        }
107        Ok(map)
108    }
109}
110
111impl WishlistRepository for SqliteWishlistRepository {
112    fn create(&self, input: CreateWishlist) -> Result<Wishlist> {
113        let id = WishlistId::new();
114        let now = Utc::now();
115        let id_str = id.to_string();
116        let now_str = now.to_rfc3339();
117
118        with_immediate_transaction(&self.pool, |tx| {
119            tx.execute(
120                "INSERT INTO wishlists (id, customer_id, name, is_public, created_at, updated_at)
121                 VALUES (?, ?, ?, ?, ?, ?)",
122                rusqlite::params![
123                    &id_str,
124                    input.customer_id.to_string(),
125                    &input.name,
126                    input.is_public as i32,
127                    &now_str,
128                    &now_str,
129                ],
130            )?;
131
132            tx.query_row("SELECT * FROM wishlists WHERE id = ?", [&id_str], Self::row_to_wishlist)
133        })
134    }
135
136    fn get(&self, id: WishlistId) -> Result<Option<Wishlist>> {
137        let conn = self.conn()?;
138        let id_str = id.to_string();
139        match conn.query_row(
140            "SELECT * FROM wishlists WHERE id = ?",
141            [&id_str],
142            Self::row_to_wishlist,
143        ) {
144            Ok(mut wishlist) => {
145                wishlist.items = Self::load_items(&conn, &id_str).map_err(map_db_error)?;
146                Ok(Some(wishlist))
147            }
148            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
149            Err(e) => Err(map_db_error(e)),
150        }
151    }
152
153    fn update(&self, id: WishlistId, input: UpdateWishlist) -> Result<Wishlist> {
154        let id_str = id.to_string();
155        let now_str = Utc::now().to_rfc3339();
156
157        with_immediate_transaction(&self.pool, |tx| {
158            let mut sets = vec!["updated_at = ?".to_string()];
159            let mut params: Vec<Box<dyn rusqlite::types::ToSql>> = vec![Box::new(now_str.clone())];
160
161            if let Some(ref name) = input.name {
162                sets.push("name = ?".into());
163                params.push(Box::new(name.clone()));
164            }
165            if let Some(is_public) = input.is_public {
166                sets.push("is_public = ?".into());
167                params.push(Box::new(is_public as i32));
168            }
169
170            let sql = format!("UPDATE wishlists SET {} WHERE id = ?", sets.join(", "));
171            params.push(Box::new(id_str.clone()));
172
173            let param_refs: Vec<&dyn rusqlite::types::ToSql> =
174                params.iter().map(|p| p.as_ref()).collect();
175            tx.execute(&sql, param_refs.as_slice())?;
176
177            let mut wishlist = tx.query_row(
178                "SELECT * FROM wishlists WHERE id = ?",
179                [&id_str],
180                Self::row_to_wishlist,
181            )?;
182            wishlist.items = Self::load_items(tx, &id_str)?;
183            Ok(wishlist)
184        })
185    }
186
187    fn list(&self, filter: WishlistFilter) -> Result<Vec<Wishlist>> {
188        let conn = self.conn()?;
189        let mut sql = "SELECT * FROM wishlists WHERE 1=1".to_string();
190        let mut params: Vec<Box<dyn rusqlite::types::ToSql>> = vec![];
191
192        if let Some(customer_id) = filter.customer_id {
193            sql.push_str(" AND customer_id = ?");
194            params.push(Box::new(customer_id.to_string()));
195        }
196        if let Some(is_public) = filter.is_public {
197            sql.push_str(" AND is_public = ?");
198            params.push(Box::new(is_public as i32));
199        }
200
201        sql.push_str(" ORDER BY created_at DESC");
202
203        crate::sqlite::append_limit_offset(&mut sql, filter.limit, filter.offset);
204
205        let param_refs: Vec<&dyn rusqlite::types::ToSql> =
206            params.iter().map(|p| p.as_ref()).collect();
207        let mut stmt = conn.prepare(&sql).map_err(map_db_error)?;
208        let wishlists = stmt
209            .query_map(param_refs.as_slice(), Self::row_to_wishlist)
210            .map_err(map_db_error)?
211            .collect::<std::result::Result<Vec<_>, _>>()
212            .map_err(map_db_error)?;
213
214        // Load items for all wishlists in one batched query
215        let ids: Vec<String> = wishlists.iter().map(|w| w.id.to_string()).collect();
216        let mut items_by_id = Self::load_items_batch(&conn, &ids).map_err(map_db_error)?;
217        let mut result = Vec::with_capacity(wishlists.len());
218        for mut wl in wishlists {
219            wl.items = items_by_id.remove(&wl.id.to_string()).unwrap_or_default();
220            result.push(wl);
221        }
222        Ok(result)
223    }
224
225    fn delete(&self, id: WishlistId) -> Result<()> {
226        let conn = self.conn()?;
227        let id_str = id.to_string();
228        // Delete items first (foreign-key-like cleanup)
229        conn.execute("DELETE FROM wishlist_items WHERE wishlist_id = ?", [&id_str])
230            .map_err(map_db_error)?;
231        conn.execute("DELETE FROM wishlists WHERE id = ?", [&id_str]).map_err(map_db_error)?;
232        Ok(())
233    }
234
235    fn add_item(&self, wishlist_id: WishlistId, item: AddWishlistItem) -> Result<WishlistItem> {
236        let wl_id_str = wishlist_id.to_string();
237        let item_id = uuid::Uuid::new_v4().to_string();
238        let now_str = Utc::now().to_rfc3339();
239
240        with_immediate_transaction(&self.pool, |tx| {
241            // Verify wishlist exists
242            tx.query_row("SELECT id FROM wishlists WHERE id = ?", [&wl_id_str], |row| {
243                row.get::<_, String>(0)
244            })
245            .map_err(|e| match e {
246                rusqlite::Error::QueryReturnedNoRows => rusqlite::Error::QueryReturnedNoRows,
247                other => other,
248            })?;
249
250            let product_id_str = item.product_id.to_string();
251            let quantity = i64::from(item.quantity.unwrap_or(1));
252
253            tx.execute(
254                "INSERT INTO wishlist_items (id, wishlist_id, product_id, variant_id, priority, quantity, added_at, notes)
255                 VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
256                rusqlite::params![
257                    &item_id,
258                    &wl_id_str,
259                    &product_id_str,
260                    &item.variant_id,
261                    &item.priority,
262                    quantity,
263                    &now_str,
264                    &item.note,
265                ],
266            )?;
267
268            // Update wishlist updated_at
269            tx.execute(
270                "UPDATE wishlists SET updated_at = ? WHERE id = ?",
271                rusqlite::params![&now_str, &wl_id_str],
272            )?;
273
274            tx.query_row("SELECT * FROM wishlist_items WHERE id = ?", [&item_id], Self::row_to_item)
275        })
276    }
277
278    fn remove_item(&self, wishlist_id: WishlistId, product_id: ProductId) -> Result<()> {
279        let conn = self.conn()?;
280        let wl_id_str = wishlist_id.to_string();
281        let product_id_str = product_id.to_string();
282        let now_str = Utc::now().to_rfc3339();
283
284        conn.execute(
285            "DELETE FROM wishlist_items WHERE wishlist_id = ? AND product_id = ?",
286            rusqlite::params![&wl_id_str, &product_id_str],
287        )
288        .map_err(map_db_error)?;
289
290        // Update wishlist updated_at
291        conn.execute(
292            "UPDATE wishlists SET updated_at = ? WHERE id = ?",
293            rusqlite::params![&now_str, &wl_id_str],
294        )
295        .map_err(map_db_error)?;
296
297        Ok(())
298    }
299}
300
301#[cfg(test)]
302mod tests {
303    use super::*;
304    use crate::DatabaseConfig;
305    use crate::sqlite::SqliteDatabase;
306    use stateset_core::CustomerId;
307
308    fn test_repo() -> SqliteWishlistRepository {
309        let db = SqliteDatabase::new(&DatabaseConfig::in_memory()).expect("in-memory db");
310        let conn = db.conn().expect("conn");
311        conn.execute_batch(
312            "CREATE TABLE IF NOT EXISTS wishlists (
313                id TEXT PRIMARY KEY,
314                customer_id TEXT NOT NULL,
315                name TEXT NOT NULL DEFAULT 'My Wishlist',
316                is_public INTEGER NOT NULL DEFAULT 0,
317                created_at TEXT,
318                updated_at TEXT
319            );
320            CREATE TABLE IF NOT EXISTS wishlist_items (
321                id TEXT PRIMARY KEY,
322                wishlist_id TEXT NOT NULL,
323                product_id TEXT NOT NULL,
324                added_at TEXT NOT NULL DEFAULT (datetime('now')),
325                notes TEXT,
326                UNIQUE(wishlist_id, product_id)
327            );",
328        )
329        .expect("create tables");
330        SqliteWishlistRepository::new(db.pool().clone())
331    }
332
333    #[test]
334    fn create_and_get() {
335        let repo = test_repo();
336        let customer_id = CustomerId::new();
337        let wishlist = repo
338            .create(CreateWishlist { customer_id, name: "Birthday Ideas".into(), is_public: true })
339            .expect("create");
340
341        assert_eq!(wishlist.name, "Birthday Ideas");
342        assert!(wishlist.is_public);
343        assert!(wishlist.items.is_empty());
344
345        let fetched = repo.get(wishlist.id).expect("get").expect("Some");
346        assert_eq!(fetched.id, wishlist.id);
347        assert_eq!(fetched.customer_id, customer_id);
348        assert_eq!(fetched.name, "Birthday Ideas");
349        assert!(fetched.is_public);
350    }
351
352    #[test]
353    fn add_item() {
354        let repo = test_repo();
355        let wishlist = repo
356            .create(CreateWishlist {
357                customer_id: CustomerId::new(),
358                name: "Gadgets".into(),
359                is_public: false,
360            })
361            .expect("create");
362
363        let product_id = ProductId::new();
364        let item = repo
365            .add_item(
366                wishlist.id,
367                AddWishlistItem {
368                    product_id,
369                    variant_id: Some("VAR-1".into()),
370                    note: Some("Love this one".into()),
371                    quantity: Some(3),
372                    priority: Some(2),
373                },
374            )
375            .expect("add_item");
376
377        assert_eq!(item.product_id, product_id);
378        assert_eq!(item.note.as_deref(), Some("Love this one"));
379        assert_eq!(item.variant_id.as_deref(), Some("VAR-1"));
380        assert_eq!(item.quantity, 3);
381        assert_eq!(item.priority, Some(2));
382
383        // Re-read from the database: variant_id, quantity, and priority must
384        // survive the round-trip (previously they were dropped and quantity
385        // always read back as 1).
386        let fetched = repo.get(wishlist.id).expect("get").expect("Some");
387        assert_eq!(fetched.items.len(), 1);
388        let stored = &fetched.items[0];
389        assert_eq!(stored.product_id, product_id);
390        assert_eq!(stored.variant_id.as_deref(), Some("VAR-1"));
391        assert_eq!(stored.quantity, 3, "quantity must survive persistence");
392        assert_eq!(stored.priority, Some(2));
393    }
394
395    #[test]
396    fn delete() {
397        let repo = test_repo();
398        let wishlist = repo
399            .create(CreateWishlist {
400                customer_id: CustomerId::new(),
401                name: "Temp".into(),
402                is_public: false,
403            })
404            .expect("create");
405
406        // Add an item so we also verify cascade-like cleanup
407        repo.add_item(
408            wishlist.id,
409            AddWishlistItem {
410                product_id: ProductId::new(),
411                variant_id: None,
412                note: None,
413                quantity: None,
414                priority: None,
415            },
416        )
417        .expect("add_item");
418
419        repo.delete(wishlist.id).expect("delete");
420        assert!(repo.get(wishlist.id).expect("get after delete").is_none());
421    }
422}
stateset_db/sqlite/wishlists.rs

stateset_db/sqlite/
wishlists.rs
