library(ggplot2)
library(gridExtra)
library(forecast)


Kcols_D   <- c("Month") 
Kcols_DPt <- c("Month", "Product_Tier") 


ma3 <- function(x, order=3, na.rm=TRUE) {
    n <- setNames(nm=seq(x))
    inds <- lapply(n, function(i) seq(from=i, to=i-order+1))[-seq(order-1)]
    ret <- sapply(inds, function(i) mean(x[i], na.rm=na.rm))
    if (order %% 2==0 && order != 2)
       ret <- ma3(ret, order=2)
    NAs <- rep(NA, max(1, (order-1)/2))
    return(c(NAs, ret, NAs))
}

x <- setNames(runif(1:10), 1:10)


## ------------------------------------------------------------------------ ##


## ------------------------------------------------------------------------ ##
##     Given DT.Acc.perDPt (from Accounting)
##         convert all ThirdParty tiers and summarize
## ------------------------------------------------------------------------ ##
    We have several 
    For cleanliness, move the DT.Acc.perDCnP info to a new table, 


    DT.Acc.perDCnP[Product_Tier %ni% c("FreeTier", "LimitedTier", "PremiumTier"), Product_Tier := "ThirdPartyPartners"]
    DT.Acc.perDPt <- aggregateDT( DT.Acc.perDCnP
                                , by=Kcols_DPt, exclude=c("Product")
                                , include=list(Streams.Spotify="Total_tracks", Streams.Orchard="Rightholders_tracks", 
                                                Revenue.Spotify="Net_revenue", Revenue.Orchard="Payable_Using",  
                                                Users.Spotify="Registered_users")
                                )
    ## Error-check.  There should be exactly one row per Month/Product_Tier
    stopifnot(!DT.Acc.perDPt[, .N, by=Kcols_DPt][, sum(N>1)])
## ------------------------------------------------------------------------ ##


## ------------------------------------------------------------------------ ##
##     Given Unique Users by Product (from Analytics)
##         convert Product to Product_Tier and re-summaraize
## ------------------------------------------------------------------------ ##

    ## Clean up names, we are using first-letter caps for Month / Product
    setnames(DT.UniqueUsers.perDP, c("month", "product"), c("Month", "Product"))

    ## Set Product_Tier
    DT.UniqueUsers.perDP[, Product_Tier := makeFactorUsingDict(vec=Product, dict=getDict.SpotifyTier(), missing_to_NA=FALSE)]

    DT.UniqueUsers.perDPt <- aggregateDT(DT.UniqueUsers.perDP, by=Kcols_DPt
                                        , include=list(Users.Orchard="UniqueUsers.perDP", Streams_analytics.Orchard="TotalStreams.perDP"))

    ## Error-check.  There should be exactly one row per Month/Product_Tier
    stopifnot(!DT.UniqueUsers.perDPt[, .N, by=Kcols_DPt][, sum(N>1)])
## ------------------------------------------------------------------------ ##


## ------------------------------------------------------------------------ ##
##     Add the Orchard info from DT.UniqueUsers.perDPt to DT.Acc.perDPt
##       Then recast the table into a stacked table with a Company column
## ------------------------------------------------------------------------ ##
    setkeyIfNot(DT.UniqueUsers.perDPt, Kcols_DPt)
    setkeyIfNot(DT.Acc.perDPt, Kcols_DPt)
    DT.Acc.perDPt[, Streams_analytics.Spotify := 0]
    DT.Acc.perDPt[DT.UniqueUsers.perDPt, c("Users.Orchard", "Streams_analytics.Orchard") := list(Users.Orchard, Streams_analytics.Orchard)]

    ## Goal is to stack by Owner (eg Orchard / Spotify)
    melted <- melt(DT.Acc.perDPt, id.vars=Kcols_DPt)[, c("variable", "Company") :=  as.data.table(do.call(rbind, strsplit(as.character(variable), "\\.")))  ]
    DT.using.DPt <- as.data.table(dcast(melted, Month+Product_Tier+Company ~ variable))

    ## Confirming diff in Streams / Streams_analytics
    DT.using.DPt[, mean(Streams_analytics / Streams, na.rm=TRUE)]
    DT.using.DPt[!is.na(Streams_analytics), list(Stream_Count_Analytics_vs_Accounting = fwp(mean((Streams_analytics-Streams) / Streams))), by="Product_Tier,Company"]


    DT.using.D <- aggregateDT(DT.using.DPt, by=c("Month,Company"), exclude="Product_Tier")
    DT.using.D[!is.na(Streams_analytics) & Company != "Spotify" & Users > 0, list(Stream_Count_Analytics_vs_Accounting = fwp(mean((Streams_analytics-Streams) / Streams))), by=key(DT.using.D)]
## ------------------------------------------------------------------------ ##


scaledDiff <- function(x, scaleby.index=1) {
    diff(x) / x[[scaleby.index]]
}
ratio <- function(x, first.is.first=TRUE) {
    if (first.is.first)
        x[[1]] / x[[2]]
    else
        x[[2]] / x[[1]]
}


## ------------------------------------------------------------------------ ##
##  DIFF BETWEEN STREAM COUNTS
## ------------------------------------------------------------------------ ##
##    (Ours-Theirs) / Theirs
##
  M1 <- melt(DT.using.D[Company=="Orchard" & Users>0, list(Month, "Accounting Summary"=Streams, "Raw Analytics"=Streams_analytics)], measure.vars=c("Accounting Summary", "Raw Analytics"), variable.name="Source")

  ## Ensure that it is ordered by Source
  setkey(M1, Month, Source)

  ## Ratio  --  same as:  DT.using.D[Users>0 & Company=="Orchard", (Streams_analytics-Streams)/Streams, by=Month]
  M1.r <- M1[,  list("Difference in\nReported Streams"="Scaled Ratio of\n  Raw / Accounting", ratio=mean(M1$value) * (0.5 + scaledDiff(value))), by=Month]

Streams_analytics/streams  - 1
  P.StreamsDiff <- {
    ggplot(data=M1, aes(x=Month)) + 
      geom_bar(aes(y=value, fill=Source), alpha=.52, stat="identity", position=position_dodge()) + 
      geom_line(data=M1.r, aes(y=ratio, color=`Difference in\nReported Streams`), size=2, alpha=1) + 
      geom_hline(y=mean(M1$value*.5), color="gray70", size=1, lty="dashed", alpha=0.5) + 
      scale_color_manual(values=c("Accounting Summary" ="Dark Red", "Raw Analytics"="Dark Blue", "Scaled Ratio of\n  Raw / Accounting"="black")) + 
      scale_fill_manual(values=c("Accounting Summary" ="Dark Red", "Raw Analytics"="Dark Blue", "Scaled Ratio of\n  Raw / Accounting"="black")) + 
      ## Legend ordering
      guides(fill = guide_legend(order = 1), 
            color = guide_legend(order = 2)) +
      millions.y() + 
       labs(  x = ""
            , y = "Number of Streams (in Millions)\nwith\nDifference in reporting, scaled to [-1, ∞) \n(Gray dotted line is zero-difference)"
            , title="Comparing reported Stream counts between\nRaw Analytics vs Accounting Summaries\n\nBlack line shows ratio of the two.")
   }

## ------------------------------------------------------------------------ ##

      ## Create Ratios
      Ratios <- M1[, list(Streams.Analytics_vs_Accounting=scaledDiff(value)), keyby=Month]

    ## Set key to ensure ordering
    setkeyIfNot(DT.using.D, Month, Company)
    M2 <- DT.using.D[, list(Users.Orch_vs_Spot = ratio(Users)), keyby=Month]
    M2 <- M2[Users.Orch_vs_Spot!=0]

    Ratios[M2, Users.Orch_vs_Spot := Users.Orch_vs_Spot]

    ## Additional columns for the graph
    M2[, one := 1]
    M2[, Users.Orch_vs_Spot.MA := ma3(Users.Orch_vs_Spot)]
    M2[, Users.Orch_vs_Spot.DELTA := diffNA(Users.Orch_vs_Spot)]
    M2[, Users.Orch_vs_Spot.ACCEL := diffNA(Users.Orch_vs_Spot.DELTA)]

    P.UserPercent <- {
      ggplot(data=M2, aes(x=Month)) + 
        geom_bar(aes(y=one), fill="green", alpha=.2, stat="identity") + 
        geom_bar(aes(y=Users.Orch_vs_Spot), alpha=.81, fill="orange", stat="identity") + 

        labels.percent("Users.Orch_vs_Spot", size=3) +
        # geom_text(aes(label=fwp(Users.Orch_vs_Spot, 1), y=Users.Orch_vs_Spot+1/2*mean(Users.Orch_vs_Spot)), size=2) + 

        geom_line(aes(y=Users.Orch_vs_Spot.MA), color="red", alpha=.8, size=.8) + 

        # geom_line(aes(y=Users.Orch_vs_Spot.DELTA+0.35), color="Blue", alpha=.8, size=1) + 
        # geom_line(aes(y=Users.Orch_vs_Spot.ACCEL+0.45), color="black", alpha=.8, size=1) + 
        # geom_abline(y=0.45, color="black", alpha=.7, color="darkgrey", lty="dashed", aes(xmin=3, xmax=Month[12])) + 
        labs(x="", y="Percent of Total Spotify Users"
            , title="\nThe Orchard's Number of Users\nAs a Percentage of Total Spotify Users\n") +
        percent.y()
    }


## ------------------------------------------------------------------------ ##

## Orchard Rev Share


    ## Set key to ensure ordering
    setkeyIfNot(DT.using.D, Month, Company)
    M3 <- DT.using.D[, list(Revenue.Orch_vs_Spot = ratio(Revenue)), keyby=Month]

    M3[, one := 1]
    M3[, Revenue.Orch_vs_Spot.MA := ma3(Revenue.Orch_vs_Spot)]
    M3[, Revenue.Orch_vs_Spot.DELTA := diffNA(Revenue.Orch_vs_Spot)]
    M3[, Revenue.Orch_vs_Spot.ACCEL := diffNA(Revenue.Orch_vs_Spot.DELTA)]

P.RevenuePercent <- 
{
  ggplot(data=M3, aes(x=Month)) + 
    geom_bar(aes(y=one), fill="green", alpha=.2, stat="identity") + 
    geom_bar(aes(y=Revenue.Orch_vs_Spot), alpha=.81, fill="orange", stat="identity") + 

    labels.percent("Revenue.Orch_vs_Spot", size=3) +
    # geom_text(aes(label=fwp(Revenue.Orch_vs_Spot, 1), y=Revenue.Orch_vs_Spot+1/2*mean(Revenue.Orch_vs_Spot)), size=2) + 

    geom_line(aes(y=Revenue.Orch_vs_Spot.MA), color="red", alpha=.8, size=.8) + 

    # geom_line(aes(y=Revenue.Orch_vs_Spot.DELTA+0.35), color="Blue", alpha=.8, size=1) + 
    # geom_line(aes(y=Revenue.Orch_vs_Spot.ACCEL+0.45), color="black", alpha=.8, size=1) + 
    # geom_abline(y=0.45, color="black", alpha=.7, color="darkgrey", lty="dashed", aes(xmin=3, xmax=Month[12])) + 
    labs(x="", y="Percent of Spotify's Total Monthly Revenue"
        , title="\nThe Orchard's Revenue from Spotify\nas a Percentage of Spotify's Reported Net Revenue\n") +
    percent.y()
}


DT.using.D[, Rev_MA := ma3(Revenue), by=Company]
P.RevenueAbsolute <- 
{
  ggplot(data=DT.using.D, aes(x=Month)) + 
    geom_bar(aes(y=Revenue, fill=Company), alpha=1, stat="identity", position=position_dodge()) + 
    # geom_bar(aes(y=Revenue.Orch_vs_Spot), alpha=.81, fill="orange", stat="identity") + 

    # labels.percent("Revenue.Orch_vs_Spot", size=3) +
    # geom_text(aes(label=fwp(Revenue.Orch_vs_Spot, 1), y=Revenue.Orch_vs_Spot+1/2*mean(Revenue.Orch_vs_Spot)), size=2) + 

    geom_line(aes(y=Rev_MA, color=Company), alpha=.35, size=.8) + 

    # geom_line(aes(y=Revenue.Orch_vs_Spot.DELTA+0.35), color="Blue", alpha=.8, size=1) + 
    # geom_line(aes(y=Revenue.Orch_vs_Spot.ACCEL+0.45), color="black", alpha=.8, size=1) + 
    # geom_abline(y=0.45, color="black", alpha=.7, color="darkgrey", lty="dashed", aes(xmin=3, xmax=Month[12])) + 
    labs(x="", y="Percent of Spotify's Total Monthly Revenue"
        , title="\nThe Orchard's Revenue from Spotify\nas a Percentage of Spotify's Reported Net Revenue\n") +
    millions.y()
}

    ggplot(data=M1, aes(x=Month)) + 
      geom_bar(aes(y=value, fill=Source), alpha=.52, stat="identity", position=position_dodge()) + 
      geom_line(data=M1.r, aes(y=ratio, color=`Difference in\nReported Streams`), size=2, alpha=1) + 
      geom_hline(y=mean(M1$value*.5), color="gray70", size=1, lty="dashed", alpha=0.5) + 
      scale_color_manual(values=c("Accounting Summary" ="Dark Red", "Raw Analytics"="Dark Blue", "Scaled Ratio of\n  Raw / Accounting"="black")) + 
      scale_fill_manual(values=c("Accounting Summary" ="Dark Red", "Raw Analytics"="Dark Blue", "Scaled Ratio of\n  Raw / Accounting"="black")) + 
      ## Legend ordering
      guides(fill = guide_legend(order = 1), 
            color = guide_legend(order = 2)) +
      millions.y() + 
       labs(  x = ""
            , y = "Number of Streams (in Millions)\nwith\nDifference in reporting, scaled to [-1, ∞) \n(Gray dotted line is zero-difference)"
            , title="Comparing reported Stream counts between\nRaw Analytics vs Accounting Summaries\n\nBlack line shows ratio of the two.")
   }




## ------------------------------------------------------------------------ ##


    ## Orchard Stream Share


    ## Set key to ensure ordering
    setkeyIfNot(DT.using.D, Month, Company)
    M4 <- DT.using.D[, list(Stream.Orch_vs_Spot = ratio(Streams)), keyby=Month]

    M4[, one := 1]
    M4[, Stream.Orch_vs_Spot.MA := ma3(Stream.Orch_vs_Spot)]
    M4[, Stream.Orch_vs_Spot.DELTA := diffNA(Stream.Orch_vs_Spot)]
    M4[, Stream.Orch_vs_Spot.ACCEL := diffNA(Stream.Orch_vs_Spot.DELTA)]

P.StreamPercent <- 
{
  ggplot(data=M4, aes(x=Month)) + 
    geom_bar(aes(y=one), fill="green", alpha=.2, stat="identity") + 
    geom_bar(aes(y=Stream.Orch_vs_Spot), alpha=.81, fill="orange", stat="identity") + 

    labels.percent("Stream.Orch_vs_Spot", size=3) +
    # geom_text(aes(label=fwp(Stream.Orch_vs_Spot, 1), y=Stream.Orch_vs_Spot+1/2*mean(Stream.Orch_vs_Spot)), size=2) + 

    geom_line(aes(y=Stream.Orch_vs_Spot.MA), color="red", alpha=.8, size=.8) + 

    # geom_line(aes(y=Stream.Orch_vs_Spot.DELTA+0.35), color="Blue", alpha=.8, size=1) + 
    # geom_line(aes(y=Stream.Orch_vs_Spot.ACCEL+0.45), color="black", alpha=.8, size=1) + 
    # geom_abline(y=0.45, color="black", alpha=.7, color="darkgrey", lty="dashed", aes(xmin=3, xmax=Month[12])) + 
    labs(x="", y="Percent of Spotify's Total Monthly Stream"
        , title="Number of Streams of The Orchard's Music\nas a Percentage of All Spotify's Monthly Streams\n(both figures as reported by\nSpotify in accounting summary)") +
    percent.y()
}
































    aggregateAndCreateARPUmeasures( DT.Acc.perDPt, by=Kcols_DPt, exclude=c("Product"))

    DT.Acc.Using.perDPt <- DT.Acc.Using [, lapply(.SD, function(x) sum(as.numeric(x), na.rm=TRUE)), keyby=Kcols_DPt]

    setkeyIfNot(DT.UniqueUsers.perDP, Kcols_DPt)
    DT.Counts.DPt[DT.UniqueUsers.perDP]




    createARPUmeasures_ <- function(DT, by=key(DT)) {
        DT[, `:=`(  Rev_per_Stream   =  Revenue / Streams
                         , Rev_per_User     =  Revenue / Users
                         , Streams_per_User =  Streams / Users
                        )
        , by=by]
        return(invisible(DT))
    }


    # aggregateAndCreateARPUmeasures <- function(DT, by=key(DT), exclude=NULL) {
    # ## Cannot make this serf-referencing due to aggreateDT() calling unique()
    #     createARPUmeasures_ (aggregateDT(DT, by=by, exclude=exclude), by=by)
    # }


    createUSINGtable <- function(DT.Source, colsUsing.expr, keyCols=key(DT.Source), colsExcluding=NULL, addCompanyTokey=TRUE)  {
    ## colsExcluding should correspond to the **names** in colsUsing.expr
        ## Fix names
        cnms <- names(colsUsing.expr)
        cnms <- ifelse(cnms=="", as.character(colsUsing.expr),  cnms)

        if (addCompanyTokey && "Company" %in% cnms && !is.null(keyCols))
            keyCols <- c(keyCols, "Company")

        ## make sure all included
        if (any(wh <- colsExcluding %ni% cnms)) 
            warning("Some columns in 'colxExcluding' are not in the **names** of 'colsUsing.expr'\nOffenders are: ", paste_l(colsExcluding[wh], 5))

        ## only keep those which are not being excluded
        colsUsing.expr <- colsUsing.expr[ cnms %ni% colsExcluding]
        createARPUmeasures_(setkeyIfNot(copy(DT.Source[, eval(colsUsing.expr)]), key=keyCols))
    }



    ## How we will create the DT's
    cols.expr.Spot <- quote(list(Company="Spotify", Month, Product_Tier, 
                             Revenue=Net_revenue, Streams=Total_tracks, Users=Registered_users))

    cols.expr.Orch <- quote(list(Company="Orchard", Month, Product_Tier, 
                             Revenue=Payable_Using, Streams=Rightholders_tracks, Users=Registered_users))

    # cols.expr.Spot <- quote(list(Month, Product_Tier, 
    #                          Revenue=Net_revenue, Streams=Total_tracks, Users=Registered_users))


    DT.Counts.DPt.Spot <- createUSINGtable(DT.Acc.perDCnP, cols.expr.Spot, Kcols_DPt)

    setkeyIfNot(DT.UniqueUsers.perDP, Kcols_DPt)


    DT.Counts.DPt[DT.UniqueUsers.perDP]


    DT.Counts.D[, c(".none") := NULL]

    DT.Counts.DPt <- DT.Acc.perDCnP[, eval(cols.expr), key=Kcols_DPt]    
    DT.Counts.D   <- copy(DT.Counts.DPt)[, Product_Tier := NULL]

    createARPUmeasures_(DT.Counts.DPt)
    createARPUmeasures_(DT.Counts.D)

## ------------------------------------------------------------------------ ##

## Plot
P.Revenue <- 
{
  ggplot() + 
    geom_bar(stat="identity", data=DT.Counts.DPt, aes(x=Month, y=Orchard_Revenue, fill=Product_Tier)) + 
    scale_y_continuous(labels=dollar) + 
    scale_fill_manual(values=Colors) + 

    ## Moving Average
    geom_line(data=DT.Acc.Using.perD, aes(x=Month, y=Orchard_Revenue_MA), color="red") +
    labs(x="Date", y="The Orchard's Monthly Revenue (in USD)"
        , title="SPOTIFY\nMonthly Revenue by Product Tier\n\nRed line is 3-month MA of monthly totals."
        , fill="Product Tier")
}

P.ARPU <- 
{
  ggplot(data=DT.Acc.Using.perD, aes(x=Month)) + 
    geom_bar(stat="identity", aes(y=Spotify_Revenue_Per_User_Annua), fill="#5800ff", alpha=0.8) + 
    scale_y_continuous(labels=dollar) + 
    # scale_fill_manual(values=Colors) + 
    ## Moving Average
    geom_text(aes(label=asCurr(Spotify_Revenue_Per_User_Annua), y=Spotify_Revenue_Per_User_Annua*1.025)
        , width=rel(.28), size=4) +
    geom_line(aes(y=Spotify_Revenue_Per_User_Annua_MA), color="red") +
    labs(x="Date", y="Annualized Revenue Per User (in USD)"
        , title="SPOTIFY\nAnnualized Revenue Per User 2013\n(based on Spotify ) \n\nRed line is 3-month MA.")
}



## Plot
P.Usership <- 
{
  ggplot() + 
    geom_bar(stat="identity", data=DT.Acc.Using.perDPt, aes(x=Month, y=Spotify_Registered_Users, fill=Product_Tier)) + 
    millions.y() + 
    scale_fill_manual(values=Colors) + 
    ## Moving Average
    geom_line(data=DT.Acc.Using.perD, aes(x=Month, y=Spotify_Registered_Users_MA), color="red") +

    ## Average streams per user
    geom_line(data=DT.Acc.Using.perD, aes(x=Month, y=Spotify_Streams_Per_User*1e6), color="Blue")  + 

    ## Revenue from fact_sales
#    geom_line(data=qres.rev, aes(x=Month, y=net), color="dark blue") + 

    labs(x="Date", y="Registered Users (in Millions)"
        , title="SPOTIFY\nUsership by Product Tier\n\nRed line is 3-month MA of monthly totals (in millions).\nBlue line is average number of streams per user (in units)."
        , fill="Product Tier")
}


P.ARPU.tier <- 
{
  ggplot(data=ArpuTier, aes(x=Month)) + 
    geom_bar(stat="identity", aes(y=V1, fill=Product_Tier), alpha=0.8) + 
    scale_y_continuous(labels=dollar) + 
    # scale_fill_manual(values=Colors) + 
    ## Moving Average
    # geom_text(aes(label=asCurr(Spotify_Revenue_Per_User_Annua), y=Spotify_Revenue_Per_User_Annua*1.025)
    #     , width=rel(.28), size=4) +
    # geom_line(aes(y=Spotify_Revenue_Per_User_Annua_MA), color="red") +
    scale_fill_manual(values=Colors) +
    facet_grid(Product_Tier~.) +
    labs(x="Date", y="Annualized Revenue Per User (in USD)"
        , title="SPOTIFY\nAnnualized Revenue Per User 2013\n\nRed line is 3-month MA.") 
}