#!/usr/bin/env python3 """ Production fix for receipts.repayment_id column issue Run this on your production server to fix the OperationalError """ import os import sys import django from datetime import datetime def log_message(message, level='INFO'): """Log messages to console""" timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S') print(f"[{timestamp}] {level}: {message}") def fix_production_receipts(): """Fix all missing columns in production receipts table""" log_message("Fixing production receipts table...") try: from django.db import connection with connection.cursor() as cursor: # First, check if receipts table exists cursor.execute(""" SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'receipts' """) table_exists = cursor.fetchone() if not table_exists: log_message("Creating receipts table...") cursor.execute(""" CREATE TABLE receipts ( id CHAR(32) NOT NULL PRIMARY KEY, repayment_id CHAR(32) NULL, loan_id CHAR(32) NOT NULL, borrower_id CHAR(32) NOT NULL, receipt_number VARCHAR(20) NOT NULL UNIQUE, amount_paid DECIMAL(12,2) NOT NULL, payment_method VARCHAR(20) NOT NULL, payment_date DATETIME(6) NOT NULL, previous_balance DECIMAL(12,2) NOT NULL, new_balance DECIMAL(12,2) NOT NULL, pdf_file VARCHAR(100) NULL, created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ) """) log_message("SUCCESS: Created receipts table") return True log_message("SUCCESS: Receipts table exists, checking for missing columns...") # Required columns for receipts table required_columns = [ ('repayment_id', 'CHAR(32) NULL'), ('loan_id', 'CHAR(32) NOT NULL'), ('borrower_id', 'CHAR(32) NOT NULL'), ('receipt_number', 'VARCHAR(20) NOT NULL'), ('amount_paid', 'DECIMAL(12,2) NOT NULL'), ('payment_method', 'VARCHAR(20) NOT NULL'), ('payment_date', 'DATETIME(6) NOT NULL'), ('previous_balance', 'DECIMAL(12,2) NOT NULL'), ('new_balance', 'DECIMAL(12,2) NOT NULL'), ('pdf_file', 'VARCHAR(100) NULL'), ('created_at', 'DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6)'), ] # Check and add missing columns for column_name, column_definition in required_columns: cursor.execute(""" SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'receipts' AND COLUMN_NAME = %s """, [column_name]) if not cursor.fetchone(): log_message(f"Adding missing {column_name} column...") try: cursor.execute(f""" ALTER TABLE receipts ADD COLUMN {column_name} {column_definition} """) log_message(f"SUCCESS: Added {column_name} column") except Exception as e: log_message(f"ERROR: Failed to add {column_name}: {e}", 'ERROR') return False else: log_message(f"SUCCESS: {column_name} column exists") # Add unique constraint on receipt_number if it doesn't exist try: cursor.execute(""" ALTER TABLE receipts ADD CONSTRAINT uk_receipts_receipt_number UNIQUE (receipt_number) """) log_message("SUCCESS: Added unique constraint on receipt_number") except Exception as e: if "Duplicate key name" in str(e) or "already exists" in str(e): log_message("SUCCESS: Unique constraint on receipt_number already exists") else: log_message(f"Note: Could not add unique constraint: {e}", 'WARNING') # Also ensure repayments table exists cursor.execute(""" SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'repayments' """) if not cursor.fetchone(): log_message("Creating repayments table...") cursor.execute(""" CREATE TABLE repayments ( id CHAR(32) NOT NULL PRIMARY KEY, loan_id CHAR(32) NOT NULL, amount DECIMAL(12,2) NOT NULL, payment_method VARCHAR(20) NOT NULL DEFAULT 'mpesa', mpesa_transaction_id VARCHAR(50) NULL, mpesa_phone_number VARCHAR(17) NULL, receipt_number VARCHAR(20) NOT NULL UNIQUE, payment_date DATETIME(6) NOT NULL, created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ) """) log_message("SUCCESS: Created repayments table") else: log_message("SUCCESS: Repayments table exists") # Add essential indexes if they don't exist essential_indexes = [ ('idx_receipts_loan_id', 'receipts', 'loan_id'), ('idx_receipts_borrower_id', 'receipts', 'borrower_id'), ('idx_receipts_receipt_number', 'receipts', 'receipt_number'), ('idx_repayments_loan_id', 'repayments', 'loan_id'), ('idx_repayments_receipt_number', 'repayments', 'receipt_number'), ] for index_name, table_name, column_name in essential_indexes: try: cursor.execute(f""" CREATE INDEX {index_name} ON {table_name} ({column_name}) """) log_message(f"SUCCESS: Created index {index_name}") except Exception as e: if "Duplicate key name" in str(e) or "already exists" in str(e): log_message(f"SUCCESS: Index {index_name} already exists") else: log_message(f"WARNING: Could not create index {index_name}: {e}", 'WARNING') return True except Exception as e: log_message(f"ERROR: Error fixing receipts table: {e}", 'ERROR') import traceback log_message(f"Traceback: {traceback.format_exc()}", 'ERROR') return False def main(): """Main execution""" log_message("HAVEN GRAZURI INVESTMENT LIMITED- Production Receipts Fix") log_message("=" * 55) try: # Setup Django with production settings os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production') django.setup() log_message("Django setup completed") if fix_production_receipts(): log_message("\n" + "=" * 55) log_message("SUCCESS: PRODUCTION FIX COMPLETED SUCCESSFULLY!") log_message("The receipts.repayment_id column issue has been resolved.") log_message("Your loan repayment functionality should now work.") log_message("You can now process loan repayments without errors.") log_message("=" * 55) return True else: log_message("\n" + "=" * 55) log_message("ERROR: PRODUCTION FIX FAILED!") log_message("Please check the error messages above.") log_message("Contact support if the issue persists.") log_message("=" * 55) return False except Exception as e: log_message(f"\nERROR: Critical error during setup: {e}", 'ERROR') import traceback log_message(f"Traceback: {traceback.format_exc()}", 'ERROR') return False if __name__ == "__main__": success = main() sys.exit(0 if success else 1)