"""
COMPREHENSIVE SASAPAY PAYMENT DEBUG SCRIPT
==========================================
Collects the last 20 IPN logs that SUCCEEDED (have a repayment) and the last 20
that FAILED (IPN arrived but no repayment was created), then runs every possible
diagnostic check on each one so we can find the exact pattern separating success
from failure.

Run on production:
    python debug_sasapay_payments.py

100% READ-ONLY — no writes, no changes.
"""

import django
import os
import json
import sys
from datetime import datetime

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
django.setup()

from django.db import connection

W  = 80
S  = "=" * W
S2 = "-" * W
S3 = "·" * W

def hdr(title, level=1):
    sep = S if level == 1 else S2
    print(f"\n{sep}")
    print(f"  {title}")
    print(sep)

def row(label, value, flag=""):
    flag_str = f"  ◄ {flag}" if flag else ""
    print(f"  {label:<35} {value}{flag_str}")

def ipn_phone_variants(phone):
    """All DB-matchable variants of a phone number."""
    phone = str(phone or '').strip()
    if not phone or len(phone) > 30:  # hashes are 64 chars — skip
        return []
    variants = [phone]
    if phone.startswith('0') and len(phone) >= 9:
        variants += ['+254' + phone[1:], '254' + phone[1:]]
    elif phone.startswith('+254'):
        variants += [phone[1:], '0' + phone[4:]]
    elif phone.startswith('254') and len(phone) >= 12:
        variants += ['+' + phone, '0' + phone[3:]]
    return list(dict.fromkeys(variants))

def is_hash(s):
    """Return True if the string looks like a SHA-256 hash (not a phone)."""
    s = str(s or '')
    return len(s) == 64 and all(c in '0123456789abcdef' for c in s.lower())

def replay_matching(c, ipn_data):
    """
    Simulate steps 1-3 of process_ipn_callback() against live DB data.
    Returns a dict with every intermediate result.
    """
    result = {
        'bill_ref':       ipn_data.get('BillRefNumber', ''),
        'msisdn':         ipn_data.get('MSISDN', ''),
        'amount':         ipn_data.get('TransAmount', '0'),
        'bill_ref_is_hash':  is_hash(ipn_data.get('BillRefNumber', '')),
        'msisdn_is_hash': is_hash(ipn_data.get('MSISDN', '')),
        'bill_ref_variants':  [],
        'msisdn_variants':    [],
        # step results
        'step1a_match':   None,   # borrower matched via BillRef as phone
        'step1a_variant': None,
        'step1b_match':   None,   # borrower matched via BillRef as loan number
        'step1c_match':   None,   # borrower matched via MSISDN
        'step1c_variant': None,
        'borrower_id':    None,
        'borrower_name':  None,
        'borrower_active':None,
        'borrower_role':  None,
        'match_method':   None,
        'step2_loan':     None,   # active loan found
        'step2_loan_status': None,
        'step2_loan_deleted': None,
        'step3_outstanding': None,
        'step3_ok':       None,
        'drop_reason':    None,   # why it failed (None = should succeed)
    }

    bill_ref = result['bill_ref']
    msisdn   = result['msisdn']

    # phone variants
    bill_variants  = ipn_phone_variants(bill_ref)
    msisdn_variants = ipn_phone_variants(msisdn)
    result['bill_ref_variants']  = bill_variants
    result['msisdn_variants']    = msisdn_variants

    # ── Step 1a: BillRef as phone ─────────────────────────────────────
    for variant in bill_variants:
        c.execute("""
            SELECT id, CONCAT(first_name,' ',last_name), role, is_active, status
            FROM users WHERE phone_number=%s AND role='borrower'
        """, [variant])
        rows = c.fetchall()
        if rows:
            uid, name, role, active, status = rows[0]
            result['step1a_match']   = True
            result['step1a_variant'] = variant
            result['borrower_id']    = str(uid)
            result['borrower_name']  = name.strip()
            result['borrower_active'] = bool(active)
            result['borrower_role']   = role
            result['match_method']    = f"BillRef phone '{variant}'"
            if not active:
                result['drop_reason'] = f"borrower is_active=False (matched via '{variant}')"
            if role != 'borrower':
                result['drop_reason'] = f"role={role!r} not 'borrower'"
            break

    # ── Step 1b: BillRef as loan number ───────────────────────────────
    if not result['step1a_match']:
        c.execute("""
            SELECT l.id, l.status, l.is_deleted, u.id, CONCAT(u.first_name,' ',u.last_name),
                   u.role, u.is_active
            FROM loans l
            JOIN users u ON u.id = l.borrower_id
            WHERE l.loan_number=%s
        """, [bill_ref])
        rows = c.fetchall()
        if rows:
            lid, lstatus, ldel, uid, name, role, active = rows[0]
            result['step1b_match']  = True
            result['borrower_id']   = str(uid)
            result['borrower_name'] = name.strip()
            result['borrower_active'] = bool(active)
            result['borrower_role']   = role
            result['match_method']    = f"loan# '{bill_ref}'"
            if lstatus != 'active':
                result['drop_reason'] = f"loan found by ref but status={lstatus!r}"
            if ldel:
                result['drop_reason'] = f"loan found by ref but is_deleted=True"
            if not active:
                result['drop_reason'] = f"borrower is_active=False (loan# match)"

    # ── Step 1c: MSISDN as phone ──────────────────────────────────────
    if not result['step1a_match'] and not result['step1b_match']:
        if result['msisdn_is_hash']:
            result['drop_reason'] = result['drop_reason'] or "MSISDN is a hash — cannot match by paying phone"
        else:
            for variant in msisdn_variants:
                c.execute("""
                    SELECT id, CONCAT(first_name,' ',last_name), role, is_active
                    FROM users WHERE phone_number=%s AND role='borrower'
                """, [variant])
                rows = c.fetchall()
                if rows:
                    uid, name, role, active = rows[0]
                    result['step1c_match']   = True
                    result['step1c_variant'] = variant
                    result['borrower_id']    = str(uid)
                    result['borrower_name']  = name.strip()
                    result['borrower_active'] = bool(active)
                    result['borrower_role']   = role
                    result['match_method']    = f"MSISDN '{variant}'"
                    break

    # ── No borrower ───────────────────────────────────────────────────
    any_match = result['step1a_match'] or result['step1b_match'] or result['step1c_match']
    if not any_match:
        result['drop_reason'] = result['drop_reason'] or "no borrower matched (all 3 strategies failed)"
        return result

    # ── Step 2: active loan ───────────────────────────────────────────
    if result['borrower_id']:
        c.execute("""
            SELECT loan_number, status, is_deleted,
                   principal_amount, amount_paid, due_date
            FROM loans
            WHERE borrower_id=%s AND status='active' AND is_deleted=0
            ORDER BY id DESC LIMIT 1
        """, [result['borrower_id']])
        loan_row = c.fetchone()
        if loan_row:
            ln, lstatus, ldel, principal, paid, due = loan_row
            result['step2_loan']        = ln
            result['step2_loan_status'] = lstatus
            result['step2_loan_deleted']= bool(ldel)
            outstanding = float(principal or 0) - float(paid or 0)
            result['step3_outstanding'] = outstanding
            result['step3_ok']          = outstanding > 0
            if outstanding <= 0:
                result['drop_reason'] = f"outstanding={outstanding:.2f} (fully paid or overpaid)"
        else:
            # Check if they have any loan at all
            c.execute("""
                SELECT loan_number, status, is_deleted FROM loans
                WHERE borrower_id=%s ORDER BY id DESC LIMIT 1
            """, [result['borrower_id']])
            any_loan = c.fetchone()
            if any_loan:
                result['drop_reason'] = (
                    f"no ACTIVE loan — last loan {any_loan[0]} "
                    f"status={any_loan[1]} is_deleted={any_loan[2]}"
                )
            else:
                result['drop_reason'] = "borrower has no loans at all"

    return result


# ═══════════════════════════════════════════════════════════════════════
#  COLLECT DATA
# ═══════════════════════════════════════════════════════════════════════

c = connection.cursor()

print(S)
print("  SASAPAY PAYMENT COMPREHENSIVE DEBUG REPORT")
print(f"  Generated: {datetime.now():%Y-%m-%d %H:%M:%S}")
print(S)

# ── Total counts ──────────────────────────────────────────────────────
c.execute("SELECT COUNT(*) FROM sasapay_ipn_logs")
total_ipns = c.fetchone()[0]

c.execute("SELECT COUNT(*) FROM repayments WHERE payment_source='automatic'")
total_auto = c.fetchone()[0]

c.execute("""
    SELECT COUNT(DISTINCT JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.TransID')))
    FROM sasapay_ipn_logs
    WHERE NOT EXISTS (
        SELECT 1 FROM repayments r
        WHERE r.mpesa_transaction_id =
              JSON_UNQUOTE(JSON_EXTRACT(sasapay_ipn_logs.raw_data,'$.TransID'))
    )
    AND JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.TransID')) != ''
    AND JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.TransID')) IS NOT NULL
""")
total_gaps = c.fetchone()[0]

c.execute("""
    SELECT COUNT(*) FROM repayments r
    LEFT JOIN receipts rc ON rc.repayment_id = r.id
    WHERE r.payment_source='automatic' AND rc.id IS NULL
""")
missing_receipts = c.fetchone()[0]

hdr("OVERVIEW")
row("Total IPN logs received",      total_ipns)
row("Total auto repayments created", total_auto)
row("IPN gaps (arrived, no repayment)", total_gaps,
    "⚠️ PAYMENTS LOST" if total_gaps > 0 else "")
row("Auto repayments missing receipt",  missing_receipts,
    "⚠️ UI shows Missing" if missing_receipts > 0 else "")


# ── Last 20 SUCCESSFUL auto repayments ───────────────────────────────
c.execute("""
    SELECT
        r.mpesa_transaction_id,
        r.receipt_number,
        r.amount,
        r.payment_date,
        l.loan_number,
        u.phone_number,
        CONCAT(u.first_name,' ',u.last_name) AS borrower,
        r.mpesa_phone_number,
        CASE WHEN rc.id IS NOT NULL THEN 'YES' ELSE 'NO' END AS has_receipt
    FROM repayments r
    JOIN loans l ON l.id = r.loan_id
    JOIN users u ON u.id = l.borrower_id
    LEFT JOIN receipts rc ON rc.repayment_id = r.id
    WHERE r.payment_source = 'automatic'
    ORDER BY r.created_at DESC
    LIMIT 20
""")
successes = c.fetchall()

# ── Last 20 FAILED IPN logs (arrived, no repayment) ──────────────────
c.execute("""
    SELECT
        l.created_at,
        JSON_UNQUOTE(JSON_EXTRACT(l.raw_data, '$.TransID'))         AS trans_id,
        JSON_UNQUOTE(JSON_EXTRACT(l.raw_data, '$.ThirdPartyTransID')) AS mpesa_ref,
        JSON_UNQUOTE(JSON_EXTRACT(l.raw_data, '$.TransAmount'))     AS amount,
        JSON_UNQUOTE(JSON_EXTRACT(l.raw_data, '$.BillRefNumber'))   AS bill_ref,
        JSON_UNQUOTE(JSON_EXTRACT(l.raw_data, '$.MSISDN'))          AS msisdn,
        JSON_UNQUOTE(JSON_EXTRACT(l.raw_data, '$.FirstName'))       AS fname,
        JSON_UNQUOTE(JSON_EXTRACT(l.raw_data, '$.MiddleName'))      AS mname,
        JSON_UNQUOTE(JSON_EXTRACT(l.raw_data, '$.LastName'))        AS lname,
        JSON_UNQUOTE(JSON_EXTRACT(l.raw_data, '$._processing_note')) AS note,
        l.raw_data
    FROM sasapay_ipn_logs l
    WHERE NOT EXISTS (
        SELECT 1 FROM repayments r
        WHERE r.mpesa_transaction_id =
              JSON_UNQUOTE(JSON_EXTRACT(l.raw_data,'$.TransID'))
    )
    AND JSON_UNQUOTE(JSON_EXTRACT(l.raw_data,'$.TransID')) != ''
    AND JSON_UNQUOTE(JSON_EXTRACT(l.raw_data,'$.TransID')) IS NOT NULL
    ORDER BY l.created_at DESC
    LIMIT 20
""")
failures = c.fetchall()


# ═══════════════════════════════════════════════════════════════════════
#  SECTION 1: SUCCESSFUL PAYMENTS — detail each one
# ═══════════════════════════════════════════════════════════════════════

hdr(f"SECTION 1 — LAST {len(successes)} SUCCESSFUL AUTO-PAYMENTS")
print(f"  These are IPN payments that DID create a repayment record.\n")

for i, (tid, rec_no, amount, pay_date, loan_no, borrower_phone,
        borrower_name, mpesa_phone, has_receipt) in enumerate(successes, 1):

    print(f"\n  [{i:02d}] {pay_date}  TransID: {tid}")
    row("    Receipt",        rec_no)
    row("    Amount",         f"KES {amount}")
    row("    Loan",           loan_no)
    row("    Borrower",       borrower_name.strip())
    row("    Borrower phone", borrower_phone)
    row("    MPESA phone",    mpesa_phone or '—')
    row("    Has receipt row",has_receipt,
        "⚠️ Missing — run fix_receipt_balances" if has_receipt == 'NO' else "")

    # Fetch the IPN log for this success to compare BillRef format
    c.execute("""
        SELECT
            JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.BillRefNumber')) AS bill_ref,
            JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.MSISDN'))        AS msisdn
        FROM sasapay_ipn_logs
        WHERE raw_data LIKE %s
        LIMIT 1
    """, [f'%{tid}%'])
    ipn_row = c.fetchone()
    if ipn_row:
        bill_ref_val, msisdn_val = ipn_row
        msisdn_display = f"[HASH:{msisdn_val[:12]}...]" if is_hash(msisdn_val) else msisdn_val
        row("    IPN BillRef",  repr(bill_ref_val))
        row("    IPN MSISDN",   msisdn_display,
            "hashed" if is_hash(msisdn_val) else "")
        # Did BillRef match borrower phone?
        if bill_ref_val:
            variants = ipn_phone_variants(bill_ref_val)
            matched = borrower_phone in variants if borrower_phone else False
            row("    BillRef→borrower match", "YES" if matched else "NO / matched another way",
                "" if matched else "check step 1b/1c")


# ═══════════════════════════════════════════════════════════════════════
#  SECTION 2: FAILED PAYMENTS — full replay each one
# ═══════════════════════════════════════════════════════════════════════

hdr(f"SECTION 2 — LAST {len(failures)} FAILED IPNs (arrived but no repayment created)")
print("  These payments reached our server but were NEVER recorded.\n")

fail_reasons = {}  # tally of drop reasons

for i, row_data in enumerate(failures, 1):
    created_at, tid, mpesa_ref, amount, bill_ref, msisdn, \
        fname, mname, lname, note, raw_data = row_data

    ipn_data = json.loads(raw_data) if isinstance(raw_data, str) else raw_data
    name = f"{fname or ''} {mname or ''} {lname or ''}".strip()

    print(f"\n  [{i:02d}] {created_at}  TransID: {tid}")
    row("    M-Pesa ref",  mpesa_ref or '—')
    row("    Amount",      f"KES {amount}")
    row("    Name",        name or '—')
    row("    BillRef",     repr(bill_ref),
        "HASH!" if is_hash(bill_ref) else
        "EMPTY!" if not bill_ref else "")
    msisdn_display = f"[HASH]" if is_hash(msisdn) else repr(msisdn)
    row("    MSISDN",      msisdn_display,
        "hashed — cannot match" if is_hash(msisdn) else "")

    if note:
        row("    Existing note", note)

    # ── Replay matching ──────────────────────────────────────────────
    replay = replay_matching(c, ipn_data)

    print(f"    {'─'*50}")
    print(f"    MATCHING REPLAY:")

    if replay['bill_ref_is_hash']:
        row("    Step 1a BillRef→phone", "SKIP — BillRef is a hash")
    elif not replay['bill_ref_variants']:
        row("    Step 1a BillRef→phone", "SKIP — BillRef empty or unrecognised format")
    else:
        row("    Step 1a BillRef→phone",
            f"{'✅ ' + replay['borrower_name'] if replay['step1a_match'] else '❌ no match'}",
            f"via {replay['step1a_variant']}" if replay['step1a_match'] else
            f"tried: {replay['bill_ref_variants']}")

    row("    Step 1b BillRef→loan#",
        f"{'✅ ' + (replay['step2_loan'] or '?') if replay['step1b_match'] else '❌ no match'}")

    if replay['msisdn_is_hash']:
        row("    Step 1c MSISDN→phone", "SKIP — MSISDN is a hash (SasaPay hashed it)")
    elif not replay['msisdn_variants']:
        row("    Step 1c MSISDN→phone", "SKIP — MSISDN empty or unrecognised")
    else:
        row("    Step 1c MSISDN→phone",
            f"{'✅ ' + replay['borrower_name'] if replay['step1c_match'] else '❌ no match'}",
            f"via {replay['step1c_variant']}" if replay['step1c_match'] else
            f"tried: {replay['msisdn_variants']}")

    if replay['borrower_name']:
        row("    Borrower found",  replay['borrower_name'])
        row("    is_active",       str(replay['borrower_active']),
            "" if replay['borrower_active'] else "⚠️ FALSE — this is why it failed")
        row("    role",            str(replay['borrower_role']),
            "" if replay['borrower_role'] == 'borrower' else "⚠️ wrong role")
    else:
        row("    Borrower found",  "❌ NONE")

    if replay['step2_loan']:
        row("    Active loan",     replay['step2_loan'])
        row("    Outstanding",     f"KES {replay['step3_outstanding']:.2f}",
            "⚠️ ZERO — payment would be dropped" if not replay['step3_ok'] else "")
    elif replay['borrower_name']:
        row("    Active loan",     "❌ NONE")

    drop = replay['drop_reason']
    if drop:
        print(f"\n    ⛔ DROP REASON: {drop}")
        fail_reasons[drop] = fail_reasons.get(drop, 0) + 1
    else:
        # All matching steps pass — crash was in Step 4 (SQL/ORM)
        print(f"\n    ⚠️  ALL MATCHING STEPS PASS — crash was in Step 4 (SQL insert)")
        print(f"       This payment should recover via /payments/admin/recover-ipn-payments/")
        fail_reasons['step4_sql_crash'] = fail_reasons.get('step4_sql_crash', 0) + 1

    # ── Check if in unknown_payments ─────────────────────────────────
    c.execute("""
        SELECT amount, notes, resolved, created_at
        FROM sasapay_unknown_payments
        WHERE reference=%s OR bill_ref=%s
        LIMIT 1
    """, [tid, bill_ref])
    unk = c.fetchone()
    if unk:
        u_amount, u_notes, u_resolved, u_date = unk
        row("    In unknown_payments", f"YES (resolved={u_resolved})",
            "can be manually matched" if not u_resolved else "")
    else:
        row("    In unknown_payments", "NO",
            "completely lost" if not replay['drop_reason'] else "")


# ═══════════════════════════════════════════════════════════════════════
#  SECTION 3: SIDE-BY-SIDE COMPARISON — success vs failure patterns
# ═══════════════════════════════════════════════════════════════════════

hdr("SECTION 3 — SUCCESS vs FAILURE PATTERN COMPARISON")

# Collect BillRef formats from successes
success_bill_refs = []
for (tid, rec_no, amount, pay_date, loan_no, borrower_phone,
     borrower_name, mpesa_phone, has_receipt) in successes:
    c.execute("""
        SELECT JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.BillRefNumber'))
        FROM sasapay_ipn_logs WHERE raw_data LIKE %s LIMIT 1
    """, [f'%{tid}%'])
    r = c.fetchone()
    if r:
        success_bill_refs.append(r[0] or '')

success_formats = {
    'phone_07xx':   sum(1 for b in success_bill_refs if b.startswith('07')),
    'phone_2547xx': sum(1 for b in success_bill_refs if b.startswith('254')),
    'phone_plus254':sum(1 for b in success_bill_refs if b.startswith('+254')),
    'loan_number':  sum(1 for b in success_bill_refs if b.startswith('BSH') or b.startswith('GRZ')),
    'hash':         sum(1 for b in success_bill_refs if is_hash(b)),
    'empty':        sum(1 for b in success_bill_refs if not b),
    'other':        sum(1 for b in success_bill_refs
                        if b and not b.startswith('07') and not b.startswith('254')
                        and not b.startswith('+254') and not b.startswith('BSH')
                        and not b.startswith('GRZ') and not is_hash(b)),
}

fail_bill_refs = []
for row_data in failures:
    _, _, _, _, bill_ref, *_ = row_data
    fail_bill_refs.append(bill_ref or '')

fail_formats = {
    'phone_07xx':   sum(1 for b in fail_bill_refs if b.startswith('07')),
    'phone_2547xx': sum(1 for b in fail_bill_refs if b.startswith('254')),
    'phone_plus254':sum(1 for b in fail_bill_refs if b.startswith('+254')),
    'loan_number':  sum(1 for b in fail_bill_refs if b.startswith('BSH') or b.startswith('GRZ')),
    'hash':         sum(1 for b in fail_bill_refs if is_hash(b)),
    'empty':        sum(1 for b in fail_bill_refs if not b),
    'other':        sum(1 for b in fail_bill_refs
                        if b and not b.startswith('07') and not b.startswith('254')
                        and not b.startswith('+254') and not b.startswith('BSH')
                        and not b.startswith('GRZ') and not is_hash(b)),
}

print(f"\n  {'BillRef format':<25} {'SUCCESSES':>12}  {'FAILURES':>10}")
print(f"  {'─'*25} {'─'*12}  {'─'*10}")
for fmt in success_formats:
    sv = success_formats[fmt]
    fv = fail_formats[fmt]
    flag = "  ← HIGH FAILURE RATE" if fv > sv and fv > 0 else ""
    print(f"  {fmt:<25} {sv:>12}  {fv:>10}{flag}")


# ═══════════════════════════════════════════════════════════════════════
#  SECTION 4: DROP REASON TALLY
# ═══════════════════════════════════════════════════════════════════════

hdr("SECTION 4 — DROP REASON TALLY (all failed IPNs)")
if fail_reasons:
    sorted_reasons = sorted(fail_reasons.items(), key=lambda x: -x[1])
    for reason, count in sorted_reasons:
        print(f"  {count:>4}×  {reason}")
else:
    print("  No failures found — all IPNs have matching repayments ✅")


# ═══════════════════════════════════════════════════════════════════════
#  SECTION 5: RECEIPT COVERAGE
# ═══════════════════════════════════════════════════════════════════════

hdr("SECTION 5 — RECEIPT COVERAGE FOR AUTO-PAYMENTS")

c.execute("""
    SELECT
        r.receipt_number,
        r.amount,
        r.payment_date,
        l.loan_number,
        CONCAT(u.first_name,' ',u.last_name) AS borrower
    FROM repayments r
    JOIN loans l ON l.id = r.loan_id
    JOIN users u ON u.id = l.borrower_id
    LEFT JOIN receipts rc ON rc.repayment_id = r.id
    WHERE r.payment_source='automatic' AND rc.id IS NULL
    ORDER BY r.payment_date DESC
    LIMIT 20
""")
no_receipt = c.fetchall()

if not no_receipt:
    print("\n  ✅ All automatic repayments have receipt rows.")
else:
    print(f"\n  ⚠️  {missing_receipts} automatic repayment(s) missing a receipt row.")
    print(f"  These show 'Missing' on the repayments page. Showing up to 20:\n")
    for rec_no, amount, pay_date, loan_no, borrower in no_receipt:
        print(f"  {pay_date}  {rec_no:<15}  {loan_no:<20}  "
              f"KES {float(amount):>10,.2f}  {borrower.strip()}")
    print(f"\n  Fix: python manage.py fix_receipt_balances")


# ═══════════════════════════════════════════════════════════════════════
#  SECTION 6: DEPLOYMENT VERSION & ENVIRONMENT
# ═══════════════════════════════════════════════════════════════════════

hdr("SECTION 6 — ENVIRONMENT & CODE VERSION")

from django.conf import settings
row("SITE_URL",        getattr(settings, 'SITE_URL', 'NOT SET'))
row("IPN endpoint",    getattr(settings, 'SITE_URL', '?') + '/payments/sasapay/ipn/')
row("DEBUG",           str(settings.DEBUG))

# Check if gunicorn has the new code loaded by hitting the IPN health-check
try:
    import urllib.request as _req
    site_url = getattr(settings, 'SITE_URL', '').rstrip('/')
    check_url = f"{site_url}/payments/sasapay/ipn/"
    with _req.urlopen(check_url, timeout=5) as _resp:
        _body = _resp.read().decode()
        if 'v4-rawsql-atomic' in _body:
            row("Live server version check", "v4-rawsql-atomic ✅ gunicorn running new code")
        elif 'v3-atomic' in _body:
            row("Live server version check", "v3-atomic ⚠️ GUNICORN NOT RESTARTED — restart it!")
        else:
            row("Live server version check", f"response: {_body[:80]}")
except Exception as _e:
    row("Live server version check", f"could not reach ({_e})")

try:
    from payments.sasapay_service import _SASAPAY_SERVICE_VERSION
    row("sasapay_service version", _SASAPAY_SERVICE_VERSION)
except Exception:
    row("sasapay_service version", "UNKNOWN — version marker missing")

# Check which Step 4 approach is deployed (reading from the imported module,
# not from disk, so this reflects what's actually running in memory)
try:
    import inspect
    from payments import sasapay_service as _svc

    # Force reimport to get what's actually on disk (useful after a deploy)
    import importlib
    importlib.reload(_svc)

    src = inspect.getsource(_svc.process_ipn_callback)
    if '_tx.atomic()' in src or 'transaction.atomic()' in src:
        if 'INSERT INTO repayments' in src:
            row("Step 4 approach", "raw SQL inside transaction.atomic() ✅ v4 (correct)")
        else:
            row("Step 4 approach", "ORM inside transaction.atomic() ⚠️ v3 (may still crash)")
    elif '_conn.commit()' in src:
        row("Step 4 approach", "raw SQL + explicit commit ⚠️ v2 (old — may silently fail)")
    else:
        row("Step 4 approach", "UNKNOWN — check sasapay_service.py manually")

    row("Diagnostic logging",  "ACTIVE ✅" if '_save_sasapay_ipn_log_note' in src else "NOT PRESENT ⚠️")
    row("Balance error guard",  "ACTIVE ✅" if 'outstanding_calc_failed' in src else "NOT PRESENT ⚠️")
    row("Step 4b receipt",     "ACTIVE ✅" if 'STEP 4b' in src else "NOT PRESENT ⚠️")
except Exception as e:
    row("Code inspection", f"ERROR: {e}")

# DB connection info
c.execute("SELECT VERSION()")
row("MySQL version", c.fetchone()[0])

c.execute("SELECT @@autocommit")
row("DB autocommit", str(c.fetchone()[0]))

c.execute("SELECT @@tx_isolation") if False else None
try:
    c.execute("SELECT @@transaction_isolation")
    row("Transaction isolation", c.fetchone()[0])
except Exception:
    try:
        c.execute("SELECT @@tx_isolation")
        row("Transaction isolation", c.fetchone()[0])
    except Exception:
        pass


# ═══════════════════════════════════════════════════════════════════════
#  SECTION 7: TIMING ANALYSIS — do failures cluster at certain times?
# ═══════════════════════════════════════════════════════════════════════

hdr("SECTION 7 — TIMING ANALYSIS")

c.execute("""
    SELECT
        DATE(created_at)                        AS day,
        COUNT(*)                                AS total_ipns,
        SUM(CASE WHEN EXISTS(
            SELECT 1 FROM repayments r
            WHERE r.mpesa_transaction_id =
                  JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.TransID'))
        ) THEN 1 ELSE 0 END)                    AS succeeded,
        SUM(CASE WHEN NOT EXISTS(
            SELECT 1 FROM repayments r
            WHERE r.mpesa_transaction_id =
                  JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.TransID'))
        ) AND JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.TransID')) != ''
        THEN 1 ELSE 0 END)                      AS failed
    FROM sasapay_ipn_logs
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY DATE(created_at)
    ORDER BY day DESC
""")
daily = c.fetchall()

print(f"\n  {'Date':<12} {'Total':>7} {'OK':>7} {'FAILED':>8}  {'Rate'}")
print(f"  {'─'*12} {'─'*7} {'─'*7} {'─'*8}  {'─'*20}")
for day, total, ok, failed in daily:
    rate = f"{100*failed/total:.0f}% fail" if total else "—"
    flag = "  ← HIGH FAIL RATE" if total and failed/total > 0.2 else ""
    print(f"  {str(day):<12} {total:>7} {ok:>7} {failed:>8}  {rate}{flag}")


# ═══════════════════════════════════════════════════════════════════════
#  SECTION 8: RECOVERABLE PAYMENTS
# ═══════════════════════════════════════════════════════════════════════

hdr("SECTION 8 — RECOVERABLE PAYMENTS (ALL-TIME)")
print("  IPN logs with no repayment but where the borrower+loan can be matched NOW:\n")

c.execute("""
    SELECT
        l.created_at,
        JSON_UNQUOTE(JSON_EXTRACT(l.raw_data,'$.TransID'))         AS trans_id,
        JSON_UNQUOTE(JSON_EXTRACT(l.raw_data,'$.TransAmount'))     AS amount,
        JSON_UNQUOTE(JSON_EXTRACT(l.raw_data,'$.BillRefNumber'))   AS bill_ref,
        JSON_UNQUOTE(JSON_EXTRACT(l.raw_data,'$.FirstName'))       AS fname,
        JSON_UNQUOTE(JSON_EXTRACT(l.raw_data,'$.LastName'))        AS lname,
        l.raw_data
    FROM sasapay_ipn_logs l
    WHERE NOT EXISTS (
        SELECT 1 FROM repayments r
        WHERE r.mpesa_transaction_id =
              JSON_UNQUOTE(JSON_EXTRACT(l.raw_data,'$.TransID'))
    )
    AND JSON_UNQUOTE(JSON_EXTRACT(l.raw_data,'$.TransID')) != ''
    AND JSON_UNQUOTE(JSON_EXTRACT(l.raw_data,'$.TransID')) IS NOT NULL
    ORDER BY l.created_at DESC
    LIMIT 50
""")
all_gaps = c.fetchall()

recoverable   = []
unrecoverable = []

for gap_date, tid, amount, bill_ref, fname, lname, raw_data in all_gaps:
    ipn_data = json.loads(raw_data) if isinstance(raw_data, str) else raw_data
    replay = replay_matching(c, ipn_data)
    name = f"{fname or ''} {lname or ''}".strip()

    if replay['step2_loan'] and replay['step3_ok']:
        recoverable.append((gap_date, tid, amount, name, replay['step2_loan']))
    else:
        unrecoverable.append((gap_date, tid, amount, name,
                               replay['drop_reason'] or 'unknown'))

print(f"  ✅ RECOVERABLE ({len(recoverable)}) — use /payments/admin/recover-ipn-payments/\n")
for gap_date, tid, amount, name, loan_no in recoverable:
    print(f"    {gap_date}  KES {float(amount):>10,.2f}  {tid}  {loan_no}  {name}")

print(f"\n  ❌ UNRECOVERABLE ({len(unrecoverable)}) — need manual investigation\n")
for gap_date, tid, amount, name, reason in unrecoverable[:20]:
    print(f"    {gap_date}  KES {float(amount):>10,.2f}  {name}")
    print(f"      Reason: {reason}")


# ═══════════════════════════════════════════════════════════════════════
#  FINAL SUMMARY
# ═══════════════════════════════════════════════════════════════════════

hdr("FINAL SUMMARY")
print(f"""
  IPN logs total         : {total_ipns}
  Auto repayments saved  : {total_auto}
  Payments lost (gaps)   : {total_gaps}
  Missing receipt rows   : {missing_receipts}
  Recoverable right now  : {len(recoverable)}
  Unrecoverable          : {len(unrecoverable)}

  TOP ACTIONS:
  1. If 'step4_sql_crash' appears in Section 4:
     → Deploy the updated sasapay_service.py (ORM + transaction.atomic)
  2. Recover lost payments:
     → /payments/admin/recover-ipn-payments/ → Preview → Commit
  3. Fix missing receipt rows:
     → python manage.py fix_receipt_balances
  4. Monitor future failures:
     → /payments/sasapay/ipn-gaps/ (new diagnostic page)
""")