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"
      # Note: retry_count already exists, so we don't add it again
    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

    # Note: Removing payment_method_id references for now until payment_methods table is created    # 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_temp_transaction, [:processing_state])
    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])

    # Add composite indexes for common queries
    create index(:pos_temp_transaction, [:gateway_type, :gateway_status])
    create index(:pos_temp_transaction, [:gateway_type, :processing_state])
    create index(:pos_transaction, [:gateway_type, :settlement_status])
    create index(:pos_transaction, [:settlement_date, :settlement_status])
  end

  def down do
    # Remove indexes
    drop index(:pos_temp_transaction, [:gateway_type])
    drop index(:pos_temp_transaction, [:gateway_reference_id])
    drop index(:pos_temp_transaction, [:gateway_status])
    drop index(:pos_temp_transaction, [:processing_state])
    drop index(:pos_transaction, [:gateway_type])
    drop index(:pos_transaction, [:gateway_reference_id])
    drop index(:pos_transaction, [:settlement_date])
    drop index(:pos_transaction, [:settlement_status])
    drop index(:pos_temp_transaction, [:gateway_type, :gateway_status])
    drop index(:pos_temp_transaction, [:gateway_type, :processing_state])
    drop index(:pos_transaction, [:gateway_type, :settlement_status])
    drop index(:pos_transaction, [:settlement_date, :settlement_status])

    # Remove fields
    alter table(:pos_temp_transaction) do
      remove :gateway_type
      remove :gateway_reference_id
      remove :gateway_status
      remove :processing_state
      # Note: retry_count already existed, so we don't remove it
      # Note: payment_method_id not added in this migration
    end

    alter table(:pos_transaction) do
      remove :gateway_type
      remove :gateway_reference_id
      remove :gateway_status
      remove :settlement_date
      remove :settlement_status
      # Note: payment_method_id not added in this migration
    end
  end
end