<?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.item.mapper.ItemMapper">

  <resultMap id="itemMap" type="com.zioinfo.mes.item.MesItem">
    <id property="id" column="id"/>
    <result property="itemCode" column="item_code"/>
    <result property="itemName" column="item_name"/>
    <result property="itemType" column="item_type"/>
    <result property="unit" column="unit"/>
    <result property="barcode" column="barcode"/>
    <result property="spec" column="spec"/>
    <result property="safetyStock" column="safety_stock"/>
    <result property="warehouseCode" column="warehouse_code"/>
    <result property="attributes" column="attributes"/>
    <result property="status" column="status"/>
    <result property="createdBy" column="created_by"/>
    <result property="createdAt" column="created_at"/>
    <result property="updatedAt" column="updated_at"/>
  </resultMap>

  <select id="findAll" resultMap="itemMap">
    SELECT * FROM mes_item
    <where>
      <if test="itemType != null and itemType != ''">AND item_type = #{itemType}</if>
      <if test="status != null and status != ''">AND status = #{status}</if>
      <if test="keyword != null and keyword != ''">
        AND (item_code ILIKE '%' || #{keyword} || '%' OR item_name ILIKE '%' || #{keyword} || '%')
      </if>
    </where>
    ORDER BY id DESC
  </select>

  <select id="findById" resultMap="itemMap">SELECT * FROM mes_item WHERE id = #{id}</select>
  <select id="findByCode" resultMap="itemMap">SELECT * FROM mes_item WHERE item_code = #{itemCode}</select>

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

  <insert id="insert" parameterType="com.zioinfo.mes.item.MesItem" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mes_item (item_code, item_name, item_type, unit, barcode, spec, safety_stock,
      warehouse_code, attributes, status, created_by)
    VALUES (#{itemCode}, #{itemName}, #{itemType}, #{unit}, #{barcode}, #{spec}, #{safetyStock},
      #{warehouseCode}, #{attributes}::jsonb, #{status}, #{createdBy})
  </insert>

  <update id="update" parameterType="com.zioinfo.mes.item.MesItem">
    UPDATE mes_item SET item_code = #{itemCode}, item_name = #{itemName}, item_type = #{itemType},
      unit = #{unit}, barcode = #{barcode}, spec = #{spec}, safety_stock = #{safetyStock},
      warehouse_code = #{warehouseCode}, attributes = #{attributes}::jsonb, status = #{status},
      updated_at = NOW()
    WHERE id = #{id}
  </update>

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

</mapper>
