#!/usr/bin/env python3 """ Complete fix for all receipts table issues This script fixes all missing columns in the receipts table """ 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)