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

// 원본: purchaseOrder.xml#purchaseOrderStatusAmountBySupply
// 발주관리 > 업체별_입고요청월 금액현황
// 입고요청일 기준 월별(M01~M12) 공급단가 합계를 업체(admin_supply_mng)별로 집계.
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 year = body.year ? String(body.year) : "";
  const projectNo = body.project_no ? String(body.project_no) : "";
  const salesMngUserId = body.sales_mng_user_id ? String(body.sales_mng_user_id) : "";
  const partnerObjId = body.partner_objid ? String(body.partner_objid) : "";

  // $1=year, $2=project_no(LIKE), $3=sales_mng_user_id, $4=partner_objid
  const params: unknown[] = [
    year || null,
    projectNo || null,
    salesMngUserId || null,
    partnerObjId || null,
  ];

  const coreSql = `
    SELECT POM.partner_objid,
           SUM(COALESCE(NULLIF(POP.supply_unit_price,''),'0')::numeric) AS total_supply_unit_price,
           ${Array.from({ length: 12 }, (_, i) => {
             const mm = String(i + 1).padStart(2, "0");
             return `SUM(CASE WHEN TO_CHAR(TO_DATE(POM.delivery_date,'YYYY-MM-DD'),'MM') = '${mm}'
                              THEN COALESCE(NULLIF(POP.supply_unit_price,''),'0')::numeric END) AS m${mm}`;
           }).join(",\n           ")}
      FROM purchase_order_master POM
      JOIN purchase_order_part POP ON POM.objid = POP.purchase_order_master_objid
      JOIN project_mgmt CM ON POM.contract_mgmt_objid = CM.objid
     WHERE POM.status = 'approvalComplete'
       AND COALESCE(POM.delivery_date,'') <> ''
       AND ($1::text IS NULL OR TO_CHAR(TO_DATE(POM.delivery_date,'YYYY-MM-DD'),'YYYY') = $1)
       AND ($2::text IS NULL OR COALESCE(CM.project_no,'') ILIKE '%' || $2 || '%'
                              OR COALESCE(CM.contract_no,'') ILIKE '%' || $2 || '%')
       AND ($3::text IS NULL OR POM.sales_mng_user_id = $3)
     GROUP BY POM.partner_objid
  `;

  const listSql = `
    SELECT ASM.objid::text AS "OBJID",
           ASM.supply_name AS "SUPPLY_NAME",
           COALESCE(S1.total_supply_unit_price, 0) AS "TOTAL_SUPPLY_UNIT_PRICE",
           ${Array.from({ length: 12 }, (_, i) => {
             const mm = String(i + 1).padStart(2, "0");
             return `COALESCE(S1.m${mm}, 0) AS "M${mm}"`;
           }).join(",\n           ")}
      FROM admin_supply_mng ASM
      JOIN (${coreSql}) S1 ON ASM.objid::varchar = S1.partner_objid
     WHERE ($4::text IS NULL OR ASM.objid::text = $4)
     ORDER BY ASM.supply_name
  `;

  const sumSql = `
    SELECT COALESCE(SUM(total_supply_unit_price), 0) AS "TOTAL_SUPPLY_UNIT_PRICE",
           ${Array.from({ length: 12 }, (_, i) => {
             const mm = String(i + 1).padStart(2, "0");
             return `COALESCE(SUM(m${mm}), 0) AS "M${mm}"`;
           }).join(",\n           ")}
      FROM (
        ${coreSql}
      ) S1
      JOIN admin_supply_mng ASM ON ASM.objid::varchar = S1.partner_objid
     WHERE ($4::text IS NULL OR ASM.objid::text = $4)
  `;

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