"""Require three distinct identities for every WP13 approval."""

from alembic import op

revision = "20260818_559"
down_revision = "20260818_558"
branch_labels = None
depends_on = None

_FACT_TABLES = (
    "plugin_registry_versions", "plugin_approvals", "plugin_runtime_leases",
    "plugin_runs", "plugin_dead_letters", "plugin_audit_outbox",
    "plugin_request_claims", "plugin_runtime_breakers", "plugin_recovery_claims",
)


def upgrade() -> None:
    op.get_bind().exec_driver_sql(r'''
    ALTER TABLE public.plugin_registry_versions
      ADD COLUMN review_manifest_digest char(64) CHECK(review_manifest_digest ~ '^[0-9a-f]{64}$');
    DO $preflight$
    BEGIN
      IF EXISTS(SELECT 1 FROM public.plugin_registry_versions WHERE state<>'draft') THEN
        RAISE EXCEPTION 'plugin_review_fact_backfill_denied';
      END IF;
    END $preflight$;
    CREATE FUNCTION public.plugin_platform_control_v5(p jsonb) RETURNS jsonb
    LANGUAGE plpgsql SECURITY DEFINER SET search_path=pg_catalog,public AS $control$
    DECLARE r record; a record; result jsonb;
    BEGIN
      IF NOT pg_has_role(session_user,'dataops_plugin_platform_control','MEMBER') OR jsonb_typeof(p)<>'object' THEN RAISE EXCEPTION 'plugin_control_denied'; END IF;
      IF p->>'action'='register' THEN
        result:=public.plugin_platform_control_v4(p);
        UPDATE public.plugin_registry_versions SET review_manifest_digest=NULL
          WHERE plugin_uid=p->>'plugin_uid' AND version=p->>'version';
        RETURN result;
      ELSIF p->>'action'='review' THEN
        IF p-ARRAY['action','plugin_uid','version','actor_ref']<>'{}'::jsonb THEN RAISE EXCEPTION 'plugin_review_closed'; END IF;
        SELECT * INTO r FROM public.plugin_registry_versions WHERE plugin_uid=p->>'plugin_uid' AND version=p->>'version' FOR UPDATE;
        IF NOT FOUND OR r.state<>'draft' OR r.submitter_ref IS NULL OR r.submitter_ref=p->>'actor_ref' THEN RAISE EXCEPTION 'plugin_review_independence_denied'; END IF;
        result:=public.plugin_platform_control_v3(p);
        UPDATE public.plugin_registry_versions
          SET review_generation=review_generation+1,reviewed_at=clock_timestamp(),review_manifest_digest=manifest_digest
          WHERE plugin_uid=p->>'plugin_uid' AND version=p->>'version' AND state='reviewed';
        RETURN result;
      ELSIF p->>'action'='issue_approval' THEN
        IF p-ARRAY['action','plugin_uid','version','actor_ref','approval_action','tenant_ref','domain_ref','expires_in_seconds']<>'{}'::jsonb THEN RAISE EXCEPTION 'plugin_approval_closed'; END IF;
        SELECT * INTO r FROM public.plugin_registry_versions WHERE plugin_uid=p->>'plugin_uid' AND version=p->>'version' FOR UPDATE;
        IF NOT FOUND OR r.state<>'reviewed' OR r.submitter_ref IS NULL OR r.review_actor IS NULL
          OR r.submitter_ref=r.review_actor OR r.submitter_ref=p->>'actor_ref'
          OR r.review_actor=p->>'actor_ref' OR r.reviewed_at IS NULL
          OR r.review_manifest_digest IS NULL OR r.review_manifest_digest<>r.manifest_digest THEN RAISE EXCEPTION 'plugin_three_identity_denied'; END IF;
        RETURN public.plugin_platform_control_v4(p);
      ELSIF p->>'action'='transition' THEN
        IF p-ARRAY['action','plugin_uid','version','actor_ref','tenant_ref','domain_ref','approval_uid','target_state','expected_fence','incident_uid']<>'{}'::jsonb THEN RAISE EXCEPTION 'plugin_transition_closed'; END IF;
        SELECT * INTO r FROM public.plugin_registry_versions WHERE plugin_uid=p->>'plugin_uid' AND version=p->>'version' FOR UPDATE;
        SELECT * INTO a FROM public.plugin_approvals WHERE approval_uid=(p->>'approval_uid')::uuid FOR UPDATE;
        IF NOT FOUND OR r.submitter_ref IS NULL OR r.review_actor IS NULL
          OR r.submitter_ref=r.review_actor OR r.submitter_ref=p->>'actor_ref'
          OR r.review_actor=p->>'actor_ref' OR a.actor_ref<>p->>'actor_ref'
          OR a.actor_ref=r.submitter_ref OR a.reviewer_ref<>r.review_actor
          OR r.reviewed_at IS NULL OR r.review_manifest_digest IS NULL
          OR r.review_manifest_digest<>r.manifest_digest OR a.manifest_digest<>r.review_manifest_digest THEN RAISE EXCEPTION 'plugin_three_identity_denied'; END IF;
        RETURN public.plugin_platform_control_v4(p);
      END IF;
      RETURN public.plugin_platform_control_v4(p);
    END $control$;
    ALTER FUNCTION public.plugin_platform_control_v5(jsonb) OWNER TO dataops_plugin_platform_owner;
    REVOKE ALL ON FUNCTION public.plugin_platform_control_v4(jsonb) FROM PUBLIC,dataops_app,dataops_app_runtime,dataops_plugin_platform_control;
    GRANT EXECUTE ON FUNCTION public.plugin_platform_control_v5(jsonb) TO dataops_plugin_platform_control;
    ''')


def downgrade() -> None:
    bind = op.get_bind()
    checks = " OR ".join(f"EXISTS(SELECT 1 FROM public.{table} LIMIT 1)" for table in _FACT_TABLES)
    if bind.exec_driver_sql(f"SELECT {checks}").scalar():
        raise RuntimeError("downgrade refused: three-identity WP13 facts are nonempty")
    bind.exec_driver_sql('''
    REVOKE ALL ON FUNCTION public.plugin_platform_control_v5(jsonb) FROM PUBLIC,dataops_app,dataops_app_runtime,dataops_plugin_platform_control;
    DROP FUNCTION public.plugin_platform_control_v5(jsonb);
    ALTER TABLE public.plugin_registry_versions DROP COLUMN review_manifest_digest;
    GRANT EXECUTE ON FUNCTION public.plugin_platform_control_v4(jsonb) TO dataops_plugin_platform_control;
    ''')