//Amad Ali

import java.util.*;
import java.io.*;
import java.sql.*;

//databaseInterface
public class databaseInterface{
	public static void main(String[]args){
		System.out.println("Starting Interface... \n");

		boolean serverConnect = false;

		try{
			//Login Credentials
			String p = "aali8";
			String u = "ALI2012";

			Class.forName("oracle.jdbc.driver.OracleDriver");
			Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@fourier.cs.iit.edu:1521:orcl", p, u);
			Statement stmt = conn.createStatement();

			System.out.println("Hacked into the matrix! Loading... \n");

			//menu();

			System.out.println("Terminating connection to the matrix... Run before the FBI comes \n");

			stmt.close();
			conn.close();

			serverConnect = true;
		}

		//exceptions
		catch(SQLException sqle){
			System.out.println("SQLException occured: " + sqle);
		}
		catch(ClassNotFoundException cnfe){
			System.out.println("ClassNotFound exception occured: " + cnfe);
		}
		if(serverConnect == false){
			System.out.println("Could not connect to the matrix. Interface quitting.");
		}
	}

	public static void menu(){
		Scanner input = new Scanner(System.in);
		boolean runMenu = true;

		while(runMenu == true){
			//Each switch case will represent one of the seven questions for this assignment
			System.out.println("Welcome to the JDBC menu!");
			System.out.println("Plesae select an option: ");
			System.out.println("1 - Enter class type -> List all classes of type + insructor who teach those classes");
			System.out.println("2 - Enter season/year -> List all classes that were given at that time");
			System.out.println("3 - Enter class type -> list the total revenue obtained from that class type");
			System.out.println("4 - Enter first/last name -> return if they are an instructor/member/both");
			System.out.println("5 - Enter user info ->display contact information");
			System.out.println("6 - Revenue of the rec center");
			System.out.println("7 - LastClassTaught");
			System.out.println("0 - Quit");

			System.out.println("\nNow select which case you would like to do. \nUser Input Selection: ");
			String choice = input.next();
			char selection = choice.charAt(0);

			switch(selection){
				case '1': caseOne(); break;
				case '2': caseTwo(); break;
				case '3': caseThree(); break;
				case '4': caseFour(); break;
				case '5': caseFive(); break;
				case '6': caseSix(); break;
				case '7': caseSeven(); break;
				case '0': caseZero(); break;
				default: System.out.println("\ncommand not recognized, please try again"); break;
			}
		}
	}

	//Case 1: Class Type -> List all classes of type + instructor
	public static void caseOne(){
		try{
			//Login Credentials
			String user = "aali8";
			String pass = "ALI2012";

			//Connection
			Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@fourier.cs.iit.edu:1521:orcl", user, pass);
			Statement stmt = conn.createStatement();

			//Scanner input
			Scanner input = new Scanner(System.in);

			//Prompt
			System.out.println("Enter a class type: ");
			String typeInput = input.next();

			//SQL Query
			String sql = "SELECT title, f_name, l_name" +
							"FROM Class, Instructor" +
							"WHERE class.type = '"+typeInput+"' and Class.instructor = Instructor.id";

			//Execute Query
			ResultSet rset = stmt.executeQuery(sql);
			while(rset.next()){
				System.out.println(" - " + rset.getString("title") + rset.getString("f_name") + rset.getString("l_name"));
			}

			stmt.close();
			conn.close();
		}
		catch(SQLException sqle){
			System.out.println("SQLException occured: " + sqle);
		}
	}

	//Case 2: User inputs a season/year/both lists all classes that are given at that time
	public static void caseTwo(){
		//String creation
		String sql = null;

		try{
			//Login Credentials
			String user = "aali8";
			String pass = "ALI2012";

			//Connection
			Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@fourier.cs.iit.edu:1521:orcl", user, pass);
			Statement stmt = conn.createStatement();

			//Scanner input
			Scanner input = new Scanner(System.in);

			//Prompt
			System.out.println("Enter Season or year(or BOTH! If both, enter season first): ");
			String userInput = input.next();

			//String Tokenizer to parse an input line
			String[] tokens = userInput.split(" ");

			//Check length of token
			if(tokens.length == 2){
				sql = "SELECT title " +
						"FROM Class " +
						"WHERE class.season = '"+tokens[0]+"' and class.year = '"+Integer.parseInt(tokens[1])+"'";
			}
			if(tokens.length == 1){
				if(tokens[0].equals("Spring")||tokens[0].equals("Summer")||tokens[0].equals("Fall")||tokens[0].equals("Winter")){
					sql = "SELECT title " +
							"FROM Class " +
							"WHERE class.season = '"+tokens[0]+"'";
				}
				else{
					sql = "SELECT title " +
							"FROM Class " +
							"WHERE Class.year = '"+Integer.parseInt(tokens[0])+"'";
				}
			}
			//Execute Query
			ResultSet rset = stmt.executeQuery(sql);
			while(rset.next()){
				System.out.println("Title: " +rset.getString("title"));
			}
		}
		catch(SQLException sqle){
			System.out.println("SQLException occured: " + sqle);
		}
	}

	//Case 3: When the user inputs a class type, list the total revenue obtained from that class type
	public static void caseThree(){
		try{
			//Login Credentials
			String user = "aali8";
			String pass = "ALI2012";

			//Connection
			Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@fourier.cs.iit.edu:1521:orcl", user, pass);
			Statement stmt = conn.createStatement();

			//Scanner input
			Scanner input = new Scanner(System.in);

			//Prompt
			System.out.print("Enter class type: ");

			//User input
			String typeInput = input.next();

			//SQL Query
			String sql = "SELECT sum(Cost) " +
							"FROM Enrollment, Class" +
							"WHERE Class.ID = Enrollment.class_id and '"+typeInput+"' = Class.type";

			ResultSet rset = stmt.executeQuery(sql);
			int total = 0;
			while(rset.next()){
				total = rset.getInt(1);
			}
			System.out.println("The total revenue for the class type is $" + total + ".");
			stmt.close();
			conn.close();
		}
		catch (SQLException sqle){
			System.out.println("SQLException occured: " + sqle);
		}
	}

	//Case 4: User enters f_name, l_name and outputs whether that person is instructor or member
	public static void caseFour(){
		try{
			//Login Credentials
			String user = "aali8";
			String pass = "ALI2012";

			//Connection
			Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@fourier.cs.iit.edu:1521:orcl", user, pass);
			Statement stmt = conn.createStatement();

			//Scanner input
			Scanner input = new Scanner(System.in);

			//Prompt
			System.out.print("First Name: ");
			String fname = input.next();

			System.out.print("Last name: ");
			String lname = input.next();

			//SQL Query
			String sql = "SELECT id " +
							"FROM RecCenterMember, Instructor " +
							"WHERE (RecCenterMember.f_name = fname & RecCenterMember.l_name = lname) & RecCenterMember.id = Instructor.id";

			//Execute Query
			//Put something there

			stmt.close();
			conn.close();
		}
		catch (SQLException sqle){
			System.out.println("SQLException occured: " + sqle);
		}
	}

	//Case 5: When given first/last name or an ID, return address/phone of that person or error
	public static void caseFive(){
		try{
			//Login Credentials
			String user = "aali8";
			String pass = "ALI2012";

			//Connection
			Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@fourier.cs.iit.edu:1521:orcl", user, pass);
			Statement stmt = conn.createStatement();

			//Scanner input
			Scanner input = new Scanner(System.in);

			//Prompt
			System.out.println("Enter '1' for First/Last name entry");
			System.out.println("Enter '2' for ID entry");
			System.out.print("What will it be?: ");

			//User input
			String choice = input.next();
			boolean exists = false;
			boolean inData = false;
			int selection = 0;

			switch(choice){
				case "1":
					selection = 1; break;
				case "2":
					selection = 2; break;
				default: return;
			}

			// First/Last Name
			if(selection == 1){
				System.out.print("First: ");
				String fname = input.next();
				System.out.print("Last: ");
				String lname = input.next();

				//SQL Query
				String sql1 = "SELECT address, phone " +
								"FROM FamilyPackage, RecCenterMember " +
								"WHERE RecCenterMember.f_name = '"+fname+"' and RecCenterMember.l_name = '"+lname+"' and RecCenterMember.family_id = FamilyPackage.family_id";

				String sql2 = "SELECT f_name, l_name " +
								"FROM RecCenterMember " +
								"WHERE RecCenterMember.f_name = '"+fname+"' and RecCenterMember.l_name = '"+lname+"'";

				//Execute Query
				ResultSet rset1 = stmt.executeQuery(sql1);
				while(rset1.next()){
					System.out.println(" - " + rset1.getString("address"));
					System.out.println(" - " + rset1.getString("phone"));
					inData = true;
				}
				ResultSet rset2 = stmt.executeQuery(sql2);
				while(rset2.next()){
					System.out.println(" - " + rset2.getString("f_name"));
					System.out.println(" - " + rset2.getString("l_name"));
					exists = true;
				}
			}
			//ID search
			if(selection ==2){
				System.out.print("ID Number: ");
				String id = input.next();

				//SQL Query
				String sql1 = "SELECT address, phone " +
								"FROM FamilyPackage, RecCenterMember " +
								"WHERE RecCenterMember.id = '"+id+"' and FamilyPackage.family_id = RecCenterMember.family_id";

				String sql2 = "SELECT f_name, l_name " +
								"FROM RecCenterMember " +
								"WHERE RecCenterMember.id = '"+id+"' ";
				//SQL Query
				ResultSet rset1 = stmt.executeQuery(sql1);
				while(rset1.next()){
					System.out.println(" - " + rset1.getString("address"));
					System.out.println(" - " + rset1.getString("phone"));
					inData = true;
				}
				ResultSet rset2 = stmt.executeQuery(sql2);
				while(rset2.next()){
					System.out.println(" - " + rset2.getString("f_name"));
					System.out.println(" - " + rset2.getString("l_name"));
					exists = true;
				}
			}
			//Write a check
		}
		catch (SQLException sqle){
			System.out.println("SQLException occured: " + sqle);
		}
	}

	//Case 6: Impact on the revenue of the RecCenter if some discounts were given for age groups
	public static void caseSix(){
		try{
			//Login Credentials
			String user = "aali8";
			String pass = "ALI2012";

			//Connection
			Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@fourier.cs.iit.edu:1521:orcl", user, pass);
			Statement stmt = conn.createStatement();

			//Scanner input
			Scanner input = new Scanner(System.in);

			//Prompt
			System.out.println("Create % discount for certain age groups, and see how it effects revenue!");
			System.out.println("Enter % discount: ");

			//User input
			double discount = input.nextDouble();

			//Select value between 0 and 1
			if(discount > 1 || discount <= 0)
				return;

			System.out.println("Lower Age Limit: ");
			int lower = input.nextInt();
			System.out.println("Upper Age Limit: ");
			int upper = input.nextInt();

			//Check
			if(lower > upper || lower < 0 || upper > 130)
				return;

			//SQL query
			String sql1 = "SELECT sum(Cost) " +
							"FROM Enrollment ";
			String sql2 = "SELECT sum(Cost) " +
							"FROM Enrollment, RecCenterMember " +
							"WHERE Enrollment.member_id = RecCenterMember.id and RecCenterMember.dob > '"+upper+"' and RecCenterMember.dob < '"+lower+"' ";

			//Executing SQL Query
			ResultSet rset1 = stmt.executeQuery(sql1);

			int val1 = 0;
			while(rset1.next())
				val1 = rset1.getInt(1);

			System.out.println("Total Revenue before discount is $" + val1 + ".");
			upper = 20120101 - (upper*10000);
			lower = 20120101 - (lower*10000);

			ResultSet rset2 = stmt.executeQuery(sql2);
			int val2 = 0;
			while(rset2.next())
				val2 = rset2.getInt(1);

			System.out.println("The revenue effected by the discount is $" + val2 + ".");
			double result = val1 - (val2 * discount);

			System.out.println("Thee total revenue with with discount included is $" + result + ".");

			stmt.close();
			conn.close();
		}
		catch (SQLException sqle){
			System.out.println("SQLException occured: " + sqle);
		}
	}

	public static void caseSeven(){
		System.out.println("Case Seven");
	}

	public static void caseZero(){
		System.out.println("Case Zero");
	}
}