defmodule DaProductApp.Repo.Migrations.CreateReconciliationExceptions do
  use Ecto.Migration

  @moduledoc """
  All four exception categories defined in the settlement spec (Section A, point 5):

    Type 5.1 — In dump, NOT in core_transactions (unmatched dump record)
    Type 5.2 — In core_transactions, NOT in dump after 30-day window
               (switch_settled_date still NULL, excluding today + Void txns)
    Type 5.3 — Void transaction found with an RRN match in the dump
    Type 5.4 — Duplicate: RRN already settled in a prior dump, appearing again

  All exception transactions are automatically set to settlement_status = 'exception_hold'
  in core_transactions. Finance Team reviews and releases via the UI.
  Released transactions are added to the next day's Settlement MIS.
  """

  def change do
    create table(:reconciliation_exceptions) do
      add :exception_type, :string, size: 5, null: false,
          comment: "5.1 | 5.2 | 5.3 | 5.4"
      add :exception_date, :date, null: false,
          comment: "Date of the reconciliation run that generated this exception"
      add :dump_source, :string, size: 15,
          comment: "SWITCH | QR_SCHEME — which dump file the record came from"
      add :dump_file_id, :bigint,
          comment: "FK dump_files.id"

      # May be NULL for type 5.1 (no matching core_transaction found)
      add :core_transaction_id, :bigint,
          comment: "FK core_transactions.id — NULL for type 5.1"

      # Dump record reference (NULL for type 5.2 — no dump record exists)
      add :switch_dump_record_id, :bigint,
          comment: "FK switch_dump_records.id — NULL for QR or 5.2"
      add :qr_dump_record_id, :bigint,
          comment: "FK qr_scheme_dump_records.id — NULL for SWITCH or 5.2"

      # Key fields for display (denormalized to avoid JOIN in exception reports)
      add :rrn, :string, size: 50
      add :tid, :string, size: 20
      add :merchant_mid, :string, size: 25
      add :transaction_amount, :decimal, precision: 12, scale: 2
      add :transaction_date, :date
      add :transaction_type, :string, size: 20
      add :description, :text,
          comment: "Human-readable explanation of the exception"

      # Hold and release workflow
      add :status, :string, size: 20, null: false, default: "hold",
          comment: "hold | reviewed | released | discarded"
      add :released_by, :bigint,
          comment: "users.id of Finance team member who released"
      add :released_at, :naive_datetime
      add :release_notes, :text

      # Email notification tracking
      add :notified_at, :naive_datetime,
          comment: "When the exception alert email was sent"

      add :inserted_at, :naive_datetime, null: false
      add :updated_at, :naive_datetime, null: false
    end

    create index(:reconciliation_exceptions, [:exception_date])
    create index(:reconciliation_exceptions, [:exception_type])
    create index(:reconciliation_exceptions, [:status])
    create index(:reconciliation_exceptions, [:core_transaction_id])
    create index(:reconciliation_exceptions, [:merchant_mid])
    create index(:reconciliation_exceptions, [:rrn, :exception_date])
  end
end