setScience("AppleMusic")
setSnowflake(wh="SCIENCE")

## -----------------------------------------------------------------------------------
## divisor ::  seems to apply only to Linear Radio 
## -----------------------------------------------------------------------------------

lib(reshape2)

# qry <- "select LABEL_PROPORTIONATE_SHARE, is_trial, product, end_date, country, filename, 
# total_paid_subscriber_royalty_bearing_plays,
# total_royalty_bearing_plays,
# LABEL_PAID_SUBSCRIBER_ROYALTY_BEARING_PLAYS, 
# LABEL_ROYALTY_BEARING_PLAYS, distributor from PROD.MARKET_SHARE.APPLE_MUSIC 
# where end_date in ('2016-02-27') and country = 'Austria' order by distributor,product DESC;"

qry <- "
SELECT *
FROM PROD.MARKET_SHARE.APPLE_MUSIC 
WHERE end_date in ('2016-02-27') 
AND country in ('Austria', 'United States', 'Turkey') 
ORDER BY distributor,product DESC;"

DT <- sfQry(qry)

fields_added_during_our_ETL <- c("filename", "distributor", "is_adsup", "is_bundle", "is_multiuser", "is_trial")
product_keywords <- c("RADIO", "PAID", "TRIAL", "FAMILY", "INDIVIDUAL", "EXTENDED", "INDIRECT") %>% selfname_()



## Find whether or not each product is being used
DT.products_by_field <- DT[, lapply(.SD, function(x) ifelse(all(is.na(x)), "-", "USING")), by=list(product)]


find_products_by_keyword <- function(keyword, DT, remove_indirect=FALSE) {
  # the DT should have one column named 'product' and all the other columns should be the fields in question, with a value of "USING" when in use
  ret <- DT[, grep(keyword, product, value=TRUE)]

  ## This is optional, but in practice is not needed, since they are the same always
  if (remove_indirect && toupper(keyword) %ni% c("INDIRECT", "EXTENDED"))
    ret <- ret[!grepl("INDIRECT", toupper(ret))]
  ret
}

find_fields_exclusive_to_keyproducts <- function(keyproducts, DT) {
  # the DT should have one column named 'product' and all the other columns should be the fields in question, with a value of "USING" when in use
  DT[, lapply(.SD, function(x) any(x[product %in% keyproducts] == "USING") && !any(x[product %ni% keyproducts] == "USING"))] %>% 
    unlist %>% 
    nwhich
}

find_fields_used_by_keyproducts <- function(keyproducts, DT) {
  # the DT should have one column named 'product' and all the other columns should be the fields in question, with a value of "USING" when in use
  DT[, lapply(.SD, function(x) any(x[product %in% keyproducts] == "USING"))] %>% 
    unlist %>% 
    nwhich
}

find_fields_never_used_by_keyproducts <- function(keyproducts, non_fields=c("product"), DT) {
  # the DT should have one column named 'product' and all the other columns should be the fields in question, with a value of "USING" when in use
  DT[, lapply(.SD, function(x) !any(x[product %in% keyproducts] == "USING"))] %>% 
    unlist %>% 
    nwhich %>%
    setdiff(non_fields)
}



ll_product_groups       <- lapply(product_keywords,   find_products_by_keyword,               DT=DT.products_by_field)
ll_fields_exlusive_to   <- lapply(ll_product_groups,  find_fields_exclusive_to_keyproducts,   DT=DT.products_by_field)
ll_fields_never_used_by <- lapply(ll_product_groups,  find_fields_never_used_by_keyproducts,  DT=DT.products_by_field)
ll_fields_used_by       <- lapply(ll_product_groups,  find_fields_used_by_keyproducts,        DT=DT.products_by_field)



DT.fields_used_by_product <- {
    ## This first line is the same as DT.products_by_field
    DT[, lapply(.SD, function(x) ifelse(all(is.na(x)), "-", "USING")), by=list(grp=product), .SDcols=names(DT)] %>%
    melt.data.table(id.vars=c("grp"),  variable.name="fields") %>% 
    dcast.data.table(fields ~ grp) %>%
    {.[, used_by_all := ifelse(apply(.SD, 1, function(x) all(x == "USING")), "Yes", "-"), by=fields]} %>%
    {.[, fields := as.character(fields)]} %>%
    setcolorderpt(startCols=c("fields", "used_by_all", "INDIVIDUAL MONTHLY PAID", "FAMILY MONTHLY PAID", "INDIVIDUAL MONTHLY TRIAL", "FAMILY MONTHLY TRIAL", "INDIRECT INDIVIDUAL MONTHLY PAID"), endCols="LINEAR RADIO") %>%    
    setkey("fields")
}


DT.fields_used_by_is_trial <- {
    DT[, lapply(.SD, function(x) ifelse(all(is.na(x)), "-", "USING")), by=list(is_trial=is_trial==1, is_radio=product=="LINEAR RADIO"), .SDcols=names(DT)] %>%
    {.[, grp := ifelse(is_radio, "Radio", ifelse(is_trial, "Trial", "Regular"))][, c("is_radio", "is_trial") := NULL] } %>% 
    melt.data.table(id.vars=c("grp"),  variable.name="fields") %>%
    dcast.data.table(fields ~ grp) %>%
    {.[, used_by_all := ifelse(apply(.SD, 1, function(x) all(x == "USING")), "Yes", "-"), by=fields]} %>%
    {.[, fields := as.character(fields)]} %>%
    setcolorderpt(startCols=c("fields", "used_by_all"), endCols="Radio") %>% {
      .[Regular == "USING" & Trial   == "-" & Radio == "-", Regular := "Exclusively"
      ][Trial   == "USING" & Regular == "-" & Radio == "-", Trial   := "Exclusively"
      ][Radio   == "USING" & Regular == "-" & Trial == "-", Radio   := "Exclusively"
      ]
    } %>%
    setkey("fields")
}

## Confirm all the fields are the same
stopifnot(DT.fields_used_by_is_trial$fields == DT.fields_used_by_product$fields)

type_and_sample_values_per_field <- function(x, mx_dont_crop=8) {
  type <- is(x)[[1]]
  sample_values <- 
    x %>% unique %>% removeNA %>% 
    {
      if (is.character(.) || length(.) <= mx_dont_crop)
        sort(.)
      else
        sample(.)
    } %>%
    {
      if (is.character(.))
        sprintf("'%s'", .)
      else
        .
    } %>%
    {
    if (length(.) <= mx_dont_crop)
      .
    else
      c(head(., 3), "...", tail(., 2))
    } %>%
    pasteC(C=", ")

  ## RETURN
  list(type, sample_values)
}


DT.Notes_on_Fields <- {
  DT[, lapply(.SD, type_and_sample_values_per_field)] %>% 
  cbind(data.table(val=factor(c("type", "sample_values"), levels=c("type", "sample_values"))), .) %>%
  melt.data.table(id.var="val", variable.name="fields") %>% 
  dcast.data.table(fields ~ val) %>%
  {.[, fields := as.character(fields)]} %>%
  {.[, type   := unlist(type)]} %>%
  {.[, sample_values := unlist(sample_values)]} %>%
  setkey("fields")
}

## Add in some columns
DT.Notes_on_Fields[DT.fields_used_by_product, used_by_all := i.used_by_all]
DT.Notes_on_Fields[, added_during_our_ETL := ifelse(fields %in% fields_added_during_our_ETL, "Yes", "-")]

if (FALSE)
  DT.Notes_on_Fields[, sample_values := NULL]

## Make sure the names of these two lists are the same
stopifnot(names(ll_fields_exlusive_to) == names(ll_fields_used_by))
for (nm in names(ll_fields_exlusive_to)) {
  col.used <- sprintf("used_by_%s_products", nm)

  DT.Notes_on_Fields[ , c(col.used) := ifelse(fields %in% ll_fields_used_by[[nm]], "Yes", "Never")       ]
  DT.Notes_on_Fields[ .(ll_fields_exlusive_to[[nm]]), c(col.used) :=  "Exclusively" ]
}

## Add a notes field
DT.Notes_on_Fields[, notes := " "]


ll_DT.AppleMusic_MktShare_Fields_Used <- 
  list(
      "by Product Type with Notes"   = DT.Notes_on_Fields
    , "by Trial vs Radio vs Regular" = DT.fields_used_by_is_trial
    , "by Specific Product"          = DT.fields_used_by_product
  )

debug(exportXLS.usingWriteXLS)
f.out <- out.p("AppleMusic_MktShare_Fields_Used", ext="xlsx")
exportXLS.usingXLConnect(DTs.list=ll_DT.AppleMusic_MktShare_Fields_Used, f.out=f.out)
catnn("\n", sprintf("bringme('%s')\n.o('%1$s')\n", f.out))