ProofedMesh/db/trust/migrations/0001_per_diem_rates.up.sql

-- Migration: 0001_per_diem_rates
-- Legt die Tabelle für Verpflegungsmehraufwands-Pauschalen (BMF-Sätze etc.) an.
-- Siehe docs/architecture/v1.1.md §6.2 und schemas/ds/per_diem/v1.0.0.json.
--
-- Design-Entscheidungen:
--   * rate_id ist eine stabile textuelle ID "PDR-<n>", vergeben über eine Sequence.
--   * Kernfelder (Beträge, country, valid_from, source_regulation) sind unveränderlich,
--     erzwungen per BEFORE UPDATE Trigger. Nur valid_to und rate_change_attestation_ref
--     dürfen nachträglich gesetzt werden (für kontrolliertes Ablaufen bzw. das Nachtragen
--     der Attestations-Referenz).
--   * Keine Überlappung von Gültigkeitsbereichen pro (country, city) — EXCLUDE-Constraint
--     mit GiST + daterange, benötigt die btree_gist Extension.
--   * Betrage als NUMERIC(10,2) — Geld, keine Floats.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE SEQUENCE IF NOT EXISTS per_diem_rates_seq START 1;

CREATE TABLE IF NOT EXISTS per_diem_rates (
    rate_id                    TEXT PRIMARY KEY
                                 DEFAULT ('PDR-' || nextval('per_diem_rates_seq')),
    country                    TEXT NOT NULL CHECK (country ~ '^[A-Z]{2}$'),
    city                       TEXT,
    currency                   TEXT NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
    full_day                   NUMERIC(10, 2) NOT NULL CHECK (full_day >= 0),
    partial_day_min8h          NUMERIC(10, 2) NOT NULL CHECK (partial_day_min8h >= 0),
    arrival_day                NUMERIC(10, 2) NOT NULL CHECK (arrival_day >= 0),
    departure_day              NUMERIC(10, 2) NOT NULL CHECK (departure_day >= 0),
    valid_from                 DATE NOT NULL,
    valid_to                   DATE,
    source_regulation          TEXT NOT NULL CHECK (length(source_regulation) BETWEEN 1 AND 500),
    created_by                 TEXT NOT NULL,
    created_at                 TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    rate_change_attestation_ref TEXT CHECK (rate_change_attestation_ref ~ '^sha256:[0-9a-f]{64}$'),
    CONSTRAINT per_diem_rates_valid_range CHECK (valid_to IS NULL OR valid_to >= valid_from)
);

CREATE INDEX idx_pdr_country_valid_from
    ON per_diem_rates (country, valid_from);

CREATE INDEX idx_pdr_lookup_active
    ON per_diem_rates (country, valid_from)
    WHERE valid_to IS NULL;

-- Prevent overlapping validity windows per (country, city).
-- COALESCE(city, '') normalises NULL to a sentinel so NULL-city overlaps are also caught.
ALTER TABLE per_diem_rates
    ADD CONSTRAINT per_diem_rates_no_overlap EXCLUDE USING gist (
        country WITH =,
        COALESCE(city, '') WITH =,
        daterange(valid_from, COALESCE(valid_to, 'infinity'::date), '[]') WITH &&
    );

-- Immutability trigger: only valid_to and rate_change_attestation_ref may be updated.
CREATE OR REPLACE FUNCTION per_diem_rates_prevent_rewrite() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.rate_id            IS DISTINCT FROM OLD.rate_id
    OR NEW.country            IS DISTINCT FROM OLD.country
    OR NEW.city               IS DISTINCT FROM OLD.city
    OR NEW.currency           IS DISTINCT FROM OLD.currency
    OR NEW.full_day           IS DISTINCT FROM OLD.full_day
    OR NEW.partial_day_min8h  IS DISTINCT FROM OLD.partial_day_min8h
    OR NEW.arrival_day        IS DISTINCT FROM OLD.arrival_day
    OR NEW.departure_day      IS DISTINCT FROM OLD.departure_day
    OR NEW.valid_from         IS DISTINCT FROM OLD.valid_from
    OR NEW.source_regulation  IS DISTINCT FROM OLD.source_regulation
    OR NEW.created_by         IS DISTINCT FROM OLD.created_by
    OR NEW.created_at         IS DISTINCT FROM OLD.created_at
    THEN
        RAISE EXCEPTION 'per_diem_rates: only valid_to and rate_change_attestation_ref may be updated (rate_id=%)', OLD.rate_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER per_diem_rates_prevent_rewrite_trg
    BEFORE UPDATE ON per_diem_rates
    FOR EACH ROW
    EXECUTE FUNCTION per_diem_rates_prevent_rewrite();

-- Audit log for admin actions on per_diem_rates. The authoritative audit lives
-- in the Transparency Log (rate_change attestations); this table is a local
-- convenience mirror for operators to query without hitting Trillian.
CREATE TABLE IF NOT EXISTS per_diem_rate_changes (
    id                          BIGSERIAL PRIMARY KEY,
    rate_id                     TEXT NOT NULL REFERENCES per_diem_rates(rate_id),
    change_kind                 TEXT NOT NULL CHECK (change_kind IN ('create', 'retire')),
    previous_valid_to           DATE,
    new_valid_to                DATE,
    performed_by                TEXT NOT NULL,
    performed_at                TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    rate_change_attestation_ref TEXT NOT NULL CHECK (rate_change_attestation_ref ~ '^sha256:[0-9a-f]{64}$')
);

CREATE INDEX idx_pdr_changes_rate_id ON per_diem_rate_changes (rate_id, performed_at DESC);