/*
 * Copyright (C) 2003-2016 eXo Platform SAS.
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */
package org.exoplatform.cs.entity;

import java.util.Calendar;
import java.util.List;


import javax.persistence.*;

import lombok.Data;

import org.exoplatform.commons.api.persistence.ExoEntity;
import org.exoplatform.cs.dto.InfoType;
import org.exoplatform.cs.dto.IssueSeverity;
import org.exoplatform.cs.dto.IssueType;
import org.exoplatform.cs.dto.Owner;

/**
 * Created by The eXo Platform SAS
 *
 * @author boubaker.khanfir@exoplatform.com
 * @since Apr 27, 2016
 */
@Entity(name = "CSTopicEntity")
@ExoEntity
@Table(name = "CS_ADDON_TOPICS")
@NamedQueries({ @NamedQuery(name = "topicEntity.findAllTickets", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true order by updateDate desc"),
        @NamedQuery(name = "topicEntity.getTopicsBySpace", query = "SELECT a FROM CSTopicEntity a where a.space.id = :spaceGroupID order by updateDate desc"),
        @NamedQuery(name = "topicEntity.getTopicsBySpaceAndStatus", query = "SELECT a FROM CSTopicEntity a where a.space.id = :spaceGroupID AND a.status = :status order by updateDate desc"),
        @NamedQuery(name = "topicEntity.getTopicsBySpaceAndAssignee", query = "SELECT a FROM CSTopicEntity a where a.space.id = :spaceGroupID AND a.assignee = :assignee order by updateDate desc"),
        @NamedQuery(name = "topicEntity.getTopicsBySpaceAndAssigneeAndStatus", query = "SELECT a FROM CSTopicEntity a where a.space.id = :spaceGroupID AND a.assignee = :assignee AND a.status = status order by updateDate desc"),
        @NamedQuery(name = "topicEntity.findOpenTicketsBySpace", query = "SELECT a FROM CSTopicEntity a where a.space.id = :spaceGroupID and a.status != :closedStatus order by updateDate desc"),
        @NamedQuery(name = "topicEntity.findOpenTicketsBySpaceAndStatus", query = "SELECT a FROM CSTopicEntity a where a.space.id = :spaceGroupID and (a.status = :status and a.status != :closedStatus) order by updateDate desc"),
        @NamedQuery(name = "topicEntity.findOpenTicketsByStatus", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true and (a.status = :status and a.status != :closedStatus) order by updateDate desc"),
        @NamedQuery(name = "topicEntity.getTopicsByAssignee", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.assignee = :assignee order by updateDate desc"),
        @NamedQuery(name = "topicEntity.getTopicsByAssigneeAndStatus", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.assignee = :assignee AND a.status = :status order by updateDate desc"),
        @NamedQuery(name = "topicEntity.getOpenTopics", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true and   (a.status is null or a.status != :status) order by updateDate desc"),
        @NamedQuery(name = "topicEntity.getOpenTopicsByAssignee", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.assignee = :assignee AND (a.status is null or a.status != :status) order by updateDate desc"),
        @NamedQuery(name = "topicEntity.findAllTicketByStatus", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.status = :status order by updateDate desc"),
        @NamedQuery(name = "topicEntity.getOpenTopicsByAssigneeAndSpace", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.assignee = :assignee AND a.space.id = :spaceGroupID AND (a.status is null or a.status != :status) order by updateDate desc"),
        @NamedQuery(name = "topicEntity.findUpdatedTopics", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true and a.updateDate >= :from AND a.updateDate <= :to order by updateDate desc"),
        @NamedQuery(name = "topicEntity.findEmptyTitleAndCreationDateTopics", query = "SELECT a FROM CSTopicEntity a where a.title is null or a.title='' or a.creationDate is null or a.creationDate is EMPTY order by updateDate desc"),
        @NamedQuery(name = "topicEntity.findTicketByTicketId", query = "SELECT a FROM CSTopicEntity a where a.ticketId = :ticketId"),
        @NamedQuery(name = "topicEntity.findEmptyTicketID", query = "SELECT a FROM CSTopicEntity a where ( a.ticketId is null or a.ticketId = '' ) AND a.type != 1"),
        @NamedQuery(name = "topicEntity.countTickets", query = "SELECT count(a) FROM CSTopicEntity a where a.type = :type AND a.space = :space"),
        @NamedQuery(name = "topicEntity.getTopicsBySpaceAndCreator", query = "SELECT a FROM CSTopicEntity a where a.space.id = :spaceGroupID AND a.creator = :creator order by updateDate desc"),
        @NamedQuery(name = "topicEntity.getTopicsBySpaceAndCreatorAndStatus", query = "SELECT a FROM CSTopicEntity a where a.space.id = :spaceGroupID AND a.creator = :creator AND a.status = status order by updateDate desc"),
        @NamedQuery(name = "topicEntity.getTopicsByCreator", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.creator = :creator order by updateDate desc"),
        @NamedQuery(name = "topicEntity.getTopicsByCreatorAndStatus", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.creator = :creator AND a.status = :status order by updateDate desc"),
        @NamedQuery(name = "topicEntity.getOpenTopicsByCreator", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.creator = :creator AND (a.status is null or a.status != :status) order by updateDate desc"),
        @NamedQuery(name = "topicEntity.getOpenTopicsByCreatorAndSpace", query = "SELECT a FROM CSTopicEntity a where a.creator = :creator AND a.space.id = :spaceGroupID AND (a.status is null or a.status != :status) order by updateDate desc"),
        @NamedQuery(name = "topicEntity.findCreatedTopicsBySpace", query = "SELECT a FROM CSTopicEntity a where a.creationDate >= :from AND a.creationDate <= :to AND a.space.id = :spaceGroupID order by creationDate desc"),
        @NamedQuery(name = "topicEntity.findCreatedTopics", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.creationDate >= :from AND a.creationDate <= :to order by creationDate desc"),
        @NamedQuery(name = "topicEntity.findNotAssignedTopicsBySpace", query = "SELECT a FROM CSTopicEntity a where a.assignee IS NULL AND a.space.id = :spaceGroupID order by creationDate desc"),
        @NamedQuery(name = "topicEntity.findNotAssignedTopics", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.assignee IS NULL order by creationDate desc"),
        @NamedQuery(name = "topicEntity.findUpdatedTopicsBySpace", query = "SELECT a FROM CSTopicEntity a where a.updateDate >= :from AND a.updateDate <= :to AND a.space.id = :spaceGroupID order by updateDate desc"),
        @NamedQuery(name = "topicEntity.findUpdatedTopicsBySpaceAndStatus", query = "SELECT a FROM CSTopicEntity a where a.updateDate >= :from AND a.updateDate <= :to AND a.space.id = :spaceGroupID And a.status = :status order by updateDate desc"),
        @NamedQuery(name = "topicEntity.findUpdatedTicketsByStatus", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.status = :status And a.updateDate >= :from AND a.updateDate <= :to order by updateDate desc"),
        @NamedQuery(name = "topicEntity.countOpenCustomerTickets", query = "SELECT  count(a.id) FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.space.internal = :internal and a.status != :closedStatus"),
        @NamedQuery(name = "topicEntity.countClosedCustomerTickets", query = "SELECT  count(a.id) FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.space.internal = :internal and a.status = :closedStatus"),
        @NamedQuery(name = "topicEntity.countOpenSpaceTickets", query = "SELECT  count(a.id) FROM CSTopicEntity a where  a.space.id = :spaceGroupID and a.status != :closedStatus"),
        @NamedQuery(name = "topicEntity.countClosedSpaceTickets", query = "SELECT  count(a.id) FROM CSTopicEntity a where  a.space.id = :spaceGroupID and a.status = :closedStatus"),
        @NamedQuery(name = "topicEntity.countOpenCustomerTicketsByDates", query = "SELECT  count(a.id) FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.space.internal = :internal and a.creationDate >= :from AND a.creationDate <= :to "),
        @NamedQuery(name = "topicEntity.countClosedCustomerTicketsByDates", query = "SELECT  count(a.id) FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.space.internal = :internal and a.status = :closedStatus and a.updateDate >= :from AND a.updateDate <= :to"),
        @NamedQuery(name = "topicEntity.countCustomerTicketsGroupdByStatusAndDates", query = "SELECT a.status, count(a.id) FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.space.internal = :internal and a.status != :closedStatus and a.updateDate >= :from AND a.updateDate <= :to group by a.status"),
        @NamedQuery(name = "topicEntity.findTicketsForReminder", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.status in ('suspended_wfi','resolved_wfv') AND a.updateDate <= :to"),
        @NamedQuery(name = "topicEntity.getTopicsByEnvironment", query = "SELECT a FROM CSTopicEntity a where a.environment.id = :environmentID"),
        @NamedQuery(name = "topicEntity.countSatisfiedTicketNumber", query = "SELECT  count(a.id) FROM CSTopicEntity a where  a.space.internal = :internal and a.customerSatisfied = true"),
        @NamedQuery(name = "topicEntity.countNotSatisfiedTicketNumber", query = "SELECT  count(a.id) FROM CSTopicEntity a where  a.space.internal = :internal and a.customerSatisfied = false"),
        @NamedQuery(name = "topicEntity.getTimeToFirstResponseAvg", query = "SELECT  avg(a.timeToFirstResponse) FROM CSTopicEntity a where  a.space.internal = :internal and a.timeToFirstResponse  IS NOT NULL"),
        @NamedQuery(name = "topicEntity.getTimeToResolutionAvg", query = "SELECT  avg(a.timeToResolution) FROM CSTopicEntity a where  a.space.internal = :internal and a.timeToResolution IS NOT NULL"),
        @NamedQuery(name = "topicEntity.countSpaceSatisfiedTicketNumber", query = "SELECT  count(a.id) FROM CSTopicEntity a where  a.space.id = :spaceGroupID and a.customerSatisfied = true"),
        @NamedQuery(name = "topicEntity.countSpaceNotSatisfiedTicketNumber", query = "SELECT  count(a.id) FROM CSTopicEntity a where  a.space.id = :spaceGroupID and a.customerSatisfied = false"),
        @NamedQuery(name = "topicEntity.getSpaceTimeToFirstResponseAvg", query = "SELECT  avg(a.timeToFirstResponse) FROM CSTopicEntity a where  a.space.id = :spaceGroupID and a.timeToFirstResponse  IS NOT NULL"),
        @NamedQuery(name = "topicEntity.getSpaceTimeToResolutionAvg", query = "SELECT  avg(a.timeToResolution) FROM CSTopicEntity a where  a.space.id = :spaceGroupID and a.timeToResolution IS NOT NULL"),
        @NamedQuery(name = "topicEntity.countSpaceTicketsGroupdByStatus", query = "SELECT a.status, count(a.id) FROM CSTopicEntity a where a.space.id = :spaceGroupID and a.status != :closedStatus group by a.status"),
        @NamedQuery(name = "topicEntity.countSpaceTicketsGroupdByOwner", query = "SELECT a.owner, count(a.id) FROM CSTopicEntity a where a.space.id = :spaceGroupID and a.status != :closedStatus group by a.owner"),
        @NamedQuery(name = "topicEntity.countSpaceTicketsGroupdByAssignee", query = "SELECT a.assignee, count(a.id) FROM CSTopicEntity a where a.space.id = :spaceGroupID and a.status != :closedStatus group by a.assignee"),
        @NamedQuery(name = "topicEntity.countCustomerTicketsGroupdBySpace", query = "SELECT a.space, count(a.id) FROM CSTopicEntity a where a.space.internal = :internal and a.status != :closedStatus group by a.space"),
        @NamedQuery(name = "topicEntity.countCustomerTicketsGroupdByOwner", query = "SELECT a.owner, count(a.id) FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.space.internal = :internal and a.status != :closedStatus group by a.owner"),
        @NamedQuery(name = "topicEntity.countCustomerTicketsGroupdByStatus", query = "SELECT a.status, count(a.id) FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.space.internal = :internal and a.status != :closedStatus group by a.status"),
        @NamedQuery(name = "topicEntity.countCustomerTicketsGroupdByAssigneeAndDates", query = "SELECT a.assignee, count(a.id) FROM CSTopicEntity a where a.space.subscriptionActive = true and a.status != :closedStatus and  a.space.internal = :internal and a.updateDate >= :from AND a.updateDate <= :to group by a.assignee"),
        @NamedQuery(name = "topicEntity.countCustomerTicketsGroupdByAssignee", query = "SELECT a.assignee, count(a.id) FROM CSTopicEntity a where a.space.subscriptionActive = true and a.status != :closedStatus and  a.space.internal = :internal group by a.assignee"),
        @NamedQuery(name = "topicEntity.countUnresolvedTicketsGroupdByAssignee", query = "SELECT a.assignee, count(a.id) FROM CSTopicEntity a where a.space.subscriptionActive = true and a.status NOT IN ('resolved_maintenance','resolved_validated','suspended_wfi','resolved_improvement','suspended_wfi','resolved_wfv','closed,') group by a.assignee"),
        @NamedQuery(name = "topicEntity.countCustomerTicketsGroupdBySeverityAndDates", query = "SELECT a.assignee, count(a.id) FROM CSTopicEntity a where a.space.subscriptionActive = true and a.status != :closedStatus and  a.space.internal = :internal and a.updateDate >= :from AND a.updateDate <= :to group by a.assignee"),
        @NamedQuery(name = "topicEntity.countCustomerTicketsGroupdBySeverity", query = "SELECT a.severity, count(a.id) FROM CSTopicEntity a where a.space.subscriptionActive = true and a.status != :closedStatus and  a.space.internal = :internal group by a.severity"),
        @NamedQuery(name = "topicEntity.countSpaceTicketsGroupdBySeverity", query = "SELECT a.severity, count(a.id) FROM CSTopicEntity a where  a.space.id = :spaceGroupID and a.status != :closedStatus group by a.severity"),
        @NamedQuery(name = "topicEntity.findTicketsForSLAReminder", query = "SELECT a FROM CSTopicEntity a where a.space.subscriptionActive = true and  a.firstWarningDate IS NOT NULL AND a.firstWarningDate <= :instant"),

})

@Data
public class TopicEntity {

  @Id
  @Column(name = "TOPIC_ID")
  private String            id;

  @Column(name = "TYPE")
  private IssueType         type;

  @Column(name = "TITLE")
  private String     title;

  @Column(name = "TICKET_ID")
  private String     ticketId;

  @Column(name = "STATUS")
  private String     status;

  @Column(name = "SEVERITY")
  private IssueSeverity     severity;

  @Column(name = "INFO_TYPE")
  private InfoType          infoType;

  @Column(name = "LINK")
  private String            link;

  @Column(name = "TOPIC_ASSIGNEE")
  private String assignee;

  @ManyToOne
  @JoinColumn(name = "SPACE_ID")
  private SpaceEntity       space;

  @ManyToOne
  @JoinColumn(name = "ENVIRONMENT_ID")
  private EnvironmentEntity environment;

  @OneToMany(mappedBy = "topic",cascade = CascadeType.ALL,fetch = FetchType.LAZY)
  private List<LogEntity> logs;

  @Column(name = "CREATIONDATE")
  @Temporal(TemporalType.TIMESTAMP)
  private Calendar creationDate;

  @Column(name = "UPDATEDATE")
  @Temporal(TemporalType.TIMESTAMP)
  private Calendar updateDate;


  @Column(name = "CREATOR")
  private String creator;


  @Column(name = "OWNER")
  private Owner owner;


  @Column(name = "ATTACHED_JIRA")
  private String attachedJira;


  @Column(name = "FIRST_WARNING_DATE")
  @Temporal(TemporalType.TIMESTAMP)
  private Calendar firstWarningDate;


  @Column(name = "LAST_WARNING_DATE")
  @Temporal(TemporalType.TIMESTAMP)
  private Calendar lastWarningDate;

  @Column(name = "AUTO_ASSIGN_DATE")
  @Temporal(TemporalType.TIMESTAMP)
  private Calendar autoAssignDate;

  @Column(name = "TIME_TO_FIRST_RESPONSE")
  private Long timeToFirstResponse;


  @Column(name = "TIME_TO_RESOLUTION")
  private Long timeToResolution;

  @Column(name = "CUSTOMER_SATISFIED")
  private Boolean customerSatisfied;

  @Column(name = "REASON")
  private String reason;
}
