import json
import logging
from django.core.serializers.json import DjangoJSONEncoder

logger = logging.getLogger(__name__)
from django.shortcuts import render, get_object_or_404, redirect
from django.contrib.auth.decorators import login_required
from users.decorators import admin_required, staff_required, portfolio_access_required
from .decorators import admin_only, admin_only_ajax
from django.contrib import messages
from django.db.models import Sum, Count, Q, F, Avg
from django.utils import timezone
from utils.datetime_utils import get_current_datetime, make_datetime_compatible
from datetime import datetime, timedelta
from decimal import Decimal, DecimalException
from .models import (
    LoanProduct, LoanApplication, Loan, Repayment, 
    RolloverRequest, MpesaTransaction
)
from utils.filtering import (
    apply_branch_and_portfolio_filters,
    get_filtered_clients,
    get_filtered_loans,
    get_filtered_applications,
    get_filtered_repayments
)
from users.models import CustomUser
from reports.models import LoanScoring
from utils.models import AuditLog, SystemSetting, Notification
from django.core.paginator import Paginator, PageNotAnInteger, EmptyPage
from django.http import JsonResponse, HttpResponse
from django.db import models, IntegrityError
import math
import os

# Import PDF generation functions
from .minimal_analytics import (
    generate_loans_dashboard_pdf,
    generate_clients_dashboard_pdf,
    generate_payments_dashboard_pdf,
    generate_reports_dashboard_pdf,
    generate_portfolio_dashboard_pdf,
    generate_custom_analytics_pdf,
    analytics_dashboard
)

def generate_enhanced_dashboard_data(branch_id=None):
    """Generate comprehensive dashboard data with enhanced analytics"""
    
    today = timezone.now().date()
    current_month_start = today.replace(day=1)
    
    # Base querysets with branch filtering
    loans_qs = Loan.objects.all()
    repayments_qs = Repayment.objects.all()
    users_qs = CustomUser.objects.filter(role='borrower')
    
    if branch_id:
        loans_qs = loans_qs.filter(borrower__branch_id=branch_id)
        repayments_qs = repayments_qs.filter(loan__borrower__branch_id=branch_id)
        users_qs = users_qs.filter(branch_id=branch_id)
    
    # Enhanced Loan Performance Analytics
    loan_performance = {
        'total_loans': loans_qs.count(),
        'active_loans': loans_qs.filter(status='active').count(),
        'paid_loans': loans_qs.filter(status='paid').count(),
        'defaulted_loans': loans_qs.filter(status='defaulted').count(),
        'total_disbursed': loans_qs.aggregate(total=Sum('principal_amount'))['total'] or 0,
        'total_collected': repayments_qs.aggregate(total=Sum('amount'))['total'] or 0,
    }
    
    # Calculate performance metrics
    loan_performance['outstanding_amount'] = loan_performance['total_disbursed'] - loan_performance['total_collected']
    loan_performance['collection_rate'] = (
        (loan_performance['total_collected'] / loan_performance['total_disbursed'] * 100) 
        if loan_performance['total_disbursed'] > 0 else 0
    )
    loan_performance['default_rate'] = (
        (loan_performance['defaulted_loans'] / loan_performance['total_loans'] * 100)
        if loan_performance['total_loans'] > 0 else 0
    )
    
    # Enhanced Loan Distribution Analytics
    loan_distribution = loans_qs.values('status').annotate(
        count=Count('id'),
        total_amount=Sum('principal_amount')
    ).order_by('status')
    
    distribution_data = {
        'labels': [],
        'counts': [],
        'amounts': [],
        'colors': []
    }
    
    color_map = {
        'active': '#10B981',      # Green
        'paid': '#3B82F6',        # Blue  
        'defaulted': '#EF4444',   # Red
        'rolled_over': '#F59E0B', # Yellow
        'pending': '#8B5CF6'      # Purple
    }
    
    for item in loan_distribution:
        distribution_data['labels'].append(item['status'].title())
        distribution_data['counts'].append(item['count'])
        distribution_data['amounts'].append(float(item['total_amount'] or 0))
        distribution_data['colors'].append(color_map.get(item['status'], '#6B7280'))
    
    # Monthly Performance Trends
    monthly_performance = []
    monthly_labels = []
    
    for i in range(12):
        month_start = current_month_start - timedelta(days=30*i)
        month_end = (month_start + timedelta(days=32)).replace(day=1)
        
        month_loans = loans_qs.filter(
            created_at__gte=month_start,
            created_at__lt=month_end
        )
        
        month_repayments = repayments_qs.filter(
            payment_date__gte=month_start,
            payment_date__lt=month_end
        )
        
        disbursed = month_loans.aggregate(total=Sum('principal_amount'))['total'] or 0
        collected = month_repayments.aggregate(total=Sum('amount'))['total'] or 0
        
        monthly_performance.insert(0, {
            'month': month_start.strftime('%b %Y'),
            'disbursed': float(disbursed),
            'collected': float(collected),
            'net_flow': float(disbursed - collected),
            'loans_count': month_loans.count()
        })
        monthly_labels.insert(0, month_start.strftime('%b %Y'))
    
    # Client Growth Analytics
    client_growth_data = []
    
    for i in range(12):
        month_start = current_month_start - timedelta(days=30*i)
        month_end = (month_start + timedelta(days=32)).replace(day=1)
        
        new_clients = users_qs.filter(
            date_joined__gte=month_start,
            date_joined__lt=month_end
        ).count()
        
        client_growth_data.insert(0, {
            'month': month_start.strftime('%b %Y'),
            'new_clients': new_clients
        })
    
    # Portfolio Quality Analytics
    overdue_loans = loans_qs.filter(
        status='active',
        due_date__lt=today
    )
    
    portfolio_quality = {
        'total_portfolio': loan_performance['total_disbursed'],
        'overdue_count': overdue_loans.count(),
        'overdue_amount': overdue_loans.aggregate(total=Sum('principal_amount'))['total'] or 0,
        'current_loans': loans_qs.filter(status='active', due_date__gte=today).count(),
    }
    
    portfolio_quality['overdue_rate'] = (
        (portfolio_quality['overdue_count'] / loan_performance['active_loans'] * 100)
        if loan_performance['active_loans'] > 0 else 0
    )
    
    # Risk Analytics
    risk_categories = {
        'low_risk': overdue_loans.filter(
            due_date__gte=today - timedelta(days=30)
        ).count(),
        'medium_risk': overdue_loans.filter(
            due_date__lt=today - timedelta(days=30),
            due_date__gte=today - timedelta(days=90)
        ).count(),
        'high_risk': overdue_loans.filter(
            due_date__lt=today - timedelta(days=90)
        ).count(),
    }
    
    return {
        'loan_performance': loan_performance,
        'loan_distribution': distribution_data,
        'client_growth': client_growth_data,
        'monthly_performance': monthly_performance,
        'portfolio_quality': portfolio_quality,
        'risk_categories': risk_categories,
        'monthly_labels': monthly_labels,
        'generated_at': timezone.now().isoformat(),
    }


@login_required
@portfolio_access_required
def filtered_loans(request):
    """View for filtered loans based on status"""
    status = request.GET.get('status', 'active')
    selected_branch_id = request.session.get('selected_branch_id')
    
    # Base queryset
    loans_qs = Loan.objects.all()
    
    # Apply portfolio-based access control for staff members
    if request.user.role in ['loan_officer', 'team_leader'] and not request.user.is_superuser:
        # Staff members can only see loans from their assigned borrowers
        loans_qs = loans_qs.filter(borrower__portfolio_manager=request.user)
    elif request.user.role in ['secretary', 'auditor'] and not request.user.is_superuser:
        # Secretaries and auditors can only see loans from borrowers in their branch
        if request.user.branch:
            loans_qs = loans_qs.filter(borrower__branch=request.user.branch)
    
    # Apply branch filtering if a branch is selected
    if selected_branch_id:
        loans_qs = loans_qs.filter(borrower__branch_id=selected_branch_id)
    
    # Apply status filtering - EXCLUDE rolled over loans from active
    if status == 'active':
        loans_qs = loans_qs.filter(status='active')  # Only truly active loans
    elif status == 'overdue':
        # Dynamic overdue filtering: due_date < current_date AND outstanding_amount > 0 AND status = 'active'
        # outstanding_amount is a @property so we use DB fields: total_amount > _amount_paid_cache
        # Validates: Requirements 5.3, 5.4, 5.5, 5.6
        from django.db.models import F
        loans_qs = loans_qs.filter(
            status='active',
            due_date__lt=timezone.now().date(),
            total_amount__gt=F('_amount_paid_cache')
        )
    elif status == 'defaulted':
        loans_qs = loans_qs.filter(status='defaulted')
    elif status == 'overdue_1_30':
        from datetime import timedelta
        thirty_days_ago = timezone.now() - timedelta(days=30)
        loans_qs = loans_qs.filter(status='active', due_date__lt=timezone.now(), due_date__gte=thirty_days_ago)
    elif status == 'overdue_31_60':
        from datetime import timedelta
        sixty_days_ago = timezone.now() - timedelta(days=60)
        thirty_days_ago = timezone.now() - timedelta(days=30)
        loans_qs = loans_qs.filter(status='active', due_date__lt=thirty_days_ago, due_date__gte=sixty_days_ago)
    elif status == 'overdue_60_plus':
        from datetime import timedelta
        sixty_days_ago = timezone.now() - timedelta(days=60)
        loans_qs = loans_qs.filter(status='active', due_date__lt=sixty_days_ago)
    elif status == 'processed_this_month':
        from datetime import datetime
        current_month = datetime.now().replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        loans_qs = loans_qs.filter(created_at__gte=current_month)
    elif status == 'interest_bearing':
        loans_qs = loans_qs.filter(interest_rate__gt=0)
    else:
        loans_qs = loans_qs.filter(status=status)
    
    # Order by most recent
    loans_qs = loans_qs.order_by('-created_at')
    
    # Pagination
    paginator = Paginator(loans_qs, 25)
    page = request.GET.get('page')
    try:
        loans = paginator.page(page)
    except PageNotAnInteger:
        loans = paginator.page(1)
    except EmptyPage:
        loans = paginator.page(paginator.num_pages)
    
    context = {
        'loans': loans,
        'status_filter': status,
        'title': f'{status.title()} Loans',
        'total_count': loans_qs.count(),
        'total_amount': loans_qs.aggregate(total=Sum('principal_amount'))['total'] or 0,
        'today': timezone.now().date(),
    }
    
    return render(request, 'loans/filtered_loans.html', context)


@login_required
def rolled_over_loans(request):
    """View for rolled over loans only"""
    selected_branch_id = request.session.get('selected_branch_id')
    
    # Base queryset for rolled over loans
    loans_qs = Loan.objects.filter(status='rolled_over')
    
    # Apply branch filtering if a branch is selected
    if selected_branch_id:
        loans_qs = loans_qs.filter(borrower__branch_id=selected_branch_id)
    
    # Order by most recent
    loans_qs = loans_qs.order_by('-created_at')
    
    # Pagination
    paginator = Paginator(loans_qs, 25)
    page = request.GET.get('page')
    try:
        loans = paginator.page(page)
    except PageNotAnInteger:
        loans = paginator.page(1)
    except EmptyPage:
        loans = paginator.page(paginator.num_pages)
    
    context = {
        'loans': loans,
        'status_filter': 'rolled_over',
        'title': 'Rolled Over Loans',
        'total_count': loans_qs.count(),
        'total_amount': loans_qs.aggregate(total=Sum('principal_amount'))['total'] or 0,
        'today': timezone.now().date(),
    }
    
    return render(request, 'loans/rolled_over_loans.html', context)


@login_required
def filtered_applications(request):
    """View for filtered loan applications based on status"""
    status = request.GET.get('status', 'pending')
    selected_branch_id = request.session.get('selected_branch_id')
    
    # Base queryset
    applications_qs = LoanApplication.objects.all()
    
    # Apply branch filtering if a branch is selected
    if selected_branch_id:
        applications_qs = applications_qs.filter(borrower__branch_id=selected_branch_id)
    
    # Apply status filtering
    applications_qs = applications_qs.filter(status=status)
    
    # Order by most recent
    applications_qs = applications_qs.order_by('-submitted_at')
    
    # Pagination
    paginator = Paginator(applications_qs, 25)
    page = request.GET.get('page')
    try:
        applications = paginator.page(page)
    except PageNotAnInteger:
        applications = paginator.page(1)
    except EmptyPage:
        applications = paginator.page(paginator.num_pages)
    
    context = {
        'applications': applications,
        'status_filter': status,
        'title': f'{status.title()} Applications',
        'total_count': applications_qs.count(),
        'total_amount': applications_qs.aggregate(total=Sum('requested_amount'))['total'] or 0,
    }
    
    return render(request, 'loans/filtered_applications.html', context)


@login_required
@portfolio_access_required
def dashboard(request):
    """Main dashboard view with statistics and charts"""
    # Get selected branch from session
    selected_branch_id = request.session.get('selected_branch_id')
    
    # Apply portfolio-based filtering for non-admin users
    if request.user.role in ['loan_officer', 'team_leader'] and not request.user.is_superuser:
        # Staff members can only see data related to their assigned borrowers
        users_qs = CustomUser.objects.filter(role='borrower', portfolio_manager=request.user)
        loans_qs = Loan.objects.filter(borrower__portfolio_manager=request.user)
        applications_qs = LoanApplication.objects.filter(borrower__portfolio_manager=request.user)
        repayments_qs = Repayment.objects.filter(loan__borrower__portfolio_manager=request.user)
    elif request.user.role in ['secretary', 'auditor'] and not request.user.is_superuser:
        # Secretaries and auditors can only see data related to borrowers in their branch
        if request.user.branch:
            users_qs = CustomUser.objects.filter(role='borrower', branch=request.user.branch)
            loans_qs = Loan.objects.filter(borrower__branch=request.user.branch)
            applications_qs = LoanApplication.objects.filter(borrower__branch=request.user.branch)
            repayments_qs = Repayment.objects.filter(loan__borrower__branch=request.user.branch)
        else:
            users_qs = CustomUser.objects.filter(role='borrower')
            loans_qs = Loan.objects.all()
            applications_qs = LoanApplication.objects.all()
            repayments_qs = Repayment.objects.all()
    else:
        # Admin and superuser can see all data
        users_qs = CustomUser.objects.filter(role='borrower')
        loans_qs = Loan.objects.all()
        applications_qs = LoanApplication.objects.all()
        repayments_qs = Repayment.objects.all()
    
    # Get current date
    today = timezone.now().date()
    
    # Calculate date ranges
    six_months_ago = today - timedelta(days=180)
    one_year_ago = today - timedelta(days=365)
    
    # Convert dates to compatible datetimes using datetime_utils
    start_of_today = make_datetime_compatible(datetime.combine(today, datetime.min.time()))
    start_of_six_months = make_datetime_compatible(datetime.combine(six_months_ago, datetime.min.time()))
    start_of_year = make_datetime_compatible(datetime.combine(one_year_ago, datetime.min.time()))
    
    # Apply branch filtering if a branch is selected (for admin users)
    if selected_branch_id and (request.user.is_superuser or request.user.role == 'admin'):
        users_qs = users_qs.filter(branch_id=selected_branch_id)
        loans_qs = loans_qs.filter(borrower__branch_id=selected_branch_id)
        applications_qs = applications_qs.filter(borrower__branch_id=selected_branch_id)
        repayments_qs = repayments_qs.filter(loan__borrower__branch_id=selected_branch_id)

    # Use filtered querysets for statistics
    total_users = users_qs.count()
    active_users = users_qs.filter(is_active=True).count()
    dormant_users = users_qs.filter(last_login__lt=start_of_six_months).count()
    blacklisted_users = users_qs.filter(status='blacklisted').count()

    # Loan statistics with branch filtering
    total_loans = loans_qs.count()
    # Only count truly active loans (exclude rolled over loans)
    active_loans = loans_qs.filter(status='active').count()
    paid_loans = loans_qs.filter(status='paid').count()
    defaulted_loans = loans_qs.filter(status='defaulted').count()
    rolled_over_loans = loans_qs.filter(status='rolled_over').count()

    # Applications with branch filtering
    pending_applications = applications_qs.filter(status='pending').count()
    approved_applications = applications_qs.filter(status='approved').count()
    rejected_applications = applications_qs.filter(status='rejected').count()

    # Financial metrics with branch filtering
    total_disbursed = loans_qs.aggregate(total=Sum('principal_amount'))['total'] or 0
    total_collected = repayments_qs.aggregate(total=Sum('amount'))['total'] or 0
    outstanding_amount = total_disbursed - total_collected

    # Monthly metrics with branch filtering
    monthly_disbursements = loans_qs.filter(
        created_at__gte=start_of_year
    ).aggregate(total=Sum('principal_amount'))['total'] or 0

    monthly_collections = repayments_qs.filter(
        payment_date__gte=start_of_year
    ).aggregate(total=Sum('amount'))['total'] or 0

    # Calculate rates and changes with branch filtering
    total_loans_count = loans_qs.count()
    defaulted_loans_count = loans_qs.filter(status='defaulted').count()
    defaulted_rate = (defaulted_loans_count / total_loans_count * 100) if total_loans_count > 0 else 0

    # Previous month comparisons with branch filtering
    prev_month_start = start_of_year - timedelta(days=30)
    prev_active_loans = loans_qs.filter(status='active', created_at__lt=start_of_year).count()
    prev_active_users = users_qs.filter(is_active=True, date_joined__lt=start_of_year).count()
    prev_pending_applications = applications_qs.filter(status='pending', submitted_at__lt=start_of_year).count()
    prev_defaulted_loans = loans_qs.filter(status='defaulted', created_at__lt=start_of_year).count()
    prev_total_loans = loans_qs.filter(created_at__lt=start_of_year).count()
    prev_default_rate = (prev_defaulted_loans / prev_total_loans * 100) if prev_total_loans > 0 else 0

    # Calculate changes
    active_loans_change = ((active_loans - prev_active_loans) / prev_active_loans * 100) if prev_active_loans > 0 else 0
    active_users_change = ((active_users - prev_active_users) / prev_active_users * 100) if prev_active_users > 0 else 0
    pending_applications_change = ((pending_applications - prev_pending_applications) / prev_pending_applications * 100) if prev_pending_applications > 0 else 0
    default_rate_change = defaulted_rate - prev_default_rate

    # Recent activity with branch filtering
    recent_activity_qs = AuditLog.objects.select_related('user').order_by('-created_at')
    if selected_branch_id:
        # Filter audit logs for branch-related activities BEFORE slicing
        recent_activity_qs = recent_activity_qs.filter(
            Q(user__branch_id=selected_branch_id) | 
            Q(user__isnull=True)  # Include system activities
        )
    # Apply slice AFTER filtering
    recent_activity = recent_activity_qs[:10]

    # Monthly data for charts with naive datetime queries and branch filtering
    monthly_data = []
    for i in range(6):
        month_start = datetime.now().replace(day=1) - timedelta(days=30*i)
        month_start = datetime.combine(month_start.date(), datetime.min.time())
        month_end = (month_start + timedelta(days=32)).replace(day=1)
        
        # Apply branch filtering to monthly chart data
        disbursed_qs = Loan.objects.filter(
            created_at__gte=month_start,
            created_at__lt=month_end
        )
        repaid_qs = Repayment.objects.filter(
            payment_date__gte=month_start,
            payment_date__lt=month_end
        )
        
        if selected_branch_id:
            disbursed_qs = disbursed_qs.filter(borrower__branch_id=selected_branch_id)
            repaid_qs = repaid_qs.filter(loan__borrower__branch_id=selected_branch_id)
        
        disbursed = disbursed_qs.aggregate(total=Sum('principal_amount'))['total'] or 0
        repaid = repaid_qs.aggregate(total=Sum('amount'))['total'] or 0
        
        monthly_data.append({
            'month': month_start.strftime('%B %Y'),
            'disbursed': str(disbursed),
            'repaid': str(repaid)
        })

    # Distribution data with branch filtering
    loan_statuses_qs = Loan.objects.values('status').annotate(count=Count('id'))
    if selected_branch_id:
        loan_statuses_qs = loan_statuses_qs.filter(borrower__branch_id=selected_branch_id)
    
    loan_statuses = loan_statuses_qs
    distribution_data = {
        'labels': [status['status'].title() for status in loan_statuses],
        'data': [str(status['count']) for status in loan_statuses],
        'colors': ['#3C9EBD', '#0A3D62', '#2ECC71', '#E74C3C']
    }

    # Client growth data with naive datetime queries and branch filtering
    client_growth = []
    for i in range(6):
        month_start = datetime.now().replace(day=1) - timedelta(days=30*i)
        month_start = datetime.combine(month_start.date(), datetime.min.time())
        month_end = (month_start + timedelta(days=32)).replace(day=1)
        
        new_clients_query = CustomUser.objects.filter(
            role='borrower',
            date_joined__gte=month_start,
            date_joined__lt=month_end
        )
        
        if selected_branch_id:
            new_clients_query = new_clients_query.filter(branch_id=selected_branch_id)
        
        new_clients = new_clients_query.count()
        
        client_growth.append({
            'month': month_start.strftime('%B %Y'),
            'new_clients': str(new_clients)
        })

    # Generate enhanced dashboard data
    try:
        enhanced_data = generate_enhanced_dashboard_data(branch_id=selected_branch_id)
        
        # Prepare dashboard data with enhanced analytics
        dashboard_data = {
            'monthlyData': monthly_data,
            'distributionData': distribution_data,
            'clientGrowth': client_growth,
            'enhanced': enhanced_data
        }
    except Exception as e:
        # Fallback to basic data if enhanced analytics fail
        dashboard_data = {
            'monthlyData': monthly_data,
            'distributionData': distribution_data,
            'clientGrowth': client_growth
        }

    context = {
        'total_users': total_users,
        'active_users': active_users,
        'dormant_users': dormant_users,
        'blacklisted_users': blacklisted_users,
        'total_loans': total_loans,
        'active_loans': active_loans,
        'paid_loans': paid_loans,
        'defaulted_loans': defaulted_loans,
        'rolled_over_loans': rolled_over_loans,
        'pending_applications': pending_applications,
        'approved_applications': approved_applications,
        'rejected_applications': rejected_applications,
        'total_disbursed': total_disbursed,
        'total_collected': total_collected,
        'outstanding_amount': outstanding_amount,
        'monthly_disbursements': monthly_disbursements,
        'monthly_collections': monthly_collections,
        'default_rate': round(defaulted_rate, 2),
        'recent_activity': recent_activity,
        'active_loans_change': round(active_loans_change, 2),
        'active_users_change': round(active_users_change, 2),
        'pending_applications_change': round(pending_applications_change, 2),
        'default_rate_change': round(default_rate_change, 2),
        'dashboard_data': dashboard_data,
    }
    
    return render(request, 'loans/dashboard.html', context)


@login_required
def loan_products(request):
    """List all loan products"""
    products = LoanProduct.objects.filter(is_active=True)
    return render(request, 'loans/loan_products.html', {'products': products})


@login_required
def loan_product_detail(request, pk):
    """Detail view for a loan product"""
    product = get_object_or_404(LoanProduct, pk=pk)
    return render(request, 'loans/loan_product_detail.html', {'product': product})


@login_required
def loan_applications(request):
    """List all loan applications"""
    # Check if user has permission to access loans
    if not request.user.has_permission('loans', 'access'):
        messages.error(request, 'You do not have permission to view loan applications.')
        return redirect('loans:loans')
    
    # Get selected branch from session
    selected_branch_id = request.session.get('selected_branch_id')
    
    # Get filter parameters
    status = request.GET.get('status')
    product = request.GET.get('product')
    client = request.GET.get('client')
    date_from = request.GET.get('date_from')
    date_to = request.GET.get('date_to')
    
    # Base queryset with branch filtering
    applications = LoanApplication.objects.select_related(
        'borrower', 'loan_product', 'reviewed_by'
    )
    
    # Apply branch filtering if a branch is selected
    if selected_branch_id:
        applications = applications.filter(borrower__branch_id=selected_branch_id)
    
    applications = applications.order_by('-submitted_at')
    
    # Apply filters
    if status:
        applications = applications.filter(status=status)
    if product:
        applications = applications.filter(loan_product__product_type=product)
    if client:
        applications = applications.filter(borrower__id=client)
    if date_from:
        applications = applications.filter(submitted_at__date__gte=date_from)
    if date_to:
        applications = applications.filter(submitted_at__date__lte=date_to)
    
    # Get filter options (also filtered by branch)
    loan_products = LoanProduct.objects.filter(is_active=True)
    clients_qs = CustomUser.objects.filter(role='borrower', status='active')
    
    if selected_branch_id:
        clients_qs = clients_qs.filter(branch_id=selected_branch_id)
    
    clients = clients_qs
    
    context = {
        'applications': applications,
        'loan_products': loan_products,
        'clients': clients,
        'statuses': LoanApplication.STATUS_CHOICES,
        'filters': {
            'status': status,
            'product': product,
            'client': client,
            'date_from': date_from,
            'date_to': date_to
        }
    }
    return render(request, 'loans/applications.html', context)


@login_required
def new_application(request):
    """Create a new loan application with improved client handling"""
    # Check if user has permission to create loans
    if not request.user.has_permission('loans', 'create'):
        messages.error(request, 'You do not have permission to create loan applications.')
        return redirect('loans:loans')
    try:
        # Get client if specified
        client_id = request.GET.get('client_id')
        client = None
        if client_id:
            try:
                client = get_object_or_404(CustomUser, pk=client_id, role='borrower')
            except:
                client = None
        
        # Get all active loan products
        loan_products = LoanProduct.objects.filter(is_active=True).order_by(
            models.Case(
                models.When(product_type='boost', then=models.Value(1)),
                models.When(product_type='boost_plus', then=models.Value(2)),
                models.When(product_type='mwamba', then=models.Value(3)),
                models.When(product_type='imara', then=models.Value(4)),
                default=models.Value(4)
            ),
            'name'
        )
        
        if request.method == 'POST':
            try:
                # Get form data with detailed validation
                client_id = request.POST.get('client_id')
                print(f"DEBUG: Received client_id: {client_id}")  # Debug logging
                
                if not client_id or client_id == '':
                    return JsonResponse({
                        'status': 'error', 
                        'message': 'Please select a client from the dropdown'
                    }, status=400)

                loan_product_id = request.POST.get('loan_product')
                if not loan_product_id:
                    return JsonResponse({
                        'status': 'error', 
                        'message': 'Please select a loan product'
                    }, status=400)

                try:
                    requested_amount = Decimal(request.POST.get('amount', '0'))
                    if requested_amount <= 0:
                        return JsonResponse({
                            'status': 'error', 
                            'message': 'Please enter a valid loan amount greater than 0'
                        }, status=400)
                except (TypeError, ValueError, DecimalException):
                    return JsonResponse({
                        'status': 'error', 
                        'message': 'Please enter a valid loan amount'
                    }, status=400)

                try:
                    requested_duration = int(request.POST.get('duration', '0'))
                    if requested_duration <= 0:
                        return JsonResponse({
                            'status': 'error', 
                            'message': 'Please enter a valid duration greater than 0'
                        }, status=400)
                except (TypeError, ValueError):
                    return JsonResponse({
                        'status': 'error', 
                        'message': 'Please enter a valid duration'
                    }, status=400)

                purpose = request.POST.get('purpose', '').strip()
                if not purpose:
                    return JsonResponse({
                        'status': 'error', 
                        'message': 'Please enter the loan purpose'
                    }, status=400)

                repayment_method = request.POST.get('repayment_method')
                if not repayment_method:
                    return JsonResponse({
                        'status': 'error', 
                        'message': 'Please select a repayment method'
                    }, status=400)

                # Handle disbursement date
                disbursement_date_str = request.POST.get('disbursement_date')
                if disbursement_date_str:
                    try:
                        disbursement_date = datetime.strptime(disbursement_date_str, '%Y-%m-%d')
                        # Check if user is trying to set a past date
                        if disbursement_date.date() < datetime.now().date():
                            if not request.user.is_staff and not request.user.is_superuser:
                                return JsonResponse({
                                    'status': 'error', 
                                    'message': 'Only administrators can set past disbursement dates'
                                }, status=403)
                    except ValueError:
                        return JsonResponse({
                            'status': 'error', 
                            'message': 'Invalid disbursement date format'
                        }, status=400)
                else:
                    disbursement_date = datetime.now()

                # Get loan product and client with better error handling
                try:
                    loan_product = LoanProduct.objects.get(pk=loan_product_id)
                except LoanProduct.DoesNotExist:
                    return JsonResponse({
                        'status': 'error', 
                        'message': 'Selected loan product not found'
                    }, status=400)
                
                try:
                    client = CustomUser.objects.get(pk=client_id, role='borrower')
                    print(f"DEBUG: Found client: {client.get_full_name()}")  # Debug logging
                except CustomUser.DoesNotExist:
                    return JsonResponse({
                        'status': 'error', 
                        'message': 'Selected client not found or is not a borrower'
                    }, status=400)
                
                # Check client status
                if hasattr(client, 'status'):
                    if client.status == 'blacklisted':
                        return JsonResponse({
                            'status': 'error', 
                            'message': f'Cannot create loan application for blacklisted client: {client.get_full_name()}'
                        }, status=403)
                    
                    if client.status == 'suspended':
                        return JsonResponse({
                            'status': 'error', 
                            'message': f'Cannot create loan application for suspended client: {client.get_full_name()}'
                        }, status=403)
                    
                    if client.status == 'inactive':
                        return JsonResponse({
                            'status': 'error', 
                            'message': f'Cannot create loan application for inactive client: {client.get_full_name()}'
                        }, status=403)
                
                # Validate amount, duration, and repayment method
                try:
                    loan_product.validate_amount(requested_amount)
                except ValueError as e:
                    return JsonResponse({'status': 'error', 'message': str(e)}, status=400)

                try:
                    loan_product.validate_duration(requested_duration)
                except ValueError as e:
                    return JsonResponse({'status': 'error', 'message': str(e)}, status=400)

                try:
                    loan_product.validate_repayment_method(repayment_method)
                except ValueError as e:
                    return JsonResponse({'status': 'error', 'message': str(e)}, status=400)
                
                # Calculate amounts
                # Convert days to months - use exact calculation, not ceiling
                # This ensures accurate interest: 90 days = 3 months, 60 days = 2 months, etc.
                months = Decimal(str(requested_duration)) / Decimal('30')
                months = max(Decimal('1'), months)  # Minimum 1 month
                interest_amount = loan_product.calculate_interest(requested_amount, months)
                processing_fee_amount = loan_product.calculate_processing_fee(requested_amount, months)
                total_amount = requested_amount + interest_amount + processing_fee_amount
                
                # Create loan application
                application = LoanApplication.objects.create(
                    borrower=client,
                    loan_product=loan_product,
                    requested_amount=requested_amount,
                    requested_duration=requested_duration,
                    purpose=purpose,
                    repayment_method=repayment_method,
                    interest_amount=interest_amount,
                    processing_fee_amount=processing_fee_amount,
                    total_amount=total_amount
                )
                
                print(f"DEBUG: Created application {application.application_number} for {client.get_full_name()}")
                
                # Auto-calculate credit score and handle auto-approval
                try:
                    # Try to get existing credit score
                    existing_score = LoanScoring.objects.filter(user=client).first()
                    if existing_score:
                        application.credit_score = existing_score.total_score
                    else:
                        application.credit_score = 0
                    
                    # Auto-approve if conditions are met
                    auto_approval_enabled = SystemSetting.get_bool('auto_approval_enabled', True)
                    auto_approval_min_score = SystemSetting.get_int('auto_approval_min_score', 80)
                    auto_approval_max_amount = Decimal(str(SystemSetting.get_float('auto_approval_max_amount', 25000)))
                    
                    if (auto_approval_enabled and 
                        application.credit_score >= auto_approval_min_score and 
                        requested_amount <= auto_approval_max_amount):
                        
                        application.auto_approved = True
                        application.status = 'approved'
                        application.reviewed_by = None
                        application.reviewed_at = datetime.now()
                        application.approval_notes = 'Auto-approved based on credit score'
                        
                        # Create loan record
                        loan = Loan.objects.create(
                            application=application,
                            borrower=client,
                            principal_amount=requested_amount,
                            interest_amount=interest_amount,
                            processing_fee=processing_fee_amount,
                            total_amount=total_amount,
                            disbursement_date=disbursement_date,
                            due_date=disbursement_date + timedelta(days=requested_duration),
                            duration_days=requested_duration,
                            status='active'
                        )
                        
                        messages.success(request, f'{loan_product.name} loan has been recorded and auto-approved!')
                    else:
                        messages.success(request, f'{loan_product.name} loan has been recorded and is under review.')
                        
                except Exception as e:
                    print(f"DEBUG: Credit score error: {e}")
                    messages.success(request, f'{loan_product.name} loan has been recorded and is under review.')
                
                application.save()
                
                # Create audit log
                AuditLog.objects.create(
                    user=request.user,
                    action='create',
                    model_name='LoanApplication',
                    object_id=str(application.id),
                    description=f'Added {loan_product.name} loan for {client.get_full_name()}'
                )
                
                # Create notification for admins
                if not application.auto_approved and SystemSetting.get_bool('email_notifications_enabled', True):
                    from django.urls import reverse
                    Notification.objects.create(
                        title=f'New {loan_product.name} Loan Application',
                        message=f'A new {loan_product.name} loan application has been submitted by {client.get_full_name()}',
                        priority='medium',
                        notification_type='application_submitted',
                        action_url=reverse('loans:application_detail', kwargs={'pk': application.id}),
                        loan_app=application
                    )
                
                return JsonResponse({
                    'status': 'success', 
                    'message': 'Loan application submitted successfully',
                    'application_id': str(application.id),
                    'application_number': application.application_number
                })
                
            except Exception as e:
                import traceback
                print("Error in loan application:", str(e))
                print(traceback.format_exc())
                return JsonResponse({
                    'status': 'error',
                    'message': f'An error occurred while processing your application: {str(e)}'
                }, status=500)
        
        # GET request - show the form
        # Get all active borrowers with proper status filtering
        clients = CustomUser.objects.filter(
            role='borrower'
        ).exclude(
            status__in=['deleted', 'archived']  # Exclude deleted/archived but allow inactive for admin review
        ).select_related().order_by('first_name', 'last_name')
        
        # Convert clients to a safe format for JavaScript
        clients_data = []
        for c in clients:
            client_data = {
                'id': str(c.id),
                'full_name': c.get_full_name(),
                'first_name': c.first_name or '',
                'last_name': c.last_name or '',
                'email': c.email or 'No email',
                'phone_number': str(c.phone_number) if c.phone_number else 'No phone',
                'business_name': getattr(c, 'business_name', '') or '',
                'id_number': getattr(c, 'id_number', '') or '',
                'status': getattr(c, 'status', 'active')
            }
            clients_data.append(client_data)
        
        # Convert loan products to dict with current settings
        loan_products_data = []
        for product in loan_products:
            try:
                product_data = product.to_dict()
                loan_products_data.append(product_data)
            except Exception as e:
                print(f"Error converting product {product.name}: {e}")
                # Fallback data - use system settings instead of hardcoded values
                loan_products_data.append({
                    'id': str(product.id),
                    'name': product.name,
                    'product_type': product.product_type,
                    'description': product.description,
                    'min_amount': float(product.get_min_amount()),
                    'max_amount': float(product.get_max_amount()),
                    'interest_rate': float(product.get_interest_rate()),
                    'processing_fee': float(product.get_processing_fee()),
                    'late_payment_penalty': float(product.get_late_payment_penalty()),
                    'duration_months': product.duration_months,
                    'min_duration': product.get_min_duration(),
                    'max_duration': product.get_max_duration(),
                    'available_repayment_methods': product.available_repayment_methods or ['monthly'],
                    'requires_guarantor': product.requires_guarantor,
                    'requires_collateral': product.requires_collateral,
                    'is_active': product.is_active
                })
        
        context = {
            'loan_products': loan_products,
            'loan_products_json': json.dumps(loan_products_data, cls=DjangoJSONEncoder),
            'clients': clients,
            'clients_json': json.dumps(clients_data, cls=DjangoJSONEncoder),
            'selected_client': client,
            'today': datetime.now().date()
        }
        return render(request, 'loans/new_application.html', context)
        
    except Exception as e:
        import traceback
        print("Error in loan application view:", str(e))
        print(traceback.format_exc())
        messages.error(request, f'An error occurred: {str(e)}')
        return redirect('loans:loans')


@login_required
def application_detail(request, pk):
    """Detail view for a loan application"""
    # Check if user has permission to access loans
    if not request.user.has_permission('loans', 'access'):
        messages.error(request, 'You do not have permission to view loan application details.')
        return redirect('loans:loans')
    application = get_object_or_404(LoanApplication, pk=pk)
    return render(request, 'loans/application_detail.html', {'application': application})


@login_required
def approve_application(request, pk):
    """Approve a loan application"""
    # Check if user has permission to approve loans
    if not request.user.has_permission('loans', 'approve'):
        messages.error(request, 'You do not have permission to approve loan applications.')
        return redirect('loans:loans')
    application = get_object_or_404(LoanApplication, pk=pk)
    
    # Check if borrower is blacklisted
    if application.borrower.status == 'blacklisted':
        messages.error(request, 'Cannot approve loan application for blacklisted client.')
        return redirect('loans:application_detail', pk=pk)
    
    # Check if borrower is suspended
    if application.borrower.status == 'suspended':
        messages.error(request, 'Cannot approve loan application for suspended client.')
        return redirect('loans:application_detail', pk=pk)
    
    # Check if borrower is inactive
    if application.borrower.status == 'inactive':
        messages.error(request, 'Cannot approve loan application for inactive client.')
        return redirect('loans:application_detail', pk=pk)
    
    # Get loan statistics
    defaulted_loans = application.borrower.loans.filter(status='defaulted').count()
    paid_loans = application.borrower.loans.filter(status='paid').count()
    
    # Get previous loans and repayment history
    from decimal import Decimal
    previous_loans = Loan.objects.filter(
        borrower=application.borrower
    ).select_related('borrower').order_by('-disbursement_date')
    
    # Calculate statistics and add repayment percentage to each loan
    loan_list = []
    total_borrowed = Decimal('0')
    total_repaid = Decimal('0')
    
    for loan in previous_loans[:10]:  # Show last 10 loans
        # Calculate repayment percentage
        if loan.total_amount and loan.total_amount > 0:
            repayment_pct = (loan.amount_paid / loan.total_amount * 100)
            loan.repayment_percentage = int(repayment_pct)
        else:
            loan.repayment_percentage = 0
        
        loan_list.append(loan)
        total_borrowed += loan.principal_amount
        total_repaid += loan.amount_paid
    
    active_loans_count = previous_loans.filter(status='active').count()
    completed_loans_count = previous_loans.filter(status='paid').count()
    
    if request.method == 'POST':
        # Handle disbursement date (admin only can set past dates)
        disbursement_date_str = request.POST.get('disbursement_date')
        disbursement_date = None
        
        if disbursement_date_str:
            try:
                disbursement_date = datetime.strptime(disbursement_date_str, '%Y-%m-%d')
                # Check if user is trying to set a past date
                if disbursement_date.date() < datetime.now().date():
                    if not request.user.is_admin():
                        messages.error(request, 'Only administrators can set past disbursement dates.')
                        return redirect('loans:approve_application', pk=pk)
            except ValueError:
                messages.error(request, 'Invalid disbursement date format.')
                return redirect('loans:approve_application', pk=pk)
        
        # Get edited loan details
        approved_amount = request.POST.get('approved_amount')
        approved_duration = request.POST.get('approved_duration')
        approved_purpose = request.POST.get('approved_purpose')
        edit_interest_rate = request.POST.get('edit_interest_rate')
        edit_processing_fee_rate = request.POST.get('edit_processing_fee_rate')
        edit_repayment_method = request.POST.get('edit_repayment_method')
        
        # Update application with edited values if provided
        if approved_amount:
            try:
                approved_amount_decimal = Decimal(approved_amount)
                # Validate amount is within product limits
                if approved_amount_decimal < application.loan_product.min_amount:
                    messages.error(request, f'Loan amount cannot be less than KES {application.loan_product.min_amount}')
                    return redirect('loans:approve_application', pk=pk)
                if approved_amount_decimal > application.loan_product.max_amount:
                    messages.error(request, f'Loan amount cannot exceed KES {application.loan_product.max_amount}')
                    return redirect('loans:approve_application', pk=pk)
                application.requested_amount = approved_amount_decimal
            except (ValueError, DecimalException):
                messages.error(request, 'Invalid loan amount provided.')
                return redirect('loans:approve_application', pk=pk)
        
        if approved_duration:
            try:
                approved_duration_int = int(approved_duration)
                # Validate duration is within product limits
                if approved_duration_int < application.loan_product.min_duration:
                    messages.error(request, f'Duration cannot be less than {application.loan_product.min_duration} days')
                    return redirect('loans:approve_application', pk=pk)
                if approved_duration_int > application.loan_product.max_duration:
                    messages.error(request, f'Duration cannot exceed {application.loan_product.max_duration} days')
                    return redirect('loans:approve_application', pk=pk)
                application.requested_duration = approved_duration_int
            except ValueError:
                messages.error(request, 'Invalid duration provided.')
                return redirect('loans:approve_application', pk=pk)
        
        if approved_purpose:
            application.purpose = approved_purpose
        
        # Handle custom interest rate
        if edit_interest_rate:
            try:
                custom_interest_rate = Decimal(edit_interest_rate)
                if custom_interest_rate < 0 or custom_interest_rate > 100:
                    messages.error(request, 'Interest rate must be between 0 and 100%')
                    return redirect('loans:approve_application', pk=pk)
                # Store custom rate for loan creation
                application.custom_interest_rate = custom_interest_rate
            except (ValueError, DecimalException):
                messages.error(request, 'Invalid interest rate provided.')
                return redirect('loans:approve_application', pk=pk)
        
        # Handle custom processing fee rate
        if edit_processing_fee_rate:
            try:
                custom_processing_fee = Decimal(edit_processing_fee_rate)
                if custom_processing_fee < 0 or custom_processing_fee > 100:
                    messages.error(request, 'Processing fee must be between 0 and 100%')
                    return redirect('loans:approve_application', pk=pk)
                # Store custom rate for loan creation
                application.custom_processing_fee = custom_processing_fee
            except (ValueError, DecimalException):
                messages.error(request, 'Invalid processing fee provided.')
                return redirect('loans:approve_application', pk=pk)
        
        # Handle repayment method
        if edit_repayment_method:
            if edit_repayment_method in application.loan_product.available_repayment_methods:
                application.repayment_method = edit_repayment_method
            else:
                messages.error(request, 'Invalid repayment method selected.')
                return redirect('loans:approve_application', pk=pk)
        
        # Recalculate loan amounts based on edited values
        application.calculate_loan_amounts()
        application.save()
        
        try:
            # Approve application and create loan
            loan = application.approve(request.user, request.POST.get('notes', ''), disbursement_date)
            
            # Create audit log
            AuditLog.objects.create(
                user=request.user,
                action='create',
                model_name='Loan',
                object_id=str(loan.id),
                description=f'Created loan {loan.loan_number} for {loan.borrower.get_full_name()}'
            )
            
            # Send loan approval SMS notification
            try:
                from payments.sms_service import send_loan_approval_sms
                send_loan_approval_sms(
                    borrower_name=loan.borrower.get_full_name(),
                    amount=float(loan.principal_amount),
                    loan_number=loan.loan_number,
                    customer_phone=loan.borrower.phone_number,
                )
            except Exception as sms_err:
                logger.warning(f"Loan approval SMS failed for {loan.loan_number}: {sms_err}")
            
            messages.success(request, f'Application approved and loan {loan.loan_number} created successfully.')
            return redirect('loans:loan_detail', pk=loan.pk)  # Redirect to loan detail page
            
        except IntegrityError as e:
            messages.error(request, 'This application has already been approved and a loan exists.')
            return redirect('loans:application_detail', pk=pk)
        except Exception as e:
            messages.error(request, f'Error approving application: {str(e)}')
            return redirect('loans:application_detail', pk=pk)
    
    return render(request, 'loans/approve_application.html', {
        'application': application,
        'defaulted_loans': defaulted_loans,
        'paid_loans': paid_loans,
        'previous_loans': loan_list,
        'total_borrowed': total_borrowed,
        'total_repaid': total_repaid,
        'active_loans_count': active_loans_count,
        'completed_loans_count': completed_loans_count,
        'today': datetime.now().date()
    })


@login_required
def reject_application(request, pk):
    """Reject a loan application"""
    # Check if user has permission to reject loans
    if not request.user.has_permission('loans', 'reject'):
        messages.error(request, 'You do not have permission to reject loan applications.')
        return redirect('loans:loans')
    application = get_object_or_404(LoanApplication, pk=pk)
    if request.method == 'POST':
        application.reject(request.user, request.POST.get('notes', ''))
        messages.success(request, 'Application rejected.')
        return redirect('loans:application_detail', pk=pk)
    
    return render(request, 'loans/reject_application.html', {'application': application})


@login_required
@portfolio_access_required
def loans(request):
    """View for managing all loans with filtering, pagination, and reporting"""
    # Check if user has permission to access loans
    if not request.user.has_permission('loans', 'access'):
        messages.error(request, 'You do not have permission to view loans.')
        return redirect('dashboard')
    # Get selected branch from session
    selected_branch_id = request.session.get('selected_branch_id')
    
    # Get all loan applications with related data, excluding deleted loans
    loans_list = LoanApplication.objects.all().select_related(
        'borrower',
        'loan_product',
        'loan'
    ).prefetch_related(
        'loan__repayments'
    ).filter(
        Q(loan__isnull=True) | Q(loan__is_deleted=False)  # Include applications without loans or with non-deleted loans
    )
    
    # Apply portfolio-based access control for staff members
    if request.user.role in ['loan_officer', 'team_leader'] and not request.user.is_superuser:
        # Staff members can only see loans from their assigned borrowers
        loans_list = loans_list.filter(borrower__portfolio_manager=request.user)
    elif request.user.role in ['secretary', 'auditor'] and not request.user.is_superuser:
        # Secretaries and auditors can only see loans from borrowers in their branch
        if request.user.branch:
            loans_list = loans_list.filter(borrower__branch=request.user.branch)
    
    # Apply branch filtering if a branch is selected
    if selected_branch_id:
        loans_list = loans_list.filter(borrower__branch_id=selected_branch_id)
    
    loans_list = loans_list.order_by('-submitted_at')
    
    # Apply filters
    status = request.GET.get('status')
    product_type = request.GET.get('product_type')
    search = request.GET.get('search')
    date_from = request.GET.get('date_from')
    date_to = request.GET.get('date_to')
    overdue = request.GET.get('overdue')
    
    # Redirect old overdue URL pattern to new cleaner pattern
    if overdue == 'true' and status in ['active', '', None]:
        # Build new URL with status=overdue instead of status=active&overdue=true
        from django.http import QueryDict
        new_params = request.GET.copy()
        new_params['status'] = 'overdue'
        if 'overdue' in new_params:
            del new_params['overdue']
        return redirect(f"{request.path}?{new_params.urlencode()}")
    
    if status:
        if status in ['active', 'paid', 'defaulted', 'rolled_over', 'written_off', 'overdue']:
            if status == 'active':
                # Only include active loans, not rolled over loans
                loans_list = loans_list.filter(loan__status='active')
            elif status == 'overdue':
                # Show overdue loans (active loans past their due date with outstanding amount > 0)
                # outstanding_amount is a @property, use DB fields: loan__total_amount > loan___amount_paid_cache
                from django.db.models import F
                loans_list = loans_list.filter(
                    loan__status='active',
                    loan__due_date__lt=timezone.now().date(),
                    loan__total_amount__gt=F('loan___amount_paid_cache')
                )
            else:
                loans_list = loans_list.filter(loan__status=status)
        elif status in ['pending', 'under_review', 'approved', 'rejected']:
            loans_list = loans_list.filter(status=status)
    else:
        # By default, exclude rolled over loans from the main loans view
        # Users can specifically view rolled over loans at /loans/rolled-over/
        loans_list = loans_list.exclude(loan__status='rolled_over')
    
    # Handle overdue parameter separately (for backward compatibility)
    # Only apply if status is not already 'overdue' to avoid duplicate filtering
    if overdue == 'true' and status not in ['overdue', 'paid', 'defaulted', 'rolled_over', 'written_off']:
        # When overdue=true is present, filter to show only overdue loans
        loans_list = loans_list.filter(
            loan__status='active',
            loan__due_date__lt=datetime.now()
        )
    
    if product_type:
        loans_list = loans_list.filter(loan_product__product_type=product_type)
    
    if search:
        loans_list = loans_list.filter(
            Q(application_number__icontains=search) |
            Q(loan__loan_number__icontains=search) |
            Q(borrower__first_name__icontains=search) |
            Q(borrower__last_name__icontains=search) |
            Q(borrower__email__icontains=search) |
            Q(borrower__phone_number__icontains=search) |
            Q(borrower__business_name__icontains=search)
        )

    if date_from:
        try:
            date_from = datetime.strptime(date_from, '%Y-%m-%d')
            loans_list = loans_list.filter(
                Q(submitted_at__date__gte=date_from) |
                Q(loan__disbursement_date__date__gte=date_from)
            )
        except ValueError:
            pass

    if date_to:
        try:
            date_to = datetime.strptime(date_to, '%Y-%m-%d')
            loans_list = loans_list.filter(
                Q(submitted_at__date__lte=date_to) |
                Q(loan__disbursement_date__date__lte=date_to)
            )
        except ValueError:
            pass
    
    # Get summary statistics
    loans_with_loan = loans_list.filter(loan__isnull=False)
    total_loans = loans_with_loan.count()
    # Only count truly active loans (exclude rolled over loans)
    active_loans = loans_with_loan.filter(loan__status='active').count()
    total_disbursed = loans_with_loan.aggregate(
        total=Sum('loan__principal_amount')
    )['total'] or 0
    total_collected = Repayment.objects.filter(
        loan__application__in=loans_with_loan
    ).aggregate(total=Sum('amount'))['total'] or 0
    
    # Advanced statistics
    advanced_stats = {
        'overdue_loans': loans_with_loan.filter(
            loan__status='active',  # Only truly active loans can be overdue
            loan__due_date__lt=datetime.now()
        ).count(),
        'default_rate': (
            loans_with_loan.filter(loan__status='defaulted').count() / 
            total_loans * 100
        ) if total_loans > 0 else 0,
        'avg_loan_amount': loans_with_loan.aggregate(
            avg=Avg('loan__principal_amount')
        )['avg'] or 0,
        'loans_by_product': loans_with_loan.values(
            'loan_product__product_type',
            'loan_product__name'
        ).annotate(
            count=Count('id'),
            total_amount=Sum('loan__principal_amount')
        ).order_by('loan_product__product_type'),
        'loans_by_status': loans_with_loan.values(
            'loan__status'
        ).annotate(
            count=Count('id'),
            total_amount=Sum('loan__principal_amount')
        ).order_by('loan__status'),
        'client_summary': loans_with_loan.values(
            'borrower__first_name',
            'borrower__last_name'
        ).annotate(
            total_loans=Count('id'),
            total_amount=Sum('loan__principal_amount'),
            active_loans=Count('loan', filter=Q(loan__status='active'))
        ).order_by('-total_loans')[:5]
    }
    
    # Handle report generation - TEMPORARILY DISABLED DUE TO RECURSION ERROR
    if False and request.GET.get('generate_report') == 'true':
        from .reports import generate_loan_statistics, generate_enhanced_charts, generate_enhanced_pdf_report
        
        # Get filter parameters for report
        product_type = request.GET.get('product_type')
        loan_product = None
        if product_type:
            loan_product = LoanProduct.objects.filter(product_type=product_type).first()
        
        borrower_id = request.GET.get('borrower')
        borrower = None
        if borrower_id:
            borrower = CustomUser.objects.filter(id=borrower_id).first()
        
        # Generate statistics and charts
        stats = generate_loan_statistics(
            start_date=date_from,
            end_date=date_to,
            borrower=borrower,
            loan_product=loan_product
        )
        charts = generate_enhanced_charts(stats)
        
        # Generate PDF
        pdf = generate_enhanced_pdf_report(stats, charts, report_type='general')
        
        # Return PDF response
        filename = "loan_report"
        if borrower:
            filename = f"loans_{borrower.get_full_name()}"
        if loan_product:
            filename += f"_{loan_product.name}"
        if date_from:
            filename += f"_from_{date_from}"
        if date_to:
            filename += f"_to_{date_to}"
        
        response = HttpResponse(pdf, content_type='application/pdf')
        response['Content-Disposition'] = f'attachment; filename="{filename}.pdf"'
        return response
    
    # Pagination
    paginator = Paginator(loans_list, 10)
    page = request.GET.get('page')
    try:
        loans = paginator.page(page)
    except PageNotAnInteger:
        loans = paginator.page(1)
    except EmptyPage:
        loans = paginator.page(paginator.num_pages)
    
    context = {
        'loans': loans,
        'total_loans': total_loans,
        'active_loans': active_loans,
        'total_disbursed': total_disbursed,
        'total_collected': total_collected,
        'advanced_stats': advanced_stats,
        'selected_status': status,
        'selected_product_type': product_type,
        'search_query': search,
        'date_from': date_from,
        'date_to': date_to,
        'loan_statuses': [
            ('pending', 'Pending'),
            ('under_review', 'Under Review'),
            ('approved', 'Approved'),
            ('rejected', 'Rejected'),
            ('active', 'Active'),
            ('paid', 'Paid'),
            ('defaulted', 'Defaulted'),
            ('written_off', 'Written Off'),
            ('rolled_over', 'Rolled Over')
        ],
        'product_types': [
            ('personal', 'Personal Loan'),
            ('business', 'Business Advance'),
            ('salary', 'Salary Loan'),
            ('emergency', 'Emergency Loan'),
            ('asset', 'Asset Finance')
        ]
    }
    
    return render(request, 'loans/loans.html', context)


@login_required
def update_loan_status(request, pk):
    """Update loan status (admin only)"""
    # Check if user has permission to edit loans
    if not request.user.has_permission('loans', 'edit'):
        messages.error(request, 'You do not have permission to update loan status.')
        return redirect('loans:loans')
    
    if not request.user.is_admin():
        messages.error(request, 'Only administrators can update loan status.')
        return redirect('loans:loans')
    
    loan = get_object_or_404(Loan, pk=pk)
    
    if request.method == 'POST':
        new_status = request.POST.get('status')
        reason = request.POST.get('reason', '')
        
        if not new_status:
            messages.error(request, 'Please select a status.')
            return redirect('loans:loans')
        
        try:
            # Validate status transition
            loan.validate_status_transition(new_status)
            
            old_status = loan.status
            
            # Handle specific status changes
            if new_status == 'defaulted':
                loan.mark_as_defaulted(reason)
            elif new_status == 'written_off':
                loan.write_off(reason)
            else:
                loan.status = new_status
                loan.save()
            
            # Create audit log
            AuditLog.objects.create(
                user=request.user,
                action='status_change',
                model_name='Loan',
                object_id=str(loan.id),
                description=f'Changed loan {loan.loan_number} status from {old_status} to {new_status}. Reason: {reason}'
            )
            
            messages.success(request, f'Loan status updated to {new_status}.')
            
        except ValueError as e:
            messages.error(request, str(e))
        except Exception as e:
            messages.error(request, f'Error updating loan status: {str(e)}')
    
    return redirect('loans:loans')


@login_required
def loan_detail(request, pk):
    """Detailed view of a loan with all related information and report generation"""
    # Check if user has permission to access loans
    if not request.user.has_permission('loans', 'access'):
        messages.error(request, 'You do not have permission to view loan details.')
        return redirect('loans:loans')
    
    try:
        loan = get_object_or_404(Loan.objects.select_related(
            'borrower',
            'application',
            'application__loan_product'
        ), pk=pk)
    except Http404:
        # Try to find the loan in rolled-over status
        loan = get_object_or_404(Loan.objects.select_related(
            'borrower',
            'application',
            'application__loan_product'
        ).filter(status='rolled_over'), pk=pk)
    
    # Get all repayments
    repayments = Repayment.objects.filter(loan=loan).order_by('-payment_date')
    
    # Get penalty charges
    penalty_charges = loan.penalty_charges.all().order_by('-applied_date')
    total_penalties = loan.total_penalties
    
    # Get rollover requests
    rollovers = RolloverRequest.objects.filter(loan=loan).order_by('-requested_at')
    
    # Calculate loan statistics
    total_paid = repayments.aggregate(total=Sum('amount'))['total'] or 0
    remaining_amount = loan.outstanding_amount  # This now includes penalties
    total_amount_with_penalties = loan.total_amount + total_penalties
    payment_progress = (total_paid / total_amount_with_penalties * 100) if total_amount_with_penalties > 0 else 0
    
    # Calculate current penalty if overdue
    current_penalty = loan.calculate_penalty() if loan.is_overdue else Decimal('0.00')
    
    # Calculate late payments
    late_payments = repayments.filter(
        payment_date__gt=loan.due_date
    ).count()
    
    # Get payment schedule using repayment scheduler
    payment_schedule = loan.get_payment_schedule()
    
    # Get arrears summary
    arrears_summary = loan.get_arrears_summary()
    
    # Get missed payment periods
    missed_periods = loan.get_missed_payment_periods()
    
    context = {
        'loan': loan,
        'repayments': repayments,
        'penalty_charges': penalty_charges,
        'total_penalties': total_penalties,
        'current_penalty': current_penalty,
        'rollovers': rollovers,
        'total_paid': total_paid,
        'remaining_amount': remaining_amount,
        'payment_progress': payment_progress,
        'payment_schedule': payment_schedule,
        'arrears_summary': arrears_summary,
        'missed_periods': missed_periods,
        'is_overdue': loan.is_overdue,
        'days_overdue': loan.days_overdue if loan.is_overdue else 0,
        'days_missed': loan.days_missed if loan.is_overdue else 0,
        'late_payments': late_payments,
        'penalty_rate': loan.application.loan_product.get_late_payment_penalty(),
        'repayment_method': loan.repayment_method,
        'expected_payment_amount': loan.expected_payment_amount,
        'payment_frequency_days': loan.payment_frequency_days,
        'next_payment_due_date': loan.next_payment_due_date,
        'payment_status': loan.payment_status,
    }
    
    # Handle report generation
    if request.GET.get('generate_report') == 'true':
        from .reports import generate_loan_statistics, generate_enhanced_charts, generate_enhanced_pdf_report
        
        # Generate statistics and charts
        stats = generate_loan_statistics(borrower=loan.borrower, loan_product=loan.application.loan_product)
        charts = generate_enhanced_charts(stats)
        
        # Generate PDF
        pdf = generate_enhanced_pdf_report(stats, charts, report_type='borrower', borrower=loan.borrower)
        
        # Return PDF response
        response = HttpResponse(pdf, content_type='application/pdf')
        response['Content-Disposition'] = f'attachment; filename="loan_{loan.loan_number}_report.pdf"'
        return response
    
    return render(request, 'loans/loan_detail.html', context)


@login_required
def disburse_loan(request, pk):
    """Disburse a loan"""
    # Check if user has permission to process loans
    if not request.user.has_permission('loans', 'process'):
        messages.error(request, 'You do not have permission to disburse loans.')
        return redirect('loans:loans')
    loan = get_object_or_404(Loan, pk=pk)
    if request.method == 'POST':
        # Handle disbursement logic
        messages.success(request, 'Loan disbursed successfully.')
        return redirect('loans:loan_detail', pk=pk)
    
    return render(request, 'loans/disburse_loan.html', {'loan': loan})


@login_required
@portfolio_access_required
def repayments(request):
    """List all repayments with filtering and search"""
    # Check if user has permission to access repayments
    if not request.user.has_permission('repayments', 'access'):
        messages.error(request, 'You do not have permission to view repayments.')
        return redirect('dashboard')
    # Get selected branch from session
    selected_branch_id = request.session.get('selected_branch_id')
    
    # Base queryset with branch filtering
    repayments_list = Repayment.objects.select_related(
        'loan', 'loan__borrower', 'loan__application__loan_product'
    )
    
    # Apply portfolio-based access control for staff members
    if request.user.role in ['loan_officer', 'team_leader'] and not request.user.is_superuser:
        # Staff members can only see repayments for their assigned borrowers
        repayments_list = repayments_list.filter(loan__borrower__portfolio_manager=request.user)
    elif request.user.role in ['secretary', 'auditor'] and not request.user.is_superuser:
        # Secretaries and auditors can only see repayments from borrowers in their branch
        if request.user.branch:
            repayments_list = repayments_list.filter(loan__borrower__branch=request.user.branch)
    elif selected_branch_id:
        # Admins and managers can filter by selected branch
        repayments_list = repayments_list.filter(loan__borrower__branch_id=selected_branch_id)
    
    repayments_list = repayments_list.order_by('-payment_date')
    
    # Search functionality
    search_query = request.GET.get('search', '')
    if search_query:
        repayments_list = repayments_list.filter(
            Q(loan__borrower__first_name__icontains=search_query) |
            Q(loan__borrower__last_name__icontains=search_query) |
            Q(loan__borrower__phone_number__icontains=search_query) |
            Q(loan__loan_number__icontains=search_query) |
            Q(payment_method__icontains=search_query)
        )
    
    # Date filtering
    start_date = request.GET.get('start_date')
    end_date = request.GET.get('end_date')
    if start_date:
        repayments_list = repayments_list.filter(payment_date__gte=start_date)
    if end_date:
        repayments_list = repayments_list.filter(payment_date__lte=end_date)
    
    # Payment method filtering
    payment_method = request.GET.get('payment_method')
    if payment_method:
        repayments_list = repayments_list.filter(payment_method=payment_method)
    
    # Payment source filtering (automatic vs manual)
    payment_source = request.GET.get('payment_source')
    if payment_source:
        repayments_list = repayments_list.filter(payment_source=payment_source)
    
    # Loan status filtering
    loan_status = request.GET.get('loan_status')
    if loan_status:
        repayments_list = repayments_list.filter(loan__status=loan_status)
    
    # Receipt status filtering
    receipt_status = request.GET.get('receipt_status')
    if receipt_status == 'with_receipt':
        repayments_list = repayments_list.filter(receipt__isnull=False)
    elif receipt_status == 'without_receipt':
        repayments_list = repayments_list.filter(receipt__isnull=True)
    
    # Pagination
    paginator = Paginator(repayments_list, 25)  # Show 25 repayments per page
    page = request.GET.get('page')
    try:
        repayments = paginator.page(page)
    except PageNotAnInteger:
        repayments = paginator.page(1)
    except EmptyPage:
        repayments = paginator.page(paginator.num_pages)
    
    # Get filter options for dropdowns (also filtered by branch)
    payment_methods_qs = Repayment.objects.all()
    loan_statuses_qs = Loan.objects.all()
    
    if selected_branch_id:
        payment_methods_qs = payment_methods_qs.filter(loan__borrower__branch_id=selected_branch_id)
        loan_statuses_qs = loan_statuses_qs.filter(borrower__branch_id=selected_branch_id)
    
    payment_methods = payment_methods_qs.values_list('payment_method', flat=True).distinct()
    loan_statuses = loan_statuses_qs.values_list('status', flat=True).distinct()
    
    context = {
        'repayments': repayments,
        'search_query': search_query,
        'start_date': start_date,
        'end_date': end_date,
        'payment_method': payment_method,
        'payment_source': payment_source,
        'loan_status': loan_status,
        'receipt_status': receipt_status,
        'payment_methods': payment_methods,
        'payment_sources': Repayment.PAYMENT_SOURCES,
        'loan_statuses': loan_statuses,
        'total_repayments': repayments_list.count(),
        'total_amount': repayments_list.aggregate(Sum('amount'))['amount__sum'] or 0,
    }
    
    return render(request, 'loans/repayments.html', context)


@login_required
def export_repayments_excel(request):
    """Export repayments to Excel"""
    # Check if user has permission to export repayments
    if not request.user.has_permission('repayments', 'export'):
        messages.error(request, 'You do not have permission to export repayment data.')
        return redirect('loans:repayments')
    try:
        from openpyxl import Workbook
        from openpyxl.styles import Font, PatternFill, Alignment
        from django.http import HttpResponse
        import io
        
        # Get selected branch from session
        selected_branch_id = request.session.get('selected_branch_id')
        
        # Base queryset with branch filtering
        repayments_list = Repayment.objects.select_related(
            'loan', 'loan__borrower', 'loan__application__loan_product'
        )
        
        # Apply branch filtering if a branch is selected
        if selected_branch_id:
            repayments_list = repayments_list.filter(loan__borrower__branch_id=selected_branch_id)
        
        # Apply filters from request
        search_query = request.GET.get('search', '')
        if search_query:
            repayments_list = repayments_list.filter(
                Q(loan__borrower__first_name__icontains=search_query) |
                Q(loan__borrower__last_name__icontains=search_query) |
                Q(loan__borrower__phone_number__icontains=search_query) |
                Q(loan__loan_number__icontains=search_query) |
                Q(payment_method__icontains=search_query)
            )
        
        start_date = request.GET.get('start_date')
        end_date = request.GET.get('end_date')
        if start_date:
            repayments_list = repayments_list.filter(payment_date__gte=start_date)
        if end_date:
            repayments_list = repayments_list.filter(payment_date__lte=end_date)
        
        payment_method = request.GET.get('payment_method')
        if payment_method:
            repayments_list = repayments_list.filter(payment_method=payment_method)
        
        loan_status = request.GET.get('loan_status')
        if loan_status:
            repayments_list = repayments_list.filter(loan__status=loan_status)
        
        receipt_status = request.GET.get('receipt_status')
        if receipt_status == 'with_receipt':
            repayments_list = repayments_list.filter(receipt__isnull=False)
        elif receipt_status == 'without_receipt':
            repayments_list = repayments_list.filter(receipt__isnull=True)
        
        repayments_list = repayments_list.order_by('-payment_date')
        
        # Create workbook
        wb = Workbook()
        ws = wb.active
        ws.title = "Repayments Report"
        
        # Define styles
        header_font = Font(bold=True, color="FFFFFF")
        header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
        center_alignment = Alignment(horizontal="center", vertical="center")
        
        # Headers
        headers = [
            'Payment Date', 'Borrower Name', 'Phone Number', 'Loan Number', 
            'Product', 'Amount (KSh)', 'Payment Method', 'Loan Status', 
            'Receipt Status', 'M-Pesa Transaction ID'
        ]
        
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = center_alignment
        
        # Data
        for row, repayment in enumerate(repayments_list, 2):
            try:
                # Safe access to related fields with comprehensive error handling
                payment_date = 'N/A'
                if repayment.payment_date:
                    try:
                        payment_date = repayment.payment_date.strftime('%Y-%m-%d %H:%M')
                    except:
                        payment_date = str(repayment.payment_date)
                
                borrower_name = 'N/A'
                if repayment.loan and repayment.loan.borrower:
                    try:
                        borrower_name = repayment.loan.borrower.get_full_name()
                    except:
                        borrower_name = f"{repayment.loan.borrower.first_name or ''} {repayment.loan.borrower.last_name or ''}".strip() or 'N/A'
                
                phone_number = 'N/A'
                if repayment.loan and repayment.loan.borrower:
                    phone_number = repayment.loan.borrower.phone_number or 'N/A'
                
                loan_number = 'N/A'
                if repayment.loan:
                    loan_number = repayment.loan.loan_number or 'N/A'
                
                # Safe access to loan product
                product_name = 'N/A'
                try:
                    if (repayment.loan and 
                        hasattr(repayment.loan, 'application') and 
                        repayment.loan.application and 
                        hasattr(repayment.loan.application, 'loan_product') and 
                        repayment.loan.application.loan_product):
                        product_name = repayment.loan.application.loan_product.name or 'N/A'
                except:
                    product_name = 'N/A'
                
                amount = 0.0
                try:
                    amount = float(repayment.amount) if repayment.amount else 0.0
                except:
                    amount = 0.0
                
                payment_method = repayment.payment_method or 'N/A'
                
                loan_status = 'N/A'
                try:
                    if repayment.loan and repayment.loan.status:
                        loan_status = repayment.loan.status.title()
                except:
                    loan_status = 'N/A'
                
                # Safe receipt status check - handle both receipt and receipt_set relationships
                receipt_status = 'No'
                try:
                    # Check multiple possible receipt relationships
                    if hasattr(repayment, 'receipt') and repayment.receipt:
                        receipt_status = 'Yes'
                    elif hasattr(repayment, 'receipt_set') and repayment.receipt_set.exists():
                        receipt_status = 'Yes'
                    elif hasattr(repayment, 'receipts') and repayment.receipts.exists():
                        receipt_status = 'Yes'
                except:
                    receipt_status = 'No'
                
                mpesa_id = repayment.mpesa_transaction_id or ''
                
                # Write data to Excel
                ws.cell(row=row, column=1, value=payment_date)
                ws.cell(row=row, column=2, value=borrower_name)
                ws.cell(row=row, column=3, value=phone_number)
                ws.cell(row=row, column=4, value=loan_number)
                ws.cell(row=row, column=5, value=product_name)
                ws.cell(row=row, column=6, value=amount)
                ws.cell(row=row, column=7, value=payment_method)
                ws.cell(row=row, column=8, value=loan_status)
                ws.cell(row=row, column=9, value=receipt_status)
                ws.cell(row=row, column=10, value=mpesa_id)
                
            except Exception as e:
                # Log the error but continue processing
                print(f"Skipping row {row} due to error: {e}")
                # Still write basic data even if there's an error
                try:
                    ws.cell(row=row, column=1, value='Error')
                    ws.cell(row=row, column=2, value=f'Repayment ID: {repayment.pk}')
                    ws.cell(row=row, column=3, value='Error')
                    ws.cell(row=row, column=4, value='Error')
                    ws.cell(row=row, column=5, value='Error')
                    ws.cell(row=row, column=6, value=0.0)
                    ws.cell(row=row, column=7, value='Error')
                    ws.cell(row=row, column=8, value='Error')
                    ws.cell(row=row, column=9, value='Error')
                    ws.cell(row=row, column=10, value='')
                except:
                    pass
                continue
        
        # Auto-adjust column widths
        for column in ws.columns:
            max_length = 0
            column_letter = column[0].column_letter
            for cell in column:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            adjusted_width = min(max_length + 2, 50)
            ws.column_dimensions[column_letter].width = adjusted_width
        
        # Save to response
        response = HttpResponse(
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        response['Content-Disposition'] = f'attachment; filename="repayments_export_{timezone.now().strftime("%Y%m%d")}.xlsx"'
        
        wb.save(response)
        return response
        
    except Exception as e:
        return HttpResponse(f"Error generating Excel file: {str(e)}", status=500)

@login_required
def export_repayments_pdf(request):
    """Export repayments to PDF with comprehensive analysis and charts"""
    # Check if user has permission to export repayments
    if not request.user.has_permission('repayments', 'export'):
        messages.error(request, 'You do not have permission to export repayment data.')
        return redirect('loans:repayments')
    try:
        from django.http import HttpResponse
        from django.template.loader import get_template
        from django.template import Context
        import io
        from reportlab.lib.pagesizes import letter, A4
        from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, PageBreak
        from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
        from reportlab.lib import colors
        from reportlab.lib.units import inch
        from reportlab.graphics.shapes import Drawing, Rect, String
        from reportlab.graphics.charts.barcharts import VerticalBarChart
        from reportlab.graphics.charts.piecharts import Pie
        from reportlab.graphics.charts.linecharts import HorizontalLineChart
        from reportlab.graphics import renderPDF
        from collections import defaultdict, Counter
        from datetime import datetime, timedelta
        import matplotlib.pyplot as plt
        import matplotlib
        matplotlib.use('Agg')  # Use non-interactive backend
        import base64
        from io import BytesIO
        
        # Get selected branch from session
        selected_branch_id = request.session.get('selected_branch_id')
        
        # Base queryset with branch filtering
        repayments_list = Repayment.objects.select_related(
            'loan', 'loan__borrower', 'loan__application__loan_product'
        )
        
        # Apply branch filtering if a branch is selected
        if selected_branch_id:
            repayments_list = repayments_list.filter(loan__borrower__branch_id=selected_branch_id)
        
        # Apply filters from request
        search_query = request.GET.get('search', '')
        if search_query:
            repayments_list = repayments_list.filter(
                Q(loan__borrower__first_name__icontains=search_query) |
                Q(loan__borrower__last_name__icontains=search_query) |
                Q(loan__borrower__phone_number__icontains=search_query) |
                Q(loan__loan_number__icontains=search_query) |
                Q(payment_method__icontains=search_query)
            )
        
        start_date = request.GET.get('start_date')
        end_date = request.GET.get('end_date')
        if start_date:
            repayments_list = repayments_list.filter(payment_date__gte=start_date)
        if end_date:
            repayments_list = repayments_list.filter(payment_date__lte=end_date)
        
        payment_method = request.GET.get('payment_method')
        if payment_method:
            repayments_list = repayments_list.filter(payment_method=payment_method)
        
        loan_status = request.GET.get('loan_status')
        if loan_status:
            repayments_list = repayments_list.filter(loan__status=loan_status)
        
        receipt_status = request.GET.get('receipt_status')
        if receipt_status == 'with_receipt':
            repayments_list = repayments_list.filter(receipt__isnull=False)
        elif receipt_status == 'without_receipt':
            repayments_list = repayments_list.filter(receipt__isnull=True)
        
        repayments_list = repayments_list.order_by('-payment_date')
        
        # Create PDF
        response = HttpResponse(content_type='application/pdf')
        response['Content-Disposition'] = f'attachment; filename="repayments_analysis_report_{timezone.now().strftime("%Y%m%d")}.pdf"'
        
        doc = SimpleDocTemplate(response, pagesize=A4)
        elements = []
        
        # Styles
        styles = getSampleStyleSheet()
        title_style = ParagraphStyle(
            'CustomTitle',
            parent=styles['Heading1'],
            fontSize=18,
            spaceAfter=30,
            alignment=1,  # Center alignment
            textColor=colors.darkblue
        )
        
        section_style = ParagraphStyle(
            'SectionTitle',
            parent=styles['Heading2'],
            fontSize=14,
            spaceAfter=12,
            spaceBefore=20,
            textColor=colors.darkgreen
        )
        
        # Title Page
        title = Paragraph("Loan Repayments Analysis Report", title_style)
        elements.append(title)
        elements.append(Spacer(1, 20))
        
        # Executive Summary
        summary_title = Paragraph("Executive Summary", section_style)
        elements.append(summary_title)
        
        # Calculate comprehensive statistics
        total_repayments = repayments_list.count()
        total_amount = repayments_list.aggregate(Sum('amount'))['amount__sum'] or 0
        avg_repayment = total_amount / total_repayments if total_repayments > 0 else 0
        
        # Payment method analysis
        payment_methods = repayments_list.values('payment_method').annotate(
            count=Count('id'),
            total_amount=Sum('amount')
        ).order_by('-total_amount')
        
        # Loan status analysis
        loan_statuses = repayments_list.values('loan__status').annotate(
            count=Count('id'),
            total_amount=Sum('amount')
        ).order_by('-total_amount')
        
        # Monthly trend analysis (last 12 months)
        end_date = timezone.now().date()
        start_date = end_date - timedelta(days=365)
        monthly_data = repayments_list.filter(
            payment_date__gte=start_date
        ).extra(
            select={'month': "DATE_FORMAT(payment_date, '%%Y-%%m-01')"}
        ).values('month').annotate(
            count=Count('id'),
            total_amount=Sum('amount')
        ).order_by('month')
        
        # Daily analysis (last 30 days)
        daily_start = end_date - timedelta(days=30)
        daily_data = repayments_list.filter(
            payment_date__gte=daily_start
        ).extra(
            select={'day': "DATE_FORMAT(payment_date, '%%Y-%%m-%%d')"}
        ).values('day').annotate(
            count=Count('id'),
            total_amount=Sum('amount')
        ).order_by('day')
        
        # Summary statistics
        summary_data = [
            ['Metric', 'Value'],
            ['Total Repayments', f"{total_repayments:,}"],
            ['Total Amount Collected', f"KSh {total_amount:,.2f}"],
            ['Average Repayment', f"KSh {avg_repayment:,.2f}"],
            ['Report Period', f"{start_date} to {end_date}"],
            ['Generated', timezone.now().strftime('%Y-%m-%d %H:%M')],
            ['Branch', 'All Branches' if not selected_branch_id else f"Branch ID: {selected_branch_id}"]
        ]
        
        summary_table = Table(summary_data, colWidths=[2.5*inch, 2.5*inch])
        summary_table.setStyle(TableStyle([
            ('BACKGROUND', (0, 0), (-1, 0), colors.darkblue),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, 0), 12),
            ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
            ('BACKGROUND', (0, 1), (-1, -1), colors.lightgrey),
            ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
            ('FONTSIZE', (0, 1), (-1, -1), 10),
            ('GRID', (0, 0), (-1, -1), 1, colors.black),
            ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
        ]))
        
        elements.append(summary_table)
        elements.append(Spacer(1, 20))
        
        # Payment Methods Analysis
        payment_title = Paragraph("Payment Methods Analysis", section_style)
        elements.append(payment_title)
        
        payment_data = [['Payment Method', 'Count', 'Total Amount', 'Percentage']]
        for method in payment_methods:
            try:
                method_name = method.get('payment_method', 'Unknown')
                count = method.get('count', 0) or 0
                method_total = method.get('total_amount', 0) or 0
                percentage = (method_total / total_amount * 100) if total_amount > 0 else 0
                payment_data.append([
                    method_name.title(),
                    str(count),
                    f"KSh {method_total:,.2f}",
                    f"{percentage:.1f}%"
                ])
            except (KeyError, TypeError, AttributeError):
                continue
        
        if len(payment_data) > 1:  # More than just header
            payment_table = Table(payment_data, colWidths=[1.5*inch, 1*inch, 1.5*inch, 1*inch])
            payment_table.setStyle(TableStyle([
                ('BACKGROUND', (0, 0), (-1, 0), colors.darkgreen),
                ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
                ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
                ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 0), (-1, 0), 10),
                ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
                ('BACKGROUND', (0, 1), (-1, -1), colors.beige),
                ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
                ('FONTSIZE', (0, 1), (-1, -1), 9),
                ('GRID', (0, 0), (-1, -1), 1, colors.black),
            ]))
            elements.append(payment_table)
        else:
            no_data = Paragraph("No payment method data available.", styles['Normal'])
            elements.append(no_data)
        elements.append(Spacer(1, 20))
        
        # Loan Status Analysis
        status_title = Paragraph("Loan Status Distribution", section_style)
        elements.append(status_title)
        
        status_data = [['Loan Status', 'Count', 'Total Amount', 'Percentage']]
        for status in loan_statuses:
            try:
                status_name = status.get('loan__status', 'Unknown')
                count = status.get('count', 0) or 0
                status_total = status.get('total_amount', 0) or 0
                percentage = (status_total / total_amount * 100) if total_amount > 0 else 0
                status_data.append([
                    status_name.title(),
                    str(count),
                    f"KSh {status_total:,.2f}",
                    f"{percentage:.1f}%"
                ])
            except (KeyError, TypeError, AttributeError):
                continue
        
        if len(status_data) > 1:  # More than just header
            status_table = Table(status_data, colWidths=[1.5*inch, 1*inch, 1.5*inch, 1*inch])
            status_table.setStyle(TableStyle([
                ('BACKGROUND', (0, 0), (-1, 0), colors.darkred),
                ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
                ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
                ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 0), (-1, 0), 10),
                ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
                ('BACKGROUND', (0, 1), (-1, -1), colors.lightblue),
                ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
                ('FONTSIZE', (0, 1), (-1, -1), 9),
                ('GRID', (0, 0), (-1, -1), 1, colors.black),
            ]))
            elements.append(status_table)
        else:
            no_data = Paragraph("No loan status data available.", styles['Normal'])
            elements.append(no_data)
        elements.append(Spacer(1, 20))
        
        # Monthly Trend Analysis
        trend_title = Paragraph("Monthly Repayment Trends (Last 12 Months)", section_style)
        elements.append(trend_title)
        
        if monthly_data:
            trend_data = [['Month', 'Repayments', 'Amount (KSh)']]
            for month in monthly_data:
                try:
                    # month['month'] is already a string from DATE_FORMAT
                    month_name = month.get('month', 'N/A') if month.get('month') else 'N/A'
                    count = month.get('count', 0) or 0
                    total_amount = month.get('total_amount', 0) or 0
                    trend_data.append([
                        month_name,
                        str(count),
                        f"{total_amount:,.0f}"
                    ])
                except (KeyError, TypeError, AttributeError):
                    # Skip invalid month data
                    continue
            
            if len(trend_data) > 1:  # More than just header
                trend_table = Table(trend_data, colWidths=[1.5*inch, 1.5*inch, 2*inch])
                trend_table.setStyle(TableStyle([
                    ('BACKGROUND', (0, 0), (-1, 0), colors.purple),
                    ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
                    ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
                    ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                    ('FONTSIZE', (0, 0), (-1, 0), 10),
                    ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
                    ('BACKGROUND', (0, 1), (-1, -1), colors.lightgrey),
                    ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
                    ('FONTSIZE', (0, 1), (-1, -1), 9),
                    ('GRID', (0, 0), (-1, -1), 1, colors.black),
                ]))
                elements.append(trend_table)
            else:
                no_data = Paragraph("No monthly trend data available.", styles['Normal'])
                elements.append(no_data)
        else:
            no_data = Paragraph("No monthly data available for the selected period.", styles['Normal'])
            elements.append(no_data)
        
        elements.append(Spacer(1, 20))
        
        # Key Insights
        insights_title = Paragraph("Key Insights & Recommendations", section_style)
        elements.append(insights_title)
        
        # Generate insights based on data
        insights = []
        
        # Payment method insights
        if payment_methods:
            try:
                top_method = payment_methods[0]
                method_name = top_method.get('payment_method', 'Unknown')
                count = top_method.get('count', 0) or 0
                method_total = top_method.get('total_amount', 0) or 0
                insights.append(f"• {method_name.title()} is the most popular payment method with {count} transactions ({method_total:,.0f} KSh)")
            except (IndexError, KeyError, TypeError):
                pass
        
        # Amount insights
        if total_amount > 0:
            insights.append(f"• Total collection of {total_amount:,.0f} KSh across {total_repayments} repayments")
            insights.append(f"• Average repayment amount is {avg_repayment:,.0f} KSh")
        
        # Status insights
        if loan_statuses:
            try:
                active_loans = next((s for s in loan_statuses if s.get('loan__status') == 'active'), None)
                if active_loans:
                    count = active_loans.get('count', 0) or 0
                    status_total = active_loans.get('total_amount', 0) or 0
                    insights.append(f"• {count} repayments from active loans ({status_total:,.0f} KSh)")
            except (KeyError, TypeError, AttributeError):
                pass
        
        # Monthly trend insights
        if len(monthly_data) >= 2:
            try:
                recent_month = monthly_data[len(monthly_data)-1]
                previous_month = monthly_data[len(monthly_data)-2]
                if recent_month['total_amount'] and previous_month['total_amount'] and previous_month['total_amount'] > 0:
                    growth = ((recent_month['total_amount'] - previous_month['total_amount']) / previous_month['total_amount']) * 100
                    insights.append(f"• Recent growth trend: {growth:.1f}% increase in collection from previous month")
            except (IndexError, KeyError, TypeError):
                pass  # Skip trend analysis if data is insufficient
        
        # Add recommendations
        insights.extend([
            "• Consider promoting the most successful payment methods",
            "• Monitor loan status distribution for risk management",
            "• Track monthly trends to identify seasonal patterns",
            "• Focus on converting pending repayments to completed status"
        ])
        
        for insight in insights:
            insight_para = Paragraph(insight, styles['Normal'])
            elements.append(insight_para)
            elements.append(Spacer(1, 6))
        
        elements.append(PageBreak())
        
        # Detailed Repayments Table
        details_title = Paragraph("Detailed Repayments List", section_style)
        elements.append(details_title)
        
        table_data = [['Date', 'Borrower', 'Loan #', 'Amount', 'Method', 'Status', 'Receipt']]
        
        for repayment in repayments_list[:50]:  # Limit to 50 records for PDF
            receipt_status = "Yes" if repayment.receipt else "No"
            table_data.append([
                repayment.payment_date.strftime('%Y-%m-%d'),
                repayment.loan.borrower.get_full_name()[:15],  # Truncate long names
                repayment.loan.loan_number[:10],  # Truncate loan numbers
                f"KSh {repayment.amount:,.0f}",
                repayment.payment_method[:8],  # Truncate method names
                repayment.loan.status.title()[:8],  # Truncate status
                receipt_status
            ])
        
        if len(repayments_list) > 50:
            table_data.append(['...', f'... and {len(repayments_list) - 50} more records', '', '', '', '', ''])
        
        repayments_table = Table(table_data, colWidths=[0.8*inch, 1.2*inch, 0.8*inch, 1*inch, 0.8*inch, 0.8*inch, 0.6*inch])
        repayments_table.setStyle(TableStyle([
            ('BACKGROUND', (0, 0), (-1, 0), colors.darkblue),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
            ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, 0), 8),
            ('BOTTOMPADDING', (0, 0), (-1, 0), 8),
            ('BACKGROUND', (0, 1), (-1, -1), colors.beige),
            ('GRID', (0, 0), (-1, -1), 1, colors.black),
            ('FONTSIZE', (0, 1), (-1, -1), 7),
            ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.lightgrey]),
        ]))
        
        elements.append(repayments_table)
        
        # Footer
        elements.append(Spacer(1, 20))
        footer_text = f"Report generated on {timezone.now().strftime('%Y-%m-%d %H:%M')} | HAVEN GRAZURI INVESTMENT LIMITED| Page 1 of 2"
        footer = Paragraph(footer_text, styles['Normal'])
        elements.append(footer)
        
        # Build PDF
        doc.build(elements)
        return response
        
    except Exception as e:
        return HttpResponse(f"Error generating PDF file: {str(e)}", status=500)

@login_required
def new_repayment(request):
    """Create a new repayment"""
    # Check if user has permission to create repayments
    if not request.user.has_permission('repayments', 'create'):
        messages.error(request, 'You do not have permission to record repayments.')
        return redirect('loans:repayments')
    
    if request.method == 'POST':
        try:
            loan_id = request.POST.get('loan')
            amount = Decimal(request.POST.get('amount'))
            payment_method = request.POST.get('payment_method')
            payment_date_str = request.POST.get('payment_date')
            mpesa_phone = request.POST.get('mpesa_phone', '').strip()
            mpesa_transaction_id = request.POST.get('mpesa_transaction_id', '').strip()
            notes = request.POST.get('notes', '').strip()
            
            if not loan_id or amount <= 0:
                raise ValueError("Invalid loan or amount")
            
            loan = get_object_or_404(Loan, id=loan_id, status='active')
            
            # Parse payment date
            if payment_date_str:
                try:
                    payment_date = datetime.strptime(payment_date_str, '%Y-%m-%dT%H:%M')
                    from django.conf import settings
                    if settings.USE_TZ:
                        payment_date = timezone.make_aware(payment_date)
                except ValueError:
                    if settings.USE_TZ:
                        payment_date = get_current_datetime()
                    else:
                        payment_date = datetime.now()
            else:
                from django.conf import settings
                if settings.USE_TZ:
                    payment_date = get_current_datetime()
                else:
                    payment_date = datetime.now()
            
            # Create repayment
            repayment = Repayment(
                loan=loan,
                amount=amount,
                payment_method=payment_method,
                payment_date=payment_date,
                mpesa_phone_number=mpesa_phone or None,
                mpesa_transaction_id=mpesa_transaction_id or None,
                notes=notes or None
            )
            repayment.save()
            
            # Create audit log
            from utils.models import AuditLog
            AuditLog.objects.create(
                user=request.user,
                action='payment',
                model_name='Repayment',
                object_id=str(repayment.id),
                description=f'Recorded payment of KES {amount:,.2f} for loan {loan.loan_number}'
            )
            
            # Send payment confirmation SMS
            try:
                from payments.sms_service import send_payment_confirmation_sms
                new_balance = float(loan.outstanding_balance) if hasattr(loan, 'outstanding_balance') else 0.0
                send_payment_confirmation_sms(
                    borrower_name=loan.borrower.get_full_name(),
                    amount=float(amount),
                    reference=repayment.receipt_number,
                    loan_number=loan.loan_number,
                    new_balance=new_balance,
                    customer_phone=loan.borrower.phone_number,
                )
            except Exception as sms_err:
                logger.warning(f"Payment confirmation SMS failed for {loan.loan_number}: {sms_err}")
            
            messages.success(request, f'Payment of KES {amount:,.2f} recorded successfully for loan {loan.loan_number}.')
            return redirect('loans:repayments')
            
        except (ValueError, DecimalException) as e:
            messages.error(request, f'Error recording payment: {str(e)}')
        except Exception as e:
            messages.error(request, f'Unexpected error: {str(e)}')
    
    # Only include truly active loans for new repayments
    loans = Loan.objects.filter(status='active').select_related('borrower').order_by('loan_number')
    return render(request, 'loans/new_repayment.html', {'loans': loans})


@login_required
def repayment_detail(request, pk):
    """Detail view for a repayment"""
    # Check if user has permission to access repayments
    if not request.user.has_permission('repayments', 'access'):
        messages.error(request, 'You do not have permission to view repayment details.')
        return redirect('loans:repayments')
    
    repayment = get_object_or_404(Repayment, pk=pk)
    return render(request, 'loans/repayment_detail.html', {'repayment': repayment})


@login_required
def rollovers(request):
    """List all rollover requests"""
    # Check if user has permission to access loans
    if not request.user.has_permission('loans', 'access'):
        messages.error(request, 'You do not have permission to view rollovers.')
        return redirect('loans:loans')
    
    rollovers = RolloverRequest.objects.select_related('loan', 'borrower').order_by('-requested_at')
    return render(request, 'loans/rollovers.html', {'rollovers': rollovers})


@login_required
def approve_rollover(request, pk):
    """Approve a rollover request with enhanced parameters"""
    # Check if user has permission to approve loans
    if not request.user.has_permission('loans', 'approve'):
        messages.error(request, 'You do not have permission to approve rollovers.')
        return redirect('loans:rollovers')
    rollover = get_object_or_404(RolloverRequest, pk=pk)
    
    if request.method == 'POST':
        notes = request.POST.get('notes', '')
        
        # Handle disbursement date (admin only can set past dates)
        disbursement_date_str = request.POST.get('disbursement_date')
        disbursement_date = None
        
        if disbursement_date_str:
            try:
                disbursement_date = datetime.strptime(disbursement_date_str, '%Y-%m-%d')
                # Check if user is trying to set a past date
                if disbursement_date.date() < datetime.now().date():
                    if not request.user.is_admin():
                        messages.error(request, 'Only administrators can set past disbursement dates.')
                        return redirect('loans:rollovers')
                # Convert to timezone-aware datetime for the new loan if USE_TZ is True
                from django.conf import settings
                if settings.USE_TZ:
                    disbursement_date = timezone.make_aware(disbursement_date)
            except ValueError:
                messages.error(request, 'Invalid disbursement date format.')
                return redirect('loans:rollovers')
        
        # Use the requested parameters from the rollover request
        new_amount = rollover.requested_amount
        new_duration = rollover.requested_duration
        new_interest_rate = rollover.requested_interest_rate
        new_processing_fee = rollover.requested_processing_fee
        
        # Allow admin to override parameters if provided in form
        override_amount = request.POST.get('override_amount')
        override_duration = request.POST.get('override_duration')
        override_interest_rate = request.POST.get('override_interest_rate')
        override_processing_fee = request.POST.get('override_processing_fee')
        
        if override_amount:
            try:
                new_amount = Decimal(override_amount)
            except (ValueError, DecimalException):
                messages.error(request, 'Invalid override amount format')
                return redirect('loans:rollovers')
        
        if override_duration:
            try:
                new_duration = int(override_duration)
            except ValueError:
                messages.error(request, 'Invalid override duration format')
                return redirect('loans:rollovers')
        
        if override_interest_rate:
            try:
                new_interest_rate = Decimal(override_interest_rate)
            except (ValueError, DecimalException):
                messages.error(request, 'Invalid override interest rate format')
                return redirect('loans:rollovers')
        
        if override_processing_fee:
            try:
                new_processing_fee = Decimal(override_processing_fee)
            except (ValueError, DecimalException):
                messages.error(request, 'Invalid override processing fee format')
                return redirect('loans:rollovers')
        
        # Process rollover approval with enhanced parameters
        new_loan = rollover.approve(
            approved_by=request.user,
            notes=notes,
            new_amount=new_amount,
            new_duration=new_duration,
            new_interest_rate=new_interest_rate,
            new_processing_fee=new_processing_fee,
            disbursement_date=disbursement_date
        )
        
        messages.success(request, f'Rollover approved! New active loan {new_loan.loan_number} created.')
        return redirect('loans:loan_detail', pk=new_loan.pk)
    
    return redirect('loans:rollovers')


@login_required
def reject_rollover(request, pk):
    """Reject a rollover request"""
    # Check if user has permission to reject loans
    if not request.user.has_permission('loans', 'reject'):
        messages.error(request, 'You do not have permission to reject rollovers.')
        return redirect('loans:rollovers')
    rollover = get_object_or_404(RolloverRequest, pk=pk)
    
    if request.method == 'POST':
        notes = request.POST.get('notes', '')
        
        # Process rollover rejection
        rollover.reject(
            rejected_by=request.user,
            notes=notes
        )
        
        messages.success(request, 'Rollover request rejected')
        return redirect('loans:loan_detail', pk=rollover.loan.pk)
    
    return redirect('loans:loan_detail', pk=rollover.loan.pk)


@login_required
def mpesa_transactions(request):
    """List all M-Pesa transactions"""
    transactions = MpesaTransaction.objects.order_by('-created_at')
    return render(request, 'loans/mpesa_transactions.html', {'transactions': transactions})


@login_required
def mpesa_detail(request, pk):
    """Detail view for an M-Pesa transaction"""
    transaction = get_object_or_404(MpesaTransaction, pk=pk)
    return render(request, 'loans/mpesa_detail.html', {'transaction': transaction})

@login_required
def record_repayment(request, pk):
    """Record a new repayment for a loan"""
    # Check if user has permission to create repayments
    if not request.user.has_permission('repayments', 'create'):
        messages.error(request, 'You do not have permission to record repayments.')
        return redirect('loans:loan_detail', pk=pk)
    
    loan = get_object_or_404(Loan, pk=pk)
    
    if request.method == 'POST':
        try:
            amount = Decimal(request.POST.get('amount'))
            payment_method = request.POST.get('payment_method')
            
            if amount <= 0:
                raise ValueError("Amount must be greater than 0")
            
            # Get payment date from form or use current time
            payment_date_str = request.POST.get('payment_date')
            if payment_date_str:
                try:
                    # Parse the datetime-local input format
                    payment_date = datetime.strptime(payment_date_str, '%Y-%m-%dT%H:%M')
                    # Handle timezone based on Django settings
                    from django.conf import settings
                    if settings.USE_TZ:
                        payment_date = timezone.make_aware(payment_date)
                except ValueError:
                    # If parsing fails, use current time
                    if settings.USE_TZ:
                        payment_date = get_current_datetime()
                    else:
                        payment_date = datetime.now()
            else:
                # Use appropriate datetime based on timezone settings
                from django.conf import settings
                if settings.USE_TZ:
                    payment_date = get_current_datetime()
                else:
                    payment_date = datetime.now()
            
            # Create repayment record with retry logic for receipt number conflicts
            max_attempts = 3
            for attempt in range(max_attempts):
                try:
                    # Use Django ORM instead of raw SQL to ensure save method is called
                    repayment = Repayment(
                        loan=loan,
                        amount=amount,
                        payment_method=payment_method,
                        payment_date=payment_date,
                        receipt_number=f"RCP-{timezone.now().strftime('%Y%m%d%H%M%S')}"
                    )
                    repayment.save()  # This will trigger the save method that updates loan.amount_paid
                    break  # Success, exit retry loop
                except IntegrityError as e:
                    if 'receipt_number' in str(e) and attempt < max_attempts - 1:
                        # Receipt number conflict, retry with slight delay
                        import time
                        time.sleep(0.1)
                        continue
                    else:
                        # Different error or max attempts reached
                        raise ValueError("Unable to generate unique receipt number. Please try again.")
            
            # Create audit log
            AuditLog.objects.create(
                user=request.user,
                action='payment',
                model_name='Repayment',
                object_id=str(repayment.id),
                description=f'Recorded payment of KES {amount:,.2f} for loan {loan.loan_number}'
            )
            
            # If M-Pesa, create transaction record
            if payment_method == 'mpesa':
                phone_number = request.POST.get('mpesa_phone')
                try:
                    # Create MpesaTransaction without the loan field to avoid missing loan_id column error
                    mpesa_transaction = MpesaTransaction(
                        transaction_type='c2b',
                        repayment=repayment,
                        phone_number=phone_number,
                        amount=amount,
                        status='success'
                    )
                    # Only set loan if the database column exists
                    # Since we have the repayment, we can get the loan through that relationship
                    # mpesa_transaction.loan = loan  # Commented out to avoid missing column error
                    mpesa_transaction.save()
                except Exception as e:
                    # Log the error but don't fail the entire repayment process
                    print(f"Warning: Could not create MpesaTransaction: {e}")
                    # The repayment was still successful, just the M-Pesa transaction logging failed
            
            # Loan payment tracking is handled automatically by the Repayment model's save method
            
            # Send payment confirmation SMS
            try:
                from payments.sms_service import send_payment_confirmation_sms
                new_balance = float(loan.outstanding_balance) if hasattr(loan, 'outstanding_balance') else 0.0
                send_payment_confirmation_sms(
                    borrower_name=loan.borrower.get_full_name(),
                    amount=float(amount),
                    reference=repayment.receipt_number,
                    loan_number=loan.loan_number,
                    new_balance=new_balance,
                    customer_phone=loan.borrower.phone_number,
                )
            except Exception as sms_err:
                logger.warning(f"Payment SMS failed for {loan.loan_number}: {sms_err}")
            
            messages.success(request, 'Payment recorded successfully', extra_tags='loan_detail')
            return redirect('loans:loan_detail', pk=pk)
            
        except (ValueError, DecimalException) as e:
            messages.error(request, str(e), extra_tags='loan_detail')
            return redirect('loans:loan_detail', pk=pk)
    
    return redirect('loans:loan_detail', pk=pk)

@login_required
def request_rollover(request, pk):
    """Request a loan rollover with enhanced options"""
    # Check if user has permission to create loans (for rollover requests)
    if not request.user.has_permission('loans', 'create'):
        messages.error(request, 'You do not have permission to request rollovers.')
        return redirect('loans:loan_detail', pk=pk)
    loan = get_object_or_404(Loan, pk=pk)
    
    if request.method == 'POST':
        try:
            # Get enhanced rollover data with proper decimal conversion
            requested_amount_str = request.POST.get('requested_amount', '0')
            requested_duration_str = request.POST.get('requested_duration', '0')
            interest_rate_str = request.POST.get('interest_rate', '0')
            processing_fee_str = request.POST.get('processing_fee', '0')
            rollover_date_str = request.POST.get('rollover_date', '').strip()
            reason = request.POST.get('reason', '').strip()
            
            # Validate and convert amounts with proper error handling
            try:
                requested_amount = Decimal(str(requested_amount_str).replace(',', ''))
                if requested_amount <= 0:
                    raise ValueError("Amount must be greater than 0")
            except (ValueError, TypeError, DecimalException) as e:
                messages.error(request, f'Invalid loan amount: {requested_amount_str}', extra_tags='loan_detail')
                return redirect('loans:loan_detail', pk=pk)
            
            try:
                requested_duration = int(requested_duration_str)
                if requested_duration <= 0:
                    raise ValueError("Duration must be greater than 0 days")
            except (ValueError, TypeError) as e:
                messages.error(request, f'Invalid duration: {requested_duration_str}', extra_tags='loan_detail')
                return redirect('loans:loan_detail', pk=pk)
            
            try:
                interest_rate = Decimal(str(interest_rate_str).replace(',', '')) if interest_rate_str else Decimal('0')
            except (ValueError, TypeError, DecimalException):
                interest_rate = Decimal('0')
            
            try:
                processing_fee = Decimal(str(processing_fee_str).replace(',', '')) if processing_fee_str else Decimal('0')
            except (ValueError, TypeError, DecimalException):
                processing_fee = Decimal('0')
            
            # Parse rollover date
            rollover_date = None
            if rollover_date_str:
                try:
                    from datetime import datetime
                    rollover_date = datetime.strptime(rollover_date_str, '%Y-%m-%d').date()
                except ValueError:
                    messages.error(request, 'Invalid rollover date format', extra_tags='loan_detail')
                    return redirect('loans:loan_detail', pk=pk)
            
            if not reason:
                messages.error(request, 'Please provide a reason for the rollover', extra_tags='loan_detail')
                return redirect('loans:loan_detail', pk=pk)
            
            # Calculate rollover fee based on loan product
            rollover_fee_percentage = loan.application.loan_product.rollover_fee_percentage
            rollover_fee = (loan.outstanding_amount * rollover_fee_percentage) / Decimal('100')
            
            # Create enhanced rollover request
            rollover_request = RolloverRequest.objects.create(
                loan=loan,
                borrower=loan.borrower,  # Use loan borrower, not request user
                requested_amount=requested_amount,
                requested_duration=requested_duration,
                requested_interest_rate=interest_rate if interest_rate > 0 else None,
                requested_processing_fee=processing_fee if processing_fee > 0 else None,
                rollover_date=rollover_date,
                reason=reason,
                rollover_fee=rollover_fee
            )
            
            messages.success(request, 'Enhanced rollover request submitted successfully', extra_tags='loan_detail')
            return redirect('loans:loan_detail', pk=pk)
            
        except (ValueError, DecimalException) as e:
            messages.error(request, str(e), extra_tags='loan_detail')
            return redirect('loans:loan_detail', pk=pk)
    
    return redirect('loans:loan_detail', pk=pk)

@login_required
def add_existing_loan(request):
    """Add an existing loan to the system, with client search on GET"""
    from users.models import CustomUser
    if request.method == 'POST':
        try:
            # Get form data
            client = get_object_or_404(CustomUser, pk=request.POST.get('client'), role='borrower')
            loan_number = request.POST.get('loan_number')
            amount = Decimal(request.POST.get('amount'))
            disbursement_date = request.POST.get('disbursement_date')
            due_date = request.POST.get('due_date')
            status = request.POST.get('status')
            # Create loan application
            application = LoanApplication.objects.create(
                borrower=client,
                loan_product=LoanProduct.objects.first(),  # Use default product for existing loans
                requested_amount=amount,
                status='approved',
                reviewed_by=request.user,
                reviewed_at=datetime.now(),
                approval_notes='Added as existing loan'
            )
            # Create loan record
            loan = Loan.objects.create(
                application=application,
                borrower=client,
                loan_number=loan_number,
                principal_amount=amount,
                total_amount=amount,  # For existing loans, use principal as total
                disbursement_date=disbursement_date,
                due_date=due_date,
                status=status,
                created_by=request.user
            )
            # Create audit log
            AuditLog.objects.create(
                user=request.user,
                action='create',
                model_name='Loan',
                object_id=str(loan.id),
                description=f'Added existing loan for {client.get_full_name()}'
            )
            messages.success(request, 'Existing loan added successfully!', extra_tags='loan_detail')
            return redirect('loans:loan_detail', pk=loan.pk)
        except Exception as e:
            messages.error(request, f'Error adding loan: {str(e)}', extra_tags='loans')
    # GET: Render form with client search
    borrowers = CustomUser.objects.filter(role='borrower', is_active=True)
    return render(request, 'loans/add_existing_loan.html', {'borrowers': borrowers})

@login_required
def api_loan_data(request):
    """API endpoint for loan data with enhanced analytics"""
    period = request.GET.get('period', '6m')
    selected_branch_id = request.session.get('selected_branch_id')
    today = datetime.now().date()
    
    # Determine date range based on period
    if period == '1w':
        months_back = 1
        start_date = today - timedelta(days=7)
    elif period == '1m':
        months_back = 1
        start_date = today - timedelta(days=30)
    elif period == '3m':
        months_back = 3
        start_date = today - timedelta(days=90)
    elif period == '6m':
        months_back = 6
        start_date = today - timedelta(days=180)
    elif period == '1y':
        months_back = 12
        start_date = today - timedelta(days=365)
    elif period == '2y':
        months_back = 24
        start_date = today - timedelta(days=730)
    elif period == '5y':
        months_back = 60
        start_date = today - timedelta(days=1825)
    elif period == 'all':
        months_back = 120  # 10 years max
        start_date = today - timedelta(days=3650)
    else:  # 6m default
        months_back = 6
        start_date = today - timedelta(days=180)
    
    # Base querysets with branch filtering
    loans_qs = Loan.objects.all()
    repayments_qs = Repayment.objects.all()
    
    if selected_branch_id:
        loans_qs = loans_qs.filter(borrower__branch_id=selected_branch_id)
        repayments_qs = repayments_qs.filter(loan__borrower__branch_id=selected_branch_id)
    
    # Get time-based data
    labels = []
    disbursements = []
    repayments = []
    
    # Determine the appropriate time interval and data points
    if period in ['1w']:
        # Daily data for 1 week
        data_points = 7
        for i in range(data_points):
            day_start = today - timedelta(days=i)
            day_end = day_start + timedelta(days=1)
            day_start = datetime.combine(day_start, datetime.min.time())
            day_end = datetime.combine(day_end, datetime.min.time())
            
            disbursed = loans_qs.filter(
                created_at__gte=day_start,
                created_at__lt=day_end
            ).aggregate(total=Sum('principal_amount'))['total'] or 0
            
            repaid = repayments_qs.filter(
                payment_date__gte=day_start,
                payment_date__lt=day_end
            ).aggregate(total=Sum('amount'))['total'] or 0
            
            day_label = day_start.strftime('%a %d')
            labels.insert(0, day_label)
            disbursements.insert(0, float(disbursed))
            repayments.insert(0, float(repaid))
            
    elif period in ['1m']:
        # Weekly data for 1 month
        data_points = 4
        for i in range(data_points):
            week_start = today - timedelta(weeks=i)
            week_end = week_start + timedelta(weeks=1)
            week_start = datetime.combine(week_start, datetime.min.time())
            week_end = datetime.combine(week_end, datetime.min.time())
            
            disbursed = loans_qs.filter(
                created_at__gte=week_start,
                created_at__lt=week_end
            ).aggregate(total=Sum('principal_amount'))['total'] or 0
            
            repaid = repayments_qs.filter(
                payment_date__gte=week_start,
                payment_date__lt=week_end
            ).aggregate(total=Sum('amount'))['total'] or 0
            
            week_label = f"Week {week_start.strftime('%d %b')}"
            labels.insert(0, week_label)
            disbursements.insert(0, float(disbursed))
            repayments.insert(0, float(repaid))
    else:
        # Monthly data for longer periods
        for i in range(months_back):
            # Calculate month boundaries
            month_start = datetime.now().replace(day=1) - timedelta(days=30*i)
            month_start = datetime.combine(month_start.date(), datetime.min.time())
            
            # Get next month start for end boundary
            if month_start.month == 12:
                next_month = month_start.replace(year=month_start.year + 1, month=1, day=1)
            else:
                next_month = month_start.replace(month=month_start.month + 1, day=1)
            
            # Query disbursements for this month
            disbursed = loans_qs.filter(
                created_at__gte=month_start,
                created_at__lt=next_month
            ).aggregate(total=Sum('principal_amount'))['total'] or 0
            
            # Query repayments for this month
            repaid = repayments_qs.filter(
                payment_date__gte=month_start,
                payment_date__lt=next_month
            ).aggregate(total=Sum('amount'))['total'] or 0
            
            # Format month label
            month_label = month_start.strftime('%b %Y')
            
            # Insert at beginning to maintain chronological order
            labels.insert(0, month_label)
            disbursements.insert(0, float(disbursed))
            repayments.insert(0, float(repaid))
    
    # Calculate additional metrics
    total_disbursed = sum(disbursements)
    total_repaid = sum(repayments)
    net_flow = total_disbursed - total_repaid
    
    # Get loan status distribution (not filtered by time period)
    distribution_loans_qs = Loan.objects.all()
    if selected_branch_id:
        distribution_loans_qs = distribution_loans_qs.filter(borrower__branch_id=selected_branch_id)
    
    loan_statuses = distribution_loans_qs.values('status').annotate(count=Count('id')).order_by('status')
    status_labels = []
    status_data = []
    status_colors = []
    
    color_map = {
        'active': '#10B981',      # Green
        'paid': '#3B82F6',        # Blue  
        'defaulted': '#EF4444',   # Red
        'rolled_over': '#F59E0B', # Yellow
        'pending': '#8B5CF6'      # Purple
    }
    
    for status in loan_statuses:
        status_labels.append(status['status'].title())
        status_data.append(status['count'])
        status_colors.append(color_map.get(status['status'], '#6B7280'))
    
    # Get client growth data (filtered by time period for growth chart)
    users_qs = CustomUser.objects.filter(role='borrower')
    if selected_branch_id:
        users_qs = users_qs.filter(branch_id=selected_branch_id)
    
    client_growth = []
    for i in range(months_back):
        month_start = datetime.now().replace(day=1) - timedelta(days=30*i)
        month_start = datetime.combine(month_start.date(), datetime.min.time())
        
        if month_start.month == 12:
            next_month = month_start.replace(year=month_start.year + 1, month=1, day=1)
        else:
            next_month = month_start.replace(month=month_start.month + 1, day=1)
        
        new_clients = users_qs.filter(
            date_joined__gte=month_start,
            date_joined__lt=next_month
        ).count()
        
        client_growth.insert(0, new_clients)
    
    return JsonResponse({
        'success': True,
        'period': period,
        'labels': labels,
        'disbursements': disbursements,
        'repayments': repayments,
        'summary': {
            'total_disbursed': total_disbursed,
            'total_repaid': total_repaid,
            'net_flow': net_flow,
            'months_count': months_back
        },
        'distribution': {
            'labels': status_labels,
            'data': status_data,
            'colors': status_colors
        },
        'client_growth': {
            'labels': labels,
            'data': client_growth
        }
    })

@login_required
@portfolio_access_required
def loans_ajax(request):
    """AJAX endpoint for loans list with filters"""
    try:
        # Get selected branch from session
        selected_branch_id = request.session.get('selected_branch_id')
        
        # Get filter parameters
        search = request.GET.get('search', '').strip()
        status_filter = request.GET.get('status', '')
        product_type_filter = request.GET.get('product_type', '')
        date_from = request.GET.get('date_from', '')
        date_to = request.GET.get('date_to', '')
        
        # Start with all loans and applications
        loans = Loan.objects.select_related('borrower', 'loan_product').all()
        applications = LoanApplication.objects.select_related('borrower', 'loan_product').all()
        
        # Apply portfolio-based access control for staff members
        if request.user.role in ['loan_officer', 'team_leader'] and not request.user.is_superuser:
            # Staff members can only see loans from their assigned borrowers
            loans = loans.filter(borrower__portfolio_manager=request.user)
            applications = applications.filter(borrower__portfolio_manager=request.user)
        elif request.user.role in ['secretary', 'auditor'] and not request.user.is_superuser:
            # Secretaries and auditors can only see loans from borrowers in their branch
            if request.user.branch:
                loans = loans.filter(borrower__branch=request.user.branch)
                applications = applications.filter(borrower__branch=request.user.branch)
        
        # Apply branch filtering if a branch is selected
        if selected_branch_id:
            loans = loans.filter(borrower__branch_id=selected_branch_id)
            applications = applications.filter(borrower__branch_id=selected_branch_id)
        
        # Apply search filter
        if search:
            loans = loans.filter(
                Q(loan_number__icontains=search) |
                Q(borrower__first_name__icontains=search) |
                Q(borrower__last_name__icontains=search) |
                Q(borrower__business_name__icontains=search) |
                Q(borrower__phone_number__icontains=search)
            )
            applications = applications.filter(
                Q(application_number__icontains=search) |
                Q(borrower__first_name__icontains=search) |
                Q(borrower__last_name__icontains=search) |
                Q(borrower__business_name__icontains=search) |
                Q(borrower__phone_number__icontains=search)
            )
        
        # Apply status filter
        if status_filter:
            loans = loans.filter(status=status_filter)
            applications = applications.filter(status=status_filter)
        
        # Apply product type filter
        if product_type_filter:
            loans = loans.filter(loan_product__product_type=product_type_filter)
            applications = applications.filter(loan_product__product_type=product_type_filter)
        
        # Apply date filters
        if date_from:
            loans = loans.filter(created_at__gte=date_from)
            applications = applications.filter(submitted_at__gte=date_from)
        
        if date_to:
            loans = loans.filter(created_at__lte=date_to)
            applications = applications.filter(submitted_at__lte=date_to)
        
        # Combine and order by creation date
        all_items = list(loans) + list(applications)
        all_items.sort(key=lambda x: x.created_at if hasattr(x, 'created_at') else x.submitted_at, reverse=True)
        
        # Render the table HTML
        from django.template.loader import render_to_string
        html = render_to_string('loans/loans_table.html', {
            'loans': all_items,
            'request': request
        }, request=request)
        
        return JsonResponse({
            'success': True,
            'html': html,
            'count': len(all_items)
        })
        
    except Exception as e:
        return JsonResponse({
            'success': False,
            'error': str(e)
        })


@login_required
def api_recent_activity(request):
    """API endpoint for recent activity"""
    # Get recent activity with naive datetime queries
    recent_activity = AuditLog.objects.select_related('user').order_by('-created_at')[:10]
    
    activities = []
    for activity in recent_activity:
        activity_type = activity.action
        icon = {
            'create': 'plus-circle',
            'update': 'edit',
            'delete': 'trash',
            'payment': 'money-bill-wave',
            'login': 'sign-in-alt',
            'logout': 'sign-out-alt'
        }.get(activity_type, 'info')
        
        activities.append({
            'type': activity_type,
            'user': activity.user.get_full_name() if activity.user else 'System',
            'action': activity.description,
            'timestamp': activity.created_at.isoformat(),
            'icon': icon
        })
    
    return JsonResponse({'activities': activities})

@login_required
@admin_only
def edit_loan(request, pk):
    """
    Edit an existing loan (admin only)
    Requirements: Security Requirements 1, 2, 3
    """
    from .validators import LoanEditValidator
    from django.core.exceptions import ValidationError as DjangoValidationError
    
    loan = get_object_or_404(Loan, pk=pk)
    
    # Get available status choices for the dropdown
    status_choices = Loan.STATUS_CHOICES
    
    if request.method == 'POST':
        try:
            # Use validator to validate all form inputs
            # Requirements: 3.12, 10.7, 10.8
            validated_data = LoanEditValidator.validate_loan_edit_form(request.POST)
            
            # Track changes for audit log
            changes = []
            old_principal = loan.principal_amount
            old_interest_amount = loan.interest_amount
            old_processing_fee = loan.processing_fee
            old_status = loan.status
            old_disbursement_date = loan.disbursement_date
            old_due_date = loan.due_date
            
            # Get status from POST data directly (validator might not include it)
            new_status = request.POST.get('status', old_status)
            
            # Validate status transition if status is changing
            if new_status != old_status:
                try:
                    loan.validate_status_transition(new_status)
                    
                    # Additional validation for 'paid' status
                    if new_status == 'paid':
                        total_owed = loan.total_amount + loan.total_penalties
                        if loan.amount_paid < total_owed:
                            messages.error(request, 
                                f'Cannot mark as paid: amount paid (KES {loan.amount_paid:,.2f}) '
                                f'is less than total owed (KES {total_owed:,.2f})')
                            return render(request, 'loans/edit_loan.html', {
                                'loan': loan,
                                'status_choices': status_choices
                            })
                    
                    changes.append(f'Status: {old_status} → {new_status}')
                    loan.status = new_status
                except ValueError as e:
                    messages.error(request, str(e))
                    return render(request, 'loans/edit_loan.html', {
                        'loan': loan,
                        'status_choices': status_choices
                    })
            
            # Update loan amounts
            if old_principal != validated_data['principal_amount']:
                changes.append(f'Principal: KES {old_principal:,.2f} → KES {validated_data["principal_amount"]:,.2f}')
            if old_interest_amount != validated_data['interest_amount']:
                changes.append(f'Interest: KES {old_interest_amount:,.2f} → KES {validated_data["interest_amount"]:,.2f}')
            if old_processing_fee != validated_data['processing_fee']:
                changes.append(f'Processing Fee: KES {old_processing_fee:,.2f} → KES {validated_data["processing_fee"]:,.2f}')
            
            # Debug logging
            print(f"DEBUG: Validated data - Principal: {validated_data['principal_amount']}, Interest: {validated_data['interest_amount']}, Processing Fee: {validated_data['processing_fee']}")
            print(f"DEBUG: Old values - Principal: {old_principal}, Interest: {old_interest_amount}, Processing Fee: {old_processing_fee}")
            
            loan.principal_amount = validated_data['principal_amount']
            loan.interest_amount = validated_data['interest_amount']
            loan.processing_fee = validated_data['processing_fee']
            
            print(f"DEBUG: After assignment - Principal: {loan.principal_amount}, Interest: {loan.interest_amount}, Processing Fee: {loan.processing_fee}")
            print(f"DEBUG: Total before save: {loan.total_amount}")
            
            # Update dates
            if validated_data['disbursement_date'] != old_disbursement_date.date():
                changes.append(f'Disbursement Date: {old_disbursement_date.strftime("%Y-%m-%d")} → {validated_data["disbursement_date"].strftime("%Y-%m-%d")}')
                loan.disbursement_date = make_datetime_compatible(
                    datetime.combine(validated_data['disbursement_date'], datetime.min.time())
                )
            
            if validated_data['due_date'] != old_due_date.date():
                changes.append(f'Due Date: {old_due_date.strftime("%Y-%m-%d")} → {validated_data["due_date"].strftime("%Y-%m-%d")}')
                loan.due_date = make_datetime_compatible(
                    datetime.combine(validated_data['due_date'], datetime.min.time())
                )
            
            # Just save the loan - the pre_save signal will recalculate total_amount
            loan.save()
            
            # Refresh from database to get the updated total
            loan.refresh_from_db()
            print(f"DEBUG: Total after save and refresh: {loan.total_amount}")
            
            # Create audit log entry
            if changes:
                AuditLog.objects.create(
                    user=request.user,
                    action='update',
                    model_name='Loan',
                    object_id=str(loan.pk),
                    description=f'Updated loan {loan.loan_number}: {"; ".join(changes)}'
                )
            
            # Show detailed success message with new total
            messages.success(request, 
                f'Loan {loan.loan_number} has been updated successfully. '
                f'New total: KES {loan.total_amount:,.2f} '
                f'(Principal: KES {loan.principal_amount:,.2f} + '
                f'Interest: KES {loan.interest_amount:,.2f} + '
                f'Processing Fee: KES {loan.processing_fee:,.2f})')
            return redirect('loans:loan_detail', pk=loan.pk)
            
        except DjangoValidationError as e:
            # Validation errors from our validator
            messages.error(request, str(e))
            return render(request, 'loans/edit_loan.html', {
                'loan': loan,
                'status_choices': status_choices
            })
        except (ValueError, DecimalException) as e:
            messages.error(request, 'Invalid input. Please check your values and try again.')
            return render(request, 'loans/edit_loan.html', {
                'loan': loan,
                'status_choices': status_choices
            })
        except Exception as e:
            messages.error(request, f'An error occurred while updating the loan: {str(e)}')
            return render(request, 'loans/edit_loan.html', {
                'loan': loan,
                'status_choices': status_choices
            })
    
    return render(request, 'loans/edit_loan.html', {
        'loan': loan,
        'status_choices': status_choices
    })


@login_required
def delete_loan(request, pk):
    """Soft delete a loan (admin only)"""
    # Check if user has permission to delete loans
    if not request.user.has_permission('loans', 'delete'):
        messages.error(request, 'You do not have permission to delete loans.')
        return redirect('loans:loans')
    
    if not request.user.is_admin():
        messages.error(request, 'You do not have permission to delete loans.')
        return redirect('loans:loans')
    
    loan = get_object_or_404(Loan, pk=pk)
    
    if request.method == 'POST':
        try:
            loan_number = loan.loan_number
            borrower_name = loan.borrower.get_full_name()
            
            # Log the deletion
            AuditLog.objects.create(
                user=request.user,
                action='soft_delete',
                model_name='Loan',
                object_id=str(loan.pk),
                description=f'Soft deleted loan {loan_number} for {borrower_name}'
            )
            
            # Soft delete the loan
            loan.soft_delete(deleted_by=request.user)
            
            messages.success(request, f'Loan {loan_number} has been moved to deleted loans.')
            return redirect('loans:loans')
            
        except Exception as e:
            messages.error(request, f'An error occurred while deleting the loan: {str(e)}')
            return redirect('loans:loan_detail', pk=loan.pk)
    
    return render(request, 'loans/delete_loan.html', {'loan': loan})

@login_required
def deleted_loans(request):
    """
    View deleted loans (admin only)
    
    Implements Requirement 5.5: Display ONLY loans where is_deleted equals true.
    Ensures no active loans appear on this page.
    
    Property 15: Deleted loans page exclusivity - For any loan displayed on the
    deleted loans page, it should have is_deleted flag equal to true, and no
    loans with is_deleted equal to false should appear.
    """
    if not request.user.is_admin():
        messages.error(request, 'You do not have permission to view deleted loans.')
        return redirect('loans:loans')
    
    # Get selected branch from session
    selected_branch_id = request.session.get('selected_branch_id')
    
    # Get all deleted loans (Requirement 5.5: ONLY loans with is_deleted=True)
    # This filter ensures complete exclusivity - no active loans will appear
    deleted_loans_queryset = Loan.objects.filter(is_deleted=True).select_related(
        'borrower', 'application__loan_product', 'deleted_by'
    )
    
    # Apply branch filtering
    if selected_branch_id:
        deleted_loans_queryset = deleted_loans_queryset.filter(borrower__branch_id=selected_branch_id)
    elif hasattr(request.user, 'branch') and request.user.branch and not request.user.is_superuser:
        deleted_loans_queryset = deleted_loans_queryset.filter(borrower__branch=request.user.branch)
    # Superuser without branch selection sees all deleted loans
    
    deleted_loans_queryset = deleted_loans_queryset.order_by('-deleted_at')
    
    # Search functionality
    search_query = request.GET.get('search', '')
    if search_query:
        deleted_loans_queryset = deleted_loans_queryset.filter(
            Q(loan_number__icontains=search_query) |
            Q(borrower__first_name__icontains=search_query) |
            Q(borrower__last_name__icontains=search_query) |
            Q(borrower__phone_number__icontains=search_query)
        )
    
    # Pagination
    paginator = Paginator(deleted_loans_queryset, 25)
    page = request.GET.get('page')
    try:
        deleted_loans = paginator.page(page)
    except PageNotAnInteger:
        deleted_loans = paginator.page(1)
    except EmptyPage:
        deleted_loans = paginator.page(paginator.num_pages)
    
    context = {
        'deleted_loans': deleted_loans,
        'search_query': search_query,
        'total_deleted': deleted_loans_queryset.count(),
    }
    
    return render(request, 'loans/deleted_loans.html', context)


@login_required
def restore_loan(request, pk):
    """Restore a soft-deleted loan (admin only)"""
    if not request.user.is_admin():
        messages.error(request, 'You do not have permission to restore loans.')
        return redirect('loans:deleted_loans')
    
    loan = get_object_or_404(Loan, pk=pk, is_deleted=True)
    
    if request.method == 'POST':
        try:
            loan_number = loan.loan_number
            borrower_name = loan.borrower.get_full_name()
            
            # Log the restoration
            AuditLog.objects.create(
                user=request.user,
                action='restore',
                model_name='Loan',
                object_id=str(loan.pk),
                description=f'Restored loan {loan_number} for {borrower_name}'
            )
            
            # Restore the loan
            loan.restore()
            
            messages.success(request, f'Loan {loan_number} has been restored successfully.')
            return redirect('loans:loans')
            
        except Exception as e:
            messages.error(request, f'Error restoring loan: {str(e)}')
            return redirect('loans:deleted_loans')
    
    return render(request, 'loans/restore_loan.html', {'loan': loan})


@login_required
def permanently_delete_loan(request, pk):
    """Permanently delete a loan (admin only)"""
    if not request.user.is_admin():
        messages.error(request, 'You do not have permission to permanently delete loans.')
        return redirect('loans:deleted_loans')
    
    loan = get_object_or_404(Loan, pk=pk, is_deleted=True)
    
    # Get repayment count for confirmation message
    repayment_count = loan.repayments.count()
    
    if request.method == 'POST':
        try:
            loan_number = loan.loan_number
            borrower_name = loan.borrower.get_full_name()
            
            # Log the permanent deletion
            AuditLog.objects.create(
                user=request.user,
                action='permanent_delete',
                model_name='Loan',
                object_id=str(loan.pk),
                description=f'Permanently deleted loan {loan_number} for {borrower_name}'
            )
            
            # Permanently delete the loan (this will cascade delete repayments)
            loan.delete()
            
            if repayment_count > 0:
                messages.success(request, f'Loan {loan_number} and its {repayment_count} repayment(s) have been permanently deleted.')
            else:
                messages.success(request, f'Loan {loan_number} has been permanently deleted.')
            return redirect('loans:deleted_loans')
            
        except Exception as e:
            messages.error(request, f'Error permanently deleting loan: {str(e)}')
            return redirect('loans:deleted_loans')
    
    return render(request, 'loans/permanently_delete_loan.html', {'loan': loan})


@login_required
def rolled_over_loans(request):
    """
    View for rolled over loans with filtering
    Requirements: 11.1, 11.2, 11.3, 11.4, 11.5
    """
    from reports.filter_service import ReportFilterService
    from loans.models import LoanProduct
    
    selected_branch_id = request.session.get('selected_branch_id')
    
    # Parse filter parameters
    filter_params = ReportFilterService.parse_filter_params(request)
    
    # Base queryset - only rolled over loans
    loans_qs = Loan.objects.filter(Q(status='rolled_over') | Q(is_rolled_over=True))
    
    # Exclude soft-deleted loans from statistics (Requirement 11.5)
    loans_qs = loans_qs.filter(is_deleted=False)
    
    # Apply rollover date range filter (Requirement 11.2)
    if filter_params.start_date and filter_params.end_date:
        loans_qs = ReportFilterService.apply_date_range_filter(
            loans_qs,
            filter_params.start_date,
            filter_params.end_date,
            date_field='updated_at'  # Use updated_at as rollover date
        )
    
    # Apply loan product filter (Requirement 11.3)
    if filter_params.product_id:
        loans_qs = ReportFilterService.apply_loan_product_filter(
            loans_qs,
            filter_params.product_id
        )
    
    # Apply branch filtering if a branch is selected
    if selected_branch_id:
        loans_qs = loans_qs.filter(borrower__branch_id=selected_branch_id)
    
    # Select related to optimize queries
    loans_qs = loans_qs.select_related(
        'borrower',
        'application',
        'application__loan_product'
    ).prefetch_related('borrower__loans')
    
    # Order by most recent rollover
    loans_qs = loans_qs.order_by('-updated_at')
    
    # Calculate statistics (excluding deleted loans)
    total_count = loans_qs.count()
    total_amount = loans_qs.aggregate(total=Sum('principal_amount'))['total'] or 0
    total_rollover_fees = sum(
        loan.application.loan_product.rollover_fee_percentage / 100 * loan.principal_amount
        for loan in loans_qs
    ) if loans_qs.exists() else 0
    
    # Pagination
    paginator = Paginator(loans_qs, 25)
    page = request.GET.get('page')
    try:
        loans = paginator.page(page)
    except PageNotAnInteger:
        loans = paginator.page(1)
    except EmptyPage:
        loans = paginator.page(paginator.num_pages)
    
    # Get loan products for filter dropdown
    loan_products = LoanProduct.objects.filter(is_active=True).order_by('name')
    
    # Get branches for filter dropdown
    try:
        from users.models import Branch
        branches = Branch.objects.filter(is_active=True).order_by('name')
    except:
        branches = []
    
    # Handle export requests
    export_format = request.GET.get('export')
    if export_format:
        from reports.export_service import ReportExportService
        export_service = ReportExportService()
        
        # Prepare report data for export
        report_data = {
            'loans': [
                {
                    'loan_number': loan.loan_number,
                    'borrower_name': loan.borrower.get_full_name(),
                    'borrower_phone': loan.borrower.phone_number,
                    'principal_amount': loan.principal_amount,
                    'processing_fee': loan.application.loan_product.rollover_fee_percentage / 100 * loan.principal_amount if loan.application and loan.application.loan_product else 0,
                    'disbursement_date': loan.updated_at,
                }
                for loan in loans_qs
            ]
        }
        
        filters = {
            'start_date': filter_params.start_date,
            'end_date': filter_params.end_date,
            'product_id': filter_params.product_id,
        }
        
        if export_format == 'excel':
            return export_service.export_to_excel(report_data, 'rolled_over_loans', filters)
        elif export_format == 'pdf':
            return export_service.export_to_pdf(report_data, 'rolled_over_loans', filters)
    
    context = {
        'loans': loans,
        'status_filter': 'rolled_over',
        'title': 'Rolled Over Loans',
        'total_count': total_count,
        'total_amount': total_amount,
        'total_rollover_fees': total_rollover_fees,
        'today': timezone.now().date(),
        'loan_products': loan_products,
        'branches': branches,
        'filter_params': filter_params,
        'selected_branch_id': selected_branch_id,
    }
    
    return render(request, 'loans/rolled_over_loans.html', context)


@login_required
def edit_repayment(request, pk):
    """Edit repayment details"""
    repayment = get_object_or_404(Repayment, pk=pk)
    
    # Check permissions - only staff can edit repayments
    if not request.user.is_staff:
        messages.error(request, 'You do not have permission to edit repayments.')
        return redirect('loans:repayments')
    
    if request.method == 'POST':
        try:
            # Get form data
            amount = Decimal(request.POST.get('amount', '0'))
            payment_method = request.POST.get('payment_method')
            payment_date = request.POST.get('payment_date')
            mpesa_transaction_id = request.POST.get('mpesa_transaction_id', '').strip()
            mpesa_phone_number = request.POST.get('mpesa_phone_number', '').strip()
            
            # Validate amount
            if amount <= 0:
                messages.error(request, 'Amount must be greater than zero.')
                return render(request, 'loans/edit_repayment.html', {
                    'repayment': repayment,
                    'payment_methods': Repayment.PAYMENT_METHODS
                })
            
            # Store old values for loan recalculation
            old_loan = repayment.loan
            old_amount = repayment.amount
            
            # Update repayment
            repayment.amount = amount
            repayment.payment_method = payment_method
            if payment_date:
                repayment.payment_date = timezone.datetime.strptime(payment_date, '%Y-%m-%dT%H:%M')
            repayment.mpesa_transaction_id = mpesa_transaction_id or None
            repayment.mpesa_phone_number = mpesa_phone_number or None
            
            # Save the repayment (this will trigger loan recalculation via signal)
            repayment.save()
            
            # Force loan recalculation to ensure amounts are updated
            old_loan.recalculate_amounts()
            old_loan.save()
            
            # Create audit log
            AuditLog.objects.create(
                user=request.user,
                action='update',
                model_name='Repayment',
                object_id=str(repayment.pk),
                description=f'Updated repayment {repayment.receipt_number}: Amount changed from KES {old_amount:,.2f} to KES {amount:,.2f}'
            )
            
            messages.success(request, f'Repayment {repayment.receipt_number} updated successfully.')
            return redirect('loans:loan_detail', pk=repayment.loan.pk)
            
        except Exception as e:
            messages.error(request, f'Error updating repayment: {str(e)}')
    
    context = {
        'repayment': repayment,
        'payment_methods': Repayment.PAYMENT_METHODS
    }
    
    return render(request, 'loans/edit_repayment.html', context)


@login_required
def delete_repayment(request, pk):
    """Delete repayment"""
    # Check if user has permission to delete repayments
    if not request.user.has_permission('repayments', 'delete'):
        messages.error(request, 'You do not have permission to delete repayments.')
        return redirect('loans:repayments')
    repayment = get_object_or_404(Repayment, pk=pk)
    
    # Check permissions - only staff can delete repayments
    if not request.user.is_staff:
        messages.error(request, 'You do not have permission to delete repayments.')
        return redirect('loans:repayments')
    
    if request.method == 'POST':
        try:
            loan = repayment.loan
            receipt_number = repayment.receipt_number
            
            # Delete the repayment (this will cascade delete the receipt)
            repayment.delete()
            
            # Recalculate loan totals
            from django.db.models import Sum
            loan.amount_paid = loan.repayments.aggregate(
                total=Sum('amount')
            )['total'] or Decimal('0.00')
            
            # Update loan status if needed
            if loan.amount_paid < loan.total_amount and loan.status == 'paid':
                loan.status = 'active'
            
            loan.save()
            
            messages.success(request, f'Repayment {receipt_number} deleted successfully.')
            return redirect('loans:loan_detail', pk=loan.pk)
            
        except Exception as e:
            messages.error(request, f'Error deleting repayment: {str(e)}')
            return redirect('loans:loan_detail', pk=repayment.loan.pk)
    
    context = {
        'repayment': repayment
    }
    
    return render(request, 'loans/delete_repayment.html', context)


@login_required
def refresh_loan_payments(request, pk):
    """Refresh and sync all payments for a specific loan"""
    loan = get_object_or_404(Loan, pk=pk)
    
    try:
        from django.db import connection, transaction
        from django.http import JsonResponse
        
        with transaction.atomic():
            with connection.cursor() as cursor:
                # Strategy 1: Update repayments that have matching mpesa transactions
                # First, get the borrower's phone number
                borrower_phone = loan.borrower.phone_number
                
                cursor.execute("""
                    UPDATE repayments r
                    INNER JOIN mpesa_transactions mt ON r.id = mt.repayment_id
                    SET r.loan_id = %s
                    WHERE (r.loan_id IS NULL OR r.loan_id = '')
                    AND mt.phone_number = %s
                    AND r.payment_date BETWEEN %s AND %s
                """, [
                    str(loan.id).replace('-', ''),
                    borrower_phone,
                    loan.disbursement_date - timedelta(days=1) if loan.disbursement_date else timezone.now() - timedelta(days=365),
                    timezone.now() + timedelta(days=1)
                ])
                
                updated_count = cursor.rowcount
                
                # Strategy 2: Match by date range for remaining orphaned repayments
                cursor.execute("""
                    UPDATE repayments r
                    SET r.loan_id = %s
                    WHERE (r.loan_id IS NULL OR r.loan_id = '')
                    AND r.payment_date BETWEEN %s AND %s
                    AND r.id NOT IN (
                        SELECT DISTINCT r2.id FROM (
                            SELECT id FROM repayments WHERE loan_id = %s
                        ) r2
                    )
                    LIMIT 100
                """, [
                    str(loan.id).replace('-', ''),
                    loan.disbursement_date - timedelta(days=1) if loan.disbursement_date else timezone.now() - timedelta(days=365),
                    timezone.now() + timedelta(days=1),
                    str(loan.id).replace('-', '')
                ])
                
                additional_count = cursor.rowcount
                total_synced = updated_count + additional_count
        
        # Recalculate loan totals
        loan.refresh_from_db()
        
        # Create audit log
        AuditLog.objects.create(
            user=request.user,
            action='refresh_payments',
            model_name='Loan',
            object_id=str(loan.id),
            description=f'Refreshed payments for loan {loan.loan_number} - synced {total_synced} payments'
        )
        
        return JsonResponse({
            'success': True,
            'synced_count': total_synced,
            'updated_count': updated_count,
            'additional_count': additional_count,
            'message': f'Successfully synced {total_synced} payments for loan {loan.loan_number}.' if total_synced > 0 else f'No orphaned payments found for loan {loan.loan_number}.'
        })
            
    except Exception as e:
        return JsonResponse({
            'success': False,
            'error': str(e)
        })
    

@login_required
def refresh_all_payments(request):
    """Refresh and sync all payments for all loans"""
    if not request.user.is_admin():
        return JsonResponse({
            'success': False,
            'error': 'You do not have permission to refresh all payments.'
        })
    
    try:
        from django.db import connection, transaction
        from django.http import JsonResponse
        
        with transaction.atomic():
            with connection.cursor() as cursor:
                # Strategy 1: Direct phone number lookup to avoid subquery issues
                cursor.execute("""
                    UPDATE repayments r
                    INNER JOIN mpesa_transactions mt ON r.id = mt.repayment_id
                    INNER JOIN loans l ON mt.phone_number = (
                        SELECT u.phone_number 
                        FROM users u 
                        WHERE u.id = l.borrower_id
                    )
                    SET r.loan_id = l.id
                    WHERE (r.loan_id IS NULL OR r.loan_id = '')
                    AND r.payment_date BETWEEN DATE_SUB(l.disbursement_date, INTERVAL 1 DAY) 
                                           AND DATE_ADD(NOW(), INTERVAL 1 DAY)
                    LIMIT 500
                """)
                
                strategy1_count = cursor.rowcount
                
                # Strategy 2: Use a more direct approach with temporary table
                cursor.execute("""
                    CREATE TEMPORARY TABLE temp_payment_matches AS
                    SELECT r.id as repayment_id, 
                           (SELECT l.id 
                            FROM loans l 
                            INNER JOIN users u ON l.borrower_id = u.id
                            WHERE l.is_deleted = 0
                            AND r.payment_date BETWEEN DATE_SUB(l.disbursement_date, INTERVAL 1 DAY) 
                                                   AND DATE_ADD(l.due_date, INTERVAL 30 DAY)
                            ORDER BY ABS(DATEDIFF(r.payment_date, l.disbursement_date))
                            LIMIT 1) as loan_id
                    FROM repayments r
                    WHERE (r.loan_id IS NULL OR r.loan_id = '')
                    LIMIT 1000
                """)
                
                # Update using the temporary table
                cursor.execute("""
                    UPDATE repayments r
                    INNER JOIN temp_payment_matches tpm ON r.id = tpm.repayment_id
                    SET r.loan_id = tpm.loan_id
                    WHERE tpm.loan_id IS NOT NULL
                    AND (r.loan_id IS NULL OR r.loan_id = '')
                """)
                
                strategy2_count = cursor.rowcount
                
                # Clean up temporary table
                cursor.execute("DROP TEMPORARY TABLE IF EXISTS temp_payment_matches")
                
                total_synced = strategy1_count + strategy2_count
        
        # Create audit log
        AuditLog.objects.create(
            user=request.user,
            action='refresh_all_payments',
            model_name='System',
            object_id='system',
            description=f'Refreshed all loan payments - synced {total_synced} payments (Strategy 1: {strategy1_count}, Strategy 2: {strategy2_count})'
        )
        
        return JsonResponse({
            'success': True,
            'total_synced': total_synced,
            'strategy1_count': strategy1_count,
            'strategy2_count': strategy2_count,
            'loans_updated': total_synced,
            'message': f'Successfully synced {total_synced} orphaned payments across all loans.' if total_synced > 0 else 'No orphaned payments found to sync.'
        })
            
    except Exception as e:
        return JsonResponse({
            'success': False,
            'error': str(e)
        })


@login_required
def completed_loans(request):
    """View for managing completed (paid) loans with filtering, pagination, and reporting"""
    
    # Get selected branch from session
    selected_branch_id = request.session.get('selected_branch_id')
    
    # Get all loan applications with related data where loan status is 'paid'
    loans_list = LoanApplication.objects.all().select_related(
        'borrower',
        'loan_product',
        'loan'
    ).prefetch_related(
        'loan__repayments'
    ).filter(
        loan__status='paid',
        loan__is_deleted=False
    )
    
    # Apply branch filtering
    if selected_branch_id:
        loans_list = loans_list.filter(borrower__branch_id=selected_branch_id)
    elif hasattr(request.user, 'branch') and request.user.branch and not request.user.is_superuser:
        loans_list = loans_list.filter(borrower__branch=request.user.branch)
    # Superuser without branch selection sees all loans
    
    loans_list = loans_list.order_by('-loan__due_date')
    
    # Apply filters
    product_type = request.GET.get('product_type')
    search = request.GET.get('search')
    date_from = request.GET.get('date_from')
    date_to = request.GET.get('date_to')
    
    if product_type:
        loans_list = loans_list.filter(loan_product__product_type=product_type)
    
    if search:
        loans_list = loans_list.filter(
            Q(application_number__icontains=search) |
            Q(loan__loan_number__icontains=search) |
            Q(borrower__first_name__icontains=search) |
            Q(borrower__last_name__icontains=search) |
            Q(borrower__email__icontains=search) |
            Q(borrower__phone_number__icontains=search) |
            Q(borrower__business_name__icontains=search)
        )

    if date_from:
        try:
            date_from = datetime.strptime(date_from, '%Y-%m-%d')
            loans_list = loans_list.filter(loan__disbursement_date__date__gte=date_from)
        except ValueError:
            pass

    if date_to:
        try:
            date_to = datetime.strptime(date_to, '%Y-%m-%d')
            loans_list = loans_list.filter(loan__due_date__date__lte=date_to)
        except ValueError:
            pass
    
    # Get summary statistics
    total_loans = loans_list.count()
    total_principal = loans_list.aggregate(
        total=Sum('loan__principal_amount')
    )['total'] or 0
    total_interest = loans_list.aggregate(
        total=Sum('loan__interest_amount')
    )['total'] or 0
    total_collected = loans_list.aggregate(
        total=Sum('loan__total_amount')
    )['total'] or 0
    
    # Advanced statistics
    advanced_stats = {
        'avg_loan_amount': loans_list.aggregate(
            avg=Avg('loan__principal_amount')
        )['avg'] or 0,
        'loans_by_product': loans_list.values(
            'loan_product__product_type'
        ).annotate(
            count=Count('id'),
            total_amount=Sum('loan__principal_amount')
        ).order_by('-count'),
        'client_summary': loans_list.values(
            'borrower__first_name',
            'borrower__last_name'
        ).annotate(
            total_loans=Count('id'),
            total_amount=Sum('loan__principal_amount')
        ).order_by('-total_amount')[:5],
    }
    
    # Pagination
    paginator = Paginator(loans_list, 25)  # Show 25 loans per page
    page = request.GET.get('page')
    try:
        loans = paginator.page(page)
    except PageNotAnInteger:
        loans = paginator.page(1)
    except EmptyPage:
        loans = paginator.page(paginator.num_pages)
    
    # Generate report if requested
    if request.GET.get('generate_report') == 'true':
        from reports.views import generate_loans_report
        return generate_loans_report(request, loans_list, 'Completed Loans Report')
    
    context = {
        'loans': loans,
        'total_loans': total_loans,
        'total_principal': total_principal,
        'total_interest': total_interest,
        'total_collected': total_collected,
        'advanced_stats': advanced_stats,
        'search_query': search,
        'selected_product_type': product_type,
        'date_from': date_from.strftime('%Y-%m-%d') if isinstance(date_from, datetime) else '',
        'date_to': date_to.strftime('%Y-%m-%d') if isinstance(date_to, datetime) else '',
        'today': timezone.now().date(),
    }
    
    return render(request, 'loans/completed_loans.html', context)


@login_required
def loan_receipt(request, pk):
    """Generate a loan receipt for a specific loan"""
    loan = get_object_or_404(Loan, pk=pk)
    
    # Get all repayments for this loan
    repayments = Repayment.objects.filter(loan=loan).order_by('payment_date')
    
    # Calculate totals using stored values
    total_paid = repayments.aggregate(total=Sum('amount'))['total'] or Decimal('0.00')
    # Use stored values for accurate calculations
    current_interest = loan.interest_amount
    current_processing_fee = loan.processing_fee
    current_total = loan.total_amount
    remaining_balance = current_total - total_paid
    
    context = {
        'loan': loan,
        'repayments': repayments,
        'total_paid': total_paid,
        'remaining_balance': remaining_balance,
        'current_total': current_total,
        'generated_at': timezone.now(),
    }
    
    return render(request, 'loans/loan_receipt.html', context)


@login_required
@portfolio_access_required
def loan_amortization(request, pk):
    """Generate a beautiful loan amortization schedule"""
    loan = get_object_or_404(Loan, pk=pk)
    
    # Check if user has access to this loan
    if request.user.role in ['loan_officer', 'team_leader'] and not request.user.is_superuser:
        if loan.borrower.portfolio_manager != request.user:
            messages.error(request, 'You do not have permission to view this loan.')
            return redirect('loans:loans')
    elif request.user.role in ['secretary', 'auditor'] and not request.user.is_superuser:
        if request.user.branch and loan.borrower.branch != request.user.branch:
            messages.error(request, 'You do not have permission to view this loan.')
            return redirect('loans:loans')
    
    # Get existing repayments
    existing_repayments = Repayment.objects.filter(loan=loan).order_by('payment_date')
    
    # Calculate amortization schedule
    amortization_schedule = loan.get_amortization_schedule()
    
    # Calculate summary statistics using stored values
    total_principal = loan.principal_amount or Decimal('0.00')
    total_interest = loan.interest_amount
    total_processing_fee = loan.processing_fee
    total_amount = loan.total_amount
    
    # Calculate paid amounts
    total_paid = existing_repayments.aggregate(total=Sum('amount'))['total'] or Decimal('0.00')
    remaining_balance = total_amount - total_paid
    
    # Calculate payment statistics
    total_payments_made = existing_repayments.count()
    total_scheduled_payments = len(amortization_schedule)
    payments_remaining = total_scheduled_payments - total_payments_made
    
    context = {
        'loan': loan,
        'amortization_schedule': amortization_schedule,
        'existing_repayments': existing_repayments,
        'total_principal': total_principal,
        'total_interest': total_interest,
        'total_processing_fee': total_processing_fee,
        'total_amount': total_amount,
        'total_paid': total_paid,
        'remaining_balance': remaining_balance,
        'total_payments_made': total_payments_made,
        'total_scheduled_payments': total_scheduled_payments,
        'payments_remaining': payments_remaining,
        'generated_at': timezone.now(),
    }

    return render(request, 'loans/loan_amortization.html', context)


# =======================
# INTEREST SCHEDULE VIEW
# =======================

from django.shortcuts import render, get_object_or_404, redirect
from django.contrib.auth.decorators import login_required
from decimal import Decimal

@login_required
def interest_schedule(request, pk):
    """Display interest schedule for a loan"""
    from loans.models import Loan

    loan = get_object_or_404(Loan, pk=pk)

    # Get payment schedule
    try:
        schedule = loan.get_payment_schedule()
    except:
        schedule = []

    # Calculate interest breakdown using stored values
    principal = loan.principal_amount or Decimal('0.00')
    interest_amount = loan.interest_amount
    processing_fee = loan.processing_fee
    total_amount = loan.total_amount

    # Calculate interest paid vs outstanding
    amount_paid = loan.amount_paid or Decimal('0.00')
    if total_amount > 0:
        interest_portion = interest_amount / total_amount
        interest_paid = min(interest_amount, amount_paid * interest_portion)
        interest_outstanding = interest_amount - interest_paid
    else:
        interest_paid = Decimal('0.00')
        interest_outstanding = Decimal('0.00')

    context = {
        'loan': loan,
        'schedule': schedule,
        'interest_breakdown': {
            'total_interest': interest_amount,
            'interest_paid': interest_paid,
            'interest_outstanding': interest_outstanding,
            'interest_rate': loan.get_display_interest_rate(),
        }
    }

    return render(request, 'loans/interest_schedule.html', context)


@login_required
@admin_only_ajax
def add_penalty(request, loan_id):
    """
    Add penalty to a loan (admin only)
    AJAX endpoint for penalty addition
    Requirements: 4.1, 4.2, 4.3, 4.4, 4.5, 4.6, 4.8, 4.9, 4.11
    Security Requirements: 1, 2, 3
    """
    from .validators import PenaltyValidator
    from .sanitizers import InputSanitizer
    from django.core.exceptions import ValidationError as DjangoValidationError
    from .models import PenaltyCharge
    
    # Only accept POST requests
    if request.method != 'POST':
        return JsonResponse({
            'status': 'error',
            'message': 'Only POST requests are allowed'
        }, status=405)
    
    # Get the loan
    try:
        loan = Loan.objects.get(pk=loan_id)
    except Loan.DoesNotExist:
        return JsonResponse({
            'status': 'error',
            'message': 'Loan not found'
        }, status=404)
    
    try:
        # Parse JSON data if content type is JSON, otherwise use POST data
        if request.content_type == 'application/json':
            import json
            data = json.loads(request.body)
        else:
            data = request.POST.dict()
        
        # Sanitize input data first
        # Requirements: 10.9, 10.10
        sanitized_data = InputSanitizer.sanitize_form_data(data, {
            'amount': 'string',
            'penalty_date': 'string',
            'reason': 'text'
        })
        
        # Validate penalty form data
        # Requirements: 4.4, 4.5, 4.11
        validated_data = PenaltyValidator.validate_penalty_form(sanitized_data)
        
        # Create penalty charge
        # Requirements: 4.3, 4.6
        penalty = PenaltyCharge.objects.create(
            loan=loan,
            amount=validated_data['amount'],
            penalty_date=validated_data['penalty_date'],
            reason=validated_data['reason'],
            penalty_rate=Decimal('0.00'),  # Manual penalty, no rate
            days_overdue=0,  # Manual penalty
            outstanding_amount=loan.outstanding_amount,
            is_automatic=False,
            applied_by=request.user
        )
        
        # Update loan's outstanding amount
        # Requirements: 4.8, 4.9
        loan.recalculate_amounts()
        loan.save()
        
        # Create audit log
        AuditLog.objects.create(
            user=request.user,
            action='create',
            model_name='PenaltyCharge',
            object_id=str(penalty.pk),
            description=f'Added penalty of KES {penalty.amount:,.2f} to loan {loan.loan_number}'
        )
        
        return JsonResponse({
            'status': 'success',
            'message': f'Penalty of KES {penalty.amount:,.2f} added successfully',
            'penalty': {
                'id': str(penalty.pk),
                'amount': str(penalty.amount),
                'penalty_date': penalty.penalty_date.strftime('%Y-%m-%d'),
                'reason': penalty.reason,
                'created_at': penalty.created_at.strftime('%Y-%m-%d %H:%M:%S')
            },
            'loan': {
                'outstanding_amount': str(loan.outstanding_amount),
                'total_penalties': str(loan.total_penalties)
            }
        })
        
    except DjangoValidationError as e:
        # Validation errors from our validator
        # Requirement: 4.11
        return JsonResponse({
            'status': 'error',
            'message': str(e)
        }, status=400)
    except Exception as e:
        # Log unexpected errors
        import logging
        logger = logging.getLogger(__name__)
        logger.error(f'Error adding penalty to loan {loan_id}: {str(e)}', exc_info=True)
        
        return JsonResponse({
            'status': 'error',
            'message': 'An unexpected error occurred while adding the penalty'
        }, status=500)
