package csis626;
import java.rmi.*;
import java.rmi.server.*;
import java.sql.*;
import java.util.*;

/**
   Remote Class for DB which contains methods to extract data
   regarding a student from the MySQL database GRAD_DEGREE
*/
public class DB extends UnicastRemoteObject implements DBInterface 
{
   private String stud_id;
   private String password;
   private String degree;
   private String personal_info;
   private String transcript_cnum;
   private String transcript_date;
   private String degree_eval;
   private String requirements;
   private Connection conn;
   private Statement stat, stat2;
   private ResultSet result;
   private boolean valid_user;
   private int num_opt_degree;
   private Vector courses_not_taken;
   private Vector prereq_courses;
   private Vector planned_courses;
   private Vector rem_req_courses;
   private Vector rem_opt_courses;

   /**
      Constructs DB using an anonymous port
   */
   public DB() throws RemoteException
   {
      /* Implicit call to superclass constructor */
   }

   /**
      Constructs DB using the supplied port
   */
   public DB(int port) throws RemoteException
   {
      super(port);
   }
   
   /**
      Returns the HTML string represention of Student's Personal Info
      @return the HTML string representation of Student's Personal Info
   */
   public String getPersonalInfo() throws RemoteException
   {
      return personal_info;
   }

   /**
      Returns the Student's Degree
      @return the Student's Degree
   */
   public String getDegree() throws RemoteException
   {
      return degree;
   }

   /**
      Returns the HTML string represention of Student's Degree Evaluation
      @return the HTML string representation of Student's Degree Evaluation
   */
   public String getDegreeEval() throws RemoteException
   {
      return degree_eval;
   }

   /**
      Returns the HTML string represention of Student's Transcript 
      sorted by course number
      @return the HTML string representation of Student's Transcript
      sorted by course number
   */
   public String getTranscript_cnum() throws RemoteException
   {
      return transcript_cnum;
   }

   /**
      Returns the HTML string represention of Student's Transcript 
      sorted by date
      @return the HTML string representation of Student's Transcript
      sorted by date
   */
   public String getTranscript_date() throws RemoteException
   {
      return transcript_date;
   }

   /**
      Returns the HTML string represention of Student's GPS Requirements
      @return the HTML string representation of Student's GPS Requirements
   */
   public String getRequirements() throws RemoteException
   {
      return requirements;
   }

   /**
      Returns the vector of courses with their prerequisites
      @return the vector of courses with their prerequisites
   */
   public Vector getPrereq() throws RemoteException
   {
      return prereq_courses;
   }

   /**
      Returns the vector of courses which have been planned
      @return the vector of courses which have been planned
   */
   public Vector getPlanned() throws RemoteException
   {
      return planned_courses;
   }

   /**
      Returns the vector of courses not taken
      @return the vector of courses not taken
   */
   public Vector getNotTaken() throws RemoteException
   {
      return courses_not_taken;
   }

   /**
      Returns the vector of remaining required courses to take
      @return the vector of remaining required courses to take
   */
   public Vector getRemainReqCourses() throws RemoteException
   {
      return rem_req_courses;
   }

   /**
      Returns the vector of optional courses that have 
      not been taken
      @return the vector of optional courses that have
      not been taken
   */
   public Vector getRemainOptCourses() throws RemoteException
   {
      return rem_opt_courses;
   }


   /**
      Saves the vector input_courses
      @param input_courses
   */
   public void saveCourses(Vector input_courses) throws RemoteException
   {
      int i;
      Vector row_vec1;

      try
      {
         /* Initialize database driver, url, username, and password */
         SimpleDataSource.init("database.properties");

         /* Connect to search_db database */
         conn = SimpleDataSource.getConnection();
	 
         /* Create SQL statement object */
         stat = conn.createStatement();

         /* Drop all the planned courses for this student */
         stat.executeUpdate("DELETE FROM CoursesPlanned WHERE stud_id='" +
                           stud_id + "';");

         for (i = 0; i < input_courses.size(); i+=3)
         {            
            row_vec1 = (Vector) input_courses.elementAt(i+1);
            stat.executeUpdate("INSERT INTO CoursesPlanned VALUES('" +
                              stud_id + "', '" + 
                              input_courses.elementAt(i) + "', '" +
                              row_vec1.elementAt(0) + "', '" +
                              row_vec1.elementAt(1) + "');"
                             );
         }

         result.close();
         stat.close();

	      if (conn != null) 
	         conn.close();
      }
      catch (Exception e)
      {
        System.out.println("Exception occurred:" + e);
      }
   } /* End method saveCourses */

   /**
      Parses through the ResultSet object for Student's
      Personal Info and converts it into a String HTML
      representation
   */
   private void parsePersonalInfo(ResultSet rslt) throws SQLException
   {
      degree = rslt.getString("degree");

      personal_info = "\n<P><B>Name: </B>" + 
                      rslt.getString("first_name") + " " +
                      rslt.getString("middle_name") + " " + 
                      rslt.getString("last_name") +
                      "\n<P><B>Student ID: </B>" + stud_id +
                      "\n<P><B>Password: </B>" + password + 
                      "\n<P><B>Address: </B>" + 
                      rslt.getString("address") + ", " + 
                      rslt.getString("city") + " " + 
                      rslt.getString("zip") +
                      "\n<P><B>Phone: </B>" + 
                      rslt.getString("phone") +
                      "\n<P><B>E-mail: </B>" + 
                      rslt.getString("email") +
                      "\n<P><B>Degree: </B>" +
                      degree;

   } /* End method parsePersonalInfo */

   /**
      Parses through the ResultSet object for Student's
      Transcript and converts it into a String HTML
      representation
   */
   private void parseTranscript(Statement stmt, ResultSet rslt) throws SQLException
   {
      transcript_cnum = "\n<TABLE BORDER=\"1\" CELLPADDING=\"7\" " +
                        "BORDERCOLOR=\"#800080\">" +
                        "\n<TR>" +
                        "\n<TD BGCOLOR=\"#D8BFD8\"><B>Course Number</B></TD>" +
                        "\n<TD BGCOLOR=\"#D8BFD8\"><B>Semester</B></TD>" +
                        "\n<TD BGCOLOR=\"#D8BFD8\"><B>Year</B></TD>" +
                        "\n<TD BGCOLOR=\"#D8BFD8\"><B>Grade</B></TD>" +
                        "\n</TR>";

      transcript_date = transcript_cnum;

      /* Pull out Courses Taken by Course Number */
      rslt = stmt.executeQuery("SELECT course_num, semester, year, grade " +
                                "FROM CoursesTaken WHERE stud_id='" +
                                stud_id + "' ORDER BY course_num ASC;");

      /* Loop through all rows */
      while (rslt.next())
      {
         transcript_cnum = transcript_cnum + "\n<TR> \n<TD>" + 
                           rslt.getString("course_num") + 
                           "</TD> \n<TD>" +
                           rslt.getString("semester") +
                           "</TD> \n<TD>" +
                           rslt.getString("year") +
                           "</TD> \n<TD>" +
                           rslt.getString("grade") +
                           "</TD> \n</TR>";

      } /* End while (rslt.next()) loop */

      /* Pull out Courses Taken by Date */
      rslt = stmt.executeQuery("SELECT course_num, semester, year, grade " +
                                "FROM CoursesTaken WHERE stud_id='" +
                                stud_id + "' ORDER BY year ASC;");

      /* Loop through all rows */
      while (rslt.next())
      {
            transcript_date = transcript_date + "\n<TR> \n<TD>" + 
                              rslt.getString("course_num") + 
                              "</TD> \n<TD>" +
                              rslt.getString("semester") +
                              "</TD> \n<TD>" +
                              rslt.getString("year") +
                              "</TD> \n<TD>" +
                              rslt.getString("grade") +
                              "</TD> \n</TR>";

      } /* End while (rslt.next()) loop */

      transcript_cnum = transcript_cnum + "\n</TABLE>";
      transcript_date = transcript_date + "\n</TABLE>";

   } /* End method parseTranscript */

   /**
      Parses through the ResultSet object for Student's
      Degree Evaluation and converts it into a String HTML
      representation
   */
   private void parseDegreeEval(Statement stmt, ResultSet rslt) throws SQLException
   {
      int i;
      int tempi;
      int index;
      Vector temp_vec;
      Vector row_vec1, row_vec2; 
      String prereq_string;     
      String prereq_string2;     


      /* Parse required courses not taken */
      degree_eval = "\n<H2>Required Classes NOT Taken</H2> \n<P>";

      rslt = stmt.executeQuery("SELECT R.course_num, R.alt_req " +
                                "FROM Requirements R LEFT JOIN " +
                                "CoursesTaken C ON R.course_num=C.course_num " +
                                "AND C.stud_id='" + stud_id +
                                "' WHERE C.semester IS NULL AND " +
                                "R.degree='" + degree + 
                                "' AND R.type='R'");

      /* Loop through all rows */
      while (rslt.next())
      {
         /* Vectorize remaining required courses to take */
         rem_req_courses.addElement(rslt.getString("course_num"));
         temp_vec = new Vector();

         /* Pull out prerequisites for this course */
         index = prereq_courses.indexOf(rslt.getString("course_num"));
         row_vec1 = (Vector) prereq_courses.elementAt(index + 1);
         prereq_string = "";
         prereq_string2 = "";

         for (i = 0; i < row_vec1.size(); i++)
         {
            row_vec2 = (Vector) row_vec1.elementAt(i);

            if (row_vec2.size() == 1)
            {
               prereq_string = prereq_string +
                               "<BR>&nbsp;&nbsp;&nbsp;&nbsp;Prereq: " + 
                               row_vec2.elementAt(0);
            }

            /* Has alternate prerequisite */
            if (row_vec2.size() == 2)
            {
               prereq_string = prereq_string +
                               "<BR>&nbsp;&nbsp;&nbsp;&nbsp;Prereq: " + 
                               row_vec2.elementAt(0) + 
                               "OR " + row_vec2.elementAt(1);
            }
         }
          
         if (rslt.getString("alt_req") != null)
         {

            /* Pull out prerequisites for this alternate requirement course */
            index = prereq_courses.indexOf(rslt.getString("alt_req"));

            row_vec1 = (Vector) prereq_courses.elementAt(index + 1);

            for (i = 0; i < row_vec1.size(); i++)
            {
               prereq_string2 = prereq_string2 + "<BR>&nbsp;&nbsp;&nbsp;&nbsp;Prereq: ";
               row_vec2 = (Vector) row_vec1.elementAt(i);

               if (row_vec2.size() == 1)
                  prereq_string2 = prereq_string2 + row_vec2.elementAt(0);

               /* Has alternate prerequisite */
               if (row_vec2.size() == 2)
               {
                  prereq_string2 = prereq_string2 + row_vec2.elementAt(0) + 
                                  "OR " + row_vec2.elementAt(1);
               }
            }

            degree_eval = degree_eval + "\n<P><B>" + 
                          rslt.getString("course_num") + 
                          "</B>" + prereq_string +
                          "\n<BR><B>OR \n<BR>" +
                          rslt.getString("alt_req") +
                          "</B>" + prereq_string2 + 
                           "\n<P>";

            temp_vec.addElement(rslt.getString("alt_req"));
         }
         else
         {
            degree_eval = degree_eval + "\n<BR><B>" + 
                          rslt.getString("course_num") +
                          "</B>" + prereq_string;
         }

         rem_req_courses.addElement(temp_vec);

      } /* End while (rslt.next()) loop */

      /* Find out number of optional courses that still need to be taken */   
      degree_eval = degree_eval + "\n<H2>Optional Classes NOT Taken (take ";

      /* Find out number of optional courses student has taken */   
      rslt = stmt.executeQuery("SELECT COUNT(*) AS num_opt FROM " +
                               "CoursesTaken C, Requirements R WHERE " +
                               "C.stud_id='" + stud_id +
                               "' AND C.course_num=R.course_num " +
                               "AND R.type='O' AND R.degree='" +
                               degree + "';");
      rslt.next();

      /* Calculate remaining number of optional courses needed to take */
      tempi = num_opt_degree - rslt.getInt("num_opt");
      if (tempi < 0)
         tempi = 0;

      degree_eval = degree_eval + tempi + ")</H2> \n<P>"; 

      /* Vectorize remaining optional courses that can be taken        */
      /* First element is the remaining # of optional courses required */
      rem_opt_courses.addElement(Integer.toString(tempi));

      /* Parse optional courses not taken */   
      rslt = stmt.executeQuery("SELECT R.course_num " +
                                "FROM Requirements R LEFT JOIN " +
                                "CoursesTaken C ON " +
                                "R.course_num=C.course_num AND " +
                                "C.stud_id='" + stud_id +
                                "' WHERE C.semester IS NULL " +
                                "AND R.degree='" + degree +                                 
                                "' AND R.type='O';");

      /* Loop through all rows */
      while (rslt.next())
      {

         /* Pull out prerequisites for this course */
         index = prereq_courses.indexOf(rslt.getString("course_num"));
         row_vec1 = (Vector) prereq_courses.elementAt(index + 1);
         prereq_string = "";

         for (i = 0; i < row_vec1.size(); i++)
         {
            row_vec2 = (Vector) row_vec1.elementAt(i);

            if (row_vec2.size() == 1)
            {
               prereq_string = prereq_string + 
                               "<BR>&nbsp;&nbsp;&nbsp;&nbsp;Prereq: " + 
                               row_vec2.elementAt(0);
            }

            /* Has alternate prerequisite */
            if (row_vec2.size() == 2)
            {
               prereq_string = prereq_string + 
                               "<BR>&nbsp;&nbsp;&nbsp;&nbsp;Prereq: " + 
                               row_vec2.elementAt(0) + 
                               " OR " + row_vec2.elementAt(1);
            }
         }

         rem_opt_courses.addElement(rslt.getString("course_num"));
         degree_eval = degree_eval + "\n<BR><B>" + 
                       rslt.getString("course_num") +
                       "</B>" + prereq_string;

      } /* End while (rslt.next()) loop */
      
   } /* End method parseDegreeEval */


   /**
      Parses through the ResultSet object for Student's
      GPS Requirements and converts it into a String HTML
      representation
   */
   private void parseRequirements(Statement stmt, Statement stmt2, 
                                  ResultSet rslt) throws SQLException
   {
      ResultSet temp_rslt;
      String alt_course;

      /* Parse required courses */   
      requirements = "\n<H2>Required Classes</H2> \n<P>";

      rslt = stmt.executeQuery("SELECT R.course_num, R.alt_req, C.title " +
                                "FROM Requirements R, Courses C WHERE " +
                                "R.degree='" + degree + 
                                "' AND R.type='R' " +
                                "AND R.course_num=C.course_num;");

      /* Loop through all rows */
      while (rslt.next())
      {
         if (rslt.getString("alt_req") != null)
         {
            alt_course = rslt.getString("alt_req");

            /* Retrieve title of alternate course */
            temp_rslt = stmt2.executeQuery("SELECT title FROM Courses WHERE " +
                                          "course_num='" + alt_course + "';");
            /* Move to first row */
            temp_rslt.next();

            requirements = requirements + "\n<P><B>" + 
                           rslt.getString("course_num") + 
                           ":</B> " +
                           rslt.getString("title") +
                           "\n<BR><B>OR \n<BR>" +
                           rslt.getString("alt_req") +
                           ":</B> " +
                           temp_rslt.getString("title") + 
                           "\n<P>";

            temp_rslt.close();
         }
         else
         {
            requirements = requirements + "\n<BR><B>" + 
                           rslt.getString("course_num") + 
                           ":</B> " +
                           rslt.getString("title");
         }
      } /* End while (rslt.next()) loop */

      requirements = requirements + "\n<H2>Optional Classes (take ";

      /* Find out number of optional courses need to be taken */   
      rslt = stmt.executeQuery("SELECT num_opt FROM Programs WHERE degree='" +
                               degree + "';");
      rslt.next();

      num_opt_degree = rslt.getInt("num_opt");

      requirements = requirements + num_opt_degree + ")</H2> \n<P>";

      /* Parse optional courses */   
      rslt = stmt.executeQuery("SELECT R.course_num, C.title " +
                                "FROM Requirements R, Courses C WHERE " +
                                "R.degree='" + degree + 
                                "' AND R.type='O' " +
                                "AND R.course_num=C.course_num;");

      /* Loop through all rows */
      while (rslt.next())
      {
            requirements = requirements + "\n<BR><B>" + 
                           rslt.getString("course_num") + 
                           ":</B> " +
                           rslt.getString("title");

      } /* End while (rslt.next()) loop */

   } /* End method parseRequirements */

   /**
      Parses through the ResultSet object for Prerequisite
      for classes not taken.
   */
   private void parsePreReqs(Statement stmt, ResultSet rslt) throws SQLException
   {
      int i;
      int date;
      String course_num;
      String sem_str;
      Vector row_vec1, row_vec2;

      /* Pull out all classes not taken */
      rslt = stmt.executeQuery("SELECT R.course_num, R.alt_req " +
                                "FROM Requirements R LEFT JOIN " +
                                "CoursesTaken C ON " +
                                "R.course_num=C.course_num AND " +
                                "C.stud_id='" + stud_id +
                                "' WHERE C.semester IS NULL " +
                                "AND R.degree='" + degree + "' " +
                                "ORDER BY course_num ASC;");

      /* Loop through all rows */
      while (rslt.next())
      {
         /* Vectorize classes not taken */   
         courses_not_taken.addElement(rslt.getString("course_num"));

         if (rslt.getString("alt_req") != null)
         {
            /* Vectorize classes not taken */   
            courses_not_taken.addElement(rslt.getString("alt_req"));
         }
      } /* End while (rslt.next()) loop */

      /* Pull out Planned classes */
      rslt = stmt.executeQuery("SELECT course_num, semester, year " +
                                "FROM CoursesPlanned WHERE stud_id='" +
                                stud_id + "' ORDER BY course_num ASC;");

      /* Loop through all rows */
      while (rslt.next())
      {
         planned_courses.addElement(rslt.getString("course_num"));

         row_vec1 = new Vector();
         row_vec1.addElement(rslt.getString("semester"));
         row_vec1.addElement(rslt.getString("year"));
         planned_courses.addElement(row_vec1);

         date = Integer.parseInt(rslt.getString("year"));
         date *= 10;
         sem_str = rslt.getString("semester");
         if (sem_str.equals("Spring"))
            date += 1;
         else if (sem_str.equals("Summer"))
            date += 2;
         else if (sem_str.equals("Fall"))
            date += 3;
         else if (sem_str.equals("Winter"))
            date += 4;
         planned_courses.addElement(Integer.toString(date));

      } /* End while (rslt.next()) loop */

      /* Pull out Prerequisites for Courses NOT Taken */
      for (i = 0; i < courses_not_taken.size(); i++)
      {
         course_num = (String) courses_not_taken.elementAt(i);

         rslt = stmt.executeQuery("SELECT course_num, prereq, alt_prereq " +
                                   "FROM Prerequisites WHERE course_num='" +
                                   course_num + "';");

         /* Place course to be checked for prereqs in vector */
         prereq_courses.addElement(course_num);
         /* For placing prereqs */
         row_vec1 = new Vector();
         row_vec2 = new Vector();

         if (rslt.next())
         {
            if ( rslt.getString("prereq") != null )
               row_vec2.addElement(rslt.getString("prereq"));

            if ( rslt.getString("alt_prereq") != null )
               row_vec2.addElement(rslt.getString("alt_prereq"));

            /* Check for more prereqs for this course */
            while (rslt.next())
            {               
               row_vec1.addElement(row_vec2);
               row_vec2 = new Vector();

               if ( rslt.getString("prereq") != null )
                  row_vec2.addElement(rslt.getString("prereq"));

               if ( rslt.getString("alt_prereq") != null )
                  row_vec2.addElement(rslt.getString("alt_prereq"));
            }
         }
         row_vec1.addElement(row_vec2);
         prereq_courses.addElement(row_vec1);
      }
     
   } /* End method parsePreReqs */

   /**
      Authenticate the user based on the supplied id and password
      @return the boolean indicating if user is valid
   */
   public boolean isValidUser(String id, String pw) throws RemoteException
   {
      try
      {
         /* Initialize database driver, url, username, and password */
         SimpleDataSource.init("database.properties");

         /* Connect to search_db database */
         conn = SimpleDataSource.getConnection();
	 
         /* Create SQL statement object */
         stat = conn.createStatement();
         stat2 = conn.createStatement();

         /* Retrieve password from database */
         result = stat.executeQuery("SELECT * FROM Students WHERE stud_id='" +
                                    id + "';");

         /* Student ID exists in database */
         if (result.next())
         {
            /* Authenticate user */
            if (pw.equals(result.getString("password")))
            {
               stud_id = id;
               password = pw;
               valid_user = true;

               courses_not_taken = new Vector();
               prereq_courses = new Vector();
               planned_courses = new Vector();
               rem_req_courses = new Vector();
               rem_opt_courses = new Vector();

               /* Pull out Student's Personal Info */
               parsePersonalInfo(result);

               /* Pull out Student's Transcript (the Courses Taken) */
               parseTranscript(stat, result);

               /* Pull out Student's GPS Requirements       */
               /* Always run this prior to parseDegree Eval */               
               parseRequirements(stat, stat2, result);

               /* Find out Prerequisites for classes not taken */
               parsePreReqs(stat, result);

               /* Pull out Student's unsatisfied requirements */
               parseDegreeEval(stat, result);
            } 
            else
            {
               valid_user = false;
            }
         }
         else
         {
            valid_user = false;
         } /* End result.next() */

         result.close();
         stat.close();
         stat2.close();

	      if (conn != null) 
	         conn.close();
      }
      catch (Exception e)
      {
        System.out.println("Exception occurred:" + e);
      }

      return valid_user;

   } /* End method isValidUser */

} /* End class DB */