<?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.analytics.mapper.AnalyticsMapper">

  <select id="productionKpi" resultType="map">
    SELECT
      COALESCE(SUM(good_qty),0) AS total_good,
      COALESCE(SUM(defect_qty),0) AS total_defect,
      ROUND( (COALESCE(SUM(defect_qty),0)::numeric
              / NULLIF(SUM(good_qty)+SUM(defect_qty),0) * 100), 2) AS defect_rate,
      (SELECT COUNT(*) FROM mes_workorder
        WHERE status IN ('DONE','CLOSED')
          AND updated_at &gt;= CURRENT_DATE - (#{days} || ' days')::interval) AS completed_workorders
    FROM mes_production
    WHERE reported_at &gt;= CURRENT_DATE - (#{days} || ' days')::interval
  </select>

  <select id="qualityKpi" resultType="map">
    SELECT
      (SELECT COUNT(*) FROM mes_inspection
        WHERE created_at &gt;= CURRENT_DATE - (#{days} || ' days')::interval) AS total_inspections,
      (SELECT ROUND( (COUNT(*) FILTER (WHERE result='PASS')::numeric
                      / NULLIF(COUNT(*) FILTER (WHERE result IN ('PASS','FAIL')),0) * 100), 1)
         FROM mes_inspection
         WHERE created_at &gt;= CURRENT_DATE - (#{days} || ' days')::interval) AS pass_rate,
      (SELECT COUNT(*) FROM mes_ncr
        WHERE created_at &gt;= CURRENT_DATE - (#{days} || ' days')::interval) AS ncr_count,
      (SELECT COUNT(*) FROM mes_ncr WHERE status NOT IN ('CLOSED')) AS open_ncr,
      (SELECT COUNT(*) FROM mes_capa WHERE status NOT IN ('CLOSED')) AS open_capa
  </select>

  <select id="inventoryKpi" 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 (
         SELECT inv.item_code FROM mes_inventory inv
         JOIN mes_item it ON it.item_code = inv.item_code
         WHERE it.safety_stock > 0
         GROUP BY inv.item_code, it.safety_stock
         HAVING SUM(inv.qty) &lt; it.safety_stock
       ) sub) AS below_safety_count
  </select>

  <select id="dailyProduction" resultType="map">
    SELECT reported_at::date AS day,
           COALESCE(SUM(good_qty),0) AS good_qty,
           COALESCE(SUM(defect_qty),0) AS defect_qty
    FROM mes_production
    WHERE reported_at &gt;= CURRENT_DATE - (#{days} || ' days')::interval
    GROUP BY reported_at::date ORDER BY day ASC
  </select>

  <select id="topItems" resultType="map">
    SELECT pr.item_code, it.item_name, COALESCE(SUM(pr.good_qty),0) AS good_qty
    FROM mes_production pr
    LEFT JOIN mes_item it ON it.item_code = pr.item_code
    WHERE pr.reported_at &gt;= CURRENT_DATE - (#{days} || ' days')::interval
    GROUP BY pr.item_code, it.item_name
    ORDER BY good_qty DESC LIMIT #{limit}
  </select>

</mapper>
