package com.dacrt.SBIABackend.security.repository;

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

import javax.transaction.Transactional;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.dacrt.SBIABackend.security.entity.Params;
import com.dacrt.SBIABackend.security.entity.Users;

@Repository
public interface UsersRepository extends JpaRepository<Users,Integer>{
	Optional<Users> findByUsr(String usr);
	boolean existsByUsr(String usr);
	boolean existsByEmail(String email);
	boolean existsByWorkerid(int idworker);
	boolean existsBySupplierid(int idsuppliers);
	boolean existsById(int id);
	Optional<Users> findByEmail(String Email);
	Optional<Users> findById(int id);
	
	 @Query(value="SELECT COUNT(1) FROM main.users a WHERE a.rolid=?1",nativeQuery=true)
	   int existsByRolid(int rolid);
	
	@Query(value="SELECT count(1) FROM main.users WHERE id=?1 AND fails = 0 and sessionid is not null",nativeQuery=true)
	//List<?>consultarSesionActiva(Integer idUsuario);
	//Optional<UsuarioSesion>consultarSesionActiva(Integer idUsuario);
	int consultarSesionEstatus(Integer id);
	
	@Query(value="SELECT count(1) FROM main.users WHERE workerid=?1",nativeQuery=true)
	int consultarUsuWorkers(Integer id);
	
	@Query(value="SELECT count(1) FROM main.users WHERE supplierid=?1",nativeQuery=true)
	int consultarUsuSupplierid(Integer id);
	
	@Query(value=" SELECT u FROM Users u WHERE u.sessionid = ?1 ")
	Optional<Users> getBySessionid(String sessionid);
	
	@Query(value=" SELECT rolid FROM Users u WHERE u.sessionid = ?1 ")
	int getRolbySessionid(String sessionid);
	//boolean existsByCodEmpleado(String codEmpleado);
	@Transactional
	@Modifying
	@Query(value="UPDATE main.users "
			+ " SET sessionid = ?2 "
			+ " ,lastsession = ?3 "
			+ " ,fails = ?4 "
			+ " ,status = ?5 "
			+ " WHERE id =?1 ", nativeQuery=true)
	void asociarSessionidydatos(Integer id,String jwt,Date fechaDate,int fail,int idestatus);
	
	@Transactional
	@Modifying
	@Query(value="UPDATE main.users "
			+ " SET workerid = NULL "
			+ " WHERE id =?1 ", nativeQuery=true)
	void updateWorkeridbyId(Integer idusers);
	
	@Transactional
	@Modifying
	@Query(value="UPDATE main.users "
			+ " SET supplierid = NULL "
			+ " WHERE id =?1 ", nativeQuery=true)
	void updateSuppliersidbyId(Integer idusers);
	
	@Transactional
	@Modifying
	@Query(value="UPDATE main.users "
			+ " SET sessionid = ?2 "
			+ " ,lastsession = ?3 "
			+ " ,fails = ?4 "
			+ " ,status = ?5 "
			+ " ,validthru = ?6 "
			+ " WHERE id =?1 ", nativeQuery=true)
	void asociarSessionidydatosvalid(Integer id,String jwt,Date fechaDate,int fail,int idestatus,Date validthru);
	
	@Transactional
	@Modifying
	@Query(value="UPDATE main.users "
			+ " SET sessionid = NULL,  "
			+ " status = 1, "
			+ " fails = 0, "
			+ " pwd = ?2 ,"
			+ " oldpwds = ?3 ,"
			+ " lastpwdchange = ?4 "
			+ " WHERE id =?1 ", nativeQuery=true)
	void cambiarContrasena(Integer id,String passwd, String oldpasswd, Date fecha);
	
	@Transactional
	@Modifying
	@Query(value="UPDATE main.users "
			+ " SET status = 1, "
			+ " fails = 0, "
			+ " pwd = ?2 ,"
			+ " oldpwds = ?3 ,"
			+ " lastpwdchange = ?4 "
			+ " WHERE id =?1 ", nativeQuery=true)
	void cambiarContrasenaSinSession(Integer id,String passwd, String oldpasswd, Date fecha);
	
	@Transactional
	@Modifying
	@Query(value="UPDATE main.users "
			+ " SET sessionid = NULL "
			+ " AND validthru = NULL "
			+ " WHERE id =?1 ", nativeQuery=true)
	void asociarSessionid(Integer id,String jwt,Date fechaDate);
	
	@Transactional
	@Modifying
	@Query(value="UPDATE main.users "
			+ " SET validthru = ?2 "
			+ " WHERE id =?1 ", nativeQuery=true)
	void asociarcambioValidthru(Integer id,Date fechaDate);
	
	@Transactional
	@Modifying
	@Query(value="UPDATE main.users "
			+ " SET validthru = ?2 AND sessionid = NULL"
			+ " WHERE id =?1 ", nativeQuery=true)
	void asociarcambioValidthruSession(Integer id,Date fechaDate);
	
	/*@Transactional
	@Modifying
	@Query(value="UPDATE main.users "
			+ " SET status = ?3 "
			+ " ,modifiedat = ?2 "
			+ " ,fails = ?3 "
			+ " WHERE id =?1 ", nativeQuery=true)
	void deleteusersbyid(Integer id,Date fechaDate,int valor);*/
	
	@Transactional
	@Modifying
	@Query(" DELETE FROM Users e where e.id = ?1 ")
	void deleteusersbyid(Integer id);
 
}