# Table of Offers mConnect Webhook

---

## CRED-6186 - 2026-05-18

### Overview

Added a new mConnect webhook event named **table-of-offers-webhook** for the Table of Offers flow. The webhook is triggered from `GradeController::MultiApprovalCheckJSONBRE()` after the decision table is generated through `saveDecisionInTable()`.

This follows the existing mConnect webhook delivery flow:

- `ConsumerApplicationController::webhook()`
- `EventMConnectWebhook`
- `ListenerMConnectWebhook`
- `command:mConnect`
- `MerchantApiIntegration::push()`

No new Laravel event, listener, command, queue, or webhook table structure is required for this change.

---

### Business Rules

- **Webhook name**: `table-of-offers-webhook`
- **Trigger point**: `GradeController::MultiApprovalCheckJSONBRE()`
- **Trigger condition**: Table of Offers path is active and `saveDecisionInTable()` has completed
- **Webhook matching**: Existing mConnect matching is used:
  - webhook row receives the event if `webhook.events` contains either:
    - `type = table-of-offers-webhook`
    - `status = table-of-offers-webhook`
- **Payload**: Real Table of Offers payload data is returned through `getMConnectPayload()`
- **Admin configuration**: The event is added to `$otherWebhookEvents` so it can be selected from the merchant webhook configuration UI

---

### Logic Added

#### Admin Event Option

`ConsumerApplicationController::$otherWebhookEvents`

- Added `table-of-offers-webhook` to the list of non-application-status webhook events.
- This makes the event available in the mConnect merchant API webhook UI.
- This does not trigger delivery by itself; the merchant webhook row must still subscribe to the event in `webhook.events`.

#### Payload Builder

`ConsumerApplicationController::getMConnectPayload($id, $status, $additionalFields = [])`

- Added a new switch case for `table-of-offers-webhook`.
- The payload sends Table of Offers data in the `offers` array.
- The payload aggregates all valid offers from both `loan_decision_approved_amount` and `loan_decision_table` without prioritizing one over the other.
- For `loan_decision_approved_amount`, valid offers (Banking ABB, Power Saving) are sent using `calculated_approved_amount` and `emi_amount`.
- For `loan_decision_table`, rows are independently evaluated:
  - If `status = 0`, a `minimum_tenure` offer is sent using `eligible_amount` and `emi`.
  - If `status != 0`, a `residual_emi` offer is sent using `eligible_amount` and `emi`.
  - Regardless of status, a `bureau_surrogate_limit` offer is sent using `bureau_surrogate_limit_amount` and `bureau_surrogate_limit_emi`.
- `roi`, `tenure`, and `processing_fee` are fetched from `merchant_offers` using the offer id.
- The combined collection is deduplicated by the suffixed `offer_id` before returning.
- **Amount Filtering:** Before any offers are returned in the payload, they are passed through a strict filter:
  - amount must be numeric
  - `amount >= 50000`
  - `amount <= original applied loan_amount (consumer_application.loan_amount)`
  - If no offers meet these criteria (e.g. all are filtered out), the payload `message` will explicitly state `"No applicable offer"`, and the `offers` array will be empty.
- **Persistence-side filtering:** The same amount filter is applied before Table of Offers rows are treated as valid during processing. This prevents a successful TOF decision from saving only rows that the webhook would later filter out.
  - Minimum tenure rows are saved only when the applied loan amount is `>= 50000`.
  - Residual EMI fallback rows are saved only when the calculated eligible amount is `>= 50000` and `<= original applied loan_amount`.
  - Bureau surrogate limit rows are saved only when the bureau surrogate amount is `>= 50000` and `<= original applied loan_amount`.
  - If a TOF path has no rows left after this filtering, that TOF path returns as failed instead of approving with an empty webhook offer list.
- **Suffix formatting:** The `offer_id` value will contain a suffix to uniquely indicate the specific flow type:
  - `_a` suffix indicates a Banking ABB offer (e.g., `123_a`).
  - `_p` suffix indicates a Power Saving offer (e.g., `123_p`).
  - `_b` suffix indicates a Bureau Surrogate Limit offer (e.g., `123_b`).
  - `_r` suffix indicates a Residual EMI offer (e.g., `123_r`).
  - Standard minimum tenure offers remain without a suffix.
  - The API wrapper accepts `offer_id` as either `integer` or `string` to accommodate this.

```json
{
  "message": "Table of offers webhook triggered",
  "bank_statement_link": "https://example.com/bank-statement/upload",
  "offers": [
    {
      "offer_id": "123_b",
      "eligible_amount": 100000,
      "emi": 9000,
      "roi": 12.5,
      "tenure": 12,
      "processing_fee": 2
    }
  ]
}
```

#### Trigger Method

`GradeController::triggerTableOfOffersWebhook($application_id)`

- Creates a `ConsumerApplicationController` instance.
- Calls `webhook()` with:
  - `type = table-of-offers-webhook`
  - `status = table-of-offers-webhook`
  - `data = getMConnectPayload($application_id, 'table-of-offers-webhook')`

#### Table of Offers Trigger Point

`GradeController::MultiApprovalCheckJSONBRE()`

- After `saveDecisionInTable($application, $status, true, $req_id)` completes in the Table of Offers flow, the new helper method is called:

```php
$this->triggerTableOfOffersWebhook($application_id);
```

This ensures the webhook is fired after the Table of Offers decision data has been generated.

#### Bureau Surrogate Decision Table Values

`loan_decision_table`

- Added two nullable columns:
  - `bureau_surrogate_limit_emi`
  - `bureau_surrogate_limit_amount`
- These values are saved only in the bureau surrogate Table of Offers path.
- When `processResidualEmi()` selects the `bureau_surrogate_limit` flow, the matching offer row is updated with the calculated bureau surrogate EMI and amount before the decision table rows are inserted.
- Bureau surrogate rows are eligible for this update only if the bureau surrogate amount passes the shared Table of Offers amount filter:
  - `amount >= 50000`
  - `amount <= original applied loan_amount`

#### Fresh Run Cleanup

`GradeController::saveDecisionInTable()`

- When a fresh `loan_decision_table` run starts, existing rows in `loan_decision_approved_amount` for the application are deleted.
- This prevents stale Power Saving / Banking ABB approved-amount rows from blocking the webhook fallback to the current `loan_decision_table` rows.
- Power Saving and Banking ABB custom EMI flows still delete and recreate their own `loan_decision_approved_amount` rows when those flows run.

---

### Configuration Required

To receive this webhook, the merchant's active row in the `webhook` table must include the new event name in the comma-separated `events` column:

```text
table-of-offers-webhook
```

The endpoint must also have:

- `webhook.status = 1`
- a valid `webhook.endpoint`
- the correct `webhook.merchant_id`

---

### Impact - Files Changed / Added

| File | Type | Change |
|------|------|--------|
| `app/Http/Controllers/Admin/ConsumerApplicationController.php` | Modified | Added `table-of-offers-webhook` to `$otherWebhookEvents` so it is selectable in the mConnect admin UI |
| `app/Http/Controllers/Admin/ConsumerApplicationController.php` | Modified | Added `table-of-offers-webhook` case in `getMConnectPayload()` with real Table of Offers payload data |
| `app/Http/Controllers/Grade/GradeController.php` | Modified | Added `triggerTableOfOffersWebhook()` helper method |
| `app/Http/Controllers/Grade/GradeController.php` | Modified | Triggered the webhook from `MultiApprovalCheckJSONBRE()` after `saveDecisionInTable()` completes for the Table of Offers flow |
| `app/Http/Controllers/Grade/GradeController.php` | Modified | Saved eligible amount and EMI for passing minimum tenure rows in `loan_decision_table` |
| `app/Http/Controllers/Grade/GradeController.php` | Modified | Saved bureau surrogate EMI and amount into `loan_decision_table` when the `bureau_surrogate_limit` flow is selected |
| `app/Http/Controllers/Grade/GradeController.php` | Modified | Applied the webhook amount filter before saving/accepting minimum tenure, residual EMI fallback, and bureau surrogate TOF rows |
| `app/Http/Controllers/Grade/GradeController.php` | Modified | Deleted stale `loan_decision_approved_amount` rows when a fresh `loan_decision_table` run starts |
| `database/migrations/2026_05_20_124221_add_bureau_surrogate_columns_to_loan_decision_table.php` | Added | Added nullable `bureau_surrogate_limit_emi` and `bureau_surrogate_limit_amount` columns to `loan_decision_table` |
| `documentation/12.table-of-offers-mconnect-webhook.md` | Added | Documentation for the new mConnect webhook event |

---

### Verification

- `php -l app/Http/Controllers/Admin/ConsumerApplicationController.php`
- `php -l app/Http/Controllers/Grade/GradeController.php`
- `git diff --check -- app/Http/Controllers/Admin/ConsumerApplicationController.php app/Http/Controllers/Grade/GradeController.php`

Expected runtime verification:

- Configure `table-of-offers-webhook` in the merchant webhook events list.
- Run a Table of Offers eligible application through `MultiApprovalCheckJSONBRE()`.
- Confirm `webhook_request_logs` contains the outbound request.
- Confirm the request payload has:
  - `type = table-of-offers-webhook`
  - `status = table-of-offers-webhook`
  - `data.offers` content from `loan_decision_approved_amount` or `loan_decision_table`.

---

### Update Table of Offers (API Wrapper)

The `/merchantRest/merchantSrv/update-table-of-offers` endpoint uses the `ssejson.json` state machine to parse the incoming string `offer_id` and apply the chosen offer directly to the application state without requiring a dedicated Laravel command (e.g., `MconnectTOFUpdate`).

#### JSON SQL Logic
1. **Validations**:
   - **Status Check**: Validates that the application `state` is in `('waiting for decision', 'pre approved', 'cf approved', 'approved')`. Uses `"equals"` operator in rule conditions.
   - **E-Sign Check**: Queries `loan_hypothecation_model` and halts with an error if any of the signing flags are true. Uses `"greaterThan"` operator in rule conditions.

2. **Flow Detection**: The `ssejson.json` state machine utilizes MySQL `CASE WHEN` and `LIKE` to map the `offer_id` suffix:
   - `_a` maps to `BANKING-ABB`
   - `_p` maps to `POWER-SAVING`
   - `_b` maps to `bureau_surrogate_limit`
   - `_r` maps to `residual_emi`
   - Otherwise, `minimum_tenure`.
   - A secondary `logic_type` maps `_a` and `_p` back to `residual_emi` for compatibility with existing persistence logic.

3. **Amount/EMI Extraction**: A strict `UNION ALL` SQL query is executed:
   - `loan_decision_approved_amount` is queried strictly matching the `policy_type`.
   - `loan_decision_table` is queried directly for `residual_emi`, `bureau_surrogate_limit`, or `minimum_tenure`.
   - The query correctly fetches the exact limit matching the offer's unique suffix.

4. **Advanced Comment Building**:
   - Queries previous state from `consumer_application`, `consumer_application_emi_calculation`, and `merchant_offers`.
   - Uses an advanced SQL `CONCAT` to dynamically construct an HTML comment specifying if the logic changed (e.g. residual to bureau), comparing the previous/new loan amount, ROI, and advance EMI, and injecting the request for 12 months bank statement & income proof.

#### JSON Persistence
Instead of executing complex EMI recalculations synchronously, the endpoint handles updates using the `persistence` block:
- **consumer_application**: Directly updates the `approved_amount`, `loan_amount`, `emi`, and the stripped `offer_id`.
- **consumer_application_emi_calculation**: Updates the `table_of_offers_logic` string using the mapped `logic_type` (preserves `residual_emi` for banking/power-saving flows).
- **consumer_application_emi_calculation_lock**: The calculation lock is intentionally NOT deleted, preserving the append-only history of the log. The background worker will safely append a new lock.
- **comments**: Adds a comment attributing the offer switch to the merchant.
- **queue_log**: Triggers `command:mConnect` with a `Merchant-Comment` status to dispatch the webhook.
- **jobs**: Triggers `reviseDocuments` using the internal jobs table. The Laravel background worker picks this up to execute `generateEMI()` which handles recreating the EMI schedule and documents based on the new amounts.