# POS Transaction Reversal Cleanup Runbook

## Background

For reversals with `reversal_reason = 'POS_CONNECTION_LOST_RECOVERY'`, two gaps existed prior to the fix in `ReversalService.updateReversalTransaction`:

1. If a reversal succeeded on a **retry** (not the first attempt), the reversal row correctly became `COMPLETED`, but the matching `pos_transaction` row was never deleted.
2. If a reversal exhausted all retries (`MAX_RETRIES_EXCEEDED`), nothing flagged the matching `pos_transaction` row for manual review.

The code fix only applies **going forward**. Rows that already reached these states before the fix was deployed need to be cleaned up manually using the queries below. Run the `SELECT` in each scenario first, confirm the row count/content looks right, and only then run the mutating statement — ideally in a transaction, against a fresh backup.

The join key: `pos_transaction_reversal.original_temp_txn_id = pos_transaction.id` (this is only valid for `reversal_reason = 'POS_CONNECTION_LOST_RECOVERY'` — other reasons link to `pos_temp_transaction`, not `pos_transaction`). The business-key columns (`s_tid`, `b_tid`, `b_mid`, `b_tid_stan`, amount) are matched too, as an extra safety check against a stale/incorrect FK.

## Scenario A — Orphaned `COMPLETED` rows

These are `pos_transaction` rows whose reversal already succeeded (with the bank) but were never removed.

### A.1 Preview

```sql
SELECT
    ptr.id               AS reversal_id,
    ptr.reversal_status,
    ptr.retry_count,
    ptr.updated_dateTime AS reversal_updated,
    pt.id                AS transaction_id,
    pt.b_tid,
    pt.b_mid,
    pt.b_tid_stan,
    pt.total_amount,
    pt.created_dateTime  AS transaction_created
FROM pos_transaction_reversal ptr
JOIN pos_transaction pt
    ON pt.id = ptr.original_temp_txn_id
    AND pt.s_tid = ptr.s_tid
    AND pt.b_tid = ptr.b_tid
    AND pt.b_mid = ptr.b_mid
    AND pt.b_tid_stan = ptr.b_tid_stan
    AND pt.total_amount = ptr.original_amount
WHERE ptr.reversal_reason = 'POS_CONNECTION_LOST_RECOVERY'
  AND ptr.reversal_status = 'COMPLETED'
ORDER BY ptr.updated_dateTime DESC;
```

Confirm the row count and spot-check a few rows before proceeding.

### A.2 Cleanup

```sql
DELETE pt
FROM pos_transaction pt
JOIN pos_transaction_reversal ptr
    ON pt.id = ptr.original_temp_txn_id
    AND pt.s_tid = ptr.s_tid
    AND pt.b_tid = ptr.b_tid
    AND pt.b_mid = ptr.b_mid
    AND pt.b_tid_stan = ptr.b_tid_stan
    AND pt.total_amount = ptr.original_amount
WHERE ptr.reversal_reason = 'POS_CONNECTION_LOST_RECOVERY'
  AND ptr.reversal_status = 'COMPLETED';
```

Run inside a transaction (`START TRANSACTION; ... ; COMMIT;`) so you can `ROLLBACK` if the affected row count doesn't match the A.1 preview.

## Scenario B — `MAX_RETRIES_EXCEEDED` rows missing the manual-review flag

These are `pos_transaction` rows where the bank reversal never succeeded, retries are exhausted, and the row needs a visible flag for manual handling.

### B.1 Preview

```sql
SELECT
    ptr.id               AS reversal_id,
    ptr.reversal_status,
    ptr.retry_count,
    pt.id                AS transaction_id,
    pt.metadata          AS current_metadata,
    pt.b_tid,
    pt.b_mid,
    pt.total_amount
FROM pos_transaction_reversal ptr
JOIN pos_transaction pt
    ON pt.id = ptr.original_temp_txn_id
    AND pt.s_tid = ptr.s_tid
    AND pt.b_tid = ptr.b_tid
    AND pt.b_mid = ptr.b_mid
    AND pt.b_tid_stan = ptr.b_tid_stan
    AND pt.total_amount = ptr.original_amount
WHERE ptr.reversal_reason = 'POS_CONNECTION_LOST_RECOVERY'
  AND ptr.reversal_status = 'MAX_RETRIES_EXCEEDED';
```

Check `current_metadata` for each row — most should be a JSON object (e.g. `{"cardHolderName":"..."}`); note any row where it isn't valid JSON (null, empty, or plain text) since those need the fallback in B.2b.

### B.2a Backfill — metadata is valid JSON (or NULL)

`JSON_SET` merges the new key into existing JSON without touching other fields; on `NULL`/empty metadata it starts from `{}`:

```sql
UPDATE pos_transaction pt
JOIN pos_transaction_reversal ptr
    ON pt.id = ptr.original_temp_txn_id
    AND pt.s_tid = ptr.s_tid
    AND pt.b_tid = ptr.b_tid
    AND pt.b_mid = ptr.b_mid
    AND pt.b_tid_stan = ptr.b_tid_stan
    AND pt.total_amount = ptr.original_amount
SET pt.metadata = JSON_SET(COALESCE(NULLIF(pt.metadata, ''), '{}'), '$.transactionStatus', 'UNDER_MANUAL_REVIEW')
WHERE ptr.reversal_reason = 'POS_CONNECTION_LOST_RECOVERY'
  AND ptr.reversal_status = 'MAX_RETRIES_EXCEEDED'
  AND (pt.metadata IS NULL OR pt.metadata = '' OR JSON_VALID(pt.metadata));
```

### B.2b Backfill — metadata is legacy/non-JSON text

For any rows flagged in B.1 where `metadata` is non-empty but not valid JSON (e.g. the old `"reversed:true;reversalTime:...;reversalId:..."` format), preserve the original text under a `legacyMetadata` key instead of overwriting it:

```sql
UPDATE pos_transaction pt
JOIN pos_transaction_reversal ptr
    ON pt.id = ptr.original_temp_txn_id
    AND pt.s_tid = ptr.s_tid
    AND pt.b_tid = ptr.b_tid
    AND pt.b_mid = ptr.b_mid
    AND pt.b_tid_stan = ptr.b_tid_stan
    AND pt.total_amount = ptr.original_amount
SET pt.metadata = JSON_OBJECT('legacyMetadata', pt.metadata, 'transactionStatus', 'UNDER_MANUAL_REVIEW')
WHERE ptr.reversal_reason = 'POS_CONNECTION_LOST_RECOVERY'
  AND ptr.reversal_status = 'MAX_RETRIES_EXCEEDED'
  AND pt.metadata IS NOT NULL
  AND pt.metadata != ''
  AND NOT JSON_VALID(pt.metadata);
```

### B.3 Verify

Re-run B.1 — every row's `current_metadata` should now include `"transactionStatus": "UNDER_MANUAL_REVIEW"`.

## Notes

- Requires MySQL 5.7.8+ / MariaDB 10.2.7+ for `JSON_SET`/`JSON_VALID`/`JSON_OBJECT`. Check your server version if these functions are unavailable.
- Run Scenario B before Scenario A if in doubt — B is non-destructive (metadata update only), A is destructive (row delete).
- These are one-time backfills for pre-fix data. Once the code fix is deployed, new reversals reaching `COMPLETED` or `MAX_RETRIES_EXCEEDED` are handled automatically.