library(RPostgreSQL)
library(RQuantLib)
if(.Platform$OS.type == "unix"){
  root.dir <- "/home/share/CorpCDOs"
}else{
  root.dir <- "//WDSENTINEL/share/CorpCDOs"
}

source(file.path(root.dir, "code", "R", "etdb.R"))
source(file.path(root.dir, "code", "R", "yieldcurve.R"))
source(file.path(root.dir, "code", "R", "cds_utils.R"))

workdate <- as.Date("2013-02-07")

MarkitData <- getMarkitIRData(workdate)
L1m <- buildMarkitYC(MarkitData, dt = 1/12)
L2m <- buildMarkitYC(MarkitData, dt = 1/6)
L3m <-  buildMarkitYC(MarkitData)
L6m <- buildMarkitYC(MarkitData, dt = 1/2)
L12m <- buildMarkitYC(MarkitData, dt = 1)
setEvaluationDate(as.Date(MarkitData$effectiveasof))

sanitize.column <- function(vec){
  vec <- gsub(",", "", vec)
  index <- grep("\\(", vec)
  for(l in index){
    vec[l] <- -as.numeric(substr(vec[l], 2, nchar(vec[l])-1))
  }
  return( as.numeric(vec) )
}

fields <-  c("Cashflow", "Principal", "Interest")
tranches <- c("COLLAT_REINVEST", "COLLAT_INITIAL")
n.scenarios <- 100

flag <- FALSE
dealnames <- c("ares11", "cifc071", "cifc122", "comst", "duanst1", "empf2",
               "galax8", "gulf052", "halcli1", "hals071", "hewett3", "hewett6",
               "hillmf", "ingim2", "ingim3", "katon10", "katonah8", "katonah9",
               "landmrk6", "landmrk8", "latcl3", "madpk6", "mayp", "mtwil2",
               "oakcp3", "oceant1", "pacific3", "primus2", "sappv1", "saratg_1",
               "shack1", "standay", "sumlk", "t2if1", "vent12", "vent7", "vent9",
               "wthrs3")
allfiles <- list.files(file.path(root.dir, "Scenarios", paste0("Prices_", workdate)), "*.txt")
allfiles <- unique(sapply(strsplit(allfiles, "-"), function(x) x[1]))
allfiles <- allfiles[!(allfiles=="Total")]
dealnames <- list.files(file.path(root.dir, "Scenarios", paste0("Prices_", workdate)), "*COLLAT_INITIAL-CF-Scen1*")
dealnames <- unique(sapply(strsplit(dealnames, "-"), function(x) x[1]))
cusips <- setdiff(allfiles, dealnames)
dealnames <- tolower(dealnames)

cfdata <- list()
for(dealname in dealnames){
    cfdata[[dealname]] <- list()
    r <- matrix(0, n.scenarios, 3)
    colnames(r) <- fields
    sqlstring <- sprintf("select marketvalue from latest_deal_model_numbers where dealname='%s'", dealname)
    mv <- dbGetQuery(dbCon, sqlstring)$marketvalue
    sqlstring <- sprintf("select \"Curr Collat Bal\" from latest_clo_universe where dealname='%s'", dealname)
    currbal <- dbGetQuery(dbCon, sqlstring)$"Curr Collat Bal"
    cfdata[[dealname]]$mv <- mv
    cfdata[[dealname]]$currbal <- currbal
    for(tranche in tranches){
        for(i in 1:n.scenarios){
            filename <- paste0(paste(toupper(dealname), tranche, "CF", paste0("Scen", i), sep="-"), ".txt")
            data <- read.table(file.path(root.dir, "Scenarios", paste0("Prices_", workdate), filename),
                               sep="\t", header=F, skip=3, colClasses="character", comment.char="")
            data <- data[,1:4]
            colnames(data) <- c("Date", "Cashflow", "Principal", "Interest")
            data$Date <- as.Date(data$Date, "%b %d, %Y")
            if(any(is.na(data$Date))){
                cat(sprintf("file: %s is messed up", filename), "\n")
                flag <- TRUE
                break
            }
            futuredates <- data$Date[data$Date>=workdate]
            pastdates <- data$Date[data$Date<workdate]
            if(i==1||length(futuredates)>length(DC$times)){
                DC <- DiscountCurve(L3m$params, L3m$tsQuotes, yearFrac(L3m$params$tradeDate, futuredates))
            }
            pv <- c()
            for(field in fields){
                data[,field] <- tryCatch(sanitize.column(data[,field]),
                                         warning = function(w){cat("garbled", dealname, i)})
                if(length(futuredates) == 0){
                    df <- rep(1, length(pastdates))
                }else{
                    df <- c(rep(1, length(pastdates)), DC$discounts[1:length(futuredates)])
                }
                if(nrow(data)>0){
                    pv <- c(pv, crossprod(df, data[,field]))
                }else{
                    pv <- c(pv, 0)
                }
            }
            r[i,] <- pv
        }
        if(flag){
            cfdata[[dealname]] <- NULL
            flag <- FALSE
            break
        }else{
            cfdata[[dealname]][[tranche]]<- r
        }
    }
}

r <- c()
for(dealname in dealnames){
    r <- rbind(r, c(cfdata[[dealname]]$mv, cfdata[[dealname]]$currbal, apply(cfdata[[dealname]]$COLLAT_REINVEST, 2, mean)[1], apply(cfdata[[dealname]]$COLLAT_INITIAL, 2, mean)[1]))
}
colnames(r) <- c("mv", "currbal", "Reinvest", "Initial")
rownames(r) <- dealnames

intexfields <-  c("Cashflow", "Principal", "Interest", "Balance")

flag <- FALSE
cusipdata <- list()
for(cusip in cusips){
    dealname <- dealnamefromcusip(cusip)
    dealdata <- getdealdata(dealname)
    schedule <- getdealschedule(dealdata)
    r <- matrix(0, n.scenarios, 3)
    colnames(r) <- fields
    sqlstring <- sprintf("select curr_balance, spread from cusip_universe where cusip = '%s'", cusip)
    indicdata <- dbGetQuery(dbCon, sqlstring)
    cusipdata[[cusip]]$currbal <- indicdata$curr_balance
    cusipdata[[cusip]]$spread <- indicdata$spread
    for(i in 1:n.scenarios){
        filename <- sprintf("%s-CF-Scen%s.txt", cusip, i)
        if(length(list.files(file.path(root.dir, "Scenarios", paste0("Prices_", workdate)), filename))==0){
            next
        }
        data <- read.table(file.path(root.dir, "Scenarios", paste0("Prices_", workdate), filename),
                           sep = "\t", header=T, colClasses="character", skip = 3, comment.char="")
        data <- data[, 1:5]
        colnames(data) <- c("Date", "Cashflow", "Principal", "Interest", "Balance")
        data$Date <- as.Date(data$Date, "%b %d, %Y")
        if(any(is.na(data$Date))){
            cat(sprintf("file: %s is messed up", filename), "\n")
            flag <- TRUE
            break
        }
        ## cleanup the data
        for(field in intexfields){
            data[,field] <- tryCatch(sanitize.column(data[,field]),
                                     warning = function(w){cat("garbled", dealname, i)})
        }
        futuredates <- data$Date[data$Date >= workdate]
        pastdates <- data$Date[data$Date < workdate]
        if(i==1||length(futuredates)>length(DC$times)){
            DC <- DiscountCurve(L3m$params, L3m$tsQuotes, yearFrac(L3m$params$tradeDate, futuredates))
        }
        if(length(futuredates) == 0){
            df <- rep(1, length(pastdates))
        }else{
            df <- c(rep(1, length(pastdates)), DC$discounts[1:length(futuredates)])
        }
        ## compute the pvs
        for(field in c("Cashflow", "Principal", "Interest")){
            if(nrow(data)>0){
                cusipdata[[cusip]][[field]] <- c(pv, crossprod(df, data[,field]))
            }else{
                cusipdata[[cusip]][[field]] <- c(pv, crossprod(df, data[,field]))
            }
        }
        ## compute the duration
        data[,"Balance"]* DC$forwards[DC$times==
        if(flag){
            cusipsdata[[cusip]] <- NULL
            flag <- FALSE
            break
        }else{
            for(field in fields){
                cusipdata[[cusip]][[field]] <- r[,field]
            }
            cusipdata[[cusip]][["duration"]] <- data[,"Interest"]/data
        }
}

prices <- c()
duration <- c()
    for(cusip in names(cusipdata)){
        prices <- c(prices, mean(cusipdata[[cusip]]$Cashflow)/cusipdata[[cusip]]$currbal)
        duration <- c(duration, mean(cusipdata[[cusip]]$Interest)/
                      (cusipdata[[cusip]]$currbal*cusipdata[[cusip]]$spread))
    }
}
cusupdata[[cusip]]$Interest-cusipdata[[cusip
durations <- getdealschedule(getdealdata("marlst"))
forwards <- DC$forwards
cusipdata[[cusip]]$Balance * forwards[i]+spreadi <- 1
for(cusip in names(cusipdata)){
    cat(cusip, prices[i], "\n")
    i <- i+1
}