package com.example.todolist.repository;

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

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.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.example.todolist.entity.Todo;

@Repository
public interface TodoRepository extends JpaRepository<Todo, Integer> {
    List<Todo> findByTitleLike(String title);

    List<Todo> findByImportance(Integer importance);

    List<Todo> findByUrgency(Integer urgency);

    List<Todo> findByDeadlineBetweenOrderByDeadlineAsc(LocalDate from, LocalDate to);

    List<Todo> findByDeadlineGreaterThanEqualOrderByDeadlineAsc(LocalDate from);

    List<Todo> findByDeadlineLessThanEqualOrderByDeadlineAsc(LocalDate to);

    List<Todo> findByDone(String done);

    // SELECT * FROM todo ORDER BY id に相当
    //List<Todo> findAllByOrderById();

    // SELECT * FROM todo WHERE owner_id=accountId ORDER BY id に相当
    List<Todo> findByOwnerIdOrderById(Integer accountId); // 追加

    @Query(value = """
            SELECT * FROM todo WHERE
            (CAST(:title AS text)         IS NULL OR title LIKE CAST(:title AS text))           AND
            (CAST(:importance AS integer) IS NULL OR importance = CAST(:importance AS integer)) AND
            (CAST(:urgency AS integer)    IS NULL OR urgency    = CAST(:urgency AS integer))    AND
            (CAST(:deadlineFrom AS date)  IS NULL OR deadline  >= CAST(:deadlineFrom AS date))  AND
            (CAST(:deadlineTo   AS date)  IS NULL OR deadline  <= CAST(:deadlineTo   AS date))  AND
            (CAST(:done AS text)          IS NULL OR done       = CAST(:done AS text))AND
            owner_id = CAST(:accountId AS integer)
            ORDER BY id
            """, countQuery = """
            SELECT COUNT(*) FROM todo WHERE
             (CAST(:title AS text)         IS NULL OR title LIKE CAST(:title AS text))           AND
             (CAST(:importance AS integer) IS NULL OR importance = CAST(:importance AS integer)) AND
             (CAST(:urgency AS integer)    IS NULL OR urgency    = CAST(:urgency AS integer))    AND
             (CAST(:deadlineFrom AS date)  IS NULL OR deadline  >= CAST(:deadlineFrom AS date))  AND
             (CAST(:deadlineTo   AS date)  IS NULL OR deadline  <= CAST(:deadlineTo   AS date))  AND
             (CAST(:done AS text)          IS NULL OR done       = CAST(:done AS text))AND
            owner_id = CAST(:accountId AS integer)
             """, nativeQuery = true)
    Page<Todo> searchByNativeQuery(
            @Param("title") String title,
            @Param("importance") Integer importance,
            @Param("urgency") Integer urgency,
            @Param("deadlineFrom") LocalDate deadlineFrom,
            @Param("deadlineTo") LocalDate deadlineTo,
            @Param("done") String done,
            @Param("accountId") Integer accountId,
            Pageable pageable);
}