Main

// Homework 5
// Drake Herman
// CIS 357
// Dr. Cho
// 4-10-18
// Desc in first java doc comment

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javafx.application.Application;
import javafx.beans.property.SimpleStringProperty;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.geometry.Insets;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.ComboBox;
import javafx.scene.control.Label;
import javafx.scene.control.ListView;
import javafx.scene.control.PasswordField;
import javafx.scene.control.SelectionMode;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableColumn.CellDataFeatures;
import javafx.scene.control.TableView;
import javafx.scene.control.TextField;
import javafx.scene.input.MouseEvent;
import javafx.scene.layout.GridPane;
import javafx.stage.Stage;
import javafx.stage.WindowEvent;
import javafx.util.Callback;

public class Homework5Herman extends Application{

	private Scene scene1, scene2, scene3; // variables for scenes
	private Stage window; // variable for the main stage
	private Connection connection; // variable for connection

	/**
	 * Homework 5 implements a mechanism for connecting to a database,
	 * searching the tables of the database, filtering specific fields
	 * of the database table, applying conditions to these fields, and
	 * displaying the database itself. This program implements extra
	 * credit numbers 1, 3 (minus all function), and 4.
	 *
	 * @author Drake Herman
	 * @version 1.0
	 * @since 4-10-18
	 *
	 * @param args command-line arguments
	 */
	public static void main(String[] args) {
		launch(args);
	}

	/**
	 * This is the start method which creates the primary stage and
	 * assigns it to the variable window. This method calls loginScreen
	 * and windowClosed. From this method the basis of the program is
	 * executed and instructions on how to handle closing the window
	 * are assigned.
	 *
	 * @param primaryStage creates the stage for the GUI
	 */
	@Override
	public void start(Stage primaryStage) {
		window = primaryStage;

		loginScreen();

		// How to close windows
		window.setOnCloseRequest(e -> windowClosed(e));
	}

	/**
	 * This is the loginScreen method which calls createGridPane,
	 * and clickedConnect. This method creates the login GUI in a
	 * GridPane and allows the user to connect to the database.
	 */
	public void loginScreen() {
		GridPane gdpOne = createGridPane();

		//Creating GUI nodes
		Label lblUsername = new Label("Username :");
		TextField txtUsername = new TextField("ddherman");
		Label lblPassword = new Label("Password :");
		PasswordField pwfPassword = new PasswordField();
		pwfPassword.setText("ddhermancis357d");
		Button btnConnect = new Button("Connect");
		btnConnect.setOnMouseClicked(e -> clickedConnect(e, txtUsername, pwfPassword));

		//Adding to the the GridPane
		gdpOne.add(lblUsername, 0, 0);
		gdpOne.add(txtUsername, 1, 0);
		gdpOne.add(lblPassword, 0, 1);
		gdpOne.add(pwfPassword, 1, 1);
		gdpOne.add(btnConnect, 1, 2);

		scene1 = new Scene(gdpOne); //sets the pane to the scene
		window.setTitle("Login");
		window.setScene(scene1); //sets the scene to the stage
		window.show(); //displays the stage
	}

	/**
	 * This is the inputScreen method which calls the createGridPane and
	 * setTableNames. This method creates the GUI for the input scene,
	 * fills the ComboBox and assigns the action for the execute button.
	 * From this method the user can select tables from the database,
	 * select fields, and apply conditions.
	 *
	 * @throws SQLException no items to be added to the ComboBox
	 */
	public void inputScreen() throws SQLException {
		GridPane gdpTwo = createGridPane();

		//creating GUI nodes
		Label lblTables = new Label("Table :");
		Label lblFields = new Label("Fields :");
		Label lblConditions = new Label("Conditions :");
		TextField txtConditions = new TextField();
		Button btnExecute = new Button("Execute");
		ComboBox<String> cbxTables = setTableNames();
		ListView<String> lstView = new ListView<>();

		//adding to the GridPane
		gdpTwo.add(lblTables, 0, 0);
		gdpTwo.add(cbxTables, 1, 0);
		gdpTwo.add(lblFields, 0, 1);
		gdpTwo.add(lstView, 1, 1);
		gdpTwo.add(lblConditions, 0, 2);
		gdpTwo.add(txtConditions, 1, 2);
		gdpTwo.add(btnExecute, 1, 3);

		//populating the ComboBox
		cbxTables.getSelectionModel().selectedItemProperty().addListener(
				(observable, oldValue, newValue) -> {
			String selectedTable = cbxTables.getValue().toString();
			try {
				lstView.getItems().clear();
				lstView.setItems(getListView(selectedTable));
				lstView.getSelectionModel().setSelectionMode(SelectionMode.MULTIPLE);
			} catch (SQLException ex) {
				System.out.println("No items");
			}
		});

		//action for button execute
		btnExecute.setOnMouseClicked(e -> {
			String Query = createQuery(lstView, cbxTables, txtConditions);
			System.out.println(Query);
			outputScreen(Query);
		});

		scene2 = new Scene(gdpTwo); //sets the pane to the scene
		window.setTitle("Input");
		window.setScene(scene2); //sets the scene to the stage
	}

	/**
	 * This is the outputScreen method which calls the conditionError
	 * method. This method creates a table view and dynamically sizes it to
	 * fit the given query. This method handles null values in the database
	 * by assigning their value to none. In the even an invalid condition
	 * is given this method will call conditionError and display an
	 * error window.
	 *
	 * @param Query the query to be executed
	 */
	@SuppressWarnings("unchecked")
	public void outputScreen(String Query) {
		TableView<String> tableView = new TableView<String>();

		ObservableList data = FXCollections.observableArrayList();

		try {
			ResultSet rs = connection.createStatement().executeQuery(Query);

			for(int i = 0 ; i < rs.getMetaData().getColumnCount(); i++){
        //Setting columns for the tableView
        final int j = i;
        TableColumn col = new TableColumn(rs.getMetaData().getColumnName(i+1));
        col.setCellValueFactory(new Callback<CellDataFeatures<ObservableList, String>,
        		ObservableValue<String>>(){
            public ObservableValue<String> call(CellDataFeatures<ObservableList, String> param) {
                return new SimpleStringProperty(param.getValue().get(j).toString());
            }
        });

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

			//Iterating rows and columns and adding data
			while(rs.next()){
        ObservableList<String> row = FXCollections.observableArrayList();
        for(int i = 1 ; i <= rs.getMetaData().getColumnCount(); i++){
        	if (rs.getString(i) == null) {
        		row.add("none");
        	} else {
            row.add(rs.getString(i));
        	}
        }

        data.add(row);
			}

			tableView.setItems(data);

			tableView.setColumnResizePolicy(TableView.CONSTRAINED_RESIZE_POLICY);
			scene3 = new Scene(tableView); //sets table to scene
			window.setTitle("Output");
			window.setScene(scene3); //sets scene to stage
		} catch (SQLException e) {
			conditionError(); //call for invalid conditions
		}
	}

	/**
	 * This is the windowClosed method which calls the
	 * inputScreen and loginScreen methods. This method handles
	 * switching between screens on closes based on the stage
	 * title. In the even the program does't need to be closed
	 * Completely the method consumes the close request.
	 *
	 * @param e a setOnCloseRequest event
	 */
	public void windowClosed(WindowEvent e) {
		if (window.getTitle() == "Output") {
			try {
				inputScreen(); //goes back to input screen
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.consume(); //keeps the GUI open
		} else if (window.getTitle() == "Input") {
			loginScreen(); //goes back to the login screen
			e.consume(); //keeps the GUI open.
		} else {
			//allows the GUI to close on the login screen
		}
	}

	/**
	 * This is the conditionError method which handles invalid
	 * condition inputs in the inputScreen method. This method
	 * creates a new stage with a GridPane that instructs the user on
	 * how to correct the error they made.
	 */
	public void conditionError() {
		Stage error = new Stage();
		GridPane gdpError = createGridPane();

		//creates the user instructions
		Label lblInstruct = new Label("Make sure only <, >, <=, >=, " +
				"==, AND, or OR are being used.");
		Label lblInstruct2 = new Label("Also check that values are " +
				"surrounded in ' '");

		//adds the labels to the pane
		gdpError.add(lblInstruct, 0, 0);
		gdpError.add(lblInstruct2, 0, 1);

		Scene scene = new Scene(gdpError); //sets the pane to the scene
		error.setTitle("Invalid Conditions Error");
		error.setScene(scene); //sets the scene to the
		error.show();
	}

	/**
	 * This is the clickedConnect method which calls the loadDriver and inputScreen
	 * methods. This method loads the jdbc driver and establishes a connection
	 * from the user to the database. Once a connection has been established the
	 * user is then sent to the input screen.
	 *
	 * @param e a setOnMouseClicked event
	 * @param username the TextField the username is entered in
	 * @param password the PasswordField the password is entered in
	 */
	public void clickedConnect(MouseEvent e, TextField username, PasswordField password) {
		//Load the driver
		loadDriver();

		//Establish the connection
		try {
		  connection = DriverManager.getConnection("jdbc:mysql://10.8.30.49:3306/ddherman" ,
					username.getText(), password.getText());
			System.out.println("Connection successful");

			inputScreen();
		} catch (SQLException e1) {
			System.out.println("Failed to connect");
		}
	}

	/**
	 * This is the setTableNames method which connects to the database and
	 * populates the ComboBox with the table names found. This method is designed
	 * so that if at any point more table names are found they will also be added
	 * to the ComboBox.
	 *
	 * @return a ComboBox with a populated list of table names
	 * @throws SQLException thrown when no tables can be found
	 */
	public ComboBox<String> setTableNames() throws SQLException {
		ComboBox<String> cbx = new ComboBox<>();
		ObservableList<String> tableNames = FXCollections.observableArrayList();

		DatabaseMetaData dbmd = connection.getMetaData();
		ResultSet rs = dbmd.getTables(null, null, "%", null);

		while (rs.next()) {
			String tableName = rs.getString(3); //gets table names

			tableNames.add(tableName); //adds table names to a list
		}

		cbx.setItems(tableNames); //adds the list to the ComboBox

		return cbx;
	}

	/**
	 * This is the getListView method which traverses the given table
	 * for its column names and adds them to a list. This list is then returned
	 * so it can be used to populate a ListView.
	 *
	 * @param table the table in which to add columns from
	 * @return an SQL statement with the given fields
	 * @throws SQLException throws when no columns are found
	 */
	public ObservableList<String> getListView(String table) throws SQLException {
		//list of column names from the table
		ObservableList<String> columnNames = FXCollections.observableArrayList();
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery("SELECT DISTINCT COLUMN_NAME " +
				"FROM INFORMATION_SCHEMA.COLUMNS " +
				"WHERE TABLE_NAME = '" + table + "'");

		//adds column names to the list
		while (rs.next()) {
			columnNames.add(rs.getString("COLUMN_NAME"));
		}

		return columnNames;
	}

	/**
	 * This is the createQuery method which calls the checkConditions method.
	 * This method creates a statement based on the selected table from the
	 * ComboBox and the selected fields from the ListView. The selected items
	 * are then formatted into SQL syntax and added to a string.
	 *
	 * @param columns the ListView for selected fields
	 * @param table the ComboBox for selected table name
	 * @param txt the TextField containing the conditions
	 * @return the finished, formatted SQL statement
	 */
	public String createQuery(ListView<String> columns, ComboBox<String> table, TextField txt) {
		ObservableList<String> selectedItems = columns.getSelectionModel().getSelectedItems();
		String[] theQuery = {"SELECT "}; //adds SELECT to the start of the statement
		selectedItems.forEach((String) -> {
			theQuery[0] += String + ", "; //adds the field name followed by a comma
		});

		//removes the extra end comma
		String editQuery = theQuery[0].substring(0, theQuery[0].length() - 2);
		//adds FROM and the table name to the statement
		editQuery += " FROM " + table.getValue().toString();

		//checks if any conditions have been specified
		editQuery = checkConditions(editQuery, txt);

		return editQuery;
	}

	/**
	 * This is the checkConditions method which adds any conditions
	 * specified in the conditions TextField to the SQL statement from
	 * createQuery and formats them to SQL statement syntax.
	 *
	 * @param query a previously created SQL statement
	 * @param txt the TextField containing the specified conditions
	 * @return an SQL statement with conditions added.
	 */
	public String checkConditions(String query, TextField txt) {
		//check if any conditions have been specified
		if (txt.getText().trim().isEmpty() != true) {
			query += " Where " + txt.getText();
		}

		return query;
	}

	/**
	 * This is the createGridPane method which creates and formats the
	 * GridPanes used in loginScreen and inputScreen.
	 *
	 * @return a formatted GridPane
	 */
	public GridPane createGridPane() {
		GridPane gridPane = new GridPane();
		gridPane.setPadding(new Insets(10, 10, 10, 10));
		gridPane.setVgap(8);
		gridPane.setHgap(8);

		return gridPane;
	}

	/**
	 * This is the loadDriver method which loads the jdbc driver so the
	 * user can connect in the connectClicked method.
	 */
	public void loadDriver() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("Driver Loaded");
		} catch (ClassNotFoundException e1) {
			System.out.println("Driver failed to load");
		}
	}
}