// ============================================================
// 구매관리 — 7개 메뉴 그리드/옵션 서비스
//
// wace_plm 1:1 이식 베이스. 마스터 테이블만 RPS 에 존재 (2026-05-14 현재):
//   ✓ sales_request_master / mbom_header / mbom_detail
//   ✓ purchase_order_master (56 cols, 0 rows)
//   ✓ client_mng / supply_mng / admin_supply_mng / part_mng / project_mgmt / contract_mgmt
//
// 누락 (운영DB 추출 후 신설 필요):
//   ✗ sales_request_part / sales_request_detail
//   ✗ quotation_request_master / quotation_received
//   ✗ purchase_order_part
//   ✗ arrival_plan
//   ✗ inventory_mgmt / inventory_mgmt_in
//   ✗ incoming_inspection / incoming_inspection_detail
//
// 정책: 누락 테이블 의존 SELECT 는 빈 그리드 + 콘솔 warning 으로 처리.
//      마스터 단독 데이터는 정상 노출 (구매리스트관리 / 품의서관리 / 발주서관리).
// ============================================================

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

export interface PurchaseListFilter {
  year?: string;
  customer_objid?: string;
  customer_cd?: string;
  project_no?: string;
  part_no?: string;
  part_name?: string;
  part_spec?: string;
  partner_objid?: string;
  purchase_order_no?: string;
  proposal_no?: string;
  search_status?: string;
  writer?: string;
  request_user?: string;
  purchase_type?: string;
  part_type?: string;
  product_cd?: string;
  category_cd?: string;
  paid_type?: string;
  mail_send_yn?: string;
  delivery_status?: string;
  close_status?: string;
  sales_mng_user_id?: string;
  regdate_start?: string;
  regdate_end?: string;
  receipt_date_start?: string;
  receipt_date_end?: string;
  delivery_start_date?: string;
  delivery_end_date?: string;
  reg_start_date?: string;
  reg_end_date?: string;
  page?: number;
  page_size?: number;
}

interface ListResult<T> {
  rows: T[];
  totalCount: number;
  page: number;
  pageSize: number;
}

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

// ─── 1) 구매리스트관리 (wace salesMng.xml salesRequestMngRegList 매퍼 1:1 베이스) ──
//
// sales_request_master + (sales_request_part 누락 → PART_NO/PART_NAME 빈값) +
// project_mgmt + contract_mgmt + comm_code + client_mng (customer 분기).
//
// WHERE: doc_type = 'PURCHASE_REQUEST' (또는 NULL) + 동적 필터.
// ORDER: regdate DESC.

export async function listPurchaseRequest(filter: PurchaseListFilter): Promise<ListResult<any>> {
  const pool = getPool();
  const { limit, offset, page, pageSize } = clampPaging(filter);

  const where: string[] = [`(SRM.DOC_TYPE = 'PURCHASE_REQUEST' OR SRM.DOC_TYPE IS NULL)`];
  const params: any[] = [];
  const addParam = (val: any) => { params.push(val); return `$${params.length}`; };

  if (filter.customer_cd) where.push(`SRM.CUSTOMER_OBJID = ${addParam(filter.customer_cd)}`);
  if (filter.project_no) where.push(`PM.PROJECT_NO ILIKE ${addParam(`%${filter.project_no}%`)}`);
  if (filter.request_user) where.push(`SRM.REQUEST_USER_ID = ${addParam(filter.request_user)}`);
  if (filter.part_type) where.push(`SRM.PRODUCT_NAME = ${addParam(filter.part_type)}`);
  if (filter.regdate_start) where.push(`SRM.REGDATE::DATE >= ${addParam(filter.regdate_start)}::DATE`);
  if (filter.regdate_end) where.push(`SRM.REGDATE::DATE <= ${addParam(filter.regdate_end)}::DATE`);
  if (filter.part_no) where.push(`EXISTS (SELECT 1 FROM MBOM_DETAIL MD JOIN PART_MNG PP ON MD.PART_OBJID::VARCHAR=PP.OBJID::VARCHAR WHERE MD.MBOM_HEADER_OBJID = SRM.MBOM_HEADER_OBJID AND PP.PART_NO ILIKE ${addParam(`%${filter.part_no}%`)})`);
  if (filter.part_name) where.push(`EXISTS (SELECT 1 FROM MBOM_DETAIL MD JOIN PART_MNG PP ON MD.PART_OBJID::VARCHAR=PP.OBJID::VARCHAR WHERE MD.MBOM_HEADER_OBJID = SRM.MBOM_HEADER_OBJID AND PP.PART_NAME ILIKE ${addParam(`%${filter.part_name}%`)})`);

  const whereSql = where.length ? `WHERE ${where.join(" AND ")}` : "";

  const dataSql = `
    SELECT
      SRM.OBJID                                     AS objid,
      SRM.REQUEST_MNG_NO                            AS request_mng_no,
      SRM.DOC_TYPE                                  AS doc_type,
      SRM.STATUS                                    AS status,
      CASE SRM.STATUS
        WHEN 'create'           THEN '작성중'
        WHEN 'approvalRequest'  THEN '결재중'
        WHEN 'approvalComplete' THEN '결재완료'
        WHEN 'reject'           THEN '반려'
        WHEN 'release'          THEN '진행중'
        ELSE COALESCE(SRM.STATUS, '')
      END                                           AS status_title,
      COALESCE(
        (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = SRM.PURCHASE_TYPE LIMIT 1), ''
      )                                             AS purchase_type_name,
      COALESCE(
        (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = SRM.ORDER_TYPE LIMIT 1), ''
      )                                             AS order_type_name,
      COALESCE(
        (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = SRM.PRODUCT_NAME LIMIT 1), ''
      )                                             AS product_name_full,
      CASE WHEN SRM.PAID_TYPE = 'paid' THEN '유상'
           WHEN SRM.PAID_TYPE = 'free' THEN '무상'
           ELSE COALESCE(SRM.PAID_TYPE, '')
      END                                           AS paid_type_name,
      PM.PROJECT_NO                                 AS project_number,
      -- 고객사 (wace 동일 — 프로젝트.contract_mgmt.customer_objid 우선)
      COALESCE(
        (SELECT CM2.CLIENT_NM FROM CLIENT_MNG CM2
          WHERE 'C_' || CM2.OBJID::VARCHAR = CTR.CUSTOMER_OBJID LIMIT 1),
        (SELECT SUPPLY_NAME FROM SUPPLY_MNG SM
          WHERE SM.OBJID::VARCHAR = CTR.CUSTOMER_OBJID LIMIT 1),
        ''
      )                                             AS customer_name,
      -- 품번/품명 (MBOM_DETAIL → PART_MNG, 다중이면 "외 N건")
      COALESCE((SELECT PP.PART_NO FROM MBOM_DETAIL MD
                 JOIN PART_MNG PP ON MD.PART_OBJID::VARCHAR = PP.OBJID::VARCHAR
                WHERE MD.MBOM_HEADER_OBJID = SRM.MBOM_HEADER_OBJID
                ORDER BY MD.REGDATE LIMIT 1), '')   AS part_no,
      COALESCE((SELECT PP.PART_NAME FROM MBOM_DETAIL MD
                 JOIN PART_MNG PP ON MD.PART_OBJID::VARCHAR = PP.OBJID::VARCHAR
                WHERE MD.MBOM_HEADER_OBJID = SRM.MBOM_HEADER_OBJID
                ORDER BY MD.REGDATE LIMIT 1), '')   AS part_name,
      (SELECT COUNT(DISTINCT PP.PART_NO)::int - 1
         FROM MBOM_DETAIL MD
         JOIN PART_MNG PP ON MD.PART_OBJID::VARCHAR = PP.OBJID::VARCHAR
        WHERE MD.MBOM_HEADER_OBJID = SRM.MBOM_HEADER_OBJID) AS part_extra_count,
      -- 견적요청서 존재여부 (quotation_request_master 와 sales_request_master_objid 매칭)
      CASE WHEN EXISTS (
        SELECT 1 FROM QUOTATION_REQUEST_MASTER QRM
         WHERE QRM.SALES_REQUEST_MASTER_OBJID = SRM.OBJID
      ) THEN 'Y' ELSE 'N' END                       AS has_quotation_request,
      SRM.REQUEST_USER_ID                           AS request_user,
      COALESCE(user_name(SRM.REQUEST_USER_ID), SRM.REQUEST_USER_ID, '') AS request_user_name,
      SRM.DELIVERY_REQUEST_DATE                     AS delivery_request_date,
      TO_CHAR(SRM.REGDATE, 'YYYY-MM-DD')            AS regdate_title,
      SRM.MBOM_HEADER_OBJID                         AS mbom_header_objid
    FROM SALES_REQUEST_MASTER SRM
    LEFT JOIN PROJECT_MGMT PM ON PM.OBJID::VARCHAR = SRM.PROJECT_NO
    LEFT JOIN CONTRACT_MGMT CTR ON CTR.OBJID = PM.CONTRACT_OBJID
    ${whereSql}
    ORDER BY SRM.REGDATE DESC
    LIMIT ${addParam(limit)} OFFSET ${addParam(offset)}
  `;
  const countSql = `
    SELECT COUNT(*)::int AS cnt
      FROM SALES_REQUEST_MASTER SRM
      LEFT JOIN PROJECT_MGMT PM ON PM.OBJID::VARCHAR = SRM.PROJECT_NO
      LEFT JOIN CONTRACT_MGMT CTR ON CTR.OBJID = PM.CONTRACT_OBJID
      ${whereSql}
  `;
  try {
    const [d, c] = await Promise.all([
      pool.query(dataSql, params),
      pool.query(countSql, params.slice(0, params.length - 2)),
    ]);
    return { rows: d.rows, totalCount: c.rows[0]?.cnt ?? 0, page, pageSize };
  } catch (e: any) {
    logger.error("listPurchaseRequest 실패", { error: e.message });
    return { rows: [], totalCount: 0, page, pageSize };
  }
}

// ─── 2) 견적요청서관리 (wace salesMng.xml getQuotationRequestList 매퍼 1:1) ──
//
// quotation_request_master + quotation_request_detail + sales_request_master +
// project_mgmt + contract_mgmt + client_mng (vendor) + comm_code + user_info +
// attach_file_info (DOC_TYPE='QUOTATION_RECEIVED').
//
// 매퍼 본문: wace_plm/src/com/pms/mapper/salesMng.xml:5248-5349
// 검색: year / project_no / quotation_request_no / vendor / mail_send_yn / writer / product_cd

export async function listQuotationRequest(filter: PurchaseListFilter): Promise<ListResult<any>> {
  const pool = getPool();
  const { limit, offset, page, pageSize } = clampPaging(filter);

  const where: string[] = [];
  const params: any[] = [];
  const addParam = (val: any) => { params.push(val); return `$${params.length}`; };

  if (filter.year) where.push(`EXTRACT(YEAR FROM QRM.REG_DATE) = ${addParam(Number(filter.year))}`);
  if (filter.project_no) where.push(`PM.PROJECT_NO ILIKE ${addParam(`%${filter.project_no}%`)}`);
  if (filter.proposal_no) where.push(`QRM.QUOTATION_REQUEST_NO ILIKE ${addParam(`%${filter.proposal_no}%`)}`);
  if (filter.partner_objid) where.push(`QRM.VENDOR_OBJID = ${addParam(filter.partner_objid)}`);
  if (filter.mail_send_yn) where.push(`COALESCE(QRM.MAIL_SEND_YN, 'N') = ${addParam(filter.mail_send_yn)}`);
  if (filter.writer) where.push(`QRM.WRITER = ${addParam(filter.writer)}`);
  if (filter.product_cd) where.push(`COALESCE(CTM.PRODUCT, SRM.PRODUCT_NAME) = ${addParam(filter.product_cd)}`);

  const whereSql = where.length ? `WHERE ${where.join(" AND ")}` : "";

  const dataSql = `
    SELECT
      QRM.OBJID                                                     AS objid,
      QRM.QUOTATION_REQUEST_NO                                      AS quotation_request_no,
      QRM.SALES_REQUEST_MASTER_OBJID                                AS sales_request_master_objid,
      QRM.PROJECT_MGMT_OBJID                                        AS project_mgmt_objid,
      QRM.VENDOR_OBJID                                              AS vendor_objid,
      QRM.VENDOR_TYPE                                               AS vendor_type,
      QRM.STATUS                                                    AS status,
      CASE QRM.STATUS
        WHEN 'create'    THEN '작성중'
        WHEN 'sent'      THEN '발송완료'
        WHEN 'received'  THEN '견적수신'
        WHEN 'completed' THEN '완료'
        ELSE COALESCE(QRM.STATUS, '')
      END                                                           AS status_name,
      QRM.MAIL_SEND_DATE                                            AS mail_send_date,
      TO_CHAR(QRM.MAIL_SEND_DATE, 'YYYY-MM-DD')                     AS mail_send_date_title,
      COALESCE(QRM.MAIL_SEND_YN, 'N')                               AS mail_send_yn,
      QRM.DUE_DATE                                                  AS due_date,
      TO_CHAR(QRM.DUE_DATE, 'YYYY-MM-DD')                           AS due_date_title,
      QRM.REMARK                                                    AS remark,
      QRM.WRITER                                                    AS writer,
      COALESCE((SELECT USER_NAME FROM USER_INFO WHERE USER_ID = QRM.WRITER LIMIT 1), QRM.WRITER, '') AS writer_name,
      QRM.REG_DATE                                                  AS reg_date,
      TO_CHAR(QRM.REG_DATE, 'YYYY-MM-DD')                           AS reg_date_title,
      SRM.REQUEST_MNG_NO                                            AS request_mng_no,
      SRM.PURCHASE_TYPE                                             AS purchase_type,
      COALESCE(
        (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = SRM.PURCHASE_TYPE LIMIT 1), ''
      )                                                             AS purchase_type_name,
      COALESCE(PM.CATEGORY_CD, SRM.ORDER_TYPE)                      AS order_type,
      COALESCE(
        (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = COALESCE(PM.CATEGORY_CD, SRM.ORDER_TYPE) LIMIT 1), ''
      )                                                             AS order_type_name,
      COALESCE(
        (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = CTM.PRODUCT LIMIT 1),
        (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = SRM.PRODUCT_NAME LIMIT 1),
        ''
      )                                                             AS product_name_title,
      PM.PROJECT_NO                                                 AS project_number,
      CM.CLIENT_NM                                                  AS vendor_name,
      (SELECT QRD.PART_NO FROM QUOTATION_REQUEST_DETAIL QRD
         WHERE QRD.QUOTATION_REQUEST_MASTER_OBJID = QRM.OBJID
         ORDER BY QRD.OBJID LIMIT 1)                                AS part_no,
      (SELECT QRD.PART_NAME FROM QUOTATION_REQUEST_DETAIL QRD
         WHERE QRD.QUOTATION_REQUEST_MASTER_OBJID = QRM.OBJID
         ORDER BY QRD.OBJID LIMIT 1)                                AS part_name,
      (SELECT COUNT(*)::int FROM QUOTATION_REQUEST_DETAIL QRD
         WHERE QRD.QUOTATION_REQUEST_MASTER_OBJID = QRM.OBJID)      AS detail_count,
      (SELECT COUNT(*)::int FROM ATTACH_FILE_INFO
         WHERE TARGET_OBJID = QRM.OBJID
           AND DOC_TYPE = 'QUOTATION_RECEIVED'
           AND COALESCE(STATUS, 'Active') = 'Active')               AS attach_file_cnt
    FROM QUOTATION_REQUEST_MASTER QRM
    LEFT JOIN SALES_REQUEST_MASTER SRM ON QRM.SALES_REQUEST_MASTER_OBJID = SRM.OBJID
    LEFT JOIN PROJECT_MGMT PM ON PM.OBJID = SRM.PROJECT_NO
    LEFT JOIN CONTRACT_MGMT CTM ON CTM.OBJID = PM.CONTRACT_OBJID
    LEFT JOIN CLIENT_MNG CM ON QRM.VENDOR_OBJID = CM.OBJID
    ${whereSql}
    ORDER BY QRM.REG_DATE DESC
    LIMIT ${addParam(limit)} OFFSET ${addParam(offset)}
  `;
  const countSql = `
    SELECT COUNT(*)::int AS cnt
      FROM QUOTATION_REQUEST_MASTER QRM
      LEFT JOIN SALES_REQUEST_MASTER SRM ON QRM.SALES_REQUEST_MASTER_OBJID = SRM.OBJID
      LEFT JOIN PROJECT_MGMT PM ON PM.OBJID = SRM.PROJECT_NO
      LEFT JOIN CONTRACT_MGMT CTM ON CTM.OBJID = PM.CONTRACT_OBJID
      LEFT JOIN CLIENT_MNG CM ON QRM.VENDOR_OBJID = CM.OBJID
      ${whereSql}
  `;
  try {
    const [d, c] = await Promise.all([
      pool.query(dataSql, params),
      pool.query(countSql, params.slice(0, params.length - 2)),
    ]);
    return { rows: d.rows, totalCount: c.rows[0]?.cnt ?? 0, page, pageSize };
  } catch (e: any) {
    logger.error("listQuotationRequest 실패", { error: e.message });
    return { rows: [], totalCount: 0, page, pageSize };
  }
}

// ─── 3) 품의서관리 (wace salesMng.xml proposalMngList) ──
// sales_request_master.doc_type='PROPOSAL'.
// 결재 우선순위: AMR.STATUS > APPROVAL.APPR_STATUS > SRM.STATUS('create'→'등록중')
export async function listProposal(filter: PurchaseListFilter): Promise<ListResult<any>> {
  const pool = getPool();
  const { limit, offset, page, pageSize } = clampPaging(filter);

  const where: string[] = [
    `SRM.STATUS IN ('create','approvalRequest','approvalComplete','reject')`,
    `(SRM.DOC_TYPE = 'PROPOSAL' OR SRM.DOC_TYPE = 'PURCHASE_REG_PROPOSAL')`,
  ];
  const params: any[] = [];
  const addParam = (val: any) => { params.push(val); return `$${params.length}`; };

  if (filter.proposal_no) where.push(`SRM.REQUEST_MNG_NO ILIKE ${addParam(`%${filter.proposal_no}%`)}`);
  if (filter.project_no) where.push(`EXISTS (SELECT 1 FROM PROJECT_MGMT PMX WHERE PMX.OBJID::VARCHAR = SRM.PROJECT_NO AND PMX.PROJECT_NO ILIKE ${addParam(`%${filter.project_no}%`)})`);
  if (filter.search_status) where.push(`SRM.STATUS = ${addParam(filter.search_status)}`);
  if (filter.regdate_start) where.push(`SRM.REGDATE::DATE >= ${addParam(filter.regdate_start)}::DATE`);
  if (filter.regdate_end) where.push(`SRM.REGDATE::DATE <= ${addParam(filter.regdate_end)}::DATE`);
  if (filter.purchase_type) where.push(`SRM.PURCHASE_TYPE = ${addParam(filter.purchase_type)}`);
  if (filter.writer) where.push(`SRM.WRITER = ${addParam(filter.writer)}`);
  if (filter.part_type) where.push(`SRM.PRODUCT_NAME = ${addParam(filter.part_type)}`);

  const whereSql = where.length ? `WHERE ${where.join(" AND ")}` : "";

  const dataSql = `
    SELECT
      SRM.OBJID                                       AS objid,
      SRM.REQUEST_MNG_NO                              AS proposal_no,
      SRM.STATUS                                      AS status,
      -- AMARANTH_STATUS 컬럼 RPS 미존재 → NULL (wace 1순위 결재상태 우선순위 향후 보완)
      NULL::text                                      AS amaranth_status,
      PM.PROJECT_NO                                   AS project_number,
      COALESCE(
        (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = SRM.PURCHASE_TYPE LIMIT 1), ''
      )                                               AS purchase_type_name,
      COALESCE(
        (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = SRM.ORDER_TYPE LIMIT 1), ''
      )                                               AS order_type_name,
      COALESCE(
        (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = SRM.PRODUCT_NAME LIMIT 1), ''
      )                                               AS product_name_title,
      COALESCE((SELECT PP.PART_NO FROM MBOM_DETAIL MD
                 JOIN PART_MNG PP ON MD.PART_OBJID::VARCHAR = PP.OBJID::VARCHAR
                WHERE MD.MBOM_HEADER_OBJID = SRM.MBOM_HEADER_OBJID
                ORDER BY MD.REGDATE LIMIT 1), '')     AS part_no,
      COALESCE((SELECT PP.PART_NAME FROM MBOM_DETAIL MD
                 JOIN PART_MNG PP ON MD.PART_OBJID::VARCHAR = PP.OBJID::VARCHAR
                WHERE MD.MBOM_HEADER_OBJID = SRM.MBOM_HEADER_OBJID
                ORDER BY MD.REGDATE LIMIT 1), '')     AS part_name,
      (SELECT COUNT(DISTINCT PP.PART_NO)::int - 1
         FROM MBOM_DETAIL MD
         JOIN PART_MNG PP ON MD.PART_OBJID::VARCHAR = PP.OBJID::VARCHAR
        WHERE MD.MBOM_HEADER_OBJID = SRM.MBOM_HEADER_OBJID) AS part_extra_count,
      CASE SRM.STATUS
        WHEN 'create'           THEN '작성중'
        WHEN 'approvalRequest'  THEN '결재중'
        WHEN 'approvalComplete' THEN '결재완료'
        WHEN 'reject'           THEN '반려'
        ELSE COALESCE(SRM.STATUS, '')
      END                                             AS status_title,
      TO_CHAR(SRM.REGDATE, 'YYYY-MM-DD')              AS regdate_title,
      SRM.WRITER                                      AS writer,
      COALESCE(user_name(SRM.WRITER), SRM.WRITER, '') AS writer_name,
      SRM.MBOM_HEADER_OBJID                           AS mbom_header_objid
    FROM SALES_REQUEST_MASTER SRM
    LEFT JOIN PROJECT_MGMT PM ON PM.OBJID::VARCHAR = SRM.PROJECT_NO
    ${whereSql}
    ORDER BY SRM.REGDATE DESC
    LIMIT ${addParam(limit)} OFFSET ${addParam(offset)}
  `;
  const countSql = `
    SELECT COUNT(*)::int AS cnt
      FROM SALES_REQUEST_MASTER SRM
      LEFT JOIN PROJECT_MGMT PM ON PM.OBJID::VARCHAR = SRM.PROJECT_NO
      ${whereSql}
  `;
  try {
    const [d, c] = await Promise.all([
      pool.query(dataSql, params),
      pool.query(countSql, params.slice(0, params.length - 2)),
    ]);
    return { rows: d.rows, totalCount: c.rows[0]?.cnt ?? 0, page, pageSize };
  } catch (e: any) {
    logger.error("listProposal 실패", { error: e.message });
    return { rows: [], totalCount: 0, page, pageSize };
  }
}

// ─── 4) 입고관리 (wace purchaseOrder.xml deliveryMngList_new 매퍼 1:1) ──
//
// 발주서별 1행 (purchase_order_master + S1 집계: PURCHASE_ORDER_PART × ARRIVAL_PLAN).
// 매퍼 본문: wace_plm/src/com/pms/mapper/purchaseOrder.xml:4381-4580
// 검색: year / customer_cd / project_no / purchase_order_no / part_no / part_name / part_spec /
//      partner_objid / sales_mng_user_id / delivery_date 범위 / reg_date 범위 / delivery_status
// 검사현황(IID_AGG/DEFECT_AGG)은 INVENTORY/INSPECTION 테이블 미존재 → 0 처리.

export async function listInbound(filter: PurchaseListFilter): Promise<ListResult<any>> {
  const pool = getPool();
  const { limit, offset, page, pageSize } = clampPaging(filter);

  const where: string[] = [
    `POM.MAIL_SEND_DATE IS NOT NULL`,
    `POM.STATUS = 'create'`,
    `(POM.MULTI_MASTER_YN = 'Y' OR COALESCE(POM.MULTI_MASTER_YN, '') <> 'Y' AND COALESCE(POM.MULTI_YN, '') <> 'Y')`,
  ];
  const params: any[] = [];
  const addParam = (val: any) => { params.push(val); return `$${params.length}`; };

  if (filter.year) where.push(`TO_CHAR(POM.REGDATE, 'YYYY') = ${addParam(String(filter.year))}`);
  if (filter.customer_cd) where.push(`CM.CUSTOMER_OBJID = REPLACE(${addParam(filter.customer_cd)}, 'C_', '')`);
  if (filter.project_no) where.push(`CM.PROJECT_NO ILIKE ${addParam(`%${filter.project_no}%`)}`);
  if (filter.purchase_order_no) where.push(`POM.PURCHASE_ORDER_NO ILIKE ${addParam(`%${filter.purchase_order_no}%`)}`);
  if (filter.partner_objid) where.push(`POM.PARTNER_OBJID = REPLACE(${addParam(filter.partner_objid)}, 'C_', '')`);
  if (filter.sales_mng_user_id) where.push(`POM.WRITER = ${addParam(filter.sales_mng_user_id)}`);
  if (filter.delivery_start_date) where.push(`POM.DELIVERY_DATE >= ${addParam(filter.delivery_start_date)}`);
  if (filter.delivery_end_date) where.push(`POM.DELIVERY_DATE <= ${addParam(filter.delivery_end_date)}`);
  if (filter.reg_start_date) where.push(`TO_CHAR(POM.REGDATE, 'YYYY-MM-DD') >= ${addParam(filter.reg_start_date)}`);
  if (filter.reg_end_date) where.push(`TO_CHAR(POM.REGDATE, 'YYYY-MM-DD') <= ${addParam(filter.reg_end_date)}`);
  if (filter.part_no) where.push(`EXISTS (SELECT 1 FROM PURCHASE_ORDER_PART POPX WHERE POPX.PURCHASE_ORDER_MASTER_OBJID = POM.OBJID AND POPX.PART_NO ILIKE ${addParam(`%${filter.part_no}%`)})`);
  if (filter.part_name) where.push(`EXISTS (SELECT 1 FROM PURCHASE_ORDER_PART POPX WHERE POPX.PURCHASE_ORDER_MASTER_OBJID = POM.OBJID AND POPX.PART_NAME ILIKE ${addParam(`%${filter.part_name}%`)})`);
  if (filter.part_spec) where.push(`EXISTS (SELECT 1 FROM PURCHASE_ORDER_PART POPX WHERE POPX.PURCHASE_ORDER_MASTER_OBJID = POM.OBJID AND POPX.SPEC ILIKE ${addParam(`%${filter.part_spec}%`)})`);

  const whereSql = `WHERE ${where.join(" AND ")}`;
  const havingSql =
    filter.delivery_status
      ? `HAVING (CASE WHEN COALESCE(S1.TOTAL_PO_QTY,0) - COALESCE(S1.TOTAL_DELIVERY_QTY,0) <= 0 THEN '입고완료'
                       WHEN TO_CHAR(NOW(),'YYYY-MM-DD') > POM.DELIVERY_DATE THEN '지연'
                       ELSE '입고중' END) = ${addParam(filter.delivery_status)}`
      : "";

  const fromSql = `
    FROM PURCHASE_ORDER_MASTER POM
    LEFT JOIN PROJECT_MGMT CM ON CM.OBJID = POM.CONTRACT_MGMT_OBJID
    LEFT JOIN (
      SELECT POP.PURCHASE_ORDER_MASTER_OBJID,
             SUM(COALESCE(POP.ORDER_QTY::NUMERIC, 0))                                                AS TOTAL_PO_QTY,
             MAX(AP_AGG.MAX_RECEIPT_DATE)                                                            AS CUR_DELIVERY_DATE,
             SUM(COALESCE(AP_AGG.SUM_RECEIPT_QTY, 0))                                                AS TOTAL_DELIVERY_QTY,
             SUM(COALESCE(POP.PARTNER_PRICE::NUMERIC, 0) * COALESCE(POP.ORDER_QTY::NUMERIC, 0))      AS TOTAL_SUPPLY_PRICE,
             SUM(COALESCE(POP.PARTNER_PRICE::NUMERIC, 0) * COALESCE(AP_AGG.SUM_RECEIPT_QTY, 0))      AS TOTAL_DELIVERY_PRICE,
             SUM(COALESCE(POP.PARTNER_PRICE::NUMERIC, 0) *
                 (COALESCE(POP.ORDER_QTY::NUMERIC, 0) - COALESCE(AP_AGG.SUM_RECEIPT_QTY, 0)))        AS TOTAL_NOT_DELIVERY_PRICE
        FROM PURCHASE_ORDER_PART POP
        LEFT JOIN (
          SELECT PARENT_OBJID, PART_OBJID,
                 SUM(COALESCE(RECEIPT_QTY::NUMERIC, 0)) AS SUM_RECEIPT_QTY,
                 MAX(RECEIPT_DATE)                     AS MAX_RECEIPT_DATE
            FROM ARRIVAL_PLAN
           GROUP BY PARENT_OBJID, PART_OBJID
        ) AP_AGG ON AP_AGG.PARENT_OBJID = POP.PURCHASE_ORDER_MASTER_OBJID
                 AND AP_AGG.PART_OBJID = POP.PART_OBJID
       GROUP BY POP.PURCHASE_ORDER_MASTER_OBJID
    ) S1 ON POM.OBJID = S1.PURCHASE_ORDER_MASTER_OBJID
    ${whereSql}
  `;

  const groupBySql = havingSql ? `GROUP BY POM.OBJID, S1.TOTAL_PO_QTY, S1.TOTAL_DELIVERY_QTY, POM.DELIVERY_DATE` : "";

  const dataSql = `
    SELECT
      POM.OBJID                                                                                     AS objid,
      POM.PURCHASE_ORDER_NO                                                                         AS purchase_order_no,
      POM.STATUS                                                                                    AS status,
      (SELECT REQUEST_MNG_NO FROM SALES_REQUEST_MASTER SRM WHERE SRM.OBJID = POM.SALES_REQUEST_OBJID LIMIT 1) AS proposal_no,
      CM.PROJECT_NO                                                                                 AS project_no,
      -- 첫 품번/품명 + "외 N건"
      (SELECT CASE WHEN COUNT(*) > 1 THEN MIN(PART_NO) || ' 외 ' || (COUNT(*) - 1) || '건' ELSE MIN(PART_NO) END
         FROM PURCHASE_ORDER_PART POPN WHERE POPN.PURCHASE_ORDER_MASTER_OBJID = POM.OBJID)          AS part_no,
      (SELECT CASE WHEN COUNT(*) > 1 THEN MIN(PART_NAME) || ' 외 ' || (COUNT(*) - 1) || '건' ELSE MIN(PART_NAME) END
         FROM PURCHASE_ORDER_PART POPN WHERE POPN.PURCHASE_ORDER_MASTER_OBJID = POM.OBJID)          AS part_name,
      POM.PARTNER_OBJID                                                                             AS partner_objid,
      (SELECT CLIENT_NM FROM CLIENT_MNG WHERE OBJID = POM.PARTNER_OBJID LIMIT 1)                    AS partner_name,
      (SELECT CC.CODE_NAME FROM COMM_CODE CC
        WHERE CC.CODE_ID = (SELECT POP2.CURRENCY FROM PURCHASE_ORDER_PART POP2
                             WHERE POP2.PURCHASE_ORDER_MASTER_OBJID = POM.OBJID
                               AND POP2.CURRENCY IS NOT NULL AND POP2.CURRENCY <> '' LIMIT 1)
        LIMIT 1)                                                                                    AS currency_name,
      POM.WRITER                                                                                    AS writer,
      COALESCE((SELECT USER_NAME FROM USER_INFO WHERE USER_ID = POM.WRITER LIMIT 1), POM.WRITER, '') AS writer_name,
      (SELECT COALESCE((SELECT USER_NAME FROM USER_INFO WHERE USER_ID = AP.WRITER), AP.WRITER, '')
         FROM ARRIVAL_PLAN AP
        WHERE AP.PARENT_OBJID = POM.OBJID
          AND AP.RECEIPT_QTY IS NOT NULL AND AP.RECEIPT_QTY::NUMERIC > 0
        ORDER BY AP.RECEIPT_DATE DESC LIMIT 1)                                                      AS delivery_writer_name,
      (SELECT AP.RECEIPT_DATE FROM ARRIVAL_PLAN AP
        WHERE AP.PARENT_OBJID = POM.OBJID
          AND AP.RECEIPT_QTY IS NOT NULL AND AP.RECEIPT_QTY::NUMERIC > 0
        ORDER BY AP.RECEIPT_DATE DESC LIMIT 1)                                                      AS delivery_regdate,
      COALESCE(S1.TOTAL_PO_QTY, 0)                                                                  AS total_po_qty,
      COALESCE(S1.TOTAL_DELIVERY_QTY, 0)                                                            AS total_delivery_qty,
      COALESCE(S1.TOTAL_PO_QTY, 0) - COALESCE(S1.TOTAL_DELIVERY_QTY, 0)                             AS non_delivery_qty,
      COALESCE(S1.TOTAL_SUPPLY_PRICE, 0)                                                            AS total_supply_price,
      COALESCE(S1.TOTAL_DELIVERY_PRICE, 0)                                                          AS total_delivery_price,
      COALESCE(S1.TOTAL_NOT_DELIVERY_PRICE, 0)                                                      AS total_not_delivery_price,
      (SELECT COUNT(1)::int FROM ATTACH_FILE_INFO AF
        WHERE AF.TARGET_OBJID = POM.OBJID
          AND AF.DOC_TYPE = 'INSPECTION_FILE'
          AND UPPER(COALESCE(AF.STATUS, 'Active')) = 'ACTIVE')                                      AS inspection_file_cnt,
      CASE WHEN COALESCE(S1.TOTAL_PO_QTY, 0) - COALESCE(S1.TOTAL_DELIVERY_QTY, 0) <= 0 THEN '입고완료'
           WHEN TO_CHAR(NOW(),'YYYY-MM-DD') > POM.DELIVERY_DATE THEN '지연'
           ELSE '입고중'
      END                                                                                          AS delivery_status,
      POM.PURCHASE_CLOSE_DATE                                                                       AS purchase_close_date
    ${fromSql}
    ${groupBySql}
    ${havingSql}
    ORDER BY POM.REGDATE DESC
    LIMIT ${addParam(limit)} OFFSET ${addParam(offset)}
  `;
  const countSql = `SELECT COUNT(*)::int AS cnt ${fromSql} ${groupBySql} ${havingSql}`;
  try {
    const [d, c] = await Promise.all([
      pool.query(dataSql, params),
      pool.query(countSql, params.slice(0, params.length - 2)),
    ]);
    return { rows: d.rows, totalCount: c.rows[0]?.cnt ?? 0, page, pageSize };
  } catch (e: any) {
    logger.error("listInbound 실패", { error: e.message });
    return { rows: [], totalCount: 0, page, pageSize };
  }
}

// ─── 5) 품목별 입고관리 (wace deliveryMngPartList 매퍼 1:1) ──
// 매퍼 본문: wace_plm/src/com/pms/mapper/purchaseOrder.xml:6309-6543
// PURCHASE_ORDER_PART 행별 1행 + AP_AGG (입고집계) + IID_AGG/DEFECT_AGG (검사 — RPS 미존재로 0).

export async function listInboundByItem(filter: PurchaseListFilter): Promise<ListResult<any>> {
  const pool = getPool();
  const { limit, offset, page, pageSize } = clampPaging(filter);

  const where: string[] = [
    `POM.MAIL_SEND_DATE IS NOT NULL`,
    `POM.STATUS = 'create'`,
    `(POM.MULTI_MASTER_YN = 'Y' OR COALESCE(POM.MULTI_MASTER_YN, '') <> 'Y' AND COALESCE(POM.MULTI_YN, '') <> 'Y')`,
  ];
  const params: any[] = [];
  const addParam = (val: any) => { params.push(val); return `$${params.length}`; };

  if (filter.year) where.push(`TO_CHAR(POM.REGDATE, 'YYYY') = ${addParam(String(filter.year))}`);
  if (filter.customer_cd) where.push(`CM.CUSTOMER_OBJID = REPLACE(${addParam(filter.customer_cd)}, 'C_', '')`);
  if (filter.project_no) where.push(`CM.PROJECT_NO ILIKE ${addParam(`%${filter.project_no}%`)}`);
  if (filter.purchase_order_no) where.push(`POM.PURCHASE_ORDER_NO ILIKE ${addParam(`%${filter.purchase_order_no}%`)}`);
  if (filter.partner_objid) where.push(`POM.PARTNER_OBJID = REPLACE(${addParam(filter.partner_objid)}, 'C_', '')`);
  if (filter.sales_mng_user_id) where.push(`POM.WRITER = ${addParam(filter.sales_mng_user_id)}`);
  if (filter.delivery_start_date) where.push(`POP.DELIVERY_REQUEST_DATE >= ${addParam(filter.delivery_start_date)}`);
  if (filter.delivery_end_date) where.push(`POP.DELIVERY_REQUEST_DATE <= ${addParam(filter.delivery_end_date)}`);
  if (filter.reg_start_date) where.push(`TO_CHAR(POM.REGDATE, 'YYYY-MM-DD') >= ${addParam(filter.reg_start_date)}`);
  if (filter.reg_end_date) where.push(`TO_CHAR(POM.REGDATE, 'YYYY-MM-DD') <= ${addParam(filter.reg_end_date)}`);
  if (filter.part_no) where.push(`POP.PART_NO ILIKE ${addParam(`%${filter.part_no}%`)}`);
  if (filter.part_name) where.push(`POP.PART_NAME ILIKE ${addParam(`%${filter.part_name}%`)}`);
  if (filter.part_spec) where.push(`POP.SPEC ILIKE ${addParam(`%${filter.part_spec}%`)}`);
  if (filter.delivery_status) {
    where.push(`(CASE WHEN COALESCE(POP.ORDER_QTY::NUMERIC, 0) - COALESCE(AP_AGG.DELIVERY_QTY, 0) <= 0 THEN '입고완료'
                      WHEN TO_CHAR(NOW(),'YYYY-MM-DD') > POM.DELIVERY_DATE THEN '지연'
                      ELSE '입고중' END) = ${addParam(filter.delivery_status)}`);
  }

  const whereSql = `WHERE ${where.join(" AND ")}`;
  const fromSql = `
    FROM PURCHASE_ORDER_PART POP
    JOIN PURCHASE_ORDER_MASTER POM ON POM.OBJID = POP.PURCHASE_ORDER_MASTER_OBJID
    LEFT JOIN PROJECT_MGMT CM ON CM.OBJID = POM.CONTRACT_MGMT_OBJID
    LEFT JOIN (
      SELECT PARENT_OBJID, PART_OBJID, SUM(COALESCE(RECEIPT_QTY::NUMERIC, 0)) AS DELIVERY_QTY
        FROM ARRIVAL_PLAN
       GROUP BY PARENT_OBJID, PART_OBJID
    ) AP_AGG ON AP_AGG.PARENT_OBJID = POM.OBJID AND AP_AGG.PART_OBJID = POP.PART_OBJID
    ${whereSql}
  `;

  const dataSql = `
    SELECT
      POP.OBJID                                                                                     AS objid,
      POP.OBJID                                                                                     AS purchase_order_part_objid,
      POM.OBJID                                                                                     AS purchase_order_master_objid,
      POM.STATUS                                                                                    AS status,
      (SELECT REQUEST_MNG_NO FROM SALES_REQUEST_MASTER SRM WHERE SRM.OBJID = POM.SALES_REQUEST_OBJID LIMIT 1) AS proposal_no,
      POM.PURCHASE_ORDER_NO                                                                         AS purchase_order_no,
      CM.PROJECT_NO                                                                                 AS project_no,
      -- 부품품번 (sales_request_part 미존재 → POP.PART_NO fallback)
      POP.PART_NO                                                                                   AS component_part_no,
      POP.PART_NO                                                                                   AS part_no,
      POP.PART_NAME                                                                                 AS part_name,
      POM.PARTNER_OBJID                                                                             AS partner_objid,
      (SELECT CLIENT_NM FROM CLIENT_MNG WHERE OBJID = POM.PARTNER_OBJID LIMIT 1)                    AS partner_name,
      (SELECT CC.CODE_NAME FROM COMM_CODE CC WHERE CC.CODE_ID = POP.CURRENCY LIMIT 1)               AS currency_name,
      POP.DELIVERY_REQUEST_DATE                                                                     AS delivery_request_date,
      COALESCE((SELECT USER_NAME FROM USER_INFO WHERE USER_ID = POM.WRITER LIMIT 1), POM.WRITER, '') AS writer_name,
      (SELECT COALESCE((SELECT USER_NAME FROM USER_INFO WHERE USER_ID = AP.WRITER), AP.WRITER, '')
         FROM ARRIVAL_PLAN AP
        WHERE AP.PARENT_OBJID = POM.OBJID
          AND AP.PART_OBJID = POP.PART_OBJID
          AND AP.RECEIPT_QTY IS NOT NULL AND AP.RECEIPT_QTY::NUMERIC > 0
        ORDER BY AP.RECEIPT_DATE DESC LIMIT 1)                                                      AS delivery_writer_name,
      (SELECT AP.RECEIPT_DATE FROM ARRIVAL_PLAN AP
        WHERE AP.PARENT_OBJID = POM.OBJID
          AND AP.PART_OBJID = POP.PART_OBJID
          AND AP.RECEIPT_QTY IS NOT NULL AND AP.RECEIPT_QTY::NUMERIC > 0
        ORDER BY AP.RECEIPT_DATE DESC LIMIT 1)                                                      AS delivery_regdate,
      COALESCE(POP.ORDER_QTY::NUMERIC, 0)                                                           AS order_qty,
      COALESCE(AP_AGG.DELIVERY_QTY, 0)                                                              AS delivery_qty,
      COALESCE(POP.ORDER_QTY::NUMERIC, 0) - COALESCE(AP_AGG.DELIVERY_QTY, 0)                        AS non_delivery_qty,
      COALESCE(POP.PARTNER_PRICE::NUMERIC, 0) * COALESCE(POP.ORDER_QTY::NUMERIC, 0)                 AS total_supply_price,
      COALESCE(POP.PARTNER_PRICE::NUMERIC, 0) * COALESCE(AP_AGG.DELIVERY_QTY, 0)                    AS total_delivery_price,
      COALESCE(POP.PARTNER_PRICE::NUMERIC, 0) *
        (COALESCE(POP.ORDER_QTY::NUMERIC, 0) - COALESCE(AP_AGG.DELIVERY_QTY, 0))                    AS total_not_delivery_price,
      -- 검사현황/폐기/확정수량 (incoming_inspection_* 미존재 → 0)
      ''                                                                                            AS inspection_status,
      0                                                                                             AS defect_qty,
      COALESCE(AP_AGG.DELIVERY_QTY, 0)                                                              AS confirmed_qty,
      CASE WHEN COALESCE(POP.ORDER_QTY::NUMERIC, 0) - COALESCE(AP_AGG.DELIVERY_QTY, 0) <= 0 THEN '입고완료'
           WHEN TO_CHAR(NOW(),'YYYY-MM-DD') > POM.DELIVERY_DATE THEN '지연'
           ELSE '입고중'
      END                                                                                          AS delivery_status
    ${fromSql}
    ORDER BY POM.REGDATE DESC, POP.OBJID
    LIMIT ${addParam(limit)} OFFSET ${addParam(offset)}
  `;
  const countSql = `SELECT COUNT(*)::int AS cnt ${fromSql}`;
  try {
    const [d, c] = await Promise.all([
      pool.query(dataSql, params),
      pool.query(countSql, params.slice(0, params.length - 2)),
    ]);
    return { rows: d.rows, totalCount: c.rows[0]?.cnt ?? 0, page, pageSize };
  } catch (e: any) {
    logger.error("listInboundByItem 실패", { error: e.message });
    return { rows: [], totalCount: 0, page, pageSize };
  }
}

// ─── 6) 입고일별 입고관리 (wace purchaseCloseList 매퍼 1:1) ──
// 매퍼 본문: wace_plm/src/com/pms/mapper/purchaseOrder.xml:6549-6765
// ARRIVAL_PLAN 행별 (RECEIPT_QTY > 0) + 매입마감/관세/세금계산서 컬럼.

export async function listInboundByDate(filter: PurchaseListFilter): Promise<ListResult<any>> {
  const pool = getPool();
  const { limit, offset, page, pageSize } = clampPaging(filter);

  const where: string[] = [
    `POM.MAIL_SEND_DATE IS NOT NULL`,
    `POM.STATUS = 'create'`,
    `COALESCE(AP.RECEIPT_QTY, '0')::NUMERIC > 0`,
  ];
  const params: any[] = [];
  const addParam = (val: any) => { params.push(val); return `$${params.length}`; };

  if (filter.year) where.push(`TO_CHAR(POM.REGDATE, 'YYYY') = ${addParam(String(filter.year))}`);
  if (filter.customer_cd) where.push(`CM.CUSTOMER_OBJID = REPLACE(${addParam(filter.customer_cd)}, 'C_', '')`);
  if (filter.project_no) where.push(`CM.PROJECT_NO ILIKE ${addParam(`%${filter.project_no}%`)}`);
  if (filter.purchase_order_no) where.push(`POM.PURCHASE_ORDER_NO ILIKE ${addParam(`%${filter.purchase_order_no}%`)}`);
  if (filter.partner_objid) where.push(`POM.PARTNER_OBJID = REPLACE(${addParam(filter.partner_objid)}, 'C_', '')`);
  if (filter.sales_mng_user_id) where.push(`POM.WRITER = ${addParam(filter.sales_mng_user_id)}`);
  if (filter.part_no) where.push(`POP.PART_NO ILIKE ${addParam(`%${filter.part_no}%`)}`);
  if (filter.part_name) where.push(`POP.PART_NAME ILIKE ${addParam(`%${filter.part_name}%`)}`);
  if (filter.part_spec) where.push(`POP.SPEC ILIKE ${addParam(`%${filter.part_spec}%`)}`);
  if (filter.receipt_date_start) where.push(`AP.RECEIPT_DATE >= ${addParam(filter.receipt_date_start)}`);
  if (filter.receipt_date_end) where.push(`AP.RECEIPT_DATE <= ${addParam(filter.receipt_date_end)}`);
  if (filter.close_status === "Y") where.push(`AP.PURCHASE_CLOSE_DATE IS NOT NULL AND AP.PURCHASE_CLOSE_DATE <> ''`);
  if (filter.close_status === "N") where.push(`(AP.PURCHASE_CLOSE_DATE IS NULL OR AP.PURCHASE_CLOSE_DATE = '')`);

  const whereSql = `WHERE ${where.join(" AND ")}`;
  const fromSql = `
    FROM ARRIVAL_PLAN AP
    JOIN PURCHASE_ORDER_MASTER POM ON POM.OBJID = AP.PARENT_OBJID
    LEFT JOIN PURCHASE_ORDER_PART POP
      ON POP.PURCHASE_ORDER_MASTER_OBJID = AP.PARENT_OBJID
     AND POP.PART_OBJID = AP.PART_OBJID
    LEFT JOIN PROJECT_MGMT CM ON CM.OBJID = POM.CONTRACT_MGMT_OBJID
    ${whereSql}
  `;
  const dataSql = `
    SELECT
      AP.OBJID                                                                                      AS objid,
      AP.OBJID                                                                                      AS arrival_plan_objid,
      POP.OBJID                                                                                     AS purchase_order_part_objid,
      POM.OBJID                                                                                     AS purchase_order_master_objid,
      (SELECT REQUEST_MNG_NO FROM SALES_REQUEST_MASTER SRM WHERE SRM.OBJID = POM.SALES_REQUEST_OBJID LIMIT 1) AS proposal_no,
      POM.PURCHASE_ORDER_NO                                                                         AS purchase_order_no,
      CM.PROJECT_NO                                                                                 AS project_no,
      POP.PART_NO                                                                                   AS component_part_no,
      POP.PART_NO                                                                                   AS part_no,
      POP.PART_NAME                                                                                 AS part_name,
      (SELECT CLIENT_NM FROM CLIENT_MNG WHERE OBJID = POM.PARTNER_OBJID LIMIT 1)                    AS partner_name,
      (SELECT CC.CODE_NAME FROM COMM_CODE CC WHERE CC.CODE_ID = POP.CURRENCY LIMIT 1)               AS currency_name,
      AP.RECEIPT_DATE                                                                               AS receipt_date,
      COALESCE((SELECT USER_NAME FROM USER_INFO WHERE USER_ID = POM.WRITER LIMIT 1), POM.WRITER, '') AS writer_name,
      COALESCE((SELECT USER_NAME FROM USER_INFO WHERE USER_ID = AP.WRITER LIMIT 1), AP.WRITER, '')  AS delivery_writer_name,
      COALESCE(AP.RECEIPT_QTY::NUMERIC, 0)                                                          AS receipt_qty,
      COALESCE(POP.PARTNER_PRICE::NUMERIC, 0) * COALESCE(AP.RECEIPT_QTY::NUMERIC, 0)                AS total_delivery_price,
      ''                                                                                            AS inspection_status,
      0                                                                                             AS defect_qty,
      COALESCE(AP.RECEIPT_QTY::NUMERIC, 0)                                                          AS confirmed_qty,
      AP.SUB_LOCATION                                                                               AS sub_location_name,
      (SELECT CC.CODE_NAME FROM COMM_CODE CC WHERE CC.CODE_ID = AP.FOREIGN_TYPE LIMIT 1)            AS foreign_type_name,
      AP.EXCHANGE_RATE                                                                              AS exchange_rate,
      (SELECT CC.CODE_NAME FROM COMM_CODE CC WHERE CC.CODE_ID = AP.TAX_TYPE LIMIT 1)                AS tax_type_name,
      AP.TAX_INVOICE_DATE                                                                           AS tax_invoice_date,
      AP.EXPORT_DECL_NO                                                                             AS export_decl_no,
      AP.LOADING_DATE                                                                               AS loading_date,
      AP.DUTY                                                                                       AS duty,
      AP.IMPORT_VAT                                                                                 AS import_vat,
      AP.PURCHASE_CLOSE_DATE                                                                        AS purchase_close_date
    ${fromSql}
    ORDER BY AP.RECEIPT_DATE DESC NULLS LAST, AP.OBJID
    LIMIT ${addParam(limit)} OFFSET ${addParam(offset)}
  `;
  const countSql = `SELECT COUNT(*)::int AS cnt ${fromSql}`;
  try {
    const [d, c] = await Promise.all([
      pool.query(dataSql, params),
      pool.query(countSql, params.slice(0, params.length - 2)),
    ]);
    return { rows: d.rows, totalCount: c.rows[0]?.cnt ?? 0, page, pageSize };
  } catch (e: any) {
    logger.error("listInboundByDate 실패", { error: e.message });
    return { rows: [], totalCount: 0, page, pageSize };
  }
}

// ─── 7) 프로젝트별 발주/입고 현황 (wace projectPurchaseDeliveryStatus) ──
// contract_mgmt + mbom_header/detail (전체수량/품목수) + purchase_order_master/part (발주현황) +
// arrival_plan (입고현황). 발주/입고는 누락 테이블 의존 → 0 표시.
export async function listProjectStatus(filter: PurchaseListFilter): Promise<ListResult<any>> {
  const pool = getPool();
  const { limit, offset, page, pageSize } = clampPaging(filter);

  // wace 운영판 WHERE CTR.MAIL_SEND_DATE IS NOT NULL — RPS contract_mgmt 미존재 컬럼이라 생략 (전체 노출)
  const where: string[] = [];
  const params: any[] = [];
  const addParam = (val: any) => { params.push(val); return `$${params.length}`; };

  if (filter.customer_objid) where.push(`CTR.CUSTOMER_OBJID = ${addParam(filter.customer_objid)}`);
  if (filter.project_no) where.push(`PM.PROJECT_NO ILIKE ${addParam(`%${filter.project_no}%`)}`);
  if (filter.product_cd) where.push(`CTR.PRODUCT = ${addParam(filter.product_cd)}`);
  if (filter.part_no) where.push(`PM.PART_NO ILIKE ${addParam(`%${filter.part_no}%`)}`);
  if (filter.part_name) where.push(`PM.PART_NAME ILIKE ${addParam(`%${filter.part_name}%`)}`);
  if (filter.year) where.push(`EXTRACT(YEAR FROM PM.REGDATE) = ${addParam(Number(filter.year))}`);

  const whereSql = where.length ? `WHERE ${where.join(" AND ")}` : "";

  const dataSql = `
    SELECT
      PM.OBJID                                              AS objid,
      PM.PROJECT_NO                                         AS project_no,
      COALESCE(
        (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = CTR.PRODUCT LIMIT 1), ''
      )                                                     AS product_name,
      PM.PART_NO                                            AS part_no,
      PM.PART_NAME                                          AS part_name,
      COALESCE(
        (SELECT CLIENT_NM FROM CLIENT_MNG CL
          WHERE 'C_' || CL.OBJID::VARCHAR = CTR.CUSTOMER_OBJID LIMIT 1),
        (SELECT SUPPLY_NAME FROM SUPPLY_MNG SM
          WHERE SM.OBJID::VARCHAR = CTR.CUSTOMER_OBJID LIMIT 1),
        ''
      )                                                     AS customer_name,
      -- BOM 기준 (mbom_detail)
      COALESCE((SELECT COUNT(DISTINCT MD.PART_OBJID)::int
                  FROM MBOM_DETAIL MD
                  JOIN MBOM_HEADER MH2 ON MD.MBOM_HEADER_OBJID = MH2.OBJID
                 WHERE MH2.PROJECT_OBJID = PM.OBJID::VARCHAR
                   AND MH2.STATUS = 'Y'), 0)                AS total_item_cnt,
      COALESCE((SELECT SUM(MD.QTY)
                  FROM MBOM_DETAIL MD
                  JOIN MBOM_HEADER MH2 ON MD.MBOM_HEADER_OBJID = MH2.OBJID
                 WHERE MH2.PROJECT_OBJID = PM.OBJID::VARCHAR
                   AND MH2.STATUS = 'Y'), 0)                AS total_qty,
      -- 발주 통계: PURCHASE_ORDER_PART × POM(STATUS='create' AND MAIL_SEND_DATE IS NOT NULL)
      COALESCE((SELECT COUNT(DISTINCT POP.PART_OBJID)::int
                  FROM PURCHASE_ORDER_PART POP
                  JOIN PURCHASE_ORDER_MASTER POM2 ON POM2.OBJID = POP.PURCHASE_ORDER_MASTER_OBJID
                 WHERE POM2.CONTRACT_MGMT_OBJID = PM.OBJID
                   AND POM2.MAIL_SEND_DATE IS NOT NULL
                   AND POM2.STATUS = 'create'), 0)          AS po_item_cnt,
      COALESCE((SELECT SUM(COALESCE(POP.ORDER_QTY::numeric, 0))
                  FROM PURCHASE_ORDER_PART POP
                  JOIN PURCHASE_ORDER_MASTER POM2 ON POM2.OBJID = POP.PURCHASE_ORDER_MASTER_OBJID
                 WHERE POM2.CONTRACT_MGMT_OBJID = PM.OBJID
                   AND POM2.MAIL_SEND_DATE IS NOT NULL
                   AND POM2.STATUS = 'create'), 0)          AS po_qty,
      -- 미발주 = BOM 품목수 - 발주 품목수 (음수 방지 GREATEST)
      GREATEST(
        COALESCE((SELECT COUNT(DISTINCT MD.PART_OBJID)::int
                    FROM MBOM_DETAIL MD
                    JOIN MBOM_HEADER MH2 ON MD.MBOM_HEADER_OBJID = MH2.OBJID
                   WHERE MH2.PROJECT_OBJID = PM.OBJID::VARCHAR
                     AND MH2.STATUS = 'Y'), 0)
        - COALESCE((SELECT COUNT(DISTINCT POP.PART_OBJID)::int
                      FROM PURCHASE_ORDER_PART POP
                      JOIN PURCHASE_ORDER_MASTER POM2 ON POM2.OBJID = POP.PURCHASE_ORDER_MASTER_OBJID
                     WHERE POM2.CONTRACT_MGMT_OBJID = PM.OBJID
                       AND POM2.MAIL_SEND_DATE IS NOT NULL
                       AND POM2.STATUS = 'create'), 0)
      , 0)                                                  AS non_po_item_cnt,
      GREATEST(
        COALESCE((SELECT SUM(MD.QTY)
                    FROM MBOM_DETAIL MD
                    JOIN MBOM_HEADER MH2 ON MD.MBOM_HEADER_OBJID = MH2.OBJID
                   WHERE MH2.PROJECT_OBJID = PM.OBJID::VARCHAR
                     AND MH2.STATUS = 'Y'), 0)
        - COALESCE((SELECT SUM(COALESCE(POP.ORDER_QTY::numeric, 0))
                      FROM PURCHASE_ORDER_PART POP
                      JOIN PURCHASE_ORDER_MASTER POM2 ON POM2.OBJID = POP.PURCHASE_ORDER_MASTER_OBJID
                     WHERE POM2.CONTRACT_MGMT_OBJID = PM.OBJID
                       AND POM2.MAIL_SEND_DATE IS NOT NULL
                       AND POM2.STATUS = 'create'), 0)
      , 0)                                                  AS non_po_qty,
      -- 입고 통계: ARRIVAL_PLAN × POM (RECEIPT_QTY > 0)
      COALESCE((SELECT COUNT(DISTINCT AP.PART_OBJID)::int
                  FROM ARRIVAL_PLAN AP
                  JOIN PURCHASE_ORDER_MASTER POM3 ON POM3.OBJID = AP.PARENT_OBJID
                 WHERE POM3.CONTRACT_MGMT_OBJID = PM.OBJID
                   AND POM3.MAIL_SEND_DATE IS NOT NULL
                   AND POM3.STATUS = 'create'
                   AND COALESCE(AP.RECEIPT_QTY::numeric, 0) > 0), 0) AS dlv_item_cnt,
      COALESCE((SELECT SUM(COALESCE(AP.RECEIPT_QTY::numeric, 0))
                  FROM ARRIVAL_PLAN AP
                  JOIN PURCHASE_ORDER_MASTER POM3 ON POM3.OBJID = AP.PARENT_OBJID
                 WHERE POM3.CONTRACT_MGMT_OBJID = PM.OBJID
                   AND POM3.MAIL_SEND_DATE IS NOT NULL
                   AND POM3.STATUS = 'create'), 0)          AS dlv_qty,
      -- 미입고 = 발주 - 입고 (음수 방지)
      GREATEST(
        COALESCE((SELECT COUNT(DISTINCT POP.PART_OBJID)::int
                    FROM PURCHASE_ORDER_PART POP
                    JOIN PURCHASE_ORDER_MASTER POM2 ON POM2.OBJID = POP.PURCHASE_ORDER_MASTER_OBJID
                   WHERE POM2.CONTRACT_MGMT_OBJID = PM.OBJID
                     AND POM2.MAIL_SEND_DATE IS NOT NULL
                     AND POM2.STATUS = 'create'), 0)
        - COALESCE((SELECT COUNT(DISTINCT AP.PART_OBJID)::int
                      FROM ARRIVAL_PLAN AP
                      JOIN PURCHASE_ORDER_MASTER POM3 ON POM3.OBJID = AP.PARENT_OBJID
                     WHERE POM3.CONTRACT_MGMT_OBJID = PM.OBJID
                       AND POM3.MAIL_SEND_DATE IS NOT NULL
                       AND POM3.STATUS = 'create'
                       AND COALESCE(AP.RECEIPT_QTY::numeric, 0) > 0), 0)
      , 0)                                                  AS non_dlv_item_cnt,
      GREATEST(
        COALESCE((SELECT SUM(COALESCE(POP.ORDER_QTY::numeric, 0))
                    FROM PURCHASE_ORDER_PART POP
                    JOIN PURCHASE_ORDER_MASTER POM2 ON POM2.OBJID = POP.PURCHASE_ORDER_MASTER_OBJID
                   WHERE POM2.CONTRACT_MGMT_OBJID = PM.OBJID
                     AND POM2.MAIL_SEND_DATE IS NOT NULL
                     AND POM2.STATUS = 'create'), 0)
        - COALESCE((SELECT SUM(COALESCE(AP.RECEIPT_QTY::numeric, 0))
                      FROM ARRIVAL_PLAN AP
                      JOIN PURCHASE_ORDER_MASTER POM3 ON POM3.OBJID = AP.PARENT_OBJID
                     WHERE POM3.CONTRACT_MGMT_OBJID = PM.OBJID
                       AND POM3.MAIL_SEND_DATE IS NOT NULL
                       AND POM3.STATUS = 'create'), 0)
      , 0)                                                  AS non_dlv_qty
    FROM PROJECT_MGMT PM
    LEFT JOIN CONTRACT_MGMT CTR ON CTR.OBJID = PM.CONTRACT_OBJID
    ${whereSql}
    ORDER BY PM.REGDATE DESC
    LIMIT ${addParam(limit)} OFFSET ${addParam(offset)}
  `;
  const countSql = `
    SELECT COUNT(*)::int AS cnt
      FROM PROJECT_MGMT PM
      LEFT JOIN CONTRACT_MGMT CTR ON CTR.OBJID = PM.CONTRACT_OBJID
      ${whereSql}
  `;
  try {
    const [d, c] = await Promise.all([
      pool.query(dataSql, params),
      pool.query(countSql, params.slice(0, params.length - 2)),
    ]);
    return { rows: d.rows, totalCount: c.rows[0]?.cnt ?? 0, page, pageSize };
  } catch (e: any) {
    logger.error("listProjectStatus 실패", { error: e.message });
    return { rows: [], totalCount: 0, page, pageSize };
  }
}

// ─── 8) 발주서관리 (wace 1:1 — purchaseOrder.xml purchaseOrderMasterList_new) ──
//
// 매퍼 본문: wace_plm/src/com/pms/mapper/purchaseOrder.xml:3295-3589
// 화면: wace_plm/.../purchaseOrder/purchaseOrderList_new.jsp (1085 lines)
// 라우트: GET /api/purchase/order-list
//
// 컬럼: 품의서No · 발주서No · 프로젝트번호 · 구매유형 · 주문유형 · 제품구분 ·
//      품번 · 품명 · 공급업체 · 환종 · 총액 · 메일발송 · 발주일 · 구매담당자 · 작성일
// 검색: 년도/고객사/프로젝트(CSV)/발주No/공급업체/품번/품명/입고요청일/발주일/
//      주문유형/제품구분/구매유형/구매담당자/메일발송
export async function listPurchaseOrderList(filter: PurchaseListFilter): Promise<ListResult<any>> {
  const pool = getPool();
  const { limit, offset, page, pageSize } = clampPaging(filter);

  const where: string[] = [
    // wace 운영판 동일: 동시발주 마스터 또는 비-동시발주만 노출
    `(POM.MULTI_MASTER_YN = 'Y' OR COALESCE(POM.MULTI_MASTER_YN, '') <> 'Y' AND COALESCE(POM.MULTI_YN, '') <> 'Y')`,
  ];
  const params: any[] = [];
  const addParam = (val: any) => { params.push(val); return `$${params.length}`; };

  if (filter.year) where.push(`TO_CHAR(POM.REGDATE, 'YYYY') = ${addParam(filter.year)}`);
  if (filter.customer_cd) {
    where.push(`EXISTS (SELECT 1 FROM PROJECT_MGMT S_P WHERE POM.CONTRACT_MGMT_OBJID = S_P.OBJID AND S_P.CUSTOMER_OBJID = REPLACE(${addParam(filter.customer_cd)}, 'C_', ''))`);
  }
  if (filter.project_no) {
    // CSV 다중 선택 지원 (wace project_nos 와 동일)
    const ids = String(filter.project_no).split(",").map((s) => s.trim()).filter(Boolean);
    if (ids.length > 0) {
      const ph = ids.map((v) => addParam(v)).join(",");
      where.push(`POM.CONTRACT_MGMT_OBJID IN (${ph})`);
    }
  }
  if (filter.purchase_order_no) where.push(`POM.PURCHASE_ORDER_NO ILIKE ${addParam(`%${filter.purchase_order_no}%`)}`);
  if (filter.partner_objid) where.push(`POM.PARTNER_OBJID = REPLACE(${addParam(filter.partner_objid)}, 'C_', '')`);
  if (filter.delivery_start_date) where.push(`POM.DELIVERY_DATE::DATE >= ${addParam(filter.delivery_start_date)}::DATE`);
  if (filter.delivery_end_date) where.push(`POM.DELIVERY_DATE::DATE <= ${addParam(filter.delivery_end_date)}::DATE`);
  if (filter.reg_start_date) where.push(`POM.MAIL_SEND_DATE::DATE >= ${addParam(filter.reg_start_date)}::DATE`);
  if (filter.reg_end_date) where.push(`POM.MAIL_SEND_DATE::DATE <= ${addParam(filter.reg_end_date)}::DATE`);
  if (filter.category_cd) where.push(`CM.CATEGORY_CD = ${addParam(filter.category_cd)}`);
  if (filter.product_cd) where.push(`CM.PRODUCT = ${addParam(filter.product_cd)}`);
  if (filter.purchase_type) where.push(`SRM.PURCHASE_TYPE = ${addParam(filter.purchase_type)}`);
  if (filter.writer) where.push(`POM.WRITER = ${addParam(filter.writer)}`);
  if (filter.part_no) {
    where.push(`EXISTS (SELECT 1 FROM PURCHASE_ORDER_PART POP WHERE POP.PURCHASE_ORDER_MASTER_OBJID = POM.OBJID AND TRIM(UPPER(POP.PART_NO)) LIKE '%' || TRIM(UPPER(${addParam(filter.part_no)})) || '%')`);
  }
  if (filter.part_name) {
    where.push(`EXISTS (SELECT 1 FROM PURCHASE_ORDER_PART POP WHERE POP.PURCHASE_ORDER_MASTER_OBJID = POM.OBJID AND TRIM(UPPER(POP.PART_NAME)) LIKE '%' || TRIM(UPPER(${addParam(filter.part_name)})) || '%')`);
  }
  if (filter.mail_send_yn === "Y") {
    where.push(`POM.MAIL_SEND_YN = 'Y' AND (POM.STATUS IS NULL OR POM.STATUS <> 'orderCancel')`);
  } else if (filter.mail_send_yn === "orderCancel") {
    where.push(`POM.STATUS = 'orderCancel'`);
  } else if (filter.mail_send_yn === "N") {
    where.push(`(POM.MAIL_SEND_YN IS NULL OR POM.MAIL_SEND_YN = '' OR POM.MAIL_SEND_YN = 'N') AND (POM.STATUS IS NULL OR POM.STATUS <> 'orderCancel')`);
  }

  const whereSql = `WHERE ${where.join(" AND ")}`;

  const fromJoinSql = `
    FROM PURCHASE_ORDER_MASTER POM
    LEFT JOIN (
      SELECT   B.OBJID            AS ROUTE_OBJID,
               B.STATUS           AS APPR_STATUS,
               CASE B.STATUS
                 WHEN 'inProcess' THEN '결재중'
                 WHEN 'complete'  THEN '결재완료'
                 WHEN 'reject'    THEN '반려'
                 WHEN 'cancel'    THEN '취소'
                 ELSE '' END      AS APPR_STATUS_NAME,
               A.OBJID            AS APPROVAL_OBJID,
               A.TARGET_OBJID,
               B.ROUTE_SEQ,
               TO_CHAR(B.REGDATE, 'YYYY-MM-DD') AS APPR_DATE
        FROM APPROVAL A,
             (SELECT T1.*
                FROM (SELECT TARGET_OBJID, MAX(T.ROUTE_SEQ) AS ROUTE_SEQ
                        FROM ROUTE T GROUP BY T.TARGET_OBJID) T,
                     ROUTE T1
               WHERE T.TARGET_OBJID = T1.TARGET_OBJID
                 AND T.ROUTE_SEQ   = T1.ROUTE_SEQ
             ) B
       WHERE A.OBJID = B.APPROVAL_OBJID
         AND A.TARGET_TYPE = 'PURCHASE_ORDER'
    ) A ON POM.OBJID::VARCHAR = A.TARGET_OBJID::VARCHAR
    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
          AND SRM.DOC_TYPE IN ('PROPOSAL', 'PURCHASE_REG_PROPOSAL')
  `;

  const dataSql = `
    SELECT
      POM.OBJID                                                                 AS objid,
      (SELECT ARRAY_TO_STRING(ARRAY_AGG(OBJID), ',') FROM PURCHASE_ORDER_MASTER S
         WHERE POM.OBJID = S.MULTI_MASTER_OBJID)                                AS multi_objids,
      TO_CHAR(POM.REGDATE, 'YYYY')                                              AS po_year,
      (SELECT SUPPLY_NAME FROM SUPPLY_MNG O
         WHERE O.OBJID::VARCHAR = CM.CUSTOMER_OBJID)                            AS customer_name,
      CM.CUSTOMER_PROJECT_NAME                                                  AS customer_project_name,
      CM.PROJECT_NO                                                             AS project_no,
      POM.PURCHASE_ORDER_NO                                                     AS purchase_order_no,
      (CASE WHEN POM.MULTI_YN = 'Y' AND POM.MULTI_MASTER_YN <> 'Y' THEN 'ㅡ' ELSE '' END)
        || COALESCE(POM.TITLE, '')                                              AS title,
      POM.DELIVERY_PLACE                                                        AS delivery_place,
      (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = POM.DELIVERY_PLACE)      AS delivery_place_name,
      POM.INSPECT_METHOD                                                        AS inspect_method,
      (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = POM.INSPECT_METHOD)      AS inspect_method_name,
      POM.PAYMENT_TERMS                                                         AS payment_terms,
      (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = POM.PAYMENT_TERMS)       AS payment_terms_name,
      POM.DELIVERY_DATE                                                         AS delivery_date,
      POM.TYPE                                                                  AS type,
      (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = POM.TYPE)                AS type_name,
      POM.PARTNER_OBJID                                                         AS partner_objid,
      (SELECT CLIENT_NM FROM CLIENT_MNG WHERE OBJID::VARCHAR = POM.PARTNER_OBJID)
                                                                                AS partner_name,
      POM.SALES_MNG_USER_ID                                                     AS sales_mng_user_id,
      (SELECT USER_NAME FROM USER_INFO WHERE USER_ID = POM.SALES_MNG_USER_ID)   AS sales_mng_user_name,
      TO_CHAR(POM.REGDATE, 'YYYY-MM-DD')                                        AS regdate,
      POM.TOTAL_PRICE                                                           AS total_price,
      POM.TOTAL_PRICE_ALL                                                       AS total_price_all,
      POM.DISCOUNT_PRICE                                                        AS discount_price,
      POM.DISCOUNT_PRICE_ALL                                                    AS discount_price_all,
      POM.TOTAL_SUPPLY_PRICE                                                    AS total_supply_price,
      POM.TOTAL_SUPPLY_UNIT_PRICE                                               AS total_supply_unit_price,
      POM.TOTAL_REAL_SUPPLY_PRICE                                               AS total_real_supply_price,
      POM.NEGO_RATE                                                             AS nego_rate,
      POM.WRITER                                                                AS writer,
      (SELECT USER_NAME FROM USER_INFO WHERE USER_ID = POM.WRITER)              AS writer_name,
      POM.ORDER_TYPE_CD                                                         AS order_type_cd,
      (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = POM.ORDER_TYPE_CD)       AS order_type_cd_name,
      POM.UNIT_CODE                                                             AS unit_code,
      (SELECT O.UNIT_NO || '-' || O.TASK_NAME FROM PMS_WBS_TASK O WHERE O.OBJID = POM.UNIT_CODE)
                                                                                AS unit_name,
      POM.MULTI_YN                                                              AS multi_yn,
      POM.MULTI_MASTER_YN                                                       AS multi_master_yn,
      POM.MULTI_MASTER_OBJID                                                    AS multi_master_objid,
      CASE WHEN POM.MULTI_MASTER_YN = 'Y' THEN '' ELSE POM.MULTI_YN END         AS multi_yn_maked,
      POM.MAIL_SEND_YN                                                          AS mail_send_yn,
      POM.MAIL_SEND_DATE                                                        AS mail_send_date,
      POM.STATUS                                                                AS status,
      COALESCE(POM.FORM_TYPE, 'general')                                        AS form_type,
      A.APPR_STATUS                                                             AS appr_status,
      CASE WHEN POM.STATUS = 'cancel' THEN '취소'
           ELSE COALESCE(A.APPR_STATUS_NAME, '작성중')
      END                                                                       AS appr_status_name,
      A.ROUTE_OBJID                                                             AS route_objid,
      A.APPROVAL_OBJID                                                          AS approval_objid,
      A.APPR_DATE                                                               AS appr_date,
      POM.SALES_REQUEST_OBJID                                                   AS sales_request_objid,
      SRM.REQUEST_MNG_NO                                                        AS proposal_no,
      (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = SRM.PURCHASE_TYPE)       AS purchase_type_name,
      (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = CM.PRODUCT)              AS product_name,
      (SELECT CODE_NAME FROM COMM_CODE WHERE CODE_ID = CM.CATEGORY_CD)          AS category_name,
      (SELECT CASE WHEN COUNT(*) > 1
                   THEN MIN(POP.PART_NO) || ' 외 ' || (COUNT(*) - 1) || '건'
                   ELSE MIN(POP.PART_NO)
              END
         FROM PURCHASE_ORDER_PART POP
        WHERE POP.PURCHASE_ORDER_MASTER_OBJID = POM.OBJID)                      AS part_no,
      (SELECT CASE WHEN COUNT(*) > 1
                   THEN MIN(POP.PART_NAME) || ' 외 ' || (COUNT(*) - 1) || '건'
                   ELSE MIN(POP.PART_NAME)
              END
         FROM PURCHASE_ORDER_PART POP
        WHERE POP.PURCHASE_ORDER_MASTER_OBJID = POM.OBJID)                      AS part_name,
      (SELECT CC.CODE_NAME FROM COMM_CODE CC
         WHERE CC.CODE_ID = (SELECT POP.CURRENCY FROM PURCHASE_ORDER_PART POP
                              WHERE POP.PURCHASE_ORDER_MASTER_OBJID = POM.OBJID::VARCHAR
                                AND POP.CURRENCY IS NOT NULL AND POP.CURRENCY <> ''
                              LIMIT 1))                                         AS currency_name
    ${fromJoinSql}
    ${whereSql}
    ORDER BY POM.REGDATE DESC NULLS LAST,
             COALESCE(NULLIF(SPLIT_PART(POM.PURCHASE_ORDER_NO, '-', 3), ''), '0')::NUMERIC DESC
    LIMIT ${addParam(limit)} OFFSET ${addParam(offset)}
  `;
  const countSql = `SELECT COUNT(*)::int AS cnt ${fromJoinSql} ${whereSql}`;
  try {
    const [d, c] = await Promise.all([
      pool.query(dataSql, params),
      pool.query(countSql, params.slice(0, params.length - 2)),
    ]);
    return { rows: d.rows, totalCount: c.rows[0]?.cnt ?? 0, page, pageSize };
  } catch (e: any) {
    logger.error("listPurchaseOrderList 실패", { error: e.message });
    return { rows: [], totalCount: 0, page, pageSize };
  }
}

// ─── 옵션 — 공급업체 / 작성자 (구매메뉴 공용) ──────────────────

// 견적요청서 / 발주서 vendor — wace 매퍼는 client_mng 와 매칭 (supply_mng 아님)
// RPS client_mng 는 status 컬럼 없음 → use_yn 사용 ('Y'/'N')
export async function listVendorOptions(): Promise<{ code: string; label: string }[]> {
  const pool = getPool();
  try {
    const r = await pool.query(
      `SELECT OBJID AS code, CLIENT_NM AS label
         FROM CLIENT_MNG
        WHERE COALESCE(USE_YN, 'Y') IN ('Y', 'y', '1')
          AND CLIENT_NM IS NOT NULL AND CLIENT_NM <> ''
        ORDER BY CLIENT_NM
        LIMIT 2000`,
    );
    return r.rows;
  } catch (e: any) {
    logger.error("listVendorOptions 실패", { error: e.message });
    return [];
  }
}

export async function listSupplierOptions(): Promise<{ code: string; label: string }[]> {
  const pool = getPool();
  try {
    const r = await pool.query(
      `SELECT OBJID::VARCHAR AS code, SUPPLY_NAME AS label
         FROM SUPPLY_MNG
        WHERE COALESCE(STATUS, 'active') IN ('active', '활성')
          AND SUPPLY_NAME IS NOT NULL AND SUPPLY_NAME <> ''
        ORDER BY SUPPLY_NAME`,
    );
    return r.rows;
  } catch (e: any) {
    logger.error("listSupplierOptions 실패", { error: e.message });
    return [];
  }
}

export async function listUserOptions(): Promise<
  { code: string; label: string; name?: string; position?: string; phone?: string; email?: string }[]
> {
  const pool = getPool();
  try {
    const r = await pool.query(
      `SELECT USER_ID                                                 AS code,
              USER_NAME || COALESCE(' (' || DEPT_NAME || ')', '')     AS label,
              USER_NAME                                               AS name,
              POSITION_NAME                                           AS position,
              CELL_PHONE                                              AS phone,
              EMAIL                                                   AS email
         FROM USER_INFO
        WHERE COALESCE(STATUS, 'active') IN ('active', '활성', 'ACTIVE')
          AND USER_NAME IS NOT NULL AND USER_NAME <> ''
        ORDER BY USER_NAME`,
    );
    return r.rows;
  } catch (e: any) {
    logger.error("listUserOptions 실패", { error: e.message });
    return [];
  }
}

export async function listProjectOptions(): Promise<{ code: string; label: string }[]> {
  const pool = getPool();
  try {
    const r = await pool.query(
      `SELECT OBJID::VARCHAR AS code, PROJECT_NO AS label
         FROM PROJECT_MGMT
        WHERE PROJECT_NO IS NOT NULL AND PROJECT_NO <> ''
        ORDER BY PROJECT_NO DESC
        LIMIT 500`,
    );
    return r.rows;
  } catch {
    return [];
  }
}
