<?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.dashboard.mapper.DashboardMapper">

  <select id="summary" resultType="map">
    SELECT
      (SELECT COUNT(*) FROM mes_workorder WHERE status IN ('RELEASED','INPROGRESS')) AS active_workorders,
      (SELECT COUNT(*) FROM mes_workorder WHERE status = 'INPROGRESS') AS inprogress_workorders,
      (SELECT COALESCE(SUM(good_qty),0) FROM mes_production WHERE reported_at::date = CURRENT_DATE) AS today_good_qty,
      (SELECT COALESCE(SUM(defect_qty),0) FROM mes_production WHERE reported_at::date = CURRENT_DATE) AS today_defect_qty,
      (SELECT COUNT(DISTINCT item_code) FROM mes_inventory) AS inventory_items,
      (SELECT COALESCE(SUM(qty),0) FROM mes_inventory) AS inventory_qty,
      (SELECT COUNT(*) FROM mes_ncr WHERE status NOT IN ('CLOSED')) AS open_ncr,
      (SELECT COUNT(*) FROM mes_inspection WHERE result = 'PENDING') AS pending_inspections,
      (SELECT COUNT(*) FROM mes_shipping WHERE status NOT IN ('SHIPPED','CANCELLED')) AS pending_shipments,
      (SELECT COUNT(*) FROM mes_equipment WHERE run_status = 'DOWN') AS equipment_down
  </select>

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

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

  <select id="equipmentRunStatus" resultType="map">
    SELECT run_status, COUNT(*) AS cnt FROM mes_equipment GROUP BY run_status ORDER BY run_status
  </select>

</mapper>
