"""add hpp_sessions and hpp_otp_tokens tables

Revision ID: e1f2a3b4c5d6
Revises: d4e5f6a7b8c9
Create Date: 2026-03-10 00:00:00.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy import inspect as sa_inspect


# revision identifiers, used by Alembic.
revision: str = 'e1f2a3b4c5d6'
down_revision: Union[str, Sequence[str], None] = 'd4e5f6a7b8c9'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    conn = op.get_bind()
    existing_tables = sa_inspect(conn).get_table_names()

    # ── 1. hpp_sessions ──────────────────────────────────────────────────────
    # Guard: table may already exist if created by a parallel branch (rbac007a8b9c0d).
    if 'hpp_sessions' not in existing_tables:
        op.create_table(
            'hpp_sessions',
            sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
            sa.Column('payment_request_id', sa.Integer(), nullable=False),
            sa.Column('token', sa.String(length=255), nullable=False),
            sa.Column('ip_address', sa.String(length=45), nullable=True),
            sa.Column('user_agent', sa.Text(), nullable=True),
            sa.Column('geo_city', sa.String(length=100), nullable=True),
            sa.Column('geo_region', sa.String(length=100), nullable=True),
            sa.Column('geo_country', sa.String(length=100), nullable=True),
            sa.Column('geo_isp', sa.String(length=255), nullable=True),
            sa.Column(
                'initiated_at',
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.Column('submitted_at', sa.DateTime(timezone=True), nullable=True),
            sa.Column('deleted_at', sa.DateTime(timezone=True), nullable=True),
            sa.ForeignKeyConstraint(
                ['payment_request_id'], ['payment_requests.id'],
                name='fk_hpp_sessions_payment_request_id',
            ),
            sa.PrimaryKeyConstraint('id'),
        )
        op.create_index(op.f('ix_hpp_sessions_id'), 'hpp_sessions', ['id'], unique=False)
        op.create_index(
            'ix_hpp_sessions_payment_request_id',
            'hpp_sessions',
            ['payment_request_id'],
            unique=False,
        )
        op.create_index(
            'ix_hpp_sessions_token',
            'hpp_sessions',
            ['token'],
            unique=False,
        )

    # ── 2. hpp_otp_tokens ────────────────────────────────────────────────────
    # Guard: table may already exist if created by a parallel branch (rbac007a8b9c0d).
    if 'hpp_otp_tokens' not in existing_tables:
        op.create_table(
            'hpp_otp_tokens',
            sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
            sa.Column('payment_request_id', sa.Integer(), nullable=False),
            sa.Column('hashed_otp', sa.String(length=255), nullable=False),
            sa.Column('phone_last4', sa.String(length=4), nullable=False),
            sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False),
            sa.Column('used_at', sa.DateTime(timezone=True), nullable=True),
            sa.Column('send_count', sa.Integer(), nullable=False, server_default=sa.text('1')),
            sa.Column('attempt_count', sa.Integer(), nullable=False, server_default=sa.text('0')),
            sa.Column('is_verified', sa.Boolean(), nullable=False, server_default=sa.text('false')),
            sa.Column(
                'created_at',
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.ForeignKeyConstraint(
                ['payment_request_id'], ['payment_requests.id'],
                name='fk_hpp_otp_tokens_payment_request_id',
            ),
            sa.PrimaryKeyConstraint('id'),
        )
        op.create_index(op.f('ix_hpp_otp_tokens_id'), 'hpp_otp_tokens', ['id'], unique=False)
        op.create_index(
            'ix_hpp_otp_tokens_payment_request_id',
            'hpp_otp_tokens',
            ['payment_request_id'],
            unique=False,
        )
        op.create_index(
            'ix_hpp_otp_tokens_active_lookup',
            'hpp_otp_tokens',
            ['payment_request_id', 'used_at', 'expires_at'],
            unique=False,
        )


def downgrade() -> None:
    conn = op.get_bind()
    existing_tables = sa_inspect(conn).get_table_names()

    if 'hpp_otp_tokens' in existing_tables:
        op.drop_index('ix_hpp_otp_tokens_active_lookup', table_name='hpp_otp_tokens')
        op.drop_index('ix_hpp_otp_tokens_payment_request_id', table_name='hpp_otp_tokens')
        op.drop_index(op.f('ix_hpp_otp_tokens_id'), table_name='hpp_otp_tokens')
        op.drop_table('hpp_otp_tokens')

    if 'hpp_sessions' in existing_tables:
        op.drop_index('ix_hpp_sessions_token', table_name='hpp_sessions')
        op.drop_index('ix_hpp_sessions_payment_request_id', table_name='hpp_sessions')
        op.drop_index(op.f('ix_hpp_sessions_id'), table_name='hpp_sessions')
        op.drop_table('hpp_sessions')