"""进销存 Admin API。挂载于 /api/admin。"""
import json

from fastapi import APIRouter, Depends, HTTPException, Query
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

from app.api.deps import get_db, require_permission
from app.core.models.user import User

# 权限依赖：读用 inventory.view，写按动作分类
_VIEW = require_permission("inventory.view")
_MANAGE = require_permission("inventory.manage")
_ADJUST = require_permission("inventory.adjust")
_PURCHASE = require_permission("inventory.purchase")
_PURCHASE_APPROVE = require_permission("inventory.purchase.approve")
_OPERATE = require_permission("inventory.operate")
from app.plugins.inventory import services as svc
from app.plugins.inventory.lifecycle import assert_writable
from app.plugins.inventory.schemas import (
    SettingsOut, EnableIn, SettingsUpdateIn, WarehouseIn, WarehouseUpdateIn, WarehouseOut,
    BalanceOut, TransactionOut, AdjustmentIn, SupplierIn, SupplierUpdateIn, SupplierProductIn,
    PurchaseOrderIn, PurchaseActionIn,
    PurchaseReceiptIn, VendorReturnIn,
    LocationIn, LocationUpdateIn, PutawayRuleIn, TransferOperationIn,
    AllocationRequestIn, FulfillmentActionIn,
    CustomerReturnIn, StockStateActionIn,
    ReorderRuleIn, LandedCostIn, SuggestionConvertIn, RevaluationIn,
    TransferIn, StocktakeIn, KitOpIn, BatchQCIn, InventoryPolicyIn,
    ProductProfileIn, ProductProfileOut,
)

router = APIRouter(tags=["inventory"])


async def _guard_write(db: AsyncSession, tenant_id: int) -> None:
    """suspended 租户禁止一切库存写入。"""
    assert_writable(await svc.get_state(db, tenant_id))


@router.get("/inventory/settings", response_model=SettingsOut | None)
async def get_settings(db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    r = await db.execute(
        text("SELECT lifecycle_state, warehouse_mode, cost_method, batch_enabled, expiry_enabled, "
             "serial_enabled, qc_enabled, allow_negative, purchase_approval, period_close_enabled, "
             "base_currency, default_warehouse_id FROM inventory_settings WHERE tenant_id=:tid LIMIT 1"),
        {"tid": user.tenant_id},
    )
    row = r.fetchone()
    if not row:
        return None
    return SettingsOut(
        lifecycle_state=row[0], warehouse_mode=row[1], cost_method=row[2],
        batch_enabled=bool(row[3]), expiry_enabled=bool(row[4]), serial_enabled=bool(row[5]),
        qc_enabled=bool(row[6]), allow_negative=bool(row[7]), purchase_approval=bool(row[8]),
        period_close_enabled=bool(row[9]), base_currency=row[10], default_warehouse_id=row[11],
    )


@router.post("/inventory/enable")
async def enable(body: EnableIn, db: AsyncSession = Depends(get_db),
                 user: User = Depends(_MANAGE)):
    wh_id = await svc.enable_inventory(
        db, user.tenant_id, warehouse_code=body.warehouse_code,
        warehouse_name=body.warehouse_name, opening_mode=body.opening_mode,
        base_currency=body.base_currency,
    )
    await db.commit()
    return {"ok": True, "default_warehouse_id": wh_id}


@router.post("/inventory/opening/import-current-stock")
async def import_current_stock_as_opening(db: AsyncSession = Depends(get_db),
                                          user: User = Depends(_MANAGE)):
    """仅为启用时选择“从零建账”的租户补录一次当前商品库存。"""
    await _guard_write(db, user.tenant_id)
    setting = (await db.execute(
        text("SELECT default_warehouse_id FROM inventory_settings WHERE tenant_id=:tid"),
        {"tid": user.tenant_id},
    )).scalar()
    if not setting:
        raise HTTPException(status_code=404, detail="进销存未启用或默认仓库不存在")
    existing = (await db.execute(
        text("SELECT 1 FROM inventory_transactions WHERE tenant_id=:tid LIMIT 1"),
        {"tid": user.tenant_id},
    )).scalar()
    if existing:
        raise HTTPException(status_code=409, detail="账本已有流水，不能重复导入期初库存")
    await svc.import_opening_from_stock(db, user.tenant_id, int(setting))
    await db.commit()
    return {"ok": True}


@router.put("/inventory/settings")
async def update_settings(body: SettingsUpdateIn, db: AsyncSession = Depends(get_db),
                          user: User = Depends(_MANAGE)):
    fields = body.model_dump(exclude_unset=True)
    if not fields:
        return {"ok": True}
    # bool → 0/1
    for k in ("batch_enabled", "expiry_enabled", "serial_enabled", "qc_enabled",
              "allow_negative", "purchase_approval", "period_close_enabled"):
        if k in fields:
            fields[k] = 1 if fields[k] else 0
    # 效期依赖批次：按「当前 + 本次提交」合并后的有效状态校验（提交里显式关批次也要拦）
    cur = (await db.execute(
        text("SELECT batch_enabled, expiry_enabled FROM inventory_settings WHERE tenant_id=:tid"),
        {"tid": user.tenant_id},
    )).fetchone()
    if cur is None:
        raise HTTPException(status_code=404, detail="进销存未启用")
    eff_batch = fields.get("batch_enabled", int(cur[0]))
    eff_expiry = fields.get("expiry_enabled", int(cur[1]))
    if eff_expiry and not eff_batch:
        raise HTTPException(status_code=400, detail="效期管理依赖批次管理：启用效期或关闭批次前请先确认批次已启用/效期已关闭")
    sets = ", ".join(f"{k}=:{k}" for k in fields)
    fields["tid"] = user.tenant_id
    res = await db.execute(
        text(f"UPDATE inventory_settings SET {sets}, updated_at=NOW(3) WHERE tenant_id=:tid"),
        fields,
    )
    if res.rowcount == 0:
        raise HTTPException(status_code=404, detail="进销存未启用")
    await db.commit()
    return {"ok": True}


@router.get("/inventory/policy")
async def get_inventory_policy(db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    return (await svc.load_policy(db, user.tenant_id)).to_json()


@router.put("/inventory/policy")
async def update_inventory_policy(body: InventoryPolicyIn, db: AsyncSession = Depends(get_db),
                                  user: User = Depends(_MANAGE)):
    await _guard_write(db, user.tenant_id)
    try:
        policy = body.to_policy()
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc)) from exc
    result = await db.execute(
        text("UPDATE inventory_settings SET sales_policy=:policy, updated_at=NOW(3) "
             "WHERE tenant_id=:tid"),
        {"policy": json.dumps(policy.to_json(), ensure_ascii=False), "tid": user.tenant_id},
    )
    if result.rowcount == 0:
        raise HTTPException(status_code=404, detail="进销存未启用")
    await db.commit()
    return policy.to_json()


async def _validate_product_profile_target(db: AsyncSession, tenant_id: int, product_id: int,
                                           variant_id: int) -> None:
    product = (await db.execute(
        text("SELECT 1 FROM products WHERE id=:pid AND tenant_id=:tid"),
        {"pid": product_id, "tid": tenant_id},
    )).scalar()
    if not product:
        raise HTTPException(status_code=404, detail="商品不存在")
    if variant_id:
        variant = (await db.execute(
            text("SELECT 1 FROM product_variants WHERE id=:vid AND product_id=:pid AND tenant_id=:tid"),
            {"vid": variant_id, "pid": product_id, "tid": tenant_id},
        )).scalar()
        if not variant:
            raise HTTPException(status_code=400, detail="规格不存在或不属于该商品")


@router.get("/inventory/products/{product_id}/profile", response_model=ProductProfileOut)
async def get_product_profile(product_id: int, variant_id: int | None = Query(None),
                              db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    vid = int(variant_id or 0)
    await _validate_product_profile_target(db, user.tenant_id, product_id, vid)
    row = (await db.execute(
        text("SELECT tracking_type, qc_required, shelf_life_days, removal_strategy "
             "FROM inventory_product_profiles WHERE tenant_id=:tid AND product_id=:pid "
             "AND variant_id=:vid"),
        {"tid": user.tenant_id, "pid": product_id, "vid": vid},
    )).fetchone()
    if not row:
        return ProductProfileOut(product_id=product_id, variant_id=variant_id)
    return ProductProfileOut(
        product_id=product_id, variant_id=variant_id, tracking_type=row[0],
        qc_required=bool(row[1]), shelf_life_days=row[2], removal_strategy=row[3],
    )


@router.put("/inventory/products/{product_id}/profile", response_model=ProductProfileOut)
async def update_product_profile(product_id: int, body: ProductProfileIn,
                                 db: AsyncSession = Depends(get_db), user: User = Depends(_MANAGE)):
    await _guard_write(db, user.tenant_id)
    vid = int(body.variant_id or 0)
    await _validate_product_profile_target(db, user.tenant_id, product_id, vid)
    settings = (await db.execute(
        text("SELECT batch_enabled, expiry_enabled, serial_enabled, qc_enabled "
             "FROM inventory_settings WHERE tenant_id=:tid"),
        {"tid": user.tenant_id},
    )).fetchone()
    if not settings:
        raise HTTPException(status_code=404, detail="进销存未启用")
    if body.tracking_type == "lot" and not settings[0]:
        raise HTTPException(status_code=400, detail="租户未启用批次能力")
    if body.tracking_type == "serial" and not settings[2]:
        raise HTTPException(status_code=400, detail="租户未启用序列号能力")
    if body.shelf_life_days and not settings[1]:
        raise HTTPException(status_code=400, detail="租户未启用效期能力")
    if body.qc_required and not settings[3]:
        raise HTTPException(status_code=400, detail="租户未启用质检能力")
    await db.execute(
        text("INSERT INTO inventory_product_profiles "
             "(tenant_id, product_id, variant_id, tracking_type, qc_required, shelf_life_days, "
             "removal_strategy, created_at, updated_at) "
             "VALUES (:tid,:pid,:vid,:tracking,:qc,:days,:strategy,NOW(3),NOW(3)) "
             "ON DUPLICATE KEY UPDATE tracking_type=VALUES(tracking_type), "
             "qc_required=VALUES(qc_required), shelf_life_days=VALUES(shelf_life_days), "
             "removal_strategy=VALUES(removal_strategy), updated_at=NOW(3)"),
        {"tid": user.tenant_id, "pid": product_id, "vid": vid,
         "tracking": body.tracking_type, "qc": int(body.qc_required),
         "days": body.shelf_life_days, "strategy": body.removal_strategy},
    )
    await db.commit()
    return ProductProfileOut(product_id=product_id, **body.model_dump())


@router.get("/inventory/warehouses", response_model=list[WarehouseOut])
async def list_warehouses(db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    r = await db.execute(
        text("SELECT id, code, name, is_active FROM inventory_warehouses "
             "WHERE tenant_id=:tid ORDER BY id"),
        {"tid": user.tenant_id},
    )
    return [WarehouseOut(id=x[0], code=x[1], name=x[2], is_active=bool(x[3])) for x in r.fetchall()]


@router.post("/inventory/warehouses", response_model=WarehouseOut, status_code=201)
async def create_warehouse(body: WarehouseIn, db: AsyncSession = Depends(get_db),
                           user: User = Depends(_MANAGE)):
    try:
        await db.execute(
            text("INSERT INTO inventory_warehouses (tenant_id, code, name, is_active, created_at, updated_at) "
                 "VALUES (:tid,:code,:name,1,NOW(3),NOW(3))"),
            {"tid": user.tenant_id, "code": body.code, "name": body.name},
        )
    except Exception:
        raise HTTPException(status_code=409, detail=f"仓库编码 {body.code} 已存在")
    wid = (await db.execute(text("SELECT LAST_INSERT_ID()"))).scalar()
    await db.execute(
        text("INSERT INTO inventory_locations "
             "(tenant_id,warehouse_id,parent_id,code,name,is_default,location_type,barcode,is_active,created_at,updated_at) "
             "VALUES (:tid,:wh,NULL,'DEFAULT','默认库位',1,'internal',NULL,1,NOW(3),NOW(3))"),
        {"tid": user.tenant_id, "wh": wid},
    )
    await db.commit()
    return WarehouseOut(id=int(wid), code=body.code, name=body.name, is_active=True)


@router.put("/inventory/warehouses/{warehouse_id}", response_model=WarehouseOut)
async def update_warehouse(warehouse_id: int, body: WarehouseUpdateIn, db: AsyncSession = Depends(get_db),
                           user: User = Depends(_MANAGE)):
    try:
        res = await db.execute(
            text("UPDATE inventory_warehouses SET code=:code, name=:name, is_active=:active, updated_at=NOW(3) "
                 "WHERE id=:id AND tenant_id=:tid"),
            {"code": body.code, "name": body.name, "active": int(body.is_active),
             "id": warehouse_id, "tid": user.tenant_id},
        )
    except Exception:
        raise HTTPException(status_code=409, detail=f"仓库编码 {body.code} 已存在")
    if res.rowcount == 0:
        raise HTTPException(status_code=404, detail="仓库不存在")
    await db.commit()
    return WarehouseOut(id=warehouse_id, code=body.code, name=body.name, is_active=body.is_active)


@router.get("/inventory/locations")
async def list_locations(warehouse_id: int | None = Query(None), db: AsyncSession = Depends(get_db),
                         user: User = Depends(_VIEW)):
    sql = ("SELECT id,warehouse_id,parent_id,code,name,is_default,location_type,barcode,is_active "
           "FROM inventory_locations WHERE tenant_id=:tid")
    params = {"tid": user.tenant_id}
    if warehouse_id is not None:
        sql += " AND warehouse_id=:wh"
        params["wh"] = warehouse_id
    rows = (await db.execute(text(sql + " ORDER BY warehouse_id,parent_id,id"), params)).fetchall()
    keys = ("id", "warehouse_id", "parent_id", "code", "name", "is_default",
            "location_type", "barcode", "is_active")
    return [dict(zip(keys, row)) for row in rows]


async def _validate_location_warehouse(db: AsyncSession, tenant_id: int, warehouse_id: int) -> None:
    exists = (await db.execute(
        text("SELECT 1 FROM inventory_warehouses WHERE id=:id AND tenant_id=:tid AND is_active=1 FOR UPDATE"),
        {"id": warehouse_id, "tid": tenant_id},
    )).scalar()
    if not exists:
        raise HTTPException(status_code=400, detail="仓库不存在、已停用或不属于当前租户")


@router.post("/inventory/locations", status_code=201)
async def create_location(body: LocationIn, db: AsyncSession = Depends(get_db),
                          user: User = Depends(_MANAGE)):
    from app.plugins.inventory.locations import validate_parent

    await _guard_write(db, user.tenant_id)
    await _validate_location_warehouse(db, user.tenant_id, body.warehouse_id)
    await validate_parent(db, user.tenant_id, body.warehouse_id, body.parent_id)
    if body.is_default:
        await db.execute(text("UPDATE inventory_locations SET is_default=0 WHERE tenant_id=:tid AND warehouse_id=:wh"),
                         {"tid": user.tenant_id, "wh": body.warehouse_id})
    try:
        await db.execute(
            text("INSERT INTO inventory_locations "
                 "(tenant_id,warehouse_id,parent_id,code,name,is_default,location_type,barcode,is_active,created_at,updated_at) "
                 "VALUES (:tid,:wh,:parent,:code,:name,:default,:kind,:barcode,1,NOW(3),NOW(3))"),
            {"tid": user.tenant_id, "wh": body.warehouse_id, "parent": body.parent_id,
             "code": body.code, "name": body.name, "default": int(body.is_default),
             "kind": body.location_type, "barcode": body.barcode},
        )
    except Exception:
        raise HTTPException(status_code=409, detail="同仓库库位编码已存在")
    location_id = int((await db.execute(text("SELECT LAST_INSERT_ID()"))).scalar())
    await db.commit()
    return {"ok": True, "id": location_id}


@router.put("/inventory/locations/{location_id}")
async def update_location(location_id: int, body: LocationUpdateIn,
                          db: AsyncSession = Depends(get_db), user: User = Depends(_MANAGE)):
    from app.plugins.inventory.locations import validate_parent

    await _guard_write(db, user.tenant_id)
    current = (await db.execute(
        text("SELECT warehouse_id FROM inventory_locations WHERE id=:id AND tenant_id=:tid FOR UPDATE"),
        {"id": location_id, "tid": user.tenant_id},
    )).fetchone()
    if current is None:
        raise HTTPException(status_code=404, detail="库位不存在")
    if body.is_default and not body.is_active:
        raise HTTPException(status_code=400, detail="默认库位不能停用")
    if int(current[0]) != int(body.warehouse_id) or not body.is_active:
        has_stock = (await db.execute(
            text("SELECT 1 FROM inventory_balances WHERE tenant_id=:tid AND location_id=:location "
                 "AND qty<>0 LIMIT 1"),
            {"tid": user.tenant_id, "location": location_id},
        )).scalar()
        if has_stock:
            raise HTTPException(status_code=409, detail="库位仍有库存，不能转移仓库或停用")
    if int(current[0]) != int(body.warehouse_id):
        has_children = (await db.execute(
            text("SELECT 1 FROM inventory_locations WHERE tenant_id=:tid AND parent_id=:id LIMIT 1"),
            {"tid": user.tenant_id, "id": location_id},
        )).scalar()
        if has_children:
            raise HTTPException(status_code=409, detail="库位仍有下级库位，不能转移仓库")
    if body.parent_id == location_id:
        raise HTTPException(status_code=400, detail="库位不能以自己作为上级")
    await _validate_location_warehouse(db, user.tenant_id, body.warehouse_id)
    await validate_parent(db, user.tenant_id, body.warehouse_id, body.parent_id, child_id=location_id)
    if body.is_default:
        await db.execute(text("UPDATE inventory_locations SET is_default=0 WHERE tenant_id=:tid AND warehouse_id=:wh"),
                         {"tid": user.tenant_id, "wh": body.warehouse_id})
    result = await db.execute(
        text("UPDATE inventory_locations SET warehouse_id=:wh,parent_id=:parent,code=:code,name=:name,"
             "is_default=:default,location_type=:kind,barcode=:barcode,is_active=:active,updated_at=NOW(3) "
             "WHERE id=:id AND tenant_id=:tid"),
        {"wh": body.warehouse_id, "parent": body.parent_id, "code": body.code, "name": body.name,
         "default": int(body.is_default), "kind": body.location_type, "barcode": body.barcode,
         "active": int(body.is_active), "id": location_id, "tid": user.tenant_id},
    )
    if result.rowcount == 0:
        raise HTTPException(status_code=404, detail="库位不存在")
    await db.commit()
    return {"ok": True}


@router.get("/inventory/putaway-rules")
async def list_putaway_rules(warehouse_id: int | None = Query(None), db: AsyncSession = Depends(get_db),
                             user: User = Depends(_VIEW)):
    sql = ("SELECT id,warehouse_id,product_id,category_id,destination_location_id,priority,is_active "
           "FROM inventory_putaway_rules WHERE tenant_id=:tid")
    params = {"tid": user.tenant_id}
    if warehouse_id is not None:
        sql += " AND warehouse_id=:wh"
        params["wh"] = warehouse_id
    rows = (await db.execute(text(sql + " ORDER BY priority,id"), params)).fetchall()
    keys = ("id", "warehouse_id", "product_id", "category_id", "destination_location_id", "priority", "is_active")
    return [dict(zip(keys, row)) for row in rows]


@router.post("/inventory/putaway-rules", status_code=201)
async def create_putaway_rule(body: PutawayRuleIn, db: AsyncSession = Depends(get_db),
                              user: User = Depends(_MANAGE)):
    await _guard_write(db, user.tenant_id)
    location = (await db.execute(
        text("SELECT 1 FROM inventory_locations WHERE id=:id AND tenant_id=:tid AND warehouse_id=:wh AND is_active=1"),
        {"id": body.destination_location_id, "tid": user.tenant_id, "wh": body.warehouse_id},
    )).scalar()
    if not location:
        raise HTTPException(status_code=400, detail="上架目标库位不属于当前租户/仓库")
    await db.execute(
        text("INSERT INTO inventory_putaway_rules "
             "(tenant_id,warehouse_id,product_id,category_id,destination_location_id,priority,is_active,created_at,updated_at) "
             "VALUES (:tid,:wh,:pid,:category,:destination,:priority,:active,NOW(3),NOW(3))"),
        {"tid": user.tenant_id, "wh": body.warehouse_id, "pid": body.product_id or 0,
         "category": body.category_id or 0, "destination": body.destination_location_id,
         "priority": body.priority, "active": int(body.is_active)},
    )
    rule_id = int((await db.execute(text("SELECT LAST_INSERT_ID()"))).scalar())
    await db.commit()
    return {"ok": True, "id": rule_id}


@router.put("/inventory/putaway-rules/{rule_id}")
async def update_putaway_rule(rule_id: int, body: PutawayRuleIn, db: AsyncSession = Depends(get_db),
                              user: User = Depends(_MANAGE)):
    await _guard_write(db, user.tenant_id)
    location = (await db.execute(
        text("SELECT 1 FROM inventory_locations WHERE id=:id AND tenant_id=:tid AND warehouse_id=:wh AND is_active=1"),
        {"id": body.destination_location_id, "tid": user.tenant_id, "wh": body.warehouse_id},
    )).scalar()
    if not location:
        raise HTTPException(status_code=400, detail="上架目标库位不属于当前租户/仓库")
    result = await db.execute(
        text("UPDATE inventory_putaway_rules SET warehouse_id=:wh,product_id=:pid,category_id=:category,"
             "destination_location_id=:destination,priority=:priority,is_active=:active,updated_at=NOW(3) "
             "WHERE id=:id AND tenant_id=:tid"),
        {"wh": body.warehouse_id, "pid": body.product_id or 0, "category": body.category_id or 0,
         "destination": body.destination_location_id, "priority": body.priority,
         "active": int(body.is_active), "id": rule_id, "tid": user.tenant_id},
    )
    if result.rowcount == 0:
        raise HTTPException(status_code=404, detail="上架规则不存在")
    await db.commit()
    return {"ok": True}


@router.get("/inventory/balances", response_model=list[BalanceOut])
async def list_balances(
    product_id: int | None = Query(None),
    warehouse_id: int | None = Query(None),
    limit: int = Query(100, le=500),
    db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW),
):
    sql = ("SELECT product_id, variant_id, warehouse_id, location_id, batch_id, stock_state, qty "
           "FROM inventory_balances WHERE tenant_id=:tid AND qty <> 0")
    params = {"tid": user.tenant_id, "lim": limit}
    if product_id is not None:
        sql += " AND product_id=:pid"
        params["pid"] = product_id
    if warehouse_id is not None:
        sql += " AND warehouse_id=:wh"
        params["wh"] = warehouse_id
    sql += " ORDER BY product_id, variant_id LIMIT :lim"
    r = await db.execute(text(sql), params)
    return [BalanceOut(product_id=x[0], variant_id=x[1], warehouse_id=x[2], location_id=x[3],
                       batch_id=x[4], stock_state=x[5], qty=x[6]) for x in r.fetchall()]


@router.get("/inventory/transactions", response_model=list[TransactionOut])
async def list_transactions(
    product_id: int | None = Query(None),
    cursor: str | None = Query(None, description="上一页最后一条 id，按 id 降序翻页"),
    limit: int = Query(50, le=200),
    db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW),
):
    # 明细类查询走键集分页，避免大 OFFSET
    sql = ("SELECT id, product_id, variant_id, warehouse_id, stock_state, qty_delta, qty_after, "
           "doc_type, doc_id, unallocated, created_at FROM inventory_transactions "
           "WHERE tenant_id=:tid")
    params = {"tid": user.tenant_id, "lim": limit}
    if product_id is not None:
        sql += " AND product_id=:pid"
        params["pid"] = product_id
    cur = svc.decode_cursor(cursor)
    if cur is not None:
        sql += " AND id < :cur"
        params["cur"] = cur
    sql += " ORDER BY id DESC LIMIT :lim"
    r = await db.execute(text(sql), params)
    return [TransactionOut(id=x[0], product_id=x[1], variant_id=x[2], warehouse_id=x[3],
                           stock_state=x[4], qty_delta=x[5], qty_after=x[6], doc_type=x[7],
                           doc_id=x[8], unallocated=bool(x[9]), created_at=str(x[10]))
            for x in r.fetchall()]


@router.post("/inventory/adjustment")
async def create_adjustment(body: AdjustmentIn, db: AsyncSession = Depends(get_db),
                            user: User = Depends(_ADJUST)):
    await _guard_write(db, user.tenant_id)
    if await svc.get_state(db, user.tenant_id) is None:
        raise HTTPException(status_code=404, detail="进销存未启用")
    await svc.apply_adjustment(db, user.tenant_id, body.lines, operator_id=user.id)
    await db.commit()
    return {"ok": True, "lines": len(body.lines)}


@router.post("/inventory/period/close")
async def close_period(period: str = Query(..., pattern=r"^\d{4}-\d{2}$"),
                       db: AsyncSession = Depends(get_db), user: User = Depends(_MANAGE)):
    """关闭一个期间：把 closed_through 前推到 period（不可倒退）。"""
    res = await db.execute(
        text("UPDATE inventory_settings SET closed_through=:p, updated_at=NOW(3) "
             "WHERE tenant_id=:tid AND (closed_through IS NULL OR closed_through < :p)"),
        {"p": period, "tid": user.tenant_id},
    )
    if res.rowcount == 0:
        raise HTTPException(status_code=409, detail="该期间已关闭或早于已关账期间")
    await db.commit()
    return {"ok": True, "closed_through": period}


# ── 供应商与采购 ────────────────────────────────────────────────

@router.post("/inventory/suppliers", status_code=201)
async def create_supplier(body: SupplierIn, db: AsyncSession = Depends(get_db),
                          user: User = Depends(_PURCHASE)):
    try:
        await db.execute(
            text("INSERT INTO inventory_suppliers "
                 "(tenant_id, code, name, currency, contact, email, phone, tax_id, contacts, addresses, "
                 "attachment_urls, delivery_notes, payment_notes, is_active, created_at, updated_at) "
                 "VALUES (:tid,:code,:name,:cur,:contact,:email,:phone,:tax_id,:contacts,:addresses,"
                 ":attachments,:delivery,:payment,1,NOW(3),NOW(3))"),
            {"tid": user.tenant_id, "code": body.code, "name": body.name,
             "cur": body.currency, "contact": body.contact, "email": body.email, "phone": body.phone,
             "tax_id": body.tax_id, "contacts": json.dumps(body.contacts),
             "addresses": json.dumps(body.addresses), "attachments": json.dumps(body.attachment_urls),
             "delivery": body.delivery_notes, "payment": body.payment_notes},
        )
    except Exception:
        raise HTTPException(status_code=409, detail=f"供应商编码 {body.code} 已存在")
    sid = (await db.execute(text("SELECT LAST_INSERT_ID()"))).scalar()
    await db.commit()
    return {"id": int(sid), "code": body.code, "name": body.name}


@router.put("/inventory/suppliers/{supplier_id}")
async def update_supplier(supplier_id: int, body: SupplierUpdateIn, db: AsyncSession = Depends(get_db),
                          user: User = Depends(_PURCHASE)):
    try:
        res = await db.execute(
            text("UPDATE inventory_suppliers SET code=:code, name=:name, currency=:cur, contact=:contact, "
                 "email=:email, phone=:phone, tax_id=:tax_id, contacts=:contacts, addresses=:addresses, "
                 "attachment_urls=:attachments, delivery_notes=:delivery, payment_notes=:payment, "
                 "is_active=:active, updated_at=NOW(3) WHERE id=:id AND tenant_id=:tid"),
            {"code": body.code, "name": body.name, "cur": body.currency, "contact": body.contact,
             "email": body.email, "phone": body.phone, "tax_id": body.tax_id,
             "contacts": json.dumps(body.contacts), "addresses": json.dumps(body.addresses),
             "attachments": json.dumps(body.attachment_urls), "delivery": body.delivery_notes,
             "payment": body.payment_notes,
             "active": int(body.is_active), "id": supplier_id, "tid": user.tenant_id},
        )
    except Exception:
        raise HTTPException(status_code=409, detail=f"供应商编码 {body.code} 已存在")
    if res.rowcount == 0:
        raise HTTPException(status_code=404, detail="供应商不存在")
    await db.commit()
    return {"ok": True}


@router.get("/inventory/suppliers")
async def list_suppliers(db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    r = await db.execute(
        text("SELECT id, code, name, currency, contact, email, phone, tax_id, contacts, addresses, "
             "attachment_urls, delivery_notes, payment_notes, is_active FROM inventory_suppliers "
             "WHERE tenant_id=:tid ORDER BY id"),
        {"tid": user.tenant_id},
    )
    return [{"id": x[0], "code": x[1], "name": x[2], "currency": x[3], "contact": x[4],
             "email": x[5], "phone": x[6], "tax_id": x[7], "contacts": x[8] or [],
             "addresses": x[9] or [], "attachment_urls": x[10] or [], "delivery_notes": x[11],
             "payment_notes": x[12], "is_active": bool(x[13])} for x in r.fetchall()]


async def _validate_supplier_product_refs(db: AsyncSession, tenant_id: int, body: SupplierProductIn) -> int:
    variant_id = body.variant_id or 0
    supplier = (await db.execute(
        text("SELECT 1 FROM inventory_suppliers WHERE id=:id AND tenant_id=:tid"),
        {"id": body.supplier_id, "tid": tenant_id},
    )).scalar()
    product = (await db.execute(
        text("SELECT 1 FROM products WHERE id=:id AND tenant_id=:tid"),
        {"id": body.product_id, "tid": tenant_id},
    )).scalar()
    if not supplier or not product:
        raise HTTPException(status_code=400, detail="供应商或商品不存在，或不属于当前租户")
    if variant_id:
        variant = (await db.execute(
            text("SELECT 1 FROM product_variants WHERE id=:id AND product_id=:pid AND tenant_id=:tid"),
            {"id": variant_id, "pid": body.product_id, "tid": tenant_id},
        )).scalar()
        if not variant:
            raise HTTPException(status_code=400, detail="商品规格不存在或不属于当前商品")
    return variant_id


@router.get("/inventory/supplier-products")
async def list_supplier_products(supplier_id: int | None = Query(None), product_id: int | None = Query(None),
                                 db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    clauses = ["tenant_id=:tid"]
    params = {"tid": user.tenant_id}
    if supplier_id is not None:
        clauses.append("supplier_id=:sid")
        params["sid"] = supplier_id
    if product_id is not None:
        clauses.append("product_id=:pid")
        params["pid"] = product_id
    rows = (await db.execute(text(
        "SELECT id, supplier_id, product_id, variant_id, supplier_sku, purchase_uom, uom_factor, "
        "unit_price, currency, min_order_qty, order_multiple, lead_time_days, priority, valid_from, "
        "valid_to, is_active FROM inventory_supplier_products WHERE " + " AND ".join(clauses) +
        " ORDER BY priority, id"
    ), params)).fetchall()
    keys = ("id", "supplier_id", "product_id", "variant_id", "supplier_sku", "purchase_uom",
            "uom_factor", "unit_price", "currency", "min_order_qty", "order_multiple",
            "lead_time_days", "priority", "valid_from", "valid_to", "is_active")
    return [dict(zip(keys, row)) for row in rows]


@router.post("/inventory/supplier-products", status_code=201)
async def create_supplier_product(body: SupplierProductIn, db: AsyncSession = Depends(get_db),
                                  user: User = Depends(_PURCHASE)):
    variant_id = await _validate_supplier_product_refs(db, user.tenant_id, body)
    values = body.model_dump()
    values.update(tid=user.tenant_id, vid=variant_id, active=int(body.is_active))
    try:
        await db.execute(text(
            "INSERT INTO inventory_supplier_products "
            "(tenant_id,supplier_id,product_id,variant_id,supplier_sku,purchase_uom,uom_factor,unit_price,"
            "currency,min_order_qty,order_multiple,lead_time_days,priority,valid_from,valid_to,is_active,created_at,updated_at) "
            "VALUES (:tid,:supplier_id,:product_id,:vid,:supplier_sku,:purchase_uom,:uom_factor,:unit_price,"
            ":currency,:min_order_qty,:order_multiple,:lead_time_days,:priority,:valid_from,:valid_to,:active,NOW(3),NOW(3))"
        ), values)
    except Exception:
        raise HTTPException(status_code=409, detail="该供应商商品关系已存在")
    catalog_id = int((await db.execute(text("SELECT LAST_INSERT_ID()"))).scalar())
    await db.commit()
    return {"ok": True, "id": catalog_id}


@router.put("/inventory/supplier-products/{catalog_id}")
async def update_supplier_product(catalog_id: int, body: SupplierProductIn,
                                  db: AsyncSession = Depends(get_db), user: User = Depends(_PURCHASE)):
    variant_id = await _validate_supplier_product_refs(db, user.tenant_id, body)
    values = body.model_dump()
    values.update(id=catalog_id, tid=user.tenant_id, vid=variant_id, active=int(body.is_active))
    result = await db.execute(text(
        "UPDATE inventory_supplier_products SET supplier_id=:supplier_id,product_id=:product_id,"
        "variant_id=:vid,supplier_sku=:supplier_sku,purchase_uom=:purchase_uom,uom_factor=:uom_factor,"
        "unit_price=:unit_price,currency=:currency,min_order_qty=:min_order_qty,order_multiple=:order_multiple,"
        "lead_time_days=:lead_time_days,priority=:priority,valid_from=:valid_from,valid_to=:valid_to,"
        "is_active=:active,updated_at=NOW(3) WHERE id=:id AND tenant_id=:tid"
    ), values)
    if result.rowcount == 0:
        raise HTTPException(status_code=404, detail="供应商商品关系不存在")
    await db.commit()
    return {"ok": True}


@router.post("/inventory/purchase-orders", status_code=201)
async def create_purchase_order(body: PurchaseOrderIn, db: AsyncSession = Depends(get_db),
                                user: User = Depends(_PURCHASE)):
    from app.plugins.inventory import purchasing
    await _guard_write(db, user.tenant_id)
    po_id = await purchasing.create_po(
        db, user.tenant_id, supplier_id=body.supplier_id, warehouse_id=body.warehouse_id,
        currency=body.currency, fx_rate=body.fx_rate, lines=body.lines, requester_id=user.id,
        supplier_promise_date=body.supplier_promise_date,
        planned_arrival_date=body.planned_arrival_date,
    )
    await db.commit()
    return {"ok": True, "po_id": po_id}


@router.get("/inventory/purchase-orders/{po_id}")
async def get_purchase_order(po_id: int, db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    po = (await db.execute(
        text("SELECT id, po_no, supplier_id, warehouse_id, status, currency, fx_rate, total_amount, created_at "
             "FROM inventory_purchase_orders WHERE id=:id AND tenant_id=:tid"),
        {"id": po_id, "tid": user.tenant_id},
    )).fetchone()
    if po is None:
        raise HTTPException(status_code=404, detail="采购单不存在")
    lines = (await db.execute(
        text("SELECT id, product_id, variant_id, qty, unit_cost, received_qty, purchase_uom, uom_factor, batch_no, expires_on "
             "FROM inventory_purchase_order_lines WHERE po_id=:po AND tenant_id=:tid ORDER BY id"),
        {"po": po_id, "tid": user.tenant_id},
    )).fetchall()
    return {"id": po[0], "po_no": po[1], "supplier_id": po[2], "warehouse_id": po[3], "status": po[4],
            "currency": po[5], "fx_rate": str(po[6]), "total_amount": str(po[7]), "created_at": str(po[8]),
            "lines": [{"id": line[0], "product_id": line[1], "variant_id": line[2] or None,
                       "qty": str(line[3]), "unit_cost": str(line[4]), "received_qty": str(line[5]),
                       "purchase_uom": line[6], "uom_factor": str(line[7]),
                       "batch_no": line[8], "expires_on": str(line[9]) if line[9] else None} for line in lines]}


@router.put("/inventory/purchase-orders/{po_id}")
async def update_purchase_order(po_id: int, body: PurchaseOrderIn, db: AsyncSession = Depends(get_db),
                                user: User = Depends(_PURCHASE)):
    from app.plugins.inventory import purchasing
    await _guard_write(db, user.tenant_id)
    await purchasing.update_po(db, user.tenant_id, po_id, supplier_id=body.supplier_id,
                               warehouse_id=body.warehouse_id, currency=body.currency,
                               fx_rate=body.fx_rate, lines=body.lines,
                               supplier_promise_date=body.supplier_promise_date,
                               planned_arrival_date=body.planned_arrival_date)
    await db.commit()
    return {"ok": True}


@router.post("/inventory/purchase-orders/{po_id}/cancel")
async def cancel_purchase_order(po_id: int, db: AsyncSession = Depends(get_db),
                                user: User = Depends(_PURCHASE)):
    from app.plugins.inventory import purchasing
    await _guard_write(db, user.tenant_id)
    await purchasing.cancel_po(db, user.tenant_id, po_id)
    await db.commit()
    return {"ok": True}


@router.post("/inventory/purchase-orders/{po_id}/approve")
async def approve_purchase_order(po_id: int, body: PurchaseActionIn | None = None,
                                 db: AsyncSession = Depends(get_db),
                                 user: User = Depends(_PURCHASE_APPROVE)):
    from app.plugins.inventory import purchasing
    await _guard_write(db, user.tenant_id)
    await purchasing.approve_po(db, user.tenant_id, po_id, approver_id=user.id,
                                comment=body.comment if body else None)
    await db.commit()
    return {"ok": True}


@router.post("/inventory/purchase-orders/{po_id}/submit")
async def submit_purchase_order(po_id: int, db: AsyncSession = Depends(get_db),
                                user: User = Depends(_PURCHASE)):
    from app.plugins.inventory import purchasing
    await _guard_write(db, user.tenant_id)
    status = await purchasing.submit_po(db, user.tenant_id, po_id)
    await db.commit()
    return {"ok": True, "status": status}


@router.post("/inventory/purchase-orders/{po_id}/close")
async def close_purchase_order(po_id: int, body: PurchaseActionIn | None = None,
                               db: AsyncSession = Depends(get_db), user: User = Depends(_PURCHASE)):
    from app.plugins.inventory import purchasing
    await _guard_write(db, user.tenant_id)
    await purchasing.close_po(db, user.tenant_id, po_id, reason=body.reason if body else None)
    await db.commit()
    return {"ok": True}


@router.post("/inventory/purchase-orders/{po_id}/receive")
async def receive_purchase_order(po_id: int, db: AsyncSession = Depends(get_db),
                                 user: User = Depends(_PURCHASE)):
    from app.plugins.inventory import purchasing
    await _guard_write(db, user.tenant_id)
    await purchasing.receive_po(db, user.tenant_id, po_id, operator_id=user.id)
    await db.commit()
    return {"ok": True}


@router.post("/inventory/receipts", status_code=201)
async def create_receipt(body: PurchaseReceiptIn, db: AsyncSession = Depends(get_db),
                         user: User = Depends(_PURCHASE)):
    from app.plugins.inventory.operations import create_purchase_receipt

    await _guard_write(db, user.tenant_id)
    operation_id = await create_purchase_receipt(
        db, user.tenant_id, body.purchase_order_id, body.lines, planned_at=body.planned_at,
        reference=body.reference, idempotency_key=body.idempotency_key,
    )
    await db.commit()
    return {"ok": True, "operation_id": operation_id}


@router.get("/inventory/receipts")
async def list_receipts(state: str | None = Query(None), db: AsyncSession = Depends(get_db),
                        user: User = Depends(_VIEW)):
    where = ["op.tenant_id=:tid", "op.operation_type='receipt'"]
    params = {"tid": user.tenant_id}
    if state:
        where.append("op.state=:state")
        params["state"] = state
    rows = (await db.execute(text(
        "SELECT op.id,op.state,op.reference,op.created_at,op.actual_at,po.po_no,w.name "
        "FROM inventory_operations op JOIN inventory_purchase_orders po ON po.id=op.source_doc_id "
        "AND po.tenant_id=op.tenant_id JOIN inventory_warehouses w ON w.id=op.warehouse_id "
        "AND w.tenant_id=op.tenant_id WHERE " + " AND ".join(where) + " ORDER BY op.id DESC"), params)).fetchall()
    return {"items": [{"id": x[0], "state": x[1], "reference": x[2], "created_at": str(x[3]),
                       "actual_at": str(x[4]) if x[4] else None, "po_no": x[5], "warehouse_name": x[6]}
                      for x in rows]}


@router.get("/inventory/receipts/{operation_id}")
async def get_receipt(operation_id: int, db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    header = (await db.execute(text("SELECT source_doc_id,state,reference,planned_at FROM inventory_operations "
                                    "WHERE id=:id AND tenant_id=:tid AND operation_type='receipt'"),
                               {"id": operation_id, "tid": user.tenant_id})).fetchone()
    if not header:
        raise HTTPException(status_code=404, detail="收货单不存在")
    moves = (await db.execute(text("SELECT purchase_order_line_id,done_qty,batch_no,expires_on,serial_numbers "
                                   "FROM inventory_moves WHERE operation_id=:id AND tenant_id=:tid ORDER BY id"),
                              {"id": operation_id, "tid": user.tenant_id})).fetchall()
    return {"id": operation_id, "purchase_order_id": header[0], "state": header[1], "reference": header[2],
            "planned_at": str(header[3]) if header[3] else None,
            "lines": [{"purchase_order_line_id": x[0], "done_qty": str(x[1]), "batch_no": x[2],
                       "expires_on": str(x[3]) if x[3] else None, "serial_numbers": json.loads(x[4]) if isinstance(x[4], str) else x[4] or []} for x in moves]}


@router.put("/inventory/receipts/{operation_id}")
async def update_receipt(operation_id: int, body: PurchaseReceiptIn, db: AsyncSession = Depends(get_db),
                         user: User = Depends(_PURCHASE)):
    from app.plugins.inventory.operations import update_purchase_receipt
    await _guard_write(db, user.tenant_id)
    await update_purchase_receipt(db, user.tenant_id, operation_id, body)
    await db.commit()
    return {"ok": True}


@router.post("/inventory/receipts/{operation_id}/cancel")
async def cancel_receipt(operation_id: int, db: AsyncSession = Depends(get_db), user: User = Depends(_PURCHASE)):
    from app.plugins.inventory.operations import cancel_purchase_receipt
    await _guard_write(db, user.tenant_id)
    await cancel_purchase_receipt(db, user.tenant_id, operation_id)
    await db.commit()
    return {"ok": True}


@router.post("/inventory/receipts/{operation_id}/validate")
async def validate_receipt(operation_id: int, db: AsyncSession = Depends(get_db),
                           user: User = Depends(_PURCHASE)):
    from app.plugins.inventory.operations import post_operation

    await _guard_write(db, user.tenant_id)
    purchase_status = await post_operation(db, user.tenant_id, operation_id, user.id)
    await db.commit()
    return {"ok": True, "purchase_status": purchase_status}


@router.post("/inventory/vendor-returns", status_code=201)
async def create_vendor_return(body: VendorReturnIn, db: AsyncSession = Depends(get_db),
                               user: User = Depends(_PURCHASE)):
    from app.plugins.inventory.operations import create_vendor_return as create_return

    await _guard_write(db, user.tenant_id)
    operation_id = await create_return(
        db, user.tenant_id, warehouse_id=body.warehouse_id,
        purchase_order_id=body.purchase_order_id, lines=body.lines, reason=body.reason,
        reference=body.reference, idempotency_key=body.idempotency_key,
    )
    await db.commit()
    return {"ok": True, "operation_id": operation_id}


@router.post("/inventory/vendor-returns/{operation_id}/validate")
async def validate_vendor_return(operation_id: int, db: AsyncSession = Depends(get_db),
                                 user: User = Depends(_PURCHASE)):
    from app.plugins.inventory.operations import post_operation

    await _guard_write(db, user.tenant_id)
    await post_operation(db, user.tenant_id, operation_id, user.id)
    await db.commit()
    return {"ok": True}


@router.post("/inventory/operations/{operation_id}/putaway")
async def putaway_operation(operation_id: int, db: AsyncSession = Depends(get_db),
                            user: User = Depends(_OPERATE)):
    from app.plugins.inventory.operations import create_and_post_putaway

    await _guard_write(db, user.tenant_id)
    putaway_id = await create_and_post_putaway(db, user.tenant_id, operation_id, user.id)
    await db.commit()
    return {"ok": True, "operation_id": putaway_id, "changed": putaway_id is not None}


@router.get("/inventory/operations")
async def list_operations(operation_type: str | None = Query(None), state: str | None = Query(None),
                          page: int = Query(1, ge=1), page_size: int = Query(50, ge=1, le=100),
                          db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    where = ["op.tenant_id=:tid"]
    params = {"tid": user.tenant_id}
    if operation_type:
        where.append("op.operation_type=:operation_type")
        params["operation_type"] = operation_type
    if state:
        where.append("op.state=:state")
        params["state"] = state
    clause = " AND ".join(where)
    total = (await db.execute(text(
        f"SELECT COUNT(*) FROM inventory_operations op WHERE {clause}"), params)).scalar()
    params.update({"lim": page_size, "offset": (page - 1) * page_size})
    rows = (await db.execute(text(
        "SELECT op.id,op.operation_type,op.state,op.source_doc_type,op.source_doc_id,op.reference,"
        "op.created_at,op.actual_at,w.name,po.po_no "
        "FROM inventory_operations op "
        "LEFT JOIN inventory_warehouses w ON w.id=op.warehouse_id AND w.tenant_id=op.tenant_id "
        "LEFT JOIN inventory_purchase_orders po ON op.source_doc_type='purchase_order' "
        "AND po.id=op.source_doc_id AND po.tenant_id=op.tenant_id "
        f"WHERE {clause} ORDER BY op.id DESC LIMIT :lim OFFSET :offset"), params)).fetchall()
    return {"items": [
        {"id": row[0], "operation_type": row[1], "state": row[2],
         "source_doc_type": row[3], "source_doc_id": row[4], "reference": row[5],
         "created_at": str(row[6]), "actual_at": str(row[7]) if row[7] else None,
         "warehouse_name": row[8], "source_doc_no": row[9]}
        for row in rows
    ], "total": int(total or 0), "page": page, "page_size": page_size}


@router.post("/inventory/transfers", status_code=201)
async def create_transfer_operation(body: TransferOperationIn, db: AsyncSession = Depends(get_db),
                                    user: User = Depends(_OPERATE)):
    from app.plugins.inventory.operations import create_transfer_operation as create_operation

    await _guard_write(db, user.tenant_id)
    operation_id = await create_operation(db, user.tenant_id, body)
    await db.commit()
    return {"ok": True, "operation_id": operation_id}


@router.post("/inventory/transfers/{operation_id}/ship")
async def ship_transfer_operation(operation_id: int, db: AsyncSession = Depends(get_db),
                                  user: User = Depends(_OPERATE)):
    from app.plugins.inventory.operations import ship_transfer

    await _guard_write(db, user.tenant_id)
    state = await ship_transfer(db, user.tenant_id, operation_id, user.id)
    await db.commit()
    return {"ok": True, "state": state}


@router.post("/inventory/transfers/{operation_id}/receive")
async def receive_transfer_operation(operation_id: int, db: AsyncSession = Depends(get_db),
                                     user: User = Depends(_OPERATE)):
    from app.plugins.inventory.operations import receive_transfer

    await _guard_write(db, user.tenant_id)
    await receive_transfer(db, user.tenant_id, operation_id, user.id)
    await db.commit()
    return {"ok": True, "state": "done"}


@router.post("/inventory/fulfillment/orders/{order_id}/allocate")
async def allocate_fulfillment(order_id: int, body: AllocationRequestIn,
                               db: AsyncSession = Depends(get_db), user: User = Depends(_OPERATE)):
    from app.plugins.inventory.fulfillment import allocate_order
    await _guard_write(db, user.tenant_id)
    result = await allocate_order(db, user.tenant_id, order_id,
                                  warehouse_id=body.warehouse_id,
                                  shortage_mode=body.shortage_mode)
    await db.commit()
    return result


@router.post("/inventory/fulfillment/orders/{order_id}/pick")
async def pick_fulfillment(order_id: int, body: FulfillmentActionIn,
                           db: AsyncSession = Depends(get_db), user: User = Depends(_OPERATE)):
    from app.plugins.inventory.fulfillment import pick_order
    await _guard_write(db, user.tenant_id)
    result = await pick_order(db, user.tenant_id, order_id, body.lines)
    await db.commit()
    return result


@router.post("/inventory/fulfillment/orders/{order_id}/ship")
async def ship_fulfillment(order_id: int, body: FulfillmentActionIn,
                           db: AsyncSession = Depends(get_db), user: User = Depends(_OPERATE)):
    from app.plugins.inventory.fulfillment import ship_order
    await _guard_write(db, user.tenant_id)
    result = await ship_order(db, user.tenant_id, order_id, user.id, body.lines)
    await db.commit()
    return result


@router.get("/inventory/fulfillment/orders")
async def list_fulfillment_orders(stage: str | None = Query(None), keyword: str | None = Query(None, max_length=60),
                                  page: int = Query(1, ge=1), page_size: int = Query(50, ge=1, le=100),
                                  db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    stage_having = {
        "allocatable": "SUM(oi.quantity) > COALESCE(SUM(a.reserved_qty),0)",
        "pickable": "COALESCE(SUM(a.reserved_qty),0) > COALESCE(SUM(a.picked_qty),0)",
        "shippable": "COALESCE(SUM(a.picked_qty),0) > COALESCE(SUM(a.shipped_qty),0)",
        "returnable": "COALESCE(SUM(a.shipped_qty),0) > COALESCE(SUM(a.returned_qty),0)",
    }
    if stage and stage not in stage_having:
        raise HTTPException(status_code=422, detail="无效履约阶段")
    where = ["o.tenant_id=:tid"]
    params = {"tid": user.tenant_id}
    if keyword:
        where.append("o.order_no LIKE :keyword")
        params["keyword"] = f"%{keyword.strip()}%"
    clause = " AND ".join(where)
    having = f" HAVING {stage_having[stage]}" if stage else ""
    base = (
        "SELECT o.id,o.order_no,o.status,COALESCE(c.name,''),o.created_at,"
        "COALESCE(SUM(oi.quantity),0),COALESCE(SUM(a.reserved_qty),0),"
        "COALESCE(SUM(a.picked_qty),0),COALESCE(SUM(a.shipped_qty),0),COALESCE(SUM(a.returned_qty),0) "
        "FROM orders o JOIN order_items oi ON oi.order_id=o.id AND oi.tenant_id=o.tenant_id "
        "LEFT JOIN inventory_allocations a ON a.tenant_id=oi.tenant_id AND a.order_id=oi.order_id "
        "AND a.order_item_id=oi.id LEFT JOIN customers c ON c.id=o.customer_id AND c.tenant_id=o.tenant_id "
        f"WHERE {clause} GROUP BY o.id,o.order_no,o.status,c.name,o.created_at{having}"
    )
    total = (await db.execute(text(f"SELECT COUNT(*) FROM ({base}) candidates"), params)).scalar()
    params.update({"lim": page_size, "offset": (page - 1) * page_size})
    rows = (await db.execute(text(f"{base} ORDER BY created_at DESC, id DESC LIMIT :lim OFFSET :offset"), params)).fetchall()
    return {"items": [
        {"id": row[0], "order_no": row[1], "status": row[2], "customer_name": row[3],
         "created_at": str(row[4]), "ordered_qty": str(row[5]), "reserved_qty": str(row[6]),
         "picked_qty": str(row[7]), "shipped_qty": str(row[8]), "returned_qty": str(row[9])}
        for row in rows
    ], "total": int(total or 0), "page": page, "page_size": page_size}


@router.get("/inventory/fulfillment/orders/{order_id}")
async def get_fulfillment(order_id: int, db: AsyncSession = Depends(get_db),
                          user: User = Depends(_VIEW)):
    from app.plugins.inventory.fulfillment import fulfillment_status
    _order_exists = await db.execute(text("SELECT 1 FROM orders WHERE id=:id AND tenant_id=:tid"),
                                     {"id": order_id, "tid": user.tenant_id})
    if not _order_exists.scalar():
        raise HTTPException(status_code=404, detail="订单不存在")
    return await fulfillment_status(db, user.tenant_id, order_id)


@router.post("/inventory/returns/orders/{order_id}")
async def create_inventory_return(order_id: int, body: CustomerReturnIn,
                                  db: AsyncSession = Depends(get_db), user: User = Depends(_OPERATE)):
    from app.plugins.inventory.returns import create_customer_return
    await _guard_write(db, user.tenant_id)
    result = await create_customer_return(db, user.tenant_id, order_id, user.id, body)
    await db.commit()
    return result


@router.post("/inventory/state-actions")
async def create_stock_state_action(body: StockStateActionIn,
                                    db: AsyncSession = Depends(get_db), user: User = Depends(_OPERATE)):
    from app.plugins.inventory.returns import apply_stock_state_action
    await _guard_write(db, user.tenant_id)
    result = await apply_stock_state_action(db, user.tenant_id, user.id, body)
    await db.commit()
    return result


@router.post("/inventory/replenishment/rules")
async def save_reorder_rule(body: ReorderRuleIn, db: AsyncSession = Depends(get_db),
                            user: User = Depends(_OPERATE)):
    from app.plugins.inventory.planning import upsert_rule
    await _guard_write(db, user.tenant_id)
    rule_id = await upsert_rule(db, user.tenant_id, body)
    await db.commit()
    return {"id": rule_id}


@router.get("/inventory/replenishment/rules")
async def list_reorder_rules(db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    rows = (await db.execute(
        text("SELECT id,product_id,variant_id,warehouse_id,supplier_id,min_qty,target_qty,safety_qty,"
             "min_order_qty,order_multiple,is_enabled FROM inventory_reorder_rules "
             "WHERE tenant_id=:tid ORDER BY id"), {"tid": user.tenant_id}
    )).fetchall()
    return [{"id": row[0], "product_id": row[1], "variant_id": row[2] or None,
             "warehouse_id": row[3], "supplier_id": row[4], "min_qty": str(row[5]),
             "target_qty": str(row[6]), "safety_qty": str(row[7]),
             "min_order_qty": str(row[8]), "order_multiple": str(row[9]),
             "is_enabled": bool(row[10])} for row in rows]


@router.post("/inventory/replenishment/generate")
async def run_replenishment(db: AsyncSession = Depends(get_db), user: User = Depends(_OPERATE)):
    from app.plugins.inventory.planning import generate_suggestions
    await _guard_write(db, user.tenant_id)
    result = await generate_suggestions(db, user.tenant_id)
    await db.commit()
    return {"items": result}


@router.get("/inventory/replenishment/suggestions")
async def list_replenishment(db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    rows = (await db.execute(
        text("SELECT id,rule_id,product_id,variant_id,warehouse_id,supplier_id,forecast_qty,suggested_qty,"
             "generated_on,generation_no,state,purchase_order_id FROM inventory_replenishment_suggestions "
             "WHERE tenant_id=:tid ORDER BY generated_on DESC,id DESC LIMIT 1000"),
        {"tid": user.tenant_id},
    )).fetchall()
    return [{"id": row[0], "rule_id": row[1], "product_id": row[2], "variant_id": row[3] or None,
             "warehouse_id": row[4], "supplier_id": row[5], "forecast_qty": str(row[6]),
             "suggested_qty": str(row[7]), "generated_on": str(row[8]), "generation_no": row[9],
             "state": row[10], "purchase_order_id": row[11]} for row in rows]


@router.post("/inventory/replenishment/convert")
async def convert_replenishment(body: SuggestionConvertIn, db: AsyncSession = Depends(get_db),
                                user: User = Depends(_OPERATE)):
    from app.plugins.inventory.planning import convert_suggestions
    await _guard_write(db, user.tenant_id)
    purchase_order_ids = await convert_suggestions(
        db, user.tenant_id, body.suggestion_ids, user.id
    )
    await db.commit()
    return {"purchase_order_ids": purchase_order_ids}


@router.post("/inventory/landed-costs")
async def create_landed_cost(body: LandedCostIn, db: AsyncSession = Depends(get_db),
                             user: User = Depends(_OPERATE)):
    from app.plugins.inventory.planning import apply_landed_cost
    await _guard_write(db, user.tenant_id)
    result = await apply_landed_cost(db, user.tenant_id, body)
    await db.commit()
    return result


@router.post("/inventory/revaluations")
async def create_revaluation(body: RevaluationIn, db: AsyncSession = Depends(get_db),
                             user: User = Depends(_OPERATE)):
    from app.plugins.inventory.planning import revalue_inventory
    await _guard_write(db, user.tenant_id)
    result = await revalue_inventory(db, user.tenant_id, user.id, body)
    await db.commit()
    return result


# ── 仓库操作 ────────────────────────────────────────────────────

@router.post("/inventory/transfer")
async def transfer(body: TransferIn, db: AsyncSession = Depends(get_db),
                   user: User = Depends(_OPERATE)):
    from app.plugins.inventory import ops_service
    from app.plugins.inventory.traceability import load_product_profile
    await _guard_write(db, user.tenant_id)
    profile = await load_product_profile(db, user.tenant_id, body.product_id, body.variant_id or 0)
    if profile["tracking_type"] == "serial":
        raise HTTPException(status_code=409, detail="序列号商品请使用 /inventory/transfers 并提交序列号")
    await ops_service.do_transfer(
        db, user.tenant_id, product_id=body.product_id, variant_id=body.variant_id,
        from_wh=body.from_warehouse_id, to_wh=body.to_warehouse_id, qty=body.qty,
        operator_id=user.id, idem_key=body.idempotency_key,
    )
    await db.commit()
    return {"ok": True}


@router.post("/inventory/stocktake")
async def stocktake(body: StocktakeIn, db: AsyncSession = Depends(get_db),
                    user: User = Depends(_OPERATE)):
    from app.plugins.inventory import ops_service
    await _guard_write(db, user.tenant_id)
    diff = await ops_service.do_stocktake(
        db, user.tenant_id, product_id=body.product_id, variant_id=body.variant_id,
        counted_qty=body.counted_qty, operator_id=user.id, warehouse_id=body.warehouse_id,
        batch_id=body.batch_id,
        idem_key=body.idempotency_key,
    )
    await db.commit()
    return {"ok": True, "diff": str(diff)}


@router.get("/inventory/purchase-orders")
async def list_purchase_orders(keyword: str | None = Query(None, max_length=60),
                               supplier_id: int | None = Query(None), status: str | None = Query(None),
                               page: int = Query(1, ge=1), page_size: int = Query(20, ge=1, le=100),
                               db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    where = ["po.tenant_id=:tid"]
    params = {"tid": user.tenant_id}
    if keyword:
        where.append("po.po_no LIKE :keyword")
        params["keyword"] = f"%{keyword.strip()}%"
    if supplier_id is not None:
        where.append("po.supplier_id=:supplier_id")
        params["supplier_id"] = supplier_id
    if status:
        where.append("po.status=:status")
        params["status"] = status
    clause = " AND ".join(where)
    total = (await db.execute(text(f"SELECT COUNT(*) FROM inventory_purchase_orders po WHERE {clause}"), params)).scalar()
    params.update({"lim": page_size, "offset": (page - 1) * page_size})
    r = await db.execute(
        text("SELECT po.id, po.po_no, po.status, po.currency, po.total_amount, po.created_at, "
             "s.name, w.name FROM inventory_purchase_orders po "
             "JOIN inventory_suppliers s ON s.id=po.supplier_id AND s.tenant_id=po.tenant_id "
             "JOIN inventory_warehouses w ON w.id=po.warehouse_id AND w.tenant_id=po.tenant_id "
             f"WHERE {clause} ORDER BY po.id DESC LIMIT :lim OFFSET :offset"), params,
    )
    return {"items": [{"id": x[0], "po_no": x[1], "status": x[2], "currency": x[3],
                        "total_amount": str(x[4]), "created_at": str(x[5]), "supplier_name": x[6],
                        "warehouse_name": x[7]} for x in r.fetchall()], "total": int(total or 0),
            "page": page, "page_size": page_size}


@router.get("/inventory/batches")
async def list_batches(db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    r = await db.execute(
        text("SELECT b.id, b.product_id, b.variant_id, b.batch_no, b.expires_on, b.qc_state, "
             "p.name, p.sku, w.id, w.name, COALESCE(SUM(bal.qty),0) "
             "FROM inventory_batches b "
             "JOIN products p ON p.id=b.product_id AND p.tenant_id=b.tenant_id "
             "LEFT JOIN inventory_balances bal ON bal.tenant_id=b.tenant_id AND bal.batch_id=b.id "
             "AND bal.stock_state='sellable' "
             "LEFT JOIN inventory_warehouses w ON w.id=bal.warehouse_id AND w.tenant_id=b.tenant_id "
             "WHERE b.tenant_id=:tid "
             "GROUP BY b.id, b.product_id, b.variant_id, b.batch_no, b.expires_on, b.qc_state, p.name, p.sku, w.id, w.name "
             "ORDER BY b.expires_on IS NULL, b.expires_on, b.id DESC LIMIT 500"),
        {"tid": user.tenant_id},
    )
    return [{"id": x[0], "product_id": x[1], "variant_id": x[2], "batch_no": x[3],
             "expires_on": str(x[4]) if x[4] else None, "qc_state": x[5], "product_name": x[6],
             "sku": x[7], "warehouse_id": x[8], "warehouse_name": x[9], "qty": str(x[10])}
            for x in r.fetchall()]


@router.post("/inventory/batches/{batch_id}/qc")
async def update_batch_qc(batch_id: int, body: BatchQCIn, db: AsyncSession = Depends(get_db),
                          user: User = Depends(_OPERATE)):
    await _guard_write(db, user.tenant_id)
    if not await svc._batch_enabled(db, user.tenant_id):
        raise HTTPException(status_code=409, detail="未启用批次管理")
    from app.plugins.inventory.traceability import apply_batch_quality
    operation_id = await apply_batch_quality(db, user.tenant_id, batch_id, body.qc_state, user.id)
    await db.commit()
    return {"ok": True, "operation_id": operation_id}


@router.get("/inventory/trace/lots/{batch_id}")
async def trace_inventory_lot(batch_id: int, db: AsyncSession = Depends(get_db),
                              user: User = Depends(_VIEW)):
    from app.plugins.inventory.traceability import trace_lot
    return await trace_lot(db, user.tenant_id, batch_id)


@router.get("/inventory/trace/serials/{serial_no}")
async def trace_inventory_serial(serial_no: str, db: AsyncSession = Depends(get_db),
                                 user: User = Depends(_VIEW)):
    from app.plugins.inventory.traceability import trace_serial
    return await trace_serial(db, user.tenant_id, serial_no)


@router.post("/inventory/kit")
async def kit_op(body: KitOpIn, db: AsyncSession = Depends(get_db),
                 user: User = Depends(_OPERATE)):
    from app.plugins.inventory import ops_service
    await _guard_write(db, user.tenant_id)
    await ops_service.do_kit(
        db, user.tenant_id, parent_product_id=body.parent_product_id,
        kit_qty=body.kit_qty, disassemble=body.disassemble, operator_id=user.id,
        idem_key=body.idempotency_key,
    )
    await db.commit()
    return {"ok": True}


# ── 报表 ────────────────────────────────────────────────────────

@router.get("/inventory/reports/stock-value")
async def report_stock_value(db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    from app.plugins.inventory.reports import valuation_report
    return await valuation_report(db, user.tenant_id)


@router.get("/inventory/reports/low-stock")
async def report_low_stock(db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    """缺货/补货建议：可下单量低于阈值的商品。"""
    r = await db.execute(
        text("SELECT id, name, stock_qty, reserved_qty, low_stock_threshold "
             "FROM products WHERE tenant_id=:tid "
             "AND (stock_qty - reserved_qty) < low_stock_threshold ORDER BY (stock_qty - reserved_qty)"),
        {"tid": user.tenant_id},
    )
    return [{"product_id": x[0], "name": x[1], "available": str((x[2] or 0) - (x[3] or 0)),
             "threshold": x[4]} for x in r.fetchall()]


@router.get("/inventory/reports/expiring")
async def report_expiring(days: int = Query(30, ge=0, le=3650),
                          db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    """临期批次：expires_on 在 days 天内。"""
    r = await db.execute(
        text("SELECT id, product_id, batch_no, expires_on FROM inventory_batches "
             "WHERE tenant_id=:tid AND expires_on IS NOT NULL "
             "AND expires_on <= DATE_ADD(CURDATE(), INTERVAL :d DAY) ORDER BY expires_on"),
        {"tid": user.tenant_id, "d": days},
    )
    return [{"batch_id": x[0], "product_id": x[1], "batch_no": x[2],
             "expires_on": str(x[3])} for x in r.fetchall()]


@router.get("/inventory/reports/unallocated")
async def report_unallocated(db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    """未分配负库存异常桶：POS 离线 FEFO 缺口，待人工补录批次冲销。"""
    r = await db.execute(
        text("SELECT id, product_id, variant_id, qty_delta, doc_id, created_at "
             "FROM inventory_transactions WHERE tenant_id=:tid AND unallocated=1 "
             "ORDER BY id DESC LIMIT 200"),
        {"tid": user.tenant_id},
    )
    return [{"txn_id": x[0], "product_id": x[1], "variant_id": x[2], "qty_delta": str(x[3]),
             "doc_id": x[4], "created_at": str(x[5])} for x in r.fetchall()]


@router.get("/inventory/reports/forecast")
async def report_forecast(db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    from app.plugins.inventory.reports import forecast_report
    return await forecast_report(db, user.tenant_id)


@router.get("/inventory/reports/aging")
async def report_aging(db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    from app.plugins.inventory.reports import aging_report
    return await aging_report(db, user.tenant_id)


@router.get("/inventory/reports/anomalies")
async def report_anomalies(db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    from app.plugins.inventory.reports import anomaly_report
    return await anomaly_report(db, user.tenant_id)


@router.get("/inventory/reports/fulfillment")
async def report_fulfillment(db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    from app.plugins.inventory.reports import fulfillment_report
    return await fulfillment_report(db, user.tenant_id)


@router.get("/inventory/reports/suppliers")
async def report_suppliers(db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    from app.plugins.inventory.reports import supplier_report
    return await supplier_report(db, user.tenant_id)


@router.get("/inventory/reconcile")
async def reconcile_ledger(db: AsyncSession = Depends(get_db), user: User = Depends(_VIEW)):
    from app.plugins.inventory.reconcile import reconcile_inventory
    differences = await reconcile_inventory(db, user.tenant_id)
    return {"ok": not differences, "differences": differences}