"""
FAITH Payment Diagnosis Script
================================
Run on the server with:

    python manage.py shell < diagnose_faith_detailed.py

or:

    python manage.py shell -c "$(cat diagnose_faith_detailed.py)"

Do NOT run directly with `python diagnose_faith_detailed.py` — it won't
have DB credentials. It must run through manage.py shell.
"""
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("=" * 80)
print("FAITH PAYMENT DIAGNOSIS")
print("SMS: UF1O16E915 | KES 1,500 | Loan BSH/202602/00173 | 01/06/2026 21:07")
print("=" * 80)

# ============================================================================
# STEP 1: SMS log — was SMS sent from Django?
# ============================================================================
print("\n[1] SMS LOG")
c.execute("""
    SELECT sms_type, borrower_name, loan_number, amount, status, created_at,
           SUBSTR(message, 1, 150) as msg_preview
    FROM sms_logs
    WHERE message LIKE '%FAITH%'
      AND created_at >= '2026-06-01 20:00:00'
      AND created_at <= '2026-06-01 23:59:00'
    ORDER BY created_at DESC
    LIMIT 10
""")
rows = c.fetchall()
if rows:
    for r in rows:
        print(f"  [{r[5]}] type={r[0]} status={r[4]} borrower={r[1]} loan={r[2]} amount={r[3]}")
        print(f"    msg: {r[6]}")
else:
    print("  NOT FOUND — no SMS log for FAITH on 01/06/2026 20:00–23:59")
    # Try broader search
    c.execute("SELECT sms_type, borrower_name, loan_number, amount, status, created_at FROM sms_logs WHERE message LIKE '%UF1O16E915%' LIMIT 5")
    rows2 = c.fetchall()
    if rows2:
        print("  But found by TransID UF1O16E915:")
        for r in rows2:
            print(f"    [{r[5]}] {r[0]} | {r[1]} | {r[2]} | {r[3]} | {r[4]}")
    else:
        print("  Also not found by UF1O16E915 — SMS may have been sent by old PHP system")

# ============================================================================
# STEP 2: IPN log — did SasaPay call our endpoint?
# ============================================================================
print("\n[2] SASAPAY IPN LOG")
c.execute("""
    SELECT id, created_at, raw_data
    FROM sasapay_ipn_logs
    WHERE created_at >= '2026-06-01 20:00:00'
      AND created_at <= '2026-06-01 23:59:00'
    ORDER BY created_at DESC
    LIMIT 30
""")
rows = c.fetchall()
faith_ipn = None
if rows:
    print(f"  Found {len(rows)} IPN(s) in window 20:00–23:59:")
    for ipn_id, created, raw in rows:
        try:
            d = json.loads(raw) if isinstance(raw, str) else raw
            tid  = d.get('TransID', '')
            ref  = d.get('ThirdPartyTransID', '')
            bill = d.get('BillRefNumber', '')
            amt  = d.get('TransAmount', '')
            fn   = d.get('FirstName', '')
            ln   = d.get('LastName', '')
            print(f"  [{created}] TransID={tid} | ThirdParty={ref} | BillRef={bill} | Amount={amt} | {fn} {ln}")
            if 'UF1O16E915' in f"{tid}{ref}" or 'FAITH' in f"{fn}{ln}".upper() or str(amt) == '1500':
                faith_ipn = d
                print("    ^^^ THIS LOOKS LIKE FAITH'S PAYMENT ^^^")
        except Exception as e:
            print(f"  Error parsing IPN {ipn_id}: {e}")
else:
    print("  NO IPNs received between 20:00–23:59 on 01/06/2026")
    print("  SasaPay likely sent to old PHP URL, not /payments/sasapay/ipn/")

# ============================================================================
# STEP 3: Repayment record — does it exist in DB?
# ============================================================================
print("\n[3] REPAYMENT RECORD IN DATABASE")

# Search by every possible identifier
searches = [
    ("mpesa_transaction_id LIKE '%UF1O16E915%'",           "TransID match"),
    ("receipt_number LIKE '%UF1O16E915%'",                  "Receipt match"),
    ("amount = 1500 AND payment_date >= '2026-06-01 20:00'", "Amount+time match"),
]

found_any = False
for where, label in searches:
    c.execute(f"""
        SELECT r.id, r.receipt_number, r.amount, r.payment_date,
               r.mpesa_transaction_id, r.payment_source, r.created_at,
               l.loan_number, u.first_name, u.last_name,
               u.branch_id, l.application_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 {where}
    """)
    rows = c.fetchall()
    if rows:
        found_any = True
        print(f"  FOUND via [{label}]:")
        for r in rows:
            print(f"    Receipt: {r[1]}")
            print(f"    Amount: {r[2]}  Date: {r[3]}  Created: {r[6]}")
            print(f"    TransID: {r[4]}  Source: {r[5]}")
            print(f"    Loan: {r[7]}  Borrower: {r[8]} {r[9]}")
            print(f"    Branch ID: {r[10]} {'(NULL)' if r[10] is None else ''}")
            print(f"    Application ID: {r[11]} {'(NULL — will cause select_related to fail)' if r[11] is None else ''}")

if not found_any:
    print("  NOT IN DATABASE — repayment was never saved")
    print("  Either: INSERT failed, or transaction was rolled back after SMS")

# ============================================================================
# STEP 4: Does loan BSH/202602/00173 exist?
# ============================================================================
print("\n[4] LOAN BSH/202602/00173")
c.execute("""
    SELECT l.id, l.loan_number, l.status, l.borrower_id, l.application_id,
           u.first_name, u.last_name, u.phone_number, u.branch_id
    FROM loans l
    LEFT JOIN users u ON u.id = l.borrower_id
    WHERE l.loan_number = 'BSH/202602/00173'
""")
rows = c.fetchall()
if rows:
    r = rows[0]
    print(f"  FOUND: Borrower={r[5]} {r[6]}  Phone={r[7]}  Status={r[2]}  Branch={r[8]}")
    print(f"  Loan DB id: {r[0]}  Application id: {r[4]}")
    # Check application exists
    if r[4]:
        c.execute("SELECT id FROM loan_applications WHERE id = %s", [r[4]])
        app = c.fetchone()
        if not app:
            print(f"  !! APPLICATION {r[4]} MISSING — select_related will drop repayments via INNER JOIN")
    # Check repayments for this loan
    c.execute("SELECT receipt_number, amount, payment_date, mpesa_transaction_id FROM repayments WHERE loan_id = %s ORDER BY payment_date DESC LIMIT 10", [r[0]])
    reps = c.fetchall()
    print(f"  Repayments on this loan: {len(reps)}")
    for rep in reps:
        print(f"    {rep[0]} | KES {rep[1]} | {rep[2]} | {rep[3]}")
else:
    print("  NOT FOUND on this server")

# ============================================================================
# STEP 5: Does borrower FAITH exist?
# ============================================================================
print("\n[5] BORROWER FAITH")
c.execute("""
    SELECT id, first_name, last_name, phone_number, branch_id, is_active
    FROM users
    WHERE (first_name LIKE '%FAITH%' OR last_name LIKE '%FAITH%')
      AND role = 'borrower'
""")
rows = c.fetchall()
if rows:
    for r in rows:
        print(f"  FOUND: {r[1]} {r[2]}  Phone={r[3]}  Branch={r[4]}  Active={r[5]}")
else:
    print("  NOT FOUND on this server")

# ============================================================================
# STEP 6: Raw vs ORM repayment counts
# ============================================================================
print("\n[6] RAW vs ORM REPAYMENT COUNTS")
c.execute("SELECT COUNT(*) FROM repayments")
raw_total = c.fetchone()[0]
orm_total = Repayment.objects.count()
orm_with_joins = Repayment.objects.select_related(
    'loan', 'loan__borrower', 'loan__application__loan_product'
).count()
print(f"  Raw SQL total:              {raw_total}")
print(f"  ORM total (no joins):       {orm_total}")
print(f"  ORM with select_related:    {orm_with_joins}")
if raw_total != orm_with_joins:
    print(f"  !! MISMATCH: {raw_total - orm_with_joins} rows hidden by INNER JOIN")

# ============================================================================
# STEP 7: Recent repayments near FAITH's payment time
# ============================================================================
print("\n[7] ALL REPAYMENTS ON 2026-06-01 AFTER 20:00")
c.execute("""
    SELECT r.receipt_number, r.amount, r.payment_date, r.mpesa_transaction_id,
           u.first_name, u.last_name, l.loan_number
    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.payment_date >= '2026-06-01 20:00:00'
    ORDER BY r.payment_date DESC
    LIMIT 20
""")
rows = c.fetchall()
if rows:
    print(f"  Found {len(rows)} repayment(s):")
    for r in rows:
        print(f"    {r[2]} | {r[4]} {r[5]} | {r[6]} | {r[0]} | KES {r[1]} | {r[3]}")
else:
    print("  NONE — no repayments recorded after 20:00 on 01/06/2026")
    print("  Confirms: payments coming in are NOT being saved to DB")

print("\n" + "=" * 80)
print("END OF DIAGNOSIS — paste full output back for analysis")
print("=" * 80)