# screen -xRR SpotifyETL


if (FALSE) {
  source("~/git/orch/src/Spotify_Accounting_ETL/20150810 Export to Looker.r")
}

## SETUP LOG
try({
  setScienceIfNot(proj="Spotify_Accounting_ETL", subProj="ExportToLooker")
  logname <- "Spotify_Accounting_ETL_to_looker"
  previousLogFile <- getLastSinkFileFromDisk(logname, dir=logDir)
  sinkFile <- newLogFile(name=logname)
  sinkOn(file=sinkFile)
})



## ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ##
## ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ##
## THIS SETUP HAS BEEN COPIED FROM 01 QUICK AUTO
{
    fresh(notify.startup=FALSE)

    setScience(proj="Spotify_Accounting_ETL", subProj="ExportToLooker", create=FALSE, subl=FALSE, load=FALSE)
    setGitBranchToSystem(); .g()

    # productsUsing.forUserCounts <- c("A", "P", "PD", "U")
    productsUsing.forUserCounts <- TRUE

    wh <- getWH_already_on(default="SCIENCE")
    dbname <- "prod"
    setSnowflake(wh=wh, dbname=dbname)

    ## Step 1 - Download the files
    source("~/git/orch/src/Spotify_Accounting_ETL/01 Retrieve Files From FTP.r")

    if (!getOption("snowflake_inuse")) {
      warning("\n\nsnowflake_inuse has been reset during file 01\n\n")
      setSnowflake(wh=wh, dbname=dbname)
    }

    ## Step 2 - Ingest the files
    source("~/git/orch/src/Spotify_Accounting_ETL/02 Ingest Spotify Files.r")
}
## ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ##
## ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ##


setScience(proj="Spotify_Accounting_ETL", subProj="ExportToLooker", load=FALSE, subl=FALSE, quiet=TRUE)

RE_CREATE_LOOKML <- FALSE

not_ingesting <- c("DT.MISSING_DATA", "DT.country", "DT.country", "DT.super_total", "ll.SpotifyRates", "row1", "DT", "DT.lookml")

## Pull the list of names of DTs
nms.DT <- lsosdt()$name %>% setdiff(not_ingesting) %>% selfname_

# show the tbales
showDataTables(nms.DT, col=25, lines=100)

wh <- getWH_already_on(default="CRON_JOBS_SMALL")
schema <- "spotifymgmt"
dbname <- "prod"

setSnowflake(wh=wh, dbname=dbname, start=TRUE)
sfShowTables(wh=wh, dbname=dbname, schema=schema, comments=FALSE)[]


## CREATE SCHEMA IF NECESSARY
if (!sfSchemaExists(dbname=dbname, schema=schema))
  sprintf("CREATE SCHEMA %s", dbschematbl(dbname=dbname, schema=schema)) %>% sfQry()

# for (nm in nms.DT) {
#   try(sprintf("DROP TABLE prod.%s.%s", schema, gsub("DT\\.", "", nm)) %>% sfQry(verbose=FALSE), silent=TRUE)
# }

for (nm in nms.DT) {
  # setnames(get(nm), gsub("\\(%\\)", "percentage", names(get(nm)), ignore.case=TRUE))
  # setnames(get(nm), gsub("\\(?ad-supported\\)?", "adsupported", names(get(nm)), ignore.case=TRUE))
  # setnames(get(nm), gsub("No.", "Number", names(get(nm)), ignore.case=TRUE))
  # setnames(get(nm), gsub("\\.", "_", names(get(nm)), ignore.case=TRUE))
  # setnames(get(nm), gsub("\\(|\\)", "", names(get(nm)), ignore.case=TRUE))
  try(ingestIntoSQL(nm, schema=schema, dbname=dbname, datetime_type="TIMESTAMP_NTZ", confirm=TRUE, drop=TRUE, append=FALSE, wh=wh, snowflake_inuse=TRUE, verbose=TRUE))
}

debug(ingestIntoSQL)

## Confrim all tables are loaded
{
  catheader("Checking that all tables are loaded", prel=3, endl=1)
  is_loaded <- nms.DT %>% gsub("DT\\.", "", .) %>% sapply(qTableExists, schema=schema, msg_sf=FALSE, snowflake=TRUE) %>% as.logical
  catn("Confirming that all tables have been loaded ...")
  if (any(!is_loaded))
    warning(warningCols("Some tables were not properly loaded: ", nms.DT[!is_loaded]))
  else
    catn("All ", length(nms.DT), " tables have been loaded!")
}


if (RE_CREATE_LOOKML) 
{
  catn("Creating Lookml ...")
  sourceSupportFns(proj="Looker")
  create_lookml_model_from_schema(schema=schema, dbname=dbname, wh=wh)
}


sinkOff()
showSink()