"""商品管理路由"""
import re
import time
import json as _json
from datetime import date, timedelta
from decimal import Decimal
from typing import Literal, Optional
from fastapi import APIRouter, Depends, HTTPException, Query
from pydantic import BaseModel
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func, or_, insert, delete, case, and_, update
from app.plugins.pos_sync.revision import bump_revision

from app.api.deps import get_db, get_admin_user
from app.core.models.user import User
from app.core.models.tenant_settings import TenantSettings
from app.core.ai_utils import call_ai, resolve_ai_extra
from app.core.services.ai_quota import consume_ai_quota
from app.core.models.brand import Brand
from app.core.models.product import Product, ProductImage, ProductVariant, ProductTierPrice, product_categories
from app.core.models.stock_status import StockStatus
from app.core.services.inventory import _auto_switch_stock_status
from app.core.services.expiry import effective_expiry_date, expiry_status as calc_expiry_status
from app.core.services.product_query import (
    build_product_filter_conditions, effective_expiry_expr, ALLOWED_EXPIRY_STATUSES,
)
from app.core.models.category import Category
from app.core.models.member import MemberLevel
from app.schemas.common import PageResult
from app.schemas.product import (
    ProductCreate, ProductUpdate, ProductOut, ProductListOut,
    ProductVariantOut, TierPriceOut, StockStatusBrief,
)
from app.services.audit import log_audit

router = APIRouter(prefix="/products", tags=["商品管理"])

_ALLOWED_EXPIRY_STATUSES = ALLOWED_EXPIRY_STATUSES  # 复用共享构造器，保持向后兼容


async def _expiry_warning_days(db: AsyncSession, tenant_id: int) -> int:
    r = await db.execute(select(TenantSettings.extra).where(TenantSettings.tenant_id == tenant_id))
    extra = r.scalar_one_or_none() or {}
    try:
        return max(0, int(extra.get("expiry_warning_days", 30)))
    except (TypeError, ValueError):
        return 30


_effective_expiry_expr = effective_expiry_expr  # 复用共享构造器


def _strip_html(text: str, max_chars: int = 600) -> str:
    """Remove HTML tags and truncate to max_chars plain-text characters."""
    if not text:
        return ""
    # Remove style/script blocks entirely
    text = re.sub(r"<(style|script)[^>]*>.*?</(style|script)>", " ", text, flags=re.DOTALL | re.IGNORECASE)
    # Remove remaining tags
    text = re.sub(r"<[^>]+>", " ", text)
    # Unescape common HTML entities
    for esc, char in [("&nbsp;", " "), ("&amp;", "&"), ("&lt;", "<"), ("&gt;", ">"), ("&quot;", '"'), ("&#39;", "'")]:
        text = text.replace(esc, char)
    text = " ".join(text.split())
    if len(text) > max_chars:
        return text[:max_chars] + "…"
    return text


_SLUG_STOP_WORDS = {"item", "new", "product", "sale", "sku"}


def _slugify(value: str | None) -> str:
    slug = re.sub(r"[^a-z0-9]+", "-", (value or "").lower()).strip("-")
    return re.sub(r"-+", "-", slug)[:120].strip("-")


def _is_meaningful_slug(slug: str) -> bool:
    return (
        bool(slug)
        and len(slug) >= 5
        and not slug.isdigit()
        and slug not in _SLUG_STOP_WORDS
        and re.search(r"[a-z]{3,}", slug) is not None
    )


async def _first_category_slug(db: AsyncSession, tenant_id: int, categories: list[str] | None) -> str:
    if not categories:
        return ""
    r = await db.execute(
        select(Category.slug).where(
            Category.tenant_id == tenant_id,
            Category.name == categories[0],
        ).limit(1)
    )
    return _slugify(r.scalar_one_or_none())


async def _unique_product_slug(db: AsyncSession, tenant_id: int, base: str, exclude_id: int | None = None) -> str:
    slug = base or f"product-{int(time.time())}"
    for i in range(0, 100):
        candidate = slug if i == 0 else f"{slug}-{i + 1}"
        q = select(Product.id).where(Product.tenant_id == tenant_id, Product.slug == candidate)
        if exclude_id is not None:
            q = q.where(Product.id != exclude_id)
        if (await db.execute(q)).scalar_one_or_none() is None:
            return candidate
    return f"{slug}-{int(time.time())}"


async def _build_product_slug(db: AsyncSession, tenant_id: int, body: ProductCreate | ProductUpdate, exclude_id: int | None = None) -> str:
    category_slug = await _first_category_slug(db, tenant_id, getattr(body, "categories", None))
    candidates = [
        body.slug,
        body.name_en,
        body.name,
        f"{category_slug}-{body.sku}" if category_slug and body.sku else "",
        body.sku,
    ]
    for value in candidates:
        slug = _slugify(value)
        if _is_meaningful_slug(slug):
            return await _unique_product_slug(db, tenant_id, slug, exclude_id)
    return await _unique_product_slug(db, tenant_id, f"product-{int(time.time())}", exclude_id)


async def _load_tier_prices(
    db: AsyncSession,
    product_id: int,
    variant_id: int | None = None,
) -> list[TierPriceOut]:
    """加载商品或规格的会员等级价格，附带等级名称和折扣率。"""
    r = await db.execute(
        select(ProductTierPrice, MemberLevel.name, MemberLevel.discount_rate)
        .join(MemberLevel, ProductTierPrice.member_level_id == MemberLevel.id)
        .where(
            ProductTierPrice.product_id == product_id,
            ProductTierPrice.variant_id == variant_id,
        )
    )
    return [
        TierPriceOut(
            member_level_id=row[0].member_level_id,
            price=row[0].price,
            level_name=row[1],
            discount_rate=row[2],
        )
        for row in r.all()
    ]


async def _sync_tier_prices(
    db: AsyncSession,
    product_id: int,
    tenant_id: int,
    tier_prices: list,
    variant_id: int | None = None,
) -> None:
    """同步商品/规格的会员等级价格（全量替换）。"""
    await db.execute(
        delete(ProductTierPrice).where(
            ProductTierPrice.product_id == product_id,
            ProductTierPrice.variant_id == variant_id,
        )
    )
    for tp in tier_prices:
        level_id = tp.member_level_id if hasattr(tp, "member_level_id") else tp["member_level_id"]
        price = tp.price if hasattr(tp, "price") else tp["price"]
        db.add(ProductTierPrice(
            tenant_id=tenant_id,
            product_id=product_id,
            variant_id=variant_id,
            member_level_id=level_id,
            price=price,
        ))


def _variant_out(
    v: ProductVariant,
    base_price: Decimal | None = None,
    tier_prices: list[TierPriceOut] | None = None,
    product_expiry_date = None,
    warning_days: int = 30,
) -> ProductVariantOut:
    weight_g = int((v.weight or 0) * 1000) if v.weight is not None else None
    independent_price = None
    if base_price is not None:
        independent_price = base_price + (v.price_modifier or Decimal("0"))
    effective_date = v.expiry_date or product_expiry_date
    return ProductVariantOut(
        id=v.id,
        product_id=v.product_id,
        tenant_id=v.tenant_id,
        sku=v.sku,
        barcode=v.barcode,
        price_modifier=v.price_modifier,
        independent_price=independent_price,
        stock_qty=v.stock_qty,
        reserved_qty=v.reserved_qty,
        image_url=v.image_url,
        weight_grams=weight_g,
        length=v.length,
        width=v.width,
        height=v.height,
        sort_order=v.sort_order,
        attributes=v.attributes or {},
        is_active=bool(v.is_active),
        is_default=bool(v.is_default),
        expiry_date=effective_date,
        expiry_status=calc_expiry_status(effective_date, warning_days=warning_days),
        tier_prices=tier_prices or [],
    )


async def _get_product_categories(db: AsyncSession, product_id: int) -> list[str]:
    r = await db.execute(
        select(Category.name)
        .join(product_categories, Category.id == product_categories.c.category_id)
        .where(product_categories.c.product_id == product_id)
    )
    return [row[0] for row in r.all()]


def _category_lookup_condition(category: str | int):
    return Category.id == category if isinstance(category, int) else Category.name == category

async def _sync_product_categories(db: AsyncSession, product_id: int, category_names: list[str | int], tenant_id: int) -> None:
    await db.execute(delete(product_categories).where(product_categories.c.product_id == product_id))
    for cat_name in category_names:
        cat_r = await db.execute(
            select(Category.id).where(_category_lookup_condition(cat_name), Category.tenant_id == tenant_id).limit(1)
        )
        cat_id = cat_r.scalar_one_or_none()
        if cat_id:
            await db.execute(insert(product_categories).values(product_id=product_id, category_id=cat_id))

    from datetime import datetime
    await db.execute(update(Product).where(Product.id == product_id).values(updated_at=datetime.utcnow()))


async def _product_out(db: AsyncSession, p: Product, cats: list[str] | None = None, brand_name: str | None = None, brand_slug: str | None = None, stock_status_brief: StockStatusBrief | None = None) -> ProductOut:
    if cats is None:
        cats = await _get_product_categories(db, p.id)
    images_r = await db.execute(
        select(ProductImage).where(ProductImage.product_id == p.id).order_by(ProductImage.sort_order.asc(), ProductImage.id.asc())
    )
    images = [img.url for img in images_r.scalars().all()]
    variants_r = await db.execute(
        select(ProductVariant)
        .where(ProductVariant.product_id == p.id, ProductVariant.tenant_id == p.tenant_id)
        .order_by(ProductVariant.sort_order.asc(), ProductVariant.id.asc())
    )
    variant_objs = variants_r.scalars().all()

    # 一次查询加载所有 tier_prices（商品级 + 所有规格级）
    all_tp_r = await db.execute(
        select(ProductTierPrice, MemberLevel.name, MemberLevel.discount_rate)
        .join(MemberLevel, ProductTierPrice.member_level_id == MemberLevel.id)
        .where(ProductTierPrice.product_id == p.id)
    )
    product_tps: list[TierPriceOut] = []
    variant_tps: dict[int, list[TierPriceOut]] = {}
    for row in all_tp_r.all():
        tp_obj, lvl_name, disc_rate = row[0], row[1], row[2]
        entry = TierPriceOut(
            member_level_id=tp_obj.member_level_id,
            price=tp_obj.price,
            level_name=lvl_name,
            discount_rate=disc_rate,
        )
        if tp_obj.variant_id is None:
            product_tps.append(entry)
        else:
            variant_tps.setdefault(tp_obj.variant_id, []).append(entry)

    if stock_status_brief is None and p.stock_status_id:
        ss_r = await db.execute(select(StockStatus).where(StockStatus.id == p.stock_status_id))
        ss = ss_r.scalar_one_or_none()
        if ss:
            stock_status_brief = StockStatusBrief.model_validate(ss)

    warning_days = await _expiry_warning_days(db, p.tenant_id)
    product_expiry = effective_expiry_date(p.expiry_date, [v.expiry_date for v in variant_objs])
    variants = [_variant_out(v, p.base_price, variant_tps.get(v.id, []), p.expiry_date, warning_days) for v in variant_objs]

    # 进销存成本只读字段
    inv_cost_managed = False
    inv_current_unit_cost = None
    inv_last_purchase_unit_cost = None
    from app.core.services.inventory import _takeover_state
    if await _takeover_state(db, p.tenant_id) is not None:
        inv_cost_managed = True
        from app.plugins.inventory.models import CostLayer, InventoryOperation
        # 加权平均单位成本：剩余成本层
        cost_r = await db.execute(
            select(
                func.sum((CostLayer.qty_in - CostLayer.qty_consumed) * CostLayer.unit_cost
                         + (CostLayer.additional_cost - CostLayer.additional_cost_consumed)),
                func.sum(CostLayer.qty_in - CostLayer.qty_consumed),
            ).where(
                CostLayer.tenant_id == p.tenant_id,
                CostLayer.product_id == p.id,
                CostLayer.qty_in > CostLayer.qty_consumed,
            )
        )
        total_value, total_qty = cost_r.one()
        if total_qty and total_qty > 0:
            inv_current_unit_cost = (total_value / total_qty).quantize(Decimal("0.0001"))
        # 最近采购价：最近一条收货完成的成本层 unit_cost（排除期初导入、退货等）
        last_layer_r = await db.execute(
            select(CostLayer.unit_cost)
            .join(InventoryOperation, CostLayer.source_operation_id == InventoryOperation.id)
            .where(
                CostLayer.tenant_id == p.tenant_id,
                CostLayer.product_id == p.id,
                InventoryOperation.operation_type == "receipt",
                InventoryOperation.state == "done",
            )
            .order_by(CostLayer.id.desc())
            .limit(1)
        )
        last_cost = last_layer_r.scalar_one_or_none()
        if last_cost is not None:
            inv_last_purchase_unit_cost = last_cost

    return ProductOut(
        id=p.id, tenant_id=p.tenant_id, name=p.name, sku=p.sku,
        categories=cats,
        brand_id=p.brand_id,
        brand=brand_name,
        brand_slug=brand_slug,
        description=p.description, base_price=p.base_price,
        market_price=p.market_price, member_price=p.member_price,
        cost_price=p.cost_price, stock_qty=p.stock_qty,
        low_stock_threshold=p.low_stock_threshold or 5,
        allow_oversell=bool(p.allow_oversell),
        status=p.status, weight_grams=int((p.weight or 0) * 1000),
        length=p.length, width=p.width, height=p.height,
        slug=p.slug, meta_title=p.meta_title, meta_description=p.meta_description,
        seo_keywords=p.seo_keywords,
        cover_url=p.cover_url, images=images, variants=variants,
        tier_prices=product_tps,
        sales_count=p.sales_count, created_at=p.created_at,
        shelf_life=p.shelf_life,
        shelf_life_en=p.shelf_life_en,
        expiry_date=product_expiry,
        expiry_status=calc_expiry_status(product_expiry, warning_days=warning_days),
        ai_description=p.ai_description,
        name_en=p.name_en,
        description_en=p.description_en,
        ai_description_en=p.ai_description_en,
        meta_title_en=p.meta_title_en,
        meta_description_en=p.meta_description_en,
        seo_keywords_en=p.seo_keywords_en,
        stock_status=stock_status_brief,
        stock_status_id=p.stock_status_id,
        tax_class_id=p.tax_class_id,
        inventory_cost_managed=inv_cost_managed,
        inventory_current_unit_cost=inv_current_unit_cost,
        inventory_last_purchase_unit_cost=inv_last_purchase_unit_cost,
    )


async def _apply_variants(db: AsyncSession, product: Product, variants: list | None, tenant_id: int) -> None:
    if variants is None:
        return
    existing_r = await db.execute(
        select(ProductVariant).where(ProductVariant.product_id == product.id, ProductVariant.tenant_id == tenant_id)
    )
    existing = {v.id: v for v in existing_r.scalars().all()}
    seen: set[int] = set()
    from app.core.services.inventory import _takeover_state
    from app.plugins.inventory.guard import strip_stock_fields
    _taken_over = await _takeover_state(db, tenant_id) is not None
    for idx, body in enumerate(variants):
        data = body.model_dump(exclude_unset=True)
        data = strip_stock_fields(data, taken_over=_taken_over)
        variant_id = data.pop("id", None)
        variant_tier_prices = data.pop("tier_prices", [])
        weight_grams = data.pop("weight_grams", None)
        if weight_grams is not None:
            data["weight"] = Decimal(str(weight_grams / 1000))
        independent_price = data.pop("independent_price", None)
        if independent_price is not None and "price_modifier" not in data:
            base = product.base_price or Decimal("0")
            data["price_modifier"] = Decimal(str(independent_price)) - base
        data["is_active"] = 1 if data.get("is_active", True) else 0
        data["is_default"] = 1 if data.get("is_default", False) else 0
        data.setdefault("sort_order", idx)
        if variant_id:
            variant = existing.get(variant_id)
            if variant is None:
                raise HTTPException(status_code=404, detail=f"SKU {variant_id} 不存在")
            seen.add(variant_id)
            for key, value in data.items():
                setattr(variant, key, value)
        else:
            variant = ProductVariant(
                tenant_id=tenant_id,
                product_id=product.id,
                **data,
            )
            db.add(variant)
            await db.flush()  # 获取 variant.id

        # 同步该规格的会员等级价格
        if variant_tier_prices is not None:
            await db.flush()
            await _sync_tier_prices(db, product.id, tenant_id, variant_tier_prices, variant_id=variant.id)
            # 第一个变体的会员价同步到商品级（variant_id=NULL），供列表页使用
            if idx == 0:
                await _sync_tier_prices(db, product.id, tenant_id, variant_tier_prices, variant_id=None)

    deleted_variant = False
    for vid, variant in existing.items():
        if vid not in seen:
            await db.delete(variant)
            deleted_variant = True
    if deleted_variant:
        await bump_revision(db, tenant_id)


@router.get("", response_model=PageResult[ProductListOut], summary="商品列表")
async def list_products(
    page: int = Query(1, ge=1),
    page_size: int = Query(10, ge=1, le=100),
    keyword: Optional[str] = None,
    category: Optional[str] = None,
    category_id: Optional[int] = None,
    status: Optional[str] = None,
    expiry_status: Optional[str] = None,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_admin_user),
):
    tid = current_user.tenant_id
    expiry_status = expiry_status or None
    if expiry_status and expiry_status not in _ALLOWED_EXPIRY_STATUSES:
        raise HTTPException(status_code=422, detail="Invalid expiry_status")
    warning_days = await _expiry_warning_days(db, tid)
    variant_expiry = (
        select(
            ProductVariant.product_id.label("product_id"),
            func.min(ProductVariant.expiry_date).label("variant_expiry_date"),
        )
        .where(ProductVariant.tenant_id == tid)
        .group_by(ProductVariant.product_id)
        .subquery()
    )
    effective_expiry = _effective_expiry_expr(Product.expiry_date, variant_expiry.c.variant_expiry_date).label("effective_expiry_date")
    q = (
        select(Product, Brand.name.label("brand_name"), Brand.slug.label("brand_slug"), effective_expiry)
        .select_from(Product)
        .outerjoin(Brand, Product.brand_id == Brand.id)
        .outerjoin(variant_expiry, variant_expiry.c.product_id == Product.id)
        .where(Product.tenant_id == tid)
    )
    conds = await build_product_filter_conditions(
        db, tid, keyword=keyword, category=category, category_id=category_id, status=status,
        expiry_status=expiry_status, effective_expiry=effective_expiry, warning_days=warning_days,
    )
    if conds:
        q = q.where(*conds)

    total_r = await db.execute(select(func.count()).select_from(q.subquery()))
    total = total_r.scalar() or 0

    q = q.order_by(Product.created_at.desc()).offset((page - 1) * page_size).limit(page_size)
    result = await db.execute(q)
    rows = result.all()

    if not rows:
        return PageResult(items=[], total=total, page=page, page_size=page_size)

    product_ids = [row[0].id for row in rows]
    cats_r = await db.execute(
        select(product_categories.c.product_id, Category.name)
        .join(Category, product_categories.c.category_id == Category.id)
        .where(product_categories.c.product_id.in_(product_ids))
    )
    product_cat_map: dict[int, list[str]] = {}
    for pid, cname in cats_r.all():
        product_cat_map.setdefault(pid, []).append(cname)

    ss_ids = {row[0].stock_status_id for row in rows if row[0].stock_status_id}
    ss_map: dict[int, StockStatusBrief] = {}
    if ss_ids:
        ss_r = await db.execute(select(StockStatus).where(StockStatus.id.in_(ss_ids)))
        for ss in ss_r.scalars().all():
            ss_map[ss.id] = StockStatusBrief.model_validate(ss)

    items = []
    for row in rows:
        p = row[0]
        brand_name = row[1]
        brand_slug = row[2]
        product_expiry = row[3]
        items.append(ProductListOut(
            id=p.id, name=p.name, sku=p.sku,
            categories=product_cat_map.get(p.id, []),
            brand_id=p.brand_id, brand=brand_name, brand_slug=brand_slug,
            base_price=p.base_price, market_price=p.market_price, member_price=p.member_price,
            stock_qty=p.stock_qty,
            status=p.status, cover_url=p.cover_url,
            slug=p.slug,
            sales_count=p.sales_count, created_at=p.created_at,
            expiry_date=product_expiry,
            expiry_status=calc_expiry_status(product_expiry, warning_days=warning_days),
            stock_status=ss_map.get(p.stock_status_id),
        ))

    return PageResult(items=items, total=total, page=page, page_size=page_size)


class ProductTranslateRequest(BaseModel):
    source_locale: Literal["zh", "en"] = "zh"
    name: str = ""
    description: str = ""
    ai_description: str = ""
    meta_title: str = ""
    meta_description: str = ""
    seo_keywords: str = ""
    name_en: str = ""
    description_en: str = ""
    ai_description_en: str = ""
    meta_title_en: str = ""
    meta_description_en: str = ""
    seo_keywords_en: str = ""


class ProductTranslateResponse(BaseModel):
    name: str = ""
    description: str = ""
    ai_description: str = ""
    meta_title: str = ""
    meta_description: str = ""
    seo_keywords: str = ""
    name_en: str = ""
    description_en: str = ""
    ai_description_en: str = ""
    meta_title_en: str = ""
    meta_description_en: str = ""
    seo_keywords_en: str = ""


@router.post("/translate", response_model=ProductTranslateResponse, summary="AI翻译商品字段")
async def translate_product(
    body: ProductTranslateRequest,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_admin_user),
):
    tid = current_user.tenant_id
    s_r = await db.execute(select(TenantSettings).where(TenantSettings.tenant_id == tid))
    s = s_r.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, tid, extra)
    await consume_ai_quota(db, tid)

    if body.source_locale == "en":
        source = {key: getattr(body, f"{key}_en") for key in ("name", "description", "ai_description", "meta_title", "meta_description", "seo_keywords")}
        target_locale, target_language, target_suffix = "zh", "Chinese", ""
    else:
        source = {key: getattr(body, key) for key in ("name", "description", "ai_description", "meta_title", "meta_description", "seo_keywords")}
        target_locale, target_language, target_suffix = "en", "English", "_en"
    desc_text = _strip_html(source["description"], max_chars=600)
    output_keys = [f"{key}{target_suffix}" for key in source]
    system_prompt = (
        f"You are a professional e-commerce translator. Translate all provided content into natural {target_language}. "
        "For description, write fluent marketing copy (wrap in <p> tag). "
        f"For SEO fields, produce search-engine-optimized {target_language} copy. "
        f"Return ONLY a valid JSON object with keys: {', '.join(output_keys)}. "
        "If a field is empty, return an empty string for it."
    )
    user_prompt = (
        f"Translate this product information from {body.source_locale} to {target_locale}:\n"
        f"name: {(source['name'] or '')[:200]}\n"
        f"description: {desc_text}\n"
        f"ai_description: {(source['ai_description'] or '')[:600]}\n"
        f"meta_title: {(source['meta_title'] or '')[:160]}\n"
        f"meta_description: {(source['meta_description'] or '')[:320]}\n"
        f"seo_keywords: {(source['seo_keywords'] or '')[:300]}\n\n"
        f"Return JSON only, no explanation."
    )

    raw = await call_ai(user_prompt, extra, system_prompt=system_prompt, max_tokens=2048, timeout=120)
    # 找到第一个 { 的位置，用 raw_decode 提取第一个完整 JSON 对象
    start = raw.find("{")
    if start == -1:
        raise HTTPException(status_code=502, detail="AI 返回格式异常，请重试")
    try:
        data, _ = _json.JSONDecoder().raw_decode(raw, start)
    except _json.JSONDecodeError:
        raise HTTPException(status_code=502, detail="AI 返回 JSON 解析失败，请重试")

    return ProductTranslateResponse(**{key: data.get(f"{key}{target_suffix}", "") for key in source}) if body.source_locale == "en" else ProductTranslateResponse(**{f"{key}_en": data.get(f"{key}_en", "") for key in source})


@router.get("/{product_id}", response_model=ProductOut, summary="商品详情")
async def get_product(
    product_id: int,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_admin_user),
):
    r = await db.execute(
        select(Product, Brand.name.label("brand_name"), Brand.slug.label("brand_slug"))
        .select_from(Product)
        .outerjoin(Brand, Product.brand_id == Brand.id)
        .where(Product.id == product_id, Product.tenant_id == current_user.tenant_id)
    )
    row = r.first()
    if not row:
        raise HTTPException(404, "商品不存在")
    p, brand_name, brand_slug = row[0], row[1], row[2]
    cats = await _get_product_categories(db, p.id)
    return await _product_out(db, p, cats, brand_name, brand_slug)


@router.post("", response_model=ProductOut, status_code=201, summary="创建商品")
async def create_product(
    body: ProductCreate,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_admin_user),
):
    slug = await _build_product_slug(db, current_user.tenant_id, body)
    data = body.model_dump(exclude={"slug", "categories", "variants", "tier_prices", "name_en", "description_en", "ai_description_en", "meta_title_en", "meta_description_en", "seo_keywords_en"})
    # 进销存接管后，新商品也不能通过目录回导直接写入库存投影。
    from app.core.services.inventory import _takeover_state
    from app.plugins.inventory.guard import strip_stock_fields
    data = strip_stock_fields(data, taken_over=await _takeover_state(db, current_user.tenant_id) is not None)
    if not data.get("stock_status_id"):
        default_ss = await db.execute(
            select(StockStatus.id).where(
                StockStatus.tenant_id == current_user.tenant_id,
                StockStatus.is_default == 1,
            )
        )
        default_id = default_ss.scalar_one_or_none()
        if default_id:
            data["stock_status_id"] = default_id
    wg = data.pop("weight_grams", None) if "weight_grams" in data else None
    if wg:
        data["weight"] = Decimal(str(wg / 1000))
    p = Product(
        tenant_id=current_user.tenant_id,
        slug=slug,
        **data,
    )
    db.add(p)
    p.name_en = body.name_en or None
    p.description_en = body.description_en or None
    p.ai_description_en = body.ai_description_en or None
    p.meta_title_en = body.meta_title_en or None
    p.meta_description_en = body.meta_description_en or None
    p.seo_keywords_en = body.seo_keywords_en or None
    await db.flush()
    await _sync_product_categories(db, p.id, body.categories, current_user.tenant_id)
    await _sync_tier_prices(db, p.id, current_user.tenant_id, body.tier_prices, variant_id=None)
    await _apply_variants(db, p, body.variants, current_user.tenant_id)
    await db.commit()
    await db.refresh(p)

    # Auto-create template for the shared product library
    try:
        await _auto_create_or_link_template(db, p, current_user.tenant_id)
        await db.commit()
    except Exception:
        pass  # template creation is best-effort, don't fail product creation

    await log_audit(
        db=db,
        tenant_id=current_user.tenant_id,
        action="products.create",
        actor_type="admin",
        actor_id=current_user.id,
        actor_name=getattr(current_user, "name", None),
        target_type="products",
        target_id=p.id,
        target_name=p.name,
    )

    return await get_product(p.id, db, current_user)


@router.put("/{product_id}", response_model=ProductOut, summary="更新商品")
async def update_product(
    product_id: int,
    body: ProductUpdate,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_admin_user),
):
    r = await db.execute(
        select(Product).where(
            Product.id == product_id,
            Product.tenant_id == current_user.tenant_id,
        )
    )
    p = r.scalar_one_or_none()
    if not p:
        raise HTTPException(404, "商品不存在")

    data = body.model_dump(exclude_unset=True, exclude={"categories", "variants", "tier_prices", "name_en", "description_en", "ai_description_en", "meta_title_en", "meta_description_en", "seo_keywords_en"})

    if "slug" in data:
        if not data["slug"]:
            data.pop("slug", None)
        elif data["slug"] != p.slug:
            data["slug"] = await _build_product_slug(db, current_user.tenant_id, body, exclude_id=product_id)

    wg = data.pop("weight_grams", None) if "weight_grams" in data else None
    if wg is not None:
        data["weight"] = Decimal(str(wg / 1000))

    # 进销存接管后 stock_qty 只读，库存改动只能走库存纠正单
    from app.core.services.inventory import _takeover_state
    from app.plugins.inventory.guard import strip_stock_fields
    data = strip_stock_fields(data, taken_over=await _takeover_state(db, current_user.tenant_id) is not None)

    for k, v in data.items():
        setattr(p, k, v)

    update_data = body.model_dump(exclude_unset=True)
    if "categories" in update_data:
        await _sync_product_categories(db, p.id, update_data["categories"], current_user.tenant_id)

    if body.tier_prices is not None:
        await _sync_tier_prices(db, p.id, current_user.tenant_id, body.tier_prices, variant_id=None)

    if body.variants is not None:
        await _apply_variants(db, p, body.variants, current_user.tenant_id)

    if body.ai_description is not None:
        p.ai_description = body.ai_description
    if body.name_en is not None:
        p.name_en = body.name_en or None
    if body.description_en is not None:
        p.description_en = body.description_en or None
    if body.ai_description_en is not None:
        p.ai_description_en = body.ai_description_en or None
    if body.meta_title_en is not None:
        p.meta_title_en = body.meta_title_en or None
    if body.meta_description_en is not None:
        p.meta_description_en = body.meta_description_en or None
    if body.seo_keywords_en is not None:
        p.seo_keywords_en = body.seo_keywords_en or None

    if "stock_qty" in data and "stock_status_id" not in data:
        await _auto_switch_stock_status(db, p, p.stock_qty)

    await db.commit()
    await db.refresh(p)

    await log_audit(
        db=db,
        tenant_id=current_user.tenant_id,
        action="products.update",
        actor_type="admin",
        actor_id=current_user.id,
        actor_name=getattr(current_user, "name", None),
        target_type="products",
        target_id=p.id,
        target_name=p.name,
    )

    return await get_product(p.id, db, current_user)


@router.delete("/{product_id}", summary="删除商品")
async def delete_product(
    product_id: int,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_admin_user),
):
    r = await db.execute(
        select(Product).where(
            Product.id == product_id,
            Product.tenant_id == current_user.tenant_id,
        )
    )
    p = r.scalar_one_or_none()
    if not p:
        raise HTTPException(404, "商品不存在")

    p_name = p.name
    await db.delete(p)
    await bump_revision(db, current_user.tenant_id)
    await db.commit()

    await log_audit(
        db=db,
        tenant_id=current_user.tenant_id,
        action="products.delete",
        actor_type="admin",
        actor_id=current_user.id,
        actor_name=getattr(current_user, "name", None),
        target_type="products",
        target_id=product_id,
        target_name=p_name,
    )

    return {"ok": True}


# ══════════════════════════════════════════════════════════════
#  Auto-create / link template on product publish
# ══════════════════════════════════════════════════════════════

async def _auto_create_or_link_template(
    db: AsyncSession, product: Product, tenant_id: int,
):
    """When a tenant publishes a product, auto-create a pending template or link to existing by SKU."""
    from app.core.models.product_template import (
        ProductTemplate, TemplateImage, TemplateVariant, TemplateSource,
    )

    if not product.sku:
        return

    existing = await db.execute(
        select(ProductTemplate).where(ProductTemplate.sku == product.sku)
    )
    tpl = existing.scalar_one_or_none()

    if tpl:
        already_linked = await db.execute(
            select(TemplateSource.id).where(
                TemplateSource.template_id == tpl.id,
                TemplateSource.tenant_id == tenant_id,
                TemplateSource.product_id == product.id,
            )
        )
        if not already_linked.scalar_one_or_none():
            db.add(TemplateSource(
                template_id=tpl.id, tenant_id=tenant_id, product_id=product.id,
            ))
        return

    tpl = ProductTemplate(
        sku=product.sku, name=product.name, name_en=product.name_en,
        description=product.description, description_en=product.description_en,
        ai_description=product.ai_description, ai_description_en=product.ai_description_en,
        base_price=product.base_price, cost_price=product.cost_price,
        market_price=product.market_price,
        weight=product.weight, length=product.length,
        width=product.width, height=product.height,
        extra_attributes=product.extra_attributes,
        meta_title=product.meta_title, meta_title_en=product.meta_title_en,
        meta_description=product.meta_description, meta_description_en=product.meta_description_en,
        seo_keywords=product.seo_keywords, seo_keywords_en=product.seo_keywords_en,
        status="pending",
    )
    db.add(tpl)
    await db.flush()

    images = await db.execute(
        select(ProductImage).where(ProductImage.product_id == product.id)
    )
    for img in images.scalars().all():
        db.add(TemplateImage(
            template_id=tpl.id, url=img.url, alt_text=img.alt_text,
            is_primary=img.is_primary, sort_order=img.sort_order,
        ))

    variants = await db.execute(
        select(ProductVariant).where(ProductVariant.product_id == product.id)
    )
    for var in variants.scalars().all():
        db.add(TemplateVariant(
            template_id=tpl.id, sku=var.sku, attributes=var.attributes,
            price_modifier=var.price_modifier, independent_price=var.independent_price,
            weight=var.weight, is_default=var.is_default,
        ))

    db.add(TemplateSource(
        template_id=tpl.id, tenant_id=tenant_id, product_id=product.id,
    ))