#!/usr/bin/env python
"""
diagnose_regression.py — run: python diagnose_regression.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 django.db import connection

SEP = '─' * 72

with connection.cursor() as c:

    # Overall stats last 7 days
    c.execute("""
        SELECT processing_status, COUNT(*) FROM sasapay_ipn_logs
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
        GROUP BY processing_status ORDER BY COUNT(*) DESC
    """)
    stats = c.fetchall()
    total = sum(r[1] for r in stats)
    print(f"\n  IPN STATUS — last 7 days  (total: {total})")
    print(SEP)
    for status, cnt in stats:
        pct = cnt/total*100 if total else 0
        flag = '✅' if status=='processed' else ('⚠️ ' if status=='duplicate' else '❌')
        print(f"  {flag} {status:<22} {cnt:>4}  ({pct:.0f}%)")

    # Daily breakdown
    c.execute("""
        SELECT DATE(created_at) as d,
               SUM(processing_status='processed') ok,
               SUM(processing_status NOT IN ('processed','duplicate')) bad,
               COUNT(*) tot
        FROM sasapay_ipn_logs
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
        GROUP BY d ORDER BY d DESC
    """)
    print(f"\n  DAILY BREAKDOWN")
    print(SEP)
    for d, ok, bad, tot in c.fetchall():
        print(f"  {d}  ✅ {int(ok):>3} processed   ❌ {int(bad):>3} failed   ({int(tot)} total)")

    # All failures with FULL notes
    c.execute("""
        SELECT created_at, processing_status, processing_note,
               JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.TransID'))           tid,
               JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.ThirdPartyTransID')) mref,
               JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.TransAmount'))       amt,
               JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.BillRefNumber'))     bref,
               JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.FirstName'))         fn,
               JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.LastName'))          ln
        FROM sasapay_ipn_logs
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
          AND processing_status NOT IN ('processed','duplicate')
        ORDER BY created_at DESC LIMIT 30
    """)
    rows = c.fetchall()
    print(f"\n  ALL FAILURES — last 7 days  ({len(rows)} shown)")
    print(SEP)
    for ts, status, note, tid, mref, amt, bref, fn, ln in rows:
        print(f"\n  {ts}  KES {amt}  {fn} {ln}")
        print(f"  TransID={tid}  MPesa={mref}  BillRef={bref!r}")
        print(f"  Status : {status}")
        print(f"  Note   : {note or '(EMPTY — check server error log)'}")

    # Last 5 successes
    c.execute("""
        SELECT created_at, receipt_number,
               JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.TransAmount')) amt,
               JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.BillRefNumber')) bref,
               JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.FirstName')) fn,
               JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.LastName')) ln
        FROM sasapay_ipn_logs WHERE processing_status='processed'
        ORDER BY created_at DESC LIMIT 5
    """)
    print(f"\n  LAST 5 SUCCESSES")
    print(SEP)
    for ts, rcpt, amt, bref, fn, ln in c.fetchall():
        print(f"  ✅ {ts}  KES {amt}  {fn} {ln}  BillRef={bref!r}  → {rcpt}")

    # Check for server errors by looking at 'pending' stuck rows
    c.execute("""
        SELECT COUNT(*) FROM sasapay_ipn_logs
        WHERE processing_status='pending'
        AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
    """)
    pending = c.fetchone()[0]
    if pending:
        print(f"\n  ⚠️  {pending} stuck in 'pending' — processor crashed mid-run")

    # Check if ADMIN_ALERT_PHONES is set to placeholder
    from django.conf import settings
    phones = getattr(settings, 'ADMIN_ALERT_PHONES', [])
    print(f"\n  ADMIN_ALERT_PHONES = {phones}")
    if '+254700000000' in phones:
        print("  ⚠️  Placeholder number in ADMIN_ALERT_PHONES — change to real number")

print(f"\n{SEP}\n  Done.\n{SEP}")