package shop.alien.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import shop.alien.entity.store.LifeFans;
import shop.alien.entity.store.vo.LifeFansVo;

import java.util.List;

@Mapper
public interface LifeFansMapper extends BaseMapper<LifeFans> {

    @Select("select MAX(foll.id) id, MAX(foll.name) name, MAX(foll.image) image, foll.phoneId, MAX(foll.blurb) blurb, MAX(foll.blockedType) blockedType, MAX(foll.blockedId) blockedId, MAX(foll.username) username, MAX(foll.accountBlurb) accountBlurb, MAX(foll.isMerchant) isMerchant, " +
            "  MAX(lb.id) blackListid, MAX(if(isnull(fans.id), 0, 1)) isFollowMe, 1 as isFollowThis,  " +
            "  (select count(1) from life_fans where fans_id= foll.phoneId and delete_flag =0) followNum, " +
            "  (select count(1) from life_fans where followed_id= foll.phoneId and delete_flag =0) fansNum from ( " +
            "    with follow as (   " +
            "        select substring_index(followed_id, '_', 1) as flag, substring_index(followed_id, '_', -1) as phone   " +
            "        from life_fans   " +
            "        where delete_flag = 0 and fans_id = #{fansId} " +
            "    )   " +
            "    select info.id, IF(info.store_application_status = 0, user.nick_name, info.store_name) AS NAME," +
            "    user.head_img image, concat('store_', user.phone) phoneId, IF(info.store_application_status = 0, user.account_blurb, info.store_blurb) AS blurb, 1 blockedType,user.id blockedId ,IFNULL(user.nick_name, user.name) username, user.account_blurb accountBlurb, IF(info.store_application_status = 1, '1', '0') AS isMerchant " +
            "    from follow foll " +
            "    join store_user user on foll.phone = user.phone " +
            "    join store_info info on info.id = user.store_id " +
            "    left join store_img img on img.store_id = user.store_id and img.img_type = '10' and img.delete_flag = 0 " +
            "    where foll.flag = 'store' and user.delete_flag = 0 and info.delete_flag = 0 " +
            "    union " +
            "    select user.id, user.user_name name, user.user_image image, concat('user_', user.user_phone) phoneId, user.jianjie blurb, 2 blockedType,user.id blockedId,'' username, '' accountBlurb, '0' AS isMerchant " +
            "    from follow foll " +
            "    join life_user user on foll.phone = user.user_phone   " +
            "    where foll.flag = 'user' and user.delete_flag = 0   " +
            ") foll   " +
            "left join life_fans fans on fans.fans_id = foll.phoneId and fans.followed_id = #{fansId} and fans.delete_flag = 0 " +
            "left join life_blacklist lb on lb.blocked_type = foll.blockedType and lb.blocked_id = foll.blockedId and lb.blocker_type = #{blockerType} and lb.blocker_id = #{blockerId} and lb.delete_flag = 0 " +
            "${ew.customSqlSegment} ")
    IPage<LifeFansVo> getMyFollowed(IPage<LifeFansVo> iPage, @Param("fansId") String fansId, @Param("blockerType") String blockerType, @Param("blockerId") String blockerId, @Param(Constants.WRAPPER) QueryWrapper<LifeFansVo> wrapper);


    @Select("select foll.*, if(isnull(fans.id), 0, 1) isFollowMe, 1 as isFollowThis from ( " +
            "    with follow as (   " +
            "        select substring_index(followed_id, '_', 1) as flag, substring_index(followed_id, '_', -1) as phone   " +
            "        from life_fans   " +
            "        where delete_flag = 0 and fans_id = #{fansId} " +
            "    )   " +
            "    select info.id, info.store_name name, img.img_url image, concat('store_', user.phone) phoneId " +
            "    from follow foll " +
            "    join store_user user on foll.phone = user.phone " +
            "    join store_info info on info.id = user.store_id " +
            "    left join store_img img on img.store_id = user.store_id and img.img_type = '10' and img.delete_flag = 0 " +
            "    where foll.flag = 'store' and user.delete_flag = 0 and info.delete_flag = 0 " +
            "    union " +
            "    select user.id, user.user_name name, user.user_image image, concat('user_', user.user_phone) phoneId " +
            "    from follow foll " +
            "    join life_user user on foll.phone = user.user_phone   " +
            "    where foll.flag = 'user' and user.delete_flag = 0   " +
            ") foll " +
            "left join life_fans fans on fans.fans_id = foll.phoneId and fans.followed_id = #{fansId} and fans.delete_flag = 0 ")
    List<LifeFansVo> getMyFollowedAll(@Param("fansId") String fansId);

    @Select("select MAX(foll.id) id, MAX(foll.name) name, MAX(foll.image) image, foll.phoneId, MAX(foll.blurb) blurb, MAX(foll.blockedType) blockedType, MAX(foll.blockedId) blockedId, MAX(foll.isMerchant) isMerchant, " +
            "  MAX(lb.id) blackListid, MAX(if(isnull(fans.id), 0, 1)) isFollowThis, 1 as isFollowMe, " +
            "    (select count(1) from life_fans fans2 where fans2.followed_id = foll.phoneId and fans2.delete_flag = 0) fansNum, " +
            "    (select count(1) from life_fans fans3 where fans3.fans_id = foll.phoneId and fans3.delete_flag = 0) followNum " +
            "from ( " +
            "    with follow as ( " +
            "        select substring_index(fans_id, '_', 1) as flag, substring_index(fans_id, '_', -1) as phone " +
            "        from life_fans " +
            "        where delete_flag = 0 and followed_id = #{fansId} " +
            "    ) " +
            "    select user.id, IF(info.store_application_status = 0, user.nick_name, info.store_name) AS name, user.head_img image, concat('store_', user.phone) phoneId, IF(info.store_application_status = 0, user.account_blurb, info.store_blurb) AS blurb, 1 blockedType,user.id blockedId, IF(info.store_application_status = 1, '1', '0') AS isMerchant" +
            "    from follow foll " +
            "    join store_user user on foll.phone = user.phone " +
            "    join store_info info on info.id = user.store_id " +
            "    left join store_img img on img.store_id = user.store_id and img.img_type = '10' and img.delete_flag = 0 " +
            "    where foll.flag = 'store' and user.delete_flag = 0 and info.delete_flag = 0" +
            "    union " +
            "    select user.id, user.user_name name, user.user_image image, concat('user_', user.user_phone) phoneId, user.jianjie blurb, 2 blockedType,user.id blockedId, '0' AS isMerchant" +
            "    from follow foll " +
            "    join life_user user on foll.phone = user.user_phone " +
            "    where foll.flag = 'user' and user.delete_flag = 0 " +
            ") foll " +
            "left join life_fans fans on fans.followed_id = foll.phoneId and fans.fans_id = #{fansId} and fans.delete_flag = 0 " +
            "left join life_blacklist lb on lb.blocked_type = foll.blockedType and lb.blocked_id = foll.blockedId and lb.blocker_type = #{blockerType} and lb.blocker_id = #{blockerId} and lb.delete_flag = 0 " +
            "${ew.customSqlSegment} ")
    IPage<LifeFansVo> getMyFans(IPage<LifeFansVo> iPage, @Param("fansId") String fansId, @Param("blockerType") String blockerType, @Param("blockerId") String blockerId, @Param(Constants.WRAPPER) QueryWrapper<LifeFansVo> wrapper);

//    @Select("select foll.*, if(isnull(fans.id), 0, 1) isFollowThis, 1 as isFollowMe, count(fans2.id) fansNum, count(fans3.id) followNum from ( " +
//            "    with follow as ( " +
//            "    select substring_index(fans_id, '_', 1) as flag, substring_index(fans_id, '_', -1) as phone " +
//            "    from life_fans " +
//            "    where delete_flag = 0 and followed_id = #{fansId} " +
//            "    ) " +
//            "    select info.id," +
//            "    \"CASE \" +\n" +
//            "        \"WHEN user.store_id IS NULL OR info.store_application_status = 0 THEN user.nick_name \" +\n" +
//            "        \"ELSE info.store_name \" +\n" +
//            "        \"END AS name, \" +\n" +
//            "        \"CASE \" +\n" +
//            "        \"WHEN user.store_id IS NULL OR info.store_application_status IN (0, 2) THEN user.account_blurb \" +\n" +
//            "        \"ELSE info.store_blurb \" +\n" +
//            "        \"END AS store_blurb, \" + " +
//            "    user.head_img image, concat('store_', user.phone) phoneId" +
//            "    from follow foll " +
//            "    join store_user user on foll.phone = user.phone " +
//            "    LEFT JOIN store_info info ON user.store_id IS NOT NULL AND info.id = user.store_id " +
//            "    and info.delete_flag = 0" +
//            "    left join store_img img on img.store_id = user.store_id and img.img_type = '10' and img.delete_flag = 0 " +
//            "    where foll.flag = 'store' and user.delete_flag = 0 " +
//            ") foll " +
//            "left join life_fans fans on fans.followed_id = foll.phoneId and fans.fans_id = #{fansId} and fans.delete_flag = 0 " +
//            "left join life_fans fans2 on fans2.followed_id = foll.phoneId and fans2.delete_flag = 0 " +
//            "left join life_fans fans3 on fans3.fans_id = foll.phoneId and fans3.delete_flag = 0 " +
//            "${ew.customSqlSegment} ")
    @Select("SELECT " +
            "MAX(foll.id) id, " +
            "MAX(foll.name) name, " +
            "MAX(foll.blurb) blurb, " +
            "MAX(foll.image) image, " +
            "foll.phoneId, " +
            "MAX(foll.isMerchant) isMerchant, " +
            "MAX(IF(isnull(fans.id), 0, 1)) isFollowThis, " +
            "1 AS isFollowMe, " +
            "count(fans2.id) fansNum, " +
            "count(fans3.id) followNum " +
            "FROM " +
            "( " +
            "WITH follow AS ( " +
            "SELECT " +
            "substring_index(fans_id, '_', 1) AS flag, " +
            "substring_index(fans_id, '_', -1) AS phone " +
            "FROM " +
            "life_fans " +
            "WHERE " +
            "delete_flag = 0 " +
            "AND followed_id = #{fansId} " +
            ") " +
            "SELECT " +
            "info.id, " +
            "CASE " +
            "WHEN user.store_id IS NULL OR info.store_application_status = 0 THEN user.nick_name " +
            "ELSE info.store_name " +
            "END AS name, " +
            "CASE " +
            "WHEN user.store_id IS NULL OR info.store_application_status IN (0, 2) THEN user.account_blurb " +
            "ELSE info.store_blurb " +
            "END AS blurb, " +
            "user.head_img AS image, " +
            "concat('store_', user.phone) AS phoneId, " +
            "CASE " +
            "WHEN user.store_id IS NOT NULL AND info.store_application_status = 1 THEN '1' " +
            "ELSE '0' " +
            "END AS isMerchant " +
            "FROM " +
            "follow foll " +
            "JOIN store_user user ON foll.phone = user.phone " +
            "LEFT JOIN store_info info ON user.store_id IS NOT NULL " +
            "AND info.id = user.store_id " +
            "AND info.delete_flag = 0 " +
            "LEFT JOIN store_img img ON img.store_id = user.store_id " +
            "AND img.img_type = '10' " +
            "AND img.delete_flag = 0 " +
            "WHERE " +
            "foll.flag = 'store' " +
            "AND user.delete_flag = 0 " +
            ") foll " +
            "LEFT JOIN life_fans fans ON fans.followed_id = foll.phoneId " +
            "AND fans.fans_id = #{fansId} " +
            "AND fans.delete_flag = 0 " +
            "LEFT JOIN life_fans fans2 ON fans2.followed_id = foll.phoneId " +
            "AND fans2.delete_flag = 0 " +
            "LEFT JOIN life_fans fans3 ON fans3.fans_id = foll.phoneId " +
            "AND fans3.delete_flag = 0 " +
            "${ew.customSqlSegment}")
    IPage<LifeFansVo> getMyStoreFans(IPage<LifeFansVo> iPage, @Param("fansId") String fansId, @Param(Constants.WRAPPER) QueryWrapper<LifeFansVo> wrapper);

    @Select("select MAX(foll.id) id, MAX(foll.name) name, MAX(foll.image) image, foll.phoneId, MAX(foll.blurb) blurb, " +
            "  MAX(if(isnull(fans.id), 0, 1)) isFollowThis, 1 as isFollowMe, " +
            "       (select count(1) from life_fans fans2 where fans2.followed_id = foll.phoneId and fans2.delete_flag = 0) fansNum, " +
            "       (select count(1) from life_fans fans3 where fans3.fans_id = foll.phoneId and fans3.delete_flag = 0) followNum " +
            " from ( " +
            "    with follow as ( " +
            "    select substring_index(fans_id, '_', 1) as flag, substring_index(fans_id, '_', -1) as phone " +
            "    from life_fans " +
            "    where delete_flag = 0 and followed_id = #{fansId} " +
            "    ) " +
            "    select user.id, user.user_name name, user.user_image image, concat('user_', user.user_phone) phoneId, user.jianjie blurb " +
            "    from follow foll " +
            "    join life_user user on foll.phone = user.user_phone " +
            "    where foll.flag = 'user' and user.delete_flag = 0 " +
            ") foll " +
            "left join life_fans fans on fans.followed_id = foll.phoneId and fans.fans_id = #{fansId} and fans.delete_flag = 0 " +
            "${ew.customSqlSegment} ")
    IPage<LifeFansVo> getMyUserFans(IPage<LifeFansVo> iPage, @Param("fansId") String fansId, @Param(Constants.WRAPPER) QueryWrapper<LifeFansVo> wrapper);

    @Select("select MAX(foll.id) id, MAX(foll.name) name, MAX(foll.image) image, foll.phoneId, MAX(foll.blurb) blurb, MAX(foll.blockedType) blockedType, MAX(foll.blockedId) blockedId, MAX(foll.username) username, MAX(foll.accountBlurb) accountBlurb, MAX(foll.isMerchant) isMerchant, " +
            "  MAX(lb.id) blackListid, 1 as isFollowThis, 1 as isFollowMe, " +
            "       (select count(1) from life_fans fans2 where fans2.followed_id = foll.phoneId and fans2.delete_flag = 0) fansNum, " +
            "       (select count(1) from life_fans fans3 where fans3.fans_id = foll.phoneId and fans3.delete_flag = 0) followNum " +
            "from ( " +
            "    with follow as ( " +
            "        select substring_index(fans1.followed_id, '_', 1) as flag, substring_index(fans1.followed_id, '_', -1) as phone " +
            "        from life_fans fans1 " +
            "        join life_fans fans2 on fans1.followed_id = fans2.fans_id and fans1.fans_id = fans2.followed_id " +
            "        where fans1.delete_flag = 0 and fans2.delete_flag = 0 and fans1.fans_id = #{fansId} " +
            "    ) " +
            "    select info.id, IF(info.store_application_status = 0, user.nick_name, info.store_name) AS name, user.head_img image, concat('store_', user.phone) phoneId, IF(info.store_application_status = 0, user.account_blurb, info.store_blurb) AS blurb, 1 blockedType,user.id blockedId ,IFNULL(user.nick_name, user.name) username, user.account_blurb accountBlurb, IF(info.store_application_status = 1, '1', '0') AS isMerchant " +
            "    from follow foll " +
            "    join store_user user on foll.phone = user.phone " +
            "    join store_info info on info.id = user.store_id " +
            "    left join store_img img on img.store_id = user.store_id and img.img_type = '10' and img.delete_flag = 0 " +
            "    where foll.flag = 'store' and user.delete_flag = 0 and info.delete_flag = 0 " +
            "    union " +
            "    select user.id, user.user_name name, user.user_image image, concat('user_', user.user_phone) phoneId, user.jianjie blurb, 2 blockedType,user.id blockedId,'' username, '' accountBlurb, '0' AS isMerchant " +
            "    from follow foll " +
            "    join life_user user on foll.phone = user.user_phone " +
            "    where foll.flag = 'user' and user.delete_flag = 0 " +
            ") foll " +
            "left join life_blacklist lb on lb.blocked_type = foll.blockedType and lb.blocked_id = foll.blockedId and lb.blocker_type = #{blockerType} and lb.blocker_id = #{blockerId} and lb.delete_flag = 0 " +
            "${ew.customSqlSegment} ")
    IPage<LifeFansVo> getMutualAttention(IPage<LifeFansVo> iPage, @Param("fansId") String fansId, @Param("blockerType") String blockerType, @Param("blockerId") String blockerId, @Param(Constants.WRAPPER) QueryWrapper<LifeFansVo> wrapper);

    @Select("select MAX(foll.id) id, MAX(foll.name) name, MAX(foll.image) image, foll.phoneId, MAX(foll.blurb) blurb, 1 as isFollowThis, 1 as isFollowMe, " +
            "       (select count(1) from life_fans fans2 where fans2.followed_id = foll.phoneId and fans2.delete_flag = 0) fansNum, " +
            "       (select count(1) from life_fans fans3 where fans3.fans_id = foll.phoneId and fans3.delete_flag = 0) followNum " +
            "from ( " +
            "    with follow as ( " +
            "        select substring_index(fans1.followed_id, '_', 1) as flag, substring_index(fans1.followed_id, '_', -1) as phone " +
            "        from life_fans fans1 " +
            "        join life_fans fans2 on fans1.followed_id = fans2.fans_id and fans1.fans_id = fans2.followed_id " +
            "        where fans1.delete_flag = 0 and fans2.delete_flag = 0 and fans1.fans_id = #{fansId} " +
            "    ) " +
            "    select user.id, user.user_name name, user.user_image image, concat('user_', user.user_phone) phoneId, user.jianjie blurb " +
            "    from follow foll " +
            "    join life_user user on foll.phone = user.user_phone " +
            "    where foll.flag = 'user' and user.delete_flag = 0 " +
            ") foll " +
            "${ew.customSqlSegment} ")
    IPage<LifeFansVo> getMutualAttentionUser(IPage<LifeFansVo> iPage, @Param("fansId") String fansId, @Param(Constants.WRAPPER) QueryWrapper<LifeFansVo> wrapper);

    @Select("select (select count(1) from life_fans where fans_id = #{phoneId} and delete_flag = 0 and followed_id not REGEXP '_$') followNum, " +
            "(select count(1) from life_fans where followed_id= #{phoneId} and delete_flag = 0 and fans_id not REGEXP '_$') fansNum, " +
            "(select count(1) from ( " +
            "        select foll.*, 1 as isFollowThis, 1 as isFollowMe, " +
            "               (select count(1) from life_fans fans2 where fans2.followed_id = foll.phoneId and fans2.delete_flag = 0) fansNum, " +
            "               (select count(1) from life_fans fans3 where fans3.fans_id = foll.phoneId and fans3.delete_flag = 0) followNum " +
            "        from ( " +
            "            with follow as ( " +
            "                select substring_index(fans1.followed_id, '_', 1) as flag, substring_index(fans1.followed_id, '_', -1) as phone " +
            "                from life_fans fans1 " +
            "                join life_fans fans2 on fans1.followed_id = fans2.fans_id and fans1.fans_id = fans2.followed_id " +
            "                where fans1.delete_flag = 0 and fans2.delete_flag = 0 and fans1.fans_id = #{phoneId} " +
            "            ) " +
            "            select info.id, info.store_name name, img.img_url image, concat('store_', user.phone) phoneId " +
            "            from follow foll " +
            "            join store_user user on foll.phone = user.phone " +
            "            join store_info info on info.id = user.store_id " +
            "            left join store_img img on img.store_id = user.store_id and img.img_type = '10' and img.delete_flag = 0 " +
            "            where foll.flag = 'store' and user.delete_flag = 0 and info.delete_flag = 0 " +
            "            union " +
            "            select user.id, user.user_name name, user.user_image image, concat('user_', user.user_phone) phoneId " +
            "            from follow foll " +
            "            join life_user user on foll.phone = user.user_phone " +
            "            where foll.flag = 'user' and user.delete_flag = 0 " +
            "        ) foll " +
            "    ) a " +
            ") friendNum, " +
            "( " +
            "    select count(id) " +
            "    from life_user_dynamics " +
            "    where delete_flag = 0 and phone_id = #{phoneId} and draft = 0 " +
            ") dynamicsNum")
    LifeFansVo getHomePageInfo(@Param("phoneId") String phoneId);
}