# This code puts together spontaneity measures for estimation

library(DBI)
library(dplyr, warn.conflicts=FALSE)
library(tidyr)

# Connect to server and read tables ----

pg <- dbConnect(RPostgres::Postgres())

# Define PG data ----

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

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

# Compute length-adjusted script measure ----

# Get word counts for presentation and answers

answer_wc <- 
    qa_pairs %>% 
    select(-question_nums) %>%
    mutate(speaker_number = unnest(answer_nums)) %>%
    mutate(context = "qa", section = 1) %>%
    select(-answer_nums) %>%
    inner_join(word_counts) %>%
    select(-count, -sent_count, -sum_6, -sum_num) %>%
    group_by(file_name, last_update, section, context) %>%
    summarise(wc_answer = sum(sum)) %>%
    ungroup() %>%
    compute()

pres_wc <- 
    word_counts %>% 
    select(file_name, last_update, section, context, sum) %>%
    inner_join(speaker_data) %>%
    filter(section == 1, context == "pres") %>%
    filter(speaker_name %NOT ILIKE% "%Operator%" & tolower(role) != "operator") %>%
    group_by(file_name, last_update, section, context) %>%
    summarise(wc_pres = sum(sum)) %>%
    compute()

# Join script measure with word counts ----

script_wc <- 
    answer_wc %>% 
    select(-context) %>%
    inner_join(pres_wc) %>%
    select(-context) %>%
    inner_join(script) %>%
    mutate_at(vars(starts_with("wc")), funs(as.numeric)) %>%
    collect() 

# Replicate the computation of `script` variable in Lee 2016 ----

# Need wc, diff in wc and their squared and cubed terms for reg
script_wc_full <- 
    script_wc %>%
    mutate(wc_pres_2 = wc_pres^2, wc_answer_2 = wc_answer^2, 
           wc_pres_3 = wc_pres^3, wc_answer_3 = wc_answer^3,
           wc_pres_less_qa = wc_pres - wc_answer,
           wc_pres_less_qa_2 = (wc_pres-wc_answer)^2,
           wc_pres_less_qa_3 = (wc_pres-wc_answer)^3)

# Run reg as specified in Appendix A
reg1 <- lm(script ~ wc_pres + wc_answer + wc_pres_less_qa + wc_pres_2 + wc_answer_2 + wc_pres_less_qa_2 + wc_pres_3 + wc_answer_3 + wc_pres_less_qa_3, data = as.data.frame(script_wc_full))
summary(reg1)

script_wc_full_res <- 
    script_wc_full %>%
    mutate(res = script - fitted(reg1)) %>%
    select(file_name, last_update, section, rawscript = script, script = res) %>%
    # Compute rscript: rank the SCRIPTi,t measure into deciles from 0 to 9 and divide by 9
    mutate(rscript = (ntile(script, n = 10) - 1)/9) %>%
    compute()

# Write to PG ----

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

script_wc_full_res %>%
    copy_to(pg, ., name = "rscript", temporary = FALSE, 
            indexes = list(c("file_name", "last_update"), 
                           c("file_name", "last_update", "section")))

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

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

dbDisconnect(pg)


