"""
Test database connection and check for loans

Run with: python test_database_connection.py
"""

import os
import django

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

from django.db import connection
from loans.models import Loan, LoanProduct, LoanApplication
from users.models import CustomUser

print("=" * 80)
print("DATABASE CONNECTION TEST")
print("=" * 80)
print()

# Show database info
print("Database Configuration:")
print(f"  Name: {connection.settings_dict['NAME']}")
print(f"  Host: {connection.settings_dict['HOST']}")
print(f"  User: {connection.settings_dict['USER']}")
print(f"  Port: {connection.settings_dict['PORT']}")
print()

# Test connection
try:
    with connection.cursor() as cursor:
        cursor.execute("SELECT DATABASE()")
        db_name = cursor.fetchone()[0]
        print(f"✓ Connected to database: {db_name}")
        print()
except Exception as e:
    print(f"❌ Connection error: {e}")
    exit(1)

# Check tables exist
print("Checking tables...")
try:
    with connection.cursor() as cursor:
        cursor.execute("SHOW TABLES")
        tables = cursor.fetchall()
        print(f"✓ Found {len(tables)} tables")
        
        # Check if loans table exists
        table_names = [table[0] for table in tables]
        if 'loans' in table_names:
            print("✓ 'loans' table exists")
        else:
            print("❌ 'loans' table NOT found!")
            print(f"Available tables: {', '.join(table_names[:10])}")
        print()
except Exception as e:
    print(f"❌ Error checking tables: {e}")
    exit(1)

# Count records using raw SQL
print("Counting records using raw SQL...")
try:
    with connection.cursor() as cursor:
        cursor.execute("SELECT COUNT(*) FROM loans")
        loan_count = cursor.fetchone()[0]
        print(f"  Loans (raw SQL): {loan_count}")
        
        cursor.execute("SELECT COUNT(*) FROM loan_applications")
        app_count = cursor.fetchone()[0]
        print(f"  Applications (raw SQL): {app_count}")
        
        cursor.execute("SELECT COUNT(*) FROM loan_products")
        product_count = cursor.fetchone()[0]
        print(f"  Products (raw SQL): {product_count}")
        
        cursor.execute("SELECT COUNT(*) FROM users_customuser WHERE role='borrower'")
        borrower_count = cursor.fetchone()[0]
        print(f"  Borrowers (raw SQL): {borrower_count}")
        print()
except Exception as e:
    print(f"❌ Error counting records: {e}")
    exit(1)

# Count using Django ORM
print("Counting records using Django ORM...")
try:
    loan_count_orm = Loan.objects.count()
    print(f"  Loans (ORM): {loan_count_orm}")
    
    app_count_orm = LoanApplication.objects.count()
    print(f"  Applications (ORM): {app_count_orm}")
    
    product_count_orm = LoanProduct.objects.count()
    print(f"  Products (ORM): {product_count_orm}")
    
    borrower_count_orm = CustomUser.objects.filter(role='borrower').count()
    print(f"  Borrowers (ORM): {borrower_count_orm}")
    print()
except Exception as e:
    print(f"❌ Error with ORM: {e}")
    exit(1)

# If loans exist, show some details
if loan_count > 0:
    print("=" * 80)
    print("SAMPLE LOANS")
    print("=" * 80)
    print()
    
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT 
                loan_number,
                principal_amount,
                interest_amount,
                processing_fee,
                total_amount,
                (principal_amount + interest_amount + processing_fee) as calculated_total,
                (total_amount - (principal_amount + interest_amount + processing_fee)) as difference
            FROM loans
            ORDER BY created_at DESC
            LIMIT 10
        """)
        
        loans = cursor.fetchall()
        
        for loan in loans:
            loan_number, principal, interest, processing_fee, total, calc_total, diff = loan
            print(f"Loan: {loan_number}")
            print(f"  Principal: KES {principal:,.2f}")
            print(f"  Interest: KES {interest:,.2f}")
            print(f"  Processing Fee: KES {processing_fee:,.2f}")
            print(f"  Stored Total: KES {total:,.2f}")
            print(f"  Calculated Total: KES {calc_total:,.2f}")
            
            if abs(diff) > 0.01:
                print(f"  ❌ INCORRECT! Difference: KES {diff:,.2f}")
            else:
                print(f"  ✅ CORRECT")
            print()
else:
    print("⚠️  No loans found in database!")
    print()
    print("The database appears to be empty or loans haven't been created yet.")

print("=" * 80)
print("TEST COMPLETE")
print("=" * 80)