"""add_provider_transactions_table

Stores the raw payment-provider response for each transaction so that
provider-level details (approval code, card type, masked card, AVS/CVV
codes, host reference, etc.) are persisted in a dedicated audit table
and can be queried independently of the JSON txn_metadata column.

Revision ID: tsys002_add_provider_transactions_table
Revises: tsys001_seed_tsys
Create Date: 2026-04-14
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql


# revision identifiers, used by Alembic.
revision = "tsys002_provider_transactions"
down_revision = "tsys001_seed_tsys"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "provider_transactions",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("transaction_id", sa.Integer(), nullable=False),
        sa.Column("provider_slug", sa.String(length=50), nullable=False),
        sa.Column("provider_txn_id", sa.String(length=255), nullable=True),
        sa.Column("host_reference_number", sa.String(length=255), nullable=True),
        sa.Column("response_code", sa.String(length=50), nullable=True),
        sa.Column("authorization_code", sa.String(length=100), nullable=True),
        sa.Column("card_type", sa.String(length=50), nullable=True),
        sa.Column("masked_card_number", sa.String(length=50), nullable=True),
        sa.Column("avs_response_code", sa.String(length=10), nullable=True),
        sa.Column("cvv_response_code", sa.String(length=10), nullable=True),
        sa.Column("amount", sa.Float(), nullable=True),
        sa.Column("currency", sa.String(length=10), nullable=True),
        sa.Column("status", sa.String(length=50), nullable=True),
        sa.Column("raw_response", sa.JSON(), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("now()"),
            nullable=False,
        ),
        sa.ForeignKeyConstraint(
            ["transaction_id"],
            ["transactions.id"],
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        "ix_provider_transactions_transaction_id",
        "provider_transactions",
        ["transaction_id"],
    )


def downgrade() -> None:
    op.drop_index(
        "ix_provider_transactions_transaction_id",
        table_name="provider_transactions",
    )
    op.drop_table("provider_transactions")