from datetime import timedelta from decimal import Decimal from django.db.models import Count, Sum from django.db.models.functions import TruncDate from django.utils import timezone from apps.accounts.models import User from apps.business.lucky_dip.models import LuckyDip, LuckyDipEntry from apps.business.orders.models import Order from apps.business.pool.models import PoolEntry from apps.business.products.models import Product from apps.business.schemes.services.distribution_metrics_service import DistributionMetricsService from apps.business.wallet.models import Wallet, WalletLedger from apps.business.withdrawals.models import WithdrawalRequest from apps.notifications.models import Notification, NotificationCampaign from apps.payments.models import PaymentTransaction from apps.support.models import SupportTicket class SystemStatsService: @staticmethod def _series_for_days(days: int = 7): today = timezone.localdate() return [today - timedelta(days=offset) for offset in reversed(range(days))] @staticmethod def _date_map(queryset, value_field: str): mapped = {} for row in queryset: day = row.get("day") if day is None: continue mapped[day] = row.get(value_field) or 0 return mapped @staticmethod def get_stats(): now = timezone.now() today = timezone.localdate() last_7_days_start = today - timedelta(days=6) last_30_days_start = now - timedelta(days=30) # Keep legacy keys for backward compatibility. total_purchases = Order.objects.filter(status="COMPLETED").count() total_rewards = WalletLedger.objects.filter( transaction_type=WalletLedger.TransactionType.CREDIT, source_type__in=[ WalletLedger.SourceType.DIRECT_INCENTIVE, WalletLedger.SourceType.LEVEL_REWARD, WalletLedger.SourceType.POWER_STREAM, WalletLedger.SourceType.LUCKY_DIP ] ).aggregate(total=Sum("amount"))["total"] or Decimal("0.00") failed_tasks = 0 withdrawal_volume = ( WithdrawalRequest.objects.filter(status=WithdrawalRequest.Status.PAID).aggregate(total=Sum("net_amount"))["total"] or Decimal("0.00") ) users_total = User.objects.count() users_active = User.objects.filter(is_active=True).count() users_new_7d = User.objects.filter(date_joined__date__gte=last_7_days_start).count() orders_qs = Order.objects.all() completed_orders_qs = orders_qs.filter(status="COMPLETED") completed_revenue = completed_orders_qs.aggregate(total=Sum("amount"))["total"] or Decimal("0.00") revenue_last_30d = completed_orders_qs.filter(created_at__gte=last_30_days_start).aggregate(total=Sum("amount"))["total"] or Decimal("0.00") withdrawals_pending_qs = WithdrawalRequest.objects.filter(status=WithdrawalRequest.Status.PENDING) withdrawals_paid_qs = WithdrawalRequest.objects.filter(status=WithdrawalRequest.Status.PAID) pending_withdrawal_amount = withdrawals_pending_qs.aggregate(total=Sum("amount_requested"))["total"] or Decimal("0.00") paid_withdrawal_amount = withdrawals_paid_qs.aggregate(total=Sum("net_amount"))["total"] or Decimal("0.00") wallet_totals = Wallet.objects.aggregate( main_total=Sum("main_balance"), repurchase_total=Sum("repurchase_balance"), locked_total=Sum("locked_amount"), ) credits_30d = WalletLedger.objects.filter( created_at__gte=last_30_days_start, transaction_type=WalletLedger.TransactionType.CREDIT, ).aggregate(total=Sum("amount"))["total"] or Decimal("0.00") debits_30d = WalletLedger.objects.filter( created_at__gte=last_30_days_start, transaction_type=WalletLedger.TransactionType.DEBIT, ).aggregate(total=Sum("amount"))["total"] or Decimal("0.00") campaigns_30d = NotificationCampaign.objects.filter(created_at__gte=last_30_days_start) notifications_30d = Notification.objects.filter(created_at__gte=last_30_days_start) support_qs = SupportTicket.objects.filter(is_active=True) support_overdue_cutoff = now - timedelta(hours=24) orders_by_day_qs = ( completed_orders_qs.filter(created_at__date__gte=last_7_days_start) .annotate(day=TruncDate("created_at")) .values("day") .annotate(count=Count("id"), revenue=Sum("amount")) ) signups_by_day_qs = ( User.objects.filter(date_joined__date__gte=last_7_days_start) .annotate(day=TruncDate("date_joined")) .values("day") .annotate(count=Count("id")) ) withdrawals_paid_by_day_qs = ( withdrawals_paid_qs.filter(created_at__date__gte=last_7_days_start) .annotate(day=TruncDate("created_at")) .values("day") .annotate(count=Count("id"), volume=Sum("net_amount")) ) orders_count_by_day = SystemStatsService._date_map(orders_by_day_qs, "count") orders_revenue_by_day = SystemStatsService._date_map(orders_by_day_qs, "revenue") signups_count_by_day = SystemStatsService._date_map(signups_by_day_qs, "count") withdrawals_count_by_day = SystemStatsService._date_map(withdrawals_paid_by_day_qs, "count") trend = [] for day in SystemStatsService._series_for_days(7): trend.append( { "date": day.isoformat(), "signups": int(signups_count_by_day.get(day, 0)), "completed_orders": int(orders_count_by_day.get(day, 0)), "revenue": str(orders_revenue_by_day.get(day, Decimal("0.00")) or Decimal("0.00")), "paid_withdrawals": int(withdrawals_count_by_day.get(day, 0)), } ) top_products_qs = ( completed_orders_qs.values("product_id", "product__name") .annotate( orders=Count("id"), revenue=Sum("amount"), ) .order_by("-revenue", "-orders")[:5] ) channel_status_qs = notifications_30d.values("channel", "status").annotate(count=Count("id")).order_by("channel", "status") channel_status = {} for row in channel_status_qs: channel = str(row.get("channel") or "unknown") status = str(row.get("status") or "unknown") channel_status.setdefault(channel, {})[status] = int(row.get("count") or 0) payment_status_qs = PaymentTransaction.objects.filter(created_at__gte=last_30_days_start).values("status").annotate(count=Count("id")) payment_status = {str(row["status"]): int(row["count"]) for row in payment_status_qs} payment_total = sum(payment_status.values()) payment_success = payment_status.get("success", 0) distribution_expected_30d = DistributionMetricsService.expected_component_totals( completed_orders_qs.filter(created_at__gte=last_30_days_start) ) or {} direct_incentive_credits_30d = WalletLedger.objects.filter( created_at__gte=last_30_days_start, transaction_type=WalletLedger.TransactionType.CREDIT, wallet_type=WalletLedger.WalletType.EARNINGS, source_type=WalletLedger.SourceType.DIRECT_INCENTIVE, ).aggregate(total=Sum("amount"))["total"] or Decimal("0.00") power_stream_credits_30d = WalletLedger.objects.filter( created_at__gte=last_30_days_start, transaction_type=WalletLedger.TransactionType.CREDIT, wallet_type=WalletLedger.WalletType.EARNINGS, source_type=WalletLedger.SourceType.POWER_STREAM, ).aggregate(total=Sum("amount"))["total"] or Decimal("0.00") growth_pool_credits_30d = PoolEntry.objects.filter(created_at__gte=last_30_days_start).aggregate(total=Sum("amount"))["total"] or Decimal("0.00") return { # Legacy keys. "total_purchases": total_purchases, "total_rewards_distributed": str(total_rewards), "failed_tasks": failed_tasks, "withdrawal_volume": str(withdrawal_volume), # Extended dashboard payload. "generated_at": now.isoformat(), "users": { "total": users_total, "active": users_active, "new_7d": users_new_7d, }, "orders": { "total": orders_qs.count(), "completed": completed_orders_qs.count(), "pending": orders_qs.filter(status="PENDING").count(), "failed": orders_qs.filter(status="FAILED").count(), "revenue_total": str(completed_revenue), "revenue_30d": str(revenue_last_30d), }, "products": { "total": Product.objects.count(), "active": Product.objects.filter(is_active=True).count(), }, "wallets": { "main_total": str(wallet_totals.get("main_total") or Decimal("0.00")), "repurchase_total": str(wallet_totals.get("repurchase_total") or Decimal("0.00")), "locked_total": str(wallet_totals.get("locked_total") or Decimal("0.00")), "credits_30d": str(credits_30d), "debits_30d": str(debits_30d), }, "distribution": { "direct_incentive_credits_30d": str(direct_incentive_credits_30d), "power_stream_credits_30d": str(power_stream_credits_30d), "growth_pool_credits_30d": str(growth_pool_credits_30d), "growth_pool_projected_30d": str( distribution_expected_30d.get("ENGAGEMENT_POOL", Decimal("0.00")) ), "platform_charges_projected_30d": str( distribution_expected_30d.get("PLATFORM_CHARGES", Decimal("0.00")) ), "tax_projected_30d": str( distribution_expected_30d.get("TAX", Decimal("0.00")) ), }, "withdrawals": { "pending_count": withdrawals_pending_qs.count(), "pending_amount": str(pending_withdrawal_amount), "paid_count": withdrawals_paid_qs.count(), "paid_amount": str(paid_withdrawal_amount), }, "lucky_dip": { "active": LuckyDip.objects.filter(is_active=True, is_drawn=False).count(), "open_for_draw": LuckyDip.objects.filter(is_active=True, is_drawn=False, draw_date__isnull=False).count(), "total_entries": LuckyDipEntry.objects.count(), }, "notifications": { "campaigns_30d": campaigns_30d.count(), "campaigns_scheduled": campaigns_30d.filter(status="scheduled").count(), "campaigns_failed": campaigns_30d.filter(status="failed").count(), "sent_30d": notifications_30d.filter(status="sent").count(), "failed_30d": notifications_30d.filter(status="failed").count(), "channel_status_30d": channel_status, }, "payments": { "status_breakdown_30d": payment_status, "success_rate_30d": round((payment_success / payment_total) * 100, 2) if payment_total else 0, }, "support": { "total": support_qs.count(), "open": support_qs.filter(status="open").count(), "in_progress": support_qs.filter(status="in_progress").count(), "resolved": support_qs.filter(status="resolved").count(), "closed": support_qs.filter(status="closed").count(), "unassigned_open": support_qs.filter(status__in=["open", "in_progress"], assigned_to__isnull=True).count(), "overdue_open": support_qs.filter( status__in=["open", "in_progress"], created_at__lte=support_overdue_cutoff, first_response_at__isnull=True, ).count(), }, "trend_7d": trend, "top_products": [ { "product_id": str(row["product_id"]), "name": row.get("product__name") or "Unknown", "orders": int(row.get("orders") or 0), "revenue": str(row.get("revenue") or Decimal("0.00")), } for row in top_products_qs ], "alerts": { "high_pending_withdrawals": withdrawals_pending_qs.count() >= 20, "payment_success_low": (round((payment_success / payment_total) * 100, 2) if payment_total else 0) < 80, "failed_notifications_present": notifications_30d.filter(status="failed").exists(), "support_overdue_present": support_qs.filter( status__in=["open", "in_progress"], created_at__lte=support_overdue_cutoff, first_response_at__isnull=True, ).exists(), }, }