package com.fs.course.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.fs.common.annotation.DataSource;
import com.fs.common.enums.DataSourceType;
import com.fs.course.domain.FsCourseWatchLog;
import com.fs.course.dto.WatchLogDTO;
import com.fs.course.param.*;
import com.fs.course.vo.*;
import com.fs.qw.domain.QwExternalContact;
import com.fs.qw.param.QwSidebarStatsParam;
import com.fs.sop.vo.QwRatingVO;
import com.fs.statis.dto.WatchCourseStatisticsResultDTO;
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.Date;
import java.util.List;
import java.util.Map;

/**
 * 短链课程看课记录Mapper接口
 *
 * @author fs
 * @date 2024-09-18
 */
public interface FsCourseWatchLogMapper extends BaseMapper<FsCourseWatchLog> {
    /**
     * 查询短链课程看课记录
     *
     * @param logId 短链课程看课记录主键
     * @return 短链课程看课记录
     */
    public FsCourseWatchLog selectFsCourseWatchLogByLogId(Long logId);

    /**
     * 查询短链课程看课记录列表
     *
     * @param fsCourseWatchLog 短链课程看课记录
     * @return 短链课程看课记录集合
     */
    public List<FsCourseWatchLog> selectFsCourseWatchLogList(FsCourseWatchLog fsCourseWatchLog);

    /**
     * 查询短链课程看课记录
     *
     * @param param 短链课程看课记录
     * @return 短链课程看课记录
     */
    @Select("SELECT wl.log_id, wl.log_type FROM " +
            "fs_course_watch_log  wl " +
            "left join crm_customer cc on wl.user_id=cc.user_id " +
            "left join qw_external_contact ec on wl.qw_external_contact_id=ec.id " +
            "WHERE " +
            " cc.customer_id = #{map.customerId} " +
            "and wl.video_id = #{map.videoId} " +
            "and ec.external_user_id= #{map.externalUserId} " )
    public FsCourseWatchLog selectFsCourseWatchLog(@Param("map") FsCourseWatchLogParam param);


    /**
     * 新增短链课程看课记录
     *
     * @param fsCourseWatchLog 短链课程看课记录
     * @return 结果
     */
    public int insertFsCourseWatchLog(FsCourseWatchLog fsCourseWatchLog);

    public int insertOrUpdateFsCourseWatchLog(FsCourseWatchLog fsCourseWatchLog);

    /**
     * 修改短链课程看课记录
     *
     * @param fsCourseWatchLog 短链课程看课记录
     * @return 结果
     */
    public int updateFsCourseWatchLog(FsCourseWatchLog fsCourseWatchLog);

    @Update("UPDATE fs_course_watch_log SET user_id = #{userId} WHERE qw_external_contact_id = #{externalUserId}")
    public int updateFsCourseWatchLogByQwExternalId(@Param("externalUserId") Long externalUserId,@Param("userId") Long userId);

    /**
     * 删除短链课程看课记录
     *
     * @param logId 短链课程看课记录主键
     * @return 结果
     */
    public int deleteFsCourseWatchLogByLogId(Long logId);

    /**
     * 批量删除短链课程看课记录
     *
     * @param logIds 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteFsCourseWatchLogByLogIds(Long[] logIds);

    @Select("select * from fs_course_watch_log where user_id = #{userId} and video_id = #{videoId} and log_type = 2")
    FsCourseWatchLog getFinishCourseVideo(@Param("userId") Long userId, @Param("videoId") Long videoId);


    //临时看课记录
    @Select("select * from fs_course_watch_log where user_id = #{userId} and video_id = #{videoId} and company_user_id = #{companyUserId}")
    FsCourseWatchLog getWatchLogByTemporary(@Param("userId") Long userId,
                                          @Param("videoId") Long videoId,
                                          @Param("companyUserId") Long companyUserId);

    @Select("select * from fs_course_watch_log " +
            "where user_id = #{userId} " +
            "and video_id = #{videoId} " +
            "and qw_user_id = #{qwUserId} " +
            "and qw_external_contact_id = #{externalId}")
    FsCourseWatchLog getWatchCourseVideo(@Param("userId") Long userId, @Param("videoId") Long videoId,@Param("qwUserId") String qwUserId,@Param("externalId") Long externalId);

    @Select("select * from fs_course_watch_log " +
            "where qw_external_contact_id = #{externalId} " +
            "and video_id = #{videoId} " +
            "and qw_user_id = #{qwUserId} ")
    FsCourseWatchLog getWatchCourseVideoByExt( @Param("externalId") Long externalId, @Param("videoId") Long videoId,@Param("qwUserId") String qwUserId);

    @Select("select * from fs_course_watch_log " +
            "where video_id = #{videoId} " +
            "and qw_user_id = #{qwUserId} " +
            "and qw_external_contact_id = #{externalId} ")
    FsCourseWatchLog getWatchCourseLogVideoBySop(@Param("videoId") Long videoId,
                                                 @Param("qwUserId") String qwUserId,
                                                 @Param("externalId") Long externalId);

    List<FsCourseWatchLogListVO> selectFsCourseWatchLogListVO(@Param("maps") FsCourseWatchLogListParam param);

    /**
     * P0：分页列表仅查主键（供 COUNT / LIMIT）
     */
    List<Long> selectFsCourseWatchLogListIds(@Param("maps") FsCourseWatchLogListParam param);

    /**
     * P0：按主键回表查询详情
     */
    List<FsCourseWatchLogListVO> selectFsCourseWatchLogListByLogIds(@Param("logIds") List<Long> logIds);

    List<FsCourseWatchLogListVO> selectFsCourseWatchLogListByParam(FsCourseWatchLogListParam param);

    @Select("select l.*,v.title,c.course_name from fs_course_watch_log l LEFT JOIN fs_user_course_video v ON v.video_id = l.video_id LEFT JOIN fs_user_course c ON c.course_id = l.course_id WHERE l.qw_external_contact_id =#{id} ORDER BY l.create_time  desc LIMIT 1  ")
    FsCourseWatchLogVO selectFsCourseWatchLogByExtId(Long id);

    @DataSource(DataSourceType.SLAVE)
    @Select("SELECT l.qw_external_contact_id,l.company_id,l.company_user_id,l.log_type,ext.`level`,l.qw_user_id "
            + "FROM fs_course_watch_log l "
            + "LEFT JOIN qw_external_contact ext ON ext.id = l.qw_external_contact_id "
            + "WHERE l.sop_id = #{SopId} AND l.log_type = 3 "
            + "AND l.create_time >= CURDATE() AND l.create_time < CURDATE() + INTERVAL 1 DAY "
            + "AND NOT EXISTS (SELECT 1 FROM fs_user_course_video v WHERE v.video_id = l.video_id AND v.is_first = 1)")
    List<FsCourseWatchLogTaskVO> selectFsCourseWatchLogByDaySopId3(@Param("SopId")String SopId);

    @DataSource(DataSourceType.SLAVE)
    @Select("SELECT l.qw_external_contact_id,l.company_id,l.company_user_id,l.log_type,ext.`level`,l.qw_user_id "
            + "FROM fs_course_watch_log l "
            + "LEFT JOIN qw_external_contact ext ON ext.id = l.qw_external_contact_id "
            + "WHERE l.sop_id = #{SopId} AND l.log_type = 4 "
            + "AND l.create_time >= CURDATE() AND l.create_time < CURDATE() + INTERVAL 1 DAY "
            + "AND NOT EXISTS (SELECT 1 FROM fs_user_course_video v WHERE v.video_id = l.video_id AND v.is_first = 1)")
    List<FsCourseWatchLogTaskVO> selectFsCourseWatchLogByDaySopId4(@Param("SopId")String SopId);

//    @Select("   SELECT\n" +
//            "            wl.log_type,\n" +
//            "            ec.level\n" +
//            "        FROM\n" +
//            "            fs_course_watch_log wl LEFT JOIN qw_external_contact ec on wl.qw_external_contact_id=ec.id\n" +
//            "        WHERE\n" +
//            "            wl.send_type = 2\n" +
//            "        and wl.qw_external_contact_id= #{externalId}\n" +
//            "        AND wl.create_time &gt;= DATE_SUB(CURDATE(), INTERVAL #{dayNum} DAY)\n" +
//            "        AND wl.create_time &lt; CURDATE()\n")

    @Select("SELECT\n" +
            "  wl.duration as watchDuration,\n" +
            "\tcv.duration as allDuration,\n" +
            "\tec.`level` AS levelType\n" +
            "FROM\n" +
            "\tfs_course_watch_log wl\n" +
            "\tLEFT JOIN qw_external_contact ec ON wl.qw_external_contact_id = ec.id\n" +
            "\tLEFT JOIN fs_user_course_video cv ON wl.video_id = cv.video_id \n" +
            "WHERE\n" +
            "\twl.send_type = 2 \n" +
            "\tAND wl.qw_external_contact_id = #{externalId} \n" +
            "\tAND wl.update_time >= DATE_SUB( CURDATE(), INTERVAL #{dayNum} DAY ) \n" +
            "\tAND wl.update_time < CURDATE() ")
    List<QwRatingVO> selectFsCourseWatchLogByExtIdRating(@Param("externalId") Long externalId, @Param("dayNum") Integer dayNum);

    @Select("SELECT\n" +
            "\twl.duration AS watchDuration,\n" +
            "\tcv.duration AS allDuration,\n" +
            "\twl.finish_time,\n" +
            "\twl.update_time," +
            "\tec.`level` \n" +
            "FROM\n" +
            "\tfs_course_watch_log wl\n" +
            "\tLEFT JOIN qw_external_contact ec ON wl.qw_external_contact_id = ec.id\n" +
            "\tLEFT JOIN fs_user_course_video cv ON wl.video_id = cv.video_id \n" +
            "WHERE\n" +
            "\twl.send_type = 2 \n" +
            "\tAND wl.qw_external_contact_id = #{externalId} \n" +
            "\tAND wl.update_time >= DATE_SUB(CURDATE(), INTERVAL #{dayNum} DAY ) \n" +
            "\tAND wl.update_time < CURDATE()")
    List<QwRatingVO> selectFsCourseWatchLogByExtIdRatingMoreStudyDays(@Param("externalId") Long externalId, @Param("dayNum") Integer dayNum);

    /**
     * 查询看课记录（支持在职转接场景）
     * 同时查询当前外部联系人ID和最近一次转接前的外部联系人ID的看课记录
     */
    @Select("SELECT\n" +
            "\twl.duration AS watchDuration,\n" +
            "\tcv.duration AS allDuration,\n" +
            "\twl.finish_time,\n" +
            "\twl.update_time," +
            "\tec.`level` \n" +
            "FROM\n" +
            "\tfs_course_watch_log wl\n" +
            "\tLEFT JOIN qw_external_contact ec ON wl.qw_external_contact_id = ec.id\n" +
            "\tLEFT JOIN fs_user_course_video cv ON wl.video_id = cv.video_id \n" +
            "WHERE\n" +
            "\twl.send_type = 2 \n" +
            "\tAND (\n" +
            "\t\twl.qw_external_contact_id = #{externalId}\n" +
            "\t\tOR wl.qw_external_contact_id = (\n" +
            "\t\t\tSELECT t.external_contact_id \n" +
            "\t\t\tFROM qw_external_contact_transfer_log t \n" +
            "\t\t\tINNER JOIN qw_external_contact ec2 ON t.external_contact_id = ec2.id\n" +
            "\t\t\tWHERE ec2.external_user_id = (\n" +
            "\t\t\t\tSELECT external_user_id FROM qw_external_contact WHERE id = #{externalId}\n" +
            "\t\t\t)\n" +
            "\t\t\tAND t.status = 1\n" +
            "\t\t\tORDER BY t.create_time DESC\n" +
            "\t\t\tLIMIT 1\n" +
            "\t\t)\n" +
            "\t)\n" +
            "\tAND wl.update_time >= DATE_SUB(CURDATE(), INTERVAL #{dayNum} DAY ) \n" +
            "\tAND wl.update_time < CURDATE()")
    List<QwRatingVO> selectFsCourseWatchLogByExtIdRatingWithTransfer(@Param("externalId") Long externalId, @Param("dayNum") Integer dayNum);

    @Select("SELECT\n" +
            "\tCOALESCE(SUM(wl.duration), 0) AS watchDuration\n" +
            "FROM\n" +
            "\tfs_course_watch_log wl\n" +
            "WHERE\n" +
            "\twl.send_type = 2 \n" +
            "\tAND wl.qw_external_contact_id = #{externalId} \n" +
            "\tAND wl.update_time >= DATE_SUB( CURDATE(), INTERVAL #{dayNum} DAY ) \n" +
            "\tAND wl.update_time < CURDATE()")
    Integer selectFsCourseWatchLogByByIsDaysNotStudy(@Param("externalId") Long externalId,@Param("dayNum") Integer dayNum);

    @Select("select l.*,v.title,c.course_name from fs_course_watch_log l LEFT JOIN fs_user_course_video v ON v.video_id = l.video_id LEFT JOIN fs_user_course c ON c.course_id = l.course_id WHERE l.qw_external_contact_id =#{ExtId} and l.qw_user_id=#{QwUserId} and DATE(l.create_time) =CURDATE() ORDER BY l.create_time  desc LIMIT 1  ")
    FsCourseWatchLogVO selectFsCourseWatchLogByExtIdAndQwUserId(@Param("ExtId")String ExtId,@Param("QwUserId")Long QwUserId);

    void insertFsCourseWatchLogBatch(@Param("watchLogs") List<FsCourseWatchLog> watchLogsToInsert);

    @Select("select * from fs_course_watch_log where log_type = 1 ")
    List<FsCourseWatchLog> findAllWatchingLogs();


    @Select("SELECT * \n" +
            "FROM fs_course_watch_log \n" +
            "WHERE log_type = 4 AND last_heartbeat_time >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 HOUR) ")
    List<FsCourseWatchLog> findWatchLogsExit();


    @Select("SELECT * \n" +
            "FROM fs_course_watch_log \n" +
            "WHERE (log_type = 1 or log_type = 4) AND finish_time is not null ")
    List<FsCourseWatchLog> findWatchLogsExitAndWatching();

    @Select("select * from fs_course_watch_log where log_type = 2 and send_finish_msg = 0 AND finish_time >= CURRENT_DATE\n" +
            "AND finish_time < CURRENT_DATE + INTERVAL 1 DAY")
    List<FsCourseWatchLog> selectFsCourseWatchLogFinish();

    @Select({"<script> " +
            "select t.* " +
            "<if test= 'sendType != 1 '> " +
            " ,concat(round(if(t.send_number=0,0,(t.on_line_num/t.send_number)*100),2),'%') on_line_rate" +
            " ,concat(round(if(t.send_number=0,0,(t.type2/t.send_number)*100),2),'%') finished_rate" +
            "</if> " +
            "from (" +
            "SELECT \n" +
            "o.video_id,o.company_id,o.qw_user_id,DATE(o.create_time) create_time," +
            "<if test= 'sendType != 1 '> " +
            " qu.qw_user_name qw_user_name," +
            "</if>\n" +
            "<if test= 'sendType == 1 '> " +
            " cu.nick_name qw_user_name," +
            "</if>\n" +
            "v.title videoName,uc.course_name,\n" +
            "SUM(CASE WHEN o.log_type = '1' THEN 1 ELSE 0 END) AS type1,\n" +
            "SUM(CASE WHEN o.log_type = '2' THEN 1 ELSE 0 END) AS type2,\n" +
            "SUM(CASE WHEN o.log_type = '3' THEN 1 ELSE 0 END) AS type3,\n" +
            "SUM(CASE WHEN o.log_type = '4' THEN 1 ELSE 0 END) AS type4,\n" +
            "(\n" +
            "  SUM(CASE WHEN o.log_type = '1' THEN 1 ELSE 0 END) +\n" +
            "  SUM(CASE WHEN o.log_type = '2' THEN 1 ELSE 0 END) +\n" +
            "  SUM(CASE WHEN o.log_type = '4' THEN 1 ELSE 0 END)\n" +
            ") AS on_line_num " +
            "<if test= 'sendType != 1 '> " +
            " ,count(o.log_id) send_number" +
            " ,sum(if((o.user_id is not null or o.user_id>0) and o.log_type=3,1,0)) is_user_wait_number" +
            " ,sum(if((o.user_id is null or o.user_id=0) and o.log_type=3,1,0)) no_user_wait_number" +
//            " ,sum(ifnull(fcr.amount,0)) red_amount" +
            ",COALESCE((SELECT SUM(amount) FROM fs_course_red_packet_log \n" +
            "     WHERE user_id = o.user_id AND video_id = o.video_id), 0) as red_amount " +
            "</if> " +
            "FROM fs_course_watch_log o " +
            "<if test= 'sendType != 1 '> " +
            " LEFT JOIN qw_user qu on qu.id=o.qw_user_id " +
//            " LEFT JOIN fs_course_red_packet_log fcr on o.user_id = fcr.user_id and fcr.video_id = o.video_id" + //会有笛卡尔积问题
            "</if>\n" +
            "LEFT JOIN fs_user_course_video v on v.video_id=o.video_id \n" +
            "LEFT JOIN fs_user_course uc on uc.course_id=v.course_id\n" +
            "<if test= 'sendType == 1 '> " +
            " LEFT JOIN company_user cu on cu.user_id=o.company_user_id\n" +
            "</if>\n" +
            "<where> \n" +
            "<if test = 'companyId != null and companyId != 0'>\n" +
            "and o.company_id=#{companyId} \n" +
            "</if>\n" +
            "<if test = 'userIds != null and userIds.size() > 0'>\n" +
            "and o.company_user_id in \n" +
            "        <foreach collection='userIds' item='id' open='(' separator=',' close=')'>\n" +
            "            #{id}\n" +
            "        </foreach>\n" +
            "</if>\n" +
            "<if test= 'sendType != null '> " +
            "       and  send_type= #{sendType} " +
            "</if>\n" +
            "<if test= 'sTime != null '> " +
            "       and o.create_time &gt;= #{sTime}\n" +
            "</if>\n" +
            "<if test='eTime != null '> " +
            "      and o.create_time &lt; DATE_ADD(#{eTime}, INTERVAL 1 DAY)\n" +
            "</if>" +
            "<if test ='sendType != 1 and nickName !=null and nickName!=\"\"'>\n" +
            "   and qu.qw_user_name like concat( #{nickName}, '%')\n" +
            "</if>" +
            "<if test ='sendType == 1 and nickName !=null and nickName!=\"\"'>\n" +
            "   and cu.nick_name like concat( #{nickName}, '%')\n" +
            "</if>" +
            "<if test ='courseId !=null'> " +
            "     and o.course_id = #{courseId} " +
            "</if>" +
            "<if test ='qwSopId !=null and qwSopId!=\"\"'> " +
            "     and o.sop_id = #{qwSopId} " +
            "</if>" +
            "<if test ='videoId !=null'> " +
            "     and o.video_id = #{videoId} " +
            "</if>" +
            "</where>" +
            "GROUP BY o.video_id," +
            "<if test= 'sendType != 1 '> " +
            " o.qw_user_id," +
            "</if>\n" +
            "<if test= 'sendType == 1 '> " +
            " o.company_user_id," +
            "</if>\n" +
            "DATE(o.create_time)\n" +
            "ORDER BY o.video_id ,DATE(o.create_time) " +
            ") t \n"+
            "</script>"})
    List<FsCourseWatchLogStatisticsListVO> selectFsCourseWatchLogStatisticsListVO(FsCourseWatchLogStatisticsListParam param);

    List<FsCourseWatchLogStatisticsListVO> selectFsCourseWatchLogStatisticsListVO_COUNT(FsCourseWatchLogStatisticsListParam param);



    @Select({"<script> " +
            " \tselect \n" +
            "\t'总合计' as qw_user_name,\n" +
            "\tsum(t1.type1) as type1,\n" +
            "\tsum(t1.type2) as type2,\n" +
            "\tsum(t1.type3) as type3,\n" +
            "\tsum(t1.type4) as type4,\n" +
            "\t (\n" +
            "     sum(t1.type1)+\n" +
            "     sum(t1.type2) +\n" +
            "     sum(t1.type4)\n" +
            "   ) AS on_line_num,\n" +
            "\t sum(send_number) as send_number,\n" +
            "\t sum(is_user_wait_number) as is_user_wait_number,\n" +
            "\t sum(no_user_wait_number) as no_user_wait_number,\n" +
            "\t sum(red_amount) as red_amount\n" +
            "\tfrom  ( select t.* " +
            "<if test= 'sendType != 1 '> " +
            " ,concat(round(if(t.send_number=0,0,(t.on_line_num/t.send_number)*100),2),'%') on_line_rate" +
            " ,concat(round(if(t.send_number=0,0,(t.type2/t.send_number)*100),2),'%') finished_rate" +
            "</if> " +
            "from (" +
            "SELECT \n" +
            "o.video_id,o.company_id,o.qw_user_id,DATE(o.create_time) create_time," +
            "<if test= 'sendType != 1 '> " +
            " qu.qw_user_name qw_user_name," +
            "</if>\n" +
            "<if test= 'sendType == 1 '> " +
            " cu.nick_name qw_user_name," +
            "</if>\n" +
            "v.title videoName,uc.course_name,\n" +
            "SUM(CASE WHEN o.log_type = '1' THEN 1 ELSE 0 END) AS type1,\n" +
            "SUM(CASE WHEN o.log_type = '2' THEN 1 ELSE 0 END) AS type2,\n" +
            "SUM(CASE WHEN o.log_type = '3' THEN 1 ELSE 0 END) AS type3,\n" +
            "SUM(CASE WHEN o.log_type = '4' THEN 1 ELSE 0 END) AS type4,\n" +
            "(\n" +
            "  SUM(CASE WHEN o.log_type = '1' THEN 1 ELSE 0 END) +\n" +
            "  SUM(CASE WHEN o.log_type = '2' THEN 1 ELSE 0 END) +\n" +
            "  SUM(CASE WHEN o.log_type = '4' THEN 1 ELSE 0 END)\n" +
            ") AS on_line_num " +
            "<if test= 'sendType != 1 '> " +
            " ,count(o.log_id) send_number" +
            " ,sum(if((o.user_id is not null or o.user_id>0) and o.log_type=3,1,0)) is_user_wait_number" +
            " ,sum(if((o.user_id is null or o.user_id=0) and o.log_type=3,1,0)) no_user_wait_number" +
//            " ,sum(ifnull(fcr.amount,0)) red_amount" +
            ",(SELECT SUM(amount) FROM fs_course_red_packet_log \n" +
            "     WHERE user_id = o.user_id AND video_id = o.video_id) as red_amount " +
            "</if> " +
            "FROM fs_course_watch_log o " +
            "<if test= 'sendType != 1 '> " +
            " LEFT JOIN qw_user qu on qu.id=o.qw_user_id " +
//            " LEFT JOIN fs_course_red_packet_log fcr on o.user_id = fcr.user_id and fcr.video_id = o.video_id" + //会有笛卡尔积问题
            "</if>\n" +
            "LEFT JOIN fs_user_course_video v on v.video_id=o.video_id \n" +
            "LEFT JOIN fs_user_course uc on uc.course_id=v.course_id\n" +
            "<if test= 'sendType == 1 '> " +
            " LEFT JOIN company_user cu on cu.user_id=o.company_user_id\n" +
            "</if>\n" +
            "where o.company_id=#{companyId} " +
            "<if test= 'sendType != null '> " +
            "       and  send_type= #{sendType} " +
            "</if>\n" +
            "<if test= 'sTime != null '> " +
            "       and DATE(o.create_time) &gt;= DATE(#{sTime})\n" +
            "</if>\n" +
            "<if test='eTime != null '> " +
            "      and DATE(o.create_time) &lt;= DATE(#{eTime})\n" +
            "</if>" +
            "<if test ='sendType != 1 and nickName !=null and nickName!=\"\"'>\n" +
            "   and qu.qw_user_name like concat( #{nickName}, '%')\n" +
            "</if>" +
            "<if test ='sendType == 1 and nickName !=null and nickName!=\"\"'>\n" +
            "   and cu.nick_name like concat( #{nickName}, '%')\n" +
            "</if>" +
            "<if test ='courseId !=null'> " +
            "     and o.course_id = #{courseId} " +
            "</if>" +
            "<if test ='videoId !=null'> " +
            "     and o.video_id = #{videoId} " +
            "</if>" +
            "GROUP BY o.video_id," +
            "<if test= 'sendType != 1 '> " +
            " o.qw_user_id," +
            "</if>\n" +
            "<if test= 'sendType == 1 '> " +
            " o.company_user_id," +
            "</if>\n" +
            "DATE(o.create_time)\n" +
            "ORDER BY o.video_id ,DATE(o.create_time) " +
            ") t ) t1 \n"+
            "</script>"})
    FsCourseWatchLogStatisticsListVO getTotalDataAddItem(FsCourseWatchLogStatisticsListParam param);
    @Select({"<script> " +
            "SELECT COUNT(*) FROM (" +
            "    SELECT 1 " +
            "    FROM fs_course_watch_log o " +
            "    <if test= 'sendType != 1 '> " +
            "    LEFT JOIN qw_user qu on qu.id = o.qw_user_id " +
            "    </if> " +
            "    <if test= 'sendType == 1 '> " +
            "    LEFT JOIN company_user cu on cu.user_id = o.company_user_id " +
            "    </if> " +
            "    WHERE o.company_id = #{companyId} " +
            "    <if test= 'sendType != null '> " +
            "        AND o.send_type = #{sendType} " +
            "    </if> " +
            "    <if test= 'sTime != null '> " +
            "        AND o.create_time &gt;= #{sTime} " +
            "    </if> " +
            "    <if test='eTime != null '> " +
            "        AND o.create_time &lt; DATE_ADD(#{eTime}, INTERVAL 1 DAY) " +
            "    </if> " +
            "    <if test ='sendType != 1 and nickName != null and nickName != \"\"'> " +
            "        AND qu.qw_user_name LIKE CONCAT(#{nickName}, '%') " +
            "    </if> " +
            "    <if test ='sendType == 1 and nickName != null and nickName != \"\"'> " +
            "        AND cu.nick_name LIKE CONCAT(#{nickName}, '%') " +
            "    </if> " +
            "    <if test ='courseId != null'> " +
            "        AND o.course_id = #{courseId} " +
            "    </if> " +
            "    <if test ='videoId != null'> " +
            "        AND o.video_id = #{videoId} " +
            "    </if> " +
            "    GROUP BY o.video_id, " +
            "    <if test= 'sendType != 1 '> " +
            "        o.qw_user_id, " +
            "    </if> " +
            "    <if test= 'sendType == 1 '> " +
            "        o.company_user_id, " +
            "    </if> " +
            "    DATE(o.create_time) " +
            ") t " +
            "</script>"})
    Long selectFsCourseWatchLogStatisticsListVOCount(FsCourseWatchLogStatisticsListParam param);


    ArrayList<FsCourseWatchLogByFinishTimeParam> selectFsCourseWatchLogByFinishTime();

    /**
     * 当天完课记录（log_type=2 且 watch_type=3 飞书看课），按主体/企微账号过滤，用于销售端一键发红包
     */
    List<TodayFinishWatchForRedPacketVO> selectTodayFinishWatchForRedPacket(
            @Param("companyId") Long companyId,
            @Param("corpId") String corpId,
            @Param("qwUserId") Long qwUserId,
            @Param("startTime") Date startTime,
            @Param("endTime") Date endTime);

    /**
     * 侧边栏发红包：该客户在该销售下，今天是否有完课（不限制 watch_type；按 watch 表 company_user_id 归属销售）
     */
    TodayFinishWatchForRedPacketVO selectTodayFinishWatchForSidebarSales(
            @Param("companyId") Long companyId,
            @Param("companyUserId") Long companyUserId,
            @Param("qwExternalContactId") Long qwExternalContactId,
            @Param("startTime") Date startTime,
            @Param("endTime") Date endTime);

    void batchUpdateWatchLog(@Param("list") List<FsCourseWatchLog> logs);

    List<FsCourseUserStatisticsListVO> selectFsCourseUserStatisticsListVO(FsCourseUserStatisticsListParam param);

    @Select({"<script> " +
            "select MIN(o.create_time) createTime,ANY_VALUE(qu.qw_user_name) qwUserName ,ext.name externalUserName,ext.avatar," +
            "   ext.create_time userCreateTime from fs_course_watch_log o " +
            "LEFT JOIN qw_user qu on qu.id=o.qw_user_id " +
            "LEFT JOIN qw_external_contact ext ON ext.id =  o.qw_external_contact_id where log_type=2 and o.company_id=#{companyId} " +
            "<if test= 'sTime != null '> " +
            "       and DATE(o.create_time) &gt;= DATE(#{sTime})\n" +
            "</if>\n" +
            "<if test='eTime != null '> " +
            "      and DATE(o.create_time) &lt;= DATE(#{eTime})\n" +
            "</if>" +
            "<if test ='nickName !=null and nickName!=\"\"'>\n" +
            "   and qu.qw_user_name like concat('%', #{nickName}, '%')\n" +
            "</if>"+
            " GROUP BY o.qw_external_contact_id"+
            "</script>"})
    List<FsCourseOverVO> selectFsCourseWatchLogOverStatisticsListVO(FsCourseOverParam param);


    void batchUpdateWatchLogSendMsg(@Param("list") List<FsCourseWatchLog> logs);

    /**
     * 乐观锁：仅当尚未发送完课消息时标记为已发送，返回影响行数（0 表示已被其他任务处理）
     */
    @Update("UPDATE fs_course_watch_log SET send_finish_msg = 1 WHERE log_id = #{logId} AND (send_finish_msg = 0 OR send_finish_msg IS NULL)")
    int casUpdateSendFinishMsg(@Param("logId") Long logId);

    @Select("SELECT l.qw_external_contact_id,l.log_type,l.qw_user_id,l.create_time ,u.first_time,u.create_time lineTime FROM fs_course_watch_log  l " +
            "LEFT JOIN qw_external_contact u ON u.id=l.qw_external_contact_id  " +
            " WHERE  DATE(l.create_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) and l.video_id =#{videoId}")
    List<FsQwCourseWatchLogVO> selectFsCourseWatchLogByNoDayAndVoidId(Long videoId);

    @Select("SELECT l.qw_external_contact_id,l.log_type,l.qw_user_id,l.create_time ,u.first_time,u.create_time lineTime FROM fs_course_watch_log  l " +
            "LEFT JOIN qw_external_contact u ON u.id=l.qw_external_contact_id  " +
            " WHERE  l.create_time between #{start} and #{end} and l.video_id =#{videoId}")
    List<FsQwCourseWatchLogVO> selectFsCourseWatchLogByNoDayAndVoidIdByTime(@Param("videoId") Long videoId, @Param("start") String start, @Param("end") String end);

    @Select("SELECT l.qw_external_contact_id,l.log_type,l.qw_user_id,l.create_time ,u.first_time,u.create_time lineTime FROM fs_course_watch_log  l " +
            "LEFT JOIN qw_external_contact u ON u.id=l.qw_external_contact_id  " +
            " WHERE  DATE(l.create_time) = DATE_SUB(CURDATE(), INTERVAL 2 DAY) and l.video_id =#{videoId}")
    List<FsQwCourseWatchLogVO> selectFsCourseWatchLogByNoDayAndVoidId2(Long videoId);


    @Select("SELECT l.qw_external_contact_id,l.log_type,l.qw_user_id,l.create_time ,u.first_time,u.create_time lineTime FROM fs_course_watch_log  l " +
            "LEFT JOIN qw_external_contact u ON u.id=l.qw_external_contact_id  " +
            " WHERE  DATE(l.create_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) and l.sop_id =#{videoId}")
    List<FsQwCourseWatchLogVO> selectFsCourseWatchLogBySopId(String id);

    @Select("SELECT * FROM fs_course_watch_log  WHERE  DATE(create_time) = CURDATE() and video_id in (select video_id from fs_user_course_video WHERE is_first=1 ) ")
    List<FsQwCourseWatchLogVO> selectFsCourseWatchLogByVideoId();

    @Select("SELECT qw_external_contact_id FROM fs_course_watch_log  WHERE log_type=2 AND create_time >= CURDATE()\n" +
            "  AND create_time < DATE_ADD(CURDATE(), INTERVAL 1 DAY) ")
    List<Long> selectFsCourseWatchLogByFinish();
    @Select("select * from fs_course_watch_log " +
            "where video_id = #{videoId} " +
            "and company_user_id = #{companyUserId} " +
            "and user_id = #{userId} and send_type = 1  order by create_time desc limit 1")
    FsCourseWatchLog getWatchCourseVideoByFsUser(@Param("userId") Long userId, @Param("videoId") Long videoId, @Param("companyUserId") Long companyUserId);

    FsCourseWatchLog getWatchLogByFsUser(@Param("videoId") Long videoId, @Param("fsUserId") Long fsUserId, @Param("companyUserId") Long companyUserId);

    List<FsCourseWatchLogStatisticsListVO> selectFsCourseWatchLogStatisticsListVONew(FsCourseWatchLogStatisticsListParam param);

    long selectFsCourseWatchLogStatisticsListVONewCount(FsCourseWatchLogStatisticsListParam param);
    @Select("WITH date_series AS (\n" +
            "  SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 6-n DAY) AS report_date\n" +
            "  FROM (\n" +
            "    SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 \n" +
            "    UNION SELECT 4 UNION SELECT 5 UNION SELECT 6\n" +
            "  ) days\n" +
            "  ORDER BY n\n" +
            "),\n" +
            "daily_data AS (\n" +
            "  SELECT \n" +
            "    DATE(create_time) AS log_date,\n" +
            "    log_type,\n" +
            "    ROW_NUMBER() OVER (PARTITION BY DATE(create_time) ORDER BY create_time DESC) AS rn\n" +
            "  FROM fs_course_watch_log\n" +
            "  WHERE qw_external_contact_id = #{extId}\n" +
            "    AND create_time >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)\n" +
            ")\n" +
            "SELECT \n" +
            "  IFNULL(dd.log_type, 0) AS log_type\n" +
            "FROM date_series ds\n" +
            "LEFT JOIN (\n" +
            "  SELECT log_date, log_type FROM daily_data WHERE rn = 1\n" +
            ") dd ON ds.report_date = dd.log_date\n" +
            "ORDER BY ds.report_date ASC  ")
    List<Integer> selectFsCourseWatchLog7DayByExtId(Long extId);

    @Select("select l.user_id,l.company_id,l.company_user_id,l.log_type,ext.`level`,l.qw_user_id FROM fs_course_watch_log l LEFT JOIN fs_user ext ON ext.user_id =l.user_id  where l.sop_id=#{SopId} and  date(l.create_time)= CURDATE() and l.log_type =4 and l.video_id not in (select video_id from fs_user_course_video WHERE is_first=1 )")
    List<FsCourseWatchLogTaskVO> selectFsCourseWatchLogByDaySopIdFsUser4(@Param("SopId")String SopId);

    @Select("select l.qw_external_contact_id,l.company_id,l.company_user_id,l.log_type,ext.`level`,l.qw_user_id,ext.last_watch_time FROM fs_course_watch_log l LEFT JOIN qw_external_contact ext ON ext.id =l.qw_external_contact_id  where l.sop_id=#{SopId} and  date(l.create_time)= CURDATE() and l.log_type =3 and l.video_id not in (select video_id from fs_user_course_video WHERE is_first=1 ) and ext.last_watch_time < #{lastTime} and ext.last_watch_time !=0  ")
    List<FsCourseWatchLogTaskVO> selectFsCourseWatchLogByDaySopId3LastTime(@Param("SopId")String SopId,@Param("lastTime")Integer lastTime);

    @Select("SELECT l.qw_external_contact_id,l.project,l.video_id,l.course_id,l.log_type,l.qw_user_id,l.create_time lineTime,l.company_user_id as company_user_id,l.user_id as user_id,l.company_id as company_id FROM fs_course_watch_log l" +
            " WHERE l.send_type=1 AND DATE(l.create_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) and l.video_id =#{videoId}")
    List<FsQwCourseWatchLogVO> selectFsCourseBeforeMonthWatchLogByVideoId(Long videoId);

    @Select("SELECT min(create_time) FROM fs_course_watch_log WHERE user_id=#{userId} limit 1")
    Date queryFirstWatchDateLogByVideoId(Long userId);

    @Select("select  * from fs_course_watch_log where user_id=#{userId} " +
            "and video_id=#{videoId} and qw_user_id=#{qwUserId} limit 1 " )
    FsCourseWatchLog selectFsCourseWatchLogByCourseSopIdAndVideoId(@Param("userId") Long userId,
                                                                   @Param("videoId") Long videoId,
                                                                   @Param("qwUserId") String qwUserId);

    @Select("WITH date_series AS (\n" +
            "  SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 6-n DAY) AS report_date\n" +
            "  FROM (\n" +
            "    SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 \n" +
            "    UNION SELECT 4 UNION SELECT 5 UNION SELECT 6\n" +
            "  ) days\n" +
            "  ORDER BY n\n" +
            "),\n" +
            "daily_data AS (\n" +
            "  SELECT \n" +
            "    DATE(create_time) AS log_date,\n" +
            "    log_type,\n" +
            "    ROW_NUMBER() OVER (PARTITION BY DATE(create_time) ORDER BY create_time DESC) AS rn\n" +
            "  FROM fs_course_watch_log\n" +
            "  WHERE qw_external_contact_id = #{extId}\n" +
            "    AND create_time >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)\n" +
            ")\n" +
            "SELECT \n" +
            "ds.report_date AS date,"+
            "  IFNULL(dd.log_type, 0) AS log_type\n" +
            "FROM date_series ds\n" +
            "LEFT JOIN (\n" +
            "  SELECT log_date, log_type FROM daily_data WHERE rn = 1\n" +
            ") dd ON ds.report_date = dd.log_date\n" +
            "ORDER BY ds.report_date ASC  ")
    List<WatchLogDTO> selectFsCourseWatchLog7Day(@Param("extId") Long extId);

    @Select("WITH date_series AS (\n" +
            "  SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 29-n DAY) AS report_date\n" +
            "  FROM (\n" +
            "    SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 \n" +
            "    UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 \n" +
            "    UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 \n" +
            "    UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 \n" +
            "    UNION SELECT 24 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29\n" +
            "  ) days\n" +
            "  ORDER BY n\n" +
            "),\n" +
            "daily_data AS (\n" +
            "  SELECT \n" +
            "    DATE(create_time) AS log_date,\n" +
            "    log_type,\n" +
            "    ROW_NUMBER() OVER (PARTITION BY DATE(create_time) ORDER BY create_time DESC) AS rn\n" +
            "  FROM fs_course_watch_log\n" +
            "  WHERE qw_external_contact_id = #{extId}\n" +
            "    AND create_time >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)\n" +
            ")\n" +
            "SELECT \n" +
            "  ds.report_date AS date,\n" +
            "  IFNULL(dd.log_type, 0) AS log_type\n" +
            "FROM date_series ds\n" +
            "LEFT JOIN (\n" +
            "  SELECT log_date, log_type FROM daily_data WHERE rn = 1\n" +
            ") dd ON ds.report_date = dd.log_date\n" +
            "ORDER BY ds.report_date ASC")
    List<WatchLogDTO> selectFsCourseWatchLog30DayByExtId(@Param("extId") Long extId);

    @Select("SELECT * FROM fs_course_watch_log " +
            "WHERE log_type = 2 AND send_finish_msg = 0 and send_type = 2 " +
            "AND send_type = 2 and finish_time >= #{startDate} AND finish_time < #{endDate} and log_id > #{maxId} order by log_id asc  " +
            "LIMIT #{limit}")
    List<FsCourseWatchLog> selectFsCourseWatchLogFinishBatchByDate(
            @Param("startDate") Date startDate,
            @Param("endDate") Date endDate,
            @Param("maxId") long  maxId,
            @Param("limit") int limit);

    @Select("select count(log_id) AS watchCount, count(case when log_type = 2 then log_id end) AS finishCount, sum(duration) AS watchTime from fs_course_watch_log where user_id = #{userId} and video_id = #{videoId}")
    Map<String, Object> selectSumByUserIdAndVideoId(@Param("userId") Long userId, @Param("videoId") Long videoId);

    void batchUpdateFsUserWatchLog(@Param("list") List<FsCourseWatchLog> list);

    @Select("<script>" +
            "select * from fs_course_watch_log " +
            "<where>" +
            "   <if test='periodId != null'>and period_id = #{periodId}</if>" +
            "   and send_type = 1" +
            "   and user_id = #{userId}" +
            "   and video_id = #{videoId}" +
            "</where>" +
            "order by log_id desc limit 1" +
            "</script>")
    FsCourseWatchLog getCourseWatchLogByUser(@Param("userId") Long userId,
                                             @Param("videoId") Long videoId,
                                             @Param("periodId") Long periodId);

    /**
     * 根据条件查询条数
     * @param params    参数
     * @return  count
     */
    int countByMap(@Param("params") Map<String, Object> params);


    @Select("<script>" +
            "select qec.id,qec.user_id,qec.external_user_id,qec.corp_id,qec.tag_ids,qec.name from fs_course_watch_log qwl " +
            "left join qw_external_contact qec on qwl.qw_external_contact_id=qec.id " +
            "        where qwl.log_id in\n" +
            "        <foreach collection=\"logIds\" item=\"id\" open=\"(\" separator=\",\" close=\")\">\n" +
            "            #{id}\n" +
            "        </foreach>" +
            "GROUP BY " +
            "    qec.user_id,\n" +
            "    qec.external_user_id,\n" +
            "    qec.corp_id"+
            "</script>")
    List<QwExternalContact> selectQwWatchLogFomExtContact(@Param("logIds") List<Long> logIds);

    List<FsCourseWatchLogListVO> selectListBytrainingCampId(PeriodStatisticCountParam param);
    @Select("select * from fs_course_watch_log " +
            "where " +
            "video_id = #{videoId} " +
            "and user_id = #{userId} limit 1 ")
    FsCourseWatchLog getWatchCourseVideoByUserId(@Param("userId") Long userId,@Param("videoId") Long videoId);

    @Select("select * from fs_course_watch_log " +
            "where " +
            "video_id = #{videoId} " +
            "and user_id = #{userId} limit 1 ")
    FsCourseWatchLog getWatchCourseVideoIsOpen(@Param("userId") Long userId,@Param("videoId") Long videoId);

    void batchUpdateWatchLogIsOpen(@Param("list") List<FsCourseWatchLog> batchList);

    @Select("select count(*) from fs_course_watch_log where user_id =#{userId} and project =#{projectId} ")
    Long selectByWatchLjDay(@Param("userId") Long userId,@Param("projectId")  Long projectId);
    @Select("SELECT IFNULL(MAX(streak), 0) AS current_consecutive_days\n" +
            "FROM (\n" +
            "    SELECT \n" +
            "        watch_date,\n" +
            "        @streak := IF(\n" +
            "            DATEDIFF(@prev_date, watch_date) = 1, \n" +
            "            @streak + 1, \n" +
            "            1\n" +
            "        ) AS streak,\n" +
            "        @prev_date := watch_date\n" +
            "    FROM (\n" +
            "        SELECT DISTINCT DATE(create_time) AS watch_date\n" +
            "        FROM fs_course_watch_log\n" +
            "        WHERE user_id = #{userId} AND project = #{projectId}\n" +
            "        ORDER BY watch_date DESC\n" +
            "    ) AS dates,\n" +
            "    (SELECT @streak := 0, @prev_date := NULL) AS vars\n" +
            ") AS streak_data;")
    Long selectByWatchlxDay(@Param("userId") Long userId,@Param("projectId")  Long projectId);

    List<FsCourseWatchLogListVO> selectFsCourseWatchLogListVOexport(@Param("maps") FsCourseWatchLogListParam param);

    List<FsCourseWatchLog> getWatchCourseByVideoId(@Param("videoId") Long videoId, @Param("userIds") List<Long> userIds);


    /**
     * 查询训练营看课人数
     */
    Integer getUserCountByCampId(@Param("trainingCampId") Long trainingCampId);

    List<FsCourseWatchLogStatisticsListByCompanyVO> selectFsCourseWatchLogStatisticsListByCompanyVO(FsCourseWatchLogStatisticsListParam param);

    /**
     * 看课统计
     * */
    List<FsCourseWatchLogStatisticsListVO> selectQwFsCourseWatchLogStatisticsListVO(@Param("data") QwSidebarStatsParam param);

    // 统计当天各公司的观看人数和完播人数, 存到redis中，定时任务每 ? 分钟执行一次
    List<WatchCourseStatisticsResultDTO> watchCourseStatisticsGroupByCompany(@Param("params") Map<String, Object> params);

    @Select({"<script>" +
            " select qw_external_contact_id from fs_course_watch_log where log_id in  " +
            "        <foreach collection=\"watchLogIds\" item=\"id\" open=\"(\" separator=\",\" close=\")\">\n" +
               "    #{id} " +
               "</foreach>" +
            "</script>"
    })
    List<Long> getExContactIdsIdsByWatchLogIds(@Param("watchLogIds")List<Long> watchLogIds);

    /**
     *  公司基本信息
     * @param param
     * @return
     */
    List<FsCourseReportVO> selectCompanyBaseInfo(FsCourseWatchLogStatisticsListParam param);

    /**
     * 看课信息
     * @param param
     * @return
     */
    List<FsCourseReportVO> selectWatchStatistics(FsCourseWatchLogStatisticsListParam param);


    List<FsCoureseWatchLogVO> selectAppIdList();

    List<FsCoureseWatchLogVO> selectUserIdList(FsCourseWatchLogParam fsCourseWatchLog);

    /**
     * 完播统计：按当前筛选条件汇总全部公司（不分页）
     */
    FsCoureseWatchLogVO selectUserIdListSummary(FsCourseWatchLogParam fsCourseWatchLog);

    /**
     * 查询会员在指定营期和销售下的项目ID列表
     */
    @Select("SELECT DISTINCT project FROM fs_course_watch_log " +
            "WHERE user_id = #{userId} AND period_id = #{periodId} " +
            "AND company_user_id = #{companyUserId} AND company_id = #{companyId} " +
            "AND project IS NOT NULL")
    List<Long> selectProjectIdsByUserPeriodAndCompanyUser(@Param("userId") Long userId,
                                                         @Param("periodId") Long periodId,
                                                         @Param("companyUserId") Long companyUserId,
                                                         @Param("companyId") Long companyId);

    /**
     * 转移会员在源营期下的观看记录到目标营期及目标销售
     */
    @Update("UPDATE fs_course_watch_log SET period_id = #{targetPeriodId}, " +
            "company_user_id = #{targetCompanyUserId}, update_time = NOW() " +
            "WHERE user_id = #{userId} AND period_id = #{sourcePeriodId} " +
            "AND company_user_id = #{sourceCompanyUserId} AND company_id = #{companyId}")
    int transferPeriodWatchLog(@Param("userId") Long userId,
                               @Param("sourcePeriodId") Long sourcePeriodId,
                               @Param("sourceCompanyUserId") Long sourceCompanyUserId,
                               @Param("targetPeriodId") Long targetPeriodId,
                               @Param("targetCompanyUserId") Long targetCompanyUserId,
                               @Param("companyId") Long companyId);
}
