LogicAndPhysicalDeisgn: kreiranjeFinal.sql

set search_path = project;


drop table if exists UserHasProgram;
drop table if exists UserHasDays;
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 Meal;
drop table if exists Workout;
drop table if exists _Day cascade;
drop table if exists _User cascade;
drop table if exists PersonalizedIngredient;
drop table if exists Ingredient;
drop table if exists PersonalizedExercise;
drop table if exists Exercise;
drop table if exists WorkoutProgram;



drop schema if exists project cascade;

create schema project;

set search_path = project;



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

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

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

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

create table Ingredient (       
        iID bigint primary key, 
        name VARCHAR(255) 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 VARCHAR(255) not null, 
        type VARCHAR(255) not null
);
create table PersonalizedExercise (     
        peID 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 VARCHAR(255) not null
);



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

        constraint UserHasDays_pkey primary key (dID, uID)
); 

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

        constraint UserHasProgram_pkey primary key (wpID, uID)
); 

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), 
        piID bigint references PersonalizedIngredient(piID),

        constraint MealHasIngredient_pkey primary key (piID, mID)
); 

create table WorkoutHasExercise(
        peID bigint references PersonalizedExercise(peID), 
        wID bigint references Workout(wID),

        constraint WorkoutHasExercise_pkey primary key (peID, wID)
); 

CREATE SEQUENCE _user_seq START 1;
CREATE SEQUENCE _day_seq START 1;
CREATE SEQUENCE _workout_seq START 1;
CREATE SEQUENCE _meal_seq START 1;
CREATE SEQUENCE _ingredient_seq START 1;
CREATE SEQUENCE _personalized_ingredient_seq START 1;
CREATE SEQUENCE _exercise_seq START 1;
CREATE SEQUENCE _personalized_exercise_seq START 1;
CREATE SEQUENCE _workout_program_seq START 1;