## Dry-Run Mode vs Production Mode
### Dry-Run Mode (`--dry-run` flag)
**What It Does:**
- Reads and validates all transactions
- Matches users and loans (CSV: `national_id`; JSON: `BillRefNumber` via `C2bUserMatchingService`)
- Simulates allocation decisions (including routing to guarantee deposit or saving to suspense)
- **Does NOT** persist payments, guarantee balances, or suspense records — it only shows intended actions
- Uses the same logic as production so you can validate behavior end-to-end

Key behaviors simulated in dry-run:
- If a loan has outstanding balance → allocation is shown
- If a loan has no outstanding repayment balance → dry-run shows the amount would be credited to the user's guarantee deposit
- If no matching user is found → dry-run shows the transaction would be saved to the `suspense_transactions` store for manual review
- If any unexpected error occurs while processing a row → dry-run shows the row would be saved to suspense

**Why run dry-run first:**
- Confirms user matching quality, loan selection, and that amounts will allocate as you expect before mutating production data.

**Output Example (dry-run):**
```
=== Payment Import (DRY RUN - NO CHANGES) ===
File: transactions.json
Format: JSON
Mode: DRY RUN
Total transactions: 5

Transaction #1:
  User Ref: 13743953 | Amount: KSh 1,500.00 | Name: ROSEMARY
  ✓ User found: ROSEMARY (ID: 42)
  ✓ Payment would be ALLOCATED to Loan #100: KSh 1,500.00

Transaction #2:
  User Ref: 31942157 | Amount: KSh 1,500.00
  ⚠ UNMATCHED: No user found — would save to suspense for manual review

Transaction #3:
  User Ref: 13743953 | Amount: KSh 10,000.00
  ✓ User found: ROSEMARY (ID: 42)
  ✓ Loan #100 has no outstanding balance — would add KSh 10,000.00 to guarantee deposit

=== DRY RUN SUMMARY ===
Total Processed: 5
Matched: 4
Unmatched (would be saved to suspense): 1
Errors: 0

⚠ This was a DRY RUN. No payments or suspense records were created.
Run without `--dry-run` to apply payments and persist changes.
```

### Error Handling & Validation

### Validation Checks
1. **File exists** — Script exits if file not found
2. **File format** — Must be `.csv` or `.json`
3. **JSON validity** — Invalid JSON exits with error
4. **User reference** — A `null` `BillRefNumber` is accepted during validation and allowed to continue through the matching flow. If no user can be resolved, the record is routed to `suspense_transactions` for manual review. A blank string remains invalid and should be treated as a missing reference.
5. **Payment amount** — Must be > 0; invalid amounts are saved to suspense (or shown in dry-run)
6. **User exists** — No matching user → transaction saved to suspense
7. **Loan exists** — No active/recent loan → transaction saved to suspense
8. **Schedule generation** — If generating a repayment schedule fails, the transaction is saved to suspense

### Error Output (behavior)
```
❌ Row 2: User not found → saved to suspense for manual review
✓ Row 3: Loan fully paid → amount routed to user's guarantee deposit
❌ Row 4: Failed to generate schedule → saved to suspense for manual review

### Key Policies for Failures
- Transactions with **no matching user** are saved to the `suspense_transactions` table for manual review and reassignment.
- Transactions where the **user exists but the loan has no outstanding balance** are routed to the user's `guarantee_deposit_balance` (visible in user dashboard and usable by allocation flows).
- Any **unexpected error** while processing a transaction (schedule generation failure, DB exception, etc.) will result in the transaction being saved to suspense to avoid losing data and to enable manual recovery.

These behaviors are mirrored in dry-run as non-persistent "would save to suspense" / "would add to guarantee deposit" messages so you can validate before running production.
# Payment Import Script: CSV & JSON Processing

## Overview

The unified payment import script (`import_payments.php`) processes historical payments from CSV or relay-box JSON exports. It intelligently matches payments to users and loans, then allocates them to pending/overdue installments using an **oldest-first allocation strategy**.

This documentation covers:
- Script architecture and workflow
- User matching logic (CSV vs JSON)
- Loan selection strategy
- Payment allocation & repayment logic
- Dry-run vs production modes
- Usage examples and error handling

---

## Quick Start

### Admin Data Manager JSON Upload

The web interface at **Admin > Data Manager > Import JSON Payments** accepts relay-box `.json` exports. PHP validates the JSON records, JavaScript displays upload progress, and the native `JsonPaymentImportService` processes payments in bounded production chunks after validation. The admin flow imports immediately after validation and does not run a dry-run or require a second confirmation. The command-line importer documented below continues to support both CSV and JSON files.

### Dry-Run (Preview Only)
```bash
# CSV format
php -d memory_limit=512M import_payments.php "C:\path\to\file.csv" --dry-run

# JSON format (relay-box export)
php -d memory_limit=512M import_payments.php "C:\path\to\file.json" --dry-run

# With verbose output
php -d memory_limit=512M import_payments.php "C:\path\to\file.json" --dry-run --verbose
```

> Recommended: run dry-run first to validate user matching, loan selection, and allocation before applying changes.

### Production (Actually Apply Payments)
```bash
# CSV format
php -d memory_limit=512M import_payments.php "C:\path\to\file.csv"

# JSON format
php -d memory_limit=512M import_payments.php "C:\path\to\file.json"
```

---

## Payment Matching Workflow

```
Input File (CSV or JSON)
    ↓
    ├─→ Extract user reference (ID Number for CSV, BillRefNumber for JSON)
    ├─→ Extract payment amount (Paid column for CSV, TransAmount for JSON)
    ↓
Find User by Reference
    ├─→ CSV: Direct national_id lookup
    ├─→ JSON: Smart normalization via C2bUserMatchingService
    ↓
Find Loan (Selection Priority)
    ├─→ 1. Match by principal amount (CSV only)
    ├─→ 2. Active loan with positive pending/overdue repayment balance
    ├─→ 3. Most recent active loan
    ├─→ 4. Most recent loan regardless of status
    ↓
Generate Schedule (if missing)
    ├─→ Ensure repayment schedule exists
    ↓
Allocate Payment (Oldest-First)
    ├─→ Record payment
    ├─→ Allocate to oldest pending/overdue installments
    ├─→ Create PaymentAllocation audit records
    ↓
Output Results
    ├─→ DRY RUN: Show what would happen
    ├─→ PRODUCTION: Actually record + log
```

---

## User Reference Matching

### CSV Format: Direct Lookup
For CSV files, the script looks for columns: `ID Number`, `Id Number`, `id_number`, or `BillRefNumber`.

```php
$user = User::where('national_id', $userRef)->first();
// Fallback:
if (!$user) {
    $user = User::where('id_number', $userRef)->first();
}
// Also support BillRefNumber-style matching via the C2bUserMatchingService.
if (!$user) {
    $user = (new C2bUserMatchingService())->findUserByBillRef($userRef);
}
```

**Example CSV Row:**
```
ID Number,Principal,Paid
27345893,10000.00,2500.00
```

### JSON Format: Smart Normalization via C2bUserMatchingService
For relay-box JSON exports, the script uses `BillRefNumber` which gets normalized to handle variations:

```php
$user = (new C2bUserMatchingService())->findUserByBillRef($billRef);
```

**Normalization Process:**
The service builds multiple candidate references from the `BillRefNumber`:

```php
protected function buildCandidateRefs(?string $billRef): array
{
    // Input: "13743953"
    // Output candidates:
    // - "13743953" (as-is)
    // - "13743953" (no spaces)
    // - "3743953" (stripped leading zeros)
    // - "00013743953" (padded to 10 digits)
    // - "000013743953" (padded to 12 digits)
    
    $trimmed = trim((string) $billRef);
    $values = array_unique(array_filter([
        $trimmed,
        preg_replace('/\s+/', '', $trimmed),        // Remove spaces
        ltrim($trimmed, '0'),                        // Strip leading zeros
        str_pad(ltrim($trimmed, '0'), 8, '0', STR_PAD_LEFT),   // Pad to 8
        str_pad(ltrim($trimmed, '0'), 10, '0', STR_PAD_LEFT),  // Pad to 10
        str_pad(ltrim($trimmed, '0'), 12, '0', STR_PAD_LEFT),  // Pad to 12
    ], ...));
    
    return array_values($values);
}
```

**Why This Matters:**
- `BillRefNumber` might come from M-Pesa with leading zeros stripped
- Different systems store national IDs with/without padding
- Normalization ensures you catch matches despite formatting differences

**Example JSON Payload:**
```json
{
    "TransID": "UG1HP9IR22",
    "TransAmount": "1500.00",
    "BillRefNumber": "13743953",
    "FirstName": "ROSEMARY"
}
```

The service will match this against users with `national_id` or `id_number` in these formats:
- `13743953`
- `0013743953`
- `000013743953`
- etc.

---

## Implementation Roadmap: Reliable JSON Payment Matching

The importer must preserve the payment identity and source timestamp from every JSON payload. The outer relay-box record status describes forwarding/processing by the relay box; it must not replace the payment fields inside `payload`. For example, a record with `status: "failed"` still contains the payment details that must be validated and either applied or sent to suspense according to the import policy.

### Source-to-Domain Field Mapping

For each JSON record, read values from `payload` and map them as follows:

| JSON field | Meaning | Required handling |
|---|---|---|
| `payload.BillRefNumber` | User reference | Normalize and match against `users.national_id`; use `id_number` only as an explicit fallback if supported by the matching service. |
| `payload.TransID` | Unique M-Pesa transaction ID | Store as `transactions.mpesa_transaction_id`, `payment_allocations.mpesa_transaction_id`, and `suspense_transactions.transaction_id`. |
| `payload.TransAmount` | Amount paid | Parse as a positive decimal amount and use the same value for allocation and audit records. |
| `payload.TransTime` | Original payment time in `YYYYMMDDHHMMSS` format | Preserve in the payload and expose as the transaction time. Do not use it as `transactions.processed_at`. |
| `transactions.processed_at` | Local time LendCore processed the payment | Set from the application clock in `Africa/Nairobi`; keep it separate from the M-Pesa transaction time. |
| `payload` | Complete source payload | Store in `transactions.callback_data` or `suspense_transactions.original_payload` for audit and reconciliation. |

The importer must not silently discard `TransID`, `BillRefNumber`, or `TransTime`. These fields are required for reconciliation, duplicate protection, and visibility in `admin/reports/c2b-transactions`. Transaction views show both the original M-Pesa transaction time from `TransTime` and the local LendCore processed time from `processed_at`.

### Phased Implementation Plan

1. **Normalize and validate the input**
  - Confirm the file is valid JSON, or that CSV callback columns contain valid JSON objects/arrays.
  - Expand a compacted JSON array into one flat callback payload per payment.
  - Extract `BillRefNumber`, `TransID`, `TransAmount`, and `TransTime` from the payload.
  - Reject missing references, missing transaction IDs, invalid timestamps, duplicate transaction IDs, and non-positive amounts to suspense or the dry-run report.
  - Keep money as decimal values; do not use a rounded display string for calculations.

2. **Match the user**
  - Pass `BillRefNumber` to `C2bUserMatchingService::findUserByBillRef()`.
  - Match normalized candidates against `national_id`, then the supported legacy `id_number` field.
  - Record the matched `user_id` on the payment transaction and allocation audit records.
  - If no user matches, create one `unmatched` suspense record keyed by `TransID`; do not create a loan payment.

3. **Select the loan**
  - Prefer the user’s active loan with an outstanding balance.
  - If there are multiple eligible loans, select using the established oldest/relevant-loan ordering and document the decision in the import result.
  - Do not select a completed loan when the user has another eligible active loan.
  - If no eligible loan exists, route the payment to the documented guarantee-deposit flow or suspense flow; do not silently mark it as allocated to a completed loan.

4. **Allocate the payment**
  - Generate a repayment schedule before allocation when the selected loan has no schedule.
  - Allocate oldest outstanding installments first, including `pending` and `overdue` repayment statuses used by the service.
  - Persist `PaymentAllocation` records for every installment touched, with the source `TransID`, loan, repayment, amount, before balance, and after balance.
  - Route any valid remainder to the user’s guarantee deposit according to the existing policy.

  - For `legacy_import = true` loans, increment `loans.legacy_paid` by the amount actually allocated. Non-legacy loans keep their existing repayment accounting.

5. **Queue callback-shaped CSV payloads**
  - Dispatch each CSV payload containing `TransID` through `ProcessC2bNonStkConfirmation`.
  - Pass the complete flat payload so `BillRefNumber`, `TransAmount`, `TransTime`, `MSISDN`, and `TransID` remain available to matching, allocation, audit, and suspense handling.
  - Do not dispatch ordinary legacy CSV rows without a callback transaction ID; retain their existing import behavior.

6. **Persist a reportable transaction record**
  - Create or update one completed `transactions` record per source `TransID` with `user_id`, `loan_id`, amount, `mpesa_transaction_id`, `callback_data`, and the parsed `TransTime`.
  - Make the operation idempotent: rerunning the same JSON file must not create a second payment or duplicate guarantee-deposit credit for an existing `TransID`.
  - Ensure the record satisfies the C2B report query: payment type, completed status, M-Pesa ID, user, loan when applicable, and `callback_data.BillRefNumber`.

7. **Handle failures atomically**
  - Process each source transaction inside its own database transaction.
  - Roll back repayment, allocation, transaction, and guarantee-deposit changes if any step fails.
  - Save the original payload to suspense with the failure reason and `TransID` so the payment can be manually reassigned without losing data.

8. **Expose and reconcile the results**
  - The admin C2B report must show both matched imported payments and unmatched suspense records for the selected `TransTime`/processing date range.
  - Display the M-Pesa `TransID`, BillRefNumber, amount, original transaction time, matched user, selected loan, and final status.
  - Reconcile totals using source payload amounts: processed count, matched count, suspense count, loan-applied amount, and guarantee-deposit amount.

### Acceptance Checks

Use a fixture containing the following payload to verify the complete path:

```json
{
  "id": 1536,
  "type": "confirmation",
  "status": "failed",
  "payload": {
   "TransID": "UHGH738Q8E",
   "TransTime": "20260816112203",
   "TransAmount": "1600.00",
   "BillRefNumber": "6204208"
  }
}
```

The test passes only when the importer matches `6204208` to the correct user, queues the payload, selects that user’s eligible loan, applies KSh 1,600.00 or routes the documented remainder, stores `UHGH738Q8E` and the parsed transaction time, and displays the result in `admin/reports/c2b-transactions`. A second run with the same `TransID` must be idempotent and make no additional balance changes. For a legacy loan, verify that `legacy_paid` increases by the amount allocated.

---

## Validation Roadmap: Legacy Loan #9618 Balance Mismatch

### Observed Case

For admin user `/admin/users/9558`, matched by `BillRefNumber`/`national_id` `11066581`:

| Value | Observed amount |
|---|---:|
| Principal | KSh 50,000.00 |
| Total payable | KSh 62,500.00 |
| Total repaid / `legacy_paid` | KSh 18,000.00 |
| Expected outstanding balance | KSh 44,500.00 |
| Stored `loans.outstanding_balance` used by importer | KSh 0.00 |

The expected balance is calculated as:

```text
total_payable - legacy_paid
KSh 62,500.00 - KSh 18,000.00 = KSh 44,500.00
```

The admin user page uses the legacy-aware `total_repaid` value, while the payment importer’s dry-run currently uses the persisted `outstanding_balance` field. This is why the loan is matched to the user but treated as having no installment balance.

### Main Repayment Structure Verification

Use the following read-only sequence before changing any payment or loan data:

1. **Confirm the loan identity**
  - Resolve `BillRefNumber 11066581` to user ID `9558`.
  - Confirm the selected active loan is `Loan #9618`.
  - Confirm `legacy_import = true`, `total_payable = 62500.00`, and `legacy_paid = 18000.00`.

2. **Inspect the repayment schedule**
  - Read every repayment belonging to loan `#9618`.
  - Record `installment_number`, `amount_due`, `amount_paid`, `balance`, `status`, `is_active`, and `due_date`.
  - Sum repayment `balance` for `pending` and `overdue` rows.
  - Compare that sum with the admin-calculated KSh 44,500.00.

3. **Trace the first divergence**
  - If repayment balances total KSh 44,500.00 but `loans.outstanding_balance` is `0.00`, the persisted loan summary is stale or was overwritten during legacy import/reconciliation.
  - If repayment balances also total `0.00`, the repayment schedule was marked paid or generated from the wrong paid basis even though `legacy_paid` remains KSh 18,000.00.
  - If repayment rows are missing, the schedule-generation or legacy-import step failed before creating the main repayment structure.

4. **Compare all balance authorities**
  - Admin summary: `total_payable - total_repaid`.
  - Legacy loan authority: `total_payable - legacy_paid`.
  - Repayment authority: sum of `balance` for active unpaid/overdue installments.
  - Stored loan summary: `loans.outstanding_balance` and `loans.amount_due`.
  - These values must be reported side by side; do not infer a paid loan from status `active` alone.

5. **Verify payment allocation readiness**
  - Confirm the main repayment rows use the statuses consumed by `RepaymentService` (`pending` and `overdue`).
  - Confirm the next payable installment has a positive `balance`.
  - Confirm the importer’s selected loan is the same loan shown in the admin page.
  - Confirm a dry run would allocate to the oldest positive-balance installment rather than route to guarantee deposit.

6. **Protect legacy accounting during repair**
  - Do not re-import the payment file while the authorities disagree.
  - Do not add a new payment, alter guarantee deposits, or mark repayments paid during diagnosis.
  - After the authoritative balance is agreed, update only the stale summary or schedule representation through the existing reconciliation process.
  - For future payments on `legacy_import = true` loans, increment `legacy_paid` by the amount actually allocated and keep it consistent with the repayment structure.

### Completion Criteria

The investigation is complete when loan `#9618` has one documented authoritative balance, the main repayment rows reflect the KSh 44,500.00 remaining amount, the persisted summary no longer reports zero, and a dry run shows the next payment being allocated to an installment. Only then should production payment processing resume.

---

## Loan Selection Strategy

Once a user is found, the script selects the target loan using this priority:

### For CSV Import (with Principal Amount)
```php
$principal = (float) preg_replace('/[^0-9.\-]/', '', $row['Principal'] ?? '0');

// 1. Try exact match by principal amount on this user
$loan = $user->loans()
    ->where('amount', $principal)
    ->orderBy('id', 'desc')
    ->first();

// 2. Fallback: Most recent active loan
if (!$loan) {
    $loan = $user->loans()
        ->where('status', 'active')
        ->orderBy('id', 'desc')
        ->first();
}

// 3. Last resort: Most recent loan regardless of status
if (!$loan) {
    $loan = $user->loans()
        ->orderBy('id', 'desc')
        ->first();
}
```

### For JSON Import (no Principal Amount)
```php
// 1. Active loan with a positive pending/overdue repayment balance
$loan = $user->loans()
    ->where('status', 'active')
  ->whereHas('repayments', function ($query) {
    $query->whereIn('status', ['pending', 'overdue'])
      ->where('balance', '>', 0);
  })
    ->orderBy('id', 'desc')
    ->first();

// 2. Fallback: most recent active loan
if (!$loan) {
    $loan = $user->loans()
    ->where('status', 'active')
        ->orderBy('id', 'desc')
        ->first();
}

// 3. Last resort: most recent loan regardless of status
if (!$loan) {
  $loan = $user->loans()->orderBy('id', 'desc')->first();
}
```

**Rationale:**
- **CSV principal matching:** If CSV includes loan amount, we can be precise
- **Repayment balance priority:** Prevents routing a payment to a zero-balance active loan while another active loan has payable installments
- **Active status:** Active loans are preferred for normal repayment
- **Most recent:** Used only as a fallback when no positive repayment balance is found
- **Safety:** Always has a fallback to prevent "no loan found" errors

---

## Repayment Schedule Generation

Before allocating a payment, the script ensures a repayment schedule exists:

```php
if ($loan->repayments()->count() === 0) {
    echo "Generating repayment schedule for Loan #{$loan->id}\n";
    $loanService->generateRepaymentSchedule($loan);
}
```

**What This Does:**
- Calls `LoanService::generateRepaymentSchedule($loan)`
- Creates individual `Repayment` records for each installment
- Sets `due_date`, `amount_due`, `status`, etc. based on loan tier settings
- Required before any payment allocation can occur

**Example Generated Schedule:**
```
Loan #42 (Amount: KSh 10,000)
├─ Repayment #1: Due 2026-08-03, KSh 2,500, Status: pending
├─ Repayment #2: Due 2026-09-03, KSh 2,500, Status: pending
├─ Repayment #3: Due 2026-10-03, KSh 2,500, Status: pending
└─ Repayment #4: Due 2026-11-03, KSh 2,500, Status: pending
```

---

## Payment Allocation Logic: Oldest-First

Once a payment amount is validated, `RepaymentService::recordLoanPayment()` allocates it to pending/overdue installments **in installment order** (oldest installment first).

### Step-by-Step Allocation

```php
public function recordLoanPayment(Loan $loan, float $paid, string $source)
{
    // 1. Get pending and overdue repayments, sorted by installment order
    $repayments = $loan->repayments()
      ->whereIn('status', ['pending', 'overdue'])
      ->orderBy('installment_number', 'asc')
        ->get();

    // 2. Iterate through each unpaid installment
    $remaining = $paid;
    foreach ($repayments as $repayment) {
        if ($remaining <= 0) break;
        
        // 3. Allocate up to the installment's remaining balance
        $allocated = min($remaining, $repayment->balance);
        
        if ($allocated > 0) {
            // 4. Create PaymentAllocation audit record
            PaymentAllocation::create([
                'repayment_id' => $repayment->id,
                'amount' => $allocated,
                'transaction_id' => $transactionId,
                'source' => $source,
            ]);
            
            // 5. Deduct from remaining payment
            $remaining -= $allocated;
            
            // 6. Check if installment is now fully paid
            if ($this->isRepaymentFullyPaid($repayment)) {
                $repayment->update(['status' => 'paid']);
            }
        }
    }
    
    // 7. Return result
    return ['paid_amount' => $paid - $remaining];
}
```

### Example Allocation Scenario

**Scenario:** User makes a KSh 6,000 payment

**Before Payment:**
```
Loan #42 (Outstanding: KSh 10,000)
├─ Repayment #1 (Due: 2026-08-03): KSh 2,500, Status: pending
├─ Repayment #2 (Due: 2026-09-03): KSh 2,500, Status: pending
├─ Repayment #3 (Due: 2026-10-03): KSh 2,500, Status: pending
└─ Repayment #4 (Due: 2026-11-03): KSh 2,500, Status: pending
```

**During Allocation (Oldest-First):**
1. **Repayment #1** receives KSh 2,500 → **FULLY PAID** (6,000 - 2,500 = 3,500 remaining)
2. **Repayment #2** receives KSh 2,500 → **FULLY PAID** (3,500 - 2,500 = 1,000 remaining)
3. **Repayment #3** receives KSh 1,000 → **PARTIALLY PAID** (1,000 - 1,000 = 0 remaining)
4. **Repayment #4** receives KSh 0 → **NOT TOUCHED** (no remaining)

**After Payment:**
```
Loan #42 (Outstanding: KSh 4,000)
├─ Repayment #1 (Due: 2026-08-03): KSh 2,500, Status: paid ✓
├─ Repayment #2 (Due: 2026-09-03): KSh 2,500, Status: paid ✓
├─ Repayment #3 (Due: 2026-10-03): KSh 2,500, Status: partial (1,500 remaining)
└─ Repayment #4 (Due: 2026-11-03): KSh 2,500, Status: pending
```

**PaymentAllocation Audit Records Created:**
```
├─ PaymentAllocation #1: Repayment #1, Amount: 2,500, Source: "Historical import payment"
├─ PaymentAllocation #2: Repayment #2, Amount: 2,500, Source: "Historical import payment"
└─ PaymentAllocation #3: Repayment #3, Amount: 1,000, Source: "Historical import payment"
```

---

## Key Data Models

### User Model
```php
class User extends Model {
    public $fillable = ['name', 'national_id', 'id_number', ...];
    public function loans() { return $this->hasMany(Loan::class); }
}
```

### Loan Model
```php
class Loan extends Model {
    public $fillable = ['user_id', 'amount', 'status', ...];
    public function repayments() { return $this->hasMany(Repayment::class); }
    public function getOutstandingBalanceAttribute() { ... }
}
```

### Repayment Model
```php
class Repayment extends Model {
    public $fillable = ['loan_id', 'due_date', 'amount_due', 'status', ...];
    public function allocations() { return $this->hasMany(PaymentAllocation::class); }
}
```

### PaymentAllocation Model
```php
class PaymentAllocation extends Model {
    public $fillable = ['repayment_id', 'amount', 'transaction_id', 'source', ...];
    public function repayment() { return $this->belongsTo(Repayment::class); }
}
```

---

## Dry-Run Mode vs Production Mode

### Dry-Run Mode (`--dry-run` flag)

**What It Does:**
- Reads all transactions
- Matches users and loans
- Checks if payment can be allocated (outstanding balance > 0)
- **Does NOT** create any records
- Shows what WOULD happen

**Output Example:**
```
=== Payment Import (DRY RUN - NO CHANGES) ===
File: transactions.json
Format: JSON
Mode: DRY RUN
Total transactions: 5

Transaction #1:
  User Ref: 13743953 | Amount: KSh 1,500.00 | Name: ROSEMARY
  ✓ User found: ROSEMARY (ID: 42, National ID: 13743953)
  Active loans: 1
    - Loan #100: KSh 8,500.00 outstanding
  ✓ Payment would be ALLOCATED:
    Amount: KSh 1,500.00
    Total payable across 1 loan(s): KSh 8,500.00
    → Loan #100, Repayment #1: KSh 1,500.00

Transaction #2:
  User Ref: 31942157 | Amount: KSh 1,500.00 | Name: MERCY
  ❌ UNMATCHED: No user found for bill ref '31942157'

=== DRY RUN SUMMARY ===
Total Processed: 5
Matched: 4
Unmatched: 1
Errors: 0

⚠ This was a DRY RUN. No payments were recorded.
Run without --dry-run to apply payments.
```

### Production Mode

**What It Does:**
- Same matching and validation as dry-run
- Callback-shaped CSV payloads are dispatched to `ProcessC2bNonStkConfirmation`, matching the live C2B confirmation queue procedure.
- Ordinary legacy CSV rows continue through `RepaymentService::recordLoanPayment()`.
- Creates `PaymentAllocation` records
- Creates/updates `Transaction` records
- Logs to Laravel logs and database
- Marks repayments as paid/partial
- Uses idempotency by `TransID`; retrying a processed payload does not apply it twice.
- For `legacy_import = true`, increments `legacy_paid` by the amount actually allocated.

**Output Example:**
```
=== Payment Import (PRODUCTION) ===
File: transactions.json
Format: JSON
Mode: PRODUCTION
Total transactions: 5

Transaction #1:
  ✓ Row 2 (User: ROSEMARY): Applied KSh 1,500.00 to Loan #100

Transaction #2:
  ❌ Row 3 (User: MERCY): User not found
  
[...]

=== SUMMARY ===
Total Processed: 5
Matched: 4
Unmatched: 1
Errors: 0
Total Amount Applied: KSh 6,000.00

✓ Import completed. All payments recorded.
```

**Audit Trail:**
- Laravel logs: `storage/logs/laravel.log`
  ```
  [2026-07-03 15:23:57] local.INFO: Payment imported via script {"file":"transactions.json","user_id":42,"loan_id":100,"amount":1500,"transaction_id":"UG1HP9IR22"}
  ```

- Database:
  - `transactions` table: New transaction record
  - `payment_allocations` table: One record per installment that received allocation
  - `repayments` table: `status` updated to `paid` or kept as is based on remaining balance

---

## CSV Format Requirements

### Required Columns
- **ID Number** (or `Id Number`, `id_number`) — User's national ID
- **Paid** (or `paid`) — Payment amount (can include currency symbols/formatting)
- **Principal** (or `principal`) — Optional, for precise loan matching

### Optional Columns
- Any other columns are ignored

### Example CSV
```csv
ID Number,Principal,Paid,Notes
27345893,10000.00,2500.00,Import batch 1
28456104,15000.00,3000.00,Import batch 1
29567215,20000.00,5000.00,Import batch 1
```

**Notes:**
- Amounts can have currency symbols: `"KSh 2,500.00"` → parsed as `2500.00`
- Empty `Paid` fields are skipped
- Missing `ID Number` causes row to be skipped

### CSV Rows Containing Callback Payloads

CSV exports may contain a `payload`, `Payload`, `json`, `JSON`, `callback_data`, or `CallbackData` column containing one compacted JSON object or an array of JSON objects. Each object is expanded into one payment before processing.

Callback-shaped CSV payloads use the same fields and queue path as live C2B confirmations:

```json
{
  "TransID": "UHGH738Q8E",
  "TransTime": "20260816112203",
  "TransAmount": "1600.00",
  "BillRefNumber": "6204208"
}
```

When `TransID` is present, production import dispatches one `ProcessC2bNonStkConfirmation` job per payload. CSV rows without callback fields continue through the existing direct/legacy importer path.

Start a queue worker before applying callback-shaped CSV payments:

```bash
php artisan queue:work --tries=3 --timeout=120
```

The importer reports these rows as queued. The final applied or suspense result is written by the queue job and can be verified in the admin C2B transactions report.

---

## JSON Format Requirements (Relay-Box Export)

### Required Fields (in payload)
- **BillRefNumber** — User's reference (mapped to national_id via normalization)
- **TransAmount** — Payment amount
- **TransID** — M-Pesa transaction ID (for audit trail)

### Optional Fields
- **TransTime** — Transaction timestamp
- **FirstName** — User name (for logging)
- **MSISDN** — Phone number (hashed in relay-box export)

### Example JSON Structure
```json
[
  {
    "id": 352,
    "type": "confirmation",
    "status": "forwarded",
    "created_at": "2026-07-01T02:59:24.000000Z",
    "payload": {
      "TransactionType": "Pay Bill",
      "TransID": "UG1HP9IR22",
      "TransTime": "20260701055923",
      "TransAmount": "1500.00",
      "BusinessShortCode": "787306",
      "BillRefNumber": "13743953",
      "FirstName": "ROSEMARY"
    }
  },
  {
    "id": 353,
    ...
  }
]
```

---

## Error Handling & Validation

### Validation Checks
1. **File exists** — Script exits if file not found
2. **File format** — Must be `.csv` or `.json`
3. **JSON validity** — Invalid JSON exits with error
4. **User reference** — Missing ID Number/BillRefNumber does not immediately fail the row when the field is `null`; the validator accepts that case and the runtime flow handles it via the unmatched-user suspense path. A blank string is still treated as a missing reference and remains invalid.
5. **Payment amount** — Must be > 0, otherwise skipped/errored
6. **User exists** — No matching user → unmatched count
7. **Loan exists** — No active/recent loan → unmatched count
8. **Schedule generation** — Fails if schedule generation throws exception

### Error Output
```
❌ Row 2: User not found
⚠ Row 3: No outstanding balance on Loan #100
❌ Row 4: Invalid amount (KSh 0)
```

### Why Some Errors Are Expected

**Unmatched Users (BillRefNumber Not Found):**
- Relay-box exports may include test transactions
- Users may be deleted since transaction occurred
- BillRefNumber formatting may not match any user
- A `null` `BillRefNumber` is valid input for the validator and should follow the same unmatched-to-suspense path rather than being rejected upfront
- A blank string is still treated as a missing reference and should be flagged during validation

**Solution:**
- Review unmatched BillRefNumbers manually
- Check if they should be force-mapped to specific users
- Consider creating missing users if they're valid
- For null references, keep the record for manual review in `suspense_transactions` instead of discarding it as invalid
- For blank references, fix the source data or reject the record before import

---

## Repayment Status Tracking

### Status Values
- **pending** — Installment has a positive remaining balance
- **overdue** — Pending installment past its due date
- **paid** — Installment balance is fully allocated

### Outstanding Balance Calculation
```php
// Main repayment balance used for legacy import decisions
$outstanding = Repayment::where('loan_id', $loanId)
  ->whereIn('status', ['pending', 'overdue'])
  ->sum('balance');
```

For `legacy_import = true` loans, the persisted `loans.outstanding_balance` field may be stale after historical import. The importer therefore uses the positive `balance` values on `pending` and `overdue` repayment rows. For loan `#9618`, this produces KSh 44,500.00 and the import allocates payments to installments instead of routing them to guarantee deposit.

### Payment Impact on Loan Status
- When last repayment is marked `paid`, loan `status` may change to `completed`
- Partial payments keep loan in `active` status
- Audit trail in `payment_allocations` shows exactly which repayments were hit

---

## Running a Real Import

### One-Time Legacy Paid Reconciliation

The old allocation logic subtracted new M-Pesa allocations from `legacy_paid`. The correction must therefore add the affected allocation amount back once to restore the missing increment. Use the dedicated script only with a cutoff covering payments processed before the increment fix, and do not rerun `--apply` for the same cutoff:

```bash
php scripts/reconcile_legacy_paid_from_history.php --before="YYYY-MM-DD HH:MM:SS" --apply
```

The script uses `payment_allocations` rows with a non-null M-Pesa transaction ID, excludes the original legacy-import allocations, and reports every changed loan. New payments after the fix increment `legacy_paid` normally through `RepaymentService`.

### Step 1: Prepare Your Data
```bash
# CSV: Ensure columns are correct
# - ID Number (national_id)
# - Principal (optional but recommended)
# - Paid (payment amount)

# JSON: Export from relay-box with all transaction details
```

### Step 2: Validate with Dry-Run
```bash
php -d memory_limit=512M import_payments.php "path/to/file.csv" --dry-run --verbose
```

Review the output:
- How many users were matched?
- How many were unmatched?
- Are the loan selections correct?
- Will payments allocate correctly?

### Step 3: Go to Production
```bash
php -d memory_limit=512M import_payments.php "path/to/file.csv"
```

### Step 4: Verify
- Check Laravel logs for import records
- Spot-check user repayment schedules
- Verify total amount applied matches expected
- Review `payment_allocations` table for audit trail

---

## Common Issues & Solutions

### Issue: "User not found"
**Cause:** BillRefNumber/ID Number doesn't match any user's national_id or id_number

**Solution:**
1. Check spelling in CSV/JSON
2. Verify user exists in database
3. Check if national_id field is populated
4. Try different padding formats manually

### Issue: "No loan found"
**Cause:** User has no loans, or all loans are in non-active status

**Solution:**
1. Verify user should have a loan
2. Check if loan status is set correctly
3. Create missing loans if needed
4. Run import after fixing user/loan data

### Issue: "No outstanding balance on Loan"
**Cause:** Loan is already fully paid, or scheduled repayments don't exist

**Solution:**
1. Check if this loan should receive more payments
2. Verify repayment schedule was generated
3. Consider if this payment should go to a different loan

### Issue: Import Too Slow
**Cause:** Large file (thousands of rows) with slow database

**Solution:**
```bash
# Increase memory limit
php -d memory_limit=2G import_payments.php "large_file.json"

# Consider batch processing (split into smaller files)
```

---

## Related Services & Controllers

### RepaymentService
- `recordLoanPayment($loan, $amount, $source)` — Main allocation method
- `recordPaymentForUserActiveLoans($user, $amount, ...)` — Allocates across all user's active loans

**File:** [app/Services/RepaymentService.php](../app/Services/RepaymentService.php)

### LoanService
- `generateRepaymentSchedule($loan)` — Creates individual repayment records

**File:** [app/Services/LoanService.php](../app/Services/LoanService.php)

### C2bUserMatchingService
- `findUserByBillRef($billRef)` — Smart normalization & lookup for JSON

**File:** [app/Services/C2bUserMatchingService.php](../app/Services/C2bUserMatchingService.php)

### ProcessC2bNonStkConfirmation Job
- Production queued job that processes relay-box confirmations
- Uses same `C2bUserMatchingService` and `RepaymentService`

**File:** [app/Jobs/ProcessC2bNonStkConfirmation.php](../app/Jobs/ProcessC2bNonStkConfirmation.php)

---

## Database Schema Relevant to Import

### users table
```sql
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(255),
  national_id VARCHAR(50) UNIQUE,
  id_number VARCHAR(50),
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);
```

### loans table
```sql
CREATE TABLE loans (
  id BIGINT PRIMARY KEY,
  user_id BIGINT FOREIGN KEY,
  amount DECIMAL(12,2),
  status ENUM('active', 'completed', 'defaulted', 'cancelled'),
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);
```

### repayments table
```sql
CREATE TABLE repayments (
  id BIGINT PRIMARY KEY,
  loan_id BIGINT FOREIGN KEY,
  due_date DATE,
  amount_due DECIMAL(12,2),
  status ENUM('pending', 'overdue', 'paid'),
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);
```

### payment_allocations table (Audit Trail)
```sql
CREATE TABLE payment_allocations (
  id BIGINT PRIMARY KEY,
  repayment_id BIGINT FOREIGN KEY,
  amount DECIMAL(12,2),
  transaction_id VARCHAR(255),
  source VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);
```

---

## Appendix: Script Location & Backup

**Current Script:**
- Location: [import_payments.php](../import_payments.php)
- Unified script supporting CSV and JSON

**Legacy Backups:**
- [storage/app/backup_import_scripts/apply_csv_payments.php](../storage/app/backup_import_scripts/apply_csv_payments.php) — CSV-only original
- [dry_run_relay_box_json.php](../dry_run_relay_box_json.php) — JSON-only dry-run script

---

## Summary

The payment import system is designed to be:
- **Flexible:** Supports both CSV and JSON (relay-box) formats
- **Safe:** Dry-run mode lets you preview before applying
- **Smart:** Handles user reference normalization and loan selection
- **Audited:** Complete PaymentAllocation trail for every allocated amount
- **Reliable:** Oldest-first allocation ensures consistent behavior

By running a dry-run first, you can validate that your data will allocate correctly before executing the production import.
