<?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.bonus.system.mapper.SysIpWhitelistMapper">

    <resultMap type="com.bonus.system.domain.SysIpWhitelist" id="SysIpWhitelistResult">
        <result property="id"    column="id"    />
        <result property="ipAddress"    column="ip_address"    />
        <result property="ipRangeStart"    column="ip_range_start"    />
        <result property="ipRangeEnd"    column="ip_range_end"    />
        <result property="accessStartTime"    column="access_start_time"    />
        <result property="accessEndTime"    column="access_end_time"    />
        <result property="createdAt"    column="created_at"    />
        <result property="updatedAt"    column="updated_at"    />
        <result property="status"    column="status"    />

    </resultMap>

    <sql id="selectSysIpWhitelistVo">
        select id, ip_address, ip_range_start, ip_range_end,status, access_start_time, access_end_time, created_at, updated_at from sys_ip_whitelist
    </sql>

    <select id="selectSysIpWhitelistList" parameterType="com.bonus.system.domain.SysIpWhitelist" resultMap="SysIpWhitelistResult">
        <include refid="selectSysIpWhitelistVo"/>
        where 1=1
            <if test="ipAddress != null  and ipAddress != ''"> and (
                ip_address = #{ipAddress}
                or (INET_ATON(ip_range_start) &lt;= INET_ATON(#{ipAddress})
                AND INET_ATON(#{ipAddress}) &lt;= INET_ATON(ip_range_end))
                )
             </if>
        order by created_at desc
    </select>

    <select id="selectSysIpWhitelistById" parameterType="Long" resultMap="SysIpWhitelistResult">
        <include refid="selectSysIpWhitelistVo"/>
        where id = #{id}
    </select>

    <insert id="insertSysIpWhitelist" parameterType="com.bonus.system.domain.SysIpWhitelist" useGeneratedKeys="true" keyProperty="id">
        insert into sys_ip_whitelist
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="ipAddress != null">ip_address,</if>
            <if test="ipRangeStart != null">ip_range_start,</if>
            <if test="ipRangeEnd != null">ip_range_end,</if>
            <if test="accessStartTime != null">access_start_time,</if>
            <if test="accessEndTime != null">access_end_time,</if>
            <if test="createdAt != null">created_at,</if>
            <if test="updatedAt != null">updated_at,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="ipAddress != null">#{ipAddress},</if>
            <if test="ipRangeStart != null">#{ipRangeStart},</if>
            <if test="ipRangeEnd != null">#{ipRangeEnd},</if>
            <if test="accessStartTime != null">#{accessStartTime},</if>
            <if test="accessEndTime != null">#{accessEndTime},</if>
            <if test="createdAt != null">#{createdAt},</if>
            <if test="updatedAt != null">#{updatedAt},</if>
        </trim>
    </insert>

    <update id="updateSysIpWhitelist" parameterType="com.bonus.system.domain.SysIpWhitelist">
        update sys_ip_whitelist
        <trim prefix="SET" suffixOverrides=",">
            <if test="ipAddress != null">ip_address = #{ipAddress},</if>
            <if test="ipRangeStart != null">ip_range_start = #{ipRangeStart},</if>
            <if test="ipRangeEnd != null">ip_range_end = #{ipRangeEnd},</if>
            access_start_time = #{accessStartTime},
            access_end_time = #{accessEndTime},
            <if test="createdAt != null">created_at = #{createdAt},</if>
            <if test="updatedAt != null">updated_at = #{updatedAt},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteSysIpWhitelistById" parameterType="Long">
        delete from sys_ip_whitelist where id = #{id}
    </delete>

    <delete id="deleteSysIpWhitelistByIds" parameterType="String">
        delete from sys_ip_whitelist where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>


    <update id="updateSysIpWhitelistStatus" parameterType="com.bonus.system.domain.SysIpWhitelist">
        update sys_ip_whitelist
        set status =#{status}
        where id = #{id}
    </update>

    <select id="selectSysIpWhitelist"  resultMap="SysIpWhitelistResult">
        <include refid="selectSysIpWhitelistVo"/>
        where status='0'
        order by created_at desc
    </select>
</mapper>