package jfxapp;

import java.net.URL;
import java.sql.*;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ResourceBundle;

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.fxml.FXML;
import javafx.geometry.Pos;
import javafx.scene.control.Button;
import javafx.scene.control.TableCell;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.layout.AnchorPane;
import javafx.stage.Stage;
import javafx.util.Callback;

public class samexam3 {

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

    private Stage stage = new Stage();

    private String checkF1 = "SELECT EXISTS ( SELECT 1 FROM pg_proc WHERE proname = 'initview');";
    private String checkF2 = "SELECT EXISTS ( SELECT 1 FROM pg_proc WHERE proname = 'initdata');";
    private String checkView = "SELECT EXISTS ( SELECT 1  FROM information_schema.tables " +
            "WHERE table_catalog='fxguidedb' AND table_schema='public' AND table_name='pohled1');";
    private String checkTable = "SELECT EXISTS ( SELECT 1  FROM information_schema.tables " +
            "WHERE table_catalog='fxguidedb' AND table_schema='public' AND table_name='pocty');";

    @FXML private ResourceBundle resources;

    @FXML private URL location;

    @FXML private AnchorPane contentPane;

    @FXML private Button button1;
    @FXML private Button button2;
    @FXML private Button button3;
    @FXML private Button button4;

    @FXML private TableView<ObservableList<ObservableList>> table1;
    @FXML private TableColumn<ObservableList<String>, String> col11;
    @FXML private TableColumn<ObservableList<String>, String> col12;
    @FXML private TableColumn<ObservableList<String>, String> col13;
    @FXML private TableColumn<ObservableList<String>, String> col14;
    @FXML private TableColumn<ObservableList<String>, String> col15;

    @FXML private TableView<ObservableList<ObservableList>> table2;
    @FXML private TableColumn<ObservableList<String>, String> col21;
    @FXML private TableColumn<ObservableList<String>, String> col22;
    @FXML private TableColumn<ObservableList<String>, String> col23;
    @FXML private TableColumn<ObservableList<String>, String> col24;
    @FXML private TableColumn<ObservableList<String>, String> col25;

    @FXML private TableView<ObservableList<ObservableList>> table3;
    @FXML private TableColumn<ObservableList<String>, String> col31;
    @FXML private TableColumn<ObservableList<String>, String> col32;
    @FXML private TableColumn<ObservableList<String>, String> col33;

    @FXML void btn1_Click(ActionEvent event) { showData1(); }

    @FXML void btn2_Click(ActionEvent event) { showData2(); }

    @FXML void btn3_Click(ActionEvent event) { showData3(); }

    @FXML void btn4_Click(ActionEvent event) { kontrola(); }

    @FXML void initialize() {
        button1.setDisable(true);
        button2.setDisable(true);
        button3.setDisable(true);
        button4.setDisable(false);
    }

    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 boolean logOK() {
        Connection c = connDB("fxguide", "fxguide");
        if (c!=null) { try { c.createStatement().execute("SELECT 1"); return true;
        } catch (SQLException e) { e.getMessage(); return false; } }
        else return false; }

    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 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 boolean checkItem (final String query) {
        boolean item = false;
        PreparedStatement pStat = null;
        CONN = connDB("fxguide", "fxguide");
        try { pStat = CONN.prepareStatement(query);
            ResultSet rs = pStat.executeQuery();
            while (rs.next()) { item = rs.getBoolean(1); }
        } catch (SQLException ex) { ex.getMessage(); }
        finally { if (pStat!=null) {
                try { pStat.close(); } catch (SQLException ex) { ex.getMessage(); }
                try { CONN.close(); } catch (SQLException ex) { ex.getMessage(); } } }
        return item; }

    public void runPGF(final String pgfName){
        CallableStatement callStat = null;
        CONN = connDB("fxguide", "fxguide");
        try { callStat = CONN.prepareCall("{call "+pgfName+"()}");
            callStat.execute();
            callStat.close(); }
        catch (SQLException e) { e.getMessage();
        } finally { if (callStat != null) {
            try { callStat.close(); }
            catch (SQLException e) { e.getMessage(); }
            try { CONN.close(); }
            catch (SQLException e) { e.getMessage(); } } } }

    private void kontrola() {
        if (!logOK()) { dialogMessage("Neproběhlo řádné přihlášení, okno bude ukončeno!","Přihlášení do databáze",'E'); onClose(); }
        else if (!checkItem(checkF1) || !checkItem(checkF2)) {
            dialogMessage("Potřebné PG funkce nejsou k dispozici, okno bude ukončeno!","Kontrola PG funkcí",'E');
            onClose(); }
        else if (!checkItem(checkView)) { Integer qm = questMessage("Pohled není k dispozici! Ano - uloží pohled do DB, Ne - zavře okno",
                "Kontrola DB pohledu");
                if (qm==1) onClose();
                    else { runPGF("initview"); kontrola(); }}
        else if (!checkItem(checkTable)) { Integer qm = questMessage("Tabulka není k dispozici! Ano - uloží tabulku do DB, Ne - zavře okno",
                "Kontrola DB tabulky");
            if (qm==1) onClose();
            else { runPGF("initdata"); kontrola(); }}
        else {
            dialogMessage("Proběhlo řádné přihlášení, databáze je přístupná","Přihlášení do databáze",'I');
            dialogMessage("Obě potřebné funkce jsou v databázi k dispozici", "Kontrola PG funkcí", 'I');
            dialogMessage("Potřebné pohledy jsou v databázi k dispozici", "Kontrola DB pohledů", 'I');
            dialogMessage("Potřebná tabulka je v databázi k dispozici", "Kontrola DB tabulky", 'I');
            button1.setDisable(false);
            button2.setDisable(false);
            button3.setDisable(false);
            button4.setDisable(true); }
    }

    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 showData1() {
        final String query = "SELECT * FROM pohled1;";
        final ObservableList data = dataView(query, new Integer[]{0,1,1,1,1});
        final TableColumn[] tc = new TableColumn[]{col11, col12, col13, col14, col15};
        initTableColumn(tc);
        alignTableColumn(tc, new Integer[]{2,1,1,1,2});
        table1.setItems(data);
    }

    private void showData2() {
        final String query = "SELECT * FROM pohled2;";
        final ObservableList data = dataView(query, new Integer[]{0,1,0,1,0});
        final TableColumn[] tc = new TableColumn[]{col21, col22, col23, col24, col25};
        initTableColumn(tc);
        alignTableColumn(tc, new Integer[]{2,1,2,1,2});
        table2.setItems(data);
    }

    private void showData3() {
        final String query = "SELECT * FROM pohled3;";
        final ObservableList data = dataView(query, new Integer[]{1,1,1});
        final TableColumn[] tc = new TableColumn[]{col31, col32, col33};
        initTableColumn(tc);
        alignTableColumn(tc, new Integer[]{2,1,2});
        table3.setItems(data);
    }

    private void onClose() { stage = (Stage) contentPane.getScene().getWindow(); stage.close(); }

}