import enum
from datetime import datetime
from typing import Optional, TYPE_CHECKING
from sqlalchemy import Boolean, DateTime, Float, ForeignKey, Index, Integer, String, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.sql import func, text
from src.apps.base.models.base import Base

if TYPE_CHECKING:
    from src.apps.pricing_control.models.pricing_template import PricingTemplate


class TransactionType(str, enum.Enum):
    CARD_PRESENT = "card_present"
    CARD_NOT_PRESENT = "card_not_present"
    ECOMMERCE = "ecommerce"
    ACH_DEBIT = "ach_debit"
    ACH_CREDIT = "ach_credit"
    CHARGEBACK = "chargeback"


class PricingTemplateRate(Base):
    __tablename__ = "pricing_template_rates"
    __table_args__ = (
        # Two partial unique indexes replace the old two-column UNIQUE constraint
        # (uq_pricing_rate_template_type, dropped in prd012_add_card_network_to_pricing_rate).
        #
        # Index 1 — network-specific rows (card_network IS NOT NULL):
        #   Prevents duplicate (template_id, transaction_type, card_network) triples.
        Index(
            "uq_pricing_rate_template_type_network_specific",
            "template_id",
            "transaction_type",
            "card_network",
            unique=True,
            postgresql_where=text("card_network IS NOT NULL AND deleted_at IS NULL"),
        ),
        # Index 2 — generic rows (card_network IS NULL):
        #   Prevents more than one generic rate per (template_id, transaction_type).
        #   NULL != NULL in PostgreSQL, so a table-level UNIQUE would allow duplicates;
        #   this partial index avoids that pitfall.
        Index(
            "uq_pricing_rate_template_type_generic",
            "template_id",
            "transaction_type",
            unique=True,
            postgresql_where=text("card_network IS NULL AND deleted_at IS NULL"),
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    template_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("pricing_templates.id"), nullable=False, index=True
    )
    transaction_type: Mapped[str] = mapped_column(String(30), nullable=False)
    # NULL means "applies to all card networks" (generic rate).
    # A non-NULL value (e.g. "visa", "mastercard", "amex") means network-specific override.
    card_network: Mapped[Optional[str]] = mapped_column(String(20), nullable=True)
    rate_percentage: Mapped[Optional[float]] = mapped_column(Float, nullable=True, default=0.0)
    fixed_fee_cents: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    tier_name: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    interchange_basis_points: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    markup_basis_points: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    updated_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True, onupdate=func.now()
    )
    deleted_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)

    template: Mapped["PricingTemplate"] = relationship(
        "PricingTemplate",
        back_populates="rates",
        foreign_keys=[template_id],
        primaryjoin="and_(PricingTemplate.id == PricingTemplateRate.template_id)",
    )