if (!grepl("^stat1", Sys.info()["nodename"])) {
  message("Creating an auto-closing SSH tunnel in the background...")
  # See https://gist.github.com/scy/6781836 for more info.
  system("ssh -f -o ExitOnForwardFailure=yes stat1006.eqiad.wmnet -L 3307:analytics-store.eqiad.wmnet:3306 sleep 10")
  library(RMySQL)
  con <- dbConnect(MySQL(), host = "127.0.0.1", group = "client", dbname = "log", port = 3307)
} else {
  con <- wmf::mysql_connect("log")
}

library(glue)
library(magrittr)

yyyymmdd <- format(Sys.Date() - 1, "%Y%m%d")
revision_number <- "16909631"

query <- glue("SELECT
  timestamp AS ts, wiki,
  event_uniqueId AS event_id,
  event_searchSessionId AS session_id,
  event_pageViewId AS page_id,
  event_action AS event,
  event_checkin AS checkin
FROM TestSearchSatisfaction2_{revision_number}
WHERE
  LEFT(timestamp, 8) = '{yyyymmdd}'
  AND wiki RLIKE 'wiki$'
  AND NOT wiki RLIKE '^(arbcom)|(be_x_old)'
  AND NOT wiki IN('commonswiki', 'mediawikiwiki', 'metawiki', 'checkuserwiki', 'donatewiki', 'collabwiki', 'foundationwiki', 'incubatorwiki', 'legalteamwiki', 'officewiki', 'outreachwiki', 'sourceswiki', 'specieswiki', 'stewardwiki', 'wikidatawiki', 'wikimania2017wiki', 'movementroleswiki', 'internalwiki', 'otrs_wikiwiki', 'projectcomwiki', 'ombudsmenwiki', 'votewiki', 'chapcomwiki', 'nostalgiawiki', 'otrs_wikiwiki')
  AND event_source = 'autocomplete'
  AND event_subTest IS NULL
  AND event_articleId IS NULL
  AND event_action IN('visitPage', 'checkin');")

# Fetch data from MySQL database:
results <- wmf::mysql_read(query, "log", con)
wmf::mysql_close(con)

results %<>%
  dplyr::mutate(ts = lubridate::ymd_hms(ts)) %>%
  dplyr::arrange(session_id, event_id, ts) %>%
  dplyr::distinct(session_id, event_id, .keep_all = TRUE) %>%
  dplyr::arrange(wiki, session_id, page_id, desc(event), ts) %>%
  dplyr::select(wiki, ts, session_id, page_id, event, checkin) %>%
  dplyr::group_by(session_id, page_id) %>%
  dplyr::filter(event == "visitPage" | (event == "checkin" & checkin == max(checkin, na.rm = TRUE))) %>%
  dplyr::ungroup() %>%
  dplyr::mutate(language = dplyr::case_when(
    wiki == "enwiki" ~ "English",
    wiki %in% c("cawiki", "frwiki") ~ "French and Catalan",
    TRUE ~ "Other languages"
  )) %>%
  data.table::data.table(key = c("wiki", "language", "session_id", "page_id"))

checkins <- c(0, 10, 20, 30, 40, 50, 60, 90, 120, 150, 180, 210, 240, 300, 360, 420)
# ^ this will be used for figuring out the interval bounds for each check-in
# Treat each individual search session as its own thing, rather than belonging
#   to a set of other search sessions by the same user.
page_visits <- as.data.frame(results[, {
  if (any(.SD$event == "checkin")) {
    last_checkin <- max(.SD$checkin, na.rm = TRUE)
    idx <- which(checkins > last_checkin)
    if (length(idx) == 0) idx <- 16 # length(checkins) = 16
    next_checkin <- checkins[min(idx)]
    status <- ifelse(last_checkin == 420, 0, 3)
    data.table::data.table(
      `last check-in` = as.integer(last_checkin),
      `next check-in` = as.integer(next_checkin),
      status = as.integer(status)
    )
  }
}, by = c("wiki", "language", "session_id", "page_id")])

surv <- survival::Surv(
  time = page_visits$`last check-in`,
  time2 = page_visits$`next check-in`,
  event = page_visits$status,
  type = "interval"
)
fit <- survival::survfit(surv ~ page_visits$language)
p <- survminer::ggsurvplot(fit, data = page_visits, risk.table = FALSE)
p <- p + ggplot2::labs(
  x = "Time (seconds)",
  title = glue("Time spent on Wikipedia Search Result Pages (SRPs) on {Sys.Date() - 1}, by language"),
  subtitle = "Dashed lines indicate which survival probabilities we track"
) + ggplot2::scale_y_continuous(labels = scales::percent_format()) +
  ggplot2::scale_color_brewer(palette = "Set1") +
  ggplot2::geom_hline(yintercept = c(0.10, 0.25, 0.50, 0.75, 0.90, 0.95, 0.99), linetype = "dashed")
print(p)