"""Persist P3-WP06 B1 policy, provision, hold-release, and reclaim lifecycle."""

from alembic import op

revision = "20260811_482"
down_revision = "20260811_481"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE public.trusted_delivery_policy_transitions (
          uid UUID PRIMARY KEY, policy_uid UUID NOT NULL REFERENCES public.trusted_delivery_policy_versions(uid),
          operation VARCHAR(20) NOT NULL CHECK(operation IN ('activate','rollback')),
          idempotency_key VARCHAR(160) NOT NULL UNIQUE,
          request_digest CHAR(64) NOT NULL CHECK(request_digest ~ '^[0-9a-f]{64}$'),
          approval_ref VARCHAR(200) NOT NULL, approval_digest CHAR(64) NOT NULL CHECK(approval_digest ~ '^[0-9a-f]{64}$'),
          actor_uid UUID NOT NULL REFERENCES public.users(id), created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
        );
        ALTER TABLE public.trusted_delivery_grants
          ADD COLUMN approval_ref VARCHAR(200),
          ADD COLUMN approval_digest CHAR(64),
          ADD COLUMN purpose VARCHAR(100),
          ADD COLUMN environment VARCHAR(30),
          ADD COLUMN target_capability JSONB;
        ALTER TABLE public.trusted_delivery_grants
          ADD CONSTRAINT trusted_delivery_grants_approval_projection_check CHECK (
            (approval_ref IS NULL AND approval_digest IS NULL AND purpose IS NULL AND environment IS NULL AND target_capability IS NULL)
            OR (approval_ref IS NOT NULL AND approval_digest ~ '^[0-9a-f]{64}$' AND purpose IS NOT NULL
                AND environment IN ('development','test','production') AND jsonb_typeof(target_capability)='object')
          );
        ALTER TABLE public.trusted_delivery_grants DROP CONSTRAINT trusted_delivery_grants_status_check;
        ALTER TABLE public.trusted_delivery_grants ADD CONSTRAINT trusted_delivery_grants_status_check
          CHECK(status IN ('active','revoked','failed','held','reclaimed'));
        ALTER TABLE public.trusted_delivery_deliveries ADD COLUMN operation VARCHAR(20) NOT NULL DEFAULT 'provision'
          CHECK(operation IN ('provision','revoke'));
        ALTER TABLE public.trusted_delivery_deliveries DROP CONSTRAINT trusted_delivery_deliveries_status_check;
        ALTER TABLE public.trusted_delivery_deliveries ADD CONSTRAINT trusted_delivery_deliveries_status_check
          CHECK(status IN ('pending','processing','applied','dead_letter','reclaimed'));
        ALTER TABLE public.trusted_delivery_receipts DROP CONSTRAINT trusted_delivery_receipts_status_check;
        ALTER TABLE public.trusted_delivery_receipts ADD CONSTRAINT trusted_delivery_receipts_status_check
          CHECK(status IN ('applied','failed','reclaimed'));
        CREATE TABLE public.trusted_delivery_hold_releases (
          uid UUID PRIMARY KEY, hold_uid UUID NOT NULL REFERENCES public.trusted_delivery_legal_holds(uid),
          idempotency_key VARCHAR(160) NOT NULL UNIQUE,
          request_digest CHAR(64) NOT NULL CHECK(request_digest ~ '^[0-9a-f]{64}$'),
          approval_ref VARCHAR(200) NOT NULL, approval_digest CHAR(64) NOT NULL CHECK(approval_digest ~ '^[0-9a-f]{64}$'),
          actor_uid UUID NOT NULL REFERENCES public.users(id), created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
        );
        CREATE UNIQUE INDEX uq_trusted_delivery_hold_release ON public.trusted_delivery_hold_releases(hold_uid);
        """
    )


def downgrade() -> None:
    op.execute(
        """DO $$ BEGIN
          IF EXISTS(SELECT 1 FROM public.trusted_delivery_policy_transitions)
             OR EXISTS(SELECT 1 FROM public.trusted_delivery_hold_releases)
             OR EXISTS(SELECT 1 FROM public.trusted_delivery_grants WHERE approval_ref IS NOT NULL)
             OR EXISTS(SELECT 1 FROM public.trusted_delivery_deliveries WHERE operation <> 'provision')
             OR EXISTS(SELECT 1 FROM public.trusted_delivery_receipts WHERE status='reclaimed') THEN
            RAISE EXCEPTION 'downgrade requires approved lifecycle archival migration';
          END IF;
        END $$;
        DROP TABLE public.trusted_delivery_hold_releases;
        ALTER TABLE public.trusted_delivery_receipts DROP CONSTRAINT trusted_delivery_receipts_status_check;
        ALTER TABLE public.trusted_delivery_receipts ADD CONSTRAINT trusted_delivery_receipts_status_check CHECK(status IN ('applied','failed'));
        ALTER TABLE public.trusted_delivery_deliveries DROP CONSTRAINT trusted_delivery_deliveries_status_check;
        ALTER TABLE public.trusted_delivery_deliveries ADD CONSTRAINT trusted_delivery_deliveries_status_check
          CHECK(status IN ('pending','processing','applied','dead_letter'));
        ALTER TABLE public.trusted_delivery_deliveries DROP COLUMN operation;
        ALTER TABLE public.trusted_delivery_grants DROP CONSTRAINT trusted_delivery_grants_status_check;
        ALTER TABLE public.trusted_delivery_grants ADD CONSTRAINT trusted_delivery_grants_status_check CHECK(status IN ('active','revoked','failed','held'));
        ALTER TABLE public.trusted_delivery_grants DROP CONSTRAINT trusted_delivery_grants_approval_projection_check;
        ALTER TABLE public.trusted_delivery_grants DROP COLUMN target_capability, DROP COLUMN environment, DROP COLUMN purpose,
          DROP COLUMN approval_digest, DROP COLUMN approval_ref;
        DROP TABLE public.trusted_delivery_policy_transitions;"""
    )