"""add signature and session fields to payment_request_authorizations

Adds three HPP-related columns to payment_requests_authorizations:
  - signature_file_id    : FK → files.id — the S3 file record for a
                           drawn signature PNG (HWHPP-303, SIGNATURE auth)
  - verified_phone_last4 : last 4 digits of the SMS-verified phone number
                           (HWHPP-304, SMS_OTP auth)
  - hpp_session_id       : FK → hpp_sessions.id — links the authorization
                           record to the HPP session that produced it,
                           providing a full IP/geo audit trail

All three columns are nullable; they are populated only for HPP-sourced
authorizations of the respective authorization_type.

Depends on: e1f2a3b4c5d6 (hpp_sessions table must exist before adding FK)

Revision ID: d6e7f8a9b0c1
Revises: c5d6e7f8a9b0
Create Date: 2026-03-10 00:05: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 = 'd6e7f8a9b0c1'
down_revision: Union[str, Sequence[str], None] = 'c5d6e7f8a9b0'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    conn = op.get_bind()
    inspector = sa_inspect(conn)
    existing_columns = {c['name'] for c in inspector.get_columns('payment_requests_authorizations')}
    existing_fks = {fk['name'] for fk in inspector.get_foreign_keys('payment_requests_authorizations')}
    existing_indexes = {idx['name'] for idx in inspector.get_indexes('payment_requests_authorizations')}

    # ── 1. signature_file_id ─────────────────────────────────────────────────
    if 'signature_file_id' not in existing_columns:
        op.add_column(
            'payment_requests_authorizations',
            sa.Column('signature_file_id', sa.Integer(), nullable=True),
        )
    if 'fk_payment_requests_authorizations_signature_file_id' not in existing_fks:
        op.create_foreign_key(
            'fk_payment_requests_authorizations_signature_file_id',
            'payment_requests_authorizations', 'files',
            ['signature_file_id'], ['id'],
        )
    if 'ix_payment_requests_authorizations_signature_file_id' not in existing_indexes:
        op.create_index(
            'ix_payment_requests_authorizations_signature_file_id',
            'payment_requests_authorizations',
            ['signature_file_id'],
            unique=False,
        )

    # ── 2. verified_phone_last4 ───────────────────────────────────────────────
    if 'verified_phone_last4' not in existing_columns:
        op.add_column(
            'payment_requests_authorizations',
            sa.Column('verified_phone_last4', sa.String(length=4), nullable=True),
        )

    # ── 3. hpp_session_id ────────────────────────────────────────────────────
    if 'hpp_session_id' not in existing_columns:
        op.add_column(
            'payment_requests_authorizations',
            sa.Column('hpp_session_id', sa.Integer(), nullable=True),
        )
    if 'fk_payment_requests_authorizations_hpp_session_id' not in existing_fks:
        op.create_foreign_key(
            'fk_payment_requests_authorizations_hpp_session_id',
            'payment_requests_authorizations', 'hpp_sessions',
            ['hpp_session_id'], ['id'],
        )
    if 'ix_payment_requests_authorizations_hpp_session_id' not in existing_indexes:
        op.create_index(
            'ix_payment_requests_authorizations_hpp_session_id',
            'payment_requests_authorizations',
            ['hpp_session_id'],
            unique=False,
        )


def downgrade() -> None:
    op.drop_constraint(
        'fk_payment_requests_authorizations_hpp_session_id',
        'payment_requests_authorizations',
        type_='foreignkey',
    )
    op.drop_index(
        'ix_payment_requests_authorizations_hpp_session_id',
        table_name='payment_requests_authorizations',
    )
    op.drop_column('payment_requests_authorizations', 'hpp_session_id')

    op.drop_column('payment_requests_authorizations', 'verified_phone_last4')

    op.drop_constraint(
        'fk_payment_requests_authorizations_signature_file_id',
        'payment_requests_authorizations',
        type_='foreignkey',
    )
    op.drop_index(
        'ix_payment_requests_authorizations_signature_file_id',
        table_name='payment_requests_authorizations',
    )
    op.drop_column('payment_requests_authorizations', 'signature_file_id')