"""AI 销售分析助手 + AI 建单解析"""
import io
import csv
import json
import re
from collections import OrderedDict
from datetime import date, timedelta
from decimal import Decimal

from fastapi import APIRouter, Depends, HTTPException, UploadFile, File
from fastapi.responses import StreamingResponse
from pydantic import BaseModel
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func, case
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment

from app.api.deps import get_db, get_admin_user, require_permission
from app.core.models.user import User
from app.core.models.tenant_settings import TenantSettings
from app.core.models.order import Order, OrderItem
from app.core.models.product import Product, ProductVariant
from app.core.models.customer import Customer
from app.core.models.category import Category
from app.core.ai_utils import call_ai, resolve_ai_extra
from app.core.services.ai_quota import consume_ai_quota

router = APIRouter(prefix="/admin/ai", tags=["AI 辅助"])

PAID_STATUSES = ["paid", "shipped", "completed"]


def _effective_expiry_expr(product_date, variant_date):
    return case(
        (product_date.is_(None), variant_date),
        (variant_date.is_(None), product_date),
        else_=func.least(product_date, variant_date),
    )
_SYSTEM_PROMPT = """你是一位专业的电商数据分析师，擅长解读销售数据、发现趋势和异常，并给出可执行的运营建议。
回答要简洁、直接、有数据支撑。使用中文回答。不要重复输入的数据，直接给出洞察和建议。
如果数据不足以回答问题，请说明并给出可能的方向。"""


class AnalyticsRequest(BaseModel):
    question: str
    range: str = "30d"


class AnalyticsResponse(BaseModel):
    answer: str
    data_summary: str


async def _gather_sales_context(db: AsyncSession, tenant_id: int, range_str: str) -> str:
    """查询关键销售指标，组装为自然语言上下文"""
    today = date.today()
    days  = {"7d": 7, "30d": 30, "90d": 90}.get(range_str, 30)
    start = today - timedelta(days=days - 1)

    # 1. 总营收 & 订单数
    rev_row = await db.execute(
        select(
            func.coalesce(func.sum(Order.grand_total), 0).label("revenue"),
            func.count(Order.id).label("cnt"),
        ).where(
            Order.tenant_id == tenant_id,
            Order.status.in_(PAID_STATUSES),
            func.date(Order.created_at) >= start,
            func.date(Order.created_at) <= today,
        )
    )
    rev = rev_row.one()
    total_revenue = float(rev.revenue)
    total_orders  = int(rev.cnt)
    avg_order_val = round(total_revenue / total_orders, 2) if total_orders else 0

    # 2. 待处理订单
    pending_row = await db.execute(
        select(func.count(Order.id)).where(
            Order.tenant_id == tenant_id,
            Order.status == "pending",
        )
    )
    pending_orders = pending_row.scalar() or 0

    # 3. 订单状态分布
    status_rows = await db.execute(
        select(Order.status, func.count(Order.id).label("cnt")).where(
            Order.tenant_id == tenant_id,
            func.date(Order.created_at) >= start,
        ).group_by(Order.status)
    )
    status_dist = {r.status: r.cnt for r in status_rows}

    # 4. 前5热销商品
    top_products_rows = await db.execute(
        select(
            Product.name,
            func.sum(OrderItem.quantity).label("qty"),
            func.sum(OrderItem.quantity * OrderItem.unit_price).label("rev"),
        )
        .join(Order, OrderItem.order_id == Order.id)
        .join(Product, OrderItem.product_id == Product.id)
        .where(
            Order.tenant_id == tenant_id,
            Order.status.in_(PAID_STATUSES),
            func.date(Order.created_at) >= start,
        )
        .group_by(Product.id, Product.name)
        .order_by(func.sum(OrderItem.quantity * OrderItem.unit_price).desc())
        .limit(5)
    )
    top_products = [
        f"{r.name}（销售额¥{float(r.rev):.0f}，销量{int(r.qty)}件）"
        for r in top_products_rows
    ]

    # 5. 分类销售
    cat_rows = await db.execute(
        select(
            Category.name.label("cat"),
            func.sum(OrderItem.quantity * OrderItem.unit_price).label("rev"),
            func.count(func.distinct(Order.id)).label("orders"),
        )
        .join(Order,    OrderItem.order_id  == Order.id)
        .join(Product,  OrderItem.product_id == Product.id)
        .join(Category, Product.category_id  == Category.id)
        .where(
            Order.tenant_id == tenant_id,
            Order.status.in_(PAID_STATUSES),
            func.date(Order.created_at) >= start,
        )
        .group_by(Category.id, Category.name)
        .order_by(func.sum(OrderItem.quantity * OrderItem.unit_price).desc())
        .limit(5)
    )
    categories = [
        f"{r.cat}（¥{float(r.rev):.0f}，{r.orders}单）"
        for r in cat_rows
    ]

    # 6. 近7天每日营收（趋势）
    trend_rows = await db.execute(
        select(
            func.date(Order.created_at).label("day"),
            func.coalesce(func.sum(Order.grand_total), 0).label("rev"),
            func.count(Order.id).label("cnt"),
        )
        .where(
            Order.tenant_id == tenant_id,
            Order.status.in_(PAID_STATUSES),
            func.date(Order.created_at) >= today - timedelta(days=6),
            func.date(Order.created_at) <= today,
        )
        .group_by(func.date(Order.created_at))
        .order_by(func.date(Order.created_at))
    )
    trend_lines = [
        f"{str(r.day)}：¥{float(r.rev):.0f}（{r.cnt}单）"
        for r in trend_rows
    ]

    # 7. 低库存商品数
    low_stock_row = await db.execute(
        select(func.count(Product.id)).where(
            Product.tenant_id == tenant_id,
            Product.status == "active",
            Product.stock_qty <= Product.low_stock_threshold,
        )
    )
    low_stock_count = low_stock_row.scalar() or 0

    settings_row = await db.execute(select(TenantSettings.extra).where(TenantSettings.tenant_id == tenant_id))
    extra = settings_row.scalar_one_or_none() or {}
    try:
        warning_days = max(0, int(extra.get("expiry_warning_days", 30)))
    except (TypeError, ValueError):
        warning_days = 30
    cutoff = today + timedelta(days=warning_days)
    variant_expiry = (
        select(
            ProductVariant.product_id.label("product_id"),
            func.min(ProductVariant.expiry_date).label("variant_expiry_date"),
        )
        .where(ProductVariant.tenant_id == tenant_id)
        .group_by(ProductVariant.product_id)
        .subquery()
    )
    effective_expiry = _effective_expiry_expr(Product.expiry_date, variant_expiry.c.variant_expiry_date)
    expiry_rows = await db.execute(
        select(Product.name, Product.sku, effective_expiry.label("expiry_date"))
        .outerjoin(variant_expiry, variant_expiry.c.product_id == Product.id)
        .where(
            Product.tenant_id == tenant_id,
            Product.status == "active",
            effective_expiry.is_not(None),
            effective_expiry <= cutoff,
        )
        .order_by(effective_expiry.asc())
        .limit(20)
    )
    expired_products = []
    expiring_products = []
    for r in expiry_rows.all():
        line = f"{r.name}（SKU:{r.sku or '-'}，到期日:{r.expiry_date}）"
        if r.expiry_date < today:
            expired_products.append(line)
        else:
            expiring_products.append(line)
    lines = [
        f"═══ 店铺销售数据（近{days}天，截至{today}）═══",
        f"总营收：¥{total_revenue:,.2f}",
        f"有效订单数：{total_orders}",
        f"平均客单价：¥{avg_order_val}",
        f"当前待处理订单：{pending_orders}",
        "",
        f"订单状态分布：{', '.join(f'{k}({v}单)' for k,v in status_dist.items())}",
        "",
        f"热销商品 TOP5：",
    ]
    lines += [f"  {i+1}. {p}" for i, p in enumerate(top_products)] or ["  （暂无数据）"]
    lines += ["", "分类销售 TOP5："]
    lines += [f"  {i+1}. {c}" for i, c in enumerate(categories)] or ["  （暂无数据）"]
    lines += ["", "近7天每日营收趋势："]
    lines += [f"  {t}" for t in trend_lines] or ["  （暂无数据）"]
    lines += ["", f"低库存商品数量：{low_stock_count} 件"]
    lines += ["", f"已过期商品：{len(expired_products)} 件"]
    lines += [f"  {p}" for p in expired_products[:10]] or ["  （暂无）"]
    lines += ["", f"{warning_days}天内临期商品：{len(expiring_products)} 件"]
    lines += [f"  {p}" for p in expiring_products[:10]] or ["  （暂无）"]

    return "\n".join(lines)


@router.post("/analytics", response_model=AnalyticsResponse, summary="AI 销售数据分析")
async def ai_analytics(
    body: AnalyticsRequest,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_admin_user),
):
    """
    根据用户问题，自动查询店铺销售数据，结合 AI 给出分析和建议。
    """
    if not body.question.strip():
        raise HTTPException(status_code=400, detail="问题不能为空")

    result = await db.execute(
        select(TenantSettings).where(TenantSettings.tenant_id == current_user.tenant_id)
    )
    s = result.scalar_one_or_none()
    extra = s.extra or {} if s else {}

    if not extra.get("ai_enabled"):
        raise HTTPException(status_code=400, detail="AI 功能未启用，请先在系统设置 → AI 配置中开启")
    extra = await resolve_ai_extra(db, current_user.tenant_id, extra)
    await consume_ai_quota(db, current_user.tenant_id)

    data_context = await _gather_sales_context(db, current_user.tenant_id, body.range)

    prompt = f"""{data_context}

═══ 用户问题 ═══
{body.question}

请基于以上数据回答问题，给出具体的数字支撑和可行建议。"""

    answer = await call_ai(prompt, extra, system_prompt=_SYSTEM_PROMPT, max_tokens=1500, timeout=90)

    days_label = {"7d": 7, "30d": 30, "90d": 90}.get(body.range, 30)
    return AnalyticsResponse(
        answer=answer,
        data_summary=f"基于近{days_label}天数据分析",
    )


# ══════════════════════════════════════════════════════════════════════
# AI 建单解析
# ══════════════════════════════════════════════════════════════════════

_ORDER_PARSE_SYSTEM = """你是电商后台订单录入助手。根据管理员的自然语言描述，提取订单信息并以 JSON 返回。
只返回 JSON，不要任何额外文字。字段说明：
- customer_keyword: 客户姓名/手机号/关键词（空字符串=未提及）
- items: 数组，每项含 product_keyword(商品名/关键词), qty(数量,整数), variant_keyword(规格描述,可空)
- coupon_code: 优惠码（空字符串=未提及）
- recv_name: 收件人姓名（空=未提及）
- recv_phone: 手机号（空=未提及）
- recv_province: 省份（空=未提及）
- recv_city: 城市（空=未提及）
- recv_district: 区县（空=未提及）
- recv_addr: 详细地址（空=未提及）
- pay_method: 支付方式（cash/alipay/wechat，默认cash）
- status: 订单状态（paid/pending，默认paid）
- note: 备注（空=无）"""


class OrderParseRequest(BaseModel):
    text: str


@router.post("/order-parse", summary="AI解析自然语言建单描述")
async def ai_order_parse(
    body: OrderParseRequest,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_admin_user),
):
    """AI 解析自然语言 → 匹配客户和商品 → 返回结构化建单数据"""
    if not body.text.strip():
        raise HTTPException(400, "描述不能为空")

    ts_r = await db.execute(
        select(TenantSettings).where(TenantSettings.tenant_id == current_user.tenant_id)
    )
    s = ts_r.scalar_one_or_none()
    extra = s.extra or {} if s else {}
    if not extra.get("ai_enabled"):
        raise HTTPException(400, "AI 功能未启用，请在系统设置中开启")

    tid = current_user.tenant_id
    extra = await resolve_ai_extra(db, tid, extra)
    await consume_ai_quota(db, tid)

    # 加载商品列表供 AI 参考（取前60件，按销量）
    prod_r = await db.execute(
        select(Product.id, Product.name, Product.sku, Product.base_price)
        .where(Product.tenant_id == tid, Product.status == "active")
        .order_by(Product.sales_count.desc())
        .limit(60)
    )
    product_list = [
        f"ID:{r.id} 【{r.name}】 SKU:{r.sku} ¥{float(r.base_price):.2f}"
        for r in prod_r
    ]
    product_hint = "\n".join(product_list) if product_list else "（暂无商品）"

    prompt = f"""可用商品列表：
{product_hint}

管理员描述：
{body.text}

请严格按 JSON 格式返回提取结果。"""

    raw = await call_ai(prompt, extra, system_prompt=_ORDER_PARSE_SYSTEM, max_tokens=800, timeout=60)

    # 解析 AI 返回的 JSON（兼容各种格式）
    text = raw.strip()

    # 1. 直接尝试
    parsed = None
    try:
        parsed = json.loads(text)
    except Exception:
        pass

    # 2. 提取 ```json ... ``` 或 ``` ... ```
    if parsed is None:
        m = re.search(r"```(?:json)?\s*(\{.*?\})\s*```", text, re.DOTALL)
        if m:
            try:
                parsed = json.loads(m.group(1))
            except Exception:
                pass

    # 3. 找第一个 { ... }（贪婪，取最外层对象）
    if parsed is None:
        m = re.search(r"\{.*\}", text, re.DOTALL)
        if m:
            try:
                parsed = json.loads(m.group(0))
            except Exception:
                pass

    if parsed is None:
        import logging
        logging.getLogger(__name__).error("AI order-parse raw: %s", raw[:500])
        raise HTTPException(500, "AI 返回格式有误，请重试或换一种描述方式")

    # ── 匹配客户 ─────────────────────────────────────────────────────
    matched_customers = []
    kw = (parsed.get("customer_keyword") or "").strip()
    if kw:
        cust_r = await db.execute(
            select(Customer.id, Customer.name, Customer.email)
            .where(
                Customer.tenant_id == tid,
                Customer.name.ilike(f"%{kw}%"),
            )
            .limit(5)
        )
        matched_customers = [
            {"id": r.id, "name": r.name, "email": r.email or ""}
            for r in cust_r
        ]

    # ── 匹配商品 ─────────────────────────────────────────────────────
    matched_items = []
    for it in (parsed.get("items") or []):
        pk = (it.get("product_keyword") or "").strip()
        vk = (it.get("variant_keyword") or "").strip()
        qty = max(1, int(it.get("qty") or 1))

        products_found = []
        if pk:
            pr = await db.execute(
                select(Product.id, Product.name, Product.sku, Product.base_price)
                .where(
                    Product.tenant_id == tid,
                    Product.status == "active",
                    Product.name.ilike(f"%{pk}%"),
                )
                .limit(5)
            )
            products_found = [
                {"id": r.id, "name": r.name, "sku": r.sku, "base_price": float(r.base_price)}
                for r in pr
            ]

        # 规格匹配
        variants_found = []
        if products_found and vk:
            p_ids = [p["id"] for p in products_found]
            vr = await db.execute(
                select(ProductVariant.id, ProductVariant.product_id,
                       ProductVariant.attributes, ProductVariant.price_modifier)
                .where(
                    ProductVariant.product_id.in_(p_ids),
                    ProductVariant.tenant_id == tid,
                    ProductVariant.is_active == 1,
                )
            )
            for v in vr:
                attr_str = str(v.attributes or "")
                if vk.lower() in attr_str.lower():
                    variants_found.append({
                        "id": v.id,
                        "product_id": v.product_id,
                        "attributes": v.attributes,
                        "price_modifier": float(v.price_modifier or 0),
                    })

        matched_items.append({
            "product_keyword": pk,
            "variant_keyword": vk,
            "qty": qty,
            "products": products_found,
            "variants": variants_found,
            # 自动选第一个
            "selected_product_id": products_found[0]["id"] if products_found else None,
            "selected_variant_id": variants_found[0]["id"] if variants_found else None,
        })

    return {
        "parsed":           parsed,
        "customers":        matched_customers,
        "items":            matched_items,
        "selected_customer": matched_customers[0] if matched_customers else None,
    }


# ── 订单导入模板下载 ─────────────────────────────────────────────────

_IMPORT_COLUMNS = [
    ("order_no",        "选填", "Optional",  "同一 order_no 的行合并为一个订单。留空则每行独立建单。",                                    "ORD-001"),
    ("customer_email",  "选填", "Optional",  "客户邮箱，用于匹配系统已有客户。找不到时在预览界面手动选择。",                              "customer@example.com"),
    ("customer_name",   "选填", "Optional",  "客户姓名，辅助识别。",                                                                     "张三"),
    ("sku",             "必填", "Required",  "商品SKU。先匹配规格SKU，找不到再匹配主商品SKU，需与系统商品一致。",                         "SKU-001"),
    ("qty",             "必填", "Required",  "购买数量，必须为正数。",                                                                   "2"),
    ("unit_price",      "选填", "Optional",  "覆盖单价。留空则使用系统售价。",                                                           "99.00"),
    ("recv_name",       "选填", "Optional",  "收件人姓名。留空在预览界面填写。",                                                         "张三"),
    ("recv_phone",      "选填", "Optional",  "收件人电话。",                                                                             "13800138000"),
    ("recv_province",   "选填", "Optional",  "省/州。",                                                                                 "广东省"),
    ("recv_city",       "选填", "Optional",  "城市。",                                                                                  "深圳市"),
    ("recv_addr",       "选填", "Optional",  "详细地址。",                                                                              "南山区科技园南路1号"),
    ("note",            "选填", "Optional",  "订单备注。",                                                                              "请尽快发货"),
    ("pay_method",      "选填", "Optional",  "支付方式：cash / alipay / wechat，默认 cash。",                                          "cash"),
    ("status",          "选填", "Optional",  "订单状态：paid / pending，默认 paid。",                                                  "paid"),
]


@router.get("/order-import-template", summary="下载订单导入模板")
async def download_order_import_template(
    current_user: User = Depends(get_admin_user),
):
    wb = openpyxl.Workbook()

    # ── Sheet 1: 数据区 ──────────────────────────────────────────────
    ws = wb.active
    ws.title = "Orders"

    req_fill = PatternFill("solid", fgColor="FFFFF2CC")   # 必填：黄色
    opt_fill = PatternFill("solid", fgColor="FFDDEEFF")   # 选填：淡蓝
    bold = Font(bold=True)
    center = Alignment(horizontal="center")

    for col_idx, (field, req_zh, _req_en, _desc, _example) in enumerate(_IMPORT_COLUMNS, start=1):
        cell = ws.cell(row=1, column=col_idx, value=field)
        cell.font = bold
        cell.fill = req_fill if req_zh == "必填" else opt_fill
        cell.alignment = center
        ws.column_dimensions[cell.column_letter].width = 20

    # 示例数据行
    ws.append(["ORD-001", "customer@example.com", "张三", "SKU-001",        2, "",    "张三", "13800138000", "广东省", "深圳市", "南山区科技园南路1号", "请尽快发货", "cash",   "paid"])
    ws.append(["ORD-001", "customer@example.com", "张三", "SKU-002",        1, 99.00, "",     "",            "",       "",       "",                  "",           "",        ""])
    ws.append(["",        "another@example.com",  "李四", "VARIANT-RED-XL", 3, "",    "李四", "13900139000", "北京市", "朝阳区", "建国路88号",         "",           "wechat",  "paid"])

    # ── Sheet 2: 使用说明 ───────────────────────────────────────────
    ws2 = wb.create_sheet("使用说明")
    ws2.column_dimensions["A"].width = 18
    ws2.column_dimensions["B"].width = 10
    ws2.column_dimensions["C"].width = 65
    ws2.column_dimensions["D"].width = 25

    ws2.append(["字段名", "是否必填", "说明", "示例值"])
    for cell in ws2[1]:
        cell.font = bold

    for field, req_zh, _req_en, desc, example in _IMPORT_COLUMNS:
        ws2.append([field, req_zh, desc, example])

    ws2.append([])
    ws2.append(["【注意事项】"])
    ws2["A" + str(ws2.max_row)].font = bold
    for note in [
        "1. 黄色背景列（sku、qty）为必填，其余列留空则在预览界面手动补充。",
        "2. 相同 order_no 的多行会合并为一个订单，每行代表一个商品。",
        "3. 若不填 order_no，相同 customer_email 的相邻行合并为一个订单。",
        "4. sku 先匹配规格 SKU，找不到再匹配主商品 SKU。",
        "5. 文件最多支持 500 行数据，支持 .xlsx 和 .csv 格式。",
    ]:
        ws2.append(["", "", note])

    buf = io.BytesIO()
    wb.save(buf)
    buf.seek(0)

    return StreamingResponse(
        buf,
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": "attachment; filename=order_import_template.xlsx"},
    )


# ── 订单表格文件解析 ─────────────────────────────────────────────────

_SHEET_COLUMNS = [col[0] for col in _IMPORT_COLUMNS]


def _parse_sheet_rows(content: bytes, filename: str) -> list[dict]:
    """将上传文件内容解析为行 dict 列表，key 为小写字段名。"""
    rows = []
    if filename.endswith(".csv"):
        try:
            text = content.decode("utf-8-sig")
        except UnicodeDecodeError:
            raise HTTPException(400, "CSV 文件编码有误，请使用 UTF-8 格式保存")
        reader = csv.DictReader(io.StringIO(text))
        for row in reader:
            rows.append({k.strip().lower(): (v.strip() if v else "") for k, v in row.items()})
    else:
        try:
            wb = openpyxl.load_workbook(io.BytesIO(content), read_only=True, data_only=True)
        except Exception:
            raise HTTPException(400, "文件格式无效，无法解析为 XLSX")
        ws = wb.active
        col_map: dict[int, str] = {}
        for r_idx, row in enumerate(ws.iter_rows(values_only=True)):
            if r_idx == 0:
                for c_idx, cell in enumerate(row):
                    if cell:
                        key = str(cell).strip().lower()
                        if key in _SHEET_COLUMNS:
                            col_map[c_idx] = key
                continue
            if not any(v for v in row if v is not None):
                continue
            normalized: dict[str, str] = {}
            for c_idx, field in col_map.items():
                val = row[c_idx] if c_idx < len(row) else None
                normalized[field] = str(val).strip() if val is not None else ""
            rows.append(normalized)
        wb.close()
    return rows


def _group_rows(rows: list[dict]) -> "OrderedDict[str, list[dict]]":
    """按 order_no 或 customer_email 分组，都没有则每行独立。"""
    has_order_no = any(r.get("order_no") for r in rows)
    has_email    = any(r.get("customer_email") for r in rows)
    groups: OrderedDict[str, list[dict]] = OrderedDict()
    for idx, row in enumerate(rows):
        if has_order_no and row.get("order_no"):
            key = f"no:{row['order_no']}"
        elif has_email and row.get("customer_email"):
            key = f"em:{row['customer_email']}"
        else:
            key = f"row:{idx}"
        groups.setdefault(key, []).append(row)
    return groups


@router.post("/import-from-sheet", summary="解析订单导入表格")
async def import_orders_from_sheet(
    file: UploadFile = File(...),
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(require_permission("orders.create")),
):
    content  = await file.read()
    filename = (file.filename or "").lower()

    if len(content) > 5 * 1024 * 1024:
        raise HTTPException(400, "文件不得超过 5 MB")

    if not (filename.endswith(".xlsx") or filename.endswith(".csv")):
        raise HTTPException(400, "仅支持 .xlsx 或 .csv 格式")

    rows = _parse_sheet_rows(content, filename)
    if not rows:
        raise HTTPException(400, "文件中没有数据行")

    first = rows[0]
    if "sku" not in first or "qty" not in first:
        raise HTTPException(400, "缺少必填列：sku 和 qty 列必须存在")

    if len(rows) > 500:
        raise HTTPException(400, "文件最多支持 500 行数据")

    tid      = current_user.tenant_id
    groups   = _group_rows(rows)
    orders   = []
    matched  = 0
    unmatched = 0
    warnings: list[str] = []

    for group_rows in groups.values():
        first_row = group_rows[0]

        # ── 匹配客户 ───────────────────────────────────────────────
        matched_customers: list[dict] = []
        selected_customer = None
        email = first_row.get("customer_email", "").strip()
        if email:
            cust_r = await db.execute(
                select(Customer.id, Customer.name, Customer.email)
                .where(Customer.tenant_id == tid, Customer.email == email)
                .limit(1)
            )
            row_c = cust_r.first()
            if row_c:
                selected_customer = {"id": row_c.id, "name": row_c.name, "email": row_c.email}
                matched_customers  = [selected_customer]

        # ── 匹配商品行 ─────────────────────────────────────────────
        matched_items: list[dict] = []
        for row in group_rows:
            sku         = row.get("sku", "").strip()
            qty_str     = row.get("qty", "1").strip()
            price_str   = row.get("unit_price", "").strip()

            try:
                qty = max(Decimal("0.01"), Decimal(qty_str))
            except Exception:
                warnings.append(f"SKU={sku!r} 的 qty 值无效，已跳过")
                continue

            unit_price = None
            if price_str:
                try:
                    unit_price = float(price_str)
                except Exception:
                    pass

            products_found:  list[dict] = []
            variants_found:  list[dict] = []
            sel_product_id   = None
            sel_variant_id   = None

            if sku:
                # 1) 先查规格 SKU
                var_r = await db.execute(
                    select(ProductVariant.id, ProductVariant.product_id,
                           ProductVariant.sku, ProductVariant.attributes,
                           ProductVariant.price_modifier)
                    .where(ProductVariant.tenant_id == tid,
                           ProductVariant.sku == sku,
                           ProductVariant.is_active == 1)
                    .limit(1)
                )
                var_row = var_r.first()

                if var_row:
                    prod_r = await db.execute(
                        select(Product.id, Product.name, Product.sku, Product.base_price)
                        .where(Product.id == var_row.product_id, Product.tenant_id == tid)
                    )
                    prod_row = prod_r.first()
                    if prod_row:
                        products_found = [{"id": prod_row.id, "name": prod_row.name,
                                           "sku": prod_row.sku,
                                           "base_price": float(prod_row.base_price)}]
                    sel_product_id = var_row.product_id
                    sel_variant_id = var_row.id
                    variants_found = [{"id": var_row.id, "product_id": var_row.product_id,
                                       "attributes": var_row.attributes,
                                       "price_modifier": float(var_row.price_modifier or 0)}]
                    matched += 1
                else:
                    # 2) 再查主商品 SKU
                    prod_r = await db.execute(
                        select(Product.id, Product.name, Product.sku, Product.base_price)
                        .where(Product.tenant_id == tid,
                               Product.sku == sku,
                               Product.status == "active")
                        .limit(1)
                    )
                    prod_row = prod_r.first()
                    if prod_row:
                        products_found = [{"id": prod_row.id, "name": prod_row.name,
                                           "sku": prod_row.sku,
                                           "base_price": float(prod_row.base_price)}]
                        sel_product_id = prod_row.id
                        matched += 1
                    else:
                        unmatched += 1
                        warnings.append(f"SKU={sku!r} 未找到匹配商品")

            matched_items.append({
                "product_keyword":    sku,
                "variant_keyword":    "",
                "qty":                float(qty),
                "unit_price":         unit_price,
                "products":           products_found,
                "variants":           variants_found,
                "selected_product_id": sel_product_id,
                "selected_variant_id": sel_variant_id,
                "_searching":         False,
                "product_candidates": products_found,
                "variant_candidates": variants_found,
            })

        parsed = {
            "recv_name":     first_row.get("recv_name",     ""),
            "recv_phone":    first_row.get("recv_phone",    ""),
            "recv_province": first_row.get("recv_province", ""),
            "recv_city":     first_row.get("recv_city",     ""),
            "recv_district": "",
            "recv_addr":     first_row.get("recv_addr",     ""),
            "note":          first_row.get("note",          ""),
            "pay_method":    first_row.get("pay_method",    "cash") or "cash",
            "status":        first_row.get("status",        "paid") or "paid",
            "coupon_code":   "",
        }

        orders.append({
            "parsed":            parsed,
            "customers":         matched_customers,
            "selected_customer": selected_customer,
            "items":             matched_items,
        })

    return {
        "orders":          orders,
        "total":           len(orders),
        "matched_items":   matched,
        "unmatched_items": unmatched,
        "warnings":        warnings,
    }