"""
Diagnose why FAITH's payment SMS was sent but repayment isn't on the repayments page.

Usage:
    python manage.py diagnose_faith
"""
from django.core.management.base import BaseCommand
from django.db import connection
from django.db.models import Q
import json


class Command(BaseCommand):
    help = 'Diagnose FAITH payment UF1O16E915 / BSH/202602/00173'

    def handle(self, *args, **options):
        w = self.stdout.write
        c = connection.cursor()

        w('=' * 70)
        w('FAITH PAYMENT DIAGNOSIS')
        w('SMS: UF1O16E915 | KES 1,500 | BSH/202602/00173 | 01/06/2026 21:07')
        w('=' * 70)

        # ------------------------------------------------------------------
        # 1. SMS log
        # ------------------------------------------------------------------
        w('\n[1] SMS LOG — was SMS sent from Django sms_service?')
        c.execute("""
            SELECT sms_type, borrower_name, loan_number, amount, status, created_at,
                   SUBSTR(message, 1, 160) AS msg
            FROM sms_logs
            WHERE message LIKE %s
              AND created_at >= '2026-06-01 20:00:00'
              AND created_at <= '2026-06-01 23:59:00'
            ORDER BY created_at DESC
            LIMIT 10
        """, ['%FAITH%'])
        rows = c.fetchall()
        if rows:
            for r in rows:
                w(f"  [{r[5]}] type={r[0]} status={r[4]} loan={r[2]} amount={r[3]}")
                w(f"    {r[6]}")
        else:
            w('  NOT FOUND by FAITH name — trying by TransID...')
            c.execute("""
                SELECT sms_type, borrower_name, loan_number, amount, status, created_at
                FROM sms_logs
                WHERE message LIKE %s
                LIMIT 5
            """, ['%UF1O16E915%'])
            rows2 = c.fetchall()
            if rows2:
                for r in rows2:
                    w(f"  FOUND by TransID: [{r[5]}] {r[0]} | {r[1]} | {r[2]} | {r[3]} | {r[4]}")
            else:
                w('  NOT FOUND at all → SMS may have come from old PHP system, not Django')

        # ------------------------------------------------------------------
        # 2. IPN log
        # ------------------------------------------------------------------
        w('\n[2] SASAPAY IPN LOG — did SasaPay call /payments/sasapay/ipn/?')
        c.execute("""
            SELECT id, created_at, raw_data
            FROM sasapay_ipn_logs
            WHERE created_at >= '2026-06-01 20:00:00'
              AND created_at <= '2026-06-01 23:59:00'
            ORDER BY created_at ASC
        """)
        rows = c.fetchall()
        if rows:
            w(f'  {len(rows)} IPN(s) received in window 20:00–23:59:')
            for ipn_id, created, raw in rows:
                try:
                    d = json.loads(raw) if isinstance(raw, str) else raw
                    tid  = d.get('TransID', '')
                    ref  = d.get('ThirdPartyTransID', '')
                    bill = d.get('BillRefNumber', '')
                    amt  = d.get('TransAmount', '')
                    fn   = d.get('FirstName', '')
                    ln   = d.get('LastName', '')
                    flag = ' <<< FAITH?' if (
                        'UF1O16E915' in f'{tid}{ref}' or
                        'FAITH' in f'{fn}{ln}'.upper() or
                        str(amt) == '1500'
                    ) else ''
                    w(f'  [{created}] TransID={tid} | 3rdParty={ref} | BillRef={bill} | KES {amt} | {fn} {ln}{flag}')
                except Exception as e:
                    w(f'  Error parsing IPN {ipn_id}: {e}')
        else:
            w('  NO IPNs received in that window')
            w('  → SasaPay is NOT calling /payments/sasapay/ipn/ for these payments')
            w('  → Payments are going to the OLD PHP system')

        # ------------------------------------------------------------------
        # 3. Repayment in DB
        # ------------------------------------------------------------------
        w('\n[3] REPAYMENT RECORD — is it in the database?')
        found = False
        for where, label, params in [
            ("mpesa_transaction_id LIKE %s", "TransID contains UF1O16E915", ['%UF1O16E915%']),
            ("amount = 1500 AND payment_date >= '2026-06-01 20:00'", "KES 1500 after 20:00", []),
        ]:
            c.execute(f"""
                SELECT r.receipt_number, r.amount, r.payment_date,
                       r.mpesa_transaction_id, r.payment_source, r.created_at,
                       l.loan_number, u.first_name, u.last_name,
                       u.branch_id, l.application_id
                FROM repayments r
                LEFT JOIN loans l ON l.id = r.loan_id
                LEFT JOIN users u ON u.id = l.borrower_id
                WHERE {where}
            """, params)
            rows = c.fetchall()
            if rows:
                found = True
                w(f'  FOUND via [{label}]:')
                for r in rows:
                    w(f'    Receipt={r[0]} | KES {r[1]} | {r[2]} | TransID={r[3]}')
                    w(f'    Source={r[4]} | Created={r[5]}')
                    w(f'    Loan={r[6]} | Borrower={r[7]} {r[8]}')
                    w(f'    Branch={r[9]} | ApplicationID={r[10]}')
                    if r[9] is None:
                        w('    !! Branch is NULL — may be hidden by branch filter')
                    if r[10] is None:
                        w('    !! ApplicationID is NULL — INNER JOIN will drop this row in ORM')
        if not found:
            w('  NOT IN DATABASE — repayment was never saved')
            w('  SMS sent successfully but INSERT was never committed')

        # ------------------------------------------------------------------
        # 4. Loan exists?
        # ------------------------------------------------------------------
        w('\n[4] LOAN BSH/202602/00173')
        c.execute("""
            SELECT l.id, l.loan_number, l.status, l.application_id,
                   u.first_name, u.last_name, u.phone_number, u.branch_id
            FROM loans l
            LEFT JOIN users u ON u.id = l.borrower_id
            WHERE l.loan_number = 'BSH/202602/00173'
        """)
        rows = c.fetchall()
        if rows:
            r = rows[0]
            w(f'  FOUND: {r[4]} {r[5]} | phone={r[6]} | status={r[2]} | branch={r[7]}')
            w(f'  application_id={r[3]}')
            if r[3]:
                c.execute('SELECT id FROM loan_applications WHERE id = %s', [r[3]])
                app = c.fetchone()
                if not app:
                    w(f'  !! Application {r[3]} MISSING → ORM select_related will drop repayments via INNER JOIN')
            # Any repayments?
            c.execute("SELECT receipt_number, amount, payment_date FROM repayments WHERE loan_id = %s ORDER BY payment_date DESC LIMIT 5", [r[0]])
            reps = c.fetchall()
            w(f'  Repayments on this loan: {len(reps)}')
            for rep in reps:
                w(f'    {rep[0]} | KES {rep[1]} | {rep[2]}')
        else:
            w('  NOT FOUND on this server')

        # ------------------------------------------------------------------
        # 5. Borrower FAITH exists?
        # ------------------------------------------------------------------
        w('\n[5] BORROWER FAITH')
        c.execute("""
            SELECT id, first_name, last_name, phone_number, branch_id, is_active
            FROM users
            WHERE (first_name LIKE %s OR last_name LIKE %s)
              AND role = 'borrower'
        """, ['%FAITH%', '%FAITH%'])
        rows = c.fetchall()
        if rows:
            for r in rows:
                w(f'  FOUND: {r[1]} {r[2]} | phone={r[3]} | branch={r[4]} | active={r[5]}')
        else:
            w('  NOT FOUND on this server')

        # ------------------------------------------------------------------
        # 6. Raw vs ORM counts
        # ------------------------------------------------------------------
        w('\n[6] RAW vs ORM REPAYMENT COUNTS')
        from loans.models import Repayment
        c.execute('SELECT COUNT(*) FROM repayments')
        raw_n = c.fetchone()[0]
        orm_n = Repayment.objects.count()
        orm_j = Repayment.objects.select_related(
            'loan', 'loan__borrower', 'loan__application__loan_product'
        ).count()
        w(f'  Raw SQL:                   {raw_n}')
        w(f'  ORM (no joins):            {orm_n}')
        w(f'  ORM (with select_related): {orm_j}')
        if raw_n != orm_j:
            w(f'  !! {raw_n - orm_j} rows hidden by INNER JOIN on loan_applications')

        # ------------------------------------------------------------------
        # 7. Repayments after 20:00 on 01/06/2026
        # ------------------------------------------------------------------
        w('\n[7] ALL REPAYMENTS ON 2026-06-01 AFTER 20:00')
        c.execute("""
            SELECT r.receipt_number, r.amount, r.payment_date,
                   r.mpesa_transaction_id, u.first_name, u.last_name, l.loan_number
            FROM repayments r
            LEFT JOIN loans l ON l.id = r.loan_id
            LEFT JOIN users u ON u.id = l.borrower_id
            WHERE r.payment_date >= '2026-06-01 20:00:00'
            ORDER BY r.payment_date DESC
            LIMIT 20
        """)
        rows = c.fetchall()
        if rows:
            w(f'  Found {len(rows)}:')
            for r in rows:
                w(f'  {r[2]} | {r[4]} {r[5]} | {r[6]} | {r[0]} | KES {r[1]} | TransID={r[3]}')
        else:
            w('  NONE — confirms payments after 20:00 are not being saved')

        w('\n' + '=' * 70)
        w('DONE — paste full output for analysis')
        w('=' * 70)