setScience("BunnyLee", load=FALSE, create=TRUE, subl=TRUE)
setGitBranchToSystem(); .g()

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

setSnowflake(wh=wh, dbname=dbname)


trackids <- DT.merged[!is.na(trackid), unique(trackid)]
setkeyIfNot(DT.tracks, trackid)

## This confirms we need to use label_sc_group
if (FALSE) {
  sfQry(paste0("SELECT DISTINCT LABEL_SC_GROUP from bi.accounting where accounting_month >= '2015-01-01' and trackid in ", pasteQ(trackids, q="")))
}

minDate <- "2014-01-01"
dateCol <- "accounting_month"
colsToPull <- c(accounting_year=sprintf("year(%s)", dateCol), supply_chain_group="label_sc_group", "labelid", "label_name", imprint="release_imprint", "label_client_manager", "artistid", "artist_name", upc="releaseid", "release_name", "isrc", "track_name", store="case when storeid = 1 then 'iTunes' when storeid = 286 then 'Spotify' else 'Other Stores' end")
colsToAgg = c("gross_revenue_usd")
qry.sales <- makeQry(tbl="accounting", schema="bi", colsToPull=colsToPull, colsToAgg=colsToAgg, dateCol=dateCol, minDate=minDate, trackid=as.numeric(trackids))

DT.sales_for_bunnylee_like_tracks <- sfQry(qry.sales)

DT.sales_for_bunnylee_like_tracks[, store := toFactorWithExpectedLevels(store, c("iTunes", "Spotify", "Other Stores"))]
DT.sales_for_bunnylee_like_tracks[, supply_chain_group := toFactorWithExpectedLevels(supply_chain_group, c("Orchard", "RED", "Allegro", "SelectO"))]

jesusForData(DT.sales_for_bunnylee_like_tracks)


years <- sunique(DT.sales_for_bunnylee_like_tracks$accounting_year) %>% selfname_

#~~  ## ------------------------- BY YEAR ---------------------- ##
#~~    ## cast, calculating revenue by year
#~~    val <- "gross_revenue_usd"
#~~    fmla <- makeFormula(Right="accounting_year", except=val, DT=DT.sales_for_bunnylee_like_tracks)
#~~    nms_orig <- unique(DT.sales_for_bunnylee_like_tracks[, accounting_year])
#~~    nms_gross <- sprintf("gross_usd_%s", nms_orig) %>% cleanColNamesForSQL_
#~~    DT.sales_for_bunnylee_like_tracks_by_year <- dcast.data.table(DT.sales_for_bunnylee_like_tracks, formula=fmla, fun.aggregate=sumn, value.var=val)
#~~  
#~~    ## Clean names
#~~    setnames(DT.sales_for_bunnylee_like_tracks_by_year, as.character(nms_orig), nms_gross)
#~~    ## Add total's column
#~~    DT.sales_for_bunnylee_like_tracks_by_year[, total_gross_revenue_usd := rowSums(.SD), .SDcols=nms_gross]

## ------------------------- BY STORE + YEAR ---------------------- ##
  ## cast, calculating revenue by year
  val <- "gross_revenue_usd"
  fmla <- makeFormula(Right=c("store", "accounting_year"), except=val, DT=DT.sales_for_bunnylee_like_tracks)
  nms_orig <- unique(DT.sales_for_bunnylee_like_tracks[, sprintf("%s_%s", store, accounting_year)])
  nms_gross <- sprintf("gross_usd_%s", nms_orig) %>% cleanColNamesForSQL_
  DT.sales_for_bunnylee_like_tracks_by_year_and_store <- dcast.data.table(DT.sales_for_bunnylee_like_tracks, formula=fmla, fun.aggregate=sumn, value.var=val)

  ## Clean names
  setnames(DT.sales_for_bunnylee_like_tracks_by_year_and_store, as.character(nms_orig), nms_gross)

  ## NOT USED
  nms_by_year <- lapply(years, extract, nms_gross)
  names(nms_by_year) %<>% paste0("gross_usd_", .)

  ## Add yearly totals
  for (.col in names(nms_by_year))
    DT.sales_for_bunnylee_like_tracks_by_year_and_store[, (.col) := rowSums(.SD), .SDcols=nms_by_year[[.col]]]

  ## Add total's column
  DT.sales_for_bunnylee_like_tracks_by_year_and_store[, total_gross_revenue_usd := rowSums(.SD), .SDcols=nms_gross]


  ## Aggregate to labels
  DT.label_agg_tracks_by_year_and_store <- aggregateDT(
      DT.sales_for_bunnylee_like_tracks_by_year_and_store
    , colsToAgg=c(nms_gross, names(nms_by_year), "total_gross_revenue_usd")
    , by=c("supply_chain_group", "labelid", "label_name", "imprint", "label_client_manager")
  )

  ## Aggregate to SC
  DT.total_agg <- aggregateDT(
      DT.sales_for_bunnylee_like_tracks_by_year_and_store
    , colsToAgg=c(nms_gross, names(nms_by_year), "total_gross_revenue_usd")
    , by=c("supply_chain_group")
  )

  ## WRITE TO XLSX
  f.out <- out.p("Sales for BunnyLee-like tracks", ext="xlsx")
  exportXLS.usingXLConnect(f.out=f.out, DTs.list=list(
      "total overviews"=DT.total_agg
    , "track sales and details"=DT.label_agg_tracks_by_year_and_store
    , "label_aggregate"=DT.label_agg_tracks_by_year_and_store
  ))


  jesusForData()
  saveImageTo()