from sqlalchemy.orm import Session
from fastapi import HTTPException, status
from typing import List, Optional, Dict, Any
import logging
from passlib.context import CryptContext
from datetime import datetime, timedelta
#import pytz
from app.common.ai_helpers import get_speech_to_text
from app.common.constants import CallSource, CronJobStatus
from app.common.twillio_helper import get_call_recording_public_url
from app.common.utils import get_setting_value  # Import the universal method
import string
import random

from app.common import models, schemas
from .department_service import get_department
from app.common.services.email_service import send_welcome_email

logger = logging.getLogger(__name__)

# Password hashing
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")

def get_advisors(
    db: Session, 
    skip: int = 0, 
    limit: int = 100,
    order_by_status: bool = False,
    order_by_name: bool = False
):
    
    """
    Get all advisors with optional ordering by status and name.
    
    Args:
        db: Database session
        skip: Number of records to skip
        limit: Maximum number of records to return
        order_by_status: If True, order by status (available first)
        order_by_name: If True, order by full name
        
    Returns:
        List of advisors with the specified ordering
    """

    query = db.query(models.Advisor, models.User.account_status) \
        .join(models.User, models.Advisor.user_id == models.User.id) \
        .order_by(models.Advisor.status.desc(), models.Advisor.full_name) \
        .offset(skip) \
        .limit(limit)

    results = query.all()

    # Combine advisor fields with account_status
    advisors = []
    for advisor, account_status in results:
        advisor_dict = advisor.__dict__.copy()
        advisor_dict.pop("_sa_instance_state", None)
        advisor_dict["account_status"] = account_status
        advisors.append(advisor_dict)

    return advisors
    # query = db.query(models.Advisor)
    #
    # # Apply ordering if requested
    # #if order_by_status:
    #     # Order by status with '1' (available) first
    # query = query.order_by(models.Advisor.status.desc())
    #
    # #if order_by_name:
    #     # Then order by full name
    # query = query.order_by(models.Advisor.full_name)
    #
    # # Apply pagination
    # advisors = query.offset(skip).limit(limit).all()
    #
    #
    #
    # return advisors

def get_advisor(db: Session, advisor_id: int) -> models.Advisor:
    advisor = db.query(models.Advisor).filter(models.Advisor.id == advisor_id).order_by(models.Advisor.full_name.asc()).first()
    if not advisor:
        raise HTTPException(status_code=404, detail="Staff member not found")
    return advisor

def get_advisor_by_email(db: Session, email: str) -> Optional[models.Advisor]:
    return db.query(models.Advisor).filter(models.Advisor.email == email).first()

def get_advisor_by_phone(db: Session, phone_number: str) -> Optional[models.Advisor]:
    return db.query(models.Advisor).filter(models.Advisor.phone_number == phone_number).first()

def create_advisor(db: Session, advisor: schemas.AdvisorCreate, password: Optional[str] = None) -> models.Advisor:
    """
    Create a new advisor and corresponding user account
    
    Args:
        db: Database session
        advisor: Advisor data
        password: Password for the user account
        
    Returns:
        Created advisor object
    """
    # Check if advisor with same email already exists
    db_advisor = get_advisor_by_email(db, advisor.email)
    if db_advisor:
        raise HTTPException(status_code=400, detail="A staff member with this email already exists.")
    
    # Check if advisor with same name already exists (case insensitive)
    db_advisor = db.query(models.Advisor).filter(
        models.Advisor.full_name.ilike(advisor.full_name)
    ).first()
    if db_advisor:
        raise HTTPException(
            status_code=400,
            detail="A staff member with this name already exists. Please use a different name."
        )

    # Check if user with same email already exists
    db_user = db.query(models.User).filter(models.User.email == advisor.email).first()
    if db_user:
        raise HTTPException(status_code=400, detail="A staff member with this email already exists.")
    
    # Check if advisor with same phone number already exists
    db_advisor = get_advisor_by_phone(db, advisor.phone_number)
    if db_advisor:
        raise HTTPException(status_code=400, detail="A staff member with this phone number already exists.")

    # Check if department exists if department_id is provided
    if advisor.department_id:
        get_department(db, advisor.department_id)
    
    # Hash the password
    generated_password = generate_strong_password()
    hashed_password = pwd_context.hash(generated_password)
    
    # Start a transaction
    try:
        # Create new user first
        db_user = models.User(
            name=advisor.full_name,
            email=advisor.email,
            phone=advisor.phone_number,
            password=hashed_password,
            type=advisor.type
        )
        db.add(db_user)
        db.flush()  # Flush to get the user ID without committing
        
        # Create new advisor with user_id reference
        db_advisor = models.Advisor(
            user_id=db_user.id,
            full_name=advisor.full_name,
            email=advisor.email,
            phone_number=advisor.phone_number,
            status=advisor.status,
            department_id=advisor.department_id,
            current_score=0,
            type=advisor.type
        )
        db.add(db_advisor)
        
        # Commit both records
        db.commit()
        
        # Instead of refreshing, query the database for the newly created advisor
        created_advisor = db.query(models.Advisor).filter(
            models.Advisor.email == advisor.email
        ).first()
        #print("=-=-=-= BEFORE SENDING EMAIL=-=-=-=")
        #Send welcome email with credentials
        email_sent = send_welcome_email(
            recipient=advisor.email,
            name=advisor.full_name,
            password=generated_password
        )
        
        # if not email_sent:
        #     print(f"Failed to send welcome email to {advisor.email}")
        
        return created_advisor
        
    except Exception as e:
        db.rollback()
        print(f"Error creating advisor and user: {str(e)}")
        raise HTTPException(status_code=500, detail=f"Failed to create advisor: {str(e)}")

def update_advisor(db: Session, advisor_id: int, advisor: schemas.AdvisorUpdate) -> models.Advisor:
    db_advisor = get_advisor(db, advisor_id)
    
    # Check if another advisor with the new email already exists
    if advisor.email and advisor.email != db_advisor.email:
        existing = get_advisor_by_email(db, advisor.email)
        if existing:
            raise HTTPException(status_code=400, detail="A staff member with this email already exists.")
    
    # Check if department exists if department_id is provided
    if advisor.department_id:
        get_department(db, advisor.department_id)
    
    # Update advisor fields if provided
    if advisor.full_name is not None:
        db_advisor.full_name = advisor.full_name
    if advisor.email is not None:
        db_advisor.email = advisor.email
    if advisor.phone_number is not None:
        db_advisor.phone_number = advisor.phone_number
    if advisor.current_score is not None:
        db_advisor.current_score = advisor.current_score
    if advisor.status is not None:
        db_advisor.status = advisor.status
    if advisor.department_id is not None:
        db_advisor.department_id = advisor.department_id
    if advisor.type is not None:
        db_advisor.type = advisor.type
    
    user_id = db_advisor.user_id
    db_user = db.query(models.User).filter(models.User.id == user_id).first()
    if db_user and advisor.type is not None:
        db_user.type = advisor.type

    db.commit()
    db.refresh(db_advisor)
    return db_advisor

def delete_advisor(db: Session, advisor_id: int) -> dict:
    """
    Delete an advisor and their corresponding user account
    
    Args:
        db: Database session
        advisor_id: ID of the advisor to delete
        
    Returns:
        Message confirming deletion
    """
    # Get the advisor
    db_advisor = get_advisor(db, advisor_id)
    if not db_advisor:
        raise HTTPException(status_code=404, detail="Staff member not found")
    
    # Get the user_id from the advisor record
    user_id = db_advisor.user_id
    
    try:
        # Delete the advisor first (due to foreign key constraint)
        db.delete(db_advisor)
        
        # Now delete the corresponding user if it exists
        if user_id:
            db_user = db.query(models.User).filter(models.User.id == user_id).first()
            if db_user:
                db.delete(db_user)
        
        # Commit the transaction
        db.commit()
        return {"message": "Advisor and associated user account deleted successfully"}
        
    except Exception as e:
        db.rollback()
        logger.error(f"Error deleting advisor and user: {str(e)}")
        raise HTTPException(status_code=500, detail=f"Failed to delete advisor: {str(e)}")

def get_advisors_by_department(db: Session, department_id: int) -> List[models.Advisor]:
    return db.query(models.Advisor).filter(models.Advisor.department_id == department_id).all()

def update_advisor_score(db: Session, advisor_id: int, score: int) -> models.Advisor:
    db_advisor = get_advisor(db, advisor_id)
    db_advisor.current_score = score
    db.commit()
    db.refresh(db_advisor)
    return db_advisor

def update_advisor_availability(db: Session, advisor_id: int, is_available: bool) -> Optional[models.Advisor]:
    """
    Update the availability status of an advisor.
    
    Args:
        db: Database session
        advisor_id: ID of the advisor to update
        is_available: True if advisor is available, False otherwise
        
    Returns:
        Updated Advisor object or None if advisor not found
    """
    try:
        # Get the advisor
        advisor = db.query(models.Advisor).filter(models.Advisor.id == advisor_id).first()
        
        if not advisor:
            print(f"Advisor with ID {advisor_id} not found")
            return None
        
        # Update the status
        if is_available:
            advisor.status = "1"
        else:
            advisor.status = "0"
        
        # Commit changes
        db.commit()
        db.refresh(advisor)
        
        #print(f"Advisor {advisor.full_name} (ID: {advisor_id}) status updated to {advisor.status}")
        return advisor
        
    except Exception as e:
        db.rollback()
        print(f"Error updating advisor availability: {str(e)}")
        raise 

def is_advisor_available(db: Session, advisor_id: int) -> Dict[str, Any]:
    """
    Check if an advisor is currently available based on their schedule and status
    
    Args:
        db: Database session
        advisor_id: ID of the advisor to check
        
    Returns:
        Dictionary with availability status and details
    """
    try:
        # Get the advisor
        advisor = db.query(models.Advisor).filter(models.Advisor.id == advisor_id).first()
        print(f"=-=-=-=-=-=-=-=Advisor status: {advisor.status}=-=-=-=-=-=-=-=")
        if not advisor:
            return {
                "available": False,
                "reason": "Advisor not found",
                "status": None
            }
        
        # Check if the advisor has an active status (1 = available, 0 = unavailable)
        if advisor.status != "1":
            return {
                "available": False,
                "reason": "Advisor is not active",
                "status": advisor.status
            }
        
        # Get the current time
        now = datetime.now()
        
        # Check if the advisor has a current schedule
        current_schedule = (
            db.query(models.AdvisorSchedule)
            .filter(
                models.AdvisorSchedule.advisor_id == advisor_id,
                models.AdvisorSchedule.shift_period_start <= now,
                models.AdvisorSchedule.shift_period_end >= now
            )
            .first()
        )
        
        if not current_schedule:
            return {
                "available": False,
                "reason": "Advisor is not scheduled at this time",
                "status": advisor.status
            }
        
        # Check if the advisor's schedule status allows availability
        # Assuming advisor_status in the schedule is also binary (1 = available, 0 = unavailable)
        if current_schedule.advisor_status != 1:
            return {
                "available": False,
                "reason": "Advisor's schedule status is not available",
                "status": current_schedule.advisor_status
            }
        
        # If we've passed all checks, the advisor is available
        return {
            "available": True,
            "reason": "Advisor is available",
            "status": advisor.status
        }
        
    except Exception as e:
        logger.error(f"Error checking advisor availability: {str(e)}")
        return {
            "available": False,
            "reason": f"Error checking availability: {str(e)}",
            "status": None
        }

def get_timezone_from_settings(db: Session) -> str:
    """
    Get timezone from alpha_settings table
    
    Args:
        db: Database session
        
    Returns:
        str: Timezone string (e.g., 'America/New_York')
    """
    try:
        timezone_setting = db.query(models.AlphaSettings).filter(
            models.AlphaSettings.key == "timezone"
        ).first()
        return timezone_setting.value if timezone_setting else 'UTC'
    except Exception as e:
        print(f"Error fetching timezone from settings: {str(e)}")
        return 'UTC'

def get_all_available_advisors(db: Session) -> List[Dict[str, Any]]:
    """
    Get a list of all currently available advisors
    
    Args:
        db: Database session
        
    Returns:
        List of available advisors with their details
    """
    try:
        # Get timezone offset from settings (stored as "-7" etc.)
        offset_str = get_setting_value(db, "TIMEZONE_OFFSET", "0")
        offset_hours = int(offset_str)
        
        # Get UTC time and apply offset
        utc_now = datetime.utcnow()
        now = utc_now + timedelta(hours=offset_hours)
        
        print(f"Current time with offset {offset_hours}: {now}")
        
        # Get all advisors with active schedules
        # Using binary status values (1 = available, 0 = unavailable)
        available_advisors = (
            db.query(models.Advisor)
            .join(
                models.AdvisorSchedule,
                models.AdvisorSchedule.advisor_id == models.Advisor.id
            )
            .filter(
                models.AdvisorSchedule.shift_period_start <= now,
                models.AdvisorSchedule.shift_period_end >= now,
                models.AdvisorSchedule.advisor_status == 1,  # Available in schedule
                models.Advisor.status == 1  # Advisor is active
            )
            .all()
        )
        
        # Format the results
        result = []
        for advisor in available_advisors:
            result.append({
                "id": advisor.id,
                "name": advisor.name,
                "email": advisor.email,
                "phone_number": advisor.phone_number,
                "status": advisor.status
            })
            
        return result
        
    except Exception as e:
        logger.error(f"Error getting available advisors: {str(e)}")
        return [] 


def generate_strong_password(length=12):
    """Generate a strong random password"""
    # Character sets for password
    lowercase = string.ascii_lowercase
    uppercase = string.ascii_uppercase
    digits = string.digits
    special_chars = "#$%&@*!"
    
    # Ensure at least one of each type
    password = [
        random.choice(lowercase),
        random.choice(uppercase),
        random.choice(digits),
        random.choice(special_chars)
    ]
    
    # Fill the rest of the password
    remaining_length = length - 4
    all_chars = lowercase + uppercase + digits + special_chars
    password.extend(random.choice(all_chars) for _ in range(remaining_length))
    
    # Shuffle the password characters
    random.shuffle(password)
    
    # Convert list to string
    return ''.join(password)

async def process_booking_intent(call: models.CallsLog, db: Session):
    try:
        twilio_call_id = call.twilio_call_id
        print(f"=-=-=-=-=-=-=-=Processing booking intent for Twilio call ID: {twilio_call_id}=-=-=-=-=-=-=-=")
        if twilio_call_id is None:
            print(f"No Twilio call ID for call {call.id}")
            return

        # Update status to processing
        call.booking_intent_status = CronJobStatus.RUNNING
        db.commit()
        
        recording_public_url = get_call_recording_public_url(twilio_call_id)
        if recording_public_url is None:
            print(f"Could not get recording URL for call {twilio_call_id}")
            call.booking_intent_status = CronJobStatus.FAILED
            db.commit()
            return
        
        transcript, booking_intent, booking_datetime, transcript_with_speaker_labels = get_speech_to_text(recording_public_url, speaker_labels=call.source==CallSource.ULTRAVOX)
        if transcript is None:
            print(f"Failed to get transcript for call {twilio_call_id}")
            call.booking_intent_status = CronJobStatus.FAILED
            db.commit()
            return

        print(f"=-=-=-=-=-=-=-=Transcript: {transcript}=-=-=-=-=-=-=-=")
        print(f"=-=-=-=-=-=-=-=Booking intent: {booking_intent}=-=-=-=-=-=-=-=")
        print(f"=-=-=-=-=-=-=-=Booking datetime: {booking_datetime}=-=-=-=-=-=-=-=")
        logger.info(f"=-=-=-=-=-=-=-=Transcript: {transcript}=-=-=-=-=-=-=-=")
        logger.info(f"=-=-=-=-=-=-=-=Booking intent: {booking_intent}=-=-=-=-=-=-=-=")
        
        transcript_with_speaker_labels_text = ""
        if transcript_with_speaker_labels and call.source == CallSource.ULTRAVOX:
            for utterance in transcript_with_speaker_labels:
                speaker_name = 'Agent:'
                if utterance.speaker == 'A':
                    speaker_name = 'Agent:'
                elif utterance.speaker == 'B':
                    speaker_name = 'User:'
                elif utterance.speaker == 'C':
                    speaker_name = 'Advisor:'
                transcript_with_speaker_labels_text += f"{speaker_name}{utterance.text} "
            logger.info(f"Formatted transcript:\n{transcript_with_speaker_labels_text}")
            call.transcript=transcript_with_speaker_labels_text

        call.twilio_recording_text = transcript
        call.booking_intent = booking_intent
        call.schedule_datetime = booking_datetime
        call.booking_intent_status = CronJobStatus.COMPLETED
        db.commit()
        db.refresh(call)
        print(f"=-=-=-=-=-=-=-=Completed processing booking intent for Twilio call ID: {twilio_call_id}=-=-=-=-=-=-=-=")
    except Exception as e:
        print(f"Error processing call {call.id}: {str(e)}")
        call.booking_intent_status = CronJobStatus.FAILED
        db.commit()