# catalog_vs_frontline_labels.r

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

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

thresh <- .80

DT.labels <- get_dim_label()

## DONT USE THIS
#     Q.basic <- 
#     "SELECT L.*, F.Revenue
#     FROM (  
#       SELECT labelid, cat_vs_front, count(*) as number_of_releases 
#       FROM (SELECT distinct release_labelid as labelid, release_catalogid as catalogid, release_cat_or_front as cat_vs_front 
#         from bi.release_with_metadata_view)
#       GROUP BY 1, 2
#     ) L
#     JOIN (
#       SELECT labelid, sum(gross) as Revenue
#       FROM production.fact_sales
#       WHERE accountingyear = 2015
#       GROUP BY 1
#     ) F
#     ON L.labelid = F.labelid
#     ORDER BY F.Revenue DESC"
#     
#     DT.basic <- sfQry(Q1)

Qry <- {
"
SELECT labelid, cat_vs_front
      , COUNT( DISTINCT catalogid) as number_of_releases
      , sum(revenue_in_2015) as revenue_in_2015
FROM (
  SELECT F.labelid, F.releaseid, R.catalogid,
           CASE WHEN releasedate > DateAdd(year, -2, current_timestamp())
               THEN 'Frontline' 
               else 'Catalog' 
           END AS cat_vs_front,
  sum(gross) as revenue_in_2015
  FROM production.fact_sales F
  JOIN production.dim_release R
  ON F.releaseid = R.releaseid
  WHERE accountingyear = 2015
  GROUP BY 1, 2, 3, 4
) 
GROUP BY 1, 2
ORDER BY 1, 2
"
}

DT.label_counts_and_rev <- sfQry(Qry)
DT.label_counts_and_rev[, is_front := cat_vs_front == 'Frontline']

DT.label_counts_and_rev.wide <- 
  DT.label_counts_and_rev[, list(
      total_revenue = sum(revenue_in_2015)
    , frontline_revenue   = sum(revenue_in_2015[is_front])
    , catalog_revenue     = sum(revenue_in_2015[!is_front])
    , total_releases      = sum(number_of_releases)
    , frontline_releases  = sum(number_of_releases[is_front])
    , catalog_releases    = sum(number_of_releases[!is_front])
  ), by=labelid]

DT.label_counts_and_rev.wide[, rank_by_total_revenue := rank(-total_revenue)]
DT.label_counts_and_rev.wide[, precent_frontline_by_revenue   := frontline_revenue          / total_revenue    ]
DT.label_counts_and_rev.wide[, precent_frontline_by_release_count := frontline_releases / total_releases ]

DT.label_counts_and_rev.wide[, by_revenue := ifelse(precent_frontline_by_revenue >= thresh, "FRONTLINE", "CATALOG")]
DT.label_counts_and_rev.wide[, by_release_count  := ifelse(precent_frontline_by_release_count >= thresh, "FRONTLINE", "CATALOG")]
DT.label_counts_and_rev.wide[, is_different := ifelse(by_revenue != by_release_count, "X", "")]

## Add in meta data for label
cols.meta_for_label <- c("label_name", "label_priority", supply_chain="label_sc_group")
addMeta_(DT.label_counts_and_rev.wide, DT.labels, colsToBring=cols.meta_for_label, idCol.candidates="labelid")

## Clean up columns and row ordering
startCols <- c("labelid", colNamesFromVector(cols.meta_for_label), "rank_by_total_revenue", "precent_frontline_by_revenue", "precent_frontline_by_release_count", "by_revenue", "by_release_count", "is_different")
setcolorderpt(DT.label_counts_and_rev.wide, startCols)
setkeyIfNot(DT.label_counts_and_rev.wide, rank_by_total_revenue, verbose=FALSE)

DT.label_counts_and_rev.wide

jesusForData(DT.label_counts_and_rev.wide, DT.label_counts_and_rev)

# ----------------------------- #
writeDT(DT.label_counts_and_rev.wide, to="rsaporta@theorchard.com", propper=TRUE)