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

blpConnect(host='192.168.9.28')

## 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, red){
    sqlstr <- paste0("INSERT INTO index_version",
                     "(index, series, version, indexfactor, cumulativeloss, lastdate, redindexcode) ",
                     "VALUES($1, $2, $3, 100, 0, 'infinity', $4) RETURNING basketid")
    if(missing(red)) {
        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)"
    ## TODO: add issue date
    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)
}

insert.newcurves <- function(tickers) {
    cds.fields <- c("cds_company_id", "cds_restructuring_type", "cds_corp_tkr",
                    "sw_seniority", "cds_company_name", "restructuring_type_short_code")
    df <- bdp(paste(tickers, "Corp"), cds.fields)
    r <- c()
    for(t in tickers) {
        r <- c(r,
               sqlArray(bds(paste(t, "Curncy"), "CDS_CURVE_INFO")$Ticker))
    }
    df["cds_curve"] <- r
    sqlstr <- paste0("INSERT INTO bbg_issuers",
                     "(name, company_id, ticker, currency, seniority, doc_clause, cds_curve, short_code)",
                     "VALUES($1, $2, $3, $4, $5, $6, $7, $8) ON CONFLICT DO NOTHING")
    with(df,
         for(i in 1:nrow(df)) {
             r <- dbSendQuery(serenitasdb, sqlstr,
                              params=list(cds_company_name[i],
                                          cds_company_id[i], cds_corp_tkr[i],
                                          "EUR", sw_seniority[i], cds_restructuring_type[i],
                                          cds_curve[i], restructuring_type_short_code[i]))
             if(dbHasCompleted(r)){
                 dbClearResult(r)
             }
         })
}

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",
                    "restructuring_type_short_code")

    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, short_code=restructuring_type_short_code))
    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, short_code)",
                         "VALUES($1, $2, $3, $4, $5, $6, $7, $8) ON CONFLICT DO NOTHING")
        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],
                                                short_code[i]))
                 if(dbHasCompleted(r)){
                     dbClearResult(r)
                 }
             })
    }
    sqlstr <- "UPDATE bbg_issuers SET index_list=index_list||$1::int where company_id=$2 and seniority=$3"
    for(i in 1:dim(df)[1]){
        r <- dbSendQuery(serenitasdb, sqlstr, params=list(basketid, df[i, "cds_company_id"],
                                                          df[i, "sw_seniority"]))
        if(dbHasCompleted(r)){
            dbClearResult(r)
        }
    }
    return(prep.data)
}

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

if(interactive()) {
    basket.id <- newbasketID('XO', 33, 1, as.Date("2025-06-20"), 500, "2I667KHL5")
    df <- newcurves('XO', 33, basket.id)
    mappings <- data.frame(company_id = df$company_id, seniority = df$seniority,
                           markit_ticker=c("AIRLIQ"),
                           markit_tier=c("SNRFOR"), coupon=100)
    markit.mappings(mappings)
    ## then update index_list in markit/import_quotes.py and rerun insert_cds
}