/* 
 * Copyright 2016 jmd. All rights reserved.
 * Use of this source code is governed by a Apache 2.0
 * license that can be found in the LICENSE file.
 */
package sk.jtd.taskapp.task.dao.impl;

import java.io.File;
import java.net.URL;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.ejb.EJB;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.ejb.TransactionAttributeType;
import javax.ejb.TransactionManagement;
import javax.ejb.TransactionManagementType;
import javax.sql.DataSource;
import sk.jmd.taskapp.io.TaskAppFile;
import sk.jmd.taskapp.so.AppProperty;
import sk.jmd.taskapp.so.TaskStatus;
import sk.jmd.taskapp.sql.JMD_DELETE_DISABLED_ACTOR;
import sk.jmd.taskapp.sql.JMD_TASK;
import sk.jmd.taskapp.sql.JMD_TASK_APP_PROPERTY;
import sk.jmd.taskapp.sql.JMD_TASK_DATA;
import sk.jmd.taskapp.sql.JMD_TASK_PROPERTY;
import sk.jmd.taskapp.sql.SEQUENCE;
import sk.jmd.taskapp.sql.TABLE;
import sk.jmd.taskapp.sql.dto.JmdTask;
import sk.jmd.taskapp.sql.dto.JmdTaskData;
import sk.jmd.taskapp.sql.dto.JmdTaskProperty;
import sk.jmd.taskapp.task.dao.TaskDao;
import sk.r3n.dto.Dto;
import sk.r3n.jdbc.PostgreSqlBuilder;
import sk.r3n.jdbc.Sql;
import sk.r3n.jdbc.SqlUtil;
import sk.r3n.sql.Column;
import sk.r3n.sql.ColumnFunction;
import sk.r3n.sql.Condition;
import sk.r3n.sql.DataType;
import sk.r3n.sql.Order;
import sk.r3n.sql.Query;
import sk.r3n.util.FileUtil;

@Stateless
@TransactionManagement(TransactionManagementType.BEAN)
public class TaskDaoImpl implements TaskDao {

    private static final Logger LOGGER = Logger.getLogger(TaskDao.class.getCanonicalName());

    private DataSource ds;

    @EJB
    public void setTaskDaoConfig(TaskDaoConfig taskDaoConfig) {
        ds = taskDaoConfig.getDataSource();
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public List<Object[]> directSelect(Query.Select select) {
        Connection connection = null;
        List<Object[]> result;
        try {
            connection = ds.getConnection();
            result = new PostgreSqlBuilder().select(connection, select);
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
        if (LOGGER.isLoggable(Level.FINE)) {
            LOGGER.log(Level.FINE, "RESULT:{0}", result);
        }
        return result;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public JmdTask getTask(Long taskId) {
        Connection connection = null;

        JmdTask result = null;
        try {
            connection = ds.getConnection();

            List<JmdTask> daoResult = new PostgreSqlBuilder().select(connection, Query
                    .SELECT(JMD_TASK.columns())
                    .FROM(TABLE.JMD_TASK())
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId),
                    JmdTask.class
            );
            if (!daoResult.isEmpty()) {
                result = daoResult.get(0);
            }
        } catch (Exception e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
        if (LOGGER.isLoggable(Level.FINE)) {
            LOGGER.log(Level.FINE, "RESULT:{0}", result);
        }
        return result;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public Long addTask(JmdTask task) {
        Connection connection = null;

        Long result = null;
        try {
            connection = ds.getConnection();

            Object[] values = new Dto().toArray(task, JMD_TASK.columns());
            values[0] = SEQUENCE.SQ_JMD_TASK();

            result = (Long) new PostgreSqlBuilder().insert(connection, Query
                    .INSERT()
                    .INTO(TABLE.JMD_TASK(), JMD_TASK.columns())
                    .VALUES(values)
                    .RETURNING(JMD_TASK.ID())
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
        if (LOGGER.isLoggable(Level.FINE)) {
            LOGGER.log(Level.FINE, "RESULT:{0}", result);
        }
        return result;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void deleteTask(Long taskId) {
        Connection connection = null;

        try {
            connection = ds.getConnection();
            connection.setAutoCommit(false);

            List<JmdTaskData> dataList = getDataList(connection, taskId);

            new PostgreSqlBuilder().delete(connection, Query
                    .DELETE()
                    .FROM(TABLE.JMD_TASK())
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );

            new TaskAppFile().deleteFiles(getTmpDir(connection), dataList);
            connection.commit();
        } catch (Exception e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.enableAutoCommit(connection);
            SqlUtil.close(connection);
        }
    }

    private File getTmpDir(Connection connection) throws Exception {
        URL url = new URL(getAppProperty(connection, AppProperty.TMP_DIR.name(), null));
        return FileUtil.toFile(url);
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public TaskStatus getStatus(Long taskId) {
        TaskStatus result = null;

        Connection connection = null;

        try {
            connection = ds.getConnection();

            List<Object[]> rows = new PostgreSqlBuilder().select(connection, Query
                    .SELECT(JMD_TASK.STATUS()).FROM(TABLE.JMD_TASK())
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );

            if (!rows.isEmpty()) {
                result = TaskStatus.valueOf((String) rows.get(0)[0]);
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
        return result;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void setStatus(Long taskId, TaskStatus status) {
        Connection connection = null;

        try {
            connection = ds.getConnection();

            new PostgreSqlBuilder().update(connection, Query
                    .UPDATE(TABLE.JMD_TASK())
                    .SET(JMD_TASK.STATUS(), status.name())
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void setInterruptEnabled(Long taskId, Boolean interruptEnabled) {
        Connection connection = null;

        try {
            connection = ds.getConnection();

            new PostgreSqlBuilder().update(connection, Query
                    .UPDATE(TABLE.JMD_TASK())
                    .SET(JMD_TASK.INTERRUPT_ENABLED(), interruptEnabled)
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void setInterruptFlag(Long taskId, Boolean interruptFlag) {
        Connection connection = null;

        try {
            connection = ds.getConnection();

            new PostgreSqlBuilder().update(connection, Query
                    .UPDATE(TABLE.JMD_TASK())
                    .SET(JMD_TASK.INTERRUPT_FLAG(), interruptFlag)
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void setStopEnabled(Long taskId, Boolean stopEnabled) {
        Connection connection = null;

        try {
            connection = ds.getConnection();

            new PostgreSqlBuilder().update(connection, Query
                    .UPDATE(TABLE.JMD_TASK())
                    .SET(JMD_TASK.STOP_ENABLED(), stopEnabled)
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void setStopFlag(Long taskId, Boolean stopFlag) {
        Connection connection = null;

        try {
            connection = ds.getConnection();

            new PostgreSqlBuilder().update(connection, Query
                    .UPDATE(TABLE.JMD_TASK())
                    .SET(JMD_TASK.STOP_FLAG(), stopFlag)
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void setKillFlag(Long taskId, Boolean killFlag) {
        Connection connection = null;

        try {
            connection = ds.getConnection();

            new PostgreSqlBuilder().update(connection, Query
                    .UPDATE(TABLE.JMD_TASK())
                    .SET(JMD_TASK.KILL_FLAG(), killFlag)
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    public void setTotalStep(Long taskId, Integer totalStep) {
        Connection connection = null;

        try {
            connection = ds.getConnection();

            new PostgreSqlBuilder().update(connection, Query
                    .UPDATE(TABLE.JMD_TASK())
                    .SET(JMD_TASK.TOTAL_STEP(), totalStep)
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    public void setActualStep(Long taskId, Integer actualStep) {
        Connection connection = null;

        try {
            connection = ds.getConnection();

            new PostgreSqlBuilder().update(connection, Query
                    .UPDATE(TABLE.JMD_TASK())
                    .SET(JMD_TASK.ACTUAL_STEP(), actualStep)
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void incStep(Long taskId) {
        Connection connection = null;

        try {
            connection = ds.getConnection();

            Column currentTimestamp = new ColumnFunction("id1", "current_timestamp", DataType.DATE);
            Column inc = new ColumnFunction("id2", JMD_TASK.ACTUAL_STEP().getName() + " + 1", DataType.INTEGER);

            new PostgreSqlBuilder().update(connection, Query
                    .UPDATE(TABLE.JMD_TASK())
                    .SET(JMD_TASK.LAST_UPDATE(), currentTimestamp)
                    .SET(JMD_TASK.ACTUAL_STEP(), inc)
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void incStep(Long taskId, String processMessage) {
        Connection connection = null;

        try {
            connection = ds.getConnection();

            Column currentTimestamp = new ColumnFunction("id1", "current_timestamp", DataType.DATE);
            Column inc = new ColumnFunction("id2", JMD_TASK.ACTUAL_STEP().getName() + " + 1", DataType.INTEGER);

            new PostgreSqlBuilder().update(connection, Query
                    .UPDATE(TABLE.JMD_TASK())
                    .SET(JMD_TASK.LAST_UPDATE(), currentTimestamp)
                    .SET(JMD_TASK.ACTUAL_STEP(), inc)
                    .SET(JMD_TASK.PROCESS_MESSAGE(), processMessage)
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void incStepToTotal(Long taskId) {
        Connection connection = null;

        try {
            connection = ds.getConnection();

            Column currentTimestamp = new ColumnFunction("id", "current_timestamp", DataType.DATE);

            new PostgreSqlBuilder().update(connection, Query
                    .UPDATE(TABLE.JMD_TASK())
                    .SET(JMD_TASK.LAST_UPDATE(), currentTimestamp)
                    .SET(JMD_TASK.ACTUAL_STEP(), JMD_TASK.TOTAL_STEP())
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void setLastUpdate(Long taskId) {
        Connection connection = null;

        try {
            connection = ds.getConnection();

            Column currentTimestamp = new ColumnFunction("id", "current_timestamp", DataType.DATE);

            new PostgreSqlBuilder().update(connection, Query
                    .UPDATE(TABLE.JMD_TASK())
                    .SET(JMD_TASK.LAST_UPDATE(), currentTimestamp)
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void setStarted(Long taskId) {
        Connection connection = null;

        try {
            connection = ds.getConnection();

            Column currentTimestamp = new ColumnFunction("id", "current_timestamp", DataType.DATE);

            new PostgreSqlBuilder().update(connection, Query
                    .UPDATE(TABLE.JMD_TASK())
                    .SET(JMD_TASK.STARTED(), currentTimestamp)
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void setFinished(Long taskId) {
        Connection connection = null;

        try {
            connection = ds.getConnection();

            Column currentTimestamp = new ColumnFunction("id", "current_timestamp", DataType.DATE);

            new PostgreSqlBuilder().update(connection, Query
                    .UPDATE(TABLE.JMD_TASK())
                    .SET(JMD_TASK.FINISHED(), currentTimestamp)
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void setQueueId(Long taskId, String queueId) {
        Connection connection = null;

        try {
            connection = ds.getConnection();

            new PostgreSqlBuilder().update(connection, Query
                    .UPDATE(TABLE.JMD_TASK())
                    .SET(JMD_TASK.QUEUE_ID(), queueId)
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void setQueueNum(Long taskId, Integer queueNum) {
        Connection connection = null;

        try {
            connection = ds.getConnection();

            new PostgreSqlBuilder().update(connection, Query
                    .UPDATE(TABLE.JMD_TASK())
                    .SET(JMD_TASK.QUEUE_NUM(), queueNum)
                    .WHERE(JMD_TASK.ID(), Condition.EQUALS, taskId)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public List<JmdTaskData> getDataList(Long taskId) {
        Connection connection = null;

        List<JmdTaskData> result = new ArrayList<>();
        try {
            connection = ds.getConnection();
            result = getDataList(connection, taskId);
        } catch (Exception e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
        if (LOGGER.isLoggable(Level.FINE)) {
            LOGGER.log(Level.FINE, "RESULT:{0}", result);
        }
        return result;
    }

    private List<JmdTaskData> getDataList(Connection connection, Long taskId) throws Exception {
        List<JmdTaskData> result = new PostgreSqlBuilder().select(connection, Query
                .SELECT(JMD_TASK_DATA.columns())
                .FROM(TABLE.JMD_TASK_DATA())
                .WHERE(JMD_TASK_DATA.TASK_FK(), Condition.EQUALS, taskId),
                JmdTaskData.class
        );
        if (LOGGER.isLoggable(Level.FINE)) {
            LOGGER.log(Level.FINE, "RESULT:{0}", result);
        }
        return result;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public JmdTaskData getData(Long taskId, String code) {
        Connection connection = null;

        JmdTaskData result = null;
        try {
            connection = ds.getConnection();

            List<JmdTaskData> daoResult = new PostgreSqlBuilder().select(connection, Query
                    .SELECT(JMD_TASK_DATA.columns())
                    .FROM(TABLE.JMD_TASK_DATA())
                    .WHERE(JMD_TASK_DATA.TASK_FK(), Condition.EQUALS, taskId)
                    .AND(JMD_TASK_DATA.CODE(), Condition.EQUALS, code),
                    JmdTaskData.class
            );

            if (!daoResult.isEmpty()) {
                result = daoResult.get(0);
            }
        } catch (Exception e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
        if (LOGGER.isLoggable(Level.FINE)) {
            LOGGER.log(Level.FINE, "RESULT:{0}", result);
        }
        return result;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void setData(JmdTaskData data) {
        Connection connection = null;
        try {
            connection = ds.getConnection();

            List<Object[]> daoResult = new PostgreSqlBuilder().select(connection, Query
                    .SELECT(JMD_TASK_DATA.TASK_FK()).COUNT()
                    .FROM(TABLE.JMD_TASK_DATA())
                    .WHERE(JMD_TASK_DATA.TASK_FK(), Condition.EQUALS, data.getTaskFk())
                    .AND(JMD_TASK_DATA.CODE(), Condition.EQUALS, data.getCode())
            );

            if (((Integer) daoResult.get(0)[0]) == 0) {
                new PostgreSqlBuilder().insert(connection, Query
                        .INSERT().INTO(TABLE.JMD_TASK_DATA(), JMD_TASK_DATA.columns())
                        .VALUES(new Dto().toArray(data, JMD_TASK_DATA.columns())));
            } else {
                new PostgreSqlBuilder().update(connection, Query
                        .UPDATE(TABLE.JMD_TASK_DATA())
                        .SET(JMD_TASK_DATA.NAME(), data.getName())
                        .SET(JMD_TASK_DATA.MIME_TYPE(), data.getMimeType())
                        .WHERE(JMD_TASK_DATA.TASK_FK(), Condition.EQUALS, data.getTaskFk())
                        .AND(JMD_TASK_DATA.CODE(), Condition.EQUALS, data.getCode())
                );
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public List<JmdTaskProperty> getProperties(Long taskId) {
        Connection connection = null;

        List<JmdTaskProperty> result = new ArrayList<>();
        try {
            connection = ds.getConnection();

            result = new PostgreSqlBuilder().select(connection, Query
                    .SELECT(JMD_TASK_PROPERTY.columns())
                    .FROM(TABLE.JMD_TASK_PROPERTY())
                    .WHERE(JMD_TASK_PROPERTY.TASK_FK(), Condition.EQUALS, taskId),
                    JmdTaskProperty.class
            );
        } catch (Exception e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
        if (LOGGER.isLoggable(Level.FINE)) {
            LOGGER.log(Level.FINE, "RESULT:{0}", result);
        }
        return result;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public JmdTaskProperty getProperty(Long taskId, String code) {
        Connection connection = null;

        JmdTaskProperty result = null;
        try {
            connection = ds.getConnection();

            List<JmdTaskProperty> daoResult = new PostgreSqlBuilder().select(connection, Query
                    .SELECT(JMD_TASK_PROPERTY.columns())
                    .FROM(TABLE.JMD_TASK_PROPERTY())
                    .WHERE(JMD_TASK_PROPERTY.TASK_FK(), Condition.EQUALS, taskId)
                    .AND(JMD_TASK_PROPERTY.CODE(), Condition.EQUALS, code),
                    JmdTaskProperty.class
            );

            if (!daoResult.isEmpty()) {
                result = daoResult.get(0);
            }
        } catch (Exception e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
        if (LOGGER.isLoggable(Level.FINE)) {
            LOGGER.log(Level.FINE, "RESULT:{0}", result);
        }
        return result;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void setProperty(JmdTaskProperty property) {
        Connection connection = null;
        try {
            connection = ds.getConnection();

            List<Object[]> daoResult = new PostgreSqlBuilder().select(connection, Query
                    .SELECT(JMD_TASK_PROPERTY.TASK_FK()).COUNT()
                    .FROM(TABLE.JMD_TASK_PROPERTY())
                    .WHERE(JMD_TASK_PROPERTY.TASK_FK(), Condition.EQUALS, property.getTaskFk())
                    .AND(JMD_TASK_PROPERTY.CODE(), Condition.EQUALS, property.getCode())
            );

            if (((Integer) daoResult.get(0)[0]) == 0) {
                new PostgreSqlBuilder().insert(connection, Query
                        .INSERT().INTO(TABLE.JMD_TASK_PROPERTY(), JMD_TASK_PROPERTY.columns())
                        .VALUES(new Dto().toArray(property, JMD_TASK_PROPERTY.columns()))
                );
            } else {
                new PostgreSqlBuilder().update(connection, Query
                        .UPDATE(TABLE.JMD_TASK_PROPERTY())
                        .SET(JMD_TASK_PROPERTY.VALUE(), property.getValue())
                        .WHERE(JMD_TASK_PROPERTY.TASK_FK(), Condition.EQUALS, property.getTaskFk())
                        .AND(JMD_TASK_PROPERTY.CODE(), Condition.EQUALS, property.getCode())
                );
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public String getAppProperty(AppProperty appProperty, String defaultValue) {
        Connection connection = null;

        try {
            connection = ds.getConnection();
            return getAppProperty(connection, appProperty.name(), defaultValue);
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    private String getAppProperty(Connection connection, String code, String defaultValue) throws SQLException {
        String result;
        List<Object[]> daoResult = new PostgreSqlBuilder().select(connection, Query
                .SELECT(JMD_TASK_APP_PROPERTY.VALUE())
                .FROM(TABLE.JMD_TASK_APP_PROPERTY())
                .WHERE(JMD_TASK_APP_PROPERTY.CODE(), Condition.EQUALS, code)
        );

        if (!daoResult.isEmpty()) {
            result = (String) daoResult.get(0)[0];
        } else {
            LOGGER.warning(MessageFormat.format("Default value {0} for code {0} will be used!", defaultValue, code));
            result = defaultValue;
        }
        if (LOGGER.isLoggable(Level.FINE)) {
            LOGGER.log(Level.FINE, "RESULT:{0}", result);
        }
        return result;
    }

    @Override
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public void setAppProperty(AppProperty appProperty, String value) {
        Connection connection = null;
        try {
            connection = ds.getConnection();

            List<Object[]> daoResult = new PostgreSqlBuilder().select(connection, Query
                    .SELECT(JMD_TASK_APP_PROPERTY.VALUE()).COUNT()
                    .FROM(TABLE.JMD_TASK_APP_PROPERTY())
                    .WHERE(JMD_TASK_APP_PROPERTY.CODE(), Condition.EQUALS, appProperty.name())
            );

            if (((Integer) daoResult.get(0)[0]) == 0) {
                new PostgreSqlBuilder().insert(connection, Query
                        .INSERT().INTO(TABLE.JMD_TASK_APP_PROPERTY(), JMD_TASK_APP_PROPERTY.CODE(), JMD_TASK_APP_PROPERTY.VALUE())
                        .VALUES(appProperty.name(), value)
                );
            } else {
                new PostgreSqlBuilder().update(connection, Query
                        .UPDATE(TABLE.JMD_TASK_APP_PROPERTY())
                        .SET(JMD_TASK_APP_PROPERTY.VALUE(), value)
                        .WHERE(JMD_TASK_APP_PROPERTY.CODE(), Condition.EQUALS, appProperty.name())
                );
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    public Date getCurrentDate() {
        Connection connection = null;

        Date result;
        try {
            connection = ds.getConnection();

            Sql sql = new Sql();
            sql.SELECT().append("current_timestamp");

            result = (Date) new PostgreSqlBuilder().executeQuery(connection, sql, DataType.TIME_STAMP).get(0)[0];
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
        if (LOGGER.isLoggable(Level.FINE)) {
            LOGGER.log(Level.FINE, "RESULT:{0}", result);
        }
        return result;
    }

    @Override
    public List<String> getDeleteDisabledActors() {
        Connection connection = null;

        List<String> result = new ArrayList<>();
        try {
            connection = ds.getConnection();

            List<Object[]> rows = new PostgreSqlBuilder().select(connection, Query
                    .SELECT(JMD_DELETE_DISABLED_ACTOR.ACTOR())
                    .FROM(TABLE.JMD_DELETE_DISABLED_ACTOR())
                    .ORDER_BY(JMD_DELETE_DISABLED_ACTOR.ACTOR(), Order.ASC)
            );

            rows.forEach((row) -> {
                result.add((String) row[0]);
            });
        } catch (Exception e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
        if (LOGGER.isLoggable(Level.FINE)) {
            LOGGER.log(Level.FINE, "RESULT:{0}", result);
        }
        return result;
    }

    @Override
    public void addDeleteDisabledActor(String actor) {
        Connection connection = null;
        try {
            connection = ds.getConnection();

            List<Object[]> daoResult = new PostgreSqlBuilder().select(connection, Query
                    .SELECT(JMD_DELETE_DISABLED_ACTOR.ACTOR()).COUNT()
                    .FROM(TABLE.JMD_DELETE_DISABLED_ACTOR())
                    .WHERE(JMD_DELETE_DISABLED_ACTOR.ACTOR(), Condition.EQUALS, actor)
            );

            if (((Integer) daoResult.get(0)[0]) == 0) {
                new PostgreSqlBuilder().insert(connection, Query
                        .INSERT().INTO(TABLE.JMD_DELETE_DISABLED_ACTOR(), JMD_DELETE_DISABLED_ACTOR.ACTOR())
                        .VALUES(actor)
                );
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

    @Override
    public void removeDeleteDisabledActor(String actor) {
        Connection connection = null;
        try {
            connection = ds.getConnection();

            new PostgreSqlBuilder().delete(connection, Query
                    .DELETE().FROM(TABLE.JMD_DELETE_DISABLED_ACTOR())
                    .WHERE(JMD_DELETE_DISABLED_ACTOR.ACTOR(), Condition.EQUALS, actor)
            );
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, e.toString(), e);
            throw new RuntimeException(e);
        } finally {
            SqlUtil.close(connection);
        }
    }

}