"""
订单匹配服务
关联pending_shipments、order_addresses和procurement_orders数据
This is part of the new independent shipping module that does not affect existing modules.
"""

import logging
from typing import Optional, Dict, List, Any, Tuple
from datetime import datetime
from sqlalchemy import select, and_, update, or_, func, cast, String
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.pending_shipments import PendingShipment
from app.models.shipment_records import ShipmentRecord
from app.models.order_addresses import OrderAddress
from app.models.procurement_orders import ProcurementOrder

logger = logging.getLogger(__name__)


class OrderMatchingService:
    """订单匹配服务 - 关联各个表的数据"""

    async def match_addresses_to_shipments(
        self,
        db: AsyncSession,
        shop_name: Optional[str] = None
    ) -> Dict[str, Any]:
        """
        将order_addresses的地址编码匹配到pending_shipments

        Args:
            db: 数据库会话
            shop_name: 店铺名称（可选，用于限定范围）

        Returns:
            匹配结果统计
        """
        try:
            # 获取待匹配的pending_shipments
            query = select(PendingShipment).where(
                and_(
                    PendingShipment.address_encoding.is_(None),
                    PendingShipment.is_deleted == False
                )
            )
            if shop_name:
                query = query.where(PendingShipment.网店名称 == shop_name)

            result = await db.execute(query)
            shipments = result.scalars().all()

            stats = {
                'total': len(shipments),
                'matched': 0,
                'unmatched': 0
            }

            for shipment in shipments:
                # 根据主订单号查找地址信息
                address_result = await db.execute(
                    select(OrderAddress).where(
                        and_(
                            OrderAddress.订单编号 == shipment.主订单编号,
                            OrderAddress.网店名称 == shipment.网店名称
                        )
                    )
                )
                address = address_result.scalar_one_or_none()

                if address:
                    # 更新pending_shipment的地址编码
                    shipment.address_encoding = address.address_encoding
                    stats['matched'] += 1
                else:
                    stats['unmatched'] += 1

            await db.commit()

            logger.info(f"地址匹配完成：总数{stats['total']}，成功{stats['matched']}，失败{stats['unmatched']}")
            return {
                'success': True,
                'statistics': stats
            }

        except Exception as e:
            logger.error(f"地址匹配失败：{str(e)}")
            await db.rollback()
            return {
                'success': False,
                'error': str(e)
            }

    async def sync_procurement_data(
        self,
        db: AsyncSession,
        shop_name: Optional[str] = None
    ) -> Dict[str, Any]:
        """
        从procurement_orders同步数据到pending_shipments（只读）

        同步的数据包括：
        - 图片路径
        - 颜色
        - 尺码
        - 货号
        - 品牌等

        Args:
            db: 数据库会话
            shop_name: 店铺名称（可选）

        Returns:
            同步结果统计
        """
        try:
            # 获取已匹配procurement_order_id但需要同步数据的记录
            query = select(PendingShipment).where(
                and_(
                    PendingShipment.procurement_order_id.isnot(None),
                    PendingShipment.is_deleted == False
                )
            )
            if shop_name:
                query = query.where(PendingShipment.网店名称 == shop_name)

            result = await db.execute(query)
            shipments = result.scalars().all()

            stats = {
                'total': len(shipments),
                'synced': 0,
                'errors': []
            }

            for shipment in shipments:
                try:
                    # 获取对应的procurement_order（只读）
                    po_result = await db.execute(
                        select(ProcurementOrder).where(
                            ProcurementOrder.id == shipment.procurement_order_id
                        )
                    )
                    po = po_result.scalar_one_or_none()

                    if po:
                        # 采购信息通过 get_pending_orders_with_details 方法的 procurement_info 对象返回
                        # 不需要将采购信息写入商家备注字段，保持Excel原始备注数据
                        stats['synced'] += 1

                except Exception as e:
                    logger.error(f"同步procurement_order {shipment.procurement_order_id} 失败：{str(e)}")
                    stats['errors'].append(f"PO ID {shipment.procurement_order_id}: {str(e)}")

            await db.commit()

            logger.info(f"数据同步完成：总数{stats['total']}，成功{stats['synced']}")
            return {
                'success': True,
                'statistics': stats
            }

        except Exception as e:
            logger.error(f"数据同步失败：{str(e)}")
            await db.rollback()
            return {
                'success': False,
                'error': str(e)
            }

    async def find_same_address_orders(
        self,
        db: AsyncSession,
        order_id: int
    ) -> List[Dict[str, Any]]:
        """
        查找具有相同地址编码的待发货订单（包含采购信息）

        Args:
            db: 数据库会话
            order_id: pending_shipments表的ID

        Returns:
            相同地址的订单列表（包含procurement_info）
        """
        try:
            # 获取指定订单
            result = await db.execute(
                select(PendingShipment).where(PendingShipment.id == order_id)
            )
            order = result.scalar_one_or_none()

            if not order or not order.address_encoding:
                return []

            # 查找相同地址编码的所有订单（包括当前订单）
            # 使用与主列表一致的显示状态逻辑
            same_address_result = await db.execute(
                select(PendingShipment).where(
                    and_(
                        PendingShipment.address_encoding == order.address_encoding,
                        # 移除 id != order_id 条件，包含当前订单
                        PendingShipment.系统发货状态 == 'PENDING',
                        PendingShipment.订单状态 == '买家已付款,等待卖家发货',
                        # 显示状态条件：与主列表保持一致
                        or_(
                            PendingShipment.manual_display_status == 'active',
                            and_(
                                PendingShipment.manual_display_status.is_(None),
                                PendingShipment.is_deleted == False
                            )
                        )
                    )
                )
            )
            shipments = same_address_result.scalars().all()

            # 组装详细数据（包含采购信息）
            orders_with_details = []
            for shipment in shipments:
                order_dict = shipment.to_dict()

                # 如果有procurement_order_id，获取采购信息
                if shipment.procurement_order_id:
                    po_result = await db.execute(
                        select(ProcurementOrder).where(
                            ProcurementOrder.id == shipment.procurement_order_id
                        )
                    )
                    po = po_result.scalar_one_or_none()
                    if po:
                        order_dict['procurement_info'] = {
                            '品牌': po.品牌,
                            '颜色': po.颜色,
                            '尺码': po.尺寸,
                            '货号': po.货号,
                            '图片': po.main_image_path,
                            '采购方式': po.procurement_method
                        }

                orders_with_details.append(order_dict)

            return orders_with_details

        except Exception as e:
            logger.error(f"查找相同地址订单失败：{str(e)}")
            return []

    async def get_pending_orders_with_details(
        self,
        db: AsyncSession,
        shop_name: Optional[str] = None,
        limit: int = 50,
        offset: int = 0,
        filters: Optional[Dict[str, Any]] = None,
        shipping_statuses: Optional[List[str]] = None,
        sort_by: Optional[str] = '订单付款时间',
        sort_order: Optional[str] = 'desc'
    ) -> Tuple[List[Dict], int]:
        """
        获取待发货订单的详细信息（结合各表数据）

        Args:
            db: 数据库会话
            shop_name: 店铺名称
            limit: 每页数量
            offset: 偏移量
            filters: 筛选条件
            shipping_statuses: 发货状态列表 (['PENDING', 'PARTIAL', 'SHIPPED'])
                - PENDING: 待发货（尚未发货任何数量）
                - PARTIAL: 部分发货（已发货部分，还有剩余）
                - SHIPPED: 已发货（全部发货完成）

        Returns:
            (订单列表, 总数)
        """
        try:
            # 构建基础查询条件
            conditions = []

            # 显示状态筛选（综合判断 manual_display_status 和 is_deleted）
            # 业务逻辑：用户手动设置优先，否则跟随系统判断
            display_filter = filters.get('show_deleted', 'active') if filters else 'active'
            if display_filter == 'active':
                # "仅活跃"：显示应该发货的订单
                # = 用户手动设置为active OR (自动模式且系统未删除)
                conditions.append(or_(
                    PendingShipment.manual_display_status == 'active',
                    and_(
                        PendingShipment.manual_display_status.is_(None),
                        PendingShipment.is_deleted == False
                    )
                ))
            elif display_filter == 'deleted':
                # "仅隐藏"：显示无需发货的订单
                # = 用户手动设置为hidden OR (自动模式且系统已删除)
                conditions.append(or_(
                    PendingShipment.manual_display_status == 'hidden',
                    and_(
                        PendingShipment.manual_display_status.is_(None),
                        PendingShipment.is_deleted == True
                    )
                ))
            # 'all' 时不添加筛选条件，显示所有状态

            # 添加发货状态筛选（支持多状态）
            if shipping_statuses and len(shipping_statuses) > 0:
                status_conditions = []
                for status in shipping_statuses:
                    if status == 'PENDING':
                        # 待发货：系统状态=PENDING 且 详情状态=PENDING（未发货任何数量）
                        status_conditions.append(and_(
                            PendingShipment.系统发货状态 == 'PENDING',
                            or_(
                                PendingShipment.发货状态详情 == 'PENDING',
                                PendingShipment.发货状态详情.is_(None)
                            )
                        ))
                    elif status == 'PARTIAL':
                        # 部分发货：详情状态=PARTIAL
                        status_conditions.append(PendingShipment.发货状态详情 == 'PARTIAL')
                    elif status == 'SHIPPED':
                        # 已发货：系统状态=SHIPPED
                        status_conditions.append(PendingShipment.系统发货状态 == 'SHIPPED')
                if status_conditions:
                    conditions.append(or_(*status_conditions))

            # 判断是否需要 JOIN procurement_orders 表
            need_join = False
            if filters:
                if filters.get('brand') or filters.get('procurement_methods'):
                    need_join = True

            # 构建查询
            if need_join:
                query = select(PendingShipment).join(
                    ProcurementOrder,
                    PendingShipment.procurement_order_id == ProcurementOrder.id,
                    isouter=True  # LEFT JOIN 以保留未匹配的订单
                )
            else:
                query = select(PendingShipment)

            # 添加基础条件
            if conditions:
                query = query.where(and_(*conditions))

            # 添加店铺筛选
            if shop_name:
                query = query.where(PendingShipment.网店名称 == shop_name)

            # 添加其他筛选条件
            if filters:
                # 退款状态筛选
                if filters.get('has_refund'):
                    query = query.where(PendingShipment.退款状态 != '没有申请退款')

                # 订单状态筛选
                if filters.get('order_status'):
                    query = query.where(PendingShipment.订单状态 == filters['order_status'])

                # 品牌筛选（需要JOIN procurement_orders）
                if filters.get('brand'):
                    query = query.where(ProcurementOrder.品牌 == filters['brand'])

                # 采购方式多选筛选
                if filters.get('procurement_methods'):
                    methods = filters['procurement_methods']
                    if isinstance(methods, list) and len(methods) > 0:
                        # 允许匹配指定采购方式的订单，或者未关联采购订单的订单（避免漏掉）
                        query = query.where(or_(
                            ProcurementOrder.procurement_method.in_(methods),
                            PendingShipment.procurement_order_id.is_(None)
                        ))

                # 商品名称/属性混合搜索
                if filters.get('product_search'):
                    search_term = f"%{filters['product_search']}%"
                    query = query.where(
                        or_(
                            PendingShipment.商品标题.like(search_term),
                            PendingShipment.商品属性.like(search_term),
                            PendingShipment.备注标签.like(search_term)
                        )
                    )

                # 订单号搜索（主订单号或子订单号）
                if filters.get('order_no'):
                    order_no = filters['order_no'].strip()
                    query = query.where(
                        or_(
                            PendingShipment.主订单编号 == order_no,
                            PendingShipment.子订单编号 == order_no
                        )
                    )

                # 备注筛选
                if filters.get('has_remark') and filters['has_remark'] != 'all':
                    if filters['has_remark'] == 'has':
                        # 有备注：商家备注或买家留言不为空
                        query = query.where(
                            or_(
                                and_(
                                    PendingShipment.商家备注.isnot(None),
                                    PendingShipment.商家备注 != ''
                                ),
                                and_(
                                    PendingShipment.主订单买家留言.isnot(None),
                                    PendingShipment.主订单买家留言 != ''
                                )
                            )
                        )
                    elif filters['has_remark'] == 'none':
                        # 无备注：商家备注和买家留言都为空
                        query = query.where(
                            and_(
                                or_(
                                    PendingShipment.商家备注.is_(None),
                                    PendingShipment.商家备注 == ''
                                ),
                                or_(
                                    PendingShipment.主订单买家留言.is_(None),
                                    PendingShipment.主订单买家留言 == ''
                                )
                            )
                        )

                # 卖家备注（商家备注）有/无筛选
                if filters.get('has_seller_remark') and filters['has_seller_remark'] != 'all':
                    if filters['has_seller_remark'] == 'has':
                        # 有卖家备注
                        query = query.where(
                            and_(
                                PendingShipment.商家备注.isnot(None),
                                PendingShipment.商家备注 != ''
                            )
                        )
                    elif filters['has_seller_remark'] == 'none':
                        # 无卖家备注
                        query = query.where(
                            or_(
                                PendingShipment.商家备注.is_(None),
                                PendingShipment.商家备注 == ''
                            )
                        )

                # 卖家备注内容搜索
                if filters.get('seller_remark_search'):
                    search_term = f"%{filters['seller_remark_search']}%"
                    query = query.where(PendingShipment.商家备注.like(search_term))

                # 仓库备注有/无筛选
                if filters.get('has_warehouse_notes') and filters['has_warehouse_notes'] != 'all':
                    if filters['has_warehouse_notes'] == 'has':
                        # 有仓库备注
                        query = query.where(
                            and_(
                                PendingShipment.warehouse_notes.isnot(None),
                                PendingShipment.warehouse_notes != ''
                            )
                        )
                    elif filters['has_warehouse_notes'] == 'none':
                        # 无仓库备注
                        query = query.where(
                            or_(
                                PendingShipment.warehouse_notes.is_(None),
                                PendingShipment.warehouse_notes == ''
                            )
                        )

                # 仓库备注内容搜索
                if filters.get('warehouse_notes_search'):
                    search_term = f"%{filters['warehouse_notes_search']}%"
                    query = query.where(PendingShipment.warehouse_notes.like(search_term))

                # 发货时间筛选
                if filters.get('shipping_time_from'):
                    try:
                        from_date = datetime.fromisoformat(filters['shipping_time_from'])
                        query = query.where(PendingShipment.系统发货时间 >= from_date)
                        logger.info(f"应用发货时间开始筛选: {from_date}")
                    except ValueError:
                        logger.warning(f"无效的发货开始时间: {filters['shipping_time_from']}")

                if filters.get('shipping_time_to'):
                    try:
                        to_date = datetime.fromisoformat(filters['shipping_time_to'])
                        # 包含结束日期的全天：设置为23:59:59
                        to_date = to_date.replace(hour=23, minute=59, second=59)
                        query = query.where(PendingShipment.系统发货时间 <= to_date)
                        logger.info(f"应用发货时间结束筛选: {to_date}")
                    except ValueError:
                        logger.warning(f"无效的发货结束时间: {filters['shipping_time_to']}")

                # 多件订单筛选
                if filters.get('multi_quantity'):
                    # 处理 NULL 值情况：购买数量为 NULL 时视为1
                    query = query.where(
                        and_(
                            PendingShipment.购买数量.isnot(None),
                            PendingShipment.购买数量 > 1
                        )
                    )
                    logger.info("应用多件订单筛选: 购买数量 > 1")

                # 快递单号搜索
                if filters.get('tracking_no'):
                    search_term = f"%{filters['tracking_no']}%"
                    query = query.where(PendingShipment.系统快递单号.like(search_term))
                    logger.info(f"应用快递单号搜索: {filters['tracking_no']}")

                # 商品扫码搜索（JSON字段模糊匹配）
                if filters.get('scanned_code'):
                    search_term = f"%{filters['scanned_code']}%"
                    # 将JSON字段转为字符串进行模糊搜索
                    query = query.where(
                        cast(PendingShipment.商品扫码记录, String).like(search_term)
                    )
                    logger.info(f"应用商品扫码搜索: {filters['scanned_code']}")

                # 上传批次筛选
                if filters.get('upload_batch_id'):
                    batch_id = filters['upload_batch_id']
                    if filters.get('only_new_in_batch'):
                        # 仅新增：该批次首次创建的订单
                        query = query.where(PendingShipment.first_upload_batch_id == batch_id)
                        logger.info(f"应用批次筛选(仅新增): first_upload_batch_id={batch_id}")
                    else:
                        # 全部涉及：该批次新增或更新的订单
                        query = query.where(or_(
                            PendingShipment.first_upload_batch_id == batch_id,
                            PendingShipment.upload_batch_id == batch_id
                        ))
                        logger.info(f"应用批次筛选(全部涉及): batch_id={batch_id}")

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

            # 获取分页数据（动态排序）
            # 支持的排序字段映射
            sort_field_map = {
                '订单付款时间': PendingShipment.订单付款时间,
            }

            # 特殊排序：采购信息（品牌→货号→颜色→尺码）
            if sort_by == 'procurement_info':
                # 需要确保已经 JOIN 了 procurement_orders 表
                if not need_join:
                    # 如果之前没有 JOIN，现在需要 JOIN
                    query = query.join(
                        ProcurementOrder,
                        PendingShipment.procurement_order_id == ProcurementOrder.id,
                        isouter=True
                    )

                # 按品牌、货号、颜色、尺码多字段排序
                if sort_order == 'asc':
                    query = query.order_by(
                        ProcurementOrder.品牌.asc().nulls_last(),
                        ProcurementOrder.货号.asc().nulls_last(),
                        ProcurementOrder.颜色.asc().nulls_last(),
                        ProcurementOrder.尺寸.asc().nulls_last()
                    )
                else:
                    query = query.order_by(
                        ProcurementOrder.品牌.desc().nulls_last(),
                        ProcurementOrder.货号.desc().nulls_last(),
                        ProcurementOrder.颜色.desc().nulls_last(),
                        ProcurementOrder.尺寸.desc().nulls_last()
                    )
            elif sort_by == 'procurement_sort':
                # 采购排序逻辑：
                # 1. 按商品（货号/商品名）的最晚付款时间倒序 - 最新采购的商品排前面
                # 2. 颜色升序
                # 3. 尺码升序
                # 4. 付款时间升序
                if not need_join:
                    query = query.join(
                        ProcurementOrder,
                        PendingShipment.procurement_order_id == ProcurementOrder.id,
                        isouter=True
                    )

                from sqlalchemy import func as sql_func

                # 商品分组字段：优先用货号，没有货号用商品标题
                product_group = sql_func.coalesce(ProcurementOrder.货号, PendingShipment.商品标题)

                # 子查询：计算每个商品组的最晚付款时间
                max_time_subquery = (
                    select(
                        sql_func.coalesce(ProcurementOrder.货号, PendingShipment.商品标题).label('product_group'),
                        sql_func.max(PendingShipment.订单付款时间).label('max_payment_time')
                    )
                    .select_from(PendingShipment)
                    .outerjoin(ProcurementOrder, PendingShipment.procurement_order_id == ProcurementOrder.id)
                    .group_by(sql_func.coalesce(ProcurementOrder.货号, PendingShipment.商品标题))
                    .subquery()
                )

                # JOIN子查询获取商品组的最晚付款时间
                query = query.outerjoin(
                    max_time_subquery,
                    product_group == max_time_subquery.c.product_group
                )

                # 颜色和尺寸字段
                color_field = sql_func.coalesce(ProcurementOrder.颜色, '')
                size_field = sql_func.coalesce(ProcurementOrder.尺寸, '')

                query = query.order_by(
                    max_time_subquery.c.max_payment_time.desc().nulls_last(),  # 商品组最晚付款时间倒序
                    color_field.asc(),      # 颜色升序
                    size_field.asc(),       # 尺码升序
                    PendingShipment.订单付款时间.asc()  # 付款时间升序
                )
            else:
                sort_column = sort_field_map.get(sort_by, PendingShipment.订单付款时间)
                if sort_order == 'asc':
                    query = query.order_by(sort_column.asc())
                else:
                    query = query.order_by(sort_column.desc())

            query = query.limit(limit).offset(offset)
            result = await db.execute(query)
            shipments = result.scalars().all()

            # 组装详细数据
            orders_with_details = []
            for shipment in shipments:
                order_dict = shipment.to_dict()

                # 如果有procurement_order_id，获取额外信息
                if shipment.procurement_order_id:
                    po_result = await db.execute(
                        select(ProcurementOrder).where(
                            ProcurementOrder.id == shipment.procurement_order_id
                        )
                    )
                    po = po_result.scalar_one_or_none()
                    if po:
                        order_dict['procurement_info'] = {
                            '品牌': po.品牌,
                            '颜色': po.颜色,
                            '尺码': po.尺寸,
                            '货号': po.货号,
                            '图片': po.main_image_path,
                            '采购方式': po.procurement_method
                        }

                # 如果有地址编码，标记该地址的总订单数（包括当前订单）
                # 使用与主列表一致的显示状态逻辑
                if shipment.address_encoding:
                    same_address_count_result = await db.execute(
                        select(func.count()).select_from(PendingShipment).where(
                            and_(
                                PendingShipment.address_encoding == shipment.address_encoding,
                                # 移除了 id != 过滤条件，现在包含当前订单
                                PendingShipment.系统发货状态 == 'PENDING',
                                # 显示状态条件：与主列表保持一致
                                or_(
                                    PendingShipment.manual_display_status == 'active',
                                    and_(
                                        PendingShipment.manual_display_status.is_(None),
                                        PendingShipment.is_deleted == False
                                    )
                                )
                            )
                        )
                    )
                    count = same_address_count_result.scalar()
                    # 只有当地址有多个订单（2个及以上）时才显示数量
                    order_dict['same_address_count'] = count if count > 1 else 0

                # 如果订单有发货记录（部分发货或已发货），附加发货记录数据
                if shipment.发货状态详情 in ('PARTIAL', 'SHIPPED') or (shipment.已发货数量 or 0) > 0:
                    records = await self.get_shipment_records(db, shipment.id)
                    order_dict['shipment_records'] = records

                # 如果有快递单号，查询该单号关联的订单数（用于拼单发货展示）
                if shipment.系统快递单号:
                    related_count_result = await db.execute(
                        select(func.count(func.distinct(ShipmentRecord.pending_shipment_id)))
                        .where(ShipmentRecord.快递单号 == shipment.系统快递单号)
                    )
                    related_count = related_count_result.scalar() or 0
                    order_dict['related_order_count'] = related_count
                else:
                    order_dict['related_order_count'] = 0

                orders_with_details.append(order_dict)

            return orders_with_details, total

        except Exception as e:
            logger.error(f"获取待发货订单失败：{str(e)}")
            return [], 0

    async def batch_update_shipping_status(
        self,
        db: AsyncSession,
        order_ids: List[int],
        tracking_number: str,
        shipping_company: str,
        operator: str
    ) -> bool:
        """
        批量更新订单发货状态

        注意：此方法会根据订单的购买数量和已发货数量判断最终状态：
        - 如果发货后全部发完，设置为 SHIPPED
        - 如果发货后仍有剩余，设置为 PARTIAL（部分发货）

        Args:
            db: 数据库会话
            order_ids: 订单ID列表
            tracking_number: 快递单号
            shipping_company: 快递公司
            operator: 操作员

        Returns:
            是否成功
        """
        try:
            now = datetime.now()

            # 逐个处理订单，根据数量判断状态
            for order_id in order_ids:
                result = await db.execute(
                    select(PendingShipment).where(PendingShipment.id == order_id)
                )
                shipment = result.scalar_one_or_none()

                if not shipment:
                    logger.warning(f"订单 {order_id} 不存在，跳过")
                    continue

                total_qty = shipment.购买数量 or 1
                current_shipped = shipment.已发货数量 or 0

                # 批量发货默认每次发1件（如果需要发多件，应该用 partial_ship）
                ship_qty = 1
                new_shipped = current_shipped + ship_qty

                # 创建发货记录
                record = ShipmentRecord(
                    pending_shipment_id=order_id,
                    快递单号=tracking_number,
                    快递公司=shipping_company,
                    发货数量=ship_qty,
                    发货时间=now,
                    操作员=operator,
                    备注='批量发货'
                )
                db.add(record)

                # 更新订单信息
                shipment.已发货数量 = new_shipped
                shipment.系统快递单号 = tracking_number
                shipment.系统快递公司 = shipping_company
                shipment.发货操作员 = operator
                shipment.系统发货时间 = now
                shipment.is_deleted = False
                shipment.updated_at = now

                # 根据数量判断发货状态
                if new_shipped >= total_qty:
                    # 全部发货完成
                    shipment.系统发货状态 = 'SHIPPED'
                    shipment.发货状态详情 = 'SHIPPED'
                    logger.info(f"订单 {order_id} 全部发货完成：{new_shipped}/{total_qty}件")
                else:
                    # 部分发货
                    shipment.发货状态详情 = 'PARTIAL'
                    # 保持 系统发货状态 为 PENDING，以便在待发货列表中仍可见
                    shipment.系统发货状态 = 'PENDING'
                    logger.info(f"订单 {order_id} 部分发货：{new_shipped}/{total_qty}件")

            await db.commit()
            logger.info(f"批量更新{len(order_ids)}个订单发货状态成功")
            return True

        except Exception as e:
            logger.error(f"批量更新发货状态失败：{str(e)}")
            await db.rollback()
            return False

    async def partial_ship(
        self,
        db: AsyncSession,
        order_id: int,
        tracking_number: str,
        shipping_company: str,
        ship_quantity: int,
        operator: str,
        remark: Optional[str] = None
    ) -> Dict[str, Any]:
        """
        部分发货 - 支持分次/分包裹发货

        Args:
            db: 数据库会话
            order_id: pending_shipments表的ID
            tracking_number: 快递单号
            shipping_company: 快递公司
            ship_quantity: 本次发货数量
            operator: 操作员
            remark: 备注

        Returns:
            发货结果
        """
        try:
            # 获取订单
            result = await db.execute(
                select(PendingShipment).where(PendingShipment.id == order_id)
            )
            shipment = result.scalar_one_or_none()

            if not shipment:
                return {'success': False, 'error': '订单不存在'}

            # 验证发货数量
            total_quantity = shipment.购买数量 or 1
            already_shipped = shipment.已发货数量 or 0
            remaining = total_quantity - already_shipped

            if ship_quantity <= 0:
                return {'success': False, 'error': '发货数量必须大于0'}

            if ship_quantity > remaining:
                return {'success': False, 'error': f'发货数量超出剩余数量，剩余{remaining}件'}

            # 创建发货记录
            record = ShipmentRecord(
                pending_shipment_id=order_id,
                快递单号=tracking_number,
                快递公司=shipping_company,
                发货数量=ship_quantity,
                发货时间=datetime.now(),
                操作员=operator,
                备注=remark
            )
            db.add(record)

            # 更新发货数量
            new_shipped = already_shipped + ship_quantity
            shipment.已发货数量 = new_shipped
            shipment.updated_at = datetime.now()

            # 判断发货状态
            if new_shipped >= total_quantity:
                # 全部发货完成
                shipment.发货状态详情 = 'SHIPPED'
                shipment.系统发货状态 = 'SHIPPED'
                shipment.系统发货时间 = datetime.now()
                shipment.系统快递单号 = tracking_number  # 最后一次的快递单号
                shipment.系统快递公司 = shipping_company
                shipment.发货操作员 = operator
                shipment.is_deleted = False
            else:
                # 部分发货 - 也需要设置快递信息
                shipment.发货状态详情 = 'PARTIAL'
                shipment.系统发货状态 = 'PARTIAL'
                # 只有首次部分发货时设置发货时间
                if shipment.系统发货时间 is None:
                    shipment.系统发货时间 = datetime.now()
                # 更新为最新的快递信息
                shipment.系统快递单号 = tracking_number
                shipment.系统快递公司 = shipping_company
                shipment.发货操作员 = operator
                shipment.is_deleted = False

            await db.commit()

            logger.info(f"订单 {order_id} 部分发货成功：本次{ship_quantity}件，已发{new_shipped}/{total_quantity}件")
            return {
                'success': True,
                'data': {
                    'order_id': order_id,
                    'shipped_quantity': ship_quantity,
                    'total_shipped': new_shipped,
                    'total_quantity': total_quantity,
                    'status': shipment.发货状态详情,
                    'record_id': record.id
                }
            }

        except Exception as e:
            logger.error(f"部分发货失败：{str(e)}")
            await db.rollback()
            return {'success': False, 'error': str(e)}

    async def get_shipment_records(
        self,
        db: AsyncSession,
        order_id: int
    ) -> List[Dict[str, Any]]:
        """
        获取订单的发货记录

        Args:
            db: 数据库会话
            order_id: pending_shipments表的ID

        Returns:
            发货记录列表
        """
        try:
            result = await db.execute(
                select(ShipmentRecord)
                .where(ShipmentRecord.pending_shipment_id == order_id)
                .order_by(ShipmentRecord.created_at.asc())
            )
            records = result.scalars().all()

            return [record.to_dict() for record in records]

        except Exception as e:
            logger.error(f"获取发货记录失败：{str(e)}")
            return []

    async def batch_partial_ship(
        self,
        db: AsyncSession,
        orders: List[Dict[str, Any]],
        tracking_number: str,
        shipping_company: str,
        operator: str
    ) -> Dict[str, Any]:
        """
        批量部分发货 - 支持多个订单同时发货（同地址合并发货场景）

        Args:
            db: 数据库会话
            orders: 订单列表，每个包含 {order_id, ship_quantity}
            tracking_number: 快递单号
            shipping_company: 快递公司
            operator: 操作员

        Returns:
            批量发货结果
        """
        results = {
            'success': True,
            'total': len(orders),
            'succeeded': 0,
            'failed': 0,
            'details': []
        }

        for order in orders:
            order_id = order.get('order_id')
            ship_quantity = order.get('ship_quantity', 1)

            result = await self.partial_ship(
                db=db,
                order_id=order_id,
                tracking_number=tracking_number,
                shipping_company=shipping_company,
                ship_quantity=ship_quantity,
                operator=operator
            )

            if result['success']:
                results['succeeded'] += 1
            else:
                results['failed'] += 1
                results['success'] = False

            results['details'].append({
                'order_id': order_id,
                **result
            })

        return results

    async def cancel_single_shipment_record(
        self,
        db: AsyncSession,
        record_id: int,
        reason: str,
        operator: str
    ) -> Dict[str, Any]:
        """
        取消单个发货记录（单个包裹）

        Args:
            db: 数据库会话
            record_id: 发货记录ID
            reason: 取消原因
            operator: 操作员

        Returns:
            取消结果
        """
        try:
            # 1. 获取发货记录
            result = await db.execute(
                select(ShipmentRecord).where(ShipmentRecord.id == record_id)
            )
            record = result.scalar_one_or_none()

            if not record:
                return {'success': False, 'error': '发货记录不存在'}

            # 2. 获取关联的订单
            shipment_result = await db.execute(
                select(PendingShipment).where(
                    PendingShipment.id == record.pending_shipment_id
                )
            )
            shipment = shipment_result.scalar_one_or_none()

            if not shipment:
                return {'success': False, 'error': '关联的订单不存在'}

            # 保存记录信息用于日志
            tracking_number = record.快递单号
            ship_quantity = record.发货数量

            # 3. 扣减已发货数量
            current_shipped = shipment.已发货数量 or 0
            new_shipped = max(0, current_shipped - ship_quantity)
            shipment.已发货数量 = new_shipped

            # 4. 重新计算发货状态
            total_quantity = shipment.购买数量 or 1
            if new_shipped <= 0:
                # 没有任何已发货，恢复为待发货
                shipment.发货状态详情 = 'PENDING'
                shipment.系统发货状态 = 'PENDING'
                shipment.系统快递单号 = None
                shipment.系统快递公司 = None
                shipment.系统发货时间 = None
            elif new_shipped < total_quantity:
                # 还有部分发货，保持部分发货状态
                shipment.发货状态详情 = 'PARTIAL'
                # 系统发货状态保持不变或根据业务需要调整
            # 如果 new_shipped >= total_quantity，保持 SHIPPED 状态

            # 5. 记录取消原因
            shipment.取消发货原因 = f"取消包裹{tracking_number}: {reason}"
            shipment.取消发货操作员 = operator
            shipment.取消发货时间 = datetime.now()
            shipment.updated_at = datetime.now()

            # 6. 删除发货记录
            await db.delete(record)

            await db.commit()

            logger.info(f"取消发货记录成功: record_id={record_id}, 快递单号={tracking_number}, "
                        f"扣减数量={ship_quantity}, 剩余已发={new_shipped}/{total_quantity}")

            return {
                'success': True,
                'data': {
                    'record_id': record_id,
                    'order_id': shipment.id,
                    'cancelled_quantity': ship_quantity,
                    'remaining_shipped': new_shipped,
                    'total_quantity': total_quantity,
                    'new_status': shipment.发货状态详情
                }
            }

        except Exception as e:
            logger.error(f"取消发货记录失败：{str(e)}")
            await db.rollback()
            return {'success': False, 'error': str(e)}

    async def get_related_orders_by_tracking(
        self,
        db: AsyncSession,
        tracking_number: str
    ) -> Dict[str, Any]:
        """
        获取同一快递单号关联的所有订单信息

        Args:
            db: 数据库会话
            tracking_number: 快递单号

        Returns:
            包含关联订单列表的字典
        """
        try:
            # 查询该快递单号的所有发货记录及其关联的订单
            result = await db.execute(
                select(ShipmentRecord, PendingShipment)
                .join(PendingShipment, ShipmentRecord.pending_shipment_id == PendingShipment.id)
                .where(ShipmentRecord.快递单号 == tracking_number)
                .order_by(ShipmentRecord.发货时间.desc())
            )
            records = result.all()

            orders = []
            for sr, ps in records:
                orders.append({
                    'record_id': sr.id,
                    '子订单编号': ps.子订单编号,
                    '主订单编号': ps.主订单编号,
                    '商品标题': ps.商品标题,
                    '商品属性': ps.商品属性,
                    '发货数量': sr.发货数量,
                    '发货时间': sr.发货时间.isoformat() if sr.发货时间 else None,
                    '操作员': sr.操作员,
                    '网店名称': ps.网店名称
                })

            return {
                'success': True,
                'tracking_number': tracking_number,
                '快递公司': records[0][0].快递公司 if records else None,
                'order_count': len(orders),
                'orders': orders
            }

        except Exception as e:
            logger.error(f"查询关联订单失败：{str(e)}")
            return {
                'success': False,
                'error': str(e),
                'orders': []
            }