# Settlement Pipeline — End-to-End Test Plan (held-transaction visibility)

**Status: PLAN ONLY — not yet implemented. Awaiting confirmation before seeding anything.**
**Branch:** `test/settlement-summary-e2e` (off `main`, includes PR #110's held/summary work)

## What we're validating

The workflow change from the last session: risk-held and reconciliation-exception
transactions must now appear in MIS and MPR as **informational-only rows** — visible,
but excluded from every money total (net payable, payout, VAT base) — and once
released, must be picked up automatically by the **next** MIS generation via
`released_for_settlement_date`, without re-running anything for the original date.

Chargeback holds are **deliberately not part of this held-visibility mechanism**
(handled via the adjustment workflow instead, per an explicit code comment in
`settlement_mis_generator.ex`) — we're seeding one anyway to confirm it behaves as
excluded, not to demand it becomes visible too.

Pipeline under test, end to end:
`core_transactions (seeded directly) → recon (match/exception) → risk hold → adjustments
→ MIS generation → MIS Details/Excel → L1/L2 approval → payout generation → MPR
dispatch → payout transmission → bank confirmation → settlement advice → VAT invoice`
→ separately, → release → next-day MIS pickup.

## Why we can skip pos_transactions/transactions/pos_settlements/core_sync

Confirmed via code read: `core_transaction_sync.ex`'s BIN backfill (`backfill_card_type_from_bin/2`)
joins on columns (`account_range_from`/`account_range_to`) that don't exist on `bin_table`
at all — it silently fails and is caught non-fatally. MDR resolution is keyed on
`merchant_mid + card_type_id` only (no MCC fallback, no live BIN lookup feeding it), and
the resolved `mdr_percentage_rate/mdr_amount/vat_amount/net_settlement_amount` are written
**directly onto `core_transactions`** at sync time — nothing downstream re-derives them.
So seeding those final columns ourselves, with realistic values, is equivalent to a
successful sync — no BIN table changes needed despite it only holding Mastercard data.

## Risk-hold rows need a real, chain-resolvable `risk_rule_hits` row (Bug E)

Superseding the earlier assumption below (test scenarios M1-3, M2-4, M3-1, M3-3, previously
"no `risk_rule_hits` row needed — confirmed equivalent"): every seeded RISK_HOLD row must
now also get a matching `risk_rule_hits` row, with `transaction_id` pointing to a real,
walkable source-table id — not an arbitrary/unrelated number. Two reasons:

1. `/admin/settlements/risk-holds` (`RiskHoldReview.load_risk_held/0`) sources its rows
   entirely from `risk_rule_hits WHERE status == "Hold"` — never from `core_transactions`
   directly. A held row with no `risk_rule_hits` counterpart is simply invisible on that
   page, which is exactly why this session's earlier seeded rows never appeared there.
2. Bug E's fix depends on resolving `risk_rule_hits.transaction_id` through the *real*
   id chain back to a `core_transactions` row (see `SETTLEMENT_E2E_BUGS_FOUND.md`, Bug E).
   A `risk_rule_hits` row seeded with a disconnected/fake id would exercise nothing —
   the resolver needs a genuine chain to walk, or the test proves the wrong thing.

**What that means per channel:**
- **QR (2-hop):** seed one `transactions` row (id `T`). Set the merchant's
  `core_transactions.source_ref_id = T`, `source_type = "QR"`. Seed
  `risk_rule_hits.transaction_id = T`, `transaction_type = "QR"`, `status = "Hold"`.
- **POS (3-hop):** seed one minimal `pos_transaction` row (id `P`) **and** one minimal
  `pos_settlements` row (id `S`, `pos_transaction_id = P`, `settlement_status = "settled"`).
  Set `core_transactions.source_ref_id = S`, `source_type = "POS"`. Seed
  `risk_rule_hits.transaction_id = P` (not `S`, not the `core_transactions.id`) —
  `transaction_type = "POS"`, `status = "Hold"`.

Use `rule_id: nil, category: "Manual"` for a quick, valid `risk_rule_hits` row (mirrors a
manually-flagged hold) — no need to seed real `risk_rules` data just for this. Optionally
seed at least one hit with a real `rule_id` too (mirrors an automated hit) so the release
fix is proven to work regardless of how the hold originated, since manual vs. automated
hits currently disagree on what `transaction_id` even means (part of Bug E's fix).

This applies to M1 row 3, M2 row 4, and M3 rows 1 & 3 below — each now needs its small
source-chain stub in addition to the `core_transactions` row already described.

## Test data design

**Isolation:** everything tagged `settlement_batch_id = 'E2E_TEST_20260829'` for easy
cleanup (`DELETE FROM core_transactions WHERE settlement_batch_id = 'E2E_TEST_20260829'`,
plus matching `switch_dump_records`/`reconciliation_exceptions`/`merchant_adjustments`
rows, same pattern the repo's own `trigger_reconciliation.exs`/prior sessions used).

**Day D (original transaction date): `2026-08-29`** — currently empty, no collision with
existing tagged test data (`2026-08-06`, `2026-08-17`, `2026-08-30` are all already used).
**Day D+1: `2026-09-01` (today)** — release actions stamp `released_for_settlement_date`
to `Date.utc_today()`, so running the release step today naturally produces D+1 = today;
no date mocking needed.

### Merchants (4, for scheme/scenario diversity)

**All 4 merchants get `registered_email = sunny@dataaegis.com`** — confirmed this is the
field `MprDispatchWorker`, `SettlementAdviceWorker`, and `VatInvoiceWorker` all read for
the notification recipient, so every email this test triggers is actually checkable.
M1 and M2 already have it set from earlier sessions; M3/M4 will be created with it.

| # | Merchant | mid | Role in test |
|---|---|---|---|
| M1 | ABC Electronics (existing, id 5) | `419926360000000` | Has a real 2.75% MDR config already — main "everything happens correctly" merchant: matched + risk hold + exception hold + chargeback hold + adjustment, mixed VISA/MASTERCARD |
| M2 | Golden Sands Mart (existing, id 8) | `425590250000000` | Recon exception focus: clean match, 5.2 (core-no-dump), 5.1 (dump-no-core), plus a risk-hold-with-matching-dump negative case, plus AMEX transactions |
| M3 | **new merchant, created fresh** | e.g. `TEST_E2E_M3` | 100%-held day (no matched rows at all) + an approved adjustment — deliberately targets the still-open Bug 4 edge case |
| M4 | **new merchant, created fresh** | e.g. `TEST_E2E_M4` | 5.3 (VOID matched in dump) + 5.4 (duplicate dump row) + an amount-mismatch negative case + an adjustment dated a *different* month — targets the still-open AR-recovery bleed gap |

### Card types (from `shukria_mms.card_types`, no BIN table involvement needed)
VISA: `VISA_CREDIT_STD` (id 6), `VISA_DEBIT_STD` (id 1). MASTERCARD: `MC_CREDIT_STD` (id 9),
`MC_DEBIT_STD` (id 3). AMEX: `AMEX_CREDIT` (id 12).

### Per-merchant transaction scenarios (Day D = 2026-08-29)

**M1 (ABC Electronics)** — 6 rows:
1. MATCHED (VISA, credit) — dump row seeded to match → clean recon match
2. MATCHED (MASTERCARD, debit) — dump row seeded to match
3. RISK_HOLD (MASTERCARD) — seeded directly `risk_hold=true, settlement_status='risk_hold', risk_rule_name='AUTO_RISK_FLAG'`, **plus a chain-resolvable `risk_rule_hits` row (see "Risk-hold rows need a real, chain-resolvable `risk_rule_hits` row (Bug E)" above)** — this is the row used for the single-release-via-real-UI test in step 14
4. EXCEPTION_HOLD via recon 5.2 — seeded `settlement_status='unmatched'`, no dump row → recon flips it
5. CHARGEBACK_HOLD — seeded directly `chargeback_hold=true, settlement_status='chargeback_hold'` (expect: absent from held-visibility popup/Excel, present only via adjustment workflow if any)
6. Adjustment: one **approved** CREDIT adjustment dated 2026-08-29 → verify it lands once, on the matched row only (Bug 2 regression), not on rows 3-5 (Bug 4 regression)

**M2 (Golden Sands Mart)** — 4 rows + dump-only orphan:
1. MATCHED (AMEX) — clean match
2. Seeded `unmatched`, no dump row → recon 5.2 exception
3. A `switch_dump_records` row with **no matching core_transactions row** (orphan RRN/TID) → recon 5.1 exception
4. RISK_HOLD (AMEX), **with a matching `switch_dump_records` row also seeded for it**, **plus a chain-resolvable `risk_rule_hits` row (Bug E — see above)** — for the bulk-release test later, and doubles as a negative case: recon's `update_core_transactions/1` explicitly excludes `risk_hold=true` rows, so this must **stay `risk_hold`**, not flip to `matched`, despite a valid dump match existing

**M3 (new merchant)** — 3 rows, ALL held, zero matched:
1. RISK_HOLD, **plus a chain-resolvable `risk_rule_hits` row (Bug E — see above)** — use the
   real-`rule_id` variant here (mirrors an automated hit), M1's uses `rule_id: nil` (manual)
2. EXCEPTION_HOLD (via recon 5.2)
3. RISK_HOLD, **plus a chain-resolvable `risk_rule_hits` row (Bug E — see above)**
4. Adjustment: one **approved** DEBIT adjustment dated 2026-08-29 → **expected to reproduce the known Bug 4 gap** (adjustment likely lands on a held row since there's no matched row to claim index 0) — this is a documentation/verification scenario, not assumed-passing

**M4 (new merchant)** — 4 rows:
1. MATCHED (VISA) with `transaction_type='VOID'`, dump row matches it → recon 5.3 exception
2. Two `switch_dump_records` rows with the same `(rrn, tid)` for the same source/channel → recon 5.4 duplicate-dump exception
3. **Negative case — amount mismatch:** a core row (MASTERCARD, amount AED 500.00) with a dump row for the same `rrn`/`tid` but amount AED 505.00 (off by more than the 0.01 tolerance) → must **NOT** match; expect it to produce a 5.1 (dump orphan) and a 5.2 (core orphan) simultaneously, not a false match
4. Adjustment: one **approved** adjustment dated a different month (e.g. `2026-07-15`) → confirm it correctly stays OUT of Day D's `build_adjustments_map` (main columns, fixed), and separately check whether it incorrectly leaks into `build_ar_recovery_map`'s AR Recovery figure (expected still-open gap per Bug 3)

### Negative scenarios (should-NOT-happen checks, grounded in what the code actually guards)

Woven into the merchant scenarios above, plus two adjustment-workflow cases below —
all of these assert something must **fail, get excluded, or stay unchanged**, not succeed:

1. **Risk-held txn with a valid matching dump row must not get auto-matched by recon** (M2 row 4).
2. **Amount mismatch beyond tolerance must not match** — produces 5.1+5.2 instead of a false match (M4 row 3).
3. **A PENDING (not-yet-approved) adjustment must never appear in MIS/Excel or AR Recovery** — seed one extra adjustment on M2 dated Day D and deliberately leave it at `approval_status: "pending"` (skip L1/L2 approve for this one only).
4. **A REJECTED adjustment must never appear anywhere and must not block MIS generation** — seed a second adjustment on M1, run `Context.reject_adjustment/3` on it, confirm the MIS/Excel are unaffected.
5. **Chargeback-hold must NOT appear in held-visibility** (M1 row 5) — confirms the "handled via adjustments, not this mechanism" exclusion is intentional and still holds.
6. **Double-release must not corrupt state** — call `RiskReleaseProcessor.release/2` a second time on M1's already-released risk hold (step 14) and confirm it doesn't re-stamp `released_for_settlement_date` to a later date, error unsafely, or duplicate the transaction into a third MIS.
7. **L2-approve without L1-approve first** — attempt `l2_approve_settlement_mis/3` directly on the fresh MIS before calling `l1_approve_settlement_mis/3`, confirm it's rejected rather than silently allowed to skip a review stage.
8. **Unmatched/exception rows must never enter payable totals** — M2 row 2 (5.2 exception) and M4 row 3 (mismatch orphan) must be fully absent from MIS totals, payout amounts, and VAT invoice base despite technically existing in `core_transactions` for the date.

Total: ~19 core_transactions rows + 2 extra adjustments, 4 merchants, all 4 recon exception
types (5.1-5.4), risk hold, exception hold, chargeback hold, and 5 adjustment scenarios
(approved×3, pending×1, rejected×1) covering both the fixed paths, the two still-open
gaps, and the 8 negative checks above.

## Execution sequence

1. **Seed merchants** — insert M3/M4 into `tid_masters` with realistic `mdr_rates` JSON (for display consistency), `enabled_schemes`, etc.
2. **Seed core_transactions** — direct `Repo.insert_all`, final MDR/VAT/net values computed by us (percentage × amount + fixed fee, matching `calculate_fee/2`'s formula) so numbers are internally consistent. For the RISK_HOLD rows (M1-3, M2-4, M3-1, M3-3), also seed the minimal source-chain stub (`pos_transaction`+`pos_settlements` or `transactions`, per channel) and a matching `risk_rule_hits` row, per "Risk-hold rows need a real, chain-resolvable `risk_rule_hits` row (Bug E)" above — needed before step 7/14 can test the Risk Hold Review page for real.
3. **Seed switch_dump_records + a `dump_files` row** (`status: "processed"`) for the matched/5.1/5.3/5.4 scenarios.
4. **Run reconciliation** — `SettlementCore.ReconciliationEngine.run(dump_file)` called synchronously in a `mix run` script (not via Oban enqueue) for a deterministic, immediately-inspectable result. Verify: `switch_dump_records.match_status`, `core_transactions.settlement_status`/`switch_settled_date`, and `reconciliation_exceptions` rows (types 5.1/5.2/5.3/5.4) all land as expected — **including negative checks 1 and 2** (M2's risk-held row must stay `risk_hold`, M4's mismatched pair must not match).
5. **Create + approve adjustments** — `Context.create_adjustment/1` → `l1_approve_adjustment/3` → `l2_approve_adjustment/3` for the 3 approved scenarios (M1 credit, M3 debit, M4 different-month), plus the 2 negative ones: one left `pending` (M2, negative check 3) and one pushed through `reject_adjustment/3` (M1, negative check 4).
6. **Generate MIS for 2026-08-29** — via the `POST /api/v1/settlements/mis/generate` endpoint (real trigger, not a shortcut) or `MisGenerationWorker` directly. Also generate MIS for an empty date (e.g. `2026-08-28`, no seeded data) as a quick sanity check that zero-eligible-transactions doesn't crash the generator.
7. **Verify MIS Details popup** (`/admin/settlements/mis`) — held rows show `is_held: true`, zeroed amounts, correct exclusion from merchant totals. Confirm the pending and rejected adjustments (negative checks 3-4) are simply absent, not zeroed-but-present.
8. **Download MIS Excel** — verify: chargeback-hold row present as informational (still excluded from totals; confirm no held-visibility crossover — negative check 5), M1's adjustment on exactly one row, M3's adjustment behavior (documenting actual vs. expected), M4's off-month adjustment absent from main columns but check AR Recovery column for the bleed, negative check 8 (M2/M4's exception rows contribute nothing to any total).
9. **Attempt L2-approve before L1-approve** (negative check 7) — confirm it's rejected. Then **L1 approve → L2 approve the MIS** properly — confirms `PayoutTransmissionWorker` + `MprDispatchWorker` get enqueued.
10. **Payout generation** (`PayoutGenerator.generate_for_mis/1`) — confirm held/informational transactions never enter `PayoutItem` totals (M3 in particular: a merchant with zero matched transactions should generate $0 or no payout at all — verify which, since it's a merchant that's *entirely* held).
11. **MPR dispatch** — confirm `hold_items` (tagged `"PENDING"`) appear in the MPR output for M1/M2/M3, separate from `txn_count`/totals, and that `list_held_only_merchants/2` correctly surfaces M3 as a held-only merchant still getting a $0 MPR.
12. **Bank confirmation** — upload/approve via `BankConfirmationService.approve_and_process_batch/2`, confirm `SettlementAdviceWorker` + `VatInvoiceWorker` get enqueued per `PayoutItem`.
13. **Settlement advice + VAT invoice** — trigger both workers directly, inspect generated PDFs for correctness (held txns should never appear here — these are payment-confirmation documents, not MIS/MPR).
14. **Release held transactions (today, 2026-09-01)**:
    - M1's risk hold → **via the Risk Hold Review UI's "Release" button** (real browser flow,
      not IEx) — this is the actual Bug E regression test; confirm it resolves to the right
      `core_transactions` row and releases cleanly instead of crashing/misfiring. Fall back
      to `RiskReleaseProcessor.release/2` directly only if the UI path is still broken and
      you need to unblock the rest of the sequence.
    - M2's risk hold → `RiskReleaseProcessor.bulk_release_before/1` (bulk) — unaffected by
      Bug E (bulk already resolves by `merchant_mid` directly), no change needed here
    - M1's exception hold → `ExceptionReleaseProcessor.release/3` **via the Exception Dashboard UI** (real browser flow, not IEx)
    - M3's exception hold → `ExceptionReleaseProcessor.bulk_release/3` (bulk)
    - Verify each: `released_for_settlement_date = 2026-09-01`, `switch_settled_date` untouched, `settlement_status` flips appropriately.
    - **Negative check 6:** call `RiskReleaseProcessor.release/2` a second time on M1's already-released risk hold — confirm no re-stamping, error, or duplicate downstream pickup.
15. **Generate MIS for 2026-09-01** — verify the just-released transactions (and only those — no unrelated same-day noise) are now included in totals via the `released_for_settlement_date` clause, with correct MDR/VAT math carried over from their original values.
16. **Settlement Summary dashboard cross-check** (`/admin/settlements/summary`, filtered to 2026-08-29) — KPI "Held Transactions" count matches seeded risk+exception rows, "Open Exceptions" matches the 4 reconciliation_exceptions rows, pivot table totals exclude all held rows, both charts render.

## Known still-open gaps this plan will surface (not fixed here unless you ask)
- Bug 4 edge case: adjustment landing on a held row when a merchant has zero matched transactions that day (M3 scenario).
- Bug 3 residual: `build_ar_recovery_map` still bleeding cross-date/cross-MIS adjustments into the AR Recovery figure (M4 off-month scenario).

## Cleanup
Single batch tag (`E2E_TEST_20260829`) plus the two new test merchant mids makes this
fully reversible: delete `core_transactions`, `switch_dump_records`, `reconciliation_exceptions`,
`merchant_adjustments`, `settlement_mis`/`settlement_mis_items`, `payout_batches`/`payout_items`,
and the two new `tid_masters` rows, all scoped to this batch/date/mids.