<?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.workorder.mapper.WorkOrderMapper">

  <resultMap id="woMap" type="com.zioinfo.mes.workorder.MesWorkOrder">
    <id property="id" column="id"/>
    <result property="woNo" column="wo_no"/>
    <result property="itemCode" column="item_code"/>
    <result property="itemName" column="item_name"/>
    <result property="planQty" column="plan_qty"/>
    <result property="producedQty" column="produced_qty"/>
    <result property="defectQty" column="defect_qty"/>
    <result property="unit" column="unit"/>
    <result property="dueDate" column="due_date"/>
    <result property="warehouseCode" column="warehouse_code"/>
    <result property="status" column="status"/>
    <result property="priority" column="priority"/>
    <result property="remark" column="remark"/>
    <result property="createdBy" column="created_by"/>
    <result property="createdAt" column="created_at"/>
    <result property="updatedAt" column="updated_at"/>
  </resultMap>

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

  <select id="findById" resultMap="woMap">
    SELECT wo.*, it.item_name FROM mes_workorder wo
    LEFT JOIN mes_item it ON it.item_code = wo.item_code WHERE wo.id = #{id}
  </select>
  <select id="findByNo" resultMap="woMap">SELECT * FROM mes_workorder WHERE wo_no = #{woNo}</select>
  <select id="countByNo" resultType="int">SELECT COUNT(*) FROM mes_workorder WHERE wo_no = #{woNo}</select>

  <insert id="insert" parameterType="com.zioinfo.mes.workorder.MesWorkOrder" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mes_workorder (wo_no, item_code, plan_qty, produced_qty, defect_qty, unit, due_date,
      warehouse_code, status, priority, remark, created_by)
    VALUES (#{woNo}, #{itemCode}, #{planQty}, #{producedQty}, #{defectQty}, #{unit}, #{dueDate},
      #{warehouseCode}, #{status}, #{priority}, #{remark}, #{createdBy})
  </insert>

  <update id="update" parameterType="com.zioinfo.mes.workorder.MesWorkOrder">
    UPDATE mes_workorder SET item_code = #{itemCode}, plan_qty = #{planQty}, unit = #{unit},
      due_date = #{dueDate}, warehouse_code = #{warehouseCode}, priority = #{priority},
      remark = #{remark}, updated_at = NOW()
    WHERE id = #{id}
  </update>

  <update id="updateStatus">
    UPDATE mes_workorder SET status = #{status}, updated_at = NOW() WHERE id = #{id}
  </update>

  <update id="addProduced">
    UPDATE mes_workorder
    SET produced_qty = COALESCE(produced_qty,0) + #{qty},
        defect_qty = COALESCE(defect_qty,0) + COALESCE(#{defect},0),
        updated_at = NOW()
    WHERE id = #{id}
  </update>

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

  <select id="countByStatus" resultType="map">
    SELECT status, COUNT(*) AS cnt FROM mes_workorder GROUP BY status
  </select>

</mapper>
