"""
待采购订单表模型
用于存储从标准化订单中筛选出的待采购订单数据
"""

from sqlalchemy import Column, Integer, String, DateTime, Numeric, Boolean, Text, Index
from sqlalchemy.sql import func
from enum import Enum

from app.core.database import Base


class ProcurementStatus(str, Enum):
    """采购状态枚举"""
    PENDING = "PENDING"      # 待采购
    ORDERED = "ORDERED"      # 已下单
    RECEIVED = "RECEIVED"    # 已收货
    CANCELLED = "CANCELLED"  # 已取消


class ProcurementMethod(str, Enum):
    """采购方式枚举"""
    GN = "GN"  # 国内现货
    MC = "MC"  # 库存
    AT = "AT"  # Atelier
    AP = "AP"  # 昌昌
    LA = "LA"  # 洛杉矶
    SS = "SS"  # Saks
    NY = "NY"  # 纽约默认
    SF = "SF"  # 旧金山
    FL = "FL"  # 佛罗里达
    CA = "CA"  # 加拿大


class SystemShippingStatus(str, Enum):
    """系统内部发货状态枚举"""
    PENDING = "PENDING"        # 待发货
    PROCESSING = "PROCESSING"  # 发货处理中
    SHIPPED = "SHIPPED"        # 已发货
    CANCELLED = "CANCELLED"    # 已取消


class ProcurementOrder(Base):
    """待采购订单表"""
    __tablename__ = "procurement_orders"
    
    # 主键
    id = Column(Integer, primary_key=True, index=True)
    
    # 原始订单信息
    original_order_id = Column(Integer, nullable=False, comment="原始标准化订单ID")
    原始订单编号 = Column(String(100), nullable=False, comment="原始订单编号")
    
    # 商品信息
    线上宝贝名称 = Column(String(500), nullable=False, comment="商品名称")
    线上销售属性 = Column(String(200), comment="销售属性")
    品牌 = Column(String(100), comment="品牌")
    货号 = Column(String(100), comment="货号")
    颜色 = Column(String(100), comment="颜色")
    尺寸 = Column(String(50), comment="尺寸")
    
    # 订单详情
    数量 = Column(Integer, nullable=False, comment="订单数量")
    订单单价 = Column(Numeric(10, 2), comment="订单单价")
    订单金额 = Column(Numeric(12, 2), comment="订单金额")
    
    # 时间信息
    付款时间 = Column(DateTime, comment="付款时间")
    创建时间 = Column(DateTime, server_default=func.now(), comment="创建时间")
    更新时间 = Column(DateTime, server_default=func.now(), onupdate=func.now(), comment="更新时间")
    
    # 订单状态
    交易状态 = Column(String(20), comment="交易状态")
    退款状态 = Column(String(20), comment="退款状态")
    网店名称 = Column(String(100), comment="网店名称")
    
    # 采购相关字段
    procurement_status = Column(String(20), default=ProcurementStatus.PENDING, comment="采购状态")
    procurement_method = Column(String(10), default=ProcurementMethod.NY, comment="采购方式")
    
    # SKU相关
    product_key = Column(String(64), comment="产品SKU哈希")
    
    # 采购备注和跟踪
    procurement_notes = Column(Text, comment="采购备注")
    assigned_to = Column(String(100), comment="分配给采购员")
    expected_delivery_date = Column(DateTime, comment="预期交付日期")
    
    # 优先级相关
    is_urgent = Column(Boolean, default=False, comment="是否紧急")
    priority_score = Column(Integer, default=0, comment="优先级分数")
    
    # 成本相关
    建议采购价 = Column(Numeric(10, 2), comment="建议采购价")
    实际采购价 = Column(Numeric(10, 2), comment="实际采购价")
    采购供应商 = Column(String(200), comment="采购供应商")
    商品小计 = Column(Numeric(12, 2), comment="单个商品的实际金额")
    
    # 图片相关
    image_urls = Column(Text, comment="图片URL列表(JSON)")
    main_image_path = Column(String(500), comment="主图本地路径")
    
    # 新增字段
    卖家备注 = Column(Text, comment="卖家备注")
    忽略采购 = Column(Boolean, default=False, comment="是否忽略采购")
    已推送 = Column(String(10), nullable=False, default="否", comment="是否已推送（是/否）")
    票据核对 = Column(String(10), nullable=False, default="否", comment="票据是否核对（是/否）")
    已发货状态 = Column(String(10), nullable=False, default="否", comment="是否已发货（是/否）")
    buyer_notes = Column(Text, comment="买手备注")
    is_out_of_stock = Column(Boolean, default=False, comment="是否缺货")

    # 淘宝报表相关字段（来自shop_sales_reports和shop_shipping_reports）
    子订单编号 = Column(String(100), comment="子订单编号(一单多件时不同)")
    淘宝订单状态 = Column(String(50), comment="淘宝真实订单状态(买家已付款,等待卖家发货等)")
    淘宝退款状态 = Column(String(50), comment="淘宝真实退款状态(买家已经申请退款等)")
    淘宝商家备注 = Column(Text, comment="淘宝报表中的商家备注")
    关联报表批次ID = Column(Integer, comment="关联shop_report_uploads.id")
    最后同步时间 = Column(DateTime, comment="最后一次从报表同步数据的时间")

    # 系统发货相关字段
    系统发货状态 = Column(String(20), default=SystemShippingStatus.PENDING, comment="系统内部发货状态")
    快递公司 = Column(String(50), comment="快递公司")
    快递单号 = Column(String(100), comment="快递单号")
    发货时间 = Column(DateTime, comment="系统发货时间")
    发货操作员 = Column(String(50), comment="发货操作员")

    # 票据核对相关字段
    invoice_code = Column(String(100), comment="票据编码")
    procurement_cost = Column(Numeric(10, 2), comment="采购金额/采购成本")


# 创建索引
Index('idx_procurement_orders_status', ProcurementOrder.procurement_status)
Index('idx_procurement_orders_method', ProcurementOrder.procurement_method)
Index('idx_procurement_orders_brand', ProcurementOrder.品牌)
Index('idx_procurement_orders_product_key', ProcurementOrder.product_key)
Index('idx_procurement_orders_payment_time', ProcurementOrder.付款时间)
Index('idx_procurement_orders_urgent', ProcurementOrder.is_urgent, ProcurementOrder.priority_score)
Index('idx_procurement_orders_original', ProcurementOrder.original_order_id)
Index('idx_procurement_system_shipping_status', ProcurementOrder.系统发货状态)
Index('idx_procurement_tracking_number', ProcurementOrder.快递单号)
Index('idx_procurement_sub_order_no', ProcurementOrder.子订单编号)
Index('idx_procurement_taobao_status', ProcurementOrder.淘宝订单状态)
Index('idx_procurement_report_batch', ProcurementOrder.关联报表批次ID)
Index('idx_procurement_invoice_code', ProcurementOrder.invoice_code)
Index('idx_procurement_cost', ProcurementOrder.procurement_cost)