package org.egl_cepgl.pm.repository;

import org.egl_cepgl.pm.model.Applicant;
import org.egl_cepgl.pm.model.ApplicantQualification;
import org.egl_cepgl.pm.model.CustomModel;
import org.egl_cepgl.pm.model.Procurement;
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 ApplicantRepository extends JpaRepository<Applicant, Long>
{

    Boolean existsByEmail(String email);

    @Query(value = "SELECT a.id AS id, a.first_name AS first_name, a.last_name AS last_name, aq.namep AS qualification"
                    + " FROM egl_pm_db.applicant a INNER JOIN egl_pm_db.applicant_qualification aq ON a.qualification= aq.id WHERE a.status= true",
           nativeQuery = true)
    List<Map<String, Object>> findAllApplicantNames();

    @Query(value = "SELECT a.id AS id, a.first_name AS first_name, a.last_name AS last_name, aq.namep AS qualification"
                     + " FROM egl_pm_db.applicant a INNER JOIN egl_pm_db.applicant_qualification aq ON a.qualification= aq.id"
                     +" WHERE a.status= true AND a.id IN (SELECT applicant_id FROM egl_pm_db.procurement_applicants WHERE procurement_id = :procurement_id)",
            nativeQuery = true)
    List<Map<String, Object>> findAllApplicantNamesByProcurement(@PathVariable("procurement_id") Long procurement_id);

    Optional<Applicant> findById(Long id);

    Optional<Applicant> findByEmail(String email);

    @Query(value = "UPDATE egl_pm_db.applicant SET status = :status WHERE id = :applicant_id",nativeQuery = true)
    Applicant changeStatus(Long applicant_id, Boolean status);

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

    String query= "select a.*, q.*, c.* from egl_pm_db.applicant a "
            +"join egl_pm_db.applicant_qualification q on a.qualification = q.id "
            +"join egl_pm_db.country c on a.nationality = c.id "
            +"where LOWER(a.first_name) like LOWER(CONCAT('%',:search,'%')) "
            +"and (LOWER(a.last_name) 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<Applicant> findAllByFilters(String search, Pageable pageable);

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

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