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

// 자재관리 > 현황 (inventoryMngDashList 대응)
// 프로젝트별 입고/불출/잔여/이동 수량 집계
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 LIKE '%' || $${idx++} || '%'`);
    params.push(body.project_no);
  }
  if (body.location_cd) {
    conditions.push(`EXISTS (SELECT 1 FROM inventory_mgmt_in I_F WHERE I_F.parent_objid = IM.objid AND I_F.location = $${idx++})`);
    params.push(body.location_cd);
  }
  if (body.part_type_cd) {
    conditions.push(`P.part_type = $${idx++}`);
    params.push(body.part_type_cd);
  }

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

  const rows = await queryRows(
    `SELECT CM.contract_no AS "PROJECT_NO",
       IM.unit AS "UNIT_NAME",
       COALESCE((SELECT code_name FROM comm_code WHERE code_id = P.part_type LIMIT 1), '') AS "PART_TYPE_NAME",
       COALESCE((SELECT ARRAY_TO_STRING(ARRAY_AGG(DISTINCT (SELECT code_name FROM comm_code WHERE code_id = I_L.location LIMIT 1)), ', ')
                 FROM inventory_mgmt_in I_L WHERE I_L.parent_objid = IM.objid), '') AS "LOCATION_NAME",
       -- 총보유(입고) = SUM(receipt_qty - move_qty)
       COALESCE((SELECT SUM(CASE WHEN IMI.receipt_qty IS NULL OR IMI.receipt_qty = '' THEN 0
                                  ELSE IMI.receipt_qty::numeric - COALESCE(NULLIF(IMI.move_qty,'')::numeric, 0) END)
                 FROM inventory_mgmt_in IMI WHERE IMI.parent_objid = IM.objid), 0) AS "TOTAL_QTY",
       -- 불출수량
       COALESCE((SELECT SUM(COALESCE(NULLIF(IMO.request_qty,'')::numeric, 0))
                 FROM inventory_mgmt_out IMO WHERE IMO.parent_objid = IM.objid), 0) AS "REQUEST_QTY",
       -- 잔여 = 입고 - 불출
       (COALESCE((SELECT SUM(CASE WHEN IMI2.receipt_qty IS NULL OR IMI2.receipt_qty = '' THEN 0
                                   ELSE IMI2.receipt_qty::numeric - COALESCE(NULLIF(IMI2.move_qty,'')::numeric, 0) END)
                  FROM inventory_mgmt_in IMI2 WHERE IMI2.parent_objid = IM.objid), 0)
        - COALESCE((SELECT SUM(COALESCE(NULLIF(IMO2.request_qty,'')::numeric, 0))
                    FROM inventory_mgmt_out IMO2 WHERE IMO2.parent_objid = IM.objid), 0)
       ) AS "REMAIN_QTY",
       -- 입고수량 (receipt_qty 합계)
       COALESCE((SELECT SUM(COALESCE(NULLIF(IMI3.receipt_qty,'')::numeric, 0))
                 FROM inventory_mgmt_in IMI3 WHERE IMI3.parent_objid = IM.objid), 0) AS "DELIVERY_QTY",
       -- 이동수량
       COALESCE((SELECT SUM(COALESCE(NULLIF(IMI4.move_qty,'')::numeric, 0))
                 FROM inventory_mgmt_in IMI4 WHERE IMI4.parent_objid = IM.objid), 0) AS "MOVE_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 CM.contract_no, IM.unit
`,
    params
  );

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