# This code creates S&P 1500 index membership for firms in se_links

#(.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")
hpr_link <- tbl(pg, "hpr_link")

# Outside of project schema
idx_index <- tbl(pg, sql("SELECT * FROM comp.idx_index"))
idxcst_his <- tbl(pg, sql("SELECT * FROM comp.idxcst_his"))

# Get links --- 

se_link_alt <- 
    se_link %>%
    union(., hpr_link) %>%
    distinct() %>%
    compute()

# Get S&P 1500 firms ----

latest_date <- "01-01-2100"

# Standard & Poor's (S&P) 1500 index (i.e., S&P 500, S&P 400 MidCap, and S&P 600 SmallCap)
# INDEXCAT INDEXID IDXIDDESC
# S&P      500     S&P 500
# S&P      400     S&P MidCap 400
# S&P      600     S&P SmallCap 600
idx_index_alt <- 
  idx_index %>%
  filter(indexcat == "S&P" & indexid %in% c("500", "400", "600")) %>%
  select(gvkeyx, indexcat, indexid) %>%
  distinct() %>%
  compute()

idxcst_his_alt <- 
  idxcst_his %>%
  mutate(thru = coalesce(thru, latest_date)) %>%
  select(gvkey, gvkeyx, fromdt, thru) %>%
  distinct() %>%
  compute()

# Get S&P 1500 members as of call start_date
sp1500 <- 
  se_link_alt %>%
  select(gvkey, file_name, last_update, start_date) %>%
  left_join(idxcst_his_alt) %>%
  # Membership must be valid as of call start_date
  filter(fromdt <= start_date & start_date <= thru) %>%
  left_join(idx_index_alt) %>%
  filter(!is.na(indexcat) & !is.na(indexid)) %>%
  distinct() %>%
  compute()
  
# Compute variables, winsorize, and require non-missing----

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

sp1500_alt <- 
  se_link_alt %>%
  left_join(sp1500) %>%
  mutate(is_sp1500 = if_else(!is.na(indexcat) & !is.na(indexid), 1, 0)) %>%
  select(gvkey, fyear, fqtr, fq_end, file_name, last_update, start_date, indexcat, indexid, is_sp1500) %>%
  distinct() %>%
  compute(name = "sp1500", temporary = FALSE, 
          index = c("file_name", "last_update"))

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

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

dbDisconnect(pg)
