CREATE TABLE person

(person_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
 username VARCHAR(20),
 fname VARCHAR(20),
 lname VARCHAR(20),
 gender ENUM('M','F'),
 birth_date DATE,
 address VARCHAR(40),
 city VARCHAR(20),
 state VARCHAR(20),
 height SMALLINT UNSIGNED,
 CONSTRAINT uc_person UNIQUE (person_id),
 CONSTRAINT pk_person PRIMARY KEY (person_id)
 );



CREATE TABLE body_weight
( person_id SMALLINT UNSIGNED,
  entry_date DATE,
  weight SMALLINT UNSIGNED,
  CONSTRAINT pk_body_weight PRIMARY KEY (person_id, entry_date),
  CONSTRAINT fk_person_id FOREIGN KEY (person_id)
    REFERENCES person(person_id)
);

CREATE TABLE body_measurement
( person_id SMALLINT UNSIGNED,
  entry_date DATE,
  bicept SMALLINT UNSIGNED,
  chest SMALLINT UNSIGNED,
  abdomen SMALLINT UNSIGNED,
  waist SMALLINT UNSIGNED,
  thigh SMALLINT UNSIGNED,
  hip SMALLINT UNSIGNED,
  CONSTRAINT pk_body_measurement PRIMARY KEY (person_id, entry_date),
  CONSTRAINT  FOREIGN KEY (person_id)
    REFERENCES person(person_id)
);

CREATE TABLE body_weight_goal
( person_id SMALLINT UNSIGNED,
  entry_date DATE,
  goal_date  DATE,
  weight SMALLINT UNSIGNED,
  CONSTRAINT pk_body_weight_goal PRIMARY KEY (person_id, entry_date),
  CONSTRAINT  FOREIGN KEY (person_id)
    REFERENCES person(person_id)
);

CREATE TABLE body_fat
( person_id SMALLINT UNSIGNED,
  entry_date DATE,
  weight SMALLINT UNSIGNED,
  CONSTRAINT pk_body_fat PRIMARY KEY (person_id, entry_date),
  CONSTRAINT   FOREIGN KEY (person_id)
    REFERENCES person(person_id)
);

CREATE TABLE body_pulse
( person_id SMALLINT UNSIGNED,
  entry_date DATE,
  resting_pulse SMALLINT UNSIGNED,
  CONSTRAINT pk_body_pulse PRIMARY KEY (person_id, entry_date),
  CONSTRAINT  FOREIGN KEY (person_id)
    REFERENCES person(person_id)
);

CREATE TABLE workout
(
  workout_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  person_id SMALLINT UNSIGNED,
  entry_date DATE,
  completed BOOLEAN,
  CONSTRAINT uc_workout UNIQUE (person_id,entry_date),
  CONSTRAINT   PRIMARY KEY (workout_id),
  CONSTRAINT  FOREIGN KEY (person_id)
    REFERENCES person(person_id)
);

CREATE TABLE workout_note
( workout_id INT UNSIGNED,
  notes VARCHAR(40),
  CONSTRAINT pk_workout_note PRIMARY KEY (workout_id),
  CONSTRAINT  FOREIGN KEY (workout_id)
    REFERENCES workout(workout_id)
);

CREATE TABLE muscle_group
( group_id  SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  muscle_name VARCHAR(40),
  CONSTRAINT pk_muscle_group_id PRIMARY KEY (group_id )
);

CREATE TABLE exercise
( exercise_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  person_id SMALLINT UNSIGNED,
  /*  s = set and L = lap */
  name VARCHAR(40),
  ex_type ENUM('S','L'),
  
 
  CONSTRAINT pk_exercise PRIMARY KEY (exercise_id),
  CONSTRAINT  FOREIGN KEY (person_id)
    REFERENCES person(person_id)
 
);

CREATE TABLE exercise_muscle_group
 ( group_id SMALLINT UNSIGNED,
   exercise_id  INT UNSIGNED,
   primary_muscle BOOLEAN,
   CONSTRAINT pk_ex_muscle_grp PRIMARY KEY (group_id, exercise_id),
   CONSTRAINT  FOREIGN KEY (group_id )
    REFERENCES muscle_group(group_id),
    CONSTRAINT  FOREIGN KEY (exercise_id)
    REFERENCES exercise(exercise_id)
 );

CREATE TABLE exercise_note
( exercise_id INT UNSIGNED,
  notes VARCHAR(40),
  CONSTRAINT pk_exercise_note PRIMARY KEY (exercise_id),
  CONSTRAINT  FOREIGN KEY (exercise_id)
    REFERENCES exercise(exercise_id)
);

CREATE TABLE exercise_record
(
  exercise_record_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  workout_id INT UNSIGNED,
  exercise_id INT UNSIGNED,
  CONSTRAINT pk_exercise_record PRIMARY KEY (exercise_record_id),
  CONSTRAINT  FOREIGN KEY (workout_id)
    REFERENCES workout(workout_id),
  CONSTRAINT  FOREIGN KEY (exercise_id)
    REFERENCES exercise(exercise_id)
);

CREATE TABLE exercise_record_note
( exercise_record_id  INT UNSIGNED,
  notes VARCHAR(40),
  CONSTRAINT pk_exercise_record_note PRIMARY KEY (exercise_record_id),
  CONSTRAINT  FOREIGN KEY (exercise_record_id)
    REFERENCES exercise_record(exercise_record_id)
);

CREATE TABLE exercise_set
( exercise_record_id  INT UNSIGNED,
  set_number SMALLINT UNSIGNED NOT NULL,
  weight SMALLINT,
  reps SMALLINT,
  CONSTRAINT pk_set PRIMARY KEY (exercise_record_id, set_number),
  CONSTRAINT  FOREIGN KEY (exercise_record_id)
    REFERENCES exercise_record(exercise_record_id)
);

CREATE TABLE exercise_lap
( exercise_record_id  INT UNSIGNED,
  lap_number SMALLINT UNSIGNED NOT NULL,
  time INT,
  distance INT,
  CONSTRAINT pk_lap PRIMARY KEY (exercise_record_id, lap_number),
  CONSTRAINT  FOREIGN KEY (exercise_record_id)
    REFERENCES exercise_record(exercise_record_id)
);