sqxp/backend/app/crud/order.py

"""订单CRUD"""
from typing import Optional, List
import json
from datetime import datetime
from decimal import Decimal
from sqlalchemy.orm import Session
from sqlalchemy import and_, or_, func, select, exists

from app.models.models import BizOrder, BizOrderItem, BizOrderStatusLog, BizOrderHis, BizOrderAdjust, SysUser
from app.crud.base import CRUDBase
from app.core.utils import generate_id
from app.core.order_status_compat import derive_three_status_from_legacy, derive_legacy_status_from_three

# 订单编辑锁超时（秒）；超时后允许他人抢锁
ORDER_EDIT_LOCK_TTL_SECONDS = 300


class OrderCRUD(CRUDBase[BizOrder]):
    @staticmethod
    def normalize_payment_method(payment_method: Optional[str]) -> str:
        """统一支付方式值，仅保留 online/offline。"""
        method = (payment_method or "").strip().lower()
        if method in ("wechat", "wx", "online"):
            return "online"
        if method == "offline":
            return "offline"
        return "online"

    def sync_pay_fields_from_amounts(self, order: BizOrder) -> None:
        """根据已付/应收同步 pay_status、payment_time 与兼容字段 status（付清后才是 paid）。"""
        pa = order.paid_amount or Decimal("0")
        ra = order.receivable_amount or Decimal("0")
        if ra > 0 and pa >= ra:
            order.pay_status = "paid"
            if not order.payment_time:
                order.payment_time = datetime.now()
        else:
            order.pay_status = "pending_payment"
        order.status = derive_legacy_status_from_three(
            order_status=getattr(order, "order_status", None) or "pending_confirm",
            delivery_status=getattr(order, "delivery_status", None) or "pending_dispatch",
            pay_status=order.pay_status,
        )

    """订单CRUD操作"""

    def _sync_three_status_fields_from_legacy(self, order: BizOrder):
        """阶段A：保持三字段与旧 status 一致（先兼容，再逐步迁移逻辑）。"""
        ts = derive_three_status_from_legacy(order.status, paid_amount=order.paid_amount)
        order.order_status = ts.order_status
        order.delivery_status = ts.delivery_status
        order.pay_status = ts.pay_status

    def _apply_three_status(self, order: BizOrder, *, order_status: str = None, delivery_status: str = None, pay_status: str = None):
        """阶段D收尾：优先写三字段，再回写兼容 status。"""
        if order_status is not None:
            order.order_status = order_status
        if delivery_status is not None:
            order.delivery_status = delivery_status
        if pay_status is not None:
            order.pay_status = pay_status
        order.status = derive_legacy_status_from_three(
            order_status=order.order_status,
            delivery_status=order.delivery_status,
            pay_status=order.pay_status,
        )

    def get_by_client(self, db: Session, client_id: str) -> List[BizOrder]:
        """根据客户获取订单列表"""
        return db.query(BizOrder).filter(
            and_(
                BizOrder.client_id == client_id,
                BizOrder.is_del == 0
            )
        ).order_by(BizOrder.create_at.desc()).all()

    def get_multi_with_filters(
        self,
        db: Session,
        client_id: str = None,
        region_id: str = None,
        status: str = None,
        order_status: str = None,
        delivery_status: str = None,
        pay_status: str = None,
        payment_method: str = None,
        cancel_status: str = None,
        order_no: str = None,
        keyword: str = None,
        start_date: str = None,
        end_date: str = None,
        skip: int = 0,
        limit: int = 10,
        list_tab: str = None,
    ) -> tuple[List[BizOrder], int]:
        """多条件查询订单"""
        query = db.query(BizOrder).filter(BizOrder.is_del == 0)

        if client_id:
            query = query.filter(BizOrder.client_id == client_id)
        if region_id:
            query = query.filter(BizOrder.region_id == region_id)
        if status:
            query = query.filter(BizOrder.status == status)
        if order_status:
            query = query.filter(BizOrder.order_status == order_status)
        if delivery_status:
            query = query.filter(BizOrder.delivery_status == delivery_status)
        if pay_status:
            query = query.filter(BizOrder.pay_status == pay_status)
        if payment_method:
            pm = self.normalize_payment_method(payment_method)
            if pm == "offline":
                query = query.filter(BizOrder.payment_method == "offline")
            else:
                query = query.filter(
                    or_(
                        BizOrder.payment_method == "online",
                        BizOrder.payment_method.is_(None),
                        BizOrder.payment_method == "",
                    )
                )
        if cancel_status:
            query = query.filter(BizOrder.cancel_status == cancel_status)
        if order_no:
            query = query.filter(BizOrder.order_no.like(f"%{order_no}%"))
        if keyword:
            kw = (keyword or "").strip()
            if kw:
                pattern = f"%{kw}%"
                client_user_match = exists(
                    select(1).select_from(SysUser).where(
                        and_(
                            SysUser.id == BizOrder.client_id,
                            SysUser.is_del == 0,
                            or_(
                                SysUser.real_name.like(pattern),
                                SysUser.username.like(pattern),
                                SysUser.phone.like(pattern),
                            ),
                        )
                    )
                )
                query = query.filter(
                    or_(
                        BizOrder.order_no.like(pattern),
                        BizOrder.contact_person.like(pattern),
                        BizOrder.contact_phone.like(pattern),
                        client_user_match,
                    )
                )
        if start_date:
            query = query.filter(BizOrder.create_at >= start_date)
        if end_date:
            query = query.filter(BizOrder.create_at <= end_date)

        # 客户端订单列表 Tab（与小程序 order.vue 分段一致，保证分页有效）
        if list_tab == "pending_payment":
            query = query.filter(
                and_(
                    BizOrder.order_status == "pending_payment",
                    BizOrder.pay_status == "pending_payment",
                    BizOrder.cancel_status != "pending_review",
                )
            )
        elif list_tab == "pending_confirm":
            query = query.filter(
                and_(
                    BizOrder.order_status == "pending_confirm",
                    BizOrder.cancel_status != "pending_review",
                )
            )
        elif list_tab == "completed":
            query = query.filter(BizOrder.order_status == "completed")
        elif list_tab == "cancelled":
            query = query.filter(BizOrder.order_status == "cancelled")

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

    def get_by_order_no(self, db: Session, order_no: str) -> Optional[BizOrder]:
        """根据订单号获取订单"""
        return db.query(BizOrder).filter(
            and_(
                BizOrder.order_no == order_no,
                BizOrder.is_del == 0
            )
        ).first()

    def create_order(
        self,
        db: Session,
        client_id: str,
        region_id: str,
        price_list_id: str,
        price_version: int,
        items: List[dict],
        address_id: str,
        address_info: dict,
        payment_method: str,
        remark: str = None,
        creator: dict = None
    ) -> BizOrder:
        """创建订单"""
        # 生成订单号
        order_no = self._generate_order_no(db)

        # 计算订单总金额
        total_amount = sum(
            Decimal(str(item.get("quantity", 0))) * Decimal(str(item.get("unitPrice", 0)))
            for item in items
        )

        normalized_payment_method = self.normalize_payment_method(payment_method)

        # 线上：未支付前主状态为待支付；线下：未收款前 pay_status 仍为待支付，但主状态进入待收货/履约阶段，可与 pending_dispatch 一并参与分配配送
        if normalized_payment_method == "offline":
            init_order_status = "pending_confirm"
        else:
            init_order_status = "pending_payment"
        init_delivery_status = "pending_dispatch"
        init_pay_status = "pending_payment"
        order = BizOrder(
            id=generate_id(),
            order_no=order_no,
            region_id=region_id,
            client_id=client_id,
            price_list_id=price_list_id,
            price_version=price_version,
            status=derive_legacy_status_from_three(
                order_status=init_order_status,
                delivery_status=init_delivery_status,
                pay_status=init_pay_status,
            ),
            order_status=init_order_status,
            delivery_status=init_delivery_status,
            pay_status=init_pay_status,
            cancel_status="none",
            payment_method=normalized_payment_method,
            client_settlement_status="settled" if normalized_payment_method == "online" else "pending",
            supplier_settlement_status="pending",
            client_settlement_at=datetime.now() if normalized_payment_method == "online" else None,
            supplier_settlement_at=None,
            total_amount=total_amount,
            paid_amount=Decimal("0"),
            receivable_amount=total_amount,
            refund_amount=Decimal("0"),
            contact_person=address_info.get("contactPerson"),
            contact_phone=address_info.get("contactPhone"),
            delivery_address=address_info.get("fullAddress"),
            remark=remark,
            payment_time=None,
            create_by=creator.get("user_id") if creator else None,
            create_name=creator.get("real_name") if creator else None
        )
        db.add(order)
        db.flush()

        # 创建订单项
        for item in items:
            amount = Decimal(str(item.get("quantity", 0))) * Decimal(str(item.get("unitPrice", 0)))
            order_item = BizOrderItem(
                id=generate_id(),
                order_id=order.id,
                product_id=item.get("productId"),
                unit_id=item.get("unitId"),
                quantity=Decimal(str(item.get("quantity", 0))),
                unit_price=Decimal(str(item.get("unitPrice", 0))),
                amount=amount,
                delivered_quantity=Decimal("0"),
                status="normal",
                note=item.get("note"),
                create_by=creator.get("user_id") if creator else None,
                create_name=creator.get("real_name") if creator else None
            )
            db.add(order_item)

        # 创建订单状态日志
        self._create_status_log(
            db,
            order.id,
            None,
            order.status,
            creator.get("user_id") if creator else client_id,
            "client",
            "创建订单"
        )

        db.commit()
        db.refresh(order)
        return order

    def update_status(
        self,
        db: Session,
        order_id: str,
        new_status: str,
        operator_id: str,
        operator_role: str,
        reason: str = None,
        note: str = None
    ) -> Optional[BizOrder]:
        """更新订单状态"""
        order = self.get(db, order_id)
        if not order:
            return None

        old_status = order.status
        order.status = new_status
        order.update_at = datetime.now()

        # 特殊状态处理
        if new_status == "paid":
            order.payment_time = datetime.now()
        elif new_status == "completed":
            order.completed_time = datetime.now()
            order.completed_by = operator_id

        # 阶段A：同步三字段
        self._sync_three_status_fields_from_legacy(order)

        # 创建状态变更日志
        self._create_status_log(
            db,
            order_id,
            old_status,
            new_status,
            operator_id,
            operator_role,
            reason,
            note
        )

        db.commit()
        db.refresh(order)
        return order

    def cancel_order(
        self,
        db: Session,
        order_id: str,
        cancel_type: str,
        reason: str,
        operator_id: str,
        operator_role: str
    ) -> Optional[BizOrder]:
        """取消订单"""
        order = self.get(db, order_id)
        if not order:
            return None

        # 待支付订单直接取消
        if order.status == "pending_payment":
            return self.update_status(
                db,
                order_id,
                "cancelled",
                operator_id,
                operator_role,
                reason=reason
            )

        # 已支付订单申请取消
        if order.delivery_status in ["pending_dispatch", "delivering"]:
            self._apply_three_status(order, order_status="pending_confirm")
            order.cancel_status = "pending_review"
            order.cancel_reason = reason
            order.update_at = datetime.now()

            self._create_status_log(
                db,
                order_id,
                order.status,
                "pending_review_cancel",  # 兼容历史日志标识
                operator_id,
                operator_role,
                reason=reason
            )

            db.commit()
            db.refresh(order)
            return order

        return None

    def review_cancel(
        self,
        db: Session,
        order_id: str,
        approved: bool,
        reviewer_id: str,
        review_note: str = None
    ) -> Optional[BizOrder]:
        """审核取消订单"""
        order = self.get(db, order_id)
        if not order or order.cancel_status != "pending_review":
            return None

        if approved:
            order.cancel_status = "approved"
            self._apply_three_status(order, order_status="cancelled")
            new_status = order.status
        else:
            # 从状态日志获取取消前的状态
            from app.models.models import BizOrderStatusLog
            cancel_log = db.query(BizOrderStatusLog).filter(
                BizOrderStatusLog.order_id == order_id,
                BizOrderStatusLog.to_status == "pending_review_cancel"
            ).order_by(BizOrderStatusLog.create_at.desc()).first()
            
            # 恢复为取消前的状态（pending_delivery, delivering, partial_delivered）
            if cancel_log and cancel_log.from_status:
                # 兼容历史：尽量从旧状态恢复到三字段
                order.status = cancel_log.from_status
                self._sync_three_status_fields_from_legacy(order)
            else:
                self._apply_three_status(order, order_status="pending_confirm", delivery_status="pending_dispatch")
            order.cancel_status = "rejected"
            new_status = order.status

        order.cancel_review_time = datetime.now()
        order.cancel_reviewed_by = reviewer_id
        order.cancel_review_note = review_note
        order.update_at = datetime.now()

        self._create_status_log(
            db,
            order_id,
            "pending_review_cancel",
            new_status,
            reviewer_id,
            "admin",
            reason=f"审核{'通过' if approved else '拒绝'}: {review_note}"
        )

        db.commit()
        db.refresh(order)
        return order

    def confirm_receive(
        self,
        db: Session,
        order_id: str,
        operator_id: str
    ) -> Optional[BizOrder]:
        """确认收货"""
        order = self.get(db, order_id)
        if not order:
            return None
        
        # 仅待确认收货状态支持确认收货
        if order.status == "pending_confirm":
            return self.update_status(
                db,
                order_id,
                "completed",
                operator_id,
                "client",
                reason="客户确认收货"
            )
        return None

    def auto_confirm_receive_overdue(
        self,
        db: Session,
        *,
        before_threshold: datetime,
        configured_days: int,
    ) -> int:
        """送达后超过 configured_days 未确认收货的订单，系统自动确认（与 force_complete 一致写三字段与日志）。"""
        threshold_expr = func.coalesce(BizOrder.delivered_time, BizOrder.update_at)
        orders = (
            db.query(BizOrder)
            .filter(
                BizOrder.is_del == 0,
                BizOrder.order_status == "pending_confirm",
                BizOrder.delivery_status == "delivered",
                BizOrder.cancel_status.in_(("none", "rejected")),
                threshold_expr <= before_threshold,
            )
            .all()
        )
        if not orders:
            return 0
        note = f"送达后超过{configured_days}天未确认收货，系统自动确认"
        count = 0
        for order in orders:
            old_status = order.status
            self._apply_three_status(order, order_status="completed")
            order.completed_time = datetime.now()
            order.completed_by = "system"
            order.update_at = datetime.now()
            self._create_status_log(
                db,
                order.id,
                old_status,
                "completed",
                "system",
                "system",
                reason=note,
            )
            count += 1
        db.commit()
        return count

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

        # 查询当天最大订单号
        last_order = db.query(BizOrder).filter(
            BizOrder.order_no.like(f"{prefix}%")
        ).order_by(BizOrder.order_no.desc()).first()

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

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

    def _create_status_log(
        self,
        db: Session,
        order_id: str,
        from_status: str,
        to_status: str,
        operator_id: str,
        operator_role: str,
        reason: str = None,
        note: str = None,
        create_name: str = None,
        log_create_at: Optional[datetime] = None,
    ):
        """创建订单状态变更日志"""
        ts = log_create_at or datetime.now()
        log = BizOrderStatusLog(
            id=generate_id(),
            order_id=order_id,
            from_status=from_status,
            to_status=to_status,
            operator_id=operator_id,
            operator_role=operator_role,
            reason=reason,
            note=note,
            create_by=operator_id,
            create_name=create_name or None,
            create_at=ts,
            update_at=ts,
        )
        db.add(log)

    def close_unpaid_online_orders(
        self,
        db: Session,
        *,
        before_time: datetime,
        timeout_note: str,
    ) -> int:
        """关闭超时未支付的线上订单。"""
        candidates = db.query(BizOrder).filter(
            BizOrder.is_del == 0,
            BizOrder.payment_method == "online",
            BizOrder.pay_status == "pending_payment",
            BizOrder.order_status == "pending_payment",
            BizOrder.create_at <= before_time,
        ).all()
        count = 0
        for order in candidates:
            self._apply_three_status(order, order_status="cancelled", delivery_status="pending_dispatch", pay_status="pending_payment")
            order.cancel_status = "approved"
            order.cancel_reason = "线上支付超时自动关闭"
            order.cancel_review_note = "系统自动关闭"
            order.update_at = datetime.now()
            self._create_status_log(
                db,
                order.id,
                "pending_payment",
                "cancelled",
                "system",
                "system",
                reason="线上支付超时自动关闭",
                note=timeout_note,
            )
            count += 1
        if count:
            db.commit()
        return count

    def admin_cancel(
        self,
        db: Session,
        *,
        order_id: str,
        operator_id: str,
        operator_name: str,
        reason: str,
        note: Optional[str] = None,
        evidence_image_urls: Optional[List[str]] = None,
    ) -> Optional[BizOrder]:
        """运营取消订单。"""
        order = self.get(db, order_id)
        if not order:
            return None
        old_status = order.status
        self._apply_three_status(order, order_status="cancelled")
        order.cancel_status = "approved"
        order.cancel_reason = reason
        if note:
            order.cancel_review_note = note
        order.cancel_review_time = datetime.now()
        order.cancel_reviewed_by = operator_id
        order.update_at = datetime.now()
        self._create_status_log(
            db,
            order.id,
            old_status,
            "cancelled",
            operator_id,
            "admin",
            reason=reason,
            note=json.dumps({"note": note or "", "evidenceImageUrls": evidence_image_urls or []}, ensure_ascii=False),
        )
        db.commit()
        db.refresh(order)
        return order

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

        his_data = {
            "id": original.id,
            "order_no": original.order_no,
            "region_id": original.region_id,
            "client_id": original.client_id,
            "price_list_id": original.price_list_id,
            "price_version": original.price_version,
            "status": original.status,
            "order_status": getattr(original, "order_status", None),
            "delivery_status": getattr(original, "delivery_status", None),
            "pay_status": getattr(original, "pay_status", None),
            "cancel_status": getattr(original, "cancel_status", None),
            "payment_method": original.payment_method,
            "client_settlement_status": getattr(original, "client_settlement_status", "pending"),
            "supplier_settlement_status": getattr(original, "supplier_settlement_status", "pending"),
            "client_settlement_at": getattr(original, "client_settlement_at", None),
            "supplier_settlement_at": getattr(original, "supplier_settlement_at", None),
            "total_amount": original.total_amount,
            "paid_amount": original.paid_amount,
            "receivable_amount": original.receivable_amount,
            "refund_amount": original.refund_amount,
            "contact_person": original.contact_person,
            "contact_phone": original.contact_phone,
            "delivery_address": original.delivery_address,
            "cancel_reason": original.cancel_reason,
            "cancel_review_time": original.cancel_review_time,
            "cancel_reviewed_by": original.cancel_reviewed_by,
            "cancel_review_note": original.cancel_review_note,
            "completed_time": original.completed_time,
            "completed_by": original.completed_by,
            "remark": original.remark,
            "payment_time": original.payment_time,
            "delivery_assign_time": original.delivery_assign_time,
            "delivered_time": getattr(original, "delivered_time", None),
            "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 = BizOrderHis(**his_data)
        db.add(his)

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

    def soft_delete_with_history(self, db: Session, id: str) -> bool:
        """删除订单并记录历史"""
        original = self.get(db, id)
        if not original:
            return False

        his_data = {
            "id": original.id,
            "order_no": original.order_no,
            "region_id": original.region_id,
            "client_id": original.client_id,
            "price_list_id": original.price_list_id,
            "price_version": original.price_version,
            "status": original.status,
            "order_status": getattr(original, "order_status", None),
            "delivery_status": getattr(original, "delivery_status", None),
            "pay_status": getattr(original, "pay_status", None),
            "cancel_status": getattr(original, "cancel_status", None),
            "payment_method": original.payment_method,
            "client_settlement_status": getattr(original, "client_settlement_status", "pending"),
            "supplier_settlement_status": getattr(original, "supplier_settlement_status", "pending"),
            "client_settlement_at": getattr(original, "client_settlement_at", None),
            "supplier_settlement_at": getattr(original, "supplier_settlement_at", None),
            "total_amount": original.total_amount,
            "paid_amount": original.paid_amount,
            "receivable_amount": original.receivable_amount,
            "refund_amount": original.refund_amount,
            "contact_person": original.contact_person,
            "contact_phone": original.contact_phone,
            "delivery_address": original.delivery_address,
            "delivered_time": getattr(original, "delivered_time", None),
            "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 = BizOrderHis(**his_data)
        db.add(his)

        return self.delete(db, id)

    def is_order_lock_expired(
        self,
        order: BizOrder,
        ttl_seconds: int = ORDER_EDIT_LOCK_TTL_SECONDS,
    ) -> bool:
        """锁是否已过期（无锁或无锁定时间视为过期/可抢）。"""
        if not getattr(order, "lock_operator_id", None):
            return True
        if not getattr(order, "lock_time", None):
            return True
        return (datetime.now() - order.lock_time).total_seconds() > ttl_seconds

    def clear_order_lock(self, db: Session, order: BizOrder) -> BizOrder:
        """清空锁字段（调用方负责 commit，或本方法内 commit）。"""
        order.lock_operator_id = None
        order.lock_time = None
        order.update_at = datetime.now()
        db.commit()
        db.refresh(order)
        return order

    def lock_order(
        self,
        db: Session,
        order_id: str,
        operator_id: str,
        ttl_seconds: int = ORDER_EDIT_LOCK_TTL_SECONDS,
    ) -> Optional[BizOrder]:
        """锁定订单，防止并发写操作；他人锁超 TTL 后可抢。"""
        order = self.get(db, order_id)
        if not order:
            return None

        if order.lock_operator_id and order.lock_operator_id != operator_id:
            if not self.is_order_lock_expired(order, ttl_seconds):
                lock_time = (
                    order.lock_time.strftime("%Y-%m-%d %H:%M:%S")
                    if order.lock_time
                    else "未知"
                )
                raise ValueError(f"订单已被用户 {order.lock_operator_id} 于 {lock_time} 锁定")

        order.lock_operator_id = operator_id
        order.lock_time = datetime.now()
        order.update_at = datetime.now()

        db.commit()
        db.refresh(order)
        return order

    def unlock_order(
        self,
        db: Session,
        order_id: str,
        operator_id: str,
        force: bool = False,
    ) -> Optional[BizOrder]:
        """解锁订单；force=True 可解他人锁；无锁时幂等成功。"""
        order = self.get(db, order_id)
        if not order:
            return None

        if not order.lock_operator_id:
            return order

        if not force and order.lock_operator_id != operator_id:
            raise ValueError("只能解锁自己锁定的订单")

        order.lock_operator_id = None
        order.lock_time = None
        order.update_at = datetime.now()

        db.commit()
        db.refresh(order)
        return order

    def force_complete(
        self,
        db: Session,
        order_id: str,
        operator_id: str,
        operator_role: str,
        reason: str = None
    ) -> Optional[BizOrder]:
        """运营强制完结订单"""
        order = self.get(db, order_id)
        if not order:
            return None
        
        # 只有待确认收货状态可以强制完结
        if order.order_status not in ["pending_confirm"]:
            raise ValueError("只有待确认收货状态的订单可以强制完结")
        
        old_status = order.status
        self._apply_three_status(order, order_status="completed")
        order.completed_time = datetime.now()
        order.completed_by = operator_id
        order.update_at = datetime.now()
        
        # 记录状态变更
        self._create_status_log(
            db,
            order_id,
            old_status,
            "completed",
            operator_id,
            operator_role,
            reason=reason or "运营强制完结"
        )
        
        db.commit()
        db.refresh(order)
        return order

    def update_to_partial_delivered(
        self,
        db: Session,
        order_id: str,
        operator_id: str,
        operator_role: str,
        reason: str = None
    ) -> Optional[BizOrder]:
        """已废弃：旧的部分送达流程不再支持。"""
        raise ValueError("部分送达流程已下线")

    def update_to_confirm(
        self,
        db: Session,
        order_id: str,
        operator_id: str,
        operator_role: str,
        reason: str = None
    ) -> Optional[BizOrder]:
        """已废弃：旧的部分送达确认流程不再支持。"""
        raise ValueError("部分送达确认流程已下线")


class OrderItemCRUD(CRUDBase[BizOrderItem]):
    """订单项CRUD操作"""

    def get_by_order(self, db: Session, order_id: str) -> List[BizOrderItem]:
        """获取订单的所有订单项"""
        return db.query(BizOrderItem).filter(
            and_(
                BizOrderItem.order_id == order_id,
                BizOrderItem.is_del == 0
            )
        ).all()

    def get_count_by_order(self, db: Session, order_id: str) -> int:
        """获取订单项数量"""
        return db.query(BizOrderItem).filter(
            and_(
                BizOrderItem.order_id == order_id,
                BizOrderItem.is_del == 0
            )
        ).count()

    def update_item_adjustment(
        self,
        db: Session,
        item_id: str,
        new_quantity: Decimal = None,
        new_amount: Decimal = None,
        new_status: str = None,
        note: str = None,
        updater_id: str = None,
        updater_name: str = None
    ) -> Optional[BizOrderItem]:
        """更新订单项（调整后）"""
        item = self.get(db, item_id)
        if not item:
            return None
        
        if new_quantity is not None:
            item.quantity = new_quantity
        if new_amount is not None:
            item.amount = new_amount
        if new_status is not None:
            item.status = new_status
        if note is not None:
            item.note = note
        
        item.update_at = datetime.now()
        item.update_by = updater_id
        
        db.commit()
        db.refresh(item)
        return item

    def mark_as_reduced(
        self,
        db: Session,
        item_id: str,
        new_quantity: Decimal,
        new_amount: Decimal,
        note: str = None
    ) -> Optional[BizOrderItem]:
        """标记为已减量"""
        return self.update_item_adjustment(
            db, item_id,
            new_quantity=new_quantity,
            new_amount=new_amount,
            new_status="reduced",
            note=note
        )

    def mark_as_deleted(
        self,
        db: Session,
        item_id: str,
        note: str = None
    ) -> Optional[BizOrderItem]:
        """标记为已删除"""
        return self.update_item_adjustment(
            db, item_id,
            new_status="deleted",
            note=note
        )

    def mark_as_substituted(
        self,
        db: Session,
        item_id: str,
        new_product_id: str,
        new_quantity: Decimal,
        new_amount: Decimal,
        note: str = None
    ) -> Optional[BizOrderItem]:
        """标记为已替代"""
        item = self.update_item_adjustment(
            db, item_id,
            new_status="substituted",
            note=note
        )
        return item


class OrderStatusLogCRUD(CRUDBase[BizOrderStatusLog]):
    """订单状态日志CRUD操作"""

    def get_by_order(self, db: Session, order_id: str) -> List[BizOrderStatusLog]:
        """获取订单的状态变更日志"""
        return db.query(BizOrderStatusLog).filter(
            BizOrderStatusLog.order_id == order_id
        ).order_by(BizOrderStatusLog.create_at.asc()).all()


class OrderAdjustCRUD(CRUDBase[BizOrderAdjust]):
    """订单调整记录CRUD操作"""

    def get_by_order(self, db: Session, order_id: str) -> List[BizOrderAdjust]:
        """获取订单的所有调整记录"""
        return db.query(BizOrderAdjust).filter(
            and_(
                BizOrderAdjust.order_id == order_id,
                BizOrderAdjust.is_del == 0
            )
        ).order_by(BizOrderAdjust.create_at.desc()).all()

    def get_by_order_item(self, db: Session, order_item_id: str) -> List[BizOrderAdjust]:
        """获取订单项的调整记录"""
        return db.query(BizOrderAdjust).filter(
            and_(
                BizOrderAdjust.order_item_id == order_item_id,
                BizOrderAdjust.is_del == 0
            )
        ).order_by(BizOrderAdjust.create_at.desc()).all()

    def create_adjust_record(
        self,
        db: Session,
        order_id: str,
        adjust_type: str,
        operator_id: str,
        operator_name: str,
        order_item_id: str = None,
        before_quantity: Decimal = None,
        after_quantity: Decimal = None,
        before_amount: Decimal = None,
        after_amount: Decimal = None,
        difference_amount: Decimal = None,
        reason: str = None,
        substitute_product_id: str = None,
        snapshot_items_json: Optional[str] = None,
    ) -> BizOrderAdjust:
        """创建调整记录"""
        adjust_record = BizOrderAdjust(
            id=generate_id(),
            order_id=order_id,
            order_item_id=order_item_id,
            adjust_type=adjust_type,
            before_quantity=before_quantity,
            after_quantity=after_quantity,
            before_amount=before_amount,
            after_amount=after_amount,
            difference_amount=difference_amount or Decimal("0"),
            reason=reason,
            substitute_product_id=substitute_product_id,
            snapshot_items_json=snapshot_items_json,
            operator_id=operator_id,
            operator_name=operator_name,
            create_by=operator_id,
            create_name=operator_name
        )
        db.add(adjust_record)
        db.commit()
        db.refresh(adjust_record)
        return adjust_record


order_crud = OrderCRUD(BizOrder)
order_item_crud = OrderItemCRUD(BizOrderItem)
order_status_log_crud = OrderStatusLogCRUD(BizOrderStatusLog)
order_adjust_crud = OrderAdjustCRUD(BizOrderAdjust)