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

/**
   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;

   /**
      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;
   }

   /**
      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 our 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 */

      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
   {
      /* 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())
      {
         if (rslt.getString("alt_req") != null)
         {
            degree_eval = degree_eval + "\n<P><B>" + 
                          rslt.getString("course_num") + 
                          "\n<BR><B>OR \n<BR>" +
                          rslt.getString("alt_req") +
                           "\n<P>";
         }
         else
         {
            degree_eval = degree_eval + "\n<BR><B>" + 
                          rslt.getString("course_num");
         }
      } /* 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();
      degree_eval = degree_eval + (num_opt_degree - rslt.getInt("num_opt")) +
                    ")</H2> \n<P>"; 
      
      /* 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())
      {
            degree_eval = degree_eval + "\n<BR><B>" + 
                          rslt.getString("course_num");

      } /* 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 */


   /**
      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;

               /* 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);

               /* 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 */