<?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.liquidnet.service.goblin.mapper.GoblinNftExCodeMapper">

    <resultMap id="BeseResult" type="com.liquidnet.service.goblin.entity.GoblinNftExCode">
        <result column="code_id" property="codeId" jdbcType="VARCHAR"/>
        <result column="activity_id" property="activityId" jdbcType="VARCHAR"/>
        <result column="code" property="code" jdbcType="VARCHAR"/>
        <result column="sku_id" property="skuId" jdbcType="VARCHAR"/>
        <result column="box_sku_id" property="boxSkuId" jdbcType="VARCHAR"/>
        <result column="state" property="state" jdbcType="INTEGER"/>
        <result column="redeem_uid" property="redeemUid" jdbcType="VARCHAR"/>
        <result column="redeem_at" property="redeemAt" jdbcType="TIMESTAMP"/>
        <result column="admin_uid" property="adminUid" jdbcType="VARCHAR"/>
        <result column="created_at" property="createdAt" jdbcType="TIMESTAMP"/>
        <result column="updated_at" property="updatedAt" jdbcType="TIMESTAMP"/>
    </resultMap>

    <select id="selectByIds" resultMap="BeseResult">
        select code_id,activity_id,sku_id,box_sku_id,state,code from goblin_nft_ex_code
        where code_id in
        <foreach collection="codeIds.split(',')" item="codeId" open="(" separator="," close=")">
            #{codeId,jdbcType=VARCHAR}
        </foreach>
    </select>

    <select id="selectGoblinNftCodeByCode" resultMap="BeseResult">
        select code_id, activity_id, code, box_sku_id, state
        from goblin_nft_ex_code
        where code = #{code}
    </select>

    <insert id="addGoblinNftExCodes">
        insert into goblin_nft_ex_code (code_id,activity_id,code,sku_id,box_sku_id,state,created_at)
        values
        <foreach collection="goblinNftExCodes" item="goblinNftExCode" separator=",">
            (
            #{goblinNftExCode.codeId},
            #{goblinNftExCode.activityId},
            #{goblinNftExCode.code},
            #{goblinNftExCode.skuId},
            #{goblinNftExCode.boxSkuId},
            #{goblinNftExCode.state},
            #{goblinNftExCode.createdAt}
            )
        </foreach>
    </insert>

    <update id="updateCodes">
        update goblin_nft_ex_code
        <set>
            state = 3 , updated_at = now()
        </set>
        where code_id in
        <foreach collection="goblinNftExCodes" item="goblinNftExCode" index="index" open="(" separator="," close=")">
            #{goblinNftExCode.codeId}
        </foreach>
    </update>


    <update id="updateCodeAdminUid" parameterType="com.liquidnet.service.goblin.entity.GoblinNftExCode">
        update goblin_nft_ex_code
        <set>
            admin_uid = #{adminUid}
        </set>
        where code_id = #{codeId}
    </update>

    <update id="updateCodeAdminUids">
        update goblin_nft_ex_code
        <set>
            admin_uid = #{adminUid}
        </set>
        where code in
        <foreach collection="codes.split(',')" item="code" open="(" separator="," close=")">
            #{code}
        </foreach>
    </update>

    <select id="selectGoblinNftCode" parameterType="com.liquidnet.service.goblin.entity.GoblinNftExCode"
            resultMap="BeseResult">
        select
        DISTINCT
        gnec.code,gnec.code_id,gnec.sku_id,gnec.box_sku_id,gnec.activity_id,gnec.state,gnec.redeem_uid,gnec.redeem_at,gnec.admin_uid,gnec.created_at
        from goblin_nft_ex_code gnec inner join goblin_nft_ex_sku gnes on gnec.sku_id = gnes.sku_id and gnes.activity_id
        = gnec.activity_id
        <where>
            <if test="code != null and code != ''">
                and gnec.code = #{code,jdbcType=VARCHAR}
            </if>
            <if test="activityId != null and activityId != ''">
                and gnec.activity_id = #{activityId,jdbcType=VARCHAR}
            </if>
            <if test="state != null">
                <choose>
                    <when test="state == 1">
                        and (now() BETWEEN gnes.ex_start_time and gnes.ex_stop_time) and
                        gnec.state =1
                    </when>
                    <when test="state == 2">
                        and gnec.state = 2
                    </when>
                    <when test="state == 3">
                        and (((now() &gt; gnes.ex_stop_time) AND gnec.state = 1) ||
                        gnec.state = 3)
                    </when>
                </choose>
            </if>
            <if test="redeemUid != null and redeemUid != ''">
                and gnec.redeem_uid = #{redeemUid,jdbcType=VARCHAR}
            </if>
            <if test="skuId != null and skuId != ''">
                and gnec.sku_id in
                <foreach collection="skuId.split(',')" item="sId" open="(" separator="," close=")">
                    #{sId,jdbcType=VARCHAR}
                </foreach>
            </if>
            <if test="isDrivi != null">
                <choose>
                    <when test="isDrivi == 1">
                        and gnec.admin_uid = ''
                    </when>
                    <when test="isDrivi == 2">
                        and gnec.admin_uid != ''
                    </when>
                </choose>
            </if>
        </where>

    </select>

    <select id="selectGoblinNftCodeExcel" parameterType="com.liquidnet.service.goblin.entity.GoblinNftExCode"
            resultMap="BeseResult">
        select
        DISTINCT
        gnec.code,gnec.code_id,gnec.sku_id,gnec.box_sku_id,gnec.activity_id,gnec.state,gnec.redeem_uid,gnec.redeem_at,gnec.admin_uid,gnec.created_at
        from goblin_nft_ex_code gnec inner join goblin_nft_ex_sku gnes on gnec.sku_id = gnes.sku_id and gnes.activity_id
        = gnec.activity_id
        <where>
            <if test="code != null and code != ''">
                and gnec.code = #{code,jdbcType=VARCHAR}
            </if>
            <if test="activityId != null and activityId != ''">
                and gnec.activity_id = #{activityId,jdbcType=VARCHAR}
            </if>
            <if test="state != null">
                <choose>
                    <when test="state == 1">
                        and (now() BETWEEN gnes.ex_start_time and gnes.ex_stop_time) and
                        gnec.state =1
                    </when>
                    <when test="state == 2">
                        and gnec.state = 2
                    </when>
                    <when test="state == 3">
                        and (((now() &gt; gnes.ex_stop_time) AND gnec.state = 1) ||
                        gnec.state = 3)
                    </when>
                </choose>
            </if>
            <if test="redeemUid != null and redeemUid != ''">
                and gnec.redeem_uid = #{redeemUid,jdbcType=VARCHAR}
            </if>
            <if test="skuId != null and skuId != ''">
                and gnec.sku_id in
                <foreach collection="skuId.split(',')" item="sId" open="(" separator="," close=")">
                    #{sId,jdbcType=VARCHAR}
                </foreach>
            </if>
            <if test="isDrivi != null">
                <choose>
                    <when test="isDrivi == 1">
                        and gnec.admin_uid = ''
                    </when>
                    <when test="isDrivi == 2">
                        and gnec.admin_uid != ''
                    </when>
                </choose>
            </if>
        </where>
        ORDER BY gnec.code desc
    </select>

    <select id="selectCodeAvailableByActivityId" resultMap="BeseResult">
        select DISTINCT gnec.code,
                        gnec.code_id,
                        gnec.sku_id,
                        gnec.box_sku_id,
                        gnec.activity_id,
                        gnec.state,
                        gnec.redeem_uid,
                        gnec.redeem_at,
                        gnec.admin_uid,
                        gnec.created_at
        from goblin_nft_ex_code gnec
                 inner join goblin_nft_ex_sku gnes
                            on gnec.sku_id = gnes.sku_id and gnec.activity_id = #{activityId,jdbcType=VARCHAR} and
                               gnec.state = 1 and (now() BETWEEN gnes.ex_start_time and gnes.ex_stop_time)
    </select>

    <select id="selectGoblinNftCodeCount" parameterType="com.liquidnet.service.goblin.entity.GoblinNftExCode"
            resultType="java.lang.Integer">
        select count(DISTINCT gnec.code)
        from goblin_nft_ex_code gnec inner join goblin_nft_ex_sku gnes
        on
        gnec.sku_id = gnes.sku_id and gnes.activity_id = gnec.activity_id
        <where>
            <if test="code != null and code != ''">
                and gnec.code = #{code,jdbcType=VARCHAR}
            </if>
            <if test="activityId != null and activityId != ''">
                and gnec.activity_id = #{activityId,jdbcType=VARCHAR}
            </if>
            <if test="state != null">
                <choose>
                    <when test="state == 1">
                        and gnec.sku_id = gnes.sku_id and (now() BETWEEN gnes.ex_start_time and gnes.ex_stop_time) and
                        gnec.state =1
                    </when>
                    <when test="state == 2">
                        and gnec.state = 2
                    </when>
                    <when test="state == 3">
                        and gnec.sku_id = gnes.sku_id and (((now() &gt; gnes.ex_stop_time) AND gnec.state = 1) ||
                        gnec.state = 3)
                    </when>
                </choose>
            </if>
            <if test="redeemUid != null and redeemUid != ''">
                and gnec.redeem_uid = #{redeemUid,jdbcType=VARCHAR}
            </if>
            <if test="skuId != null and skuId != ''">
                and gnec.sku_id in
                <foreach collection="skuId.split(',')" item="sId" open="(" separator="," close=")">
                    #{sId,jdbcType=VARCHAR}
                </foreach>
            </if>
            <if test="isDrivi != null">
                <choose>
                    <when test="isDrivi == 1">
                        and gnec.admin_uid = ''
                    </when>
                    <when test="isDrivi == 2">
                        and gnec.admin_uid != ''
                    </when>
                </choose>
            </if>
        </where>

    </select>

    <select id="selectByActivityIds" resultMap="BeseResult">
        select code_id,sku_id,box_sku_id,activity_id,state from goblin_nft_ex_code where
        activity_id in
        <foreach collection="activityIds.split(',')" item="activityId" open="(" close=")" separator=",">
            #{activityId,jdbcType=VARCHAR}
        </foreach>
    </select>

</mapper>
