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