## This function should be ran after done creating Analytics_track_level
## It aggregates out tracks from that table 


get_manually_exclude_cols_for_release_level <- function(tbl, exact=FALSE, warn_on_blank=TRUE) {

  is.char_of_length1(tbl, fail=TRUE)

  if (!exact) {
    if (grepl("^accounting", tolower(tbl)))
      tbl <- "accounting"
    else if (grepl("^analytics", tolower(tbl)))
      tbl <- "analytics_track_level"
  }

  ret <- NULL
  if (tbl == "analytics_track_level")
    ret <- c("row_number"
            , "gpuest", "gest", "gest_net_of_sc"
            , "activity_month544_start", "activity_month544_end", "accounting_max_date_by_group"
            , "accounting_days_in_month", "accounting_days_present", "accounting_days_missing", "paidunits_expected_ratio")
  if (tbl == "accounting")
    ret <- c("row_number", "retail_price", "partner_commission"
            , "fees_cloud_publishing_usd", "fees_cloud_publishing_ccur"
      )

  if (is.null(tbl) && warn_on_blank)
    warning("No list of columns in get_manually_exclude_cols_for_release_level()  for tbl = '", tbl, "'")

  return(ret)
}


create_release_level_table <- function(
      tbl.trk  = "analytics_track_level"
    , tbl.rel = "analytics_release_level"
    , manually_exclude = get_manually_exclude_cols_for_release_level(tbl=tbl.trk)
    , 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 create_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, N=20, max_iterations=300)
  ## ---------------------------------------------------------------- ##



  ## ----------- ##
  ## BEGIN       ##
  ## ----------- ##

  ## USE a "_next" setup
  tbl.rel_final <- copy(tbl.rel)
  tbl.rel %<>% paste0("_next")

  all_cols <- sfShowCols(schema=schema, tbl=tbl.trk, dbname=dbname) %>% tolower


  # manually_exclude <- c("row_number"
  #                     , "gpuest", "gest", "gest_net_of_sc"
  #                     , "activity_month544_start", "activity_month544_end", "accounting_max_date_by_group", "accounting_days_in_month", "accounting_days_present", "accounting_days_missing", "paidunits_expected_ratio")

  ## These are columns that have the keyword TRACK or ISRC in them but are not actually TRACK COLUMNS
  non_track_cols <- c("release_number_of_tracks", "album_vs_track")
  track_cols <- extract("track|isrc", all_cols) %>% setdiff(non_track_cols)

  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
  }

  ## Which cols to exclude from colsToPull
  colsNotToPull <- colNamesFromVector(c(colsToAgg, exclude_due_to_colsWithaggFunc, track_cols, manually_exclude))
  colsToPull <- setdiff(all_cols, colsNotToPull)

  qry.select_release_level <- makeQry(dbname=dbname, schema=schema, tbl=tbl.trk, colsToPull=colsToPull, colsToAgg=colsToAgg, colsWithaggFunc=colsWithaggFunc, limit=NULL)

  qry.create <- setQry(sprintf(
  "CREATE OR REPLACE TABLE %s
  COMMENT = 'Aggregated from %s [Autogenerated via R Script]'
  AS
  (
    %s
  )
  "
    , dbschematbl(dbname=dbname, schema=schema, tbl=tbl.rel)
    , tbl.trk
    , qry.select_release_level
  ))

  sfQry(qry.create)


  rows.in <-  qRowCount(dbname=dbname, schema=schema, tbl=tbl.trk,  snowflake_inuse=TRUE)
  rows.out <- qRowCount(dbname=dbname, schema=schema, tbl=tbl.rel, snowflake_inuse=TRUE)

  DT.row_count <- data.table(track_level = rows.in, release_level = rows.out)
  DT.row_count[, diff := rows.in - rows.out]
  DT.row_count[, perc_diff := (rows.in - rows.out) / rows.in]

  print(formnumb(DT.row_count)[])

  ## After queries complete, replace the existing table with the temp table we were populating
  verboseMsg(verbose, "Swapping in the new ", tbl.rel_final, " table", func="message")
  swap_in_new_table(tbl_old=tbl.rel_final, tbl_new=tbl.rel, schema=schema, zArchive.old=zArchive.old, wh=wh, dbname=dbname)

  verboseMsg(verbose, "Done with creating analytics_release_level -- you might want to call the confirm function", func="message")

  return(TRUE)
}