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

// 원본: /salesMng/salesPartChgList.do — part_mng_history + sales_part_chg + project_mgmt
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 conditions: string[] = [
    "NOT (PM.his_status = 'DEPLOY' AND PM.change_type IS NULL AND PM.revision = 'RE')",
    "PM.revision IS NOT NULL",
    "COALESCE(PM.bom_status,'') = 'deploy'",
  ];
  const params: unknown[] = [];
  let idx = 1;

  if (body.year) {
    conditions.push(`TO_CHAR(CM.regdate, 'YYYY') = $${idx++}`);
    params.push(body.year);
  }
  if (body.project_no) {
    conditions.push(`COALESCE(CM.project_no, CM.contract_no) LIKE '%' || $${idx++} || '%'`);
    params.push(body.project_no);
  }
  if (body.part_no) {
    conditions.push(`PM.part_no LIKE '%' || $${idx++} || '%'`);
    params.push(body.part_no);
  }
  if (body.part_name) {
    conditions.push(`PM.part_name LIKE '%' || $${idx++} || '%'`);
    params.push(body.part_name);
  }
  if (body.change_type) {
    conditions.push(`PM.change_type = $${idx++}`);
    params.push(body.change_type);
  }
  if (body.change_option) {
    conditions.push(`PM.change_option = $${idx++}`);
    params.push(body.change_option);
  }
  if (body.part_type) {
    conditions.push(`PM.part_type = $${idx++}`);
    params.push(body.part_type);
  }
  if (body.revision) {
    conditions.push(`PM.revision = $${idx++}`);
    params.push(body.revision);
  }
  if (body.part_writer) {
    conditions.push(`PM.writer = $${idx++}`);
    params.push(body.part_writer);
  }
  if (body.sales_writer) {
    conditions.push(`SPC.writer = $${idx++}`);
    params.push(body.sales_writer);
  }
  if (body.act_cd) {
    conditions.push(`SPC.act_cd = $${idx++}`);
    params.push(body.act_cd);
  }
  if (body.act_status) {
    conditions.push(`COALESCE(SPC.act_status, '0001063') = $${idx++}`);
    params.push(body.act_status);
  }
  if (body.eo_date_start) {
    conditions.push(`TO_DATE(PM.eo_date, 'YYYY-MM-DD') >= TO_DATE($${idx++}, 'YYYY-MM-DD')`);
    params.push(body.eo_date_start);
  }
  if (body.eo_date_end) {
    conditions.push(`TO_DATE(PM.eo_date, 'YYYY-MM-DD') <= TO_DATE($${idx++}, 'YYYY-MM-DD')`);
    params.push(body.eo_date_end);
  }
  if (body.confirm_date_start) {
    conditions.push(`TO_DATE(SPC.confirm_date, 'YYYY-MM-DD') >= TO_DATE($${idx++}, 'YYYY-MM-DD')`);
    params.push(body.confirm_date_start);
  }
  if (body.confirm_date_end) {
    conditions.push(`TO_DATE(SPC.confirm_date, 'YYYY-MM-DD') <= TO_DATE($${idx++}, 'YYYY-MM-DD')`);
    params.push(body.confirm_date_end);
  }

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

  const sql = `
    SELECT PM.objid::text AS "OBJID",
      PM.eo_no AS "EO_NO",
      COALESCE(CM.project_no, CM.contract_no, '') AS "PROJECT_NO",
      CM.customer_project_name AS "CUSTOMER_PROJECT_NAME",
      (SELECT part_no || ' ' || part_name FROM part_mng SP WHERE SP.objid::text = PM.parent_part_objid LIMIT 1) AS "PARENT_PART_INFO",
      PM.part_no AS "PART_NO",
      PM.part_name AS "PART_NAME",
      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 AS "CHANGE_TYPE",
      (SELECT code_name FROM comm_code WHERE code_id = PM.change_type LIMIT 1) AS "CHANGE_TYPE_NAME",
      PM.change_option AS "CHANGE_OPTION",
      (SELECT code_name FROM comm_code WHERE code_id = PM.change_option LIMIT 1) AS "CHANGE_OPTION_NAME",
      PM.revision AS "REVISION",
      PM.eo_date AS "EO_DATE",
      PM.part_type AS "PART_TYPE",
      (SELECT code_name FROM comm_code WHERE code_id = PM.part_type LIMIT 1) AS "PART_TYPE_NAME",
      PM.writer AS "WRITER",
      COALESCE(
        (SELECT user_name FROM user_info WHERE user_id = PM.writer LIMIT 1),
        (SELECT user_name FROM user_info_history WHERE user_id = PM.writer LIMIT 1),
        PM.writer
      ) AS "WRITER_NAME",
      TO_CHAR(PM.reg_date, 'YYYY-MM-DD') AS "HIS_REG_DATE_TITLE",
      PM.bom_report_objid AS "BOM_REPORT_OBJID",
      PM.qty_child_objid AS "QTY_CHILD_OBJID",
      SPC.objid::text AS "SPC_OBJID",
      SPC.confirm_date AS "CONFIRM_DATE",
      SPC.writer AS "WRITER1",
      COALESCE(
        (SELECT user_name FROM user_info WHERE user_id = SPC.writer LIMIT 1),
        (SELECT user_name FROM user_info_history WHERE user_id = SPC.writer LIMIT 1),
        SPC.writer
      ) AS "WRITER1_NAME",
      SPC.act_cd AS "ACT_CD",
      (SELECT code_name FROM comm_code WHERE code_id = SPC.act_cd LIMIT 1) AS "ACT_NAME",
      SPC.purchase_order_master_objid AS "PURCHASE_ORDER_MASTER_OBJID",
      POM.purchase_order_no AS "PURCHASE_ORDER_NO",
      COALESCE(SPC.act_status, '0001063') AS "ACT_STATUS",
      (SELECT code_name FROM comm_code WHERE code_id = COALESCE(SPC.act_status, '0001063') LIMIT 1) AS "ACT_STATUS_NAME"
    FROM part_mng_history PM
      LEFT OUTER JOIN sales_part_chg SPC
        ON PM.objid::text = SPC.part_objid
        AND COALESCE(PM.qty_child_objid,'') = COALESCE(SPC.qty_child_objid,'')
      LEFT OUTER JOIN purchase_order_master POM ON SPC.purchase_order_master_objid = POM.objid
      LEFT OUTER JOIN project_mgmt CM ON PM.contract_objid = CM.objid
    WHERE ${where}
    ORDER BY COALESCE(PM.edit_date, PM.reg_date) DESC, PM.part_no
  `;

  try {
    const rows = await queryRows(sql, params);
    return NextResponse.json({ RESULTLIST: rows, TOTAL_CNT: rows.length });
  } catch (e) {
    console.error("purchase/design-change list:", e);
    return NextResponse.json({ success: false, message: "조회 중 오류가 발생했습니다." }, { status: 500 });
  }
}
