-- Migration: 003_create_mitglieder_profile
-- Creates the mitglieder_profile and dienstgrad_verlauf tables for fire department member data.
-- Rollback:
--   DROP TABLE IF EXISTS dienstgrad_verlauf;
--   DROP TABLE IF EXISTS mitglieder_profile;

-- ============================================================
-- mitglieder_profile
-- One-to-one extension of the users table.
-- A user can exist without a profile (profile is created later
-- by a Kommandant). The user_id is both FK and UNIQUE.
-- ============================================================
CREATE TABLE IF NOT EXISTS mitglieder_profile (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id                 UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    -- Internal member identifier assigned by the Kommandant
    mitglieds_nr            VARCHAR(32) UNIQUE,

    -- Rank (Dienstgrad) with allowed values enforced by CHECK
    dienstgrad              VARCHAR(64)
        CHECK (dienstgrad IS NULL OR dienstgrad IN (
            'Feuerwehranwärter',
            'Feuerwehrmann',
            'Feuerwehrfrau',
            'Oberfeuerwehrmann',
            'Oberfeuerwehrfrau',
            'Hauptfeuerwehrmann',
            'Hauptfeuerwehrfrau',
            'Löschmeister',
            'Oberlöschmeister',
            'Hauptlöschmeister',
            'Brandmeister',
            'Oberbrandmeister',
            'Hauptbrandmeister',
            'Brandinspektor',
            'Oberbrandinspektor',
            'Brandoberinspektor',
            'Brandamtmann'
        )),
    dienstgrad_seit         DATE,

    -- Funktion(en) — a member can hold multiple roles simultaneously
    -- Stored as a PostgreSQL TEXT array
    funktion                TEXT[] DEFAULT '{}',

    -- Membership status
    status                  VARCHAR(32) NOT NULL DEFAULT 'aktiv'
        CHECK (status IN (
            'aktiv',
            'passiv',
            'ehrenmitglied',
            'jugendfeuerwehr',
            'anwärter',
            'ausgetreten'
        )),

    -- Important dates
    eintrittsdatum          DATE,
    austrittsdatum          DATE,
    geburtsdatum            DATE,          -- sensitive: only shown to Kommandant/Admin

    -- Contact information (stored raw, formatted on display)
    telefon_mobil           VARCHAR(32),
    telefon_privat          VARCHAR(32),

    -- Emergency contact (sensitive: only own record visible to Mitglied)
    notfallkontakt_name     VARCHAR(255),
    notfallkontakt_telefon  VARCHAR(32),

    -- Driving licenses (e.g. ['B', 'C', 'CE'])
    fuehrerscheinklassen    TEXT[] DEFAULT '{}',

    -- Uniform sizing
    tshirt_groesse          VARCHAR(8)
        CHECK (tshirt_groesse IS NULL OR tshirt_groesse IN (
            'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL'
        )),
    schuhgroesse            VARCHAR(8),

    -- Free-text notes (Kommandant only)
    bemerkungen             TEXT,

    -- Profile photo URL (separate from the Authentik profile_picture_url)
    bild_url                TEXT,

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

    -- Enforce one-to-one relationship with users
    CONSTRAINT uq_mitglieder_profile_user_id UNIQUE (user_id)
);

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

CREATE INDEX IF NOT EXISTS idx_mitglieder_profile_status
    ON mitglieder_profile(status);

CREATE INDEX IF NOT EXISTS idx_mitglieder_profile_dienstgrad
    ON mitglieder_profile(dienstgrad);

CREATE INDEX IF NOT EXISTS idx_mitglieder_profile_mitglieds_nr
    ON mitglieder_profile(mitglieds_nr);

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

-- ============================================================
-- dienstgrad_verlauf
-- Append-only audit log of every rank change.
-- Never deleted; soft history only.
-- ============================================================
CREATE TABLE IF NOT EXISTS dienstgrad_verlauf (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    dienstgrad_neu  VARCHAR(64) NOT NULL,
    dienstgrad_alt  VARCHAR(64),            -- NULL on first assignment
    datum           DATE NOT NULL DEFAULT CURRENT_DATE,
    durch_user_id   UUID REFERENCES users(id) ON DELETE SET NULL,
    bemerkung       TEXT,
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_dienstgrad_verlauf_user_id
    ON dienstgrad_verlauf(user_id);

CREATE INDEX IF NOT EXISTS idx_dienstgrad_verlauf_datum
    ON dienstgrad_verlauf(datum DESC);
