package io.aiven.klaw.repository;

import io.aiven.klaw.dao.SchemaRequest;
import io.aiven.klaw.dao.SchemaRequestID;
import java.util.List;
import java.util.Optional;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.data.repository.query.QueryByExampleExecutor;

public interface SchemaRequestRepo
    extends CrudRepository<SchemaRequest, SchemaRequestID>, QueryByExampleExecutor<SchemaRequest> {
  Optional<SchemaRequest> findById(SchemaRequestID schemaRequestId);

  boolean existsByTenantIdAndEnvironmentAndRequestStatusAndTopicname(
      int tenantId, String environment, String requestStatus, String topicname);

  boolean existsByTenantIdAndEnvironmentAndRequestStatusAndRequestOperationTypeAndTopicname(
      int tenantId,
      String environment,
      String requestStatus,
      String requestOperationType,
      String topicname);

  @Query(
      value = "select max(avroschemaid) from kwschemarequests where tenantid = :tenantId",
      nativeQuery = true)
  Integer getNextSchemaRequestId(@Param("tenantId") Integer tenantId);

  boolean existsSchemaRequestByEnvironmentAndTenantId(
      @Param("envId") String envId, @Param("tenantId") Integer tenantId);

  boolean existsSchemaRequestByEnvironmentAndTenantIdAndRequestStatus(
      String envId, Integer tenantId, String requestStatus);

  boolean existsByTeamIdAndTenantIdAndRequestStatus(
      Integer teamId, Integer tenantId, String topicStatus);

  boolean existsByRequestorAndTenantIdAndRequestStatus(
      String requestor, Integer tenantId, String requestStatus);

  List<SchemaRequest> findAllByTenantId(int tenantId);

  @Query(
      value =
          "select requesttype, count(*) from kwschemarequests where tenantid = :tenantId"
              + " and teamid = :teamId group by requesttype",
      nativeQuery = true)
  List<Object[]> findAllSchemaRequestsGroupByOperationType(
      @Param("teamId") Integer teamId, @Param("tenantId") Integer tenantId);

  @Query(
      value =
          "select topicstatus, count(*) from kwschemarequests where tenantid = :tenantId"
              + " and teamid = :teamId group by topicstatus",
      nativeQuery = true)
  List<Object[]> findAllSchemaRequestsGroupByStatus(
      @Param("teamId") Integer teamId, @Param("tenantId") Integer tenantId);

  @Query(
      value =
          "select count(*) from kwschemarequests where tenantid = :tenantId"
              + " and teamid = :teamId and requestor != :requestor "
              + " and topicstatus = :topicStatus group by topicstatus",
      nativeQuery = true)
  Long countRequestorsSchemaRequestsGroupForStatusType(
      @Param("teamId") Integer teamId,
      @Param("tenantId") Integer tenantId,
      @Param("requestor") String requestor,
      @Param("topicStatus") String topicStatus);

  void deleteByTenantId(int tenantId);
}
