""" Order Query & Analytics Service - Comprehensive order querying, filtering, and breakdown. Provides: - Order listing with tabs (All, Bundle, Retail, Status filters) - Order detail with shipment/payment breakdown - Fulfillment timeline tracking - Wallet usage transparency - Order statistics and analytics """ from decimal import Decimal from django.db.models import Q, Sum, Count, F from django.utils import timezone from datetime import timedelta from ..models import Order from apps.business.marketplace.models_part2 import OrderStatusHistory from apps.business.schema_compat import ( defer_missing_order_part_payment_fields, defer_missing_product_part_payment_fields, model_field_is_available, ) from apps.business.wallet.models import WalletLedger from apps.geo.models import StoreTransferLedger, FranchiseStoreEarning class OrderQueryService: """Service for comprehensive order querying and analytics.""" @staticmethod def get_user_orders(user, tab=None, status_filter=None, page=1, page_size=20): """ Get user orders with tab/status filtering. Tabs: 'all', 'bundle', 'retail', 'pending', 'shipped', 'delivered', 'cancelled' Returns: { 'count': int, 'total_pages': int, 'page': int, 'results': [OrderDto, ...] } """ qs = ( Order.objects .filter(user=user) .select_related('product', 'distributor') .prefetch_related('product__images') .order_by('-created_at') ) qs = defer_missing_order_part_payment_fields(qs) qs = defer_missing_product_part_payment_fields(qs, relation_prefix='product') # Apply tab filters if tab == 'bundle': qs = qs.filter(order_type='BUNDLE') elif tab == 'retail': qs = qs.filter(order_type='RETAIL') # Apply status filters if tab == 'pending': qs = qs.filter(status__in=['PENDING', 'PAYMENT_REVIEW', 'PARTIAL_PAID']) elif tab == 'shipped': qs = qs.filter(franchise_fulfillment_status='IN_TRANSIT') elif tab == 'delivered': qs = qs.filter(franchise_fulfillment_status='DELIVERED') elif tab == 'cancelled': qs = qs.filter(franchise_fulfillment_status='CANCELLED') # Additional status filter if status_filter: qs = qs.filter(status=status_filter) total_count = qs.count() total_pages = (total_count + page_size - 1) // page_size if total_count > 0 else 1 offset = (page - 1) * page_size orders = list(qs[offset:offset + page_size]) return { 'count': total_count, 'total_pages': total_pages, 'page': page, 'results': [_build_order_dto(order) for order in orders], } @staticmethod def get_order_detail(user, order_id): """ Get comprehensive order detail with all breakdown information. Returns OrderDetailDto or None if not found/unauthorized. """ order_qs = ( Order.objects.filter(id=order_id, user=user) .select_related( 'product', 'distributor__sponsor_distributor', 'distributor__binary_parent_distributor', 'payment_transaction', 'franchise_assignment__franchise', 'part_payment_conversion_product', ) .prefetch_related('product__images') ) order_qs = defer_missing_order_part_payment_fields(order_qs) order_qs = defer_missing_product_part_payment_fields(order_qs, relation_prefix='product') order_qs = defer_missing_product_part_payment_fields(order_qs, relation_prefix='part_payment_conversion_product') order = order_qs.first() if not order: return None return _build_order_detail_dto(order) @staticmethod def get_order_shipment_tracking(order_id): """Get shipment tracking timeline for an order.""" order = Order.objects.filter(id=order_id).first() if not order: return None if getattr(order, 'order_type', None) == 'RETAIL' or hasattr(order, 'shipment') or order.status_history.exists(): shipment = getattr(order, 'shipment', None) history_qs = order.status_history.order_by('created_at') timeline = [] total_quantity = order.items.aggregate(total=Sum('quantity'))['total'] or 0 for history in history_qs: timeline.append({ 'id': str(history.id), 'type': 'STATUS_HISTORY', 'status': history.status, 'store_name': 'Retail Fulfillment', 'timestamp': history.created_at.isoformat(), 'remarks': history.description, 'quantity': total_quantity, }) if shipment: timeline.append({ 'id': str(shipment.id), 'type': 'SHIPMENT', 'status': shipment.status, 'store_name': 'Retail Fulfillment', 'timestamp': shipment.created_at.isoformat(), 'remarks': shipment.tracking_number or shipment.shipping_method, 'quantity': total_quantity, }) return { 'order_id': str(order.id), 'reference_id': order.reference_id, 'current_status': shipment.status if shipment else order.status, 'timeline': timeline, 'estimated_delivery': shipment.estimated_delivery.isoformat() if shipment and shipment.estimated_delivery else _estimate_delivery_date(order), } transfers = StoreTransferLedger.objects.filter( order=order ).order_by('created_at') timeline = [] for transfer in transfers: timeline.append({ 'id': str(transfer.id), 'type': transfer.transfer_type, 'status': transfer.transfer_status, 'store_name': transfer.store.name if transfer.store else 'Unknown', 'timestamp': transfer.created_at.isoformat(), 'remarks': transfer.remarks, 'quantity': transfer.quantity, }) return { 'order_id': str(order.id), 'reference_id': order.reference_id, 'current_status': order.franchise_fulfillment_status, 'timeline': timeline, 'estimated_delivery': _estimate_delivery_date(order), } @staticmethod def get_order_wallet_breakdown(order_id): """ Get detailed wallet usage breakdown for an order. Shows: - Main wallet deduction - Super coin deduction - Repurchase wallet usage - Remaining balances after purchase """ order = Order.objects.filter(id=order_id).first() if not order: return None return { 'order_id': str(order.id), 'reference_id': order.reference_id, 'payment_method': order.payment_method, 'total_amount': str(order.amount), 'breakdown': { 'voucher_amount': str(order.voucher_amount), 'super_coin_amount': str(order.super_coin_amount), 'cash_amount': str(order.cash_amount), 'wallet_amount': str(Decimal(order.amount) - order.super_coin_amount - order.cash_amount - order.voucher_amount), }, 'wallet_ledger_entry_id': str(order.wallet_ledger_entry_id) if order.wallet_ledger_entry_id else None, } @staticmethod def get_user_order_stats(user): """ Get user order statistics for dashboard. Returns: { 'total_orders': int, 'bundle_orders': int, 'retail_orders': int, 'pending_orders': int, 'completed_orders': int, 'total_spent': str, 'bundle_spent': str, 'retail_spent': str, } """ orders = Order.objects.filter(user=user) return { 'total_orders': orders.count(), 'bundle_orders': orders.filter(order_type='BUNDLE').count(), 'retail_orders': orders.filter(order_type='RETAIL').count(), 'pending_orders': orders.filter(status__in=['PENDING', 'PAYMENT_REVIEW', 'PARTIAL_PAID']).count(), 'completed_orders': orders.filter(status='COMPLETED').count(), 'total_spent': str(orders.aggregate(total=Sum('amount'))['total'] or Decimal('0.00')), 'bundle_spent': str(orders.filter(order_type='BUNDLE').aggregate(total=Sum('amount'))['total'] or Decimal('0.00')), 'retail_spent': str(orders.filter(order_type='RETAIL').aggregate(total=Sum('amount'))['total'] or Decimal('0.00')), } def _estimate_delivery_date(order): """Estimate delivery date based on order and transfer status.""" transfers = StoreTransferLedger.objects.filter(order=order).order_by('-created_at').first() if not transfers: # If no transfers yet, estimate 3-5 days from now return (timezone.now() + timedelta(days=4)).date().isoformat() if transfers.transfer_status == 'DELIVERED': return transfers.updated_at.date().isoformat() elif transfers.transfer_status == 'IN_TRANSIT': # Estimate 2-3 days from last update return (transfers.updated_at + timedelta(days=2)).date().isoformat() else: # Pending or other status, estimate 3-5 days from now return (timezone.now() + timedelta(days=4)).date().isoformat() def _build_order_dto(order): """Build order DTO for list view.""" using = order._state.db or 'default' payment_mode = _compat_order_field(order, 'payment_mode', 'FULL', using=using) ownership_status = _compat_order_field(order, 'ownership_status', 'FULL_OWNED', using=using) part_pending_amount = _compat_order_field(order, 'part_pending_amount', Decimal('0.00'), using=using) is_availability_blocked = _compat_order_field(order, 'is_availability_blocked', False, using=using) return { 'id': str(order.id), 'reference_id': order.reference_id, 'product_name': order.product.name if order.product else 'Unknown', 'product_image': _get_product_image_url(order.product), 'amount': str(order.amount), 'order_type': order.order_type, 'status': order.status, 'fulfillment_status': order.franchise_fulfillment_status, 'payment_method': order.payment_method, 'payment_mode': payment_mode, 'ownership_status': ownership_status, 'part_pending_amount': str(part_pending_amount), 'is_availability_blocked': bool(is_availability_blocked), 'created_at': order.created_at.isoformat(), 'updated_at': order.updated_at.isoformat(), 'distributor_code': order.distributor.distributor_code if order.distributor else None, } def _build_order_detail_dto(order): """Build comprehensive order detail DTO.""" using = order._state.db or 'default' # Get shipment tracking shipment_tracking = OrderQueryService.get_order_shipment_tracking(order.id) # Get wallet breakdown wallet_breakdown = OrderQueryService.get_order_wallet_breakdown(order.id) # Get payment info payment_info = None if order.payment_transaction: payment_info = { 'provider': order.payment_transaction.provider, 'provider_order_id': order.payment_transaction.provider_order_id, 'provider_payment_id': order.payment_transaction.provider_payment_id, 'status': order.payment_transaction.status, } # Get franchise/vendor info franchise_info = None if order.franchise_assignment: franchise_info = { 'franchise_id': str(order.franchise_assignment.franchise_id), 'franchise_name': order.franchise_assignment.franchise.name if order.franchise_assignment.franchise else 'Unknown', 'status': order.franchise_fulfillment_status, } # Get distributor info if bundle order distributor_info = None if order.order_type == 'BUNDLE' and order.distributor: from apps.business.distributor.services.sponsor_income_service import get_sponsor_income_summary try: sponsor_summary = get_sponsor_income_summary(order.user) dist_data = next( (d for d in sponsor_summary.get('distributor_breakdown', []) if d['id'] == str(order.distributor.id)), None ) if dist_data: distributor_info = { 'distributor_id': str(order.distributor.id), 'distributor_code': order.distributor.distributor_code, 'sponsor_code': order.distributor.sponsor_distributor.distributor_code if order.distributor.sponsor_distributor else None, 'binary_parent_code': order.distributor.binary_parent_distributor.distributor_code if order.distributor.binary_parent_distributor else None, 'binary_position': order.distributor.binary_position, 'withdrawal_eligible': dist_data.get('withdrawal_eligible', False), 'power_stream_active': dist_data.get('power_stream_active', False), 'positions_count': dist_data.get('positions_count', 0), } except Exception: pass payment_mode = _compat_order_field(order, 'payment_mode', 'FULL', using=using) ownership_status = _compat_order_field(order, 'ownership_status', 'FULL_OWNED', using=using) is_availability_blocked = _compat_order_field(order, 'is_availability_blocked', False, using=using) part_payment_percentage = _compat_order_field(order, 'part_payment_percentage', 50, using=using) part_total_amount = _compat_order_field(order, 'part_total_amount', None, using=using) part_paid_amount = _compat_order_field(order, 'part_paid_amount', Decimal('0.00'), using=using) part_pending_amount = _compat_order_field(order, 'part_pending_amount', Decimal('0.00'), using=using) part_payment_started_at = _compat_order_field(order, 'part_payment_started_at', None, using=using) part_payment_due_at = _compat_order_field(order, 'part_payment_due_at', None, using=using) part_payment_completed_at = _compat_order_field(order, 'part_payment_completed_at', None, using=using) part_payment_converted_at = _compat_order_field(order, 'part_payment_converted_at', None, using=using) part_payment_conversion_product = _compat_order_field(order, 'part_payment_conversion_product', None, using=using) return { 'id': str(order.id), 'reference_id': order.reference_id, 'order_type': order.order_type, 'product_name': order.product.name if order.product else 'Unknown', 'product_image': _get_product_image_url(order.product), 'product_category': order.product.category if order.product else None, 'amount': str(order.amount), 'payment_method': order.payment_method, 'status': order.status, 'payment_mode': payment_mode, 'ownership_status': ownership_status, 'is_availability_blocked': bool(is_availability_blocked), 'part_payment': { 'percentage': part_payment_percentage, 'total_amount': str(part_total_amount) if part_total_amount is not None else None, 'paid_amount': str(part_paid_amount), 'pending_amount': str(part_pending_amount), 'started_at': part_payment_started_at.isoformat() if part_payment_started_at else None, 'due_at': part_payment_due_at.isoformat() if part_payment_due_at else None, 'completed_at': part_payment_completed_at.isoformat() if part_payment_completed_at else None, 'converted_at': part_payment_converted_at.isoformat() if part_payment_converted_at else None, 'conversion_product': { 'id': str(part_payment_conversion_product.id), 'name': part_payment_conversion_product.name, } if part_payment_conversion_product else None, }, 'fulfillment_status': order.franchise_fulfillment_status, 'fulfillment_mode': order.fulfillment_mode, 'created_at': order.created_at.isoformat(), 'updated_at': order.updated_at.isoformat(), 'shipping_address': order.shipping_address, 'billing_address': order.billing_address, 'dispatch_address': order.dispatch_address, 'payment_info': payment_info, 'wallet_breakdown': wallet_breakdown, 'franchise_info': franchise_info, 'distributor_info': distributor_info, 'shipment_tracking': shipment_tracking, } def _get_product_image_url(product): """Resolve product image URL across legacy and marketplace product image structures.""" if not product: return None # Backward compatibility: some deployments may still expose Product.image. legacy_image = getattr(product, 'image', None) if legacy_image: try: return str(legacy_image.url) except Exception: pass # Current model uses related ProductImage records. try: primary = product.images.filter(is_primary=True).first() candidate = primary or product.images.first() if candidate and getattr(candidate, 'image', None): return str(candidate.image.url) except Exception: return None return None def _compat_order_field(order, field_name, default, using='default'): if not model_field_is_available(Order, field_name, using=using): return default return getattr(order, field_name, default)