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

1 files changed, 34 insertions, 2 deletions

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)