## TODO: Split budget by stores, where there are multiple stores

DT.stores     <- get_dim_store(refresh=FALSE)
if (!exists("skip_exportOfBudget"))
  skip_exportOfBudget <- FALSE

## Grab all the .xls / .xlsx files
files  <- dir(folder.manual_templates, pattern="\\.xls[x]$", full=TRUE)

## TEMPOARY -- drop any files ending in "- old" or "- updated"
files <- files[!grepl("ManualTemplate For MGMT Report - (updated|old)", files)]

## If a file is open in excel, there will be a duplicate version 
##   in the folder with the preface "~$filename"  -- Ignore these
files.ignore <-  dir(folder.manual_templates, pattern="^~\\$.+\\.xls[x]$", full=TRUE)
files <- setdiff(files, files.ignore)

## extract the file names
nms.file <- basename(files)
names(files) <- nms.file

## Extract the month
month <- stringr::str_extract(nms.file, "\\d{4}-\\d{2}")
month <- as.Date(paste0(month, "-01"))

## only keep those files whose filename starts with a month
files <- files[!is.na(month)]
month <- month[!is.na(month)]

## Inform User
message(sprintf("Importing %i %s with date range %s ~ %s", length(files), plrl("files", files), min(month), max(month)))


ingestFile <- function(f, m) {
    wb <- loadWorkbook(f)

    Music <- readWorksheet(wb, sheet="Music", startCol=2L, check.names=FALSE)
    Video <- readWorksheet(wb, sheet="Video", startCol=2L, check.names=FALSE)

    setnames(Music, 1, "item")
    setnames(Video, 1, "item")

    Music <- as.data.table(Music) [!is.na(item)]
    Video <- as.data.table(Video) [!is.na(item)]

    ##  TEMP
    ## I've changed the 'manualline' term to 'ancillaryline'
    ##   ... this needs to be updated in the template files. For now, doint it manually
    if ("manualline" %in% names(Music)) setnames(Music, "manualline", "ancillaryline")
    if ("manualline" %in% names(Video)) setnames(Video, "manualline", "ancillaryline")

    ## TEMP -- there should be a line for delivery.
    ##     If it does not exist, copy a line, say Publishing Services, and modify it. eg, values at about 1/2 that of Publishing services
    if ("Delivery" %ni% Music$ancillaryline) {
      Music <- rbind(Music, copy(Music[ "Publishing Services" == ancillaryline])[, ancillaryline := "Delivery"][, store_name := "_Delivery"] [, Estimate_Current_Month := Estimate_Current_Month / 2][, Budget := Budget / 2])
    }


    cbind(date=m, rbind(Music, Video, fill=TRUE), updated=Sys.time())
}

## CREATE DT.manual & Clean it
{
  ## Ingest and rbind the multiple files
  DT.manual.ll <- mapply(ingestFile, files, month, SIMPLIFY=FALSE)
  DT.manual    <- rbindlist(DT.manual.ll) [, "DONT EDIT PAST THIS COLUMN" := NULL]
  setInfo(DT.manual, "an rbindlist of DT.manual.ll which itself\nis a list of DT ingestions of the Excel manual templates\nSubsequently we will add details to DT.manual from DT.stores")

  ## Make all the names lowercase to match the rest of the DTs
  setnamestolower(DT.manual)

  ## Instead of 'zUncategorized' use 'zUncategorized Store'
  DT.manual[store_musicbucket == 'zUncategorized', store_musicbucket := 'zUncategorized Store']
  DT.manual[store_videobucket == 'zUncategorized', store_videobucket := 'zUncategorized Store']


  ## For Budget, etc, release_product_type is same as music_vs_video
  DT.manual[, release_product_type := music_vs_video]
  DT.manual[release_product_type=="Video", release_product_type := "Video Etc"]
  DT.manual[store_videobucket=="Retail Video" & music_vs_video == "Video" & label_sc_group %in% c("Allegro", "SelectO", "OSC"),   release_product_type := "Movie"]
  DT.manual[store_videobucket=="Retail Video" & music_vs_video == "Video" & label_sc_group == "SelectO",                          release_product_type := "Web Videos"]

  ## POSIX columns need cleaning
  ## I believe that this is due to Excel / XLConnect reading in the (equivalent of) today() function in a column where some values already have date stamps
  cols.Posix <- nwhich(sapply(DT.manual, is.POSIX))
  if (length(cols.Posix))
      suppressWarnings(DT.manual[, (cols.Posix) := lapply(.SD, function(x) as.POSIXct(as.character(x))), .SDcols=cols.Posix ])

  ## NAs in character columns need cleaning
  cols.Char <- nwhich(sapply(DT.manual, is.character))
  if (length(cols.Char))
      suppressWarnings(DT.manual[, (cols.Char) := lapply(.SD, function(x) {x[x=="NA"] <- NA; x}), .SDcols=cols.Char ])

  ## All ancillaryline entires fall under "Other Services" -- there is an error in the excel file that changes this
  DT.manual[!is.na(ancillaryline), store_musicbucket := "Other Services"]


  ## TODO:  Split by multiple storeids
  ## .... for now, just use the first store
  DT.manual[, storeid := sapply(strsplit(gsub("\\((.+)\\)", "\\1", storeid), ","), "[[", 1), ]
  DT.manual[, storeid := suppressWarnings(as.integer(storeid))]

  ## NA storeid will default to (-1)
  DT.manual[is.na(storeid), storeid := -1]

  ## Cleanup storeids
  ## Cannot allow superset for DT.manual, but yest for DT.stores
  setkeyIfNot(DT.manual, storeid, verbose=FALSE, organize=TRUE, superset.ok=FALSE)
  setkeyIfNot(DT.stores, storeid, verbose=FALSE, superset.ok=TRUE)

  ## Must add a dummy column, since ifelse() etc fails with same column name
  DT.manual[DT.stores, store_name_actual := i.store_name, allow=TRUE] ## 2015-02-10 added 'allow'
  DT.manual[!is.na(store_name_actual), store_name := store_name_actual]
  ## Drop the store_name_actual column
  DT.manual[, store_name_actual := NULL]
  ## CONFIRM: No NA's in store_name
  stopifnot(DT.manual[, !is.na(store_name)])


  ## Add in store_musicbucket info from DT.stores
  DT.manual[DT.stores, store_musicbucket := i.store_musicbucket, allow=TRUE] ## 2015-02-10 added 'allow'

  ## Retail Video should all have a *MUSIC* bucket of "Download"
  DT.manual[store_videobucket == "Retail Video", store_musicbucket := "Download"]

  ## CONFIRM THAT THE ONLY NAs are from ancillaryline before modifying
  stopifnot(DT.manual[is.na(store_musicbucket), !is.na(ancillaryline)])
  DT.manual[is.na(store_musicbucket), store_musicbucket := "Other Services"]

  ## This should be changed in the SQL, but just in case, catching it here as well
  DT.manual[store_musicbucket == "zOTHER STORESz", store_musicbucket := "zUncategorized Store"]

  ## NOTE TO SELF:  What about 'store_videobucket' ?  -- NO NEED
  ## Note that store_videobucket came in from the dicts and is correct



  ## Append a "zz" to start of store_name s with "_"
  DT.manual[store_name == "_Other Other Services", store_name := "_Other"]
  DT.manual[!is.na(ancillaryline) & grepl("^_", store_name), store_name := paste0("zz", store_name)]

  ## Cleanup NA's in store_musicbucket/store_videobucket
  DT.manual[is.na(store_musicbucket), store_musicbucket := "zUncategorized Store"]
  DT.manual[is.na(store_videobucket), store_videobucket := "zUncategorized Store"]

  ## Furthermore... the buckets are by STORE thuse, they should match by storeid
  ##  fix it here, and not in excel, since we might have to split by store (1, 186) etc
  setkeyIfNot(DT.manual, storeid, verbose=FALSE)
  DT.manual[DT.manual[!.(-1), lunique(store_musicbucket) > 1, by=storeid][(V1)], store_musicbucket := setdiff(unique(store_musicbucket), "zUncategorized Store")]
  DT.manual[DT.manual[!.(-1), lunique(store_videobucket) > 1, by=storeid][(V1)], store_videobucket := setdiff(unique(store_videobucket), "zUncategorized Store")]


  ## CONFIRM, NO DUPLICATE VALUES
  stopifnot(DT.manual[!.(-1), lunique(store_musicbucket) & lunique(store_videobucket)])
  ## TODO:  This line above does NOT do what I think it does.... but why do I even need unique values here??

  ## ADD SUPERBUCKET
  addSuperBucket_ (DT.manual)

  ## CHECK WITH PRAS ON WHY THESE IS TRUE: 
  ##   2014-05-01       1     iTunes          Video        SelectO            Video Etc                  FALSE          Download      Retail Video         NA          NA               NA            NA          NA                     NA               0
  ##   2014-05-01       1     iTunes          Video        SelectO           Web Videos                  FALSE          Download      Retail Video         NA 0.00000e+00      0.00000e+00      19604366 0.00000e+00                     NA              NA
  ##   
  ##   2014-05-01       1     iTunes          Video        Allegro                Movie                  FALSE          Download      Retail Video         NA 2.29476e+01      2.76718e+01      19604366 1.17054e-06                     NA              NA
  ##   2014-05-01       1     iTunes          Video        Allegro            Video Etc                  FALSE          Download      Retail Video         NA          NA               NA            NA          NA                     NA               0
  DT.manual[storeid == 1 & music_vs_video == "Video" & label_sc_group == "Allegro", release_product_type := "Movie"]
  DT.manual[storeid == 1 & music_vs_video == "Video" & label_sc_group == "SelectO", release_product_type := "Web Videos"]

  ## key will be the date-store-subgroup columns
  setkeyIfNot(DT.manual, kCols.splitgroup, organize=TRUE, verbose=FALSE, superset.ok=FALSE)
}


# FOR FAKE DATA ::      ## TEMPORARY til I have future data
# FOR FAKE DATA ::      ## The only real data is May 2014, all else is fake
# FOR FAKE DATA ::      cols.measure <- c("estimate_current_month", "manual_addition", "budget")
# FOR FAKE DATA ::      dates <- setdiff(DT.manual$date, as.Date("2014-05-01"))
# FOR FAKE DATA ::      for (dat in dates) {
# FOR FAKE DATA ::        set.seed(1)
# FOR FAKE DATA ::        fctr <- month(as.Date(dat, origin=.origin)) - 5
# FOR FAKE DATA ::        DT.manual[.(dat), (cols.measure) := lapply(.SD, function(x)  x * (1.03^fctr + runif(length(x), -1e-3, 1e-3))), .SDcols=cols.measure]
# FOR FAKE DATA ::      }
# FOR FAKE DATA ::      rm(dates, cols.measure, fctr)


## CLEANUP 
rm(files)

cat("Done ingesting & processing manual template files\n")

### ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ###