# screen -xRR LongTail

setScience(proj="Spotify_Long_Tail", subl=FALSE, load=FALSE)
setGitBranchToSystem(); .g()

file.dl <- "https://dl.dropboxusercontent.com/u/86262175/dave%20live%20list_20150824.csv"
file.live_list <- ingest.p("spotify_live_list_.csv")

sprintf("wget %s %s", file.dl, file.live_list) %>% system()
as.path(getwd(), "dave live list_20150824.csv") %>% file.rename(file.live_list)


file.exists(file.live_list)
DT.live_list <- fread(file.live_list, sep=",", header=TRUE)
cleanColNamesForSQL_(DT.live_list)

names(DT.live_list)

wh <- "LOOKER_WH_LARGE"
dbname <- "prod"

schema <- "spotify"
tbl <- "live_list"

stage_name <- "spotify_live_list"

## RUN THIS ONE TIME
if (FALSE)
{
  format_name <- "csv"
  delim <- ","
  use_gzip <- FALSE

  sfCreateFormatAndStage(stage_name=stage_name, format_name=format_name, header=FALSE, delimiter=delim, gzip=use_gzip, overwrite=TRUE, quote_string='"', trim_space=FALSE, create_schema=TRUE
  # , comment_format="For ingesting data dumped from Redshift using unloadQry() or unloadTableToS3Bucket()"
  , comment_stage="For exports of Spotify Carveout reports pulled by Ops from Spotify."
  , wh=wh, dbname=dbname, schema=schema
  )
}

Qry.create <- makeSQLtable(DT.live_list, datetime_type="TIMESTAMP_NTZ", table.name=tbl, schema=schema, dbname=dbname, create_only=TRUE)
sfQry(Qry.create, wh=wh)

sfPopulateTable(tbl=tbl, schema=schema, file_name=file.live_list, stage_name=stage_name, dbname=dbname)

headDB(tbl=tbl, schema=schema)

qry.create_by_country <- 
"CREATE TABLE prod.spotify.live_list_by_country AS 
(
  SELECT C.value::string AS country_code, upc, ean, live, live_from, live_until,  inserted, updated, type
  FROM prod.spotify.live_list,
       LATERAL FLATTEN(input=>split(trim(countries), ' ')) C
)
"

sfQry(qry.create_by_country)
headDB("prod.spotify.live_list_by_country")


----------------------------------------------------------------------------------
loadFromJesus("~rsaporta/gitData/orch/data/Spotify_Accounting_ETL/DT.spotify_raw-20150825_1020-360x3.RDS")

DT.summer_trend_2014 <- runQry(cluster=4, 
"SELECT download_activity_date as activity_date, sum(paidunits) AS streams
FROM production.fact_analytics
WHERE storeid = 286 AND dayid >= 5115
  AND ((extract('year' from download_activity_date)::integer = 2014 AND (extract('month' from download_activity_date)::integer >= 2 AND extract('month' from download_activity_date)::integer <= 10)) OR download_activity_date is NULL)
GROUP BY 1
ORDER BY 1
")
f.summer_trend_2014 <- jesusForData(DT.summer_trend_2014)
f.summer_trend_2014
DT.summer_trend_2015 <-  DT.spotify_raw[year(activity_date) == 2015 & month(activity_date) >= 2 & month(activity_date) <=10, list(streams=sum(streams), year=2015), keyby=activity_date %m-% months(12)][, streams_normalized := scale(streams)]
DT.summer_trend_2015
DT.summer_trend_2014[, year := year(activity_date)]
DT.summer_trend_2014[, streams_normalized := scale(streams)]

P.spotify_summer_trend <- ggLinegraph(rbind(DT.summer_trend_2014, DT.summer_trend_2015)[, year := factor(year)], x="activity_date", y="streams_normalized", color="year", title="Spotify SUMMER TREND comparison: 2014 vs 2015")
ggsave.out(P.spotify_summer_trend)

----------------------------------------------------------------------------------
DT.summer_trend <- runQry(cluster=4, 
"SELECT dayid, download_activity_date as activity_date, sum(paidunits) AS paidunits
FROM production.fact_analytics
WHERE storeid = 286 AND dayid >= 5115
  AND (((extract('year' from download_activity_date)::integer = 2014 OR  extract('year' from download_activity_date)::integer = 2015) AND (extract('month' from download_activity_date)::integer >= 2 AND extract('month' from download_activity_date)::integer <= 10)) OR download_activity_date is NULL)
GROUP BY 1, 2
ORDER BY 1
")

jesusForData(DT.summer_trend)
loadFromJesus("~rsaporta/git/orch/data/Spotify_Long_Tail/DT.summer_trend-20150825_1003-268x3.RDS")

DT.summer_trend[, year := factor(year(activity_date))]
ggLinegraph(DT.summer_trend, x="activity_date", y="paidunits", color="year")
DT.summer_trend[, date := as.Date(ifelse(year == 2014, activity_date, activity_date %m-% months(12)), origin=.origin)]
DT.summer_trend[, streams_normalized := scale(paidunits), by=year]

ggLinegraph(DT.summer_trend, x="date", y="streams_normalized", color="year", alpha=0.6)

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


headDB("staging_raw_spotify_v2", schema="production")



Just a quick sum by release id


DT.counts_by_release <- sfQry(
"SELECT releaseid, sum(paidunits) as streams
FROM production.fact_analytics
where storeid = 286
AND download_activity_date >= '2014-08-01'
AND download_activity_date <= '2015-07-31'
GROUP BY 1
ORDER BY 1
"
)

jesusForData(DT.counts_by_release)

DT.counts_by_release[, releaseid := as.idcol(releaseid)]
upcs_all <- DT.live_list[, as.idcol(unique(trim(c(upc, ean))))]
upcs_missing <- setdiff(upcs_all, DT.counts_by_release$releaseid)
length(upcs_missing) / length(upcs_all)
length(upcs_all)
DT.counts_by_release_with_zero <- rbind(DT.counts_by_release, data.table(releaseid=upcs_all, streams=0))


breaks <-  (10^(0:10)) %>% sapply("*", c(1,5)) %>% c(-Inf, -1, 0) %>% sort %>% selfname_
levs <- c()
for (i in seq(length(breaks)-1))
  levs <- c(levs, {c(list(fmt="%s - %s"), as.list(formatK(breaks[c(i, i+1)])) ) %>% do.call(sprintf, .)})
levs[levs == "0 - 1"] <- "0"

DT.counts_by_release_with_zero[, streams_count := cut2(streams, cuts=breaks)]
changeLevels(DT.counts_by_release_with_zero, "streams_count", newlevs=levs)

jesusForData(DT.counts_by_release_with_zero)

f <- DT.longtail_tally <- writeDT(DT.tally, to=getRS())

DT.tally <- DT.counts_by_release_with_zero[, list(number_of_releases=.N), keyby=streams_count]
DT.tally[, streamed_during_timeframe := ifelse(streams_count == "0", "Never", "At Least Once")]
DT.tally[, perc.of_all_releases := percOfTotal(number_of_releases)]

TITLE <- "Orchard Releases that are live on Spotify\n Grouped by the number of streams each release received in the last twelve months"
P.Spotify_releases_without_streams <- ggBarchart2(DT.tally, x="streams_count", y="number_of_releases", fill="streamed_during_timeframe", legend="bottom", ylab="Number of Releases (in Thousands)", title=TITLE) + angledtext(x=20, y=0) + thousands.y()
P.Spotify_releases_without_streams <- 
  ggBarchart2(DT.tally, x="streams_count", y="perc.of_all_releases", fill="streamed_during_timeframe", legend="bottom", ylab="Number of Releases (Percent of All Releases Live on Spotify)", title=TITLE) + angledtext(x=20, y=0) + percent.y()

ggsave(P.Spotify_releases_without_streams, width=10, height=10, file= "~/git/orch/plots/Spotify_Long_Tail/Spotify_releases_without_streams.png")
reveal("~/git/orch/plots/Spotify_Long_Tail/Spotify_releases_without_streams.png")
ggsave.out(P.Spotify_releases_without_streams, width=10, height=10, open=TRUE)


.myf(angled)
ggLinegraph

Add in all releases...!

lib(Hmisc)
cut2(0:21, breaks)
cut2(0:21, breaks, levels.mean=FALSE) %>% 
.myf(levels)

(0:21, breaks, levels.mean=TRUE) %>% table

DT[, stream_count := cut2(streams, breaks)]
DT.tally <- DT[, .N, keyby = stream_count]