<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.rj.mapper.CpaBrandScoreStatisticsMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.rj.entity.CpaBrandScoreStatistics">
        <id column="id" property="id" />
        <result column="dealer_code" property="dealerCode" />
        <result column="dealer_name" property="dealerName" />
        <result column="big_area" property="bigArea" />
        <result column="statistics_date" property="statisticsDate" />
        <result column="statistics_type" property="statisticsType" />
        <result column="total_records" property="totalRecords" />
        <result column="total_score" property="totalScore" />
        <result column="average_score_percentage" property="averageScorePercentage" />
        <result column="positive_count" property="positiveCount" />
        <result column="neutral_count" property="neutralCount" />
        <result column="negative_count" property="negativeCount" />
        <result column="positive_rate" property="positiveRate" />
        <result column="neutral_rate" property="neutralRate" />
        <result column="negative_rate" property="negativeRate" />
        <result column="earliest_interaction" property="earliestInteraction" />
        <result column="latest_interaction" property="latestInteraction" />
        <result column="created_at" property="createdAt" />
        <result column="created_by" property="createdBy" />
        <result column="updated_at" property="updatedAt" />
        <result column="updated_by" property="updatedBy" />
        <result column="is_deleted" property="isDeleted" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, dealer_code, dealer_name, big_area, statistics_date, statistics_type,
        total_records, total_score, average_score_percentage,
        positive_count, neutral_count, negative_count,
        positive_rate, neutral_rate, negative_rate,
        earliest_interaction, latest_interaction,
        created_at, created_by, updated_at, updated_by, is_deleted
    </sql>

    <!-- 根据经销商编码和统计类型查询统计数据 -->
    <select id="selectByDealerCodeAndType" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List" />
        FROM cpa_brandcore_statistics
        WHERE is_deleted = 0
        <if test="dealerCode != null and dealerCode != ''">
            AND dealer_code = #{dealerCode}
        </if>
        <if test="statisticsType != null and statisticsType != ''">
            AND statistics_type = #{statisticsType}
        </if>
        ORDER BY statistics_date DESC, average_score_percentage DESC
    </select>

    <!-- 根据大区和统计类型查询统计数据 -->
    <select id="selectByBigAreaAndType" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List" />
        FROM cpa_brandcore_statistics
        WHERE is_deleted = 0
        <if test="bigArea != null and bigArea != ''">
            AND big_area = #{bigArea}
        </if>
        <if test="statisticsType != null and statisticsType != ''">
            AND statistics_type = #{statisticsType}
        </if>
        ORDER BY statistics_date DESC, average_score_percentage DESC
    </select>

    <!-- 根据日期范围查询统计数据 -->
    <select id="selectByDateRange" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List" />
        FROM cpa_brandcore_statistics
        WHERE is_deleted = 0
        <if test="startDate != null">
            AND statistics_date >= #{startDate}
        </if>
        <if test="endDate != null">
            AND statistics_date &lt;= #{endDate}
        </if>
        <if test="statisticsType != null and statisticsType != ''">
            AND statistics_type = #{statisticsType}
        </if>
        ORDER BY statistics_date DESC, average_score_percentage DESC
    </select>

    <!-- 获取经销商排名统计 -->
    <select id="selectDealerRanking" resultType="java.util.Map">
        SELECT 
            dealer_code,
            dealer_name,
            big_area,
            SUM(total_records) as total_records,
            SUM(total_score) as total_score,
            ROUND(AVG(average_score_percentage), 2) as avg_score_percentage,
            SUM(positive_count) as total_positive_count,
            SUM(neutral_count) as total_neutral_count,
            SUM(negative_count) as total_negative_count,
            ROUND(SUM(positive_count) / SUM(total_records) * 100, 2) as avg_positive_rate
        FROM cpa_brandcore_statistics
        WHERE is_deleted = 0
        <if test="statisticsType != null and statisticsType != ''">
            AND statistics_type = #{statisticsType}
        </if>
        GROUP BY dealer_code, dealer_name, big_area
        ORDER BY avg_score_percentage DESC, total_score DESC
        <if test="limit != null and limit > 0">
            LIMIT #{limit}
        </if>
    </select>

    <!-- 获取大区排名统计 -->
    <select id="selectBigAreaRanking" resultType="java.util.Map">
        SELECT 
            big_area,
            COUNT(DISTINCT dealer_code) as dealer_count,
            SUM(total_records) as total_records,
            SUM(total_score) as total_score,
            ROUND(AVG(average_score_percentage), 2) as avg_score_percentage,
            SUM(positive_count) as total_positive_count,
            SUM(neutral_count) as total_neutral_count,
            SUM(negative_count) as total_negative_count,
            ROUND(SUM(positive_count) / SUM(total_records) * 100, 2) as avg_positive_rate
        FROM cpa_brandcore_statistics
        WHERE is_deleted = 0
        <if test="statisticsType != null and statisticsType != ''">
            AND statistics_type = #{statisticsType}
        </if>
        GROUP BY big_area
        ORDER BY avg_score_percentage DESC, total_score DESC
    </select>

    <!-- 批量插入统计数据 -->
    <insert id="batchInsert" parameterType="java.util.List">
        INSERT INTO cpa_brandcore_statistics (
            id, dealer_code, dealer_name, big_area, statistics_date, statistics_type,
            total_records, total_score, average_score_percentage,
            positive_count, neutral_count, negative_count,
            positive_rate, neutral_rate, negative_rate,
            earliest_interaction, latest_interaction,
            created_at, created_by, updated_at, updated_by, is_deleted
        ) VALUES
        <foreach collection="statisticsList" item="item" separator=",">
            (
                #{item.id}, #{item.dealerCode}, #{item.dealerName}, #{item.bigArea}, 
                #{item.statisticsDate}, #{item.statisticsType},
                #{item.totalRecords}, #{item.totalScore}, #{item.averageScorePercentage},
                #{item.positiveCount}, #{item.neutralCount}, #{item.negativeCount},
                #{item.positiveRate}, #{item.neutralRate}, #{item.negativeRate},
                #{item.earliestInteraction}, #{item.latestInteraction},
                #{item.createdAt}, #{item.createdBy}, #{item.updatedAt}, #{item.updatedBy}, #{item.isDeleted}
            )
        </foreach>
        ON DUPLICATE KEY UPDATE
            total_records = VALUES(total_records),
            total_score = VALUES(total_score),
            average_score_percentage = VALUES(average_score_percentage),
            positive_count = VALUES(positive_count),
            neutral_count = VALUES(neutral_count),
            negative_count = VALUES(negative_count),
            positive_rate = VALUES(positive_rate),
            neutral_rate = VALUES(neutral_rate),
            negative_rate = VALUES(negative_rate),
            earliest_interaction = VALUES(earliest_interaction),
            latest_interaction = VALUES(latest_interaction),
            updated_at = VALUES(updated_at),
            updated_by = VALUES(updated_by)
    </insert>

    <!-- 根据条件删除统计数据 -->
    <delete id="deleteByCondition">
        DELETE FROM cpa_brandcore_statistics
        WHERE is_deleted = 0
        <if test="dealerCode != null and dealerCode != ''">
            AND dealer_code = #{dealerCode}
        </if>
        <if test="statisticsType != null and statisticsType != ''">
            AND statistics_type = #{statisticsType}
        </if>
        <if test="startDate != null">
            AND statistics_date >= #{startDate}
        </if>
        <if test="endDate != null">
            AND statistics_date &lt;= #{endDate}
        </if>
    </delete>

    <!-- 执行日统计SQL（方案1：按经销商和日期双重分组统计） -->
    <select id="executeDailyStatistics" resultType="java.util.Map">
        SELECT 
            dealer_code,
            dealer_name,
            big_area,
            DATE(interaction_date) as statistics_date,
            COUNT(*) as daily_records,
            -- 得分统计
            SUM(CASE 
                WHEN entity_brand = '积极' THEN 2
                WHEN entity_brand = '中性' THEN 1
                WHEN entity_brand = '消极' THEN 0
                ELSE 0
            END) as daily_total_score,
            ROUND(SUM(CASE 
                WHEN entity_brand = '积极' THEN 2
                WHEN entity_brand = '中性' THEN 1
                WHEN entity_brand = '消极' THEN 0
                ELSE 0
            END) / COUNT(*) * 100, 2) as daily_average_score_percentage,
            -- 情感分布统计
            SUM(CASE WHEN entity_brand = '积极' THEN 1 ELSE 0 END) as daily_positive_count,
            SUM(CASE WHEN entity_brand = '中性' THEN 1 ELSE 0 END) as daily_neutral_count,
            SUM(CASE WHEN entity_brand = '消极' THEN 1 ELSE 0 END) as daily_negative_count,
            -- 百分比统计
            ROUND(SUM(CASE WHEN entity_brand = '积极' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as daily_positive_rate,
            ROUND(SUM(CASE WHEN entity_brand = '中性' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as daily_neutral_rate,
            ROUND(SUM(CASE WHEN entity_brand = '消极' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as daily_negative_rate,
            -- 时间范围信息
            MIN(interaction_date) as daily_earliest_time,
            MAX(interaction_date) as daily_latest_time
        FROM customer_profile_analysis 
        WHERE is_deleted = 0 
            AND entity_brand IS NOT NULL 
            AND entity_brand != ''
            AND interaction_date >= #{startDateTime}
            AND interaction_date &lt;= #{endDateTime}
        GROUP BY dealer_code, dealer_name, big_area, DATE(interaction_date)
        ORDER BY statistics_date DESC, daily_total_score DESC, daily_average_score_percentage DESC
    </select>

    <!-- 执行日期范围统计SQL -->
    <select id="executeDateRangeStatistics" resultType="java.util.Map">
        SELECT 
            dealer_code,
            dealer_name,
            big_area,
            COUNT(*) as daily_records,
            -- 得分统计
            SUM(CASE 
                WHEN entity_brand = '积极' THEN 2
                WHEN entity_brand = '中性' THEN 1
                WHEN entity_brand = '消极' THEN 0
                ELSE 0
            END) as daily_total_score,
            ROUND(SUM(CASE 
                WHEN entity_brand = '积极' THEN 2
                WHEN entity_brand = '中性' THEN 1
                WHEN entity_brand = '消极' THEN 0
                ELSE 0
            END) / COUNT(*) * 100, 2) as daily_average_score_percentage,
            -- 情感分布统计
            SUM(CASE WHEN entity_brand = '积极' THEN 1 ELSE 0 END) as daily_positive_count,
            SUM(CASE WHEN entity_brand = '中性' THEN 1 ELSE 0 END) as daily_neutral_count,
            SUM(CASE WHEN entity_brand = '消极' THEN 1 ELSE 0 END) as daily_negative_count,
            -- 百分比统计
            ROUND(SUM(CASE WHEN entity_brand = '积极' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as daily_positive_rate,
            ROUND(SUM(CASE WHEN entity_brand = '中性' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as daily_neutral_rate,
            ROUND(SUM(CASE WHEN entity_brand = '消极' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as daily_negative_rate,
            -- 时间范围信息
            MIN(interaction_date) as daily_earliest_time,
            MAX(interaction_date) as daily_latest_time
        FROM customer_profile_analysis 
        WHERE is_deleted = 0 
            AND entity_brand IS NOT NULL 
            AND entity_brand != ''
            AND interaction_date >= #{startDateTime}
            AND interaction_date &lt;= #{endDateTime}
        GROUP BY dealer_code, dealer_name, big_area
        ORDER BY daily_total_score DESC, daily_average_score_percentage DESC
    </select>

    <!-- 执行总计统计SQL -->
    <select id="executeTotalStatistics" resultType="java.util.Map">
        SELECT 
            dealer_code,
            dealer_name,
            big_area,
            COUNT(*) as daily_records,
            -- 得分统计
            SUM(CASE 
                WHEN entity_brand = '积极' THEN 2
                WHEN entity_brand = '中性' THEN 1
                WHEN entity_brand = '消极' THEN 0
                ELSE 0
            END) as daily_total_score,
            ROUND(SUM(CASE 
                WHEN entity_brand = '积极' THEN 2
                WHEN entity_brand = '中性' THEN 1
                WHEN entity_brand = '消极' THEN 0
                ELSE 0
            END) / COUNT(*) * 100, 2) as daily_average_score_percentage,
            -- 情感分布统计
            SUM(CASE WHEN entity_brand = '积极' THEN 1 ELSE 0 END) as daily_positive_count,
            SUM(CASE WHEN entity_brand = '中性' THEN 1 ELSE 0 END) as daily_neutral_count,
            SUM(CASE WHEN entity_brand = '消极' THEN 1 ELSE 0 END) as daily_negative_count,
            -- 百分比统计
            ROUND(SUM(CASE WHEN entity_brand = '积极' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as daily_positive_rate,
            ROUND(SUM(CASE WHEN entity_brand = '中性' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as daily_neutral_rate,
            ROUND(SUM(CASE WHEN entity_brand = '消极' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as daily_negative_rate,
            -- 时间范围信息
            MIN(interaction_date) as daily_earliest_time,
            MAX(interaction_date) as daily_latest_time
        FROM customer_profile_analysis 
        WHERE is_deleted = 0 
            AND entity_brand IS NOT NULL 
            AND entity_brand != ''
        GROUP BY dealer_code, dealer_name, big_area
        ORDER BY daily_total_score DESC, daily_average_score_percentage DESC
    </select>

</mapper>