#!/usr/bin/env python
"""
Comprehensive fix for all missing loan_id columns in production database.
This addresses multiple OperationalError 1054 issues with missing foreign key columns.
"""

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('missing_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_loan_id_column(table_name, nullable=False):
    """Add loan_id column to specified table"""
    logger.info(f"Adding loan_id column to {table_name} table...")
    
    try:
        with connection.cursor() as cursor:
            # Determine column definition based on nullable requirement
            column_def = "char(32) NULL" if nullable else "char(32) NOT NULL"
            
            # Add the column
            cursor.execute(f"""
                ALTER TABLE `{table_name}` 
                ADD COLUMN `loan_id` {column_def}
            """)
            logger.info(f"loan_id column added to {table_name}")
            
            # Add index for performance
            cursor.execute(f"""
                ALTER TABLE `{table_name}` 
                ADD KEY `{table_name}_loan_id_idx` (`loan_id`)
            """)
            logger.info(f"Index added for {table_name}.loan_id")
            
            # Try to add foreign key constraint
            try:
                cursor.execute(f"""
                    ALTER TABLE `{table_name}` 
                    ADD CONSTRAINT `{table_name}_loan_id_fk` 
                    FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE
                """)
                logger.info(f"Foreign key constraint added for {table_name}.loan_id")
            except Exception as fk_error:
                logger.warning(f"Could not add foreign key constraint for {table_name}: {fk_error}")
                logger.info(f"Column created without foreign key constraint - functionality will work")
                
    except Exception as e:
        logger.error(f"Error adding loan_id column to {table_name}: {e}")
        return False
    
    return True

def fix_missing_columns():
    """Fix all missing loan_id columns across tables"""
    logger.info("Checking and fixing missing loan_id columns...")
    
    # Tables that need loan_id columns
    tables_to_fix = [
        ('receipts', False),  # NOT NULL
        ('loan_statements', False),  # NOT NULL
        ('utils_notification', True),  # NULL allowed (already exists but checking)
    ]
    
    fixed_tables = []
    failed_tables = []
    
    for table_name, nullable in tables_to_fix:
        if not check_column_exists(table_name, 'loan_id'):
            logger.info(f"Missing loan_id column in {table_name}")
            if add_loan_id_column(table_name, nullable):
                fixed_tables.append(table_name)
            else:
                failed_tables.append(table_name)
        else:
            logger.info(f"loan_id column already exists in {table_name}")
    
    return fixed_tables, failed_tables

def verify_all_fixes():
    """Verify all loan_id columns exist"""
    logger.info("Verifying all loan_id columns exist...")
    
    tables_to_check = ['receipts', 'loan_statements', 'utils_notification']
    missing_columns = []
    
    for table_name in tables_to_check:
        if not check_column_exists(table_name, 'loan_id'):
            missing_columns.append(f"{table_name}.loan_id")
            logger.error(f"MISSING: loan_id column in {table_name}")
        else:
            logger.info(f"FOUND: loan_id column in {table_name}")
    
    return missing_columns

def main():
    """Main execution function"""
    logger.info("Starting comprehensive loan_id columns fix...")
    
    try:
        # Fix missing columns
        fixed_tables, failed_tables = fix_missing_columns()
        
        if failed_tables:
            logger.error(f"Failed to fix tables: {failed_tables}")
            return False
        
        if fixed_tables:
            logger.info(f"Successfully fixed tables: {fixed_tables}")
        
        # Verify all fixes
        missing = verify_all_fixes()
        
        if missing:
            logger.error(f"Some columns still missing: {missing}")
            return False
        
        logger.info("All loan_id columns 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)