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

  <!-- JOIN 기반 FROM 절 -->
  <sql id="salesReportJoinBase">
    FROM SALES_ORDER_MNG SOM
    JOIN SALES_ORDER_DETAIL SOD
      ON SOM.ORDER_NO = SOD.ORDER_NO
      AND SOM.COMPANY_CODE = SOD.COMPANY_CODE
    LEFT JOIN CUSTOMER_MNG CM
      ON SOM.PARTNER_ID = CM.CUSTOMER_CODE
      AND SOM.COMPANY_CODE = CM.COMPANY_CODE
    LEFT JOIN (
      SELECT DISTINCT ON (ITEM_NUMBER, COMPANY_CODE)
        ITEM_NUMBER, ITEM_NAME, COMPANY_CODE
      FROM ITEM_INFO
      ORDER BY ITEM_NUMBER, COMPANY_CODE, CREATED_DATE DESC
    ) II
      ON SOD.PART_CODE = II.ITEM_NUMBER
      AND SOD.COMPANY_CODE = II.COMPANY_CODE
  </sql>

  <!-- 검색 조건 (table alias 포함) -->
  <sql id="salesReportSearchCondition">
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (SOM.COMPANY_CODE = #{company_code} OR SOM.COMPANY_CODE = '*')
    </if>
    <if test="start_date != null and start_date != ''">
      AND COALESCE(SOD.DUE_DATE, SOM.ORDER_DATE::TEXT, SOM.CREATED_DATE::DATE::TEXT) &gt;= #{start_date}
    </if>
    <if test="end_date != null and end_date != ''">
      AND COALESCE(SOD.DUE_DATE, SOM.ORDER_DATE::TEXT, SOM.CREATED_DATE::DATE::TEXT) &lt;= #{end_date}
    </if>
  </sql>

  <!-- 목록 조회 (페이지네이션) -->
  <select id="getSalesReportList" parameterType="map" resultType="map">
    SELECT
      SOM.ORDER_NO,
      COALESCE(SOD.DUE_DATE, SOM.ORDER_DATE::TEXT, SOM.CREATED_DATE::DATE::TEXT) AS DATE,
      SOM.ORDER_DATE,
      SOM.PARTNER_ID,
      COALESCE(CM.CUSTOMER_NAME, SOM.PARTNER_ID, '미지정') AS CUSTOMER,
      SOD.PART_CODE,
      COALESCE(II.ITEM_NAME, SOD.PART_NAME, SOD.PART_CODE, '미지정') AS ITEM,
      CAST(COALESCE(NULLIF(SOD.QTY, ''), '0') AS NUMERIC) AS ORDER_QTY,
      CAST(COALESCE(NULLIF(SOD.SHIP_QTY, ''), '0') AS NUMERIC) AS SHIP_QTY,
      CAST(COALESCE(NULLIF(SOD.UNIT_PRICE, ''), '0') AS NUMERIC) AS UNIT_PRICE,
      CAST(COALESCE(NULLIF(SOD.AMOUNT, ''), '0') AS NUMERIC) AS ORDER_AMT,
      1 AS ORDER_COUNT,
      SOM.STATUS,
      SOM.COMPANY_CODE
    <include refid="salesReportJoinBase"/>
    WHERE 1=1
    <include refid="salesReportSearchCondition"/>
    ORDER BY DATE DESC NULLS LAST
    <include refid="common.pagination"/>
  </select>

  <!-- 목록 카운트 -->
  <select id="getSalesReportListCnt" parameterType="map" resultType="int">
    SELECT COUNT(*)
    <include refid="salesReportJoinBase"/>
    WHERE 1=1
    <include refid="salesReportSearchCondition"/>
  </select>

  <!-- 집계 요약 (BigDecimal 처리용) -->
  <select id="getSalesReportSummary" parameterType="map" resultType="map">
    SELECT
      COUNT(*) AS TOTAL_COUNT,
      COALESCE(SUM(CAST(COALESCE(NULLIF(SOD.AMOUNT, ''), '0') AS NUMERIC)), 0) AS TOTAL_AMOUNT,
      COALESCE(AVG(CAST(COALESCE(NULLIF(SOD.UNIT_PRICE, ''), '0') AS NUMERIC)), 0) AS AVG_UNIT_PRICE,
      COALESCE(SUM(CAST(COALESCE(NULLIF(SOD.QTY, ''), '0') AS NUMERIC)), 0) AS TOTAL_QTY,
      COALESCE(SUM(CAST(COALESCE(NULLIF(SOD.SHIP_QTY, ''), '0') AS NUMERIC)), 0) AS TOTAL_SHIP_QTY
    <include refid="salesReportJoinBase"/>
    WHERE 1=1
    <include refid="salesReportSearchCondition"/>
  </select>

  <!-- 전체 원본 데이터 (GET /data용, 페이지네이션 없음) -->
  <select id="getSalesReportDataRows" parameterType="map" resultType="map">
    SELECT
      SOM.ORDER_NO,
      COALESCE(SOD.DUE_DATE, SOM.ORDER_DATE::TEXT, SOM.CREATED_DATE::DATE::TEXT) AS DATE,
      SOM.ORDER_DATE,
      SOM.PARTNER_ID,
      COALESCE(CM.CUSTOMER_NAME, SOM.PARTNER_ID, '미지정') AS CUSTOMER,
      SOD.PART_CODE,
      COALESCE(II.ITEM_NAME, SOD.PART_NAME, SOD.PART_CODE, '미지정') AS ITEM,
      CAST(COALESCE(NULLIF(SOD.QTY, ''), '0') AS NUMERIC) AS ORDER_QTY,
      CAST(COALESCE(NULLIF(SOD.SHIP_QTY, ''), '0') AS NUMERIC) AS SHIP_QTY,
      CAST(COALESCE(NULLIF(SOD.UNIT_PRICE, ''), '0') AS NUMERIC) AS UNIT_PRICE,
      CAST(COALESCE(NULLIF(SOD.AMOUNT, ''), '0') AS NUMERIC) AS ORDER_AMT,
      1 AS ORDER_COUNT,
      SOM.STATUS,
      SOM.COMPANY_CODE
    <include refid="salesReportJoinBase"/>
    WHERE 1=1
    <include refid="salesReportSearchCondition"/>
    ORDER BY DATE DESC NULLS LAST
  </select>

  <!-- 거래처 필터 옵션 -->
  <select id="getSalesReportCustomers" parameterType="map" resultType="map">
    SELECT DISTINCT CUSTOMER_CODE AS VALUE, CUSTOMER_NAME AS LABEL
    FROM CUSTOMER_MNG
    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
    ORDER BY CUSTOMER_NAME
  </select>

  <!-- 상태 필터 옵션 -->
  <select id="getSalesReportStatuses" parameterType="map" resultType="map">
    SELECT DISTINCT STATUS AS VALUE, STATUS AS LABEL
    FROM SALES_ORDER_MNG
    WHERE STATUS IS NOT NULL
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
    ORDER BY STATUS
  </select>

  <!-- 하위 호환 스텁 (sales_order_mng 기반 단건 조회) -->
  <select id="getSalesReportInfo" parameterType="map" resultType="map">
    SELECT
      SOM.ORDER_NO,
      SOM.COMPANY_CODE,
      SOM.STATUS,
      SOM.CREATED_DATE AS CREATED_DATE,
      SOM.UPDATED_DATE AS UPDATED_DATE
    FROM SALES_ORDER_MNG SOM
    WHERE SOM.ORDER_NO = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (SOM.COMPANY_CODE = #{company_code} OR SOM.COMPANY_CODE = '*')
    </if>
  </select>

  <insert id="insertSalesReport" parameterType="map">
    INSERT INTO SALES_ORDER_MNG (COMPANY_CODE, CREATED_DATE, UPDATED_DATE)
    VALUES (#{company_code}, NOW(), NOW())
  </insert>

  <update id="updateSalesReport" parameterType="map">
    UPDATE SALES_ORDER_MNG
    SET UPDATED_DATE = NOW()
    WHERE ORDER_NO = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
  </update>

  <delete id="deleteSalesReport" parameterType="map">
    DELETE FROM SALES_ORDER_MNG
    WHERE ORDER_NO = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
  </delete>

</mapper>
