import java.sql.*;
import java.util.*;
import java.io.*;
import MDMSupport.BuddyDetails;
import MDMSupport.BuddyTableModel;

public class DBServer extends _DBServerInterfaceImplBase {
//Declare a connecton pool
  ConnectionPool cpool;
  ConnectionPool statCpool;

  /** Construct a persistently named object. */
  public DBServer(java.lang.String name) {
    super(name);
    jbInit();
  }
  /** Construct a transient object. */
  public DBServer() {
    super();
    jbInit();
  }

  private void jbInit() {
    try{
      System.out.println("Retrieving properties...");
      Properties props = getProperties();
      String InitCon = (String)props.get("InitCon");
      String Incr = (String)props.get("Incr");
      String StatInitCon = (String)props.get("StatInitCon");
      String StatIncr = (String)props.get("StatIncr");
      System.out.println("A new connection pool is being generated...");
      cpool = new ConnectionPool((String)props.get("DBConURL"), (String)props.get("UName"), (String)props.get("PWord")
                      , (String)props.get("DClassName"), Integer.parseInt(InitCon), Integer.parseInt(Incr));
      System.out.println("Connection pool generated.");
      System.out.println("A new connection pool dedicated for status is being generated...");
      statCpool = new ConnectionPool((String)props.get("DBConURL"), (String)props.get("UName"), (String)props.get("PWord")
                      , (String)props.get("DClassName"), Integer.parseInt(StatInitCon), Integer.parseInt(StatIncr));
      System.out.println("Connection pool generated.");
    }
    catch(ClassNotFoundException e){
      System.out.println("Couldn't load database driver: " + e.getMessage());
    }
    catch(SQLException e){
      System.out.println("SQLException caught: " + e.getMessage());
    }
  }

  private Properties getProperties(){
    boolean found = true;
		Properties props=null;
    ObjectInputStream oin=null;
    FileInputStream in=null;
		try{
  		in=new FileInputStream("DBProperties.props");
		}
		catch(FileNotFoundException ex){
	  	System.out.println("File not found "+ ex.getMessage());
      try{
        in.close();
      }
      catch(Exception subEx){
        subEx.printStackTrace();
      }
      found = false;
		}
		catch(SecurityException ex){System.out.println("Security exception-I "+ex.getMessage());}
 		if(found){
  		try{
	    	oin=new ObjectInputStream(in);
 			}
 			catch(StreamCorruptedException ex){
        System.out.println("StreamCorruptedException-I "+ex.getMessage());
      }
      catch(IOException ex){
        System.out.println("IOException-I "+ex.getMessage());
      }
      try{
        props=(Properties)oin.readObject();
      }
      catch(ClassNotFoundException ex){
        System.out.println("ClassNotFoundException "+ex.getMessage());
      }
      catch(InvalidClassException ex){
        System.out.println("InvalidClassException "+ex.getMessage());
      }
      catch(StreamCorruptedException ex){
        System.out.println("StreamCorruptedException-II "+ex.getMessage());
      }
      catch(OptionalDataException ex){
        System.out.println("OPtionalDataException "+ex.getMessage());
      }
      catch(IOException ex){
        System.out.println("IOException-II "+ex.getMessage());
      }
 		}
    try{
      oin.close();
    }
    catch(Exception ex){
      ex.printStackTrace();
    }
		return props;
  }
//Operation returns vector formation that contains
//the buddy details
  public BuddyTableModel getBuddyTableModel(
    java.lang.String userName){
    // IMPLEMENT: Operation
    System.out.println("Preparing Buddy Table Model for " + userName);
    BuddyTableModel buddyContainer = new BuddyTableModel();
    Connection con = null;
    try{
      con = cpool.getConnection();
      Statement stmtGetBuddies = con.createStatement();
      PreparedStatement stmtAffiliation = con.prepareStatement("select Affiliation from M_USER mu, M_USER_AFFILIATION muaf where muaf.user_id = mu.user_id and mu.user_id = (?) ORDER BY Affiliation");
      PreparedStatement stmtAssociation = con.prepareStatement("select Association from M_USER mu, M_USER_ASSOCIATION muas where muas.user_id = mu.user_id and mu.user_id = (?) ORDER BY Association");
      PreparedStatement stmtSubSpeciality = con.prepareStatement("select SubSpeciality from M_USER mu, M_USER_SUBSPECIALITY muss where muss.user_id = mu.user_id and mu.user_id = (?) ORDER BY SubSpeciality");
      PreparedStatement stmtSpeciality = con.prepareStatement("select Speciality from M_USER mu, M_USER_SPECIALITY mus where mus.user_id = mu.user_id and mu.user_id = (?) ORDER BY Speciality");
      String qryGetBuddies = "select mu.user_id, prefix_name, first_name, middle_initial, last_name, user_status, location, gender, country from m_user mu, t_user_buddy tub where tub.buddy_id = mu.user_id and tub.user_id = '" + userName + "' ORDER BY user_id";
      ResultSet rsBuddies = stmtGetBuddies.executeQuery(qryGetBuddies);
      while(rsBuddies.next()) {
//Process the buddy
        BuddyDetails buddy = new BuddyDetails(rsBuddies.getString("user_id"), rsBuddies.getString("prefix_name") + " " + rsBuddies.getString("first_name") + " " + rsBuddies.getString("middle_initial") + " " + rsBuddies.getString("last_name"),
                      rsBuddies.getString("user_status"),rsBuddies.getString("gender"),
                      rsBuddies.getString("location"),rsBuddies.getString("country"));
        stmtAffiliation.setString(1, buddy.buddyId);
        ResultSet rsAffiliation = stmtAffiliation.executeQuery();
        while(rsAffiliation.next()){
          buddy.Affiliation.addElement(rsAffiliation.getString("Affiliation"));
        }
        stmtAssociation.setString(1, buddy.buddyId);
        ResultSet rsAssociation = stmtAssociation.executeQuery();
        while(rsAssociation.next()){
          buddy.Association.addElement(rsAssociation.getString("Association"));
        }
        stmtSpeciality.setString(1, buddy.buddyId);
        ResultSet rsSpeciality = stmtSpeciality.executeQuery();
        while(rsSpeciality.next()){
          buddy.Speciality.addElement(rsSpeciality.getString("Speciality"));
        }
        stmtSubSpeciality.setString(1, buddy.buddyId);
        ResultSet rsSubSpeciality = stmtSubSpeciality.executeQuery();
        while(rsSubSpeciality.next()){
          buddy.SubSpeciality.addElement(rsSubSpeciality.getString("SubSpeciality"));
        }
        buddyContainer.put(buddy.buddyId, buddy);
        rsAffiliation.close();
        rsAssociation.close();
        rsSpeciality.close();
        rsSubSpeciality.close();
        rsAffiliation = null;
        rsAssociation = null;
        rsSpeciality = null;
        rsSubSpeciality = null;
      }
      rsBuddies.close();
      stmtAffiliation.close();
      stmtAssociation.close();
      stmtSubSpeciality.close();
      stmtSpeciality.close();
      stmtGetBuddies.close();
      rsBuddies = null;
      stmtAffiliation = null;
      stmtAssociation = null;
      stmtSubSpeciality = null;
      stmtSpeciality = null;
      stmtGetBuddies = null;
    }
    catch(Exception e){
      System.out.println("In exception " + e);
    }
    finally{
      if (con != null){
        cpool.returnConnection(con);
        con = null;
      }
    }
    return buddyContainer;
  }

  public BuddyTableModel getInfoAboutUsersAndAdd(String userName, Vector userIds){
    System.out.println("Processing getInfoAboutUsersAndAdd request for: " + userName);
    this.addBuddies(userName, userIds);
    return getInfoAboutUsers(userIds);
  }

  public boolean isUserOnline(java.lang.String userName) {
  // IMPLEMENT: Operation
    System.out.println("isUserOnline check for: " + userName);
    Connection con = null;
    boolean result = false;
    try{
      con = statCpool.getConnection();
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT user_status FROM M_USER WHERE USER_ID = '" + userName + "'");
      rs.next();
      if(rs.getString("user_status").equalsIgnoreCase("online")) result = true;
      rs.close();
      stmt.close();
      rs = null;
      stmt = null;
    }
    catch(Exception e){
      System.out.println("In exception " + e);
    }
    finally{
      if (con != null){
        statCpool.returnConnection(con);
        con = null;
      }
    }
    return result;
  }


  public java.lang.String getStatus(
    java.lang.String userName
  ) {
    // IMPLEMENT: Operation
    System.out.println("getStatus request for: " + userName);
    Connection con = null;
    String result = "Offline";
    try{
      con = statCpool.getConnection();
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT user_status FROM M_USER WHERE USER_ID = '" + userName + "'");
      rs.next();
      result = rs.getString("user_status");
      rs.close();
      stmt.close();
      rs = null;
      stmt =  null;
    }
    catch(Exception e){
      System.out.println("In exception " + e);
    }
    finally{
      if (con != null){
        statCpool.returnConnection(con);
        con = null;
      }
    }
    return result;
  }

//Operation returns true if the parameter supplied user exists.
  public boolean isUserExisting(
    java.lang.String userName
  ) {
    // IMPLEMENT: Operation
    System.out.println("isUserExisting request for: " + userName);
    Connection con = null;
    boolean result = false;
    try{
      con = cpool.getConnection();
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT USER_NAME FROM M_USER WHERE USER_ID = '" + userName + "'");
      while(rs.next()) result = true;
      rs.close();
      stmt.close();
      rs = null;
      stmt = null;
    }
    catch(Exception e){
      System.out.println("In exception " + e);
    }
    finally{
      if (con != null){
        cpool.returnConnection(con);
        con = null;
      }
    }
    return result;
  }

  public void setStatus(String userName, String status){
    System.out.println("setStatus request for: " + userName + " " + status);
    Connection con = null;
    try{
      con = statCpool.getConnection();
      Statement stmt = con.createStatement();
      int count = stmt.executeUpdate("UPDATE M_USER SET user_status = '" + status + "' where user_id = '" + userName + "'");
      stmt.close();
      stmt = null;
    }
    catch(Exception e){
      System.out.println("In exception " + e);
    }
    finally{
      if (con != null){
        statCpool.returnConnection(con);
        con = null;
      }
    }
  }

  public void addBuddy(String userId, String buddyId){
    System.out.println("addBuddy request for: " + userId);
    Connection con = null;
    try{
      con = cpool.getConnection();
      Statement stmt = con.createStatement();
      int count = stmt.executeUpdate("Insert INTO T_USER_BUDDY(USER_ID, BUDDY_ID) Values ('" + userId + "', '" + buddyId + "')");
      stmt.close();
      stmt = null;
    }
    catch(Exception e){
      System.out.println("In exception " + e);
    }
    finally{
      if (con != null){
        cpool.returnConnection(con);
        con = null;
      }
    }
  }

  public void addBuddies(String userId, Vector buddyIds){
    System.out.println("addBuddies request for: " + userId);
    Enumeration e = buddyIds.elements();
    Connection con = null;
    try{
      con = cpool.getConnection();
      Statement stmt = con.createStatement();
      while (e.hasMoreElements()){
        try{
          int count = stmt.executeUpdate("Insert INTO T_USER_BUDDY(USER_ID, BUDDY_ID) Values ('" + userId + "', '" + (String)e.nextElement() + "')");
        }
        catch(Exception ex){
          System.out.println("In exception " + ex);
        }
      }
      stmt.close();
      stmt = null;
    }
    catch(Exception ex){
      System.out.println("In exception " + ex);
    }
    finally{
      if (con != null){
        cpool.returnConnection(con);
        con = null;
      }
    }
  }

  public void deleteBuddy(String userId, String buddyId){
    System.out.println("deleteBuddy request for: " + userId);
    Connection con = null;
    try{
      con = cpool.getConnection();
      Statement stmt = con.createStatement();
      int count = stmt.executeUpdate("Delete FROM T_USER_BUDDY where USER_ID = '" + userId + "' and BUDDY_ID = '" + buddyId + "'");
      stmt.close();
      stmt = null;
    }
    catch(Exception e){
      System.out.println("In exception " + e);
    }
    finally{
      if (con != null){
        cpool.returnConnection(con);
        con = null;
      }
    }
  }

//Returns a vector of buddy ids
  public Vector search(String Speciality, String SubSpeciality,
            String Affiliation, String Association, String BuddyId){
    System.out.println("Search request being processed");
    Connection con = null;
    Vector result = new Vector();
    String strQuery = "select distinct mu.user_id from m_user mu, M_USER_AFFILIATION muaf, M_USER_ASSOCIATION muas, M_USER_SPECIALITY mus, M_USER_SUBSPECIALITY muss, T_USER_BUDDY tub where mu.user_id = mu.user_id ";
    if(!Speciality.equalsIgnoreCase("<All>")) {
      strQuery = strQuery + " and mu.user_id = mus.user_id and mus.speciality = '" + Speciality + "' ";
    }
    if(!SubSpeciality.equalsIgnoreCase("<All>")) {
      strQuery = strQuery + " and mu.user_id = muss.user_id and muss.subspeciality = '" + SubSpeciality + "' ";
    }
    if(!Affiliation.equalsIgnoreCase("<All>")) {
      strQuery = strQuery + " and mu.user_id = muaf.user_id and muaf.Affiliation = '" + Affiliation + "' ";
    }
    if(!Association.equalsIgnoreCase("<All>")) {
      strQuery = strQuery + " and mu.user_id = muas.user_id and muas.Association = '" + Association + "' ";
    }
    if(!BuddyId.equals("")) {
      strQuery = strQuery + " and mu.user_id LIKE '" + BuddyId + "' ";
    }
    strQuery = strQuery + " ORDER BY user_id";
    try{
      con = cpool.getConnection();
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(strQuery);
      while(rs.next()){
        result.addElement(rs.getString("user_id"));
      }
      rs.close();
      stmt.close();
      rs = null;
      stmt = null;
      strQuery = null;
    }
    catch(Exception e){
      System.out.println("In exception " + e);
    }
    finally{
      if (con != null){
        cpool.returnConnection(con);
        con = null;
      }
    }
    return result;
  }

  public BuddyTableModel getInfoAboutUsers(Vector userIds){
    BuddyTableModel buddyContainer = new BuddyTableModel();
    Connection con = null;
    Enumeration e = userIds.elements();
    try{
      con = cpool.getConnection();
      PreparedStatement stmtGetBuddies = con.prepareStatement("select mu.user_id, user_name, user_status, location, gender, country from m_user mu where mu.user_id = (?) ORDER BY user_id");
      PreparedStatement stmtAffiliation = con.prepareStatement("select Affiliation from M_USER mu, M_USER_AFFILIATION muaf where muaf.user_id = mu.user_id and mu.user_id = (?) ORDER BY Affiliation");
      PreparedStatement stmtAssociation = con.prepareStatement("select Association from M_USER mu, M_USER_ASSOCIATION muas where muas.user_id = mu.user_id and mu.user_id = (?) ORDER BY Association");
      PreparedStatement stmtSubSpeciality = con.prepareStatement("select SubSpeciality from M_USER mu, M_USER_SUBSPECIALITY muss where muss.user_id = mu.user_id and mu.user_id = (?) ORDER BY SubSpeciality");
      PreparedStatement stmtSpeciality = con.prepareStatement("select Speciality from M_USER mu, M_USER_SPECIALITY mus where mus.user_id = mu.user_id and mu.user_id = (?) ORDER BY Speciality");
      while(e.hasMoreElements()){
        stmtGetBuddies.setString(1, (String)e.nextElement());
        ResultSet rsBuddies = stmtGetBuddies.executeQuery();
        while(rsBuddies.next()) {
//Process the buddy
          BuddyDetails buddy = new BuddyDetails(rsBuddies.getString("user_id"), rsBuddies.getString("user_name"),
                        rsBuddies.getString("user_status"), rsBuddies.getString("location"),
                        rsBuddies.getString("gender"),rsBuddies.getString("country"));
          stmtAffiliation.setString(1, buddy.buddyId);
          ResultSet rsAffiliation = stmtAffiliation.executeQuery();
          while(rsAffiliation.next()){
            buddy.Affiliation.addElement(rsAffiliation.getString("Affiliation"));
          }
          stmtAssociation.setString(1, buddy.buddyId);
          ResultSet rsAssociation = stmtAssociation.executeQuery();
          while(rsAssociation.next()){
            buddy.Association.addElement(rsAssociation.getString("Association"));
          }
          stmtSpeciality.setString(1, buddy.buddyId);
          ResultSet rsSpeciality = stmtSpeciality.executeQuery();
          while(rsSpeciality.next()){
            buddy.Speciality.addElement(rsSpeciality.getString("Speciality"));
          }
          stmtSubSpeciality.setString(1, buddy.buddyId);
          ResultSet rsSubSpeciality = stmtSubSpeciality.executeQuery();
          while(rsSubSpeciality.next()){
            buddy.SubSpeciality.addElement(rsSubSpeciality.getString("SubSpeciality"));
          }
          buddyContainer.put(buddy.buddyId, buddy);
          rsAffiliation.close();
          rsAssociation.close();
          rsSpeciality.close();
          rsSubSpeciality.close();
          rsAffiliation = null;
          rsAssociation = null;
          rsSpeciality = null;
          rsSubSpeciality = null;
        }
        rsBuddies.close();
        rsBuddies = null;
      }
      stmtGetBuddies.close();
      stmtAffiliation.close();
      stmtAssociation.close();
      stmtSubSpeciality.close();
      stmtSpeciality.close();
      stmtGetBuddies = null;
      stmtAffiliation = null;
      stmtAssociation = null;
      stmtSubSpeciality = null;
      stmtSpeciality = null;
    }
    catch(Exception ex){
      System.out.println("In exception " + ex);
    }
    finally{
      if (con != null){
        cpool.returnConnection(con);
        con = null;
      }
    }
    return buddyContainer;
  }

  public Vector getUsersForBuddy(String BuddyId){return new Vector();}

  public Vector getCodeValues(String code){
    Connection con = null;
    Vector result = new Vector();
    try{
      con = cpool.getConnection();
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT CODE_VALUE FROM T_CODE_VALUES WHERE CODE_NAME = '" + code + "' ORDER BY CODE_VALUE");
      while(rs.next()){
        result.addElement(rs.getString("CODE_VALUE"));
      }
      rs.close();
      stmt.close();
      rs = null;
      stmt = null;
    }
    catch(Exception e){
      System.out.println("In exception " + e);
    }
    finally{
      if (con != null){
        cpool.returnConnection(con);
        con = null;
      }
    }
    return result;
  }

  public static void main(String[] args){
    try{
      // Initialize the ORB.
      org.omg.CORBA.ORB orb = org.omg.CORBA.ORB.init(args,null);
      // Initialize the BOA.
      org.omg.CORBA.BOA boa = orb.BOA_init();
      // Create the account manager object.
      DBServer manager =
        new DBServer("DBServer");
      // Export the newly created object.
      boa.obj_is_ready(manager);
      System.out.println(manager + " is ready.");
      // Wait for incoming requests
      boa.impl_is_ready();
    }
    catch(Exception e){
      e.printStackTrace();
    }
  }
}