#!/usr/bin/env python
"""
diagnose_recent_failures.py
────────────────────────────
Shows every IPN received in the last 7 days and exactly what happened to each.
Run on the server: python diagnose_recent_failures.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
from collections import defaultdict

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

def run():
    with connection.cursor() as c:

        # 1. What columns does sasapay_ipn_logs actually have right now?
        c.execute("""
            SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sasapay_ipn_logs'
            ORDER BY ORDINAL_POSITION
        """)
        cols = [r[0] for r in c.fetchall()]
        print(f'\nsasapay_ipn_logs columns: {cols}\n')

        has_status = 'processing_status' in cols
        has_note   = 'processing_note' in cols

        # 2. All IPNs last 7 days
        if has_status:
            c.execute("""
                SELECT created_at,
                       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'))         fname,
                       JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.LastName'))          lname,
                       processing_status, processing_note
                FROM sasapay_ipn_logs
                WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
                ORDER BY created_at DESC
            """)
        else:
            c.execute("""
                SELECT created_at,
                       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'))         fname,
                       JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.LastName'))          lname,
                       NULL, NULL
                FROM sasapay_ipn_logs
                WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
                ORDER BY created_at DESC
            """)
        rows = c.fetchall()

    print(SEP2)
    print(f'  IPNs last 7 days: {len(rows)}')
    print(SEP2)

    status_counts = defaultdict(int)

    for created, tid, mref, amt, bref, fname, lname, status, note in rows:
        # Check if repayment exists for this TransID
        with connection.cursor() as c2:
            c2.execute(
                "SELECT receipt_number FROM repayments "
                "WHERE mpesa_transaction_id = %s LIMIT 1", [tid]
            )
            rep = c2.fetchone()

        has_repayment = bool(rep)
        receipt = rep[0] if rep else None

        if has_repayment:
            outcome = f'✅ repayment={receipt}'
            status_counts['recorded'] += 1
        else:
            outcome = f'❌ NO repayment'
            status_counts['dropped'] += 1

        status_str = status or 'N/A (old column missing)'
        print(f'  {str(created)[:19]}  KES {amt:>8}  {outcome}')
        print(f'    Ref={mref}  BillRef={bref!r}  Name={fname} {lname}')
        if status:
            print(f'    Status: {status_str}')
            if note and note != 'None':
                print(f'    Note  : {note}')

        # If no repayment and status is None/pending — that means the new
        # code crashed before processing and didn't even mark the status
        if not has_repayment and not status:
            print(f'    ⚠️  No processing_status set — crash before Step 2?')
        print()

    print(SEP2)
    print(f'  SUMMARY: {status_counts["recorded"]} recorded, '
          f'{status_counts["dropped"]} dropped')
    print(SEP2)

    # 3. Check if the new sasapay_ipn_logs columns exist properly
    if not has_status:
        print()
        print('  ⚠️  CRITICAL: processing_status column is MISSING from sasapay_ipn_logs')
        print('     This means migration 0007 did not apply the new columns.')
        print('     Run: python create_missing_tables.py')
        print('     Then: DJANGO_SETTINGS_MODULE=branch_system.settings_production '
              'python manage.py migrate payments 0006 --fake && '
              'python manage.py migrate payments')

    # 4. Quick check — can the new code import SasaPayIPNLog.STATUS_PROCESSED?
    try:
        from payments.sasapay_models import SasaPayIPNLog
        _ = SasaPayIPNLog.STATUS_PROCESSED
        print('\n  ✅ SasaPayIPNLog model imports correctly with new fields')
    except AttributeError as e:
        print(f'\n  ❌ SasaPayIPNLog model is missing new attributes: {e}')
        print('     The model code change was not deployed. Reload gunicorn.')
    except Exception as e:
        print(f'\n  ❌ SasaPayIPNLog import error: {e}')

    # 5. Check sasapay_service version on server
    try:
        from payments.sasapay_service import _SASAPAY_SERVICE_VERSION
        print(f'  sasapay_service version: {_SASAPAY_SERVICE_VERSION}')
        if 'v5' not in _SASAPAY_SERVICE_VERSION:
            print('  ⚠️  Server is NOT running the new v5 code — reload gunicorn!')
        else:
            print('  ✅ v5 code is deployed')
    except Exception as e:
        print(f'  Could not check version: {e}')


if __name__ == '__main__':
    run()