# Apple Music Sony Figures Export To Snowflake.r

fresh(notify=FALSE);
setJavaMemoryParameter(initial=2048, max=4096*2)
setScience("AppleMusic", subProj="SonyFigures", create=TRUE, subl=FALSE, load=FALSE)
# setJavaMemoryParameter(initial=2048, max=4096*2)
# library(rJava)
# setJavaMemoryParameter(initial=2048, max=4096*2)

# setGitBranchToSystem(); .g()
# 
if (.Pfm != "Darwin") {
  schema_out <- "AppleMusic"
  wh <- getWH_by_interactive()
  dbname <- "prod"
}

options(snowflake_inuse=TRUE)


dict.country_names <- c(
  #                                      ISO  = SONY
                       "Antigua and Barbuda"  = "Antigua & Barbu"
  ,                     "Dominican Republic"  = "Dominican Rep."
  ,                             "Kyrgyzstan"  = "Kyrgyztan"
  # ,  "Lao People&apos;s Democratic Republic"  = "Laos"
  # ,        "Micronesia, Federated States of"  = "Micronesia"
  # ,                   "Moldova, Republic Of"  = "Moldova"
  ,                       "Papua New Guinea"  = "Papua NewGuinea"
  ,                               "Slovakia"  = "Slovakia Rep."
  ,                  "Saint Kitts and Nevis"  = "St. Kitts&Nevis"
  ,                   "United Arab Emirates"  = "Arab Emirates"
  ,                         "United Kingdom"  = "Great Britain"
  # ,                          "United States"  = "USA"
  ,                "British Virgin Islands"  = "Virgin Islands (Brit.)"
)

dict.am_sony_names <- 
c("SME Reporting Period" = "activity_month544"
  , "Reporting Currency (RC)" = "Reporting Currency"
  , "Affiliate Country" = "country_name"
  , "Country Key" = "country_code"
  , "Total Royalty Bearing Broadcast Plays" = "AppleMusic RB Broadcast Plays"
  , "Label Royalty Bearing Broadcast Plays" = "SME RB Broadcast Plays"
  , "Label Proportionate Share" = "SME Proportionate Share"
  , "Total Royalty Bearing Plays" = "AppleMusic RB Streams"
  , "Label Royalty Bearing Plays" = "SME RB Streams"
  , "Period" = "apple_reporting_period")


if (old_method.using_xlsx <- FALSE) {
  f.sony.xlsx <- extractIngestFiles(subFolder="SonyFigures", ext=c("xls", "xlsx")) %>% {setdiff(., extract("(simple|upto \\d{4})", .))}
  stopifnot(length(f.sony.xlsx) == 1, file.exists(f.sony.xlsx))
  ll_DT.sony <- XLStoDT(f=f.sony.xlsx, sheets=c("Apple Music", "Linear Radio"), sheetAsCol=FALSE)
} else {
## NEW METHOD;  HAVE TO PREVIOUSLY EXPORT TO CSV
  f.sony.csvs <- extractIngestFiles(subFolder="SonyFigures/csvs", ext=c("csv"))
  stopifnot(length(f.sony.csvs) == 2, file.exists(f.sony.csvs))
  names(f.sony.csvs) %<>% removeText("(^Apple Music Statement Rollup \\- |\\.csv$)", .)
  ll_DT.sony <- lapply(f.sony.csvs, function(x) suppressWarnings(fread(x)))
}
BackUpOrRestore("ll_DT.sony", force=TRUE)

for (i in seq(ll_DT.sony)) {
  catheader("DT:  '", names(ll_DT.sony)[[i]], "'")
  # ll_DT.sony[[i]] %>% setnames("SME Reporting Period", "activity_month544") %>% cleanColNamesForSQL_
  ll_DT.sony[[i]] %>% setnamesByDict(dict=dict.am_sony_names, warn_for_new_return=FALSE, showWarnings=FALSE) %>% cleanColNamesForSQL_ %>% 
     {.[, membership_mode := NA_character_]} %>%
     {.[grepl("TRIAL", service_channel), membership_mode := "TRIAL"]} %>%
     {.[grepl("PAID",  service_channel), membership_mode := "PAID"]} %>%
     {.[, membership := topropper(removeText("\\s*(PAID|TRIAL)", service_channel))]} %>%
     {.[, activity_month544 := as.Date(activity_month544)]} %>%
     ## Any column with a "-" is most likely a numeric
     { for (nm in names(.))
         if (is.character(.[[nm]]) && any("-" == .[[nm]]) && !all(is.na(.[[nm]]))) {
            catn("Converting '", nm, "' to numeric", sep="")
            .[, (nm) := as.num.nowarn(get(nm))]
         }
     }
}

DT.AppleMusic_FiguresFS  <- copy(ll_DT.sony[["Apple Music"]])
DT.LinearRadio_FiguresFS <- copy(ll_DT.sony[["Linear Radio"]])

## If these have no length, something went wrong above with ll_DT.sony <- ... 
stopifnot(length(DT.AppleMusic_FiguresFS) > 0)
stopifnot(length(DT.LinearRadio_FiguresFS) > 0)

## Make sure the 'streams' columns in the table, if present, are numeric
extract("streams", DT.AppleMusic_FiguresFS) %>% { 
  if (length(.))
    DT.AppleMusic_FiguresFS[, c(.) := lapply(.SD, as.numeric), .SDcols=.]
}
extract("streams", DT.LinearRadio_FiguresFS) %>% { 
  if (length(.))
    DT.LinearRadio_FiguresFS[, c(.) := lapply(.SD, as.numeric), .SDcols=.]
}

for (nm in names(dict.country_names)) {
  cn <- dict.country_names[[nm]]
  DT.AppleMusic_FiguresFS[ country_name == cn, country_name := nm]
  DT.LinearRadio_FiguresFS[country_name == cn, country_name := nm]
}

## This portion was meant to add in country_code from DT.country by joining on country_name
## I wrote this before realizing that the XLS files have country_key which simply were not getting pulled in via the XLConnect
## But now that the sheets are manually converted to CSV, they ARE available
if (FALSE) {

    ## confirm no country codes are missing
    ### ------------------------------------------ ###
        DT.country <- get_dim_country(refresh=FALSE)

        missing_countries.AppleMusic_FiguresFS <- setdiff(DT.AppleMusic_FiguresFS$country_name, DT.country$country_name)
        missing_countries.LinearRadio_FiguresFS <- setdiff(DT.LinearRadio_FiguresFS$country_name, DT.country$country_name)

        if (length(missing_countries.AppleMusic_FiguresFS))
          warning(warningCols("countries are present in DT.AppleMusic_FiguresFS missing from DT.country: ", missing_countries.AppleMusic_FiguresFS))
        if (length(missing_countries.LinearRadio_FiguresFS))
          warning(warningCols("countries are present in DT.LinearRadio_FiguresFS missing from DT.country: ", missing_countries.LinearRadio_FiguresFS))
    ### ------------------------------------------ ###

    addColsFrom_(DT.AppleMusic_FiguresFS, DT.country, joinCols="country_name", colsToBring=c("country_code", "countryid"))
    addColsFrom_(DT.LinearRadio_FiguresFS, DT.country, joinCols="country_name", colsToBring=c("country_code", "countryid"))
}

setkeyIfNot(DT.AppleMusic_FiguresFS,  c("activity_month544", "apple_reporting_period", "country_code", "country_name", "membership", "membership_mode", "service_channel"), organize=TRUE, verbose=FALSE)
setkeyIfNot(DT.LinearRadio_FiguresFS, c("activity_month544", "apple_reporting_period", "country_code", "country_name", "membership", "membership_mode", "service_channel"), organize=TRUE, verbose=FALSE)


## EXPORT TO SNOWFLAKE
setSnowflake(wh=wh, dbname=dbname)
ingestIntoSQL(DT=DT.AppleMusic_FiguresFS, schema=schema_out, wh=wh, dbname=dbname, drop=TRUE)
ingestIntoSQL(DT=DT.LinearRadio_FiguresFS, schema=schema_out, wh=wh, dbname=dbname, drop=TRUE)

## --------------------------------------------------------------- ##
## COMPARE ORCHARD TO SONY FIGURES
## CREATING XLSX REPORT FOR PRAS
## --------------------------------------------------------------- ##
  DT.apple_orchard <- makeQry(schema="AppleMusic", tbl="applemusicraw", dateCol="datestamp", colsToPull=c("activity_date" = "datestamp", "membership", "membership_mode", "country_code"="storefront_name"), colsToAgg=c(orchard_streams="quantity"), aggFunc="sum", limit=NULL) %>% sfQry()

setkey(DT.apple_orchard, "activity_date")
DT.apple_orchard[, activity_month544 := convertDateToiTunes544(activity_date)]

## CONFIRM, VISUALLY that we are dealing with the same dates
DT.apple_orchard[, list(m=min(activity_date), M=max(activity_date)), keyby=activity_month544]
DT.AppleMusic_FiguresFS[, list(unique(apple_reporting_period)), keyby=list(activity_month544)]

DT.apple_orchard.aggd_monthly <- aggregateDT(DT.apple_orchard, by=c("activity_month544", "country_code", "membership_mode", "membership"), colsToAgg="orchard_streams", exclude="activity_date")

DT.AppleMusic_FiguresFS.aggd_monthly <- DT.AppleMusic_FiguresFS[, list(apple_streams=sumn(applemusic_rb_streams)), keyby=list(activity_month544, country_code, membership_mode, membership)]

is(DT.AppleMusic_FiguresFS.aggd_monthly$activity_month544)
is(DT.apple_orchard.aggd_monthly$activity_month544)

## Make sure keys are the same
stopifnot(identical(key(DT.AppleMusic_FiguresFS.aggd_monthly), key(DT.apple_orchard.aggd_monthly)))

## MERGE THE TWO
DT.apple_and_orchard_merged <- merge(DT.apple_orchard.aggd_monthly, DT.AppleMusic_FiguresFS.aggd_monthly)
setInfo(DT.apple_and_orchard_merged, "merge of orchard and applemusic streams. Orchard comes from our raw tables; applemusic comes from sme excel file")

## There are no apple_streams count for PAID prior to october.  Set these to NA
stopifnot(DT.apple_and_orchard_merged[membership_mode == "PAID" & activity_month544 <= "2015-09-01", all(apple_streams == 0)])
DT.apple_and_orchard_merged[membership_mode == "PAID" & activity_month544 <= "2015-09-01", apple_streams := NA]

DT.apple_and_orchard_merged[, orchard_marketshare := as.perc(orchard_streams / apple_streams)]
DT.apple_and_orchard_merged[!is.finite(orchard_marketshare), orchard_marketshare := NA]

## JUST PAID VS TRIAL, BY COUNTRY
DT.apple_and_orchard_merged.regional_paid_vs_trial <- aggregateDT(DT.apple_and_orchard_merged, by=c("activity_month544", "country_code", "membership_mode"), exclude=c("membership", "orchard_marketshare"))
DT.apple_and_orchard_merged.regional_paid_vs_trial[membership_mode == "PAID" & activity_month544 <= "2015-09-01", apple_streams := NA]
DT.apple_and_orchard_merged.regional_paid_vs_trial[, orchard_marketshare := as.perc(orchard_streams / apple_streams)]
DT.apple_and_orchard_merged.regional_paid_vs_trial[!is.finite(orchard_marketshare), orchard_marketshare := NA]

## JUST PAID VS TRIAL (aggregate out country)
DT.apple_and_orchard_merged.ww_paid_vs_trial <- aggregateDT(DT.apple_and_orchard_merged, by=c("activity_month544", "membership_mode"), exclude=c("country_code", "membership", "orchard_marketshare"))
DT.apple_and_orchard_merged.ww_paid_vs_trial[membership_mode == "PAID" & activity_month544 <= "2015-09-01", apple_streams := NA]
DT.apple_and_orchard_merged.ww_paid_vs_trial[, orchard_marketshare := as.perc(orchard_streams / apple_streams)]
DT.apple_and_orchard_merged.ww_paid_vs_trial[!is.finite(orchard_marketshare), orchard_marketshare := NA]

## BLENDED, REGIONAL
DT.apple_and_orchard_merged.regional_blended <- aggregateDT(DT.apple_and_orchard_merged, by=c("activity_month544", "country_code"), exclude=c("membership", "membership_mode", "orchard_marketshare"))
DT.apple_and_orchard_merged.regional_blended[activity_month544 <= "2015-09-01", apple_streams := NA]
DT.apple_and_orchard_merged.regional_blended[, orchard_marketshare := as.perc(orchard_streams / apple_streams)]
DT.apple_and_orchard_merged.regional_blended[!is.finite(orchard_marketshare), orchard_marketshare := NA]

## BLENDED, WW
DT.apple_and_orchard_merged.ww_blended <- aggregateDT(DT.apple_and_orchard_merged, by=c("activity_month544"), exclude=c("country_code", "membership", "membership_mode", "orchard_marketshare"))
DT.apple_and_orchard_merged.ww_blended[activity_month544 <= "2015-09-01", apple_streams := NA]
DT.apple_and_orchard_merged.ww_blended[, orchard_marketshare := as.perc(orchard_streams / apple_streams)]
DT.apple_and_orchard_merged.ww_blended[!is.finite(orchard_marketshare), orchard_marketshare := NA]

## List of DT's to export to xls. Names in the list will be the sheet names
ll_DTs_to_output <- list(
    WW_Blended            = DT.apple_and_orchard_merged.ww_blended
  , WW_Paid_vs_Free       = DT.apple_and_orchard_merged.ww_paid_vs_trial
  , Regional_Blended      = DT.apple_and_orchard_merged.regional_blended
  , Regional_Paid_vs_Free = DT.apple_and_orchard_merged.regional_paid_vs_trial
  , Regional_by_Membership_Type = DT.apple_and_orchard_merged
)

## Export
f.out <- out.p("AppleMusic_Marketshare_using_SME_Figures.xlsx")
exportXLS.usingXLConnect(f.out=f.out, DTs.list=ll_DTs_to_output)