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

  <!-- 생산 리포트 -->
  <select id="getProductionReportData" parameterType="map" resultType="map">
    SELECT
      COALESCE(wi.start_date, wi.created_date::DATE::TEXT) AS date,
      COALESCE(wi.routing, '미지정') AS process,
      COALESCE(ei.equipment_name, wi.equipment_id, '미지정') AS equipment,
      COALESCE(ii.item_name, wi.item_id, '미지정') AS item,
      COALESCE(wi.worker, '미지정') AS worker,
      CAST(COALESCE(NULLIF(wi.qty, ''), '0') AS NUMERIC) AS plan_qty,
      COALESCE(pr.production_qty, '0') AS prod_qty,
      COALESCE(pr.defect_qty, '0') AS defect_qty,
      0 AS run_time,
      0 AS down_time,
      wi.status,
      wi.company_code
    FROM work_instruction wi
    LEFT JOIN (
      SELECT wo_id, company_code,
        SUM(CAST(COALESCE(NULLIF(production_qty, ''), '0') AS NUMERIC)) AS production_qty,
        SUM(CAST(COALESCE(NULLIF(defect_qty, ''), '0') AS NUMERIC)) AS defect_qty
      FROM production_record
      GROUP BY wo_id, company_code
    ) pr ON wi.id = pr.wo_id AND wi.company_code = pr.company_code
    LEFT JOIN (
      SELECT DISTINCT ON (equipment_code, company_code)
        equipment_code, equipment_name, equipment_type, company_code
      FROM equipment_info
      ORDER BY equipment_code, company_code, created_date DESC
    ) ei ON wi.equipment_id = ei.equipment_code AND wi.company_code = ei.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 wi.item_id = ii.item_number AND wi.company_code = ii.company_code
    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (wi.company_code = #{company_code} OR wi.company_code = '*')
    </if>
    <if test="start_date != null and start_date != ''">
      AND COALESCE(wi.start_date, wi.created_date::DATE::TEXT) &gt;= #{start_date}
    </if>
    <if test="end_date != null and end_date != ''">
      AND COALESCE(wi.start_date, wi.created_date::DATE::TEXT) &lt;= #{end_date}
    </if>
    ORDER BY date DESC NULLS LAST
  </select>

  <!-- 재고 리포트 -->
  <select id="getInventoryReportData" parameterType="map" resultType="map">
    SELECT
      COALESCE(ist.updated_date, ist.created_date)::DATE::TEXT AS date,
      ist.item_code,
      COALESCE(ii.item_name, ist.item_code, '미지정') AS item,
      COALESCE(wi.warehouse_name, ist.warehouse_code, '미지정') AS warehouse,
      '일반' AS category,
      CAST(COALESCE(NULLIF(ist.current_qty::TEXT, ''), '0') AS NUMERIC) AS current_qty,
      CAST(COALESCE(NULLIF(ist.safety_qty::TEXT, ''), '0') AS NUMERIC) AS safety_qty,
      COALESCE(ih_in.in_qty, '0') AS in_qty,
      COALESCE(ih_out.out_qty, '0') AS out_qty,
      0 AS stock_value,
      GREATEST(
        CAST(COALESCE(NULLIF(ist.safety_qty::TEXT, ''), '0') AS NUMERIC)
        - CAST(COALESCE(NULLIF(ist.current_qty::TEXT, ''), '0') AS NUMERIC), 0
      ) AS shortage_qty,
      CASE
        WHEN CAST(COALESCE(NULLIF(ist.current_qty::TEXT, ''), '0') AS NUMERIC) &gt; 0
          AND COALESCE(ih_out.out_qty, '0') &gt; 0
        THEN ROUND(
          COALESCE(ih_out.out_qty, '0')::NUMERIC
          / CAST(COALESCE(NULLIF(ist.current_qty::TEXT, ''), '1') AS NUMERIC), 2)
        ELSE 0
      END AS turnover,
      ist.company_code
    FROM inventory_stock ist
    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 ist.item_code = ii.item_number AND ist.company_code = ii.company_code
    LEFT JOIN warehouse_info wi ON ist.warehouse_code = wi.warehouse_code
      AND ist.company_code = wi.company_code
    LEFT JOIN (
      SELECT item_code, company_code,
        SUM(CAST(COALESCE(NULLIF(quantity::TEXT, ''), '0') AS NUMERIC)) AS in_qty
      FROM inventory_history
      WHERE transaction_type = 'IN'
      GROUP BY item_code, company_code
    ) ih_in ON ist.item_code = ih_in.item_code AND ist.company_code = ih_in.company_code
    LEFT JOIN (
      SELECT item_code, company_code,
        SUM(CAST(COALESCE(NULLIF(quantity::TEXT, ''), '0') AS NUMERIC)) AS out_qty
      FROM inventory_history
      WHERE transaction_type = 'OUT'
      GROUP BY item_code, company_code
    ) ih_out ON ist.item_code = ih_out.item_code AND ist.company_code = ih_out.company_code
    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (ist.company_code = #{company_code} OR ist.company_code = '*')
    </if>
    ORDER BY date DESC NULLS LAST
  </select>

  <!-- 구매 리포트 -->
  <select id="getPurchaseReportData" parameterType="map" resultType="map">
    SELECT
      COALESCE(po.order_date, po.created_date::DATE::TEXT) AS date,
      po.purchase_no,
      COALESCE(po.supplier_name, po.supplier_code, '미지정') AS supplier,
      COALESCE(po.item_name, po.item_code, '미지정') AS item,
      po.item_code,
      COALESCE(po.manager, '미지정') AS manager,
      po.status,
      CAST(COALESCE(NULLIF(po.order_qty, ''), '0') AS NUMERIC) AS order_qty,
      CAST(COALESCE(NULLIF(po.received_qty, ''), '0') AS NUMERIC) AS receive_qty,
      CAST(COALESCE(NULLIF(po.unit_price, ''), '0') AS NUMERIC) AS unit_price,
      CAST(COALESCE(NULLIF(po.amount, ''), '0') AS NUMERIC) AS order_amt,
      CAST(COALESCE(NULLIF(po.received_qty, ''), '0') AS NUMERIC)
        * CAST(COALESCE(NULLIF(po.unit_price, ''), '0') AS NUMERIC) AS receive_amt,
      1 AS order_cnt,
      po.company_code
    FROM purchase_order_mng po
    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (po.company_code = #{company_code} OR po.company_code = '*')
    </if>
    <if test="start_date != null and start_date != ''">
      AND COALESCE(po.order_date, po.created_date::DATE::TEXT) &gt;= #{start_date}
    </if>
    <if test="end_date != null and end_date != ''">
      AND COALESCE(po.order_date, po.created_date::DATE::TEXT) &lt;= #{end_date}
    </if>
    ORDER BY date DESC NULLS LAST
  </select>

  <!-- 품질 리포트 -->
  <select id="getQualityReportData" parameterType="map" resultType="map">
    SELECT
      COALESCE(pr.production_date, pr.created_date::DATE::TEXT) AS date,
      COALESCE(ii.item_name, wi.item_id, '미지정') AS item,
      '일반검사' AS defect_type,
      COALESCE(wi.routing, '미지정') AS process,
      COALESCE(pr.worker_name, '미지정') AS inspector,
      CAST(COALESCE(NULLIF(pr.production_qty, ''), '0') AS NUMERIC) AS insp_qty,
      CAST(COALESCE(NULLIF(pr.production_qty, ''), '0') AS NUMERIC)
        - CAST(COALESCE(NULLIF(pr.defect_qty, ''), '0') AS NUMERIC) AS pass_qty,
      CAST(COALESCE(NULLIF(pr.defect_qty, ''), '0') AS NUMERIC) AS defect_qty,
      0 AS rework_qty,
      0 AS scrap_qty,
      0 AS claim_cnt,
      pr.company_code
    FROM production_record pr
    LEFT JOIN work_instruction wi ON pr.wo_id = wi.id AND pr.company_code = wi.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 wi.item_id = ii.item_number AND wi.company_code = ii.company_code
    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (pr.company_code = #{company_code} OR pr.company_code = '*')
    </if>
    <if test="start_date != null and start_date != ''">
      AND COALESCE(pr.production_date, pr.created_date::DATE::TEXT) &gt;= #{start_date}
    </if>
    <if test="end_date != null and end_date != ''">
      AND COALESCE(pr.production_date, pr.created_date::DATE::TEXT) &lt;= #{end_date}
    </if>
    ORDER BY date DESC NULLS LAST
  </select>

  <!-- 설비 리포트 -->
  <select id="getEquipmentReportData" parameterType="map" resultType="map">
    SELECT
      COALESCE(ei.updated_date, ei.created_date)::DATE::TEXT AS date,
      ei.equipment_code,
      COALESCE(ei.equipment_name, ei.equipment_code) AS equipment,
      COALESCE(ei.equipment_type, '미지정') AS equip_type,
      COALESCE(ei.location, '미지정') AS line,
      COALESCE(ui.user_name, ei.manager_id, '미지정') AS manager,
      ei.status,
      CAST(COALESCE(NULLIF(ei.capacity_per_day::TEXT, ''), '0') AS NUMERIC) AS run_time,
      0 AS down_time,
      100 AS op_rate,
      0 AS fault_cnt,
      0 AS mtbf,
      0 AS mttr,
      0 AS maint_cost,
      CAST(COALESCE(NULLIF(ei.capacity_per_day::TEXT, ''), '0') AS NUMERIC) AS prod_qty,
      ei.company_code
    FROM equipment_info ei
    LEFT JOIN (
      SELECT DISTINCT ON (user_id) user_id, user_name
      FROM user_info
    ) ui ON ei.manager_id = ui.user_id
    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (ei.company_code = #{company_code} OR ei.company_code = '*')
    </if>
    ORDER BY equipment ASC
  </select>

  <!-- 금형 리포트 -->
  <select id="getMoldReportData" parameterType="map" resultType="map">
    SELECT
      COALESCE(mm.updated_date, mm.created_date)::DATE::TEXT AS date,
      mm.mold_code,
      COALESCE(mm.mold_name, mm.mold_code) AS mold,
      COALESCE(mm.mold_type, mm.category, '미지정') AS mold_type,
      COALESCE(ii.item_name, '미지정') AS item,
      COALESCE(mm.manufacturer, '미지정') AS maker,
      mm.operation_status AS status,
      CAST(COALESCE(NULLIF(mm.shot_count::TEXT, ''), '0') AS NUMERIC) AS shot_cnt,
      CAST(COALESCE(NULLIF(mm.warranty_shot_count::TEXT, ''), '0') AS NUMERIC) AS guarantee_shot,
      CASE
        WHEN CAST(COALESCE(NULLIF(mm.warranty_shot_count::TEXT, ''), '0') AS NUMERIC) &gt; 0
        THEN ROUND(
          CAST(COALESCE(NULLIF(mm.shot_count::TEXT, ''), '0') AS NUMERIC) * 100.0
          / CAST(COALESCE(NULLIF(mm.warranty_shot_count::TEXT, ''), '1') AS NUMERIC), 1)
        ELSE 0
      END AS life_rate,
      0 AS repair_cnt,
      0 AS repair_cost,
      0 AS prod_qty,
      0 AS defect_rate,
      CAST(COALESCE(NULLIF(mm.cavity_count::TEXT, ''), '0') AS NUMERIC) AS cavity_use,
      mm.company_code
    FROM mold_mng mm
    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 mm.mold_code = ii.item_number AND mm.company_code = ii.company_code
    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (mm.company_code = #{company_code} OR mm.company_code = '*')
    </if>
    ORDER BY mold ASC
  </select>

</mapper>
