library(Rblpapi)
source("serenitasdb.R")

blpConnect(host='192.168.9.61')

## This script helps adding the necessary data for a new index series

bbgname <- function(indextype, series, tenor="5Y"){
    ## indextype is EUR, IG, HY or XOVER
    if(indextype=="EU"){
        indextype <- "EUR"
        indexname <- 'ITRX'
    }else if(indextype=="XO"){
        indextype <- "XOVER"
        indexname <- "ITRX"
    }else{
        indexname <- 'CDX'
    }
    return( sprintf("%s %s CDSI S%s %s Corp", indexname, indextype, series, tenor) )
}

newbasketID <- function(indextype='IG', series, version=1, mat_5yr, coupon){
    sqlstr <- paste0("INSERT INTO index_version",
                     "(index, series, version, indexfactor, cumulativeloss, lastdate, redindexcode) ",
                     "VALUES($1, $2, $3, 100, 0, 'infinity', $4) RETURNING basketid")
    red <- bdp(bbgname(indextype, series), "CDS_RED_PAIR_CODE")$CDS_RED_PAIR_CODE
    basketid <- as.integer(dbGetQuery(serenitasdb, sqlstr,
                                      params=list(indextype, series, version, red))$basketid)
    maturity <- mat_5yr + lubridate::years(c(-2, 0, 2 ,5))
    tenor <- c("3yr", "5yr", "7yr", "10yr")
    sqlstr <- "INSERT INTO index_maturity VALUES($1, $2, $3, $4, $5)"
    for(i in seq_along(tenor)){
        r <- dbSendQuery(serenitasdb, sqlstr,
                        params=list(indextype, series, tenor[i], maturity[i], coupon))
        if(dbHasCompleted(r)){
            dbClearResult(r)
        }
    }
    return(basketid)
}

newcurves <- function(indextype, series, basketid){
    index <- bds(bbgname(indextype, series), "INDX_MEMBERS")
    tickers <- index[,5]
    cds.fields <- c("cds_company_id", "cds_restructuring_type", "cds_corp_tkr", "sw_seniority")

    df <- bdp(paste(tickers, "Corp"), cds.fields)
    df <- cbind(tickers, df)
    prep.data <- with(df, data.frame(Name=index[,1], company_id=cds_company_id,
                                     ticker=cds_corp_tkr, currency=index[,3],
                                     seniority=sw_seniority, doc_clause=cds_restructuring_type,
                                     curve_ticker=tickers))
    all.companyids <- dbGetQuery(serenitasdb, "SELECT company_id FROM bbg_issuers")
    prep.data <- prep.data[!prep.data$company_id %in% all.companyids$company_id,]
    if(nrow(prep.data) > 0){
        cdscurves <- data.frame()
        for(ticker in prep.data$curve_ticker){
            cdscurves <- rbind(cdscurves,
                               data.frame(ticker=ticker,
                                          bds(paste(ticker, "Curncy"), "CDS_CURVE_INFO")))
        }
        temp <- aggregate(Ticker~ticker, data=cdscurves, FUN=sqlArray)
        colnames(temp) <- c("curve_ticker", "cds_curve")
        prep.data <- merge(prep.data, temp)
        prep.data$doc_clause <- gsub("-", " ", prep.data$doc_clause)

        sqlstr <- paste0("INSERT INTO bbg_issuers",
                         "(name, company_id, ticker, currency, seniority, doc_clause, cds_curve)",
                         "VALUES($1, $2, $3, $4, $5, $6, $7)")
        with(prep.data,
             for(i in 1:nrow(prep.data)){
                 r <- dbSendQuery(serenitasdb, sqlstr,
                                  params = list(Name[i], company_id[i], ticker[i],
                                  currency[i], seniority[i], doc_clause[i], cds_curve[i]))
                 if(dbHasCompleted(r)){
                     dbClearResult(r)
                 }
             })
    }
    sqlstr <- "UPDATE bbg_issuers SET index_list=index_list||$1::int where company_id=$2"
    for(id in df$cds_company_id){
        r <- dbSendQuery(serenitasdb, sqlstr, params=list(basketid, id))
        if(dbHasCompleted(r)){
            dbClearResult(r)
        }
    }
    return(prep.data)
}

markit.mappings <- function(df){
    sqlstr <- "INSERT INTO bbg_markit_mapping VALUES('infinity', $1, $2, $3, $4)"
    with(df,
         for(i in 1:nrow(df)){
             r <- dbSendQuery(serenitasdb, sqlstr,
                              params=list(company_id[i],
                                  markit_ticker[i],
                                  seniority[i],
                                  coupon[i]))
             if(dbHasCompleted(r)){
                 dbClearResult(r)
             }
         })
}

if(interactive()) {
    basket.id <- newbasketID('HY', 26, 1, as.Date("2021-06-20"), 500)
    df <- newcurves('HY', 26, basket.id)
    mappings <- data.frame(company_id = df$company_id,
                           markit_ticker=c("BLL","THEADT", "EQIX", "TRADIG", "TGRI-Ptnr", "ETE"),
                           seniority=c(rep("SNRFOR",3),"SUBLT2","SNRFOR","SECDOM"), coupon=500)
    markit.mappings(mappings)
    ## then update index_list in markit/import_quotes.py and rerun insert_cds
}