## deezer marketshare - process files.r
screen -xRR deezer

setScience(proj="marketshare", subProj="deezer", create=TRUE)
lib(rvest)

wh <- "looker_wh_large"
dbname <- "prod"
setSnowflake(wh=wh, dbname="prod")


# dir("/ODrive/Streaming DMS Revenue Summaries/Deezer/2015")
folder <- "/ODrive/Streaming DMS Revenue Summaries/Deezer/2015/2015-08/TheOrchard_201508/"
if (.Pfm == "Darwin")
  folder <- "~/Desktop/TheOrchard_201508 (Deezer)/"

## Confrim is a dir
stopifnot(file.exists(folder), isdir(folder))

files <- extractFilesFromFolder(folder, pattern="country_", ext="html", full=TRUE)
names(files) %<>% removeText(pattern="(country_|\\.html)")

stopifnot(length(files) > 0)


deezer.transpose_to_datatable <- function(X) {
  # setnames(as.data.table(t(X[, 2])), X[, 1])
  nms <- tolower(X[, 1])
  nms[[1]] <- "what"
  nms[nms == "period"] <- "activity_month"
  nms[nms == "territory"] <- "country_name"
  nms <- gsub("blogmusik offer", "product", nms)
  nms <- gsub("blogmusik", "deezer", nms)
  nms <- gsub("the orchard's", "orchard", nms)
  nms <- gsub("revenues", "revenue", nms)
  nms <- gsub("number of streams", "streams", nms)
  X[, 2] %>% t %>% as.data.table %>% setnames(nms) %>% cleanColNamesForSQL_
}


## for (nm in names(files)) {}
f <- files[["FR"]]

ll_DTs <- emptylist(files)
verboseMsg(verbose, "Will process", length(files), "files")
for (nm in names(files)) {
  verboseMsg(verbose, "Processing '", nm, "' (", which(names(files) == nm), " of ", length(files), ")", sep="", seconds=TRUE)
  f <- files[[nm]]

  tmp_DT <- read_html(f) %>% html_nodes("table") %>% html_table %>% lapply(deezer.transpose_to_datatable) %>% rbindlist(fill=TRUE, use.names=TRUE)

  ## remove "INVOICE STATEMENT DETAILS :"
  pat.st_details <- "^INVOICE STATEMENT DETAILS :\\s*"
  stopifnot(grepl(pat.st_details, tmp_DT$what))
  tmp_DT[, what := removeText(pat.st_details, what)]

  ## Drop the column "what" if it is the same as product
  if (all (tmp_DT[, what == product]))
    tmp_DT[, what := NULL]

  ## Convert Date. Use if-statement to ensure we don't run this twice
  if (!is.Date(tmp_DT$activity_month))
    tmp_DT[, activity_month := as.Date(paste0(activity_month, "01"), format="%Y%m%d")]

  ## extract currency code
  cols_with_curr <- nwhich(tmp_DT[, lapply(.SD, function(x) any(grepl("USD|EUR", x)))])
  tmp_DT[, currency_code := strsplit(deezer_revenue, " ") %>% sapply(tail, 1)]
  cols_with_curr2 <- paste0(cols_with_curr, "_2")
  tmp_DT[, (cols_with_curr2) := currency_code %>% unique %>% sprintf("\\s%s$", .) %>% {lapply(.SD, function(x) removeText(x=x, pat=.)%>% currToNumeric)}, by=currency_code, .SDcols=cols_with_curr]
  for (col in cols_with_curr) {
    col2 <- paste0(col, "_2")
    if (tmp_DT[, all(!xor(is.na(get(col)), is.na(get(col2))))]) {
      tmp_DT[, (col) := get(col2)]
      tmp_DT[, (col2) := NULL]
    } else {
      warning("It appears that NA's were introduced when cleaning up column '", col, "'")
    }
  }

  stream_cols <- extract("streams", tmp_DT)
  tmp_DT[, (stream_cols) := lapply(.SD, function(x) removeText(pat=intToUtf8(160), x=x) %>% as.numeric), .SDcols=stream_cols]

  tmp_DT[, country_code := nm]
  ll_DTs[[nm]] <- tmp_DT
}

DT.deezer <- rbindlist(ll_DTs, use.names=TRUE, fill=TRUE)

## TODO: Confirm that country_name and country_code match

## CALCULATE MARKETSHARE
{
  catheader("By Product", endl=0)
  DT.deezer_ww_marketshare_by_product <- DT.deezer[, list(orchard_streams=sum(orchard_streams), total_deezer_streams=sum(deezer_streams)), by=product][, marketshare := orchard_streams / total_deezer_streams] []
  print(DT.deezer_ww_marketshare_by_product)
  catheader("Overall", endl=0)
  DT.deezer_ww_marketshare <- DT.deezer[, list(orchard_streams=sum(orchard_streams), total_deezer_streams=sum(deezer_streams))][, marketshare := orchard_streams / total_deezer_streams] []
  print(DT.deezer_ww_marketshare)
}

writeDT(DT.deezer_ww_marketshare)
writeDT(DT.deezer_ww_marketshare_by_product)


sourceSupportFns(proj="looker")