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

from __future__ import annotations

from datetime import date
from typing import List

from fastapi import APIRouter, Depends
from sqlalchemy import func, text
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.weighing import WeighingSlip
from app.schemas.dashboard import DashboardStats, DashboardCargoStat, DashboardPartnerCargoStat

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


@router.get("/stats", response_model=DashboardStats)
async def get_dashboard_stats(
    session: AsyncSession = Depends(get_async_session),
) -> DashboardStats:
    """获取仪表盘统计数据"""
    today = date.today().strftime("%Y-%m-%d")

    # 计算今日入库总量（从inbound_outbound表）
    today_inbound_query = select(func.coalesce(func.sum(InboundOutbound.quantity), 0)).where(
        InboundOutbound.date == today,
        InboundOutbound.business_type == "入库",
        InboundOutbound.is_deleted == 0,
    )
    today_inbound_result = await session.exec(today_inbound_query)
    today_inbound = float(today_inbound_result.one() or 0)

    # 计算今日出库总量（从inbound_outbound表）
    today_outbound_query = select(func.coalesce(func.sum(InboundOutbound.quantity), 0)).where(
        InboundOutbound.date == today,
        InboundOutbound.business_type == "出库",
        InboundOutbound.is_deleted == 0,
    )
    today_outbound_result = await session.exec(today_outbound_query)
    today_outbound = float(today_outbound_result.one() or 0)

    # 计算异常订单数（待入库/待出库状态的订单）
    abnormal_query = select(func.count(WeighingSlip.id)).where(
        WeighingSlip.is_voided == 0,
    )
    abnormal_result = await session.exec(abnormal_query)
    abnormal_orders = abnormal_result.one() or 0

    return DashboardStats(
        today_inbound=today_inbound,
        today_outbound=today_outbound,
        abnormal_orders=abnormal_orders,
    )


@router.get("/inbound/by-cargo", response_model=List[DashboardCargoStat])
async def get_inbound_by_cargo(
    session: AsyncSession = Depends(get_async_session),
) -> List[DashboardCargoStat]:
    """获取今日入库量按货物统计（计划量 vs 实际量）"""
    today = date.today().strftime("%Y-%m-%d")

    # 出入库单表左联地磅单表，按货物统计
    # 计划量来自出入库单表的quantity，实际量来自地磅单表的net_weight（转换为吨）
    query = """
        SELECT
            COALESCE(io.product, ws.cargo_name, '未填写货物') as label,
            COALESCE(SUM(io.quantity), 0) as planned,
            COALESCE(SUM(ws.net_weight) / 1000.0, 0) as actual
        FROM inbound_outbound io
        LEFT JOIN weighing_slips ws ON ws.inbound_outbound_id = io.id
            AND ws.is_voided = 0
        WHERE io.date = :today
            AND io.business_type = '入库'
            AND io.is_deleted = 0
        GROUP BY COALESCE(io.product, ws.cargo_name, '未填写货物')
        ORDER BY planned DESC
    """

    result = await session.execute(text(query), {"today": today})
    rows = result.all()

    return [
        DashboardCargoStat(label=row.label, planned=float(row.planned), actual=float(row.actual))
        for row in rows
    ]


@router.get("/inbound/by-partner-cargo", response_model=List[DashboardPartnerCargoStat])
async def get_inbound_by_partner_cargo(
    session: AsyncSession = Depends(get_async_session),
) -> List[DashboardPartnerCargoStat]:
    """获取今日入库量按往来单位+货物统计（计划量 vs 实际量）"""
    today = date.today().strftime("%Y-%m-%d")

    query = """
        SELECT
            CONCAT(COALESCE(io.partner_company, ''), ' - ', COALESCE(io.product, ws.cargo_name, '未填写货物')) as label,
            COALESCE(SUM(io.quantity), 0) as planned,
            COALESCE(SUM(ws.net_weight) / 1000.0, 0) as actual
        FROM inbound_outbound io
        LEFT JOIN weighing_slips ws ON ws.inbound_outbound_id = io.id
            AND ws.is_voided = 0
        WHERE io.date = :today
            AND io.business_type = '入库'
            AND io.is_deleted = 0
        GROUP BY io.partner_company, io.product, ws.cargo_name
        ORDER BY io.partner_company, planned DESC
    """

    result = await session.execute(text(query), {"today": today})
    rows = result.all()

    return [
        DashboardPartnerCargoStat(label=row.label, planned=float(row.planned), actual=float(row.actual))
        for row in rows
    ]


@router.get("/outbound/by-cargo", response_model=List[DashboardCargoStat])
async def get_outbound_by_cargo(
    session: AsyncSession = Depends(get_async_session),
) -> List[DashboardCargoStat]:
    """获取今日出库量按货物统计（计划量 vs 实际量）"""
    today = date.today().strftime("%Y-%m-%d")

    query = """
        SELECT
            COALESCE(io.product, ws.cargo_name, '未填写货物') as label,
            COALESCE(SUM(io.quantity), 0) as planned,
            COALESCE(SUM(ws.net_weight) / 1000.0, 0) as actual
        FROM inbound_outbound io
        LEFT JOIN weighing_slips ws ON ws.inbound_outbound_id = io.id
            AND ws.is_voided = 0
        WHERE io.date = :today
            AND io.business_type = '出库'
            AND io.is_deleted = 0
        GROUP BY COALESCE(io.product, ws.cargo_name, '未填写货物')
        ORDER BY planned DESC
    """

    result = await session.execute(text(query), {"today": today})
    rows = result.all()

    return [
        DashboardCargoStat(label=row.label, planned=float(row.planned), actual=float(row.actual))
        for row in rows
    ]


@router.get("/outbound/by-partner-cargo", response_model=List[DashboardPartnerCargoStat])
async def get_outbound_by_partner_cargo(
    session: AsyncSession = Depends(get_async_session),
) -> List[DashboardPartnerCargoStat]:
    """获取今日出库量按往来单位+货物统计（计划量 vs 实际量）"""
    today = date.today().strftime("%Y-%m-%d")

    query = """
        SELECT
            CONCAT(COALESCE(io.partner_company, ''), ' - ', COALESCE(io.product, ws.cargo_name, '未填写货物')) as label,
            COALESCE(SUM(io.quantity), 0) as planned,
            COALESCE(SUM(ws.net_weight) / 1000.0, 0) as actual
        FROM inbound_outbound io
        LEFT JOIN weighing_slips ws ON ws.inbound_outbound_id = io.id
            AND ws.is_voided = 0
        WHERE io.date = :today
            AND io.business_type = '出库'
            AND io.is_deleted = 0
        GROUP BY io.partner_company, io.product, ws.cargo_name
        ORDER BY io.partner_company, planned DESC
    """

    result = await session.execute(text(query), {"today": today})
    rows = result.all()

    return [
        DashboardPartnerCargoStat(label=row.label, planned=float(row.planned), actual=float(row.actual))
        for row in rows
    ]