RelationalDesign: ddl_nutritioneer_0.0.sql

DDL SQL PostgreSQL:

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" (
    email       TEXT        PRIMARY KEY,
    username    TEXT        NOT NULL,
    password    TEXT        NOT NULL,
    role        user_role   NOT NULL DEFAULT 'user'
);

CREATE TABLE ingredient (
    name        TEXT                NOT NULL PRIMARY KEY,
    description TEXT                NOT NULL,
    energy      NUMERIC             NOT NULL,
    kcal        NUMERIC             NOT NULL,
    type        ingredient_type     NOT NULL DEFAULT 'other'
);

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

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

CREATE TABLE recipe (
    id          BIGINT      PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name        TEXT        NOT NULL,
    guide       TEXT        NOT NULL,
    kcal_sum    NUMERIC     NOT NULL,
    user_email  TEXT        NOT NULL REFERENCES "user"(email) ON DELETE CASCADE
);

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_by   TEXT            NOT NULL REFERENCES "user"(email) ON DELETE CASCADE
);

CREATE TABLE comment (
    id          BIGINT      PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    description TEXT        NOT NULL,
    post_id     BIGINT      NOT NULL REFERENCES post(id) ON DELETE CASCADE,
    created_by  TEXT        NOT NULL REFERENCES "user"(email) ON DELETE CASCADE
);

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_email  TEXT        NOT NULL REFERENCES "user"(email) ON DELETE CASCADE
);

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_email  TEXT        NOT NULL REFERENCES "user"(email) ON DELETE CASCADE
);

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_email       TEXT        NOT NULL REFERENCES "user"(email) ON DELETE CASCADE
);

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

CREATE TABLE ingredient_contains_nutrient (
    ingredient_name TEXT        NOT NULL REFERENCES ingredient(name) ON DELETE CASCADE,
    nutrient_id     BIGINT      NOT NULL REFERENCES nutrient(id) ON DELETE CASCADE,
    quantity        NUMERIC     NOT NULL,
    PRIMARY KEY (ingredient_name, 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_name     TEXT        NOT NULL REFERENCES ingredient(name) ON DELETE CASCADE,
    buy_quantity        NUMERIC     NOT NULL,
    PRIMARY KEY (list_id, ingredient_name)
);

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)
);