# screen -xRR nightlife

setScience("label_nightlife", create=TRUE, load=FALSE, subl=FALSE)
syncIngestDir()
setGitBranchToSystem(); .g()

wh <- getWH_already_on(default="SCIENCE")
setSnowflake(wh=wh, dbname="prod")


f.in <- "Nightlife_LIC_Labels_Data.csv" %>% ingest.p
stopifnot(file.exists(f.in))

DT.nightlife <- fread(f.in, colClasses="character") %>% cleanColNamesForSQL_
DT.nightlife[, cleaned_name_to_match := clean_names_to_simple_alpha(label)]

## Check against label_name
if (!exists("DT.labels")) DT.labels <- get_dim_label(refresh=TRUE)
DT.labels[, cleaned_name_to_match := clean_names_to_simple_alpha(label_name)]
addColsFrom_(DT.nightlife, DT.labels, colsToBring=c(labelid_by_label_name="labelid", "label_owner"), joinCols="cleaned_name_to_match")
DT.nightlife[!is.na(labelid_by_label_name)]

DT.imprints[imprint_name == "PAMPA RECORDS"]

DT.nightlife[grepl("pampa", cleaned_name_to_match, ignore.case=TRUE)]
# DT.labels[grepl("pampa", cleaned_name_to_match, ignore.case=TRUE)]
# DT.labels[labelid == 11783]

## Check against imprint names
if (!exists("DT.imprints")) 
  DT.imprints <- sfQry(makeQry(colsToPull=c("imprint_name"="imprint", "labelid", "imprintid", parent_label_name="L.labelname"), tbl=c(I="dim_imprint"), join="production.dim_label L on L.labelid = I.labelid", schema="production"))
DT.imprints[, cleaned_name_to_match := clean_names_to_simple_alpha(imprint_name)]
addColsFrom_(DT.nightlife, DT.imprints, colsToBring=c(labelid_by_imprint_name="labelid", "imprint_name", "imprintid", "parent_label_name"), joinCols="cleaned_name_to_match")
DT.nightlife[!is.na(labelid_by_imprint_name)]

## Check against subaccount names
if (!exists("DT.subaccounts")) DT.subaccounts <- sfQry(makeQry(colsToPull=c("subaccount_name"="subaccountname", "labelid", "subaccountid"), tbl="dim_subaccount", schema="production"))
DT.subaccounts[, cleaned_name_to_match := clean_names_to_simple_alpha(subaccount_name)]
addColsFrom_(DT.nightlife, DT.subaccounts, colsToBring=c(labelid_by_subaccount_name="labelid", "subaccount_name", "subaccountid"), joinCols="cleaned_name_to_match")
DT.nightlife[!is.na(labelid_by_subaccount_name)]


## Pick a label_id
DT.nightlife[is.na(label_id) | label_id == "", label_id := labelid_by_label_name]
DT.nightlife[is.na(label_id) | label_id == "", label_id := labelid_by_imprint_name]
DT.nightlife[is.na(label_id) | label_id == "", label_id := labelid_by_subaccount_name]

## look and confirm
DT.nightlife[!is.na(labelid_by_imprint_name) | !is.na(labelid_by_label_name) | !is.na(labelid_by_subaccount_name)]

startCols <- c("label", "cleaned_name_to_match", "label_id", extract("labelid", DT.nightlife)) %>% unique
setcolorderpt(DT.nightlife, startCols)


DT.client_managers <- sfQry("SELECT * FROM bi.CLIENT_MANAGERS_BY_LABEL")

DT.nightlife[, label_id := as.integer(label_id)]
DT.nightlife[, client_manager := NULL]
addColsFrom_(DT.nightlife, DT.client_managers, joinCols.r="label_id", joinCols.g="labelid")


writeDT(DT.nightlife, to=getRS())