library(glue)
library(magrittr)

query <- "ADD JAR hdfs:///wmf/refinery/current/artifacts/refinery-hive.jar;
CREATE TEMPORARY FUNCTION get_host_properties AS 'org.wikimedia.analytics.refinery.hive.GetHostPropertiesUDF';
USE wmf;
WITH static_maps AS (
  SELECT DISTINCT
    IF(
      get_host_properties(PARSE_URL(CONCAT('http://', uri_host, uri_path, uri_query), 'QUERY', 'domain')).project = '-',
      get_host_properties(PARSE_URL(CONCAT('http://', uri_host, uri_path, uri_query), 'QUERY', 'domain')).project_class,
      CONCAT(
        get_host_properties(PARSE_URL(CONCAT('http://', uri_host, uri_path, uri_query), 'QUERY', 'domain')).project,
        '.',
        get_host_properties(PARSE_URL(CONCAT('http://', uri_host, uri_path, uri_query), 'QUERY', 'domain')).project_class
      )
    ) AS project,
    REGEXP_REPLACE(REFLECT('java.net.URLDecoder', 'decode', PARSE_URL(CONCAT('http://', uri_host, uri_path, uri_query), 'QUERY', 'title'), 'UTF-8'), '\\\\s', '_') AS page_title
  FROM webrequest
  WHERE
    webrequest_source = 'upload'
    AND year = ${year} AND month = ${month} AND day = ${day}
    AND uri_host = 'maps.wikimedia.org'
    AND http_status IN('200', '304')
    AND uri_path RLIKE '^/img/.*\\.png$'
    AND PARSE_URL(CONCAT('http://', uri_host, uri_path, uri_query), 'QUERY', 'domain') IS NOT NULL
    AND PARSE_URL(CONCAT('http://', uri_host, uri_path, uri_query), 'QUERY', 'title') IS NOT NULL
    AND uri_query <> '?loadtesting'
),
maps_pvs AS (
  SELECT
    static_maps.project AS project,
    static_maps.page_title AS page_title,
    pvh.view_count AS pageviews
  FROM static_maps
  LEFT JOIN pageview_hourly pvh ON (
    static_maps.page_title = pvh.page_title
    AND static_maps.project = pvh.project
    AND pvh.year = ${year} AND pvh.month = ${month} AND pvh.day = ${day}
  )
),
daily_pvs AS (
  SELECT project, COUNT(DISTINCT(page_title)) AS pages_with_thumbnails, SUM(pageviews) AS pageviews
  FROM maps_pvs
  WHERE project IS NOT NULL
  GROUP BY project
)
SELECT
  daily_pvs.project AS project, pages_with_thumbnails, total_pages, pageviews, total_pageviews
FROM daily_pvs
LEFT JOIN (
  SELECT project, COUNT(DISTINCT(page_title)) AS total_pages, SUM(view_count) AS total_pageviews
  FROM pageview_hourly
  WHERE year = ${year} AND month = ${month} AND day = ${day} AND view_count > 0
  GROUP BY project
) AS project_pvs ON daily_pvs.project = project_pvs.project;"

end_date <- Sys.Date() - 1
start_date <- end_date - 60

filters <- paste0(c("", paste(" grep -v", c("JAVA_TOOL_OPTIONS", "parquet.hadoop", "WARN:", ":WARN"))), collapse = " |")

results <- do.call(rbind, lapply(
  seq(start_date, end_date, by = "day"),
  function(date) {
    message("Fetching data from ", format(date, "%d %B %Y"))
    year <- lubridate::year(date)
    month <- lubridate::month(date)
    day <- lubridate::mday(date)
    query <- glue(query, .open = "${", .close = "}")
    query_dump <- tempfile()
    cat(query, file = query_dump)
    results_dump <- tempfile()
    system(glue("export HADOOP_HEAPSIZE=1024 && hive -f {query_dump} 2> /dev/null {filters} > {results_dump}"))
    result <- read.delim(results_dump, sep = "\t", quote = "", as.is = TRUE, header = TRUE)
    file.remove(query_dump, results_dump)
    return(cbind(date = date, result))
  }
))

readr::write_tsv(results, "mapframe_pageviews.tsv")

library(magrittr)
library(ggplot2)
library(dplyr)

results <- readr::read_tsv("~/Desktop/mapframe_pageviews.tsv", col_types = "Dciiii")
results$prop <- results$pageviews / results$total_pageviews
results$prop[is.na(results$prop)] <- 0
results$include <- FALSE
results$include[results$project %in% unique(results$project[results$prop >= 0.01])] <- TRUE

results %>%
  group_by(date) %>%
  summarize(prop = sum(pageviews, na.rm = TRUE) / sum(total_pageviews, na.rm = TRUE)) %>%
  ggplot(aes(x = date, y = prop)) +
  geom_bar(stat = "identity", fill = "gray40", color = "white") +
  scale_x_date(date_breaks = "1 week", date_labels = "%d %b") +
  scale_y_continuous(labels = scales::percent_format()) +
  labs(
    x = "Date", y = "Proportion of pageviews",
    title = "Overall proportion of pageviews that pages with mapframes are responsible for",
    subtitle = "Total across all projects with mapframes"
  )

results %>%
  group_by(date) %>%
  summarize(prop = sum(pages_with_thumbnails) / sum(total_pages)) %>%
  ggplot(aes(x = date, y = prop)) +
  geom_bar(stat = "identity", fill = "gray40", color = "white") +
  scale_x_date(date_breaks = "1 week", date_labels = "%d %b") +
  scale_y_continuous(labels = scales::percent_format()) +
  labs(
    x = "Date", y = "Proportion of pages viewed",
    title = "Overall proportion of pages viewed that have a mapframe",
    subtitle = "Out of total pages viewed that day"
  )

results %>%
  filter(include) %>%
  ggplot(aes(x = date, y = prop)) +
  geom_bar(stat = "identity", fill = "gray40", color = "white") +
  scale_x_date(date_breaks = "1 week", date_labels = "%d %b") +
  scale_y_continuous(labels = scales::percent_format(), breaks = seq(0, 0.1, 0.02)) +
  facet_wrap(~ project) +
  labs(
    x = "Date", y = "Proportion of pageviews",
    title = "Proportion of pageviews that pages with mapframes are responsible for",
    subtitle = "Only including projects & languages for which we've observed a share of at least 1%"
  )

results %>%
  group_by(project) %>%
  mutate(include = any(pages_with_thumbnails > 100)) %>%
  ungroup %>%
  filter(include) %>%
  mutate(prop = pages_with_thumbnails / total_pages) %>%
  ggplot(aes(x = date, y = prop)) +
  geom_bar(stat = "identity", fill = "gray40", color = "white") +
  scale_x_date(date_breaks = "1 week", date_labels = "%d %b") +
  scale_y_continuous(labels = scales::percent_format(), breaks = seq(0, 0.1, 0.01)) +
  facet_wrap(~ project) +
  labs(
    x = "Date", y = "Proportion of pages viewed",
    title = "Proportion of pages viewed that have a mapframe out of the total pages viewed for that project",
    subtitle = "Only including projects & languages for which at least 100 pages with a mapframe have been viewed on any one day"
  )