"""
Permanent fix: creates the missing receipt record for RCP-001618
so the receipt generator never tries to reuse it and crash.
Upload to /home/xygbfpsg/phingrazuri/ and run once: python fix_rcp001618_permanent.py
"""
import sys, pymysql, uuid

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()
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

# Get the RCP-001618 repayment details
rep = q("SELECT id, loan_id, amount, payment_method, payment_date FROM repayments WHERE receipt_number='RCP-001618'")
if not rep:
    print("RCP-001618 repayment not found — nothing to do"); sys.exit(0)

rep_id, loan_id, amount, pay_method, pay_date = rep[0]
rep_uuid  = to_uuid(str(rep_id))
loan_uuid = to_uuid(str(loan_id)) if loan_id else None

# Check if receipt already exists
existing = q("SELECT id FROM receipts WHERE receipt_number='RCP-001618'")
if existing:
    print("Receipt RCP-001618 already exists in receipts table — nothing to do")
    sys.exit(0)

# Create the missing receipt record
new_id = str(uuid.uuid4())
r = run("""
    INSERT INTO receipts
        (id, receipt_number, repayment_id, loan_id, amount_paid,
         payment_method, payment_date, previous_balance, new_balance, created_at)
    VALUES (%s, 'RCP-001618', %s, %s, %s, %s, %s, 0.00, 0.00, NOW())
""", [new_id, rep_uuid, loan_uuid, float(amount), pay_method or 'mpesa', pay_date])

if r is True:
    print(f"Created receipt record for RCP-001618 (repayment {rep_uuid}, KES {amount})")
    print("The receipt generator will now skip RCP-001618 permanently.")
else:
    print(f"Failed: {r}")

cur.close(); conn.close()