package com.ruoyi.system.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ruoyi.system.domain.PosStore;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

/**
 * posstoreMapper接口
 *
 * @author ruoyi
 * @date 2023-05-14
 */
public interface PosStoreMapper extends BaseMapper<PosStore>
{
    //按照距离查询推荐商家（dayang=打烊标记：state=1 或当前不在营业时段；打烊门店全局沉底，同组内按距离升序）
    @Select("SELECT *,(st_distance(point(longitude,latitude),point(#{longitude},#{latitude}))*111195/1000 ) as juli,(SELECT del_flag FROM info_user WHERE info_user.user_id = pos_store.user_id) as xxzt,(CASE WHEN pos_store.state = 1 THEN 1 WHEN EXISTS (SELECT 1 FROM operating_hours oh WHERE oh.md_id = pos_store.id AND oh.day_of_week = WEEKDAY(CURDATE()) + 1 AND oh.is_open = 1 AND TIME(oh.start_time) <= CURTIME() AND CURTIME() <= TIME(oh.end_time)) THEN 0 ELSE 1 END) as dayang FROM pos_store WHERE del_flag='0' and off_shelf='0' and area like #{area}  HAVING xxzt=0 ORDER BY dayang ASC, juli ASC limit #{page},20")  //查询语句
    List<PosStore> getmdlist(@Param("longitude")BigDecimal longitude, @Param("latitude")BigDecimal latitude,@Param("page")Integer page,@Param("area")String area,@Param("juli")Integer juli);  //反回结果

    //按照分类距离查询分类商家
    @Select("SELECT *,(st_distance(point(longitude,latitude),point(#{longitude},#{latitude}))*111195/1000 ) as juli,(SELECT del_flag FROM info_user WHERE info_user.user_id = pos_store.user_id) as xxzt,(CASE WHEN pos_store.state = 1 THEN 1 WHEN EXISTS (SELECT 1 FROM operating_hours oh WHERE oh.md_id = pos_store.id AND oh.day_of_week = WEEKDAY(CURDATE()) + 1 AND oh.is_open = 1 AND TIME(oh.start_time) <= CURTIME() AND CURTIME() <= TIME(oh.end_time)) THEN 0 ELSE 1 END) as dayang FROM pos_store WHERE del_flag='0' and off_shelf='0' and area like #{area} and FIND_IN_SET(#{sort},sort) > 0  HAVING xxzt=0 ORDER BY dayang ASC, juli ASC limit #{page},20")  //查询语句
    List<PosStore> getdaidiq(@Param("longitude")BigDecimal longitude, @Param("latitude")BigDecimal latitude,@Param("page")Integer page,@Param("area")String area,@Param("juli")Integer juli,@Param("sort")Integer sort);  //反回结果

    /** 门店列表总数（条件与 getmdlist 一致，只返回条数；total 用于分页元数据，勿用当前页行数充当） */
    @Select("SELECT COUNT(*) FROM pos_store WHERE del_flag='0' and off_shelf='0' and area like #{area} AND (SELECT del_flag FROM info_user WHERE info_user.user_id = pos_store.user_id) = 0")
    long getmdlistCount(@Param("area")String area);

    /** 分类门店列表总数（条件与 getdaidiq 一致） */
    @Select("SELECT COUNT(*) FROM pos_store WHERE del_flag='0' and off_shelf='0' and area like #{area} and FIND_IN_SET(#{sort},sort) > 0 AND (SELECT del_flag FROM info_user WHERE info_user.user_id = pos_store.user_id) = 0")
    long getdaidiqCount(@Param("area")String area,@Param("sort")Integer sort);

    //按照服务类型距离查询分类商家
    @Select("SELECT *,(st_distance(point(longitude,latitude),point(#{longitude},#{latitude}))*111195/1000 ) as juli,(SELECT del_flag FROM info_user WHERE info_user.user_id = pos_store.user_id) as xxzt FROM pos_store WHERE del_flag='0' and off_shelf='0' and area like #{area} and server_type like #{server_type} HAVING xxzt=0 ORDER BY juli ASC limit #{page},20 ")  //查询语句
    List<PosStore> getserverlist(@Param("longitude")BigDecimal longitude, @Param("latitude")BigDecimal latitude,@Param("page")Integer page,@Param("area")String area,@Param("juli")Integer juli,@Param("server_type")String server_type);  //反回结果

//    @Select("SELECT IFNULL(COUNT(*),0) type FROM pos_store")  //查询语句
    int getStoreSum(@Param("startDate") String startDate,@Param("endDate") String endDate);  //反回结果

    //统计用户门店数量
    @Select("SELECT IFNULL(COUNT(*),0) FROM pos_store where del_flag='0' and user_id = #{id}")  //查询语句
    int getshstore( @Param("id") Long id);  //反回结果

    /** 查询普通商家当前未删除门店ID */
    @Select("SELECT id FROM pos_store WHERE user_id = #{userId} AND del_flag = '0' ORDER BY id")
    List<Long> selectOwnedStoreIdsByUserId(@Param("userId") Long userId);

    //按照评分查询门店
    @Select("SELECT *,(st_distance(point(longitude,latitude),point(#{longitude},#{latitude}))*111195/1000 ) as juli,(SELECT del_flag FROM info_user WHERE info_user.user_id = pos_store.user_id) as xxzt,(SELECT IFNULL(AVG(score),0) FROM pos_review WHERE pos_review.md_id = pos_store.id) as pingf,(SELECT IFNULL(COUNT(id),0) FROM pos_order WHERE pos_order.md_id = pos_store.id) as ddsl  FROM pos_store WHERE del_flag='0' and off_shelf='0' HAVING xxzt=0 ORDER BY pingf DESC limit #{page},10")  //查询语句
    List<PosStore> getPingfStore(@Param("longitude")BigDecimal longitude, @Param("latitude")BigDecimal latitude,@Param("page")Integer page,@Param("juli")Integer juli);  //反回结果

    //精选门店
    @Select("<script>SELECT *,(st_distance(point(longitude,latitude),point(#{longitude},#{latitude}))*111195/1000 ) as juli,(SELECT del_flag FROM info_user WHERE info_user.user_id = pos_store.user_id) as xxzt,(SELECT IFNULL(AVG(score),0) FROM pos_review WHERE pos_review.md_id = pos_store.id) as pingf,(SELECT IFNULL(COUNT(id),0) FROM pos_order WHERE pos_order.md_id = pos_store.id) as ddsl  FROM pos_store WHERE del_flag='0' and off_shelf='0' " +
            " <if test='flId != null and flId != 0'>and FIND_IN_SET(#{flId},sort) > 0</if> " +
            " HAVING xxzt=0 ORDER BY pingf DESC limit #{page},10</script>")  //查询语句
    List<PosStore> getjxStore(@Param("longitude")BigDecimal longitude, @Param("latitude")BigDecimal latitude,@Param("page")Integer page,@Param("flId")Long flId);  //反回结果

    /** 精选门店数量（条件与 getjxStore 一致，只返回条数） */
    @Select("<script>SELECT COUNT(*) FROM pos_store WHERE del_flag='0' and off_shelf='0' " +
            " AND (SELECT del_flag FROM info_user WHERE info_user.user_id = pos_store.user_id) = 0 " +
            " <if test='flId != null and flId != 0'> AND FIND_IN_SET(#{flId},sort) &gt; 0</if></script>")
    int getjxStoreCount(@Param("longitude") BigDecimal longitude, @Param("latitude") BigDecimal latitude, @Param("flId") Long flId);



    //按照热度查询门店
    @Select("SELECT *,(st_distance(point(longitude,latitude),point(#{longitude},#{latitude}))*111195/1000 ) as juli,(SELECT del_flag FROM info_user WHERE info_user.user_id = pos_store.user_id) as xxzt,(SELECT IFNULL(AVG(score),0) FROM pos_review WHERE pos_review.md_id = pos_store.id) as pingf,(SELECT IFNULL(COUNT(id),0) FROM pos_order WHERE pos_order.md_id = pos_store.id) as ddsl FROM pos_store WHERE del_flag='0' and off_shelf='0' HAVING xxzt=0 ORDER BY ddsl DESC limit #{page},10")  //查询语句
    List<PosStore> getReduStore(@Param("longitude")BigDecimal longitude, @Param("latitude")BigDecimal latitude,@Param("page")Integer page,@Param("juli")Integer juli);  //反回结果

    //按照新店查询门店
    @Select("SELECT *,(st_distance(point(longitude,latitude),point(#{longitude},#{latitude}))*111195/1000 ) as juli,(SELECT del_flag FROM info_user WHERE info_user.user_id = pos_store.user_id) as xxzt,(SELECT IFNULL(AVG(score),0) FROM pos_review WHERE pos_review.md_id = pos_store.id) as pingf,(SELECT IFNULL(COUNT(id),0) FROM pos_order WHERE pos_order.md_id = pos_store.id) as ddsl FROM pos_store WHERE del_flag='0' and off_shelf='0' HAVING xxzt=0 ORDER BY cretim DESC limit #{page},10")  //查询语句
    List<PosStore> getNewStore(@Param("longitude")BigDecimal longitude, @Param("latitude")BigDecimal latitude,@Param("page")Integer page,@Param("juli")Integer juli);  //反回结果



    /**
     * 查询posstore
     *
     * @param id posstore主键
     * @return posstore
     */
    public PosStore selectPosStoreById(Long id);

    /**
     * 查询posstore列表
     *
     * @param posStore posstore
     * @return posstore集合
     */
    public List<PosStore> selectPosStoreList(PosStore posStore);

    /**
     * 新增posstore
     *
     * @param posStore posstore
     * @return 结果
     */
    public int insertPosStore(PosStore posStore);

    /**
     * 修改posstore
     *
     * @param posStore posstore
     * @return 结果
     */
    public int updatePosStore(PosStore posStore);

    /**
     * 删除posstore
     *
     * @param id posstore主键
     * @return 结果
     */
    public int deletePosStoreById(Long id);

    /**
     * 批量删除posstore
     *
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deletePosStoreByIds(Long[] ids);

    /**
     * 根据商品关键词联表查询店铺（分页）
     * @param page 分页对象
     * @param keyword 关键词
     * @param language 语言
     * @return 分页店铺列表
     */
    public IPage<PosStore> selectStoresByFoodKeyword(IPage<PosStore> page,
                                                     @Param("keyword") String keyword,
                                                     @Param("language") String language);

    /**
     * 夜市摊位列表（含搜索）
     */
    List<PosStore> getNightMarketStalls(@Param("nightMarketId") Long nightMarketId,
                                         @Param("keyword") String keyword,
                                         @Param("offset") Integer offset);

    /**
     * 夜市摊位总数（含搜索）
     */
    int getNightMarketStallsCount(@Param("nightMarketId") Long nightMarketId,
                                   @Param("keyword") String keyword);

    /**
     * 夜市下用户关注的摊位列表
     */
    List<PosStore> getFollowedStalls(@Param("longitude") BigDecimal longitude,
                                      @Param("latitude") BigDecimal latitude,
                                      @Param("nightMarketId") Long nightMarketId,
                                      @Param("userId") Long userId,
                                      @Param("offset") Integer offset);

    /**
     * 夜市下用户常购的摊位列表（按下单次数排序）
     */
    List<PosStore> getFrequentStalls(@Param("longitude") BigDecimal longitude,
                                      @Param("latitude") BigDecimal latitude,
                                      @Param("nightMarketId") Long nightMarketId,
                                      @Param("userId") Long userId,
                                      @Param("offset") Integer offset);
}
