defmodule Ecto.Migrations.CreateBankConfirmationBatches do
  use Ecto.Migration

  def change do
    create table(:bank_confirmation_batches) do
      add :filename, :string, null: false, comment: "Uploaded CSV filename"
      add :uploaded_at, :naive_datetime, comment: "When batch was uploaded"
      add :total_records, :integer, default: 0, comment: "Total CSV rows"
      add :successful_records, :integer, default: 0, comment: "Successfully validated rows"
      add :failed_records, :integer, default: 0, comment: "Validation failed rows"

      # 2-Level Approval Fields
      add :approval_status, :string, size: 20, default: "pending",
          comment: "pending | approved | rejected"
      add :approved_at, :naive_datetime, comment: "When L2 approved"
      add :rejected_at, :naive_datetime, comment: "When L2 rejected"
      add :rejection_reason, :text, comment: "Reason if rejected"

      # Processing Fields
      add :committed_at, :naive_datetime, comment: "When batch was committed to DB"
      add :commit_status, :string, size: 20,
          comment: "pending | success | partial_success | failed"
      add :commit_error, :text, comment: "Error message if commit failed"

      # Foreign Keys
      add :user_id, references(:users, on_delete: :restrict),
          comment: "FK - L1 who uploaded batch"
      add :approved_by_user_id, references(:users, on_delete: :restrict),
          comment: "FK - L2 who approved/rejected batch"

      timestamps(type: :naive_datetime)
    end

    create index(:bank_confirmation_batches, [:approval_status])
    create index(:bank_confirmation_batches, [:user_id])
    create index(:bank_confirmation_batches, [:approved_by_user_id])
  end
end