#!/usr/bin/env python
"""
Fix all table collations 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 TABLE COLLATIONS (FORCE MODE)")
print("=" * 80)

try:
    with connection.cursor() as cursor:
        # Disable foreign key checks
        cursor.execute("SET FOREIGN_KEY_CHECKS=0")
        
        # Get all tables
        cursor.execute("SHOW TABLES")
        tables = [list(row.values())[0] for row in cursor.fetchall()]
        
        print(f"\nFixing {len(tables)} tables...")
        
        fixed = 0
        errors = 0
        
        for table in tables:
            try:
                cursor.execute(f"ALTER TABLE `{table}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci")
                print(f"  ✓ {table}")
                fixed += 1
            except Exception as e:
                print(f"  ✗ {table}: {str(e)}")
                errors += 1
        
        # Re-enable foreign key checks
        cursor.execute("SET FOREIGN_KEY_CHECKS=1")
        
        connection.commit()
        
        print("\n" + "=" * 80)
        print(f"✓ COMPLETE - Fixed {fixed} tables, {errors} errors")
        print("=" * 80)
        
except Exception as e:
    print(f"\n✗ Error: {str(e)}")
    connection.rollback()
finally:
    connection.close()