# Alternative Phase 4 Design: Extending Existing Tables

## Migration to Add MPGS-Specific Fields

Instead of creating new tables, we can extend existing tables with minimal MPGS-specific fields:

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

  def change do
    # Add MPGS-specific fields to pos_temp_transaction
    alter table(:pos_temp_transaction) do
      add :gateway_session_id, :string, size: 100     # MPGS session ID
      add :gateway_transaction_id, :string, size: 50  # MPGS transaction ID
      add :gateway_order_id, :string, size: 50        # MPGS order ID
      add :processing_state, :string, size: 20        # PENDING/PROCESSING/COMPLETED/FAILED
      add :retry_count, :integer, default: 0          # Retry attempts
    end

    # Add MPGS-specific fields to pos_transaction
    alter table(:pos_transaction) do
      add :gateway_session_id, :string, size: 100     # MPGS session ID
      add :gateway_transaction_id, :string, size: 50  # MPGS transaction ID
      add :gateway_order_id, :string, size: 50        # MPGS order ID
      add :gateway_result, :string, size: 20          # SUCCESS/FAILURE/ERROR/PENDING
      add :authorized_amount, :decimal, precision: 12, scale: 2  # Amount authorized
      add :captured_amount, :decimal, precision: 12, scale: 2   # Amount captured
      add :settlement_date, :date                      # Settlement date
      add :authorization_expires_at, :utc_datetime    # Authorization expiry
    end

    # Create separate payment methods table (this one is truly separate concern)
    create table(:payment_methods, primary_key: true) do
      add :type, :string, size: 20, null: false        # CARD/DIGITAL_WALLET
      add :card_brand, :string, size: 20               # VISA/MASTERCARD
      add :last_four_digits, :string, size: 4          # Last 4 digits
      add :masked_card_number, :string, size: 25       # Masked display
      add :pci_token, :string, size: 100               # PCI token
      add :gateway_token, :string, size: 100           # Gateway token
      add :expiry_month, :string, size: 2              # MM
      add :expiry_year, :string, size: 4               # YYYY
      add :status, :string, size: 20, default: "ACTIVE"
      add :customer_id, :string, size: 50
      add :merchant_id, :string, size: 20
      add :usage_count, :integer, default: 0
      add :metadata, :text                             # Additional data JSON
      timestamps()
    end

    # Add payment method reference to transactions
    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
    create index(:pos_temp_transaction, [:gateway_session_id])
    create index(:pos_temp_transaction, [:gateway_transaction_id])
    create index(:pos_transaction, [:gateway_transaction_id])
    create index(:pos_transaction, [:settlement_date])
    create unique_index(:payment_methods, [:pci_token])
    create index(:payment_methods, [:customer_id])
  end
end
```

## Using Metadata Field for MPGS-Specific Data

Store MPGS-specific data in the existing `metadata` JSON field:

```elixir
# In metadata field:
%{
  "mpgs" => %{
    "session_version" => "63",
    "api_operation" => "PAY",
    "gateway_response" => %{...},  # Full MPGS response
    "request_payload" => %{...},   # Original request
    "risk_score" => 0.25,
    "avs_result" => "VERIFIED",
    "cvv_result" => "MATCHED"
  }
}
```

## Authorization Handling

For authorization/capture lifecycle, we can:

1. **Use existing amount fields**: `auth_amount` vs `total_amount`
2. **Track in metadata**: Store capture history in JSON
3. **Use pos_reversal**: For voids and partial refunds

Example authorization tracking in metadata:
```elixir
%{
  "mpgs" => %{
    "authorization" => %{
      "code" => "123456",
      "expires_at" => "2025-01-10T10:00:00Z",
      "captures" => [
        %{"amount" => 50.00, "captured_at" => "2025-01-03T14:30:00Z"},
        %{"amount" => 30.00, "captured_at" => "2025-01-04T09:15:00Z"}
      ],
      "remaining_amount" => 20.00
    }
  }
}
```