#!/usr/bin/env python
"""
Fix broken repayment relationships
Transactions that have repayment_id set but repayment doesn't exist in database
"""
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 fix_broken_relationships():
    """Find and fix transactions with broken repayment relationships"""
    from loans.models import MpesaTransaction, Repayment
    from django.db import connection
    
    print("\n" + "="*70)
    print("FIXING BROKEN REPAYMENT RELATIONSHIPS")
    print("="*70)
    
    # Get the actual table name from the model (safe - comes from model definition)
    mpesa_table = MpesaTransaction._meta.db_table
    
    # Use raw SQL to find all transactions with repayment_id set
    with connection.cursor() as cursor:
        cursor.execute(f"""
            SELECT id, repayment_id, trans_id, status 
            FROM `{mpesa_table}` 
            WHERE repayment_id IS NOT NULL
        """)
        rows = cursor.fetchall()
    
    print(f"\nFound {len(rows)} transactions with repayment_id set in database")
    
    broken_count = 0
    fixed_count = 0
    
    for row in rows:
        transaction_id, repayment_id, trans_id, status = row
        trans_id_str = trans_id or 'None'
        
        # Check if repayment exists
        if not Repayment.objects.filter(id=repayment_id).exists():
            print(f"\n  ✗ Broken relationship found:")
            print(f"    Transaction ID: {transaction_id}")
            print(f"    Transaction: {trans_id_str}")
            print(f"    Repayment ID: {repayment_id} (does not exist in database)")
            print(f"    Status: {status}")
            
            try:
                # Get the transaction object
                transaction = MpesaTransaction.objects.get(id=transaction_id)
                
                # Clear the broken relationship using raw SQL to be sure
                with connection.cursor() as cursor:
                    cursor.execute(f"""
                        UPDATE `{mpesa_table}` 
                        SET repayment_id = NULL 
                        WHERE id = %s
                    """, [transaction_id])
                
                # Update status and notes via ORM
                transaction.refresh_from_db()
                if transaction.status == 'processed':
                    transaction.status = 'failed'
                transaction.processing_notes = (transaction.processing_notes or "") + f" | Broken repayment relationship cleared (repayment {repayment_id} does not exist)"
                transaction.save()
                
                print(f"    ✓ Cleared broken relationship")
                broken_count += 1
                
                # Try to reprocess if borrower exists
                transaction.refresh_from_db()
                if transaction.borrower:
                    print(f"    → Attempting to recreate repayment...")
                    try:
                        repayment_new = transaction.create_repayment()
                        if repayment_new and Repayment.objects.filter(id=repayment_new.id).exists():
                            print(f"    ✓ Repayment recreated: {repayment_new.receipt_number}")
                            fixed_count += 1
                        else:
                            print(f"    ✗ Could not recreate repayment")
                            transaction.refresh_from_db()
                            print(f"      Status: {transaction.status}")
                            print(f"      Notes: {transaction.processing_notes}")
                    except Exception as e:
                        print(f"    ✗ Error recreating repayment: {e}")
                        import traceback
                        traceback.print_exc()
                else:
                    print(f"    ⚠ Cannot recreate - transaction has no borrower")
                    
            except MpesaTransaction.DoesNotExist:
                print(f"    ✗ Transaction object not found (ID: {transaction_id})")
            except Exception as e:
                print(f"    ✗ Error fixing transaction: {e}")
                import traceback
                traceback.print_exc()
    
    print(f"\n{'='*70}")
    print("SUMMARY")
    print("="*70)
    print(f"Broken relationships found and cleared: {broken_count}")
    print(f"Repayments successfully recreated: {fixed_count}")
    print("="*70)

if __name__ == '__main__':
    print("="*70)
    print("FIX BROKEN REPAYMENT RELATIONSHIPS")
    print("="*70)
    
    if not setup_django():
        sys.exit(1)
    
    fix_broken_relationships()
    
    print("\n" + "="*70)
    print("DONE")
    print("="*70)