package proj2;

import java.sql.*;



public class P3 {

	public static void main(String[] args) throws Exception {
		// Load and register a JDBC driver
		try {
			// Load the driver (registers itself)
			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception E) {
			System.err.println("Unable to load driver.");
			E.printStackTrace();
		}
		try {
			// Connect to the database
			Connection conn1;
			String dbUrl = "jdbc:mysql://csdb.cs.iastate.edu:3306/db363schultz1";
			String user = "dbu363schultz1";
			String password = "IxjD1867";
			conn1 = DriverManager.getConnection(dbUrl, user, password);
			System.out.println("*** Connected to the database ***");

			// Create Statement and ResultSet variables to use throughout the
			// project
			Statement statement = conn1.createStatement();
			ResultSet rs;

//			rs = statement.executeQuery("drop table Person");
//			rs = statement.executeQuery("drop table Instructor");
//			rs = statement.executeQuery("drop table Student");
//			rs = statement.executeQuery("drop table Course");
//			rs = statement.executeQuery("drop table Enrollment");
//			rs = statement.executeQuery("drop table Offering");
//
//			rs = statement.executeQuery("create table Person (" + " "
//					+ "Name char (20)," + " "
//					+ "ID char (9) not null," + " "
//					+ "Address char (30)," + " "
//					+ "DOB date," + " "
//					+ "Primary key (ID)");
//
//			rs = statement.executeQuery("create table Instructor (" + " "
//					+ "InstructorID char (9) not null references Person (ID)," + " "
//					+ "Rank char (12)," + " "
//					+ "Salary int," + " "
//					+ "primary key (InstructorID))");
//
//			rs = statement.executeQuery("create table Student ("  + " "
//					+ "StudentID char (9) not null references Person (ID),"  + " "
//					+ "Classification char (10),"  + " "
//					+ "GPA double,"  + " "
//					+ "MentorID char (9) references Instructor (InstructorID),"  + " "
//					+ "CreditHours int,"  + " "
//					+ "Primary key (StudentID))");
//
//			rs = statement.executeQuery("create table Course (" + " "
//					+ "CourseCode char (6) not null," + " "
//					+ "CourseName char (50)," + " "
//					+ "PreReq char (6))");
//
//			rs = statement.executeQuery("create table Offering (" + " "
//					+ "CourseCode char (6) not null references Course (CourseCode)," + " "
//					+ "SectionNo int not null," + " "
//					+ "InstructorID char (9) not null references Instructor (InstructorID)," + " "
//					+ "Primary key (CourseCode, SectionNo))");
//
//			rs = statement.executeQuery("create table Enrollment (" + " "
//					+ "CourseCode char(6) NOT NULL," + " "
//					+ "SectionNo int NOT NULL," + " "
//					+ "StudentID char(9) NOT NULL references Student,"  + " "
//					+ "Grade char(4) NOT NULL," + " "
//					+ "primary key (CourseCode, StudentID),"  + " "
//					+ "foreign key (CourseCode, SectionNo) references Offering(CourseCode, SectionNo))");
//
//			System.out.println("it made it this far");



			rs = statement.executeQuery("select S.StudentID, S.GPA, S.CreditHours, E.Grade" + " "
					+ "from Student S, Enrollment E" + " "
					+ "where S.StudentID = E.StudentID");
			double gradeValue = 0;

			while(rs.next()){

				switch(rs.getString("Grade").trim()){
				case "A":	//do things
					gradeValue = 4.0;
					break;
				case "A-":
					gradeValue = 3.66;
					break;
				case "B+":
					gradeValue = 3.33;
					break;
				case "B":
					gradeValue = 3.0;
					break;
				case "B-":
					gradeValue = 2.66;
					break;
				case "C+":
					gradeValue = 2.33;
					break;
				case "C":
					gradeValue = 2.0;
					break;
				case "C-":
					gradeValue = 1.66;
					break;
				case "D+":
					gradeValue = 1.33;
					break;
				case "D":
					gradeValue = 1.00;
					break;
				case "F":
					gradeValue = 0.0;
					break;
				}


				int newCreditHours = rs.getInt("CreditHours") + 3;
				double newGPA = ((rs.getDouble("GPA") * rs.getInt("CreditHours")) + (gradeValue * 3)) / (newCreditHours);
				newGPA = Math.round(newGPA*100)/100.0;

				String newClassification = "";
				if(newCreditHours < 30){
					newClassification = "Freshman";
				}
				else if(newCreditHours < 60){
					newClassification = "Sophomore";
				}
				else if(newCreditHours < 90){
					newClassification = "Junior";
				}
				else{
					newClassification = "Senior";
				}




//				PreparedStatement stmt2 =
//						conn1.prepareStatement ("update Emp" + " " +
//						"set DName=? , Salary=?" + " " +
//						"where Name = ? " );
//						// Execute stmt2: Move Hari to Shoes with a salary of $65,000:
//						stmt2.setString(1,"Shoes");
//						stmt2.setInt(2,65000);
//						stmt2.setString(3,"Hari");
//						stmt2.executeUpdate();
				PreparedStatement stmt2 =
						conn1.prepareStatement("update Student" + " "
								+ "set GPA=?, Classification=?, CreditHours=?" + " "
								+ "where StudentID=?"
								);
						stmt2.setDouble(1, newGPA);
						stmt2.setString(2, newClassification);
						stmt2.setInt(3, newCreditHours);
						stmt2.setString(4, rs.getString("StudentID"));
						stmt2.executeUpdate();

			}


			ResultSet rs2;
//			rs2 = statement.executeQuery("select S.StudentID, S.GPA, S.CreditHours, E.Grade" + " "
//					+ "from Student S, Enrollment E" + " "
//					+ "where S.StudentID = E.StudentID");
			rs2 = statement.executeQuery("select S.StudentID, S.MentorID, S.GPA" + " "
					+ "from Student S" + " "
					+ "where S.Classification = 'Senior'" + " "
					+ "order by S.GPA desc"
					);
			double lowestOfHigh = 0;
			rs2.next();
			for(int i=0; i<5; i++){
				System.out.println(rs2.getString("StudentID") + " " + rs2.getString("MentorID") + " " + rs2.getDouble("GPA"));
				lowestOfHigh = rs2.getDouble("GPA");
				rs2.next();
			}
			while(rs2.getDouble("GPA") == lowestOfHigh){
				System.out.println(rs2.getString("StudentID") + " " + rs2.getString("MentorID") + " " + rs2.getDouble("GPA"));
				rs2.next();
			}


//			// get salaries of all instructors
//			rs = statement.executeQuery("select * from Instructor f");
//
//			int totalSalary = 0;
//			int salary;
//
//			while (rs.next()) {
//				// get value of salary from each tuple
//				salary = rs.getInt("Salary");
//				totalSalary += salary;
//			}
//			System.out.println("Total Salary of all faculty: " + totalSalary);











			// Close all statements and connections
			statement.close();
			rs.close();
			conn1.close();

		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
	}

}