test_countries <- c('DE', 'CH', 'US', 'BR', 'GB', 'MX', 'SE')

setScienceIfNot("Spotify_Accounting_ETL", subProj="BizReview", load=FALSE)
loadIfNotExists("DT.streams_from_raw_analytics")

wh <- "LOOKER_WH_LARGE"
setSnowflake(wh=wh, dbname="prod")
---------------------------

tbls <- list (
    RAW = "staging_raw_spotify_v2"
  ,  BI = "analytics"
  ,  FA = "fact_analytics"
)

schemas <- list(RAW="production", BI="bi", FA="production")
dateCols <- list(RAW="tmstamp::date", BI="activity_date", FA="download_activity_date")
colsToAggs <- list(RAW="count(*)", BI="units", FA="paidunits")
joins  <- list(RAW=NULL, BI=NULL, FA="production.dim_country DC ON FA.countryid = DC.countryid ")
country_codes  <- list(RAW="user_country", BI="country_code", FA="country_code")
storeids  <- list(RAW=NULL, BI=286, FA=286)
otherCols  <- list(RAW=NULL, BI=c("label_sc_group", "from_errors_table"), FA=NULL)

minDate <- "2015-08-01"
maxDate <- "2015-08-13"

# ---------------------------

mapply(qDateInfo, tbl=tbls, schema=schemas, whereIn=lapply(storeids, function(x) setNames(obj=list(x), nm="storeid")), SIMPLIFY=FALSE, verbose=TRUE)

list_DTs <- emptylist(tbls)
for (nm in names(tbls)) {
  if (length(list_DTs[[nm]]) && nrow(list_DTs[[nm]]))
    next;

  dateCol <- c(date = dateCols[[nm]])
  list_DTs[[nm]] <-
    makeQry(tbl=unlist(tbls[nm]), schema=schemas[[nm]], colsToAgg=c(streams=colsToAggs[[nm]]), colsToPull=c(dateCol, "country_code"=country_codes[[nm]], otherCols[[nm]]), storeid=storeids[[nm]], where=setNames(list(test_countries), nm=country_codes[[nm]]), minDate="2015-08-01", maxDate=maxDate, dateCol=dateCol, prependCols.with.tbl=FALSE, join=joins[[nm]]) %>%
     sfQry(verbose=TRUE)
}


DT.row_compre <- lapply(names(list_DTs), function(nm) list_DTs[[nm]][, setNames(nm=nm, obj=list(sumn(streams))), keyby=date]) %>% merge_list_DTs()
DT.row_compre[, perc.FA_to_RAW := fwp((FA-RAW) / RAW, 3)]
DT.row_compre[, perc.BI_to_RAW := fwp((BI-RAW) / RAW, 3)]
DT.row_compre[, perc.BI_to_FA  := fwp((BI- FA) / FA, 3)]


SELECT MIN(download_activity_date) as min_download_activity_date
      , MAX(download_activity_date) as max_download_activity_date
      , MIN(dayid) as min_dayid
      , MAX(dayid) as max_dayid
FROM   production.fact_analytics
WHERE  (storeid=286)

sfQry("SELECT download_activity_date, dayid, storeid from production.fact_analytics where dayid BETWEEN 5800 AND 5870 limit 5")


qBI <- makeQry(tbl="analytics", schema="bi", colsToAgg=c(streams="units"), colsToPull=c(date=dateCol, "country_code", "label_sc_group"), storeid = 286, country_code=test_countries, minDate="2015-08-01", dateCol=dateCol)

}
qBI <- makeQry(tbl="analytics", schema="bi", colsToAgg=c(streams="units"), colsToPull=c(date=dateCol, "country_code", "label_sc_group"), storeid = 286, country_code=test_countries, minDate="2015-08-01", dateCol=dateCol)
DT.bi_anal <- sfQry(qBI)

dateCol

DT.plot.combined <- DT.streams_from_raw_analytics[activity_date >= "2015-08-01" & activity_date <= "2015-08-12" & country_code %in% test_countries, lapply(.SD, function(x) sumn(as.numeric(x))), keyby=list(activity_date), .SDcols="streams"]
ggLinegraph(DT.plot.combined, x="activity_date", y="streams", title="STAGING_RAW_SPOTIFY_V2 - test countries")

DT.plot.by_country <- DT.streams_from_raw_analytics[country_code %in% test_countries, lapply(.SD, function(x) sumn(as.numeric(x))), keyby=list(activity_date, country_code), .SDcols="streams"]
ggLinegraph(DT.plot.by_country[country_code != "US" & streams > 3e8], x="activity_date", y="streams", color="country_code", vline_value=c("2015-08-12", "2015-08-13"), title="TEST COUNTRIES ONLY")

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


dateCol <- "activity_date"
qBI <- makeQry(tbl="analytics", schema="bi", colsToAgg=c(streams="units"), colsToPull=c(date=dateCol, "country_code", "label_sc_group"), storeid = 286, country_code=test_countries, minDate="2015-08-01", dateCol=dateCol)
DT.bi_anal <- sfQry(qBI)

dateCol

DT.plot.combined <- DT.streams_from_raw_analytics[activity_date >= "2015-08-01" & activity_date <= "2015-08-12" & country_code %in% test_countries, lapply(.SD, function(x) sumn(as.numeric(x))), keyby=list(activity_date), .SDcols="streams"]
ggLinegraph(DT.plot.combined, x="activity_date", y="streams", title="STAGING_RAW_SPOTIFY_V2 - test countries")

DT.plot.by_country <- DT.streams_from_raw_analytics[country_code %in% test_countries, lapply(.SD, function(x) sumn(as.numeric(x))), keyby=list(activity_date, country_code), .SDcols="streams"]
ggLinegraph(DT.plot.by_country[country_code != "US" & streams > 3e8], x="activity_date", y="streams", color="country_code", vline_value=c("2015-08-12", "2015-08-13"), title="TEST COUNTRIES ONLY")

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

dateCol <- "activity_date"
qBI <- makeQry(tbl="analytics", schema="bi", colsToAgg=c(streams="units"), colsToPull=c(dateCol, "country_code", "label_sc_group"), storeid = 286, country_code=test_countries, minDate="2015-08-01", dateCol=dateCol)
DT.bi_anal <- sfQry(qBI)

jesusForData(DT.bi_anal)

DT.plot.combined <- DT.bi_anal[activity_date >= "2015-08-01" & activity_date <= "2015-08-12", lapply(.SD, function(x) sumn(as.numeric(x))), keyby=list(activity_date), .SDcols="streams"]
ggLinegraph(DT.plot.combined, x="activity_date", y="streams", title="FACT_ANALYTICS - test countries")


DT.plot.by_country <- DT.streams_from_raw_analytics[, lapply(.SD, function(x) sumn(as.numeric(x))), keyby=list(activity_date, country_code), .SDcols="streams"]
ggLinegraph(DT.plot.by_country[country_code != "US" & streams > 3e8], x="activity_date", y="streams", color="country_code", vline_value=c("2015-08-12", "2015-08-13"))


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

dateCol <- "download_activity_date"
qFA <- makeQry(tbl=c(FA="fact_analytics"), schema="production", colsToAgg=c(streams="units"), colsToPull=c(dateCol, "country_code"), storeid = 286, country_code=test_countries, minDate="2015-08-01", dateCol=dateCol, join="production.dim_country DC ON FA.countryid = DC.countryid ", prependCols.with.tbl=FALSE)
DT.fact_anal <- sfQry(qFA)

jesusForData(DT.fact_anal)

DT.plot.combined <- DT.fact_anal[download_activity_date >= "2015-08-01" & download_activity_date <= "2015-08-12", lapply(.SD, function(x) sumn(as.numeric(x))), keyby=list(download_activity_date), .SDcols="streams"]
ggLinegraph(DT.plot.combined, x="download_activity_date", y="streams", title="FACT_ANALYTICS - test countries")

DT.plot.by_country <- DT.streams_from_raw_analytics[, lapply(.SD, function(x) sumn(as.numeric(x))), keyby=list(activity_date, country_code), .SDcols="streams"]
ggLinegraph(DT.plot.by_country[country_code != "US" & streams > 3e8], x="activity_date", y="streams", color="country_code", vline_value=c("2015-08-12", "2015-08-13"))