from datetime import datetime
from decimal import Decimal
from typing import Optional, List

from sqlalchemy import BigInteger, String, Text, JSON, DECIMAL, ForeignKey, Index, select, func
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 RefundRequest(Base, TenantMixin, TimestampMixin):
    """退款/退货/换货申请"""

    __tablename__ = "refund_requests"
    __table_args__ = (
        Index("ix_refund_order", "order_id"),
        Index("ix_refund_customer", "customer_id"),
        Index("ix_refund_status", "status"),
        Index("ix_refund_created", "created_at"),
        {
            "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="RESTRICT"), nullable=False
    )
    order_no: Mapped[str] = mapped_column(String(50), nullable=False)
    customer_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("customers.id", ondelete="RESTRICT"), nullable=False
    )

    # refund=仅退款 return=退货 exchange=换货
    type: Mapped[str] = mapped_column(String(20), nullable=False)
    reason: Mapped[str] = mapped_column(Text, nullable=False)

    # pending/approved/rejected/processing/completed
    status: Mapped[str] = mapped_column(String(20), nullable=False, default="pending")

    refund_amount: Mapped[Decimal] = mapped_column(DECIMAL(12, 2), nullable=False)

    # 退货商品 [{"variant_id":1,"qty":2}]
    return_items: Mapped[Optional[list]] = mapped_column(JSON, nullable=True)
    # 凭证图片 URL 列表
    images: Mapped[Optional[list]] = mapped_column(JSON, nullable=True)

    admin_note: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    admin_id: Mapped[Optional[int]] = mapped_column(
        BigInteger, ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    tracking_no: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)

    processed_at: Mapped[Optional[datetime]] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=True)
    completed_at: Mapped[Optional[datetime]] = mapped_column(MYSQL_DATETIME(fsp=3), nullable=True)

    # ── 关联 ─────────────────────────────────────────────────────
    tenant = relationship("Tenant", lazy="noload")
    order = relationship("Order", lazy="noload")
    customer = relationship("Customer", lazy="noload")
    admin = relationship("User", lazy="noload")

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