package com.tourguide.modules.review.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.tourguide.modules.review.entity.OrderReview;
import com.tourguide.modules.review.dto.VisitorReviewStatRow;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.math.BigDecimal;
import java.util.Collection;
import java.util.List;

public interface OrderReviewMapper extends BaseMapper<OrderReview> {
    @Select("SELECT DISTINCT scenic_name FROM tour_order_review WHERE deleted = 0 AND scenic_name IS NOT NULL AND scenic_name <> '' ORDER BY scenic_name")
    List<String> selectScenicOptions();

    @Select("SELECT COUNT(*) FROM tour_order_review WHERE order_id = #{orderId} AND deleted = 0")
    long countByOrderId(Long orderId);

    @Select({
            "<script>",
            "SELECT visitor_id AS visitorId, COUNT(*) AS reviewCount ",
            "FROM tour_order_review WHERE deleted = 0 AND status = 1 AND visitor_id IN ",
            "<foreach collection='visitorIds' item='visitorId' open='(' separator=',' close=')'>#{visitorId}</foreach>",
            "GROUP BY visitor_id",
            "</script>"
    })
    List<VisitorReviewStatRow> selectVisitorReviewStats(@Param("visitorIds") Collection<Long> visitorIds);

    @Select("SELECT COALESCE(AVG(score), 0) FROM tour_order_review WHERE guide_id = #{guideId} AND status = 1 AND deleted = 0")
    BigDecimal selectAverageScore(Long guideId);
}