#!/usr/bin/env python
"""
Fix for missing related_loan_id column in notifications table (not utils_notification).
This addresses the persistent production error with notifications table.
"""

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

def check_table_exists(table_name):
    """Check if a table exists"""
    with connection.cursor() as cursor:
        cursor.execute(f"SHOW TABLES LIKE '{table_name}'")
        return cursor.fetchone() is not None

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_to_notifications():
    """Add related_loan_id column to notifications table"""
    logger.info("Adding related_loan_id column to notifications table...")
    
    try:
        with connection.cursor() as cursor:
            # Add the column
            cursor.execute("""
                ALTER TABLE `notifications` 
                ADD COLUMN `related_loan_id` char(32) NULL
            """)
            logger.info("related_loan_id column added to notifications table")
            
            # Add index for performance
            cursor.execute("""
                ALTER TABLE `notifications` 
                ADD KEY `notifications_related_loan_id_idx` (`related_loan_id`)
            """)
            logger.info("Index added for notifications.related_loan_id")
            
            # Try to add foreign key constraint
            try:
                cursor.execute("""
                    ALTER TABLE `notifications` 
                    ADD CONSTRAINT `notifications_related_loan_id_fk` 
                    FOREIGN KEY (`related_loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE
                """)
                logger.info("Foreign key constraint added for notifications.related_loan_id")
            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 to notifications: {e}")
        return False
    
    return True

def main():
    """Main execution function"""
    logger.info("Starting notifications table fix...")
    
    try:
        # Check if notifications table exists
        if not check_table_exists('notifications'):
            logger.error("notifications table does not exist!")
            return False
        
        # Check if column already exists
        if check_column_exists('notifications', 'related_loan_id'):
            logger.info("related_loan_id column already exists in notifications table")
            return True
        
        # Add the missing column
        if not add_related_loan_id_to_notifications():
            logger.error("Failed to add related_loan_id column to notifications")
            return False
        
        # Verify the fix
        if check_column_exists('notifications', 'related_loan_id'):
            logger.info("SUCCESS: related_loan_id column exists in notifications table")
        else:
            logger.error("FAILED: related_loan_id column still missing from notifications table")
            return False
        
        logger.info("Notifications table fix completed successfully!")
        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)