/*
 * PortfolioEvaluator.java
 *
 * Created on October 31, 2007, 4:08 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package uc2;

import java.util.*;
import java.math.*;
import java.sql.*;

public class PortfolioEvaluator 
{
    List<StockData> stocksList = new ArrayList<StockData>();
    double alpha_p;
    double beta_p;
    double risk_p;
    double exp_return_p;
    double variance_p;
    double value_p;
    double ttl_gain_p;
    double sharpe_ratio;
    
    /** Creates a new instance of PortfolioEvaluator */
    class StockData 
    {
        String symbol;
        double alpha = 0.0;
        double beta = 0.0;
        double residue = 0.0;
        double price = 0.0;
        double buy_price = 0.0;
        double weight = 0.0;
        double share = 0.0;
        double ratio = 0.0;  
        double exp_return = 0.0;
        double variance = 0.0;
        double sharpe_ratio = 0.0;
    }
    
    public PortfolioEvaluator() 
    {
        alpha_p = 0.0;
        beta_p = 0.0;
        risk_p = 0.0;
        exp_return_p = 0.0;
        variance_p = 0.0;
        value_p = 0.0;
        ttl_gain_p = 0.0;
        sharpe_ratio = 0.0;
    }
    
    boolean getStocks(long investorID) 
    {
        // Connect to DatabaseCommunicator and use InvestorID as a key
        // to find query the stocks in Investor's portfolio
        DatabaseCommunicator.connect();
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        try 
        {
            String query1 = "SELECT symbol, buy_price, num_shares FROM portfolio WHERE investor_id = " + investorID + ";";
            rs1 = DatabaseCommunicator.query(query1);
            while(rs1.next()) 
            {
                StockData stock = new StockData();
                stock.symbol = rs1.getString("symbol");
                stock.buy_price = rs1.getDouble("buy_price");
                stock.ratio = 0.0;
                stock.share = rs1.getDouble("num_shares");

                String query2 = "SELECT last FROM stock WHERE symbol = '" + stock.symbol + "';";
                rs2 = DatabaseCommunicator.query(query2);
                rs2.next();
                stock.price = rs2.getDouble("last");
                stocksList.add(stock);
            }
            DatabaseCommunicator.close();
            if(stocksList.size() <= 0)
                return( false );
        }
        catch (Exception e) 
        {
            System.err.println("Exception: " + e.toString());
            return( false );
        }
        return( true );
    }

    void calculatePortfolioValue() 
    {
        double ss = 0;
        for(int stockID=0; stockID < stocksList.size(); stockID++) 
        {
            StockData stockData = (StockData) stocksList.get(stockID);
            ss += (stockData.price*stockData.share);
        }
        value_p = ss;        
    }
    
    void calculatePortfolioTotalGain() 
    {
        double ss = 0;
        for(int stockID=0; stockID < stocksList.size(); stockID++) 
        {
            StockData stockData = (StockData) stocksList.get(stockID);
            ss += (stockData.buy_price*stockData.share);
        }
        ttl_gain_p = value_p - ss;
    }

    void calculateStocksWeightsInPortfolio() 
    {
        for(int stockID=0; stockID < stocksList.size(); stockID++) 
        {
            StockData stockData = (StockData) stocksList.get(stockID);
            stockData.weight = (stockData.price*stockData.share)/value_p;
        }
    }
        
    void updateStocksWeightsInPortfolio() 
    {
        DatabaseCommunicator.connect();
        try
        {
            // Update stock parameters in table portfolio.
            for(int stockID=0; stockID < stocksList.size(); stockID++)
            {
                StockData stockData = (StockData) stocksList.get(stockID);
                DatabaseCommunicator.query("UPDATE portfolio SET weight = " + stockData.weight + " where symbol = '" + stockData.symbol + "';");
            }
            DatabaseCommunicator.close();
        }
        catch (Exception e)
        {
            System.err.println("Exception: " + e.toString());
        }
    }

    public boolean evaluateStocksInPortfolio(long investorID) 
    {
        // Connect to DatabaseCommunicator and use InvestorID as a key
        // to find query the stocks in Investor's portfolio
        if(getStocks(investorID) == false)
            return( false );
        calculatePortfolioValue();
        calculatePortfolioTotalGain();
        calculateStocksWeightsInPortfolio();
        updateStocksWeightsInPortfolio();
        // loop through resultSet and perform Single Index Model calculation
        ListIterator<StockData> stock = stocksList.listIterator();
        for(int stockID=0; stockID < stocksList.size(); stockID++) 
        {
            StockData stockData = (StockData) stocksList.get(stockID);
            if(EvaluatedStockSet.notInStockComputedSet(stockData.symbol)) 
            {
                System.out.println();
                System.out.println("stockID = "+stockData.symbol);
                StockEvaluator stockEval = new StockEvaluator();
                stockEval.computeStockParameters(stockData.symbol);
                stockEval.updateStockParameters(stockData.symbol);
                stockData.alpha = stockEval.alpha;
                stockData.beta = stockEval.beta;
                stockData.residue = stockEval.residue;
                stockData.exp_return = stockEval.exp_return;
                stockData.variance = stockEval.variance;
                stockData.sharpe_ratio = stockEval.sharpe_ratio;
            }
            else 
            {
                // System.out.println("Stock " + stockData.symbol + " has been evaluated!");
                DatabaseCommunicator.connect();
                ResultSet rs = null;
                try 
                {
                    String query = "SELECT alpha, beta, residue, exp_return, variance, sharpe_ratio FROM stock WHERE symbol = '" + stockData.symbol + "';";
                    rs = DatabaseCommunicator.query(query);
                    // System.out.println(rs.getFetchSize());
                    while(rs.next()) 
                    {
                        stockData.alpha = rs.getDouble("alpha");
                        stockData.beta = rs.getDouble("beta");
                        stockData.residue = rs.getDouble("residue");
                        stockData.exp_return = rs.getDouble("exp_return");
                        stockData.variance = rs.getDouble("variance");
                        stockData.sharpe_ratio = rs.getDouble("sharpe_ratio");
                    } 
                    DatabaseCommunicator.close();
                }
                catch (Exception e) 
                {
                    System.err.println("Exception: " + e.toString());
                }
            }
        }
        return( true );
    }

    public void updatePortfolioParameters(long investorID) 
    {
        // Need to update the portfolio alpha, beta and risk in investor's profile
        DatabaseCommunicator.connect();
        try 
        {
            DatabaseCommunicator.query("UPDATE investor SET alpha = " + alpha_p + " WHERE investor_id = " + investorID + ";");
            DatabaseCommunicator.query("UPDATE investor SET beta = " + beta_p + " WHERE investor_id = " + investorID + ";");
            DatabaseCommunicator.query("UPDATE investor SET residue = " + risk_p + " WHERE investor_id = " + investorID + ";");
            DatabaseCommunicator.query("UPDATE investor SET exp_return = " + exp_return_p + " WHERE investor_id = " + investorID + ";");
            DatabaseCommunicator.query("UPDATE investor SET variance = " + variance_p + " WHERE investor_id = " + investorID + ";");
            DatabaseCommunicator.query("UPDATE investor SET sharpe_ratio = " + sharpe_ratio + " WHERE investor_id = " + investorID + ";");
            DatabaseCommunicator.close();
        }
        catch (Exception e) 
        {
            System.err.println("Exception: " + e.toString());
        }
    }
            
    void calculatePortfolioParameters() 
    {
        double systemVar = 0.0;

        alpha_p = 0.0;
        beta_p = 0.0;
        risk_p = 0.0;
        exp_return_p = 0.0;
        variance_p = 0.0;
        for(int stockID=0; stockID < stocksList.size(); stockID++) 
        {
            StockData stockData = (StockData) stocksList.get(stockID);
            alpha_p += (stockData.weight*stockData.alpha);
            beta_p += (stockData.weight*stockData.beta);
            exp_return_p += (stockData.weight*stockData.exp_return);
            systemVar += (stockData.weight*stockData.beta);
            risk_p += (Math.pow(stockData.weight,2)*stockData.residue);
        }
        systemVar = Math.pow(systemVar, 2.0)*MarketEvaluator.variance;  
        variance_p = systemVar+risk_p;
    }    
    
    void computeSharpeRatio() 
    {
        sharpe_ratio = (exp_return_p-MarketEvaluator.risk_free_rate)/Math.pow(variance_p, 0.5);
    }
    
    public boolean evaluatePortfolioParameters(long investorID)
    {
        if(evaluateStocksInPortfolio(investorID) == true)
        {
            // Need to calculate the portfolio alpha, beta and risk
            calculatePortfolioParameters();
            computeSharpeRatio();
            updatePortfolioParameters(investorID);
            return( true );
        }
        return( false );
    }
}