package io.delphiplatform.api.v3.rdb.repository;

import org.springframework.data.domain.Pageable;
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 java.util.Collection;
import java.util.List;

import io.delphiplatform.api.v3.rdb.entity.ChartEntity;
import io.delphiplatform.api.v3.rdb.entity.ChartProjection;

public interface ChartRepository extends JpaRepository<ChartEntity, String>, JpaSpecificationExecutor<ChartEntity> {

    @Query(
        value = ""
            + " select "
            + "     ch.chart_id,"
            + "     ch.dsp_id,"
            + "     ch.country_code,"
            + "     ch.name,"
            + "     ch.num_tracks,"
            + "     ch.uri,"
            + "     ch.dsp_chart_id,"
            + "     ch.chart_group,"
            + "     dsp.name as dsp_name,"
            + "     dsp.slug as dsp_slug"
            + " from chart ch "
            + " inner join dsp on ch.dsp_id = dsp.dsp_id "
            + " where ch.dsp_id in (:dsps) "
            + "     and (:chartGroup is NULL or ch.chart_group = CAST(:chartGroup AS TEXT)) "
            + "     and ((:countries) is null or ch.country_code in (:countries)) ",
        nativeQuery = true
    )
    List<ChartProjection> findByDspAndChartGroupAndCountries(@Param("chartGroup") String chartGroup,
        @Param("dsps") Collection<String> dsps, @Param("countries") Collection<String> countries,
        Pageable pageable);

}