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

from __future__ import annotations

from datetime import datetime, date

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.plans import PurchasePlan
from app.models.inventory import InboundOutbound
from app.models.weighing import WeighingSlip
from app.schemas.plans import PurchasePlanCreate, PurchasePlanRead, PurchasePlanUpdate, PurchasePlanWithFillLink
from app.schemas.inventory import InboundOutboundRead
from app.schemas.weighing import WeighingSlipRead
from pydantic import BaseModel

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


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


STATUS_THIRD_PARTY_FILL = "待三方填写"


@router.get("/", response_model=list[PurchasePlanWithFillLink])
async def list_plans(
    q: str | None = Query(default=None, description="模糊搜索往来单位或产品"),
    business_type: str | None = Query(default=None, alias="businessType", description="业务类型"),
    execution_status: str | None = Query(default=None, alias="executionStatus", description="执行状态"),
    created_from: str | None = Query(default=None, alias="createdFrom", description="创建开始日期"),
    created_to: str | None = Query(default=None, alias="createdTo", description="创建结束日期"),
    start_date_from: str | None = Query(default=None, alias="startDateFrom", description="计划起始日期开始"),
    start_date_to: str | None = Query(default=None, alias="startDateTo", description="计划起始日期结束"),
    session: AsyncSession = Depends(get_async_session),
) -> list[PurchasePlanWithFillLink]:
    # 默认按创建时间倒序
    query = _base_query().order_by(PurchasePlan.created_at.desc())

    if q:
        query = query.where(
            (func.lower(PurchasePlan.customer_name).contains(q.lower()))
            | (func.lower(PurchasePlan.product).contains(q.lower()))
            | (func.lower(PurchasePlan.plan_no).contains(q.lower()))
        )

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

    if execution_status:
        query = query.where(PurchasePlan.execution_status == execution_status)

    # 按创建时间过滤（日期粒度）
    if created_from:
        query = query.where(PurchasePlan.created_at >= created_from)

    if created_to:
        query = query.where(PurchasePlan.created_at <= created_to + " 23:59:59")

    # 按计划起始日期过滤
    if start_date_from:
        query = query.where(PurchasePlan.start_date >= start_date_from)

    if start_date_to:
        query = query.where(PurchasePlan.start_date <= start_date_to)

    result = await session.exec(query)
    plans = result.all()
    if not plans:
        return []

    # 1）查出「生成链接给三方填写」对应的出入库单 id
    plan_ids = [p.id for p in plans]
    fill_link_result = await session.exec(
        select(InboundOutbound).where(
            InboundOutbound.purchase_plan_id.in_(plan_ids),
            InboundOutbound.is_deleted == 0,
            InboundOutbound.status == STATUS_THIRD_PARTY_FILL,
        )
    )
    fill_links = fill_link_result.all()
    plan_id_to_inv_id = {
        inv.purchase_plan_id: inv.id for inv in fill_links if inv.purchase_plan_id
    }

    # 2）不依赖 purchase_plans.executed_quantity 字段，直接从地磅表实时汇总已执行量（kg）
    plan_nos = [p.plan_no for p in plans if p.plan_no]
    executed_sum_map: dict[tuple[int | None, str | None], float] = {}

    if plan_ids or plan_nos:
        weighing_query = (
            select(
                WeighingSlip.purchase_plan_id,
                WeighingSlip.plan_no,
                func.sum(WeighingSlip.net_weight).label("total_executed"),
            )
            .where(
                WeighingSlip.is_voided == 0,
                (
                    (WeighingSlip.purchase_plan_id.in_(plan_ids))
                    | (WeighingSlip.plan_no.in_(plan_nos))
                ),
            )
            .group_by(WeighingSlip.purchase_plan_id, WeighingSlip.plan_no)
        )
        weighing_result = await session.exec(weighing_query)
        for row in weighing_result.all():
            key = (row.purchase_plan_id, row.plan_no)
            executed_sum_map[key] = float(row.total_executed or 0)

    def calc_executed_for_plan(plan: PurchasePlan) -> float:
        """
        根据业务逻辑，对每个计划实时计算已执行量（kg）：
        - 关联条件与 _process_weighing_business_logic 中保持一致：
          WeighingSlip.plan_no == plan.plan_no OR WeighingSlip.purchase_plan_id == plan.id
        - 这里先按 (purchase_plan_id, plan_no) 汇总，再在内存中做一次匹配累加。
        """
        if not executed_sum_map:
            return 0.0
        total = 0.0
        for (pid, pno), executed in executed_sum_map.items():
            if pid is not None and pid == plan.id:
                total += executed
            elif (
                pno
                and plan.plan_no
                and pno == plan.plan_no
            ):
                total += executed
        return total

    # 3）构造返回数据：使用实时计算的 executed_quantity 覆盖模型里的字段
    result_list: list[PurchasePlanWithFillLink] = []
    for p in plans:
        data = p.model_dump()
        data["executed_quantity"] = calc_executed_for_plan(p)
        data["fill_link_inventory_id"] = plan_id_to_inv_id.get(p.id)
        result_list.append(PurchasePlanWithFillLink(**data))

    return result_list


def _generate_plan_no() -> str:
    """生成计划号：SX-YYYYMMDDHHMMSS001"""
    now = datetime.now()
    return f"SX-{now.strftime('%Y%m%d%H%M%S')}001"


@router.post("/", response_model=PurchasePlanRead, status_code=status.HTTP_201_CREATED)
async def create_plan(
    payload: PurchasePlanCreate,
    session: AsyncSession = Depends(get_async_session),
) -> PurchasePlan:
    # 如果没有提供计划号，自动生成
    plan_no = payload.plan_no if payload.plan_no else _generate_plan_no()

    # 设置默认执行状态
    execution_status = payload.execution_status
    if not execution_status:
        execution_status = "待入库" if payload.business_type == "入库" else "待出库"

    plan = PurchasePlan(
        plan_no=plan_no,
        plan_date=payload.plan_date,
        start_date=payload.start_date,
        end_date=payload.end_date,
        business_type=payload.business_type,
        product=payload.product.strip(),
        specification=payload.specification,
        planned_quantity=payload.planned_quantity,
        vehicle_no=payload.vehicle_no or "",
        customer_name=payload.customer_name.strip() if payload.customer_name else None,
        remarks=payload.remarks,
        is_deleted=0,
        execution_status=execution_status,
        executed_at=None,
        last_weighing_slip_id=None,
        executed_quantity=0,
        created_at=datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
        auto_generate_enabled=payload.auto_generate_enabled or 0,
        auto_generate_end_month=payload.auto_generate_end_month,
        auto_generate_last_month=None,
    )
    session.add(plan)
    await session.commit()
    await session.refresh(plan)
    return plan


@router.get("/{id}", response_model=PurchasePlanRead)
async def get_plan(
    id: int,
    session: AsyncSession = Depends(get_async_session),
) -> PurchasePlan:
    return await _get_plan(id, session)


@router.patch("/{id}", response_model=PurchasePlanRead)
async def update_plan(
    id: int,
    payload: PurchasePlanUpdate,
    session: AsyncSession = Depends(get_async_session),
) -> PurchasePlan:
    plan = await _get_plan(id, session)
    payload_dict = payload.model_dump(exclude_unset=True)

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

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


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


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


class PlanRelatedData(BaseModel):
    inventories: list[InboundOutboundRead]
    weighings: list[WeighingSlipRead]


@router.get("/{id}/related", response_model=PlanRelatedData)
async def get_plan_related(
    id: int,
    session: AsyncSession = Depends(get_async_session),
) -> PlanRelatedData:
    """获取计划关联的出入库单和地磅单"""
    plan = await _get_plan(id, session)
    
    # 获取关联的出入库单（通过 purchase_plan_id）
    inventory_result = await session.exec(
        select(InboundOutbound).where(
            InboundOutbound.purchase_plan_id == plan.id,
            InboundOutbound.is_deleted == 0
        ).order_by(InboundOutbound.date.desc(), InboundOutbound.id.desc())
    )
    inventories = inventory_result.all()
    
    # 获取关联的地磅单（通过 plan_no 或 purchase_plan_id）
    weighing_query = select(WeighingSlip).where(
        WeighingSlip.is_voided == 0
    )
    if plan.plan_no:
        weighing_query = weighing_query.where(
            (WeighingSlip.plan_no == plan.plan_no) |
            (WeighingSlip.purchase_plan_id == plan.id)
        )
    else:
        weighing_query = weighing_query.where(
            WeighingSlip.purchase_plan_id == plan.id
        )
    weighing_query = weighing_query.order_by(WeighingSlip.created_at.desc())
    weighing_result = await session.exec(weighing_query)
    weighings = weighing_result.all()
    
    return PlanRelatedData(
        inventories=[InboundOutboundRead.model_validate(inv.model_dump()) for inv in inventories],
        weighings=[WeighingSlipRead.model_validate(w.model_dump()) for w in weighings]
    )


class ThirdPartyPlanPageData(BaseModel):
    """三方填写 H5 页面数据：计划信息 + 当天出入库单列表 + 本月历史出入库单列表"""

    plan: PurchasePlanRead
    today_inventories: list[InboundOutboundRead]
    month_inventories: list[InboundOutboundRead]


def _parse_date_str(d: str | None) -> date | None:
    if not d:
        return None
    try:
        return datetime.strptime(d, "%Y-%m-%d").date()
    except Exception:
        return None


@router.get("/{id}/third-party-page", response_model=ThirdPartyPlanPageData)
async def get_third_party_plan_page(
    id: int,
    session: AsyncSession = Depends(get_async_session),
) -> ThirdPartyPlanPageData:
    """
    提供给三方使用的 H5 页面初始化数据：
    - 校验当前日期是否在计划有效期内（start_date/end_date）
    - 返回计划基本信息
    - 返回该计划“当天”的出入库单列表（按日期+id 倒序）
    """
    plan = await _get_plan(id, session)

    today = date.today()
    start = _parse_date_str(plan.start_date)
    end = _parse_date_str(plan.end_date)

    # 仅在计划有效期内允许填写：如果配置了开始/结束日期，则进行约束
    if start and today < start:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="计划尚未生效，当前日期不允许填写出入库单",
        )
    if end and today > end:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="计划已过期，当前日期不允许填写出入库单",
        )

    # 查询该计划当天的出入库单（基于 date 字段）
    today_str = today.strftime("%Y-%m-%d")
    inventory_result = await session.exec(
        select(InboundOutbound)
        .where(
            InboundOutbound.purchase_plan_id == plan.id,
            InboundOutbound.is_deleted == 0,
            InboundOutbound.date == today_str,
        )
        .order_by(InboundOutbound.date.desc(), InboundOutbound.id.desc())
    )
    inventories = inventory_result.all()

    # 查询该计划本月（不含今天）的历史出入库单
    month_start_str = today.replace(day=1).strftime("%Y-%m-%d")
    month_history_result = await session.exec(
        select(InboundOutbound)
        .where(
            InboundOutbound.purchase_plan_id == plan.id,
            InboundOutbound.is_deleted == 0,
            InboundOutbound.date >= month_start_str,
            InboundOutbound.date < today_str,
        )
        .order_by(InboundOutbound.date.desc(), InboundOutbound.id.desc())
    )
    month_history_inventories = month_history_result.all()

    return ThirdPartyPlanPageData(
        plan=PurchasePlanRead.model_validate(plan.model_dump()),
        today_inventories=[
            InboundOutboundRead.model_validate(inv.model_dump()) for inv in inventories
        ],
        month_inventories=[
            InboundOutboundRead.model_validate(inv.model_dump())
            for inv in month_history_inventories
        ],
    )