-- PostgreSQL schema for Gym Exercise Directory

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

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

DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'difficulty_level') THEN
    CREATE TYPE difficulty_level AS ENUM ('Beginner', 'Intermediate', 'Advanced');
  END IF;
END $$;

CREATE TABLE IF NOT EXISTS exercises (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  muscle_group_id BIGINT NOT NULL REFERENCES muscle_groups(id) ON DELETE RESTRICT,
  equipment_id BIGINT NOT NULL REFERENCES equipment(id) ON DELETE RESTRICT,
  difficulty difficulty_level NOT NULL,
  instructions TEXT[] NOT NULL,
  video_url TEXT,
  target_images_json JSONB
);

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);