from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

# ─────────────────────────────────────────────────────────────
revision: str = "cf80ac9c98fd"
down_revision: Union[str, Sequence[str], None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None
# ─────────────────────────────────────────────────────────────


def upgrade() -> None:
    """Create users and user_profile with portable types (no PG ENUM/JSONB)."""
    # Create "users" table
    op.create_table(
        "users",
        sa.Column("id", sa.Integer(), primary_key=True, nullable=False),
        sa.Column("tg_id", sa.String(length=64), nullable=False, unique=True),
        sa.Column("user_name", sa.String(length=128), nullable=True),

        # Use plain string for enums (portable): stored as VARCHAR with optional app-level validation
        sa.Column("user_gender", sa.String(length=32), nullable=True),

        sa.Column("city", sa.String(length=128), nullable=True),

        # Timestamps: use SQL-standard CURRENT_TIMESTAMP; app code should set/refresh updated_at
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
        ),
    )

    # Create "user_profile" table
    op.create_table(
        "user_profile",
        sa.Column("id", sa.Integer(), primary_key=True, nullable=False),
        sa.Column(
            "user_id",
            sa.Integer(),
            sa.ForeignKey("users.id", ondelete="CASCADE"),
            nullable=False,
            unique=True,
        ),

        # Enum-like fields as plain strings (portable)
        sa.Column("where_practicing", sa.String(length=32), nullable=True),
        sa.Column("have_clients", sa.String(length=32), nullable=True),
        sa.Column("massage_technique", sa.String(length=32), nullable=True),
        sa.Column("social_skill", sa.String(length=32), nullable=True),
        sa.Column("communication_ease", sa.String(length=32), nullable=True),

        # Portable JSON (works across PostgreSQL/MySQL/SQLite)
        sa.Column("raw_json", sa.JSON(), nullable=True),

        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
        ),
    )

    # Optional: lightweight indices for lookups (safe across DBs)
    op.create_index("ix_users_tg_id", "users", ["tg_id"], unique=True)
    op.create_index("ix_user_profile_user_id", "user_profile", ["user_id"], unique=True)


def downgrade() -> None:
    """Drop user_profile and users (reverse order for FK safety)."""
    op.drop_index("ix_user_profile_user_id", table_name="user_profile")
    op.drop_index("ix_users_tg_id", table_name="users")
    op.drop_table("user_profile")
    op.drop_table("users")