<?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.inspection.mapper.InspectionMapper">

  <resultMap id="insMap" type="com.zioinfo.mes.inspection.MesInspection">
    <id property="id" column="id"/>
    <result property="inspectionNo" column="inspection_no"/>
    <result property="inspectionType" column="inspection_type"/>
    <result property="itemCode" column="item_code"/>
    <result property="lotNo" column="lot_no"/>
    <result property="refType" column="ref_type"/>
    <result property="refNo" column="ref_no"/>
    <result property="specCode" column="spec_code"/>
    <result property="sampleSize" column="sample_size"/>
    <result property="measurements" column="measurements"/>
    <result property="result" column="result"/>
    <result property="defectQty" column="defect_qty"/>
    <result property="inspector" column="inspector"/>
    <result property="remark" column="remark"/>
    <result property="createdBy" column="created_by"/>
    <result property="createdAt" column="created_at"/>
  </resultMap>

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

  <select id="findById" resultMap="insMap">SELECT * FROM mes_inspection WHERE id = #{id}</select>
  <select id="countByNo" resultType="int">SELECT COUNT(*) FROM mes_inspection WHERE inspection_no = #{inspectionNo}</select>

  <insert id="insert" parameterType="com.zioinfo.mes.inspection.MesInspection" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mes_inspection (inspection_no, inspection_type, item_code, lot_no, ref_type, ref_no,
      spec_code, sample_size, measurements, result, defect_qty, inspector, remark, created_by)
    VALUES (#{inspectionNo}, #{inspectionType}, #{itemCode}, #{lotNo}, #{refType}, #{refNo},
      #{specCode}, #{sampleSize}, #{measurements}::jsonb, #{result}, #{defectQty}, #{inspector}, #{remark}, #{createdBy})
  </insert>

  <update id="updateResult">
    UPDATE mes_inspection
    SET result = #{result},
        measurements = COALESCE(#{measurements}::jsonb, measurements),
        defect_qty = COALESCE(#{defectQty}, defect_qty)
    WHERE id = #{id}
  </update>

  <select id="passRateByType" resultType="map">
    SELECT inspection_type,
           COUNT(*) AS total,
           COUNT(*) FILTER (WHERE result = 'PASS') AS pass_count,
           COUNT(*) FILTER (WHERE result = 'FAIL') AS fail_count,
           ROUND( (COUNT(*) FILTER (WHERE result = 'PASS')::numeric
                   / NULLIF(COUNT(*) FILTER (WHERE result IN ('PASS','FAIL')),0) * 100), 1) AS pass_rate
    FROM mes_inspection
    WHERE created_at &gt;= CURRENT_DATE - (#{days} || ' days')::interval
    GROUP BY inspection_type
    ORDER BY inspection_type
  </select>

</mapper>
