from sqlalchemy.orm import Session, joinedload, selectinload
from sqlalchemy import and_, or_, func, desc, case
from typing import Optional, Dict, List, Any
from datetime import datetime, timedelta
from ..models.workflow import Task, TaskComment, TaskStatus, TaskPriority
from ..models.user import User
import logging

logger = logging.getLogger(__name__)

class TaskService:
    """Task Management Service"""
    
    @staticmethod
    def create_task(
        db: Session,
        title: str,
        created_by: int,
        task_type: str = 'general',
        description: Optional[str] = None,
        priority: TaskPriority = TaskPriority.medium,
        workflow_instance_id: Optional[int] = None,
        booking_id: Optional[int] = None,
        room_id: Optional[int] = None,
        assigned_to: Optional[int] = None,
        due_date: Optional[datetime] = None,
        estimated_duration_minutes: Optional[int] = None,
        metadata: Optional[Dict[str, Any]] = None
    ) -> Task:
        """Create a new task"""
        task = Task(
            title=title,
            description=description,
            task_type=task_type,
            priority=priority,
            workflow_instance_id=workflow_instance_id,
            booking_id=booking_id,
            room_id=room_id,
            assigned_to=assigned_to,
            created_by=created_by,
            due_date=due_date,
            estimated_duration_minutes=estimated_duration_minutes,
            meta_data=metadata or {},
            status=TaskStatus.pending if not assigned_to else TaskStatus.assigned
        )
        db.add(task)
        db.commit()
        db.refresh(task)
        return task
    
    @staticmethod
    def get_tasks(
        db: Session,
        assigned_to: Optional[int] = None,
        created_by: Optional[int] = None,
        status: Optional[TaskStatus] = None,
        priority: Optional[TaskPriority] = None,
        task_type: Optional[str] = None,
        booking_id: Optional[int] = None,
        room_id: Optional[int] = None,
        workflow_instance_id: Optional[int] = None,
        overdue_only: bool = False,
        skip: int = 0,
        limit: int = 100
    ) -> List[Task]:
        """Get tasks with optional filters"""
        query = db.query(Task).options(
            joinedload(Task.assignee).selectinload(User.role),
            joinedload(Task.creator_user).selectinload(User.role)
        )
        
        if assigned_to:
            query = query.filter(Task.assigned_to == assigned_to)
        if created_by:
            query = query.filter(Task.created_by == created_by)
        if status:
            query = query.filter(Task.status == status)
        if priority:
            query = query.filter(Task.priority == priority)
        if task_type:
            query = query.filter(Task.task_type == task_type)
        if booking_id:
            query = query.filter(Task.booking_id == booking_id)
        if room_id:
            query = query.filter(Task.room_id == room_id)
        if workflow_instance_id:
            query = query.filter(Task.workflow_instance_id == workflow_instance_id)
        if overdue_only:
            now = datetime.utcnow()
            query = query.filter(
                and_(
                    Task.due_date < now,
                    Task.status != TaskStatus.completed,
                    Task.status != TaskStatus.cancelled
                )
            )
        
        # MySQL doesn't support NULLS LAST, so we use CASE to handle NULLs
        return query.order_by(
            desc(Task.priority == TaskPriority.urgent),
            desc(Task.priority == TaskPriority.high),
            case((Task.due_date.is_(None), 1), else_=0),  # NULLs last
            Task.due_date.asc(),
            Task.created_at.desc()
        ).offset(skip).limit(limit).all()
    
    @staticmethod
    def get_task_by_id(db: Session, task_id: int) -> Optional[Task]:
        """Get task by ID"""
        return db.query(Task).options(
            joinedload(Task.assignee).selectinload(User.role),
            joinedload(Task.creator_user).selectinload(User.role)
        ).filter(Task.id == task_id).first()
    
    @staticmethod
    def update_task(
        db: Session,
        task_id: int,
        title: Optional[str] = None,
        description: Optional[str] = None,
        status: Optional[TaskStatus] = None,
        priority: Optional[TaskPriority] = None,
        assigned_to: Optional[int] = None,
        due_date: Optional[datetime] = None,
        notes: Optional[str] = None,
        actual_duration_minutes: Optional[int] = None
    ) -> Optional[Task]:
        """Update task"""
        task = db.query(Task).filter(Task.id == task_id).first()
        if not task:
            return None
        
        if title is not None:
            task.title = title
        if description is not None:
            task.description = description
        if status is not None:
            task.status = status
            if status == TaskStatus.completed:
                task.completed_at = datetime.utcnow()
                if not task.actual_duration_minutes and task.created_at:
                    duration = (datetime.utcnow() - task.created_at).total_seconds() / 60
                    task.actual_duration_minutes = int(duration)
        if priority is not None:
            task.priority = priority
        if assigned_to is not None:
            task.assigned_to = assigned_to
            if assigned_to and task.status == TaskStatus.pending:
                task.status = TaskStatus.assigned
        if due_date is not None:
            task.due_date = due_date
        if notes is not None:
            task.notes = notes
        if actual_duration_minutes is not None:
            task.actual_duration_minutes = actual_duration_minutes
        
        # Update status to overdue if past due date
        if task.due_date and task.due_date < datetime.utcnow():
            if task.status not in [TaskStatus.completed, TaskStatus.cancelled]:
                task.status = TaskStatus.overdue
        
        task.updated_at = datetime.utcnow()
        db.commit()
        db.refresh(task)
        return task
    
    @staticmethod
    def assign_task(db: Session, task_id: int, user_id: int) -> Optional[Task]:
        """Assign task to a user"""
        task = db.query(Task).filter(Task.id == task_id).first()
        if not task:
            return None
        
        task.assigned_to = user_id
        if task.status == TaskStatus.pending:
            task.status = TaskStatus.assigned
        task.updated_at = datetime.utcnow()
        db.commit()
        db.refresh(task)
        return task
    
    @staticmethod
    def start_task(db: Session, task_id: int) -> Optional[Task]:
        """Mark task as in progress"""
        task = db.query(Task).filter(Task.id == task_id).first()
        if not task:
            return None
        
        task.status = TaskStatus.in_progress
        task.updated_at = datetime.utcnow()
        db.commit()
        db.refresh(task)
        return task
    
    @staticmethod
    def complete_task(db: Session, task_id: int, notes: Optional[str] = None) -> Optional[Task]:
        """Mark task as completed"""
        task = db.query(Task).filter(Task.id == task_id).first()
        if not task:
            return None
        
        task.status = TaskStatus.completed
        task.completed_at = datetime.utcnow()
        if notes:
            task.notes = (task.notes or '') + f'\n\nCompleted: {notes}'
        
        # Calculate actual duration
        if task.created_at:
            duration = (datetime.utcnow() - task.created_at).total_seconds() / 60
            task.actual_duration_minutes = int(duration)
        
        task.updated_at = datetime.utcnow()
        db.commit()
        db.refresh(task)
        
        # Update workflow instance status if this task belongs to one
        if task.workflow_instance_id:
            from ..services.workflow_service import WorkflowService
            WorkflowService.complete_workflow_instance(db, task.workflow_instance_id)
        
        return task
    
    @staticmethod
    def cancel_task(db: Session, task_id: int, reason: Optional[str] = None) -> Optional[Task]:
        """Cancel task"""
        task = db.query(Task).filter(Task.id == task_id).first()
        if not task:
            return None
        
        task.status = TaskStatus.cancelled
        if reason:
            task.notes = (task.notes or '') + f'\n\nCancelled: {reason}'
        task.updated_at = datetime.utcnow()
        db.commit()
        db.refresh(task)
        return task
    
    @staticmethod
    def add_task_comment(
        db: Session,
        task_id: int,
        user_id: int,
        comment: str
    ) -> TaskComment:
        """Add comment to task"""
        task_comment = TaskComment(
            task_id=task_id,
            user_id=user_id,
            comment=comment
        )
        db.add(task_comment)
        db.commit()
        db.refresh(task_comment)
        return task_comment
    
    @staticmethod
    def get_task_comments(db: Session, task_id: int) -> List[TaskComment]:
        """Get all comments for a task"""
        return db.query(TaskComment).options(
            joinedload(TaskComment.user)
        ).filter(
            TaskComment.task_id == task_id
        ).order_by(TaskComment.created_at.asc()).all()
    
    @staticmethod
    def get_task_statistics(
        db: Session,
        assigned_to: Optional[int] = None,
        start_date: Optional[datetime] = None,
        end_date: Optional[datetime] = None
    ) -> Dict[str, Any]:
        """Get task statistics"""
        query = db.query(Task)
        
        if assigned_to:
            query = query.filter(Task.assigned_to == assigned_to)
        if start_date:
            query = query.filter(Task.created_at >= start_date)
        if end_date:
            query = query.filter(Task.created_at <= end_date)
        
        total = query.count()
        pending = query.filter(Task.status == TaskStatus.pending).count()
        assigned = query.filter(Task.status == TaskStatus.assigned).count()
        in_progress = query.filter(Task.status == TaskStatus.in_progress).count()
        completed = query.filter(Task.status == TaskStatus.completed).count()
        overdue = query.filter(Task.status == TaskStatus.overdue).count()
        cancelled = query.filter(Task.status == TaskStatus.cancelled).count()
        
        # Calculate average completion time
        completed_tasks = query.filter(Task.status == TaskStatus.completed).all()
        avg_completion_time = None
        if completed_tasks:
            total_duration = sum(
                t.actual_duration_minutes or 0
                for t in completed_tasks
                if t.actual_duration_minutes
            )
            count_with_duration = sum(1 for t in completed_tasks if t.actual_duration_minutes)
            if count_with_duration > 0:
                avg_completion_time = total_duration / count_with_duration
        
        return {
            'total': total,
            'pending': pending,
            'assigned': assigned,
            'in_progress': in_progress,
            'completed': completed,
            'overdue': overdue,
            'cancelled': cancelled,
            'completion_rate': (completed / total * 100) if total > 0 else 0,
            'average_completion_time_minutes': avg_completion_time
        }
    
    @staticmethod
    def get_my_tasks(db: Session, user_id: int, status: Optional[TaskStatus] = None) -> List[Task]:
        """Get tasks assigned to a user"""
        query = db.query(Task).options(
            joinedload(Task.assignee).selectinload(User.role),
            joinedload(Task.creator_user).selectinload(User.role)
        ).filter(Task.assigned_to == user_id)
        
        if status:
            query = query.filter(Task.status == status)
        else:
            # Exclude completed and cancelled by default
            query = query.filter(
                Task.status.notin_([TaskStatus.completed, TaskStatus.cancelled])
            )
        
        # MySQL doesn't support NULLS LAST, so we use CASE to handle NULLs
        return query.order_by(
            desc(Task.priority == TaskPriority.urgent),
            desc(Task.priority == TaskPriority.high),
            case((Task.due_date.is_(None), 1), else_=0),  # NULLs last
            Task.due_date.asc()
        ).all()

