from django.utils import timezone from rest_framework import viewsets, status from rest_framework.decorators import action from rest_framework.response import Response from django.db.models.functions import Coalesce, NullIf from django.db.models import ( Q, Avg, OuterRef, Subquery, Count, Value, IntegerField, ) from django_filters.rest_framework import DjangoFilterBackend from .mixins import CustomerSearchMixin from .models import Customer from .serializers import CustomerSerializer, CustomerHistorySerializer from apps.calls.models import Call from apps.calls.constants import TRANSFER_STATUS, BotType from apps.userprofile.services import ProfileServices from utils.paginations import OurLimitOffsetPagination class CustomerViewSet(viewsets.ModelViewSet, CustomerSearchMixin): queryset = Customer.objects.all() serializer_class = CustomerSerializer filter_backends = [DjangoFilterBackend] def get_queryset(self): qs = self.queryset user = self.request.user if not user.is_superuser: if not user.active_company: return Customer.objects.none() qs = qs.filter( company=user.active_company ) return qs @action( detail=False, methods=['get'], url_path='customer-stats', pagination_class=OurLimitOffsetPagination, filter_backends=[DjangoFilterBackend] ) def customer_stats(self, request): queryset = self.filter_queryset(self.get_queryset()) call_stats_subquery = ( Call.objects.filter( from_number=OuterRef('phone'), company=OuterRef('company'), ) .values('company') .annotate( total_calls=Count('id'), missed_calls=Count( 'id', filter=Q(transfer_status=TRANSFER_STATUS.FAILED.value), ), received_calls=Count( 'id', filter=Q(transfer_status=TRANSFER_STATUS.SUCCESSFUL.value), ), not_transferred_calls=Count( 'id', filter=Q(transfer_status=TRANSFER_STATUS.NOT_TRANSFERRED.value), ), ) ) last_call_subquery = ( call_stats_subquery .order_by('-created_at') .values('created_at')[:1] ) queryset = queryset.annotate( last_call_time=Subquery(last_call_subquery), total_calls=Coalesce( Subquery(call_stats_subquery.values('total_calls'), IntegerField()), Value(0), ), missed_calls=Coalesce( Subquery(call_stats_subquery.values('missed_calls'), IntegerField()), Value(0), ), received_calls=Coalesce( Subquery(call_stats_subquery.values('received_calls'), IntegerField()), Value(0), ), not_transferred_calls=Coalesce( Subquery(call_stats_subquery.values('not_transferred_calls'), IntegerField()), Value(0), ), customer_name=Coalesce( NullIf('name', Value('')), Value('Unknown') ), ).order_by('-last_call_time') paginator = self.pagination_class() queryset = self.apply_call_search(queryset) page = paginator.paginate_queryset(queryset, request, view=self) results = [ { "customer_id": c.id, "customer_name": c.customer_name, "phone_number": c.phone, "total_calls": c.total_calls, "missed_calls": c.missed_calls, "received_calls": c.received_calls, "not_transferred_calls": c.not_transferred_calls, "last_call_time": ( c.last_call_time if c.last_call_time else None ), } for c in page ] return paginator.get_paginated_response(results) @action( detail=False, methods=['get'], url_path='customer-history', pagination_class=OurLimitOffsetPagination ) def customer_history(self, request): serializer = CustomerHistorySerializer(data=request.query_params) serializer.is_valid(raise_exception=True) data = serializer.validated_data customer_id = data['customer_id'] start_dt = data['start_date'] end_dt = data['end_date'] user = request.user customer_qs = self.queryset if not user.is_superuser: if not user.active_company: return Response( {"detail": "No active company found"}, status=status.HTTP_404_NOT_FOUND, ) customer_qs = customer_qs.filter(company=user.active_company) try: customer = customer_qs.get(id=customer_id) except Customer.DoesNotExist: return Response( {"detail": "Customer not found"}, status=status.HTTP_404_NOT_FOUND, ) if timezone.is_naive(start_dt): start_dt = timezone.make_aware(start_dt, timezone.utc) if timezone.is_naive(end_dt): end_dt = timezone.make_aware(end_dt, timezone.utc) calls_qs = Call.objects.filter( from_number=customer.phone, company=customer.company, created_at__gte=start_dt, created_at__lt=end_dt, ).select_related( 'transfer_user', 'transfer_user__profile', ).order_by('-created_at') dates_qs = ( calls_qs .values_list('created_at__date', flat=True) .distinct() .order_by('-created_at__date') ) paginator = OurLimitOffsetPagination() paginated_dates = paginator.paginate_queryset( list(dates_qs), request, view=self, ) calls_by_date = {} for call in calls_qs: local_date = timezone.localtime(call.created_at).date() calls_by_date.setdefault(local_date, []).append(call) history = [] for date in paginated_dates: day_calls = calls_by_date.get(date, []) if not day_calls: continue full_datetime = timezone.localtime(day_calls[0].created_at) calls_payload = [] for call in day_calls: advisor = call.transfer_user advisor_name = ( ProfileServices.display_name_for_company( advisor, call.company ) if advisor else 'Not Transferred' ) calls_payload.append({ "id": call.id, "twilio_call_id": call.twilio_call_sid, "sentiment": call.sentiment, "advisor_name": advisor_name, "summary": call.summary, "duration": float(call.duration or 0), "time": call.created_at, "transcript": "",#call.transcript, "call_id": call.call_id, "bot_type_label": BotType(call.bot_type).label, }) history.append({ "date": full_datetime, "calls": calls_payload, }) return Response({ "count": paginator.count, "next": paginator.get_next_link(), "previous": paginator.get_previous_link(), "customer_name": customer.name or "Unknown", "customer_phone": customer.phone, "total_calls": calls_qs.count(), "history": history, }) @action(detail=True, methods=['get'], url_path='recent-calls') def recent_calls(self, request, pk): user = request.user try: customer = self.get_queryset().select_related('company').get(pk=pk) except Customer.DoesNotExist: return Response( {"detail": "Customer not found"}, status=status.HTTP_404_NOT_FOUND ) if not user.is_superuser: if not user.active_company or customer.company_id != user.active_company_id: return Response( {"detail": "Customer not found"}, status=status.HTTP_404_NOT_FOUND ) calls_qs = ( Call.objects .filter( from_number=customer.phone, company_id=customer.company_id, # bot_type=BotType.SERVICE_BOT.value, ) .select_related( 'transfer_user', 'transfer_user__profile', ) .only( 'id', 'call_id', 'twilio_call_sid', 'created_at', 'duration', 'sentiment', 'summary', # 'transcript', 'bot_type', 'transfer_status', 'transfer_user__username', 'transfer_user__profile__first_name', 'transfer_user__profile__last_name', ) ) sentiment_avg = calls_qs.exclude( sentiment__isnull=True ).aggregate( avg=Avg('sentiment') )['avg'] recent_calls = list( calls_qs.order_by('-created_at')[:10] ) recent_call_datetime = recent_calls[0].created_at if recent_calls else None call_records = [] for call in recent_calls: advisor = call.transfer_user advisor_name = ( ProfileServices.display_name_for_company( advisor, call.company ) if advisor else 'Not Transferred' ) call_records.append({ "id": call.id, "call_id": call.call_id, "twilio_call_id": call.twilio_call_sid, "customer_name": customer.name or "Unknown", "timestamp": call.created_at, "duration_seconds": float(call.duration or 0), "transcript": '',#call.transcript, "sentiment_score": call.sentiment, "summary": call.summary, "advisor_name": advisor_name, "bot_type_label": BotType(call.bot_type).label, }) return Response({ "customer_name": customer.name or "Unknown", "phone": customer.phone, "recent_call_datetime": recent_call_datetime, "avg_sentiment": round(sentiment_avg, 2), "total_calls": calls_qs.count(), "call_records": call_records, })