// ============================================================
// 개발관리 PART Excel Import 서비스 — wace_plm PartMngService 1:1
//
// 원본 흐름 (wace partMng/openPartExcelImportPopUp.jsp):
//   1) /partMng/partParsingExcelFile.do  → 업로드 파일 파싱 + 검증
//      엑셀 row[2..]부터 22컬럼 추출. 검증 결과는 행마다 NOTE 컬럼에 누적.
//      코드명 → 코드값 매핑 (PART_TYPE/ACCTFG/UNIT_DC/UNITMANG_DC, 한글 → 0/1/8/Y/N).
//      품번 중복 (PART_MNG 기존 + 엑셀 내 중복) → "품번중복" NOTE.
//   2) /partMng/partUploadSave.do        → 그리드 데이터 INSERT
//      partMng.getPartObjid 로 part_no 존재 여부 확인. 없으면 createObjId + mergePartMng INSERT.
//      (NOTE에 에러 있는 행은 클라이언트가 차단)
//
// vexplor_rps 차이:
//   · 운영판은 파일을 서버에 저장 → /partParsingExcelFile.do 가 그 파일을 읽음.
//   · RPS는 multer memoryStorage 로 받은 Buffer 를 그 자리에서 파싱 (WbsTemplate parseExcel 패턴).
//   · 결과 그리드는 클라이언트 메모리에만 유지 → 저장 호출 시 다시 검증 한 번 더 수행.
// ============================================================

import * as XLSX from "xlsx";
import { PoolClient } from "pg";
import { getPool, transaction } from "../database/db";
import { logger } from "../utils/logger";
import { createObjId } from "../utils/objidUtil";

// ─── wace 운영판 PARENT_CODE_ID (commCode) ──────────────────
const CODE_PARENT_PART_TYPE = "0000062";   // 범주 (PART_TYPE)
const CODE_PARENT_ACCTFG    = "0900213";   // 계정구분
const CODE_PARENT_UNIT_DC   = "0001399";   // 단위 (UNIT_DC / UNITMANG_DC)

// 엑셀 22컬럼 (1행 헤더 — wace JSP colModel 순서)
// 0:품번 1:품명 2:재료 3:열처리경도 4:열처리방법 5:표면처리 6:메이커 7:범주 이름
// 8:규격 9:계정구분 10:조달구분 11:재고단위 12:관리단위 13:환산수량
// 14:LOT구분 15:사용여부 16:검사여부 17:SET품여부 18:의뢰여부 19:개당길이 20:개당소요량 21:비고

export interface PartExcelRow {
  NOTE: string;
  PART_NO: string;
  PART_NAME: string;
  MATERIAL: string;
  HEAT_TREATMENT_HARDNESS: string;
  HEAT_TREATMENT_METHOD: string;
  SURFACE_TREATMENT: string;
  MAKER: string;
  PART_TYPE: string;       // 코드값(`code_id`) — 매핑 실패 시 원본 한글
  PART_TYPE_NAME?: string; // 화면용 라벨
  SPEC: string;
  ACCTFG: string;
  ACCTFG_NAME?: string;
  ODRFG: string;
  ODRFG_NAME?: string;
  UNIT_DC: string;
  UNIT_DC_NAME?: string;
  UNITMANG_DC: string;
  UNITMANG_DC_NAME?: string;
  UNITCHNG_NB: string;
  LOT_FG: string;
  USE_YN: string;
  QC_FG: string;
  SETITEM_FG: string;
  REQ_FG: string;
  UNIT_LENGTH: string;
  UNIT_QTY: string;
  REMARK: string;
}

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

function getCell(row: any[], idx: number): string {
  const v = row?.[idx];
  if (v === undefined || v === null) return "";
  return String(v).trim();
}

// ─── 코드명 → 코드값 매핑 (commCode 1:1) ────────────────────

async function fetchCodeMap(
  client: PoolClient,
  parentCodeId: string
): 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`,
    [parentCodeId]
  );
  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;
}

function mapKr01(value: string, t: string, f: string, defaultVal: string): string {
  const v = value.trim();
  if (!v) return defaultVal;
  if (v === t) return "1";
  if (v === f) return "0";
  return v; // 숫자 입력 시 그대로
}
function mapOdrfg(value: string): string {
  const v = value.trim();
  if (!v) return "";
  if (v === "구매") return "0";
  if (v === "생산") return "1";
  if (v.toUpperCase() === "PHANTOM") return "8";
  return v;
}

// ─── 1) 파싱 + 검증 ─────────────────────────────────────────

export async function parseAndValidate(buffer: Buffer): Promise<{
  rows: PartExcelRow[];
  hasError: boolean;
}> {
  const wb = XLSX.read(buffer, { type: "buffer" });
  const sheet = wb.Sheets[wb.SheetNames[0]];
  const raw: any[][] = XLSX.utils.sheet_to_json(sheet, {
    header: 1, raw: false, defval: "",
  });

  const result: PartExcelRow[] = [];
  const client = await getPool().connect();
  try {
    const [partTypeMap, acctfgMap, unitDcMap] = await Promise.all([
      fetchCodeMap(client, CODE_PARENT_PART_TYPE),
      fetchCodeMap(client, CODE_PARENT_ACCTFG),
      fetchCodeMap(client, CODE_PARENT_UNIT_DC),
    ]);

    const partNoSeenInFile = new Map<string, number>();
    let emptyRowCnt = 0;

    // wace: rowIndex = 2 부터 (0=안내라벨 "입력", 1=헤더)
    for (let i = 2; i < raw.length; i++) {
      const row = raw[i];
      if (!row) continue;

      const cur: PartExcelRow = {
        NOTE: "",
        PART_NO: getCell(row, 0),
        PART_NAME: getCell(row, 1),
        MATERIAL: getCell(row, 2),
        HEAT_TREATMENT_HARDNESS: getCell(row, 3),
        HEAT_TREATMENT_METHOD: getCell(row, 4),
        SURFACE_TREATMENT: getCell(row, 5),
        MAKER: getCell(row, 6),
        PART_TYPE: "",
        SPEC: getCell(row, 8),
        ACCTFG: "",
        ODRFG: "",
        UNIT_DC: "",
        UNITMANG_DC: "",
        UNITCHNG_NB: getCell(row, 13),
        LOT_FG: "",
        USE_YN: "",
        QC_FG: "",
        SETITEM_FG: "",
        REQ_FG: "",
        UNIT_LENGTH: getCell(row, 19),
        UNIT_QTY: getCell(row, 20),
        REMARK: getCell(row, 21),
      };

      // wace getCellValue 의 emptyColCnt(8 미만)로 행 채택. 단순화: 모든 컬럼 빈값이면 빈 행.
      const nonEmptyCount =
        (cur.PART_NO ? 1 : 0) + (cur.PART_NAME ? 1 : 0) + (cur.MATERIAL ? 1 : 0) +
        (cur.HEAT_TREATMENT_HARDNESS ? 1 : 0) + (cur.HEAT_TREATMENT_METHOD ? 1 : 0) +
        (cur.SURFACE_TREATMENT ? 1 : 0) + (cur.MAKER ? 1 : 0) +
        (getCell(row, 7) ? 1 : 0) + (cur.SPEC ? 1 : 0) +
        (getCell(row, 9) ? 1 : 0) + (getCell(row, 10) ? 1 : 0) +
        (getCell(row, 11) ? 1 : 0) + (getCell(row, 12) ? 1 : 0) + (cur.UNITCHNG_NB ? 1 : 0) +
        (getCell(row, 14) ? 1 : 0) + (getCell(row, 15) ? 1 : 0) +
        (getCell(row, 16) ? 1 : 0) + (getCell(row, 17) ? 1 : 0) +
        (getCell(row, 18) ? 1 : 0) + (cur.UNIT_LENGTH ? 1 : 0) +
        (cur.UNIT_QTY ? 1 : 0) + (cur.REMARK ? 1 : 0);

      if (nonEmptyCount === 0) {
        emptyRowCnt++;
        if (emptyRowCnt > 3) break; // wace 동일
        continue;
      }

      // 품번 필수 + 중복 검사 (DB + 엑셀 내)
      if (!cur.PART_NO) {
        appendNote(cur, "필수입력 - 품번");
      } else {
        const dbRes = await client.query(
          `SELECT 1 FROM PART_MNG WHERE PART_NO = $1 LIMIT 1`,
          [cur.PART_NO]
        );
        if ((dbRes.rowCount ?? 0) > 0) {
          appendNote(cur, "품번중복");
        }
        if (partNoSeenInFile.has(cur.PART_NO)) {
          appendNote(cur, `엑셀 내 품번중복(row ${partNoSeenInFile.get(cur.PART_NO)})`);
        } else {
          partNoSeenInFile.set(cur.PART_NO, i + 1);
        }
      }

      if (!cur.PART_NAME) appendNote(cur, "필수입력 - 품명");

      // PART_TYPE (범주 이름) — 코드명 → code_id
      const partTypeIn = getCell(row, 7);
      if (partTypeIn) {
        const hit = partTypeMap.get(partTypeIn.toUpperCase());
        if (hit) {
          cur.PART_TYPE = hit.code_id;
          cur.PART_TYPE_NAME = hit.code_name;
        } else {
          cur.PART_TYPE = partTypeIn;
          appendNote(cur, `범주 이름 확인:${partTypeIn}`);
        }
      }

      // ACCTFG (계정구분)
      const acctfgIn = getCell(row, 9);
      if (acctfgIn) {
        if (/^\d+$/.test(acctfgIn)) {
          cur.ACCTFG = acctfgIn;
        } else {
          const hit = acctfgMap.get(acctfgIn.toUpperCase());
          if (hit) { cur.ACCTFG = hit.code_id; cur.ACCTFG_NAME = hit.code_name; }
          else cur.ACCTFG = acctfgIn;
        }
      }

      // ODRFG (조달구분)
      const odrfgIn = getCell(row, 10);
      cur.ODRFG = mapOdrfg(odrfgIn);
      if (odrfgIn && cur.ODRFG === odrfgIn && !/^[018]$/.test(odrfgIn)) {
        appendNote(cur, `조달구분 확인:${odrfgIn}`);
      }
      cur.ODRFG_NAME = cur.ODRFG === "0" ? "구매"
                    : cur.ODRFG === "1" ? "생산"
                    : cur.ODRFG === "8" ? "Phantom" : odrfgIn;

      // UNIT_DC (재고단위)
      const unitDcIn = getCell(row, 11);
      if (unitDcIn) {
        if (/^\d+$/.test(unitDcIn)) cur.UNIT_DC = unitDcIn;
        else {
          const hit = unitDcMap.get(unitDcIn.toUpperCase());
          if (hit) { cur.UNIT_DC = hit.code_id; cur.UNIT_DC_NAME = hit.code_name; }
          else cur.UNIT_DC = unitDcIn;
        }
      }

      // UNITMANG_DC (관리단위) — 동일 매핑
      const unitmangIn = getCell(row, 12);
      if (unitmangIn) {
        if (/^\d+$/.test(unitmangIn)) cur.UNITMANG_DC = unitmangIn;
        else {
          const hit = unitDcMap.get(unitmangIn.toUpperCase());
          if (hit) { cur.UNITMANG_DC = hit.code_id; cur.UNITMANG_DC_NAME = hit.code_name; }
          else cur.UNITMANG_DC = unitmangIn;
        }
      }

      cur.LOT_FG     = mapKr01(getCell(row, 14), "사용", "미사용", "0");
      cur.USE_YN     = mapKr01(getCell(row, 15), "사용", "미사용", "1");
      cur.QC_FG      = mapKr01(getCell(row, 16), "검사", "무검사", "0");
      cur.SETITEM_FG = mapKr01(getCell(row, 17), "여", "부", "0");
      cur.REQ_FG     = mapKr01(getCell(row, 18), "여", "부", "0");

      result.push(cur);
      emptyRowCnt = 0;
    }
  } finally {
    client.release();
  }

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

// ─── 2) 저장 (mergePartMng 1:1, 신규만 INSERT) ──────────────

export interface SavePartExcelInput {
  PART_NO: string;
  PART_NAME: string;
  MATERIAL?: string;
  HEAT_TREATMENT_HARDNESS?: string;
  HEAT_TREATMENT_METHOD?: string;
  SURFACE_TREATMENT?: string;
  MAKER?: string;
  PART_TYPE?: string;
  SPEC?: string;
  ACCTFG?: string;
  ODRFG?: string;
  UNIT_DC?: string;
  UNITMANG_DC?: string;
  UNITCHNG_NB?: string | number;
  LOT_FG?: string;
  USE_YN?: string;
  QC_FG?: string;
  SETITEM_FG?: string;
  REQ_FG?: string;
  UNIT_LENGTH?: string;
  UNIT_QTY?: string;
  REMARK?: string;
}

export async function saveExcelRows(
  userId: string,
  rows: SavePartExcelInput[]
): Promise<{ inserted: number; skipped: number; skippedPartNos: string[] }> {
  if (!rows || rows.length === 0) return { inserted: 0, skipped: 0, skippedPartNos: [] };

  let inserted = 0;
  let skipped = 0;
  const skippedPartNos: string[] = [];

  await transaction(async (client: PoolClient) => {
    for (const r of rows) {
      const partNo = (r.PART_NO ?? "").trim();
      if (!partNo) { skipped++; continue; }

      // wace partMng.getPartObjid — IS_LAST='1' 인 동일 part_no 가 있으면 INSERT 스킵
      const existRes = await client.query(
        `SELECT OBJID FROM PART_MNG WHERE PART_NO = $1 AND IS_LAST = '1' LIMIT 1`,
        [partNo]
      );
      if ((existRes.rowCount ?? 0) > 0) {
        skipped++;
        skippedPartNos.push(partNo);
        continue;
      }

      const objid = createObjId();
      // wace mergePartMng 1:1 (엑셀 임포트가 채우는 컬럼만)
      await client.query(
        `INSERT INTO PART_MNG (
            OBJID, PART_NO, PART_NAME, SPEC, MATERIAL, PART_TYPE, REMARK,
            STATUS, REG_DATE, WRITER, IS_LAST,
            MAKER,
            HEAT_TREATMENT_HARDNESS, HEAT_TREATMENT_METHOD, SURFACE_TREATMENT,
            ACCTFG, ODRFG, UNIT_DC, UNITMANG_DC, UNITCHNG_NB,
            LOT_FG, USE_YN, QC_FG, SETITEM_FG, REQ_FG,
            UNIT_LENGTH, UNIT_QTY
         ) VALUES (
            $1::numeric, $2, $3, $4, $5, $6, $7,
            'create', NOW(), $8, '1',
            $9,
            $10, $11, $12,
            $13, $14, $15, $16,
            CASE WHEN $17 = '' OR $17 IS NULL THEN NULL ELSE $17::numeric END,
            COALESCE($18, '0'), COALESCE($19, '1'), COALESCE($20, '0'),
            COALESCE($21, '0'), COALESCE($22, '0'),
            $23, $24
         )`,
        [
          objid, partNo, r.PART_NAME ?? "", r.SPEC ?? null, r.MATERIAL ?? null,
          r.PART_TYPE ?? null, r.REMARK ?? null,
          userId,
          r.MAKER ?? null,
          r.HEAT_TREATMENT_HARDNESS ?? null, r.HEAT_TREATMENT_METHOD ?? null, r.SURFACE_TREATMENT ?? null,
          r.ACCTFG ?? null, r.ODRFG ?? null, r.UNIT_DC ?? null, r.UNITMANG_DC ?? null,
          r.UNITCHNG_NB === undefined || r.UNITCHNG_NB === null ? "" : String(r.UNITCHNG_NB),
          r.LOT_FG ?? null, r.USE_YN ?? null, r.QC_FG ?? null, r.SETITEM_FG ?? null, r.REQ_FG ?? null,
          r.UNIT_LENGTH ?? null, r.UNIT_QTY ?? null,
        ]
      );
      inserted++;
    }
  });

  logger.info("PART 엑셀 임포트 저장 완료", { userId, inserted, skipped });
  return { inserted, skipped, skippedPartNos };
}
