<?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.shipping.mapper.ShippingMapper">

  <resultMap id="shipMap" type="com.zioinfo.mes.shipping.MesShipping">
    <id property="id" column="id"/>
    <result property="shippingNo" column="shipping_no"/>
    <result property="partnerCode" column="partner_code"/>
    <result property="orderRef" column="order_ref"/>
    <result property="itemCode" column="item_code"/>
    <result property="itemName" column="item_name"/>
    <result property="lotNo" column="lot_no"/>
    <result property="planQty" column="plan_qty"/>
    <result property="pickedQty" column="picked_qty"/>
    <result property="packedQty" column="packed_qty"/>
    <result property="shippedQty" column="shipped_qty"/>
    <result property="warehouseCode" column="warehouse_code"/>
    <result property="locationCode" column="location_code"/>
    <result property="status" column="status"/>
    <result property="inspectionRequired" column="inspection_required"/>
    <result property="inspectionResult" column="inspection_result"/>
    <result property="deliveryNo" column="delivery_no"/>
    <result property="statementNo" column="statement_no"/>
    <result property="shipDate" column="ship_date"/>
    <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="shipMap">
    SELECT sh.*, it.item_name FROM mes_shipping sh
    LEFT JOIN mes_item it ON it.item_code = sh.item_code
    <where>
      <if test="status != null and status != ''">AND sh.status = #{status}</if>
      <if test="partnerCode != null and partnerCode != ''">AND sh.partner_code = #{partnerCode}</if>
      <if test="keyword != null and keyword != ''">
        AND (sh.shipping_no ILIKE '%' || #{keyword} || '%' OR it.item_name ILIKE '%' || #{keyword} || '%')
      </if>
    </where>
    ORDER BY sh.id DESC
  </select>

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

  <select id="countByNo" resultType="int">SELECT COUNT(*) FROM mes_shipping WHERE shipping_no = #{shippingNo}</select>

  <insert id="insert" parameterType="com.zioinfo.mes.shipping.MesShipping" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mes_shipping (shipping_no, partner_code, order_ref, item_code, lot_no, plan_qty,
      picked_qty, packed_qty, shipped_qty, warehouse_code, location_code, status,
      inspection_required, remark, created_by)
    VALUES (#{shippingNo}, #{partnerCode}, #{orderRef}, #{itemCode}, #{lotNo}, #{planQty},
      0, 0, 0, #{warehouseCode}, #{locationCode}, #{status},
      #{inspectionRequired}, #{remark}, #{createdBy})
  </insert>

  <update id="update" parameterType="com.zioinfo.mes.shipping.MesShipping">
    UPDATE mes_shipping SET partner_code = #{partnerCode}, order_ref = #{orderRef},
      item_code = #{itemCode}, lot_no = #{lotNo}, plan_qty = #{planQty},
      warehouse_code = #{warehouseCode}, location_code = #{locationCode},
      inspection_required = #{inspectionRequired}, remark = #{remark}, updated_at = NOW()
    WHERE id = #{id}
  </update>

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

  <update id="updatePicked">
    UPDATE mes_shipping SET picked_qty = #{pickedQty}, status = 'PICKING', updated_at = NOW() WHERE id = #{id}
  </update>

  <update id="updatePacked">
    UPDATE mes_shipping SET packed_qty = #{packedQty}, status = 'PACKING', updated_at = NOW() WHERE id = #{id}
  </update>

  <update id="updateInspection">
    UPDATE mes_shipping SET inspection_result = #{inspectionResult}, status = 'INSPECTED', updated_at = NOW() WHERE id = #{id}
  </update>

  <update id="updateShip">
    UPDATE mes_shipping
    SET shipped_qty = #{shippedQty}, delivery_no = #{deliveryNo}, statement_no = #{statementNo},
        status = 'SHIPPED', ship_date = CURRENT_DATE, updated_at = NOW()
    WHERE id = #{id}
  </update>

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

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

</mapper>
