import math import traceback from fastapi import APIRouter, Depends, HTTPException, Request, status, Body, Query, Path from sqlalchemy.orm import Session from typing import List, Dict, Any, Optional from datetime import datetime, timedelta, date, timezone, time from pydantic import BaseModel, Field from sqlalchemy import Date, func, case, desc, asc, cast, String, and_, literal_column, or_, distinct, select from fastapi.responses import StreamingResponse from app.advisor_service.services.time_service import convert_to_local_time from app.common import schemas, models import firebase_admin from firebase_admin import credentials from firebase_admin import messaging import logging from app.common.ai_helpers import get_speech_to_text from app.common.constants import OffTime from app.common.models import User, Customer, TransferResult from app.common.database import get_db from app.common.twillio_helper import get_call_recording_public_url, get_twillio_client from app.common.utils import convert_to_utc from app.utils.exceptions import NotFoundException, ValidationError from app.utils.response import Response from ..services.advisor_service import ( get_advisors, get_advisor, create_advisor, process_booking_intent, update_advisor, delete_advisor, get_advisors_by_department, update_advisor_score, update_advisor_availability, is_advisor_available, get_all_available_advisors ) from ..services.auth_utils import validate_token from ..services.dashboard_service import ( get_unique_repetitive_counts, get_daily_call_counts, get_hourly_call_volume, get_summary_statistics ) from zoneinfo import ZoneInfo from app.twilio_service.services.recording_service import stream_recording_from_url, download_recording_by_call_id from app.common.models import Settings from auth_service.services.settings_service import get_setting from calendar import month_name from collections import defaultdict import pytz router = APIRouter() cred = credentials.Certificate("firebase/serviceAccountKey.json") print(cred) firebase_admin.initialize_app(cred) # Define request model for dashboard data class DashboardRequest(BaseModel): start_date: Optional[datetime] = None end_date: Optional[datetime] = None # Define color codes at the top of the file # PERFORMANCE_COLORS = { # "best": "#76c487", # 0-10% # "good": "#9ff9b3", # 11-20% # "ok": "#e6e8b3", # 21-40% # "bad": "#f2bc92", # 41-60% # "worse": "#f89595" # 61%+ # } # Define request models directly in this file as a fallback class DateRangeRequest(BaseModel): start_date: date end_date: date class DailyLogRequest(BaseModel): log_date: date advisor_id: int # Add advisor_id to the request model # First, modify the DateRangeRequest schema to include advisor_id class AdvisorTimeStatsRequest(BaseModel): start_date: date end_date: date advisor_id: Optional[int] = None @router.get("/advisors", response_model=List[schemas.Advisor]) async def read_advisors( skip: int = 0, limit: int = 100, db: Session = Depends(get_db), #current_user: Dict = Depends(validate_token) ): """ Get all advisors, ordered by status (available first) and then by full name. Args: skip: Number of records to skip for pagination limit: Maximum number of records to return Returns: List of advisors ordered by availability and name """ # Update the get_advisors function call to include ordering parameters advisors = get_advisors( db, skip=skip, limit=limit, order_by_status=True, # Order by status (available first) order_by_name=True # Then order by name ) return advisors @router.get("/advisors/speech/get-speech-to-text") async def get_speech_text(call_id: str = Query(..., description="The call ID to get speech-to-text for")): """ Get speech-to-text for a given call Args: call_id: The call ID to get speech-to-text for """ recording_public_url = get_call_recording_public_url(call_id) if recording_public_url is None: raise HTTPException( status_code=status.HTTP_404_NOT_FOUND, detail="No recording found for this call" ) transcript, booking_intent, booking_datetime, transcript_with_speaker_labels = get_speech_to_text(recording_public_url) return {"transcript": transcript, "booking_intent": booking_intent} @router.get("/advisors/notifications") async def get_notifications( start: int = Query(0, description="Number of records to skip"), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Get notifications for the authenticated advisor. Returns latest 5 notifications by default, with pagination for the rest. """ limit = 5 # Get advisor_id from the token user = db.query(User).filter(User.email == current_user.get("email")).first() if not user.type == "advisor": raise HTTPException( status_code=status.HTTP_403_FORBIDDEN, detail="No advisor ID found in token" ) advisor = db.query(models.Advisor).filter(models.Advisor.user_id == user.id).first() advisor_id = advisor.id # Get total count of notifications total_count = db.query( func.count(models.Notifications.id) ).filter( models.Notifications.advisor_id == advisor_id ).scalar() # Get total unread notifications count total_unread = db.query( func.count(models.Notifications.id) ).filter( models.Notifications.advisor_id == advisor_id, models.Notifications.is_read == False ).scalar() # Get notifications with pagination notifications = db.query( models.Notifications ).filter( models.Notifications.advisor_id == advisor_id ).order_by( models.Notifications.created_at.desc() # Order by most recent first ).offset(start).limit(limit).all() response_data = [] for notification in notifications: # Get transcript from CallsLog if call_id exists transcript = None call_summary = None if notification.call_id: call_log = db.query(models.CallsLog).filter( models.CallsLog.call_id == notification.call_id ).first() if call_log: transcript = call_log.transcript call_summary = call_log.call_summary notification_data = { "id": notification.id, "notification_type": notification.notification_type, "title": notification.title, "call_id": notification.call_id, "transcript": transcript, "call_summary": call_summary, "message": notification.message, "customer_phone": notification.customer_phone, "customer_name": notification.customer_name if notification.customer_name else "Unknown", "is_read": notification.is_read, "read_at": notification.read_at.isoformat() if notification.read_at else None, "created_at": notification.created_at.isoformat() if notification.created_at else None } response_data.append(notification_data) return { "data": response_data, "total": total_count, "total_unread": total_unread, "start": start, "limit": limit } @router.get("/advisors/notifications/latest") async def get_latest_notification( db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Get single latest notification for the authenticated advisor. """ # Get advisor_id from the token user = db.query(User).filter(User.email == current_user.get("email")).first() if not user.type == "advisor": raise HTTPException( status_code=status.HTTP_403_FORBIDDEN, detail="No advisor ID found in token" ) advisor = db.query(models.Advisor).filter(models.Advisor.user_id == user.id).first() advisor_id = advisor.id print('advisor_id', advisor_id) # Get the single latest notification notification = db.query( models.Notifications ).filter( models.Notifications.advisor_id == advisor_id, models.Notifications.is_read == False, models.Notifications.notification_type == "missed_call" ).order_by( models.Notifications.created_at.desc() # Order by most recent first ).first() if not notification: return {"data": None} notification_data = { "id": notification.id, "notification_type": notification.notification_type, "title": notification.title, "message": notification.message, "customer_phone": notification.customer_phone, "customer_name": notification.customer_name if notification.customer_name else "Unknown", "is_read": notification.is_read, "created_at": notification.created_at.isoformat() if notification.created_at else None } return { "data": notification_data, } @router.get("/advisors/notifications/read-all") async def read_all_notifications( db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Mark all notifications as read for the authenticated advisor """ # Get advisor_id from the token user = db.query(User).filter(User.email == current_user.get("email")).first() if not user.type == "advisor": raise HTTPException( status_code=status.HTTP_403_FORBIDDEN, detail="No advisor ID found in token" ) # advisor = db.query(models.Advisor).filter(models.Advisor.user_id == user.id).first() # advisor_id = advisor.id # Update all unread notifications to mark them as read result = db.query(models.Notifications).filter( models.Notifications.user_id == user.id, models.Notifications.is_read == False # Only update unread notifications ).update({ models.Notifications.is_read: True, models.Notifications.read_at: datetime.now(timezone.utc) }) db.commit() return { "message": f"Marked {result} notifications as read" } @router.post("/advisors/update-fcm") async def updateFcmToken( req_data: schemas.UpdateTokenRequest, db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ) -> Dict[str, Any]: db_user = db.query(models.User).filter(models.User.email == current_user.get('email')).first() if db_user: db_user.fcm_token = req_data.token db.commit() db.refresh(db_user) return { 'message': 'Fcm token updated successfully' } @router.get("/advisors/send-notification") async def sendPushNotification( db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ) -> Dict[str, Any]: db_user = db.query(models.User).filter(models.User.email == current_user.get('email')).first() message_title = "DealerPulse Update" message_body = "Hi john, someone is missing you. Guess what, it's your client. Please call back asap." if db_user: if db_user.fcm_token is not None: registration_token = db_user.fcm_token # See documentation on defining a message payload. message = messaging.Message( data={ 'score': '850', 'time': '2:45', }, token=registration_token, ) # Send a message to the device corresponding to the provided # registration token. response = messaging.send(message) # Response is a message ID string. print('Successfully sent message:', response) return { 'message': 'Notification sent successfully' } @router.get("/advisors/{advisor_id}", response_model=schemas.AdvisorDetail) async def read_advisor( advisor_id: int, db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): return get_advisor(db, advisor_id=advisor_id) @router.post("/advisors", status_code=status.HTTP_201_CREATED) async def create_new_advisor( advisor_data: Dict = Body(...), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Create a new advisor with a user account Expects a JSON object with advisor details. The password field is optional - if not provided, a random secure password will be generated. Example request: { "full_name": "John Doe", "email": "john@example.com", "phone_number": "+1234567890", "status": "1", "department_id": 1, "type": "sales", "password": "securepassword123" // Optional } If password is not provided, the response will include a generated_password field. """ # Extract password from the data (it's now optional) password = advisor_data.pop("password", None) advisor_data["status"] = "0" # Convert remaining dictionary to AdvisorCreate model try: advisor = schemas.AdvisorCreate(**advisor_data) except Exception as e: raise HTTPException( status_code=status.HTTP_400_BAD_REQUEST, detail=f"Invalid advisor data: {str(e)}" ) # Create the advisor with or without a password created_advisor = create_advisor(db=db, advisor=advisor, password=password) # Prepare response response_data = { "id": created_advisor.id, "full_name": created_advisor.full_name, "email": created_advisor.email, "phone_number": created_advisor.phone_number, "status": created_advisor.status, "department_id": created_advisor.department_id, "type": created_advisor.type, "user_id": created_advisor.user_id } # Include generated password in response if one was created if hasattr(created_advisor, 'generated_password'): response_data["generated_password"] = created_advisor.generated_password return response_data @router.put("/advisors/{advisor_id}", response_model=schemas.Advisor) async def update_existing_advisor( advisor_id: int, advisor: schemas.AdvisorUpdate, db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): # Get the advisor from db db_advisor = db.query(models.Advisor).filter(models.Advisor.id == advisor_id).first() if not db_advisor: raise HTTPException( status_code=404, detail=f"A staff member with id {advisor_id} not found" ) # Check if another advisor exists with the same name (case insensitive) existing_advisor = db.query(models.Advisor).filter( models.Advisor.full_name.ilike(advisor.full_name), models.Advisor.id != advisor_id # Exclude current advisor being updated ).first() if existing_advisor: raise HTTPException( status_code=status.HTTP_400_BAD_REQUEST, detail="A staff member with this name already exists. Please use a different name." ) # Get associated user record if db_advisor.user_id: user = db.query(models.User).filter(models.User.id == db_advisor.user_id).first() if user: # Update user name if advisor name is being updated if advisor.full_name: user.name = advisor.full_name db.add(user) db.commit() return update_advisor(db=db, advisor_id=advisor_id, advisor=advisor) @router.delete("/advisors/{advisor_id}") async def delete_existing_advisor( advisor_id: int, db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): return delete_advisor(db=db, advisor_id=advisor_id) @router.get("/departments/{department_id}/advisors", response_model=List[schemas.Advisor]) async def read_advisors_by_department( department_id: int, db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): advisors = get_advisors_by_department(db, department_id=department_id) return advisors @router.patch("/advisors/{advisor_id}/score", response_model=schemas.Advisor) async def update_advisor_score_endpoint( advisor_id: int, score: int, db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): return update_advisor_score(db=db, advisor_id=advisor_id, score=score) @router.post("/availability/{advisor_id}") async def set_advisor_availability( advisor_id: int, is_available: bool = Body(..., embed=True), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ) -> Dict: """ Set an advisor's availability status. Args: advisor_id: ID of the advisor is_available: True to mark as available, False to mark as unavailable Returns: JSON response with updated advisor information """ advisor = update_advisor_availability(db, advisor_id, is_available) if not advisor: raise HTTPException( status_code=404, detail=f"Advisor with ID {advisor_id} not found" ) return { "id": advisor.id, "full_name": advisor.full_name, "status": advisor.status, "message": f"Advisor {advisor.full_name} is now {'available' if advisor.status == '1' else 'unavailable'}" } # New dashboard endpoints @router.get("/dashboard/call-counts") async def get_call_counts( start_date: datetime = Query(None), end_date: datetime = Query(None), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ) -> Dict[str, Any]: """ Get counts of unique and repetitive calls for the given date range. """ try: # Default to last 30 days if no dates provided if not start_date: start_date = datetime.now() - timedelta(days=30) if not end_date: end_date = datetime.now() # Ensure end_date is at the end of the day end_date = end_date.replace(hour=23, minute=59, second=59) # Get call counts call_counts = get_unique_repetitive_counts(db, start_date, end_date, user=current_user) return { "success": True, "data": call_counts, "date_range": { "start_date": start_date.isoformat(), "end_date": end_date.isoformat() } } except Exception as e: raise HTTPException( status_code=500, detail=f"Error retrieving call counts: {str(e)}" ) @router.get("/dashboard/daily-calls") async def get_daily_calls( start_date: datetime = Query(None), end_date: datetime = Query(None), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ) -> Dict[str, Any]: """ Get daily counts of unique and repetitive calls for the given date range. """ try: # Default to last 30 days if no dates provided if not start_date: start_date = datetime.now() - timedelta(days=30) if not end_date: end_date = datetime.now() # Ensure end_date is at the end of the day end_date = end_date.replace(hour=23, minute=59, second=59) user_type = current_user.get("user", {}).get("type") advisor_id = current_user.get("user", {}).get("advisor_id") # Get daily call counts based on user type if user_type == "manager": daily_calls = get_daily_call_counts(db, start_date, end_date) else: # For non-managers, only get their own data daily_calls = get_daily_call_counts(db, start_date, end_date, advisor_id=advisor_id) return { "success": True, "data": daily_calls, "date_range": { "start_date": start_date.isoformat(), "end_date": end_date.isoformat() } } except Exception as e: raise HTTPException( status_code=500, detail=f"Error retrieving daily call counts: {str(e)}" ) @router.get("/dashboard/hourly-calls") async def get_hourly_calls( start_date: datetime = Query(None), end_date: datetime = Query(None), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ) -> Dict[str, Any]: """ Get average call volume by hour of day for the given date range. """ try: # Default to last 30 days if no dates provided if not start_date: start_date = datetime.now() - timedelta(days=30) if not end_date: end_date = datetime.now() # Ensure end_date is at the end of the day end_date = end_date.replace(hour=23, minute=59, second=59) # Get hourly call volume hourly_calls = get_hourly_call_volume(db, start_date, end_date) return { "success": True, "data": hourly_calls, "date_range": { "start_date": start_date.isoformat(), "end_date": end_date.isoformat() } } except Exception as e: raise HTTPException( status_code=500, detail=f"Error retrieving hourly call volume: {str(e)}" ) @router.get("/dashboard/summary") async def get_call_summary( start_date: datetime = Query(None), end_date: datetime = Query(None), advisor_id: Optional[int] = Query(None, description="Filter by advisor ID"), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ) -> Dict[str, Any]: """ Get summary statistics for the given date range. Args: start_date: Optional start date for filtering (defaults to 30 days ago) end_date: Optional end date for filtering (defaults to current date) advisor_id: Optional advisor ID to filter statistics by advisor """ try: # Default to last 30 days if no dates provided if not start_date: start_date = datetime.now() - timedelta(days=30) if not end_date: end_date = datetime.now() # Ensure end_date is at the end of the day end_date = end_date.replace(hour=23, minute=59, second=59) # Get summary statistics, passing the advisor_id if provided summary = get_summary_statistics(db, start_date, end_date, advisor_id) return { "success": True, "data": summary, "date_range": { "start_date": start_date.isoformat(), "end_date": end_date.isoformat() } } except Exception as e: print(f"Error retrieving call summary: {str(e)}") raise HTTPException( status_code=500, detail=f"Error retrieving call summary: {str(e)}" ) @router.post("/dashboard/all") async def get_all_dashboard_data( request: DashboardRequest, db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ) -> Dict[str, Any]: """ Get all dashboard data in a single API call for the given date range. Combines call counts, daily calls, hourly calls, and summary statistics. Request body: { "start_date": "2023-01-01T00:00:00-07:00", // Optional, defaults to 30 days ago "end_date": "2023-01-31T23:59:59-07:00" // Optional, defaults to current date } """ try: today = datetime.now().date() start_date = request.start_date end_date = request.end_date timezone = getattr(request, 'current_timezone', "America/Los_Angeles") if start_date: start_date = convert_to_utc(start_date) if end_date: end_date = convert_to_utc(end_date) start_date_val = start_date or (today - timedelta(days=30)) end_date_val = end_date or today user_id = current_user.get('id') advisor_id = None advisor_phone = None if user_id is not None: advisor = db.query(models.Advisor).filter(models.Advisor.user_id == user_id, models.Advisor.type == "advisor").first() if advisor: advisor_id = advisor.id advisor_phone = advisor.phone_number start_date = datetime.combine(start_date_val, datetime.min.time()) end_date = datetime.combine(end_date_val, datetime.max.time()) call_counts = get_unique_repetitive_counts(db, start_date, end_date, user=current_user, advisor_id=advisor_phone) daily_calls = get_daily_call_counts(db, start_date, end_date, user=current_user, advisor_id=advisor_phone) hourly_calls = get_hourly_call_volume(db, start_date, end_date, user=current_user, advisor_id=advisor_phone) summary = get_summary_statistics(db, start_date, end_date, user=current_user, advisor_id=advisor_id) dashboard_data = { "call_counts": call_counts, "daily_calls": daily_calls, "hourly_calls": hourly_calls, "summary": summary } return { "success": True, "data": dashboard_data, "date_range": { "start_date": start_date_val.isoformat(), "end_date": end_date_val.isoformat() } } except Exception as e: import traceback print("Error in get_customer_call_history:") print(traceback.format_exc()) raise HTTPException( status_code=500, detail=f"Error retrieving dashboard data: {str(e)}" ) @router.post("/dashboard/leaderboard") async def get_leaderboard( request: DashboardRequest = Body(...), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ) -> Dict[str, Any]: """ Get advisor leaderboard data for the dashboard. """ try: performance_colors = { "best": "#76c487", # 0-10% "good": "#9ff9b3", # 11-20% "ok": "#e6e8b3", # 21-40% "bad": "#f2bc92", # 41-60% "worse": "#f89595" # 61%+ } start_date = request.start_date end_date = request.end_date if start_date: start_date = convert_to_utc(start_date) if end_date: end_date = convert_to_utc(end_date) today = date.today() if not start_date: start_date = today - timedelta(days=30) if not end_date: end_date = today start_datetime = datetime.combine(start_date, datetime.min.time()) end_datetime = datetime.combine(end_date, datetime.max.time()) advisors = db.query(models.Advisor).filter(models.Advisor.status == "1").all() # transfer_result_sample = db.query(models.TransferResult).first() # if transfer_result_sample: # print("TransferResult columns:", transfer_result_sample.__dict__) result = [] for advisor in advisors: total_calls = db.query(models.TransferResult).join( models.CallsLog, models.TransferResult.twilio_call_id == models.CallsLog.twilio_call_id ).filter( models.CallsLog.transferred_to == advisor.phone_number, models.TransferResult.transferred_to == advisor.id, models.CallsLog.call_date_time >= start_datetime, models.CallsLog.call_date_time <= end_datetime, models.CallsLog.company_id == current_user.get('default_company') ).count() successful_calls = db.query(models.TransferResult).join( models.CallsLog, models.TransferResult.twilio_call_id == models.CallsLog.twilio_call_id ).filter( models.CallsLog.transferred_to == advisor.phone_number, models.TransferResult.transferred_to == advisor.id, models.TransferResult.status == True, models.CallsLog.call_date_time >= start_datetime, models.CallsLog.call_date_time <= end_datetime, models.CallsLog.company_id == current_user.get('default_company') ).count() missed_calls = db.query(models.TransferResult).join( models.CallsLog, models.TransferResult.twilio_call_id == models.CallsLog.twilio_call_id ).filter( models.CallsLog.transferred_to == advisor.phone_number, models.TransferResult.transferred_to == advisor.id, models.TransferResult.status == False, models.CallsLog.call_date_time >= start_datetime, models.CallsLog.call_date_time <= end_datetime, models.CallsLog.company_id == current_user.get('default_company') ).count() missed_percentage = 0 if total_calls > 0: missed_percentage = (missed_calls / total_calls) * 100 background_color = performance_colors["best"] if missed_percentage > 60: background_color = performance_colors["worse"] elif missed_percentage > 40: background_color = performance_colors["bad"] elif missed_percentage > 20: background_color = performance_colors["ok"] elif missed_percentage > 10: background_color = performance_colors["good"] result.append({ "advisor_id": advisor.id, "advisor_name": advisor.full_name, "total_calls": total_calls, "successful_calls": successful_calls, "missed_calls": missed_calls, "missed_percentage": round(missed_percentage, 1), "background_color": background_color }) result.sort(key=lambda x: x["missed_percentage"], reverse=True) return { "success": True, "data": result, "date_range": { "start_date": start_date.isoformat() if isinstance(start_date, date) else start_date, "end_date": end_date.isoformat() if isinstance(end_date, date) else end_date } } except Exception as e: print(traceback.format_exc()) raise HTTPException( status_code=500, detail=f"Error retrieving leaderboard data: {str(e)}" ) @router.post("/dashboard/recent-calls") async def get_recent_calls( start_date: Optional[datetime] = Query(None), end_date: Optional[datetime] = Query(None), limit: int = Query(10, description="Number of recent calls to return"), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ) -> Dict[str, Any]: """ Get a list of recent calls with details, sorted by date (newest first). Args: start_date: Optional start date for filtering end_date: Optional end date for filtering limit: Number of recent calls to return (default: 10) Returns: List of recent calls with details including transfer status and advisor name """ try: if not start_date: start_date = datetime.now() - timedelta(days=30) if not end_date: end_date = datetime.now() end_date = end_date.replace(hour=23, minute=59, second=59) calls_query = ( db.query( models.CallsLog.id, models.CallsLog.call_id, models.CallsLog.call_date_time.label('call_date'), models.CallsLog.caller_name.label('customer_name'), models.CallsLog.caller_number.label('customer_phone'), models.CallsLog.call_summary, models.CallsLog.recording_url, models.CallsLog.call_duration, models.TransferResult.status, models.Advisor.full_name.label('advisor_name') ) .outerjoin( models.TransferResult, models.CallsLog.twilio_call_id == models.TransferResult.twilio_call_id ) .outerjoin( models.Advisor, models.TransferResult.transferred_to == models.Advisor.id ) .filter( models.CallsLog.call_date_time >= start_date, models.CallsLog.call_date_time <= end_date, models.CallsLog.company_id == current_user.get('default_company') ) .order_by(models.CallsLog.call_date_time.desc()) .limit(limit) .all() ) result = [] for call in calls_query: transfer_status = "successful" if call.status else "failed" if call.status is None: transfer_status = "not transferred" call_duration_str = str(call.call_duration) if call.call_duration else "unknown" result.append({ "call_id": call.call_id, "id": call.id, "call_date": call.call_date, "customer_name": call.customer_name or "Unknown", "customer_phone": call.customer_phone, "transfer_status": transfer_status, "call_summary": call.call_summary or "", "recording_url": call.recording_url or "", "call_duration": call_duration_str, "advisor_name": call.advisor_name or "Not transferred" }) return { "success": True, "data": result, "date_range": { "start_date": convert_to_local_time(db,start_date).isoformat(), "end_date": convert_to_local_time(db,end_date).isoformat() }, "count": len(result) } except Exception as e: raise HTTPException( status_code=500, detail=f"Error retrieving recent calls: {str(e)}" ) def is_off_time(call_datetime: datetime) -> bool: """ Check if the given UTC datetime falls within off-hours (7 PM to 9 AM PST) """ # Convert UTC to PST pst = pytz.timezone('America/Los_Angeles') pst_time = call_datetime.astimezone(pst) # Get hour in PST hour = pst_time.hour # Off hours are between 7 PM (19:00) and 9 AM (09:00) return hour >= 19 or hour < 9 @router.post("/dashboard/call-log") async def get_call_log( request: Dict[str, Any] = Body(...), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ) -> Dict[str, Any]: """ Get a filtered call log with details, sorted by date (newest first). Supports filtering by transfer status (successful/failed). """ try: #print("-----------") # Extract request parameters start_date = request.get('start_date') end_date = request.get('end_date') timezone = request.get('timezone', 'UTC') sentiment = request.get('sentiment') req_advisor_id = request.get('advisor_id') customer_phone = request.get('customer_phone') transfer_status = request.get('transfer_status') # New parameter: 'successful', 'failed', or None department = request.get('department') callId = request.get('twilio_call_id') page = request.get('page', 1) page_size = request.get('page_size', 10) offset = (page - 1) * page_size # Convert string dates to datetime objects if they are strings if isinstance(start_date, str): try: start_date = datetime.fromisoformat(start_date) except ValueError: start_date = datetime.strptime(start_date, "%Y-%m-%d") if isinstance(end_date, str): try: end_date = datetime.fromisoformat(end_date) except ValueError: end_date = datetime.strptime(end_date, "%Y-%m-%d") # Set end date to end of day end_date = end_date.replace(hour=23, minute=59, second=59) # Convert to UTC if needed if start_date: start_date = convert_to_utc(start_date) if end_date: end_date = convert_to_utc(end_date) today = date.today() if not start_date: start_date = today - timedelta(days=30) if not end_date: end_date = today # Convert dates to datetime with appropriate time boundaries if isinstance(start_date, str): start_date = datetime.strptime(start_date, "%Y-%m-%d").date() if isinstance(end_date, str): end_date = datetime.strptime(end_date, "%Y-%m-%d").date() start_datetime = datetime.combine(start_date, datetime.min.time()) end_datetime = datetime.combine(end_date, datetime.max.time()) if transfer_status == "offtime": # For off hours in PDT (7 PM to 9 AM PDT) # Convert to UTC: PDT is UTC-7, so 7 PM PDT = 2 AM UTC next day evening_start = datetime.combine(start_date, time(2, 0)) # 7 PM PDT = 2 AM UTC evening_end = datetime.combine(start_date, time(6, 59)) # 11:59 PM PDT = 6:59 AM UTC morning_start = datetime.combine(start_date, time(7, 0)) # 12 AM PDT = 7 AM UTC morning_end = datetime.combine(start_date, time(15, 59)) # 9 AM PDT = 4:59 PM UTC else: start_datetime = datetime.combine(start_date, datetime.min.time()) end_datetime = datetime.combine(end_date, datetime.max.time()) # Debug information print(f"Filtering with: start_date={start_datetime}, end_date={end_datetime}") print(f"Sentiment filter: {sentiment}") print(f"Advisor ID filter: {req_advisor_id}") print(f"Customer phone filter: {customer_phone}") print(f"Transfer status filter: {transfer_status}") print(f"Department filter: {department}") if department: department = department.strip().lower() user_id = current_user.get('id') advisor_id = None advisor_phone = None if user_id is not None: # Check if user_id is valid advisor = db.query(models.Advisor).filter(models.Advisor.user_id == user_id, models.Advisor.type == "advisor").first() if advisor: advisor_id = advisor.id advisor_phone = advisor.phone_number # Build the base query for all statistics base_query = ( db.query(models.CallsLog) .outerjoin( models.TransferResult, models.CallsLog.twilio_call_id == models.TransferResult.twilio_call_id ) .filter( models.CallsLog.call_date_time >= start_datetime, models.CallsLog.call_date_time <= end_datetime, ) ) if department: base_query = base_query.filter( func.lower(models.CallsLog.department) == department ) if callId: base_query = base_query.filter(models.CallsLog.twilio_call_id == callId) # Apply optional filters to base query if sentiment: #print(f"Applying sentiment filter: {sentiment}") base_query = base_query.filter(func.lower(models.CallsLog.sentiment_score) == func.lower(sentiment)) # if isinstance(department, str) and department.strip(): # base_query = base_query.filter( # func.lower(models.CallsLog.department) == department.lower()) if req_advisor_id: #print(f"Applying advisor_id filter: {advisor_id}") base_query = base_query.filter(models.TransferResult.transferred_to == req_advisor_id) if advisor_id or advisor_phone: base_query = base_query.filter( models.CallsLog.transferred_to == advisor_phone, models.TransferResult.transferred_to == advisor_id ) if customer_phone: # print(f"Applying customer_phone filter: {customer_phone}") base_query = base_query.filter(models.CallsLog.caller_number.like(f"%{customer_phone}%")) # Calculate summary statistics before applying transfer status filter # (We want the summary to always show all calls even if we're filtering by status) total_calls = base_query.count() total_pages = math.ceil(total_calls / page_size) total_successful_calls = ( base_query.filter( models.CallsLog.transferred_to == advisor_phone if advisor_phone is not None else True, models.TransferResult.status == True ).count() ) total_missed_calls = ( base_query.filter( models.CallsLog.transferred_to == advisor_phone if advisor_phone is not None else True, models.TransferResult.status == False ).count() ) # Calculate average call duration avg_duration_result = ( db.query(func.avg(models.CallsLog.call_duration)) .select_from(base_query.subquery()) .scalar() ) # Handle the case where avg_duration might be None avg_duration_seconds = 0 if avg_duration_result: # If call_duration is stored as a timedelta if isinstance(avg_duration_result, timedelta): avg_duration_seconds = avg_duration_result.total_seconds() # If call_duration is stored as seconds or other numeric value else: avg_duration_seconds = float(avg_duration_result) # Format average duration as HH:MM:SS hours, remainder = divmod(int(avg_duration_seconds), 3600) minutes, seconds = divmod(remainder, 60) avg_call_time = f"{int(hours):02}:{int(minutes):02}:{int(seconds):02}" # Now apply transfer status filter if provided if transfer_status: if transfer_status.lower() == 'successful': print("Filtering for successful transfers only") base_query = base_query.filter(models.TransferResult.status == True) elif transfer_status.lower() == 'failed': print("Filtering for failed transfers only") base_query = base_query.filter(models.TransferResult.status == False) # Build the detailed query with pagination for the results query = ( db.query( models.CallsLog.id, models.CallsLog.call_id, models.CallsLog.twilio_call_id, models.CallsLog.call_date_time.label('call_date'), models.CallsLog.caller_name.label('customer_name'), models.CallsLog.caller_number.label('customer_phone'), models.CallsLog.call_summary, models.CallsLog.recording_url, models.CallsLog.call_duration, models.CallsLog.is_offtime, models.CallsLog.department, models.CallsLog.sentiment_score.label('sentiment'), models.TransferResult.status, models.Advisor.full_name.label('advisor_name'), models.Advisor.id.label('advisor_id') ) .outerjoin( models.TransferResult, models.CallsLog.twilio_call_id == models.TransferResult.twilio_call_id ) .outerjoin( models.Advisor, models.TransferResult.transferred_to == models.Advisor.id ) ) if callId: callId = callId.strip() query = query.filter(models.CallsLog.twilio_call_id == callId) if transfer_status == "offtime": query = query.filter( models.CallsLog.transferred_to == advisor_phone if advisor_phone is not None else True, models.CallsLog.is_offtime == OffTime.YES ) else: query = query.filter( models.CallsLog.transferred_to == advisor_phone if advisor_phone is not None else True, models.CallsLog.call_date_time >= start_datetime, models.CallsLog.call_date_time <= end_datetime ) # Apply optional filters to detailed query if sentiment: query = query.filter(func.lower(models.CallsLog.sentiment_score) == func.lower(sentiment)) if department: query = query.filter( func.lower(models.CallsLog.department) == department ) if req_advisor_id: query = query.filter(models.TransferResult.transferred_to == req_advisor_id) if customer_phone: query = query.filter(models.CallsLog.caller_number.like(f"%{customer_phone}%")) # Apply transfer status filter to detailed query if transfer_status: if transfer_status.lower() == 'successful': query = query.filter(models.TransferResult.status == True) elif transfer_status.lower() == 'failed': query = query.filter(models.TransferResult.status == False) # Get total count before applying pagination total_count = query.count() print(f"Total matching records: {total_count}") # Apply sorting and pagination calls_query = ( query .order_by(models.CallsLog.call_date_time.desc()) .offset(offset) .limit(page_size) .all() ) # Format the results result = [] for call in calls_query: call_transfer_status = "successful" if call.status else "missed" if call.status is None: call_transfer_status = "not transferred" call_duration_str = str(call.call_duration) if call.call_duration else "unknown" print(call,"------") result.append({ "id": call.id, "call_id": call.call_id, "twilio_call_id": call.twilio_call_id, "call_date": call.call_date, "is_off_time": call.is_offtime == OffTime.YES, "customer_name": call.customer_name or "Unknown", "customer_phone": call.customer_phone, "transfer_status": call_transfer_status, "call_summary": call.call_summary or "", "recording_url": call.recording_url or "", "call_duration": call_duration_str, "advisor_name": call.advisor_name or "Not transferred", "advisor_id": call.advisor_id, "sentiment": call.sentiment or "neutral", "department": call.department, }) # Create summary object summary = { "total_calls": total_calls, "total_successful_calls": total_successful_calls, "total_missed_calls": total_missed_calls, "filtered_count": total_count, # Count after applying the transfer_status filter "avg_call_time": avg_call_time, "avg_call_time_seconds": int(avg_duration_seconds) } # If no results but filters were applied, return a more helpful message filter_info = [] if sentiment: filter_info.append(f"sentiment='{sentiment}'") if advisor_id: filter_info.append(f"advisor_id={advisor_id}") if customer_phone: filter_info.append(f"customer_phone='{customer_phone}'") if transfer_status: filter_info.append(f"transfer_status='{transfer_status}'") if not result and filter_info: filter_str = ", ".join(filter_info) return { "success": False, "message": f"No records found matching filters: {filter_str} for the date range {start_date} to {end_date}", "data": [], "summary": summary, # Include summary even for empty results "date_range": { "start_date": start_date.isoformat() if isinstance(start_date, date) else start_date, "end_date": end_date.isoformat() if isinstance(end_date, date) else end_date }, "filters": { "sentiment": sentiment, "advisor_id": advisor_id, "customer_phone": customer_phone, "transfer_status": transfer_status }, "pagination": { "total": total_calls, "total_pages": total_pages, "page": page, "page_size": page_size } } return { "success": True, "data": result, "summary": summary, # Add summary to response "date_range": { "start_date": start_date.isoformat() if isinstance(start_date, date) else start_date, "end_date": end_date.isoformat() if isinstance(end_date, date) else end_date }, "filters": { "sentiment": sentiment, "advisor_id": advisor_id, "customer_phone": customer_phone, "transfer_status": transfer_status }, "pagination": { "total": total_count, "total_pages": total_pages, "page": page, "page_size": page_size, } } except Exception as e: import traceback print(f"Error in call-log endpoint: {str(e)}") print(traceback.format_exc()) raise HTTPException( status_code=500, detail=f"Error retrieving call log: {str(e)}" ) @router.post("/dashboard/advisor-stats") async def get_advisor_stats( request: Dict[str, Any] = Body(...), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ) -> Dict[str, Any]: """ Get detailed statistics for a specific advisor within a date range. Args: request: Request body with advisor_id, start_date and end_date Returns: Dictionary with advisor statistics including calls missed, calls attended, and details of their last 10 calls """ try: # Extract data from request advisor_id = request.get('advisor_id') start_date = request.get('start_date') end_date = request.get('end_date') # Default to last 30 days if no dates provided today = date.today() if not start_date: start_date = today - timedelta(days=30) if not end_date: end_date = today # Convert string dates to datetime objects if isinstance(start_date, str): try: start_datetime = datetime.fromisoformat(start_date) except ValueError: start_datetime = datetime.strptime(start_date, "%Y-%m-%d") else: start_datetime = datetime.combine(start_date, datetime.min.time()) if isinstance(end_date, str): try: end_datetime = datetime.fromisoformat(end_date) end_datetime = end_datetime.replace(hour=23, minute=59, second=59) except ValueError: end_datetime = datetime.strptime(end_date, "%Y-%m-%d") end_datetime = end_datetime.replace(hour=23, minute=59, second=59) else: end_datetime = datetime.combine(end_date, datetime.max.time()) # Check if advisor exists advisor = db.query(models.Advisor).filter(models.Advisor.id == advisor_id).first() if not advisor: raise HTTPException( status_code=404, detail=f"Advisor with ID {advisor_id} not found" ) # Get calls missed (status = False) calls_missed = db.query(models.TransferResult).filter( models.TransferResult.transferred_to == advisor_id, models.TransferResult.status == False, models.TransferResult.call_datetime >= start_datetime, models.TransferResult.call_datetime <= end_datetime ).count() # Get calls attended (status = True) calls_attended = db.query(models.TransferResult).filter( models.TransferResult.transferred_to == advisor_id, models.TransferResult.status == True, models.TransferResult.call_datetime >= start_datetime, models.TransferResult.call_datetime <= end_datetime ).count() # Calculate total calls and attendance rate total_calls = calls_missed + calls_attended attendance_rate = (calls_attended / total_calls * 100) if total_calls > 0 else 0 # Get last 10 calls with details last_calls_query = ( db.query( models.CallsLog.id, models.CallsLog.call_date_time, models.CallsLog.caller_name, models.CallsLog.caller_number, models.CallsLog.call_summary, models.CallsLog.recording_url, models.CallsLog.call_duration, models.CallsLog.sentiment_score, models.CallsLog.twilio_call_id, # Added twilio_call_id models.TransferResult.status ) .join( models.TransferResult, models.CallsLog.twilio_call_id == models.TransferResult.twilio_call_id ) .filter( models.TransferResult.transferred_to == advisor_id, models.CallsLog.call_date_time >= start_datetime, models.CallsLog.call_date_time <= end_datetime ) .order_by(models.CallsLog.call_date_time.desc()) .limit(10) .all() ) # Format the last calls data last_calls = [] for call in last_calls_query: last_calls.append({ "id": call.id, "call_date": convert_to_local_time(db,call.call_date_time).isoformat() if call.call_date_time else None, "customer_name": call.caller_name or "Unknown", "customer_phone": call.caller_number, "call_summary": call.call_summary or "", "recording_url": call.recording_url or "", "call_duration": str(call.call_duration) if call.call_duration else "unknown", "sentiment": call.sentiment_score or "neutral", "status": "attended" if call.status else "missed", "twilio_call_id": call.twilio_call_id # Added twilio_call_id }) return { "success": True, "advisor": { "id": advisor.id, "name": advisor.full_name, "department_id": advisor.department_id, "phone_number": advisor.phone_number, "status": advisor.status }, "stats": { "calls_missed": calls_missed, "calls_attended": calls_attended, "total_calls": total_calls, "attendance_rate": round(attendance_rate, 1) }, "last_calls": last_calls, "date_range": { "start_date": start_datetime.isoformat(), "end_date": end_datetime.isoformat() } } except Exception as e: import traceback print(f"Error in advisor-stats endpoint: {str(e)}") print(traceback.format_exc()) raise HTTPException( status_code=500, detail=f"Error retrieving advisor statistics: {str(e)}" ) @router.get("/dashboard/call-details/{call_id}") async def get_call_details( call_id: str, db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ) -> Dict[str, Any]: """ Get detailed information about a specific call by its Twilio call ID. Args: call_log_id: The id of alpha_call_log table Returns: Dictionary with call details including customer information, recording URL, transcript, and call summary """ try: # Query the call details call = db.query(models.CallsLog).filter( models.CallsLog.id == call_id ).first() if not call: raise HTTPException( status_code=404, detail=f"Call with Twilio ID {call_id} not found" ) # Get transfer information if available transfer_info = db.query(models.TransferResult).filter( models.TransferResult.twilio_call_id == call.twilio_call_id ).first() # Get advisor information if the call was transferred advisor_info = None if transfer_info and transfer_info.transferred_to: advisor = db.query(models.Advisor).filter( models.Advisor.id == transfer_info.transferred_to ).first() if advisor: advisor_info = { "id": advisor.id, "name": advisor.full_name, "phone_number": advisor.phone_number, "status": "attended" if transfer_info.status else "missed" } # Format the response return { "success": True, "call_details": { "call_id": call.call_id, "twilio_call_id": call.twilio_call_id, "call_date": call.call_date_time, "customer_name": call.caller_name or "Unknown", "customer_phone": call.caller_number, "call_summary": call.call_summary or "", "transcript": call.transcript or "", "recording_url": call.recording_url or "", "call_duration": str(call.call_duration) if call.call_duration else "unknown", "sentiment": call.sentiment_score or "neutral" }, "transfer_info": advisor_info } except Exception as e: import traceback print(f"Error in call-details endpoint: {str(e)}") print(traceback.format_exc()) raise HTTPException( status_code=500, detail=f"Error retrieving call details: {str(e)}" ) @router.get("/dashboard/download-recording/{call_id}") async def download_recording( call_id: str, db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Download the MP3 recording for a specific call by its Twilio call ID. Args: twilio_call_id: The Twilio call ID to download the recording for Returns: Streaming response with the MP3 file """ try: # Import necessary models from app.common.models import CallsLog # Query to get the twilio_call_id from the alpha_calls_log table using call_id call = db.query(CallsLog).filter(CallsLog.call_id == call_id).first() if not call: raise HTTPException( status_code=404, detail=f"Call with call_id {call_id} not found in alpha_calls_log table" ) # Use the twilio_call_id from the call record twilio_call_id = call.twilio_call_id if not twilio_call_id: raise HTTPException( status_code=404, detail=f"No Twilio call ID found for call with call_id {twilio_call_id}" ) # Use the service function to download the recording streaming_response, error = await download_recording_by_call_id(twilio_call_id, db) if error: raise HTTPException( status_code=500, detail=f"Error streaming recording: {error}" ) if not streaming_response: raise HTTPException( status_code=500, detail="Failed to create streaming response" ) return streaming_response except Exception as e: import traceback print(f"Error in download-recording endpoint: {str(e)}") print(traceback.format_exc()) raise HTTPException( status_code=500, detail=f"Error downloading recording: {str(e)}" ) #ALL AVAILABLE ADVISORS LIST. DO NOT CH @router.get("/all-available", response_model=List[Dict[str, Any]]) async def get_available_advisors( db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Get a list of all currently available advisors """ available_advisors = get_all_available_advisors(db) return available_advisors @router.get("/advisors/{advisor_id}/availability", response_model=Dict[str, Any]) async def check_advisor_availability( advisor_id: int, db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Check if an advisor is currently available """ availability = is_advisor_available(db, advisor_id) return availability @router.post("/advisors/time-stats") async def get_advisor_time_stats( request: AdvisorTimeStatsRequest = Body(...), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Get aggregated statistics for an advisor's online time and calls within a date range. Args: request: JSON body with start_date, end_date, and optional advisor_id Returns: Dictionary with daily stats including online time, missed calls, and successful calls If advisor_id is not provided, returns stats for all advisors """ try: start_date = request.start_date end_date = request.end_date advisor_id = request.advisor_id start_datetime = datetime.combine(start_date, datetime.min.time()) end_datetime = datetime.combine(end_date, datetime.max.time()) date_range_days = [(start_date + timedelta(days=i)) for i in range((end_date - start_date).days + 1)] result = [] if advisor_id is not None: advisor = db.query(models.Advisor).filter(models.Advisor.id == advisor_id).first() if not advisor: raise HTTPException(status_code=404, detail=f"Advisor with ID {advisor_id} not found") advisor_data = get_advisor_stats(db, advisor, date_range_days, user=current_user) result.extend(advisor_data) return { "success": True, "advisor": { "id": advisor.id, "name": advisor.full_name }, "data": result, "date_range": { "start_date": start_date.isoformat(), "end_date": end_date.isoformat() } } else: advisors = db.query(models.Advisor).all() if not advisors: return { "success": True, "data": [], "date_range": { "start_date": start_date.isoformat(), "end_date": end_date.isoformat() } } for advisor in advisors: advisor_data = get_advisor_stats(db, advisor, date_range_days) result.extend(advisor_data) return { "success": True, "data": result, "date_range": { "start_date": start_date.isoformat(), "end_date": end_date.isoformat() } } except Exception as e: import traceback print(f"Error in advisor time stats endpoint: {str(e)}") print(traceback.format_exc()) raise HTTPException(status_code=500, detail=f"Error retrieving advisor time stats: {str(e)}") # Helper function to get stats for a specific advisor def get_advisor_stats(db, advisor, date_range_days): """Get time and call statistics for a specific advisor over a date range""" result = [] for day in date_range_days: day_start = datetime.combine(day, datetime.min.time()) day_end = datetime.combine(day, datetime.max.time()) # Get total online time directly from total_session total_seconds = db.query(func.sum(models.LoginLog.total_session)).filter( models.LoginLog.advisor_id == advisor.id, models.LoginLog.log_date == day, models.LoginLog.total_session != None # Only include records with valid total_session ).scalar() or 0 # Format as HH:MM:SS hours, remainder = divmod(total_seconds, 3600) minutes, seconds = divmod(remainder, 60) total_online_time = f"{int(hours):02}:{int(minutes):02}:{int(seconds):02}" # Get call statistics missed_calls = db.query(models.TransferResult).filter( models.TransferResult.transferred_to == advisor.id, models.TransferResult.status == False, models.TransferResult.call_datetime >= day_start, models.TransferResult.call_datetime <= day_end ).count() successful_calls = db.query(models.TransferResult).filter( models.TransferResult.transferred_to == advisor.id, models.TransferResult.status == True, models.TransferResult.call_datetime >= day_start, models.TransferResult.call_datetime <= day_end ).count() # Add to result result.append({ "date": day.isoformat(), "total_online_time": total_online_time, "total_seconds": total_seconds, "missed_calls": missed_calls, "successful_calls": successful_calls, "advisor_id": advisor.id, "advisor_name": advisor.full_name }) return result @router.post("/advisors/daily-time-logs", response_model=schemas.AdvisorDailyTimeLogResponse) async def get_advisor_daily_time_logs( request_data: DailyLogRequest = Body(...), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ) -> Dict[str, Any]: """ Get detailed login/logout logs for an advisor on a specific day, adjusted for client timezone. """ try: # Get timezone offset from settings timezone_setting = db.query(models.Settings).filter( models.Settings.name == 'TIMEZONE_OFFSET' ).first() timezone_offset = int(timezone_setting.value) if timezone_setting else -7 # Default to -7 if not set log_date = request_data.log_date advisor_id = request_data.advisor_id # Check if advisor exists advisor = db.query(models.Advisor).filter(models.Advisor.id == advisor_id).first() if not advisor: raise HTTPException(status_code=404, detail=f"Advisor with ID {advisor_id} not found") # Get all logs for the day where total_session is not null logs = db.query(models.LoginLog).filter( models.LoginLog.advisor_id == advisor_id, models.LoginLog.log_date == log_date, models.LoginLog.total_session != None ).order_by(models.LoginLog.login_time).all() # Format time logs with timezone adjustment time_logs = [] total_seconds = 0 for log in logs: # Adjust login and logout times for timezone local_login = log.login_time + timedelta(hours=timezone_offset) local_logout = log.logout_time + timedelta(hours=timezone_offset) if log.logout_time else None duration_seconds = log.total_session or 0 total_seconds += duration_seconds # Format duration as HH:MM:SS hours, remainder = divmod(duration_seconds, 3600) minutes, seconds = divmod(remainder, 60) duration_str = f"{int(hours):02}:{int(minutes):02}:{int(seconds):02}" time_logs.append({ "login_time": local_login, "logout_time": local_logout, "duration": duration_str, "duration_seconds": duration_seconds }) # Format total time hours, remainder = divmod(total_seconds, 3600) minutes, seconds = divmod(remainder, 60) total_time = f"{int(hours):02}:{int(minutes):02}:{int(seconds):02}" return { "success": True, "advisor": { "id": advisor.id, "name": advisor.full_name }, "log_date": log_date.isoformat(), "time_logs": time_logs, "total_time": total_time, "timezone_offset": timezone_offset # Include timezone offset in response } except Exception as e: import traceback print(f"Error in advisor daily time logs endpoint: {str(e)}") print(traceback.format_exc()) raise HTTPException(status_code=500, detail=f"Error retrieving advisor daily time logs: {str(e)}") @router.get("/customers", response_model=dict) def get_customers( db: Session = Depends(get_db), current_user = Depends(validate_token), page: int = Query(1, ge=1), page_size: int = Query(10, ge=1, le=100) ): offset = (page - 1) * page_size customers = db.query(Customer).offset(offset).limit(page_size).all() total_customers = db.query(Customer).count() total_pages = math.ceil(total_customers / page_size) records = [] for customer in customers: customer_name = customer.customer_name phone_number = customer.phone_number # Count total calls total_calls = db.query(func.count(models.CallsLog.id)).filter( models.CallsLog.caller_number == phone_number).scalar() # Count missed calls (TransferResult.status == False) missed_calls = db.query(func.count(models.TransferResult.id)).filter(TransferResult.customer_number == phone_number, TransferResult.status == False).scalar() # Count received calls (TransferResult.status != False) received_calls = db.query(func.count(models.TransferResult.id)).filter(TransferResult.customer_number == phone_number, TransferResult.status != False).scalar() # Get the last call time (max timestamp) last_call_time = db.query(func.max(models.CallsLog.call_date_time)).filter( models.CallsLog.caller_number == phone_number).scalar() records.append({ "customer_id": customer.id, "customer_name": customer_name, "phone_number": phone_number, "total_calls": total_calls, "missed_calls": missed_calls, "received_calls": received_calls, "last_call_time": last_call_time }) # # results = ( # db.query( # Customer.customer_name, # Customer.phone_number, # func.count(models.CallsLog.id.distinct()).label("total_calls"), # Use distinct to avoid counting duplicates # # func.coalesce(func.avg(CallsLog.duration), 0).label("avg_duration"), # func.count(case((TransferResult.status == False, 1))).label("missed_calls"), # func.count(case((TransferResult.status != False, 1))).label("received_calls"), # Customer.last_call_date_time.label("last_call_time") # ) # .join(TransferResult, TransferResult.customer_number == Customer.phone_number) # .join(models.CallsLog, models.CallsLog.caller_number == Customer.phone_number) # # .filter( # # Customer.customer_name != None, # Exclude null names # # Customer.customer_name != "", # Exclude empty names # # ~Customer.customer_name.ilike("%Unknown%") # # ) # .group_by(Customer.id) # .order_by(Customer.last_call_date_time.desc().nullslast()) # ) # records = results.all() # # records = [ # { # "customer_name": row.customer_name, # "phone_number": row.phone_number, # "total_calls": row.total_calls, # "missed_calls": row.missed_calls, # "received_calls": row.received_calls, # "last_call_time": row.last_call_time # } # for row in records # ] return { "data": records, "pagination": { "page": page, "page_size": page_size, "total_pages": total_pages, "total_customers": total_customers } } class CustomerNameUpdate(BaseModel): customer_id: int = Field(..., gt=0, example=113) new_name: str = Field(..., min_length=1, max_length=100, example="Updated Name") @router.post("/customers/update-name", response_model=dict, status_code=status.HTTP_200_OK) async def update_customer_name_by_id( payload: CustomerNameUpdate = Body(...), # This parses the JSON body db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Update customer name by ID (ID in request body). Client should send JSON like: { "customer_id": 113, "new_name": "New Customer Name" } """ customer = db.query(Customer).filter(Customer.id == payload.customer_id).first() if not customer: raise HTTPException( status_code=status.HTTP_404_NOT_FOUND, detail="Customer not found" ) # Update and save customer.customer_name = payload.new_name.strip() db.commit() db.refresh(customer) return { "customer_id": customer.id, "new_name": customer.customer_name, } @router.get("/customers/detail/{customer_id}", response_model=dict) def get_customer_by_id(customer_id: int, db: Session = Depends(get_db)): customer = db.query(Customer).filter(Customer.id == customer_id).first() if not customer: raise HTTPException(status_code=404, detail="Customer not found") # Get last 10 calls last_10_calls = ( db.query(models.CallsLog) .filter(models.CallsLog.caller_number == customer.phone_number) .order_by(models.CallsLog.call_date_time.desc()) .limit(10) .all() ) # Get most recent call recent_call_datetime = last_10_calls[0].call_date_time if last_10_calls else None # Sample logic: average sentiment of last 10 calls # sentiments = [call.sentiment_score for call in last_10_calls if call.sentiment_score is not None] # overall_sentiment = round(sum(sentiments) / len(sentiments), 2) if sentiments else None # Fetch the last 10 calls and calculate the average sentiment score most_common = ( db.query( models.CallsLog.sentiment_score, func.count(models.CallsLog.sentiment_score).label('count') ) .filter(models.CallsLog.caller_number == customer.phone_number) .group_by(models.CallsLog.sentiment_score) .order_by(func.count(models.CallsLog.sentiment_score).desc()) .first() ) most_common_sentiment = most_common[0] # Prepare last_10_call_records_list call_records_list = [] for call in last_10_calls: advisor = db.query(models.Advisor).filter(models.Advisor.phone_number==call.transferred_to).scalar() customer = db.query(models.Customer).filter(models.Customer.phone_number==call.caller_number).scalar() call_records_list.append({ "call_id": call.call_id, "twilio_call_id": call.twilio_call_id, "customer_name": customer.customer_name, "timestamp": call.call_date_time, "duration": call.call_duration, "transcript": call.transcript, "sentiment_score": call.sentiment_score, "summary": call.call_summary, "advisor_name": advisor.full_name if advisor else None, }) record = { "customer_name": customer.customer_name, "phone": customer.phone_number, "recent_call_datetime": recent_call_datetime, "overall_sentiment": most_common_sentiment, "call_records": call_records_list } return {"data": record} @router.post("/catch-phrases/graph") async def bdc_dashboard( request: Dict[str, Any] = Body(...), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Get catch phrases graph data for a given date range. Returns catch phrases graph data along with total count. """ page = request.get('page', 1) page_size = request.get('page_size', 20) offset = (page - 1) * page_size start_date = request.get('start_date') end_date = request.get('end_date') advisor_id = request.get('advisor_id') filter_catch_phrases = request.get('catch_phrases') timezone = request.get('timezone', 'America/Los_Angeles') if isinstance(start_date, str): try: start_date = datetime.fromisoformat(start_date) except ValueError: start_date = datetime.strptime(start_date, "%Y-%m-%d") if isinstance(end_date, str): try: end_date = datetime.fromisoformat(end_date) except ValueError: end_date = datetime.strptime(end_date, "%Y-%m-%d") end_date = end_date.replace(hour=23, minute=59, second=59) if start_date: start_date = convert_to_utc(start_date) if end_date: end_date = convert_to_utc(end_date) today = datetime.now() if not start_date: start_date = today - timedelta(days=30) if not end_date: end_date = today start_datetime = datetime.combine(start_date, datetime.min.time()) end_datetime = datetime.combine(end_date, datetime.max.time()) catch_phrases_settings = db.query(Settings).filter( Settings.name == 'CATCH_PHRASES' ).first() if not catch_phrases_settings: raise HTTPException(status_code=404, detail="Catch phrases not found in settings") catch_phrases = catch_phrases_settings.value.strip('"') catch_phrases_list = catch_phrases.split(',') calls_log_data_query = db.query(models.CallsLog).filter( models.CallsLog.call_date_time >= start_datetime, models.CallsLog.call_date_time <= end_datetime, models.CallsLog.company_id == current_user.get('default_company') ) if advisor_id: advisor = db.query(models.Advisor).filter(models.Advisor.id == advisor_id).first() if advisor: calls_log_data_query = calls_log_data_query.filter(models.CallsLog.transferred_to == advisor.phone_number) if filter_catch_phrases: conditions = [] phrases = filter_catch_phrases.split(',') for phrase in phrases: conditions.append(func.lower(models.CallsLog.twilio_recording_text).like(f"%{phrase.strip().lower()}%")) calls_log_data_query = calls_log_data_query.filter(or_(*conditions)) calls_log_data = calls_log_data_query.all() calls_log_data_paginated = calls_log_data_query.order_by(models.CallsLog.call_date_time.desc()).limit(page_size).offset(offset).all() total_calls = calls_log_data_query.count() total_pages = math.ceil(total_calls / page_size) retCalls = [] for call in calls_log_data_paginated: catch_phrases_found = [] twilio_recording_text = call.twilio_recording_text.lower() if call.twilio_recording_text else "" for phrase in catch_phrases_list: if phrase.strip().lower() in twilio_recording_text: catch_phrases_found.append(phrase) advisor_name = None advisor_phone = None if call.transferred_to is not None: advisor = db.query(models.Advisor).filter(models.Advisor.phone_number==call.transferred_to).scalar() if advisor: advisor_name = advisor.full_name advisor_phone = advisor.phone_number retCalls.append({ "id": call.id, "call_id": call.call_id, "twilio_call_id": call.twilio_call_id, "summary": call.call_summary, "call_date_time": call.call_date_time, "customer_name": call.caller_name, "customer_phone": call.caller_number, "transcript": call.transcript, "catch_phrases_found": catch_phrases_found, "advisor": { "name": advisor_name, "phone": advisor_phone } }) # Initialize a dictionary to store phrase counts phrase_counts = {phrase.strip().lower(): 0 for phrase in catch_phrases_list} # Count occurrences of each phrase in transcripts for call in calls_log_data: if call.twilio_recording_text: transcript_text = call.twilio_recording_text.lower() for phrase in catch_phrases_list: phrase = phrase.strip().lower() if phrase in transcript_text: phrase_counts[phrase] += 1 # Format data for graph graph_data = [ { "phrase": phrase, "count": count } for phrase, count in phrase_counts.items() ] # Sort by count in descending order graph_data.sort(key=lambda x: x["count"], reverse=True) return { "calls": retCalls, "graph_data": graph_data, "pagination": { "page": page, "page_size": page_size, "total_pages": total_pages, "total_calls": total_calls } } @router.get("/executive-summary/data") async def get_executive_summary( request: Dict[str, Any] = Body(...), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Get executive summary for a given date range. Returns executive summary data along with total count. """ start_date = request.get('start_date') end_date = request.get('end_date') advisor_id = request.get('advisor_id') timezone = request.get('timezone', 'America/Los_Angeles') today = date.today() if not start_date: start_date = today - timedelta(days=30) if not end_date: end_date = today advisor = db.query(models.Advisor).filter(models.Advisor.id == advisor_id).first() # Query base for calls within date range calls_query = db.query(models.CallsLog).filter( models.CallsLog.call_date_time >= start_date, models.CallsLog.call_date_time <= end_date ) # a. Number of incoming calls incoming_calls = calls_query.count() # b. Top 10 calling numbers top_callers = ( calls_query .with_entities( models.CallsLog.caller_number, func.count(models.CallsLog.id).label('call_count') ) .group_by(models.CallsLog.caller_number) .order_by(desc('call_count')) .limit(10) .all() ) # c & d. Advisor call statistics advisor_stats = ( db.query( models.Advisor.id, models.Advisor.full_name, func.count(case((models.TransferResult.status == False, 1))).label('missed_calls'), func.count(case((models.TransferResult.status == True, 1))).label('answered_calls') ) .outerjoin(models.TransferResult, models.TransferResult.transferred_to == models.Advisor.id) .filter(models.TransferResult.call_datetime.between(start_date, end_date)) .group_by(models.Advisor.id, models.Advisor.full_name) .all() ) # e. Advisor online/offline and call duration stats advisor_time_stats = ( db.query( models.Advisor.id, models.Advisor.full_name, func.sum(models.CallsLog.call_duration).label('total_call_time'), func.count(case((models.Advisor.status == '1', 1))).label('online_count'), func.count(case((models.Advisor.status == '0', 1))).label('offline_count') ) .outerjoin(models.CallsLog, models.CallsLog.transferred_to == models.Advisor.phone_number) .filter(models.CallsLog.call_date_time.between(start_date, end_date)) .group_by(models.Advisor.id, models.Advisor.full_name) .all() ) # f. Customer sentiment ratings sentiment_stats = ( db.query( models.Advisor.id, models.Advisor.full_name, func.avg( case( (models.CallsLog.sentiment_score == 'Positive', 1.0), (models.CallsLog.sentiment_score == 'Neutral', 0.0), (models.CallsLog.sentiment_score == 'Negative', -1.0), else_=None ) ).label('avg_sentiment'), func.count(models.CallsLog.sentiment_score).label('rated_calls') ) .join(models.CallsLog, models.CallsLog.transferred_to == models.Advisor.phone_number) .filter(models.CallsLog.call_date_time.between(start_date, end_date)) .group_by(models.Advisor.id, models.Advisor.full_name) .all() ) # g. Historical timeline for specific customer customer_history = None if advisor and advisor.phone_number: customer_history = ( db.query( models.CallsLog, models.TransferResult.status ) .outerjoin( models.TransferResult, models.CallsLog.twilio_call_id == models.TransferResult.twilio_call_id ) .filter( or_( models.CallsLog.caller_number == advisor.phone_number, models.CallsLog.transferred_to == advisor.phone_number ) ) .order_by(models.CallsLog.call_date_time.desc()) .all() ) return { "incoming_calls": incoming_calls, "top_callers": [{"number": c[0], "count": c[1]} for c in top_callers], "advisor_stats": [ { "advisor_id": stat[0], "advisor_name": stat[1], "missed_calls": stat[2], "answered_calls": stat[3] } for stat in advisor_stats ], "advisor_time_stats": [ { "advisor_id": stat[0], "advisor_name": stat[1], "total_call_time_seconds": stat[2], "online_hours": stat[3], "offline_hours": stat[4] } for stat in advisor_time_stats ], "sentiment_stats": [ { "advisor_id": stat[0], "advisor_name": stat[1], "avg_sentiment": float(stat[2]) if stat[2] else None, "rated_calls": stat[3] } for stat in sentiment_stats ], "customer_history": [ { "call_id": call.CallsLog.id, "duration": call.CallsLog.call_duration, "created_at": call.CallsLog.call_date_time, "status": "successful" if call.status else "missed" if call.status is not None else "not-transferred" } for call in (customer_history or []) ] } @router.get("/reports/incoming-calls-report") async def incoming_calls_report( db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Get monthly incoming calls and missed call percentage for the current year. """ current_year = datetime.now().year calls = db.query( func.extract('month', models.CallsLog.call_date_time).label('month'), func.count(models.CallsLog.id).label('total_calls'), func.sum(case((models.TransferResult.status == False, 1), else_=0)).label('missed_calls') ).join( models.TransferResult, models.CallsLog.twilio_call_id == models.TransferResult.twilio_call_id ).filter( func.extract('year', models.CallsLog.call_date_time) == current_year, models.CallsLog.company_id == current_user.get('default_company') ).group_by('month').order_by('month').all() data = [] for row in calls: month_idx = int(row.month) data.append({ "month": month_name[month_idx], "calls": row.total_calls, "missed": f"{round((row.missed_calls / row.total_calls) * 100) if row.total_calls else 0}%" }) return {"data": data} @router.post("/reports/top-calling-numbers") async def top_calling_numbers( request: Dict[str, Any] = Body(...), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Get top 10 calling numbers (numbers that called the most) with their details. Request body: { "start_date": "2023-01-01", // Optional, defaults to 30 days ago "end_date": "2023-01-31" // Optional, defaults to current date } """ start_date = request.get('start_date') end_date = request.get('end_date') if isinstance(start_date, str): try: start_date = datetime.fromisoformat(start_date) except ValueError: start_date = datetime.strptime(start_date, "%Y-%m-%d") if isinstance(end_date, str): try: end_date = datetime.fromisoformat(end_date) except ValueError: end_date = datetime.strptime(end_date, "%Y-%m-%d") end_date = end_date.replace(hour=23, minute=59, second=59) today = datetime.now() if not start_date: start_date = today - timedelta(days=30) if not end_date: end_date = today if start_date: start_date = convert_to_utc(start_date) if end_date: end_date = convert_to_utc(end_date) start_datetime = datetime.combine(start_date, datetime.min.time()) end_datetime = datetime.combine(end_date, datetime.max.time()) top_calls = ( db.query( models.CallsLog.caller_number, models.CallsLog.twilio_call_id, func.count(models.CallsLog.id).label('call_count'), func.max(models.CallsLog.call_date_time).label('last_call_time'), func.max(models.CallsLog.caller_name).label('caller_name'), func.max(models.CallsLog.call_id).label('call_id'), func.max(models.CallsLog.call_summary).label('call_summary'), func.max(models.CallsLog.sentiment_score).label('sentiment_score'), func.max(models.CallsLog.call_duration).label('last_duration'), func.max(models.CallsLog.transcript).label('last_transcript') ) .filter( models.Advisor.type == 'advisor', models.CallsLog.call_date_time >= start_datetime, models.CallsLog.call_date_time <= end_datetime, # models.CallsLog.company_id == current_user.get('default_company') ) .group_by(models.CallsLog.caller_number, models.CallsLog.twilio_call_id) .order_by(desc('call_count')) .limit(10) .all() ) data = [] for call in top_calls: data.append({ "twilio_call_id": call.twilio_call_id, "call_date_time": call.last_call_time, "customer_name": call.caller_name or "Unknown", "customer_number": call.caller_number, "call_count": call.call_count, "duration": str(call.last_duration) if call.last_duration else "0", "transcript": call.last_transcript or "", "call_id": call.call_id, "sentiment_score": call.sentiment_score, "call_summary": call.call_summary }) return {"data": data} @router.post("/reports/worst-attendees") async def worst_call_attendees( request: Dict[str, Any] = Body(...), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Get top 10 worst call attendees based on missed calls. """ start_date = request.get('start_date') end_date = request.get('end_date') if isinstance(start_date, str): try: start_date = datetime.fromisoformat(start_date) except ValueError: start_date = datetime.strptime(start_date, "%Y-%m-%d") if isinstance(end_date, str): try: end_date = datetime.fromisoformat(end_date) except ValueError: end_date = datetime.strptime(end_date, "%Y-%m-%d") end_date = end_date.replace(hour=23, minute=59, second=59) today = datetime.now() if not start_date: start_date = today - timedelta(days=30) if not end_date: end_date = today if start_date: start_date = convert_to_utc(start_date) if end_date: end_date = convert_to_utc(end_date) start_datetime = datetime.combine(start_date, datetime.min.time()) end_datetime = datetime.combine(end_date, datetime.max.time()) advisor_stats = ( db.query( models.Advisor.full_name, func.count(distinct(case((models.TransferResult.status == False, models.TransferResult.id)))).label('missed_calls'), func.count(distinct(case((models.TransferResult.status == True, models.TransferResult.id)))).label('attended_calls'), func.round( case( (func.count(distinct(models.TransferResult.id)) == 0, 100), else_=func.avg(case((models.TransferResult.status == False, 1), else_=0)) * 100 ), 2 ).label('missed_calls_percentage'), func.round( case( (func.count(distinct(models.TransferResult.id)) == 0, 0), else_=func.avg(case((models.TransferResult.status == True, 1), else_=0)) * 100 ), 2 ).label('attended_calls_percentage') ) .filter( models.Advisor.type == 'advisor', models.CallsLog.call_date_time >= start_datetime, models.CallsLog.call_date_time <= end_datetime, # models.CallsLog.company_id == current_user.get('default_company') ) .join( models.TransferResult, models.TransferResult.transferred_to == models.Advisor.id ) .filter( models.TransferResult.call_datetime >= start_datetime, models.TransferResult.call_datetime <= end_datetime ) .group_by(models.Advisor.full_name) .having(func.count(distinct(models.TransferResult.id)) > 0) # Only include advisors with calls .order_by(desc('missed_calls_percentage')) .limit(10) .all() ) data = [] for stat in advisor_stats: data.append({ "advisor_name": stat.full_name, "calls_missed": stat.missed_calls, "calls_attended": stat.attended_calls, "average_missed": f"{stat.missed_calls_percentage}%", "average_attended": f"{stat.attended_calls_percentage}%" }) return {"data": data} @router.post("/reports/best-attendees") async def best_call_attendees( request: Dict[str, Any] = Body(...), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Get top 10 best call attendees based on attended calls. """ start_date = request.get('start_date') end_date = request.get('end_date') if isinstance(start_date, str): try: start_date = datetime.fromisoformat(start_date) except ValueError: start_date = datetime.strptime(start_date, "%Y-%m-%d") if isinstance(end_date, str): try: end_date = datetime.fromisoformat(end_date) except ValueError: end_date = datetime.strptime(end_date, "%Y-%m-%d") end_date = end_date.replace(hour=23, minute=59, second=59) today = datetime.now() if not start_date: start_date = today - timedelta(days=30) if not end_date: end_date = today if start_date: start_date = convert_to_utc(start_date) if end_date: end_date = convert_to_utc(end_date) start_datetime = datetime.combine(start_date, datetime.min.time()) end_datetime = datetime.combine(end_date, datetime.max.time()) advisor_stats = ( db.query( models.Advisor.full_name, func.count(distinct(case((models.TransferResult.status == False, models.TransferResult.id)))).label('missed_calls'), func.count(distinct(case((models.TransferResult.status == True, models.TransferResult.id)))).label('attended_calls'), func.round( case( (func.count(distinct(models.TransferResult.id)) == 0, 100), else_=func.avg(case((models.TransferResult.status == False, 1), else_=0)) * 100 ), 2 ).label('missed_calls_percentage'), func.round( case( (func.count(distinct(models.TransferResult.id)) == 0, 0), else_=func.avg(case((models.TransferResult.status == True, 1), else_=0)) * 100 ), 2 ).label('attended_calls_percentage') ) .filter( models.Advisor.type == 'advisor', models.CallsLog.call_date_time >= start_datetime, models.CallsLog.call_date_time <= end_datetime, # models.CallsLog.company_id == current_user.get('default_company') ) .join( models.TransferResult, models.TransferResult.transferred_to == models.Advisor.id ) .filter( models.TransferResult.call_datetime >= start_datetime, models.TransferResult.call_datetime <= end_datetime ) .group_by(models.Advisor.full_name) .having(func.count(distinct(models.TransferResult.id)) > 0) # Only include advisors with calls .order_by(desc('attended_calls_percentage')) .limit(10) .all() ) data = [] for stat in advisor_stats: data.append({ "advisor_name": stat.full_name, "calls_missed": stat.missed_calls, "calls_attended": stat.attended_calls, "average_missed": f"{stat.missed_calls_percentage}%", "avg_attended": f"{stat.attended_calls_percentage}%" }) return {"data": data} @router.post("/reports/time-utilisation") async def advisor_time_utilisation( request: Dict[str, Any] = Body(...), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Get advisor time utilisation report. """ start_date = request.get('start_date') end_date = request.get('end_date') if isinstance(start_date, str): try: start_date = datetime.fromisoformat(start_date) except ValueError: start_date = datetime.strptime(start_date, "%Y-%m-%d") if isinstance(end_date, str): try: end_date = datetime.fromisoformat(end_date) except ValueError: end_date = datetime.strptime(end_date, "%Y-%m-%d") end_date = end_date.replace(hour=23, minute=59, second=59) today = datetime.now() if not start_date: start_date = today - timedelta(days=30) if not end_date: end_date = today if start_date: start_date = convert_to_utc(start_date) if end_date: end_date = convert_to_utc(end_date) start_datetime = datetime.combine(start_date, datetime.min.time()) end_datetime = datetime.combine(end_date, datetime.max.time()) Advisor = models.Advisor LoginLog = models.LoginLog active_dates_subq = ( db.query( cast(LoginLog.log_date, Date).label("log_date") ) .filter(LoginLog.log_date >= start_datetime, LoginLog.log_date <= end_datetime) .group_by(cast(LoginLog.log_date, Date)) .having(func.sum(LoginLog.total_session) > 0) .subquery() ) advisors_subq = ( db.query( Advisor.id.label("advisor_id"), Advisor.full_name ) .filter(Advisor.type == "advisor") .subquery() ) advisor_dates = ( db.query( advisors_subq.c.advisor_id, advisors_subq.c.full_name, active_dates_subq.c.log_date ) .select_from(advisors_subq) .join(active_dates_subq, literal_column("true")) # Cartesian product .subquery() ) time_stats = ( db.query( advisor_dates.c.log_date, advisor_dates.c.full_name, func.coalesce(func.sum(LoginLog.total_session), 0).label("total_online_time") ) .outerjoin( LoginLog, and_( LoginLog.advisor_id == advisor_dates.c.advisor_id, cast(LoginLog.log_date, Date) == advisor_dates.c.log_date ) ) .group_by(advisor_dates.c.log_date, advisor_dates.c.full_name) .order_by(advisor_dates.c.log_date, advisor_dates.c.full_name) .all() ) data = [] for stat in time_stats: total_seconds = stat.total_online_time or 0 hours, remainder = divmod(total_seconds, 3600) minutes, seconds = divmod(remainder, 60) time_str = f"{int(hours):02}:{int(minutes):02}:{int(seconds):02}" data.append({ "date": stat.log_date.isoformat() if stat.log_date else '0', "advisor_name": stat.full_name, "total_time_online": time_str }) return { "data": data, "pagination": { "total_pages": 1, "page": 1, "page_size": 20 } } @router.post("/reports/customer-ratings") async def advisor_customer_ratings( request: Dict[str, Any] = Body(...), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Get advisor-wise customer ratings based on sentiment scores. """ start_date = request.get('start_date') end_date = request.get('end_date') if isinstance(start_date, str): try: start_date = datetime.fromisoformat(start_date) except ValueError: start_date = datetime.strptime(start_date, "%Y-%m-%d") if isinstance(end_date, str): try: end_date = datetime.fromisoformat(end_date) except ValueError: end_date = datetime.strptime(end_date, "%Y-%m-%d") end_date = end_date.replace(hour=23, minute=59, second=59) today = datetime.now() if not start_date: start_date = today - timedelta(days=30) if not end_date: end_date = today if start_date: start_date = convert_to_utc(start_date) if end_date: end_date = convert_to_utc(end_date) start_datetime = datetime.combine(start_date, datetime.min.time()) end_datetime = datetime.combine(end_date, datetime.max.time()) sentiment_stats = ( db.query( models.Advisor.id, models.Advisor.full_name, models.Advisor.phone_number, func.avg( case( (models.CallsLog.sentiment_score == 'Positive', 1.0), (models.CallsLog.sentiment_score == 'Neutral', 0.0), (models.CallsLog.sentiment_score == 'Negative', -1.0), else_=None ) ).label('avg_sentiment'), func.count(models.CallsLog.sentiment_score).label('rated_calls') ) .filter( models.Advisor.type == 'advisor', models.CallsLog.call_date_time >= start_datetime, models.CallsLog.call_date_time <= end_datetime, # models.CallsLog.company_id == current_user.get('default_company') ) .join( models.CallsLog, models.CallsLog.transferred_to == models.Advisor.phone_number ) .group_by( models.Advisor.id, models.Advisor.full_name ) .having(func.count(models.CallsLog.sentiment_score) > 0) # Only include advisors with ratings .order_by(desc('avg_sentiment')) .all() ) data = [] for stat in sentiment_stats: avg_sentiment = float(stat.avg_sentiment) if stat.avg_sentiment is not None else 0 sentiment_category = "Positive" if avg_sentiment > 0 else "Negative" if avg_sentiment < 0 else "Neutral" data.append({ "advisor_id": stat.id, "advisor_name": stat.full_name, "advisor_phone": stat.phone_number, "average_sentiment": round(avg_sentiment, 2), "sentiment_category": sentiment_category, "total_rated_calls": stat.rated_calls }) return {"data": data} @router.get("/customers/search") def search_customers( q: str = Query(..., description="Search by customer name or phone number"), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): """ Search customers by name or phone number (case-insensitive, partial match). Returns: id, customer_name, customer_phone """ results = ( db.query(models.Customer) .filter( or_( models.Customer.customer_name.ilike(f"%{q}%"), models.Customer.phone_number.ilike(f"%{q}%") ) ) .limit(10) .all() ) return [ { "id": customer.id, "customer_name": customer.customer_name, "customer_phone": customer.phone_number } for customer in results ] @router.post("/customers/call-history") def get_customer_call_history( request: Dict[str, Any] = Body(...), db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): customer_id = request.get('customer_id') page = request.get('page', 1) page_size = request.get('page_size', 20) start_date = request.get('start_date') end_date = request.get('end_date') if not customer_id: raise HTTPException(status_code=400, detail="Customer ID is required") if page_size > 100: page_size = 100 if isinstance(start_date, str): try: start_date = datetime.fromisoformat(start_date) except ValueError: start_date = datetime.strptime(start_date, "%Y-%m-%d") if isinstance(end_date, str): try: end_date = datetime.fromisoformat(end_date) except ValueError: end_date = datetime.strptime(end_date, "%Y-%m-%d") end_date = end_date.replace(hour=23, minute=59, second=59) today = datetime.now() if not start_date: start_date = today - timedelta(days=30) if not end_date: end_date = today if start_date: start_date = convert_to_utc(start_date) if end_date: end_date = convert_to_utc(end_date) start_datetime = datetime.combine(start_date, datetime.min.time()) end_datetime = datetime.combine(end_date, datetime.max.time()) customer = db.query(models.Customer).filter(models.Customer.id == customer_id).first() if not customer: raise HTTPException(status_code=404, detail="Customer not found") offset = (page - 1) * page_size total_calls = db.query(models.CallsLog).filter(models.CallsLog.caller_number == customer.phone_number, models.CallsLog.call_date_time >= start_datetime, models.CallsLog.call_date_time <= end_datetime, # models.CallsLog.company_id == current_user.get('default_company') ).count() total_pages = math.ceil(total_calls / page_size) calls = ( db.query( models.CallsLog, models.Advisor.full_name.label("advisor_name") ) .outerjoin( models.Advisor, models.CallsLog.transferred_to == models.Advisor.phone_number ) .filter(models.CallsLog.caller_number == customer.phone_number, models.CallsLog.call_date_time >= start_datetime, models.CallsLog.call_date_time <= end_datetime, # models.CallsLog.company_id == current_user.get('default_company') ) .order_by(models.CallsLog.call_date_time.desc()) .offset(offset) .limit(page_size) .all() ) grouped = defaultdict(list) for call, advisor_name in calls: sentiment = (call.sentiment_score or "").lower() if sentiment == "positive": sentiment_icon = "positive" elif sentiment == "negative": sentiment_icon = "negative" else: sentiment_icon = "neutral" grouped[call.call_date_time.date()].append({ "id": call.id, "twilio_call_id": call.twilio_call_id, "sentiment": call.sentiment_score, "sentiment_icon": sentiment_icon, "advisor_name": advisor_name, "summary": call.call_summary, "duration": call.call_duration, "time": call.call_date_time, "transcript": call.transcript, "call_id": call.call_id, }) history = [] for date in sorted(grouped.keys(), reverse=True): history.append({ "date": date, "calls": grouped[date] }) return { "customer_name": customer.customer_name, "customer_phone": customer.phone_number, "total_calls": total_calls, "history": history, "pagination": { "total_pages": total_pages, "page": page, "page_size": page_size } } @router.post("/call/process") async def process_call( request: Request, db: Session = Depends(get_db), current_user: Dict = Depends(validate_token) ): data = await request.json() call_id = data.get("call_id") if not call_id: raise ValidationError("Call ID is required") call = db.query(models.CallsLog).filter(models.CallsLog.id == call_id).first() if not call: raise NotFoundException("Call", call_id) try: await process_booking_intent(call, db) return Response.post(message="Call processed successfully.") except Exception as e: return Response.error(message="Something went wrong while processing the call.") finally: db.close()