#!/usr/bin/env python3
"""
Test script to verify that the borrower_id fix is working
"""

import os
import sys
import django

# Setup Django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'RuralPoint.settings')
django.setup()

from django.db import connection
from loans.models import Loan
from utils.models import Receipt

def test_database_schema():
    """Test that the borrower_id column exists and is populated"""
    print("🔍 Testing database schema...")
    
    try:
        with connection.cursor() as cursor:
            # Check if borrower_id column exists
            cursor.execute("""
                SELECT COUNT(*) 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'receipts' 
                AND COLUMN_NAME = 'borrower_id'
            """)
            
            if cursor.fetchone()[0] == 0:
                print("❌ borrower_id column does not exist in receipts table")
                return False
            
            print("✓ borrower_id column exists")
            
            # Check for null values
            cursor.execute("SELECT COUNT(*) FROM receipts WHERE borrower_id IS NULL")
            null_count = cursor.fetchone()[0]
            
            if null_count > 0:
                print(f"⚠️  Warning: {null_count} receipts have NULL borrower_id")
            else:
                print("✓ All receipts have borrower_id populated")
            
            # Test the query that was failing
            cursor.execute("SELECT COUNT(*) FROM receipts WHERE borrower_id IS NOT NULL")
            valid_count = cursor.fetchone()[0]
            print(f"✓ {valid_count} receipts have valid borrower_id")
            
            return True
            
    except Exception as e:
        print(f"❌ Database test failed: {e}")
        return False

def test_model_queries():
    """Test that Django model queries work properly"""
    print("\n🔍 Testing Django model queries...")
    
    try:
        # Test Receipt model query
        receipt_count = Receipt.objects.count()
        print(f"✓ Receipt.objects.count() = {receipt_count}")
        
        # Test filtering by borrower
        if receipt_count > 0:
            first_receipt = Receipt.objects.first()
            if first_receipt and first_receipt.borrower:
                borrower_receipts = Receipt.objects.filter(borrower=first_receipt.borrower).count()
                print(f"✓ Receipts for borrower {first_receipt.borrower.get_full_name()}: {borrower_receipts}")
            else:
                print("⚠️  First receipt has no borrower assigned")
        
        return True
        
    except Exception as e:
        print(f"❌ Model query test failed: {e}")
        return False

def test_repayment_creation():
    """Test creating a repayment (simulation)"""
    print("\n🔍 Testing repayment creation simulation...")
    
    try:
        # Find an active loan for testing
        active_loan = Loan.objects.filter(status='active').first()
        
        if not active_loan:
            print("⚠️  No active loans found for testing")
            return True
        
        print(f"✓ Found active loan: {active_loan.loan_number}")
        print(f"✓ Borrower: {active_loan.borrower.get_full_name()}")
        print(f"✓ Outstanding amount: KES {active_loan.outstanding_amount:,.2f}")
        
        # Test that we can access the borrower through the loan
        borrower_id = str(active_loan.borrower.id)
        print(f"✓ Borrower ID: {borrower_id}")
        
        return True
        
    except Exception as e:
        print(f"❌ Repayment creation test failed: {e}")
        return False

def main():
    """Run all tests"""
    print("🧪 Testing receipts borrower_id fix...")
    print("=" * 50)
    
    all_passed = True
    
    # Test database schema
    if not test_database_schema():
        all_passed = False
    
    # Test model queries
    if not test_model_queries():
        all_passed = False
    
    # Test repayment creation
    if not test_repayment_creation():
        all_passed = False
    
    print("\n" + "=" * 50)
    
    if all_passed:
        print("✅ All tests passed! The fix appears to be working correctly.")
        print("\n📋 You can now test the repayment functionality in the web interface:")
        print("   1. Go to a loan detail page")
        print("   2. Try to record a repayment")
        print("   3. The 'Unknown column borrower_id' error should be resolved")
    else:
        print("❌ Some tests failed. Please check the errors above.")
        return False
    
    return True

if __name__ == "__main__":
    success = main()
    sys.exit(0 if success else 1)