Day 10

# Configure postgrex to use the build-in JSON library before it's compiled
Application.put_env(:postgrex, :json_library, JSON)

Mix.install([
  {:ecto_sql, "~> 3.10"},
  {:postgrex, ">= 0.0.0"},
  {:kino, "~> 0.18.0"}
])

Kino.configure(inspect: [charlists: :as_lists])

Application.put_env(:myapp, Repo,
  url: "ecto://postgres:postgres@localhost/advent_of_sql_2025"
)

defmodule Repo do
  use Ecto.Repo, adapter: Ecto.Adapters.Postgres, otp_app: :myapp
end

{:ok, _pid} = Repo.start_link()

Setup

import Ecto.Query

defmodule Delivery do
  use Ecto.Schema

  schema "deliveries" do
    field :child_name, :string
    field :delivery_location, :string
    field :gift_name, :string
    field :scheduled_at, :naive_datetime
  end
end

defmodule MisdeliveredPresent do
  use Ecto.Schema

  schema "misdelivered_presents" do
    field :child_name, :string
    field :delivery_location, :string
    field :gift_name, :string
    field :scheduled_at, :naive_datetime
    field :flagged_at, :naive_datetime
    field :reason, :string
  end
end

Query

from(d in Delivery)
|> Repo.aggregate(:count)

from(m in MisdeliveredPresent)
|> Repo.aggregate(:count)

# from(d in Delivery)
# from(m in MisdeliveredPresent)
# |> Repo.all()

invalid =
  from(
    d in Delivery,
    where:
      d.delivery_location in [
        "Volcano Rim",
        "Drifting Igloo",
        "Abandoned Lighthouse",
        "The Vibes"
      ],
    select: d
  )

misdelivered = from(d in subquery(invalid),
  select: %{
    id: d.id,
    child_name: d.child_name,
    delivery_location: d.delivery_location,
    gift_name: d.gift_name,
    scheduled_at: d.scheduled_at,
    flagged_at: fragment("NOW()"),
    reason: "Invalid delivery location"
  }
)

# I don't see any way to do this in one query without using raw sql
{num_inserted, inserted} = Repo.insert_all(MisdeliveredPresent, misdelivered, returning: true)
{num_deleted, _deleted} = Repo.delete_all(invalid)

IO.puts("Inserted #{num_inserted}, deleted: #{num_deleted}")

inserted
|> Kino.DataTable.new()