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

// 원본: purchaseOrder.purchaseOrderSalesBomPartListByPartnerContract
// 프로젝트 × 유닛 × 공급업체 기반으로 구매BOM(sales_bom_report_part)에서
// 해당 공급업체가 등록된 부품을 발주서 작성 시 자동 투입용으로 조회.
// 선택된 파트너가 supply_objid/1/2/3/4 중 어느 슬롯에 있든 그 슬롯의 단가만
// 해당 컬럼(PARTNER_PRICE or PRICE1..4)에 복사, 나머지는 '0'.
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 contractObjId = String(body.contract_mgmt_objid || body.CONTRACT_MGMT_OBJID || "");
  const unitCode = String(body.unit_code || body.UNIT_CODE || "");
  const partnerObjId = String(body.partner_objid || body.PARTNER_OBJID || "");
  if (!contractObjId || !partnerObjId) {
    return NextResponse.json({ RESULTLIST: [] });
  }

  const sql = `
    WITH RECURSIVE VIEW_BOM AS (
      SELECT A.bom_report_objid, A.objid, A.parent_objid, A.child_objid,
             A.parent_part_no, A.part_no, A.qty, A.seq,
             1 AS lev,
             ARRAY[A.child_objid::text] AS path,
             COALESCE(NULLIF(A.qty,''),'0')::numeric AS aggregate_qty
        FROM part_bom_report PBR
        JOIN bom_part_qty A ON PBR.objid = A.bom_report_objid
       WHERE PBR.contract_objid = $1
         AND ($2::text = '' OR PBR.unit_code = $2)
         AND (A.parent_objid IS NULL OR A.parent_objid = '')
         AND A.status = 'deploy'
      UNION ALL
      SELECT B.bom_report_objid, B.objid, B.parent_objid, B.child_objid,
             B.parent_part_no, B.part_no, B.qty, B.seq,
             V.lev + 1,
             V.path || B.child_objid::text,
             V.aggregate_qty * COALESCE(NULLIF(B.qty,''),'0')::numeric
        FROM bom_part_qty B
        JOIN VIEW_BOM V ON B.parent_objid = V.child_objid
         AND V.bom_report_objid = B.bom_report_objid
       WHERE B.status = 'deploy'
         AND NOT (B.child_objid = ANY(V.path))
    )
    SELECT base.*,
           -- 재고수량 = 입고수량 합 - 출고요청수량 합
           (COALESCE((SELECT SUM(CASE WHEN COALESCE(NULLIF(receipt_qty,''),'') = '' THEN 0
                                      ELSE receipt_qty::numeric END)
                       FROM inventory_mgmt IM
                       INNER JOIN inventory_mgmt_in O ON O.parent_objid = IM.objid
                      WHERE IM.part_objid = base."PART_OBJID"), 0)
          - COALESCE((SELECT SUM(CASE WHEN COALESCE(NULLIF(request_qty,''),'') = '' THEN 0
                                      ELSE request_qty::numeric END)
                       FROM inventory_mgmt IM
                       INNER JOIN inventory_mgmt_out O ON O.parent_objid = IM.objid
                      WHERE IM.part_objid = base."PART_OBJID"), 0)
           ) AS "STOCK_QTY",
           -- 총발주수량 = 이 품번으로 approvalComplete 된 이전 발주 누적
           COALESCE((SELECT SUM(COALESCE(NULLIF(POP.order_qty,''),'0')::numeric)
                       FROM purchase_order_part POP
                       JOIN purchase_order_master POM
                         ON POM.objid = POP.purchase_order_master_objid
                      WHERE POP.part_objid = base."PART_OBJID"
                        AND POM.status = 'approvalComplete'), 0) AS "TOTAL_ORDER_QTY"
      FROM (
        SELECT T1.parent_objid AS "BOM_REPORT_OBJID",
               PM.objid::text AS "PART_OBJID",
               T1.objid::text AS "SALES_BOM_REPORT_PART_OBJID",
               PM.part_name AS "PART_NAME",
               PM.part_no AS "PART_NO",
               PM.spec AS "SPEC",
               PM.maker AS "MAKER",
               PM.remark AS "REMARK",
               PM.unit AS "UNIT",
               (SELECT MAX(aggregate_qty) FROM VIEW_BOM V
                  WHERE V.child_objid = BPQ.child_objid
                    AND V.bom_report_objid = PBR.objid) AS "ORDER_QTY",
               BPQ.seq AS "SEQ_SORT",
               CASE WHEN T1.supply_objid  = $3 THEN T1.price  ELSE '0' END AS "PARTNER_PRICE",
               CASE WHEN T1.supply_objid1 = $3 THEN T1.price1 ELSE '0' END AS "PRICE1",
               CASE WHEN T1.supply_objid2 = $3 THEN T1.price2 ELSE '0' END AS "PRICE2",
               CASE WHEN T1.supply_objid3 = $3 THEN T1.price3 ELSE '0' END AS "PRICE3",
               CASE WHEN T1.supply_objid4 = $3 THEN T1.price4 ELSE '0' END AS "PRICE4"
          FROM sales_bom_report_part T1
          JOIN part_bom_report PBR ON T1.parent_objid = PBR.objid::text
          JOIN bom_part_qty BPQ ON PBR.objid = BPQ.bom_report_objid
                                AND T1.bom_part_qty_objid = BPQ.child_objid
          JOIN part_mng PM ON BPQ.last_part_objid = PM.objid::varchar
         WHERE PBR.contract_objid = $1
           AND ($2::text = '' OR PBR.unit_code = $2)
           AND BPQ.status = 'deploy'
           AND COALESCE(PM.part_type,'') <> '0001788'
           AND (T1.supply_objid  = $3
             OR T1.supply_objid1 = $3
             OR T1.supply_objid2 = $3
             OR T1.supply_objid3 = $3
             OR T1.supply_objid4 = $3)
      ) base
     ORDER BY base."SEQ_SORT" NULLS LAST
  `;

  try {
    const rows = await queryRows(sql, [contractObjId, unitCode, partnerObjId]);
    return NextResponse.json({ RESULTLIST: rows, TOTAL_CNT: rows.length });
  } catch (e) {
    console.error("order/bom-parts-by-partner:", e);
    return NextResponse.json(
      { success: false, message: "BOM 부품 조회 중 오류가 발생했습니다." },
      { status: 500 },
    );
  }
}
