# Create Analytics_release_level on Snowflake.r

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




stop("Analytics_release_level  is not yet ready -- this file will not do what you think it does")

if (FALSE)
{
  setGitBranchToSystem()
  .g()
}

setScience(proj="DeNormalizing", subProj="Analytics_release_level", quiet=TRUE, load=FALSE, create=FALSE, subl=FALSE)
snowflake_inuse <- TRUE
setPrompt(full=TRUE)

options(sfGetCon_quiet_connex = FALSE)


## TODO 2015-07-28 -- Does this need a designated Log file?
sinkFile <- newLogFile(name="BI_Analytics_release_level_Creation")
sinkOn(file=sinkFile)

## DEFAULT TO SNOWFLAKE FOR NOW
assignIfNotExist(wh, getSnowflakeWH())
assignIfNotExist(dbname, getSnowflakeDB())

if (wh == "") wh <- "BI_ETL_JOBS_STANDARD"
if (dbname == "") dbname <- "prod"

setSnowflake(wh=wh, dbname=dbname, start=TRUE)

### CHECK FOR ANY BLANKS IN THE DOWNLOAD_ACTIVITY_DATE FIELD
blank_dates <- sfQry("SELECT 'A' FROM prod.production.fact_analytics_release_level WHERE download_activity_date IS NULL LIMIT 5") %>% nrow
if (blank_dates > 0) {
  f_update_date <- "~/git/orch/src/DeNormalizing/BI_tables/fact_analytics_release_level - update download_activity_date.sql"
  sqlFileToQry(f_update_date) %>% sfQry(wh=wh, verbose=TRUE)
}


folder <- src.p("BI_tables", "Analytics")

files <- extractFilesFromFolder(folder, ext="sql", full=TRUE)
files %<>% filegroup_adjust_for_snowflake(snowflake_inuse=snowflake_inuse)

## REMOVE the CREATE files, since we are using SELECT to create
files %<>% {.[!grepl("^01_A. create_", names(.))]}

for (file in files) {
  try(cat("\n\n ------- EXECUTING SQL FILE:  ", path.unexpand(file), "-------- \n"))
  qry_whole <- sqlFileToQry(file, clear=TRUE)
  ## track_level to release level
  qry_whole %<>% gsub("nalytics_track_level", "nalytics_release_level", ., ignore.case=TRUE)
  

  qry_splat <- strsplit(qry_whole, ";")[[1]] %>% trim %>% removeNullsAndBlanksFromList
  ## Use for-loop instead of sapply, as easier to recover from in event of crash
  # sapply(qry_splat, sfQry, wh=wh, dbname=dbname)
  for (q_splat in qry_splat)
    sfQry(q_splat, wh=wh, dbname=dbname)
}


swap_in_new_table(tbl_old="Analytics_release_level", tbl_new="Analytics_release_level_next", schema="bi", zArchive.old=TRUE, wh=wh, dbname=dbname)

print(sfShowTables(schema="bi"))

## 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")})


sinkOff()
catn("Today is ", timeStamp(human=TRUE))