package com.dacrt.SBIABackend.repository;

import java.util.Optional;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.dacrt.SBIABackend.entity.Applications;
import com.dacrt.SBIABackend.entity.Evalunits;
import com.dacrt.SBIABackend.entity.Positions;
import com.dacrt.SBIABackend.entity.Processes;

public interface PositionsRepository extends JpaRepository<Positions,Integer>,JpaSpecificationExecutor<Positions>{
	
	boolean existsByName(String name);
	
	Optional<Positions> getByName(String name);
	
	boolean existsById(Integer id);
	
	@Query(value=" SELECT u FROM Positions u WHERE u.id= ?1 ")
	Positions getById(int id);
	
	@Query(value=" SELECT count(1) FROM Positions u WHERE u.id= ?1 and u.status = 1 ")
	int getByIdandStatus(int id);

	@Transactional
	@Modifying
	@Query(" DELETE FROM Positions p where p.id = ?1 ")
	void borrarPositions(int processid);
	
	//@Query(value=" SELECT count(u) FROM Workers u WHERE u.positionunitid= ?1 ")
	//int getCountEmpleados(int id);
	
	@Query(value=" SELECT count(u) FROM Positionunits u WHERE u.positionid = ?1 ")
	int getCountPrositionsUnits(int id);
	
//	@Query(value=" SELECT count(u) FROM Evalunitpos u WHERE u.positionid = ?1 ")
//	int getCountEvalunitpos(int id);
	@Query(value=" SELECT count(1) from Positions s,Evalunitpos w where s.id = ?1 and s.id = w.positionid ")
 	int getCountEvalunitpos(int id);
	
	@Query(value=" select count(1) from Positionunits s,Workers w where s.positionid = ?1  and s.id = w.positionunitid")
	int getCountEmpleados(int id);
	
	/*@Query(value=" SELECT count(u) FROM Workers u WHERE u.positionidid= ?1 ")
	int getCountEmpleados(int id);
	
	@Query(value=" SELECT count(u) FROM Positionunits u WHERE u.positionid = ?1 ")
	int getCountPrositionsUnits(int id);
	
	@Query(value=" SELECT count(u) FROM Evalunitpos u WHERE u.positionid.id = ?1 ")
	int getCountEvalunitpos(int id);*/
}