<?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.lotserial.mapper.LotSerialMapper">

  <resultMap id="lotMap" type="com.zioinfo.mes.lotserial.MesLotSerial">
    <id property="id" column="id"/>
    <result property="lotNo" column="lot_no"/>
    <result property="serialNo" column="serial_no"/>
    <result property="itemCode" column="item_code"/>
    <result property="itemName" column="item_name"/>
    <result property="qty" column="qty"/>
    <result property="sourceType" column="source_type"/>
    <result property="sourceRef" column="source_ref"/>
    <result property="mfgDate" column="mfg_date"/>
    <result property="expiryDate" column="expiry_date"/>
    <result property="status" column="status"/>
    <result property="createdBy" column="created_by"/>
    <result property="createdAt" column="created_at"/>
  </resultMap>

  <select id="findAll" resultMap="lotMap">
    SELECT l.*, it.item_name FROM mes_lotserial l
    LEFT JOIN mes_item it ON it.item_code = l.item_code
    <where>
      <if test="itemCode != null and itemCode != ''">AND l.item_code = #{itemCode}</if>
      <if test="status != null and status != ''">AND l.status = #{status}</if>
      <if test="keyword != null and keyword != ''">
        AND (l.lot_no ILIKE '%' || #{keyword} || '%' OR l.serial_no ILIKE '%' || #{keyword} || '%')
      </if>
    </where>
    ORDER BY l.id DESC
  </select>

  <select id="findById" resultMap="lotMap">
    SELECT l.*, it.item_name FROM mes_lotserial l
    LEFT JOIN mes_item it ON it.item_code = l.item_code WHERE l.id = #{id}
  </select>

  <select id="findByLot" resultMap="lotMap">SELECT * FROM mes_lotserial WHERE lot_no = #{lotNo} LIMIT 1</select>

  <insert id="insert" parameterType="com.zioinfo.mes.lotserial.MesLotSerial" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mes_lotserial (lot_no, serial_no, item_code, qty, source_type, source_ref,
      mfg_date, expiry_date, status, created_by)
    VALUES (#{lotNo}, #{serialNo}, #{itemCode}, #{qty}, #{sourceType}, #{sourceRef},
      #{mfgDate}, #{expiryDate}, #{status}, #{createdBy})
  </insert>

  <update id="updateStatus">UPDATE mes_lotserial SET status = #{status} WHERE id = #{id}</update>

  <select id="findExpiringSoon" resultMap="lotMap">
    SELECT l.*, it.item_name FROM mes_lotserial l
    LEFT JOIN mes_item it ON it.item_code = l.item_code
    WHERE l.expiry_date IS NOT NULL AND l.status = 'ACTIVE'
      AND l.expiry_date &lt;= CURRENT_DATE + (#{days} || ' days')::interval
    ORDER BY l.expiry_date ASC
  </select>

</mapper>
