-- Migration 009: Soft delete for vehicles
-- Adds deleted_at to fahrzeuge and refreshes the view to exclude soft-deleted rows.
-- Hard DELETE is replaced by UPDATE SET deleted_at = NOW() in the service layer.

ALTER TABLE fahrzeuge
    ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;

COMMENT ON COLUMN fahrzeuge.deleted_at IS
    'NULL = active vehicle. Set to timestamp when soft-deleted. Records are never physically removed.';

-- Partial index: only index active (non-deleted) vehicles for fast lookups
CREATE INDEX IF NOT EXISTS idx_fahrzeuge_active
    ON fahrzeuge(id)
    WHERE deleted_at IS NULL;

-- Refresh the view to exclude soft-deleted vehicles
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
WHERE f.deleted_at IS NULL;
