<?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.progress.mapper.ProgressMapper">

  <select id="workorderProgress" resultType="map">
    SELECT wo.id, wo.wo_no, wo.item_code, it.item_name, wo.plan_qty, wo.produced_qty, wo.defect_qty,
           wo.status, wo.priority, wo.due_date,
           ROUND( (COALESCE(wo.produced_qty,0) / NULLIF(wo.plan_qty,0) * 100)::numeric, 1) AS progress_rate,
           CASE WHEN wo.due_date IS NOT NULL AND wo.due_date &lt; CURRENT_DATE
                     AND wo.status NOT IN ('DONE','CLOSED','CANCELLED')
                THEN TRUE ELSE FALSE END AS delayed
    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="status == null or status == ''">AND wo.status IN ('RELEASED','INPROGRESS','DONE')</if>
    </where>
    ORDER BY wo.due_date ASC NULLS LAST, wo.id DESC
  </select>

  <select id="workorderProgressOne" resultType="map">
    SELECT wo.id, wo.wo_no, wo.item_code, it.item_name, wo.plan_qty, wo.produced_qty, wo.defect_qty,
           wo.status, wo.priority, wo.due_date, wo.warehouse_code,
           ROUND( (COALESCE(wo.produced_qty,0) / NULLIF(wo.plan_qty,0) * 100)::numeric, 1) AS progress_rate
    FROM mes_workorder wo
    LEFT JOIN mes_item it ON it.item_code = wo.item_code
    WHERE wo.id = #{id}
  </select>

  <select id="processProgressByWo" resultType="map">
    SELECT id, op_seq, process_code, process_name, equipment_code, worker_id, status,
           input_qty, output_qty, started_at, ended_at
    FROM mes_process_progress
    WHERE workorder_id = #{workorderId}
    ORDER BY op_seq ASC
  </select>

  <select id="liveProcess" resultType="map">
    SELECT pp.wo_no, pp.process_code, pp.process_name, pp.equipment_code, pp.worker_id,
           pp.input_qty, pp.output_qty, pp.started_at
    FROM mes_process_progress pp
    WHERE pp.status = 'RUNNING'
    ORDER BY pp.started_at DESC
  </select>

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

  <select id="delayedWorkorders" resultType="map">
    SELECT wo.id, wo.wo_no, wo.item_code, wo.plan_qty, wo.produced_qty, wo.status, wo.due_date,
           (CURRENT_DATE - wo.due_date) AS days_overdue
    FROM mes_workorder wo
    WHERE wo.due_date IS NOT NULL AND wo.due_date &lt; CURRENT_DATE
      AND wo.status NOT IN ('DONE','CLOSED','CANCELLED')
    ORDER BY wo.due_date ASC
  </select>

  <select id="delayedJobs" resultType="map">
    SELECT j.id, j.job_no, j.wo_no, j.process_name, j.worker_id, j.assigned_qty, j.done_qty,
           j.status, j.planned_end
    FROM mes_job j
    WHERE j.planned_end IS NOT NULL AND j.planned_end &lt; NOW()
      AND j.status NOT IN ('DONE','CANCELLED')
    ORDER BY j.planned_end ASC
  </select>

  <select id="progressSummary" 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 COUNT(*) FROM mes_workorder
        WHERE due_date IS NOT NULL AND due_date &lt; CURRENT_DATE
          AND status NOT IN ('DONE','CLOSED','CANCELLED')) AS delayed_workorders,
      (SELECT COUNT(*) FROM mes_process_progress WHERE status = 'RUNNING') AS running_processes,
      (SELECT COUNT(*) FROM mes_job WHERE status IN ('ASSIGNED','STARTED','PAUSED')) AS active_jobs,
      (SELECT ROUND(AVG(COALESCE(produced_qty,0) / NULLIF(plan_qty,0) * 100)::numeric,1)
         FROM mes_workorder WHERE status IN ('RELEASED','INPROGRESS','DONE')) AS avg_progress_rate
  </select>

</mapper>
