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

// 원본: /salesMng/salesRequestMngRegList.do — sales_request_master + sales_request_part
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(`TO_CHAR(SRM.regdate, 'YYYY') = $${idx++}`);
    params.push(body.year);
  }
  if (body.project_no) {
    conditions.push(
      `SRM.project_no IN (SELECT objid FROM project_mgmt WHERE COALESCE(project_no, contract_no) LIKE '%' || $${idx++} || '%')`
    );
    params.push(body.project_no);
  }
  if (body.request_cd) {
    conditions.push(`SRM.request_cd = $${idx++}`);
    params.push(body.request_cd);
  }
  if (body.receipt_writer) {
    conditions.push(`SRM.receipt_user_id = $${idx++}`);
    params.push(body.receipt_writer);
  }
  if (body.status) {
    conditions.push(`SRM.status = $${idx++}`);
    params.push(body.status);
  }
  if (body.receipt_date_start) {
    conditions.push(`TO_DATE(SRM.receipt_date,'YYYY-MM-DD') >= TO_DATE($${idx++}, 'YYYY-MM-DD')`);
    params.push(body.receipt_date_start);
  }
  if (body.receipt_date_end) {
    conditions.push(`TO_DATE(SRM.receipt_date,'YYYY-MM-DD') <= TO_DATE($${idx++}, 'YYYY-MM-DD')`);
    params.push(body.receipt_date_end);
  }

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

  const sql = `
    SELECT SRM.objid::text AS "OBJID",
      SRM.request_mng_no AS "REQUEST_MNG_NO",
      SRM.request_cd AS "REQUEST_CD",
      (SELECT code_name FROM comm_code WHERE code_id = SRM.request_cd LIMIT 1) AS "REQUEST_CD_NAME",
      SRM.project_no AS "PROJECT_OBJID",
      (SELECT COALESCE(project_no, contract_no) FROM project_mgmt WHERE objid = SRM.project_no LIMIT 1) AS "PROJECT_NUMBER",
      (SELECT customer_project_name FROM project_mgmt WHERE objid = SRM.project_no LIMIT 1) AS "PROJECT_NAME",
      SRM.unit_name AS "UNIT_CODE",
      (SELECT COALESCE(O.unit_no,'') || '-' || COALESCE(O.task_name,'') FROM pms_wbs_task O WHERE O.objid = SRM.unit_name LIMIT 1) AS "UNIT_CODE_NAME",
      SRM.request_reasons AS "REQUEST_REASONS",
      (SELECT code_name FROM comm_code WHERE code_id = SRM.request_reasons LIMIT 1) AS "REQUEST_REASONS_NAME",
      SRM.request_user_id AS "REQUEST_USER_ID",
      COALESCE(
        (SELECT user_name FROM user_info WHERE user_id = SRM.request_user_id LIMIT 1),
        (SELECT user_name FROM user_info_history WHERE user_id = SRM.request_user_id LIMIT 1),
        SRM.request_user_id
      ) AS "REQUEST_USER_NAME",
      SRM.delivery_request_date AS "DELIVERY_REQUEST_DATE",
      SRM.status AS "STATUS",
      CASE
        WHEN ((SELECT COUNT(1) FROM purchase_order_master P WHERE P.sales_request_objid = SRM.objid)
              >= (SELECT COUNT(1) FROM sales_request_part SRP WHERE SRP.sales_request_master_objid = SRM.objid))
             AND (SELECT COUNT(1) FROM purchase_order_master P WHERE P.sales_request_objid = SRM.objid) > 0
          THEN '발주완료'
        WHEN (SELECT COUNT(1) FROM purchase_order_master P WHERE P.sales_request_objid = SRM.objid) > 0
          THEN '발주부분완료'
        WHEN SRM.status = 'create' THEN '등록'
        WHEN SRM.status = 'release' THEN '제출 완료'
        WHEN SRM.status = 'reception' THEN '접수'
        WHEN SRM.status = 'approvalRequest' THEN '결재중'
        WHEN SRM.status = 'approvalComplete' THEN '결재완료'
        WHEN SRM.status = 'reject' THEN '반려'
        ELSE ''
      END AS "STATUS_TITLE",
      SRM.receipt_user_id AS "RECEIPT_USER_ID",
      COALESCE(
        (SELECT user_name FROM user_info WHERE user_id = SRM.receipt_user_id LIMIT 1),
        (SELECT user_name FROM user_info_history WHERE user_id = SRM.receipt_user_id LIMIT 1),
        SRM.receipt_user_id
      ) AS "RECEIPT_USER_NAME",
      SRM.receipt_date AS "RECEIPT_DATE",
      TO_CHAR(SRM.regdate, 'YYYY-MM-DD') AS "REGDATE_TITLE",
      SRP.TOTAL_QTY AS "TOTAL_QTY",
      SRP.ITEMS_QTY AS "ITEMS_QTY",
      (SELECT STRING_AGG(purchase_order_no, ',')
         FROM purchase_order_master P WHERE P.sales_request_objid = SRM.objid) AS "PURCHASE_ORDER_NO_ARR"
    FROM sales_request_master SRM
      LEFT OUTER JOIN (
        SELECT sales_request_master_objid,
          SUM(COALESCE(qty,'0')::numeric) AS TOTAL_QTY,
          COUNT(1) AS ITEMS_QTY
        FROM sales_request_part
        GROUP BY sales_request_master_objid
      ) SRP ON SRP.sales_request_master_objid = SRM.objid
    WHERE ${where}
    ORDER BY SRM.regdate DESC
  `;

  try {
    const rows = await queryRows(sql, params);
    return NextResponse.json({ RESULTLIST: rows, TOTAL_CNT: rows.length });
  } catch (e) {
    console.error("purchase/request list:", e);
    return NextResponse.json({ success: false, message: "조회 중 오류가 발생했습니다." }, { status: 500 });
  }
}
