if (FALSE)
  source("~/git/orch/src/AppleMusic/Trial Estimates.r")

setScience(proj="AppleMusic", subProj="Trial")
setGitBranchToSystem()
.g()

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


DT.country <- get_dim_country(refresh=TRUE)
stopifnot(DT.country[country_code == "LY", country_name == "Libya"])

CARIBB_Countries <- c("Antigua and Barbuda","Anguilla","Barbados","Bermuda","Belize","Dominica","Grenada","Saint Kitts and Nevis","Cayman Islands","Trinidad and Tobago","British Virgin Islands") %>% pasteC(C=",")
CIS_Countries <- "Armenia, Azerbaijan, Belarus, Kazakhstan, Kyrgyzstan, Moldova, Tajikistan, Uzbekistan, Ukraine, Turkmenistan, Georgia"
AFRICA_Countries <- c("Nigeria", "Ethiopia", "Egypt", "Tanzania", "Kenya", "Algeria", "Sudan", "Uganda", "Morocco", "Ghana", "Mozambique", "Angola", "Ivory Coast", "Madagascar", "Cameroon", "Niger", "Burkina Faso", "Mali", "Malawi", "Zambia", "Senegal", "Chad", "Zimbabwe", "Rwanda", "Tunisia", "Somalia", "Guinea", "Benin", "Burundi", "Togo", "Eritrea", "Sierra Leone", "Libya", "Central African Republic", "Congo", "Liberia", "Mauritania", "Namibia", "Botswana", "Gambia", "Equatorial Guinea", "Lesotho", "Gabon", "Guinea-Bissau", "Mauritius", "Swaziland", "Djibouti", "Réunion", "Comoros", "Cape Verde", "Western Sahara", "Mayotte", "São Tomé and Príncipe", "Seychelles", "Saint Helena")
AFRICA_Countries %<>% pasteC(C=",")

DT.oanda <- ingest.p("OANDA_rates_20150909.csv") %>% fread(header=TRUE) %>% setnamestolower
DT.rates2 <- ingest.p("AppleMusic Trial Rates uncleaned.csv") %>% fread(header=TRUE)

DT.rates2[, cleaned := removeText("\\s*\\(.+\\)\\s*", territory)]
DT.rates2[, cleaned := gsub(",\\s*and", ",", cleaned)]
DT.rates2[, row := seq(.N)]
DT.rates2[, rate_per_play := as.numeric(rate_per_play)]

## SOME MANUAL ADJUSTMENTS
## 'and africa countries' gets dropped by the above gsub
DT.rates2[cleaned == "Middle East Countries", cleaned := "Middle East Countries, Africa Countries"]
## Misspelling
DT.rates2[cleaned == "Macau and Brunei", cleaned := "Macau, Brunei"]

## Replace the generic with specific countries
DT.rates2[, cleaned := gsub("Caribbean", CARIBB_Countries, cleaned, ignore.case=TRUE)]
DT.rates2[, cleaned := gsub("CIS Countries", CIS_Countries, cleaned, ignore.case=TRUE)]
DT.rates2[, cleaned := gsub("Africa Countries", AFRICA_Countries, cleaned, ignore.case=TRUE)]


DT.rates <- DT.rates2[, c(country_name = strsplit(cleaned, ",\\s*")), by=c("rate_per_play", "currency", "row")]
DT.rates[, row := NULL]
DT.rates[, country_name := trim(country_name)]
DT.rates[country_name == "UAE", country_name := "United Arab Emirates"]



## ENCODING
DT.country[, country_name := enc2utf8(country_name)]
DT.rates[, country_name := enc2utf8(country_name)]

    ## There shouldnt be any misses via one that are caught via the other method
    missing_countries <- setdiff(DT.rates$country_name, DT.country$country_name)
    matched_countries <- sapply(DT.rates$country_name, pmatch, DT.country$country_name)
    unmatched_countries <- matched_countries %>% is.na %>% nwhich()
    mm <- matched_countries[setdiff(missing_countries, unmatched_countries)]
    DT.country[mm]
    DT.rates[country_name %in% unmatched_countries]


addColsFrom_(DT.rates, DT.oanda, colsToBring=c("to_usd", "from_usd"), joinCols="currency")
stopifnot(!DT.rates[is.na(to_usd), .N])

addColsFrom_(DT.rates, DT.country, colsToBring=c("country_code", "countryid"), joinCols="country_name")
## FIX NA Country code
DT.rates[country_name == "Namibia", country_code := "NA"]
DT.rates[is.na(country_code)]

DT.rates[, rate_per_play_usd := rate_per_play * to_usd]



schema <- "applemusic"
tbl.apple  <- "APPLEMUSICRAW"
tbl.rates  <- "APPLE_MUSIC_RATES"

dateCol <- c(date="datestamp::date")
colsToAgg <- c(dateCol)

# makeQry(tbl=tbl.apple, schema=schema, colsToPull="*", aggFunc=NULL, limit=7) %>% sfQry

DT.apple_music_rates <- DT.rates
ingestIntoSQL(DT.apple_music_rates, schema=schema, drop=TRUE)

sourceSupportFns(proj="Looker")
create_lookml_from_tbl(tbl=tbl.rates, schema=schema)


## HOW MUCH iS APPLE RED
if (FALSE) {
  minDate <- "2015-06-28"
  maxDate <- "2015-09-10"
  schema <- "BI"
  tbl <- "ANALYTICS_NEXT"
  transac_types <- c("AS", "S")
  dateCol <- "activity_date"
  colsToPull <- c("store_name", "activity_month544", "activity_date", "transac_type_abbr", "label_sc_group", "currency_code", "country_name", "transac_type_group")
  colsToAgg <- c(revenue="royaltydollar", "paidunits", "freeunits", "units")

  DT.apple_red <- 
    makeQry(tbl=tbl, schema=schema, colsToPull=colsToPull, colsToAgg=colsToAgg, dateCol=dateCol, minDate=minDate, maxDate=maxDate, storeid=1, transac_type_abbr=transac_types) %>% 
    sfQry

  kCols.ww <- c("activity_month544", "transac_type_abbr", "label_sc_group")
  DT.apple_red.ww <- DT.apple_red[, lapply(.SD, sumn), keyby=kCols.ww, .SDcols="units"]

  DT.apple_red.ww <- DT.apple_red.ww[, list(label_sc_group, streams=units, percent_of_streams_ww = fwp(percOfTotal(units))), keyby=setdiff(kCols.ww, "label_sc_group")]
  DT.apple_red.ww[, sumn(streams), keyby=activity_month544]

JULY: 122760288
AUG:  122745277

JULY: 116939141
AUG:  135710038

DT.apple_red[, list(streams=sum(units)), keyby=list(month(activity_date))]




}