gravl/db/init.sql

-- Gravl Database Schema

-- Users table
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  gender VARCHAR(10),
  age INT,
  weight DECIMAL(5,1),
  neck_cm DECIMAL(4,1),
  waist_cm DECIMAL(4,1),
  hip_cm DECIMAL(4,1),
  experience_level VARCHAR(20),
  bench_1rm DECIMAL(5,1),
  squat_1rm DECIMAL(5,1),
  deadlift_1rm DECIMAL(5,1),
  goal VARCHAR(30),
  workouts_per_week INT,
  onboarding_complete BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Programs table
CREATE TABLE IF NOT EXISTS programs (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    weeks INTEGER DEFAULT 6,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Program days (e.g., Push, Pull, Legs)
CREATE TABLE IF NOT EXISTS program_days (
    id SERIAL PRIMARY KEY,
    program_id INTEGER REFERENCES programs(id) ON DELETE CASCADE,
    day_number INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Exercises master table
CREATE TABLE IF NOT EXISTS exercises (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    muscle_group VARCHAR(100),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Program exercises (which exercises on which day)
CREATE TABLE IF NOT EXISTS program_exercises (
    id SERIAL PRIMARY KEY,
    program_day_id INTEGER REFERENCES program_days(id) ON DELETE CASCADE,
    exercise_id INTEGER REFERENCES exercises(id) ON DELETE CASCADE,
    sets INTEGER DEFAULT 3,
    reps_min INTEGER DEFAULT 8,
    reps_max INTEGER DEFAULT 12,
    order_num INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Workout logs
CREATE TABLE IF NOT EXISTS workout_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER DEFAULT 1,
    program_exercise_id INTEGER REFERENCES program_exercises(id) ON DELETE CASCADE,
    date DATE NOT NULL,
    set_number INTEGER NOT NULL,
    weight DECIMAL(10,2),
    reps INTEGER,
    completed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_workout_logs_user_date ON workout_logs(user_id, date);
CREATE INDEX IF NOT EXISTS idx_workout_logs_exercise ON workout_logs(program_exercise_id);
CREATE INDEX IF NOT EXISTS idx_program_days_program ON program_days(program_id);
CREATE INDEX IF NOT EXISTS idx_program_exercises_day ON program_exercises(program_day_id);

-- Insert PPL Program
INSERT INTO programs (name, description, weeks) VALUES 
('Push/Pull/Legs', 'Classic 6-day PPL split for strength and hypertrophy. 6-week progressive program.', 6)
ON CONFLICT DO NOTHING;

-- Insert exercises
INSERT INTO exercises (name, muscle_group, description) VALUES
-- Push exercises
('Bench Press', 'Chest', 'Barbell bench press - main chest compound'),
('Overhead Press', 'Shoulders', 'Standing barbell overhead press'),
('Incline Dumbbell Press', 'Chest', 'Incline dumbbell press for upper chest'),
('Lateral Raises', 'Shoulders', 'Dumbbell lateral raises for side delts'),
('Tricep Pushdowns', 'Triceps', 'Cable tricep pushdowns'),
('Overhead Tricep Extension', 'Triceps', 'Cable or dumbbell overhead extension'),

-- Pull exercises
('Deadlift', 'Back', 'Conventional deadlift - main posterior chain compound'),
('Barbell Rows', 'Back', 'Bent over barbell rows'),
('Pull-ups', 'Back', 'Bodyweight or weighted pull-ups'),
('Face Pulls', 'Rear Delts', 'Cable face pulls for rear delts and rotator cuff'),
('Barbell Curls', 'Biceps', 'Standing barbell curls'),
('Hammer Curls', 'Biceps', 'Dumbbell hammer curls'),

-- Legs exercises
('Squat', 'Quads', 'Barbell back squat - main leg compound'),
('Romanian Deadlift', 'Hamstrings', 'RDL for hamstrings and glutes'),
('Leg Press', 'Quads', 'Machine leg press'),
('Leg Curls', 'Hamstrings', 'Lying or seated leg curls'),
('Calf Raises', 'Calves', 'Standing or seated calf raises'),
('Walking Lunges', 'Quads', 'Dumbbell walking lunges')
ON CONFLICT DO NOTHING;

-- Insert program days for PPL
INSERT INTO program_days (program_id, day_number, name) VALUES
(1, 1, 'Push A'),
(1, 2, 'Pull A'),
(1, 3, 'Legs A'),
(1, 4, 'Push B'),
(1, 5, 'Pull B'),
(1, 6, 'Legs B')
ON CONFLICT DO NOTHING;

-- Insert program exercises
-- Push A (day 1)
INSERT INTO program_exercises (program_day_id, exercise_id, sets, reps_min, reps_max, order_num) VALUES
(1, 1, 4, 6, 8, 1),   -- Bench Press 4x6-8
(1, 2, 3, 8, 10, 2),  -- OHP 3x8-10
(1, 3, 3, 10, 12, 3), -- Incline DB Press 3x10-12
(1, 4, 3, 12, 15, 4), -- Lateral Raises 3x12-15
(1, 5, 3, 10, 12, 5), -- Tricep Pushdowns 3x10-12
(1, 6, 3, 10, 12, 6)  -- Overhead Extension 3x10-12
ON CONFLICT DO NOTHING;

-- Pull A (day 2)
INSERT INTO program_exercises (program_day_id, exercise_id, sets, reps_min, reps_max, order_num) VALUES
(2, 7, 4, 5, 6, 1),   -- Deadlift 4x5-6
(2, 8, 4, 8, 10, 2),  -- Barbell Rows 4x8-10
(2, 9, 3, 6, 10, 3),  -- Pull-ups 3x6-10
(2, 10, 3, 15, 20, 4), -- Face Pulls 3x15-20
(2, 11, 3, 10, 12, 5), -- Barbell Curls 3x10-12
(2, 12, 3, 10, 12, 6)  -- Hammer Curls 3x10-12
ON CONFLICT DO NOTHING;

-- Legs A (day 3)
INSERT INTO program_exercises (program_day_id, exercise_id, sets, reps_min, reps_max, order_num) VALUES
(3, 13, 4, 6, 8, 1),  -- Squat 4x6-8
(3, 14, 3, 10, 12, 2), -- RDL 3x10-12
(3, 15, 3, 10, 12, 3), -- Leg Press 3x10-12
(3, 16, 3, 12, 15, 4), -- Leg Curls 3x12-15
(3, 17, 4, 12, 15, 5)  -- Calf Raises 4x12-15
ON CONFLICT DO NOTHING;

-- Push B (day 4)
INSERT INTO program_exercises (program_day_id, exercise_id, sets, reps_min, reps_max, order_num) VALUES
(4, 2, 4, 6, 8, 1),   -- OHP 4x6-8 (main lift)
(4, 1, 3, 8, 10, 2),  -- Bench Press 3x8-10
(4, 3, 3, 10, 12, 3), -- Incline DB Press 3x10-12
(4, 4, 4, 12, 15, 4), -- Lateral Raises 4x12-15
(4, 5, 3, 10, 12, 5), -- Tricep Pushdowns 3x10-12
(4, 6, 3, 10, 12, 6)  -- Overhead Extension 3x10-12
ON CONFLICT DO NOTHING;

-- Pull B (day 5)
INSERT INTO program_exercises (program_day_id, exercise_id, sets, reps_min, reps_max, order_num) VALUES
(5, 8, 4, 6, 8, 1),   -- Barbell Rows 4x6-8 (main lift)
(5, 9, 4, 6, 10, 2),  -- Pull-ups 4x6-10
(5, 7, 3, 8, 10, 3),  -- Deadlift 3x8-10 (lighter)
(5, 10, 3, 15, 20, 4), -- Face Pulls 3x15-20
(5, 11, 4, 10, 12, 5), -- Barbell Curls 4x10-12
(5, 12, 3, 10, 12, 6)  -- Hammer Curls 3x10-12
ON CONFLICT DO NOTHING;

-- Legs B (day 6)
INSERT INTO program_exercises (program_day_id, exercise_id, sets, reps_min, reps_max, order_num) VALUES
(6, 13, 3, 8, 10, 1),  -- Squat 3x8-10
(6, 14, 4, 8, 10, 2),  -- RDL 4x8-10 (main lift)
(6, 18, 3, 10, 12, 3), -- Walking Lunges 3x10-12
(6, 16, 4, 10, 12, 4), -- Leg Curls 4x10-12
(6, 17, 4, 12, 15, 5)  -- Calf Raises 4x12-15
ON CONFLICT DO NOTHING;