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

// SCM 발주관리 목록 조회
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[] = [];
  const params: unknown[] = [];
  let idx = 1;

  // SCM: filter by partner unless admin
  if (!user.isAdmin) {
    conditions.push(
      `POM.partner_objid = (SELECT objid::text FROM supply_mng WHERE UPPER(supply_code) = UPPER($${idx++}) LIMIT 1)`
    );
    params.push(user.partnerCd);
  }

  if (body.year) {
    conditions.push(`TO_CHAR(POM.regdate, 'YYYY') = $${idx++}`);
    params.push(body.year);
  }
  if (body.purchase_order_no) {
    conditions.push(`POM.purchase_order_no LIKE '%' || $${idx++} || '%'`);
    params.push(body.purchase_order_no);
  }
  if (body.customer_name) {
    conditions.push(`COALESCE((SELECT supply_name FROM supply_mng WHERE objid::text = CM.customer_objid LIMIT 1), '') LIKE '%' || $${idx++} || '%'`);
    params.push(body.customer_name);
  }
  if (body.project_no) {
    conditions.push(`CM.contract_no LIKE '%' || $${idx++} || '%'`);
    params.push(body.project_no);
  }
  if (body.status_code || body.status) {
    conditions.push(`POM.status = $${idx++}`);
    params.push(body.status_code || body.status);
  }

  const where = conditions.length > 0 ? conditions.join(" AND ") : "1=1";

  const sql = `
    SELECT POM.objid::text AS "OBJID", POM.purchase_order_no AS "PURCHASE_ORDER_NO",
      POM.multi_master_yn AS "MULTI_MASTER_YN",
      COALESCE((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((SELECT supply_name FROM supply_mng WHERE objid::text = CM.customer_objid LIMIT 1), '') AS "CUSTOMER_NAME",
      CM.contract_no AS "PROJECT_NO",
      COALESCE((SELECT code_name FROM comm_code WHERE code_id = POM.unit_code LIMIT 1), POM.unit_code) AS "UNIT_NAME",
      POM.title AS "TITLE", POM.delivery_date AS "DELIVERY_DATE",
      COALESCE((SELECT code_name FROM comm_code WHERE code_id = POM.delivery_place LIMIT 1), '') AS "DELIVERY_PLACE_NAME",
      COALESCE((SELECT COUNT(*) FROM purchase_order_part WHERE purchase_order_master_objid = POM.objid), 0) AS "PART_CNT",
      COALESCE((SELECT SUM(COALESCE(real_order_qty, order_qty, '0')::numeric) FROM purchase_order_part WHERE purchase_order_master_objid = POM.objid), 0) AS "REAL_ORDER_CNT",
      POM.status AS "STATUS_NAME",
      COALESCE((SELECT COUNT(*) FROM arrival_plan WHERE parent_objid = POM.objid::text), 0) AS "ARRIVAL_CNT",
      POM.sales_status AS "SALES_STATUS",
      POM.reception_status AS "RECEPTION_STATUS",
      -- arrival_plan 기반 입고/미납/불량
      COALESCE((SELECT SUM(COALESCE(NULLIF(AP.receipt_qty,'')::numeric, 0)) FROM arrival_plan AP WHERE AP.parent_objid = POM.objid::text), 0) AS "RECEIPT_QTY",
      COALESCE((SELECT SUM(COALESCE(real_order_qty, order_qty, '0')::numeric) FROM purchase_order_part WHERE purchase_order_master_objid = POM.objid), 0)
        - COALESCE((SELECT SUM(COALESCE(NULLIF(AP2.receipt_qty,'')::numeric, 0)) FROM arrival_plan AP2 WHERE AP2.parent_objid = POM.objid::text), 0) AS "NON_DELIVERY_QTY",
      COALESCE((SELECT SUM(COALESCE(NULLIF(AP3.error_qty,'')::numeric, 0)) FROM arrival_plan AP3 WHERE AP3.parent_objid = POM.objid::text AND AP3.error_qty IS NOT NULL AND AP3.error_qty != '' AND AP3.error_qty != '0'), 0) AS "ERROR_QTY",
      -- 거래명세서 발행일
      COALESCE((SELECT MAX(IM.issuance_date) FROM invoice_mgmt IM WHERE IM.parent_objid = POM.objid::text), '') AS "ISSUANCE_DATE"
    FROM purchase_order_master POM
    LEFT JOIN contract_mgmt CM ON CM.objid = POM.contract_mgmt_objid
    WHERE ${where}
    ORDER BY POM.regdate DESC
  `;

  const rows = await queryRows(sql, params);
  return NextResponse.json({ RESULTLIST: rows, TOTAL_CNT: rows.length });
}
