"""
Client Report Service

Provides client growth, portfolio metrics, and demographic analytics
for the reports system.

This service tracks client acquisition, growth trends, portfolio distribution,
and demographic breakdowns.
"""

from decimal import Decimal
from typing import Dict, List, Optional
from django.db.models import Sum, Count, Q, Avg, F
from django.utils import timezone
from .query_optimizer import QueryOptimizer
from .cache_service import CacheService


class ClientReportService:
    """
    Service for generating client growth and portfolio metrics.
    
    Provides functionality for:
    - Client growth tracking (new clients, total clients)
    - Portfolio distribution by demographics
    - Client acquisition trends
    - Geographic and demographic breakdowns
    - Loan penetration rates
    """
    
    @staticmethod
    def get_client_metrics(branch_id: Optional[str] = None) -> Dict:
        """
        Calculate aggregate client growth and portfolio metrics.
        
        Args:
            branch_id: Optional branch ID to filter clients
            
        Returns:
            Dictionary containing:
            - total_clients: Total count of active clients
            - new_clients_this_month: Clients registered this month
            - new_clients_last_month: Clients registered last month
            - growth_rate: Month-over-month growth percentage
            - clients_with_loans: Count of clients with active loans
            - loan_penetration_rate: Percentage of clients with loans
            - total_outstanding: Total outstanding amount across all clients
            
        Validates: Requirements 2.1, 2.2
        """
        from users.models import CustomUser
        from loans.models import Loan
        from datetime import datetime, timedelta
        
        # Try to get from cache first
        def calculate_metrics():
            # Get active clients (exclude soft-deleted) with optimized query
            clients = CustomUser.objects.filter(
                role='borrower',
                is_active=True
            ).exclude(
                status='inactive'
            )
            
            # Apply branch filter if provided
            if branch_id:
                clients = clients.filter(branch_id=branch_id)
            
            # Optimize queryset
            clients = QueryOptimizer.get_optimized_clients_queryset(clients)
            
            total_clients = clients.count()
            
            # Calculate date ranges
            now = timezone.now()
            first_day_this_month = now.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
            first_day_last_month = (first_day_this_month - timedelta(days=1)).replace(day=1)
            
            # Get new clients this month
            new_clients_this_month = clients.filter(
                date_joined__gte=first_day_this_month
            ).count()
            
            # Get new clients last month
            new_clients_last_month = clients.filter(
                date_joined__gte=first_day_last_month,
                date_joined__lt=first_day_this_month
            ).count()
            
            # Calculate growth rate
            if new_clients_last_month > 0:
                growth_rate = ((new_clients_this_month - new_clients_last_month) / new_clients_last_month) * 100
            else:
                growth_rate = 100.0 if new_clients_this_month > 0 else 0.0
            
            # Get clients with active loans
            clients_with_loans = clients.filter(
                loans__status='active',
                loans__is_deleted=False,
                loans__is_rolled_over=False
            ).distinct().count()
            
            # Calculate loan penetration rate
            loan_penetration_rate = (clients_with_loans / total_clients * 100) if total_clients > 0 else 0.0
            
            # Calculate total outstanding by summing outstanding_amount property for each loan
            # Use optimized queryset
            active_loans = QueryOptimizer.get_optimized_loans_queryset(
                Loan.objects.filter(
                    borrower__in=clients,
                    status='active',
                    is_deleted=False,
                    is_rolled_over=False
                )
            )
            
            total_outstanding = Decimal('0.00')
            for loan in active_loans:
                total_outstanding += loan.outstanding_amount
            
            return {
                'total_clients': total_clients,
                'new_clients_this_month': new_clients_this_month,
                'new_clients_last_month': new_clients_last_month,
                'growth_rate': round(growth_rate, 2),
                'clients_with_loans': clients_with_loans,
                'loan_penetration_rate': round(loan_penetration_rate, 2),
                'total_outstanding': total_outstanding
            }
        
        # Use cache service
        return CacheService.get_or_set_client_metrics(
            branch_id,
            calculate_metrics,
            timeout=300  # 5 minutes
        )
    
    @staticmethod
    def get_demographic_distribution(branch_id: Optional[str] = None) -> Dict:
        """
        Get distribution of clients by demographics.
        
        Returns:
        - Gender distribution (Male, Female, Other)
        - Age group distribution
        - Clients by loan status (with loans, without loans)
        
        Args:
            branch_id: Optional branch ID to filter clients
            
        Returns:
            Dictionary with demographic breakdowns
            
        Validates: Requirements 2.3
        """
        from users.models import CustomUser
        from datetime import datetime
        
        # Get active clients (exclude soft-deleted) with optimized query
        clients = CustomUser.objects.filter(
            role='borrower',
            is_active=True
        ).exclude(
            status='inactive'
        )
        
        # Apply branch filter if provided
        if branch_id:
            clients = clients.filter(branch_id=branch_id)
        
        # Optimize queryset
        clients = QueryOptimizer.get_optimized_clients_queryset(clients)
        
        # Gender distribution
        gender_distribution = {
            'Male': clients.filter(gender='M').count(),
            'Female': clients.filter(gender='F').count(),
            'Other': clients.filter(gender='Other').count(),
        }
        
        # Loan status distribution
        clients_with_loans = clients.filter(
            loans__status='active',
            loans__is_deleted=False,
            loans__is_rolled_over=False
        ).distinct().count()
        
        clients_without_loans = clients.count() - clients_with_loans
        
        loan_status_distribution = {
            'With Active Loans': clients_with_loans,
            'Without Loans': clients_without_loans,
        }
        
        return {
            'gender': gender_distribution,
            'loan_status': loan_status_distribution,
        }
    
    @staticmethod
    def get_growth_trends(months: int = 6, branch_id: Optional[str] = None) -> List[Dict]:
        """
        Get client growth trends over the past N months.
        
        Args:
            months: Number of months to analyze (default 6)
            branch_id: Optional branch ID to filter clients
            
        Returns:
            List of dictionaries containing monthly growth data,
            ordered chronologically
            
        Validates: Requirements 2.4
        """
        from users.models import CustomUser
        from datetime import datetime, timedelta
        from django.db.models.functions import TruncMonth
        
        # Get active clients (exclude soft-deleted) with optimized query
        clients = CustomUser.objects.filter(
            role='borrower',
            is_active=True
        ).exclude(
            status='inactive'
        )
        
        # Apply branch filter if provided
        if branch_id:
            clients = clients.filter(branch_id=branch_id)
        
        # Calculate date range
        now = timezone.now()
        start_date = now - timedelta(days=months * 30)
        
        # Get monthly registration counts
        monthly_data = clients.filter(
            date_joined__gte=start_date
        ).annotate(
            month=TruncMonth('date_joined')
        ).values('month').annotate(
            new_clients=Count('id')
        ).order_by('month')
        
        # Format results
        growth_trends = []
        cumulative_total = clients.filter(date_joined__lt=start_date).count()
        
        for data in monthly_data:
            cumulative_total += data['new_clients']
            growth_trends.append({
                'month': data['month'].strftime('%B %Y'),
                'new_clients': data['new_clients'],
                'cumulative_total': cumulative_total,
            })
        
        return growth_trends
    
    @staticmethod
    def get_top_clients_by_portfolio(limit: int = 10, branch_id: Optional[str] = None) -> List[Dict]:
        """
        Get clients with the largest outstanding loan portfolios.
        
        Args:
            limit: Maximum number of clients to return (default 10)
            branch_id: Optional branch ID to filter clients
            
        Returns:
            List of dictionaries containing client info and portfolio metrics,
            ordered by total_outstanding descending
            
        Validates: Requirements 2.5
        """
        from users.models import CustomUser
        from loans.models import Loan
        
        # Get active clients (exclude soft-deleted) with optimized query
        clients = CustomUser.objects.filter(
            role='borrower',
            is_active=True
        ).exclude(
            status='inactive'
        )
        
        # Apply branch filter if provided
        if branch_id:
            clients = clients.filter(branch_id=branch_id)
        
        # Optimize queryset
        clients = QueryOptimizer.get_optimized_clients_queryset(clients)
        
        # Calculate portfolio size for each client
        client_portfolios = []
        for client in clients:
            # Get active loans
            active_loans = Loan.objects.filter(
                borrower=client,
                status='active',
                is_deleted=False,
                is_rolled_over=False
            )
            
            total_loans = active_loans.count()
            
            # Skip clients without loans
            if total_loans == 0:
                continue
            
            # Calculate total outstanding
            total_outstanding = Decimal('0.00')
            for loan in active_loans:
                total_outstanding += loan.outstanding_amount
            
            # Calculate total disbursed
            total_disbursed = active_loans.aggregate(
                total=Sum('principal_amount')
            )['total'] or Decimal('0.00')
            
            client_portfolios.append({
                'id': str(client.id),
                'name': client.get_full_name(),
                'phone_number': client.phone_number,
                'total_loans': total_loans,
                'total_disbursed': total_disbursed,
                'total_outstanding': total_outstanding,
                'date_joined': client.date_joined.strftime('%Y-%m-%d') if client.date_joined else 'N/A',
            })
        
        # Sort by total outstanding descending
        client_portfolios.sort(key=lambda x: x['total_outstanding'], reverse=True)
        
        # Return top N clients
        return client_portfolios[:limit]