depo

defmodule Depo.DB do
  @moduledoc """
  A DB is a GenServer that manages an open database connection
  and a cache of prepared SQL statements. 
  """
  use GenServer

  def init(:memory) do
    {:ok, db} = :esqlite3.open(':memory:')
    {:ok, %{
      # An open connection to the database.
      db: db,
      # An atom-keyed cache of named prepared SQL statements.
      cache: %{}
    }}
  end
  def init(create: path) when is_binary(path) do
    if File.exists?(path) do
      {:stop, :file_already_exists}
    else
      {:ok, db} = path
      |> to_charlist() 
      |> :esqlite3.open()
      {:ok, %{
        # An open connection to the database.
        db: db,
      }}
    end
  end
  def init(path) when is_binary(path) do
    if File.exists?(path) do
      {:ok, db} = path 
      |> to_charlist() 
      |> :esqlite3.open()
      {:ok, %{
        # An open connection to the database.
        db: db,
      }}
    else
      {:stop, :no_such_file}
    end
  end

  def terminate(_reason, %{db: db}) do
    :ok = :esqlite3.close(db)
  end

  def handle_cast({:write, sql}, state)
    when is_binary(sql) do
    :esqlite3.exec(to_charlist(sql), state.db)
    {:noreply, state}
  end

  def handle_cast({:write, cmd}, state)
    when is_atom(cmd) do
    if Map.has_key?(state.cache, cmd) do
      :"$done" = :esqlite3.step(state.cache[cmd])
    end
    {:noreply, state}
  end

  def handle_cast({:write, sql, values}, state)
    when is_binary(sql) do
    {:ok, stmt} = :esqlite3.prepare(to_charlist(sql), state.db)
    :ok = :esqlite3.bind(stmt, values)
    :"$done" = :esqlite3.step(stmt)
    {:noreply, state}
  end

  def handle_cast({:write, cmd, values}, state)
    when is_atom(cmd) do
    if Map.has_key?(state.cache, cmd) do
      stmt = state.cache[cmd]
      :ok = :esqlite3.bind(stmt, values)
      :"$done" = :esqlite3.step(stmt)
    end
    {:noreply, state}
  end

  def handle_cast({:teach, cmds}, state) 
    when is_map(cmds) do
    # Prepare the statements and add them to the cache.
    state = Map.update!(state, :cache, fn cache ->
      Enum.reduce(cmds, cache, fn {name, sql}, cache -> 
        true = is_atom(name) && is_binary(sql)
        {:ok, stmt} = :esqlite3.prepare(to_charlist(sql), state.db)
        Map.put(cache, name, stmt)
      end) |> Enum.into(%{})
    end)
    {:noreply, state}
  end

  def handle_call({:read, sql}, _from, state)
    when is_binary(sql) do
    case :esqlite3.prepare(to_char_list(sql), state.db) do
      {:error, reason} ->
        {:reply, {:error, reason}, state}
      {:ok, stmt} ->  
        {:reply, get_values(stmt), state}
    end
  end

  def handle_call({:read, cmd}, _from, state)
    when is_atom(cmd) do
    if Map.has_key?(state.cache, cmd) do
      {:reply, get_values(state.cache[cmd]), state}
    else
      {:reply, {:error, :unknown_command}, state}
    end
  end

  def handle_call({:stream, to_pid, cmd}, _from, state)
    when is_binary(cmd) do
    case :esqlite3.prepare(to_char_list(cmd), state.db) do
      {:error, _} ->
        {:noreply, state}
      {:ok, stmt} ->
        # Stream the results from an isolated process.
        stream_id = spawn_link(fn ->
          results_stream(stmt)
          |> Enum.each(fn result ->
            send(to_pid, {self(), result})
          end)
        end)
        {:reply, stream_id, state}
    end    
  end
  # def handle_call({:stream, to_pid, cmd}, _from, state)
  #   when is_atom(cmd) do
  # end

  # Get a stream of results from a compiled SQL statement. 
  defp results_stream(stmt) do
    keys = :esqlite3.column_names(stmt)
    Stream.unfold(stmt, fn stmt ->
      case :esqlite3.step(stmt) do
        {:row, values} ->
          # Convert the row values into a map and return it.
          columns = 0..(tuple_size(keys)-1)
          result = Enum.reduce(columns, %{}, fn(i, m) ->
            Map.put(m, elem(keys, i), elem(values, i))
          end)
          {result, stmt}
        _ -> nil
      end
    end)
  end

  defp get_values(stmt) do
    keys = :esqlite3.column_names(stmt)
    case all_rows(stmt) do
      {:error, reason} -> 
        {:error, reason}
      {:ok, rows} ->
        # Convert the rows into maps with column names as keys.
        Enum.map(rows, fn row ->
          Enum.reduce(0..(tuple_size(keys)-1), %{}, fn(i, m) ->
            Map.put(m, elem(keys, i), elem(row, i))
          end)
        end)
    end
  end

  defp all_rows(stmt) do
    all_rows(stmt, [])
  end  
  defp all_rows(stmt, rows) do
    case :esqlite3.step(stmt) do
      {:row, values} ->
        all_rows(stmt, [values | rows])
      :"$done" -> {:ok, rows}
      :"$busy" -> {:error, :busy}
    end    
  end
end