text: DROP DATABASE IF EXISTS foodfy;
  CREATE DATABASE foodfy;
  
  
  -- recipes table
  CREATE TABLE "recipes" (
    "id" SERIAL PRIMARY KEY,
    "chef_id" int DEFAULT 0,
    "user_id" int NOT NULL,
    "title" text NOT NULL,
    "ingredients" text[],
    "preparation" text[],
    "information" text,
    "created_at" timestamp DEFAULT(now()),
    "updated_at" timestamp DEFAULT(now())
  );
  
  -- chefs table
  CREATE TABLE "chefs" (
    "id" SERIAL PRIMARY KEY,
    "name" text NOT NULL,
    "created_at" timestamp DEFAULT(now())
  );
  
  -- chef_files table
  CREATE TABLE "chef_files" (
    "id" SERIAL PRIMARY KEY,
    "chef_id" int NOT NULL,
    "file_id" int NOT NULL
  );
  
  -- recipe_files table
  CREATE TABLE "recipe_files" (
    "id" SERIAL PRIMARY KEY,
    "recipe_id" int NOT NULL,
    "file_id" int NOT NULL
  );
  
  -- files table
  CREATE TABLE "files" (
    "id" SERIAL PRIMARY KEY,
    "name" text NOT NULL,
    "path" text NOT NULL
  );
  
  -- users table
  CREATE TABLE "users" (
    "id" SERIAL PRIMARY KEY,
    "name" TEXT NOT NULL,
    "email" TEXT UNIQUE NOT NULL,
    "password" TEXT NOT NULL,
    "reset_token" TEXT,
    "reset_token_expires" TEXT,
    "is_admin" BOOLEAN DEFAULT false,
    "is_verified" BOOLEAN DEFAULT false,
    "created_at" TIMESTAMP DEFAULT(now()),
  	"updated_at" TIMESTAMP DEFAULT(now())
  );
  
  -- User Session table
  CREATE TABLE "session" (
    "sid" varchar NOT NULL COLLATE "default",
    "sess" json NOT NULL,
    "expire" timestamp(6) NOT NULL
  )
  WITH (OIDS=FALSE);
  ALTER TABLE "session" ADD CONSTRAINT "session_pkey" PRIMARY KEY ("sid") NOT DEFERRABLE INITIALLY IMMEDIATE;
  
  CREATE INDEX "IDX_session_expire" ON "session" ("expire");
  
  -- Relate tables
  ALTER TABLE "recipes" ADD FOREIGN KEY ("chef_id") REFERENCES "chefs" ("id");
  ALTER TABLE "recipes" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");
  
  ALTER TABLE "chef_files" ADD FOREIGN KEY ("chef_id") REFERENCES "chefs" ("id");
  ALTER TABLE "chef_files" ADD FOREIGN KEY ("file_id") REFERENCES "files" ("id");
  
  ALTER TABLE "recipe_files" ADD FOREIGN KEY ("recipe_id") REFERENCES "recipes" ("id");
  ALTER TABLE "recipe_files" ADD FOREIGN KEY ("file_id") REFERENCES "files" ("id");
  
  -- Create procedure
  CREATE FUNCTION trigger_set_timestamp()
  RETURNS TRIGGER AS $$
  BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  
  -- Auto updated_at users
  CREATE TRIGGER set_timestamp_users
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_set_timestamp();
  
  -- Auto updated_at users
  CREATE TRIGGER set_timestamp_recipes
  BEFORE UPDATE ON recipes
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_set_timestamp();
  
  
  ALTER TABLE "recipe_files"
  DROP CONSTRAINT recipe_files_file_id_fkey,
  ADD CONSTRAINT recipe_files_file_id_fkey
  FOREIGN KEY ("file_id")
  REFERENCES "files" ("id")
  ON DELETE CASCADE;
  
  ALTER TABLE "chef_files"
  DROP CONSTRAINT chef_files_file_id_fkey,
  ADD CONSTRAINT chef_files_file_id_fkey
  FOREIGN KEY ("file_id")
  REFERENCES "files" ("id")
  ON DELETE CASCADE;
  
  -- Populate Chefs table with standart Chef
  INSERT INTO chefs(name) VALUES ('Foodfy');
  INSERT INTO files(name, path) VALUES ('Foodfy_avatar', 'public/img/foodfy-avatar.png');
  INSERT INTO chef_files(chef_id, file_id) VALUES ('1', '1');