# Settlement & Reconciliation — Implementation Plan v2

**Branch:** `feature/settlement-recon-core`
**Date:** 2026-03-07 (Updated: rate model corrected, database corrected to MySQL)
**Scope:** 17 gap items from gap analysis + interchange/MDR rate corrections
**Excluded:** Risk Core UI (separate workstream — currently uses `transactions` table, will migrate to `core_transactions` independently)

---

## Database Technology — MySQL 8.0+ (MANDATORY)

> **FOR ALL AI AGENTS / COPILOT SESSIONS:** This entire project uses **MySQL 8.0+** exclusively.
> There is **NO PostgreSQL** anywhere in the codebase. All migrations, queries, and schema
> definitions MUST use MySQL-compatible syntax.

### Database Topology

| Ecto Repo | Adapter | Database | Purpose | Access |
|-----------|---------|----------|---------|--------|
| `DaProductApp.Repo` | `Ecto.Adapters.MyXQL` | `shukria_transactions` | Legacy app tables (users, terminals, disputes) | Read/Write |
| `PlatformCore.Repo` | `Ecto.Adapters.MyXQL` | `shukria_transactions` | **Same DB** — settlement, recon, core_transactions, TID masters | Read/Write |
| `PlatformCore.ShukriaMmsRepo` | `Ecto.Adapters.MyXQL` | `shukria_mms_new_local` | MMS reference data (merchants, rates, cards, BIN) | **Read-Only** |

### MySQL-Specific Rules for Migrations & Schemas

| Concern | MySQL Approach | ❌ Do NOT Use (PostgreSQL) |
|---------|---------------|---------------------------|
| JSON columns | `:map` type → MySQL `JSON` column | `jsonb`, `JSONB` |
| Array columns | Store as JSON array (`:map` / `{:array, :string}` → serialized) | PostgreSQL native arrays |
| Primary keys | `BIGINT UNSIGNED AUTO_INCREMENT` (Ecto default `:id`) | `SERIAL`, `BIGSERIAL` |
| Boolean | `TINYINT(1)` (Ecto `:boolean`) | Native `BOOLEAN` type |
| Text search | `LIKE` / `FULLTEXT INDEX` | `ILIKE`, `tsvector`, `tsquery` |
| Upserts | `ON DUPLICATE KEY UPDATE` / `Repo.insert(on_conflict: ...)` | `ON CONFLICT ... DO UPDATE` (Postgres syntax) |
| Enums | `VARCHAR` with application-level validation | PostgreSQL `CREATE TYPE ... AS ENUM` |
| Date functions | `DATE()`, `NOW()`, `DATE_SUB()`, `DATEDIFF()` | `CURRENT_DATE`, `age()`, `interval` |
| String concat | `CONCAT(a, b)` | `a \|\| b` |
| Case sensitivity | MySQL default is case-insensitive (utf8mb4_0900_ai_ci) | PostgreSQL is case-sensitive |
| Locking | `FOR UPDATE` (InnoDB) | `FOR UPDATE SKIP LOCKED` (use with caution in MySQL 8+) |

### Background Jobs — Oban with Dolphin Engine

- Oban is configured with **`Oban.Engines.Dolphin`** (MySQL-compatible engine)
- Notifier: `Oban.Notifiers.Polling` (NOT PostgreSQL PubSub)
- All Oban migrations use MyXQL adapter
- Cron jobs defined in `config/config.exs` under `Oban.Plugins.Cron`

### Dev Credentials

```
host: localhost
username: root
password: dataaegis123
databases: shukria_transactions, shukria_mms_new_local
charset: utf8mb4
```

### Production

- `DATABASE_URL` env var → `shukria_transactions` (PlatformCore.Repo + DaProductApp.Repo)
- `SHUKRIA_MMS_DATABASE_URL` env var → `shukria_mms_new_local` (ShukriaMmsRepo, read-only)
- All repos explicitly set `adapter: Ecto.Adapters.MyXQL` in `config/runtime.exs`

---

## Rate & Interchange Fee — Data Flow Understanding

### CRITICAL CORRECTION: MMS Table Roles (confirmed with business)

The MMS table naming is misleading. Despite being called "mdr_rates", these are actually:

| MMS Table | Actual Role | What it represents | Keyed by |
|-----------|------------|-------------------|----------|
| **`mdr_rates`** (via `mdr_templates`) | **Interchange (cost rate)** | What card schemes **charge Mercury** per card product type, per merchant's interchange schedule | `mdr_template_id` + `card_type_id` (card_type from BIN) |
| **`mcc_mdr_rates`** | **MDR (sell rate)** | What Mercury **charges the merchant** — the Merchant Discount Rate, standardized per MCC | `mcc_id` + `card_type_id` |

**Why this mapping:**
- Interchange is card-type-specific (determined by BIN → card_type) — ✓ matches `mdr_rates` keyed on `card_type_id`
- Interchange can vary by merchant agreement — ✓ matches `mdr_templates.merchant_id` link
- MDR is standardized per merchant category (MCC) — ✓ matches `mcc_mdr_rates` keyed on `mcc_id`
- Sample values: `mdr_rates` has very small rates (0.0006%) consistent with per-transaction interchange fees; `mcc_mdr_rates` has 1.65% rates consistent with standard MDR

### Corrected Architecture

```
                            shukria_mms (MySQL, read-only)
                     ┌───────────────────────────────────────────────┐
                     │                                               │
                     │  bin_table (local MySQL — shukria_transactions)│
                     │    masked_pan → bin_prefix → card_type_id ───┐│
                     │                                              ││
  INTERCHANGE        │  mdr_templates (per-merchant schedule)       ││
  (Cost Rate)        │    merchant_mid → merchant_metadata          ││
  What schemes       │    → merchant_id → mdr_templates             ││
  charge Mercury     │    → mdr_template_id                         ││
        │            │                                              ││
        │            │  mdr_rates ◄─────────────────────────────────┤│
        │            │    mdr_template_id + card_type_id            ││
        │            │    → percentage_rate, fixed_fee, min, max    ││
        │            │    = INTERCHANGE FEE per card product        ││
        │            │                                              ││
  ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─│
                     │                                              ││
  MDR                │  mcc_mdr_rates ◄─────────────────────────────┘│
  (Sell Rate)        │    mcc_id + card_type_id                      │
  What Mercury       │    → percentage_rate, fixed_fee, min, max     │
  charges merchant   │    = MDR (Merchant Discount Rate)             │
        │            │                                               │
        │            │  card_types                                   │
        │            │    id → code, name, category, brand           │
        │            └───────────────────────────────────────────────┘
        │
        ▼
 ┌──────────────────────────────────────────────────────┐
 │  core_transactions (MySQL — PlatformCore.Repo)       │
 │                                                      │
 │  CORRECTED flow at sync time:                        │
 │  1. masked_pan → BIN lookup → card_type_id           │
 │  2. INTERCHANGE: template → mdr_rates → interchange  │
 │  3. MDR: mcc + card_type → mcc_mdr_rates → mdr      │
 │  4. Calculate:                                       │
 │     interchange_fee = calc from mdr_rates            │
 │     mdr_amount = calc from mcc_mdr_rates             │
 │     VAT = mdr_amount × 5%                            │
 │     Net = Gross - MDR - VAT                          │
 │     Margin = MDR - Interchange                       │
 └──────────────────────────────────────────────────────┘
```

### The BUG in Current Code

**Current `resolve_mdr_rate()` in `core_transaction_sync.ex` (lines 370-390):**

```elixir
# CURRENT (WRONG):
def resolve_mdr_rate(merchant_mid, card_type_id, mcc_code, txn_date) do
  with {:ok, metadata} <- get_merchant_metadata(merchant_mid),
       {:ok, template} <- get_mdr_template(metadata.merchant_id, txn_date),
       {:ok, rate}     <- get_mdr_rate(template.id, card_type_id, txn_date) do
    {:ok, %{...rate fields...}}  # ← Stores as "mdr_amount" — BUT THIS IS INTERCHANGE!
  else
    _ -> get_mcc_mdr_rate(mcc_code, card_type_id, txn_date)  # ← Falls back to MDR — WRONG FALLBACK
  end
end
```

**Problems:**
1. `mdr_rates` (interchange) is stored as `mdr_amount` on core_transactions — **wrong field**
2. `mcc_mdr_rates` (actual MDR) is treated as a fallback when interchange not found — **should be separate lookup**
3. `interchange_fee` column stays NULL — **never populated**
4. Net settlement = `gross - mdr_amount - vat` uses interchange as MDR — **wrong deduction**

### Corrected Calculation Chain

```
For each core_transaction:

Step 1 — Card Type Resolution (existing, correct):
  masked_pan → bin_table.bin_prefix → card_type_id
  card_type_id → card_types.code (e.g. "VISA_DEBIT_STD")
  card_type_id → card_types.category (DEBIT/CREDIT/PREPAID)
  card_type_id → card_types.brand (VISA/MC/AMEX)

Step 2 — Interchange Fee Resolution (RENAMED from resolve_mdr_rate):
  merchant_mid → merchant_metadata.merchant_id
  merchant_id → mdr_templates (active, date-effective)
  mdr_template_id + card_type_id → mdr_rates → {pct, fixed, min, max}
  This is INTERCHANGE — what schemes charge Mercury

Step 3 — MDR Resolution (NEW separate lookup):
  mcc_code + card_type_id → mcc_mdr_rates → {pct, fixed, min, max}
  This is MDR — what Mercury charges the merchant

Step 4 — Calculate Interchange Fee:
  raw_interchange = (amount × interchange_pct / 100) + interchange_fixed
  interchange_fee = max(min_fee, min(max_fee, raw_interchange))

Step 5 — Calculate MDR:
  raw_mdr = (amount × mdr_pct / 100) + mdr_fixed
  mdr_amount = max(mdr_min, min(mdr_max, raw_mdr))

Step 6 — VAT (on MDR, since MDR is Mercury's service charge):
  vat_amount = mdr_amount × 5%

Step 7 — Net Settlement to Merchant:
  net_settlement = gross - mdr_amount - vat_amount
  (Interchange does NOT reduce merchant payout — it's Mercury's internal cost)

Step 8 — Mercury Profitability (MIS level):
  mercury_revenue = mdr_amount
  mercury_cost    = interchange_fee
  mercury_margin  = mdr_amount - interchange_fee
```

### Rate Resolution Example (with corrected model)

```
Transaction: Amount = 500.00 AED, Merchant MID = "Mercury_F1CE8D7"
             masked_pan = "476173**1234", MCC = 12

Step 1 — BIN Lookup:
  bin_table: bin_prefix "476173" → card_type_id = 1
  card_types: id=1 → code="VISA_DEBIT_STD", category="DEBIT", brand="VISA"

Step 2 — Interchange Resolution (from mdr_rates via template):
  merchant_metadata: merchant_mid="Mercury_F1CE8D7" → merchant_id=430
  mdr_templates:     merchant_id=430, effective today → template_id=2
  mdr_rates:         template_id=2, card_type_id=1
                     → {pct: 0.0006, fixed: 0.03, min: 0.07, max: 0.14}

Step 3 — MDR Resolution (from mcc_mdr_rates):
  mcc_mdr_rates:     mcc_id=12, card_type_id=1
                     → {pct: 1.6500, fixed: 0.00, min: nil, max: nil}

Step 4 — Calculate Interchange:
  raw = (500.00 × 0.0006 / 100) + 0.03 = 0.003 + 0.03 = 0.033
  interchange_fee = max(0.07, min(0.14, 0.033)) = 0.07 (min-capped)

Step 5 — Calculate MDR:
  raw = (500.00 × 1.6500 / 100) + 0.00 = 8.25
  mdr_amount = 8.25 (no min/max bounds)

Step 6 — VAT:
  vat = 8.25 × 5% = 0.4125 → 0.41 (rounded)

Step 7 — Net to Merchant:
  net = 500.00 - 8.25 - 0.41 = 491.34

Step 8 — Mercury Margin:
  revenue = 8.25 (MDR collected from merchant)
  cost    = 0.07 (interchange paid to scheme)
  margin  = 8.25 - 0.07 = 8.18
```

---

## Phase 1 — Rate Model Correction & Interchange Fix (Priority: CRITICAL)

**Estimated effort:** 4–5 days
**Impact:** This phase fixes a fundamental bug — the current code treats interchange as MDR.

### 1.1 Split `resolve_mdr_rate` into Two Separate Functions

**Gap:** #7 — Core bug: interchange stored as MDR, actual MDR never calculated

**Current code (WRONG):**
```elixir
# core_transaction_sync.ex — resolve_mdr_rate() currently:
# 1. Queries mdr_templates → mdr_rates (this is INTERCHANGE, not MDR!)
# 2. Falls back to mcc_mdr_rates (this IS actual MDR, but treated as fallback)
# 3. Stores result as "mdr_amount" — WRONG when source is mdr_rates
```

**Change — Create two separate resolution functions:**

```elixir
# NEW: resolve_interchange_rate/4 — queries mdr_templates → mdr_rates
# Purpose: Get interchange cost from MMS (what schemes charge Mercury)
def resolve_interchange_rate(merchant_mid, card_type_id, _mcc_code, txn_date) do
  with {:ok, metadata} <- get_merchant_metadata(merchant_mid),
       {:ok, template} <- get_mdr_template(metadata.merchant_id, txn_date),
       {:ok, rate}     <- get_mdr_rate(template.id, card_type_id, txn_date) do
    {:ok, %{
      interchange_template_id: template.id,
      interchange_percentage: rate.percentage_rate,
      interchange_fixed_fee: rate.fixed_fee || Decimal.new(0),
      interchange_min_fee: rate.minimum_fee,
      interchange_max_fee: rate.maximum_fee
    }}
  else
    _ -> {:error, :no_interchange_rate}
  end
end

# RENAMED: resolve_mdr_rate/3 — queries mcc_mdr_rates ONLY
# Purpose: Get MDR sell rate (what Mercury charges merchant)
def resolve_mdr_rate(mcc_code, card_type_id, txn_date) do
  get_mcc_mdr_rate(mcc_code, card_type_id, txn_date)
  # Returns {:ok, %{percentage_rate, fixed_fee, minimum_fee, maximum_fee}}
end
```

**Files:** `core_transaction_sync.ex`

### 1.2 Rewrite `apply_mdr_to_batch_rows` for Dual Calculation

**Current:** Single calculation loop storing interchange-rate as `mdr_amount`.

**Change:** The per-row update must now calculate BOTH rates independently:

```elixir
defp apply_mdr_to_batch_rows(batch_id, source, merchant_mid, card_type_id, mcc_code, txn_date) do
  # Step 1: Resolve interchange (from mdr_templates → mdr_rates)
  interchange_info = resolve_interchange_rate(merchant_mid, card_type_id, mcc_code, txn_date)

  # Step 2: Resolve MDR (from mcc_mdr_rates)
  mdr_info = resolve_mdr_rate(mcc_code, card_type_id, txn_date)

  rows = fetch_batch_rows(batch_id, source, merchant_mid, card_type_id)

  Enum.each(rows, fn %{id: id, amount: amount} ->
    # Calculate interchange fee
    interchange_fee = case interchange_info do
      {:ok, info} -> calculate_fee(amount, info)
      _ -> Decimal.new(0)  # Log warning
    end

    # Calculate MDR (sell rate)
    mdr_amount = case mdr_info do
      {:ok, info} -> calculate_fee(amount, info)
      _ -> Decimal.new(0)  # Log warning — no MCC rate found
    end

    # VAT on MDR (Mercury's service charge to merchant)
    vat_amount = Decimal.round(Decimal.mult(mdr_amount, Decimal.div(vat_rate, 100)), 2)

    # Net to merchant = Gross - MDR - VAT
    # (interchange is Mercury's cost, does NOT reduce merchant payout)
    net_settlement = Decimal.sub(Decimal.sub(amount, mdr_amount), vat_amount)

    # Update core_transaction with BOTH rates
    update_core_transaction(id, %{
      # MDR fields (sell rate from mcc_mdr_rates)
      mdr_percentage_rate: mdr_info_pct,
      mdr_fixed_fee: mdr_info_fixed,
      mdr_amount: mdr_amount,
      # Interchange fields (cost rate from mdr_rates via template)
      interchange_fee: interchange_fee,
      interchange_template_id: template_id,
      # Derived fields
      vat_rate: vat_rate,
      vat_amount: vat_amount,
      gross_amount: amount,
      net_settlement_amount: net_settlement
    })
  end)
end
```

**Fields on core_transactions affected:**
- `mdr_amount` — NOW calculated from `mcc_mdr_rates` (was from `mdr_rates` — WRONG)
- `interchange_fee` — NOW populated from `mdr_rates` via templates (was always NULL)
- `mdr_template_id` — NOW stores the interchange template ID (rename later or add separate field)
- `net_settlement_amount` — NOW correct (was wrong because MDR was wrong)

**Files:** `core_transaction_sync.ex`

### 1.3 Rename `resolve_mdr_rate` Callers in `update_mdr_rates_for_batch`

**Current:** `update_mdr_rates_for_batch` groups transactions by `(merchant_mid, card_type_id, mcc_code, txn_date)` and calls `resolve_mdr_rate` once per group.

**Change:**
- Pass `mcc_code` alongside `merchant_mid` and `card_type_id` to the new `apply_mdr_to_batch_rows`
- The function now internally resolves BOTH interchange and MDR
- Remove the single `resolve_mdr_rate → apply` pattern

**Files:** `core_transaction_sync.ex`

### 1.4 Add `interchange_template_id` Field to core_transactions

**Current:** `mdr_template_id` on core_transactions was meant for MDR but actually stored the interchange template.

**Change:**
- Add migration: `alter table core_transactions add column interchange_template_id bigint`
- Store the `mdr_templates.id` used for interchange lookup in this field
- Clear `mdr_template_id` field (it's meaningless for MCC-level MDR which has no template)
- **Files:** migration, `core_transaction.ex`

### 1.5 Update SettlementMisGenerator

**Current:** MIS generator uses `SUM(ct.interchange_fee)` which was always NULL, then falls back to `interchange_rates` local table (empty).

**Change:**
- `interchange_fee` is now populated at sync time (1.2) — SUM aggregation will work
- Remove `lookup_interchange_fee` function that queries the empty `interchange_rates` table
- Add Mercury margin calculation: `margin = total_mdr - total_interchange`
- Keep `interchange_rates` local table as backup source for YSP-provided rates (Phase 1.7)
- **Files:** `settlement_mis_generator.ex`

### 1.6 Update ReconciliationEngine to Override Interchange from YSP

**Gap:** #17 — Interchange fee not updated during reconciliation

**Current:** Reconciliation only updates `switch_settled_date` and `settlement_status`.

**Change:**
- If YSP switch dump record contains actual `interchange_fee` → write it to core_transactions, overriding the MMS-calculated value
- This is the most accurate source (actual scheme interchange for the specific transaction)
- Priority: YSP actual > mdr_rates (MMS calculated) > 0 (unknown)
- **Files:** `reconciliation_engine.ex`, `switch_dump_record.ex`

### 1.7 Keep `interchange_rates` Table as YSP Fallback

**Change:** The local `interchange_rates` table (currently empty) retains its role as:
- Repository for Mercury's master interchange table shared with YSP
- Used by `settlement_mis_generator.ex` as secondary fallback if MMS has no rate
- Create `mix settlement.load_interchange_rates` task to load from CSV
- **Files:** new `lib/mix/tasks/settlement/load_interchange_rates.ex`

### 1.8 Backfill card_type_code from card_type_id

**Current:** `card_type_id` set from BIN, but `card_type_code` often NULL. YSP interchange_rates keys on `card_type_code`.

**Change:**
- After BIN backfill, populate `card_type_code` from `shukria_mms.card_types.code`
- Used for interchange_rates table lookup (1.7 fallback)
- **Files:** `core_transaction_sync.ex`

---

## Phase 2 — Reconciliation Engine Fixes (Priority: Critical)

**Estimated effort:** 2 days

### 2.1 Add auth_number to Reconciliation Match Key

**Gap:** #1 — Match key should be `RRN + TID + Amount + Date + Auth`

**Current:** Match SQL joins on `rrn + tid + amount (±0.01) + 30-day window`. `auth_number` is **NOT** in the WHERE clause.

**Change:**
- Add `AND ct.auth_number = sdr.auth_number` to the match SQL in `match_records/1`
- Must be NULL-safe: `AND (ct.auth_number = sdr.auth_number OR (ct.auth_number IS NULL AND sdr.auth_number IS NULL))`
- Without auth_number, duplicate transactions with same RRN+TID+Amount+Date but different authorizations will match incorrectly
- **Files:** `reconciliation_engine.ex`

### 2.2 Calculate total_amount_on_hold for Exception Alerts

**Gap:** #9 — Hardcoded to 0

**Change:**
- After generating exceptions, SUM the transaction amounts of all new exceptions created in this run
- Pass actual total to `maybe_send_exception_alert`
- Query: `SELECT COALESCE(SUM(ct.transaction_amount), 0) FROM reconciliation_exceptions re JOIN core_transactions ct ON ct.id = re.core_transaction_id WHERE re.reconciliation_run_id = $run_id`
- **Files:** `reconciliation_engine.ex`

### 2.3 Partial Refund Support

**Gap:** #8 — Only FULL refund handled; partial refund amount not tracked

**Change:**
- When matching refund transactions during reconciliation:
  - Compare refund amount vs original transaction amount
  - If refund amount < original amount → set `refund_status = 'PARTIAL'`, store `refund_amount`
  - If refund amount = original amount → set `refund_status = 'FULL'`
- Add `refund_amount` decimal field to `core_transactions` if not present (migration)
- Add `refund_status` enum field if not present: `[nil, "FULL", "PARTIAL"]`
- **Files:** `reconciliation_engine.ex`, `core_transaction.ex`, migration

### 2.4 Cumulative Exception Aging Report

**Gap:** #15 — No aging report for outstanding exceptions

**Change:**
- Add `Context.list_outstanding_exceptions/1` function: queries `ReconciliationException` where status = "hold" or "pending", grouped by age buckets:
  - 0–7 days
  - 8–15 days
  - 16–30 days
  - 31+ days
- Each bucket shows: count, total amount, type breakdown (5.1 Present in A not B, 5.2 reverse, etc.)
- Add aging summary section to `ExceptionDashboard` LiveView
- **Files:** `context.ex`, `exception_dashboard.ex`

---

## Phase 3 — TID Master & Schema Enrichment (Priority: High)

**Estimated effort:** 3 days

### 3.1 Add bank_name to TID Master + MMS Sync

**Gap:** #5 — Only `bank_code` stored; PayoutItem `bank_name` always NULL

**Current MMS flow:** `approval_sync.ex` reads `merchant_metadata.bank_code` and maps it to `tid_masters.bank_code`. There is no `bank_name` anywhere.

**Investigation needed:**
- Check if `banks` table exists in shukria_mms (users.bank_id FK → banks)
- Check if `users.meta` JSON contains `bank_name`

**Change:**
- If `banks` table exists → create `Mms.Bank` read-only schema, JOIN on `users.bank_id`
- Update `ApprovalSync.build_tid_master_attrs` to populate `bank_name`
- Add `bank_name` field to `tid_masters` schema + migration
- Update PayoutItem generation to use actual `bank_name`
- **Files:** `tid_master.ex`, `approval_sync.ex`, migration, possibly new `mms/bank.ex`

### 3.2 Add Missing Fields to core_transactions

**Gap:** #11 — Missing `store_id`, `DR_CR`, `qr_tag`; #12 — Missing `onboarding_source/id`

**Change:** Single migration adding:
- `store_id` (string) — populated from TID Master lookup during sync
- `dr_cr` (string, "D"/"C") — derived from `transaction_type`: SALE/PURCHASE→"D", REFUND→"C"
- `qr_tag` (string) — carried from QR source table
- `spare_fields` (map) — for future extensibility
- `onboarding_source` (string) — from TID Master (sourced from MMS `users.created_by_role_id` mapping)
- `onboarding_id` (string) — from TID Master
- `bank_transfer_reference` (string) — for Phase 4.1

**Update core_transaction_sync.ex:**
- Set `dr_cr` based on transaction_type during POS and QR sync INSERT SQL
- Set `store_id` from TID Master lookup
- Carry `qr_tag` from QR source
- **Files:** `core_transaction.ex`, `core_transaction_sync.ex`, migration

### 3.3 Add onboarding_source / onboarding_id to TID Master

**Gap:** #12

**Change:**
- Add `onboarding_source`, `onboarding_id` to `tid_masters` schema + migration
- Populate from MMS during approval sync
- `onboarding_source` = `users.created_by_role_id` mapped to descriptive string
- `onboarding_id` = `users.id` or `merchant_metadata.id`
- **Files:** `tid_master.ex`, `approval_sync.ex`, migration

### 3.4 TID Master Listing UI

**Gap:** #10 — No browsing/search interface

**Change:**
- Create `SettlementLive.TidMasterBrowser` LiveView with:
  - Paginated table with search by MID/TID/merchant name
  - Filters: status, bank_code, MCC, onboarding_source
  - Drill-down showing full TID Master detail
  - Export to CSV
- Add route: `live "/admin/settlements/tid-master", SettlementLive.TidMasterBrowser, :index`
- **Files:** new `tid_master_browser.ex`, `router.ex`, `context.ex`

---

## Phase 4 — Settlement Engine Completions (Priority: High)

**Estimated effort:** 4 days

### 4.1 Save Transfer Reference to core_transactions After Bank Confirmation

**Gap:** #3 — Transfer reference NOT written back

**Current:** `mark_transactions_paid` only sets `settlement_status = "paid"`. The bank's transfer reference from ACK file is NOT stored on core_transactions.

**Change:**
- `bank_transfer_reference` field already added in Phase 3.2 migration
- Update `BankAckPollingWorker.process_ack_row` to extract transfer reference from ACK CSV
- Pass it to `mark_transactions_paid` and SET on core_transactions
- Update `mark_transactions_paid` query to include `bank_transfer_reference = $ref`
- **Files:** `bank_ack_polling_worker.ex`, `context.ex` or `payout_generator.ex`

### 4.2 VAT MIS Consolidated Report

**Gap:** #4 — Individual VAT invoices exist but no consolidated VAT MIS report

**Change:**
- Create `SettlementCore.VatMisGenerator` module:
  - Aggregates all VAT invoices for a settlement date
  - Produces CSV with columns: Merchant Name, MID, Transaction Count, Gross Amount, MDR Amount, VAT Amount, Invoice Number
  - Summary row at bottom: totals
- Add controller endpoint: `GET /api/v1/settlements/vat-mis/:date/download`
- Add "Download VAT MIS" button in MIS Approval LiveView
- Trigger generation alongside existing `VatInvoiceWorker`
- **Files:** new `vat_mis_generator.ex`, controller endpoint, `mis_approval.ex`

### 4.3 Risk Team Release UI (Settlement-Side)

**Gap:** #2 — Backend exists, no UI

**Note:** Full Risk Core UI is separate. This is a minimal settlement-facing view for Finance/Ops to see and release risk-held transactions.

**Change:**
- Create `SettlementLive.RiskHoldReview` LiveView:
  - Lists `core_transactions` where `risk_hold = true`, grouped by merchant
  - Shows risk rule name, transaction details, hold age
  - Actions: Release (single), Bulk Release (per merchant), with confirmation dialog
  - Calls existing `RiskReleaseProcessor.release/2` and `bulk_release_for_merchant/1`
- Route: `live "/admin/settlements/risk-holds", SettlementLive.RiskHoldReview, :index`
- **Files:** new `risk_hold_review.ex`, `router.ex`

### 4.4 Bank API Transmission Method

**Gap:** #6 — API method returns `:not_implemented`

**Change:**
- Implement `transmit_via_api/2` in `Bank.PayoutTransmitter`:
  - POST encrypted payout JSON to bank API endpoint (URL from config)
  - Use HTTPoison/Req with mTLS certificates
  - Parse response for confirmation reference
  - Update payout_batch.status based on response
- Config-driven dispatch: `transmission_method` on PayoutBatch determines SFTP vs API
- **Files:** `bank/payout_transmitter.ex`, `bank/api_client.ex`

---

## Phase 5 — Adjustments & Operational Enhancements (Priority: Medium)

**Estimated effort:** 2 days

### 5.1 Add LACR Adjustment Type

**Gap:** #14 — No `lacr` in adjustment type dropdown

**Change:**
- Add `"lacr"` to `@valid_types` in `MerchantAdjustment` schema
- Update `AdjustmentManager` LiveView dropdown to include "LACR (Loan Against Card Receivables)"
- Add LACR handling in MIS generator if calculation differs from other adjustments
- **Files:** `merchant_adjustment.ex`, `adjustment_manager.ex`

### 5.2 SLA Deadline Monitoring & Alerting

**Gap:** #16 — No alerting if 6AM/7AM/8AM deadlines missed

**Change:**
- Create `SettlementCore.Workers.SlaMonitorWorker` (Oban cron job):
  - Cron schedule: `"15 6,7,8 * * 1-5"` (Mon-Fri at 06:15, 07:15, 08:15)
  - At 06:15 → Check: reconciliation completed for previous business day? (check `dump_files.status` = "processed")
  - At 07:15 → Check: MIS generated? (check `settlement_mis` exists for settlement date)
  - At 08:15 → Check: MIS approved? (check `settlement_mis.approval_status = "approved"`)
  - If SLA breached → send alert email to Finance + Ops team via `SettlementNotifier`
- Add to Oban config in `config.exs`
- **Files:** new `workers/sla_monitor_worker.ex`, `settlement_notifier.ex`, `config/config.exs`

### 5.3 Cumulative Exception Export

**Gap:** #15 — Extends 2.4 with downloadable report

**Change:**
- Add "Export Exceptions CSV" button on Exception Dashboard
- CSV columns: Exception ID, Type (5.1–5.4), RRN, TID, Amount, Transaction Date, Exception Date, Age Days, Status, Resolution Notes
- **Files:** `exception_dashboard.ex`, controller endpoint

---

## Phase 6 — Merchant Portal & Self-Service (Priority: Medium)

**Estimated effort:** 3–4 days

### 6.1 Merchant-Facing Settlement Portal

**Gap:** #13 — No merchant self-service portal

**Change:**
- **Leverage Existing Merchant APIs:** Base the UI on the existing settlement APIs in `router.ex` (`/api/v1/merchant/settlements`, `/api/v1/merchant/settlement/:settlement_id`, etc.).
- **Create Merchant-Scoped LiveView UI:**
  - Create merchant-scoped routes under `/merchant/settlements/` for authenticated merchants.
  - `GET /merchant/settlements` — Settlement history (list of dates with status, amount, and links to reports).
  - `GET /merchant/settlements/:date/mpr` — Merchant Payment Report view/download.
  - `GET /merchant/settlements/:date/advice` — Settlement Advice view/download.
  - `GET /merchant/settlements/:date/vat-invoice` — VAT Invoice view/download (accessing the `vat_invoices` table).
- **Security & Scope:**
  - Scope all queries by `merchant_mid` extracted from the authenticated merchant session (`UserAuth`).
  - Read-only views — **No merchant approval or adjustment actions allowed**.
- **Files:** new LiveView module(s) under `SettlementLive.Merchant.*`, `router.ex`

---

## Phase 7 — Lower-Priority Items (Priority: Low)

**Estimated effort:** 2 days

### 7.1 MIS Discard Notes / Query Mechanism

**Gap:** Not in top 17 but referenced in requirement — Finance team should be able to add notes when discarding MIS

**Change:**
- Add `discard_reason` (string) and `discard_notes` (text) to `SettlementMis` schema + migration
- Update `Context.reject_settlement_mis/3` to accept and store reason + notes
- Show rejection reason/notes in MIS Approval UI history
- **Files:** `settlement_mis.ex`, `context.ex`, `mis_approval.ex`, migration

---

## Implementation Schedule

| Phase | Description | Est. Days | Dependencies | Priority |
|-------|------------|-----------|--------------|----------|
| **1** | **Rate model correction + interchange fix** | **4–5** | None | **CRITICAL (foundational bug)** |
| **2** | Reconciliation engine fixes | 2 | None (parallel with Phase 1) | **Critical** |
| **3** | TID Master & schema enrichment | 3 | Phase 1 (rates now correct) | **High** |
| **4** | Settlement engine completions | 4 | Phase 1 + 2 | **High** |
| **5** | Adjustments & operational | 2 | Phase 4 | **Medium** |
| **6** | Merchant portal | 3–4 | Phase 4 | **Medium** |
| **7** | Lower-priority items | 2 | Any | **Low** |

**Total estimated:** 20–22 working days

**Recommended parallel execution:**
- **Week 1:** Phase 1 + Phase 2 in parallel (critical fixes)
- **Week 2:** Phase 3 (schema enrichment + TID Master UI)
- **Week 3:** Phase 4 (settlement engine completions)
- **Week 4:** Phase 5 + Phase 6 + Phase 7 (medium/low priority items)

---

## Gap → Phase Mapping (All 17 Items)

| # | Gap Description | Severity | Phase | Task |
|---|----------------|----------|-------|------|
| 1 | Recon match key missing `auth_number` | Critical | **2** | 2.1 |
| 2 | No Risk team release UI | Critical | **4** | 4.3 |
| 3 | Transfer reference NOT saved to core_transactions | Critical | **4** | 4.1 |
| 4 | No VAT MIS consolidated report | Critical | **4** | 4.2 |
| 5 | `bank_name` missing from TID Master | High | **3** | 3.1 |
| 6 | Bank API transmission method is stub | High | **4** | 4.4 |
| 7 | **Interchange = mdr_rates, MDR = mcc_mdr_rates (swapped in code!)** | **CRITICAL** | **1** | 1.1–1.8 |
| 8 | No partial refund logic | High | **2** | 2.3 |
| 9 | `total_amount_on_hold` hardcoded to 0 | High | **2** | 2.2 |
| 10 | No TID Master browsing UI | High | **3** | 3.4 |
| 11 | Missing `store_id`, `DR_CR`, `qr_tag` on core_transactions | Medium | **3** | 3.2 |
| 12 | `onboarding_source`/`onboarding_id` missing | Medium | **3** | 3.3 |
| 13 | No Merchant Portal for self-serve MPR/Advice/VAT | Medium | **6** | 6.1 |
| 14 | LACR adjustment type not in dropdown | Medium | **5** | 5.1 |
| 15 | No cumulative exception aging report | Medium | **2+5** | 2.4, 5.3 |
| 16 | No SLA deadline monitoring/alerting | Medium | **5** | 5.2 |
| 17 | Interchange fee not updated during reconciliation | Low | **1** | 1.6 |

---

## Key Technical Decisions

### 1. Rate Model (CORRECTED)
**Decision:** `mdr_rates` via `mdr_templates` = **Interchange** (cost rate). `mcc_mdr_rates` = **MDR** (sell rate).

**Rationale:** Confirmed with business. Despite MMS naming, the `mdr_templates → mdr_rates` chain represents your interchange cost (per card type, determined by BIN), while `mcc_mdr_rates` represents what Mercury charges the merchant (per MCC category). The current code incorrectly treats both as the same thing with a fallback pattern.

### 2. Interchange Data Source Priority
**Decision:** Three-tier interchange resolution:
1. **YSP actual** (from switch dump at reconciliation time) — most accurate
2. **MMS `mdr_rates`** via templates (at sync time) — pre-calculated from merchant schedule
3. **Local `interchange_rates` table** (loaded from Mercury's master CSV) — scheme-level fallback

**Rationale:** YSP has actual per-transaction data. MMS has merchant-specific template data. Local table is Mercury's reference master.

### 3. MDR Data Source
**Decision:** `mcc_mdr_rates` from MMS is the **sole source** for MDR (sell rate).

**Rationale:** MDR is standardized per MCC + card type. No merchant-specific overrides needed (as confirmed: merchant-specific templates hold interchange, not MDR).

### 4. Net Settlement Formula
**Decision:** `net = gross - mdr - vat`. Interchange is NOT deducted from merchant payout.

**Rationale:** Interchange is Mercury's internal cost (paid to schemes). The merchant only sees: their gross amount minus Mercury's MDR charge minus VAT on that charge.

### 5. `interchange_rates` Local Table Retention
**Decision:** Keep the local `interchange_rates` table + schema. Do NOT delete.

**Rationale:** Still needed for: (a) Mercury's master interchange file shared with YSP, (b) fallback when MMS mdr_rates lack a rate for a specific card type, (c) possible future use when YSP sends bulk interchange data.

### 6. VAT Rate Source
**Decision:** Configurable via `Application.get_env(:settlement_core, :vat_rate, Decimal.new("5.0"))`.

**Rationale:** UAE VAT is currently 5% but may change. Runtime configuration avoids code changes.

### 7. Risk Hold Release Scope
**Decision:** Minimal settlement-facing UI only. Full risk rule engine UI remains in Risk Core.

**Rationale:** Per user direction — risk_core is a separate workstream.

### 8. Migration Batching
**Decision:** One migration per phase (max), combining related field additions.

**Rationale:** Fewer migrations = cleaner deployment. All Phase 3 fields go in one ALTER TABLE.