#!/usr/bin/env python
"""
fix_stephen_duplicate.py
────────────────────────
SasaPay retried IPN SPEJ7R9Y1TYFKGT (KES 1,500) 6 times.
The duplicate guard failed because the repayment's mpesa_transaction_id
was not set, so all 6 created or linked to RCP-002442.

This script:
  1. Shows exactly what's in the DB for this transaction
  2. Checks how many repayment rows exist with that receipt / trans_id
  3. Keeps exactly ONE repayment of KES 1,500 and removes any genuine
     duplicates (same loan, same trans_id, same amount, different id)
  4. Fixes the mpesa_transaction_id on the surviving repayment so the
     duplicate guard works going forward

Set DRY_RUN = True to inspect without changing 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 django.db import connection

DRY_RUN = False   # ← set True first to inspect

TRANS_ID     = 'SPEJ7R9Y1TYFKGT'
RECEIPT      = 'RCP-002442'
LOAN_NUMBER  = 'BSH/202507/000101'
EXPECTED_AMT = 1500.00

SEP = '─' * 68

def run():
    from loans.models import Repayment, Loan
    from utils.models import Receipt

    print()
    print(SEP)
    print('  STEPHEN KIARIE — DUPLICATE IPN AUDIT')
    print(SEP)

    # ── 1. Show all repayments for this loan matching the transaction ──────
    try:
        loan = Loan.objects.get(loan_number=LOAN_NUMBER, is_deleted=False)
    except Loan.DoesNotExist:
        print(f'  ❌  Loan {LOAN_NUMBER} not found.')
        return

    # All repayments on this loan
    all_reps = Repayment.objects.filter(loan=loan).order_by('created_at')
    print(f'  Loan: {loan.loan_number}  borrower: {loan.borrower.get_full_name()}')
    print(f'  All repayments ({all_reps.count()}):')
    for r in all_reps:
        print(f'    id={r.id}  receipt={r.receipt_number}  '
              f'amount={r.amount}  mpesa_ref={r.mpesa_transaction_id!r}  '
              f'date={r.payment_date}')

    # ── 2. Find repayments that are genuine duplicates of this one ─────────
    # Duplicates = same loan, same amount (1500), receipt is RCP-002442
    # OR mpesa_transaction_id matches SPEJ7R9Y1TYFKGT
    dupes = Repayment.objects.filter(
        loan=loan,
        amount=EXPECTED_AMT,
        receipt_number=RECEIPT,
    ).order_by('created_at')

    by_trans = Repayment.objects.filter(
        loan=loan,
        mpesa_transaction_id=TRANS_ID,
    ).order_by('created_at')

    print()
    print(f'  Repayments with receipt={RECEIPT}: {dupes.count()}')
    print(f'  Repayments with trans_id={TRANS_ID}: {by_trans.count()}')

    # The one to KEEP is the earliest created one
    candidates = list(dupes) or list(by_trans)
    if not candidates:
        print('  ✅  No duplicate repayments found — nothing to do.')
        return

    to_keep = candidates[0]
    to_delete = [r for r in candidates if r.id != to_keep.id]

    print()
    print(f'  KEEPING   : {to_keep.id}  receipt={to_keep.receipt_number}  '
          f'amount={to_keep.amount}  created={to_keep.created_at}')
    for r in to_delete:
        print(f'  DELETING  : {r.id}  receipt={r.receipt_number}  '
              f'amount={r.amount}  created={r.created_at}')

    if not to_delete:
        print()
        print('  ✅  Only one repayment exists — no duplicates to remove.')
        print('     Checking mpesa_transaction_id...')
        if not to_keep.mpesa_transaction_id:
            if DRY_RUN:
                print(f'  [DRY RUN] Would set mpesa_transaction_id={TRANS_ID} on {to_keep.id}')
            else:
                Repayment.objects.filter(id=to_keep.id).update(
                    mpesa_transaction_id=TRANS_ID
                )
                print(f'  ✅  Set mpesa_transaction_id={TRANS_ID} on surviving repayment.')
        else:
            print(f'  ✅  mpesa_transaction_id already set: {to_keep.mpesa_transaction_id}')
        _show_loan_balance(loan)
        return

    if DRY_RUN:
        print()
        print(f'  [DRY RUN] Would delete {len(to_delete)} duplicate repayment(s).')
        print('  Set DRY_RUN=False to apply.')
        return

    # ── 3. Delete duplicates and fix the surviving record ─────────────────
    from django.db import transaction as dbtx
    with dbtx.atomic():
        for r in to_delete:
            # Remove linked Receipt row if it's a duplicate too
            try:
                Receipt.objects.filter(repayment=r).delete()
            except Exception:
                pass
            r.delete()
            print(f'  🗑️   Deleted duplicate repayment {r.id}')

        # Fix mpesa_transaction_id on survivor so duplicate guard works
        if not to_keep.mpesa_transaction_id:
            Repayment.objects.filter(id=to_keep.id).update(
                mpesa_transaction_id=TRANS_ID
            )
            print(f'  ✅  Set mpesa_transaction_id={TRANS_ID} on surviving repayment.')

        # Recalculate loan amount_paid cache
        from django.db.models import Sum
        loan_id_str = str(loan.id).replace('-', '')
        with connection.cursor() as cur:
            cur.execute("""
                UPDATE loans SET
                    amount_paid = COALESCE(
                        (SELECT SUM(amount) FROM repayments WHERE loan_id=%s), 0),
                    updated_at = NOW()
                WHERE id = %s
            """, [loan_id_str, loan_id_str])

    print()
    _show_loan_balance(loan)


def _show_loan_balance(loan):
    loan.refresh_from_db()
    from django.db.models import Sum
    total = loan.repayments.aggregate(t=Sum('amount'))['t'] or 0
    print(f'  Loan {loan.loan_number} after fix:')
    print(f'    Repayments sum : KES {total:,.2f}')
    try:
        print(f'    Outstanding    : KES {loan.outstanding_amount:,.2f}')
    except Exception as e:
        print(f'    Outstanding    : ERROR {e}')
    print()
    print(SEP)


if __name__ == '__main__':
    run()