LogicAndPhysicalDeisgn: kreiranjeFinal2.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 PersIngrsIsOfType;
drop table if exists PersExerIsOfType;

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

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

create table PersIngrsIsOfType (
    iID bigint references Ingredient(iID), 
    piID bigint references PersonalizedIngredient(piID),

    constraint PersIngrsIsOfType_pkey primary key (piID, 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
);

create table PersExerIsOfType (
    eID bigint references Exercise(eID), 
    peID bigint references PersonalizedExercise(peID),

    constraint PersExerIsOfType_pkey primary key (peID, 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;
CREATE SEQUENCE _pers_ingrs_is_of_type_seq START 1;
CREATE SEQUENCE _pers_exer_is_of_type_seq START 1;