<?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="org.dromara.zjk.mapper.ZjkPointUserMapper">

    <resultMap id="BaseResultMap" type="org.dromara.zjk.domain.ZjkPointUser">
        <result property="id" column="ID" jdbcType="VARCHAR"/>
        <result property="userId" column="user_id" jdbcType="BIGINT"/>
        <result property="expertId" column="expert_id" jdbcType="BIGINT"/>
        <result property="rewardId" column="reward_id" jdbcType="VARCHAR"/>
        <result property="rewardTitle" column="reward_title" jdbcType="VARCHAR"/>
        <result property="pointCount" column="point_count" jdbcType="VARCHAR"/>
        <result property="checkStatus" column="check_status" jdbcType="SMALLINT"/>
        <result property="createDept" column="create_dept" jdbcType="VARCHAR"/>
        <result property="tenantId" column="tenant_id" jdbcType="VARCHAR"/>
        <result property="createBy" column="CREATE_BY" jdbcType="BIGINT"/>
        <result property="createTime" column="CREATE_TIME" jdbcType="TIMESTAMP"/>
        <result property="updateBy" column="UPDATE_BY" jdbcType="BIGINT"/>
        <result property="updateTime" column="UPDATE_TIME" jdbcType="TIMESTAMP"/>
    </resultMap>

    <sql id="Base_Column_List">
        ID,user_id,expert_id,
        reward_id,reward_title,point_count,
        check_status,create_dept,tenant_id,
        CREATE_BY,CREATE_TIME,UPDATE_BY,
        UPDATE_TIME
    </sql>
    <select id="getRankAndPoint" resultType="org.dromara.zjk.domain.vo.PointShowVO">
        select aa.*
        from (SELECT zpu.user_id,
                     zpu.expert_id,
                     zpu.check_status,
                     RANK() OVER (ORDER BY point_count DESC) AS rank,
                     zpu.point_count                         AS points
              FROM zjk_point_user zpu) aa
        WHERE aa.user_id = #{userId}

    </select>
    <select id="getExpertPointRank" resultType="org.dromara.zjk.domain.vo.PointExpertGetVO">
        SELECT aa.*, ze.expert_name, RANK() OVER (ORDER BY points DESC) AS rank
        from (select expert_id, sum(points) points
              from zjk_point
              where CREATE_TIME > #{rangeDay}
              group by expert_id) aa
                 inner JOIN zjk_expert ze on aa.expert_id = ze.expert_id
    </select>
</mapper>
