create_table.sql

-- Drop All tables and sequences if they exist
DROP TABLE IF EXISTS sets;
DROP TABLE IF EXISTS workouts;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS reviews;
DROP SEQUENCE if EXISTS workouts_id_seq;
DROP SEQUENCE if EXISTS sets_id_seq;
DROP TABLE IF EXISTS reviews_id_seq;

-- Create table Schema, without Foriegn keys to avoid conflict
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    password VARCHAR(255),
    pfp VARCHAR(255),
    isAdmin BOOLEAN
);

CREATE TABLE workouts (
    id SERIAL PRIMARY KEY,
    workout_date DATE,
    location VARCHAR(255),
    notes TEXT,
    time_start TIME,
    time_stop TIME CHECK (time_stop>=time_start),
    user_id INTEGER NOT NULL
);

CREATE TABLE sets (
    id SERIAL PRIMARY KEY,
    exercise_name TEXT CHECK (char_length(exercise_name) <= 100),
    --- exercise_name TEXT NOT NULL CHECK (char_length(exercise_name) <= 100),
    weight NUMERIC CHECK (weight >= 0),
    reps INTEGER CHECK (reps >= 0),
    sets INTEGER CHECK (sets >= 0),
    myo_rep_id INTEGER,
    failure BOOLEAN,
    notes TEXT,
    video_link TEXT,
    exercise_time TIME,
    workout_id INTEGER NOT NULL,
    exercise_type TEXT CHECK (char_length(exercise_type) <= 50)
    --- exercise_type TEXT NOT NULL CHECK (char_length(exercise_type) <= 50),
);

CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    title TEXT CHECK (char_length(title) <= 50),
    comment TEXT CHECK (char_length(comment) <= 1000),
    user_id INTEGER NOT NULL,
    workout_id INTEGER NOT NULL
);

-- Copy Data from CSVs
COPY workouts (id, workout_date, location, notes, time_start, time_stop, user_id)
FROM '/data/workouts.csv' 
DELIMITER ',' 
CSV HEADER;

COPY users (id, name, password, pfp, isAdmin)
FROM '/data/users.csv' 
DELIMITER ',' 
CSV HEADER;

COPY sets (id, exercise_name, weight, reps, sets, myo_rep_id, failure, notes, video_link, exercise_time, workout_id, exercise_type) 
FROM '/data/sets.csv' 
DELIMITER ',' 
CSV HEADER;

COPY reviews (id, title, comment, user_id, workout_id) 
FROM '/data/reviews.csv' 
DELIMITER ',' 
CSV HEADER;

-- Include Forgeign Keys now
ALTER TABLE workouts ADD CONSTRAINT fk_workouts_user 
    FOREIGN KEY (user_id) REFERENCES users(id);

ALTER TABLE sets ADD CONSTRAINT fk_sets_workout 
    FOREIGN KEY (workout_id) REFERENCES workouts(id);

ALTER TABLE reviews ADD CONSTRAINT fk_reviews_workout
    FOREIGN KEY (workout_id) REFERENCES workouts(id);

ALTER TABLE reviews ADD CONSTRAINT fk_reviews_user
    FOREIGN KEY (user_id) REFERENCES users(id);

-- This will create the sequences, match it to the current max id, and link it to the desired tables 
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));
SELECT setval('workouts_id_seq', (SELECT MAX(id) FROM workouts));
SELECT setval('sets_id_seq', (SELECT MAX(id) FROM sets));
SELECT setval('reviews_id_seq', (SELECT MAX(id) FROM reviews));