Mock setup for CrossFit workout database

training.sql

-- DOWN
DROP SCHEMA public CASCADE;

-- UP
CREATE SCHEMA public;

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

-- Equipment enum
CREATE TYPE equipment as enum (
  'Barbell',
  'Dumbbell',
  'Kettlebell',
  'Rower',
  'SkiErg',
  'Bodyweight'
);

ALTER TYPE equipment ADD VALUE 'BulgarianBag';

-- WOD table
CREATE TABLE wod (
  id          serial primary key,
  created_at  timestamp not null DEFAULT NOW(),
  updated_at  timestamp not null DEFAULT NOW()
);

-- Exercise table
CREATE TABLE exercise (
  id         serial primary key,
  name       text not null,
  equipment  equipment not null,
  UNIQUE (name, equipment)
);

-- Sets table
CREATE TABLE exercise_sets (
  id           serial primary key,
  exercise_id  int not null references exercise(id),
  reps         int not null,
  weight       int not null
);

ALTER TABLE exercise_sets ALTER COLUMN weight TYPE float;

-- WOD/Exercise table
CREATE TABLE wod_exercise (
  id            serial primary key,
  wod_id        int not null references wod(id),
  exercise_id   int not null references exercise(id)
);

-- INSERT DATA
INSERT INTO wod DEFAULT VALUES;

INSERT INTO exercise (name, equipment) VALUES
  ('Hang Power Clean', 'Barbell'),
  ('Deadlift', 'Barbell'),
  ('Sumo deadlift high pull', 'Barbell'),
  ('Push press', 'Barbell'),
  ('Thruster', 'Barbell'),
  ('Burpee over bar', 'Bodyweight')
ON CONFLICT DO NOTHING;

INSERT INTO exercise (name, equipment) VALUES
  ('Power Clean', 'Barbell'),
  ('SkiErg', 'SkiErg'),
  ('Row', 'Rower'),
  ('Hang Power Snatch', 'Dumbbell'),
  ('One Side Bulgarian Bag Squat', 'BulgarianBag'),
  ('Russian Kettlebell Swing', 'Kettlebell')
 ON CONFLICT DO NOTHING;
 
INSERT INTO exercise (name, equipment) VALUES
  ('Squat Snatch', 'Barbell')
ON CONFLICT DO NOTHING;


INSERT INTO exercise_sets (exercise_id, reps, weight) VALUES
  (19, '5', NULL),
  (8, '10', '40'),
  (6, '15', '40'),
  (7, '20', '40'),
  (1, '25', '40'),
  (2, '30', '60');
  
INSERT INTO exercise_sets (exercise_id, reps, weight) VALUES
  (26, 3, 30),
  (26, 3, 40),
  (26, 3, 42.5),
  (26, 3, 45),
  (26, 3, 47.5);
  
INSERT INTO exercise_sets (exercise_id, reps, weight) VALUES
  (21, 10, NULL),
  (22, 20, NULL),
  (23, 30, 17.5),
  (24, 40, 10),
  (25, 50, 24);
    
--DELETE FROM wod_exercise;
  
INSERT INTO wod_exercise (wod_id, exercise_id) VALUES
  (1, 19),
  (1, 8),
  (1, 19),
  (1, 6),
  (1, 19),
  (1, 7),
  (1, 19),
  (1, 1),
  (1, 19),
  (1, 2),
  (1, 19);

INSERT INTO wod_exercise (wod_id, exercise_id) VALUES
  (2, 26);
  
INSERT INTO wod_exercise (wod_id, exercise_id) VALUES
  (2, 21),
  (2, 22),
  (2, 23),
  (2, 24),
  (2, 25);
  
CREATE FUNCTION public.wod_total_weight (wod public.wod) RETURNS float AS $$
  SELECT
    SUM(COALESCE(es.weight,0) * COALESCE(es.reps,0))
  FROM 
    wod_exercise AS we 
    LEFT JOIN exercise_sets AS es ON es.exercise_id = we.exercise_id
  WHERE 
    we.wod_id = wod.id;
$$ language sql stable;