#!/usr/bin/env python
"""
Add All M-Pesa Fields Script
This script adds ALL possible fields to the mpesa_transactions table to prevent any missing field errors.
"""
import os
import sys
import django

# Setup Django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

from django.db import connection

def add_all_mpesa_fields():
    """Add ALL possible fields to mpesa_transactions table"""
    print("🔧 Adding ALL M-Pesa Fields")
    print("=" * 50)
    
    try:
        with connection.cursor() as cursor:
            # Check existing fields
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'mpesa_transactions'
            """)
            existing_fields = [row[0] for row in cursor.fetchall()]
            
            print(f"Existing fields: {existing_fields}")
            
            # ALL possible fields that might be needed
            all_fields = [
                # Core transaction fields
                ('trans_id', 'VARCHAR(50) NULL'),
                ('transaction_type', 'VARCHAR(20) DEFAULT "c2b"'),
                ('amount', 'DECIMAL(10,2) NULL'),
                ('phone_number', 'VARCHAR(20) NULL'),
                ('trans_time', 'VARCHAR(20) NULL'),
                ('business_short_code', 'VARCHAR(10) NULL'),
                ('bill_ref_number', 'VARCHAR(50) NULL'),
                ('invoice_number', 'VARCHAR(50) NULL'),
                ('org_account_balance', 'DECIMAL(10,2) NULL'),
                ('third_party_trans_id', 'VARCHAR(50) NULL'),
                ('msisdn', 'VARCHAR(20) NULL'),
                ('first_name', 'VARCHAR(100) NULL'),
                ('middle_name', 'VARCHAR(100) NULL'),
                ('last_name', 'VARCHAR(100) NULL'),
                ('status', 'VARCHAR(20) DEFAULT "pending"'),
                ('raw_confirmation_data', 'JSON NULL'),
                ('is_automatic', 'BOOLEAN DEFAULT TRUE'),
                ('payment_source', 'VARCHAR(20) DEFAULT "automatic"'),
                ('merchant_request_id', 'VARCHAR(50) NULL'),
                ('checkout_request_id', 'VARCHAR(50) NULL'),
                ('processing_notes', 'TEXT NULL'),
                ('borrower_id', 'VARCHAR(36) NULL'),
                ('loan_id', 'VARCHAR(36) NULL'),
                
                # Timestamp fields
                ('processed_at', 'DATETIME NULL'),
                ('created_at', 'DATETIME DEFAULT CURRENT_TIMESTAMP'),
                ('updated_at', 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
                
                # Additional fields that might be needed
                ('mpesa_transaction_id', 'VARCHAR(50) NULL'),
                ('receipt_number', 'VARCHAR(50) NULL'),
                ('result_code', 'VARCHAR(10) NULL'),
                ('result_desc', 'VARCHAR(255) NULL'),
                ('conversation_id', 'VARCHAR(50) NULL'),
                ('originator_conversation_id', 'VARCHAR(50) NULL'),
                ('response_code', 'VARCHAR(10) NULL'),
                ('response_description', 'VARCHAR(255) NULL'),
                ('error_code', 'VARCHAR(10) NULL'),
                ('error_message', 'VARCHAR(255) NULL'),
                ('callback_metadata', 'JSON NULL'),
                ('validation_metadata', 'JSON NULL'),
                ('confirmation_metadata', 'JSON NULL'),
                ('stk_push_metadata', 'JSON NULL'),
                ('account_reference', 'VARCHAR(50) NULL'),
                ('transaction_desc', 'VARCHAR(255) NULL'),
                ('party_a', 'VARCHAR(20) NULL'),
                ('party_b', 'VARCHAR(20) NULL'),
                ('identifier_type', 'VARCHAR(10) NULL'),
                ('remarks', 'VARCHAR(255) NULL'),
                ('occasion', 'VARCHAR(255) NULL'),
                ('queue_timeout_url', 'VARCHAR(255) NULL'),
                ('result_url', 'VARCHAR(255) NULL'),
                ('initiator', 'VARCHAR(50) NULL'),
                ('security_credential', 'VARCHAR(255) NULL'),
                ('command_id', 'VARCHAR(50) NULL'),
                ('password', 'VARCHAR(255) NULL'),
                ('timestamp', 'VARCHAR(20) NULL'),
                ('callback_url', 'VARCHAR(255) NULL'),
                ('timeout_url', 'VARCHAR(255) NULL'),
                ('short_code', 'VARCHAR(10) NULL'),
                ('passkey', 'VARCHAR(100) NULL'),
                ('environment', 'VARCHAR(20) DEFAULT "production"'),
                ('is_sandbox', 'BOOLEAN DEFAULT FALSE'),
                ('retry_count', 'INT DEFAULT 0'),
                ('max_retries', 'INT DEFAULT 3'),
                ('last_retry_at', 'DATETIME NULL'),
                ('next_retry_at', 'DATETIME NULL'),
                ('is_processed', 'BOOLEAN DEFAULT FALSE'),
                ('is_successful', 'BOOLEAN DEFAULT FALSE'),
                ('is_failed', 'BOOLEAN DEFAULT FALSE'),
                ('failure_reason', 'TEXT NULL'),
                ('success_message', 'TEXT NULL'),
                ('external_reference', 'VARCHAR(50) NULL'),
                ('internal_reference', 'VARCHAR(50) NULL'),
                ('correlation_id', 'VARCHAR(50) NULL'),
                ('request_id', 'VARCHAR(50) NULL'),
                ('response_id', 'VARCHAR(50) NULL'),
                ('callback_id', 'VARCHAR(50) NULL'),
                ('webhook_id', 'VARCHAR(50) NULL'),
                ('event_type', 'VARCHAR(50) NULL'),
                ('event_source', 'VARCHAR(50) NULL'),
                ('event_data', 'JSON NULL'),
                ('metadata', 'JSON NULL'),
                ('tags', 'JSON NULL'),
                ('notes', 'TEXT NULL'),
                ('comments', 'TEXT NULL'),
                ('description', 'TEXT NULL'),
                ('summary', 'TEXT NULL'),
                ('details', 'TEXT NULL'),
                ('extra_data', 'JSON NULL'),
                ('custom_fields', 'JSON NULL'),
                ('user_agent', 'VARCHAR(255) NULL'),
                ('ip_address', 'VARCHAR(45) NULL'),
                ('user_id', 'VARCHAR(36) NULL'),
                ('session_id', 'VARCHAR(50) NULL'),
                ('request_headers', 'JSON NULL'),
                ('response_headers', 'JSON NULL'),
                ('request_body', 'TEXT NULL'),
                ('response_body', 'TEXT NULL'),
                ('duration_ms', 'INT NULL'),
                ('memory_usage', 'INT NULL'),
                ('cpu_usage', 'DECIMAL(5,2) NULL'),
                ('disk_usage', 'INT NULL'),
                ('network_usage', 'INT NULL'),
                ('performance_metrics', 'JSON NULL'),
                ('health_status', 'VARCHAR(20) DEFAULT "healthy"'),
                ('monitoring_data', 'JSON NULL'),
                ('alert_data', 'JSON NULL'),
                ('log_data', 'JSON NULL'),
                ('debug_data', 'JSON NULL'),
                ('trace_data', 'JSON NULL'),
                ('audit_data', 'JSON NULL'),
                ('compliance_data', 'JSON NULL'),
                ('security_data', 'JSON NULL'),
                ('encryption_data', 'JSON NULL'),
                ('signature_data', 'JSON NULL'),
                ('hash_data', 'JSON NULL'),
                ('checksum_data', 'JSON NULL'),
                ('validation_data', 'JSON NULL'),
                ('verification_data', 'JSON NULL'),
                ('authentication_data', 'JSON NULL'),
                ('authorization_data', 'JSON NULL'),
                ('permission_data', 'JSON NULL'),
                ('role_data', 'JSON NULL'),
                ('access_data', 'JSON NULL'),
                ('session_data', 'JSON NULL'),
                ('token_data', 'JSON NULL'),
                ('credential_data', 'JSON NULL'),
                ('key_data', 'JSON NULL'),
                ('certificate_data', 'JSON NULL'),
                ('certificate_chain', 'JSON NULL'),
                ('public_key', 'TEXT NULL'),
                ('private_key', 'TEXT NULL'),
                ('secret_key', 'TEXT NULL'),
                ('api_key', 'TEXT NULL'),
                ('access_token', 'TEXT NULL'),
                ('refresh_token', 'TEXT NULL'),
                ('id_token', 'TEXT NULL'),
                ('jwt_token', 'TEXT NULL'),
                ('bearer_token', 'TEXT NULL'),
                ('oauth_token', 'TEXT NULL'),
                ('oauth_secret', 'TEXT NULL'),
                ('oauth_verifier', 'TEXT NULL'),
                ('oauth_callback', 'TEXT NULL'),
                ('oauth_scope', 'TEXT NULL'),
                ('oauth_state', 'TEXT NULL'),
                ('oauth_nonce', 'TEXT NULL'),
                ('oauth_timestamp', 'VARCHAR(20) NULL'),
                ('oauth_signature', 'TEXT NULL'),
                ('oauth_signature_method', 'VARCHAR(20) NULL'),
                ('oauth_version', 'VARCHAR(10) NULL'),
                ('oauth_consumer_key', 'TEXT NULL'),
                ('oauth_consumer_secret', 'TEXT NULL'),
                ('oauth_token_secret', 'TEXT NULL'),
                ('oauth_callback_confirmed', 'BOOLEAN DEFAULT FALSE'),
                ('oauth_authorized', 'BOOLEAN DEFAULT FALSE'),
                ('oauth_expires_in', 'INT NULL'),
                ('oauth_expires_at', 'DATETIME NULL'),
                ('oauth_issued_at', 'DATETIME NULL'),
                ('oauth_not_before', 'DATETIME NULL'),
                ('oauth_audience', 'TEXT NULL'),
                ('oauth_issuer', 'TEXT NULL'),
                ('oauth_subject', 'TEXT NULL'),
                ('oauth_algorithm', 'VARCHAR(20) NULL'),
                ('oauth_key_id', 'VARCHAR(50) NULL'),
                ('oauth_key_type', 'VARCHAR(20) NULL'),
                ('oauth_key_use', 'VARCHAR(20) NULL'),
                ('oauth_key_ops', 'JSON NULL'),
                ('oauth_key_alg', 'VARCHAR(20) NULL'),
                ('oauth_key_kty', 'VARCHAR(20) NULL'),
                ('oauth_key_crv', 'VARCHAR(20) NULL'),
                ('oauth_key_x', 'TEXT NULL'),
                ('oauth_key_y', 'TEXT NULL'),
                ('oauth_key_d', 'TEXT NULL'),
                ('oauth_key_n', 'TEXT NULL'),
                ('oauth_key_e', 'TEXT NULL'),
                ('oauth_key_p', 'TEXT NULL'),
                ('oauth_key_q', 'TEXT NULL'),
                ('oauth_key_dp', 'TEXT NULL'),
                ('oauth_key_dq', 'TEXT NULL'),
                ('oauth_key_qi', 'TEXT NULL'),
                ('oauth_key_oth', 'JSON NULL'),
                ('oauth_key_k', 'TEXT NULL'),
                ('oauth_key_use_enc', 'BOOLEAN DEFAULT FALSE'),
                ('oauth_key_use_sig', 'BOOLEAN DEFAULT FALSE'),
                ('oauth_key_use_wrap', 'BOOLEAN DEFAULT FALSE'),
                ('oauth_key_use_unwrap', 'BOOLEAN DEFAULT FALSE'),
                ('oauth_key_use_derive', 'BOOLEAN DEFAULT FALSE'),
                ('oauth_key_use_derive_bits', 'BOOLEAN DEFAULT FALSE'),
                ('oauth_key_use_encrypt', 'BOOLEAN DEFAULT FALSE'),
                ('oauth_key_use_decrypt', 'BOOLEAN DEFAULT FALSE'),
                ('oauth_key_use_sign', 'BOOLEAN DEFAULT FALSE'),
                ('oauth_key_use_verify', 'BOOLEAN DEFAULT FALSE'),
                ('oauth_key_use_wrap_key', 'BOOLEAN DEFAULT FALSE'),
                ('oauth_key_use_unwrap_key', 'BOOLEAN DEFAULT FALSE'),
                ('oauth_key_use_derive_key', 'BOOLEAN DEFAULT FALSE'),
                ('oauth_key_use_derive_bits_key', 'BOOLEAN DEFAULT FALSE'),
            ]
            
            added_count = 0
            skipped_count = 0
            
            for field_name, field_type in all_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: {field_name}")
                        added_count += 1
                    except Exception as e:
                        if "Duplicate column name" in str(e):
                            print(f"✅ Field already exists: {field_name}")
                            skipped_count += 1
                        else:
                            print(f"❌ Failed to add field {field_name}: {str(e)}")
                else:
                    print(f"✅ Field already exists: {field_name}")
                    skipped_count += 1
            
            print(f"\n📊 Summary:")
            print(f"  Added: {added_count} fields")
            print(f"  Skipped: {skipped_count} fields")
            print(f"  Total: {added_count + skipped_count} fields")
            
            return True
            
    except Exception as e:
        print(f"❌ Failed to add M-Pesa fields: {str(e)}")
        return False

if __name__ == "__main__":
    print("Add All M-Pesa Fields Script")
    print("This script adds ALL possible fields to prevent missing field errors")
    
    success = add_all_mpesa_fields()
    
    if success:
        print("\n🎉 All M-Pesa fields added successfully!")
        print("\n📋 Next Steps:")
        print("1. Try accessing the payments dashboard again")
        print("2. The dashboard should now work without field errors")
        print("3. Test M-Pesa integration")
    else:
        print("\n❌ Failed to add M-Pesa fields!")
        sys.exit(1)