"""
Fixes UUID format in today's repayments and receipts.
The nuclear_fix.py inserted IDs as hex (no dashes) but Django expects dashed UUIDs.
Upload to /home/xygbfpsg/phingrazuri/ and run: python fix_uuid_format.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(hex_str):
    """Convert 32-char hex to dashed UUID format."""
    h = str(hex_str).replace('-', '')
    if len(h) == 32:
        return f"{h[0:8]}-{h[8:12]}-{h[12:16]}-{h[16:20]}-{h[20:32]}"
    return hex_str  # already formatted or wrong length

# ── Fix repayments table: IDs inserted as hex need dashes ────────────────────
print("\n-- Checking repayments inserted today --")
today_reps = q("""
    SELECT id, loan_id, mpesa_transaction_id, receipt_number
    FROM repayments
    WHERE DATE(created_at) = CURDATE()
""")
print(f"   Found {len(today_reps)} today")

for rep_id, loan_id, trans_id, receipt_num in today_reps:
    rep_str  = str(rep_id)
    loan_str = str(loan_id) if loan_id else None

    # Check if IDs are missing dashes (32 hex chars, no dashes)
    rep_needs_fix  = '-' not in rep_str  and len(rep_str) == 32
    loan_needs_fix = loan_str and '-' not in loan_str and len(loan_str) == 32

    if rep_needs_fix or loan_needs_fix:
        new_rep_id  = to_uuid(rep_str)
        new_loan_id = to_uuid(loan_str) if loan_str else loan_str

        # Update repayment ID and loan_id
        r = run("UPDATE repayments SET id=%s, loan_id=%s WHERE id=%s",
                [new_rep_id, new_loan_id, rep_str])
        print(f"   Fixed repayment: {rep_str[:8]}... -> {new_rep_id} [{r}]")

        # Fix receipts that reference the old hex repayment_id
        r2 = run("UPDATE receipts SET repayment_id=%s, loan_id=%s WHERE repayment_id=%s",
                 [new_rep_id, new_loan_id, rep_str])
        print(f"   Fixed receipt FK: {r2}")
    else:
        print(f"   OK: {rep_str} (already has dashes)")

# ── Fix receipts table: IDs inserted as hex ──────────────────────────────────
print("\n-- Checking receipts inserted today --")
today_recs = q("""
    SELECT id, repayment_id, loan_id
    FROM receipts
    WHERE DATE(created_at) = CURDATE()
""")
print(f"   Found {len(today_recs)} today")

for rec_id, rep_id, loan_id in today_recs:
    rec_str  = str(rec_id)
    rep_str  = str(rep_id) if rep_id else None
    loan_str = str(loan_id) if loan_id else None

    rec_needs_fix  = '-' not in rec_str  and len(rec_str) == 32
    rep_needs_fix  = rep_str  and '-' not in rep_str  and len(rep_str) == 32
    loan_needs_fix = loan_str and '-' not in loan_str and len(loan_str) == 32

    if rec_needs_fix or rep_needs_fix or loan_needs_fix:
        new_rec_id  = to_uuid(rec_str)
        new_rep_id  = to_uuid(rep_str)  if rep_str  else rep_str
        new_loan_id = to_uuid(loan_str) if loan_str else loan_str

        r = run("UPDATE receipts SET id=%s, repayment_id=%s, loan_id=%s WHERE id=%s",
                [new_rec_id, new_rep_id, new_loan_id, rec_str])
        print(f"   Fixed receipt: {rec_str[:8]}... -> {new_rec_id} [{r}]")
    else:
        print(f"   OK: {rec_str}")

# ── Verify ────────────────────────────────────────────────────────────────────
print("\n-- Verify --")
rows = q("""
    SELECT r.id, r.receipt_number, r.loan_id, l.loan_number
    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
""")
for row in rows:
    print(f"   {row[1]} | loan_id:{str(row[2])[:8]}... | loan:{row[3]}")

cur.close(); conn.close()
print("\nDone. Refresh https://grazuri.uzuriapps.xyz/loans/repayments/")