"""add reviewed account privacy export and erasure lifecycle

Revision ID: a6c9e2f4b781
Revises: 4d8a2f6c9b31
Create Date: 2026-08-16 23:58:00
"""

from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


revision: str = "a6c9e2f4b781"
down_revision: Union[str, Sequence[str], None] = "4d8a2f6c9b31"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        "privacy_requests",
        sa.Column("id", sa.String(length=128), nullable=False),
        sa.Column("user_id", sa.String(length=128), nullable=False),
        sa.Column("request_type", sa.String(length=32), nullable=False),
        sa.Column("status", sa.String(length=32), nullable=False),
        sa.Column("requested_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("request_digest", sa.LargeBinary(length=32), nullable=False),
        sa.Column("idempotency_key", sa.String(length=128), nullable=False),
        sa.Column("correlation_id", sa.String(length=128), nullable=False),
        sa.Column("state_generation", sa.Integer(), nullable=False),
        sa.Column("cancelled_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("completed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("completed_by_user_id", sa.String(length=128), nullable=True),
        sa.Column("completion_reason", sa.String(length=64), nullable=True),
        sa.Column("completion_idempotency_key", sa.String(length=128), nullable=True),
        sa.Column("completion_request_digest", sa.LargeBinary(length=32), nullable=True),
        sa.Column("completion_summary_json", sa.JSON(), nullable=False),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.func.now(),
            nullable=False,
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.func.now(),
            nullable=False,
        ),
        sa.CheckConstraint(
            "request_type = 'deletion'",
            name="ck_privacy_requests_type",
        ),
        sa.CheckConstraint(
            "status IN ('pending', 'cancelled', 'completed')",
            name="ck_privacy_requests_status",
        ),
        sa.CheckConstraint(
            "state_generation >= 0",
            name="ck_privacy_requests_generation",
        ),
        sa.CheckConstraint(
            "(status = 'pending' AND cancelled_at IS NULL AND completed_at IS NULL "
            "AND completed_by_user_id IS NULL AND completion_reason IS NULL "
            "AND completion_idempotency_key IS NULL "
            "AND completion_request_digest IS NULL) OR "
            "(status = 'cancelled' AND cancelled_at IS NOT NULL "
            "AND completed_at IS NULL AND completed_by_user_id IS NULL "
            "AND completion_reason IS NULL AND completion_idempotency_key IS NULL "
            "AND completion_request_digest IS NULL) OR "
            "(status = 'completed' AND cancelled_at IS NULL "
            "AND completed_at IS NOT NULL AND completed_by_user_id IS NOT NULL "
            "AND completion_reason IS NOT NULL "
            "AND completion_idempotency_key IS NOT NULL "
            "AND completion_request_digest IS NOT NULL)",
            name="ck_privacy_requests_lifecycle",
        ),
        sa.ForeignKeyConstraint(["completed_by_user_id"], ["users.id"]),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"]),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "user_id",
            "idempotency_key",
            name="uq_privacy_requests_user_idempotency",
        ),
    )
    op.create_index(
        "ix_privacy_requests_status_requested",
        "privacy_requests",
        ["status", "requested_at"],
        unique=False,
    )
    op.create_index(
        "uq_privacy_requests_pending_user",
        "privacy_requests",
        ["user_id"],
        unique=True,
        sqlite_where=sa.text("status = 'pending'"),
        postgresql_where=sa.text("status = 'pending'"),
    )


def downgrade() -> None:
    op.drop_index(
        "uq_privacy_requests_pending_user",
        table_name="privacy_requests",
    )
    op.drop_index(
        "ix_privacy_requests_status_requested",
        table_name="privacy_requests",
    )
    op.drop_table("privacy_requests")