<?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="mold">

  <!-- ── 검색 조건 ────────────────────────────────────────────────────────── -->
  <sql id="moldSearchCondition">
    <if test="mold_code != null and mold_code != ''">
      AND mold_code ILIKE '%' || #{mold_code} || '%'
    </if>
    <if test="mold_name != null and mold_name != ''">
      AND mold_name ILIKE '%' || #{mold_name} || '%'
    </if>
    <if test="mold_type != null and mold_type != ''">
      AND mold_type = #{mold_type}
    </if>
    <if test="operation_status != null and operation_status != ''">
      AND operation_status = #{operation_status}
    </if>
    <if test="keyword != null and keyword != ''">
      AND (mold_code ILIKE '%' || #{keyword} || '%'
        OR mold_name ILIKE '%' || #{keyword} || '%')
    </if>
  </sql>

  <!-- ── 금형 목록 ─────────────────────────────────────────────────────────── -->
  <select id="getMoldList" parameterType="map" resultType="map">
    SELECT *

    FROM mold_mng

    WHERE 1=1
    <include refid="common.companyCodeFilter"/>
    <include refid="moldSearchCondition"/>

    ORDER BY created_date DESC
    <include refid="common.pagination"/>
  </select>

  <select id="getMoldListCnt" parameterType="map" resultType="int">
    SELECT COUNT(*)

    FROM mold_mng

    WHERE 1=1
    <include refid="common.companyCodeFilter"/>
    <include refid="moldSearchCondition"/>
  </select>

  <!-- ── 금형 단건 ─────────────────────────────────────────────────────────── -->
  <select id="getMoldInfo" parameterType="map" resultType="map">
    SELECT *

    FROM mold_mng

    WHERE mold_code = #{mold_code}
    <include refid="common.companyCodeFilter"/>

    LIMIT 1
  </select>

  <!-- ── 금형 등록 ─────────────────────────────────────────────────────────── -->
  <insert id="insertMold" parameterType="map">
    INSERT INTO mold_mng (
      company_code, mold_code, mold_name, mold_type, category,
      manufacturer, manufacturing_number, manufacturing_date,
      cavity_count, shot_count, mold_quantity, base_input_qty,
      operation_status, remarks, image_path, memo, writer
    ) VALUES (
      #{company_code}, #{mold_code}, #{mold_name}, #{mold_type}, #{category},
      #{manufacturer}, #{manufacturing_number}, #{manufacturing_date},
      #{cavity_count, jdbcType=INTEGER}, #{shot_count, jdbcType=INTEGER},
      #{mold_quantity, jdbcType=INTEGER}, #{base_input_qty, jdbcType=INTEGER},
      #{operation_status}, #{remarks}, #{image_path}, #{memo}, #{writer}
    )
  </insert>

  <!-- ── 금형 수정 ─────────────────────────────────────────────────────────── -->
  <update id="updateMold" parameterType="map">
    UPDATE mold_mng SET
      mold_name = COALESCE(#{mold_name}, mold_name),
      mold_type = #{mold_type},
      category = #{category},
      manufacturer = #{manufacturer},
      manufacturing_number = #{manufacturing_number},
      manufacturing_date = #{manufacturing_date},
      cavity_count = COALESCE(#{cavity_count, jdbcType=INTEGER}, cavity_count),
      shot_count = COALESCE(#{shot_count, jdbcType=INTEGER}, shot_count),
      mold_quantity = COALESCE(#{mold_quantity, jdbcType=INTEGER}, mold_quantity),
      base_input_qty = COALESCE(#{base_input_qty, jdbcType=INTEGER}, base_input_qty),
      operation_status = COALESCE(#{operation_status}, operation_status),
      remarks = #{remarks},
      image_path = #{image_path},
      memo = #{memo},
      updated_date = NOW()

    WHERE mold_code = #{mold_code}
    <include refid="common.companyCodeFilter"/>
  </update>

  <!-- ── 금형 삭제 ─────────────────────────────────────────────────────────── -->
  <delete id="deleteMold" parameterType="map">
    DELETE FROM mold_mng

    WHERE mold_code = #{mold_code}
    <include refid="common.companyCodeFilter"/>
  </delete>

  <delete id="deleteMoldSerialByMoldCode" parameterType="map">
    DELETE FROM mold_serial

    WHERE mold_code = #{mold_code}
    <include refid="common.companyCodeFilter"/>
  </delete>

  <delete id="deleteMoldInspectionByMoldCode" parameterType="map">
    DELETE FROM mold_inspection_item

    WHERE mold_code = #{mold_code}
    <include refid="common.companyCodeFilter"/>
  </delete>

  <delete id="deleteMoldPartByMoldCode" parameterType="map">
    DELETE FROM mold_part

    WHERE mold_code = #{mold_code}
    <include refid="common.companyCodeFilter"/>
  </delete>

  <!-- ── 일련번호 ───────────────────────────────────────────────────────────── -->
  <select id="getMoldSerialList" parameterType="map" resultType="map">
    SELECT *

    FROM mold_serial

    WHERE mold_code = #{mold_code}
    <include refid="common.companyCodeFilter"/>

    ORDER BY serial_number
  </select>

  <insert id="insertMoldSerial" parameterType="map">
    INSERT INTO mold_serial (
      company_code, mold_code, serial_number, status, progress,
      work_description, manager, completion_date, remarks, writer
    ) VALUES (
      #{company_code}, #{mold_code}, #{serial_number}, #{status},
      #{progress, jdbcType=INTEGER}, #{work_description}, #{manager},
      #{completion_date}, #{remarks}, #{writer}
    )
  </insert>

  <delete id="deleteMoldSerial" parameterType="map">
    DELETE FROM mold_serial

    WHERE id = #{id}
    <include refid="common.companyCodeFilter"/>
  </delete>

  <select id="getMoldSerialSummary" parameterType="map" resultType="map">
    SELECT
      COUNT(*) AS total,
      COUNT(*) FILTER (WHERE status = 'IN_USE') AS in_use,
      COUNT(*) FILTER (WHERE status = 'REPAIR') AS repair,
      COUNT(*) FILTER (WHERE status = 'STORED') AS stored,
      COUNT(*) FILTER (WHERE status = 'DISPOSED') AS disposed

    FROM mold_serial

    WHERE mold_code = #{mold_code}
    <include refid="common.companyCodeFilter"/>
  </select>

  <!-- ── 점검항목 ───────────────────────────────────────────────────────────── -->
  <select id="getMoldInspectionList" parameterType="map" resultType="map">
    SELECT *

    FROM mold_inspection_item

    WHERE mold_code = #{mold_code}
    <include refid="common.companyCodeFilter"/>

    ORDER BY created_date
  </select>

  <insert id="insertMoldInspection" parameterType="map">
    INSERT INTO mold_inspection_item (
      company_code, mold_code, inspection_item, inspection_cycle,
      inspection_method, inspection_content, lower_limit, upper_limit,
      unit, is_active, checklist, remarks, writer
    ) VALUES (
      #{company_code}, #{mold_code}, #{inspection_item}, #{inspection_cycle},
      #{inspection_method}, #{inspection_content}, #{lower_limit}, #{upper_limit},
      #{unit}, #{is_active}, #{checklist}, #{remarks}, #{writer}
    )
  </insert>

  <delete id="deleteMoldInspection" parameterType="map">
    DELETE FROM mold_inspection_item

    WHERE id = #{id}
    <include refid="common.companyCodeFilter"/>
  </delete>

  <!-- ── 부품 ──────────────────────────────────────────────────────────────── -->
  <select id="getMoldPartList" parameterType="map" resultType="map">
    SELECT *

    FROM mold_part

    WHERE mold_code = #{mold_code}
    <include refid="common.companyCodeFilter"/>

    ORDER BY created_date
  </select>

  <insert id="insertMoldPart" parameterType="map">
    INSERT INTO mold_part (
      company_code, mold_code, part_name, replacement_cycle,
      unit, specification, manufacturer, manufacturer_code,
      image_path, remarks, writer
    ) VALUES (
      #{company_code}, #{mold_code}, #{part_name}, #{replacement_cycle},
      #{unit}, #{specification}, #{manufacturer}, #{manufacturer_code},
      #{image_path}, #{remarks}, #{writer}
    )
  </insert>

  <delete id="deleteMoldPart" parameterType="map">
    DELETE FROM mold_part

    WHERE id = #{id}
    <include refid="common.companyCodeFilter"/>
  </delete>

</mapper>
