stateset_db/sqlite/
bom.rs

1//! SQLite BOM (Bill of Materials) repository implementation
2
3use super::parse_helpers::{
4    parse_datetime, parse_datetime_row, parse_decimal_row, parse_enum_row, parse_uuid,
5    parse_uuid_opt_row, parse_uuid_row,
6};
7use super::{build_in_clause, params_refs, uuid_params};
8use chrono::Utc;
9use r2d2::Pool;
10use r2d2_sqlite::SqliteConnectionManager;
11use stateset_core::{
12    BatchResult, BillOfMaterials, BomComponent, BomFilter, BomRepository, BomStatus, CommerceError,
13    CreateBom, CreateBomComponent, ProductId, Result, UpdateBom, validate_batch_size,
14};
15use uuid::Uuid;
16
17/// SQLite implementation of `BomRepository`
18#[derive(Debug)]
19pub struct SqliteBomRepository {
20    pool: Pool<SqliteConnectionManager>,
21}
22
23impl SqliteBomRepository {
24    #[must_use]
25    pub const fn new(pool: Pool<SqliteConnectionManager>) -> Self {
26        Self { pool }
27    }
28
29    fn conn(&self) -> Result<r2d2::PooledConnection<SqliteConnectionManager>> {
30        self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))
31    }
32
33    fn parse_bom_status(s: &str) -> Result<BomStatus> {
34        s.parse()
35            .map_err(|e| CommerceError::DatabaseError(format!("Invalid bom.status '{s}': {e}")))
36    }
37
38    fn row_to_component(row: &rusqlite::Row<'_>) -> rusqlite::Result<BomComponent> {
39        Ok(BomComponent {
40            id: parse_uuid_row(&row.get::<_, String>("id")?, "bom_component", "id")?,
41            bom_id: parse_uuid_row(&row.get::<_, String>("bom_id")?, "bom_component", "bom_id")?,
42            component_product_id: parse_uuid_opt_row(
43                row.get::<_, Option<String>>("component_product_id")?,
44                "bom_component",
45                "component_product_id",
46            )?
47            .map(ProductId::from),
48            component_sku: row.get("component_sku")?,
49            name: row.get("name")?,
50            quantity: parse_decimal_row(
51                &row.get::<_, String>("quantity")?,
52                "bom_component",
53                "quantity",
54            )?,
55            unit_of_measure: row.get("unit_of_measure")?,
56            position: row.get("position")?,
57            notes: row.get("notes")?,
58            created_at: parse_datetime_row(
59                &row.get::<_, String>("created_at")?,
60                "bom_component",
61                "created_at",
62            )?,
63            updated_at: parse_datetime_row(
64                &row.get::<_, String>("updated_at")?,
65                "bom_component",
66                "updated_at",
67            )?,
68        })
69    }
70
71    /// Load components for a batch of BOM ids in chunked `IN`-clause queries,
72    /// grouped by parent BOM id. Uses the caller's connection.
73    fn load_components_batch(
74        conn: &rusqlite::Connection,
75        ids: &[Uuid],
76    ) -> Result<std::collections::HashMap<Uuid, Vec<BomComponent>>> {
77        let mut map: std::collections::HashMap<Uuid, Vec<BomComponent>> =
78            std::collections::HashMap::with_capacity(ids.len());
79        let id_strings: Vec<String> = ids.iter().map(Uuid::to_string).collect();
80        for chunk in id_strings.chunks(500) {
81            let placeholders = build_in_clause(chunk.len());
82            let sql = format!(
83                "SELECT id, bom_id, component_product_id, component_sku, name, quantity,
84                        unit_of_measure, position, notes, created_at, updated_at
85                 FROM manufacturing_bom_components WHERE bom_id IN ({placeholders})
86                 ORDER BY position, created_at"
87            );
88            let mut stmt =
89                conn.prepare(&sql).map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
90            let param_refs: Vec<&dyn rusqlite::ToSql> =
91                chunk.iter().map(|s| s as &dyn rusqlite::ToSql).collect();
92            let rows = stmt
93                .query_map(param_refs.as_slice(), |row| {
94                    let comp = Self::row_to_component(row)?;
95                    Ok((comp.bom_id, comp))
96                })
97                .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
98            for row in rows {
99                let (parent, comp) =
100                    row.map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
101                map.entry(parent).or_default().push(comp);
102            }
103        }
104        Ok(map)
105    }
106
107    fn load_components(&self, bom_id: Uuid) -> Result<Vec<BomComponent>> {
108        let conn = self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
109
110        let mut stmt = conn
111            .prepare(
112                "SELECT id, bom_id, component_product_id, component_sku, name, quantity,
113                        unit_of_measure, position, notes, created_at, updated_at
114                 FROM manufacturing_bom_components WHERE bom_id = ?
115                 ORDER BY position, created_at",
116            )
117            .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
118
119        let rows = stmt
120            .query_map([bom_id.to_string()], |row| {
121                Ok(BomComponent {
122                    id: parse_uuid_row(&row.get::<_, String>(0)?, "bom_component", "id")?,
123                    bom_id: parse_uuid_row(&row.get::<_, String>(1)?, "bom_component", "bom_id")?,
124                    component_product_id: parse_uuid_opt_row(
125                        row.get::<_, Option<String>>(2)?,
126                        "bom_component",
127                        "component_product_id",
128                    )?
129                    .map(ProductId::from),
130                    component_sku: row.get(3)?,
131                    name: row.get(4)?,
132                    quantity: parse_decimal_row(
133                        &row.get::<_, String>(5)?,
134                        "bom_component",
135                        "quantity",
136                    )?,
137                    unit_of_measure: row.get(6)?,
138                    position: row.get(7)?,
139                    notes: row.get(8)?,
140                    created_at: parse_datetime_row(
141                        &row.get::<_, String>(9)?,
142                        "bom_component",
143                        "created_at",
144                    )?,
145                    updated_at: parse_datetime_row(
146                        &row.get::<_, String>(10)?,
147                        "bom_component",
148                        "updated_at",
149                    )?,
150                })
151            })
152            .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
153
154        let mut components = Vec::new();
155        for row in rows {
156            components.push(row.map_err(|e| CommerceError::DatabaseError(e.to_string()))?);
157        }
158
159        Ok(components)
160    }
161}
162
163impl BomRepository for SqliteBomRepository {
164    fn create(&self, input: CreateBom) -> Result<BillOfMaterials> {
165        let id = Uuid::new_v4();
166        let bom_number = BillOfMaterials::generate_bom_number();
167        let now = Utc::now();
168        let revision = input.revision.clone().unwrap_or_else(|| "A".to_string());
169
170        // Insert BOM in a scoped block to release the connection before adding components
171        {
172            let conn = self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
173            conn.execute(
174                "INSERT INTO manufacturing_boms (id, bom_number, product_id, name, description, revision, status, created_by, created_at, updated_at)
175                 VALUES (?, ?, ?, ?, ?, ?, 'draft', ?, ?, ?)",
176                rusqlite::params![
177                    id.to_string(),
178                    bom_number,
179                    input.product_id.to_string(),
180                    input.name,
181                    input.description,
182                    revision,
183                    input.created_by.map(|u| u.to_string()),
184                    now.to_rfc3339(),
185                    now.to_rfc3339(),
186                ],
187            )
188            .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
189        } // Connection released here
190
191        // Create components if provided (after releasing the main connection)
192        let mut components = Vec::new();
193        if let Some(comp_inputs) = input.components {
194            for comp_input in comp_inputs {
195                let comp = self.add_component(id, comp_input)?;
196                components.push(comp);
197            }
198        }
199
200        Ok(BillOfMaterials {
201            id,
202            bom_number,
203            product_id: input.product_id,
204            name: input.name,
205            description: input.description,
206            revision,
207            status: BomStatus::Draft,
208            components,
209            created_by: input.created_by,
210            updated_by: None,
211            created_at: now,
212            updated_at: now,
213        })
214    }
215
216    fn get(&self, id: Uuid) -> Result<Option<BillOfMaterials>> {
217        // Query BOM in a scoped block to release connection before loading components
218        let bom_data = {
219            let conn = self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
220
221            let result = conn.query_row(
222                "SELECT id, bom_number, product_id, name, description, revision, status,
223                        created_by, updated_by, created_at, updated_at
224                 FROM manufacturing_boms WHERE id = ?",
225                [id.to_string()],
226                |row| {
227                    Ok((
228                        row.get::<_, String>(0)?,
229                        row.get::<_, String>(1)?,
230                        row.get::<_, String>(2)?,
231                        row.get::<_, String>(3)?,
232                        row.get::<_, Option<String>>(4)?,
233                        row.get::<_, String>(5)?,
234                        row.get::<_, String>(6)?,
235                        row.get::<_, Option<String>>(7)?,
236                        row.get::<_, Option<String>>(8)?,
237                        row.get::<_, String>(9)?,
238                        row.get::<_, String>(10)?,
239                    ))
240                },
241            );
242
243            match result {
244                Ok(data) => Some(data),
245                Err(rusqlite::Error::QueryReturnedNoRows) => None,
246                Err(e) => return Err(CommerceError::DatabaseError(e.to_string())),
247            }
248        }; // Connection released here
249
250        match bom_data {
251            Some((
252                id_str,
253                bom_number,
254                product_id,
255                name,
256                description,
257                revision,
258                status,
259                created_by,
260                updated_by,
261                created_at,
262                updated_at,
263            )) => {
264                let bom_id = parse_uuid(&id_str, "bom", "id")?;
265                let components = self.load_components(bom_id)?;
266
267                Ok(Some(BillOfMaterials {
268                    id: bom_id,
269                    bom_number,
270                    product_id: ProductId::from(parse_uuid(&product_id, "bom", "product_id")?),
271                    name,
272                    description,
273                    revision,
274                    status: Self::parse_bom_status(&status)?,
275                    components,
276                    created_by: match created_by {
277                        Some(ref s) if !s.is_empty() => Some(parse_uuid(s, "bom", "created_by")?),
278                        _ => None,
279                    },
280                    updated_by: match updated_by {
281                        Some(ref s) if !s.is_empty() => Some(parse_uuid(s, "bom", "updated_by")?),
282                        _ => None,
283                    },
284                    created_at: parse_datetime(&created_at, "bom", "created_at")?,
285                    updated_at: parse_datetime(&updated_at, "bom", "updated_at")?,
286                }))
287            }
288            None => Ok(None),
289        }
290    }
291
292    fn get_by_number(&self, bom_number: &str) -> Result<Option<BillOfMaterials>> {
293        // Query ID in a scoped block to release connection before calling self.get()
294        let id_result = {
295            let conn = self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
296
297            let result = conn.query_row(
298                "SELECT id FROM manufacturing_boms WHERE bom_number = ?",
299                [bom_number],
300                |row| row.get::<_, String>(0),
301            );
302
303            match result {
304                Ok(id_str) => Some(parse_uuid(&id_str, "bom", "id")?),
305                Err(rusqlite::Error::QueryReturnedNoRows) => None,
306                Err(e) => return Err(CommerceError::DatabaseError(e.to_string())),
307            }
308        }; // Connection released here
309
310        match id_result {
311            Some(id) => self.get(id),
312            None => Ok(None),
313        }
314    }
315
316    fn update(&self, id: Uuid, input: UpdateBom) -> Result<BillOfMaterials> {
317        // Get existing BOM first (releases connection after)
318        let existing = self.get(id)?.ok_or(CommerceError::NotFound)?;
319        let now = Utc::now();
320
321        let new_name = input.name.unwrap_or(existing.name);
322        let new_description = input.description.or(existing.description);
323        let new_revision = input.revision.unwrap_or(existing.revision);
324        let new_status = input.status.unwrap_or(existing.status);
325
326        // Do the update in a scoped block
327        {
328            let conn = self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
329            conn.execute(
330                "UPDATE manufacturing_boms SET name = ?, description = ?, revision = ?, status = ?, updated_by = ?, updated_at = ? WHERE id = ?",
331                rusqlite::params![
332                    new_name,
333                    new_description,
334                    new_revision,
335                    new_status.to_string(),
336                    input.updated_by.map(|u| u.to_string()),
337                    now.to_rfc3339(),
338                    id.to_string(),
339                ],
340            )
341            .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
342        } // Connection released here
343
344        // Fetch and return the updated BOM
345        self.get(id)?.ok_or(CommerceError::NotFound)
346    }
347
348    fn list(&self, filter: BomFilter) -> Result<Vec<BillOfMaterials>> {
349        let conn = self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
350
351        let mut boms = {
352            let limit = i64::from(filter.limit.unwrap_or(100));
353            let offset = i64::from(filter.offset.unwrap_or(0));
354
355            let mut sql = "SELECT id, bom_number, product_id, name, description, revision, status,
356                        created_by, updated_by, created_at, updated_at
357                 FROM manufacturing_boms WHERE 1=1"
358                .to_string();
359            let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
360
361            if let Some(product_id) = filter.product_id {
362                sql.push_str(" AND product_id = ?");
363                params.push(Box::new(product_id.to_string()));
364            }
365
366            if let Some(status) = filter.status {
367                sql.push_str(" AND status = ?");
368                params.push(Box::new(status.to_string()));
369            }
370
371            if let Some(search) = filter.search {
372                sql.push_str(" AND (name LIKE ? OR bom_number LIKE ?)");
373                let search_pattern = format!("%{search}%");
374                params.push(Box::new(search_pattern.clone()));
375                params.push(Box::new(search_pattern));
376            }
377
378            sql.push_str(" ORDER BY created_at DESC LIMIT ? OFFSET ?");
379            params.push(Box::new(limit));
380            params.push(Box::new(offset));
381
382            let mut stmt =
383                conn.prepare(&sql).map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
384
385            let param_refs: Vec<&dyn rusqlite::ToSql> =
386                params.iter().map(std::convert::AsRef::as_ref).collect();
387
388            let rows = stmt
389                .query_map(param_refs.as_slice(), |row| {
390                    let created_by: Option<String> = row.get("created_by")?;
391                    let updated_by: Option<String> = row.get("updated_by")?;
392                    Ok(BillOfMaterials {
393                        id: parse_uuid_row(&row.get::<_, String>("id")?, "bom", "id")?,
394                        bom_number: row.get("bom_number")?,
395                        product_id: ProductId::from(parse_uuid_row(
396                            &row.get::<_, String>("product_id")?,
397                            "bom",
398                            "product_id",
399                        )?),
400                        name: row.get("name")?,
401                        description: row.get("description")?,
402                        revision: row.get("revision")?,
403                        status: parse_enum_row(&row.get::<_, String>("status")?, "bom", "status")?,
404                        components: Vec::new(),
405                        created_by: match created_by {
406                            Some(ref s) if !s.is_empty() => {
407                                Some(parse_uuid_row(s, "bom", "created_by")?)
408                            }
409                            _ => None,
410                        },
411                        updated_by: match updated_by {
412                            Some(ref s) if !s.is_empty() => {
413                                Some(parse_uuid_row(s, "bom", "updated_by")?)
414                            }
415                            _ => None,
416                        },
417                        created_at: parse_datetime_row(
418                            &row.get::<_, String>("created_at")?,
419                            "bom",
420                            "created_at",
421                        )?,
422                        updated_at: parse_datetime_row(
423                            &row.get::<_, String>("updated_at")?,
424                            "bom",
425                            "updated_at",
426                        )?,
427                    })
428                })
429                .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
430
431            let mut list = Vec::new();
432            for row in rows {
433                list.push(row.map_err(|e| CommerceError::DatabaseError(e.to_string()))?);
434            }
435            list
436        };
437
438        // Batch-load components for all listed BOMs on the same connection.
439        let ids: Vec<Uuid> = boms.iter().map(|b| b.id).collect();
440        let mut components_by_id = Self::load_components_batch(&conn, &ids)?;
441        for bom in &mut boms {
442            bom.components = components_by_id.remove(&bom.id).unwrap_or_default();
443        }
444
445        Ok(boms)
446    }
447
448    fn delete(&self, id: Uuid) -> Result<()> {
449        let conn = self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
450
451        // Mark as obsolete instead of hard delete
452        conn.execute(
453            "UPDATE manufacturing_boms SET status = 'obsolete', updated_at = ? WHERE id = ?",
454            rusqlite::params![Utc::now().to_rfc3339(), id.to_string()],
455        )
456        .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
457
458        Ok(())
459    }
460
461    fn add_component(&self, bom_id: Uuid, component: CreateBomComponent) -> Result<BomComponent> {
462        let conn = self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
463
464        let id = Uuid::new_v4();
465        let now = Utc::now();
466        let uom = component.unit_of_measure.unwrap_or_else(|| "each".to_string());
467
468        conn.execute(
469            "INSERT INTO manufacturing_bom_components (id, bom_id, component_product_id, component_sku, name, quantity, unit_of_measure, position, notes, created_at, updated_at)
470             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
471            rusqlite::params![
472                id.to_string(),
473                bom_id.to_string(),
474                component.component_product_id.map(|u| u.to_string()),
475                component.component_sku,
476                component.name,
477                component.quantity.to_string(),
478                uom,
479                component.position,
480                component.notes,
481                now.to_rfc3339(),
482                now.to_rfc3339(),
483            ],
484        )
485        .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
486
487        Ok(BomComponent {
488            id,
489            bom_id,
490            component_product_id: component.component_product_id,
491            component_sku: component.component_sku,
492            name: component.name,
493            quantity: component.quantity,
494            unit_of_measure: uom,
495            position: component.position,
496            notes: component.notes,
497            created_at: now,
498            updated_at: now,
499        })
500    }
501
502    fn update_component(
503        &self,
504        component_id: Uuid,
505        component: CreateBomComponent,
506    ) -> Result<BomComponent> {
507        let conn = self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
508
509        let now = Utc::now();
510        let uom = component.unit_of_measure.unwrap_or_else(|| "each".to_string());
511
512        conn.execute(
513            "UPDATE manufacturing_bom_components SET component_product_id = ?, component_sku = ?, name = ?, quantity = ?, unit_of_measure = ?, position = ?, notes = ?, updated_at = ? WHERE id = ?",
514            rusqlite::params![
515                component.component_product_id.map(|u| u.to_string()),
516                component.component_sku,
517                component.name,
518                component.quantity.to_string(),
519                uom,
520                component.position,
521                component.notes,
522                now.to_rfc3339(),
523                component_id.to_string(),
524            ],
525        )
526        .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
527
528        // Fetch the updated component
529        let row = conn
530            .query_row(
531                "SELECT bom_id, created_at FROM manufacturing_bom_components WHERE id = ?",
532                [component_id.to_string()],
533                |row| Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?)),
534            )
535            .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
536
537        Ok(BomComponent {
538            id: component_id,
539            bom_id: parse_uuid(&row.0, "bom_component", "bom_id")?,
540            component_product_id: component.component_product_id,
541            component_sku: component.component_sku,
542            name: component.name,
543            quantity: component.quantity,
544            unit_of_measure: uom,
545            position: component.position,
546            notes: component.notes,
547            created_at: parse_datetime(&row.1, "bom_component", "created_at")?,
548            updated_at: now,
549        })
550    }
551
552    fn remove_component(&self, component_id: Uuid) -> Result<()> {
553        let conn = self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
554
555        conn.execute(
556            "DELETE FROM manufacturing_bom_components WHERE id = ?",
557            [component_id.to_string()],
558        )
559        .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
560
561        Ok(())
562    }
563
564    fn get_components(&self, bom_id: Uuid) -> Result<Vec<BomComponent>> {
565        self.load_components(bom_id)
566    }
567
568    fn activate(&self, id: Uuid) -> Result<BillOfMaterials> {
569        self.update(id, UpdateBom { status: Some(BomStatus::Active), ..Default::default() })
570    }
571
572    fn count(&self, filter: BomFilter) -> Result<u64> {
573        let conn = self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
574
575        let mut sql = "SELECT COUNT(*) FROM manufacturing_boms WHERE 1=1".to_string();
576        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
577
578        if let Some(product_id) = filter.product_id {
579            sql.push_str(" AND product_id = ?");
580            params.push(Box::new(product_id.to_string()));
581        }
582
583        if let Some(status) = filter.status {
584            sql.push_str(" AND status = ?");
585            params.push(Box::new(status.to_string()));
586        }
587
588        if let Some(search) = filter.search {
589            sql.push_str(" AND (name LIKE ? OR bom_number LIKE ?)");
590            let search_pattern = format!("%{search}%");
591            params.push(Box::new(search_pattern.clone()));
592            params.push(Box::new(search_pattern));
593        }
594
595        let param_refs: Vec<&dyn rusqlite::ToSql> =
596            params.iter().map(std::convert::AsRef::as_ref).collect();
597
598        let count: i64 = conn
599            .query_row(&sql, param_refs.as_slice(), |row| row.get(0))
600            .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
601
602        Ok(count as u64)
603    }
604
605    // === Batch Operations ===
606
607    fn create_batch(&self, inputs: Vec<CreateBom>) -> Result<BatchResult<BillOfMaterials>> {
608        validate_batch_size(&inputs)?;
609        let mut result = BatchResult::with_capacity(inputs.len());
610
611        for (index, input) in inputs.into_iter().enumerate() {
612            match self.create(input) {
613                Ok(bom) => result.record_success(bom),
614                Err(e) => result.record_failure(index, None, &e),
615            }
616        }
617
618        Ok(result)
619    }
620
621    fn create_batch_atomic(&self, inputs: Vec<CreateBom>) -> Result<Vec<BillOfMaterials>> {
622        validate_batch_size(&inputs)?;
623        if inputs.is_empty() {
624            return Ok(vec![]);
625        }
626
627        let mut conn = self.conn()?;
628        let tx = super::begin_immediate(&mut conn)
629            .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
630        let mut results = Vec::with_capacity(inputs.len());
631
632        for input in inputs {
633            let id = Uuid::new_v4();
634            let bom_number = BillOfMaterials::generate_bom_number();
635            let now = Utc::now();
636            let revision = input.revision.clone().unwrap_or_else(|| "A".to_string());
637
638            tx.execute(
639                "INSERT INTO manufacturing_boms (id, bom_number, product_id, name, description, revision, status, created_by, created_at, updated_at)
640                 VALUES (?, ?, ?, ?, ?, ?, 'draft', ?, ?, ?)",
641                rusqlite::params![
642                    id.to_string(),
643                    bom_number,
644                    input.product_id.to_string(),
645                    input.name,
646                    input.description,
647                    revision,
648                    input.created_by.map(|u| u.to_string()),
649                    now.to_rfc3339(),
650                    now.to_rfc3339(),
651                ],
652            )
653            .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
654
655            // Insert components if provided
656            let mut components = Vec::new();
657            if let Some(comp_inputs) = input.components {
658                for comp_input in comp_inputs {
659                    let comp_id = Uuid::new_v4();
660                    let uom =
661                        comp_input.unit_of_measure.clone().unwrap_or_else(|| "each".to_string());
662
663                    tx.execute(
664                        "INSERT INTO manufacturing_bom_components (id, bom_id, component_product_id, component_sku, name, quantity, unit_of_measure, position, notes, created_at, updated_at)
665                         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
666                        rusqlite::params![
667                            comp_id.to_string(),
668                            id.to_string(),
669                            comp_input.component_product_id.map(|u| u.to_string()),
670                            comp_input.component_sku,
671                            comp_input.name,
672                            comp_input.quantity.to_string(),
673                            uom,
674                            comp_input.position,
675                            comp_input.notes,
676                            now.to_rfc3339(),
677                            now.to_rfc3339(),
678                        ],
679                    )
680                    .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
681
682                    components.push(BomComponent {
683                        id: comp_id,
684                        bom_id: id,
685                        component_product_id: comp_input.component_product_id,
686                        component_sku: comp_input.component_sku,
687                        name: comp_input.name,
688                        quantity: comp_input.quantity,
689                        unit_of_measure: uom,
690                        position: comp_input.position,
691                        notes: comp_input.notes,
692                        created_at: now,
693                        updated_at: now,
694                    });
695                }
696            }
697
698            results.push(BillOfMaterials {
699                id,
700                bom_number,
701                product_id: input.product_id,
702                name: input.name,
703                description: input.description,
704                revision,
705                status: BomStatus::Draft,
706                components,
707                created_by: input.created_by,
708                updated_by: None,
709                created_at: now,
710                updated_at: now,
711            });
712        }
713
714        tx.commit().map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
715        Ok(results)
716    }
717
718    fn update_batch(
719        &self,
720        updates: Vec<(Uuid, UpdateBom)>,
721    ) -> Result<BatchResult<BillOfMaterials>> {
722        validate_batch_size(&updates)?;
723        let mut result = BatchResult::with_capacity(updates.len());
724
725        for (index, (id, input)) in updates.into_iter().enumerate() {
726            match self.update(id, input) {
727                Ok(bom) => result.record_success(bom),
728                Err(e) => result.record_failure(index, Some(id.to_string()), &e),
729            }
730        }
731
732        Ok(result)
733    }
734
735    fn update_batch_atomic(&self, updates: Vec<(Uuid, UpdateBom)>) -> Result<Vec<BillOfMaterials>> {
736        validate_batch_size(&updates)?;
737        if updates.is_empty() {
738            return Ok(vec![]);
739        }
740
741        let mut conn = self.conn()?;
742        let tx = super::begin_immediate(&mut conn)
743            .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
744        let mut results = Vec::with_capacity(updates.len());
745
746        for (id, input) in updates {
747            let now = Utc::now();
748
749            // Fetch existing BOM data within the transaction
750            let existing_data: (String, Option<String>, String, String) = tx
751                .query_row(
752                    "SELECT name, description, revision, status FROM manufacturing_boms WHERE id = ?",
753                    [id.to_string()],
754                    |row| {
755                        Ok((
756                            row.get::<_, String>(0)?,
757                            row.get::<_, Option<String>>(1)?,
758                            row.get::<_, String>(2)?,
759                            row.get::<_, String>(3)?,
760                        ))
761                    },
762                )
763                .map_err(|e| match e {
764                    rusqlite::Error::QueryReturnedNoRows => CommerceError::NotFound,
765                    _ => CommerceError::DatabaseError(e.to_string()),
766                })?;
767
768            let new_name = input.name.unwrap_or(existing_data.0);
769            let new_description = input.description.or(existing_data.1);
770            let new_revision = input.revision.unwrap_or(existing_data.2);
771            let existing_status = Self::parse_bom_status(&existing_data.3)?;
772            let new_status = input.status.unwrap_or(existing_status);
773
774            tx.execute(
775                "UPDATE manufacturing_boms SET name = ?, description = ?, revision = ?, status = ?, updated_by = ?, updated_at = ? WHERE id = ?",
776                rusqlite::params![
777                    new_name,
778                    new_description,
779                    new_revision,
780                    new_status.to_string(),
781                    input.updated_by.map(|u| u.to_string()),
782                    now.to_rfc3339(),
783                    id.to_string(),
784                ],
785            )
786            .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
787
788            // Fetch updated BOM with full data for the result
789            let bom_data: (String, String, Option<String>, Option<String>, String, String) = tx
790                .query_row(
791                    "SELECT bom_number, product_id, created_by, updated_by, created_at, updated_at
792                     FROM manufacturing_boms WHERE id = ?",
793                    [id.to_string()],
794                    |row| {
795                        Ok((
796                            row.get::<_, String>(0)?,
797                            row.get::<_, String>(1)?,
798                            row.get::<_, Option<String>>(2)?,
799                            row.get::<_, Option<String>>(3)?,
800                            row.get::<_, String>(4)?,
801                            row.get::<_, String>(5)?,
802                        ))
803                    },
804                )
805                .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
806
807            results.push(BillOfMaterials {
808                id,
809                bom_number: bom_data.0,
810                product_id: ProductId::from(parse_uuid(&bom_data.1, "bom", "product_id")?),
811                name: new_name,
812                description: new_description,
813                revision: new_revision,
814                status: new_status,
815                components: vec![], // Components loaded separately after commit
816                created_by: match bom_data.2 {
817                    Some(ref s) if !s.is_empty() => Some(parse_uuid(s, "bom", "created_by")?),
818                    _ => None,
819                },
820                updated_by: match bom_data.3 {
821                    Some(ref s) if !s.is_empty() => Some(parse_uuid(s, "bom", "updated_by")?),
822                    _ => None,
823                },
824                created_at: parse_datetime(&bom_data.4, "bom", "created_at")?,
825                updated_at: parse_datetime(&bom_data.5, "bom", "updated_at")?,
826            });
827        }
828
829        tx.commit().map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
830
831        // Load components for each BOM after commit
832        for bom in &mut results {
833            bom.components = self.load_components(bom.id)?;
834        }
835
836        Ok(results)
837    }
838
839    fn delete_batch(&self, ids: Vec<Uuid>) -> Result<BatchResult<Uuid>> {
840        validate_batch_size(&ids)?;
841        let mut result = BatchResult::with_capacity(ids.len());
842
843        for (index, id) in ids.into_iter().enumerate() {
844            match self.delete(id) {
845                Ok(()) => result.record_success(id),
846                Err(e) => result.record_failure(index, Some(id.to_string()), &e),
847            }
848        }
849
850        Ok(result)
851    }
852
853    fn delete_batch_atomic(&self, ids: Vec<Uuid>) -> Result<()> {
854        validate_batch_size(&ids)?;
855        if ids.is_empty() {
856            return Ok(());
857        }
858
859        let mut conn = self.conn()?;
860        let tx = super::begin_immediate(&mut conn)
861            .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
862
863        let placeholders = build_in_clause(ids.len());
864
865        // Mark BOMs as obsolete (soft delete) using IN clause
866        let sql = format!(
867            "UPDATE manufacturing_boms SET status = 'obsolete', updated_at = ? WHERE id IN ({placeholders})"
868        );
869
870        // Build params with timestamp first, then IDs
871        let now = Utc::now().to_rfc3339();
872        let mut all_params: Vec<Box<dyn rusqlite::ToSql>> = vec![Box::new(now)];
873        all_params.extend(uuid_params(&ids));
874        let all_params_refs: Vec<&dyn rusqlite::ToSql> =
875            all_params.iter().map(std::convert::AsRef::as_ref).collect();
876
877        tx.execute(&sql, all_params_refs.as_slice())
878            .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
879
880        tx.commit().map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
881        Ok(())
882    }
883
884    fn get_batch(&self, ids: Vec<Uuid>) -> Result<Vec<BillOfMaterials>> {
885        validate_batch_size(&ids)?;
886        if ids.is_empty() {
887            return Ok(vec![]);
888        }
889
890        let conn = self.conn()?;
891        let placeholders = build_in_clause(ids.len());
892        let sql = format!(
893            "SELECT id, bom_number, product_id, name, description, revision, status,
894                    created_by, updated_by, created_at, updated_at
895             FROM manufacturing_boms WHERE id IN ({placeholders})"
896        );
897
898        let params = uuid_params(&ids);
899        let params_refs = params_refs(&params);
900
901        let mut stmt =
902            conn.prepare(&sql).map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
903
904        let rows = stmt
905            .query_map(params_refs.as_slice(), |row| {
906                Ok((
907                    row.get::<_, String>(0)?,
908                    row.get::<_, String>(1)?,
909                    row.get::<_, String>(2)?,
910                    row.get::<_, String>(3)?,
911                    row.get::<_, Option<String>>(4)?,
912                    row.get::<_, String>(5)?,
913                    row.get::<_, String>(6)?,
914                    row.get::<_, Option<String>>(7)?,
915                    row.get::<_, Option<String>>(8)?,
916                    row.get::<_, String>(9)?,
917                    row.get::<_, String>(10)?,
918                ))
919            })
920            .map_err(|e| CommerceError::DatabaseError(e.to_string()))?;
921
922        let mut bom_data_list = Vec::new();
923        for row in rows {
924            bom_data_list.push(row.map_err(|e| CommerceError::DatabaseError(e.to_string()))?);
925        }
926
927        // Release connection before loading components
928        drop(stmt);
929        drop(conn);
930
931        // Build BOMs and load components
932        let mut boms = Vec::with_capacity(bom_data_list.len());
933        for (
934            id_str,
935            bom_number,
936            product_id,
937            name,
938            description,
939            revision,
940            status,
941            created_by,
942            updated_by,
943            created_at,
944            updated_at,
945        ) in bom_data_list
946        {
947            let bom_id = parse_uuid(&id_str, "bom", "id")?;
948            let components = self.load_components(bom_id)?;
949
950            boms.push(BillOfMaterials {
951                id: bom_id,
952                bom_number,
953                product_id: ProductId::from(parse_uuid(&product_id, "bom", "product_id")?),
954                name,
955                description,
956                revision,
957                status: Self::parse_bom_status(&status)?,
958                components,
959                created_by: match created_by {
960                    Some(ref s) if !s.is_empty() => Some(parse_uuid(s, "bom", "created_by")?),
961                    _ => None,
962                },
963                updated_by: match updated_by {
964                    Some(ref s) if !s.is_empty() => Some(parse_uuid(s, "bom", "updated_by")?),
965                    _ => None,
966                },
967                created_at: parse_datetime(&created_at, "bom", "created_at")?,
968                updated_at: parse_datetime(&updated_at, "bom", "updated_at")?,
969            });
970        }
971
972        Ok(boms)
973    }
974}
975
976#[cfg(test)]
977mod tests {
978    use super::*;
979    use crate::SqliteDatabase;
980    use rust_decimal_macros::dec;
981
982    fn fresh_repo() -> SqliteBomRepository {
983        SqliteDatabase::in_memory().expect("in-memory").bom()
984    }
985
986    fn make_bom(
987        repo: &SqliteBomRepository,
988        product: ProductId,
989        name: &str,
990        components: Vec<CreateBomComponent>,
991    ) -> BillOfMaterials {
992        repo.create(CreateBom {
993            product_id: product,
994            name: name.into(),
995            description: None,
996            revision: None,
997            components: if components.is_empty() { None } else { Some(components) },
998            created_by: None,
999        })
1000        .expect("create bom")
1001    }
1002
1003    #[test]
1004    fn count_applies_search_filter() {
1005        let repo = fresh_repo();
1006        let product = ProductId::new();
1007        make_bom(&repo, product, "Widget Assembly", vec![]);
1008        make_bom(&repo, product, "Gadget Assembly", vec![]);
1009
1010        let filter = BomFilter { search: Some("Widget".into()), ..Default::default() };
1011        let listed = repo.list(filter.clone()).expect("list");
1012        assert_eq!(listed.len(), 1, "list filters by search");
1013        assert_eq!(
1014            repo.count(filter).expect("count"),
1015            1,
1016            "count must apply the search filter and match the filtered list"
1017        );
1018    }
1019
1020    #[test]
1021    fn list_batched_component_loading_preserves_per_parent_components() {
1022        let repo = fresh_repo();
1023        let product = ProductId::new();
1024        let component = |name: &str| CreateBomComponent {
1025            component_product_id: None,
1026            component_sku: Some(name.into()),
1027            name: name.into(),
1028            quantity: dec!(1),
1029            unit_of_measure: None,
1030            position: None,
1031            notes: None,
1032        };
1033        // Distinct component sets per parent so cross-wiring would be caught.
1034        make_bom(&repo, product, "BOM A", vec![component("A-1"), component("A-2")]);
1035        make_bom(&repo, product, "BOM B", vec![component("B-1")]);
1036        make_bom(
1037            &repo,
1038            product,
1039            "BOM C",
1040            vec![component("C-1"), component("C-2"), component("C-3")],
1041        );
1042
1043        let listed = repo.list(BomFilter::default()).expect("list");
1044        assert_eq!(listed.len(), 3);
1045        for bom in &listed {
1046            let fetched = repo.get(bom.id).expect("get").expect("present");
1047            assert_eq!(
1048                bom.components.len(),
1049                fetched.components.len(),
1050                "list must load the same components as get for {}",
1051                bom.name
1052            );
1053            let listed_skus: Vec<_> =
1054                bom.components.iter().map(|c| c.component_sku.clone()).collect();
1055            let fetched_skus: Vec<_> =
1056                fetched.components.iter().map(|c| c.component_sku.clone()).collect();
1057            assert_eq!(listed_skus, fetched_skus, "component sets must match for {}", bom.name);
1058            let prefix = bom.name.chars().next_back().unwrap();
1059            assert!(
1060                bom.components.iter().all(|c| c.name.starts_with(prefix)),
1061                "components must belong to their own parent"
1062            );
1063        }
1064    }
1065
1066    #[test]
1067    fn load_components_orders_by_position() {
1068        let repo = fresh_repo();
1069        let product = ProductId::new();
1070        let component = |pos: &str, name: &str| CreateBomComponent {
1071            component_product_id: None,
1072            component_sku: Some(name.into()),
1073            name: name.into(),
1074            quantity: dec!(1),
1075            unit_of_measure: None,
1076            position: Some(pos.into()),
1077            notes: None,
1078        };
1079        // Insert out of position order; expect ascending position on read-back.
1080        let bom = make_bom(
1081            &repo,
1082            product,
1083            "Ordered BOM",
1084            vec![component("30", "C-30"), component("10", "C-10"), component("20", "C-20")],
1085        );
1086
1087        let loaded = repo.get(bom.id).expect("get").expect("present");
1088        let positions: Vec<String> =
1089            loaded.components.iter().map(|c| c.position.clone().unwrap_or_default()).collect();
1090        assert_eq!(positions, vec!["10", "20", "30"], "components must be ordered by position");
1091    }
1092}
stateset_db/sqlite/bom.rs

stateset_db/sqlite/
bom.rs
