package JDBC;

import java.sql.*;

public class Example {
	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;
			Statement statement = null;
			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 ***");

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

			// Extract data from result set.. executing once for each student
			while (outerResultSet.next()) {

				// Retrieve Each Student record's individual columns
				int studentId = outerResultSet.getInt("StudentID");
				String classification = outerResultSet.getString("Classification");
				float gpa = outerResultSet.getFloat("GPA");
				int creditHour = outerResultSet.getInt("CreditHours");

				// Display values before updating
				System.out.println("\nBefore updating:");
				System.out.print("Student Id: " + studentId);
				System.out.print(", GPA: " + gpa);
				System.out.print(", Classification: " + classification);
				System.out.println(", CreditHour: " + creditHour);

				// select enrollment record for this particualr student
				sql = "SELECT StudentID, Grade FROM Enrollment where StudentID=" + studentId;
				ResultSet innerResultSet = statement_1.executeQuery(sql);

				int totalCreditHours = creditHour;
				float gpaSum = gpa * creditHour;
				while (innerResultSet.next()) {
					String grade = innerResultSet.getString("Grade");
					totalCreditHours += 3;
					gpaSum += grade_to_number(grade) * 3;
				}

				// calculate final gpa by dividing the sum by total credits
				float x = gpaSum / totalCreditHours;
				double gpaToBeUpdated = Math.round(x * 100) / 100.0;

				System.out.print(gpaToBeUpdated + " ");
				System.out.print(totalCreditHours + " ");
				System.out.print(studentId + " ");
				System.out.print(get_new_classification(totalCreditHours) + " ");

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


				innerResultSet.close();

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

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

	/*
	 * Function which returns classification value based on the total credits of
	 * student
	 */
	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";
		}
	}

}