# Create Analytics_track_level on Snowflake.r

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

system("touch ~/git/orch/log/DeNormalizing/starting_creating_analytics.txt")


## PULL GIT CHANGES
try({
  setGitBranchToSystem()
  .g()
}, silent=FALSE)

## Set Science
setScience(proj="DeNormalizing", subProj="Analytics_track_level", quiet=TRUE, load=FALSE, create=FALSE, subl=FALSE)
snowflake_inuse <- TRUE
setPrompt(full=TRUE)
options(sfGetCon_quiet_connex = FALSE)
options(snowflake_msg_sf=FALSE)

keep_the_old_table = FALSE

try(notifyAndEmail(subj="Starting Analytics_track_level BI ETL", msg=timeStamp("Starting Analytics_track_level BI ETL at", sep=" ", human=TRUE)))

## SETUP LOG
try({
  logname <- sprintf("BI_%s_Creation", "Analytics_track_level")
  previousLogFile <- getLastSinkFileFromDisk(logname, dir=logDir)
  sinkFile <- newLogFile(name=logname)
  sinkOn(file=sinkFile)
})

## WRAP EXECUTION IN try()
try ({

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

if (wh == "") wh <- "BI_ETL_JOBS_STANDARD"
if (dbname == "") dbname <- "prod"
schema <- "bi"
tbl_dest <- "Analytics_track_level"
tbl_tmp <- sprintf("%s_next", tbl_dest)

sfWarehouseOn(wh=wh, size="XL")
setSnowflake(wh=wh, dbname=dbname, start=TRUE)
sfWaitForWarehouse(wh=wh, N.seconds=30, max_iterations=100, verbose.waiting=TRUE)

## Check if tbl_tmp exists. If so, FAIL.
if (qTableExists(dbname=dbname, schema=schema, tbl=tbl_tmp, snowflake_inuse=TRUE)) {
  ## move the logfile from zArchive
  accidentallyZarchivedLogFile <- as.path(dirname(previousLogFile), "zArchive", basename(previousLogFile))
  if (file.exists(accidentallyZarchivedLogFile) && !file.exists(previousLogFile)) {
    message("About to abort... moving previous log file back\n\t  to '", previousLogFile, "'")
    print(file.rename(accidentallyZarchivedLogFile, previousLogFile))
  }
  stop("Table ", dbschematbl(dbname=dbname, schema=schema, tbl=tbl_tmp), " already exists.\nThis might mean that the previous run ended with error or is still running (or some other issue).\nAborting.\n")
}

## TODO, change the Analytics folder then update this to use tbl_dest
folder <- src.p("BI_tables", "Analytics")
comment <- sprintf("Table last updated %s EST", timeStamp(human=TRUE, tz="America/New_York"))

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(.))]}
verboseMsg(verbose, "Will execute the following files: ", pasteQand(names(files)), minw=120)

for (file in files) {
  try(cat("\n\n ------- EXECUTING SQL FILE:  ", path.unexpand(file), "-------- \n"))
  qry_whole <- sqlFileToQry(file, clear=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)
    ## Wrapping this in TRY;  I dont like doing so since that ultimately may allow for impropper data
    try({
      sfQry(q_splat, wh=wh, dbname=dbname)
    })
}


## After queries complete, replace the existing table with the temp table we were populating
swap_in_new_table(tbl_old=tbl_dest, tbl_new=tbl_tmp, schema=schema, zArchive.old=keep_the_old_table, wh=wh, dbname=dbname)
sfQry(sprintf("ALTER TABLE %s SET COMMENT = '%s'", dbschematbl(dbname=dbname, schema=schema, tbl=tbl_dest), comment))

## Show tables and dates
print(sfShowTables(schema=schema), nrow=200)
print(qMaxDate(schema=schema, tbl=tbl_dest, byCols=c("store_name", "storeid"), verbose=TRUE))
})

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

## ----------- RELEASE LEVEL ----------------------- ##
  ## Create Release Level
  try(  create_release_level_table(tbl.trk="analytics_track_level", tbl.rel="analytics_release_level", schema=schema, dbname=dbname, wh=wh, zArchive.old=keep_the_old_table, verbose=TRUE) )
  ## Run Confirmation Script to confirm that release and track level have the same figures
  try({
    if (confirm_release_level_table(tbl.trk="analytics_track_level", tbl.rel="analytics_release_level", schema=schema, dbname=dbname, wh=wh, zArchive.old=keep_the_old_table, verbose=TRUE))
          catn("CONFIRMED:  analytics_release_level is same as analytics_track_level")
    else
          catn("ERROR:  analytics_release_level is DIFFERENT from analytics_track_level")
  })
  catheader("Updating Permissions -- after Release Level", endl=0)
  try({source("~/git/orch/src/DeNormalizing/snowflake_permissioning_update.r")})
## ----------- RELEASE LEVEL ----------------------- ##


try(notifyAndEmail(subj="Completed Analytics (Release & Track Level) BI ETL", msg=timeStamp("Completed Analytics (Release & Track Level) BI ETL at", sep=" ", human=TRUE)))
catheader("Script Completed Succesfully;  Exiting")
sinkOff()
catn("Today is ", timeStamp(human=TRUE))