-- =============================================================================
-- Migration 029: Vehicle Out-of-Service Timeframe + Lehrgang Booking Type
--
-- 1. Add ausser_dienst_von / ausser_dienst_bis columns to fahrzeuge
-- 2. Add 'lehrgang' value to fahrzeug_buchung_art enum
-- 3. Remove 'in_lehrgang' from vehicle status CHECK constraint
-- 4. Refresh fahrzeuge_mit_pruefstatus view to include new columns
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 1. Add out-of-service timeframe columns
-- -----------------------------------------------------------------------------
ALTER TABLE fahrzeuge
    ADD COLUMN IF NOT EXISTS ausser_dienst_von TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS ausser_dienst_bis TIMESTAMPTZ;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE table_name = 'fahrzeuge'
          AND constraint_name = 'chk_ausser_dienst_zeitraum'
    ) THEN
        ALTER TABLE fahrzeuge
            ADD CONSTRAINT chk_ausser_dienst_zeitraum
            CHECK (
                ausser_dienst_von IS NULL
                OR ausser_dienst_bis IS NULL
                OR ausser_dienst_bis > ausser_dienst_von
            );
    END IF;
END
$$;

CREATE INDEX IF NOT EXISTS idx_fahrzeuge_ausser_dienst_zeitraum
    ON fahrzeuge(id, ausser_dienst_von, ausser_dienst_bis)
    WHERE ausser_dienst_von IS NOT NULL;

-- -----------------------------------------------------------------------------
-- 2. Add 'lehrgang' to fahrzeug_buchung_art enum
-- -----------------------------------------------------------------------------
DO $$
BEGIN
    ALTER TYPE fahrzeug_buchung_art ADD VALUE IF NOT EXISTS 'lehrgang';
EXCEPTION
    WHEN others THEN
        IF SQLERRM NOT LIKE '%already exists%' THEN
            RAISE;
        END IF;
END
$$;

-- -----------------------------------------------------------------------------
-- 3. Remove 'in_lehrgang' from vehicle status CHECK
-- -----------------------------------------------------------------------------

-- Migrate existing in_lehrgang rows first
UPDATE fahrzeuge SET status = 'einsatzbereit' WHERE status = 'in_lehrgang';

-- Drop old check constraint (created in migration 005 as fahrzeuge_status_check)
ALTER TABLE fahrzeuge DROP CONSTRAINT IF EXISTS fahrzeuge_status_check;

-- Re-add with only 3 valid values
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE table_name = 'fahrzeuge'
          AND constraint_name = 'chk_fahrzeuge_status'
    ) THEN
        ALTER TABLE fahrzeuge
            ADD CONSTRAINT chk_fahrzeuge_status
            CHECK (status IN (
                'einsatzbereit',
                'ausser_dienst_wartung',
                'ausser_dienst_schaden'
            ));
    END IF;
END
$$;

-- -----------------------------------------------------------------------------
-- 4. Refresh fahrzeuge_mit_pruefstatus view to include new columns
-- -----------------------------------------------------------------------------
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.ausser_dienst_von,
    f.ausser_dienst_bis,
    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;
