defmodule HiiveRisk.Repo.Migrations.CreateReviewPipeline do
  use Ecto.Migration

  def up do
    Oban.Migration.up(version: 12)

    create table(:risk_cases) do
      add(:source_event_id, :text, null: false)
      add(:policy_version, :text, null: false)
      add(:snapshot, :map, null: false)
      add(:signals, :map, null: false)
      add(:audit_sample, :boolean, null: false)
      add(:audit, :boolean, null: false, default: false)
      add(:status, :text, null: false, default: "pending_decision")
      add(:decision, :map)
      add(:verdict, :map)
      add(:disposition, :text)
      add(:risk_level, :integer)
      add(:reasons, {:array, :text}, null: false, default: [])
      add(:last_error, :text)
      add(:assessed_at, :utc_datetime_usec)
      timestamps(type: :utc_datetime_usec)
    end

    create(unique_index(:risk_cases, [:source_event_id, :policy_version]))
    create(index(:risk_cases, [:disposition, :risk_level, :inserted_at]))

    create(
      constraint(:risk_cases, :valid_status,
        check: "status IN ('pending_decision', 'pending_sft', 'completed', 'failed')"
      )
    )

    create(
      constraint(:risk_cases, :valid_risk_level,
        check: "risk_level IS NULL OR risk_level BETWEEN 1 AND 5"
      )
    )
  end

  def down do
    drop(table(:risk_cases))
    Oban.Migration.down(version: 1)
  end
end