"""
Simple Reports Service for Dashboard
Provides basic analytics without complex calculations that might fail
"""
from django.db import models
from django.db.models import Sum, Count, Q, Avg, F, Case, When, Value, DecimalField, Max
from django.utils import timezone
from datetime import datetime, timedelta, date
from decimal import Decimal
from loans.models import Loan, LoanApplication, Repayment
from users.models import CustomUser


class SimpleReportsService:
    """
    Simple service class for generating basic reports and analytics
    """
    
    def __init__(self):
        self._refresh_dates()

    def _refresh_dates(self):
        """Refresh date references — called at the start of each request to avoid stale singleton dates"""
        self.today = timezone.now().date()
        self.current_month_start = self.today.replace(day=1)

    def get_summary_metrics(self, branch_id=None, portfolio_manager_id=None):
        """Get basic summary metrics for the dashboard"""
        # Base queryset - exclude soft-deleted and rolled-over loans
        loans_qs = Loan.objects.filter(
            status='active', 
            is_deleted=False,
            is_rolled_over=False
        ).exclude(status='rolled_over')
        
        # Apply portfolio-based filtering
        if portfolio_manager_id:
            loans_qs = loans_qs.filter(borrower__portfolio_manager_id=portfolio_manager_id)
        elif branch_id:
            # Branch ID is a UUID, no need to convert to int
            loans_qs = loans_qs.filter(borrower__branch_id=branch_id)
        
        # Basic counts and sums
        total_active_loans = loans_qs.count()
        
        # Portfolio value (sum of principal amounts)
        portfolio_data = loans_qs.aggregate(
            total_portfolio=Sum('principal_amount'),
            total_disbursed=Sum('total_amount')
        )
        
        total_portfolio_value = portfolio_data['total_portfolio'] or Decimal('0.00')
        total_disbursed = portfolio_data['total_disbursed'] or Decimal('0.00')
        
        # Calculate total collected and outstanding by iterating (since amount_paid is a property)
        total_collected = Decimal('0.00')
        total_outstanding = Decimal('0.00')
        for loan in loans_qs:
            loan_total = loan.total_amount or Decimal('0.00')
            loan_paid = loan.amount_paid or Decimal('0.00')
            total_collected += loan_paid
            total_outstanding += (loan_total - loan_paid)
        
        # Collection rate
        collection_rate = Decimal('0.00')
        if total_disbursed > 0:
            collection_rate = (total_collected / total_disbursed) * 100
        
        # Loans due today using repayment scheduler
        from loans.repayment_scheduler import RepaymentScheduler
        loans_due_today = 0
        overdue_loans = 0
        
        for loan in loans_qs:
            next_due_date = RepaymentScheduler.get_next_payment_due_date(loan)
            if next_due_date == self.today:
                loans_due_today += 1
            
            if RepaymentScheduler.is_loan_in_arrears(loan):
                overdue_loans += 1
        
        return {
            'total_active_loans': total_active_loans,
            'total_portfolio_value': total_portfolio_value,
            'total_outstanding': total_outstanding,
            'collection_rate': float(collection_rate),
            'loans_due_today': loans_due_today,
            'overdue_loans': overdue_loans,
        }
    
    def get_loans_due_today(self, branch_id=None, portfolio_manager_id=None):
        """Get loans due today using repayment scheduler"""
        from loans.repayment_scheduler import RepaymentScheduler
        
        # Exclude soft-deleted and rolled-over loans
        loans_qs = Loan.objects.filter(
            status='active', 
            is_deleted=False,
            is_rolled_over=False
        ).exclude(status='rolled_over')
        
        if portfolio_manager_id:
            loans_qs = loans_qs.filter(borrower__portfolio_manager_id=portfolio_manager_id)
        elif branch_id:
            # Branch ID is a UUID, no need to convert to int
            loans_qs = loans_qs.filter(borrower__branch_id=branch_id)
        
        loans_due_today = []
        daily_count = 0
        weekly_count = 0
        monthly_count = 0
        
        for loan in loans_qs.select_related('borrower', 'application', 'application__loan_product'):
            next_due_date = RepaymentScheduler.get_next_payment_due_date(loan)
            if next_due_date == self.today:
                expected_payment_amount = RepaymentScheduler.calculate_expected_payment_amount(loan)
                repayment_method = RepaymentScheduler.get_repayment_method(loan)
                
                # Count by repayment method
                if repayment_method == 'daily':
                    daily_count += 1
                elif repayment_method == 'weekly':
                    weekly_count += 1
                else:
                    monthly_count += 1
                
                loan_data = {
                    'id': str(loan.id),  # Convert UUID to string for JSON serialization
                    'loan_number': loan.loan_number,
                    'borrower__first_name': loan.borrower.first_name,
                    'borrower__last_name': loan.borrower.last_name,
                    'borrower__phone_number': loan.borrower.phone_number,
                    'total_amount': float(loan.total_amount),
                    'amount_paid': float(loan.amount_paid),
                    'outstanding_balance': float(loan.outstanding_amount),
                    'expected_payment_amount': float(expected_payment_amount),
                    'repayment_method': repayment_method,
                    'due_date': next_due_date.isoformat() if isinstance(next_due_date, date) else str(next_due_date),
                    'loan_product': loan.application.loan_product.name if loan.application and loan.application.loan_product else 'Unknown',
                    'product_type': loan.application.loan_product.product_type if loan.application and loan.application.loan_product else 'unknown'
                }
                loans_due_today.append(loan_data)
        
        return {
            'loans': loans_due_today,
            'summary': {
                'total_loans_due': len(loans_due_today),
                'total_amount_due': sum(loan['outstanding_balance'] for loan in loans_due_today)
            },
            'categorized': {
                'today': loans_due_today
            },
            'daily_count': daily_count,
            'weekly_count': weekly_count,
            'monthly_count': monthly_count
        }
    
    def get_delinquent_loans(self, branch_id=None, portfolio_manager_id=None):
        """Get delinquent loans categorized by severity using repayment scheduler"""
        from loans.repayment_scheduler import RepaymentScheduler
        
        # Exclude soft-deleted and rolled-over loans
        loans_qs = Loan.objects.filter(
            status='active', 
            is_deleted=False,
            is_rolled_over=False
        ).exclude(status='rolled_over')
        
        if portfolio_manager_id:
            loans_qs = loans_qs.filter(borrower__portfolio_manager_id=portfolio_manager_id)
        elif branch_id:
            # Branch ID is a UUID, no need to convert to int
            loans_qs = loans_qs.filter(borrower__branch_id=branch_id)
        
        # Categorize by severity using repayment scheduler
        mild_delinquent = 0
        moderate_delinquent = 0
        severe_delinquent = 0
        daily_critical_count = 0
        
        for loan in loans_qs:
            if RepaymentScheduler.is_loan_in_arrears(loan):
                arrears_summary = RepaymentScheduler.get_arrears_summary(loan)
                days_overdue = arrears_summary['days_overdue']
                repayment_method = arrears_summary['repayment_method']
                
                # Adjust thresholds based on repayment method
                if repayment_method == 'daily':
                    if days_overdue <= 3:
                        mild_delinquent += 1
                    elif days_overdue <= 7:
                        moderate_delinquent += 1
                    else:
                        severe_delinquent += 1
                        daily_critical_count += 1  # Daily loans over 7 days are critical
                elif repayment_method == 'weekly':
                    if days_overdue <= 14:
                        mild_delinquent += 1
                    elif days_overdue <= 21:
                        moderate_delinquent += 1
                    else:
                        severe_delinquent += 1
                else:  # monthly
                    if days_overdue <= 30:
                        mild_delinquent += 1
                    elif days_overdue <= 60:
                        moderate_delinquent += 1
                    else:
                        severe_delinquent += 1
        
        return {
            'loans': [],
            'summary': {
                'mild_delinquent_count': mild_delinquent,
                'moderate_delinquent_count': moderate_delinquent,
                'severe_delinquent_count': severe_delinquent,
                'total_delinquent': mild_delinquent + moderate_delinquent + severe_delinquent
            },
            'daily_critical_count': daily_critical_count
        }
    
    def get_processing_fees_current_month(self, branch_id=None, portfolio_manager_id=None):
        """Get processing fees for current month"""
        loans_qs = Loan.objects.filter(
            is_deleted=False,
            created_at__date__gte=self.current_month_start
        )
        
        if portfolio_manager_id:
            loans_qs = loans_qs.filter(borrower__portfolio_manager_id=portfolio_manager_id)
        elif branch_id:
            # Branch ID is a UUID, no need to convert to int
            loans_qs = loans_qs.filter(borrower__branch_id=branch_id)
        
        fees_data = loans_qs.aggregate(
            total_fees=Sum('processing_fee'),
            total_loans=Count('id')
        )
        
        return {
            'summary': {
                'total_processing_fees': fees_data['total_fees'] or Decimal('0.00'),
                'total_loans_processed': fees_data['total_loans'] or 0
            }
        }
    
    def get_interest_income_current_month(self, branch_id=None, portfolio_manager_id=None):
        """Get interest income for current month"""
        loans_qs = Loan.objects.filter(
            is_deleted=False,
            created_at__date__gte=self.current_month_start
        )
        
        if portfolio_manager_id:
            loans_qs = loans_qs.filter(borrower__portfolio_manager_id=portfolio_manager_id)
        elif branch_id:
            # Branch ID is a UUID, no need to convert to int
            loans_qs = loans_qs.filter(borrower__branch_id=branch_id)
        
        interest_data = loans_qs.aggregate(
            total_interest=Sum('interest_amount'),
            total_loans=Count('id')
        )
        
        return {
            'summary': {
                'total_interest_income': interest_data['total_interest'] or Decimal('0.00'),
                'total_loans': interest_data['total_loans'] or 0
            }
        }
    
    def get_registration_fees_current_month(self, branch_id=None, portfolio_manager_id=None):
        """Get registration fees for current month - placeholder"""
        return {
            'summary': {
                'total_registration_income': Decimal('0.00'),
                'total_registrations': 0
            }
        }
    
    def get_customer_requests_current_month(self, branch_id=None, portfolio_manager_id=None):
        """Get customer requests for current month - placeholder"""
        return {
            'summary': {
                'pending_requests': 0,
                'in_progress_requests': 0,
                'resolved_requests': 0
            }
        }
    
    def get_completed_loans_analytics(self, branch_id=None, portfolio_manager_id=None):
        """Get completed loans analytics"""
        # Use 'paid' status instead of 'completed'
        loans_qs = Loan.objects.filter(status='paid', is_deleted=False)
        
        if portfolio_manager_id:
            loans_qs = loans_qs.filter(borrower__portfolio_manager_id=portfolio_manager_id)
        elif branch_id:
            # Branch ID is a UUID, no need to convert to int
            loans_qs = loans_qs.filter(borrower__branch_id=branch_id)
        
        # Total completed loans
        total_completed = loans_qs.count()
        
        # Completed this month - use updated_at to track when loan was marked as paid
        completed_this_month = loans_qs.filter(
            updated_at__gte=self.current_month_start
        ).count()
        
        # Completed this year
        year_start = self.today.replace(month=1, day=1)
        completed_this_year = loans_qs.filter(
            updated_at__gte=year_start
        ).count()
        
        # Total value of completed loans - calculate amount_paid manually since it's a property
        completed_data = loans_qs.aggregate(
            total_principal=Sum('principal_amount')
        )
        
        # Calculate total collected by iterating (amount_paid is a property)
        total_collected = Decimal('0.00')
        for loan in loans_qs:
            total_collected += (loan.amount_paid or Decimal('0.00'))
        
        return {
            'summary': {
                'total_completed_loans': total_completed,
                'completed_this_month': completed_this_month,
                'completed_this_year': completed_this_year,
                'total_principal_completed': completed_data['total_principal'] or Decimal('0.00'),
                'total_amount_collected': total_collected,
            }
        }
    
    def get_overdue_loans_analytics(self, branch_id=None, portfolio_manager_id=None):
        """Get detailed overdue loans analytics"""
        from loans.repayment_scheduler import RepaymentScheduler
        
        # Exclude soft-deleted and rolled-over loans
        loans_qs = Loan.objects.filter(
            status='active', 
            is_deleted=False,
            is_rolled_over=False
        ).exclude(status='rolled_over')
        
        if portfolio_manager_id:
            loans_qs = loans_qs.filter(borrower__portfolio_manager_id=portfolio_manager_id)
        elif branch_id:
            # Branch ID is a UUID, no need to convert to int
            loans_qs = loans_qs.filter(borrower__branch_id=branch_id)
        
        # Count overdue loans by severity
        total_overdue = 0
        mild_overdue = 0  # 1-30 days
        moderate_overdue = 0  # 31-60 days
        severe_overdue = 0  # 60+ days
        total_overdue_amount = Decimal('0.00')
        
        for loan in loans_qs:
            if RepaymentScheduler.is_loan_in_arrears(loan):
                total_overdue += 1
                arrears_summary = RepaymentScheduler.get_arrears_summary(loan)
                days_overdue = arrears_summary['days_overdue']
                total_overdue_amount += arrears_summary['arrears_amount']
                
                if days_overdue <= 30:
                    mild_overdue += 1
                elif days_overdue <= 60:
                    moderate_overdue += 1
                else:
                    severe_overdue += 1
        
        return {
            'summary': {
                'total_overdue_loans': total_overdue,
                'mild_overdue': mild_overdue,
                'moderate_overdue': moderate_overdue,
                'severe_overdue': severe_overdue,
                'total_overdue_amount': total_overdue_amount,
            }
        }
    
    def get_client_growth_analytics(self, branch_id=None, portfolio_manager_id=None):
        """Get comprehensive client growth analytics"""
        clients_qs = CustomUser.objects.filter(role='borrower', status='active')
        
        if portfolio_manager_id:
            clients_qs = clients_qs.filter(portfolio_manager_id=portfolio_manager_id)
        elif branch_id:
            # Branch ID is a UUID, no need to convert to int
            clients_qs = clients_qs.filter(branch_id=branch_id)
        
        # Total clients
        total_clients = clients_qs.count()
        
        # This week
        week_start = self.today - timedelta(days=self.today.weekday())
        clients_this_week = clients_qs.filter(date_joined__gte=week_start).count()
        
        # This month
        clients_this_month = clients_qs.filter(date_joined__gte=self.current_month_start).count()
        
        # This year
        year_start = self.today.replace(month=1, day=1)
        clients_this_year = clients_qs.filter(date_joined__gte=year_start).count()
        
        # Monthly breakdown for the year
        monthly_breakdown = {}
        for month_num in range(1, 13):
            month_start = year_start.replace(month=month_num)
            if month_num == 12:
                month_end = year_start.replace(year=year_start.year + 1, month=1)
            else:
                month_end = year_start.replace(month=month_num + 1)
            
            count = clients_qs.filter(
                date_joined__gte=month_start,
                date_joined__lt=month_end
            ).count()
            
            month_name = month_start.strftime('%B')
            monthly_breakdown[month_name] = count
        
        # Find best and worst months
        if monthly_breakdown:
            best_month = max(monthly_breakdown, key=monthly_breakdown.get)
            worst_month = min(monthly_breakdown, key=monthly_breakdown.get)
            best_month_count = monthly_breakdown[best_month]
            worst_month_count = monthly_breakdown[worst_month]
        else:
            best_month = None
            worst_month = None
            best_month_count = 0
            worst_month_count = 0
        
        # Weekly breakdown for the last 12 weeks
        weekly_breakdown = {}
        for week_offset in range(12):
            week_start_date = week_start - timedelta(weeks=week_offset)
            week_end_date = week_start_date + timedelta(days=7)
            
            count = clients_qs.filter(
                date_joined__gte=week_start_date,
                date_joined__lt=week_end_date
            ).count()
            
            week_label = f"Week of {week_start_date.strftime('%b %d')}"
            weekly_breakdown[week_label] = count
        
        # Growth rate calculation
        last_month_start = (self.current_month_start - timedelta(days=1)).replace(day=1)
        clients_last_month = clients_qs.filter(
            date_joined__gte=last_month_start,
            date_joined__lt=self.current_month_start
        ).count()
        
        if clients_last_month > 0:
            growth_rate = ((clients_this_month - clients_last_month) / clients_last_month) * 100
        else:
            growth_rate = 100.0 if clients_this_month > 0 else 0.0
        
        return {
            'summary': {
                'total_clients': total_clients,
                'clients_this_week': clients_this_week,
                'clients_this_month': clients_this_month,
                'clients_this_year': clients_this_year,
                'growth_rate': float(growth_rate),
                'best_month': best_month,
                'best_month_count': best_month_count,
                'worst_month': worst_month,
                'worst_month_count': worst_month_count,
            },
            'monthly_breakdown': monthly_breakdown,
            'weekly_breakdown': weekly_breakdown,
        }
    
    def get_missed_payments_summary(self, branch_id=None, portfolio_manager_id=None):
        """Get summary of missed payments by repayment method"""
        from loans.repayment_scheduler import RepaymentScheduler
        
        # Base queryset - exclude soft-deleted and rolled-over loans
        loans_qs = Loan.objects.filter(
            status='active', 
            is_deleted=False,
            is_rolled_over=False
        ).exclude(status='rolled_over')
        
        # Apply portfolio-based filtering
        if portfolio_manager_id:
            loans_qs = loans_qs.filter(borrower__portfolio_manager_id=portfolio_manager_id)
        elif branch_id:
            # Branch ID is a UUID, no need to convert to int
            loans_qs = loans_qs.filter(borrower__branch_id=branch_id)
        
        # Count missed payments by repayment method
        daily_missed = 0
        weekly_missed = 0
        monthly_missed = 0
        
        for loan in loans_qs:
            missed_periods = RepaymentScheduler.get_missed_payment_periods(loan)
            if missed_periods:
                repayment_method = RepaymentScheduler.get_repayment_method(loan)
                if repayment_method == 'daily':
                    daily_missed += 1
                elif repayment_method == 'weekly':
                    weekly_missed += 1
                else:  # monthly
                    monthly_missed += 1
        
        return {
            'summary': {
                'daily_missed': daily_missed,
                'weekly_missed': weekly_missed,
                'monthly_missed': monthly_missed,
                'total_missed': daily_missed + weekly_missed + monthly_missed
            }
        }
    
    def _safe_call(self, method, *args, default=None, **kwargs):
        """Call a method and return default on any exception, logging the error."""
        import logging
        import traceback
        logger = logging.getLogger(__name__)
        try:
            return method(*args, **kwargs)
        except Exception as e:
            logger.error(f"[SimpleReportsService] {method.__name__} FAILED: {e}\n{traceback.format_exc()}")
            return default

    def generate_dashboard_data(self, branch_id=None, portfolio_manager_id=None):
        """Generate dashboard data — each section is independent so one failure doesn't zero everything."""
        import logging
        import uuid as _uuid
        logger = logging.getLogger(__name__)
        self._refresh_dates()

        # Validate branch_id is a proper UUID
        if branch_id:
            try:
                _uuid.UUID(str(branch_id))
            except (ValueError, AttributeError):
                logger.warning(f"[SimpleReportsService] Invalid branch_id '{branch_id}' — ignoring filter")
                branch_id = None

        # Defaults for each section
        default_summary = {
            'total_active_loans': 0, 'total_portfolio_value': Decimal('0.00'),
            'total_outstanding': Decimal('0.00'), 'collection_rate': 0.0,
            'loans_due_today': 0, 'overdue_loans': 0,
        }
        default_loans_due = {
            'loans': [], 'summary': {'total_loans_due': 0, 'total_amount_due': Decimal('0.00')},
            'categorized': {'today': []}, 'daily_count': 0, 'weekly_count': 0, 'monthly_count': 0,
        }
        default_delinquent = {
            'loans': [], 'daily_critical_count': 0,
            'summary': {'mild_delinquent_count': 0, 'moderate_delinquent_count': 0,
                        'severe_delinquent_count': 0, 'total_delinquent': 0},
        }
        default_missed = {'summary': {'daily_missed': 0, 'weekly_missed': 0, 'monthly_missed': 0, 'total_missed': 0}}
        default_fees = {'summary': {'total_processing_fees': Decimal('0.00'), 'total_loans_processed': 0}}
        default_interest = {'summary': {'total_interest_income': Decimal('0.00'), 'total_loans': 0}}
        default_reg = {'summary': {'total_registration_income': Decimal('0.00'), 'total_registrations': 0}}
        default_requests = {'summary': {'pending_requests': 0, 'in_progress_requests': 0, 'resolved_requests': 0}}
        default_completed = {'summary': {'total_completed_loans': 0, 'completed_this_month': 0,
                                         'completed_this_year': 0, 'total_principal_completed': Decimal('0.00'),
                                         'total_amount_collected': Decimal('0.00')}}
        default_overdue = {'summary': {'total_overdue_loans': 0, 'mild_overdue': 0, 'moderate_overdue': 0,
                                       'severe_overdue': 0, 'total_overdue_amount': Decimal('0.00')}}
        default_growth = {
            'summary': {'total_clients': 0, 'clients_this_week': 0, 'clients_this_month': 0,
                        'clients_this_year': 0, 'growth_rate': 0.0, 'best_month': None,
                        'best_month_count': 0, 'worst_month': None, 'worst_month_count': 0},
            'monthly_breakdown': {}, 'weekly_breakdown': {},
        }

        return {
            'summary_metrics': self._safe_call(self.get_summary_metrics, branch_id, portfolio_manager_id, default=default_summary),
            'loans_due_today': self._safe_call(self.get_loans_due_today, branch_id, portfolio_manager_id, default=default_loans_due),
            'delinquent_loans': self._safe_call(self.get_delinquent_loans, branch_id, portfolio_manager_id, default=default_delinquent),
            'missed_payments': self._safe_call(self.get_missed_payments_summary, branch_id, default=default_missed),
            'processing_fees_current_month': self._safe_call(self.get_processing_fees_current_month, branch_id, portfolio_manager_id, default=default_fees),
            'interest_income_current_month': self._safe_call(self.get_interest_income_current_month, branch_id, portfolio_manager_id, default=default_interest),
            'registration_fees_current_month': self._safe_call(self.get_registration_fees_current_month, branch_id, portfolio_manager_id, default=default_reg),
            'customer_requests_current_month': self._safe_call(self.get_customer_requests_current_month, branch_id, portfolio_manager_id, default=default_requests),
            'completed_loans': self._safe_call(self.get_completed_loans_analytics, branch_id, portfolio_manager_id, default=default_completed),
            'overdue_loans': self._safe_call(self.get_overdue_loans_analytics, branch_id, portfolio_manager_id, default=default_overdue),
            'client_growth': self._safe_call(self.get_client_growth_analytics, branch_id, portfolio_manager_id, default=default_growth),
            'generated_at': timezone.now().isoformat(),
        }
    
    # Placeholder methods for compatibility with existing views
    def get_processing_fees_report(self, **kwargs):
        """
        Get processing fees report with detailed data and filtering
        Requirements: 6.1, 6.2, 6.3, 6.4, 6.5
        """
        from django.utils import timezone
        from django.db.models import Sum
        from decimal import Decimal
        
        branch_id = kwargs.get('branch_id')
        start_date = kwargs.get('start_date')
        end_date = kwargs.get('end_date')
        period = kwargs.get('period', 'month')
        loan_product_id = kwargs.get('loan_product_id')
        
        # Set date range
        if not start_date:
            if period == 'today':
                start_date = timezone.now().date()
                end_date = timezone.now().date()
            elif period == 'week':
                start_date = timezone.now().date() - timedelta(days=7)
                end_date = timezone.now().date()
            elif period == 'month':
                start_date = timezone.now().date() - timedelta(days=30)
                end_date = timezone.now().date()
            elif period == 'quarter':
                start_date = timezone.now().date() - timedelta(days=90)
                end_date = timezone.now().date()
            else:
                start_date = timezone.now().date() - timedelta(days=30)
                end_date = timezone.now().date()
        
        if not end_date:
            end_date = timezone.now().date()
        
        # Get loans with processing fees in the date range
        # Requirement 6.2: Filter by date range (disbursement date)
        # Exclude soft-deleted and rolled-over loans
        loans_qs = Loan.objects.filter(
            is_deleted=False,
            is_rolled_over=False,
            disbursement_date__gte=start_date,
            disbursement_date__lte=end_date
        ).select_related('borrower', 'application__loan_product')
        
        # Requirement 6.1: Filter by branch
        if branch_id:
            loans_qs = loans_qs.filter(borrower__branch_id=branch_id)
        
        # Requirement 6.3: Filter by loan product
        if loan_product_id:
            loans_qs = loans_qs.filter(application__loan_product_id=loan_product_id)
        
        # Requirement 6.4: Calculate total processing fees using aggregation
        total_processing_fees = loans_qs.aggregate(
            total=Sum('processing_fee')
        )['total'] or Decimal('0.00')
        
        # Build detailed loan data
        processing_fees = []
        for loan in loans_qs:
            processing_fee = loan.processing_fee or Decimal('0.00')
            
            if processing_fee > 0:
                # Calculate fee percentage based on principal amount
                fee_percentage = (processing_fee / loan.principal_amount * 100) if loan.principal_amount > 0 else Decimal('0.00')
                
                # Get product name
                product_name = 'Standard Loan'
                if loan.application and loan.application.loan_product:
                    product_name = loan.application.loan_product.name
                
                fee_data = {
                    'id': str(loan.id),
                    'loan_number': loan.loan_number or f'LOAN-{loan.id}',
                    'borrower_name': f"{loan.borrower.first_name} {loan.borrower.last_name}",
                    'borrower_phone': loan.borrower.phone_number or '',
                    'product_name': product_name,
                    'principal_amount': loan.principal_amount or Decimal('0.00'),
                    'processing_fee': processing_fee,
                    'fee_rate': fee_percentage,
                    'disbursement_date': loan.disbursement_date.isoformat() if loan.disbursement_date else None,
                    'status': loan.status,
                    'fee_status': 'paid' if loan.status == 'active' else 'pending',
                }
                
                processing_fees.append(fee_data)
        
        # Calculate summary statistics
        total_loans = len(processing_fees)
        average_fee = total_processing_fees / total_loans if total_loans > 0 else Decimal('0.00')
        
        # Requirement 6.5: Calculate period comparison (current vs previous period)
        current_duration = (end_date - start_date).days
        previous_end = start_date - timedelta(days=1)
        previous_start = previous_end - timedelta(days=current_duration)
        
        # Get previous period fees
        previous_loans_qs = Loan.objects.filter(
            is_deleted=False,
            is_rolled_over=False,
            disbursement_date__gte=previous_start,
            disbursement_date__lte=previous_end
        )
        
        if branch_id:
            previous_loans_qs = previous_loans_qs.filter(borrower__branch_id=branch_id)
        
        if loan_product_id:
            previous_loans_qs = previous_loans_qs.filter(application__loan_product_id=loan_product_id)
        
        previous_period_fees = previous_loans_qs.aggregate(
            total=Sum('processing_fee')
        )['total'] or Decimal('0.00')
        
        # Calculate period difference and growth rate
        period_difference = total_processing_fees - previous_period_fees
        growth_rate = Decimal('0.00')
        if previous_period_fees > 0:
            growth_rate = (period_difference / previous_period_fees) * 100
        
        # Calculate monthly breakdown
        monthly_breakdown = {}
        for fee in processing_fees:
            if fee['disbursement_date']:
                month_key = fee['disbursement_date'][:7]  # Extract 'YYYY-MM'
                if month_key not in monthly_breakdown:
                    monthly_breakdown[month_key] = {'count': 0, 'amount': Decimal('0.00')}
                monthly_breakdown[month_key]['count'] += 1
                monthly_breakdown[month_key]['amount'] += fee['processing_fee']
        
        # Calculate additional metrics
        fees_collected = sum(fee['processing_fee'] for fee in processing_fees if fee['fee_status'] == 'paid')
        fees_pending = total_processing_fees - fees_collected
        collection_rate = (fees_collected / total_processing_fees * 100) if total_processing_fees > 0 else Decimal('0.00')
        highest_fee = max([fee['processing_fee'] for fee in processing_fees], default=Decimal('0.00'))
        
        # Find most active product
        product_counts = {}
        for fee in processing_fees:
            product = fee['product_name']
            product_counts[product] = product_counts.get(product, 0) + 1
        top_product = max(product_counts.items(), key=lambda x: x[1])[0] if product_counts else 'N/A'
        
        return {
            'summary': {
                'total_processing_fees': total_processing_fees,
                'total_loans_processed': total_loans,
                'average_fee': average_fee,
                'average_processing_fee': average_fee,  # Alias for template compatibility
                'fees_collected': fees_collected,
                'fees_pending': fees_pending,
                'collection_rate': collection_rate,
                'growth_rate': growth_rate,
                'highest_fee': highest_fee,
                'top_product': top_product,
                'current_period_fees': total_processing_fees,
                'previous_period_fees': previous_period_fees,
                'period_difference': period_difference,
                'fee_percentage': Decimal('0.00'),  # Placeholder
            },
            'monthly_breakdown': monthly_breakdown,
            'loans': processing_fees,
            'fees': processing_fees,  # Alias for template compatibility
            'chart_data': {
                'monthly_trend': {
                    'labels': list(monthly_breakdown.keys()),
                    'amounts': [float(monthly_breakdown[key]['amount']) for key in monthly_breakdown.keys()]
                },
                'product_breakdown': {
                    'labels': ['Processing Fees'],
                    'amounts': [float(total_processing_fees)]
                }
            }
        }
    
    def get_processing_fees_trend_analysis(self, **kwargs):
        """Placeholder for processing fees trend analysis"""
        return {
            'monthly_trends': [],
            'summary': {}
        }
    
    def get_interest_income_report(self, **kwargs):
        """Get interest income report with detailed data"""
        from django.utils import timezone
        from decimal import Decimal
        
        branch_id = kwargs.get('branch_id')
        month = kwargs.get('month')
        year = kwargs.get('year')
        
        # Set date range
        if not year:
            year = timezone.now().year
        if not month:
            month = timezone.now().month
        
        # Get loans with interest in the specified month/year - exclude soft-deleted
        loans_qs = Loan.objects.filter(
            is_deleted=False,
            created_at__year=year,
            created_at__month=month
        )
        
        if branch_id:
            loans_qs = loans_qs.filter(borrower__branch_id=branch_id)
        
        # Calculate interest income using actual interest_amount field
        interest_income = []
        total_interest_income = Decimal('0.00')
        
        for loan in loans_qs:
            # Use the actual interest amount from the loan record
            interest_amt = loan.interest_amount or Decimal('0.00')
            
            if interest_amt > 0:
                # Calculate interest rate from actual amounts
                if loan.principal_amount and loan.principal_amount > 0:
                    # Calculate the effective interest rate
                    interest_rate = (interest_amt / loan.principal_amount * 100)
                else:
                    interest_rate = Decimal('0.00')
                
                income_data = {
                    'id': str(loan.id),  # Convert UUID to string for JSON serialization
                    'loan_number': loan.loan_number or f'LOAN-{loan.id}',
                    'borrower_name': f"{loan.borrower.first_name} {loan.borrower.last_name}",
                    'loan_amount': loan.principal_amount or Decimal('0.00'),
                    'interest_rate': interest_rate,
                    'monthly_interest': interest_amt,  # This is the total interest, not monthly
                    'total_interest': interest_amt,
                    'created_date': loan.created_at.date().isoformat() if loan.created_at else None,
                    'status': loan.status,
                }
                
                interest_income.append(income_data)
                total_interest_income += interest_amt
        
        # Calculate summary statistics
        total_loans = len(interest_income)
        average_interest = total_interest_income / total_loans if total_loans > 0 else Decimal('0.00')
        
        # Calculate monthly breakdown for the year using actual interest amounts
        monthly_breakdown = {}
        for month_num in range(1, 13):
            month_loans = Loan.objects.filter(
                is_deleted=False,
                created_at__year=year,
                created_at__month=month_num
            )
            if branch_id:
                month_loans = month_loans.filter(borrower__branch_id=branch_id)
            
            # Use actual interest_amount field
            month_interest = month_loans.aggregate(
                total=Sum('interest_amount')
            )['total'] or Decimal('0.00')
            
            monthly_breakdown[f"{year}-{month_num:02d}"] = float(month_interest)
        
        # Calculate rate distribution
        rate_distribution = {
            '15%': len([loan for loan in interest_income if loan['interest_rate'] == 15.0]),
            '20%': len([loan for loan in interest_income if loan['interest_rate'] == 20.0]),
            '25%': len([loan for loan in interest_income if loan['interest_rate'] == 25.0]),
        }
        
        return {
            'summary': {
                'total_interest_income': total_interest_income,
                'total_loans': total_loans,
                'average_interest': average_interest,
            },
            'monthly_breakdown': monthly_breakdown,
            'rate_distribution': rate_distribution,
            'loans': interest_income,
            'chart_data': {
                'monthly_trend': {
                    'labels': list(monthly_breakdown.keys()),
                    'amounts': list(monthly_breakdown.values())
                },
                'rate_distribution': {
                    'labels': list(rate_distribution.keys()),
                    'counts': list(rate_distribution.values())
                },
                'product_performance': {
                    'labels': ['Interest Income'],
                    'amounts': [float(total_interest_income)]
                }
            }
        }
    
    def get_interest_income_trend_analysis(self, **kwargs):
        """Placeholder for interest income trend analysis"""
        return {
            'monthly_trends': [],
            'summary': {}
        }
    
    def get_all_time_registration_fees_report(self, **kwargs):
        """Get all-time registration fees focusing on client registration fees"""
        from django.db.models import Sum, Count, Q
        from django.db.models.functions import Coalesce
        from django.utils import timezone
        from users.models import CustomUser
        from decimal import Decimal
        
        branch_id = kwargs.get('branch_id')
        start_date = kwargs.get('start_date')
        end_date = kwargs.get('end_date')
        payment_status = kwargs.get('payment_status')
        
        # Initialize totals
        total_income = Decimal('0.00')
        total_count = 0
        fees_list = []
        
        try:
            # Focus on CustomUser registration fees (the ones added during client registration)
            user_reg_fees = CustomUser.objects.filter(
                role='borrower',
                registration_fee_amount__gt=0
            ).exclude(registration_fee_amount__isnull=True)
            
            # Apply branch filtering
            if branch_id:
                user_reg_fees = user_reg_fees.filter(branch_id=branch_id)
            
            # Apply date filtering
            if start_date:
                user_reg_fees = user_reg_fees.filter(created_at__gte=start_date)
            if end_date:
                user_reg_fees = user_reg_fees.filter(created_at__lte=end_date)
            
            # Apply payment status filtering
            if payment_status == 'paid':
                user_reg_fees = user_reg_fees.filter(registration_fee_paid=True)
            elif payment_status == 'pending':
                user_reg_fees = user_reg_fees.filter(Q(registration_fee_paid=False) | Q(registration_fee_paid__isnull=True))
            
            # Get paid registration fees
            paid_user_fees = user_reg_fees.filter(registration_fee_paid=True)
            
            # Calculate totals from paid CustomUser registration fees
            user_data = paid_user_fees.aggregate(
                total_income=Coalesce(Sum('registration_fee_amount'), Decimal('0.00')),
                total_count=Count('id')
            )
            
            total_income = user_data['total_income'] or Decimal('0.00')
            total_count = user_data['total_count'] or 0
            
            # Get pending fees
            pending_user_fees = user_reg_fees.filter(Q(registration_fee_paid=False) | Q(registration_fee_paid__isnull=True))
            pending_data = pending_user_fees.aggregate(
                total_pending=Coalesce(Sum('registration_fee_amount'), Decimal('0.00')),
                pending_count=Count('id')
            )
            
            total_pending = pending_data['total_pending'] or Decimal('0.00')
            pending_count = pending_data['pending_count'] or 0
            
            # Add to fees list - all registration fees
            for user in user_reg_fees.order_by('-registration_fee_payment_date', '-created_at'):
                payment_status_display = 'paid' if user.registration_fee_paid else 'pending'
                
                fees_list.append({
                    'id': str(user.id),
                    'client_id': str(user.id),
                    'client_name': user.get_full_name(),
                    'client_phone': user.phone_number,
                    'client_id_number': getattr(user, 'id_number', 'N/A'),
                    'customer_name': user.get_full_name(),
                    'customer_phone': user.phone_number,
                    'registration_date': user.created_at,
                    'product_type': 'Client Registration',
                    'fee_name': 'Client Registration Fee',
                    'fee_amount': user.registration_fee_amount,
                    'amount': user.registration_fee_amount,
                    'discount_amount': 0,
                    'payment_method': user.get_registration_fee_payment_method_display() if user.registration_fee_payment_method else 'Not Specified',
                    'payment_date': user.registration_fee_payment_date or user.created_at if user.registration_fee_paid else None,
                    'payment_status': payment_status_display,
                    'notes': f'Registered on {user.created_at.strftime("%Y-%m-%d")}'
                })
        
        except Exception as e:
            # Log error but continue
            import logging
            logger = logging.getLogger(__name__)
            logger.warning(f"Error processing registration fee data: {e}")
        
        # Calculate average fee
        average_fee = total_income / total_count if total_count > 0 else Decimal('0.00')
        
        # Calculate collection rate
        total_expected = total_income + total_pending
        collection_rate = (total_income / total_expected * 100) if total_expected > 0 else Decimal('0.00')
        collection_efficiency = collection_rate
        
        return {
            'report_type': 'registration_fees',
            'period': {
                'start_date': start_date,
                'end_date': end_date,
                'period_display': 'Custom Period' if start_date or end_date else 'All Time'
            },
            'summary': {
                'total_registration_income': total_income,
                'total_registrations': total_count + pending_count,
                'average_registration_fee': average_fee,
                'collection_rate': collection_rate,
                'collection_efficiency': collection_efficiency,
                'paid_registrations': total_count,
                'pending_payments': total_pending,
                'pending_count': pending_count,
                'fees_collected': total_income,
                'fees_outstanding': total_pending,
                'monthly_growth': Decimal('0.00'),
                'monthly_growth_rate': Decimal('0.00'),
            },
            'fees': fees_list,
            'registrations': fees_list,  # Alias for template compatibility
        }
    
    def get_registration_fees_report(self, **kwargs):
        """
        Get registration fees report with comprehensive metrics
        Requirements: 8.1, 8.2, 8.3, 8.4, 8.5
        """
        from django.db.models import Sum, Count, Q, Avg
        from django.db.models.functions import Coalesce
        from django.utils import timezone
        from users.models import CustomUser
        from decimal import Decimal
        
        branch_id = kwargs.get('branch_id')
        start_date = kwargs.get('start_date')
        end_date = kwargs.get('end_date')
        payment_status = kwargs.get('payment_status')
        
        # Initialize totals
        total_income = Decimal('0.00')
        total_count = 0
        fees_list = []
        
        try:
            # Focus on CustomUser registration fees (the ones added during client registration)
            user_reg_fees = CustomUser.objects.filter(
                role='borrower',
                registration_fee_amount__gt=0
            ).exclude(registration_fee_amount__isnull=True)
            
            # Requirement 8.1: Apply branch filtering
            if branch_id:
                user_reg_fees = user_reg_fees.filter(branch_id=branch_id)
            
            # Apply date filtering (registration date range)
            if start_date:
                user_reg_fees = user_reg_fees.filter(created_at__gte=start_date)
            if end_date:
                user_reg_fees = user_reg_fees.filter(created_at__lte=end_date)
            
            # Apply payment status filtering
            if payment_status == 'paid':
                user_reg_fees = user_reg_fees.filter(registration_fee_paid=True)
            elif payment_status == 'pending':
                user_reg_fees = user_reg_fees.filter(Q(registration_fee_paid=False) | Q(registration_fee_paid__isnull=True))
            
            # Get paid registration fees
            paid_user_fees = user_reg_fees.filter(registration_fee_paid=True)
            
            # Calculate totals from paid CustomUser registration fees
            user_data = paid_user_fees.aggregate(
                total_income=Coalesce(Sum('registration_fee_amount'), Decimal('0.00')),
                total_count=Count('id')
            )
            
            total_income = user_data['total_income'] or Decimal('0.00')
            total_count = user_data['total_count'] or 0
            
            # Get pending fees
            pending_user_fees = user_reg_fees.filter(Q(registration_fee_paid=False) | Q(registration_fee_paid__isnull=True))
            pending_data = pending_user_fees.aggregate(
                total_pending=Coalesce(Sum('registration_fee_amount'), Decimal('0.00')),
                pending_count=Count('id')
            )
            
            total_pending = pending_data['total_pending'] or Decimal('0.00')
            pending_count = pending_data['pending_count'] or 0
            
            # Requirement 8.3: Calculate highest single fee (maximum value)
            highest_single_fee = Decimal('0.00')
            if paid_user_fees.exists():
                highest_fee_data = paid_user_fees.aggregate(
                    max_fee=Coalesce(Max('registration_fee_amount'), Decimal('0.00'))
                )
                highest_single_fee = highest_fee_data['max_fee'] or Decimal('0.00')
            
            # Requirement 8.4: Calculate average days to pay
            average_days_to_pay = Decimal('0.00')
            days_to_pay_list = []
            for user in paid_user_fees:
                if user.registration_fee_payment_date and user.created_at:
                    days_diff = (user.registration_fee_payment_date.date() - user.created_at.date()).days
                    if days_diff >= 0:  # Only count non-negative values
                        days_to_pay_list.append(days_diff)
            
            if days_to_pay_list:
                average_days_to_pay = Decimal(str(sum(days_to_pay_list) / len(days_to_pay_list)))
            
            # Requirement 8.2 & 8.5: Calculate growth vs last period (period comparison)
            current_period_total = total_income
            previous_period_total = Decimal('0.00')
            growth_vs_last_period = Decimal('0.00')
            growth_rate = Decimal('0.00')
            
            if start_date and end_date:
                # Calculate previous period with same duration
                current_duration = (end_date - start_date).days
                previous_end = start_date - timedelta(days=1)
                previous_start = previous_end - timedelta(days=current_duration)
                
                # Get previous period fees
                previous_fees = CustomUser.objects.filter(
                    role='borrower',
                    registration_fee_amount__gt=0,
                    registration_fee_paid=True,
                    created_at__gte=previous_start,
                    created_at__lte=previous_end
                ).exclude(registration_fee_amount__isnull=True)
                
                if branch_id:
                    previous_fees = previous_fees.filter(branch_id=branch_id)
                
                previous_data = previous_fees.aggregate(
                    total=Coalesce(Sum('registration_fee_amount'), Decimal('0.00'))
                )
                previous_period_total = previous_data['total'] or Decimal('0.00')
                
                # Requirement 8.5: Calculate arithmetic difference
                growth_vs_last_period = current_period_total - previous_period_total
                
                # Calculate growth rate percentage
                if previous_period_total > 0:
                    growth_rate = (growth_vs_last_period / previous_period_total) * 100
                elif current_period_total > 0:
                    growth_rate = Decimal('100.00')  # 100% growth from zero
            
            # Add to fees list - all registration fees
            for user in user_reg_fees.order_by('-registration_fee_payment_date', '-created_at'):
                payment_status_display = 'paid' if user.registration_fee_paid else 'pending'
                
                fees_list.append({
                    'id': str(user.id),
                    'client_id': str(user.id),
                    'client_name': user.get_full_name(),
                    'client_phone': user.phone_number,
                    'client_id_number': getattr(user, 'id_number', 'N/A'),
                    'customer_name': user.get_full_name(),
                    'customer_phone': user.phone_number,
                    'registration_date': user.created_at,
                    'product_type': 'Client Registration',
                    'fee_name': 'Client Registration Fee',
                    'fee_amount': user.registration_fee_amount,
                    'amount': user.registration_fee_amount,
                    'discount_amount': 0,
                    'payment_method': user.get_registration_fee_payment_method_display() if user.registration_fee_payment_method else 'Not Specified',
                    'payment_date': user.registration_fee_payment_date or user.created_at if user.registration_fee_paid else None,
                    'payment_status': payment_status_display,
                    'notes': f'Registered on {user.created_at.strftime("%Y-%m-%d")}'
                })
        
        except Exception as e:
            # Log error but continue
            import logging
            logger = logging.getLogger(__name__)
            logger.warning(f"Error processing registration fee data: {e}")
        
        # Calculate average fee
        average_fee = total_income / total_count if total_count > 0 else Decimal('0.00')
        
        # Calculate collection rate
        total_expected = total_income + total_pending
        collection_rate = (total_income / total_expected * 100) if total_expected > 0 else Decimal('0.00')
        collection_efficiency = collection_rate
        
        return {
            'report_type': 'registration_fees',
            'period': {
                'start_date': start_date,
                'end_date': end_date,
                'period_display': 'Custom Period' if start_date or end_date else 'All Time'
            },
            'summary': {
                'total_registration_income': total_income,
                'total_registrations': total_count + pending_count,
                'average_registration_fee': average_fee,
                'collection_rate': collection_rate,
                'collection_efficiency': collection_efficiency,
                'paid_registrations': total_count,
                'pending_payments': total_pending,
                'pending_count': pending_count,
                'fees_collected': total_income,
                'fees_outstanding': total_pending,
                # New metrics for Requirements 8.2, 8.3, 8.4, 8.5
                'highest_single_fee': highest_single_fee,
                'average_days_to_pay': average_days_to_pay,
                'current_period_total': current_period_total,
                'previous_period_total': previous_period_total,
                'growth_vs_last_period': growth_vs_last_period,
                'growth_rate': growth_rate,
                'monthly_growth': growth_vs_last_period,  # Alias for compatibility
                'monthly_growth_rate': growth_rate,  # Alias for compatibility
            },
            'fees': fees_list,
            'registrations': fees_list,  # Alias for template compatibility
        }
    
    def get_loans_in_arrears_report(self, **kwargs):
        """
        Get loans in arrears report with detailed data using repayment method-aware calculation.
        
        Implements proper arrears definition:
        - due_date < current_date AND outstanding_amount > 0
        - Excludes fully paid loans (outstanding_amount = 0)
        - Excludes rolled-over loans
        - Excludes soft-deleted loans
        
        Validates: Requirements 14.1, 14.2, 14.3, 14.4, 14.5
        """
        from django.utils import timezone
        from django.db.models import Sum, Count, Avg, Q, F
        from decimal import Decimal
        from datetime import datetime
        from loans.repayment_scheduler import RepaymentScheduler
        from reports.filter_service import ReportFilterService
        
        branch_id = kwargs.get('branch_id')
        arrears_filter = kwargs.get('arrears_amount_filter')
        sort_by = kwargs.get('sort_by', 'amount_desc')
        start_date = kwargs.get('start_date')
        end_date = kwargs.get('end_date')
        
        # Get all loans - we'll apply proper arrears filtering
        # Requirement 14.4: Exclude rolled-over loans
        # Requirement 14.5: Exclude soft-deleted loans
        loans_qs = Loan.objects.all()
        
        # Use filter service to exclude rolled-over and soft-deleted loans
        loans_qs = ReportFilterService.apply_loan_status_filter(
            loans_qs,
            exclude_rolled_over=True,
            exclude_deleted=True
        )
        
        # Requirement 14.3: Exclude fully paid loans
        # Paid loans have outstanding_amount = 0, so they won't appear in arrears
        # But we also explicitly exclude loans with status='paid' for clarity
        loans_qs = loans_qs.exclude(status='paid')
        
        if branch_id:
            loans_qs = loans_qs.filter(borrower__branch_id=branch_id)
        
        # Apply date filtering
        if start_date:
            try:
                start_date_obj = datetime.strptime(start_date, '%Y-%m-%d').date()
                loans_qs = loans_qs.filter(disbursement_date__gte=start_date_obj)
            except ValueError:
                pass
        
        if end_date:
            try:
                end_date_obj = datetime.strptime(end_date, '%Y-%m-%d').date()
                loans_qs = loans_qs.filter(disbursement_date__lte=end_date_obj)
            except ValueError:
                pass
        
        # Use the new repayment scheduler to get loans in arrears
        # Requirement 14.1: Arrears definition - due_date < current_date AND outstanding_amount > 0
        # Requirement 14.2: Days in arrears calculation - (current_date - due_date).days
        # Requirement 14.3: Fully paid exclusion - outstanding_amount = 0 excluded
        # The RepaymentScheduler.get_loans_in_arrears() method implements these requirements
        loans_in_arrears_data = RepaymentScheduler.get_loans_in_arrears(loans_qs, branch_id)
        
        loans_in_arrears = []
        total_arrears_amount = Decimal('0.00')
        
        for loan_data in loans_in_arrears_data:
            loan = loan_data['loan']
            arrears_summary = loan_data['arrears_summary']
            
            # Get last payment
            try:
                last_payment = Repayment.objects.filter(loan=loan).order_by('-payment_date').first()
            except:
                last_payment = None
            
            # Determine severity level based on repayment method and days overdue
            days_overdue = arrears_summary['days_overdue']
            arrears_amount = arrears_summary['arrears_amount']
            repayment_method = arrears_summary['repayment_method']
            
            # Adjust severity thresholds based on repayment method
            if repayment_method == 'daily':
                if days_overdue > 7:
                    severity_level = 'critical'
                    severity_label = 'Critical'
                elif days_overdue > 3:
                    severity_level = 'high'
                    severity_label = 'High Risk'
                elif days_overdue > 1:
                    severity_level = 'medium'
                    severity_label = 'Medium Risk'
                else:
                    severity_level = 'low'
                    severity_label = 'Low Risk'
            elif repayment_method == 'weekly':
                if days_overdue > 21:
                    severity_level = 'critical'
                    severity_label = 'Critical'
                elif days_overdue > 14:
                    severity_level = 'high'
                    severity_label = 'High Risk'
                elif days_overdue > 7:
                    severity_level = 'medium'
                    severity_label = 'Medium Risk'
                else:
                    severity_level = 'low'
                    severity_label = 'Low Risk'
            else:  # monthly
                if days_overdue > 90:
                    severity_level = 'critical'
                    severity_label = 'Critical'
                elif days_overdue > 60:
                    severity_level = 'high'
                    severity_label = 'High Risk'
                elif days_overdue > 30:
                    severity_level = 'medium'
                    severity_label = 'Medium Risk'
                else:
                    severity_level = 'low'
                    severity_label = 'Low Risk'
            
            loan_info = {
                'id': str(loan.id),  # Convert UUID to string for JSON serialization
                'loan_number': loan.loan_number or f'LOAN-{loan.id}',
                'borrower_name': f"{loan.borrower.first_name} {loan.borrower.last_name}",
                'borrower_phone': loan.borrower.phone_number,
                'due_date': loan.due_date.isoformat() if hasattr(loan.due_date, 'isoformat') else str(loan.due_date) if loan.due_date else None,
                'arrears_amount': float(arrears_amount),
                'days_overdue': days_overdue,
                'severity_level': severity_level,
                'severity_label': severity_label,
                'last_payment_date': last_payment.payment_date.isoformat() if last_payment and hasattr(last_payment.payment_date, 'isoformat') else (str(last_payment.payment_date) if last_payment and last_payment.payment_date else None),
                'last_payment_amount': float(last_payment.amount) if last_payment else None,
                'loan_product': loan.application.loan_product.name if loan.application and loan.application.loan_product else 'Unknown',
                'product_type': loan.application.loan_product.product_type if loan.application and loan.application.loan_product else 'unknown',
                'repayment_method': repayment_method,
                'expected_payment_amount': float(arrears_summary['expected_payment_amount']),
                'payment_frequency': arrears_summary['payment_frequency'],
                'missed_periods_count': arrears_summary['missed_periods_count'],
                'next_payment_due_date': RepaymentScheduler.get_next_payment_due_date(loan),
                'payment_status': RepaymentScheduler.get_payment_status(loan),
                'principal_amount': float(loan.principal_amount),
                'total_amount': float(loan.total_amount),
                'amount_paid': float(loan.amount_paid),
                'missed_periods': arrears_summary['missed_periods']
            }
            
            loans_in_arrears.append(loan_info)
            total_arrears_amount += arrears_amount
        
        # Apply arrears amount filter
        if arrears_filter:
            if arrears_filter == 'under_10k':
                loans_in_arrears = [loan for loan in loans_in_arrears if loan['arrears_amount'] < 10000]
            elif arrears_filter == '10k_50k':
                loans_in_arrears = [loan for loan in loans_in_arrears if 10000 <= loan['arrears_amount'] < 50000]
            elif arrears_filter == 'over_50k':
                loans_in_arrears = [loan for loan in loans_in_arrears if loan['arrears_amount'] >= 50000]
        
        # Apply sorting
        if sort_by == 'amount_desc':
            loans_in_arrears.sort(key=lambda x: x['arrears_amount'], reverse=True)
        elif sort_by == 'amount_asc':
            loans_in_arrears.sort(key=lambda x: x['arrears_amount'])
        elif sort_by == 'days_overdue_desc':
            loans_in_arrears.sort(key=lambda x: x['days_overdue'], reverse=True)
        elif sort_by == 'days_overdue_asc':
            loans_in_arrears.sort(key=lambda x: x['days_overdue'])
        elif sort_by == 'borrower_name':
            loans_in_arrears.sort(key=lambda x: x['borrower_name'])
        elif sort_by == 'due_date_desc':
            loans_in_arrears.sort(key=lambda x: x['due_date'], reverse=True)
        elif sort_by == 'due_date_asc':
            loans_in_arrears.sort(key=lambda x: x['due_date'])
        
        # Calculate summary statistics
        total_loans = len(loans_in_arrears)
        average_arrears = total_arrears_amount / total_loans if total_loans > 0 else Decimal('0.00')
        
        # Calculate severity breakdown
        severity_breakdown = {
            'low_risk_count': len([loan for loan in loans_in_arrears if loan['severity_level'] == 'low']),
            'medium_risk_count': len([loan for loan in loans_in_arrears if loan['severity_level'] == 'medium']),
            'high_risk_count': len([loan for loan in loans_in_arrears if loan['severity_level'] == 'high']),
            'critical_count': len([loan for loan in loans_in_arrears if loan['severity_level'] == 'critical']),
        }
        
        return {
            'summary': {
                'total_loans_in_arrears': total_loans,
                'total_arrears_amount': total_arrears_amount,
                'average_arrears_amount': average_arrears,
                'recovery_rate': 0,  # Placeholder
            },
            'severity_breakdown': severity_breakdown,
            'loans': loans_in_arrears,
            'recovery_trend': {
                'labels': [],
                'data': []
            }
        }
    
    def get_loans_due_report(self, **kwargs):
        """Get loans due report with detailed data using repayment method-aware calculation"""
        from django.utils import timezone
        from decimal import Decimal
        from datetime import timedelta
        from loans.repayment_scheduler import RepaymentScheduler
        
        branch_id = kwargs.get('branch_id')
        start_date = kwargs.get('start_date')
        end_date = kwargs.get('end_date')
        today_only = kwargs.get('today_only', False)
        
        # Set date range
        if today_only:
            start_date = timezone.now().date()
            end_date = timezone.now().date()
        elif not start_date:
            start_date = timezone.now().date()
        if not end_date:
            end_date = start_date + timedelta(days=30)
        
        # Get all active loans (we'll filter for due dates using the new scheduler) - exclude soft-deleted
        loans_qs = Loan.objects.filter(
            is_deleted=False,
            status__in=['active', 'rolled_over', 'defaulted']
        )
        
        if branch_id:
            loans_qs = loans_qs.filter(borrower__branch_id=branch_id)
        
        # Calculate loans due using repayment scheduler
        loans_due = []
        total_due_amount = Decimal('0.00')
        
        for loan in loans_qs:
            # Get next payment due date using repayment scheduler
            next_due_date = RepaymentScheduler.get_next_payment_due_date(loan)
            expected_payment_amount = RepaymentScheduler.calculate_expected_payment_amount(loan)
            repayment_method = RepaymentScheduler.get_repayment_method(loan)
            
            # Check if this loan has a payment due in our date range
            if start_date <= next_due_date <= end_date:
                days_until_due = (next_due_date - timezone.now().date()).days
                
                # Determine priority based on repayment method
                if repayment_method == 'daily':
                    if days_until_due < 0:
                        priority = 'overdue'
                        priority_label = 'Overdue'
                    elif days_until_due == 0:
                        priority = 'due_today'
                        priority_label = 'Due Today'
                    else:
                        priority = 'due_later'
                        priority_label = 'Due Later'
                elif repayment_method == 'weekly':
                    if days_until_due < 0:
                        priority = 'overdue'
                        priority_label = 'Overdue'
                    elif days_until_due <= 1:
                        priority = 'due_today'
                        priority_label = 'Due Today/Tomorrow'
                    elif days_until_due <= 7:
                        priority = 'due_this_week'
                        priority_label = 'Due This Week'
                    else:
                        priority = 'due_later'
                        priority_label = 'Due Later'
                else:  # monthly
                    if days_until_due < 0:
                        priority = 'overdue'
                        priority_label = 'Overdue'
                    elif days_until_due == 0:
                        priority = 'due_today'
                        priority_label = 'Due Today'
                    elif days_until_due <= 7:
                        priority = 'due_this_week'
                        priority_label = 'Due This Week'
                    else:
                        priority = 'due_later'
                        priority_label = 'Due Later'
                
                # Get last payment
                try:
                    last_payment = Repayment.objects.filter(loan=loan).order_by('-payment_date').first()
                except:
                    last_payment = None
                
                loan_data = {
                    'id': str(loan.id),  # Convert UUID to string for JSON serialization
                    'loan_number': loan.loan_number or f'LOAN-{loan.id}',
                    'borrower_name': f"{loan.borrower.first_name} {loan.borrower.last_name}",
                    'borrower_phone': loan.borrower.phone_number,
                    'due_date': next_due_date.isoformat() if isinstance(next_due_date, date) else str(next_due_date) if next_due_date else None,
                    'outstanding_balance': float(loan.outstanding_amount),
                    'expected_payment_amount': float(expected_payment_amount),
                    'days_until_due': days_until_due,
                    'priority': priority,
                    'priority_label': priority_label,
                    'last_payment_date': last_payment.payment_date.isoformat() if last_payment and hasattr(last_payment.payment_date, 'isoformat') else (str(last_payment.payment_date) if last_payment and last_payment.payment_date else None),
                    'last_payment_amount': float(last_payment.amount) if last_payment else None,
                    'repayment_method': repayment_method,
                    'loan_product': loan.application.loan_product.name if loan.application and loan.application.loan_product else 'Unknown',
                    'product_type': loan.application.loan_product.product_type if loan.application and loan.application.loan_product else 'unknown',
                    'principal_amount': float(loan.principal_amount),
                    'total_amount': float(loan.total_amount),
                    'amount_paid': float(loan.amount_paid),
                }
                
                loans_due.append(loan_data)
                total_due_amount += expected_payment_amount
        
        # Calculate summary statistics
        total_loans = len(loans_due)
        average_due = total_due_amount / total_loans if total_loans > 0 else Decimal('0.00')
        
        # Calculate priority breakdown
        priority_breakdown = {
            'overdue_count': len([loan for loan in loans_due if loan['priority'] == 'overdue']),
            'due_today_count': len([loan for loan in loans_due if loan['priority'] == 'due_today']),
            'due_this_week_count': len([loan for loan in loans_due if loan['priority'] == 'due_this_week']),
            'due_later_count': len([loan for loan in loans_due if loan['priority'] == 'due_later']),
        }
        
        return {
            'summary': {
                'total_loans_due': total_loans,
                'total_amount_due': total_due_amount,
                'average_due_amount': average_due,
            },
            'priority_breakdown': priority_breakdown,
            'loans': loans_due,
            'chart_data': {
                'due_date_distribution': {
                    'labels': [],
                    'amounts': []
                }
            }
        }
    
    def get_delinquent_loans_report(self, **kwargs):
        """Get delinquent loans report with detailed data"""
        from django.utils import timezone
        from decimal import Decimal
        
        branch_id = kwargs.get('branch_id')
        days_filter = kwargs.get('days_overdue_filter')
        
        # Get loans that are overdue (include active, rolled_over, and defaulted) - exclude soft-deleted
        loans_qs = Loan.objects.filter(
            is_deleted=False,
            status__in=['active', 'rolled_over', 'defaulted'],
            due_date__lt=timezone.now()
        )
        
        if branch_id:
            loans_qs = loans_qs.filter(borrower__branch_id=branch_id)
        
        # Calculate delinquent loans
        delinquent_loans = []
        total_delinquent_amount = Decimal('0.00')
        
        for loan in loans_qs:
            # Calculate outstanding amount
            outstanding = loan.outstanding_amount
            
            if outstanding > 0:
                # Handle both date and datetime objects
                due_date = loan.due_date
                if hasattr(due_date, 'date'):
                    due_date = due_date.date()
                days_overdue = (timezone.now().date() - due_date).days
                
                # Apply days filter
                if days_filter:
                    if days_filter == '1_30' and not (1 <= days_overdue <= 30):
                        continue
                    elif days_filter == '31_60' and not (31 <= days_overdue <= 60):
                        continue
                    elif days_filter == '61_90' and not (61 <= days_overdue <= 90):
                        continue
                    elif days_filter == '90_plus' and days_overdue < 90:
                        continue
                
                # Determine delinquency level
                if days_overdue <= 30:
                    delinquency_level = 'mild'
                    delinquency_label = 'Mild (1-30 days)'
                elif days_overdue <= 60:
                    delinquency_level = 'moderate'
                    delinquency_label = 'Moderate (31-60 days)'
                elif days_overdue <= 90:
                    delinquency_level = 'severe'
                    delinquency_label = 'Severe (61-90 days)'
                else:
                    delinquency_level = 'critical'
                    delinquency_label = 'Critical (90+ days)'
                
                # Get last payment
                try:
                    last_payment = Repayment.objects.filter(loan=loan).order_by('-payment_date').first()
                except:
                    last_payment = None
                
                loan_data = {
                    'id': str(loan.id),  # Convert UUID to string for JSON serialization
                    'loan_number': loan.loan_number or f'LOAN-{loan.id}',
                    'borrower_name': f"{loan.borrower.first_name} {loan.borrower.last_name}",
                    'borrower_phone': loan.borrower.phone_number,
                    'due_date': loan.due_date.isoformat() if hasattr(loan.due_date, 'isoformat') else str(loan.due_date) if loan.due_date else None,
                    'outstanding_balance': outstanding,
                    'days_overdue': days_overdue,
                    'delinquency_level': delinquency_level,
                    'delinquency_label': delinquency_label,
                    'last_payment_date': last_payment.payment_date.isoformat() if last_payment and hasattr(last_payment.payment_date, 'isoformat') else (str(last_payment.payment_date) if last_payment and last_payment.payment_date else None),
                    'last_payment_amount': float(last_payment.amount) if last_payment else None,
                }
                
                delinquent_loans.append(loan_data)
                total_delinquent_amount += outstanding
        
        # Calculate summary statistics
        total_loans = len(delinquent_loans)
        average_delinquent = total_delinquent_amount / total_loans if total_loans > 0 else Decimal('0.00')
        
        # Calculate delinquency breakdown
        delinquency_breakdown = {
            'mild_count': len([loan for loan in delinquent_loans if loan['delinquency_level'] == 'mild']),
            'moderate_count': len([loan for loan in delinquent_loans if loan['delinquency_level'] == 'moderate']),
            'severe_count': len([loan for loan in delinquent_loans if loan['delinquency_level'] == 'severe']),
            'critical_count': len([loan for loan in delinquent_loans if loan['delinquency_level'] == 'critical']),
        }
        
        return {
            'summary': {
                'total_delinquent_loans': total_loans,
                'total_delinquent_amount': total_delinquent_amount,
                'average_delinquent_amount': average_delinquent,
            },
            'delinquency_breakdown': delinquency_breakdown,
            'loans': delinquent_loans,
            'recovery_trend': {
                'labels': [],
                'amounts': []
            },
            'age_analysis': {
                'counts': [delinquency_breakdown['mild_count'], delinquency_breakdown['moderate_count'], 
                          delinquency_breakdown['severe_count'], delinquency_breakdown['critical_count']]
            }
        }
    
    def get_customer_requests_report(self, **kwargs):
        """Placeholder for customer requests report"""
        return {
            'summary': {
                'pending_requests': 0,
                'in_progress_requests': 0,
                'resolved_requests': 0
            },
            'requests': []
        }
    
    def generate_comprehensive_dashboard_data(self, **kwargs):
        """Alias for generate_dashboard_data for compatibility"""
        return self.generate_dashboard_data(kwargs.get('branch_id'))
    
    def _get_summary_metrics(self, **kwargs):
        """Alias for get_summary_metrics for compatibility"""
        return self.get_summary_metrics(kwargs.get('branch_id'))


# Create a global instance
simple_reports_service = SimpleReportsService()