from sqlalchemy.orm import Session
from typing import Dict, Any, Optional, List
import json
import logging
from app.common import models
from datetime import datetime
import os
import pytz
from datetime import datetime
import json
import phonenumbers
from phonenumbers import (
parse,
format_number,
PhoneNumberFormat,
NumberParseException,
is_valid_number,
)
from app.common.services.email_service import send_email
from app.common.constants import SALES_EMAIL_CREDENTIALS
from app.common import models, schemas
# Set up logging
logger = logging.getLogger(__name__)
def get_paginated_appointments(
db: Session,
page: int,
page_size: int,
startDate: Optional[datetime],
endDate: Optional[datetime],
) -> Dict[str, Any]:
try:
offset = (page - 1) * page_size
query = db.query(models.AppointmentScheduling)
pst = pytz.timezone("US/Pacific")
# Apply date filtering if startDate and endDate are provided
if startDate and endDate:
query = query.filter(
models.AppointmentScheduling.created_at >= startDate,
models.AppointmentScheduling.created_at <= endDate,
)
total = query.count()
appointments = (
query.order_by(models.AppointmentScheduling.id.desc())
.offset(offset)
.limit(page_size)
.all()
)
def format_pst(dt):
if dt:
if dt.tzinfo is None:
dt = pytz.utc.localize(dt)
return dt.astimezone(pst).strftime("%b %d, %Y %I:%M %p")
return None
def serialize(appointment):
return {
"id": appointment.id,
"name": appointment.name,
"email": appointment.email,
"phone": appointment.phone,
"twilio_call_id": appointment.twilio_call_id,
"schedule": appointment.schedule,
"off_time": appointment.off_time,
"created_at": appointment.created_at,
"updated_at": (
appointment.updated_at.strftime("%Y-%m-%d %H:%M:%S")
if appointment.updated_at
else None
),
}
return {
"data": [serialize(a) for a in appointments],
"pagination": {
"total": total,
"total_pages": (total + page_size - 1) // page_size,
"page": page,
"page_size": page_size,
},
}
except Exception as e:
logger.error(f"Error retrieving appointments: {str(e)}")
return {
"data": [],
"pagination": {
"total": 0,
"total_pages": 0,
"page": page,
"page_size": page_size,
},
}
def fetch_call_log_by_twilio_id(
db: Session, twilio_call_id: str
) -> Optional[Dict[str, Any]]:
try:
call = (
db.query(models.CallsLog)
.filter(models.CallsLog.twilio_call_id == twilio_call_id)
.first()
)
if not call:
return None
return {
"id": call.id,
"call_date_time": (
call.call_date_time.strftime("%Y-%m-%d %H:%M:%S")
if call.call_date_time
else None
),
"call_duration": call.call_duration,
"call_cost": call.call_cost,
"call_summary": call.call_summary,
"transferred_to": call.transferred_to,
"transfer_status": call.transfer_status,
"call_id": call.call_id,
"source": call.source,
"twilio_call_id": call.twilio_call_id,
"caller_number": call.caller_number,
"caller_name": call.caller_name,
"end_reason": call.end_reason,
"sentiment_score": call.sentiment_score,
"recording_url": call.recording_url,
"transcript": call.transcript,
"call_status": call.call_status,
"booking_intent": call.booking_intent,
"booking_intent_status": call.booking_intent_status,
"twilio_recording_text": call.twilio_recording_text,
}
except Exception as e:
logger.error(f"Failed to fetch call log by twilio_call_id: {str(e)}")
return None
def format_phone_number(phone: str) -> str:
try:
phone_number = parse(phone, None)
if not is_valid_number(phone_number):
return phone
# Get raw international format: +14248423103
raw = format_number(phone_number, PhoneNumberFormat.E164)
# Format to +1 (424) 842-3103 manually
if raw.startswith("+1") and len(raw) == 12:
country_code = raw[:2] # +1
area_code = raw[2:5] # 424
central_office = raw[5:8] # 842
line_number = raw[8:] # 3103
return f"{country_code} ({area_code}) {central_office}-{line_number}"
# For non-US numbers, fallback to default INTERNATIONAL format
return format_number(phone_number, PhoneNumberFormat.INTERNATIONAL)
except NumberParseException:
return phone
def create_appointment(db: Session, appointment: schemas.AppointmentCreate):
existing = db.query(models.AppointmentScheduling).filter_by(twilio_call_id=appointment.twilio_call_id).first()
if existing:
# Update existing record
for field, value in appointment.dict().items():
setattr(existing, field, value)
db.commit()
db.refresh(existing)
return existing, True # updated
else:
# Create new record
db_appointment = models.AppointmentScheduling(**appointment.dict())
db.add(db_appointment)
db.commit()
db.refresh(db_appointment)
return db_appointment, False # newly created
def send_appointment_email(appointment):
appSchedule = datetime.fromisoformat(appointment["schedule"])
# managers = self.db.query(Advisor).filter(Advisor.type == "manager").all()
# manager_emails = [manager.email for manager in managers]
# manager_emails.append("shahzad@fluten.ai")
# manager_emails = ''
# manager_emails.append("hananali423@gmail.com")
# manager_emails.append("smalik@fluten.ai")
# manager_emails.append("mimj1800@gmail.com")
# manager_emails_str = "shahzad@fluten.ai, smalik@fluten.ai, zaidnexrupt@gmail.com, hananali423@gmail.com"
if os.environ.get("ENVIRONMENT") == "LIVE":
manager_emails_str = "ninda@surfcitynissan.com, Kgomez@puentehillsnissan.com, Kbarrita@coronanissan.com, dlopez@surfcitynissan.com, shahzad@fluten.ai, tmckinney@gkmotors.com"
print("Live emails")
else:
manager_emails_str = "shahzad@fluten.ai, smalik@fluten.ai, zaidnexrupt@gmail.com, hananali423@gmail.com"
print("Dev emails")
print(manager_emails_str, "------------------------emails")
pst = pytz.timezone("America/Los_Angeles")
current_time = datetime.now(pst).strftime("%b %d, %Y %I:%M %p")
formatted_schedule_date = appSchedule.strftime("%b %d, %Y %I:%M %p")
email_body = f"""Dear Team, A new appointment has been scheduled on DealerPulse"""
conv = format_phone_number(appointment["phone"])
html_body = f"""
Dear Team,
A new appointment has been scheduled on DealerPulse:
Timestamp: {current_time}
Name: {appointment['name']}
Phone: {conv}
Schedule: {formatted_schedule_date}
Regards,
DealerPulse Team
"""
send_email(
recipient=manager_emails_str,
subject="DealerPulse Appointment Schedule Alert [Surfcity Nissan]",
body=email_body,
html_body=html_body,
credentials=SALES_EMAIL_CREDENTIALS,
)
return True
def send_appointment_email_update(appointment):
appSchedule = datetime.fromisoformat(appointment["schedule"])
# managers = self.db.query(Advisor).filter(Advisor.type == "manager").all()
# manager_emails = [manager.email for manager in managers]
# manager_emails.append("shahzad@fluten.ai")
# manager_emails = ''
# manager_emails.append("hananali423@gmail.com")
# manager_emails.append("smalik@fluten.ai")
# manager_emails.append("mimj1800@gmail.com")
# manager_emails_str = "shahzad@fluten.ai, smalik@fluten.ai, zaidnexrupt@gmail.com, hananali423@gmail.com"
if os.environ.get("ENVIRONMENT") == "LIVE":
manager_emails_str = "ninda@surfcitynissan.com, Kgomez@puentehillsnissan.com, Kbarrita@coronanissan.com, dlopez@surfcitynissan.com, shahzad@fluten.ai, tmckinney@gkmotors.com"
print("Live emails")
else:
manager_emails_str = "shahzad@fluten.ai, smalik@fluten.ai, zaidnexrupt@gmail.com, hananali423@gmail.com"
print("Dev emails")
print(manager_emails_str, "------------------------emails")
pst = pytz.timezone("America/Los_Angeles")
current_time = datetime.now(pst).strftime("%b %d, %Y %I:%M %p")
formatted_schedule_date = appSchedule.strftime("%b %d, %Y %I:%M %p")
email_body = f"""Dear Team, An appointment has been updated on DealerPulse"""
conv = format_phone_number(appointment["phone"])
html_body = f"""
Dear Team,
An appointment has been updated on DealerPulse:
Timestamp: {current_time}
Name: {appointment['name']}
Phone: {conv}
Updated Schedule: {formatted_schedule_date}
Regards,
DealerPulse Team
"""
send_email(
recipient=manager_emails_str,
subject="DealerPulse Appointment Schedule Alert [Surfcity Nissan]",
body=email_body,
html_body=html_body,
credentials=SALES_EMAIL_CREDENTIALS,
)
return True