package JDBC;

import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
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);
			top_five_seniors(conn1);

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

	private static void top_five_seniors(Connection conn1) throws SQLException, IOException {
		Statement statement = conn1.createStatement();

		String sql = "select (select name from Person where id= StudentID) as StudentName, Classification, GPA, (select name from Person where ID = MentorID) as MentorName from Student  where Classification = 'Senior' order by GPA desc";
		ResultSet outerResultSet = statement.executeQuery(sql);

		String filename = "JDBC_StudentsOutput.txt";
		FileWriter fileWriter = new FileWriter(filename);
		PrintWriter printWriter = new PrintWriter(fileWriter);

		int i = 0;
		float temp = 0;
		while (outerResultSet.next()) {
			if (outerResultSet.getFloat("GPA") == temp || i < 5) {
				String student_name = outerResultSet.getString("StudentName");
				String classification = outerResultSet.getString("Classification");
				float gpa = outerResultSet.getFloat("GPA");
				String mentor = outerResultSet.getString("MentorName");

				String student_data = student_name + ", " + classification + ", " + gpa + ", " + mentor;
				System.out.println(student_data);
				printWriter.print(student_data + "\n");
				i++;

				if (i == 5) {
					temp = gpa;
				}

			} else {
				break;
			}
		}
		printWriter.close();
	}

	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);
		}
		System.out.println();
		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";
		}
	}

}