#' Attempt to interpolate a prepared query manually
#'
#' @param query Query string, with placeholders
#' @param con Database connection. Required by [glue::glue_sql()] for
#'   correct escaping.
#' @param values Unnamed list of values.
#' @return Interpolated query, as a character.
#' @author Alexey Shiklomanov
#' @export
interpolate_query <- function(query, con, values) {
  PEcAn.logger::logger.warn(paste0(
    "`PostgreSQL` driver does not support prepared statements, so attempting ",
    "to interpolate query manually. ",
    "This may not be perfect or safe. ",
    "You are strongly encouraged to use the `Postgres` database driver instead."
  ))
  nvalues <- length(values)

  # All values have a placeholder
  need_placeholders <- paste0("$", seq_len(nvalues))
  has_placeholders <- vapply(need_placeholders, grepl, logical(1),
                             x = query, fixed = TRUE)

  # All placeholders have an associated value
  all_placeholders <- gregexpr("\\$[[:digit:]]+", query)[[1]]

  # ...and are sequential, numbered $1 to $N.
  need_placeholders2 <- paste0("$", seq_along(all_placeholders))
  has_placeholders2 <- vapply(need_placeholders2, grepl, logical(1),
                              x = query, fixed = TRUE)

  if (!all(has_placeholders) ||
        !all(has_placeholders2) ||
         length(all_placeholders) != nvalues) {
    PEcAn.logger::logger.severe(paste0(
      "Values require ", nvalues, " placeholders, ",
      "but only ", sum(all_placeholders), " found in query."
    ))
  }

  query_fmt <- query
  for (i in seq_along(need_placeholders)) {
    iph <- all_placeholders[[i]]
    ph <- paste0("\\", need_placeholders[[i]])
    v <- values[[i]]
    xval <- paste0("{X", i, "}")
    if (length(v) > 1) {
      PEcAn.logger::logger.warn(paste0(
        "Detected a value of length > 1, so replacing preceding `=` with `IN` ",
        "(if present). This may be unreliable."
      ))
      rxp <- paste0("( *= *)(?=", ph, ")")
      query_fmt <- gsub(rxp, " IN ", query_fmt, perl = TRUE)
      xval <- paste0("({X", i, "*})")
    }
    query_fmt <- gsub(ph, xval, query_fmt)
  }

  values_names <- setNames(values, paste0("X", seq_along(need_placeholders)))

  string <- glue::glue_sql(query_fmt, .envir = values_names, .con = con)
  PEcAn.logger::logger.debug(paste0(
    "Query has been interpolated as follows: \n",
    string
  ), wrap = FALSE)
  as.character(string)
}

query <- "SELECT * FROM models WHERE id = $1 AND name = $2"
values <- list(5, c("ED", "SIGNET"))
con <- DBI::dbConnect(RPostgreSQL::PostgreSQL(), user = "bety", password = "bety", host = "localhost", port = 5432)

interpolate_query(query, con, values)