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

// 원본: /salesMng/salesLongDeliveryList.do — sales_long_delivery + _input + _predict
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.ld_part_name || body.part_name) {
    conditions.push(`T1.ld_part_name LIKE '%' || $${idx++} || '%'`);
    params.push(body.ld_part_name || body.part_name);
  }
  if (body.Location || body.location) {
    conditions.push(`T1.location = $${idx++}`);
    params.push(body.Location || body.location);
  }
  if (body.spec) {
    conditions.push(`T1.spec LIKE '%' || $${idx++} || '%'`);
    params.push(body.spec);
  }
  if (body.maker) {
    conditions.push(`T1.maker LIKE '%' || $${idx++} || '%'`);
    params.push(body.maker);
  }
  if (body.material_code) {
    conditions.push(`T1.material_code LIKE '%' || $${idx++} || '%'`);
    params.push(body.material_code);
  }
  if (body.admin_supply) {
    conditions.push(`T1.supply_objid = $${idx++}`);
    params.push(body.admin_supply);
  }

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

  const sql = `
    SELECT T1.objid::text AS "OBJID",
      T1.ld_part_name AS "LD_PART_NAME",
      T1.spec AS "SPEC",
      T1.form_no AS "FORM_NO",
      T1.maker AS "MAKER",
      T1.material_code AS "MATERIAL_CODE",
      T1.supply_objid AS "SUPPLY_OBJID",
      (SELECT supply_name FROM admin_supply_mng S WHERE S.objid::text = T1.supply_objid LIMIT 1) AS "SUPPLY_NAME",
      T1.location AS "LOCATION",
      (SELECT code_name FROM comm_code WHERE code_id = T1.location LIMIT 1) AS "LOCATION_NAME",
      T1.price AS "PRICE",
      (COALESCE(T1.price,'0')::numeric *
        COALESCE((SELECT SUM(COALESCE(input_qty,'0')::numeric) FROM sales_long_delivery_input T2 WHERE T2.parent_objid = T1.objid), 0)
      )::text AS "PRICE_SUM",
      COALESCE(
        (SELECT SUM(COALESCE(input_qty,'0')::numeric) FROM sales_long_delivery_input T2
           WHERE T2.parent_objid = T1.objid AND (contract_objid IS NULL OR contract_objid = '')), 0
      ) -
      COALESCE(
        (SELECT SUM(COALESCE(input_qty,'0')::numeric) FROM sales_long_delivery_input T2
           WHERE T2.parent_objid = T1.objid AND contract_objid IS NOT NULL AND contract_objid != ''), 0
      ) AS "INPUT_QTY",
      COALESCE(
        (SELECT COUNT(1) FROM sales_long_delivery_input T2 WHERE T2.parent_objid = T1.objid), 0
      ) AS "INPUT_CNT",
      T3.M_TOTAL AS "M_TOTAL",
      T3.M01 AS "M01", T3.M02 AS "M02", T3.M03 AS "M03",
      T3.M04 AS "M04", T3.M05 AS "M05", T3.M06 AS "M06",
      T3.M07 AS "M07", T3.M08 AS "M08", T3.M09 AS "M09",
      T3.M10 AS "M10", T3.M11 AS "M11", T3.M12 AS "M12"
    FROM sales_long_delivery T1
      LEFT OUTER JOIN (
        SELECT parent_objid,
          SUM(COALESCE(qty,'0')::numeric) AS M_TOTAL,
          SUM(CASE WHEN month::numeric = 1  THEN qty::numeric END) AS M01,
          SUM(CASE WHEN month::numeric = 2  THEN qty::numeric END) AS M02,
          SUM(CASE WHEN month::numeric = 3  THEN qty::numeric END) AS M03,
          SUM(CASE WHEN month::numeric = 4  THEN qty::numeric END) AS M04,
          SUM(CASE WHEN month::numeric = 5  THEN qty::numeric END) AS M05,
          SUM(CASE WHEN month::numeric = 6  THEN qty::numeric END) AS M06,
          SUM(CASE WHEN month::numeric = 7  THEN qty::numeric END) AS M07,
          SUM(CASE WHEN month::numeric = 8  THEN qty::numeric END) AS M08,
          SUM(CASE WHEN month::numeric = 9  THEN qty::numeric END) AS M09,
          SUM(CASE WHEN month::numeric = 10 THEN qty::numeric END) AS M10,
          SUM(CASE WHEN month::numeric = 11 THEN qty::numeric END) AS M11,
          SUM(CASE WHEN month::numeric = 12 THEN qty::numeric END) AS M12
        FROM sales_long_delivery_predict
        GROUP BY parent_objid
      ) T3 ON T3.parent_objid = T1.objid
    WHERE ${where}
    ORDER BY T1.ld_part_name, "SUPPLY_NAME"
  `;

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