-- SQLite schema for Gym Exercise Directory
-- Compatible with PostgreSQL conceptually (types differ)

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS muscle_groups (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  slug TEXT NOT NULL UNIQUE,
  description TEXT,
  image_url TEXT
);

CREATE TABLE IF NOT EXISTS equipment (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  slug TEXT NOT NULL UNIQUE
);

-- Store "instructions" as JSON text array for SQLite.
-- Store "target_images_json" as JSON text.
CREATE TABLE IF NOT EXISTS exercises (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  muscle_group_id INTEGER NOT NULL,
  equipment_id INTEGER NOT NULL,
  difficulty TEXT NOT NULL CHECK (difficulty IN ('Beginner', 'Intermediate', 'Advanced')),
  instructions_json TEXT NOT NULL,
  video_url TEXT,
  target_images_json TEXT,
  FOREIGN KEY (muscle_group_id) REFERENCES muscle_groups(id) ON DELETE RESTRICT,
  FOREIGN KEY (equipment_id) REFERENCES equipment(id) ON DELETE RESTRICT
);

CREATE INDEX IF NOT EXISTS idx_muscle_slug ON muscle_groups(slug);
CREATE INDEX IF NOT EXISTS idx_equipment_slug ON equipment(slug);
CREATE INDEX IF NOT EXISTS idx_exercises_muscle ON exercises(muscle_group_id);
CREATE INDEX IF NOT EXISTS idx_exercises_slug ON exercises(slug);