#!/usr/bin/env python
"""
Fix for missing related_loan field in utils.Notification model.
This addresses the production error: OperationalError (1054, "Unknown column 'notifications.related_loan_id' in 'where clause'")
"""

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('utils_notification_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_field():
    """Add related_loan_id field to utils_notification table"""
    logger.info("Adding related_loan_id field to utils_notification table...")
    
    try:
        with connection.cursor() as cursor:
            # Add the 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 field: {e}")
        return False
    
    return True

def apply_migration():
    """Apply the migration for the new field"""
    logger.info("Applying utils migration 0015...")
    
    try:
        from django.core.management import call_command
        call_command('migrate', 'utils', '0015', verbosity=2)
        logger.info("Migration applied successfully")
        return True
    except Exception as e:
        logger.error(f"Error applying migration: {e}")
        return False

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

def main():
    """Main execution function"""
    logger.info("Starting utils Notification model fix...")
    
    try:
        # Check if field already exists
        if check_column_exists('utils_notification', 'related_loan_id'):
            logger.info("related_loan_id field already exists - no action needed")
            return True
        
        # Try to apply migration first
        if apply_migration():
            logger.info("Migration applied successfully")
        else:
            logger.info("Migration failed, trying direct SQL approach...")
            # Fallback to direct SQL
            if not add_related_loan_field():
                logger.error("Failed to add related_loan_id field")
                return False
        
        # Verify the fix
        if not verify_fix():
            logger.error("Fix verification failed")
            return False
        
        logger.info("Utils Notification model 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)
