"""add user verification fields and verification_records table

Revision ID: 067_user_verification
Revises: 066_zjmf_finance_foundation
Create Date: 2026-06-22
"""

from alembic import op
import sqlalchemy as sa

revision = "067_user_verification"
down_revision = "066_zjmf_finance_foundation"
branch_labels = None
depends_on = None


def _column_names(bind: sa.engine.Connection, table_name: str) -> set[str]:
    return {column["name"] for column in sa.inspect(bind).get_columns(table_name)}


def _table_exists(bind: sa.engine.Connection, table_name: str) -> bool:
    return table_name in set(sa.inspect(bind).get_table_names())


def upgrade() -> None:
    bind = op.get_bind()

    # --- extend users table ---
    if _table_exists(bind, "users"):
        columns = _column_names(bind, "users")
        if "real_name" not in columns:
            op.add_column("users", sa.Column("real_name", sa.String(50), nullable=True))
        if "id_card_verified" not in columns:
            op.add_column(
                "users",
                sa.Column(
                    "id_card_verified",
                    sa.String(20),
                    nullable=False,
                    server_default="unverified",
                ),
            )
        if "id_card_verified_at" not in columns:
            op.add_column(
                "users", sa.Column("id_card_verified_at", sa.DateTime(timezone=True), nullable=True)
            )
        if "id_card_front_image" not in columns:
            op.add_column("users", sa.Column("id_card_front_image", sa.String(500), nullable=True))
        if "id_card_back_image" not in columns:
            op.add_column("users", sa.Column("id_card_back_image", sa.String(500), nullable=True))
        if "id_card_face_image" not in columns:
            op.add_column("users", sa.Column("id_card_face_image", sa.String(500), nullable=True))
        if "id_card_verify_remark" not in columns:
            op.add_column("users", sa.Column("id_card_verify_remark", sa.Text, nullable=True))

    # --- create user_verification_records table ---
    if not _table_exists(bind, "user_verification_records"):
        op.create_table(
            "user_verification_records",
            sa.Column("id", sa.Integer, primary_key=True, autoincrement=True),
            sa.Column("user_id", sa.BigInteger(), sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True),
            sa.Column("real_name", sa.String(50), nullable=True),
            sa.Column("id_card_no", sa.String(32), nullable=True),
            sa.Column("id_card_front_image", sa.String(500), nullable=True),
            sa.Column("id_card_back_image", sa.String(500), nullable=True),
            sa.Column("id_card_face_image", sa.String(500), nullable=True),
            sa.Column("provider", sa.String(50), nullable=False, server_default="baidu"),
            sa.Column("provider_task_id", sa.String(128), nullable=True, index=True),
            sa.Column("status", sa.String(20), nullable=False, server_default="pending"),
            sa.Column("provider_response", sa.JSON, nullable=True),
            sa.Column("verified_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("rejected_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("reject_reason", sa.Text, nullable=True),
            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(), onupdate=sa.func.now(), nullable=False),
        )


def downgrade() -> None:
    bind = op.get_bind()

    if _table_exists(bind, "user_verification_records"):
        op.drop_table("user_verification_records")

    if _table_exists(bind, "users"):
        columns = _column_names(bind, "users")
        for col in (
            "real_name",
            "id_card_verified",
            "id_card_verified_at",
            "id_card_front_image",
            "id_card_back_image",
            "id_card_face_image",
            "id_card_verify_remark",
        ):
            if col in columns:
                op.drop_column("users", col)
