# screen -xRR Views
setScience(proj="DeNormalizing", subProj="countryRankByStore", load=FALSE, subl=FALSE)
setGitBranchToSystem(); .g();

## Snowflake setup
## ----------------------- ##
  wh <- getWH_already_on(default="SCIENCE")
  dbname <- "prod"
  setSnowflake(wh=wh, dbname=dbname)
## ----------------------- ##

## Table that will be loaded
schema <- "bi"
tbl <- "country_store_rankings_2015todate"

storesUsing <- c(1, 286, 453, 187, 496, 348, 447, 376, 4, 7)
minDate <- as.Date("2015-01-01")
## Minimum threshold needed for country to be considered separately
thresh.percent_of_store <- 0.001

countryCols <- c("country_code", "country_name")
storeCols <- c("storeid", "store_name")

colsToAgg <- "gross_revenue_usd"
DT.gross_by_store <- makeQry(tbl="accounting", schema="bi", colsToAgg=colsToAgg, colsToPull=c(storeCols, countryCols), minDate=minDate, dateCol="activity_month") %>% sfQry()

## Manually fix some values                       ##
## ---------------------------------------------- ##
DT.gross_by_store[store_name == "iTunes/Apple",     store_name := "iTunes"]
DT.gross_by_store[store_name == "Rhapsody/Napster", store_name := "Rhapsody"]
## country name Namibia is an issue
DT.gross_by_store[country_name == "Namibia", country_code := "NA"]

## BACKUPS
jesusForData(DT.gross_by_store)
BackUpOrRestore("DT.gross_by_store", force=TRUE)


kCols <- countryCols


# DT.gross_by_store.aggd <- makeFormula(Left=countryCols, Right=setdiff(storeCols, "storeid")) %>% dcast.data.table(DT.gross_by_store[storeid %in% storesUsing], formula=., value.var=c("gross"), fun=sum)
DT.gross_by_store.aggd <- makeFormula(Left=countryCols, Right=storeCols) %>% dcast.data.table(DT.gross_by_store[storeid %in% storesUsing], formula=., value.var=colsToAgg, fun=sum)
# setnamesSpaceToUnderscore_(DT.gross_by_store.aggd)

## Confrim no underscore in the store_name.  If there IS one, then the strsplit (downstream) will fail
stopifnot(!grepl("_", DT.gross_by_store.aggd$store_name))

# ## Organize
# setcolorderpt(DT.gross_by_store.aggd, c(countryCols, "iTunes", "iTunes/Apple", "Spotify", "YouTube", "Google_Play", "Google Play", "Amazon", "Amazon_Video", "Amazon Video"), showWarnings=FALSE)

## These are the columns to take the rank off
measureCols <- setdiff(names(DT.gross_by_store.aggd), countryCols)

## Clear out any NAs
for (col in measureCols)
  DT.gross_by_store.aggd[get(col) == 0, (col) := NA]

## ----------------------------------------------------------- ##
## Create three separate tables, then rbind them ((1) Gross Rev, (2) Rank, (3) Percent of Store Total)
## ----------------------------------------------------------- ##
## GROSS REVENUE
DT.gross_by_store.aggd_gross <- copy(DT.gross_by_store.aggd)[, what := "gross"]

## RANKING
DT.gross_by_store.aggd_rank <- copy(DT.gross_by_store.aggd)[, what := "rank"]
DT.gross_by_store.aggd_rank[, (measureCols) := lapply(.SD, function(x) ifelse(is.na(x), 99999, 1.0 * rank(-x, na.last=TRUE, ties="min"))), .SDcols=measureCols]

## PERCENTAGE OF STORES TOTAL YTD REVENUE
DT.gross_by_store.aggd_perc <- copy(DT.gross_by_store.aggd)[, what := "percent_by_store"]
DT.gross_by_store.aggd_perc[, (measureCols) := lapply(.SD, percOfTotal, as.perc=FALSE), .SDcols=measureCols]

## Combine the tables, then melt, parse out store info, then recast
tmp_DT.aggd_gross_rank <- rbind(DT.gross_by_store.aggd_gross, DT.gross_by_store.aggd_rank, DT.gross_by_store.aggd_perc)

## Melt the table
tmp_DT.aggd_gross_rank <- data.table:::melt.data.table(tmp_DT.aggd_gross_rank, id.vars=c(countryCols, "what"), variable.factor=FALSE)
tmp_DT.aggd_gross_rank[, (storeCols) := as.data.table(do.call(rbind, strsplit(variable, "_")))]

DT.country_store_rankings_2015todate <- makeFormula(Left=c(storeCols, countryCols), Right="what") %>% dcast.data.table(tmp_DT.aggd_gross_rank, formula=., value.var=c("value"), fun=sum)

## Show an example
{
  catheader("EXAMPLE: iTunes Countries comprising at least .1% of total sales", prel=2, endl=0)
  print(DT.country_store_rankings_2015todate[storeid == 1 & percent_by_store >= 0.001][order(rank)])
  pasteR(100) %>% catn("", .)
}

## set key to store-country columns
setkeyIfNot(DT.country_store_rankings_2015todate, c(storeCols, countryCols), organize=TRUE, verbose=FALSE)
DT.country_store_rankings_2015todate

## Identify all countries which are above the threshold for at least one store in the list
top_countries <- DT.country_store_rankings_2015todate[percent_by_store >= thresh.percent_of_store, unique(country_code)]

## Show an example
{
  catheader("EXAMPLE: Avg rank per country (except for rank 99999", prel=2, endl=1)
  print(DT.country_store_rankings_2015todate[country_code %in% top_countries, list(floor(mean(rank[rank != 99999])),floor(mean(rank[storeid %in% c(1, 285) & rank != 99999]))), by=countryCols][order(V1)] %>% setnames(c("V1", "V2"), paste0("Avg_rank", c("_across_all_stores", "_itunes_and_spotify"))) %>% topropper)
  catn("TODO:  ... ?")
  pasteR(88) %>% catn("\t", .)
}

ingestIntoSQL(schema=schema, tbl=tbl, append=FALSE, add.ingestDate=TRUE, datetime_type="TIMESTAMP_TZ", snowflake=TRUE, wh="SCIENCE", verbose=TRUE)
jesusForData(DT.country_store_rankings_2015todate)
jesusForData(top_countries)

srcOther("Looker") %>% sourceSupportFns
create_lookml_from_tbl(schema=schema, tbl=tbl)

makeQry(schema=schema, tbl=tbl, aggFunc=NULL, colsToPull="*" , limit=NULL) %>% sfQry()


sfQry("SELECT * FROM production.dim_country WHERE countryname = 'Namibia'")



### ------------------------------------------------------------------------------------------------------------- ###
###        PART 2 - plotting all stores
### ------------------------------------------------------------------------------------------------------------- ###
if (.Pfm == "Darwin") {
  setScience(proj="DeNormalizing", subProj="countryRankByStore", load=FALSE, subl=FALSE)
  lib(ggplot2)
  setGitBranchToSystem(); .g();
  countryCols <- c("country_code", "country_name")
  storeCols <- c("storeid", "store_name")

  loadFromJesus("DT.gross_by_store")
  loadFromJesus("DT.country_store_rankings_2015todate")
  loadFromJesus("DT.country")
  loadFromJesus("top_countries")
  DT.total_store_gross_ytd <- DT.gross_by_store[, lapply(.SD, sumn), .SDcols="gross_revenue_usd", by=storeCols][order(gross_revenue_usd, decreasing=TRUE)]

  DT.gross_by_store_by_region_group <- copy(DT.gross_by_store)
  addColsFrom_(DT.gross_by_store_by_region_group, DT.country, colsToBring="broad_region_group", joinCols=c("country_code"))
  DT.gross_by_store_by_region_group <- aggregateDT(DT.gross_by_store_by_region_group, colsToAgg="gross_revenue_usd", by=setdiff(names(DT.gross_by_store_by_region_group), c("gross_revenue_usd", countryCols)))

  ## (1) Stores (aggregated total)
  ggplot(data=DT.total_store_gross_ytd, aes(x=gross_revenue_usd)) + geom_density(fill="dark grey", color="dark grey") + log.x() + labs(x="Total Gross Revenue USD (for each store)", title="Distribution of Stores, by Total Revenue")
  ## (2) Countries, by Store
  ggplot(data=DT.country_store_rankings_2015todate, aes(x=gross, fill=store_name)) + geom_density(alpha=0.1) + log.x() + labs(x="Gross Revenue USD (for each country)", title="Distribution of Countries, by Revenue")
  ## (3) Stores, by Region Group
  ggplot(data=DT.gross_by_store_by_region_group, aes(x=gross_revenue_usd, fill=broad_region_group)) + geom_density(alpha=0.1) + log.x() + labs(x="Gross Revenue USD (for each store)", title="Distribution of Stores, by Revenue, by Broad Region Group")
}
### ------------------------------------------------------------------------------------------------------------- ###