yhlxj/netflix-dao/src/main/java/com/cyksj/mapper/GroupsMapper.java

package com.cyksj.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.cyksj.model.entity.AccountDoubleVerifyRecord;
import com.cyksj.model.entity.GroupsTrips;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * @author chan
 * @date 2022/9/27 4:24 PM
 */
public interface GroupsMapper extends BaseMapper<GroupsTrips> {
    @Update("update groups_trips set available_num = available_num - 1 where id = #{groupId} and available_num > 0")
    int decrAvailableNum(Long groupId);

    @Update("update groups_trips set available_num = available_num + 1 where id = #{groupId} and available_num < num")
    int incrAvailableNum(Long groupId);

    @Select("select count(*) from groups_trips where sku_id = #{skuId} and num = available_num")
	Integer selectEmptyNetfilxTrips(Long skuId);

	@Select("select gt.* from (select a.account,t.account_id,t.id as groups_id,t.sku_id,t.num from account a inner join groups_trips t on a.id = t.account_id where t.sku_id in (select id from goods_don_sku where goods_id = #{goodsId} and status is true) and t.num > 1 and a.api_secret = '') gt" +
			" where gt.num = (select count(*) from groups_relation gr where gr.groups_id = gt.groups_id and gr.`status` = 'validity')" +
			" and not EXISTS (select account_id from account_double_verify_record where account_id = gt.account_id and deleted is true)" +
			" and DATE_SUB(now(),INTERVAL 3 day) >= (select max(update_time) from groups_relation gr where gr.groups_id = gt.groups_id and gr.`status` = 'validity')")
	List<AccountDoubleVerifyRecord> selectGPTDoubleVerifyTrips(Long goodsId);
}