<?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.job.mapper.JobMapper">

  <resultMap id="jobMap" type="com.zioinfo.mes.job.MesJob">
    <id property="id" column="id"/>
    <result property="jobNo" column="job_no"/>
    <result property="workorderId" column="workorder_id"/>
    <result property="woNo" column="wo_no"/>
    <result property="itemCode" column="item_code"/>
    <result property="itemName" column="item_name"/>
    <result property="opSeq" column="op_seq"/>
    <result property="processCode" column="process_code"/>
    <result property="processName" column="process_name"/>
    <result property="equipmentCode" column="equipment_code"/>
    <result property="workerId" column="worker_id"/>
    <result property="assignedQty" column="assigned_qty"/>
    <result property="doneQty" column="done_qty"/>
    <result property="status" column="status"/>
    <result property="plannedStart" column="planned_start"/>
    <result property="plannedEnd" column="planned_end"/>
    <result property="startedAt" column="started_at"/>
    <result property="endedAt" column="ended_at"/>
    <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="jobMap">
    SELECT j.*, it.item_name FROM mes_job j
    LEFT JOIN mes_item it ON it.item_code = j.item_code
    <where>
      <if test="status != null and status != ''">AND j.status = #{status}</if>
      <if test="workerId != null and workerId != ''">AND j.worker_id = #{workerId}</if>
      <if test="woNo != null and woNo != ''">AND j.wo_no = #{woNo}</if>
      <if test="equipmentCode != null and equipmentCode != ''">AND j.equipment_code = #{equipmentCode}</if>
    </where>
    ORDER BY j.id DESC
  </select>

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

  <select id="countByNo" resultType="int">SELECT COUNT(*) FROM mes_job WHERE job_no = #{jobNo}</select>

  <insert id="insert" parameterType="com.zioinfo.mes.job.MesJob" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mes_job (job_no, workorder_id, wo_no, item_code, op_seq, process_code, process_name,
      equipment_code, worker_id, assigned_qty, done_qty, status, planned_start, planned_end, remark, created_by)
    VALUES (#{jobNo}, #{workorderId}, #{woNo}, #{itemCode}, #{opSeq}, #{processCode}, #{processName},
      #{equipmentCode}, #{workerId}, #{assignedQty}, 0, #{status}, #{plannedStart}, #{plannedEnd}, #{remark}, #{createdBy})
  </insert>

  <update id="update" parameterType="com.zioinfo.mes.job.MesJob">
    UPDATE mes_job SET op_seq = #{opSeq}, process_code = #{processCode}, process_name = #{processName},
      assigned_qty = #{assignedQty}, planned_start = #{plannedStart}, planned_end = #{plannedEnd},
      remark = #{remark}, updated_at = NOW()
    WHERE id = #{id}
  </update>

  <update id="assign">
    UPDATE mes_job SET worker_id = #{workerId}, equipment_code = #{equipmentCode}, updated_at = NOW()
    WHERE id = #{id}
  </update>

  <update id="updateStatus">
    UPDATE mes_job
    SET status = #{status},
        started_at = CASE WHEN #{status} = 'STARTED' AND started_at IS NULL THEN NOW() ELSE started_at END,
        ended_at = CASE WHEN #{status} = 'DONE' THEN NOW() ELSE ended_at END,
        updated_at = NOW()
    WHERE id = #{id}
  </update>

  <update id="addDone">
    UPDATE mes_job SET done_qty = COALESCE(done_qty,0) + #{qty}, updated_at = NOW() WHERE id = #{id}
  </update>

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

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

  <select id="workloadByWorker" resultType="map">
    SELECT worker_id, COUNT(*) AS active_jobs, COALESCE(SUM(assigned_qty - COALESCE(done_qty,0)),0) AS remaining_qty
    FROM mes_job
    WHERE status IN ('ASSIGNED','STARTED','PAUSED') AND worker_id IS NOT NULL
    GROUP BY worker_id ORDER BY active_jobs DESC
  </select>

</mapper>
