"""secure verification identity fields

Revision ID: 071_secure_verification_identity
Revises: 070_provision_interfaces
Create Date: 2026-06-26
"""

from alembic import op
import sqlalchemy as sa


revision = "071_secure_verification_identity"
down_revision = "070_provision_interfaces"
branch_labels = None
depends_on = None


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


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 _index_names(bind: sa.engine.Connection, table_name: str) -> set[str]:
    return {index["name"] for index in sa.inspect(bind).get_indexes(table_name)}


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

    if _table_exists(bind, "users"):
        columns = _column_names(bind, "users")
        if "id_card_no_hash" not in columns:
            op.add_column("users", sa.Column("id_card_no_hash", sa.String(64), nullable=True))
        indexes = _index_names(bind, "users")
        if "ix_users_id_card_no_hash" not in indexes:
            op.create_index("ix_users_id_card_no_hash", "users", ["id_card_no_hash"], unique=True)
        if dialect != "sqlite":
            if "id_card_no" in columns:
                op.alter_column("users", "id_card_no", existing_type=sa.String(32), type_=sa.String(255), nullable=True)
            if "real_name" in columns:
                op.alter_column("users", "real_name", existing_type=sa.String(50), type_=sa.String(255), nullable=True)

    if _table_exists(bind, "user_verification_records"):
        columns = _column_names(bind, "user_verification_records")
        if "id_card_no_hash" not in columns:
            op.add_column("user_verification_records", sa.Column("id_card_no_hash", sa.String(64), nullable=True))
        indexes = _index_names(bind, "user_verification_records")
        if "ix_user_verification_records_id_card_no_hash" not in indexes:
            op.create_index(
                "ix_user_verification_records_id_card_no_hash",
                "user_verification_records",
                ["id_card_no_hash"],
            )
        if dialect != "sqlite":
            if "id_card_no" in columns:
                op.alter_column(
                    "user_verification_records",
                    "id_card_no",
                    existing_type=sa.String(32),
                    type_=sa.String(255),
                    nullable=True,
                )
            if "real_name" in columns:
                op.alter_column(
                    "user_verification_records",
                    "real_name",
                    existing_type=sa.String(50),
                    type_=sa.String(255),
                    nullable=True,
                )


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, "user_verification_records"):
        indexes = _index_names(bind, "user_verification_records")
        if "ix_user_verification_records_id_card_no_hash" in indexes:
            op.drop_index("ix_user_verification_records_id_card_no_hash", table_name="user_verification_records")
        columns = _column_names(bind, "user_verification_records")
        if "id_card_no_hash" in columns:
            op.drop_column("user_verification_records", "id_card_no_hash")

    if _table_exists(bind, "users"):
        indexes = _index_names(bind, "users")
        if "ix_users_id_card_no_hash" in indexes:
            op.drop_index("ix_users_id_card_no_hash", table_name="users")
        columns = _column_names(bind, "users")
        if "id_card_no_hash" in columns:
            op.drop_column("users", "id_card_no_hash")
