# Payment Gateway Transaction Storage Implementation

## Overview

This implementation creates a separate transaction storage mechanism for Payment Gateway transactions in the `pg_pos_transaction` table, distinct from regular POS terminal transactions stored in `pos_transaction`.

## Database Schema

### Table: `pg_pos_transaction`

The table has an **identical structure** to the `pos_transaction` table with all 45 fields:

```sql
CREATE TABLE pg_pos_transaction (
  id BIGINT NOT NULL AUTO_INCREMENT,
  s_txn_type VARCHAR(25),
  s_tid VARCHAR(8),
  s_mid VARCHAR(15),
  s_tid_stan VARCHAR(6),
  s_tid_invoiceno VARCHAR(6),
  s_tid_batchno VARCHAR(6),
  b_tid VARCHAR(8),
  b_mid VARCHAR(15),
  acquirer_id BIGINT,
  b_tid_stan VARCHAR(6),
  b_tid_invoiceno VARCHAR(6),
  b_tid_batchno VARCHAR(6),
  b_tid_date VARCHAR(8),
  b_tid_time VARCHAR(6),
  entry_mode VARCHAR(3),
  condition_code VARCHAR(2),
  currency_code VARCHAR(3),
  mti VARCHAR(4),
  proc_code VARCHAR(6),
  total_amount DECIMAL(12,2),
  auth_amount DECIMAL(12,2),
  cash_amount DECIMAL(12,2),
  tip_amount DECIMAL(12,2),
  approval_code VARCHAR(6),
  reference_no VARCHAR(12),
  response_code VARCHAR(2),
  response_message VARCHAR(256),
  closure_status VARCHAR(25) NOT NULL DEFAULT 'OPEN',
  mcc_code VARCHAR(4),
  encrypted_track2 VARCHAR(256),
  encrypted_pan VARCHAR(256),
  hash_pan CHAR(64),
  masked_card_no VARCHAR(19),
  encrypted_expiry VARCHAR(64),
  pan_seq VARCHAR(3),
  emv_data TEXT,
  acquirer_reference_no VARCHAR(23),
  scheme_reference_no VARCHAR(23),
  created_dateTime DATETIME,
  updated_dateTime DATETIME,
  metadata TEXT,
  order_number VARCHAR(50),
  parent_txn_id BIGINT,
  completed_dateTime DATETIME,
  PRIMARY KEY (id),
  KEY idx_pg_transaction_btid_bmid (b_tid, b_mid),
  KEY idx_pg_transaction_stid (s_tid),
  KEY idx_pg_transaction_created (created_dateTime),
  KEY idx_pg_transaction_response_code (response_code),
  KEY idx_pg_transaction_approval_code (approval_code),
  KEY idx_pg_transaction_amount (total_amount),
  KEY idx_pg_transaction_mti (mti)
)
```

## Implementation Files

### 1. Migration File
**Location:** `priv/repo/migrations/20260507074839_create_pg_pos_transaction.exs`

Creates the `pg_pos_transaction` table with identical schema to `pos_transaction`.

**To run the migration:**
```bash
mix ecto.migrate
```

### 2. Ecto Schema
**Location:** `lib/da_product_app/payment_gateway/pg_pos_transaction.ex`

Defines the `DaProductApp.PaymentGateway.PgPosTransaction` schema module with:
- All 45 fields mapped to appropriate Ecto types
- `changeset/2` for creating/updating transactions
- `update_status_changeset/2` for status-only updates
- Validation for closure_status field

### 3. Context Module
**Location:** `lib/da_product_app/payment_gateway/transactions.ex`

Provides the `DaProductApp.PaymentGateway.Transactions` context with functions:

- `create_pg_transaction/1` - Create new transaction
- `get_pg_transaction/1` - Get by ID
- `get_pg_transaction_by_order_number/1` - Get by order number
- `get_pg_transaction_by_reference/1` - Get by reference number
- `update_pg_transaction/2` - Update transaction
- `update_pg_transaction_status/2` - Update status only
- `list_pg_transactions/1` - List with filters
- `list_pg_transactions_paginated/1` - Paginated listing
- `get_pg_transactions_by_terminal/1` - Filter by terminal ID
- `get_pg_transactions_by_merchant/1` - Filter by merchant ID
- `get_pg_transactions_by_date_range/2` - Filter by date range
- `delete_pg_transaction/1` - Delete transaction

### 4. Transaction Helper
**Location:** `lib/da_product_app/payment_gateway/transaction_helper.ex`

Provides the `DaProductApp.PaymentGateway.TransactionHelper` module with:

- `create_from_payment_initiation/1` - Create transaction when payment is initiated
- `update_from_callback/2` - Update transaction from callback response
- `upsert_from_callback/1` - Create or update from callback

## Integration Points

### 1. Payment Initiation (Gateway LiveView)
**File:** `apps/payment_gateway_app/lib/payment_gateway_app_web/live/payment_live/gateway.ex`

When a card payment form is submitted:
```elixir
DaProductApp.PaymentGateway.TransactionHelper.create_from_payment_initiation(%{
  order_id: order_id,
  merchant_id: merchant_id,
  amount: amount,
  currency: currency
})
```

This creates a transaction record with:
- `closure_status: "PENDING"`
- `response_message: "Payment initiated"`
- Timestamp in `created_dateTime`

### 2. Payment Callback
**File:** `apps/payment_gateway_app/lib/payment_gateway_app_web/controllers/page_controller.ex`

When YSP or other provider sends callback:
```elixir
DaProductApp.PaymentGateway.TransactionHelper.upsert_from_callback(callback_params)
```

This:
- Finds existing transaction by `order_number`
- Updates with response data (approval code, reference numbers, etc.)
- Sets `closure_status` to "CLOSED" (success) or "FAILED" based on response code
- Updates `completed_dateTime`

### 3. Sandbox Mode
**File:** `apps/payment_gateway_app/lib/payment_gateway_app_web/live/payment_live/sandbox.ex`

For sandbox simulations:
- Success simulation: Creates transaction with `response_code: "00"` and `closure_status: "CLOSED"`
- Failure simulation: Creates transaction with `response_code: "05"` and `closure_status: "FAILED"`

## Transaction Lifecycle

### 1. Initiation
```
User submits payment form
  ↓
Gateway.handle_event("submit_card")
  ↓
TransactionHelper.create_from_payment_initiation()
  ↓
Transaction created with status: PENDING
```

### 2. Processing
```
Payment submitted to YSP/Provider
  ↓
(External processing)
```

### 3. Callback/Response
```
Provider sends callback
  ↓
PageController.callback()
  ↓
TransactionHelper.upsert_from_callback()
  ↓
Transaction updated with:
  - response_code
  - approval_code
  - reference numbers
  - status: CLOSED or FAILED
  - completed_dateTime
```

## Closure Status Values

- `OPEN` - Initial state (not yet used in payment gateway)
- `PENDING` - Payment initiated, awaiting response
- `CLOSED` - Payment successful (response code "00" or "000")
- `FAILED` - Payment declined or error
- `CANCELLED` - Payment cancelled

## Querying Transactions

### Get all payment gateway transactions
```elixir
DaProductApp.PaymentGateway.Transactions.list_pg_transactions()
```

### Get by order number
```elixir
DaProductApp.PaymentGateway.Transactions.get_pg_transaction_by_order_number("ORD-12345")
```

### Get by merchant
```elixir
DaProductApp.PaymentGateway.Transactions.get_pg_transactions_by_merchant("MERCHANT_ID")
```

### Get with pagination
```elixir
DaProductApp.PaymentGateway.Transactions.list_pg_transactions_paginated(%{
  page: 1,
  page_size: 20,
  s_mid: "MERCHANT_ID"
})
```

### Get by date range
```elixir
start_date = ~N[2026-05-01 00:00:00]
end_date = ~N[2026-05-31 23:59:59]
DaProductApp.PaymentGateway.Transactions.get_pg_transactions_by_date_range(start_date, end_date)
```

## Differences from pos_transaction

| Aspect | pos_transaction | pg_pos_transaction |
|--------|----------------|-------------------|
| **Source** | POS terminals | Payment Gateway (web/mobile) |
| **Repository** | DaProductApp.Repo | DaProductApp.Repo (same) |
| **Database** | shukria_transactions | shukria_transactions (same) |
| **Schema Module** | `DaProductApp.PosTransactions.PosTransaction` | `DaProductApp.PaymentGateway.PgPosTransaction` |
| **Context** | (Various) | `DaProductApp.PaymentGateway.Transactions` |
| **Indexes** | `idx_transaction_*` | `idx_pg_transaction_*` |
| **Primary Use** | Card-present transactions | Card-not-present (CNP) transactions |

## Testing the Implementation

### 1. Run Migration
```bash
cd /var/www/internaltesting/meghana/prverification/CloudLayer
INCLUDE_PAYMENT_GATEWAY=true mix ecto.migrate
```

### 2. Start Server with Payment Gateway
```bash
INCLUDE_PAYMENT_GATEWAY=true ENABLE_PAYMENT_GATEWAY=true mix phx.server
```

Or use the helper script:
```bash
./start_with_pg.sh
# Choose option 1 to enable Payment Gateway
```

### 3. Test Transaction Creation

Access the payment gateway sandbox:
```
http://localhost:4001/pgpayments/sandbox?token=<base64_token>
```

Submit a payment and check the database:
```sql
SELECT * FROM pg_pos_transaction ORDER BY created_dateTime DESC LIMIT 10;
```

### 4. Verify Transaction Storage

Check that transactions are being created:
```elixir
# In IEx console
iex> alias DaProductApp.PaymentGateway.Transactions
iex> Transactions.list_pg_transactions() |> length()
# Should show count of stored transactions
```

## Future Enhancements

1. **Refund Support**: Add fields for refund tracking
2. **Webhooks**: Implement async webhook handling for status updates
3. **Analytics**: Add reporting queries for transaction analytics
4. **Reconciliation**: Tools to match transactions with bank settlements
5. **Audit Trail**: Track all status changes with timestamps

## Troubleshooting

### Migration Issues
If migration fails:
```bash
mix ecto.rollback
mix ecto.migrate
```

### Transaction Not Created
Check logs for errors:
```bash
tail -f /path/to/logs/dev.log | grep "Payment gateway transaction"
```

### Schema Not Found
Ensure the payment gateway app is enabled:
```bash
export INCLUDE_PAYMENT_GATEWAY=true
mix compile --force
```

## Summary

This implementation provides a complete, production-ready solution for storing Payment Gateway transactions separately from POS transactions while maintaining schema compatibility and providing comprehensive query capabilities.