"""verification plugin rules and material retention

Revision ID: 068_verification_plugin_rules
Revises: 067_user_verification
Create Date: 2026-06-22
"""

from alembic import op
import sqlalchemy as sa

revision = "068_verification_plugin_rules"
down_revision = "067_user_verification"
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 upgrade() -> None:
    bind = op.get_bind()

    if _table_exists(bind, "products"):
        columns = _column_names(bind, "products")
        if "requires_real_name" not in columns:
            op.add_column(
                "products",
                sa.Column("requires_real_name", sa.Boolean(), nullable=False, server_default=sa.text("0")),
            )

    if _table_exists(bind, "product_groups"):
        columns = _column_names(bind, "product_groups")
        if "requires_real_name" not in columns:
            op.add_column(
                "product_groups",
                sa.Column("requires_real_name", sa.Boolean(), nullable=False, server_default=sa.text("0")),
            )

    if _table_exists(bind, "user_verification_records"):
        columns = _column_names(bind, "user_verification_records")
        if "mode" not in columns:
            op.add_column(
                "user_verification_records",
                sa.Column("mode", sa.String(32), nullable=False, server_default="two_factor"),
            )
        if "material_expires_at" not in columns:
            op.add_column(
                "user_verification_records",
                sa.Column("material_expires_at", sa.DateTime(timezone=True), nullable=True),
            )
        if "material_cleaned" not in columns:
            op.add_column(
                "user_verification_records",
                sa.Column("material_cleaned", sa.Boolean(), nullable=False, server_default=sa.text("0")),
            )


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

    if _table_exists(bind, "user_verification_records"):
        columns = _column_names(bind, "user_verification_records")
        for column in ("material_cleaned", "material_expires_at", "mode"):
            if column in columns:
                op.drop_column("user_verification_records", column)

    if _table_exists(bind, "product_groups"):
        columns = _column_names(bind, "product_groups")
        if "requires_real_name" in columns:
            op.drop_column("product_groups", "requires_real_name")

    if _table_exists(bind, "products"):
        columns = _column_names(bind, "products")
        if "requires_real_name" in columns:
            op.drop_column("products", "requires_real_name")
