import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Scanner;



public class Main {

    public static Connection con;

    static {
        try {
            con = DBClass.getConnection();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Scanner input = new Scanner(System.in);
        Statement state;
        String response = "";
        boolean DBExists = false;
        try {
            con = DBClass.getConnection();
            DBClass.buildDatabase(DBExists);
            state = con.createStatement();
            input.nextLine();

            while (!response.equals("stop")) {
                System.out.println("\n\nWhat do you want to do? You can type 'reset' to reset the passengers " +
                        "and schedules tables, 'file' to load records from a " +
                        "file. Type 'stop' to exit. Do not type the quotations");
                response = input.nextLine();
                switch (response) {
                    case "reset":
                        DBClass.tableReset();
                        break;
                    case "file":
                        System.out.println("What is the name of the file you want to load in. Example: 'sample.txt' without the quotations");
                        Scanner sc = new Scanner(new File(input.next()));

                        while (sc.hasNextLine()) {
                            String line = sc.nextLine();
                            if (line.charAt(0) == 'P') {
                                insertIntoPassenger(line, state);
                            } else if (line.charAt(0) == 'S') {
                                insertIntoScheduleTable(line);
                            }
                        }
                        sc.close();
                        break;
                }
            }

            } catch(Exception e){
                e.printStackTrace();
            }
            finally {
            con.close();
        }

    }

    public static void insertIntoPassenger(String str, Statement state) throws SQLException {
        String[] strArr = str.split(" ");
        String sqlIns = "INSERT INTO Passengers_Table VALUES(" + strArr[1] + ",'" +
                strArr[2] + "','" + strArr[3] + "','" + strArr[4] + "','" + strArr[5] + "');";
        state.executeUpdate(sqlIns);
        state.close();
    }

    public static void insertIntoScheduleTable(String str) throws SQLException, ClassNotFoundException {
        Statement state2 = con.createStatement();
        Statement state3 = con.createStatement();
        Statement state4 = con.createStatement();

        DateTimeFormatter frmt = DateTimeFormatter.ofPattern("MM/dd/yyyy");
        String[] line = str.split(" ");
        int passengerID = Integer.parseInt(line[1]);
        int planePref;
        String seatType = line[3];
        String prefDate = line[4];
        ResultSet rs;
        int count = 0;

        if(!line[2].toUpperCase().equals("X")){
            planePref = Integer.parseInt(line[2]);

            if(planePref > 3){
                planePref = 1;
                LocalDate ld = LocalDate.parse(prefDate, frmt);
                ld = ld.plusDays(1);
                prefDate = ld.format(frmt);
            }

            //if person is luxury
            if(seatType.equals("L")){
                count = 0;
                rs = state2.executeQuery("SELECT * FROM Schedules_Table WHERE Seat_Section = 'L' AND Flight_Date ='" + prefDate + "' AND Flight_TUID = " + planePref +";");

                while(rs.next()){
                    count++;
                }
                state2.close();
                //check to see if luxury is full
                if(count >= findMaxSeats(seatType, planePref)){
                    ++planePref;
                    line[2] = String.valueOf(planePref);
                    insertIntoScheduleTable(buildString(line));
                } else {
                    String insertS = "INSERT INTO Schedules_Table (Passenger_TUID, Flight_TUID, Flight_Date, Seat_Section, Seat_Number, Price_Paid)" +
                            " VALUES(" + passengerID + "," + planePref + ",'"
                            + prefDate + "','" + seatType + "'," + (count + 1) + "," + 2500 + ");";
                    state3.executeUpdate(insertS);
                }


            } else {
                //LOGIC FOR VIP
                rs = state2.executeQuery("SELECT * FROM Schedules_Table WHERE Seat_Section = 'V' AND Flight_Date ='" + prefDate + "' AND Flight_TUID = " + planePref +";");

                while(rs.next()){
                    count++;
                }

                state2.close();

                if(count >= findMaxSeats(seatType, planePref)){
                    //kick out a luxury
                    rs = state2.executeQuery("SELECT * FROM Schedules_Table WHERE Seat_Section = 'L' AND Price_Paid = 2500 AND Flight_Date ='" + prefDate + "' AND Flight_TUID = " + planePref +";");
                    boolean seatFound = false;
                    while(rs.next()){
                        if(rs.getString("Seat_Section").equals("L")){
                            String[] temp = {"S", rs.getString("Passenger_TUID"), rs.getString("Flight_TUID"), "L", rs.getString("Flight_Date")};
                            state4.executeUpdate("UPDATE Schedules_Table SET Passenger_TUID =" + passengerID +", Price_Paid = 4000 WHERE Passenger_TUID = " + rs.getString("Passenger_TUID") + ";");
                            insertIntoScheduleTable(buildString(temp));
                            seatFound = true;
                            break;
                        }
                    }

                    if(!seatFound){
                        ++planePref;
                        line[2] = String.valueOf(planePref);
                        insertIntoScheduleTable(buildString(line));
                    }

                } else {
                    String insertS = "INSERT INTO Schedules_Table (Passenger_TUID, Flight_TUID, Flight_Date, Seat_Section, Seat_Number, Price_Paid)" +
                            " VALUES(" + passengerID + "," + planePref + ",'"
                            + prefDate + "','" + seatType + "'," + (count + 1) + "," + 4000 + ");";
                    state3.executeUpdate(insertS);
                }
            }
        } else {
            line[2] = String.valueOf(1);
            insertIntoScheduleTable(buildString(line));
        }


    }

    public static String buildString(String[] str){
        String newLine = "";
        for(int i = 0; i < str.length; i++){
            newLine += str[i] + " ";
        }


        return newLine;
    }

    public static int findMaxSeats(String seatType, int flightID){
        if(flightID == 1){
            if (seatType.equals("V")) {
                return 4;
            } else {
                return 6;
            }
        }else if(flightID == 2){
            if (seatType.equals("V")) {
                return 3;
            } else {
                return 5;
            }
        } else {
            if (seatType.equals("V")) {
                return 6;

            } else {
                return 8;
            }
        }

    }

}