#!/usr/bin/env python
"""
Production Reports Diagnostic Tool

Run this script on production to diagnose why reports might be showing zeros.
"""
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, LoanProduct
from users.models import CustomUser, Branch
from django.db.models import Sum, Count, Avg, Q
from django.utils import timezone
from decimal import Decimal
from datetime import timedelta

print("=" * 80)
print("PRODUCTION REPORTS DIAGNOSTIC TOOL")
print("=" * 80)
print(f"Run Date: {timezone.now()}")
print("=" * 80)

# 1. Database Connection Check
print("\n1. DATABASE CONNECTION")
print("-" * 80)
try:
    from django.db import connection
    with connection.cursor() as cursor:
        cursor.execute("SELECT VERSION()")
        version = cursor.fetchone()
        print(f"✅ Database connected: {version[0]}")
except Exception as e:
    print(f"❌ Database connection error: {str(e)}")
    sys.exit(1)

# 2. Loans Check
print("\n2. LOANS DATA")
print("-" * 80)
try:
    total_loans = Loan.objects.count()
    active_loans = Loan.objects.filter(status='active').count()
    not_deleted = Loan.objects.filter(is_deleted=False).count()
    
    print(f"Total loans in database: {total_loans}")
    print(f"Active loans: {active_loans}")
    print(f"Not deleted: {not_deleted}")
    
    if total_loans == 0:
        print("⚠️  WARNING: No loans found in database!")
        print("   → Create test loans to see data in reports")
    else:
        print("✅ Loans exist in database")
        
except Exception as e:
    print(f"❌ Error checking loans: {str(e)}")

# 3. Processing Fees Check
print("\n3. PROCESSING FEES DATA")
print("-" * 80)
try:
    loans_with_fees = Loan.objects.filter(
        is_deleted=False,
        processing_fee__isnull=False
    ).exclude(processing_fee=0)
    
    total_fees = Loan.objects.filter(is_deleted=False).aggregate(
        total=Sum('processing_fee')
    )['total'] or Decimal('0.00')
    
    avg_fee = Loan.objects.filter(is_deleted=False).aggregate(
        avg=Avg('processing_fee')
    )['avg'] or Decimal('0.00')
    
    print(f"Loans with processing fees: {loans_with_fees.count()}")
    print(f"Total processing fees: KSh {total_fees:,.2f}")
    print(f"Average processing fee: KSh {avg_fee:,.2f}")
    
    if total_fees == 0:
        print("⚠️  WARNING: No processing fees found!")
        print("   → Loans may not have processing_fee field populated")
        print("   → Check if loans were created with processing fees")
    else:
        print("✅ Processing fees data exists")
        
    # Show sample
    if loans_with_fees.exists():
        print("\n   Sample loans with fees:")
        for loan in loans_with_fees[:3]:
            print(f"   - {loan.loan_number or loan.id}: KSh {loan.processing_fee:,.2f}")
            
except Exception as e:
    print(f"❌ Error checking processing fees: {str(e)}")

# 4. Interest Income Check
print("\n4. INTEREST INCOME DATA")
print("-" * 80)
try:
    loans_with_interest = Loan.objects.filter(
        is_deleted=False,
        interest_amount__isnull=False
    ).exclude(interest_amount=0)
    
    total_interest = Loan.objects.filter(is_deleted=False).aggregate(
        total=Sum('interest_amount')
    )['total'] or Decimal('0.00')
    
    avg_interest = Loan.objects.filter(is_deleted=False).aggregate(
        avg=Avg('interest_amount')
    )['avg'] or Decimal('0.00')
    
    print(f"Loans with interest: {loans_with_interest.count()}")
    print(f"Total interest income: KSh {total_interest:,.2f}")
    print(f"Average interest: KSh {avg_interest:,.2f}")
    
    if total_interest == 0:
        print("⚠️  WARNING: No interest income found!")
        print("   → Loans may not have interest_amount field populated")
    else:
        print("✅ Interest income data exists")
        
except Exception as e:
    print(f"❌ Error checking interest income: {str(e)}")

# 5. Branch Filtering Check
print("\n5. BRANCH FILTERING")
print("-" * 80)
try:
    branches = Branch.objects.all()
    print(f"Total branches: {branches.count()}")
    
    for branch in branches:
        branch_loans = Loan.objects.filter(
            is_deleted=False,
            borrower__branch=branch
        )
        branch_fees = branch_loans.aggregate(
            total=Sum('processing_fee')
        )['total'] or Decimal('0.00')
        
        print(f"   {branch.name}: {branch_loans.count()} loans, KSh {branch_fees:,.2f} in fees")
        
    # Check loans without branch
    no_branch = Loan.objects.filter(
        is_deleted=False,
        borrower__branch__isnull=True
    ).count()
    
    if no_branch > 0:
        print(f"   ⚠️  {no_branch} loans have no branch assigned")
        
except Exception as e:
    print(f"❌ Error checking branches: {str(e)}")

# 6. User Roles Check
print("\n6. USER ROLES & PERMISSIONS")
print("-" * 80)
try:
    users = CustomUser.objects.filter(is_active=True)
    print(f"Total active users: {users.count()}")
    
    role_breakdown = users.values('role').annotate(count=Count('id'))
    for item in role_breakdown:
        print(f"   {item['role']}: {item['count']} users")
        
    # Check admin users
    admins = users.filter(Q(role='admin') | Q(is_superuser=True))
    print(f"\n   Admin users: {admins.count()}")
    if admins.count() == 0:
        print("   ⚠️  No admin users found!")
        
except Exception as e:
    print(f"❌ Error checking users: {str(e)}")

# 7. Recent Activity Check
print("\n7. RECENT ACTIVITY (Last 30 days)")
print("-" * 80)
try:
    thirty_days_ago = timezone.now() - timedelta(days=30)
    
    recent_loans = Loan.objects.filter(
        is_deleted=False,
        created_at__gte=thirty_days_ago
    )
    
    recent_fees = recent_loans.aggregate(
        total=Sum('processing_fee')
    )['total'] or Decimal('0.00')
    
    print(f"Loans created: {recent_loans.count()}")
    print(f"Processing fees: KSh {recent_fees:,.2f}")
    
    if recent_loans.count() == 0:
        print("⚠️  No recent loans created")
        print("   → Reports may show zeros if filtering by current month")
        
except Exception as e:
    print(f"❌ Error checking recent activity: {str(e)}")

# 8. Report Service Test
print("\n8. REPORT SERVICE TEST")
print("-" * 80)
try:
    from reports.simple_reports_service import SimpleReportsService
    service = SimpleReportsService()
    
    # Test processing fees report
    pf_report = service.get_processing_fees_report(period='month')
    print(f"Processing Fees Report:")
    print(f"   Total Fees: KSh {pf_report['summary']['total_processing_fees']:,.2f}")
    print(f"   Loans: {pf_report['summary']['total_loans_processed']}")
    
    # Test interest income report
    ii_report = service.get_interest_income_report(
        year=timezone.now().year,
        month=timezone.now().month
    )
    print(f"\nInterest Income Report:")
    print(f"   Total Interest: KSh {ii_report['summary']['total_interest_income']:,.2f}")
    print(f"   Loans: {ii_report['summary']['total_loans']}")
    
    print("\n✅ Report service working correctly")
    
except Exception as e:
    print(f"❌ Error testing report service: {str(e)}")
    import traceback
    traceback.print_exc()

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

recommendations = []

if Loan.objects.count() == 0:
    recommendations.append("❌ Create loans in the system")
    
if Loan.objects.filter(processing_fee__gt=0).count() == 0:
    recommendations.append("❌ Ensure loans have processing_fee values set")
    
if Loan.objects.filter(interest_amount__gt=0).count() == 0:
    recommendations.append("❌ Ensure loans have interest_amount values set")
    
recent_count = Loan.objects.filter(
    created_at__gte=timezone.now() - timedelta(days=30)
).count()
if recent_count == 0:
    recommendations.append("⚠️  No recent loans - current month reports will show zeros")
    recommendations.append("   → Use 'All Time' filter to see historical data")
    
if not recommendations:
    recommendations.append("✅ All checks passed - reports should be working!")
    recommendations.append("   → If still showing zeros, check:")
    recommendations.append("     1. User is logged in as admin (to see all branches)")
    recommendations.append("     2. Branch filter is not too restrictive")
    recommendations.append("     3. Date filter is set to 'All Time'")
    recommendations.append("     4. Application has been restarted after deployment")

for rec in recommendations:
    print(rec)

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