# install libraries
# only need to run once ever
# comment it out before knitting
#install.packages("dplyr")
#library(dplyr)
#install.packages("sqldf")
#library(sqldf)
#...whatever other packages you are missing

# don't show warnings while knitting
knitr::opts_chunk$set(warning = FALSE, message = FALSE)

# constants
my_working_dir_mac <- "/Users/cassiemarte/Desktop/GBUS461/Sephora"  # working directory if on a Mac

myFile_products <- "products.csv"
myFile_reviews <- "reviews.csv"
myFile_authors <- "authors.csv"

# import data
setwd(my_working_dir_mac)
products <- read.csv(myFile_products)
reviews <- read.csv(myFile_reviews)
authors <- read.csv(myFile_authors)

# look at first 6 rows of the products table
head(products)

# look at the first 6 rows of the reviews table
head(reviews)

# look at the first 6 rows of the authors table
head(authors)

# pull in product info by inner join
# call the library, sqldf, 
# then the function, which also happens to be called sqldf
reviews <- sqldf::sqldf(" 
SELECT r.*, p.brand_name, p.product_name
FROM reviews r INNER JOIN products p ON r.product_id = p.product_id
                      ")

# convert the column to type "date"
reviews$submission_date <- as.Date(reviews$submission_date, format = "%m/%d/%y")

# get distinct values of brand name and product name
# in the products table
# call the library, dplyr, then the function distinct
dplyr::distinct(reviews, brand_name, product_name)

# calculate means based on reviews in total, then specific brand name..
mean(reviews$rating)

## [1] 4.038411

mean(reviews[reviews$brand_name=='L\'Occitane',]$rating) # checking for equivalents to

## [1] 3.880503

# Need the slash because of the ' in L'Occitane
# Your turn: what is the mean for La Mer?
mean(reviews[reviews$brand_name=='La Mer',]$rating)

## [1] 4.056645

mean(reviews[reviews$brand_name=='La Mer',]$rating)

## [1] 4.056645

# plot histograms. Lay out the 3 graphs
layout(matrix(c(1,1,2,3), 2, 2, byrow = TRUE))

hTotal <- hist(reviews$rating, breaks = seq(0, 5, by = 1),
           main = 'Distribution of Ratings', xlab = 'Review Ratings (in Stars)')

h1 <- hist(reviews[reviews$brand_name == 'L\'Occitane',]$rating, breaks = seq(0, 5, by = 1),
           main = 'Distribution of L\'Occitane Ratings', xlab = 'Review Ratings (in Stars)')

h2 <- hist(reviews[reviews$brand_name == 'La Mer',]$rating, breaks = seq(0, 5, by = 1),
           main = 'Distribution of La Mer Ratings', xlab = 'Review Ratings (in Stars)')