source: sql/latest-unmigrated/ddl_nutritioneer_2_0.sql

CREATE TYPE user_role AS ENUM (
    'user',
    'administrator',
    'trainer'
);

CREATE TYPE ingredient_type AS ENUM (
    'dairy',
    'meat',
    'fish',
    'herb',
    'carb',
    'vegetable',
    'fruit',
    'legume',
    'nut',
    'seed',
    'oil',
    'spice',
    'beverage',
    'other'
);

CREATE TYPE nutrient_unit AS ENUM (
    'milligram',
    'gram',
    'microgram'
);

CREATE TYPE nutrient_type AS ENUM (
    'macro',
    'micro'
);

CREATE TYPE restriction_type AS ENUM (
    'vegan',
    'vegetarian',
    'halal',
    'kosher',
    'pescatarian',
    'allergen',
    'keto',
    'paleo',
    'gluten_free',
    'lactose_free',
    'diabetic',
    'low_sodium',
    'other'
);

CREATE TYPE post_status AS ENUM (
    'published',
    'draft',
    'archived'
);


CREATE TABLE "user" (
    id          BIGINT      PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    email       TEXT        NOT NULL,
    username    TEXT        NOT NULL,
    password    TEXT        NOT NULL,
    role        user_role   NOT NULL DEFAULT 'user',

    CONSTRAINT uq_user_email        UNIQUE (email),
    CONSTRAINT uq_user_username     UNIQUE (username),
    CONSTRAINT ck_user_email_format CHECK  (email LIKE '%_@_%.__%'),
    CONSTRAINT ck_user_username_len CHECK  (length(username) BETWEEN 3 AND 40),
    CONSTRAINT ck_user_password_len CHECK  (length(password) >= 8)
);

CREATE TABLE ingredient (
    id          BIGINT              PRIMARY KEY GENERATED ALWAYS AS IDENTITY, --FIX
    name        TEXT                NOT NULL,
    description TEXT                NOT NULL,
    energy      NUMERIC             NOT NULL,
    kcal        NUMERIC             NOT NULL,
    type        ingredient_type     NOT NULL DEFAULT 'other',

    CONSTRAINT uq_ingredient_name   UNIQUE (name),
    CONSTRAINT ck_ingredient_energy CHECK  (energy >= 0),
    CONSTRAINT ck_ingredient_kcal   CHECK  (kcal   >= 0)
);

CREATE TABLE nutrient (
    id          BIGINT          PRIMARY KEY GENERATED ALWAYS AS IDENTITY, -- FIX
    quantity    NUMERIC         NOT NULL,
    description TEXT            NOT NULL,
    unit        nutrient_unit   NOT NULL,
    type        nutrient_type   NOT NULL,

    CONSTRAINT uq_nutrient_description UNIQUE (description),
    CONSTRAINT ck_nutrient_quantity    CHECK  (quantity > 0)
);

CREATE TABLE restriction (
    id          BIGINT              PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    description TEXT                NOT NULL,
    type        restriction_type    NOT NULL,

    CONSTRAINT uq_restriction_description UNIQUE (description)
);


CREATE TABLE recipe (
    id          BIGINT      PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name        TEXT        NOT NULL,
    guide       TEXT        NOT NULL,
    kcal_sum    NUMERIC     NOT NULL,
    servings    NUMERIC     NOT NULL DEFAULT 1,
    user_id     BIGINT      NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, -- FIX (user_email)

    CONSTRAINT ck_recipe_kcal_sum   CHECK  (kcal_sum >= 0),
    CONSTRAINT ck_recipe_servings   CHECK  (servings > 0)
);


CREATE TABLE post (
    id           BIGINT          PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    media        BYTEA,
    media_type   TEXT,
    media_name   TEXT,
    is_private   BOOLEAN         NOT NULL DEFAULT FALSE,
    is_favourite BOOLEAN         NOT NULL DEFAULT FALSE,
    status       post_status     NOT NULL DEFAULT 'draft',
    created_at   TIMESTAMP       NOT NULL DEFAULT NOW(),
    user_id      BIGINT          NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, --FIX (created_by)
    recipe_id    BIGINT          REFERENCES recipe(id) ON DELETE SET NULL,

    CONSTRAINT ck_post_media_type CHECK  (media IS NULL OR media_type IS NOT NULL),
    CONSTRAINT uq_post_recipe     UNIQUE (recipe_id)
);

CREATE TABLE comment (
    id          BIGINT      PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    description TEXT        NOT NULL,
    created_at  TIMESTAMP   NOT NULL DEFAULT NOW(),
    post_id     BIGINT      NOT NULL REFERENCES post(id)   ON DELETE CASCADE,
    user_id     BIGINT      NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, --FIX (created_by)

    CONSTRAINT ck_comment_not_empty CHECK (length(trim(description)) > 0)
);

CREATE TABLE grocery_list (
    id          BIGINT      PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    date_time   TIMESTAMP   NOT NULL DEFAULT NOW(),
    notes       TEXT,
    is_bought   BOOLEAN     NOT NULL DEFAULT FALSE,
    kcal        NUMERIC     NOT NULL,
    user_id     BIGINT      NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, -- FIX (user_email)

    CONSTRAINT ck_grocery_list_kcal CHECK (kcal >= 0)
);

CREATE TABLE intake_planner (
    id          BIGINT      PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    date_time   TIMESTAMP   NOT NULL DEFAULT NOW(),
    notes       TEXT,
    kcal        NUMERIC     NOT NULL,
    is_consumed BOOLEAN     NOT NULL DEFAULT FALSE,
    user_id     BIGINT      NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, -- FIX (user_email)

    CONSTRAINT ck_intake_planner_kcal CHECK (kcal >= 0)
);

CREATE TABLE biometrics (
    id               BIGINT      PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    date             DATE        NOT NULL,
    weight           NUMERIC,
    height           NUMERIC,
    age              NUMERIC,
    muscle_fat_ratio NUMERIC,
    user_id          BIGINT      NOT NULL REFERENCES "user"(id) ON DELETE CASCADE, -- FIX (user_email)

    CONSTRAINT uq_biometrics_user_date UNIQUE (user_id, date)
);


CREATE TABLE recipe_contains_ingredient (
    recipe_id           BIGINT      NOT NULL REFERENCES recipe(id)     ON DELETE CASCADE,
    ingredient_id       BIGINT      NOT NULL REFERENCES ingredient(id) ON DELETE CASCADE, -- FIX (ingredient_name)
    ingredient_quantity NUMERIC     NOT NULL,
    PRIMARY KEY (recipe_id, ingredient_id)
);

CREATE TABLE ingredient_contains_nutrient ( 
    ingredient_id   BIGINT      NOT NULL REFERENCES ingredient(id) ON DELETE CASCADE, -- FIX (ingredient_name)
    nutrient_id     BIGINT      NOT NULL REFERENCES nutrient(id)   ON DELETE CASCADE,
    quantity        NUMERIC     NOT NULL,
    PRIMARY KEY (ingredient_id, nutrient_id)
);

CREATE TABLE recipe_contains_restriction (
    recipe_id       BIGINT      NOT NULL REFERENCES recipe(id)      ON DELETE CASCADE,
    restriction_id  BIGINT      NOT NULL REFERENCES restriction(id) ON DELETE CASCADE,
    PRIMARY KEY (recipe_id, restriction_id)
);

CREATE TABLE grocery_list_bulk_add_ingredient (
    list_id     BIGINT      NOT NULL REFERENCES grocery_list(id) ON DELETE CASCADE,
    recipe_id   BIGINT      NOT NULL REFERENCES recipe(id)       ON DELETE CASCADE,
    PRIMARY KEY (list_id, recipe_id)
);

CREATE TABLE grocery_list_single_add_ingredient (
    list_id             BIGINT      NOT NULL REFERENCES grocery_list(id) ON DELETE CASCADE,
    ingredient_id       BIGINT      NOT NULL REFERENCES ingredient(id)   ON DELETE CASCADE, -- FIX (ingredient_name)
    buy_quantity        NUMERIC     NOT NULL,
    PRIMARY KEY (list_id, ingredient_id)
);

CREATE TABLE intake_planner_save_to_list_post (
    planner_id  BIGINT      NOT NULL REFERENCES intake_planner(id) ON DELETE CASCADE,
    post_id     BIGINT      NOT NULL REFERENCES post(id)           ON DELETE CASCADE,
    PRIMARY KEY (planner_id, post_id)
);