package com.liquidnet.service.goblin.mapper;

import com.liquidnet.service.goblin.dto.GoblinSelfGoodsCategoryDto;
import com.liquidnet.service.goblin.entity.GoblinSelfGoodsCategory;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * <p>
 * 平台商品分类 Mapper 接口
 * </p>
 *
 * @author liquidnet
 * @since 2021-12-27
 */
public interface GoblinSelfGoodsCategoryMapper extends BaseMapper<GoblinSelfGoodsCategory> {
    //UPDATE goblin_self_goods_category gsgc
    //INNER JOIN (
    //        SELECT cate_fid cate_id,count(1) ct FROM goblin_goods WHERE cate_fid IS NOT NULL AND del_flg='0' group by cate_fid
    //        UNION ALL
    //        SELECT cate_sid cate_id,count(1) ct FROM goblin_goods WHERE cate_sid IS NOT NULL AND del_flg='0' group by cate_sid
    //        UNION ALL
    //        SELECT cate_tid cate_id,count(1) ct FROM goblin_goods WHERE cate_tid IS NOT NULL AND del_flg='0' group by cate_tid
    //        ) t ON t.cate_id=gsgc.cate_id
    //SET gsgc.counts=t.ct
    //WHERE gsgc.del_flg='0'
    @Update("UPDATE goblin_self_goods_category gsgc " +
            "INNER JOIN (" +
            "        SELECT cate_fid cate_id,count(1) ct FROM goblin_goods WHERE cate_fid IS NOT NULL AND del_flg='0' group by cate_fid " +
            "        UNION ALL " +
            "        SELECT cate_sid cate_id,count(1) ct FROM goblin_goods WHERE cate_sid IS NOT NULL AND del_flg='0' group by cate_sid " +
            "        UNION ALL " +
            "        SELECT cate_tid cate_id,count(1) ct FROM goblin_goods WHERE cate_tid IS NOT NULL AND del_flg='0' group by cate_tid " +
            "        ) t ON t.cate_id=gsgc.cate_id " +
            "SET gsgc.counts=t.ct " +
            "WHERE gsgc.del_flg='0'")
    int statsForForMultiGrade();

    // SELECT cf.cate_id AS fcateId, cf.name AS fname, cf.icon AS ficon, cf.ne_isbn AS fneIsbn, cf.counts AS fcounts , cs.cate_id AS scateId, cs.name AS sname, cs.icon AS sicon, cs.ne_isbn AS sneIsbn, cs.counts AS scounts , ct.cate_id AS tcateId, ct.name AS tname, ct.icon AS ticon, ct.ne_isbn AS tneIsbn, ct.counts AS tcounts FROM goblin_self_goods_category cf LEFT JOIN ( SELECT mid, cate_id, name, icon, grade , ne_isbn, counts, cate_pid FROM goblin_self_goods_category WHERE grade = '2' AND del_flg = '0' ) cs ON cs.cate_pid = cf.cate_id LEFT JOIN ( SELECT mid, cate_id, name, icon, grade , ne_isbn, counts, cate_pid FROM goblin_self_goods_category WHERE grade = '3' AND del_flg = '0' ) ct ON ct.cate_pid = cs.cate_id WHERE cf.grade = '1' AND del_flg = '0' ORDER BY cf.mid, cs.mid, ct.mid;
    @Select({"select cf.cate_id fcateId," +
            "       cf.name    fname," +
            "       cf.icon    ficon," +
            "       cf.ne_isbn fneIsbn," +
            "       cf.counts  fcounts," +
            "       cs.cate_id scateId," +
            "       cs.name    sname," +
            "       cs.icon    sicon," +
            "       cs.ne_isbn sneIsbn," +
            "       cs.counts  scounts," +
            "       ct.cate_id tcateId," +
            "       ct.name    tname," +
            "       ct.icon    ticon," +
            "       ct.ne_isbn tneIsbn," +
            "       ct.counts  tcounts " +
            "from goblin_self_goods_category cf " +
            "         left join (" +
            "    select mid, cate_id, name, icon, grade, ne_isbn, counts, cate_pid from goblin_self_goods_category where grade = '2' and del_flg = '0' " +
            ") cs on cs.cate_pid = cf.cate_id " +
            "         left join (" +
            "    select mid, cate_id, name, icon, grade, ne_isbn, counts, cate_pid from goblin_self_goods_category where grade = '3' and del_flg = '0' " +
            ") ct on ct.cate_pid = cs.cate_id " +
            "where cf.grade = '1' and del_flg = '0' ORDER BY cf.mid,cs.mid,ct.mid"})
    List<GoblinSelfGoodsCategoryDto> listForMultiGrade();

    List<String> listForMultiGradeCateId(@Param("cateIdArr") String[] cateIdArr);

    // SELECT count(1) AS ct FROM ( SELECT gsgc2.cate_id, gsgc2.name, gsgc2.grade, gsgc2.cate_pid FROM goblin_self_goods_category gsgc2 WHERE gsgc2.del_flg = '0' AND gsgc2.cate_pid = '2268131118729' AND gsgc2.name = 'CD' UNION SELECT gsgc3.cate_id, gsgc3.name, gsgc3.grade, gsgc3.cate_pid FROM goblin_self_goods_category gsgc2 LEFT JOIN goblin_self_goods_category gsgc3 ON gsgc2.cate_id = gsgc3.cate_pid AND gsgc3.grade = '3' WHERE gsgc2.del_flg = '0' AND gsgc2.cate_pid = '2268131118729' AND gsgc3.name = 'CD' ) t
    @Select({"select count(1) ct " +
            "from (" +
            "         SELECT gsgc2.cate_id, gsgc2.name, gsgc2.grade, gsgc2.cate_pid " +
            "         from goblin_self_goods_category gsgc2 " +
            "         where gsgc2.del_flg = '0' " +
            "           and gsgc2.cate_pid = #{cateFid} " +
            "           and gsgc2.name = #{name} " +
            "         union " +
            "         SELECT gsgc3.cate_id, gsgc3.name, gsgc3.grade, gsgc3.cate_pid " +
            "         from goblin_self_goods_category gsgc2 " +
            "                  left join goblin_self_goods_category gsgc3 on gsgc2.cate_id = gsgc3.cate_pid and gsgc3.grade = '3' " +
            "         where gsgc2.del_flg = '0' " +
            "           and gsgc2.cate_pid = #{cateFid} " +
            "           and gsgc3.name = #{name} " +
            "     ) t"})
    int countForMatchName(@Param("cateFid") String cateFid, @Param("name") String name);
}
