from sqlalchemy.orm import Session, load_only
from sqlalchemy import func, and_, or_, case, extract, distinct
from typing import Optional, Dict, List, Any
from datetime import datetime, timedelta, date
from ..models.booking import Booking, BookingStatus
from ..models.payment import Payment, PaymentStatus, PaymentMethod
from ..models.room import Room, RoomStatus
from ..models.room_type import RoomType
from ..models.user import User
from ..models.service_usage import ServiceUsage
from ..models.service import Service
from ..models.review import Review, ReviewStatus
from ..models.invoice import Invoice
import logging

logger = logging.getLogger(__name__)

class AnalyticsService:
    """Advanced Analytics & Business Intelligence Service"""
    
    @staticmethod
    def parse_date_range(start_date: Optional[str], end_date: Optional[str]) -> tuple[Optional[datetime], Optional[datetime]]:
        """Parse date range strings to datetime objects"""
        start = None
        end = None
        
        if start_date:
            try:
                start = datetime.strptime(start_date, '%Y-%m-%d')
            except ValueError:
                start = datetime.fromisoformat(start_date.replace('Z', '+00:00'))
        
        if end_date:
            try:
                end = datetime.strptime(end_date, '%Y-%m-%d')
                end = end.replace(hour=23, minute=59, second=59)
            except ValueError:
                end = datetime.fromisoformat(end_date.replace('Z', '+00:00'))
        
        return start, end
    
    # ==================== REVENUE ANALYTICS ====================
    
    @staticmethod
    def get_revpar(db: Session, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> Dict[str, Any]:
        """Calculate RevPAR (Revenue Per Available Room)"""
        total_rooms = db.query(Room).count()
        
        if not start_date or not end_date:
            # Default to last 30 days
            end_date = datetime.utcnow()
            start_date = end_date - timedelta(days=30)
        
        days = (end_date - start_date).days + 1
        available_room_nights = total_rooms * days
        
        # Calculate total revenue from completed payments
        revenue_query = db.query(func.sum(Payment.amount)).filter(
            Payment.payment_status == PaymentStatus.completed
        )
        if start_date:
            revenue_query = revenue_query.filter(Payment.payment_date >= start_date)
        if end_date:
            revenue_query = revenue_query.filter(Payment.payment_date <= end_date)
        
        total_revenue = revenue_query.scalar() or 0.0
        
        revpar = float(total_revenue) / available_room_nights if available_room_nights > 0 else 0.0
        
        return {
            'revpar': round(revpar, 2),
            'total_revenue': float(total_revenue),
            'available_room_nights': available_room_nights,
            'period_days': days,
            'total_rooms': total_rooms
        }
    
    @staticmethod
    def get_adr(db: Session, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> Dict[str, Any]:
        """Calculate ADR (Average Daily Rate)"""
        try:
            # Use load_only to exclude non-existent columns (rate_plan_id, group_booking_id)
            query = db.query(Booking).options(
                load_only(Booking.id, Booking.check_in_date, Booking.check_out_date, Booking.total_price, Booking.status)
            ).filter(
                Booking.status.in_([BookingStatus.confirmed, BookingStatus.checked_in, BookingStatus.checked_out])
            )
            
            if start_date:
                query = query.filter(Booking.check_in_date >= start_date)
            if end_date:
                query = query.filter(Booking.check_in_date <= end_date)
            
            bookings = query.all()
            
            if not bookings:
                return {
                    'adr': 0.0,
                    'period': {
                        'start': start_date.isoformat() if start_date else None,
                        'end': end_date.isoformat() if end_date else None
                    }
                }
            
            total_adr = 0.0
            count = 0
            
            for booking in bookings:
                try:
                    if booking.check_in_date and booking.check_out_date and booking.total_price:
                        nights = (booking.check_out_date - booking.check_in_date).days + 1
                        if nights > 0:
                            daily_rate = float(booking.total_price) / nights
                            total_adr += daily_rate
                            count += 1
                except Exception as e:
                    logger.warning(f"Error processing booking {booking.id} for ADR: {str(e)}")
                    continue
            
            adr = total_adr / count if count > 0 else 0.0
            
            return {
                'adr': round(adr, 2),
                'period': {
                    'start': start_date.isoformat() if start_date else None,
                    'end': end_date.isoformat() if end_date else None
                }
            }
        except Exception as e:
            logger.error(f"Error calculating ADR: {str(e)}")
            raise
    
    @staticmethod
    def get_occupancy_rate(db: Session, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> Dict[str, Any]:
        """Calculate Occupancy Rate"""
        try:
            total_rooms = db.query(Room).count()
            
            if not start_date or not end_date:
                end_date = datetime.utcnow()
                start_date = end_date - timedelta(days=30)
            
            if start_date > end_date:
                # Swap if dates are reversed
                start_date, end_date = end_date, start_date
            
            days = (end_date - start_date).days + 1
            if days <= 0:
                days = 1
            
            available_room_nights = total_rooms * days if total_rooms > 0 else 0
            
            # Calculate occupied room nights by fetching bookings and calculating in Python
            # Use load_only to exclude non-existent columns (rate_plan_id, group_booking_id)
            bookings_query = db.query(Booking).options(
                load_only(Booking.id, Booking.check_in_date, Booking.check_out_date, Booking.status)
            ).filter(
                Booking.status.in_([BookingStatus.confirmed, BookingStatus.checked_in, BookingStatus.checked_out])
            )
            
            # Filter bookings that overlap with the date range
            bookings = bookings_query.filter(
                and_(Booking.check_in_date <= end_date, Booking.check_out_date >= start_date)
            ).all()
            
            occupied_room_nights = 0
            
            for booking in bookings:
                try:
                    if booking.check_in_date and booking.check_out_date:
                        # Calculate overlap with date range
                        overlap_start = max(booking.check_in_date, start_date)
                        overlap_end = min(booking.check_out_date, end_date)
                        
                        if overlap_start <= overlap_end:
                            nights = (overlap_end - overlap_start).days + 1
                            if nights > 0:
                                occupied_room_nights += nights
                except Exception as e:
                    logger.warning(f"Error processing booking {booking.id} for occupancy: {str(e)}")
                    continue
            
            occupancy_rate = (occupied_room_nights / available_room_nights * 100) if available_room_nights > 0 else 0.0
            
            return {
                'occupancy_rate': round(occupancy_rate, 2),
                'occupied_room_nights': int(occupied_room_nights),
                'available_room_nights': available_room_nights,
                'period_days': days
            }
        except Exception as e:
            logger.error(f"Error calculating occupancy rate: {str(e)}")
            raise
    
    @staticmethod
    def get_revenue_forecast(db: Session, forecast_days: int = 30) -> Dict[str, Any]:
        """Revenue forecasting based on historical data"""
        end_date = datetime.utcnow()
        start_date = end_date - timedelta(days=90)  # Use last 90 days for forecast
        
        # Get daily revenue for last 90 days
        daily_revenue = db.query(
            func.date(Payment.payment_date).label('date'),
            func.sum(Payment.amount).label('revenue')
        ).filter(
            Payment.payment_status == PaymentStatus.completed,
            Payment.payment_date >= start_date
        ).group_by(func.date(Payment.payment_date)).order_by(func.date(Payment.payment_date)).all()
        
        if not daily_revenue:
            return {'forecast': [], 'average_daily_revenue': 0, 'forecast_period': forecast_days}
        
        # Calculate average daily revenue
        total_revenue = sum(float(rev) for _, rev in daily_revenue)
        avg_daily_revenue = total_revenue / len(daily_revenue) if daily_revenue else 0
        
        # Simple forecast: use average daily revenue
        forecast = []
        for i in range(1, forecast_days + 1):
            forecast_date = (end_date + timedelta(days=i)).date()
            forecast.append({
                'date': forecast_date.isoformat(),
                'forecasted_revenue': round(avg_daily_revenue, 2),
                'confidence': 'medium'
            })
        
        return {
            'forecast': forecast,
            'average_daily_revenue': round(avg_daily_revenue, 2),
            'forecast_period': forecast_days,
            'based_on_days': len(daily_revenue)
        }
    
    @staticmethod
    def get_market_penetration(db: Session, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> Dict[str, Any]:
        """Market penetration analysis"""
        if not start_date or not end_date:
            end_date = datetime.utcnow()
            start_date = end_date - timedelta(days=30)
        
        # Get bookings by source/channel (if available)
        # For now, we'll analyze by booking creation method
        # Use func.count() to avoid loading all columns (including non-existent rate_plan_id)
        total_bookings = db.query(Booking).filter(
            Booking.created_at >= start_date,
            Booking.created_at <= end_date
        ).with_entities(func.count(Booking.id)).scalar() or 0
        
        # Analyze by room type popularity
        room_type_bookings = db.query(
            RoomType.name,
            func.count(Booking.id).label('bookings'),
            func.sum(Payment.amount).label('revenue')
        ).join(Room, RoomType.id == Room.room_type_id).join(
            Booking, Room.id == Booking.room_id
        ).join(
            Payment, Booking.id == Payment.booking_id
        ).filter(
            Payment.payment_status == PaymentStatus.completed,
            Booking.created_at >= start_date,
            Booking.created_at <= end_date
        ).group_by(RoomType.name).all()
        
        penetration_by_type = [
            {
                'room_type': name,
                'bookings': int(count),
                'revenue': float(revenue or 0),
                'market_share': round((count / total_bookings * 100) if total_bookings > 0 else 0, 2)
            }
            for name, count, revenue in room_type_bookings
        ]
        
        return {
            'total_bookings': total_bookings,
            'penetration_by_room_type': penetration_by_type,
            'period': {
                'start': start_date.isoformat(),
                'end': end_date.isoformat()
            }
        }
    
    # ==================== OPERATIONAL ANALYTICS ====================
    
    @staticmethod
    def get_staff_performance(db: Session, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> Dict[str, Any]:
        """Staff performance metrics"""
        if not start_date or not end_date:
            end_date = datetime.utcnow()
            start_date = end_date - timedelta(days=30)
        
        # Get staff users
        from ..models.role import Role
        staff_users = db.query(User).join(Role, User.role_id == Role.id).filter(
            or_(Role.name == 'staff', Role.name == 'admin')
        ).all()
        
        performance_metrics = []
        for staff in staff_users:
            # Count bookings handled (if we track this)
            # For now, we'll use check-ins/check-outs as proxy
            checkins = db.query(func.count(Booking.id)).filter(
                Booking.status == BookingStatus.checked_in,
                Booking.created_at >= start_date,
                Booking.created_at <= end_date
            ).scalar() or 0
            
            performance_metrics.append({
                'staff_id': staff.id,
                'staff_name': staff.full_name,
                'email': staff.email,
                'check_ins_handled': checkins,
                'performance_score': checkins  # Simple metric
            })
        
        return {
            'staff_performance': performance_metrics,
            'period': {
                'start': start_date.isoformat(),
                'end': end_date.isoformat()
            }
        }
    
    @staticmethod
    def get_service_usage_analytics(db: Session, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> Dict[str, Any]:
        """Service usage analytics"""
        query = db.query(
            Service.id,
            Service.name,
            Service.category,
            func.count(ServiceUsage.id).label('usage_count'),
            func.sum(ServiceUsage.total_price).label('total_revenue'),
            func.avg(ServiceUsage.unit_price).label('avg_price')
        ).join(ServiceUsage, Service.id == ServiceUsage.service_id)
        
        if start_date:
            query = query.filter(ServiceUsage.usage_date >= start_date)
        if end_date:
            query = query.filter(ServiceUsage.usage_date <= end_date)
        
        service_data = query.group_by(Service.id, Service.name, Service.category).all()
        
        services = [
            {
                'service_id': sid,
                'service_name': name,
                'category': category,
                'usage_count': int(count),
                'total_revenue': float(revenue or 0),
                'average_price': float(avg_price or 0)
            }
            for sid, name, category, count, revenue, avg_price in service_data
        ]
        
        return {
            'services': services,
            'total_services': len(services),
            'total_usage': sum(s['usage_count'] for s in services),
            'total_revenue': sum(s['total_revenue'] for s in services)
        }
    
    @staticmethod
    def get_operational_efficiency(db: Session, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> Dict[str, Any]:
        """Operational efficiency metrics"""
        if not start_date or not end_date:
            end_date = datetime.utcnow()
            start_date = end_date - timedelta(days=30)
        
        # Booking conversion rate
        # Use func.count() to avoid loading all columns (including non-existent rate_plan_id)
        total_bookings = db.query(Booking).filter(
            Booking.created_at >= start_date,
            Booking.created_at <= end_date
        ).with_entities(func.count(Booking.id)).scalar() or 0
        
        confirmed_bookings = db.query(Booking).filter(
            Booking.status.in_([BookingStatus.confirmed, BookingStatus.checked_in, BookingStatus.checked_out]),
            Booking.created_at >= start_date,
            Booking.created_at <= end_date
        ).with_entities(func.count(Booking.id)).scalar() or 0
        
        conversion_rate = (confirmed_bookings / total_bookings * 100) if total_bookings > 0 else 0
        
        # Average booking value
        avg_booking_value = db.query(func.avg(Booking.total_price)).filter(
            Booking.status.in_([BookingStatus.confirmed, BookingStatus.checked_in, BookingStatus.checked_out]),
            Booking.created_at >= start_date,
            Booking.created_at <= end_date
        ).scalar() or 0.0
        
        # Cancellation rate
        cancelled_bookings = db.query(Booking).filter(
            Booking.status == BookingStatus.cancelled,
            Booking.created_at >= start_date,
            Booking.created_at <= end_date
        ).with_entities(func.count(Booking.id)).scalar() or 0
        
        cancellation_rate = (cancelled_bookings / total_bookings * 100) if total_bookings > 0 else 0
        
        return {
            'conversion_rate': round(conversion_rate, 2),
            'average_booking_value': round(float(avg_booking_value), 2),
            'cancellation_rate': round(cancellation_rate, 2),
            'total_bookings': total_bookings,
            'confirmed_bookings': confirmed_bookings,
            'cancelled_bookings': cancelled_bookings
        }
    
    # ==================== GUEST ANALYTICS ====================
    
    @staticmethod
    def get_guest_lifetime_value(db: Session, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> Dict[str, Any]:
        """Guest Lifetime Value (LTV) analysis"""
        query = db.query(
            User.id,
            User.full_name,
            User.email,
            func.count(distinct(Booking.id)).label('total_bookings'),
            func.sum(Payment.amount).label('total_spent')
        ).join(Booking, User.id == Booking.user_id).join(
            Payment, Booking.id == Payment.booking_id
        ).filter(
            Payment.payment_status == PaymentStatus.completed
        )
        
        if start_date:
            query = query.filter(Payment.payment_date >= start_date)
        if end_date:
            query = query.filter(Payment.payment_date <= end_date)
        
        guest_data = query.group_by(User.id, User.full_name, User.email).order_by(
            func.sum(Payment.amount).desc()
        ).limit(100).all()
        
        guests = [
            {
                'user_id': uid,
                'name': name,
                'email': email,
                'total_bookings': int(bookings),
                'lifetime_value': float(spent or 0),
                'average_booking_value': round(float(spent or 0) / int(bookings) if bookings > 0 else 0, 2)
            }
            for uid, name, email, bookings, spent in guest_data
        ]
        
        avg_ltv = sum(g['lifetime_value'] for g in guests) / len(guests) if guests else 0
        
        return {
            'guests': guests,
            'average_ltv': round(avg_ltv, 2),
            'total_guests_analyzed': len(guests)
        }
    
    @staticmethod
    def get_customer_acquisition_cost(db: Session, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> Dict[str, Any]:
        """Customer Acquisition Cost (CAC) analysis"""
        if not start_date or not end_date:
            end_date = datetime.utcnow()
            start_date = end_date - timedelta(days=30)
        
        # Get new customers (first booking in period)
        new_customers = db.query(distinct(Booking.user_id)).filter(
            Booking.created_at >= start_date,
            Booking.created_at <= end_date
        ).subquery()
        
        # Get first booking date for each user
        first_booking_dates = db.query(
            Booking.user_id,
            func.min(Booking.created_at).label('first_booking')
        ).group_by(Booking.user_id).subquery()
        
        new_customers_in_period = db.query(Booking.user_id).join(
            first_booking_dates,
            Booking.user_id == first_booking_dates.c.user_id
        ).filter(
            first_booking_dates.c.first_booking >= start_date,
            first_booking_dates.c.first_booking <= end_date
        ).distinct().count()
        
        # For CAC calculation, we'd need marketing spend data
        # For now, we'll return the number of new customers
        # In a real system, CAC = Marketing Spend / New Customers
        
        return {
            'new_customers': new_customers_in_period,
            'period': {
                'start': start_date.isoformat(),
                'end': end_date.isoformat()
            },
            'note': 'CAC calculation requires marketing spend data'
        }
    
    @staticmethod
    def get_repeat_guest_rate(db: Session, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> Dict[str, Any]:
        """Repeat guest rate analysis"""
        if not start_date or not end_date:
            end_date = datetime.utcnow()
            start_date = end_date - timedelta(days=90)
        
        # Total unique guests
        total_guests = db.query(distinct(Booking.user_id)).filter(
            Booking.created_at >= start_date,
            Booking.created_at <= end_date
        ).count()
        
        # Guests with multiple bookings
        repeat_guests = db.query(Booking.user_id).filter(
            Booking.created_at >= start_date,
            Booking.created_at <= end_date
        ).group_by(Booking.user_id).having(
            func.count(Booking.id) > 1
        ).count()
        
        repeat_rate = (repeat_guests / total_guests * 100) if total_guests > 0 else 0
        
        return {
            'repeat_guest_rate': round(repeat_rate, 2),
            'total_guests': total_guests,
            'repeat_guests': repeat_guests,
            'one_time_guests': total_guests - repeat_guests
        }
    
    @staticmethod
    def get_guest_satisfaction_trends(db: Session, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> Dict[str, Any]:
        """Guest satisfaction trends from reviews"""
        query = db.query(
            func.date(Review.created_at).label('date'),
            func.avg(Review.rating).label('avg_rating'),
            func.count(Review.id).label('review_count')
        ).filter(Review.status == ReviewStatus.approved)
        
        if start_date:
            query = query.filter(Review.created_at >= start_date)
        if end_date:
            query = query.filter(Review.created_at <= end_date)
        
        trends = query.group_by(func.date(Review.created_at)).order_by(
            func.date(Review.created_at)
        ).all()
        
        satisfaction_data = [
            {
                'date': str(date),
                'average_rating': round(float(avg_rating or 0), 2),
                'review_count': int(count)
            }
            for date, avg_rating, count in trends
        ]
        
        overall_avg = sum(d['average_rating'] for d in satisfaction_data) / len(satisfaction_data) if satisfaction_data else 0
        
        return {
            'trends': satisfaction_data,
            'overall_average_rating': round(overall_avg, 2),
            'total_reviews': sum(d['review_count'] for d in satisfaction_data)
        }
    
    # ==================== FINANCIAL ANALYTICS ====================
    
    @staticmethod
    def get_profit_loss(db: Session, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> Dict[str, Any]:
        """Profit & Loss report"""
        if not start_date or not end_date:
            end_date = datetime.utcnow()
            start_date = end_date - timedelta(days=30)
        
        # Revenue
        total_revenue = db.query(func.sum(Payment.amount)).filter(
            Payment.payment_status == PaymentStatus.completed,
            Payment.payment_date >= start_date,
            Payment.payment_date <= end_date
        ).scalar() or 0.0
        
        # Refunds
        refunds = db.query(func.sum(Payment.amount)).filter(
            Payment.payment_status == PaymentStatus.refunded,
            Payment.payment_date >= start_date,
            Payment.payment_date <= end_date
        ).scalar() or 0.0
        
        # Service costs (if we track service costs)
        # For now, we'll use service revenue as proxy for costs
        service_revenue = db.query(func.sum(ServiceUsage.total_price)).filter(
            ServiceUsage.usage_date >= start_date,
            ServiceUsage.usage_date <= end_date
        ).scalar() or 0.0
        
        # Net revenue
        net_revenue = float(total_revenue) - float(refunds)
        
        # Gross profit (simplified - would need actual cost data)
        gross_profit = net_revenue  # Assuming no direct costs tracked
        
        return {
            'total_revenue': round(float(total_revenue), 2),
            'refunds': round(float(refunds), 2),
            'net_revenue': round(net_revenue, 2),
            'gross_profit': round(gross_profit, 2),
            'period': {
                'start': start_date.isoformat(),
                'end': end_date.isoformat()
            }
        }
    
    @staticmethod
    def get_payment_method_analytics(db: Session, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> Dict[str, Any]:
        """Payment method analytics"""
        query = db.query(
            Payment.payment_method,
            func.count(Payment.id).label('transaction_count'),
            func.sum(Payment.amount).label('total_amount'),
            func.avg(Payment.amount).label('avg_amount')
        ).filter(Payment.payment_status == PaymentStatus.completed)
        
        if start_date:
            query = query.filter(Payment.payment_date >= start_date)
        if end_date:
            query = query.filter(Payment.payment_date <= end_date)
        
        method_data = query.group_by(Payment.payment_method).all()
        
        methods = [
            {
                'payment_method': method.value if hasattr(method, 'value') else str(method),
                'transaction_count': int(count),
                'total_amount': float(total or 0),
                'average_amount': round(float(avg or 0), 2),
                'percentage': 0  # Will calculate below
            }
            for method, count, total, avg in method_data
        ]
        
        total_amount = sum(m['total_amount'] for m in methods)
        for method in methods:
            method['percentage'] = round((method['total_amount'] / total_amount * 100) if total_amount > 0 else 0, 2)
        
        return {
            'payment_methods': methods,
            'total_transactions': sum(m['transaction_count'] for m in methods),
            'total_amount': round(total_amount, 2)
        }
    
    @staticmethod
    def get_refund_analysis(db: Session, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> Dict[str, Any]:
        """Refund analysis"""
        query = db.query(
            func.date(Payment.payment_date).label('date'),
            func.count(Payment.id).label('refund_count'),
            func.sum(Payment.amount).label('refund_amount')
        ).filter(
            Payment.payment_status == PaymentStatus.refunded
        )
        
        if start_date:
            query = query.filter(Payment.payment_date >= start_date)
        if end_date:
            query = query.filter(Payment.payment_date <= end_date)
        
        refund_data = query.group_by(func.date(Payment.payment_date)).order_by(
            func.date(Payment.payment_date)
        ).all()
        
        refunds = [
            {
                'date': str(date),
                'refund_count': int(count),
                'refund_amount': float(amount or 0)
            }
            for date, count, amount in refund_data
        ]
        
        total_refunds = sum(r['refund_amount'] for r in refunds)
        total_count = sum(r['refund_count'] for r in refunds)
        avg_refund = total_refunds / total_count if total_count > 0 else 0
        
        return {
            'refunds': refunds,
            'total_refund_amount': round(total_refunds, 2),
            'total_refund_count': total_count,
            'average_refund_amount': round(avg_refund, 2)
        }
    
    # ==================== COMPREHENSIVE ANALYTICS ====================
    
    @staticmethod
    def get_comprehensive_analytics(
        db: Session,
        start_date: Optional[str] = None,
        end_date: Optional[str] = None,
        include_revenue: bool = True,
        include_operational: bool = True,
        include_guest: bool = True,
        include_financial: bool = True
    ) -> Dict[str, Any]:
        """Get comprehensive analytics across all categories"""
        start, end = AnalyticsService.parse_date_range(start_date, end_date)
        
        result = {
            'period': {
                'start': start.isoformat() if start else None,
                'end': end.isoformat() if end else None
            }
        }
        
        if include_revenue:
            result['revenue'] = {
                'revpar': AnalyticsService.get_revpar(db, start, end),
                'adr': AnalyticsService.get_adr(db, start, end),
                'occupancy_rate': AnalyticsService.get_occupancy_rate(db, start, end),
                'revenue_forecast': AnalyticsService.get_revenue_forecast(db),
                'market_penetration': AnalyticsService.get_market_penetration(db, start, end)
            }
        
        if include_operational:
            result['operational'] = {
                'staff_performance': AnalyticsService.get_staff_performance(db, start, end),
                'service_usage': AnalyticsService.get_service_usage_analytics(db, start, end),
                'operational_efficiency': AnalyticsService.get_operational_efficiency(db, start, end)
            }
        
        if include_guest:
            result['guest'] = {
                'lifetime_value': AnalyticsService.get_guest_lifetime_value(db, start, end),
                'customer_acquisition_cost': AnalyticsService.get_customer_acquisition_cost(db, start, end),
                'repeat_guest_rate': AnalyticsService.get_repeat_guest_rate(db, start, end),
                'satisfaction_trends': AnalyticsService.get_guest_satisfaction_trends(db, start, end)
            }
        
        if include_financial:
            result['financial'] = {
                'profit_loss': AnalyticsService.get_profit_loss(db, start, end),
                'payment_methods': AnalyticsService.get_payment_method_analytics(db, start, end),
                'refund_analysis': AnalyticsService.get_refund_analysis(db, start, end)
            }
        
        return result

