"""
Diagnostic: Check why FAITH's payment (UF1O16E915) isn't showing on repayments page.
Run on the server: python manage.py shell < diagnose_faith_repayment.py
Or: python diagnose_faith_repayment.py
"""
import django
import os
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

from django.db import connection
from django.db.models import Q
from loans.models import Repayment, Loan
from users.models import CustomUser
import json

c = connection.cursor()

print("=" * 70)
print("DIAGNOSTIC: FAITH Payment UF1O16E915")
print("=" * 70)

# Step 1: Find the IPN log
print("\n1. IPN LOG FOR UF1O16E915")
print("-" * 70)
c.execute("""
    SELECT raw_data, created_at 
    FROM sasapay_ipn_logs 
    WHERE raw_data LIKE '%UF1O16E915%'
    ORDER BY created_at DESC 
    LIMIT 5
""")
rows = c.fetchall()
if rows:
    for raw, created in rows:
        d = json.loads(raw) if isinstance(raw, str) else raw
        print(f"  Found: [{created}]")
        print(f"    TransID: {d.get('TransID')}")
        print(f"    BillRef: {d.get('BillRefNumber')}")
        print(f"    Amount: {d.get('TransAmount')}")
        print(f"    Name: {d.get('FirstName')} {d.get('MiddleName')} {d.get('LastName')}")
else:
    print("  NOT FOUND — payment never hit /payments/sasapay/ipn/")
    print("  This means SasaPay is sending to a different URL or old PHP system")

# Step 2: Find the repayment
print("\n2. REPAYMENT RECORD FOR UF1O16E915")
print("-" * 70)
c.execute("""
    SELECT r.id, r.receipt_number, r.amount, r.payment_date, r.mpesa_transaction_id,
           l.loan_number, l.borrower_id,
           u.first_name, u.last_name, u.phone_number, u.branch_id
    FROM repayments r
    LEFT JOIN loans l ON l.id = r.loan_id
    LEFT JOIN users u ON u.id = l.borrower_id
    WHERE r.mpesa_transaction_id = 'UF1O16E915'
""")
cols = [d[0] for d in c.description]
rows = c.fetchall()
if rows:
    for r in rows:
        row_dict = dict(zip(cols, r))
        print(f"  Repayment found:")
        print(f"    Receipt: {row_dict['receipt_number']}")
        print(f"    Amount: {row_dict['amount']}")
        print(f"    Loan: {row_dict['loan_number']}")
        print(f"    Borrower: {row_dict['first_name']} {row_dict['last_name']} (ID: {row_dict['borrower_id']})")
        print(f"    Phone: {row_dict['phone_number']}")
        print(f"    Branch ID: {row_dict['branch_id']} {'(NULL — no branch assigned!)' if row_dict['branch_id'] is None else ''}")
        
        # Check if this would be visible via ORM
        print(f"\n  ORM visibility check:")
        try:
            rep = Repayment.objects.get(mpesa_transaction_id='UF1O16E915')
            print(f"    ✓ ORM can read repayment {rep.receipt_number}")
        except Repayment.DoesNotExist:
            print(f"    ✗ ORM CANNOT read repayment (row exists but joins fail)")
        
        # Check with select_related like the repayments view
        try:
            rep_with_joins = Repayment.objects.select_related(
                'loan', 'loan__borrower', 'loan__application__loan_product'
            ).get(mpesa_transaction_id='UF1O16E915')
            print(f"    ✓ ORM with select_related (like repayments view) can read it")
        except Repayment.DoesNotExist:
            print(f"    ✗ ORM with select_related FAILS — loan.application is NULL or missing")
        
        # Check branch filter
        borrower_branch = row_dict['branch_id']
        if borrower_branch is None:
            print(f"\n  ⚠ BRANCH FILTER ISSUE:")
            print(f"    Borrower has no branch assigned (branch_id is NULL)")
            print(f"    BEFORE FIX: Would be hidden when any branch filter is active")
            print(f"    AFTER FIX: Will be visible (Q filter includes branch__isnull=True)")
else:
    print("  NOT FOUND — repayment was not created")

# Step 3: Check loan and application link
print("\n3. LOAN & APPLICATION CHECK")
print("-" * 70)
c.execute("""
    SELECT l.loan_number, l.borrower_id, l.application_id, la.id as app_exists
    FROM loans l
    LEFT JOIN loan_applications la ON la.id = l.application_id
    WHERE l.id IN (
        SELECT loan_id FROM repayments WHERE mpesa_transaction_id = 'UF1O16E915'
    )
""")
cols = [d[0] for d in c.description]
rows = c.fetchall()
if rows:
    for r in rows:
        row_dict = dict(zip(cols, r))
        print(f"  Loan: {row_dict['loan_number']}")
        print(f"  Application ID: {row_dict['application_id']}")
        if row_dict['app_exists'] is None:
            print(f"    ✗ CRITICAL: loan.application_id points to non-existent application")
            print(f"       This causes select_related to drop the row via INNER JOIN")
        else:
            print(f"    ✓ Application exists")
else:
    print("  No loan found for this repayment")

# Step 4: Check MpesaTransaction record (alternative path)
print("\n4. MPESA TRANSACTION RECORD (alternative processing path)")
print("-" * 70)
c.execute("""
    SELECT id, trans_id, amount, status, bill_ref_number, 
           borrower_id, loan_id, processing_notes
    FROM mpesa_transactions
    WHERE trans_id = 'UF1O16E915' OR mpesa_transaction_id = 'UF1O16E915'
""")
cols = [d[0] for d in c.description]
rows = c.fetchall()
if rows:
    for r in rows:
        row_dict = dict(zip(cols, r))
        print(f"  MpesaTransaction found:")
        print(f"    Status: {row_dict['status']}")
        print(f"    Bill Ref: {row_dict['bill_ref_number']}")
        print(f"    Borrower ID: {row_dict['borrower_id']}")
        print(f"    Loan ID: {row_dict['loan_id']}")
        print(f"    Notes: {row_dict['processing_notes']}")
else:
    print("  No MpesaTransaction record — used sasapay_service.py direct path")

print("\n" + "=" * 70)
print("SUMMARY")
print("=" * 70)
print("""
If repayment exists but isn't visible on /loans/repayments/:
  1. Check if borrower.branch is NULL → fixed by Q filter (branch__isnull=True)
  2. Check if loan.application_id points to missing application → causes select_related to drop row
  3. Check if user viewing has portfolio_manager filter blocking it
  4. Check if selected_branch_id session filter is active

If repayment doesn't exist:
  1. IPN never arrived (SasaPay sends to old PHP URL)
  2. IPN arrived but borrower matching failed (phone format mismatch)
  3. Loan matching failed (no active loan for borrower)
""")