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

import com.dacrt.SBIABackend.entity.Campaigns;
import com.dacrt.SBIABackend.entity.Facilities;
import com.dacrt.SBIABackend.security.entity.Users;

@Repository
public interface CampaignsRepository extends JpaRepository<Campaigns,Integer>,JpaSpecificationExecutor<Campaigns>{

	Optional<Campaigns> findById(int id);
	Optional<Campaigns> findByName(String name);
	//boolean existsByUsr(String usr);
	//boolean existsByEmail(String email);
	//boolean existsByWorkerid(int idworker);
	//boolean existsBySupplierid(int idsuppliers);
	boolean existsById(int id);
	boolean existsByStatus(int idstatus);
	//Optional<Campaigns> findByEmail(String Email);

	@Transactional
	@Modifying
	@Query(" DELETE FROM Campaigns e where e.id = ?1 ")
	void deleteCampaignsbyid(Integer id);
	
	@Transactional
	@Modifying
	@Query("update Campaigns e set e.status = 0 where id <> ?1")
	void updateAllCampaingById(Integer campaignid);
	
	@Transactional
	@Modifying
	@Query("update Campaigns e set e.status = 0")
	void updateAllCampaings();
	
	@Transactional
	@Modifying
	@Query(" update Campaigns e set e.status = 1 where id = ?1")
	void updateOneCampaingById(Integer campaignid);


	@Transactional
	@Modifying
	@Query(" update Campaigns e set e.status = ?2 where id = ?1")
	void updateOneCampaingByIdStatus(Integer campaignid,int idestatus);
	
	@Query("SELECT count (1) FROM Campaigns u where status = 1 ")
	int validarexisteactivo();
	
	//select count(1) from main.campaigns where id>4

	@Query("SELECT count (1) FROM Campaigns u where id > ?1 ")
	int countCampanassuperiores(Integer campaignid);
	
	@Query("SELECT  CASE WHEN u.dateto < now()	THEN TRUE ELSE FALSE END AS expiro FROM Campaigns u where u.status = 1 ")
	boolean consultarvigenciacampanaactiva();
	
	@Query(" SELECT c.status from Evalprocesses ep,Campaignunits cu ,Campaigns c WHERE ep.campaignunitid = cu.id and cu.campaingid = c.id and ep.id = ?1" )
	int findStatusCampaignByProcessId(int processid);
	
	@Query(" SELECT c.status from Evalunits ep,Campaignunits cu ,Campaigns c WHERE ep.campaingunitid = cu.id and cu.campaingid = c.id and ep.id = ?1" )
	int findStatusCampaignByEvalUnitId(int processid);
	
	@Query(value=" SELECT max(u.id) FROM Campaigns u")
	int getMaxRecordCampaign();
	
	/*@Transactional
	@Modifying
	@Query(" UPDATE Campaigns  SET status = 2 WHERE id IN (SELECT id FROM (select id, CASE WHEN dateto < now() then TRUE ELSE FALSE END AS expiro FROM Campaigns) as t1 WHERE expiro = false) ")
	void updateCampaingSoloExpired();*/
}