# This codes creates merge link from streetevents to companies in company_fyear

#(.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")

# Outside of project schema
fundq <- tbl(pg, sql("SELECT * FROM comp.fundq"))
company_fyear <- tbl(pg, sql("SELECT * FROM exec_comp_2016.company_fyear"))
se_call_link <- tbl(pg, sql("SELECT * FROM executive_2016.se_call_link"))
calls <- tbl(pg, sql("SELECT * FROM streetevents_old.calls"))

# Get quarter end dates ----

start_date <- '1997-01-01'

fundq_alt <- 
    fundq %>%
    # Keep standard reporting
    filter(indfmt == 'INDL' & datafmt == 'STD' & popsrc == 'D' & consol == 'C' & !is.na(fyearq)) %>%   
    filter(datadate > start_date) %>%
    select(gvkey, fq_end = datadate, fyearq, fqtr) %>%
    filter(!is.na(fyearq) & !is.na(fqtr)) %>%
    distinct() %>%
    group_by(gvkey, fyearq, fqtr) %>%
    filter(fq_end == min(fq_end)) %>%
    ungroup() %>%
    compute()

# Get latest calls by update and extract date details ---

latest_calls <-
    calls %>%
    select(file_name, last_update, start_date) %>%
    distinct() %>%
    group_by(file_name) %>%
    filter(last_update == max(last_update)) %>%
    ungroup() %>%
    mutate(call_yr_mon = to_char(start_date, 'YYYY-MM'),
           call_mon = as.integer(date_part('month', start_date)),
           call_yr = as.integer(date_part('year', start_date))) %>%
    select(file_name, last_update, start_date,
           call_mon, call_yr, call_yr_mon, call_mon) %>%
    compute()

# Create link table ----

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

# Match company links to fiscal years
se_fyear <- 
    latest_calls %>%
    select(file_name, last_update, start_date, 
           call_mon, call_yr, call_yr_mon) %>%
    inner_join(se_call_link %>%
                   select(file_name, permno, company_id)) %>%
    inner_join(company_fyear) %>%
    # Identify fiscal year the call belongs to
    # Call date is [fy_end - 9 months, fy_end + 3 months]
    filter(sql("fy_end - interval '9 months'") <= start_date 
           & start_date < sql("fy_end + interval '3 months'")) %>%
    # For multiple fy_end matches, keep min fy_end
    group_by(file_name) %>%
    filter(fy_end == min(fy_end)) %>%
    ungroup() %>%
    distinct() %>%
    compute()

# Match to fiscal quarters; keep the most immediate end of quarter
se_link <- 
    se_fyear %>%
    left_join(fundq_alt %>%
                  mutate(fyear = fyearq)) %>%
    left_join(fundq_alt %>%
                  mutate(fyear = fyearq + 1)) %>%
    left_join(fundq_alt %>%
                  mutate(fyear = fyearq - 1)) %>%
    filter(fq_end <= start_date) %>%
    group_by(file_name, last_update, start_date) %>%
    filter(fq_end == max(fq_end)) %>%
    ungroup() %>%
    compute(name = 'se_link', temporary = FALSE,
            index = list(c("file_name", "start_date"), 
                         c("company_id", "fyear")))

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

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

dbDisconnect(pg)
