// ============================================================
// 개발관리 BOM Report CSV Import 서비스 — wace_plm PartMngService.parsingCsvFile 1:1
//
// 운영판 흐름 (wace partMng/openBomReportExcelImportPopUp.jsp):
//   · Drop Zone: "Drag & Drop CSV 템플릿" + fnc_setFileDropZone(..., "csv")  → CSV 전용
//   · /partMng/parsingExcelFile.do 의 .csv 분기에서 parsingCsvFile() 호출
//   · /partMng/partBomApplySave.do 의 savePartBomMaster() 로 저장
//
// CSV 컬럼 (11개, 헤더 1줄 후 데이터):
//   0:수준 1:품번 2:품명 3:수량 4:항목수량 5:재료 6:열처리경도 7:열처리방법
//   8:표면처리 9:공급업체(MAKER) 10:범주이름(PART_TYPE)
//   (11번 이후 컬럼은 파싱하지 않음 — 범주이름에 따라 자동값 설정)
//
// 핵심: PARENT_PART_NO 컬럼이 없음. "수준(level)" 으로 부모-자식 자동 결정.
//   · 숫자("1","2","3","4"): 깊이 D 행의 부모 = depthToPartNoMap[D-1] (마지막에 등록된 D-1 깊이 품번)
//   · 점 표기법("1","1.1","1.4.1"): 마지막 점 이전이 부모 수준 → levelToPartNoMap[parentLevel]
//
// CSV 자동 변환:
//   · Assy/ASSY → 조립품, Buy/BUY → 구매품, Make/MAKE → 부품 (영문 PART_TYPE → 한글)
//   · 범주 → 계정구분/조달구분 자동 (조립품 0001813 / 부품 0001812 → ACCTFG=4, ODRFG=1;
//                                    구매품 0000063 → ACCTFG=7, ODRFG=0)
//   · 기본값 일괄: UNIT_DC=0001400(EA), UNITMANG_DC=0001400, UNITCHNG_NB=1,
//                  LOT_FG=1(사용), USE_YN=1(사용), QC_FG=0(무검사), SETITEM_FG=0(부), REQ_FG=0(부)
//
// 검증 (wace 1:1):
//   · 모품번 자품번 목록 존재 검증은 rowIndex > 2 일 때만 (1·2 레벨 면제)
//   · PART_TYPE 매핑 실패: NOTE "부품유형 확인:..."
//   · 결과 누적: emptyColCnt < 9 또는 NOTE 있으면 row 채택
//
// 인코딩 자동 감지 (wace detectFileEncoding 1:1):
//   CP949 → UTF-8 → EUC-KR → MS949 순서. 각각 디코딩 → 깨진 문자(�) 개수 세서 0이면 즉시 선택,
//   아니면 가장 적은 것. UTF-8 BOM(EF BB BF) 자동 제거.
//
// 저장 (savePartBomMaster 1:1):
//   · 헤더 part_bom_report INSERT(신규) / DELETE 자식트리+UPDATE(수정)
//   · 자식 PART: IS_LAST='1' 존재 시 updatePartInfoFromCsv UPDATE, 없으면 insertpartInfo INSERT
//   · 부모 PART: 존재 시 lookup, 없으면 "" (절대 INSERT 안 함)
//   · bom_part_qty INSERT (relatePartInfo) — 부모행 CHILD_OBJID 체인
// ============================================================

import * as iconv from "iconv-lite";
import { parse as parseCsvSync } from "csv-parse/sync";
import { PoolClient } from "pg";
import { getPool, transaction } from "../database/db";
import { logger } from "../utils/logger";
import { createObjId } from "../utils/objidUtil";

const CODE_PARENT_PART_TYPE = "0000062";
const PART_TYPE_ASSEMBLY = "0001813";  // 조립품
const PART_TYPE_PART     = "0001812";  // 부품
const PART_TYPE_BUY      = "0000063";  // 구매품

const DEFAULT_UNIT_DC      = "0001400"; // EA
const DEFAULT_UNITMANG_DC  = "0001400"; // EA
const DEFAULT_UNITCHNG_NB  = "1";
const DEFAULT_LOT_FG       = "1";       // 사용
const DEFAULT_USE_YN       = "1";       // 사용
const DEFAULT_QC_FG        = "0";       // 무검사
const DEFAULT_SETITEM_FG   = "0";       // 부
const DEFAULT_REQ_FG       = "0";       // 부

export interface BomCsvRow {
  NOTE: string;
  LEVEL: string;            // 표시용 (CSV 수준)
  PARENT_PART_NO: string;   // 수준에서 계산된 부모 품번
  PART_NO: string;
  PART_NAME: string;
  QTY: string;
  ITEM_QTY: string;
  MATERIAL: string;
  HEAT_TREATMENT_HARDNESS: string;
  HEAT_TREATMENT_METHOD: string;
  SURFACE_TREATMENT: string;
  MAKER: string;
  PART_TYPE: string;          // code_id (자동 변환된 한글 → DB 조회 결과)
  PART_TYPE_NAME: string;     // 한글 (Assy→조립품 변환 적용)
  ACCTFG: string;
  ODRFG: string;
  UNIT_DC: string;
  UNITMANG_DC: string;
  UNITCHNG_NB: string;
  LOT_FG: string;
  USE_YN: string;
  QC_FG: string;
  SETITEM_FG: string;
  REQ_FG: string;
}

function appendNote(r: BomCsvRow, msg: string) {
  r.NOTE = r.NOTE ? `${r.NOTE} / ${msg}` : msg;
}

// ─── 헬퍼 ───────────────────────────────────────────────────

// wace getCsvValue 1:1 — 따옴표 제거 + 빈 카운트
function getCsvValue(values: string[], idx: number, emptyCounter: { count: number }): string {
  if (idx >= values.length) {
    emptyCounter.count++;
    return "";
  }
  let v = (values[idx] ?? "").trim();
  if (v.length > 1 && v.startsWith('"') && v.endsWith('"')) {
    v = v.substring(1, v.length - 1);
  }
  if (!v) emptyCounter.count++;
  return v;
}

// wace getParentLevel 1:1: "1.4.1" → "1.4" / "1.8" → "1" / "1" → ""
function getParentLevel(level: string): string {
  if (!level) return "";
  const lastDot = level.lastIndexOf(".");
  return lastDot > 0 ? level.substring(0, lastDot) : "";
}

// CSV 영문 범주명 → 한글 (wace 1:1)
function normalizePartTypeName(raw: string): string {
  const upper = raw.toUpperCase();
  if (upper === "ASSY") return "조립품";
  if (upper === "BUY")  return "구매품";
  if (upper === "MAKE") return "부품";
  return raw;
}

// 범주 code_id → ACCTFG/ODRFG 자동 매핑 (wace 1:1)
function autoAcctfgOdrfg(partTypeCode: string): { acctfg: string; odrfg: string } {
  if (partTypeCode === PART_TYPE_ASSEMBLY || partTypeCode === PART_TYPE_PART) {
    return { acctfg: "4", odrfg: "1" };  // 반제품/생산
  }
  if (partTypeCode === PART_TYPE_BUY) {
    return { acctfg: "7", odrfg: "0" };  // 비용/구매
  }
  return { acctfg: "", odrfg: "" };
}

// 인코딩 자동 감지 (wace detectFileEncoding 1:1)
function detectAndDecode(buffer: Buffer): { text: string; encoding: string } {
  const encodings = ["cp949", "utf-8", "euc-kr", "ms949"];
  let bestEncoding = "utf-8";
  let bestText = "";
  let minReplacement = Number.POSITIVE_INFINITY;

  for (const enc of encodings) {
    try {
      const decoded = iconv.decode(buffer, enc);
      let replacementCount = 0;
      const sample = decoded.substring(0, 4096);
      for (let i = 0; i < sample.length; i++) {
        if (sample.charCodeAt(i) === 0xFFFD) replacementCount++;
      }
      if (replacementCount === 0) {
        return { text: stripBom(decoded), encoding: enc };
      }
      if (replacementCount < minReplacement) {
        minReplacement = replacementCount;
        bestEncoding = enc;
        bestText = decoded;
      }
    } catch {
      continue;
    }
  }
  return { text: stripBom(bestText), encoding: bestEncoding };
}

function stripBom(s: string): string {
  if (s.length > 0 && s.charCodeAt(0) === 0xFEFF) return s.substring(1);
  return s;
}

async function fetchPartTypeMap(client: PoolClient): Promise<Map<string, { code_id: string; code_name: string }>> {
  const r = await client.query(
    `SELECT CODE_ID AS code_id, CODE_NAME AS code_name
       FROM COMM_CODE WHERE PARENT_CODE_ID = $1`,
    [CODE_PARENT_PART_TYPE]
  );
  const m = new Map<string, { code_id: string; code_name: string }>();
  for (const row of r.rows) if (row.code_name) m.set(String(row.code_name).trim().toUpperCase(), row);
  return m;
}

// ─── 1) CSV 파싱 + 검증 (parsingCsvFile 1:1) ────────────────

export async function parseAndValidate(buffer: Buffer): Promise<{
  rows: BomCsvRow[];
  hasError: boolean;
  firstLevel: { part_no: string; part_name: string } | null;
  encoding: string;
}> {
  const { text, encoding } = detectAndDecode(buffer);

  // RFC4180 호환 파서 — 따옴표 내부 콤마/줄바꿈, "" 이스케이프 모두 안전 처리
  // (이전: line.split(",") 단순 split → 따옴표 안 콤마/멀티라인 셀 깨짐)
  // relax_quotes / relax_column_count : 운영판 wace 사용자가 만든 CSV 의 비정형 quote 도 관대 처리
  const allRows: string[][] = parseCsvSync(text, {
    columns: false,
    skip_empty_lines: false,
    relax_quotes: true,
    relax_column_count: true,
    trim: false,
  });

  // 1차 스캔: 모든 자품번 + 수준→품번 매핑 (wace 1:1)
  const allPartNumbers = new Set<string>();
  const levelToPartNoMap = new Map<string, string>();

  for (let i = 0; i < allRows.length; i++) {
    const values = allRows[i];
    if (!values || values.length < 2) continue;
    if (i === 0) continue; // 헤더
    const level = (values[0] ?? "").trim();
    const partNo = (values[1] ?? "").trim();
    if (partNo) {
      allPartNumbers.add(partNo);
      if (level) levelToPartNoMap.set(level, partNo);
    }
  }

  const result: BomCsvRow[] = [];
  let firstLevel: { part_no: string; part_name: string } | null = null;

  // 2차 스캔: 행 파싱 + 부모 결정 + 검증
  const currentDepthPartNoMap = new Map<number, string>();  // 숫자 수준용 (wace 1:1)

  const client = await getPool().connect();
  try {
    const partTypeMap = await fetchPartTypeMap(client);

    let rowIndex = 0;
    for (const values of allRows) {
      if (rowIndex === 0) { rowIndex++; continue; } // 헤더 skip
      if (values.length < 11) { rowIndex++; continue; }  // wace: 최소 11컬럼 (수준 포함)

      const emptyCounter = { count: 0 };
      let colIndex = 0;
      const level                = getCsvValue(values, colIndex++, emptyCounter);
      const partNo               = getCsvValue(values, colIndex++, emptyCounter);
      const partName             = getCsvValue(values, colIndex++, emptyCounter);
      const qty                  = getCsvValue(values, colIndex++, emptyCounter);
      const itemQty              = getCsvValue(values, colIndex++, emptyCounter);
      const material             = getCsvValue(values, colIndex++, emptyCounter);
      const heatHardness         = getCsvValue(values, colIndex++, emptyCounter);
      const heatMethod           = getCsvValue(values, colIndex++, emptyCounter);
      const surfaceTreatment     = getCsvValue(values, colIndex++, emptyCounter);
      const supplier             = getCsvValue(values, colIndex++, emptyCounter);
      const partTypeRaw          = getCsvValue(values, colIndex++, emptyCounter);

      // 수준 → 부모 결정 (wace 1:1)
      let parentPartNo = "";
      if (level) {
        const asInt = Number(level);
        if (Number.isInteger(asInt) && /^\d+$/.test(level)) {
          // 숫자 수준: 깊이 D 행 → D-1 깊이의 최신 품번이 부모
          const currentDepth = asInt;
          if (partNo) currentDepthPartNoMap.set(currentDepth, partNo);
          if (currentDepth > 1) {
            parentPartNo = currentDepthPartNoMap.get(currentDepth - 1) ?? "";
          }
        } else {
          // 점 표기법: 마지막 점 이전 = 부모 수준
          const parentLevel = getParentLevel(level);
          if (parentLevel) parentPartNo = levelToPartNoMap.get(parentLevel) ?? "";
        }
      }

      let noteMsg = "";

      // 모품번 자품번 목록 존재 검증 (wace: rowIndex > 2 일 때만)
      if (parentPartNo && rowIndex > 2 && !allPartNumbers.has(parentPartNo)) {
        noteMsg += `모품번 미존재: ${parentPartNo} ; `;
      }

      // PART_TYPE 처리 (wace 1:1)
      const partTypeNormalized = partTypeRaw ? normalizePartTypeName(partTypeRaw) : "";
      let partTypeCode = "";
      if (partTypeNormalized) {
        const hit = partTypeMap.get(partTypeNormalized.toUpperCase());
        if (hit) {
          partTypeCode = hit.code_id;
        } else if (rowIndex > 2) {
          noteMsg += `부품유형 확인: ${partTypeRaw} ; `;
        }
      }

      const { acctfg, odrfg } = autoAcctfgOdrfg(partTypeCode);

      const row: BomCsvRow = {
        NOTE: noteMsg.trim().replace(/\s*;\s*$/, ""),
        LEVEL: level,
        PARENT_PART_NO: parentPartNo,
        PART_NO: partNo,
        PART_NAME: partName,
        QTY: qty,
        ITEM_QTY: itemQty || qty,
        MATERIAL: material,
        HEAT_TREATMENT_HARDNESS: heatHardness,
        HEAT_TREATMENT_METHOD: heatMethod,
        SURFACE_TREATMENT: surfaceTreatment,
        MAKER: supplier,
        PART_TYPE: partTypeCode,
        PART_TYPE_NAME: partTypeNormalized,
        ACCTFG: acctfg,
        ODRFG: odrfg,
        UNIT_DC: DEFAULT_UNIT_DC,
        UNITMANG_DC: DEFAULT_UNITMANG_DC,
        UNITCHNG_NB: DEFAULT_UNITCHNG_NB,
        LOT_FG: DEFAULT_LOT_FG,
        USE_YN: DEFAULT_USE_YN,
        QC_FG: DEFAULT_QC_FG,
        SETITEM_FG: DEFAULT_SETITEM_FG,
        REQ_FG: DEFAULT_REQ_FG,
      };

      // wace: NOTE 있거나 emptyColCnt < 9 면 결과 채택
      if (row.NOTE || emptyCounter.count < 9) {
        result.push(row);
      }

      // 첫 1레벨(parent 없는 첫 행) → 헤더 자동
      if (!firstLevel && !parentPartNo && partNo) {
        firstLevel = { part_no: partNo, part_name: partName };
      }

      rowIndex++;
    }
  } finally {
    client.release();
  }

  const hasError = result.some((r) => r.NOTE);
  return { rows: result, hasError, firstLevel, encoding };
}

// ─── 2) 헤더 part_no 중복 검사 (wace checkDuplicatePartNo) ──

export async function checkDuplicateBomPartNo(partNo: string, excludeObjid?: string): Promise<boolean> {
  if (!partNo) return false;
  const sql = excludeObjid
    ? `SELECT 1 FROM PART_BOM_REPORT WHERE PART_NO = $1 AND OBJID <> $2 LIMIT 1`
    : `SELECT 1 FROM PART_BOM_REPORT WHERE PART_NO = $1 LIMIT 1`;
  const params = excludeObjid ? [partNo.trim(), excludeObjid] : [partNo.trim()];
  const r = await getPool().query(sql, params);
  return (r.rowCount ?? 0) > 0;
}

// ─── 3) E-BOM 복사: 기존 BOM_PART_QTY → BomCsvRow[] ────────

export async function copyBomForGrid(sourceObjid: string): Promise<BomCsvRow[]> {
  const sql = `
    WITH RECURSIVE TREE AS (
       SELECT BP.OBJID, BP.PARENT_OBJID, BP.CHILD_OBJID, BP.PART_NO, BP.PARENT_PART_NO, BP.QTY, BP.ITEM_QTY,
              1 AS LEV, ARRAY[BP.SEQ] AS PATH
         FROM BOM_PART_QTY BP
        WHERE BP.BOM_REPORT_OBJID = $1
          AND (BP.PARENT_OBJID IS NULL OR BP.PARENT_OBJID = '')
       UNION ALL
       SELECT B.OBJID, B.PARENT_OBJID, B.CHILD_OBJID, B.PART_NO, B.PARENT_PART_NO, B.QTY, B.ITEM_QTY,
              T.LEV + 1, T.PATH || B.SEQ
         FROM BOM_PART_QTY B
         JOIN TREE T ON B.PARENT_OBJID = T.CHILD_OBJID
        WHERE B.BOM_REPORT_OBJID = $1
    )
    SELECT T.LEV,
           PM.PART_NO        AS PART_NO_REAL,
           PM.PART_NAME      AS PART_NAME,
           T.QTY, T.ITEM_QTY,
           PM.MATERIAL, PM.HEAT_TREATMENT_HARDNESS, PM.HEAT_TREATMENT_METHOD, PM.SURFACE_TREATMENT,
           PM.MAKER, PM.PART_TYPE,
           CC.CODE_NAME      AS PART_TYPE_NAME,
           PM_PARENT.PART_NO AS PARENT_PART_NO_REAL,
           PM.ACCTFG, PM.ODRFG,
           PM.UNIT_DC, PM.UNITMANG_DC, PM.UNITCHNG_NB,
           PM.LOT_FG, PM.USE_YN, PM.QC_FG, PM.SETITEM_FG, PM.REQ_FG,
           T.PATH
      FROM TREE T
      LEFT JOIN PART_MNG PM        ON PM.OBJID::varchar = T.PART_NO
      LEFT JOIN PART_MNG PM_PARENT ON PM_PARENT.OBJID::varchar = T.PARENT_PART_NO
      LEFT JOIN COMM_CODE CC       ON CC.CODE_ID = PM.PART_TYPE
     ORDER BY T.PATH
  `;
  const r = await getPool().query(sql, [sourceObjid]);
  return r.rows.map((row): BomCsvRow => ({
    NOTE: "",
    LEVEL: String(row.lev ?? ""),
    PARENT_PART_NO: row.parent_part_no_real ?? "",
    PART_NO: row.part_no_real ?? "",
    PART_NAME: row.part_name ?? "",
    QTY: row.qty != null ? String(row.qty) : "",
    ITEM_QTY: row.item_qty != null ? String(row.item_qty) : (row.qty != null ? String(row.qty) : ""),
    MATERIAL: row.material ?? "",
    HEAT_TREATMENT_HARDNESS: row.heat_treatment_hardness ?? "",
    HEAT_TREATMENT_METHOD: row.heat_treatment_method ?? "",
    SURFACE_TREATMENT: row.surface_treatment ?? "",
    MAKER: row.maker ?? "",
    PART_TYPE: row.part_type ?? "",
    PART_TYPE_NAME: row.part_type_name ?? "",
    ACCTFG: row.acctfg ?? "",
    ODRFG: row.odrfg ?? "",
    UNIT_DC: row.unit_dc ?? DEFAULT_UNIT_DC,
    UNITMANG_DC: row.unitmang_dc ?? DEFAULT_UNITMANG_DC,
    UNITCHNG_NB: row.unitchng_nb != null ? String(row.unitchng_nb) : DEFAULT_UNITCHNG_NB,
    LOT_FG: row.lot_fg ?? DEFAULT_LOT_FG,
    USE_YN: row.use_yn ?? DEFAULT_USE_YN,
    QC_FG: row.qc_fg ?? DEFAULT_QC_FG,
    SETITEM_FG: row.setitem_fg ?? DEFAULT_SETITEM_FG,
    REQ_FG: row.req_fg ?? DEFAULT_REQ_FG,
  }));
}

// ─── 4) BOM 저장 (savePartBomMaster 1:1) ────────────────────

export interface BomSaveInput {
  bomReportObjid?: string;
  productCd: string;
  partNo: string;
  partName: string;
  version?: string;
  rows: BomCsvRow[];
}

export interface BomSaveResult {
  bomReportObjid: string;
  insertedParts: number;
  updatedParts: number;
  bomRows: number;
  mode: "create" | "update";
}

export async function saveBomReport(userId: string, input: BomSaveInput): Promise<BomSaveResult> {
  if (!input.productCd) throw new Error("제품구분은 필수입니다.");
  if (!input.partNo)    throw new Error("품번은 필수입니다.");
  if (!input.partName)  throw new Error("품명은 필수입니다.");

  let insertedParts = 0;
  let updatedParts = 0;
  let bomRows = 0;
  let bomReportObjid = (input.bomReportObjid && input.bomReportObjid.trim()) ? input.bomReportObjid.trim() : "";
  const mode: "create" | "update" = bomReportObjid ? "update" : "create";

  await transaction(async (client: PoolClient) => {
    if (mode === "update") {
      await client.query(`DELETE FROM BOM_PART_QTY WHERE BOM_REPORT_OBJID = $1`, [bomReportObjid]);
      await client.query(
        `UPDATE PART_BOM_REPORT
            SET STATUS = 'N', WRITER = $1, edit_date = NOW(),
                PRODUCT_CD = $2, PART_NO = $3, PART_NAME = $4, REVISION = $5
          WHERE OBJID = $6`,
        [userId, input.productCd, input.partNo, input.partName, input.version ?? null, bomReportObjid]
      );
    } else {
      bomReportObjid = createObjId();
      await client.query(
        `INSERT INTO PART_BOM_REPORT (
            OBJID, CUSTOMER_OBJID, CONTRACT_OBJID, UNIT_CODE,
            STATUS, WRITER, REGDATE,
            MULTI_YN, MULTI_MASTER_YN, MULTI_BREAK_YN, MULTI_MASTER_OBJID,
            PRODUCT_CD, PART_NO, PART_NAME, REVISION
         ) VALUES (
            $1, NULL, NULL, NULL,
            'N', $2, NOW(),
            'N', 'N', NULL, NULL,
            $3, $4, $5, $6
         )`,
        [bomReportObjid, userId, input.productCd, input.partNo, input.partName, input.version ?? null]
      );
    }

    if (!input.rows || input.rows.length === 0) return;

    const partObjIdCache = new Map<string, string>();           // PART_NO → part_mng.objid (자식·부모 공용 캐시)
    const childBomObjIdByPartNo = new Map<string, string>();    // PART_NO → bom_part_qty.child_objid

    // 자식 PART: 있으면 updatePartInfoFromCsv UPDATE, 없으면 insertpartInfo INSERT (wace 1:1)
    async function upsertChildPart(r: BomCsvRow): Promise<string> {
      if (partObjIdCache.has(r.PART_NO)) return partObjIdCache.get(r.PART_NO)!;

      const exist = await client.query(
        `SELECT OBJID::varchar AS part_objid FROM PART_MNG WHERE PART_NO = $1 AND IS_LAST = '1' LIMIT 1`,
        [r.PART_NO]
      );
      if ((exist.rowCount ?? 0) > 0) {
        const id = exist.rows[0].part_objid;
        await client.query(
          `UPDATE PART_MNG SET
              PART_NAME               = $1,
              MATERIAL                = $2,
              HEAT_TREATMENT_HARDNESS = $3,
              HEAT_TREATMENT_METHOD   = $4,
              SURFACE_TREATMENT       = $5,
              PART_TYPE               = COALESCE(NULLIF($6, ''), PART_TYPE),
              MAKER                   = $7,
              ACCTFG                  = $8,
              ODRFG                   = $9,
              UNIT_DC                 = $10,
              UNITMANG_DC             = $11,
              UNITCHNG_NB             = COALESCE(NULLIF($12, ''), '0')::numeric,
              LOT_FG                  = COALESCE(NULLIF($13, ''), LOT_FG),
              USE_YN                  = COALESCE(NULLIF($14, ''), USE_YN),
              QC_FG                   = COALESCE(NULLIF($15, ''), QC_FG),
              SETITEM_FG              = COALESCE(NULLIF($16, ''), SETITEM_FG),
              REQ_FG                  = COALESCE(NULLIF($17, ''), REQ_FG),
              EDIT_DATE               = NOW()
            WHERE OBJID = $18::numeric`,
          [
            r.PART_NAME, r.MATERIAL,
            r.HEAT_TREATMENT_HARDNESS, r.HEAT_TREATMENT_METHOD, r.SURFACE_TREATMENT,
            r.PART_TYPE, r.MAKER,
            r.ACCTFG, r.ODRFG,
            r.UNIT_DC, r.UNITMANG_DC, r.UNITCHNG_NB,
            r.LOT_FG, r.USE_YN, r.QC_FG, r.SETITEM_FG, r.REQ_FG,
            id,
          ]
        );
        updatedParts++;
        partObjIdCache.set(r.PART_NO, id);
        return id;
      }

      const newId = createObjId();
      await client.query(
        `INSERT INTO PART_MNG (
            OBJID, PART_NO, PART_NAME, MATERIAL, REMARK,
            STATUS, REG_DATE, WRITER, IS_LAST,
            HEAT_TREATMENT_HARDNESS, HEAT_TREATMENT_METHOD, SURFACE_TREATMENT,
            PART_TYPE, MAKER,
            ACCTFG, ODRFG, UNIT_DC, UNITMANG_DC, UNITCHNG_NB,
            LOT_FG, USE_YN, QC_FG, SETITEM_FG, REQ_FG
         ) VALUES (
            $1::numeric, $2, $3, $4, '',
            'create', NOW(), $5, '1',
            $6, $7, $8,
            NULLIF($9, ''), $10,
            $11, $12, $13, $14,
            COALESCE(NULLIF($15, ''), '0')::numeric,
            COALESCE($16, '0'), COALESCE($17, '1'), COALESCE($18, '0'),
            COALESCE($19, '0'), COALESCE($20, '0')
         )`,
        [
          newId, r.PART_NO, r.PART_NAME, r.MATERIAL,
          userId,
          r.HEAT_TREATMENT_HARDNESS, r.HEAT_TREATMENT_METHOD, r.SURFACE_TREATMENT,
          r.PART_TYPE, r.MAKER,
          r.ACCTFG, r.ODRFG, r.UNIT_DC, r.UNITMANG_DC, r.UNITCHNG_NB,
          r.LOT_FG, r.USE_YN, r.QC_FG, r.SETITEM_FG, r.REQ_FG,
        ]
      );
      insertedParts++;
      partObjIdCache.set(r.PART_NO, newId);
      return newId;
    }

    // 부모 PART: 있으면 lookup, 없으면 "" (wace 1:1 — INSERT 절대 안 함)
    async function lookupParentPart(partNo: string): Promise<string> {
      if (!partNo) return "";
      if (partObjIdCache.has(partNo)) return partObjIdCache.get(partNo)!;
      const exist = await client.query(
        `SELECT OBJID::varchar AS part_objid FROM PART_MNG WHERE PART_NO = $1 AND IS_LAST = '1' LIMIT 1`,
        [partNo]
      );
      if ((exist.rowCount ?? 0) > 0) {
        const id = exist.rows[0].part_objid;
        partObjIdCache.set(partNo, id);
        return id;
      }
      return "";
    }

    for (const r of input.rows) {
      if (!r.PART_NO) continue;

      const partObjid = await upsertChildPart(r);
      const parentPartObjid = await lookupParentPart(r.PARENT_PART_NO);
      const parentBomObjid  = r.PARENT_PART_NO ? (childBomObjIdByPartNo.get(r.PARENT_PART_NO) ?? "") : "";
      const newBomObjid     = createObjId();
      const newChildObjid   = createObjId();

      await client.query(
        `INSERT INTO BOM_PART_QTY (
            BOM_REPORT_OBJID, OBJID, PARENT_OBJID, CHILD_OBJID,
            PARENT_PART_NO, PART_NO,
            QTY, ITEM_QTY, QTY_TEMP,
            REGDATE, WRITER, SEQ, STATUS, LAST_PART_OBJID
         ) VALUES (
            $1, $2, NULLIF($3, ''), $4,
            $5, $6,
            COALESCE(NULLIF($7, ''), '0')::numeric,
            COALESCE(NULLIF($8, ''), '0')::numeric,
            COALESCE(NULLIF($7, ''), '0')::numeric,
            NOW(), $9, nextval('seq_bom_qty'), 'deploy', NULL
         )`,
        [
          bomReportObjid, newBomObjid, parentBomObjid, newChildObjid,
          parentPartObjid, partObjid,
          r.QTY ?? "",
          r.ITEM_QTY ?? r.QTY ?? "",
          userId,
        ]
      );

      childBomObjIdByPartNo.set(r.PART_NO, newChildObjid);
      bomRows++;
    }
  });

  logger.info("BOM CSV Import 저장 완료", { userId, bomReportObjid, mode, insertedParts, updatedParts, bomRows });
  return { bomReportObjid, insertedParts, updatedParts, bomRows, mode };
}

// ─── 5) BOM 목록 (E-BOM 복사 select 옵션) ─────────────────

export async function listForCopySelect(productCd?: string) {
  const params: any[] = [];
  let where = "1=1";
  if (productCd) { params.push(productCd); where = `T.PRODUCT_CD = $1`; }
  const sql = `
    SELECT T.OBJID, T.PART_NO, T.PART_NAME, T.REVISION, T.PRODUCT_CD,
           TO_CHAR(T.REGDATE, 'YYYY-MM-DD') AS REGDATE
      FROM PART_BOM_REPORT T
     WHERE ${where}
     ORDER BY T.REGDATE DESC NULLS LAST
     LIMIT 200
  `;
  const r = await getPool().query(sql, params);
  return r.rows;
}
