package eu.dnetlib.data.openaire.dsm.repository;

import java.util.List;

import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
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 eu.dnetlib.data.is.common.ReadOnlyRepository;
import eu.dnetlib.data.openaire.dsm.model.BrowseTerm;
import eu.dnetlib.data.openaire.dsm.model.view.SimpleDsWithApis;

@Repository
@ConditionalOnProperty(value = "openaire.api.enable.dsm", havingValue = "true")
public interface SimpleDsWithApisRepository extends ReadOnlyRepository<SimpleDsWithApis, String>, JpaSpecificationExecutor<SimpleDsWithApis> {

	@Query(value = "select * from dsm_datasources_view where id = :value or name ilike %:value%  or other_name ilike %:value%", nativeQuery = true)
	Page<SimpleDsWithApis> search(@Param("value") String value, Pageable pageable);

	Page<SimpleDsWithApis> findByType(String type, Pageable pageable);

	Page<SimpleDsWithApis> findByCollectedFrom(String collectdFrom, Pageable pageable);

	Page<SimpleDsWithApis> findByConsenttermsofuse(Boolean consenttermsofuse, Pageable pageable);

	Page<SimpleDsWithApis> findByFulltextdownload(Boolean fulltextdownload, Pageable pageable);

	@Query(value = "select * from dsm_datasources_view where ? = ANY(hidden_compliances)", nativeQuery = true)
	Page<SimpleDsWithApis> findByCompliance(String compliance, Pageable pageable);

	@Query(value = "select * from dsm_datasources_view where ? = ANY(hidden_countries)", nativeQuery = true)
	Page<SimpleDsWithApis> findByCountry(String country, Pageable pageable);

	@Query(value = "select * from dsm_datasources_view where ? = ANY(hidden_protocols)", nativeQuery = true)
	Page<SimpleDsWithApis> findByProtocol(String brotocol, Pageable pageable);

	@Query(value = "select * from dsm_datasources_view where ? = ANY(hidden_actives)", nativeQuery = true)
	Page<SimpleDsWithApis> findByActive(Boolean active, Pageable pageable);

	@Query(value = "select type as term, type as name, count(*) as total from dsm_datasources_view group by type order by total desc", nativeQuery = true)
	List<BrowseTerm> browseTermsForType();

	@Query(value = "select collectedfrom as term, collectedfrom as name, count(*) as total from dsm_datasources_view group by collectedfrom order by total desc", nativeQuery = true)
	List<BrowseTerm> browseTermsForCollectedFrom();

	@Query(value = "select c as term, c as name, count(*) as total from (select unnest(hidden_compliances) as c from dsm_datasources_view) as t group by c order by total desc", nativeQuery = true)
	List<BrowseTerm> browseTermsForCompliance();

	@Query(value = "select c as term, c as name, count(*) as total from (select unnest(hidden_countries) as c from dsm_datasources_view) as t group by c order by total desc", nativeQuery = true)
	List<BrowseTerm> browseTermsForCountry();

	@Query(value = "select c as term, c as name, count(*) as total from (select unnest(hidden_protocols) as c from dsm_datasources_view) as t group by c order by total desc", nativeQuery = true)
	List<BrowseTerm> browseTermsForProtocol();

	@Query(value = "select c as term, c as name, count(*) as total from (select unnest(hidden_actives) as c from dsm_datasources_view) as t group by c order by total desc", nativeQuery = true)
	List<BrowseTerm> browseTermsForActive();

	@Query(value = "select consenttermsofuse as term, consenttermsofuse as name, count(*) as total from dsm_datasources_view group by consenttermsofuse order by total desc", nativeQuery = true)
	List<BrowseTerm> browseTermsForConsenttermsofuse();

	@Query(value = "select fulltextdownload as term, fulltextdownload as name, count(*) as total from dsm_datasources_view group by fulltextdownload order by total desc", nativeQuery = true)
	List<BrowseTerm> browseTermsForFulltextdownload();

}
