sqxp/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
)


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
    ) -> 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)

            query = db.query(BizOrder).filter(
                and_(
                    BizOrder.is_del == 0,
                    BizOrder.create_at >= day_start,
                    BizOrder.create_at < day_end
                )
            )

            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_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 get_dashboard_summary(
        db: Session,
        region_id: str = None
    ) -> Dict[str, Any]:
        """获取仪表盘汇总数据"""
        today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
        month_start = today.replace(day=1)

        order_query = db.query(BizOrder).filter(
            and_(BizOrder.is_del == 0, BizOrder.create_at >= month_start)
        )
        if region_id:
            order_query = order_query.filter(BizOrder.region_id == region_id)

        month_orders = order_query.all()
        today_orders = [o for o in month_orders if o.create_at >= today]

        pending_delivery_count = db.query(BizOrder).filter(
            and_(
                BizOrder.is_del == 0,
                BizOrder.delivery_status == "pending_dispatch"
            )
        )
        if region_id:
            pending_delivery_count = pending_delivery_count.filter(BizOrder.region_id == region_id)

        return {
            "todayOrderCount": len(today_orders),
            "todayOrderAmount": round(sum(float(o.total_amount or 0) for o in today_orders), 2),
            "monthOrderCount": len(month_orders),
            "monthOrderAmount": round(sum(float(o.total_amount or 0) for o in month_orders), 2),
            "pendingDeliveryCount": pending_delivery_count.count(),
            "completedOrderCount": sum(1 for o in month_orders if o.status == "completed"),
            "activePriceListCount": db.query(BizPriceList).filter(
                and_(BizPriceList.is_del == 0, BizPriceList.status == "active")
            ).count(),
            "customerCount": db.query(SysUser).filter(
                and_(SysUser.is_del == 0, SysUser.role == "client")
            ).count(),
            "supplierCount": db.query(SysUser).filter(
                and_(SysUser.is_del == 0, SysUser.role == "supplier")
            ).count()
        }


statistics_crud = StatisticsCRUD()