from sqlalchemy.orm import Session
from fastapi import HTTPException, status
from datetime import datetime, time, timedelta, date
from typing import List, Dict, Any, Optional
import os
import logging
from app.common.models import Advisor, AdvisorSchedule, User
from app.common import schemas

logger = logging.getLogger(__name__)

# Parse shift times from environment variables
def parse_time(time_str: str) -> time:
    """Parse time string in format HH:MM to time object"""
    hours, minutes = map(int, time_str.split(':'))
    return time(hours, minutes)

SHIFTS = {
    "SHIFT_1": {
        "start": parse_time(os.getenv("SHIFT_1_START", "09:00")),
        "end": parse_time(os.getenv("SHIFT_1_END", "17:00")),
        "name": "Morning Shift"
    },
    "SHIFT_2": {
        "start": parse_time(os.getenv("SHIFT_2_START", "17:00")),
        "end": parse_time(os.getenv("SHIFT_2_END", "01:00")),
        "name": "Evening Shift"
    },
    "SHIFT_3": {
        "start": parse_time(os.getenv("SHIFT_3_START", "01:00")),
        "end": parse_time(os.getenv("SHIFT_3_END", "09:00")),
        "name": "Night Shift"
    }
}

def is_advisor_in_shift(advisor_id: int, db: Session) -> bool:
    """
    Check if an advisor is currently in their scheduled shift
    
    Args:
        advisor_id: ID of the advisor to check
        db: Database session
        
    Returns:
        bool: True if advisor is in shift, False otherwise
    """
    try:
        # Get current date and time
        now = datetime.now()
        current_time = now.time()
        current_date = now.date()
        
        # Get advisor's schedule for today
        schedule = db.query(AdvisorSchedule).filter(
            AdvisorSchedule.advisor_id == advisor_id,
            AdvisorSchedule.shift_period_start <= current_date,
            AdvisorSchedule.shift_period_end >= current_date
        ).first()
        
        if not schedule:
            return False
        
        # Check if current time is within the shift
        shift_info = SHIFTS.get(schedule.shift)
        if not shift_info:
            return False
        
        shift_start = shift_info["start"]
        shift_end = shift_info["end"]
        
        # Handle overnight shifts
        if shift_start > shift_end:  # e.g., 17:00 to 01:00
            return current_time >= shift_start or current_time <= shift_end
        else:
            return shift_start <= current_time <= shift_end
            
    except Exception as e:
        logger.error(f"Error checking if advisor is in shift: {str(e)}")
        return False

def create_advisor_schedule(
    db: Session, 
    schedule_data: schemas.AdvisorScheduleCreate,
    manager_id: int
) -> AdvisorSchedule:
    """
    Create a new schedule for an advisor
    If there's an overlapping schedule, it will be overridden
    
    Args:
        db: Database session
        schedule_data: Schedule data
        manager_id: ID of the manager creating the schedule
        
    Returns:
        Created schedule
    """
    # Check if advisor exists
    advisor = db.query(Advisor).filter(Advisor.id == schedule_data.advisor_id).first()
    if not advisor:
        raise HTTPException(status_code=404, detail="Advisor not found")
    
    # Check if there's an overlapping schedule
    overlapping_schedule = db.query(AdvisorSchedule).filter(
        AdvisorSchedule.advisor_id == schedule_data.advisor_id,
        AdvisorSchedule.shift_period_start <= schedule_data.shift_period_end,
        AdvisorSchedule.shift_period_end >= schedule_data.shift_period_start
    ).first()
    
    # If there's an overlapping schedule, delete it
    if overlapping_schedule:
        db.delete(overlapping_schedule)
        db.commit()
    
    # Create new schedule
    db_schedule = AdvisorSchedule(
        advisor_id=schedule_data.advisor_id,
        shift_setup_by=manager_id,
        shift_period_start=schedule_data.shift_period_start,
        shift_period_end=schedule_data.shift_period_end,
        shift=schedule_data.shift,
        advisor_status=schedule_data.advisor_status
    )
    
    db.add(db_schedule)
    db.commit()
    db.refresh(db_schedule)
    
    # Update advisor status based on current shift
    update_advisor_status(db, schedule_data.advisor_id)
    
    return db_schedule

def update_advisor_schedule(
    db: Session,
    schedule_id: int,
    schedule_data: schemas.AdvisorScheduleUpdate,
    manager_id: int
) -> AdvisorSchedule:
    """
    Update an existing advisor schedule
    
    Args:
        db: Database session
        schedule_id: ID of the schedule to update
        schedule_data: Updated schedule data
        manager_id: ID of the manager updating the schedule
        
    Returns:
        Updated schedule
    """
    # Get the schedule
    db_schedule = db.query(AdvisorSchedule).filter(AdvisorSchedule.id == schedule_id).first()
    if not db_schedule:
        raise HTTPException(status_code=404, detail="Schedule not found")
    
    # Update fields
    if schedule_data.shift_period_start is not None:
        db_schedule.shift_period_start = schedule_data.shift_period_start
    
    if schedule_data.shift_period_end is not None:
        db_schedule.shift_period_end = schedule_data.shift_period_end
    
    if schedule_data.shift is not None:
        db_schedule.shift = schedule_data.shift
    
    if schedule_data.advisor_status is not None:
        db_schedule.advisor_status = schedule_data.advisor_status
    
    # Update who modified it
    db_schedule.shift_setup_by = manager_id
    db_schedule.updated_on = datetime.utcnow()
    
    db.commit()
    db.refresh(db_schedule)
    
    # Update advisor status based on current shift
    update_advisor_status(db, db_schedule.advisor_id)
    
    return db_schedule

def delete_advisor_schedule(db: Session, schedule_id: int) -> Dict[str, Any]:
    """
    Delete an advisor schedule
    
    Args:
        db: Database session
        schedule_id: ID of the schedule to delete
        
    Returns:
        Success message
    """
    # Get the schedule
    db_schedule = db.query(AdvisorSchedule).filter(AdvisorSchedule.id == schedule_id).first()
    if not db_schedule:
        raise HTTPException(status_code=404, detail="Schedule not found")
    
    advisor_id = db_schedule.advisor_id
    
    # Delete the schedule
    db.delete(db_schedule)
    db.commit()
    
    # Update advisor status based on current shift
    update_advisor_status(db, advisor_id)
    
    return {"message": "Schedule deleted successfully"}

def get_advisor_schedules(
    db: Session, 
    advisor_id: Optional[int] = None,
    start_date: Optional[datetime] = None,
    end_date: Optional[datetime] = None
) -> List[Dict[str, Any]]:
    """
    Get advisor schedules with optional filtering, including advisor details
    
    Args:
        db: Database session
        advisor_id: Optional advisor ID to filter by
        start_date: Optional start date to filter by
        end_date: Optional end date to filter by
        
    Returns:
        List of advisor schedules with advisor details
    """
    query = (
        db.query(AdvisorSchedule)
        .join(
            Advisor,
            AdvisorSchedule.advisor_id == Advisor.id,
            isouter=True  # Use left outer join to ensure we get schedules even if advisor details are missing
        )
        .add_columns(
            Advisor.full_name.label('advisor_name'),
            Advisor.type.label('advisor_type')
        )
    )
    
    # Apply filters
    if advisor_id:
        query = query.filter(AdvisorSchedule.advisor_id == advisor_id)
    
    if start_date:
        query = query.filter(AdvisorSchedule.shift_period_end >= start_date)
    
    if end_date:
        query = query.filter(AdvisorSchedule.shift_period_start <= end_date)
    
    # Order by start date
    query = query.order_by(AdvisorSchedule.shift_period_start)
    
    # Format the results
    results = []
    for row in query.all():
        schedule = row[0]  # The AdvisorSchedule object
        advisor_name = row.advisor_name  # Using the label we defined
        advisor_type = row.advisor_type  # Using the label we defined
        
        results.append({
            "id": schedule.id,
            "advisor_id": schedule.advisor_id,
            "advisor_name": advisor_name,
            "advisor_type": advisor_type,
            "shift_period_start": schedule.shift_period_start,
            "shift_period_end": schedule.shift_period_end,
            "advisor_status": schedule.advisor_status,
            "shift": schedule.shift,
            "created_at": schedule.created_at,
            "updated_on": schedule.updated_on
        })
    
    return results

def update_advisor_status(db: Session, advisor_id: int) -> bool:
    """
    Update an advisor's status based on their current schedule
    
    Args:
        db: Database session
        advisor_id: ID of the advisor to update
        
    Returns:
        bool: True if advisor is in shift, False otherwise
    """
    try:
        # Check if advisor is in shift
        in_shift = is_advisor_in_shift(advisor_id, db)
        
        # Get the advisor
        advisor = db.query(Advisor).filter(Advisor.id == advisor_id).first()
        if not advisor:
            logger.error(f"Advisor with ID {advisor_id} not found")
            return False
        
        # Update advisor status
        if in_shift:
            # Only set to online if they're in shift and their schedule allows it
            schedule = db.query(AdvisorSchedule).filter(
                AdvisorSchedule.advisor_id == advisor_id,
                AdvisorSchedule.shift_period_start <= datetime.now().date(),
                AdvisorSchedule.shift_period_end >= datetime.now().date()
            ).first()
            
            if schedule and schedule.advisor_status:
                advisor.status = "1"  # Online
            else:
                advisor.status = "0"  # Offline
        else:
            advisor.status = "0"  # Offline
        
        # Also update the user status
        if advisor.user_id:
            user = db.query(User).filter(User.id == advisor.user_id).first()
            if user:
                user.status = advisor.status
        
        db.commit()
        return in_shift
        
    except Exception as e:
        logger.error(f"Error updating advisor status: {str(e)}")
        db.rollback()
        return False

def refresh_all_advisor_statuses(db: Session) -> Dict[str, Any]:
    """
    Refresh the status of all advisors based on their schedules
    
    Args:
        db: Database session
        
    Returns:
        Summary of updates
    """
    try:
        # Get all advisors
        advisors = db.query(Advisor).all()
        
        updated_count = 0
        online_count = 0
        offline_count = 0
        
        for advisor in advisors:
            in_shift = update_advisor_status(db, advisor.id)
            updated_count += 1
            
            if in_shift:
                online_count += 1
            else:
                offline_count += 1
        
        return {
            "message": "All advisor statuses refreshed",
            "total_updated": updated_count,
            "online": online_count,
            "offline": offline_count
        }
        
    except Exception as e:
        logger.error(f"Error refreshing all advisor statuses: {str(e)}")
        db.rollback()
        raise HTTPException(
            status_code=500,
            detail=f"Error refreshing advisor statuses: {str(e)}"
        )

def create_bulk_advisor_schedules(
    db: Session,
    advisor_ids: List[int],
    base_schedule: Dict[str, Any],
    manager_id: int
) -> Dict[str, Any]:
    """
    Create schedules for multiple advisors at once
    
    Args:
        db: Database session
        advisor_ids: List of advisor IDs
        base_schedule: Base schedule data (without advisor_id)
        manager_id: ID of the manager creating the schedules
        
    Returns:
        Summary of created schedules
    """
    results = {
        "success": [],
        "failed": []
    }
    
    for advisor_id in advisor_ids:
        try:
            # Create a complete schedule object with advisor_id
            schedule_data = {**base_schedule, "advisor_id": advisor_id}
            schedule_create = schemas.AdvisorScheduleCreate(**schedule_data)
            
            # Create the schedule
            schedule = create_advisor_schedule(db, schedule_create, manager_id)
            
            # Add to success list
            results["success"].append({
                "advisor_id": advisor_id,
                "schedule_id": schedule.id
            })
            
        except Exception as e:
            # Add to failed list
            results["failed"].append({
                "advisor_id": advisor_id,
                "error": str(e)
            })
    
    return results

def get_advisors_by_shift(
    db: Session,
    shift: str,
    check_date: Optional[date] = None
) -> List[Dict[str, Any]]:
    """
    Get all advisors scheduled for a specific shift on a given date
    
    Args:
        db: Database session
        shift: Shift identifier (e.g., "SHIFT_1")
        check_date: Date to check (defaults to today)
        
    Returns:
        List of advisors with their schedule details
    """
    # If no date provided, use today
    if check_date is None:
        check_date = datetime.now().date()
    
    # Convert to datetime for database query
    check_datetime = datetime.combine(check_date, datetime.min.time())
    
    # Get all schedules for the specified shift and date
    schedules = db.query(AdvisorSchedule).filter(
        AdvisorSchedule.shift == shift,
        AdvisorSchedule.shift_period_start <= check_datetime,
        AdvisorSchedule.shift_period_end >= check_datetime,
        AdvisorSchedule.advisor_status == True
    ).all()
    
    # Get advisor details for each schedule
    result = []
    for schedule in schedules:
        advisor = db.query(Advisor).filter(
            Advisor.id == schedule.advisor_id
        ).first()
        
        if advisor:
            result.append({
                "advisor_id": advisor.id,
                "full_name": advisor.full_name,
                "email": advisor.email,
                "phone_number": advisor.phone_number,
                "status": advisor.status,
                "schedule_id": schedule.id,
                "shift": schedule.shift,
                "shift_period_start": schedule.shift_period_start,
                "shift_period_end": schedule.shift_period_end
            })
    
    return result

def get_available_advisors_at_datetime(
    db: Session,
    check_datetime: datetime
) -> List[Dict[str, Any]]:
    """
    Get advisors available at a specific date and time
    
    Args:
        db: Database session
        check_datetime: Datetime to check availability
        
    Returns:
        List of advisors with their IDs and statuses
    """
    # Get the date and time components
    check_date = check_datetime.date()
    check_time = check_datetime.time()
    
    # Find which shift this time belongs to
    target_shift = None
    for shift_key, shift_info in SHIFTS.items():
        shift_start = shift_info["start"]
        shift_end = shift_info["end"]
        
        # Handle shifts that cross midnight
        if shift_end < shift_start:
            # If time is after start or before end, it's in this shift
            if check_time >= shift_start or check_time <= shift_end:
                target_shift = shift_key
                break
        else:
            # Normal case: if time is between start and end
            if shift_start <= check_time <= shift_end:
                target_shift = shift_key
                break
    
    if not target_shift:
        return []  # No shift found for this time
    
    # Get all schedules for the specified date and shift
    schedules = db.query(AdvisorSchedule).filter(
        AdvisorSchedule.shift == target_shift,
        AdvisorSchedule.shift_period_start <= check_date,
        AdvisorSchedule.shift_period_end >= check_date,
        AdvisorSchedule.advisor_status == True
    ).all()
    
    # Get advisor details for each schedule
    result = []
    for schedule in schedules:
        advisor = db.query(Advisor).filter(
            Advisor.id == schedule.advisor_id
        ).first()
        
        if advisor:
            result.append({
                "advisor_id": advisor.id,
                "full_name": advisor.full_name,
                "email": advisor.email,
                "current_status": advisor.status,
                "shift": schedule.shift,
                "schedule_id": schedule.id
            })
    
    return result