"""
Comprehensive diagnostic for deleted loan display issues
"""

import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

from loans.models import Loan
from django.db.models import Q

print("=" * 80)
print("DELETED LOAN DIAGNOSTIC")
print("=" * 80)

# 1. Check all loans with 'DELETED' in loan_number
print("\n1. Checking for loans with 'DELETED' in loan_number...")
deleted_suffix_loans = Loan.objects.filter(loan_number__icontains='DELETED')
print(f"   Found: {deleted_suffix_loans.count()} loans")
if deleted_suffix_loans.exists():
    for loan in deleted_suffix_loans:
        print(f"   - {loan.loan_number} (is_deleted={loan.is_deleted}, status={loan.status})")

# 2. Check all soft-deleted loans
print("\n2. Checking all soft-deleted loans (is_deleted=True)...")
soft_deleted = Loan.objects.filter(is_deleted=True)
print(f"   Found: {soft_deleted.count()} soft-deleted loans")
if soft_deleted.count() > 0:
    print(f"   Showing first 10:")
    for loan in soft_deleted[:10]:
        print(f"   - {loan.loan_number} (status={loan.status}, deleted_at={loan.deleted_at})")

# 3. Check for loans with status='active' but is_deleted=True
print("\n3. Checking for inconsistent loans (status='active' AND is_deleted=True)...")
inconsistent = Loan.objects.filter(status='active', is_deleted=True)
print(f"   Found: {inconsistent.count()} inconsistent loans")
if inconsistent.exists():
    print(f"   ⚠️  WARNING: These loans are marked as both 'active' and 'deleted'!")
    for loan in inconsistent:
        print(f"   - {loan.loan_number}")
        print(f"     Borrower: {loan.borrower.get_full_name()}")
        print(f"     Deleted at: {loan.deleted_at}")
        print(f"     Deleted by: {loan.deleted_by}")

# 4. Check total loan counts
print("\n4. Loan Statistics:")
total_loans = Loan.objects.count()
active_loans = Loan.objects.filter(is_deleted=False).count()
deleted_loans = Loan.objects.filter(is_deleted=True).count()
print(f"   Total loans: {total_loans}")
print(f"   Active loans (is_deleted=False): {active_loans}")
print(f"   Deleted loans (is_deleted=True): {deleted_loans}")

# 5. Check for loans with 'deleted' status
print("\n5. Checking for loans with status='deleted'...")
status_deleted = Loan.objects.filter(status='deleted')
print(f"   Found: {status_deleted.count()} loans")
if status_deleted.exists():
    print(f"   ⚠️  WARNING: Loans should not have status='deleted'!")
    print(f"   The system uses is_deleted flag, not status field")

# 6. Recommendations
print("\n" + "=" * 80)
print("RECOMMENDATIONS")
print("=" * 80)

if inconsistent.exists():
    print("\n⚠️  ISSUE FOUND: Loans with status='active' but is_deleted=True")
    print("   These loans will show as 'Active' in the UI but are actually deleted.")
    print("   ")
    print("   SOLUTION: Update the status field for deleted loans")
    print("   Suggested status: 'written_off' or keep 'active' but ensure UI checks is_deleted")

if deleted_suffix_loans.exists():
    print("\n⚠️  ISSUE FOUND: Loans with 'DELETED' in loan_number field")
    print("   The loan_number field should not contain 'DELETED' suffix.")
    print("   ")
    print("   SOLUTION: Remove 'DELETED' suffix from loan_number field")
    print("   Run: python fix_deleted_loan_numbers.py")

if not inconsistent.exists() and not deleted_suffix_loans.exists():
    print("\n✅ No issues found in the database!")
    print("   If you're seeing 'DELETED' in the UI, it might be:")
    print("   1. A template display issue (check loan_detail.html)")
    print("   2. A caching issue (clear browser cache)")
    print("   3. Looking at production data (check which environment you're in)")

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