#!/usr/bin/env python
"""
Check processing fees data in the database
"""
import os
import sys
import django

# Setup Django
sys.path.append(os.path.dirname(os.path.abspath(__file__)))
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

from loans.models import Loan
from django.db.models import Count, Sum, Avg
from decimal import Decimal

print("=" * 80)
print("PROCESSING FEES DATA CHECK")
print("=" * 80)

# Check all loans
all_loans = Loan.objects.filter(is_deleted=False)
print(f"\nTotal loans (not deleted): {all_loans.count()}")

# Check loans with processing fees
loans_with_fees = all_loans.exclude(processing_fee__isnull=True).exclude(processing_fee=0)
print(f"Loans with processing fees > 0: {loans_with_fees.count()}")

# Check loans without processing fees
loans_without_fees = all_loans.filter(processing_fee__isnull=True) | all_loans.filter(processing_fee=0)
print(f"Loans without processing fees: {loans_without_fees.count()}")

# Show sample data
print("\n" + "=" * 80)
print("SAMPLE LOAN DATA (First 10 loans)")
print("=" * 80)

for loan in all_loans[:10]:
    print(f"\nLoan ID: {loan.id}")
    print(f"  Loan Number: {loan.loan_number or 'N/A'}")
    print(f"  Borrower: {loan.borrower.first_name} {loan.borrower.last_name}")
    print(f"  Principal: KSh {loan.principal_amount:,.2f}")
    print(f"  Processing Fee: KSh {loan.processing_fee or 0:,.2f}")
    print(f"  Total Amount: KSh {loan.total_amount or 0:,.2f}")
    print(f"  Status: {loan.status}")
    print(f"  Created: {loan.created_at}")

# Calculate totals
total_fees = all_loans.aggregate(total=Sum('processing_fee'))['total'] or Decimal('0.00')
avg_fee = all_loans.aggregate(avg=Avg('processing_fee'))['avg'] or Decimal('0.00')

print("\n" + "=" * 80)
print("SUMMARY STATISTICS")
print("=" * 80)
print(f"Total Processing Fees: KSh {total_fees:,.2f}")
print(f"Average Processing Fee: KSh {avg_fee:,.2f}")

# Check by status
print("\n" + "=" * 80)
print("BREAKDOWN BY STATUS")
print("=" * 80)
status_breakdown = all_loans.values('status').annotate(
    count=Count('id'),
    total_fees=Sum('processing_fee')
).order_by('-count')

for item in status_breakdown:
    print(f"{item['status']}: {item['count']} loans, KSh {item['total_fees'] or 0:,.2f} in fees")

# Check recent loans (last 30 days)
from django.utils import timezone
from datetime import timedelta

thirty_days_ago = timezone.now() - timedelta(days=30)
recent_loans = all_loans.filter(created_at__gte=thirty_days_ago)
recent_fees = recent_loans.aggregate(total=Sum('processing_fee'))['total'] or Decimal('0.00')

print("\n" + "=" * 80)
print("RECENT LOANS (Last 30 days)")
print("=" * 80)
print(f"Count: {recent_loans.count()}")
print(f"Total Processing Fees: KSh {recent_fees:,.2f}")

print("\n" + "=" * 80)
print("CHECK COMPLETE")
print("=" * 80)