"""
Phase 3 产品派生表模型
"""

from datetime import datetime
from decimal import Decimal
from typing import List, Optional
from enum import Enum as PyEnum

from sqlalchemy import (
    Column, Integer, String, Numeric, DateTime, Text, Boolean, 
    Index, ForeignKey, func, Enum as SQLEnum
)
from sqlalchemy.orm import relationship, Mapped
from sqlalchemy.ext.hybrid import hybrid_property

from app.core.database import Base


class ProductCategory(PyEnum):
    """产品分类枚举"""
    MC = "MC"  # 美妆类
    AT = "AT"  # 服装类  
    AP = "AP"  # 配饰类
    LA = "LA"  # 箱包类
    SS = "SS"  # 鞋子类
    NY = "NY"  # 内衣类
    OTHER = "OTHER"  # 其他类


class ProcurementPriority(PyEnum):
    """采购优先级"""
    HIGH = "HIGH"      # 高优先级
    MEDIUM = "MEDIUM"  # 中优先级
    LOW = "LOW"        # 低优先级


class Product(Base):
    """
    产品主表 - 从 order_items_norm 聚合生成的产品信息
    每个 product_key 对应一个产品记录
    """
    __tablename__ = "products"
    
    # 主键和唯一标识
    id = Column(Integer, primary_key=True, index=True)
    product_key = Column(String(64), unique=True, nullable=False, comment="产品去重键SHA1")
    sku_id = Column(String(128), nullable=True, comment="标准化SKU ID")
    
    # 基本产品信息
    品牌 = Column(String(100), nullable=True, comment="标准化品牌名")
    货号 = Column(String(100), nullable=True, comment="产品货号")
    线上宝贝名称 = Column(Text, nullable=True, comment="商品名称")
    颜色 = Column(String(50), nullable=True, comment="标准化颜色")
    尺寸 = Column(String(50), nullable=True, comment="标准化尺寸")
    
    # 分类信息
    category = Column(SQLEnum(ProductCategory), default=ProductCategory.OTHER, comment="自动分类")
    category_confidence = Column(Numeric(5, 4), default=0.0, comment="分类置信度")
    
    # 商业信息
    平均价格 = Column(Numeric(10, 2), nullable=True, comment="平均订单单价")
    最低价格 = Column(Numeric(10, 2), nullable=True, comment="最低订单单价")
    最高价格 = Column(Numeric(10, 2), nullable=True, comment="最高订单单价")
    
    # 销售统计
    总销量 = Column(Integer, default=0, comment="总销售数量")
    订单数量 = Column(Integer, default=0, comment="相关订单数量")
    最近订单时间 = Column(DateTime, nullable=True, comment="最近订单时间")
    首次订单时间 = Column(DateTime, nullable=True, comment="首次订单时间")
    
    # 库存相关
    待采购数量 = Column(Integer, default=0, comment="待采购数量")
    已采购数量 = Column(Integer, default=0, comment="已采购数量")
    可用库存 = Column(Integer, default=0, comment="当前可用库存")
    
    # 采购优先级
    procurement_priority = Column(SQLEnum(ProcurementPriority), default=ProcurementPriority.MEDIUM, comment="采购优先级")
    
    # 图片信息
    图片数量 = Column(Integer, default=0, comment="商品图片数量")
    主要图片 = Column(Text, nullable=True, comment="主要商品图片JSON")
    
    # 元数据
    is_active = Column(Boolean, default=True, comment="产品是否激活")
    created_at = Column(DateTime, default=func.now(), comment="创建时间")
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now(), comment="更新时间")
    last_aggregated_at = Column(DateTime, nullable=True, comment="最后聚合时间")
    
    # 关联关系
    pending_purchases: Mapped[List["PendingPurchase"]] = relationship("PendingPurchase", back_populates="product")
    purchase_items: Mapped[List["PurchaseListItem"]] = relationship("PurchaseListItem", back_populates="product")
    
    # 索引
    __table_args__ = (
        Index('idx_product_brand_category', '品牌', 'category'),
        Index('idx_product_procurement', 'procurement_priority', '待采购数量'),
        Index('idx_product_sales', '总销量', '最近订单时间'),
        Index('idx_product_active', 'is_active', 'created_at'),
    )
    
    @hybrid_property
    def needs_procurement(self) -> bool:
        """判断是否需要采购"""
        return self.待采购数量 > 0 and self.可用库存 < self.待采购数量
    
    @hybrid_property
    def procurement_shortage(self) -> int:
        """计算采购缺口"""
        return max(0, self.待采购数量 - self.可用库存)
    
    def __repr__(self):
        return f"<Product(id={self.id}, product_key={self.product_key}, 品牌={self.品牌}, sku_id={self.sku_id})>"


class PendingPurchase(Base):
    """
    待采购表 - 记录需要采购的产品及数量
    从订单中分析出尚未满足的采购需求
    """
    __tablename__ = "pending_purchases"
    
    # 主键
    id = Column(Integer, primary_key=True, index=True)
    
    # 关联产品
    product_id = Column(Integer, ForeignKey("products.id"), nullable=False, comment="关联产品ID")
    product_key = Column(String(64), nullable=False, comment="产品去重键")
    
    # 采购信息
    需求数量 = Column(Integer, nullable=False, comment="需求采购数量")
    已分配数量 = Column(Integer, default=0, comment="已分配到采购清单的数量")
    剩余需求 = Column(Integer, nullable=False, comment="剩余未分配数量")
    
    # 优先级和状态
    priority = Column(SQLEnum(ProcurementPriority), default=ProcurementPriority.MEDIUM, comment="采购优先级")
    is_urgent = Column(Boolean, default=False, comment="是否紧急")
    
    # 价格信息
    建议采购价 = Column(Numeric(10, 2), nullable=True, comment="建议采购价格")
    预算上限 = Column(Numeric(10, 2), nullable=True, comment="采购预算上限")
    
    # 时间信息
    需求生成时间 = Column(DateTime, default=func.now(), comment="需求生成时间")
    期望交付时间 = Column(DateTime, nullable=True, comment="期望交付时间")
    last_updated = Column(DateTime, default=func.now(), onupdate=func.now(), comment="最后更新时间")
    
    # 备注
    采购备注 = Column(Text, nullable=True, comment="采购备注信息")
    
    # 采购方式和来源
    procurement_method = Column(String(10), default="NY", comment="采购方式(NY/LA/AP/SS/MC)")
    order_count = Column(Integer, default=0, comment="相关订单数量")
    order_source = Column(String(20), default="网上下载", comment="订单来源")
    
    # 关联关系
    product: Mapped["Product"] = relationship("Product", back_populates="pending_purchases")
    purchase_items: Mapped[List["PurchaseListItem"]] = relationship("PurchaseListItem", back_populates="pending_purchase")
    
    # 索引
    __table_args__ = (
        Index('idx_pending_priority_urgent', 'priority', 'is_urgent'),
        Index('idx_pending_product', 'product_id', 'product_key'),
        Index('idx_pending_remaining', '剩余需求', '需求生成时间'),
    )
    
    @hybrid_property
    def is_fully_allocated(self) -> bool:
        """是否已完全分配"""
        return self.剩余需求 <= 0
    
    def __repr__(self):
        return f"<PendingPurchase(id={self.id}, product_key={self.product_key}, 需求数量={self.需求数量}, 剩余需求={self.剩余需求})>"


class PurchaseList(Base):
    """
    采购清单表 - 管理采购批次和供应商信息
    """
    __tablename__ = "purchase_lists"
    
    # 主键
    id = Column(Integer, primary_key=True, index=True)
    
    # 采购清单基本信息
    采购单号 = Column(String(100), unique=True, nullable=False, comment="采购单号")
    采购标题 = Column(String(200), nullable=True, comment="采购清单标题")
    
    # 供应商信息
    供应商名称 = Column(String(100), nullable=True, comment="供应商名称")
    供应商联系方式 = Column(String(200), nullable=True, comment="供应商联系方式")
    
    # 状态信息
    状态 = Column(String(20), default="草稿", comment="采购状态: 草稿/已提交/已确认/部分到货/已完成/已取消")
    
    # 金额统计
    商品总数量 = Column(Integer, default=0, comment="商品总数量")
    预估总金额 = Column(Numeric(12, 2), default=0.0, comment="预估总金额")
    实际总金额 = Column(Numeric(12, 2), nullable=True, comment="实际采购总金额")
    
    # 时间信息
    创建时间 = Column(DateTime, default=func.now(), comment="创建时间")
    提交时间 = Column(DateTime, nullable=True, comment="提交时间")
    确认时间 = Column(DateTime, nullable=True, comment="供应商确认时间")
    预计到货时间 = Column(DateTime, nullable=True, comment="预计到货时间")
    实际到货时间 = Column(DateTime, nullable=True, comment="实际到货时间")
    
    # 操作信息
    创建人 = Column(String(100), nullable=True, comment="创建人")
    审批人 = Column(String(100), nullable=True, comment="审批人")
    
    # 备注
    采购备注 = Column(Text, nullable=True, comment="采购备注")
    
    # 关联关系
    items: Mapped[List["PurchaseListItem"]] = relationship("PurchaseListItem", back_populates="purchase_list", cascade="all, delete-orphan")
    confirmations: Mapped[List["ProcurementConfirmation"]] = relationship("ProcurementConfirmation", back_populates="purchase_list")
    
    # 索引
    __table_args__ = (
        Index('idx_purchase_status_time', '状态', '创建时间'),
        Index('idx_purchase_supplier', '供应商名称', '状态'),
    )
    
    @hybrid_property
    def is_editable(self) -> bool:
        """是否可编辑"""
        return self.状态 in ["草稿"]
    
    @hybrid_property
    def completion_rate(self) -> float:
        """完成率"""
        if not self.items:
            return 0.0
        completed_items = sum(1 for item in self.items if item.is_received)
        return completed_items / len(self.items) if self.items else 0.0
    
    def __repr__(self):
        return f"<PurchaseList(id={self.id}, 采购单号={self.采购单号}, 状态={self.状态})>"


class PurchaseListItem(Base):
    """
    采购清单明细表 - 采购清单中的具体商品项
    """
    __tablename__ = "purchase_list_items"
    
    # 主键
    id = Column(Integer, primary_key=True, index=True)
    
    # 关联信息
    purchase_list_id = Column(Integer, ForeignKey("purchase_lists.id"), nullable=False, comment="采购清单ID")
    product_id = Column(Integer, ForeignKey("products.id"), nullable=False, comment="产品ID")
    pending_purchase_id = Column(Integer, ForeignKey("pending_purchases.id"), nullable=True, comment="待采购记录ID")
    
    # 商品信息（冗余存储便于查看）
    product_key = Column(String(64), nullable=False, comment="产品去重键")
    品牌 = Column(String(100), nullable=True, comment="品牌")
    货号 = Column(String(100), nullable=True, comment="货号") 
    线上宝贝名称 = Column(Text, nullable=True, comment="商品名称")
    颜色 = Column(String(50), nullable=True, comment="颜色")
    尺寸 = Column(String(50), nullable=True, comment="尺寸")
    
    # 采购数量和价格
    采购数量 = Column(Integer, nullable=False, comment="计划采购数量")
    单价 = Column(Numeric(10, 2), nullable=True, comment="采购单价")
    小计 = Column(Numeric(10, 2), nullable=True, comment="小计金额")
    
    # 到货信息
    已到货数量 = Column(Integer, default=0, comment="已到货数量")
    待收货数量 = Column(Integer, nullable=False, comment="待收货数量")
    
    # 状态
    is_received = Column(Boolean, default=False, comment="是否已收货")
    收货时间 = Column(DateTime, nullable=True, comment="收货时间")
    
    # 备注
    商品备注 = Column(Text, nullable=True, comment="商品备注")
    
    # 时间信息  
    created_at = Column(DateTime, default=func.now(), comment="创建时间")
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now(), comment="更新时间")
    
    # 关联关系
    purchase_list: Mapped["PurchaseList"] = relationship("PurchaseList", back_populates="items")
    product: Mapped["Product"] = relationship("Product", back_populates="purchase_items")
    pending_purchase: Mapped[Optional["PendingPurchase"]] = relationship("PendingPurchase", back_populates="purchase_items")
    
    # 索引
    __table_args__ = (
        Index('idx_purchase_item_list_product', 'purchase_list_id', 'product_id'),
        Index('idx_purchase_item_received', 'is_received', '收货时间'),
        Index('idx_purchase_item_pending', 'pending_purchase_id'),
    )
    
    @hybrid_property
    def remaining_quantity(self) -> int:
        """剩余待收数量"""
        return self.采购数量 - self.已到货数量
    
    def __repr__(self):
        return f"<PurchaseListItem(id={self.id}, product_key={self.product_key}, 采购数量={self.采购数量}, 已到货数量={self.已到货数量})>"