<?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.inventory.mapper.InventoryMapper">

  <resultMap id="invMap" type="com.zioinfo.mes.inventory.MesInventory">
    <id property="id" column="id"/>
    <result property="itemCode" column="item_code"/>
    <result property="warehouseCode" column="warehouse_code"/>
    <result property="locationCode" column="location_code"/>
    <result property="lotNo" column="lot_no"/>
    <result property="qty" column="qty"/>
    <result property="unit" column="unit"/>
    <result property="updatedAt" column="updated_at"/>
    <result property="itemName" column="item_name"/>
    <result property="safetyStock" column="safety_stock"/>
  </resultMap>

  <resultMap id="txnMap" type="com.zioinfo.mes.inventory.MesInventoryTxn">
    <id property="id" column="id"/>
    <result property="txnType" column="txn_type"/>
    <result property="itemCode" column="item_code"/>
    <result property="fromWarehouse" column="from_warehouse"/>
    <result property="fromLocation" column="from_location"/>
    <result property="toWarehouse" column="to_warehouse"/>
    <result property="toLocation" column="to_location"/>
    <result property="lotNo" column="lot_no"/>
    <result property="qty" column="qty"/>
    <result property="refType" column="ref_type"/>
    <result property="refNo" column="ref_no"/>
    <result property="createdBy" column="created_by"/>
    <result property="createdAt" column="created_at"/>
  </resultMap>

  <select id="findAll" resultMap="invMap">
    SELECT inv.*, it.item_name, it.safety_stock
    FROM mes_inventory inv
    LEFT JOIN mes_item it ON it.item_code = inv.item_code
    <where>
      <if test="itemCode != null and itemCode != ''">AND inv.item_code = #{itemCode}</if>
      <if test="warehouseCode != null and warehouseCode != ''">AND inv.warehouse_code = #{warehouseCode}</if>
      <if test="keyword != null and keyword != ''">
        AND (inv.item_code ILIKE '%' || #{keyword} || '%' OR it.item_name ILIKE '%' || #{keyword} || '%')
      </if>
    </where>
    ORDER BY inv.item_code, inv.warehouse_code, inv.location_code
  </select>

  <select id="findOne" resultMap="invMap">
    SELECT * FROM mes_inventory
    WHERE item_code = #{itemCode}
      AND COALESCE(warehouse_code,'') = COALESCE(#{warehouseCode},'')
      AND COALESCE(location_code,'') = COALESCE(#{locationCode},'')
      AND COALESCE(lot_no,'') = COALESCE(#{lotNo},'')
    LIMIT 1
  </select>

  <select id="sumQty" resultType="java.lang.Double">
    SELECT COALESCE(SUM(qty),0) FROM mes_inventory WHERE item_code = #{itemCode}
  </select>

  <select id="findBelowSafety" resultMap="invMap">
    SELECT inv.item_code, SUM(inv.qty) AS qty, it.item_name, it.safety_stock,
           MIN(inv.id) AS id, MAX(inv.warehouse_code) AS warehouse_code
    FROM mes_inventory inv
    JOIN mes_item it ON it.item_code = inv.item_code
    WHERE it.safety_stock IS NOT NULL AND it.safety_stock > 0
    GROUP BY inv.item_code, it.item_name, it.safety_stock
    HAVING SUM(inv.qty) &lt; it.safety_stock
    ORDER BY inv.item_code
  </select>

  <insert id="insertStock" parameterType="com.zioinfo.mes.inventory.MesInventory" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mes_inventory (item_code, warehouse_code, location_code, lot_no, qty, unit, updated_at)
    VALUES (#{itemCode}, #{warehouseCode}, #{locationCode}, #{lotNo}, #{qty}, #{unit}, NOW())
  </insert>

  <update id="updateQty">
    UPDATE mes_inventory SET qty = #{qty}, updated_at = NOW() WHERE id = #{id}
  </update>

  <insert id="insertTxn" parameterType="com.zioinfo.mes.inventory.MesInventoryTxn" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mes_inventory_txn (txn_type, item_code, from_warehouse, from_location,
      to_warehouse, to_location, lot_no, qty, ref_type, ref_no, created_by)
    VALUES (#{txnType}, #{itemCode}, #{fromWarehouse}, #{fromLocation}, #{toWarehouse},
      #{toLocation}, #{lotNo}, #{qty}, #{refType}, #{refNo}, #{createdBy})
  </insert>

  <select id="findTxns" resultMap="txnMap">
    SELECT * FROM mes_inventory_txn
    <where>
      <if test="itemCode != null and itemCode != ''">AND item_code = #{itemCode}</if>
      <if test="refType != null and refType != ''">AND ref_type = #{refType}</if>
      <if test="refNo != null and refNo != ''">AND ref_no = #{refNo}</if>
    </where>
    ORDER BY id DESC LIMIT #{limit}
  </select>

  <select id="findByLot" resultMap="txnMap">
    SELECT * FROM mes_inventory_txn WHERE lot_no = #{lotNo} ORDER BY id ASC
  </select>

  <select id="kpiSummary" resultType="map">
    SELECT
      (SELECT COUNT(DISTINCT item_code) FROM mes_inventory) AS item_count,
      (SELECT COALESCE(SUM(qty),0) FROM mes_inventory) AS total_qty,
      (SELECT COUNT(*) FROM mes_inventory_txn WHERE created_at::date = CURRENT_DATE) AS today_txn_count
  </select>

</mapper>
