"""
1. Fixes the loan_id FK on today's repayments (UUID format mismatch)
2. Permanently removes RCP-001618 from receipts so it never blocks again
3. Verifies the repayments page will work for future payments

Upload to /home/xygbfpsg/phingrazuri/ and run: python fix_loan_fk.py
"""
import sys, pymysql

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

try:
    conn = pymysql.connect(**DB)
    cur  = conn.cursor()
    print("Connected")
except Exception as e:
    print(f"FAIL: {e}"); sys.exit(1)

def q(sql, p=None):
    cur.execute(sql, p or ()); return cur.fetchall()
def run(sql, p=None):
    try: cur.execute(sql, p or ()); return True
    except Exception as e: return str(e)

def to_uuid(h):
    h = str(h).replace('-','')
    return f"{h[0:8]}-{h[8:12]}-{h[12:16]}-{h[16:20]}-{h[20:32]}" if len(h)==32 else h

# ── 1. Fix today's repayments: match loan_id to actual loans.id format ────────
print("\n-- Fix loan_id FK on today's repayments --")
today = q("""
    SELECT r.id, r.loan_id, r.receipt_number
    FROM repayments r
    WHERE DATE(r.created_at) = CURDATE()
""")

for rep_id, loan_id, receipt_num in today:
    loan_id_str = str(loan_id)
    # Find the actual loan ID in the loans table (try both formats)
    loan_hex = loan_id_str.replace('-','')
    loan_dashed = to_uuid(loan_hex)

    # Check which format exists in loans table
    exists_dashed = q("SELECT id FROM loans WHERE id=%s LIMIT 1", [loan_dashed])
    exists_hex    = q("SELECT id FROM loans WHERE id=%s LIMIT 1", [loan_hex])

    if exists_dashed:
        correct_id = loan_dashed
    elif exists_hex:
        correct_id = loan_hex
    else:
        print(f"   WARN: loan {loan_id_str} not found in loans table for {receipt_num}")
        continue

    # Update repayment with correct loan_id
    r = run("UPDATE repayments SET loan_id=%s WHERE id=%s", [correct_id, str(rep_id)])
    # Update receipt too
    run("UPDATE receipts SET loan_id=%s WHERE repayment_id=%s", [correct_id, str(rep_id)])
    print(f"   {receipt_num}: loan_id -> {correct_id[:8]}... [{r}]")

# ── 2. Permanently remove RCP-001618 from receipts ────────────────────────────
print("\n-- Permanently remove RCP-001618 from receipts --")
# Check what repayment it belongs to
rows = q("SELECT id, repayment_id, loan_id FROM receipts WHERE receipt_number='RCP-001618'")
if rows:
    for row in rows:
        # Only delete if the repayment itself is NOT from today (old orphan)
        rep_id = row[1]
        if rep_id:
            rep_exists = q("SELECT id, created_at FROM repayments WHERE id=%s", [rep_id])
            if rep_exists:
                print(f"   RCP-001618 belongs to repayment {rep_id} created {rep_exists[0][1]}")
                print(f"   Keeping — it has a valid repayment")
            else:
                r = run("DELETE FROM receipts WHERE receipt_number='RCP-001618'")
                print(f"   Deleted orphan RCP-001618 [{r}]")
        else:
            r = run("DELETE FROM receipts WHERE receipt_number='RCP-001618'")
            print(f"   Deleted null-repayment RCP-001618 [{r}]")
else:
    print("   RCP-001618 not in receipts — good")

# ── 3. Verify today's repayments are fully linked ─────────────────────────────
print("\n-- Today's repayments --")
rows = q("""
    SELECT r.receipt_number, r.amount, r.mpesa_transaction_id,
           l.loan_number, u.first_name, u.last_name,
           r.loan_id, l.id as actual_loan_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 DATE(r.created_at) = CURDATE()
    ORDER BY r.created_at DESC
""")
for row in rows:
    linked = "OK" if row[7] else "BROKEN"
    print(f"   {row[0]} | KES {row[1]} | {row[3]} | {row[4]} {row[5]} [{linked}]")

# ── 4. Check what RCP-001618 repayment looks like ─────────────────────────────
print("\n-- RCP-001618 repayment --")
rows = q("SELECT id, loan_id, amount, payment_date, created_at FROM repayments WHERE receipt_number='RCP-001618'")
for row in rows:
    print(f"   id={row[0]}, loan_id={row[1]}, amount={row[2]}, date={row[3]}, created={row[4]}")

cur.close(); conn.close()
print("\nDone.")