-- Migration 009: system_state and graduation_attestation tables (Layer D2, E4)

BEGIN;

CREATE TABLE IF NOT EXISTS system_state (
    variant_id      VARCHAR(32) PRIMARY KEY,
    halted_at       TIMESTAMPTZ,
    halted_by_reason VARCHAR(128),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS graduation_attestation (
    id              BIGSERIAL PRIMARY KEY,
    variant_id      VARCHAR(32) NOT NULL,
    window_start    TIMESTAMPTZ NOT NULL,
    window_end      TIMESTAMPTZ NOT NULL,
    row_count       INT NOT NULL,
    content_hash    VARCHAR(64) NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_graduation_attestation_variant
    ON graduation_attestation (variant_id, created_at DESC);

INSERT INTO schema_versions (version, description)
VALUES (9, 'system_state and graduation_attestation tables')
ON CONFLICT DO NOTHING;

COMMIT;
