## ------------------------------------------------------------------------------------------------ ##
## ------------------------------------------------------------------------------------------------ ##
##  This is the main file to estimate/forecast the total number of Snowflake credits 
##  we will consume for the next two years (well, three)
##  
##  This also compares against Vertica costs when we were considering them, 
##    although that is less important now
##  
##  The yearly totals get written to two files: 
##    f.out.snowflake.wh_usage_in_credits ["~/git/orch/out/PricingModelForDB/snowflake.wh_usage_in_credits.tsv"]
##    f.out.snowflake.storage_in_TBs  ["~/git/orch/out/PricingModelForDB/snowflake.storage_in_TBs.tsv"]
##  
##  All of the starting figures are in the file
##    ~/git/orch/src/PricingModelForDB/supportFns/starting_sizes.r
##
##  Initial inputs have been separated into "Linear growth" and "Exponential Growth"
##    with different model inputs
##  
## ------------------------------------------------------------------------------------------------ ##
## ------------------------------------------------------------------------------------------------ ##


# screen -xRR SFLocal

setScience("PricingModelForDB", create=TRUE, subl=FALSE, load=FALSE)
setGitBranchToSystem();  .g()
lib(reshape2)
lib(lubridate)

## Starting credits
storage_TBs <- 50
usage_monthly_credits <- 8000

## Average Monthly Growth Rate
storage_AMGR <- 1.8
usage_AMGR <- 1.8

## Growth rate on the growth rate
## TBD
DT.tbls_in_snowflake <- sizes_of_all_tables(refresh=FALSE, overwrite.ifexists_and_loading_from_disk=TRUE)

## FYI :: Here is the starting TB count
c(starting_sizes_TB.expo_growth, starting_sizes_TB.linear_growth) %>% 
  sum %>% 
  round(1) %>% 
  catn("Total starting size is: ", ., "TB")


## Combine the exponential growth and linear growth data sets into a single data.table
DT.data_sizes <- rbind(
    starting_sizes_TB.expo_growth %>% {data.table(tbl=names(.), growth_type="expo", TB=.)}
  , starting_sizes_TB.linear_growth %>% {data.table(tbl=names(.), growth_type="linear", TB=.)}
)

months_out <- 36 # as.Date("2019-04-01") %>% monthsDiff(x=., y=monthFloor(today()), verbose=FALSE)
include_in_vertica <- c(include_in_vertica.expo_growth, include_in_vertica.linear_growth)

estimates <- c(vlow = 0.75, low = 0.85, mid = 1, high = 1.2, vhigh = 1.35)
ll_DT.growths <- emptylist(estimates)

estimates_usage <- c(vlow = 0.55, low = 0.85, mid = 1, high = 1.2, vhigh = 1.35)
ll_DT.usage_growth <- emptylist(estimates_usage)


### USAGE
DT.usage <- data.table(purpose=names(credits.starting_usage), credits=credits.starting_usage)
for (est in names(estimates_usage)) {
  mult = estimates_usage[[est]]
  ll_DT.usage_growth[[est]] <- DT.usage[, 
          linear_growth_model(starting=credits, perc_increase=mult*credits.monthly_rate_for_linear_growth[[purpose]], total_months_to_model=months_out, value_nm="credits", decay_of_rate=ifelse(purpose=="tech_engineers", 0, 0.030)), by=purpose
          ][, est := est]
}
DT.usage_growth <- rbindlist(ll_DT.usage_growth)
DT.usage_growth[, month := nextMonth(monthFloor(today()), months_out)]
DT.usage_growth[, years_out := months_out %/% 12]
DT.usage_growth[, months_out := NULL]

DT.usage_growth[, sum(credits), keyby=list(month, est)]

## GRAPHING THE USAGE
if (FALSE) 
{
  ggLinegraph(DT.usage_growth, dotsize=FALSE, linetype="purpose", x="month", y="credits", color="est", ylims=c(0, max(25000, DT.usage_growth$credits)), title="Snowflake Usage over 3 years by Purpose")
  ggLinegraph(DT.usage_growth[, list(credits=sum(credits)), keyby=list(month, est)], dotsize=FALSE, x="month", y="credits", color="est", ylims=c(0, max(35000, DT.usage_growth$credits)), title="Snowflake Usage over 3 years by Purpose")

  tmp.months_using <- as.Date(c("2016-04-01", "2017-05-01", "2018-05-01", "2019-05-01"))
  DT.usage_growth[, list(credits=sum(credits)), keyby=list(month, est) ][ month %in% tmp.months_using] %>% dcast(month ~ est) %>% formnumb(round=-2) %>% print()
}


for (est in names(estimates)) {
  mult = estimates[[est]]
  ll_DT.growths[[est]] <- DT.data_sizes[, j={
        if (growth_type == "linear")
            linear_growth_model(starting=TB, perc_increase=mult*monthly_rate.linear_growth[[tbl]], total_months_to_model=months_out, value_nm="Snowflake_TB")
        else
            expo_growth_doubling_model(starting=TB, doubles_every=(1/mult)*months_to_double.expo_growth[[tbl]], total_months_to_model=months_out, value_nm="Snowflake_TB")
      }, by=list(tbl, growth_type)][, est := est]
}
DT.growth <- rbindlist(ll_DT.growths)[, est := setFactorOrder(est, ordering=names(estimates))]
## Add in Vertica TBs
DT.growth[, in_vertica := tbl %in% c(nwhich(include_in_vertica))]
DT.growth[, TB_3x.vertica := ifelse(in_vertica, Snowflake_TB * 3, 0)]
DT.growth[, TB_7x.vertica := ifelse(in_vertica, Snowflake_TB * 7, 0)]

## Add in Date, extract year_end_months for graphing, then drop the months_out column
DT.growth[, month := nextMonth(monthFloor(today()), months_out)]
DT.growth[, years_out := months_out %/% 12]
year_end_months <- DT.growth[months_out %% 12 == 11, sunique(month)]

DT.growth[, months_out := NULL]

setInfo(DT.growth, "TB Estimates with vlow to vhigh")
# ----------------------------
# ggLinegraph(DT.growth[est == "mid"], color="tbl", x="month", y="Snowflake_TB", vline=year_end_months)
# ggLinegraph(DT.growth[, list(Snowflake_TB=sum(Snowflake_TB)), keyby=list(month, growth_type)], color="growth_type", x="month", y="Snowflake_TB", vline=year_end_months)
# ggLinegraph(DT.growth[, list(Snowflake_TB=sum(Snowflake_TB)), keyby=list(month, est)], color="est", x="month", y="Snowflake_TB", smooth_se=TRUE, smooth="+", vline=year_end_months)
# -----------------------

incl.growth_type <- FALSE
kCols.tall <- c("DB_type", "est", "month") ## "years_out"
kCols.tall_no_month <- setdiff(kCols.tall, "month")


DT.total_data_sizes_tall <- aggregateDT(DT.growth, exclude=c("tbl", "in_vertica", if (!incl.growth_type) "growth_type"), colsToAgg=c("Snowflake_TB", "TB_3x.vertica", "TB_7x.vertica")) %>% data.table:::melt(id.vars=c(setdiff(kCols.tall, "DB_type"), "years_out"), value.name="TB_total", variable.name="DB_type")
setkeyIfNot(DT.total_data_sizes_tall, kCols.tall)

## Compute the incremental TB;  Perpetual licenses will be a function of this
DT.total_data_sizes_tall[, TB_total.round_to_5 := 5 * (1 + (TB_total %/% 5))]
## Only do yearly incrementals, with initial being the current val
DT.total_data_sizes_tall[month %in% c(min(month), nextMonth(year_end_months)), TB_incremental := c(TB_total[[1]], diff(TB_total)), by=kCols.tall_no_month]
## Fill in the blanks as 0
DT.total_data_sizes_tall[is.na(TB_incremental), TB_incremental := 0]

if (any(is.na(DT.total_data_sizes_tall$TB_incremental)))
  warning("There are NAs in TB_incremental -- there should not be.  \n\nHINT: Check your keys")

key(DT.total_data_sizes_tall)


## Add in Credit Usage, which ws calculated way up top
DT.usage_growth[, list(DB_type="Snowflake_TB", credits=sum(credits)), keyby=list(est, month, years_out)]
DT.usage_growth.aggd <- aggregateDT(DT.usage_growth[][, DB_type := "Snowflake_TB"], by=kCols.tall, colsToAgg=c("wh_usage_credits"="credits"))
DT.usage_growth.aggd[, DB_type := factor(DB_type, levels=levels(DT.total_data_sizes_tall$DB_type))]
DT.usage_growth.aggd[, est     := factor(est,     levels=levels(DT.total_data_sizes_tall$est))]
addColsFrom_(DT.total_data_sizes_tall, 
            DT.usage_growth.aggd,
            joinCols=kCols.tall,
            colsToBring=c("wh_usage_credits")
            )


## make sure the first key column is DB_type
stopifnot(identical(key(DT.total_data_sizes_tall)[[1]], "DB_type"))

## VERTICA COSTS
DT.total_data_sizes_tall[!.("Snowflake_TB")] [est == "mid"]
DT.total_data_sizes_tall[!.("Snowflake_TB"), storage  := vertica_storage_cost_perpetual(TB_incremental=TB_incremental)]
DT.total_data_sizes_tall[!.("Snowflake_TB"), support  := vertica_support_costs_annually(vertica.storage_costs=storage)]
DT.total_data_sizes_tall[!.("Snowflake_TB"), hw       := ifelse(month %in% year_end_months, vertica_hw_costs_annually(TB_total=TB_total.round_to_5), 0) %>% shiftUp(roll=TRUE, n=11), by=setdiff(kCols.tall_no_month, "years_out")]
DT.total_data_sizes_tall[!.("Snowflake_TB"), DBA      := vertica_DBA_resource_costs_monthly(TB_total.round_to_5)]

## SNOWFLAKE COSTS
DT.total_data_sizes_tall[, cost_per_credit := snowflake_cost_per_credit()]

DT.total_data_sizes_tall[.("Snowflake_TB"), storage := snowflake_stroage_costs_per_month(TB_total=TB_total)]
DT.total_data_sizes_tall[.("Snowflake_TB"), support := 0]
DT.total_data_sizes_tall[.("Snowflake_TB"), hw      := cost_per_credit * wh_usage_credits]
DT.total_data_sizes_tall[.("Snowflake_TB"), DBA     := 0]

## TOTALS & SUBTOTALS
DT.total_data_sizes_tall[, SubTotal_StorageSupport_Costs := storage + support]
DT.total_data_sizes_tall[, SubTotal_HardwareDBA_Costs    := hw + DBA]
DT.total_data_sizes_tall[, TOTAL_MONTHLY_COSTS           := storage + support + hw + DBA]

## View
DT.total_data_sizes_tall[est=="mid"][!.("Snowflake_TB")]
DT.total_data_sizes_tall[est=="mid"][.("Snowflake_TB")]


## PLOT Snowflake vs Vertica
DT_plot.yearly <- DT.total_data_sizes_tall[DB_type %in% c("Snowflake_TB", "TB_3x.vertica") & est %in% c("high", "mid", "low"), list(USD=sum(TOTAL_MONTHLY_COSTS)), keyby=list(est, years_out, DB_type)]
DT_plot.yearly[, vendor := factor(ifelse(DB_type == "Snowflake_TB", "Snowflake", "Vertica"), levels=c("Vertica", "Snowflake"))]
P.snowflake_vs_vertica_3yrs <- ggLinegraph(DT_plot.yearly, x="years_out", y="USD", color="vendor", linetype="est", title="Snowflake vs Vertica\nEstimated 3 year costs")
f.out.snowflake_vs_vertica_3yrs <- ggsave.out(P.snowflake_vs_vertica_3yrs)


## VIEW THE DATA
DT.total_data_sizes_tall[.("Snowflake_TB")][est == "mid"]
DT.total_data_sizes_tall[.("TB_3x.vertica")][est == "mid"]
DT.total_data_sizes_tall[est == "mid"] [1:60]
DT.total_data_sizes_tall[.("TB_3x.vertica")][est == "mid"] [, sum(TOTAL_MONTHLY_COSTS), keyby=years_out]

## ------------------------------ 
## JUST SNOWFLAKE ------ 
## ------------------------------ 
##    SUM UP FOR THE YEAR
DT.snowflake.yearly_agg <- DT.total_data_sizes_tall[DB_type == "Snowflake_TB"] %>% aggregateDT(exclude=c("DB_type", "month"), by=c("est", "years_out", "cost_per_credit"), showWarnings.info=FALSE)
tmp_DT.snowflake_melted <- melt.data.table(DT.snowflake.yearly_agg, id.vars=c("est", "years_out"), measure.vars=c("TB_total", "wh_usage_credits"))

## Sepearate it out into two tables
DT.snowflake.wh_usage_in_credits <- tmp_DT.snowflake_melted[variable == "wh_usage_credits"] %>% dcast.data.table(years_out ~ est)
DT.snowflake.storage_in_TBs      <- tmp_DT.snowflake_melted[variable == "TB_total"] %>% dcast.data.table(years_out ~ est)
## Add in description
DT.snowflake.wh_usage_in_credits[, what := "Usage in Credits"]
DT.snowflake.storage_in_TBs[, what := "Storage in TBs"]

## Combine into a single file for output
f.out.snowflake.wh_usage_in_credits <- writeDT(DT.snowflake.wh_usage_in_credits)
f.out.snowflake.storage_in_TBs  <- writeDT(DT.snowflake.storage_in_TBs)

.o(f.out.snowflake.wh_usage_in_credits)
.o(f.out.snowflake.storage_in_TBs)

.o(outDir)
## ------------------------------