// ============================================================
// 개발관리 설계변경 리스트 (M5) — wace partMng.xml#partMngHistList 1:1 read-only.
//
// 매퍼 매핑:
//   partMngHistList (line 7,787) → list()
// + getByObjid() — 행 클릭 상세 다이얼로그용 (raw PART_MNG_HISTORY 행)
//
// vexplor_rps 적응:
//   · NVL() → COALESCE()
//   · PART_MNG.OBJID(bigint) = PM.PARENT_PART_NO(varchar) → ::varchar cast
//   · CODE_NAME() → LEFT JOIN comm_code 별칭
// ============================================================

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

export interface EoHistoryListFilter {
  Year?: string;
  contract_objid?: string;
  unit_code?: string;
  part_no?: string;
  part_name?: string;
  change_option?: string;
  eo_start_date?: string;
  eo_end_date?: string;
  change_type?: string;
  part_type?: string;
  writer_id?: string;
  page?: number;
  page_size?: number;
}

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

  if (filter.Year)           { conds.push(`TO_CHAR(CM.REGDATE,'YYYY') = $${idx++}`); params.push(filter.Year); }
  if (filter.contract_objid) { conds.push(`CM.OBJID = $${idx++}`); params.push(filter.contract_objid); }
  if (filter.unit_code)      { conds.push(`B.UNIT_CODE = $${idx++}`); params.push(filter.unit_code); }
  if (filter.part_no)        { conds.push(`UPPER(PM.PART_NO) LIKE UPPER($${idx++})`); params.push(`%${filter.part_no}%`); }
  if (filter.part_name)      { conds.push(`UPPER(PM.PART_NAME) LIKE UPPER($${idx++})`); params.push(`%${filter.part_name}%`); }
  if (filter.change_option)  { conds.push(`PM.CHANGE_OPTION = $${idx++}`); params.push(filter.change_option); }
  if (filter.eo_start_date)  { conds.push(`TO_DATE(PM.EO_DATE,'YYYY-MM-DD') >= TO_DATE($${idx++},'YYYY-MM-DD')`); params.push(filter.eo_start_date); }
  if (filter.eo_end_date)    { conds.push(`TO_DATE(PM.EO_DATE,'YYYY-MM-DD') <= TO_DATE($${idx++},'YYYY-MM-DD')`); params.push(filter.eo_end_date); }
  if (filter.change_type)    { conds.push(`PM.CHANGE_TYPE = $${idx++}`); params.push(filter.change_type); }
  if (filter.part_type)      { conds.push(`PM.PART_TYPE = $${idx++}`); params.push(filter.part_type); }
  if (filter.writer_id)      { conds.push(`PM.WRITER = $${idx++}`); params.push(filter.writer_id); }

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

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

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

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

  const baseSql = `
    SELECT
       PM.OBJID,
       PM.EO_NO,
       TO_CHAR(CM.REGDATE, 'YYYY') AS YEAR,
       COALESCE(CM.CUSTOMER_PROJECT_NAME, CM2.CUSTOMER_PROJECT_NAME) AS PROJECT_NAME,
       COALESCE(CM2.PROJECT_NO, CM.PROJECT_NO) AS PROJECT_NO,
       (SELECT SP.PART_NO || ' ' || SP.PART_NAME
          FROM PART_MNG SP
         WHERE SP.OBJID::varchar = PM.PARENT_PART_NO) AS PARENT_PART_INFO,
       CASE WHEN PM.CHANGE_OPTION = '0001790'
            THEN COALESCE(PM.PART_NO,'') || '->' || COALESCE(PM.CHG_PART_NO,'')
            ELSE PM.PART_NO END AS PART_NO_DISP,
       CASE WHEN PM.CHANGE_OPTION = '0001790'
            THEN COALESCE(PM.PART_NAME,'') || '->' ||
                 COALESCE((SELECT P.PART_NAME FROM PART_MNG P WHERE P.OBJID::varchar = PM.CHG_PART_OBJID), '')
            ELSE PM.PART_NAME END AS PART_NAME_DISP,
       PM.PART_NO,
       PM.PART_NAME,
       PM.BOM_QTY_STATUS,
       CASE WHEN PM.BOM_QTY_STATUS = 'adding' THEN PM.QTY_TEMP ELSE PM.QTY END AS QTY,
       CASE WHEN PM.BOM_QTY_STATUS = 'adding' THEN ''
            WHEN PM.BOM_QTY_STATUS = 'beforeEdit' AND PM.QTY = PM.QTY_TEMP THEN ''
            ELSE PM.QTY_TEMP END AS QTY_TEMP,
       PM.CHANGE_TYPE,
       CC_CHGTYPE.code_name  AS CHANGE_TYPE_NAME,
       PM.CHANGE_OPTION,
       CC_CHGOPT.code_name   AS CHANGE_OPTION_NAME,
       CASE WHEN PM.CHANGE_OPTION = '0001790'
            THEN COALESCE(PM.REVISION,'') || '->' || COALESCE(PM.CHG_PART_REV,'')
            ELSE PM.REVISION END AS REVISION_DISP,
       PM.REVISION,
       PM.EO_DATE,
       PM.PART_TYPE,
       CC_PARTTYPE.code_name AS PART_TYPE_NAME,
       PM.WRITER,
       UI.user_name AS WRITER_NAME,
       COALESCE(WTS.UNIT_NO || '-' || WTS.TASK_NAME, '') AS UNIT_NAME,
       TO_CHAR(PM.HIS_REG_DATE, 'YYYY-MM-DD') AS HIS_REG_DATE_TITLE,
       PM.BOM_DEPLOY_DATE,
       TO_CHAR(PM.BOM_DEPLOY_DATE, 'YYYY-MM-DD') AS BOM_DEPLOY_DATE_TITLE
      FROM PART_MNG_HISTORY PM
      LEFT JOIN PROJECT_MGMT   CM   ON CM.OBJID  = PM.CONTRACT_OBJID
      LEFT JOIN PART_BOM_REPORT B   ON B.OBJID   = PM.BOM_REPORT_OBJID
      LEFT JOIN PROJECT_MGMT   CM2  ON CM2.OBJID = B.CONTRACT_OBJID
      LEFT JOIN PMS_WBS_TASK   WTS  ON WTS.OBJID = B.UNIT_CODE
      LEFT JOIN user_info      UI   ON UI.user_id = PM.WRITER
      LEFT JOIN COMM_CODE CC_CHGTYPE  ON CC_CHGTYPE.code_id  = PM.CHANGE_TYPE   AND CC_CHGTYPE.status='active'
      LEFT JOIN COMM_CODE CC_CHGOPT   ON CC_CHGOPT.code_id   = PM.CHANGE_OPTION AND CC_CHGOPT.status='active'
      LEFT JOIN COMM_CODE CC_PARTTYPE ON CC_PARTTYPE.code_id = PM.PART_TYPE     AND CC_PARTTYPE.status='active'
     WHERE NOT (PM.HIS_STATUS = 'DEPLOY' AND PM.CHANGE_TYPE IS NULL AND PM.REVISION = 'RE')
       AND PM.REVISION IS NOT NULL
       AND COALESCE(PM.BOM_STATUS, '') = 'deploy'
       AND ${where.sql}
  `;

  const dataSql = `${baseSql}
     ORDER BY COALESCE(PM.HIS_REG_DATE, PM.REG_DATE) DESC, PM.PART_NO
     LIMIT $${where.params.length + 1} OFFSET $${where.params.length + 2}`;
  const countSql = `SELECT COUNT(*)::int AS total FROM (${baseSql}) X`;

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

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

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

export async function getByObjid(objid: string) {
  const sql = `
    SELECT PM.*,
           CC_CHGTYPE.code_name  AS change_type_name,
           CC_CHGOPT.code_name   AS change_option_name,
           CC_PARTTYPE.code_name AS part_type_name,
           UI.user_name          AS writer_name,
           CM.customer_project_name,
           CM.project_no
      FROM PART_MNG_HISTORY PM
      LEFT JOIN PROJECT_MGMT  CM ON CM.OBJID = PM.CONTRACT_OBJID
      LEFT JOIN user_info     UI ON UI.user_id = PM.WRITER
      LEFT JOIN COMM_CODE CC_CHGTYPE  ON CC_CHGTYPE.code_id  = PM.CHANGE_TYPE   AND CC_CHGTYPE.status='active'
      LEFT JOIN COMM_CODE CC_CHGOPT   ON CC_CHGOPT.code_id   = PM.CHANGE_OPTION AND CC_CHGOPT.status='active'
      LEFT JOIN COMM_CODE CC_PARTTYPE ON CC_PARTTYPE.code_id = PM.PART_TYPE     AND CC_PARTTYPE.status='active'
     WHERE PM.OBJID = $1::numeric
  `;
  const r = await getPool().query(sql, [objid]);
  return r.rows[0] ?? null;
}
