-- 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 products.
-- A 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 parents (
    id                          TEXT UNIQUE NOT NULL PRIMARY KEY,
    parent                      TEXT                             DEFAULT NULL CHECK (id IS NOT parent),
    FOREIGN KEY(parent) REFERENCES parents(id)
) STRICT;

CREATE TABLE products (
    id                          TEXT UNIQUE NOT NULL PRIMARY KEY,
    name                        TEXT           NOT NULL,
    description                 TEXT,
    parent                      TEXT                             DEFAULT NULL,
    FOREIGN KEY(parent) REFERENCES parents(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 TABLE units (
    id                          TEXT UNIQUE NOT NULL PRIMARY KEY,
    full_name_singular          TEXT UNIQUE NOT NULL,
    full_name_plural            TEXT UNIQUE NOT NULL,
    short_name                  TEXT UNIQUE NOT NULL,
    description                 TEXT
) 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;

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