content: package com.nonononoki.alovoa.repo;
  
  import com.nonononoki.alovoa.entity.User;
  import com.nonononoki.alovoa.entity.user.Gender;
  import com.nonononoki.alovoa.model.UserSearchRequest;
  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 java.util.Collection;
  import java.util.Date;
  import java.util.List;
  import java.util.UUID;
  
  public interface UserRepository extends JpaRepository<User, Long> {
  
      String SEARCH_SELECT_QUERY = "SELECT u FROM User u ";
      String SEARCH_JOIN_MISC_QUERY = "LEFT JOIN u.miscInfos misc ";
      String SEARCH_JOIN_INTEREST_QUERY = "LEFT JOIN u.interests interest ";
      String SEARCH_BASE_QUERY = "WHERE u.disabled = FALSE AND u.admin = FALSE AND u.confirmed = TRUE AND u.intention IS NOT NULL AND "
              + "u.locationLatitude IS NOT NULL AND u.locationLongitude IS NOT NULL "
              + "AND u.profilePicture IS NOT NULL AND :preferedGender MEMBER OF u.preferedGenders "
              + "AND TIMESTAMPDIFF(YEAR, u.dates.dateOfBirth, CURDATE()) + u.preferedMaxAge >= :age AND TIMESTAMPDIFF(YEAR, u.dates.dateOfBirth, CURDATE()) + u.preferedMinAge <= :age AND u.dates.dateOfBirth >= :minDate AND u.dates.dateOfBirth <= :maxDate "
              + "AND u.id NOT IN (:likeIds) AND u.id NOT IN (:likeIds) AND u.id NOT IN (:hideIds) "
              + "AND u.id NOT IN (:blockIds) "
              + "AND u.id NOT IN (:blockedByIds) "
              + "AND u.gender.id IN (:genderIds) ";
      String SEARCH_INTENTION_QUERY = "AND u.intention.id IN (:intentionIds) ";
      String SEARCH_MISC_INFO_QUERY = "AND misc.value IN (:miscInfoIds) ";
      String SEARCH_INTEREST_QUERY = "AND interest.text IN (:interestTexts)  ";
      String SEARCH_LOCATION_QUERY = "AND u.locationLatitude BETWEEN :latitudeFrom AND :latitudeTo AND u.locationLongitude BETWEEN :longitudeFrom AND :longitudeTo ";
  
      @Query(value = SEARCH_SELECT_QUERY + SEARCH_BASE_QUERY + SEARCH_LOCATION_QUERY)
      List<User> usersSearchBaseQuery(@Param("age") int age, @Param("minDate") Date minDate, @Param("maxDate") Date maxDate,
                                      @Param("preferedGender") Gender preferedGender,
                                      @Param("latitudeFrom") Double latitudeFrom, @Param("latitudeTo") Double latitudeTo,
                                      @Param("longitudeFrom") Double longitudeFrom, @Param("longitudeTo") Double longitudeTo,
                                      @Param("likeIds") Collection<Long> likeIds,
                                      @Param("hideIds") Collection<Long> hideIds, @Param("blockIds") Collection<Long> blockIds,
                                      @Param("blockedByIds") Collection<Long> blockedByIds, @Param("genderIds") Collection<Long> genderIds,
                                      Pageable page);
  
      @Query(value = SEARCH_SELECT_QUERY + SEARCH_BASE_QUERY + SEARCH_LOCATION_QUERY + SEARCH_INTENTION_QUERY)
      List<User> usersSearchNoExtrasQuery(@Param("age") int age,
                                          @Param("minDate") Date minDate,
                                          @Param("maxDate") Date maxDate,
                                          @Param("preferedGender") Gender preferedGender,
                                          @Param("latitudeFrom") Double latitudeFrom,
                                          @Param("latitudeTo") Double latitudeTo,
                                          @Param("longitudeFrom") Double longitudeFrom,
                                          @Param("longitudeTo") Double longitudeTo,
                                          @Param("likeIds") Collection<Long> likeIds,
                                          @Param("hideIds") Collection<Long> hideIds,
                                          @Param("blockIds") Collection<Long> blockIds,
                                          @Param("blockedByIds") Collection<Long> blockedByIds,
                                          @Param("genderIds") Collection<Long> genderIds,
                                          @Param("intentionIds") Collection<Long> intentionIds,
                                          Pageable page);
  
      @Query(value = SEARCH_SELECT_QUERY + SEARCH_JOIN_MISC_QUERY + SEARCH_JOIN_INTEREST_QUERY + SEARCH_BASE_QUERY + SEARCH_LOCATION_QUERY + SEARCH_INTENTION_QUERY + SEARCH_MISC_INFO_QUERY + SEARCH_INTEREST_QUERY)
      List<User> usersSearchInterestMiscQuery(@Param("age") int age,
                                              @Param("minDate") Date minDate,
                                              @Param("maxDate") Date maxDate,
                                              @Param("preferedGender") Gender preferedGender,
                                              @Param("latitudeFrom") Double latitudeFrom,
                                              @Param("latitudeTo") Double latitudeTo,
                                              @Param("longitudeFrom") Double longitudeFrom,
                                              @Param("longitudeTo") Double longitudeTo,
                                              @Param("likeIds") Collection<Long> likeIds,
                                              @Param("hideIds") Collection<Long> hideIds,
                                              @Param("blockIds") Collection<Long> blockIds,
                                              @Param("blockedByIds") Collection<Long> blockedByIds,
                                              @Param("genderIds") Collection<Long> genderIds,
                                              @Param("intentionIds") Collection<Long> intentionIds,
                                              @Param("miscInfoIds") Collection<Integer> miscInfos,
                                              @Param("interestTexts") Collection<String> interests,
                                              Pageable page);
  
      @Query(value = SEARCH_SELECT_QUERY + SEARCH_JOIN_INTEREST_QUERY + SEARCH_BASE_QUERY + SEARCH_LOCATION_QUERY + SEARCH_INTENTION_QUERY + SEARCH_INTEREST_QUERY)
      List<User> usersSearchInterestQuery(@Param("age") int age,
                                          @Param("minDate") Date minDate,
                                          @Param("maxDate") Date maxDate,
                                          @Param("preferedGender") Gender preferedGender,
                                          @Param("latitudeFrom") Double latitudeFrom,
                                          @Param("latitudeTo") Double latitudeTo,
                                          @Param("longitudeFrom") Double longitudeFrom,
                                          @Param("longitudeTo") Double longitudeTo,
                                          @Param("likeIds") Collection<Long> likeIds,
                                          @Param("hideIds") Collection<Long> hideIds,
                                          @Param("blockIds") Collection<Long> blockIds,
                                          @Param("blockedByIds") Collection<Long> blockedByIds,
                                          @Param("genderIds") Collection<Long> genderIds,
                                          @Param("intentionIds") Collection<Long> intentionIds,
                                          @Param("interestTexts") Collection<String> interests,
                                          Pageable page);
  
      @Query(value = SEARCH_SELECT_QUERY + SEARCH_JOIN_MISC_QUERY + SEARCH_BASE_QUERY + SEARCH_LOCATION_QUERY + SEARCH_INTENTION_QUERY + SEARCH_MISC_INFO_QUERY)
      List<User> usersSearchMiscQuery(@Param("age") int age,
                                      @Param("minDate") Date minDate,
                                      @Param("maxDate") Date maxDate,
                                      @Param("preferedGender") Gender preferedGender,
                                      @Param("latitudeFrom") Double latitudeFrom,
                                      @Param("latitudeTo") Double latitudeTo,
                                      @Param("longitudeFrom") Double longitudeFrom,
                                      @Param("longitudeTo") Double longitudeTo,
                                      @Param("likeIds") Collection<Long> likeIds,
                                      @Param("hideIds") Collection<Long> hideIds,
                                      @Param("blockIds") Collection<Long> blockIds,
                                      @Param("blockedByIds") Collection<Long> blockedByIds,
                                      @Param("genderIds") Collection<Long> genderIds,
                                      @Param("intentionIds") Collection<Long> intentionIds,
                                      @Param("miscInfoIds") Collection<Integer> miscInfos,
                                      Pageable page);
  
      @Query(value = SEARCH_SELECT_QUERY + SEARCH_BASE_QUERY)
      List<User> usersSearchIgnoreLocation(@Param("age") int age, @Param("minDate") Date minDate, @Param("maxDate") Date maxDate,
                                           @Param("preferedGender") Gender preferedGender, @Param("likeIds") Collection<Long> likeIds,
                                           @Param("hideIds") Collection<Long> hideIds,
                                           @Param("blockIds") Collection<Long> blockIds,
                                           @Param("blockedByIds") Collection<Long> blockedByIds,
                                           @Param("genderIds") Collection<Long> genderIds, Pageable page);
  
      User findByEmail(String email);
  
      User findByUuid(UUID uuid);
  
      List<User> findByUuidIn(Collection<UUID> uuidList);
  
      long countByConfirmed(boolean confirmed);
  
      long countByConfirmedAndGenderId(boolean confirmed, long genderId);
  
      default List<User> usersSearchNoExtras(UserSearchRequest request, Pageable page) {
          return usersSearchNoExtrasQuery(request.getAge(), request.getMinDateDob(), request.getMaxDateDob(), request.getPreferedGender(), request.getMinLat(),
                  request.getMaxLat(), request.getMinLong(), request.getMaxLong(),
                  request.getLikeIds(), request.getHideIds(), request.getBlockIds(), request.getBlockedByIds(), request.getGenderIds(),
                  request.getIntentionIds(),
                  page);
      }
  
      default List<User> usersSearchInterestMisc(UserSearchRequest request, Pageable page) {
          return usersSearchInterestMiscQuery(request.getAge(), request.getMinDateDob(), request.getMaxDateDob(), request.getPreferedGender(),
                  request.getMinLat(), request.getMaxLat(), request.getMinLong(), request.getMaxLong(),
                  request.getLikeIds(), request.getHideIds(), request.getBlockIds(), request.getBlockedByIds(), request.getGenderIds(),
                  request.getIntentionIds(),
                  request.getMiscInfos(),
                  request.getInterests(),
                  page);
      }
  
      default List<User> usersSearchInterest(UserSearchRequest request, Pageable page) {
          return usersSearchInterestQuery(request.getAge(), request.getMinDateDob(), request.getMaxDateDob(), request.getPreferedGender(),
                  request.getMinLat(), request.getMaxLat(), request.getMinLong(), request.getMaxLong(),
                  request.getLikeIds(), request.getHideIds(), request.getBlockIds(), request.getBlockedByIds(), request.getGenderIds(),
                  request.getIntentionIds(),
                  request.getInterests(),
                  page);
      }
  
      default List<User> usersSearchMisc(UserSearchRequest request, Pageable page) {
          return usersSearchMiscQuery(request.getAge(), request.getMinDateDob(), request.getMaxDateDob(), request.getPreferedGender(),
                  request.getMinLat(), request.getMaxLat(), request.getMinLong(), request.getMaxLong(),
                  request.getLikeIds(), request.getHideIds(), request.getBlockIds(), request.getBlockedByIds(), request.getGenderIds(),
                  request.getIntentionIds(),
                  request.getMiscInfos(),
                  page);
      }
  
      default List<User> usersBaseSearch(UserSearchRequest request, Pageable page) {
          return usersSearchBaseQuery(request.getAge(), request.getMinDateDob(), request.getMaxDateDob(), request.getPreferedGender(),
                  request.getMinLat(), request.getMaxLat(), request.getMinLong(), request.getMaxLong(),
                  request.getLikeIds(), request.getHideIds(), request.getBlockIds(), request.getBlockedByIds(), request.getGenderIds(),
                  page);
      }
  
      List<User> findByConfirmedIsFalseAndAdminFalseAndDatesCreationDateBefore(Date date);
  
      default List<User> usersSearchAllIgnoreLocation(UserSearchRequest request, Pageable page) {
          return usersSearchIgnoreLocation(
                  request.getAge(), request.getMinDateDob(), request.getMaxDateDob(), request.getPreferedGender(),
                  request.getLikeIds(), request.getHideIds(), request.getBlockIds(),
                  request.getBlockedByIds(), request.getGenderIds(), page);
      }
  
      default List<User> usersDonate(Date minDate, Date maxDate) {
          return findTop20ByDisabledFalseAndAdminFalseAndConfirmedTrueAndIntentionNotNullAndLocationLatitudeNotNullAndProfilePictureNotNullAndDatesDateOfBirthGreaterThanEqualAndDatesDateOfBirthLessThanEqualOrderByTotalDonationsDesc(
                  minDate, maxDate);
      }
  
      default List<User> adminSearch() {
          return findTop100ByDisabledFalseAndAdminFalseAndConfirmedTrueAndIntentionNotNullAndLocationLatitudeNotNullAndProfilePictureNotNullOrderByDatesCreationDateDesc();
      }
  
      // used for sending mails to all
      List<User> findByDisabledFalseAndAdminFalseAndConfirmedTrue();
  
      List<User> findTop100ByDisabledFalseAndAdminFalseAndConfirmedTrueAndIntentionNotNullAndLocationLatitudeNotNullAndProfilePictureNotNullOrderByDatesCreationDateDesc();
  
      // users donate
      List<User> findTop20ByDisabledFalseAndAdminFalseAndConfirmedTrueAndIntentionNotNullAndLocationLatitudeNotNullAndProfilePictureNotNullAndDatesDateOfBirthGreaterThanEqualAndDatesDateOfBirthLessThanEqualOrderByTotalDonationsDesc(
              Date minDate, Date maxDate);
  }
  