"""
DIAGNOSTIC: Why did Rose's June 7 KES 600 payment (SPEJ7SB44I2XRV3) fail
while today's payments succeeded?

Compares the IPN log, repayments table, borrower record, loan record,
and unknown_payments table for the failed TransID vs a known-good one.

Run on production:
    python diagnose_ipn_june7_vs_today.py

No writes are made — read-only throughout.
"""

import django
import os
import json
from decimal import Decimal

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

from django.db import connection

SEP = "=" * 70

# ── Transactions to inspect ────────────────────────────────────────────────
# Rose's failed payment (Jun 7, 22:59) + today's four ⚡ Auto payments
FAILED_TRANS_ID  = 'SPEJ7SB44I2XRV3'   # Rose KES 600
# Add any today TransID here to use as the "good" comparison baseline.
# Leave empty and the script will auto-pick the most recent automatic repayment.
GOOD_TRANS_ID    = ''   # fill in e.g. 'SJK0AB12XY...' or leave blank

ROSE_PHONE       = '+254727564254'
ROSE_LOAN        = 'BSH/202602/00163'


def run():
    c = connection.cursor()

    # ── 1. IPN LOG for the failed payment ──────────────────────────────────
    print(SEP)
    print(f"1. IPN LOG — failed TransID: {FAILED_TRANS_ID}")
    print(SEP)
    c.execute("""
        SELECT id, created_at, raw_data
        FROM sasapay_ipn_logs
        WHERE raw_data LIKE %s
        ORDER BY created_at ASC
    """, [f'%{FAILED_TRANS_ID}%'])
    rows = c.fetchall()
    if not rows:
        print("  ❌ NOT FOUND in sasapay_ipn_logs — IPN never arrived at the server.")
        print("     Possible causes: wrong callback URL registered with SasaPay,")
        print("     server was down, or SasaPay didn't fire this notification.")
    else:
        print(f"  ✅ Found {len(rows)} IPN log entr{'y' if len(rows)==1 else 'ies'}")
        for log_id, created_at, raw_data in rows:
            d = json.loads(raw_data) if isinstance(raw_data, str) else raw_data
            print(f"\n  Log ID:     {log_id}")
            print(f"  Received:   {created_at}")
            print(f"  TransID:    {d.get('TransID')}")
            print(f"  MPesaRef:   {d.get('ThirdPartyTransID')}")
            print(f"  Amount:     KES {d.get('TransAmount')}")
            print(f"  BillRef:    {d.get('BillRefNumber')!r}   ← what customer put as account ref")
            print(f"  MSISDN:     {d.get('MSISDN')!r}          ← phone that actually paid")
            print(f"  Name:       {d.get('FirstName')} {d.get('MiddleName','')} {d.get('LastName')}")
            note = d.get('_processing_note')
            if note:
                print(f"  Drop note:  {note}   ← why it was rejected (added by new code)")
            else:
                print(f"  Drop note:  (none — payment predates diagnostic logging)")

    # ── 2. Was a repayment created? ────────────────────────────────────────
    print()
    print(SEP)
    print(f"2. REPAYMENT ROW — TransID: {FAILED_TRANS_ID}")
    print(SEP)
    c.execute("""
        SELECT r.receipt_number, r.amount, r.payment_source,
               r.payment_date, r.created_at,
               l.loan_number
        FROM repayments r
        JOIN loans l ON l.id = r.loan_id
        WHERE r.mpesa_transaction_id = %s
    """, [FAILED_TRANS_ID])
    rows = c.fetchall()
    if not rows:
        print("  ❌ No repayment row — payment was NOT recorded in the system.")
    else:
        for rec_no, amount, src, pay_date, created_at, loan_no in rows:
            print(f"  ✅ FOUND: receipt={rec_no}  amount={amount}  "
                  f"loan={loan_no}  date={pay_date}")

    # ── 3. Was it saved as an unknown payment? ─────────────────────────────
    print()
    print(SEP)
    print(f"3. UNKNOWN PAYMENTS — searching for {FAILED_TRANS_ID} / {ROSE_PHONE}")
    print(SEP)
    c.execute("""
        SELECT amount, paid_by, msisdn, bill_ref, reference, notes, resolved, created_at
        FROM sasapay_unknown_payments
        WHERE reference = %s OR msisdn LIKE %s OR bill_ref LIKE %s
        ORDER BY created_at DESC
        LIMIT 10
    """, [FAILED_TRANS_ID, '%727564254%', '%727564254%'])
    rows = c.fetchall()
    if not rows:
        print("  (nothing found — not in unknown_payments either)")
        print("  → IPN arrived and was logged, but either:")
        print("    a) Processing crashed before the unknown_payments insert, OR")
        print("    b) It was silently dropped between IPN log and unknown_payments save")
    else:
        for amount, paid_by, msisdn, bill_ref, ref, notes, resolved, created_at in rows:
            print(f"  Amount: {amount}  Ref: {ref}  MSISDN: {msisdn}")
            print(f"  BillRef: {bill_ref!r}  Resolved: {resolved}")
            print(f"  Notes:  {notes}")
            print(f"  Date:   {created_at}")

    # ── 4. Borrower record for Rose ────────────────────────────────────────
    print()
    print(SEP)
    print(f"4. BORROWER — Rose Wairimu Njuguna ({ROSE_PHONE})")
    print(SEP)

    # Check all phone variants
    variants = []
    p = ROSE_PHONE.strip()
    variants.append(p)
    if p.startswith('+254'):
        variants += ['0' + p[4:], p[1:]]
    elif p.startswith('0'):
        variants += ['+254' + p[1:], '254' + p[1:]]

    c.execute("""
        SELECT id, first_name, last_name, phone_number, role, is_active, status
        FROM users
        WHERE phone_number IN ({})
        ORDER BY id
    """.format(','.join(['%s'] * len(variants))), variants)
    rows = c.fetchall()
    if not rows:
        print(f"  ❌ No user found for phone variants: {variants}")
        print("     This means the phone matching would fail — payment goes to unknown_payments")
    else:
        for uid, fn, ln, phone, role, is_active, status in rows:
            active_flag = "✅ is_active=True" if is_active else "❌ is_active=False"
            print(f"  User: {fn} {ln}  phone={phone}  role={role}")
            print(f"  {active_flag}  status={status}  id={uid}")
            if not is_active:
                print("  ⚠️  FOUND: is_active=False — borrower matching REQUIRES is_active=True")
                print("     This is why the payment was not matched on June 7!")
            if role != 'borrower':
                print(f"  ⚠️  FOUND: role={role!r} — borrower matching REQUIRES role='borrower'")

    # ── 5. Loan record for BSH/202602/00163 ───────────────────────────────
    print()
    print(SEP)
    print(f"5. LOAN — {ROSE_LOAN}")
    print(SEP)
    c.execute("""
        SELECT id, loan_number, status, is_deleted,
               principal_amount, amount_paid, due_date,
               borrower_id
        FROM loans
        WHERE loan_number = %s
    """, [ROSE_LOAN])
    rows = c.fetchall()
    if not rows:
        print(f"  ❌ Loan {ROSE_LOAN} not found in loans table")
    else:
        for lid, ln, status, is_del, principal, paid, due, borrower_id in rows:
            print(f"  Loan:      {ln}")
            print(f"  Status:    {status}  is_deleted={is_del}")
            print(f"  Principal: {principal}  Paid: {paid}")
            print(f"  Due date:  {due}")
            print(f"  BorrowerID:{borrower_id}")
            if status != 'active':
                print(f"  ⚠️  status={status!r} — loan matching requires status='active'")
            if is_del:
                print(f"  ⚠️  is_deleted=True — ActiveLoanManager would exclude this loan!")

    # ── 6. Compare with a today's GOOD payment ────────────────────────────
    print()
    print(SEP)
    print("6. TODAY'S SUCCESSFUL AUTO-PAYMENTS (for comparison)")
    print(SEP)

    good_tid = GOOD_TRANS_ID
    if not good_tid:
        # Auto-pick most recent automatic repayment
        c.execute("""
            SELECT r.mpesa_transaction_id, r.receipt_number, r.amount,
                   r.payment_date, l.loan_number
            FROM repayments r
            JOIN loans l ON l.id = r.loan_id
            WHERE r.payment_source = 'automatic'
            ORDER BY r.created_at DESC
            LIMIT 5
        """)
        good_rows = c.fetchall()
        if good_rows:
            good_tid = good_rows[0][0]
            print(f"  Auto-selected most recent automatic repayments:")
            for tid, rec, amt, pay_date, ln in good_rows:
                print(f"    ✅ {pay_date}  {tid}  {ln}  KES {amt}")
        else:
            print("  No automatic repayments found to compare against.")

    if good_tid and good_tid != FAILED_TRANS_ID:
        print()
        print(f"  IPN log for good TransID {good_tid}:")
        c.execute("""
            SELECT created_at, raw_data
            FROM sasapay_ipn_logs
            WHERE raw_data LIKE %s
            ORDER BY created_at ASC
        """, [f'%{good_tid}%'])
        for created_at, raw_data in c.fetchall():
            d = json.loads(raw_data) if isinstance(raw_data, str) else raw_data
            print(f"    Received: {created_at}")
            print(f"    BillRef:  {d.get('BillRefNumber')!r}")
            print(f"    MSISDN:   {d.get('MSISDN')!r}")
            print(f"    Amount:   KES {d.get('TransAmount')}")

    # ── 7. IPN gap check — all unprocessed IPNs around June 7 ─────────────
    print()
    print(SEP)
    print("7. ALL UNPROCESSED IPNs — Jun 6 to Jun 9 (IPN arrived, no repayment created)")
    print(SEP)
    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, '$.MSISDN'))       AS msisdn,
               JSON_UNQUOTE(JSON_EXTRACT(l.raw_data, '$.FirstName'))    AS fname,
               JSON_UNQUOTE(JSON_EXTRACT(l.raw_data, '$.LastName'))     AS lname,
               JSON_UNQUOTE(JSON_EXTRACT(l.raw_data, '$._processing_note')) AS note
        FROM sasapay_ipn_logs l
        WHERE l.created_at BETWEEN '2026-06-06 00:00:00' AND '2026-06-09 23:59:59'
          AND 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 ASC
    """)
    rows = c.fetchall()
    if not rows:
        print("  ✅ No gaps found — all IPNs in this window have matching repayments.")
    else:
        print(f"  ❌ {len(rows)} unprocessed IPN(s) found:\n")
        for created_at, tid, amount, bill_ref, msisdn, fname, lname, note in rows:
            print(f"  {created_at}  {tid}")
            print(f"    Amount: KES {amount}   Name: {fname} {lname}")
            print(f"    BillRef: {bill_ref!r}   MSISDN: {msisdn!r}")
            print(f"    Drop reason: {note or '(none — predates diagnostic logging)'}")
            print()

    # ── 8. Dry-run replay through matching logic ──────────────────────────
    print()
    print(SEP)
    print(f"8. DRY-RUN REPLAY — simulate process_ipn_callback for {FAILED_TRANS_ID}")
    print(SEP)

    c.execute("""
        SELECT raw_data FROM sasapay_ipn_logs
        WHERE raw_data LIKE %s LIMIT 1
    """, [f'%{FAILED_TRANS_ID}%'])
    row = c.fetchone()
    if not row:
        print("  Cannot replay — IPN log not found.")
    else:
        ipn_data = json.loads(row[0]) if isinstance(row[0], str) else row[0]

        from payments.sasapay_service import _phone_variants, _normalise_phone
        from users.models import CustomUser
        from loans.models import Loan

        bill_ref = ipn_data.get('BillRefNumber', '')
        msisdn   = ipn_data.get('MSISDN', '')
        amount   = ipn_data.get('TransAmount', '0')

        print(f"  BillRef : {bill_ref!r}")
        print(f"  MSISDN  : {msisdn!r}")
        print(f"  Amount  : KES {amount}")
        print()

        # Step 1a — BillRef as phone
        borrower   = None
        match_note = None
        for variant in _phone_variants(bill_ref):
            try:
                borrower = CustomUser.objects.get(
                    phone_number=variant, role='borrower', is_active=True
                )
                match_note = f"BillRef variant '{variant}'"
                break
            except CustomUser.DoesNotExist:
                continue
            except CustomUser.MultipleObjectsReturned:
                borrower = CustomUser.objects.filter(
                    phone_number=variant, role='borrower', is_active=True
                ).first()
                match_note = f"BillRef variant '{variant}' (first of multiple)"
                break

        if borrower:
            print(f"  Step 1a (BillRef as phone) : ✅ matched → {borrower.get_full_name()} via {match_note}")
        else:
            print(f"  Step 1a (BillRef as phone) : ❌ no match for {_phone_variants(bill_ref)}")

        # Step 1b — BillRef as loan number
        if not borrower:
            try:
                loan_by_ref = Loan.active_objects.get(loan_number=bill_ref, status='active')
                borrower    = loan_by_ref.borrower
                match_note  = f"loan number '{bill_ref}'"
                print(f"  Step 1b (BillRef as loan#) : ✅ matched → {borrower.get_full_name()}")
            except Exception:
                print(f"  Step 1b (BillRef as loan#) : ❌ no active loan '{bill_ref}'")

        # Step 1c — MSISDN fallback
        if not borrower:
            msisdn_variants = _phone_variants(msisdn)
            print(f"  Step 1c (MSISDN fallback)  : MSISDN={msisdn!r}")
            if not msisdn_variants:
                print("             ⚠️  MSISDN is a SHA-256 hash — SasaPay hashed the phone number.")
                print("             _phone_variants() returns [] for a hash → MSISDN fallback always fails.")

        if not borrower:
            print()
            print("  ❌ BORROWER MATCH FAILED — payment would go to unknown_payments")
        else:
            # Step 2 — find active loan
            loan = (
                Loan.active_objects
                .filter(borrower=borrower, status='active')
                .order_by('-id')
                .first()
            )
            if loan:
                print(f"  Step 2 (active loan)       : ✅ {loan.loan_number}")
            else:
                print("  Step 2 (active loan)       : ❌ no active loan found")

            if loan:
                # Step 3 — outstanding balance
                try:
                    outstanding = loan.outstanding_amount
                    balance_ok  = outstanding > 0
                    print(f"  Step 3 (outstanding)       : {'✅' if balance_ok else '❌'} {outstanding}")
                    if not balance_ok:
                        print("             ⚠️  Outstanding was 0 — old code would have dropped the payment here.")
                        print("             New code has outstanding_calc_failed guard to prevent this.")
                    else:
                        print()
                        print("  ━━━ ROOT CAUSE: Step 4 SQL crash ━━━")
                        print("  All matching steps PASS today. The crash on Jun 7 was inside")
                        print("  the raw SQL INSERT (Step 4). Most likely: UUID format mismatch.")
                        print("  The code used .replace('-','') (no-dash hex) but the DB column")
                        print("  may store loan IDs WITH dashes — causing an FK violation that")
                        print("  was silently swallowed by the view's outer except.")
                        print("  SasaPay got HTTP 200 back and never retried.")
                        print()

                        # Check exactly how UUIDs are stored
                        c.execute("SELECT id FROM loans WHERE loan_number=%s LIMIT 1", [loan.loan_number])
                        db_row = c.fetchone()
                        if db_row:
                            stored = str(db_row[0])
                            with_dashes = str(loan.id)
                            no_dashes   = with_dashes.replace('-', '')
                            print(f"  UUID FORMAT CHECK:")
                            print(f"    DB stores loan.id as  : {stored!r}")
                            print(f"    str(loan.id)          : {with_dashes!r}")
                            print(f"    str(loan.id).replace  : {no_dashes!r}")
                            if stored == with_dashes:
                                print("    ⚠️  DB stores WITH dashes. Code was stripping dashes → FK mismatch!")
                                print("    ⚠️  THIS IS THE JUNE 7 CRASH CAUSE.")
                            elif stored == no_dashes:
                                print("    ✅ DB stores without dashes — .replace() is correct.")
                            else:
                                print(f"    ❓ Unexpected stored format: {stored!r}")

                        c.execute("SELECT id FROM repayments LIMIT 1")
                        rep_row = c.fetchone()
                        if rep_row:
                            rep_id = str(rep_row[0])
                            print(f"    DB repayment.id       : {rep_id!r}")
                            if '-' in rep_id:
                                print("    ⚠️  Repayments stored WITH dashes. Code uses .hex (no dashes) → PK mismatch!")
                            else:
                                print("    ✅ Repayments stored without dashes — .hex is correct.")
                        print()
                        print("  The new code fixes this with per-step logging so future crashes")
                        print("  appear in server logs and the IPN Gaps page (/payments/sasapay/ipn-gaps/).")
                except Exception as e:
                    print(f"  Step 3 (outstanding)       : ❌ RAISED: {e}")
                    print("             Old code returned 0 on this error → payment dropped.")
                    print("             New code: outstanding_calc_failed=True → proceeds to Step 4.")

    # ── 9. Summary ────────────────────────────────────────────────────────
    print()
    print(SEP)
    print("9. SUMMARY / WHAT TO DO NEXT")
    print(SEP)
    print("""
  Root cause confirmed:
    IPN arrived ✅ | Borrower active ✅ | Loan active ✅
    No repayment ❌ | Not in unknown_payments ❌

    The old process_ipn_callback() crashed silently after saving the IPN log.
    The view's outer except returned HTTP 200 to SasaPay — so it never retried.
    No error was logged anywhere visible.

    The new code adds per-step logging so every future failure is traceable.

  To recover Rose's KES 600 payment:
    → /payments/admin/recover-ipn-payments/ → Preview → Commit
    → Both the repayment row AND receipt row will be created.
""")


if __name__ == '__main__':
    run()