NOTE ---- 2016-01-21 -- This was left halfdone.
      Need to get  get_dim_track_with_tuid()  Working first.  
      Then clean them

      I might abandon this and just use snowflake functinos instead







setScience("text_matching", create=TRUE)
setGitBranchToSystem(); .g()

wh <- getWH_already_on("SCIENCE")
dbname <- "prod"

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

get_track_id_from_fuzzy_track_name <- function(track_name, idCol=c("track_unique_id", "trackid"), refresh_dim_track=FALSE, full_return=FALSE, verbose=TRUE) {
  # DT.ret <- data.table(
  #     track_name=track_name
  #   , track_name_semi_cleaned = clean_names_to_simple_alpha(track_name, whitespace=FALSE, inside_parens=TRUE, parens=TRUE, trim=TRUE, tolower=TRUE, duplicate_whitespace=TRUE)
  #   , track_name_cleaned = track_name
  #   )

  idCol <- match.arg(idCol)

  DT.tracks_cleaned <- get_dim_track_cleaned(refresh_dim_track=refresh_dim_track, verbose=TRUE, idCol=idCol)

  # DT.ret <- data.table(track_name=iconv(track_name, to=convert_to, from=convert_from))
  DT.ret <- data.table(track_name=track_name, row_number=seq_along(track_name))
  add_cleaned_column_(DT.ret, cols=c("track_name"))

  ## Add in matches
  sfxs <- c("", "_cleaned", "_cleaned_nows")
  message("You are about to get three warnings for 'bmerge(...) .. known encoding UTF-8 ...' \nYou can disregard these")
  for (n in sfxs) {
    kCol <- sprintf("track_name%s", n)
    iCol <- sprintf("csv.tid_by_name%s", n)
    # j.expr <- sprintf("(%s) := i.%1$s", iCol)
    matchKey(DT.ret, DT.tracks_cleaned, keyCols=kCol, superset.ok=FALSE, verbose=FALSE)
    DT.ret[unique(DT.tracks_cleaned, by=kCol), (iCol) := get(iCol)]
    catn()
  }

  ## We ultimately want to create a tall table, with each track_name repeaeted as many rows 
  ##  as it has matches for trackid
  ##  and indicate in each row whether the match is exact or using 'name_cleaned' or 'name_cleaned_nows'
  ## 
  ##  To execute, strsplit each CSV to create three different DTs, adding match_type to each DT
  ##  Stack the DTs together, set match_type to a factor (so that we can find its 'min' easily)
  ##  Then take the min match_type by trackid 
  ##  Take along the metadata, ie the 'byCols'

  ## Metadata columns
  byCols <- paste0("track_name", sfxs) %>% c("row_number", .)
  ## Which columns to split, one at a time
  parseCols <- sprintf("csv.tid_by_name%s", sfxs)

  ### Split and Stack them
  DT.large_ret <- lapply(parseCols, function(pCol) {
    DT.ret[, strsplit(get(pCol), ","), by=byCols][, match_type := removeText("csv.tid_", pCol)]
  }) %>% rbindlist %>% setnames("V1", "trackid")

  ## Set to factor to find the 'min'
  DT.large_ret[, match_type := toFactorWithExpectedLevels(match_type, paste0("by_name", sfxs))]

  ## Take the min (ie, sort then take the first), by each trackid-metadata combination
  return(
    DT.large_ret[, list(match_type = sort(match_type)[[1]]), keyby=c(byCols, "trackid")]
  )
}


get_dim_track_cleaned <- function(DT.tracks_cleaned=copy(get_dim_track_with_tuid(refresh=refresh_dim_track)), idCol=c("track_unique_id", "trackid"), refresh_dim_track=FALSE, confirm_unique_hash=TRUE, verbose=TRUE) {
## idCol is which column of DT.tracks_cleaned to use as the ID
##    trackid is the actual  id in the redshift tables  (production.dim_track)
##    track_unique_id is the id in the MySQL tables (reportsAR, reportsDD)

  stopifnot(require(digest))

  idCol <- match.arg(idCol)

  force(DT.tracks_cleaned)

  verboseMsg(verbose, "Getting DT.tracks_cleaned  -------------------------------- ")

  if (!all(c("track_name_cleaned", "track_name_cleaned_nows") %in% names(DT.tracks_cleaned))) {
    verboseMsg(verbose, "Adding track_name_cleaned / track_name_cleaned_nows to DT.tracks_cleaned", sep="", time=FALSE)
    add_cleaned_column_(DT.tracks_cleaned, cols="track_name", whitespace="both")
  }

  ## Any special flags for track?
  # DT.tracks_cleaned[, is_various_tracks := is_various_tracks(track_name_cleaned, already_cleaned=TRUE)]

  ## Create a flat string of IDs per each name, and cleaned name
  verboseMsg(verbose, "Adding CSV by name for each column")
  if ("csv.tid_by_name"               %ni% names(DT.tracks_cleaned)) DT.tracks_cleaned[, csv.tid_by_name               := vector_to_flat_string(trackid), by=track_name]
  if ("csv.tid_by_name_cleaned"       %ni% names(DT.tracks_cleaned)) DT.tracks_cleaned[, csv.tid_by_name_cleaned       := vector_to_flat_string(trackid), by=track_name_cleaned]
  if ("csv.tid_by_name_cleaned_nows"  %ni% names(DT.tracks_cleaned)) DT.tracks_cleaned[, csv.tid_by_name_cleaned_nows  := vector_to_flat_string(trackid), by=track_name_cleaned_nows]

  ## Hash them for smaller strings
  verboseMsg(verbose, "Adding HASH by name for each column")
  if ("hash.tid_by_name"               %ni% names(DT.tracks_cleaned)) DT.tracks_cleaned[, hash.tid_by_name               := digest(csv.tid_by_name,              algo="murmur32")]
  if ("hash.tid_by_name_cleaned"       %ni% names(DT.tracks_cleaned)) DT.tracks_cleaned[, hash.tid_by_name_cleaned       := digest(csv.tid_by_name_cleaned,      algo="murmur32")]
  if ("hash.tid_by_name_cleaned_nows"  %ni% names(DT.tracks_cleaned)) DT.tracks_cleaned[, hash.tid_by_name_cleaned_nows  := digest(csv.tid_by_name_cleaned_nows, algo="murmur32")]

  if (confirm_unique_hash) {
    verboseMsg(verbose, "Confirming unique hash by name ... ")
    confirmed <- TRUE
    for (sfx in c("", "_cleaned", "_cleaned_nows"))
      if (1 != nrow(unique(DT.tracks_cleaned, by=c(paste0("csv.tid_by_name", sfx)))[, .N, by=c(paste0("hash.tid_by_name", sfx))])) {
        warning("uniqueness of the hash may NOT be confirmed for sfx == ", sfx, "\n")
        confirmed <- FALSE
      }
    if (confirmed)
      verboseMsg(verbose, "uniqueness of the hash confirmed in get_dim_track_cleaned()", time=FALSE)
  } 


  verboseMsg(verbose, "Done Getting DT.tracks_cleaned  -------------------------------- ")
  return(invisible(DT.tracks_cleaned))
}