#!/usr/bin/env python
"""
Comprehensive fix for ALL missing columns in production database.
This addresses all OperationalError 1054 issues by adding 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('comprehensive_column_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_column(table_name, column_name, column_type, nullable=True, default=None):
    """Add a column to a table"""
    logger.info(f"Adding {column_name} column to {table_name} table...")
    
    try:
        with connection.cursor() as cursor:
            # Build column definition
            null_clause = "NULL" if nullable else "NOT NULL"
            default_clause = f"DEFAULT {default}" if default else ""
            
            # Add the column
            cursor.execute(f"""
                ALTER TABLE `{table_name}` 
                ADD COLUMN `{column_name}` {column_type} {null_clause} {default_clause}
            """)
            logger.info(f"{column_name} column added to {table_name}")
            
            # Add index for foreign key columns
            if column_name.endswith('_id'):
                cursor.execute(f"""
                    ALTER TABLE `{table_name}` 
                    ADD KEY `{table_name}_{column_name}_idx` (`{column_name}`)
                """)
                logger.info(f"Index added for {table_name}.{column_name}")
            
            return True
                
    except Exception as e:
        logger.error(f"Error adding {column_name} column to {table_name}: {e}")
        return False

def rename_column(table_name, old_column, new_column, column_type):
    """Rename a column in a table"""
    logger.info(f"Renaming {old_column} to {new_column} in {table_name} table...")
    
    try:
        with connection.cursor() as cursor:
            cursor.execute(f"""
                ALTER TABLE `{table_name}` 
                CHANGE COLUMN `{old_column}` `{new_column}` {column_type}
            """)
            logger.info(f"Column renamed from {old_column} to {new_column} in {table_name}")
            return True
                
    except Exception as e:
        logger.error(f"Error renaming column in {table_name}: {e}")
        return False

def fix_column_type(table_name, column_name, new_type):
    """Fix column data type"""
    try:
        with connection.cursor() as cursor:
            # First, update any invalid data to NULL
            cursor.execute(f"UPDATE {table_name} SET {column_name} = NULL WHERE LENGTH({column_name}) != 32 AND {column_name} IS NOT NULL")
            
            # Then modify the column type
            cursor.execute(f"ALTER TABLE {table_name} MODIFY COLUMN {column_name} {new_type} DEFAULT NULL")
            logger.info(f"Fixed column type: {table_name}.{column_name} -> {new_type}")
            return True
    except Exception as e:
        logger.error(f"Failed to fix column type {table_name}.{column_name}: {e}")
        return False

def fix_all_missing_columns():
    """Fix all known missing columns across all tables"""
    logger.info("Starting comprehensive column fix...")
    
    # Define all missing columns based on errors encountered
    missing_columns = [
        # Table, Column, Type, Nullable, Default
        ('notifications', 'related_loan_id', 'char(32)', True, None),
        ('notifications', 'related_application_id', 'char(32)', True, None),
        ('notifications', 'user_id', 'char(32)', False, None),
        ('receipts', 'loan_id', 'char(32)', False, None),
        ('loan_statements', 'loan_id', 'char(32)', False, None),
        ('utils_notification', 'related_loan_id', 'char(32)', True, None),
        ('utils_notification', 'loan_id', 'char(32)', True, None),
        ('offer_letters', 'application_id', 'char(32)', False, None),
        ('penalty_charges', 'created_at', 'datetime(6)', False, 'CURRENT_TIMESTAMP(6)'),
        ('receipts', 'repayment_id', 'char(32)', True, None),
        ('loan_applications', 'registration_fee_amount', 'decimal(12,2)', True, '0.00'),
        ('loans', 'registration_fee', 'decimal(12,2)', False, '0.00'),
        ('customer_requests', 'related_loan_id', 'char(32)', True, None),
        ('registration_fee_payments', 'related_loan_id', 'char(32)', True, None),
        ('users_groups', 'customuser_id', 'char(32)', False, None),
        ('users_user_permissions', 'customuser_id', 'char(32)', False, None),
        ('portfolio_performance', 'portfolio_manager_id', 'char(32)', True, None),
        ('portfolio_performance', 'verified_by_id', 'char(32)', True, None),
        ('user_permissions', 'granted_by_id', 'char(32)', True, None),
    ]
    
    # Column renames needed
    column_renames = [
        # Table, Old Column, New Column, Type
        ('report_schedules_recipients', 'user_id', 'customuser_id', 'char(32) NOT NULL'),
    ]
    
    # Column type fixes for incorrectly defined foreign keys
    column_type_fixes = [
        # Table, Column, New Type
        ('loans', 'borrower', 'char(32)'),
        ('loans', 'original_loan', 'char(32)'),
        ('loans', 'application', 'char(32)'),
        ('loan_applications', 'borrower', 'char(32)'),
        ('loan_applications', 'reviewed_by', 'char(32)'),
        ('loan_applications', 'loan_product', 'char(32)'),
        ('receipts', 'borrower', 'char(32)'),
        ('receipts', 'loan', 'char(32)'),
        ('receipts', 'repayment', 'char(32)'),
    ]
    fixed_columns = []
    failed_columns = []
    skipped_columns = []
    
    # Handle column renames first
    for table_name, old_column, new_column, column_type in column_renames:
        if not check_table_exists(table_name):
            logger.warning(f"Table {table_name} does not exist - skipping rename")
            skipped_columns.append(f"{table_name}.{old_column}->{new_column}")
            continue
            
        if check_column_exists(table_name, old_column) and not check_column_exists(table_name, new_column):
            if rename_column(table_name, old_column, new_column, column_type):
                fixed_columns.append(f"{table_name}.{old_column}->{new_column}")
            else:
                failed_columns.append(f"{table_name}.{old_column}->{new_column}")
        elif check_column_exists(table_name, new_column):
            logger.info(f"Column {new_column} already exists in {table_name}")
        else:
            logger.warning(f"Column {old_column} does not exist in {table_name} to rename")
    
    # Handle column type fixes
    for table_name, column_name, new_type in column_type_fixes:
        if not check_table_exists(table_name):
            logger.warning(f"Table {table_name} does not exist - skipping type fix")
            skipped_columns.append(f"{table_name}.{column_name}")
            continue
            
        if check_column_exists(table_name, column_name):
            if fix_column_type(table_name, column_name, new_type):
                fixed_columns.append(f"{table_name}.{column_name} (type fix)")
            else:
                failed_columns.append(f"{table_name}.{column_name} (type fix)")
        else:
            logger.warning(f"Column {column_name} does not exist in {table_name}")
            skipped_columns.append(f"{table_name}.{column_name}")
    
    # Handle missing columns
    for table_name, column_name, column_type, nullable, default in missing_columns:
        # Check if table exists
        if not check_table_exists(table_name):
            logger.warning(f"Table {table_name} does not exist - skipping {column_name}")
            skipped_columns.append(f"{table_name}.{column_name}")
            continue
        
        # Check if column already exists
        if check_column_exists(table_name, column_name):
            logger.info(f"Column {column_name} already exists in {table_name}")
            continue
        
        # Add the column
        if add_column(table_name, column_name, column_type, nullable, default):
            fixed_columns.append(f"{table_name}.{column_name}")
        else:
            failed_columns.append(f"{table_name}.{column_name}")
    
    return fixed_columns, failed_columns, skipped_columns

def create_missing_tables():
    """Create any missing tables that are referenced"""
    logger.info("Creating missing tables...")
    
    tables_to_create = [
        ('penalty_charges', """
            CREATE TABLE `penalty_charges` (
                `id` char(32) NOT NULL PRIMARY KEY,
                `amount` decimal(12,2) NOT NULL,
                `penalty_rate` decimal(5,2) NOT NULL,
                `days_overdue` int unsigned NOT NULL,
                `outstanding_amount` decimal(12,2) NOT NULL,
                `applied_date` datetime(6) NOT NULL,
                `loan_id` char(32) NOT NULL,
                `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
                KEY `penalty_charges_loan_id_idx` (`loan_id`),
                KEY `penalty_charges_applied_date_idx` (`applied_date`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """),
        ('customer_requests', """
            CREATE TABLE `customer_requests` (
                `id` char(32) NOT NULL PRIMARY KEY,
                `request_number` varchar(20) NOT NULL UNIQUE,
                `subject` varchar(200) NOT NULL,
                `description` longtext NOT NULL,
                `request_type` varchar(50) NOT NULL,
                `priority` varchar(20) NOT NULL DEFAULT 'medium',
                `status` varchar(20) NOT NULL DEFAULT 'pending',
                `assigned_to_id` char(32) NULL,
                `customer_id` char(32) NOT NULL,
                `related_loan_id` char(32) NULL,
                `related_application_id` char(32) NULL,
                `resolved_by_id` char(32) NULL,
                `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
                `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
                `resolved_at` datetime(6) NULL,
                KEY `customer_requests_customer_id_idx` (`customer_id`),
                KEY `customer_requests_status_idx` (`status`),
                KEY `customer_requests_created_at_idx` (`created_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """),
        ('registration_fee_payments', """
            CREATE TABLE `registration_fee_payments` (
                `id` char(32) NOT NULL PRIMARY KEY,
                `receipt_number` varchar(20) NOT NULL UNIQUE,
                `amount` decimal(12,2) NOT NULL,
                `payment_method` varchar(20) NOT NULL,
                `payment_date` datetime(6) NOT NULL,
                `transaction_reference` varchar(100) NULL,
                `payment_notes` longtext NULL,
                `customer_id` char(32) NOT NULL,
                `processed_by_id` char(32) NULL,
                `registration_fee_id` char(32) NOT NULL,
                `related_application_id` char(32) NULL,
                `related_loan_id` char(32) NULL,
                `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
                KEY `registration_fee_payments_customer_id_idx` (`customer_id`),
                KEY `registration_fee_payments_payment_date_idx` (`payment_date`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """),
        ('offer_letters', """
            CREATE TABLE `offer_letters` (
                `id` char(32) NOT NULL PRIMARY KEY,
                `letter_number` varchar(20) NOT NULL UNIQUE,
                `application_id` char(32) NOT NULL,
                `borrower_id` char(32) NOT NULL,
                `loan_amount` decimal(12,2) NOT NULL,
                `interest_rate` decimal(5,2) NOT NULL,
                `duration_days` int unsigned NOT NULL,
                `terms_conditions` longtext NOT NULL,
                `generated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
                `expires_at` datetime(6) NOT NULL,
                `status` varchar(20) NOT NULL DEFAULT 'pending',
                `signed_at` datetime(6) NULL,
                `pdf_file` varchar(100) NULL,
                KEY `offer_letters_application_id_idx` (`application_id`),
                KEY `offer_letters_borrower_id_idx` (`borrower_id`),
                KEY `offer_letters_status_idx` (`status`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """),
        ('portfolio_assignments', """
            CREATE TABLE `portfolio_assignments` (
                `id` char(32) NOT NULL PRIMARY KEY,
                `assignment_number` varchar(20) NOT NULL UNIQUE,
                `portfolio_manager_id` char(32) NOT NULL,
                `client_id` char(32) NOT NULL,
                `assigned_date` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
                `status` varchar(20) NOT NULL DEFAULT 'active',
                `notes` longtext NULL,
                `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
                `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
                KEY `portfolio_assignments_portfolio_manager_id_idx` (`portfolio_manager_id`),
                KEY `portfolio_assignments_client_id_idx` (`client_id`),
                KEY `portfolio_assignments_status_idx` (`status`),
                KEY `portfolio_assignments_assigned_date_idx` (`assigned_date`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """)
    ]
    
    created_tables = []
    
    for table_name, create_sql in tables_to_create:
        if not check_table_exists(table_name):
            try:
                with connection.cursor() as cursor:
                    cursor.execute(create_sql)
                logger.info(f"Created table: {table_name}")
                created_tables.append(table_name)
            except Exception as e:
                logger.error(f"Failed to create table {table_name}: {e}")
        else:
            logger.info(f"Table {table_name} already exists")
    
    return created_tables

def verify_all_fixes():
    """Verify all columns exist"""
    logger.info("Verifying all fixes...")
    
    # Check critical columns that cause errors
    critical_columns = [
        ('notifications', 'related_loan_id'),
        ('notifications', 'related_application_id'),
        ('receipts', 'loan_id'),
        ('loan_statements', 'loan_id'),
        ('offer_letters', 'application_id'),
        ('penalty_charges', 'created_at'),
        ('utils_notification', 'related_loan_id'),
        ('users_groups', 'customuser_id'),
        ('users_user_permissions', 'customuser_id'),
    ]
    
    missing = []
    
    for table_name, column_name in critical_columns:
        if check_table_exists(table_name):
            if not check_column_exists(table_name, column_name):
                missing.append(f"{table_name}.{column_name}")
                logger.error(f"MISSING: {column_name} in {table_name}")
            else:
                logger.info(f"FOUND: {column_name} in {table_name}")
        else:
            logger.warning(f"Table {table_name} does not exist")
    
    return missing

def main():
    """Main execution function"""
    logger.info("Starting comprehensive database fix for ALL missing columns...")
    
    try:
        # Create missing tables first
        created_tables = create_missing_tables()
        if created_tables:
            logger.info(f"Created tables: {created_tables}")
        
        # Fix all missing columns
        fixed, failed, skipped = fix_all_missing_columns()
        
        if fixed:
            logger.info(f"Successfully fixed columns: {fixed}")
        
        if failed:
            logger.error(f"Failed to fix columns: {failed}")
        
        if skipped:
            logger.warning(f"Skipped columns (table doesn't exist): {skipped}")
        
        # Verify all fixes
        missing = verify_all_fixes()
        
        if missing:
            logger.error(f"Some columns still missing: {missing}")
            return False
        
        logger.info("ALL DATABASE FIXES COMPLETED SUCCESSFULLY!")
        logger.info("All client operations should now work without OperationalError 1054")
        return True
        
    except Exception as e:
        logger.error(f"Unexpected error during comprehensive fix: {e}")
        return False

if __name__ == "__main__":
    success = main()
    sys.exit(0 if success else 1)
