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

// 제품관리_BOM 조회 (정전개) - 원본: partMng.structureAscendingList / getStructureAscendingList.do
// PRODUCT_MGMT_SPEC (MASTER_OBJID) 또는 UPG_NO 기반으로 PART_BOM_REPORT 매칭,
// BOM_PART_QTY 재귀 CTE로 트리 구조 조회
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 productCode = String(body.product_code || "");       // product_mgmt.objid 또는 PMUM.objid
  const productMgmtSpec = String(body.product_mgmt_spec || ""); // spec_name 문자열
  const upgNo = String(body.upg_no || "");
  const customerCd = String(body.customer_cd || "");
  const projectName = String(body.project_name || "");
  const unitCode = String(body.unit_code || "");
  const searchPartNo = String(body.search_partNo || "");
  const searchPartName = String(body.search_partName || "");
  const searchLevel = String(body.search_level || "");

  // 매칭되는 bom_report_objid 리스트 확보
  const reportConds: string[] = ["1=1"];
  const reportParams: unknown[] = [];
  let pi = 1;
  if (productCode) {
    reportConds.push(`PBM.product_mgmt_objid::varchar = $${pi++}::varchar`);
    reportParams.push(productCode);
  }
  if (productMgmtSpec) {
    reportConds.push(`PBM.spec_name::varchar = $${pi++}::varchar`);
    reportParams.push(productMgmtSpec);
  }
  if (upgNo) {
    reportConds.push(`PBM.product_mgmt_upg = $${pi++}`);
    reportParams.push(upgNo);
  }
  if (customerCd) {
    reportConds.push(`PBM.customer_objid = $${pi++}`);
    reportParams.push(customerCd);
  }
  if (projectName) {
    reportConds.push(`PBM.contract_objid = $${pi++}`);
    reportParams.push(projectName);
  }
  if (unitCode) {
    reportConds.push(`PBM.unit_code = $${pi++}`);
    reportParams.push(unitCode);
  }
  // 품번만으로 조회 시 상위 200 report 내에 해당 품번이 없으면 빈 결과가 나오므로,
  // 품번 정확 일치 노드가 존재하는 bom_report 로 사전 필터링 (anchor 와 동일 기준)
  if (searchPartNo) {
    reportConds.push(`PBM.objid IN (
      SELECT DISTINCT BPQ.bom_report_objid
      FROM bom_part_qty BPQ
      JOIN part_mng PT ON PT.objid::text = BPQ.part_no
      WHERE UPPER(TRIM(PT.part_no)) = UPPER(TRIM($${pi++}))
    )`);
    reportParams.push(searchPartNo);
  }
  if (searchPartName) {
    reportConds.push(`PBM.objid IN (
      SELECT DISTINCT BPQ.bom_report_objid
      FROM bom_part_qty BPQ
      JOIN part_mng PT ON PT.objid::text = BPQ.part_no
      WHERE UPPER(PT.part_name) LIKE UPPER('%' || $${pi++} || '%')
    )`);
    reportParams.push(searchPartName);
  }

  const reports = await queryRows<{ OBJID: string }>(
    `SELECT objid::text AS "OBJID" FROM part_bom_report PBM WHERE ${reportConds.join(" AND ")}`,
    reportParams,
  );
  if (reports.length === 0) {
    return NextResponse.json({ RESULTLIST: [], TOTAL_CNT: 0, MAX_LEVEL: 0 });
  }
  const reportIds = reports.map((r) => r.OBJID);

  // 재귀 CTE로 BOM 트리 조회
  const placeholders = reportIds.map((_, i) => `$${i + 1}`).join(",");
  const idx = reportIds.length;

  // 품번이 입력되면 재귀 anchor 를 "품번 정확 일치 노드"로 바꿔 그 subtree 만 전개.
  // (품번 포함 매칭이 아니라 정확 일치 기준. 대소문자/공백 무시)
  // 품명은 subtree 내 추가 필터로만 사용 (기존대로 LIKE).
  const extraParams: unknown[] = [];
  let ei = idx + 1;
  let anchorWhere: string;
  if (searchPartNo) {
    anchorWhere = `A.bom_report_objid IN (${placeholders})
        AND EXISTS (
          SELECT 1 FROM part_mng PT
          WHERE PT.objid::text = A.part_no
            AND UPPER(TRIM(PT.part_no)) = UPPER(TRIM($${ei++}))
        )`;
    extraParams.push(searchPartNo);
  } else {
    anchorWhere = `(A.parent_objid IS NULL OR A.parent_objid = '')
        AND A.bom_report_objid IN (${placeholders})`;
  }

  const extraConds: string[] = [];
  if (searchPartName) {
    extraConds.push(`UPPER(P.part_name) LIKE UPPER('%' || $${ei++} || '%')`);
    extraParams.push(searchPartName);
  }
  const extraWhere = extraConds.length > 0 ? " AND " + extraConds.join(" AND ") : "";

  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.regdate, A.seq,
        1::int AS lev,
        ARRAY[A.child_objid::text] AS path,
        FALSE AS cycle,
        A.child_objid AS root_objid,
        A.child_objid AS sub_root_objid
      FROM bom_part_qty A
      WHERE ${anchorWhere}
      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.regdate, B.seq,
        V.lev + 1,
        V.path || B.child_objid::text,
        B.parent_objid = ANY(V.path),
        V.root_objid,
        CASE WHEN V.lev = 1 THEN B.child_objid ELSE V.sub_root_objid END
      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
    ),
    MAX_LEV AS (
      SELECT MAX(lev) AS max_level FROM VIEW_BOM
    )
    SELECT
      V.bom_report_objid AS "BOM_REPORT_OBJID",
      V.objid AS "OBJID",
      V.parent_objid AS "PARENT_OBJID",
      V.child_objid AS "CHILD_OBJID",
      V.root_objid AS "ROOT_OBJID",
      V.sub_root_objid AS "SUB_ROOT_OBJID",
      V.part_no AS "PART_NO_RAW",
      V.qty AS "QTY",
      V.lev AS "LEV",
      V.lev AS "LEVEL",
      (SELECT max_level FROM MAX_LEV) AS "MAX_LEVEL",
      (CASE WHEN EXISTS (SELECT 1 FROM bom_part_qty C WHERE C.parent_objid = V.child_objid
                           AND C.bom_report_objid = V.bom_report_objid) THEN 0 ELSE 1 END) AS "LEAF",
      P.objid::text AS "PART_OBJID",
      P.part_no AS "PART_NO",
      P.part_name AS "PART_NAME",
      P.revision AS "REVISION",
      P.material AS "MATERIAL",
      P.spec AS "SPEC",
      P.post_processing AS "POST_PROCESSING",
      P.maker AS "MAKER",
      P.eo_no AS "EO_NO",
      P.eo_date AS "EO_DATE",
      P.remark AS "REMARK",
      P.part_type AS "PART_TYPE",
      (SELECT code_name FROM comm_code WHERE code_id = P.part_type) AS "PART_TYPE_TITLE",
      (SELECT COUNT(*) FROM attach_file_info WHERE target_objid = P.objid::text AND doc_type = '3D_CAD') AS "FILE_3D_CNT",
      (SELECT COUNT(*) FROM attach_file_info WHERE target_objid = P.objid::text AND doc_type = '2D_DRAWING_CAD') AS "FILE_2D_CNT",
      (SELECT COUNT(*) FROM attach_file_info WHERE target_objid = P.objid::text AND doc_type = '2D_PDF_CAD') AS "FILE_PDF_CNT"
    FROM VIEW_BOM V
      LEFT JOIN part_mng P ON P.objid::text = V.part_no
    WHERE 1=1 ${extraWhere}
    ORDER BY V.bom_report_objid, V.root_objid, V.path, V.regdate
  `;

  const rows = await queryRows(sql, [...reportIds, ...extraParams]);
  let maxLevel = rows.length > 0 ? Number(rows[0].MAX_LEVEL || 0) : 0;

  // search_level 필터: 해당 레벨 이하만 남김
  let filtered = rows;
  if (searchLevel) {
    const n = Number(searchLevel);
    filtered = rows.filter((r) => Number(r.LEV || 0) <= n);
    if (filtered.length > 0) maxLevel = Math.max(...filtered.map((r) => Number(r.LEV || 0)));
  }

  return NextResponse.json({ RESULTLIST: filtered, TOTAL_CNT: filtered.length, MAX_LEVEL: maxLevel });
}
