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


class ShipmentBatch(Base, TenantMixin, TimestampMixin):
    """发货批次 — 一次发货动作对应一个批次"""

    __tablename__ = "shipment_batches"
    __table_args__ = (
        Index("ix_sb_tenant_status", "tenant_id", "status"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_unicode_ci",
        },
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    batch_no: Mapped[str] = mapped_column(String(50), nullable=False, comment="批次号 如 20260520-001")
    carrier_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("shipping_carriers.id", ondelete="RESTRICT"), nullable=False
    )
    status: Mapped[str] = mapped_column(
        String(20), nullable=False, default="draft",
        comment="draft / reviewed / confirmed"
    )
    confirmed_at: Mapped[datetime | None] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=True)
    created_by: Mapped[int | None] = mapped_column(
        BigInteger, ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )

    parcels = relationship(
        "ShipmentParcel", back_populates="batch",
        lazy="noload", cascade="all, delete-orphan"
    )


class ShipmentParcel(Base, TenantMixin, TimestampMixin):
    """包裹/箱 — 一箱一行"""

    __tablename__ = "shipment_parcels"
    __table_args__ = (
        Index("ix_sp_batch", "batch_id"),
        Index("ix_sp_order", "order_id"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_unicode_ci",
        },
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    batch_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("shipment_batches.id", ondelete="CASCADE"), nullable=False
    )
    order_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("orders.id", ondelete="RESTRICT"), nullable=False
    )
    tracking_no: Mapped[str] = mapped_column(String(50), nullable=False, comment="快递单号 FTD0123A3F8C101")
    box_index: Mapped[int] = mapped_column(Integer, nullable=False, default=1, comment="该订单第几箱")
    weight: Mapped[Decimal] = mapped_column(DECIMAL(8, 3), nullable=False, default=Decimal("0"))

    batch = relationship("ShipmentBatch", back_populates="parcels", lazy="noload")
    items = relationship(
        "ShipmentParcelItem", back_populates="parcel",
        lazy="noload", cascade="all, delete-orphan"
    )


class ShipmentParcelItem(Base, TimestampMixin):
    """箱内商品 — 审核时可在同订单的箱间拖拽调整"""

    __tablename__ = "shipment_parcel_items"
    __table_args__ = (
        Index("ix_spi_parcel", "parcel_id"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_unicode_ci",
        },
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    parcel_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("shipment_parcels.id", ondelete="CASCADE"), nullable=False
    )
    order_item_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("order_items.id", ondelete="RESTRICT"), nullable=False
    )
    product_name: Mapped[str] = mapped_column(String(200), nullable=False, comment="商品名称快照")
    sku_name: Mapped[str | None] = mapped_column(String(200), nullable=True, comment="规格名称快照")
    qty: Mapped[int] = mapped_column(
        Integer, nullable=False,
        comment="该箱内该商品数量（可小于原 order_item.qty，支持同商品跨箱拆分）"
    )

    parcel = relationship("ShipmentParcel", back_populates="items", lazy="noload")