library(tidyverse)
library(lubridate)
library(arrow)
options(scipen=999)

path <- "your_path_here"

getsession_info <- function(requests_df, quartile, year, outfile){
  
  # calculate summary statistics per session
  sessioninfo <- requests_df %>%
    group_by(course_id) %>%
    mutate(
      start_course = min(timestamp),
      end_course = endcourse) %>%
    group_by(course_id, user_id, session_no) %>%
    summarize(
      n_clicks = n(),
      start_course = first(start_course), 
      end_course = first(end_course), 
      firsttime = (hour(first(timestamp)) + minute(first(timestamp))/60) - 6,
      firsttime = ifelse(firsttime < 6, firsttime + 18, firsttime - 6),
      # change first time of the day to numerical, shifted to start at 6 am
      # (6:00 = 0, 6:30 = 0.50, 22:00 = 16, 02:00 = 20)
      starttime = min(timestamp),
      endttime = max(timestamp),
      totaltime = endttime - starttime,
      totaltime_min = ifelse(!is.na(first(totaltime)), first(totaltime)/60, NA),
      interval_time = first(diff_time),
      schedule_time = first(timestamp[grepl("schedule", url)]),
      courseinfo_time = first(timestamp[grepl("course-information", url)]),
      studyguide_time = first(timestamp[web_application_action == "syllabus"])) %>%
    ungroup() %>%
    # at least 2 clicks per session
    filter(n_clicks >= 2)
  
  # Summarize the session statistics over the full course
  session_sum <- sessioninfo %>%
    group_by(course_id, user_id) %>%
    summarize(
      time_to_first_login = as.numeric(first(starttime) - 
                                         first(start_course)),
      time_to_first_schedule = as.numeric(first(schedule_time) - 
                                            first(start_course)),
      time_to_first_courseinfo = as.numeric(first(courseinfo_time) - 
                                              first(start_course)),
      time_to_first_studyguide = as.numeric(first(studyguide_time) - 
                                              first(start_course)),
      time_to_last_login = as.numeric(last(starttime) - 
                                        first(as.POSIXct(end_course))),
      n_clicks = sum(n_clicks, na.rm = T),
      n_sessions = n(),
      totalsessiontime_min = sum(totaltime_min, na.rm = T),
      m_sessiontime = as.numeric(mean(totaltime, na.rm = T)),
      sd_sessiontime = sd(totaltime, na.rm = T),
      m_intervaltime = mean(interval_time, na.rm = T),
      sd_intervaltime = sd(interval_time, na.rm = T),
      max_intervaltime = max(interval_time, na.rm = T),
      # map all times to one day, to 
      m_starttime = mean(firsttime, na.rm = T)
    ) %>%
    ungroup()
  
   # calculate summary statistics per session per week
  session_weekinfo <- requests_df %>%
    group_by(course_id, user_id, weeklabel, session_no) %>%
    summarize(
      n_clicks = n(),
      firsttime = (hour(first(timestamp)) + minute(first(timestamp))/60) - 6,
      firsttime = ifelse(firsttime < 6, firsttime + 18, firsttime - 6),
      starttime = min(timestamp),
      endttime = max(timestamp),
      totaltime = endttime - starttime,
      totaltime_min = ifelse(!is.na(totaltime), totaltime/60, NA),
      interval_time = first(diff_time)) %>%
    ungroup() %>%
    # at least 2 clicks per session
    filter(n_clicks >= 2)
  
  # Summarize the weekly session statistics over the full course
  session_sumweek <- session_weekinfo %>%
    group_by(course_id, user_id, weeklabel) %>%
    summarize(
      n_clicks = sum(n_clicks, na.rm = T),
      n_sessions = n(),
      totalsessiontime_min = sum(totaltime_min, na.rm = T),
      m_sessiontime = as.numeric(mean(totaltime, na.rm = T)),
      sd_sessiontime = sd(totaltime, na.rm = T),
      m_intervaltime = mean(interval_time, na.rm = T),
      sd_intervaltime = sd(interval_time, na.rm = T),
      max_intervaltime = max(interval_time, na.rm = T),
      m_starttime = mean(firsttime, na.rm = T)
    ) %>%
    ungroup()
  
  # sum per first vs. second half of the course
  session_sumweekhalf <- session_weekinfo %>%
    mutate(
      half = case_when(
       (weeklabel %in% c("w1", "w2", "w3", "w4", "w3a") |
                       (weeklabel %in% c("w5") & quartile == 4)) ~ "half1",
       (weeklabel %in% c("w5", "w6", "w7", "w8", "w9", "w6a")) ~ "half2")) %>%
    filter(!is.na(half)) %>%
        group_by(course_id, user_id, half) %>%
        summarize(
          sd_sessiontime = sd(totaltime, na.rm = T),
          sd_intervaltime = sd(interval_time, na.rm = T)
    ) %>%
    pivot_wider(id_col = c(course_id, user_id), 
                names_from = half,
                values_from = c(sd_sessiontime, sd_intervaltime))
  
  # convert to datawide and merge indicators
  session_weekwide <- session_sumweek %>%
    pivot_wider(id_col = c(course_id, user_id), names_from = weeklabel,
                values_from = c(n_clicks, n_sessions, totalsessiontime_min, 
                                m_sessiontime, sd_sessiontime, m_intervaltime, 
                                sd_intervaltime,
                                max_intervaltime, m_starttime)) %>% 
    left_join(session_sum) %>%
    left_join(session_sumweekhalf) %>%
    mutate_at(vars(starts_with("n_"), 
                   starts_with("m_sessiontime"),
                   starts_with("totalsessiontime_min")),
              ~replace_na(., 0))
  
  # save session indicators to a separate file
  write.csv(session_weekwide, paste0(path, outfile),
            row.names = FALSE)
  
  session_weekwide
}

getassignment_info <- function(requests_df, quartile, year, outfile){
  requests4 <- requests_df
  
  # load assignments 
  assign_dim <- read_parquet(paste0(path, "assignment_dim.snappy.parquet"), 
                           as_tibble = TRUE,
                           col_types = cols(.default = "c")) %>%
    na_if("\\N")
  assign_fact <- read_parquet(paste0(path, "assignment_fact.snappy.parquet"), 
                            as_tibble = TRUE, 
                            col_types = cols(.default = "c")) %>%
    na_if("\\N") 
  assign_submission_fact <- read_parquet(paste0(path, 
                                              "submission_fact.snappy.parquet"), 
                                       as_tibble = TRUE, 
                                       col_types = cols(.default = "c")) %>%
    na_if("\\N")
  
  assign_submission_fact2 <- assign_submission_fact %>%
    #remove quizzes & wiki submissions
    filter(!is.na(quiz_id) | !is.na(wiki_id)) %>%
    mutate_at(c("score"), as.numeric)
  assign_submission_dim <- read_parquet(paste0(path, 
                                 "submission_dim.snappy.parquet"), 
                                      as_tibble = TRUE, 
                                 col_types = cols(.default = "c")) %>%
    na_if("\\N")
  
  ####### transform & merge tables #############################################
  # merge assignment_fact and assignment_dim
  assign_fact_dim <- assign_dim %>%
    # remove unpublished assignments
    filter(workflow_state != "unpublished") %>%
    left_join(assign_fact,by = c("id" = "assignment_id", "course_id",
                               "points_possible")) %>%
    select(-updated_at, -created_at, -workflow_state)
  
  
  # filter only interaction with assignments from requests
  requests_assignment <- requests4 %>%
    filter(!is.na(assignment_id)) %>%
    mutate(assignment_id = as.numeric(assignment_id),
           # get canvas_id from URL in requests
           assignment_canvas_id = gsub(".*/assignments/","", url),
           assignment_canvas_id = as.numeric(gsub("/.*", "", 
                                                  assignment_canvas_id)))
  
  # merge assignment submission dim and fact
  assign_subm_fact_dim <- assign_submission_dim %>%
    # only submissions after 2019
    filter(!is.na(submitted_at), submitted_at > "2020") %>%
    left_join(assign_submission_fact2, by = c("assignment_id", 
                                            "id" = "submission_id",
                                           "user_id")) %>%
    rename("submission_id" = "id")
  
  # merge submissions with assignment fact dim table
  assignment_submission <- assign_subm_fact_dim %>%
    select(-canvas_id) %>%
    left_join(assign_fact_dim, by = c("assignment_id"= "id","course_id"))

  
  # extract courses and add weeklabels
  assignment_submission2 <- assignment_submission %>%
    mutate(date_submission = as.POSIXlt(submitted_at),
           quartile = quartile,
           weekno = strftime(date_submission,"%V"),
           weeklabel = calculate_weeklabel(weekno, quartile, year))
  
  
  # remove clicks outside timeframe & filter courses
  assignment_submission3 <- assignment_submission2 %>%
    filter(!is.na(weeklabel), course_id %in% requests4$course_id)
  
  
####### Summarize assignment info ##############################################
  ### assignment CLICKS ONLY (via requests)
  # sum over full course
  assignment_sum <- requests_assignment %>%
    group_by(course_id, user_id) %>%
    summarize(
      n_assignmentclicks = n()
    ) %>%
    ungroup()
  
  # sum per week
  assignment_sumweek <- requests_assignment %>%
    group_by(course_id, user_id, weeklabel) %>%
    summarize(
      n_assignmentclicks = n()
    ) %>%
    ungroup()
  
 ### assignment SUBMISSION INFO
  # create one submission entry per (unique) assignment per user per course
  assignment_submission4 <- assignment_submission3 %>%
    group_by(course_id, assignment_id, user_id) %>%
    arrange(as.POSIXct(submitted_at)) %>%
    summarize(
      firstattempt_date = min(submitted_at),
      weeklabel = first(weeklabel), # weeklabel of first attempt
      lastattempt_date = last(submitted_at),
      late = ifelse(!is.na(due_at), firstattempt_date > first(due_at), 0)
    ) %>%
    ungroup()
  
  # summarize assignment indicators for full course
  assignment_submission4_sum <- assignment_submission4 %>%
    group_by(course_id, user_id) %>%
    summarize(
      n_assignment = n(),
      n_late_assignment = sum(late)
    ) %>% ungroup()
  
  # summarize assignment indicators per week
  assignment_submission4_sumweek <- assignment_submission4 %>%
    group_by(course_id, user_id, weeklabel) %>%
    summarize(
      n_assignment = n(),
      n_late_assignment = sum(late)
    ) %>% ungroup()
  
  # convert to datawide
  assignment_weekwide <- assignment_submission4_sumweek %>%
    full_join(assignment_sumweek) %>%
    pivot_wider(id_col = c(course_id, user_id), names_from = weeklabel,
                values_from = c(n_assignment, n_late_assignment, 
                                n_assignmentclicks)) %>% 
    left_join(assignment_submission4_sum, by = c("course_id", "user_id")) %>%
    left_join(assignment_sum, by = c("course_id", "user_id")) %>%
    mutate_at(vars(starts_with("n_")), ~replace_na(., 0))
  
  write.csv(assignment_weekwide, paste0(path, outfile),
            row.names = FALSE)
  
  
  assignment_weekwide
}

getquiz_info <- function(requests_df, quartile, year, outfile){

  requests4 <- requests_df

# load quizzes 
quiz_dim <- read_parquet(paste0(path, "quiz_dim.snappy.parquet"), 
                         as_tibble = TRUE, 
                         col_types = cols(.default = "c")) %>%
    na_if("\\N")
quiz_fact <- read_parquet(paste0(path, "quiz_fact.snappy.parquet"), 
                         as_tibble = TRUE,
                         col_types = cols(.default = "c"))  %>%
    na_if("\\N")
quiz_submission_fact <- read_parquet(paste0(path, 
                         "quiz_submission_fact.snappy.parquet"), 
                          as_tibble = TRUE,
                         col_types = cols(.default = "c")) %>%
    na_if("\\N") %>%
  mutate_at(c("total_attempts", "score", "quiz_points_possible"), as.numeric) 
quiz_submission_dim <- read_parquet(paste0(path, 
                                          "quiz_submission_dim.snappy.parquet"), 
                                     as_tibble = TRUE,
                                    col_types = cols(.default = "c")) %>%
    na_if("\\N")


####### transform & merge tables ###############################################
# merge quiz_fact and quiz_dim
quiz_fact_dim <- quiz_dim %>%
  # remove unpublished quizzes
  filter(workflow_state != "unpublished") %>%
  left_join(quiz_fact,by = c("id" = "quiz_id", "course_id","assignment_id",
                             "points_possible")) %>%
  select(-updated_at, -created_at, -due_at, -workflow_state)


# filter only interaction with quizzes from requests
requests_quiz <- requests4 %>%
  filter(!is.na(quiz_id)) %>%
  mutate(quiz_id = as.numeric(quiz_id),
         # get canvas_id from URL in requests
         quiz_canvas_id = gsub(".*/quizzes/","", url),
         quiz_canvas_id = as.numeric(gsub("/.*", "", quiz_canvas_id)))

# merge quiz submission dim and fact
quiz_subm_fact_dim <- quiz_submission_fact %>%
  filter(!is.na(date)) %>%
  select(-enrollment_term_id, -course_account_id, -assignment_id,
         -enrollment_rollup_id, -fudge_points)  %>%
  right_join(quiz_submission_dim, by = c("quiz_id", 
                                         "quiz_submission_id" = "id",
                                         "user_id","submission_id"))

# merge submissions with quiz fact dim table
quiz_submission <- quiz_subm_fact_dim %>%
  select(-canvas_id) %>%
  left_join(quiz_fact_dim, by = c("quiz_id"= "id","course_id"))

# extract courses and add weeklabels
quiz_submission2 <- quiz_submission %>%
  mutate(date_submission = as.POSIXlt(date),
         quartile = quartile,
         weekno = strftime(date_submission,"%V"),
         weeklabel = calculate_weeklabel(weekno, quartile, year))
         

# remove clicks outside timeframe & filter courses
quiz_submission3 <- quiz_submission2 %>%
  filter(!is.na(weeklabel), course_id %in% requests4$course_id) 

####### Summarize quiz info ####################################################
### QUIZ CLICKS ONLY (via requests)
# sum over full course
quiz_sum <- requests_quiz %>%
  group_by(course_id, user_id) %>%
  summarize(
    n_quizclicks = n()
  ) %>%
  ungroup()

# sum per week
quiz_sumweek <- requests_quiz %>%
  group_by(course_id, user_id, weeklabel) %>%
  summarize(
    n_quizclicks = n()
  ) %>%
  ungroup()

### ALL other quiz indicators via quiz_submissions

### QUIZ SUBMISSION INFO
# get max score per quiz 
## Note: points_possible is often 0, while score > 0
## max score can be higher than max points possible (and vice versa)
## so we take the highest score of both
quiz_submission_sum <- quiz_submission3 %>%
  group_by(course_id, quiz_id) %>%
  summarize(
    max_quiz_score = max(quiz_points_possible, score)
  ) %>%
  ungroup()

# create one submission entry per (unique) quiz per user per course
quiz_submission4 <- quiz_submission3 %>%
  left_join(quiz_submission_sum) %>%
  group_by(course_id, quiz_id, user_id) %>%
  arrange(as.POSIXct(date)) %>%
  summarize(
    firstattempt_date = first(date),
    weeklabel = first(weeklabel), # weeklabel of first attempt
    lastattempt_date = max(date),
    late = ifelse(!is.na(due_at), firstattempt_date > first(due_at), 0),
    quiz_type = first(quiz_type),
    total_duration = sum(as.numeric(time_taken)),
    first_duration = sum(as.numeric(time_taken)),
    m_duration = mean(as.numeric(time_taken)/60),
    nof_questions = first(question_count),
    time_limit = first(as.numeric(time_limit)),
    perc_time_taken = ifelse(time_limit > 0, m_duration/time_limit, NA),
    allowed_attempts = first(allowed_attempts),
    total_attempts = first(total_attempts),
    max_score_perc = max(score)/first(max_quiz_score),
    mean_score_perc = mean(score)/first(max_quiz_score)
  ) %>%
  ungroup()

# summarize quiz indicators for full course
quiz_submission4_sum <- quiz_submission4 %>%
  group_by(course_id, user_id) %>%
  summarize(
    n_practicequiz = sum(quiz_type == "practice_quiz" | time_limit == -1),
    n_examquiz = sum(quiz_type %in% c("assignment", "graded_survey") 
                     & time_limit != -1),
    n_quiz_retry = sum(total_attempts > 1 & allowed_attempts > 1),
    m_examquiz_duration_perc = ifelse(n_examquiz > 0, mean(perc_time_taken, 
                                                           na.rm = T), NA),
    m_practicequiz_duration = mean(m_duration, na.rm = T),
    max_quizscore_perc = mean(max_score_perc, na.rm = T)
  ) %>% ungroup()

# summarize quiz indicators per week
quiz_submission4_sumweek <- quiz_submission4 %>%
  group_by(course_id, user_id, weeklabel) %>%
  summarize(
    n_practicequiz = sum(quiz_type == "practice_quiz" | time_limit == -1),
    n_examquiz = sum(quiz_type %in% c("assignment", "graded_survey") 
                     & time_limit != -1),
    n_quiz_retry = sum(total_attempts > 1 & allowed_attempts > 1),
    m_examquiz_duration_perc = ifelse(n_examquiz > 0, mean(perc_time_taken, 
                                                           na.rm = T),  NA),
    m_practicequiz_duration = mean(m_duration, na.rm = T),
    max_quizscore_perc = mean(max_score_perc, na.rm = T)
  )%>% ungroup()

# convert to datawide
quiz_weekwide <- quiz_submission4_sumweek %>%
  full_join(quiz_sumweek) %>%
  pivot_wider(id_col = c(course_id, user_id), names_from = weeklabel,
              values_from = c(n_practicequiz, n_examquiz, n_quiz_retry,
                              m_examquiz_duration_perc, m_practicequiz_duration,
                              max_quizscore_perc, 
                              n_quizclicks)) %>% 
  left_join(quiz_submission4_sum, by = c("course_id", "user_id")) %>%
  left_join(quiz_sum, by = c("course_id", "user_id")) %>%
  mutate_at(vars(starts_with("n_")), ~replace_na(., 0))

write.csv(quiz_weekwide, paste0(path, outfile),
          row.names = FALSE)

quiz_weekwide
}

getforum_info <- function(requests_df, quartile, year, outfile){

  requests4 <- requests_df
  
  # load discussion tables 
  discussion_entry_fact <- read_parquet(paste0(path, 
                                      "discussion_entry_fact.snappy.parquet"), 
                           as_tibble = TRUE, 
                           col_types = cols(.default = "c")) %>%
    na_if("\\N") %>%
    filter(!is.na(course_id)) 
  discussion_topic_fact <- read_parquet(paste0(path, 
                                        "discussion_topic_fact.snappy.parquet"), 
                           as_tibble = TRUE, 
                           col_types = cols(.default = "c")) %>%
    na_if("\\N") %>%
    # remove discussion in groups
    filter(!is.na(course_id)) 
  discussion_topic_dim <- read_parquet(paste0(path, 
                                        "discussion_topic_dim.snappy.parquet"), 
                           as_tibble = TRUE, 
                           col_types = cols(.default = "c")) %>%
    na_if("\\N") %>%
    filter(!is.na(course_id)) 
  
  
  ####### transform & merge tables #############################################
  
  # merge discussion_topic dim &  fact  
  disc_topics <- discussion_topic_dim %>%
    left_join(discussion_topic_fact, by = c("id" = "discussion_topic_id",
                                            "course_id", "group_id")) 
  
  # filter courses and add weeklabels
  disc_topics2 <- disc_topics %>%
    filter(!is.na(posted_at)) %>%
    mutate(date_submission = as.POSIXlt(posted_at),
           quartile = quartile,
           weekno = strftime(date_submission,"%V"),
           weeklabel = calculate_weeklabel(weekno, quartile, year))
  
  # remove clicks outside timeframe & filter courses
  disc_topics3 <- disc_topics2 %>%
    filter(!is.na(weeklabel), course_id %in% requests4$course_id)
    
    
  #merge discussion entry fact
  disc_entries <- disc_topics %>%
    select(-user_id) %>%
    right_join(discussion_entry_fact, by = c("id" = "topic_id", "course_id")) 
  
  
  # filter courses and add weeklabels
  disc_entries2 <- disc_entries %>%
    filter(!is.na(posted_at)) %>%
    mutate(date_submission = as.POSIXlt(posted_at),
           quartile = quartile,
           weekno = strftime(date_submission,"%V"),
           weeklabel = calculate_weeklabel(weekno, quartile, year))
  
  # remove clicks outside timeframe & filter courses
  disc_entries3 <- disc_entries2 %>%
    filter(!is.na(weeklabel), course_id %in% requests4$course_id)
  

  # merge with discussion topic dim to get discussion type
  requests_discussions <- requests4 %>%
    filter(!is.na(discussion_id)) %>% 
    left_join(discussion_topic_dim, by = c("discussion_id" = "id", "course_id"))
  
  ####### Summarize discussion info ############################################
  ### Discussion CLICKS ONLY (via requests)
  # sum over full course
  disc_sum <- requests_discussions %>%
    group_by(course_id, user_id) %>%
    summarize(
      n_forumclicks = sum(is.na(type)),
      n_announcementclicks = sum(type == "Announcement")
    ) %>%
    ungroup()
  
  # sum per week
  disc_sumweek <- requests_discussions %>%
    group_by(course_id, user_id, weeklabel) %>%
    summarize(
      n_forumclicks = sum(is.na(type)),
      n_announcementclicks = sum(type == "Announcement")
    ) %>%
    ungroup()
  
  ### ALL other discussion indicators via discussion_submissions
  
  # post in topics
  topic_sum <- disc_topics3 %>%
    group_by(course_id, user_id) %>%
    summarize(
      n_forumtopic_post = n_distinct(id)
    ) %>% ungroup()
  
  # summarize assignment indicators per week
  topic_sumweek <- disc_topics3 %>%
    group_by(course_id, user_id, weeklabel) %>%
    summarize(
      n_forumtopic_post = n_distinct(id)
    ) %>% ungroup()
  
  # post in topics
  entry_sum <- disc_entries3 %>%
    group_by(course_id, user_id) %>%
    summarize(
      n_forumreply_post = n_distinct(id)
    ) %>% ungroup()
  
  # summarize assignment indicators per week
  entry_sumweek <- disc_entries3 %>%
    group_by(course_id, user_id, weeklabel) %>%
    summarize(
      n_forumreply_post = n_distinct(id)
    ) %>% ungroup()
  
  
  # convert to datawide
  disc_weekwide <- topic_sumweek %>%
    full_join(entry_sumweek) %>%
    full_join(disc_sumweek) %>%
    pivot_wider(id_col = c(course_id, user_id), names_from = weeklabel,
                values_from = c(n_forumreply_post, n_forumtopic_post, 
                                n_forumclicks, n_announcementclicks)) %>% 
    left_join(topic_sum, by = c("course_id", "user_id")) %>%
    left_join(entry_sum, by = c("course_id", "user_id")) %>%
    left_join(disc_sum, by = c("course_id", "user_id")) %>%
    mutate_at(vars(starts_with("n_")), ~replace_na(., 0)) %>%
    filter(!is.na(user_id))
  
  write.csv(disc_weekwide, paste0(path, outfile),
            row.names = FALSE)
  
  disc_weekwide
  
  
}

getfiles_info <- function(requests_df, quartile, year, outfile){

  requests4 <- requests_df
  
  
  # load files 
  file_dim <- read_parquet(paste0(path, "file_dim.snappy.parquet"), 
                             as_tibble = TRUE, 
                           col_types = cols(.default = "c")) %>%
    na_if("\\N")
  
####### transform & merge tables ###############################################

file_dim2 <- file_dim %>%
    mutate(course_id = as.numeric(gsub("^7542", "", course_id))) %>%
    filter(course_id %in% requests4$course_id) %>%
    mutate(file_type = gsub("\\/.*", "", content_type),
           file_type = ifelse(file_type == "binary"| is.na(file_type), 
                              "unknown",
                              file_type),
           file_id = as.numeric(gsub("^7542", "", id)))


requests_files <- requests4 %>%
  filter(grepl("files/", url)) %>%
  mutate(file_id = gsub(".*/files/", "", url),
         file_id = as.numeric(gsub("/.*|\\?.*", "", file_id))) %>%
  filter(!is.na(file_id))

#merge with file_dim for file_types
requests_files2 <- requests_files %>%
  left_join(select(file_dim2, file_id, file_type, display_name)) 

# roughly file types include: video (.mp4), text (.docx, csv), 
# application (pdf, pptx, zip), image (jpg, png), unknown (.sav, .R, .do, .dta)

# extract video views in canvas conferences
requests_conferences <- requests4 %>%
  filter(grepl("conferences/|panopto", url)) %>%
  mutate(conference_id = gsub(".*/conferences/", "", url),
         conference_id = as.numeric(gsub("/.*|\\?.*", "", conference_id)))

####### Summarize file info ####################################################

# get unique access
# create one entry per (unique) file per user per course
files2 <- requests_files2 %>%
  group_by(course_id, file_id, user_id) %>%
  arrange(as.POSIXct(timestamp)) %>%
  summarize(
    n_fileclicks = n(),
    weeklabel = first(weeklabel), # weeklabel of first access
    downloaded = sum(grepl("download", url)) > 0 ,
    video = first(file_type) == "video"
  ) %>%
  ungroup()

# summarize files indicators for full course
files_sum <- files2 %>%
  group_by(course_id, user_id) %>%
  summarize(
    n_fileclicks = sum(n_fileclicks, na.rm = T),
    n_fileaccess = n(),
    n_video = sum(video),
    n_videoclicks = sum(n_fileclicks[video], na.rm = T),
    n_filedownload = sum(downloaded)
  ) %>% ungroup()

# summarize assignment indicators per week
files_sumweek <- files2 %>%
  group_by(course_id, user_id, weeklabel) %>%
  summarize(
    n_fileclicks = sum(n_fileclicks, na.rm = T),
    n_fileaccess = n(),
    n_videofile = sum(video),
    n_videofileclicks = sum(n_fileclicks[video], na.rm = T),
    n_filedownload = sum(downloaded)
  ) %>% ungroup()

# get unique access
# create one entry per (unique) video per user per course
conferences2 <- requests_conferences %>%
  group_by(course_id, url, user_id) %>%
  arrange(as.POSIXct(timestamp)) %>%
  summarize(
    n_confclicks = n(),
    weeklabel = first(weeklabel), # weeklabel of first access
  ) %>%
  ungroup()


# summarize video indicators for full course
video_sum <- conferences2 %>%
  group_by(course_id, user_id) %>%
  summarize(
    n_videoclicks_total = sum(n_confclicks, na.rm = T),
    n_video_total = n()
  ) %>% ungroup()

# summarize video indicators per week
video_sumweek <- conferences2 %>%
  group_by(course_id, user_id, weeklabel) %>%
  summarize(
    n_videoclicks = sum(n_confclicks, na.rm = T),
    n_video = n()
  ) %>% ungroup()


# convert to datawide
files_weekwide <- files_sumweek %>%
  full_join(video_sumweek) %>%
  pivot_wider(id_col = c(course_id, user_id), names_from = weeklabel,
              values_from = c(n_fileclicks, n_fileaccess, 
                              n_filedownload, n_videofileclicks, n_videofile,
                              n_videoclicks, n_video)) %>% 
  full_join(files_sum) %>%
  full_join(video_sum) %>%
  mutate_at(vars(starts_with("n_")), ~replace_na(., 0))

write.csv(files_weekwide, paste0(path, outfile),
          row.names = FALSE)

files_weekwide

}

session_weekwide <- getsession_info(requests4, quartile, year, 
                                    "session_indicators.csv")
assignment_weekwide <- getassignment_info(requests4, quartile, year, 
                                          "assignment_indicators.csv")
quiz_weekwide <- getquiz_info(requests4, quartile, year, 
                              "quiz_indicators.csv")
disc_weekwide <- getforum_info(requests4, quartile, year, 
                               "discussion_indicators.csv")
files_weekwide <- getfiles_info(requests4, quartile, year, 
                                "file_indicators.csv")

# merge all canvas indicators
canvasall <- session_weekwide %>%
  left_join(assignment_weekwide,  by = c("course_id", "user_id")) %>%
  left_join(quiz_weekwide,  by = c("course_id", "user_id")) %>%
  left_join(disc_weekwide,  by = c("course_id", "user_id")) %>%
  left_join(files_weekwide,  by = c("course_id", "user_id")) 

write.csv(canvasall, paste0(path, "CanvasIndicators.csv"))