AIDS

package sample;

import javafx.beans.property.SimpleStringProperty;
import javafx.beans.value.ChangeListener;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.event.EventHandler;
import javafx.fxml.FXML;
import javafx.fxml.FXMLLoader;
import javafx.scene.control.*;
import javafx.scene.control.cell.TextFieldTableCell;
import javafx.scene.input.MouseButton;
import javafx.scene.input.MouseEvent;
import javafx.scene.layout.BorderPane;
import javafx.util.Callback;
import jdk.nashorn.internal.runtime.ECMAException;
import org.postgresql.ds.PGPoolingDataSource;
import sample.inserts.*;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Optional;

public class Controller {
    private static String url;
    private static String user;
    private static String password;
    protected PGPoolingDataSource source;
    private Connection con;
    private ObservableList<ObservableList> flights;
    private ObservableList<ObservableList> passengers;
    private ObservableList<ObservableList> boardingPasses;
    private ObservableList<ObservableList> airTickets;
    private ObservableList<ObservableList> aircrafts;
    private ObservableList<ObservableList> luggageList;
    private ObservableList<ObservableList> cargoList;
    private ObservableList<ObservableList> techOps;

    @FXML
    private BorderPane mainPane;

    @FXML
    private TableView flight;

    @FXML
    private TableView passenger;

    @FXML
    private TableView boarding_pass;

    @FXML
    private TableView air_ticket;

    @FXML
    private TableView aircraft;

    @FXML
    private TableView luggage;

    @FXML
    private TableView cargo;

    @FXML
    private TableView technical_operation;

    @FXML
    private TextArea consoleTextArea;

    @FXML
    private TextArea detailTextArea;

    @FXML
    public void setDatabaseBtnClicked(ActionEvent actionEvent) throws Exception {
        Dialog<ButtonType> dialog = new Dialog<>();
        dialog.initOwner(mainPane.getScene().getWindow());
        dialog.setTitle("Set database login info");
        FXMLLoader fxmlLoader = new FXMLLoader();
        fxmlLoader.setLocation(getClass().getResource("setDatabaseDialog.fxml"));
        try {
            dialog.getDialogPane().setContent(fxmlLoader.load());
        } catch (IOException e) {
            System.out.println("Couldn't load the dialog");
            e.printStackTrace();
            return;
        }

        dialog.getDialogPane().getButtonTypes().add(ButtonType.OK);
        dialog.getDialogPane().getButtonTypes().add(ButtonType.CANCEL);

        Optional<ButtonType> result = dialog.showAndWait();

        if (result.isPresent() && result.get() == ButtonType.OK) {
            setDatabaseDialogController controller = fxmlLoader.getController();
            url = controller.getUrl();
            user = controller.getUser();
            password = controller.getPassword();
            connectToDbs();
        }
    }

    @FXML
    public void addAirTicket() throws Exception {
        String from = "", to = "", created = "", class1 = "", passenger_id = "", flight_id = "";

        Dialog<ButtonType> dialog = new Dialog<>();
        dialog.initOwner(mainPane.getScene().getWindow());
        dialog.setTitle("New Air ticket");
        FXMLLoader fxmlLoader = new FXMLLoader();
        fxmlLoader.setLocation(getClass().getResource("inserts/insertAirTicket.fxml"));
        try {
            dialog.getDialogPane().setContent(fxmlLoader.load());
        } catch (IOException e) {
            System.out.println("Couldn't load the dialog");
            e.printStackTrace();
            return;
        }

        dialog.getDialogPane().getButtonTypes().add(ButtonType.OK);
        dialog.getDialogPane().getButtonTypes().add(ButtonType.CANCEL);

        Optional<ButtonType> result = dialog.showAndWait();

        if (result.isPresent() && result.get() == ButtonType.OK) {
            insertAirTicketController controller = fxmlLoader.getController();
            from = controller.getFrom();
            to = controller.getTo();
            created = controller.getCreated();
            class1 = controller.getClass1();
            passenger_id = controller.getPassenger_id();
            flight_id = controller.getFlight_id();

            try {
                Statement stmt = con.createStatement();
                String sql = "INSERT INTO AIR_TICKET (\"from\", \"to\", created, class, passenger_id, flight_id) " +
                        "VALUES ('" + from + "','" + to + "','" + created + "'," + class1 + "," + passenger_id + "," + flight_id + ")";
                System.out.println(sql);
                stmt.executeUpdate(sql);
            } catch (SQLException sqle) {
                System.out.println(sqle.getMessage());
            }
        }
    }

    @FXML
    private void addAircraft() throws Exception {
        String model = "", capacity = "", dateOfBuilt = "", maxLoad = "", registration = "";

        Dialog<ButtonType> dialog = new Dialog<>();
        dialog.initOwner(mainPane.getScene().getWindow());
        dialog.setTitle("New Aircraft");
        FXMLLoader fxmlLoader = new FXMLLoader();
        fxmlLoader.setLocation(getClass().getResource("inserts/insertAircraft.fxml"));
        try {
            dialog.getDialogPane().setContent(fxmlLoader.load());
        } catch (IOException e) {
            System.out.println("Couldn't load the dialog");
            e.printStackTrace();
            return;
        }

        dialog.getDialogPane().getButtonTypes().add(ButtonType.OK);
        dialog.getDialogPane().getButtonTypes().add(ButtonType.CANCEL);

        Optional<ButtonType> result = dialog.showAndWait();

        if (result.isPresent() && result.get() == ButtonType.OK) {
            insertAircraftController controller = fxmlLoader.getController();
            model = controller.getModel();
            capacity = controller.getCapacity();
            dateOfBuilt = controller.getDateOfBuilt();
            maxLoad = controller.getMaxLoad();
            registration = controller.getRegistration();
        }

        try {
            Statement stmt = con.createStatement();
            String sql = "INSERT INTO AIRCRAFT (model, capacity, date_of_built, max_load, registration) " +
                    "VALUES ('" + model + "','" + capacity + "','" + dateOfBuilt + "'," + maxLoad + ",'" + registration + "')";
            System.out.println(sql);
            stmt.executeUpdate(sql);
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
        }
    }

    @FXML
    private void addBoardingPass() {
        String class1 = "", created = "", flight_id = "", air_ticket_id = "", seat = "";

        Dialog<ButtonType> dialog = new Dialog<>();
        dialog.initOwner(mainPane.getScene().getWindow());
        dialog.setTitle("New Boarding pass");
        FXMLLoader fxmlLoader = new FXMLLoader();
        fxmlLoader.setLocation(getClass().getResource("inserts/insertBoardingPass.fxml"));
        try {
            dialog.getDialogPane().setContent(fxmlLoader.load());
        } catch (IOException e) {
            System.out.println("Couldn't load the dialog");
            e.printStackTrace();
            return;
        }

        dialog.getDialogPane().getButtonTypes().add(ButtonType.OK);
        dialog.getDialogPane().getButtonTypes().add(ButtonType.CANCEL);

        Optional<ButtonType> result = dialog.showAndWait();

        if (result.isPresent() && result.get() == ButtonType.OK) {
            insertBoardingPassController controller = fxmlLoader.getController();
            class1 = controller.getClass1();
            created = controller.getCreated();
            flight_id = controller.getFlight_id();
            air_ticket_id = controller.getAir_ticket_id();
            seat = controller.getSeat();
        }

        try {
            Statement stmt = con.createStatement();
            String sql = "INSERT INTO BOARDING_PASS (class, created, flight_id, air_ticket_id, seat) " +
                    "VALUES (" + class1 + ",'" + created + "'," + flight_id + "," + air_ticket_id + "," + seat + ")";
            stmt.executeUpdate(sql);
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
        }
    }

    @FXML
    private void addCargo() {
        String weight = "", flight_id = "";

        Dialog<ButtonType> dialog = new Dialog<>();
        dialog.initOwner(mainPane.getScene().getWindow());
        dialog.setTitle("New Cargo");
        FXMLLoader fxmlLoader = new FXMLLoader();
        fxmlLoader.setLocation(getClass().getResource("inserts/insertCargo.fxml"));
        try {
            dialog.getDialogPane().setContent(fxmlLoader.load());
        } catch (IOException e) {
            System.out.println("Couldn't load the dialog");
            e.printStackTrace();
            return;
        }

        dialog.getDialogPane().getButtonTypes().add(ButtonType.OK);
        dialog.getDialogPane().getButtonTypes().add(ButtonType.CANCEL);

        Optional<ButtonType> result = dialog.showAndWait();

        if (result.isPresent() && result.get() == ButtonType.OK) {
            insertCargoController controller = fxmlLoader.getController();
            weight = controller.getWeight();
            flight_id = controller.getFlight_id();
        }

        try {
            Statement stmt = con.createStatement();
            String sql = "INSERT INTO CARGO (weight, flight_id) " +
                    "VALUES (" + weight + "," + flight_id + ")";
            stmt.executeUpdate(sql);
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
        }
    }

    @FXML
    private void addFlight() {
        String from = "", to = "", gate = "", departure = "", arrival = "", status = "", flight_code = "", aircraft_id = "";

        Dialog<ButtonType> dialog = new Dialog<>();
        dialog.initOwner(mainPane.getScene().getWindow());
        dialog.setTitle("New Flight");
        FXMLLoader fxmlLoader = new FXMLLoader();
        fxmlLoader.setLocation(getClass().getResource("inserts/insertFlight.fxml"));
        try {
            dialog.getDialogPane().setContent(fxmlLoader.load());
        } catch (IOException e) {
            System.out.println("Couldn't load the dialog");
            e.printStackTrace();
            return;
        }

        dialog.getDialogPane().getButtonTypes().add(ButtonType.OK);
        dialog.getDialogPane().getButtonTypes().add(ButtonType.CANCEL);

        Optional<ButtonType> result = dialog.showAndWait();

        if (result.isPresent() && result.get() == ButtonType.OK) {
            insertFlightController controller = fxmlLoader.getController();
            from = controller.getFrom();
            to = controller.getTo();
            gate = controller.getGate();
            departure = controller.getDeparture();
            arrival = controller.getArrival();
            status = controller.getStatus();
            flight_code = controller.getFlightCode();
            aircraft_id = controller.getAircraftID();
        }

        try {
            Statement stmt = con.createStatement();
            String sql = "INSERT INTO AIRCRAFT (\"from\", \"to\", gate, departure, arrival, status, flight_code, aircraft_id) " +
                    "VALUES ('" + from + "','" + to + "','" + gate + "','" + departure + "','" + arrival + "','" + status + "','" + flight_code + "'," + aircraft_id + ")";
            System.out.println(sql);
            stmt.executeUpdate(sql);
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
        }
    }

    @FXML
    private void addLuggage() {
        String weight = "", boarding_pass_id = "";

        Dialog<ButtonType> dialog = new Dialog<>();
        dialog.initOwner(mainPane.getScene().getWindow());
        dialog.setTitle("New Luggage");
        FXMLLoader fxmlLoader = new FXMLLoader();
        fxmlLoader.setLocation(getClass().getResource("inserts/insertLuggage.fxml"));
        try {
            dialog.getDialogPane().setContent(fxmlLoader.load());
        } catch (IOException e) {
            System.out.println("Couldn't load the dialog");
            e.printStackTrace();
            return;
        }

        dialog.getDialogPane().getButtonTypes().add(ButtonType.OK);
        dialog.getDialogPane().getButtonTypes().add(ButtonType.CANCEL);

        Optional<ButtonType> result = dialog.showAndWait();

        if (result.isPresent() && result.get() == ButtonType.OK) {
            insertLuggageController controller = fxmlLoader.getController();
            weight = controller.getWeight();
            boarding_pass_id = controller.getBoardingPassID();
        }

        try {
            Statement stmt = con.createStatement();
            String sql = "INSERT INTO LUGGAGE (weight, boarding_pass_id) " +
                    "VALUES (" + weight + "," + boarding_pass_id + ")";
            stmt.executeUpdate(sql);
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
        }
    }

    @FXML
    private void addPassenger() {
        String dateOfBirth = "", phoneNo = "", status = "", firstName = "", lastName = "", email = "", sex = "";

        Dialog<ButtonType> dialog = new Dialog<>();
        dialog.initOwner(mainPane.getScene().getWindow());
        dialog.setTitle("New Passenger");
        FXMLLoader fxmlLoader = new FXMLLoader();
        fxmlLoader.setLocation(getClass().getResource("inserts/insertPassenger.fxml"));
        try {
            dialog.getDialogPane().setContent(fxmlLoader.load());
        } catch (IOException e) {
            System.out.println("Couldn't load the dialog");
            e.printStackTrace();
            return;
        }

        dialog.getDialogPane().getButtonTypes().add(ButtonType.OK);
        dialog.getDialogPane().getButtonTypes().add(ButtonType.CANCEL);

        Optional<ButtonType> result = dialog.showAndWait();

        if (result.isPresent() && result.get() == ButtonType.OK) {
            insertPassengerController controller = fxmlLoader.getController();
            dateOfBirth = controller.getDateOfBirth();
            phoneNo = controller.getPhoneNo();
            status = controller.getStatus();
            firstName = controller.getFirstName();
            lastName = controller.getLastName();
            email = controller.getEmail();
            sex = controller.getSex();
        }

        try {
            Statement stmt = con.createStatement();
            String sql = "INSERT INTO PASSENGER (date_of_birth, phone_no, status, first_name, last_name, email, sex) " +
                    "VALUES ('" + dateOfBirth + "','" + phoneNo + "','" + status + "','" + firstName + "','" + lastName + "','" + email + "','" + sex + "'')";
            System.out.println(sql);
            stmt.executeUpdate(sql);
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
        }
    }

    @FXML
    private void addTechnicalOperation() {
        String end = "", aircraft_id = "", firs_name = "", last_name = "", info = "";

        Dialog<ButtonType> dialog = new Dialog<>();
        dialog.initOwner(mainPane.getScene().getWindow());
        dialog.setTitle("New Technical operation");
        FXMLLoader fxmlLoader = new FXMLLoader();
        fxmlLoader.setLocation(getClass().getResource("inserts/insertTechnicalOperation.fxml"));
        try {
            dialog.getDialogPane().setContent(fxmlLoader.load());
        } catch (IOException e) {
            System.out.println("Couldn't load the dialog");
            e.printStackTrace();
            return;
        }

        dialog.getDialogPane().getButtonTypes().add(ButtonType.OK);
        dialog.getDialogPane().getButtonTypes().add(ButtonType.CANCEL);

        Optional<ButtonType> result = dialog.showAndWait();

        if (result.isPresent() && result.get() == ButtonType.OK) {
            insertTechnicalOperationController controller = fxmlLoader.getController();
            end = controller.getEnd();
            aircraft_id = controller.getAircraftID();
            firs_name = controller.getFirstName();
            last_name = controller.getLastName();
            info = controller.getInfo();
        }

        try {
            Statement stmt = con.createStatement();
            String sql = "INSERT INTO TECHNICAL_OPERATION (end, aircraft_id, first_name, last_name, info) " +
                    "VALUES ('" + end + "'," + aircraft_id + ",'" + firs_name + "','" + last_name + "','" + info + "')";
            stmt.executeUpdate(sql);
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
        }
    }

    @FXML
    public void executeConsole() {
        try {
            source = dbs_conn.getSource(url.split("/")[0], url.split("/")[1], user, password);
            Connection conn = source.getConnection();
            try {
                conn.createStatement().execute(consoleTextArea.getText());
            } catch (Exception e) {

            } finally {
                try {
                    if (conn != null) conn.close();
                } catch (Exception e) {
                    detailTextArea.setText(e.getMessage());
                }
            }
        } catch (Exception e) {
            detailTextArea.setText(e.getMessage());
        } finally {
            refreshTables();
        }
    }

    @FXML
    private void refreshTables() {
        try {
            flight.getItems().clear();
            flight.getColumns().clear();
            flights = FXCollections.observableArrayList();
            getData(flight, "flight", flights);

            passenger.getItems().clear();
            passenger.getColumns().clear();
            passengers = FXCollections.observableArrayList();
            getData(passenger, "passenger", passengers);

            boarding_pass.getItems().clear();
            boarding_pass.getColumns().clear();
            boardingPasses = FXCollections.observableArrayList();
            getData(boarding_pass, "boarding_pass", boardingPasses);

            air_ticket.getItems().clear();
            air_ticket.getColumns().clear();
            airTickets = FXCollections.observableArrayList();
            getData(air_ticket, "air_ticket", airTickets);

            aircraft.getItems().clear();
            aircraft.getColumns().clear();
            aircrafts = FXCollections.observableArrayList();
            getData(aircraft, "aircraft", aircrafts);

            luggage.getItems().clear();
            luggage.getColumns().clear();
            luggageList = FXCollections.observableArrayList();
            getData(luggage, "luggage", luggageList);

            cargo.getItems().clear();
            cargo.getColumns().clear();
            cargoList = FXCollections.observableArrayList();
            getData(cargo, "cargo", cargoList);

            technical_operation.getItems().clear();
            technical_operation.getColumns().clear();
            techOps = FXCollections.observableArrayList();
            getData(technical_operation, "technical_operation", techOps);

        } catch (Exception e) {

        }
    }

    @FXML
    public void clearConsole() {
        consoleTextArea.clear();
        detailTextArea.clear();
    }

    public void getData(TableView table, String _class, ObservableList<ObservableList> rows) throws SQLException {
        /*
        Pripoji sa na databazu
         */
        source = dbs_conn.getSource(url.split("/")[0], url.split("/")[1], user, password);
        Connection conn = source.getConnection();
        try {
            /*
            Vyselectuje prvych 100 rows z databazy aby to netrvalo prilis dlho
             */
            String SQL = "SELECT * FROM " + _class + " LIMIT 100";
            ResultSet rs = conn.createStatement().executeQuery(SQL);

            /*
            Dynamicky vytvori columns v TableView podla toho kolko columns sa nachadza v danej tabulke v databaze
             */
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                final int j = i;
                TableColumn col = new TableColumn(rs.getMetaData().getColumnName(i + 1));
                col.setCellFactory(TextFieldTableCell.forTableColumn());
                col.setOnEditCommit(new EventHandler<TableColumn.CellEditEvent>() {
                    @Override
                    public void handle(TableColumn.CellEditEvent cellEditEvent) {
                        editCell(cellEditEvent);
                    }
                });
                col.setCellValueFactory(new Callback<TableColumn.CellDataFeatures<ObservableList, String>, ObservableValue<String>>() {
                    public ObservableValue<String> call(TableColumn.CellDataFeatures<ObservableList, String> param) {
                        return new SimpleStringProperty(param.getValue().get(j).toString());
                    }
                });

                table.getColumns().addAll(col);
            }

            /*
            Naplni ObservableList datami
             */
            while (rs.next()) {
                ObservableList<String> row = FXCollections.observableArrayList();
                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                    if (rs.getObject(i) == null) {
                        row.add("No data");
                    } else {
                        row.add(rs.getString(i));
                    }
                }
                rows.add(row);
            }

            table.setItems(rows);
            ContextMenu cm = new ContextMenu();
            MenuItem mi = new MenuItem("Delete");
            StringBuilder row = new StringBuilder();

            table.getSelectionModel().selectedItemProperty().addListener(new ChangeListener() {
                @Override
                public void changed(ObservableValue observableValue, Object o, Object t1) {
                    // TOTO TREBA OPRAVIT!!!
                    if (t1 != null) {
                        row.append(t1.toString());
                    }
                }
            });
            mi.setOnAction(new EventHandler<ActionEvent>() {
                @Override
                public void handle(ActionEvent actionEvent) {
                    String[] rowParts = row.toString().substring(1, row.length() - 1).split(",");
                    int id = 0;
                    for (int i = 0; i < table.getColumns().size(); i++) {
                        if (((TableColumn)table.getColumns().get(i)).getText().matches("id")) {
                            id += Integer.parseInt(rowParts[i].substring(1));
                        }
                    }
                    try {
                        source = dbs_conn.getSource(url.split("/")[0], url.split("/")[1], user, password);
                        Connection conn = source.getConnection();
                        String sql = "DELETE FROM " + table.getId() + " WHERE id = " + String.valueOf(id) + ";";
                        detailTextArea.setText(sql);
                        conn.createStatement().execute(sql);
                    } catch (Exception e) {
                        detailTextArea.setText(e.getMessage());
                    }
                    table.getItems().remove(table.getSelectionModel().getSelectedIndex());
                    table.refresh();
                }
            });
            cm.getItems().add(mi);
            table.addEventHandler(MouseEvent.MOUSE_CLICKED, new EventHandler<MouseEvent>() {
                @Override
                public void handle(MouseEvent mouseEvent) {
                    if (mouseEvent.getButton() == MouseButton.SECONDARY) {
                        cm.show(table, mouseEvent.getScreenX(), mouseEvent.getScreenY());
                    }
                }
            });

        } catch (SQLException e) {
            detailTextArea.textProperty().setValue(e.getMessage());
        } finally {
            try {
                if (conn != null) conn.close();
            } catch (Exception e) {
                detailTextArea.textProperty().setValue(e.getMessage());
            }
        }
    }

    private void editCell(TableColumn.CellEditEvent cellEditEvent) {
        /*
        Zistim novu hodnotu bunky, index stlpca a zvysne hodnoty v riadku
         */
        String newValue = cellEditEvent.getNewValue().toString();
        int columnIndex = cellEditEvent.getTablePosition().getColumn();
        String rawRow = cellEditEvent.getRowValue().toString();
        String[] rowData = rawRow.substring(1, rawRow.length() - 1).split(",");

        /*
        Vytvorim novy riadok s aktualizovanymi hodnotami
         */
        rowData[columnIndex] = newValue;

        try {
            source = dbs_conn.getSource(url.split("/")[0], url.split("/")[1], user, password);
            Connection conn = source.getConnection();
            int id = 0;
            for (int i = 0; i < cellEditEvent.getTableView().getColumns().size(); i++) {
                if (((TableColumn)cellEditEvent.getTableView().getColumns().get(i)).getText().matches("id")) {
                    System.out.println(((TableColumn)cellEditEvent.getTableView().getColumns().get(i)).getText());
                    id += Integer.parseInt(rowData[i].substring(1));
                }
            }
            String sql = "UPDATE " + cellEditEvent.getTableView().getId() + " SET \"" + cellEditEvent.getTableColumn().getText() + "\" = '" + newValue + "' WHERE id = " + id + ";";
            detailTextArea.setText(sql);
            conn.createStatement().execute(sql);
        } catch (Exception e) {
            detailTextArea.setText(e.getMessage());
        }
    }

    private void connectToDbs() {
        Statement stmt;

        dbs_conn conn = new dbs_conn(url, user, password);
        con = conn.connect();

        refreshTables();
    }
}