## ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ##
##       NOTES JUNE 2016
## ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ##
##       This unnamed file is what Creates the "playlist_metadata" table
##       It also calls the script that creates "playlist_metadata_unique"
##       
##       Last ran this Manually on June 2, 2016.
##       This needs to be scheduled
## ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ##


#   ## screen -xRR SpotMeta
#   ## screen -xRR Playlists
#   
#   In the language of the spotify api, 
#   href  refers to the API link that gets "this.self" data
#   url   refers to (I believe) an external link that you can put in a webbrowser
#   uri   refers to a link that you can put in the Spotify UI
#   uri <~~> url  are easily convertible between the two
#   
#   Example with owner:
#   href:  https://api.spotify.com/v1/users/liviadf
#   url:   http://open.spotify.com/user/liviadf
#   uri:   spotify:user:liviadf
#   
#   
#   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#       TODO:
#       The number of followers is what changes most often
#       A change in follower count will not issue a new snapshot_id  (I believe only a track listing/ordering change)
#       We should
#       (1) Create a separate table of  playlist_id | snapshot_id | api_timestamp | number of followers
#       (2) See if We can request just the playlists follower count from the playlist API, given a URI
#       
#       TODO:  Split up the /users/ folder into sub Folders:  112, 113, .., A, B, 
#   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

setScience("Spotify_Metadata_2016", subProj="TableCreation")
setGitBranchToSystem();  .g()
try(setPrompt(full=TRUE))

## Source in other supportFns;  also source in current supportFns recursively
sourceSupportFns(proj="Spotify Playlist Webscrape")
sourceSupportFns(recursive=TRUE)

wh <- "SPOTIFYAGGREGATES"
wh.mini <- getWH_already_on(default="cron_jobs_small")
dbname <- "prod"
schema.out <- "spot_metadata"

## The table sused for reading / writing data
tbl.uris_in      <- "all_playlist_uris"
tbl.out_metadata <- "playlist_metadata"

try({
  notifyAndEmail("Starting Spot_Metadata Table Creation on Snowflake")
})

using_snapshot_id <- FALSE
## --------------------------------------------------- ##
## Grab all of the URIs from the RAW table
## Then compare against all of the URIs already processed
## --------------------------------------------------- ##
  setSnowflake(wh=wh.mini, dbname=dbname, start=TRUE)
  qry_get_uris <- makeQry(tbl=tbl.uris_in, schema=schema.out, dbname=dbname, colsToPull=c("playlist_id", "playlist_uri", if (using_snapshot_id) "snapshot_id"), expandStar=FALSE, colsToAgg=NULL, limit=NULL, wh=wh.mini)
  DT.uris_in   <- sfQry(qry_get_uris, wh=wh.mini)
  if (using_snapshot_id)
    DT.uris_in[, snapshot_id := as.character(snapshot_id)]

  DT.uris_previously_processed <- sfQry(makeQry(colsToPull=c(playlist_id="id", if (using_snapshot_id) "snapshot_id", "api_timestamp"), tbl=tbl.out_metadata, schema=schema.out, wh=wh.mini), wh=wh.mini)

  verboseMsg(verbose, "All-in-all there are ", nrow(DT.uris_in), " uris -- now let's drop already processed")

  matchKey(DT.uris_in, DT.uris_previously_processed, key=c("playlist_id", if (using_snapshot_id) "snapshot_id"), super=FALSE)
  DT.uris_in <- DT.uris_in[!DT.uris_previously_processed] [!"<unknown playlist>"]
  verboseMsg(verbose, "Now there are ", nrow(DT.uris_in), " uris to process")
## --------------------------------------------------- ##

## -------------------------------------------------------------------------------- ##
## An alternative approach -- Keeping this code here for manual debugging/fixing    ##
## -------------------------------------------------------------------------------- ##
## An alternative approach is to download it all to disk, then read it from there.
## However, reading from disk does take a very long time given the current file setup
## 
    ## TODO:  Split up the /users/ folder into sub Folders:  112, 113, .., A, B, 
    if (FALSE) {
      sinkFile_reading_from_disk <- sinkOn()
      just_download_raw_json_and_save_to_disk(more_uris[[1]])
      sinkOff()
      showSink(file=sinkFile_reading_from_disk$filename)
    }
## -------------------------------------------------------------------------------- ##


## -------------------------------------------------------------------------------- ##
##        MAIN PART:   Download and create DT.playlist_metadata                     ##
##                                                                                  ##
##  * DOWNLOAD FILES                                                                ##
##  * PARSE                                                                         ##
##  * RBIND INTO A SINGLE DT.playlist_metadata                                      ##
##                                                                                  ##
## -------------------------------------------------------------------------------- ##
    sink_file <- sinkOn()
    {
      DT.playlist_metadata <- get_basic_playlist_metadata_from_list_of_uris(DT.uris_in$playlist_uri)

      ## ---- Manual Recovery -------------------------- ##
      ## If the above failed at the rbind step, we can read from disk
      ## This only works for now and will need to be cleaned up
      ## since this reads ALLLLLL the previously downloaded files
      ## not just the most recent
      if (FALSE || isErr(DT.playlist_metadata))
         DT.playlist_metadata <- read_and_parse_files_from_disk()
      ## ---- Manual Recovery -------------------------- ##
      
      jesusForData(DT.playlist_metadata, git=FALSE)
    }

    ## Slight difference in names for the actual table than the DT
    dict.nms_DT_to_tbl <- c(id="playlist_id", uri="playlist_uri", api_timestamp="api.timestamp")
## -------------------------------------------------------------------------------- ##


## -------------------------------------------------------------------------------- ##
## ----  INSERT the data and UPDATE the _unique Table ------------------------------ ##
## -------------------------------------------------------------------------------- ##
    ## If there is a warehouse already on, use that instead of botting one up
    wh %<>% getWH_already_on(default=.)
    setSnowflake(wh=wh, dbname=dbname, start=TRUE)

    ## CONFIRM ALL THE COLUMNS ARE AS EXPECTED
    if (qTableExists(tbl=tbl.out_metadata, schema=schema.out, wh=wh, snowflake_inuse=TRUE)) {
        existing_tbl.current_cols <- sfDesc(tbl=tbl.out_metadata, schema=schema.out, wh=wh, snowflake_inuse=TRUE, quiet=TRUE)[, tolower(name)]
        ## TODO: Apply dictionary.  The three columns that show up here are due to nmsToChange
        allSetDiff(sapply(existing_tbl.current_cols, function(x) ifelse(x %in% names(dict.nms_DT_to_tbl), dict.nms_DT_to_tbl[x], x)), names(DT.playlist_metadata), A.nm="Snowflake", B.nm="DT")
    }


    verboseMsg(verbose, "Inserting the data into", tbl.out_metadata)
    ingestIntoSQL(append=TRUE, DT=DT.playlist_metadata, tbl=tbl.out_metadata, schema=schema.out, wh=wh, dbname=dbname, snowflake_inuse=TRUE, dont_take_copy_of_DT=FALSE, datetime_type='TIMESTAMP_NTZ', comment_tbl="Data from Spotify Public API", nmsToChange=dict.nms_DT_to_tbl)

    verboseMsg(verbose, "Updating the '_unique' table")
    src.p("playlist_metadata_unique upsert.sql") %>% sqlFileToQry %>% sfQry(verbose=FALSE)
## -------------- ------------------------------------------------------------------ ##

try({
  notifyAndEmail("DONE: Spot_Metadata Table Creation on Snowflake")
})

sinkOff()
showSink()