stateset_db/sqlite/
shipping_zones.rs

1//! SQLite implementation of shipping zone repository
2
3use super::{
4    map_db_error, parse_datetime_row, parse_json_row, parse_uuid_row, with_immediate_transaction,
5};
6use chrono::Utc;
7use r2d2::Pool;
8use r2d2_sqlite::SqliteConnectionManager;
9use stateset_core::{
10    CommerceError, CreateShippingZone, Result, ShippingZone, ShippingZoneFilter, ShippingZoneId,
11    ShippingZoneRepository, UpdateShippingZone,
12};
13
14#[derive(Debug)]
15pub struct SqliteShippingZoneRepository {
16    pool: Pool<SqliteConnectionManager>,
17}
18
19impl SqliteShippingZoneRepository {
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_zone(row: &rusqlite::Row<'_>) -> rusqlite::Result<ShippingZone> {
30        let countries_json: String = row.get("countries")?;
31        let regions_json: String = row.get("regions")?;
32        let postal_codes_json: String = row.get("postal_codes")?;
33
34        Ok(ShippingZone {
35            id: parse_uuid_row(&row.get::<_, String>("id")?, "shipping_zone", "id")?.into(),
36            name: row.get("name")?,
37            countries: parse_json_row(&countries_json, "shipping_zone", "countries")?,
38            regions: parse_json_row(&regions_json, "shipping_zone", "regions")?,
39            postal_codes: parse_json_row(&postal_codes_json, "shipping_zone", "postal_codes")?,
40            priority: row.get("priority")?,
41            is_active: row.get::<_, i32>("is_active")? != 0,
42            created_at: parse_datetime_row(
43                &row.get::<_, String>("created_at")?,
44                "shipping_zone",
45                "created_at",
46            )?,
47            updated_at: parse_datetime_row(
48                &row.get::<_, String>("updated_at")?,
49                "shipping_zone",
50                "updated_at",
51            )?,
52        })
53    }
54}
55
56impl ShippingZoneRepository for SqliteShippingZoneRepository {
57    fn create(&self, input: CreateShippingZone) -> Result<ShippingZone> {
58        let id = ShippingZoneId::new();
59        let now = Utc::now();
60        let id_str = id.to_string();
61        let now_str = now.to_rfc3339();
62
63        let countries_json = serde_json::to_string(&input.countries)
64            .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
65        let regions_json = serde_json::to_string(&input.regions)
66            .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
67        let postal_codes_json = serde_json::to_string(&input.postal_codes)
68            .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
69
70        with_immediate_transaction(&self.pool, |tx| {
71            tx.execute(
72                "INSERT INTO shipping_zones (id, name, countries, regions, postal_codes, priority, is_active, created_at, updated_at)
73                 VALUES (?, ?, ?, ?, ?, ?, 1, ?, ?)",
74                rusqlite::params![
75                    &id_str,
76                    &input.name,
77                    &countries_json,
78                    &regions_json,
79                    &postal_codes_json,
80                    input.priority.unwrap_or(0),
81                    &now_str,
82                    &now_str,
83                ],
84            )?;
85
86            tx.query_row("SELECT * FROM shipping_zones WHERE id = ?", [&id_str], Self::row_to_zone)
87        })
88    }
89
90    fn get(&self, id: ShippingZoneId) -> Result<Option<ShippingZone>> {
91        let conn = self.conn()?;
92        match conn.query_row(
93            "SELECT * FROM shipping_zones WHERE id = ?",
94            [id.to_string()],
95            Self::row_to_zone,
96        ) {
97            Ok(z) => Ok(Some(z)),
98            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
99            Err(e) => Err(map_db_error(e)),
100        }
101    }
102
103    fn update(&self, id: ShippingZoneId, input: UpdateShippingZone) -> Result<ShippingZone> {
104        let id_str = id.to_string();
105        let now_str = Utc::now().to_rfc3339();
106
107        with_immediate_transaction(&self.pool, |tx| {
108            let mut sets = vec!["updated_at = ?".to_string()];
109            let mut params: Vec<Box<dyn rusqlite::types::ToSql>> = vec![Box::new(now_str.clone())];
110
111            if let Some(ref name) = input.name {
112                sets.push("name = ?".into());
113                params.push(Box::new(name.clone()));
114            }
115            if let Some(ref countries) = input.countries {
116                let json = serde_json::to_string(countries).map_err(|e| {
117                    rusqlite::Error::ToSqlConversionFailure(Box::new(CommerceError::DatabaseError(
118                        e.to_string(),
119                    )))
120                })?;
121                sets.push("countries = ?".into());
122                params.push(Box::new(json));
123            }
124            if let Some(ref regions) = input.regions {
125                let json = serde_json::to_string(regions).map_err(|e| {
126                    rusqlite::Error::ToSqlConversionFailure(Box::new(CommerceError::DatabaseError(
127                        e.to_string(),
128                    )))
129                })?;
130                sets.push("regions = ?".into());
131                params.push(Box::new(json));
132            }
133            if let Some(ref postal_codes) = input.postal_codes {
134                let json = serde_json::to_string(postal_codes).map_err(|e| {
135                    rusqlite::Error::ToSqlConversionFailure(Box::new(CommerceError::DatabaseError(
136                        e.to_string(),
137                    )))
138                })?;
139                sets.push("postal_codes = ?".into());
140                params.push(Box::new(json));
141            }
142            if let Some(priority) = input.priority {
143                sets.push("priority = ?".into());
144                params.push(Box::new(priority));
145            }
146            if let Some(is_active) = input.is_active {
147                sets.push("is_active = ?".into());
148                params.push(Box::new(is_active as i32));
149            }
150
151            let sql = format!("UPDATE shipping_zones SET {} WHERE id = ?", sets.join(", "));
152            params.push(Box::new(id_str.clone()));
153
154            let param_refs: Vec<&dyn rusqlite::types::ToSql> =
155                params.iter().map(|p| p.as_ref()).collect();
156            tx.execute(&sql, param_refs.as_slice())?;
157
158            tx.query_row("SELECT * FROM shipping_zones WHERE id = ?", [&id_str], Self::row_to_zone)
159        })
160    }
161
162    fn list(&self, filter: ShippingZoneFilter) -> Result<Vec<ShippingZone>> {
163        let conn = self.conn()?;
164        let mut sql = "SELECT * FROM shipping_zones WHERE 1=1".to_string();
165        let mut params: Vec<Box<dyn rusqlite::types::ToSql>> = vec![];
166
167        if let Some(ref country) = filter.country {
168            // JSON array contains the country code
169            sql.push_str(" AND countries LIKE ?");
170            params.push(Box::new(format!("%\"{country}\"%")));
171        }
172        if let Some(is_active) = filter.is_active {
173            sql.push_str(" AND is_active = ?");
174            params.push(Box::new(is_active as i32));
175        }
176
177        sql.push_str(" ORDER BY priority ASC, created_at DESC");
178
179        crate::sqlite::append_limit_offset(&mut sql, filter.limit, filter.offset);
180
181        let param_refs: Vec<&dyn rusqlite::types::ToSql> =
182            params.iter().map(|p| p.as_ref()).collect();
183        let mut stmt = conn.prepare(&sql).map_err(map_db_error)?;
184        let rows = stmt
185            .query_map(param_refs.as_slice(), Self::row_to_zone)
186            .map_err(map_db_error)?
187            .collect::<std::result::Result<Vec<_>, _>>()
188            .map_err(map_db_error)?;
189        Ok(rows)
190    }
191
192    fn delete(&self, id: ShippingZoneId) -> Result<()> {
193        let conn = self.conn()?;
194        conn.execute("DELETE FROM shipping_zones WHERE id = ?", [id.to_string()])
195            .map_err(map_db_error)?;
196        Ok(())
197    }
198
199    fn find_matching_zones(
200        &self,
201        country: &str,
202        region: Option<&str>,
203        postal_code: Option<&str>,
204    ) -> Result<Vec<ShippingZone>> {
205        // Fetch all active zones and filter in Rust for correct JSON array matching
206        let all_active =
207            self.list(ShippingZoneFilter { is_active: Some(true), ..Default::default() })?;
208
209        let matched: Vec<ShippingZone> = all_active
210            .into_iter()
211            .filter(|zone| {
212                // Must match country (or zone has no country restriction)
213                let country_match =
214                    zone.countries.is_empty() || zone.countries.iter().any(|c| c == country);
215                if !country_match {
216                    return false;
217                }
218
219                // If zone specifies regions, region must match
220                if !zone.regions.is_empty() {
221                    if let Some(r) = region {
222                        if !zone.regions.iter().any(|zr| zr == r) {
223                            return false;
224                        }
225                    } else {
226                        return false;
227                    }
228                }
229
230                // If zone specifies postal codes, postal code must match (simple prefix match)
231                if !zone.postal_codes.is_empty() {
232                    if let Some(pc) = postal_code {
233                        if !zone.postal_codes.iter().any(|pattern| {
234                            if pattern.ends_with('*') {
235                                pc.starts_with(&pattern[..pattern.len() - 1])
236                            } else {
237                                pc == pattern
238                            }
239                        }) {
240                            return false;
241                        }
242                    } else {
243                        return false;
244                    }
245                }
246
247                true
248            })
249            .collect();
250
251        Ok(matched)
252    }
253}
254
255#[cfg(test)]
256mod tests {
257    use super::*;
258    use crate::DatabaseConfig;
259    use crate::sqlite::SqliteDatabase;
260
261    fn test_repo() -> SqliteShippingZoneRepository {
262        let db = SqliteDatabase::new(&DatabaseConfig::in_memory()).expect("in-memory db");
263        let conn = db.conn().expect("conn");
264        conn.execute_batch(
265            "CREATE TABLE IF NOT EXISTS shipping_zones (
266                id TEXT PRIMARY KEY,
267                name TEXT NOT NULL,
268                countries TEXT NOT NULL DEFAULT '[]',
269                regions TEXT NOT NULL DEFAULT '[]',
270                postal_codes TEXT NOT NULL DEFAULT '[]',
271                priority INTEGER NOT NULL DEFAULT 0,
272                is_active INTEGER NOT NULL DEFAULT 1,
273                created_at TEXT NOT NULL DEFAULT (datetime('now')),
274                updated_at TEXT NOT NULL DEFAULT (datetime('now'))
275            );",
276        )
277        .expect("create tables");
278        SqliteShippingZoneRepository::new(db.pool().clone())
279    }
280
281    #[test]
282    fn create_and_get_zone() {
283        let repo = test_repo();
284        let zone = repo
285            .create(CreateShippingZone {
286                name: "Domestic US".into(),
287                countries: vec!["US".into()],
288                regions: vec![],
289                postal_codes: vec![],
290                priority: Some(1),
291            })
292            .expect("create");
293
294        assert_eq!(zone.name, "Domestic US");
295        assert_eq!(zone.countries, vec!["US".to_string()]);
296        assert!(zone.is_active);
297
298        let fetched = repo.get(zone.id).expect("get").expect("found");
299        assert_eq!(fetched.id, zone.id);
300    }
301
302    #[test]
303    fn list_and_delete_zones() {
304        let repo = test_repo();
305        repo.create(CreateShippingZone {
306            name: "US".into(),
307            countries: vec!["US".into()],
308            regions: vec![],
309            postal_codes: vec![],
310            priority: None,
311        })
312        .expect("create US");
313
314        repo.create(CreateShippingZone {
315            name: "EU".into(),
316            countries: vec!["DE".into(), "FR".into()],
317            regions: vec![],
318            postal_codes: vec![],
319            priority: None,
320        })
321        .expect("create EU");
322
323        let all = repo.list(ShippingZoneFilter::default()).expect("list");
324        assert_eq!(all.len(), 2);
325
326        repo.delete(all[0].id).expect("delete");
327        let remaining = repo.list(ShippingZoneFilter::default()).expect("list after delete");
328        assert_eq!(remaining.len(), 1);
329    }
330}
stateset_db/sqlite/shipping_zones.rs

stateset_db/sqlite/
shipping_zones.rs
