package io.aiven.klaw.repository;

import io.aiven.klaw.dao.AclRequestID;
import io.aiven.klaw.dao.AclRequests;
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 AclRequestsRepo
    extends CrudRepository<AclRequests, AclRequestID>, QueryByExampleExecutor<AclRequests> {
  Optional<AclRequests> findById(AclRequestID aclRequestID);

  List<AclRequests> findAllByTenantId(int tenantId);

  List<AclRequests>
      getAllByRequestStatusAndRequestorNotAndApprovalsRequiredApproverAndApprovalsApproverName(
          String requestStatus, String requestor, String requiredApprover, String approverName);

  List<AclRequests> getAllByRequestStatusAndRequestorNotAndApprovalsRequiredApprover(
      String requestStatus, String requestor, String requiredApprover);

  List<AclRequests> getAllByApprovalsRequiredApprover(String requiredApprover);

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

  boolean existsByTenantIdAndEnvironmentAndRequestStatusAndTopicnameAndAssociatedAclId(
      int tenantId, String env, String requestStatus, String topicName, int associatedAclId);

  boolean existsByTenantIdAndEnvironmentAndRequestStatus(
      int tenantId, String env, String requestStatus);

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

  boolean existsByTeamIdAndTenantIdAndRequestStatus(
      @Param("teamId") Integer teamId, @Param("tenantId") Integer tenantId, String requestStatus);

  @Query(
      value =
          "select count(*) from kwaclrequests where (requestor = :userId) and tenantid = :tenantId and topicstatus='created'",
      nativeQuery = true)
  List<Object[]> findAllRecordsCountForUserId(
      @Param("userId") String userId, @Param("tenantId") Integer tenantId);

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

  @Query(
      value = "select max(aclid) from kwaclrequests where tenantid = :tenantId",
      nativeQuery = true)
  Integer getNextAclRequestId(@Param("tenantId") Integer tenantId);

  // requests raised by my team
  @Query(
      value =
          "select acltype, count(*) from kwaclrequests where tenantid = :tenantId"
              + " and requestingteam = :requestingTeamId group by acltype",
      nativeQuery = true)
  List<Object[]> findAllAclRequestsGroupByOperationTypeMyTeam(
      @Param("requestingTeamId") Integer requestingTeamId, @Param("tenantId") Integer tenantId);

  // requests raised by my team
  @Query(
      value =
          "select topicstatus, count(*) from kwaclrequests where tenantid = :tenantId"
              + " and requestingteam = :requestingTeamId group by topicstatus",
      nativeQuery = true)
  List<Object[]> findAllAclRequestsGroupByStatusMyTeam(
      @Param("requestingTeamId") Integer requestingTeamId, @Param("tenantId") Integer tenantId);

  // requests assigned to my team
  @Query(
      value =
          "select acltype, count(*) from kwaclrequests where tenantid = :tenantId"
              + " and teamid = :assignedToTeamId group by acltype",
      nativeQuery = true)
  List<Object[]> findAllAclRequestsGroupByOperationTypeAssignedToTeam(
      @Param("assignedToTeamId") Integer assignedToTeamId, @Param("tenantId") Integer tenantId);

  // requests assigned to my team
  @Query(
      value =
          "select topicstatus, count(*) from kwaclrequests where tenantid = :tenantId"
              + " and teamid = :assignedToTeamId group by topicstatus",
      nativeQuery = true)
  List<Object[]> findAllAclRequestsGroupByStatusAssignedToTeam(
      @Param("assignedToTeamId") Integer assignedToTeamId, @Param("tenantId") Integer tenantId);

  // requests assigned to my team
  @Query(
      value =
          "select count(*) from kwaclrequests where tenantid = :tenantId"
              + " and teamid = :assignedToTeamId and requestor != :requestor "
              + " and topicstatus = :topicStatus group by topicStatus",
      nativeQuery = true)
  Long countRequestorsAclRequestsGroupByStatusType(
      @Param("assignedToTeamId") Integer assignedToTeamId,
      @Param("tenantId") Integer tenantId,
      @Param("requestor") String requestor,
      @Param("topicStatus") String topicStatus);

  void deleteByTenantId(int tenantId);
}
