feat(treewide): Add tests and barcode buying/consuming

13 files changed, 435 insertions, 23 deletions

diff --git a/crates/rocie-server/src/storage/migrate/mod.rs b/crates/rocie-server/src/storage/migrate/mod.rs
index 3fdc400..ae0732b 100644
--- a/crates/rocie-server/src/storage/migrate/mod.rs
+++ b/crates/rocie-server/src/storage/migrate/mod.rs
@@ -198,7 +198,7 @@ pub(crate) mod db_version_parse {
 }
 
 /// Returns the current data as UNIX time stamp.
-fn get_current_date() -> i64 {
+pub(crate) fn get_current_date() -> i64 {
     let start = SystemTime::now();
     let seconds_since_epoch: TimeDelta = TimeDelta::from_std(
         start
diff --git a/crates/rocie-server/src/storage/migrate/sql/0->1.sql b/crates/rocie-server/src/storage/migrate/sql/0->1.sql
index 5aa497d..fd48c68 100644
--- a/crates/rocie-server/src/storage/migrate/sql/0->1.sql
+++ b/crates/rocie-server/src/storage/migrate/sql/0->1.sql
@@ -19,13 +19,45 @@ CREATE TABLE version (
 -- TODO: Fix the possibility for cyclic parent-ship entries <2025-09-05>
 CREATE TABLE parents (
     id                          TEXT UNIQUE NOT NULL PRIMARY KEY,
-    parent                      TEXT                             DEFAULT NULL CHECK (id IS NOT parent),
+    parent                      TEXT                             DEFAULT NULL CHECK (
+                                                                            id IS NOT parent
+                                                                        ),
     FOREIGN KEY(parent) REFERENCES parents(id)
 ) STRICT;
 
+-- Record with barcodes were bought, and how much of this buy is already used up.
+CREATE TABLE buys (
+    buy_id              TEXT    UNIQUE NOT NULL PRIMARY KEY,
+    barcode_id          INTEGER        NOT NULL,
+    used_amount         INTEGER                              DEFAULT NULL CHECK (used_amount > 0),
+    timestamp           INTEGER        NOT NULL,
+    FOREIGN KEY(barcode_id) REFERENCES barcodes(id)
+) STRICT;
+
+CREATE TRIGGER used_amount_lower_or_equal_than_actual_amount_update
+BEFORE UPDATE OF used_amount ON buys
+FOR EACH ROW
+BEGIN
+    SELECT RAISE(FAIL, "Used amount higher than actual amount")
+    FROM barcodes
+    WHERE NEW.barcode_id = barcodes.id
+      AND NEW.used_amount > barcodes.amount;
+END;
+
+CREATE TRIGGER used_amount_lower_or_equal_than_actual_amount_insert
+BEFORE INSERT ON buys
+FOR EACH ROW
+BEGIN
+    SELECT RAISE(FAIL, "Used amount higher than actual amount")
+    FROM barcodes
+    WHERE NEW.barcode_id = barcodes.id
+      AND NEW.used_amount > barcodes.amount;
+END;
+
+
 CREATE TABLE products (
     id                          TEXT UNIQUE NOT NULL PRIMARY KEY,
-    name                        TEXT           NOT NULL,
+    name                        TEXT        NOT NULL,
     description                 TEXT,
     parent                      TEXT                             DEFAULT NULL,
     FOREIGN KEY(parent) REFERENCES parents(id)
diff --git a/crates/rocie-server/src/storage/sql/barcode.rs b/crates/rocie-server/src/storage/sql/barcode.rs
new file mode 100644
index 0000000..239ed8c
--- /dev/null
+++ b/crates/rocie-server/src/storage/sql/barcode.rs
@@ -0,0 +1,30 @@
+use serde::{Deserialize, Serialize};
+use utoipa::ToSchema;
+
+use crate::storage::sql::unit::UnitId;
+
+#[derive(ToSchema, Debug, Clone, Serialize, Deserialize)]
+pub(crate) struct Barcode {
+    #[schema(format = Int64, minimum = 0)]
+    pub(crate) id: BarcodeId,
+    pub(crate) amount: UnitAmount,
+}
+
+#[derive(ToSchema, Debug, Clone, Copy, Serialize, Deserialize)]
+pub(crate) struct BarcodeId(u32);
+
+impl BarcodeId {
+    pub(crate) fn to_db(self) -> i64 {
+        i64::from(self.0)
+    }
+    pub(crate) fn from_db(val: i64) -> Self {
+        Self(u32::try_from(val).expect("Should be strictly positive"))
+    }
+}
+
+#[derive(ToSchema, Debug, Clone, Copy, Serialize, Deserialize)]
+pub(crate) struct UnitAmount {
+    #[schema(format = Int64, minimum = 0)]
+    pub(crate) value: u32,
+    pub(crate) unit: UnitId,
+}
diff --git a/crates/rocie-server/src/storage/sql/get/barcode/mod.rs b/crates/rocie-server/src/storage/sql/get/barcode/mod.rs
new file mode 100644
index 0000000..7b656b1
--- /dev/null
+++ b/crates/rocie-server/src/storage/sql/get/barcode/mod.rs
@@ -0,0 +1,50 @@
+use crate::{
+    app::App,
+    storage::sql::{
+        barcode::{Barcode, BarcodeId, UnitAmount},
+        unit::UnitId,
+    },
+};
+
+use sqlx::query;
+
+impl Barcode {
+    pub(crate) async fn from_id(app: &App, id: BarcodeId) -> Result<Option<Self>, from_id::Error> {
+        let db_id = id.to_db();
+
+        let record = query!(
+            "
+        SELECT product_id, amount, unit
+        FROM barcodes
+        WHERE id = ?
+",
+            db_id
+        )
+        .fetch_optional(&app.db)
+        .await?;
+
+        if let Some(record) = record {
+            Ok(Some(Self {
+                id,
+                amount: UnitAmount {
+                    value: u32::try_from(record.amount).expect("Is strictly positive"),
+                    unit: UnitId::from_db(&record.unit),
+                },
+            }))
+        } else {
+            Ok(None)
+        }
+    }
+}
+
+pub(crate) mod from_id {
+    use actix_web::ResponseError;
+
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("Failed to execute the sql query")]
+        SqlError(#[from] sqlx::Error),
+    }
+
+    impl ResponseError for Error {}
+}
diff --git a/crates/rocie-server/src/storage/sql/get/mod.rs b/crates/rocie-server/src/storage/sql/get/mod.rs
index fa22f81..048cb3d 100644
--- a/crates/rocie-server/src/storage/sql/get/mod.rs
+++ b/crates/rocie-server/src/storage/sql/get/mod.rs
@@ -1,2 +1,4 @@
 pub(crate) mod product;
+pub(crate) mod product_amount;
 pub(crate) mod unit;
+pub(crate) mod barcode;
diff --git a/crates/rocie-server/src/storage/sql/get/product/mod.rs b/crates/rocie-server/src/storage/sql/get/product/mod.rs
index d23297a..541f388 100644
--- a/crates/rocie-server/src/storage/sql/get/product/mod.rs
+++ b/crates/rocie-server/src/storage/sql/get/product/mod.rs
@@ -1,7 +1,8 @@
 use crate::{
     app::App,
     storage::sql::{
-        product::{Barcode, Product, ProductId, UnitAmount},
+        barcode::{Barcode, BarcodeId, UnitAmount},
+        product::{Product, ProductId},
         unit::UnitId,
     },
 };
@@ -63,7 +64,7 @@ impl Product {
                 associated_bar_codes: barcodes
                     .into_iter()
                     .map(|record| Barcode {
-                        id: u32::try_from(record.id).expect("Should be strictly positive"),
+                        id: BarcodeId::from_db(record.id),
                         amount: UnitAmount {
                             value: u32::try_from(record.amount)
                                 .expect("Should be strictly positve"),
diff --git a/crates/rocie-server/src/storage/sql/get/product_amount/mod.rs b/crates/rocie-server/src/storage/sql/get/product_amount/mod.rs
new file mode 100644
index 0000000..7700274
--- /dev/null
+++ b/crates/rocie-server/src/storage/sql/get/product_amount/mod.rs
@@ -0,0 +1,56 @@
+use crate::{
+    app::App,
+    storage::sql::{
+        barcode::UnitAmount, product::ProductId, product_amount::ProductAmount, unit::UnitId,
+    },
+};
+
+use sqlx::query;
+
+impl ProductAmount {
+    pub(crate) async fn from_id(
+        app: &App,
+        product_id: ProductId,
+    ) -> Result<Option<Self>, from_id::Error> {
+        let record = query!(
+            r#"
+SELECT
+    SUM(barcodes.amount - IFNULL(buys.used_amount,0)) "available_amount: u32",
+    barcodes.unit
+FROM barcodes
+    JOIN products ON products.id = ?
+    JOIN buys ON buys.barcode_id = barcodes.id
+GROUP BY barcodes.unit;
+"#,
+            product_id
+        )
+        .fetch_all(&app.db)
+        .await?;
+
+        assert!(record.len() <= 1, "We currently only support one unit");
+
+        if let Some(record) = record.first() {
+            Ok(Some(Self {
+                product_id,
+                amount: UnitAmount {
+                    value: record.available_amount.expect("Should always be set"),
+                    unit: UnitId::from_db(&record.unit),
+                },
+            }))
+        } else {
+            Ok(None)
+        }
+    }
+}
+
+pub(crate) mod from_id {
+    use actix_web::ResponseError;
+
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("Failed to execute the sql query")]
+        SqlError(#[from] sqlx::Error),
+    }
+
+    impl ResponseError for Error {}
+}
diff --git a/crates/rocie-server/src/storage/sql/insert/barcode/mod.rs b/crates/rocie-server/src/storage/sql/insert/barcode/mod.rs
new file mode 100644
index 0000000..62a2e11
--- /dev/null
+++ b/crates/rocie-server/src/storage/sql/insert/barcode/mod.rs
@@ -0,0 +1,241 @@
+use std::str::FromStr;
+
+use serde::{Deserialize, Serialize};
+use sqlx::query;
+use uuid::Uuid;
+
+use crate::{
+    app::App,
+    storage::{
+        migrate::get_current_date,
+        sql::{
+            barcode::{Barcode, BarcodeId, UnitAmount},
+            insert::{Operations, Transactionable},
+            unit::Unit,
+        },
+    },
+};
+
+#[derive(Debug, Deserialize, Serialize)]
+pub(crate) enum Operation {
+    Buy {
+        buy_id: Uuid,
+        id: BarcodeId,
+    },
+    Consume {
+        buy_id: Uuid,
+        id: BarcodeId,
+        amount: UnitAmount,
+    },
+}
+
+impl Transactionable for Operation {
+    type ApplyError = apply::Error;
+    type UndoError = undo::Error;
+
+    async fn apply(self, txn: &mut sqlx::SqliteConnection) -> Result<(), apply::Error> {
+        match self {
+            Operation::Buy { buy_id, id } => {
+                let id = id.to_db();
+                let buy_id = buy_id.to_string();
+                let timestamp = get_current_date();
+
+                query!(
+                    "
+                    INSERT INTO buys (buy_id, barcode_id, timestamp)
+                    VALUES (?, ?, ?)
+",
+                    buy_id,
+                    id,
+                    timestamp,
+                )
+                .execute(txn)
+                .await?;
+            }
+            Operation::Consume { buy_id, id, amount } => {
+                let id = id.to_db();
+                let buy_id = buy_id.to_string();
+
+                let old_amount = {
+                    let record = query!(
+                        "
+                        SELECT used_amount
+                        FROM buys
+                        WHERE buy_id = ?;
+",
+                        buy_id
+                    )
+                    .fetch_one(&mut *txn)
+                    .await?;
+
+                    u32::try_from(record.used_amount.unwrap_or(0))
+                        .expect("Should be strictly positive")
+                };
+
+                // TODO: Check, that this does not overflow the maximum amount. <2025-09-21>
+                let new_amount = amount.value + old_amount;
+
+                // TODO(@bpeetz): We need to add the amount. <2025-09-09>
+                query!(
+                    "
+                    UPDATE buys
+                    SET used_amount = ?
+                    WHERE barcode_id = ? AND buy_id = ?
+",
+                    new_amount,
+                    id,
+                    buy_id
+                )
+                .execute(txn)
+                .await?;
+            }
+        }
+        Ok(())
+    }
+
+    async fn undo(self, txn: &mut sqlx::SqliteConnection) -> Result<(), undo::Error> {
+        match self {
+            Operation::Buy { buy_id, id } => {
+                let id = id.to_db();
+                let buy_id = buy_id.to_string();
+
+                query!(
+                    "
+                    DELETE FROM buys
+                    WHERE barcode_id = ? AND buy_id = ?
+",
+                    id,
+                    buy_id
+                )
+                .execute(txn)
+                .await?;
+            }
+            Operation::Consume { buy_id, id, amount } => {
+                todo!("We would need to subtract the amount.");
+            }
+        }
+        Ok(())
+    }
+}
+
+pub(crate) mod undo {
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("Failed to execute undo sql statments: {0}")]
+        SqlError(#[from] sqlx::Error),
+    }
+}
+pub(crate) mod apply {
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("Failed to execute apply sql statments: {0}")]
+        SqlError(#[from] sqlx::Error),
+    }
+}
+
+impl Barcode {
+    pub(crate) fn buy(&self, ops: &mut Operations<Operation>) {
+        let id = Uuid::new_v4();
+        ops.push(Operation::Buy {
+            buy_id: id,
+            id: self.id,
+        });
+    }
+
+    pub(crate) async fn consume(
+        &self,
+        app: &App,
+        amount: UnitAmount,
+        ops: &mut Operations<Operation>,
+    ) -> Result<(), consume::Error> {
+        assert_eq!(
+            self.amount.unit, amount.unit,
+            "We currently do not support unit conversions yet"
+        );
+
+        if amount.value > self.amount.value {
+            let foreign_amount_unit = Unit::from_id(app, amount.unit).await?;
+
+            if let Some(foreign_amount_unit) = foreign_amount_unit {
+                let self_amount_unit = Unit::from_id(app, self.amount.unit)
+                    .await?
+                    .expect("This unit id should always exist");
+
+                return Err(consume::Error::ConsumedMoreThanAvailable {
+                    consumed: Box::new((amount, foreign_amount_unit)),
+                    available: Box::new((self.amount, self_amount_unit)),
+                });
+            }
+
+            return Err(consume::Error::UnitIdDoesNotExist(amount.unit));
+        }
+
+        let barcode_id = self.id.to_db();
+        let buy_id = {
+            let record = query!(
+                "
+                SELECT buy_id
+                FROM buys
+                WHERE barcode_id = ? AND (used_amount IS NULL OR used_amount < ?)
+                ORDER BY timestamp DESC
+                LIMIT 1;
+",
+                barcode_id,
+                self.amount.value
+            )
+            .fetch_optional(&app.db)
+            .await?;
+
+            if let Some(found) = record {
+                Uuid::from_str(&found.buy_id).expect("Was a uuid, should still be one")
+            } else {
+                return Err(consume::Error::NoMoreAvailable);
+            }
+        };
+
+        ops.push(Operation::Consume {
+            id: self.id,
+            amount,
+            buy_id,
+        });
+
+        Ok(())
+    }
+}
+
+pub(crate) mod consume {
+    use actix_web::ResponseError;
+
+    use crate::storage::{
+        self,
+        sql::{
+            barcode::UnitAmount,
+            unit::{Unit, UnitId},
+        },
+    };
+
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("Failed to execute apply sql statments: {0}")]
+        Sql(#[from] sqlx::Error),
+
+        #[error("Failed to fetch an unit from a specified amount id value")]
+        UnitGet(#[from] storage::sql::get::unit::from_id::Error),
+
+        #[error("The specified unit-id does not exist: {0}")]
+        UnitIdDoesNotExist(UnitId),
+
+        #[error("No more of this product is available, buy more. ")]
+        NoMoreAvailable,
+
+        #[error(
+            "Consumed more than available: consumed {} {}, but available: {} {}", consumed.0.value, consumed.1.short_name, available.0.value, available.1.short_name,
+        )]
+        ConsumedMoreThanAvailable {
+            consumed: Box<(UnitAmount, Unit)>,
+            available: Box<(UnitAmount, Unit)>,
+        },
+    }
+
+    impl ResponseError for Error {}
+}
diff --git a/crates/rocie-server/src/storage/sql/insert/mod.rs b/crates/rocie-server/src/storage/sql/insert/mod.rs
index eec6ad2..e6728d9 100644
--- a/crates/rocie-server/src/storage/sql/insert/mod.rs
+++ b/crates/rocie-server/src/storage/sql/insert/mod.rs
@@ -7,6 +7,7 @@ use log::{debug, trace};
 use serde::{Serialize, de::DeserializeOwned};
 use sqlx::{SqliteConnection, query};
 
+pub(crate) mod barcode;
 pub(crate) mod product;
 pub(crate) mod unit;
 
diff --git a/crates/rocie-server/src/storage/sql/insert/product/mod.rs b/crates/rocie-server/src/storage/sql/insert/product/mod.rs
index b6dd604..e14d3f4 100644
--- a/crates/rocie-server/src/storage/sql/insert/product/mod.rs
+++ b/crates/rocie-server/src/storage/sql/insert/product/mod.rs
@@ -3,8 +3,9 @@ use sqlx::query;
 use uuid::Uuid;
 
 use crate::storage::sql::{
+    barcode::Barcode,
     insert::{Operations, Transactionable},
-    product::{Barcode, Product, ProductId},
+    product::{Product, ProductId},
 };
 
 #[derive(Debug, Deserialize, Serialize)]
@@ -47,7 +48,7 @@ impl Transactionable for Operation {
                 .await?;
             }
             Operation::AssociateBarcode { id, barcode } => {
-                let barcode_id = i64::from(barcode.id);
+                let barcode_id = barcode.id.to_db();
                 let barcode_amount_value = i64::from(barcode.amount.value);
                 let barcode_amount_unit = barcode.amount.unit;
 
@@ -90,7 +91,7 @@ impl Transactionable for Operation {
                 .await?;
             }
             Operation::AssociateBarcode { id, barcode } => {
-                let barcode_id = i64::from(barcode.id);
+                let barcode_id = barcode.id.to_db();
                 let barcode_amount_value = i64::from(barcode.amount.value);
                 let barcode_amount_unit = barcode.amount.unit;
 
diff --git a/crates/rocie-server/src/storage/sql/mod.rs b/crates/rocie-server/src/storage/sql/mod.rs
index f5ad88a..edce187 100644
--- a/crates/rocie-server/src/storage/sql/mod.rs
+++ b/crates/rocie-server/src/storage/sql/mod.rs
@@ -3,4 +3,6 @@ pub(crate) mod insert;
 
 // Types
 pub(crate) mod product;
+pub(crate) mod product_amount;
 pub(crate) mod unit;
+pub(crate) mod barcode;
diff --git a/crates/rocie-server/src/storage/sql/product.rs b/crates/rocie-server/src/storage/sql/product.rs
index e2a4f0d..93cc6a0 100644
--- a/crates/rocie-server/src/storage/sql/product.rs
+++ b/crates/rocie-server/src/storage/sql/product.rs
@@ -5,7 +5,7 @@ use sqlx::{Database, Encode, Type};
 use utoipa::ToSchema;
 use uuid::Uuid;
 
-use crate::storage::sql::unit::{Unit, UnitId};
+use crate::storage::sql::barcode::Barcode;
 
 #[derive(Clone, ToSchema, Serialize, Deserialize)]
 pub(crate) struct Product {
@@ -58,17 +58,3 @@ where
         <String as Type<DB>>::type_info()
     }
 }
-
-#[derive(ToSchema, Debug, Clone, Serialize, Deserialize)]
-pub(crate) struct Barcode {
-    #[schema(format = Int64, minimum = 0)]
-    pub(crate) id: u32,
-    pub(crate) amount: UnitAmount,
-}
-
-#[derive(ToSchema, Debug, Clone, Serialize, Deserialize)]
-pub(crate) struct UnitAmount {
-    #[schema(format = Int64, minimum = 0)]
-    pub(crate) value: u32,
-    pub(crate) unit: UnitId,
-}
diff --git a/crates/rocie-server/src/storage/sql/product_amount.rs b/crates/rocie-server/src/storage/sql/product_amount.rs
new file mode 100644
index 0000000..232c5db
--- /dev/null
+++ b/crates/rocie-server/src/storage/sql/product_amount.rs
@@ -0,0 +1,10 @@
+use serde::{Deserialize, Serialize};
+use utoipa::ToSchema;
+
+use crate::storage::sql::{barcode::UnitAmount, product::ProductId};
+
+#[derive(Clone, ToSchema, Serialize, Deserialize)]
+pub(crate) struct ProductAmount {
+    pub(crate) product_id: ProductId,
+    pub(crate) amount: UnitAmount,
+}