"""自动分单发货插件 — 全部 API 端点

路由前缀：/api/admin/dispatch
"""
from __future__ import annotations

import io
from datetime import datetime, date
from typing import List, Optional

from fastapi import APIRouter, Depends, HTTPException, status
from fastapi.responses import StreamingResponse
from pydantic import BaseModel, Field
from sqlalchemy import select, func, update
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from app.api.deps import get_db, get_admin_user
from app.core.models.user import User
from app.core.models.order import Order, OrderItem
from app.core.models.shipping_carrier import ShippingCarrier
from app.plugins.auto_dispatch.models import ShipmentBatch, ShipmentParcel, ShipmentParcelItem
from app.plugins.auto_dispatch.services.dispatcher import build_parcel_data
from app.plugins.auto_dispatch.services.exporter import build_dispatch_excel
from app.core.services.plugin_helper import require_plugin


async def _require_plugin_active(db: AsyncSession = Depends(get_db)) -> None:
    """路由级守卫：插件停用时返回 503"""
    await require_plugin("auto_dispatch", db)


router = APIRouter(
    prefix="/dispatch",
    tags=["发货管理"],
    dependencies=[Depends(_require_plugin_active)],
)


# ── Schemas ───────────────────────────────────────────────────────────────────

class CreateBatchIn(BaseModel):
    order_ids: List[int] = Field(..., min_length=1)
    carrier_id: int


class ParcelItemOut(BaseModel):
    id: int
    order_item_id: int
    product_name: str
    sku_name: Optional[str]
    qty: int
    model_config = {"from_attributes": True}


class ParcelOut(BaseModel):
    id: int
    order_id: int
    tracking_no: str
    box_index: int
    weight: float
    items: List[ParcelItemOut] = []
    model_config = {"from_attributes": True}


class BatchOut(BaseModel):
    id: int
    batch_no: str
    carrier_id: int
    carrier_name: str
    status: str
    confirmed_at: Optional[datetime]
    order_count: int
    parcel_count: int
    parcels: List[ParcelOut] = []
    model_config = {"from_attributes": True}


class BatchListItem(BaseModel):
    id: int
    batch_no: str
    carrier_name: str
    status: str
    confirmed_at: Optional[datetime]
    order_count: int
    parcel_count: int
    created_at: datetime
    model_config = {"from_attributes": True}


class ReallocateItem(BaseModel):
    parcel_item_id: int
    target_parcel_id: int
    qty: Optional[int] = None  # None = 全部移动；指定数量 = 拆分


class CreateParcelIn(BaseModel):
    order_id: int
    tracking_no: Optional[str] = None  # 手填运单号，不填则自动生成


class ReallocateIn(BaseModel):
    moves: List[ReallocateItem]
    tracking_overrides: dict[int, str] = {}  # {parcel_id: tracking_no}，手填运单号优先


# ── 辅助函数 ──────────────────────────────────────────────────────────────────

async def _get_batch_or_404(batch_id: int, tenant_id: int, db: AsyncSession) -> ShipmentBatch:
    result = await db.execute(
        select(ShipmentBatch)
        .where(ShipmentBatch.id == batch_id, ShipmentBatch.tenant_id == tenant_id)
        .options(
            selectinload(ShipmentBatch.parcels).selectinload(ShipmentParcel.items)
        )
    )
    batch = result.scalar_one_or_none()
    if not batch:
        raise HTTPException(status_code=404, detail="批次不存在")
    return batch


def _format_address(addr: dict) -> str:
    parts = [
        addr.get("country", ""),
        addr.get("province", ""),
        addr.get("city", ""),
        addr.get("district", ""),
        addr.get("address", "") or addr.get("street", ""),
    ]
    return "".join(p for p in parts if p)


# ── 1. 创建批次 ───────────────────────────────────────────────────────────────

@router.post("/batches", status_code=201)
async def create_batch(
    body: CreateBatchIn,
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_admin_user),
):
    """选订单 + 快递公司 → 自动分箱 → 创建草稿批次"""
    tenant_id = user.tenant_id

    # 取快递公司
    carrier_result = await db.execute(
        select(ShippingCarrier).where(
            ShippingCarrier.id == body.carrier_id,
            ShippingCarrier.tenant_id == tenant_id,
        )
    )
    carrier = carrier_result.scalar_one_or_none()
    if not carrier:
        raise HTTPException(status_code=404, detail="快递公司不存在")

    carrier_prefix = (carrier.api_config or {}).get("tracking_prefix") or carrier.code.upper()

    # 校验订单：必须是 paid 且未被 confirmed 批次占用
    orders_result = await db.execute(
        select(Order)
        .where(Order.id.in_(body.order_ids), Order.tenant_id == tenant_id)
        .options(selectinload(Order.items))
    )
    orders = orders_result.scalars().all()

    if len(orders) != len(body.order_ids):
        raise HTTPException(status_code=400, detail="部分订单不存在")

    not_paid = [o.order_no for o in orders if o.status != "paid"]
    if not_paid:
        raise HTTPException(status_code=400, detail=f"以下订单未付款：{', '.join(not_paid)}")

    # 检查是否已有 confirmed 批次占用
    conflict_check = await db.execute(
        select(ShipmentParcel.order_id)
        .join(ShipmentBatch, ShipmentBatch.id == ShipmentParcel.batch_id)
        .where(
            ShipmentParcel.order_id.in_(body.order_ids),
            ShipmentBatch.status == "confirmed",
            ShipmentBatch.tenant_id == tenant_id,
        )
    )
    conflict_ids = [r[0] for r in conflict_check.fetchall()]
    if conflict_ids:
        conflict_nos = [o.order_no for o in orders if o.id in conflict_ids]
        raise HTTPException(status_code=400, detail=f"以下订单已发货：{', '.join(conflict_nos)}")

    # 生成批次号
    today_str = date.today().strftime("%Y%m%d")
    count_result = await db.execute(
        select(func.count(ShipmentBatch.id)).where(
            ShipmentBatch.tenant_id == tenant_id,
            ShipmentBatch.batch_no.like(f"{today_str}-%"),
        )
    )
    batch_seq = (count_result.scalar() or 0) + 1
    batch_no = f"{today_str}-{batch_seq:03d}"

    # 创建批次
    batch = ShipmentBatch(
        tenant_id=tenant_id,
        batch_no=batch_no,
        carrier_id=body.carrier_id,
        status="draft",
        created_by=user.id,
    )
    db.add(batch)
    await db.flush()  # 获取 batch.id

    # 对每个订单分箱并创建 ShipmentParcel + ShipmentParcelItem
    for order in orders:
        oi_list = [
            {
                "id": oi.id,
                "product_snapshot": oi.product_snapshot if isinstance(oi.product_snapshot, dict) else {},
                "quantity": oi.quantity,
                "variant_id": getattr(oi, "variant_id", None),
                "product_id": getattr(oi, "product_id", 0),
                "unit_price": float(getattr(oi, "unit_price", 0)),
            }
            for oi in order.items
        ]
        parcel_data_list = build_parcel_data(
            order_id=order.id,
            order_no=order.order_no,
            order_items=oi_list,
            carrier_prefix=carrier_prefix,
            rules=[],
        )
        for pd in parcel_data_list:
            parcel = ShipmentParcel(
                tenant_id=tenant_id,
                batch_id=batch.id,
                order_id=pd["order_id"],
                tracking_no=pd["tracking_no"],
                box_index=pd["box_index"],
                weight=pd["weight"],
            )
            db.add(parcel)
            await db.flush()

            for item_data in pd["items"]:
                db.add(ShipmentParcelItem(
                    parcel_id=parcel.id,
                    order_item_id=item_data["order_item_id"],
                    product_name=item_data["product_name"],
                    sku_name=item_data["sku_name"],
                    qty=item_data["qty"],
                ))

    await db.commit()

    # 返回完整批次详情
    return await _get_batch_detail(batch.id, tenant_id, carrier.name, db)


# ── 2. 批次列表 ───────────────────────────────────────────────────────────────

@router.get("/batches")
async def list_batches(
    page: int = 1,
    page_size: int = 20,
    status: Optional[str] = None,
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_admin_user),
):
    tenant_id = user.tenant_id
    q = select(ShipmentBatch).where(ShipmentBatch.tenant_id == tenant_id)
    if status:
        q = q.where(ShipmentBatch.status == status)
    q = q.order_by(ShipmentBatch.created_at.desc())

    total_result = await db.execute(select(func.count()).select_from(q.subquery()))
    total = total_result.scalar()

    q = q.offset((page - 1) * page_size).limit(page_size)
    batches_result = await db.execute(q)
    batches = batches_result.scalars().all()

    # 批量取快递公司名称
    carrier_ids = list({b.carrier_id for b in batches})
    carrier_map = {}
    if carrier_ids:
        c_result = await db.execute(
            select(ShippingCarrier).where(ShippingCarrier.id.in_(carrier_ids))
        )
        carrier_map = {c.id: c.name for c in c_result.scalars().all()}

    # 批量取箱数、订单数、订单号
    parcel_stats = {}
    order_nos_by_batch: dict[int, list[str]] = {}
    if batches:
        batch_ids = [b.id for b in batches]
        stats_result = await db.execute(
            select(
                ShipmentParcel.batch_id,
                func.count(ShipmentParcel.id).label("parcel_count"),
                func.count(ShipmentParcel.order_id.distinct()).label("order_count"),
            )
            .where(ShipmentParcel.batch_id.in_(batch_ids))
            .group_by(ShipmentParcel.batch_id)
        )
        parcel_stats = {row.batch_id: row for row in stats_result.fetchall()}

        # 取每个批次对应的订单号
        parcels_mini = await db.execute(
            select(ShipmentParcel.batch_id, ShipmentParcel.order_id)
            .where(ShipmentParcel.batch_id.in_(batch_ids))
            .distinct()
        )
        batch_order_ids: dict[int, list[int]] = {}
        for row in parcels_mini.fetchall():
            batch_order_ids.setdefault(row.batch_id, []).append(row.order_id)

        all_order_ids = list({oid for oids in batch_order_ids.values() for oid in oids})
        if all_order_ids:
            ono_result = await db.execute(
                select(Order.id, Order.order_no).where(Order.id.in_(all_order_ids))
            )
            ono_map = {row.id: row.order_no for row in ono_result.fetchall()}
            for bid, oids in batch_order_ids.items():
                order_nos_by_batch[bid] = [ono_map.get(oid, str(oid)) for oid in sorted(oids)]

    items = []
    for b in batches:
        stats = parcel_stats.get(b.id)
        items.append({
            "id": b.id,
            "batch_no": b.batch_no,
            "carrier_name": carrier_map.get(b.carrier_id, ""),
            "status": b.status,
            "confirmed_at": b.confirmed_at,
            "order_count": stats.order_count if stats else 0,
            "parcel_count": stats.parcel_count if stats else 0,
            "created_at": b.created_at,
            "order_nos": order_nos_by_batch.get(b.id, []),
        })

    return {"total": total, "items": items}


# ── 3. 批次详情 ───────────────────────────────────────────────────────────────

@router.get("/batches/{batch_id}")
async def get_batch(
    batch_id: int,
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_admin_user),
):
    batch = await _get_batch_or_404(batch_id, user.tenant_id, db)
    carrier_result = await db.execute(
        select(ShippingCarrier).where(ShippingCarrier.id == batch.carrier_id)
    )
    carrier = carrier_result.scalar_one_or_none()
    carrier_name = carrier.name if carrier else ""
    return await _get_batch_detail(batch_id, user.tenant_id, carrier_name, db)


async def _get_batch_detail(batch_id: int, tenant_id: int, carrier_name: str, db: AsyncSession):
    result = await db.execute(
        select(ShipmentBatch)
        .where(ShipmentBatch.id == batch_id, ShipmentBatch.tenant_id == tenant_id)
        .options(
            selectinload(ShipmentBatch.parcels).selectinload(ShipmentParcel.items)
        )
    )
    batch = result.scalar_one_or_none()
    if not batch:
        raise HTTPException(status_code=404, detail="批次不存在")

    order_ids = list({p.order_id for p in batch.parcels})

    # 查订单号，供前端显示
    orders_result = await db.execute(
        select(Order.id, Order.order_no).where(Order.id.in_(order_ids))
    )
    order_no_map = {row.id: row.order_no for row in orders_result.fetchall()}

    parcels_out = []
    for parcel in sorted(batch.parcels, key=lambda p: (p.order_id, p.box_index)):
        parcels_out.append({
            "id": parcel.id,
            "order_id": parcel.order_id,
            "order_no": order_no_map.get(parcel.order_id, ""),
            "tracking_no": parcel.tracking_no,
            "box_index": parcel.box_index,
            "weight": float(parcel.weight),
            "items": [
                {
                    "id": item.id,
                    "order_item_id": item.order_item_id,
                    "product_name": item.product_name,
                    "sku_name": item.sku_name,
                    "qty": item.qty,
                }
                for item in parcel.items
            ],
        })

    return {
        "id": batch.id,
        "batch_no": batch.batch_no,
        "carrier_id": batch.carrier_id,
        "carrier_name": carrier_name,
        "status": batch.status,
        "confirmed_at": batch.confirmed_at,
        "order_count": len(order_ids),
        "parcel_count": len(batch.parcels),
        "parcels": parcels_out,
    }


# ── 4. 删除草稿批次 ───────────────────────────────────────────────────────────

@router.delete("/batches/{batch_id}", status_code=204)
async def delete_batch(
    batch_id: int,
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_admin_user),
):
    batch = await _get_batch_or_404(batch_id, user.tenant_id, db)
    if batch.status == "confirmed":
        raise HTTPException(status_code=400, detail="已确认批次不可删除")
    await db.delete(batch)
    await db.commit()


# ── 4a. 重新发货（撤销已确认批次，回滚订单状态）──────────────────────────────

@router.post("/batches/{batch_id}/redispatch", status_code=200)
async def redispatch_batch(
    batch_id: int,
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_admin_user),
):
    """撤销已确认批次：回滚订单状态至 paid，清除快递信息，删除批次记录"""
    batch = await _get_batch_or_404(batch_id, user.tenant_id, db)
    if batch.status != "confirmed":
        raise HTTPException(status_code=400, detail="仅已确认批次可撤销重发")

    # 收集该批次写入的所有 tracking_no，用于从订单 shipments 中精确过滤
    batch_tracking_nos = {p.tracking_no for p in batch.parcels}

    # 按订单聚合
    from collections import defaultdict
    order_ids = list({p.order_id for p in batch.parcels})
    orders_result = await db.execute(
        select(Order).where(Order.id.in_(order_ids), Order.tenant_id == user.tenant_id)
    )
    orders = orders_result.scalars().all()

    for order in orders:
        # 回滚订单状态
        order.status = "paid"
        order.tracking_no = None
        order.carrier = None

        # 从 extra_attributes["shipments"] 移除本批次写入的面单记录
        extra = dict(order.extra_attributes or {})
        shipments = [
            s for s in (extra.get("shipments") or [])
            if s.get("tracking_no") not in batch_tracking_nos
        ]
        extra["shipments"] = shipments
        order.extra_attributes = extra

    await db.delete(batch)
    await db.commit()

    return {"message": "批次已撤销，订单状态已回滚至待发货", "order_ids": order_ids}


# ── 5a. 新增空箱（前端临时箱转真实记录）─────────────────────────────────────

@router.post("/batches/{batch_id}/parcels/new", status_code=201)
async def add_parcel(
    batch_id: int,
    body: CreateParcelIn,
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_admin_user),
):
    """为批次中的某个订单新建一个空箱，返回真实 parcel id 和 tracking_no"""
    batch = await _get_batch_or_404(batch_id, user.tenant_id, db)
    if batch.status == "confirmed":
        raise HTTPException(status_code=400, detail="已确认批次不可修改")

    order_parcels = [p for p in batch.parcels if p.order_id == body.order_id]
    if not order_parcels:
        raise HTTPException(status_code=400, detail="订单不属于该批次")

    new_index = max(p.box_index for p in order_parcels) + 1

    carrier_result = await db.execute(
        select(ShippingCarrier).where(ShippingCarrier.id == batch.carrier_id)
    )
    carrier = carrier_result.scalar_one_or_none()
    carrier_prefix = (carrier.api_config or {}).get("tracking_prefix") or carrier.code.upper()

    order_result = await db.execute(select(Order).where(Order.id == body.order_id))
    order = order_result.scalar_one_or_none()

    from app.plugins.auto_dispatch.services.dispatcher import generate_tracking_no
    tracking_no = body.tracking_no or generate_tracking_no(order.order_no, carrier_prefix, new_index)

    new_parcel = ShipmentParcel(
        tenant_id=user.tenant_id,
        batch_id=batch_id,
        order_id=body.order_id,
        tracking_no=tracking_no,
        box_index=new_index,
        weight=0,
    )
    db.add(new_parcel)
    await db.commit()
    await db.refresh(new_parcel)

    return {"id": new_parcel.id, "tracking_no": tracking_no, "box_index": new_index}


# ── 5. 调整商品分配（拖拽保存）────────────────────────────────────────────────

@router.patch("/batches/{batch_id}/parcels")
async def reallocate_items(
    batch_id: int,
    body: ReallocateIn,
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_admin_user),
):
    """
    将 parcel_item_id 移动到 target_parcel_id。
    校验：两个 parcel 必须属于同一 order（跨订单不允许）。
    空箱自动删除并重排 tracking_no。
    """
    batch = await _get_batch_or_404(batch_id, user.tenant_id, db)
    if batch.status == "confirmed":
        raise HTTPException(status_code=400, detail="已确认批次不可修改")

    # 构建 parcel_id → order_id 映射
    parcel_order_map = {p.id: p.order_id for p in batch.parcels}

    for move in body.moves:
        item_result = await db.execute(
            select(ShipmentParcelItem).where(ShipmentParcelItem.id == move.parcel_item_id)
        )
        item = item_result.scalar_one_or_none()
        if not item:
            raise HTTPException(status_code=404, detail=f"parcel_item {move.parcel_item_id} 不存在")

        src_order_id = parcel_order_map.get(item.parcel_id)
        tgt_order_id = parcel_order_map.get(move.target_parcel_id)

        if src_order_id != tgt_order_id:
            raise HTTPException(status_code=400, detail="不允许跨订单移动商品")

        move_qty = move.qty if move.qty is not None else item.qty
        if move_qty <= 0 or move_qty > item.qty:
            raise HTTPException(status_code=400, detail=f"移动数量 {move_qty} 超出商品库存 {item.qty}")

        if move_qty >= item.qty:
            # 整件移动
            item.parcel_id = move.target_parcel_id
        else:
            # 部分移动：拆分成两行
            item.qty -= move_qty
            db.add(ShipmentParcelItem(
                parcel_id=move.target_parcel_id,
                order_item_id=item.order_item_id,
                product_name=item.product_name,
                sku_name=item.sku_name,
                qty=move_qty,
            ))

    await db.flush()

    # 删除空箱 + 重排 box_index 和 tracking_no
    carrier_result = await db.execute(
        select(ShippingCarrier).where(ShippingCarrier.id == batch.carrier_id)
    )
    carrier = carrier_result.scalar_one_or_none()
    carrier_prefix = (carrier.api_config or {}).get("tracking_prefix") or carrier.code.upper()

    # 重新加载 parcels
    await db.refresh(batch, ["parcels"])
    for parcel in list(batch.parcels):
        await db.refresh(parcel, ["items"])

    # 按订单分组，重排
    from collections import defaultdict
    order_parcels: dict[int, list] = defaultdict(list)
    for p in batch.parcels:
        order_parcels[p.order_id].append(p)

    # 查订单号
    order_ids = list(order_parcels.keys())
    orders_result = await db.execute(
        select(Order.id, Order.order_no).where(Order.id.in_(order_ids))
    )
    order_no_map = {row.id: row.order_no for row in orders_result.fetchall()}

    for order_id, parcels in order_parcels.items():
        order_no = order_no_map.get(order_id, "")
        non_empty = [p for p in parcels if p.items]
        empty = [p for p in parcels if not p.items]
        for ep in empty:
            await db.delete(ep)

        non_empty.sort(key=lambda p: p.box_index)
        for new_idx, p in enumerate(non_empty, start=1):
            p.box_index = new_idx
            from app.plugins.auto_dispatch.services.dispatcher import generate_tracking_no
            p.tracking_no = body.tracking_overrides.get(p.id) or generate_tracking_no(order_no, carrier_prefix, new_idx)

    await db.commit()
    return await _get_batch_detail(batch_id, user.tenant_id, carrier.name if carrier else "", db)


# ── 6. 确认发货 ───────────────────────────────────────────────────────────────

@router.post("/batches/{batch_id}/confirm")
async def confirm_batch(
    batch_id: int,
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_admin_user),
):
    """确认发货：回填 order.tracking_no / order.carrier / order.status / extra shipments"""
    import uuid as _uuid
    from collections import defaultdict

    batch = await _get_batch_or_404(batch_id, user.tenant_id, db)
    if batch.status == "confirmed":
        raise HTTPException(status_code=400, detail="已确认，请勿重复操作")

    carrier_result = await db.execute(
        select(ShippingCarrier).where(ShippingCarrier.id == batch.carrier_id)
    )
    carrier = carrier_result.scalar_one_or_none()
    carrier_name = carrier.name if carrier else ""

    # 按订单聚合各箱 tracking_no（有序）
    order_trackings: dict[int, list[str]] = defaultdict(list)
    for parcel in sorted(batch.parcels, key=lambda p: p.box_index):
        order_trackings[parcel.order_id].append(parcel.tracking_no)

    now_str = datetime.utcnow().isoformat(timespec="seconds")

    skipped = []
    for order_id, tracking_nos in order_trackings.items():
        order_result = await db.execute(
            select(Order).where(Order.id == order_id, Order.tenant_id == user.tenant_id)
        )
        order = order_result.scalar_one_or_none()
        if not order or order.status not in ("paid",):
            skipped.append(order_id)
            continue

        order.tracking_no = ",".join(tracking_nos)
        order.carrier = carrier_name
        order.status = "shipped"

        # 写入 extra_attributes["shipments"]，供订单详情页展示
        extra = dict(order.extra_attributes or {})
        existing = list(extra.get("shipments") or [])
        for tn in tracking_nos:
            existing.append({
                "id": str(_uuid.uuid4()),
                "carrier": carrier_name,
                "tracking_no": tn,
                "estimated_delivery": "",
                "note": "",
                "added_at": now_str,
            })
        extra["shipments"] = existing
        order.extra_attributes = extra

    batch.status = "confirmed"
    batch.confirmed_at = datetime.utcnow()
    await db.commit()

    return {
        "message": "确认发货成功",
        "skipped_order_ids": skipped,
    }


# ── 7a. 快速导出（从订单列表直接导出，按已确认批次分箱，每箱一行）────────────

class QuickExportIn(BaseModel):
    order_ids: List[int] = Field(..., min_length=1)


@router.post("/quick-export")
async def quick_export(
    body: QuickExportIn,
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_admin_user),
):
    """
    按已确认批次的实际分箱导出，每箱一行，含快递公司和单号。
    若某订单尚未在已确认批次中，则回退为一行（快递信息留空）。
    """
    from app.core.models.tenant import Tenant

    # 基础信息
    tenant_result = await db.execute(select(Tenant).where(Tenant.id == user.tenant_id))
    tenant = tenant_result.scalar_one_or_none()
    sender_name = tenant.name if tenant else "SME Store"

    orders_result = await db.execute(
        select(Order).where(Order.id.in_(body.order_ids), Order.tenant_id == user.tenant_id)
    )
    order_map = {o.id: o for o in orders_result.scalars().all()}
    if not order_map:
        raise HTTPException(status_code=404, detail="订单不存在")

    # 查这些订单在已确认批次里的分箱记录
    parcels_result = await db.execute(
        select(ShipmentParcel)
        .join(ShipmentBatch, ShipmentBatch.id == ShipmentParcel.batch_id)
        .where(
            ShipmentParcel.order_id.in_(body.order_ids),
            ShipmentBatch.tenant_id == user.tenant_id,
            ShipmentBatch.status == "confirmed",
        )
        .options(selectinload(ShipmentParcel.items))
        .order_by(ShipmentParcel.order_id, ShipmentParcel.box_index)
    )
    confirmed_parcels = parcels_result.scalars().all()

    # 取快递公司名称
    batch_ids = list({p.batch_id for p in confirmed_parcels})
    carrier_by_batch: dict[int, str] = {}
    if batch_ids:
        batches_result = await db.execute(
            select(ShipmentBatch).where(ShipmentBatch.id.in_(batch_ids))
        )
        batch_carrier_ids = {b.id: b.carrier_id for b in batches_result.scalars().all()}
        carrier_ids = list(set(batch_carrier_ids.values()))
        carriers_result = await db.execute(
            select(ShippingCarrier).where(ShippingCarrier.id.in_(carrier_ids))
        )
        carrier_name_map = {c.id: c.name for c in carriers_result.scalars().all()}
        for bid, cid in batch_carrier_ids.items():
            carrier_by_batch[bid] = carrier_name_map.get(cid, "")

    # 已有分箱记录的订单 ID
    dispatched_order_ids = {p.order_id for p in confirmed_parcels}

    parcels_data = []

    # 已确认分箱订单：每箱一行
    for parcel in confirmed_parcels:
        order = order_map.get(parcel.order_id)
        addr = (order.shipping_address if order else {}) or {}
        goods_parts = [
            f"{item.product_name}{' ' + item.sku_name if item.sku_name else ''} *{item.qty}"
            for item in parcel.items
        ]
        total_qty = sum(item.qty for item in parcel.items)
        parcels_data.append({
            "carrier_name": carrier_by_batch.get(parcel.batch_id, ""),
            "tracking_no": parcel.tracking_no,
            "recipient_name": addr.get("name", "") or addr.get("full_name", ""),
            "recipient_phone": addr.get("phone", "") or addr.get("mobile", ""),
            "recipient_address": _format_address(addr),
            "sender_name": sender_name,
            "goods_detail": ", ".join(goods_parts),
            "goods_qty": total_qty,
            "weight": float(parcel.weight),
            "id_card": addr.get("id_card", "") or addr.get("id_number", ""),
        })

    # 未发货订单（不在已确认批次中）：每订单一行，快递信息留空
    undispatched_ids = [oid for oid in body.order_ids if oid not in dispatched_order_ids]
    if undispatched_ids:
        oi_result = await db.execute(
            select(OrderItem).where(OrderItem.order_id.in_(undispatched_ids))
        )
        items_by_order: dict[int, list] = {}
        for item in oi_result.scalars().all():
            items_by_order.setdefault(item.order_id, []).append(item)

        for oid in undispatched_ids:
            order = order_map.get(oid)
            if not order:
                continue
            addr = (order.shipping_address or {})
            items = items_by_order.get(oid, [])
            goods_parts = []
            total_qty = 0
            for item in items:
                snap = item.product_snapshot or {}
                name = snap.get("name") or snap.get("product_name") or "商品"
                variant = snap.get("variant") or snap.get("sku_name") or ""
                goods_parts.append(f"{name}{' ' + variant if variant else ''} *{item.quantity}")
                total_qty += item.quantity
            parcels_data.append({
                "carrier_name": "",
                "tracking_no": "",
                "recipient_name": addr.get("name", "") or addr.get("full_name", ""),
                "recipient_phone": addr.get("phone", "") or addr.get("mobile", ""),
                "recipient_address": _format_address(addr),
                "sender_name": sender_name,
                "goods_detail": ", ".join(goods_parts),
                "goods_qty": total_qty,
                "weight": 0,
                "id_card": addr.get("id_card", "") or addr.get("id_number", ""),
            })

    from datetime import date as _date
    batch_label = _date.today().strftime("%Y%m%d")
    xlsx_bytes = build_dispatch_excel(parcels_data, batch_no=batch_label)
    filename = f"dispatch_orders_{batch_label}.xlsx"

    return StreamingResponse(
        io.BytesIO(xlsx_bytes),
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": f"attachment; filename={filename}"},
    )


# ── 7. 导出 Excel ─────────────────────────────────────────────────────────────

@router.get("/batches/{batch_id}/export")
async def export_excel(
    batch_id: int,
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_admin_user),
):
    batch = await _get_batch_or_404(batch_id, user.tenant_id, db)
    if batch.status != "confirmed":
        raise HTTPException(status_code=400, detail="仅已确认批次可导出")

    carrier_result = await db.execute(
        select(ShippingCarrier).where(ShippingCarrier.id == batch.carrier_id)
    )
    carrier = carrier_result.scalar_one_or_none()
    carrier_name = carrier.name if carrier else ""

    from app.core.models.tenant import Tenant
    tenant_result = await db.execute(
        select(Tenant).where(Tenant.id == user.tenant_id)
    )
    tenant = tenant_result.scalar_one_or_none()
    sender_name = tenant.name if tenant else "SME Store"

    order_ids = list({p.order_id for p in batch.parcels})
    orders_result = await db.execute(
        select(Order).where(Order.id.in_(order_ids))
    )
    order_map = {o.id: o for o in orders_result.scalars().all()}

    parcels_data = []
    for parcel in sorted(batch.parcels, key=lambda p: (p.order_id, p.box_index)):
        order = order_map.get(parcel.order_id)
        addr = (order.shipping_address if order else {}) or {}

        goods_parts = [
            f"{item.product_name}{' ' + item.sku_name if item.sku_name else ''} *{item.qty}"
            for item in parcel.items
        ]
        total_qty = sum(item.qty for item in parcel.items)

        parcels_data.append({
            "carrier_name": carrier_name,
            "tracking_no": parcel.tracking_no,
            "recipient_name": addr.get("name", "") or addr.get("full_name", ""),
            "recipient_phone": addr.get("phone", "") or addr.get("mobile", ""),
            "recipient_address": _format_address(addr),
            "sender_name": sender_name,
            "goods_detail": ",".join(goods_parts),
            "goods_qty": total_qty,
            "weight": float(parcel.weight),
            "id_card": addr.get("id_card", "") or addr.get("id_number", ""),
        })

    xlsx_bytes = build_dispatch_excel(parcels_data, batch_no=batch.batch_no)
    filename = f"dispatch_{batch.batch_no}_{date.today().strftime('%Y%m%d')}.xlsx"

    return StreamingResponse(
        io.BytesIO(xlsx_bytes),
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": f"attachment; filename={filename}"},
    )


# ── 8. 获取面单数据 ───────────────────────────────────────────────────────────

@router.get("/batches/{batch_id}/labels")
async def get_labels(
    batch_id: int,
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_admin_user),
):
    """返回 JSON 数据供前端渲染面单（前端负责条码生成和打印）"""
    batch = await _get_batch_or_404(batch_id, user.tenant_id, db)

    carrier_result = await db.execute(
        select(ShippingCarrier).where(ShippingCarrier.id == batch.carrier_id)
    )
    carrier = carrier_result.scalar_one_or_none()
    carrier_name = carrier.name if carrier else ""

    from app.core.models.tenant import Tenant
    tenant_result = await db.execute(
        select(Tenant).where(Tenant.id == user.tenant_id)
    )
    tenant = tenant_result.scalar_one_or_none()
    sender_name = tenant.name if tenant else "SME Store"

    order_ids = list({p.order_id for p in batch.parcels})
    orders_result = await db.execute(
        select(Order).where(Order.id.in_(order_ids))
    )
    order_map = {o.id: o for o in orders_result.scalars().all()}

    labels = []
    for parcel in sorted(batch.parcels, key=lambda p: (p.order_id, p.box_index)):
        order = order_map.get(parcel.order_id)
        addr = (order.shipping_address if order else {}) or {}
        labels.append({
            "tracking_no": parcel.tracking_no,
            "carrier_name": carrier_name,
            "sender_name": sender_name,
            "recipient_name": addr.get("name", "") or addr.get("full_name", ""),
            "recipient_phone": addr.get("phone", "") or addr.get("mobile", ""),
            "recipient_address": _format_address(addr),
            "confirmed_at": batch.confirmed_at.strftime("%Y-%m-%d") if batch.confirmed_at else "",
            "items": [
                {
                    "product_name": item.product_name,
                    "sku_name": item.sku_name,
                    "qty": item.qty,
                }
                for item in parcel.items
            ],
        })

    return {"carrier_name": carrier_name, "labels": labels}