raex_back/src/main/java/com/izouma/nineth/repo/CollectionRepo.java

package com.izouma.nineth.repo;

import com.fasterxml.jackson.annotation.JsonView;
import com.izouma.nineth.domain.Collection;
import com.izouma.nineth.dto.CollectionInfoDTO;
import com.izouma.nineth.dto.CollectionStockAndSale;
import com.izouma.nineth.dto.RecommendCollection;
import com.izouma.nineth.enums.CollectionSource;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.CachePut;
import org.springframework.cache.annotation.Cacheable;
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.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.annotation.Nonnull;
import javax.transaction.Transactional;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;
import java.util.Set;

public interface CollectionRepo extends JpaRepository<Collection, Long>, JpaSpecificationExecutor<Collection> {
    @Query("update Collection t set t.del = true where t.id = ?1")
    @Modifying
    @Transactional
    @CacheEvict(value = {"collection", "recommend"}, allEntries = true)
    void softDelete(Long id);

    @Transactional
    @Modifying
    @Query(value = "update collection_info c set c.on_shelf = ?2, c.salable = ?3, c.start_time = ?4, " +
            "c.schedule_sale = ?5, c.sort = ?6, c.detail = ?7, c.privileges = ?8, " +
            "c.properties = ?9, c.model3d = ?10, c.max_count = ?11, c.count_id = ?12, c.scan_code = ?13, " +
            "c.no_sold_out = ?14, c.assignment = ?15, c.coupon_payment = ?16, c.share_bg = ?17," +
            "c.register_bg = ?18, c.vip_quota = ?19, c.time_delay = ?20, c.sale_time = ?21, c.hold_days = ?22, " +
            "c.open_quota = ?23, c.total_quota = ?24, c.minimum_charge = ?25 " +
            "where c.id = ?1", nativeQuery = true)
    @CacheEvict(value = {"collection", "recommend"}, allEntries = true)
    void update(@Nonnull Long id, boolean onShelf, boolean salable, LocalDateTime startTime,
                boolean schedule, int sort, String detail, String privileges,
                String properties, String model3d, int maxCount, String countId, boolean scanCode,
                boolean noSoldOut, int assignment, boolean couponPayment, String shareBg, String registerBg,
                Integer vipQuota, Boolean timeDelay, LocalDateTime saleTime, Integer holdDays, Boolean openQuota,
                Integer totalQuota, BigDecimal minimumCharge);

    @Cacheable("collection")
    @JsonView(Collection.View.Detail.class)
    Optional<Collection> findById(@Nonnull Long id);

    @Query("select c from Collection c where c.del = false and c.id = ?1")
    Optional<Collection> findDetailById(@Nonnull Long id);

    @Cacheable("collectionInfo")
    @JsonView(Collection.View.Detail.class)
    @Query("select new com.izouma.nineth.dto.CollectionInfoDTO(c,cp) from Collection c left join CollectionPrivilege cp " +
            "on c.id = cp.collectionId where c.id = ?1 ")
    Optional<CollectionInfoDTO> findInfoById(@Nonnull Long id);

    Optional<Collection> findByIdAndDelFalse(Long id);

    @Query("update Collection t set t.likes = t.likes + ?2 where t.id = ?1")
    @Modifying
    @Transactional
    @CacheEvict(value = "collection", key = "#id")
    void addLike(Long id, int num);

    @Query(value = "select distinct c from Collection c join Like l on l.collectionId = c.id " +
            "where l.userId = ?1 and l.del = false and c.del = false and c.companyId = ?2")
    List<Collection> userLikes(Long userId, Long companyId);

    List<Collection> findByScheduleSaleTrueAndOnShelfFalseAndStartTimeBeforeAndDelFalse(LocalDateTime time);

    @Nonnull
    @CachePut(value = "collection", key = "#collection.id")
    Collection save(@Nonnull Collection collection);

    @Query("select new com.izouma.nineth.dto.RecommendCollection(c,r) from Collection c join Recommend r on c.id = r.collectionId " +
            "where c.del = false and c.onShelf = true and r.companyId = ?2 and r.type = ?1 and r.category = 'COLLECTION' order by r.sort desc")
    List<RecommendCollection> recommend(String type, Long companyId);

    @Transactional
    @Modifying
    @Query("update Collection c set c.currentNumber = COALESCE(c.currentNumber, 0) + ?2 where c.id = ?1")
    @CacheEvict(value = "collection", key = "#id")
    void increaseNumber(Long id, int d);

    @Transactional
    @Modifying
    @Query("update Collection c set c.currentNumber = ?2 where c.id = ?1")
    @CacheEvict(value = "collection", key = "#id")
    void setNumber(Long id, int num);

    @Transactional
    @Modifying
    @Query("update Collection c set c.total = COALESCE(c.total, 0) + ?2 where c.id = ?1")
    @CacheEvict(value = "collection", key = "#id")
    void increaseTotal(Long id, int d);

    @Transactional
    @Modifying
    @Query("update Collection c set c.onShelf = ?2 where c.id = ?1")
    @CacheEvict(value = "collection", key = "#id")
    void setOnShelf(Long id, boolean onShelf);

    @Transactional
    @Modifying
    @Query("update Collection c set c.inPaying = ?2 where c.id = ?1")
    @CacheEvict(value = "collection", key = "#id")
    void setInPaying(Long id, boolean inPaying);

    @Transactional
    @Modifying
    @Query("update Collection c set c.scheduleSale = false, c.startTime = null, c.onShelf = ?2, c.salable = true where c.id = ?1")
    @CacheEvict(value = "collection", key = "#id")
    void scheduleOnShelf(Long id, boolean onShelf);

    @Query("select c.currentNumber from Collection c where c.id = ?1")
    Optional<Integer> getCurrentNumber(Long id);

    List<Collection> findByScheduleSaleTrue();

    List<Collection> findByNameLike(String name);

    List<Collection> findByStockGreaterThan(int stock);

    @Query("update Collection c set c.stock = ?2 where c.id = ?1")
    @Transactional
    @Modifying
    int updateStock(Long id, int stock);

    @Query("update Collection c set c.sale = ?2 where c.id = ?1")
    @Transactional
    @Modifying
    int updateSale(Long id, int sale);

    @Query("select c.stock from Collection c where c.id = ?1")
    Integer getStock(Long id);

    @Query("select c.sale from Collection c where c.id = ?1")
    Integer getSale(Long id);

    @Query("select new com.izouma.nineth.dto.CollectionStockAndSale(c.id, c.stock, c.sale) from Collection c where c.stock > 0")
    List<CollectionStockAndSale> getStockAndSale();

    List<Collection> findAllByIdIn(java.util.Collection<Long> ids);

    @Query(value = "select c.id, c.pic, c.model3d, c.minter_avatar, c.owner_avatar, c.detail from collection_info c", nativeQuery = true)
    List<List<String>> selectResource();

    @Query(value = "select c.id, c.pic, c.model3d, c.minter_avatar, c.owner_avatar, c.detail from collection_info c where c.id = ?1", nativeQuery = true)
    List<List<String>> selectResource(Long id);

    @Modifying
    @Transactional
    @Query(value = "update collection_info c set c.pic = ?2, c.model3d = ?3, c.minter_avatar = ?4, " +
            "c.owner_avatar = ?5, c.detail = ?6 where c.id = ?1", nativeQuery = true)
    int updateCDN(Long id, String pic, String model3d, String minterAvatar,
                  String ownerAvatar, String detail);

    @Query("update Collection c set c.vipQuota = ?2 where c.id = ?1")
    @Transactional
    @Modifying
    int updateVipQuota(Long id, int vipQuota);

    @Query("select c.vipQuota from Collection c where c.id = ?1")
    Integer getVipQuota(Long id);

    @Query(value = "select c.asset_id from collection_info c where  c.source =  'TRANSFER' and c.created_at <= ?1 and c.salable = true and c.on_shelf = true limit 3000", nativeQuery = true)
    Set<Long> findResaleCollectionOverTime(LocalDateTime startTime);

    List<Collection> findAllByNameLike(String name);

    @Query(value = "select avg(t.price) from (select c.price from collection_info c where c.name like ?1 " +
            "and c.source = 'TRANSFER' " +
            "and c.salable = true " +
            "and c.stock > 0 order by c.price limit 5) t", nativeQuery = true)
    String lowestPrice(String search);

    @Query(value = "SELECT c.original_price FROM collection_info c WHERE c.NAME LIKE ?1 AND c.original_price != 'null' ORDER BY c.original_price LIMIT 1", nativeQuery = true)
    String lowestOriginPrice(String search);

    @Query(value = "select min(t.price) from (select c.price from collection_info c where c.name like ?1 " +
            "and c.source = 'TRANSFER' " +
            "and c.salable = true " +
            "and c.stock > 0 order by c.price limit 5) t", nativeQuery = true)
    String lowestPrices(String search);

    @Query("select c from Collection c join c.tags t on t.id = ?1 ")
    Page<Collection> byTag(Long tagId, Pageable pageable);

    @Query("select count(id) from Collection where source = 'TRANSFER' and name like ?1")
    int countAllByNameLike(String name);

    Collection findFirstByOnShelfAndAssetId(boolean onShelf, Long assetId);

    List<Collection> findAllByOasisIdInAndSourceInAndStockGreaterThan(List<Long> oasisIds, List<CollectionSource> sources, int sale);

    @Query("select min(price) from Collection where source = 'TRANSFER' and salable = true and onShelf = true and del = false and prefixName like ?1")
    BigDecimal findMinPriceByPrefixName(String prefixName);

    @Query("select min(price) from Collection where source = 'TRANSFER' and salable = true and onShelf = true and del = false and prefixName like ?1 and name like ?2 and name not like ?3")
    BigDecimal findMinPriceByNameAndPrefixName(String prefixName, String nameLike, String nameNotLike);
}