#!/usr/bin/env python
"""
Fix for missing loan_id column in receipts table.
This addresses the production error: OperationalError (1054, "Unknown column 'receipts.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('receipts_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():
    """Add loan_id column to receipts table"""
    logger.info("Adding loan_id column to receipts table...")
    
    try:
        with connection.cursor() as cursor:
            # Add the column
            cursor.execute("""
                ALTER TABLE `receipts` 
                ADD COLUMN `loan_id` char(32) NOT NULL
            """)
            logger.info("loan_id column added successfully")
            
            # Add index for performance
            cursor.execute("""
                ALTER TABLE `receipts` 
                ADD KEY `receipts_loan_id_idx` (`loan_id`)
            """)
            logger.info("Index added successfully")
            
            # Try to add foreign key constraint
            try:
                cursor.execute("""
                    ALTER TABLE `receipts` 
                    ADD CONSTRAINT `receipts_loan_id_fk` 
                    FOREIGN KEY (`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 loan_id column: {e}")
        return False
    
    return True

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

def main():
    """Main execution function"""
    logger.info("Starting receipts loan_id column fix...")
    
    try:
        # Check if column already exists
        if check_column_exists('receipts', 'loan_id'):
            logger.info("loan_id column already exists in receipts - no action needed")
            return True
        
        # Add the missing column
        if not add_loan_id_column():
            logger.error("Failed to add loan_id column to receipts")
            return False
        
        # Verify the fix
        if not verify_fix():
            logger.error("Fix verification failed")
            return False
        
        logger.info("Receipts loan_id column 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)