"""
Check if FAITH's payment (UF1O16E915 / BSH/202602/00173) exists on the server
Run on server: python check_faith_server.py
"""
import django
import os
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

from django.db import connection
from loans.models import Repayment
from users.models import CustomUser

c = connection.cursor()

print("=" * 70)
print("CHECKING FAITH PAYMENT ON SERVER")
print("=" * 70)

# Check 1: Find repayment by transaction ID
print("\n1. Looking for repayment with TransID = UF1O16E915...")
c.execute("""
    SELECT r.id, r.receipt_number, r.amount, r.payment_date, r.mpesa_transaction_id,
           l.loan_number, l.borrower_id,
           u.first_name, u.last_name, u.phone_number, u.branch_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 r.mpesa_transaction_id LIKE '%UF1O16E915%'
    OR r.mpesa_transaction_id LIKE '%1O16E915%'
""")
rows = c.fetchall()
if rows:
    print(f"  ✓ Found {len(rows)} repayment(s):")
    for r in rows:
        print(f"    Receipt: {r[1]}, Amount: {r[2]}, Loan: {r[5]}, Borrower: {r[7]} {r[8]}, Branch: {r[10]}")
else:
    print("  ✗ NOT FOUND")

# Check 2: Find by loan number
print("\n2. Looking for loan BSH/202602/00173...")
c.execute("""
    SELECT id, loan_number, borrower_id, status
    FROM loans
    WHERE loan_number = 'BSH/202602/00173'
""")
rows = c.fetchall()
if rows:
    print(f"  ✓ Loan found: {rows[0]}")
    loan_id = rows[0][0]
    
    # Check repayments for this loan
    c.execute("""
        SELECT receipt_number, amount, payment_date, mpesa_transaction_id
        FROM repayments
        WHERE loan_id = %s
        ORDER BY payment_date DESC
        LIMIT 10
    """, [loan_id])
    reps = c.fetchall()
    print(f"  Repayments for this loan: {len(reps)}")
    for r in reps:
        print(f"    {r[0]} - {r[1]} - {r[2]} - TransID: {r[3]}")
else:
    print("  ✗ Loan NOT FOUND on server")

# Check 3: Find FAITH borrower
print("\n3. Looking for FAITH borrower...")
c.execute("""
    SELECT id, first_name, last_name, phone_number, branch_id
    FROM users
    WHERE (first_name = 'FAITH' OR last_name = 'FAITH')
    AND role = 'borrower'
""")
rows = c.fetchall()
if rows:
    print(f"  ✓ Found {len(rows)} borrower(s) named FAITH:")
    for r in rows:
        print(f"    ID: {r[0]}, Name: {r[1]} {r[2]}, Phone: {r[3]}, Branch: {r[4]}")
        
        # Get their loans
        c.execute("""
            SELECT loan_number, status
            FROM loans
            WHERE borrower_id = %s
            ORDER BY created_at DESC
            LIMIT 5
        """, [r[0]])
        loans = c.fetchall()
        print(f"    Loans: {len(loans)}")
        for loan in loans:
            print(f"      - {loan[0]} ({loan[1]})")
else:
    print("  ✗ FAITH borrower NOT FOUND")

# Check 4: Recent payments on 2026-06-01 around 21:07
print("\n4. All repayments on 2026-06-01 between 20:00 and 22: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'
    AND r.payment_date <= '2026-06-01 22:00:00'
    ORDER BY r.payment_date DESC
""")
rows = c.fetchall()
print(f"  Found {len(rows)} repayments in that window:")
for r in rows:
    print(f"    {r[2]} - {r[4]} {r[5]} - {r[6]} - {r[0]} - {r[1]} - TransID: {r[3]}")

# Check 5: IPN logs for UF1O16E915
print("\n5. IPN logs containing UF1O16E915...")
c.execute("""
    SELECT created_at, raw_data
    FROM sasapay_ipn_logs
    WHERE raw_data LIKE '%UF1O16E915%'
    OR raw_data LIKE '%1O16E915%'
    ORDER BY created_at DESC
    LIMIT 5
""")
rows = c.fetchall()
if rows:
    import json
    print(f"  ✓ Found {len(rows)} IPN log(s):")
    for r in rows:
        d = json.loads(r[1]) if isinstance(r[1], str) else r[1]
        print(f"    [{r[0]}] TransID={d.get('TransID')}, BillRef={d.get('BillRefNumber')}, Amount={d.get('TransAmount')}")
else:
    print("  ✗ NO IPN LOG — payment never hit /payments/sasapay/ipn/")

# Check 6: Total repayments count vs what ORM returns
print("\n6. Repayments count check...")
c.execute("SELECT COUNT(*) FROM repayments")
raw_count = c.fetchone()[0]
orm_count = Repayment.objects.count()
print(f"  Raw SQL count: {raw_count}")
print(f"  ORM count: {orm_count}")
if raw_count != orm_count:
    print(f"  ⚠ MISMATCH: {raw_count - orm_count} repayments hidden by ORM")

print("\n" + "=" * 70)
print("If repayment doesn't exist:")
print("  - SasaPay is sending to old PHP URL, not /payments/sasapay/ipn/")
print("  - Or loan BSH/202602/00173 doesn't exist on this server")
print("=" * 70)