shanxia2.0/backend/app/api/pl/freight.py

"""运输计划管理 - 运费结算 API"""
from datetime import date, datetime
from typing import Any, List, Optional

from fastapi import APIRouter, Body, Depends, HTTPException, Query, Request, status
from pydantic import BaseModel, Field
from sqlalchemy.orm import Session

from app.core.database import get_db
from app.core.db_auth import DbUserContext, get_db_current_user
from app.core.tenant_context import get_tenant_no
from app.schemas.pl.cancel_reason import CancelReasonBody
from app.utils.excel_export import (
    EXPORT_MAX_ROWS,
    ExportColumn,
    ExportValidationError,
    export_rows_to_xlsx_response,
    filter_export_columns,
)
from app.models.pl_models import PlFreightSettlement, PlPlan, PlTransport
from app.schemas.pl.freight import (
    FreightSettlementApprovalActionBody,
    FreightSettlementByTransportRead,
    FreightSettlementCreate,
    FreightSettlementDisburseBody,
    FreightSettlementListResponse,
    FreightSettlementRead,
    FreightSettlementSubmitApprovalBody,
    FreightSettlementUpdate,
    FreightSettlementWithTransports,
    RemittanceVoucherRead,
    UnsettledPlanListResponse,
)
from app.services import pl_freight_settlement_service as freight_svc

router = APIRouter(prefix="/freight", tags=["运输计划-运费结算"])


def _generate_settlement_no(db: Session) -> str:
    today = date.today()
    prefix = f"YF{today.strftime('%Y%m%d')}"

    last = (
        db.query(PlFreightSettlement)
        .filter(
            PlFreightSettlement.settlement_no.like(f"{prefix}%"),
            PlFreightSettlement.del_flag == 0,
        )
        .order_by(PlFreightSettlement.settlement_no.desc())
        .first()
    )

    seq = int(last.settlement_no[-2:]) + 1 if last else 1
    return f"{prefix}{seq:02d}"


def _merge_query(request: Request, **known) -> dict:
    out = {k: v for k, v in known.items() if v is not None and v != ""}
    for k, v in request.query_params.multi_items():
        if k in ("page", "pageSize"):
            continue
        if k not in out and v not in (None, ""):
            out[k] = v
    return out


def _param_get(params: dict, *keys: str) -> Any:
    for key in keys:
        if key in params and params[key] not in (None, ""):
            return params[key]
    return None


def _body_to_params(body: BaseModel) -> dict:
    data = body.model_dump(by_alias=True, exclude_none=True)
    for key in ("ids", "columns"):
        data.pop(key, None)
    extra = getattr(body, "model_extra", None) or {}
    for key, value in extra.items():
        if key not in data and value not in (None, ""):
            data[key] = value
    return data


def _unsettled_column_filter_kwargs(params: dict) -> dict:
    return {
        "plan_no": _param_get(params, "planNo", "plan_no"),
        "plan_no_in": _param_get(params, "planNoIn", "plan_no_in"),
        "plan_type": _param_get(params, "planType", "plan_type"),
        "plan_type_in": _param_get(params, "planTypeIn", "plan_type_in"),
        "license_plate": _param_get(params, "licensePlate", "license_plate"),
        "license_plate_in": _param_get(params, "licensePlateIn", "license_plate_in"),
        "transport_company": _param_get(params, "transportCompany", "transport_company"),
        "transport_company_in": _param_get(params, "transportCompanyIn", "transport_company_in"),
        "freight_amount_op": _param_get(params, "freightAmountOp", "freight_amount_op"),
        "freight_amount": _param_get(params, "freightAmount", "freight_amount"),
        "freight_amount_min": _param_get(params, "freightAmountMin", "freight_amount_min"),
        "freight_amount_max": _param_get(params, "freightAmountMax", "freight_amount_max"),
        "freight_type": _param_get(params, "freightType", "freight_type"),
        "freight_type_in": _param_get(params, "freightTypeIn", "freight_type_in"),
        "transport_qty_op": _param_get(params, "transportQtyOp", "transport_qty_op"),
        "transport_qty": _param_get(params, "transportQty", "transport_qty"),
        "transport_qty_min": _param_get(params, "transportQtyMin", "transport_qty_min"),
        "transport_qty_max": _param_get(params, "transportQtyMax", "transport_qty_max"),
        "has_invoice": _param_get(params, "hasInvoice", "has_invoice"),
        "has_invoice_in": _param_get(params, "hasInvoiceIn", "has_invoice_in"),
        "freight_remark": _param_get(params, "freightRemark", "freight_remark"),
        "freight_remark_in": _param_get(params, "freightRemarkIn", "freight_remark_in"),
    }



class FreightColumnFacetBody(BaseModel):
    """运费未结算列头可选值（去重）"""

    field: str = Field(..., description="planNo | licensePlate | transportCompany | freightRemark")
    keyword: Optional[str] = Field(default=None, description="可选值关键字")
    limit: Optional[int] = Field(default=500, ge=1, le=500, description="返回条数上限")


@router.post("/column-facet-values")
def freight_column_facet_values(
    body: FreightColumnFacetBody,
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
):
    """运费未结算列头可选值（计划号/车牌/承运商/备注去重，租户范围）"""
    from sqlalchemy import func as sa_func

    from app.core.tenant_context import get_tenant_no

    tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
    lim = min(max(1, int(body.limit or 500)), 500)
    kw = (body.keyword or "").strip()
    field = str(body.field or "").strip()

    def _finish(rows):
        out = []
        for row in rows:
            value = str(row.value).strip() if row.value is not None else ""
            if not value:
                continue
            out.append({"value": value, "label": value, "count": int(row.cnt or 0)})
        return out

    if field == "transportCompany":
        col = PlTransport.carrier_name
        q = (
            db.query(col.label("value"), sa_func.count(PlPlan.id).label("cnt"))
            .select_from(PlPlan)
            .join(PlTransport, PlTransport.id == PlPlan.transport_id)
            .filter(
                PlPlan.tenant_no == tenant_no,
                PlPlan.del_flag == 0,
                PlTransport.tenant_no == tenant_no,
                PlTransport.del_flag == 0,
                col.isnot(None),
                col != "",
            )
        )
    else:
        col_map = {
            "planNo": PlPlan.plan_no,
            "licensePlate": PlPlan.license_plate,
            "freightRemark": PlPlan.freight_remark,
        }
        col = col_map.get(field)
        if col is None:
            raise HTTPException(status_code=400, detail="不支持的列")
        q = (
            db.query(col.label("value"), sa_func.count(PlPlan.id).label("cnt"))
            .filter(
                PlPlan.tenant_no == tenant_no,
                PlPlan.del_flag == 0,
                col.isnot(None),
                col != "",
            )
        )
    if kw:
        q = q.filter(col.like(f"%{kw}%"))
    rows = q.group_by(col).order_by(col.asc()).limit(lim).all()
    return {"list": _finish(rows)}


@router.get("/unsettled-plans", response_model=UnsettledPlanListResponse)
def list_unsettled_plans(
    request: Request,
    q: Optional[str] = Query(default=None),
    carrier_id: Optional[str] = Query(default=None, alias="carrierId"),
    plan_no: Optional[str] = Query(default=None, alias="planNo"),
    plan_type: Optional[str] = Query(default=None, alias="planType"),
    plan_type_in: Optional[str] = Query(default=None, alias="planTypeIn"),
    license_plate: Optional[str] = Query(default=None, alias="licensePlate"),
    page: int = Query(default=1, ge=1),
    page_size: int = Query(default=20, ge=1, le=500, alias="pageSize"),
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
) -> UnsettledPlanListResponse:
    tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
    params = _merge_query(
        request,
        q=q,
        carrierId=carrier_id,
        planNo=plan_no,
        planType=plan_type,
        planTypeIn=plan_type_in,
        licensePlate=license_plate,
    )
    total, items = freight_svc.list_unsettled_plans(
        db,
        tenant_no=tenant_no,
        user=user,
        q=_param_get(params, "q"),
        carrier_id=_param_get(params, "carrierId", "carrier_id"),
        **_unsettled_column_filter_kwargs(params),
        page=page,
        page_size=page_size,
    )
    return UnsettledPlanListResponse(total=total, list=items)


@router.get("/", response_model=FreightSettlementListResponse)
def list_settlements(
    request: Request,
    q: Optional[str] = Query(default=None),
    carrier_id: Optional[str] = Query(default=None, alias="carrierId"),
    settlement_no: Optional[str] = Query(default=None, alias="settlementNo"),
    settlement_status: Optional[str] = Query(default=None, alias="settlementStatus"),
    settlement_status_in: Optional[str] = Query(default=None, alias="settlementStatusIn"),
    tab: Optional[str] = Query(
        default=None,
        description="bills|approval_pending|approval_done|disburse|settled",
    ),
    page: int = Query(default=1, ge=1),
    page_size: int = Query(default=20, ge=1, le=500, alias="pageSize"),
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
) -> FreightSettlementListResponse:
    tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
    params = _merge_query(
        request,
        q=q,
        carrierId=carrier_id,
        settlementNo=settlement_no,
        settlementStatus=settlement_status,
        settlementStatusIn=settlement_status_in,
        tab=tab,
    )
    total, items = freight_svc.list_settlements_enriched(
        db,
        tenant_no=tenant_no,
        user_id=user.id,
        user=user,
        q=_param_get(params, "q"),
        carrier_id=_param_get(params, "carrierId", "carrier_id"),
        settlement_no=_param_get(params, "settlementNo", "settlement_no"),
        settlement_status=_param_get(params, "settlementStatus", "settlement_status"),
        settlement_status_in=_param_get(
            params, "settlementStatusIn", "settlement_status_in"
        ),
        tab=_param_get(params, "tab"),
        page=page,
        page_size=page_size,
    )
    return FreightSettlementListResponse(total=total, list=items)



UNSETTLED_EXPORT_COLUMNS = [
    ExportColumn(title="计划编号", field="planNo"),
    ExportColumn(title="计划日期", field="planDate"),
    ExportColumn(title="单据类型", field="planType"),
    ExportColumn(title="装货客户", field="loadCustomers"),
    ExportColumn(title="送货客户", field="deliveryCustomers"),
    ExportColumn(title="车牌号", field="licensePlate"),
    ExportColumn(title="承运商", field="transportCompany"),
    ExportColumn(title="运费(元)", field="freightAmount"),
    ExportColumn(title="运费计算方式", field="freightType"),
    ExportColumn(title="运输量", field="transportQty"),
    ExportColumn(title="运输单价", field="freightPrice"),
    ExportColumn(title="是否开票", field="hasInvoice"),
    ExportColumn(title="归档时间", field="archiveTime"),
]

PLAN_TYPE_LABELS = {"direct": "直销单", "inbound": "入库单", "outbound": "出库单"}
FREIGHT_TYPE_LABELS = {"by_weight": "按重量", "by_trip": "按趟次"}
SETTLEMENT_STATUS_LABELS = {
    "pending": "草稿",
    "rejected": "已驳回",
    "approving": "审批中",
    "approved": "待发放",
    "confirmed": "已发放",
    "cancelled": "已作废",
}


class UnsettledExportBody(BaseModel):
    q: Optional[str] = None
    carrier_id: Optional[str] = Field(default=None, alias="carrierId")
    plan_no: Optional[str] = Field(default=None, alias="planNo")
    plan_type: Optional[str] = Field(default=None, alias="planType")
    plan_type_in: Optional[str] = Field(default=None, alias="planTypeIn")
    license_plate: Optional[str] = Field(default=None, alias="licensePlate")
    ids: Optional[List[str]] = None
    columns: Optional[List[str]] = None
    model_config = {"populate_by_name": True, "extra": "allow"}


@router.post("/unsettled-plans/export")
def export_unsettled_plans(
    body: UnsettledExportBody,
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
):
    try:
        tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
        params = _body_to_params(body)
        _, items = freight_svc.list_unsettled_plans(
            db,
            tenant_no=tenant_no,
            user=user,
            q=_param_get(params, "q"),
            carrier_id=_param_get(params, "carrierId", "carrier_id"),
            **_unsettled_column_filter_kwargs(params),
            ids=body.ids,
            page=1,
            page_size=EXPORT_MAX_ROWS,
            max_page_size=EXPORT_MAX_ROWS,
        )
        rows = []
        for item in items:
            data = dict(item)
            data["deliveryCustomers"] = "\n".join(data.get("deliveryCustomers") or [])
            data["loadCustomers"] = "\n".join(data.get("loadCustomers") or [])
            data["planType"] = PLAN_TYPE_LABELS.get(str(data.get("planType") or ""), data.get("planType"))
            freight_type = str(data.get("freightType") or "")
            data["freightType"] = FREIGHT_TYPE_LABELS.get(freight_type, freight_type)
            data["hasInvoice"] = "是" if int(data.get("hasInvoice") or 0) == 1 else "否"
            for k, v in list(data.items()):
                if hasattr(v, "isoformat"):
                    data[k] = str(v)[:19]
            rows.append(data)
        cols = filter_export_columns(UNSETTLED_EXPORT_COLUMNS, body.columns)
        date_str = datetime.now().strftime("%Y%m%d")
        return export_rows_to_xlsx_response(
            db, tenant_no=tenant_no, sheet_title="待结算计划", columns=cols, rows=rows,
            filename_stem=f"待结算计划_{date_str}",
        )
    except ExportValidationError as e:
        raise HTTPException(status_code=400, detail=str(e)) from e


SETTLEMENT_EXPORT_COLUMNS = [
    ExportColumn(title="结算单号", field="settlementNo"),
    ExportColumn(title="运费合计(元)", field="totalFreight"),
    ExportColumn(title="关联计划数", field="planCount"),
    ExportColumn(title="车牌摘要", field="licensePlateSummary"),
    ExportColumn(title="结算状态", field="settlementStatus"),
    ExportColumn(title="发放金额(元)", field="disburseAmount"),
    ExportColumn(title="发放时间", field="disburseTime"),
    ExportColumn(title="当前审批人", field="currentApproverName"),
    ExportColumn(title="提交人", field="submitterName"),
    ExportColumn(title="创建时间", field="createTime"),
    ExportColumn(title="审批完成时间", field="approvalFinishedTime"),
]


class SettlementExportBody(BaseModel):
    q: Optional[str] = None
    carrier_id: Optional[str] = Field(default=None, alias="carrierId")
    settlement_no: Optional[str] = Field(default=None, alias="settlementNo")
    settlement_status: Optional[str] = Field(default=None, alias="settlementStatus")
    settlement_status_in: Optional[str] = Field(default=None, alias="settlementStatusIn")
    tab: Optional[str] = None
    ids: Optional[List[str]] = None
    columns: Optional[List[str]] = None
    model_config = {"populate_by_name": True, "extra": "allow"}


@router.post("/export")
def export_settlements(
    body: SettlementExportBody,
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
):
    try:
        tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
        params = _body_to_params(body)
        _, items = freight_svc.list_settlements_enriched(
            db,
            tenant_no=tenant_no,
            user_id=user.id,
            user=user,
            q=_param_get(params, "q"),
            carrier_id=_param_get(params, "carrierId", "carrier_id"),
            settlement_no=_param_get(params, "settlementNo", "settlement_no"),
            settlement_status=_param_get(params, "settlementStatus", "settlement_status"),
            settlement_status_in=_param_get(
                params, "settlementStatusIn", "settlement_status_in"
            ),
            tab=_param_get(params, "tab"),
            ids=body.ids,
            page=1,
            page_size=EXPORT_MAX_ROWS,
            max_page_size=EXPORT_MAX_ROWS,
        )
        rows = []
        for item in items:
            data = dict(item)
            stv = data.get("settlementStatus")
            if stv is not None:
                data["settlementStatus"] = SETTLEMENT_STATUS_LABELS.get(str(stv), stv)
            for k, v in list(data.items()):
                if hasattr(v, "isoformat"):
                    data[k] = str(v)[:19]
            rows.append(data)
        cols = filter_export_columns(SETTLEMENT_EXPORT_COLUMNS, body.columns)
        date_str = datetime.now().strftime("%Y%m%d")
        return export_rows_to_xlsx_response(
            db, tenant_no=tenant_no, sheet_title="运费结算", columns=cols, rows=rows,
            filename_stem=f"运费结算_{date_str}",
        )
    except ExportValidationError as e:
        raise HTTPException(status_code=400, detail=str(e)) from e


@router.get("/by-transport/{transport_id}", response_model=Optional[FreightSettlementByTransportRead])
def get_settlement_by_transport(
    transport_id: str,
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
) -> Optional[FreightSettlementByTransportRead]:
    tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
    detail = freight_svc.get_settlement_by_transport_id(
        db, tenant_no=tenant_no, transport_id=transport_id, user=user
    )
    if not detail:
        return None
    return FreightSettlementByTransportRead.model_validate(detail)


@router.post("/", response_model=FreightSettlementRead, status_code=status.HTTP_201_CREATED)
def create_settlement(
    payload: FreightSettlementCreate,
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
) -> FreightSettlementRead:
    tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
    settlement = freight_svc.create_settlement(
        db,
        tenant_no=tenant_no,
        plan_ids=payload.plan_ids,
        transport_ids=payload.transport_ids,
        user_id=user.id,
        dept_id=user.dept_id,
        settlement_no=_generate_settlement_no(db),
    )
    return settlement


@router.get("/{id}", response_model=FreightSettlementWithTransports)
def get_settlement(
    id: str,
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
) -> FreightSettlementWithTransports:
    tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
    detail = freight_svc.get_settlement_detail(
        db, tenant_no=tenant_no, settlement_id=id, user_id=user.id, user=user
    )
    return FreightSettlementWithTransports.model_validate(detail)


@router.put("/{id}", response_model=FreightSettlementRead)
def update_settlement(
    id: str,
    payload: FreightSettlementUpdate,
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
) -> FreightSettlementRead:
    tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
    return freight_svc.update_settlement_draft(
        db,
        tenant_no=tenant_no,
        settlement_id=id,
        payload=payload,
        user_id=user.id,
        user=user,
    )


@router.post("/{id}/cancel", response_model=FreightSettlementRead)
def cancel_settlement(
    id: str,
    body: Optional[CancelReasonBody] = Body(default=None),
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
) -> FreightSettlementRead:
    tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
    return freight_svc.cancel_draft_settlement(
        db,
        tenant_no=tenant_no,
        settlement_id=id,
        user_id=user.id,
        user=user,
        cancel_reason=body.cancel_reason if body else None,
    )


@router.delete("/{id}", status_code=status.HTTP_204_NO_CONTENT)
def delete_settlement(
    id: str,
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
) -> None:
    tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
    freight_svc.delete_draft_settlement(
        db, tenant_no=tenant_no, settlement_id=id, user_id=user.id, user=user
    )


@router.post("/{id}/submit-approval", response_model=FreightSettlementRead)
def submit_settlement_approval(
    id: str,
    payload: FreightSettlementSubmitApprovalBody,
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
) -> FreightSettlementRead:
    tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
    return freight_svc.submit_settlement_approval(
        db,
        tenant_no=tenant_no,
        settlement_id=id,
        body=payload,
        user_id=user.id,
        dept_id=user.dept_id,
        user=user,
    )


@router.post("/approval/{approval_id}", response_model=FreightSettlementRead)
def process_settlement_approval(
    approval_id: str,
    payload: FreightSettlementApprovalActionBody,
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
) -> FreightSettlementRead:
    tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
    if payload.action not in ("approve", "reject"):
        raise HTTPException(status_code=400, detail="action 必须为 approve 或 reject")
    return freight_svc.process_settlement_approval(
        db,
        tenant_no=tenant_no,
        approval_id=approval_id,
        action=payload.action,
        comment=payload.comment,
        user_id=user.id,
    )


@router.post("/{id}/withdraw-approval", response_model=FreightSettlementRead)
def withdraw_settlement_approval(
    id: str,
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
) -> FreightSettlementRead:
    tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
    return freight_svc.withdraw_settlement_approval(
        db, tenant_no=tenant_no, settlement_id=id, user_id=user.id, user=user
    )


@router.get("/{id}/approval-flow")
def get_settlement_approval_flow(
    id: str,
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
):
    tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
    freight_svc.get_settlement_row(db, tenant_no, id, user=user)
    return freight_svc.get_approval_flow(db, id)


@router.post("/{id}/disburse", response_model=FreightSettlementRead)
def disburse_settlement(
    id: str,
    payload: FreightSettlementDisburseBody,
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
) -> FreightSettlementRead:
    tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
    return freight_svc.disburse_settlement(
        db,
        tenant_no=tenant_no,
        settlement_id=id,
        body=payload,
        user_id=user.id,
        dept_id=user.dept_id,
        user=user,
    )


# 向后兼容旧接口
@router.post("/{id}/submit", response_model=FreightSettlementRead, deprecated=True)
def submit_settlement_legacy(
    id: str,
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
) -> FreightSettlementRead:
    raise HTTPException(status_code=400, detail="请使用 submit-approval 接口并选择审批人")


@router.post("/{id}/approve", response_model=FreightSettlementRead, deprecated=True)
def approve_settlement_legacy(
    id: str,
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
) -> FreightSettlementRead:
    raise HTTPException(status_code=400, detail="请使用 approval/{approvalId} 接口审批")


@router.get("/voucher/{settlement_id}", response_model=list[RemittanceVoucherRead])
def list_vouchers(
    settlement_id: str,
    db: Session = Depends(get_db),
    user: DbUserContext = Depends(get_db_current_user),
) -> list[RemittanceVoucherRead]:
    tenant_no = get_tenant_no(db, user.to_tenant_user_dict())
    freight_svc.get_settlement_row(db, tenant_no, settlement_id, user=user)
    detail = freight_svc.get_settlement_detail(
        db, tenant_no=tenant_no, settlement_id=settlement_id, user_id=user.id, user=user
    )
    return detail.get("vouchers") or []