#!/usr/bin/env python
"""
Fix collation mismatches by temporarily disabling foreign key checks
"""
import pymysql
import os
from dotenv import load_dotenv

load_dotenv()

connection = pymysql.connect(
    host=os.getenv('DB_HOST', 'localhost'),
    user=os.getenv('DB_USER'),
    password=os.getenv('DB_PASSWORD'),
    database=os.getenv('DB_NAME'),
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

print("=" * 80)
print("FIXING ALL COLLATIONS (DISABLING FOREIGN KEY CHECKS)")
print("=" * 80)

try:
    with connection.cursor() as cursor:
        # Disable foreign key checks
        print("\nDisabling foreign key checks...")
        cursor.execute("SET FOREIGN_KEY_CHECKS=0")
        
        # Get all tables with utf8mb4_unicode_ci
        cursor.execute("SHOW TABLES")
        tables = [list(row.values())[0] for row in cursor.fetchall()]
        
        tables_fixed = 0
        tables_failed = 0
        
        print(f"\nProcessing {len(tables)} tables...")
        
        for table in tables:
            try:
                cursor.execute(f"SHOW TABLE STATUS LIKE '{table}'")
                status = cursor.fetchone()
                collation = status['Collation']
                
                if collation and 'utf8mb4_unicode_ci' in collation:
                    cursor.execute(f"ALTER TABLE `{table}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci")
                    print(f"  ✓ Fixed {table}")
                    tables_fixed += 1
            except Exception as e:
                print(f"  ✗ Error fixing {table}: {str(e)}")
                tables_failed += 1
        
        # Re-enable foreign key checks
        print("\nRe-enabling foreign key checks...")
        cursor.execute("SET FOREIGN_KEY_CHECKS=1")
        
        connection.commit()
        
        print("\n" + "=" * 80)
        print("SUMMARY")
        print("=" * 80)
        print(f"Tables fixed: {tables_fixed}")
        print(f"Tables failed: {tables_failed}")
        
        if tables_failed == 0:
            print("\n✓ ALL COLLATIONS FIXED SUCCESSFULLY!")
        else:
            print(f"\n⚠ {tables_failed} tables could not be fixed")
        
except Exception as e:
    print(f"\n✗ Error: {str(e)}")
    connection.rollback()
finally:
    connection.close()