-- Migration 008: Simplify inspection model
-- Remove fahrzeug_pruefungen table and related structures.
-- Only §57a (paragraph57a_faellig_am) and Wartung (naechste_wartung_am)
-- remain as the two tracked inspection deadlines, stored on fahrzeuge.

-- Drop the pruefungen table (cascades to indexes)
DROP TABLE IF EXISTS fahrzeug_pruefungen CASCADE;

-- Drop and recreate the fleet overview view (simplified — no CTE)
DROP VIEW IF EXISTS fahrzeuge_mit_pruefstatus;

CREATE OR REPLACE VIEW fahrzeuge_mit_pruefstatus AS
SELECT
    f.id,
    f.bezeichnung,
    f.kurzname,
    f.amtliches_kennzeichen,
    f.fahrgestellnummer,
    f.baujahr,
    f.hersteller,
    f.typ_schluessel,
    f.besatzung_soll,
    f.status,
    f.status_bemerkung,
    f.standort,
    f.bild_url,
    f.created_at,
    f.updated_at,
    f.paragraph57a_faellig_am,
    CASE
        WHEN f.paragraph57a_faellig_am IS NOT NULL
        THEN f.paragraph57a_faellig_am::date - CURRENT_DATE
        ELSE NULL
    END AS paragraph57a_tage_bis_faelligkeit,
    f.naechste_wartung_am,
    CASE
        WHEN f.naechste_wartung_am IS NOT NULL
        THEN f.naechste_wartung_am::date - CURRENT_DATE
        ELSE NULL
    END AS wartung_tage_bis_faelligkeit,
    LEAST(
        CASE WHEN f.paragraph57a_faellig_am IS NOT NULL
             THEN f.paragraph57a_faellig_am::date - CURRENT_DATE
             ELSE NULL END,
        CASE WHEN f.naechste_wartung_am IS NOT NULL
             THEN f.naechste_wartung_am::date - CURRENT_DATE
             ELSE NULL END
    ) AS naechste_pruefung_tage
FROM fahrzeuge f;

-- Index support for alert queries
CREATE INDEX IF NOT EXISTS idx_fahrzeuge_paragraph57a ON fahrzeuge(paragraph57a_faellig_am) WHERE paragraph57a_faellig_am IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_fahrzeuge_wartung ON fahrzeuge(naechste_wartung_am) WHERE naechste_wartung_am IS NOT NULL;
