mport java.util.*;
import java.sql.*;

public class StudentApplication
{
	private static Scanner scanner;
	private static Connection conn = null;
	private static Statement stmt = null;
	private static Throwable e;

	public static boolean ConnectToDB() {
		try {

			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/University";

			conn = DriverManager.getConnection(url,"larry","pass");
			System.out.println("Connection Successful!");
			stmt = conn.createStatement();
			return true;

		} catch(SQLException e) {
			System.err.println("SQL Exception! ");
			System.err.println(e.getMessage());
		}catch(Exception e){
			System.err.println(e.getMessage());
		}
		return false;
}


	public static void main (String[] args) {

		boolean dbConnection = false;
		try{
			dbConnection = ConnectToDB();
		}
		catch(Exception e)
		{
			System.out.println("Sorry could not connect to DB.");
			return;
		}
		if(!dbConnection){
			System.out.println("Exiting System as unable to connect to DB");
			return;
		}
		int choice;
		scanner = new Scanner (System.in);

		choice = menu();
		while (choice != 7) {

			switch (choice) {
				case 1: choice1(); break;
				case 2: choice2(); break;
				case 3: choice3(); break;
				case 4: choice4(); break;
				case 5: choice5(); break;
				case 6: choice6(); break;
				default: break;
			}

			choice = menu();
		};
	}

	public static int menu() {

		int choice;

		System.out.println ("\n\n");
		System.out.println ("1.	Add a new student to the system");
		System.out.println ("2.	Display information about a student with a given student ID");
		System.out.println ("3.	Change a student's telephone number");
		System.out.println ("4.	List all the students in the system");
		System.out.println ("5.	Delete a student from the system with a given Student ID");
		System.out.println ("6.	Register a Student in a Course");
		System.out.println ("7.	Exit");

		System.out.print ("\n\nPleasemake a selection: ");

		choice = scanner.nextInt();
		return choice;
	}

	//Add a new student to the Database
	public static void choice1() {
		String firstName, lastName;
		String phone;

		System.out.println ("Enter the student's first name, last name, and telephone number: ");

		firstName = scanner.next();
		lastName = scanner.next();
		phone = scanner.next();

		// create and insert Student in Student Table
		try{
			String sql = "SELECT MAX(StudentID) as maxID FROM Student";
			ResultSet rs = stmt.executeQuery(sql);
			int maxID = 0;
			while(rs.next())
			{
				maxID=rs.getInt("maxID");
				System.out.println(maxID);
			}
			int nextID = maxID + 10;
			//NOTE THE CONSTRUCTION OF SQL STATEMENT
			sql = "INSERT INTO STUDENT VALUES(" + nextID +
					",'" + firstName + "','"+lastName+ "','"+phone +"')";
			System.out.println("INSERT STATEMENT IS :\n " + sql);
			stmt.execute(sql);
			System.out.println("New Student created with ID : "+ nextID);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Sorry unable to create Student");
			return;
		}
	}

	//Display information about a student with a given student ID
	public static void choice2() {

		int studentID;
		StudentTwo student;

		System.out.print("Please enter a student ID: ");
		studentID = scanner.nextInt();

		student = getStudent(studentID);

		if (student == null)
			System.out.println ("There is no student with this ID.");
		else
			System.out.println (student.toString());
	}

	//Change a student's telephone number
	public static void choice3() {

		int studentID;
		StudentTwo student;

		System.out.print("Please enter a student ID: ");
		studentID = scanner.nextInt();

	student = getStudent(studentID);

		if (student == null)
			System.out.println ("There is no student with this ID.");
		else {
			String newPhone;
			System.out.print ("Please enter the new telephone number: ");
			newPhone = scanner.next();
			//student.setPhone(newPhone);
			String sql = "UPDATE Student SET Phone = '"+newPhone+"' WHERE StudentID = "+ studentID;
			try{
				stmt.executeUpdate(sql);
			}
			catch(Exception e){
				e.printStackTrace();
				System.out.println("Sorry could not update the phone number");
				return;

			}
			System.out.println ("Student's telephone number has been updated.");
		}

	}

	//List all the students in the system
	public static void choice4() {
		StudentTwo st = new StudentTwo();
		try{
			String sql = "SELECT * FROM Student";
			ResultSet rs = stmt.executeQuery(sql);
			System.out.println("List of Students:");
			while(rs.next())
			{
				st.setID(rs.getInt("StudentID"));//get by column name
				st.setFirstName(rs.getString("FirstName"));
				st.setLastName(rs.getString(3));//get by column index
				st.setPhone(rs.getString(4));
				System.out.println(st.toString());
			}
		}
		catch(Exception e){
			e.printStackTrace();
			System.out.println("Sorry unable to fetch Students from DB");
			st = null;
		}
	}

	//Delete a Student from the DB
	public static void choice5() {
		int studentID;

		System.out.print("Please enter a student ID: ");
		studentID = scanner.nextInt();
		try{
			String sql = "DELETE FROM Student WHERE StudentID = "+ studentID;
			int res = stmt.executeUpdate(sql);
			System.out.println(res + " Student deleted");
		}
		catch(Exception e){
			e.printStackTrace();
			System.out.println("Sorry Unable to delete Student");
		}
	}

	//Register a Student in a Course
	public static void choice6() {
		int studentID;
		int courseID;
		System.out.print("Please enter a student ID: \n");
		studentID = scanner.nextInt();
		System.out.print("Please select a Course(Enter 1 or 2 or 3) : ");
		System.out.print("1. OOP with Java\n");
		System.out.print("2. Intro to C#\n");
		System.out.print("3. Introduction to JDBC\n");
		courseID = scanner.nextInt();
		if(courseID != 1 &&courseID != 2 &&courseID != 3)
		{
			System.out.println("Please enter correct choice");
			return;
		}
		try{
			String sql = "INSERT INTO Registration(StudentID, CourseID) VALUES("+ studentID+","+courseID+")";
			boolean res = stmt.execute(sql);
			System.out.println("Student Registered \n");
		}
		catch(Exception e){
			e.printStackTrace();
			System.out.println("Sorry Unable to register Student");
		}
	}
	public static StudentTwo getStudent(int studentID) {
		StudentTwo s = new StudentTwo();
		try{

			String sql = "SELECT * FROM Student WHERE StudentID = "+ studentID;
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next())
			{
				s.setID(rs.getInt("StudentID"));//get by column name
				s.setFirstName(rs.getString("FirstName"));
				s.setLastName(rs.getString(3));//get by column index
				s.setPhone(rs.getString(4));
			}
		}
		catch(Exception e){
			e.printStackTrace();
			System.out.println("Sorry unable to fetch Student from DB");
			s = null;
		}
		return s;
	}

}