#!/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)