-- Migration 068: Checklisten (Checklist system)
-- Templates, vehicle-specific items, execution records, and due date tracking.
-- Depends on: 067_fahrzeug_typen.sql (fahrzeug_typen table)

-- ═══════════════════════════════════════════════════════════════════════════
-- 1. Checklist-Vorlagen (Templates)
-- ═══════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS checklist_vorlagen (
  id              SERIAL       PRIMARY KEY,
  name            VARCHAR(200) NOT NULL,
  fahrzeug_typ_id INT          REFERENCES fahrzeug_typen(id) ON DELETE SET NULL,
  intervall       VARCHAR(20)  CHECK (intervall IN ('weekly','monthly','yearly','custom')),
  intervall_tage  INT,
  beschreibung    TEXT,
  aktiv           BOOLEAN      NOT NULL DEFAULT TRUE,
  created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- ═══════════════════════════════════════════════════════════════════════════
-- 2. Vorlage Items (Template line items)
-- ═══════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS checklist_vorlage_items (
  id              SERIAL       PRIMARY KEY,
  vorlage_id      INT          NOT NULL REFERENCES checklist_vorlagen(id) ON DELETE CASCADE,
  bezeichnung     VARCHAR(500) NOT NULL,
  beschreibung    TEXT,
  pflicht         BOOLEAN      NOT NULL DEFAULT TRUE,
  sort_order      INT          NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_cvi_vorlage_id ON checklist_vorlage_items(vorlage_id);

-- ═══════════════════════════════════════════════════════════════════════════
-- 3. Fahrzeug-spezifische Checklist Items
-- ═══════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS fahrzeug_checklist_items (
  id              SERIAL       PRIMARY KEY,
  fahrzeug_id     UUID         NOT NULL REFERENCES fahrzeuge(id) ON DELETE CASCADE,
  bezeichnung     VARCHAR(500) NOT NULL,
  beschreibung    TEXT,
  pflicht         BOOLEAN      NOT NULL DEFAULT TRUE,
  sort_order      INT          NOT NULL DEFAULT 0,
  aktiv           BOOLEAN      NOT NULL DEFAULT TRUE
);

CREATE INDEX IF NOT EXISTS idx_fci_fahrzeug_id ON fahrzeug_checklist_items(fahrzeug_id);

-- ═══════════════════════════════════════════════════════════════════════════
-- 4. Checklist-Ausführungen (Execution records)
-- ═══════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS checklist_ausfuehrungen (
  id              UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  fahrzeug_id     UUID         NOT NULL REFERENCES fahrzeuge(id) ON DELETE CASCADE,
  vorlage_id      INT          REFERENCES checklist_vorlagen(id) ON DELETE SET NULL,
  status          VARCHAR(30)  NOT NULL DEFAULT 'offen'
                  CHECK (status IN ('offen','abgeschlossen','unvollstaendig','freigegeben')),
  ausgefuehrt_von UUID         REFERENCES users(id) ON DELETE SET NULL,
  ausgefuehrt_am  TIMESTAMPTZ,
  freigegeben_von UUID         REFERENCES users(id) ON DELETE SET NULL,
  freigegeben_am  TIMESTAMPTZ,
  notizen         TEXT,
  created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ca_fahrzeug_id ON checklist_ausfuehrungen(fahrzeug_id);
CREATE INDEX IF NOT EXISTS idx_ca_vorlage_id ON checklist_ausfuehrungen(vorlage_id);
CREATE INDEX IF NOT EXISTS idx_ca_status ON checklist_ausfuehrungen(status);

-- ═══════════════════════════════════════════════════════════════════════════
-- 5. Ausführung Items (Execution line items / answers)
-- ═══════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS checklist_ausfuehrung_items (
  id               SERIAL       PRIMARY KEY,
  ausfuehrung_id   UUID         NOT NULL REFERENCES checklist_ausfuehrungen(id) ON DELETE CASCADE,
  vorlage_item_id  INT          REFERENCES checklist_vorlage_items(id) ON DELETE SET NULL,
  fahrzeug_item_id INT          REFERENCES fahrzeug_checklist_items(id) ON DELETE SET NULL,
  bezeichnung      VARCHAR(500),
  ergebnis         VARCHAR(20)  CHECK (ergebnis IN ('ok','nok','na')),
  kommentar        TEXT,
  created_at       TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_cai_ausfuehrung_id ON checklist_ausfuehrung_items(ausfuehrung_id);

-- ═══════════════════════════════════════════════════════════════════════════
-- 6. Fälligkeiten (Due date tracking per vehicle+template)
-- ═══════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS checklist_faelligkeit (
  fahrzeug_id           UUID    NOT NULL REFERENCES fahrzeuge(id) ON DELETE CASCADE,
  vorlage_id            INT     NOT NULL REFERENCES checklist_vorlagen(id) ON DELETE CASCADE,
  naechste_faellig_am   DATE    NOT NULL,
  letzte_ausfuehrung_id UUID    REFERENCES checklist_ausfuehrungen(id) ON DELETE SET NULL,
  PRIMARY KEY (fahrzeug_id, vorlage_id)
);

-- ═══════════════════════════════════════════════════════════════════════════
-- 7. Auto-update updated_at trigger for checklist_vorlagen
-- ═══════════════════════════════════════════════════════════════════════════

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_checklist_vorlagen_updated') THEN
    CREATE TRIGGER trg_checklist_vorlagen_updated BEFORE UPDATE ON checklist_vorlagen
      FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
  END IF;
END $$;
