#!/usr/bin/env python3
"""
Emergency fix for missing borrower_id column in receipts table
This script will add the missing borrower_id column to the receipts table in production
"""

import os
import sys
import django
import pymysql
from decimal import Decimal
import logging
from datetime import datetime

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

from django.conf import settings
from django.db import connection, transaction

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

def log_message(message, level="INFO"):
    timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    print(f"[{timestamp}] {level}: {message}")
    if level == "ERROR":
        logger.error(message)
    else:
        logger.info(message)

def check_column_exists(table_name, column_name):
    """Check if a column exists in a table"""
    try:
        with connection.cursor() as cursor:
            cursor.execute(f"""
                SELECT COUNT(*) 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = '{table_name}' 
                AND COLUMN_NAME = '{column_name}'
            """)
            result = cursor.fetchone()
            return result[0] > 0
    except Exception as e:
        log_message(f"Error checking column {column_name} in {table_name}: {e}", "ERROR")
        return False

def add_borrower_id_column():
    """Add borrower_id column to receipts table"""
    log_message("Adding borrower_id column to receipts table...")
    
    try:
        with connection.cursor() as cursor:
            # Add the borrower_id column
            cursor.execute("""
                ALTER TABLE receipts 
                ADD COLUMN borrower_id CHAR(32) NULL
            """)
            log_message("✓ borrower_id column added to receipts table")
            
            # Add index for performance
            cursor.execute("""
                CREATE INDEX IF NOT EXISTS idx_receipts_borrower_id 
                ON receipts (borrower_id)
            """)
            log_message("✓ Index created for borrower_id column")
            
            return True
            
    except Exception as e:
        log_message(f"Error adding borrower_id column: {e}", "ERROR")
        return False

def populate_borrower_id():
    """Populate borrower_id column from loan relationships"""
    log_message("Populating borrower_id column from existing data...")
    
    try:
        with connection.cursor() as cursor:
            # Update borrower_id from loan relationship
            cursor.execute("""
                UPDATE receipts r
                INNER JOIN loans l ON r.loan_id = l.id
                SET r.borrower_id = l.borrower_id
                WHERE r.borrower_id IS NULL
            """)
            
            affected_rows = cursor.rowcount
            log_message(f"✓ Updated {affected_rows} receipts with borrower_id")
            
            return True
            
    except Exception as e:
        log_message(f"Error populating borrower_id: {e}", "ERROR")
        return False

def verify_fix():
    """Verify that the fix was successful"""
    log_message("Verifying the fix...")
    
    try:
        with connection.cursor() as cursor:
            # Check if column exists
            if not check_column_exists('receipts', 'borrower_id'):
                log_message("❌ borrower_id column still missing", "ERROR")
                return False
            
            # Check for null values
            cursor.execute("""
                SELECT COUNT(*) FROM receipts WHERE borrower_id IS NULL
            """)
            null_count = cursor.fetchone()[0]
            
            if null_count > 0:
                log_message(f"⚠️  Warning: {null_count} receipts still have NULL borrower_id", "ERROR")
            else:
                log_message("✓ All receipts have borrower_id populated")
            
            # Test a simple query that was failing
            cursor.execute("""
                SELECT COUNT(*) FROM receipts WHERE borrower_id IS NOT NULL
            """)
            valid_count = cursor.fetchone()[0]
            log_message(f"✓ {valid_count} receipts have valid borrower_id")
            
            return True
            
    except Exception as e:
        log_message(f"Error verifying fix: {e}", "ERROR")
        return False

def main():
    """Main execution function"""
    log_message("Starting emergency fix for receipts.borrower_id column...")
    
    try:
        # Check if column already exists
        if check_column_exists('receipts', 'borrower_id'):
            log_message("borrower_id column already exists, checking population...")
            
            with connection.cursor() as cursor:
                cursor.execute("SELECT COUNT(*) FROM receipts WHERE borrower_id IS NULL")
                null_count = cursor.fetchone()[0]
                
                if null_count > 0:
                    log_message(f"Found {null_count} receipts with NULL borrower_id, populating...")
                    if not populate_borrower_id():
                        log_message("Failed to populate borrower_id", "ERROR")
                        return False
                else:
                    log_message("✓ borrower_id column already properly populated")
        else:
            # Add the column
            if not add_borrower_id_column():
                log_message("Failed to add borrower_id column", "ERROR")
                return False
            
            # Populate the column
            if not populate_borrower_id():
                log_message("Failed to populate borrower_id column", "ERROR")
                return False
        
        # Verify the fix
        if not verify_fix():
            log_message("Fix verification failed", "ERROR")
            return False
        
        log_message("✅ Emergency fix completed successfully!")
        log_message("The repayment functionality should now work properly.")
        
        return True
        
    except Exception as e:
        log_message(f"Critical error during fix: {e}", "ERROR")
        return False

if __name__ == "__main__":
    success = main()
    if not success:
        sys.exit(1)
    else:
        log_message("Fix completed. You can now test the repayment functionality.")