#!/usr/bin/env python3
"""
Advanced Collation Fix with Foreign Key Handling
Fixes the remaining 15 tables that have foreign key constraint issues
"""

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

# 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('foreign_key_collation_fix.log'),
        logging.StreamHandler()
    ]
)
logger = logging.getLogger(__name__)

def get_foreign_keys(table_name):
    """Get all foreign key constraints for a table"""
    with connection.cursor() as cursor:
        cursor.execute(f"""
            SELECT 
                CONSTRAINT_NAME,
                COLUMN_NAME,
                REFERENCED_TABLE_NAME,
                REFERENCED_COLUMN_NAME
            FROM information_schema.KEY_COLUMN_USAGE 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = %s 
            AND REFERENCED_TABLE_NAME IS NOT NULL
        """, [table_name])
        return cursor.fetchall()

def drop_foreign_keys(table_name):
    """Drop all foreign key constraints for a table"""
    foreign_keys = get_foreign_keys(table_name)
    dropped_constraints = []
    
    with connection.cursor() as cursor:
        for constraint_name, column_name, ref_table, ref_column in foreign_keys:
            try:
                cursor.execute(f"ALTER TABLE `{table_name}` DROP FOREIGN KEY `{constraint_name}`")
                dropped_constraints.append((constraint_name, column_name, ref_table, ref_column))
                logger.info(f"✓ Dropped foreign key {constraint_name} from {table_name}")
            except Exception as e:
                logger.error(f"✗ Failed to drop foreign key {constraint_name}: {str(e)}")
    
    return dropped_constraints

def recreate_foreign_keys(table_name, constraints):
    """Recreate foreign key constraints after collation fix"""
    with connection.cursor() as cursor:
        for constraint_name, column_name, ref_table, ref_column in constraints:
            try:
                # Create new constraint name to avoid conflicts
                new_constraint_name = f"{constraint_name}_new"
                cursor.execute(f"""
                    ALTER TABLE `{table_name}` 
                    ADD CONSTRAINT `{new_constraint_name}` 
                    FOREIGN KEY (`{column_name}`) 
                    REFERENCES `{ref_table}`(`{ref_column}`)
                """)
                logger.info(f"✓ Recreated foreign key {new_constraint_name} for {table_name}")
            except Exception as e:
                logger.error(f"✗ Failed to recreate foreign key {constraint_name}: {str(e)}")

def fix_table_with_foreign_keys(table_name):
    """Fix table collation by temporarily dropping foreign keys"""
    logger.info(f"Fixing table with foreign keys: {table_name}")
    
    try:
        # Step 1: Drop foreign key constraints
        dropped_constraints = drop_foreign_keys(table_name)
        
        # Step 2: Convert table collation
        with connection.cursor() as cursor:
            cursor.execute(f"ALTER TABLE `{table_name}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
            logger.info(f"✓ Converted {table_name} to utf8mb4_unicode_ci")
        
        # Step 3: Recreate foreign key constraints
        recreate_foreign_keys(table_name, dropped_constraints)
        
        return True
        
    except Exception as e:
        logger.error(f"✗ Error fixing table {table_name}: {str(e)}")
        return False

def main():
    """Fix the remaining tables with foreign key issues"""
    logger.info("Starting foreign key collation fix...")
    
    # Tables that failed in the previous run
    problem_tables = [
        'django_admin_log',
        'loan_applications', 
        'loan_scoring',
        'loan_statements',
        'loans',
        'notifications',
        'otp_verifications',
        'receipts',
        'repayments',
        'user_access_logs',
        'users',
        'utils_auditlog',
        'utils_document',
        'utils_documentshare',
        'utils_notification'
    ]
    
    success_count = 0
    error_count = 0
    
    for table in problem_tables:
        logger.info(f"Processing {table}...")
        if fix_table_with_foreign_keys(table):
            success_count += 1
        else:
            error_count += 1
    
    # Summary
    logger.info("="*50)
    logger.info("FOREIGN KEY COLLATION FIX SUMMARY")
    logger.info("="*50)
    logger.info(f"Total tables processed: {len(problem_tables)}")
    logger.info(f"Successfully fixed: {success_count}")
    logger.info(f"Errors encountered: {error_count}")
    
    if error_count == 0:
        logger.info("✓ All foreign key collation issues resolved!")
        logger.info("✓ Your Django application should now work perfectly!")
    else:
        logger.warning(f"⚠ {error_count} tables still have issues.")

if __name__ == "__main__":
    main()