"""Multi-Backend Payment Provider Support

Adds payment_providers, merchant_provider_configs, merchant_provider_credentials tables.
Adds active_provider_id to merchants.
Adds provider_id to transactions, payment_methods, merchant_transactions.
Adds occurred_at column to transactions (keeping ocurred_at for backward compat).

Revision ID: a1b2c3d4e5f8
Revises: f6ac9f5fd8f5
Create Date: 2026-03-02 00:00:00.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


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


def upgrade() -> None:
    # ── 1. payment_providers ────────────────────────────────────────────────
    op.create_table(
        'payment_providers',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('name', sa.String(length=100), nullable=False),
        sa.Column('slug', sa.String(length=50), nullable=False),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('is_active', sa.Boolean(), nullable=False, server_default=sa.text('true')),
        sa.Column('is_default', sa.Boolean(), nullable=False, server_default=sa.text('false')),
        sa.Column('supported_payment_methods', sa.JSON(), nullable=True),
        sa.Column('onboarding_schema', sa.JSON(), nullable=True),
        sa.Column('logo_url', sa.Text(), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('deleted_at', sa.DateTime(timezone=True), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('slug', name='uq_payment_providers_slug'),
    )
    op.create_index(op.f('ix_payment_providers_id'), 'payment_providers', ['id'], unique=False)
    op.create_index(op.f('ix_payment_providers_slug'), 'payment_providers', ['slug'], unique=False)

    # ── 2. merchant_provider_configs ────────────────────────────────────────
    op.create_table(
        'merchant_provider_configs',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('merchant_id', sa.Integer(), nullable=False),
        sa.Column('provider_id', sa.Integer(), nullable=False),
        sa.Column('is_active', sa.Boolean(), nullable=False, server_default=sa.text('true')),
        sa.Column('onboarding_status', sa.String(length=30), nullable=False, server_default='not_started'),
        sa.Column('config_data', sa.JSON(), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('deleted_at', sa.DateTime(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(['merchant_id'], ['merchants.id'], ),
        sa.ForeignKeyConstraint(['provider_id'], ['payment_providers.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('merchant_id', 'provider_id', name='uq_merchant_provider_configs_merchant_provider'),
    )
    op.create_index(op.f('ix_merchant_provider_configs_id'), 'merchant_provider_configs', ['id'], unique=False)
    op.create_index(op.f('ix_merchant_provider_configs_merchant_id'), 'merchant_provider_configs', ['merchant_id'], unique=False)
    op.create_index(op.f('ix_merchant_provider_configs_provider_id'), 'merchant_provider_configs', ['provider_id'], unique=False)

    # ── 3. merchant_provider_credentials ────────────────────────────────────
    op.create_table(
        'merchant_provider_credentials',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('merchant_provider_config_id', sa.Integer(), nullable=False),
        sa.Column('credential_key', sa.String(length=100), nullable=False),
        sa.Column('credential_value', sa.Text(), nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(['merchant_provider_config_id'], ['merchant_provider_configs.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint(
            'merchant_provider_config_id', 'credential_key',
            name='uq_merchant_provider_credentials_config_key'
        ),
    )
    op.create_index(
        op.f('ix_merchant_provider_credentials_id'), 'merchant_provider_credentials', ['id'], unique=False
    )
    op.create_index(
        op.f('ix_merchant_provider_credentials_merchant_provider_config_id'),
        'merchant_provider_credentials', ['merchant_provider_config_id'], unique=False
    )

    # ── 4. Add active_provider_id to merchants ───────────────────────────────
    op.add_column(
        'merchants',
        sa.Column('active_provider_id', sa.Integer(), nullable=True)
    )
    op.create_foreign_key(
        'fk_merchants_active_provider_id',
        'merchants', 'payment_providers',
        ['active_provider_id'], ['id']
    )

    # ── 5. Add provider_id to transactions ───────────────────────────────────
    op.add_column(
        'transactions',
        sa.Column('provider_id', sa.Integer(), nullable=True)
    )
    op.create_foreign_key(
        'fk_transactions_provider_id',
        'transactions', 'payment_providers',
        ['provider_id'], ['id']
    )

    # ── 6. Add occurred_at to transactions (corrected spelling) ─────────────
    op.add_column(
        'transactions',
        sa.Column('occurred_at', sa.DateTime(), nullable=True)
    )

    # ── 7. Add provider_id to payment_methods ────────────────────────────────
    op.add_column(
        'payment_methods',
        sa.Column('provider_id', sa.Integer(), nullable=True)
    )
    op.create_foreign_key(
        'fk_payment_methods_provider_id',
        'payment_methods', 'payment_providers',
        ['provider_id'], ['id']
    )

    # ── 8. Add provider_id to merchant_transactions ──────────────────────────
    op.add_column(
        'merchant_transactions',
        sa.Column('provider_id', sa.Integer(), nullable=True)
    )
    op.create_foreign_key(
        'fk_merchant_transactions_provider_id',
        'merchant_transactions', 'payment_providers',
        ['provider_id'], ['id']
    )

    # ── 9. Seed the Payrix provider record ───────────────────────────────────
    op.execute(
        """
        INSERT INTO payment_providers (name, slug, description, is_active, is_default,
            supported_payment_methods, onboarding_schema, created_at)
        VALUES (
            'Payrix (Worldpay)',
            'payrix',
            'Legacy Payrix / Worldpay payment processor integration',
            true,
            true,
            '{"card": true, "ach": true}',
            '{
                "fields": [
                    {"key": "api_key", "label": "API Key", "type": "text", "required": true},
                    {"key": "api_secret", "label": "API Secret", "type": "password", "required": true},
                    {"key": "provider_merchant_id", "label": "Payrix Merchant ID", "type": "text", "required": false}
                ]
            }',
            NOW()
        )
        """
    )


def downgrade() -> None:
    # Remove foreign keys and columns from existing tables
    op.drop_constraint('fk_merchant_transactions_provider_id', 'merchant_transactions', type_='foreignkey')
    op.drop_column('merchant_transactions', 'provider_id')

    op.drop_constraint('fk_payment_methods_provider_id', 'payment_methods', type_='foreignkey')
    op.drop_column('payment_methods', 'provider_id')

    op.drop_constraint('fk_transactions_provider_id', 'transactions', type_='foreignkey')
    op.drop_column('transactions', 'provider_id')
    op.drop_column('transactions', 'occurred_at')

    op.drop_constraint('fk_merchants_active_provider_id', 'merchants', type_='foreignkey')
    op.drop_column('merchants', 'active_provider_id')

    # Drop new tables in reverse dependency order
    op.drop_index(op.f('ix_merchant_provider_credentials_merchant_provider_config_id'), table_name='merchant_provider_credentials')
    op.drop_index(op.f('ix_merchant_provider_credentials_id'), table_name='merchant_provider_credentials')
    op.drop_table('merchant_provider_credentials')

    op.drop_index(op.f('ix_merchant_provider_configs_provider_id'), table_name='merchant_provider_configs')
    op.drop_index(op.f('ix_merchant_provider_configs_merchant_id'), table_name='merchant_provider_configs')
    op.drop_index(op.f('ix_merchant_provider_configs_id'), table_name='merchant_provider_configs')
    op.drop_table('merchant_provider_configs')

    op.drop_index(op.f('ix_payment_providers_slug'), table_name='payment_providers')
    op.drop_index(op.f('ix_payment_providers_id'), table_name='payment_providers')
    op.drop_table('payment_providers')