"""seed_interchange_rates

Seeds standard Visa, Mastercard, Amex, Discover interchange rates for PRD-HWADM-002 (Story HWADM-210).
Rates reflect 2024 published interchange schedules. Admin can adjust via the interchange-rates CRUD endpoints.

Revision ID: pricing003_interchange_seed
Revises: pricing002_interchange_rates
Create Date: 2026-04-29
"""
from alembic import op
import sqlalchemy as sa
from datetime import datetime, timezone

revision = "pricing003_interchange_seed"
down_revision = "pricing002_interchange_rates"
branch_labels = None
depends_on = None

EFFECTIVE_FROM = datetime(2024, 1, 1, tzinfo=timezone.utc)

# Seed data: (card_network, card_type, transaction_type, rate_percentage, fixed_fee_cents, description)
INTERCHANGE_SEED = [
    # Visa — Consumer Credit
    ("visa", "consumer_credit", "card_present",     1.51, 10, "Visa Consumer Credit Card Present"),
    ("visa", "consumer_credit", "card_not_present", 1.80, 10, "Visa Consumer Credit Card Not Present"),
    ("visa", "consumer_credit", "ecommerce",        2.30, 10, "Visa Consumer Credit eCommerce"),
    # Visa — Business Credit
    ("visa", "business_credit", "card_present",     2.20, 10, "Visa Business Credit Card Present"),
    ("visa", "business_credit", "card_not_present", 2.65, 10, "Visa Business Credit Card Not Present"),
    ("visa", "business_credit", "ecommerce",        2.65, 10, "Visa Business Credit eCommerce"),
    # Visa — Debit
    ("visa", "debit",           "card_present",     0.80, 15, "Visa Debit Card Present"),
    ("visa", "debit",           "card_not_present", 1.65, 15, "Visa Debit Card Not Present"),
    ("visa", "debit",           "ecommerce",        1.65, 15, "Visa Debit eCommerce"),
    # Visa — Prepaid
    ("visa", "prepaid",         "card_present",     1.15, 15, "Visa Prepaid Card Present"),
    ("visa", "prepaid",         "card_not_present", 1.75, 15, "Visa Prepaid Card Not Present"),
    ("visa", "prepaid",         "ecommerce",        1.75, 15, "Visa Prepaid eCommerce"),

    # Mastercard — Consumer Credit
    ("mastercard", "consumer_credit", "card_present",     1.58, 10, "Mastercard Consumer Credit Card Present"),
    ("mastercard", "consumer_credit", "card_not_present", 1.89, 10, "Mastercard Consumer Credit Card Not Present"),
    ("mastercard", "consumer_credit", "ecommerce",        2.35, 10, "Mastercard Consumer Credit eCommerce"),
    # Mastercard — Business Credit
    ("mastercard", "business_credit", "card_present",     2.30, 10, "Mastercard Business Credit Card Present"),
    ("mastercard", "business_credit", "card_not_present", 2.70, 10, "Mastercard Business Credit Card Not Present"),
    ("mastercard", "business_credit", "ecommerce",        2.70, 10, "Mastercard Business Credit eCommerce"),
    # Mastercard — Debit
    ("mastercard", "debit",           "card_present",     0.80, 15, "Mastercard Debit Card Present"),
    ("mastercard", "debit",           "card_not_present", 1.60, 15, "Mastercard Debit Card Not Present"),
    ("mastercard", "debit",           "ecommerce",        1.60, 15, "Mastercard Debit eCommerce"),
    # Mastercard — Prepaid
    ("mastercard", "prepaid",         "card_present",     1.20, 15, "Mastercard Prepaid Card Present"),
    ("mastercard", "prepaid",         "card_not_present", 1.75, 15, "Mastercard Prepaid Card Not Present"),
    ("mastercard", "prepaid",         "ecommerce",        1.75, 15, "Mastercard Prepaid eCommerce"),

    # Amex — Consumer
    ("amex", "consumer_credit", "card_present",     2.30, 10, "Amex Consumer Credit Card Present"),
    ("amex", "consumer_credit", "card_not_present", 2.50, 10, "Amex Consumer Credit Card Not Present"),
    ("amex", "consumer_credit", "ecommerce",        2.95, 10, "Amex Consumer Credit eCommerce"),
    # Amex — Business
    ("amex", "business_credit", "card_present",     2.50, 10, "Amex Business Credit Card Present"),
    ("amex", "business_credit", "card_not_present", 2.70, 10, "Amex Business Credit Card Not Present"),
    ("amex", "business_credit", "ecommerce",        3.00, 10, "Amex Business Credit eCommerce"),

    # Discover — Consumer Credit
    ("discover", "consumer_credit", "card_present",     1.56, 10, "Discover Consumer Credit Card Present"),
    ("discover", "consumer_credit", "card_not_present", 1.87, 10, "Discover Consumer Credit Card Not Present"),
    ("discover", "consumer_credit", "ecommerce",        2.30, 10, "Discover Consumer Credit eCommerce"),
    # Discover — Business Credit
    ("discover", "business_credit", "card_present",     2.20, 10, "Discover Business Credit Card Present"),
    ("discover", "business_credit", "card_not_present", 2.65, 10, "Discover Business Credit Card Not Present"),
    ("discover", "business_credit", "ecommerce",        2.65, 10, "Discover Business Credit eCommerce"),
    # Discover — Debit
    ("discover", "debit",           "card_present",     0.80, 15, "Discover Debit Card Present"),
    ("discover", "debit",           "card_not_present", 1.65, 15, "Discover Debit Card Not Present"),
    ("discover", "debit",           "ecommerce",        1.65, 15, "Discover Debit eCommerce"),
]


def upgrade() -> None:
    interchange_rates = sa.table(
        "interchange_rates",
        sa.column("card_network", sa.String),
        sa.column("card_type", sa.String),
        sa.column("transaction_type", sa.String),
        sa.column("rate_percentage", sa.Float),
        sa.column("fixed_fee_cents", sa.Integer),
        sa.column("description", sa.Text),
        sa.column("effective_from", sa.DateTime(timezone=True)),
        sa.column("effective_to", sa.DateTime(timezone=True)),
        sa.column("is_active", sa.Boolean),
    )

    rows = [
        {
            "card_network": r[0],
            "card_type": r[1],
            "transaction_type": r[2],
            "rate_percentage": r[3],
            "fixed_fee_cents": r[4],
            "description": r[5],
            "effective_from": EFFECTIVE_FROM,
            "effective_to": None,
            "is_active": True,
        }
        for r in INTERCHANGE_SEED
    ]
    op.bulk_insert(interchange_rates, rows)


def downgrade() -> None:
    op.execute("DELETE FROM interchange_rates")