"""
标准化订单数据模型
"""

from datetime import datetime
from typing import Optional
from sqlalchemy import (
    Column, Integer, String, Text, DateTime, Float, 
    Index, UniqueConstraint, JSON, Enum as SQLEnum
)
from sqlalchemy.sql import func
import enum

from app.core.database import Base


class ProcurementStatus(str, enum.Enum):
    """采购状态枚举"""
    WAITING = "WAITING"          # 等待采购
    RESERVED = "RESERVED"        # 已预留
    PURCHASED = "PURCHASED"      # 已采购
    FULFILLED = "FULFILLED"      # 已完成
    COMPENSATED = "COMPENSATED"  # 已抵扣


class OrderItemNorm(Base):
    """标准化订单明细表 - 一行一商品"""
    
    __tablename__ = "order_items_norm"
    
    # 主键
    id = Column(Integer, primary_key=True, index=True)
    
    # 产品去重键（唯一）
    product_key = Column(String(64), unique=True, nullable=False, comment="产品去重键SHA1")
    
    # 基本展示信息
    原始订单编号 = Column(String(100), nullable=False, index=True, comment="原始订单编号")
    网店名称 = Column(String(100), comment="网店名称")
    交易状态 = Column(String(50), comment="交易状态")
    付款时间 = Column(DateTime, index=True, comment="付款时间")
    买家留言 = Column(Text, comment="买家留言")
    卖家备注 = Column(Text, comment="卖家备注")
    订单金额 = Column(Float, comment="订单金额")
    退款状态 = Column(String(50), comment="退款状态")
    
    # 商品信息
    线上宝贝名称 = Column(Text, nullable=False, comment="线上宝贝名称")
    线上销售属性 = Column(Text, comment="线上销售属性")
    图片链接 = Column(JSON, comment="图片链接数组")
    图片数量 = Column(Integer, default=0, comment="图片数量")
    订单单价 = Column(Float, nullable=False, comment="订单单价")
    数量 = Column(Integer, nullable=False, comment="数量")
    
    # 解析结果
    品牌 = Column(String(100), index=True, comment="标准化品牌名")
    货号 = Column(String(100), comment="提取的货号")
    颜色 = Column(String(50), comment="标准化颜色")
    尺寸 = Column(String(50), comment="标准化尺寸")
    
    # 编码信息
    线上商家编码 = Column(String(100), comment="线上商家编码")
    商品编号 = Column(String(100), comment="商品编号")
    SKU编号 = Column(String(100), comment="SKU编号")
    sku_id = Column(String(200), index=True, comment="生成的SKU标识")
    
    # 采购信息
    procurement_status = Column(
        SQLEnum(ProcurementStatus),
        default=ProcurementStatus.WAITING,
        nullable=False,
        comment="采购状态"
    )
    procurement_time = Column(DateTime, comment="采购时间")
    manual_note = Column(Text, comment="人工备注")
    
    # 系统字段
    created_at = Column(DateTime, server_default=func.now(), comment="创建时间")
    updated_at = Column(DateTime, onupdate=func.now(), comment="更新时间")
    
    # 原始数据关联
    raw_order_id = Column(Integer, comment="原始订单ID")
    
    # 索引
    __table_args__ = (
        Index('idx_order_brand', '原始订单编号', '品牌'),
        Index('idx_payment_status', '付款时间', 'procurement_status'),
        Index('idx_sku_brand', 'sku_id', '品牌'),
        {"comment": "标准化订单明细表"}
    )


class BrandAlias(Base):
    """品牌别名表"""
    
    __tablename__ = "brand_aliases"
    
    id = Column(Integer, primary_key=True, index=True)
    canonical_brand = Column(String(100), nullable=False, comment="标准品牌名")
    alias = Column(String(100), unique=True, nullable=False, comment="别名")
    confidence = Column(Integer, default=100, comment="置信度")
    is_confirmed = Column(
        SQLEnum(enum.Enum('ConfirmStatus', 'pending confirmed rejected')),
        default='pending',
        comment="确认状态"
    )
    
    # 系统字段
    created_at = Column(DateTime, server_default=func.now(), comment="创建时间")
    created_by = Column(String(100), default="system", comment="创建者")
    confirmed_at = Column(DateTime, comment="确认时间")
    confirmed_by = Column(String(100), comment="确认者")
    
    __table_args__ = (
        Index('idx_canonical_alias', 'canonical_brand', 'alias'),
        {"comment": "品牌别名映射表"}
    )


class ColorAlias(Base):
    """颜色别名表"""
    
    __tablename__ = "color_aliases"
    
    id = Column(Integer, primary_key=True, index=True)
    canonical_color = Column(String(50), nullable=False, comment="标准颜色名")
    alias = Column(String(100), unique=True, nullable=False, comment="颜色别名")
    confidence = Column(Integer, default=100, comment="置信度")
    
    created_at = Column(DateTime, server_default=func.now(), comment="创建时间")
    
    __table_args__ = (
        {"comment": "颜色别名映射表"}
    )


class SizeAlias(Base):
    """尺码别名表"""
    
    __tablename__ = "size_aliases"
    
    id = Column(Integer, primary_key=True, index=True)
    canonical_size = Column(String(50), nullable=False, comment="标准尺码")
    alias = Column(String(100), unique=True, nullable=False, comment="尺码别名")
    confidence = Column(Integer, default=100, comment="置信度")
    
    created_at = Column(DateTime, server_default=func.now(), comment="创建时间")
    
    __table_args__ = (
        {"comment": "尺码别名映射表"}
    )