-- Enable pgcrypto for UUID generation if not already enabled
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Models table with required fields
CREATE TABLE IF NOT EXISTS models (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  short_description TEXT,
  description_html TEXT,
  seo_title TEXT,
  seo_description TEXT,
  author_name TEXT,
  gender TEXT,
  hair_color TEXT,
  height TEXT,
  location TEXT,
  featured BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Model images table
CREATE TABLE IF NOT EXISTS model_images (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  model_id UUID NOT NULL REFERENCES models(id) ON DELETE CASCADE,
  url TEXT NOT NULL,
  position INT DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Defensive alters in case table exists but columns do not
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name='models' AND column_name='description_html'
  ) THEN
    EXECUTE 'ALTER TABLE models ADD COLUMN description_html TEXT';
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name='models' AND column_name='short_description'
  ) THEN
    EXECUTE 'ALTER TABLE models ADD COLUMN short_description TEXT';
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name='models' AND column_name='seo_title'
  ) THEN
    EXECUTE 'ALTER TABLE models ADD COLUMN seo_title TEXT';
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name='models' AND column_name='seo_description'
  ) THEN
    EXECUTE 'ALTER TABLE models ADD COLUMN seo_description TEXT';
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name='models' AND column_name='author_name'
  ) THEN
    EXECUTE 'ALTER TABLE models ADD COLUMN author_name TEXT';
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name='models' AND column_name='gender'
  ) THEN
    EXECUTE 'ALTER TABLE models ADD COLUMN gender TEXT';
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name='models' AND column_name='hair_color'
  ) THEN
    EXECUTE 'ALTER TABLE models ADD COLUMN hair_color TEXT';
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name='models' AND column_name='height'
  ) THEN
    EXECUTE 'ALTER TABLE models ADD COLUMN height TEXT';
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name='models' AND column_name='location'
  ) THEN
    EXECUTE 'ALTER TABLE models ADD COLUMN location TEXT';
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name='models' AND column_name='featured'
  ) THEN
    EXECUTE 'ALTER TABLE models ADD COLUMN featured BOOLEAN DEFAULT FALSE';
  END IF;
END$$;
