#!/usr/bin/env python
"""
Investigate why payments aren't being matched to borrowers or added to repayments database
This script will trace through the entire payment processing flow and identify where it fails
"""
import os
import sys
import django
from pathlib import Path

def setup_django():
    """Setup Django environment"""
    script_dir = Path(__file__).resolve().parent
    
    manage_py = script_dir / 'manage.py'
    if not manage_py.exists():
        manage_py = script_dir.parent / 'manage.py'
    
    if manage_py.exists():
        with open(manage_py, 'r') as f:
            content = f.read()
            if 'DJANGO_SETTINGS_MODULE' in content:
                import re
                match = re.search(r"os\.environ\.setdefault\(['\"]DJANGO_SETTINGS_MODULE['\"],\s*['\"]([^'\"]+)['\"]", content)
                if match:
                    os.environ.setdefault('DJANGO_SETTINGS_MODULE', match.group(1))
    
    settings_modules = [
        'config.settings',
        'settings',
        'project.settings',
        'branchsystem.settings',
        'branch_system.settings',
    ]
    
    for settings_module in settings_modules:
        try:
            os.environ.setdefault('DJANGO_SETTINGS_MODULE', settings_module)
            django.setup()
            print(f"✓ Django setup successful with settings: {settings_module}")
            return True
        except Exception as e:
            continue
    
    print("✗ Error: Could not setup Django. Please set DJANGO_SETTINGS_MODULE")
    return False

def normalize_phone(phone):
    """Normalize phone for comparison"""
    if not phone:
        return None
    clean = ''.join(filter(str.isdigit, str(phone)))
    if clean.startswith('254'):
        return '+' + clean
    elif clean.startswith('0'):
        return '+254' + clean[1:]
    return phone

def investigate_transaction(trans_id):
    """Investigate a specific transaction"""
    from loans.models import MpesaTransaction, Repayment, Loan
    from payments.models import MpesaCallback
    from django.contrib.auth import get_user_model
    
    User = get_user_model()
    
    print("\n" + "="*80)
    print(f"INVESTIGATING TRANSACTION: {trans_id}")
    print("="*80)
    
    # STEP 1: Check if callback exists
    print("\n[STEP 1] Checking if callback was received...")
    callbacks = MpesaCallback.objects.filter(callback_type='confirmation')
    callback = None
    for cb in callbacks:
        if isinstance(cb.raw_data, dict) and cb.raw_data.get('TransID') == trans_id:
            callback = cb
            break
    
    if not callback:
        print("  ✗ CRITICAL: Callback not found in database!")
        print("    This means the callback was never received or stored.")
        return
    
    print(f"  ✓ Callback found: {callback.id}")
    print(f"    Processed: {callback.processed}")
    print(f"    Created: {callback.created_at}")
    
    raw_data = callback.raw_data
    bill_ref = raw_data.get('BillRefNumber')
    amount = raw_data.get('TransAmount')
    phone = raw_data.get('MSISDN', '')
    
    print(f"    Bill Ref (ID Number): {bill_ref}")
    print(f"    Amount: {amount}")
    print(f"    Phone: {phone}")
    
    # STEP 2: Check if transaction exists
    print("\n[STEP 2] Checking if transaction was created...")
    transaction = MpesaTransaction.objects.filter(trans_id=trans_id).first()
    
    if not transaction:
        print("  ✗ CRITICAL: Transaction not created!")
        print("    This means process_confirmation_callback() failed to create the transaction.")
        print("    Possible causes:")
        print("      - get_or_create() failed silently")
        print("      - Database error during transaction creation")
        print("      - Exception caught and ignored")
        return
    
    print(f"  ✓ Transaction found: {transaction.id}")
    print(f"    Status: {transaction.status}")
    print(f"    Bill Ref: {transaction.bill_ref_number}")
    print(f"    Phone: {transaction.msisdn or transaction.phone_number}")
    print(f"    Amount: {transaction.amount}")
    
    # STEP 3: Check borrower matching
    print("\n[STEP 3] Investigating borrower matching...")
    
    if not bill_ref:
        print("  ✗ CRITICAL: No Bill Ref (ID Number) in callback data!")
        print("    Cannot match borrower without ID number.")
        return
    
    print(f"  Looking for borrower with ID number: {bill_ref}")
    
    # Try to find borrower by ID
    borrowers_by_id = User.objects.filter(id_number=bill_ref, role='borrower')
    if borrowers_by_id.exists():
        borrower = borrowers_by_id.first()
        print(f"  ✓ Borrower found by ID: {borrower.get_full_name()} ({borrower.email})")
        print(f"    Phone in system: {borrower.phone_number}")
        print(f"    Phone from payment: {phone}")
        
        # Check if phone matches
        borrower_phone_normalized = normalize_phone(borrower.phone_number)
        payment_phone_normalized = normalize_phone(phone)
        
        if borrower_phone_normalized == payment_phone_normalized:
            print(f"    ✓ Phone numbers match!")
        else:
            print(f"    ⚠ Phone numbers don't match:")
            print(f"      Borrower: {borrower.phone_number} (normalized: {borrower_phone_normalized})")
            print(f"      Payment: {phone} (normalized: {payment_phone_normalized})")
        
        # Check if transaction is linked to borrower
        if transaction.borrower == borrower:
            print(f"  ✓ Transaction is linked to borrower")
        else:
            print(f"  ✗ PROBLEM: Transaction borrower is {transaction.borrower or 'None'}, but borrower with ID {bill_ref} exists!")
            print(f"    This means match_borrower() is not working correctly.")
            print(f"    Expected: {borrower.get_full_name()}")
            print(f"    Actual: {transaction.borrower.get_full_name() if transaction.borrower else 'None'}")
    else:
        print(f"  ✗ PROBLEM: No borrower found with ID number: {bill_ref}")
        print(f"    This is why the payment can't be matched!")
        
        # Try to find by phone
        if phone:
            phone_normalized = normalize_phone(phone)
            print(f"\n  Trying to find borrower by phone: {phone} (normalized: {phone_normalized})")
            
            # Try different phone variants
            phone_variants = [phone, phone_normalized]
            if phone_normalized and phone_normalized.startswith('+254'):
                phone_variants.extend([
                    phone_normalized[1:],  # Remove +
                    '0' + phone_normalized[4:],  # Local format
                ])
            elif phone_normalized and phone_normalized.startswith('254'):
                phone_variants.extend([
                    '+' + phone_normalized,
                    '0' + phone_normalized[3:],
                ])
            
            found_by_phone = False
            for variant in phone_variants:
                borrowers = User.objects.filter(phone_number=variant, role='borrower')
                if borrowers.exists():
                    borrower = borrowers.first()
                    print(f"    ✓ Found borrower by phone ({variant}): {borrower.get_full_name()}")
                    print(f"      ID Number: {borrower.id_number}")
                    found_by_phone = True
                    break
            
            if not found_by_phone:
                print(f"    ✗ No borrower found with any phone variant")
    
    # STEP 4: Check if borrower is linked to transaction
    print("\n[STEP 4] Checking if transaction has borrower...")
    if transaction.borrower:
        print(f"  ✓ Transaction has borrower: {transaction.borrower.get_full_name()}")
        print(f"    Borrower ID: {transaction.borrower.id_number}")
        print(f"    Borrower Phone: {transaction.borrower.phone_number}")
    else:
        print(f"  ✗ PROBLEM: Transaction has no borrower!")
        print(f"    This means match_borrower() returned None or failed.")
        print(f"    Processing notes: {transaction.processing_notes or 'None'}")
        
        # Try to manually match
        print(f"\n  → Attempting manual match...")
        try:
            borrower = transaction.match_borrower()
            transaction.refresh_from_db()
            
            if borrower:
                print(f"    ✓ match_borrower() returned: {borrower.get_full_name()}")
                print(f"    Transaction borrower after match: {transaction.borrower.get_full_name() if transaction.borrower else 'None'}")
            else:
                print(f"    ✗ match_borrower() returned None")
                print(f"    Processing notes: {transaction.processing_notes}")
        except Exception as e:
            print(f"    ✗ Error in match_borrower(): {e}")
            import traceback
            traceback.print_exc()
    
    # STEP 5: Check for active loans
    print("\n[STEP 5] Checking for active loans...")
    if transaction.borrower:
        active_loans = Loan.active_objects.filter(
            borrower=transaction.borrower,
            status='active'
        )
        
        if active_loans.exists():
            print(f"  ✓ Found {active_loans.count()} active loan(s):")
            for loan in active_loans:
                print(f"    - {loan.loan_number}: KES {loan.total_amount:,.2f} (Outstanding: KES {loan.outstanding_amount:,.2f})")
        else:
            print(f"  ✗ PROBLEM: No active loans found for borrower!")
            print(f"    This is why repayments can't be created!")
            print(f"    Borrower: {transaction.borrower.get_full_name()}")
    else:
        print(f"  ⚠ Cannot check loans - transaction has no borrower")
    
    # STEP 6: Check if repayment exists
    print("\n[STEP 6] Checking if repayment exists in database...")
    try:
        repayment = transaction.repayment
        if repayment:
            print(f"  ✓ Repayment exists: {repayment.id}")
            print(f"    Receipt: {repayment.receipt_number}")
            print(f"    Amount: {repayment.amount}")
            print(f"    Loan: {repayment.loan.loan_number}")
            print(f"    Payment Date: {repayment.payment_date}")
            
            # Verify repayment is in database
            repayment_exists = Repayment.objects.filter(id=repayment.id).exists()
            if repayment_exists:
                print(f"    ✓ Repayment confirmed in database")
            else:
                print(f"    ✗ CRITICAL: Repayment object exists but not in database!")
        else:
            print(f"  ✗ PROBLEM: Transaction has no repayment!")
            print(f"    Transaction status: {transaction.status}")
            print(f"    Processing notes: {transaction.processing_notes or 'None'}")
            
            # Check if repayments exist for this borrower/loan
            if transaction.borrower and transaction.loan:
                repayments = Repayment.objects.filter(
                    loan=transaction.loan,
                    mpesa_transaction_id=trans_id
                )
                if repayments.exists():
                    print(f"    ⚠ Found {repayments.count()} repayment(s) with this transaction ID but not linked to transaction!")
                    for rp in repayments:
                        print(f"      - {rp.receipt_number}: KES {rp.amount}")
    except Exception as e:
        print(f"  ✗ CRITICAL: Error accessing repayment: {e}")
        print(f"    This suggests the repayment relationship is broken!")
        import traceback
        traceback.print_exc()
    
    # STEP 7: Check if repayment creation would work
    print("\n[STEP 7] Testing repayment creation...")
    if transaction.borrower:
        active_loans = Loan.active_objects.filter(
            borrower=transaction.borrower,
            status='active'
        ).order_by('due_date')
        
        if active_loans.exists():
            print(f"  → Testing create_repayment()...")
            try:
                # Save original repayment link
                original_repayment = transaction.repayment
                
                # Temporarily clear repayment to test creation
                if original_repayment:
                    transaction.repayment = None
                    transaction.save()
                
                repayment = transaction.create_repayment()
                transaction.refresh_from_db()
                
                if repayment:
                    print(f"    ✓ create_repayment() succeeded!")
                    print(f"      Repayment: {repayment.receipt_number}")
                    print(f"      Amount: {repayment.amount}")
                    print(f"      Loan: {repayment.loan.loan_number}")
                    
                    # Verify in database
                    if Repayment.objects.filter(id=repayment.id).exists():
                        print(f"      ✓ Repayment confirmed in database")
                    else:
                        print(f"      ✗ CRITICAL: Repayment created but not in database!")
                else:
                    print(f"    ✗ create_repayment() returned None")
                    print(f"      Status: {transaction.status}")
                    print(f"      Notes: {transaction.processing_notes}")
            except Exception as e:
                print(f"    ✗ Error in create_repayment(): {e}")
                import traceback
                traceback.print_exc()
        else:
            print(f"  ⚠ Cannot test - no active loans")
    else:
        print(f"  ⚠ Cannot test - no borrower")
    
    # SUMMARY
    print("\n" + "="*80)
    print("DIAGNOSIS SUMMARY")
    print("="*80)
    
    issues = []
    
    if not transaction:
        issues.append("✗ Transaction was never created")
    elif not transaction.borrower:
        issues.append("✗ Borrower not matched - match_borrower() failed")
    elif not Loan.active_objects.filter(borrower=transaction.borrower, status='active').exists():
        issues.append("✗ No active loans for borrower")
    else:
        try:
            if not transaction.repayment:
                issues.append("✗ Repayment not created - create_repayment() failed")
            elif not Repayment.objects.filter(id=transaction.repayment.id).exists():
                issues.append("✗ Repayment created but not in database")
        except:
            issues.append("✗ Repayment relationship broken")
    
    if issues:
        print("\nROOT CAUSES IDENTIFIED:")
        for issue in issues:
            print(f"  {issue}")
    else:
        print("\n✓ No issues found - payment should be processed correctly")
    
    print("="*80)

def investigate_all_pending():
    """Investigate all pending transactions"""
    from loans.models import MpesaTransaction
    from payments.models import MpesaCallback
    
    print("\n" + "="*80)
    print("INVESTIGATING ALL PENDING TRANSACTIONS")
    print("="*80)
    
    # Find all pending callbacks
    pending_callbacks = MpesaCallback.objects.filter(
        callback_type='confirmation',
        processed=False
    ).order_by('-created_at')[:10]
    
    print(f"\nFound {pending_callbacks.count()} pending callbacks")
    
    for callback in pending_callbacks:
        raw_data = callback.raw_data
        if isinstance(raw_data, dict):
            trans_id = raw_data.get('TransID')
            if trans_id:
                investigate_transaction(trans_id)
                print("\n")

if __name__ == '__main__':
    print("="*80)
    print("PAYMENT PROCESSING INVESTIGATION TOOL")
    print("="*80)
    
    if not setup_django():
        sys.exit(1)
    
    # Check specific transaction
    import sys
    if len(sys.argv) > 1:
        trans_id = sys.argv[1]
        investigate_transaction(trans_id)
    else:
        # Check latest pending
        investigate_all_pending()