from fastapi import APIRouter, Depends, HTTPException, status, Query, Request
from sqlalchemy.orm import Session, joinedload, load_only
from sqlalchemy import and_, or_, func, desc
from typing import List, Optional
from datetime import datetime, timedelta
from ..config.database import get_db
from ..middleware.auth import get_current_user, authorize_roles
from ..models.user import User
from ..models.role import Role
from ..models.room import Room, RoomStatus
from ..models.booking import Booking, BookingStatus
from ..models.room_maintenance import RoomMaintenance, MaintenanceType, MaintenanceStatus
from ..models.housekeeping_task import HousekeepingTask, HousekeepingStatus, HousekeepingType
from ..models.room_inspection import RoomInspection, InspectionType, InspectionStatus
from ..models.room_attribute import RoomAttribute
from ..services.room_assignment_service import RoomAssignmentService
from pydantic import BaseModel
from typing import Dict, Any

router = APIRouter(prefix='/advanced-rooms', tags=['advanced-room-management'])


# ==================== Room Assignment Optimization ====================

@router.post('/assign-optimal-room')
async def assign_optimal_room(
    request_data: dict,
    current_user: User = Depends(authorize_roles('admin', 'staff')),
    db: Session = Depends(get_db)
):
    """Find the best available room for a booking based on preferences"""
    try:
        room_type_id = request_data.get('room_type_id')
        check_in_str = request_data.get('check_in')
        check_out_str = request_data.get('check_out')
        num_guests = request_data.get('num_guests', 1)
        guest_preferences = request_data.get('guest_preferences', {})
        exclude_room_ids = request_data.get('exclude_room_ids', [])
        
        if not room_type_id or not check_in_str or not check_out_str:
            raise HTTPException(status_code=400, detail='Missing required fields')
        
        check_in = datetime.fromisoformat(check_in_str.replace('Z', '+00:00'))
        check_out = datetime.fromisoformat(check_out_str.replace('Z', '+00:00'))
        
        best_room = RoomAssignmentService.find_best_room(
            db=db,
            room_type_id=room_type_id,
            check_in=check_in,
            check_out=check_out,
            num_guests=num_guests,
            guest_preferences=guest_preferences,
            exclude_room_ids=exclude_room_ids
        )
        
        if not best_room:
            return {
                'status': 'success',
                'data': {'room': None, 'message': 'No suitable room available'}
            }
        
        return {
            'status': 'success',
            'data': {
                'room': {
                    'id': best_room.id,
                    'room_number': best_room.room_number,
                    'floor': best_room.floor,
                    'view': best_room.view,
                    'status': best_room.status.value
                }
            }
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get('/{room_id}/availability-calendar')
async def get_room_availability_calendar(
    room_id: int,
    start_date: str = Query(...),
    end_date: str = Query(...),
    current_user: User = Depends(authorize_roles('admin', 'staff')),
    db: Session = Depends(get_db)
):
    """Get detailed availability calendar for a room"""
    try:
        start = datetime.fromisoformat(start_date.replace('Z', '+00:00'))
        end = datetime.fromisoformat(end_date.replace('Z', '+00:00'))
        
        calendar = RoomAssignmentService.get_room_availability_calendar(
            db=db,
            room_id=room_id,
            start_date=start,
            end_date=end
        )
        
        if not calendar:
            raise HTTPException(status_code=404, detail='Room not found')
        
        return {'status': 'success', 'data': calendar}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


# ==================== Room Maintenance ====================

@router.get('/maintenance')
async def get_maintenance_records(
    room_id: Optional[int] = Query(None),
    status: Optional[str] = Query(None),
    maintenance_type: Optional[str] = Query(None),
    page: int = Query(1, ge=1),
    limit: int = Query(20, ge=1, le=100),
    current_user: User = Depends(authorize_roles('admin', 'staff')),
    db: Session = Depends(get_db)
):
    """Get maintenance records with filtering"""
    try:
        # Check if user is staff (not admin) - staff should only see their assigned records
        role = db.query(Role).filter(Role.id == current_user.role_id).first()
        is_staff = role and role.name == 'staff'
        
        query = db.query(RoomMaintenance)
        
        # Filter by assigned_to for staff users
        if is_staff:
            query = query.filter(RoomMaintenance.assigned_to == current_user.id)
        
        if room_id:
            query = query.filter(RoomMaintenance.room_id == room_id)
        if status:
            query = query.filter(RoomMaintenance.status == MaintenanceStatus(status))
        if maintenance_type:
            query = query.filter(RoomMaintenance.maintenance_type == MaintenanceType(maintenance_type))
        
        total = query.count()
        query = query.order_by(desc(RoomMaintenance.scheduled_start))
        
        offset = (page - 1) * limit
        records = query.offset(offset).limit(limit).all()
        
        result = []
        for record in records:
            result.append({
                'id': record.id,
                'room_id': record.room_id,
                'room_number': record.room.room_number if record.room else None,
                'maintenance_type': record.maintenance_type.value,
                'status': record.status.value,
                'title': record.title,
                'description': record.description,
                'scheduled_start': record.scheduled_start.isoformat() if record.scheduled_start else None,
                'scheduled_end': record.scheduled_end.isoformat() if record.scheduled_end else None,
                'actual_start': record.actual_start.isoformat() if record.actual_start else None,
                'actual_end': record.actual_end.isoformat() if record.actual_end else None,
                'assigned_to': record.assigned_to,
                'assigned_staff_name': record.assigned_staff.full_name if record.assigned_staff else None,
                'priority': record.priority,
                'blocks_room': record.blocks_room,
                'estimated_cost': float(record.estimated_cost) if record.estimated_cost else None,
                'actual_cost': float(record.actual_cost) if record.actual_cost else None,
                'created_at': record.created_at.isoformat() if record.created_at else None
            })
        
        return {
            'status': 'success',
            'data': {
                'maintenance_records': result,
                'pagination': {
                    'total': total,
                    'page': page,
                    'limit': limit,
                    'total_pages': (total + limit - 1) // limit
                }
            }
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.post('/maintenance')
async def create_maintenance_record(
    maintenance_data: dict,
    current_user: User = Depends(authorize_roles('admin', 'staff')),
    db: Session = Depends(get_db)
):
    """Create a new maintenance record"""
    try:
        room = db.query(Room).filter(Room.id == maintenance_data.get('room_id')).first()
        if not room:
            raise HTTPException(status_code=404, detail='Room not found')
        
        scheduled_start = datetime.fromisoformat(maintenance_data['scheduled_start'].replace('Z', '+00:00'))
        scheduled_end = None
        if maintenance_data.get('scheduled_end'):
            scheduled_end = datetime.fromisoformat(maintenance_data['scheduled_end'].replace('Z', '+00:00'))
        
        block_start = None
        block_end = None
        if maintenance_data.get('block_start'):
            block_start = datetime.fromisoformat(maintenance_data['block_start'].replace('Z', '+00:00'))
        if maintenance_data.get('block_end'):
            block_end = datetime.fromisoformat(maintenance_data['block_end'].replace('Z', '+00:00'))
        
        maintenance = RoomMaintenance(
            room_id=maintenance_data['room_id'],
            maintenance_type=MaintenanceType(maintenance_data.get('maintenance_type', 'preventive')),
            status=MaintenanceStatus(maintenance_data.get('status', 'scheduled')),
            title=maintenance_data.get('title', 'Maintenance'),
            description=maintenance_data.get('description'),
            scheduled_start=scheduled_start,
            scheduled_end=scheduled_end,
            assigned_to=maintenance_data.get('assigned_to'),
            reported_by=current_user.id,
            estimated_cost=maintenance_data.get('estimated_cost'),
            blocks_room=maintenance_data.get('blocks_room', True),
            block_start=block_start,
            block_end=block_end,
            priority=maintenance_data.get('priority', 'medium'),
            notes=maintenance_data.get('notes')
        )
        
        # Update room status if blocking and maintenance is active
        if maintenance.blocks_room and maintenance.status in [MaintenanceStatus.scheduled, MaintenanceStatus.in_progress]:
            # Only update if room is currently available
            if room.status == RoomStatus.available:
                room.status = RoomStatus.maintenance
        
        db.add(maintenance)
        db.commit()
        db.refresh(maintenance)
        
        return {
            'status': 'success',
            'message': 'Maintenance record created successfully',
            'data': {'maintenance_id': maintenance.id}
        }
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))


@router.put('/maintenance/{maintenance_id}')
async def update_maintenance_record(
    maintenance_id: int,
    maintenance_data: dict,
    current_user: User = Depends(authorize_roles('admin', 'staff')),
    db: Session = Depends(get_db)
):
    """Update a maintenance record"""
    try:
        maintenance = db.query(RoomMaintenance).filter(RoomMaintenance.id == maintenance_id).first()
        if not maintenance:
            raise HTTPException(status_code=404, detail='Maintenance record not found')
        
        # Check if user is staff (not admin) - staff can only update their own assigned records
        role = db.query(Role).filter(Role.id == current_user.role_id).first()
        is_staff = role and role.name == 'staff'
        
        if is_staff:
            # Staff can only update records assigned to them
            if maintenance.assigned_to != current_user.id:
                raise HTTPException(status_code=403, detail='You can only update maintenance assigned to you')
            # Staff can only update status and completion fields
            allowed_fields = {'status', 'actual_start', 'actual_end', 'completion_notes', 'actual_cost'}
            if any(key not in allowed_fields for key in maintenance_data.keys()):
                raise HTTPException(status_code=403, detail='You can only update status and completion information')
        
        # Update fields
        if 'status' in maintenance_data:
            new_status = MaintenanceStatus(maintenance_data['status'])
            
            # Only the assigned user can mark the maintenance as completed
            if new_status == MaintenanceStatus.completed:
                if not maintenance.assigned_to:
                    raise HTTPException(status_code=400, detail='Maintenance must be assigned before it can be marked as completed')
                if maintenance.assigned_to != current_user.id:
                    raise HTTPException(status_code=403, detail='Only the assigned staff member can mark this maintenance as completed')
            
            old_status = maintenance.status
            maintenance.status = new_status
            
            # Update room status based on maintenance status
            if maintenance.status == MaintenanceStatus.completed and maintenance.blocks_room:
                # Check if room has other active maintenance
                other_maintenance = db.query(RoomMaintenance).filter(
                    and_(
                        RoomMaintenance.room_id == maintenance.room_id,
                        RoomMaintenance.id != maintenance_id,
                        RoomMaintenance.blocks_room == True,
                        RoomMaintenance.status.in_([MaintenanceStatus.scheduled, MaintenanceStatus.in_progress])
                    )
                ).first()
                
                if not other_maintenance:
                    # Check if room has active bookings
                    from datetime import datetime
                    active_booking = db.query(Booking).filter(
                        and_(
                            Booking.room_id == maintenance.room_id,
                            Booking.status.in_([BookingStatus.confirmed, BookingStatus.checked_in]),
                            Booking.check_in_date <= datetime.utcnow(),
                            Booking.check_out_date > datetime.utcnow()
                        )
                    ).first()
                    
                    if active_booking:
                        maintenance.room.status = RoomStatus.occupied
                    else:
                        maintenance.room.status = RoomStatus.available
            elif maintenance.status in [MaintenanceStatus.scheduled, MaintenanceStatus.in_progress] and maintenance.blocks_room:
                # Set room to maintenance if it's not occupied
                if maintenance.room.status == RoomStatus.available:
                    maintenance.room.status = RoomStatus.maintenance
        
        if 'actual_start' in maintenance_data:
            maintenance.actual_start = datetime.fromisoformat(maintenance_data['actual_start'].replace('Z', '+00:00'))
        if 'actual_end' in maintenance_data:
            maintenance.actual_end = datetime.fromisoformat(maintenance_data['actual_end'].replace('Z', '+00:00'))
        if 'completion_notes' in maintenance_data:
            maintenance.completion_notes = maintenance_data['completion_notes']
        if 'actual_cost' in maintenance_data:
            maintenance.actual_cost = maintenance_data['actual_cost']
        
        db.commit()
        
        return {
            'status': 'success',
            'message': 'Maintenance record updated successfully'
        }
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))


# ==================== Housekeeping Tasks ====================

@router.get('/housekeeping')
async def get_housekeeping_tasks(
    room_id: Optional[int] = Query(None),
    status: Optional[str] = Query(None),
    task_type: Optional[str] = Query(None),
    date: Optional[str] = Query(None),
    page: int = Query(1, ge=1),
    limit: int = Query(20, ge=1, le=100),
    current_user: User = Depends(authorize_roles('admin', 'staff')),
    db: Session = Depends(get_db)
):
    """Get housekeeping tasks with filtering"""
    try:
        # Check if user is staff (not admin) - staff should only see their assigned tasks
        role = db.query(Role).filter(Role.id == current_user.role_id).first()
        is_staff = role and role.name == 'staff'
        
        query = db.query(HousekeepingTask)
        
        # Filter by assigned_to for staff users
        if is_staff:
            query = query.filter(HousekeepingTask.assigned_to == current_user.id)
        
        if room_id:
            query = query.filter(HousekeepingTask.room_id == room_id)
        if status:
            query = query.filter(HousekeepingTask.status == HousekeepingStatus(status))
        if task_type:
            query = query.filter(HousekeepingTask.task_type == HousekeepingType(task_type))
        if date:
            date_obj = datetime.fromisoformat(date.replace('Z', '+00:00')).date()
            query = query.filter(func.date(HousekeepingTask.scheduled_time) == date_obj)
        
        total = query.count()
        query = query.order_by(HousekeepingTask.scheduled_time)
        
        offset = (page - 1) * limit
        tasks = query.offset(offset).limit(limit).all()
        
        result = []
        for task in tasks:
            result.append({
                'id': task.id,
                'room_id': task.room_id,
                'room_number': task.room.room_number if task.room else None,
                'booking_id': task.booking_id,
                'task_type': task.task_type.value,
                'status': task.status.value,
                'scheduled_time': task.scheduled_time.isoformat() if task.scheduled_time else None,
                'started_at': task.started_at.isoformat() if task.started_at else None,
                'completed_at': task.completed_at.isoformat() if task.completed_at else None,
                'assigned_to': task.assigned_to,
                'assigned_staff_name': task.assigned_staff.full_name if task.assigned_staff else None,
                'checklist_items': task.checklist_items,
                'notes': task.notes,
                'quality_score': task.quality_score,
                'estimated_duration_minutes': task.estimated_duration_minutes,
                'actual_duration_minutes': task.actual_duration_minutes
            })
        
        return {
            'status': 'success',
            'data': {
                'tasks': result,
                'pagination': {
                    'total': total,
                    'page': page,
                    'limit': limit,
                    'total_pages': (total + limit - 1) // limit
                }
            }
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.post('/housekeeping')
async def create_housekeeping_task(
    task_data: dict,
    current_user: User = Depends(authorize_roles('admin', 'staff')),
    db: Session = Depends(get_db)
):
    """Create a new housekeeping task"""
    try:
        room = db.query(Room).filter(Room.id == task_data.get('room_id')).first()
        if not room:
            raise HTTPException(status_code=404, detail='Room not found')
        
        scheduled_time = datetime.fromisoformat(task_data['scheduled_time'].replace('Z', '+00:00'))
        assigned_to = task_data.get('assigned_to')
        
        task = HousekeepingTask(
            room_id=task_data['room_id'],
            booking_id=task_data.get('booking_id'),
            task_type=HousekeepingType(task_data.get('task_type', 'vacant')),
            status=HousekeepingStatus(task_data.get('status', 'pending')),
            scheduled_time=scheduled_time,
            assigned_to=assigned_to,
            created_by=current_user.id,
            checklist_items=task_data.get('checklist_items', []),
            notes=task_data.get('notes'),
            estimated_duration_minutes=task_data.get('estimated_duration_minutes')
        )
        
        db.add(task)
        db.commit()
        db.refresh(task)
        
        # Send notification to assigned staff member if task is assigned
        if assigned_to:
            try:
                from ..routes.chat_routes import manager
                assigned_staff = db.query(User).filter(User.id == assigned_to).first()
                task_data_notification = {
                    'id': task.id,
                    'room_id': task.room_id,
                    'room_number': room.room_number,
                    'task_type': task.task_type.value,
                    'status': task.status.value,
                    'scheduled_time': task.scheduled_time.isoformat() if task.scheduled_time else None,
                    'assigned_to': task.assigned_to,
                    'created_at': task.created_at.isoformat() if task.created_at else None
                }
                notification_data = {
                    'type': 'housekeeping_task_assigned',
                    'data': task_data_notification
                }
                # Send notification to the specific staff member
                if assigned_to in manager.staff_connections:
                    try:
                        await manager.staff_connections[assigned_to].send_json(notification_data)
                    except Exception as e:
                        print(f'Error sending housekeeping task notification to staff {assigned_to}: {e}')
            except Exception as e:
                print(f'Error setting up housekeeping task notification: {e}')
        
        return {
            'status': 'success',
            'message': 'Housekeeping task created successfully',
            'data': {'task_id': task.id}
        }
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))


@router.put('/housekeeping/{task_id}')
async def update_housekeeping_task(
    task_id: int,
    task_data: dict,
    current_user: User = Depends(authorize_roles('admin', 'staff')),
    db: Session = Depends(get_db)
):
    """Update a housekeeping task"""
    try:
        task = db.query(HousekeepingTask).filter(HousekeepingTask.id == task_id).first()
        if not task:
            raise HTTPException(status_code=404, detail='Housekeeping task not found')
        
        # Check if user is staff (not admin) - staff can only update their own assigned tasks
        role = db.query(Role).filter(Role.id == current_user.role_id).first()
        is_staff = role and role.name == 'staff'
        
        if is_staff:
            # Staff can only update tasks assigned to them
            if task.assigned_to != current_user.id:
                raise HTTPException(status_code=403, detail='You can only update tasks assigned to you')
            # Staff cannot change assignment
            if 'assigned_to' in task_data and task_data.get('assigned_to') != task.assigned_to:
                raise HTTPException(status_code=403, detail='You cannot change task assignment')
        
        old_assigned_to = task.assigned_to
        assigned_to_changed = False
        
        if 'assigned_to' in task_data and not is_staff:
            new_assigned_to = task_data.get('assigned_to')
            if new_assigned_to != old_assigned_to:
                task.assigned_to = new_assigned_to
                assigned_to_changed = True
        
        if 'status' in task_data:
            new_status = HousekeepingStatus(task_data['status'])
            
            # Only the assigned user can mark the task as completed
            if new_status == HousekeepingStatus.completed:
                if not task.assigned_to:
                    raise HTTPException(status_code=400, detail='Task must be assigned before it can be marked as completed')
                if task.assigned_to != current_user.id:
                    raise HTTPException(status_code=403, detail='Only the assigned staff member can mark this task as completed')
            
            task.status = new_status
            
            if new_status == HousekeepingStatus.in_progress and not task.started_at:
                task.started_at = datetime.utcnow()
            elif new_status == HousekeepingStatus.completed and not task.completed_at:
                task.completed_at = datetime.utcnow()
                if task.started_at:
                    duration = (task.completed_at - task.started_at).total_seconds() / 60
                    task.actual_duration_minutes = int(duration)
        
        if 'checklist_items' in task_data:
            task.checklist_items = task_data['checklist_items']
        if 'notes' in task_data:
            task.notes = task_data['notes']
        if 'issues_found' in task_data:
            task.issues_found = task_data['issues_found']
        if 'quality_score' in task_data:
            task.quality_score = task_data['quality_score']
        if 'inspected_by' in task_data:
            task.inspected_by = task_data['inspected_by']
            task.inspected_at = datetime.utcnow()
        if 'inspection_notes' in task_data:
            task.inspection_notes = task_data['inspection_notes']
        
        db.commit()
        db.refresh(task)
        
        # Send notification if assignment changed
        if assigned_to_changed and task.assigned_to:
            try:
                from ..routes.chat_routes import manager
                room = db.query(Room).filter(Room.id == task.room_id).first()
                task_data_notification = {
                    'id': task.id,
                    'room_id': task.room_id,
                    'room_number': room.room_number if room else None,
                    'task_type': task.task_type.value,
                    'status': task.status.value,
                    'scheduled_time': task.scheduled_time.isoformat() if task.scheduled_time else None,
                    'assigned_to': task.assigned_to,
                    'updated_at': task.updated_at.isoformat() if task.updated_at else None
                }
                notification_data = {
                    'type': 'housekeeping_task_assigned',
                    'data': task_data_notification
                }
                # Send notification to the newly assigned staff member
                if task.assigned_to in manager.staff_connections:
                    try:
                        await manager.staff_connections[task.assigned_to].send_json(notification_data)
                    except Exception as e:
                        print(f'Error sending housekeeping task notification to staff {task.assigned_to}: {e}')
            except Exception as e:
                print(f'Error setting up housekeeping task notification: {e}')
        
        return {
            'status': 'success',
            'message': 'Housekeeping task updated successfully'
        }
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))


# ==================== Room Inspections ====================

@router.get('/inspections')
async def get_room_inspections(
    room_id: Optional[int] = Query(None),
    inspection_type: Optional[str] = Query(None),
    status: Optional[str] = Query(None),
    page: int = Query(1, ge=1),
    limit: int = Query(20, ge=1, le=100),
    current_user: User = Depends(authorize_roles('admin', 'staff')),
    db: Session = Depends(get_db)
):
    """Get room inspections with filtering"""
    try:
        # Check if user is staff (not admin) - staff should only see their assigned inspections
        role = db.query(Role).filter(Role.id == current_user.role_id).first()
        is_staff = role and role.name == 'staff'
        
        query = db.query(RoomInspection)
        
        # Filter by inspected_by for staff users
        if is_staff:
            query = query.filter(RoomInspection.inspected_by == current_user.id)
        
        if room_id:
            query = query.filter(RoomInspection.room_id == room_id)
        if inspection_type:
            query = query.filter(RoomInspection.inspection_type == InspectionType(inspection_type))
        if status:
            query = query.filter(RoomInspection.status == InspectionStatus(status))
        
        total = query.count()
        query = query.order_by(desc(RoomInspection.scheduled_at))
        
        offset = (page - 1) * limit
        inspections = query.offset(offset).limit(limit).all()
        
        result = []
        for inspection in inspections:
            result.append({
                'id': inspection.id,
                'room_id': inspection.room_id,
                'room_number': inspection.room.room_number if inspection.room else None,
                'booking_id': inspection.booking_id,
                'inspection_type': inspection.inspection_type.value,
                'status': inspection.status.value,
                'scheduled_at': inspection.scheduled_at.isoformat() if inspection.scheduled_at else None,
                'started_at': inspection.started_at.isoformat() if inspection.started_at else None,
                'completed_at': inspection.completed_at.isoformat() if inspection.completed_at else None,
                'inspected_by': inspection.inspected_by,
                'inspector_name': inspection.inspector.full_name if inspection.inspector else None,
                'checklist_items': inspection.checklist_items,
                'overall_score': float(inspection.overall_score) if inspection.overall_score else None,
                'overall_notes': inspection.overall_notes,
                'issues_found': inspection.issues_found,
                'requires_followup': inspection.requires_followup,
                'created_at': inspection.created_at.isoformat() if inspection.created_at else None
            })
        
        return {
            'status': 'success',
            'data': {
                'inspections': result,
                'pagination': {
                    'total': total,
                    'page': page,
                    'limit': limit,
                    'total_pages': (total + limit - 1) // limit
                }
            }
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.post('/inspections')
async def create_room_inspection(
    inspection_data: dict,
    current_user: User = Depends(authorize_roles('admin', 'staff')),
    db: Session = Depends(get_db)
):
    """Create a new room inspection"""
    try:
        room = db.query(Room).filter(Room.id == inspection_data.get('room_id')).first()
        if not room:
            raise HTTPException(status_code=404, detail='Room not found')
        
        scheduled_at = datetime.fromisoformat(inspection_data['scheduled_at'].replace('Z', '+00:00'))
        
        inspection = RoomInspection(
            room_id=inspection_data['room_id'],
            booking_id=inspection_data.get('booking_id'),
            inspection_type=InspectionType(inspection_data.get('inspection_type', 'routine')),
            status=InspectionStatus(inspection_data.get('status', 'pending')),
            scheduled_at=scheduled_at,
            inspected_by=inspection_data.get('inspected_by'),
            created_by=current_user.id,
            checklist_items=inspection_data.get('checklist_items', []),
            checklist_template_id=inspection_data.get('checklist_template_id')
        )
        
        db.add(inspection)
        db.commit()
        db.refresh(inspection)
        
        return {
            'status': 'success',
            'message': 'Room inspection created successfully',
            'data': {'inspection_id': inspection.id}
        }
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))


@router.put('/inspections/{inspection_id}')
async def update_room_inspection(
    inspection_id: int,
    inspection_data: dict,
    current_user: User = Depends(authorize_roles('admin', 'staff')),
    db: Session = Depends(get_db)
):
    """Update a room inspection"""
    try:
        inspection = db.query(RoomInspection).filter(RoomInspection.id == inspection_id).first()
        if not inspection:
            raise HTTPException(status_code=404, detail='Room inspection not found')
        
        # Check if user is staff (not admin) - staff can only update their own assigned inspections
        role = db.query(Role).filter(Role.id == current_user.role_id).first()
        is_staff = role and role.name == 'staff'
        
        if is_staff:
            # Staff can only update inspections assigned to them
            if inspection.inspected_by != current_user.id:
                raise HTTPException(status_code=403, detail='You can only update inspections assigned to you')
            # Staff can only update status and inspection results
            allowed_fields = {'status', 'checklist_items', 'overall_score', 'overall_notes', 'issues_found', 'requires_followup', 'followup_notes'}
            if any(key not in allowed_fields for key in inspection_data.keys()):
                raise HTTPException(status_code=403, detail='You can only update status and inspection results')
        
        if 'status' in inspection_data:
            new_status = InspectionStatus(inspection_data['status'])
            
            # Only the assigned user can mark the inspection as completed
            if new_status == InspectionStatus.completed:
                if not inspection.inspected_by:
                    raise HTTPException(status_code=400, detail='Inspection must be assigned before it can be marked as completed')
                if inspection.inspected_by != current_user.id:
                    raise HTTPException(status_code=403, detail='Only the assigned inspector can mark this inspection as completed')
            
            inspection.status = new_status
            
            if new_status == InspectionStatus.in_progress and not inspection.started_at:
                inspection.started_at = datetime.utcnow()
            elif new_status == InspectionStatus.completed and not inspection.completed_at:
                inspection.completed_at = datetime.utcnow()
        
        if 'checklist_items' in inspection_data:
            inspection.checklist_items = inspection_data['checklist_items']
        if 'overall_score' in inspection_data:
            inspection.overall_score = inspection_data['overall_score']
        if 'overall_notes' in inspection_data:
            inspection.overall_notes = inspection_data['overall_notes']
        if 'issues_found' in inspection_data:
            inspection.issues_found = inspection_data['issues_found']
        if 'photos' in inspection_data:
            inspection.photos = inspection_data['photos']
        if 'requires_followup' in inspection_data:
            inspection.requires_followup = inspection_data['requires_followup']
        if 'followup_notes' in inspection_data:
            inspection.followup_notes = inspection_data['followup_notes']
        if 'maintenance_request_id' in inspection_data:
            inspection.maintenance_request_id = inspection_data['maintenance_request_id']
        
        db.commit()
        
        return {
            'status': 'success',
            'message': 'Room inspection updated successfully'
        }
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))


# ==================== Room Status Board ====================

@router.get('/status-board')
async def get_room_status_board(
    floor: Optional[int] = Query(None),
    current_user: User = Depends(authorize_roles('admin', 'staff')),
    db: Session = Depends(get_db)
):
    """Get visual room status board with all rooms and their current status"""
    try:
        query = db.query(Room).options(joinedload(Room.room_type))
        if floor:
            query = query.filter(Room.floor == floor)
        
        rooms = query.order_by(Room.floor, Room.room_number).all()
        
        result = []
        for room in rooms:
            # Get current booking if any
            # Use load_only to avoid querying columns that don't exist in the database (rate_plan_id, group_booking_id)
            current_booking = db.query(Booking).options(
                joinedload(Booking.user),
                load_only(Booking.id, Booking.user_id, Booking.room_id, Booking.check_in_date, Booking.check_out_date, Booking.status)
            ).filter(
                and_(
                    Booking.room_id == room.id,
                    Booking.status.in_([BookingStatus.confirmed, BookingStatus.checked_in]),
                    Booking.check_in_date <= datetime.utcnow(),
                    Booking.check_out_date > datetime.utcnow()
                )
            ).first()
            
            # Get active maintenance
            active_maintenance = db.query(RoomMaintenance).filter(
                and_(
                    RoomMaintenance.room_id == room.id,
                    RoomMaintenance.blocks_room == True,
                    RoomMaintenance.status.in_([MaintenanceStatus.scheduled, MaintenanceStatus.in_progress])
                )
            ).first()
            
            # Get pending housekeeping tasks
            pending_housekeeping = db.query(HousekeepingTask).filter(
                and_(
                    HousekeepingTask.room_id == room.id,
                    HousekeepingTask.status == HousekeepingStatus.pending,
                    func.date(HousekeepingTask.scheduled_time) == datetime.utcnow().date()
                )
            ).count()
            
            result.append({
                'id': room.id,
                'room_number': room.room_number,
                'floor': room.floor,
                'status': room.status.value,
                'room_type': room.room_type.name if room.room_type else None,
                'current_booking': {
                    'id': current_booking.id,
                    'guest_name': current_booking.user.full_name if current_booking.user else 'Unknown',
                    'check_out': current_booking.check_out_date.isoformat()
                } if current_booking else None,
                'active_maintenance': {
                    'id': active_maintenance.id,
                    'title': active_maintenance.title,
                    'type': active_maintenance.maintenance_type.value
                } if active_maintenance else None,
                'pending_housekeeping_count': pending_housekeeping
            })
        
        return {
            'status': 'success',
            'data': {'rooms': result}
        }
    except Exception as e:
        import logging
        import traceback
        logger = logging.getLogger(__name__)
        logger.error(f'Error in get_room_status_board: {str(e)}')
        logger.error(f'Traceback: {traceback.format_exc()}')
        raise HTTPException(status_code=500, detail=str(e))

