# Room Database

This document covers the full Room database schema, column descriptions, service-layer business rules, and how each transaction step reads from and writes to the database.

---

## 1. Overview

| Property | Value |
|----------|-------|
| Database file | `acquire.db` |
| Current version | 6 |
| Journal mode | `TRUNCATE` (safe on power loss) |
| Main thread access | Allowed (`allowMainThreadQueries()`) |
| Entities | `Record`, `Merchant`, `ReversalData` |
| Tables | `T_RECORD`, `T_MERCHANT`, `T_REVERSAL_DATA` |
| TypeConverters | None — all columns use native Room types |
| Foreign keys | None — tables linked by soft MID + TID values |

**Initialisation** — `AcquireDatabase.getInstance()` is a double-checked-locking singleton. The database is created lazily on first access using `BaseApplication.getAppContext()`. All migrations are registered at build time.

```
database/
  AcquireDatabase.java      — @Database class, singleton, migrations
  dao/
    RecordDao.java
    MerchantDao.java
    ReversalDataDao.java
  model/
    Record.java
    Merchant.java
    ReversalData.java
  service/
    RecordService.java       (interface)
    MerchantService.java     (interface)
    ReversalDataService.java (interface)
    impl/
      RecordServiceImpl.java
      MerchantServiceImpl.java
      ReversalDataServiceImpl.java
```

---

## 2. Schema Migrations

| From → To | SQL |
|-----------|-----|
| 1 → 2 | `ALTER TABLE T_RECORD ADD COLUMN MW_REFERENCE TEXT` |
| 2 → 3 | `ALTER TABLE T_RECORD ADD COLUMN CLOUD_RECEIPT_DATA TEXT` |
| 3 → 4 | `ALTER TABLE T_RECORD ADD COLUMN RESPONSE_DATA_CLOUD TEXT` |
| 4 → 5 | `ALTER TABLE T_RECORD ADD COLUMN CLOUD_LOGO_PATH TEXT` |
| 5 → 6 | `ALTER TABLE T_RECORD ADD COLUMN CLOUD_FOOTER_LOGO_PATH TEXT` |

Columns added in v3 (`CLOUD_RECEIPT_DATA`) and v5–6 (`CLOUD_LOGO_PATH`, `CLOUD_FOOTER_LOGO_PATH`) are **deprecated** and no longer written by the application. `RESPONSE_DATA_CLOUD` (v4) is the active column for host-supplied cloud receipt JSON.

---

## 3. T_RECORD — Transaction Records

**Entity:** `database/.../model/Record.java`  
**DAO:** `database/.../dao/RecordDao.java`  
**Service:** `database/.../service/impl/RecordServiceImpl.java`

Only **approved** transactions (response code `"00"` / `"10"`) are written to this table. Declined transactions create a temporary in-memory `Record` for receipt printing but never persist it.

### Column Reference

| Column | Java Field | SQL Type | Description |
|--------|-----------|----------|-------------|
| `ID` | `id` | INTEGER PK AUTOINCREMENT | Row identifier |
| `MID` | `mid` | TEXT | Merchant ID |
| `TID` | `tid` | TEXT | Terminal ID |
| `TRANS_TYPE` | `transType` | TEXT | Transaction type string — matches `TransType` constants (e.g. `SALE`, `VOID_SALE`, `REFUND`, `PRE_AUTH`, `AUTH_COMPLETE`, `QR_CODE`, `SETTLE`) |
| `PROCESS_CODE` | `processCode` | TEXT | ISO 8583 Field 3 process code |
| `STATUS` | `status` | INTEGER | `TransStatus.SUCCESS = 0`, `CANCELLED` — set to `CANCELLED` by void steps |
| `CARD_NO` | `cardNo` | TEXT | Masked card number (PAN) |
| `ENTRY_MODE` | `entryMode` | INTEGER | Card entry method — `MAG`, `INSERT`, `TAP`, `MANUAL` enum ordinal |
| `AMOUNT` | `amount` | INTEGER | Transaction amount in the smallest currency unit (cents) |
| `TIP_AMOUNT` | `tipAmount` | INTEGER | Tip / gratuity in cents |
| `BILL_AMOUNT` | `billAmount` | INTEGER | Base amount before tip in cents |
| `TRACE_NO` | `traceNo` | TEXT | System trace / STAN — enforced unique by service layer |
| `TIME` | `time` | TEXT | Transaction time `HHmmss` — from host Field 12 |
| `DATE` | `date` | TEXT | Transaction date `yyyyMMdd` — from host Field 13 |
| `EXP_DATE` | `expDate` | TEXT | Card expiry `yyMM` |
| `FIELD_22` | `field22` | TEXT | ISO 8583 Field 22 — POS entry mode code |
| `CARD_SERIAL_NO` | `cardSerialNo` | TEXT | Card sequence number (EMV tag `0x9F36` / mag) |
| `TRACK2` | `track2` | TEXT | Magnetic stripe track 2 data |
| `TRACK3` | `track3` | TEXT | Magnetic stripe track 3 data |
| `REFER_NO` | `referNo` | TEXT | Retrieval Reference Number — from host Field 37 |
| `AUTH_CODE` | `authCode` | TEXT | Authorisation code — from host Field 38 |
| `RESPONSE_CODE` | `responseCode` | TEXT | ISO 8583 response code — `"00"` approved, `"10"` partial |
| `BATCH_NO` | `batchNo` | TEXT | Settlement batch number |
| `ORIGINAL_BATCH` | `origBatch` | TEXT | Original batch number — copied from parent for void/refund |
| `ORIGINAL_TRACE_NO` | `origTraceNo` | TEXT | Original transaction trace — populated for void/refund/auth-complete |
| `ORIGINAL_AUTH_CODE` | `origAuthCode` | TEXT | Original authorisation code — for void/refund |
| `ORIGINAL_REFER_NO` | `origReferNo` | TEXT | Original retrieval reference — for void/refund |
| `ORIGINAL_DATE` | `origDate` | TEXT | Original transaction date `yyyyMMdd` — for void/refund |
| `CARD_ORGANIZATION` | `cardOrg` | TEXT | Card scheme name (`Visa`, `MasterCard`, `Amex`, etc.) — links to `T_MERCHANT.CARD_ORGANIZATION` |
| `BATCH_UP_FLAG` | `batchUpFlag` | INTEGER (bool) | `1` after record is uploaded during settlement, `0` initially |
| `CURRENCY_CODE` | `currencyCode` | TEXT | ISO 4217 currency code (e.g. `840` for USD) |
| `QR_PAY_CODE` | `qrPayCode` | TEXT | QR code string for scan-to-pay |
| `BIZ_ORDER_NO` | `bizOrderNo` | TEXT | Business / platform order reference (QR transactions) |
| `FIELD_55` | `field55` | TEXT | ISO 8583 Field 55 — hex-encoded EMV cryptogram data |
| `SIGN_PATH` | `signPath` | TEXT | File path to saved customer signature bitmap |
| `FREE_SIGN` | `freeSign` | INTEGER (bool) | `1` = signature waived (low-value contactless, etc.) |
| `FREE_PIN` | `freePin` | INTEGER (bool) | `1` = PIN was not collected (derived: `pinBlock` AND `offlinePinBlock` both empty at save time) |
| `OUT_ORDER_NO` | `outOrderNo` | TEXT | External system order number |
| `CARD_SN` | `cardSn` | TEXT | EMV card serial number (tag `0x5F34`) |
| `EMV_PRINT_DATA` | `emvPrintData` | TEXT | Formatted EMV string for receipt printing (AID, TVR, CID, TSI, AC) |
| `REMARKS` | `remarks` | TEXT | Free-text receipt footer remarks |
| `CLOUD_RECEIPT_DATA` | `cloudReceiptData` | TEXT | **Deprecated (v3)** — superseded by `RESPONSE_DATA_CLOUD` |
| `RESPONSE_DATA_CLOUD` | `responseDataCloud` | TEXT | Full JSON from ISO 8583 Field 63 — cloud receipt rendered from this on print and reprint |
| `CLOUD_LOGO_PATH` | `cloudLogoPath` | TEXT | **Deprecated (v5)** — not written |
| `CLOUD_FOOTER_LOGO_PATH` | `cloudFooterLogoPath` | TEXT | **Deprecated (v6)** — not written |
| `MW_REFERENCE` | `middlewareReference` | TEXT | ISO 8583 Field 60 — bank-assigned values (MID, TID, date, STAN, ref); parsed by `BankDetailsParser` for local receipt layout |

### RecordDao Queries

```sql
-- Insert (OnConflictStrategy.REPLACE)
INSERT OR REPLACE INTO t_record (...) VALUES (...)

-- Core lookups
SELECT * FROM t_record WHERE TRACE_NO = ?                        -- findByTraceNo
SELECT * FROM t_record WHERE REFER_NO = ?                        -- findByReferNum
SELECT * FROM t_record WHERE AUTH_CODE = ?                       -- findByAuthCode
SELECT * FROM t_record WHERE OUT_ORDER_NO = ?                    -- findByOutOrderNo
SELECT * FROM t_record WHERE BIZ_ORDER_NO = ?                   -- findByQrOrder
SELECT * FROM t_record WHERE TRANS_TYPE = ? AND STATUS = 0       -- findByTransType

-- Pagination (newest first)
SELECT * FROM t_record ORDER BY ID DESC LIMIT ?, ?               -- findByRangeDesc
SELECT * FROM t_record ORDER BY ID     LIMIT ?, ?               -- findByRange (ASC)
SELECT * FROM t_record WHERE MID = ? AND TID = ? ORDER BY ID LIMIT ?, ?

-- Counts
SELECT COUNT(*) FROM t_record
SELECT COUNT(*) FROM t_record WHERE MID = ? AND TID = ?

-- Delete
DELETE FROM t_record WHERE ID = ?
DELETE FROM t_record WHERE MID = ? AND TID = ?   -- post-settlement wipe per merchant
DELETE FROM t_record                              -- full wipe

-- Dynamic SQL (via @RawQuery + SimpleSQLiteQuery)
SELECT * FROM T_RECORD [WHERE <filters>] ORDER BY ID DESC LIMIT ?, ?
SELECT COUNT(*) FROM T_RECORD [WHERE <filters>]
-- Filters built at runtime: TRANS_TYPE IN (...), STATUS IN (...), DATE BETWEEN ? AND ?
```

### RecordServiceImpl Business Rules

- **Duplicate trace prevention** — `add()` calls `findByTrace()` first; rejects insert if a record with that `TRACE_NO` already exists.
- **Auto ID resolution on update** — if `record.id == 0`, `update()` looks up the row by `TRACE_NO` and fills in the ID before issuing `UPDATE`.
- **Dynamic filtered pagination** — `findByPageDesc(transTypes[], status[], startDate, endDate, page, size)` builds a `WHERE` clause at runtime using the supplied filter arrays.

---

## 4. T_MERCHANT — Merchant Configuration

**Entity:** `database/.../model/Merchant.java`  
**DAO:** `database/.../dao/MerchantDao.java`  
**Service:** `database/.../service/impl/MerchantServiceImpl.java`

One row per card organisation per merchant. The combination of `MID` + `TID` must be unique within the table (enforced by service layer).

### Column Reference

| Column | Java Field | SQL Type | Description |
|--------|-----------|----------|-------------|
| `ID` | `id` | INTEGER PK AUTOINCREMENT | Row identifier |
| `MID` | `mid` | TEXT | Merchant ID — matches `T_RECORD.MID` |
| `TID` | `tid` | TEXT | Terminal ID — matches `T_RECORD.TID` |
| `CARD_ORGANIZATION` | `cardOrg` | TEXT | Card scheme this merchant row covers (`Visa`, `MasterCard`, `DEFAULT`, etc.) — used as lookup key by `MerchantService.find(cardOrg)` |
| `BATCH_NO` | `batchNo` | TEXT | Current open batch number — incremented after each successful settlement |
| `SETTLE_EQUAL` | `settleEqual` | INTEGER (bool) | `1` when host-confirmed totals match terminal totals |
| `SETTLE_STEP` | `settleStep` | INTEGER | Settlement progress: `0` = idle, `STEP_SETTLEMENT_SENT` = request sent, `STEP_BATCH_UP` = batch upload sent. Persists partial state across restarts so settlement can resume |
| `SETTLE_DATE` | `settleDate` | TEXT | Date of last successful settlement `yyyyMMdd` |
| `SETTLE_TIME` | `settleTime` | TEXT | Time of last successful settlement `HHmmss` |
| `LAST_RECEIPT` | `lastReceipt` | TEXT | Last receipt data reference |

### MerchantDao Queries

```sql
INSERT OR REPLACE INTO T_MERCHANT (...) VALUES (...)

SELECT * FROM T_MERCHANT
SELECT * FROM T_MERCHANT WHERE MID = ? AND TID = ?
SELECT * FROM T_MERCHANT WHERE CARD_ORGANIZATION = ?

UPDATE T_MERCHANT SET ... WHERE ID = ?

DELETE FROM T_MERCHANT WHERE ID = ?
DELETE FROM T_MERCHANT
```

### MerchantServiceImpl Business Rules

- **Duplicate prevention** — `add()` calls `find(mid, tid)` first; rejects insert if a row already exists for that MID + TID pair.
- **Settlement halt recovery** — `clearHalt()` sets `SETTLE_STEP = 0` on a list of merchants, used after a settlement attempt is abandoned or completed.
- `SETTLE_STEP` is the sole indicator of mid-settlement state. If the app restarts during settlement, the non-zero `SETTLE_STEP` signals that the previous attempt was incomplete.

---

## 5. T_REVERSAL_DATA — Pending Reversals

**Entity:** `database/.../model/ReversalData.java`  
**DAO:** `database/.../dao/ReversalDataDao.java`  
**Service:** `database/.../service/impl/ReversalDataServiceImpl.java`

This table holds **at most one row** at any time. It stores the minimum transaction data needed to send a reversal if the original request was sent but no response was received (network timeout, power loss). The service enforces the singleton constraint.

### Column Reference

| Column | Java Field | SQL Type | Description |
|--------|-----------|----------|-------------|
| `ID` | `id` | INTEGER PK AUTOINCREMENT | Row identifier |
| `HAS_SEND` | `hasSend` | INTEGER | Number of reversal attempts made — capped at 3 |
| `MID` | `mid` | TEXT | Merchant ID |
| `TID` | `tid` | TEXT | Terminal ID |
| `TRANS_TYPE` | `transType` | TEXT | Original transaction type |
| `CARD_NO` | `cardNo` | TEXT | Card number |
| `ENTRY_MODE` | `entryMode` | INTEGER | Card entry mode ordinal |
| `PROCESS_CODE` | `processCode` | TEXT | ISO 8583 process code of original request |
| `AMOUNT` | `amount` | INTEGER | Original transaction amount in cents |
| `TRACE_NO` | `traceNo` | TEXT | Original STAN — used to match response on retry |
| `EXP_DATE` | `expDate` | TEXT | Card expiry `yyMM` |
| `FIELD_22` | `field22` | TEXT | POS entry mode (Field 22) |
| `CARD_SERIAL_NO` | `cardSerialNo` | TEXT | Card sequence number |
| `SERVER_CODE` | `serverCode` | TEXT | Server / issuer code |
| `ORIGINAL_AUTH_CODE` | `origAuthCode` | TEXT | Auth code from original transaction |
| `CURRENCY_CODE` | `currencyCode` | TEXT | ISO 4217 currency code |
| `FIELD_55` | `field55` | TEXT | EMV cryptogram data — updated after response if partial |
| `NII` | `nii` | TEXT | Network Identifier Index |
| `date` | `date` | TEXT | Original transaction date `yyyyMMdd` |
| `time` | `time` | TEXT | Original transaction time `HHmmss` |

### ReversalDataServiceImpl Business Rules

- **Singleton enforcement** — `add()` rejects insert if `getReverseRecord()` returns non-null; only one reversal row is ever stored.
- **`getReverseRecord()`** returns `findAll().get(0)` if list is non-empty, otherwise `null`.
- **`updateField55()`** updates only the `FIELD_55` column of the existing row — used when the EMV cryptogram must be refreshed before a retry.
- **Lifecycle:**
  - Written by `PreCheckStep` / packing steps *before* sending the host request.
  - Deleted by `AddRecordStep` after the transaction is successfully saved.
  - Deleted by `ClearSettleStep` after settlement completes.
  - Deleted if the host returns a definitive decline (response code not `UC`/`FL`).

---

## 6. DataConverter — PubBean ↔ Record Mapping

`core/.../tools/DataConverter.java` is the single conversion point between the in-memory transaction model (`PubBean`) and the persisted model (`Record` / `ReversalData`).

### `pubBeanToRecord()` — write path

Called by `AddRecordStep` and `DeclinedReceiptStep` (temporary record only):

| Record column set | Source in PubBean |
|-------------------|------------------|
| MID / TID | `getMid()` / `getTid()` |
| TRANS_TYPE | `getTransType()` |
| STATUS | hardcoded `TransStatus.SUCCESS` |
| CARD_NO | `getCardNo()` |
| AMOUNT / TIP_AMOUNT / BILL_AMOUNT | `getAmount()` / `getTipAmount()` / `getBillAmount()` |
| TRACE_NO / BATCH_NO | `getTraceNo()` / `getBatchNo()` |
| DATE / TIME / EXP_DATE | `getDate()` / `getTime()` / `getExpDate()` |
| REFER_NO / AUTH_CODE / RESPONSE_CODE | `getReferNo()` / `getAuthCode()` / `getResultCode()` |
| ORIGINAL_* columns | `getOrigTraceNo()`, `getOrigDate()`, `getOrigAuthCode()`, `getOrigReferNo()` |
| CARD_ORGANIZATION | `getCardOrg()` |
| FIELD_55 / EMV_PRINT_DATA | `getField55()` / `getEmvPrintData()` |
| FREE_PIN | `getPinBlock()` AND `getOfflinePinBlock()` both empty → `true` |
| FREE_SIGN / SIGN_PATH | `isFreeSign()` / `getSignPath()` |
| RESPONSE_DATA_CLOUD | `getResponseDataCloud()` (Field 63 JSON) |
| MW_REFERENCE | `getBankDetails()` (Field 60) |
| QR_PAY_CODE / BIZ_ORDER_NO | `getQrPayCode()` / `getBizOrderNo()` |

### `recordToPubBean()` — read path

Called by reprint and void/refund flows to restore state from a stored record:

Mirrors the write mapping above. Notable: `RESPONSE_DATA_CLOUD` → `setResponseDataCloud()` and `MW_REFERENCE` → `setBankDetails()` are both restored, so reprint produces an identical receipt to the original.

### `pubBeanToReversal()` / `reversalToPubBean()`

Subset mapping — only the fields required to reconstruct a reversal ISO 8583 message. No receipt, signature, or cloud-receipt fields are included in `ReversalData`.

---

## 7. Database Access by Transaction Step

### Write Steps

| Step | Table | Operation | Condition |
|------|-------|-----------|-----------|
| `AddRecordStep` | `T_RECORD` | INSERT | Approved only (`"00"` / `"10"`) |
| `AddRecordStep` | `T_REVERSAL_DATA` | DELETE ALL | After successful record save |
| `PackSaleStep` (pre-send) | `T_REVERSAL_DATA` | INSERT | Before host request is sent |
| `PackSettleStep` | `T_RECORD` | UPDATE `BATCH_UP_FLAG = 1` per record | After each batch-upload ACK |
| `PackSettleStep` | `T_MERCHANT` | UPDATE `SETTLE_STEP` | At each settlement phase |
| `ClearSettleStep` | `T_RECORD` | DELETE WHERE MID+TID | After settlement confirmed |
| `ClearSettleStep` | `T_MERCHANT` | UPDATE `SETTLE_STEP = 0` | After settlement confirmed |
| `ClearSettleStep` | `T_REVERSAL_DATA` | DELETE ALL | After settlement |

### Read Steps

| Step | Table | Query | Purpose |
|------|-------|-------|---------|
| `FindOrigTraceStep` | `T_RECORD` | `WHERE TRACE_NO = ?` | Locate original for void/refund |
| `ReprintReceipt` | `T_RECORD` | `WHERE TRACE_NO = ?` | Load record for reprint |
| `PackSettleStep` | `T_RECORD` | `WHERE MID = ? AND TID = ?` (paginated) | Collect all records for batch |
| `PackSettleStep` | `T_MERCHANT` | `findAll()` | Get merchant list for settlement |
| `PreCheckStep` | `T_REVERSAL_DATA` | `findAll()` | Check for pending reversal on startup |
| History/Search UI | `T_RECORD` | Dynamic SQL with filters | Paginated transaction list |

---

## 8. Key Business Rules Summary

| Rule | Where enforced |
|------|---------------|
| Only approved transactions (`"00"`/`"10"`) are saved | `AddRecordStep` — checks `ResultCode.isApproved()` before insert |
| `TRACE_NO` must be unique in `T_RECORD` | `RecordServiceImpl.add()` — pre-check `findByTrace()` |
| `MID` + `TID` must be unique in `T_MERCHANT` | `MerchantServiceImpl.add()` — pre-check `find(mid, tid)` |
| Only one row allowed in `T_REVERSAL_DATA` | `ReversalDataServiceImpl.add()` — rejects if `getReverseRecord() != null` |
| Cloud receipt JSON preserved verbatim for reprint | `RESPONSE_DATA_CLOUD` column — written by `AddRecordStep`, read by reprint flow |
| `BATCH_UP_FLAG` tracks per-record settlement upload | Set to `1` in `PackSettleStep` after batch-upload ACK; reset by record deletion |
| Settlement state survives app restart | `T_MERCHANT.SETTLE_STEP` — non-zero value triggers resume on next settlement attempt |
| Reversal retried up to 3 times | `T_REVERSAL_DATA.HAS_SEND` counter incremented per attempt |

---

## 9. Relevant Source Files

| File | Role |
|------|------|
| `database/.../AcquireDatabase.java` | `@Database` singleton, version, migrations |
| `database/.../model/Record.java` | `T_RECORD` entity |
| `database/.../model/Merchant.java` | `T_MERCHANT` entity |
| `database/.../model/ReversalData.java` | `T_REVERSAL_DATA` entity |
| `database/.../dao/RecordDao.java` | All record SQL queries |
| `database/.../dao/MerchantDao.java` | All merchant SQL queries |
| `database/.../dao/ReversalDataDao.java` | All reversal SQL queries |
| `database/.../service/impl/RecordServiceImpl.java` | Duplicate-trace guard, dynamic pagination |
| `database/.../service/impl/MerchantServiceImpl.java` | Duplicate-MID guard, clearHalt |
| `database/.../service/impl/ReversalDataServiceImpl.java` | Singleton enforcement, updateField55 |
| `core/.../tools/DataConverter.java` | PubBean ↔ Record ↔ ReversalData mapping |
| `core/.../steps/AddRecordStep.java` | Approved-only insert, reversal cleanup |
| `core/.../steps/FindOrigTraceStep.java` | Void/refund record lookup |
| `core/.../trans/impl/settle/PackSettleStep.java` | Batch read + BATCH_UP_FLAG update |
| `core/.../trans/impl/settle/ClearSettleStep.java` | Post-settlement DELETE |