#!/usr/bin/env python3 """ Complete fix for all receipts table issues - ASCII version This script fixes all missing columns in the receipts table without Unicode characters """ 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_receipts_table_complete(): """Complete fix for receipts table - recreate if necessary""" log_message("Complete fix for receipts table...") try: from django.db import connection with connection.cursor() as cursor: # Check current table structure cursor.execute(""" SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'receipts' ORDER BY ORDINAL_POSITION """) existing_columns = cursor.fetchall() log_message(f"Found {len(existing_columns)} existing columns in receipts table") if existing_columns: for col in existing_columns: log_message(f" - {col[0]} ({col[1]}, nullable: {col[2]})") # Required columns with their definitions required_columns = { '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', '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)' } existing_column_names = [col[0] for col in existing_columns] if existing_columns else [] missing_columns = [] for col_name in required_columns: if col_name not in existing_column_names: missing_columns.append(col_name) if not existing_columns: # Table doesn't exist, create it log_message("Creating receipts table from scratch...") create_sql = f""" CREATE TABLE receipts ( {', '.join([f'{name} {definition}' for name, definition in required_columns.items()])} ) """ cursor.execute(create_sql) log_message("SUCCESS: Created receipts table") elif missing_columns: # Add missing columns log_message(f"Adding {len(missing_columns)} missing columns...") for col_name in missing_columns: log_message(f"Adding {col_name}...") try: # Skip PRIMARY KEY constraint when adding to existing table definition = required_columns[col_name].replace(' PRIMARY KEY', '') cursor.execute(f""" ALTER TABLE receipts ADD COLUMN {col_name} {definition} """) log_message(f"SUCCESS: Added {col_name}") except Exception as e: log_message(f"ERROR: Failed to add {col_name}: {e}", 'ERROR') return False else: log_message("SUCCESS: All required columns exist") # Add essential indexes indexes = [ ('idx_receipts_repayment_id', 'repayment_id'), ('idx_receipts_loan_id', 'loan_id'), ('idx_receipts_borrower_id', 'borrower_id'), ('idx_receipts_receipt_number', 'receipt_number'), ('idx_receipts_payment_date', 'payment_date'), ] for index_name, column_name in indexes: try: cursor.execute(f""" CREATE INDEX {index_name} ON receipts ({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') # Add unique constraint on receipt_number 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 already exists") else: log_message(f"WARNING: Could not add unique constraint: {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 fix_repayments_table(): """Ensure repayments table exists with correct structure""" log_message("Checking repayments table...") try: from django.db import connection with connection.cursor() as cursor: # Check if 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), INDEX idx_repayments_loan_id (loan_id), INDEX idx_repayments_receipt_number (receipt_number), INDEX idx_repayments_payment_date (payment_date) ) """) log_message("SUCCESS: Created repayments table") else: log_message("SUCCESS: Repayments table exists") return True except Exception as e: log_message(f"ERROR: Error with repayments table: {e}", 'ERROR') return False def main(): """Main execution""" log_message("HAVEN GRAZURI INVESTMENT LIMITED- Complete Receipts Fix") log_message("=" * 60) try: # Setup Django os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production') django.setup() log_message("Django setup completed") # Fix both tables repayments_ok = fix_repayments_table() receipts_ok = fix_receipts_table_complete() if repayments_ok and receipts_ok: log_message("\n" + "=" * 60) log_message("SUCCESS: COMPLETE FIX SUCCESSFUL!") log_message("All receipts table issues have been resolved.") log_message("Your loan repayment functionality should now work perfectly.") log_message("=" * 60) return True else: log_message("\n" + "=" * 60) log_message("ERROR: SOME FIXES FAILED!") log_message("Please check the error messages above.") log_message("=" * 60) return False except Exception as e: log_message(f"\nERROR: Critical error: {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)