from datetime import date, datetime
from decimal import Decimal
from sqlalchemy import BigInteger, String, Text, JSON, DECIMAL, UniqueConstraint, Index, ForeignKey, Date
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 Order(Base, TenantMixin, TimestampMixin):
    """订单主表（订单计算引擎输出目标）"""

    __tablename__ = "orders"
    __table_args__ = (
        UniqueConstraint("tenant_id", "order_no", name="uk_orders_tenant_order_no"),
        Index("ix_orders_status", "status"),
        Index("ix_orders_tenant_status_date", "tenant_id", "status", "created_at"),
        Index("ix_orders_paid_at", "paid_at"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_unicode_ci",
        },
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    customer_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("customers.id", ondelete="RESTRICT"),
        nullable=False, index=True
    )
    order_no: Mapped[str] = mapped_column(String(50), nullable=False, comment="人类可读单号，如 ORD-20260503-001")
    status: Mapped[str] = mapped_column(
        String(30), nullable=False, default="pending",
        comment="pending/paid/shipped/completed/cancelled"
    )

    # 金额（全部 DECIMAL，禁止 FLOAT）
    subtotal: Mapped[Decimal] = mapped_column(DECIMAL(12, 2), nullable=False, comment="商品小计（税前折扣前）")
    discount_total: Mapped[Decimal] = mapped_column(DECIMAL(12, 2), nullable=False, default=Decimal("0"), comment="折扣合计（信号写入）")
    shipping_total: Mapped[Decimal] = mapped_column(DECIMAL(12, 2), nullable=False, default=Decimal("0"), comment="运费（信号写入）")
    tax_total: Mapped[Decimal] = mapped_column(DECIMAL(12, 2), nullable=False, default=Decimal("0"), comment="税额（NZ GST 15%）")
    grand_total: Mapped[Decimal] = mapped_column(DECIMAL(12, 2), nullable=False, comment="最终金额")
    currency: Mapped[str] = mapped_column(String(3), nullable=False, default="NZD", comment="ISO 4217 货币码（基准货币）")

    # 顾客展示货币快照（下单时记录顾客选择的货币，NULL 表示与基准货币相同）
    display_currency: Mapped[str | None] = mapped_column(String(3), nullable=True, comment="顾客选择的展示货币代码")
    display_currency_symbol: Mapped[str | None] = mapped_column(String(10), nullable=True, comment="展示货币符号快照")
    display_exchange_rate: Mapped[Decimal | None] = mapped_column(DECIMAL(18, 8), nullable=True, comment="下单时汇率快照（1基准=N展示）")
    display_grand_total: Mapped[Decimal | None] = mapped_column(DECIMAL(12, 2), nullable=True, comment="展示货币的订单总额")

    # 地址快照（JSON，防止客户改地址后历史订单失真）
    shipping_address: Mapped[dict] = mapped_column(JSON, nullable=False, comment="收货地址快照")
    billing_address: Mapped[dict | None] = mapped_column(JSON, nullable=True, comment="账单地址快照")

    note: Mapped[str | None] = mapped_column(Text, nullable=True, comment="买家备注")
    extra_attributes: Mapped[dict | None] = mapped_column(JSON, nullable=True, comment="ERP 编号、发票信息等行业字段")

    # 物流字段
    carrier: Mapped[str | None] = mapped_column(String(50), nullable=True, comment="快递公司名称")
    tracking_no: Mapped[str | None] = mapped_column(String(100), nullable=True, comment="运单号")
    estimated_delivery: Mapped[date | None] = mapped_column(Date, nullable=True, comment="预计送达日期")

    paid_at: Mapped[MYSQL_DATETIME | None] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=True, index=True)

    # 运费方案快照
    shipping_method_id: Mapped[int | None] = mapped_column(
        BigInteger, ForeignKey("shipping_methods.id", ondelete="SET NULL"),
        nullable=True, comment="用户选择的运费方案ID"
    )

    # ── 关联 ─────────────────────────────────────────────────────
    tenant = relationship("Tenant", back_populates="orders", lazy="noload")
    customer = relationship("Customer", back_populates="orders", lazy="noload")
    items = relationship("OrderItem", back_populates="order", lazy="noload", cascade="all, delete-orphan")
    payments = relationship("Payment", back_populates="order", lazy="noload")

    def get_attribute(self, key: str, default=None):
        if not self.extra_attributes:
            return default
        return self.extra_attributes.get(key, default)

    def set_attribute(self, key: str, value) -> None:
        if self.extra_attributes is None:
            self.extra_attributes = {}
        self.extra_attributes = {**self.extra_attributes, key: value}

    def __repr__(self) -> str:
        return f"<Order id={self.id} order_no={self.order_no!r} status={self.status!r}>"


class OrderItem(Base, TimestampMixin):
    """订单明细（含商品快照）"""

    __tablename__ = "order_items"
    __table_args__ = (
        Index("ix_oitem_order", "order_id"),
        Index("ix_oitem_product", "product_id"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_unicode_ci",
        },
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    order_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("orders.id", ondelete="CASCADE"), nullable=False)
    tenant_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("tenants.id", ondelete="RESTRICT"), nullable=False, index=True)
    product_id: Mapped[int | None] = mapped_column(BigInteger, ForeignKey("products.id", ondelete="SET NULL"), nullable=True, index=True)
    variant_id: Mapped[int | None] = mapped_column(BigInteger, ForeignKey("product_variants.id", ondelete="SET NULL"), nullable=True, index=True)

    # 商品快照：下单瞬间保存，历史订单不受商品变更影响
    product_snapshot: Mapped[dict] = mapped_column(JSON, nullable=False, comment="商品完整快照（名称/规格/图片/价格）")

    quantity: Mapped[Decimal] = mapped_column(DECIMAL(12, 2), nullable=False, comment="购买数量")
    unit_price: Mapped[Decimal] = mapped_column(DECIMAL(12, 2), nullable=False, comment="下单时单价（含插件折后）")
    total_price: Mapped[Decimal] = mapped_column(DECIMAL(12, 2), nullable=False, comment="行小计")
    tax_rate: Mapped[Decimal] = mapped_column(
        DECIMAL(6, 4), nullable=False, default=Decimal("0"), comment="税率快照"
    )
    tax_amount: Mapped[Decimal] = mapped_column(
        DECIMAL(12, 2), nullable=False, default=Decimal("0"), comment="行税额快照"
    )

    order = relationship("Order", back_populates="items", lazy="noload")

    def __repr__(self) -> str:
        return f"<OrderItem id={self.id} order_id={self.order_id} qty={self.quantity}>"