# CSV Legacy Import Validation Script

This document describes the legacy CSV import validation script and how to use it to compare imported database loan records against legacy CSV source data.

## Purpose

The validation script verifies that legacy CSV import rows were applied correctly into the database by comparing key loan and repayment values for each imported `national_id`.

## Script path

- `scripts/validate_Loan_legacy_import.php`

## What it validates

The script compares CSV values against imported loan records for the following fields:

- `principal` → loan amount
- `paid` → total allocated repayment amount via `loan->total_repaid` (which returns `legacy_paid` for legacy imports, or `repayments()->sum('amount_paid')` for normal loans)
- `arrears` → loan arrears calculated from overdue repayment balances
- `due` → loan `amount_due` from the next pending repayment schedule
- `fine` → active loan fines total via `loan->fines()`
- `payable` → imported loan `total_payable`
- `reference` → loan reference if present
- `disbursed_date` → loan disbursement date if present

## Exact field mappings from the legacy master importer

This validator follows the field behavior in `app/Services/MasterLegacyImportService.php`.

- CSV `principal` maps to `Loan::amount`.
- CSV `payable` maps to `Loan::total_payable`.
  - If the legacy importer receives a positive CSV `payable`, it uses that value.
  - Otherwise it calculates `total_payable` from `principal + interest` based on the tier interest rate.
- CSV `paid` is validated against:
  - `Loan->legacy_paid` if `legacy_import = true` (historical snapshot)
  - `Loan->repayments()->sum('amount_paid')` if `legacy_import = false` (live calculation)
- CSV `arrears` is validated against the loan's stored `arrears` value.
- CSV `due` is validated against the imported loan's `amount_due`, which the importer reconciles from the next pending/overdue repayment balance or outstanding balance.
- CSV `fine` is validated against `Loan->fines()->sum('amount')`.
- CSV `reference` maps to `Loan::reference`.
- CSV `disbursed_date` maps to `Loan::disbursement_date`.

The importer also reconciles summary fields after payment import in `MasterLegacyImportService::reconcileLoanSummaryFromRepayments()`, so validation checks the final stored loan state rather than the raw CSV summary alone.

## Relationship to the legacy importer

This validator is intentionally modeled on the legacy import flow in `app/Services/MasterLegacyImportService.php`.

- Field normalization and import behavior come from `MasterLegacyImportService::applyLoanImportDefaults()`.
- Payment and summary reconciliation come from `MasterLegacyImportService::reconcileLoanSummaryFromRepayments()`.
- The script compares the CSV source values against the loan state produced by the legacy import service rather than a separate snapshot.

**Repayment allocation for legacy imports**: When a legacy-imported loan has a non-zero `Paid` amount, the `MasterLegacyImportService::applyHistoricalPayment()` method allocates that amount across the repayment schedule using `RepaymentService::recordLoanPayment()`. The allocation is idempotent—it only runs once per loan, even if the import is re-run on the same data. The allocated payments create `PaymentAllocation` audit records that can be inspected to verify the distribution pattern.

Related documentation: [LEGACY_IMPORT_LOAN_FLAG_UPDATE.md](LEGACY_IMPORT_LOAN_FLAG_UPDATE.md) documents the safe `legacy_import` flag pattern for preserving the imported CSV `Paid` value on legacy-imported loans.

## Matching logic

The script matches each CSV row to the database as follows:

1. Normalize CSV headers into canonical names.
2. Extract `national_id` and skip rows without it.
3. Find the imported user by `national_id`.
4. Retrieve the user's active/approved/pending loans.
5. Resolve a matching loan using, in order:
   - CSV `reference` when `--loan-reference` is provided
   - CSV `disbursed_date`
   - the most recent loan by creation date

## Output

The script prints a summary report including:

- total rows read
- skipped rows
- matched rows
- rows with no user
- rows with no loan
- mismatch counts per field
- total rows with any mismatch or issue

When `--show-errors` is passed, it also prints detailed mismatch rows.

If `--report` is provided, the script writes a CSV report with one row per import row and the following fields:

- `row`
- `national_id`
- `loan_id`
- `loan_reference`
- `status`
- `note`
- CSV and DB values for `principal`, `paid`, `arrears`, `due`, `fine`, `payable`, `reference`, and `disbursed_date`
- `mismatch_fields`

## Usage

```bash
php scripts/validate_Loan_legacy_import.php --csv="C:\path\to\legacy.csv" --report="C:\path\to\validation_report.csv" --show-errors --loan-reference --threshold=0.01
```

### Options

- `--csv` (required): path to the legacy CSV file
- `--report`: path to export a CSV validation report
- `--show-errors`: print detailed rows with mismatches
- `--loan-reference`: attempt to resolve the imported loan by CSV loan reference
- `--threshold`: numeric threshold for acceptable rounding differences (default `0.01`)

## Notes

- Numeric CSV values are normalized by stripping currency symbols, commas, quotes, and whitespace.
- Date matching is normalized to `Y-m-d` when both sides are parseable.
- The script is designed as a validation layer only and does not modify the database.

## Analysis helper script

An additional helper script is included to analyze the exported validation report (`validation_report.csv`) and produce a concise JSON summary of mismatches for `paid`, `arrears`, and `due`.

- Script path: `scripts/analyze_validation_report.php`
- Purpose: read the CSV produced by `--report` and summarize:
  - total rows processed
  - mismatch counts per field (`paid`, `arrears`, `due`)
  - average and maximum absolute differences
  - direction counts (DB > CSV vs DB < CSV)
  - common mismatch field patterns (e.g. `paid+arrears`)

Usage:

```bash
php scripts/analyze_validation_report.php
```

The script expects the report file to be located at the repository root as `validation_report.csv` (the same path used by `--report`). It prints a compact JSON object. Example output (trimmed):

```json
{
  "rows": 321,
  "counts": { "paid": 178, "arrears": 199, "due": 100 },
  "avg_diff": { "paid": 18367.98, "arrears": 15399.96, "due": 10045.93 },
  "max_diff": { "paid": 144000, "arrears": 144000, "due": 72000 }
}
```

This helper is intended for rapid triage after running the validator and exporting the report; it does not change any data.
