# This code creates estimation data for question-level analysis

#(.packages())
#rm(list = ls())

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")
nonans_ner <- tbl(pg, "nonans_ner")
calls_set <- tbl(pg, "calls_set")

# Restrict to all_ans >= all_ans_min
# 5 is 25th percentile for CEOs
all_ans_min <- 5

# Pre-process call_set ----

calls_set_alt <- 
    calls_set %>%
    # Drop variables in nonans_ner_alt
    select(-matches("answer_|question_|refuse|unable|aftercall|nonans|all_"), 
           -matches("^impgr")) %>%
    compute(index = list(c("file_name", "last_update")))

# Compute nonanswer data -----
# WARNING: fyear is fiscal year of a call (contemporanous fyear call charactericstics)

library(psych)

# Merge non-answer data to {company_id, fy_end}
nonans_ner_alt <- 
    nonans_ner %>%
    inner_join(se_link) %>%
    # Require min participation
    filter(all_ans >= all_ans_min) %>%
    select(file_name, last_update, answer_nums, question_nums, all_ans, 
           matches("is_|has_"), 
           positive, negative, fog, uncertainty, words_ans) %>%
    collect() %>%
    # Winsorize pooled sample 
    mutate_at(.vars = vars(positive, negative, fog, uncertainty, words_ans),
              .funs = funs(winsor(., 0.01))) %>%
    copy_to(pg, ., name = "nonans_ner_alt", temporary = TRUE)

# Merge firms, executives, and calls ----

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

ner_set <-
    calls_set_alt %>%
    inner_join(nonans_ner_alt) %>%
    # Compute forward-looking statements about performance categories
    mutate(has_brvb_mpr = has_forward_brvb & has_earn_mpr, 
           has_money_percent = (has_money | has_percent), 
           has_date_time = (has_date | has_time)) %>%
    # WARNING: check colSums(is.na(DATA)) before filtering
    filter_at(.vars = vars(everything()), 
              all_vars(!is.na(.))) %>%
    compute(name = "ner_set", temporary = FALSE, 
            index = c("file_name", "last_update"))

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

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

dbDisconnect(pg)
