import traceback
from sqlalchemy.sql import extract
from sqlalchemy.orm import Session
from sqlalchemy import func, distinct, cast, extract, text
from datetime import datetime, timedelta
from typing import Dict, Any, Optional, List, Tuple
import logging
from app.common.models import TransferResult, CallsLog
from app.advisor_service.services.time_service import convert_to_local_time
from app.common.models import Advisor as AdvisorModel

logger = logging.getLogger(__name__)

def get_unique_repetitive_counts(
    db: Session,
    start_date: datetime,
    end_date: datetime,
    user = None,
    advisor_id = None
) -> Dict[str, int]:
    """
    Get counts of unique and repetitive calls for the given date range.
    """
    try:
        query = db.query(
            CallsLog.caller_number,
            func.count(CallsLog.id).label('call_count')
        ).filter(
            CallsLog.call_date_time >= start_date,
            CallsLog.call_date_time <= end_date
        )

        if advisor_id is not None:
            query = query.filter(CallsLog.transferred_to == advisor_id)
            
        # if user is not None:
        #     query = query.filter(CallsLog.company_id == user.get('default_company'))

        query = query.group_by(CallsLog.caller_number)
        calls_in_range = query.all()

        unique_calls = 0
        repetitive_calls = 0

        for call in calls_in_range:
            if call.call_count == 1:
                unique_calls += 1
            else:
                repetitive_calls += 1

        return {
            "unique": unique_calls,
            "repetitive": repetitive_calls
        }

    except Exception as e:
        logger.error(f"Error retrieving unique/repetitive call counts: {str(e)}")
        raise

def get_daily_call_counts(
    db: Session,
    start_date: datetime,
    end_date: datetime,
    user = None,
    advisor_id = None
) -> List[Dict[str, Any]]:
    """
    Get daily counts of unique and repetitive calls for the given date range.
    Returns a list of dictionaries with date, unique and repetitive call counts.
    """
    try:
        daily_counts = {}

        current_date = start_date
        while current_date <= end_date:
            date_str = current_date.strftime("%b-%d")
            daily_counts[date_str] = {"unique": 0, "repetitive": 0}
            current_date += timedelta(days=1)

        daily_caller_counts = db.query(
            func.date_trunc('day', CallsLog.call_date_time).label('call_date'),
            CallsLog.caller_number,
            func.count(CallsLog.id).label('call_count')
        ).filter(
            CallsLog.call_date_time >= start_date,
            CallsLog.call_date_time <= end_date
        )
        
        if advisor_id:
            daily_caller_counts = daily_caller_counts.filter(CallsLog.transferred_to == advisor_id)
        
        # if user is not None:
        #     daily_caller_counts = daily_caller_counts.filter(CallsLog.company_id == user.get('default_company'))
        
        query = daily_caller_counts.group_by(
            func.date_trunc('day', CallsLog.call_date_time),
            CallsLog.caller_number
        ).all()

        for record in query:
            date_str = record.call_date.strftime("%b-%d")
            if record.call_count == 1:
                daily_counts[date_str]["unique"] += 1
            else:
                daily_counts[date_str]["repetitive"] += 1

        result = []
        for date_str, counts in daily_counts.items():
            result.append({
                "date": date_str,
                "unique": counts["unique"],
                "repetitive": counts["repetitive"]
            })

        result.sort(key=lambda x: datetime.strptime(x["date"], "%b-%d"))

        return result

    except Exception as e:
        logger.error(f"Error retrieving daily call counts: {str(e)}")
        raise

def get_hourly_call_volume(
    db: Session,
    start_date: datetime,
    end_date: datetime,
    user = None,
    advisor_id = None
) -> List[Dict[str, Any]]:
    """
    Get average call volume by hour of day for the given date range.
    Returns a list of dictionaries with time and calls count.
    """
    try:

        query = db.query(
            extract('hour', CallsLog.call_date_time).label('hour'),
            func.count(CallsLog.id).label('call_count')
        ).filter(
            CallsLog.call_date_time >= start_date,
            CallsLog.call_date_time <= end_date
        )

        if advisor_id is not None:
            query = query.filter(CallsLog.transferred_to == advisor_id)
        # if user is not None:
        #     query = query.filter(CallsLog.company_id == user.get('default_company'))
        
        query = query.group_by(
            extract('hour', CallsLog.call_date_time)
        )

        hourly_counts = query.all()
        days_in_range = (end_date - start_date).days + 1

        result = []
        for record in hourly_counts:
            utc_hour = int(record.hour)

            sample_date = datetime.combine(start_date.date(), datetime.min.time().replace(hour=utc_hour))
            local_date = convert_to_local_time(db, sample_date)
            local_hour = local_date.hour
            time_str = f"{local_hour:02d}:00"

            total_calls = record.call_count
            result.append({"time": time_str, "calls": total_calls})

        result.sort(key=lambda x: int(x["time"].split(":")[0]))
        return result

    except Exception as e:
        logger.error(f"Error retrieving hourly call volume: {str(e)}")
        raise

def get_summary_statistics(
    db: Session,
    start_date: datetime,
    end_date: datetime,
    user = None,
    advisor_id = None
) -> Dict[str, Any]:
    """
    Get summary statistics for the given date range.
    
    Args:
        db: Database session
        start_date: Start date for filtering
        end_date: End date for filtering
        advisor_id: Optional advisor ID to filter calls by advisor
    """
    try:
        total_calls_query = db.query(func.count(CallsLog.id)).filter(
            CallsLog.call_date_time >= start_date,
            CallsLog.call_date_time <= end_date
        )

        missed_calls_query = db.query(func.count(TransferResult.id)).join(
                    CallsLog,
                    CallsLog.twilio_call_id == TransferResult.twilio_call_id
                ).filter(
                    CallsLog.call_date_time >= start_date,
                    CallsLog.call_date_time <= end_date,
                    TransferResult.status == False
                )

        avg_duration_query = db.query(
            func.avg(
                text("EXTRACT(EPOCH FROM CAST(call_duration AS INTERVAL))")
            )
        ).filter(
            CallsLog.call_date_time >= start_date,
            CallsLog.call_date_time <= end_date,
            CallsLog.call_duration.isnot(None)
        )
        
        if advisor_id:
            advisor = db.query(AdvisorModel).filter(AdvisorModel.id == advisor_id).first()

            if advisor and advisor.phone_number:
                total_calls_query = total_calls_query.filter(
                    CallsLog.transferred_to == advisor.phone_number,
                    CallsLog.call_date_time >= start_date,
                    CallsLog.call_date_time <= end_date
                )

                avg_duration_query = db.query(
                    func.avg(
                        text("EXTRACT(EPOCH FROM CAST(call_duration AS INTERVAL))")
                    )
                ).select_from(CallsLog).join(
                    TransferResult,
                    TransferResult.twilio_call_id == CallsLog.twilio_call_id
                ).filter(
                    TransferResult.status == True,
                    CallsLog.transferred_to == advisor.phone_number,
                    CallsLog.call_date_time >= start_date,
                    CallsLog.call_date_time <= end_date,
                    CallsLog.call_duration.isnot(None)
                )
               
                missed_calls_query = db.query(func.count(TransferResult.id)).join(
                        CallsLog,
                        CallsLog.twilio_call_id == TransferResult.twilio_call_id
                    ).filter(
                        CallsLog.transferred_to == advisor.phone_number,
                        CallsLog.call_date_time >= start_date,
                        CallsLog.call_date_time <= end_date,
                        TransferResult.status == False
                    )
            
        # if user is not None:
        #     total_calls_query = total_calls_query.filter(CallsLog.company_id == user.get('default_company'))
        #     avg_duration_query = avg_duration_query.filter(CallsLog.company_id == user.get('default_company'))
        #     missed_calls_query = missed_calls_query.filter(CallsLog.company_id == user.get('default_company'))
        
        total_calls = total_calls_query.scalar() or 0
        total_missed_calls = missed_calls_query.scalar() or 0
        avg_duration_seconds = avg_duration_query.scalar() or 0

        minutes = int(avg_duration_seconds // 60)
        seconds = int(avg_duration_seconds % 60)
        avg_call_time = {
            "minutes": minutes,
            "seconds": seconds
        }

        return {
            "total_calls": total_calls,
            "total_missed_calls": total_missed_calls,
            "average_call_time": avg_call_time
        }

    except Exception as e:
        logger.error(f"Error retrieving summary statistics: {str(e)}")
        print(traceback.format_exc())
        raise