<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="processWorkStandard">

  <sql id="processWorkStandardItemSearchCondition">
    <if test="keyword != null and keyword != ''">
      AND (i.item_code ILIKE '%' || #{keyword} || '%' OR i.item_name ILIKE '%' || #{keyword} || '%')
    </if>
    <if test="item_type != null and item_type != ''">
      AND i.item_type = #{item_type}
    </if>
  </sql>

  <select id="getProcessWorkStandardItemList" parameterType="map" resultType="map">
    SELECT
      i.item_code, i.item_name, i.item_type, i.unit,
      COUNT(pr.id) AS routing_count,
      MAX(CASE WHEN pr.is_default THEN pr.version_no ELSE NULL END) AS default_version

    FROM items i

    LEFT JOIN process_routing pr ON i.item_code = pr.item_code

    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (i.company_code = #{company_code} OR i.company_code = '*')
    </if>
    <include refid="processWorkStandardItemSearchCondition"/>

    GROUP BY i.item_code, i.item_name, i.item_type, i.unit
    <include refid="common.dynamicOrderBy"/>
    <include refid="common.pagination"/>
  </select>

  <select id="getProcessWorkStandardItemListCnt" parameterType="map" resultType="int">
    SELECT COUNT(*) FROM (
      SELECT i.item_code
      FROM items i
      LEFT JOIN process_routing pr ON i.item_code = pr.item_code
      WHERE 1=1
      <if test="company_code != null and company_code != &quot;*&quot;">
        AND (i.company_code = #{company_code} OR i.company_code = '*')
      </if>
      <include refid="processWorkStandardItemSearchCondition"/>
      GROUP BY i.item_code
    ) sub
  </select>

  <select id="getProcessWorkStandardRoutingList" parameterType="map" resultType="map">
    SELECT
      pr.*,
      COUNT(prd.id) AS detail_count

    FROM process_routing pr

    LEFT JOIN process_routing_detail prd ON pr.id = prd.routing_id

    WHERE pr.item_code = #{item_code}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (pr.company_code = #{company_code} OR pr.company_code = '*')
    </if>

    GROUP BY pr.id

    ORDER BY pr.version_no ASC
  </select>

  <update id="setProcessWorkStandardDefaultVersion" parameterType="map">
    UPDATE process_routing
    SET is_default = CASE WHEN id = #{version_id} THEN TRUE ELSE FALSE END

    WHERE item_code = (SELECT item_code FROM process_routing WHERE id = #{version_id})
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (company_code = #{company_code} OR company_code = '*')
    </if>
  </update>

  <update id="unsetProcessWorkStandardDefaultVersion" parameterType="map">
    UPDATE process_routing
    SET is_default = FALSE

    WHERE id = #{version_id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (company_code = #{company_code} OR company_code = '*')
    </if>
  </update>

  <select id="getProcessWorkStandardWorkItemList" parameterType="map" resultType="map">
    SELECT
      pwi.*,
      prd.process_code, prd.process_name, prd.sequence_no AS routing_sequence_no

    FROM process_work_item pwi

    LEFT JOIN process_routing_detail prd ON pwi.routing_detail_id = prd.id

    WHERE pwi.routing_detail_id = #{routing_detail_id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (pwi.company_code = #{company_code} OR pwi.company_code = '*')
    </if>

    ORDER BY pwi.sequence_no ASC
  </select>

  <insert id="insertProcessWorkStandardWorkItem" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO process_work_item (
      company_code, routing_detail_id, work_item_code, work_item_name,
      sequence_no, work_time, description, CREATED_DATE, UPDATED_DATE
    ) VALUES (
      #{company_code}, #{routing_detail_id}, #{work_item_code}, #{work_item_name},
      COALESCE(#{sequence_no}, 0), COALESCE(#{work_time}, 0), #{description}, NOW(), NOW()
    )
  </insert>

  <update id="updateProcessWorkStandardWorkItem" parameterType="map">
    UPDATE process_work_item
    <set>
      <if test="work_item_name != null">work_item_name = #{work_item_name},</if>
      <if test="sequence_no != null">sequence_no = #{sequence_no},</if>
      <if test="work_time != null">work_time = #{work_time},</if>
      <if test="description != null">description = #{description},</if>
      UPDATED_DATE = NOW()
    </set>

    WHERE id = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (company_code = #{company_code} OR company_code = '*')
    </if>
  </update>

  <delete id="deleteProcessWorkStandardWorkItem" parameterType="map">
    DELETE FROM process_work_item

    WHERE id = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (company_code = #{company_code} OR company_code = '*')
    </if>
  </delete>

  <select id="getProcessWorkStandardWorkItemDetailList" parameterType="map" resultType="map">
    SELECT * FROM process_work_item_detail

    WHERE work_item_id = #{work_item_id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (company_code = #{company_code} OR company_code = '*')
    </if>

    ORDER BY detail_no ASC
  </select>

  <insert id="insertProcessWorkStandardWorkItemDetail" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO process_work_item_detail (
      company_code, work_item_id, detail_no, content,
      standard_time, quality_point, safety_note, CREATED_DATE, UPDATED_DATE
    ) VALUES (
      #{company_code}, #{work_item_id}, COALESCE(#{detail_no}, 1), #{content},
      COALESCE(#{standard_time}, 0), #{quality_point}, #{safety_note}, NOW(), NOW()
    )
  </insert>

  <update id="updateProcessWorkStandardWorkItemDetail" parameterType="map">
    UPDATE process_work_item_detail
    <set>
      <if test="detail_no != null">detail_no = #{detail_no},</if>
      <if test="content != null">content = #{content},</if>
      <if test="standard_time != null">standard_time = #{standard_time},</if>
      <if test="quality_point != null">quality_point = #{quality_point},</if>
      <if test="safety_note != null">safety_note = #{safety_note},</if>
      UPDATED_DATE = NOW()
    </set>

    WHERE id = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (company_code = #{company_code} OR company_code = '*')
    </if>
  </update>

  <delete id="deleteProcessWorkStandardWorkItemDetail" parameterType="map">
    DELETE FROM process_work_item_detail

    WHERE id = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (company_code = #{company_code} OR company_code = '*')
    </if>
  </delete>

  <select id="getProcessWorkStandardRegisteredItemList" parameterType="map" resultType="map">
    SELECT pri.*, i.item_name, i.item_type, i.unit

    FROM process_registered_item pri

    LEFT JOIN items i ON pri.item_code = i.item_code

    WHERE pri.screen_code = #{screen_code}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (pri.company_code = #{company_code} OR pri.company_code = '*')
    </if>

    ORDER BY pri.id ASC
  </select>

  <insert id="insertProcessWorkStandardRegisteredItem" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO process_registered_item (
      company_code, screen_code, item_code, routing_id, CREATED_DATE, UPDATED_DATE
    ) VALUES (
      #{company_code}, #{screen_code}, #{item_code}, #{routing_id}, NOW(), NOW()
    )
  </insert>

  <insert id="insertProcessWorkStandardRegisteredItemBatch" parameterType="map">
    INSERT INTO process_registered_item (
      company_code, screen_code, item_code, routing_id, CREATED_DATE, UPDATED_DATE
    ) VALUES
    <foreach collection="items" item="item" separator=",">
      (#{company_code}, #{item.screenCode}, #{item.itemCode}, #{item.routingId}, NOW(), NOW())
    </foreach>
  </insert>

  <delete id="deleteProcessWorkStandardRegisteredItem" parameterType="map">
    DELETE FROM process_registered_item

    WHERE id = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (company_code = #{company_code} OR company_code = '*')
    </if>
  </delete>

</mapper>
