package com.fs.qw.mapper;

import com.fs.qw.domain.QwAutoTags;
import com.fs.qw.param.QwAutoTagsParam;
import com.fs.qw.vo.QwAutoTagsVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 自动打标签主Mapper接口
 *
 * @author fs
 * @date 2024-07-20
 */
@Repository
public interface QwAutoTagsMapper
{
    /**
     * 查询自动打标签主
     *
     * @param id 自动打标签主主键
     * @return 自动打标签主
     */

    public QwAutoTags selectQwAutoTagsById(Long id);
    @Select("SELECT * FROM qw_auto_tags WHERE id = #{id}")
    public QwAutoTagsVO selectQwAutoTagsByIdVO(Long id);

    /** 根据qw_user的id匹配最新的符合条件的标签 */
    @Select("SELECT * FROM  qw_auto_tags " +
            "WHERE " +
            "corp_id=#{corpId} " +
            "AND is_apply='1' " +
            "AND JSON_CONTAINS(apply_users, CAST(#{qwUserId} AS JSON), '$') " +
            "AND type='3' " +
            "ORDER BY create_time DESC " +
            "LIMIT 1;")
    public QwAutoTags selectQwAutoTagsByIdJSON(@Param("corpId")String corpId, @Param("qwUserId") Long qwUserId);



    @Select("SELECT * FROM  qw_auto_tags " +
            "WHERE " +
            "corp_id=#{corpId} " +
            "AND is_apply='1' " +
            "AND JSON_CONTAINS(apply_users, CAST(#{qwUserId} AS JSON), '$') " +
            "AND type='4' " +
            "ORDER BY create_time DESC " +
            "LIMIT 1;")
    public QwAutoTags selectQwAutoReplyTagsByIdJSON(@Param("corpId")String corpId, @Param("qwUserId") Long qwUserId);


    /**
     * 根据群id 匹配最新的符合条件的标签
     */
    @Select("SELECT *  FROM qw_auto_tags " +
            "WHERE " +
            "    corp_id = #{corpId}" +
            "    AND is_apply = '1' " +
            "    AND type = '2' " +
            "    AND EXISTS ( " +
            "        SELECT 1 " +
            "        FROM JSON_TABLE(rules_tags,'$[*]' COLUMNS (rules JSON PATH '$.rules') ) AS jt " +
            "        WHERE JSON_CONTAINS(jt.rules, CONCAT('\"', #{CharId}, '\"'), '$') " +
            "    ) " +
            "ORDER BY " +
            "create_time DESC " +
            "LIMIT 1 ")
    public QwAutoTags selectQwAutoTagsByChatIdJSON(@Param("corpId")String corpId, @Param("CharId") String CharId);

    /**
     * 查询自动打标签主列表
     *
     * @param qwAutoTags 自动打标签主
     * @return 自动打标签主集合
     */
    public List<QwAutoTags> selectQwAutoTagsList(QwAutoTags qwAutoTags);

//    @Select("<script>" +
//            "SELECT " +
//            "COUNT(CASE WHEN  DATE(qatl.add_time) = CURDATE() THEN 1 END) AS numTagsTadayCount, " +
//            "COUNT(qatl.auto_tag_id) AS totalNumTagsCount,  " +
//            "qat.*  " +
//            "FROM " +
//            "qw_auto_tags qat " +
//            "LEFT JOIN qw_auto_tags_logs qatl ON qat.id = qatl.auto_tag_id  " +
//            "<if test=\"type != null and type==3 and ruleType==00 \"> " +
//            "Left join qw_user qu  ON FIND_IN_SET(qu.id,REPLACE(REPLACE(REPLACE(qat.apply_users, '[', ''), ']', ''), ' ', '')) > 0  " +
//            "</if> " +
//            "AND qat.company_id = qatl.company_id " +
//            "<where> " +
//            "            <if test=\"type != null \"> and qat.type = #{type}</if> " +
//            "            <if test=\"createName != null  and createName != ''\"> and qat.create_name like concat('%', #{createName}, '%')</if> " +
//            "            <if test=\"applyUsers != null  and applyUsers != ''\"> and qat.apply_users = #{applyUsers}</if> " +
//            "            <if test=\"ruleName != null  and ruleName != ''\"> and qat.rule_name like concat('%', #{ruleName}, '%')</if> " +
//            "            <if test=\"rulesTags != null  and rulesTags != ''\"> and qat.rules_tags = #{rulesTags}</if> " +
//            "            <if test=\"totalNumTags != null \"> and qat.total_num_tags = #{totalNumTags}</if> " +
//            "            <if test=\"numTagsTaday != null \"> and qat.num_tags_taday = #{numTagsTaday}</if> " +
//            "            <if test=\"isApply != null \"> and qat.is_apply = #{isApply}</if> " +
//            "            <if test=\"companyId != null \"> and qat.company_id = #{companyId}</if> "+
//            "            <if test=\"type != null  and type==3 and ruleType==01 \"> and qu.company_user_id = #{companyUserId}</if> " +
//            "</where> " +
//            "GROUP BY qat.id " +
//            "ORDER BY  qat.create_time desc "+
//            "</script>")
@Select("<script>" +
        "SELECT " +
        "COUNT(CASE WHEN DATE(qatl.add_time) = CURDATE() THEN 1 END) AS numTagsTodayCount, " +
        "COUNT(qatl.auto_tag_id) AS totalNumTagsCount, " +
        "qat.* " +
        "FROM qw_auto_tags qat " +
        "LEFT JOIN qw_auto_tags_logs qatl ON qat.id = qatl.auto_tag_id " +
        "AND qat.corp_id = qatl.corp_id " +
        "<where> " +
            "            <if test=\"type != null \"> and qat.type = #{type}</if> " +
            "            <if test=\"createName != null  and createName != ''\"> and qat.create_name like concat( #{createName}, '%')</if> " +
            "            <if test=\"applyUsers != null  and applyUsers != ''\"> and qat.apply_users = #{applyUsers}</if> " +
            "            <if test=\"appleUserOne != null  and appleUserOne != ''\"> AND FIND_IN_SET(#{appleUserOne}, REPLACE(REPLACE(qat.apply_users, '[', ''), ']', '')) </if> " +
            "            <if test=\"ruleName != null  and ruleName != ''\"> and qat.rule_name like concat( #{ruleName}, '%')</if> " +
            "            <if test=\"rulesTags != null  and rulesTags != ''\"> and qat.rules_tags = #{rulesTags}</if> " +
            "            <if test=\"totalNumTags != null \"> and qat.total_num_tags = #{totalNumTags}</if> " +
            "            <if test=\"numTagsTaday != null \"> and qat.num_tags_taday = #{numTagsTaday}</if> " +
            "            <if test=\"isApply != null \"> and qat.is_apply = #{isApply}</if> " +
            "            <if test=\"companyId != null \"> and qat.company_id = #{companyId}</if> "+
            "            <if test=\"corpId != null \"> and qat.corp_id = #{corpId}</if> "+
            "            <if test=\"isType != null \"> and (qat.type = 3 or  qat.type = 4) </if> "+
        "</where> " +
        "GROUP BY qat.id " +
        "ORDER BY qat.create_time DESC " +
        "</script>")
public List<QwAutoTagsVO> selectQwAutoTagsListVO(QwAutoTagsParam qwAutoTags);


    @Select("<script>" +
            "SELECT " +
            "COUNT(CASE WHEN DATE(qatl.add_time) = CURDATE() THEN 1 END) AS numTagsTodayCount, " +
            "COUNT(qatl.auto_tag_id) AS totalNumTagsCount, " +
            "qat.* " +
            "FROM qw_auto_tags qat " +
            "LEFT JOIN qw_auto_tags_logs qatl ON qat.id = qatl.auto_tag_id AND qat.company_id = qatl.company_id " +
            "LEFT JOIN qw_user qu ON FIND_IN_SET(qu.id, REPLACE(REPLACE(REPLACE(qat.apply_users, '[', ''), ']', ''), ' ', '')) > 0 " +
            "<where> " +
            "            <if test=\"type != null \"> and qat.type = #{type}</if> " +
            "            <if test=\"createName != null  and createName != ''\"> and qat.create_name like concat('%', #{createName}, '%')</if> " +
            "            <if test=\"applyUsers != null  and applyUsers != ''\"> and qat.apply_users = #{applyUsers}</if> " +
            "            <if test=\"ruleName != null  and ruleName != ''\"> and qat.rule_name like concat('%', #{ruleName}, '%')</if> " +
            "            <if test=\"rulesTags != null  and rulesTags != ''\"> and qat.rules_tags = #{rulesTags}</if> " +
            "            <if test=\"totalNumTags != null \"> and qat.total_num_tags = #{totalNumTags}</if> " +
            "            <if test=\"numTagsTaday != null \"> and qat.num_tags_taday = #{numTagsTaday}</if> " +
            "            <if test=\"isApply != null \"> and qat.is_apply = #{isApply}</if> " +
            "            <if test=\"companyId != null \"> and qat.company_id = #{companyId}</if> "+
            "            <if test=\"type != null  \"> and qu.company_user_id = #{companyUserId}</if> " +
            "</where> " +
            "GROUP BY qat.id " +
            "ORDER BY qat.create_time DESC " +
            "</script>")
public List<QwAutoTagsVO> selectQwAutoTagsListMyVO(QwAutoTags qwAutoTags);

    /**
     * 新增自动打标签主
     *
     * @param qwAutoTags 自动打标签主
     * @return 结果
     */
    public int insertQwAutoTags(QwAutoTags qwAutoTags);

    /**
     * 修改自动打标签主
     *
     * @param qwAutoTags 自动打标签主
     * @return 结果
     */
    public int updateQwAutoTags(QwAutoTags qwAutoTags);

    /**
     * 删除自动打标签主
     *
     * @param id 自动打标签主主键
     * @return 结果
     */
    public int deleteQwAutoTagsById(Long id);

    /**
     * 批量删除自动打标签主
     *
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteQwAutoTagsByIds(Long[] ids);
}
