setScience("ETL", subProj="reportsAR", create=TRUE, subl=FALSE)
setGitBranchToSystem();  .g()

options("tbl.dontclean" = TRUE)

wh <- "REPORTSAR_MIGRATION"
dbname <- "prod"
schema.out <- "art_relations"

initial_tables <- c("artist_info", "asset", "publishing_escrow", "publishing_escrow_released", "releases", "track", "track_artist", "vendor") %>% selfname_
files.tsv <-  emptylist(initial_tables)

##################################
READ FROM TABLE
##################################

setDBall("MySQL")
tbl <- "track_artist"
for (tbl in initial_tables) 
{
  catheader(tbl)
  s.t({DT <- runQry(makeQry(tbl=tbl, schema=NULL, colsToPull="*", expandStar=FALSE, limit=NULL))}, title="reading")
  nm.DT <- sprintf("DT.%s", tbl)
  assign(nm.DT, value=DT)

  ## Save to dataDir
  s.t(title="writing .RDS to dataDir",    jesusForData(objNames=nm.DT, dir=data.p("reportsAR"))  )
  s.t(title="writing .TSV to outDir",     {files.tsv[[tbl]] <-  writeDT(get(nm.DT), subfolder="reportsAR", base.file.name=tbl)}  )  

  rm(DT)
  gc()
}

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

#  for (tbl in initial_tables) {
#    catheader(tbl)
#    nm.DT <- sprintf("DT.%s", tbl)
#  
#    ingestIntoSQL(append=TRUE, DT=get(nm.DT), tbl=tbl, schema=schema.out, wh=wh, dbname=dbname, snowflake_inuse=TRUE, datetime_type='TIMESTAMP_NTZ')
#  
#    if (nrow(get(nm.DT)) == qRowCount(tbl=tbl, schema=schema.out, wh=wh, dbname=dbname, snowflake_inuse=TRUE, msg_sf=FALSE))
#      rm(list=nm.DT)
#  }
#  
#  
#  ##################################
#  ALTERNATVIE:  READ FROM DISK
#  ##################################


## If it breaks and we need to manually repopulate the files list
if (FALSE)
  files.tsv <- dir(full=TRUE, out.p("reportsAR")) %>% sort %>% setNames(sort(initial_tables), obj=.)


#  ###########################################
#  WRITE OPTION 1:  POPULATE FROM files.tsv
#  ###########################################
for (tbl in extract("escrow", initial_tables)) 
{
  catheader(tbl, prel=1, endl=0)
  if (!qRowCount(tbl=tbl, schema=schema.out, wh=wh, dbname=dbname, snowflake_inuse=TRUE))
      sfPopulateTable(overwrite=FALSE, file_name=files.tsv[[tbl]], tbl=tbl, schema=schema.out, wh=wh, dbname=dbname)
}


#  ###############################################
#  WRITE OPTION 1:  LOAD DT AND POPULATE FROM DT
#  ###############################################
for (tbl in tail(initial_tables, -1)) {
  catheader(tbl, prel=1, endl=0)
  nm.DT <- sprintf("DT.%s", tbl)
  
  if (!exists(nm.DT))
    loadFromJesus(nm.DT)

  ingestIntoSQL(append=TRUE, DT=get(nm.DT), tbl=tbl, schema=schema.out, wh=wh, dbname=dbname, snowflake_inuse=TRUE, datetime_type='TIMESTAMP_NTZ')

  # if (nrow(get(nm.DT)) == qRowCount(tbl=tbl, schema=schema.out, wh=wh, dbname=dbname, snowflake_inuse=TRUE, msg_sf=FALSE))
    rm(list=nm.DT)
}