<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zioinfo.mes.spec.mapper.SpecMapper">

  <resultMap id="specMap" type="com.zioinfo.mes.spec.MesSpec">
    <id property="id" column="id"/>
    <result property="specCode" column="spec_code"/>
    <result property="itemCode" column="item_code"/>
    <result property="inspectionType" column="inspection_type"/>
    <result property="characteristic" column="characteristic"/>
    <result property="lsl" column="lsl"/>
    <result property="usl" column="usl"/>
    <result property="target" column="target"/>
    <result property="unit" column="unit"/>
    <result property="aql" column="aql"/>
    <result property="items" column="items"/>
    <result property="status" column="status"/>
    <result property="createdBy" column="created_by"/>
    <result property="createdAt" column="created_at"/>
  </resultMap>

  <select id="findAll" resultMap="specMap">
    SELECT * FROM mes_spec
    <where>
      <if test="itemCode != null and itemCode != ''">AND item_code = #{itemCode}</if>
      <if test="inspectionType != null and inspectionType != ''">AND inspection_type = #{inspectionType}</if>
    </where>
    ORDER BY id DESC
  </select>

  <select id="findById" resultMap="specMap">SELECT * FROM mes_spec WHERE id = #{id}</select>
  <select id="findByCode" resultMap="specMap">SELECT * FROM mes_spec WHERE spec_code = #{specCode}</select>

  <select id="countByCode" resultType="int">
    SELECT COUNT(*) FROM mes_spec WHERE spec_code = #{specCode}
    <if test="excludeId != null">AND id != #{excludeId}</if>
  </select>

  <insert id="insert" parameterType="com.zioinfo.mes.spec.MesSpec" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mes_spec (spec_code, item_code, inspection_type, characteristic, lsl, usl, target,
      unit, aql, items, status, created_by)
    VALUES (#{specCode}, #{itemCode}, #{inspectionType}, #{characteristic}, #{lsl}, #{usl}, #{target},
      #{unit}, #{aql}, #{items}::jsonb, #{status}, #{createdBy})
  </insert>

  <update id="update" parameterType="com.zioinfo.mes.spec.MesSpec">
    UPDATE mes_spec SET item_code = #{itemCode}, inspection_type = #{inspectionType},
      characteristic = #{characteristic}, lsl = #{lsl}, usl = #{usl}, target = #{target},
      unit = #{unit}, aql = #{aql}, items = #{items}::jsonb, status = #{status}
    WHERE id = #{id}
  </update>

  <delete id="delete">DELETE FROM mes_spec WHERE id = #{id}</delete>

</mapper>
