setScience(proj="ContentAnalysis", subProj="RawDataIngestion", create=TRUE, subl=TRUE)

## This is the subfolder containing the split files
## If in the future we get an updated set of files, we should add it to a new subfolder
subFolder <- "tracks_live_as_of_20151124"

## Folder where the split files are located
folder <- ingest.p("Spotify Deliveries", subFolder)
stopifnot(file.exists(folder), isdir(folder))

## Bucket to where to update the files of what spotify has on file
bucket <- s3_p("spotify", subFolder)


--------------------  vvvvv TODO vvvvv ---------------
files are currently in the bucket -- get them into Snowflake!!
    -------------------------------------
      s3_ls(bucket)
      create file format
      create table
      load into snowflake
--------------------  ^^^^ TODO ^^^^ ---------------


files <- extractFilesFromFolder(folder, ext="csv", full=TRUE)
DT <- fread(files[[1]], nrow=20000)
desc(DT)

qry.create <- 
"
CREATE TABLE prod.spotify.tracks_live
(
  licensor VARCHAR(255),
  live VARCHAR(2),
  live_from TIMESTAMP_TZ,
  live_until TIMESTAMP_TZ,
  isrc VARCHAR(32),
  inserted TIMESTAMP_TZ,
  updated TIMESTAMP_TZ,
  track_uri VARCHAR(255)
)
"

qry.file_format <- 
"
CREATE OR REPLACE FILE FORMAT prod.public.spotify_live_tracks TYPE = 'CSV'
   FIELD_DELIMITER = ','
   RECORD_DELIMITER = '\\n'
   SKIP_HEADER = 1
   DATE_FORMAT = 'AUTO'
   TIMESTAMP_FORMAT = 'AUTO'
   -- TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF Z'
   ESCAPE = 'NONE'
   ESCAPE_UNENCLOSED_FIELD = 'NONE'
   TRIM_SPACE = FALSE
   FIELD_OPTIONALLY_ENCLOSED_BY = 'NONE'
   NULL_IF = ('')
   ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE
;
"

## QUESTIONS
(1) How can I specify that the time stamp columns are in UTC 
if they do not have a time zone indicated
(2) Can I have ignore the first column in every file? 
(3) There is a column that is 'Y'/'N'; Can it be converted to 1/0 on load?
(4) Can I load 

setSnowflake(wh="LOOKER_WH_LARGE")

## Create Table
sfQry(qry.create)

## Create File Format
sfQry(qry.file_format)

## Create Stage
sfCreateStage(overwrite_if_exists=TRUE, stage_name="spotify_live_tracks", bucket=bucket, format_name="prod.public.spotify_live_tracks", comment="For loading into Spotify.spotify_live_tracks from CSVs sent\ndirectly from Spotify")

(DT.validate <- sfPopulateTable(validation_mode=TRUE, tbl="tracks_live", schema="spotify", dbname="prod", bucket=bucket, stage_name="prod.public.spotify_live_tracks", format_name="prod.public.spotify_live_tracks"))
(DT.ret <- sfPopulateTable(tbl="tracks_live", schema="spotify", dbname="prod", bucket=bucket, stage_name="prod.public.spotify_live_tracks", format_name="prod.public.spotify_live_tracks"))

sourceSupportFns(proj="Looker")
create_lookml_from_tbl(tbl="tracks_live", schema="spotify", dbname="prod", wh=wh)