# Spotify_Raw_Analytics_Files.r


"This file brings over the spotify archive files from the S3 bucket to the HDD on the R Box"



setScience("Spotify_Accounting_ETL", load=FALSE, subl=FALSE, create=TRUE)
startAWStools()

localFolder <- data.p("Spotify_Analytics_S3_Archive/")
minDate <- "2014-04-01"
maxDate <- "2014-07-31"


## Location of Archive Files
bucket.spot_arch <- s3_p("Spotify", "archives", prefix="", root="cucumbers")

## Get all folders in archive bucket
SpotifyArchive.Folders <- as.data.table(suppressWarnings(s3_ls(bucket.spot_arch)))
setnames(SpotifyArchive.Folders, c("type", "folder"))

## Grab July's Folders
SpotifyArchive.Folders[, activity_date := gsub(".*/", "", gsub("/$", "", folder))]
SpotifyArchive.Folders[, activity_date := as.Date(activity_date, format="%Y%m%d")]

## Only keep files within date range
SpotifyArchive.Folders <- SpotifyArchive.Folders[activity_date >= minDate & activity_date <= maxDate]

SpotifyArchive.Folders[, activity_month_date := as.Date(format(activity_date, "%Y-%m-01"))]

## CONFIRM: Exactly one file per month
stopifnot(SpotifyArchive.Folders[, equals0(.N - daysInMonth(activity_month_date)), by=activity_month_date][, V1])

## Grab the list of files (bucket) in each parent folder
SpotifyArchive.Files <- SpotifyArchive.Folders[, as.data.table(s3_ls(folder)), by=list(folder, activity_date, activity_month_date)]
setnames(SpotifyArchive.Files, "bucket", "file.fullpath")
SpotifyArchive.Files[, localFile := gsub(bucket.spot_arch, paste0(localFolder, "/"), file.fullpath)]
jesusForData(SpotifyArchive.Files, info=sprintf("activity %s to %s.", minDate, maxDate))

## Pull the local files
SpotifyArchive.Files[, s3_download(file.fullpath, localFile, folder=NULL), by=activity_date]

## Confim they are present
stopifnot(SpotifyArchive.Files[, file.exists(localFile)])

## Size info
SpotifyArchive.Files[, size.localFile := file.info(localFile)$size]
setnames(SpotifyArchive.Files, "size", "size.remoteFile")

SpotifyArchive.Files[, filename := gsub(".*/", "", file.fullpath)]
SpotifyArchive.Files[, filetype := gsub("_.*", "", filename)]
SpotifyArchive.Files[, activitydate := gsub("([a-z]+?_)|([_A-Z]*?\\.gz)", "", filename)]
SpotifyArchive.Files[, activitydate := as.Date(activitydate, format="%Y%m%d", origin=.origin)]
stopifnot(SpotifyArchive.Files[, !is.na(SpotifyArchive.Files)])


if (!"rowCoun" %chin% names(SpotifyArchive.Files))
  SpotifyArchive.Files[, rowCount := fRowcount(localFile, verbose=FALSE), by=filename]
else 
  rm(DT.RowCounts)

DT.RowCounts <- SpotifyArchive.Files[filetype=="streams", list("Total Monthly Units"=formnumb(sum(rowCount))), keyby=list("Activity Month" = format(activity_month_date, "%B"))]
DT.RowCounts[, "Activity Month"  := factor(`Activity Month`, levels=month.name)]
setkeyIfNot(DT.RowCounts)

f.out.rowcounts <- out.p("spotify_raw_row_counts", ext=".tsv")
write.table(DT.RowCounts, file=f.out.rowcounts, col.names=TRUE, row.names=FALSE, sep="\t")
.o(f.out.rowcounts)
##-----
DT.RowCounts.byday <- SpotifyArchive.Files[filetype=="streams", list("Total Monthly Units"=formnumb(sum(rowCount))), keyby=list(activitydate)]
f.out.rowcounts.byday <- out.p("spotify_raw_row_counts_by_day", ext=".tsv")
write.table(DT.RowCounts, file=f.out.rowcounts.byday, col.names=TRUE, row.names=FALSE, sep="\t")
.o(f.out.rowcounts.byday)



===================


DT.spotify_fact_analytics <- runQry(makeQry("fact_analytics", where=list(storeid=286), colsToPull=c("Activity_Month"=sql1stOfMonth("download_activity_date")), colsToAgg=c(rows="count(*)"), minDate="2014-04-01", dateCol="download_activity_date"))

setkey(DT.spotify_fact_analytics, activity_month)
DT.spotify_fact_analytics[, "Activity Month"  := factor(format(activity_month, "%B"), levels=month.name) ]
setcolorderpt(DT.spotify_fact_analytics, c("Activity Month", "rows"))
DT.spotify_fact_analytics[, rows := formnumb(rows)]

f.out.fact_analytics <- out.p("spotify_fact_analytics_count", ext=".tsv")
write.table(DT.spotify_fact_analytics, file=f.out.fact_analytics, col.names=TRUE, row.names=FALSE, sep="\t")
.o(f.out.fact_analytics)

--------------------------

DT.spotify_gpu <- runQry("SELECT download_accounting_month, transac_typeid, gpu_is_estimate, gpu_using from bi.gpu where storeid = 286 order by 1, 2, 3")