.us()
.us()
verbose <- TRUE
setScience(proj="Growing", create=TRUE, subl=FALSE)



DT.artists  <- setkey(runQry("SELECT artistid AS id,  artistname as name  FROM dim_artist"), id)
DT.genres   <- setkey(runQry("SELECT genreid  AS id,  genrename  as name  FROM dim_genre"),  id)
DT.stores   <- setkey(runQry("SELECT storeid  AS id,  storename  as name  FROM dim_store"),  id)
DT.transacs <- setkey(runQry("SELECT transactiontypeid  AS id,  transactiontypedesc  as name  FROM dim_transactiontype"),  id)



minDate         <- "2014-01-01"
countries.using <- c(1)

genres.using    <- DT.genres[name %in% c("Folk", "Latin Music", "Metal", "Pop", "Punk", "Reggae", "Rock"), sort(id)]
stores.using    <- DT.stores[rowSums(sapply(c("You", "iTunes", "iRadio", "Spotify", "Rhapsody", "Napster", "Box", "Punk", "Play"), grepl, name)) > 0, id]


Qry <- paste(
              "\n SELECT    artistid", 
              "\n         , download_activity_date", 
              "\n         , storeid", 
              "\n         , genreid", 
              "\n         , transactiontypeid AS transacid", 
              "\n         , uuid as user", 
              "\n         , sum(units) as units", 
              "\n         , sum(paidunits) as paidunits", 
              "\n FROM   production.fact_analytics", 
              "\n WHERE  download_activity_date > ", pasteQ(minDate, w=""), 
              "\n   AND  genreid   in ", pasteQ(genres.using, q=""), 
              "\n   AND  storeid   in ", pasteQ(stores.using, q=""), 
              "\n   AND  countryid in ", pasteQ(countries.using, q=""), 
              "\n GROUP BY 1, 2, 3, 4, 5, 6", 
              "\n "
            )

## EXECUTE QUERY
DT.raw_for_growing <- runQry(Qry);  DT.raw_for_growing.bak <- copy(DT.raw_for_growing)


mergeInMeta_ <- function(DT.raw, DT.meta, newColName.raw, idCol.raw, idCol.meta="id", nameCol.meta="name", dropIfNoNAs=TRUE) {
## 
## a fancy wrapper for:
##      DT.raw_for_growing[, store := DT.stores[.(storeid)]$name ] [, storeid := NULL]
##   

    ## Allow for characters
    if (is.character(DT.meta))
      DT.meta <- get(DT.meta, envir=parent.frame())
    if (is.character(DT.raw))
      DT.raw <- get(DT.raw, envir=parent.frame())


    if (DT.raw %cont% idCol.raw && DT.meta %cont% idCol.raw)
      stop("\n\nidCol.raw ('", idCol.raw, "') cannot be a name in both the raw and meta.\nTry renaming the col in DT.meta to just 'id'")

    setkeyIfNot(DT.meta, idCol.meta, verbose=FALSE)

    DT.raw[, c(newColName.raw) := DT.meta[.(get(idCol.raw))][[nameCol.meta]] ] 
    # browser()

    if (dropIfNoNAs && !any(is.na(DT.raw[[newColName.raw]])))
      DT.raw[, c(idCol.raw) := NULL]

    invisible(DT.raw)
}

DT.raw_for_growing <- copy(DT.raw_for_growing.bak)
## ADD IN META INFO, DROP the id 
metaToClean <- c("artist", "genre", "store", "transac")
for (m in metaToClean) {
    if (verbose)
        cat("processing m =", m, "\n")

    mergeInMeta_ (DT.raw=DT.raw_for_growing, 
                  DT.meta=sprintf("DT.%ss", m), 
                  newColName.raw=m, 
                  idCol.raw=sprintf("%sid", m), 
                  idCol.meta="id", 
                  nameCol.meta="name", 
                  dropIfNoNAs=TRUE)
}


kCols <- c("artist", "store", "transac", "download_activity_date", "user")
setkeyIfNot(DT.raw_for_growing, kCols)
setcolorderpt(DT.raw_for_growing, kCols)
jesusForData(DT.raw_for_growing, info="meta merged in")

## Some artists missing for some reason?
##    DT.raw_for_growing[.(NA_character_)][, table(artistid, store)]
##    #  artistid   iTunes Spotify YouTube
##    #    163757        0       0      17
##    #    535946      243     292       0
DT.raw_for_growing <- DT.raw_for_growing[!is.na(artist)][, artistid := NULL]

## Drop low sales, we dont need them.  We are looking for top sellers
DT.raw_for_growing[, logunits := round(log10(units))]
lowSales <- DT.raw_for_growing[logunits < 2 | !is.finite(logunits)]
artistsKeeping <- DT.raw_for_growing[.(unique(lowSales$artist))][units >= 1200, unique(artist)]
lowSales <- lowSales[!.(artistsKeeping)]
DT.raw_less <- DT.raw_for_growing[!.(lowSales)]


## Count how many users per artist
DT.raw_less[user != 0]


saveImageTo()