#!/usr/bin/env python3 """ Add Missing Validation Fields Script This script adds the missing raw_validation_data field and other essential fields to the mpesa_transactions table in production. """ import os import sys import django # Setup Django environment os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings') django.setup() from django.db import connection def add_missing_validation_fields(): """Add missing validation and other essential fields to mpesa_transactions table""" print("Add Missing Validation Fields Script") print("=" * 50) print("Adding missing fields to mpesa_transactions table...") try: with connection.cursor() as cursor: # Check existing fields first cursor.execute("SHOW COLUMNS FROM mpesa_transactions") existing_fields = [row[0] for row in cursor.fetchall()] print(f"Existing fields: {len(existing_fields)}") # Essential missing fields that are commonly needed essential_fields = [ ('raw_validation_data', 'JSON NULL'), ('raw_callback_data', 'JSON NULL'), ('raw_stk_data', 'JSON NULL'), ('raw_query_data', 'JSON NULL'), ('raw_balance_data', 'JSON NULL'), ('raw_reversal_data', 'JSON NULL'), ('validation_result', 'VARCHAR(20) NULL'), ('callback_result', 'VARCHAR(20) NULL'), ('stk_result', 'VARCHAR(20) NULL'), ('query_result', 'VARCHAR(20) NULL'), ('balance_result', 'VARCHAR(20) NULL'), ('reversal_result', 'VARCHAR(20) NULL'), ('validation_status', 'VARCHAR(20) NULL'), ('callback_status', 'VARCHAR(20) NULL'), ('stk_status', 'VARCHAR(20) NULL'), ('query_status', 'VARCHAR(20) NULL'), ('balance_status', 'VARCHAR(20) NULL'), ('reversal_status', 'VARCHAR(20) NULL'), ('validation_message', 'TEXT NULL'), ('callback_message', 'TEXT NULL'), ('stk_message', 'TEXT NULL'), ('query_message', 'TEXT NULL'), ('balance_message', 'TEXT NULL'), ('reversal_message', 'TEXT NULL'), ('validation_code', 'VARCHAR(10) NULL'), ('callback_code', 'VARCHAR(10) NULL'), ('stk_code', 'VARCHAR(10) NULL'), ('query_code', 'VARCHAR(10) NULL'), ('balance_code', 'VARCHAR(10) NULL'), ('reversal_code', 'VARCHAR(10) NULL'), ('validation_time', 'DATETIME NULL'), ('callback_time', 'DATETIME NULL'), ('stk_time', 'DATETIME NULL'), ('query_time', 'DATETIME NULL'), ('balance_time', 'DATETIME NULL'), ('reversal_time', 'DATETIME NULL'), ('validation_attempts', 'INT DEFAULT 0'), ('callback_attempts', 'INT DEFAULT 0'), ('stk_attempts', 'INT DEFAULT 0'), ('query_attempts', 'INT DEFAULT 0'), ('balance_attempts', 'INT DEFAULT 0'), ('reversal_attempts', 'INT DEFAULT 0'), ('validation_success', 'BOOLEAN DEFAULT FALSE'), ('callback_success', 'BOOLEAN DEFAULT FALSE'), ('stk_success', 'BOOLEAN DEFAULT FALSE'), ('query_success', 'BOOLEAN DEFAULT FALSE'), ('balance_success', 'BOOLEAN DEFAULT FALSE'), ('reversal_success', 'BOOLEAN DEFAULT FALSE'), ('validation_failure', 'BOOLEAN DEFAULT FALSE'), ('callback_failure', 'BOOLEAN DEFAULT FALSE'), ('stk_failure', 'BOOLEAN DEFAULT FALSE'), ('query_failure', 'BOOLEAN DEFAULT FALSE'), ('balance_failure', 'BOOLEAN DEFAULT FALSE'), ('reversal_failure', 'BOOLEAN DEFAULT FALSE'), ('validation_error', 'TEXT NULL'), ('callback_error', 'TEXT NULL'), ('stk_error', 'TEXT NULL'), ('query_error', 'TEXT NULL'), ('balance_error', 'TEXT NULL'), ('reversal_error', 'TEXT NULL'), ('validation_response', 'TEXT NULL'), ('callback_response', 'TEXT NULL'), ('stk_response', 'TEXT NULL'), ('query_response', 'TEXT NULL'), ('balance_response', 'TEXT NULL'), ('reversal_response', 'TEXT NULL'), ('validation_headers', 'JSON NULL'), ('callback_headers', 'JSON NULL'), ('stk_headers', 'JSON NULL'), ('query_headers', 'JSON NULL'), ('balance_headers', 'JSON NULL'), ('reversal_headers', 'JSON NULL'), ('validation_payload', 'TEXT NULL'), ('callback_payload', 'TEXT NULL'), ('stk_payload', 'TEXT NULL'), ('query_payload', 'TEXT NULL'), ('balance_payload', 'TEXT NULL'), ('reversal_payload', 'TEXT NULL'), ('validation_url', 'VARCHAR(255) NULL'), ('stk_url', 'VARCHAR(255) NULL'), ('query_url', 'VARCHAR(255) NULL'), ('balance_url', 'VARCHAR(255) NULL'), ('reversal_url', 'VARCHAR(255) NULL'), ('validation_method', 'VARCHAR(10) NULL'), ('callback_method', 'VARCHAR(10) NULL'), ('stk_method', 'VARCHAR(10) NULL'), ('query_method', 'VARCHAR(10) NULL'), ('balance_method', 'VARCHAR(10) NULL'), ('reversal_method', 'VARCHAR(10) NULL'), ('validation_timeout', 'INT NULL'), ('callback_timeout', 'INT NULL'), ('stk_timeout', 'INT NULL'), ('query_timeout', 'INT NULL'), ('balance_timeout', 'INT NULL'), ('reversal_timeout', 'INT NULL'), ('validation_retry_backoff', 'BOOLEAN DEFAULT FALSE'), ('callback_retry_backoff', 'BOOLEAN DEFAULT FALSE'), ('stk_retry_backoff', 'BOOLEAN DEFAULT FALSE'), ] added_count = 0 skipped_count = 0 error_count = 0 for field_name, field_type in essential_fields: if field_name not in existing_fields: try: cursor.execute(f"ALTER TABLE mpesa_transactions ADD COLUMN {field_name} {field_type}") print(f"Added {field_name}") added_count += 1 except Exception as e: if 'Row size too large' in str(e): print(f"Skipped {field_name} - Row size limit reached") error_count += 1 else: print(f"Error adding {field_name}: {e}") error_count += 1 else: print(f"Field already exists: {field_name}") skipped_count += 1 connection.commit() print("\n" + "=" * 50) print("SUMMARY:") print(f"Added: {added_count} fields") print(f"Skipped: {skipped_count} fields") print(f"Errors: {error_count} fields") print(f"Total processed: {len(essential_fields)} fields") if added_count > 0: print("\nSUCCESS: Missing fields added successfully!") print("The payments dashboard should now work without field errors.") else: print("\nINFO: No new fields were added (all already exist or hit limits).") return True except Exception as e: print(f"ERROR: Failed to add missing fields: {e}") return False if __name__ == "__main__": print("Starting missing fields addition...") success = add_missing_validation_fields() if success: print("\nScript completed successfully!") print("You can now try accessing the payments dashboard.") else: print("\nScript failed!") sys.exit(1)