package org.rh.db;

import org.rh.stat.engine.StatLib;
import org.rh.stat.engine.StatLibName;
import org.rh.variable.Variable;
import org.rh.variable.VariableType;
import org.rh.variable.transforms.Transform;
import org.rh.variable.transforms.TransformOption;

import java.io.Serializable;
import java.sql.*;
import java.util.*;

public class VariableDAO implements Serializable {

    final private AnalysisDAO analysisDAO = new AnalysisDAO();
    final private TransformDAO transformDAO = new TransformDAO();
    private Map<Integer, Variable> variableDBCache = new HashMap<Integer, Variable>();
    private Map<Integer, Variable> allVariablesCache = new HashMap<Integer, Variable>();
    private Map<String, List<Variable>> labVars = new HashMap<String, List<Variable>>();

    private static VariableDAO singleton;

    private VariableDAO() {

    }

    public static VariableDAO getInstance() throws Exception {
        if (singleton == null) {
            singleton = new VariableDAO();
            try {
                singleton.refreshCache();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return singleton;
    }

    // ***************************************************************************************************************************
    // ****** INSERT NEW VARIABLE ************************************************************************************************
    // ***************************************************************************************************************************
    public synchronized void createVariableRow(Variable variable) throws Exception {//String name, Lab lab, Variable parentVariable, VariableType variableType, boolean isDate, Transform transform) throws Exception {
        final String insertSQL = "INSERT INTO variable (" +
                "lab," +
                "parentVariable," +
                "variableType," +
                "dateFormat, " +
                "name" +
                ")" +
                " VALUES (?,?,?,?,?) ";
                /*"ON DUPLICATE KEY UPDATE " +
                "lab = values(lab)," +
                "parentVariable = values(parentVariable)," +
                "variableType = values(variableType)," +
                "dateFormat = values(dateFormat)," +
                "name = values(name)";*/
        Connection connection = null;
        int id = -999;
        try {
            connection = ConnectionPool.getInstance().getConnection();
            connection.setAutoCommit(false);
            {
                PreparedStatement preparedStatement = connection.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS);

                preparedStatement.setString(1, variable.getLab().getName());

                if (variable.getParentVariable() != null) {
                    preparedStatement.setInt(2, variable.getParentVariable().getId());
                } else {
                    preparedStatement.setInt(2, -1);
                }

                preparedStatement.setString(3, variable.getVariableType().toString());
                preparedStatement.setString(4, variable.getDateFormat());
                preparedStatement.setString(5, variable.getName());
                preparedStatement.executeUpdate();

                ResultSet keys = preparedStatement.getGeneratedKeys();
                keys.next();
                id = keys.getInt(1);
                variable.setId(id);
                preparedStatement.close();
            }

            saveCategoryNames(connection, variable, id);

            if (variable.getTransform() != null) {
                final String transformInsertSQL = "INSERT INTO transform (" +
                        "transformName, " +
                        "variableId, " +
                        "statEngine " +
                        ") VALUES (?,?,?)";

                PreparedStatement preparedStatement = connection.prepareStatement(transformInsertSQL);

                preparedStatement.setString(1, variable.getTransform().getName());
                preparedStatement.setInt(2, id);
                preparedStatement.setString(3, variable.getTransform().getStatLib().getLib());

                preparedStatement.executeUpdate();
                preparedStatement.close();
            }

        /*
        transformId              INT REFERENCES transform (id),
        name                     VARCHAR(100),
        value                    VARCHAR(100),
        transformOptionValueType VARCHAR(100) REFERENCES transformOptionValueTypeENUM (transformOptionValueType),
        statEngine               VARCHAR(100) REFERENCES statEngine (lib)
         */
            if (variable.getTransform() != null && variable.getTransform().getOptions() != null && variable.getTransform().getOptions().size() > 0) {
                final String transformOptionInsertSQL = "INSERT INTO transformOption (" +
                        "variableId, " +
                        "name, " +
                        "value," +
                        "transformOptionValueType" +

                        ") VALUES (?,?,?,?)";


                // now insert the options for this transform if there are any
                Collection<TransformOption> options = variable.getTransform().getOptions().values();
                PreparedStatement optionPreparedStatement = connection.prepareStatement(transformOptionInsertSQL);
                if (options != null && options.size() > 0) {
                    for (TransformOption option : options) {
                        optionPreparedStatement.clearParameters();
                        optionPreparedStatement.setInt(1, id);
                        optionPreparedStatement.setString(2, option.getName());
                        optionPreparedStatement.setString(3, option.getValue().toString());
                        optionPreparedStatement.setString(4, option.getType().getName());
                        optionPreparedStatement.executeUpdate();
                    }
                }
            }

            connection.commit();
            variableDBCache.put(id, variable);
            variable.setId(id);

        } catch (Exception e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
            throw e;
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    // ***************************************************************************************************************************
    // ****** SAVE VARIABLE ******************************************************************************************************
    // ***************************************************************************************************************************

    public synchronized void save(Variable variable) throws Exception {
        Connection connection = ConnectionPool.getInstance().getConnection();
        saveCategoryNames(connection, variable, variable.getId());
        analysisDAO.saveUnivariateStats(connection, variable);
        connection.close();
        variableDBCache.put(variable.getId(), variable);
    }

    // ***************************************************************************************************************************
    // ****** SAVE CATEGORIES ****************************************************************************************************
    // ***************************************************************************************************************************

    public synchronized void saveCategoryNames(Connection connection, Variable variable, int varId) throws Exception {
        final String updateCategories = "INSERT INTO  categoryNames (variableId, idx, value) VALUES (?,?,?)  " +
                "ON DUPLICATE KEY UPDATE " +
                " value = values(value)";

        if (!variable.isCategorical()) {
            return;
        }

        String[] categoryNames = variable.getCategories();
        if (categoryNames == null || categoryNames.length < 1) {
            return;
        }

        PreparedStatement preparedStatement = connection.prepareStatement(updateCategories);

        for (int idx = 0; idx < categoryNames.length; idx++) {
            preparedStatement.clearParameters();
            preparedStatement.setInt(1, varId);
            preparedStatement.setInt(2, idx);
            preparedStatement.setString(3, categoryNames[idx]);
            preparedStatement.executeUpdate();
        }
        preparedStatement.close();
        variableDBCache.put(variable.getId(), variable);
    }

    public synchronized void saveCategoryNames(Variable variable, int varId) throws Exception {
        Connection connection = ConnectionPool.getInstance().getConnection();
        saveCategoryNames(connection, variable, varId);
        connection.close();
    }

    public synchronized List<Variable> getLabVariables(String labName) {
        return labVars.get(labName);
    }

    public synchronized Variable getVariable(int variableId) throws Exception {
        return allVariablesCache.get(variableId);
    }

    public synchronized Map<Integer, Variable> getAllVariablesById() {
        return Collections.unmodifiableMap(allVariablesCache);
    }

    private void setCategories() throws Exception {
        Map<Integer, Map<Integer, String>> categories = new HashMap<Integer, Map<Integer, String>>();
        final String query = "SELECT variableId, idx, value FROM categoryNames";
        Connection connection = ConnectionPool.getInstance().getConnection();
        ResultSet resultSet = connection.createStatement().executeQuery(query);
        while (resultSet.next()) {
            int varId = resultSet.getInt(1);
            int idx = resultSet.getInt(2);
            String value = resultSet.getString(3);

            if (categories.get(varId) == null) {
                categories.put(varId, new HashMap<Integer, String>());
            }

            categories.get(varId).put(idx, value);
        }

        for (int varId : categories.keySet()) {
            Map<Integer, String> catMap = categories.get(varId);
            String[] cats = new String[catMap.size()];
            for (int idx : catMap.keySet()) {
                cats[idx] = catMap.get(idx);
            }
            allVariablesCache.get(varId).updateCategoryNames(cats);
        }
    }

    private void refreshCache() throws Exception {
        Connection connection = ConnectionPool.getInstance().getConnection();


        /* Get all TransformOptions */
        final Map<Integer, List<TransformOption>> variableIdTransformOptionMap = new HashMap<Integer, List<TransformOption>>();
        final String transformOptionQuery = "SELECT variableId, name, value, transformOptionValueType FROM transformOption";
        ResultSet resultSet = connection.createStatement().executeQuery(transformOptionQuery);
        while (resultSet.next()) {
            final int variableId = resultSet.getInt(1);
            final String name = resultSet.getString(2);
            final String value = resultSet.getString(3);
            final Class type = Class.forName(resultSet.getString(4));
            final TransformOption transformOption = new TransformOption(type, name, value);

            if (variableIdTransformOptionMap.get(variableId) == null) {
                variableIdTransformOptionMap.put(variableId, new ArrayList<TransformOption>());
            }

            variableIdTransformOptionMap.get(variableId).add(transformOption);
        }
        resultSet.close();



        /* Get all Transforms */
        final Map<Integer, Transform> variableIdTransformMap = new HashMap<Integer, Transform>();
        final String transformQuery = "SELECT variableId, transformName, statEngine FROM transform";
        resultSet = connection.createStatement().executeQuery(transformQuery);
        while (resultSet.next()) {
            final int variableId = resultSet.getInt(1);
            final String method = resultSet.getString(2);
            final StatLibName statLibName = StatLibName.valueOf(resultSet.getString(3));
            final Transform transform = new Transform(method, variableIdTransformOptionMap.get(variableId), StatLib.getStatLib(statLibName));
            variableIdTransformMap.put(variableId, transform);
        }
        resultSet.close();


        /* Get all Variables */
        variableDBCache = new HashMap<Integer, Variable>();
        allVariablesCache = new HashMap<Integer, Variable>();
        labVars = new HashMap<String, List<Variable>>();
        final String variableByIdQuery = " SELECT id, name, parentVariable, lab, variableType, allTransformsCalculated, dateFormat, isCategorical FROM variable ORDER BY id ASC ";
        resultSet = connection.createStatement().executeQuery(variableByIdQuery);
        while (resultSet.next()) {

            int id = resultSet.getInt(1);
            if (id < 1) {
                continue;
            }
            String name = resultSet.getString(2);

            int parentVariableId = resultSet.getInt(3);
            Variable parent = null;
            if (parentVariableId > 0) {
                parent = this.getVariable(parentVariableId);
            }
            String labName = resultSet.getString(4);
            String variableType = resultSet.getString(5);
            boolean allTransformsCalculated = resultSet.getBoolean(6);
            String dateFormat = resultSet.getString(7);
            boolean isCategorical = resultSet.getBoolean(8);

            // parentVariable, Transform , String name, Lab lab, double[] data, boolean isDate, VariableType variableType, String[] categories)
            Variable variable;
            if (parent != null) {
                Transform transform = variableIdTransformMap.get(id);
                variable = new Variable(id, parent, transform, name.substring(parent.getName().length()), LabDAO.getInstance().getLab(labName), null, dateFormat, VariableType.valueOf(variableType), null);
                transform.setInput(variable.getParentVariable());
                parent.addChild(variable);
            } else {
                variable = new Variable(id, parent, null, name, LabDAO.getInstance().getLab(labName), null, dateFormat, VariableType.valueOf(variableType), null);
                variableDBCache.put(id, variable);
                if (labVars.get(labName) == null) {
                    labVars.put(labName, new ArrayList<Variable>());
                }
                labVars.get(labName).add(variable);
            }
            allVariablesCache.put(id, variable);
        }
        resultSet.close();

        connection.close();
        setCategories();
    }
}