package com.dacrt.SBIABackend.repository;


import java.util.List;
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.stereotype.Repository;

import com.dacrt.SBIABackend.dto.UnitsDtoStatus;
import com.dacrt.SBIABackend.entity.Units;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;


//import com.dacrt.SBIABackend.security.dto.ParamsDto;
import com.dacrt.SBIABackend.security.entity.Params;
import com.dacrt.SBIABackend.security.entity.Users;

@Repository
public interface UnitsRepository extends JpaRepository<Units,Integer>,JpaSpecificationExecutor<Units>{
	
	
	@Query("SELECT count(c) FROM Units c")
	long countRegUnits();
	
	Optional<Units> findById(int id);
	Optional<Units> findByRef(String ref);
	Optional<Units> findByName(String name);
	boolean existsByName(String name);
	boolean existsByRef(String ref);
	//boolean existsByWorkerid(int idworker);
	//boolean existsBySupplierid(int idsuppliers);
	boolean existsById(int id);
	
	@Query(value="SELECT u.id, u.dsc, u.status, CASE WHEN u.status = 1 THEN 'Activo' ELSE 'Inactivo' END AS estatus,f.id AS idfac, f.name AS namefac, v.id AS vpid, v.name AS namevp, "
			+ "elemento ->> 'dsc' AS desc, elemento ->> 'value' AS valor FROM Units u, Facilities f, jsonb_array_elements(p.value::jsonb) AS elemento, Vicepresidencies v, Params p "
			+ "WHERE u.facilityid = f.id AND u.vicepresidencyid = v.id AND p.paramname = 'UNIT_TYPE' AND elemento ->> 'value' = CAST(u.unittypepar AS TEXT) AND u.unittypepar IS NOT NULL AND u.status = 1",nativeQuery=true)
	//List<?>consultarSesionActiva(Integer idUsuario);
	//Optional<UsuarioSesion>consultarSesionActiva(Integer idUsuario);
	UnitsDtoStatus consultarUnidadesexpandir();
	
	
	@Transactional
	@Modifying
	@Query(" DELETE FROM Units e where e.id = ?1 ")
	void deleteunitsbyid(Integer id);
	
}