from decimal import Decimal
from sqlalchemy import BigInteger, String, JSON, DECIMAL, Integer, Index, ForeignKey
from sqlalchemy.dialects.mysql import TINYINT
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.core.models.base import Base, TimestampMixin, TenantMixin


class ShippingMethod(Base, TenantMixin, TimestampMixin):
    """物流方案 = 快递公司 × 运费区域 × 计价方式

    pricing_method 取值：
      flat              - 固定运费（base_fee 即总运费）
      weight            - 按重量计费（首 first_weight kg 收 base_fee，之后每 unit_step kg 加 unit_fee）
      dimensional_weight- 按体积重计费（L×W×H cm³ / dimensional_divisor = 计费重量 kg，再按 weight 逻辑）
      per_item          - 按件数计费（首件 base_fee，之后每件加 unit_fee）
      order_value_tier  - 按订单金额阶梯（规则存在 shipping_rate_tiers 表）
    """

    __tablename__ = "shipping_methods"
    __table_args__ = (
        Index("ix_shipping_active", "is_active"),
        Index("ix_shipping_tenant_zone", "tenant_id", "zone_id"),
        Index("ix_shipping_carrier", "carrier_id"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_unicode_ci",
        },
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String(100), nullable=False, comment="方案名称，如 NZ Post Standard")
    description: Mapped[str | None] = mapped_column(String(500), nullable=True, comment="前台展示描述")

    # ── 关联快递公司和区域 ────────────────────────────────────────
    carrier_id: Mapped[int | None] = mapped_column(
        BigInteger, ForeignKey("shipping_carriers.id", ondelete="SET NULL"),
        nullable=True, comment="快递公司 ID，NULL=自定义/自提"
    )
    zone_id: Mapped[int | None] = mapped_column(
        BigInteger, ForeignKey("shipping_zones.id", ondelete="SET NULL"),
        nullable=True, comment="运费区域 ID，NULL=适用所有区域"
    )

    # ── 计价方式 ─────────────────────────────────────────────────
    pricing_method: Mapped[str] = mapped_column(
        String(30), nullable=False, default="flat",
        comment="flat/weight/dimensional_weight/per_item/order_value_tier"
    )

    # ── 通用费率字段 ──────────────────────────────────────────────
    base_fee: Mapped[Decimal] = mapped_column(
        DECIMAL(10, 2), nullable=False, default=Decimal("0"),
        comment="基础费用：固定费 / 首重费 / 首件费"
    )
    unit_fee: Mapped[Decimal] = mapped_column(
        DECIMAL(10, 2), nullable=False, default=Decimal("0"),
        comment="续加单价：每续重单位 / 每续件"
    )
    first_weight: Mapped[Decimal] = mapped_column(
        DECIMAL(10, 3), nullable=False, default=Decimal("1"),
        comment="首重重量：weight 模式下首重 kg"
    )
    unit_step: Mapped[Decimal] = mapped_column(
        DECIMAL(10, 3), nullable=False, default=Decimal("1"),
        comment="续重步长：weight 模式下单位 kg（如 0.5 = 每 0.5 kg 一档）"
    )

    # ── 门槛控制 ──────────────────────────────────────────────────
    free_threshold: Mapped[Decimal | None] = mapped_column(
        DECIMAL(12, 2), nullable=True, comment="满额免运费门槛（优先于阶梯规则）"
    )
    min_order_amount: Mapped[Decimal | None] = mapped_column(
        DECIMAL(12, 2), nullable=True, comment="最低起运金额，未达到则不显示此方案"
    )
    max_weight: Mapped[Decimal | None] = mapped_column(
        DECIMAL(10, 3), nullable=True, comment="最大可接受包裹重量（kg），超出不显示"
    )

    # ── 体积重参数 ────────────────────────────────────────────────
    dimensional_divisor: Mapped[int] = mapped_column(
        Integer, nullable=False, default=5000,
        comment="体积重除数（cm³/5000=kg，国际标准；空运常用 6000）"
    )

    estimated_days: Mapped[str | None] = mapped_column(String(50), nullable=True, comment="预计送达，如 3-5 working days")
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    is_active: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)
    extra_config: Mapped[dict | None] = mapped_column(JSON, nullable=True, comment="插件扩展配置（向后兼容）")

    # ── 旧字段保留（向后兼容平铺费率插件）────────────────────────
    carrier: Mapped[str | None] = mapped_column(String(50), nullable=True, comment="[已废弃] 用 carrier_id 替代")
    base_rate: Mapped[Decimal] = mapped_column(DECIMAL(8, 2), nullable=False, default=Decimal("0"),
                                               comment="[已废弃] 用 base_fee 替代")

    # ── 关联 ─────────────────────────────────────────────────────
    carrier_obj = relationship("ShippingCarrier", back_populates="methods", lazy="noload",
                               foreign_keys=[carrier_id])
    zone = relationship("ShippingZone", back_populates="methods", lazy="noload",
                        foreign_keys=[zone_id])
    tiers = relationship("ShippingRateTier", back_populates="method", lazy="noload",
                         cascade="all, delete-orphan")
    surcharges = relationship("ShippingSurcharge", back_populates="method", lazy="noload",
                              cascade="all, delete-orphan")

    def __repr__(self) -> str:
        return f"<ShippingMethod id={self.id} name={self.name!r} pricing={self.pricing_method!r}>"