defmodule Ecto.Migrations.CreateBankConfirmationRecords do
  use Ecto.Migration

  def change do
    create table(:bank_confirmation_records) do
      add :batch_id, references(:bank_confirmation_batches, on_delete: :cascade),
          null: false, comment: "FK - parent batch"

      # CSV Data Fields
      add :merchant_mid, :string, size: 20, null: false, comment: "Merchant MID from CSV"
      add :settlement_date, :date, null: false, comment: "Settlement date from CSV"
      add :payout_amount, :decimal, precision: 18, scale: 2, comment: "Amount paid to merchant"
      add :bank_transfer_ref, :string, size: 100, comment: "Bank transfer reference/RRN"

      # Validation Status
      add :validation_status, :string, size: 20, default: "validated",
          comment: "validated | error"
      add :validation_errors, :text, comment: "JSON array of validation errors"

      # Processing Status
      add :processing_status, :string, size: 20, default: "pending",
          comment: "pending | committed | failed"
      add :processing_error, :text, comment: "Error if processing failed"

      # Audit Fields
      add :csv_row_number, :integer, comment: "Row number in uploaded CSV"

      timestamps(type: :naive_datetime)
    end

    create index(:bank_confirmation_records, [:batch_id])
    create index(:bank_confirmation_records, [:merchant_mid])
    create index(:bank_confirmation_records, [:settlement_date])
    create index(:bank_confirmation_records, [:validation_status])
  end
end