package JDBC;

import java.sql.*;

public class JDBC_Students {
	public static void main(String[] args) throws Exception {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception E) {
			System.err.println("Unable to load driver.");
			E.printStackTrace();
		}
		try {
			Connection conn1;
			String dbUrl = "jdbc:mysql://csdb.cs.iastate.edu:3306/db363bbanothu";
			String user = "dbu363bbanothu";
			String password = "KmfE9217";
			conn1 = DriverManager.getConnection(dbUrl, user, password);
			System.out.println("*** Connected to the database ***");

			// Functions to do
			update_student_values( conn1);


		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static void update_student_values(Connection conn1) throws SQLException {
		Statement statement = conn1.createStatement();
		Statement statement_1 = conn1.createStatement();
		Statement statement_2 = conn1.createStatement();

		String sql = "SELECT StudentID, Classification, GPA, CreditHours FROM Student";
		ResultSet outerResultSet = statement.executeQuery(sql);

		while (outerResultSet.next()) {
			int studentId = outerResultSet.getInt("StudentID");
			String classification = outerResultSet.getString("Classification");
			float gpa = outerResultSet.getFloat("GPA");
			int credits = outerResultSet.getInt("CreditHours");

			System.out.println("\nBefore update: " + "Student Id: " + studentId + ", GPA: " + gpa + ", Classification: " + classification + ", CreditHour: " + credits);

			sql = "SELECT StudentID, Grade FROM Enrollment where StudentID=" + studentId;
			ResultSet innerResultSet = statement_1.executeQuery(sql);
			int total_credit_hours = credits;
			float total_gpa = gpa * credits;
			while (innerResultSet.next()) {
				String grade = innerResultSet.getString("Grade");
				total_credit_hours += 3;
				total_gpa += grade_to_number(grade) * 3;
			}
			innerResultSet.close();


			double gpaToBeUpdated = Math.round((total_gpa / total_credit_hours) * 100) / 100.0;
			System.out.println("After update:  " + "Student Id: "+ studentId + ", GPA: " + gpaToBeUpdated + ", Classification: " + get_new_classification(total_credit_hours) + ", CreditHour: " + total_credit_hours);

			sql = "UPDATE Student " + "SET Classification = '" + get_new_classification(total_credit_hours) + "', GPA = " + gpaToBeUpdated + ", CreditHours = " + total_credit_hours + " WHERE StudentID=" + studentId;
			statement_2.executeUpdate(sql);
		}
		outerResultSet.close();

	}

	public static float grade_to_number(String grade) {
		if (grade.equalsIgnoreCase("A")) {
			return (float) 4.0;
		} else if (grade.equalsIgnoreCase("A-")) {
			return (float) 3.66;
		} else if (grade.equalsIgnoreCase("B+")) {
			return (float) 3.33;
		} else if (grade.equalsIgnoreCase("B")) {
			return (float) 3.00;
		} else if (grade.equalsIgnoreCase("B-")) {
			return (float) 2.66;
		} else if (grade.equalsIgnoreCase("C+")) {
			return (float) 2.33;
		} else if (grade.equalsIgnoreCase("C")) {
			return (float) 2.00;
		} else if (grade.equalsIgnoreCase("C-")) {
			return (float) 1.66;
		} else if (grade.equalsIgnoreCase("D+")) {
			return (float) 1.33;
		} else if (grade.equalsIgnoreCase("D")) {
			return (float) 1.00;
		} else {
			return 0;
		}
	}


	public static String get_new_classification(int credits) {
		if (credits < 30) {
			return "Freshman";
		} else if (credits < 60) {
			return "Sophomore";
		} else if (credits < 90) {
			return "Junior";
		} else {
			return "Senior";
		}
	}

}