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

// 자재관리 > 자재리스트 (inventoryMng.getInventoryMngGridList 대응)
// 원본: /Users/jhj/FITO/src/com/pms/mapper/inventoryMng.xml (line 790)
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"];
  const params: unknown[] = [];
  let idx = 1;

  // project_nos: comma-delimited (다중 선택)
  const projectNos = String(body.project_nos || "").trim();
  if (projectNos) {
    const arr = projectNos.split(",").map((s) => s.trim()).filter(Boolean);
    if (arr.length) {
      const placeholders = arr.map(() => `$${idx++}`).join(",");
      conditions.push(`IM.contract_objid IN (${placeholders})`);
      params.push(...arr);
    }
  }
  if (body.unit_code) {
    conditions.push(`IM.unit = $${idx++}`);
    params.push(body.unit_code);
  }
  if (body.part_no) {
    conditions.push(`UPPER(P.part_no) LIKE UPPER('%' || $${idx++} || '%')`);
    params.push(body.part_no);
  }
  if (body.part_name) {
    conditions.push(`UPPER(P.part_name) LIKE UPPER('%' || $${idx++} || '%')`);
    params.push(body.part_name);
  }
  if (body.spec) {
    conditions.push(`UPPER(P.spec) LIKE UPPER('%' || $${idx++} || '%')`);
    params.push(body.spec);
  }
  if (body.part_type) {
    conditions.push(`P.part_type = $${idx++}`);
    params.push(body.part_type);
  }
  if (body.cls_cd) {
    conditions.push(`IM.cls_cd = $${idx++}`);
    params.push(body.cls_cd);
  }
  if (body.cau_cd) {
    conditions.push(`IM.cau_cd = $${idx++}`);
    params.push(body.cau_cd);
  }
  if (body.location) {
    conditions.push(`IM.location = $${idx++}`);
    params.push(body.location);
  }
  if (body.writer) {
    conditions.push(`IM.writer = $${idx++}`);
    params.push(body.writer);
  }

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

  const rows = await queryRows(
    `SELECT
        IM.OBJID::text AS "OBJID",
        IM.PART_OBJID AS "PART_OBJID",
        IM.CONTRACT_OBJID AS "CONTRACT_OBJID",
        IM.UNIT AS "UNIT",
        IM.CLS_CD AS "CLS_CD",
        IM.CAU_CD AS "CAU_CD",
        IM.QTY AS "QTY",
        IM.LOCATION AS "LOCATION",
        IM.REG_DATE AS "REG_DATE",
        IM.PRICE AS "PRICE",
        IM.WRITER AS "WRITER",
        IM.PART_NO AS "PART_NO",
        IM.PART_NAME AS "PART_NAME",
        IM.SPEC AS "SPEC",
        IM.MAKER AS "MAKER",
        IM.PART_TYPE_NAME AS "PART_TYPE_NAME",
        IM.PART_TYPE AS "PART_TYPE",
        IM.MATERIAL AS "MATERIAL",
        IM.WRITER_NAME AS "WRITER_NAME",
        (SELECT project_no FROM project_mgmt O WHERE O.objid = IM.CONTRACT_OBJID) AS "PROJECT_NO",
        (
          COALESCE((
            SELECT SUM(CASE WHEN (O.receipt_qty IS NULL OR O.receipt_qty = '')
                            THEN 0
                            ELSE (O.receipt_qty::numeric - COALESCE(O.move_qty::numeric, 0))
                       END)
            FROM inventory_mgmt_in O WHERE O.parent_objid = IM.OBJID
          ), 0)
          - COALESCE((
            SELECT SUM(CASE WHEN (O.request_qty IS NULL OR O.request_qty = '')
                            THEN 0
                            ELSE O.request_qty::numeric
                       END)
            FROM inventory_mgmt_out O WHERE O.parent_objid = IM.OBJID
          ), 0)
        ) AS "USE_CNT",
        (
          COALESCE((
            SELECT SUM(CASE WHEN (O.receipt_qty IS NULL OR O.receipt_qty = '')
                            THEN 0
                            ELSE (O.receipt_qty::numeric - COALESCE(O.move_qty::numeric, 0))
                       END)
            FROM inventory_mgmt_in O, inventory_mgmt S_IM
            WHERE S_IM.part_objid = IM.PART_OBJID
              AND O.parent_objid = S_IM.objid
          ), 0)
          - COALESCE((
            SELECT SUM(CASE WHEN (O.request_qty IS NULL OR O.request_qty = '')
                            THEN 0
                            ELSE O.request_qty::numeric
                       END)
            FROM inventory_mgmt_out O, inventory_mgmt S_IM
            WHERE S_IM.part_objid = IM.PART_OBJID
              AND O.parent_objid = S_IM.objid
          ), 0)
        ) AS "USE_CNT_ALL",
        COALESCE((
          SELECT SUM(CASE WHEN (O.out_qty IS NULL OR O.out_qty = '')
                          THEN 0
                          ELSE O.out_qty::numeric
                     END)
          FROM inventory_mgmt_out O WHERE O.parent_objid = IM.OBJID
        ), 0) AS "REQUEST_QTY",
        (SELECT O.unit_no || '-' || O.task_name FROM pms_wbs_task O WHERE O.objid = IM.UNIT) AS "UNIT_NAME",
        (
          SELECT ARRAY_TO_STRING(ARRAY_AGG(DISTINCT (SELECT code_name FROM comm_code CC WHERE CC.code_id = IMI.location)), ',')
          FROM inventory_mgmt_in IMI WHERE IMI.parent_objid = IM.OBJID
        ) AS "LOCATION_NAME",
        (
          SELECT MAX(O.remark) FROM inventory_mgmt_out_master O
          WHERE O.objid IN (
            SELECT T1.inventory_request_master_objid FROM inventory_mgmt_out T1
            WHERE T1.parent_objid = IM.OBJID ORDER BY T1.regdate DESC LIMIT 1
          )
        ) AS "REMARK",
        (SELECT code_name FROM comm_code WHERE code_id = IM.CLS_CD) AS "CLS_CD_NAME",
        (SELECT code_name FROM comm_code WHERE code_id = IM.CAU_CD) AS "CAU_CD_NAME"
     FROM (
        SELECT
          IM.objid AS OBJID,
          IM.part_objid AS PART_OBJID,
          P.part_no AS PART_NO,
          P.part_name AS PART_NAME,
          P.spec AS SPEC,
          P.maker AS MAKER,
          (SELECT code_name FROM comm_code WHERE code_id = P.part_type) AS PART_TYPE_NAME,
          P.part_type AS PART_TYPE,
          P.material AS MATERIAL,
          IM.cls_cd AS CLS_CD,
          IM.cau_cd AS CAU_CD,
          IM.qty AS QTY,
          IM.location AS LOCATION,
          IM.reg_date AS REG_DATE,
          IM.price AS PRICE,
          IM.writer AS WRITER,
          (SELECT user_name FROM user_info WHERE user_id = IM.writer) AS WRITER_NAME,
          IM.contract_objid AS CONTRACT_OBJID,
          IM.unit AS UNIT
        FROM inventory_mgmt IM
          INNER JOIN part_mng P ON P.objid::varchar = IM.part_objid
        WHERE ${where}
      ) IM
     ORDER BY IM.REG_DATE DESC NULLS LAST
`,
    params,
  );

  return NextResponse.json({ RESULTLIST: rows, TOTAL_CNT: rows.length });
}
