library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
mlpdbCon <- dbConnect(drv, dbname="mlpdb", user="mlpdb_user", password="Serenitas1",
                   host="debian")

nameToBasketID <- function(name, date=Sys.Date()){
    sqlstr <- "SELECT * from nametobasketid('%s', '%s')"
    r <- dbGetQuery(mlpdbCon, sprintf(sqlstr, name, date))
    return(as.integer(r))
}

load.index <- function(name, date=Sys.Date(), tenor="5yr"){
    id <- nameToBasketID(name, date)
    sqlstr <- "SELECT indexfactor, cumulativeloss, maturity from index_desc where basketid=%s and tenor='%s'"
    r <- as.list(dbGetQuery(mlpdbCon, sprintf(sqlstr, id, tenor)))
    return(list(tenor=tenor, factor=r$indexfactor/100, maturity=r$maturity,
                loss=r$cumulativeloss/100, recovery=0.4, name=name))
}

cdslist <- function(indexname, date=Sys.Date()){
    basketid <- nameToBasketID(indexname, date)
    sqlstr <- "select * from CDS_Issuers where index_list @> '{%s}'"
    return( dbGetQuery(mlpdbCon, sprintf(sqlstr, basketid)))
}

arr.convert <- function(arr){
    arr <- unlist(lapply(arr, function(x)strsplit(substr(x,2,nchar(x)-1),",",fixed=TRUE)))
    arr[arr=="NULL"] <- NA
    arr <- matrix(as.numeric(arr), nrow=length(arr)/8, ncol=8, byrow=T)
    colnames(arr) <- c("6m", "1y", "2y", "3y", "4y", "5y", "7y", "10y")
    return(arr)
}

get.indexquotes <- function(indexname, date=Sys.Date()){
    r <- dbGetQuery(mlpdbCon, sprintf("select * from curve_quotes('%s', '%s')", indexname, date))
    quotes <- list(tickers=r[,1],
                   spread_curve = arr.convert(r$spread_curve),
                   upfront_curve =arr.convert(r$upfront_curve),
                   recovery_curve = arr.convert(r$recovery_curve))
    return( quotes )
}

indexsplit <- function(indexname){
    r <- regexpr("(\\D*)(\\d*)", indexname, perl=T)
    cs <- attr(r, "capture.start")
    cl <- attr(r, "capture.length")
    index <- substr(indexname, cs[1], cs[1]+cl[1]-1)
    series <- substr(indexname, cs[2], cs[2]+cl[2]-1)
    return(list(index=toupper(index), series=series))
}

get.tranchequotes <- function(indexname, tenor='5yr', date=Sys.Date()){
    sqlstr <- paste("select * from tranche_quotes where index='%s'",
                    "and series=%s and quotedate::date='%s' and tenor = '%s'",
                    "and quotesource='MKIT' order by attach asc")
    temp <- indexsplit(indexname)
    r <- dbGetQuery(mlpdbCon, sprintf(sqlstr, temp$index, temp$series, date, tenor))
    if(nrow(r)==0){
        sqlstr <- paste("select * from tranche_quotes where index='%s'",
                    "and series=%s and quotedate::date='%s' and tenor = '%s'",
                    "order by attach asc")
         r <- dbGetQuery(mlpdbCon, sprintf(sqlstr, temp$index, temp$series, date, tenor))
    }
    return( r )
}