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

final class Main {
    final static String user = "jlb6776";
    final static String password = "Apple123";
    final static String db = "";
    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 {
        System.out.println("Let start work with Java and JDBC.");


        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection con = DriverManager.getConnection(jdbc);
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("select * from Plane");
        while (rs.next())
            System.out.println(rs.getString("Maker")+" "+rs.getString("Model"));
        rs.close();



        try {
            Statement st = con.createStatement();
            String code = " Create VIEW vp As select * from Plane";
            st.executeUpdate(code);

            System.out.println(" VIEW successfully created!");
        } catch (SQLException s) {
            System.out.println("VIEW already exists!");
        }


        //stmt.close();


        while(true){
            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");

            try{
                choice = input.nextInt();
            }
            catch(Exception e){
                System.out.println("Please Enter a number");
                TimeUnit.SECONDS.sleep(1);
                input.next();
                continue;
            }
            if(choice < 1 || choice > 4){
                System.out.println("Please Enter 1-4");
                TimeUnit.SECONDS.sleep(1);
                continue;
            }

            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(): ");
                String date = input.next();

                Statement flyAssignments = con.createStatement();
                var query = flyAssignments.executeQuery("select * from PilotFlyAssignments");
                while (query.next())
                    System.out.println(query.getString("PilotID")+" "+query.getString("Pilot_Name"));

                query.close();
                flyAssignments.close();

                input.next();
                continue;
            }
            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");
                String fldate = input.next();
                System.out.println("Departure Time");
                String depart = input.next();
                System.out.println("Arrival Time");
                String arrive = input.next();

                Statement addPilot = con.createStatement();
                String thing = "insert into FlightLegInstance values(" + flno + ", " + seq + ", " + fldate + ", " + depart + ", " + arrive + ", " + pilotId + ");";
                var query = addPilot.executeQuery(thing);
                query.close();
                addPilot.close();
                System.out.println("Table Updated ");
                TimeUnit.SECONDS.sleep(1);
                continue;
            }
            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");
                String date = input.next();

                Statement addPilot = con.createStatement();
                String thing = "insert into Pilot values(" + id + ", " + name + ", " + date + ");";
                var query = addPilot.executeQuery(thing);

                System.out.println("Pilot Added");
                TimeUnit.SECONDS.sleep(1);
                continue;
            }
            else{
                con.close();
                System.exit(0);
            }
        }
    }
}