## confirm analytics_release_level.r

confirm_release_level_table <- function(
      tbl.trk = "analytics_track_level"
    , tbl.rel = "analytics_release_level"
      ## dateCol to use
    , dateCol = ifelse(grepl("accounting", tbl.trk), "activity_month", "activity_date")  ## Alternate for debugging:   <- "thisrow_last_updated::DATE"
    , dateCol.rel = dateCol
    , dateCol.trk = dateCol
    , colsToPull = c("storeid", "countryid", "transac_typeid")
    , minDate = NULL # as.Date("2014-01-01")
    , schema  = "bi"
    , dbname  = "prod"
    , wh      = getWH_already_on(default=c("BI_ETL_JOBS_STANDARD", "LOOKER_WH_LARGE", "Science"))[[1]]
    , zArchive.old = FALSE
    , verbose = TRUE
) {

  ## ---------------------------------------------------------------- ##
  ## SETUP
  ## ---------------------------------------------------------------- ##
    verboseMsg(verbose, pasteR(92, x="="), "\nBeginning confirm_release_level_table() for tbl.rel = '", tbl.rel, "'\n", pasteR(92, x="="), sep="")

    ## Science porject should have already been set
    setScienceIfNot(proj="DeNormalizing", subProj="ReleaseLevelAggregation", verbose="auto")

    ## Don't need to reset snowflake
    ## setSnowflake(wh=wh, dbname=dbname, start=TRUE)

    ## Wait for warehouse
    sfWaitForWarehouse(wh=wh, turn_on=TRUE, N=20, max_iterations=300)
  ## ---------------------------------------------------------------- ##



  ## ----------- ##
  ## BEGIN       ##
  ## ----------- ##
  maxDate.rel <- qMaxDate(tbl=tbl.rel, schema=schema, dateCol=dateCol.rel, verbose=FALSE)
  maxDate.trk <- qMaxDate(tbl=tbl.trk, schema=schema, dateCol=dateCol.trk, verbose=FALSE)

  any_maxDate_isNA <- any(is.na(c(maxDate.rel, maxDate.trk)))
  if (any_maxDate_isNA) {
    warning("At least one of the maxDates was somehow NA", "\n\t maxDate.rel := ", maxDate.rel, "\n\t maxDate.trk := ", maxDate.trk)
    maxDate <- NULL
  } else if (maxDate.rel != maxDate.trk) {
    warning("\nThere is a different maxDate for each:", "\n\t maxDate.rel := ", maxDate.rel, "\n\t maxDate.trk := ", maxDate.trk)
    maxDate <- NULL
  } else {
    ## Otherwise, just take one
    maxDate <- maxDate.rel
  }

  if (tbl.trk == "Analytics_track_level") {
    colsToAgg <- c("units", "paidunits", "freeunits", "royaltydollar")
    colsWithaggFunc <- c(thisrow_min_fact_processeddaytime = "MIN (thisrow_min_fact_processeddaytime)", thisrow_max_fact_processeddaytime = "MAX (thisrow_max_fact_processeddaytime)")
    exclude_due_to_colsWithaggFunc = c("thisrow_min_fact_processeddaytime", "thisrow_max_fact_processeddaytime")
  } else if (tbl.trk == "accounting" || tbl.trk == "accounting_track_level") {
    colsToAgg <- c(
          "gross_revenue_usd_spotify_unreversed"
        , "units"
        , "unknown_fx_spread_usd"
        , "gross_revenue_usd"
        , "fees_mechanical_admin_usd"
        , "adjusted_gross_revenue_usd"
        , "fees_distribution_usd"
        , "client_net_receipt_usd"
        , "fees_dpd_publishing_usd"
        , "fees_ringtone_publishing_usd"
        # , "fees_cloud_publishing_usd"
        , "client_actual_net_usd"
        , "currency_exchange_rate_at_payout"
        , "gross_revenue_ccur"
        , "fees_mechanical_admin_ccur"
        , "adjusted_gross_ccur"
        , "fees_distribution_ccur"
        , "client_net_receipt_ccur"
        , "fees_dpd_publishing_ccur"
        , "fees_ringtone_publishing_ccur"
        # , "fees_cloud_publishing_ccur"
        , "client_actual_net_ccur"
      )
    colsWithaggFunc <- NULL
    exclude_due_to_colsWithaggFunc = NULL
  }

  DT.rel <- sfQry(makeQry(tbl.rel, schema=schema, minDate=minDate, maxDate=maxDate, colsToPull=c("date"=dateCol.rel, colsToPull), colsToAgg=colsToAgg, dateCol=dateCol.rel, limit=NULL))
  DT.trk <- sfQry(makeQry(tbl.trk, schema=schema, minDate=minDate, maxDate=maxDate, colsToPull=c("date"=dateCol.trk, colsToPull), colsToAgg=colsToAgg, dateCol=dateCol.trk, limit=NULL))

  ## Make sure the colsToAgg are numeric, not integers
  DT.rel[, (colsToAgg) := lapply(.SD, as.numeric), .SDcols=colsToAgg]
  DT.trk[, (colsToAgg) := lapply(.SD, as.numeric), .SDcols=colsToAgg]

  ## Check their sums
  totals.rel <- DT.rel[, lapply(.SD, sumn), .SDcols=colsToAgg]
  totals.trk <- DT.trk[, lapply(.SD, sumn), .SDcols=colsToAgg]
  totals.rel == totals.trk

  ## Merge into one Table
  matchKey(DT.rel, DT.trk, key=c("date", colsToPull), superset.ok=FALSE)
  sufxs <- c(".rel", ".trk")
  DT.rel_trk <- merge(DT.rel, DT.trk, all=TRUE, suffix=sufxs) %>% setcolorder_suffix()

  ## Compute some differences
  for (col in colsToAgg) {
    DT.rel_trk[, paste0(col, "_absdiff")  := {get(paste0(col, sufxs[[1]])) - get(paste0(col, sufxs[[2]]))}]
    DT.rel_trk[, paste0(col, "_percdiff") := {get(paste0(col, "_absdiff")) / get(paste0(col, sufxs[[1]]))}]
    DT.rel_trk[, paste0(col, "_hasdiff")  := {get(paste0(col, "_absdiff")) != 0}]
  }


  ## MANUALLY Investigate if needed
  {
    DT.rel_trk[, list(perc_of_rows_that_are_diff = percTrue(units_hasdiff)), keyby=countryid]
    DT.rel_trk[, fwp(percTrue(units_hasdiff)), keyby=countryid][V1!="0 %"] %>% print(nrow=300)
    DT.rel_trk[, fwp(percTrue(units_hasdiff)), keyby=storeid] %>% print(nrow=300)
  }

  ## Check if any of the "_hasdiff" columns are TRUE
  iexpr_hasdiff <- sprintf("%s_hasdiff", colsToAgg) %>% pasteC(C="|") %>% sprintf("(%s)", .)
  DT.mismatched <- DT.rel_trk[(eval(parse(text=iexpr_hasdiff)))]

  if (nrow(DT.mismatched)) {
    ## THERE IS AN ERROR
    f.mismatches <- writeDT(DT.mismatched, base.file.name=timeStamp("analytics_mismatches_between_rel_and_trk_levels"), message_on_no_rows=FALSE)
    subj <- "ERROR: Analytics Release Level does not match to Analytics Track Level"
    email(to=getTo("data"), subject=subj, body=paste(subj, "\n\nSee the attached file"), attachments=f.mismatches)
  } else {
    ## THERE IS NO ERROR
    subj <- "CONFIRMED: Analytics Release Level and Analytics Track Level match to each other"
    email(to=getTo("data"), subject=subj, body=paste(subj, "\n\n*No Action Needed*"))
  }

  return (nrow(DT.mismatched) == 0)
}
  ### ------------------- DONE --------------------- ###