"""
Direct fix: removes RCP-001618 from receipts table so it stops blocking all repayments.
Then processes today's payments using direct SQL — no Django ORM, no atomic blocks.

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

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)

# ── STEP 1: Nuke RCP-001618 from receipts ────────────────────────────────────
print("\n-- STEP 1: Remove RCP-001618 from receipts table --")
rows = q("SELECT id, repayment_id FROM receipts WHERE receipt_number='RCP-001618'")
print(f"   Found {len(rows)} receipt(s) with RCP-001618")
for row in rows:
    r = run("DELETE FROM receipts WHERE receipt_number='RCP-001618'")
    print(f"   Deleted: {r}")

# Also nuke RCP-002440 duplicates (keep only 1 if it has a valid repayment)
rows_2440 = q("SELECT id, repayment_id FROM receipts WHERE receipt_number='RCP-002440'")
print(f"   Found {len(rows_2440)} receipt(s) with RCP-002440")
if len(rows_2440) > 1:
    for row in rows_2440[1:]:
        run("DELETE FROM receipts WHERE id=%s", [row[0]])
    print(f"   Kept 1, deleted {len(rows_2440)-1} duplicates")

# ── STEP 2: Find highest receipt number ──────────────────────────────────────
print("\n-- STEP 2: Find next receipt number --")
rows = q("""
    SELECT receipt_number FROM repayments
    WHERE receipt_number REGEXP '^RCP-[0-9]{6}$'
    AND receipt_number NOT IN ('RCP-001618','RCP-002440')
    ORDER BY CAST(SUBSTRING(receipt_number,5) AS UNSIGNED) DESC LIMIT 1
""")
max_num = int(rows[0][0].split('-')[1]) if rows else 2439
print(f"   Current max: RCP-{max_num:06d}")

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

# ── STEP 3: Get today's unique IPN payloads ───────────────────────────────────
print("\n-- STEP 3: Get today's IPN payloads --")
ipn_rows = q("""
    SELECT raw_data FROM sasapay_ipn_logs
    WHERE DATE(created_at) = CURDATE()
    ORDER BY created_at ASC
""")

seen = {}
for row in ipn_rows:
    data = row[0] if isinstance(row[0], dict) else json.loads(row[0])
    tid  = data.get('TransID', '')
    if tid and tid not in seen:
        seen[tid] = data
print(f"   {len(seen)} unique payments today")

# ── STEP 4: For each payment, find borrower+loan and insert repayment directly
print("\n-- STEP 4: Insert repayments directly via SQL --")

for trans_id, data in seen.items():
    amount   = float(data.get('TransAmount', 0))
    bill_ref = data.get('BillRefNumber', '')
    msisdn   = data.get('MSISDN', '')
    mpesa_ref = data.get('ThirdPartyTransID', trans_id)
    fname    = data.get('FirstName', '')
    lname    = data.get('LastName', '')

    # Check if already properly saved
    existing = q("SELECT id, receipt_number, loan_id FROM repayments WHERE mpesa_transaction_id=%s", [trans_id])
    if existing:
        rn = existing[0][1]
        if rn and rn not in ('RCP-001618', 'RCP-002440', ''):
            print(f"   SKIP {trans_id} -> already has {rn}")
            continue
        else:
            # Delete bad repayment
            for e in existing:
                run("DELETE FROM receipts WHERE repayment_id=%s", [str(e[0]).replace('-','')])
                run("DELETE FROM repayments WHERE id=%s", [str(e[0]).replace('-','')])

    # Find borrower by phone
    phone = str(bill_ref).strip()
    norm  = ('+254'+phone[1:]) if phone.startswith('0') else \
            ('+'+phone) if phone.startswith('254') else phone

    user = q("SELECT id FROM users WHERE phone_number IN (%s,%s) AND role='borrower' LIMIT 1",
             [norm, phone])
    if not user:
        # Try MSISDN
        ms = str(msisdn).strip()
        ms_norm = ('+254'+ms[1:]) if ms.startswith('0') else ('+'+ms) if ms.startswith('254') else ms
        user = q("SELECT id FROM users WHERE phone_number IN (%s,%s) AND role='borrower' LIMIT 1",
                 [ms_norm, ms])
    if not user:
        print(f"   SKIP {trans_id} -> no borrower for phone {phone}")
        continue

    user_hex = str(user[0][0]).replace('-','')

    # Find active loan for this borrower (prefer GRZ-* or BSH-* loans)
    loan = q("""
        SELECT id, loan_number, total_amount FROM loans
        WHERE borrower_id=%s AND status='active'
        AND loan_number NOT LIKE 'TEST-%%'
        AND loan_number NOT LIKE 'LN-%%'
        ORDER BY created_at DESC LIMIT 1
    """, [user_hex])
    if not loan:
        loan = q("""
            SELECT id, loan_number, total_amount FROM loans
            WHERE borrower_id=%s AND status='active'
            ORDER BY created_at DESC LIMIT 1
        """, [user_hex])
    if not loan:
        print(f"   SKIP {trans_id} -> no active loan for borrower {user_hex}")
        continue

    loan_hex    = str(loan[0][0]).replace('-','')
    loan_number = loan[0][1]

    # Generate receipt number
    receipt_num = next_rcp()
    while q("SELECT 1 FROM repayments WHERE receipt_number=%s", [receipt_num]) or \
          q("SELECT 1 FROM receipts WHERE receipt_number=%s", [receipt_num]):
        receipt_num = next_rcp()

    # Insert repayment
    rep_id = uuid.uuid4().hex
    pay_date = datetime.now()
    r1 = run("""
        INSERT INTO repayments
            (id, loan_id, amount, payment_method, payment_source,
             mpesa_transaction_id, mpesa_phone_number,
             receipt_number, payment_date, created_at)
        VALUES (%s, %s, %s, 'mpesa', 'automatic', %s, %s, %s, %s, NOW())
    """, [rep_id, loan_hex, amount, trans_id,
          norm[:17], receipt_num, pay_date])

    if r1 is not True:
        print(f"   FAIL {trans_id} -> repayment insert: {r1}")
        continue

    # Insert receipt
    rec_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, 'mpesa', %s, 0.00, 0.00, NOW())
    """, [rec_id, receipt_num, rep_id, loan_hex, amount, pay_date])

    status = "OK" if r2 is True else f"receipt warn: {r2}"
    print(f"   DONE {trans_id} -> {receipt_num} | {loan_number} | KES {amount} [{status}]")

# ── STEP 5: Verify ────────────────────────────────────────────────────────────
print("\n-- STEP 5: Verify --")
today = q("SELECT COUNT(*) FROM repayments WHERE DATE(created_at)=CURDATE()")[0][0]
total = q("SELECT COUNT(*) FROM repayments")[0][0]
print(f"   Today's repayments : {today}")
print(f"   Total repayments   : {total}")

rows = q("""
    SELECT r.receipt_number, r.amount, r.mpesa_transaction_id,
           l.loan_number, u.first_name, u.last_name
    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:
    print(f"   {row[0]} | KES {row[1]} | {row[2]} | {row[3]} | {row[4]} {row[5]}")

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