/*
 * ValueAtRisk.java
 *
 * Created on November 21, 2007, 10:44 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 ValueAtRisk {
    
    /** Creates a new instance of ValueAtRisk */
    public ValueAtRisk() {
    }
    
    static void printValueAtRisk(long investorID,
                                 double portfolioValue,
                                 double rateAtRisk,
                                 double valueAtRisk)
    {
        if(valueAtRisk >= 0.001)
        {
            System.out.println();
            System.out.println("With 95% Confidence Level, the daily maximal loss for Investor " + investorID + " is:");
            System.out.println("Portfolio Value : " + portfolioValue);
            System.out.println("Rate@Risk       : " + rateAtRisk);
            if(valueAtRisk <= portfolioValue)
                System.out.println("Value@Risk      : " + valueAtRisk);
            else
                System.out.println("Value@Risk      : " + portfolioValue);
        }
    }
    
    public static boolean doesInvestorOwnStocks(long investorID) {
            boolean retval = false;
            String query = "SELECT symbol FROM portfolio where investor_id = " + investorID + ";";
            ResultSet resultSet = DatabaseCommunicator.query(query);
            try
            {
                if (resultSet.next()) {
                        retval = true;
                }
            }
            catch (Exception e) {
                    System.err.println("ValueAtRisk Class: " + e.toString());
            }
            return retval;
    }

    public static void calculateValueAtRisk() 
    {
        ResultSet rs = null;
        double value_at_risk_rate = 0.0;
        double value_at_risk_value = 0.0;
        DatabaseCommunicator.connect();
        try 
        {
            String query = "SELECT investor_id, portfolio_value, exp_return, variance FROM investor;";
            rs = DatabaseCommunicator.query(query);
            while(rs.next()) 
            {
                value_at_risk_rate = rs.getDouble("exp_return")-1.645*Math.pow(rs.getDouble("variance"), 0.5);
                value_at_risk_value = -rs.getDouble("portfolio_value")*value_at_risk_rate;
                String query1 = "UPDATE investor SET value_at_risk = " + value_at_risk_value + " WHERE investor_id = " + rs.getInt("investor_id") + ";";
                DatabaseCommunicator.query(query1);
                
                if(doesInvestorOwnStocks(rs.getInt("investor_id")))
                    printValueAtRisk(rs.getInt("investor_id"),
                                     rs.getDouble("portfolio_value"),
                                     value_at_risk_rate,
                                     value_at_risk_value);
            } 
            DatabaseCommunicator.close();
        }
        catch (Exception e)
        {
            System.err.println("Exception: " + e.toString());
        }
    }
}