"""Add append-only signed governance audit evidence seals."""

from alembic import op

revision = "20260730_360"
down_revision = "20260729_350"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE public.governance_audit_seals (
            uid UUID PRIMARY KEY,
            period_start TIMESTAMPTZ NOT NULL,
            period_end TIMESTAMPTZ NOT NULL,
            categories JSONB NOT NULL,
            event_count INTEGER NOT NULL CHECK (event_count >= 0),
            root_hash CHAR(64) NOT NULL,
            signature CHAR(64) NOT NULL,
            key_version VARCHAR(80) NOT NULL,
            sealed_by VARCHAR(160) NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (period_end > period_start),
            CHECK (jsonb_typeof(categories) = 'array')
        );
        CREATE INDEX idx_governance_audit_seals_created
            ON public.governance_audit_seals(created_at DESC, uid);
        CREATE INDEX idx_governance_audit_seals_period
            ON public.governance_audit_seals(period_start, period_end);
        """
    )


def downgrade() -> None:
    raise RuntimeError(
        "governance audit evidence is append-only; "
        "schema downgrade requires an approved archival migration"
    )