#!/usr/bin/env python3
"""
Verification script to test that collation issues are resolved
"""

import os
import django
from django.db import connection

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

def test_collation_queries():
    """Test queries that were previously failing due to collation issues"""
    
    print("Testing collation fix...")
    print("=" * 50)
    
    try:
        with connection.cursor() as cursor:
            # Test 1: Check all tables have correct collation
            cursor.execute("""
                SELECT TABLE_NAME, TABLE_COLLATION 
                FROM information_schema.TABLES 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_TYPE = 'BASE TABLE'
                AND TABLE_COLLATION != 'utf8mb4_unicode_ci'
            """)
            
            incorrect_tables = cursor.fetchall()
            if incorrect_tables:
                print(f"❌ Found {len(incorrect_tables)} tables with incorrect collation:")
                for table, collation in incorrect_tables:
                    print(f"   {table}: {collation}")
                return False
            else:
                print("✅ All tables have correct collation (utf8mb4_unicode_ci)")
            
            # Test 2: Try a query that was failing before
            cursor.execute("""
                SELECT COUNT(*) FROM users u 
                JOIN loans l ON u.id = l.borrower_id 
                WHERE u.status = 'active'
            """)
            result = cursor.fetchone()
            print(f"✅ User-Loan join query successful: {result[0]} active users with loans")
            
            # Test 3: Try notification queries
            cursor.execute("""
                SELECT COUNT(*) FROM utils_notification n
                JOIN users u ON n.user_id = u.id
                WHERE n.read_at IS NULL
            """)
            result = cursor.fetchone()
            print(f"✅ Notification-User join query successful: {result[0]} unread notifications")
            
            # Test 4: Try receipt queries
            cursor.execute("""
                SELECT COUNT(*) FROM receipts r
                JOIN loans l ON r.loan_id = l.id
                WHERE r.payment_method = 'mpesa'
            """)
            result = cursor.fetchone()
            print(f"✅ Receipt-Loan join query successful: {result[0]} M-Pesa receipts")
            
            print("=" * 50)
            print("🎉 ALL COLLATION TESTS PASSED!")
            print("🎉 Your Django application is now working correctly!")
            print("=" * 50)
            return True
            
    except Exception as e:
        print(f"❌ Test failed: {str(e)}")
        return False

if __name__ == "__main__":
    success = test_collation_queries()
    if success:
        print("\n✅ Verification complete - collation issues resolved!")
    else:
        print("\n❌ Verification failed - some issues remain")