package br.gov.sigefp.rh.repository;

import br.gov.sigefp.rh.domain.Agent;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

import java.util.Optional;
import java.util.UUID;

@Repository
public interface AgentRepository extends JpaRepository<Agent, UUID>, JpaSpecificationExecutor<Agent> {

    Optional<Agent> findByMatricula(String matricula);

    Optional<Agent> findByNif(String nif);

    Optional<Agent> findByBiNumber(String biNumber);

    boolean existsByMatricula(String matricula);

    boolean existsByNif(String nif);

    boolean existsByBiNumber(String biNumber);

    java.util.List<Agent> findByStatus(String status);

    org.springframework.data.domain.Page<Agent> findByFullNameContainingIgnoreCaseOrMatriculaContainingIgnoreCase(
            String fullName, String matricula, org.springframework.data.domain.Pageable pageable);

    long countByStatus(String status);

    boolean existsBySalaryCategory(UUID salaryCategory);

    boolean existsBySalaryGrade(UUID salaryGrade);

    boolean existsBySalaryStep(UUID salaryStep);

    @org.springframework.data.jpa.repository.Query("SELECT a.status, COUNT(a) FROM Agent a GROUP BY a.status")
    java.util.List<Object[]> countAgentsByStatus();

    @org.springframework.data.jpa.repository.Query("SELECT a.status, COUNT(a) FROM Agent a WHERE (:spec IS NULL OR a.status IS NOT NULL) GROUP BY a.status")
    java.util.List<Object[]> countAgentsByStatusWithSpecification(
            @org.springframework.data.repository.query.Param("spec") org.springframework.data.jpa.domain.Specification<Agent> spec);
}
