defmodule Microwaveprop.Aprs do
  @moduledoc """
  Read-only access to aprs.me's `packets` table for 144 MHz calibration.

  This module never writes, never schemas, never mirrors. Each call
  issues a SELECT against `Microwaveprop.AprsRepo` (which connects to
  aprs.me's database) and returns raw maps for downstream parsing by
  `Microwaveprop.Aprs.PathParser` and consumption by the
  `Calibrate.Aprs144` mix task.

  aprs.me retains 24 h of packet history in production
  (`PACKET_RETENTION_DAYS=1`), so any "historical" query is bounded by
  that window.

  Tests run against a local `aprsme_test` database whose schema mirrors
  aprs.me's partitioned `packets` table; rows are inserted via raw SQL
  in a setup block under the `Ecto.Adapters.SQL.Sandbox`.

  > #### Error semantics {: .warning}
  >
  > Both queries call `Ecto.Adapters.SQL.query!/3` and will raise on
  > connection or SQL failures. Today the only caller is the
  > `Calibrate.Aprs144` mix task, where a hard exit is the right
  > ergonomic. Async callers (`Task.async_stream`, LiveView
  > `start_async`, etc.) MUST catch and log per the project's
  > swallowed-error rule in CLAUDE.md.
  """

  alias Ecto.Adapters.SQL
  alias Microwaveprop.AprsRepo

  @type packet_row :: %{
          id: binary(),
          sender: String.t(),
          base_callsign: String.t(),
          lat: float(),
          lon: float(),
          path: String.t(),
          received_at: DateTime.t()
        }

  @type position :: {lat :: float(), lon :: float(), last_heard_at :: DateTime.t()}

  @recent_packets_sql """
  SELECT id, sender, base_callsign, lat, lon, path, received_at
    FROM packets
    WHERE has_position = true
      AND lat IS NOT NULL
      AND lon IS NOT NULL
      AND path IS NOT NULL
      AND path <> ''
      AND is_item = false
      AND is_object = false
      AND sender IS NOT NULL
      AND received_at >= $1
    ORDER BY received_at ASC
    LIMIT $2
  """

  @station_positions_sql """
  SELECT DISTINCT ON (base_callsign) base_callsign, lat, lon, received_at
    FROM packets
    WHERE base_callsign = ANY($1)
      AND has_position = true
      AND lat IS NOT NULL
      AND lon IS NOT NULL
    ORDER BY base_callsign, received_at DESC
  """

  @doc """
  Returns recent position-bearing packets with non-empty paths, oldest first.

  Filters out item/object packets and rows missing position or path.
  Raises on connection or SQL failure — see moduledoc on async-context safety.

  ## Options

    * `:since` — `%DateTime{}`. Default `now - 1h`.
    * `:limit` — non-neg integer. Default `50_000`.
  """
  @spec recent_packets_with_paths(keyword()) :: [packet_row()]
  def recent_packets_with_paths(opts \\ []) do
    since = Keyword.get_lazy(opts, :since, fn -> DateTime.add(DateTime.utc_now(), -3600, :second) end)
    limit = Keyword.get(opts, :limit, 50_000)

    # Force the bound to UTC so a non-UTC %DateTime{} doesn't silently
    # match a wrong window after `to_naive` strips the tz tag.
    naive_since = since |> DateTime.shift_zone!("Etc/UTC") |> DateTime.to_naive()

    %Postgrex.Result{rows: rows} =
      SQL.query!(AprsRepo, @recent_packets_sql, [naive_since, limit])

    Enum.map(rows, &decode_packet_row/1)
  end

  @doc """
  Returns the most recent known position for each callsign in `callsigns`.

  Callsigns with no positioned packets simply don't appear in the result.
  Empty input list short-circuits to `%{}` without querying.

  Raises on connection or SQL failure — see moduledoc on async-context safety.
  """
  @spec station_positions([String.t()]) :: %{String.t() => position()}
  def station_positions([]), do: %{}

  def station_positions(callsigns) when is_list(callsigns) do
    %Postgrex.Result{rows: rows} = SQL.query!(AprsRepo, @station_positions_sql, [callsigns])

    Map.new(rows, fn [base_callsign, lat, lon, received_at] ->
      {base_callsign, {to_float(lat), to_float(lon), to_utc_datetime(received_at)}}
    end)
  end

  defp decode_packet_row([id, sender, base_callsign, lat, lon, path, received_at]) do
    %{
      id: id,
      sender: sender,
      base_callsign: base_callsign,
      lat: Decimal.to_float(lat),
      lon: Decimal.to_float(lon),
      path: path,
      received_at: DateTime.from_naive!(received_at, "Etc/UTC")
    }
  end

  defp to_float(%Decimal{} = d), do: Decimal.to_float(d)

  defp to_utc_datetime(%NaiveDateTime{} = naive), do: DateTime.from_naive!(naive, "Etc/UTC")
end