-- Migration: 014_create_atemschutz
-- Atemschutz-Traegerverwaltung (Breathing Apparatus Carrier Management)
-- Depends on: 001_create_users_table (users table, uuid-ossp, update_updated_at_column)
--             003_create_mitglieder_profile (mitglieder_profile for the view)
-- Rollback:
--   DROP VIEW  IF EXISTS atemschutz_uebersicht;
--   DROP TABLE IF EXISTS atemschutz_traeger;

-- ============================================================
-- TABLE: atemschutz_traeger (BA Carrier Registry)
-- Each row = one firefighter qualified/tracked for BA use.
-- One record per user (enforced by UNIQUE constraint).
-- ============================================================
CREATE TABLE IF NOT EXISTS atemschutz_traeger (
    id                          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id                     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    -- Atemschutz-Lehrgang (BA course qualification)
    atemschutz_lehrgang         BOOLEAN NOT NULL DEFAULT FALSE,
    lehrgang_datum              DATE,

    -- G26.3 Aerztliche Untersuchung (doctor's examination)
    untersuchung_datum          DATE,
    untersuchung_gueltig_bis    DATE,       -- typically valid 3 years
    untersuchung_ergebnis       VARCHAR(30)
        CHECK (untersuchung_ergebnis IS NULL OR untersuchung_ergebnis IN (
            'tauglich', 'bedingt_tauglich', 'nicht_tauglich'
        )),

    -- Leistungstest / Finnentest (performance test)
    leistungstest_datum         DATE,
    leistungstest_gueltig_bis   DATE,       -- typically valid 1 year
    leistungstest_bestanden     BOOLEAN,

    -- Free-text notes (Kommandant / Atemschutzwart)
    bemerkung                   TEXT,

    -- Audit timestamps
    created_at                  TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at                  TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- One record per user
    CONSTRAINT uq_atemschutz_user UNIQUE (user_id)
);

-- ============================================================
-- Indexes for the most common query patterns
-- ============================================================
CREATE INDEX IF NOT EXISTS idx_atemschutz_user
    ON atemschutz_traeger(user_id);

CREATE INDEX IF NOT EXISTS idx_atemschutz_untersuchung
    ON atemschutz_traeger(untersuchung_gueltig_bis)
    WHERE untersuchung_gueltig_bis IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_atemschutz_leistungstest
    ON atemschutz_traeger(leistungstest_gueltig_bis)
    WHERE leistungstest_gueltig_bis IS NOT NULL;

-- ============================================================
-- Auto-update trigger for updated_at
-- Reuses the function already created by migration 001.
-- ============================================================
CREATE TRIGGER update_atemschutz_updated_at
    BEFORE UPDATE ON atemschutz_traeger
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================
-- VIEW: atemschutz_uebersicht
-- Dashboard view with user info and computed validity status.
-- Joins user + mitglieder_profile for display name, rank, etc.
-- Computes expiry flags so the frontend can render traffic-light
-- indicators without any date math on the client side.
-- ============================================================
CREATE OR REPLACE VIEW atemschutz_uebersicht AS
SELECT
    at.*,
    u.name              AS user_name,
    u.given_name        AS user_given_name,
    u.family_name       AS user_family_name,
    u.email             AS user_email,
    mp.status           AS mitglied_status,
    mp.dienstgrad,

    -- Computed: is the medical exam still valid?
    CASE
        WHEN at.untersuchung_gueltig_bis IS NOT NULL
        THEN at.untersuchung_gueltig_bis >= CURRENT_DATE
        ELSE FALSE
    END AS untersuchung_gueltig,

    -- Computed: days until medical exam expires (negative = expired)
    CASE
        WHEN at.untersuchung_gueltig_bis IS NOT NULL
        THEN at.untersuchung_gueltig_bis::date - CURRENT_DATE
        ELSE NULL
    END AS untersuchung_tage_rest,

    -- Computed: is the performance test still valid?
    CASE
        WHEN at.leistungstest_gueltig_bis IS NOT NULL
        THEN at.leistungstest_gueltig_bis >= CURRENT_DATE
        ELSE FALSE
    END AS leistungstest_gueltig,

    -- Computed: days until performance test expires (negative = expired)
    CASE
        WHEN at.leistungstest_gueltig_bis IS NOT NULL
        THEN at.leistungstest_gueltig_bis::date - CURRENT_DATE
        ELSE NULL
    END AS leistungstest_tage_rest,

    -- Computed: fully qualified for BA use (einsatzbereit)?
    -- Requires: course done + valid medical (tauglich) + valid performance test (bestanden)
    CASE
        WHEN at.atemschutz_lehrgang = TRUE
             AND at.untersuchung_gueltig_bis IS NOT NULL
             AND at.untersuchung_gueltig_bis >= CURRENT_DATE
             AND at.untersuchung_ergebnis = 'tauglich'
             AND at.leistungstest_gueltig_bis IS NOT NULL
             AND at.leistungstest_gueltig_bis >= CURRENT_DATE
             AND at.leistungstest_bestanden = TRUE
        THEN TRUE
        ELSE FALSE
    END AS einsatzbereit

FROM atemschutz_traeger at
JOIN users u ON u.id = at.user_id
LEFT JOIN mitglieder_profile mp ON mp.user_id = at.user_id;
