source: src/main/java/edu/gjoko/schedlr/repositories/AppointmentRepository.java

package edu.gjoko.schedlr.repositories;

import edu.gjoko.schedlr.entity.Appointment;
import edu.gjoko.schedlr.entity.Stakeholder;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface AppointmentRepository extends JpaRepository<Appointment, Long> {

    @Query(value = "select ap from Appointment as ap " +
            "where ap.service.business.id = :businessId " +
            "and ap.appointmentStatus = 'NEW'")
    List<Appointment> getActiveAppointmentsByBusiness(Long businessId);

    @Query( value = "select ap from Appointment as ap " +
            "where ap.service.business.id = :businessId " +
            "and (" +
            "(ap.startTime between :startDate and :endDate) " +
            "or (ap.endTime between :startDate and :endDate) " +
            "or (:startDate = ap.startTime and ap.startTime = :endDate)" +
            "or (:startDate > ap.startTime and ap.endTime > :endDate)" +
            ")")
    List<Appointment> findBlockingAppointments(Long businessId, LocalDateTime startDate, LocalDateTime endDate);

    @Query(value = "select ap from Appointment  as ap " +
            "where ap.service.business.owner.id = :businessOwnerId " +
            "and ap.startTime > :now ")
    List<Appointment> findFutureAppointmentsByBusinessOwnerId(Long businessOwnerId, LocalDateTime now);

    @Query(value = "select ap from Appointment  as ap " +
            "where ap.customer.id = :customerId " +
            "and ap.startTime < :now")
    List<Appointment> findPastAppointmentsByCustomerId(Long customerId, LocalDateTime now);

    @Query(value = "select ap from Appointment  as ap " +
            "where ap.service.business.owner.id = :businessOwnerId " +
            "and ap.startTime < :now ")
    List<Appointment> findPastAppointmentsByBusinessOwnerId(Long businessOwnerId, LocalDateTime now);

    @Query(value = "select ap from Appointment  as ap " +
            "where ap.customer.id = :customerId " +
            "and ap.startTime > :now ")
    List<Appointment> findFutureAppointmentsByCustomerId(Long customerId, LocalDateTime now);

    Optional<Appointment> findAppointmentByIdAndCustomer_Id(Long id, Long customerId);

    Optional<Appointment> findAppointmentByIdAndService_Business_Owner_Id(Long id, Long ownerId);
}