if (FALSE)
  source("~rsaporta/git/orch/src/DeNormalizing/Additional Tables/release_processing_days.r")

setScience(proj="DeNormalizing", quiet=TRUE, subProj="clientmanager", load=FALSE)
setGitBranchToSystem(); .g();

wh <- getWH_already_on()
dbname <- "prod"
schema <- "bi"
size <- "L"
tbl <- "release_processing_days"

setSnowflake(wh=wh, dbname=dbname)
sfWarehouseOn(wh=wh, size=size)

f.in <- ingest.p("client_manager_related", "release_process_date_by_cm", ext="tsv")
stopifnot(file.exists(f.in))

DT <- fread(f.in, colClasses=c("character", "integer", "character", "character", "date", "date"), na.strings="(null)")
setIDCols(DT)
setnames(DT, "assigned_to", "client_manager_id")

if ("labelid" %ni% names(DT)) {
  DT.release_label <- sfQry("SELECT releaseid AS upc, labelid FROM production.dim_release", wh=wh, dbname=dbname)
  setIDCols(DT.release_label)
  addColsFrom_(DT, DT.release_label, colsToBring="labelid", joinCols="upc")
}

if (!is.Date(DT$transfer_to_content)) DT[, transfer_to_content := as.Date(transfer_to_content)]
if (!is.Date(DT$ingestion_completed)) DT[, ingestion_completed := as.Date(ingestion_completed)]

BackUpOrRestore("DT")

## Calculate days and years
DT[, days_to_complete := as.integer(ingestion_completed - transfer_to_content)]
DT[, year := as.integer(year(ingestion_completed))]
DT <- DT[!is.na(year)]

## Adjust negative dates
DT[days_to_complete >= (-2) & days_to_complete < (0), days_to_complete := 0]
DT[days_to_complete < (-2), days_to_complete := (-3)]

## I am assuming that there is only one label_identifier per label
## If this is a false assumption, then I've got issues
stopifnot(DT[, .N, by=list(labelid, label_identifier)][, .N, by=labelid][, N==1])

## Note that since client_manager is a property of label, there is only one CM per label
## We do not (but should) track in our database system when a label changes assignment to a different CM
stopifnot(DT[, .N, by=list(labelid, client_manager)][, .N, by=labelid][, N==1])


## Calculate Average by CM
kCols     <- c("year", "client_manager_id", "client_manager")
kCols.li  <- c(kCols,    "label_identifier")
kCols.lab <- c(kCols.li, "labelid")
setkeyIfNot(DT, kCols.lab, verbose=FALSE, organize=TRUE)

## Calculations by Client Manager
DT[, yearly_average_days_by_cm := as.numeric(median(days_to_complete))          , by=kCols]
DT[, total_releases_by_year_by_cm := .N                                         , by=kCols]
DT[, number_of_labels_releasing_content_by_year_by_cm := lunique(labelid)       , by=kCols]
DT[, number_of_releases_longer_than_14_days_by_cm := sum(days_to_complete > 14) , by=kCols]
DT[, yearly_average_releases_per_label_by_cm := lunique(upc) / lunique(labelid) , by=kCols]

## Calculations by Client Manager & Label Identifier
DT[, yearly_average_days_by_cm_by_li := as.numeric(median(days_to_complete))          , by=kCols.li]
DT[, total_releases_by_year_by_cm_by_li := .N                                         , by=kCols.li]
DT[, number_of_labels_releasing_content_by_year_by_cm_by_li := lunique(labelid)       , by=kCols.li]
DT[, number_of_releases_longer_than_14_days_by_cm_by_li := sum(days_to_complete > 14) , by=kCols.li]
DT[, yearly_average_releases_per_label_by_cm_by_li := lunique(upc) / lunique(labelid) , by=kCols.li]

## Calculations by Label  (and since they are a property of the label, also by Client Manager & Label Identifier)
DT[, yearly_average_days_by_label := as.numeric(median(days_to_complete))          , by=kCols.lab]
DT[, total_releases_by_year_by_label := .N                                         , by=kCols.lab]
DT[, number_of_labels_releasing_content_by_year_by_label := lunique(labelid)       , by=kCols.lab]
DT[, number_of_releases_longer_than_14_days_by_label := sum(days_to_complete > 14) , by=kCols.lab]
DT[, yearly_average_releases_per_label_by_label := lunique(upc) / lunique(labelid) , by=kCols.lab]

## Count the total labels with at least one release longer than 14 days
tmp_DT.countlabs <- DT[, any(days_to_complete > 14), by=kCols.lab][, sum(V1), by=kCols]
DT[tmp_DT.countlabs, number_of_labels_with_at_least_one_release_longer_than_14_days_by_cm := V1]

## Count the average number of releases per label, for each client manager
DT[, yearly_average_releases_per_label_by_cm := lunique(upc) / lunique(labelid), by=kCols]

## Organize the columns, sorting alphabetically
setcolorderpt(DT, sort.middle.cols=TRUE, startCols=c(kCols.lab, "upc", "transfer_to_content", "ingestion_completed", "days_to_complete"))

## There should be no NAs
stopifnot(DT[, !is.na(number_of_releases_longer_than_14_days_by_cm)])
stopifnot(DT[, !is.na(number_of_labels_with_at_least_one_release_longer_than_14_days_by_cm)])

## -------------------------------------- ##
## Some metrics '_by_label' make no sense, since they are already by label
## but they are a nice little check that the calculations are correct
## Confirm them, then drop the columns
## -------------------------------------- ##
stopifnot(DT$number_of_labels_releasing_content_by_year_by_label == 1)
DT[, number_of_labels_releasing_content_by_year_by_label := NULL]

stopifnot(DT[, yearly_average_releases_per_label_by_label == total_releases_by_year_by_label])
DT[, yearly_average_releases_per_label_by_label := NULL]
## -------------------------------------- ##


## Upload to Snowflake
ingestIntoSQL(DT=DT, tbl=tbl, schema=schema, dbname=dbname, wh=wh, drop=TRUE)

## Create lookml
sourceSupportFns(proj="Looker")
f.look <- create_lookml_from_tbl(tbl=tbl, schema=schema, dbname=dbname, wh=wh)

## Locally, bring a copy of the lookml files
if (.Pfm == "Darwin") {
  setScienceIfNot(proj="DeNormalizing");  sourceSupportFns(proj="Looker")
  bringAndCopyLookerFiles("~/git/orch/out/DeNormalizing/auto_generated_lookmls/bi/bi.release_processing_days.view.lookml", over=TRUE)
}