-- Gym Tracker schema
-- Create a database first, then run this file.

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE workouts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  machine_name VARCHAR(120) NOT NULL,
  workout_type VARCHAR(50) NOT NULL,
  body_group ENUM('Legs','Arms','Chest','Cardio') NOT NULL,
  is_cardio TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uniq_workout (machine_name)
) ENGINE=InnoDB;

CREATE TABLE gym_sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ended_at DATETIME NULL,
  note VARCHAR(255) NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX (user_id, started_at)
) ENGINE=InnoDB;

CREATE TABLE session_exercises (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_id INT NOT NULL,
  workout_id INT NOT NULL,
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ended_at DATETIME NULL,
  FOREIGN KEY (session_id) REFERENCES gym_sessions(id) ON DELETE CASCADE,
  FOREIGN KEY (workout_id) REFERENCES workouts(id) ON DELETE RESTRICT,
  INDEX (session_id, workout_id, started_at)
) ENGINE=InnoDB;

CREATE TABLE exercise_sets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_exercise_id INT NOT NULL,
  set_no INT NOT NULL,
  set_type ENUM('strength','cardio') NOT NULL,
  reps INT NULL,
  weight_kg DECIMAL(6,2) NULL,
  distance_km DECIMAL(6,2) NULL,
  avg_speed_kph DECIMAL(6,2) NULL,
  duration_seconds INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (session_exercise_id) REFERENCES session_exercises(id) ON DELETE CASCADE,
  UNIQUE KEY uniq_set (session_exercise_id, set_no)
) ENGINE=InnoDB;

-- Seed some example workouts
INSERT INTO workouts (machine_name, workout_type, body_group, is_cardio) VALUES
('Leg Press', 'Strength', 'Legs', 0),
('Hamstring Curl', 'Strength', 'Legs', 0),
('Chest Press', 'Strength', 'Chest', 0),
('Pec Deck', 'Strength', 'Chest', 0),
('Lat Pulldown', 'Strength', 'Arms', 0),
('Bicep Curl Machine', 'Strength', 'Arms', 0),
('Treadmill', 'Cardio', 'Cardio', 1),
('Exercise Bike', 'Cardio', 'Cardio', 1);
