package org.egl_cepgl.pm.repository;

import org.egl_cepgl.pm.model.Cabinet;
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.stereotype.Repository;
import org.springframework.web.bind.annotation.PathVariable;

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

@Repository
public interface CabinetRepository extends JpaRepository<Cabinet, Long>
{
    Boolean existsByEmail(String email);

    Boolean existsByEmailAndIdNot(String email, Long id);

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

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

    @Query(value = "SELECT e.id AS id, e.namep AS namep"
            + " FROM egl_pm_db.cabinet e"
            +" WHERE e.status= true AND e.email NOT IN (SELECT u.email FROM egl_pm_db.users u)",
            nativeQuery = true)
    List<Map<String, Object>> findAllCabinetNamesNotInUser();

    Optional<Cabinet> findById(Long id);

    Optional<Cabinet> findByEmail(String email);

    @Query(value = "UPDATE egl_pm_db.cabinet SET status = :status WHERE id = :cabinet_id",nativeQuery = true)
    Cabinet changeStatus(Long cabinet_id, Boolean status);

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

    Page<Cabinet> findAllByCountry_NamepContainingIgnoreCaseOrNamepContainingIgnoreCaseOrQualification_NamepContainingIgnoreCaseOrCreatedatBetween(String s, String s1, String s2, LocalDate date1, LocalDate bdate2, Pageable pageable);

    Page<Cabinet> findAllByCountry_NamepContainingIgnoreCaseOrNamepContainingIgnoreCaseOrQualification_NamepContainingIgnoreCaseOrCreatedatEquals(String s, String s1, String s2, LocalDate date1, Pageable pageable);

    Page<Cabinet> findAllByCountry_NamepContainingIgnoreCaseOrNamepContainingIgnoreCaseOrQualification_NamepContainingIgnoreCase(String s, String s1, String s2, Pageable pageable);

    Page<Cabinet> findAllByCreatedatBetween(LocalDate date1, LocalDate date2, Pageable pageable);

    Page<Cabinet> findAllByCreatedatEquals(LocalDate date1, Pageable pageable);

}