library(dplyr); library(tidyr); library(ggplot2)

# Read in data
eu_chats <- read.csv("20180601_20180607_EU.csv", sep = ",", stringsAsFactors = FALSE)

# Rename columns
colnames(eu_chats) <- c("date", "type", "retailer_code", "count")

# Remove time from date column
eu_chats$date <- gsub(", 00:00:00.000", "", eu_chats$date)
eu_chats$date <- gsub("st", "", eu_chats$date)
eu_chats$date <- gsub("nd", "", eu_chats$date)
eu_chats$date <- gsub("rd", "", eu_chats$date)
eu_chats$date <- gsub("th", "", eu_chats$date)

eu_chats$date <- as.Date(eu_chats$date, format='%B %d %Y')

# Label missed anc completed chats accordingly
eu_chats$type[eu_chats$type == "conversation-auto-archived"] <- "Missed"
eu_chats$type[eu_chats$type == "conversation-archived"] <- "Completed"

# Add new columns (intialise to 0 or "retailer")
eu_chats$retailer <- ""

# Identify France, Germany & UK stores
eu_chats$retailer[eu_chats$retailer_code == "npqPjZyMy5"] <- "Retailer1"
eu_chats$retailer[eu_chats$retailer_code == "HbNaIqdedB"] <- "Retailer2"
eu_chats$retailer[eu_chats$retailer_code == "1mRdYODJBH"] <- "Retailer3"
eu_chats$retailer[eu_chats$retailer_code == "GGdwO3HFDV"] <- "Retailer4"
eu_chats$retailer[eu_chats$retailer_code == "Tj8vwJvyH1"] <- "Retailer5"
eu_chats$retailer_code <- NULL

# Visualise chats

eu_chats %>%
  spread(type, count, fill = 0) %>%   # Spread the count column in missed and completed
  mutate(Total = Completed + Missed) %>%   # Create the Total column
  ggplot(aes(as.Date(date, tz = "Europe/London"), Total)) +
  geom_col(aes(fill = "Total"),
           colour = "black", width = 0.75) + # total bar (with stat = "identity")
  geom_col(aes(y = Missed, fill = "Missed"),
           colour = "black", width = 0.75) + # missed bar
  geom_text(aes(label = paste("Total chats:", Total)), # add total label
            hjust = -0.05, vjust = 0.7, size = 3.5) +
  geom_text(aes(label = paste("Missed chats:", Missed, "(", round(Missed/Total*100, 2), "%)")), # add missed label and calculate percentage
            hjust = -0.05, vjust = -0.7, size = 3.5, colour = "red") +
  scale_fill_manual(name = "",  # Manual fill scale
                    values = c("Total" = "forestgreen", "Missed" = "red")) +
  facet_grid(retailer~.) +  # Displayed per retailer
  scale_y_continuous(limits = c(0, max(eu_chats$count) * 2)) + # Make labels visible
  scale_x_date(date_breaks = "1 day", name = "Date") +
  ggtitle(paste("Missed Chats (", min(eu_chats$date), "-", max(eu_chats$date), ")")) +
  coord_flip()