# SF_Utils_Setters_and_Getters.r


##  --------------------------------------------------------------------- ## 
##         WHICH DATABASE or dbname?                                      ##
##  --------------------------------------------------------------------- ##
##                                                                        ##
##   NOTE THESE FOUR FUNCTIONS                                            ##
##   set & get refer to R Options                                         ##
##   Show and Use refer to Snowflake Settings                             ##
##                                                                        ##
##     getSnowflakeDB                                                     ##
##     setSnowflakeDB                                                     ##
##                                                                        ##
##     .sf_showDB                                                         ##
##     .sf_useDB                                                          ##
##                                                                        ##
##   setSnowflakeDB() calls .sf_useDB                                     ##
##   getSnowflakeDB(from_system = TRUE) calls .sf_showDB                  ##
##                                                                        ##
##  --------------------------------------------------------------------- ## 


if (FALSE) 
{
  .sf_show_current("wh")     %>% print
  .sf_show_current("dbname") %>% print
  .sf_show_current("schema") %>% print
}
if (FALSE) 
{
  refresh <- TRUE
  getSnowflakeWH(refresh=refresh) %>% print
  getSnowflakeDB(refresh=refresh) %>% print
  getSnowflakeSchema(refresh=refresh) %>% print
}

.is_RODBC_loaded <- function() {
  "package:RODBC" %in% search()
}

.is_POSTGRES_loaded <- function() {
  "package:RPostgreSQL" %in% search()
}

.sf_use <- function(wh=NULL, dbname=NULL, schema=NULL, warnOfUse=TRUE, connex=sfGetCon(set_defaults=FALSE), verbose="Un-Used") {
  if (!.is_RODBC_loaded())
    .DBlib("Snowflake")
  
  if (warnOfUse)
    warning ("Do NOT call .sf_use() directly.   call setSnowflake() instead, which sets R options as well")

  if (!is.null(wh))
    sprintf("USE WAREHOUSE %s", wh) %>% sqlQuery(channel=connex, .)
  if (!is.null(dbname))
    sprintf("USE DATABASE %s", dbname) %>% sqlQuery(channel=connex, .)
  if (!is.null(schema))
    sprintf("USE SCHEMA %s", schema) %>% sqlQuery(channel=connex, .)

  return(TRUE)
}

.sf_show_current <- function(object=c("wh", "dbname", "schema"), connex=sfGetCon(set_defaults=FALSE), verbose="Un-Used") {
  if (!.is_RODBC_loaded())
    .DBlib("Snowflake")
  
  force(connex)

  object <- match.arg(object, several.ok=TRUE) %>% selfname_

  sapply(object, function(obj) {
    object_name <- obj %>% {ifelse(. == "wh", "warehouse", ifelse(. == "dbname", "database", .))} %>% toupper
    sprintf("SELECT CURRENT_%s()", object_name) %>% sqlQuery(channel=connex, ., stringsAsFactors=FALSE) %>% unlist(use.names=FALSE)
  })
}

.sf_check_if_current <- function(object=c("wh", "dbname", "schema"), name, connex=sfGetCon(set_defaults=FALSE), verbose="Un-Used") {
  
  if (is.list(name))
    name <- unlist(name, use.names=FALSE)

  if (!is.character(name))
    stop ("name must be a string or list of strings")
  name <- toupper(name)

  if (!.is_RODBC_loaded())
    .DBlib("Snowflake")
  
  force(connex)

  object <- match.arg(object, several.ok=TRUE) %>% selfname_

  if (!(length(name) %in% c(1, length(object))))
    stop ("name must either have length 1 or the same length as object")

  mapply(object, name, FUN=function(obj, n) {
    object_name <- obj %>% {ifelse(. == "wh", "warehouse", ifelse(. == "dbname", "database", .))} %>% toupper
    sprintf("SELECT CURRENT_%s()", object_name) %>% sqlQuery(channel=connex, ., stringsAsFactors=FALSE) %>% unlist(use.names=FALSE) %>% {. == n}
  })
}

setSnowflakeToProduction <- function(wh="SCIENCE", start=FALSE, connex=sfGetCon(), verbose=TRUE) {
   setSnowflake(wh=wh, dbname="prod", schema="production", start=start, connex=connex, verbose=verbose)
}

setSnowflake <- function(wh=NULL, dbname=NULL, schema=NULL, start=FALSE, confirm=TRUE, showWarnings=!start, connex=sfGetCon(set_defaults=FALSE), verbose=TRUE) {
## NOTE:  R options are also set in getSnowflake in the event of a refresh.

  force(wh); force(dbname); force(schema); force(start);
  
  if (!.is_RODBC_loaded())
    .DBlib("snowflake")

  force(connex)
  
  
  options("snowflake_inuse" = TRUE)

  objects <- list(wh=wh, dbname=dbname, schema=schema)

  ## Only keep those objects that are not NULL
  objects %<>% removeNullsFromList

  # if (!length(objects)) {
  #   warning("All options passed to setSnowflake() were NULL. No changes made to snowflake", call.=FALSE)
  #   return(invisible(objects))
  # }

  ## Confirm all objects are string of length 1
  sapply(objects, is.char_of_length1, showWarnings=FALSE) %>% {!.} %>% {
    if (any(.))
      stop("All arguments to setSnowflake() must be either NULL or a character vector of length 1. Offender(s) is (are) ", pasteQand(nwhich(.)))
  }

  ## Verbose output for any changes being made
  if (length(objects)) {
    current_vals <- .sf_show_current(object=names(objects), connex=connex)
    if (verbose)
      sprintf("Changing %6s from '%s' to '%s'", names(objects), current_vals, objects)[toupper(current_vals) != toupper(objects)] %>% cat(sep="\n")
  }

  ## set snowflake options
  do.call(.sf_use, c(list(warnOfUse=FALSE, connex=connex, verbose=FALSE), objects))

  ## set R options
  options(setNames(sprintf("snowflake_%s", names(objects)), obj=objects))

  if (start) {
    if (!sfIsWarehouseOn(wh, exact=TRUE, ignore.case=TRUE))
      try(sfWarehouseOn(wh, set_warehouse=FALSE, connex=connex, verbose=verbose), silent=FALSE)
  }

  return(invisible(objects))
}

getSnowflake <- function(object=c("wh", "dbname", "schema"), refresh=FALSE, connex=sfGetCon(), verbose=FALSE) {
  object <- match.arg(object, several.ok=TRUE)
  missing_refresh <- missing(refresh)

  if (refresh) {
    settings <- .sf_show_current(object, connex=connex) %>% as.list
    ## set the local settings
    options(setNames(sprintf("snowflake_%s", names(settings)), obj=settings))
    return(settings)
  }

  # browser(text="check getSnowflake dbname.dbname")

  ## ELSE
  sapply(object, function(obj) {
    opt_name <- sprintf("snowflake_%s", obj)
    ans <- getOption(opt_name)
    if (is.null(ans) && missing_refresh)
      ans <- getSnowflake(obj, refresh=TRUE)
    unname(valueIfNull(ans, ""))
  })

}

getSnowflakeWH <- function(refresh=FALSE, connex=sfGetCon(), verbose=FALSE) {
  getSnowflake(object="wh", refresh=refresh, connex=connex, verbose=verbose)
}
getSnowflakeDB <- function(refresh=FALSE, connex=sfGetCon(), verbose=FALSE) {
  getSnowflake(object="dbname", refresh=refresh, connex=connex, verbose=verbose)
}
getSnowflakeSchema <- function(refresh=FALSE, connex=sfGetCon(), verbose=FALSE) {
  getSnowflake(object="schema", refresh=refresh, connex=connex, verbose=verbose)
}

getWH_already_on <- function(default="SCIENCE", possible_wh=c(default, "SCIENCE", "SPOTIFYAGGREGATES", "LOOKER_WH_LARGE", "LOAD_WH", getSnowflakeWH())) {
  DT.warehouses <- sfShowWarehouses(verbose=FALSE)

  if (nrow(DT.warehouses)) {
    DT.warehouses[, state := trim(state)]
    DT.warehouses <- DT.warehouses[state != "Off"]
  }

  possible_wh %<>% toupper %>% unique()

  ret <- character()

  if ("On" %in% DT.warehouses$state) {
    ## First check default
    for (deft in toupper(default))
      if (deft %in% DT.warehouses[state == "On", name])
        return(deft)

    whs_on <- DT.warehouses[state == "On"][, name]
    ## take the first match
    ## if there was no match, ret will have no length
    ret <- head(possible_wh[possible_wh %in% whs_on], 1)
  }
  
  # if (!length(ret))
  #   ret <- DT.warehouses[name %in% possible_wh, head(name, 1)]

  ## If there is no match, return the first item from default
  if (!length(ret)) {
    if (length(default))
      ret <- default[[1]]
    else 
      ret <- "science"  ## don't return blank
  }

  return(ret)
}

getWH_by_interactive <- function(default=getWH_already_on()) {
  if (interactive()) 
    default
  else
    "CRON_JOBS_SMALL"
}