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

## SNOWFLAKE IS OUT OF SYNC -- USING REDSHIFT FOR NOW
if (FALSE) {
  wh <- "LOOKER_WH_LARGE"
  setSnowflake(wh=wh, dbname="prod")
} else {
  options(snowflake_inuse = FALSE)
  setDBall(cluster=4)
}

## Verbose to User
cat("Snowflake is being used? ~~~> ", getOption("snowflake_inuse"), "\n")


DT.Jedi_Mind_Tricks <- runQry("SELECT * FROM BI.JEDIMINDTRICKS")
# DT.Jedi_Mind_Tricks[`transaction grouping` == "Stream" & !(units == 0)]
# DT.Jedi_Mind_Tricks[`transaction grouping` == "Stream" & units == 0, .N, keyby=`transaction abbr`]
# DT.Jedi_Mind_Tricks[`transaction grouping` == "Stream" & `country name` == "United Kingdom" & upc == 886788046405 & `transaction abbr` == "S" & `weeks since release` == 197]
setnamestopropper(DT.Jedi_Mind_Tricks)

DT.Jedi_Mind_Tricks[`Transaction Grouping` == "Stream", `Estimated Revenue` := NA]

if (!is.Date(DT.Jedi_Mind_Tricks[["Release Date"]]))
  DT.Jedi_Mind_Tricks[, 'Release Date' := as.tz(`Release Date`, 'UTC') %>% as.Date]

k <- names(DT.Jedi_Mind_Tricks) %>% head(-2)
setkeyIfNot(DT.Jedi_Mind_Tricks, k)

jesusForData(DT.Jedi_Mind_Tricks)



DT.Jedi_Mind_Tricks_NO_STOREINFO <- aggregateDT(DT.Jedi_Mind_Tricks, exclude=c("Store Name"))
DT.Jedi_Mind_Tricks_NO_STOREINFO_NO_COUNTRYINFO <- aggregateDT(DT.Jedi_Mind_Tricks, exclude=c("Store Name", "Country Name", "Market Group"))


to <- c("Marissa Putney <mputney@theorchard.com>", "Samantha Wolff <swolff@theorchard.com>")
cc <- "Data Requests <DataRequests@theorchard.com>"

writeDT (DT.Jedi_Mind_Tricks, to=to, subject="Jedi Mind Tricks Raw Data OVERALL", cc=cc, proppernames=TRUE)
writeDT (DT.Jedi_Mind_Tricks_NO_STOREINFO, to=to, subject="Jedi Mind Tricks Raw Data [Store Info REMOVED]", cc=cc, proppernames=TRUE)
writeDT (DT.Jedi_Mind_Tricks_NO_STOREINFO_NO_COUNTRYINFO, to=to, subject="Jedi Mind Tricks Raw Data [Store & Country Info REMOVED]", cc=cc, proppernames=TRUE)


DT.Jedi_Mind_Tricks[`Transaction Abbr` == 'DT' & UPC == 886788046405][order(`Weeks Since Release`)]
DT.Jedi_Mind_Tricks_NO_STOREINFO[`Transaction Abbr` == 'DT' & UPC == 886788046405][order(`Weeks Since Release`)]
DT.Jedi_Mind_Tricks_NO_STOREINFO_NO_COUNTRYINFO[`Transaction Abbr` == 'DT' & UPC == 886788046405][order(`Weeks Since Release`)]