#!/usr/bin/env python
"""
Fix RolloverRequest schema by directly adding missing columns to the database.
This script is designed to run in production without requiring Django migrations.
"""
import os
import sys
import django
import logging
from decimal import Decimal

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

# Set up Django environment
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

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

def get_table_name(model_name):
    """Get the actual table name for a model by checking what exists in the database"""
    possible_names = [
        f'loans_{model_name.lower()}',
        f'loan_{model_name.lower()}',
        f'{model_name.lower()}',
        f'xygbfpsg_graz.loans_{model_name.lower()}',
        f'xygbfpsg_graz.loan_{model_name.lower()}'
    ]
    
    with connection.cursor() as cursor:
        for name in possible_names:
            try:
                if 'mysql' in connection.vendor:
                    cursor.execute(f"SHOW TABLES LIKE '{name}'")
                elif 'sqlite' in connection.vendor:
                    cursor.execute(f"SELECT name FROM sqlite_master WHERE type='table' AND name='{name}'")
                else:  # PostgreSQL
                    cursor.execute(f"SELECT to_regclass('{name}')")
                
                result = cursor.fetchone()
                if result:
                    logger.info(f"Found table: {name}")
                    return name
            except Exception as e:
                logger.warning(f"Error checking table {name}: {str(e)}")
    
    # If we get here, try to get the table name from Django's model
    try:
        from loans.models import RolloverRequest
        table_name = RolloverRequest._meta.db_table
        logger.info(f"Using Django model table name: {table_name}")
        return table_name
    except:
        logger.warning("Could not determine table name from Django model")
    
    # Default fallback
    return 'loans_rolloverrequest'

def check_column_exists(table_name, column_name):
    """Check if a column exists in a table"""
    try:
        with connection.cursor() as cursor:
            if 'mysql' in connection.vendor:
                cursor.execute("""
                    SELECT COUNT(*)
                    FROM information_schema.COLUMNS
                    WHERE TABLE_SCHEMA = DATABASE()
                    AND TABLE_NAME = %s
                    AND COLUMN_NAME = %s
                """, [table_name.split('.')[-1], column_name])
            elif 'sqlite' in connection.vendor:
                cursor.execute(f"PRAGMA table_info({table_name})")
                columns = [col[1] for col in cursor.fetchall()]
                return column_name in columns
            else:
                cursor.execute("""
                    SELECT COUNT(*)
                    FROM information_schema.columns
                    WHERE table_name = %s
                    AND column_name = %s
                """, [table_name.split('.')[-1], column_name])
            
            if 'sqlite' not in connection.vendor:
                result = cursor.fetchone()[0]
                return result > 0
        
        return False
    except Exception as e:
        logger.error(f"Error checking if column exists: {str(e)}")
        return False

def add_column_if_not_exists(table_name, column_name, column_definition):
    """Add a column to a table if it doesn't exist"""
    try:
        if not check_column_exists(table_name, column_name):
            logger.info(f"Adding column {column_name} to {table_name}")
            with connection.cursor() as cursor:
                if 'mysql' in connection.vendor:
                    cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN {column_name} {column_definition}")
                elif 'sqlite' in connection.vendor:
                    cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN {column_name} {column_definition}")
                else:  # PostgreSQL
                    cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN IF NOT EXISTS {column_name} {column_definition}")
            return True
        else:
            logger.info(f"Column {column_name} already exists in {table_name}")
            return False
    except Exception as e:
        logger.error(f"Error adding column: {str(e)}")
        return False

def fix_rollover_schema():
    """Fix the RolloverRequest schema by adding missing columns"""
    # Get the actual table name
    table_name = get_table_name('rolloverrequest')
    
    if not table_name:
        logger.error("Could not find RolloverRequest table")
        return False
    
    try:
        with transaction.atomic():
            # Add missing columns with appropriate definitions
            columns_to_add = {
                'requested_amount': 'DECIMAL(12,2) NOT NULL DEFAULT 0',
                'requested_duration': 'INTEGER NOT NULL DEFAULT 30',
                'requested_interest_rate': 'DECIMAL(5,2) NULL',
                'requested_processing_fee': 'DECIMAL(12,2) NULL',
            }
            
            for column_name, definition in columns_to_add.items():
                added = add_column_if_not_exists(table_name, column_name, definition)
                if added:
                    logger.info(f"Successfully added column {column_name} to {table_name}")
            
            # Try to update the requested_amount to match the loan amount for existing records
            try:
                loan_table = get_table_name('loan')
                if loan_table:
                    with connection.cursor() as cursor:
                        try:
                            cursor.execute(f"""
                                UPDATE {table_name} rr
                                JOIN {loan_table} l ON rr.loan_id = l.id
                                SET rr.requested_amount = l.amount
                                WHERE rr.requested_amount = 0
                            """)
                            rows_updated = cursor.rowcount
                            logger.info(f"Updated requested_amount for {rows_updated} existing rollover requests")
                            
                            cursor.execute(f"""
                                UPDATE {table_name} rr
                                JOIN {loan_table} l ON rr.loan_id = l.id
                                SET rr.requested_duration = l.duration_days
                                WHERE rr.requested_duration = 30
                            """)
                            rows_updated = cursor.rowcount
                            logger.info(f"Updated requested_duration for {rows_updated} existing rollover requests")
                        except Exception as e:
                            logger.warning(f"Could not update existing records: {str(e)}")
            except Exception as e:
                logger.warning(f"Error updating existing records: {str(e)}")
            
            logger.info("Schema fix completed successfully")
            return True
            
    except Exception as e:
        logger.error(f"Error fixing schema: {str(e)}")
        return False

def fix_migration_history():
    """Fix inconsistent migration history by updating the django_migrations table"""
    try:
        with transaction.atomic():
            with connection.cursor() as cursor:
                # Check if the problematic migration exists
                cursor.execute("""
                    SELECT id FROM django_migrations 
                    WHERE app = 'users' AND name = '0012_branch'
                """)
                migration_exists = cursor.fetchone()
                
                if migration_exists:
                    # Check if the dependency exists
                    cursor.execute("""
                        SELECT id FROM django_migrations 
                        WHERE app = 'users' AND name = '0011_manual_add_is_default'
                    """)
                    dependency_exists = cursor.fetchone()
                    
                    if not dependency_exists:
                        # Add the missing dependency migration with an earlier applied date
                        try:
                            cursor.execute("""
                                INSERT INTO django_migrations (app, name, applied)
                                SELECT 'users', '0011_manual_add_is_default', 
                                       (SELECT applied FROM django_migrations 
                                        WHERE app = 'users' AND name = '0012_branch') - INTERVAL 1 SECOND
                            """)
                            logger.info("Added missing migration users.0011_manual_add_is_default")
                        except Exception as e:
                            # Try alternative syntax for SQLite
                            try:
                                cursor.execute("""
                                    INSERT INTO django_migrations (app, name, applied)
                                    SELECT 'users', '0011_manual_add_is_default', 
                                           datetime((SELECT applied FROM django_migrations 
                                            WHERE app = 'users' AND name = '0012_branch'), '-1 second')
                                """)
                                logger.info("Added missing migration users.0011_manual_add_is_default (SQLite syntax)")
                            except Exception as e2:
                                logger.error(f"Could not add migration: {str(e2)}")
                    else:
                        logger.info("Dependency migration already exists")
                else:
                    logger.info("Problematic migration does not exist")
            
            logger.info("Migration history fix completed successfully")
            return True
            
    except Exception as e:
        logger.error(f"Error fixing migration history: {str(e)}")
        return False

def modify_loan_detail_view():
    """Modify the loan_detail view to work without the missing columns"""
    try:
        # Try to find the views.py file
        import os
        from django.conf import settings
        
        base_dir = settings.BASE_DIR
        views_file = os.path.join(base_dir, 'loans', 'views.py')
        
        if not os.path.exists(views_file):
            logger.warning(f"Could not find views file at {views_file}")
            return False
        
        # Create a backup
        backup_file = views_file + '.bak'
        try:
            import shutil
            shutil.copy2(views_file, backup_file)
            logger.info(f"Created backup of views.py at {backup_file}")
        except Exception as e:
            logger.warning(f"Could not create backup: {str(e)}")
        
        # Read the file
        with open(views_file, 'r') as f:
            content = f.read()
        
        # Check if we need to modify the file
        if "RolloverRequest.objects.filter(loan=loan)" in content:
            # Replace the problematic line with raw SQL
            modified_content = content.replace(
                "rollovers = RolloverRequest.objects.filter(loan=loan).order_by('-requested_at')",
                """# Modified by fix_rollover_schema.py
    try:
        rollovers = RolloverRequest.objects.filter(loan=loan).order_by('-requested_at')
    except Exception as e:
        # Fallback to raw SQL if there's an error
        from django.db import connection
        with connection.cursor() as cursor:
            cursor.execute(\"\"\"
                SELECT id, loan_id, borrower_id, reason, rollover_fee, status, 
                       reviewed_by_id, reviewed_at, review_notes, requested_at, 
                       created_at, updated_at
                FROM loans_rolloverrequest 
                WHERE loan_id = %s 
                ORDER BY requested_at DESC
            \"\"\", [str(loan.id)])
            columns = [col[0] for col in cursor.description]
            rollovers = [dict(zip(columns, row)) for row in cursor.fetchall()]"""
            )
            
            # Write the modified content
            with open(views_file, 'w') as f:
                f.write(modified_content)
            
            logger.info("Modified loan_detail view to work without the missing columns")
            return True
        else:
            logger.info("No need to modify loan_detail view")
            return True
            
    except Exception as e:
        logger.error(f"Error modifying loan_detail view: {str(e)}")
        return False

if __name__ == "__main__":
    logger.info("Starting database schema fix script")
    
    # Fix the schema issues
    schema_fixed = fix_rollover_schema()
    
    # Fix migration history issues
    migration_fixed = fix_migration_history()
    
    # Modify the loan_detail view as a fallback
    view_modified = modify_loan_detail_view()
    
    if schema_fixed and migration_fixed:
        logger.info("All fixes applied successfully")
        sys.exit(0)
    else:
        logger.error("Some fixes failed to apply")
        sys.exit(1)