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.ApplicationSimple;
import id.iptek.utms.agent.model.PendingDeleteTask;
import id.iptek.utms.agent.model.TaskAckReq;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

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

    public DeleteTaskDao() {
    }

    public List<PendingDeleteTask> getPendingTaskForOnlineTerminals(int minutes, int limit) throws DatabaseException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<PendingDeleteTask> list = new ArrayList<>();
        try {
            conn = DBConn.getInstance().getConnection();
            pstmt = conn.prepareStatement(""
                    + "select "
                    + "	terminal.sn, "
                    + "	task.id,task.\"name\",task.delete_time, "
                    + "	app.id app_id,app.app_name,app.package_name, "
                    + "	log.id log_id "
                    + "from public.tms_delete_task task "
                    + "	inner join public.tms_delete_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_delete_task_application_simple_link link on link.delete_task_id=task.id and link.application_simple_id=log.application_simple_id "
                    + "	inner join public.tms_application_simple app on link.application_simple_id=app.id "
                    + "where 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()) {
                PendingDeleteTask task = new PendingDeleteTask();
                task.setTerminalSN(rs.getString(1));
                task.setId(rs.getString(2));
                task.setName(rs.getString(3));
                task.setDeleteTime(rs.getTimestamp(4));
                ApplicationSimple app = new ApplicationSimple();
                app.setId(rs.getString(5));
                app.setAppName(rs.getString(6));
                app.setPackageName(rs.getString(7));
                task.setApp(app);
                task.setLogId(rs.getString(8));
                list.add(task);
            }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return list;
    }

    public List<PendingDeleteTask> getPendingTaskForTerminals(int minutes, int limit) throws DatabaseException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<PendingDeleteTask> list = new ArrayList<>();
        try {
            conn = DBConn.getInstance().getConnection();
            pstmt = conn.prepareStatement(""
                    + "select "
                    + "	terminal.sn, "
                    + "	task.id,task.\"name\",task.delete_time, "
                    + "	app.id app_id,app.app_name,app.package_name, "
                    + "	log.id log_id "
                    + "from public.tms_delete_task task "
                    + "	inner join public.tms_delete_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_delete_task_application_simple_link link on link.delete_task_id=task.id and link.application_simple_id=log.application_simple_id "
                    + "	inner join public.tms_application_simple app on link.application_simple_id=app.id "
                    + "where 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()) {
                PendingDeleteTask task = new PendingDeleteTask();
                task.setTerminalSN(rs.getString(1));
                task.setId(rs.getString(2));
                task.setName(rs.getString(3));
                task.setDeleteTime(rs.getTimestamp(4));
                ApplicationSimple app = new ApplicationSimple();
                app.setId(rs.getString(5));
                app.setAppName(rs.getString(6));
                app.setPackageName(rs.getString(7));
                task.setApp(app);
                task.setLogId(rs.getString(8));
                list.add(task);
            }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return list;
    }

    public List<PendingDeleteTask> getPendingTaskForTerminal(String terminalSN) throws DatabaseException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<PendingDeleteTask> list = new ArrayList<>();
        try {
            conn = DBConn.getInstance().getConnection();
            pstmt = conn.prepareStatement(""
                    + "select "
                    + "	terminal.sn, "
                    + "	task.id,task.\"name\",task.delete_time, "
                    + "	app.id app_id,app.app_name,app.package_name, "
                    + "	log.id log_id "
                    + "from public.tms_delete_task task "
                    + "	inner join public.tms_delete_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_delete_task_application_simple_link link on link.delete_task_id=task.id and link.application_simple_id=log.application_simple_id "
                    + "	inner join public.tms_application_simple app on link.application_simple_id=app.id "
                    + "where lower(terminal.sn)=? "
                    + "	and log.activity=0");
            pstmt.setString(1, terminalSN.toLowerCase());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                PendingDeleteTask task = new PendingDeleteTask();
                task.setTerminalSN(rs.getString(1));
                task.setId(rs.getString(2));
                task.setName(rs.getString(3));
                task.setDeleteTime(rs.getTimestamp(4));
                ApplicationSimple app = new ApplicationSimple();
                app.setId(rs.getString(5));
                app.setAppName(rs.getString(6));
                app.setPackageName(rs.getString(7));
                task.setApp(app);
                task.setLogId(rs.getString(8));
                list.add(task);
            }
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        return list;
    }

    public boolean saveLastBroadcastTs(PendingDeleteTask 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_delete_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 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
            saved |= updateDeleteTaskLogActivity(conn, ackReq);
            if (saved) {
                int totalUnfinished = getUnfinishedTask(conn, ackReq);
                if (totalUnfinished == 0) {
                    //logger.debug("Total unfinished is 0, set to DONE");
                    //boolean taskStatusUpdated = updateDownloadTaskLogStatus(conn, ackReq);
                    //logger.debug("Download Task status updated? {}", taskStatusUpdated);
                }
            }
            conn.commit();
            conn.setAutoCommit(true);
        } catch (SQLException ex) {
            DBUtil.rollback(conn);
            throw new DatabaseException(ex);
        } finally {
            DBUtil.close(conn);
        }
        return saved;
    }

    private boolean updateDeleteTaskLogActivity(Connection conn, TaskAckReq ackReq) throws SQLException {
        boolean saved = false;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(""
                    + "update tms_delete_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 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_delete_task_log "
                    + "where task_id = (select id from tms_delete_task_log where id = ?::uuid) "
                    + "	and activity not in (3,4)");
            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;
    }

}
