-- Migration 074: Checklist multi-type assignment (junction tables)
-- Replaces single FK columns with M:N junction tables

-- 1. Create junction tables
CREATE TABLE IF NOT EXISTS checklist_vorlage_fahrzeug_typen (
  vorlage_id        INTEGER NOT NULL REFERENCES checklist_vorlagen(id) ON DELETE CASCADE,
  fahrzeug_typ_id   INTEGER NOT NULL REFERENCES fahrzeug_typen(id) ON DELETE CASCADE,
  PRIMARY KEY (vorlage_id, fahrzeug_typ_id)
);

CREATE TABLE IF NOT EXISTS checklist_vorlage_fahrzeuge (
  vorlage_id  INTEGER NOT NULL REFERENCES checklist_vorlagen(id) ON DELETE CASCADE,
  fahrzeug_id UUID    NOT NULL REFERENCES fahrzeuge(id) ON DELETE CASCADE,
  PRIMARY KEY (vorlage_id, fahrzeug_id)
);

CREATE TABLE IF NOT EXISTS checklist_vorlage_ausruestung_typen (
  vorlage_id          INTEGER NOT NULL REFERENCES checklist_vorlagen(id) ON DELETE CASCADE,
  ausruestung_typ_id  INTEGER NOT NULL REFERENCES ausruestung_typen(id) ON DELETE CASCADE,
  PRIMARY KEY (vorlage_id, ausruestung_typ_id)
);

CREATE TABLE IF NOT EXISTS checklist_vorlage_ausruestung (
  vorlage_id    INTEGER NOT NULL REFERENCES checklist_vorlagen(id) ON DELETE CASCADE,
  ausruestung_id UUID   NOT NULL REFERENCES ausruestung(id) ON DELETE CASCADE,
  PRIMARY KEY (vorlage_id, ausruestung_id)
);

-- 2. Migrate existing single-FK data into junction tables
-- (only if the old columns exist — safe with DO $$ blocks)
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='checklist_vorlagen' AND column_name='fahrzeug_typ_id') THEN
    INSERT INTO checklist_vorlage_fahrzeug_typen (vorlage_id, fahrzeug_typ_id)
      SELECT id, fahrzeug_typ_id FROM checklist_vorlagen WHERE fahrzeug_typ_id IS NOT NULL
      ON CONFLICT DO NOTHING;
  END IF;
  IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='checklist_vorlagen' AND column_name='fahrzeug_id') THEN
    INSERT INTO checklist_vorlage_fahrzeuge (vorlage_id, fahrzeug_id)
      SELECT id, fahrzeug_id FROM checklist_vorlagen WHERE fahrzeug_id IS NOT NULL
      ON CONFLICT DO NOTHING;
  END IF;
  IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='checklist_vorlagen' AND column_name='ausruestung_typ_id') THEN
    INSERT INTO checklist_vorlage_ausruestung_typen (vorlage_id, ausruestung_typ_id)
      SELECT id, ausruestung_typ_id FROM checklist_vorlagen WHERE ausruestung_typ_id IS NOT NULL
      ON CONFLICT DO NOTHING;
  END IF;
  IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='checklist_vorlagen' AND column_name='ausruestung_id') THEN
    INSERT INTO checklist_vorlage_ausruestung (vorlage_id, ausruestung_id)
      SELECT id, ausruestung_id FROM checklist_vorlagen WHERE ausruestung_id IS NOT NULL
      ON CONFLICT DO NOTHING;
  END IF;
END $$;

-- 3. Drop old FK columns (use DO $$ for safety)
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='checklist_vorlagen' AND column_name='fahrzeug_typ_id') THEN
    ALTER TABLE checklist_vorlagen DROP COLUMN fahrzeug_typ_id;
  END IF;
  IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='checklist_vorlagen' AND column_name='fahrzeug_id') THEN
    ALTER TABLE checklist_vorlagen DROP COLUMN fahrzeug_id;
  END IF;
  IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='checklist_vorlagen' AND column_name='ausruestung_typ_id') THEN
    ALTER TABLE checklist_vorlagen DROP COLUMN ausruestung_typ_id;
  END IF;
  IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='checklist_vorlagen' AND column_name='ausruestung_id') THEN
    ALTER TABLE checklist_vorlagen DROP COLUMN ausruestung_id;
  END IF;
END $$;
