-- Add migration script here
CREATE TYPE exercise_variant AS ENUM ('time', 'number', 'failure');

CREATE TABLE exercises (
    exercise_id uuid PRIMARY KEY,
    name varchar NOT NULL,
    description varchar NOT NULL,
    created_at TIMESTAMP DEFAULT now(),
    updated_at TIMESTAMP DEFAULT now()
);

CREATE TABLE muscles (
    muscle_id varchar(16) PRIMARY KEY,
    name varchar NOT NULL
);

CREATE TABLE categories (
    category_id varchar(16) PRIMARY KEY,
    name varchar NOT NULL
);

CREATE TABLE workouts (
    workout_id uuid PRIMARY KEY,
    name varchar NOT NULL,
    description varchar NOT NULL,
    created_at TIMESTAMP DEFAULT now(),
    updated_at TIMESTAMP DEFAULT now()
);

CREATE TABLE workout_exercises (
    workout_exercises_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    workout_id uuid NOT NULL,
    exercise_id uuid NOT NULL,
    exercise_type exercise_variant NOT NULL,
    position integer NOT NULL,
    sets integer,
    reps integer,
    time integer,
    FOREIGN KEY (workout_id) REFERENCES workouts(workout_id) ON DELETE CASCADE,
    FOREIGN KEY (exercise_id) REFERENCES exercises(exercise_id) ON DELETE CASCADE
);

CREATE TABLE exercise_muscles (
    exercise_id uuid NOT NULL,
    muscle_id varchar(16) NOT NULL,
    PRIMARY KEY (exercise_id, muscle_id),
    FOREIGN KEY (exercise_id) REFERENCES exercises(exercise_id) ON DELETE CASCADE,
    FOREIGN KEY (muscle_id) REFERENCES muscles(muscle_id) ON DELETE CASCADE
);

CREATE TABLE exercise_categories (
    exercise_id uuid NOT NULL,
    category_id varchar(16) NOT NULL,
    PRIMARY KEY (exercise_id, category_id),
    FOREIGN KEY (exercise_id) REFERENCES exercises(exercise_id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE CASCADE
);


-- Initialize muscle data
INSERT INTO muscles (muscle_id, name) VALUES
('chest', 'Chest'),
('back', 'Back'),
('biceps', 'Biceps'),
('triceps', 'Triceps'),
('legs', 'Legs'),
('shoulders', 'Shoulders');

-- Initialize caregory data
INSERT INTO categories (category_id, name) VALUES
('strength', 'Strength'),
('cardio', 'Cardio');