## from O:\ Drive "iTunes Hidden Content"

if (FALSE) {
  setScience(proj="ContentAnalysis", subProj="Hidden", subl=FALSE, load=FALSE)
  setGitBranchToSystem(); .g()
  subfolder="iTunes Hidden Content"
}


get_DT.hidden_raw <- function(subfolder="iTunes Hidden Content") {
  
  folders <- ingest.p(subfolder, c("ORCH", "IODA", "IRIS"))

  files.xlsx <- sapply(folders, extractFilesFromFolder, ext="xlsx", recursive=TRUE, full=TRUE)

  .failures <<- c()
  i <- 0; for (file in unlist(files.xlsx)) {
    f.csv <- sub("\\.xlsx$", ".csv", file)
    if (!file.exists(f.csv))
      tryCatch({
        verboseMsg(verbose, sprintf("%5i : %s", i <- i + 1, file), minw=128, sep="")
        DT <- XLStoDT(file, verbose=FALSE)
        ## Some files have more than one sheet
        if (!is.data.table(DT) && is.list(DT)) {
          ## if one of the sheets is named similar to hidden music, use that
          if (length(sh.nm <- extract("HiddenMusic", names(DT))) == 1)
            DT <- DT[[sh.nm]]
          else
            stop ("\n", file, "\nhas more than one sheet and not sure which to use:\n\t", pasteQand(names(DT)))
        } 
        writeDT(DT, full_file_path=f.csv)
      }, error = function(e) {
        catn("file '", path.unexpand(file), "' failed with error: \n\t", e$message, sep="")
        Sys.sleep(0.5)
        .failures <<- c(.failures, file)
      })
  }

  files.csv <- sapply(folders, extractFilesFromFolder, ext="csv", recursive=TRUE, full=TRUE)
  stopifnot(length(files.csv) == length(files.xlsx))

  s.t({
    ll_DT <- unlist(files.csv) %>% lapply(function(f) {print(f); fread(f, sep="\t", header=TRUE)[, c("date", "content_owner") := list(extract_date_from_filename.hidcont(f), content_owner_from_filename(f))]})
    stopifnot(length(ll_DT) == length(unlist(files.csv)))
    DT.hidden_raw <- rbindlist(ll_DT, fill=TRUE, use.names=TRUE)
    if (DT.hidden_raw[!is.na(Col4), all(Col4 == Grid)])
      invisible(DT.hidden_raw[, Col4 := NULL])

    ## Manual correction -- just to keep the dates in line
    DT.hidden_raw[date == "2013-10-25", date := date - 1]

  }, title="fread in the list of DTs then rbind them")

  DT.hidden_raw[, sheetSource := NULL]
  if (all(DT.hidden_raw[, length(unique(Provider)), by=content_owner]$V1 == 1))
    DT.hidden_raw[, Provider := NULL]

  ## these are the columns to make sure the table is unique by
  cols_to_unique_by <- c("Apple Id", "UPC/EAN/JAN", "Vendor ID", "Artist", "Album", "Label Name", "Reason", "content_owner")
  ## Confirm there are no NA's in date
  stopifnot(!DT.hidden_raw[, any(is.na(date))])

  ## Set key to make sure min date is first non-duplicate
  setkeyIfNot(DT.hidden_raw, c("date", cols_to_unique_by[1:3]), organize=TRUE, verbose=FALSE)
  DT.hidden_raw[, .is_dup := duplicated(.SD), .SD=cols_to_unique_by]

  ## Drop the duplicates
  DT.hidden_raw <- DT.hidden_raw[(!.is_dup)]
  DT.hidden_raw[, .is_dup := NULL]

  ## THERE ARE six (approx) rows of data that are corrupted (it looks like previous line did not read correctly)  Drop them
  if (nrow(    tmp_DT.albs <-   DT.hidden_raw[grepl("\t", Album), list(date, content_owner, Album)]  ))
  {
    tmp_DT.albs <- unique(tmp_DT.albs[, strsplit(Album, "\n"), keyby=list(date, content_owner)], by="V1")
    tmp_DT.albs <- tmp_DT.albs[, strsplit(V1, "\t") %>% lapply(function(x) as.data.table(t(x))) %>% rbindlist(fill=TRUE) %>% {.[!is.na(get(tail(names(.),1)))]}, keyby=list(date, content_owner)]
    stopifnot(tmp_DT.albs$V3 == "")
    tmp_DT.albs[, V3 := NULL]
    nms.albs <- c("date", "content_owner", "Apple Id", "UPC/EAN/JAN", "Vendor ID", "Artist", "Album", "Genre", "Total Tracks", "Total Discs", "Is Complete", "Provider", "Label Name", "Audio must be redelivered for iTunes Plus", "Reason")
    setnames(tmp_DT.albs, nms.albs)
    tmp_DT.albs[, `Is Complete` := as.logical(toupper(`Is Complete`))]
    for (nm in copy(names(tmp_DT.albs)))
      tmp_DT.albs[, (nm) := as(get(nm), Class=class(DT.hidden_raw[[nm]]))]
    tmp_DT.albs[, from_funky := TRUE]

    DT.hidden_raw[grepl("\t", Album), Album := NA]
    DT.hidden_raw <- rbind(DT.hidden_raw, tmp_DT.albs, fill=TRUE)
  }

  ## SECOND CLEANING NEEDED; Doing the first cleaning then the second helps move it faster
  DT.hidden_raw[, Artist2 := clean_names_to_simple_alpha(Artist, whitespace=TRUE)]
  DT.hidden_raw[, Album2 := clean_names_to_simple_alpha(Album, whitespace=TRUE)]
  DT.hidden_raw[, Label2 := clean_names_to_simple_alpha(`Label Name`, whitespace=TRUE)]


  ## identify if any are ringtones
  rt <- {. %>% grepl(pat="ring(\\s|_)*tone", x=., ignore.case=TRUE)}
  DT.hidden_raw[, is_ringtone := rt(Artist) | rt(Album) | rt(Genre) | rt(`Label Name`) | rt(Reason)]
  DT.hidden_raw[, is_various_artists := is_various_artists(Album2, already=TRUE)]


  ## DROP THE NEXT BATCH OF DUPs
  DT.hidden_raw[, .is_dup := duplicated(.SD), .SD=gsub("(Album|Artist|Label)( Name)?", "\\12", cols_to_unique_by)]
  DT.hidden_raw <- DT.hidden_raw[!(.is_dup)]
  DT.hidden_raw[, .is_dup := NULL]

  ## THIRD BATCH OF DUPS THE NEXT BATCH OF DUPs
  DT.hidden_raw[, .is_dup := duplicated(.SD), .SD=c(cols_to_unique_by[1:3], c("Artist", "Album2", "Reason"))]
  DT.hidden_raw[(.is_dup)][date >= "2015-01-01"][Reason == "Hidden: Refusal"]
  DT.hidden_raw[(.is_dup)][date >= "2015-05-01"][Reason == "Hidden: Editorial Discretion"]
  DT.hidden_raw[(.is_dup)][date >= "2015-05-01"][, .N, by=Reason]
  DT.hidden_raw[`Apple Id` == 160024873]
  DT.hidden_raw[`Apple Id` == 259918322]

  DT.hidden_raw[`Vendor ID` == 471626]
  DT.hidden_raw[`Vendor ID` == 815565014177]
  DT.hidden_raw[`Vendor ID` == 829410989152]

  DT.hidden_raw <- DT.hidden_raw[!(.is_dup)]
  DT.hidden_raw[, .is_dup := NULL]


stop("You left this mid-complete in   '~/git/orch/src/ContentAnalysis/supportFns/get_DT.hidden_raw.r' ")

#  ~~~~~~~~~~~~~~~
#  setnames(DT.hidden_raw, "UPC/EAN/JAN", "upc")
#  setIDCols(DT.hidden_raw)
#  DT.hidden_raw[upc == 803680584958]
#  addColsFrom_(DT.hidden_raw, DT.releases_with_metadata, colsToBring=c("label_client_manager", "label_name", "label_owner"), joinCols="upc")
#  DT.hidden_raw[(is_dup) & content_owner == "IODA"]
#  upcs2 <- DT.hidden_raw[, .N, by=list(date, upc=as.character(upc))][N>1, upc]
#  DT.hidden_raw[upc %in% upcs2, .N, keyby=content_owner]
#  DT.hidden_raw[upc %in% DT.hidden_raw[upc != 0 & content_owner == "IODA", intersect(upc, upcs2)]][order(upc, date)][, upc := as.character(upc)][]
#  ~~~~~~~~~~~~~~~
#  tmp_DT.with_dup_upcs <- setIDCols(unique(DT.hidden_raw[, list(.N, content_owner), by=list(upc=upc, reason=Reason, aid=`Apple Id`)][N>1], by=NULL))
#  tmp_DT.with_dup_upcs[, list(N=lunique(content_owner)), by=upc][N>1]
#  DT.hidden_raw[upc == 884385455583]
#  DT.hidden_raw[upc == 669910204429]
#  ioda_upcs <- tmp_DT.with_dup_upcs[content_owner == "IODA", upc]
#  DT.hidden_raw[upc %in% DT.hidden_raw[upc %in% ioda_upcs][date >= "2015-08-18"][date <= "2015-09-11"]$upc][order(upc, date)]
#  
#  
#  DT.hidden_raw[upc %in% upcs2, .N, keyby=content_owner]
#  DT.hidden_raw[upc %in% DT.hidden_raw[upc != 0 & content_owner == "IODA", intersect(upc, upcs2)]][order(upc, date)][, upc := as.character(upc)][]
#  ~~~~~~~~~~~~~~~


  DT.hidden_raw[(is_ringtone)]
  DT.hidden_raw[, .N, keyby=list(upc=as.character(`UPC/EAN/JAN`), `Reason`)][N>1]
  DT.hidden_raw[ `UPC/EAN/JAN` == 13491160528]

  ## Should be logical
  DT.hidden_raw[, `Is Complete` := as.logical(toupper(`Is Complete`))]

  ## Should be logical
  DT.hidden_raw[, `Audio must be redelivered for iTunes Plus` := toupper(`Audio must be redelivered for iTunes Plus`) == "YES"]



  return(DT.hidden_raw)
  ## ------------------------------------------------------------ ##
  ## This is an internal check to check row counts, etc
  ## ------------------------------------------------------------ ##
  ## DT.hidden_raw[date >= "2013-09-10" & date <= "2014-11-30", .N, keyby=list(date)][, d := diffNA(date)][]
  ## 
  ## tmp_DT.counts <- DT.hidden_raw[, .N, keyby=list(date, content_owner)] %>% dcast(date ~ content_owner) %>% setDT %>% melt(id.var="date")
  ## tmp_DT.counts[, msng := as.integer(is.na(value))]
  ## tmp_DT.counts[, min(which(!msng)), by=variable]
  ## tmp_DT.counts[variable != "ORCH" & date <= "2013-01-04", msng := 0]
  ## ggBarchart2(tmp_DT.counts, fill="variable", y="msng", x="date")
  ## tmp_DT.counts[as.logical(msng)]
  ## ------------------------------------------------------------ ##
}