import java.sql.*;

public class P3 {
	public static void main(String[] args) throws Exception {new P3();}

	public P3() throws Exception {
		// Load and register a JDBC driver
		try {
			// Load the driver (registers itself)
			Class.forName("com.mysql.cj.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/db363mhoppe";
			String user = "dbu363mhoppe";
			String password = "TrlK2579";
			conn1 = DriverManager.getConnection(dbUrl + "?useSSL=false&serverTimezone=CST", user, password);
			System.out.println("*** Connected to the database ***");

			loadTables(conn1);

			PartA_E(conn1);

			PartB(conn1);

			PartC(conn1);

			PartD(conn1);

			PartA_E(conn1);

			PartF(conn1);

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

	private void PartA_E(Connection conn1) throws SQLException {
		Statement statement = conn1.createStatement();
		ResultSet rs = null;

		rs = statement.executeQuery(	"select p.Name, i.Salary " +
										"from Instructor i, Person p " +
										"where i.InstructorID = p.ID");

		int totalSalary = 0;

		while (rs.next()) {
			String name = rs.getString("Name");
			int salary = rs.getInt("Salary");

			System.out.println(name + ", " + salary);
			totalSalary += salary;
		}
		System.out.println("Total Salary of all faculty: " + totalSalary);

		statement.close();
		rs.close();
	}

	private void PartB(Connection conn1) throws SQLException {
		Statement statement = conn1.createStatement();
		ResultSet rs = null;

		rs = statement.executeQuery("select s.GPA from Student s order by GPA DESC");
		for(int i = 0; i < 20; i++) rs.next();
		double TWTYthGPA = rs.getDouble("GPA");

		try {statement.executeUpdate("drop table MeritList");} catch (SQLException e) {}

		statement.executeUpdate("create table MeritList as " +
									"select s.StudentID, s.Classification, s.MentorID, s.GPA " +
									"from Student s " +
									"where s.GPA >= " + TWTYthGPA + ";");

		statement.close();
		rs.close();
	}

	private void PartC(Connection conn1) throws SQLException {
		Statement statement = conn1.createStatement();
		ResultSet rs = null;

		rs = statement.executeQuery("select * from MeritList m order by m.GPA");

		while (rs.next()) {
			System.out.println(rs.getString("StudentID") + ", " +
			rs.getString("Classification") + ", " +
			rs.getString("MentorID") + ", " +
			rs.getDouble("GPA"));
		}

		statement.close();
		rs.close();
	}

	private void PartD(Connection conn1) throws SQLException {
		double[] raisePer = {1.04, 1.06, 1.08, 1.1};

		Statement statement = conn1.createStatement();
		ResultSet rs = null;
		rs = statement.executeQuery(
		"select max( case " +
		"when sub.Classification = 'Freshman' then 1 " +
		"when sub.Classification = 'Sophomore' then 2 " +
		"when sub.Classification = 'Junior' then 3 " +
		"when sub.Classification = 'Senior' then 4 end ), sub.* " +
		"from( " +
				"select m.Classification, i.* " +
				"from MeritList m, Instructor i  where i.InstructorID = m.MentorID) as sub "
		 + "group by sub.InstructorID");

		PreparedStatement stmt2 =
				conn1.prepareStatement ("update Instructor" + " " +
				"set Rank=? , Salary=?" + " " +
				"where InstructorID = ? " );

		while (rs.next()) {
			stmt2.setString(1, rs.getString(4));
			stmt2.setInt(2,(int) (Integer.parseInt(rs.getString(5)) * raisePer[Integer.parseInt(rs.getString(1)) - 1]) );
			stmt2.setString(3,rs.getString(3));
			stmt2.executeUpdate();
		}
		statement.close();
		rs.close();
	}

	private void PartF(Connection conn1) throws SQLException {
		Statement statement = conn1.createStatement();

		try {statement.executeUpdate("drop table MeritList");} catch (SQLException e) {}

		statement.close();

		conn1.close();
	}

	public void loadTables(Connection conn1){
		Statement stmt = null;
		try {
			stmt = conn1.createStatement();

			stmt.executeUpdate("use db363mhoppe;");
		} catch (SQLException e) {}

		try {stmt.executeUpdate("drop table Enrollment");} catch (SQLException e) {}
		try {stmt.executeUpdate("drop table Offering");} catch (SQLException e) {}
		try {stmt.executeUpdate("drop table Course");} catch (SQLException e) {}
		try {stmt.executeUpdate("drop table Student");} catch (SQLException e) {}
		try {stmt.executeUpdate("drop table Instructor");} catch (SQLException e) {}
		try {stmt.executeUpdate("drop table Person;");} catch (SQLException e) {}

		try {
		stmt.executeUpdate("create table Person (" +
									"Name char (20)," +
									"ID char (9) not null," +
									"Address char (30)," +
									"DOB date," +
									"Primary key (ID));");



		stmt.executeUpdate("create table Instructor (" +
									"InstructorID char (9) not null references Person(ID)," +
									"Rank char (12), " +
									"Salary int," +
									"Primary key ( InstructorID));");

		stmt.executeUpdate("create table Student (" +
									"StudentID char (9) not null references Person," +
									"Classification char (10)," +
									"GPA double," +
									"MentorID char (9) references InstructorID(Instructor)," +
									"CreditHours int," +
									"Primary key ( StudentID )" +
									");");

		stmt.executeUpdate("create table Course (" +
									"CourseCode char (6) not null," +
									"CourseName char (50)," +
									"PreReq char (6)," +
									"Primary key ( CourseCode,PreReq)" +
									");");

		stmt.executeUpdate("create table Offering(" +
									"CourseCode char (6) not null," +
									"SectionNo int not null," +
									"InstructorID char (9) not null references Instructor(InstructorID), " +
									"Primary key ( CourseCode , SectionNo )" +
									");");

		stmt.executeUpdate("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)" +
									");");

		stmt.executeUpdate(	"load xml local infile 'D:/External Storage/Documents/IASTATE/CS363/Project 1/UniversityXML/Course.xml' " +
							"into table Course " +
							"rows identified by '<Course>';");

		stmt.executeUpdate(	"load xml local infile 'D:/External Storage/Documents/IASTATE/CS363/Project 1/UniversityXML/Enrollment.xml' " +
							"into table Enrollment " +
							"rows identified by '<Enrollment>';");

		stmt.executeUpdate(	"load xml local infile 'D:/External Storage/Documents/IASTATE/CS363/Project 1/UniversityXML/Instructor.xml' " +
							"into table Instructor " +
							"rows identified by '<Instructor>';");

		stmt.executeUpdate(	"load xml local infile 'D:/External Storage/Documents/IASTATE/CS363/Project 1/UniversityXML/Offering.xml' " +
							"into table Offering " +
							"rows identified by '<Offering>';");

		stmt.executeUpdate(	"load xml local infile 'D:/External Storage/Documents/IASTATE/CS363/Project 1/UniversityXML/Person.xml' " +
							"into table Person " +
							"rows identified by '<Person>';");

		stmt.executeUpdate(	"load xml local infile 'D:/External Storage/Documents/IASTATE/CS363/Project 1/UniversityXML/Student.xml' " +
							"into table Student " +
							"rows identified by '<Student>';");
		stmt.close();
		} catch (SQLException e) {}
	}
}