library(RPostgreSQL)
library(RQuantLib)
library(yaml)
library(hash)
library(data.table)
options(stringsAsFactors = FALSE)
args <- commandArgs(trailingOnly=TRUE)

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

if(length(args) >= 1){
  workdate <- as.Date(args[1])
}else{
  workdate <- Sys.Date()
}

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"))
source(file.path(root.dir, "code", "R", "intex_deal_functions.R"))
source(file.path(root.dir, "code", "R", "optimization.R"))
source(file.path(root.dir, "code", "R", "interpweights.R"))
source(file.path(root.dir, "code", "R", "index_definitions.R"))

calibration.date <- prevBusDay(workdate)
MarkitData <- getMarkitIRData(calibration.date)
futurequotes <- read.csv(file.path(root.dir, "data", "Yield Curves",
                                   sprintf("futures-%s.csv", calibration.date)), header=F)
setEvaluationDate(as.Date(MarkitData$effectiveasof))
setCalendarContext("UnitedStates/GovernmentBond")
L1m <- buildMarkitYC(MarkitData, futurequotes[,2], dt = 1/12)
L2m <- buildMarkitYC(MarkitData, futurequotes[,2], dt = 1/6)
L3m <-  buildMarkitYC(MarkitData, futurequotes[,2])
L6m <- buildMarkitYC(MarkitData, futurequotes[,2], dt = 1/2)
L12m <- buildMarkitYC(MarkitData, futurequotes[,2], dt = 1)

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

processzipfiles <- function(workdate=Sys.Date()){
    pricesdir <- file.path(root.dir, "Scenarios", paste0("Prices_", workdate))
    zipfiles <- file.path(pricesdir, list.files(pricesdir, "*.zip"))
    zipfiles <- zipfiles[order(file.info(zipfiles)$ctime)]
    colnames <- paste0("column",1:20)
    tb <- list()
    for(n in seq_along(zipfiles)){
        zip <- zipfiles[n]
        r <- list()
        for(i in 1:100){
            conn <- unz(zip, filename=sprintf("CF-Scen%s.txt", i))
            data <- read.table(conn, sep="\t", header=F, colClasses="character", col.names=colnames,
                               comment.char="", fill=TRUE, stringsAsFactors=F)
            newcolnames <- data[1,]
            newcolnames <- newcolnames[newcolnames!=""]
            data <- data[-1,1:length(newcolnames)]
            colnames(data) <- newcolnames
            cleandata <- data.table(Dealname = data[,1], Date=as.Date(data$Date,  "%b %d, %Y"),
                                    Cashflow=sanitize.column(data$Cashflow),
                                    Principal = sanitize.column(data$Principal),
                                    Interest = sanitize.column(data$Interest),
                                    Balance = sanitize.column(data$Balance),
                                    `Accum Interest Shortfall` = sanitize.column(data$`Accum Interest Shortfall`),
                                    Scenario=i)
            r[[paste0("Scen",i)]] <- cleandata
        }
        r <- rbindlist(r)
        r <- r[Dealname!="TOTAL"]
        if(n==1){
            tb <- r
        }else{
            tb <- rbind(tb, r)
        }
    }
    return(tb)
}
processzipfiles2 <- function(workdate=Sys.Date()){
    pricesdir <- file.path(root.dir, "Scenarios", paste0("Prices_", workdate))
    zipfiles <- file.path(pricesdir, list.files(pricesdir, "*.zip"))
    zipfiles <- zipfiles[order(file.info(zipfiles)$ctime)]
    for(n in seq_along(zipfiles)){
        zip <- zipfiles[n]
        allfiles <- unzip(zip, list=TRUE)$Name
        dealnames <- grep("COLLAT.*Scen100", allfiles, value=TRUE)
        dealnames <- unique(unlist(lapply(strsplit(dealnames, "-"), function(x)x[1])))
        allfiles <- unique(unlist(lapply(strsplit(allfiles, "-"), function(x)x[1])))
        allfiles <- allfiles[!(allfiles=="Total")]
        cusips <- setdiff(allfiles, dealnames)
        dealnames <- tolower(dealnames)
        if(n==1){
            dealnames.hash <- hash(dealnames, 1)
            cusips.hash <- hash(cusips, 1)
        }else{
            for( c in cusips){
                cusips.hash[c] <- n
            }
            for(d in dealnames){
                dealnames.hash[d] <- n
            }
        }
    }
    return(list(dealnames=dealnames.hash, cusips=cusips.hash, zipfiles=zipfiles))
}

getdealcf2 <- function(tb){
    uniqdealnames <- unique(tb[,Dealname])
    uniqdealnames <- uniqdealnames[uniqdealnames!="TOTAL"]
    dealnames <- grep("COLLAT", uniqdealnames, value=TRUE)
    cusips <- grep("COLLAT", uniqdealnames, invert=TRUE, value=TRUE)
    alldates <- unique(tb$Date)
    n.scenarios <- 100
    T <- ifelse(alldates>=L3m$params$tradeDate, yearFrac(L3m$params$tradeDate, alldates), 0)
    DC <- DiscountCurve(L3m$params, L3m$tsQuotes, T)
    df <- data.table(Date=alldates,
                     Discounts=DC$discounts,
                     T=T)
    setkey(df, Date)
    setkey(tb, Date)
    tb[,Balance:=max(Balance-`Accum Interest Shortfall`, 0), by=1:nrow(tb)]
    sqlstring <- sprintf(paste("select cusip, dealname, curr_balance, spread from historical_cusip_universe",
                         "where cusip in ('%s')"), paste(cusips, collapse="','"))
    cusipdata <- data.table(dbGetQuery(dbCon, sqlstring), key="cusip")
    data <- tb[df][,list(Cashflow=temp <- sum(Cashflow*Discounts),
                         Principal=sum(Principal*Discounts),
                         Interest=sum(Interest*Discounts),
                         wal=sum(-diff(Balance)*T[-1])/cusipdata[Dealname]$curr_balance,
                         duration=if(temp==0) 0 else sum(Cashflow * Discounts * T)/temp
                         ), by=list(Dealname,Scenario)]

    setkey(data, "Dealname")
    cfdata <- list()
    for(dealname in dealnames){
        dealname.split <- unlist(strsplit(dealname, ","))
        dealname.short <- tolower(dealname.split[1])
        tranche <- dealname.split[2]
        if(is.null(cfdata[[dealname.short]])){
            sqlstring <- sprintf("select marketvalue from latest_deal_model_numbers where dealname='%s'",
                                 dealname.short)
            mv <- dbGetQuery(dbCon, sqlstring)$marketvalue
            sqlstring <- sprintf(paste0("select \"Curr Collat Bal\", \"Reinv End Date\" from ",
                                        "latest_clo_universe where dealname='%s'"), dealname.short)
            currbal <- dbGetQuery(dbCon, sqlstring)$"Curr Collat Bal"
            cfdata[[dealname.short]] <- list(currbal = currbal,
                                             mv = mv)
        }
        cfdata[[dealname.short]][[tranche]] <- data[dealname]
    }
    for(dealname in names(cfdata)){
        cf <- rep(0, n.scenarios)
        for(name in grep("COLLAT", names(cfdata[[dealname]]), value=TRUE)){
            cf <- cf + cfdata[[dealname]][[name]][,Cashflow]
        }
        cfdata[[dealname]]$wapbasis <- (mean(cf)- cfdata[[dealname]]$mv)/cfdata[[dealname]]$mv
        program <- KLfit(t(cf)/1e8, rep(1/n.scenarios, n.scenarios),
                         cfdata[[dealname]]$mv/1e8)
        cfdata[[dealname]]$weight <- program$weight
        cat(dealname, "\n")
    }
    setnames(cusipdata, "cusip", "Dealname")
    cusipdata <- data[cusipdata,list(Cashflow=temp <- sum(cfdata[[dealname]]$weight * Cashflow),
                                     wal = sum(cfdata[[dealname]]$weight * wal),
                                     duration = sum(cfdata[[dealname]]$weight * duration),
                                     price = 100 * temp/curr_balance,
                                     delta = compute.delta(dist, cfdata[[dealname]]$weight,
                                         Cashflow/curr_balance, workdate)
                                )]
    return( list(cusipdata, cfdata) )
}


getdealcf <- function(params, workdate=Sys.Date()){
    cfdata <- list()
    fields <-  c("Cashflow", "Principal", "Interest")
    n.scenarios <- 100
    for(dealname in keys(params$dealnames)){
        zip <- params$zipfiles[params$dealnames[[dealname]]]
        sqlstring <- sprintf("select marketvalue from latest_deal_model_numbers where dealname='%s'", dealname)
        mv <- dbGetQuery(dbCon, sqlstring)$marketvalue
        sqlstring <- sprintf(paste0("select \"Curr Collat Bal\", \"Reinv End Date\", ",
                                    "\"Deal Next Pay Date\" , maturity, \"Principal Bal\" from ",
                                    "historical_clo_universe('%s', '%s')"), dealname, workdate)
        sqldata <- dbGetQuery(dbCon, sqlstring)
        ## to get the list of payment dates, we backtrack a little bit, because sometimes the first
        ## date in intex vector is pre current date.
        ## we also add some wiggle room at the end to make sure we're not missing one date
        prevpaydate <- basic.advance(sqldata$"Deal Next Pay Date", -6, "month")
        alldates <- seq.Date(from=prevpaydate, sqldata$maturity+7, by="1 month")

        T <- ifelse(alldates>=L3m$params$tradeDate, yearFrac(L3m$params$tradeDate, alldates), 0)
        DC <- DiscountCurve(L3m$params, L3m$tsQuotes, T)
        df <- data.table(Date=alldates,
                         Discounts=DC$discounts,
                         T=T, key="Date")
        cfdata[[dealname]] <- list(mv = mv, currbal = sqldata$"Curr Collat Bal")
        config <- file.path(file.path(root.dir, "Scenarios", paste("Intex curves", workdate, sep="_"), "csv"),
                            paste0(dealname, ".config"))
        config <- yaml.load_file(config)
        if(is.na(sqldata$"Reinv End Date")||!config$reinvflag){
            tranches <- "COLLAT"
        }else{
            tranches <- c("COLLAT_INITIAL", "COLLAT_REINVEST")
            if(dealname=="octag11"){
                tranches <- c("COLLAT_USD_INITIAL", "COLLAT_USD_REINVEST")
            }
        }
        for(tranche in tranches){
            r <- matrix(0, n.scenarios, 3)
            colnames(r) <- fields
            for(i in 1:n.scenarios){
                filename <- paste0(paste(toupper(dealname), tranche, "CF", paste0("Scen", i), sep="-"), ".txt")

                conn <- unz(zip, filename)
                data <- read.table(conn, sep="\t", header=TRUE, colClasses="character", comment.char="")
                data <- data.table(data)
                ## data <- fread(file.path(root.dir, "Scenarios", paste0("Prices_", workdate), filename),
                ##               sep="\t", colClasses="character")
                data <- data[-c(1,2),]
                data[,Date:=as.Date(Date, "%b %d, %Y")]
                data <- data.table(Date=data[,Date], data[,lapply(.SD, sanitize.column),.SDcols=2:4],key="Date")
                r[i,] <- as.numeric(data[df][,list(sum(Cashflow*Discounts),
                                                   sum(Principal*Discounts),
                                                   sum(Interest*Discounts))])
            }
            cfdata[[dealname]][[tranche]]<- r
        }
        cf <- rep(0,n.scenarios)
        for(tranche in tranches){
            cf <- cf+cfdata[[dealname]][[tranche]][,"Cashflow"]
        }
        if(sqldata$"Principal Bal" < 0){
            cf <- cf-sqldata$"Principal Bal"
        }
        cfdata[[dealname]]$wapbasis <- (mean(cf)- cfdata[[dealname]]$mv)/cfdata[[dealname]]$mv
        program <- KLfit(t(cf)/1e8, rep(1/n.scenarios, n.scenarios),
                         cfdata[[dealname]]$mv/1e8)
        cfdata[[dealname]]$weight <- program$weight
        cat(dealname, "\n")
    }
    return( cfdata )
}

getcusipcf <- function(params, cfdata, dist, workdate=Sys.Date()){
    cusipdata <- list()
    cusips <- keys(params$cusips)
    dealnames <- dealnamefromcusip(cusips)
    n.scenarios <- 100
    intexfields <-  c("Cashflow", "Principal", "Interest", "Balance",
                      "Accum Interest Shortfall")
    fields <-  c("Cashflow", "Principal", "Interest")
    for(i in 1:length(cusips)){
        cusip <- cusips[i]
        zip <- params$zipfiles[params$cusips[[cusip]]]
        dealname <- dealnames[i]
        dealdata <- getdealdata(dealname)
        schedule <- getdealschedule(dealdata)
        alldates <- rev(seq.Date(from=dealdata$maturity, to=workdate-90, by="-1 month"))
        T <- ifelse(alldates>=L3m$params$tradeDate, yearFrac(L3m$params$tradeDate, alldates), 0)
        DC <- DiscountCurve(L3m$params, L3m$tsQuotes, T)
        df <- data.table(Date=alldates,
                         Discounts=DC$discounts,
                         T=T, key="Date")
        r <- matrix(0, n.scenarios, 5)
        colnames(r) <- c(fields, "wal", "duration")
        sqlstring <- sprintf("select curr_balance, spread from historical_cusip_universe('%s', '%s')",
                             cusip, workdate)
        indicdata <- dbGetQuery(dbCon, sqlstring)
        for(j in 1:n.scenarios){
            filename <- sprintf("%s-CF-Scen%s.txt", cusip, j)
            conn <- unz(zip, filename)
            data <- data.table(read.table(conn, sep="\t", header=TRUE, colClasses="character",
                                          check.names=FALSE))
            data <- data[-c(1,2),]
            data <- data[,Date:=as.Date(Date, "%b %d, %Y")]
            data <- data.table(Date=data[,Date],data[,lapply(.SD,sanitize.column),.SDcols=2:6],key="Date")
            data[,Balance:=max(Balance-`Accum Interest Shortfall`, 0), by=1:nrow(data)]
            r[j,] <- as.numeric(df[data,roll=TRUE][,list(Cashflow=temp <- sum(Cashflow*Discounts),
                                            Principal=sum(Principal*Discounts),
                                            Interest=sum(Interest*Discounts),
                                            wal=sum(-diff(Balance)*T[-1])/indicdata$curr_balance,
                                            duration=if(temp==0) 0 else sum(Cashflow * Discounts * T)/temp)])
        }
        cusipdata[[cusip]] <- list(currbal=indicdata$curr_balance,
                                   spread=indicdata$spread,
                                   Cashflow=temp <- crossprod(cfdata[[dealname]]$weight, r[,"Cashflow"]),
                                   wal = crossprod(cfdata[[dealname]]$weight, r[,"wal"]),
                                   duration = crossprod(cfdata[[dealname]]$weight, r[,"duration"]),
                                   price = 100 * temp/indicdata$curr_balance,
                                   delta = compute.delta(dist, cfdata[[dealname]]$weight,
                                       r[,"Cashflow"]/indicdata$curr_balance, workdate),
                                   fields=r)
        cat("done", cusip, "\n")
    }

    return(cusipdata)
}

compute.delta <- function(indexdist, dealweight, cusip.pv, workdate=Sys.Date(), K1=0, K2=1){
    calibration.date <- prevBusDay(workdate)
    ## we assume the index is fully funded - need to be changed depending
    ## on how we fund the swaps (hence floating coupon instead of fixed)
    cs <- couponSchedule(nextIMMDate(workdate), hy21$maturity, "Q",
                         "FLOAT", 0.05, 0, calibration.date)
    nT <- dim(indexdist$L)[2]
    Ngrid <- dim(indexdist$L)[1]
    scenariosl <- matrix(0, length(dealweight), nT)
    scenariosr <- matrix(0, length(dealweight), nT)
    for(t in 1:nT){
        scenariosl[,t] <- interpvalues(indexdist$L[,t], seq(0, 1, length=Ngrid), dealweight)
        scenariosr[,t] <- interpvalues(indexdist$R[,t], seq(0, 1, length=Ngrid), dealweight)
    }

    indexpv <- c()
    for(i in 1:length(dealweight)){
        indexpv <- c(indexpv, funded.tranche.pv(scenariosl[i,], scenariosr[i,], cs, K1, K2, TRUE))
    }

    model <- lm(cusip.pv~indexpv, weights=dealweight)
    return(model$coef[2])
}

if(length(args)>=2){
    cusips <- args[-1]
    dealnames <- unique(dealnamefromcusip(cusips))
}else{
    params <- processzipfiles2(workdate)
}

cfdata <- getdealcf(params, workdate)
cusips <- keys(params$cusips)
availablecusips <- cusips[dealnamefromcusip(cusips) %in% names(cfdata)]
## load dist into the environment
load(file.path(root.dir, "Scenarios", "Calibration", sprintf("marketdata-%s.RData", calibration.date)))
cusipdata <- getcusipcf(params, cfdata, dist, workdate)
save.dir <- file.path(root.dir, "Scenarios", paste0("Prices_", workdate))
save(cusipdata, cfdata, file=file.path(save.dir, "cashflows.RData"),
     compress="xz")

## upload wapbasis
for(dealname in names(cfdata)){
    sqlstring <- sprintf(paste0("UPDATE et_deal_model_numbers SET ",
                                "wapbasis = '%s' WHERE dealname= '%s' AND updatedate = '%s'"),
                         cfdata[[dealname]]$wapbasis*100,
                         dealname,
                         strftime(workdate))
    dbSendQuery(dbCon, sqlstring)
}

## upload model data
for(cusip in names(cusipdata)){
    sqlstring <- sprintf(paste0("SELECT updatedate from et_cusip_model_numbers",
                                " WHERE cusip='%s'"), cusip)
    sqldata <- dbGetQuery(dbCon, sqlstring)
    if(nrow(sqldata)&& (workdate %in% sqldata$updatedate)){
        columns <- c("price", "wal", "duration", "delta")
        values <- c(cusipdata[[cusip]]$price, cusipdata[[cusip]]$wal,
                    cusipdata[[cusip]]$duration, cusipdata[[cusip]]$delta)
        index <- which(!is.na(values))
        setstring <- character(length(index))
        for(i in 1:length(index)){
            setstring[i] <- sprintf("%s = %s", columns[index[i]], values[index[i]])
        }
        setstring <- paste(setstring, collapse=",")
        sqlstring <- sprintf(paste0("UPDATE et_cusip_model_numbers SET ",
                                    "%s WHERE cusip='%s' and updatedate='%s'"),
                             setstring,
                             cusip,
                             strftime(workdate))
    }else{
        columns <- c("Cusip", "price", "wal", "duration", "delta", "updatedate")
        values <- c(cusip, cusipdata[[cusip]]$price, cusipdata[[cusip]]$wal,
                    cusipdata[[cusip]]$duration, cusipdata[[cusip]]$delta,
                    strftime(workdate))
        colstring <- paste(columns[!is.na(values)], collapse=",")
        valstring <-  paste(values[!is.na(values)], collapse="','")
        sqlstring <- sprintf(paste0("INSERT INTO et_cusip_model_numbers",
                                    "(%s) VALUES('%s')"),
                             colstring, valstring)
    }
    dbSendQuery(dbCon, sqlstring)
}