package cn.lili.modules.member.mapper;

import cn.lili.modules.member.entity.dos.FootPrint;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 浏览历史数据处理层
 *
 * @author Chopper
 * @since 2020-02-25 14:10:16
 */
public interface FootprintMapper extends BaseMapper<FootPrint> {
    /**
     * 删除超过100条后的记录
     *
     * @param memberId 会员ID
     */
    @Delete("DELETE li_foot_print " +
            "FROM li_foot_print " +
            "LEFT JOIN ( " +
            "  SELECT id " +
            "  FROM ( " +
            "    SELECT id " +
            "    FROM li_foot_print " +
            "    WHERE member_id = ${memberId} " +
            "    ORDER BY create_time DESC " +
            "    LIMIT 100 " +
            "  ) AS keep " +
            ") AS latest_footprints " +
            "ON li_foot_print.id = latest_footprints.id " +
            "WHERE li_foot_print.member_id = ${memberId} AND latest_footprints.id IS NULL; ")
    void deleteLastFootPrint(String memberId);


    @Select("SELECT " +
            "lfp.ref_id AS GoodsId, " +
            "lg.goods_name AS GoodsName, "+
            "COUNT(*) AS view_count " +
            "FROM li_foot_print lfp " +
            "LEFT JOIN li_goods lg ON lfp.ref_id = lg.id " +
            "WHERE lfp.delete_flag = 0 " +
            "AND lfp.create_time BETWEEN #{startTime} AND #{endTime} " +
            "AND lfp.view_type = 'goods' " +
            "AND lg.goods_name is not null " +
            "GROUP BY lfp.ref_id " +  // 按商品ID分组
            "ORDER BY view_count DESC " +
            "LIMIT #{currentLimit}"
            )
    List<Map<String,Object>> selectViewAndCompletionRateCountByDay(Date startTime,
                                                                   Date endTime,
                                                                   Integer currentLimit);

    /**
     * 按视频维度统计：每个视频的总浏览数和完播率
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 包含视频ID、名称、总浏览数、完播率的列表
     */
    @Select({
            "SELECT",
            "  lfp.ref_id AS video_id, " +
            "  lmk.title, " +
            "  COUNT(*) AS total_views, " +
            "  ROUND(" +
                    "    IF(COUNT(*) = 0, 0," +
                    "      (SUM(" +
                    "          CASE WHEN ( " +
                    "            CAST(lfp.play_at AS DECIMAL(10,3)) >= CAST(lmk.video_duration AS DECIMAL(10,3)) " +
                    "            OR " +
                    "            CAST(lfp.play_at AS DECIMAL(10,3)) / CAST(lmk.video_duration AS DECIMAL(10,3)) > 0.9 " +
                    "          ) THEN 1  " +
                    "          ELSE 0 " +
                    "        END " +
                    "      ) / COUNT(*)) * 100  " +
                    "    ), 2  " +
                    "  ) AS complete_rate " +
            "FROM li_foot_print lfp" +
            "  LEFT JOIN lmk_video lmk ON lfp.ref_id = lmk.id " +  // 按实际关联字段调整
            "WHERE " +
            "  lfp.delete_flag = 0",
            "  AND lfp.view_type = 'video' " +// 只统计视频类型
            "  AND lfp.create_time BETWEEN #{startTime} AND #{endTime} " +
            "  AND lmk.video_duration > 0 " +
            "  AND ref_id IS NOT NULL AND lmk.title IS NOT NULL "+
            "GROUP BY lfp.ref_id "+
            "ORDER BY total_views DESC " +
            "LIMIT #{currentLimit}"
    })
    List<Map<String, Object>> selectEachVideoStats(
             Date startTime,
            Date endTime,
            Integer currentLimit
    );

}