import common_sql
import gleam/dynamic/decode
import gleam/int
import gleam/list
import gleam/option
import gleam/regexp
import gleam/result
import sqlight

fn param_to_value(param: common_sql.Param) -> sqlight.Value {
  case param {
    common_sql.PInt(n) -> sqlight.int(n)
    common_sql.PString(s) -> sqlight.text(s)
    common_sql.PFloat(f) -> sqlight.float(f)
    common_sql.PBool(b) -> sqlight.bool(b)
    common_sql.PNull -> sqlight.null()
  }
}

fn sqlight_error_to_connect_error(err: sqlight.Error) -> common_sql.DbError {
  let sqlight.SqlightError(_, message, _) = err
  common_sql.ConnectionError(message)
}

fn sqlight_error_to_query_error(err: sqlight.Error) -> common_sql.DbError {
  let sqlight.SqlightError(_, message, _) = err
  common_sql.QueryError(message)
}

/// Converts a Portable query (PostgreSQL-style `$N` placeholders) to SQLite
/// `?` placeholders, and reorders/repeats `values` to match the order the
/// `$N` tokens appear in the SQL string.
///
/// Handles out-of-order placeholders (`WHERE a = $2 AND b = $1`) and
/// repeated placeholders (`WHERE a = $1 OR b = $1`).
///
/// Known limitation: `$N` inside SQL string literals is not distinguished
/// from a real placeholder (a full SQL parser would be required for that).
@internal
pub fn convert_portable(
  sql: String,
  values: List(sqlight.Value),
) -> #(String, List(sqlight.Value)) {
  let assert Ok(re) = regexp.from_string("\\$([0-9]+)")
  let reordered =
    list.map(regexp.scan(re, sql), fn(m) {
      case m.submatches {
        [option.Some(n_str), ..] ->
          case int.parse(n_str) {
            Ok(n) -> list.drop(values, n - 1) |> list.first
            Error(Nil) -> Error(Nil)
          }
        _ -> Error(Nil)
      }
    })
    |> result.values
  #(regexp.replace(each: re, in: sql, with: "?"), reordered)
}

/// Returns a `common_sql.Driver` backed by SQLite via the `sqlight` library.
pub fn driver() -> common_sql.Driver(sqlight.Connection) {
  common_sql.Driver(
    driver_type: "sqlite",
    connect: fn(url) {
      sqlight.open(url)
      |> result.map_error(sqlight_error_to_connect_error)
    },
    execute: fn(conn, query, params) {
      let values = list.map(params, param_to_value)
      let #(sql, ordered_values) = case query {
        common_sql.Sql(s) -> #(s, values)
        common_sql.Portable(s) -> convert_portable(s, values)
      }
      sqlight.query(
        sql,
        on: conn,
        with: ordered_values,
        expecting: decode.dynamic,
      )
      |> result.map_error(sqlight_error_to_query_error)
    },
    close: fn(conn) {
      let _ = sqlight.close(conn)
      Nil
    },
  )
}