package com.devjob.repository;

import com.devjob.domain.Job;
import com.devjob.domain.JobStatus;
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.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

/**
 * EP11: Pageable을 받아 Page<Job>을 반환하는 페이징 쿼리 메서드.
 * EP13: company는 LAZY이므로, 목록 조회처럼 company 정보가 항상 필요한 경우
 * fetch join으로 N+1을 방지한 findByStatusWithCompany를 사용한다.
 * EP14: 쿼리 메서드로 표현하기 어려운 조건은 @Query + JPQL로,
 * 조건이 동적으로 바뀌는 검색은 JpaSpecificationExecutor로 처리한다.
 */
public interface JobRepository extends JpaRepository<Job, Long>, JpaSpecificationExecutor<Job> {

    Page<Job> findByStatus(JobStatus status, Pageable pageable);

    Page<Job> findByTitleContaining(String title, Pageable pageable);

    @Query(value = """
            SELECT j FROM Job j
            JOIN FETCH j.company
            WHERE j.status = :status
            """,
            countQuery = "SELECT COUNT(j) FROM Job j WHERE j.status = :status")
    Page<Job> findByStatusWithCompany(@Param("status") JobStatus status, Pageable pageable);

    /**
     * 제목 또는 회사명에 keyword가 포함되고, location이 일치하는 OPEN 공고를 검색한다.
     * keyword/location이 null이면 해당 조건은 건너뛴다.
     */
    @Query(value = """
            SELECT j FROM Job j
            JOIN FETCH j.company c
            WHERE (:keyword IS NULL OR
                   LOWER(j.title) LIKE LOWER(CONCAT('%', :keyword, '%'))
                   OR LOWER(c.name) LIKE LOWER(CONCAT('%', :keyword, '%')))
              AND (:location IS NULL OR c.location = :location)
              AND j.status = 'OPEN'
            """,
            countQuery = """
            SELECT COUNT(j) FROM Job j
            JOIN j.company c
            WHERE (:keyword IS NULL OR
                   LOWER(j.title) LIKE LOWER(CONCAT('%', :keyword, '%'))
                   OR LOWER(c.name) LIKE LOWER(CONCAT('%', :keyword, '%')))
              AND (:location IS NULL OR c.location = :location)
              AND j.status = 'OPEN'
            """)
    Page<Job> searchJobs(@Param("keyword") String keyword, @Param("location") String location, Pageable pageable);
}
