# ------------------------------------------------------------------------------------- #
#            CONSTRUCT WHERE CLAUSE                                                     #
# ------------------------------------------------------------------------------------- #
#   Two different methods, depending on whether whereIn is a data.table or a named-list #
#   Note that even if `whereIn` is NULL, the second method still address date min/max   #
#      assuming they are not also NULL                                                  #
# ------------------------------------------------------------------------------------- #

  where_NoClaims <- function(ids.to.ignore=33:36) {
  ## We will often need to exclude these trans ids from fact_analytics
    sprintf("transactiontypeid NOT in (%s)", pasteC(ids.to.ignore, C=","))
  }

  makeWhereClause <- function(whereIn, dateCol, minDate=NULL, maxDate=NULL, lte.maxDate=c("<=", "<"), gte.minDate=c(">=", ">"), prependCols.with.tbl=FALSE, conjunction=c("AND", "OR"), ...) {
    UseMethod('makeWhereClause')
  }

  makeWhereClause.data.table <- function(whereIn, dateCol, minDate=NULL, maxDate=NULL, lte.maxDate=c("<=", "<"), gte.minDate=c(">=", ">"), tbl=NULL, prependCols.with.tbl=FALSE, conjunction=c("AND", "OR")) {
    ## TODO: Describe what is exepcted, then check names and expected types, etc

    if (!missing(conjunction))
      warning ("'conjunction' is not used when whereIn is a data.table")

    lte.maxDate <- match.arg(lte.maxDate)
    gte.minDate <- match.arg(gte.minDate)

    ## CONFIRM minDate / maxDate are proper (ie, not both NULL, and each either logical or null)
    isLogicalOrNull <- function(x) is.null(x) || is.logical(x) 
    if (!length(c(minDate, maxDate)) || !isLogicalOrNull(minDate) || !isLogicalOrNull(maxDate) )
      stop("when `whereIn` is a data.table, minDate & maxDate must be logical with just one of the two allowed to be NULL.")

    ## Drop columns from whereIn if var is not TRUE
    if(!isTRUE(minDate))
      suppressWarnings(whereIn[, minDate := NULL])
    if(!isTRUE(maxDate))
      suppressWarnings(whereIn[, maxDate := NULL])

    ## for ease of handling, gather the column names being used into objects
    nms <- names(whereIn)
    # these names come from the `minDateBy` function
    nms.dates <- intersect(c("minDate", "maxDate"), nms)
    # we want all the other names, which will comprise the where clause
    nms.c  <- setdiff(nms, nms.dates)

    # Clean the names of `whereIn` so that they paste appropriately
    names(nms.c) <- nms.c
    if (prependCols.with.tbl)
      nms.c[] <- valueIfNull(names(tbl), tbl) %>% paste0(., ".", nms.c)
    setnames(whereIn, names(nms.c), nms.c)

    ## Create the where clause
    ret <- 
      whereIn[, list(.whereClause = 
        paste(        
          c(  lapply(names(.SD), function(x) paste(x, "in", pasteQ(unique(get(x)))))
                  , if ("minDate" %in% names(.BY))
                      paste(dateCol, gte.minDate, .BY[["minDate"]], collapse=" AND ")
                  , if ("maxDate" %in% names(.BY))
                      paste(dateCol, lte.maxDate, .BY[["maxDate"]], collapse=" AND ")
                )
          , collapse="     AND "
        ))
      , .SDcols=nms, by=nms.dates][, paste("(", .whereClause, ")", collapse=" OR ")]

    return(ret)
  }
  

  .clean_conjunction <- function(conjunction, choices=c("AND", "OR")) {
    if (!length(conjunction))  {
      warning("conjunction has no length, returning NULL", call.=FALSE)
      return(NULL)
    }

    conjunction[[1]] %>% toupper %>% trim %>% match.arg(choices=choices) %>% sprintf(" %s ", .)
  }

  makeWhereClause.default <- function(whereIn, dateCol, minDate=NULL, maxDate=NULL, lte.maxDate=c("<=", "<"), gte.minDate=c(">=", ">"), tbl=NULL, prependCols.with.tbl=FALSE, schema=NULL, conjunction=c("AND", "OR"), wherein="WRONG_ARGUMENT--CHECK CAP", ...) {
                   
    ## Check for typos
    if (any(c("wehre", "were", "whree") %in% names(list(...))) )
      warning ("it appears you might have a typo or misspelling of 'where' in the arguments to makeQry() or some other function that uses makeWhereClause()")
    if (!missing(wherein)) 
      stop ("Argument wherein is incorrect. It should whereIn with a capital-I ")

    lte.maxDate <- match.arg(lte.maxDate)
    gte.minDate <- match.arg(gte.minDate)

    ## clean up, remove any spaces, match it, then put the spaces in or back.
    conjunction %<>% .clean_conjunction

    if ("conjunction" %in% names(whereIn)) {
      conj.wi <- whereIn$conjunction %>% .clean_conjunction
        whereIn$conjunction <- NULL
      if (!identical(conj.wi, conjunction))
        warning ("The conjunction in whereIn is '", conj.wi, "' while the conjunction given explicitly is '", conjunction, "'\n The latter will be used")
    }

    ## Append Dots
    if (length(list(...))) {
        whereIn <- c(as.list(whereIn), list(...))
        ## ALSO check for human error
        if ("stbl" %in% names(list(...)))
            warning ("argument 'stbl' sent to makeWhereClause -- was this a mistake, did you mean 'tbl'?")
    }

    ## Drop any NULLs.  Useful for 'list(if(expr) "some clause", store=1:7, etc..)'
    non_blank <- sapply(whereIn, length)>0
    if (!any(non_blank) && length(whereIn))
      warning("whereIn has no non-NULL clauses")
    whereIn <- whereIn[non_blank]

    ## if blank, return emptry string
    if (!length(whereIn)) {
      if (!length(minDate) && !length(maxDate))
        return("")
      # else 
      #   message("2015-04-12  New behavior in makeWhereClause :: did NOT exit routine when 'whereIn' was NULL. Instead checked minDate and maxDate")
    }

    ## If any whereIn are factors, convert to characters
    if (length(whereIn) && length(wh.factors <- whichFactors(whereIn)))
      whereIn[wh.factors] <- lapply(whereIn[wh.factors], as.character)

    if (isTRUE(prependCols.with.tbl) && (is.null(tbl) || tbl == ""))
      stop ("if 'prependCols.with.tbl' is TRUE, 'tbl' must be specified")


    quote_theDate <- function(date) {
      if (is.null(date))
        return(FALSE)
      if (length(date) > 1) {
        warning("'", capture.output(substitute(date)), "', is to have length of 1.\n  Query might be incorrect")
        return(TRUE)
      }
      ## If it looks like a date string, return TRUE
      if (grepl("^\\d+\\-\\d+\\-\\d+$", date))
        return(TRUE)
      ## A small integer is probably a periodid. Dont quote it
      if (is.numeric(date) && date > 0 && date < 900 && as.integer(date) == date)
        return(FALSE)
      ## Characters are expected to already be quoted if needed (we dont want to quote them automatically, as sometimes they are subqueries wrapped in parens)
      if (is.character(date))
        return(FALSE)
      ## All others, quote them
      return(TRUE)
    }

    if (quote_theDate(minDate))  minDate <- paste0("'", minDate, "'")
    if (quote_theDate(maxDate))  maxDate <- paste0("'", maxDate, "'")

# old 2015-01-25    if (!is.null(minDate) && (!is.character(minDate) || grepl("^\\d+\\-\\d+\\-\\d+$", minDate)))
# old 2015-01-25      minDate <- paste0("'", minDate, "'")
# old 2015-01-25    if (!is.null(maxDate) && (!is.character(maxDate) || grepl("^\\d+\\-\\d+\\-\\d+$", maxDate)))
# old 2015-01-25      maxDate <- paste0("'", maxDate, "'")


    browser(expr=inDebugMode("makeWhere"), text="In makeWhereClause.default(), right before pasting the 'ret'")

    # ## paste together the clause
    # ret <- {
    #   paste(  c(
    #     ## if whereIn is given AND has names
    #     if (!is.null(whereIn) && !is.null(names(whereIn))) {
    #       whereIn <- lapply(whereIn, unique)
    #       L <- (sapply(whereIn, length) > 1)       # has Length more than one
    #       C <- (sapply(whereIn, is.character))     # is Character
    #       D <- (sapply(whereIn, is.date_or_time))  # is Date or time
    #       H <- !(names(whereIn) == "")             # Has Name
    #       if (any(!H & !C))
    #         warning ("There are un-named values in whereIn that are not-characters.\nThis will have odd results in the query structure")
    #       paste0(
    #           if (prependCols.with.tbl) paste0(tbl, ".")
    #         , names(whereIn)
    #         , ifelse(H, ifelse(L, " in ", "="), "")
    #         , mapply(pasteQ, whereIn, q=ifelse(H & (C | D), "'", ""), wrap=ifelse(L, "(", ""))  # <~~ This paren throws off findFnsInFile()
    #         , collapse=conjunction)
    #     } else if (!is.null(whereIn)) {
    #       paste(whereIn, collapse=conjunction)
    #     }
    #     ,
    #     if(!is.null(minDate))
    #       sprintf("%s %s %s", dateCol, gte.minDate, minDate)
    #       # paste( dateCol, gte.minDate, minDate )
    #     ,
    #     if(!is.null(maxDate))
    #       sprintf("%s %s %s", dateCol, lte.maxDate, maxDate)
    #       # paste( dateCol, lte.maxDate, maxDate )

    #   ), collapse=conjunction) 
    # }


    ntbl <- valueIfNull(names(tbl), dbschematbl(schema=schema, tbl=tbl))

    ## paste together the clause
    WClause <- {
      paste(  c(
        ## if whereIn is given AND has names
        if (!is.null(whereIn) && !is.null(names(whereIn))) {
          whereIn <- lapply(whereIn, unique)
          L <- (sapply(whereIn, length) > 1)       # has Length more than one
          C <- (sapply(whereIn, is.character))     # is Character
          D <- (sapply(whereIn, is.date_or_time))  # is Date or time
          H <- !(names(whereIn) == "")             # Has Name
          if (any(!H & !C))
            warning ("There are un-named values in whereIn that are not-characters.\nThis will have odd results in the query structure")
          paste0(
              if (prependCols.with.tbl) paste0(ntbl, ".")
            , names(whereIn)
            , ifelse(H, ifelse(L, " in ", "="), "")
            , mapply(pasteQ, whereIn, q=ifelse(H & (C | D), "'", ""), wrap=ifelse(L, "(", ""))  # <~~ This paren throws off findFnsInFile()
            , collapse=conjunction)
        } else if (!is.null(whereIn)) {
          paste(whereIn, collapse=conjunction)
        }
      ), collapse=conjunction) 
    }


    ## Combine the where clause with the dates. 
    ## Dates will always be combined with where clause using 'AND'
    if (prependCols.with.tbl && !is.null(dateCol))
      dateCol <- sprintf("%s.%s", ntbl, dateCol)
    ret <- 
    {
      paste(c(
        ## If the rest of where clause is non-blank, 
        if (!is.null(WClause) && nchar(WClause))
          sprintf("(%s)", WClause)    
        ,
        if(!is.null(minDate))
          sprintf("%s %s %s", dateCol, gte.minDate, minDate)
          # paste( dateCol, gte.minDate, minDate )
        ,
        if(!is.null(maxDate))
          sprintf("%s %s %s", dateCol, lte.maxDate, maxDate)
          # paste( dateCol, lte.maxDate, maxDate )
      ), collapse=" AND ") 
    }

    ret <- gsub("= ?'is NULL'", " is NULL", ret, ignore.case=TRUE)

    ## REPLACE ~ with iLIKE
    pat <- "='~ ?"
    ret <- gsub(pat, repl=" ILIKE '", ret)

    return(ret)
  }