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

// 제품관리_설계변경 리스트 (원본: partMng.partMngHistList)
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[] = [
    "1=1",
    `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(String(body.Year));
  }
  if (body.contract_objid) {
    conditions.push(`CM.objid = $${idx++}`);
    params.push(body.contract_objid);
  }
  if (body.unit_code) {
    conditions.push(`B.unit_code = $${idx++}`);
    params.push(body.unit_code);
  }
  if (body.part_no) {
    conditions.push(`UPPER(PM.part_no) LIKE UPPER('%' || $${idx++} || '%')`);
    params.push(body.part_no);
  }
  if (body.part_name) {
    conditions.push(`UPPER(PM.part_name) LIKE UPPER('%' || $${idx++} || '%')`);
    params.push(body.part_name);
  }
  if (body.change_option) {
    conditions.push(`PM.change_option = $${idx++}`);
    params.push(body.change_option);
  }
  if (body.eo_start_date) {
    conditions.push(`TO_DATE(PM.eo_date, 'YYYY-MM-DD') >= TO_DATE($${idx++}, 'YYYY-MM-DD')`);
    params.push(body.eo_start_date);
  }
  if (body.eo_end_date) {
    conditions.push(`TO_DATE(PM.eo_date, 'YYYY-MM-DD') <= TO_DATE($${idx++}, 'YYYY-MM-DD')`);
    params.push(body.eo_end_date);
  }
  if (body.change_type) {
    conditions.push(`PM.change_type = $${idx++}`);
    params.push(body.change_type);
  }
  if (body.part_type) {
    conditions.push(`PM.part_type = $${idx++}`);
    params.push(body.part_type);
  }
  if (body.writer_id) {
    conditions.push(`PM.writer = $${idx++}`);
    params.push(body.writer_id);
  }

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

  const sql = `
    SELECT PM.objid::text AS "OBJID",
      PM.eo_no AS "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 part_no || ' ' || part_name FROM part_mng SP WHERE SP.objid = PM.parent_part_no) AS "PARENT_PART_INFO",
      CASE WHEN PM.change_option = '0001790' THEN PM.part_no || '->' || PM.chg_part_no ELSE PM.part_no END AS "PART_NO",
      CASE WHEN PM.change_option = '0001790'
           THEN PM.part_name || '->' || (SELECT part_name FROM part_mng P WHERE P.objid = PM.chg_part_objid::varchar)
           ELSE PM.part_name END AS "PART_NAME",
      PM.bom_qty_status AS "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 AS "CHANGE_TYPE",
      (SELECT code_name FROM comm_code WHERE code_id = PM.change_type) AS "CHANGE_TYPE_NAME",
      PM.change_option AS "CHANGE_OPTION",
      (SELECT code_name FROM comm_code WHERE code_id = PM.change_option) AS "CHANGE_OPTION_NAME",
      CASE WHEN PM.change_option = '0001790' THEN PM.revision || '->' || PM.chg_part_rev ELSE PM.revision END 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) AS "PART_TYPE_NAME",
      PM.writer AS "WRITER",
      (SELECT user_name FROM user_info WHERE user_id = PM.writer) AS "WRITER_NAME",
      WTS.unit_no || '-' || WTS.task_name AS "UNIT_NAME",
      TO_CHAR(PM.his_reg_date, 'YYYY-MM-DD') AS "HIS_REG_DATE_TITLE"
    FROM part_mng_history PM
      LEFT OUTER JOIN project_mgmt CM ON PM.contract_objid = CM.objid
      LEFT OUTER JOIN part_bom_report B ON PM.bom_report_objid = B.objid
      LEFT OUTER JOIN project_mgmt CM2 ON B.contract_objid = CM2.objid
      LEFT OUTER JOIN pms_wbs_task WTS ON B.unit_code = WTS.objid
    WHERE ${where}
    ORDER BY COALESCE(PM.his_reg_date, PM.reg_date) DESC, PM.part_no
  `;

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