-- Migration 048: Catalog categories table + item characteristics

-- 1. Categories table (with parent_id for subcategories)
CREATE TABLE IF NOT EXISTS ausruestung_kategorien_katalog (
  id         SERIAL PRIMARY KEY,
  name       TEXT NOT NULL,
  parent_id  INT REFERENCES ausruestung_kategorien_katalog(id) ON DELETE CASCADE,
  erstellt_am TIMESTAMPTZ DEFAULT NOW()
);

-- Unique: top-level categories by name (where parent_id IS NULL)
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;

-- Migrate existing categories from free-text
INSERT INTO ausruestung_kategorien_katalog (name)
SELECT DISTINCT kategorie FROM ausruestung_artikel WHERE kategorie IS NOT NULL AND kategorie != ''
ON CONFLICT DO NOTHING;

-- Add kategorie_id FK to artikel
ALTER TABLE ausruestung_artikel ADD COLUMN IF NOT EXISTS kategorie_id INT REFERENCES ausruestung_kategorien_katalog(id) ON DELETE SET NULL;

-- Populate kategorie_id from existing text values
UPDATE ausruestung_artikel a
SET kategorie_id = k.id
FROM ausruestung_kategorien_katalog k
WHERE k.name = a.kategorie AND a.kategorie_id IS NULL AND k.parent_id IS NULL;

-- 2. Characteristics definitions per catalog item
CREATE TABLE IF NOT EXISTS ausruestung_artikel_eigenschaften (
  id          SERIAL PRIMARY KEY,
  artikel_id  INT NOT NULL REFERENCES ausruestung_artikel(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  typ         TEXT NOT NULL DEFAULT 'options',
  optionen    TEXT[],
  pflicht     BOOLEAN DEFAULT FALSE,
  sort_order  INT DEFAULT 0,
  UNIQUE(artikel_id, name)
);

-- 3. Characteristic values filled per request position
CREATE TABLE IF NOT EXISTS ausruestung_position_eigenschaften (
  id              SERIAL PRIMARY KEY,
  position_id     INT NOT NULL REFERENCES ausruestung_anfrage_positionen(id) ON DELETE CASCADE,
  eigenschaft_id  INT NOT NULL REFERENCES ausruestung_artikel_eigenschaften(id) ON DELETE CASCADE,
  wert            TEXT NOT NULL,
  UNIQUE(position_id, eigenschaft_id)
);

-- 4. Add manage_categories permission
INSERT INTO permissions (id, feature_group_id, label, description, sort_order)
VALUES ('ausruestungsanfrage:manage_categories', 'ausruestungsanfrage', 'Kategorien verwalten', 'Katalog-Kategorien erstellen und bearbeiten', 5)
ON CONFLICT (id) DO NOTHING;

-- Grant manage_categories to groups that have manage_catalog
INSERT INTO group_permissions (authentik_group, permission_id)
SELECT gp.authentik_group, 'ausruestungsanfrage:manage_categories'
FROM group_permissions gp
WHERE gp.permission_id = 'ausruestungsanfrage:manage_catalog'
ON CONFLICT DO NOTHING;

-- 5. Remove view_all permission (approve covers the "Alle Anfragen" tab now)
DELETE FROM group_permissions WHERE permission_id = 'ausruestungsanfrage:view_all';
DELETE FROM permissions WHERE id = 'ausruestungsanfrage:view_all';
