-- user table for users.
CREATE TABLE IF NOT EXISTS Users (
    id INTEGER PRIMARY KEY,
    password TEXT UNIQUE NOT NULL,
    uuid TEXT UNIQUE NOT NULL,
    name TEXT UNIQUE NOT NULL
);

-- statistics table for users to log their consumption
CREATE TABLE IF NOT EXISTS Statistics (
    id INTEGER PRIMARY KEY,
    date DATETIME NOT NULL,
    user_id INT NOT NULL,
    quantity INT
);

-- preferences table for a user.
CREATE TABLE IF NOT EXISTS Preferences (
    id INTEGER PRIMARY KEY,
    color TEXT NOT NULL DEFAULT "#000000",
    user_id INT UNIQUE NOT NULL,
    size_id INT NOT NULL DEFAULT 1,
    FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE,
    FOREIGN KEY(size_id) REFERENCES Sizes(id)
);

-- lookup table for sizes.
CREATE TABLE IF NOT EXISTS Sizes (
    id INTEGER PRIMARY KEY,
    size INT NOT NULL,
    unit TEXT DEFAULT "oz"
);

CREATE TABLE IF NOT EXISTS APIToken (
    id INTEGER PRIMARY KEY,
    token TEXT NOT NULL,
    user_id INTEGER NOT NULL,
    FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE
);