"""Fix the immutability trigger on Postgres: the `json` column type has no
equality operator, so `NEW.spec IS DISTINCT FROM OLD.spec` errored on EVERY
update to a published row — fail-closed by accident, but it also blocked
legitimate non-spec updates (e.g. retiring). Compare through ::jsonb, which
has real (key-order-insensitive) equality.

Found by exercising the deployed path (`UPDATE ...` via psql), not by tests —
the SQLite suite can't see Postgres operator gaps.

Revision ID: 0002_trigger_jsonb
Revises: 913bfbbc6c4e
"""

from alembic import op

revision = "0002_trigger_jsonb"
down_revision = "913bfbbc6c4e"
branch_labels = None
depends_on = None

FUNC = """
    CREATE OR REPLACE FUNCTION forbid_published_workflow_update()
    RETURNS trigger AS $$
    BEGIN
        IF OLD.status = 'published' AND (
            NEW.spec::jsonb IS DISTINCT FROM OLD.spec::jsonb
            OR NEW.key IS DISTINCT FROM OLD.key
            OR NEW.version IS DISTINCT FROM OLD.version
        ) THEN
            RAISE EXCEPTION 'published workflow specs are immutable; publish a new version';
        END IF;
        RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
"""


def upgrade():
    bind = op.get_bind()
    if bind.dialect.name == "postgresql":
        op.execute(FUNC)


def downgrade():
    pass  # the broken function is not worth restoring