"""Persist two-phase identity-sync mass-action confirmations (#2275).

Revision ID: 441
Revises: 440
Create Date: 2026-08-02

Only SHA-256 token digests are stored. The immutable target/action/item
fingerprint and one-time consumed status bind an approval to one exact sweep.
"""

from __future__ import annotations

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import id_type_for, table_kw_for

revision = "441"
down_revision = "440"
branch_labels = None
depends_on = None

TABLE = "identity_sync_destructive_plans"


def upgrade() -> None:
    target_t = id_type_for("identity_sync_targets")
    run_t = id_type_for("identity_sync_runs")
    person_t = id_type_for("identity_persons")
    op.create_table(
        TABLE,
        sa.Column("id", sa.CHAR(36), primary_key=True),
        sa.Column(
            "target_id",
            target_t,
            sa.ForeignKey("identity_sync_targets.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("scope", sa.String(32), nullable=False),
        sa.Column("action", sa.String(24), nullable=False),
        sa.Column("fingerprint", sa.String(64), nullable=False),
        sa.Column("target_snapshot_hash", sa.String(64), nullable=False),
        sa.Column("item_count", sa.Integer(), nullable=False),
        sa.Column("item_snapshot", sa.JSON(), nullable=False),
        sa.Column("status", sa.String(16), nullable=False, server_default="pending"),
        sa.Column("plan_expires_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("confirmation_token_hash", sa.String(64), nullable=True),
        sa.Column(
            "confirmed_by",
            person_t,
            sa.ForeignKey("identity_persons.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column("confirmed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("confirmation_expires_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "created_run_id",
            run_t,
            sa.ForeignKey("identity_sync_runs.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column(
            "consumed_run_id",
            run_t,
            sa.ForeignKey("identity_sync_runs.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column(
            "consumed_by",
            person_t,
            sa.ForeignKey("identity_persons.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column("consumed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.func.now(),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.func.now(),
        ),
        **table_kw_for("identity_sync_targets"),
    )
    op.create_index(
        "ix_idsync_destructive_target_status",
        TABLE,
        ["target_id", "status", "created_at"],
    )
    op.create_index(
        "ix_idsync_destructive_target_scope_fp",
        TABLE,
        ["target_id", "scope", "fingerprint"],
    )
    op.create_index(
        "ix_idsync_destructive_token_hash",
        TABLE,
        ["confirmation_token_hash"],
    )


def downgrade() -> None:
    op.drop_table(TABLE)