/*
Class: CSE 3330
Semester: Fall 2018
Student Name: Burnes, Jordan, jlb6776
Student ID: 1001426776
Assignment: project #4
*/


import java.sql.*;
import java.util.Scanner;
import java.util.concurrent.TimeUnit;

final class Main {
    //Info needed for connection
    final static String user = "jlb6776";
    final static String password = "Apple123";
    final static String db = "jlb6776";
    final static String jdbc = "jdbc:mysql://localhost:3306/"+db+"?user="+user+"&password="+password;

    final static Scanner input = new Scanner(System.in);

    public static void main ( String[] args ) throws Exception {
        //Connectiong to driver
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection con = DriverManager.getConnection(jdbc);

        //Menu Options and main program loop
        while(true){
            System.out.print("\033[H\033[2J");
            System.out.flush();
            int choice = 0;
            System.out.println("Please Type One of the Following Numbers and Press Enter: ");
            System.out.println("1. Check if Pilot is busy on a certain day and show the pilot assignments for this day");
            System.out.println("2. Assign a Pilot to a flight leg instance");
            System.out.println("3. Add a Pilot");
            System.out.println("4. Quit");

            //Error checking for invalid input
            try{
                choice = input.nextInt();
            }
            catch(Exception e){
                System.out.println("Please Enter a number");
                //A time delay to make things look nicer
                TimeUnit.SECONDS.sleep(1);
                input.next();
                continue;
            }
            if(choice < 1 || choice > 4){
                System.out.println("Please Enter 1-4");
                TimeUnit.SECONDS.sleep(1);
                continue;
            }

            //First choice to search View3 based on Name and date
            if(choice == 1){
                System.out.println("Please Enter the Pilots Name");
                String name = input.next();
                System.out.println("Please Enter the Date in the Following Format(YYYYMMDD): ");
                String date = input.next();

                //Creating connection and then creating query and error checking for incorrect data in query
                Statement flyAssignments = con.createStatement();
                ResultSet query;
                try{
                    query = flyAssignments.executeQuery("select * from PilotFlyAssignments where Name='" + name + "' AND FDate=" + date + ";");
                }catch(Exception e){
                    System.out.println("Error: " + e.getMessage());
                    System.out.println("Please enter a key and press enter to go back to the menu");
                    input.next();
                    continue;
                }

                //Printing out query, or if null saying nothing was found
                if(query.next() == false){
                    System.out.println("There are no pilots with that Name and Flight Date. ");
                }
                while (query.next())
                    System.out.println(query.getString("ID")+" "+query.getString("Name") + " "+query.getString("FLNO")+" "+query.getString("TOCity")+" "+query.getString("FDate"));

                query.close();
                flyAssignments.close();
                System.out.println("If you would like to coninue, type in any key then press enter");
                input.next();
                continue;
            }

            //Second choice for inputing FlightLegInstance
            else if(choice == 2){
                System.out.println("ID of Pilot: ");
                String pilotId = input.next();
                System.out.println("Flight Number: ");
                String flno = input.next();
                System.out.println("Seq: ");
                String seq = input.next();
                System.out.println("Flight Date In Format(YYYYMMDD");
                String fldate = input.next();
                System.out.println("Departure Time In Format(HHMMSS)");
                String depart = input.next();
                System.out.println("Arrival Time In Format (HHMMSS)");
                String arrive = input.next();

                //Adding connection and creating insert string
                Statement addPilot = con.createStatement();
                String thing = "insert into FlightLegInstance values(" + flno + ", " + seq + ", " + fldate + ", " + depart + ", " + arrive + ", " + pilotId + ");";

                //Error checking for things such as foregin key constraints
                try{
                    addPilot.executeUpdate(thing);
                }catch (Exception e){
                    System.out.println("Error: " + e.getMessage());
                    System.out.println("Please type a key then press enter to go back to the menu");
                    input.next();
                    continue;
                }
                addPilot.close();
                System.out.println("Table Updated, Please wait to be redirected to menu ");
                TimeUnit.SECONDS.sleep(2);
                continue;
            }

            //Third choice to add a Pilot
            else if(choice == 3){
                System.out.println("Enter Id");
                String id = input.next();
                System.out.println("Enter Name");
                String name = input.next();
                System.out.println("Enter Date hired In Format (YYYYMMDD)");
                String date = input.next();

                //creating connection and insert string
                Statement addPilot = con.createStatement();
                String thing = "insert into Pilot values(" + id + ", '" + name + "', " + date + ");";

                //Error checking for things going wrong when adding such as incorrect format of data entered
                try{
                    addPilot.executeUpdate(thing);
                }catch(Exception e){
                    System.out.println("Error: " + e.getMessage());
                    System.out.println("Please type a key then press enter to go back to menu");
                    input.next();
                    continue;
                }

                System.out.println("Pilot Added, Please wait to be redirected to menu");
                TimeUnit.SECONDS.sleep(2);
                continue;
            }
            else{
                con.close();
                System.exit(0);
            }
        }
    }
}