#!/usr/bin/env python
"""
diagnose_specific_payments.py
──────────────────────────────
Checks specific M-Pesa transactions by reference number to find exactly
why they didn't record in the system.

Run on the server:
    python diagnose_specific_payments.py
"""

import os, sys, django, json
os.environ['DJANGO_SETTINGS_MODULE'] = 'branch_system.settings_production'
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
django.setup()

from decimal import Decimal
from django.db import connection

# ── Payments to investigate ───────────────────────────────────────────────────
# (mpesa_ref, amount, account_used, borrower_name, date)
PAYMENTS = [
    ('UFGGT8LN3M',  Decimal('1100.00'), '0724633735', 'Jane Wambura',       '16/6/26 18:32'),
    ('UFGJ08EHPW',  Decimal('2500.00'), '0724628383', 'Roseline Mwangangi', '16/6/26 18:26'),
    ('UFGJR81Y0Q',  Decimal('2000.00'), '0748736118', 'Edwin',              '16/6/26 10:39'),
    ('UFHO1868U4',  Decimal('1500.00'), '0721994742', 'Faith Kibuchi',      '17/6/26 06:56'),
    ('UFG5M8580B',  Decimal('5000.00'), '0729244413', 'Ester Mathini',      '16/6/26 20:24'),
]

SEP  = '─' * 72
SEP2 = '═' * 72

def phone_variants(phone):
    p = phone.strip().lstrip('+')
    variants = set()
    if p.startswith('254'):
        variants = {p, '+' + p, '0' + p[3:]}
    elif p.startswith('0'):
        variants = {p, '254' + p[1:], '+254' + p[1:]}
    else:
        variants = {p}
    return variants

def run():
    from users.models import CustomUser
    from loans.models import Loan, Repayment

    print()
    print(SEP2)
    print('  SPECIFIC PAYMENT DIAGNOSIS')
    print(SEP2)

    # ── Global IPN health check first ────────────────────────────────────────
    with connection.cursor() as c:
        c.execute("SELECT COUNT(*) FROM sasapay_ipn_logs")
        total_ipns = c.fetchone()[0]
        c.execute("""
            SELECT COUNT(*) FROM sasapay_ipn_logs
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 2 DAY)
        """)
        recent_ipns = c.fetchone()[0]

    print(f'\n  SasaPay IPN log: {total_ipns} total, {recent_ipns} in last 48h')
    if recent_ipns == 0:
        print('  ⚠️  NO IPNs received in last 48h — SasaPay is not calling your server.')
        print('     Most likely cause: IPN URL not registered or wrong in SasaPay portal.')
        print('     Expected URL: https://uzuriapps.xyz/payments/sasapay/ipn/')
    print()

    for mpesa_ref, amount, account_phone, name, date in PAYMENTS:
        print(SEP)
        print(f'  {name}  |  KES {amount:,.0f}  |  Ref: {mpesa_ref}  |  {date}')
        print(f'  Account used at M-Pesa: 1122#{account_phone}')
        print(SEP)

        # ── 1. Check if IPN arrived ───────────────────────────────────────
        with connection.cursor() as c:
            c.execute(
                "SELECT id, created_at, raw_data, processing_status, processing_note "
                "FROM sasapay_ipn_logs "
                "WHERE raw_data LIKE %s OR raw_data LIKE %s "
                "ORDER BY created_at DESC LIMIT 5",
                [f'%{mpesa_ref}%', f'%{account_phone}%']
            )
            ipn_rows = c.fetchall()

        if not ipn_rows:
            print('  ❌  IPN NEVER ARRIVED — not in sasapay_ipn_logs at all.')
            print('      SasaPay did not call your server for this payment.')
            print('      → This is an IPN delivery failure, not a matching failure.')
            _check_ipn_url_hint()
        else:
            for ipn_id, ipn_time, raw, status, note in ipn_rows:
                try:
                    d = json.loads(raw) if isinstance(raw, str) else (raw or {})
                except Exception:
                    d = {}
                trans_id  = d.get('TransID', '?')
                bill_ref  = d.get('BillRefNumber', '?')
                ipn_amount = d.get('TransAmount', '?')
                print(f'  IPN received: {ipn_time}')
                print(f'    TransID   : {trans_id}')
                print(f'    BillRef   : {bill_ref!r}  (what the customer typed)')
                print(f'    Amount    : KES {ipn_amount}')
                print(f'    Status    : {status}')
                if note:
                    print(f'    Drop note : {note}')

                # Check if repayment exists
                with connection.cursor() as c:
                    c.execute(
                        "SELECT receipt_number, loan_id, amount, payment_date "
                        "FROM repayments WHERE mpesa_transaction_id = %s LIMIT 1",
                        [trans_id]
                    )
                    rep = c.fetchone()

                if rep:
                    print(f'  ✅ Repayment EXISTS: {rep[0]}  KES {rep[2]}  {rep[3]}')
                else:
                    print(f'  ❌ NO repayment created')
                    _explain_drop(status, note, bill_ref, account_phone)
                print()

        # ── 2. Check if borrower exists with this phone ───────────────────
        variants = phone_variants(account_phone)
        borrower = CustomUser.objects.filter(
            phone_number__in=variants, role='borrower'
        ).first()

        if not borrower:
            print(f'  ⚠️  No borrower found with phone {account_phone}')
            print(f'      Tried variants: {variants}')
            print('      → Either the phone number in the system is different,')
            print('        or this customer is not registered.')
        else:
            loan = Loan.objects.filter(
                borrower=borrower, status='active', is_deleted=False
            ).order_by('-created_at').first()
            id_num = getattr(borrower, 'id_number', '') or ''
            print(f'  Borrower  : {borrower.get_full_name()}')
            print(f'  Phone in DB: {borrower.phone_number}')
            print(f'  ID Number : {id_num or "⚠️  NOT SET"}')
            print(f'  Active loan: {loan.loan_number if loan else "NONE"}')
            if loan:
                try:
                    print(f'  Outstanding: KES {loan.outstanding_amount:,.2f}')
                except Exception as e:
                    print(f'  Outstanding: ERROR — {e}')

        print()

    # ── Summary of all IPN gaps in last 48h ──────────────────────────────────
    print(SEP2)
    print('  ALL IPN GAPS — last 48 hours')
    print(SEP2)
    with connection.cursor() as c:
        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, '$.FirstName'))     AS fname,
                JSON_UNQUOTE(JSON_EXTRACT(l.raw_data, '$.LastName'))      AS lname,
                l.processing_status,
                l.processing_note
            FROM sasapay_ipn_logs l
            WHERE l.created_at >= DATE_SUB(NOW(), INTERVAL 48 HOUR)
              AND l.processing_status NOT IN ('processed', 'duplicate')
            ORDER BY l.created_at DESC
        """)
        gaps = c.fetchall()

    if not gaps:
        print('  No gaps in last 48h (or no IPNs received at all).')
    else:
        print(f'  {len(gaps)} unprocessed IPN(s) in last 48h:\n')
        for row in gaps:
            created, tid, mref, amt, bref, fn, ln, status, note = row
            print(f'  ❌ {created}  KES {amt}  Ref={mref}  BillRef={bref!r}  '
                  f'Name={fn} {ln}')
            print(f'     Status: {status}  |  Note: {note or "—"}')
            print()

    # ── Check IPN URL is reachable ────────────────────────────────────────────
    print(SEP2)
    print('  IPN URL HEALTH CHECK')
    print(SEP2)
    try:
        import requests as _req
        from django.conf import settings
        base = getattr(settings, 'SITE_URL', 'https://uzuriapps.xyz').rstrip('/')
        url  = f'{base}/payments/sasapay/ipn/'
        resp = _req.get(url, timeout=10)
        if resp.status_code == 200:
            print(f'  ✅ IPN endpoint reachable: {url}')
            print(f'     Response: {resp.text[:120]}')
        else:
            print(f'  ⚠️  IPN endpoint returned HTTP {resp.status_code}: {url}')
    except Exception as e:
        print(f'  ❌ Could not reach IPN endpoint: {e}')

    print()
    print(SEP2)
    print('  HOW TO MANUALLY RECORD THE MISSED PAYMENTS')
    print(SEP2)
    print('  For each payment above that shows "IPN NEVER ARRIVED":')
    print('  1. Go to the loan in the admin panel')
    print('  2. Click "Record Payment" and enter the amount manually')
    print('  3. Use the M-Pesa reference (e.g. UFGGT8LN3M) as the reference')
    print()
    print('  To fix the root cause (IPN not arriving):')
    print('  → Log into SasaPay merchant portal')
    print('  → Settings → IPN URL → set to:')
    print('     https://uzuriapps.xyz/payments/sasapay/ipn/')
    print()


def _check_ipn_url_hint():
    print('      ─────────────────────────────────────────')
    print('      The admin is receiving M-Pesa SMS but the system is not.')
    print('      This is a SasaPay IPN delivery problem — SasaPay accepted')
    print('      the payment but did not notify your server.')
    print('      ─────────────────────────────────────────')


def _explain_drop(status, note, bill_ref, account_phone):
    """Print a plain-English explanation of why the IPN was dropped."""
    if status == 'no_match':
        print(f'  REASON: No borrower found matching BillRef {bill_ref!r}')
        print(f'          The customer paid using phone {account_phone} as the')
        print(f'          account reference. Check that this phone number exists')
        print(f'          in the borrowers table exactly as typed.')
    elif status == 'no_loan':
        print(f'  REASON: Borrower was matched but has no ACTIVE loan.')
        print(f'          Their loan may be marked as paid/rolled_over/defaulted.')
    elif status == 'no_balance':
        print(f'  REASON: Loan outstanding balance is 0 — already fully paid.')
    elif status == 'failed':
        print(f'  REASON: Processing error — {note}')
        print('          Check server error logs for the full traceback.')
    elif status == 'pending':
        print(f'  REASON: IPN arrived but was never processed (stuck in pending).')
        print('          This may be a code bug introduced during deployment.')
    else:
        print(f'  REASON: {status} — {note}')


if __name__ == '__main__':
    run()