#!/usr/bin/env python
"""
Add M-Pesa Transaction Fields Script
This script adds the missing fields to the MpesaTransaction model.
"""
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_mpesa_transaction_fields():
    """Add missing fields to MpesaTransaction model"""
    print("🔧 Adding M-Pesa Transaction Fields")
    print("=" * 40)
    
    try:
        with connection.cursor() as cursor:
            # Check if MpesaTransaction table exists
            cursor.execute("""
                SELECT TABLE_NAME 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'loans_mpesatransaction'
            """)
            table_exists = cursor.fetchone()
            
            if not table_exists:
                print("❌ MpesaTransaction table doesn't exist")
                return False
            
            print("✅ MpesaTransaction table exists")
            
            # Check existing fields
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'loans_mpesatransaction'
            """)
            existing_fields = [row[0] for row in cursor.fetchall()]
            print(f"Existing fields: {existing_fields}")
            
            # Define fields to add
            fields_to_add = [
                ('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'),
            ]
            
            # Add missing fields
            for field_name, field_type in fields_to_add:
                if field_name not in existing_fields:
                    try:
                        print(f"🔄 Adding field: {field_name}")
                        cursor.execute(f"ALTER TABLE loans_mpesatransaction ADD COLUMN {field_name} {field_type}")
                        print(f"✅ Added field: {field_name}")
                    except Exception as e:
                        if "Duplicate column name" in str(e):
                            print(f"✅ Field already exists: {field_name}")
                        else:
                            print(f"❌ Failed to add field {field_name}: {str(e)}")
                            return False
                else:
                    print(f"✅ Field already exists: {field_name}")
            
            # Verify all fields were added
            print("🔄 Verifying all fields...")
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'loans_mpesatransaction'
            """)
            final_fields = [row[0] for row in cursor.fetchall()]
            print(f"Final fields: {final_fields}")
            
            print("✅ All M-Pesa Transaction fields added successfully!")
            return True
                
    except Exception as e:
        print(f"❌ Failed to add M-Pesa Transaction fields: {str(e)}")
        return False

def test_mpesa_transaction_model():
    """Test MpesaTransaction model after adding fields"""
    print("\n🔄 Testing MpesaTransaction model...")
    try:
        from loans.models import MpesaTransaction
        
        # Try to create a test transaction
        test_transaction = MpesaTransaction.objects.create(
            trans_id='TEST123',
            transaction_type='c2b',
            amount=100.00,
            phone_number='254712345678',
            status='pending'
        )
        
        print("✅ MpesaTransaction model is working!")
        
        # Clean up test transaction
        test_transaction.delete()
        print("✅ Test transaction cleaned up")
        
        return True
        
    except Exception as e:
        print(f"❌ MpesaTransaction model test failed: {str(e)}")
        return False

if __name__ == "__main__":
    print("Add M-Pesa Transaction Fields Script")
    print("This script adds missing fields to the MpesaTransaction model")
    
    # Add fields
    success = add_mpesa_transaction_fields()
    
    if success:
        print("\n🎉 M-Pesa Transaction fields added successfully!")
        
        # Test model
        if test_mpesa_transaction_model():
            print("\n✅ M-Pesa Transaction model is now working!")
            print("\n📋 Next Steps:")
            print("1. Try accessing the payments dashboard again")
            print("2. Test M-Pesa integration")
        else:
            print("\n⚠️ Fields added but model still has issues")
    else:
        print("\n❌ Failed to add M-Pesa Transaction fields!")
        sys.exit(1)