Fitness and workout tracker

CREATE TABLE users (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    email text NOT NULL UNIQUE,
    display_name text NOT NULL
);

CREATE TABLE muscle_groups (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE exercises (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL UNIQUE,
    equipment text,
    primary_muscle_id uuid
);
CREATE INDEX exercises_primary_muscle_id_idx ON exercises (primary_muscle_id);

CREATE TABLE plans (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id uuid NOT NULL,
    name text NOT NULL
);
CREATE INDEX plans_user_id_idx ON plans (user_id);

CREATE TABLE plan_exercises (
    plan_id uuid NOT NULL,
    exercise_id uuid NOT NULL,
    position int NOT NULL,
    target_sets int DEFAULT 3 NOT NULL,
    target_reps int DEFAULT 10 NOT NULL,
    PRIMARY KEY (plan_id, exercise_id)
);
CREATE INDEX plan_exercises_exercise_id_idx ON plan_exercises (exercise_id);

CREATE TABLE sessions (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id uuid NOT NULL,
    plan_id uuid,
    started_at timestamptz DEFAULT now() NOT NULL,
    ended_at timestamptz,
    notes text
);
CREATE INDEX sessions_user_id_idx ON sessions (user_id);
CREATE INDEX sessions_plan_id_idx ON sessions (plan_id);

CREATE TABLE session_sets (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    session_id uuid NOT NULL,
    exercise_id uuid NOT NULL,
    set_number int NOT NULL,
    reps int NOT NULL,
    weight_kg numeric(6,2),
    UNIQUE (session_id, exercise_id, set_number)
);
CREATE INDEX session_sets_exercise_id_idx ON session_sets (exercise_id);

CREATE TABLE body_measurements (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id uuid NOT NULL,
    measured_on date NOT NULL,
    weight_kg numeric(5,2),
    body_fat_percent numeric(4,1),
    UNIQUE (user_id, measured_on)
);

ALTER TABLE exercises ADD FOREIGN KEY (primary_muscle_id) REFERENCES muscle_groups (id);

ALTER TABLE plans ADD FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE plan_exercises ADD FOREIGN KEY (plan_id) REFERENCES plans (id) ON DELETE CASCADE;

ALTER TABLE plan_exercises ADD FOREIGN KEY (exercise_id) REFERENCES exercises (id);

ALTER TABLE sessions ADD FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE sessions ADD FOREIGN KEY (plan_id) REFERENCES plans (id) ON DELETE SET NULL;

ALTER TABLE session_sets ADD FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE;

ALTER TABLE session_sets ADD FOREIGN KEY (exercise_id) REFERENCES exercises (id);

ALTER TABLE body_measurements ADD FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;