import { NextRequest, NextResponse } from "next/server";
import { queryRows } from "@/lib/db";
import { getSession } from "@/lib/session";

// product/part - PART 등록(temp)/조회(release) 공용
// - mode: "register" → partMng.partMngTempList (status IN create/changing)
// - 기본 → partMng.partMngGridList (status = release)
export async function POST(request: NextRequest) {
  const user = await getSession();
  if (!user) return NextResponse.json({ success: false }, { status: 401 });

  const body = await request.json();
  const mode = String(body.mode || "").toLowerCase();
  const isRegister = mode === "register";

  const conditions: string[] = [];
  const params: unknown[] = [];
  let idx = 1;

  if (isRegister) {
    conditions.push(`P.status IN ('create','changing')`);
  } else {
    conditions.push(`P.status = 'release'`);
    // 원본: SEARCH_REVISION_RELEASE="1" 일 때만 is_last='1' 필터 (기본 "0"/"" 은 전체 조회)
    const rev = String(body.SEARCH_REVISION_RELEASE ?? "");
    if (rev === "1" || rev === "Y") {
      conditions.push(`P.is_last = '1'`);
    }
  }

  if (body.SEARCH_PART_NO) {
    conditions.push(`UPPER(P.part_no) LIKE UPPER('%' || $${idx++} || '%')`);
    params.push(body.SEARCH_PART_NO);
  }
  if (body.SEARCH_PART_NAME) {
    conditions.push(`UPPER(P.part_name) LIKE UPPER('%' || $${idx++} || '%')`);
    params.push(body.SEARCH_PART_NAME);
  }
  if (body.SEARCH_MATERIAL) {
    conditions.push(`UPPER(P.material) LIKE UPPER('%' || $${idx++} || '%')`);
    params.push(body.SEARCH_MATERIAL);
  }
  if (body.SEARCH_SPEC) {
    conditions.push(`UPPER(P.spec) LIKE UPPER('%' || $${idx++} || '%')`);
    params.push(body.SEARCH_SPEC);
  }
  if (body.SEARCH_PART_TYPE) {
    conditions.push(`P.part_type = $${idx++}`);
    params.push(body.SEARCH_PART_TYPE);
  }
  if (body.SEARCH_YEAR) {
    conditions.push(`TO_CHAR(P.reg_date, 'YYYY') = $${idx++}`);
    params.push(String(body.SEARCH_YEAR));
  }
  if (body.WRITER) {
    conditions.push(`P.writer = $${idx++}`);
    params.push(body.WRITER);
  }

  const where = conditions.join(" AND ");

  const sql = `
    SELECT
      ROW_NUMBER() OVER (
        ORDER BY P.part_no,
          (CASE WHEN P.revision LIKE 'RE%' THEN 0 ELSE 1 END) DESC,
          P.revision DESC
      ) AS "NUM",
      ROW_NUMBER() OVER (
        ORDER BY P.excel_upload_seq NULLS LAST, P.part_no
      ) AS "RNUM",
      P.objid::text AS "OBJID",
      P.part_no AS "PART_NO",
      P.part_name AS "PART_NAME",
      -- partMngTempGridList: PARENT_PART_INFO = Q.PARENT_PART_NO 기반 PART_NO
      (SELECT part_no FROM part_mng SP WHERE SP.objid = Q.parent_part_no) AS "PARENT_PART_INFO",
      -- BOM_QTY: PART_TYPE='0000063' 이면 '1', 아니면 bom_part_qty 합계
      CASE
        WHEN P.part_type = '0000063' THEN '1'
        ELSE COALESCE(
          (SELECT SUM(CASE WHEN QTY = '' THEN 0 ELSE COALESCE(QTY,'0')::NUMERIC END)::text
             FROM bom_part_qty Q2 WHERE Q2.last_part_objid = P.objid),
          ''
        )
      END AS "BOM_QTY",
      -- 원본 Q_QTY 로직 (partMngTempGridList 2250-2255)
      CASE
        WHEN Q.status = 'deploy' THEN Q.qty
        WHEN (Q.qty_temp IS NULL OR Q.qty_temp = '') THEN COALESCE(Q.qty, P.qty::text)
        ELSE COALESCE(Q.qty_temp, P.qty::text)
      END AS "Q_QTY",
      Q.bom_report_objid AS "BOM_REPORT_OBJID",
      Q.objid::text AS "OBJID_QTY",
      Q.child_objid AS "CHILD_OBJID",
      Q.qty AS "QTY",
      Q.qty_temp AS "QTY_TEMP",
      (SELECT COUNT(*) FROM attach_file_info WHERE target_objid = P.objid::text AND doc_type = '3D_CAD') AS "CU01_CNT",
      (SELECT COUNT(*) FROM attach_file_info WHERE target_objid = P.objid::text AND doc_type = '2D_DRAWING_CAD') AS "CU02_CNT",
      (SELECT COUNT(*) FROM attach_file_info WHERE target_objid = P.objid::text AND doc_type = '2D_PDF_CAD') AS "CU03_CNT",
      P.material AS "MATERIAL",
      P.spec AS "SPEC",
      P.post_processing AS "POST_PROCESSING",
      P.maker AS "MAKER",
      P.major_category AS "MAJOR_CATEGORY",
      P.sub_category AS "SUB_CATEGORY",
      P.revision AS "REVISION",
      P.eo_no AS "EO_NO",
      P.eo_date AS "EO_DATE",
      P.part_type AS "PART_TYPE",
      COALESCE((SELECT code_name FROM comm_code WHERE code_id = P.part_type LIMIT 1), '') AS "PART_TYPE_TITLE",
      P.remark AS "REMARK",
      P.status AS "STATUS",
      P.is_last AS "IS_LAST",
      P.is_longd AS "IS_LONGD",
      P.change_type AS "CHANGE_TYPE",
      P.change_option AS "CHANGE_OPTION",
      P.code1 AS "CODE1",
      P.code2 AS "CODE2",
      P.code3 AS "CODE3",
      P.code4 AS "CODE4",
      P.code5 AS "CODE5",
      P.writer AS "WRITER",
      COALESCE((SELECT user_name FROM user_info WHERE user_id = P.writer LIMIT 1), '') AS "WRITER_NAME",
      TO_CHAR(P.reg_date, 'YYYY-MM-DD') AS "REG_DATE"
    FROM part_mng P
      -- 원본 partMngTempGridList JOIN: changing 파트의 deployed 형제(같은 PART_NO)가 사용된 BOM_PART_QTY(beforeEdit) 매핑
      LEFT OUTER JOIN bom_part_qty Q
        ON P.objid IN (
             SELECT DISTINCT PM1.objid
               FROM part_mng PM1, part_mng PM2
              WHERE PM1.status = 'changing'
                AND PM2.status != 'changing'
                AND PM2.objid = Q.part_no
                AND PM1.part_no = PM2.part_no
           )
       AND Q.status IN ('beforeEdit')
    WHERE ${where}
    ORDER BY P.part_no,
      (CASE WHEN P.revision LIKE 'RE%' THEN 0 ELSE 1 END) DESC,
      P.revision DESC
  `;

  const rows = await queryRows(sql, params);
  return NextResponse.json({ RESULTLIST: rows, TOTAL_CNT: rows.length });
}
