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

// inventoryMng/inventoryMngInputGridList.do 대응
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;

  if (body.year) {
    conditions.push(`LEFT(IM.reg_date, 4) = $${idx++}`);
    params.push(body.year);
  }
  if (body.project_no) {
    conditions.push(`CM.contract_no = $${idx++}`);
    params.push(body.project_no);
  }
  if (body.part_no) {
    conditions.push(`P.part_no LIKE '%' || $${idx++} || '%'`);
    params.push(body.part_no);
  }
  if (body.part_name) {
    conditions.push(`P.part_name LIKE '%' || $${idx++} || '%'`);
    params.push(body.part_name);
  }
  if (body.spec) {
    conditions.push(`P.spec LIKE '%' || $${idx++} || '%'`);
    params.push(body.spec);
  }
  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 sql = `
    SELECT IM.objid::text AS "OBJID",
      CM.contract_no AS "PROJECT_NO",
      COALESCE((SELECT code_name FROM comm_code WHERE code_id = POM.unit_code LIMIT 1), IM.unit) AS "UNIT_NAME",
      P.part_no AS "PART_NO", P.part_name AS "PART_NAME", P.spec AS "SPEC", P.maker AS "MAKER",
      COALESCE((SELECT code_name FROM comm_code WHERE code_id = IM.cls_cd LIMIT 1), '') AS "CLS_CD_NAME",
      COALESCE((SELECT code_name FROM comm_code WHERE code_id = IM.cau_cd LIMIT 1), '') AS "CAU_CD_NAME",
      COALESCE(IM.qty::numeric, 0) AS "QTY",
      COALESCE((SELECT code_name FROM comm_code WHERE code_id = IM.location LIMIT 1), '') AS "LOCATION_NAME",
      COALESCE(IM.price::numeric, 0) AS "PRICE",
      IM.reg_date AS "REG_DATE",
      COALESCE((SELECT user_name FROM user_info WHERE user_id = IM.writer LIMIT 1), '') AS "WRITER_NAME",
      -- 총입고수량 = SUM(inventory_mgmt_in.receipt_qty)
      COALESCE((SELECT SUM(COALESCE(NULLIF(IMI.receipt_qty,'')::numeric, 0))
                FROM inventory_mgmt_in IMI WHERE IMI.parent_objid = IM.objid), 0) AS "INPUT_QTY",
      -- 최종입고일
      COALESCE((SELECT MAX(TO_CHAR(IMI2.regdate, 'YYYY-MM-DD'))
                FROM inventory_mgmt_in IMI2 WHERE IMI2.parent_objid = IM.objid), '') AS "INPUT_DATE",
      -- 잔여수량 = qty - SUM(투입이력 input_qty)
      COALESCE(NULLIF(IM.qty,'')::numeric, 0)
        - COALESCE((SELECT SUM(COALESCE(IMH.input_qty::numeric, 0))
                    FROM inventory_mgmt_history IMH WHERE IMH.parent_objid = IM.objid), 0) AS "REMAIN_QTY"
    FROM inventory_mgmt IM
    LEFT JOIN part_mng P ON P.objid = IM.part_objid
    LEFT JOIN contract_mgmt CM ON CM.objid::text = IM.contract_objid
    WHERE ${where}
    ORDER BY IM.reg_date DESC NULLS LAST
  `;

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