loadFromJesus("DT.revshare", srcDir=srcOther("Spotify_Accounting_ETL"), over=TRUE, verbose=FALSE)

## Ensure month is a Date type
DT.revshare[, month := as.Date(month)]

## Crop below minDate.global  -- specifically, the accounting was different prior to 2013
if (exists("minDate.global"))
  assignWithInfo(DT.revshare, DT.revshare[month >= minDate.global], info=sprintf("Cropped to month >= %s", minDate.global))

## there is an odd row in June 2013 for product TL with no currecncy.  Drop it
DT.revshare <- DT.revshare[!(product_description == "TL" & is.na(currency))]


## There are some NA's in payable_usd.  All of these are for March 2013 and earlier
# CONFIRM (1) No recent NAs (2) all NAs are for either USD or EUR
if (!all(DT.revshare[is.na(payable_usd), month <= "2013-03-01"])) warning ("Some NAs in payable_usd is for after 2013-03-01")
if (!all(DT.revshare[is.na(payable_usd), currency %in% c("USD", "EUR")])) warning ("Some NAs in payable_usd are for after unexpected Currencies")

## Fill missing payable_usd. 
## ------------------------------- ##
  ## USD :: simply copy over
  ## First confirm all payable == payable_usd for currency USD, and that there are no NAs in payable
  stopifnot(DT.revshare[!is.na(payable_usd) & currency == "USD", identical(payable_usd, payable)])
  stopifnot(DT.revshare[!is.na(payable_usd) & currency == "USD", !is.na(payable)])
  DT.revshare[is.na(payable_usd) & currency == "USD", payable_usd := payable]

  ## EUR :: simply copy over
  ## First confirm all payable == payable_eur for currency EUR, and that there are no NAs in payable
  stopifnot(DT.revshare[!is.na(payable_eur) & currency == "EUR", identical(payable_eur, payable)])
  stopifnot(DT.revshare[!is.na(payable_eur) & currency == "EUR", !is.na(payable)])
  DT.revshare[is.na(payable_eur) & currency == "EUR", payable_eur := payable]
  ## Find the conversion rate per month
  tmp_DT.EUR_conv_rates <- DT.revshare[!is.na(payable_usd) & !is.na(payable) & currency == "EUR", list(EUR_to_USD = median(payable_usd / payable)), by=month]
  matchKey(DT.revshare, tmp_DT.EUR_conv_rates, "month", superset.ok=TRUE, verbose=FALSE)
  DT.revshare[tmp_DT.EUR_conv_rates, EUR_to_USD := EUR_to_USD, allow=TRUE]

  ## CONFIRM: All the NAs for payable_usd and "EUR" have payable_eur identical to payable
  stopifnot(DT.revshare[is.na(payable_usd) & currency == "EUR", identical(payable_eur, payable)])
  DT.revshare[is.na(payable_usd) & currency == "EUR", payable_usd := EUR_to_USD * payable]
## ------------------------------- ##


## Gross and Net revenue are in local currency
## We want them in USD.   Thus, we need to calculate the conversion rate
DT.revshare[, currency_rate_per_USD.calculated := payable_usd / payable]
DT.revshare[, net_revenue_usd   := net_revenue   * currency_rate_per_USD.calculated]
DT.revshare[, gross_revenue_usd := gross_revenue * currency_rate_per_USD.calculated]
DT.revshare[, currency_rate_per_USD.calculated := NULL]  ## Clear this unneeeded column

setkeyIfNot(DT.revshare, month, country_code, verbose=FALSE)

setInfo(DT.revshare, info=appendInfo(new.info=paste0("Modified DT.revshare with payable_usd filled in and cropped to minDate=", minDate.global, "\nnet_revenue_usd :: The Spotify Net Revenue in USD\ngross_revenue_usd :: The Spotify Gross Revenue in USD"), DT=DT.revshare))