common_sql

import gleam/dynamic
import gleam/dynamic/decode
import gleam/list
import gleam/result
import gleam/string

/// A query parameter value to be passed to a driver.
pub type Param {
  PInt(Int)
  PString(String)
  PFloat(Float)
  PBool(Bool)
  PNull
}

/// Errors that can arise from database operations.
pub type DbError {
  QueryError(String)
  ConnectionError(String)
}

/// A SQL query to be executed.
///
/// - `Sql(String)` — driver-native SQL passed as-is. Use the placeholder syntax
///   expected by the target driver (e.g. `?` for SQLite, `$1` for PostgreSQL).
/// - `Portable(String)` — SQL written with PostgreSQL-style `$1, $2, …`
///   placeholders. Drivers that require a different syntax (e.g. SQLite) convert
///   the placeholders automatically, so the same string works across all drivers.
pub type Query {
  Sql(String)
  Portable(String)
}

/// A vtable record that abstracts over a specific database driver.
/// The `conn` type parameter lets each driver use its own opaque connection type.
pub type Driver(conn) {
  Driver(
    /// Identifies the underlying database engine, e.g. `"sqlite"` or `"postgresql"`.
    driver_type: String,
    connect: fn(String) -> Result(conn, DbError),
    execute: fn(conn, Query, List(Param)) ->
      Result(List(dynamic.Dynamic), DbError),
    close: fn(conn) -> Nil,
  )
}

/// Establish a connection using the given driver and connection URL.
pub fn connect(driver: Driver(conn), url: String) -> Result(conn, DbError) {
  driver.connect(url)
}

/// Execute a SQL query, decode each returned row with `decoder`, and collect
/// all rows into a `List(a)`. Decoding errors are surfaced as `QueryError`.
pub fn execute(
  driver: Driver(conn),
  conn: conn,
  query: Query,
  params: List(Param),
  decoder: decode.Decoder(a),
) -> Result(List(a), DbError) {
  use rows <- result.try(driver.execute(conn, query, params))
  rows
  |> list.map(fn(row) {
    decode.run(row, decoder)
    |> result.map_error(fn(errors) { QueryError(string.inspect(errors)) })
  })
  |> result.all
}

/// Close a connection using the given driver.
pub fn close(driver: Driver(conn), conn: conn) -> Nil {
  driver.close(conn)
}

/// Open a connection, run `f` with it, then close it — even if `f` returns
/// an error. This is the preferred way to use a connection as it guarantees
/// the connection is always closed.
///
/// ```gleam
/// use conn <- sql.with_connection(driver, "postgres://localhost/mydb")
/// sql.execute(driver, conn, "SELECT id FROM users", [], decode.int)
/// ```
pub fn with_connection(
  driver: Driver(conn),
  url: String,
  f: fn(conn) -> Result(a, DbError),
) -> Result(a, DbError) {
  use conn <- result.try(driver.connect(url))
  let result = f(conn)
  driver.close(conn)
  result
}