package org.egl_cepgl.pm.repository;

import org.egl_cepgl.pm.dto.ProcurementDto;
import org.egl_cepgl.pm.model.Procurement;
import org.egl_cepgl.pm.model.Project;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.Collection;
import java.util.Date;
import java.util.List;

public interface ProcurementRepository extends JpaRepository<Procurement, Long>
{
    String query= "select p.*, pr.*, c.* from egl_pm_db.procurement p "
        +"join egl_pm_db.project pr on p.project_id = pr.id "
        +"join egl_pm_db.procurement_category c on p.category_id = c.id "
        +"where (LOWER(p.namep) like LOWER(CONCAT('%',:search,'%')) "
        +"or LOWER(pr.namep) like LOWER(CONCAT('%',:search,'%')) or LOWER(c.namep) like LOWER(CONCAT('%',:search,'%'))) ";

    String query2= "and cast(p.created_at as DATE) = cast(:bdate as DATE) ";

    String query3= "and cast(p.created_at as DATE) between cast(:bdate as DATE) and cast(:edate as DATE)";

    @Query(value = query, nativeQuery = true)
    Page<Procurement> findAllByFilters(String search, Pageable pageable);

    @Query(value = query + query2, nativeQuery = true)
    Page<Procurement> findAllByFilters2(String search, Pageable pageable, String bdate);

    @Query(value = query + query3, nativeQuery = true)
    Page<Procurement> findAllByFilters3(String search, Pageable pageable, String bdate, String edate);

    List<Procurement> findAllByIdIsIn(Collection<Long> pro_ids);
}