package id.iptek.utms.agent.dao;

import id.iptek.utms.agent.db.DBConn;
import id.iptek.utms.agent.db.DBUtil;
import id.iptek.utms.agent.db.DatabaseException;
import id.iptek.utms.agent.model.ApplicationExt;
import id.iptek.utms.agent.model.DownloadTask;
import id.iptek.utms.agent.model.PendingDownloadTask;
import id.iptek.utms.agent.model.TaskAckReq;
import id.iptek.utms.agent.model.enumeration.TaskStatus;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author Jaka
 */
public class DownloadTaskDao {

    private Logger logger = LoggerFactory.getLogger(getClass());

    public DownloadTaskDao() {
    }

    /*
    public Date getCurrentTime() throws DatabaseException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Date currenTime = null;
        try {
            conn = DBConn.getInstance().getConnection();
            pstmt = conn.prepareStatement(""
                    + "select now();");
            rs = pstmt.executeQuery();
            if (rs.next()) {
                currenTime = rs.getTimestamp(1);
            }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return currenTime;
    }

    private boolean updateDownloadTaskLogStatus(Connection conn, TaskAckReq ackReq) throws SQLException {
        boolean saved = false;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(""
                    + "update tms_download_task set status=4,update_ts=NOW(),updated_by=? where id=((select task_id from tms_download_task_log where id = ?::uuid))");
            int idx = 1;
            pstmt.setString(idx++, ackReq.getTerminalSN());
            pstmt.setString(idx++, ackReq.getAckId());
            saved = pstmt.executeUpdate() >= 1;
        } finally {
            DBUtil.close(pstmt);
        }
        return saved;
    }

    public List<PendingDownloadTask> getPendingTaskForTerminal(String terminalSN) throws DatabaseException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<PendingDownloadTask> list = new ArrayList<>();
        try {
            conn = DBConn.getInstance().getConnection();
            pstmt = conn.prepareStatement(""
                    + "select  "
                    + "	terminal.sn, "
                    + "	task.id,task.\"name\",task.download_time_type,task.download_time, "
                    + "	task.installation_time_type,task.installation_time,task.installation_notification, "
                    + "	log.id log_id, "
                    + "	app.id app_id,app.\"name\" app_name,app.package_name,app.app_version,app.company_name, "
                    + "	app.uninstallable,app.description,app.apk_id, "
                    + " file.id file_id,file.ext,file.create_date, "
                    + " app.checksum,app.download_url "
                    + "from public.tms_download_task task "
                    + "	inner join public.tms_download_task_log log on log.task_id=task.id "
                    + "	inner join public.tms_terminal terminal on log.terminal_id=terminal.id and terminal.delete_ts is null "
                    + "	inner join public.tms_terminal_group_link tglink on tglink.terminal_id=terminal.id "
                    + "	inner join public.tms_download_task_terminal_group_link glink on glink.download_task_id=task.id and glink.group_id=tglink.terminal_group_id "
                    + "	inner join public.tms_download_task_application_link link on link.download_task_id=task.id and link.application_id=log.application_id  "
                    + "	inner join public.tms_application app on link.application_id=app.id "
                    + "	inner join public.sys_file file on app.apk_id=file.id "
                    + "where lower(terminal.sn)=? "
                    + "	and log.activity=0");
            pstmt.setString(1, terminalSN.toLowerCase());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                PendingDownloadTask task = new PendingDownloadTask();
                task.setTerminalSN(rs.getString(1));
                task.setId(rs.getString(2));
                task.setName(rs.getString(3));
                task.setDownloadTimeType(rs.getInt(4));
                task.setDownloadTime(rs.getTimestamp(5));
                task.setInstallationTimeType(rs.getInt(6));
                task.setInstallationTime(rs.getTimestamp(7));
                task.setInstallationNotification(rs.getInt(8));
                task.setLogId(rs.getString(9));
                Application app = new Application();
                app.setId(rs.getString(10));
                app.setAppName(rs.getString(11));
                app.setPackageName(rs.getString(12));
                app.setVersion(rs.getString(13));
                app.setCompanyName(rs.getString(14));
                app.setUninstallable(rs.getBoolean(15));
                app.setDescription(rs.getString(16));
                app.setApkId(rs.getString(17));
                app.setFileId(rs.getString(18));
                app.setFileExt(rs.getString(19));
                app.setFileCreateDate(rs.getDate(20));
                app.setChecksum(rs.getString(21));
                app.setDownloadUrl(rs.getString(22));
                task.setApp(app);
                list.add(task);
            }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return list;
    }

    public List<PendingDownloadTask> getPendingTaskForOnlineTerminals(int minutes, int limit) throws DatabaseException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<PendingDownloadTask> list = new ArrayList<>();
        try {
            conn = DBConn.getInstance().getConnection();
            pstmt = conn.prepareStatement(""
                    + "select  "
                    + "	terminal.sn, "
                    + "	task.id,task.\"name\",task.download_time_type,task.download_time, "
                    + "	task.installation_time_type,task.installation_time,task.installation_notification, "
                    + "	log.id log_id, "
                    + "	app.id app_id,app.\"name\" app_name,app.package_name,app.app_version,app.company_name, "
                    + "	app.uninstallable,app.description,app.apk_id, "
                    + " file.id file_id,file.ext,file.create_date, "
                    + " app.checksum,app.download_url "
                    + "from public.tms_download_task task "
                    + "	inner join public.tms_download_task_log log on log.task_id=task.id "
                    + "	inner join public.tms_terminal terminal on log.terminal_id=terminal.id and terminal.delete_ts is null and terminal.heartbeat_status=1 "
                    + "	inner join public.tms_terminal_group_link tglink on tglink.terminal_id=terminal.id "
                    + "	inner join public.tms_download_task_terminal_group_link glink on glink.download_task_id=task.id and glink.group_id=tglink.terminal_group_id "
                    + "	inner join public.tms_download_task_application_link link on link.download_task_id=task.id and link.application_id=log.application_id  "
                    + "	inner join public.tms_application app on link.application_id=app.id "
                    + "	inner join public.sys_file file on app.apk_id=file.id "
                    + "where (task.broadcast is null or task.broadcast=false) and log.activity=0 and (log.last_broadcast_ts is null or (log.last_broadcast_ts < current_timestamp - interval '"+minutes+" minutes')) "
                    + "order by task.create_ts desc,log.last_broadcast_ts asc "
                    + "limit ? offset 0");
            pstmt.setInt(1, limit);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                PendingDownloadTask task = new PendingDownloadTask();
                task.setTerminalSN(rs.getString(1));
                task.setId(rs.getString(2));
                task.setName(rs.getString(3));
                task.setDownloadTimeType(rs.getInt(4));
                task.setDownloadTime(rs.getTimestamp(5));
                task.setInstallationTimeType(rs.getInt(6));
                task.setInstallationTime(rs.getTimestamp(7));
                task.setInstallationNotification(rs.getInt(8));
                task.setLogId(rs.getString(9));
                Application app = new Application();
                app.setId(rs.getString(10));
                app.setAppName(rs.getString(11));
                app.setPackageName(rs.getString(12));
                app.setVersion(rs.getString(13));
                app.setCompanyName(rs.getString(14));
                app.setUninstallable(rs.getBoolean(15));
                app.setDescription(rs.getString(16));
                app.setApkId(rs.getString(17));
                app.setFileId(rs.getString(18));
                app.setFileExt(rs.getString(19));
                app.setFileCreateDate(rs.getDate(20));
                app.setChecksum(rs.getString(21));
                app.setDownloadUrl(rs.getString(22));
                task.setApp(app);
                list.add(task);
            }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return list;
    }

    public List<PendingDownloadTask> getPendingTaskForTerminals(int minutes, int limit) throws DatabaseException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<PendingDownloadTask> list = new ArrayList<>();
        try {
            conn = DBConn.getInstance().getConnection();
            pstmt = conn.prepareStatement(""
                    + "select  "
                    + "	terminal.sn, "
                    + "	task.id,task.\"name\",task.download_time_type,task.download_time, "
                    + "	task.installation_time_type,task.installation_time,task.installation_notification, "
                    + "	log.id log_id, "
                    + "	app.id app_id,app.\"name\" app_name,app.package_name,app.app_version,app.company_name, "
                    + "	app.uninstallable,app.description,app.apk_id, "
                    + " file.id file_id,file.ext,file.create_date, "
                    + " app.checksum,app.download_url "
                    + "from public.tms_download_task task "
                    + "	inner join public.tms_download_task_log log on log.task_id=task.id "
                    + "	inner join public.tms_terminal terminal on log.terminal_id=terminal.id and terminal.delete_ts is null "
                    + "	inner join public.tms_terminal_group_link tglink on tglink.terminal_id=terminal.id "
                    + "	inner join public.tms_download_task_terminal_group_link glink on glink.download_task_id=task.id and glink.group_id=tglink.terminal_group_id "
                    + "	inner join public.tms_download_task_application_link link on link.download_task_id=task.id and link.application_id=log.application_id  "
                    + "	inner join public.tms_application app on link.application_id=app.id "
                    + "	inner join public.sys_file file on app.apk_id=file.id "
                    + "where (task.broadcast is null or task.broadcast=false) and log.activity=0 and (log.last_broadcast_ts is null or (log.last_broadcast_ts < now() - interval '"+minutes+" minutes')) "
                    + "order by task.create_ts desc,log.last_broadcast_ts asc "
                    + "limit ? offset 0");
            pstmt.setInt(1, limit);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                PendingDownloadTask task = new PendingDownloadTask();
                task.setTerminalSN(rs.getString(1));
                task.setId(rs.getString(2));
                task.setName(rs.getString(3));
                task.setDownloadTimeType(rs.getInt(4));
                task.setDownloadTime(rs.getTimestamp(5));
                task.setInstallationTimeType(rs.getInt(6));
                task.setInstallationTime(rs.getTimestamp(7));
                task.setInstallationNotification(rs.getInt(8));
                task.setLogId(rs.getString(9));
                Application app = new Application();
                app.setId(rs.getString(10));
                app.setAppName(rs.getString(11));
                app.setPackageName(rs.getString(12));
                app.setVersion(rs.getString(13));
                app.setCompanyName(rs.getString(14));
                app.setUninstallable(rs.getBoolean(15));
                app.setDescription(rs.getString(16));
                app.setApkId(rs.getString(17));
                app.setFileId(rs.getString(18));
                app.setFileExt(rs.getString(19));
                app.setFileCreateDate(rs.getDate(20));
                app.setChecksum(rs.getString(21));
                app.setDownloadUrl(rs.getString(22));
                task.setApp(app);
                list.add(task);
            }
            logger.debug("Total Pending Size: {}", list.size());
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return list;
    }

    public PendingDownloadTask getNextPendingTaskForBroadcast() throws DatabaseException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        PendingDownloadTask task = null;
        try {
            conn = DBConn.getInstance().getConnection();
            pstmt = conn.prepareStatement(""
                    + "select  "
                    + "	'SN' sn, "
                    + "	task.id,task.\"name\",task.download_time_type,task.download_time, "
                    + "	task.installation_time_type,task.installation_time,task.installation_notification, "
                    + "	'BROADCAST' log_id, "
                    + "	app.id app_id,app.\"name\" app_name,app.package_name,app.app_version,app.company_name, "
                    + "	app.uninstallable,app.description,app.apk_id, "
                    + " file.id file_id,file.ext,file.create_date, "
                    + " app.checksum,app.download_url "
                    + "from public.tms_download_task task "
                    + "	inner join public.tms_download_task_log log on log.task_id=task.id  "
                    + "	inner join public.tms_download_task_application_link link on link.download_task_id=task.id and link.application_id=log.application_id  "
                    + "	inner join public.tms_application app on link.application_id=app.id "
                    + "	inner join public.sys_file file on app.apk_id=file.id "
                    + "where task.broadcast=true and task.status not in (4, 6, 7, 99) "
                    + "order by task.create_ts asc "
                    + "limit 1 offset 0");
            rs = pstmt.executeQuery();
            if (rs.next()) {
                task = new PendingDownloadTask();
                task.setTerminalSN(rs.getString(1)); // dummy SN
                task.setId(rs.getString(2));
                task.setName(rs.getString(3));
                task.setDownloadTimeType(rs.getInt(4));
                task.setDownloadTime(rs.getTimestamp(5));
                task.setInstallationTimeType(rs.getInt(6));
                task.setInstallationTime(rs.getTimestamp(7));
                task.setInstallationNotification(rs.getInt(8));
                task.setLogId(rs.getString(9)); // dummy log id
                Application app = new Application();
                app.setId(rs.getString(10));
                app.setAppName(rs.getString(11));
                app.setPackageName(rs.getString(12));
                app.setVersion(rs.getString(13));
                app.setCompanyName(rs.getString(14));
                app.setUninstallable(rs.getBoolean(15));
                app.setDescription(rs.getString(16));
                app.setApkId(rs.getString(17));
                app.setFileId(rs.getString(18));
                app.setFileExt(rs.getString(19));
                app.setFileCreateDate(rs.getDate(20));
                app.setChecksum(rs.getString(21));
                app.setDownloadUrl(rs.getString(22));
                task.setApp(app);
            }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return task;
    }

    public boolean saveLastBroadcastTs(PendingDownloadTask task) throws DatabaseException {
        boolean saved = false;
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBConn.getInstance().getConnection();
            conn.setAutoCommit(false);

            pstmt = conn.prepareStatement(""
                    + "update tms_download_task_log set last_broadcast_ts=NOW() where id=?::uuid");
            pstmt.setString(1, task.getLogId());
            int affectedRows = pstmt.executeUpdate();
            saved = affectedRows == 1;
            conn.commit();
            conn.setAutoCommit(true);
        } catch (SQLException ex) {
            DBUtil.rollback(conn);
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return saved;
    }

    public List<SimplePendingDownloadTask> getPendingTasksForBroadcast() throws DatabaseException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<SimplePendingDownloadTask> list = new ArrayList<>();
        try {
            conn = DBConn.getInstance().getConnection();
            pstmt = conn.prepareStatement(""
                    + "select  "
                    + "	task.id,task.\"name\" "
                    + "from public.tms_download_task task "
                    + "where task.status not in (4, 6, 7, 99) "
                    + "order by task.create_ts asc");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                SimplePendingDownloadTask task = new SimplePendingDownloadTask();
                task.setId(rs.getString(1));
                task.setName(rs.getString(2));
                task.setGroupIds(getTaskGroupIds(conn, task));
                list.add(task);
            }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return list;
    }

    private List<String> getTaskGroupIds(Connection conn, SimplePendingDownloadTask task) throws DatabaseException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<String> groupIds = new ArrayList<>();
        try {
            pstmt = conn.prepareStatement(""
                    + "select tgl.group_id  "
                    + "from public.tms_download_task task "
                    + "	inner join public.tms_download_task_terminal_group_link tgl on tgl.download_task_id=task.id "
                    + "where task.id = ?::uuid");
            pstmt.setString(1, task.getId());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                groupIds.add(rs.getString(1));
            }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(pstmt);
        }
        return groupIds;
    }

    public PendingDownloadTask getPendingTask(String taskId, String terminalId) throws DatabaseException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        PendingDownloadTask task = null;
        try {
            conn = DBConn.getInstance().getConnection();
            pstmt = conn.prepareStatement(""
                    + "select  "
                    + "	'SN' sn, "
                    + "	task.id,task.\"name\",task.download_time_type,task.download_time, "
                    + "	task.installation_time_type,task.installation_time,task.installation_notification, "
                    + "	log.id log_id, "
                    + "	app.id app_id,app.\"name\" app_name,app.package_name,app.app_version,app.company_name, "
                    + "	app.uninstallable,app.description,app.apk_id, "
                    + " file.id file_id,file.ext,file.create_date, "
                    + " app.checksum,app.download_url "
                    + "from public.tms_download_task task "
                    + "	inner join public.tms_download_task_log log on log.task_id=task.id  "
                    + "	inner join public.tms_download_task_application_link link on link.download_task_id=task.id and link.application_id=log.application_id  "
                    + "	inner join public.tms_application app on link.application_id=app.id "
                    + "	inner join public.sys_file file on app.apk_id=file.id "
                    + "where task.id=?::uuid and log.terminal_id=?::uuid");
            pstmt.setString(1, taskId);
            pstmt.setString(2, terminalId);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                task = new PendingDownloadTask();
                task.setTerminalSN(rs.getString(1)); // dummy SN
                task.setId(rs.getString(2));
                task.setName(rs.getString(3));
                task.setDownloadTimeType(rs.getInt(4));
                task.setDownloadTime(rs.getTimestamp(5));
                task.setInstallationTimeType(rs.getInt(6));
                task.setInstallationTime(rs.getTimestamp(7));
                task.setInstallationNotification(rs.getInt(8));
                task.setLogId(rs.getString(9)); // dummy log id
                Application app = new Application();
                app.setId(rs.getString(10));
                app.setAppName(rs.getString(11));
                app.setPackageName(rs.getString(12));
                app.setVersion(rs.getString(13));
                app.setCompanyName(rs.getString(14));
                app.setUninstallable(rs.getBoolean(15));
                app.setDescription(rs.getString(16));
                app.setApkId(rs.getString(17));
                app.setFileId(rs.getString(18));
                app.setFileExt(rs.getString(19));
                app.setFileCreateDate(rs.getDate(20));
                app.setChecksum(rs.getString(21));
                app.setDownloadUrl(rs.getString(22));
                task.setApp(app);
            }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return task;
    }*/
    
    
    /**
     * Get Unscheduled Tasks
     * @return
     * @throws DatabaseException 
     */
    public List<DownloadTask> getUnscheduledTasks() throws DatabaseException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<DownloadTask> tasks = new ArrayList<>();
        try {
            conn = DBConn.getInstance().getConnection();
            pstmt = conn.prepareStatement(""
                    + "select task.id,task.\"name\",\n" 
                    + "	task.publish_time_type,task.publish_time,\n" 
                    + "	task.download_time_type,task.download_time,\n" 
                    + "	task.installation_time_type,task.installation_time,task.installation_notification,\n" 
                    + "	task.status,task.tenant_id,task.create_ts,task.\"version\" \n" 
                    + "from tms_download_task task\n" 
                    + "where task.delete_ts is null and task.status in (0,1,2) \n" 
                    + "	and (task.publish_time_type=1 \n" 
                    + "	    or (task.publish_time_type=2 and now() >= (task.publish_time - interval '1 hour'))\n" 
                    + "	)\n" 
                    + "order by task.publish_time_type asc, task.publish_time asc");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                DownloadTask task = new DownloadTask();
                task.setId(rs.getString(1));
                task.setName(rs.getString(2));
                task.setPublishTimeType(rs.getInt(3));
                Timestamp ts = rs.getTimestamp(4);
                if(!rs.wasNull()) {
                    task.setPublishTime(ts);
                }
                task.setDownloadTimeType(rs.getInt(5));
                ts = rs.getTimestamp(6);
                if(!rs.wasNull()) {
                    task.setDownloadTime(ts);
                }
                task.setInstallationNotification(rs.getInt(7));
                ts = rs.getTimestamp(8);
                if(!rs.wasNull()) {
                    task.setDownloadTime(ts);
                }
                task.setInstallationNotification(rs.getInt(9));
                task.setStatus(rs.getInt(10));
                tasks.add(task);
           }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return tasks;
    }
    
    public List<ApplicationExt> getTaskApplications(DownloadTask task) throws DatabaseException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<ApplicationExt> apps = new ArrayList<>();
        try {
            conn = DBConn.getInstance().getConnection();
            pstmt = conn.prepareStatement(""
                    + "SELECT app.id,app.\"name\",app.package_name,app.app_version,\n "
                    + "    app.company_name,app.uninstallable,app.description,app.checksum,\n "
                    + "    app.unique_name,applink.download_url,applink.download_url_exp,\n "
                    + "    app.unique_icon_name,app.icon_url,app.icon_url_exp\n "
                    + "FROM \"public\".tms_application app\n "
                    + "    INNER JOIN \"public\".tms_download_task_application_link applink on applink.application_id=app.id\n "
                    + "    INNER JOIN \"public\".tms_download_task task on applink.download_task_id=task.id\n "
                    + "WHERE task.id=?::uuid ");
            pstmt.setString(1, task.getId());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                ApplicationExt app = new ApplicationExt();
                app.setId(rs.getString(1));
                app.setAppName(rs.getString(2));
                app.setPackageName(rs.getString(3));
                app.setVersion(rs.getString(4));
                app.setCompanyName(rs.getString(5));
                app.setUninstallable(rs.getBoolean(6));
                app.setDescription(rs.getString(7));
                app.setChecksum(rs.getString(8));
                app.setUniqueName(rs.getString(9));
                app.setDownloadUrl(rs.getString(10));
                Date downloadUrlExp = rs.getDate(11);
                if(!rs.wasNull()) {
                    app.setDownloadUrlExp(downloadUrlExp);
                }
                app.setUniqueIconName(rs.getString(12));
                app.setIconUrl(rs.getString(13));
                Date iconUrlExp = rs.getDate(14);
                if(!rs.wasNull()) {
                    app.setIconUrlExp(iconUrlExp);
                }
                apps.add(app);
            }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return apps;
    }
    
    public List<String> getFinalTerminalIdForTask(DownloadTask task) throws DatabaseException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<String> terminalIds = new ArrayList<>();
        try {
            conn = DBConn.getInstance().getConnection();
            pstmt = conn.prepareStatement(""
                    + "SELECT DISTINCT t0.terminal_id \n"
                    + "FROM (\n"
                    + "    SELECT tlink.terminal_id\n"
                    + "    FROM \"public\".tms_download_task_terminal_link tlink\n"
                    + "    WHERE tlink.download_task_id=?::uuid\n"
                    + "    UNION ALL\n"
                    + "    SELECT tglink.terminal_id\n"
                    + "    FROM \"public\".tms_download_task_terminal_group_link ttglink\n"
                    + "        INNER JOIN \"public\".tms_terminal_group tgroup on ttglink.group_id=tgroup.id\n"
                    + "        INNER JOIN \"public\".tms_terminal_group_link tglink on tglink.terminal_group_id=tgroup.id\n"
                    + "    WHERE ttglink.download_task_id=?::uuid\n"
                    + ") t0\n");
            pstmt.setString(1, task.getId());
            pstmt.setString(2, task.getId());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                terminalIds.add(rs.getString(1));
            }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return terminalIds;
    }
    
    private boolean updateDownloadTaskStatus(Connection conn, DownloadTask task) throws SQLException {
        boolean saved = false;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(""
                    + "update tms_download_task set status=? where id=?::uuid");
            int idx = 1;
            pstmt.setInt(idx++, task.getStatus().getValue());
            pstmt.setString(idx++, task.getId());
            saved = pstmt.executeUpdate() == 1;
        } finally {
            DBUtil.close(pstmt);
        }
        return saved;
    }
    
    private boolean populateDownloadTask(Connection conn, DownloadTask task) throws SQLException {
        boolean saved = false;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(""
                + "INSERT INTO \"public\".tms_download_task_log (id,create_ts,created_by,\"version\",task_id, \n"
                + "        application_id,activity,terminal_id,terminal_group_id) \n"
                + "SELECT t1.id,t1.create_ts,t1.created_by,t1.\"version\", \n"
                + "    t1.task_id,t1.application_id,t1.activity,t1.terminal_id,t1.terminal_group_id \n"
                + "FROM ( \n"
                + "    SELECT t0.id,t0.create_ts,t0.created_by,t0.\"version\", \n"
                + "        t0.task_id,t0.application_id,t0.activity,t0.terminal_id,t0.terminal_group_id, \n"
                + "        row_number() over (partition by t0.task_id,t0.terminal_id order by t0.terminal_group_id asc) as rownumber \n"
                + "    FROM ( \n"
                + "        -- mapped to terminal group \n"
                + "        SELECT  \n"
                + "            newid() id,now() create_ts,'utms-agent' created_by,1 \"version\", \n"
                + "            task.id task_id,app.id application_id, \n"
                + "            0 activity,tgl.terminal_id,tgl.terminal_group_id \n"
                + "        FROM \"public\".tms_application app \n"
                + "            INNER JOIN \"public\".tms_download_task_application_link applink on applink.application_id=app.id \n"
                + "            INNER JOIN \"public\".tms_download_task task on applink.download_task_id=task.id \n"
                + "            INNER JOIN \"public\".tms_download_task_terminal_group_link dttgl on task.id = dttgl.download_task_id \n"
                + "            INNER JOIN \"public\".tms_terminal_group_link tgl on dttgl.group_id = tgl.terminal_group_id \n"
                + "        WHERE task.id=?::uuid and task.status=1 \n"
                + "        UNION ALL \n"
                + "        -- mapped to terminal \n"
                + "        SELECT  \n"
                + "            newid() id,now() create_ts,'utms-agent' created_by,1 \"version\", \n"
                + "            task.id task_id,app.id application_id, \n"
                + "            0 activity,dttl.terminal_id,null terminal_group_id \n"
                + "        FROM \"public\".tms_application app \n"
                + "            INNER JOIN \"public\".tms_download_task_application_link applink on applink.application_id=app.id \n"
                + "            INNER JOIN \"public\".tms_download_task task on applink.download_task_id=task.id \n"
                + "            INNER JOIN \"public\".tms_download_task_terminal_link dttl on task.id = dttl.download_task_id \n"
                + "        WHERE task.id=?::uuid and task.status=1 \n"
                + "    ) t0 \n"
                + ") t1 \n"
                + "WHERE t1.rownumber=1; \n"
                );
            pstmt.setString(1, task.getId());
            pstmt.setString(2, task.getId());
            saved = pstmt.executeUpdate() > 0;
        } finally {
            DBUtil.close(pstmt);
        }
        return saved;
    }
    
    public List<PendingDownloadTask> getUnpublishedPendingTaskByTerminal(DownloadTask task) throws DatabaseException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<PendingDownloadTask> tasks = new ArrayList<>();
        try {
            conn = DBConn.getInstance().getConnection();
            pstmt = conn.prepareStatement(""
                + "SELECT log.id, \n"
                + "    log.task_id,task.\"name\" task_name, \n"
                + "    task.download_time_type,task.download_time, \n"
                + "    task.installation_time_type,task.installation_time,task.installation_notification, \n"
                + "    log.activity, \n"
                + "    log.terminal_id,terminal.sn,log.terminal_group_id, \n"
                + "    log.application_id app_id,app.\"name\" app_name,app.package_name, \n"
                + "    app.app_version,app.company_name,app.uninstallable,app.description, \n"
                + "    app.checksum,app.unique_name,app.unique_icon_name,app.icon_url,app.icon_url_exp, \n"
                + "    app.file_size,applink.download_url,applink.download_url_exp, \n"
                + "    log.last_broadcast_ts \n"
                + "FROM \"public\".tms_download_task_log log \n"
                + "    INNER JOIN \"public\".tms_download_task task on log.task_id = task.id \n"
                + "    INNER JOIN \"public\".tms_download_task_application_link applink on log.task_id = applink.download_task_id \n"
                + "    INNER JOIN \"public\".tms_terminal terminal on log.terminal_id = terminal.id \n"
                + "    INNER JOIN \"public\".tms_application app on log.application_id = app.id \n"
                + "WHERE log.task_id=?::uuid and log.activity=0 and log.terminal_group_id is null; \n"
                );
            pstmt.setString(1, task.getId());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                PendingDownloadTask pendingTask = new PendingDownloadTask();
                pendingTask.setLogId(rs.getString(1));
                pendingTask.setId(rs.getString(2));
                pendingTask.setName(rs.getString(3));
                pendingTask.setDownloadTimeType(rs.getInt(4));
                Timestamp ts = rs.getTimestamp(5);
                if(!rs.wasNull()) {
                    pendingTask.setDownloadTime(ts);
                }
                pendingTask.setInstallationTimeType(rs.getInt(6));
                ts = rs.getTimestamp(7);
                if(!rs.wasNull()) {
                    pendingTask.setInstallationTime(ts);
                }
                pendingTask.setInstallationNotification(rs.getInt(8));
                pendingTask.setActivity(rs.getInt(9));
                // 10: terminal id
                pendingTask.setTerminalSN(rs.getString(11));
                pendingTask.setTerminalGroupId(rs.getString(12));
                // application
                ApplicationExt app = new ApplicationExt();
                app.setId(rs.getString(13));
                app.setAppName(rs.getString(14));
                app.setPackageName(rs.getString(15));
                app.setVersion(rs.getString(16));
                app.setCompanyName(rs.getString(17));
                app.setUninstallable(rs.getBoolean(18));
                app.setDescription(rs.getString(19));
                app.setChecksum(rs.getString(20));
                app.setUniqueName(rs.getString(21));
                app.setUniqueIconName(rs.getString(22));
                app.setIconUrl(rs.getString(23));
                Date iconUrlExp = rs.getDate(24);
                if(!rs.wasNull()) {
                    app.setIconUrlExp(iconUrlExp);
                }
                app.setFileSize(rs.getLong(25));
                app.setDownloadUrl(rs.getString(26));
                Date downloadUrlExp = rs.getDate(27);
                if(!rs.wasNull()) {
                    app.setDownloadUrlExp(downloadUrlExp);
                }
                pendingTask.setApp(app);
                Date lastBroadcastTs = rs.getDate(28);
                if(!rs.wasNull()) {
                    pendingTask.setLastBroadcastTs(lastBroadcastTs);
                }
                tasks.add(pendingTask);
            }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return tasks;
    }
    
    public List<PendingDownloadTask> getUnpublishedPendingTaskByTerminalGroup(DownloadTask task) throws DatabaseException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<PendingDownloadTask> tasks = new ArrayList<>();
        try {
            conn = DBConn.getInstance().getConnection();
            pstmt = conn.prepareStatement(""
            + "SELECT t0.* from ( \n"
            + "	SELECT log.id,log.task_id,task.\"name\" task_name,  \n"
            + "	    task.download_time_type,task.download_time,  \n"
            + "	    task.installation_time_type,task.installation_time,task.installation_notification,  \n"
            + "	    log.terminal_group_id,  \n"
            + "	    log.application_id app_id,app.\"name\" app_name,app.package_name,  \n"
            + "	    app.app_version,app.company_name,app.uninstallable,app.description,  \n"
            + "	    app.checksum,app.unique_name,app.unique_icon_name,app.icon_url,app.icon_url_exp,  \n"
            + "	    app.file_size,applink.download_url,applink.download_url_exp,  \n"
            + "	    ttgl.last_broadcasted_time, \n"
            + "	    row_number() over (partition by log.task_id,log.terminal_group_id order by ttgl.last_broadcasted_time asc) as rownumber \n"
            + "	FROM \"public\".tms_download_task_log log  \n"
            + "	    INNER JOIN \"public\".tms_download_task task on log.task_id = task.id  \n"
            + "	    INNER JOIN \"public\".tms_download_task_application_link applink on log.task_id = applink.download_task_id  \n"
            + "	    INNER JOIN \"public\".tms_download_task_terminal_group_link ttgl on log.task_id = ttgl.download_task_id and log.terminal_group_id = ttgl.group_id  \n"
            + "	    INNER JOIN \"public\".tms_application app on log.application_id = app.id  \n"
            + "	WHERE log.task_id=?::uuid and log.activity=0  \n"
            + "	    and log.terminal_group_id is not null \n"
            + ") t0 \n"
            + "where t0.rownumber=1; \n"
            );
            pstmt.setString(1, task.getId());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                PendingDownloadTask pendingTask = new PendingDownloadTask();
                pendingTask.setLogId(rs.getString(1));
                pendingTask.setId(rs.getString(2));
                pendingTask.setName(rs.getString(3));
                pendingTask.setDownloadTimeType(rs.getInt(4));
                Timestamp ts = rs.getTimestamp(5);
                if(!rs.wasNull()) {
                    pendingTask.setDownloadTime(ts);
                }
                pendingTask.setInstallationTimeType(rs.getInt(6));
                ts = rs.getTimestamp(7);
                if(!rs.wasNull()) {
                    pendingTask.setInstallationTime(ts);
                }
                pendingTask.setInstallationNotification(rs.getInt(8));
                pendingTask.setTerminalGroupId(rs.getString(9));
                // application
                ApplicationExt app = new ApplicationExt();
                app.setId(rs.getString(10));
                app.setAppName(rs.getString(11));
                app.setPackageName(rs.getString(12));
                app.setVersion(rs.getString(13));
                app.setCompanyName(rs.getString(14));
                app.setUninstallable(rs.getBoolean(15));
                app.setDescription(rs.getString(16));
                app.setChecksum(rs.getString(17));
                app.setUniqueName(rs.getString(18));
                app.setUniqueIconName(rs.getString(19));
                app.setIconUrl(rs.getString(20));
                Date iconUrlExp = rs.getDate(21);
                if(!rs.wasNull()) {
                    app.setIconUrlExp(iconUrlExp);
                }
                app.setFileSize(rs.getLong(22));
                app.setDownloadUrl(rs.getString(23));
                Date downloadUrlExp = rs.getDate(24);
                if(!rs.wasNull()) {
                    app.setDownloadUrlExp(downloadUrlExp);
                }
                pendingTask.setApp(app);
                Date lastBroadcastTs = rs.getDate(25);
                if(!rs.wasNull()) {
                    pendingTask.setLastBroadcastTs(lastBroadcastTs);
                }
                tasks.add(pendingTask);
            }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return tasks;
    }
    
    public boolean populateAndBuildDownloadTaskLogs(DownloadTask task) throws DatabaseException {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        boolean saved = false;
        try {
            conn = DBConn.getInstance().getConnection();
            conn.setAutoCommit(false);
            // update download task status
            task.setStatus(TaskStatus.NOT_STARTED.getValue());
            saved = updateDownloadTaskStatus(conn, task);
            logger.info("Update download task status: {} ? {}", task.getId(), saved);
            if(saved) {
                // populate task logs
                saved &= populateDownloadTask(conn, task);
                logger.info("Populate download task log: {} ? {}", task.getId(), saved);
            }
            conn.commit();
            conn.setAutoCommit(true);
        } catch (SQLException ex) {
            DBUtil.rollback(conn);
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, cstmt, rs);
        }
        return saved;
    }
    
    public boolean updateGroupLastBroadcastTs(PendingDownloadTask task) throws DatabaseException {
        boolean saved = false;
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBConn.getInstance().getConnection();
            conn.setAutoCommit(false);

            pstmt = conn.prepareStatement(""
                    + "update tms_download_task_terminal_group_link set last_broadcasted_time=now() where download_task_id=?::uuid and group_id=?::uuid");
            int idx = 1;
            pstmt.setString(idx++, task.getId());
            pstmt.setString(idx++, task.getTerminalGroupId());
            saved = pstmt.executeUpdate() == 1;
            
            conn.commit();
            conn.setAutoCommit(true);
        } catch (SQLException ex) {
            DBUtil.rollback(conn);
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(pstmt);
            DBUtil.close(conn);
        }
        return saved;
    }
    
    public boolean updateTerminalLastBroadcastTs(PendingDownloadTask task) throws DatabaseException {
        boolean saved = false;
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBConn.getInstance().getConnection();
            conn.setAutoCommit(false);

            pstmt = conn.prepareStatement(""
                    + "update tms_download_task_log set last_broadcast_ts=now() where id=?::uuid");
            int idx = 1;
            pstmt.setString(idx++, task.getLogId());
            saved = pstmt.executeUpdate() == 1;
            
            conn.commit();
            conn.setAutoCommit(true);
        } catch (SQLException ex) {
            DBUtil.rollback(conn);
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(pstmt);
            DBUtil.close(conn);
        }
        return saved;
    }
    
    public boolean save(TaskAckReq ackReq) throws DatabaseException {
        boolean saved = false;
        Connection conn = null;
        try {
            conn = DBConn.getInstance().getConnection();
            conn.setAutoCommit(false);

            // update download task log activity
            if(!ackReq.isBroadcasted()) {
                // sent to single device
                saved |= updateDownloadTaskLogActivity(conn, ackReq);
            } else {
                // broadcasted to terminal group initially
                saved |= updateDownloadTaskLogActivityByTaskAndTerminal(conn, ackReq);
            }
            conn.commit();
            conn.setAutoCommit(true);
        } catch (SQLException ex) {
            DBUtil.rollback(conn);
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn);
        }
        return saved;
    }

    private boolean updateDownloadTaskLogActivity(Connection conn, TaskAckReq ackReq) throws SQLException {
        boolean saved = false;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(""
                    + "update tms_download_task_log set activity=?,message=?,update_ts=NOW(),updated_by=? where id=?::uuid");
            int idx = 1;
            pstmt.setInt(idx++, ackReq.getActivity());
            pstmt.setString(idx++, ackReq.getMessage());
            pstmt.setString(idx++, ackReq.getTerminalSN());
            pstmt.setString(idx++, ackReq.getAckId());
            saved = pstmt.executeUpdate() == 1;
        } finally {
            DBUtil.close(pstmt);
        }
        return saved;
    }

    private boolean updateDownloadTaskLogActivityByTaskAndTerminal(Connection conn, TaskAckReq ackReq) throws SQLException {
        boolean saved = false;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(""
                    + "update tms_download_task_log set old_activity=activity,activity=?,message=?,update_ts=NOW(),updated_by=? where task_id=?::uuid and terminal_id=?::uuid");
            int idx = 1;
            pstmt.setInt(idx++, ackReq.getActivity());
            pstmt.setString(idx++, ackReq.getMessage());
            pstmt.setString(idx++, ackReq.getTerminalSN());
            pstmt.setString(idx++, ackReq.getTaskId());
            pstmt.setString(idx++, ackReq.getTerminalId());
            saved = pstmt.executeUpdate() >= 1;
        } finally {
            DBUtil.close(pstmt);
        }
        return saved;
    }

    private int getUnfinishedTask(Connection conn, TaskAckReq ackReq) throws DatabaseException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int total = 0;
        try {
            pstmt = conn.prepareStatement(""
                    + "select count(*) from public.tms_download_task_log "
                    + "where task_id = (select id from tms_download_task_log where id = ?::uuid) "
                    + "	and activity not in (4,6,7)");
            pstmt.setString(1, ackReq.getAckId());
            rs = pstmt.executeQuery();
            if (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(pstmt);
        }
        return total;
    }
}
