library(Rbbg)
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
dbCon <- dbConnect(drv, dbname="mlpdb", user="mlpdb_user", password="Serenitas1",
                   host="debian")
setwd("/home/share/CorpCDOs/data/bloomberg/CDS")
bbgConn <- blpConnect(host='192.168.1.108', port='8194')
ig.indices <- c("19", "21", "22")
igpattern <- "CDXIG5%s Curncy"
hy.indices <- c("15", "17", "19", "21", "22")
hypattern <- "CDXHY5%s Curncy"
itraxx.indices <- c("19", "20", "21")
itraxxpattern <- "ITXEB5%s Curncy"

for(index in ig.indices){
    assign(paste0("IG", index), bds(bbgConn, sprintf(igpattern, index), "INDX_MEMBERS"))
}
assign("IG9", bds(bbgConn, "IBOXUG09 Curncy", "INDX_MEMBERS"))

for(index in hy.indices){
    assign(paste0("HY", index), bds(bbgConn, sprintf(hypattern, index), "INDX_MEMBERS"))
}

for(index in itraxx.indices){
    assign(paste0("EU", index), bds(bbgConn, sprintf(itraxxpattern, index), "INDX_MEMBERS"))
}
assign("EU9", bds(bbgConn, "ITRXEB09 Curncy", "INDX_MEMBERS"))

all.names <- c()
all.indices <- c(paste0("HY", hy.indices), paste0("IG", ig.indices), paste0("EU", itraxx.indices), "IG9", "EU9")
for(index in all.indices){
    all.names <- rbind(all.names, get(index))
}

tickers <- unique(all.names[,5])
data <- bdp(bbgConn, paste(tickers, "Corp"), c("cds_company_id", "cds_restructuring_type", "cds_corp_tkr"))
data <- cbind(tickers, data)
unique.tickers <- aggregate(data, by=list(data$cds_company_id), FUN=function(x)x[1])$tickers
cdscurves <- bds(bbgConn, paste(unique.tickers, "Curncy"), "CDS_CURVE_INFO")
temp1 <- all.names[match(unique.tickers, all.names[,5]),]
temp2 <- data[match(unique.tickers, data$tickers),]
curves <- aggregate(cdscurves[,2], by=list(cdscurves$ticker), FUN=function(x)x)
prep.data <- data.frame(Name=temp1[,1], company_id=temp2[,2], ticker=temp2[,4],
                   currency=temp1[,3], seniority=temp1[,4], doc_clause=temp2[,3])

sqlstr <- "INSERT INTO CDS_Issuers VALUES('%s', %s, '%s', '%s', '%s', '%s', '%s')"
with(prep.data,
     for(i in 1:nrow(prep.data)){
         stmt <- sprintf(sqlstr, gsub("'","''", Name[i]), company_id[i], ticker[i], currency[i],
                         seniority[i], doc_clause[i],
                         paste0("{\"", paste(curves[i,2], collapse="\",\""), "\"}"))
         dbSendQuery(dbCon, stmt)
     })
write.csv(cdscurves, file=paste0("single_names_", Sys.Date(), ".csv"), row.names=FALSE)

data <- c()
for(index in c("ig21", "ig19", "hy21", "hy19")){
    df <- read.csv(paste0(index, "_tranches_bbgid.csv"), check.names=FALSE)
    for(tenor in c("3Y", "5Y", "7Y")){
        if(tenor %in% names(df)){
            data <- rbind(data, bdp(bbgConn, paste(df[[tenor]], "Corp"), c("px_last", "tranche_delta", "underlying_reference_px_rt")))
        }
    }
}

##to get default data
"default_data"

write.csv(data, file=paste0("tranche_data_", Sys.Date(), ".csv"), row.names=TRUE)
blpDisconnect(bbgConn)

ITXEB521