if (getProjName() != "Spotify_Accounting_ETL") {
  fresh()
  setScience("Spotify_Accounting_ETL", create=TRUE, subl=FALSE, load=TRUE)
}

if (!exists("DT.SpotifyRates"))
  loadFromJesus("DT.SpotifyRates")

rm(DT.MISSING_DATA)
DT.revsharecrop <- copy(DT.revshare[month >= "2013-04-01"])  ## There were some changes after 2013-03-01

kCols.DC  <- c("month", "country_code")
kCols.DCP <- c("month", "country_code", "product")
setkeyIfNot(DT.revsharecrop, kCols.DCP, verbose=FALSE, organize=TRUE)

## Add in product_type
DT.revsharecrop[product == "A", product_type := "Ad-Supported"]
DT.revsharecrop[product == "U", product_type := "Desktop Basic"]
DT.revsharecrop[product == "P", product_type := "Premium"]
DT.revsharecrop[is.na(product_type), product_type := "Premium (Bundled or Discounted)"]

## Add in the W & X & Z columns from DT.SpotifyRates
DT.revsharecrop[, year := year(month)]
c.ch1 <- setkey(DT.SpotifyRates[, list(currency.contract = unique(currency)), keyby=list(year, country_code)])
c.ch2 <- setkey(DT.revsharecrop[month >="2013-09-01", list(currency.actual   = unique(currency)),  keyby=list(year, country_code)])
DT.incorrect_currency <- merge(c.ch1, c.ch2, by=c("year", "country_code"))[currency.contract != currency.actual]
addCountry.byCode_(DT.incorrect_currency)
writeDT(DT.incorrect_currency, reveal=FALSE)

colsToBring_1 <- c("retail.unlimited", "retail.premium")
if (any(colsToBring_1 %ni% DT.revsharecrop))
  addColsFrom_(DT.revsharecrop, DT.SpotifyRates, joinCols=c("year", "country_code", "currency"), colsToBring=colsToBring_1 )

colsToBring_1 <- c("orch_pool.unlimited_W", "orch_pool.premium_X", "per_play_cpm_Z")
if (any(colsToBring_1 %ni% DT.revsharecrop))
  addColsFrom_(DT.revsharecrop, DT.SpotifyRates, joinCols=c("year", "country_code"), colsToBring=colsToBring_1 )



#~=~=#   ------------------------------------------
#~=~=#   "Streams" here means Streams played longer than 30 seconds
#~=~=#   "UserCount" means number of users in that tier group
#~=~=#   "Premium" will require adjustment if Spotify Family is released
#~=~=#   
#~=~=#   OurPayment <- max(A, B), by = Month, by territory
#~=~=#   
#~=~=#   A <- sum(i, ii, iii)
#~=~=#   B <- Z / 1000 * [All Paid Streams]
#~=~=#   
#~=~=#   i  <- max(ia, NA)
#~=~=#     ia <- proRata("Free") * A% * gross rev of Spotify FREE
#~=~=#     ib <- NA
#~=~=#   ii <- max(iia, iib)
#~=~=#     iia <- proRata("Unlimited") * A% * gross rev of Spotify Unlimited
#~=~=#     iib <- proRata("Unlimited") * W * No.Of Unlimited subscribers
#~=~=#   iii <- max(iiia, iiib)
#~=~=#     iiia <- proRata("Premium") * A% * gross rev of Spotify Premium
#~=~=#     iiib <- proRata("Premium") * X * No.Of Premium subscribers
#~=~=#   ------------------------------------------



  Month <- as.Date("2014-08-01")
  Month <- seq(as.Date("2013-08-01"), length=3, by="1 month")
  Month <- as.Date("2013-12-01")
  territory <- "CL"
  territory <- "US"
  territory <- "TH"
  prod   <- "P"


DT.revsharecrop[, OrchardRev.usingNet   := validPercentage(revenue_share/100, 0, 1, silent=TRUE) * (rightholders_tracks / total_tracks) * net_revenue]
DT.revsharecrop[, OrchardRev.usingGross := validPercentage(revenue_share/100, 0, 1, silent=TRUE) * (rightholders_tracks / total_tracks) * gross_revenue]

DT.revsharecrop[product == "U", Orchard.W := (rightholders_tracks / total_tracks) * orch_pool.unlimited_W * registered_users]
DT.revsharecrop[product == "U", using := ifelse(OrchardRev.usingNet > Orchard.W, "OrchardRev.usingNet", "Orchard.W")]
DT.revsharecrop[using == "Orchard.W"]
DT.revsharecrop[using == "Orchard.W" & !equals(payable, Orchard.W, 1)]
## IMPORTANT &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
message("THIS IS IMPORTANT\nShows which calculation used for each month in 2014, by country")
print( DT.revsharecrop[year == 2014 & product == "U", table(using, country_code)] )
## IMPORTANT &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

## All others, as premium
DT.revsharecrop[product %ni% c("U", "A"), Orchard.X := (rightholders_tracks / total_tracks) * orch_pool.premium_X * registered_users]
DT.revsharecrop[product %ni% c("U", "A"), using := ifelse(OrchardRev.usingNet > Orchard.X, "OrchardRev.usingNet", "Orchard.X")]
DT.revsharecrop[using == "Orchard.X"]
DT.revsharecrop[using == "Orchard.X" & !equals(payable, Orchard.X, 1)]

print( DT.revsharecrop[year == 2014 & product %ni% c("U", "A"), table(using, country_code)] )

## Alternate to Sum, using Z
## ifelse does NOT work with integer64
DT.revsharecrop[product != "A", Orchard.Z := (per_play_cpm_Z * as.integer64(rightholders_tracks))]
DT.revsharecrop[product == "A", Orchard.Z := 0]

#### ------------ CALCULATE A & B in max(A, B) -------------- ####
## Calculate "A" in max(A, B)
{
  noVal <- (-.0001) # Used in place of NA
  suppressWarnings(DT.revsharecrop[, semiPayable_A := NULL])
  DT.revsharecrop[product %ni% c("U", "A"), semiPayable_A := as.numeric(maxn(Orchard.X, OrchardRev.usingNet, noVal)), by=kCols.DCP]
  DT.revsharecrop[product %in% c("U"), semiPayable_A := as.numeric(maxn(Orchard.W, OrchardRev.usingNet, noVal)), by=kCols.DCP]
  DT.revsharecrop[product %in% c("A"), semiPayable_A := as.numeric(OrchardRev.usingNet), by=kCols.DCP]
}
{
  suppressWarnings(DT.revsharecrop[, semiPayable_B := NULL])
  DT.revsharecrop[product != "A", semiPayable_B := per_play_cpm_Z  * as.numeric(rightholders_tracks/1000) ]
  DT.revsharecrop[product == "A", semiPayable_B := 0]
}

## WHAT WE SHOULD GET PAID
{
  suppressWarnings(DT.revsharecrop[, Payable_Using := NULL])
  DT.revsharecrop[, Payable_Using := ifelse(c(A=formnumb(sum(semiPayable_A), round=0)) >= c(B=formnumb(sum(semiPayable_B))), "A", "B"), by=kCols.DC]
  DT.revsharecrop[kvals]
  table(DT.revsharecrop[, list(country_code, Payable_Using)])
  table(unique(DT.revsharecrop[year == 2014], by=c("month", "country_code"))[, list(country_code, Payable_Using)])
  as.data.table(table(unique(DT.revsharecrop[year == 2014], by=c("month", "country_code"))[, list(Payable_Using, country_code)])) [count != 0]
}
#### ------------ CALCULATE A & B in max(A, B) -------------- ####
DT.revsharecrop[, 
as.numeric(sumn(ifelse(using == "Orchard.X", as.numeric(Orchard.X), ifelse(using=="Orchard.W", as.numeric(Orchard.W), as.numeric(OrchardRev.usingNet)))))
]

DT.revsharecrop[, use_A_or_B := {
  .SD[, list(B = sumn(Orchard.Z), A = as.numeric(sumn(ifelse(using == "Orchard.X", as.numeric(Orchard.X), ifelse(using=="Orchard.W", as.numeric(Orchard.W), as.numeric(OrchardRev.usingNet))))))][, ifelse(A >= B, "A", "B")]
}, by=kCols.DCP]


DT.revsharecrop[, PayIsGross := abs(OrchardRev.usingGross - payable) / payable < .005]
DT.revsharecrop[, PayIsNet   := abs(OrchardRev.usingNet   - payable) / payable < .005]
DT.revsharecrop[!PayIsNet & OrchardRev.usingNet < .1 & payable < .1, PayIsNet := TRUE]

key(DT.incorrect_currency)
setkeyIfNot(DT.revsharecrop[!(PayIsNet)], key(DT.incorrect_currency))[DT.incorrect_currency, nomatch=0L]

## Always using Net for Ad Supported
DT.revsharecrop[product == "A" & !(PayIsNet)]
DT.SpotifyRates

print.revshare(DT.revsharecrop[(PayIsGross) & !(PayIsNet) & payable > 1], nrow=20)
part_i <- function(Month, territory, prod) {
  kvals <- CJ(Month, territory, prod)
  kvals <- CJ(Month, territory)
  DT.revsharecrop[kvals]
  DT.revsharecrop[country_code == "TH"]

  Aperc <- DT.revsharecrop[kvals]$revenue_share / 100

  totalStreams   <- as.integer64(DT.revsharecrop[kvals]$total_tracks)
  orchardStreams <- as.integer64(DT.revsharecrop[kvals]$rightholders_tracks)
  grossRev       <- DT.revsharecrop[kvals]$gross_revenue
  netRev         <- DT.revsharecrop[kvals]$net_revenue

  Aperc * (orchardStreams / totalStreams)
  grossRev * Aperc * (orchardStreams / totalStreams)
  OrchardRevenue <- netRev * Aperc * (orchardStreams / totalStreams)

  ## Confrim
  equals(DT.revsharecrop[kvals]$payable, OrchardRevenue, tol=1)
}
----------------------------------

ON A COUNTRY BY COUNTRY BASIS, THE GREATER OF (A) AND (B): 
(A) The sum of three parts (three tiers)
    i. Our pro-rata share of A% of gross revenue attributed to Spotify FREE
    ii. The greater of
           (ii_a) Our pro-rata share of A% of gross revenue attributable to Spotify Unlimited
           (ii_b) Our pro-rata share of W per Spotify Unlimited subscriber
    iii. The greater of:
           (iIi_a) Our pro-rata share of A% of gross revenue attributable to Spotify Premium + Spotify Family
           (iiI_b) Our pro-rata share of (X per Spotify Premium subscriber) + (X/2 per Spotify Premium Family add-on subscriber)
(B) Amount (Z / 1,000) for all plays of Our recordings, regardless of tier, (with minimum time span of 30 seconds)

----

A% is 60% upto and including 2013-12-31 and 55% therafter

"label pools" (?) and "per play fees" for each territory are in Schedule 2. 

DT.revsharecrop