sqxp/backend/app/crud/settlement.py

"""结算与退款CRUD"""
from typing import Optional, List, Tuple
from datetime import datetime
from decimal import Decimal
from sqlalchemy.orm import Session
from sqlalchemy import and_, func

from app.models.models import BizSettlement, BizSettlementHis, BizRefund, BizRefundHis, BizOrder
from app.crud.base import CRUDBase
from app.crud.order import order_crud
from app.core.utils import generate_id
from app.core.order_status_compat import derive_legacy_status_from_three


class SettlementCRUD(CRUDBase[BizSettlement]):
    """结算记录CRUD操作"""

    def get_multi_with_filters(
        self,
        db: Session,
        region_id: str = None,
        client_id: str = None,
        status: str = None,
        start_date: str = None,
        end_date: str = None,
        skip: int = 0,
        limit: int = 10
    ) -> Tuple[List[BizSettlement], int]:
        """多条件查询结算记录"""
        query = db.query(BizSettlement).filter(BizSettlement.is_del == 0)

        if region_id:
            query = query.filter(BizSettlement.region_id == region_id)
        if client_id:
            query = query.filter(BizSettlement.client_id == client_id)
        if status:
            query = query.filter(BizSettlement.status == status)
        if start_date:
            query = query.filter(BizSettlement.create_at >= start_date)
        if end_date:
            query = query.filter(BizSettlement.create_at <= end_date)

        total = query.count()
        settlements = query.order_by(BizSettlement.create_at.desc()).offset(skip).limit(limit).all()
        return settlements, total

    def get_by_order(self, db: Session, order_id: str) -> List[BizSettlement]:
        """根据订单获取结算记录"""
        return db.query(BizSettlement).filter(
            and_(
                BizSettlement.order_id == order_id,
                BizSettlement.is_del == 0
            )
        ).all()

    def create_settlement(
        self,
        db: Session,
        order_id: str,
        client_id: str,
        region_id: str,
        amount: Decimal,
        settlement_method: str,
        settlement_time: datetime = None,
        attachment_url: str = None,
        note: str = None,
        creator: dict = None
    ) -> BizSettlement:
        """创建结算记录"""
        settlement = BizSettlement(
            id=generate_id(),
            order_id=order_id,
            client_id=client_id,
            region_id=region_id,
            amount=amount,
            settlement_method=settlement_method,
            settlement_time=settlement_time or datetime.now(),
            settlement_file_url=attachment_url,
            settlement_note=note,
            status="confirmed",
            reviewed_by=creator.get("user_id") if creator else None,
            reviewed_time=datetime.now(),
            create_by=creator.get("user_id") if creator else None,
            create_name=creator.get("real_name") if creator else None
        )
        db.add(settlement)
        db.flush()

        # 更新订单的已付金额
        order = db.query(BizOrder).filter(BizOrder.id == order_id).first()
        if order:
            order.paid_amount = (order.paid_amount or Decimal("0")) + amount
            if order.paid_amount >= order.receivable_amount:
                order.paid_amount = order.receivable_amount
            order.update_at = datetime.now()
            order_crud.sync_pay_fields_from_amounts(order)

        db.commit()
        db.refresh(settlement)
        return settlement

    def batch_create_settlements(
        self,
        db: Session,
        order_ids: List[str],
        settlement_method: str,
        settlement_time: datetime = None,
        attachment_url: str = None,
        note: str = None,
        creator: dict = None
    ) -> List[BizSettlement]:
        """批量创建结算记录"""
        settlements = []
        for order_id in order_ids:
            order = db.query(BizOrder).filter(
                and_(
                    BizOrder.id == order_id,
                    BizOrder.is_del == 0
                )
            ).first()

            if order and order.status not in ["cancelled", "completed"]:
                # 计算未付金额
                unpaid_amount = (order.receivable_amount or Decimal("0")) - (order.paid_amount or Decimal("0"))

                settlement = BizSettlement(
                    id=generate_id(),
                    order_id=order_id,
                    client_id=order.client_id,
                    region_id=order.region_id,
                    amount=unpaid_amount,
                    settlement_method=settlement_method,
                    settlement_time=settlement_time or datetime.now(),
                    settlement_file_url=attachment_url,
                    settlement_note=note,
                    status="confirmed",
                    reviewed_by=creator.get("user_id") if creator else None,
                    reviewed_time=datetime.now(),
                    create_by=creator.get("user_id") if creator else None,
                    create_name=creator.get("real_name") if creator else None
                )
                db.add(settlement)
                settlements.append(settlement)

                # 更新订单的已付金额
                order.paid_amount = order.receivable_amount
                order.update_at = datetime.now()
                order_crud.sync_pay_fields_from_amounts(order)

        db.commit()
        return settlements

    def confirm_settlement(
        self,
        db: Session,
        settlement_id: str,
        reviewer_id: str,
        note: str = None
    ) -> Optional[BizSettlement]:
        """确认结算"""
        settlement = self.get(db, settlement_id)
        if not settlement:
            return None

        if settlement.status != "pending":
            return None

        settlement.status = "confirmed"
        settlement.reviewed_by = reviewer_id
        settlement.reviewed_time = datetime.now()
        if note:
            settlement.settlement_note = note
        settlement.update_at = datetime.now()

        # 更新订单的已付金额
        order = db.query(BizOrder).filter(BizOrder.id == settlement.order_id).first()
        if order:
            order.paid_amount = (order.paid_amount or Decimal("0")) + settlement.amount
            if order.paid_amount >= order.receivable_amount:
                order.paid_amount = order.receivable_amount
            order.update_at = datetime.now()
            order_crud.sync_pay_fields_from_amounts(order)

        db.commit()
        db.refresh(settlement)
        return settlement

    def update_with_history(self, db: Session, id: str, obj_in: dict, updater: dict = None) -> Optional[BizSettlement]:
        """更新结算记录并记录历史"""
        original = self.get(db, id)
        if not original:
            return None

        his_data = {
            "id": original.id,
            "order_id": original.order_id,
            "client_id": original.client_id,
            "region_id": original.region_id,
            "amount": original.amount,
            "settlement_method": original.settlement_method,
            "settlement_time": original.settlement_time,
            "settlement_file_url": original.settlement_file_url,
            "settlement_note": original.settlement_note,
            "status": original.status,
            "reviewed_by": original.reviewed_by,
            "reviewed_time": original.reviewed_time,
            "create_at": original.create_at,
            "create_by": original.create_by,
            "create_name": original.create_name,
            "update_at": original.update_at,
            "update_by": original.update_by,
        }
        his = BizSettlementHis(**his_data)
        db.add(his)

        return self.update(db, id, obj_in, updater)

    def get_client_summary(
        self,
        db: Session,
        region_id: str = None,
        client_id: str = None,
        skip: int = 0,
        limit: int = 10
    ) -> Tuple[List[dict], int]:
        """获取客户待结算汇总"""
        query = db.query(
            BizOrder.client_id,
            func.count(BizOrder.id).label("order_count"),
            func.sum(BizOrder.receivable_amount - BizOrder.paid_amount).label("total_unpaid")
        ).filter(
            and_(
                BizOrder.is_del == 0,
                BizOrder.receivable_amount > BizOrder.paid_amount,
                BizOrder.status.notin_(["cancelled"])
            )
        )

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

        query = query.group_by(BizOrder.client_id)

        total = query.count()
        results = query.offset(skip).limit(limit).all()

        return [{"client_id": r.client_id, "order_count": r.order_count, "total_unpaid": r.total_unpaid or Decimal("0")} for r in results], total

    def get_statistics(
        self,
        db: Session,
        region_id: str = None,
        start_date: str = None,
        end_date: str = None
    ) -> dict:
        """获取结算统计数据"""
        # 待结算金额（使用单条SQL查询，避免N+1问题）
        pending_query = db.query(
            func.count(BizOrder.id).label("order_count"),
            func.coalesce(func.sum(BizOrder.receivable_amount - BizOrder.paid_amount), 0).label("pending_amount")
        ).filter(
            and_(
                BizOrder.is_del == 0,
                BizOrder.receivable_amount > BizOrder.paid_amount,
                BizOrder.status.notin_(["cancelled", "completed"])
            )
        )
        if region_id:
            pending_query = pending_query.filter(BizOrder.region_id == region_id)
        pending_result = pending_query.first()

        # 已结算金额
        settled_query = db.query(
            func.count(BizSettlement.id).label("settlement_count"),
            func.coalesce(func.sum(BizSettlement.amount), 0).label("settled_amount")
        ).filter(
            and_(
                BizSettlement.is_del == 0,
                BizSettlement.status == "confirmed"
            )
        )
        if region_id:
            settled_query = settled_query.filter(BizSettlement.region_id == region_id)
        if start_date:
            settled_query = settled_query.filter(BizSettlement.reviewed_time >= start_date)
        if end_date:
            settled_query = settled_query.filter(BizSettlement.reviewed_time <= end_date)
        settled_result = settled_query.first()

        # 待处理退款数
        refund_pending_count = db.query(func.count(BizRefund.id)).filter(
            and_(
                BizRefund.is_del == 0,
                BizRefund.status.in_(["pending", "processing"])
            )
        )
        if region_id:
            refund_pending_count = refund_pending_count.join(
                BizOrder, BizOrder.id == BizRefund.order_id
            ).filter(BizOrder.region_id == region_id)
        refund_pending_count = refund_pending_count.scalar() or 0

        return {
            "pendingOrderCount": pending_result.order_count or 0,
            "pendingAmount": float(pending_result.pending_amount or 0),
            "settledCount": settled_result.settlement_count or 0,
            "settledAmount": float(settled_result.settled_amount or 0),
            "refundPendingCount": refund_pending_count
        }


class RefundCRUD(CRUDBase[BizRefund]):
    """退款记录CRUD操作"""

    def get_multi_with_filters(
        self,
        db: Session,
        region_id: str = None,
        order_id: str = None,
        order_no: str = None,
        status: str = None,
        refund_method: str = None,
        start_date: str = None,
        end_date: str = None,
        skip: int = 0,
        limit: int = 10
    ) -> Tuple[List[BizRefund], int]:
        """多条件查询退款记录"""
        query = db.query(BizRefund).filter(BizRefund.is_del == 0)

        if region_id:
            # 需要关联订单获取区域
            query = query.join(BizOrder, BizOrder.id == BizRefund.order_id).filter(
                BizOrder.region_id == region_id
            )
        if order_id:
            query = query.filter(BizRefund.order_id == order_id)
        if order_no:
            query = query.join(BizOrder, BizOrder.id == BizRefund.order_id).filter(
                BizOrder.order_no.like(f"%{order_no}%")
            )
        if status:
            query = query.filter(BizRefund.status == status)
        if refund_method:
            query = query.filter(BizRefund.refund_method == refund_method)
        if start_date:
            query = query.filter(BizRefund.create_at >= start_date)
        if end_date:
            query = query.filter(BizRefund.create_at <= end_date)

        total = query.count()
        refunds = query.order_by(BizRefund.create_at.desc()).offset(skip).limit(limit).all()
        return refunds, total

    def get_by_order(self, db: Session, order_id: str) -> List[BizRefund]:
        """根据订单获取退款记录"""
        return db.query(BizRefund).filter(
            and_(
                BizRefund.order_id == order_id,
                BizRefund.is_del == 0
            )
        ).all()

    def get_by_refund_no(self, db: Session, refund_no: str) -> Optional[BizRefund]:
        """根据退款单号获取退款记录"""
        return db.query(BizRefund).filter(
            and_(
                BizRefund.refund_no == refund_no,
                BizRefund.is_del == 0
            )
        ).first()

    def create_refund(
        self,
        db: Session,
        order_id: str,
        refund_amount: Decimal,
        refund_method: str,
        reason: str = None,
        creator: dict = None
    ) -> BizRefund:
        """创建退款记录"""
        refund_no = self._generate_refund_no(db)

        refund = BizRefund(
            id=generate_id(),
            order_id=order_id,
            refund_no=refund_no,
            refund_amount=refund_amount,
            refund_method=refund_method,
            refund_result="processing",
            status="pending",
            create_by=creator.get("user_id") if creator else None,
            create_name=creator.get("real_name") if creator else None
        )
        db.add(refund)

        # 更新订单的退款金额和状态
        order = db.query(BizOrder).filter(BizOrder.id == order_id).first()
        if order:
            order.refund_amount = (order.refund_amount or Decimal("0")) + refund_amount
            order.update_at = datetime.now()

            # 如果是微信退款，更新订单状态为退款中
            if refund_method == "wechat":
                order.pay_status = "refunding"
                order.status = derive_legacy_status_from_three(
                    order_status=order.order_status,
                    delivery_status=order.delivery_status,
                    pay_status=order.pay_status,
                )

        db.commit()
        db.refresh(refund)
        return refund

    def process_refund(
        self,
        db: Session,
        refund_id: str,
        refund_result: str,
        refund_no_wechat: str = None,
        updater: dict = None
    ) -> Optional[BizRefund]:
        """处理退款（成功/失败）"""
        refund = self.get(db, refund_id)
        if not refund:
            return None

        refund.refund_result = refund_result
        refund.refund_time = datetime.now()
        if refund_no_wechat:
            refund.refund_no_wechat = refund_no_wechat

        if refund_result == "success":
            refund.status = "completed"
            # 更新订单退款金额
            order = db.query(BizOrder).filter(BizOrder.id == refund.order_id).first()
            if order:
                order.refund_amount = (order.refund_amount or Decimal("0")) + refund.refund_amount
                order.paid_amount = max(Decimal("0"), (order.paid_amount or Decimal("0")) - refund.refund_amount)
                order.pay_status = "refunded" if (order.paid_amount or Decimal("0")) <= Decimal("0") else "paid"
                order.status = derive_legacy_status_from_three(
                    order_status=order.order_status,
                    delivery_status=order.delivery_status,
                    pay_status=order.pay_status,
                )
                order.update_at = datetime.now()

                # 如果退款金额等于已支付金额，订单变为已取消
                if order.refund_amount >= (order.total_amount - order.paid_amount + order.refund_amount):
                    # 保留部分退款状态
                    pass
        else:
            refund.status = "failed"
            # 恢复订单状态
            order = db.query(BizOrder).filter(BizOrder.id == refund.order_id).first()
            if order and order.pay_status == "refunding":
                order.pay_status = "paid" if (order.paid_amount or Decimal("0")) > Decimal("0") else "pending_payment"
                order.status = derive_legacy_status_from_three(
                    order_status=order.order_status,
                    delivery_status=order.delivery_status,
                    pay_status=order.pay_status,
                )

        refund.update_at = datetime.now()
        db.commit()
        db.refresh(refund)
        return refund

    def handle_refund_failure(
        self,
        db: Session,
        refund_id: str,
        fail_reason: str
    ) -> Optional[BizRefund]:
        """处理退款失败"""
        refund = self.get(db, refund_id)
        if not refund:
            return None

        refund.refund_result = "failed"
        refund.refund_fail_reason = fail_reason
        refund.refund_time = datetime.now()
        refund.status = "failed"
        refund.update_at = datetime.now()

        # 恢复订单状态
        order = db.query(BizOrder).filter(BizOrder.id == refund.order_id).first()
        if order and order.pay_status == "refunding":
            order.pay_status = "paid" if (order.paid_amount or Decimal("0")) > Decimal("0") else "pending_payment"
            order.status = derive_legacy_status_from_three(
                order_status=order.order_status,
                delivery_status=order.delivery_status,
                pay_status=order.pay_status,
            )
            order.update_at = datetime.now()

        db.commit()
        db.refresh(refund)
        return refund

    def _generate_refund_no(self, db: Session) -> str:
        """生成退款单号"""
        today = datetime.now().strftime("%Y%m%d")
        prefix = f"REF{today}"

        last_refund = db.query(BizRefund).filter(
            BizRefund.refund_no.like(f"{prefix}%")
        ).order_by(BizRefund.refund_no.desc()).first()

        if last_refund:
            last_no = int(last_refund.refund_no[-4:])
            new_no = last_no + 1
        else:
            new_no = 1

        return f"{prefix}{str(new_no).zfill(4)}"

    def update_with_history(self, db: Session, id: str, obj_in: dict, updater: dict = None) -> Optional[BizRefund]:
        """更新退款记录并记录历史"""
        original = self.get(db, id)
        if not original:
            return None

        his_data = {
            "id": original.id,
            "order_id": original.order_id,
            "refund_no": original.refund_no,
            "refund_amount": original.refund_amount,
            "refund_method": original.refund_method,
            "refund_no_wechat": original.refund_no_wechat,
            "refund_time": original.refund_time,
            "refund_result": original.refund_result,
            "refund_fail_reason": original.refund_fail_reason,
            "status": original.status,
            "create_at": original.create_at,
            "create_by": original.create_by,
            "create_name": original.create_name,
            "update_at": original.update_at,
            "update_by": original.update_by,
        }
        his = BizRefundHis(**his_data)
        db.add(his)

        return self.update(db, id, obj_in, updater)

    def get_refund_statistics(
        self,
        db: Session,
        start_date: str = None,
        end_date: str = None
    ) -> dict:
        """获取退款统计数据"""
        base_query = db.query(BizRefund).filter(
            and_(
                BizRefund.is_del == 0,
                BizRefund.status == "completed"
            )
        )

        if start_date:
            base_query = base_query.filter(BizRefund.refund_time >= start_date)
        if end_date:
            base_query = base_query.filter(BizRefund.refund_time <= end_date)

        # 成功退款金额
        success_amount = base_query.filter(
            BizRefund.refund_result == "success"
        ).func.sum(BizRefund.refund_amount).scalar() or Decimal("0")

        # 失败退款金额
        failed_amount = base_query.filter(
            BizRefund.refund_result == "failed"
        ).func.sum(BizRefund.refund_amount).scalar() or Decimal("0")

        # 待处理退款数
        pending_count = db.query(func.count(BizRefund.id)).filter(
            and_(
                BizRefund.is_del == 0,
                BizRefund.status.in_(["pending", "processing"])
            )
        ).scalar() or 0

        # 退款失败数
        failed_count = db.query(func.count(BizRefund.id)).filter(
            and_(
                BizRefund.is_del == 0,
                BizRefund.status == "failed"
            )
        ).scalar() or 0

        return {
            "successAmount": float(success_amount),
            "failedAmount": float(failed_amount),
            "pendingCount": pending_count,
            "failedCount": failed_count
        }


settlement_crud = SettlementCRUD(BizSettlement)
refund_crud = RefundCRUD(BizRefund)