package org.exoplatform.cs.entity;

import java.util.Calendar;


import javax.persistence.*;
import lombok.Data;
import org.exoplatform.commons.api.persistence.ExoEntity;
import org.exoplatform.cs.dto.LogType;

/**
 * Created by IntelliJ IDEA.
 * User: ali
 * Date: 04/01/17
 * Time: 18:39
 */
@Entity(name = "CSLogEntity")
@ExoEntity
@Table(name = "CS_ADDON_LOG")
@NamedQueries({ @NamedQuery(name = "logEntity.getLogsByTicket", query = "SELECT a FROM CSLogEntity a where a.topic.id = :topicId"),
                @NamedQuery(name = "logEntity.getLogsByTicketAndStatus", query = "SELECT a FROM CSLogEntity a where a.topic.id = :topicId and a.type = :type and a.newValue in :status order by a.when asc"),
                @NamedQuery(name = "logEntity.getLogsByNewValue", query = "SELECT a FROM CSLogEntity a where  a.newValue = :newValue order by a.when asc"),
                @NamedQuery(name = "logEntity.getTicketsUpdatesBetweenDates", query = "SELECT a FROM CSLogEntity a where a.when >= :from AND a.when <= :to "),
                @NamedQuery(name = "logEntity.getTimeToFirstResponseAvgBetweenDates", query = "SELECT  avg(a.timeToFirstResponse) FROM CSLogEntity a where  a.topic.space.internal =:internal and a.type=:type and a.when >= :from AND a.when <= :to"),
                @NamedQuery(name = "logEntity.getTimeToFirstResponseAvgBySpaceBetweenDates", query = "SELECT  avg(a.timeToFirstResponse) FROM CSLogEntity a where  a.spaceId=:spaceId and a.type=:type and a.when >= :from AND a.when <= :to"),
                @NamedQuery(name = "logEntity.getTimeToResolutionAvgBetweenDates", query = "SELECT  avg(a.timeToResolution) FROM CSLogEntity a where  a.topic.space.internal =:internal and a.type=:type and a.when >= :from AND a.when <= :to"),
                @NamedQuery(name = "logEntity.getTimeToResolutionAvgBySpaceBetweenDates", query = "SELECT  avg(a.timeToResolution) FROM CSLogEntity a where  a.spaceId=:spaceId and a.type=:type and a.when >= :from AND a.when <= :to"),
                @NamedQuery(name = "logEntity.countLogsByTypeAndNewValueBetweenDates", query = "SELECT  count(a.id) FROM CSLogEntity a where  a.topic.space.internal =:internal and a.type=:type and a.newValue = :newValue and a.when >= :from AND a.when <= :to"),
                @NamedQuery(name = "logEntity.countLogsByTypeAndNewValueAndSpaceBetweenDates", query = "SELECT  count(a.id) FROM CSLogEntity a where  a.type=:type and a.newValue = :newValue and a.when >= :from AND a.when <= :to and a.spaceId=:spaceId "),
                @NamedQuery(name = "logEntity.getLogsByTypeAndNewValueBetweenDatesGroupedByTickets", query = "SELECT  a.id FROM CSLogEntity a where  a.topic.space.internal =:internal and a.type=:type and a.newValue = :newValue and a.when >= :from AND a.when <= :to GROUP BY a.topic.id"),
                @NamedQuery(name = "logEntity.getLogsByTypeAndNewValueAndSpaceBetweenDatesGroupedByTickets", query = "SELECT  a.id FROM CSLogEntity a where  a.spaceId=:spaceId and a.type=:type and a.newValue = :newValue and a.when >= :from AND a.when <= :to GROUP BY a.topic.id"),
                @NamedQuery(name = "logEntity.countLogsByTypeAndNewValue", query = "SELECT  count(a.id) FROM CSLogEntity a where  a.topic.space.internal =:internal and a.type=:type and a.newValue = :newValue"),
                @NamedQuery(name = "logEntity.countLogsByTypeBetweenDates", query = "SELECT  count(a.id) FROM CSLogEntity a where  a.topic.space.internal =:internal and a.type=:type  and a.when >= :from AND a.when <= :to"),
                @NamedQuery(name = "logEntity.countLogsByTypeAndSpaceBetweenDates", query = "SELECT  count(a.id) FROM CSLogEntity a where  a.type=:type  and a.when >= :from AND a.when <= :to and a.spaceId=:spaceId"),
                @NamedQuery(name = "logEntity.countLogsByType", query = "SELECT  count(a.id) FROM CSLogEntity a where  a.topic.space.internal =:internal and a.type=:type"),
                @NamedQuery(name = "logEntity.findByType", query = "SELECT a FROM CSLogEntity a where a.type=:type order by a.when asc"),
                @NamedQuery(name = "logEntity.findByTypeAndSpace", query = "SELECT a FROM CSLogEntity a where a.type=:type and a.spaceId=:spaceId order by a.when asc"),

        @NamedQuery(name = "logEntity.getTimeToFirstResponseBetweenDates", query = "SELECT  a FROM CSLogEntity a where  a.topic.space.internal =:internal and a.type=:type and a.when >= :from AND a.when <= :to"),

        @NamedQuery(name = "logEntity.getTimeToResolutionBetweenDates", query = "SELECT  a FROM CSLogEntity a where  a.topic.space.internal =:internal and a.type=:type and a.when >= :from AND a.when <= :to"),
})

@NamedNativeQuery(name= "logEntity.test", query = "SELECT totals.newValue, count(totals.id) FROM (SELECT * FROM CSLogEntity c WHERE c.type IN (0,1)  GROUP BY c.topic.id ORDER BY c.topic.id DESC) AS totals GROUP BY totals.newValue")

@Data
public class LogEntity {
  @Id
  @SequenceGenerator(name = "SEQ_CS_LOG_ID")
  @GeneratedValue(strategy = GenerationType.AUTO, generator = "SEQ_CS_LOG_ID")
  @Column(name = "LOG_ID")
  private String            id;

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

  @Column(name = "USER")
  private String userID;

  @Column(name = "DATE")
  @Temporal(TemporalType.TIMESTAMP)
  private Calendar when;

  @Column(name = "ORIGINAL_VALUE")
  private String originalValue;

  @Column(name = "NEW_VALUE")
  private String newValue;

  @ManyToOne(fetch=FetchType.LAZY)
  @JoinColumn(name="TICKET_ID")
  private TopicEntity topic;

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


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

  @Column(name = "SPACE_ID")
  private String spaceId;


  public LogEntity() {
  }

  public LogEntity(LogType type, String userID, Calendar when, String originalValue, String newValue, TopicEntity topic, Long timeToFirstResponse, Long timeToResolution, String spaceId) {
    this.type = type;
    this.userID = userID;
    this.when = when;
    this.originalValue = originalValue;
    this.newValue = newValue;
    this.topic = topic;
    this.timeToFirstResponse = timeToFirstResponse;
    this.timeToResolution = timeToResolution;
    this.spaceId = spaceId;

  }
}
