drop schema if exists project cascade;
create schema project;
drop table if exists User_;
drop table if exists BuildMuscleProgram;
drop table if exists Workout;
drop table if exists WeightLossProgram;
drop table if exists Supplements;
drop table if exists LoyalityCardUser;
drop table if exists NoLoyalityCardUser;
drop table if exists Program;
drop table if exists MealPlan;
drop table if exists Payment;

-- User_ Table
CREATE TABLE User_ (
    user_id SERIAL PRIMARY KEY,
    name_surname TEXT,
    email TEXT,
    weight NUMERIC,
    height NUMERIC,
    address TEXT,
    tel_number NUMERIC,
    account_number NUMERIC,
    card_number NUMERIC,
    password TEXT
);

-- Program Table
CREATE TABLE Program (
    program_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES User_(user_id),
    program_name TEXT
);

-- Weight Loss Program Table
CREATE TABLE Weight_Loss_Program (
    wlp_id SERIAL PRIMARY KEY,
    program_id INT REFERENCES Program(program_id)
);

-- Meal Plan Table
CREATE TABLE Meal_Plan (
    meal_id SERIAL PRIMARY KEY,
    wlp_id INT REFERENCES Weight_Loss_Program(wlp_id),
    calories NUMERIC
);

-- Build Muscles Program Table
CREATE TABLE Build_Muscles_Program (
    bmp_id SERIAL PRIMARY KEY,
    program_id INT REFERENCES Program(program_id),
    meal TEXT
);

-- Workout Table
CREATE TABLE Workout (
    w_id SERIAL PRIMARY KEY,
    bmp_id INT REFERENCES Build_Muscles_Program(bmp_id),
    name TEXT
);

-- Supplements Table
CREATE TABLE Supplements (
    supplements_id SERIAL PRIMARY KEY,
    program_id INT REFERENCES Program(program_id),
    vitamins_minerals TEXT,
    aminos TEXT,
    preworkouts TEXT,
    creatine TEXT,
    proteins TEXT,
    fat_burners TEXT
);

-- Payment Table
CREATE TABLE Payment (
    payment_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES User_(user_id),
    loyalty_card_number INT REFERENCES Loyalty_Card_User(loyalty_card_number),
    ordinal_user INT REFERENCES No_Loyalty_Card_User(ordinal_user),
    payment_date DATE
);

-- Loyalty Card User Table
CREATE TABLE Loyalty_Card_User (
    loyalty_card_number SERIAL PRIMARY KEY,
    user_id INT REFERENCES User_(user_id),
    price NUMERIC,
    discount NUMERIC
);

-- No Loyalty Card User Table
CREATE TABLE No_Loyalty_Card_User (
    ordinal_user SERIAL PRIMARY KEY,
    user_id INT REFERENCES User_(user_id),
    price NUMERIC
);

