-- ============================================================================
--   W A R N U N G  –  K I – G E N E R I E R T E S   D A T E N B A N K – S C H E M A
--
--   Dieses SQL-Schema / diese Daten wurden vollständig von einer KI generiert.
--   Experiment-Key: run_17_gates_default
--
--   WICHTIG:
--   • Rein experimentell – KEINE Qualitätssicherung, KEINE Tests
--   • KEIN PRODUKTIVEINSATZ – auch nicht in Test- oder Staging-Umgebungen!
--   • Kann Datenverlust, Inkonsistenzen, Sicherheitslücken oder Abstürze verursachen
--   • Nur für Forschung, Demonstration und Lernzwecke gedacht
--
--   Verwenden auf eigene Gefahr!
-- ============================================================================
-- Datenbank-Dialekt: PostgreSQL 16+
-- Kompatibilität: PostgreSQL >= 15 empfohlen

CREATE TABLE runs (
    run_id            BIGSERIAL    NOT NULL,
    run_label         TEXT         NOT NULL,
    experiment_key    TEXT         NOT NULL,
    setup_fingerprint TEXT         NOT NULL,
    policy_hash       TEXT         NOT NULL,
    gate_version      TEXT         NOT NULL,
    mode              TEXT         NOT NULL DEFAULT 'warn',
    started_at        TIMESTAMPTZ  NOT NULL,
    completed_at      TIMESTAMPTZ,
    decision_card_url TEXT,
    notes             TEXT,
    CONSTRAINT runs_pkey PRIMARY KEY (run_id),
    CONSTRAINT runs_run_label_key UNIQUE (run_label),
    CONSTRAINT runs_mode_chk CHECK (mode IN ('warn', 'observe', 'enforce')),
    CONSTRAINT runs_timeline_chk CHECK (completed_at IS NULL OR completed_at >= started_at)
);

COMMENT ON TABLE runs IS 'Metadaten zu Gate-V1-Experimentläufen (z. B. Run #14–#17).';

CREATE TABLE strata (
    stratum_code TEXT    NOT NULL,
    description  TEXT    NOT NULL,
    is_pinned    BOOLEAN NOT NULL,
    near_expiry  BOOLEAN NOT NULL,
    CONSTRAINT strata_pkey PRIMARY KEY (stratum_code)
);

COMMENT ON TABLE strata IS 'Referenztabelle für Strata (pinned, unpinned, near-expiry, ...).';

CREATE TABLE log_entries (
    log_id                  BIGSERIAL    NOT NULL,
    run_id                  BIGINT       NOT NULL,
    stratum_code            TEXT         NOT NULL,
    recorded_at             TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    retry_taken             BOOLEAN      NOT NULL,
    retry_total_overhead_ms INTEGER      NOT NULL,
    expires_at_dist_hours   NUMERIC(8,3) NOT NULL,
    delta_t                 NUMERIC(9,3) NOT NULL,
    CONSTRAINT log_entries_pkey PRIMARY KEY (log_id),
    CONSTRAINT log_entries_run_fk FOREIGN KEY (run_id)
        REFERENCES runs (run_id) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT log_entries_stratum_fk FOREIGN KEY (stratum_code)
        REFERENCES strata (stratum_code) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT log_entries_retry_overhead_chk CHECK (retry_total_overhead_ms BETWEEN 0 AND 1000),
    CONSTRAINT log_entries_expires_dist_chk CHECK (expires_at_dist_hours BETWEEN -720 AND 720),
    CONSTRAINT log_entries_delta_t_chk CHECK (delta_t BETWEEN -500 AND 500)
);

COMMENT ON TABLE log_entries IS 'Zentrale Log-Tabelle für Gate-V1-Entscheidungsdaten (Δt, Retry-Overhead etc.).';
COMMENT ON COLUMN log_entries.delta_t IS 'Gemessene Δt-Abweichung in Millisekunden (negativ bedeutet zu früh).';
COMMENT ON COLUMN log_entries.expires_at_dist_hours IS 'Zeitabstand bis zum Expiry (Stunden, kann negativ sein).';

CREATE INDEX log_entries_run_stratum_idx
    ON log_entries (run_id, stratum_code);

CREATE INDEX log_entries_stratum_delta_t_idx
    ON log_entries (stratum_code, delta_t);

CREATE INDEX log_entries_retry_taken_idx
    ON log_entries (stratum_code, retry_taken)
    WHERE retry_taken = TRUE;