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

// 원본: purchaseOrder.xml#deliveryMngList_new
// 입고관리 > 입고결과등록 목록
// - 결재완료 발주서만 (status = 'approvalComplete')
// - 동시발주 마스터 + 일반 건만 (슬레이브 제외)
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[] = [
    "POM.status = 'approvalComplete'",
    "(COALESCE(POM.multi_master_yn,'') = 'Y' OR (COALESCE(POM.multi_master_yn,'') != 'Y' AND COALESCE(POM.multi_yn,'') != 'Y'))",
  ];
  const params: unknown[] = [];
  let idx = 1;

  // Year: 최종입고일(MAX receipt_date) 기준 — 원본과 동일
  if (body.year) {
    conditions.push(
      `TO_CHAR(TO_DATE(S1.cur_delivery_date, 'YYYY-MM-DD'), 'YYYY') = $${idx++}`,
    );
    params.push(body.year);
  }
  if (body.customer_cd) {
    conditions.push(`CM.customer_objid::text = $${idx++}`);
    params.push(body.customer_cd);
  }
  if (body.customer_project_name) {
    conditions.push(
      `TRIM(UPPER(CM.customer_project_name)) LIKE '%' || TRIM(UPPER($${idx++})) || '%'`,
    );
    params.push(body.customer_project_name);
  }
  // project_nos: 복수 objid (array 또는 CSV 문자열)
  const projectNos: string[] = Array.isArray(body.project_nos)
    ? body.project_nos.filter(Boolean).map(String)
    : body.project_nos
      ? String(body.project_nos).split(",").filter(Boolean)
      : [];
  if (projectNos.length > 0) {
    const placeholders = projectNos.map(() => `$${idx++}`).join(", ");
    conditions.push(`CM.objid::text IN (${placeholders})`);
    params.push(...projectNos);
  }
  if (body.unit_code) {
    conditions.push(`POM.unit_code = $${idx++}`);
    params.push(body.unit_code);
  }
  if (body.po_client_id) {
    conditions.push(`POM.po_client_id = $${idx++}`);
    params.push(body.po_client_id);
  }
  if (body.purchase_order_no) {
    conditions.push(
      `TRIM(UPPER(POM.purchase_order_no)) LIKE '%' || TRIM(UPPER($${idx++})) || '%'`,
    );
    params.push(body.purchase_order_no);
  }
  if (body.type) {
    conditions.push(`POM.type = $${idx++}`);
    params.push(body.type);
  }
  if (body.delivery_start_date) {
    conditions.push(
      `TO_DATE(POM.delivery_date, 'YYYY-MM-DD') >= TO_DATE($${idx++}, 'YYYY-MM-DD')`,
    );
    params.push(body.delivery_start_date);
  }
  if (body.delivery_end_date) {
    conditions.push(
      `TO_DATE(POM.delivery_date, 'YYYY-MM-DD') <= TO_DATE($${idx++}, 'YYYY-MM-DD')`,
    );
    params.push(body.delivery_end_date);
  }
  if (body.partner_objid) {
    conditions.push(`POM.partner_objid = $${idx++}`);
    params.push(body.partner_objid);
  }
  // sales_mng_user_ids: 복수 user_id
  const userIds: string[] = Array.isArray(body.sales_mng_user_ids)
    ? body.sales_mng_user_ids.filter(Boolean).map(String)
    : body.sales_mng_user_ids
      ? String(body.sales_mng_user_ids).split(",").filter(Boolean)
      : [];
  if (userIds.length > 0) {
    const placeholders = userIds.map(() => `$${idx++}`).join(", ");
    conditions.push(`POM.sales_mng_user_id IN (${placeholders})`);
    params.push(...userIds);
  }
  if (body.reg_start_date) {
    conditions.push(`TO_CHAR(POM.regdate, 'YYYY-MM-DD') >= $${idx++}`);
    params.push(body.reg_start_date);
  }
  if (body.reg_end_date) {
    conditions.push(`TO_CHAR(POM.regdate, 'YYYY-MM-DD') <= $${idx++}`);
    params.push(body.reg_end_date);
  }
  if (body.delivery_status) {
    conditions.push(
      `(CASE WHEN 0 >= (COALESCE((SELECT SUM(COALESCE(O.real_order_qty,'0')::numeric) FROM purchase_order_part O WHERE POM.objid::text = O.purchase_order_master_objid), 0) - COALESCE(S1.total_delivery_qty, 0)) THEN '입고완료'
              WHEN TO_CHAR(NOW(), 'YYYY-MM-DD') > POM.delivery_date THEN '지연'
              ELSE '입고중' END) = $${idx++}`,
    );
    params.push(body.delivery_status);
  }
  if (body.SEARCH_PART_NO) {
    conditions.push(
      `EXISTS (SELECT 1 FROM purchase_order_part POP WHERE POP.purchase_order_master_objid = POM.objid::text
               AND TRIM(UPPER(COALESCE(POP.part_no,''))) LIKE '%' || TRIM(UPPER($${idx++})) || '%')`,
    );
    params.push(body.SEARCH_PART_NO);
  }
  if (body.SEARCH_PART_NAME) {
    conditions.push(
      `EXISTS (SELECT 1 FROM purchase_order_part POP WHERE POP.purchase_order_master_objid = POM.objid::text
               AND TRIM(UPPER(COALESCE(POP.part_name,''))) LIKE '%' || TRIM(UPPER($${idx++})) || '%')`,
    );
    params.push(body.SEARCH_PART_NAME);
  }
  if (body.SEARCH_PART_SPEC) {
    conditions.push(
      `EXISTS (SELECT 1 FROM purchase_order_part POP WHERE POP.purchase_order_master_objid = POM.objid::text
               AND TRIM(UPPER(COALESCE(POP.spec,''))) LIKE '%' || TRIM(UPPER($${idx++})) || '%')`,
    );
    params.push(body.SEARCH_PART_SPEC);
  }

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

  const sql = `
    SELECT POM.objid::text AS "OBJID",
      TO_CHAR(POM.regdate, 'YYYY') AS "POM_YEAR",
      (SELECT supply_name FROM supply_mng SM WHERE SM.objid::text = CM.customer_objid LIMIT 1) AS "CUSTOMER_NAME",
      CM.customer_project_name AS "CUSTOMER_PROJECT_NAME",
      (SELECT COALESCE(O.unit_no,'') || '-' || COALESCE(O.task_name,'') FROM pms_wbs_task O WHERE O.objid = POM.unit_code LIMIT 1) AS "UNIT_NAME",
      COALESCE(CM.project_no, CM.contract_no) AS "PROJECT_NO",
      POM.purchase_order_no AS "PURCHASE_ORDER_NO",
      POM.title AS "TITLE",
      POM.delivery_place AS "DELIVERY_PLACE",
      (SELECT code_name FROM comm_code WHERE code_id = POM.delivery_place LIMIT 1) AS "DELIVERY_PLACE_NAME",
      POM.inspect_method AS "INSPECT_METHOD",
      (SELECT code_name FROM comm_code WHERE code_id = POM.inspect_method LIMIT 1) AS "INSPECT_METHOD_NAME",
      POM.payment_terms AS "PAYMENT_TERMS",
      (SELECT code_name FROM comm_code WHERE code_id = POM.payment_terms LIMIT 1) AS "PAYMENT_TERMS_NAME",
      POM.delivery_date AS "DELIVERY_DATE",
      POM.partner_objid AS "PARTNER_OBJID",
      (SELECT supply_name FROM admin_supply_mng WHERE objid::text = POM.partner_objid LIMIT 1) AS "PARTNER_NAME",
      POM.sales_mng_user_id AS "SALES_MNG_USER_ID",
      (SELECT user_name FROM user_info WHERE user_id = POM.sales_mng_user_id LIMIT 1) AS "SALES_MNG_USER_NAME",
      TO_CHAR(POM.regdate, 'YYYY-MM-DD') AS "REGDATE",
      POM.total_price AS "TOTAL_PRICE",
      POM.discount_price AS "DISCOUNT_PRICE",
      POM.total_supply_unit_price AS "TOTAL_SUPPLY_UNIT_PRICE",
      POM.nego_rate AS "NEGO_RATE",
      COALESCE(POM.multi_master_yn,'') AS "MULTI_MASTER_YN",
      COALESCE(POM.multi_yn,'') AS "MULTI_YN",
      CASE WHEN COALESCE(POM.multi_master_yn,'') = 'Y' THEN '' ELSE COALESCE(POM.multi_yn,'') END AS "MULTI_YN_MAKED",
      COALESCE((SELECT SUM(COALESCE(O.real_order_qty,'0')::numeric) FROM purchase_order_part O WHERE POM.objid::text = O.purchase_order_master_objid), 0) AS "TOTAL_PO_QTY",
      S1.cur_delivery_date AS "CUR_DELIVERY_DATE",
      COALESCE(S1.total_delivery_qty, 0) AS "TOTAL_DELIVERY_QTY",
      (COALESCE((SELECT SUM(COALESCE(O.real_order_qty,'0')::numeric) FROM purchase_order_part O WHERE POM.objid::text = O.purchase_order_master_objid), 0)
        - COALESCE(S1.total_delivery_qty, 0)) AS "NON_DELIVERY_QTY",
      (CASE
        WHEN 0 >= (COALESCE((SELECT SUM(COALESCE(O.real_order_qty,'0')::numeric) FROM purchase_order_part O WHERE POM.objid::text = O.purchase_order_master_objid), 0) - COALESCE(S1.total_delivery_qty, 0))
          THEN '입고완료'
        WHEN TO_CHAR(NOW(), 'YYYY-MM-DD') > POM.delivery_date THEN '지연'
        ELSE '입고중'
      END) AS "DELIVERY_STATUS",
      POM.type AS "TYPE",
      (SELECT code_name FROM comm_code WHERE code_id = POM.type LIMIT 1) AS "TYPE_NAME",
      POM.order_type_cd AS "ORDER_TYPE_CD",
      (SELECT code_name FROM comm_code WHERE code_id = POM.order_type_cd LIMIT 1) AS "ORDER_TYPE_CD_NAME",
      (SELECT UI.user_name FROM arrival_plan AP JOIN user_info UI ON UI.user_id = AP.receiver_id
         WHERE AP.parent_objid = POM.objid::text AND AP.receipt_date IS NOT NULL AND AP.receipt_date <> ''
         ORDER BY AP.receipt_date DESC LIMIT 1) AS "CUR_RECEIVER_NAME"
    FROM purchase_order_master POM
    LEFT OUTER JOIN (
      SELECT POP.purchase_order_master_objid,
             MAX(DH.receipt_date) AS cur_delivery_date,
             SUM(COALESCE(DH.receipt_qty,'0')::numeric) AS total_delivery_qty
        FROM purchase_order_part POP
        LEFT OUTER JOIN arrival_plan DH ON POP.objid::text = DH.order_part_objid
       GROUP BY POP.purchase_order_master_objid
    ) S1 ON POM.objid::text = S1.purchase_order_master_objid
    LEFT OUTER JOIN project_mgmt CM ON POM.contract_mgmt_objid = CM.objid
    WHERE ${where}
    ORDER BY POM.regdate DESC
  `;

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