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

// 자재관리 > 불출의뢰서 (inventoryMng.materialRequesrGridtList 대응)
// 원본: /Users/jhj/FITO/src/com/pms/mapper/inventoryMng.xml (line 1957)
export async function POST(request: NextRequest) {
  const user = await getSession();
  if (!user) return NextResponse.json({ success: false }, { status: 401 });

  const body = await request.json();

  // 내부 서브쿼리 조건 (INVENTORY_MGMT_OUT_MASTER 기준)
  const innerConds: string[] = ["1=1"];
  const innerParams: unknown[] = [];
  let idx = 1;

  if (body.Year) {
    innerConds.push(`TO_CHAR(IM.regdate, 'YYYY') = $${idx++}`);
    innerParams.push(body.Year);
  }
  if (body.project_no) {
    innerConds.push(`IM.contract_mgmt_objid = $${idx++}`);
    innerParams.push(body.project_no);
  }
  if (body.request_user) {
    innerConds.push(`IM.writer = $${idx++}`);
    innerParams.push(body.request_user);
  }
  if (body.reception_user) {
    innerConds.push(`IM.reception_id = $${idx++}`);
    innerParams.push(body.reception_user);
  }
  if (body.reception_status) {
    innerConds.push(`IM.reception_status = $${idx++}`);
    innerParams.push(body.reception_status);
  }
  if (body.out_status) {
    innerConds.push(
      `(CASE WHEN IM.outstatus = 'complete' THEN 'complete' ELSE 'NG' END) = $${idx++}`,
    );
    innerParams.push(body.out_status);
  }
  if (body.request_start_date) {
    innerConds.push(`TO_DATE(IM.request_date, 'YYYY-MM-DD') >= TO_DATE($${idx++}, 'YYYY-MM-DD')`);
    innerParams.push(body.request_start_date);
  }
  if (body.request_end_date) {
    innerConds.push(`TO_DATE(IM.request_date, 'YYYY-MM-DD') <= TO_DATE($${idx++}, 'YYYY-MM-DD')`);
    innerParams.push(body.request_end_date);
  }
  if (body.reception_start_date) {
    innerConds.push(
      `TO_DATE(IM.reception_date, 'YYYY-MM-DD') >= TO_DATE($${idx++}, 'YYYY-MM-DD')`,
    );
    innerParams.push(body.reception_start_date);
  }
  if (body.reception_end_date) {
    innerConds.push(
      `TO_DATE(IM.reception_date, 'YYYY-MM-DD') <= TO_DATE($${idx++}, 'YYYY-MM-DD')`,
    );
    innerParams.push(body.reception_end_date);
  }

  // 외부 조건 (PART_NO_ARR/PART_NAME_ARR — 서브쿼리 결과에 적용)
  const outerConds: string[] = ["1=1"];
  const outerParams: unknown[] = [];

  if (body.part_no) {
    outerConds.push(`UPPER(PART_NO_ARR) LIKE '%' || TRIM(UPPER($${idx++})) || '%'`);
    outerParams.push(body.part_no);
  }
  if (body.part_name) {
    outerConds.push(`UPPER(PART_NAME_ARR) LIKE '%' || TRIM(UPPER($${idx++})) || '%'`);
    outerParams.push(body.part_name);
  }

  const params = [...innerParams, ...outerParams];

  const rows = await queryRows(
    `SELECT IM.*
      FROM (
        SELECT
          IM.objid::text AS "OBJID",
          (SELECT STRING_AGG(part_no, ',') FROM part_mng O
            WHERE O.objid IN (
              SELECT part_objid FROM inventory_mgmt O1
              WHERE O1.objid IN (
                SELECT parent_objid FROM inventory_mgmt_out O2
                WHERE O2.inventory_request_master_objid = IM.objid
              )
            )) AS "PART_NO_ARR",
          (SELECT STRING_AGG(part_name, ',') FROM part_mng O
            WHERE O.objid IN (
              SELECT part_objid FROM inventory_mgmt O1
              WHERE O1.objid IN (
                SELECT parent_objid FROM inventory_mgmt_out O2
                WHERE O2.inventory_request_master_objid = IM.objid
              )
            )) AS "PART_NAME_ARR",
          IM.parent_objid AS "PARENT_OBJID",
          IM.inventory_out_no AS "INVENTORY_OUT_NO",
          IM.request_date AS "REQUEST_DATE",
          IM.request_id AS "REQUEST_ID",
          IM.reception_status AS "RECEPTION_STATUS",
          CASE WHEN IM.reception_status = 'reception' THEN '접수' ELSE '미접수' END
            AS "RECEPTION_STATUS_TITLE",
          IM.reception_id AS "RECEPTION_ID",
          (SELECT user_name FROM user_info O WHERE O.user_id = IM.reception_id)
            AS "RECEPTION_USER_NAME",
          IM.reception_date AS "RECEPTION_DATE",
          IM.outstatus AS "OUTSTATUS",
          CASE WHEN IM.outstatus = 'complete' THEN '완료' ELSE '미완료' END
            AS "OUTSTATUS_TITLE",
          IM.writer AS "WRITER",
          (SELECT user_name FROM user_info O WHERE O.user_id = IM.writer) AS "REQUEST_USER_NAME",
          IM.regdate AS "REGDATE",
          TO_CHAR(IM.regdate, 'YYYY-MM-DD') AS "REG_DATE",
          IMO.request_qty AS "REQUEST_QTY",
          IMO.out_qty AS "OUT_QTY",
          IMO.inventory_request_master_objid AS "INVENTORY_REQUEST_MASTER_OBJID"
        FROM inventory_mgmt_out_master IM
        LEFT OUTER JOIN (
          SELECT
            inventory_request_master_objid,
            SUM(COALESCE(NULLIF(request_qty, '')::numeric, 0)) AS request_qty,
            SUM(COALESCE(NULLIF(out_qty, '')::numeric, 0)) AS out_qty
          FROM inventory_mgmt_out
          GROUP BY inventory_request_master_objid
        ) IMO ON IMO.inventory_request_master_objid = IM.objid
        WHERE ${innerConds.join(" AND ")}
      ) IM
     WHERE ${outerConds.join(" AND ")}
     ORDER BY "REGDATE" DESC
`,
    params,
  );

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