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\n" +
            "INNER JOIN (\n" +
            "        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\n" +
            "        UNION ALL\n" +
            "        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\n" +
            "        UNION ALL\n" +
            "        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\n" +
            "        ) t ON t.cate_id=gsgc.cate_id\n" +
            "SET gsgc.counts=t.ct\n" +
            "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 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 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'
    @Select({"select cf.cate_id fcateId,\n" +
            "       cf.name    fname,\n" +
            "       cf.icon    ficon,\n" +
            "       cf.ne_isbn fneIsbn,\n" +
            "       cf.counts  fcounts,\n" +
            "       cs.cate_id scateId,\n" +
            "       cs.name    sname,\n" +
            "       cs.icon    sicon,\n" +
            "       cs.ne_isbn sneIsbn,\n" +
            "       cs.counts  scounts,\n" +
            "       ct.cate_id tcateId,\n" +
            "       ct.name    tname,\n" +
            "       ct.icon    ticon,\n" +
            "       ct.ne_isbn tneIsbn,\n" +
            "       ct.counts  tcounts\n" +
            "from goblin_self_goods_category cf\n" +
            "         left join (\n" +
            "    select cate_id, name, icon, grade, ne_isbn, counts, cate_pid from goblin_self_goods_category where grade = '2' and del_flg = '0'\n" +
            ") cs on cs.cate_pid = cf.cate_id\n" +
            "         left join (\n" +
            "    select cate_id, name, icon, grade, ne_isbn, counts, cate_pid from goblin_self_goods_category where grade = '3' and del_flg = '0'\n" +
            ") ct on ct.cate_pid = cs.cate_id\n" +
            "where cf.grade = '1' and del_flg = '0'"})
    List<GoblinSelfGoodsCategoryDto> listForForMultiGrade();

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