// ============================================================
// 발주서관리 — 등록/수정 폼 (general 양식) 서비스
//
// wace_plm 1:1 이식 베이스:
//   - controller: purchaseOrder/purchaseOrderFormPopup_general.do
//                 purchaseOrder/purchaseOrderFormPopup_generalSave.do
//   - service:    PurchaseOrderService.savePurchaseOrder_new (1472-1817)
//   - mapper:     purchaseOrder.xml mergePurchaseOrderMaster (530-714) +
//                 mergePurchaseOrderPartInfo (1205-1325) +
//                 getPurchaseOrderMasterInfo (1343-1556) +
//                 getPURCHASE_ORDER_PART
//                 salesMng.xml getProposalPartList (5012-5125) +
//                 getProposalInfo (4919-)
//
// 운영 핵심 흐름: 품의서(/purchase/proposal)에서 "발주서생성" → general 다이얼로그 →
//                 품의서 품목 자동 채움 → 마스터 입력 → 저장.
//
// 본 모듈은 form-init / form-get 두 GET 엔드포인트를 제공.
// (save / delete 는 다음 단계)
// ============================================================

import { getPool } from "../database/db";
import { logger } from "../utils/logger";
import { createObjId } from "../utils/objidUtil";

export interface OrderFormInitResult {
  master: Record<string, any>;
  parts: Record<string, any>[];
}

export interface SaveOrderFormPayload {
  master: Record<string, any>;
  parts: Record<string, any>[];
  deletedPartObjids?: string[];
}

export interface SaveOrderFormResult {
  objid: string;
  purchase_order_no: string;
}

/**
 * GET /api/purchase/order-form/init?proposal_objid=...
 *
 * 품의서(sales_request_master)에서 발주서 등록 폼을 채울 데이터를 반환.
 * wace controller `purchaseOrderFormPopup_general.do` 의 신규 등록 분기와 1:1.
 *
 * master 기본값:
 *   - PROPOSAL_OBJID / SALES_REQUEST_OBJID = proposal_objid
 *   - CONTRACT_MGMT_OBJID = proposal.PROJECT_NO
 *   - PURCHASE_ORDER_NO = "RPS{YY}-{MMDD}-{NN}" (NN: 당일 발주 카운트+1)
 *   - PURCHASE_DATE / ORDER_DATE = 오늘
 *   - STATUS = "create" / FORM_TYPE = "general"
 * parts: salesMng.getProposalPartList SQL 1:1 → 발주서 그리드 형식 변환
 *        (ORDER_QTY=QTY, PARTNER_PRICE=UNIT_PRICE, SUPPLY_UNIT_PRICE=QTY*UNIT_PRICE 등)
 */
export async function getPurchaseOrderFormInit(proposalObjid: string): Promise<OrderFormInitResult> {
  const pool = getPool();

  // 1) 품의서 마스터 정보 (PROJECT_NO 등)
  let proposal: Record<string, any> | null = null;
  if (proposalObjid) {
    try {
      const r = await pool.query(
        `SELECT OBJID, PROJECT_NO, MBOM_HEADER_OBJID, TITLE, REQUEST_USER_ID, PURCHASE_TYPE
           FROM SALES_REQUEST_MASTER WHERE OBJID = $1`,
        [proposalObjid],
      );
      proposal = r.rows[0] ?? null;
    } catch (e: any) {
      logger.warn("getProposalInfo 실패", { error: e.message });
    }
  }

  // 2) 발주번호 채번 (wace mergePurchaseOrderMaster INSERT 절 1:1)
  let purchaseOrderNo = "";
  try {
    const r = await pool.query(
      `SELECT 'RPS' || TO_CHAR(NOW(),'YY') || '-' || TO_CHAR(NOW(),'MMDD') || '-' ||
              LPAD((COALESCE(MAX(CASE WHEN PURCHASE_ORDER_NO LIKE 'RPS' || TO_CHAR(NOW(),'YY-MMDD') || '-%'
                                       THEN SPLIT_PART(PURCHASE_ORDER_NO, '-', 3) ELSE '0' END)::INTEGER, 0) + 1)::TEXT, 2, '0')
              AS po_no
         FROM PURCHASE_ORDER_MASTER`,
    );
    purchaseOrderNo = r.rows[0]?.po_no ?? "";
  } catch (e: any) {
    logger.warn("발주번호 채번 실패", { error: e.message });
  }

  // USER_INFO 에서 기본 담당자 user_id lookup (select 자동 매칭용)
  // 운영판 wace 는 매니저 select 옵션에 user_id 가 바인딩됨
  let mgr1Id = "";
  let mgr2Id = "";
  try {
    const r = await pool.query(
      `SELECT USER_ID, USER_NAME
         FROM USER_INFO
        WHERE USER_NAME IN ('안동윤', '서동민')
           OR EMAIL IN ('ady1225@rps-korea.com', 'sdm0927@rps-korea.com')`,
    );
    for (const u of r.rows) {
      if (u.user_name === "안동윤" && !mgr1Id) mgr1Id = String(u.user_id);
      if (u.user_name === "서동민" && !mgr2Id) mgr2Id = String(u.user_id);
    }
  } catch (e: any) {
    logger.warn("기본 담당자 user_id lookup 실패", { error: e.message });
  }

  const todayIso = new Date().toISOString().slice(0, 10);
  const master: Record<string, any> = {
    objid: "",                                  // 신규 — 클라이언트가 채워 보내거나 save 시 채번
    purchase_order_no: purchaseOrderNo,
    purchase_date: todayIso,                   // 발주일
    order_date_kor: formatKorDate(new Date()), // 발주일자 한글 표기 (wace ORDER_DATE)
    status: "create",
    form_type: "general",
    sales_request_objid: proposalObjid || "",
    proposal_objid: proposalObjid || "",
    contract_mgmt_objid: proposal?.project_no ?? "",
    title: proposal?.title ?? "",
    // wace controller _general 기본 담당자 (RPS 운영 고정값)
    sales_mng_user_id:  mgr1Id,
    manager_name:     "안동윤",
    manager_position: "팀장",
    manager_phone:    "010-2313-2702",
    manager_email:    "ady1225@rps-korea.com",
    sales_mng_user_id2: mgr2Id,
    manager_name2:     "서동민",
    manager_position2: "주임",
    manager_phone2:    "010-9538-9513",
    manager_email2:    "sdm0927@rps-korea.com",
  };

  // 3) 품의서 품목 → 발주서 파트 변환 (salesMng.getProposalPartList 1:1)
  const parts: Record<string, any>[] = [];
  if (proposalObjid) {
    try {
      logger.info("발주서 init parts 조회", { proposalObjid });
      const r = await pool.query(
        `SELECT
           ROW_NUMBER() OVER(ORDER BY SRP.REGDATE)             AS rnum,
           SRP.OBJID                                           AS srp_objid,
           SRP.PART_OBJID                                      AS part_objid,
           PM.PART_NO                                          AS part_no,
           PM.PART_NAME                                        AS part_name,
           PM.SPEC                                             AS spec,
           PM.MATERIAL                                         AS material,
           COALESCE(NULLIF(SRP.UNIT, ''), PM.UNIT_DC)          AS unit,
           COALESCE(
             (SELECT CODE_NAME FROM COMM_CODE CC WHERE CC.CODE_ID = NULLIF(SRP.UNIT, '')),
             (SELECT CODE_NAME FROM COMM_CODE CC WHERE CC.CODE_ID = PM.UNIT_DC)
           )                                                   AS unit_title,
           SRP.QTY                                             AS qty,
           COALESCE(SRP.UNIT_PRICE, 0)                         AS unit_price,
           CASE WHEN COALESCE(SRP.TOTAL_PRICE::NUMERIC, 0) > 0
                THEN SRP.TOTAL_PRICE::NUMERIC
                ELSE COALESCE(SRP.QTY::NUMERIC, 0) * COALESCE(SRP.UNIT_PRICE::NUMERIC, 0)
           END                                                 AS total_price,
           SRP.VENDOR_PM                                       AS vendor_pm,
           (SELECT CLIENT_NM FROM CLIENT_MNG
             WHERE OBJID::VARCHAR = SRP.VENDOR_PM)             AS vendor_name,
           SRP.REMARK                                          AS remark,
           SRP.DELIVERY_REQUEST_DATE                           AS delivery_request_date,
           COALESCE(SRP.MATERIAL_YN, 'N')                      AS material_yn,
           (SELECT PJ.PART_NAME FROM PROJECT_MGMT PJ
             WHERE PJ.OBJID::VARCHAR = SRM.PROJECT_NO)         AS project_product_name,
           PM.PART_NAME                                        AS component_part_name,
           SRP.CURRENCY                                        AS currency,
           (SELECT CC.CODE_NAME FROM COMM_CODE CC
             WHERE CC.CODE_ID = NULLIF(SRP.CURRENCY, ''))      AS currency_name
         FROM SALES_REQUEST_PART SRP
         LEFT JOIN PART_MNG PM
                ON SRP.PART_OBJID::VARCHAR = PM.OBJID::VARCHAR
         LEFT JOIN SALES_REQUEST_MASTER SRM
                ON SRP.SALES_REQUEST_MASTER_OBJID = SRM.OBJID
         WHERE SRP.SALES_REQUEST_MASTER_OBJID = $1
         ORDER BY SRP.REGDATE`,
        [proposalObjid],
      );
      logger.info("발주서 init parts 결과", { proposalObjid, rowCount: r.rows.length });

      for (const row of r.rows) {
        const qty = toNum(row.qty);
        const unitPrice = toNum(row.unit_price);
        const projectName = (row.project_product_name ?? "").toString().trim();
        const componentName = (row.component_part_name ?? "").toString().trim();
        const remark = projectName && componentName ? `${projectName} / ${componentName}`
                     : projectName || componentName || (row.remark ?? "");

        parts.push({
          // 발주서 그리드 형식 (wace controller _general 변환 1:1)
          objid: "",                                  // 신규 row
          part_objid: row.part_objid ?? "",
          row_num: row.rnum,
          part_no: row.part_no ?? "",
          part_name: row.part_name ?? "",
          spec: row.spec ?? "",
          material: row.material ?? "",
          order_qty: qty,
          unit: row.unit || "0001400",                // wace 기본값 EA
          unit_title: row.unit_title ?? "",
          part_delivery_place: "RPS",
          partner_price: unitPrice,
          supply_unit_price: qty * unitPrice,
          remark,
          delivery_request_date: row.delivery_request_date ?? "",
          currency: row.currency ?? "",
          currency_name: row.currency_name ?? "",
          // 추적용 (저장 시 신규 row 임을 구분)
          _src: "proposal",
          _src_objid: row.srp_objid,
        });
      }
    } catch (e: any) {
      logger.error("getProposalPartList 실패", { error: e.message });
    }
  }

  return { master, parts };
}

/**
 * GET /api/purchase/order-form/:objid
 *
 * 발주서 마스터 + 파트 조회 (수정/조회 모드).
 * wace `getPurchaseOrderMasterInfo` (1343-1556) + `getPURCHASE_ORDER_PART` 의 RPS 압축판.
 */
export async function getPurchaseOrderForm(objid: string): Promise<OrderFormInitResult | null> {
  const pool = getPool();
  try {
    const m = await pool.query(
      `SELECT POM.*,
              (SELECT CLIENT_NM FROM CLIENT_MNG
                WHERE OBJID::VARCHAR = POM.PARTNER_OBJID)         AS partner_name,
              (SELECT USER_NAME FROM USER_INFO
                WHERE USER_ID = POM.SALES_MNG_USER_ID)            AS sales_mng_user_name,
              (SELECT USER_NAME FROM USER_INFO
                WHERE USER_ID = POM.WRITER)                       AS writer_name,
              CM.PROJECT_NO                                       AS project_no,
              SRM.REQUEST_MNG_NO                                  AS proposal_no
         FROM PURCHASE_ORDER_MASTER POM
         LEFT JOIN PROJECT_MGMT CM ON POM.CONTRACT_MGMT_OBJID = CM.OBJID
         LEFT JOIN SALES_REQUEST_MASTER SRM ON POM.SALES_REQUEST_OBJID = SRM.OBJID
        WHERE POM.OBJID = $1`,
      [objid],
    );
    if (m.rows.length === 0) return null;

    const p = await pool.query(
      `SELECT POP.*,
              (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = POP.UNIT)     AS unit_title,
              (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = POP.CURRENCY) AS currency_name
         FROM PURCHASE_ORDER_PART POP
        WHERE POP.PURCHASE_ORDER_MASTER_OBJID = $1
        ORDER BY POP.REGDATE`,
      [objid],
    );

    return { master: m.rows[0], parts: p.rows };
  } catch (e: any) {
    logger.error("getPurchaseOrderForm 실패", { error: e.message });
    return null;
  }
}

function toNum(v: any): number {
  if (v == null || v === "") return 0;
  const s = String(v).replace(/,/g, "");
  const n = Number(s);
  return Number.isFinite(n) ? n : 0;
}

function formatKorDate(d: Date): string {
  return `${d.getFullYear()}년 ${String(d.getMonth() + 1).padStart(2, "0")}월 ${String(d.getDate()).padStart(2, "0")}일`;
}

/** "1,234,567.50" / 1234567.5 / null → '1234567.5' (numeric 컬럼은 모두 varchar 저장) */
function strNum(v: any): string {
  if (v == null) return "";
  const s = String(v).replace(/,/g, "").trim();
  return s;
}

/** pick from various-cased keys (UPPER/lower/snake). 클라이언트가 어느 케이스로 보내든 수용. */
function pick(o: Record<string, any>, ...keys: string[]): any {
  for (const k of keys) {
    if (o == null) continue;
    if (k in o && o[k] !== undefined && o[k] !== null) return o[k];
    const lk = k.toLowerCase();
    if (lk in o && o[lk] !== undefined && o[lk] !== null) return o[lk];
    const uk = k.toUpperCase();
    if (uk in o && o[uk] !== undefined && o[uk] !== null) return o[uk];
  }
  return "";
}

/**
 * POST /api/purchase/order-form/save
 *
 * wace `PurchaseOrderService.savePurchaseOrder_new` (1472-1817) 의 단일-마스터 분기 1:1.
 *   1) 마스터 UPSERT (mergePurchaseOrderMaster — INSERT ... ON CONFLICT(OBJID) DO UPDATE)
 *   2) 파트 UPSERT (mergePurchaseOrderPartInfo — INSERT ... ON CONFLICT(OBJID) DO UPDATE)
 *   3) deletedPartObjids 일괄 DELETE
 * 트랜잭션. 동시발주(MULTI_*)는 RPS 미사용이라 제외.
 *
 * payload.master.objid 가 비어 있으면 신규 채번(createObjId).
 * payload.parts[i].objid 가 비어 있으면 신규 채번.
 */
export async function savePurchaseOrderForm(
  payload: SaveOrderFormPayload,
  writerId: string,
): Promise<SaveOrderFormResult> {
  const pool = getPool();
  const client = await pool.connect();
  try {
    await client.query("BEGIN");

    const m = payload.master ?? {};
    let masterObjid = String(pick(m, "objid") || "").trim();
    const isNew = !masterObjid;
    if (isNew) masterObjid = createObjId();

    // 마스터 UPSERT — wace mergePurchaseOrderMaster (purchaseOrder.xml 530-714) 1:1
    // PURCHASE_ORDER_NO: 신규일 때만 채번 서브쿼리, 수정은 기존 값 유지.
    const writer = writerId || String(pick(m, "writer") || "");
    const params: any[] = [
      /*  1 */ masterObjid,
      /*  2 */ String(pick(m, "po_client_id") || ""),
      /*  3 */ String(pick(m, "category_cd") || ""),
      /*  4 */ String(pick(m, "product_group") || ""),
      /*  5 */ String(pick(m, "product") || ""),
      /*  6 */ String(pick(m, "product_code") || ""),
      /*  7 */ String(pick(m, "my_company_objid") || ""),
      /*  8 */ String(pick(m, "partner_objid") || ""),
      /*  9 */ String(pick(m, "delivery_date") || ""),
      /* 10 */ String(pick(m, "delivery_place") || ""),
      /* 11 */ String(pick(m, "effective_date") || ""),
      /* 12 */ String(pick(m, "payment_terms") || ""),
      /* 13 */ String(pick(m, "remark") || ""),
      /* 14 */ String(pick(m, "request_content") || ""),
      /* 15 */ writer,
      /* 16 */ String(pick(m, "status") || "create"),
      /* 17 */ String(pick(m, "sales_request_objid") || ""),
      /* 18 */ String(pick(m, "sales_mng_user_id") || ""),
      /* 19 */ String(pick(m, "sales_mng_user_id2") || ""),
      /* 20 */ String(pick(m, "form_type") || "general"),
      /* 21 */ String(pick(m, "title") || ""),
      /* 22 */ String(pick(m, "purchase_date") || ""),
      /* 23 */ String(pick(m, "contract_mgmt_objid") || ""),
      /* 24 */ String(pick(m, "type") || ""),
      /* 25 */ String(pick(m, "inspect_method") || ""),
      /* 26 */ String(pick(m, "total_price_txt") || ""),
      /* 27 */ String(pick(m, "total_price_txt_all") || ""),
      /* 28 */ String(pick(m, "vat_method") || ""),
      /* 29 */ strNum(pick(m, "total_supply_unit_price")),
      /* 30 */ strNum(pick(m, "total_supply_price")),
      /* 31 */ strNum(pick(m, "total_real_supply_price")),
      /* 32 */ strNum(pick(m, "discount_price")),
      /* 33 */ strNum(pick(m, "total_price")),
      /* 34 */ strNum(pick(m, "total_price_all")),
      /* 35 */ strNum(pick(m, "nego_rate")),
      /* 36 */ String(pick(m, "supply_bus_no") || ""),
      /* 37 */ String(pick(m, "supply_user_name") || ""),
      /* 38 */ String(pick(m, "supply_user_hp") || ""),
      /* 39 */ String(pick(m, "supply_user_tel") || ""),
      /* 40 */ String(pick(m, "supply_user_fax") || ""),
      /* 41 */ String(pick(m, "supply_user_email") || ""),
      /* 42 */ String(pick(m, "supply_addr") || ""),
      /* 43 */ String(pick(m, "unit_code") || ""),
      /* 44 */ String(pick(m, "bom_report_objid") || ""),
      /* 45 */ String(pick(m, "order_type_cd") || ""),
      /* 46 */ String(pick(m, "multi_yn") || "N"),
      /* 47 */ String(pick(m, "multi_master_yn") || "N"),
      /* 48 */ String(pick(m, "multi_master_objid") || ""),
      /* 49 */ String(pick(m, "delivery_plan_date") || ""),
      /* 50 */ String(pick(m, "delivery_plan_qty") || ""),
      /* 51 */ String(pick(m, "purchase_order_no_org") || ""),
      /* 52 */ String(pick(m, "shipment") || ""),
      /* 53 */ String(pick(m, "packing") || ""),
      /* 54 */ String(pick(m, "validity") || ""),
      /* 55 */ String(pick(m, "attn_to") || ""),
    ];

    const upsertMasterSql = `
      INSERT INTO PURCHASE_ORDER_MASTER (
        OBJID, PO_CLIENT_ID, PURCHASE_ORDER_NO, CATEGORY_CD, PRODUCT_GROUP, PRODUCT, PRODUCT_CODE,
        MY_COMPANY_OBJID, PARTNER_OBJID, DELIVERY_DATE, DELIVERY_PLACE, EFFECTIVE_DATE, PAYMENT_TERMS,
        REMARK, REQUEST_CONTENT, WRITER, REGDATE, STATUS, SALES_REQUEST_OBJID, SALES_MNG_USER_ID,
        SALES_MNG_USER_ID2, FORM_TYPE, TITLE, PURCHASE_DATE, CONTRACT_MGMT_OBJID, TYPE, INSPECT_METHOD,
        TOTAL_PRICE_TXT, TOTAL_PRICE_TXT_ALL, VAT_METHOD, TOTAL_SUPPLY_UNIT_PRICE, TOTAL_SUPPLY_PRICE,
        TOTAL_REAL_SUPPLY_PRICE, DISCOUNT_PRICE, TOTAL_PRICE, TOTAL_PRICE_ALL, NEGO_RATE,
        SUPPLY_BUS_NO, SUPPLY_USER_NAME, SUPPLY_USER_HP, SUPPLY_USER_TEL, SUPPLY_USER_FAX,
        SUPPLY_USER_EMAIL, SUPPLY_ADDR, UNIT_CODE, BOM_REPORT_OBJID, ORDER_TYPE_CD, MULTI_YN,
        MULTI_MASTER_YN, MULTI_MASTER_OBJID, DELIVERY_PLAN_DATE, DELIVERY_PLAN_QTY,
        PURCHASE_ORDER_NO_ORG, SHIPMENT, PACKING, VALIDITY, ATTN_TO
      ) VALUES (
        $1, $2,
        (SELECT 'RPS'||TO_CHAR(NOW(),'YY')||'-'||TO_CHAR(NOW(),'MMDD')||'-'||
                LPAD((COALESCE(MAX(CASE WHEN PURCHASE_ORDER_NO LIKE 'RPS'||TO_CHAR(NOW(),'YY-MMDD')||'-%'
                                         THEN SPLIT_PART(PURCHASE_ORDER_NO,'-',3) ELSE '0' END)::INTEGER,0)+1)::TEXT, 2, '0')
           FROM PURCHASE_ORDER_MASTER),
        $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15,
        NOW(), $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28,
        $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45,
        $46, $47, $48, $49, $50, $51, $52, $53, $54, $55
      ) ON CONFLICT (OBJID) DO UPDATE SET
        PO_CLIENT_ID      = EXCLUDED.PO_CLIENT_ID,
        CATEGORY_CD       = EXCLUDED.CATEGORY_CD,
        PRODUCT_GROUP     = EXCLUDED.PRODUCT_GROUP,
        PRODUCT           = EXCLUDED.PRODUCT,
        PRODUCT_CODE      = EXCLUDED.PRODUCT_CODE,
        MY_COMPANY_OBJID  = EXCLUDED.MY_COMPANY_OBJID,
        PARTNER_OBJID     = EXCLUDED.PARTNER_OBJID,
        DELIVERY_DATE     = EXCLUDED.DELIVERY_DATE,
        DELIVERY_PLACE    = EXCLUDED.DELIVERY_PLACE,
        EFFECTIVE_DATE    = EXCLUDED.EFFECTIVE_DATE,
        PAYMENT_TERMS     = EXCLUDED.PAYMENT_TERMS,
        REMARK            = EXCLUDED.REMARK,
        REQUEST_CONTENT   = EXCLUDED.REQUEST_CONTENT,
        WRITER            = EXCLUDED.WRITER,
        STATUS            = EXCLUDED.STATUS,
        SALES_MNG_USER_ID = EXCLUDED.SALES_MNG_USER_ID,
        SALES_MNG_USER_ID2 = EXCLUDED.SALES_MNG_USER_ID2,
        FORM_TYPE         = CASE WHEN EXCLUDED.FORM_TYPE = '' THEN PURCHASE_ORDER_MASTER.FORM_TYPE ELSE EXCLUDED.FORM_TYPE END,
        TITLE             = EXCLUDED.TITLE,
        PURCHASE_DATE     = EXCLUDED.PURCHASE_DATE,
        CONTRACT_MGMT_OBJID = EXCLUDED.CONTRACT_MGMT_OBJID,
        TYPE              = EXCLUDED.TYPE,
        INSPECT_METHOD    = EXCLUDED.INSPECT_METHOD,
        TOTAL_PRICE_TXT   = EXCLUDED.TOTAL_PRICE_TXT,
        TOTAL_PRICE_TXT_ALL = EXCLUDED.TOTAL_PRICE_TXT_ALL,
        VAT_METHOD        = EXCLUDED.VAT_METHOD,
        TOTAL_SUPPLY_UNIT_PRICE = EXCLUDED.TOTAL_SUPPLY_UNIT_PRICE,
        TOTAL_SUPPLY_PRICE      = EXCLUDED.TOTAL_SUPPLY_PRICE,
        TOTAL_REAL_SUPPLY_PRICE = EXCLUDED.TOTAL_REAL_SUPPLY_PRICE,
        DISCOUNT_PRICE    = EXCLUDED.DISCOUNT_PRICE,
        TOTAL_PRICE       = EXCLUDED.TOTAL_PRICE,
        TOTAL_PRICE_ALL   = EXCLUDED.TOTAL_PRICE_ALL,
        NEGO_RATE         = EXCLUDED.NEGO_RATE,
        SUPPLY_BUS_NO     = EXCLUDED.SUPPLY_BUS_NO,
        SUPPLY_USER_NAME  = EXCLUDED.SUPPLY_USER_NAME,
        SUPPLY_USER_HP    = EXCLUDED.SUPPLY_USER_HP,
        SUPPLY_USER_TEL   = EXCLUDED.SUPPLY_USER_TEL,
        SUPPLY_USER_FAX   = EXCLUDED.SUPPLY_USER_FAX,
        SUPPLY_USER_EMAIL = EXCLUDED.SUPPLY_USER_EMAIL,
        SUPPLY_ADDR       = EXCLUDED.SUPPLY_ADDR,
        UNIT_CODE         = EXCLUDED.UNIT_CODE,
        BOM_REPORT_OBJID  = EXCLUDED.BOM_REPORT_OBJID,
        ORDER_TYPE_CD     = EXCLUDED.ORDER_TYPE_CD,
        DELIVERY_PLAN_DATE = EXCLUDED.DELIVERY_PLAN_DATE,
        DELIVERY_PLAN_QTY  = EXCLUDED.DELIVERY_PLAN_QTY,
        MULTI_YN          = EXCLUDED.MULTI_YN,
        MULTI_MASTER_YN   = EXCLUDED.MULTI_MASTER_YN,
        PURCHASE_ORDER_NO_ORG = EXCLUDED.PURCHASE_ORDER_NO_ORG,
        SHIPMENT          = EXCLUDED.SHIPMENT,
        PACKING           = EXCLUDED.PACKING,
        VALIDITY          = EXCLUDED.VALIDITY,
        ATTN_TO           = EXCLUDED.ATTN_TO
      RETURNING OBJID, PURCHASE_ORDER_NO`;

    const upRes = await client.query(upsertMasterSql, params);
    const savedNo: string = upRes.rows[0]?.purchase_order_no ?? "";

    // 파트 UPSERT — wace mergePurchaseOrderPartInfo (1205-1325) 1:1
    const parts = payload.parts ?? [];
    for (const raw of parts) {
      const p: Record<string, any> = raw ?? {};
      let popObjid = String(pick(p, "objid") || "").trim();
      if (!popObjid) popObjid = createObjId();

      const popParams: any[] = [
        /*  1 */ popObjid,
        /*  2 */ masterObjid,
        /*  3 */ String(pick(p, "part_objid") || ""),     // bigint 캐스트 in SQL
        /*  4 */ strNum(pick(p, "bom_qty")),
        /*  5 */ strNum(pick(p, "qty", "order_qty")),
        /*  6 */ strNum(pick(p, "order_qty")),
        /*  7 */ strNum(pick(p, "partner_price")),
        /*  8 */ String(pick(p, "remark") || ""),
        /*  9 */ writer,
        /* 10 */ String(pick(p, "status") || "create"),
        /* 11 */ String(pick(p, "part_name") || ""),
        /* 12 */ String(pick(p, "part_no") || ""),
        /* 13 */ String(pick(p, "do_no") || ""),
        /* 14 */ String(pick(p, "thickness") || ""),
        /* 15 */ String(pick(p, "width") || ""),
        /* 16 */ String(pick(p, "height") || ""),
        /* 17 */ String(pick(p, "out_diameter") || ""),
        /* 18 */ String(pick(p, "length") || ""),
        /* 19 */ String(pick(p, "in_diameter") || ""),
        /* 20 */ String(pick(p, "inven_total_qty") || ""),
        /* 21 */ String(pick(p, "ld_part_objid") || ""),
        /* 22 */ String(pick(p, "spec") || ""),
        /* 23 */ String(pick(p, "maker") || ""),
        /* 24 */ String(pick(p, "unit") || ""),
        /* 25 */ strNum(pick(p, "supply_unit_price")),
        /* 26 */ strNum(pick(p, "price1")),
        /* 27 */ strNum(pick(p, "price2")),
        /* 28 */ strNum(pick(p, "price3")),
        /* 29 */ strNum(pick(p, "price4")),
        /* 30 */ strNum(pick(p, "supply_unit_vat_price")),
        /* 31 */ strNum(pick(p, "supply_unit_vat_sum_price")),
        /* 32 */ strNum(pick(p, "total_order_qty")),
        /* 33 */ strNum(pick(p, "stock_qty")),
        /* 34 */ strNum(pick(p, "real_order_qty")),
        /* 35 */ strNum(pick(p, "real_supply_price")),
        /* 36 */ String(pick(p, "part_delivery_place") || ""),
        /* 37 */ String(pick(p, "product_name") || ""),
        /* 38 */ String(pick(p, "work_order_no") || ""),
        /* 39 */ String(pick(p, "delivery_request_date") || ""),
        /* 40 */ String(pick(p, "currency") || ""),
      ];

      // part_objid 가 빈 문자열이면 NULL 로 — bigint 컬럼은 빈 문자열 INSERT 실패.
      const partObjidParam = popParams[2] === "" ? null : popParams[2];
      popParams[2] = partObjidParam;

      await client.query(
        `INSERT INTO PURCHASE_ORDER_PART (
           OBJID, PURCHASE_ORDER_MASTER_OBJID, PART_OBJID, BOM_QTY, QTY, ORDER_QTY,
           PARTNER_PRICE, REMARK, WRITER, REGDATE, STATUS,
           PART_NAME, PART_NO, DO_NO, THICKNESS, WIDTH, HEIGHT, OUT_DIAMETER, LENGTH,
           IN_DIAMETER, INVEN_TOTAL_QTY, LD_PART_OBJID, SPEC, MAKER, UNIT,
           SUPPLY_UNIT_PRICE, PRICE1, PRICE2, PRICE3, PRICE4,
           SUPPLY_UNIT_VAT_PRICE, SUPPLY_UNIT_VAT_SUM_PRICE, TOTAL_ORDER_QTY,
           STOCK_QTY, REAL_ORDER_QTY, REAL_SUPPLY_PRICE,
           PART_DELIVERY_PLACE, PRODUCT_NAME, WORK_ORDER_NO, DELIVERY_REQUEST_DATE, CURRENCY
         ) VALUES (
           $1, $2, NULLIF($3::text,'')::bigint, $4, $5,
           COALESCE(NULLIF($6,'')::numeric::integer, 0),
           $7, $8, $9, NOW(), $10,
           $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24,
           $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35,
           $36, $37, $38, $39, $40
         ) ON CONFLICT (OBJID) DO UPDATE SET
           ORDER_QTY        = COALESCE(NULLIF($6,'')::numeric::integer, 0),
           PARTNER_PRICE    = EXCLUDED.PARTNER_PRICE,
           REMARK           = EXCLUDED.REMARK,
           STATUS           = EXCLUDED.STATUS,
           MODIFIER         = EXCLUDED.WRITER,
           UPDATE_DATE      = NOW(),
           LD_PART_OBJID    = EXCLUDED.LD_PART_OBJID,
           SPEC             = EXCLUDED.SPEC,
           MAKER            = EXCLUDED.MAKER,
           UNIT             = EXCLUDED.UNIT,
           SUPPLY_UNIT_PRICE = EXCLUDED.SUPPLY_UNIT_PRICE,
           PRICE1           = EXCLUDED.PRICE1,
           PRICE2           = EXCLUDED.PRICE2,
           PRICE3           = EXCLUDED.PRICE3,
           PRICE4           = EXCLUDED.PRICE4,
           SUPPLY_UNIT_VAT_PRICE     = EXCLUDED.SUPPLY_UNIT_VAT_PRICE,
           SUPPLY_UNIT_VAT_SUM_PRICE = EXCLUDED.SUPPLY_UNIT_VAT_SUM_PRICE,
           TOTAL_ORDER_QTY  = EXCLUDED.TOTAL_ORDER_QTY,
           STOCK_QTY        = EXCLUDED.STOCK_QTY,
           REAL_ORDER_QTY   = EXCLUDED.REAL_ORDER_QTY,
           REAL_SUPPLY_PRICE = EXCLUDED.REAL_SUPPLY_PRICE,
           PART_DELIVERY_PLACE = EXCLUDED.PART_DELIVERY_PLACE,
           PRODUCT_NAME     = EXCLUDED.PRODUCT_NAME,
           WORK_ORDER_NO    = EXCLUDED.WORK_ORDER_NO,
           DELIVERY_REQUEST_DATE = EXCLUDED.DELIVERY_REQUEST_DATE,
           CURRENCY         = EXCLUDED.CURRENCY`,
        popParams,
      );
    }

    // 삭제된 파트 정리 — 클라이언트가 보낸 deletedPartObjids 일괄 DELETE
    const deletedIds = (payload.deletedPartObjids ?? []).filter((s) => !!s);
    if (deletedIds.length > 0) {
      await client.query(
        `DELETE FROM PURCHASE_ORDER_PART
           WHERE PURCHASE_ORDER_MASTER_OBJID = $1
             AND OBJID = ANY($2::text[])`,
        [masterObjid, deletedIds],
      );
    }

    await client.query("COMMIT");
    return { objid: masterObjid, purchase_order_no: savedNo };
  } catch (e: any) {
    await client.query("ROLLBACK");
    logger.error("savePurchaseOrderForm 실패", { error: e.message, stack: e.stack });
    throw e;
  } finally {
    client.release();
  }
}

/**
 * DELETE /api/purchase/order-form/:objid
 *
 * 마스터 1건 + 파트 cascade 삭제. wace `deletePurchaseOrderMaster` 의 단일판.
 */
export async function deletePurchaseOrderForm(objid: string): Promise<void> {
  const pool = getPool();
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query(`DELETE FROM PURCHASE_ORDER_PART   WHERE PURCHASE_ORDER_MASTER_OBJID = $1`, [objid]);
    await client.query(`DELETE FROM PURCHASE_ORDER_MASTER WHERE OBJID = $1`, [objid]);
    await client.query("COMMIT");
  } catch (e: any) {
    await client.query("ROLLBACK");
    logger.error("deletePurchaseOrderForm 실패", { error: e.message });
    throw e;
  } finally {
    client.release();
  }
}
