package org.exoplatform.cs.dao;

import org.exoplatform.commons.persistence.impl.GenericDAOJPAImpl;
import org.exoplatform.container.ExoContainer;
import org.exoplatform.container.ExoContainerContext;
import org.exoplatform.cs.dto.LogType;
import org.exoplatform.cs.entity.LogEntity;
import org.exoplatform.cs.entity.SpaceEntity;
import org.exoplatform.cs.service.CSConstants;
import org.exoplatform.cs.service.CSSpaceService;
import org.gatein.common.logging.Logger;
import org.gatein.common.logging.LoggerFactory;

import javax.persistence.Query;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: ali
 * Date: 06/01/17
 * Time: 12:15
 */
public class LogDAO extends GenericDAOJPAImpl<LogEntity, String> {
private static final Logger LOG = LoggerFactory.getLogger(LogDAO.class);

    public List<LogEntity> getupdates(Calendar from, Calendar to) {
        return getEntityManager().createNamedQuery("logEntity.getTicketsUpdatesBetweenDates", LogEntity.class).setParameter("from", from).setParameter("to", to).getResultList();

    }

    public List<LogEntity> getLogsByTicketAndStatus(String topicId, List<String> status) {
        return getEntityManager().createNamedQuery("logEntity.getLogsByTicketAndStatus", LogEntity.class)
                .setParameter("topicId", topicId)
                .setParameter("type", LogType.TICKET_STATUS_CHANGED)
                .setParameter("status", status).getResultList();

    }
    public List<LogEntity> getLogsByTicket(String topicId) {
        return getEntityManager().createNamedQuery("logEntity.getLogsByTicket", LogEntity.class)
                .setParameter("topicId", topicId).getResultList();

    }

    public List<LogEntity> getTimeToFirstResponseBetweenDates(Calendar from, Calendar to, boolean internal) {
        return getEntityManager().createNamedQuery("logEntity.getTimeToFirstResponseBetweenDates", LogEntity.class)
                .setParameter("internal", internal)
                .setParameter("from", from)
                .setParameter("to", to)
                .setParameter("type", LogType.TIME_TO_FIRST_RESPONSE_ADDED)
                .getResultList();

    }

    public List<LogEntity> getTimeToResolutionBetweenDates(Calendar from, Calendar to, boolean internal) {
        return getEntityManager().createNamedQuery("logEntity.getTimeToResolutionBetweenDates", LogEntity.class)
                .setParameter("internal", internal)
                .setParameter("from", from)
                .setParameter("to", to)
                .setParameter("type", LogType.TIME_TO_RESOLUTION_ADDED)
                .getResultList();

    }


    public List<LogEntity> getLogsByNewValue(String newValue) {
        return getEntityManager().createNamedQuery("logEntity.getLogsByNewValue", LogEntity.class)
                .setParameter("newValue", newValue).getResultList();

    }

    public Double getTimeToFirstResponseAvgBetweenDates(Calendar from, Calendar to, boolean internal) {

        try {
            return getEntityManager().createNamedQuery("logEntity.getTimeToFirstResponseAvgBetweenDates", Double.class)
                    .setParameter("internal", internal)
                .setParameter("from", from)
                .setParameter("to", to)
                .setParameter("type", LogType.TIME_TO_FIRST_RESPONSE_ADDED)
                .getSingleResult();
/*    }  catch (NullPointerException e) {
        return 0;*/
    }   catch (Exception e) {
        LOG.warn("Exception while attempting to get request", e);
        throw e;
    }
    }
    public Double getTimeToFirstResponseAvgBySpaceBetweenDates(String spaceId, Calendar from, Calendar to) {

        try {
            return getEntityManager().createNamedQuery("logEntity.getTimeToFirstResponseAvgBySpaceBetweenDates", Double.class)
                    .setParameter("spaceId", spaceId)
                .setParameter("from", from)
                .setParameter("to", to)
                .setParameter("type", LogType.TIME_TO_FIRST_RESPONSE_ADDED)
                .getSingleResult();
/*    }  catch (NullPointerException e) {
        return 0;*/
    }   catch (Exception e) {
        LOG.warn("Exception while attempting to get request", e);
        throw e;
    }

    }

    public Double getTimeToResolutionAvgBetweenDates(Calendar from, Calendar to, boolean internal) {
        try {
        return getEntityManager().createNamedQuery("logEntity.getTimeToResolutionAvgBetweenDates", Double.class)
                .setParameter("internal", internal)
                .setParameter("from", from)
                .setParameter("to", to)
                .setParameter("type", LogType.TIME_TO_RESOLUTION_ADDED)
                .getSingleResult();
/*    }  catch (NullPointerException e) {
        return 0;*/
    }   catch (Exception e) {
        LOG.warn("Exception while attempting to get request", e);
        throw e;
    }

    }

    public Double getTimeToResolutionAvgBySpaceBetweenDates(String spaceId,Calendar from, Calendar to) {
        try {
        return getEntityManager().createNamedQuery("logEntity.getTimeToResolutionAvgBySpaceBetweenDates", Double.class)
                .setParameter("spaceId", spaceId)
                .setParameter("from", from)
                .setParameter("to", to)
                .setParameter("type", LogType.TIME_TO_RESOLUTION_ADDED)
                .getSingleResult();
/*    }  catch (NullPointerException e) {
        return 0;*/
    }   catch (Exception e) {
        LOG.warn("Exception while attempting to get request", e);
        throw e;
    }

    }

    public Long countLogsByTypeAndNewValueBetweenDates(Calendar from, Calendar to, LogType type, String newValue, boolean internal) {
        try {
        return getEntityManager().createNamedQuery("logEntity.countLogsByTypeAndNewValueBetweenDates", Long.class)
                .setParameter("internal", internal)
                .setParameter("from", from)
                .setParameter("to", to)
                .setParameter("type",type)
                .setParameter("newValue",newValue)
                .getSingleResult();
    }   catch (Exception e) {
        LOG.warn("Exception while attempting to get request", e);
        throw e;
    }

    }
    public Long countLogsByTypeAndNewValueAndSpaceBetweenDates(String spaceId,  Calendar from, Calendar to, LogType type, String newValue) {
        try {
        return getEntityManager().createNamedQuery("logEntity.countLogsByTypeAndNewValueAndSpaceBetweenDates", Long.class)
                .setParameter("spaceId", spaceId)
                .setParameter("from", from)
                .setParameter("to", to)
                .setParameter("type",type)
                .setParameter("newValue",newValue)
                .getSingleResult();
    }   catch (Exception e) {
        LOG.warn("Exception while attempting to get request", e);
        throw e;
    }

    }
    public int countLogsByTypeAndNewValueBetweenDatesGroupedByTickets(Calendar from, Calendar to, LogType type, String newValue, boolean internal) {
        try {
        return getEntityManager().createNamedQuery("logEntity.getLogsByTypeAndNewValueBetweenDatesGroupedByTickets", String.class)
                .setParameter("internal", internal)
                .setParameter("from", from)
                .setParameter("to", to)
                .setParameter("type",type)
                .setParameter("newValue",newValue)
                .getResultList().size();
    }   catch (Exception e) {
        LOG.warn("Exception while attempting to get request", e);
        throw e;
    }

    }
    public int countLogsByTypeAndNewValueAndSpaceBetweenDatesGroupedByTickets(String spaceId,  Calendar from, Calendar to, LogType type, String newValue) {
        try {
        return getEntityManager().createNamedQuery("logEntity.getLogsByTypeAndNewValueAndSpaceBetweenDatesGroupedByTickets", String.class)
                .setParameter("spaceId", spaceId)
                .setParameter("from", from)
                .setParameter("to", to)
                .setParameter("type",type)
                .setParameter("newValue",newValue)
                .getResultList().size();
    }   catch (Exception e) {
        LOG.warn("Exception while attempting to get request", e);
        throw e;
    }

    }

    public Long countLogsNumberBetweenDates(Calendar from, Calendar to, LogType type, boolean internal) {
        try {
        return getEntityManager().createNamedQuery("logEntity.countLogsByTypeBetweenDates", Long.class)
                .setParameter("internal", internal)
                .setParameter("from", from)
                .setParameter("to", to)
                .setParameter("type",type)
                .getSingleResult();
/*    }  catch (NullPointerException e) {
        return 0;*/
    }   catch (Exception e) {
        LOG.warn("Exception while attempting to get request", e);
        throw e;
    }

    }
    public Long countLogsNumberBySpaceBetweenDates(String spaceId,  Calendar from, Calendar to, LogType type) {
        try {
        return getEntityManager().createNamedQuery("logEntity.countLogsByTypeAndSpaceBetweenDates", Long.class)
                .setParameter("spaceId", spaceId)
                .setParameter("from", from)
                .setParameter("to", to)
                .setParameter("type",type)
                .getSingleResult();
/*    }  catch (NullPointerException e) {
        return 0;*/
    }   catch (Exception e) {
        LOG.warn("Exception while attempting to get request", e);
        throw e;
    }

    }
    public Long countLogsNumber( LogType type, String newValue, boolean internal) {
        try {
        return getEntityManager().createNamedQuery("logEntity.countLogsByTypeAndNewValue", Long.class)
                .setParameter("internal", internal)
                .setParameter("type",type)
                .setParameter("newValue",newValue)
                .getSingleResult();
/*    }  catch (NullPointerException e) {
        return 0;*/
    }   catch (Exception e) {
        LOG.warn("Exception while attempting to get request", e);
        throw e;
    }

    }

    public List<LogEntity> getFirstLogByType(LogType type) {
        try {

            return getEntityManager().createNamedQuery("logEntity.findByType", LogEntity.class)
                    .setParameter("type", type)
                    .setFirstResult(0)
                    .setMaxResults(1)
                    .getResultList();

        } catch (Exception e) {
            LOG.warn("Exception while attempting to get first log", e);
            throw e;
        }
    }

    public List<LogEntity> getFirstLogByTypeAndSpace (String spaceId, LogType type) {
        try {

            return getEntityManager().createNamedQuery("logEntity.findByTypeAndSpace", LogEntity.class)
                    .setParameter("type", type)
                    .setParameter("spaceId", spaceId)
                    .setFirstResult(0)
                    .setMaxResults(1)
                    .getResultList();

        } catch (Exception e) {
            LOG.warn("Exception while attempting to get first log", e);
            throw e;
        }
    }

    public List<LogEntity> getByType(LogType type, int offset, int limit) {
        try {
            if (offset >= 0 && limit > 0) {
                return getEntityManager().createNamedQuery("logEntity.findByType", LogEntity.class)
                        .setParameter("type", type)
                        .setFirstResult(offset)
                        .setMaxResults(limit)
                        .getResultList();
            }
            return getEntityManager().createNamedQuery("logEntity.findByType", LogEntity.class)
                    .setParameter("type", type)
                    .getResultList();

        } catch (Exception e) {
            LOG.warn("Exception while attempting to get first log", e);
            throw e;
        }
    }

    public List<LogEntity> getTimeToFirstResponseFirstLog() {
        try {

            return getEntityManager().createNamedQuery("logEntity.findByType", LogEntity.class)
                    .setParameter("type", LogType.TIME_TO_FIRST_RESPONSE_ADDED)
                    .setFirstResult(0)
                    .setMaxResults(1)
                    .getResultList();

        } catch (Exception e) {
            LOG.warn("Exception while attempting to get first log", e);
            throw e;
        }
    }
    public List<LogEntity> getTimeToResolutionFirstLog() {
        try {

            return getEntityManager().createNamedQuery("logEntity.findByType", LogEntity.class)
                    .setParameter("type", LogType.TIME_TO_RESOLUTION_ADDED)
                    .setFirstResult(0)
                    .setMaxResults(1)
                    .getResultList();

        } catch (Exception e) {
            LOG.warn("Exception while attempting to get first log", e);
            throw e;
        }
    }

    public List<Object[]> countLogsGroupdByNewValue(boolean internal) {
        try {
            ExoContainer containerContext = ExoContainerContext.getCurrentContainer();
            SpaceDAO spaceDAO = containerContext.getComponentInstanceOfType(SpaceDAO.class);
            List<SpaceEntity> internalSpaces = spaceDAO.getSpacesByType(true,0,0);
            List<String> internalList = new ArrayList<>();
            for(SpaceEntity space : internalSpaces){
                internalList.add(space.getGroupId());
            }
            if(internalList.isEmpty()) {
                internalList.add("");
                LOG.warn("No internal spaces was found, this my lead to unexpected chart results ");
            }
            if(internal){
                Query q = getEntityManager().createNativeQuery("SELECT newValue, count(logId) FROM (SELECT c.`TICKET_ID` as ticketId, ANY_VALUE(c.`NEW_VALUE`) as newValue, ANY_VALUE(c.`LOG_ID`) as logId FROM CS_ADDON_LOG c WHERE c.`TYPE`IN (1) AND c.`SPACE_ID` IN (:internalList) GROUP BY c.`TICKET_ID` ORDER BY c.`TICKET_ID` DESC) AS totals GROUP BY newValue");
                q.setParameter("internalList", internalList);
                List<Object[]> logs = q.getResultList();
                return logs;
            } else{
                Query q = getEntityManager().createNativeQuery("SELECT newValue, count(logId) FROM (SELECT c.`TICKET_ID` as ticketId, ANY_VALUE(c.`NEW_VALUE`) as newValue, ANY_VALUE(c.`LOG_ID`) as logId FROM CS_ADDON_LOG c WHERE c.`TYPE`IN (1) AND c.`SPACE_ID` NOT IN (:internalList) GROUP BY c.`TICKET_ID` ORDER BY c.`TICKET_ID` DESC) AS totals GROUP BY newValue");
                q.setParameter("internalList", internalList);
                List<Object[]> logs = q.getResultList();
                return logs;
            }

        }  catch (Exception e) {
            LOG.warn("Exception while attempting to get request", e);
            throw e;
        }
    }

    public List<Object[]> getLogsGroupdByNewValue(boolean internal) {
        try {
            ExoContainer containerContext = ExoContainerContext.getCurrentContainer();
            SpaceDAO spaceDAO = containerContext.getComponentInstanceOfType(SpaceDAO.class);
            List<SpaceEntity> internalSpaces = spaceDAO.getSpacesByType(true,0,0);
            List<String> internalList = new ArrayList<>();
            for(SpaceEntity space : internalSpaces){
                internalList.add(space.getGroupId());
            }
            if(internalList.isEmpty()) {
                internalList.add("");
                LOG.warn("No internal spaces was found, this my lead to unexpected chart results ");
            }
            if(internal){
                Query q = getEntityManager().createNativeQuery("SELECT ANY_VALUE(ticketId), newValue, ANY_VALUE(logId) FROM (SELECT c.`TICKET_ID` as ticketId, c.`NEW_VALUE` as newValue, c.`LOG_ID` as logId FROM CS_ADDON_LOG c WHERE c.`TYPE`IN (1) AND c.`SPACE_ID` IN (:internalList) ORDER BY c.`TICKET_ID` DESC) AS totals GROUP BY newValue");
                q.setParameter("internalList", internalList);
                List<Object[]> logs = q.getResultList();
                return logs;
            } else{
                Query q = getEntityManager().createNativeQuery("SELECT  ANY_VALUE(ticketId), newValue, ANY_VALUE(logId) FROM (SELECT c.`TICKET_ID` as ticketId, c.`NEW_VALUE` as newValue, c.`LOG_ID` as logId FROM CS_ADDON_LOG c WHERE c.`TYPE`IN (1) AND c.`SPACE_ID` NOT IN (:internalList) ORDER BY c.`TICKET_ID` DESC) AS totals GROUP BY newValue");
                q.setParameter("internalList", internalList);
                List<Object[]> logs = q.getResultList();
                return logs;
            }

        }  catch (Exception e) {
            LOG.warn("Exception while attempting to get request", e);
            throw e;
        }
    }

    public List<Object[]> countLogsGroupdByNewValueAndDates(boolean internal, Calendar to) {
        try {
            ExoContainer containerContext = ExoContainerContext.getCurrentContainer();
            SpaceDAO spaceDAO = containerContext.getComponentInstanceOfType(SpaceDAO.class);
            List<SpaceEntity> internalSpaces = spaceDAO.getSpacesByType(true,0,0);
            List<String> internalList = new ArrayList<>();
            for(SpaceEntity space : internalSpaces){
                internalList.add(space.getGroupId());
            }
            if(internalList.isEmpty()) {
                internalList.add("");
                LOG.warn("No internal spaces was found, this my lead to unexpected chart results ");
            }
            if(internal){
                Query q = getEntityManager().createNativeQuery("SELECT c.`TICKET_ID`, ANY_VALUE(c.`NEW_VALUE`) , MAX(c.`LOG_ID`) as logId FROM CS_ADDON_LOG c WHERE c.`TYPE` IN (1) AND c.`SPACE_ID` IN (:internalList) AND c.`LOG_DATE` <= :to GROUP BY c.`TICKET_ID` ORDER BY c.`TICKET_ID` DESC");
                q.setParameter("internalList", internalList);
                q.setParameter("to", to);
                List<Object[]> logs = q.getResultList();
                return logs;
            } else{
                Query q = getEntityManager().createNativeQuery("SELECT c.`TICKET_ID`, ANY_VALUE(c.`NEW_VALUE`), MAX(c.`LOG_ID`) as logId FROM CS_ADDON_LOG c WHERE c.`TYPE` IN (1) AND c.`SPACE_ID` NOT IN (:internalList) AND c.`LOG_DATE` <= :to GROUP BY c.`TICKET_ID` ORDER BY c.`TICKET_ID` DESC");
                q.setParameter("internalList", internalList);
                q.setParameter("to", to);
                List<Object[]> logs = q.getResultList();
                return logs;
            }

        }  catch (Exception e) {
            LOG.warn("Exception while attempting to get request", e);
            throw e;
        }
    }

    public List<Object[]> countLogsBySpaceGroupdByNewValueAndDates(String spaceId, Calendar to) {
        try {

                Query q = getEntityManager().createNativeQuery("SELECT c.`TICKET_ID`, ANY_VALUE(c.`NEW_VALUE`), MAX(c.`LOG_ID`) as logId FROM CS_ADDON_LOG c WHERE c.`TYPE` IN (1) AND c.`SPACE_ID` =:spaceId AND c.`LOG_DATE` <= :to GROUP BY c.`TICKET_ID` ORDER BY c.`TICKET_ID` DESC");
                q.setParameter("spaceId", spaceId);
                q.setParameter("to", to);
                List<Object[]> logs = q.getResultList();
                return logs;


        }  catch (Exception e) {
            LOG.warn("Exception while attempting to get request", e);
            throw e;
        }
    }
}
