# CSV Legacy Import Validation Roadmap

## Purpose

This roadmap describes the design and validation workflow for a new script that compares legacy CSV import data against the imported database records. The script should verify that the following imported values are accurate on a per-user basis by national ID:

- principal
- paid
- arrears
- dues
- fines

## Goals

- Use the legacy CSV file as the source of truth for imported rows.
- Match each CSV row to the imported user by `national_id`.
- Compare numeric import fields against the database record for the corresponding imported loan or user summary.
- Validate that CSV `payable` is honored for imported `total_payable` and installment sizing when available.
- Validate that CSV `paid` is recorded through repayment allocations and reflected in `loan->repayments()->sum('amount_paid')`.
- Identify mismatches and surface them in a concise validation report.
- Preserve existing import behavior; this is a validation layer only.

## Validation Scope

The script should compare the following values:

- `principal` → imported loan amount or principal field in the database
- `paid` → total historic payments or `amount_paid` for the loan/user
- `arrears` → imported arrears value versus database arrears balance
- `due` → imported due amount versus loan schedule next due value or `amount_due`
- `fine` → imported fine amount versus active fine records or loan-level fine summaries

## Inputs

- Legacy CSV file path
- Database connection to the imported LendCore environment
- `national_id` value used for matching

## Matching Strategy

1. Normalize CSV headers to expected names.
2. Read each CSV row and extract `national_id`.
3. Skip rows missing `national_id` or with invalid IDs.
4. For each valid row, query the database for the corresponding user by `national_id`.
5. If a user is found, retrieve the imported loan and summary fields needed for comparison.

## Comparison Logic

For each matched row, compare:

- CSV `principal` against the database loan amount or loan principal field.
- CSV `paid` against the loan/user payment summary.
- CSV `arrears` against the current loan arrears balance.
- CSV `due` against the next scheduled installment due or `amount_due`.
- CSV `fine` against active/legacy fine values associated with the loan or user.

## Reporting

The script should generate a report containing:

- Total rows processed
- Rows matched by `national_id`
- Rows skipped due to missing or invalid `national_id`
- Rows with no matching database user
- Value mismatch counts for each field: `principal`, `paid`, `arrears`, `due`, `fine`
- A detailed list of mismatched rows showing:
  - `national_id`
  - CSV values
  - Database values
  - Difference or discrepancy note

## Validation Rules

- Numeric values should be parsed consistently from CSV formats such as `24,000` and `24,000.00`.
- Blank or missing numeric fields should be treated as zero only when appropriate.
- Small rounding differences within `0.01` should be handled as acceptable, but the report should flag them separately.
- If the CSV contains a loan reference or disbursement date, use that as a secondary match when multiple imported loans exist for the same user.

## Operational Workflow

1. Prepare the CSV file and ensure it is UTF-8 encoded.
2. Run the validation script against the current imported database.
3. Review the summary report and identify any rows with field mismatches.
4. Investigate mismatches in the source CSV and the imported loan records.
5. If errors are found, determine whether the issue is a CSV formatting problem, a migration bug, or a database inconsistency.

## Output Format

- Summary counts of matched, skipped, and mismatched rows.
- Detailed mismatch table in plain text or CSV output.
- Optional flags for `--export-report` or `--show-only-errors`.

## Recommended Checks Before Code Implementation

- Confirm the canonical field mapping between CSV columns and database fields.
- Confirm how `arrears`, `due`, and `fine` are represented in the current database schema.
- Identify whether imported loan data is stored in a single loan record or spread across loan, repayment, fine, and transaction tables.
- Determine whether the validation should compare against the latest loan state or the original imported snapshot.

## Next Steps

- Draft the script signature and input options.
- Define the database read path and object relationships to use for comparisons.
- Specify the report layout and output destination.
- Create test cases with sample CSV rows and expected validation outcomes.
- Add implementation documentation in `docs/CSV_LEGACY_LOAN_IMPORT_VALIDATION_SCRIPT.md` and link it from the roadmap.

## Validation Script Design

This section defines how the validation script should be structured and what it should check.

### Script signature

- `php scripts/validate_Loan_legacy_import.php --csv="path/to/file.csv" --report="path/to/report.csv"`
- Optional flags:
  - `--show-errors` to print detailed rows with mismatches
  - `--loan-reference` to use loan reference as a secondary match key
  - `--threshold=0.01` to treat small rounding differences as acceptable

This validation flow is implemented in `docs/CSV_LEGACY_LOAN_IMPORT_VALIDATION_SCRIPT.md` and `scripts/validate_Loan_legacy_import.php`.

### Core validation steps

1. Load and normalize headers from the legacy CSV.
2. For each row:
   - Parse `national_id`, `principal`, `paid`, `arrears`, `due`, and `fine`.
   - Normalize numeric strings with commas, quotes, and locale-specific formatting.
   - Skip rows missing `national_id`.
   - Query the database for the user by `national_id`.
   - If the user exists, resolve the relevant imported loan by loan reference and disbursement date when available.
   - Compare the CSV values to the corresponding database values.
3. Record a mismatch if any field differs beyond the allowed rounding tolerance.

### Legacy importer field reference

This validation flow is based on the legacy import field behavior in `app/Services/MasterLegacyImportService.php`.

- `applyLoanImportDefaults()` is the source of record for how `payable`, `principal`, `interest`, and repayment schedule values are populated.
- `reconcileLoanSummaryFromRepayments()` shows how imported repayment totals are used to derive `amount_paid`, `amount_due`, and `outstanding_balance`.
- The validation script should be used to verify that the database state matches the loan records produced by the legacy importer service.

#### Exact mappings

- `principal` → `Loan::amount`
- `payable` → `Loan::total_payable` (prefers CSV when present; otherwise calculates from tier interest)
- `paid` → `Loan->repayments()->sum('amount_paid')`
- `arrears` → `Loan::arrears`
- `due` → `Loan::amount_due`
- `fine` → `Loan->fines()->sum('amount')`
- `reference` → `Loan::reference`
- `disbursed_date` → `Loan::disbursement_date`

### Report details

The script should output:

- Total rows processed
- Rows matched
- Rows skipped
- Rows with no matching user
- Mismatch counts by field
- Detailed mismatches with:
  - `national_id`
  - `loan_reference`
  - `csv_principal`, `db_principal`
  - `csv_paid`, `db_paid`
  - `csv_arrears`, `db_arrears`
  - `csv_due`, `db_due`
  - `csv_fine`, `db_fine`
  - `difference`
  - `notes`

### Validation output format

- Plain-text summary to the console
- CSV or JSON mismatch export for review and follow-up

### Audit and follow-up

- Include an audit header in the report with the CSV file name, import timestamp, and validation run timestamp.
- Add a column for `investigation_notes` so analysts can record why a mismatch occurred.

### Update expectations

This roadmap should now explicitly capture the script design, the field-level comparisons, and the report output without changing the existing database schema or import behavior.
