<?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.aucma.base.mapper.ProcessSnapshotMapper">

    <resultMap type="ProcessSnapshot" id="ProcessSnapshotResult">
        <result property="snapshotId" column="snapshot_id"/>
        <result property="deviceCode" column="device_code"/>
        <result property="deviceName" column="device_name"/>
        <result property="snapshotTime" column="snapshot_time"/>
        <result property="snapshotType" column="snapshot_type"/>
        <result property="orderCode" column="order_code"/>
        <result property="remark" column="remarks"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="isFlag" column="is_flag"/>
        <result property="moldCode" column="mold_code"/>
        <result property="productCode" column="product_code"/>
        <result property="backupName" column="backup_name"/>
        <result property="isDefault" column="is_default"/>
    </resultMap>

    <sql id="selectProcessSnapshotVo">
        SELECT s.snapshot_id, s.device_code, d.device_name, s.snapshot_time, s.snapshot_type,
               s.order_code, s.remarks, s.create_by, s.create_time, s.update_by, s.update_time, s.is_flag,
               s.mold_code, s.product_code, s.backup_name, s.is_default
        FROM process_snapshot s
        LEFT JOIN base_deviceledger d ON s.device_code = d.device_code
    </sql>

    <select id="selectProcessSnapshotList" parameterType="ProcessSnapshot" resultMap="ProcessSnapshotResult">
        <include refid="selectProcessSnapshotVo"/>
        <where>
            s.is_flag = '1'
            <if test="deviceCode != null and deviceCode != ''">AND s.device_code = #{deviceCode}</if>
            <if test="snapshotType != null and snapshotType != ''">AND s.snapshot_type = #{snapshotType}</if>
            <if test="orderCode != null and orderCode != ''">AND s.order_code = #{orderCode}</if>
            <if test="moldCode != null and moldCode != ''">AND s.mold_code = #{moldCode}</if>
            <if test="productCode != null and productCode != ''">AND s.product_code = #{productCode}</if>
            <if test="isDefault != null and isDefault != ''">AND s.is_default = #{isDefault}</if>
            <if test="params != null and params.beginTime != null and params.endTime != null">
                AND s.snapshot_time BETWEEN #{params.beginTime} AND #{params.endTime}
            </if>
        </where>
        ORDER BY s.snapshot_time DESC
    </select>

    <select id="selectProcessSnapshotBySnapshotId" parameterType="Long" resultMap="ProcessSnapshotResult">
        <include refid="selectProcessSnapshotVo"/>
        WHERE s.snapshot_id = #{snapshotId}
    </select>

    <insert id="insertProcessSnapshot" parameterType="ProcessSnapshot">
        <selectKey keyProperty="snapshotId" resultType="long" order="BEFORE">
            SELECT seq_process_snapshot_index.NEXTVAL as snapshotId FROM DUAL
        </selectKey>
        INSERT INTO process_snapshot
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="snapshotId != null">snapshot_id,</if>
            <if test="deviceCode != null">device_code,</if>
            <if test="snapshotTime != null">snapshot_time,</if>
            <if test="snapshotType != null">snapshot_type,</if>
            <if test="orderCode != null">order_code,</if>
            <if test="remark != null">remarks,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="isFlag != null">is_flag,</if>
            <if test="moldCode != null">mold_code,</if>
            <if test="productCode != null">product_code,</if>
            <if test="backupName != null">backup_name,</if>
            <if test="isDefault != null">is_default,</if>
        </trim>
        <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
            <if test="snapshotId != null">#{snapshotId},</if>
            <if test="deviceCode != null">#{deviceCode},</if>
            <if test="snapshotTime != null">#{snapshotTime},</if>
            <if test="snapshotType != null">#{snapshotType},</if>
            <if test="orderCode != null">#{orderCode},</if>
            <if test="remark != null">#{remark},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="isFlag != null">#{isFlag},</if>
            <if test="moldCode != null">#{moldCode},</if>
            <if test="productCode != null">#{productCode},</if>
            <if test="backupName != null">#{backupName},</if>
            <if test="isDefault != null">#{isDefault},</if>
        </trim>
    </insert>

    <update id="updateProcessSnapshot" parameterType="ProcessSnapshot">
        UPDATE process_snapshot
        <trim prefix="SET" suffixOverrides=",">
            <if test="deviceCode != null">device_code = #{deviceCode},</if>
            <if test="snapshotTime != null">snapshot_time = #{snapshotTime},</if>
            <if test="snapshotType != null">snapshot_type = #{snapshotType},</if>
            <if test="orderCode != null">order_code = #{orderCode},</if>
            <if test="remark != null">remarks = #{remark},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="moldCode != null">mold_code = #{moldCode},</if>
            <if test="productCode != null">product_code = #{productCode},</if>
            <if test="backupName != null">backup_name = #{backupName},</if>
            <if test="isDefault != null">is_default = #{isDefault},</if>
        </trim>
        WHERE snapshot_id = #{snapshotId}
    </update>

    <delete id="deleteProcessSnapshotBySnapshotId" parameterType="Long">
        UPDATE process_snapshot SET is_flag = '0' WHERE snapshot_id = #{snapshotId}
    </delete>

    <delete id="deleteProcessSnapshotBySnapshotIds" parameterType="String">
        UPDATE process_snapshot SET is_flag = '0' WHERE snapshot_id IN
        <foreach item="snapshotId" collection="array" open="(" separator="," close=")">
            #{snapshotId}
        </foreach>
    </delete>

    <!-- 根据快照时间点查询参数值（时间窗口60秒内最接近的记录） -->
    <select id="selectParamsBySnapshotTime" resultType="BaseDeviceParamVal">
        SELECT * FROM (
            SELECT
                v.record_id AS recordId,
                v.param_code AS paramCode,
                v.device_code AS deviceCode,
                v.device_id AS deviceId,
                v.param_name AS paramName,
                v.param_value AS paramValue,
                v.collect_time AS collectTime,
                v.record_time AS recordTime,
                ROW_NUMBER() OVER (PARTITION BY v.param_code ORDER BY ABS(v.collect_time - #{snapshotTime})) AS rn
            FROM base_device_param_val v
            WHERE v.device_code = #{deviceCode}
              AND v.collect_time BETWEEN #{snapshotTime} - INTERVAL '1' MINUTE AND #{snapshotTime} + INTERVAL '1' MINUTE
        ) WHERE rn = 1
        ORDER BY paramCode
    </select>

    <!-- 对比两个快照的参数差异 -->
    <select id="compareSnapshots" resultType="java.util.Map">
        WITH snapshot1_params AS (
            SELECT * FROM (
                SELECT
                    v.param_code,
                    v.param_name,
                    v.param_value,
                    ROW_NUMBER() OVER (PARTITION BY v.param_code ORDER BY ABS(v.collect_time - #{snapshotTime1})) AS rn
                FROM base_device_param_val v
                WHERE v.device_code = #{deviceCode}
                  AND v.collect_time BETWEEN #{snapshotTime1} - INTERVAL '1' MINUTE AND #{snapshotTime1} + INTERVAL '1' MINUTE
            ) WHERE rn = 1
        ),
        snapshot2_params AS (
            SELECT * FROM (
                SELECT
                    v.param_code,
                    v.param_name,
                    v.param_value,
                    ROW_NUMBER() OVER (PARTITION BY v.param_code ORDER BY ABS(v.collect_time - #{snapshotTime2})) AS rn
                FROM base_device_param_val v
                WHERE v.device_code = #{deviceCode}
                  AND v.collect_time BETWEEN #{snapshotTime2} - INTERVAL '1' MINUTE AND #{snapshotTime2} + INTERVAL '1' MINUTE
            ) WHERE rn = 1
        )
        SELECT
            NVL(a.param_code, b.param_code) AS paramCode,
            NVL(a.param_name, b.param_name) AS paramName,
            a.param_value AS beforeValue,
            b.param_value AS afterValue,
            CASE WHEN NVL(a.param_value, 'NULL') != NVL(b.param_value, 'NULL') THEN 'Y' ELSE 'N' END AS isChanged
        FROM snapshot1_params a
        FULL OUTER JOIN snapshot2_params b ON a.param_code = b.param_code
        ORDER BY NVL(a.param_code, b.param_code)
    </select>

</mapper>
