# This code creates estimation data for calls

#(.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")
calls_merged <- tbl(pg, sql("SELECT * FROM calls_merged"))

# Compute variables, winsorize, and require non-missing----

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

library(psych)

calls_set <- 
    calls_merged %>%
    # Compute compensation variables
    mutate_at(.vars = vars(bonus, stock_awards, tdc1_cash),
              .funs = funs(coalesce(., 0))) %>%
    # Stock-based compensation ratio as in Hollander et al. JAR (2010)
    mutate(eqcomp = (value_opt_new + stock_awards)/NULLIF(value_opt_new + stock_awards + tdc1_cash, 0),
           salary_bonus = salary + bonus) %>%
    mutate_at(.vars = vars(matches("^ciq")), 
              .funs = funs(greatest(0, .))) %>%
    mutate_at(.vars = vars(matches("^is_ciq")), 
              .funs = funs(coalesce(as.integer(.), 0))) %>%
    # Log variables
    mutate_at(.vars = vars(salary_bonus, value_shares_held, tenure, 
                           atq, saleq, mcapq,
                           tnic3tsimm),
              .funs = funs(log = log(. + 1))) %>%
    # Keep data for estimation and descriptives
    select(company_id, management_id, gvkey, permno, fyear, fy_end, fqtr, fq_end,
           file_name, last_update, start_date, # Call identifiers
           gsector, ggroup, sic3q, sic4q, is_sp1500, # Industry, S&P 1500 membership
           matches("all_ans|nonans|refuse|unable|aftercall"), # Nonanswer variables
           words_ans_avg, # Avg. length of a response
           mcapq, mcapq_log, atq, atq_log, saleq, saleq_log, # Size
           sdret_3, ret_3, # CRSP characteristics
           lossq, ibq_to_atq, ibq_to_atq_lead, type_ib, # Profitability
           tnic3tsimm, tnic3tsimm_log, hhi3, # Competition
           comp, comp_sic3_i, # Competition
           mbq, # Growth
           is_ceo, tenure, tenure_log, # CEO characteristics
           eqcomp, value_shares_held, value_shares_held_log, # Hollander et al. compensation
           salary, bonus, salary_bonus_log, # CEO cash compensation
           value_vested,  value, # New opt grants
           delta_vested, delta,
           vega_vested, vega,
           matches("^impgr"), # China import
           levq, # Debt
           rawscript, script, rscript # Spontaneity measures
           ) %>%
    # Pull data to R to winsorize
    collect() %>%
    # Require non-missing variables
    # WARNING: check colSums(is.na(DATA)) before filtering
    # Exclude China import variables 
    filter_at(.vars = vars(-matches("^impgr"), -matches("script")), 
              all_vars(!is.na(.))) %>%
    # Drop 2001 and 2002
    filter(fyear > 2002) %>%
    # Winsorize pooled sample---some variables are thingly populated  
    mutate_at(.vars = vars(-company_id, -management_id, -gvkey, -permno, 
                           -fyear, -fy_end, -fqtr, -fq_end,
                           -file_name, -last_update, -start_date,
                           -gsector, -ggroup, -sic3q, -sic4q, -is_sp1500, # Industry
                           -lossq, -matches("is_")), # Dummies
              .funs = funs(winsor(., 0.01))) %>%
    arrange(company_id, management_id, fyear) %>%
    copy_to(pg, ., name = "calls_set", temporary = FALSE, 
            indexes = list(c("company_id", "fyear"), 
                           c("company_id", "management_id", "fyear")))

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

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

dbDisconnect(pg)
