// ============================================================
// 프로젝트관리 > 제품구분_WBS관리 (wace_plm 도메인 이식)
// 메인 화면: wace `/project/wbsTemplateMngList.do`
// 통합 팝업: wace `/project/WBSExcelImportPopUp.do`
// 매퍼 SQL: wace_plm/src/com/pms/mapper/project.xml:5552 외
// JSP 원본: wbsTemplateMngList.jsp + WBSExcelImportPopUp.jsp
// 서비스 원본: ProjectService.java:2902 mergeExcelUploadWBS
//
// 1:1 이식 + 변경점:
//   · objid 채번: wace CommonUtils.createObjId() → PG `gen_random_uuid()::text` (영업관리 패턴)
//   · 엑셀 파싱: Apache POI XSSFWorkbook → npm `xlsx` (SheetJS)
//   · 트랜잭션: SqlSession.commit() → PG client BEGIN/COMMIT
// ============================================================

import { getPool } from "../database/db";
import { logger } from "../utils/logger";
import * as XLSX from "xlsx";

// ─── 타입 ────────────────────────────────────────────────

export interface TemplateListFilter {
  product?: string;
}

export interface WbsTaskRow {
  WBS_TASK_OBJID: string;
  TASK_NAME: string;
  UNIT_NO: string;
  UPPER_TASK_OBJID: string;
  TASK_LEVEL: string;
}

export interface SaveTemplatePayload {
  templateObjId?: string;
  product: string;
  title: string;
  customer_product?: string;
  tasks: WbsTaskRow[];          // TOTAL 행 포함 (TASK_LEVEL=0)
}

// ─── 1) 메인 그리드 (wace project.wbsTemplateMngGridList 1:1) ─────

export async function listTemplates(filter: TemplateListFilter) {
  const pool = getPool();
  const conditions: string[] = ["1=1"];
  const params: any[] = [];
  let idx = 1;

  if (filter.product) {
    conditions.push(`PRODUCT_OBJID = $${idx++}`);
    params.push(filter.product);
  }

  const sql = `
    SELECT
        OBJID,
        PRODUCT_OBJID,
        CODE_NAME(PRODUCT_OBJID) AS PRODUCT_NAME,
        TITLE,
        WRITER,
        (SELECT DEPT_NAME || USER_NAME FROM USER_INFO WHERE USER_ID = WRITER) AS WRITER_TITLE,
        REG_DATE,
        TO_CHAR(REG_DATE, 'YYYY-MM-DD') AS REG_DATE_TITLE,
        (SELECT COUNT(1) FROM PMS_WBS_TASK_STANDARD PWTS WHERE PWTS.PARENT_OBJID = OBJID) AS WBS_TASK_CNT,
        CUSTOMER_PRODUCT
    FROM PMS_WBS_TEMPLATE
    WHERE ${conditions.join(" AND ")}
    ORDER BY REG_DATE DESC NULLS LAST
  `;

  const result = await pool.query(sql, params);
  return result.rows;
}

// ─── 2) 팝업 진입: 헤더 + 트리 (wace WBSExcelImportPopUp.do 1:1) ──

export async function getById(objid: string) {
  const pool = getPool();

  const masterSql = `
    SELECT OBJID, PRODUCT_OBJID,
           CODE_NAME(PRODUCT_OBJID) AS PRODUCT_OBJID_NAME,
           TITLE, WRITER, REG_DATE, CUSTOMER_PRODUCT
    FROM PMS_WBS_TEMPLATE
    WHERE OBJID = $1
  `;

  const taskSql = `
    SELECT T.OBJID,
           T.PARENT_OBJID,
           T.TASK_NAME,
           T.TASK_SEQ,
           T.TASK_LEVEL,
           T.USER_ID,
           (SELECT USER_NAME FROM USER_INFO WHERE USER_ID = T.USER_ID) AS USER_ID_TITLE,
           T.WRITER,
           T.REG_DATE,
           T.UNIT_NO,
           T.UPPER_TASK_OBJID
    FROM PMS_WBS_TASK_STANDARD AS T
    WHERE T.PARENT_OBJID = $1
    ORDER BY CAST(T.TASK_SEQ AS INTEGER)
  `;

  const [master, tasks] = await Promise.all([
    pool.query(masterSql, [objid]),
    pool.query(taskSql, [objid]),
  ]);

  return {
    master: master.rows[0] ?? null,
    tasks: tasks.rows,
  };
}

// ─── 3) 중복 체크 (wace project.getWBSTemplateProductList 1:1) ────

export async function checkDuplicate(product: string, title: string) {
  const pool = getPool();
  const sql = `
    SELECT T.OBJID
    FROM PMS_WBS_TEMPLATE T
    LEFT OUTER JOIN PMS_WBS_TASK_STANDARD T1 ON T.OBJID = T1.PARENT_OBJID
    WHERE T.PRODUCT_OBJID = $1
      AND T.TITLE = $2
    LIMIT 1
  `;
  const result = await pool.query(sql, [product, title]);
  return result.rows.length > 0;
}

// ─── 4) 통합 저장 (wace ProjectService.mergeExcelUploadWBS 1:1) ────

export async function saveTemplate(payload: SaveTemplatePayload, writer: string) {
  const pool = getPool();
  const client = await pool.connect();

  try {
    await client.query("BEGIN");

    let masterObjId: string;

    if (payload.templateObjId) {
      // 수정 모드: 헤더 UPDATE 안 함, 트리 일괄 DELETE → 재 INSERT (wace 동일)
      masterObjId = payload.templateObjId;
      await client.query(
        `DELETE FROM PMS_WBS_TASK_STANDARD WHERE PARENT_OBJID = $1`,
        [masterObjId]
      );
    } else {
      // 신규 모드: 헤더 INSERT (wace saveWBSTaskTemp 1:1)
      const masterRes = await client.query(
        `INSERT INTO PMS_WBS_TEMPLATE
                (OBJID, PRODUCT_OBJID, TITLE, CUSTOMER_PRODUCT, WRITER, REG_DATE)
         VALUES (gen_random_uuid()::text, $1, $2, $3, $4, now())
         RETURNING OBJID`,
        [
          payload.product,
          payload.title,
          payload.customer_product ?? "",
          writer,
        ]
      );
      masterObjId = masterRes.rows[0].objid;
    }

    // 트리 INSERT (wace saveWBSTemplateTaskInfo — upsert)
    for (let i = 0; i < payload.tasks.length; i++) {
      const t = payload.tasks[i];
      await client.query(
        `INSERT INTO PMS_WBS_TASK_STANDARD
                (OBJID, PARENT_OBJID, TASK_NAME, TASK_SEQ, TASK_LEVEL,
                 USER_ID, WRITER, REG_DATE, UNIT_NO, UPPER_TASK_OBJID)
         VALUES ($1, $2, $3, $4, $5, $6, $7, now(), $8, $9)
         ON CONFLICT (OBJID) DO UPDATE SET
            TASK_NAME        = EXCLUDED.TASK_NAME,
            TASK_SEQ         = EXCLUDED.TASK_SEQ,
            TASK_LEVEL       = EXCLUDED.TASK_LEVEL,
            USER_ID          = EXCLUDED.USER_ID,
            UNIT_NO          = EXCLUDED.UNIT_NO,
            UPPER_TASK_OBJID = EXCLUDED.UPPER_TASK_OBJID`,
        [
          t.WBS_TASK_OBJID,
          masterObjId,
          t.TASK_NAME,
          String(i + 1),
          t.TASK_LEVEL,
          "",                   // user_id (wace mergeExcelUploadWBS도 미설정)
          writer,
          t.UNIT_NO,
          t.UPPER_TASK_OBJID,
        ]
      );
    }

    await client.query("COMMIT");
    return { OBJID: masterObjId };
  } catch (e) {
    await client.query("ROLLBACK");
    logger.error("[wbsTemplateService.saveTemplate] failed", { error: e });
    throw e;
  } finally {
    client.release();
  }
}

// ─── 5) 다건 삭제 (wace ProjectService.deleteWBSTemplateMaster 1:1) ─

export async function deleteTemplates(objids: string[]) {
  if (!objids || objids.length === 0) {
    return { result: "true", msg: "삭제할 대상이 없습니다." };
  }

  const pool = getPool();
  const client = await pool.connect();

  try {
    await client.query("BEGIN");

    const placeholders = objids.map((_, i) => `$${i + 1}`).join(",");

    await client.query(
      `DELETE FROM PMS_WBS_TEMPLATE WHERE OBJID IN (${placeholders})`,
      objids
    );
    await client.query(
      `DELETE FROM PMS_WBS_TASK_STANDARD WHERE PARENT_OBJID IN (${placeholders})`,
      objids
    );

    await client.query("COMMIT");
    return { result: "true", msg: "삭제하였습니다." };
  } catch (e) {
    await client.query("ROLLBACK");
    logger.error("[wbsTemplateService.deleteTemplates] failed", { error: e });
    throw e;
  } finally {
    client.release();
  }
}

// ─── 6) 엑셀 파싱 (wace ProjectService.parsingExcelFile 1:1) ──────
// 입력: 업로드된 .xlsx Buffer (multer memoryStorage)
// 출력: List<{ WBS_OBJID, UNIT_NO, TASK_NAME }> — 클라이언트가 행 채움
//
// 엑셀 규칙 (wace 동일):
//  · 0행 = "입력" 라벨, 1행 = "수준 / unit name" 헤더 → 무시
//  · 2행부터 데이터: 열 0/1/2 = 수준1/2/3 셀, 열 3 = TASK_NAME
//  · UNIT_NO = 수준3 → 수준2 → 수준1 우선
//  · unit_no + task_name 둘 다 있어야 결과 포함

export function parseExcel(buffer: Buffer) {
  const wb = XLSX.read(buffer, { type: "buffer" });
  const firstSheet = wb.SheetNames[0];
  const sheet = wb.Sheets[firstSheet];

  // raw=false → cell 표시값(string) / defval='' → 빈 셀도 string ''
  const rows = XLSX.utils.sheet_to_json<any[]>(sheet, {
    header: 1,
    raw: false,
    defval: "",
  });

  const result: Array<{ WBS_OBJID: string; UNIT_NO: string; TASK_NAME: string }> = [];

  // 2행(인덱스 2)부터 — wace 동일
  for (let i = 2; i < rows.length; i++) {
    const row = rows[i];
    if (!row) continue;

    const levelValues = [
      String(row[0] ?? "").trim(),
      String(row[1] ?? "").trim(),
      String(row[2] ?? "").trim(),
    ];
    const taskName = String(row[3] ?? "").trim();

    const unitNo = levelValues[2] || levelValues[1] || levelValues[0];

    if (unitNo && taskName) {
      result.push({
        // wace는 server측에서 OBJID 발급 (CommonUtils.createObjId)
        // vexplor_rps: 클라이언트가 화면 표시용 임시 ID 발급, 저장 시 새 OBJID 부여
        // → 여기서는 crypto.randomUUID()로 임시 ID 생성
        WBS_OBJID: crypto.randomUUID(),
        UNIT_NO: unitNo,
        TASK_NAME: taskName,
      });
    }
  }

  return result;
}
