-- MySQL / MariaDB schema for Gym Exercise Directory
-- Server: localhost:3306
-- Database: ypffecbo_gym

CREATE TABLE IF NOT EXISTS muscle_groups (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  description TEXT NULL,
  image_url TEXT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_muscle_name (name),
  UNIQUE KEY uq_muscle_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS equipment (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_equipment_name (name),
  UNIQUE KEY uq_equipment_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS exercises (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  muscle_group_id BIGINT UNSIGNED NOT NULL,
  equipment_id BIGINT UNSIGNED NOT NULL,
  difficulty ENUM('Beginner','Intermediate','Advanced') NOT NULL,
  instructions_json JSON NOT NULL,
  video_url TEXT NULL,
  target_images_json JSON NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_exercise_slug (slug),
  KEY idx_exercises_muscle (muscle_group_id),
  KEY idx_exercises_equipment (equipment_id),
  CONSTRAINT fk_exercises_muscle
    FOREIGN KEY (muscle_group_id) REFERENCES muscle_groups(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_exercises_equipment
    FOREIGN KEY (equipment_id) REFERENCES equipment(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;