# data.table way this stuff feels faster than dplyr but isn't very FP when using := methods
# alternatively, use the .() aka list() feature and create a new table. Still faster than dplyr or plyr
# https://mran.microsoft.com/web/packages/data.table/vignettes/datatable-intro.html
library(data.table) # for fread and other data.table functions
library(tidyverse)  # for as_tibble to feed into ggplot
library(lubridate)  # for round_date
library(fasttime)   # for fastPOSIXct

dtCLE01=fread("c:/kewoo/eai/CLE.Identity.d20171204.csv")
AESTDiff <- 36000
interval.length <- "1 seconds"

# exploratory
str(dtCLE01)
nrow(dtCLE01)
names(dtCLE01)
dtCLE01[,.(TIME_STAMP, APPLICATIONID)]
# end exploration

tb01.tx.times.all <-dtCLE01[, list(transactionid,
                                componentname,
                                startPct = round_date(fastPOSIXct(start)-AESTDiff, interval.length),
                                endtPct = round_date(fastPOSIXct(endt)-AESTDiff, interval.length))
                         ]
tb01.expandedIntervals <- tb01.tx.times.all[, list(intervals = seq(startPct, endtPct, by=1)), by = transactionid
                             ][, list(txCount = .N), by = intervals]
ggplot() +
  geom_line(data=tb01.expandedIntervals, aes(x=intervals,y=txCount), color='blue')






# FOR FILTERED TIME RANGE
start.AEST <- fastPOSIXct("2017-12-04 21:17:00")-36000
end.AEST <- fastPOSIXct("2017-12-04 21:32:00")-36000
tb01.tx.times.filtered <- tb01.tx.times.all[startPct > start.AEST & endtPct < end.AEST]
tb01.expandedIntervals <- tb01.tx.times.filtered[, list(intervals = seq(startPct, endtPct, by=1)), by = transactionid
                                            ][, list(txCount = .N), by = intervals]
ggplot() +
  geom_line(data=tb01.expandedIntervals, aes(x=intervals,y=txCount), color='blue')








dtER=fread("c:/kewoo/eai/EXCEPTIONREC.identity.d20171204.csv")
tb02.tx.times.all <-dtER[, list(transactionid,
                                   COMPONENTNAME,
                                   endtPct = round_date(fastPOSIXct(TIME_STAMP)-AESTDiff, interval.length))]
tb02.txCounts <- tb02.tx.times.all[, list(txCount = .N), by = endtPct]

ggplot() +
  geom_line(data=tb01.expandedIntervals, aes(x=intervals,y=txCount), color='blue') +
  geom_line(data=tb02.txCounts, aes(x=endtPct,y=txCount), color='red')
  

# 20171215: The reason there's a drop in txCount during a service interruption
#           is because startPct == endPct caused by the group by in the original extracting SQL
#           Solution is to extract actual endPct from EXCEPTIONREC joining via transactionid
# first, take outer join
dtOJ <- tb02.tx.times.all[tb01.tx.times.all, on = "transactionid"]
# second, populate blank (NA) startPct values with i.startPct
dtOJ[is.na(endtPct), endtPct := i.endtPct]
dtOJ.expandedIntervals <- dtOJ[, list(intervals = seq(startPct, endtPct, by=1)), by = transactionid
                                            ][, list(txCount = .N), by = intervals]
ggplot() +
  geom_line(data=dtOJ.expandedIntervals, aes(x=intervals,y=txCount), color='blue')