if (getProjName() != "Spotify_Accounting_ETL") {
  fresh()
  setScience("Spotify_Accounting_ETL", create=TRUE, subl=FALSE, load=TRUE)
}

f.in.rates <- ingest.p("Spotify_Rates", ext=".xlsx")

ll.SpotifyRates <- XLStoDT(f.in.rates)
DT.SpotifyRates <- rbindlist(ll.SpotifyRates)

## NAME CLEANUP
{
  ## Remove double spaces and trim
  nms <- cleanWS(names(DT.SpotifyRates))
  ## Move the Product names to end of string
  nms <- gsub("(Spotify )([A-Za-z]+?)( )(.*)", "\\4.\\2", nms, ignore.case=TRUE)

  ## "implied retail" becomes "retail"
  nms <- gsub("implied retail", "retail", nms, ignore.case=TRUE)

  ## "label pool" becomes "orch pool"
  nms <- gsub("label pool", "orch pool", nms, ignore.case=TRUE)

  ## manually clean up some names
  nms[nms == "Curr"] <- "currency"
  nms[nms == "Territory"] <- "country"
  nms[nms == "sheetSource"] <- "year"
  
  ## spaces to underscores
  nms <- spaceToUnderscore(nms)
  ## make lowercase
  nms <- tolower(nms)

  # Row 1 is part of the header
  {
    row1 <- DT.SpotifyRates[1]
    row1[, sheetSource := NA]
    row1 <- removeNA(trim(unlist(row1)), "")
    row1 <- gsub("\\(", "_", gsub("\\)", "", row1))
    nms <- paste0(nms, row1)
  }

  setnames(DT.SpotifyRates, nms)
}

## The headers are actually two rows, this was captured into the names via 'row1' above. 
## Now we need to drop those rows. It should be one row per year
##  They can be identified by NA in the country column
{
    ## CONFIRM: one such row per year
    stopifnot(DT.SpotifyRates[is.na(country), .N==1, by=year][, V1])
    ## drop said row
    DT.SpotifyRates <- DT.SpotifyRates[!is.na(country)]
}

## -- DEEP COPY ---
## Some "country" values are actually larger region headers
## They are identified by the rest of rows being NA (other than year)
DT.SpotifyRates <- ConvertExcelHeaderRowsInColumn(DT.SpotifyRates, col="country", nm.newCol="region", markerCols=setdiff(names(DT.SpotifyRates), c("country", "year")))

## Convert numeric columns
convertNumberCols_(DT.SpotifyRates)

## Convert string columns
charCols <- nwhich(sapply(DT.SpotifyRates, is.character))
DT.SpotifyRates[, (charCols) := lapply(.SD, function(x) cleanWS(gsub(intToUtf8(160), " ", x))), .SDcols=charCols]

## -------------------------------------------------------- ###
## Fix countries
## -------------------------------------------------------- ###
  dict.countries_alt <- c("UK" = "GB", "UAE" = "AE", "Taiwan" = "TW", "Serbia" = "RS",  "New-Zealand"="NZ", "Bosnia / Herz" = "BA", "Vietnam" = "VN", "Dominican Rep" = "DO", "Venezuela" = "VE", "Mongolia" = "MN")
  DT.country <- get_dim_country(refresh=FALSE)


  ## CHECK FOR MISSING COUNTRIES
  {
    missing_countries <- setdiff(DT.SpotifyRates$country, DT.country$country_name)
    missing_countries <- setdiff(missing_countries, names(dict.countries_alt))
    if (length(missing_countries))
      print(DT.country[rowSums(sapply(missing_countries, function(x) grepl(substr(tolower(x), 1, 3), tolower(country_name)))) >= 1])
  }

  ## Add in the country code
  addColsFrom_(DT.SpotifyRates, DT.country, colsToBring="country_code", "country", "country_name")
  ## Add in the Alternates
  DT.SpotifyRates[is.na(country_code), country_code := dict.countries_alt[country]]
## -------------------------------------------------------- ###


kCols.terr <- c("region", "country_code", "country", "currency", "year")
setkeyIfNot(DT.SpotifyRates, kCols.terr, organize=TRUE, verbose=FALSE)
DT.SpotifyRates

jesusForData(DT.SpotifyRates, verbose=FALSE)
print(saveImageTo(subProj="After Ingesting Rates"))