dibang-xiaoshou/backend/app/api/routes/inventory.py

from __future__ import annotations

import re
from datetime import datetime

from fastapi import APIRouter, Depends, HTTPException, Query, status
from sqlalchemy import func
from sqlmodel import select
from sqlmodel.ext.asyncio.session import AsyncSession

from app.db.session import get_async_session
from app.models.inventory import InboundOutbound
from app.models.master_data import Vehicle
from app.models.weighing import WeighingSlip
from app.models.plans import PurchasePlan
from app.schemas.inventory import (
    InboundOutboundCreate,
    InboundOutboundRead,
    InboundOutboundUpdate,
)
from app.schemas.plans import PurchasePlanRead
from app.schemas.weighing import WeighingSlipRead
from pydantic import BaseModel, Field

router = APIRouter(prefix="/inventory", tags=["inventory"])


def _base_query():
    return select(InboundOutbound).where(InboundOutbound.is_deleted == 0)


async def _generate_batch_no(session: AsyncSession) -> str:
    """
    生成出入库单号，格式为 CRKDH+0000001 这样的递增序号
    """
    # 查询所有以 CRKDH 开头的 batch_no
    result = await session.exec(
        select(InboundOutbound.batch_no)
        .where(
            InboundOutbound.batch_no.like("CRKDH%"),
            InboundOutbound.is_deleted == 0
        )
    )
    batch_nos = result.all()
    
    # 提取所有序号
    max_seq = 0
    pattern = re.compile(r"^CRKDH(\d+)$")
    for batch_no in batch_nos:
        if batch_no:
            match = pattern.match(batch_no)
            if match:
                seq = int(match.group(1))
                if seq > max_seq:
                    max_seq = seq
    
    # 生成新的序号（递增1）
    new_seq = max_seq + 1
    return f"CRKDH{new_seq:07d}"


@router.get("/", response_model=list[InboundOutboundRead])
async def list_inventory(
    q: str | None = Query(default=None, description="模糊搜索往来单位或产品"),
    # 前端使用的是 dateFrom / dateTo，这里通过 alias 做映射
    date_from: str | None = Query(default=None, alias="dateFrom", description="开始日期"),
    date_to: str | None = Query(default=None, alias="dateTo", description="结束日期"),
    status_filter: str | None = Query(default=None, alias="status", description="状态筛选"),
    business_type: str | None = Query(default=None, alias="businessType", description="业务类型"),
    session: AsyncSession = Depends(get_async_session),
) -> list[InboundOutbound]:
    query = _base_query().order_by(InboundOutbound.date.desc(), InboundOutbound.id.desc())

    if q:
        query = query.where(
            (func.lower(InboundOutbound.partner_company).contains(q.lower()))
            | (func.lower(InboundOutbound.product).contains(q.lower()))
        )

    if date_from:
        query = query.where(InboundOutbound.date >= date_from)

    if date_to:
        query = query.where(InboundOutbound.date <= date_to)

    if status_filter:
        query = query.where(InboundOutbound.status == status_filter)

    if business_type:
        query = query.where(InboundOutbound.business_type == business_type)

    result = await session.exec(query)
    inventories = result.all()

    # 批量查询所有出入库单关联的地磅单（避免 N+1）
    inventory_ids = [inv.id for inv in inventories if inv.id is not None]
    weighing_map: dict[int, WeighingSlip] = {}
    if inventory_ids:
        weighing_result = await session.exec(
            select(WeighingSlip).where(
                WeighingSlip.inbound_outbound_id.in_(inventory_ids),
                WeighingSlip.is_voided == 0,
            ).order_by(WeighingSlip.id.asc())
        )
        for w in weighing_result.all():
            if w.inbound_outbound_id is not None and w.inbound_outbound_id not in weighing_map:
                weighing_map[w.inbound_outbound_id] = w

    result_list = []
    for inventory in inventories:
        weighing = weighing_map.get(inventory.id) if inventory.id else None
        inventory_dict = inventory.model_dump()
        if weighing:
            inventory_dict['gross_weight'] = weighing.gross_weight if weighing.gross_weight is not None else None
            inventory_dict['tare_weight'] = weighing.tare_weight if weighing.tare_weight is not None else None
            inventory_dict['deduction'] = weighing.deduction if weighing.deduction is not None else None
            inventory_dict['net_weight'] = weighing.net_weight if weighing.net_weight is not None else None
            inventory_dict['gross_weight_time'] = weighing.gross_weight_time if weighing.gross_weight_time and weighing.gross_weight_time.strip() else None
            inventory_dict['tare_weight_time'] = weighing.tare_weight_time if weighing.tare_weight_time and weighing.tare_weight_time.strip() else None
            inventory_dict['has_weighing_slip'] = True
        else:
            inventory_dict['gross_weight'] = None
            inventory_dict['tare_weight'] = None
            inventory_dict['deduction'] = None
            inventory_dict['net_weight'] = None
            inventory_dict['gross_weight_time'] = None
            inventory_dict['tare_weight_time'] = None
            inventory_dict['has_weighing_slip'] = False
        try:
            result_list.append(InboundOutboundRead.model_validate(inventory_dict))
        except Exception:
            # 单条数据校验失败不影响整表返回，跳过该条
            continue
    return result_list


@router.post("/", response_model=InboundOutboundRead, status_code=status.HTTP_201_CREATED)
async def create_inventory(
    payload: InboundOutboundCreate,
    session: AsyncSession = Depends(get_async_session),
) -> InboundOutbound:
    # 如果没有提供 batch_no，自动生成
    batch_no = payload.batch_no.strip() if payload.batch_no else None
    if not batch_no:
        batch_no = await _generate_batch_no(session)
    
    # 确保 status 有合理的默认值：如果前端未传入，则根据业务类型设置为待入库或待出库
    status_value = (
        payload.status.strip()
        if getattr(payload, "status", None) and str(payload.status).strip()
        else ("待入库" if payload.business_type == "入库" else "待出库")
    )

    inventory = InboundOutbound(
        date=payload.date,
        partner_company=payload.partner_company.strip(),
        business_type=payload.business_type,
        product=payload.product.strip(),
        quantity=payload.quantity,
        plan_no=payload.plan_no.strip() if payload.plan_no else None,
        batch_no=batch_no,
        vehicle_no=payload.vehicle_no.strip(),
        status=status_value,
        remarks=payload.remarks,
        purchase_plan_id=payload.purchase_plan_id,
        is_deleted=0,
        created_at=datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
    )
    session.add(inventory)
    await session.commit()
    await session.refresh(inventory)
    return inventory


class CreateLinkForPlanPayload(BaseModel):
    purchase_plan_id: int = Field(alias="purchasePlanId")
    remarks: str | None = None

    model_config = {"populate_by_name": True}


def _status_third_party_fill() -> str:
    """三方填写待完善状态的 status 值"""
    return "待三方填写"


# 必须放在 /{id} 之前，否则 create-link-for-plan 会被匹配成 id 导致 405
@router.post("/create-link-for-plan", response_model=InboundOutboundRead, status_code=status.HTTP_201_CREATED)
async def create_link_for_plan(
    payload: CreateLinkForPlanPayload,
    session: AsyncSession = Depends(get_async_session),
) -> InboundOutbound:
    """根据计划预生成出入库单（日期、数量、车牌号为空），供三方通过链接填写。同一计划可多次点击，每次生成一条新出入库单。"""
    plan_result = await session.exec(
        select(PurchasePlan).where(
            PurchasePlan.id == payload.purchase_plan_id,
            PurchasePlan.is_deleted == 0,
        )
    )
    plan = plan_result.one_or_none()
    if not plan:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Plan not found",
        )

    batch_no = await _generate_batch_no(session)
    inventory = InboundOutbound(
        date="",
        partner_company=(plan.customer_name or "").strip() or "待填写",
        business_type=plan.business_type,
        product=(plan.product or "").strip(),
        quantity=0.0,
        plan_no=(plan.plan_no or "").strip() or None,
        batch_no=batch_no,
        vehicle_no="",
        status=_status_third_party_fill(),
        remarks=payload.remarks,
        purchase_plan_id=plan.id,
        is_deleted=0,
        created_at=datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
    )
    session.add(inventory)
    await session.commit()
    await session.refresh(inventory)
    return inventory


@router.get("/{id}", response_model=InboundOutboundRead)
async def get_inventory(
    id: int,
    session: AsyncSession = Depends(get_async_session),
) -> InboundOutbound:
    return await _get_inventory(id, session)


async def _ensure_vehicle_exists(vehicle_no: str, session: AsyncSession) -> None:
    if not (vehicle_no and vehicle_no.strip()):
        return
    from sqlalchemy import func
    v = vehicle_no.strip()
    result = await session.exec(
        select(Vehicle).where(
            Vehicle.is_deleted == 0,
            func.lower(Vehicle.vehicle_no) == v.lower(),
        ).limit(1)
    )
    if result.first():
        return
    from datetime import datetime as dt
    vehicle = Vehicle(
        vehicle_no=v,
        remarks=None,
        is_deleted=0,
        created_at=dt.now().isoformat(),
    )
    session.add(vehicle)


@router.patch("/{id}", response_model=InboundOutboundRead)
async def update_inventory(
    id: int,
    payload: InboundOutboundUpdate,
    session: AsyncSession = Depends(get_async_session),
) -> InboundOutbound:
    inventory = await _get_inventory(id, session)
    payload_dict = payload.model_dump(exclude_unset=True)

    vehicle_no_updated = payload_dict.get("vehicle_no")
    if vehicle_no_updated is not None and str(vehicle_no_updated).strip():
        await _ensure_vehicle_exists(str(vehicle_no_updated).strip(), session)

    for key, value in payload_dict.items():
        if isinstance(value, str):
            value = value.strip()
        setattr(inventory, key, value)

    # 三方填写链接保存后：若当前为「待三方填写」且日期、数量、车牌号均已填写，则改为待入库/待出库
    if inventory.status == _status_third_party_fill():
        has_date = bool(inventory.date and inventory.date.strip())
        has_qty = inventory.quantity is not None and inventory.quantity != 0
        has_vehicle = bool(inventory.vehicle_no and inventory.vehicle_no.strip())
        if has_date and has_qty and has_vehicle:
            inventory.status = "待入库" if inventory.business_type == "入库" else "待出库"

    await session.commit()
    await session.refresh(inventory)
    return inventory


@router.delete("/{id}", status_code=status.HTTP_204_NO_CONTENT)
async def delete_inventory(
    id: int,
    session: AsyncSession = Depends(get_async_session),
) -> None:
    inventory = await _get_inventory(id, session)
    inventory.is_deleted = 1
    await session.commit()


async def _get_inventory(id: int, session: AsyncSession) -> InboundOutbound:
    result = await session.exec(_base_query().where(InboundOutbound.id == id))
    inventory = result.one_or_none()
    if inventory is None:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"Inventory {id} not found",
        )
    return inventory


class InventoryRelatedData(BaseModel):
    plan: PurchasePlanRead | None
    weighing: WeighingSlipRead | None


@router.get("/{id}/related", response_model=InventoryRelatedData)
async def get_inventory_related(
    id: int,
    session: AsyncSession = Depends(get_async_session),
) -> InventoryRelatedData:
    """获取出入库单关联的计划和地磅单"""
    inventory = await _get_inventory(id, session)
    
    # 获取关联的计划（通过 purchase_plan_id）
    plan = None
    if inventory.purchase_plan_id:
        plan_result = await session.exec(
            select(PurchasePlan).where(
                PurchasePlan.id == inventory.purchase_plan_id,
                PurchasePlan.is_deleted == 0
            )
        )
        plan = plan_result.one_or_none()
    
    # 获取关联的地磅单（通过 inbound_outbound_id）
    weighing_result = await session.exec(
        select(WeighingSlip).where(
            WeighingSlip.inbound_outbound_id == inventory.id,
            WeighingSlip.is_voided == 0
        ).limit(1)
    )
    weighing = weighing_result.first()
    
    return InventoryRelatedData(
        plan=PurchasePlanRead.model_validate(plan.model_dump()) if plan else None,
        weighing=WeighingSlipRead.model_validate(weighing.model_dump()) if weighing else None
    )