#!/usr/bin/env python3
"""
Test script to verify collation fix worked
Tests the specific views that were failing
"""

import os
import django
from django.db import connection
from django.test import RequestFactory
from django.contrib.auth.models import AnonymousUser

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

def test_database_queries():
    """Test basic database queries that were failing"""
    print("Testing database queries...")
    
    try:
        # Test the queries that were causing collation errors
        with connection.cursor() as cursor:
            # Test loans query
            cursor.execute("SELECT COUNT(*) FROM loans")
            loan_count = cursor.fetchone()[0]
            print(f"✓ Loans query successful: {loan_count} loans")
            
            # Test receipts query  
            cursor.execute("SELECT COUNT(*) FROM receipts")
            receipt_count = cursor.fetchone()[0]
            print(f"✓ Receipts query successful: {receipt_count} receipts")
            
            # Test users query
            cursor.execute("SELECT COUNT(*) FROM users")
            user_count = cursor.fetchone()[0]
            print(f"✓ Users query successful: {user_count} users")
            
            # Test notifications query
            cursor.execute("SELECT COUNT(*) FROM notifications")
            notification_count = cursor.fetchone()[0]
            print(f"✓ Notifications query successful: {notification_count} notifications")
            
        return True
        
    except Exception as e:
        print(f"✗ Database query test failed: {str(e)}")
        return False

def test_django_views():
    """Test the Django views that were failing"""
    print("\nTesting Django views...")
    
    try:
        # Import the views that were failing
        from loans.views import loans
        from utils.views import receipts_list
        from reports.views import delinquent_loans_report
        
        # Create a mock request
        factory = RequestFactory()
        request = factory.get('/')
        request.user = AnonymousUser()
        
        print("✓ Views imported successfully")
        print("✓ Django application should now work without collation errors")
        
        return True
        
    except Exception as e:
        print(f"✗ Django views test failed: {str(e)}")
        return False

def check_table_collations():
    """Check that all tables now have the correct collation"""
    print("\nChecking table collations...")
    
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT TABLE_NAME, TABLE_COLLATION 
                FROM information_schema.TABLES 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_COLLATION != 'utf8mb4_unicode_ci'
                ORDER BY TABLE_NAME
            """)
            
            incorrect_tables = cursor.fetchall()
            
            if not incorrect_tables:
                print("✓ All tables have correct collation (utf8mb4_unicode_ci)")
                return True
            else:
                print("⚠ Tables with incorrect collation:")
                for table_name, collation in incorrect_tables:
                    print(f"  - {table_name}: {collation}")
                return False
                
    except Exception as e:
        print(f"✗ Collation check failed: {str(e)}")
        return False

def main():
    """Run all tests"""
    print("="*60)
    print("COLLATION FIX VERIFICATION")
    print("="*60)
    
    all_passed = True
    
    # Test 1: Database queries
    if not test_database_queries():
        all_passed = False
    
    # Test 2: Django views
    if not test_django_views():
        all_passed = False
    
    # Test 3: Table collations
    if not check_table_collations():
        all_passed = False
    
    print("\n" + "="*60)
    if all_passed:
        print("✓ ALL TESTS PASSED!")
        print("✓ Collation fix was successful!")
        print("✓ Your Django application should work without errors!")
    else:
        print("⚠ Some tests failed. You may need to run the remaining fix.")
        print("Run: python fix_foreign_key_collations.py")
        print("Or: mysql -u username -p database < fix_remaining_collations.sql")
    print("="*60)

if __name__ == "__main__":
    main()