library(Rbbg)
library(RPostgreSQL)
library(lubridate)
drv <- dbDriver("PostgreSQL")
dbCon <- dbConnect(drv, dbname="serenitasdb", user="serenitas_user", password="Serenitas1",
                   host="debian")

bbgConn <- blpConnect(host='192.168.1.108', port='8194')

## This script helps adding the necessary data for a new index series
newbasketID <- function(indextype='IG', series, mat_5yr, coupon){
    sqlstr <- paste("INSERT INTO index_version(index, series, version, indexfactor, cumulativeloss, lastdate)",
                    "VALUES('%s', %s, %s, 100, 0, 'infinity')")
    stmt <- sprintf(sqlstr, indextype, series, 1)
    dbSendQuery(dbCon, stmt)
    maturity <- mat_5yr + years(c(-2, 0, 2 ,5))
    tenor <- c("3yr", "5yr", "7yr", "10yr")
    sqlstr <- "INSERT INTO index_maturity VALUES('%s', %s, '%s', '%s', %s)"
    for(i in seq_along(tenor)){
        stmt <- sprintf(sqlstr, indextype, series, tenor[i], maturity[i], coupon)
        dbSendQuery(dbCon, stmt)
    }
}

##TODO: fill the index
indexname <- 'ITRX' ## or CDX
indextype <- 'EUR' ## or IG or HY or EUR
series <- 22
newbasketID('EU', 22, as.Date("2019-12-20"))

index <- bds(bbgConn, sprintf("%s %s CDSI S%s 5Y Corp", indexname, indextype, newseries), "INDX_MEMBERS")
tickers <- index[,5]

cds.fields <- c("cds_company_id", "cds_restructuring_type", "cds_corp_tkr", "sw_seniority")
data <- bdp(bbgConn, paste(tickers, "Corp"), cds.fields)
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")
curves <- aggregate(cdscurves[,2], by=list(cdscurves$ticker), FUN=function(x)x)
prep.data <- data.frame(Name=index[,1], company_id=data$cds_company_id, ticker=data$cds_corp_tkr,
                        currency=index[,3], seniority=data$sw_seniority,
                        doc_clause=data$cds_restructuring_type)

all.companyids <- dbGetQuery(dbCon, "SELECT company_id from cds_issuers")
curves <- curves[!prep.data$company_id %in% all.companyids$company_id,]
prep.data <- prep.data[!prep.data$company_id %in% all.companyids$company_id,]
prep.data[prep.data$doc_clause=="Modified-Modified Restructurin","doc_clause"] <- "Modified Modified Restructurin"
sqlstr <- "INSERT INTO bbg_issuers VALUES('%s', %s, '%s', '%s', '%s', '%s', '%s')"
with(prep.data,
     for(i in 1:nrow(curves)){
         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)
     })

sqlstr <- "SELECT basketid from index_version where index='%s' and series=%s"
basketid <- as.integer(dbGetQuery(dbCon, sprintf(sqlstr, 'EU', series)))
##fill index_list column
sqlstr <- "UPDATE CDS_Issuers SET index_list=index_list||%s where company_id=%s"
for(id in data$cds_company_id){
    dbSendQuery(dbCon, sprintf(sqlstr, basketid, id))
}