#!/usr/bin/env python
"""
diagnose_multi_borrowers.py
───────────────────────────
Diagnoses why automatic payments failed for a list of borrowers.
Searches by name fragment — no need for exact loan numbers.

Run on the server:
    python diagnose_multi_borrowers.py

Output: one section per borrower showing exactly why their IPNs
        were dropped or never arrived.
"""

import os, sys, django, json
from decimal import Decimal

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

from django.db import connection
from django.db.models import Sum, Q

from loans.models import Loan, Repayment, MpesaTransaction
from users.models import CustomUser
from payments.sasapay_models import SasaPayUnknownPayment, SasaPayIPNLog

# ── Borrowers to investigate ──────────────────────────────────────────────────
TARGETS = [
    'Anastasia wangari',
    'Rose njuguna',
    'Isaac kingu',        # "kungu" might be a typo
    'Stephen diaries',    # could be a nickname / business name
    'Elijah muya',
]

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

def hr(title=''):
    print()
    print(SEP)
    if title:
        print(f'  {title}')
        print(SEP)

def phone_variants(phone):
    """Return all common Kenyan phone format variants for a number."""
    variants = set()
    p = (phone or '').strip()
    if not p:
        return variants
    if p.startswith('+'):
        variants |= {p, p[1:], '0' + p[4:]}
    elif p.startswith('254'):
        variants |= {p, '+' + p, '0' + p[3:]}
    elif p.startswith('0'):
        variants |= {p, '254' + p[1:], '+254' + p[1:]}
    else:
        variants.add(p)
    return variants

def _fuzzy_match_score(query_word, candidate):
    """
    Return a 0-100 similarity score between query_word and candidate string.
    Uses SequenceMatcher — handles typos, swapped letters, truncated names.
    """
    from difflib import SequenceMatcher
    q = query_word.lower().strip()
    c = candidate.lower().strip()
    if not q or not c:
        return 0
    # Exact substring match is best
    if q in c or c in q:
        return 95
    return int(SequenceMatcher(None, q, c).ratio() * 100)

def find_borrowers(name_fragment):
    """
    Find borrower(s) by name fragment with fuzzy matching.

    Strategy (in order):
      1. Exact substring match on first/last name (fast DB query)
      2. Each word in the fragment tried individually as a substring
      3. Fuzzy similarity scoring on all remaining borrowers — anything
         scoring >= FUZZY_THRESHOLD is included with a warning

    Returns a list of (borrower, match_note) tuples.
    """
    FUZZY_THRESHOLD = 72   # 0-100; lower = more permissive

    parts = [p for p in name_fragment.strip().split() if len(p) > 1]

    # ── Pass 1: exact substring per word ─────────────────────────────────
    q = Q()
    for part in parts:
        q |= (Q(first_name__icontains=part) |
              Q(last_name__icontains=part)  |
              Q(username__icontains=part))

    exact = list(CustomUser.objects.filter(q, role='borrower').distinct())
    if exact:
        # If multiple exact matches, prefer borrowers whose first_name matches
        # the first word of the query (cuts "Anastasia wangari" from 9 → 1)
        if len(exact) > 1 and parts:
            first_word = parts[0].lower()
            narrowed = [b for b in exact
                        if first_word in (b.first_name or '').lower()]
            if narrowed:
                exact = narrowed
        return [(b, 'exact match') for b in exact]

    # ── Pass 2: fuzzy score against every active borrower ────────────────
    all_borrowers = CustomUser.objects.filter(role='borrower', is_active=True)
    scored = []
    for b in all_borrowers:
        full = b.get_full_name().lower()
        first = (b.first_name or '').lower()
        last  = (b.last_name  or '').lower()

        best = 0
        for part in parts:
            for field in [full, first, last]:
                s = _fuzzy_match_score(part, field)
                if s > best:
                    best = s
            # Also try each word in the stored name individually
            for stored_word in full.split():
                s = _fuzzy_match_score(part, stored_word)
                if s > best:
                    best = s

        if best >= FUZZY_THRESHOLD:
            scored.append((best, b))

    if not scored:
        return []

    scored.sort(key=lambda x: -x[0])
    # Collapse: if top score is >=90 return only those tied at the top,
    # otherwise return all above threshold (max 5)
    top_score = scored[0][0]
    if top_score >= 90:
        results = [(b, f'fuzzy {score}%') for score, b in scored if score >= 90]
    else:
        results = [(b, f'fuzzy {score}% — verify this is the right person')
                   for score, b in scored[:5]]
    return results

def get_ipn_logs_for_borrower(borrower):
    """Fetch all SasaPay IPN logs that might belong to this borrower."""
    phone = getattr(borrower, 'phone_number', '') or ''
    id_num = getattr(borrower, 'id_number', '') or ''
    variants = phone_variants(phone)
    search_terms = list(filter(None, variants | {id_num}))

    rows = []
    seen_ids = set()
    with connection.cursor() as c:
        for term in search_terms:
            c.execute(
                "SELECT id, created_at, raw_data FROM sasapay_ipn_logs "
                "WHERE raw_data LIKE %s ORDER BY created_at ASC",
                [f'%{term}%']
            )
            for row in c.fetchall():
                if row[0] not in seen_ids:
                    seen_ids.add(row[0])
                    rows.append(row)
    return rows

def diagnose_borrower(name_fragment):
    print()
    print(SEP2)
    print(f'  TARGET: {name_fragment.upper()}')
    print(SEP2)

    results = find_borrowers(name_fragment)

    if not results:
        print(f'  ❌  No borrower found matching "{name_fragment}".')
        print('      Tried exact substring and fuzzy matching — no match above 72%.')
        print('      Check spelling or search the admin panel.')
        return

    if len(results) > 1:
        print(f'  ⚠️   {len(results)} borrower(s) matched:')
        for b, note in results:
            print(f'       - {b.get_full_name():<30}  phone={b.phone_number}  '
                  f'id={b.id_number}  [{note}]')
        print('      Running diagnosis for ALL of them.')
        print()

    for borrower, match_note in results:
        print(f'  Match: {match_note}')
        _diagnose_one(borrower)

def _diagnose_one(borrower):
    phone  = getattr(borrower, 'phone_number', '') or ''
    id_num = getattr(borrower, 'id_number', '')  or ''
    variants = phone_variants(phone)

    print(f'  Borrower  : {borrower.get_full_name()}')
    print(f'  Phone     : {phone}')
    print(f'  ID Number : {id_num or "⚠️  NOT SET"}')
    print(f'  Active    : {borrower.is_active}')
    print()

    # ── Active loans ─────────────────────────────────────────────────────
    active_loans = Loan.objects.filter(
        borrower=borrower, status='active', is_deleted=False
    ).order_by('-created_at')

    all_loans = Loan.objects.filter(
        borrower=borrower, is_deleted=False
    ).order_by('-created_at')

    if not active_loans.exists():
        latest = all_loans.first()
        if latest:
            print(f'  ⚠️  No ACTIVE loan. Latest loan: {latest.loan_number} — status={latest.status}')
            print('      If the loan is "paid" or "rolled_over", SasaPay IPN will drop payments')
            print('      to unknown_payments because there\'s no active loan to credit.')
        else:
            print('  ❌  No loans at all for this borrower.')
        print()
    else:
        for loan in active_loans:
            try:
                outstanding = loan.outstanding_amount
                outstanding_str = f'KES {outstanding:,.2f}'
            except Exception as e:
                outstanding_str = f'ERROR: {e}'
            total_paid = loan.repayments.aggregate(t=Sum('amount'))['t'] or Decimal('0')
            print(f'  Loan      : {loan.loan_number}  status={loan.status}')
            print(f'  Principal : KES {loan.principal_amount:,.2f}')
            print(f'  Paid      : KES {total_paid:,.2f}  |  Outstanding: {outstanding_str}')
            print(f'  Due       : {loan.due_date}')
            print()

    # ── MpesaTransaction rows ─────────────────────────────────────────────
    mpesa_txns = MpesaTransaction.objects.filter(borrower=borrower).order_by('-created_at')
    if mpesa_txns.exists():
        print(f'  MpesaTransactions ({mpesa_txns.count()}):')
        for t in mpesa_txns:
            flag = '✅' if t.status == 'processed' else '❌'
            print(f'    {flag} [{t.status:<18}] {str(t.created_at)[:19]}  '
                  f'KES {t.amount:,.2f}  ref={t.trans_id or t.mpesa_transaction_id}')
            if t.processing_notes:
                print(f'         notes: {t.processing_notes}')
    else:
        print('  MpesaTransactions : none')
    print()

    # ── SasaPay IPN logs ──────────────────────────────────────────────────
    ipn_rows = get_ipn_logs_for_borrower(borrower)

    if not ipn_rows:
        print('  SasaPay IPNs : ❌  NONE found for this borrower.')
        print()
        print('  ┌─ WHY NO IPNs? Most likely causes (in order of likelihood):')
        print('  │')
        print('  │  1. SasaPay IPN URL is wrong or was not set when these payments')
        print('  │     were made. Check the SasaPay merchant portal → Settings →')
        print('  │     IPN URL should be: https://uzuriapps.xyz/payments/sasapay/ipn/')
        print('  │')
        print('  │  2. Customer paid to the OLD PHP system (grazuri.uzuriapps.xyz)')
        print('  │     which was the registered IPN URL before migration.')
        if not id_num:
            print('  │')
            print('  │  3. ⚠️  ID number is missing on this borrower profile.')
            print('  │     Even if IPN arrives, it cannot match without an ID.')
        print('  └─')
    else:
        print(f'  SasaPay IPNs : {len(ipn_rows)} found')
        print()
        for ipn_id, created_at, raw_data in ipn_rows:
            try:
                d = json.loads(raw_data) if isinstance(raw_data, str) else (raw_data or {})
            except Exception:
                d = {}

            trans_id  = d.get('TransID', '?')
            amount    = d.get('TransAmount', '?')
            bill_ref  = d.get('BillRefNumber', '?')
            msisdn    = d.get('MSISDN', '?')
            note      = d.get('_processing_note', '')
            mpesa_ref = d.get('ThirdPartyTransID', '')

            with connection.cursor() as c:
                c.execute(
                    "SELECT receipt_number FROM repayments "
                    "WHERE mpesa_transaction_id = %s LIMIT 1",
                    [trans_id]
                )
                rep_row = c.fetchone()

            if rep_row:
                outcome = f'✅ Repayment created → {rep_row[0]}'
            else:
                outcome = '❌ NO repayment created'

            print(f'    {outcome}')
            print(f'      Date    : {created_at}')
            print(f'      TransID : {trans_id}  |  M-Pesa ref: {mpesa_ref}')
            print(f'      Amount  : KES {amount}')
            print(f'      BillRef : {bill_ref!r}')
            print(f'      MSISDN  : {str(msisdn)[:30]}{"..." if len(str(msisdn)) > 30 else ""}')
            if note:
                print(f'      ⚠️  Drop reason: {note}')
                _explain_drop(note, bill_ref, id_num, phone)
            print()

    # ── Unknown/suspense payments ─────────────────────────────────────────
    name_parts = borrower.get_full_name().split()
    uq = Q()
    for v in variants:
        if v:
            uq |= Q(msisdn__icontains=v) | Q(bill_ref__icontains=v)
    for part in name_parts:
        if len(part) > 2:
            uq |= Q(paid_by__icontains=part)

    unknown = SasaPayUnknownPayment.objects.filter(uq) if uq else SasaPayUnknownPayment.objects.none()
    if unknown.exists():
        print(f'  ⚠️  SUSPENSE (unknown_payments): {unknown.count()} unmatched payment(s)!')
        for u in unknown:
            resolved = '✅ resolved' if u.resolved else '❌ UNRESOLVED'
            print(f'    [{resolved}] KES {u.amount}  from={u.paid_by!r}  '
                  f'msisdn={u.msisdn}  ref={u.bill_ref!r}')
            print(f'      notes: {u.notes}')
        print('  → Go to /payments/sasapay/unknown-payments/ to assign these.')
    else:
        print('  Suspense      : none')

    # ── IPN gap check via raw SQL (catches ALL gaps, not just for this borrower) ──
    # Show any recent IPNs with no matching repayment, filtered by phone variants
    gap_terms = list(filter(None, variants | {id_num}))
    gap_rows = []
    seen = set()
    with connection.cursor() as c:
        for term in gap_terms:
            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,'$._processing_note')) AS note
                FROM sasapay_ipn_logs l
                WHERE l.raw_data LIKE %s
                  AND NOT EXISTS (
                      SELECT 1 FROM repayments r
                      WHERE r.mpesa_transaction_id =
                            JSON_UNQUOTE(JSON_EXTRACT(l.raw_data,'$.TransID'))
                  )
                ORDER BY l.created_at DESC
            """, [f'%{term}%'])
            for row in c.fetchall():
                key = row[1]  # trans_id
                if key and key not in seen:
                    seen.add(key)
                    gap_rows.append(row)

    if gap_rows:
        print()
        print(f'  IPN GAPS (arrived but no repayment): {len(gap_rows)}')
        for created_at, trans_id, amount, bill_ref, note in gap_rows:
            print(f'    ❌ {created_at}  KES {amount}  TransID={trans_id}  '
                  f'BillRef={bill_ref!r}')
            if note:
                print(f'       Drop reason: {note}')

    print()
    print('  ' + '─' * 68)


def _explain_drop(note, bill_ref, id_num, phone):
    """Print a human explanation of a _processing_note drop reason."""
    note_lower = (note or '').lower()
    print('         ↳ ', end='')
    if 'no borrower' in note_lower or 'no match' in note_lower:
        print('Customer used an account reference the system couldn\'t match.')
        print(f'            BillRef used: {bill_ref!r}')
        print(f'            ID on record: {id_num!r}')
        print(f'            Phone on record: {phone!r}')
        print('            Fix: ensure the borrower\'s ID number is set, OR customer')
        print('            must type their ID number (not phone) at the M-Pesa prompt.')
    elif 'no active loan' in note_lower:
        print('Borrower was matched but has NO active loan at time of payment.')
        print('            Fix: check loan status — may be rolled_over or paid.')
    elif 'no outstanding' in note_lower or 'no balance' in note_lower:
        print('Loan is fully paid / outstanding balance was 0.')
    elif 'already processed' in note_lower or 'duplicate' in note_lower:
        print('Duplicate IPN — payment was already recorded.')
    else:
        print(f'Unknown drop reason. Full note: {note}')


# ── Run ───────────────────────────────────────────────────────────────────────
print()
print(SEP2)
print('  PAYMENT FAILURE DIAGNOSIS — PRODUCTION')
print(f'  Targets: {len(TARGETS)} borrowers')
print(SEP2)

for target in TARGETS:
    diagnose_borrower(target)

print()
print(SEP2)
print('  GLOBAL IPN GAPS (last 30 days — all borrowers)')
print(SEP2)
with connection.cursor() as c:
    c.execute("""
        SELECT COUNT(*) FROM sasapay_ipn_logs l
        WHERE l.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
          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
          )
          AND JSON_UNQUOTE(JSON_EXTRACT(l.raw_data,'$.TransID')) != ''
          AND JSON_UNQUOTE(JSON_EXTRACT(l.raw_data,'$.TransID')) IS NOT NULL
    """)
    total_gaps = c.fetchone()[0]

    c.execute("""
        SELECT COUNT(*) FROM sasapay_ipn_logs
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    """)
    total_ipns = c.fetchone()[0]

    c.execute("""
        SELECT
            JSON_UNQUOTE(JSON_EXTRACT(l.raw_data,'$._processing_note')) AS reason,
            COUNT(*) AS cnt
        FROM sasapay_ipn_logs l
        WHERE l.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
          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,'$._processing_note')) IS NOT NULL
          AND JSON_UNQUOTE(JSON_EXTRACT(l.raw_data,'$._processing_note')) != 'null'
        GROUP BY reason
        ORDER BY cnt DESC
    """)
    reasons = c.fetchall()

print(f'  Total IPNs last 30 days : {total_ipns}')
print(f'  IPNs with no repayment  : {total_gaps}  '
      f'({(total_gaps/total_ipns*100):.1f}% drop rate)' if total_ipns else '  No IPNs.')
print()
if reasons:
    print('  Drop reasons breakdown:')
    for reason, cnt in reasons:
        print(f'    {cnt:>4}×  {reason}')

print()
print(SEP2)
print('  Diagnosis complete.')
print(SEP2)