package org.egl_cepgl.pm.repository;

import org.egl_cepgl.pm.model.Applicant;
import org.egl_cepgl.pm.model.CustomModel;
import org.egl_cepgl.pm.model.Enterprise;
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 org.springframework.web.bind.annotation.PathVariable;

import java.util.List;
import java.util.Map;
import java.util.Optional;

public interface EnterpriseRepository extends JpaRepository<Enterprise, Long>
{

    Boolean existsByEmail(String email);

    @Query(value = "SELECT e.id AS id, e.namep AS namep"
            + " FROM egl_pm_db.enterprise e WHERE e.status= true",
            nativeQuery = true)
    List<Map<String, Object>> findAllEnterpriseNames();

    @Query(value = "SELECT e.id AS id, e.namep AS namep"
            + " FROM egl_pm_db.enterprise e"
            +" WHERE e.status= true AND e.id IN (SELECT enterprise_id FROM egl_pm_db.procurement_enterprises WHERE procurement_id = :procurement_id)",
            nativeQuery = true)
    List<Map<String, Object>> findAllEnterpriseNamesByProcurement(@PathVariable("procurement_id") Long procurement_id);

    Optional<Enterprise> findById(Long id);

    Optional<Enterprise> findByEmail(String email);

    @Query(value = "UPDATE egl_pm_db.enterprise SET status = :status WHERE id = :enterprise_id",nativeQuery = true)
    Enterprise changeStatus(Long enterprise_id, Boolean status);

    @Query(value = "select t.id, t.namep from egl_pm_db.enterprise t",nativeQuery = true)
    List<Map<String, Object>> findAllIdsAndNames();

    String query= "select a.*, q.*, c.* from egl_pm_db.enterprise a "
            +"join egl_pm_db.enterprise_qualification q on a.qualification = q.id "
            +"join egl_pm_db.country c on a.nationality = c.id "
            +"where (LOWER(a.namep) like LOWER(CONCAT('%',:search,'%')) "
            +"or LOWER(q.namep) like LOWER(CONCAT('%',:search,'%')) or LOWER(c.namep) like LOWER(CONCAT('%',:search,'%'))) ";

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

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

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

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

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

}