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

// 조립 리스트 조회 (part_bom_report.objid 기준)
// body: { bomReportObjid }
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 bomObjid = String(body.bomReportObjid || "");
  if (!bomObjid) return NextResponse.json({ RESULTLIST: [], HEADER: {} });

  // 헤더: 프로젝트 + 유닛 정보
  const header = await queryOne(
    `SELECT T.objid::text AS "OBJID",
            T.contract_objid AS "CONTRACT_OBJID",
            T.unit_code AS "UNIT_CODE",
            (SELECT project_no FROM project_mgmt WHERE objid = T.contract_objid) AS "PROJECT_NO",
            (SELECT project_name FROM project_mgmt WHERE objid = T.contract_objid) AS "PROJECT_NAME",
            (SELECT supply_name FROM supply_mng WHERE objid::varchar = T.customer_objid LIMIT 1) AS "CUSTOMER_NAME",
            (SELECT COALESCE(O.unit_no,'') || '-' || COALESCE(O.task_name,'') FROM pms_wbs_task O WHERE O.objid = T.unit_code LIMIT 1) AS "UNIT_NAME"
       FROM part_bom_report T
      WHERE T.objid = $1`,
    [bomObjid],
  );

  // 조립 리스트: bom_part_qty + part_mng + assembly_wbs_task
  //   + 입고예정일/입고일 (arrival_plan, part_objid)
  //   + 입고결과 (inventory_mgmt_in × inventory_mgmt)
  //   + 인수수량 (inventory_mgmt_out × inventory_mgmt)
  //   + 발주수량 (purchase_order_master × purchase_order_part)
  const rows = await queryRows(
    `SELECT BP.objid::text AS "BOM_PART_QTY_OBJID",
            BP.child_objid AS "CHILD_OBJID",
            COALESCE(NULLIF(BP.last_part_objid,''), BP.part_no) AS "PART_OBJID",
            PM.part_no AS "PART_NO",
            COALESCE(PM.part_name, '') AS "PART_NAME",
            COALESCE(PM.material, '') AS "MATERIAL",
            COALESCE(PM.spec, '') AS "SPEC",
            COALESCE(PM.maker, '') AS "MAKER",
            COALESCE(PM.revision, '') AS "REVISION",
            COALESCE(PM.eo_no, '') AS "EO_NO",
            COALESCE(PM.eo_date, '') AS "EO_DATE",
            COALESCE((SELECT code_name FROM comm_code WHERE code_id = PM.part_type LIMIT 1), '') AS "PART_TYPE_TITLE",
            COALESCE(PM.part_type, '') AS "PART_TYPE",
            COALESCE(NULLIF(BP.qty,''), '') AS "QTY",
            A.objid::text AS "ASSEMBLY_OBJID",
            COALESCE(A.assembly_user_id, '') AS "ASSEMBLY_USER_ID",
            COALESCE((SELECT user_name FROM user_info WHERE user_id = A.assembly_user_id LIMIT 1), A.assembly_user_id, '') AS "ASSEMBLY_USER_NAME",
            COALESCE(A.assembly_date, '') AS "ASSEMBLY_DATE",
            COALESCE(A.insourcing, '') AS "INSOURCING",
            COALESCE(A.outsourcing, '') AS "OUTSOURCING",
            -- 입고예정일 (최대 3개)
            COALESCE((
              SELECT ARRAY_TO_STRING(ARRAY_AGG(arrival_plan_date ORDER BY arrival_plan_date), ',')
                FROM (SELECT DISTINCT arrival_plan_date FROM arrival_plan AP
                       WHERE AP.part_objid = COALESCE(NULLIF(BP.last_part_objid,''), BP.part_no)
                         AND COALESCE(arrival_plan_date,'') <> ''
                       ORDER BY arrival_plan_date LIMIT 3) t
            ), '') AS "ARRIVAL_PLAN_DATES",
            -- 입고일 (최대 3개)
            COALESCE((
              SELECT ARRAY_TO_STRING(ARRAY_AGG(receipt_date ORDER BY receipt_date), ',')
                FROM (SELECT DISTINCT receipt_date FROM arrival_plan AP
                       WHERE AP.part_objid = COALESCE(NULLIF(BP.last_part_objid,''), BP.part_no)
                         AND COALESCE(receipt_date,'') <> ''
                       ORDER BY receipt_date LIMIT 3) t
            ), '') AS "RECEIPT_DATES",
            -- 입고결과 (RECEIPT_QTY - MOVE_QTY 합계)
            COALESCE((
              SELECT SUM(
                  COALESCE(NULLIF(IMI.receipt_qty,'')::integer, 0)
                - COALESCE(NULLIF(IMI.move_qty,'')::integer, 0))
                FROM inventory_mgmt_in IMI
                JOIN inventory_mgmt IM ON IMI.parent_objid = IM.objid
               WHERE IMI.contract_mgmt_objid = (SELECT contract_objid FROM part_bom_report WHERE objid = $1)
                 AND IM.part_objid = COALESCE(NULLIF(BP.last_part_objid,''), BP.part_no)
            ), 0) AS "DELIVERY_QTY",
            -- 발주수량 (구매품표준용)
            COALESCE((
              SELECT SUM(COALESCE(NULLIF(POP.order_qty,'')::numeric, 0))
                FROM purchase_order_master POM
                JOIN purchase_order_part POP ON POM.objid = POP.purchase_order_master_objid
               WHERE POM.contract_mgmt_objid = (SELECT contract_objid FROM part_bom_report WHERE objid = $1)
                 AND POP.part_objid = COALESCE(NULLIF(BP.last_part_objid,''), BP.part_no)
            ), 0) AS "ORDER_QTY",
            -- 인수수량 (unit 단위)
            COALESCE((
              SELECT SUM(COALESCE(NULLIF(IMO.out_qty,'')::integer, 0))
                FROM inventory_mgmt_out IMO
                JOIN inventory_mgmt IM ON IMO.parent_objid = IM.objid
               WHERE IMO.contract_mgmt_objid = (SELECT contract_objid FROM part_bom_report WHERE objid = $1)
                 AND IMO.unit = (SELECT unit_code FROM part_bom_report WHERE objid = $1)
                 AND IM.part_objid = COALESCE(NULLIF(BP.last_part_objid,''), BP.part_no)
            ), 0) AS "RECEIVE_QTY"
       FROM bom_part_qty BP
       LEFT JOIN part_mng PM
              ON COALESCE(NULLIF(BP.last_part_objid,''), BP.part_no) = PM.objid::varchar
       LEFT JOIN assembly_wbs_task A
              ON A.parent_objid = BP.bom_report_objid
             AND A.part_objid = COALESCE(NULLIF(BP.last_part_objid,''), BP.part_no)
             AND A.bom_qty_child_objid = BP.child_objid
      WHERE BP.bom_report_objid = $1
        AND PM.part_type IS NOT NULL AND PM.part_type <> ''
        AND BP.status IN ('beforeEdit','editing','deleting','deploy')
      ORDER BY PM.part_no`,
    [bomObjid],
  );

  return NextResponse.json({ RESULTLIST: rows, HEADER: header || {} });
}
