package mara.mybox.controller;

import java.util.List;
import java.util.Optional;
import javafx.beans.value.ObservableValue;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.scene.control.Alert;
import javafx.scene.control.Button;
import javafx.scene.control.ButtonType;
import javafx.scene.control.CheckBox;
import javafx.scene.control.ContextMenu;
import javafx.scene.control.MenuItem;
import javafx.scene.control.SeparatorMenuItem;
import javafx.scene.control.Tab;
import javafx.scene.control.TabPane;
import javafx.scene.input.KeyCode;
import javafx.scene.input.KeyEvent;
import javafx.scene.input.MouseEvent;
import javafx.scene.layout.Region;
import javafx.scene.web.WebView;
import javafx.stage.Modality;
import javafx.stage.Stage;
import mara.mybox.data.QueryCondition;
import mara.mybox.data.QueryCondition.DataOperation;
import mara.mybox.db.DerbyBase;
import mara.mybox.db.TableQueryCondition;
import mara.mybox.fxml.FxmlControl;
import mara.mybox.tools.HtmlTools;
import mara.mybox.value.AppVariables;
import static mara.mybox.value.AppVariables.logger;
import static mara.mybox.value.AppVariables.message;
import mara.mybox.value.CommonValues;

/**
 * @Author Mara
 * @CreateDate 2020-5-2
 * @License Apache License Version 2.0
 */
public class DataAnalysisController<P> extends TableManageController<P> {

    protected String finalTitle, dataQueryString, pageQueryString;
    protected String queryPrefix, sizePrefix, clearPrefix, queryOrder, orderTitle,
            dataQuerySQL, sizeQuerySQL, clearSQL, pageQuerySQL;
    protected String tableDefinition;
    protected DataOperation dataOperation;
    protected QueryCondition queryCondition, exportCondition, clearCondition;
    protected boolean prefixEditable, supportTop;
    protected int topNumber;

    @FXML
    protected TabPane tabsPane;
    @FXML
    protected Tab dataTab, infoTab, settingsTab;
    @FXML
    protected WebView infoView;
    @FXML
    protected GeographyCodeConditionTreeController geoController;
    @FXML
    protected Button dataImportButton, dataExportButton;
    @FXML
    protected CheckBox consoleCheck;

    public DataAnalysisController() {
        prefixEditable = false;
        supportTop = false;
    }

    /*
        Methods need implementation/updates
     */
    public void initSQL() {
//        queryPrefix = "SELECT * FROM " + dataName;
//        sizePrefix = "SELECT count(dataid) FROM " + dataName;
//        clearPrefix = "DELETE FROM " + dataName;
    }

    protected DerbyBase dataTable() {
        return null;
    }

    protected DataExportController dataExporter() {
        return null;
    }

    protected void checkOrderBy() {

    }

    protected String checkWhere() {
        if (geoController == null) {
            return null;
        }
        geoController.check();
        String where = geoController.getFinalConditions();
        if (where == null) {
            popError(message("SetConditionsComments"));
            return null;
        }
        return where;
    }

    protected String checkTitle() {
        if (geoController == null) {
            return null;
        }
        String title = geoController.getFinalTitle();
        if (title == null) {
            popError(message("SetConditionsComments"));
            return null;
        }
        return title;
    }

    protected QueryCondition checkCondition(boolean careOrder) {
        String where = checkWhere();
        if (where == null) {
            return null;
        }
        checkOrderBy();
        String title = checkTitle()
                + (!careOrder || orderTitle == null || orderTitle.isBlank() ? "" : "\n" + orderTitle)
                + (topNumber <= 0 ? "" : "\n" + message("NumberTopDataDaily") + ": " + topNumber);
        return QueryCondition.create()
                .setDataName(dataName)
                .setPrefix(queryPrefix)
                .setWhere(where)
                .setOrder(queryOrder)
                .setTop(topNumber)
                .setTitle(title);
    }

    protected boolean checkQueryCondition() {
        QueryCondition condition = checkCondition(true);
        if (condition == null) {
            popError(message("SetConditionsComments"));
            return false;
        }
        queryCondition = condition.setDataOperation(DataOperation.QueryData);
        return true;
    }

    protected boolean checkClearCondition() {
        QueryCondition condition = checkCondition(false);
        if (condition == null) {
            popError(message("SetConditionsComments"));
            return false;
        }
        clearCondition = condition.setDataOperation(DataOperation.ClearData);
        return true;
    }

    protected boolean checkExportCondition() {
        QueryCondition condition = checkCondition(true);
        if (condition == null) {
            popError(message("SetConditionsComments"));
            return false;
        }
        exportCondition = condition.setDataOperation(DataOperation.ExportData);
        return true;
    }

    protected void setQuerySQL() {
        if (queryCondition == null) {
            return;
        }
        if (queryCondition.getWhere() != null && !queryCondition.getWhere().isBlank()) {
            dataQuerySQL = queryCondition.getPrefix() + " WHERE " + queryCondition.getWhere();
            sizeQuerySQL = sizePrefix + " WHERE " + queryCondition.getWhere();
        } else {
            dataQuerySQL = queryCondition.getPrefix();
            sizeQuerySQL = sizePrefix;
        }
        dataQuerySQL += queryCondition.getOrder() == null || queryCondition.getOrder().isBlank()
                ? "" : " ORDER BY " + queryCondition.getOrder();
        pageQuerySQL = null;

        dataQueryString = dataQuerySQL
                + (queryCondition.getTop() <= 0 ? "" : "</br>" + message("NumberTopDataDaily") + ": " + topNumber);
        pageQueryString = pageQuerySQL;
    }

    @FXML
    public void queryData() {
        if (isSettingValues) {
            return;
        }
        if (!checkQueryCondition()) {
            return;
        }
        TableQueryCondition.write(queryCondition, true);
        loadTableData();
    }

    @Override
    public boolean preLoadingTableData() {
        if (isSettingValues) {
            return false;
        }
        if (queryCondition == null) {
            popError(message("SetConditionsComments"));
            return false;
        }
        if (timer != null) {
            timer.cancel();
            timer = null;
        }
        infoView.getEngine().loadContent​("");
        finalTitle = queryCondition.getTitle().replaceAll("\n", " ");
        setQuerySQL();
        return true;
    }

    protected void setPageSQL() {
        String dataFetch = "OFFSET " + currentPageStart + " ROWS FETCH NEXT " + currentPageSize + " ROWS ONLY";
        pageQuerySQL = dataQuerySQL + " " + dataFetch;
        pageQueryString = pageQuerySQL;
        if (queryCondition != null) {
            queryCondition.setFetch(dataFetch);
        }
        if (pagesNumber > 1) {
            finalTitle = queryCondition.getTitle() + " - " + message("Page") + currentPage;
        }
    }

    @Override
    public List<P> readPageData() {
        setPageSQL();
        return null;
    }

    @Override
    public void postLoadedTableData() {
        if (queryCondition == null) {
            return;
        }
        loadInfo();
    }

    @FXML
    protected void popImportMenu(MouseEvent mouseEvent) {

    }

    @FXML
    protected void popSetMenu(MouseEvent mouseEvent) {

    }


    /*
        Common methods
     */
    @Override
    public void initializeNext() {
        try {
            super.initializeNext();
            initSQL();
            consoleCheck.selectedProperty().addListener(
                    (ObservableValue<? extends Boolean> ov, Boolean oldValue, Boolean newValue) -> {
                        loadInfo();
                    });

        } catch (Exception e) {
            logger.error(e.toString());
        }
    }

    @Override
    public void afterSceneLoaded() {
        try {
            super.afterSceneLoaded();
            setButtons();
            loadInfo();
            if (geoController != null) {
                geoController.setUserController(this);
                geoController.loadTree();
            }
        } catch (Exception e) {
            logger.error(e.toString());
        }
    }

    protected void setButtons() {
        try {
            FxmlControl.removeTooltip(goButton);
            FxmlControl.removeTooltip(clearButton);
            FxmlControl.setTooltip(deleteButton, message("Delete") + "\nDELETE / CTRL+d / ALT+d\n\n"
                    + message("DataDeletedComments"));
            FxmlControl.removeTooltip(setButton);
            FxmlControl.removeTooltip(dataImportButton);
            FxmlControl.removeTooltip(dataExportButton);
            goButton.requestFocus();
        } catch (Exception e) {
            logger.error(e.toString());
        }
    }

    @FXML
    @Override
    public void refreshAction() {
        queryData();
        if (geoController != null) {
            geoController.loadTree();
        }
    }

    public void loadInfo() {
        try {
            String html = "";
            if (queryCondition == null) {
                html += "<b>" + message("SetConditionsComments") + "</b> </br>";
            } else {
                html += "<b>" + message("QueryConditionsName") + ":</b> </br>";
                html += "<font color=\"#2e598a\">" + queryCondition.getTitle().replaceAll("\n", "</br>") + "</font></br></br>";

                html += "<b>" + message("QueryConditions") + ": </b></br>";
                html += "<font color=\"#2e598a\">" + queryCondition.getWhere() + "</font></br></br>";

                if (dataQueryString != null && !dataQueryString.isBlank()) {
                    html += "<b>" + message("DataQuery") + ": </b></br>";
                    html += "<font color=\"#2e598a\">" + dataQueryString + "</font></br>";
                    html += "<b>" + message("DataNumber") + ": </b>" + totalSize + "</br></br>";
                }
                if (queryCondition.getFetch() != null && !queryCondition.getFetch().isBlank()) {
                    html += "<b>" + message("CurrentPage") + ": </b></br>";
                    html += "<font color=\"#2e598a\">" + queryCondition.getFetch() + "</font></br>";
                    html += "<b>" + message("DataNumber") + ": </b>" + tableData.size() + "</br></br>";
                }
            }

            html += loadMoreInfo();

            html = HtmlTools.html(null,
                    consoleCheck.isSelected() ? HtmlTools.ConsoleStyle : HtmlTools.DefaultStyle,
                    html);
            infoView.getEngine().loadContent​(html);

        } catch (Exception e) {
            logger.error(e.toString());
        }
    }

    protected String loadMoreInfo() {
        return "";
    }

    @Override
    protected void checkSelected() {
        if (isSettingValues) {
            return;
        }
        super.checkSelected();
        int selection = tableView.getSelectionModel().getSelectedIndices().size();
        if (setButton != null) {
            setButton.setDisable(selection == 0);
        }
    }

    @FXML
    @Override
    public void clearAction() {
        if (!checkClearCondition()) {
            return;
        }
        TableQueryCondition.write(clearCondition, true);
        clear();
    }

    protected void setClearSQL() {
        clearSQL = clearPrefix + (clearCondition.getWhere().isBlank() ? "" : " WHERE " + clearCondition.getWhere());
    }

    public void clear() {
        if (clearCondition == null) {
            popError(message("SetConditionsComments"));
            return;
        }
        setClearSQL();
        Alert alert = new Alert(Alert.AlertType.CONFIRMATION);
        alert.setTitle(getBaseTitle());
        alert.setContentText(AppVariables.message("SureClearConditions")
                + "\n\n" + clearCondition.getTitle().replaceAll("</br>", "\n")
                + "\n\n" + clearSQL
                + "\n\n" + message("DataDeletedComments")
        );
        alert.getDialogPane().setMinWidth(Region.USE_PREF_SIZE);
        alert.getDialogPane().setMinHeight(Region.USE_PREF_SIZE);
        ButtonType buttonSure = new ButtonType(AppVariables.message("Sure"));
        ButtonType buttonCancel = new ButtonType(AppVariables.message("Cancel"));
        alert.getButtonTypes().setAll(buttonSure, buttonCancel);
        Stage stage = (Stage) alert.getDialogPane().getScene().getWindow();
        stage.setAlwaysOnTop(true);
        stage.toFront();

        Optional<ButtonType> result = alert.showAndWait();
        if (result.get() != buttonSure) {
            return;
        }
        synchronized (this) {
            if (task != null) {
                return;
            }
            task = new SingletonTask<Void>() {
                private int count = 0;

                @Override
                protected boolean handle() {
                    count = dataTable().update(clearSQL);

                    return true;
                }

                @Override
                protected void whenSucceeded() {
                    alertInformation(message("Deleted") + ": " + count);
                    refreshAction();
                }

            };
            openHandlingStage(task, Modality.WINDOW_MODAL);
            Thread thread = new Thread(task);
            thread.setDaemon(true);
            thread.start();
        }
    }

    public void loadAsConditions(QueryCondition condition) {
        queryCondition = condition;
        loadTableData();
    }

    protected String tableDefinition() {
        if (tableDefinition == null) {
            tableDefinition = dataTable().getCreate_Table_Statement();
        }
        return tableDefinition;
    }

    @FXML
    protected void popQueryMenu(MouseEvent mouseEvent) {
        try {
            if (popMenu != null && popMenu.isShowing()) {
                popMenu.hide();
            }
            popMenu = new ContextMenu();
            popMenu.setAutoHide(true);

            MenuItem menu = new MenuItem(message("QueryAsCondition") + "\nF1 / CTRL+q / ALT+Q");
            menu.setOnAction((ActionEvent event) -> {
                queryData();
            });
            popMenu.getItems().add(menu);

            popMenu.getItems().add(new SeparatorMenuItem());
            menu = new MenuItem(message("InputConditions"));
            menu.setOnAction((ActionEvent event) -> {
                QueryCondition condition = QueryCondition.create()
                        .setDataName(dataName)
                        .setDataOperation(DataOperation.QueryData)
                        .setPrefix(queryPrefix);
                DataQueryController controller = (DataQueryController) openStage(CommonValues.DataQueryFxml);
                controller.setValue(this, condition, tableDefinition(), prefixEditable, supportTop);
            });
            popMenu.getItems().add(menu);

            List<QueryCondition> list = TableQueryCondition.readList(dataName,
                    DataOperation.QueryData,
                    AppVariables.fileRecentNumber > 0 ? AppVariables.fileRecentNumber : 15);
            if (list != null && !list.isEmpty()) {
                popMenu.getItems().add(new SeparatorMenuItem());
                menu = new MenuItem(message("RecentUsedConditions"));
                menu.setStyle("-fx-text-fill: #2e598a;");
                popMenu.getItems().add(menu);
                for (QueryCondition condition : list) {
                    menu = new MenuItem(condition.getTitle().replaceAll("</br>", " ").replaceAll("\n", " "));
                    menu.setOnAction((ActionEvent event) -> {
                        queryCondition = condition;
                        loadTableData();
                    });
                    popMenu.getItems().add(menu);
                }
            }

            popMenu.getItems().add(new SeparatorMenuItem());
            menu = new MenuItem(message("MenuClose"));
            menu.setStyle("-fx-text-fill: #2e598a;");
            menu.setOnAction((ActionEvent event) -> {
                popMenu.hide();
                popMenu = null;
            });
            popMenu.getItems().add(menu);

            FxmlControl.locateBelow((Region) mouseEvent.getSource(), popMenu);

        } catch (Exception e) {
            logger.error(e.toString());
        }

    }

    @FXML
    protected void popClearMenu(MouseEvent mouseEvent) {
        try {
            if (popMenu != null && popMenu.isShowing()) {
                popMenu.hide();
            }
            popMenu = new ContextMenu();
            popMenu.setAutoHide(true);

            MenuItem menu = new MenuItem(message("ClearAsCondition") + "\nCTRL+r / ALT+r");
            menu.setOnAction((ActionEvent event) -> {
                clearAction();
            });
            popMenu.getItems().add(menu);

            popMenu.getItems().add(new SeparatorMenuItem());

            menu = new MenuItem(message("InputConditions"));
            menu.setOnAction((ActionEvent event) -> {
                QueryCondition condition = QueryCondition.create()
                        .setDataName(dataName)
                        .setDataOperation(DataOperation.ClearData)
                        .setPrefix(clearPrefix);
                DataQueryController controller = (DataQueryController) openStage(CommonValues.DataQueryFxml);
                controller.setValue(this, condition, tableDefinition(), prefixEditable, supportTop);
            });
            popMenu.getItems().add(menu);

            List<QueryCondition> list = TableQueryCondition.readList(
                    dataName, DataOperation.ClearData,
                    AppVariables.fileRecentNumber > 0 ? AppVariables.fileRecentNumber : 15);
            if (list != null && !list.isEmpty()) {
                popMenu.getItems().add(new SeparatorMenuItem());

                menu = new MenuItem(message("RecentUsedConditions"));
                menu.setStyle("-fx-text-fill: #2e598a;");
                popMenu.getItems().add(menu);

                for (QueryCondition condition : list) {
                    menu = new MenuItem(condition.getTitle().replaceAll("</br>", " ").replaceAll("\n", " "));
                    menu.setOnAction((ActionEvent event) -> {
                        clearCondition = condition;
                        clear();
                    });
                    popMenu.getItems().add(menu);
                }
            }

            popMenu.getItems().add(new SeparatorMenuItem());
            menu = new MenuItem(message("MenuClose"));
            menu.setStyle("-fx-text-fill: #2e598a;");
            menu.setOnAction((ActionEvent event) -> {
                popMenu.hide();
                popMenu = null;
            });
            popMenu.getItems().add(menu);

            FxmlControl.locateBelow((Region) mouseEvent.getSource(), popMenu);

        } catch (Exception e) {
            logger.error(e.toString());
        }

    }

    public void clearAsConditions(QueryCondition condition) {
        clearCondition = condition;
        clear();
    }

    @FXML
    public void exportData() {
        if (!checkExportCondition()) {
            return;
        }
        TableQueryCondition.write(exportCondition, true);
        DataExportController controller = dataExporter();
        controller.setValue(this, exportCondition, tableDefinition(), prefixEditable, supportTop);
    }

    @FXML
    protected void popExportMenu(MouseEvent mouseEvent) {
        try {
            if (popMenu != null && popMenu.isShowing()) {
                popMenu.hide();
            }
            popMenu = new ContextMenu();
            popMenu.setAutoHide(true);

            MenuItem menu;

            menu = new MenuItem(message("ExportAsCondition") + "\nF3 / CTRL+e / ALT+E");
            menu.setOnAction((ActionEvent event) -> {
                exportData();
            });
            popMenu.getItems().add(menu);

            if (queryCondition != null && tableData != null && !tableData.isEmpty()) {
                menu = new MenuItem(message("ExportCurrentPage"));
                menu.setOnAction((ActionEvent event) -> {
                    if (queryCondition == null || tableData == null || tableData.isEmpty()) {
                        popError(message("NoData"));
                        return;
                    }
                    QueryCondition condition = QueryCondition.create()
                            .setDataName(dataName)
                            .setDataOperation(DataOperation.ExportData)
                            .setTitle(finalTitle)
                            .setPrefix(queryCondition.getPrefix())
                            .setWhere(queryCondition.getWhere())
                            .setOrder(queryCondition.getOrder())
                            .setFetch(queryCondition.getFetch())
                            .setTop(queryCondition.getTop());
                    DataExportController controller = dataExporter();
                    controller.currentPage(this, condition, tableDefinition(), prefixEditable, supportTop);
                });
                popMenu.getItems().add(menu);
            }

            popMenu.getItems().add(new SeparatorMenuItem());
            menu = new MenuItem(message("InputConditions"));
            menu.setOnAction((ActionEvent event) -> {
                QueryCondition condition = QueryCondition.create()
                        .setDataName(dataName)
                        .setDataOperation(DataOperation.ExportData)
                        .setPrefix(queryPrefix);
                DataExportController controller = dataExporter();
                controller.setValue(this, condition, tableDefinition(), prefixEditable, supportTop);
            });
            popMenu.getItems().add(menu);

            List<QueryCondition> list = TableQueryCondition.readList(
                    dataName, DataOperation.ExportData,
                    AppVariables.fileRecentNumber > 0 ? AppVariables.fileRecentNumber : 15);
            if (list != null && !list.isEmpty()) {
                popMenu.getItems().add(new SeparatorMenuItem());
                menu = new MenuItem(message("RecentUsedConditions"));
                menu.setStyle("-fx-text-fill: #2e598a;");
                popMenu.getItems().add(menu);
                for (QueryCondition condition : list) {
                    menu = new MenuItem(condition.getTitle().replaceAll("</br>", " ").replaceAll("\n", " "));
                    menu.setOnAction((ActionEvent event) -> {
                        DataExportController controller = dataExporter();
                        controller.setValue(this, condition, tableDefinition(), prefixEditable, supportTop);
                    });
                    popMenu.getItems().add(menu);
                }
            }

            popMenu.getItems().add(new SeparatorMenuItem());
            menu = new MenuItem(message("MenuClose"));
            menu.setStyle("-fx-text-fill: #2e598a;");
            menu.setOnAction((ActionEvent event) -> {
                popMenu.hide();
                popMenu = null;
            });
            popMenu.getItems().add(menu);

            FxmlControl.locateBelow((Region) mouseEvent.getSource(), popMenu);

        } catch (Exception e) {
            logger.error(e.toString());
        }
    }

    @Override
    public void keyHandler(KeyEvent event) {
        KeyCode code = event.getCode();
        if (code != null) {
            switch (code) {
                case F1:
                    queryData();
                    return;
                case F3:
                    exportData();
            }
        }
        super.keyHandler(event);
    }

    @Override
    public void controlHandler(KeyEvent event) {
        if (!event.isControlDown()) {
            return;
        }
        String key = event.getText();
        if (key != null) {
            switch (key) {
                case "q":
                case "Q":
                    queryData();
                    return;
                case "e":
                case "E":
                    exportData();
                    return;
                case "r":
                case "R":
                    clearAction();
            }
        }
        super.controlHandler(event);
    }

    @Override
    public void altHandler(KeyEvent event) {
        if (!event.isAltDown()) {
            return;
        }
        String key = event.getText();
        if (key != null) {
            switch (key) {
                case "q":
                case "Q":
                    queryData();
                    return;
                case "e":
                case "E":
                    exportData();
                    return;
                case "r":
                case "R":
                    clearAction();
            }
        }
        super.altHandler(event);
    }

    @Override
    public boolean leavingScene() {
        try {
            if (timer != null) {
                timer.cancel();
                timer = null;
            }
            if (geoController != null) {
                geoController.leavingScene();
            }
        } catch (Exception e) {
        }
        return super.leavingScene();
    }

}