# Excess Payment Total Repaid Update

## Purpose

This update ensures that an M-Pesa payment greater than the loan amount payable is fully reflected in `Total Repaid`, while the excess amount continues to be credited to the user's guarantee deposit.

Example:

- Total Payable: KSh 12,000.00
- M-Pesa payment: KSh 12,500.00
- Total Repaid: KSh 12,500.00
- Guarantee deposit credited: KSh 500.00

The update applies to both normal loans and legacy-imported loans.

## Application Files Updated

1. `app/Models/Loan.php`
   - `total_repaid` now adds completed payment transactions specifically recorded for guarantee-deposit excess amounts.
   - Legacy loans continue to use `legacy_paid` as their authoritative repaid total.
   - No database schema changes were introduced.

2. `app/Services/RepaymentService.php`
   - Keeps the existing installment rollover behavior.
   - Continues adding any remaining payment amount to `users.guarantee_deposit_balance`.
   - For normal loans, records the excess using the existing `transactions` table with the reference `Guarantee deposit excess payment`.
   - For legacy loans, adds the excess to `legacy_paid`.
   - Attributes an excess payment to the last loan that actually received an installment allocation when a user has multiple active loans.

## One-Time Data Correction

The affected historical payments were identified from completed payment transactions by comparing the M-Pesa callback amount with the amount allocated to repayments. All ten affected loans were legacy-imported loans.

The one-time correction used XAMPP PHP and Laravel Tinker against the live MariaDB database. It updated only the existing `loans.legacy_paid` column for the verified excess amounts. No migration, table alteration, deletion, or data conversion was performed.

### Historical Correction Review

The first correction incorrectly treated the repayment allocation total as previous payments and then added the current M-Pesa payment again. This double-counted the current payment for loans whose transaction rows already represented that payment, including payments split across multiple installments.

The affected totals were recalculated from the actual payment history. Loan #9598 had no previous payment; its KSh 11,000 M-Pesa payment was split into five KSh 2,100 repayment allocations, with KSh 500 credited to the guarantee deposit. Its corrected Total Repaid is therefore KSh 11,000.00.

## Corrected Loans

| Loan ID | User ID | M-Pesa Amount | Allocated to Loan | Excess to Guarantee Deposit | Final Total Repaid |
| ---: | ---: | ---: | ---: | ---: | ---: |
| 9598 | 9538 | KSh 11,000.00 | KSh 10,500.00 | KSh 500.00 | KSh 11,000.00 |
| 9678 | 9618 | KSh 6,000.00 | KSh 5,000.00 | KSh 1,000.00 | KSh 26,000.00 |
| 9746 | 9686 | KSh 12,500.00 | KSh 12,000.00 | KSh 500.00 | KSh 12,500.00 |
| 9756 | 9696 | KSh 6,500.00 | KSh 5,000.00 | KSh 1,500.00 | KSh 41,500.00 |
| 9827 | 9767 | KSh 37,000.00 | KSh 36,000.00 | KSh 1,000.00 | KSh 37,000.00 |
| 9828 | 9768 | KSh 62,000.00 | KSh 60,000.00 | KSh 2,000.00 | KSh 62,000.00 |
| 9829 | 9769 | KSh 50,000.00 | KSh 48,000.00 | KSh 2,000.00 | KSh 50,000.00 |
| 9849 | 9789 | KSh 6,900.00 | KSh 6,300.00 | KSh 600.00 | KSh 19,350.00 |
| 9873 | 9814 | KSh 49,500.00 | KSh 48,000.00 | KSh 1,500.00 | KSh 49,500.00 |
| 9894 | 9835 | KSh 5,560.00 | KSh 5,000.00 | KSh 560.00 | KSh 13,060.00 |

## Database Safety

- Database engine: XAMPP MariaDB/MySQL.
- Existing schema preserved.
- No new columns or tables required.
- Existing guarantee deposit balances were preserved.
- Future normal-loan excesses are stored as completed payment transactions and included in `total_repaid`.
- Future legacy-loan excesses are stored in `legacy_paid` and included in `total_repaid`.

## Validation

- Loan #9746 verified in MariaDB with `total_payable = 12000.00`, `legacy_paid = 12500.00`, and `total_repaid = 12500.00`.
- Loans #9598, #9827, #9828, #9829, and #9873 were rechecked and corrected after the initial historical backfill was found to double-count their current M-Pesa payments.
- User #9686 guarantee deposit balance verified at KSh 500.00.
- PHP syntax checks passed for both updated application files.
