"""Persisted tenant membership and one-shot control claims.

This deliberately supersedes the revoked 501--503 payload-authorized paths.
The caller supplies a principal and route only as facts to be checked against
the persistent mapping; a tenant identifier is never accepted by these
functions.  A claim is single-use, DB-clock bounded, and binds the exact
JSON request that will later be authorized.
"""

from alembic import op

revision = "20260817_505"
down_revision = "20260817_504"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        r"""
        DO $$ BEGIN
          IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname='dataops_tenant_foundation_owner')
             OR NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname='dataops_tenant_control') THEN
            RAISE EXCEPTION 'tenant control roles must be provisioned before migration';
          END IF;
          IF NOT pg_has_role(current_user, 'dataops_tenant_foundation_owner', 'MEMBER')
             OR NOT pg_has_role(current_user, 'dataops_tenant_control', 'MEMBER') THEN
            RAISE EXCEPTION 'migration identity lacks tenant control memberships';
          END IF;
        END $$;

        CREATE TABLE public.tenant_control_memberships (
          tenant_id text NOT NULL REFERENCES public.tenants(tenant_id) ON DELETE RESTRICT,
          principal_id uuid NOT NULL,
          host text NOT NULL CHECK (host=lower(host) AND host ~ '^[a-z0-9][a-z0-9.-]{0,252}$'),
          created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
          PRIMARY KEY (tenant_id,principal_id,host),
          UNIQUE (principal_id,host)
        );
        CREATE TABLE public.tenant_control_claims (
          claim_uid uuid PRIMARY KEY,
          nonce uuid NOT NULL UNIQUE,
          tenant_id text NOT NULL REFERENCES public.tenants(tenant_id) ON DELETE RESTRICT,
          principal_id uuid NOT NULL,
          host text NOT NULL CHECK (host=lower(host) AND host ~ '^[a-z0-9][a-z0-9.-]{0,252}$'),
          action text NOT NULL CHECK (action IN ('quota_reserve','lifecycle_transition')),
          request jsonb NOT NULL CHECK (jsonb_typeof(request)='object'),
          request_digest char(64) NOT NULL,
          status text NOT NULL DEFAULT 'issued' CHECK (status IN ('issued','consumed')),
          created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
          expires_at timestamptz NOT NULL,
          consumed_at timestamptz,
          CHECK (expires_at > created_at),
          CHECK ((status='issued' AND consumed_at IS NULL) OR (status='consumed' AND consumed_at IS NOT NULL))
        );
        CREATE INDEX tenant_control_claims_active_idx
          ON public.tenant_control_claims(claim_uid,status,expires_at);
        ALTER TABLE public.tenant_control_memberships OWNER TO dataops_tenant_foundation_owner;
        ALTER TABLE public.tenant_control_claims OWNER TO dataops_tenant_foundation_owner;
        ALTER TABLE public.tenants ENABLE ROW LEVEL SECURITY;
        REVOKE ALL ON TABLE public.tenants,public.tenant_control_memberships,public.tenant_control_claims
          FROM PUBLIC,dataops_app_runtime,dataops_tenant_control;

        CREATE FUNCTION public.tenant_control_issue_claim(p_payload jsonb)
        RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path=pg_catalog,public AS $tenant_claim_issue$
        DECLARE v_principal uuid; v_host text; v_action text; v_tenant text; v_claim uuid; v_nonce uuid;
        DECLARE v_request jsonb; v_digest text; v_expires timestamptz;
        BEGIN
          IF NOT pg_has_role(session_user,'dataops_tenant_control','MEMBER') THEN
            RAISE EXCEPTION 'tenant_control_identity_required';
          END IF;
          IF jsonb_typeof(p_payload)<>'object'
             OR NOT (p_payload ?& ARRAY['claim_uid','nonce','principal_id','host','action','request'])
             OR p_payload-ARRAY['claim_uid','nonce','principal_id','host','action','request']<>'{}'::jsonb THEN
            RAISE EXCEPTION 'tenant_payload_closed';
          END IF;
          IF coalesce(p_payload->>'claim_uid','') !~* '^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$'
             OR coalesce(p_payload->>'nonce','') !~* '^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$'
             OR coalesce(p_payload->>'principal_id','') !~* '^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$'
             OR p_payload->>'host' !~ '^[a-z0-9][a-z0-9.-]{0,252}$'
             OR p_payload->>'host' <> lower(p_payload->>'host')
             OR p_payload->>'action' NOT IN ('quota_reserve','lifecycle_transition')
             OR jsonb_typeof(p_payload->'request')<>'object' THEN
            RAISE EXCEPTION 'tenant_payload_invalid';
          END IF;
          v_claim := (p_payload->>'claim_uid')::uuid;
          v_nonce := (p_payload->>'nonce')::uuid;
          v_principal := (p_payload->>'principal_id')::uuid;
          v_host := p_payload->>'host';
          v_action := p_payload->>'action';
          v_request := p_payload->'request';
          SELECT m.tenant_id INTO v_tenant
            FROM public.tenant_control_memberships m
            JOIN public.tenants t ON t.tenant_id=m.tenant_id
           WHERE m.principal_id=v_principal AND m.host=v_host AND t.state='active';
          IF NOT FOUND THEN RAISE EXCEPTION 'tenant_membership_denied'; END IF;
          v_digest := encode(sha256(convert_to(v_request::text,'utf8')),'hex');
          v_expires := clock_timestamp()+interval '120 seconds';
          INSERT INTO public.tenant_control_claims(
            claim_uid,nonce,tenant_id,principal_id,host,action,request,request_digest,expires_at
          ) VALUES(v_claim,v_nonce,v_tenant,v_principal,v_host,v_action,v_request,v_digest,v_expires);
          PERFORM set_config('dataops.tenant_id',v_tenant,true);
          INSERT INTO public.tenant_audit_events(tenant_id,event_type,payload_digest,lease_fence)
            VALUES(v_tenant,'tenant.claim.issued',v_digest,0);
          RETURN jsonb_build_object('claim_uid',v_claim,'tenant_id',v_tenant,'action',v_action,
                                    'request_digest',v_digest,'expires_at',v_expires);
        END; $tenant_claim_issue$;
        ALTER FUNCTION public.tenant_control_issue_claim(jsonb) OWNER TO dataops_tenant_foundation_owner;

        CREATE FUNCTION public.tenant_control_consume_claim(
          p_claim_uid uuid,p_nonce uuid,p_action text,p_request jsonb
        ) RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path=pg_catalog,public AS $tenant_claim_consume$
        DECLARE v_claim public.tenant_control_claims%ROWTYPE;
        BEGIN
          IF NOT pg_has_role(session_user,'dataops_tenant_control','MEMBER') THEN
            RAISE EXCEPTION 'tenant_control_identity_required';
          END IF;
          IF p_action NOT IN ('quota_reserve','lifecycle_transition') OR jsonb_typeof(p_request)<>'object' THEN
            RAISE EXCEPTION 'tenant_payload_closed';
          END IF;
          SELECT * INTO v_claim FROM public.tenant_control_claims
            WHERE claim_uid=p_claim_uid FOR UPDATE;
          IF NOT FOUND OR v_claim.nonce<>p_nonce OR v_claim.action<>p_action
             OR v_claim.request<>p_request THEN
            RAISE EXCEPTION 'tenant_claim_denied';
          END IF;
          IF v_claim.status<>'issued' OR v_claim.expires_at<=clock_timestamp() THEN
            RAISE EXCEPTION 'tenant_claim_replayed_or_expired';
          END IF;
          UPDATE public.tenant_control_claims SET status='consumed',consumed_at=clock_timestamp()
            WHERE claim_uid=v_claim.claim_uid AND status='issued';
          IF NOT FOUND THEN RAISE EXCEPTION 'tenant_claim_replayed_or_expired'; END IF;
          PERFORM set_config('dataops.tenant_id',v_claim.tenant_id,true);
          INSERT INTO public.tenant_audit_events(tenant_id,event_type,payload_digest,lease_fence)
            VALUES(v_claim.tenant_id,'tenant.claim.consumed',v_claim.request_digest,0);
          RETURN jsonb_build_object('claim_uid',v_claim.claim_uid,'tenant_id',v_claim.tenant_id,
                                    'principal_id',v_claim.principal_id,'action',v_claim.action,
                                    'request_digest',v_claim.request_digest);
        END; $tenant_claim_consume$;
        ALTER FUNCTION public.tenant_control_consume_claim(uuid,uuid,text,jsonb) OWNER TO dataops_tenant_foundation_owner;
        REVOKE ALL ON FUNCTION public.tenant_control_issue_claim(jsonb),
          public.tenant_control_consume_claim(uuid,uuid,text,jsonb) FROM PUBLIC,dataops_app_runtime;
        GRANT EXECUTE ON FUNCTION public.tenant_control_issue_claim(jsonb),
          public.tenant_control_consume_claim(uuid,uuid,text,jsonb) TO dataops_tenant_control;
        """
    )


def downgrade() -> None:
    op.execute(
        """
        DO $$ BEGIN
          IF EXISTS (SELECT 1 FROM public.tenant_control_claims LIMIT 1)
             OR EXISTS (SELECT 1 FROM public.tenant_control_memberships LIMIT 1) THEN
            RAISE EXCEPTION 'tenant control downgrade refused while control data exists';
          END IF;
        END $$;
        REVOKE EXECUTE ON FUNCTION public.tenant_control_issue_claim(jsonb),
          public.tenant_control_consume_claim(uuid,uuid,text,jsonb) FROM dataops_tenant_control;
        DROP FUNCTION public.tenant_control_consume_claim(uuid,uuid,text,jsonb);
        DROP FUNCTION public.tenant_control_issue_claim(jsonb);
        DROP TABLE public.tenant_control_claims;
        DROP TABLE public.tenant_control_memberships;
        """
    )