# create a new, summary, dataset based on the average elapsed seconds per day
# calculate the median, sigma and upper and lower "control" limits
# display in a Highchart chart
# adapted in part from https://norbertocioffi.medium.com/shewhart-control-charts-with-r-f62a3359a4a1

# placeholder for highchart
highcharter::highchartOutput("summary_chart", height = "500px")

# render highchart
output$summary_chart <- highcharter::renderHighchart({

  # create new summary data frame from df: date (no time), average_elapsed_seconds
  df_summary <- df %>%
    # get date, no time
    dplyr::mutate(date = as.Date(StartTime)) %>%
    # group by date
    dplyr::group_by(date) %>%
    # average elapsed seconds per day (ignore DatabaseName)
    dplyr::summarise(
      average_elapsed_seconds = mean(elapsed_seconds),
      # drop fields not explicitly named here
      .groups = "drop"
    ) %>%
    # order by date (necessary for chart)
    dplyr::arrange(date)

  # calculate limits using Median Absolute Deviation (MAD) method
  center <- median(df_summary$average_elapsed_seconds)
  # calculate sigma using MAD, multiplied by constant to make comparable to standard deviation for normal distribution
  sigma <- mad(df_summary$average_elapsed_seconds, constant = 1.4826)
  # Upper Control Limit, rounded to nearest whole second three sigma above the center/median
  ucl <- round(center + 3L * sigma)
  # Lower Control Limit, rounded to nearest whole second
  lcl <- round(center - 3L * sigma)
  # reset LCL to zero if lower than zero (duration in seconds cannot be *less* than zero)
  lcl <- max(lcl, 0L)

  # add coloring for if plot points are outliers (only for above upper range)
  # red for out of range, transparent for in range
  df_summary <- df_summary %>%
    dplyr::mutate(
      point_color = ifelse(average_elapsed_seconds > ucl, "red", "transparent")
    )
  # new field "ts", convert date to JS timestamp
  df_summary <- df_summary %>%
    dplyr::mutate(ts = highcharter::datetime_to_timestamp(date))

  # output the chart
  highcharter::highchart() %>%
    # line chart
    highcharter::hc_chart(type = "line") %>%
    # title
    highcharter::hc_title(
      text = "Database average backup duration by day",
      align = "left",
      x = 55L
    ) %>%
    highcharter::hc_subtitle(
      text = "Click a point to see backup details by database for that day",
      align = "left",
      x = 55L
    ) %>%
    # axis labels
    # x axis is date, formatted as day/month (e.g. 1/Jan)
    highcharter::hc_xAxis(
      type = "datetime",
      labels = list(format = "{value:%e/%b}")
    ) %>%
    # y axis is average duration in seconds
    highcharter::hc_yAxis(
      title = list(text = "Avg. duration (seconds)"),
      # format with thousands separator and no decimal places
      labels = list(format = "{value:,.0f}"),
      # on y axis, add dotted green lines for UCL and LCL, and dashed purple line for center/median
      # left-aligned labels in same colors
      plotLines = list(
        list(value = ucl, color = "#3EA57B", width = 1L, dashStyle = "Dot", opacity = 0.6, label = list(text = paste0("Upper control limit = ", ucl, " secs"), align = "left", x = 0L, style = list(color = "#3EA57B"))),
        list(value = lcl, color = "#3EA57B", width = 1L, dashStyle = "Dot", opacity = 0.6, label = list(text = paste0("Lower control limit = ", lcl, " secs"), align = "left", x = 0L, style = list(color = "#3EA57B"))),
        list(value = center, color = "#9481CC", width = 1L, dashStyle = "Dash", opacity = 0.6, label = list(text = paste0("Median/centerline = ", round(center, 2L), " secs"), align = "left", x = 0L, style = list(color = "#9481CC")))
      ),
      # set y axis to start at zero, and hide the zero label (as it's obvious from the axis)
      # start at zero
      min = 0L,
      # don't display the zero label
      showFirstLabel = FALSE
    ) %>%
    # custom tooltip
    highcharter::hc_tooltip(
      useHTML = TRUE,
      formatter = JS("
        function() {
          return '<b>Date:</b> ' + Highcharts.dateFormat('%e/%b/%Y', this.x) + '<br>' +
                 '<b>Avg. duration:</b> ' + Highcharts.numberFormat(this.y, 0) + ' seconds';
        }
      ")
    ) %>%
    # line series
    highcharter::hc_add_series(
      name = "Average duration",
      data = purrr::map2(
        # map timestamp to x, and elapsed seconds to y
        df_summary$ts,
        df_summary$average_elapsed_seconds,
        # output a list
        ~list(x = .x, y = .y)
      ),
      # heavy line
      lineWidth = 4L,
      color = "#8f8f8f",
      opacity = 0.8
    ) %>%
    # points
    highcharter::hc_add_series(
      name = "Points",
      type = "scatter",
      data = purrr::pmap(
        # map timestamp to x, elapsed seconds to y, and color
        list(df_summary$ts, df_summary$average_elapsed_seconds, df_summary$point_color),
        # set color based on if above UCL (red) or not
        function(x, y, col) {
          # output a list
          list(x = x, y = y, color = col)
        }
      ),
      # slightly larger point
      marker = list(radius = 3L)
    ) %>%
    # disable the legend (as only one series, points are colored by if above UCL or not, legend not needed)
    highcharter::hc_legend(
      enabled = FALSE
    ) %>%
    # for clicks on a point, set "selected_date" to the date of the clicked point
    # (converted from JS timestamp to R date)
    highcharter::hc_plotOptions(
      series = list(
        cursor = "pointer",
        point = list(
          events = list(
            # set selected date on click, convert from JS timestamp to R date in format YYYY-MM-DD
            # set the input value input$selected_date, which triggers a change to selected_date_outer$date
            click = JS("function() { Shiny.setInputValue('selected_date', new Date(this.x).toISOString().slice(0,10), { priority: 'event' }); }") # nolint: line_len
          )
        )
      )
    )
})