stateset_db/sqlite/
returns.rs

1//! SQLite return repository implementation
2
3use super::parse_helpers::{parse_decimal, parse_uuid};
4use super::{
5    build_in_clause, map_db_error, params_refs, parse_datetime_row, parse_decimal_opt_row,
6    parse_decimal_row, parse_enum_row, parse_uuid_row, sum_decimal_query, uuid_params,
7};
8use chrono::Utc;
9use r2d2::Pool;
10use r2d2_sqlite::SqliteConnectionManager;
11use rust_decimal::Decimal;
12use stateset_core::{
13    BatchResult, CommerceError, CreateReturn, CustomerId, OrderId, OrderItemId, Result, Return,
14    ReturnFilter, ReturnId, ReturnItem, ReturnRepository, ReturnStatus, UpdateReturn,
15    validate_batch_size,
16};
17use uuid::Uuid;
18
19/// SQLite implementation of `ReturnRepository`
20#[derive(Debug)]
21pub struct SqliteReturnRepository {
22    pool: Pool<SqliteConnectionManager>,
23}
24
25/// Validate a single return line against its order item, inside a write
26/// transaction, returning the item's `(sku, name, unit_price)` for the caller
27/// to record on the return.
28///
29/// Rejects the return when:
30/// - the order item does not exist,
31/// - the order item belongs to a different order than the one being returned, or
32/// - returning `return_qty` more units would exceed what was purchased, counting
33///   units already claimed by non-terminal returns (rejected/cancelled returns
34///   release their claim).
35///
36/// This guards against over-returning (and thus over-refunding) more units than
37/// were ordered, and against returning another order's items.
38fn validate_return_item_tx(
39    tx: &rusqlite::Transaction<'_>,
40    order_id: &str,
41    order_item_id: &str,
42    return_qty: i64,
43) -> Result<(String, String, String)> {
44    let (sku, name, unit_price, oi_order_id, ordered_qty): (String, String, String, String, i64) =
45        tx.query_row(
46            "SELECT sku, name, unit_price, order_id, quantity FROM order_items WHERE id = ?",
47            [order_item_id],
48            |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?, row.get(4)?)),
49        )
50        .map_err(|e| match e {
51            rusqlite::Error::QueryReturnedNoRows => {
52                CommerceError::ValidationError(format!("Order item {order_item_id} not found"))
53            }
54            other => map_db_error(other),
55        })?;
56
57    if oi_order_id != order_id {
58        return Err(CommerceError::ValidationError(format!(
59            "Order item {order_item_id} does not belong to order {order_id}"
60        )));
61    }
62
63    // Units already returned for this order item, excluding rejected/cancelled
64    // returns (which release their claim on the ordered quantity).
65    let already_returned: i64 = tx
66        .query_row(
67            "SELECT COALESCE(SUM(ri.quantity), 0) FROM return_items ri
68             JOIN returns r ON ri.return_id = r.id
69             WHERE ri.order_item_id = ? AND r.status NOT IN ('rejected', 'cancelled')",
70            [order_item_id],
71            |row| row.get(0),
72        )
73        .map_err(map_db_error)?;
74
75    if return_qty + already_returned > ordered_qty {
76        return Err(CommerceError::ValidationError(format!(
77            "Cannot return {return_qty} of order item {order_item_id}: only {} remain returnable ({ordered_qty} ordered, {already_returned} already returned)",
78            ordered_qty - already_returned
79        )));
80    }
81
82    Ok((sku, name, unit_price))
83}
84
85impl SqliteReturnRepository {
86    #[must_use]
87    pub const fn new(pool: Pool<SqliteConnectionManager>) -> Self {
88        Self { pool }
89    }
90
91    fn conn(&self) -> Result<r2d2::PooledConnection<SqliteConnectionManager>> {
92        self.pool.get().map_err(|e| CommerceError::DatabaseError(e.to_string()))
93    }
94
95    fn row_to_return(row: &rusqlite::Row<'_>) -> rusqlite::Result<Return> {
96        Ok(Return {
97            id: ReturnId::from(parse_uuid_row(&row.get::<_, String>("id")?, "return", "id")?),
98            order_id: OrderId::from(parse_uuid_row(
99                &row.get::<_, String>("order_id")?,
100                "return",
101                "order_id",
102            )?),
103            customer_id: CustomerId::from(parse_uuid_row(
104                &row.get::<_, String>("customer_id")?,
105                "return",
106                "customer_id",
107            )?),
108            status: parse_enum_row(&row.get::<_, String>("status")?, "return", "status")?,
109            reason: parse_enum_row(&row.get::<_, String>("reason")?, "return", "reason")?,
110            reason_details: row.get("reason_details")?,
111            idempotency_key: row.get("idempotency_key")?,
112            refund_amount: parse_decimal_opt_row(
113                row.get::<_, Option<String>>("refund_amount")?,
114                "return",
115                "refund_amount",
116            )?,
117            refund_method: row.get("refund_method")?,
118            tracking_number: row.get("tracking_number")?,
119            items: vec![], // Loaded separately
120            notes: row.get("notes")?,
121            version: row.get::<_, Option<i32>>("version")?.unwrap_or(1),
122            created_at: parse_datetime_row(
123                &row.get::<_, String>("created_at")?,
124                "return",
125                "created_at",
126            )?,
127            updated_at: parse_datetime_row(
128                &row.get::<_, String>("updated_at")?,
129                "return",
130                "updated_at",
131            )?,
132        })
133    }
134
135    /// Load items for many returns in one batched `IN (...)` query, keyed by
136    /// the return id's string form.
137    fn load_return_items_batch(
138        conn: &rusqlite::Connection,
139        ids: &[ReturnId],
140    ) -> Result<std::collections::HashMap<String, Vec<ReturnItem>>> {
141        let mut items_by_id: std::collections::HashMap<String, Vec<ReturnItem>> =
142            std::collections::HashMap::with_capacity(ids.len());
143        if ids.is_empty() {
144            return Ok(items_by_id);
145        }
146        let placeholders = vec!["?"; ids.len()].join(", ");
147        let sql = format!(
148            "SELECT id, return_id, order_item_id, sku, name, quantity, condition, refund_amount
149             FROM return_items WHERE return_id IN ({placeholders})"
150        );
151        let mut stmt = conn.prepare(&sql).map_err(map_db_error)?;
152        let params = ids.iter().map(ToString::to_string).collect::<Vec<_>>();
153        let items = stmt
154            .query_map(rusqlite::params_from_iter(params.iter()), |row| {
155                Ok(ReturnItem {
156                    id: parse_uuid_row(&row.get::<_, String>("id")?, "return_item", "id")?,
157                    return_id: ReturnId::from(parse_uuid_row(
158                        &row.get::<_, String>("return_id")?,
159                        "return_item",
160                        "return_id",
161                    )?),
162                    order_item_id: OrderItemId::from(parse_uuid_row(
163                        &row.get::<_, String>("order_item_id")?,
164                        "return_item",
165                        "order_item_id",
166                    )?),
167                    sku: row.get("sku")?,
168                    name: row.get("name")?,
169                    quantity: row.get("quantity")?,
170                    condition: parse_enum_row(
171                        &row.get::<_, String>("condition")?,
172                        "return_item",
173                        "condition",
174                    )?,
175                    refund_amount: parse_decimal_row(
176                        &row.get::<_, String>("refund_amount")?,
177                        "return_item",
178                        "refund_amount",
179                    )?,
180                })
181            })
182            .map_err(map_db_error)?
183            .collect::<rusqlite::Result<Vec<_>>>()
184            .map_err(map_db_error)?;
185        for item in items {
186            items_by_id.entry(item.return_id.to_string()).or_default().push(item);
187        }
188        Ok(items_by_id)
189    }
190
191    #[allow(dead_code)]
192    fn load_return_items(&self, return_id: Uuid) -> Result<Vec<ReturnItem>> {
193        let conn = self.conn()?;
194        let mut stmt = conn
195            .prepare(
196                "SELECT id, return_id, order_item_id, sku, name, quantity, condition, refund_amount
197                 FROM return_items WHERE return_id = ?",
198            )
199            .map_err(map_db_error)?;
200
201        let items = stmt
202            .query_map([return_id.to_string()], |row| {
203                Ok(ReturnItem {
204                    id: parse_uuid_row(&row.get::<_, String>("id")?, "return_item", "id")?,
205                    return_id: ReturnId::from(parse_uuid_row(
206                        &row.get::<_, String>("return_id")?,
207                        "return_item",
208                        "return_id",
209                    )?),
210                    order_item_id: OrderItemId::from(parse_uuid_row(
211                        &row.get::<_, String>("order_item_id")?,
212                        "return_item",
213                        "order_item_id",
214                    )?),
215                    sku: row.get("sku")?,
216                    name: row.get("name")?,
217                    quantity: row.get("quantity")?,
218                    condition: parse_enum_row(
219                        &row.get::<_, String>("condition")?,
220                        "return_item",
221                        "condition",
222                    )?,
223                    refund_amount: parse_decimal_row(
224                        &row.get::<_, String>("refund_amount")?,
225                        "return_item",
226                        "refund_amount",
227                    )?,
228                })
229            })
230            .map_err(map_db_error)?
231            .collect::<rusqlite::Result<Vec<_>>>()
232            .map_err(map_db_error)?;
233
234        Ok(items)
235    }
236
237    /// Delete a return and its items
238    fn delete(&self, id: Uuid) -> Result<()> {
239        let mut conn = self.conn()?;
240        let tx = super::begin_immediate(&mut conn).map_err(map_db_error)?;
241
242        tx.execute("DELETE FROM return_items WHERE return_id = ?", [id.to_string()])
243            .map_err(map_db_error)?;
244        tx.execute("DELETE FROM returns WHERE id = ?", [id.to_string()]).map_err(map_db_error)?;
245        tx.commit().map_err(map_db_error)?;
246        Ok(())
247    }
248
249    fn get_by_idempotency_key(&self, key: &str) -> Result<Option<Return>> {
250        let conn = self.conn()?;
251        let result = conn.query_row(
252            "SELECT * FROM returns WHERE idempotency_key = ?",
253            [key],
254            Self::row_to_return,
255        );
256
257        match result {
258            Ok(mut ret) => {
259                let mut stmt = conn
260                    .prepare(
261                        "SELECT id, return_id, order_item_id, sku, name, quantity, condition, refund_amount
262                         FROM return_items WHERE return_id = ?",
263                    )
264                    .map_err(map_db_error)?;
265
266                ret.items = stmt
267                    .query_map([ret.id.to_string()], |row| {
268                        Ok(ReturnItem {
269                            id: parse_uuid_row(&row.get::<_, String>("id")?, "return_item", "id")?,
270                            return_id: ReturnId::from(parse_uuid_row(
271                                &row.get::<_, String>("return_id")?,
272                                "return_item",
273                                "return_id",
274                            )?),
275                            order_item_id: OrderItemId::from(parse_uuid_row(
276                                &row.get::<_, String>("order_item_id")?,
277                                "return_item",
278                                "order_item_id",
279                            )?),
280                            sku: row.get("sku")?,
281                            name: row.get("name")?,
282                            quantity: row.get("quantity")?,
283                            condition: parse_enum_row(
284                                &row.get::<_, String>("condition")?,
285                                "return_item",
286                                "condition",
287                            )?,
288                            refund_amount: parse_decimal_row(
289                                &row.get::<_, String>("refund_amount")?,
290                                "return_item",
291                                "refund_amount",
292                            )?,
293                        })
294                    })
295                    .map_err(map_db_error)?
296                    .collect::<rusqlite::Result<Vec<_>>>()
297                    .map_err(map_db_error)?;
298
299                Ok(Some(ret))
300            }
301            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
302            Err(e) => Err(map_db_error(e)),
303        }
304    }
305}
306
307impl ReturnRepository for SqliteReturnRepository {
308    fn create(&self, input: CreateReturn) -> Result<Return> {
309        if let Some(key) = input.idempotency_key.as_deref() {
310            if let Some(existing) = self.get_by_idempotency_key(key)? {
311                return Ok(existing);
312            }
313        }
314
315        // Validate return has at least one item
316        if input.items.is_empty() {
317            return Err(CommerceError::ValidationError(
318                "Return must have at least one item".into(),
319            ));
320        }
321
322        // Validate item quantities
323        for item in &input.items {
324            if item.quantity <= 0 {
325                return Err(CommerceError::ValidationError(format!(
326                    "Return item quantity must be positive, got {}",
327                    item.quantity
328                )));
329            }
330        }
331
332        let mut conn = self.conn()?;
333        let tx = super::begin_immediate(&mut conn).map_err(map_db_error)?;
334        let id = Uuid::new_v4();
335        let now = Utc::now();
336
337        // Get order to get customer_id
338        let customer_id: String = tx
339            .query_row(
340                "SELECT customer_id FROM orders WHERE id = ?",
341                [input.order_id.to_string()],
342                |row| row.get(0),
343            )
344            .map_err(|_| CommerceError::OrderNotFound(input.order_id.into()))?;
345
346        tx.execute(
347            "INSERT INTO returns (id, order_id, customer_id, status, reason, reason_details, idempotency_key, notes, created_at, updated_at)
348             VALUES (?, ?, ?, 'requested', ?, ?, ?, ?, ?, ?)",
349            rusqlite::params![
350                id.to_string(),
351                input.order_id.to_string(),
352                customer_id,
353                input.reason.to_string(),
354                input.reason_details,
355                input.idempotency_key,
356                input.notes,
357                now.to_rfc3339(),
358                now.to_rfc3339(),
359            ],
360        )
361        .map_err(map_db_error)?;
362
363        // Insert return items
364        for item in &input.items {
365            let item_id = Uuid::new_v4();
366
367            // Validate the item belongs to this order and the return quantity
368            // does not exceed what remains returnable, then get its details.
369            let (sku, name, unit_price) = validate_return_item_tx(
370                &tx,
371                &input.order_id.to_string(),
372                &item.order_item_id.to_string(),
373                i64::from(item.quantity),
374            )?;
375
376            let refund_amount = parse_decimal(&unit_price, "order_item", "unit_price")?
377                * Decimal::from(item.quantity);
378
379            tx.execute(
380                "INSERT INTO return_items (id, return_id, order_item_id, sku, name, quantity, condition, refund_amount)
381                 VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
382                rusqlite::params![
383                    item_id.to_string(),
384                    id.to_string(),
385                    item.order_item_id.to_string(),
386                    sku,
387                    name,
388                    item.quantity,
389                    item.condition.unwrap_or_default().to_string(),
390                    refund_amount.to_string(),
391                ],
392            )
393            .map_err(map_db_error)?;
394        }
395
396        // Calculate total refund amount
397        let return_id_param = id.to_string();
398        let return_params: [&dyn rusqlite::ToSql; 1] = [&return_id_param];
399        let total_refund = sum_decimal_query(
400            &tx,
401            "SELECT refund_amount FROM return_items WHERE return_id = ?",
402            &return_params,
403            "return_item",
404            "refund_amount",
405        )?;
406
407        tx.execute(
408            "UPDATE returns SET refund_amount = ? WHERE id = ?",
409            rusqlite::params![total_refund.to_string(), return_id_param],
410        )
411        .map_err(map_db_error)?;
412
413        // Build the return with items using the same transaction.
414        let mut ret = tx
415            .query_row("SELECT * FROM returns WHERE id = ?", [id.to_string()], Self::row_to_return)
416            .map_err(map_db_error)?;
417
418        {
419            let mut stmt = tx
420                .prepare(
421                    "SELECT id, return_id, order_item_id, sku, name, quantity, condition, refund_amount
422                     FROM return_items WHERE return_id = ?",
423                )
424                .map_err(map_db_error)?;
425
426            ret.items = stmt
427                .query_map([id.to_string()], |row| {
428                    Ok(ReturnItem {
429                        id: parse_uuid_row(&row.get::<_, String>("id")?, "return_item", "id")?,
430                        return_id: ReturnId::from(parse_uuid_row(
431                            &row.get::<_, String>("return_id")?,
432                            "return_item",
433                            "return_id",
434                        )?),
435                        order_item_id: OrderItemId::from(parse_uuid_row(
436                            &row.get::<_, String>("order_item_id")?,
437                            "return_item",
438                            "order_item_id",
439                        )?),
440                        sku: row.get("sku")?,
441                        name: row.get("name")?,
442                        quantity: row.get("quantity")?,
443                        condition: parse_enum_row(
444                            &row.get::<_, String>("condition")?,
445                            "return_item",
446                            "condition",
447                        )?,
448                        refund_amount: parse_decimal_row(
449                            &row.get::<_, String>("refund_amount")?,
450                            "return_item",
451                            "refund_amount",
452                        )?,
453                    })
454                })
455                .map_err(map_db_error)?
456                .collect::<rusqlite::Result<Vec<_>>>()
457                .map_err(map_db_error)?;
458        }
459
460        tx.commit().map_err(map_db_error)?;
461
462        Ok(ret)
463    }
464
465    fn get(&self, id: ReturnId) -> Result<Option<Return>> {
466        let conn = self.conn()?;
467        let result = conn.query_row(
468            "SELECT * FROM returns WHERE id = ?",
469            [id.to_string()],
470            Self::row_to_return,
471        );
472
473        match result {
474            Ok(mut ret) => {
475                // Inline load_return_items to use same connection
476                let mut stmt = conn
477                    .prepare(
478                        "SELECT id, return_id, order_item_id, sku, name, quantity, condition, refund_amount
479                         FROM return_items WHERE return_id = ?",
480                    )
481                    .map_err(map_db_error)?;
482
483                ret.items = stmt
484                    .query_map([id.to_string()], |row| {
485                        Ok(ReturnItem {
486                            id: parse_uuid_row(&row.get::<_, String>("id")?, "return_item", "id")?,
487                            return_id: ReturnId::from(parse_uuid_row(
488                                &row.get::<_, String>("return_id")?,
489                                "return_item",
490                                "return_id",
491                            )?),
492                            order_item_id: OrderItemId::from(parse_uuid_row(
493                                &row.get::<_, String>("order_item_id")?,
494                                "return_item",
495                                "order_item_id",
496                            )?),
497                            sku: row.get("sku")?,
498                            name: row.get("name")?,
499                            quantity: row.get("quantity")?,
500                            condition: parse_enum_row(
501                                &row.get::<_, String>("condition")?,
502                                "return_item",
503                                "condition",
504                            )?,
505                            refund_amount: parse_decimal_row(
506                                &row.get::<_, String>("refund_amount")?,
507                                "return_item",
508                                "refund_amount",
509                            )?,
510                        })
511                    })
512                    .map_err(map_db_error)?
513                    .collect::<rusqlite::Result<Vec<_>>>()
514                    .map_err(map_db_error)?;
515
516                Ok(Some(ret))
517            }
518            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
519            Err(e) => Err(map_db_error(e)),
520        }
521    }
522
523    fn update(&self, id: ReturnId, input: UpdateReturn) -> Result<Return> {
524        let conn = self.conn()?;
525        let now = Utc::now();
526
527        let mut updates = vec!["updated_at = ?", "version = version + 1"];
528        let mut params: Vec<Box<dyn rusqlite::ToSql>> = vec![Box::new(now.to_rfc3339())];
529
530        if let Some(status) = &input.status {
531            updates.push("status = ?");
532            params.push(Box::new(status.to_string()));
533        }
534        if let Some(tracking) = &input.tracking_number {
535            updates.push("tracking_number = ?");
536            params.push(Box::new(tracking.clone()));
537        }
538        if let Some(amount) = &input.refund_amount {
539            updates.push("refund_amount = ?");
540            params.push(Box::new(amount.to_string()));
541        }
542        if let Some(method) = &input.refund_method {
543            updates.push("refund_method = ?");
544            params.push(Box::new(method.clone()));
545        }
546        if let Some(notes) = &input.notes {
547            updates.push("notes = ?");
548            params.push(Box::new(notes.clone()));
549        }
550
551        params.push(Box::new(id.to_string()));
552
553        let sql = format!("UPDATE returns SET {} WHERE id = ?", updates.join(", "));
554        let params_refs: Vec<&dyn rusqlite::ToSql> =
555            params.iter().map(std::convert::AsRef::as_ref).collect();
556
557        conn.execute(&sql, params_refs.as_slice()).map_err(map_db_error)?;
558
559        // Inline the get logic to avoid connection pool deadlock
560        let result = conn.query_row(
561            "SELECT * FROM returns WHERE id = ?",
562            [id.to_string()],
563            Self::row_to_return,
564        );
565
566        let raw_id: Uuid = id.into();
567        match result {
568            Ok(mut ret) => {
569                // Inline load_return_items to use same connection
570                let mut stmt = conn
571                    .prepare(
572                        "SELECT id, return_id, order_item_id, sku, name, quantity, condition, refund_amount
573                         FROM return_items WHERE return_id = ?",
574                    )
575                    .map_err(map_db_error)?;
576
577                ret.items = stmt
578                    .query_map([id.to_string()], |row| {
579                        Ok(ReturnItem {
580                            id: parse_uuid_row(&row.get::<_, String>("id")?, "return_item", "id")?,
581                            return_id: ReturnId::from(parse_uuid_row(
582                                &row.get::<_, String>("return_id")?,
583                                "return_item",
584                                "return_id",
585                            )?),
586                            order_item_id: OrderItemId::from(parse_uuid_row(
587                                &row.get::<_, String>("order_item_id")?,
588                                "return_item",
589                                "order_item_id",
590                            )?),
591                            sku: row.get("sku")?,
592                            name: row.get("name")?,
593                            quantity: row.get("quantity")?,
594                            condition: parse_enum_row(
595                                &row.get::<_, String>("condition")?,
596                                "return_item",
597                                "condition",
598                            )?,
599                            refund_amount: parse_decimal_row(
600                                &row.get::<_, String>("refund_amount")?,
601                                "return_item",
602                                "refund_amount",
603                            )?,
604                        })
605                    })
606                    .map_err(map_db_error)?
607                    .collect::<rusqlite::Result<Vec<_>>>()
608                    .map_err(map_db_error)?;
609
610                Ok(ret)
611            }
612            Err(rusqlite::Error::QueryReturnedNoRows) => Err(CommerceError::ReturnNotFound(raw_id)),
613            Err(e) => Err(map_db_error(e)),
614        }
615    }
616
617    fn list(&self, filter: ReturnFilter) -> Result<Vec<Return>> {
618        let conn = self.conn()?;
619        let mut sql = "SELECT * FROM returns WHERE 1=1".to_string();
620        let mut params: Vec<Box<dyn rusqlite::ToSql>> = vec![];
621
622        if let Some(order_id) = &filter.order_id {
623            sql.push_str(" AND order_id = ?");
624            params.push(Box::new(order_id.to_string()));
625        }
626        if let Some(customer_id) = &filter.customer_id {
627            sql.push_str(" AND customer_id = ?");
628            params.push(Box::new(customer_id.to_string()));
629        }
630        if let Some(status) = &filter.status {
631            sql.push_str(" AND status = ?");
632            params.push(Box::new(status.to_string()));
633        }
634        if let Some(reason) = &filter.reason {
635            sql.push_str(" AND reason = ?");
636            params.push(Box::new(reason.to_string()));
637        }
638        if let Some(from) = &filter.from_date {
639            sql.push_str(" AND created_at >= ?");
640            params.push(Box::new(from.to_rfc3339()));
641        }
642        if let Some(to) = &filter.to_date {
643            sql.push_str(" AND created_at <= ?");
644            params.push(Box::new(to.to_rfc3339()));
645        }
646
647        // Keyset cursor: (created_at, id) for stable DESC ordering
648        if let Some((cursor_date, cursor_id)) = &filter.after_cursor {
649            sql.push_str(" AND (created_at < ? OR (created_at = ? AND id < ?))");
650            params.push(Box::new(cursor_date.clone()));
651            params.push(Box::new(cursor_date.clone()));
652            params.push(Box::new(cursor_id.clone()));
653        }
654
655        sql.push_str(" ORDER BY created_at DESC, id DESC");
656
657        // Offset pagination applies only in non-cursor mode; the helper emits
658        // `LIMIT -1 OFFSET n` when an offset is set without a limit (SQLite rejects
659        // a bare OFFSET).
660        let offset = if filter.after_cursor.is_none() { filter.offset } else { None };
661        crate::sqlite::append_limit_offset(&mut sql, filter.limit, offset);
662
663        let params_refs: Vec<&dyn rusqlite::ToSql> =
664            params.iter().map(std::convert::AsRef::as_ref).collect();
665        let mut stmt = conn.prepare(&sql).map_err(map_db_error)?;
666
667        let returns = stmt
668            .query_map(params_refs.as_slice(), Self::row_to_return)
669            .map_err(map_db_error)?
670            .collect::<rusqlite::Result<Vec<_>>>()
671            .map_err(map_db_error)?;
672
673        // Load items for each return using same connection
674        let ids: Vec<ReturnId> = returns.iter().map(|r| r.id).collect();
675        let mut items_by_id = Self::load_return_items_batch(&conn, &ids)?;
676        let mut result = vec![];
677        for mut ret in returns {
678            ret.items = items_by_id.remove(&ret.id.to_string()).unwrap_or_default();
679            result.push(ret);
680        }
681
682        Ok(result)
683    }
684
685    fn approve(&self, id: ReturnId) -> Result<Return> {
686        let ret = self.get(id)?.ok_or(CommerceError::ReturnNotFound(id.into()))?;
687
688        if ret.status != ReturnStatus::Requested {
689            return Err(CommerceError::ReturnCannotBeApproved(ret.status.to_string()));
690        }
691
692        self.update(id, UpdateReturn { status: Some(ReturnStatus::Approved), ..Default::default() })
693    }
694
695    fn reject(&self, id: ReturnId, reason: &str) -> Result<Return> {
696        let ret = self.get(id)?.ok_or(CommerceError::ReturnNotFound(id.into()))?;
697
698        if ret.status != ReturnStatus::Requested {
699            return Err(CommerceError::ReturnCannotBeApproved(ret.status.to_string()));
700        }
701
702        self.update(
703            id,
704            UpdateReturn {
705                status: Some(ReturnStatus::Rejected),
706                notes: Some(reason.to_string()),
707                ..Default::default()
708            },
709        )
710    }
711
712    fn complete(&self, id: ReturnId) -> Result<Return> {
713        let ret = self.get(id)?.ok_or(CommerceError::ReturnNotFound(id.into()))?;
714
715        if !ret.can_complete() {
716            return Err(CommerceError::NotPermitted(format!(
717                "Return cannot be completed in status: {}",
718                ret.status
719            )));
720        }
721
722        self.update(
723            id,
724            UpdateReturn { status: Some(ReturnStatus::Completed), ..Default::default() },
725        )
726    }
727
728    fn cancel(&self, id: ReturnId) -> Result<Return> {
729        self.update(
730            id,
731            UpdateReturn { status: Some(ReturnStatus::Cancelled), ..Default::default() },
732        )
733    }
734
735    fn count(&self, filter: ReturnFilter) -> Result<u64> {
736        let conn = self.conn()?;
737        let mut sql = "SELECT COUNT(*) FROM returns WHERE 1=1".to_string();
738        let mut params: Vec<Box<dyn rusqlite::ToSql>> = vec![];
739
740        if let Some(status) = &filter.status {
741            sql.push_str(" AND status = ?");
742            params.push(Box::new(status.to_string()));
743        }
744
745        let params_refs: Vec<&dyn rusqlite::ToSql> =
746            params.iter().map(std::convert::AsRef::as_ref).collect();
747        let count: i64 =
748            conn.query_row(&sql, params_refs.as_slice(), |row| row.get(0)).map_err(map_db_error)?;
749
750        Ok(count as u64)
751    }
752
753    // === Batch Operations ===
754
755    fn create_batch(&self, inputs: Vec<CreateReturn>) -> Result<BatchResult<Return>> {
756        validate_batch_size(&inputs)?;
757        let mut result = BatchResult::with_capacity(inputs.len());
758
759        for (index, input) in inputs.into_iter().enumerate() {
760            match self.create(input) {
761                Ok(ret) => result.record_success(ret),
762                Err(e) => result.record_failure(index, None, &e),
763            }
764        }
765
766        Ok(result)
767    }
768
769    fn create_batch_atomic(&self, inputs: Vec<CreateReturn>) -> Result<Vec<Return>> {
770        validate_batch_size(&inputs)?;
771        if inputs.is_empty() {
772            return Ok(vec![]);
773        }
774
775        let mut conn = self.conn()?;
776        let tx = super::begin_immediate(&mut conn).map_err(map_db_error)?;
777        let mut results = Vec::with_capacity(inputs.len());
778
779        for input in inputs {
780            let id = Uuid::new_v4();
781            let now = Utc::now();
782
783            // Get order to get customer_id
784            let customer_id: String = tx
785                .query_row(
786                    "SELECT customer_id FROM orders WHERE id = ?",
787                    [input.order_id.to_string()],
788                    |row| row.get(0),
789                )
790                .map_err(|_| CommerceError::OrderNotFound(input.order_id.into()))?;
791
792            tx.execute(
793                "INSERT INTO returns (id, order_id, customer_id, status, reason, reason_details, idempotency_key, notes, created_at, updated_at)
794                 VALUES (?, ?, ?, 'requested', ?, ?, ?, ?, ?, ?)",
795                rusqlite::params![
796                    id.to_string(),
797                    input.order_id.to_string(),
798                    customer_id,
799                    input.reason.to_string(),
800                    input.reason_details,
801                    input.idempotency_key.clone(),
802                    input.notes,
803                    now.to_rfc3339(),
804                    now.to_rfc3339(),
805                ],
806            )
807            .map_err(map_db_error)?;
808
809            // Insert return items
810            let mut items = Vec::with_capacity(input.items.len());
811            for item in &input.items {
812                let item_id = Uuid::new_v4();
813
814                // Validate the item belongs to this order and the return
815                // quantity does not exceed what remains returnable, then get its
816                // details.
817                let (sku, name, unit_price) = validate_return_item_tx(
818                    &tx,
819                    &input.order_id.to_string(),
820                    &item.order_item_id.to_string(),
821                    i64::from(item.quantity),
822                )?;
823
824                let refund_amount = parse_decimal(&unit_price, "order_item", "unit_price")?
825                    * Decimal::from(item.quantity);
826
827                tx.execute(
828                    "INSERT INTO return_items (id, return_id, order_item_id, sku, name, quantity, condition, refund_amount)
829                     VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
830                    rusqlite::params![
831                        item_id.to_string(),
832                        id.to_string(),
833                        item.order_item_id.to_string(),
834                        sku.clone(),
835                        name.clone(),
836                        item.quantity,
837                        item.condition.unwrap_or_default().to_string(),
838                        refund_amount.to_string(),
839                    ],
840                )
841                .map_err(map_db_error)?;
842
843                items.push(ReturnItem {
844                    id: item_id,
845                    return_id: ReturnId::from(id),
846                    order_item_id: item.order_item_id,
847                    sku,
848                    name,
849                    quantity: item.quantity,
850                    condition: item.condition.unwrap_or_default(),
851                    refund_amount,
852                });
853            }
854
855            // Calculate total refund amount
856            let return_id_param = id.to_string();
857            let return_params: [&dyn rusqlite::ToSql; 1] = [&return_id_param];
858            let total_refund = sum_decimal_query(
859                &tx,
860                "SELECT refund_amount FROM return_items WHERE return_id = ?",
861                &return_params,
862                "return_item",
863                "refund_amount",
864            )?;
865
866            tx.execute(
867                "UPDATE returns SET refund_amount = ? WHERE id = ?",
868                rusqlite::params![total_refund.to_string(), return_id_param],
869            )
870            .map_err(map_db_error)?;
871
872            results.push(Return {
873                id: ReturnId::from(id),
874                order_id: input.order_id,
875                customer_id: CustomerId::from(parse_uuid(&customer_id, "return", "customer_id")?),
876                status: ReturnStatus::Requested,
877                reason: input.reason,
878                reason_details: input.reason_details,
879                idempotency_key: input.idempotency_key,
880                refund_amount: Some(total_refund),
881                refund_method: None,
882                tracking_number: None,
883                items,
884                notes: input.notes,
885                version: 1,
886                created_at: now,
887                updated_at: now,
888            });
889        }
890
891        tx.commit().map_err(map_db_error)?;
892        Ok(results)
893    }
894
895    fn update_batch(&self, updates: Vec<(ReturnId, UpdateReturn)>) -> Result<BatchResult<Return>> {
896        validate_batch_size(&updates)?;
897        let mut result = BatchResult::with_capacity(updates.len());
898
899        for (index, (id, input)) in updates.into_iter().enumerate() {
900            match self.update(id, input) {
901                Ok(ret) => result.record_success(ret),
902                Err(e) => result.record_failure(index, Some(id.to_string()), &e),
903            }
904        }
905
906        Ok(result)
907    }
908
909    fn update_batch_atomic(&self, updates: Vec<(ReturnId, UpdateReturn)>) -> Result<Vec<Return>> {
910        validate_batch_size(&updates)?;
911        if updates.is_empty() {
912            return Ok(vec![]);
913        }
914
915        let mut conn = self.conn()?;
916        let tx = super::begin_immediate(&mut conn).map_err(map_db_error)?;
917        let mut results = Vec::with_capacity(updates.len());
918
919        for (id, input) in updates {
920            let now = Utc::now();
921
922            let mut update_parts = vec!["updated_at = ?"];
923            let mut params: Vec<Box<dyn rusqlite::ToSql>> = vec![Box::new(now.to_rfc3339())];
924
925            if let Some(status) = &input.status {
926                update_parts.push("status = ?");
927                params.push(Box::new(status.to_string()));
928            }
929            if let Some(tracking) = &input.tracking_number {
930                update_parts.push("tracking_number = ?");
931                params.push(Box::new(tracking.clone()));
932            }
933            if let Some(amount) = &input.refund_amount {
934                update_parts.push("refund_amount = ?");
935                params.push(Box::new(amount.to_string()));
936            }
937            if let Some(method) = &input.refund_method {
938                update_parts.push("refund_method = ?");
939                params.push(Box::new(method.clone()));
940            }
941            if let Some(notes) = &input.notes {
942                update_parts.push("notes = ?");
943                params.push(Box::new(notes.clone()));
944            }
945
946            params.push(Box::new(id.to_string()));
947
948            let sql = format!("UPDATE returns SET {} WHERE id = ?", update_parts.join(", "));
949            let params_refs: Vec<&dyn rusqlite::ToSql> =
950                params.iter().map(std::convert::AsRef::as_ref).collect();
951
952            let rows_affected = tx.execute(&sql, params_refs.as_slice()).map_err(map_db_error)?;
953            if rows_affected == 0 {
954                return Err(CommerceError::ReturnNotFound(id.into()));
955            }
956
957            // Fetch the updated return
958            let ret = tx
959                .query_row(
960                    "SELECT * FROM returns WHERE id = ?",
961                    [id.to_string()],
962                    Self::row_to_return,
963                )
964                .map_err(map_db_error)?;
965
966            results.push(ret);
967        }
968
969        tx.commit().map_err(map_db_error)?;
970
971        // Load items for all returns in one batched query
972        let conn = self.conn()?;
973        let ids: Vec<ReturnId> = results.iter().map(|r| r.id).collect();
974        let mut items_by_id = Self::load_return_items_batch(&conn, &ids)?;
975        for ret in &mut results {
976            ret.items = items_by_id.remove(&ret.id.to_string()).unwrap_or_default();
977        }
978
979        Ok(results)
980    }
981
982    fn delete_batch(&self, ids: Vec<ReturnId>) -> Result<BatchResult<Uuid>> {
983        validate_batch_size(&ids)?;
984        let mut result = BatchResult::with_capacity(ids.len());
985
986        for (index, id) in ids.into_iter().enumerate() {
987            let raw_id: Uuid = id.into();
988            match self.delete(raw_id) {
989                Ok(()) => result.record_success(raw_id),
990                Err(e) => result.record_failure(index, Some(id.to_string()), &e),
991            }
992        }
993
994        Ok(result)
995    }
996
997    fn delete_batch_atomic(&self, ids: Vec<ReturnId>) -> Result<()> {
998        validate_batch_size(&ids)?;
999        if ids.is_empty() {
1000            return Ok(());
1001        }
1002
1003        let mut conn = self.conn()?;
1004        let tx = super::begin_immediate(&mut conn).map_err(map_db_error)?;
1005
1006        let raw_ids: Vec<Uuid> = ids.iter().map(|id| (*id).into()).collect();
1007        let placeholders = build_in_clause(ids.len());
1008        let params = uuid_params(&raw_ids);
1009        let params_refs = params_refs(&params);
1010
1011        // Delete return items first
1012        let sql = format!("DELETE FROM return_items WHERE return_id IN ({placeholders})");
1013        tx.execute(&sql, params_refs.as_slice()).map_err(map_db_error)?;
1014
1015        // Delete returns
1016        let sql = format!("DELETE FROM returns WHERE id IN ({placeholders})");
1017        tx.execute(&sql, params_refs.as_slice()).map_err(map_db_error)?;
1018
1019        tx.commit().map_err(map_db_error)?;
1020        Ok(())
1021    }
1022
1023    fn get_batch(&self, ids: Vec<ReturnId>) -> Result<Vec<Return>> {
1024        validate_batch_size(&ids)?;
1025        if ids.is_empty() {
1026            return Ok(vec![]);
1027        }
1028
1029        let conn = self.conn()?;
1030        let raw_ids: Vec<Uuid> = ids.iter().map(|id| (*id).into()).collect();
1031        let placeholders = build_in_clause(ids.len());
1032        let sql = format!("SELECT * FROM returns WHERE id IN ({placeholders})");
1033
1034        let params = uuid_params(&raw_ids);
1035        let params_refs = params_refs(&params);
1036
1037        let mut stmt = conn.prepare(&sql).map_err(map_db_error)?;
1038        let returns = stmt
1039            .query_map(params_refs.as_slice(), Self::row_to_return)
1040            .map_err(map_db_error)?
1041            .collect::<rusqlite::Result<Vec<_>>>()
1042            .map_err(map_db_error)?;
1043
1044        // Load items for all returns in one batched query
1045        let ids: Vec<ReturnId> = returns.iter().map(|r| r.id).collect();
1046        let mut items_by_id = Self::load_return_items_batch(&conn, &ids)?;
1047        let mut result = vec![];
1048        for mut ret in returns {
1049            ret.items = items_by_id.remove(&ret.id.to_string()).unwrap_or_default();
1050            result.push(ret);
1051        }
1052
1053        Ok(result)
1054    }
1055}
stateset_db/sqlite/returns.rs

stateset_db/sqlite/
returns.rs
