LogicAndPhysicalDeisgn: kreiranje.sql

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)
);