#!/usr/bin/env python
"""
diagnose_loan_payments.py
─────────────────────────
Diagnostic script: given a loan number (or borrower phone), shows why
payments are or aren't being recorded.

Usage (run from the project root):
    python diagnose_loan_payments.py

Edit the LOAN_NUMBER or BORROWER_PHONE constants below before running.
"""

import os
import sys
import django

# ─── 1. Bootstrap Django ──────────────────────────────────────────────────────
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
sys.path.insert(0, BASE_DIR)
# Force production settings — must match what wsgi.py uses on the server.
# We set this explicitly (not setdefault) so it overrides any stale env var.
os.environ['DJANGO_SETTINGS_MODULE'] = 'branch_system.settings_production'
django.setup()

# ─── 2. Target ────────────────────────────────────────────────────────────────
# Change these values to the loan / borrower you want to investigate
LOAN_NUMBER    = 'BSH/202508/00105'   # ← Lucy Muthoni's loan
BORROWER_PHONE = '+254723488245'       # ← her phone (optional fallback)

# ─── 3. Imports (after django.setup) ─────────────────────────────────────────
from decimal import Decimal
from django.db import connection
from django.db.models import Sum

from loans.models import Loan, Repayment, MpesaTransaction
from users.models import CustomUser

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

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


# ─────────────────────────────────────────────────────────────────────────────
# SECTION A: Loan & Borrower
# ─────────────────────────────────────────────────────────────────────────────
hr('A. LOAN')
try:
    loan = Loan.objects.get(loan_number=LOAN_NUMBER)
except Loan.DoesNotExist:
    print(f'  ❌  Loan {LOAN_NUMBER!r} does NOT exist in the database.')
    print('      Check the loan number and try again.')
    sys.exit(1)

borrower = loan.borrower
print(f'  Loan           : {loan.loan_number}')
print(f'  Status         : {loan.status}')
print(f'  Borrower       : {borrower.get_full_name() if borrower else "NOT SET"}')
print(f'  Borrower phone : {getattr(borrower, "phone_number", "N/A")}')
print(f'  ID Number      : {getattr(borrower, "id_number", "NOT SET") or "NOT SET"}')
print(f'  is_active      : {borrower.is_active if borrower else "N/A"}')
print(f'  Principal      : KES {loan.principal_amount:,.2f}')

try:
    outstanding = loan.outstanding_amount
    print(f'  Outstanding    : KES {outstanding:,.2f}')
except Exception as e:
    print(f'  Outstanding    : ⚠️  ERROR computing outstanding_amount: {e}')
    outstanding = None

db_amount_paid = loan.repayments.aggregate(total=Sum('amount'))['total'] or Decimal('0')
print(f'  Repayments sum : KES {db_amount_paid:,.2f}  (direct DB aggregate)')
print(f'  Due Date       : {loan.due_date}')
print(f'  Disbursed      : {loan.disbursement_date if hasattr(loan, "disbursement_date") else "N/A"}')


# ─────────────────────────────────────────────────────────────────────────────
# SECTION B: All repayments on this loan
# ─────────────────────────────────────────────────────────────────────────────
hr('B. REPAYMENTS ON THIS LOAN')
repayments = Repayment.objects.filter(loan=loan).order_by('payment_date')
if not repayments.exists():
    print('  No repayments found.')
else:
    print(f'  {"Receipt":<20} {"Date":<22} {"Amount":>12} {"Method":<12} {"Source":<12} {"MPesa Ref"}')
    print(f'  {"-"*20} {"-"*22} {"-"*12} {"-"*12} {"-"*12} {"-"*20}')
    for r in repayments:
        mpesa_ref = r.mpesa_transaction_id or ''
        print(f'  {r.receipt_number:<20} {str(r.payment_date)[:19]:<22} {r.amount:>12,.2f} '
              f'{r.payment_method:<12} {r.payment_source:<12} {mpesa_ref}')
    print()
    print(f'  Total recorded: KES {repayments.aggregate(t=Sum("amount"))["t"]:,.2f}  ({repayments.count()} payments)')


# ─────────────────────────────────────────────────────────────────────────────
# SECTION C: MpesaTransaction rows linked to this loan
# ─────────────────────────────────────────────────────────────────────────────
hr('C. MPESA TRANSACTIONS — linked to this loan')
linked_txns = MpesaTransaction.objects.filter(loan=loan).order_by('-created_at')
if not linked_txns.exists():
    print('  None found.')
else:
    for t in linked_txns:
        print(f'  TransID    : {t.trans_id or t.mpesa_transaction_id}')
        print(f'  Amount     : KES {t.amount:,.2f}')
        print(f'  Status     : {t.status}')
        print(f'  BillRef    : {t.bill_ref_number}')
        print(f'  Repayment  : {t.repayment_id or "NOT LINKED"}')
        print(f'  Notes      : {t.processing_notes}')
        print(f'  Created    : {t.created_at}')
        print()


# ─────────────────────────────────────────────────────────────────────────────
# SECTION D: All MpesaTransaction rows for this borrower (any status)
# ─────────────────────────────────────────────────────────────────────────────
hr('D. ALL MPESA TRANSACTIONS for this borrower')
borrower_txns = MpesaTransaction.objects.filter(borrower=borrower).order_by('-created_at') if borrower else []
if not borrower_txns:
    print('  No transactions found for this borrower.')
else:
    for t in borrower_txns:
        status_flag = '✅' if t.status == 'processed' else '❌'
        print(f'  {status_flag} [{t.status:<18}] {str(t.created_at)[:19]}  '
              f'KES {t.amount:,.2f}  TransID={t.trans_id or t.mpesa_transaction_id}')
        if t.processing_notes:
            print(f'       Notes: {t.processing_notes}')


# ─────────────────────────────────────────────────────────────────────────────
# SECTION E: SasaPay IPN logs for this borrower / loan
# ─────────────────────────────────────────────────────────────────────────────
hr('E. SASAPAY IPN LOGS — any IPN matching borrower phone, ID, or loan number')

id_number    = getattr(borrower, 'id_number', '') or ''
phone_number = getattr(borrower, 'phone_number', '') or ''
# Build all phone variants
phone_variants = []
p = phone_number.strip()
if p.startswith('+'):
    phone_variants = [p, p[1:], '0' + p[4:]]
elif p.startswith('254'):
    phone_variants = [p, '+' + p, '0' + p[3:]]
elif p.startswith('0'):
    phone_variants = [p, '254' + p[1:], '+254' + p[1:]]
else:
    phone_variants = [p]

search_terms = list(set(filter(None, [LOAN_NUMBER, id_number] + phone_variants + [BORROWER_PHONE])))

with connection.cursor() as c:
    ipn_rows = []
    for term in search_terms:
        if not term:
            continue
        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():
            # deduplicate by id
            if not any(r[0] == row[0] for r in ipn_rows):
                ipn_rows.append(row)

if not ipn_rows:
    print('  ❌  No SasaPay IPN logs found for this borrower/loan.')
    print('      This means SasaPay never sent us a notification for these payments,')
    print('      OR the BillRefNumber used at payment time does not match any known')
    print('      identifier (phone, ID number, loan number) for this borrower.')
else:
    import json as _json
    print(f'  Found {len(ipn_rows)} IPN log(s):')
    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
        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', '')

        # Check if this IPN resulted in a repayment
        with connection.cursor() as c2:
            c2.execute(
                "SELECT receipt_number FROM repayments WHERE mpesa_transaction_id = %s LIMIT 1",
                [trans_id]
            )
            rep_row = c2.fetchone()

        status_flag = '✅ Repayment created' if rep_row else '❌ NO repayment'
        receipt_str = f'  Receipt: {rep_row[0]}' if rep_row else ''

        print(f'  {status_flag}{receipt_str}')
        print(f'    IPN ID    : {ipn_id}')
        print(f'    Received  : {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    : {msisdn}')
        if note:
            print(f'    ⚠️  Note   : {note}')
        print()


# ─────────────────────────────────────────────────────────────────────────────
# SECTION F: Unknown / unmatched SasaPay payments (suspense bucket)
# ─────────────────────────────────────────────────────────────────────────────
hr('F. UNKNOWN/UNMATCHED SASAPAY PAYMENTS (suspense) — for this borrower')
from payments.sasapay_models import SasaPayUnknownPayment
from django.db.models import Q
unk_qs = SasaPayUnknownPayment.objects.all()
name_parts = borrower.get_full_name().split() if borrower else []
q = Q()
for v in phone_variants:
    if v:
        q |= Q(msisdn__icontains=v) | Q(bill_ref__icontains=v)
for part in name_parts:
    if len(part) > 2:
        q |= Q(paid_by__icontains=part)
matches = unk_qs.filter(q) if q else unk_qs.none()
if matches.exists():
    print(f'  Found {matches.count()} unmatched payment(s) in suspense:')
    for u in matches:
        resolved_flag = '✅ Resolved' if u.resolved else '❌ Unresolved'
        print(f'    [{resolved_flag}]  KES {u.amount}  from {u.paid_by}')
        print(f'      MSISDN  : {u.msisdn}')
        print(f'      BillRef : {u.bill_ref}')
        print(f'      Ref     : {u.reference}')
        print(f'      Notes   : {u.notes}')
        print(f'      Created : {u.created_at}')
        print()
    print('  → Go to /payments/sasapay/unknown-payments/ to manually match these.')
else:
    print('  No unmatched payments found in suspense for this borrower.')


# ─────────────────────────────────────────────────────────────────────────────
# SECTION G: M-Pesa C2B callbacks (Safaricom direct)
# ─────────────────────────────────────────────────────────────────────────────
hr('G. MPESA C2B CALLBACKS (Safaricom raw) — for this borrower')
from payments.models import MpesaCallback
# Search raw_data JSON for any phone / ID / loan ref
cb_qs = MpesaCallback.objects.all()
from django.db.models import Q as DQ
cb_q = DQ()
for v in phone_variants + [id_number, LOAN_NUMBER]:
    if v:
        cb_q |= DQ(raw_data__icontains=v)
callbacks = cb_qs.filter(cb_q).order_by('-created_at')[:20]
if not callbacks.exists():
    print('  No raw M-Pesa C2B callbacks found for this borrower.')
else:
    print(f'  Found {callbacks.count()} callback(s):')
    for cb in callbacks:
        d = cb.raw_data or {}
        trans_id = d.get('TransID', '?')
        amount   = d.get('TransAmount', '?')
        bill_ref = d.get('BillRefNumber', '?')
        print(f'    [{cb.callback_type:<14}] Processed={cb.processed}  '
              f'TransID={trans_id}  Amount={amount}  BillRef={bill_ref!r}')
        if cb.transaction:
            t = cb.transaction
            print(f'      → Linked to MpesaTransaction status={t.status}  '
                  f'repayment={t.repayment_id or "NONE"}')
        print()


# ─────────────────────────────────────────────────────────────────────────────
# SECTION H: Borrower ID number match test
# ─────────────────────────────────────────────────────────────────────────────
hr('H. BORROWER ID NUMBER — match test')
id_num = getattr(borrower, 'id_number', None)
if not id_num:
    print('  ⚠️  Borrower has NO id_number set in the database.')
    print('     M-Pesa C2B payments match on bill_ref_number = id_number.')
    print('     If this field is blank, ALL automatic payments will fail to match.')
else:
    print(f'  ID number on record : {id_num!r}')
    print('  ✅  ID number is set. Payments typed with this ID should auto-match.')
    print()
    print('  Phone variants that would match this borrower:')
    for v in phone_variants:
        print(f'    {v}')


# ─────────────────────────────────────────────────────────────────────────────
# SECTION I: Receipt number integrity check
# ─────────────────────────────────────────────────────────────────────────────
hr('I. RECEIPT NUMBER INTEGRITY')
from utils.models import Receipt
for r in repayments:
    receipt_exists = Receipt.objects.filter(receipt_number=r.receipt_number).exists()
    flag = '✅' if receipt_exists else '⚠️  MISSING in receipts table'
    print(f'  {r.receipt_number}  → {flag}')


# ─────────────────────────────────────────────────────────────────────────────
# SECTION J: SUMMARY & RECOMMENDATIONS
# ─────────────────────────────────────────────────────────────────────────────
hr('J. SUMMARY')
print(f'  Loan          : {loan.loan_number}  ({loan.status})')
print(f'  Borrower      : {borrower.get_full_name() if borrower else "N/A"}')
print(f'  ID number     : {id_num or "⚠️  NOT SET"}')
print(f'  Principal     : KES {loan.principal_amount:,.2f}')
print(f'  Repayments    : {repayments.count()} recorded  (KES {db_amount_paid:,.2f})')
if outstanding is not None:
    print(f'  Outstanding   : KES {outstanding:,.2f}')
print()

issues = []

if not id_num:
    issues.append(
        '❌ CRITICAL: Borrower has no ID number — all auto-matching will fail.\n'
        '   Fix: go to the borrower profile and add their national ID number.'
    )

failed_txns = list(MpesaTransaction.objects.filter(borrower=borrower, status='failed')) if borrower else []
unlinked_txns = list(MpesaTransaction.objects.filter(borrower=borrower, repayment__isnull=True).exclude(status__in=['failed','rejected','cancelled'])) if borrower else []

if failed_txns:
    issues.append(
        f'⚠️  {len(failed_txns)} MpesaTransaction(s) have status=failed.\n'
        '   Fix: Go to /payments/transactions/<id>/reprocess/ to retry each one.'
    )

if unlinked_txns:
    issues.append(
        f'⚠️  {len(unlinked_txns)} MpesaTransaction(s) exist but have no linked repayment.\n'
        '   Fix: Go to /payments/transactions/<id>/manual-match/ to link them.'
    )

ipn_unmatched_count = sum(
    1 for _, _, raw in ipn_rows
    if '_processing_note' in (raw if isinstance(raw, str) else str(raw))
)

if not issues:
    print('  ✅ No obvious issues detected from the data above.')
    print('     Check sections E and F above for dropped/unmatched IPN details.')
else:
    print('  ISSUES FOUND:')
    for issue in issues:
        print()
        for line in issue.split('\n'):
            print(f'  {line}')

print()
print('  Useful admin URLs:')
print(f'  /payments/transactions/?borrower=...      — all transactions for this borrower')
print(f'  /payments/sasapay/unknown-payments/       — suspense payments to manually assign')
print(f'  /payments/sasapay/ipn-gaps/               — IPN logs with no matching repayment')
print(f'  /payments/unconfirmed-payments/           — payments awaiting admin approval')
print()
print(SEP2)
print('  Diagnosis complete.')
print(SEP2)