"""
发货上传批次管理表模型
This is part of the new independent shipping module that does not affect existing modules.
"""

from datetime import datetime
from typing import Optional, Dict, Any
from sqlalchemy import (
    Column, Integer, String, Text, DateTime, JSON, Index
)
from sqlalchemy.sql import func
from app.core.database import Base
import uuid


class ShippingUploadBatch(Base):
    """发货上传批次管理表"""
    __tablename__ = "shipping_upload_batches"

    # 主键
    id = Column(Integer, primary_key=True, autoincrement=True)
    batch_id = Column(String(36), nullable=False, unique=True)  # UUID
    upload_type = Column(String(20))  # shipping_list(发货清单) / address_list(地址表)

    # 店铺和文件信息
    网店名称 = Column(String(100))  # 小晴天7/uslife/晴七公主/信泽供货商
    file_name = Column(String(255))
    file_hash = Column(String(64))  # SHA-256

    # ========== 统计信息 ==========
    总行数 = Column(Integer, default=0)
    匹配成功数 = Column(Integer, default=0)
    未匹配数 = Column(Integer, default=0)
    无需发货数 = Column(Integer, default=0)  # 自动标记为无需发货的订单数
    重复订单数 = Column(Integer, default=0)  # 更新的重复订单数
    新增订单数 = Column(Integer, default=0)  # 新增的订单数

    # ========== 处理状态 ==========
    upload_status = Column(String(20), default='processing')  # processing/completed/failed
    error_message = Column(Text)
    processing_details = Column(JSON)  # 存储详细的处理信息

    # ========== 操作信息 ==========
    上传时间 = Column(DateTime, server_default=func.now())
    完成时间 = Column(DateTime)
    操作员 = Column(String(50))

    # ========== 审计字段 ==========
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, onupdate=func.now())

    # ========== 表索引 ==========
    __table_args__ = (
        Index('idx_batch_shop', '网店名称'),
        Index('idx_batch_status', 'upload_status'),
        Index('idx_batch_type', 'upload_type'),
        Index('idx_batch_created', 'created_at'),
    )

    def __init__(self, **kwargs):
        """初始化时自动生成batch_id"""
        super().__init__(**kwargs)
        if not self.batch_id:
            self.batch_id = str(uuid.uuid4())

    def __repr__(self):
        return f"<ShippingUploadBatch(batch_id={self.batch_id}, type={self.upload_type}, status={self.upload_status})>"

    def to_dict(self):
        """Convert to dictionary for API responses"""
        return {
            'id': self.id,
            'batch_id': self.batch_id,
            'upload_type': self.upload_type,
            '网店名称': self.网店名称,
            'file_name': self.file_name,
            '总行数': self.总行数,
            '匹配成功数': self.匹配成功数,
            '未匹配数': self.未匹配数,
            '无需发货数': self.无需发货数,
            '重复订单数': self.重复订单数,
            '新增订单数': self.新增订单数,
            'upload_status': self.upload_status,
            'error_message': self.error_message,
            'processing_details': self.processing_details,
            '上传时间': self.上传时间.isoformat() if self.上传时间 else None,
            '完成时间': self.完成时间.isoformat() if self.完成时间 else None,
            '操作员': self.操作员,
        }

    def set_statistics(self, stats: Dict[str, int]):
        """Update statistics from processing results"""
        self.总行数 = stats.get('total', 0)
        self.匹配成功数 = stats.get('matched', 0)
        self.未匹配数 = stats.get('unmatched', 0)
        self.无需发货数 = stats.get('no_shipping_needed', 0)
        self.重复订单数 = stats.get('duplicates', 0)
        self.新增订单数 = stats.get('new_orders', 0)

    def mark_completed(self):
        """Mark batch as completed"""
        self.upload_status = 'completed'
        self.完成时间 = datetime.now()

    def mark_failed(self, error_message: str):
        """Mark batch as failed with error message"""
        self.upload_status = 'failed'
        self.error_message = error_message
        self.完成时间 = datetime.now()

    @property
    def is_completed(self):
        """Check if batch processing is completed"""
        return self.upload_status == 'completed'

    @property
    def is_failed(self):
        """Check if batch processing failed"""
        return self.upload_status == 'failed'

    @property
    def success_rate(self):
        """Calculate success rate of matching"""
        if self.总行数 == 0:
            return 0.0
        return (self.匹配成功数 / self.总行数) * 100

    def get_summary(self):
        """Get processing summary"""
        return {
            'batch_id': self.batch_id,
            'type': self.upload_type,
            'shop': self.网店名称,
            'status': self.upload_status,
            'success_rate': f"{self.success_rate:.1f}%",
            'statistics': {
                '总行数': self.总行数,
                '匹配成功': self.匹配成功数,
                '未匹配': self.未匹配数,
                '无需发货': self.无需发货数,
                '重复更新': self.重复订单数,
                '新增订单': self.新增订单数,
            }
        }