package com.fs.course.mapper;

import com.fs.course.domain.FsCourseSopLogs;
import com.fs.course.param.FsCourseSopLogsAiBParam;
import com.fs.course.param.FsCourseSopLogsParam;
import com.fs.course.param.FsCourseSopParam;
import com.fs.course.vo.FsCourseSopLogsCVOList;
import com.fs.course.vo.FsCourseSopLogsListVO;
import com.fs.course.vo.FsCourseSopLogsTVOList;
import com.fs.course.vo.FsCourseSopLogsVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.ArrayList;
import java.util.List;

/**
 * 看课SOP  定时任务Mapper接口
 *
 * @author fs
 * @date 2024-09-04
 */
public interface FsCourseSopLogsMapper
{
    /**
     * 查询看课SOP  定时任务
     *
     * @param id 看课SOP  定时任务主键
     * @return 看课SOP  定时任务
     */
    public FsCourseSopLogs selectFsCourseSopLogsById(Long id);

    /**
     * 查询看课SOP  定时任务列表
     *
     * @param fsSopLogs 看课SOP  定时任务
     * @return 看课SOP  定时任务集合
     */
    public List<FsCourseSopLogs> selectFsCourseSopLogsList(FsCourseSopLogs fsSopLogs);

    /**
     * 获取单条规则 的 执行记录历史
     */
    @Select("<script>" +
            "SELECT cc.mobile,cu.nick_name as userName,qu.qw_user_name, qu.id as qwId,cu.user_id as companyUserId, qecc.external_user_name as externalUserName, fsl.*  " +
            "FROM " +
            "fs_course_sop_logs fsl " +
            "LEFT JOIN qw_user qu ON fsl.qw_userid=qu.qw_user_id " +
            "LEFT JOIN company_user cu on qu.company_user_id=cu.user_id " +
            "LEFT JOIN qw_external_contact_crm qecc on qecc.external_user_id=fsl.external_user_id " +
            "LEFT JOIN crm_customer cc on qecc.customer_id=cc.customer_id " +
            "<where> " +
            "fsl.sop_id=#{map.sopId} and fsl.company_id=#{map.companyId} " +
            "<if test='map.qwUserName != null'> AND qu.qw_user_name like concat('%', #{map.qwUserName}, '%')  </if> " +
            "<if test='map.externalUserName != null'> AND qecc.external_user_name like concat('%', #{map.externalUserName}, '%') </if> " +
            "<if test='map.sendStatus != null'> AND fsl.send_status = #{map.sendStatus}</if> " +
            "<if test='map.qwUserid != null'> AND qu.id = #{map.qwUserid}</if> " +
            "<if test='map.watchStatus != null'> AND fsl.watch_status = #{map.watchStatus}</if> " +
            "<if test='map.receivingStatus != null'> AND fsl.receiving_status = #{map.receivingStatus}</if> " +
            "</where> " +
            "</script>")
    public List<FsCourseSopLogsCVOList> selectFsCourseSopLogsCVOList(@Param("map") FsCourseSopLogsParam param);


    /**
     * 新增看课SOP  定时任务
     *
     * @param fsSopLogs 看课SOP  定时任务
     * @return 结果
     */
    public int insertFsCourseSopLogs(FsCourseSopLogs fsSopLogs);

    @Update("<script>" +
            "UPDATE fs_course_sop_logs SET send_status = 5 " +
            "WHERE " +
            "qw_userid=#{map.qwUserid} " +
            "and external_user_id=#{map.externalUserId} " +
            "and send_status= 3 " +
            "and receiving_status= 0 " +
            "and business_id=#{map.businessId} " +
            "<if test='map.watchStatus == 2'> " +
            "and watch_status= 1  " +
            "</if> " +
            "<if test='map.watchStatus == 3'> " +
            "and watch_status= 1 or watch_status = 2" +
            "</if> " +
            "</script>")
    public int updateFsCourseSopLogsByWatchLogType(@Param("map") FsCourseSopLogs sopLogs);


    /**
     * 修改看课SOP  定时任务
     *
     * @param fsSopLogs 看课SOP  定时任务
     * @return 结果
     */
    public int updateFsCourseSopLogs(FsCourseSopLogs fsSopLogs);

    /**
     * 删除看课SOP  定时任务
     *
     * @param id 看课SOP  定时任务主键
     * @return 结果
     */
    public int deleteFsCourseSopLogsById(Long id);

    /**
     * 批量删除看课SOP  定时任务
     *
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteFsCourseSopLogsByIds(Long[] ids);

    /**
     * AI-chat课程 获取员工sop发送列表历史
     */
    @Select("<script>" +
            "SELECT cc.weixin, cc.mobile, cu.nick_name AS userName, qu.qw_user_name, qu.id AS qwId, " +
            "cu.user_id AS companyUserId, qecc.external_user_name AS externalUserName, qst.* " +
            "FROM fs_course_sop_logs qst " +
            "LEFT JOIN qw_user qu ON qst.qw_userid = qu.qw_user_id " +
            "LEFT JOIN company_user cu ON qu.company_user_id = cu.user_id " +
            "LEFT JOIN qw_external_contact_crm qecc ON qecc.external_user_id = qst.external_user_id " +
            "LEFT JOIN crm_customer cc ON qecc.customer_id = cc.customer_id " +
            "<where> " +
            "cu.user_id = #{map.userId} AND qst.company_id = #{map.companyId} " +
            "<if test='map.watchStatus != null'> AND qst.watch_status = #{map.watchStatus}</if> " +
            "<if test='map.sopId != null'> AND qst.sop_id = #{map.sopId}</if> " +
            "<if test='map.sendStatus != null'> " +
            "AND qst.send_status = #{map.sendStatus} " +
            "</if> " +
            "<if test='map.sendStatus != null and map.sendStatus == 3'> " +
            "AND qst.send_time &lt; NOW() " +
            "</if> " +
            "<if test='map.receivingStatus != null'> AND qst.receiving_status = #{map.receivingStatus}</if> " +
            "</where> " +
            "</script>")

    List<FsCourseSopLogsListVO> selectSopSendCustomerLogsListVO(@Param("map") FsCourseSopParam param);

    /**
     * AI-chat课程 获取员工sop发送历史 详情
     */
    @Select("<script>" +
            "SELECT cc.weixin, cc.mobile,cu.nick_name as userName,qu.qw_user_name, qu.id as qwId,cu.user_id as companyUserId, qecc.external_user_name as externalUserName, qst.*  " +
            "FROM " +
            "fs_course_sop_logs qst " +
            "LEFT JOIN qw_user qu ON qst.qw_userid=qu.qw_user_id " +
            "LEFT JOIN company_user cu on qu.company_user_id=cu.user_id " +
            "LEFT JOIN qw_external_contact_crm qecc on qecc.external_user_id=qst.external_user_id " +
            "LEFT JOIN crm_customer cc on qecc.customer_id=cc.customer_id " +
            "where " +
            "cu.user_id=#{map.userId} " +
            "and qst.company_id=#{map.companyId} " +
            "and qst.id= #{map.id} " +
            "</script>")
    FsCourseSopLogsVO getFsCourseSopLogsDetailVO(@Param("map") FsCourseSopLogsAiBParam fsSopLogsAiBParam);

//    @Select("WITH RankedLogs AS (\n" +
//            "    SELECT\n" +
//            "        *,\n" +
//            "        ROW_NUMBER() OVER (PARTITION BY qw_userid ORDER BY send_time) AS rn\n" +
//            "    FROM\n" +
//            "        fs_course_sop_logs\n" +
//            "    WHERE\n" +
//            "        send_status = 3 \n" +
//            "        AND receiving_status = 0 \n" +
//            "        AND send_time <= NOW()\n" +
//            ")\n" +
//            "SELECT\n" +
//            "    cc.weixin,\n" +
//            "    cc.mobile,\n" +
//            "    cu.nick_name AS userName,\n" +
//            "    cu.user_id AS companyUserId,\n" +
//            "    cc.qw_name AS externalUserName,\n" +
//            "    qst.*\n" +
//            "FROM\n" +
//            "    RankedLogs qst\n" +
//            "LEFT JOIN company_user cu ON qst.company_user_id = cu.user_id\n" +
//            "LEFT JOIN crm_customer cc ON qst.customer_id = cc.customer_id \n" +
//            "WHERE\n" +
//            "    qst.rn = 1 \n")
    @Select("<script> " +
            "WITH RankedLogs AS (\n" +
            "    SELECT\n" +
            "        *,\n" +
            "        ROW_NUMBER() OVER (PARTITION BY qw_userid ORDER BY send_time) AS rn\n" +
            "    FROM\n" +
            "        fs_course_sop_logs\n" +
            "    WHERE\n" +
            "        send_status = 3 \n" +
            "        AND receiving_status = 0 \n" +
            "        AND send_time &lt; NOW()\n" +
            ")\n" +
            "SELECT\n" +
            "    cc.weixin,\n" +
            "    cc.mobile,\n" +
            "    cu.nick_name AS userName,\n" +
            "    cu.user_id AS companyUserId,\n" +
            "    cc.qw_name AS externalUserName,\n" +
            "    qst.*\n" +
            "FROM\n" +
            "    RankedLogs qst\n" +
            "LEFT JOIN company_user cu ON qst.company_user_id = cu.user_id\n" +
            "LEFT JOIN crm_customer cc ON qst.customer_id = cc.customer_id \n" +
            "WHERE\n" +
            "    qst.rn = 1\n" +
            "    AND qst.company_user_id in " +
            " <foreach collection='list'  item='item' index='index'  open='(' separator=',' close=')'> #{item} </foreach> " +
            "</script>")
    public List<FsCourseSopLogsTVOList> selectCourseSopLogsTVOList(@Param("list") List<String> arrayList);


    @Update("<script>" +
            "update fs_course_sop_logs set send_status = 3 , send_time= #{maps.sendTime} " +
            "where " +
            "qw_userid = #{maps.qwUserid} " +
            "and watch_status = #{maps.watchStatus} " +
            "and send_status = #{maps.sendStatus} " +
            "and sop_type = #{maps.sopType} " +
            "and business_id = #{maps.businessId} " +
            "and external_user_id = #{maps.externalUserId} " +
            "</script>")
    int updateWatchStatus(@Param("maps") FsCourseSopLogs courseSopLogs);

}
