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

import javafx.application.Application;
import javafx.beans.property.SimpleStringProperty;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.event.EventHandler;
import javafx.scene.Group;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.ComboBox;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TextField;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.paint.Color;
import javafx.stage.Stage;

public class Main extends Application
{
    public static class Pokemon
    {
        private final SimpleStringProperty ID, nama, elemen_1, elemen_2;
        private Pokemon(String ID, String nama, String elemen_1, String elemen_2)
        {
            this.ID = new SimpleStringProperty(ID);
            this.nama = new SimpleStringProperty(nama);
            this.elemen_1 = new SimpleStringProperty(elemen_1);
            this.elemen_2 = new SimpleStringProperty(elemen_2);
        }
        public String getID()
        {
            return ID.get();
        }
        public void setID(String fID)
        {
            ID.set(fID);
        }
        public String getNama()
        {
            return nama.get();
        }
        public void setNama(String fNama)
        {
            nama.set(fNama);
        }
        public String getElemen_1()
        {
            return elemen_1.get();
        }
        public void setElemen_1(String fElemen_1)
        {
            elemen_1.set(fElemen_1);
        }
        public String getElemen_2()
        {
            return elemen_2.get();
        }
        public void setElemen_2(String fElemen_2)
        {
            elemen_2.set(fElemen_2);
        }
    }

    private final TableView<Pokemon> t_pokemon   = new TableView<>();
    private final ObservableList<Pokemon> t_data = FXCollections.observableArrayList();

    private final ComboBox<String> cb_elemen_1     = new ComboBox<String>(),
                                   cb_elemen_2   = new ComboBox<String>();

    @Override
    public void start(Stage stage) throws Exception
    {
        Group root  = new Group();
        Scene scene = new Scene(root, 400, 450, Color.WHITE);
        scene.getStylesheets().add("style.css");

        TableColumn<Pokemon, String>
            t_col_0 = new TableColumn<Pokemon, String>("ID"),
            t_col_1 = new TableColumn<Pokemon, String>("Nama"),
            t_col_2 = new TableColumn<Pokemon, String>("Elemen 1"),
            t_col_3 = new TableColumn<Pokemon, String>("Elemen 2");

        t_col_0.setCellValueFactory(new PropertyValueFactory<>("ID"));
        t_col_1.setCellValueFactory(new PropertyValueFactory<>("nama"));
        t_col_2.setCellValueFactory(new PropertyValueFactory<>("elemen_1"));
        t_col_3.setCellValueFactory(new PropertyValueFactory<>("elemen_2"));

        t_pokemon.setLayoutX(25);
        t_pokemon.setLayoutY(25);
        t_pokemon.setMaxSize(350, 350);

        t_col_0.setPrefWidth(50);
        t_col_1.setPrefWidth(100);
        t_col_2.setPrefWidth(100);
        t_col_3.setPrefWidth(100);

        t_pokemon.setId("my-table");

        t_pokemon.setEditable(true);
        t_pokemon.setItems(t_data);
        t_pokemon.getColumns().addAll(t_col_0, t_col_1, t_col_2, t_col_3);

        root.getChildren().add(t_pokemon);

        retrieveTable();

        TextField tf_nama = new TextField();

        tf_nama.setPromptText("NAMA POKEMON");
        tf_nama.setPrefWidth(100);
        tf_nama.setLayoutX(25);
        tf_nama.setLayoutY(400);

        root.getChildren().add(tf_nama);

        cb_elemen_1.setPromptText("ELEMEN 1");
        cb_elemen_1.setPrefWidth(100);
        cb_elemen_1.setLayoutX(135);
        cb_elemen_1.setLayoutY(400);

        root.getChildren().add(cb_elemen_1);

        cb_elemen_2.setPromptText("ELEMEN 2");
        cb_elemen_2.setPrefWidth(100);
        cb_elemen_2.setLayoutX(245);
        cb_elemen_2.setLayoutY(400);

        root.getChildren().add(cb_elemen_2);

        retrieveComboBox();

        Button btn_add = new Button("+");

        btn_add.setPrefWidth(10);
        btn_add.setLayoutX(355);
        btn_add.setLayoutY(400);

        root.getChildren().add(btn_add);

        btn_add.setOnAction(new EventHandler<ActionEvent>(){

            @Override
            public void handle(ActionEvent arg0)
            {
                if(tf_nama.getText() != null && cb_elemen_1.getValue() != null && cb_elemen_2.getValue() != null)
                {
                    insertTable(
                        tf_nama.getText(),
                        cb_elemen_1.getSelectionModel().getSelectedIndex(),
                        cb_elemen_2.getSelectionModel().getSelectedIndex());
                }
            }
        });

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

    public void retrieveComboBox()
    {
        try
        {
            Class.forName("org.h2.Driver").newInstance();
            Connection connection =
                DriverManager.getConnection("jdbc:h2:tcp://localhost/~/saungkode", "saungkode",  "");

            Statement statement = connection.createStatement();
            ResultSet resultSet =
                statement.executeQuery("SELECT JENIS_ELEMEN FROM ELEMEN");

            cb_elemen_1.getItems().add("-");
            cb_elemen_2.getItems().add("-");

            while(resultSet.next())
            {
                cb_elemen_1.getItems().add(resultSet.getString("JENIS_ELEMEN"));
                cb_elemen_2.getItems().add(resultSet.getString("JENIS_ELEMEN"));
            }

            connection.close();
            statement.close();
            resultSet.close();
        }
        catch(Exception e)
        {
            System.out.println("Error : " + e);
        }
    }

    public void retrieveTable()
    {
        try
        {
            Class.forName("org.h2.Driver").newInstance();
            Connection connection =
                DriverManager.getConnection("jdbc:h2:tcp://localhost/~/saungkode", "saungkode",  "");

            Statement statement = connection.createStatement();
            ResultSet resultSet =
                statement.executeQuery(
                      "SELECT poke.id_pokemon, poke.nama, elm.jenis_elemen "
                    + "FROM pokemon poke, elemen elm "
                    + "WHERE poke.elemen_primer = elm.id_elemen "
                    + "OR poke.elemen_sekunder  = elm.id_elemen");

            t_data.clear();

            String col1 = "",
                   col2 = "",
                   col3 = "",
                   col4 = "";

            boolean odd  = true;
            int beforeID = -1;

            while(resultSet.next())
            {
                col1 = resultSet.getString("ID_POKEMON");

                if(odd)
                {
                    col2 = resultSet.getString("NAMA");
                    col3 = resultSet.getString("JENIS_ELEMEN");
                }
                else
                {
                    if(beforeID == Integer.parseInt(col1))
                    {
                        col4 = resultSet.getString("JENIS_ELEMEN");

                        t_data.add(
                            new Pokemon(col1, col2, col3, col4));
                    }
                    else
                    {
                        col4 = "-";

                        t_data.add(
                            new Pokemon(String.valueOf(beforeID), col2, col3, col4));

                        col2 = resultSet.getString("NAMA");
                        col3 = resultSet.getString("JENIS_ELEMEN");

                        odd = !odd;
                    }
                }

                beforeID = Integer.parseInt(col1);
                odd = !odd;
            }

            connection.close();
            statement.close();
            resultSet.close();
        }
        catch(Exception e)
        {
            System.out.println("Error : " + e);
        }
    }

    public void insertTable(String nama, int elemen_1, int elemen_2)
    {
        try
        {
            Class.forName("org.h2.Driver").newInstance();
            Connection connection =
                DriverManager.getConnection("jdbc:h2:tcp://localhost/~/saungkode", "saungkode",  "");

            String query = "INSERT INTO POKEMON "
                    + "(NAMA, ELEMEN_PRIMER, ELEMEN_SEKUNDER) "
                    + "VALUES('"+nama+"', ";

            if(elemen_1 > 0)
            {
                query += elemen_1;
            }
            else
            {
                query += "null";
            }

            query += ", ";

            if(elemen_2 > 0)
            {
                query += elemen_2;
            }
            else
            {
                query += "null";
            }

            query += ")";

            Statement statement = connection.createStatement();
            statement.executeUpdate(query);

            retrieveTable();

            connection.close();
            statement.close();
        }
        catch(Exception e)
        {
            System.out.println("Error : " + e);
        }
    }
}