databaseDiagram.dbml

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE
  public.category (
    id UUID NOT NULL DEFAULT uuid_generate_v4 () PRIMARY KEY,
    NAME TEXT NOT NULL UNIQUE,
    description TEXT,
    parent_id UUID REFERENCES public.category (id) ON DELETE SET NULL,
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
  );

CREATE TABLE
  public.user (
    id UUID NOT NULL DEFAULT uuid_generate_v4 () PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    PASSWORD TEXT NOT NULL,
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
  );

CREATE TABLE
  public.recipe (
    id UUID NOT NULL DEFAULT uuid_generate_v4 () PRIMARY KEY,
    NAME TEXT NOT NULL,
    description TEXT,
    preview TEXT,
    servings INTEGER NOT NULL,
    prep_time TEXT NOT NULL,
    cook_time TEXT NOT NULL,
    main_img TEXT NOT NULL,
    main_img_alt TEXT,
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_id UUID NOT NULL REFERENCES public.user (id),
    category_id UUID NOT NULL REFERENCES public.category (id)
  );

CREATE TABLE
  public.recipeimage (
    id UUID NOT NULL DEFAULT uuid_generate_v4 () PRIMARY KEY,
    image_url TEXT NOT NULL,
    alt_text TEXT,
    recipe_id UUID NOT NULL REFERENCES public.recipe (id) ON DELETE CASCADE
  );

CREATE TABLE
  public.ingredient (
    id UUID NOT NULL DEFAULT uuid_generate_v4 () PRIMARY KEY,
    NAME TEXT NOT NULL,
    quantity TEXT NOT NULL,
    recipe_id UUID NOT NULL REFERENCES public.recipe (id) ON DELETE CASCADE
  );

CREATE TABLE
  public.instruction (
    id UUID NOT NULL DEFAULT uuid_generate_v4 () PRIMARY KEY,
    step_number INTEGER NOT NULL,
    description TEXT NOT NULL,
    image_id UUID REFERENCES public.recipeimage (id) ON DELETE SET NULL,
    recipe_id UUID NOT NULL REFERENCES public.recipe (id) ON DELETE CASCADE
  );

CREATE TABLE
  public.like (
    id UUID NOT NULL DEFAULT uuid_generate_v4 () PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES public.user (id),
    recipe_id UUID NOT NULL REFERENCES public.recipe (id),
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, recipe_id)
  );

CREATE TABLE
  public.review (
    id UUID NOT NULL DEFAULT uuid_generate_v4 () PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES public.user (id),
    recipe_id UUID NOT NULL REFERENCES public.recipe (id),
    rating INTEGER NOT NULL CHECK (
      rating >= 1
      AND rating <= 5
    ),
    review_text TEXT,
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
  );