-- Migration 067: Fahrzeug-Typen (Vehicle Types)
-- Dynamic vehicle type table with many-to-many junction to fahrzeuge.
-- Seeds initial types from existing fahrzeuge.typ_schluessel values.

-- ═══════════════════════════════════════════════════════════════════════════
-- 1. Fahrzeug-Typen
-- ═══════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS fahrzeug_typen (
  id              SERIAL       PRIMARY KEY,
  name            VARCHAR(100) NOT NULL UNIQUE,
  beschreibung    TEXT,
  icon            VARCHAR(50),
  created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- ═══════════════════════════════════════════════════════════════════════════
-- 2. Junction table (many-to-many)
-- ═══════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS fahrzeug_fahrzeug_typen (
  fahrzeug_id     UUID         NOT NULL REFERENCES fahrzeuge(id) ON DELETE CASCADE,
  fahrzeug_typ_id INT          NOT NULL REFERENCES fahrzeug_typen(id) ON DELETE CASCADE,
  PRIMARY KEY (fahrzeug_id, fahrzeug_typ_id)
);

CREATE INDEX IF NOT EXISTS idx_fft_fahrzeug_id ON fahrzeug_fahrzeug_typen(fahrzeug_id);
CREATE INDEX IF NOT EXISTS idx_fft_typ_id ON fahrzeug_fahrzeug_typen(fahrzeug_typ_id);

-- ═══════════════════════════════════════════════════════════════════════════
-- 3. Seed types from existing typ_schluessel values
-- ═══════════════════════════════════════════════════════════════════════════

INSERT INTO fahrzeug_typen (name)
  SELECT DISTINCT typ_schluessel
  FROM fahrzeuge
  WHERE typ_schluessel IS NOT NULL AND typ_schluessel != ''
ON CONFLICT (name) DO NOTHING;

-- Populate junction table from existing assignments
INSERT INTO fahrzeug_fahrzeug_typen (fahrzeug_id, fahrzeug_typ_id)
  SELECT f.id, ft.id
  FROM fahrzeuge f
  JOIN fahrzeug_typen ft ON ft.name = f.typ_schluessel
  WHERE f.typ_schluessel IS NOT NULL AND f.typ_schluessel != ''
ON CONFLICT DO NOTHING;
