library(DBI)
serenitasdb <- dbConnect(RPostgres::Postgres(),
                         dbname="serenitasdb",
                         user = "serenitas_user",
                         host="debian")

sqlArray <- function(vec){
    vec[is.na(vec)] <- "NULL"
    return( sprintf("Array[%s]", paste(vec, collapse=",")) )
}

sqlQuote <- function(slist){
    return( unlist(lapply(slist, function(s) sprintf("'%s'", s)) ) )
}

nameToBasketID <- function(name, date=Sys.Date()){
    sqlstr <- "SELECT * from nametobasketid($1, $2)"
    r <- dbGetQuery(serenitasdb, sqlstr, params=list(name, date))
    return(as.integer(r))
}

set.index.desc <- function(index, date=Sys.Date()){
    ## retrieve factor, loss, and maturity from the database for a given index
    ## depending on date
    if(class(index)!="creditIndex"){
        stop("need to pass a credit index")
    }
    id <- nameToBasketID(index$name, date)
    sqlstr <- paste("SELECT indexfactor, cumulativeloss, maturity from index_desc",
                    "WHERE tenor=$1 and basketid=$2")
    r <- as.list(dbGetQuery(serenitasdb, sqlstr, params=list(index$tenor, id)))
    r$maturity <- as.Date(r$maturity)
    return(c(index,
             list(tradedate = date, factor=r$indexfactor/100,
                  loss=r$cumulativeloss/100, maturity=r$maturity)))
}

cdslist <- function(indexname, date=Sys.Date()){
    basketid <- nameToBasketID(indexname, date)
    sqlstr <- "select * from CDS_Issuers where index_list @> '{%s}'"
    return( dbGetQuery(serenitasdb, 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(serenitasdb, "SELECT * from curve_quotes($1, $2)",
                    params = list(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){
    return(list(index = toupper(substr(indexname, 0, 2)),
                series=substr(indexname, 3, nchar(indexname))))
}

get.tranchequotes <- function(indexname, tenor='5yr', date=Sys.Date()){
    temp <- indexsplit(indexname)
    ## first try the easy way:
    sqlstr <- paste("select * from tranche_quotes",
                    "where index=$1 and series=$2 and quotedate::date=$3",
                    "and tenor = $4 order by attach asc")
    r <- dbGetQuery(serenitasdb, sqlstr,
                    params = list(temp$index, temp$series, date, tenor))
    ## check if set is unique and complete
    ## hy9 and hy10 tranche is gone
    if(tolower(indexname) %in% c("hy9", "hy10")){
        lower.attach <- 10
    }else{
        lower.attach <- 0
    }
    if(length(unique(r$attach))==length(r$attach) && all(c(r$attach, 100)==c(lower.attach, r$detach))){
        return(r)
    }
    ##else we work harder
    ##we get the list of distinct quotes
    sqlstr <- paste("select distinct quotesource, quotedate from tranche_quotes",
                    "where index=$1 and series=$2 and quotedate::date=$3",
                    "and tenor = $4 order by quotedate desc")
    distinct.quotes <- dbGetQuery(serenitasdb, sqlstr,
                                  params = list(temp$index, temp$series, date, tenor))
    flag <- FALSE
    ##we loop through the disctinct quotes until we find a complete set
    if(nrow(distinct.quotes)==0){
        return(NULL)
    }
    for(i in 1:nrow(distinct.quotes)){
        if(temp$index == 'HY' && temp$series>=15){ ##don't want the tranchelets quoted by CITI
            sqlstr <- paste("select * from tranche_quotes where index=$1",
                            "and series=$2 and tenor = $3 and quotedate=$4",
                            "and detach-attach!=5  and quotesource=$5 order by attach asc")
        }else{
            sqlstr <- paste("select * from tranche_quotes where index=$1",
                            "and series=$2 and tenor = $3 and quotedate=$4",
                            "and quotesource=$5 order by attach asc")
        }
        r <- dbGetQuery(serenitasdb, sqlstr,
                        params = list(temp$index, temp$series, tenor,
                            distinct.quotes$quotedate[i],
                            distinct.quotes$quotesource[i]))
        if(all(c(r$attach, 100)==c(lower.attach, r$detach))){#set is complete
            flag <- TRUE
            break
        }
    }
    if(!flag){
        stop("no quotes for that day")
    }
    return( r )
}

couponfromindex <- function(indexname, tenor){
    temp <- indexsplit(indexname)
    sqlstr <- "select coupon from index_maturity where index=$1 and series=$2 and tenor=$3"
    r <- dbGetQuery(serenitasdb, sqlstr, params = list(temp$index, temp$series, tenor))
    r$coupon
}

getlastdate <- function(indexname, tenor){
    temp <- indexsplit(indexname)
    r <- dbGetQuery(serenitasdb,
                    paste("SELECT max(date)+1 AS date FROM risk_numbers",
                          "WHERE index=$1 and series=$2 and tenor=$3"),
                    params = list(temp$index, temp$series, tenor))
    return(as.Date(r$date, format="%Y-%m-%d"))
}