<?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.common.mapper.DeviceUnknownMapper">

    <!-- 通用查询结果映射 -->
    <resultMap id="BaseResultMap" type="com.common.entity.DeviceUnknown">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="created_at" property="createdAt" jdbcType="TIMESTAMP"/>
        <result column="device_collect_id" property="deviceCollectId" jdbcType="INTEGER"/>
        <result column="device_collect_name" property="deviceCollectName" jdbcType="VARCHAR"/>
        <result column="device_ip" property="deviceIp" jdbcType="VARCHAR"/>
        <result column="first_time" property="firstTime" jdbcType="TIMESTAMP"/>
        <result column="last_time" property="lastTime" jdbcType="TIMESTAMP"/>
        <result column="organization_id" property="organizationId" jdbcType="INTEGER"/>
        <result column="network_protocol" property="networkProtocol" jdbcType="VARCHAR"/>
        <result column="source_method" property="sourceMethod" jdbcType="VARCHAR"/>
    </resultMap>

    <!-- 插入设备记录 -->
    <insert id="insert" parameterType="com.common.entity.DeviceUnknown"
            useGeneratedKeys="true" keyProperty="id" keyColumn="id">
        INSERT INTO device_unknown (
            created_at,
            device_collect_id,
            device_collect_name,
            device_ip,
            first_time,
            last_time,
            organization_id,
            network_protocol,
            source_method
        ) VALUES (
            COALESCE(#{createdAt}, NOW() AT TIME ZONE 'utc'),
            #{deviceCollectId},
            #{deviceCollectName},
            #{deviceIp}::inet,
            #{firstTime},
            #{lastTime},
            #{organizationId},
            #{networkProtocol},
            #{sourceMethod}
        )
    </insert>

    <!-- 批量插入设备记录 -->
    <insert id="batchInsert" parameterType="java.util.List">
        INSERT INTO device_unknown (
        created_at,
        device_collect_id,
        device_collect_name,
        device_ip,
        first_time,
        last_time,
        organization_id,
        network_protocol,
        source_method
        ) VALUES
        <foreach collection="devices" item="device" separator=",">
            (
            COALESCE(#{device.createdAt}, NOW() AT TIME ZONE 'utc'),
            #{device.deviceCollectId},
            #{device.deviceCollectName},
            #{device.deviceIp}::inet,
            #{device.firstTime},
            #{device.lastTime},
            #{device.organizationId},
            #{device.networkProtocol},
            #{device.sourceMethod}
            )
        </foreach>
    </insert>

    <!-- 根据ID查询设备 -->
    <select id="selectById" resultMap="BaseResultMap">
        SELECT
            id,
            created_at,
            device_collect_id,
            device_collect_name,
            device_ip::text as device_ip,
            first_time,
            last_time,
            organization_id,
            network_protocol,
            source_method
        FROM device_unknown
        WHERE id = #{id}
    </select>

    <!-- 根据IP查询设备 -->
    <select id="selectByIp" resultMap="BaseResultMap">
        SELECT
            id,
            created_at,
            device_collect_id,
            device_collect_name,
            device_ip::text as device_ip,
            first_time,
            last_time,
            organization_id,
            network_protocol,
            source_method
        FROM device_unknown
        WHERE device_ip = #{deviceIp}::inet
        ORDER BY last_time DESC
    </select>

    <!-- 根据组织ID查询设备 -->
    <select id="selectByOrganizationId" resultMap="BaseResultMap">
        SELECT
            id,
            created_at,
            device_collect_id,
            device_collect_name,
            device_ip::text as device_ip,
            first_time,
            last_time,
            organization_id,
            network_protocol,
            source_method
        FROM device_unknown
        WHERE organization_id = #{organizationId}
        ORDER BY last_time DESC
    </select>

    <!-- 查询所有设备 -->
    <select id="selectAll" resultMap="BaseResultMap">
        SELECT
            id,
            created_at,
            device_collect_id,
            device_collect_name,
            device_ip::text as device_ip,
            first_time,
            last_time,
            organization_id,
            network_protocol,
            source_method
        FROM device_unknown
        ORDER BY last_time DESC
    </select>

    <!-- 分页查询设备 -->
    <select id="selectPage" resultMap="BaseResultMap">
        SELECT
            id,
            created_at,
            device_collect_id,
            device_collect_name,
            device_ip::text as device_ip,
            first_time,
            last_time,
            organization_id,
            network_protocol,
            source_method
        FROM device_unknown
        ORDER BY last_time DESC
        LIMIT #{limit} OFFSET #{offset}
    </select>

    <!-- 根据条件查询设备 -->
    <select id="selectByCondition" parameterType="com.common.entity.DeviceUnknown"
            resultMap="BaseResultMap">
        SELECT
        id,
        created_at,
        device_collect_id,
        device_collect_name,
        device_ip::text as device_ip,
        first_time,
        last_time,
        organization_id,
        network_protocol,
        source_method
        FROM device_unknown
        <where>
            <if test="deviceCollectId != null">
                AND device_collect_id = #{deviceCollectId}
            </if>
            <if test="deviceCollectName != null and deviceCollectName != ''">
                AND device_collect_name ILIKE CONCAT('%', #{deviceCollectName}, '%')
            </if>
            <if test="deviceIp != null and deviceIp != ''">
                AND device_ip = #{deviceIp}::inet
            </if>
            <if test="organizationId != null">
                AND organization_id = #{organizationId}
            </if>
            <if test="networkProtocol != null and networkProtocol != ''">
                AND network_protocol = #{networkProtocol}
            </if>
            <if test="sourceMethod != null and sourceMethod != ''">
                AND source_method = #{sourceMethod}
            </if>
            <if test="firstTime != null">
                AND first_time >= #{firstTime}
            </if>
            <if test="lastTime != null">
                AND last_time >= #{lastTime}
            </if>
        </where>
        ORDER BY last_time DESC
    </select>

    <!-- 根据ID更新设备信息 -->
    <update id="updateById" parameterType="com.common.entity.DeviceUnknown">
        UPDATE device_unknown
        <set>
            <if test="deviceCollectId != null">
                device_collect_id = #{deviceCollectId},
            </if>
            <if test="deviceCollectName != null and deviceCollectName != ''">
                device_collect_name = #{deviceCollectName},
            </if>
            <if test="deviceIp != null and deviceIp != ''">
                device_ip = #{deviceIp}::inet,
            </if>
            <if test="firstTime != null">
                first_time = #{firstTime},
            </if>
            <if test="lastTime != null">
                last_time = #{lastTime},
            </if>
            <if test="organizationId != null">
                organization_id = #{organizationId},
            </if>
            <if test="networkProtocol != null and networkProtocol != ''">
                network_protocol = #{networkProtocol},
            </if>
            <if test="sourceMethod != null and sourceMethod != ''">
                source_method = #{sourceMethod},
            </if>
        </set>
        WHERE id = #{id}
    </update>

    <!-- 更新最后发现时间 -->
    <update id="updateLastTime">
        UPDATE device_unknown
        SET last_time = #{lastTime}
        WHERE id = #{id}
    </update>

    <!-- 根据ID删除设备 -->
    <delete id="deleteById">
        DELETE FROM device_unknown
        WHERE id = #{id}
    </delete>

    <!-- 根据组织ID删除设备 -->
    <delete id="deleteByOrganizationId">
        DELETE FROM device_unknown
        WHERE organization_id = #{organizationId}
    </delete>

    <!-- 统计设备数量 -->
    <select id="count" resultType="java.lang.Long">
        SELECT COUNT(*) FROM device_unknown
    </select>

    <!-- 根据条件统计设备数量 -->
    <select id="countByCondition" parameterType="com.common.entity.DeviceUnknown"
            resultType="java.lang.Long">
        SELECT COUNT(*) FROM device_unknown
        <where>
            <if test="deviceCollectId != null">
                AND device_collect_id = #{deviceCollectId}
            </if>
            <if test="deviceCollectName != null and deviceCollectName != ''">
                AND device_collect_name ILIKE CONCAT('%', #{deviceCollectName}, '%')
            </if>
            <if test="deviceIp != null and deviceIp != ''">
                AND device_ip = #{deviceIp}::inet
            </if>
            <if test="organizationId != null">
                AND organization_id = #{organizationId}
            </if>
            <if test="networkProtocol != null and networkProtocol != ''">
                AND network_protocol = #{networkProtocol}
            </if>
            <if test="sourceMethod != null and sourceMethod != ''">
                AND source_method = #{sourceMethod}
            </if>
            <if test="firstTime != null">
                AND first_time >= #{firstTime}
            </if>
            <if test="lastTime != null">
                AND last_time >= #{lastTime}
            </if>
        </where>
    </select>

</mapper>