"""prd012: add card_network column to pricing_template_rates.

Extends the pricing rate model with per-card-network overrides (e.g. Visa,
Mastercard, Amex).  Because PostgreSQL UNIQUE constraints treat two NULLs as
distinct, we cannot use a simple three-column UNIQUE constraint for the
(template_id, transaction_type, card_network) key — a single NULL network
value would allow unlimited duplicate "generic" rows.

Instead we:
  1. Drop the existing two-column unique constraint uq_pricing_rate_template_type.
  2. Add a partial unique index for network-specific rows
     (card_network IS NOT NULL AND deleted_at IS NULL).
  3. Add a partial unique index for generic (card_network IS NULL) rows
     (deleted_at IS NULL).

This design enforces exactly one generic rate and one rate per distinct card
network per (template_id, transaction_type) pair, while correctly handling
soft-deletes.

Revision ID: prd012_add_card_network_to_pricing_rate
Revises: prd011_merge_scheduler_tables
Create Date: 2026-05-14
"""
from typing import Union, Sequence

import sqlalchemy as sa
from alembic import op

revision: str = "prd012_add_card_network_to_pricing_rate"
down_revision: Union[str, Sequence[str], None] = "prd011_merge_scheduler_tables"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ------------------------------------------------------------------
    # 1. Add card_network column (nullable; NULL means "generic / all networks")
    # ------------------------------------------------------------------
    op.add_column(
        "pricing_template_rates",
        sa.Column("card_network", sa.String(20), nullable=True),
    )

    # ------------------------------------------------------------------
    # 2. Drop the old two-column unique constraint that does not account
    #    for card_network or soft-deletes.
    # ------------------------------------------------------------------
    op.drop_constraint(
        "uq_pricing_rate_template_type",
        "pricing_template_rates",
        type_="unique",
    )

    # ------------------------------------------------------------------
    # 3. Create partial unique index for network-specific rows.
    #    Guarantees: no duplicate (template_id, transaction_type, card_network)
    #    tuples where card_network IS NOT NULL and the row is not soft-deleted.
    # ------------------------------------------------------------------
    op.create_index(
        "uq_pricing_rate_template_type_network_specific",
        "pricing_template_rates",
        ["template_id", "transaction_type", "card_network"],
        unique=True,
        postgresql_where=sa.text("card_network IS NOT NULL AND deleted_at IS NULL"),
    )

    # ------------------------------------------------------------------
    # 4. Create partial unique index for generic (NULL network) rows.
    #    Guarantees: exactly one generic rate per (template_id, transaction_type)
    #    for non-deleted rows.
    # ------------------------------------------------------------------
    op.create_index(
        "uq_pricing_rate_template_type_generic",
        "pricing_template_rates",
        ["template_id", "transaction_type"],
        unique=True,
        postgresql_where=sa.text("card_network IS NULL AND deleted_at IS NULL"),
    )


def downgrade() -> None:
    # Remove partial indexes
    op.drop_index(
        "uq_pricing_rate_template_type_generic",
        table_name="pricing_template_rates",
    )
    op.drop_index(
        "uq_pricing_rate_template_type_network_specific",
        table_name="pricing_template_rates",
    )

    # Restore the original two-column unique constraint.
    # NOTE: this will fail if duplicate (template_id, transaction_type) rows
    # were inserted after the upgrade (e.g. network-specific rows).  Clean up
    # those rows before downgrading if needed.
    op.create_unique_constraint(
        "uq_pricing_rate_template_type",
        "pricing_template_rates",
        ["template_id", "transaction_type"],
    )

    # Remove column
    op.drop_column("pricing_template_rates", "card_network")