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

// 구매관리 > 구매BOM관리 — 원본: /salesMng/salesBomReportList.do
// 테이블: part_bom_report + sales_bom_report + project_mgmt + pms_wbs_task
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[] = ["1=1"];
  const params: unknown[] = [];
  let idx = 1;

  if (body.year) {
    conditions.push(`TO_CHAR(CM.regdate, 'YYYY') = $${idx++}`);
    params.push(body.year);
  }
  if (body.customer_cd) {
    conditions.push(`PBR.customer_objid = $${idx++}`);
    params.push(body.customer_cd);
  }
  if (body.project_no) {
    conditions.push(`COALESCE(CM.project_no, CM.contract_no) LIKE '%' || $${idx++} || '%'`);
    params.push(body.project_no);
  }
  if (body.unit_code) {
    conditions.push(`PBR.unit_code = $${idx++}`);
    params.push(body.unit_code);
  }
  if (body.unit_name) {
    conditions.push(
      `(SELECT COALESCE(O.unit_no,'') || '-' || COALESCE(O.task_name,'') FROM pms_wbs_task O WHERE O.objid = PBR.unit_code) LIKE '%' || $${idx++} || '%'`
    );
    params.push(body.unit_name);
  }
  if (body.writer2_id) {
    conditions.push(`SBR.writer = $${idx++}`);
    params.push(body.writer2_id);
  }

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

  const sql = `
    SELECT PBR.objid::text AS "OBJID",
      SBR.objid::text AS "SBR_OBJID",
      COALESCE(CM.project_no, CM.contract_no, '') AS "PROJECT_NO",
      CM.req_del_date AS "REQ_DEL_DATE",
      (SELECT supply_name FROM admin_supply_mng WHERE objid::text = PBR.customer_objid LIMIT 1) AS "CUSTOMER_NAME",
      (SELECT code_name FROM comm_code WHERE code_id = CM.contract_company LIMIT 1) AS "CONTRACT_COMPANY_NAME",
      (SELECT code_name FROM comm_code WHERE code_id = CM.manufacture_plant LIMIT 1) AS "MANUFACTURE_PLANT_NAME",
      PBR.unit_code AS "UNIT_CODE",
      (SELECT COALESCE(O.unit_no,'') || '-' || COALESCE(O.task_name,'') FROM pms_wbs_task O WHERE O.objid = PBR.unit_code LIMIT 1) AS "UNIT_NAME",
      (SELECT COUNT(*) FROM bom_part_qty A WHERE A.bom_report_objid = PBR.objid) AS "BOM_CNT",
      PBR.deploy_date AS "DEPLOY_DATE",
      PBR.writer AS "WRITER1",
      COALESCE(
        (SELECT user_name FROM user_info WHERE user_id = PBR.writer LIMIT 1),
        (SELECT user_name FROM user_info_history WHERE user_id = PBR.writer LIMIT 1),
        PBR.writer
      ) AS "WRITER1_NAME",
      (SELECT COUNT(1) FROM sales_bom_report_part SBRP WHERE SBRP.parent_objid = PBR.objid) AS "SALES_PART_CNT",
      TO_CHAR(SBR.regdate, 'YYYY-MM-DD') AS "REGDATE2",
      SBR.writer AS "WRITER2",
      COALESCE(
        (SELECT user_name FROM user_info WHERE user_id = SBR.writer LIMIT 1),
        (SELECT user_name FROM user_info_history WHERE user_id = SBR.writer LIMIT 1),
        SBR.writer
      ) AS "WRITER2_NAME"
    FROM part_bom_report PBR
      LEFT OUTER JOIN sales_bom_report SBR ON PBR.objid = SBR.parent_objid
      INNER JOIN project_mgmt CM ON PBR.contract_objid = CM.objid
    WHERE ${where}
    ORDER BY CM.regdate DESC, PBR.regdate DESC
  `;

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