"""
Checks if the deployed models.py has RCP-001618 blacklisted.
Also checks the latest repayment for UF1016A4I7.
Upload to /home/xygbfpsg/phingrazuri/ and run: python check_deployed_models.py
"""
import sys, os, pymysql, json

DB = dict(host='localhost', user='xygbfpsg_graz',
          password="+MvX9&%PV']]pW}", database='xygbfpsg_loans',
          port=3306, charset='utf8mb4', autocommit=True)

conn = pymysql.connect(**DB)
cur  = conn.cursor()

def q(sql, p=None):
    cur.execute(sql, p or ()); return cur.fetchall()

# 1. Check if RCP-001618 is blacklisted in deployed models.py
print("-- Check deployed models.py blacklist --")
models_path = '/home/xygbfpsg/phingrazuri/loans/models.py'
try:
    with open(models_path) as f:
        content = f.read()
    if 'RCP-001618' in content:
        print("   GOOD: RCP-001618 IS blacklisted in deployed models.py")
    else:
        print("   BAD:  RCP-001618 NOT in deployed models.py blacklist")
        print("   The models.py fix was NOT deployed — this is why payments still fail")
except Exception as e:
    print(f"   Cannot read models.py: {e}")

# 2. Check latest repayment for UF1016A4I7
print("\n-- Check UF1016A4I7 repayment --")
rows = q("SELECT id, receipt_number, loan_id, amount, created_at FROM repayments WHERE mpesa_transaction_id='UF1016A4I7'")
if rows:
    for r in rows:
        loan_id = str(r[2])
        loan = q("SELECT loan_number FROM loans WHERE id=%s", [loan_id])
        print(f"   Found: {r[1]} | KES {r[3]} | loan_id={loan_id[:8]}... | loan={loan[0][0] if loan else 'NOT FOUND'} | {r[4]}")
        if not loan:
            print(f"   PROBLEM: loan_id {loan_id} not found in loans table")
            # Try hex version
            loan_hex = loan_id.replace('-','')
            loan2 = q("SELECT id, loan_number FROM loans WHERE id=%s OR id=%s", [loan_id, loan_hex])
            if loan2:
                print(f"   Found with hex: {loan2[0][1]}")
                # Fix it
                correct_id = str(loan2[0][0])
                cur.execute("UPDATE repayments SET loan_id=%s WHERE mpesa_transaction_id='UF1016A4I7'", [correct_id])
                print(f"   FIXED: loan_id updated to {correct_id}")
else:
    print("   NOT FOUND in repayments table — payment was rolled back")
    print("   The RCP-001618 crash is still happening")

# 3. Check receipts table for RCP-001618
print("\n-- RCP-001618 in receipts --")
rows = q("SELECT id, repayment_id FROM receipts WHERE receipt_number='RCP-001618'")
print(f"   {'EXISTS' if rows else 'MISSING'} in receipts table")

# 4. Total repayments today
print("\n-- Today's repayments --")
rows = q("""
    SELECT r.receipt_number, r.amount, r.mpesa_transaction_id,
           l.loan_number, r.loan_id
    FROM repayments r
    LEFT JOIN loans l ON l.id = r.loan_id
    WHERE DATE(r.created_at) = CURDATE()
    ORDER BY r.created_at DESC
""")
print(f"   Count: {len(rows)}")
for r in rows:
    linked = "OK" if r[3] else "BROKEN-loan_id"
    print(f"   {r[0]} | KES {r[1]} | {r[2]} | {r[3] or 'NULL'} [{linked}]")

cur.close(); conn.close()