-- Migration 049: Add parent_id to categories for subcategory support
-- (048 already created the table without parent_id)

ALTER TABLE ausruestung_kategorien_katalog
  ADD COLUMN IF NOT EXISTS parent_id INT REFERENCES ausruestung_kategorien_katalog(id) ON DELETE CASCADE;

-- Unique: top-level categories by name
CREATE UNIQUE INDEX IF NOT EXISTS ausruestung_kat_top_unique
  ON ausruestung_kategorien_katalog (name) WHERE parent_id IS NULL;

-- Unique: subcategories by (parent_id, name)
CREATE UNIQUE INDEX IF NOT EXISTS ausruestung_kat_child_unique
  ON ausruestung_kategorien_katalog (parent_id, name) WHERE parent_id IS NOT NULL;
