zkxw/backend/app/crud/production_material_prep.py

"""配置试剂配制单 CRUD"""
from __future__ import annotations

from datetime import date, datetime
from decimal import Decimal
from typing import Any, Dict, List, Optional, Tuple

from sqlalchemy import and_, func, or_
from sqlalchemy.orm import Session

from app.core.utils import generate_uuid, parse_datetime
from app.crud.production_material import _dec, _fmt_dt, _is_enabled_status
from app.crud.production_material_stock_batch import (
    _create_batch_and_usage,
    _get_material_for_update,
    _parse_date,
    _parse_expire_date,
    _require_str,
    _stock_out_batch,
    stock_in_no_exists,
    _optional_str,
)
from app.crud.production_material_usage import list_available_requisitions_for_prep
from app.models.models import SysUser
from app.models.production_material import (
    BizProductionMaterial,
    BizProductionMaterialPrep,
    BizProductionMaterialPrepLine,
    BizProductionMaterialStockBatch,
    BizProductionMaterialUsage,
)

PREP_STATUSES = ("draft", "completed", "cancelled")


def _status_label(status: str) -> str:
    if status == "completed":
        return "已完成"
    if status == "cancelled":
        return "已作废"
    return "草稿"


def _fmt_date(d: Optional[date]) -> str:
    return d.strftime("%Y-%m-%d") if d else ""


def _batch_map_for_prep_lines(
    db: Session, batch_ids: List[str]
) -> Dict[str, BizProductionMaterialStockBatch]:
    ids = [x for x in batch_ids if x]
    if not ids:
        return {}
    rows = (
        db.query(BizProductionMaterialStockBatch)
        .filter(BizProductionMaterialStockBatch.id.in_(ids), BizProductionMaterialStockBatch.del_flag == 0)
        .all()
    )
    return {r.id: r for r in rows}


def _user_nick_name_map(db: Session, user_ids: List[str]) -> Dict[str, str]:
    ids = list({x for x in user_ids if x})
    if not ids:
        return {}
    rows = db.query(SysUser.id, SysUser.nick_name).filter(SysUser.id.in_(ids)).all()
    return {u.id: (u.nick_name or "").strip() for u in rows}


def _generate_prep_no(db: Session, tenant_no: str) -> str:
    day = datetime.now().strftime("%Y%m%d")
    like = f"PZ-{day}-%"
    cnt = (
        db.query(func.count(BizProductionMaterialPrep.id))
        .filter(
            BizProductionMaterialPrep.tenant_no == tenant_no,
            BizProductionMaterialPrep.prep_no.like(like),
        )
        .scalar()
    )
    return f"PZ-{day}-{(cnt or 0) + 1:03d}"


def _generate_stock_in_no(db: Session, tenant_no: str) -> str:
    day = datetime.now().strftime("%Y%m%d")
    like = f"RK{day}%"
    cnt = (
        db.query(func.count(BizProductionMaterialPrep.id))
        .filter(
            BizProductionMaterialPrep.tenant_no == tenant_no,
            BizProductionMaterialPrep.stock_in_no.like(like),
            BizProductionMaterialPrep.del_flag == 0,
        )
        .scalar()
    )
    sno = f"RK{day}{(cnt or 0) + 1:04d}"
    if stock_in_no_exists(db, sno, tenant_no):
        sno = f"RK{day}{(cnt or 0) + 1:04d}P"
    return sno


def _line_to_dict(row: BizProductionMaterialPrepLine) -> Dict[str, Any]:
    return {
        "id": row.id,
        "prepId": row.prep_id,
        "componentMaterialId": row.component_material_id,
        "componentMaterialType": row.component_material_type or "",
        "componentMaterialCode": row.component_material_code or "",
        "componentMaterialName": row.component_material_name or "",
        "requisitionUsageId": (row.requisition_usage_id or "").strip(),
        "actualQty": _dec(row.actual_qty),
        "usageUnit": row.usage_unit or "",
        "orderNum": row.order_num if row.order_num is not None else 0,
        "remark": row.remark or "",
    }


def _header_to_dict(
    row: BizProductionMaterialPrep,
    *,
    create_by_name: str = "",
    line_count: int = 0,
) -> Dict[str, Any]:
    return {
        "id": row.id,
        "prepNo": row.prep_no or "",
        "configuredMaterialId": row.configured_material_id,
        "configuredMaterialCode": row.configured_material_code or "",
        "configuredMaterialName": row.configured_material_name or "",
        "prepTime": _fmt_dt(row.prep_time),
        "outputQty": _dec(row.output_qty),
        "outputUnit": row.output_unit or "",
        "batchNo": row.batch_no or "",
        "storageLocation": row.storage_location or "",
        "expireDate": _fmt_date(row.expire_date),
        "stockInNo": row.stock_in_no or "",
        "outputBatchId": row.output_batch_id or "",
        "status": row.status or "draft",
        "statusLabel": _status_label(row.status or "draft"),
        "lineCount": line_count,
        "remark": row.remark or "",
        "createBy": row.create_by or "",
        "createByName": create_by_name or row.create_by or "",
        "createTime": _fmt_dt(row.create_time),
        "updateTime": _fmt_dt(row.update_time),
    }


def _get_prep_row(db: Session, prep_id: str, tenant_no: str) -> BizProductionMaterialPrep:
    row = (
        db.query(BizProductionMaterialPrep)
        .filter(
            BizProductionMaterialPrep.id == prep_id,
            BizProductionMaterialPrep.tenant_no == tenant_no,
            BizProductionMaterialPrep.del_flag == 0,
        )
        .first()
    )
    if not row:
        raise ValueError("配制单不存在")
    return row


def _require_draft(row: BizProductionMaterialPrep) -> None:
    if (row.status or "").strip() != "draft":
        raise ValueError("仅草稿状态的配制单可编辑")


def _validate_requisition(
    db: Session,
    req_id: str,
    *,
    tenant_no: str,
    component_material_id: str,
    seen: set,
) -> BizProductionMaterialUsage:
    if not req_id:
        raise ValueError("请选择子物料领用记录")
    if req_id in seen:
        raise ValueError("配制明细中存在重复的领用记录")
    seen.add(req_id)

    req = (
        db.query(BizProductionMaterialUsage)
        .filter(
            BizProductionMaterialUsage.id == req_id,
            BizProductionMaterialUsage.tenant_no == tenant_no,
            BizProductionMaterialUsage.del_flag == 0,
            BizProductionMaterialUsage.source_type == "manual_out",
            BizProductionMaterialUsage.change_type == "use",
        )
        .first()
    )
    if not req:
        raise ValueError("领用记录不存在或已失效")
    if (req.material_id or "").strip() != component_material_id:
        raise ValueError("领用记录与子物料不匹配")
    return req


def _parse_prep_lines(
    db: Session,
    lines: List[Dict[str, Any]],
    *,
    tenant_no: str,
) -> List[Dict[str, Any]]:
    if not lines:
        raise ValueError("请至少添加一条配制明细")
    parsed: List[Dict[str, Any]] = []
    seen_req: set = set()
    for idx, it in enumerate(lines):
        comp_id = (
            it.get("componentMaterialId") or it.get("component_material_id") or ""
        ).strip()
        if not comp_id:
            raise ValueError("请选择子物料")

        comp_type = (
            it.get("componentMaterialType") or it.get("component_material_type") or ""
        ).strip().lower()
        if comp_type not in ("reagent", "consumable"):
            raise ValueError("子物料类型无效")

        comp = (
            db.query(BizProductionMaterial)
            .filter(
                BizProductionMaterial.id == comp_id,
                BizProductionMaterial.tenant_no == tenant_no,
                BizProductionMaterial.del_flag == 0,
            )
            .first()
        )
        if not comp:
            raise ValueError("子物料不存在")

        req_id = (
            it.get("requisitionUsageId") or it.get("requisition_usage_id") or ""
        ).strip()
        req = _validate_requisition(
            db, req_id, tenant_no=tenant_no, component_material_id=comp_id, seen=seen_req
        )

        qty_raw = it.get("actualQty") if it.get("actualQty") is not None else it.get("actual_qty")
        if qty_raw is None or Decimal(str(qty_raw)) <= 0:
            raise ValueError("实际用量必须大于0")
        actual_qty = Decimal(str(qty_raw))

        usage_unit = (
            it.get("usageUnit") or it.get("usage_unit") or comp.unit or ""
        ).strip()[:32]

        parsed.append(
            {
                "idx": idx,
                "component": comp,
                "component_type": comp_type,
                "requisition": req,
                "actual_qty": actual_qty,
                "usage_unit": usage_unit,
                "remark": (it.get("remark") or "").strip()[:512] or None,
            }
        )
    return parsed


def list_preps(
    db: Session,
    tenant_no: str,
    *,
    page: int = 1,
    page_size: int = 10,
    keyword: Optional[str] = None,
    status: Optional[str] = None,
    configured_material_id: Optional[str] = None,
) -> Tuple[List[Dict[str, Any]], int]:
    q0 = db.query(BizProductionMaterialPrep).filter(
        BizProductionMaterialPrep.tenant_no == tenant_no,
        BizProductionMaterialPrep.del_flag == 0,
    )
    st = (status or "").strip().lower()
    if st in PREP_STATUSES:
        q0 = q0.filter(BizProductionMaterialPrep.status == st)
    cmid = (configured_material_id or "").strip()
    if cmid:
        q0 = q0.filter(BizProductionMaterialPrep.configured_material_id == cmid)
    kw = (keyword or "").strip()
    if kw:
        like = f"%{kw}%"
        q0 = q0.filter(
            or_(
                BizProductionMaterialPrep.prep_no.like(like),
                BizProductionMaterialPrep.configured_material_code.like(like),
                BizProductionMaterialPrep.configured_material_name.like(like),
                BizProductionMaterialPrep.batch_no.like(like),
                BizProductionMaterialPrep.stock_in_no.like(like),
            )
        )
    total = q0.count()
    rows = (
        q0.order_by(BizProductionMaterialPrep.prep_time.desc(), BizProductionMaterialPrep.create_time.desc())
        .offset((page - 1) * page_size)
        .limit(page_size)
        .all()
    )
    prep_ids = [r.id for r in rows]
    line_counts: Dict[str, int] = {}
    if prep_ids:
        cnt_rows = (
            db.query(
                BizProductionMaterialPrepLine.prep_id,
                func.count(BizProductionMaterialPrepLine.id),
            )
            .filter(
                BizProductionMaterialPrepLine.prep_id.in_(prep_ids),
                BizProductionMaterialPrepLine.del_flag == 0,
            )
            .group_by(BizProductionMaterialPrepLine.prep_id)
            .all()
        )
        line_counts = {pid: int(c) for pid, c in cnt_rows}
    name_map = _user_nick_name_map(db, [r.create_by for r in rows if r.create_by])
    items = [
        _header_to_dict(
            r,
            create_by_name=name_map.get(r.create_by or "", ""),
            line_count=line_counts.get(r.id, 0),
        )
        for r in rows
    ]
    return items, total


def get_prep_detail(db: Session, prep_id: str, tenant_no: str) -> Dict[str, Any]:
    row = _get_prep_row(db, prep_id, tenant_no)
    lines = (
        db.query(BizProductionMaterialPrepLine)
        .filter(
            BizProductionMaterialPrepLine.prep_id == prep_id,
            BizProductionMaterialPrepLine.tenant_no == tenant_no,
            BizProductionMaterialPrepLine.del_flag == 0,
        )
        .order_by(
            BizProductionMaterialPrepLine.order_num.asc(),
            BizProductionMaterialPrepLine.create_time.asc(),
        )
        .all()
    )
    name_map = _user_nick_name_map(db, [row.create_by] if row.create_by else [])
    result = _header_to_dict(
        row,
        create_by_name=name_map.get(row.create_by or "", ""),
        line_count=len(lines),
    )
    req_ids = list(
        {(ln.requisition_usage_id or "").strip() for ln in lines if (ln.requisition_usage_id or "").strip()}
    )
    req_map: Dict[str, BizProductionMaterialUsage] = {}
    if req_ids:
        reqs = (
            db.query(BizProductionMaterialUsage)
            .filter(
                BizProductionMaterialUsage.id.in_(req_ids),
                BizProductionMaterialUsage.tenant_no == tenant_no,
                BizProductionMaterialUsage.del_flag == 0,
            )
            .all()
        )
        req_map = {r.id: r for r in reqs}
    req_bmap = _batch_map_for_prep_lines(db, [r.batch_id for r in req_map.values() if r.batch_id])

    enriched_lines: List[Dict[str, Any]] = []
    for ln in lines:
        item = _line_to_dict(ln)
        req = req_map.get((ln.requisition_usage_id or "").strip())
        if req:
            batch = req_bmap.get(req.batch_id or "")
            item["batchNo"] = req.batch_no or ""
            item["stockInNo"] = req.stock_in_no or ""
            item["catalogNo"] = req.catalog_no or ""
            item["manufacturer"] = req.manufacturer or ""
            item["storageLocation"] = (req.storage_location or "").strip() or (
                (batch.storage_location or "") if batch else ""
            )
            item["requisitionQty"] = abs(float(req.change_qty or 0))
            if batch:
                item["specModel"] = batch.spec_model or ""
                item["stockInDate"] = _fmt_date(batch.stock_in_date)
                item["expireDate"] = _fmt_date(batch.expire_date)
        enriched_lines.append(item)
    result["lines"] = enriched_lines

    batch_id = (row.output_batch_id or "").strip()
    if batch_id:
        batch = (
            db.query(BizProductionMaterialStockBatch)
            .filter(
                BizProductionMaterialStockBatch.id == batch_id,
                BizProductionMaterialStockBatch.tenant_no == tenant_no,
                BizProductionMaterialStockBatch.del_flag == 0,
            )
            .first()
        )
        if batch:
            result["specModel"] = batch.spec_model or ""
            result["catalogNo"] = batch.catalog_no or ""
            result["manufacturer"] = batch.manufacturer or ""
            result["stockInDate"] = _fmt_date(batch.stock_in_date)
            result["unitPrice"] = None
            result["amount"] = None

    return result


def create_prep(
    db: Session,
    *,
    data: Dict[str, Any],
    tenant_no: str,
    user_id: Optional[str] = None,
    dept_id: str = "",
) -> Dict[str, Any]:
    """创建配制单（草稿）：用于旧版流程。"""
    configured_id = (
        data.get("configuredMaterialId") or data.get("configured_material_id") or ""
    ).strip()
    if not configured_id:
        raise ValueError("请选择配置试剂")

    configured = (
        db.query(BizProductionMaterial)
        .filter(
            BizProductionMaterial.id == configured_id,
            BizProductionMaterial.tenant_no == tenant_no,
            BizProductionMaterial.del_flag == 0,
        )
        .first()
    )
    if not configured:
        raise ValueError("配置试剂不存在")
    if (configured.material_type or "").strip() != "configured_reagent":
        raise ValueError("所选物料不是配置试剂")

    prep_time_raw = data.get("prepTime") or data.get("prep_time")
    prep_time = parse_datetime(prep_time_raw) if prep_time_raw else datetime.now()

    output_qty_raw = data.get("outputQty") if data.get("outputQty") is not None else data.get("output_qty")
    if output_qty_raw is None or Decimal(str(output_qty_raw)) <= 0:
        raise ValueError("产出数量必须大于0")
    output_qty = Decimal(str(output_qty_raw))

    batch_no = _require_str(data.get("batchNo") or data.get("batch_no"), "产出批号")
    expire_raw = data.get("expireDate") or data.get("expire_date")
    expire_date = _parse_expire_date(expire_raw)

    lines_raw = data.get("lines") or []
    parsed_lines = _parse_prep_lines(db, lines_raw, tenant_no=tenant_no)

    now = datetime.now()
    prep_id = generate_uuid()
    prep_no = _generate_prep_no(db, tenant_no)

    prep = BizProductionMaterialPrep(
        id=prep_id,
        prep_no=prep_no,
        configured_material_id=configured_id,
        configured_material_code=configured.material_code,
        configured_material_name=configured.material_name,
        prep_time=prep_time,
        output_qty=output_qty,
        output_unit=(data.get("outputUnit") or data.get("output_unit") or configured.unit or "").strip()[:32] or None,
        batch_no=batch_no,
        storage_location=_optional_str(data.get("storageLocation") or data.get("storage_location")),
        expire_date=expire_date,
        status="draft",
        tenant_no=tenant_no,
        remark=(data.get("remark") or "").strip()[:512] or None,
        create_by=user_id,
        create_dept_id=dept_id or configured.create_dept_id or "",
        create_time=now,
        update_by=user_id,
        update_time=now,
        del_flag=0,
    )
    db.add(prep)

    for pl in parsed_lines:
        comp = pl["component"]
        db.add(
            BizProductionMaterialPrepLine(
                id=generate_uuid(),
                prep_id=prep_id,
                component_material_id=comp.id,
                component_material_type=pl["component_type"],
                component_material_code=comp.material_code,
                component_material_name=comp.material_name,
                requisition_usage_id=pl["requisition"].id,
                actual_qty=pl["actual_qty"],
                usage_unit=pl["usage_unit"] or None,
                tenant_no=tenant_no,
                order_num=pl["idx"],
                remark=pl["remark"],
                create_by=user_id,
                create_dept_id=dept_id or configured.create_dept_id or "",
                create_time=now,
                update_by=user_id,
                update_time=now,
                del_flag=0,
            )
        )

    db.commit()
    return get_prep_detail(db, prep_id, tenant_no)


def create_quick_prep(
    db: Session,
    *,
    data: Dict[str, Any],
    tenant_no: str,
    user_id: Optional[str] = None,
    dept_id: str = "",
) -> Dict[str, Any]:
    """一次性配制：创建即完成（扣减子物料 → 入库配置试剂 → 自动出库给操作人）。"""
    configured_name = (data.get("configuredMaterialName") or data.get("configured_material_name") or "").strip()
    if not configured_name:
        raise ValueError("请输入配置试剂名称")

    output_qty_raw = data.get("outputQty") if data.get("outputQty") is not None else data.get("output_qty")
    if output_qty_raw is None or Decimal(str(output_qty_raw)) <= 0:
        raise ValueError("产出数量必须大于0")
    output_qty = Decimal(str(output_qty_raw))

    lines_raw = data.get("lines") or []
    if not lines_raw:
        raise ValueError("请至少添加一条子物料")

    # 校验并解析子物料
    parsed_lines = _parse_quick_prep_lines(db, lines_raw, tenant_no=tenant_no)

    now = datetime.now()
    prep_id = generate_uuid()
    prep_no = _generate_prep_no(db, tenant_no)

    stock_in_no_raw = (data.get("stockInNo") or data.get("stock_in_no") or "").strip()
    if stock_in_no_raw:
        if stock_in_no_exists(db, stock_in_no_raw, tenant_no):
            raise ValueError("入库编号已存在")
        stock_in_no = stock_in_no_raw
    else:
        stock_in_no = _generate_stock_in_no(db, tenant_no)

    # 配置试剂为自配物料，不核算单价与金额（库表 NOT NULL，存 0；接口层按类型屏蔽展示）
    unit_price_out = Decimal("0")
    amount_out = Decimal("0")

    spec_model = _optional_str(data.get("specModel") or data.get("spec_model"))
    output_unit = _optional_str(data.get("outputUnit") or data.get("output_unit")) or "瓶"

    # 自动创建或获取配置试剂物料
    configured_material = _get_or_create_configured_material(
        db,
        configured_name,
        tenant_no,
        user_id,
        dept_id,
        spec_model=spec_model,
        unit=output_unit,
    )

    output_unit = output_unit or configured_material.unit or "瓶"

    # 生成批号
    batch_no_raw = (data.get("batchNo") or data.get("batch_no") or "").strip()
    batch_no = batch_no_raw if batch_no_raw else prep_no

    catalog_no_raw = (data.get("catalogNo") or data.get("catalog_no") or "").strip()
    catalog_no = catalog_no_raw if catalog_no_raw else (configured_material.material_code or batch_no or "—")

    manufacturer = (data.get("manufacturer") or "").strip() or "自配"

    # 入库日期（业务日期，仅日期）与变动记录时间（含时分秒）分离，对齐普通入库逻辑
    stock_in_date_raw = data.get("stockInDate") or data.get("stock_in_date")
    stock_in_date = _parse_date(stock_in_date_raw) if stock_in_date_raw else now.date()

    prep_time_raw = (
        data.get("prepTime")
        or data.get("prep_time")
        or data.get("stockInTime")
        or data.get("stock_in_time")
    )
    operate_time = parse_datetime(prep_time_raw) if prep_time_raw else now

    try:
        # 1. 创建配制单（状态直接为 completed）
        expire_date = _parse_expire_date(data.get("expireDate") or data.get("expire_date") or "")
        if not expire_date:
            expire_date = datetime(now.year + 1, now.month, now.day).date()

        prep = BizProductionMaterialPrep(
            id=prep_id,
            prep_no=prep_no,
            configured_material_id=configured_material.id,
            configured_material_code=configured_material.material_code,
            configured_material_name=configured_name,
            prep_time=operate_time,
            output_qty=output_qty,
            output_unit=output_unit,
            batch_no=batch_no,
            storage_location=_optional_str(data.get("storageLocation") or data.get("storage_location")),
            expire_date=expire_date,
            stock_in_no=stock_in_no,
            status="completed",
            tenant_no=tenant_no,
            remark=(data.get("remark") or "").strip()[:512] or None,
            create_by=user_id,
            create_dept_id=dept_id or configured_material.create_dept_id or "",
            create_time=now,
            update_by=user_id,
            update_time=now,
            del_flag=0,
        )
        db.add(prep)

        # 2. 创建配制明细并扣减子物料
        for pl in parsed_lines:
            comp = pl["component"]
            req = pl["requisition"]
            actual_qty = pl["actual_qty"]

            # 创建配制明细行
            db.add(
                BizProductionMaterialPrepLine(
                    id=generate_uuid(),
                    prep_id=prep_id,
                    component_material_id=comp.id,
                    component_material_type=pl["component_type"],
                    component_material_code=comp.material_code,
                    component_material_name=comp.material_name,
                    requisition_usage_id=req.id if req else None,
                    actual_qty=actual_qty,
                    usage_unit=pl["usage_unit"] or None,
                    tenant_no=tenant_no,
                    order_num=pl["idx"],
                    remark=pl.get("remark"),
                    create_by=user_id,
                    create_dept_id=dept_id or configured_material.create_dept_id or "",
                    create_time=now,
                    update_by=user_id,
                    update_time=now,
                    del_flag=0,
                )
            )

            # 扣减子物料库存（写入 prep_consume 记录）
            _write_quick_prep_consume_usage(
                db,
                prep=prep,
                material=comp,
                requisition=req,
                actual_qty=actual_qty,
                usage_unit=pl["usage_unit"] or None,
                tenant_no=tenant_no,
                user_id=user_id,
                dept_id=dept_id or configured_material.create_dept_id or "",
                operate_time=operate_time,
            )

        # 3. 创建配置试剂入库批次
        catalog_no = catalog_no if catalog_no else (configured_material.material_code or batch_no or "—")

        batch = _create_batch_and_usage(
            db,
            material=configured_material,
            stock_in_no=stock_in_no,
            stock_in_date=stock_in_date,
            expire_date=expire_date,
            catalog_no=catalog_no,
            batch_no=batch_no,
            manufacturer=manufacturer,
            storage_location=_optional_str(data.get("storageLocation") or data.get("storage_location")),
            unit_price=unit_price_out,
            amount=amount_out,
            qty=output_qty,
            remark=f"配制单 {prep_no}",
            tenant_no=tenant_no,
            user_id=user_id,
            operate_time=operate_time,
            commit=False,
            source_type="prep_in",
            prep_id=prep.id,
        )

        prep.output_batch_id = batch.id
        db.add(prep)

        # 4. 自动出库给当前操作人
        _create_auto_stock_out(
            db,
            batch=batch,
            output_qty=output_qty,
            tenant_no=tenant_no,
            user_id=user_id,
            dept_id=dept_id or configured_material.create_dept_id or "",
            operate_time=operate_time,
        )

        db.commit()
    except Exception:
        db.rollback()
        raise

    return get_prep_detail(db, prep_id, tenant_no)


def _parse_quick_prep_lines(
    db: Session,
    lines: List[Dict[str, Any]],
    *,
    tenant_no: str,
) -> List[Dict[str, Any]]:
    """解析一次性配制的子物料（按领用记录）。"""
    if not lines:
        raise ValueError("请至少添加一条子物料")
    parsed: List[Dict[str, Any]] = []
    seen_req: set = set()

    for idx, it in enumerate(lines):
        req_id = (
            it.get("requisitionUsageId")
            or it.get("requisition_usage_id")
            or ""
        ).strip()
        if not req_id:
            raise ValueError("请选择领用记录")

        comp_id = (it.get("componentMaterialId") or it.get("component_material_id") or "").strip()
        if not comp_id:
            raise ValueError("子物料不能为空")

        comp_type = (it.get("componentMaterialType") or it.get("component_material_type") or "").strip().lower()
        if comp_type not in ("reagent", "consumable"):
            raise ValueError("子物料类型无效")

        qty_raw = it.get("consumptionQty") if it.get("consumptionQty") is not None else it.get("consumption_qty")
        if qty_raw is None or Decimal(str(qty_raw)) <= 0:
            raise ValueError("消耗数量必须大于0")
        consumption_qty = Decimal(str(qty_raw))

        if req_id in seen_req:
            raise ValueError("同一领用记录不可重复使用")
        seen_req.add(req_id)

        req = (
            db.query(BizProductionMaterialUsage)
            .filter(
                BizProductionMaterialUsage.id == req_id,
                BizProductionMaterialUsage.tenant_no == tenant_no,
                BizProductionMaterialUsage.del_flag == 0,
                BizProductionMaterialUsage.source_type == "manual_out",
                BizProductionMaterialUsage.change_type == "use",
            )
            .first()
        )
        if not req:
            raise ValueError("领用记录不存在或已失效")
        if (req.requisition_status or "open") == "closed":
            raise ValueError("领用记录已标记为使用完，不可用于配制")

        comp = (
            db.query(BizProductionMaterial)
            .filter(
                BizProductionMaterial.id == comp_id,
                BizProductionMaterial.tenant_no == tenant_no,
                BizProductionMaterial.del_flag == 0,
            )
            .first()
        )
        if not comp:
            raise ValueError("子物料不存在")
        if (req.material_id or "").strip() != comp.id:
            raise ValueError("领用记录与子物料不匹配")

        req_qty = abs(req.change_qty or Decimal("0"))
        if consumption_qty > req_qty:
            raise ValueError(
                f"子物料「{comp.material_name}」消耗数量不能超过领用数量 {req_qty}"
            )

        batch = None
        if req.batch_id:
            batch = (
                db.query(BizProductionMaterialStockBatch)
                .filter(
                    BizProductionMaterialStockBatch.id == req.batch_id,
                    BizProductionMaterialStockBatch.tenant_no == tenant_no,
                    BizProductionMaterialStockBatch.del_flag == 0,
                )
                .first()
            )

        usage_unit = (it.get("usageUnit") or it.get("usage_unit") or req.usage_unit or comp.unit or "").strip()[:32]

        parsed.append({
            "idx": idx,
            "component": comp,
            "component_type": comp_type,
            "batch": batch,
            "requisition": req,
            "actual_qty": consumption_qty,
            "usage_unit": usage_unit,
            "remark": (it.get("remark") or "").strip()[:512] or None,
        })
    return parsed


def _get_or_create_configured_material(
    db: Session,
    name: str,
    tenant_no: str,
    user_id: Optional[str],
    dept_id: str,
    *,
    spec_model: Optional[str] = None,
    unit: Optional[str] = None,
) -> BizProductionMaterial:
    """获取或创建配置试剂物料（按名称匹配）。"""
    # 先查找是否存在
    existing = (
        db.query(BizProductionMaterial)
        .filter(
            BizProductionMaterial.material_name == name,
            BizProductionMaterial.material_type == "configured_reagent",
            BizProductionMaterial.tenant_no == tenant_no,
            BizProductionMaterial.del_flag == 0,
        )
        .first()
    )
    if existing:
        if not _is_enabled_status(existing.status):
            raise ValueError(f"配置试剂「{name}」已停用，请启用后再配制")
        return existing

    # 自动生成编码
    day = datetime.now().strftime("%Y%m%d")
    like = f"PZR-{day}-%"
    cnt = (
        db.query(func.count(BizProductionMaterial.id))
        .filter(
            BizProductionMaterial.tenant_no == tenant_no,
            BizProductionMaterial.material_code.like(like),
            BizProductionMaterial.del_flag == 0,
        )
        .scalar()
    )
    code = f"PZR-{day}-{(cnt or 0) + 1:03d}"

    now = datetime.now()
    mat = BizProductionMaterial(
        id=generate_uuid(),
        material_type="configured_reagent",
        material_code=code,
        material_name=name,
        spec_model=spec_model,
        unit=unit or "瓶",
        stock_qty=Decimal("0"),
        warning_qty=Decimal("0"),
        status="1",
        tenant_no=tenant_no,
        create_by=user_id,
        create_dept_id=dept_id,
        create_time=now,
        update_by=user_id,
        update_time=now,
        del_flag=0,
    )
    db.add(mat)
    return mat


def _write_quick_prep_consume_usage(
    db: Session,
    *,
    prep: BizProductionMaterialPrep,
    material: BizProductionMaterial,
    requisition: Optional[BizProductionMaterialUsage],
    actual_qty: Decimal,
    usage_unit: Optional[str],
    tenant_no: str,
    user_id: Optional[str],
    dept_id: str,
    operate_time: datetime,
) -> None:
    """写入子物料消耗记录（关联领用记录，不重复扣减批次库存）。"""
    if not requisition:
        raise ValueError("子物料必须关联领用记录")

    stock_qty = material.stock_qty if material.stock_qty is not None else Decimal("0")

    unit_price = requisition.unit_price if requisition else None
    line_amount = Decimal("0")
    if unit_price is not None and actual_qty > 0:
        line_amount = (Decimal(str(unit_price)) * actual_qty).quantize(Decimal("0.01"))

    usage = BizProductionMaterialUsage(
        id=generate_uuid(),
        material_id=material.id,
        material_type=material.material_type,
        material_code=material.material_code,
        material_name=material.material_name,
        batch_id=requisition.batch_id if requisition else None,
        stock_in_no=requisition.stock_in_no if requisition else None,
        catalog_no=requisition.catalog_no if requisition else None,
        batch_no=requisition.batch_no if requisition else None,
        manufacturer=requisition.manufacturer if requisition else None,
        storage_location=requisition.storage_location if requisition else None,
        unit_price=unit_price,
        amount=line_amount if line_amount > 0 else (requisition.amount if requisition else None),
        change_type="use",
        change_qty=-actual_qty,
        usage_unit=usage_unit,
        stock_before=stock_qty,
        stock_after=stock_qty,  # 汇总库存暂不扣减，按批次扣减
        parent_usage_id=requisition.id if requisition else None,
        prep_id=prep.id,
        source_type="prep_consume",
        tenant_no=tenant_no,
        remark=f"配制单 {prep.prep_no}",
        create_by=user_id,
        create_dept_id=dept_id,
        create_time=operate_time,
        update_by=user_id,
        update_time=operate_time,
        del_flag=0,
    )
    db.add(usage)


def _create_auto_stock_out(
    db: Session,
    *,
    batch,
    output_qty: Decimal,
    tenant_no: str,
    user_id: Optional[str],
    dept_id: str,
    operate_time: datetime,
) -> None:
    """自动出库配置试剂给当前操作人（写入领用记录）。"""
    if not user_id:
        return

    _stock_out_batch(
        db,
        batch.id,
        qty=output_qty,
        receiver=user_id,
        purpose="配制自用",
        remark="配制产出自动出库",
        usage_unit=batch.unit,
        tenant_no=tenant_no,
        user_id=user_id,
        operate_time=operate_time,
    )


def update_prep(
    db: Session,
    prep_id: str,
    *,
    data: Dict[str, Any],
    tenant_no: str,
    user_id: Optional[str] = None,
    dept_id: str = "",
) -> Dict[str, Any]:
    prep = _get_prep_row(db, prep_id, tenant_no)
    _require_draft(prep)
    now = datetime.now()

    if data.get("prepTime") or data.get("prep_time"):
        prep.prep_time = parse_datetime(data.get("prepTime") or data.get("prep_time"))

    if data.get("outputQty") is not None or data.get("output_qty") is not None:
        output_qty_raw = data.get("outputQty") if data.get("outputQty") is not None else data.get("output_qty")
        if Decimal(str(output_qty_raw)) <= 0:
            raise ValueError("产出数量必须大于0")
        prep.output_qty = Decimal(str(output_qty_raw))

    if data.get("outputUnit") is not None or data.get("output_unit") is not None:
        prep.output_unit = (
            data.get("outputUnit") or data.get("output_unit") or ""
        ).strip()[:32] or None

    if data.get("batchNo") or data.get("batch_no"):
        prep.batch_no = _require_str(data.get("batchNo") or data.get("batch_no"), "产出批号")

    if data.get("storageLocation") is not None or data.get("storage_location") is not None:
        prep.storage_location = _optional_str(
            data.get("storageLocation") if data.get("storageLocation") is not None else data.get("storage_location")
        )

    if data.get("expireDate") or data.get("expire_date"):
        prep.expire_date = _parse_expire_date(data.get("expireDate") or data.get("expire_date"))

    if data.get("remark") is not None:
        prep.remark = (data.get("remark") or "").strip()[:512] or None

    lines_raw = data.get("lines")
    if lines_raw is not None:
        parsed_lines = _parse_prep_lines(db, lines_raw, tenant_no=tenant_no)
        existing = (
            db.query(BizProductionMaterialPrepLine)
            .filter(
                BizProductionMaterialPrepLine.prep_id == prep_id,
                BizProductionMaterialPrepLine.del_flag == 0,
            )
            .all()
        )
        for ln in existing:
            ln.del_flag = 1
            ln.update_by = user_id
            ln.update_time = now
            db.add(ln)
        for pl in parsed_lines:
            comp = pl["component"]
            db.add(
                BizProductionMaterialPrepLine(
                    id=generate_uuid(),
                    prep_id=prep_id,
                    component_material_id=comp.id,
                    component_material_type=pl["component_type"],
                    component_material_code=comp.material_code,
                    component_material_name=comp.material_name,
                    requisition_usage_id=pl["requisition"].id,
                    actual_qty=pl["actual_qty"],
                    usage_unit=pl["usage_unit"] or None,
                    tenant_no=tenant_no,
                    order_num=pl["idx"],
                    remark=pl["remark"],
                    create_by=user_id,
                    create_dept_id=dept_id or prep.create_dept_id or "",
                    create_time=now,
                    update_by=user_id,
                    update_time=now,
                    del_flag=0,
                )
            )

    prep.update_by = user_id
    prep.update_time = now
    db.add(prep)
    db.commit()
    return get_prep_detail(db, prep_id, tenant_no)


def _write_prep_consume_usage(
    db: Session,
    *,
    prep: BizProductionMaterialPrep,
    line: BizProductionMaterialPrepLine,
    req: BizProductionMaterialUsage,
    tenant_no: str,
    user_id: Optional[str],
    dept_id: str,
    operate_time: datetime,
) -> Decimal:
    material = (
        db.query(BizProductionMaterial)
        .filter(
            BizProductionMaterial.id == line.component_material_id,
            BizProductionMaterial.tenant_no == tenant_no,
            BizProductionMaterial.del_flag == 0,
        )
        .first()
    )
    if not material:
        raise ValueError("子物料不存在")

    actual_qty = line.actual_qty or Decimal("0")
    stock_qty = material.stock_qty if material.stock_qty is not None else Decimal("0")

    unit_price = req.unit_price
    line_amount = Decimal("0")
    if unit_price is not None and actual_qty > 0:
        line_amount = (Decimal(str(unit_price)) * actual_qty).quantize(Decimal("0.01"))

    usage = BizProductionMaterialUsage(
        id=generate_uuid(),
        material_id=material.id,
        material_type=material.material_type,
        material_code=material.material_code,
        material_name=material.material_name,
        batch_id=req.batch_id,
        stock_in_no=req.stock_in_no,
        catalog_no=req.catalog_no,
        batch_no=req.batch_no,
        manufacturer=req.manufacturer,
        storage_location=req.storage_location,
        unit_price=req.unit_price,
        amount=line_amount if line_amount > 0 else req.amount,
        change_type="use",
        change_qty=-actual_qty,
        usage_unit=line.usage_unit,
        stock_before=stock_qty,
        stock_after=stock_qty,
        parent_usage_id=req.id,
        prep_id=prep.id,
        source_type="prep_consume",
        tenant_no=tenant_no,
        remark=f"配制单 {prep.prep_no}",
        create_by=user_id,
        create_dept_id=dept_id,
        create_time=operate_time,
        update_by=user_id,
        update_time=operate_time,
        del_flag=0,
    )
    db.add(usage)
    return line_amount


def complete_prep(
    db: Session,
    prep_id: str,
    *,
    tenant_no: str,
    user_id: Optional[str] = None,
    dept_id: str = "",
) -> Dict[str, Any]:
    prep = _get_prep_row(db, prep_id, tenant_no)
    if (prep.status or "").strip() != "draft":
        raise ValueError("仅草稿状态的配制单可确认配制")

    lines = (
        db.query(BizProductionMaterialPrepLine)
        .filter(
            BizProductionMaterialPrepLine.prep_id == prep_id,
            BizProductionMaterialPrepLine.del_flag == 0,
        )
        .order_by(BizProductionMaterialPrepLine.order_num.asc())
        .all()
    )
    if not lines:
        raise ValueError("配制单无明细，无法确认")

    configured = _get_material_for_update(db, prep.configured_material_id, tenant_no)
    if (configured.material_type or "").strip() != "configured_reagent":
        raise ValueError("配置试剂目录无效")
    if not _is_enabled_status(configured.status):
        raise ValueError(f"配置试剂「{configured.material_name}」已停用")

    operate_time = datetime.now()
    total_amount = Decimal("0")

    try:
        req_map: Dict[str, BizProductionMaterialUsage] = {}
        for line in lines:
            req_id = (line.requisition_usage_id or "").strip()
            if not req_id:
                raise ValueError("配制明细缺少领用记录")
            if req_id not in req_map:
                req = (
                    db.query(BizProductionMaterialUsage)
                    .filter(
                        BizProductionMaterialUsage.id == req_id,
                        BizProductionMaterialUsage.tenant_no == tenant_no,
                        BizProductionMaterialUsage.del_flag == 0,
                        BizProductionMaterialUsage.source_type == "manual_out",
                    )
                    .first()
                )
                if not req:
                    raise ValueError("领用记录不存在或已失效")
                req_map[req_id] = req
            amt = _write_prep_consume_usage(
                db,
                prep=prep,
                line=line,
                req=req_map[req_id],
                tenant_no=tenant_no,
                user_id=user_id,
                dept_id=dept_id or prep.create_dept_id or "",
                operate_time=operate_time,
            )
            total_amount += amt

        stock_in_no = _generate_stock_in_no(db, tenant_no)
        output_qty = prep.output_qty or Decimal("0")
        if output_qty <= 0:
            raise ValueError("产出数量必须大于0")

        unit_price = Decimal("0")
        amount = total_amount
        if output_qty > 0 and total_amount > 0:
            unit_price = (total_amount / output_qty).quantize(Decimal("0.01"))
            amount = (unit_price * output_qty).quantize(Decimal("0.01"))

        stock_in_date = prep.prep_time.date() if prep.prep_time else operate_time.date()
        catalog_no = (configured.material_code or prep.batch_no or "—").strip() or "—"

        batch = _create_batch_and_usage(
            db,
            material=configured,
            stock_in_no=stock_in_no,
            stock_in_date=stock_in_date,
            expire_date=prep.expire_date,
            catalog_no=catalog_no,
            batch_no=prep.batch_no,
            manufacturer="自配",
            storage_location=prep.storage_location,
            unit_price=unit_price,
            amount=amount,
            qty=output_qty,
            remark=f"配制单 {prep.prep_no}",
            tenant_no=tenant_no,
            user_id=user_id,
            operate_time=operate_time,
            commit=False,
            source_type="prep_in",
            prep_id=prep.id,
        )

        prep.status = "completed"
        prep.stock_in_no = stock_in_no
        prep.output_batch_id = batch.id
        prep.update_by = user_id
        prep.update_time = operate_time
        db.add(prep)

        db.commit()
    except Exception:
        db.rollback()
        raise

    return get_prep_detail(db, prep_id, tenant_no)


def cancel_prep(
    db: Session,
    prep_id: str,
    *,
    tenant_no: str,
    user_id: Optional[str] = None,
) -> None:
    prep = _get_prep_row(db, prep_id, tenant_no)
    _require_draft(prep)
    now = datetime.now()
    prep.status = "cancelled"
    prep.update_by = user_id
    prep.update_time = now
    db.add(prep)
    db.commit()


def delete_prep(
    db: Session,
    prep_id: str,
    *,
    tenant_no: str,
    user_id: Optional[str] = None,
) -> None:
    prep = _get_prep_row(db, prep_id, tenant_no)
    _require_draft(prep)
    now = datetime.now()
    prep.del_flag = 1
    prep.update_by = user_id
    prep.update_time = now
    db.add(prep)

    lines = (
        db.query(BizProductionMaterialPrepLine)
        .filter(
            BizProductionMaterialPrepLine.prep_id == prep_id,
            BizProductionMaterialPrepLine.del_flag == 0,
        )
        .all()
    )
    for ln in lines:
        ln.del_flag = 1
        ln.update_by = user_id
        ln.update_time = now
        db.add(ln)

    db.commit()


def list_available_material_batches(
    db: Session,
    tenant_no: str,
    *,
    page: int = 1,
    page_size: int = 50,
    keyword: Optional[str] = None,
    material_type: Optional[str] = None,
    user_info=None,
) -> Tuple[List[Dict[str, Any]], int]:
    """查询可用于配制的领用记录（有剩余、未标记已用完）。"""
    return list_available_requisitions_for_prep(
        db,
        tenant_no,
        page=page,
        page_size=page_size,
        keyword=keyword,
        material_type=material_type,
        user_info=user_info,
    )