package jfxapp;

import javafx.application.Platform;
import javafx.beans.property.SimpleStringProperty;
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.geometry.Pos;
import javafx.scene.control.*;
import javafx.scene.input.MouseButton;
import javafx.scene.input.MouseEvent;
import javafx.scene.layout.AnchorPane;
import javafx.scene.layout.GridPane;
import javafx.util.Callback;
import org.joda.time.IllegalFieldValueException;
import org.joda.time.LocalDate;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

import java.sql.*;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;



public class samexam4 {

    private Connection CONN = null;
    private String host = "jdbc:postgresql://127.0.0.1:5432/fxguidedb";
    private NumberFormat nform2 = new DecimalFormat("#,##0.00");

    Integer pid = null;

    private ArrayList<String> data = new ArrayList<String>();

    @FXML private AnchorPane contentPane;
    @FXML private TabPane tabPane;
    @FXML private GridPane grid1;

    @FXML private Button d_Button;
    @FXML private Button a_Button;
    @FXML private Button e_Button;
    @FXML private Button u_Button;

    @FXML private TextField u_ID;
    @FXML private TextField u_Cele;
    @FXML private TextField u_Desetinne;
    @FXML private TextField u_Maledes;
    @FXML private TextField u_Text;
    @FXML private TextField u_Datum;

    @FXML private TableView<ObservableList<ObservableList>> table1;

    @FXML private TableColumn<ObservableList<String>, String> col1;
    @FXML private TableColumn<ObservableList<String>, String> col2;
    @FXML private TableColumn<ObservableList<String>, String> col3;
    @FXML private TableColumn<ObservableList<String>, String> col4;
    @FXML private TableColumn<ObservableList<String>, String> col5;
    @FXML private TableColumn<ObservableList<String>, String> col6;

    @FXML void btn_D_Click(ActionEvent event) { onDelete(); }
    @FXML void btn_A_Click(ActionEvent event) { rec_Update(); }
    @FXML void btn_E_Click(ActionEvent event) { isDate(); }
    @FXML void btn_U_Click(ActionEvent event) { save_Update(); }

    @FXML void initialize() {
        showFmt();
        table1.setOnMouseClicked(new EventHandler<MouseEvent>() {
            @Override
            public void handle(MouseEvent mouseEvent) {
                if (mouseEvent.getButton().equals(MouseButton.PRIMARY)) {
                    if (mouseEvent.getClickCount() == 2) {
                        tableClick(); } } } }); }

    private Connection connDB (final String jmeno, final String heslo) {
        try { Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) { e.printStackTrace(); }
        try { CONN = DriverManager.getConnection(host, jmeno, heslo); return CONN;
        } catch (SQLException e) { e.getMessage(); return CONN; } }

    private Integer questMessage(final String text, final String title) {
        DialogFX dfx = new DialogFX();
        dfx.setType(DialogFX.Type.QUESTION);
        dfx.setTitleText(title);
        dfx.setMessage(text);
        return dfx.showDialog(); }

    private void dialogMessage(final String text, final String title, final char type) {
        DialogFX dfx = new DialogFX();
        dfx.setTitleText(title);
        dfx.setMessage(text);
        switch (type) { case 'A': dfx.setType(DialogFX.Type.ACCEPT); break;
            case 'E': dfx.setType(DialogFX.Type.ERROR); break;
            case 'I': dfx.setType(DialogFX.Type.INFO); break;
        } dfx.showDialog(); }

    private void setFocus(final TextField tf) {
        Platform.runLater(new Runnable() {
            @Override public void run() {
                tf.requestFocus();
            }
        });
    }

    private boolean emptyTF(final TextField field) {
        if(field.getText().isEmpty()) {
            dialogMessage("Pole " + field.getId() + " je prázdné!", "Kontrola editačních polí", 'E');
            setFocus(field);
            return true; }
        else return false; }

    private boolean validLEN(final TextField field, final Integer len){
        if(field.getText().length()>len) {
            dialogMessage("Pole " + field.getId() + " je delší než maximum " + len.toString() + " znaků!", "Kontrola editačních polí", 'E');
            field.setText(field.getText().substring(0,len));
            setFocus(field);
            return true; }
        else  return false; }

    private boolean isInteger(final TextField input) {
        try { Integer.parseInt(input.getText());
            return true; }
        catch (NumberFormatException e) {
            return false; } }

    private boolean validINT(final TextField field){
        if (!isInteger(field)) {
            dialogMessage("Pole " + field.getId() + " neobsahuje celé číslo!", "Kontrola editačních polí", 'E');
            field.setText("");
            setFocus(field);
            return true; }
        else {
            return false; } }

    private boolean isDouble(final TextField input) {
        try { Double.parseDouble(input.getText());
            return true; }
        catch (NumberFormatException e) {
            return false; } }

    private boolean validDBL(final TextField field){
        field.setText(field.getText().replaceFirst(",","."));
        if (!isDouble(field)) {
            dialogMessage("Pole " + field.getId() + " neobsahuje desetinné číslo!", "Kontrola editačních polí", 'E');
            setFocus(field);
            return true;
        } else {
            Double prevod = Double.valueOf(field.getText());
            if(prevod<0.0) {
                dialogMessage("Pole " + field.getId() + " obsahuje záporné číslo!", "Kontrola editačních polí", 'E');
                setFocus(field);
                return true;
            } else {
                Double zustatek = Math.round(prevod * 100) / 100.0;
                field.setText(zustatek.toString());
                return false; } } }

    private void isDate() {

        String input = "11.12.2013";
        LocalDate localDate = null;
        DateTimeFormatter formatter = DateTimeFormat.forPattern("d.M.yyyy");
        try { localDate = formatter.parseLocalDate(input); }
        catch (IllegalFieldValueException e) {  }
        catch (IllegalArgumentException f) { }

        if (localDate != null) {
            System.out.println(input);
            System.out.println(localDate);
        }
        else System.out.println("Spatny format!");
    }

    private boolean isDatum(final TextField input) {
        LocalDate localDate = null;
        DateTimeFormatter formatter = DateTimeFormat.forPattern("d.M.yyyy");
        try { localDate = formatter.parseLocalDate(input.getText());
        return true; }
        catch (IllegalFieldValueException e) { return false; }
        catch (IllegalArgumentException f) { return false; }
    }

    private boolean validDTM(final TextField field){
        if (!isDatum(field)) {
            dialogMessage("Pole " + field.getId() + " neobsahuje datum ve správném formátu!", "Kontrola editačních polí", 'E');
            field.setText("");
            setFocus(field);
            return true; }
        else {
            return false; } }

    private ObservableList<ObservableList> dataView (final String query, final Integer[] cols){
        ObservableList<ObservableList> data = FXCollections.observableArrayList();
        PreparedStatement pStat = null;
        String item = null;
        Integer type = null;
        Double dbl = null;
        CONN = connDB("fxguide", "fxguide");
        try { pStat = CONN.prepareStatement(query);
            ResultSet rs = pStat.executeQuery();
            while (rs.next()) {
                ObservableList<Object> row = FXCollections.observableArrayList();
                for (int i=1;i<=cols.length;i++) {
                    item=rs.getObject(i).toString();
                    type=cols[i-1];
                    switch (type) { case 0: row.add(item); break;
                        case 1: row.add(item.replaceAll("\\s+$", "")); break;
                        case 2: dbl=Double.valueOf(item); row.add(nform2.format(dbl)); break;
                        case 3: row.add(item.substring(8) + "." + item.substring(5, 7) + "." + item.substring(0, 4)); break;
                        case 4: dbl=Double.valueOf(item);  row.add((dbl < 0.001 && dbl >-0.001) ? "" : nform2.format(dbl)); break; } }
                data.add(row); }
        } catch (SQLException e) { e.getMessage(); }
        finally {
            if (pStat != null) {
                try { pStat.close(); }
                catch (SQLException e) { e.getMessage(); }
                try { CONN.close(); }
                catch (SQLException e) { e.getMessage(); }} }
        return data;
    }

    private void initTableColumn(final TableColumn[] column) {
        for (int i=0;i<column.length;i++) {
            final int finalI = i;
            column[i].setCellValueFactory(new Callback<TableColumn.CellDataFeatures<ObservableList<String>, String>, ObservableValue<String>>() {
                @Override
                public ObservableValue<String> call(TableColumn.CellDataFeatures<ObservableList<String>, String> param) {
                    return new SimpleStringProperty(param.getValue().get(finalI));
                } }); } }

    public void alignTableColumn(final TableColumn[] column, final Integer[] pos) {
        for (int i=0;i<column.length;i++) {
            final int finalI = i;
            column[i].setCellFactory(new Callback<TableColumn<ObservableList<String>, String>, TableCell<ObservableList<String>, String>>() {
                @Override
                public TableCell<ObservableList<String>, String> call(TableColumn<ObservableList<String>, String> param) {
                    TableCell<ObservableList<String>, String> tc = new TableCell<ObservableList<String>, String>() {
                        @Override
                        public void updateItem(String item, boolean empty) {
                            if (item != null) { setText(item); } } };
                    switch (pos[finalI]) {
                        case 1: tc.setAlignment(Pos.CENTER_LEFT); break;
                        case 2: tc.setAlignment(Pos.CENTER); break;
                        case 3: tc.setAlignment(Pos.CENTER_RIGHT); break; }
                    return tc;
                } }); } }

    private void showRaw() {
        final String query = "SELECT * FROM udaje;";
        final ObservableList data = dataView(query, new Integer[]{0,0,0,0,0,0});
        final TableColumn[] tc = new TableColumn[]{col1, col2, col3, col4, col5, col6};
        initTableColumn(tc);
        alignTableColumn(tc, new Integer[]{1,1,1,1,1,1});
        table1.setItems(data);
    }

    private void showFmt() {
        final String query = "SELECT * FROM udaje;";
        final ObservableList data = dataView(query, new Integer[]{0,0,2,4,1,3});
        final TableColumn[] tc = new TableColumn[]{col1, col2, col3, col4, col5, col6};
        initTableColumn(tc);
        alignTableColumn(tc, new Integer[]{2,3,3,3,1,2});
        table1.setItems(data);
    }

    private void tableClick() {
        pid = Integer.valueOf(String.valueOf(table1.getSelectionModel().getSelectedItems().get(0).get(0)));
        u_ID.setText(pid.toString());
        u_Cele.setText(String.valueOf(table1.getSelectionModel().getSelectedItems().get(0).get(1)));
        u_Desetinne.setText(String.valueOf(table1.getSelectionModel().getSelectedItems().get(0).get(2)));
        u_Maledes.setText(String.valueOf(table1.getSelectionModel().getSelectedItems().get(0).get(3)));
        u_Text.setText(String.valueOf(table1.getSelectionModel().getSelectedItems().get(0).get(4)));
        u_Datum.setText(String.valueOf(table1.getSelectionModel().getSelectedItems().get(0).get(5)));
        tabPane.getSelectionModel().select(1);
        tf_RO();
        d_Button.setDisable(false);
        a_Button.setDisable(false);
        u_Button.setDisable(true);
        e_Button.setDisable(true);
        System.out.println(String.valueOf(table1.getSelectionModel().getSelectedItems().get(0).get(0)));
    }

    private void deleteRec (final String tablename, final String colname, final Integer item) {
        CONN = connDB("fxguide", "fxguide");
        CallableStatement calStat = null;
        try { calStat = CONN.prepareCall("{call delrow(?,?,?)}");
            calStat.setString(1, tablename);
            calStat.setString(2, colname);
            calStat.setInt(3, item);
            calStat.execute();
            calStat.close();
        } catch (SQLException e) { e.getMessage();
        } finally {
            if (calStat != null) {
                try {
                calStat.close(); }
            catch (SQLException e) { e.getMessage(); }
                try { CONN.close(); }
                catch (SQLException e) { e.getMessage(); } } } }

    private void sqlQuery(final String query){
        CONN = connDB("fxguide", "fxguide");
        PreparedStatement pStat = null;
        try { CONN.setAutoCommit(false);
            pStat = CONN.prepareStatement(query);
            pStat.executeUpdate();
            CONN.commit();
        } catch (SQLException e) { e.getMessage();
            try { CONN.rollback(); }
            catch (SQLException e1) { e1.getMessage(); }
        } finally { if (pStat != null) {
            try { pStat.close(); }
            catch (SQLException e) { e.getMessage(); } }
            try { CONN.setAutoCommit(true); }
            catch (SQLException e) { e.getMessage(); }
            try { CONN.close(); }
            catch (SQLException e) { e.getMessage(); }} }

    private void onSQL() {
        //String sql="DELETE FROM udaje WHERE id=1;";
        String sql = "DELETE FROM udaje WHERE id="+u_ID.getText()+";";
        Integer quest = questMessage("Je vybrána položka ID =" +u_ID.getText()+" , opravdu chcete vybranou položku smazat?", "Mazání záznamu");
        if (quest==0) {
        sqlQuery(sql);
        showFmt();
        tabPane.getSelectionModel().select(0); } }

    private void onDelete() {
        Integer quest = questMessage("Je vybrána položka ID =" +pid.toString()+" , opravdu chcete vybranou položku smazat?", "Mazání záznamu");
        if (quest==0) {
            deleteRec("udaje", "id", pid);
            showFmt();
            tabPane.getSelectionModel().select(0);
        }
    }

    private void tf_RO() {
        u_Cele.setEditable(false);
        u_Desetinne.setEditable(false);
        u_Maledes.setEditable(false);
        u_Text.setEditable(false);
        u_Datum.setEditable(false); }

    private void tf_RW() {
        u_Cele.setEditable(true);
        u_Desetinne.setEditable(true);
        u_Maledes.setEditable(true);
        u_Text.setEditable(true);
        u_Datum.setEditable(true); }

    private void rec_Update() {
        tf_RW();
        u_Button.setDisable(false);
        e_Button.setDisable(false);
        u_Cele.requestFocus();
        data.clear();
        data.add(u_Cele.getText());
        data.add(u_Desetinne.getText());
        data.add(u_Maledes.getText());
        data.add(u_Text.getText());
        data.add(u_Datum.getText());
    }

    private void save_Update() {
        if (emptyTF(u_Cele)) return;
        else if (emptyTF(u_Desetinne)) return;
        else if (emptyTF(u_Maledes)) return;
        else if (emptyTF(u_Text)) return;
        else if (emptyTF(u_Datum)) return;
        else if (validLEN(u_Text, 32)) return;
        else if (validINT(u_Cele)) return;
        else if (validDBL(u_Desetinne)) return;
        else if (validDBL(u_Maledes)) return;
        else if (validDTM(u_Datum)) return;
        else {

        }
    }

}