<?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.certificate.mapper.CertificateMapper">

  <resultMap id="certMap" type="com.zioinfo.mes.certificate.MesCertificate">
    <id property="id" column="id"/>
    <result property="certificateNo" column="certificate_no"/>
    <result property="certType" column="cert_type"/>
    <result property="itemCode" column="item_code"/>
    <result property="lotNo" column="lot_no"/>
    <result property="partnerCode" column="partner_code"/>
    <result property="refType" column="ref_type"/>
    <result property="refNo" column="ref_no"/>
    <result property="issuedDate" column="issued_date"/>
    <result property="content" column="content"/>
    <result property="filePath" column="file_path"/>
    <result property="status" column="status"/>
    <result property="createdBy" column="created_by"/>
    <result property="createdAt" column="created_at"/>
  </resultMap>

  <select id="findAll" resultMap="certMap">
    SELECT * FROM mes_certificate
    <where>
      <if test="certType != null and certType != ''">AND cert_type = #{certType}</if>
      <if test="itemCode != null and itemCode != ''">AND item_code = #{itemCode}</if>
      <if test="lotNo != null and lotNo != ''">AND lot_no = #{lotNo}</if>
    </where>
    ORDER BY id DESC
  </select>

  <select id="findById" resultMap="certMap">SELECT * FROM mes_certificate WHERE id = #{id}</select>
  <select id="countByNo" resultType="int">SELECT COUNT(*) FROM mes_certificate WHERE certificate_no = #{certificateNo}</select>

  <insert id="insert" parameterType="com.zioinfo.mes.certificate.MesCertificate" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mes_certificate (certificate_no, cert_type, item_code, lot_no, partner_code,
      ref_type, ref_no, issued_date, content, file_path, status, created_by)
    VALUES (#{certificateNo}, #{certType}, #{itemCode}, #{lotNo}, #{partnerCode},
      #{refType}, #{refNo}, #{issuedDate}, #{content}::jsonb, #{filePath}, #{status}, #{createdBy})
  </insert>

</mapper>
