# create client_managers tbl.r
if (FALSE)
  source("~/git/orch/src/DeNormalizing/create client_managers tbl.r")


setScience(proj="DeNormalizing", quiet=TRUE, subProj="clientmanager", load=FALSE)

tbl <- "client_managers_by_label"
schema <- "BI"

wh <- "SCIENCE"
dbname <- "prod"

refresh <- TRUE


## This is the file that is used as a temp file to write-to
file.cm_by_label <- ingest.p("client_manager_related", "client_managers_by_label", ext="tsv")

if (refresh) {
#  ## so long as DBI // rstats-db // MySQL is still a bit buggy
#  devtools::install_github("rstats-db/DBI")

  setDBall("MySql")

  ## Grab the meta data for the client managers
  DT.orch_cm_meta <- runQry(makeQry(tbl="orchadmin_users", colsToPull=c(client_manager_id="id", "f_name", "l_name", "owner", client_manager_is_active="active", client_manager_is_closer="closer"), aggFunc=NULL, limit=NULL))
  DT.orch_cm_meta[, client_manager := paste(f_name, l_name)] [, c("f_name", "l_name") := NULL]
  
  # ## CHANGE fields active and closer from 'Y'/'N' to  TRUE/FALSE
  # DT.orch_cm_meta[, is_active := (toupper(active) == "Y")][, active := NULL]
  # DT.orch_cm_meta[, is_closer := (toupper(closer) == "Y")][, closer := NULL]


  colsToPull = c(labelid="vendor_id", label_is_distributor="is_distributor", client_manager_id="assigned_to", label_is_owned="is_owned", label_status="status", label_type="label_identifier") # , label_name="company", "status", just_plain_name="name", "join_vendor_id", label_owner="owner",
  DT.client_managers_by_label <- runQry(makeQry(tbl="vendor", colsToPull=colsToPull, aggFunc=NULL, limit=NULL))
  ## The is_owned field is 'Yes'/'No' instead of 'Y'/'N' -- modify to clean up
  DT.client_managers_by_label[, label_is_owned := substr(label_is_owned, 1, 1)]

  ## BRING IN THE CLIENT NAME info to the client managers DT
  ## OLD # addColsFrom_(DT.client_managers_by_label, DT.orch_cm_meta, colsToBring=c(client_manager="full_name", client_manager_is_active="active"), joinCols="client_manager_id")
  ## NEW
  addColsFrom_(DT.client_managers_by_label, DT.orch_cm_meta, joinCols="client_manager_id")

  stopifnot(!nrow(DT.client_managers_by_label[is.na(client_manager_is_active) & (client_manager_id != 0 & !is.na(client_manager_id))]))
  writeDT(DT.client_managers_by_label, full_file_path=file.cm_by_label)
} else {
  DT.client_managers_by_label <- fread(ingest.p("client_managers_by_label.tsv"))
}


setSnowflake(wh=wh, dbname=dbname)
ingestIntoSQL(DT=DT.client_managers_by_label, tbl=tbl, schema=schema, dbname=dbname, datetime_type='TIMESTAMP_NTZ', drop=TRUE, append=FALSE, verbose=TRUE, verbose.qry=TRUE)


## LOOKER'ize it
if (FALSE) {
  cls(25)
  sourceSupportFns(proj="Looker")
  create_lookml_from_tbl("CLIENT_MANAGERS_BY_LABEL", schema="bi")
}