### EVENTUALLY, THIS SHOULD PULL THE DATA FRESH FROM REPORTS AR
##  For now, just use the flat files that dave previously pulled

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

RUN_QRY <- TRUE
LOOKML <- TRUE

setScience(proj="DeNormalizing", subProj="AdditionalTables")

date_last_modified <- as.Date("2015-10-28")
wh <- "cron_jobs_small"
dbname <- "prod"
schema <- "carveouts"
tbl.label <- "label_level_carveouts"
tbl.release <- "release_level_carveouts"

if (RUN_QRY) setSnowflake(wh=wh, dbname=dbname, start=TRUE)

f.ins <- extractIngestFiles("carve")

if (length(f.ins) > 2)
  stop ("Rick:  You forgot to fix the script to check for largest file by date -- is that a function")

if (RUN_QRY) DT.stores <- sfQry("SELECT storeid, store_name from bi.store_view order by 1")


f.label_levels <- extract("label", f.ins)
tmp_DT.label_level_carveouts <- fread(f.label_levels, na.strings=c("(null)", "null"), colClasses=c("integer", "integer", "character"))
setnames(tmp_DT.label_level_carveouts, "vendor_id", "labelid")
setnames(tmp_DT.label_level_carveouts, "customer_master_master_id", "storeid")
tmp_DT.label_level_carveouts[, storeid := as.integer(storeid)]
labs_odd <- tmp_DT.label_level_carveouts[is.na(storeid) & is.na(country_code), labelid]
addColsFrom_(tmp_DT.label_level_carveouts, DT.stores, joinCols="storeid")
setnames(tmp_DT.label_level_carveouts, "storeid", "label_level_carved_out_storeid")
setnames(tmp_DT.label_level_carveouts, "store_name", "label_level_carved_out_store_name")
setnames(tmp_DT.label_level_carveouts, "country_code", "label_level_carved_out_country_code")

f.release_levels <- extract("release", f.ins)
tmp_DT.release_level_carveouts <- fread(f.release_levels, na.strings=c("(null)"), colClasses=c("integer", "integer", "character"))
setnames(tmp_DT.release_level_carveouts, "customer_master_master_id", "storeid")
setnames(tmp_DT.release_level_carveouts, "upc", "releaseid")
tmp_DT.release_level_carveouts[, storeid := as.integer(storeid)]
releaseids_odd <- tmp_DT.release_level_carveouts[is.na(storeid) & is.na(country_code), releaseid]
addColsFrom_(tmp_DT.release_level_carveouts, DT.stores, joinCols="storeid")
setnames(tmp_DT.release_level_carveouts, "storeid", "release_level_carved_out_storeid")
setnames(tmp_DT.release_level_carveouts, "store_name", "release_level_carved_out_store_name")
setnames(tmp_DT.release_level_carveouts, "country_code", "release_level_carved_out_country_code")

if (length(releaseids_odd)) {
  warning("There are ", length(releaseids_odd), " releaseids with neither store nor country carveout, why are they on the list?")
}
if (length(labs_odd)) {
  warning("There are ", length(labs_odd), " labels with neither store nor country carveout, why are they on the list?")
}


## Pull all labelids for each releaseid and merge to tmp_DT.release_level_carveouts
if (RUN_QRY) DT.release_to_label <- sfQry("SELECT releaseid as releaseid, labelid from production.dim_release order by releaseid")
addColsFrom_(tmp_DT.release_level_carveouts, DT.release_to_label, joinCols="releaseid")

## Pull all label ids
if (RUN_QRY) DT.all_labels <- sfQry("SELECT labelid from production.dim_label order by labelid")

## merge with 
setkeyIfNot(DT.all_labels, labelid, verbose=FALSE, organize=TRUE)
setkeyIfNot(tmp_DT.label_level_carveouts, labelid, verbose=FALSE, organize=TRUE)
setkeyIfNot(tmp_DT.release_level_carveouts, labelid, verbose=FALSE, organize=TRUE)

# tmp_DT.label_level_carveouts <- tmp_DT.label_level_carveouts[DT.all_labels, allow=TRUE]
# tmp_DT.label_level_carveouts[tmp_DT.release_level_carveouts, 
#    `:=`(label_has_some_releases_with_store_carveouts   = any(!is.na(release_level_carved_out_storeid))
#       , label_has_some_releases_with_country_carveouts = any(!is.na(release_level_carved_out_country_code))
#       ), allow=TRUE]
# 
# tmp_DT.label_level_carveouts[, .N, by=list(labelid, label_has_some_releases_with_country_carveouts, label_has_some_releases_with_store_carveouts)]
# DT.label_and_release_level_carveouts[!is.na(labelid), .N, by=list(labelid, label_has_some_releases_with_country_carveouts, label_has_some_releases_with_store_carveouts)]
# 
# labs_lr <- DT.label_and_release_level_carveouts[(label_has_some_releases_with_store_carveouts), .N, by=labelid][order(N, decreasing=TRUE)]$labelid
# labs_l  <- tmp_DT.label_level_carveouts[(label_has_some_releases_with_store_carveouts), .N, by=labelid][order(N, decreasing=TRUE)]$labelid
# 
# allSetDiff(labs_lr, labs_l)
# 
# tmp_DT.label_level_carveouts[labelid == 7123]
# DT.label_and_release_level_carveouts[labelid == 7123]
# unique(DT.label_and_release_level_carveouts[labelid == 7123], by=c("labelid", "releaseid", "release_level_carved_out_storeid"))
# unique(tmp_DT.release_level_carveouts[labelid == 7123], by=c("labelid", "releaseid", "release_level_carved_out_storeid"))
# unique(DT.label_and_release_level_carveouts[labelid == 7123], by="releaseid")


# ---------------------------------------------------------------------- #

setkeyIfNot(DT.all_labels, labelid, verbose=FALSE, organize=TRUE)
setkeyIfNot(tmp_DT.label_level_carveouts, labelid, verbose=FALSE, organize=TRUE)
setkeyIfNot(tmp_DT.release_level_carveouts, labelid, verbose=FALSE, organize=TRUE)

## Merge the three tables
DT.label_and_release_level_carveouts <- {
  DT.all_labels %>% 
    merge(tmp_DT.label_level_carveouts,   by="labelid", all=TRUE, allow=TRUE) %>%
    merge(tmp_DT.release_level_carveouts, by="labelid", all=TRUE, allow=TRUE)
}

## Add in label info, regarding whether it has releases with special carveouts
DT.label_and_release_level_carveouts[, label_has_some_releases_with_store_carveouts   := any(!is.na(release_level_carved_out_storeid)), by=labelid]
DT.label_and_release_level_carveouts[, label_has_some_releases_with_country_carveouts := any(!is.na(release_level_carved_out_country_code)), by=labelid]
## ------------
DT.label_and_release_level_carveouts[, label_has_some_store_carveouts   := any(!is.na(label_level_carved_out_storeid)), by=labelid]
DT.label_and_release_level_carveouts[, label_has_some_country_carveouts := any(!is.na(label_level_carved_out_country_code)), by=labelid]


## ------------
DT.label_and_release_level_carveouts[, release_has_some_store_carveouts   := any(!is.na(release_level_carved_out_storeid)), by=releaseid]
DT.label_and_release_level_carveouts[, release_has_some_country_carveouts := any(!is.na(release_level_carved_out_country_code)), by=releaseid]

## In order for this to be used accurately, we would need to merge in ALL releases for each label (or at least for each label that has some carveout).  Otherwise, positives will only show for those releases already on this list.
##
# DT.label_and_release_level_carveouts[, release_has_some_label_level_store_carveouts   := any(!is.na(label_level_carved_out_storeid)), by=releaseid]
# DT.label_and_release_level_carveouts[, release_has_some_label_level_country_carveouts := any(!is.na(label_level_carved_out_country_code)), by=releaseid]

labCols <- extract("label",       DT.label_and_release_level_carveouts) %>% {.[!grepl("^release_has", .)]}
relCols <- extract("release|releaseid", DT.label_and_release_level_carveouts) %>% {.[!grepl("^label_has",   .)]}

## Different ways to confirm that all of the columns are included
stopifnot(!length(intersect(labCols, relCols)))
stopifnot(!length(setdiff(names(DT.label_and_release_level_carveouts), c(labCols, relCols))))
stopifnot(identical(sort(names(DT.label_and_release_level_carveouts)), sort(unname(c(labCols, relCols)))))

## ISOLATE DT.label_level_carveouts
DT.label_level_carveouts <- DT.label_and_release_level_carveouts[!is.na(labelid), .N, keyby=labCols][, N := NULL]
## Confirm that the "has_..." cols are unique by 
has_cols <- extract("has", DT.label_level_carveouts)
stopifnot(DT.label_level_carveouts[, lapply(.SD, function(x) length(unique(x)) == 1), by=labelid, .SDcols=has_cols][, lapply(.SD, all), .SDcols=has_cols] %>% unlist)

## ISOLATE DT.release_level_carveouts
DT.release_level_carveouts <- DT.label_and_release_level_carveouts[!is.na(releaseid), .N, keyby=relCols][, N := NULL]
## Confirm that the "has_..." cols are unique by 
has_cols <- extract("has", DT.release_level_carveouts)
stopifnot(DT.release_level_carveouts[, lapply(.SD, function(x) length(unique(x)) == 1), by=releaseid, .SDcols=has_cols][, lapply(.SD, all), .SDcols=has_cols] %>% unlist)

## DROP ANY ROWS WHERE THERE ARE NO CARVE OUTS
## This relates to the warning earlier, in that they should not be in this list to begin with
DT.release_level_carveouts <- DT.release_level_carveouts[(release_has_some_store_carveouts | release_has_some_country_carveouts)]
DT.label_level_carveouts <- DT.label_level_carveouts[(label_has_some_store_carveouts | label_has_some_country_carveouts) | (label_has_some_releases_with_store_carveouts | label_has_some_releases_with_country_carveouts)]


ingestIntoSQL(DT=DT.label_level_carveouts,   tbl=tbl.label,    schema=schema, dbname=dbname, wh=wh, add.ingestDate=TRUE, datetime_type='TIMESTAMP_TZ', drop=TRUE, snowflake=TRUE)
ingestIntoSQL(DT=DT.release_level_carveouts, tbl=tbl.release,  schema=schema, dbname=dbname, wh=wh, add.ingestDate=TRUE, datetime_type='TIMESTAMP_TZ', drop=TRUE, snowflake=TRUE)

sfShowTables(schema=schema, dbname=dbname)

if (LOOKML)
{
  cls(25)
  sourceSupportFns(proj="Looker")
  create_lookml_model_from_schema(dbname=dbname, schema=schema)
}


if (FALSE)
{
  setScienceIfNot(proj='misc')
  sourceSupportFns(proj='Looker')
  bringAndCopyLookerFiles(f='~rsaporta/git/orch/out/DeNormalizing/auto_generated_lookmls/carveouts')

  .o('/Applications/Tower 2.0.app')
}