#!/usr/bin/env python
"""
Emergency production fix for missing related_loan_id column in utils_notification table.
This script specifically addresses the OperationalError 1054 on client delete operations.
"""

import os
import sys
import django
import logging
from django.db import connection

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

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

def check_column_exists(table_name, column_name):
    """Check if a column exists in a table"""
    with connection.cursor() as cursor:
        cursor.execute(f"SHOW COLUMNS FROM {table_name} LIKE '{column_name}'")
        return cursor.fetchone() is not None

def add_related_loan_id_column():
    """Add related_loan_id column to utils_notification table"""
    logger.info("Adding related_loan_id column to utils_notification table...")
    
    try:
        with connection.cursor() as cursor:
            # Add column
            cursor.execute("""
                ALTER TABLE `utils_notification` 
                ADD COLUMN `related_loan_id` char(32) NULL
            """)
            logger.info("related_loan_id column added successfully")
            
            # Add index for performance
            cursor.execute("""
                ALTER TABLE `utils_notification` 
                ADD KEY `utils_notification_related_loan_id_idx` (`related_loan_id`)
            """)
            logger.info("Index added successfully")
            
            # Try to add foreign key constraint
            try:
                cursor.execute("""
                    ALTER TABLE `utils_notification` 
                    ADD CONSTRAINT `utils_notification_related_loan_id_fk` 
                    FOREIGN KEY (`related_loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE
                """)
                logger.info("Foreign key constraint added successfully")
            except Exception as fk_error:
                logger.warning(f"Could not add foreign key constraint: {fk_error}")
                logger.info("Column created without foreign key constraint - functionality will work")
                
    except Exception as e:
        logger.error(f"Error adding related_loan_id column: {e}")
        return False
    
    return True

def verify_fix():
    """Verify the fix was applied successfully"""
    logger.info("Verifying related_loan_id column exists...")
    
    if check_column_exists('utils_notification', 'related_loan_id'):
        logger.info("SUCCESS: related_loan_id column exists in utils_notification")
        return True
    else:
        logger.error("FAILED: related_loan_id column still missing")
        return False

def main():
    """Main execution function"""
    logger.info("Starting related_loan_id column fix for production...")
    
    try:
        # Check if column already exists
        if check_column_exists('utils_notification', 'related_loan_id'):
            logger.info("related_loan_id column already exists - no action needed")
            return True
        
        # Add the missing column
        if not add_related_loan_id_column():
            logger.error("Failed to add related_loan_id column")
            return False
        
        # Verify the fix
        if not verify_fix():
            logger.error("Fix verification failed")
            return False
        
        logger.info("Production fix completed successfully!")
        logger.info("Client delete operations should now work without OperationalError 1054")
        return True
        
    except Exception as e:
        logger.error(f"Unexpected error during fix: {e}")
        return False

if __name__ == "__main__":
    success = main()
    sys.exit(0 if success else 1)