# This code creates ret and sdret for 3-month window prior to a conference call

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

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

start_sample <- '1995-01-01' # begin data from

# Define PG data ----

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

# Outside of project schema
dsf <- tbl(pg, sql("SELECT * FROM crsp.dsf"))

# Get data ----

# Daily stock file
dsf <- 
    dsf %>%
    filter(date > start_sample) %>%
    select(permno, date, prc, ret, vol, cfacpr, cfacshr) %>%
    mutate(year = sql("extract(year from date)"), 
           month = sql("extract(month from date)")) %>%
    compute(index = list(c("permno"), 
                         c("permno", "date"), 
                         c("permno", "year", "month")))

# Get link table with start_dates ---

se_link_alt <- 
    se_link %>%
    select(company_id, gvkey, permno, fyear, fy_end, file_name, last_update, start_date) %>%
    union(., hpr_link %>%
              select(company_id, gvkey, permno, fyear, fy_end, file_name, last_update, start_date)) %>%
    distinct() %>%
    compute()

company_dates <- 
    se_link_alt %>%
    mutate(year = sql("extract(year from start_date)"), 
           month = sql("extract(month from start_date)")) %>%   
    distinct() %>%
    compute(index = list("permno", 
                         c("permno", "start_date"),
                         c("permno", "year", "month")))

# Compute cumulative returns and volatility {3, 12}-months prior to call ---- 

# Compute annualized volatility of returns ---
sdret_3 <- 
    company_dates %>%
    left_join(dsf, by = "permno") %>%
    filter(sql("ret > -1 AND (start_date - interval '3 month') <= date AND date < start_date")) %>%
    distinct() %>%
    group_by(permno, file_name, start_date) %>%
    summarize(sdret_3 = sqrt(250)*sd(log(1 + ret)), 
              n_sdret_3 = n()) %>%
    ungroup() %>%
    compute(index = list("permno", c("permno", "start_date")))

sdret_12 <- 
    company_dates %>%
    left_join(dsf, by = "permno") %>%
    filter(sql("ret > -1 AND (start_date - interval '12 month') <= date AND date < start_date")) %>%
    distinct() %>%
    group_by(permno, file_name, start_date) %>%
    summarize(sdret_12 = sqrt(250)*sd(log(1 + ret)), 
              n_sdret_12 = n()) %>%
    ungroup() %>%
    compute(index = list("permno", c("permno", "start_date")))

# Compute cumulative returns ---
ret_3 <- 
    company_dates %>%
    left_join(dsf, by = "permno") %>%
    filter(sql("ret > -1 AND (start_date - interval '3 month') <= date AND date < start_date")) %>%
    distinct() %>%
    group_by(permno, file_name, start_date) %>%
    summarize(ret_3 = exp(sum(log(1 + ret))) - 1, 
              n_ret_3 = n()) %>%
    ungroup() %>%
    compute(index = list("permno", c("permno", "start_date")))

ret_12 <- 
    company_dates %>%
    left_join(dsf, by = "permno") %>%
    filter(sql("ret > -1 AND (start_date - interval '12 month') <= date AND date < start_date")) %>%
    distinct() %>%
    group_by(permno, file_name, start_date) %>%
    summarize(ret_12 = exp(sum(log(1 + ret))) - 1, 
              n_ret_12 = n()) %>%
    ungroup() %>%
    compute(index = list("permno", c("permno", "start_date")))

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

# Merge parameters
crsp <- 
    company_dates %>%    
    left_join(sdret_3) %>%
    left_join(sdret_12) %>%
    left_join(ret_3) %>%
    left_join(ret_12) %>%
    # Restrict the min number of obs n_xxx_3 and n_xxx_12
    mutate(sdret_3 = if_else(n_sdret_3 < 59, NA, sdret_3),
           ret_3 = if_else(n_ret_3 < 59, NA, ret_3),
           sdret_12 = if_else(n_sdret_12 < 248, NA, sdret_12),
           ret_12 = if_else(n_ret_12 < 248, NA, ret_12)) %>%
    distinct() %>%
    compute(name = "crsp_calls", temporary = FALSE, 
            index = list("permno", c("permno", "start_date")))

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

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

dbDisconnect(pg)
