package com.liquidnet.service.sweet.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.liquidnet.service.sweet.entity.SweetAnswer;
import com.liquidnet.service.sweet.vo.SweetUserTempVo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 答题表 Mapper 接口
 * </p>
 *
 * @author jiangxiulong
 * @since 2021-08-12
 */
public interface SweetAnswerMapper extends BaseMapper<SweetAnswer> {

    // 鸠占鹊巢 写sql 选中你了
    // 海皮威尔门票
    @Select({"select user_id as 'uid',user_mobile as 'mobile' from kylin_order_tickets as kot " +
            "    inner join kylin_order_ticket_status as kots on kot.order_tickets_id = kots.order_id " +
            "    inner join kylin_order_ticket_relations as kotr on kot.order_tickets_id = kotr.order_id " +
            " where (status = 1 or status = 4) and performance_id in (select performances_id from kylin_performances where title like '%海皮威尔%')" +
            " group by user_id;"
    })
    List<SweetUserTempVo> selectHPWR();

    // 和平饭店门票
    @Select({"select user_id as 'uid',user_mobile as 'mobile' from kylin_order_tickets as kot " +
            "    inner join kylin_order_ticket_status as kots on kot.order_tickets_id = kots.order_id " +
            "    inner join kylin_order_ticket_relations as kotr on kot.order_tickets_id = kotr.order_id " +
            " where (status = 1 or status = 4) and performance_id in (select performances_id from kylin_performances where title like '%和平饭店%')" +
            " group by user_id;"
    })
    List<SweetUserTempVo> selectHPFD();

    // 虎啸春门票
    @Select({"select user_id as 'uid',user_mobile as 'mobile' from kylin_order_tickets as kot " +
            "    inner join kylin_order_ticket_status as kots on kot.order_tickets_id = kots.order_id " +
            "    inner join kylin_order_ticket_relations as kotr on kot.order_tickets_id = kotr.order_id " +
            " where (status = 1 or status = 4) and performance_id in (select performances_id from kylin_performances where title like '%虎啸春%')" +
            " group by user_id;"
    })
    List<SweetUserTempVo> selectHXC();

    // 会员
    @Select({"select au.uid as 'uid',au.mobile as 'mobile' from adam_user_member as aum " +
            " inner join adam_user au on aum.uid = au.uid  " +
            " where aum.state = 1 and aum.expiry_at >= '2021-08-20';"
    })
    List<SweetUserTempVo> selectMember();

    // 消费不小于1
    @Select({"select count(0) as 'buyCount',user_id as 'uid',user_mobile as 'mobile' from kylin_order_tickets as kot inner join kylin_order_ticket_status as kots on kot.order_tickets_id = kots.order_id " +
            " where (status = 1 or status = 4) " +
            " group by user_id " +
            " having buyCount >= 1;"
    })
    List<SweetUserTempVo> selectOrder1();

    // 消费不小于2
    @Select({"select count(0) as 'buyCount',user_id as 'uid',user_mobile as 'mobile' from kylin_order_tickets as kot inner join kylin_order_ticket_status as kots on kot.order_tickets_id = kots.order_id " +
            " where (status = 1 or status = 4) " +
            " group by user_id " +
            " having buyCount >= 2;"
    })
    List<SweetUserTempVo> selectOrder2();

    // 消费不小于3
    @Select({"select count(0) as 'buyCount',user_id as 'uid',user_mobile as 'mobile' from kylin_order_tickets as kot inner join kylin_order_ticket_status as kots on kot.order_tickets_id = kots.order_id " +
            " where (status = 1 or status = 4) " +
            " group by user_id " +
            " having buyCount >= 3;"
    })
    List<SweetUserTempVo> selectOrder3();

    // 占完了
}
