package com.dacrt.SBIABackend.repository;

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 org.springframework.stereotype.Repository;

import com.dacrt.SBIABackend.entity.Evalprocreports;

@Repository
public interface EvalprocreportsRepository extends JpaRepository<Evalprocreports,Integer>,JpaSpecificationExecutor<Evalprocreports>{
	@Query(" SELECT count(1) FROM Evalprocreports e where e.evalprocessid.id = ?1 AND e.regreportid.id = ?2 ")
	int contarEvalprocIdreports(Integer idProcess, Integer idReporte);
	
	@Query(" SELECT count(1) FROM Evalprocreports e where e.evalprocessid.id = ?1 ")
	int contarEvalprocreports(Integer id);
	
	@Query(" SELECT count(e) FROM Evalprocreports e where e.evalprocessid.id = ?1 ")
	Long contarEvalprocreportes(Integer id);
	
	@Transactional
	@Modifying
	@Query(" DELETE FROM Evalprocreports e where e.evalprocessid.id = ?1 ")
	void deleteEvalprocreports(Integer id);
	
	@Transactional
	@Modifying
	@Query(" DELETE FROM Evalprocreports e where e.evalprocessid.id = ?1 AND e.regreportid.id = ?2")
	void deleteEvalprocesIdreports(Integer idProcess, Integer idReporte);
	
	@Transactional
	@Modifying
	@Query("DELETE FROM Evalprocreports e WHERE e.regreportid.id IN (SELECT r.id "
	        + "FROM Regreports r "
	        + "LEFT JOIN Requesters req ON r.requesterid = req.id "
	        + "WHERE ( lower(r.name) LIKE %?1% " // Corregido: Espacio antes de % y sin LIKE repetido
	        + "OR lower(req.name) LIKE %?1% "    // Corregido: Sin LIKE repetido
	        + "OR lower(frequency) LIKE %?1% "  // Corregido: Sin LIKE repetido
	        + "OR lower(penalty) LIKE %?1% )) and e.evalprocessid.id = ?2 ") // Corregido: Sin LIKE repetido y paréntesis de cierre
	void deleteFiltroEvalprocreports(String filtro,Integer idEvalProcreport);
	
	@Query("SELECT count(e) FROM Evalprocreports e WHERE e.regreportid.id IN (SELECT r.id "
	        + "FROM Regreports r "
	        + "LEFT JOIN Requesters req ON r.requesterid = req.id "
	        + "WHERE ( lower(r.name) LIKE %?1% " // Corregido: Espacio antes de % y sin LIKE repetido
	        + "OR lower(req.name) LIKE %?1% "    // Corregido: Sin LIKE repetido
	        + "OR lower(frequency) LIKE %?1% "  // Corregido: Sin LIKE repetido
	        + "OR lower(penalty) LIKE %?1% )) and e.evalprocessid.id = ?2 ") // Corregido: Sin LIKE repetido y paréntesis de cierre
	Long cantidadEvalprocreports(String filtro,Integer idEvalProcreport);
	
	@Transactional
    @Modifying
    @Query(value = "INSERT INTO main.evalprocreports (regreportid, evalprocessid, createdat, modifiedat) " +
            "SELECT r.id, ?2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP " +
            "FROM main.regreports r " +
            "LEFT JOIN main.requesters req ON r.requesterid = req.id " +
            "WHERE ( lower(r.name) LIKE %?1% " +
            "OR lower(req.name) LIKE %?1% " +
            "OR lower(frequency) LIKE %?1% " +
            "OR lower(penalty) LIKE %?1% )", nativeQuery = true)
    void insertEvalprocreportsByFilter(String filtro, Integer evalprocessId);
	
	@Transactional
    @Modifying
    @Query(value = "INSERT INTO main.evalprocreports (regreportid, evalprocessid, createdat, modifiedat) " +
            "SELECT r.id, ?1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP " +
            "FROM main.regreports r " +
            "LEFT JOIN main.requesters req ON r.requesterid = req.id ", nativeQuery = true)
    void insertEvalprocreportsSinFilter(Integer evalprocessId);

}