-- articles
CREATE TABLE IF NOT EXISTS Articles
(
    id            INTEGER PRIMARY KEY NOT NULL,
    uuid          TEXT UNIQUE         NOT NULL,                                                       -- UUID
    stock         INTEGER             NOT NULL DEFAULT 0,
    stock_expected INTEGER             NOT NULL DEFAULT 100,
    name          TEXT                NOT NULL,
    description   TEXT                NULL,                                                           --in html
    price100      INTEGER             NOT NULL,                                                       -- in cents
    discount100   INTEGER             NULL,                                                           -- in percent
    category      TEXT                NULL,

    CONSTRAINT c_stock CHECK ( stock >= 0 ),
    CONSTRAINT c_stockExpected CHECK ( stock_expected >= 0 ),                                          --inactive in current version
    CONSTRAINT c_discount100 CHECK ( discount100 IS NULL OR 0 <= discount100 AND discount100 <= 100 ) --inactive in current version
);

-- article images
CREATE TABLE IF NOT EXISTS Images
(
    id         INTEGER PRIMARY KEY NOT NULL,
    article_id INTEGER             NOT NULL,
    base64     TEXT                NOT NULL,
    FOREIGN KEY (article_id) REFERENCES Articles (id)
);

CREATE TABLE IF NOT EXISTS Farm_Images
(
    id         INTEGER PRIMARY KEY NOT NULL,
    article_id INTEGER             NOT NULL,
    base64     TEXT                NOT NULL,
    standard   INTEGER             NOT NULL, --there should always only be one standard image

    FOREIGN KEY (article_id) REFERENCES Articles (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS Reviews
(
    id         INTEGER PRIMARY KEY NOT NULL,
    article_id INTEGER             NOT NULL,
    rating     INTEGER             NOT NULL,
    content    TEXT                NULL,
    timestamp  INTEGER             NOT NULL,
    FOREIGN KEY (article_id) REFERENCES Articles (id),
    CONSTRAINT c_rating CHECK ( rating >= 0 AND rating <= 10)
);

CREATE TABLE IF NOT EXISTS Customers
(
    id      INTEGER PRIMARY KEY NOT NULL,
    name    TEXT                NOT NULL, --nachname
    surname TEXT                NOT NULL, --vorname,
    address TEXT                NOT NULL,
    country TEXT                NOT NULL,
    zip     TEXT                NOT NULL
);

CREATE TABLE IF NOT EXISTS Accounts
(
    id          INTEGER PRIMARY KEY NOT NULL,
    customer_id INTEGER             NOT NULL,
    email       TEXT                NOT NULL,
    password    TEXT                NOT NULL,
    lang_i18n   TEXT                NOT NULL,
    admin       INTEGER             NOT NULL DEFAULT 0,
    FOREIGN KEY (customer_id) REFERENCES Customers (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS Orders
(
    id          INTEGER PRIMARY KEY NOT NULL,
    customer_id INTEGER             NOT NULL,
    time        INTEGER             NOT NULL, --unix millis or epoch, TBD
    status      INTEGER             NOT NULL, --enum, see java or JS
    FOREIGN KEY (customer_id) REFERENCES Customers (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS Order_Items
(
    id         INTEGER NOT NULL PRIMARY KEY,
    order_id   INTEGER NOT NULL,
    article_id INTEGER NOT NULL,
    amount     INTEGER NOT NULL,
    -- art conf not needed

    CONSTRAINT cAmount CHECK (amount > 0),
    FOREIGN KEY (order_id) REFERENCES Orders (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (article_id) REFERENCES Articles (id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS Sessions
(
    id         INTEGER     NOT NULL PRIMARY KEY,
    token      TEXT UNIQUE NOT NULL,
    timeout    INTEGER     NOT NULL,
    account_id INTEGER     NOT NULL,
    FOREIGN KEY (account_id) REFERENCES Accounts (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);