package employee;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javafx.application.Application;
import static javafx.application.Application.launch;
import javafx.event.ActionEvent;
import javafx.geometry.Insets;
import javafx.geometry.Pos;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.ComboBox;
import javafx.scene.layout.GridPane;
import javafx.stage.Stage;

public class Employee extends Application {
    private GridPane root;
    private ComboBox<String> cboEmployees;
    @Override
    public void start(Stage primaryStage) {

        Button btnCreateCon = new Button();
        btnCreateCon.setText("Connect");
        btnCreateCon.setMinWidth(200);
        btnCreateCon.setOnAction(e -> OnCreateConnection(e));

        Button btnGetData = new Button();
        btnGetData.setText("get EMP");
        btnGetData.setMinWidth(200);
        btnGetData.setOnAction(e -> OnGetData(e));

        cboEmployees = new ComboBox<>();
        cboEmployees.setPromptText("list");
        cboEmployees.setVisibleRowCount(2);

        root = new GridPane();
        root.setAlignment(Pos.CENTER);
        root.setPadding(new Insets(20));
        root.setVgap(20);
        root.addRow(0, btnCreateCon);
        root.addRow(1, btnGetData);

        Scene scene = new Scene(root, 600, 400);


        primaryStage.setScene(scene);
        primaryStage.show();
    }

    public static void main(String[] args) {
        launch(args);
    }

    private void OnCreateConnection(ActionEvent e) {
         Connection con = null;
         if(con == null){
            con = GetConnection();

         }
    }

    private Connection GetConnection(){
        Connection con = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/examdb";
            String user = "root";
            String password = "";
            con = DriverManager.getConnection(url,user,password);
        } catch (ClassNotFoundException | SQLException ex) {
            Logger.getLogger(Employee.class.getName()).log(Level.SEVERE, null, ex);
        }
        return con;
    }

    private void OnGetData(ActionEvent e) {
        ResultSet emp = GetData();
        try{
            while(emp.next())
            {
                int ID = emp.getInt("ID");
                String name = emp.getString("name");
                boolean age = emp.getBoolean("gender");
                String country = emp.getString("country");
                int salary = emp.getInt("salary");


                cboEmployees.getItems().add((ID + " " +name + " "+ age+ " "+ country+ " "+ salary));
            }
        } catch (SQLException ex) {
            Logger.getLogger(Employee.class.getName()).log(Level.SEVERE, null, ex);
        }
        root.addRow(2, cboEmployees);
    }

    private ResultSet GetData(){
        ResultSet results = null;
        Connection con= null;
        try{
            con = GetConnection();
            Statement s = con.createStatement();
            String sqlSelect = "SELECT `ID`, `Name`, `Age`, `Gender`, `Country`, `salary` FROM `employee` WHERE Country = 'jordan' AND salary >=500";
            results = s.executeQuery(sqlSelect);
        } catch (SQLException ex) {
            Logger.getLogger(Employee.class.getName()).log(Level.SEVERE, null, ex);
        }
        return results;
    }

}