-- Migration 011: Ausrüstungsverwaltung (Equipment Management)
-- Depends on: 001_create_users_table.sql (uuid-ossp extension, users table)
--             005_create_fahrzeuge.sql (fahrzeuge table)
--             009_vehicle_soft_delete.sql (soft-delete pattern)

-- ============================================================
-- TABLE: ausruestung_kategorien (Equipment Categories — lookup)
-- ============================================================
CREATE TABLE IF NOT EXISTS ausruestung_kategorien (
    id           UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name         VARCHAR(100) NOT NULL UNIQUE,              -- e.g. 'Atemschutzgeräte'
    kurzname     VARCHAR(30) NOT NULL UNIQUE,               -- e.g. 'PA'
    sortierung   INTEGER NOT NULL DEFAULT 0,                -- display order
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);


-- ============================================================
-- TABLE: ausruestung (Core Equipment Inventory)
-- ============================================================
CREATE TABLE IF NOT EXISTS ausruestung (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    bezeichnung             VARCHAR(200) NOT NULL,                      -- e.g. 'Dräger PSS 5000'
    kategorie_id            UUID NOT NULL REFERENCES ausruestung_kategorien(id),
    seriennummer            VARCHAR(100),                               -- manufacturer serial
    inventarnummer          VARCHAR(50),                                -- internal inventory number
    hersteller              VARCHAR(150),                               -- manufacturer
    baujahr                 INTEGER CHECK (baujahr >= 1950 AND baujahr <= 2100),
    status                  VARCHAR(30) NOT NULL DEFAULT 'einsatzbereit'
                                CHECK (status IN (
                                    'einsatzbereit',
                                    'beschaedigt',
                                    'in_wartung',
                                    'ausser_dienst'
                                )),
    status_bemerkung        TEXT,                                       -- free-text status note
    ist_wichtig             BOOLEAN NOT NULL DEFAULT FALSE,             -- drives vehicle card warnings
    fahrzeug_id             UUID REFERENCES fahrzeuge(id) ON DELETE SET NULL,  -- nullable
    standort                VARCHAR(150) NOT NULL DEFAULT 'Lager',      -- used when no fahrzeug
    pruef_intervall_monate  INTEGER CHECK (pruef_intervall_monate > 0), -- nullable
    letzte_pruefung_am      DATE,
    naechste_pruefung_am    DATE,
    bemerkung               TEXT,                                       -- general notes
    deleted_at              TIMESTAMPTZ,                                -- soft-delete
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ausruestung_status
    ON ausruestung(status);
CREATE INDEX IF NOT EXISTS idx_ausruestung_kategorie
    ON ausruestung(kategorie_id);
CREATE INDEX IF NOT EXISTS idx_ausruestung_fahrzeug
    ON ausruestung(fahrzeug_id)
    WHERE fahrzeug_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_ausruestung_active
    ON ausruestung(id)
    WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_ausruestung_pruefung
    ON ausruestung(naechste_pruefung_am)
    WHERE naechste_pruefung_am IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_ausruestung_wichtig
    ON ausruestung(fahrzeug_id, status)
    WHERE ist_wichtig = TRUE AND deleted_at IS NULL;

-- Auto-update updated_at (reuses function from migration 001)
CREATE TRIGGER update_ausruestung_updated_at
    BEFORE UPDATE ON ausruestung
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();


-- ============================================================
-- TABLE: ausruestung_wartungslog (Service/Inspection History)
-- ============================================================
CREATE TABLE IF NOT EXISTS ausruestung_wartungslog (
    id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ausruestung_id    UUID NOT NULL REFERENCES ausruestung(id) ON DELETE CASCADE,
    datum             DATE NOT NULL,
    art               VARCHAR(30) NOT NULL
                          CHECK (art IN (
                              'Prüfung',
                              'Reparatur',
                              'Sonstiges'
                          )),
    beschreibung      TEXT NOT NULL,
    ergebnis          VARCHAR(30)
                          CHECK (ergebnis IS NULL OR ergebnis IN (
                              'bestanden',
                              'bestanden_mit_maengeln',
                              'nicht_bestanden'
                          )),
    kosten            DECIMAL(8,2) CHECK (kosten >= 0),
    pruefende_stelle  VARCHAR(150),                         -- e.g. 'Atemschutzwerkstatt Bezirk'
    dokument_url      VARCHAR(500),                         -- link to scan/PDF
    erfasst_von       UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ausruestung_wartung_item
    ON ausruestung_wartungslog(ausruestung_id);
CREATE INDEX IF NOT EXISTS idx_ausruestung_wartung_datum
    ON ausruestung_wartungslog(datum DESC);


-- ============================================================
-- VIEW: ausruestung_mit_pruefstatus
-- For each active equipment item, joins category and vehicle
-- and computes pruefung_tage_bis_faelligkeit (negative = overdue).
-- The dashboard equipment panel and fleet overview query this view.
-- ============================================================
CREATE OR REPLACE VIEW ausruestung_mit_pruefstatus AS
SELECT
    a.*,
    k.name        AS kategorie_name,
    k.kurzname    AS kategorie_kurzname,
    f.bezeichnung AS fahrzeug_bezeichnung,
    f.kurzname    AS fahrzeug_kurzname,
    CASE
        WHEN a.naechste_pruefung_am IS NOT NULL
        THEN a.naechste_pruefung_am::date - CURRENT_DATE
        ELSE NULL
    END AS pruefung_tage_bis_faelligkeit
FROM ausruestung a
JOIN ausruestung_kategorien k ON k.id = a.kategorie_id
LEFT JOIN fahrzeuge f ON f.id = a.fahrzeug_id AND f.deleted_at IS NULL
WHERE a.deleted_at IS NULL;


-- ============================================================
-- SEED DATA: Equipment Categories
-- ============================================================
INSERT INTO ausruestung_kategorien (name, kurzname, sortierung) VALUES
    ('Atemschutzgeräte',             'PA',     1),
    ('Pumpen',                       'Pumpe',  2),
    ('Schläuche',                    'SL',     3),
    ('Leitern',                      'Leiter', 4),
    ('Rettungsgeräte',               'RG',     5),
    ('Messgeräte',                   'MG',     6),
    ('Persönliche Schutzausrüstung', 'PSA',    7),
    ('Kommunikation',                'Funk',   8),
    ('Beleuchtung',                  'Licht',  9),
    ('Sonstige',                     'Sonst.', 10)
ON CONFLICT (name) DO NOTHING;
