import { NextRequest, NextResponse } from "next/server";
import { queryRows } from "@/lib/db";
import { getSession } from "@/lib/session";

// 원본: purchaseOrder.xml#deliveryMngStatus
// 입고관리 > 현황 — 프로젝트 BOM 1개(part_bom_report) 단위 집계 리포트
// 4개 그룹: 프로젝트정보 / 발주내역 / 입고현황 / 수입검사결과(불량현황)
export async function POST(request: NextRequest) {
  const user = await getSession();
  if (!user) return NextResponse.json({ success: false }, { status: 401 });

  const body = await request.json();
  const conditions: string[] = [
    "SBR.parent_objid = PBR.objid",
    "PBR.contract_objid = CM.objid",
  ];
  const params: unknown[] = [];
  let idx = 1;

  if (body.Year) {
    conditions.push(`TO_CHAR(CM.regdate, 'YYYY') = $${idx++}`);
    params.push(body.Year);
  }
  if (body.customer_objid) {
    conditions.push(`CM.customer_objid = $${idx++}`);
    params.push(body.customer_objid);
  }

  // project_nos (array or CSV)
  const projectNos: string[] = Array.isArray(body.project_nos)
    ? body.project_nos.filter(Boolean).map(String)
    : body.project_nos
      ? String(body.project_nos).split(",").filter(Boolean)
      : [];
  if (projectNos.length > 0) {
    const placeholders = projectNos.map(() => `$${idx++}`).join(", ");
    conditions.push(`CM.objid::text IN (${placeholders})`);
    params.push(...projectNos);
  }

  if (body.unit_code) {
    conditions.push(`PBR.unit_code = $${idx++}`);
    params.push(body.unit_code);
  }

  const where = conditions.join(" AND ");

  const sql = `
    SELECT T.*,
      CASE WHEN "TOTAL_PO_PART_CNT"::numeric = 0 THEN 0
           WHEN "TOTAL_BOM_PART_CNT"::numeric = 0 THEN 0
           ELSE ROUND("TOTAL_PO_PART_CNT"::numeric / "TOTAL_BOM_PART_CNT"::numeric * 100, 1)
      END AS "PO_RATE",
      CASE WHEN "TOTAL_BOM_PART_CNT"::numeric - "TOTAL_PO_PART_CNT"::numeric < 0 THEN 0
           ELSE "TOTAL_BOM_PART_CNT"::numeric - "TOTAL_PO_PART_CNT"::numeric
      END AS "NON_PO_PART_CNT",
      "TOTAL_DELIVERY_QTY"::numeric AS "DELIVERY_QTY",
      ("TOTAL_PO_QTY"::numeric - "TOTAL_DELIVERY_QTY"::numeric) AS "NON_DELIVERY_QTY",
      CASE WHEN "TOTAL_DEFECT_QTY"::numeric = 0 THEN 0
           WHEN "TOTAL_PO_QTY"::numeric = 0 THEN 0
           ELSE ROUND("TOTAL_DEFECT_QTY"::numeric / "TOTAL_PO_QTY"::numeric * 100, 1)
      END AS "DELIVERY_RATE"
    FROM (
      SELECT CM.objid::text AS "OBJID",
        SBR.objid::text AS "SALES_OBJID",
        PBR.objid::text AS "BOM_REPORT_OBJID",
        TO_CHAR(CM.regdate, 'YYYY') AS "CM_YEAR",
        (SELECT supply_name FROM supply_mng SM WHERE SM.objid::text = CM.customer_objid LIMIT 1) AS "CUSTOMER_NAME",
        CM.customer_project_name AS "CUSTOMER_PROJECT_NAME",
        COALESCE(CM.project_no, CM.contract_no) AS "PROJECT_NO",
        (SELECT COALESCE(O.unit_no,'') || '-' || COALESCE(O.task_name,'') FROM pms_wbs_task O WHERE O.objid = PBR.unit_code LIMIT 1) AS "UNIT_PART_NAME",

        -- BOM 부품개수
        (SELECT COUNT(DISTINCT CASE WHEN PM.part_type IN (SELECT code_id FROM comm_code WHERE parent_code_id = '0000062') THEN BPQ.part_no ELSE NULL END)
           FROM bom_part_qty BPQ
           JOIN part_mng PM ON BPQ.part_no = PM.objid::text
          WHERE BPQ.bom_report_objid = PBR.objid::text
            AND BPQ.status IN ('beforeEdit','editing','deleting','deploy')
        ) AS "TOTAL_BOM_PART_CNT",

        -- BOM 총수량 (재귀 CTE) — LEV>2 & IS_LEAF
        (WITH RECURSIVE V(bom_report_objid, child_objid, qty, lev, path, is_leaf, aggregate_qty) AS (
           SELECT A.bom_report_objid, A.child_objid, A.qty, 1,
                  ARRAY[A.child_objid::text], FALSE,
                  COALESCE(NULLIF(A.qty,''),'0')::numeric
             FROM bom_part_qty A
            WHERE A.bom_report_objid = PBR.objid::text
              AND (A.parent_objid IS NULL OR A.parent_objid = '')
           UNION ALL
           SELECT B.bom_report_objid, B.child_objid, B.qty, V.lev + 1,
                  V.path || B.child_objid::text,
                  B.parent_objid = ANY(V.path),
                  V.aggregate_qty * COALESCE(NULLIF(B.qty,''),'0')::numeric
             FROM bom_part_qty B
             JOIN V ON B.parent_objid = V.child_objid AND B.bom_report_objid = V.bom_report_objid
            WHERE NOT (B.parent_objid = ANY(V.path))
         )
         SELECT SUM(V.aggregate_qty) FROM V
          WHERE V.lev > 2
            AND NOT EXISTS (SELECT 1 FROM bom_part_qty C WHERE C.parent_objid = V.child_objid AND C.bom_report_objid = V.bom_report_objid)
        ) AS "TOTAL_BOM_PART_QTY_SUM",

        -- 발주품 개수 (발주서 기준)
        (SELECT COUNT(DISTINCT PO.part_no)
           FROM bom_part_qty Q
           JOIN purchase_order_part PO ON COALESCE(NULLIF(Q.last_part_objid,''), Q.part_no) = PO.part_objid
           JOIN purchase_order_master POM ON POM.objid::text = PO.purchase_order_master_objid
           JOIN part_mng P ON P.objid::text = PO.part_objid
          WHERE POM.bom_report_objid = PBR.objid::text
            AND Q.bom_report_objid = PBR.objid::text
            AND Q.status IN ('beforeEdit','editing','deleting','deploy')
            AND POM.status = 'approvalComplete'
            AND COALESCE(P.part_type,'') != ''
        ) AS "TOTAL_PO_PART_CNT",

        -- 발주수량 총계
        (SELECT SUM(COALESCE(NULLIF(POP.order_qty,''),'0')::numeric)
           FROM purchase_order_master POM
           JOIN purchase_order_part POP ON POM.objid::text = POP.purchase_order_master_objid
          WHERE POM.bom_report_objid = PBR.objid::text
            AND POM.status = 'approvalComplete'
            AND POM.contract_mgmt_objid = CM.objid
        ) AS "TOTAL_PO_QTY",

        -- 발주수량 신규 (order_type_cd='0001407')
        (SELECT SUM(COALESCE(NULLIF(POP.order_qty,''),'0')::numeric)
           FROM purchase_order_master POM
           JOIN purchase_order_part POP ON POM.objid::text = POP.purchase_order_master_objid
          WHERE POM.bom_report_objid = PBR.objid::text
            AND POM.order_type_cd = '0001407'
            AND POM.status = 'approvalComplete'
            AND POM.contract_mgmt_objid = CM.objid
        ) AS "TOTAL_PO_NEW_QTY",

        -- 발주수량 재발주 (order_type_cd='0001408')
        (SELECT SUM(COALESCE(NULLIF(POP.order_qty,''),'0')::numeric)
           FROM purchase_order_master POM
           JOIN purchase_order_part POP ON POM.objid::text = POP.purchase_order_master_objid
          WHERE POM.bom_report_objid = PBR.objid::text
            AND POM.order_type_cd = '0001408'
            AND POM.status = 'approvalComplete'
            AND POM.contract_mgmt_objid = CM.objid
        ) AS "TOTAL_PO_RE_QTY",

        -- 입고수량 (inventory_mgmt_in 기준)
        (SELECT SUM(COALESCE(NULLIF(DH.receipt_qty,''),'0')::numeric)
           FROM inventory_mgmt_in DH
           JOIN purchase_order_master POM ON POM.objid::text = DH.purchase_order_sub_objid
          WHERE CM.objid::text = DH.contract_mgmt_objid
            AND POM.bom_report_objid = PBR.objid::text
            AND POM.status = 'approvalComplete'
            AND POM.contract_mgmt_objid = CM.objid
        ) AS "TOTAL_DELIVERY_QTY",

        -- 부적합 집계
        COALESCE(S1.total_defect_qty, 0) AS "TOTAL_DEFECT_QTY",
        COALESCE(S1.defect_qty_1, 0) AS "DEFECT_QTY_1",
        COALESCE(S1.defect_qty_2, 0) AS "DEFECT_QTY_2",
        COALESCE(S1.defect_qty_3, 0) AS "DEFECT_QTY_3",
        COALESCE(S1.defect_qty_4, 0) AS "DEFECT_QTY_4",
        COALESCE(S1.total_defect_price, 0) AS "TOTAL_DEFECT_PRICE"
      FROM sales_bom_report SBR,
           part_bom_report PBR
      LEFT OUTER JOIN (
        SELECT POM.bom_report_objid,
               SUM(COALESCE(NULLIF(DH.error_qty,''),'0')::numeric) AS total_defect_qty,
               SUM(CASE WHEN DH.error_reason = '0001114' THEN COALESCE(NULLIF(DH.error_qty,''),'0')::numeric ELSE NULL END) AS defect_qty_1,
               SUM(CASE WHEN DH.error_reason = '0001115' THEN COALESCE(NULLIF(DH.error_qty,''),'0')::numeric ELSE NULL END) AS defect_qty_2,
               SUM(CASE WHEN DH.error_reason = '0001116' THEN COALESCE(NULLIF(DH.error_qty,''),'0')::numeric ELSE NULL END) AS defect_qty_3,
               SUM(CASE WHEN DH.error_reason = '0001117' THEN COALESCE(NULLIF(DH.error_qty,''),'0')::numeric ELSE NULL END) AS defect_qty_4,
               SUM(COALESCE(NULLIF(POP.supply_unit_price,''),'0')::numeric * COALESCE(NULLIF(DH.error_qty,''),'0')::numeric) AS total_defect_price
          FROM purchase_order_master POM
          JOIN purchase_order_part POP ON POM.objid::text = POP.purchase_order_master_objid
          JOIN arrival_plan DH ON POM.objid::text = DH.parent_objid AND POP.part_objid = DH.part_objid
         WHERE POM.status = 'approvalComplete'
         GROUP BY POM.bom_report_objid
      ) S1 ON PBR.objid::text = S1.bom_report_objid,
      project_mgmt CM
      WHERE ${where}
      ORDER BY "PROJECT_NO" DESC, "UNIT_PART_NAME"
    ) T
  `;

  try {
    const rows = await queryRows(sql, params);
    return NextResponse.json({ RESULTLIST: rows, TOTAL_CNT: rows.length });
  } catch (e) {
    console.error("delivery/status:", e);
    return NextResponse.json(
      { success: false, message: "조회 중 오류가 발생했습니다." },
      { status: 500 },
    );
  }
}
