.us(); 
lib(reshape2,  quietly=TRUE)
lib(ggplot,    quietly=TRUE)
lib(Hmisc,     quietly=TRUE)

setScience("Spotify_Stream_Count", create=TRUE, load=FALSE, subl=FALSE)

saveing <- TRUE

minDate <- monthCeiling(today() - 125)
dateCol <- "download_date"
kCols.spot <- c("labelid", "tier")


DT.SpotifyCounts <- makeQry(tbl="staging_raw_spotify_v2", colsToPull=c("product", releaseid="upc", month=sql1stOfMonth(dateCol)), colsToAgg=c(streams="*"), aggFunc="count", dateCol=dateCol, minDate=minDate) %>% runQry(verbose=TRUE, all.pfm=TRUE)


rm(Q.release_to_label)
(Q.release_to_label <- makeQry("release", c("releaseid", "labelid")) )
DT.release_to_label <- runQry(Q.release_to_label)

matchKey(DT.SpotifyCounts, DT.release_to_label, "releaseid")
DT.SpotifyCounts[DT.release_to_label, labelid := i.labelid]

DT.SpotifyCounts[, tier := getDict.SpotifyTier()[product]]

setkeyIfNot(DT.SpotifyCounts, kCols.spot, organize=TRUE)
DT.SpotifyCounts[, label_streams_per_tier := sum(streams), by=kCols.spot]

(DT.SpotifyCounts_labels <- unique(DT.SpotifyCounts, by=kCols.spot)[, c("releaseid", "streams", "product") := NULL][tier == "LimitedTier", tier := "PremiumTier"][, lapply(.SD, sum), keyby=kCols.spot, .SD="label_streams_per_tier"])

DT.SpotifyCounts_labels.casted <- dcast.data.table(DT.SpotifyCounts_labels[DT.SpotifyCounts_labels[, sum(label_streams_per_tier), keyby=labelid][V1>1, .N, keyby=labelid]], formula=(labelid ~ tier), fun.aggregate=sum, value.var="label_streams_per_tier")

DT.SpotifyCounts_labels.casted[, total_streams := (FreeTier + PremiumTier)]
DT.SpotifyCounts_labels.casted[, ratio_of_free := FreeTier / total_streams]

if (saveing) {
  print(jesusForData(DT.SpotifyCounts_labels.casted,  verbose=FALSE))
  print(jesusForData(DT.SpotifyCounts_labels,         verbose=FALSE))
  print(jesusForData(DT.SpotifyCounts,                verbose=FALSE))
  print(jesusForData(DT.release_to_label,             verbose=FALSE))
  saveImageTo()
}



topPerc <- .9

## IDentify Top Percent
DT.SpotifyCounts_labels.casted[, isTopPerc := (PremiumTier > quantile(PremiumTier, topPerc)) | (FreeTier > quantile(FreeTier, topPerc))]

## CUTS
cuts <- 10^seq(0, 8, by=1)
cuts.nm <- ifelse(cuts >= 1e6, paste(fwS(cuts / 1e6, align="right"), "M"), ifelse(cuts >= 1e3, paste(fwS(cuts / 1e3, align="right"), "K"), fwS(cuts, align="right")))

## Apply Cuts
DT.SpotifyCounts_labels.casted[(isTopPerc), AdSupported := cut(FreeTier, breaks=c(0,cuts), labels=cuts.nm)]
DT.SpotifyCounts_labels.casted[(isTopPerc), Premium        := cut(PremiumTier, breaks=c(0,cuts), labels=cuts.nm)]
DT.SpotifyCounts_labels.casted[(isTopPerc), TotalStreams   := cut(PremiumTier, breaks=c(0,cuts), labels=cuts.nm)]

title <- subtext("Ratio of Ad-Supported to Premium Streams", "by Label")
yl <- "<<< Mostly Premium <<<<             RATIO             >>>> Mostly Ad-Supported >>>"
xl <- ""
xVal <- sprintf("All Orchard-distributed Labels having Spotify Activity since %s", humanDate(minDate, wday=FALSE))

P.streams_ratio <- {set.seed(1); ggplot(data=DT.SpotifyCounts_labels.casted) + geom_boxplot(aes(x=xVal, y=ratio_of_free)) + xlab(xl) + ylab(yl) + ggtitle(title) + geom_jitter(data=DT.SpotifyCounts_labels.casted[(isTopPerc) ], aes(x=xVal, y=ratio_of_free, shape=Premium, color=AdSupported, size=TotalStreams), alpha=.5)  + scale_size_manual(values=c(.6, .9, 1.25, 2.5, 5, 8, 12, 20)) + scale_shape_manual(values=c(3, 18, 17, 15, 19, 8, 3)) + percent.y()}

ggsave.out(P.streams_ratio, height=10)

dir(as.path(outDir, "plots"))
.o(outDir)