"""Persist P3-WP06 trusted-delivery grants without external credentials or raw data."""

from alembic import op

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


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE public.trusted_delivery_policy_versions (
          uid UUID PRIMARY KEY, code VARCHAR(120) NOT NULL, version VARCHAR(30) NOT NULL,
          status VARCHAR(20) NOT NULL CHECK(status IN ('draft','active','retired')),
          selector JSONB NOT NULL, resource_rules JSONB NOT NULL,
          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(),
          UNIQUE(code,version), CHECK(jsonb_typeof(selector)='object'), CHECK(jsonb_typeof(resource_rules)='object')
        );
        CREATE UNIQUE INDEX uq_trusted_delivery_active_policy ON public.trusted_delivery_policy_versions(code) WHERE status='active';
        CREATE TABLE public.trusted_delivery_grants (
          uid UUID PRIMARY KEY, policy_uid UUID NOT NULL REFERENCES public.trusted_delivery_policy_versions(uid),
          subject_uid UUID NOT NULL REFERENCES public.users(id), asset_uid UUID NOT NULL,
          provider VARCHAR(20) NOT NULL CHECK(provider IN ('database','api','file','iam','kms','dlp','siem')),
          idempotency_key VARCHAR(160) NOT NULL UNIQUE, request_digest CHAR(64) NOT NULL CHECK(request_digest ~ '^[0-9a-f]{64}$'),
          expires_at TIMESTAMPTZ NOT NULL, status VARCHAR(20) NOT NULL CHECK(status IN ('active','revoked','failed','held')),
          reason_code VARCHAR(80) NOT NULL, current_version INTEGER NOT NULL DEFAULT 1 CHECK(current_version>0),
          created_by UUID NOT NULL REFERENCES public.users(id), created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(), updated_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
        );
        CREATE INDEX ix_trusted_delivery_grants_expiry ON public.trusted_delivery_grants(status,expires_at);
        CREATE TABLE public.trusted_delivery_deliveries (
          uid UUID PRIMARY KEY, grant_uid UUID NOT NULL REFERENCES public.trusted_delivery_grants(uid),
          idempotency_key VARCHAR(160) NOT NULL UNIQUE, request_digest CHAR(64) NOT NULL CHECK(request_digest ~ '^[0-9a-f]{64}$'),
          status VARCHAR(20) NOT NULL CHECK(status IN ('pending','processing','applied','dead_letter')),
          attempt_count INTEGER NOT NULL DEFAULT 0 CHECK(attempt_count BETWEEN 0 AND 3), next_attempt_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
          lease_owner VARCHAR(80), lease_fence BIGINT NOT NULL DEFAULT 0 CHECK(lease_fence>=0), lease_expires_at TIMESTAMPTZ,
          created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(), updated_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
          CHECK((status='processing')=(lease_owner IS NOT NULL AND lease_expires_at IS NOT NULL))
        );
        CREATE INDEX ix_trusted_delivery_delivery_claim ON public.trusted_delivery_deliveries(status,next_attempt_at);
        CREATE TABLE public.trusted_delivery_receipts (
          uid UUID PRIMARY KEY, delivery_uid UUID NOT NULL UNIQUE REFERENCES public.trusted_delivery_deliveries(uid),
          grant_uid UUID NOT NULL REFERENCES public.trusted_delivery_grants(uid), status VARCHAR(20) NOT NULL CHECK(status IN ('applied','failed')),
          receipt_code VARCHAR(120) NOT NULL, request_digest CHAR(64) NOT NULL CHECK(request_digest ~ '^[0-9a-f]{64}$'),
          response_digest CHAR(64) NOT NULL CHECK(response_digest ~ '^[0-9a-f]{64}$'), diff_digest CHAR(64) NOT NULL CHECK(diff_digest ~ '^[0-9a-f]{64}$'),
          created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
        );
        CREATE OR REPLACE FUNCTION public.protect_trusted_delivery_receipt() RETURNS trigger LANGUAGE plpgsql AS $$
        BEGIN RAISE EXCEPTION 'trusted delivery receipt is append-only'; END $$;
        CREATE TRIGGER trg_trusted_delivery_receipt_append_only BEFORE UPDATE OR DELETE OR TRUNCATE ON public.trusted_delivery_receipts FOR EACH STATEMENT EXECUTE FUNCTION public.protect_trusted_delivery_receipt();
        CREATE TABLE public.trusted_delivery_legal_holds (
          uid UUID PRIMARY KEY, asset_uid UUID NOT NULL, operation VARCHAR(30) NOT NULL CHECK(operation IN ('freeze','export_review','destruction_review')),
          status VARCHAR(20) NOT NULL CHECK(status IN ('pending','active','released')),
          approver_ref_one VARCHAR(200) NOT NULL, approver_ref_two VARCHAR(200) NOT NULL, evidence_digest CHAR(64) NOT NULL CHECK(evidence_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(),
          CHECK(approver_ref_one<>approver_ref_two)
        );
        CREATE UNIQUE INDEX uq_trusted_delivery_active_hold ON public.trusted_delivery_legal_holds(asset_uid) WHERE status='active';
        CREATE TABLE public.trusted_delivery_evidence (
          uid UUID PRIMARY KEY, action VARCHAR(80) NOT NULL, actor_uid UUID NOT NULL REFERENCES public.users(id),
          safe_detail JSONB NOT NULL DEFAULT '{}'::jsonb, created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(), CHECK(jsonb_typeof(safe_detail)='object')
        );
        CREATE OR REPLACE FUNCTION public.protect_trusted_delivery_evidence() RETURNS trigger LANGUAGE plpgsql AS $$
        BEGIN RAISE EXCEPTION 'trusted delivery evidence is append-only'; END $$;
        CREATE TRIGGER trg_trusted_delivery_evidence_append_only BEFORE UPDATE OR DELETE OR TRUNCATE ON public.trusted_delivery_evidence FOR EACH STATEMENT EXECUTE FUNCTION public.protect_trusted_delivery_evidence();
        """
    )


def downgrade() -> None:
    op.execute(
        """DO $$ BEGIN
          IF EXISTS(SELECT 1 FROM public.trusted_delivery_policy_versions) OR EXISTS(SELECT 1 FROM public.trusted_delivery_grants)
             OR EXISTS(SELECT 1 FROM public.trusted_delivery_deliveries) OR EXISTS(SELECT 1 FROM public.trusted_delivery_receipts)
             OR EXISTS(SELECT 1 FROM public.trusted_delivery_legal_holds) OR EXISTS(SELECT 1 FROM public.trusted_delivery_evidence) THEN
            RAISE EXCEPTION 'downgrade requires approved archival migration';
          END IF;
        END $$;
        DROP TABLE public.trusted_delivery_evidence;
        DROP FUNCTION public.protect_trusted_delivery_evidence();
        DROP TABLE public.trusted_delivery_legal_holds;
        DROP TABLE public.trusted_delivery_receipts;
        DROP FUNCTION public.protect_trusted_delivery_receipt();
        DROP TABLE public.trusted_delivery_deliveries;
        DROP TABLE public.trusted_delivery_grants;
        DROP TABLE public.trusted_delivery_policy_versions;"""
    )