import java.io.Console;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class Faculty {

	private static final String oracleServer = "dbs2.cs.umb.edu";
	private static final String oracleServerSid = "dbs2";
	private static Connection conn = null;

	public static void main(String args[]) {

		conn = getConnection();

		System.out.print("Please enter faculty ID (-1 to register): ");
		Scanner input = new Scanner(System.in);
		int fid = Integer.parseInt(input.nextLine());

		if(fid == -1) {

			fid = registerFaculty();

		} else {

			try {

				// check that ID exists
				Statement stmt = conn.createStatement();
				ResultSet fidExists = stmt.executeQuery("SELECT COUNT(*) FROM Faculty WHERE fid = " + fid);
				fidExists.next();

				if(fidExists.getInt(1) == 0) {
					System.out.println("Invalid faculty ID specified! Please re-enter or register.");
					return;
				}

			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}

		}

		printMenu();
		getCommands(fid);

		try {

			if (conn != null) {
			      conn.close();
			}

		} catch (SQLException e) {
			System.out.println(e.getMessage());
			System.exit(1);
		}

	}

	public static int registerFaculty() {

		Scanner input = new Scanner(System.in);

		System.out.println("Entering faculty registration process.");
		System.out.println("--------------------------------------");

		System.out.print("Enter new faculty ID: ");
		int fid = Integer.parseInt(input.nextLine());

		System.out.print("Enter new faculty name: ");
		String fname = input.nextLine();

		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("INSERT INTO Faculty VALUES (" + fid + ", '" + fname + "')");
			stmt.close();
		} catch(SQLException e) {
			System.out.println(e.getMessage());
		}

		return fid;

	}

	public static void printMenu() {

		System.out.println("-----------------------");
		System.out.println("L : list all courses.");
		System.out.println("C : create my course.");
		System.out.println("D : delete my course.");
		System.out.println("S : search all courses.");
		System.out.println("M : list my courses.");
		System.out.println("X : exit application.");
		System.out.println("-----------------------");

	}

	public static void getCommands(int fid) {

		String command = "";
		Scanner input = new Scanner(System.in);

		while(!command.equalsIgnoreCase("X")) {

			System.out.print("Enter a command: ");
			command = input.nextLine();

			if(command.equalsIgnoreCase("L")) {
				performCommandL(fid);
			} else if(command.equalsIgnoreCase("C")) {
				performCommandC(fid);
			} else if (command.equalsIgnoreCase("D")) {
				performCommandD(fid);
			} else if(command.equalsIgnoreCase("S")) {
				performCommandS(fid);
			} else if(command.equalsIgnoreCase("M")) {
				performCommandM(fid);
			} else if(command.equalsIgnoreCase("X")) {
				return;
			}

		}

	}

	private static void performCommandM(int fid) {

		try {

			Statement stmt = conn.createStatement();
			ResultSet allCourses = stmt.executeQuery("SELECT cid, cname, credits FROM Courses WHERE fid = " + fid);

			System.out.println("-----------------------");

			while(allCourses.next()) {

				System.out.println("Course ID: " + allCourses.getInt(1) + "\t\tCourse Name: " + allCourses.getString(2) + "\t\tCredits: " + allCourses.getInt(3));

			}

			System.out.println("-----------------------");

			allCourses.close();
			stmt.close();

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}

	}

	private static void performCommandS(int fid) {

		Scanner scan = new Scanner(System.in);
		String searchName = null;

		try {

			System.out.print("Please enter the course name to search: ");
			searchName = scan.nextLine().toUpperCase();

			Statement stmt = conn.createStatement();
			ResultSet allCourses = stmt.executeQuery("SELECT cid, cname, credits, fid FROM Courses WHERE UPPER(cname) LIKE '%" + searchName + "%'");

			System.out.println("-----------------------");

			while(allCourses.next()) {

				System.out.println("Course ID: " + allCourses.getInt(1) + "\t\tCourse Name: " + allCourses.getString(2) + "\t\tCredits: " + allCourses.getInt(3) + "\t\tFaculty ID: " + allCourses.getInt(4));

			}

			System.out.println("-----------------------");

			allCourses.close();
			stmt.close();

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}

	}

	private static void performCommandD(int fid) {

		Scanner scan = new Scanner(System.in);
		int cid = -1;

		try {

			System.out.println("-----------------------");

			System.out.print("Please enter the course ID to delete: ");
			cid = Integer.parseInt(scan.nextLine());

			Statement stmt = conn.createStatement();
			ResultSet cfid = stmt.executeQuery("SELECT fid FROM courses WHERE cid = " + cid);
			// cfid.first(); seems like Oracle driver doesn't support this
			cfid.next();

			if(!(cfid.getInt(1) == fid)) {
				System.out.println("You do not have the authority to delete this course!");
				System.out.println("-----------------------");
				cfid.close();
				stmt.close();
				return;
			}

			cfid.close();
			stmt.close();

			stmt = conn.createStatement();
			ResultSet enrl = stmt.executeQuery("SELECT COUNT(*) FROM Enrolled WHERE cid = " + cid );
			// enrl.first(); seems like Oracle driver doesn't support this
			enrl.next();

			if(enrl.getInt(1) > 4) {
				System.out.println("You cannot delete this course if there 5 or more students enrolled!");
				System.out.println("-----------------------");
				enrl.close();
				stmt.close();
				return;
			}

			enrl.close();
			stmt.close();

			stmt = conn.createStatement();
			stmt.executeUpdate("DELETE FROM Courses WHERE cid = " + cid);

			System.out.println("Course deleted!");
			System.out.println("-----------------------");

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}

	}

	private static void performCommandC(int fid) {

		Scanner scan = new Scanner(System.in);
		int cid = -1;
		String cname = null;
		int credits = -1;

		try {

			System.out.println("-----------------------");

			System.out.print("Please enter the new course ID: ");
			cid = Integer.parseInt(scan.nextLine());
			System.out.print("Please enter the new course name: ");
			cname = scan.nextLine();
			System.out.print("Please enter the new course number of credits: ");
			credits = Integer.parseInt(scan.nextLine());

			Statement stmt = conn.createStatement();
			stmt.executeUpdate("INSERT INTO Courses VALUES (" + cid + ", '" + cname + "', " + credits + ", " + fid + ")");
			stmt.close();

			System.out.println("New course submitted!");
			System.out.println("-----------------------");

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}

	}

	private static void performCommandL(int fid) {

		try {

			Statement stmt = conn.createStatement();
			ResultSet allCourses = stmt.executeQuery("SELECT cid, cname, credits, fid FROM Courses");

			System.out.println("-----------------------");

			while(allCourses.next()) {

				System.out.println("Course ID: " + allCourses.getInt(1) + "\t\tCourse Name: " + allCourses.getString(2) + "\t\tCredits: " + allCourses.getInt(3) + "\t\tFaculty ID: " + allCourses.getInt(4));

			}

			System.out.println("-----------------------");

			allCourses.close();
			stmt.close();

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}

	}

	public static Connection getConnection() {

		// Get username and password
		Scanner input = new Scanner(System.in);
		System.out.print("Username: ");
		String username = input.nextLine();
		System.out.print("Password: ");
		//the following is used to mask the password
		Console console = System.console();
		String password = new String(console.readPassword());
		String connString = "jdbc:oracle:thin:@" + oracleServer + ":1521:" + oracleServerSid;

		System.out.println("Please wait for the connection to establish...");

		// load the driver
		String jdbcDriver = "oracle.jdbc.OracleDriver";

		try {
			Class.forName(jdbcDriver);
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}

		Connection conn;

		try { // Connect to the database
			conn = DriverManager.getConnection(connString, username, password);
		} catch(SQLException e) {
			System.out.println("Connection Error!");
			System.out.println(e.getMessage());
			return null;
		}

		return conn;

	}

}