"""
Fixes today's repayments so they appear in the repayments page.
The repayments were created but their receipt creation failed/rolled back.
This script:
1. Finds all today's repayments that have no receipt or duplicate receipt
2. Assigns them correct unique receipt numbers
3. Creates the receipt records

Upload to /home/xygbfpsg/phingrazuri/ and run: python fix_todays_repayments.py
"""
import sys, os, pymysql, uuid
from datetime import datetime

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 to production DB")
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)

# ── 1. Find the highest existing receipt number ───────────────────────────────
print("\n-- Finding highest receipt number --")
rows = q("""
    SELECT receipt_number FROM repayments
    WHERE receipt_number LIKE 'RCP-______'
    AND receipt_number NOT IN ('RCP-001618', 'RCP-002440')
    ORDER BY receipt_number DESC LIMIT 1
""")
if rows:
    try:
        max_num = int(rows[0][0].split('-')[1])
    except:
        max_num = 2440
else:
    max_num = 2440
print(f"   Highest existing: RCP-{max_num:06d}")

def next_receipt():
    global max_num
    max_num += 1
    # Skip blacklisted
    while max_num in (1618, 2440):
        max_num += 1
    return f"RCP-{max_num:06d}"

# ── 2. Find today's repayments with bad/duplicate receipts ───────────────────
print("\n-- Finding today's repayments --")
today_reps = q("""
    SELECT r.id, r.receipt_number, r.mpesa_transaction_id, r.loan_id,
           r.amount, r.payment_date, r.payment_method
    FROM repayments r
    WHERE DATE(r.created_at) = CURDATE()
    ORDER BY r.created_at ASC
""")
print(f"   Found {len(today_reps)} repayments created today")

# ── 3. Fix each one ───────────────────────────────────────────────────────────
print("\n-- Fixing repayments --")
fixed = 0
for rep_id, receipt_num, trans_id, loan_id, amount, pay_date, pay_method in today_reps:
    rep_hex  = str(rep_id).replace('-', '')
    loan_hex = str(loan_id).replace('-', '') if loan_id else None

    # Check if receipt exists in receipts table
    in_receipts = q("SELECT id FROM receipts WHERE repayment_id=%s", [rep_hex])

    # Check if receipt_number is bad (duplicate or blacklisted)
    is_bad_receipt = receipt_num in ('RCP-001618', 'RCP-002440', None, '')

    # Count how many repayments share this receipt number
    if receipt_num:
        sharing = q("SELECT COUNT(*) FROM repayments WHERE receipt_number=%s", [receipt_num])[0][0]
        is_duplicate = sharing > 1
    else:
        is_duplicate = False

    if not in_receipts or is_bad_receipt or is_duplicate:
        # Assign a new unique receipt number
        new_receipt = next_receipt()
        # Make sure it's truly unique
        while q("SELECT 1 FROM repayments WHERE receipt_number=%s", [new_receipt]) or \
              q("SELECT 1 FROM receipts WHERE receipt_number=%s", [new_receipt]):
            new_receipt = next_receipt()

        # Update the repayment's receipt number
        r = run("UPDATE repayments SET receipt_number=%s WHERE id=%s",
                [new_receipt, rep_hex])

        # Delete old bad receipt if exists
        if in_receipts:
            run("DELETE FROM receipts WHERE repayment_id=%s", [rep_hex])

        # Create new receipt record
        new_id = uuid.uuid4().hex
        r2 = 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, %s, %s, %s, %s, %s, %s, 0.00, 0.00, NOW())
        """, [new_id, new_receipt, rep_hex, loan_hex,
              float(amount), pay_method or 'mpesa', pay_date])

        status = "OK" if (r is True and r2 is True) else f"WARN: {r} / {r2}"
        print(f"   {trans_id or rep_id} -> {new_receipt} [{status}]")
        fixed += 1
    else:
        print(f"   {trans_id or rep_id} -> {receipt_num} [already OK]")

print(f"\n   Fixed {fixed} repayments")

# ── 4. Verify ─────────────────────────────────────────────────────────────────
print("\n-- Verification --")
today_count = q("SELECT COUNT(*) FROM repayments WHERE DATE(created_at)=CURDATE()")[0][0]
total_count = q("SELECT COUNT(*) FROM repayments")[0][0]
print(f"   Today's repayments : {today_count}")
print(f"   Total repayments   : {total_count}")

# Show today's repayments with their loan numbers
print("\n-- Today's repayments (should appear in UI) --")
rows = q("""
    SELECT r.receipt_number, r.amount, r.mpesa_transaction_id,
           l.loan_number, r.payment_date
    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[0]} | KES {row[1]} | {row[2]} | {row[3]} | {row[4]}")

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