# Generic Gateway Integration Architecture

## Core Concept: Gateway Reference + Metadata Approach

Instead of adding gateway-specific fields, we use a clean reference system where:
1. **Gateway Reference ID** - Links to the external gateway transaction
2. **Gateway Type** - Identifies which gateway (MPGS, VISA, etc.)
3. **Metadata JSON** - Stores all gateway-specific data in a standardized structure

## Benefits

✅ **Future-Proof**: Works for any gateway (MPGS, VISA, Amex, etc.)
✅ **Clean Schema**: Existing tables stay focused and clean
✅ **No Breaking Changes**: Existing code continues to work
✅ **Flexible**: Each gateway can store unique fields as needed
✅ **Queryable**: Can still filter and report on gateway-specific data
✅ **Maintainable**: Single approach for all gateways

## Database Design

### Migration: Add Generic Gateway Fields

```elixir
defmodule DaProductApp.Repo.Migrations.AddGenericGatewayFields do
  use Ecto.Migration

  def change do
    # Add generic gateway fields to pos_temp_transaction
    alter table(:pos_temp_transaction) do
      add :gateway_type, :string, size: 20           # "MPGS", "VISA", "AMEX", etc.
      add :gateway_reference_id, :string, size: 100  # Gateway's transaction ID
      add :gateway_status, :string, size: 20         # "PENDING", "SUCCESS", "FAILED"
      add :processing_state, :string, size: 20       # "CREATED", "PROCESSING", "COMPLETED"
      add :retry_count, :integer, default: 0         # Retry attempts
    end

    # Add generic gateway fields to pos_transaction  
    alter table(:pos_transaction) do
      add :gateway_type, :string, size: 20           # "MPGS", "VISA", "AMEX", etc.
      add :gateway_reference_id, :string, size: 100  # Gateway's transaction ID
      add :gateway_status, :string, size: 20         # Final gateway status
      add :settlement_date, :date                    # Settlement date
      add :settlement_status, :string, size: 20      # "PENDING", "SETTLED"
    end

    # Add payment method reference (reusable across all gateways)
    alter table(:pos_temp_transaction) do
      add :payment_method_id, references(:payment_methods, on_delete: :restrict)
    end

    alter table(:pos_transaction) do
      add :payment_method_id, references(:payment_methods, on_delete: :restrict)
    end

    # Add indexes for performance
    create index(:pos_temp_transaction, [:gateway_type])
    create index(:pos_temp_transaction, [:gateway_reference_id])
    create index(:pos_temp_transaction, [:gateway_status])
    create index(:pos_transaction, [:gateway_type])
    create index(:pos_transaction, [:gateway_reference_id])
    create index(:pos_transaction, [:settlement_date])
    create index(:pos_transaction, [:settlement_status])
  end
end
```

## Standardized Metadata Structure

### MPGS Metadata Format
```elixir
%{
  "gateway" => %{
    "type" => "MPGS",
    "version" => "63",
    "environment" => "PRODUCTION", # or "SANDBOX"
    
    # Session Management
    "session" => %{
      "id" => "SESSION_0000123456789",
      "version" => "63",
      "created_at" => "2025-01-03T14:30:00Z",
      "expires_at" => "2025-01-03T15:30:00Z"
    },
    
    # Request/Response Data
    "request" => %{
      "api_operation" => "PAY",
      "order_id" => "ORDER_123456",
      "amount" => 100.00,
      "currency" => "USD"
    },
    
    "response" => %{
      "result" => "SUCCESS",
      "gateway_code" => "APPROVED",
      "authorization_code" => "123456",
      "transaction_id" => "TXN_789012345",
      "processor_response_code" => "00",
      "response_timestamp" => "2025-01-03T14:30:15Z"
    },
    
    # Authorization Lifecycle (for auth/capture flows)
    "authorization" => %{
      "code" => "123456",
      "amount" => 100.00,
      "expires_at" => "2025-01-10T14:30:00Z",
      "captured_amount" => 0.00,
      "remaining_amount" => 100.00,
      "captures" => [
        # Array of capture records when captures happen
      ]
    },
    
    # Payment Method Details
    "payment_method" => %{
      "type" => "CARD",
      "brand" => "VISA",
      "funding" => "CREDIT",
      "verification" => %{
        "cvv_result" => "MATCHED",
        "avs_result" => "VERIFIED"
      }
    },
    
    # Risk and Fraud
    "risk" => %{
      "score" => 0.25,
      "decision" => "ACCEPT",
      "rules_triggered" => []
    },
    
    # Settlement Information
    "settlement" => %{
      "batch_id" => "BATCH_20250103",
      "expected_date" => "2025-01-05",
      "currency" => "USD",
      "interchange_rate" => 1.65
    },
    
    # Debug and Audit
    "debug" => %{
      "trace_id" => "TRACE_123456789",
      "processing_time_ms" => 1250,
      "retry_count" => 0,
      "errors" => []
    }
  }
}
```

### Future Gateway Metadata (VISA Direct example)
```elixir
%{
  "gateway" => %{
    "type" => "VISA_DIRECT",
    "version" => "1.0",
    
    "request" => %{
      "system_trace_audit_number" => "123456",
      "transaction_identifier" => "381228649430015",
      "originator" => %{
        "card_acceptor" => %{
          "id_code" => "ABCD1234ABCD123",
          "name" => "Merchant Name"
        }
      }
    },
    
    "response" => %{
      "action_code" => "00",
      "approval_code" => "20304B",
      "transaction_identifier" => "381228649430015"
    }
    
    # ... VISA-specific fields
  }
}
```

## Code Implementation

### Enhanced Schema (PosTransaction)
```elixir
defmodule DaProductApp.Acquirer.Schemas.PosTransaction do
  # ... existing fields ...
  
  # Generic Gateway Fields
  field :gateway_type, :string              # "MPGS", "VISA", "AMEX"
  field :gateway_reference_id, :string      # Gateway's transaction ID
  field :gateway_status, :string            # Gateway result status
  field :settlement_date, :date            # Settlement date
  field :settlement_status, :string        # Settlement status
  
  # Payment Method Reference
  belongs_to :payment_method, DaProductApp.Acquirer.Schemas.PaymentMethod
  
  # ... existing metadata field for gateway-specific data ...
  
  # Helper functions for gateway metadata
  def get_gateway_metadata(transaction, key_path) do
    get_in(transaction.metadata, ["gateway"] ++ key_path)
  end
  
  def put_gateway_metadata(transaction, key_path, value) do
    metadata = transaction.metadata || %{}
    updated_metadata = put_in(metadata, ["gateway"] ++ key_path, value)
    %{transaction | metadata: updated_metadata}
  end
  
  def mpgs_session_id(transaction) do
    get_gateway_metadata(transaction, ["session", "id"])
  end
  
  def gateway_authorization_code(transaction) do
    get_gateway_metadata(transaction, ["response", "authorization_code"])
  end
end
```

### Generic Transaction Processor
```elixir
defmodule DaProductApp.Acquirer.GenericProcessor do
  @moduledoc """
  Generic transaction processor that delegates to gateway-specific processors
  while maintaining a consistent interface and data structure.
  """
  
  def process_transaction(gateway_type, config, internal_msg, opts \\ []) do
    with {:ok, processor} <- get_gateway_processor(gateway_type),
         {:ok, gateway_response} <- processor.process_transaction(config, internal_msg, opts),
         {:ok, transaction} <- store_transaction_with_metadata(gateway_type, gateway_response, internal_msg) do
      {:ok, transaction}
    end
  end
  
  defp get_gateway_processor("MPGS"), do: {:ok, DaProductApp.Acquirer.Mastercard.MpgsProcessor}
  defp get_gateway_processor("VISA"), do: {:ok, DaProductApp.Acquirer.Visa.VisaProcessor}
  defp get_gateway_processor(type), do: {:error, {:unsupported_gateway, type}}
  
  defp store_transaction_with_metadata(gateway_type, gateway_response, internal_msg) do
    transaction_attrs = %{
      # Standard fields from internal_msg
      s_tid: internal_msg[:field_41],
      mti: internal_msg[:mti],
      # ... other standard fields ...
      
      # Generic gateway fields
      gateway_type: gateway_type,
      gateway_reference_id: gateway_response.gateway_transaction_id,
      gateway_status: map_gateway_status(gateway_response.result),
      
      # Gateway-specific data in metadata
      metadata: build_gateway_metadata(gateway_type, gateway_response)
    }
    
    DaProductApp.Transactions.create_transaction(transaction_attrs)
  end
end
```

## Query Examples

### Gateway-Specific Queries
```elixir
# All MPGS transactions today
DaProductApp.Transactions.list_transactions_by_gateway("MPGS", Date.utc_today())

# Failed transactions by gateway for retry
DaProductApp.Transactions.list_failed_transactions_by_gateway("MPGS")

# MPGS transactions with pending settlements
from(t in PosTransaction,
  where: t.gateway_type == "MPGS" and t.settlement_status == "PENDING",
  where: fragment("JSON_EXTRACT(metadata, '$.gateway.session.id') IS NOT NULL")
)

# Authorization amounts from metadata
from(t in PosTransaction,
  where: t.gateway_type == "MPGS",
  select: %{
    id: t.id,
    auth_amount: fragment("JSON_EXTRACT(metadata, '$.gateway.authorization.amount')"),
    captured_amount: fragment("JSON_EXTRACT(metadata, '$.gateway.authorization.captured_amount')")
  }
)
```

## Settlement and Reporting Benefits

### Cross-Gateway Reporting
```elixir
# Transaction volume by gateway
def gateway_volume_report(start_date, end_date) do
  from(t in PosTransaction,
    where: t.inserted_at >= ^start_date and t.inserted_at <= ^end_date,
    group_by: t.gateway_type,
    select: %{
      gateway: t.gateway_type,
      transaction_count: count(t.id),
      total_amount: sum(t.total_amount),
      success_rate: fragment("AVG(CASE WHEN gateway_status = 'SUCCESS' THEN 1.0 ELSE 0.0 END)")
    }
  )
end

# Settlement reconciliation across gateways
def settlement_reconciliation(settlement_date) do
  from(t in PosTransaction,
    where: t.settlement_date == ^settlement_date,
    group_by: [t.gateway_type, t.settlement_status],
    select: %{
      gateway: t.gateway_type,
      status: t.settlement_status,
      count: count(t.id),
      amount: sum(t.total_amount)
    }
  )
end
```

This approach gives you:
✅ **Complete flexibility** for any gateway
✅ **Clean, maintainable schemas**
✅ **Rich reporting capabilities**
✅ **Future-proof architecture**
✅ **Easy settlement reconciliation**