package jfxapp;

import javafx.beans.property.SimpleStringProperty;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.EventHandler;
import javafx.fxml.FXML;
import javafx.geometry.Pos;
import javafx.scene.control.TableCell;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.input.MouseButton;
import javafx.scene.input.MouseEvent;
import javafx.scene.layout.AnchorPane;
import javafx.util.Callback;
import javafx.util.converter.DateStringConverter;

import java.sql.*;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

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");

    @FXML private AnchorPane contentPane;

    @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 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 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() {
        System.out.println(String.valueOf(table1.getSelectionModel().getSelectedItems().get(0).get(0)));
    }
}