# For testing - use in-memory DB
con <- dbConnect(RSQLite::SQLite(), dbname = ":memory:") 

# Uncomment to manipulate real DB
#con <- dbConnect(RSQLite::SQLite(), dbname = "project3_job_skills.db") 

baseUrl <- "https://raw.githubusercontent.com/nolivercuny/data607-team-6-project-3/master/sql/"
tables <- c("company", "job_listing", "job_listing_skill","job_listing_word")
for (table in tables) {
  fileUrl <- paste(baseUrl, table,".sql",sep = "")
  createTableStatement <- getURL(fileUrl)
  print(paste("Creating ", table, " table"))
  dbSendQuery(con, createTableStatement)
}

urlfile<-"https://raw.githubusercontent.com/nolivercuny/data607-team-6-project-3/master/data/job_listings_final.csv" 
jobdat <- read_csv(url(urlfile))

companiesDf <- jobdat %>% 
  select(company_name, company_size, industry) %>% 
  distinct() 

companiesDf$company_name <- companiesDf$company_name %>% replace_na("unknown")
dbWriteTable(con,"company",companiesDf, append=TRUE)

# Remove columns that are in the company table
jobListingDf <- jobdat %>% select(-c("company_size", "industry"))

# Fix the one company name that is NA
jobListingDf$company_name <- jobListingDf$company_name %>% replace_na("unknown")

# Read companies to get company ID for joining
companiesWithId <- dbReadTable(con,"company")

# Join job listing with company to populate id, then drop company-specific cols
# rename id column to company id
joined <- left_join(jobListingDf, companiesWithId, by="company_name") %>% 
  select(-c("company_size", "industry","company_name")) %>%
  rename(company_id = id)

#write dataframe to job_listing table
dbWriteTable(con, "job_listing", joined, append=TRUE)

wordsDataUrl <- "https://raw.githubusercontent.com/nolivercuny/data607-team-6-project-3/master/data/jobdata_words.csv"
wordsJobListingData <- read_csv(url(wordsDataUrl))
wordsJobListingData<-data_frame(wordsJobListingData)

wordsDf <- wordsJobListingData %>% select(search_rank, word)
jobListingWithId <- dbReadTable(con,"job_listing")
joinedWords<-left_join(wordsDf, jobListingWithId, by="search_rank") %>%
  select(id, word) %>%
  rename(job_listing_id = id, skill = word)
dbWriteTable(con,"job_listing_word", joinedWords, append=TRUE)

skillsDataUrl <- "https://raw.githubusercontent.com/nolivercuny/data607-team-6-project-3/master/JobSkills.csv"
skillsData <- read_csv(url(skillsDataUrl))
skillsDf <-data_frame(skillsData)

jobListingWithId <- dbReadTable(con,"job_listing")
skillsTableDf <- data.frame(job_listing_id=integer(), skill=character())
for (i in 1:nrow(jobListingWithId)) {
  listing <- jobListingWithId[i,]
  for (j in 1:nrow(skillsDf)) {
    skill <- skillsDf[j,]
    detected <- str_detect(listing$description, 
                           regex(paste('[^A-Z0-9]',skill,'[^A-Z0-9]',sep = ''),
                                 ignore_case = TRUE))
    if(detected==TRUE){
      skillsTableDf <- skillsTableDf %>% 
        add_row(job_listing_id = listing$id, skill = skill$JobSkill)
    }
  }
}

dbWriteTable(con,"job_listing_skill", skillsTableDf, append=TRUE)
dbDisconnect(con)

# replace with relative or remote path to db
dbLocation <-"/Users/jeff/Development/data607-team-6-project-3/data/project3_job_skills.db"
con <- dbConnect(RSQLite::SQLite(), dbname = dbLocation)

# get tables
tables <- dbListTables(con)
company_df <- dbReadTable(con, 'company')
listing_df <- dbReadTable(con, 'job_listing')
skills_df <- dbReadTable(con, 'job_listing_skill')
words_df <- dbReadTable(con, 'job_listing_word')

# construct fact tables
job_listings <- listing_df %>%
  inner_join(company_df, by = c('company_id' = 'id'))

job_listings_skills <- job_listings %>%
  inner_join(skills_df, by = c('id' = 'job_listing_id'))

words_df %>%
  count(skill) %>%
  with(wordcloud(skill,n,scale=c(4,0.5),max.words=50,
                 random.color = FALSE,rot.per=0.25,colors = colours()))