library(stringr)
source("db.R")
serenitasdb <- dbConn("serenitasdb")

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

sqlQuote <- function(svec){
    return( str_c("'", svec, ",") )
}

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)))
    if(id %in% c(447L, 449L, 453L)) { #NSINO didn't trigger for XO22 in tranches
        r$indexfactor <- r$indexfactor + 1 + 1/3
    }
    for(key in c("tradedate", "factor", "loss", "maturity")) {
        index[[key]] <- NULL
    }
    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 bbg_issuers where Array[$1::int] && index_list"
    return( dbGetQuery(serenitasdb, sqlstr, params=list(basketid)))
}

arr.convert <- function(arr, col.names){
    ## convert into a sqlarray into an R arraay
    ## inverse of sqlArray
    if(missing(col.names)){
        ncol <- str_count(arr[1], ",") + 1
    }else{
        ncol <- length(col.names)
    }
    arr <- str_split_fixed(str_sub(arr, 2, -2), ",", ncol)
    arr[arr=="NULL"] <- NA
    storage.mode(arr) <- "numeric"
    if(!missing(col.names)){
        colnames(arr) <- col.names
    }
    return(arr)
}

get.singlenamesquotes <- function(indexname, date=Sys.Date()){
    r <- dbGetQuery(serenitasdb, "SELECT * from curve_quotes($1, $2)",
                    params = list(indexname, date))
    tenors <- c("6m", paste0(c(1, 2, 3, 4, 5, 7, 10), "y"))
    quotes <- list(tickers=r[,1],
                   spread_curve = arr.convert(r$spread_curve, tenors),
                   upfront_curve =arr.convert(r$upfront_curve, tenors),
                   recovery_curve = arr.convert(r$recovery_curve, tenors))
    return( quotes )
}

indexsplit <- function(indexname){
    return(list(indextype = str_to_upper(str_sub(indexname, 0, 2)),
                series=str_sub(indexname, 3, -1)))
}

get.tranchequotes <- function(indexname, tenor='5yr', date=Sys.Date()){
    ## first try the easy way:
    sqlstr <- "SELECT * from get_tranche_quotes($1, $2, $3, $4)"
    r <- with(indexsplit(indexname),
              suppressWarnings(
                  dbGetQuery(serenitasdb, sqlstr, params = list(indextype, series, tenor, date))
              )
              )
    return( r )
}

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

getlastdate <- function(indexname, tenor){
    stmt <- paste("SELECT max(date)+1 AS date FROM risk_numbers_old",
                  "WHERE index=$1 and series=$2 and tenor=$3")
    r <- with(indexsplit(indexname),
              dbGetQuery(serenitasdb, stmt, params =
                         list(indextype, series, tenor)))
    return( r$date )
}

get.indexquotes <- function(indexname, tenor) {
    stmt <- "SELECT * from index_quotes WHERE index=$1 and series=$2 and tenor=$3 ORDER BY date"
    return ( with(indexsplit(indexname),
                  dbGetQuery(serenitasdb, stmt, params=list(indextype, series, tenor))) )
}

get.skews <- function(indexname, tenor){
    stmt <- "select date, skew from risk_numbers where index=$1 and series=$2 and tenor=$3 order by date"
    arr <- with(indexsplit(indexname),
                dbGetQuery(serenitasdb, stmt, params=list(indextype, series, tenor)))
    dates <- arr$date
    skews <- data.frame(dates, arr.convert(arr$skew))
    return ( skews[,-ncol(skews)] )
}