# -- TODO:  (1) change the download ringtone transactions
# -- TODO:  (2) check for any NULL dates in either table
# -- TODO:  (3) check for any NULL music_vs_video in either table





## SQL Executor.r
## This script runs the  INSERT INTO TABLE  scripts for the aggregated_* tables. 



## -------------------------------------------------------------------- ##
## CAN BE RAN FROM TERMINAL AS:                                         ## 
## -------------------------------------------------------------------- ##
##
## git pull; Revo64 -e 'source("~rsaporta/git/orch/src/chartio/SQL Executor.r")'
## git pull; Revo64 -e 'source("~rsaporta/git/orch/src/chartio/SQL Executor.r")' .GPU=FALSE TEST="hi world"
## git pull; Revo64 -e 'source("~rsaporta/git/orch/src/chartio/SQL Executor.r") .GPU=TRUE'
##
##
## -------------------------------------------------------------------- ##




.us()
.us()
setDBall()
showDBsettings()
verbose.dbcon.off()
setScience("DeNormalizing", subl=FALSE, quiet=TRUE)

## FOlder which contains the SQL scripts for denormalizing the tables
SQL.folder.parent <- srcDir  # "~/git/orch/src/DeNormalizing/"

if (exists("TEST"))
  cat("TEST is: '", TEST, "'\n", sep="")

## .GPU : whether to rerun the .GPU 
.GPU <- if (exists(".GPU")) .GPU else TRUE

## MANUALLY, to skip GPU ingestion
# .GPU <- FALSE

{
  START.TIME <- Sys.time()

  ## ONLY RE-RUN GPU CREATION IF NOT FLAGGED TO SKIP
  if (!exists(".GPU") || isTRUE(.GPU)) {
    message("Beginning with sourcing GPU creation")
    ## CREATE/UPDATE GPU
    source("~/git/orch/src/chartio/Create DS_scratch.GPU from analytics.r")
    qUpdatePerms(tbl="gpu", schema="ds_scratch", user="orcdpipeline")
    message("Done sourcing GPU creation")
  }  else  {
    message("Skipping GPU creation")
  }




  ### -----  FIND LATEST FILES & FOLDERS -----  ###
      ## Grab the "AggregatedTables YYMMDD" folder with the latest date
      folders <- dir(SQL.folder.parent, pattern="^AggregatedTables")
      SQL.folder <- as.path(SQL.folder.parent, max(folders))

      ## SQL files start with two digits and end with .sql or .SQL
      SQL.files <- dir(SQL.folder, pattern="^\\d\\d.+\\.[Ss][Qq][Ll]", full=TRUE)
      SQL.files <- sort(SQL.files)

      ## REMOVE TEMP FILE
      SQL.files <- SQL.files[!grepl("03 Updates \\(Temp\\)\\.sql", SQL.files)]

      ## name it, for the list
      SQL.files <- setNames(obj=SQL.files, nm=basename(SQL.files))
  ### -----  // END:  FIND LATEST FILES & FOLDERS -----  ###

      SQL.files <- SQL.files[grepl("^(\\d\\d_AN|03)", names(SQL.files))]

  ## read in the raw text
  QRYs <- lapply(SQL.files, function(ff) {
              raw <- readLines(ff)
              ## strip white space
              raw <- gsub("^\\s*|\\s*$", "", raw)
              ## remove comments and empty lines
              raw <- raw[!grepl("^\\s*--", raw) & !raw==""]
              pasteC(raw, C="\n")
            })

  ## In place of CREATE, instead TRUNCATE
  create.pat <- "^\\s*CREATE TABLE "
  create.qrys <- grep(create.pat, QRYs)
  QRYs[create.qrys] <- lapply(strsplit(gsub(create.pat, "TRUNCATE TABLE ", QRYs[create.qrys]), "\n"), "[[", 1L)

  ## Create an empty list to store the results
  ret <- emptylist(QRYs)

  {
    notify("Beginning")
    for (nm in names(QRYs) ) {
      message(sprintf("%s\n    -=\tExecuting '%s'  =- \n%1$s", pasteR(78), nm))
      Sys.sleep(2.5*2)

      ## EXECUTE
      ret[nm] <- runQry(QRYs[[nm]], verbose=FALSE, results.not.expected=TRUE)

      ## If a population has completed, notify
      if (grepl("populate", nm))
        notifyAndEmail(sprintf("SQL completed for '%s'", nm))

    }
    notify("Done Running")
  }

  END.TIME <- Sys.time()

  notifyAndEmail(paste("Total run time for table updates: ", fwSecs(END.TIME - START.TIME)))
}