## magnesound deezer.r
ssh $AWS
screen -xRR deezer

#    labelid       labelname priority countryid    owner        last_updated        date_created isdistributor ownerid
# 1:   id.magnesound Magnesound S.L.        2        15 kdigital 2015-08-02 20:03:46 2013-08-01 00:13:16             N       9

setScience(proj="misc", subProj="Magnesound", load=FALSE)

deezer_add_country_info <- function(DT, not_ROW=c(6, 15)) {
  DT <- copy(DT)
  DT[countryid %ni% not_ROW, countryid := 00]
  addCountryInfo_(DT, colsToBring=c("country_code", "country_name"))
  DT[countryid == 0, `:=`(country_name = 'Rest of World', country_code = 'ROW')]
  DT[, countryid := NULL]
  DT[, storeid := NULL]
  DT <- aggregateDT(DT, colsToAgg=c("units"), exclude=NULL)

  ## set levels order
  DT[, country_code := setFactorOrder(country_code, ordering=c("ES", "FR"))]
  DT[, country_name := setFactorOrder(country_name, ordering=c("Spain", "France"))]

  return(DT)
}

id.magnesound <- 17132

known_missing_dates <- c("2014-11-15", "2014-12-14", "2014-12-24") %>% as.Date

## NEW:  2015-08-05
cluster <- 7
dateCol <- "download_activity_date"
minDate <- "2012-01-01"
minDate2 <- "2014-11-01"
maxDate2 <- "2014-12-31"


## SCRAP WORK 
# DT.magnesound <- makeQry(tbl="fact_analytics", colsToAgg=c(".RU", "freeunits"), colsToPull=c("storeid", month=sql1stOfMonth(dateCol)), labelid=id.magnesound, key="colsToPull", minDate=minDate, dateCol=dateCol) %>% 
#         runQry(cluster=cluster)
# 
# DT.magnesound_country <- makeQry(tbl="fact_analytics", colsToAgg=c(".RU", "freeunits"), colsToPull=c("storeid", "countryid", month=sql1stOfMonth(dateCol)), labelid=id.magnesound, storeid=c(286, 348, 424), key="colsToPull", minDate=minDate, dateCol=dateCol) %>% 
#         runQry(cluster=cluster)
# 
# jesusForData(DT.magnesound_country)
# addCountryInfo_(DT.magnesound_country)
# DT.magnesound_country[storeid == 348 & country_code %in% c("US", "ES", "FR")] %>% print(nrow=500)
# 
# ## Monthly, just for spain
# DT.magnesound_release_spain <- makeQry(tbl="fact_analytics", colsToAgg=c(".RU", "freeunits"), colsToPull=c("storeid", "countryid", "releaseid", month=sql1stOfMonth(dateCol)), labelid=id.magnesound, storeid=c(348), countryid=c(15), key="colsToPull", minDate=minDate, dateCol=dateCol) %>%
#          runQry(cluster=cluster)
# 
# ## Daily, just for spain.  Only units
# DT.magnesound_release_spain_daily <- makeQry(tbl="fact_analytics", colsToAgg=c(units="paidunits"), colsToPull=c("storeid", "countryid", "releaseid", date=dateCol), labelid=id.magnesound, storeid=c(348), countryid=c(15), key="colsToPull", minDate=minDate2, maxDate=maxDate2, dateCol=dateCol) %>% 
#         runQry(cluster=cluster)
# 
# jesusForData(DT.magnesound_release_spain_daily)
 

## Pull daily data for the
DT.magnesound_release_daily <- makeQry(tbl="fact_analytics", colsToAgg=c(units="paidunits"), colsToPull=c("storeid", "countryid", "releaseid", date=dateCol), labelid=id.magnesound, storeid=c(348), key="colsToPull", minDate=minDate2, maxDate=maxDate2, dateCol=dateCol) %>% 
        runQry(cluster=cluster)

jesusForData(DT.magnesound_release_daily)

## Set blank dates to NA
DT.magnesound_release_daily[date %in% known_missing_dates, units := NA]

## Only keep Spain & France. Other countries become ROW and aggregate out
DT.magnesound_release_daily <- deezer_add_country_info(DT.magnesound_release_daily)

## add release meta
DT.releases <- runQry(paste("SELECT releaseid, releasename as release_name, labelid FROM dim_release WHERE releaseid in ", DT.magnesound_release_daily[, unique(releaseid) %>% pasteQ(q="")]))
addColsFrom_(DT.magnesound_release_daily, DT.releases, joinCols=c("releaseid"))

## clean up id col
DT.magnesound_release_daily[, releaseid := as.idcol(releaseid)]

## KEY COLUMNS
kCols <- c("releaseid", "release_name", "labelid", "country_code", "country_name", "date")
setkeyIfNot(DT.magnesound_release_daily, kCols, organize=TRUE, verbose=FALSE)

## Add rank info for the release
DT.magnesound_release_daily[DT.magnesound_release_daily[, sumn(units), keyby=releaseid][, list(releaseid, rank_for_release = rank(-V1))], rank_for_release := rank_for_release]
setkeyIfNot(DT.magnesound_release_daily, c("rank_for_release", kCols), organize=TRUE, verbose=FALSE)
DT.magnesound_release_daily

## Pump in any missing dates for each release
DT.magnesound_release_daily <- DT.magnesound_release_daily[CJ_allDatesByCols(DT.magnesound_release_daily, by=kCols, universal.range=TRUE, interval="day")]

## SAVE THE TOP 
jesusForData(DT.magnesound_release_daily, info="withMetaData")
writeDT(DT.magnesound_release_daily, to=getRS("to"), subject="Magnesound daily breakdowns BY RELEASE")

## Aggregate out the release info 
DT.magnesound_aggregated_daily <- aggregateDT(DT.magnesound_release_daily, colsToAgg=c("units"), exclude=c("releaseid", "release_name", "labelid", "rank_for_release"))
jesusForData(DT.magnesound_aggregated_daily, info="withMetaData")
writeDT(DT.magnesound_aggregated_daily, to=getRS("to"), subject="Magnesound daily breakdowns, aggregated.")

## ~~~~~~~ PLOT IT ~~~~~~~~~ ##
lib(ggplot)
library(ggplot2, )
ggLinegraph(DT.magnesound_aggregated_daily, x="date", y="units", facet_formula="country_name ~ .", facet_scale="free", yscale=NULL, title="Deezer Daily Activity for Magnesound")


########### ----- DAILY ---------- ###########
########    ----- DAILY ----------    ########
## ======== ----- DAILY ---------- ======== ##

  &&& - UPDATE: Note that the majority of the daily downlaods ARE magnesound.  Therefore, it is NOT across the board

  DT.deezer_daily <- makeQry(tbl="fact_analytics", colsToAgg=c(units="paidunits"), colsToPull=c("storeid", countryid = "CASE countryid WHEN 6 then 6 when 15 then 15 else 0 end", date=dateCol), storeid=c(348), key="colsToPull", minDate=minDate2, maxDate=maxDate2, dateCol=dateCol) %>%  runQry(cluster=cluster)
  DT.deezer_daily <- DT.deezer_daily[CJ_allDatesByCols(DT.deezer_daily, universal.range=TRUE, interval="day")]
  DT.deezer_daily[date %in% known_missing_dates, units := NA]
  DT.deezer_daily <- deezer_add_country_info(DT.deezer_daily)

  jesusForData(DT.deezer_daily)

  Title <- "Deezer Activity, aggregated for ALL Labels\nNote in Spain there is a 60% drop\nfrom Nov 16 to Nov 17 2014\n(Data is missing for Nov 15)"
  DT.deezer_daily[date >"2014-11-12" & date  <= "2014-11-18"]
  P.Deezer_All_Labels <- ggLinegraph(DT.deezer_daily, x="date", y="units", facet_formula="country_name ~ .", facet_scale="free", title=Title)

  Title <- "Deezer Activity for Magnesound\nNote in Spain there is a sudden drop\nfrom Nov 16 to Nov 17 2014\n(Data is missing for Nov 15)"
  DT.magnesound_aggregated_daily[date >"2014-11-12" & date  <= "2014-11-18"]
  P.Deezer_Magnesound <- ggLinegraph(DT.magnesound_aggregated_daily, x="date", y="units", facet_formula="country_name ~ .", facet_scale="free", title=Title)

  f.deezer_magnesound <- ggsave.out(plot=P.Deezer_Magnesound)
  f.deezer_all_labels <- ggsave.out(plot=P.Deezer_All_Labels)

## ======== ----- DAILY ---------- ======== ##
########    ----- DAILY ----------    ########
########### ----- DAILY ---------- ###########


########### ----- OTHER LABELS ---------- ###########
########    ----- OTHER LABELS ----------    ########
## ======== ----- OTHER LABELS ---------- ======== ##
  DT.deezer_all_labels_in_spain_daily <- makeQry(tbl="fact_analytics", colsToAgg=c(units="paidunits"), colsToPull=c("storeid", "labelid", countryid = "CASE countryid WHEN 6 then 6 when 15 then 15 else 0 end", date=dateCol), storeid=c(348), key="colsToPull", minDate=minDate2, maxDate=maxDate2, dateCol=dateCol) %>%  runQry(cluster=cluster)
  DT.deezer_all_labels_in_spain_daily <- DT.deezer_all_labels_in_spain_daily[CJ_allDatesByCols(DT.deezer_all_labels_in_spain_daily, universal.range=TRUE, interval="day")]
  DT.deezer_all_labels_in_spain_daily[date %in% known_missing_dates, units := NA]

  ## Set all non-top labels to -1, then aggregate
  top_labels_in_spain <- DT.deezer_all_labels_in_spain_daily[country_code == "ES", sum(units), keyby=labelid][order(V1, decreasing=TRUE)][1:25, labelid]
  DT.deezer_all_labels_in_spain_daily[labelid %ni% top_labels_in_spain, labelid := -1]

  ## Add country info then aggregate
  DT.deezer_all_labels_in_spain_daily <- deezer_add_country_info(DT.deezer_all_labels_in_spain_daily)

  ## add label metadata
  addColsFrom_(DT.deezer_all_labels_in_spain_daily, get_dim_label(), joinCols="labelid", colsToBring=c("label_name"))
  DT.deezer_all_labels_in_spain_daily[labelid == -1, label_name := "all other labels combined"]

  ## levels, to organize labels
  setkeyIfNot(DT.deezer_all_labels_in_spain_daily, labelid, superset.ok=TRUE, verbose=FALSE)
  levs <- DT.deezer_all_labels_in_spain_daily[.(top_labels_in_spain), unique(label_name) %>% as.character %>% c("all other labels combined")]
  DT.deezer_all_labels_in_spain_daily[, label_name := setFactorOrder(label_name, levs)]

  ## key
  setkeyIfNot(DT.deezer_all_labels_in_spain_daily, setdiff(names(DT.deezer_all_labels_in_spain_daily), "units"), verbose=FALSE)

  ## Reshape
  DT.deezer_all_labels_in_spain_daily.dcast <- DT.deezer_all_labels_in_spain_daily[country_code == "ES" & date >= "2014-11-12" & date <= "2014-11-19", as.data.table(rbind(setNames(nm=date, obj=units))), keyby=list(labelid, label_name)]
  DT.deezer_all_labels_in_spain_daily.dcast[, prec.16_to_17th := ((`2014-11-16` - `2014-11-17`) / `2014-11-16`)]
  setcolorderpt(DT.deezer_all_labels_in_spain_daily.dcast, c("labelid", "label_name", "prec.16_to_17tha"))
  setkeyIfNot(DT.deezer_all_labels_in_spain_daily.dcast, "label_name", verbose=FALSE, organize=FALSE)
  writeDT(DT.deezer_all_labels_in_spain_daily.dcast, to=getRS())

## ======== ----- OTHER LABELS ---------- ======== ##
########    ----- OTHER LABELS ----------    ########
########### ----- OTHER LABELS ---------- ###########