package trader.db;

import java.util.*;
import java.sql.*;
import trader.*;


public class BrokerModelDbImpl implements BrokerModel {
  ArrayList changeListeners = new ArrayList(10);
  Connection theConnection;
  
  public BrokerModelDbImpl() throws Exception {
    this("localhost");
  }

  public BrokerModelDbImpl(String hostName) throws Exception {
    String  database = "sample";     // --action StockMarket
    String url;
    try {
      //** 1 Register a JDBC driver
      //**   The name of the pointbase driver class is
      //**   "com.pointbase.jdbc.jdbcUniversalDriver"
      Class.forName("com.pointbase.jdbc.jdbcUniversalDriver");
      //** 2 Construct the url string for JDBC access.
      //**   To be flexible use the local variables hostName
      //**   and database to construct the url string 
      url = "jdbc:pointbase://" + hostName +
        "/" + database;
      //** 3 Assign to theConnection attribute a connection
      //**   object obtained by invoking the getConnection
      //**   method on the DriverManager class
      theConnection = DriverManager.getConnection
        (url, "public", "public");
    } catch (Exception e) {
      e.printStackTrace();
      throw e;
    }
  }

  /** ---------------------------------------------------------
   * This is a helper method used by many methods.
   * It returns a Connection object. 
   * In a single threaded environment you can return a single
   * connection object stored in the attribute theConnection.
   * In a multi-threaded environment each thread will require
   * its own connection. A future subclass of this class can
   * override this method and obtain the connection object 
   * from a connection pool.
   */
  protected Connection obtainConnection() throws SQLException{
    // return the theConnection attribute
    return theConnection;
  }

  /** ---------------------------------------------------------
   * This is a helper method used by many methods
   * It returns true if the ssn (ie customer id) is found in 
   * the Customer table
   */
  protected boolean ssnExists(String ssn) {
    try {
      Connection con = obtainConnection();
      Statement stmt = con.createStatement();
      ResultSet result = stmt.executeQuery
        ("SELECT ssn FROM Customer WHERE ssn="+ "'" +ssn+ "'");
      return result.next();
    } catch (SQLException e) {
      System.err.println ("in ssnExists, query for " + ssn + " failed");
      e.printStackTrace();
      return false;
    }
  }
  //modified  by  ourteam 051228
  protected boolean symbolExists(String id, String symbol, int quantity) {
    try {
      Connection con = obtainConnection();
      Statement stmt = con.createStatement();
      ResultSet result = stmt.executeQuery
        ("SELECT SSN FROM Shares WHERE SSN='" + id.trim()
        + "' AND symbol='"+symbol.trim()+ "' AND quantity >=" + quantity);
      return result.next();
    } catch (SQLException e) {
      System.err.println ("in symbolExists, query for " + symbol + " failed");
      e.printStackTrace();
      return false;
    }
  }
//end
// Broker model state change listener registration methods
  /**-----------------------------------------------------------
   * Adds requester to the list of objects to be notified when an
   * object(Customer, Portfolio or Stock) in the broker model 
   * alters state   
   */
  public void addChangeListener(BrokerView bv)
    throws BrokerException {
    //** 1 add bv to changeListeners using the add method   
    changeListeners.add(bv);
  }
  

  /**-----------------------------------------------------------
   * This method notifies all registered BrokerView listeners
   * that a customer object has changed.
   */
  private void fireModelChangeEvent(Customer cust) {
    BrokerView v;
    for (int i=0; i<changeListeners.size(); i++) {
      try {    
        System.out.println("Model:fireModelChangeEvent loop " +i);
        v = (BrokerView) changeListeners.get(i);
        v.handleCustomerChange(cust);
      } catch(Exception e) {
        System.out.println(e.toString());
      }
    }
  }

// Iteration 1 Customer segment broker model methods
// Customer segment state change methods
  /**----------------------------------------------------------
   * Adds the Customer to the broker model 
   */ 
  public void addCustomer(Customer cust) 
    throws BrokerException {
    try {
      String id = null;
      String name = null;
      String addr = null;
      Connection con;
      Statement stmt;
      int rowCount;
      String request;
      //** 1 Assign id with id from cust object
      id = cust.getId();
      //** 2 Assign name with name from cust object
      name = cust.getName();
      //** 3 Assign addr with addr from cust object
      addr = cust.getAddr(); 
      //Q? what is the purpose of the following 3 code lines?
      if (ssnExists(id)) {
        throw new BrokerException("Duplicate id");
      }
      //** 4 Assign con by invoking the obtainConnection()
      //**   for example see similar step in ssnExists method
      con = obtainConnection();
      //** 5 Assign stmt by invoking createStatement on con
      //**   for example see similar step in ssnExists method
      stmt = con.createStatement();
      // The following creates the required INSERT SQL string
      request = 
        "INSERT INTO Customer (ssn, cust_name, address) VALUES ("
        + "'" + id + "'" + ","
        + "'" + name + "'" + ","
        + "'" + addr + "'" + ")";
      //** 6 Assign rowCount with the value returned by invoking 
      //**   executeUpdate(request) method on the stmt object
      rowCount = stmt.executeUpdate(request);
      //** 7 Invoke the fireModelChangeEvent with cust as param.
      //**   Q? why is this step necessary?
      fireModelChangeEvent(cust);
    } catch (Exception e) {
      e.printStackTrace();
      throw new BrokerException("BrokerDbImpl.addCustomer\n" +e);
    }
  }

  /**-------------------------------------------------------------
   * deletes the customer from the broker model
   */
  public void deleteCustomer(Customer cust)
    throws BrokerException{
    try {
      String id = null;
      Connection con;
      Statement stmt;
      int rowCount;
      String request;
      //** 1 Assign id with id from cust object
      id = cust.getId();
      //Q? What is the purpose of the following 4 code lines?
      con = obtainConnection();
      stmt = con.createStatement();
      if (!ssnExists(id)) {
        throw new BrokerException("Record for " + id +
          " not found");
      }
      //** 2 Assign con by invoking the obtainConnection()
      //**   for example see similar step in ssnExists method
      //con = obtainConnection();
      //** 3 Assign stmt by invoking createStatement on con
      //**   for example see similar step in ssnExists method
      //stmt = con.createStatement();
      // The following creates the required DELETE SQL string
      // to delete rows from the Shares table
      request = 
        "DELETE FROM Shares WHERE ssn=" + "'" + id + "'";
      //** 4 Assign rowCount with the value returned by invoking 
      //**   executeUpdate(request) method on the stmt object
      rowCount = stmt.executeUpdate(request);

      // The following creates the required DELETE SQL string
      // to delete the customer from the Customers table
      request = 
        "DELETE FROM Customer WHERE ssn=" + "'" + id + "'";
      //** 5 Assign rowCount with the value returned by invoking 
      //**   executeUpdate(request) method on the stmt object
      rowCount = stmt.executeUpdate(request);
      //** 6 Using the setName method set the string 
      //**   "- customer deleted -" to the name field of the
      //**   cust object . 
      cust.setName("- customer deleted -");
      //** 7 Invoke the fireModelChangeEvent with cust as param.
      fireModelChangeEvent(cust);
    } catch (Exception e) {
      e.printStackTrace();
      throw new BrokerException
        ("BrokerDbImpl.deleteCustomer\n" +e);
    }
  }
  
  //modified  by  ourteam 051228
  //begin
  public void addPortfolio(Portfolio port) 
    throws BrokerException {
    try {
      String id = null;
      String name = null;
      float  price=0;
      int quantity=0;
      Share[] shares = null;
      String  symbol=null;
      //int quantity=0;
      Connection con;
      Statement stmt;
      int rowCount;
      String request=null;
      //System.out.println("database: addPortfolio begin");
      //** 1 Assign id with id from cust object
      id = port.getCustomer().getId();
      //** 2 Assign name with name from cust object
      name = port.getCustomer().getName();
      //** 3 Assign addr with addr from cust object
      System.out.println("id="+id+"name="+name);
      shares = port.getShares();
      System.out.println("shares.length="+shares.length);
      symbol=shares[0].getStock().getSymbol();
      price=shares[0].getStock().getPrice();
      quantity=shares[0].getQuantity();
      //symbol=port.getShare().getStock().getSymbol();
      //quantity = port.getShare().getQuantity();
      //Q? what is the purpose of the following 3 code lines?
      System.out.println("get from shares[0]: symbol ="+symbol+",price="+price);
      con = obtainConnection();
      stmt = con.createStatement();
      if (!ssnExists(id)) {
      	throw  new  BrokerException("you  can't  invest");
      	/*request = 
        "INSERT INTO Shares (ssn,symbol, quantity) VALUES ("
        + "'" + symbol + "'" + ","
        + "'" + price + "" + ","
        + "" + quantity + "" + ")";*/
        //throw new BrokerException("Duplicate id");
      //** 4 Assign con by invoking the obtainConnection()
      //**   for example see similar step in ssnExists method
      //con = obtainConnection();
      //** 5 Assign stmt by invoking createStatement on con
      //**   for example see similar step in ssnExists method
      //stmt = con.createStatement();
      // The following creates the required INSERT SQL string
      //else
      //request = 
       //"INSERT INTO Customer (ssn, cust_name, shares) VALUES ("
      //  + "'" + id + "'" + ","
      //  + "'" + name + "'" + ","
      //  + "'" + shares.getQuantity() + "'" +shares.getStock().getSymbol()+"'"+ ")";
    }
     else if(!symbolExists(id,symbol,quantity)){ 
     /*    request = 
        "INSERT INTO Shares (ssn,symbol, quantity) VALUES ("
        + "'" + symbol + "',"
        + price + ","
        + quantity + ")";*/
        
        request = 
        "INSERT INTO Shares (ssn,symbol, quantity) VALUES ("
        + "'" + id + "',"
        + "'" + symbol + "',"
        + quantity + ")";
      }
     else{
      	 /*
      	 request= "UPDATE Shares SET QUANTITY=QUANTITY+" + quantity
       	 			+ " WHERE SSN='" + id + "' AND SYMBOL='" + symbol + "'";
       	 */
       	 
       	 request= "UPDATE Shares SET QUANTITY=QUANTITY+" + quantity
       	 			+ " WHERE SSN='" + id + "' AND SYMBOL='" + symbol + "'";
      	}
      System.out.println("SQL before:"+request);	
    //request = "";
      //** 6 Assign rowCount with the value returned by invoking 
      //**   executeUpdate(request) method on the stmt object
      rowCount = stmt.executeUpdate(request);
      System.out.println("SQL after:"+request);
      //** 7 Invoke the fireModelChangeEvent with cust as param.
      //**   Q? why is this step necessary?
      fireModelChangeEvent(port.getCustomer());
    } catch (Exception e) {
      e.printStackTrace();
      throw new BrokerException("BrokerDbImpl.addPortfolio\n" +e);
    }
  }

  /**-------------------------------------------------------------
   * deletes the customer from the broker model
   */
  public void deletePortfolio(Portfolio port)
    throws BrokerException{
    try {
      String id = null;
      String symbol=null;
      String  name=null;
      float  price=0;
      Share[] shares = null;
      int  quantity=0;
      id = port.getCustomer().getId();
      name = port.getCustomer().getName();
      shares = port.getShares();
      symbol=shares[0].getStock().getSymbol();
      price=shares[0].getStock().getPrice();
      quantity=shares[0].getQuantity();
      Connection con;
      Statement stmt;
      int rowCount;
      String request=null;
      //** 1 Assign id with id from cust object
      //id = port.getCustomer().getId();
      //symbol = port.getShare().getStock().getSymbol();
      //quantity = port.getShare().getQuantity();
      //first id, symbol exist?
      //second symbol's number > quantity?
      //third sell symbol
      
      con = obtainConnection();
      stmt = con.createStatement();
      //Q? What is the purpose of the following 4 code lines?
      if (!symbolExists(id, symbol, quantity)) {
        throw new BrokerException("Record for " + id + "'s " + symbol +
          " not found");
      } else {
      //modified by ourteam 051228
      //begin
       	request= "UPDATE Shares SET QUANTITY=QUANTITY-" + quantity
       	 			+ " WHERE SSN='" + id + "' AND SYMBOL='" + symbol + "'";
      	rowCount = stmt.executeUpdate(request);
      }
      //end
      //request = 
        //"DELETE FROM Shares WHERE ssn=" + "'" + id + "'";
      //** 4 Assign rowCount with the value returned by invoking 
      //**   executeUpdate(request) method on the stmt object
      //rowCount = stmt.executeUpdate(request);

      // The following creates the required DELETE SQL string
      // to delete the customer from the Customers table
      //request = 
	       // "DELETE FROM Customer WHERE ssn=" + "'" + id + "'";
      //** 5 Assign rowCount with the value returned by invoking 
      //**   executeUpdate(request) method on the stmt object
      //** 6 Using the setName method set the string 
      //**   "- customer deleted -" to the name field of the
      //**   cust object . 
      //** 7 Invoke the fireModelChangeEvent with cust as param.
      //fireModelChangeEvent(port.getCustomer());
    } catch (Exception e) {
      e.printStackTrace();
      throw new BrokerException
        ("BrokerDbImpl.deletePortfolio\n" +e);
    }
  }
  
  //end


  /**-------------------------------------------------------------
   * Updates the customer in the broker model
   */
  public void updateCustomer(Customer cust)
    throws BrokerException {
    try {
      String id = null;
      String name = null;
      String addr = null;
      Connection con;
      Statement stmt;
      String request;
      int rowCount;
      //** 1 Assign id with id from cust object
      id = cust.getId();
      //** 2 Assign name with name from cust object
      name = cust.getName();
      //** 3 Assign addr with addr from cust object
      addr = cust.getAddr(); 
      //** Q? What is the purpose of the following 4 code lines
      if (!ssnExists(id)) {
        throw new BrokerException("Record for " + id +
          " not found");