# This code creates competition intensity rates on conference call level

library(RPostgreSQL)
library(dplyr, warn.conflicts = FALSE)

pg <- dbConnect(PostgreSQL())
dbExecute(pg, "SET work_mem TO '10GB'") # Speeds-up creation of temporary tables

# Define PG data ----

# Project schema
rs <- dbGetQuery(pg, "SET search_path TO nonans_product, public")
se_link <- tbl(pg, "se_link")

# Outside of project schema
qa_pairs <- tbl(pg, sql("SELECT * FROM streetevents_old.qa_pairs"))
speaker_data <- tbl(pg, sql("SELECT * FROM streetevents_old.speaker_data"))
comp_intensity <- tbl(pg, sql("SELECT * FROM streetevents_old.comp_intensity"))

# Pull data for calls in se_link
se_link_alt <-
    se_link %>%
    select(file_name, last_update) %>%
    compute()

# Keep speaker data in se_link 
speaker_data_alt <- 
    speaker_data %>%
    inner_join(se_link_alt) %>%
    select(file_name, last_update, speaker_number, context) %>%
    distinct() %>%
    compute(index = c("file_name", "last_update", "speaker_number", "context"))

# Keep qa data in se_link and get individual answer nums
qa_pairs_alt <- 
    qa_pairs %>%
    inner_join(se_link_alt) %>%
    # Unnest answer_nums
    mutate(speaker_number = sql("unnest(answer_nums)"), 
           context = sql("'qa'::text"), 
           answer = TRUE) %>%
    select(file_name, last_update, speaker_number, context, answer) %>%
    distinct() %>%
    compute(index = list(c("file_name", "last_update", "speaker_number", "context")))

# Get unique utterances = {pres, qa answers(!)} for company representatives
utter <- 
    speaker_data_alt %>%
    inner_join(qa_pairs_alt) %>%
    # Keep only company representatives talking
    filter(context == "pres" | (context == "qa" & answer)) %>%
    distinct() %>% 
    compute(index = c("file_name", "last_update", "speaker_number"))

# Get competition intensity by {speaker_number, context}
comp_data <-
    comp_intensity %>%
    inner_join(se_link_alt) %>%
    filter(!exclude) %>%
    # competition intensity have multiple obs. per {file_name, last_update}
    distinct() %>%
    mutate(has_comp = !is.na(competition_word)) %>%
    group_by(file_name, last_update, speaker_number, context) %>%
    summarize(is_comp = bool_or(has_comp)) %>%
    ungroup() %>%
    compute()

dbGetQuery(pg, "DROP TABLE IF EXISTS comp_rate")

comp_rate <-
    utter %>%
    # Competition data is only(!) when the utterance contains reference to competition
    # WARNING: left_join!
    left_join(comp_data) %>%
    distinct() %>%
    group_by(file_name, last_update) %>%
    summarize(all_utter = n(),
              comp = coalesce(sum(as.integer(is_comp)), 0)) %>%
    mutate(comp_rate = comp/NULLIF(all_utter, 0)) %>%
    ungroup() %>%
    compute(name = "comp_rate", temporary = FALSE)

db_comment <- paste0("CREATED USING azakolyukina/nonans_product/create_comp_rate.R ON ", Sys.time())
dbGetQuery(pg, paste0("COMMENT ON TABLE comp_rate IS '",
                      db_comment, "';"))

dbGetQuery(pg, "ALTER TABLE comp_rate OWNER TO nonans_product")
dbGetQuery(pg, "GRANT SELECT ON comp_rate TO nonans_product_access")
dbGetQuery(pg, "VACUUM ANALYZE comp_rate")

dbDisconnect(pg)
            