"""Persist P3-WP06 trusted-delivery control profiles and release gates."""

from alembic import op

revision = "20260811_484"
down_revision = "20260811_483"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE public.trusted_delivery_control_profile_versions (
          uid UUID PRIMARY KEY, profile_id VARCHAR(40) NOT NULL, version VARCHAR(40) NOT NULL,
          status VARCHAR(20) NOT NULL CHECK(status IN ('draft','active','retired')),
          profile JSONB NOT NULL CHECK(jsonb_typeof(profile)='object'),
          profile_digest CHAR(64) NOT NULL CHECK(profile_digest ~ '^[0-9a-f]{64}$'),
          created_by UUID NOT NULL REFERENCES public.users(id), current_version INTEGER NOT NULL DEFAULT 1 CHECK(current_version>0),
          created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(), updated_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
          UNIQUE(profile_id,version)
        );
        CREATE UNIQUE INDEX uq_trusted_delivery_active_control_profile
          ON public.trusted_delivery_control_profile_versions(profile_id) WHERE status='active';
        CREATE TABLE public.trusted_delivery_control_profile_transitions (
          uid UUID PRIMARY KEY, profile_uid UUID NOT NULL REFERENCES public.trusted_delivery_control_profile_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()
        );
        CREATE TABLE public.trusted_delivery_capability_approvals (
          uid UUID PRIMARY KEY, provider VARCHAR(20) NOT NULL CHECK(provider IN ('kms','dlp','siem','iam','target')),
          capability VARCHAR(80) NOT NULL, version VARCHAR(40) NOT NULL,
          config_digest CHAR(64) NOT NULL CHECK(config_digest ~ '^[0-9a-f]{64}$'),
          status VARCHAR(20) NOT NULL CHECK(status IN ('approved','revoked')),
          idempotency_key VARCHAR(160) NOT NULL UNIQUE, request_digest CHAR(64) NOT NULL CHECK(request_digest ~ '^[0-9a-f]{64}$'),
          created_by UUID NOT NULL REFERENCES public.users(id), created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
          UNIQUE(provider,capability,version)
        );
        CREATE UNIQUE INDEX uq_trusted_delivery_active_capability
          ON public.trusted_delivery_capability_approvals(provider,capability) WHERE status='approved';
        CREATE TABLE public.trusted_delivery_supply_chain_gates (
          uid UUID PRIMARY KEY, artifact_digest CHAR(64) NOT NULL CHECK(artifact_digest ~ '^[0-9a-f]{64}$'),
          sbom_digest CHAR(64) NOT NULL CHECK(sbom_digest ~ '^[0-9a-f]{64}$'),
          license_policy_digest CHAR(64) NOT NULL CHECK(license_policy_digest ~ '^[0-9a-f]{64}$'),
          vulnerability_scan_digest CHAR(64) NOT NULL CHECK(vulnerability_scan_digest ~ '^[0-9a-f]{64}$'),
          approval_id VARCHAR(160) NOT NULL, approval_version VARCHAR(40) NOT NULL,
          evidence_expires_at TIMESTAMPTZ NOT NULL, decision_code VARCHAR(80) NOT NULL CHECK(decision_code IN ('RELEASE_GATE_APPROVED','RELEASE_GATE_REJECTED')),
          idempotency_key VARCHAR(160) NOT NULL UNIQUE, request_digest CHAR(64) NOT NULL CHECK(request_digest ~ '^[0-9a-f]{64}$'),
          created_by UUID NOT NULL REFERENCES public.users(id), created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
        );
        CREATE TABLE public.trusted_delivery_control_evidence (
          uid UUID PRIMARY KEY, evidence_ref VARCHAR(200) NOT NULL UNIQUE,
          evidence_digest CHAR(64) NOT NULL CHECK(evidence_digest ~ '^[0-9a-f]{64}$'), decision_code VARCHAR(120) NOT NULL,
          gate_uid UUID REFERENCES public.trusted_delivery_supply_chain_gates(uid), actor_uid UUID NOT NULL REFERENCES public.users(id),
          created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
        );
        CREATE OR REPLACE FUNCTION public.protect_trusted_delivery_control_evidence() RETURNS trigger LANGUAGE plpgsql AS $$
        BEGIN RAISE EXCEPTION 'trusted delivery control evidence is append-only'; END $$;
        CREATE TRIGGER trg_trusted_delivery_control_evidence_append_only BEFORE UPDATE OR DELETE OR TRUNCATE
          ON public.trusted_delivery_control_evidence FOR EACH STATEMENT EXECUTE FUNCTION public.protect_trusted_delivery_control_evidence();
        CREATE TABLE public.trusted_delivery_destruction_approvals (
          uid UUID PRIMARY KEY, asset_uid UUID NOT NULL, approval_ref_one VARCHAR(200) NOT NULL, approval_ref_two VARCHAR(200) NOT NULL,
          evidence_digest CHAR(64) NOT NULL CHECK(evidence_digest ~ '^[0-9a-f]{64}$'), status VARCHAR(20) NOT NULL CHECK(status IN ('approved','blocked')),
          idempotency_key VARCHAR(160) NOT NULL UNIQUE, request_digest CHAR(64) NOT NULL CHECK(request_digest ~ '^[0-9a-f]{64}$'),
          created_by UUID NOT NULL REFERENCES public.users(id), created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
          CHECK(approval_ref_one<>approval_ref_two)
        );
        CREATE OR REPLACE FUNCTION public.block_trusted_delivery_destruction_on_hold() RETURNS trigger LANGUAGE plpgsql AS $$
        BEGIN
          IF EXISTS(SELECT 1 FROM public.trusted_delivery_legal_holds WHERE asset_uid=NEW.asset_uid AND status='active') THEN
            RAISE EXCEPTION 'destruction blocked by active legal hold';
          END IF;
          RETURN NEW;
        END $$;
        CREATE TRIGGER trg_trusted_delivery_destruction_hold BEFORE INSERT ON public.trusted_delivery_destruction_approvals
          FOR EACH ROW EXECUTE FUNCTION public.block_trusted_delivery_destruction_on_hold();
        """
    )


def downgrade() -> None:
    op.execute(
        """DO $$ BEGIN
          IF EXISTS(SELECT 1 FROM public.trusted_delivery_control_profile_versions)
             OR EXISTS(SELECT 1 FROM public.trusted_delivery_capability_approvals)
             OR EXISTS(SELECT 1 FROM public.trusted_delivery_supply_chain_gates)
             OR EXISTS(SELECT 1 FROM public.trusted_delivery_control_evidence)
             OR EXISTS(SELECT 1 FROM public.trusted_delivery_destruction_approvals) THEN
            RAISE EXCEPTION 'downgrade requires approved control archival migration';
          END IF;
        END $$;
        DROP TABLE public.trusted_delivery_destruction_approvals;
        DROP FUNCTION public.block_trusted_delivery_destruction_on_hold();
        DROP TABLE public.trusted_delivery_control_evidence;
        DROP FUNCTION public.protect_trusted_delivery_control_evidence();
        DROP TABLE public.trusted_delivery_supply_chain_gates;
        DROP TABLE public.trusted_delivery_capability_approvals;
        DROP TABLE public.trusted_delivery_control_profile_transitions;
        DROP TABLE public.trusted_delivery_control_profile_versions;"""
    )