-- rocie - An enterprise grocery management system
--
-- Copyright (C) 2026 Benedikt Peetz <benedikt.peetz@b-peetz.de>
-- SPDX-License-Identifier: GPL-3.0-or-later
--
-- This file is part of Rocie.
--
-- You should have received a copy of the License along with this program.
-- If not, see <https://www.gnu.org/licenses/gpl-3.0.txt>.

-- All tables should be declared STRICT, as I actually like to have types checking (and a
-- db that doesn't lie to me).

CREATE TABLE version (
    -- The `namespace` is only useful, if other tools ever build on this database
    namespace   TEXT           NOT NULL,

    -- The version.
    number      INTEGER UNIQUE NOT NULL PRIMARY KEY,

    -- The validity of this version as UNIX time stamp
    valid_from  INTEGER        NOT NULL CHECK (valid_from < valid_to),
    -- If set to `NULL`, represents the current version
    valid_to    INTEGER UNIQUE          CHECK (valid_to > valid_from)
) STRICT;

-- Encodes the tree structure of the product parents.
-- A product parent cannot be a product, but can have parents on it's own.
-- TODO: Fix the possibility for cyclic parent-ship entries <2025-09-05>
CREATE TABLE product_parents (
    id                          TEXT UNIQUE NOT NULL PRIMARY KEY,
    parent                      TEXT                             DEFAULT NULL CHECK (
                                                                            id IS NOT parent
                                                                        ),
    name                        TEXT UNIQUE NOT NULL,
    description                 TEXT,
    FOREIGN KEY(parent) REFERENCES product_parents(id)
) STRICT;

-- Encodes the tree structure of the recipe parents.
-- TODO: Fix the possibility for cyclic parent-ship entries <2025-09-05>
CREATE TABLE recipe_parents (
    id                          TEXT UNIQUE NOT NULL PRIMARY KEY,
    parent                      TEXT                             DEFAULT NULL CHECK (
                                                                            id IS NOT parent
                                                                        ),
    name                        TEXT UNIQUE NOT NULL,
    description                 TEXT,
    FOREIGN KEY(parent) REFERENCES recipe_parents(id)
) STRICT;

-- Stores the registered users.
CREATE TABLE users (
    id                          TEXT UNIQUE NOT NULL PRIMARY KEY,
    name                        TEXT UNIQUE NOT NULL,
    password_hash               TEXT        NOT NULL,
    description                 TEXT
) 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 UNIQUE NOT NULL,
    description                 TEXT,
    parent                      TEXT                             DEFAULT NULL,
    unit_property               TEXT        NOT NULL,
    FOREIGN KEY(parent) REFERENCES product_parents(id),
    FOREIGN KEY(unit_property) REFERENCES unit_properties(id)
) STRICT;

CREATE TABLE barcodes (
    id                          INTEGER UNIQUE NOT NULL PRIMARY KEY,
    product_id                  TEXT           NOT NULL,
    amount                      INTEGER        NOT NULL,
    unit                        TEXT           NOT NULL,
    FOREIGN KEY(product_id) REFERENCES products(id),
    FOREIGN KEY(unit) REFERENCES units(id)
) STRICT;
CREATE TRIGGER unit_has_property_of_product_update
BEFORE UPDATE OF product_id,unit ON barcodes
FOR EACH ROW
BEGIN
    SELECT RAISE(FAIL, "Used different unit_property for product_id and unit")
    FROM products, units
    WHERE NEW.product_id = products.id
      AND NEW.unit = units.id
      AND units.unit_property != products.unit_property;
END;

CREATE TRIGGER unit_has_property_of_product_insert
BEFORE INSERT ON barcodes
FOR EACH ROW
BEGIN
    SELECT RAISE(FAIL, "Used different unit_property for product_id and unit")
    FROM products, units
    WHERE NEW.product_id = products.id
      AND NEW.unit = units.id
      AND units.unit_property != products.unit_property;
END;

CREATE TABLE units (
    id                          TEXT UNIQUE NOT NULL PRIMARY KEY,
    unit_property               TEXT NOT NULL,
    full_name_singular          TEXT UNIQUE NOT NULL,
    full_name_plural            TEXT UNIQUE NOT NULL,
    short_name                  TEXT UNIQUE NOT NULL,
    description                 TEXT,
    FOREIGN KEY(unit_property) REFERENCES unit_properties(id)
) STRICT;

CREATE TABLE unit_properties (
    id                          TEXT UNIQUE NOT NULL PRIMARY KEY,
    name                        TEXT UNIQUE NOT NULL,
    description                 TEXT
) STRICT;

CREATE TABLE recipies (
    id                          TEXT UNIQUE NOT NULL PRIMARY KEY,
    name                        TEXT UNIQUE NOT NULL,
    parent                      TEXT,
    content                     TEXT        NOT NULL,
    FOREIGN KEY(parent) REFERENCES recipe_parents(id)
) STRICT;

-- Encodes unit conversions:
-- {factor} {from_unit} = 1 {to_unit}
-- E.g.: 1000 g = 1 kg
CREATE TABLE unit_conversions (
    from_unit                  TEXT NOT NULL,
    to_unit                    TEXT NOT NULL,
    factor                     REAL NOT NULL,
    FOREIGN KEY(from_unit)     REFERENCES units(id),
    FOREIGN KEY(to_unit)       REFERENCES units(id)
) STRICT;
CREATE TRIGGER both_units_have_same_unit_property_update
BEFORE UPDATE OF from_unit,to_unit ON unit_conversions
FOR EACH ROW
BEGIN
    SELECT RAISE(FAIL, "Used not the same unit_property")
    FROM units, units AS units2
    WHERE NEW.from_unit = units.id
      AND NEW.to_unit = units2.id
      AND units.unit_property != units2.unit_property;
END;

CREATE TRIGGER both_units_have_same_unit_property_insert
BEFORE INSERT ON unit_conversions
FOR EACH ROW
BEGIN
    SELECT RAISE(FAIL, "Used not the same unit_property")
    FROM units, units AS units2
    WHERE NEW.from_unit = units.id
      AND NEW.to_unit = units2.id
      AND units.unit_property != units2.unit_property;
END;

-- Log of all the applied operations to this db.
CREATE TABLE txn_log (
    timestamp INTEGER NOT NULL,
    operation TEXT    NOT NULL
) STRICT;

CREATE TABLE rocie_config (
    -- Make it impossible to insert more than one value here.
    id           INTEGER PRIMARY KEY NOT NULL CHECK (id = 0),
    use_defaults INTEGER             NOT NULL CHECK (use_defaults = 1 OR use_defaults = 0)
) STRICT;

INSERT INTO rocie_config (id, use_defaults)
VALUES (0, 0);