#!/usr/bin/env python """ recover_dropped_payments.py ─────────────────────────── Manually creates repayment records for IPN payments that arrived but whose repayment INSERT was silently dropped (transaction rollback bug). Each entry in PAYMENTS_TO_RECOVER is verified before inserting: - Checks the IPN log exists - Checks no repayment already exists for that TransID - Inserts repayment + updates loan cache + creates receipt Run on the server: python recover_dropped_payments.py Set DRY_RUN = True to preview without writing anything. """ import os, sys, django os.environ['DJANGO_SETTINGS_MODULE'] = 'branch_system.settings_production' sys.path.insert(0, os.path.dirname(os.path.abspath(__file__))) django.setup() from decimal import Decimal from datetime import datetime from django.db import connection, transaction as dbtx DRY_RUN = False # ← set True to preview only # ── Payments to recover ──────────────────────────────────────────────────── # Each dict: trans_id, amount, loan_number, payment_date, mpesa_ref PAYMENTS_TO_RECOVER = [ { 'label' : 'Rose Wairimu Njuguna — KES 600 Jun 7', 'trans_id' : 'SPEJ7SB44I2XRV3', 'mpesa_ref' : 'UF7HH6J639', 'amount' : Decimal('600.00'), 'loan_number' : 'BSH/202602/00163', 'phone' : '0727564254', 'payment_date' : datetime(2026, 6, 7, 23, 34, 16), }, { 'label' : 'Issac Kungu Mucheru — KES 500 Jun 9', 'trans_id' : 'SPEJ7SCSKFRFEV3', 'mpesa_ref' : 'UF9P675KDO', 'amount' : Decimal('500.00'), 'loan_number' : 'BSH/202603/00183', 'phone' : '0723957761', 'payment_date' : datetime(2026, 6, 9, 7, 4, 17), }, ] # ────────────────────────────────────────────────────────────────────────── SEP = '─' * 68 def run(): from loans.models import Loan, Repayment from utils.models import Receipt import uuid as _uuid for p in PAYMENTS_TO_RECOVER: print() print(SEP) print(f" {p['label']}") print(SEP) trans_id = p['trans_id'] # ── Guard 1: IPN log must exist ─────────────────────────────────── with connection.cursor() as c: c.execute( "SELECT COUNT(*) FROM sasapay_ipn_logs WHERE raw_data LIKE %s", [f'%{trans_id}%'] ) ipn_exists = c.fetchone()[0] > 0 if not ipn_exists: print(f" ❌ No IPN log found for TransID {trans_id}. Skipping.") continue print(f" ✅ IPN log confirmed for {trans_id}") # ── Guard 2: repayment must NOT already exist ───────────────────── if Repayment.objects.filter(mpesa_transaction_id=trans_id).exists(): rep = Repayment.objects.filter(mpesa_transaction_id=trans_id).first() print(f" ⚠️ Repayment already exists: {rep.receipt_number}. Skipping.") continue print(f" ✅ No existing repayment — safe to create") # ── Fetch loan ──────────────────────────────────────────────────── try: loan = Loan.objects.get(loan_number=p['loan_number'], is_deleted=False) except Loan.DoesNotExist: print(f" ❌ Loan {p['loan_number']} not found. Skipping.") continue borrower = loan.borrower print(f" Loan : {loan.loan_number} borrower={borrower.get_full_name()}") print(f" Amount : KES {p['amount']:,.2f}") print(f" Date : {p['payment_date']}") if DRY_RUN: print(" [DRY RUN] Would create repayment — no changes made.") continue # ── Pick receipt number under advisory lock ─────────────────────── LOCK_NAME = 'havengrazuri_ipn_receipt_lock' BLACKLISTED = {1618, 2440} receipt_num = None with connection.cursor() as cur: cur.execute("SELECT GET_LOCK(%s, 10)", [LOCK_NAME]) if not cur.fetchone()[0]: print(" ❌ Could not acquire receipt lock. Skipping.") continue try: cur.execute( "SELECT MAX(CAST(SUBSTRING(receipt_number,5) AS UNSIGNED)) " "FROM repayments WHERE receipt_number REGEXP '^RCP-[0-9]+$'" ) max_rep = cur.fetchone()[0] or 0 cur.execute( "SELECT MAX(CAST(SUBSTRING(receipt_number,5) AS UNSIGNED)) " "FROM receipts WHERE receipt_number REGEXP '^RCP-[0-9]+$'" ) max_rec = cur.fetchone()[0] or 0 next_n = max(max_rep, max_rec) + 1 while next_n in BLACKLISTED: next_n += 1 receipt_num = f"RCP-{next_n:06d}" finally: cur.execute("SELECT RELEASE_LOCK(%s)", [LOCK_NAME]) print(f" Receipt : {receipt_num}") # ── Insert repayment (raw SQL, same approach as IPN service) ─────── rep_id = _uuid.uuid4().hex loan_id_str = str(loan.id).replace('-', '') norm_phone = _normalise_phone(p['phone']) try: with dbtx.atomic(): with connection.cursor() as cur: cur.execute(""" 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_id_str, float(p['amount']), trans_id, norm_phone, receipt_num, p['payment_date']]) cur.execute(""" UPDATE loans SET amount_paid = COALESCE( (SELECT SUM(amount) FROM repayments WHERE loan_id=%s), 0), last_payment_date = %s, updated_at = NOW() WHERE id = %s """, [loan_id_str, p['payment_date'], loan_id_str]) print(f" ✅ Repayment {receipt_num} created and loan cache updated.") except Exception as e: print(f" ❌ Failed to insert repayment: {e}") continue # ── Create Receipt row (isolated — failure here is non-fatal) ────── try: from loans.models import Repayment as _Rep from django.db.models import Sum rep_obj = _Rep.objects.get(pk=rep_id) loan_obj = Loan.objects.get(pk=loan.id) if not Receipt.objects.filter(repayment=rep_obj).exists(): prev_paid = _Rep.objects.filter( loan_id=loan.id, payment_date__lt=p['payment_date'], ).exclude(pk=rep_id).aggregate(t=Sum('amount'))['t'] or Decimal('0') Receipt.objects.create( repayment = rep_obj, loan = loan_obj, borrower = borrower, receipt_number = receipt_num, amount_paid = p['amount'], payment_method = 'mpesa', payment_date = p['payment_date'], previous_balance = loan_obj.principal_amount - prev_paid, new_balance = loan_obj.principal_amount - prev_paid - p['amount'], ) print(f" ✅ Receipt row created.") except Exception as e: print(f" ⚠️ Receipt creation failed (repayment IS saved): {e}") print() print(SEP) if DRY_RUN: print(" DRY RUN complete — no changes were written.") else: print(" Recovery complete.") print(SEP) def _normalise_phone(phone): p = (phone or '').strip().lstrip('+') if p.startswith('0'): p = '254' + p[1:] return p[:17] if __name__ == '__main__': run()