"""
Client Growth Analytics Service

This service provides comprehensive client growth and analytics functionality
including acquisition trends, demographic analysis, lifecycle analytics, and
loan officer performance tracking.
"""

from django.db import models
from django.db.models import Count, Sum, Avg, Q, F, Case, When, Value, IntegerField
from django.utils import timezone
from datetime import datetime, timedelta, date
from decimal import Decimal
from typing import Dict, List, Optional, Any, Tuple
import calendar
from collections import defaultdict

from users.models import CustomUser, Branch
from loans.models import Loan, LoanApplication
from utils.models import Notification


class ClientGrowthAnalytics:
    """
    Service class for client growth and analytics
    Provides methods for tracking client acquisition, demographics, lifecycle, and officer performance
    """
    
    def __init__(self):
        self.current_date = timezone.now().date()
    
    def get_acquisition_trends(self, branch_id: Optional[str] = None, period: str = 'monthly', 
                             months: int = 12) -> Dict[str, Any]:
        """
        Get client acquisition trends over time
        
        Args:
            branch_id: Optional branch ID to filter by
            period: 'daily', 'weekly', 'monthly', or 'yearly'
            months: Number of months to look back
            
        Returns:
            Dictionary containing acquisition trend data
        """
        end_date = self.current_date
        start_date = end_date - timedelta(days=months * 30)
        
        # Base queryset for clients
        clients_query = CustomUser.objects.filter(
            role='borrower',
            created_at__date__gte=start_date,
            created_at__date__lte=end_date
        )
        
        if branch_id:
            clients_query = clients_query.filter(branch_id=branch_id)
        
        # Group by period
        if period == 'daily':
            date_format = '%Y-%m-%d'
            date_field = 'created_at__date'
        elif period == 'weekly':
            date_format = '%Y-W%U'
            date_field = 'created_at__week'
        elif period == 'monthly':
            date_format = '%Y-%m'
            date_field = 'created_at__month'
        else:  # yearly
            date_format = '%Y'
            date_field = 'created_at__year'
        
        # Get acquisition data
        acquisition_data = clients_query.extra(
            select={'period': f"DATE_FORMAT(created_at, '{date_format}')"}
        ).values('period').annotate(
            new_clients=Count('id'),
            approved_clients=Count('id', filter=Q(status='active')),
            pending_clients=Count('id', filter=Q(status='pending_approval')),
            rejected_clients=Count('id', filter=Q(status__in=['inactive', 'suspended']))
        ).order_by('period')
        
        # Calculate conversion rates
        trend_data = []
        total_new = 0
        total_approved = 0
        
        for data in acquisition_data:
            total_new += data['new_clients']
            total_approved += data['approved_clients']
            
            conversion_rate = (data['approved_clients'] / data['new_clients'] * 100) if data['new_clients'] > 0 else 0
            
            trend_data.append({
                'period': data['period'],
                'new_clients': data['new_clients'],
                'approved_clients': data['approved_clients'],
                'pending_clients': data['pending_clients'],
                'rejected_clients': data['rejected_clients'],
                'conversion_rate': round(conversion_rate, 2),
                'cumulative_new': total_new,
                'cumulative_approved': total_approved
            })
        
        # Calculate growth rates
        for i in range(1, len(trend_data)):
            prev_new = trend_data[i-1]['new_clients']
            current_new = trend_data[i]['new_clients']
            
            if prev_new > 0:
                growth_rate = ((current_new - prev_new) / prev_new) * 100
                trend_data[i]['growth_rate'] = round(growth_rate, 2)
            else:
                trend_data[i]['growth_rate'] = 0
        
        # Set first period growth rate to 0
        if trend_data:
            trend_data[0]['growth_rate'] = 0
        
        # Calculate summary statistics
        total_clients = clients_query.count()
        avg_monthly_acquisition = total_clients / months if months > 0 else 0
        overall_conversion_rate = (total_approved / total_new * 100) if total_new > 0 else 0
        
        return {
            'trend_data': trend_data,
            'summary': {
                'total_clients': total_clients,
                'total_approved': total_approved,
                'total_pending': clients_query.filter(status='pending_approval').count(),
                'total_rejected': clients_query.filter(status__in=['inactive', 'suspended']).count(),
                'avg_monthly_acquisition': round(avg_monthly_acquisition, 2),
                'overall_conversion_rate': round(overall_conversion_rate, 2),
                'period': period,
                'months_analyzed': months,
                'date_range': {
                    'start': start_date.isoformat(),
                    'end': end_date.isoformat()
                }
            }
        }
    
    def get_demographic_analysis(self, filters: Optional[Dict[str, Any]] = None) -> Dict[str, Any]:
        """
        Get client demographic breakdown and analysis
        
        Args:
            filters: Optional filters (branch_id, date_range, status, etc.)
            
        Returns:
            Dictionary containing demographic analysis data
        """
        # Base queryset
        clients_query = CustomUser.objects.filter(role='borrower')
        
        # Apply filters
        if filters:
            if 'branch_id' in filters and filters['branch_id']:
                clients_query = clients_query.filter(branch_id=filters['branch_id'])
            
            if 'status' in filters and filters['status']:
                clients_query = clients_query.filter(status=filters['status'])
            
            if 'date_range' in filters and filters['date_range']:
                start_date = filters['date_range'].get('start')
                end_date = filters['date_range'].get('end')
                if start_date:
                    clients_query = clients_query.filter(created_at__date__gte=start_date)
                if end_date:
                    clients_query = clients_query.filter(created_at__date__lte=end_date)
        
        total_clients = clients_query.count()
        
        # Gender distribution
        gender_data = clients_query.values('gender').annotate(
            count=Count('id')
        ).order_by('gender')
        
        gender_distribution = {}
        for item in gender_data:
            gender_key = item['gender'] or 'Unknown'
            gender_distribution[gender_key] = {
                'count': item['count'],
                'percentage': round((item['count'] / total_clients * 100), 2) if total_clients > 0 else 0
            }
        
        # Age distribution
        current_year = timezone.now().year
        age_ranges = [
            ('18-25', 18, 25),
            ('26-35', 26, 35),
            ('36-45', 36, 45),
            ('46-55', 46, 55),
            ('56-65', 56, 65),
            ('65+', 66, 100)
        ]
        
        age_distribution = {}
        for age_range, min_age, max_age in age_ranges:
            min_birth_year = current_year - max_age
            max_birth_year = current_year - min_age
            
            count = clients_query.filter(
                date_of_birth__year__gte=min_birth_year,
                date_of_birth__year__lte=max_birth_year
            ).count()
            
            age_distribution[age_range] = {
                'count': count,
                'percentage': round((count / total_clients * 100), 2) if total_clients > 0 else 0
            }
        
        # Marital status distribution
        marital_data = clients_query.values('marital_status').annotate(
            count=Count('id')
        ).order_by('marital_status')
        
        marital_distribution = {}
        for item in marital_data:
            marital_key = item['marital_status'] or 'Unknown'
            marital_distribution[marital_key] = {
                'count': item['count'],
                'percentage': round((item['count'] / total_clients * 100), 2) if total_clients > 0 else 0
            }
        
        # Geographic distribution (by county)
        geographic_data = clients_query.values('county').annotate(
            count=Count('id')
        ).order_by('-count')[:10]  # Top 10 counties
        
        geographic_distribution = {}
        for item in geographic_data:
            county_key = item['county'] or 'Unknown'
            geographic_distribution[county_key] = {
                'count': item['count'],
                'percentage': round((item['count'] / total_clients * 100), 2) if total_clients > 0 else 0
            }
        
        # Business type distribution
        business_data = clients_query.values('business_type').annotate(
            count=Count('id')
        ).order_by('-count')[:10]  # Top 10 business types
        
        business_distribution = {}
        for item in business_data:
            business_key = item['business_type'] or 'Unknown'
            business_distribution[business_key] = {
                'count': item['count'],
                'percentage': round((item['count'] / total_clients * 100), 2) if total_clients > 0 else 0
            }
        
        # Income distribution
        income_ranges = [
            ('0-10K', 0, 10000),
            ('10K-25K', 10000, 25000),
            ('25K-50K', 25000, 50000),
            ('50K-100K', 50000, 100000),
            ('100K-250K', 100000, 250000),
            ('250K+', 250000, float('inf'))
        ]
        
        income_distribution = {}
        for income_range, min_income, max_income in income_ranges:
            if max_income == float('inf'):
                count = clients_query.filter(monthly_income__gte=min_income).count()
            else:
                count = clients_query.filter(
                    monthly_income__gte=min_income,
                    monthly_income__lt=max_income
                ).count()
            
            income_distribution[income_range] = {
                'count': count,
                'percentage': round((count / total_clients * 100), 2) if total_clients > 0 else 0
            }
        
        return {
            'total_clients': total_clients,
            'gender_distribution': gender_distribution,
            'age_distribution': age_distribution,
            'marital_distribution': marital_distribution,
            'geographic_distribution': geographic_distribution,
            'business_distribution': business_distribution,
            'income_distribution': income_distribution,
            'analysis_date': timezone.now().isoformat(),
            'filters_applied': filters or {}
        }
    
    def get_lifecycle_analytics(self, client_segment: Optional[str] = None) -> Dict[str, Any]:
        """
        Get client lifecycle analytics and journey analysis
        
        Args:
            client_segment: Optional segment filter ('new', 'active', 'dormant', 'churned')
            
        Returns:
            Dictionary containing lifecycle analytics data
        """
        # Define lifecycle stages
        current_date = timezone.now().date()
        
        # Base queryset
        clients_query = CustomUser.objects.filter(role='borrower')
        
        # Apply segment filter
        if client_segment:
            if client_segment == 'new':
                # Clients registered in last 30 days
                clients_query = clients_query.filter(
                    created_at__date__gte=current_date - timedelta(days=30)
                )
            elif client_segment == 'active':
                # Clients with active loans or recent activity
                clients_query = clients_query.filter(
                    Q(loans__status='active') | 
                    Q(loan_applications__created_at__gte=current_date - timedelta(days=90))
                ).distinct()
            elif client_segment == 'dormant':
                # Clients with no activity in last 90 days
                active_client_ids = CustomUser.objects.filter(
                    Q(loans__status='active') | 
                    Q(loan_applications__created_at__gte=current_date - timedelta(days=90))
                ).values_list('id', flat=True)
                clients_query = clients_query.exclude(id__in=active_client_ids)
            elif client_segment == 'churned':
                # Clients with rejected status or no activity in 180+ days
                clients_query = clients_query.filter(
                    Q(status__in=['inactive', 'suspended']) |
                    Q(created_at__date__lt=current_date - timedelta(days=180))
                )
        
        total_clients = clients_query.count()
        
        # Lifecycle stage analysis
        lifecycle_stages = {
            'registration': clients_query.count(),
            'kyc_completed': clients_query.filter(is_verified=True).count(),
            'first_application': clients_query.filter(loan_applications__isnull=False).distinct().count(),
            'first_approval': clients_query.filter(loans__isnull=False).distinct().count(),
            'active_borrower': clients_query.filter(loans__status='active').distinct().count(),
            'repeat_borrower': clients_query.annotate(
                loan_count=Count('loans')
            ).filter(loan_count__gt=1).count(),
            'defaulted': clients_query.filter(loans__status='defaulted').distinct().count()
        }
        
        # Calculate conversion rates between stages
        conversion_rates = {}
        stages = list(lifecycle_stages.keys())
        for i in range(1, len(stages)):
            prev_stage = stages[i-1]
            current_stage = stages[i]
            
            if lifecycle_stages[prev_stage] > 0:
                rate = (lifecycle_stages[current_stage] / lifecycle_stages[prev_stage]) * 100
                conversion_rates[f"{prev_stage}_to_{current_stage}"] = round(rate, 2)
            else:
                conversion_rates[f"{prev_stage}_to_{current_stage}"] = 0
        
        # Time to conversion analysis
        time_to_first_loan = clients_query.filter(
            loans__isnull=False
        ).annotate(
            days_to_loan=F('loans__created_at') - F('created_at')
        ).aggregate(
            avg_days=Avg('days_to_loan'),
            min_days=models.Min('days_to_loan'),
            max_days=models.Max('days_to_loan')
        )
        
        # Client value analysis
        client_value_data = clients_query.annotate(
            total_borrowed=Sum('loans__principal_amount'),
            total_paid=Sum('loans__amount_paid'),
            loan_count=Count('loans'),
            avg_loan_size=Avg('loans__principal_amount')
        ).aggregate(
            avg_total_borrowed=Avg('total_borrowed'),
            avg_total_paid=Avg('total_paid'),
            avg_loan_count=Avg('loan_count'),
            avg_loan_size=Avg('avg_loan_size')
        )
        
        # Retention analysis
        retention_periods = [30, 60, 90, 180, 365]
        retention_data = {}
        
        for period in retention_periods:
            cutoff_date = current_date - timedelta(days=period)
            
            # Clients who registered before the cutoff
            eligible_clients = clients_query.filter(created_at__date__lte=cutoff_date)
            eligible_count = eligible_clients.count()
            
            if eligible_count > 0:
                # Clients who had activity after the cutoff
                retained_clients = eligible_clients.filter(
                    Q(loans__created_at__date__gt=cutoff_date) |
                    Q(loan_applications__created_at__date__gt=cutoff_date)
                ).distinct().count()
                
                retention_rate = (retained_clients / eligible_count) * 100
                retention_data[f"{period}_days"] = {
                    'eligible_clients': eligible_count,
                    'retained_clients': retained_clients,
                    'retention_rate': round(retention_rate, 2)
                }
            else:
                retention_data[f"{period}_days"] = {
                    'eligible_clients': 0,
                    'retained_clients': 0,
                    'retention_rate': 0
                }
        
        return {
            'total_clients': total_clients,
            'client_segment': client_segment,
            'lifecycle_stages': lifecycle_stages,
            'conversion_rates': conversion_rates,
            'time_to_conversion': {
                'avg_days_to_first_loan': time_to_first_loan['avg_days'].days if time_to_first_loan['avg_days'] else 0,
                'min_days_to_first_loan': time_to_first_loan['min_days'].days if time_to_first_loan['min_days'] else 0,
                'max_days_to_first_loan': time_to_first_loan['max_days'].days if time_to_first_loan['max_days'] else 0
            },
            'client_value': {
                'avg_total_borrowed': float(client_value_data['avg_total_borrowed'] or 0),
                'avg_total_paid': float(client_value_data['avg_total_paid'] or 0),
                'avg_loan_count': float(client_value_data['avg_loan_count'] or 0),
                'avg_loan_size': float(client_value_data['avg_loan_size'] or 0)
            },
            'retention_analysis': retention_data,
            'analysis_date': timezone.now().isoformat()
        }
    
    def get_officer_performance(self, officer_id: Optional[str] = None, period: str = 'monthly',
                              months: int = 6) -> Dict[str, Any]:
        """
        Get loan officer performance metrics and tracking
        
        Args:
            officer_id: Optional specific officer ID, if None returns all officers
            period: 'monthly', 'quarterly', or 'yearly'
            months: Number of months to analyze
            
        Returns:
            Dictionary containing officer performance data
        """
        end_date = timezone.now().date()
        start_date = end_date - timedelta(days=months * 30)
        
        # Base queryset for loan officers
        officers_query = CustomUser.objects.filter(
            role__in=['loan_officer', 'team_leader']
        )
        
        if officer_id:
            officers_query = officers_query.filter(id=officer_id)
        
        performance_data = []
        
        for officer in officers_query:
            # Get officer's portfolio clients
            portfolio_clients = officer.portfolio_clients.filter(
                role='borrower',
                assigned_date__date__gte=start_date if officer.portfolio_clients.filter(assigned_date__isnull=False).exists() else start_date
            )
            
            client_ids = list(portfolio_clients.values_list('id', flat=True))
            
            # Loan statistics
            loans = Loan.objects.filter(
                borrower_id__in=client_ids,
                created_at__date__gte=start_date,
                created_at__date__lte=end_date
            )
            
            applications = LoanApplication.objects.filter(
                borrower_id__in=client_ids,
                created_at__date__gte=start_date,
                created_at__date__lte=end_date
            )
            
            # Calculate performance metrics
            total_clients = portfolio_clients.count()
            total_loans = loans.count()
            active_loans = loans.filter(status='active').count()
            completed_loans = loans.filter(status='paid').count()
            defaulted_loans = loans.filter(status='defaulted').count()
            
            total_disbursed = loans.aggregate(total=Sum('principal_amount'))['total'] or Decimal('0')
            total_collected = loans.aggregate(total=Sum('amount_paid'))['total'] or Decimal('0')
            # Calculate outstanding using outstanding_amount property
            active_loan_objects = loans.filter(status='active')
            total_outstanding = sum(loan.outstanding_amount for loan in active_loan_objects)
            
            # Application metrics
            total_applications = applications.count()
            approved_applications = applications.filter(status='approved').count()
            rejected_applications = applications.filter(status='rejected').count()
            pending_applications = applications.filter(status='pending').count()
            
            # Calculate rates
            collection_rate = (total_collected / total_disbursed * 100) if total_disbursed > 0 else Decimal('0')
            default_rate = (Decimal(str(defaulted_loans)) / Decimal(str(total_loans)) * 100) if total_loans > 0 else Decimal('0')
            approval_rate = (Decimal(str(approved_applications)) / Decimal(str(total_applications)) * 100) if total_applications > 0 else Decimal('0')
            
            # Client acquisition in period
            new_clients = portfolio_clients.filter(
                assigned_date__date__gte=start_date,
                assigned_date__date__lte=end_date
            ).count()
            
            # Average loan size
            avg_loan_size = loans.aggregate(avg=Avg('principal_amount'))['avg'] or Decimal('0')
            
            # Portfolio at risk (PAR) - loans overdue by 30+ days
            overdue_loans = loans.filter(
                status='active',
                due_date__lt=end_date - timedelta(days=30)
            )
            par_amount = overdue_loans.aggregate(
                total=Sum(F('total_amount') - F('amount_paid'))
            )['total'] or Decimal('0')
            par_ratio = (par_amount / total_outstanding * 100) if total_outstanding > 0 else Decimal('0')
            
            # Monthly trend data
            monthly_trends = []
            for i in range(months):
                month_start = end_date.replace(day=1) - timedelta(days=i*30)
                month_end = (month_start + timedelta(days=32)).replace(day=1) - timedelta(days=1)
                
                month_loans = loans.filter(
                    created_at__date__gte=month_start,
                    created_at__date__lte=month_end
                )
                
                month_disbursed = month_loans.aggregate(total=Sum('principal_amount'))['total'] or Decimal('0')
                month_collected = month_loans.aggregate(total=Sum('amount_paid'))['total'] or Decimal('0')
                
                monthly_trends.append({
                    'month': month_start.strftime('%Y-%m'),
                    'loans_disbursed': month_loans.count(),
                    'amount_disbursed': float(month_disbursed),
                    'amount_collected': float(month_collected),
                    'new_clients': portfolio_clients.filter(
                        assigned_date__date__gte=month_start,
                        assigned_date__date__lte=month_end
                    ).count()
                })
            
            monthly_trends.reverse()  # Show oldest to newest
            
            officer_performance = {
                'officer_id': str(officer.id),
                'officer_name': officer.get_full_name(),
                'officer_role': officer.role,
                'branch': officer.branch.name if officer.branch else 'No Branch',
                'portfolio_metrics': {
                    'total_clients': total_clients,
                    'new_clients_period': new_clients,
                    'total_loans': total_loans,
                    'active_loans': active_loans,
                    'completed_loans': completed_loans,
                    'defaulted_loans': defaulted_loans,
                    'total_applications': total_applications,
                    'approved_applications': approved_applications,
                    'rejected_applications': rejected_applications,
                    'pending_applications': pending_applications
                },
                'financial_metrics': {
                    'total_disbursed': float(total_disbursed),
                    'total_collected': float(total_collected),
                    'total_outstanding': float(total_outstanding),
                    'avg_loan_size': float(avg_loan_size),
                    'par_amount': float(par_amount)
                },
                'performance_ratios': {
                    'collection_rate': float(collection_rate),
                    'default_rate': float(default_rate),
                    'approval_rate': float(approval_rate),
                    'par_ratio': float(par_ratio)
                },
                'monthly_trends': monthly_trends,
                'period_analyzed': {
                    'start_date': start_date.isoformat(),
                    'end_date': end_date.isoformat(),
                    'months': months
                }
            }
            
            performance_data.append(officer_performance)
        
        # Calculate comparative metrics if multiple officers
        if len(performance_data) > 1:
            # Calculate averages and rankings
            metrics_for_comparison = [
                'collection_rate', 'default_rate', 'approval_rate', 'par_ratio'
            ]
            
            for metric in metrics_for_comparison:
                values = [officer['performance_ratios'][metric] for officer in performance_data]
                avg_value = sum(values) / len(values) if values else 0
                
                for officer in performance_data:
                    officer['performance_ratios'][f'{metric}_vs_avg'] = officer['performance_ratios'][metric] - avg_value
            
            # Rank officers by key metrics
            performance_data.sort(key=lambda x: x['performance_ratios']['collection_rate'], reverse=True)
            for i, officer in enumerate(performance_data):
                officer['rankings'] = {
                    'collection_rate_rank': i + 1,
                    'total_officers': len(performance_data)
                }
        
        return {
            'officers_analyzed': len(performance_data),
            'period': period,
            'months_analyzed': months,
            'performance_data': performance_data,
            'analysis_date': timezone.now().isoformat(),
            'summary': {
                'total_clients': sum(officer['portfolio_metrics']['total_clients'] for officer in performance_data),
                'total_loans': sum(officer['portfolio_metrics']['total_loans'] for officer in performance_data),
                'total_disbursed': sum(officer['financial_metrics']['total_disbursed'] for officer in performance_data),
                'total_collected': sum(officer['financial_metrics']['total_collected'] for officer in performance_data),
                'avg_collection_rate': sum(officer['performance_ratios']['collection_rate'] for officer in performance_data) / len(performance_data) if performance_data else 0,
                'avg_default_rate': sum(officer['performance_ratios']['default_rate'] for officer in performance_data) / len(performance_data) if performance_data else 0
            }
        }