# Settlement & Reconciliation — Phased Implementation Plan

> **Branch:** `feature/settlement-recon-core`
> **Base requirement:** `docs/settlement_recon/Settlement_reports_completion.txt`
> **Plan date:** March 6, 2026
> **Total phases:** 7

---

## How to Read This Document

Each phase contains:
- **Goal** — what business capability is unlocked after this phase completes
- **Dependencies** — what must be done before this phase starts
- **Tickets** — individual implementation tasks with module/file targets and effort estimates
- **Acceptance criteria** — how to verify the phase is complete

Effort is estimated in **person-days (PD)**.

---

## Phase 1 — Data Foundation & Schema Completion

**Goal:** Complete the data model so every downstream process has the fields it needs.
**Dependency:** None — can start immediately.
**Target completion:** 1–2 weeks

### 1.1 TID Master — New unified schema

The requirements specify a single TID Master per terminal. Currently merchant data is fragmented across `shukria_mms` tables. MMS is the **source of truth** — the sales/onboarding team onboards merchants there. Only merchants whose `contract_approval_status = 'approved'` in the MMS `users` table are permitted to flow into `tid_masters`.

#### MMS data source mapping

| `tid_masters` field | Source MMS table | Source column / logic |
|---|---|---|
| `mms_user_id` | `users` | `users.id` |
| `mid` | `merchant_metadata` | `merchant_id` |
| `merchant_ref_number` | `merchant_metadata` | `merchant_refrence_number` |
| `store_id` | `merchant_stores` | `id WHERE is_primary = 1` |
| `merchant_name_legal` | `users` | `name` |
| `merchant_name_dba` | `merchant_stores` | `dba_name WHERE is_primary = 1` |
| `registered_mobile` | `users` | `phone` |
| `registered_email` | `users` | `email` |
| `mcc_code` | `users` | `mcc_id` → join to mcc table |
| `bank_code` | `merchant_metadata` | `bank_code` |
| `bank_account_number` | `users` | `meta->>'bank_account_number'` |
| `iban` | `users` | `meta->>'iban'` |
| `virtual_iban` | `users` | `meta->>'virtual_iban'` |
| `enabled_schemes` | `users` | `products` (jsonb array) |
| `mdr_rates` snapshot | `mdr_templates` + `mdr_rates` | Active template for merchant + card_type rates |
| `mcc_description` | `mdr_templates` | `sub_mcc` |
| `onboarding_date` | `users` | `contract_approved_at` |
| `contract_approved_at` | `users` | `contract_approved_at` |
| `tid` / `qr_id` | Local `pos_settlements` / `transactions` | Matched by `merchant_mid` after MMS record exists |

> `bank_account_number`, `iban`, `virtual_iban` live inside `users.meta` JSON in MMS. If the MMS team moves these to dedicated columns, update the sync query accordingly.

#### Data movement strategy

MMS approval triggers the gate. Two stages:

1. **Bootstrap (one-time):** Run at deployment. Queries MMS for all `users WHERE contract_approval_status = 'approved'`, joins `merchant_metadata` + `merchant_stores (is_primary=1)` + active `mdr_templates` + `mdr_rates`, and inserts into `tid_masters`. Also back-fills `tid` / `qr_id` from local `pos_settlements` / `transactions`.

2. **Ongoing sync (recurring Oban worker):** Polls MMS daily (or on-demand via API trigger) for `users WHERE contract_approval_status = 'approved' AND contract_approved_at > :last_sync_checkpoint` or `updated_at > :last_sync_checkpoint`. Upserts `tid_masters` on `mms_user_id`. Also re-snapshots `mdr_rates` when `mdr_templates.updated_at` has changed since last sync (handles rate changes).

**Tasks:**

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 1.1.1 | Create `tid_masters` migration. Fields: `mms_user_id` (unique, FK reference only), `mid`, `store_id`, `merchant_ref_number`, `tid`, `qr_id`, `merchant_name_legal`, `merchant_name_dba`, `bank_code`, `bank_account_number`, `iban`, `virtual_iban`, `enabled_schemes` (jsonb), `mdr_rates` (jsonb snapshot), `mcc_code`, `mcc_description`, `registered_mobile`, `registered_email`, `onboarding_date`, `contract_approved_at`, `mms_synced_at`, `last_transaction_date`, `last_transaction_amount`, `status`, `spare_fields` (jsonb). Unique index on `mms_user_id`; index on `mid`, `tid`. | `priv/repo/migrations/YYYYMMDD_create_tid_masters.exs` | 1 |
| 1.1.2 | Extend `SettlementCore.Mms.Merchant` schema (currently minimal) to map the full `users` table fields required for sync: `contract_approval_status`, `contract_approved_at`, `mcc_id`, `phone`, `meta` (jsonb), `products` (jsonb), `updated_at`. Add `Mms.MerchantStore` read-only schema for `merchant_stores` table. | `apps/settlement_core/lib/settlement_core/mms/merchant.ex`, `apps/settlement_core/lib/settlement_core/mms/merchant_store.ex` | 1 |
| 1.1.3 | Create `SettlementCore.TidMaster` Ecto schema with `changeset/2`, `mms_sync_changeset/2` (used by sync worker), and read helpers | `apps/settlement_core/lib/settlement_core/tid_master.ex` | 1 |
| 1.1.4 | Add `TidMaster` queries to `SettlementCore.Context`: `get_tid_master_by_tid/1`, `get_tid_master_by_mid/1`, `get_tid_master_by_mms_user_id/1`, `upsert_tid_master/1`, `update_last_transaction/3` | `apps/settlement_core/lib/settlement_core/context.ex` | 1 |
| 1.1.5 | Create `SettlementCore.Mms.ApprovalSync` module: `build_tid_master_attrs/1` (takes `mms_user_id`, queries MMS across `users` + `merchant_metadata` + `merchant_stores` + `mdr_templates` + `mdr_rates`, returns attrs map), `sync_one/1`, `sync_all_approved/0` (bootstrap), `sync_since/1` (checkpoint-based). This is the single place where MMS → `tid_masters` field mapping logic lives. | `apps/settlement_core/lib/settlement_core/mms/approval_sync.ex` | 2 |
| 1.1.6 | Create `SettlementCore.Workers.MmsApprovalSyncWorker` Oban periodic worker (queue `:settlements`, runs daily at 01:00 AM before any settlement pipeline): calls `ApprovalSync.sync_since/1` using `SyncLog` checkpoint. Also expose `POST /api/v1/merchants/:user_id/sync-to-tms` for immediate on-demand sync when a merchant is approved in MMS. | `apps/settlement_core/lib/settlement_core/workers/mms_approval_sync_worker.ex` | 1.5 |
| 1.1.7 | Bootstrap seed: calls `ApprovalSync.sync_all_approved/0` then back-fills `tid`, `qr_id` from local `pos_settlements` / `transactions` by matching on `merchant_mid` | `priv/repo/seeds/tid_master_seed.exs` | 1 |

**Acceptance criteria:**
- `tid_masters` table exists; only merchants with `contract_approval_status = 'approved'` in MMS are present
- `get_tid_master_by_tid/1` returns `iban` and `bank_account_number` needed by payout pipeline
- `MmsApprovalSyncWorker` runs daily; newly approved merchants appear in `tid_masters` within 24 hours (or immediately via on-demand API)
- When MDR rates change in MMS, `tid_masters.mdr_rates` snapshot is updated on next sync run
- Bootstrap seed populates all existing approved merchants with their `tid`/`qr_id`

---

### 1.2 Transaction Dump — Missing fields on `core_transactions`

**Tasks:**

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 1.2.1 | Add migration: `alter table core_transactions add column card_holder_name varchar(100)`, `customer_mobile varchar(20)`, `customer_country varchar(3)`, `reversal_status boolean default false`, `reversal_date date` | `priv/repo/migrations/YYYYMMDD_add_missing_fields_core_transactions.exs` | 0.5 |
| 1.2.2 | Update `CoreTransaction` schema and `status_changeset/2` to include new fields | `apps/settlement_core/lib/settlement_core/core_transaction.ex` | 0.5 |
| 1.2.3 | Update `PosSettlement` INSERT-INTO-SELECT raw SQL in `CoreTransactionSync` to populate new fields from source POS tables where available | `apps/settlement_core/lib/settlement_core/core_transaction_sync.ex` | 1 |

**Acceptance criteria:**
- `core_transactions` has `card_holder_name`, `customer_mobile`, `customer_country`, `reversal_status`, `reversal_date`
- POS sync populates these from source records

---

### 1.3 BIN Table & Interchange Rate — Load static data

> Both schemas are implemented; data load is pending YSP files (noted TODOs in code).

**Tasks:**

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 1.3.1 | Create `BinTableLoader` mix task: reads YSP-supplied CSV, validates columns, bulk-inserts into `bin_table`, deduplicates on `bin_prefix` | `lib/mix/tasks/load_bin_table.ex` | 1 |
| 1.3.2 | Create `InterchangeRateLoader` mix task: reads YSP interchange CSV, validates, bulk-inserts into `interchange_rates`, respects `effective_date`/`expiry_date` | `lib/mix/tasks/load_interchange_rates.ex` | 1 |
| 1.3.3 | Integrate BIN lookup into `CoreTransactionSync.insert_to_core/2` — populate `card_type_id` from BIN table for POS transactions where masked_pan is available | `apps/settlement_core/lib/settlement_core/core_transaction_sync.ex` | 1 |
| 1.3.4 | Integrate interchange rate into MIS item generation (Phase 4) — add `interchange_fee` field sourced from `InterchangeRate.rate_query/3` | Deferred to Phase 4 | 0 |

**Acceptance criteria:**
- `mix load_bin_table --file=path/to/bin.csv` runs without errors
- `mix load_interchange_rates --file=path/to/interchange.csv` runs without errors
- After sync, `core_transactions.card_type_id` is populated from BIN for card transactions

---

**Phase 1 Total: ~14 PD (~3 weeks)**

> Phase 1.1 increased from 5 PD to 8.5 PD due to addition of: `Mms.MerchantStore` schema (1.1.2), `ApprovalSync` module (1.1.5), and `MmsApprovalSyncWorker` + on-demand API (1.1.6). The one-time seed (1.1.7) is reduced because the heavy lifting now lives in `ApprovalSync.sync_all_approved/0`.

---

## Phase 2 — Dump File Ingestion Pipeline

**Goal:** Automated daily ingestion of YSP Switch Settled Dump and QR Scheme Settled Dumps into `switch_dump_records` / `qr_scheme_dump_records`. Must complete by 6:00 AM.
**Dependency:** Phase 1 complete (BIN table populated for card type resolution).
**Target completion:** 2–3 weeks

### 2.1 YSP Switch Dump — SFTP fetch

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 2.1.1 | Create `SettlementCore.Ysp.SftpClient` — Erlang `:ssh_sftp` wrapper mirroring `AlipayPlus.SftpClient`. Config keys: `ysp_sftp_host`, `ysp_sftp_port`, `ysp_sftp_user`, `ysp_sftp_password/key_path`, `ysp_sftp_base_dir` | `apps/settlement_core/lib/settlement_core/ysp/sftp_client.ex` | 1 |
| 2.1.2 | Create `SettlementCore.Ysp.SftpFetcher` Oban worker: connects to YSP SFTP, lists files for previous day matching pattern `*_switch_settled_*.csv`, downloads to local temp path, queues `SwitchDumpProcessing` jobs | `apps/settlement_core/lib/settlement_core/ysp/sftp_fetcher.ex` | 1.5 |
| 2.1.3 | Create `SettlementCore.Ysp.Jobs.SwitchDumpProcessing` Oban worker (queue `:reconciliation`, max 3 attempts): receives file path, calls parser, inserts records, updates `dump_files` status | `apps/settlement_core/lib/settlement_core/ysp/jobs/switch_dump_processing.ex` | 1 |

### 2.2 YSP Switch Dump — File parser

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 2.2.1 | Create `SettlementCore.Ysp.SwitchDumpParser`: parses YSP Base II CSV. Maps columns to `SwitchDumpRecord` fields: `rrn`, `tid`, `auth_number`, `stan`, `merchant_mid`, `transaction_amount`, `transaction_currency`, `transaction_type`, `transaction_datetime`, `card_type_code`, `scheme_name`, `masked_pan`, `switch_settled_date`, `switch_settlement_amount`, `switch_mdr_amount`, `switch_vat_amount`, `switch_net_amount`. Returns `{:ok, [records]}` or `{:error, reason}` | `apps/settlement_core/lib/settlement_core/ysp/switch_dump_parser.ex` | 2 |
| 2.2.2 | Create `SettlementCore.Ysp.QrSchemeDumpParser`: parses QR scheme dump files (AANI, STC Pay formats). Extends with `scheme_reference_no`, `merchant_id` fields | `apps/settlement_core/lib/settlement_core/ysp/qr_scheme_dump_parser.ex` | 2 |
| 2.2.3 | Create `SettlementCore.Ysp.DumpImporter` context module: `import_switch_dump/2`, `import_qr_scheme_dump/2` — creates `DumpFile` record, bulk-inserts `SwitchDumpRecord` / `QrSchemeDumpRecord` in chunks of 500, updates `DumpFile.status` to `processed` or `failed` | `apps/settlement_core/lib/settlement_core/ysp/dump_importer.ex` | 2 |

### 2.3 Scheduler

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 2.3.1 | Create `SettlementCore.Ysp.Scheduler` GenServer: schedules SFTP fetch job at 03:00 AM daily (before reconciliation engine target of 06:00 AM), exposes `trigger_manual_fetch/1` for a given date | `apps/settlement_core/lib/settlement_core/ysp/scheduler.ex` | 1 |
| 2.3.2 | Register `Ysp.Scheduler` in `SettlementCore.Application` supervision tree | `apps/settlement_core/lib/settlement_core/application.ex` | 0.5 |
| 2.3.3 | Add upload fallback: `POST /api/v1/reconciliation/dump-files/upload` controller action for manual file upload when SFTP not available — saves to `DumpFile`, queues processing job | `apps/platform_web/lib/platform_web/controllers/reconciliation_controller.ex` | 1 |

**Acceptance criteria:**
- At 03:00 AM the scheduler automatically fetches the previous day's switch dump from SFTP
- Parsed records are inserted into `switch_dump_records` with `dump_file_id` reference
- `dump_files.status` transitions from `processing` → `processed`
- Manual upload via API also works as fallback

**Phase 2 Total: ~12 PD (~2.5 weeks)**

---

## Phase 3 — Reconciliation Engine

**Goal:** 1:1 matching of dump records against `core_transactions`, generation of all four exception types, cumulative exception MIS. Target: completes by 6:00 AM daily.
**Dependency:** Phase 2 (dump records must exist before matching starts).
**Target completion:** 3–4 weeks

### 3.1 Matching logic

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 3.1.1 | Create `SettlementCore.ReconciliationEngine` module with `run/1` (accepts `%DumpFile{}`). Orchestrates full pipeline: match → exceptions → update `core_transactions` → finalize | `apps/settlement_core/lib/settlement_core/reconciliation_engine.ex` | 1 |
| 3.1.2 | Implement `match_switch_records/1`: for each `SwitchDumpRecord` with `match_status: :unmatched`, query `core_transactions` on `(rrn, tid, auth_number)` first; if found, validate `transaction_amount` and `transaction_datetime` (date part only). On full match → update `SwitchDumpRecord` to `matched`, set `matched_core_transaction_id`. On partial match (RRN found but amount/date differ) → set to `exception` | `apps/settlement_core/lib/settlement_core/reconciliation_engine.ex` | 3 |
| 3.1.3 | Implement `match_qr_scheme_records/1`: same logic but for `QrSchemeDumpRecord`, uses `(rrn, tid, transaction_datetime)` composite key | `apps/settlement_core/lib/settlement_core/reconciliation_engine.ex` | 2 |
| 3.1.4 | After successful match: update `core_transactions.switch_settled_date = dump_record.switch_settled_date` and set `settlement_status = :matched` via `CoreTransaction.status_changeset/2`. Run in `Repo.transaction/1` | `apps/settlement_core/lib/settlement_core/reconciliation_engine.ex` | 1.5 |
| 3.1.5 | Implement 30-day window lookup: dump records are matched only against `core_transactions` with `transaction_datetime >= dump_date - 30 days`. Add index on `core_transactions (rrn, tid, auth_number)` | `priv/repo/migrations/YYYYMMDD_add_recon_index_core_transactions.exs` | 0.5 |

### 3.2 Exception generation (Requirements 5.1–5.4)

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 3.2.1 | **Type 5.1** — In dump, not in Mercury: after match pass, create `ReconciliationException` for every `SwitchDumpRecord` still `unmatched`. Set `exception_type: "5.1"`, `source: :SWITCH`, populate `rrn`, `tid`, `merchant_mid` | `apps/settlement_core/lib/settlement_core/reconciliation_engine.ex` | 1 |
| 3.2.2 | **Type 5.2** — In Mercury, not in dump (blank `switch_settled_date`): query `core_transactions` where `switch_settled_date IS NULL AND transaction_type != 'VOID' AND DATE(transaction_datetime) < :today AND DATE(transaction_datetime) >= :today - 30`. Create `ReconciliationException` for each. Mark `core_transactions.exception_type = "5.2"` | `apps/settlement_core/lib/settlement_core/reconciliation_engine.ex` | 1.5 |
| 3.2.3 | **Type 5.3** — Void matched in dump: after match pass, query `core_transactions` where `transaction_type = 'VOID' AND id IN (matched core transaction ids for today)`. Create `ReconciliationException` with `exception_type: "5.3"` | `apps/settlement_core/lib/settlement_core/reconciliation_engine.ex` | 1 |
| 3.2.4 | **Type 5.4** — Duplicate in dump: before matching, detect `SwitchDumpRecord` records with same `rrn + tid` that also exist in a previous `dump_file` (`dump_date < current_dump_date`). Set `exception_type: "5.4"`, skip from matching | `apps/settlement_core/lib/settlement_core/reconciliation_engine.ex` | 1.5 |
| 3.2.5 | Add `hold_transaction/1` helper: sets `core_transactions.exception_type = exception_type_code` and `settlement_status = :exception_hold` for all exception-tagged transactions | `apps/settlement_core/lib/settlement_core/reconciliation_engine.ex` | 0.5 |
| 3.2.6 | Refund handling: for dump records where `transaction_type = 'REFUND'`, find original transaction by `rrn` in `core_transactions`, update `refund_status = true`, `refund_date = dump_record.switch_settled_date` | `apps/settlement_core/lib/settlement_core/reconciliation_engine.ex` | 1 |

### 3.3 Oban worker + daily scheduler

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 3.3.1 | Create `SettlementCore.Workers.ReconciliationWorker` Oban worker (queue `:reconciliation`, max 2 attempts, unique by `dump_file_id`): calls `ReconciliationEngine.run/1`, logs output | `apps/settlement_core/lib/settlement_core/workers/reconciliation_worker.ex` | 1 |
| 3.3.2 | Extend `Ysp.Scheduler` (or create `ReconciliationScheduler`): after SFTP fetch jobs complete (04:00 AM), enqueue `ReconciliationWorker` for each newly imported `DumpFile` | `apps/settlement_core/lib/settlement_core/ysp/scheduler.ex` | 1 |

### 3.4 Exception report API & UI

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 3.4.1 | Create `ReconciliationController` with actions: `index` (list exceptions by date/type/status), `show`, `release` (calls `release_changeset/2`), `bulk_release`. Add routes | `apps/platform_web/lib/platform_web/controllers/reconciliation_controller.ex` | 2 |
| 3.4.2 | Create `ReconciliationLive.ExceptionDashboard` LiveView: filterable table of exceptions by date/type, release button per row, cumulative MIS totals (outstanding count, amount by exception type) | `apps/platform_web/lib/platform_web/live/reconciliation_live/exception_dashboard.ex` | 3 |
| 3.4.3 | Add routes: `GET /admin/reconciliation/exceptions` (LiveView), `GET /api/v1/reconciliation/exceptions`, `PATCH /api/v1/reconciliation/exceptions/:id/release` | `apps/platform_web/lib/platform_web/router.ex` | 0.5 |

**Acceptance criteria:**
- After 6:00 AM, all `switch_dump_records` from previous day are either `matched` or `exception`
- `core_transactions.switch_settled_date` is set for all matched records
- All 4 exception types create `reconciliation_exceptions` rows
- Exception dashboard shows today's and cumulative outstanding exceptions
- Finance can release exceptions via UI

**Phase 3 Total: ~22 PD (~4 weeks)**

---

## Phase 4 — Settlement Engine & Finance Approval

**Goal:** Generate daily Settlement MIS from matched/cleared `core_transactions`, support 2-level Finance approval, provide merchant adjustment interface. Target: MIS generated by 7:00 AM, Finance reviews by 8:00 AM.
**Dependency:** Phase 3 (matched transactions with `switch_settled_date` set).
**Target completion:** 3–4 weeks

### 4.1 Settlement MIS generation

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 4.1.1 | Create `SettlementCore.SettlementMisGenerator` module with `generate_for_date/2` (`date`, `channel`: POS/QR/COMBINED). Queries `core_transactions` where `switch_settled_date = date AND settlement_status = :matched AND risk_hold = false AND chargeback_hold = false AND exception_type IS NULL` | `apps/settlement_core/lib/settlement_core/settlement_mis_generator.ex` | 2 |
| 4.1.2 | Aggregate per `(merchant_mid, card_type_code, scheme_name)`: sum `gross_amount`, `mdr_amount`, `vat_amount`, `interchange_fee`, count transactions. For each group create a `SettlementMisItem`. Create one parent `SettlementMis` record with totals | `apps/settlement_core/lib/settlement_core/settlement_mis_generator.ex` | 2.5 |
| 4.1.3 | Populate `interchange_fee` per MIS item using `InterchangeRate.rate_query/3` with `scheme_name + card_type_code + date` | `apps/settlement_core/lib/settlement_core/settlement_mis_generator.ex` | 1 |
| 4.1.4 | Apply approved `MerchantAdjustment` records (status `:approved`, `applied_to_payout = false`, `settlement_date = date`) to MIS totals — add to `total_adjustments_credit` / `total_adjustments_debit` in `SettlementMisItem` | `apps/settlement_core/lib/settlement_core/settlement_mis_generator.ex` | 1.5 |
| 4.1.5 | Create `SettlementCore.Workers.MisGenerationWorker` Oban worker (queue `:settlements`, unique by `settlement_date + channel`): calls `SettlementMisGenerator.generate_for_date/2`. Schedule at 07:00 AM after reconciliation is confirmed done | `apps/settlement_core/lib/settlement_core/workers/mis_generation_worker.ex` | 1 |

### 4.2 Finance approval UI & API

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 4.2.1 | Create `SettlementMisController` with actions: `index`, `show`, `l1_approve`, `l2_approve`, `reject`, `download_csv`. Protected by role-based `Finance_L1`/`Finance_L2` authorization plugs | `apps/platform_web/lib/platform_web/controllers/settlement_mis_controller.ex` | 2 |
| 4.2.2 | Create `SettlementLive.MisApproval` LiveView: shows pending MIS records, drills into MIS items (per merchant/card type), approve/reject buttons, displays totals (gross, MDR, VAT, interchange, net), download button | `apps/platform_web/lib/platform_web/live/settlement_live/mis_approval.ex` | 4 |
| 4.2.3 | Add routes: `GET /admin/settlements/mis` (LiveView), `GET /api/v1/settlements/mis`, `PATCH /api/v1/settlements/mis/:id/l1-approve`, `PATCH /api/v1/settlements/mis/:id/l2-approve`, `PATCH /api/v1/settlements/mis/:id/reject`, `GET /api/v1/settlements/mis/:id/download` | `apps/platform_web/lib/platform_web/router.ex` | 0.5 |
| 4.2.4 | Implement `download_csv` action: generates CSV from `SettlementMisItems` with columns: `Merchant Name, MID, Transaction ID/Auth, Beneficiary A/c Number, Transaction Date, Bank Name, IBAN, Gross Amount, Total Commission, VAT, AR Recovery, Net Payable, Risk Status, TID/QR Tag, RRN, Interchange Rate` | `apps/platform_web/lib/platform_web/controllers/settlement_mis_controller.ex` | 1.5 |

### 4.3 Merchant Adjustment interface

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 4.3.1 | Create `MerchantAdjustmentController` with `index`, `create`, `approve`, `reject` actions. `create` accepts: `merchant_id` (displays name), `debit_credit`, `amount`, `reason` (dropdown: Chargeback Recovery, MDR Adjustment, Credit Adjustment, Debit Adjustment, Other Fees), `description` free-text, optional `core_transaction_id` | `apps/platform_web/lib/platform_web/controllers/merchant_adjustment_controller.ex` | 2 |
| 4.3.2 | Create `SettlementLive.AdjustmentManager` LiveView: merchant name autocomplete on MID entry, adjustment entry form, pending approvals list | `apps/platform_web/lib/platform_web/live/settlement_live/adjustment_manager.ex` | 3 |
| 4.3.3 | Implement bulk upload: `POST /api/v1/settlements/adjustments/bulk-upload` accepts CSV with columns `merchant_mid, merchant_id, debit_credit, amount, reason, description, reference_number`. Parse, validate, and create pending `MerchantAdjustment` records | `apps/platform_web/lib/platform_web/controllers/merchant_adjustment_controller.ex` | 2 |
| 4.3.4 | Add routes: `GET /admin/settlements/adjustments` (LiveView), `GET|POST /api/v1/settlements/adjustments`, `PATCH /api/v1/settlements/adjustments/:id/approve`, `POST /api/v1/settlements/adjustments/bulk-upload` | `apps/platform_web/lib/platform_web/router.ex` | 0.5 |

### 4.4 Risk & Chargeback integration into settlement batch

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 4.4.1 | Ensure `MisGenerationWorker` excludes `risk_hold = true` and `chargeback_hold = true` transactions from MIS items (already in query filter from 4.1.1) | `apps/settlement_core/lib/settlement_core/settlement_mis_generator.ex` | 0.5 |
| 4.4.2 | Create `SettlementCore.RiskReleaseProcessor`: when Risk team releases a transaction (sets `risk_hold = false`), upsert it into the next business day's pending MIS — update `core_transactions.settlement_status = :matched` | `apps/settlement_core/lib/settlement_core/risk_release_processor.ex` | 1.5 |
| 4.4.3 | Create `SettlementCore.ExceptionReleaseProcessor`: when Finance team releases a `ReconciliationException`, update linked `core_transaction.settlement_status = :matched` and clear `exception_type` so it is picked up by next MIS generation run | `apps/settlement_core/lib/settlement_core/exception_release_processor.ex` | 1.5 |

**Acceptance criteria:**
- Daily `SettlementMis` record is auto-generated at 07:00 AM in `draft` status
- Finance L1 can approve → `l1_approved`; Finance L2 can approve → `approved`; either can reject → `rejected`
- MIS CSV download contains all required columns from the requirements doc
- Merchant adjustments can be entered (single + bulk), approved, and reflected in MIS
- Risk-released and exception-released transactions appear in next day's MIS

**Phase 4 Total: ~27 PD (~4 weeks)**

---

## Phase 5 — Payout Pipeline

**Goal:** After Finance L2 approval, automatically generate aggregated payout batch, encrypt it, transmit it to Mercury's bank, and receive bank confirmation. All merchant payouts confirmed with transfer reference numbers.
**Dependency:** Phase 4 (MIS must be in `approved` status before payout generation).
**Target completion:** 3–4 weeks

### 5.1 Payout file generation

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 5.1.1 | Create `SettlementCore.PayoutGenerator` module with `generate_for_mis/1` (`%SettlementMis{}`). Groups `SettlementMisItems` by `merchant_mid`, fetches beneficiary bank details from `TidMaster` (bank_account_number, iban, bank_name, bank_routing_code), applies approved adjustments, calculates `final_payout_amount = net_settlement - adjustments_debit + adjustments_credit`. Creates one `PayoutItem` per merchant and one `PayoutBatch` | `apps/settlement_core/lib/settlement_core/payout_generator.ex` | 2.5 |
| 5.1.2 | Implement `generate_payout_csv/1`: serialises `PayoutBatch` to agreed bank format CSV. Columns: `Sequence, MerchantName, MID, IBAN, BankName, BankAccountNumber, Currency, GrossAmount, MDRCharges, VAT, Adjustments, FinalPayoutAmount, TransactionCount, ValueDate, BatchReference` | `apps/settlement_core/lib/settlement_core/payout_generator.ex` | 1.5 |
| 5.1.3 | Implement `generate_payout_json/1`: JSON format alternative where bank API accepts JSON payload | `apps/settlement_core/lib/settlement_core/payout_generator.ex` | 1 |

### 5.2 File encryption

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 5.2.1 | Create `SettlementCore.FileEncryptor` module: `encrypt_file/2` using Erlang `:crypto` with AES-256-CBC. Key sourced from app config `settlement_core :payout_encryption_key`. Stores encrypted file at `payout_batches.encrypted_file_path`, plaintext at `payout_batches.file_path`, SHA256 hash at `payout_batches.file_hash`. Key reference (key name/version) stored at `encryption_key_ref` | `apps/settlement_core/lib/settlement_core/file_encryptor.ex` | 2 |

### 5.3 Bank transmission

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 5.3.1 | Create `SettlementCore.Bank.SftpClient`: SFTP client for outbound transmission. Connects to Mercury's settlement bank SFTP. Config: `bank_sftp_host`, `bank_sftp_port`, `bank_sftp_user`, `bank_sftp_key_path`, `bank_sftp_upload_dir` | `apps/settlement_core/lib/settlement_core/bank/sftp_client.ex` | 1 |
| 5.3.2 | Create `SettlementCore.Bank.ApiClient`: HTTP client (via `Req`) for banks that accept payment instruction API. Config: `bank_api_base_url`, `bank_api_key`. Implements `submit_payment_batch/1` | `apps/settlement_core/lib/settlement_core/bank/api_client.ex` | 1.5 |
| 5.3.3 | Create `SettlementCore.Bank.PayoutTransmitter`: `transmit/1` — checks `PayoutBatch.transmission_method` (`:sftp` / `:api` / `:email`), dispatches to appropriate client, updates `PayoutBatch` with `transmit_changeset/2` on success | `apps/settlement_core/lib/settlement_core/bank/payout_transmitter.ex` | 2 |
| 5.3.4 | Create `SettlementCore.Workers.PayoutTransmissionWorker` Oban worker (queue `:payouts`, max 3 attempts): triggered on MIS L2 approval event → calls `PayoutGenerator.generate_for_mis/1` → `FileEncryptor.encrypt_file/2` → `PayoutTransmitter.transmit/1` | `apps/settlement_core/lib/settlement_core/workers/payout_transmission_worker.ex` | 1.5 |

### 5.4 Bank confirmation & payment rejection handling

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 5.4.1 | Create `POST /api/v1/bank/payout-confirmation` webhook controller: receives bank ACK with `transfer_reference_number`, `merchant_id`, `status` (credited/failed/returned). Calls `PayoutItem.bank_confirm_changeset/4`, updates `core_transactions.settlement_status = :paid` for all items in the batch | `apps/platform_web/lib/platform_web/controllers/bank_webhook_controller.ex` | 2 |
| 5.4.2 | Create `SettlementCore.Workers.BankAckPollingWorker` Oban worker: for banks using SFTP ACK files — polls bank SFTP for confirmation files every 2 hours post-transmission, parses ACK file, calls same confirmation logic | `apps/settlement_core/lib/settlement_core/workers/bank_ack_polling_worker.ex` | 2 |
| 5.4.3 | Payment rejection re-initiation: add `PayoutBatchController` with `reinitiate_payout/2` action — takes list of failed `PayoutItem` ids, creates a new independent `PayoutBatch` for those items only (status `:draft`), allows Finance to review and trigger transmission again | `apps/platform_web/lib/platform_web/controllers/payout_batch_controller.ex` | 2 |
| 5.4.4 | Add `PayoutBatch` management LiveView: list batches, show per-batch items with confirmation status, reinitiate button for failed items | `apps/platform_web/lib/platform_web/live/settlement_live/payout_dashboard.ex` | 3 |
| 5.4.5 | Add routes: `GET /admin/settlements/payouts` (LiveView), `GET /api/v1/settlements/payouts`, `POST /api/v1/bank/payout-confirmation`, `POST /api/v1/settlements/payouts/:id/reinitiate` | `apps/platform_web/lib/platform_web/router.ex` | 0.5 |

**Acceptance criteria:**
- After L2 approval, payout batch is auto-generated, encrypted, and transmitted within minutes
- `payout_batches.status` transitions `generating → ready → transmitted → confirmed`
- Bank confirmation webhook updates individual `payout_items.bank_confirmation_status`
- `core_transactions.settlement_status = :paid` after confirmation received
- Failed payout items can be re-initiated independently

**Phase 5 Total: ~23 PD (~4 weeks)**

---

## Phase 6 — Merchant Notifications

**Goal:** Auto-generate and email MPR (Merchant Payment Report), Settlement Advice, and VAT Invoice to merchants after payout confirmation. Finance team alerted on exceptions and rejections.
**Dependency:** Phase 5 (bank confirmation must exist before Settlement Advice can be sent).
**Target completion:** 2 weeks

### 6.1 Swoosh mailer module

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 6.1.1 | Create `SettlementNotifier` Swoosh mailer module with functions: `send_mpr/2`, `send_settlement_advice/2`, `send_vat_invoice/2`, `send_exception_alert/2`, `send_rejection_alert/2`. Each composes a Swoosh `Email`, resolves recipient from `TidMaster.registered_email` | `apps/platform_web/lib/platform_web/emails/settlement_notifier.ex` | 2 |

### 6.2 MPR (Merchant Payment Report)

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 6.2.1 | Create MPR HEEx email template: MID, Merchant Name, Settlement Date, Transaction Count, Gross Amount, MDR, VAT, Adjustments, Net Payout Amount, Batch Reference. Attach PDF via `chromic_pdf` or CSV attachment | `apps/platform_web/lib/platform_web_web/templates/email/mpr.html.heex` | 2 |
| 6.2.2 | Create `SettlementCore.Workers.MprDispatchWorker` Oban worker: triggered after MIS L2 approval. Iterates per-merchant `SettlementMisItems`, composes MPR via template, calls `SettlementNotifier.send_mpr/2`. One email per merchant, marks `SettlementMisItem.mpr_sent = true` | `apps/settlement_core/lib/settlement_core/workers/mpr_dispatch_worker.ex` | 2 |
| 6.2.3 | Add `mpr_sent boolean`, `mpr_sent_at` to `settlement_mis_items` migration | `priv/repo/migrations/YYYYMMDD_add_mpr_sent_to_mis_items.exs` | 0.5 |

### 6.3 Settlement Advice

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 6.3.1 | Create Settlement Advice email HEEx template: includes bank Transfer Reference Number, Value Date, Net Payout Amount, and transaction breakdown | `apps/platform_web/lib/platform_web_web/templates/email/settlement_advice.html.heex` | 2 |
| 6.3.2 | Create `SettlementCore.Workers.SettlementAdviceWorker` Oban worker: triggered after bank confirmation received. Iterates each confirmed `PayoutItem`, fetches merchant email from `TidMaster`, sends Settlement Advice via `SettlementNotifier.send_settlement_advice/2` | `apps/settlement_core/lib/settlement_core/workers/settlement_advice_worker.ex` | 1.5 |

### 6.4 VAT Invoice & VAT MIS

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 6.4.1 | Create `vat_invoices` migration + `SettlementCore.VatInvoice` schema: `merchant_mid`, `settlement_date`, `invoice_number`, `gross_amount`, `mdr_amount`, `vat_amount` (5% of MDR), `payout_item_id`, `sent_at` | `priv/repo/migrations/YYYYMMDD_create_vat_invoices.exs` + `apps/settlement_core/lib/settlement_core/vat_invoice.ex` | 1.5 |
| 6.4.2 | Create VAT Invoice email HEEx template following UAE VAT format: Invoice Number, TRN Number (Mercury), Merchant Name/TRN, Service Description (MDR Charges), Net MDR, VAT (5%), Total | `apps/platform_web/lib/platform_web_web/templates/email/vat_invoice.html.heex` | 2 |
| 6.4.3 | Create `SettlementCore.Workers.VatInvoiceWorker`: generates `VatInvoice` records (auto-incrementing invoice number), sends email via `SettlementNotifier.send_vat_invoice/2`, generates VAT MIS CSV for Finance | `apps/settlement_core/lib/settlement_core/workers/vat_invoice_worker.ex` | 2 |

### 6.5 Exception & rejection alerts to Finance

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 6.5.1 | Extend `ReconciliationEngine.run/1`: after generating exceptions, trigger `SettlementNotifier.send_exception_alert/2` to Finance email group with summary (count per exception type, total amount on hold) | `apps/settlement_core/lib/settlement_core/reconciliation_engine.ex` | 1 |
| 6.5.2 | Extend `BankAckPollingWorker` / bank webhook: on payment rejection, trigger `SettlementNotifier.send_rejection_alert/2` to Finance + Ops email groups with rejected `PayoutItem` details | `apps/settlement_core/lib/settlement_core/workers/bank_ack_polling_worker.ex` | 1 |

**Acceptance criteria:**
- Merchant receives MPR email on same day as MIS approval, before payout
- Merchant receives Settlement Advice email after bank confirms payout
- Merchant receives VAT Invoice email alongside Settlement Advice
- Finance receives exception summary email daily after reconciliation
- Finance + Ops receive rejection alert email for each failed payout item

**Phase 6 Total: ~17 PD (~2.5 weeks)**

---

## Phase 7 — Chargeback Management

**Goal:** End-to-end chargeback lifecycle — intake from card schemes, tracking per merchant, recovery deduction from settlement, integration with MIS and risk.
**Dependency:** Phase 4 (MIS generation must filter on `chargeback_hold`; adjustments interface from Phase 4 is reused for chargeback recovery entry).
**Target completion:** 2–3 weeks

### 7.1 Chargeback schema & lifecycle

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 7.1.1 | Create `chargeback_cases` migration: `case_number varchar unique`, `merchant_mid`, `merchant_id`, `core_transaction_id`, `rrn`, `auth_number`, `transaction_amount`, `transaction_currency`, `transaction_date`, `scheme_name`, `card_type_code`, `chargeback_reason_code`, `chargeback_reason_description`, `scheme_case_reference`, `chargeback_date`, `response_due_date`, `recovery_amount`, `recovery_currency`, `status` (received/representment/pre_arbitration/resolved/written_off), `resolution_date`, `resolution_notes`, `financial_impact` (recovered/written_off), `recovery_deducted_at`, `payout_batch_id` | `priv/repo/migrations/YYYYMMDD_create_chargeback_cases.exs` | 1 |
| 7.1.2 | Create `SettlementCore.ChargebackCase` schema with changesets: `receive_changeset/2`, `representment_changeset/2`, `resolve_changeset/2`, `write_off_changeset/2`, `mark_recovered_changeset/2` | `apps/settlement_core/lib/settlement_core/chargeback_case.ex` | 1.5 |
| 7.1.3 | Add `ChargebackCase` queries to `SettlementCore.Context`: `list_chargebacks/1`, `get_chargeback/1`, `create_chargeback/1`, `get_outstanding_chargebacks_for_mid/1` | `apps/settlement_core/lib/settlement_core/context.ex` | 1 |

### 7.2 Chargeback intake

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 7.2.1 | Create `SettlementCore.Chargeback.CsvImporter`: parses chargeback notification CSV from card schemes (columns: case_number, merchant_mid, rrn, auth, amount, reason_code, description, scheme_reference, chargeback_date, response_due_date). Creates `ChargebackCase` records and links to `core_transactions` via RRN. Sets `core_transactions.chargeback_hold = true` | `apps/settlement_core/lib/settlement_core/chargeback/csv_importer.ex` | 2 |
| 7.2.2 | Create `POST /api/v1/chargebacks/import` API: accepts chargeback CSV upload, delegates to `CsvImporter`, returns summary of imported/failed records | `apps/platform_web/lib/platform_web/controllers/chargeback_controller.ex` | 1 |

### 7.3 Recovery deduction in settlement

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 7.3.1 | Extend `SettlementMisGenerator.generate_for_date/2`: call `get_outstanding_chargebacks_for_mid/1` for each MID in the batch. For each `ChargebackCase` with `status: :resolved, financial_impact: :recovered, recovery_deducted_at: nil`, create a `MerchantAdjustment` with `adjustment_type: :debit, reason: :chargeback_recovery` and auto-approve it | `apps/settlement_core/lib/settlement_core/settlement_mis_generator.ex` | 2 |
| 7.3.2 | After recovery deduction: mark `chargeback_cases.recovery_deducted_at = now()`, `payout_batch_id` once payout is generated | `apps/settlement_core/lib/settlement_core/settlement_mis_generator.ex` | 0.5 |
| 7.3.3 | Extend `RiskCore` seeds/rules to automatically flag merchants with 2+ chargebacks in 30 days (`High Frequency Chargeback Merchant` rule is already seeded) — verify the rule fires correctly using `core_transactions` chargeback count | `apps/risk_core/lib/risk_core/context.ex` | 1 |

### 7.4 Chargeback management UI

| # | Task | File / Module | PD |
|---|------|---------------|----|
| 7.4.1 | Create `ChargebackController` with `index`, `show`, `representment`, `resolve`, `write_off` actions | `apps/platform_web/lib/platform_web/controllers/chargeback_controller.ex` | 2 |
| 7.4.2 | Create `ChargebackLive.Dashboard` LiveView: list chargebacks by status/date/merchant, due-date highlighting, summary cards (total outstanding, total recovery, past-due count) | `apps/platform_web/lib/platform_web/live/chargeback_live/dashboard.ex` | 3 |
| 7.4.3 | Add routes: `GET /admin/chargebacks` (LiveView), `GET|POST /api/v1/chargebacks`, `GET /api/v1/chargebacks/:id`, `PATCH /api/v1/chargebacks/:id/resolve`, `POST /api/v1/chargebacks/import` | `apps/platform_web/lib/platform_web/router.ex` | 0.5 |

**Acceptance criteria:**
- Chargeback CSV from card schemes can be imported and creates `ChargebackCase` records
- Imported chargebacks set `core_transactions.chargeback_hold = true` on linked transactions
- Resolved chargebacks are auto-deducted from the next settlement MIS as adjustments
- Chargeback dashboard shows lifecycle status per case with due-date alerts

**Phase 7 Total: ~16 PD (~2.5 weeks)**

---

## Overall Phased Roadmap

```
Phase 1 — Data Foundation            Weeks 1–2     (~10 PD)
Phase 2 — Dump File Ingestion         Weeks 3–5     (~12 PD)
Phase 3 — Reconciliation Engine       Weeks 5–8     (~22 PD)
Phase 4 — Settlement Engine & MIS     Weeks 8–12    (~27 PD)
Phase 5 — Payout Pipeline             Weeks 12–16   (~23 PD)
Phase 6 — Notifications               Weeks 15–17   (~17 PD)  ← overlaps P5
Phase 7 — Chargeback Management       Weeks 17–20   (~16 PD)
```

Total: **~127 person-days** across ~20 weeks (5 months) with 1 developer.
With 2 developers running Phase 3+4 and Phase 2+6 in parallel: ~12–13 weeks.

---

## Current Branch Status by Requirement Section

| Req Section | What is DONE in `feature/settlement-recon-core` |
|---|---|
| §1 TID Master | Not started |
| §2 Transaction Dump | Core schema done (85%) — missing 5 fields |
| §3 Dump Ingestion | Schemas + AlipayPlus SFTP done; YSP SFTP/parser missing |
| §4 Reconciliation Engine | All schemas done; **matching logic not written** |
| §5 Settlement Engine / MDR | MDR + VAT calc done; MIS generation, payout, bank integration missing |
| §5.6 Settlement MIS | Schema + approval changeset done; **no generator or UI** |
| §5.7 Finance Approval | Changeset logic done; **no controller or LiveView** |
| §5.8 Payout + Encryption | Schemas done; **no generation, encryption, or transmission** |
| §5.9–13 Notifications | Swoosh dependency only; **no mailer modules, templates, or workers** |
| §5.14–16 Release flows | Schema changesets done; **no release processors or pipelines** |
| Chargeback | Only boolean flags; **no lifecycle module** |
| BIN / Interchange | Schemas done; **no data loaded** |