set search_path = project;


drop table if exists UserHasProgram;
drop table if exists UserHasDay;
drop table if exists DayHasMeal;
drop table if exists MealHasIngredient;
drop table if exists DayHasWorkout;
drop table if exists WorkoutHasExercise;
drop table if exists WorkoutProgramHasWorkout;


drop table if exists _User;
drop table if exists _Day;
drop table if exists Meal;
drop table if exists Ingredient;
drop table if exists PersonalizedIngredient;
drop table if exists Workout;
drop table if exists Exercise;
drop table if exists PersonalizedExercise;
drop table if exists WorkoutProgram;



drop domain if exists string_dolg;
drop domain if exists string_kratok;


drop schema if exists project;

create schema project;

set search_path = project;


create domain string_dolg AS character varying(4000);
create domain string_kratok AS character varying(500);


create table _User (	
	uID bigint primary key, 
	name string_kratok not null unique,
	password string_kratok not null, 
	age integer not null, 
	height integer,
	weight integer,
	role string_kratok
);

create table _Day (	
	dID bigint primary key, 
	date date not null
);

create table Workout (	
	wID bigint primary key, 
	duration integer,
	name string_kratok,
	uID BIGINT,
    foreign key (uID) references _User(uID)
);

create table Meal (	
	mID bigint primary key, 
	name string_kratok not null,
	type string_kratok not null,
	uID BIGINT,
    foreign key (uID) references _User(uID)
);

create table Ingredient (	
	iID bigint primary key, 
	name string_kratok not null, 
	calories integer not null,
 	protein integer not null,
 	carbs integer not null,
	fats integer not null
);

create table PersonalizedIngredient (	
	piID bigint primary key, 
	quantity integer not null,
	iID bigint,
	foreign key (iID) references Ingredient(iID)
);

create table Exercise (	
	eID bigint primary key, 
	name string_kratok not null, 
	type string_kratok not null
);
create table PersonalizedExercise (	
	pID bigint primary key, 
	reps integer,
	sets integer,
	weight integer,
	time integer,
	eID bigint,
	foreign key (eID) references Exercise(eID)
);

create table WorkoutProgram (	
	wpID bigint primary key, 
	name string_kratok not null
);



create table UserHasDay(
	uID bigint references _User(uID), 
	dID bigint references _Day(dID),

	constraint UserHasDay_pkey primary key (dID)
); 

create table UserHasProgram(
	uID bigint references _User(uID), 
	wpID bigint references WorkoutProgram(wpID),

	constraint UserHasProgram_pkey primary key (wpID)
); 

create table DayHasWorkout(
	wID bigint references Workout(wID), 
	dID bigint references _Day(dID),

	constraint DayHasWorkout_pkey primary key (wID, dID)
); 

create table WorkoutProgramHasWorkout(
	wID bigint references Workout(wID), 
	wpID bigint references WorkoutProgram(wpID),

	constraint WorkoutProgramHasWorkout_pkey primary key (wpID, wID)
); 

create table DayHasMeal(
	mID bigint references Meal(mID), 
	dID bigint references _Day(dID),

	constraint DayHasMeal_pkey primary key (dID, mID)
); 


create table MealHasIngredient(
	mID bigint references Meal(mID), 
	iID bigint references Ingredient(iID),

	constraint MealHasIngredient_pkey primary key (iID, mID)
); 

create table WorkoutHasExercise(
	eID bigint references Exercise(eID), 
	wID bigint references Workout(wID),

	constraint WorkoutHasExercise_pkey primary key (eID, wID)
); 



