<?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.lesingle.modules.biz.review.mapper.AnalyticsMapper">

    <sql id="contestIn">
        <foreach collection="contestIds" item="cid" open="(" separator="," close=")">
            #{cid}
        </foreach>
    </sql>

    <select id="selectAvgReviewCycleDays" resultType="java.math.BigDecimal">
        SELECT AVG(day_diff)
        FROM (
            SELECT DATEDIFF(MIN(s.score_time), w.submit_time) AS day_diff
            FROM t_biz_contest_work w
            INNER JOIN t_biz_contest_work_score s ON s.work_id = w.id AND s.deleted = 0
            WHERE w.deleted = 0
              AND w.is_latest = 1
              AND w.valid_state = 1
              AND w.contest_id IN <include refid="contestIn"/>
              AND w.submit_time IS NOT NULL
            <if test="!isSuperTenant and tenantId != null">
              AND w.tenant_id = #{tenantId}
            </if>
            GROUP BY w.id
            HAVING MIN(s.score_time) IS NOT NULL
        ) t
    </select>

    <select id="selectScoreCountLast30Days" resultType="long">
        SELECT COUNT(*)
        FROM t_biz_contest_work_score s
        WHERE s.deleted = 0
          AND s.score_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
          AND s.contest_id IN <include refid="contestIn"/>
        <if test="!isSuperTenant and tenantId != null">
          AND s.tenant_id = #{tenantId}
        </if>
    </select>

    <select id="selectPendingAssignments" resultType="long">
        SELECT COUNT(*)
        FROM t_biz_contest_work_judge_assignment a
        WHERE a.status = 'assigned'
          AND a.contest_id IN <include refid="contestIn"/>
    </select>

    <select id="selectAvgStddevAcrossWorks" resultType="java.math.BigDecimal">
        SELECT AVG(work_std)
        FROM (
            SELECT STDDEV_POP(s.total_score) AS work_std
            FROM t_biz_contest_work_score s
            WHERE s.deleted = 0
              AND s.contest_id IN <include refid="contestIn"/>
            <if test="!isSuperTenant and tenantId != null">
              AND s.tenant_id = #{tenantId}
            </if>
            GROUP BY s.work_id
            HAVING COUNT(*) > 1
        ) x
    </select>

    <select id="selectMonthlyRegistrationCounts" resultType="map">
        SELECT DATE_FORMAT(r.registration_time, '%Y-%m') AS ym, COUNT(*) AS cnt
        FROM t_biz_contest_registration r
        WHERE r.deleted = 0
          AND r.contest_id IN <include refid="contestIn"/>
        <if test="!isSuperTenant and tenantId != null">
          AND r.tenant_id = #{tenantId}
        </if>
          AND r.registration_time >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 5 MONTH)
        GROUP BY DATE_FORMAT(r.registration_time, '%Y-%m')
        ORDER BY ym
    </select>

    <select id="selectMonthlyWorkCounts" resultType="map">
        SELECT DATE_FORMAT(w.submit_time, '%Y-%m') AS ym, COUNT(*) AS cnt
        FROM t_biz_contest_work w
        WHERE w.deleted = 0
          AND w.is_latest = 1
          AND w.valid_state = 1
          AND w.contest_id IN <include refid="contestIn"/>
        <if test="!isSuperTenant and tenantId != null">
          AND w.tenant_id = #{tenantId}
        </if>
          AND w.submit_time IS NOT NULL
          AND w.submit_time >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 5 MONTH)
        GROUP BY DATE_FORMAT(w.submit_time, '%Y-%m')
        ORDER BY ym
    </select>

    <select id="selectAwardDistribution" resultType="map">
        SELECT
            w.award_level AS awardLevel,
            w.award_name AS awardNameRaw,
            COUNT(*) AS cnt
        FROM t_biz_contest_work w
        WHERE w.deleted = 0
          AND w.is_latest = 1
          AND w.valid_state = 1
          AND w.contest_id IN <include refid="contestIn"/>
        <if test="!isSuperTenant and tenantId != null">
          AND w.tenant_id = #{tenantId}
        </if>
          AND w.award_level IS NOT NULL
          AND w.award_level != 'none'
        GROUP BY w.award_level, w.award_name
    </select>

    <select id="selectJudgeWorkload" resultType="map">
        SELECT
            j.judge_id AS judgeId,
            COALESCE(
                (SELECT MAX(sc.judge_name) FROM t_biz_contest_work_score sc
                 WHERE sc.judge_id = j.judge_id AND sc.deleted = 0
                   AND sc.contest_id IN <include refid="contestIn"/>
                <if test="!isSuperTenant and tenantId != null">
                   AND sc.tenant_id = #{tenantId}
                </if>
                ),
                MAX(u.nickname),
                '评委'
            ) AS judgeName,
            COUNT(DISTINCT j.contest_id) AS contestCount,
            (SELECT COUNT(*) FROM t_biz_contest_work_judge_assignment a
             WHERE a.judge_id = j.judge_id AND a.contest_id IN <include refid="contestIn"/>
            ) AS assignedCount,
            (SELECT COUNT(*) FROM t_biz_contest_work_score sc2
             WHERE sc2.judge_id = j.judge_id AND sc2.deleted = 0
               AND sc2.contest_id IN <include refid="contestIn"/>
            <if test="!isSuperTenant and tenantId != null">
               AND sc2.tenant_id = #{tenantId}
            </if>
            ) AS scoredCount,
            (SELECT AVG(sc3.total_score) FROM t_biz_contest_work_score sc3
             WHERE sc3.judge_id = j.judge_id AND sc3.deleted = 0
               AND sc3.contest_id IN <include refid="contestIn"/>
            <if test="!isSuperTenant and tenantId != null">
               AND sc3.tenant_id = #{tenantId}
            </if>
            ) AS avgScore,
            (SELECT STDDEV_POP(sc4.total_score) FROM t_biz_contest_work_score sc4
             WHERE sc4.judge_id = j.judge_id AND sc4.deleted = 0
               AND sc4.contest_id IN <include refid="contestIn"/>
            <if test="!isSuperTenant and tenantId != null">
               AND sc4.tenant_id = #{tenantId}
            </if>
            ) AS scoreStddev
        FROM t_biz_contest_judge j
        LEFT JOIN t_sys_user u ON u.id = j.judge_id AND u.deleted = 0
        WHERE j.contest_id IN <include refid="contestIn"/>
          AND j.deleted = 0
        GROUP BY j.judge_id
        ORDER BY j.judge_id
    </select>

    <!-- 单活动对比行：报名数、通过数、作品数、已评审、获奖、均分 -->
    <select id="selectContestComparisonRow" resultType="map">
        SELECT
            (SELECT COUNT(*) FROM t_biz_contest_registration r
             WHERE r.deleted = 0 AND r.contest_id = #{contestId}
            <if test="!isSuperTenant and tenantId != null">
               AND r.tenant_id = #{tenantId}
            </if>
            <if test="rangeStart != null and rangeEnd != null">
               AND r.registration_time BETWEEN #{rangeStart} AND #{rangeEnd}
            </if>
            ) AS registrations,
            (SELECT COUNT(*) FROM t_biz_contest_registration r
             WHERE r.deleted = 0 AND r.contest_id = #{contestId}
               AND r.registration_state = 'passed'
            <if test="!isSuperTenant and tenantId != null">
               AND r.tenant_id = #{tenantId}
            </if>
            <if test="rangeStart != null and rangeEnd != null">
               AND r.registration_time BETWEEN #{rangeStart} AND #{rangeEnd}
            </if>
            ) AS passedRegs,
            (SELECT COUNT(*) FROM t_biz_contest_work w
             WHERE w.deleted = 0 AND w.is_latest = 1 AND w.valid_state = 1 AND w.contest_id = #{contestId}
            <if test="!isSuperTenant and tenantId != null">
               AND w.tenant_id = #{tenantId}
            </if>
            <if test="rangeStart != null and rangeEnd != null">
               AND w.submit_time BETWEEN #{rangeStart} AND #{rangeEnd}
            </if>
            ) AS workCount,
            (SELECT COUNT(*) FROM t_biz_contest_work w
             WHERE w.deleted = 0 AND w.is_latest = 1 AND w.valid_state = 1 AND w.contest_id = #{contestId}
               AND w.status IN ('accepted', 'awarded')
            <if test="!isSuperTenant and tenantId != null">
               AND w.tenant_id = #{tenantId}
            </if>
            <if test="rangeStart != null and rangeEnd != null">
               AND w.submit_time BETWEEN #{rangeStart} AND #{rangeEnd}
            </if>
            ) AS reviewedCount,
            (SELECT COUNT(*) FROM t_biz_contest_work w
             WHERE w.deleted = 0 AND w.is_latest = 1 AND w.valid_state = 1 AND w.contest_id = #{contestId}
               AND w.award_level IS NOT NULL AND w.award_level != 'none'
            <if test="!isSuperTenant and tenantId != null">
               AND w.tenant_id = #{tenantId}
            </if>
            <if test="rangeStart != null and rangeEnd != null">
               AND w.submit_time BETWEEN #{rangeStart} AND #{rangeEnd}
            </if>
            ) AS awardedCount,
            (SELECT AVG(w.final_score) FROM t_biz_contest_work w
             WHERE w.deleted = 0 AND w.is_latest = 1 AND w.valid_state = 1 AND w.contest_id = #{contestId}
               AND w.final_score IS NOT NULL
            <if test="!isSuperTenant and tenantId != null">
               AND w.tenant_id = #{tenantId}
            </if>
            <if test="rangeStart != null and rangeEnd != null">
               AND w.submit_time BETWEEN #{rangeStart} AND #{rangeEnd}
            </if>
            ) AS avgScore
    </select>

</mapper>
