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

  @moduledoc """
  Integrity verification audit log for every EOD batch sync trigger.

  When POS or QR calls the sync API, it sends expected counts/amounts in the
  verification block. After the INSERT INTO...SELECT completes, the engine
  compares actual inserted values against expected values and records the
  outcome here.

  A 'mismatch' status immediately triggers an exception alert to Finance
  and Tech teams. All subsequent processing for that batch is blocked until
  the mismatch is resolved.
  """

  def change do
    create table(:sync_log) do
      add :source, :string, size: 10, null: false,
          comment: "POS | QR"
      add :settlement_batch_id, :string, size: 50, null: false
      add :sync_date, :date, null: false
      add :triggered_at, :naive_datetime, null: false

      # Actual values (what the engine inserted)
      add :inserted_count, :integer, default: 0
      add :inserted_amount, :decimal, precision: 14, scale: 2

      # Expected values (from the API verification block)
      add :expected_count_settled, :integer
      add :expected_amount_settled, :decimal, precision: 14, scale: 2
      add :expected_count_failed, :integer
      add :expected_amount_failed, :decimal, precision: 14, scale: 2
      add :expected_count_reversal, :integer
      add :expected_amount_reversal, :decimal, precision: 14, scale: 2

      # Result
      add :status, :string, size: 10, null: false, default: "pending",
          comment: "pending | ok | mismatch | failed"
      add :mismatch_detail, :text,
          comment: "JSON describing exactly which counts/amounts differ"
      add :resolved_at, :naive_datetime
      add :resolved_by, :bigint, comment: "users.id"
      add :resolution_notes, :text

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

    create index(:sync_log, [:sync_date])
    create index(:sync_log, [:source, :sync_date])
    create index(:sync_log, [:status])
    create unique_index(:sync_log, [:source, :settlement_batch_id],
      name: :sync_log_batch_unique,
      comment: "Each batch can only be synced once")
  end
end