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

// 원본: purchaseOrder.xml#purchaseOrderMasterList_new
// 발주관리 > 발주서관리 목록. 동시발주는 마스터 행만 노출(MULTI_MASTER_YN='Y' 또는 동시발주 아님).
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",
    "(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;

  if (body.year) {
    conditions.push(`TO_CHAR(POM.regdate, 'YYYY') = $${idx++}`);
    params.push(body.year);
  }
  if (body.customer_cd) {
    conditions.push(
      `EXISTS (SELECT 1 FROM project_mgmt SP WHERE POM.contract_mgmt_objid = SP.objid AND SP.customer_objid::text = $${idx++})`,
    );
    params.push(body.customer_cd);
  }
  if (body.project_no) {
    // project_no(LIKE) — project_mgmt.project_no 또는 contract_no 부분 일치
    conditions.push(
      `EXISTS (SELECT 1 FROM project_mgmt SP WHERE POM.contract_mgmt_objid = SP.objid
               AND (COALESCE(SP.project_no,'') LIKE '%' || $${idx} || '%'
                    OR COALESCE(SP.contract_no,'') LIKE '%' || $${idx} || '%'))`,
    );
    params.push(body.project_no);
    idx++;
  }
  if (body.customer_project_name) {
    conditions.push(`CM.customer_project_name LIKE '%' || $${idx++} || '%'`);
    params.push(body.customer_project_name);
  }
  if (body.unit_code) {
    conditions.push(`COALESCE(POM.unit_code,'') LIKE '%' || $${idx++} || '%'`);
    params.push(body.unit_code);
  }
  if (body.purchase_order_no) {
    conditions.push(`POM.purchase_order_no LIKE '%' || $${idx++} || '%'`);
    params.push(body.purchase_order_no);
  }
  if (body.type) {
    conditions.push(`POM.type = $${idx++}`);
    params.push(body.type);
  }
  if (body.order_type_cd) {
    conditions.push(`POM.order_type_cd = $${idx++}`);
    params.push(body.order_type_cd);
  }
  if (body.delivery_start_date) {
    conditions.push(
      `COALESCE(POM.delivery_date,'') <> '' AND 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(
      `COALESCE(POM.delivery_date,'') <> '' AND 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);
  }
  if (body.sales_mng_user_id) {
    conditions.push(`POM.sales_mng_user_id = $${idx++}`);
    params.push(body.sales_mng_user_id);
  }
  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.po_client_id) {
    conditions.push(`POM.po_client_id = $${idx++}`);
    params.push(body.po_client_id);
  }
  if (body.part_no) {
    conditions.push(
      `EXISTS (SELECT 1 FROM purchase_order_part POP WHERE POP.purchase_order_master_objid = POM.objid
               AND TRIM(UPPER(COALESCE(POP.part_no,''))) LIKE '%' || TRIM(UPPER($${idx++})) || '%')`,
    );
    params.push(body.part_no);
  }
  if (body.part_name) {
    conditions.push(
      `EXISTS (SELECT 1 FROM purchase_order_part POP WHERE POP.purchase_order_master_objid = POM.objid
               AND TRIM(UPPER(COALESCE(POP.part_name,''))) LIKE '%' || TRIM(UPPER($${idx++})) || '%')`,
    );
    params.push(body.part_name);
  }
  if (body.part_spec) {
    conditions.push(
      `EXISTS (SELECT 1 FROM purchase_order_part POP WHERE POP.purchase_order_master_objid = POM.objid
               AND TRIM(UPPER(COALESCE(POP.spec,''))) LIKE '%' || TRIM(UPPER($${idx++})) || '%')`,
    );
    params.push(body.part_spec);
  }
  if (body.appr_status) {
    const s = String(body.appr_status);
    if (s === "cancel") {
      conditions.push(`POM.status = 'cancel'`);
    } else if (s === "complete") {
      conditions.push(`POM.status = 'approvalComplete'`);
    } else if (s === "create") {
      conditions.push(
        `POM.status = 'create'
         AND NOT EXISTS (SELECT 1 FROM approval AT2 WHERE AT2.target_objid::text = POM.objid::text)
         AND NOT EXISTS (SELECT 1 FROM approval_target AT3 WHERE AT3.target_objid::text = POM.objid::text OR AT3.master_target_objid::text = POM.objid::text)`,
      );
    } else {
      // inProcess / reject 등은 approval.status 기준
      conditions.push(`A.appr_status = $${idx++}`);
      params.push(s);
    }
  }

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

  const sql = `
    SELECT POM.objid::text AS "OBJID",
      (SELECT ARRAY_TO_STRING(ARRAY_AGG(S.objid), ',') FROM purchase_order_master S
         WHERE POM.objid = S.multi_master_objid) AS "MULTI_OBJIDS",
      TO_CHAR(CM.regdate, 'YYYY') AS "CM_YEAR",
      TO_CHAR(POM.regdate, 'YYYY') AS "PO_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",
      COALESCE(CM.project_no, CM.contract_no) AS "PROJECT_NO",
      POM.purchase_order_no AS "PURCHASE_ORDER_NO",
      (CASE WHEN COALESCE(POM.multi_yn,'') = 'Y' AND COALESCE(POM.multi_master_yn,'') != 'Y' THEN 'ㅡ' ELSE '' END)
        || COALESCE(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.type AS "TYPE",
      (SELECT code_name FROM comm_code WHERE code_id = POM.type LIMIT 1) AS "TYPE_NAME",
      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",
      COALESCE(POM.total_price,'0') AS "TOTAL_PRICE",
      COALESCE(POM.total_price_all,'0') AS "TOTAL_PRICE_ALL",
      COALESCE(POM.discount_price,'0') AS "DISCOUNT_PRICE",
      COALESCE(POM.discount_price_all,'0') AS "DISCOUNT_PRICE_ALL",
      COALESCE(POM.total_supply_price,'0') AS "TOTAL_SUPPLY_PRICE",
      COALESCE(POM.total_supply_unit_price,'0') AS "TOTAL_SUPPLY_UNIT_PRICE",
      COALESCE(POM.total_real_supply_price,'0') AS "TOTAL_REAL_SUPPLY_PRICE",
      POM.nego_rate AS "NEGO_RATE",
      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",
      POM.unit_code AS "UNIT_CODE",
      (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(POM.multi_yn,'') AS "MULTI_YN",
      COALESCE(POM.multi_master_yn,'') AS "MULTI_MASTER_YN",
      POM.multi_master_objid AS "MULTI_MASTER_OBJID",
      CASE WHEN COALESCE(POM.multi_master_yn,'') = 'Y' THEN '' ELSE COALESCE(POM.multi_yn,'') END AS "MULTI_YN_MAKED",
      POM.status AS "STATUS",
      A.appr_status AS "APPR_STATUS",
      CASE WHEN POM.status = 'cancel' THEN '취소'
           ELSE COALESCE(A.appr_status_name, '작성중')
      END AS "APPR_STATUS_NAME",
      A.route_objid AS "ROUTE_OBJID",
      A.approval_objid AS "APPROVAL_OBJID",
      A.appr_date AS "APPR_DATE"
    FROM purchase_order_master POM
    LEFT OUTER JOIN (
      SELECT B.objid AS route_objid,
             B.status AS appr_status,
             CASE B.status
               WHEN 'inProcess' THEN '결재중'
               WHEN 'complete' THEN '결재완료'
               WHEN 'reject' THEN '반려'
               WHEN 'cancel' THEN '취소'
               ELSE ''
             END AS appr_status_name,
             AP.objid AS approval_objid,
             AP.target_objid,
             B.route_seq,
             TO_CHAR(B.regdate, 'YYYY-MM-DD') AS appr_date
        FROM approval AP
        JOIN (
          SELECT T1.*
            FROM (SELECT target_objid, MAX(route_seq) AS route_seq FROM route GROUP BY target_objid) T
            JOIN route T1 ON T.target_objid = T1.target_objid AND T.route_seq = T1.route_seq
        ) B ON AP.objid = B.approval_objid
       WHERE AP.target_type IN ('PURCHASE_ORDER')
    ) A ON POM.objid::text = A.target_objid::text
    LEFT OUTER JOIN project_mgmt CM ON POM.contract_mgmt_objid = CM.objid
    WHERE ${where}
    ORDER BY
      CASE WHEN POM.purchase_order_no ~ '^[^-]+-[^-]+-[0-9]+$'
           THEN SPLIT_PART(POM.purchase_order_no, '-', 3)::numeric
           ELSE 0 END DESC,
      POM.regdate DESC
  `;

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