from fastapi import APIRouter, Depends, HTTPException, status, Query
from sqlalchemy.orm import Session, load_only, joinedload
from sqlalchemy import func, and_
from typing import 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.booking import Booking, BookingStatus
from ..models.payment import Payment, PaymentStatus
from ..models.room import Room, RoomStatus
from ..models.service_usage import ServiceUsage
from ..models.service import Service
from ..utils.response_helpers import success_response
router = APIRouter(prefix='/reports', tags=['reports'])

@router.get('')
async def get_reports(from_date: Optional[str]=Query(None, alias='from'), to_date: Optional[str]=Query(None, alias='to'), type: Optional[str]=Query(None), current_user: User=Depends(authorize_roles('admin', 'staff', 'accountant')), db: Session=Depends(get_db)):
    try:
        start_date = None
        end_date = None
        if from_date:
            try:
                start_date = datetime.strptime(from_date, '%Y-%m-%d')
            except ValueError:
                start_date = datetime.fromisoformat(from_date.replace('Z', '+00:00'))
        if to_date:
            try:
                end_date = datetime.strptime(to_date, '%Y-%m-%d')
                end_date = end_date.replace(hour=23, minute=59, second=59)
            except ValueError:
                end_date = datetime.fromisoformat(to_date.replace('Z', '+00:00'))
        booking_query = db.query(Booking)
        payment_query = db.query(Payment).filter(Payment.payment_status == PaymentStatus.completed)
        if start_date:
            booking_query = booking_query.filter(Booking.created_at >= start_date)
            payment_query = payment_query.filter(Payment.payment_date >= start_date)
        if end_date:
            booking_query = booking_query.filter(Booking.created_at <= end_date)
            payment_query = payment_query.filter(Payment.payment_date <= end_date)
        # Use func.count() to avoid loading all columns (including non-existent rate_plan_id)
        total_bookings = booking_query.with_entities(func.count(Booking.id)).scalar() or 0
        total_revenue = payment_query.with_entities(func.sum(Payment.amount)).scalar() or 0.0
        total_customers = db.query(func.count(func.distinct(Booking.user_id))).scalar() or 0
        if start_date or end_date:
            customer_query = db.query(func.count(func.distinct(Booking.user_id)))
            if start_date:
                customer_query = customer_query.filter(Booking.created_at >= start_date)
            if end_date:
                customer_query = customer_query.filter(Booking.created_at <= end_date)
            total_customers = customer_query.scalar() or 0
        available_rooms = db.query(Room).filter(Room.status == RoomStatus.available).count()
        occupied_rooms = db.query(func.count(func.distinct(Booking.room_id))).filter(Booking.status.in_([BookingStatus.confirmed, BookingStatus.checked_in])).scalar() or 0
        revenue_by_date = []
        if start_date and end_date:
            daily_revenue_query = db.query(func.date(Payment.payment_date).label('date'), func.sum(Payment.amount).label('revenue'), func.count(func.distinct(Payment.booking_id)).label('bookings')).filter(Payment.payment_status == PaymentStatus.completed)
            if start_date:
                daily_revenue_query = daily_revenue_query.filter(Payment.payment_date >= start_date)
            if end_date:
                daily_revenue_query = daily_revenue_query.filter(Payment.payment_date <= end_date)
            daily_revenue_query = daily_revenue_query.group_by(func.date(Payment.payment_date)).order_by(func.date(Payment.payment_date))
            daily_data = daily_revenue_query.all()
            revenue_by_date = [{'date': str(date), 'revenue': float(revenue or 0), 'bookings': int(bookings or 0)} for date, revenue, bookings in daily_data]
        bookings_by_status = {}
        for status in BookingStatus:
            # Use func.count() to avoid loading all columns (including non-existent rate_plan_id)
            count = booking_query.filter(Booking.status == status).with_entities(func.count(Booking.id)).scalar() or 0
            status_name = status.value if hasattr(status, 'value') else str(status)
            bookings_by_status[status_name] = count
        top_rooms_query = db.query(Room.id, Room.room_number, func.count(Booking.id).label('bookings'), func.sum(Payment.amount).label('revenue')).join(Booking, Room.id == Booking.room_id).join(Payment, Booking.id == Payment.booking_id).filter(Payment.payment_status == PaymentStatus.completed)
        if start_date:
            top_rooms_query = top_rooms_query.filter(Booking.created_at >= start_date)
        if end_date:
            top_rooms_query = top_rooms_query.filter(Booking.created_at <= end_date)
        top_rooms_data = top_rooms_query.group_by(Room.id, Room.room_number).order_by(func.sum(Payment.amount).desc()).limit(10).all()
        top_rooms = [{'room_id': room_id, 'room_number': room_number, 'bookings': int(bookings or 0), 'revenue': float(revenue or 0)} for room_id, room_number, bookings, revenue in top_rooms_data]
        service_usage_query = db.query(Service.id, Service.name, func.count(ServiceUsage.id).label('usage_count'), func.sum(ServiceUsage.total_price).label('total_revenue')).join(ServiceUsage, Service.id == ServiceUsage.service_id)
        if start_date:
            service_usage_query = service_usage_query.filter(ServiceUsage.usage_date >= start_date)
        if end_date:
            service_usage_query = service_usage_query.filter(ServiceUsage.usage_date <= end_date)
        service_usage_data = service_usage_query.group_by(Service.id, Service.name).order_by(func.sum(ServiceUsage.total_price).desc()).limit(10).all()
        service_usage = [{'service_id': service_id, 'service_name': service_name, 'usage_count': int(usage_count or 0), 'total_revenue': float(total_revenue or 0)} for service_id, service_name, usage_count, total_revenue in service_usage_data]
        return success_response(data={'total_bookings': total_bookings, 'total_revenue': float(total_revenue), 'total_customers': int(total_customers), 'available_rooms': available_rooms, 'occupied_rooms': occupied_rooms, 'revenue_by_date': revenue_by_date if revenue_by_date else None, 'bookings_by_status': bookings_by_status, 'top_rooms': top_rooms if top_rooms else None, 'service_usage': service_usage if service_usage else None})
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@router.get('/dashboard')
async def get_dashboard_stats(current_user: User=Depends(authorize_roles('admin', 'staff', 'accountant')), db: Session=Depends(get_db)):
    try:
        # Use func.count() to avoid loading all columns (including non-existent rate_plan_id)
        total_bookings = db.query(Booking).with_entities(func.count(Booking.id)).scalar() or 0
        active_bookings = db.query(Booking).filter(Booking.status.in_([BookingStatus.pending, BookingStatus.confirmed, BookingStatus.checked_in])).with_entities(func.count(Booking.id)).scalar() or 0
        total_revenue = db.query(func.sum(Payment.amount)).filter(Payment.payment_status == PaymentStatus.completed).scalar() or 0.0
        today_start = datetime.utcnow().replace(hour=0, minute=0, second=0, microsecond=0)
        today_revenue = db.query(func.sum(Payment.amount)).filter(and_(Payment.payment_status == PaymentStatus.completed, Payment.payment_date >= today_start)).scalar() or 0.0
        total_rooms = db.query(Room).count()
        available_rooms = db.query(Room).filter(Room.status == RoomStatus.available).count()
        week_ago = datetime.utcnow() - timedelta(days=7)
        recent_bookings = db.query(Booking).filter(Booking.created_at >= week_ago).with_entities(func.count(Booking.id)).scalar() or 0
        pending_payments = db.query(Payment).filter(Payment.payment_status == PaymentStatus.pending).count()
        return success_response(data={'total_bookings': total_bookings, 'active_bookings': active_bookings, 'total_revenue': float(total_revenue), 'today_revenue': float(today_revenue), 'total_rooms': total_rooms, 'available_rooms': available_rooms, 'recent_bookings': recent_bookings, 'pending_payments': pending_payments})
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@router.get('/customer/dashboard')
async def get_customer_dashboard_stats(current_user: User=Depends(get_current_user), db: Session=Depends(get_db)):
    try:
        from datetime import datetime, timedelta
        # Use func.count() to avoid loading all columns (including non-existent rate_plan_id)
        total_bookings = db.query(Booking).filter(Booking.user_id == current_user.id).with_entities(func.count(Booking.id)).scalar() or 0
        user_bookings = db.query(Booking.id).filter(Booking.user_id == current_user.id).subquery()
        total_spending = db.query(func.sum(Payment.amount)).filter(and_(Payment.booking_id.in_(db.query(user_bookings.c.id)), Payment.payment_status == PaymentStatus.completed)).scalar() or 0.0
        now = datetime.utcnow()
        currently_staying = db.query(Booking).filter(and_(Booking.user_id == current_user.id, Booking.status == BookingStatus.checked_in, Booking.check_in_date <= now, Booking.check_out_date >= now)).with_entities(func.count(Booking.id)).scalar() or 0
        # Use load_only to exclude non-existent columns and eagerly load room relationships
        upcoming_bookings_query = db.query(Booking).options(
            load_only(Booking.id, Booking.booking_number, Booking.check_in_date, Booking.check_out_date, Booking.status, Booking.total_price, Booking.user_id, Booking.room_id, Booking.created_at),
            joinedload(Booking.room).joinedload(Room.room_type)
        ).filter(and_(Booking.user_id == current_user.id, Booking.status.in_([BookingStatus.confirmed, BookingStatus.pending]), Booking.check_in_date > now)).order_by(Booking.check_in_date.asc()).limit(5).all()
        upcoming_bookings = []
        for booking in upcoming_bookings_query:
            booking_dict = {'id': booking.id, 'booking_number': booking.booking_number, 'check_in_date': booking.check_in_date.isoformat() if booking.check_in_date else None, 'check_out_date': booking.check_out_date.isoformat() if booking.check_out_date else None, 'status': booking.status.value if isinstance(booking.status, BookingStatus) else booking.status, 'total_price': float(booking.total_price) if booking.total_price else 0.0}
            if booking.room:
                booking_dict['room'] = {'id': booking.room.id, 'room_number': booking.room.room_number, 'room_type': {'name': booking.room.room_type.name if booking.room.room_type else None}}
            upcoming_bookings.append(booking_dict)
        # Use load_only to exclude non-existent columns and eagerly load room relationships
        recent_bookings_query = db.query(Booking).options(
            load_only(Booking.id, Booking.booking_number, Booking.status, Booking.user_id, Booking.room_id, Booking.created_at),
            joinedload(Booking.room)
        ).filter(Booking.user_id == current_user.id).order_by(Booking.created_at.desc()).limit(5).all()
        recent_activity = []
        for booking in recent_bookings_query:
            activity_type = None
            if booking.status == BookingStatus.checked_out:
                activity_type = 'Check-out'
            elif booking.status == BookingStatus.checked_in:
                activity_type = 'Check-in'
            elif booking.status == BookingStatus.confirmed:
                activity_type = 'Booking Confirmed'
            elif booking.status == BookingStatus.pending:
                activity_type = 'Booking'
            else:
                activity_type = 'Booking'
            activity_dict = {'action': activity_type, 'booking_id': booking.id, 'booking_number': booking.booking_number, 'created_at': booking.created_at.isoformat() if booking.created_at else None}
            if booking.room:
                activity_dict['room'] = {'room_number': booking.room.room_number}
            recent_activity.append(activity_dict)
        last_month_start = (now - timedelta(days=30)).replace(day=1, hour=0, minute=0, second=0)
        last_month_end = now.replace(day=1, hour=0, minute=0, second=0) - timedelta(seconds=1)
        # Use func.count() to avoid loading all columns (including non-existent rate_plan_id)
        last_month_bookings = db.query(Booking).filter(and_(Booking.user_id == current_user.id, Booking.created_at >= last_month_start, Booking.created_at <= last_month_end)).with_entities(func.count(Booking.id)).scalar() or 0
        this_month_bookings = db.query(Booking).filter(and_(Booking.user_id == current_user.id, Booking.created_at >= now.replace(day=1, hour=0, minute=0, second=0), Booking.created_at <= now)).with_entities(func.count(Booking.id)).scalar() or 0
        booking_change_percentage = 0
        if last_month_bookings > 0:
            booking_change_percentage = (this_month_bookings - last_month_bookings) / last_month_bookings * 100
        last_month_spending = db.query(func.sum(Payment.amount)).filter(and_(Payment.booking_id.in_(db.query(user_bookings.c.id)), Payment.payment_status == PaymentStatus.completed, Payment.payment_date >= last_month_start, Payment.payment_date <= last_month_end)).scalar() or 0.0
        this_month_spending = db.query(func.sum(Payment.amount)).filter(and_(Payment.booking_id.in_(db.query(user_bookings.c.id)), Payment.payment_status == PaymentStatus.completed, Payment.payment_date >= now.replace(day=1, hour=0, minute=0, second=0), Payment.payment_date <= now)).scalar() or 0.0
        spending_change_percentage = 0
        if last_month_spending > 0:
            spending_change_percentage = (this_month_spending - last_month_spending) / last_month_spending * 100
        return success_response(data={'total_bookings': total_bookings, 'total_spending': float(total_spending), 'currently_staying': currently_staying, 'upcoming_bookings': upcoming_bookings, 'recent_activity': recent_activity, 'booking_change_percentage': round(booking_change_percentage, 1), 'spending_change_percentage': round(spending_change_percentage, 1)})
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@router.get('/revenue')
async def get_revenue_report(start_date: Optional[str]=Query(None), end_date: Optional[str]=Query(None), current_user: User=Depends(authorize_roles('admin', 'staff', 'accountant')), db: Session=Depends(get_db)):
    try:
        query = db.query(Payment).filter(Payment.payment_status == PaymentStatus.completed)
        if start_date:
            start = datetime.fromisoformat(start_date.replace('Z', '+00:00'))
            query = query.filter(Payment.payment_date >= start)
        if end_date:
            end = datetime.fromisoformat(end_date.replace('Z', '+00:00'))
            query = query.filter(Payment.payment_date <= end)
        total_revenue = db.query(func.sum(Payment.amount)).filter(Payment.payment_status == PaymentStatus.completed).scalar() or 0.0
        revenue_by_method = db.query(Payment.payment_method, func.sum(Payment.amount).label('total')).filter(Payment.payment_status == PaymentStatus.completed).group_by(Payment.payment_method).all()
        method_breakdown = {}
        for method, total in revenue_by_method:
            method_name = method.value if hasattr(method, 'value') else str(method)
            method_breakdown[method_name] = float(total or 0)
        daily_revenue = db.query(func.date(Payment.payment_date).label('date'), func.sum(Payment.amount).label('total')).filter(Payment.payment_status == PaymentStatus.completed).group_by(func.date(Payment.payment_date)).order_by(func.date(Payment.payment_date).desc()).limit(30).all()
        daily_breakdown = [{'date': date.isoformat() if isinstance(date, datetime) else str(date), 'revenue': float(total or 0)} for date, total in daily_revenue]
        return success_response(data={'total_revenue': float(total_revenue), 'revenue_by_method': method_breakdown, 'daily_breakdown': daily_breakdown})
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))