// ============================================================
// 개발관리 E-BOM (M3 등록 / M4 조회) — wace partMng.xml 1:1 이식.
//
// 매퍼 매핑 (원본: wace_plm/src/com/pms/mapper/partMng.xml):
//   getBOMStandardStructureGridList → list()           (M3 그리드, part_bom_report)
//   updateStructureStatus           → updateStatus()   (M3 상태변경)
//   deleteBomReport + deleteBomQty  → removeMany()     (M3 다중 삭제 트랜잭션)
//   structureAscendingList          → ascending()      (M4 정전개, 재귀 CTE)
//   selectStructureDescendingList   → descending()     (M4 역전개, 재귀 CTE)
//
// vexplor_rps 적응:
//   · customer_mng 매핑: wace SUPPLY_MNG.OBJID → vexplor customer_mng.customer_code
//   · PRODUCT_NAME: wace CODE_NAME() → vexplor LEFT JOIN comm_code CC_PRD
//   · M4 product_mgmt_spec/upg/vc 분기 제거 (vexplor part_bom_report 단순화)
// ============================================================

import { PoolClient } from "pg";
import { getPool, transaction } from "../database/db";
import { logger } from "../utils/logger";

// ─── 필터/바디 타입 ──────────────────────────────────────────

export interface BomReportListFilter {
  customer_cd?: string;
  project_name?: string;
  unit_code?: string;
  search_unit_name?: string;
  search_writer?: string;
  product_cd?: string;
  search_part_no?: string;
  search_part_name?: string;
  search_from_date?: string;
  search_to_date?: string;
  status?: string;
  page?: number;
  page_size?: number;
}

export interface BomReportStatusBody {
  product_cd?: string;
  part_no?: string;
  part_name?: string;
  version?: string;
  status: string;
}

export interface BomTreeFilter {
  bom_report_objid?: string;
  project_name?: string;     // part_bom_report.contract_objid
  unit_code?: string;
  search_part_no?: string;
  search_part_name?: string;
  search_level?: string | number;  // wace 1:1 — 1~5 표시 레벨 (lev <= search_level)
}

// ─── 공용 파라미터 빌더 ────────────────────────────────────

function buildListWhere(filter: BomReportListFilter, startIdx: number) {
  const params: any[] = [];
  const conds: string[] = [];
  let idx = startIdx;

  if (filter.customer_cd)      { conds.push(`T.CUSTOMER_OBJID = $${idx++}`); params.push(filter.customer_cd); }
  if (filter.project_name)     { conds.push(`T.CONTRACT_OBJID = $${idx++}`); params.push(filter.project_name); }
  if (filter.unit_code)        { conds.push(`T.UNIT_CODE = $${idx++}`); params.push(filter.unit_code); }
  if (filter.search_unit_name) {
    conds.push(`EXISTS (SELECT 1 FROM PMS_WBS_TASK W
                         WHERE W.OBJID = T.UNIT_CODE
                           AND (UPPER(W.TASK_NAME) LIKE UPPER($${idx}) OR UPPER(W.UNIT_NO) LIKE UPPER($${idx})))`);
    params.push(`%${filter.search_unit_name}%`); idx++;
  }
  if (filter.search_writer)    { conds.push(`T.WRITER = $${idx++}`); params.push(filter.search_writer); }
  if (filter.product_cd)       { conds.push(`T.PRODUCT_CD = $${idx++}`); params.push(filter.product_cd); }
  if (filter.search_part_no)   { conds.push(`UPPER(T.PART_NO) LIKE UPPER($${idx++})`); params.push(`%${filter.search_part_no}%`); }
  if (filter.search_part_name) { conds.push(`UPPER(T.PART_NAME) LIKE UPPER($${idx++})`); params.push(`%${filter.search_part_name}%`); }
  if (filter.search_from_date) { conds.push(`T.REGDATE::date >= TO_DATE($${idx++}, 'YYYY-MM-DD')`); params.push(filter.search_from_date); }
  if (filter.search_to_date)   { conds.push(`T.REGDATE::date <= TO_DATE($${idx++}, 'YYYY-MM-DD')`); params.push(filter.search_to_date); }
  if (filter.status)           { conds.push(`T.STATUS = $${idx++}`); params.push(filter.status); }

  return { sql: conds.length ? conds.join(" AND ") : "1=1", params };
}

function paginate(filter: { page?: number; page_size?: number }) {
  const page = Math.max(1, Number(filter.page) || 1);
  const pageSize = Math.min(500, Math.max(1, Number(filter.page_size) || 50));
  return { limit: pageSize, offset: (page - 1) * pageSize, page, pageSize };
}

// ─── M3 그리드 ──────────────────────────────────────────────

export async function list(filter: BomReportListFilter) {
  const { limit, offset, page, pageSize } = paginate(filter);
  const where = buildListWhere(filter, 1);
  const pool = getPool();

  const baseSql = `
    SELECT
       ROW_NUMBER() OVER (ORDER BY T.REGDATE DESC) AS NUM,
       T.OBJID, T.CUSTOMER_OBJID, SM.customer_name AS CUSTOMER_NAME,
       T.CONTRACT_OBJID, PM.CUSTOMER_PROJECT_NAME, PM.PROJECT_NO,
       T.UNIT_CODE, COALESCE(WT.UNIT_NO || '-' || WT.TASK_NAME, '') AS UNIT_NAME,
       T.STATUS,
       -- 운영판 wace 매퍼 1:1 (CREATE/CHANGEDESIGN/DEPLOY 만 라벨, 그 외 'Y'/'N' 등은 raw 표시)
       CASE UPPER(T.STATUS)
         WHEN 'CREATE' THEN '등록중'
         WHEN 'CHANGEDESIGN' THEN '설계변경미배포'
         WHEN 'DEPLOY' THEN '배포완료'
         ELSE COALESCE(T.STATUS, '') END AS STATUS_TITLE,
       T.WRITER, UI.dept_name AS DEPT_NAME, UI.user_name AS USER_NAME,
       COALESCE(UI.dept_name || '/' || UI.user_name, '') AS DEPT_USER_NAME,
       T.REGDATE, TO_CHAR(T.REGDATE, 'YYYY-MM-DD') AS REG_DATE,
       T.DEPLOY_DATE, T.REVISION,
       EO_DATA.EO_NO, EO_DATA.EO_DATE,
       T.NOTE, T.MULTI_YN, T.MULTI_MASTER_YN, T.MULTI_BREAK_YN, T.MULTI_MASTER_OBJID,
       COALESCE(EO_DATA.BOM_CNT, 0)::int AS BOM_CNT,
       T.PRODUCT_CD, CC_PRD.code_name AS PRODUCT_NAME,
       T.PART_NO, T.PART_NAME
      FROM PART_BOM_REPORT T
      LEFT JOIN customer_mng SM   ON SM.customer_code = T.CUSTOMER_OBJID
      LEFT JOIN PROJECT_MGMT PM   ON PM.OBJID = T.CONTRACT_OBJID
      LEFT JOIN PMS_WBS_TASK WT   ON WT.OBJID = T.UNIT_CODE
      LEFT JOIN user_info UI      ON UI.user_id = T.WRITER
      LEFT JOIN COMM_CODE CC_PRD  ON CC_PRD.code_id = T.PRODUCT_CD AND CC_PRD.status = 'active'
      LEFT JOIN (
        SELECT BP.BOM_REPORT_OBJID,
               MAX(PM2.EO_NO) AS EO_NO,
               MAX(PM2.EO_DATE) AS EO_DATE,
               COUNT(*) AS BOM_CNT
          FROM BOM_PART_QTY BP
          LEFT JOIN PART_MNG PM2 ON BP.PART_NO = PM2.OBJID::varchar
         GROUP BY BP.BOM_REPORT_OBJID
      ) EO_DATA ON EO_DATA.BOM_REPORT_OBJID = T.OBJID
     WHERE ${where.sql}
  `;

  const dataSql = `${baseSql} ORDER BY T.REGDATE DESC NULLS LAST LIMIT $${where.params.length + 1} OFFSET $${where.params.length + 2}`;
  const countSql = `SELECT COUNT(*)::int AS total FROM (${baseSql}) X`;

  const [dataRes, countRes] = await Promise.all([
    pool.query(dataSql, [...where.params, limit, offset]),
    pool.query(countSql, where.params),
  ]);

  return { rows: dataRes.rows, total: countRes.rows[0]?.total ?? 0, page, pageSize };
}

// ─── M3 단건 ────────────────────────────────────────────────

export async function getByObjid(objid: string) {
  const sql = `
    SELECT T.*,
           CC_PRD.code_name AS PRODUCT_NAME,
           (SELECT COUNT(*) FROM BOM_PART_QTY Q WHERE Q.BOM_REPORT_OBJID = T.OBJID) AS BOM_CNT
      FROM PART_BOM_REPORT T
      LEFT JOIN COMM_CODE CC_PRD ON CC_PRD.code_id = T.PRODUCT_CD AND CC_PRD.status='active'
     WHERE T.OBJID = $1
  `;
  const r = await getPool().query(sql, [objid]);
  return r.rows[0] ?? null;
}

// ─── M3 상태 변경 ──────────────────────────────────────────

export async function updateStatus(userId: string, objid: string, body: BomReportStatusBody) {
  if (!body.status) throw new Error("status는 필수입니다.");
  // RPS 정책: 상태를 'Y' 로 변경한 시점을 확정일(DEPLOY_DATE) 로 기록.
  // 'N' 으로 변경 시는 기존 DEPLOY_DATE 보존 (마지막 확정 기록 유지).
  const sql = `
    UPDATE PART_BOM_REPORT
       SET PRODUCT_CD  = COALESCE($1, PRODUCT_CD),
           PART_NO     = COALESCE($2, PART_NO),
           PART_NAME   = COALESCE($3, PART_NAME),
           REVISION    = COALESCE($4, REVISION),
           STATUS      = $5::varchar,
           DEPLOY_DATE = CASE WHEN UPPER($5::varchar) = 'Y' THEN TO_CHAR(NOW(), 'YYYY-MM-DD') ELSE DEPLOY_DATE END,
           editer      = $6,
           edit_date   = NOW()
     WHERE OBJID = $7
  `;
  const r = await getPool().query(sql, [
    body.product_cd ?? null,
    body.part_no ?? null,
    body.part_name ?? null,
    body.version ?? null,
    body.status,
    userId,
    objid,
  ]);
  return r.rowCount ?? 0;
}

// ─── M3 다중 삭제 (트랜잭션) ───────────────────────────────

export async function removeMany(objids: string[]): Promise<number> {
  if (!objids || objids.length === 0) return 0;
  let removed = 0;
  await transaction(async (client: PoolClient) => {
    // 1) 자식 트리
    await client.query(
      `DELETE FROM BOM_PART_QTY WHERE BOM_REPORT_OBJID = ANY($1::varchar[])`,
      [objids]
    );
    // 2) 메인
    const r = await client.query(
      `DELETE FROM PART_BOM_REPORT WHERE OBJID = ANY($1::varchar[])`,
      [objids]
    );
    removed = r.rowCount ?? 0;
  });
  logger.info("E-BOM 삭제 완료", { removed });
  return removed;
}

// ─── M4 정전개 (재귀 CTE) ──────────────────────────────────

export async function ascending(filter: BomTreeFilter) {
  const pool = getPool();
  const params: any[] = [];
  const conds: string[] = [];
  let idx = 1;

  // 시작점 필터 (anchor): 명시적 bom_report_objid 또는 part_bom_report 필터로 좁힘.
  // 품번/품명 검색은 결과 필터가 아니라 매칭된 PART 가 들어있는 BOM_REPORT 전체를 anchor 로
  // 잡아야 트리 자식들이 같이 풀림 (wace 운영판 동작 1:1).
  if (filter.bom_report_objid) {
    conds.push(`BP.bom_report_objid = $${idx++}`);
    params.push(filter.bom_report_objid);
  } else if (filter.project_name || filter.unit_code) {
    const subConds: string[] = [];
    if (filter.project_name) { subConds.push(`contract_objid = $${idx++}`); params.push(filter.project_name); }
    if (filter.unit_code)    { subConds.push(`unit_code = $${idx++}`); params.push(filter.unit_code); }
    conds.push(`BP.bom_report_objid IN (SELECT objid FROM part_bom_report WHERE ${subConds.join(" AND ")})`);
  }
  if (filter.search_part_no) {
    conds.push(`BP.bom_report_objid IN (
      SELECT DISTINCT BQ.bom_report_objid FROM bom_part_qty BQ
       WHERE EXISTS (
         SELECT 1 FROM part_mng PMS
          WHERE PMS.objid::varchar = BQ.part_no
            AND UPPER(PMS.part_no) LIKE UPPER($${idx++})
       )
    )`);
    params.push(`%${filter.search_part_no}%`);
  }
  if (filter.search_part_name) {
    conds.push(`BP.bom_report_objid IN (
      SELECT DISTINCT BQ.bom_report_objid FROM bom_part_qty BQ
       WHERE EXISTS (
         SELECT 1 FROM part_mng PMS
          WHERE PMS.objid::varchar = BQ.part_no
            AND UPPER(PMS.part_name) LIKE UPPER($${idx++})
       )
    )`);
    params.push(`%${filter.search_part_name}%`);
  }
  const startWhere = conds.length ? conds.join(" AND ") : "1=1";

  // 결과 단계 필터 — search_level 만 (트리 깊이 제한)
  const finalConds: string[] = [];
  if (filter.search_level) {
    finalConds.push(`T.lev <= $${idx++}::int`);
    params.push(filter.search_level);
  }
  const finalWhere = finalConds.length ? `WHERE ${finalConds.join(" AND ")}` : "";

  const sql = `
    WITH RECURSIVE TREE(bom_report_objid, objid, parent_objid, child_objid, part_no, qty, item_qty, seq, status, lev, path, cycle) AS (
       SELECT BP.bom_report_objid, BP.objid, BP.parent_objid, BP.child_objid,
              BP.part_no, BP.qty, BP.item_qty, BP.seq, BP.status,
              1, ARRAY[BP.objid::varchar], FALSE
         FROM bom_part_qty BP
        WHERE (BP.parent_objid IS NULL OR BP.parent_objid = '')
          AND ${startWhere}
       UNION ALL
       SELECT B.bom_report_objid, B.objid, B.parent_objid, B.child_objid,
              B.part_no, B.qty, B.item_qty, B.seq, B.status,
              T.lev + 1, T.path || B.objid::varchar, B.objid::varchar = ANY(T.path)
         FROM bom_part_qty B
         JOIN TREE T ON B.parent_objid = T.child_objid AND NOT T.cycle
    )
    SELECT T.bom_report_objid, T.objid, T.parent_objid, T.child_objid, T.part_no, T.qty, T.seq, T.status,
           T.lev, T.path,
           T.item_qty AS p_qty,
           PM.part_no   AS pm_part_no,
           PM.part_name AS pm_part_name,
           PM.spec, PM.material, PM.weight, PM.remark,
           PM.heat_treatment_hardness, PM.heat_treatment_method, PM.surface_treatment,
           PM.maker, PM.part_type,
           CC.code_name AS part_type_title,
           PM.edit_date,
           PM.eo_no, PM.revision,
           (SELECT COUNT(1) FROM attach_file_info F WHERE F.target_objid = PM.objid::varchar AND F.doc_type='3D_CAD')         AS cu01_cnt,
           (SELECT COUNT(1) FROM attach_file_info F WHERE F.target_objid = PM.objid::varchar AND F.doc_type='2D_DRAWING_CAD') AS cu02_cnt,
           (SELECT COUNT(1) FROM attach_file_info F WHERE F.target_objid = PM.objid::varchar AND F.doc_type='2D_PDF_CAD')     AS cu03_cnt,
           (SELECT COALESCE(MAX(lev), 0) FROM TREE) AS max_level
      FROM TREE T
      LEFT JOIN part_mng PM  ON T.part_no = PM.objid::varchar
      LEFT JOIN comm_code CC ON CC.code_id = PM.part_type
      ${finalWhere}
     ORDER BY T.path
  `;
  const r = await pool.query(sql, params);
  const max_level = r.rows[0]?.max_level ?? 0;
  return { rows: r.rows, max_level };
}

// ─── M4 엑셀 다운로드용 풀 컬럼 (wace structureAscendingListExcel.jsp 1:1) ──
// 그리드 ascending/descending 보다 추가: P_QTY(=bom_part_qty.item_qty), MAKER, PART_TYPE_TITLE,
// HEAT_TREATMENT_HARDNESS, HEAT_TREATMENT_METHOD, SURFACE_TREATMENT
//
// 엑셀 컬럼 (wace 1:1):
//   동적 L1..LMaxLevel ("*" 표시) + 품번 / 품명 / 수량(QTY) / 항목수량(P_QTY) /
//   3D / 2D / PDF (Y/공백) / 재료 / 열처리경도 / 열처리방법 / 표면처리 / 메이커 / 범주 이름 / 비고

function buildAscendingExcelSql(filter: BomTreeFilter, startIdx: number) {
  const params: any[] = [];
  const conds: string[] = [];
  let idx = startIdx;

  if (filter.bom_report_objid) {
    conds.push(`BP.bom_report_objid = $${idx++}`);
    params.push(filter.bom_report_objid);
  } else if (filter.project_name || filter.unit_code) {
    const subConds: string[] = [];
    if (filter.project_name) { subConds.push(`contract_objid = $${idx++}`); params.push(filter.project_name); }
    if (filter.unit_code)    { subConds.push(`unit_code = $${idx++}`); params.push(filter.unit_code); }
    conds.push(`BP.bom_report_objid IN (SELECT objid FROM part_bom_report WHERE ${subConds.join(" AND ")})`);
  }
  // 품번/품명 검색 — anchor 단계로 (매칭 PART 가 들어있는 BOM 전체 트리 표시)
  if (filter.search_part_no) {
    conds.push(`BP.bom_report_objid IN (
      SELECT DISTINCT BQ.bom_report_objid FROM bom_part_qty BQ
       WHERE EXISTS (
         SELECT 1 FROM part_mng PMS
          WHERE PMS.objid::varchar = BQ.part_no
            AND UPPER(PMS.part_no) LIKE UPPER($${idx++})
       )
    )`);
    params.push(`%${filter.search_part_no}%`);
  }
  if (filter.search_part_name) {
    conds.push(`BP.bom_report_objid IN (
      SELECT DISTINCT BQ.bom_report_objid FROM bom_part_qty BQ
       WHERE EXISTS (
         SELECT 1 FROM part_mng PMS
          WHERE PMS.objid::varchar = BQ.part_no
            AND UPPER(PMS.part_name) LIKE UPPER($${idx++})
       )
    )`);
    params.push(`%${filter.search_part_name}%`);
  }
  const startWhere = conds.length ? conds.join(" AND ") : "1=1";

  // 엑셀은 search_level 적용 안 함 (전체 트리 다운로드가 자연스러움). 필요 시 추가.
  const finalWhere = "";

  return { params, startWhere, finalWhere };
}

export async function ascendingForExcel(filter: BomTreeFilter) {
  const { params, startWhere, finalWhere } = buildAscendingExcelSql(filter, 1);
  const sql = `
    WITH RECURSIVE TREE(bom_report_objid, objid, parent_objid, child_objid, part_no, qty, item_qty, seq, status, lev, path, cycle) AS (
       SELECT BP.bom_report_objid, BP.objid, BP.parent_objid, BP.child_objid,
              BP.part_no, BP.qty, BP.item_qty, BP.seq, BP.status,
              1, ARRAY[BP.objid::varchar], FALSE
         FROM bom_part_qty BP
        WHERE (BP.parent_objid IS NULL OR BP.parent_objid = '')
          AND ${startWhere}
       UNION ALL
       SELECT B.bom_report_objid, B.objid, B.parent_objid, B.child_objid,
              B.part_no, B.qty, B.item_qty, B.seq, B.status,
              T.lev + 1, T.path || B.objid::varchar, B.objid::varchar = ANY(T.path)
         FROM bom_part_qty B
         JOIN TREE T ON B.parent_objid = T.child_objid AND NOT T.cycle
    )
    SELECT T.lev,
           PM.part_no   AS pm_part_no,
           PM.part_name AS pm_part_name,
           T.qty, T.item_qty AS p_qty,
           PM.material, PM.remark,
           PM.heat_treatment_hardness, PM.heat_treatment_method, PM.surface_treatment,
           PM.maker, PM.part_type,
           CC.code_name AS part_type_title,
           (SELECT COUNT(1) FROM attach_file_info F WHERE F.target_objid = PM.objid::varchar AND F.doc_type='3D_CAD')         AS cu01_cnt,
           (SELECT COUNT(1) FROM attach_file_info F WHERE F.target_objid = PM.objid::varchar AND F.doc_type='2D_DRAWING_CAD') AS cu02_cnt,
           (SELECT COUNT(1) FROM attach_file_info F WHERE F.target_objid = PM.objid::varchar AND F.doc_type='2D_PDF_CAD')     AS cu03_cnt,
           (SELECT COALESCE(MAX(lev), 0) FROM TREE) AS max_level
      FROM TREE T
      LEFT JOIN part_mng PM  ON T.part_no = PM.objid::varchar
      LEFT JOIN comm_code CC ON CC.code_id = PM.part_type
      ${finalWhere}
     ORDER BY T.path
  `;
  const r = await getPool().query(sql, params);
  const max_level = r.rows[0]?.max_level ?? 0;
  return { rows: r.rows, max_level };
}

export async function descendingForExcel(filter: BomTreeFilter) {
  const params: any[] = [];
  const anchorConds: string[] = [];
  let idx = 1;

  if (filter.search_part_no) {
    anchorConds.push(`EXISTS (SELECT 1 FROM part_mng PMA WHERE PMA.objid::varchar = BP.part_no AND UPPER(PMA.part_no) LIKE UPPER($${idx++}))`);
    params.push(`%${filter.search_part_no}%`);
  }
  if (filter.search_part_name) {
    anchorConds.push(`EXISTS (SELECT 1 FROM part_mng PMA WHERE PMA.objid::varchar = BP.part_no AND UPPER(PMA.part_name) LIKE UPPER($${idx++}))`);
    params.push(`%${filter.search_part_name}%`);
  }
  if (filter.bom_report_objid) {
    anchorConds.push(`BP.bom_report_objid = $${idx++}`);
    params.push(filter.bom_report_objid);
  } else if (filter.project_name || filter.unit_code) {
    const subConds: string[] = [];
    if (filter.project_name) { subConds.push(`contract_objid = $${idx++}`); params.push(filter.project_name); }
    if (filter.unit_code)    { subConds.push(`unit_code = $${idx++}`); params.push(filter.unit_code); }
    anchorConds.push(`BP.bom_report_objid IN (SELECT objid FROM part_bom_report WHERE ${subConds.join(" AND ")})`);
  }
  if (anchorConds.length === 0) return { rows: [], max_level: 0 };
  const anchorWhere = anchorConds.join(" AND ");

  const sql = `
    WITH RECURSIVE TREE(bom_report_objid, objid, parent_objid, child_objid, part_no, qty, item_qty, seq, status, lev, path, cycle) AS (
       SELECT BP.bom_report_objid, BP.objid, BP.parent_objid, BP.child_objid,
              BP.part_no, BP.qty, BP.item_qty, BP.seq, BP.status,
              1, ARRAY[BP.objid::varchar], FALSE
         FROM bom_part_qty BP
        WHERE ${anchorWhere}
       UNION ALL
       SELECT B.bom_report_objid, B.objid, B.parent_objid, B.child_objid,
              B.part_no, B.qty, B.item_qty, B.seq, B.status,
              T.lev + 1, T.path || B.objid::varchar, B.objid::varchar = ANY(T.path)
         FROM bom_part_qty B
         JOIN TREE T ON B.child_objid = T.parent_objid AND NOT T.cycle
    )
    SELECT T.lev,
           PM.part_no   AS pm_part_no,
           PM.part_name AS pm_part_name,
           T.qty, T.item_qty AS p_qty,
           PM.material, PM.remark,
           PM.heat_treatment_hardness, PM.heat_treatment_method, PM.surface_treatment,
           PM.maker, PM.part_type,
           CC.code_name AS part_type_title,
           (SELECT COUNT(1) FROM attach_file_info F WHERE F.target_objid = PM.objid::varchar AND F.doc_type='3D_CAD')         AS cu01_cnt,
           (SELECT COUNT(1) FROM attach_file_info F WHERE F.target_objid = PM.objid::varchar AND F.doc_type='2D_DRAWING_CAD') AS cu02_cnt,
           (SELECT COUNT(1) FROM attach_file_info F WHERE F.target_objid = PM.objid::varchar AND F.doc_type='2D_PDF_CAD')     AS cu03_cnt,
           (SELECT COALESCE(MAX(lev), 0) FROM TREE) AS max_level
      FROM TREE T
      LEFT JOIN part_mng PM  ON T.part_no = PM.objid::varchar
      LEFT JOIN comm_code CC ON CC.code_id = PM.part_type
     ORDER BY T.path
  `;
  const r = await getPool().query(sql, params);
  const max_level = r.rows[0]?.max_level ?? 0;
  return { rows: r.rows, max_level };
}

// ─── M4 역전개 (재귀 CTE — parent 방향) ────────────────────

export async function descending(filter: BomTreeFilter) {
  const pool = getPool();
  const params: any[] = [];
  const anchorConds: string[] = [];
  let idx = 1;

  // anchor: PART 검색 필터로 leaf 후보 선택. 없으면 전체 (잎-가지가 자식 없는 행)
  if (filter.search_part_no) {
    anchorConds.push(`EXISTS (SELECT 1 FROM part_mng PMA WHERE PMA.objid::varchar = BP.part_no AND UPPER(PMA.part_no) LIKE UPPER($${idx++}))`);
    params.push(`%${filter.search_part_no}%`);
  }
  if (filter.search_part_name) {
    anchorConds.push(`EXISTS (SELECT 1 FROM part_mng PMA WHERE PMA.objid::varchar = BP.part_no AND UPPER(PMA.part_name) LIKE UPPER($${idx++}))`);
    params.push(`%${filter.search_part_name}%`);
  }
  if (filter.bom_report_objid) {
    anchorConds.push(`BP.bom_report_objid = $${idx++}`);
    params.push(filter.bom_report_objid);
  } else if (filter.project_name || filter.unit_code) {
    const subConds: string[] = [];
    if (filter.project_name) { subConds.push(`contract_objid = $${idx++}`); params.push(filter.project_name); }
    if (filter.unit_code)    { subConds.push(`unit_code = $${idx++}`); params.push(filter.unit_code); }
    anchorConds.push(`BP.bom_report_objid IN (SELECT objid FROM part_bom_report WHERE ${subConds.join(" AND ")})`);
  }
  if (anchorConds.length === 0) {
    // 검색 조건이 전혀 없으면 빈 결과 반환 (역전개는 통상 PART 한정 조회)
    return { rows: [], max_level: 0 };
  }
  const anchorWhere = anchorConds.join(" AND ");

  // 표시 레벨 필터 (wace search_level 1:1)
  const levelWhereParts: string[] = [];
  if (filter.search_level) {
    levelWhereParts.push(`T.lev <= $${idx++}::int`);
    params.push(filter.search_level);
  }
  const levelWhere = levelWhereParts.length ? `WHERE ${levelWhereParts.join(" AND ")}` : "";

  const sql = `
    WITH RECURSIVE TREE(bom_report_objid, objid, parent_objid, child_objid, part_no, qty, seq, status, lev, path, cycle) AS (
       SELECT BP.bom_report_objid, BP.objid, BP.parent_objid, BP.child_objid,
              BP.part_no, BP.qty, BP.seq, BP.status,
              1, ARRAY[BP.objid::varchar], FALSE
         FROM bom_part_qty BP
        WHERE ${anchorWhere}
       UNION ALL
       SELECT B.bom_report_objid, B.objid, B.parent_objid, B.child_objid,
              B.part_no, B.qty, B.seq, B.status,
              T.lev + 1, T.path || B.objid::varchar, B.objid::varchar = ANY(T.path)
         FROM bom_part_qty B
         JOIN TREE T ON B.child_objid = T.parent_objid AND NOT T.cycle
    )
    SELECT T.bom_report_objid, T.objid, T.parent_objid, T.child_objid, T.part_no, T.qty, T.seq, T.status,
           T.lev, T.path,
           PM.part_no   AS pm_part_no,
           PM.part_name AS pm_part_name,
           PM.spec, PM.material, PM.weight, PM.remark,
           PM.edit_date,
           PM.eo_no, PM.revision,
           (SELECT COUNT(1) FROM attach_file_info F WHERE F.target_objid = PM.objid::varchar AND F.doc_type='3D_CAD')         AS cu01_cnt,
           (SELECT COUNT(1) FROM attach_file_info F WHERE F.target_objid = PM.objid::varchar AND F.doc_type='2D_DRAWING_CAD') AS cu02_cnt,
           (SELECT COUNT(1) FROM attach_file_info F WHERE F.target_objid = PM.objid::varchar AND F.doc_type='2D_PDF_CAD')     AS cu03_cnt,
           (SELECT COALESCE(MAX(lev), 0) FROM TREE) AS max_level
      FROM TREE T
      LEFT JOIN part_mng PM ON T.part_no = PM.objid::varchar
      ${levelWhere}
     ORDER BY T.path
  `;
  const r = await pool.query(sql, params);
  const max_level = r.rows[0]?.max_level ?? 0;
  return { rows: r.rows, max_level };
}
