mtsd/backend/app/crud/statistics.py

"""数据统计 CRUD"""
from sqlalchemy.orm import Session
from sqlalchemy import and_, func, desc, extract
from typing import Optional, List, Dict, Any, Tuple
from datetime import datetime, timedelta
from decimal import Decimal

from app.models.models import (
    BizOrder, BizOrderItem, BizDeliveryTask,
    BizSettlement, BizRefund, BizPriceList, BizPriceListItem,
    SysUser, SysRegion, BasProduct,
    BizInquiry, BizQuotation, BizDeliveryWarning,
)


class StatisticsCRUD:
    """数据统计CRUD操作"""

    @staticmethod
    def get_order_statistics(
        db: Session,
        region_id: str = None,
        start_date: str = None,
        end_date: str = None
    ) -> Dict[str, Any]:
        """获取订单统计数据"""
        query = db.query(BizOrder).filter(BizOrder.is_del == 0)

        if region_id:
            query = query.filter(BizOrder.region_id == region_id)

        if start_date:
            try:
                start_dt = datetime.strptime(start_date, "%Y-%m-%d")
                query = query.filter(BizOrder.create_at >= start_dt)
            except:
                pass

        if end_date:
            try:
                end_dt = datetime.strptime(end_date, "%Y-%m-%d")
                end_dt = end_dt.replace(hour=23, minute=59, second=59)
                query = query.filter(BizOrder.create_at <= end_dt)
            except:
                pass

        orders = query.all()

        total_orders = len(orders)
        pending_payment = sum(1 for o in orders if o.status == "pending_payment")
        pending_dispatch = sum(1 for o in orders if getattr(o, "delivery_status", None) == "pending_dispatch")
        delivering = sum(1 for o in orders if o.status == "delivering")
        completed = sum(1 for o in orders if o.status == "completed")
        cancelled = sum(1 for o in orders if o.status == "cancelled")

        total_amount = sum(float(o.total_amount or 0) for o in orders)
        paid_amount = sum(float(o.paid_amount or 0) for o in orders)
        refund_amount = sum(float(o.refund_amount or 0) for o in orders)

        return {
            "totalOrders": total_orders,
            "pendingPayment": pending_payment,
            "pendingDelivery": pending_dispatch,
            "delivering": delivering,
            "completed": completed,
            "cancelled": cancelled,
            "totalAmount": round(total_amount, 2),
            "paidAmount": round(paid_amount, 2),
            "refundAmount": round(refund_amount, 2)
        }

    @staticmethod
    def get_order_trend(
        db: Session,
        region_id: str = None,
        days: int = 7,
        include_inactive_regions: bool = False,
    ) -> List[Dict[str, Any]]:
        """获取订单趋势数据（按日统计）"""
        result = []
        today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
        region_active_clause = [] if include_inactive_regions else [SysRegion.is_active == True]

        for i in range(days - 1, -1, -1):
            day_start = today - timedelta(days=i)
            day_end = day_start + timedelta(days=1)

            query = (
                db.query(BizOrder)
                .join(SysRegion, BizOrder.region_id == SysRegion.id)
                .filter(
                    and_(
                        BizOrder.is_del == 0,
                        BizOrder.create_at >= day_start,
                        BizOrder.create_at < day_end,
                        SysRegion.is_del == 0,
                    *region_active_clause,
                    )
                )
            )

            if region_id:
                query = query.filter(BizOrder.region_id == region_id)

            orders = query.all()
            order_count = len(orders)
            order_amount = sum(float(o.total_amount or 0) for o in orders)

            result.append({
                "date": day_start.strftime("%Y-%m-%d"),
                "orderCount": order_count,
                "orderAmount": round(order_amount, 2)
            })

        return result

    @staticmethod
    def get_today_order_stats_by_region(
        db: Session,
        region_id: str = None,
        include_inactive_regions: bool = False,
    ) -> List[Dict[str, Any]]:
        """今日各区域营业额(元)、订单笔数；列出未删除的区域，无单则为 0。"""
        today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
        day_end = today + timedelta(days=1)
        region_active_clause = [] if include_inactive_regions else [SysRegion.is_active == True]

        reg_q = db.query(SysRegion).filter(
            SysRegion.is_del == 0,
        )
        if region_id:
            reg_q = reg_q.filter(SysRegion.id == region_id)
        regions = reg_q.order_by(SysRegion.sort_order, SysRegion.name).all()

        agg_q = (
            db.query(
                BizOrder.region_id,
                func.count(BizOrder.id).label("order_count"),
                func.coalesce(func.sum(BizOrder.total_amount), 0).label("order_amount"),
            )
            .join(SysRegion, BizOrder.region_id == SysRegion.id)
            .filter(
                BizOrder.is_del == 0,
                BizOrder.create_at >= today,
                BizOrder.create_at < day_end,
                SysRegion.is_del == 0,
                *region_active_clause,
            )
            .group_by(BizOrder.region_id)
        )
        if region_id:
            agg_q = agg_q.filter(BizOrder.region_id == region_id)
        agg_map: Dict[str, Any] = {r.region_id: r for r in agg_q.all() if r.region_id}

        out: List[Dict[str, Any]] = []
        for reg in regions:
            row = agg_map.get(reg.id)
            cnt = int(row.order_count or 0) if row else 0
            amt = float(row.order_amount or 0) if row else 0.0
            out.append({
                "regionId": reg.id,
                "regionName": reg.name or reg.id,
                "orderCount": cnt,
                "orderAmount": round(amt, 2),
            })
        return out

    @staticmethod
    def get_today_order_stats_by_client(
        db: Session,
        region_id: str = None,
        limit: int = 30,
        include_inactive_regions: bool = False,
    ) -> List[Dict[str, Any]]:
        """今日各下单客户：下单金额合计(元)、商品数量合计（订单行数量之和）；按金额降序，最多 limit 条。"""
        today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
        day_end = today + timedelta(days=1)
        region_active_clause = [] if include_inactive_regions else [SysRegion.is_active == True]

        amt_q = (
            db.query(
                BizOrder.client_id,
                func.coalesce(func.sum(BizOrder.total_amount), 0).label("order_amount"),
            )
            .join(SysRegion, BizOrder.region_id == SysRegion.id)
            .filter(
                BizOrder.is_del == 0,
                BizOrder.create_at >= today,
                BizOrder.create_at < day_end,
                SysRegion.is_del == 0,
                *region_active_clause,
            )
        )
        if region_id:
            amt_q = amt_q.filter(BizOrder.region_id == region_id)
        amt_rows = amt_q.group_by(BizOrder.client_id).all()

        qty_q = (
            db.query(
                BizOrder.client_id,
                func.coalesce(func.sum(BizOrderItem.quantity), 0).label("qty"),
            )
            .select_from(BizOrderItem)
            .join(
                BizOrder,
                and_(
                    BizOrder.id == BizOrderItem.order_id,
                    BizOrder.is_del == 0,
                    BizOrder.create_at >= today,
                    BizOrder.create_at < day_end,
                ),
            )
            .join(SysRegion, BizOrder.region_id == SysRegion.id)
            .filter(BizOrderItem.is_del == 0)
            .filter(SysRegion.is_del == 0, *region_active_clause)
        )
        if region_id:
            qty_q = qty_q.filter(BizOrder.region_id == region_id)
        qty_rows = qty_q.group_by(BizOrder.client_id).all()
        qty_map: Dict[str, float] = {
            r.client_id: float(r.qty or 0) for r in qty_rows if r.client_id
        }

        merged: List[Dict[str, Any]] = []
        for r in amt_rows:
            cid = r.client_id
            if not cid:
                continue
            merged.append({
                "clientId": cid,
                "orderAmount": round(float(r.order_amount or 0), 2),
                "productQuantity": round(float(qty_map.get(cid, 0) or 0), 4),
            })

        merged.sort(key=lambda x: -x["orderAmount"])
        merged = merged[: max(1, min(int(limit or 30), 100))]

        client_ids = [m["clientId"] for m in merged]
        names: Dict[str, str] = {}
        if client_ids:
            for u in (
                db.query(SysUser)
                .filter(and_(SysUser.is_del == 0, SysUser.id.in_(client_ids)))
                .all()
            ):
                names[u.id] = (u.real_name or u.phone or u.username or u.id) or u.id

        out: List[Dict[str, Any]] = []
        for m in merged:
            cid = m["clientId"]
            nm = names.get(cid, cid)
            out.append({
                "clientId": cid,
                "clientName": nm,
                "orderAmount": m["orderAmount"],
                "productQuantity": m["productQuantity"],
            })
        return out

    @staticmethod
    def get_financial_statistics(
        db: Session,
        region_id: str = None,
        start_date: str = None,
        end_date: str = None
    ) -> Dict[str, Any]:
        """获取财务统计数据"""
        order_query = db.query(BizOrder).filter(BizOrder.is_del == 0)
        settlement_query = db.query(BizSettlement).filter(BizSettlement.is_del == 0)
        refund_query = db.query(BizRefund).filter(BizRefund.is_del == 0)

        if region_id:
            order_query = order_query.filter(BizOrder.region_id == region_id)
            settlement_query = settlement_query.filter(BizSettlement.region_id == region_id)

        if start_date:
            try:
                start_dt = datetime.strptime(start_date, "%Y-%m-%d")
                order_query = order_query.filter(BizOrder.create_at >= start_dt)
                settlement_query = settlement_query.filter(BizSettlement.create_at >= start_dt)
                refund_query = refund_query.filter(BizRefund.create_at >= start_dt)
            except:
                pass

        if end_date:
            try:
                end_dt = datetime.strptime(end_date, "%Y-%m-%d")
                end_dt = end_dt.replace(hour=23, minute=59, second=59)
                order_query = order_query.filter(BizOrder.create_at <= end_dt)
                settlement_query = settlement_query.filter(BizSettlement.create_at <= end_dt)
                refund_query = refund_query.filter(BizRefund.create_at <= end_dt)
            except:
                pass

        orders = order_query.all()
        settlements = settlement_query.filter(BizSettlement.status == "confirmed").all()
        refunds = refund_query.filter(BizRefund.status == "completed").all()

        total_revenue = sum(float(o.total_amount or 0) for o in orders)
        total_received = sum(float(s.amount or 0) for s in settlements)
        total_refund = sum(float(r.refund_amount or 0) for r in refunds)
        net_revenue = total_received - total_refund

        return {
            "totalRevenue": round(total_revenue, 2),
            "totalReceived": round(total_received, 2),
            "totalRefund": round(total_refund, 2),
            "netRevenue": round(net_revenue, 2),
            "orderCount": len(orders),
            "settlementCount": len(settlements),
            "refundCount": len(refunds)
        }

    @staticmethod
    def get_financial_trend(
        db: Session,
        region_id: str = None,
        days: int = 7
    ) -> List[Dict[str, Any]]:
        """获取财务趋势数据（按日统计）"""
        result = []
        today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)

        for i in range(days - 1, -1, -1):
            day_start = today - timedelta(days=i)
            day_end = day_start + timedelta(days=1)

            settlement_query = db.query(BizSettlement).filter(
                and_(
                    BizSettlement.is_del == 0,
                    BizSettlement.status == "confirmed",
                    BizSettlement.create_at >= day_start,
                    BizSettlement.create_at < day_end
                )
            )

            refund_query = db.query(BizRefund).filter(
                and_(
                    BizRefund.is_del == 0,
                    BizRefund.status == "completed",
                    BizRefund.create_at >= day_start,
                    BizRefund.create_at < day_end
                )
            )

            if region_id:
                settlement_query = settlement_query.filter(BizSettlement.region_id == region_id)

            settlements = settlement_query.all()
            refunds = refund_query.all()

            received = sum(float(s.amount or 0) for s in settlements)
            refund = sum(float(r.refund_amount or 0) for r in refunds)

            result.append({
                "date": day_start.strftime("%Y-%m-%d"),
                "received": round(received, 2),
                "refund": round(refund, 2),
                "net": round(received - refund, 2)
            })

        return result

    @staticmethod
    def get_delivery_statistics(
        db: Session,
        region_id: str = None,
        start_date: str = None,
        end_date: str = None
    ) -> Dict[str, Any]:
        """获取配送统计数据"""
        query = db.query(BizDeliveryTask).filter(BizDeliveryTask.is_del == 0)

        if region_id:
            query = query.filter(BizDeliveryTask.region_id == region_id)

        if start_date:
            try:
                start_dt = datetime.strptime(start_date, "%Y-%m-%d")
                query = query.filter(BizDeliveryTask.create_at >= start_dt)
            except:
                pass

        if end_date:
            try:
                end_dt = datetime.strptime(end_date, "%Y-%m-%d")
                end_dt = end_dt.replace(hour=23, minute=59, second=59)
                query = query.filter(BizDeliveryTask.create_at <= end_dt)
            except:
                pass

        tasks = query.all()

        pending = sum(1 for t in tasks if t.status == "pending")
        picked_up = sum(1 for t in tasks if t.status == "picked_up")
        delivering = sum(1 for t in tasks if t.status == "delivering")
        completed = sum(1 for t in tasks if t.status == "completed")
        cancelled = sum(1 for t in tasks if t.status == "cancelled")

        return {
            "totalTasks": len(tasks),
            "pending": pending,
            "pickedUp": picked_up,
            "delivering": delivering,
            "completed": completed,
            "cancelled": cancelled
        }

    @staticmethod
    def get_product_statistics(
        db: Session,
        region_id: str = None,
        start_date: str = None,
        end_date: str = None,
        limit: int = 10
    ) -> List[Dict[str, Any]]:
        """获取商品销售排行"""
        query = db.query(
            BizOrderItem.product_id,
            func.sum(BizOrderItem.quantity).label("total_quantity"),
            func.sum(BizOrderItem.amount).label("total_amount")
        ).join(
            BizOrder,
            and_(
                BizOrder.id == BizOrderItem.order_id,
                BizOrder.is_del == 0,
                BizOrder.status.in_(["completed", "delivering", "pending_confirm"])
            )
        ).filter(
            BizOrderItem.is_del == 0
        )

        if region_id:
            query = query.filter(BizOrder.region_id == region_id)

        if start_date:
            try:
                start_dt = datetime.strptime(start_date, "%Y-%m-%d")
                query = query.filter(BizOrder.create_at >= start_dt)
            except:
                pass

        if end_date:
            try:
                end_dt = datetime.strptime(end_date, "%Y-%m-%d")
                end_dt = end_dt.replace(hour=23, minute=59, second=59)
                query = query.filter(BizOrder.create_at <= end_dt)
            except:
                pass

        results = query.group_by(BizOrderItem.product_id).order_by(
            desc("total_amount")
        ).limit(limit).all()

        product_stats = []
        for r in results:
            product = db.query(BasProduct).filter(
                and_(BasProduct.id == r.product_id, BasProduct.is_del == 0)
            ).first()

            product_stats.append({
                "productId": r.product_id,
                "productName": product.name if product else None,
                "totalQuantity": float(r.total_quantity or 0),
                "totalAmount": float(r.total_amount or 0)
            })

        return product_stats

    @staticmethod
    def get_customer_statistics(
        db: Session,
        region_id: str = None,
        start_date: str = None,
        end_date: str = None,
        limit: int = 10
    ) -> List[Dict[str, Any]]:
        """获取客户订单排行"""
        query = db.query(
            BizOrder.client_id,
            func.count(BizOrder.id).label("order_count"),
            func.sum(BizOrder.total_amount).label("total_amount")
        ).filter(
            and_(
                BizOrder.is_del == 0,
                BizOrder.status.in_(["completed", "delivering", "pending_confirm", "pending_payment"])
            )
        )

        if region_id:
            query = query.filter(BizOrder.region_id == region_id)

        if start_date:
            try:
                start_dt = datetime.strptime(start_date, "%Y-%m-%d")
                query = query.filter(BizOrder.create_at >= start_dt)
            except:
                pass

        if end_date:
            try:
                end_dt = datetime.strptime(end_date, "%Y-%m-%d")
                end_dt = end_dt.replace(hour=23, minute=59, second=59)
                query = query.filter(BizOrder.create_at <= end_dt)
            except:
                pass

        results = query.group_by(BizOrder.client_id).order_by(
            desc("total_amount")
        ).limit(limit).all()

        customer_stats = []
        for r in results:
            customer = db.query(SysUser).filter(
                and_(SysUser.id == r.client_id, SysUser.is_del == 0)
            ).first()

            customer_stats.append({
                "customerId": r.client_id,
                "customerName": customer.real_name if customer else None,
                "customerPhone": customer.phone if customer else None,
                "orderCount": r.order_count,
                "totalAmount": float(r.total_amount or 0)
            })

        return customer_stats

    @staticmethod
    def _dashboard_order_region_clause(region_id: str = None):
        return [BizOrder.region_id == region_id] if region_id else []

    @staticmethod
    def get_dashboard_summary(
        db: Session,
        region_id: str = None,
        include_inactive_regions: bool = False,
    ) -> Dict[str, Any]:
        """获取仪表盘汇总数据（运营工作台；含待办摘要、最新订单、预警数等，字段驼峰）"""
        today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
        tomorrow_start = today + timedelta(days=1)
        month_start = today.replace(day=1)
        yesterday_start = today - timedelta(days=1)
        region_clause = StatisticsCRUD._dashboard_order_region_clause(region_id)
        region_active_clause = [] if include_inactive_regions else [SysRegion.is_active == True]

        order_query = (
            db.query(BizOrder)
            .join(SysRegion, BizOrder.region_id == SysRegion.id)
            .filter(
                and_(
                    BizOrder.is_del == 0,
                    BizOrder.create_at >= month_start,
                    SysRegion.is_del == 0,
                    *region_active_clause,
                    *region_clause,
                )
            )
        )
        month_orders = order_query.all()
        today_orders = [
            o for o in month_orders if o.create_at and o.create_at >= today
        ]
        prev_day_orders = [
            o
            for o in month_orders
            if o.create_at and yesterday_start <= o.create_at < today
        ]

        pending_delivery_q = (
            db.query(BizOrder)
            .join(SysRegion, BizOrder.region_id == SysRegion.id)
            .filter(
                and_(
                    BizOrder.is_del == 0,
                    BizOrder.delivery_status == "pending_dispatch",
                    SysRegion.is_del == 0,
                    *region_active_clause,
                    *region_clause,
                )
            )
        )
        pending_delivery_count = pending_delivery_q.count()

        def _delta_hint(cur_n: int, prev_n: int, unit: str) -> str:
            d = cur_n - prev_n
            if d > 0:
                return f"较昨日 +{d}{unit}"
            if d < 0:
                return f"较昨日 {d}{unit}"
            return "与昨日持平"

        def _amount_delta_hint(cur_amt: float, prev_amt: float) -> str:
            d = round(cur_amt - prev_amt, 2)
            if d > 0.0001:
                return f"较昨日 +¥{d:,.2f}"
            if d < -0.0001:
                return f"较昨日 ¥{d:,.2f}"
            return "与昨日持平"

        today_order_count = len(today_orders)
        today_order_amount = round(sum(float(o.total_amount or 0) for o in today_orders), 2)
        prev_count = len(prev_day_orders)
        prev_amount = round(sum(float(o.total_amount or 0) for o in prev_day_orders), 2)

        # 未解决的配送时效预警（可按区域通过任务表关联）
        warn_q = (
            db.query(BizDeliveryWarning)
            .join(
                BizDeliveryTask,
                and_(
                    BizDeliveryTask.id == BizDeliveryWarning.task_id,
                    BizDeliveryTask.is_del == 0,
                ),
            )
            .join(SysRegion, BizDeliveryTask.region_id == SysRegion.id)
            .filter(
                and_(
                    BizDeliveryWarning.is_del == 0,
                    BizDeliveryWarning.is_resolved == 0,
                    SysRegion.is_del == 0,
                    *region_active_clause,
                )
            )
        )
        if region_id:
            warn_q = warn_q.filter(BizDeliveryTask.region_id == region_id)
        delivery_warning_count = warn_q.count()

        # 今日新提交报价：状态为已提交，且提交时间（无则用创建时间）落在当日 0 点～次日 0 点
        quot_submitted_at = func.coalesce(BizQuotation.submit_time, BizQuotation.create_at)
        sub_quot_q = (
            db.query(BizQuotation)
            .join(SysRegion, BizQuotation.region_id == SysRegion.id)
            .filter(
                and_(
                    BizQuotation.is_del == 0,
                    BizQuotation.status == "submitted",
                    quot_submitted_at >= today,
                    quot_submitted_at < tomorrow_start,
                    SysRegion.is_del == 0,
                    *region_active_clause,
                )
            )
        )
        if region_id:
            sub_quot_q = sub_quot_q.filter(BizQuotation.region_id == region_id)
        pending_quotation_count = sub_quot_q.count()

        sub_quot_preview = (
            db.query(BizQuotation, BizInquiry)
            .join(BizInquiry, BizInquiry.id == BizQuotation.inquiry_id)
            .join(SysRegion, BizQuotation.region_id == SysRegion.id)
            .filter(
                BizQuotation.is_del == 0,
                BizQuotation.status == "submitted",
                quot_submitted_at >= today,
                quot_submitted_at < tomorrow_start,
                BizInquiry.is_del == 0,
                SysRegion.is_del == 0,
                *region_active_clause,
                *([BizQuotation.region_id == region_id] if region_id else []),
            )
            .order_by(desc(BizQuotation.submit_time), desc(BizQuotation.create_at))
            .limit(2)
            .all()
        )
        quotation_lines = []
        for q, inq in sub_quot_preview:
            title = (inq.title or "询价单")[:48]
            quotation_lines.append(title)

        pd_preview = (
            db.query(BizOrder)
            .join(SysRegion, BizOrder.region_id == SysRegion.id)
            .filter(
                and_(
                    BizOrder.is_del == 0,
                    BizOrder.delivery_status == "pending_dispatch",
                    SysRegion.is_del == 0,
                    *region_active_clause,
                    *region_clause,
                )
            )
            .order_by(desc(BizOrder.create_at))
            .limit(2)
            .all()
        )
        delivery_lines = [o.order_no for o in pd_preview]

        cancel_preview = (
            db.query(BizOrder)
            .join(SysRegion, BizOrder.region_id == SysRegion.id)
            .filter(
                and_(
                    BizOrder.is_del == 0,
                    BizOrder.cancel_status == "pending_review",
                    SysRegion.is_del == 0,
                    *region_active_clause,
                    *region_clause,
                )
            )
            .order_by(desc(BizOrder.update_at))
            .limit(2)
            .all()
        )
        cancel_client_ids = list({o.client_id for o in cancel_preview if o.client_id})
        cancel_clients = {}
        if cancel_client_ids:
            for u in (
                db.query(SysUser)
                .filter(and_(SysUser.is_del == 0, SysUser.id.in_(cancel_client_ids)))
                .all()
            ):
                cancel_clients[u.id] = u.real_name or u.phone or u.id
        cancel_lines = []
        for o in cancel_preview:
            nm = cancel_clients.get(o.client_id, "")
            cancel_lines.append(f"{o.order_no}" + (f" · {nm[:16]}" if nm else ""))

        settle_preview = (
            db.query(BizOrder)
            .join(SysRegion, BizOrder.region_id == SysRegion.id)
            .filter(
                and_(
                    BizOrder.is_del == 0,
                    BizOrder.client_settlement_status == "pending",
                    BizOrder.pay_status == "paid",
                    BizOrder.order_status.in_(["pending_confirm", "completed"]),
                    SysRegion.is_del == 0,
                    *region_active_clause,
                    *region_clause,
                )
            )
            .order_by(desc(BizOrder.update_at))
            .limit(2)
            .all()
        )
        settle_client_ids = list({o.client_id for o in settle_preview if o.client_id})
        settle_clients = {}
        if settle_client_ids:
            for u in (
                db.query(SysUser)
                .filter(and_(SysUser.is_del == 0, SysUser.id.in_(settle_client_ids)))
                .all()
            ):
                settle_clients[u.id] = u.real_name or u.phone or u.id
        settle_lines = []
        for o in settle_preview:
            nm = settle_clients.get(o.client_id, "")
            settle_lines.append(f"{o.order_no}" + (f" · {nm[:16]}" if nm else ""))

        pending_settlement_count = (
            db.query(BizOrder)
            .join(SysRegion, BizOrder.region_id == SysRegion.id)
            .filter(
                and_(
                    BizOrder.is_del == 0,
                    BizOrder.client_settlement_status == "pending",
                    BizOrder.pay_status == "paid",
                    BizOrder.order_status.in_(["pending_confirm", "completed"]),
                    SysRegion.is_del == 0,
                    *region_active_clause,
                    *region_clause,
                )
            )
            .count()
        )

        pending_cancel_count = (
            db.query(BizOrder)
            .join(SysRegion, BizOrder.region_id == SysRegion.id)
            .filter(
                and_(
                    BizOrder.is_del == 0,
                    BizOrder.cancel_status == "pending_review",
                    SysRegion.is_del == 0,
                    *region_active_clause,
                    *region_clause,
                )
            )
            .count()
        )

        # 最新订单（工作台列表）
        recent_rows = (
            db.query(BizOrder)
            .join(SysRegion, BizOrder.region_id == SysRegion.id)
            .filter(and_(BizOrder.is_del == 0, SysRegion.is_del == 0, *region_active_clause, *region_clause))
            .order_by(desc(BizOrder.create_at))
            .limit(8)
            .all()
        )
        rid_set = list({o.region_id for o in recent_rows if o.region_id})
        cid_set = list({o.client_id for o in recent_rows if o.client_id})
        region_name_map = {}
        if rid_set:
            for r in db.query(SysRegion).filter(
                and_(SysRegion.is_del == 0, *region_active_clause, SysRegion.id.in_(rid_set))
            ).all():
                region_name_map[r.id] = r.name
        client_name_map = {}
        if cid_set:
            for u in db.query(SysUser).filter(and_(SysUser.is_del == 0, SysUser.id.in_(cid_set))).all():
                client_name_map[u.id] = u.real_name or u.phone or "-"

        recent_orders = []
        for o in recent_rows:
            order_status = getattr(o, "order_status", None) or "pending_payment"
            recent_orders.append(
                {
                    "id": o.id,
                    "orderNo": o.order_no,
                    "clientName": client_name_map.get(o.client_id, "-"),
                    "regionName": region_name_map.get(o.region_id, "-"),
                    "totalAmount": float(o.total_amount) if o.total_amount else 0,
                    "orderStatus": order_status,
                    "cancelStatus": getattr(o, "cancel_status", None) or "none",
                    "deliveryStatus": getattr(o, "delivery_status", None) or "pending_dispatch",
                    "createAt": str(o.create_at) if o.create_at else None,
                }
            )

        todo_snapshots = [
            {
                "type": "quotation",
                "title": "今日新提交报价",
                "count": pending_quotation_count,
                "items": quotation_lines,
            },
            {
                "type": "delivery",
                "title": "待分配配送",
                "count": pending_delivery_count,
                "items": delivery_lines,
            },
            {
                "type": "cancel",
                "title": "取消待审核",
                "count": pending_cancel_count,
                "items": cancel_lines,
            },
            {
                "type": "settlement",
                "title": "待客户结算",
                "count": pending_settlement_count,
                "items": settle_lines,
            },
        ]

        # “客户总数/供应商总数”口径：统计用户表（sys_user）并按 region_id 区分
        # - region_id 为空：不过滤全局
        # - region_id 传入：按 SysUser.region_id 过滤
        customer_count = (
            db.query(SysUser)
            .join(SysRegion, SysUser.region_id == SysRegion.id)
            .filter(
                and_(
                    SysUser.is_del == 0,
                    SysUser.role == "client",
                    SysRegion.is_del == 0,
                    *region_active_clause,
                    *([SysUser.region_id == region_id] if region_id else []),
                )
            )
            .count()
        )

        supplier_count = (
            db.query(SysUser)
            .join(SysRegion, SysUser.region_id == SysRegion.id)
            .filter(
                and_(
                    SysUser.is_del == 0,
                    SysUser.role == "supplier",
                    SysRegion.is_del == 0,
                    *region_active_clause,
                    *([SysUser.region_id == region_id] if region_id else []),
                )
            )
            .count()
        )

        return {
            "todayOrderCount": today_order_count,
            "todayOrderAmount": today_order_amount,
            "monthOrderCount": len(month_orders),
            "monthOrderAmount": round(sum(float(o.total_amount or 0) for o in month_orders), 2),
            "pendingDeliveryCount": pending_delivery_count,
            "completedOrderCount": sum(
                1 for o in month_orders if (getattr(o, "order_status", None) or o.status) == "completed"
            ),
            "activePriceListCount": (
                db.query(BizPriceList)
                .join(SysRegion, BizPriceList.region_id == SysRegion.id)
                .filter(
                    and_(
                        BizPriceList.is_del == 0,
                        BizPriceList.status == "active",
                        SysRegion.is_del == 0,
                        *region_active_clause,
                        *([BizPriceList.region_id == region_id] if region_id else []),
                    )
                )
                .count()
            ),
            "customerCount": customer_count,
            "supplierCount": supplier_count,
            "todayOrderCountHint": _delta_hint(today_order_count, prev_count, " 单"),
            "todayOrderAmountHint": _amount_delta_hint(today_order_amount, prev_amount),
            "deliveryWarningCount": delivery_warning_count,
            "recentOrders": recent_orders,
            "todoSnapshots": todo_snapshots,
        }


statistics_crud = StatisticsCRUD()