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

  <!-- ── 동적 검색 조건 (alias si. 사용, companyCodeFilter 직접 작성) ── -->
  <sql id="shippingOrderSearchCondition">
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (si.company_code = #{company_code} OR si.company_code = '*')
    </if>
    <if test="date_from != null and date_from != ''">
      AND si.instruction_date &gt;= #{date_from}::date
    </if>
    <if test="date_to != null and date_to != ''">
      AND si.instruction_date &lt;= #{date_to}::date
    </if>
    <if test="status != null and status != ''">
      AND si.status = #{status}
    </if>
    <if test="customer != null and customer != ''">
      AND (c.customer_name ILIKE '%' || #{customer} || '%'
           OR si.partner_id ILIKE '%' || #{customer} || '%')
    </if>
    <if test="keyword != null and keyword != ''">
      AND (si.instruction_no ILIKE '%' || #{keyword} || '%'
           OR si.memo ILIKE '%' || #{keyword} || '%')
    </if>
  </sql>

  <!-- ── 출하지시 목록 (JSON_AGG 포함) ── -->
  <select id="getShippingOrderList" parameterType="map" resultType="map">
    SELECT
      si.*,
      COALESCE(c.customer_name, si.partner_id, '') AS customer_name,
      COALESCE(
        JSON_AGG(
          JSON_BUILD_OBJECT(
            'id',               sid.id,
            'item_code',        sid.item_code,
            'item_name',        COALESCE(i.item_name, sid.item_name, sid.item_code),
            'spec',             sid.spec,
            'material',         sid.material,
            'order_qty',        sid.order_qty,
            'plan_qty',         sid.plan_qty,
            'ship_qty',         sid.ship_qty,
            'source_type',      sid.source_type,
            'shipment_plan_id', sid.shipment_plan_id,
            'sales_order_id',   sid.sales_order_id,
            'detail_id',        sid.detail_id
          )
        ) FILTER (WHERE sid.id IS NOT NULL),
        '[]'
      ) AS items

    FROM shipment_instruction si
    LEFT JOIN customer_mng c
      ON si.partner_id = c.customer_code AND si.company_code = c.company_code
    LEFT JOIN shipment_instruction_detail sid
      ON si.id = sid.instruction_id AND si.company_code = sid.company_code
    LEFT JOIN LATERAL (
      SELECT item_name FROM item_info
      WHERE item_number = sid.item_code AND company_code = si.company_code
      LIMIT 1
    ) i ON TRUE

    WHERE 1=1
    <include refid="shippingOrderSearchCondition"/>

    GROUP BY si.id, c.customer_name
    ORDER BY si.created_date DESC
  </select>

  <select id="getShippingOrderListCnt" parameterType="map" resultType="int">
    SELECT COUNT(DISTINCT si.id)

    FROM shipment_instruction si
    LEFT JOIN customer_mng c
      ON si.partner_id = c.customer_code AND si.company_code = c.company_code

    WHERE 1=1
    <include refid="shippingOrderSearchCondition"/>
  </select>

  <select id="getShippingOrderInfo" parameterType="map" resultType="map">
    SELECT *

    FROM shipment_instruction

    WHERE id = #{id}
      AND (company_code = #{company_code} OR company_code = '*')
  </select>

  <select id="getShippingOrderSeqNo" parameterType="map" resultType="map">
    SELECT COUNT(*) + 1 AS seq

    FROM shipment_instruction

    WHERE (company_code = #{company_code} OR company_code = '*')
      AND instruction_no LIKE #{like_pattern}
  </select>

  <insert id="insertShippingOrder" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO shipment_instruction (
      company_code, instruction_no, instruction_date, partner_id, status, memo,
      carrier_name, vehicle_no, driver_name, driver_contact,
      arrival_time, delivery_address,
      created_date, created_by
    ) VALUES (
      #{company_code}, #{instruction_no}, #{instruction_date}::date, #{partner_id},
      COALESCE(#{status}, 'READY'), #{memo},
      #{carrier_name}, #{vehicle_no}, #{driver_name}, #{driver_contact},
      #{arrival_time}, #{delivery_address},
      NOW(), #{created_by}
    )
  </insert>

  <update id="updateShippingOrder" parameterType="map">
    UPDATE shipment_instruction

    SET
      instruction_date  = #{instruction_date}::date,
      partner_id        = #{partner_id},
      status            = COALESCE(#{status}, 'READY'),
      memo              = #{memo},
      carrier_name      = #{carrier_name},
      vehicle_no        = #{vehicle_no},
      driver_name       = #{driver_name},
      driver_contact    = #{driver_contact},
      arrival_time      = #{arrival_time},
      delivery_address  = #{delivery_address},
      updated_date      = NOW(),
      updated_by        = #{updated_by}

    WHERE id = #{id}
      AND (company_code = #{company_code} OR company_code = '*')
  </update>

  <delete id="deleteShippingOrderDetails" parameterType="map">
    DELETE FROM shipment_instruction_detail

    WHERE instruction_id = #{instruction_id}
      AND (company_code = #{company_code} OR company_code = '*')
  </delete>

  <delete id="deleteShippingOrders" parameterType="map">
    DELETE FROM shipment_instruction

    WHERE (company_code = #{company_code} OR company_code = '*')
      AND id IN
      <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
      </foreach>
  </delete>

  <delete id="deleteShippingOrder" parameterType="map">
    DELETE FROM shipment_instruction

    WHERE id = #{id}
      AND (company_code = #{company_code} OR company_code = '*')
  </delete>

  <insert id="insertShippingOrderDetail" parameterType="map">
    INSERT INTO shipment_instruction_detail (
      company_code, instruction_id,
      shipment_plan_id, sales_order_id, detail_id,
      item_code, item_name, spec, material,
      order_qty, plan_qty, ship_qty,
      source_type, created_date, created_by
    ) VALUES (
      #{company_code}, #{instruction_id},
      #{shipment_plan_id}, #{sales_order_id}, #{detail_id},
      #{item_code}, #{item_name}, #{spec}, #{material},
      COALESCE(#{order_qty}, 0), COALESCE(#{plan_qty}, 0), COALESCE(#{ship_qty}, 0),
      COALESCE(#{source_type}, 'shipmentPlan'), NOW(), #{created_by}
    )
  </insert>

  <!-- ── 출하계획 소스 (shipment_plan) ── -->
  <sql id="shipmentPlanSourceFrom">
    FROM shipment_plan sp
    LEFT JOIN sales_order_detail d ON sp.detail_id = d.id AND sp.company_code = d.company_code
    LEFT JOIN sales_order_mng m ON sp.sales_order_id = m.id AND sp.company_code = m.company_code
    LEFT JOIN LATERAL (
      SELECT item_name FROM item_info
      WHERE item_number = COALESCE(d.part_code, m.part_code)
        AND company_code = sp.company_code
      LIMIT 1
    ) i ON TRUE
    LEFT JOIN customer_mng c
      ON COALESCE(m.partner_id, d.delivery_partner_code) = c.customer_code
      AND sp.company_code = c.company_code
    WHERE (sp.company_code = #{company_code} OR sp.company_code = '*')
      AND sp.status = 'READY'
    <if test="keyword != null and keyword != ''">
      AND (COALESCE(d.part_code, m.part_code, '') ILIKE '%' || #{keyword} || '%'
           OR COALESCE(i.item_name, d.part_name, m.part_name, '') ILIKE '%' || #{keyword} || '%')
    </if>
    <if test="customer != null and customer != ''">
      AND (c.customer_name ILIKE '%' || #{customer} || '%'
           OR COALESCE(m.partner_id, d.delivery_partner_code, '') ILIKE '%' || #{customer} || '%')
    </if>
  </sql>

  <select id="getShipmentPlanSourceList" parameterType="map" resultType="map">
    SELECT
      sp.id, sp.plan_qty, sp.plan_date, sp.status, sp.shipment_plan_no,
      COALESCE(m.order_no, d.order_no, '') AS order_no,
      COALESCE(d.part_code, m.part_code, '') AS item_code,
      COALESCE(i.item_name, d.part_name, m.part_name,
               COALESCE(d.part_code, m.part_code, '')) AS item_name,
      COALESCE(d.spec, m.spec, '') AS spec,
      COALESCE(m.material, '') AS material,
      COALESCE(c.customer_name, m.partner_id, d.delivery_partner_code, '') AS customer_name,
      COALESCE(m.partner_id, d.delivery_partner_code, '') AS partner_code,
      sp.detail_id, sp.sales_order_id
    <include refid="shipmentPlanSourceFrom"/>

    ORDER BY sp.created_date DESC
    LIMIT #{page_size} OFFSET #{offset}
  </select>

  <select id="getShipmentPlanSourceCnt" parameterType="map" resultType="int">
    SELECT COUNT(*)
    <include refid="shipmentPlanSourceFrom"/>
  </select>

  <!-- ── 수주 소스 (sales_order_detail) ── -->
  <sql id="salesOrderSourceFrom">
    FROM sales_order_detail d
    LEFT JOIN sales_order_mng m ON d.order_no = m.order_no AND d.company_code = m.company_code
    LEFT JOIN LATERAL (
      SELECT item_name FROM item_info
      WHERE item_number = d.part_code AND company_code = d.company_code
      LIMIT 1
    ) i ON TRUE
    LEFT JOIN customer_mng c
      ON COALESCE(d.delivery_partner_code, m.partner_id) = c.customer_code
      AND d.company_code = c.company_code
    WHERE (d.company_code = #{company_code} OR d.company_code = '*')
    <if test="keyword != null and keyword != ''">
      AND (d.part_code ILIKE '%' || #{keyword} || '%'
           OR COALESCE(i.item_name, d.part_name, d.part_code) ILIKE '%' || #{keyword} || '%'
           OR d.order_no ILIKE '%' || #{keyword} || '%')
    </if>
    <if test="customer != null and customer != ''">
      AND (c.customer_name ILIKE '%' || #{customer} || '%'
           OR COALESCE(d.delivery_partner_code, m.partner_id, '') ILIKE '%' || #{customer} || '%')
    </if>
  </sql>

  <select id="getSalesOrderSourceList" parameterType="map" resultType="map">
    SELECT
      d.id, d.order_no, d.part_code AS item_code,
      COALESCE(i.item_name, d.part_name, d.part_code) AS item_name,
      COALESCE(d.spec, '') AS spec,
      COALESCE(m.material, '') AS material,
      COALESCE(d.delivery_partner_code, m.partner_id, '') AS partner_code,
      COALESCE(c.customer_name, d.delivery_partner_code, m.partner_id, '') AS customer_name,
      m.id AS master_id
    <include refid="salesOrderSourceFrom"/>

    ORDER BY d.created_date DESC
    LIMIT #{page_size} OFFSET #{offset}
  </select>

  <select id="getSalesOrderSourceCnt" parameterType="map" resultType="int">
    SELECT COUNT(*)
    <include refid="salesOrderSourceFrom"/>
  </select>

  <!-- ── 품목 소스 (item_info) ── -->
  <sql id="itemSourceFrom">
    FROM item_info
    WHERE (company_code = #{company_code} OR company_code = '*')
    <if test="keyword != null and keyword != ''">
      AND (item_number ILIKE '%' || #{keyword} || '%'
           OR item_name ILIKE '%' || #{keyword} || '%')
    </if>
  </sql>

  <select id="getItemSourceList" parameterType="map" resultType="map">
    SELECT
      item_number AS item_code,
      item_name,
      COALESCE(size, '') AS spec,
      COALESCE(material, '') AS material
    <include refid="itemSourceFrom"/>

    ORDER BY item_name
    LIMIT #{page_size} OFFSET #{offset}
  </select>

  <select id="getItemSourceCnt" parameterType="map" resultType="int">
    SELECT COUNT(*)
    <include refid="itemSourceFrom"/>
  </select>

</mapper>
