# Dropping all tables first before creating them.
drop table RecipeIngredient;
drop table QuantityUnit;
drop table Recipe;
drop table CookMethod;
drop table Category;
drop table Ingredient;

# Create Ingredient tbl.
create table Ingredient(
ingredientId int generated always as identity,
name varchar(50) unique not null,
description varchar(150),
primary key (ingredientId)
);

# Create Category tbl.
create table Category(
categoryId int generated always as identity,
name varchar(50) unique not null,
primary key (categoryId)
);

# Create Cook Method tbl.
create table CookMethod(
cookMethodId int generated always as identity,
name varchar(50) unique not null,
primary key(cookMethodId)
);

# Create Recipe tbl.
# steps varchar(150) unique not null, - Old col definition.
create table Recipe(
recipeId int generated always as identity,
name varchar(50) unique not null,
source varchar(50),
difficulty int default 1 not null,
temperature double,
cookMethod int,
steps clob not null,
author varchar(100),
category int not null,
keyIngredient int not null,
primary key (recipeId),
foreign key (cookMethod) references CookMethod(cookMethodId),
foreign key (category) references Category(categoryId),
foreign key (keyIngredient) references Ingredient(ingredientId)
);

# Create QuantityUnit tbl.
create table QuantityUnit(
quantityUnitId int generated always as identity,
name varchar(50) unique not null,
unit varchar(20) unique not null,
primary key (quantityUnitId)
);

# Create RecipeIngredient tbl.
create table RecipeIngredient(
recipe int not null,
ingredient int not null,
quantity double not null,
quantityUnit int not null,
primary key (recipe, ingredient),
foreign key (ingredient) references Ingredient(ingredientId),
foreign key (recipe) references Recipe(recipeId),
foreign key (quantityUnit) references QuantityUnit(quantityUnitId)
);