#!/usr/bin/env python3
"""
Ultimate Collation Fix - Resolves all MySQL collation conflicts
This script fixes the collation issues causing OperationalError 1267
"""

import os
import django
from django.db import connection, transaction
import logging
from datetime import datetime

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

# Setup logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('ultimate_collation_fix.log'),
        logging.StreamHandler()
    ]
)
logger = logging.getLogger(__name__)

class CollationFixer:
    def __init__(self):
        self.target_collation = 'utf8mb4_unicode_ci'
        self.target_charset = 'utf8mb4'
        self.foreign_keys_dropped = []
        
    def get_all_foreign_keys(self):
        """Get all foreign key constraints in the database"""
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT 
                    TABLE_NAME,
                    CONSTRAINT_NAME,
                    COLUMN_NAME,
                    REFERENCED_TABLE_NAME,
                    REFERENCED_COLUMN_NAME
                FROM information_schema.KEY_COLUMN_USAGE 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND REFERENCED_TABLE_NAME IS NOT NULL
                ORDER BY TABLE_NAME, CONSTRAINT_NAME
            """)
            return cursor.fetchall()
    
    def drop_all_foreign_keys(self):
        """Drop all foreign key constraints to allow collation changes"""
        logger.info("Dropping all foreign key constraints...")
        foreign_keys = self.get_all_foreign_keys()
        
        with connection.cursor() as cursor:
            for table_name, constraint_name, column_name, ref_table, ref_column in foreign_keys:
                try:
                    cursor.execute(f"ALTER TABLE `{table_name}` DROP FOREIGN KEY `{constraint_name}`")
                    self.foreign_keys_dropped.append({
                        'table': table_name,
                        'constraint': constraint_name,
                        'column': column_name,
                        'ref_table': ref_table,
                        'ref_column': ref_column
                    })
                    logger.info(f"✓ Dropped FK {constraint_name} from {table_name}")
                except Exception as e:
                    logger.warning(f"⚠ Could not drop FK {constraint_name}: {str(e)}")
        
        logger.info(f"Dropped {len(self.foreign_keys_dropped)} foreign key constraints")
    
    def get_all_tables(self):
        """Get all tables in the database"""
        with connection.cursor() as cursor:
            cursor.execute("SHOW TABLES")
            return [row[0] for row in cursor.fetchall()]
    
    def fix_table_collation(self, table_name):
        """Fix collation for a specific table"""
        try:
            with connection.cursor() as cursor:
                # Convert table to target collation
                cursor.execute(f"""
                    ALTER TABLE `{table_name}` 
                    CONVERT TO CHARACTER SET {self.target_charset} 
                    COLLATE {self.target_collation}
                """)
                logger.info(f"✓ Fixed collation for table {table_name}")
                return True
        except Exception as e:
            logger.error(f"✗ Failed to fix collation for {table_name}: {str(e)}")
            return False
    
    def recreate_foreign_keys(self):
        """Recreate all foreign key constraints"""
        logger.info("Recreating foreign key constraints...")
        success_count = 0
        error_count = 0
        
        with connection.cursor() as cursor:
            for fk in self.foreign_keys_dropped:
                try:
                    # Use original constraint name
                    cursor.execute(f"""
                        ALTER TABLE `{fk['table']}` 
                        ADD CONSTRAINT `{fk['constraint']}` 
                        FOREIGN KEY (`{fk['column']}`) 
                        REFERENCES `{fk['ref_table']}`(`{fk['ref_column']}`)
                    """)
                    logger.info(f"✓ Recreated FK {fk['constraint']} for {fk['table']}")
                    success_count += 1
                except Exception as e:
                    logger.warning(f"⚠ Could not recreate FK {fk['constraint']}: {str(e)}")
                    error_count += 1
        
        logger.info(f"Recreated {success_count} foreign keys, {error_count} failed")
    
    def verify_collations(self):
        """Verify that all tables have the correct collation"""
        logger.info("Verifying table collations...")
        
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT TABLE_NAME, TABLE_COLLATION 
                FROM information_schema.TABLES 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_TYPE = 'BASE TABLE'
                ORDER BY TABLE_NAME
            """)
            
            incorrect_tables = []
            for table_name, collation in cursor.fetchall():
                if collation != self.target_collation:
                    incorrect_tables.append((table_name, collation))
            
            if incorrect_tables:
                logger.warning(f"Found {len(incorrect_tables)} tables with incorrect collation:")
                for table, collation in incorrect_tables:
                    logger.warning(f"  {table}: {collation}")
                return False
            else:
                logger.info("✓ All tables have correct collation!")
                return True
    
    def run_fix(self):
        """Run the complete collation fix process"""
        logger.info("="*60)
        logger.info("ULTIMATE COLLATION FIX - Starting Process")
        logger.info("="*60)
        
        try:
            # Step 1: Drop all foreign keys
            self.drop_all_foreign_keys()
            
            # Step 2: Fix all table collations
            logger.info("Fixing table collations...")
            tables = self.get_all_tables()
            success_count = 0
            
            for table in tables:
                if self.fix_table_collation(table):
                    success_count += 1
            
            logger.info(f"Fixed collation for {success_count}/{len(tables)} tables")
            
            # Step 3: Recreate foreign keys
            self.recreate_foreign_keys()
            
            # Step 4: Verify results
            if self.verify_collations():
                logger.info("="*60)
                logger.info("✅ COLLATION FIX COMPLETED SUCCESSFULLY!")
                logger.info("✅ All MySQL collation conflicts resolved!")
                logger.info("✅ Your Django application should now work perfectly!")
                logger.info("="*60)
                return True
            else:
                logger.warning("⚠ Some tables still have incorrect collation")
                return False
                
        except Exception as e:
            logger.error(f"❌ Critical error during collation fix: {str(e)}")
            return False

def main():
    """Main execution function"""
    fixer = CollationFixer()
    success = fixer.run_fix()
    
    if success:
        print("\n🎉 SUCCESS! Collation issues have been resolved.")
        print("You can now access your Django application without collation errors.")
    else:
        print("\n⚠ WARNING: Some issues remain. Check the log file for details.")
    
    return success

if __name__ == "__main__":
    main()