-- ===== LOOKUP TABLES =====
CREATE TABLE role (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE cuisine (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE category (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  text VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE allergen (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE ingredient (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
);

-- ===== USER =====
CREATE TABLE "user" (
  id SERIAL PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL UNIQUE 
  CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
  password VARCHAR(255) NOT NULL
  CHECK (
    password ~ '[A-Z]' AND
    password ~ '[a-z]' AND
    password ~ '[0-9]' AND
    length(password) >= 8
  ),
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE user_role (
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  PRIMARY KEY (user_id, role_id),

  FOREIGN KEY (user_id)
    REFERENCES "user"(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  FOREIGN KEY (role_id)
    REFERENCES role(id)
    ON DELETE RESTRICT -- da ne mozhe da se izbrishe role ako se koristi
    ON UPDATE CASCADE
);

CREATE TABLE user_allergen (
  user_id INT NOT NULL,
  allergen_id INT NOT NULL,
  PRIMARY KEY (user_id, allergen_id),

  FOREIGN KEY (user_id)
    REFERENCES "user"(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  FOREIGN KEY (allergen_id)
    REFERENCES allergen(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

-- ===== PREFERENCES =====
CREATE TABLE preferences (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL UNIQUE,
  calorie_limit INT CHECK (calorie_limit >= 0),
  spice_level INT CHECK (spice_level BETWEEN 1 AND 5),
  max_proteins INT CHECK (max_proteins >= 0),
  max_cooking_time INT CHECK (max_cooking_time >= 0),

  FOREIGN KEY (user_id)
    REFERENCES "user"(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE preferences_cuisine (
  preferences_id INT NOT NULL,
  cuisine_id INT NOT NULL,
  PRIMARY KEY (preferences_id, cuisine_id),

  FOREIGN KEY (preferences_id)
    REFERENCES preferences(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  FOREIGN KEY (cuisine_id)
    REFERENCES cuisine(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

-- ===== RECIPE =====
CREATE TABLE recipe (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  image VARCHAR(500),
  servings INT CHECK (servings > 0),
  proteins INT CHECK (proteins >= 0),
  fat INT CHECK (fat >= 0),
  carbs INT CHECK (carbs >= 0),
  spice_level INT CHECK (spice_level BETWEEN 1 AND 5),
  total_time INT CHECK (total_time >= 0),
  calories INT CHECK (calories >= 0),
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  cuisine_id INT,

  FOREIGN KEY (cuisine_id)
    REFERENCES cuisine(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

CREATE TABLE recipe_category (
  recipe_id INT NOT NULL,
  category_id INT NOT NULL,
  PRIMARY KEY (recipe_id, category_id),

  FOREIGN KEY (recipe_id)
    REFERENCES recipe(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  FOREIGN KEY (category_id)
    REFERENCES category(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

CREATE TABLE recipe_tags (
  recipe_id INT NOT NULL,
  tag_id INT NOT NULL,
  PRIMARY KEY (recipe_id, tag_id),

  FOREIGN KEY (recipe_id)
    REFERENCES recipe(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  FOREIGN KEY (tag_id)
    REFERENCES tags(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

CREATE TABLE recipe_ingredient (
  recipe_id INT NOT NULL,
  ingredient_id INT NOT NULL,
  quantity VARCHAR(100),

  PRIMARY KEY (recipe_id, ingredient_id),

  FOREIGN KEY (recipe_id)
    REFERENCES recipe(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  FOREIGN KEY (ingredient_id)
    REFERENCES ingredient(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

CREATE TABLE ingredient_allergen (
  ingredient_id INT NOT NULL,
  allergen_id INT NOT NULL,
  PRIMARY KEY (ingredient_id, allergen_id),

  FOREIGN KEY (ingredient_id)
    REFERENCES ingredient(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  FOREIGN KEY (allergen_id)
    REFERENCES allergen(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

-- ===== INSTRUCTIONS =====
CREATE TABLE instruction (
  id SERIAL PRIMARY KEY,
  recipe_id INT NOT NULL,
  step_number INT NOT NULL CHECK (step_number > 0),
  text TEXT NOT NULL,

  UNIQUE (recipe_id, step_number),

  FOREIGN KEY (recipe_id)
    REFERENCES recipe(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE instruction_media (
  id SERIAL PRIMARY KEY,
  instruction_id INT NOT NULL,
  url VARCHAR(500) NOT NULL,
  type VARCHAR(20) NOT NULL CHECK (type IN ('image', 'video')),
  created_at TIMESTAMP DEFAULT NOW(),

  FOREIGN KEY (instruction_id)
    REFERENCES instruction(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

-- ===== FORUM =====
CREATE TABLE forumpost (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  recipe_id INT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),

  FOREIGN KEY (user_id)
    REFERENCES "user"(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  FOREIGN KEY (recipe_id)
    REFERENCES recipe(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

CREATE TABLE forumpost_like (
  user_id INT NOT NULL,
  forumpost_id INT NOT NULL,
  PRIMARY KEY (user_id, forumpost_id),

  FOREIGN KEY (user_id)
    REFERENCES "user"(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  FOREIGN KEY (forumpost_id)
    REFERENCES forumpost(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE forumpost_comment (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  forumpost_id INT NOT NULL,
  parent_comment_id INT CHECK (parent_comment_id IS NULL OR parent_comment_id != id),
  content TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),

  FOREIGN KEY (user_id)
    REFERENCES "user"(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  FOREIGN KEY (forumpost_id)
    REFERENCES forumpost(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  FOREIGN KEY (parent_comment_id)
    REFERENCES forumpost_comment(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

-- ===== USER-RECIPE ACTIONS =====
CREATE TABLE recipe_favorite (
  user_id INT NOT NULL,
  recipe_id INT NOT NULL,
  PRIMARY KEY (user_id, recipe_id),

  FOREIGN KEY (user_id)
    REFERENCES "user"(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  FOREIGN KEY (recipe_id)
    REFERENCES recipe(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE recipe_attempt (
  user_id INT NOT NULL,
  recipe_id INT NOT NULL,
  attempted_at TIMESTAMP DEFAULT NOW(),

  PRIMARY KEY (user_id, recipe_id),

  FOREIGN KEY (user_id)
    REFERENCES "user"(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  FOREIGN KEY (recipe_id)
    REFERENCES recipe(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE recipe_review (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  recipe_id INT NOT NULL,
  rating INT CHECK (rating BETWEEN 1 AND 5),
  comment TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),

  UNIQUE (user_id, recipe_id),

  FOREIGN KEY (user_id, recipe_id)
    REFERENCES recipe_attempt(user_id, recipe_id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE recipe_view (
  user_id INT NOT NULL,
  recipe_id INT NOT NULL,
  viewed_at TIMESTAMP NOT NULL DEFAULT NOW(),

  PRIMARY KEY (user_id, recipe_id, viewed_at),

  FOREIGN KEY (user_id)
    REFERENCES "user"(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  FOREIGN KEY (recipe_id)
    REFERENCES recipe(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE recipe_dislike (
  user_id INT NOT NULL,
  recipe_id INT NOT NULL,
  PRIMARY KEY (user_id, recipe_id),

  FOREIGN KEY (user_id)
    REFERENCES "user"(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  FOREIGN KEY (recipe_id)
    REFERENCES recipe(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);