package com.dacrt.SBIABackend.repository;

import java.util.Date;
import java.util.List;
import java.util.Optional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.dacrt.SBIABackend.entity.Riskevalfactors;
import com.dacrt.SBIABackend.entity.Riskevaluations;

@Repository
public interface RiskevaluationsRepository extends JpaRepository<Riskevaluations,Integer>, JpaSpecificationExecutor<Riskevaluations>{
	
    @Query("SELECT r FROM Riskevaluations r WHERE r.instrumentid = :instId " +
           "AND r.relateditem = :relItem AND r.relateditemdsc = :relDsc " +
           "AND CAST(r.initialdate AS date) = CAST(:initDate AS date)")
    Optional<Riskevaluations> findDuplicate(@Param("instId") Integer instId, 
                                            @Param("relItem") Integer relItem, 
                                            @Param("relDsc") String relDsc, 
                                            @Param("initDate") Date initDate);
	
    @Query(value=" SELECT count(*) FROM main.riskevaluations r WHERE r.instrumentid = :instId ", nativeQuery = true)
    int findInstrumento(@Param("instId") Integer instId);
	
    // ESTA LÍNEA SE ELIMINÓ PORQUE CAUSABA EL ERROR
    // Optional<Riskevaluations> findByRiskevaluationidAndRiskfactorid(Integer evalId, Integer rfId);

    List<Riskevaluations> findByInstrumentidAndStatus(Integer instrumentid, Integer status);
    
	//select count(*)  from main.riskevaluations r where r.instrumentid in (select t.instrumentid  from main.instrumentriskfactors t  where t.riskfactorid = 71)
	//and r.status = 0
	
	 @Query(value=" select count(*)  FROM main.riskevaluations r where r.instrumentid in (select t.instrumentid  from main.instrumentriskfactors t  where t.riskfactorid = :instId) and r.status = 0 ", nativeQuery = true)
     int findInstrumentoEvalOpen(@Param("instId") Integer instId);
}