# This code extracts debt/equity offerings from Capital IQ data

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

# Get data ----

start_date <- '2000-01-01'
    
dbGetQuery(pg, "DROP TABLE IF EXISTS ciq_offerings")

ciq_offerings <- 
    wrds_keydev %>%
    filter(announcedate >= start_date) %>%
    select(gvkey, announcedate, eventtype, objectroletype) %>%
    # News for issuers only
    filter(objectroletype %in% c("Target", "Seller")) %>%
    filter(eventtype %in% c("Fixed Income Offerings", 
                            "Follow-on Equity Offerings",
                            "IPOs", 
                            "Private Placements")) %>%
    mutate(is_ciq_debt = (eventtype == "Fixed Income Offerings"), 
           is_ciq_equity = (eventtype == "Follow-on Equity Offerings"
                        | eventtype == "IPOs"), 
           is_ciq_private = (eventtype == "Private Placements")) %>%
    mutate(is_ciq_offering = (is_ciq_debt | is_ciq_equity | is_ciq_private)) %>%
    filter(!is.na(gvkey) & !is.na(announcedate)) %>%
    # Collapse by date
    group_by(gvkey, announcedate) %>%
    summarize_at(.vars = vars(matches("is_ciq_")), 
              .funs = funs(bool_or(.))) %>%
    ungroup() %>%
    compute(name = "ciq_offerings", temporary = FALSE, 
            index = list("gvkey", c("gvkey", "announcedate")))

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

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

dbDisconnect(pg)
