## Create All Views.r

## EXECUTION IS AT BOTTOM OF FILE, AFTER FUNCTION

# source("~/git/orch/src/DeNormalizing/BI_tables/Create All Views.r")


## -------------------------------------------------------------------------------------------------- ##

CreateViews.snowflake <- function(folder=src.p("BI_tables", "Views"), viewWithDependencies=c("fa_errors_view", "release_view", "artist_view", "label_view"), viewsDependedOn=c("country_view"), cluster=NULL, files=extractFilesFromFolder(folder, ext="sql", full=TRUE), wh=getSnowflakeWH(), dbname=getSnowflakeDB(), schema="bi", snowflake_inuse=getOption("snowflake_inuse", FALSE), verbose.qry=FALSE, verbose=TRUE) {

  files %<>% filegroup_adjust_for_snowflake(snowflake_inuse=snowflake_inuse)

  ## Need to drop fa_errors_view for now... the create or replace does not work for it
  if ("fa_errors_view.sql" %in% names(files) && qViewExists(schema="bi", tbl="FA_ERRORS_VIEW", msg_sf=FALSE))
    sfQry("DROP VIEW bi.FA_ERRORS_VIEW", verbose=TRUE)

  qrys <- sqlFileToQry(files, clear=TRUE) %>% sub("CREATE VIEW", "CREATE OR REPLACE VIEW", .)
  ret <- list()
  for (nm.qry in names(qrys)) {
    qry <- qrys[[nm.qry]]
    verboseMsg(verbose, "Executing sql for view '", removeText("\\.sql$", nm.qry), "'", sep="", minw=90)
    if (!nchar(qry)) {
      warning("qry has no content ... did you comment the whole thing out? If so zArchive the file.")
      ret <- c(ret, NA)
    } else
      ret <- c(ret, try({  sfQry(qry, wh=wh, dbname=dbname, verbose=verbose.qry)  }))
  }

  ## qUpdatePerms() was for redshift
  ## source the following file for snowflake to update the permissions on the newly created tables
  catheader("Updating Permissions", endl=0)
  try({source("~/git/orch/src/DeNormalizing/snowflake_permissioning_update.r")})

  return(ret)
}

## -------------------------------------------------------------------------------------------------- ##

CreateViews <- function(folder, viewWithDependencies=c("fa_errors_view", "release_view", "artist_view", "label_view"), viewsDependedOn=c("country_view"), cluster=NULL, files=extractFilesFromFolder(folder, ext="sql", full=TRUE), wh=getSnowflakeWH(), dbname=getSnowflakeDB(), schema="bi", snowflake_inuse=getOption("snowflake_inuse", FALSE), verbose=TRUE) {


"~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
"NOTE TO SELF 2015-08-01 -- qShowViews returns different result structure for redshift than snowflake. Namely with and without schema. 
      Therefore the schema removal/adding throughout this function is horribly sloppy.  When time, clean it up at the qShowViews level"
"~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"



  if (!is.null(cluster))
    setDBall(cluster=cluster)


  ## if files is given explicitly, adjust folder and names(files)
  if (!missing(files)) {
    if (!missing(folder))
      stop ("only one of 'folder' and 'files' may be given explicitly")

    folder <- dirname(files)
    if (lunique(folder) == 1)
      folder <- folder[[1]]

    if (is.null(names(files)))
      selfname_(files)

    ## &&& TODO:  I just remembered that we have to watch out for cascading dependencies. 
    ##   It is not so easy as to just use folder or files.  Must use both, find depenceies, recreate those, etc. 

  }

  # names(files) <- basename(files)
  BackUpOrRestore("files")

  ## some views have special snowflake versions.  Drop one version accordingly
  files %<>% filegroup_adjust_for_snowflake(snowflake_inuse=snowflake_inuse)
  #  nms.sf_specific <- extract("_snowflake", names(files))
  #  if (length(nms.sf_specific)) {
  #    if (snowflake_inuse)
  #      files <- files[names(files) %ni% gsub("_snowflake", "", nms.sf_specific)]
  #    else 
  #      files <- files[names(files) %ni% nms.sf_specific]
  #
  #    names(files) <- gsub("_snowflake", "", names(files))
  #  }

  ## clean up the names(files)
  names(files) %>% gsub("\\.sql", "", .)  %>% toupper %>% setattr(files, "names", .)

  schema %<>% toupper
  viewsDependedOn %<>% toupper
  ## Get list of existing views in the schema
  views_existing <- qShowViews(justnames=TRUE, rowCount=FALSE, cluster=cluster, showWarnings=FALSE, wh=wh, dbname=dbname, schema=schema, snowflake_inuse=snowflake_inuse) %>% toupper


  qrys.create <- sqlFileToQry(files, clear=TRUE)
  ## Add dbname to the view queries;  This should not be necessary, it should be on the connection
  ## .... on second thought.  This should be set on the connection level.  Additionally, what if a table is from somewhere other than production?
  # if (!isNULLorBlank(dbname))
  #   qrys.create %<>% sapply(function(x) gsub(" production\\.", sprintf(" %s.production.", dbname), x))
  qrys.drop   <- makeDropQryFromCreate(qrys.create)
  views <- unclass(gsub("\\s*DROP\\s+VIEW\\s+", "", qrys.drop)) %>% toupper

  ## remove any prefaces from the create statements
  views %<>% .rm_schema_preface(schema=dbname, tbl=.) %>% .rm_schema_preface(schema=schema, tbl=.)

  browser(expr=inDebugMode(c("CreateViewsUpTop")), text="in CreateViews() at top")

  ## names should be the same
  stopifnot(identical(names(files), names(qrys.create)))
  stopifnot(identical(names(files), names(qrys.drop)))

  ## FACT_ANALYTICS_ERROR
  ##  There is a view called 'fa_errors_view' which is an ETL on fact_analytics_error for the mgmt tables
  ##  and it depends on most of the views being dropped and recreated here. 
  ## It too needs to be dropped and recreated
  tbl.faerrors <- schemaPaste(tbl='FA_ERRORS_VIEW', schema=schema)
  drop_and_recreate.faerrors <- tbl.faerrors %in% views_existing

  ## viewsDependedOn      have to be the first ones created and the last ones deleted
  ## viewWithDependencies have to be the first ones created and the last ones deleted
  ## Thus, the ordering is: 
  ##    viewWithDependencies, views_OTHER, viewsDependedOn

  ## ------------------------------------------------------------------------ ##
  ## Adjust order according to dependencies
  ## ------------------------------------------------------------------------ ##
  ## Check for additional viewsDependedOn
  addl.viewsDependedOn <- nwhich(sapply(setdiff(names(views), viewsDependedOn),
                            function(x) any(grepl(views[[x]], qrys.create[names(qrys.create) %ni% c(x, viewWithDependencies)]))))
  viewsDependedOn <- c(addl.viewsDependedOn, viewsDependedOn)

  ## MAKE UPPERCASE
  viewsDependedOn      %<>% toupper
  viewWithDependencies %<>% toupper

  ## only keep those which are actual views, as per the files in the folder
  viewsDependedOn      <- intersect(viewsDependedOn, names(views))
  viewWithDependencies <- intersect(viewWithDependencies, names(views))

  ## Order by what to DROP first.  (when creating, we will call rev())
  views <- views[c(viewWithDependencies, setdiff(x=names(views), y=c(viewWithDependencies, viewsDependedOn)), viewsDependedOn)]
  ## ------------------------------------------------------------------------ ##

  browser(expr=inDebugMode(c("CreateViews", "view")), text="in CreateViews() before for loops")

  ## ------------------------------------------------------------------------ ##
  ## DROP IF EXISTS
  ## ------------------------------------------------------------------------ ##

            ### 2015-08-01 -- this is part of the sloppy workaround for the output of qShowViews
            views_existing_no_schema <- views_existing %>% .rm_schema_preface(schema=dbname, tbl=.) %>% .rm_schema_preface(schema=schema, tbl=.)
            if (length(views_existing) && !any(views %chin% views_existing) && length(intersect(views, views_existing_no_schema) > 1))
              views_existing <- views_existing_no_schema
            names(views_existing) <- toupper(names(views_existing))

  if (length(views_existing) && any(views %chin% views_existing)) {
    for (view.nm in names(views)[views %chin% views_existing]) {
        verboseMsg(verbose, sprintf("Dropping view %22s", view.nm), " ...... ", time=FALSE, endl=0)
        err <- try(runQry(qrys.drop[[view.nm]], cluster=cluster, wh=wh, dbname=dbname, snowflake_inuse=snowflake_inuse, msg_sf=FALSE, verbose=FALSE))
        verboseMsg(verbose, ifelse(isErr(err), "ERROR !", "DONE."), time=FALSE, endl=1)
    }
  } else if (length(views_existing)) {
    warning ("There are views which already exist in schema '", schema, "', however none of them are in the VIEWS designated for creating in CreateViews()", call.=FALSE)
  } else {
    message ("There are no existing views to drop")
  }
  verboseMsg(verbose, pasteR(51), time=FALSE, endl=1)
  ## ------------------------------------------------------------------------ ##

  ## ------------------------------------------------------------------------ ##
  ## CREATE VIEW -- create in reverse order
  ## ------------------------------------------------------------------------ ##
  for (view.nm in rev(names(views))) {
    try({
      
      verboseMsg(verbose, sprintf("Creating view %22s", view.nm), " ...... ", time=FALSE, endl=0)
      if (qViewExists(views[view.nm], cluster=cluster, wh=wh, dbname=dbname, schema=schema, msg_sf=FALSE))
        verboseMsg(verbose, " !!!  ERROR  -- View Already Exists!", time=FALSE, endl=1)
      else {
        err <- try(runQry(qrys.create[[view.nm]], cluster=cluster, wh=wh, dbname=dbname, snowflake_inuse=snowflake_inuse, msg_sf=FALSE, verbose=FALSE))
        try(qUpdatePerms(tbl=gsub("\\.sql$", "", views[view.nm]), schema=schema, user="orcdpipeline", cluster=cluster, wh=wh, dbname=dbname, snowflake_inuse=snowflake_inuse, msg_sf=FALSE, verbose=FALSE))
        if (snowflake_inuse)
          if (!qViewExists(views[view.nm], cluster=cluster, wh=wh, dbname=dbname, schema=schema, msg_sf=FALSE)) {
            stop (sprintf("view '%s' not created properly in dbname=%s, schema=%s", view.nm, dbname, schema), if (isErr(err)) paste0("\nsfQry threw error: ", gsub("Error : ", "", as.character(err))))
          }
        verboseMsg(verbose, ifelse(isErr(err), "ERROR !", "DONE."), time=FALSE, endl=1)
      }
    })
  }
  ## ------------------------------------------------------------------------ ##

  return(invisible(views))
}

## ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ##












# -------------------------------------------------------------------------------- # 
#                                    EXECUTION                                     # 
# -------------------------------------------------------------------------------- # 

## personal git function - ensure clean working space
if (FALSE) 
{
  setGitBranchToSystem()
  .g()
  .us()
  source("~/git/orch/src/DeNormalizing/BI_tables/Create All Views.r")
}


## UPDATE THE client_managers tbl -- this is used in label_view
source("~/git/orch/src/DeNormalizing/create client_managers tbl.r")


## set project workspace
setScience(proj="DeNormalizing", subProj="VIEWS", quiet=TRUE, load=FALSE, create=FALSE, subl=FALSE)

## LOG FILES
sinkFile.create_views <- newLogFile(name="Views_Creation")
if (!(exists("DONTSINK", envir=globalenv()) && isTRUE(DONTSINK)) && !interactive()) {
  sinkOn(file=sinkFile.create_views)
}

## CONFIRM WAREHOUSE
wh <- getWH_by_interactive()

if (wh != "CRON_JOBS_SMALL")
  warning ("WRONG WAREHOUSE BEING USED FOR CREATE ALL VIEWS", call.=FALSE)

## Clear snowflake settings
setSnowflake(wh=NULL, dbname=NULL, schema=NULL)

## Where to pull SQL files from
folder <- src.p("BI_tables", "Views")


## ----------------------------------------------------- ##  
## DB Connection parameters.
## If not already present, assign the given values
## ----------------------------------------------------- ##  
  ## REDSHIFT
  assignIfNotExist(cluster, 7)

  ## SNOWFLAKE
  assignIfNotExist(wh, getSnowflakeWH())
  assignIfNotExist(dbname, getSnowflakeDB())
  setSnowflake(wh=wh, dbname=dbname)
## ----------------------------------------------------- ##  


## ----------------------------------------------------- ##  
## Manually comment in or out, whether using snowflake or redshift
## ----------------------------------------------------- ##  
  options("snowflake_inuse"=TRUE)
  # options("snowflake_inuse"=FALSE)
## ----------------------------------------------------- ##  

## More Verbose
options(sfGetCon_quiet_connex = FALSE)

## OLD:  Run the function
if (isTRUE(getOption("snowflake_inuse"))) {
  message ("Updating views in Snowflake ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ")
  assignIfNotExist(wh, getSnowflakeWH())
  assignIfNotExist(dbname, getSnowflakeDB())
  cat("\n\n ------- BEGINNING VIEW (DROP & RE)CREATE IN Snowflake -------- \n")
  CreateViews.snowflake(folder, cluster=cluster, schema="bi", wh=wh, dbname=dbname, snowflake_inuse=TRUE, verbose.qry=FALSE, verbose=TRUE)
} else {
  message ("XXXXX >>>>      Updating views in REDSHIFT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ")
  clustersUsing <- unique(c(getOption("db.defaultcluster.in"), getOption("db.defaultcluster.out")))

  for (cluster in clustersUsing) {
    cat("\n\n ------- BEGINNING VIEW (DROP & RE)CREATE ON CLUSTER ", cluster, "-------- \n")
    CreateViews(folder, cluster=cluster, schema="bi", verbose=TRUE)
  }
}
cls(3)
sinkOff()

















# -------------------------------------------------------------------------------- #
#                                    SCRATCH                                       #
# -------------------------------------------------------------------------------- #
#      This is an export for the main views that I want to put on a google drive   #
#      to allow people to give input as to grouping                                #
# -------------------------------------------------------------------------------- #
if (FALSE) 
{
  wh <- getWH_already_on()
  dbname <- "prod"
  setSnowflake(wh=wh, dbname=dbname)
  views <- qShowViews(schema="bi", justnames=TRUE)

  views %<>% setdiff("JEDIMINDTRICK")

  too_large <- c("artist", "release", "label", "track", "fa_error")
  pat <- too_large %>% regOr %>% sprintf("^%s.*_view$", .)
  views %<>% {setdiff(., extract(pat, .))}

  views %<>% extract("_view$", .)

  ll.files <- c()
  for (view in views) {
    DT.samp <- headDB(view, schema="bi", n=1000, snowflake=TRUE)
    ll.files %<>% c(writeDT(DT.samp, base.file.name=view))
  }

  quickEmail(to=getRS(), file=ll.files, info="dim views")
}
# -------------------------------------------------------------------------------- #