#!/usr/bin/env python
"""
Fix All Database Fields Script
This script adds all missing fields to all M-Pesa related tables.
"""
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_repayment_fields():
    """Add missing fields to Repayment model"""
    print("🔄 Adding Repayment model fields...")
    
    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 = 'loans_repayment'
            """)
            existing_fields = [row[0] for row in cursor.fetchall()]
            
            # Add payment_source field if missing
            if 'payment_source' not in existing_fields:
                cursor.execute("ALTER TABLE loans_repayment ADD COLUMN payment_source VARCHAR(20) DEFAULT 'manual'")
                print("✅ Added payment_source field to Repayment model")
            else:
                print("✅ payment_source field already exists in Repayment model")
            
            return True
            
    except Exception as e:
        print(f"❌ Failed to add Repayment fields: {str(e)}")
        return False

def add_mpesa_transaction_fields():
    """Add missing fields to MpesaTransaction model"""
    print("🔄 Adding MpesaTransaction model fields...")
    
    try:
        with connection.cursor() as cursor:
            # Check if 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 - creating it...")
                # Create the table with all required fields
                cursor.execute("""
                    CREATE TABLE loans_mpesatransaction (
                        id VARCHAR(36) PRIMARY KEY,
                        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,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                    )
                """)
                print("✅ Created MpesaTransaction table")
                return True
            
            # 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()]
            
            # Add missing fields
            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'),
            ]
            
            for field_name, field_type in fields_to_add:
                if field_name not in existing_fields:
                    try:
                        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}")
            
            return True
            
    except Exception as e:
        print(f"❌ Failed to add MpesaTransaction fields: {str(e)}")
        return False

def add_mpesa_configuration_fields():
    """Add missing fields to MpesaConfiguration model"""
    print("🔄 Adding MpesaConfiguration model fields...")
    
    try:
        with connection.cursor() as cursor:
            # Check if table exists
            cursor.execute("""
                SELECT TABLE_NAME 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'payments_mpesaconfiguration'
            """)
            table_exists = cursor.fetchone()
            
            if not table_exists:
                print("❌ MpesaConfiguration table doesn't exist - creating it...")
                cursor.execute("""
                    CREATE TABLE payments_mpesaconfiguration (
                        id VARCHAR(36) PRIMARY KEY,
                        business_short_code VARCHAR(10) UNIQUE,
                        environment VARCHAR(20) DEFAULT 'production',
                        consumer_key VARCHAR(100) NULL,
                        consumer_secret VARCHAR(100) NULL,
                        passkey VARCHAR(100) NULL,
                        validation_url VARCHAR(255) NULL,
                        confirmation_url VARCHAR(255) NULL,
                        response_type VARCHAR(20) DEFAULT 'Completed',
                        is_active BOOLEAN DEFAULT TRUE,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                    )
                """)
                print("✅ Created MpesaConfiguration table")
                return True
            
            print("✅ MpesaConfiguration table exists")
            return True
            
    except Exception as e:
        print(f"❌ Failed to add MpesaConfiguration fields: {str(e)}")
        return False

def fix_all_database_fields():
    """Fix all database fields"""
    print("🔧 Fixing All Database Fields")
    print("=" * 50)
    
    # Fix Repayment model
    if not add_repayment_fields():
        return False
    
    # Fix MpesaTransaction model
    if not add_mpesa_transaction_fields():
        return False
    
    # Fix MpesaConfiguration model
    if not add_mpesa_configuration_fields():
        return False
    
    print("\n✅ All database fields fixed successfully!")
    return True

if __name__ == "__main__":
    print("Fix All Database Fields Script")
    print("This script adds all missing fields to M-Pesa related tables")
    
    success = fix_all_database_fields()
    
    if success:
        print("\n🎉 All database fields fixed!")
        print("\n📋 Next Steps:")
        print("1. Try accessing the payments dashboard again")
        print("2. Test M-Pesa integration")
        print("3. Check that all models are working")
    else:
        print("\n❌ Failed to fix database fields!")
        sys.exit(1)