"""共享商品筛选构造器。

管理端商品列表（products.py:list_products）与 catalog_transfer 导出复用同一套
过滤语义：关键词、分类（含后代）、状态、效期状态。抽到这里避免两处逻辑随后续
筛选扩展而漂移。此模块只构造查询条件，不涉及排序、分页与响应结构。
"""
from datetime import date, timedelta

from sqlalchemy import select, or_, and_, case, func
from sqlalchemy.ext.asyncio import AsyncSession

from app.core.models.product import Product, product_categories
from app.core.models.category import Category

ALLOWED_EXPIRY_STATUSES = {"none", "expired", "expiring", "normal"}


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),
    )


async def collect_category_descendants(
    db: AsyncSession, tenant_id: int, category_name: str | None = None, *, category_id: int | None = None,
) -> set[int]:
    """选父分类时连带其全部子孙分类（BFS 收集后代）。

    传 category_id 时按 id 精确定位根分类（租户内唯一，避免同名分类被一起命中）；
    否则按 name 匹配（同名分类会全部命中，保留旧行为兼容）。定位不到返回空集。
    """
    cat_rows = (await db.execute(
        select(Category.id, Category.parent_id, Category.name).where(Category.tenant_id == tenant_id)
    )).all()
    if category_id is not None:
        matched = {r.id for r in cat_rows if r.id == category_id}
    else:
        matched = {r.id for r in cat_rows if r.name == category_name}
    children: dict[int | None, list[int]] = {}
    for r in cat_rows:
        children.setdefault(r.parent_id, []).append(r.id)
    cat_ids, stack = set(), list(matched)
    while stack:  # ponytail: 分类量小内存遍历足够，过万再换递归 CTE
        c = stack.pop()
        if c in cat_ids:
            continue
        cat_ids.add(c)
        stack.extend(children.get(c, []))
    return cat_ids


async def build_product_filter_conditions(
    db: AsyncSession,
    tenant_id: int,
    *,
    keyword: str | None = None,
    category: str | None = None,
    category_id: int | None = None,
    status: str | None = None,
    expiry_status: str | None = None,
    effective_expiry=None,
    warning_days: int = 30,
) -> list:
    """返回可 AND 到商品查询上的过滤条件列表。

    与 list_products 逐条等价：
    - keyword：商品名称或 SKU 模糊匹配。
    - category_id / category：分类 + 全部后代分类，定位不到 → 空结果。
      优先用 category_id（唯一，避免同名分类混入），无则回退 category 名称。
    - status：商品状态精确匹配。
    - expiry_status：依赖调用方构造的 effective_expiry 表达式；未传则跳过。
    """
    conds: list = []
    if keyword:
        conds.append(or_(
            Product.name.ilike(f"%{keyword}%"),
            Product.sku.ilike(f"%{keyword}%"),
        ))
    if category_id is not None or category:
        cat_ids = await collect_category_descendants(db, tenant_id, category, category_id=category_id)
        if cat_ids:
            conds.append(Product.id.in_(
                select(product_categories.c.product_id).where(
                    product_categories.c.category_id.in_(cat_ids)
                )
            ))
        else:
            conds.append(Product.id.is_(None))  # 分类不存在 → 空结果
    if status:
        conds.append(Product.status == status)
    if expiry_status and effective_expiry is not None:
        today = date.today()
        cutoff = today + timedelta(days=warning_days)
        if expiry_status == "none":
            conds.append(effective_expiry.is_(None))
        elif expiry_status == "expired":
            conds.append(effective_expiry < today)
        elif expiry_status == "expiring":
            conds.append(and_(effective_expiry >= today, effective_expiry <= cutoff))
        elif expiry_status == "normal":
            conds.append(effective_expiry > cutoff)
    return conds


if __name__ == "__main__":  # 自检：BFS 后代收集与空结果语义
    import asyncio

    class _FakeResult:
        def __init__(self, rows): self._rows = rows
        def all(self): return self._rows

    class _Row:
        def __init__(self, id, parent_id, name):
            self.id, self.parent_id, self.name = id, parent_id, name

    class _FakeDB:
        def __init__(self, rows): self._rows = rows
        async def execute(self, *_): return _FakeResult(self._rows)

    # 树： 1(食品) → 2(零食) → 4(薯片)， 1 → 3(饮料)， 5(独立)
    rows = [_Row(1, None, "食品"), _Row(2, 1, "零食"), _Row(3, 1, "饮料"),
            _Row(4, 2, "薯片"), _Row(5, None, "独立")]

    async def _run():
        db = _FakeDB(rows)
        assert await collect_category_descendants(db, 1, "食品") == {1, 2, 3, 4}
        assert await collect_category_descendants(db, 1, "零食") == {2, 4}
        assert await collect_category_descendants(db, 1, "薯片") == {4}
        assert await collect_category_descendants(db, 1, "不存在") == set()
        # 按 id 精确定位：只取该分支，不牵连同名分类
        assert await collect_category_descendants(db, 1, category_id=2) == {2, 4}
        assert await collect_category_descendants(db, 1, category_id=999) == set()
        # 分类不存在 → 追加一个恒假条件，保证空结果
        conds = await build_product_filter_conditions(db, 1, category="不存在")
        assert len(conds) == 1
        conds = await build_product_filter_conditions(db, 1, category_id=999)
        assert len(conds) == 1  # 不存在的 id → 空结果
        conds = await build_product_filter_conditions(db, 1, category_id=2)
        assert len(conds) == 1
        # 无任何筛选 → 空条件
        assert await build_product_filter_conditions(db, 1) == []
        print("product_query self-check OK")

    asyncio.run(_run())