from django.db.models import Sum, Q
from loans.models import LoanApplication, LoanInterestWaivered, LoanPenaltyWaivered, LoanPenalty, LoanPayments
from questbanker_api.utils import get_current_user


def apply_loan_filters(queryset, report_filter, filter_1, filter_2):
    """
    Apply filters to loan queryset based on filter type
    """
    if not report_filter or not filter_1 or not filter_2:
        return queryset
    
    filters = Q()
    
    if report_filter == 'client_type_officer':
        filters &= Q(loan_officer__id__in=filter_1) & Q(customer__branch_customer_type__id__in=filter_2)
    elif report_filter == 'gender_officer':
        filters &= Q(loan_officer__id__in=filter_1) & Q(customer__gender__in=filter_2)
    elif report_filter == 'officer_branch':
        filters &= Q(loan_officer__id__in=filter_1) & Q(organisation_branch__id__in=filter_2)
    elif report_filter == 'client_type_product':
        filters &= Q(loan_application_product__id__in=filter_1) & Q(customer__branch_customer_type__id__in=filter_2)
    elif report_filter == 'product_officer':
        filters &= Q(loan_officer__id__in=filter_1) & Q(loan_application_product__id__in=filter_2)
    elif report_filter == 'gender_product':
        filters &= Q(loan_application_product__id__in=filter_1) & Q(customer__gender__in=filter_2)
    elif report_filter == 'product_branch':
        filters &= Q(loan_application_product__id__in=filter_1) & Q(organisation_branch__id__in=filter_2)
    elif report_filter == 'gender_branch':
        filters &= Q(organisation_branch__id__in=filter_1) & Q(customer__gender__in=filter_2)
    elif report_filter == 'client_type_branch':
        filters &= Q(organisation_branch__id__in=filter_1) & Q(customer__branch_customer_type__id__in=filter_2)
    elif report_filter == 'group_branch':
        filters &= Q(organisation_branch__id__in=filter_1) & Q(loan_group__id__in=filter_2)
    elif report_filter == 'product_sector':
        filters &= Q(loan_application_product__id__in=filter_1) & Q(loan_sector__id__in=filter_2)
    
    return queryset.filter(filters)


def get_loan_interest_waiver_report(request, report_date, report_filter, filter_1, filter_2):
    """
    Generate loan interest waiver report with filters
    """
    organisation_id = get_current_user(request, 'organisation_id', None)
    
    # Base queryset for loans with interest waivers
    loans_query = LoanApplication.objects.filter(
        organisation_branch__branch_organisation__id=organisation_id,
        is_deleted=False,
        deleted=False,
        id__in=LoanInterestWaivered.objects.filter(
            deleted=False,
            date_added__lte=report_date
        ).values_list('loan_application', flat=True)
    )
    
    # Apply filters
    loans_query = apply_loan_filters(loans_query, report_filter, filter_1, filter_2)
    
    results = []
    for loan in loans_query:
        # Get disbursement details
        disbursement = getattr(loan, 'loan_application_disbursement', None)
        total_interest_expected = disbursement.total_interest_expected if disbursement else 0
        
        # Calculate total interest waived up to report date
        total_interest_waived = LoanInterestWaivered.objects.filter(
            loan_application=loan,
            deleted=False,
            date_added__lte=report_date
        ).aggregate(total=Sum('amount'))['total'] or 0
        
        results.append({
            'loan_id': loan.id,
            'name': loan.customer.name,
            'member_number': loan.customer.member_number or loan.customer.old_member_number or '',
            'total_interest_expected': float(total_interest_expected),
            'total_interest_waived': float(total_interest_waived)
        })
    
    return results


def get_loan_penalty_waiver_report(request, report_date, report_filter, filter_1, filter_2):
    """
    Generate loan penalty waiver report with filters
    """
    organisation_id = get_current_user(request, 'organisation_id', None)
    
    # Base queryset for loans with penalty waivers
    loans_query = LoanApplication.objects.filter(
        organisation_branch__branch_organisation__id=organisation_id,
        is_deleted=False,
        deleted=False,
        id__in=LoanPenaltyWaivered.objects.filter(
            deleted=False,
            date_added__lte=report_date
        ).values_list('loan_application', flat=True)
    )
    
    # Apply filters
    loans_query = apply_loan_filters(loans_query, report_filter, filter_1, filter_2)
    
    results = []
    for loan in loans_query:
        # Calculate total penalty up to report date
        total_penalty = LoanPenalty.objects.filter(
            loan_application=loan,
            deleted=False,
            date_added__lte=report_date
        ).aggregate(total=Sum('amount'))['total'] or 0
        
        # Calculate penalty paid up to report date
        penalty_paid = LoanPayments.objects.filter(
            loan_application=loan,
            payment_status='normal',
            deleted=False,
            date_added__lte=report_date
        ).aggregate(total=Sum('penalty_paid'))['total'] or 0
        
        # Calculate penalty waived up to report date
        penalty_waived = LoanPenaltyWaivered.objects.filter(
            loan_application=loan,
            deleted=False,
            date_added__lte=report_date
        ).aggregate(total=Sum('amount'))['total'] or 0
        
        results.append({
            'loan_id': loan.id,
            'name': loan.customer.name,
            'penalty': float(total_penalty),
            'penalty_paid': float(penalty_paid),
            'penalty_waived': float(penalty_waived)
        })
    
    return results