# Admin Loan General Information Update

## Summary
The admin loan management experience now exposes a richer general-information view for each loan row, includes export support for Excel and PDF, and adds a derived earned-interest metric that reflects actual collections against total payable.

## What changed
- Added a General Information section inside the expandable loan row on the admin loans list.
- Added an earned-interest metric for each loan and for the overall dashboard summary using the actual paid-to-total-payable ratio. The value is derived as:

```php
$earned = $interest * ($repaid / $payable);
return max(0.0, min($interest, $earned));
```

This keeps the metric read-only and prevents any change to the underlying repayment or disbursement engine.
- The section displays the requested fields:
  - Day
  - Member
  - ID Number
  - Address
  - Reference
  - Phone
  - Guarantor
  - Disbursed date
  - Cycle
  - Principal
  - Payable
  - Paid
  - Outstanding
  - Due
  - Arrears
  - Officer _id
  - Officer Name
  - % interest
- Added export buttons for the loans list so the same information can be downloaded as Excel or PDF.
- Added an “Earned Interest” summary card to the admin dashboard and an “Earned Interest” value in the admin loan list and user loan overview so staff can see the portion of interest realized from actual repayments.
- Added a dedicated export flow in the admin reporting controller using the existing Excel/PDF export pattern.
- Added an optional installment due-date filter to `/admin/loans`.
- The due-date filter matches loans that have at least one repayment installment due on the selected calendar date.
- The selected due date is preserved when navigating through paginated admin results.
- The same due-date filter is applied to the admin Excel and PDF general-information exports.
- Admin Excel and PDF exports use the same national ID search as the loan list, so filtered results remain consistent for requests such as `search=1022226`.
- Exports include all loans matching the active filters, regardless of the current pagination page, and preserve the loan list's newest-first ordering.
- Phone and Guarantor columns in the Excel export are explicitly formatted as text so long values remain human-readable instead of displaying in scientific notation.
- Reference and Guarantor field values are sourced from the user profile (`$loan->user->reference` and `$loan->user->guarantor`) ensuring consistent, uniform data across all views: the admin loans list general-information panel, Excel exports, and PDF exports. This prevents missing or incomplete values by centralizing the source to the borrower's profile rather than loan-specific fields.
- Added the installment due-date filter to the officer loan list at `/officer/loans`.
- The officer filter supports selecting a date, filtering results, and clearing the filter.
- Added optional admin loan-list filters for loans with positive arrears and loans with positive fines.
- The arrears and fines filters are also applied to the general-information Excel and PDF exports.

## Files updated
- resources/views/admin/loans/index.blade.php
- resources/views/admin/users/show.blade.php
- app/Models/Loan.php
- app/Http/Controllers/Admin/AdminDashboardController.php
- app/Http/Controllers/Admin/AdminReportingController.php
- routes/web.php
- resources/views/admin/reports/loan_general_info_pdf.blade.php
- app/Exports/ArrayExport.php
- resources/views/officer/loans/index.blade.php
- app/Http/Controllers/Officer/OfficerDashboardController.php
- tests/Feature/AdminLoanGeneralInfoTest.php

## Notes
This update is presentation and reporting focused. It uses existing loan, user, repayment, and tier data rather than introducing new storage fields.

The installment filter is applied through the repayment schedule's `due_date` field:

```php
$query->whereHas('repayments', function ($query) use ($selectedDate) {
  $query->whereDate('due_date', $selectedDate);
});
```

The date is validated as an optional calendar date before it is used in the admin and officer queries. Leaving the filter blank preserves the existing unfiltered loan lists.

The general-information export applies the same status, tier, national ID, and installment due-date filters as `/admin/loans`. In particular, the `search` value is matched against the borrower's `national_id`, which prevents a loan visible in the filtered admin list from being omitted from the Excel or PDF export. The export returns every matching loan rather than only the current paginated page and uses the same newest-first ordering.

### Reference and Guarantor field source

The Reference and Guarantor columns in all views (admin loan list general-information section, Excel exports, PDF exports) now consistently source these values from the borrower's user profile (`$loan->user->reference` and `$loan->user->guarantor`) rather than from loan-specific fields. This ensures:
- Uniform data across all display formats (list view, Excel, PDF)
- Complete values for all users since the profile fields are the single source of truth
- Consistency with other user-sourced fields like address and phone number
- Prevention of N/A or missing values in exports when user profile data is populated

For the general-information Excel export, `ArrayExport` accepts optional text-column formats and forces those configured values to text before PhpSpreadsheet writes the workbook. The Phone column (`F`) and Guarantor column (`G`) are exported as shared text values with Excel's text number format, while the browser and PDF views render the same values as strings. This prevents long values such as `254705098359` from appearing as `2.54705E+11`.

## Admin loan creation and approval workflow

The admin loan create and edit workflow now reuses the existing `LoanService` fee, tier, and status logic while adding the following controls:

- The admin can assign a borrower, tier, and approved officer from the loan form.
- The selected tier dynamically sets the amount field's minimum and maximum values; the controller validates the same range server-side.
- Registration fee eligibility is based on whether the borrower has a previous `active` or `completed` loan.
- Borrowers with a previous active or completed loan do not require a registration fee.
- Borrowers without a previous active or completed loan retain the normal registration-fee behavior.
- Admins can mark the LAF fee as paid during creation, editing, or approval.
- When the admin marks LAF as paid, the fee is kept on the loan for record-keeping but is excluded from the customer payable total. The payable calculation becomes principal + interest + any applicable registration fee only.
- If the admin does not mark LAF as paid, the loan can remain approved with the LAF balance pending for the user's existing STK Push payment flow.
- Pending loans remain editable before approval.
- Approval requires the stored `admin_disbursement_pin` from the `settings` table.
- An invalid PIN leaves the loan in its current pending state.
- Existing disbursement validation still prevents disbursement until required fees are paid.

The relevant workflow files are:

- `resources/views/admin/loans/create.blade.php`
- `resources/views/admin/loans/edit.blade.php`
- `resources/views/admin/loans/show.blade.php`
- `app/Http/Controllers/Admin/LoanController.php`
- `app/Services/LoanService.php`

The registration-fee rule is centralized in `LoanService::isFirstDisbursement()`, which now checks for prior loans with status `active` or `completed`. Officer assignment is stored through the borrower's existing `assigned_officer_id` field; no new database column is introduced.

### Dynamic loan preview on create

The admin create-loan view now displays a live preview after the borrower, tier, and amount are selected. The preview shows:

- interest amount and tier interest percentage
- repayment duration and installment period
- calculated LAF fee
- registration fee, including the active/completed-loan waiver rule
- estimated total payable

The preview also reflects whether the admin marks the LAF fee as paid. It is a client-side estimate only; the submitted amount, tier range, fees, interest, and total payable continue to be recalculated and validated by `LoanService` on the server. When LAF is marked as paid by admin, the server-side payable calculation excludes that LAF component from the customer total so the loan total matches the expected principal + interest + registration-fee formula.

## Zero-balance loan completion update

Loans are now treated consistently across normal and `legacy_paid` repayment flows. When an active loan's computed outstanding balance is KSh 0.00 or less after rounding to two decimal places, only the loan `status` is updated from `active` to `completed`. Repayments, transactions, payment allocations, and financial amounts are not deleted or rewritten.

### Updated scripts

- `app/Models/Loan.php`
  - Added `hasZeroOutstandingBalance()` using the existing `total_repaid` calculation. Legacy loans continue to use `legacy_paid`; normal loans continue to use repayment totals and guarantee-deposit excess payments.
  - Added `syncCompletionStatus()` to update only qualifying active loan statuses.
  - Added `effective_status` for portal rendering while a previously paid active loan awaits reconciliation.
- `app/Services/RepaymentService.php`
  - Calls `syncCompletionStatus()` after every repayment allocation, covering both normal and legacy repayment paths.
- `app/Console/Commands/SyncCompletedLoanStatuses.php`
  - Adds the non-destructive repair command:

```bash
php artisan loans:sync-completed-statuses
```

  - The command scans active loans and changes only those with a zero computed outstanding balance to `completed`.
- Admin, user, and officer loan views
  - Use `effective_status` for badges, labels, and status-dependent display actions so paid-off active loans appear as completed immediately.
- `tests/Feature/AdminLoanGeneralInfoTest.php`
  - Covers normal repayment completion and legacy `legacy_paid` repayment completion.

The reconciliation command is safe to rerun. It does not remove records or modify repayment amounts, balances, fees, transactions, or allocations.

## Latest loan transaction ordering update

Loan transaction histories and latest-transaction indicators now use a loan-specific activity date instead of collection order. The activity date is selected using this precedence:

1. The linked repayment's `paid_at` value, when available.
2. The transaction's `processed_at` value when `paid_at` is unavailable.

This ensures the last paid repayment is shown as the latest transaction for that loan, while disbursement and other transactions continue to use their own `processed_at` value. No transaction or repayment data is changed.

### Updated scripts

- `app/Models/Transaction.php`
  - Added the `activity_at` accessor implementing the `paid_at` then `processed_at` precedence.
- `app/Http/Controllers/Admin/LoanController.php`
- `app/Http/Controllers/Web/UserDashboardController.php`
- `app/Http/Controllers/Officer/OfficerDashboardController.php`
  - Eager-load linked repayment data where loan transaction histories are displayed.
- Admin, user, and officer loan/repayment views
  - Sort transaction histories and latest-transaction indicators by `activity_at` descending.

Affected views include the admin loan list and detail pages, admin repayment pages, user loan and repayment pages, and the officer loan detail page.

### Admin loan-list amount display fix

The `Last Transaction` column in `resources/views/admin/loans/index.blade.php` now displays the amount from the selected latest transaction. It no longer repeats the loan principal from `$loan->amount`. For example, Loan #9873 now shows its latest payment of KSh 48,000 above the older KSh 40,000 disbursement, while the principal remains available in the General Information section.

### Date-cast compatibility fix

`Repayment::$paid_at` is explicitly cast to `datetime` in `app/Models/Repayment.php`. The `Transaction::activity_at` accessor also converts any legacy raw `paid_at` string to Carbon before returning it. This keeps date sorting compatible with existing database records and prevents a type error when loading `/admin/loans`.

## M-Pesa Transaction and Processing Time Update

Payment transactions now keep two timestamps distinct:

- **M-Pesa transaction time** comes from the payload `TransTime` value, such as `20260823062844`, interpreted in `Africa/Nairobi` time.
- **Processed time** comes from the LendCore application clock when the payment is processed and is stored in `transactions.processed_at`.

This applies to direct M-Pesa callbacks, queued C2B payments, and CSV uploads containing callback payloads. The complete payload remains stored in `transactions.callback_data`; no database schema change was introduced. Admin, officer, user, repayment, and C2B report views display both values separately.

### Historical timestamp correction

Before this update, some completed payload payments had `transactions.processed_at` overwritten with the payload `TransTime`. A controlled MariaDB repair corrected 220 historical payment rows matching that exact pattern by setting `processed_at` to the stored UTC `created_at` value plus three hours for Nairobi time. Payment amounts, payloads, transaction IDs, repayments, and guarantee deposits were not changed. A final verification found no remaining rows matching the old overwrite pattern.
