"""
发货服务 - 处理系统内部发货逻辑
实现订单合并、发货、修改、取消等核心功能
"""

from datetime import datetime
from typing import List, Dict, Optional, Tuple
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, and_, func, or_
from sqlalchemy.orm import selectinload

from app.models.procurement_orders import ProcurementOrder, SystemShippingStatus
from app.models.shipping_packages import ShippingPackage, PackageStatus
from app.models.raw_orders import RawOrder


class ShippingService:
    """发货服务类"""

    async def find_pending_shipping_orders(
        self,
        db: AsyncSession,
        limit: int = 100,
        offset: int = 0,
        procurement_method: Optional[str] = None,
        品牌: Optional[str] = None
    ) -> Dict:
        """
        查找待发货订单

        Args:
            db: 数据库会话
            limit: 返回数量限制
            offset: 偏移量
            procurement_method: 采购方式筛选
            品牌: 品牌筛选

        Returns:
            包含订单列表和总数的字典
        """
        query = select(ProcurementOrder).where(
            and_(
                or_(
                    ProcurementOrder.系统发货状态 == SystemShippingStatus.PENDING,
                    ProcurementOrder.系统发货状态.is_(None)  # 兼容旧数据,NULL视为待发货
                ),
                or_(
                    ProcurementOrder.procurement_status == "RECEIVED",  # 已收货
                    ProcurementOrder.procurement_status == "ORDERED"    # 已下单也可以发货
                )
            )
        )

        if procurement_method:
            query = query.where(ProcurementOrder.procurement_method == procurement_method)

        if 品牌:
            query = query.where(ProcurementOrder.品牌 == 品牌)

        # 获取总数
        count_query = select(func.count()).select_from(query.subquery())
        total = await db.scalar(count_query)

        # 获取分页数据
        query = query.limit(limit).offset(offset).order_by(ProcurementOrder.付款时间.desc())
        result = await db.execute(query)
        orders = result.scalars().all()

        return {
            "orders": orders,
            "total": total or 0,
            "limit": limit,
            "offset": offset
        }

    async def find_mergeable_orders_exact(
        self,
        db: AsyncSession,
        order_ids: List[int]
    ) -> Dict:
        """
        精确匹配可合并订单
        基于: 手机号 + 姓名 + 地址 + 买家账号 精确匹配

        Args:
            db: 数据库会话
            order_ids: 待检查的采购订单ID列表

        Returns:
            可合并订单分组字典
        """
        if not order_ids:
            return {"mergeable_groups": [], "unmergeable_orders": []}

        # 获取采购订单及其对应的原始订单信息
        query = select(ProcurementOrder).where(
            ProcurementOrder.id.in_(order_ids)
        )
        result = await db.execute(query)
        procurement_orders = result.scalars().all()

        # 获取原始订单编号
        original_order_numbers = [po.原始订单编号 for po in procurement_orders]

        # 从raw_orders获取收货信息
        raw_query = select(RawOrder).where(
            RawOrder.原始订单编号.in_(original_order_numbers)
        )
        raw_result = await db.execute(raw_query)
        raw_orders = {ro.原始订单编号: ro for ro in raw_result.scalars().all()}

        # 构建匹配键 -> 订单列表的映射
        match_groups = {}

        for po in procurement_orders:
            raw = raw_orders.get(po.原始订单编号)
            if not raw:
                continue

            # 构建精确匹配键: 手机号|姓名|地址|买家账号
            match_key = f"{raw.收货人手机号 or ''}|{raw.收货人姓名 or ''}|{raw.收货地址 or ''}|{raw.买家旺旺号 or ''}"

            if match_key not in match_groups:
                match_groups[match_key] = {
                    "match_info": {
                        "phone": raw.收货人手机号,
                        "name": raw.收货人姓名,
                        "address": raw.收货地址,
                        "buyer_account": raw.买家旺旺号
                    },
                    "procurement_orders": []
                }

            match_groups[match_key]["procurement_orders"].append({
                "id": po.id,
                "order_number": po.原始订单编号,
                "product_name": po.线上宝贝名称,
                "quantity": po.数量,
                "procurement_method": po.procurement_method
            })

        # 分离可合并和不可合并的订单
        mergeable_groups = [
            group for group in match_groups.values()
            if len(group["procurement_orders"]) > 1
        ]

        unmergeable_orders = [
            group["procurement_orders"][0]
            for group in match_groups.values()
            if len(group["procurement_orders"]) == 1
        ]

        return {
            "mergeable_groups": mergeable_groups,
            "unmergeable_orders": unmergeable_orders,
            "total_groups": len(mergeable_groups),
            "total_unmergeable": len(unmergeable_orders)
        }

    async def create_shipping_package(
        self,
        db: AsyncSession,
        procurement_order_ids: List[int],
        快递公司: str,
        快递单号: str,
        操作员: str,
        shipping_notes: Optional[str] = None
    ) -> ShippingPackage:
        """
        创建快递包裹并更新采购订单发货状态

        Args:
            db: 数据库会话
            procurement_order_ids: 采购订单ID列表
            快递公司: 快递公司名称
            快递单号: 快递单号
            操作员: 操作员名称
            shipping_notes: 发货备注

        Returns:
            创建的快递包裹对象
        """
        # 获取采购订单
        query = select(ProcurementOrder).where(
            ProcurementOrder.id.in_(procurement_order_ids)
        )
        result = await db.execute(query)
        orders = result.scalars().all()

        if not orders:
            raise ValueError("未找到指定的采购订单")

        # 检查订单是否已发货
        already_shipped = [o for o in orders if o.系统发货状态 != SystemShippingStatus.PENDING]
        if already_shipped:
            raise ValueError(f"订单 {[o.id for o in already_shipped]} 已发货，无法重复发货")

        # 获取第一个订单的收货信息作为包裹信息
        first_order_number = orders[0].原始订单编号
        raw_query = select(RawOrder).where(RawOrder.原始订单编号 == first_order_number)
        raw_result = await db.execute(raw_query)
        first_raw = raw_result.scalar_one_or_none()

        if not first_raw:
            raise ValueError(f"未找到原始订单 {first_order_number}")

        # 创建快递包裹
        package = ShippingPackage(
            快递公司=快递公司,
            快递单号=快递单号,
            收货人姓名=first_raw.收货人姓名,
            收货人手机号=first_raw.收货人手机号,
            收货地址=first_raw.收货地址,
            买家账号=first_raw.买家旺旺号,
            total_skus=len(orders),
            merged_order_numbers=[o.原始订单编号 for o in orders],
            merged_procurement_ids=[o.id for o in orders],
            package_status=PackageStatus.SHIPPED,
            created_by=操作员,
            shipped_at=datetime.now(),
            shipping_notes=shipping_notes
        )

        db.add(package)

        # 更新采购订单发货状态
        for order in orders:
            order.系统发货状态 = SystemShippingStatus.SHIPPED
            order.快递公司 = 快递公司
            order.快递单号 = 快递单号
            order.发货时间 = datetime.now()
            order.发货操作员 = 操作员

        await db.commit()
        await db.refresh(package)

        return package

    async def update_tracking_number(
        self,
        db: AsyncSession,
        package_id: int,
        new_快递公司: str,
        new_快递单号: str,
        操作员: str
    ) -> ShippingPackage:
        """
        修改快递单号

        Args:
            db: 数据库会话
            package_id: 包裹ID
            new_快递公司: 新的快递公司
            new_快递单号: 新的快递单号
            操作员: 操作员名称

        Returns:
            更新后的包裹对象
        """
        # 获取包裹
        query = select(ShippingPackage).where(ShippingPackage.id == package_id)
        result = await db.execute(query)
        package = result.scalar_one_or_none()

        if not package:
            raise ValueError(f"未找到包裹 {package_id}")

        if package.package_status == PackageStatus.CANCELLED:
            raise ValueError("已取消的包裹无法修改")

        old_快递单号 = package.快递单号

        # 更新包裹信息
        package.快递公司 = new_快递公司
        package.快递单号 = new_快递单号
        package.updated_at = datetime.now()

        # 同步更新所有关联的采购订单
        if package.merged_procurement_ids:
            query = select(ProcurementOrder).where(
                ProcurementOrder.id.in_(package.merged_procurement_ids)
            )
            result = await db.execute(query)
            orders = result.scalars().all()

            for order in orders:
                order.快递公司 = new_快递公司
                order.快递单号 = new_快递单号

        await db.commit()
        await db.refresh(package)

        return package

    async def cancel_shipping(
        self,
        db: AsyncSession,
        package_id: int,
        操作员: str,
        cancel_reason: str
    ) -> ShippingPackage:
        """
        取消发货

        Args:
            db: 数据库会话
            package_id: 包裹ID
            操作员: 操作员名称
            cancel_reason: 取消原因

        Returns:
            取消后的包裹对象
        """
        # 获取包裹
        query = select(ShippingPackage).where(ShippingPackage.id == package_id)
        result = await db.execute(query)
        package = result.scalar_one_or_none()

        if not package:
            raise ValueError(f"未找到包裹 {package_id}")

        if package.package_status == PackageStatus.CANCELLED:
            raise ValueError("包裹已经被取消")

        # 更新包裹状态
        package.package_status = PackageStatus.CANCELLED
        package.cancelled_at = datetime.now()
        package.cancelled_by = 操作员
        package.cancel_reason = cancel_reason
        package.updated_at = datetime.now()

        # 将关联的采购订单状态改回待发货
        if package.merged_procurement_ids:
            query = select(ProcurementOrder).where(
                ProcurementOrder.id.in_(package.merged_procurement_ids)
            )
            result = await db.execute(query)
            orders = result.scalars().all()

            for order in orders:
                order.系统发货状态 = SystemShippingStatus.PENDING
                order.快递公司 = None
                order.快递单号 = None
                order.发货时间 = None
                order.发货操作员 = None

        await db.commit()
        await db.refresh(package)

        return package

    async def get_shipping_history(
        self,
        db: AsyncSession,
        limit: int = 100,
        offset: int = 0,
        快递单号: Optional[str] = None,
        包裹状态: Optional[str] = None
    ) -> Dict:
        """
        查询发货历史

        Args:
            db: 数据库会话
            limit: 返回数量限制
            offset: 偏移量
            快递单号: 快递单号筛选
            包裹状态: 包裹状态筛选

        Returns:
            包含包裹列表和总数的字典
        """
        query = select(ShippingPackage)

        conditions = []
        if 快递单号:
            conditions.append(ShippingPackage.快递单号.like(f"%{快递单号}%"))
        if 包裹状态:
            conditions.append(ShippingPackage.package_status == 包裹状态)

        if conditions:
            query = query.where(and_(*conditions))

        # 获取总数
        count_query = select(func.count()).select_from(query.subquery())
        total = await db.scalar(count_query)

        # 获取分页数据
        query = query.limit(limit).offset(offset).order_by(ShippingPackage.created_at.desc())
        result = await db.execute(query)
        packages = result.scalars().all()

        return {
            "packages": packages,
            "total": total or 0,
            "limit": limit,
            "offset": offset
        }

    async def get_package_details(
        self,
        db: AsyncSession,
        package_id: int
    ) -> Dict:
        """
        获取包裹详细信息，包括关联的采购订单

        Args:
            db: 数据库会话
            package_id: 包裹ID

        Returns:
            包裹详细信息字典
        """
        # 获取包裹
        query = select(ShippingPackage).where(ShippingPackage.id == package_id)
        result = await db.execute(query)
        package = result.scalar_one_or_none()

        if not package:
            raise ValueError(f"未找到包裹 {package_id}")

        # 获取关联的采购订单
        orders = []
        if package.merged_procurement_ids:
            query = select(ProcurementOrder).where(
                ProcurementOrder.id.in_(package.merged_procurement_ids)
            )
            result = await db.execute(query)
            orders = result.scalars().all()

        return {
            "package": package,
            "procurement_orders": orders,
            "order_count": len(orders)
        }

    async def get_available_brands(
        self,
        db: AsyncSession
    ) -> Dict:
        """
        获取所有待发货订单中的可用品牌列表

        Args:
            db: 数据库会话

        Returns:
            包含品牌列表和总数的字典
        """
        # 查询所有待发货订单中的不同品牌
        query = select(ProcurementOrder.品牌).distinct().where(
            and_(
                or_(
                    ProcurementOrder.系统发货状态 == SystemShippingStatus.PENDING,
                    ProcurementOrder.系统发货状态.is_(None)
                ),
                or_(
                    ProcurementOrder.procurement_status == "RECEIVED",
                    ProcurementOrder.procurement_status == "ORDERED"
                ),
                ProcurementOrder.品牌.isnot(None),  # 排除NULL品牌
                ProcurementOrder.品牌 != ""  # 排除空字符串
            )
        ).order_by(ProcurementOrder.品牌)

        result = await db.execute(query)
        brands = [brand for brand in result.scalars().all() if brand]

        return {
            "brands": brands,
            "total": len(brands)
        }