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

// 재료비관리 (materialCostTotaltGridList 원본 1:1 포팅)
// ALL_TOTAL_PRICE = BOM + 재발주(0001408) + 장납기
// NEW_TOTAL_PRICE = 발주(0001407)
// ALL_TOTAL_PRICE_RE = 재발주(0001408)
// MATERIAL_COST_GOAL_RATE = ALL_TOTAL_PRICE × 100 / MATERIAL_COST_GOAL
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.year) {
    conditions.push(`TO_CHAR(T.regdate, 'YYYY') = $${idx++}`);
    params.push(body.year);
  }
  if (body.project_no) {
    conditions.push(`T.project_no LIKE '%' || $${idx++} || '%'`);
    params.push(body.project_no);
  }
  if (body.customer_objid) {
    conditions.push(`T.customer_objid = $${idx++}`);
    params.push(body.customer_objid);
  }
  if (body.product) {
    conditions.push(`T.product = $${idx++}`);
    params.push(body.product);
  }
  if (body.pm_user_id) {
    conditions.push(`T.pm_user_id = $${idx++}`);
    params.push(body.pm_user_id);
  }

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

  const sql = `
    WITH RECURSIVE view_bom AS (
      SELECT pbr.contract_objid, a.bom_report_objid, a.child_objid,
             1 AS lev, ARRAY[a.child_objid::text] AS path, false AS cycle,
             COALESCE(NULLIF(a.qty,''),'0')::numeric AS aggregate_qty
        FROM part_bom_report pbr
        JOIN bom_part_qty a ON pbr.objid = a.bom_report_objid
       WHERE (a.parent_objid IS NULL OR a.parent_objid = '')
      UNION ALL
      SELECT v.contract_objid, b.bom_report_objid, b.child_objid,
             v.lev + 1, v.path || b.child_objid::text,
             b.parent_objid = ANY(v.path),
             v.aggregate_qty * COALESCE(NULLIF(b.qty,''),'0')::numeric
        FROM bom_part_qty b
        JOIN view_bom v ON b.parent_objid = v.child_objid AND v.bom_report_objid = b.bom_report_objid
       WHERE NOT v.cycle
    ),
    bom_material AS (
      SELECT pbr.contract_objid,
             SUM((COALESCE(NULLIF(sp.price,''),'0')::numeric
                + COALESCE(NULLIF(sp.price1,''),'0')::numeric
                + COALESCE(NULLIF(sp.price2,''),'0')::numeric
                + COALESCE(NULLIF(sp.price3,''),'0')::numeric
                + COALESCE(NULLIF(sp.price4,''),'0')::numeric) * v.aggregate_qty) AS total
        FROM part_bom_report pbr
        JOIN sales_bom_report sb ON sb.parent_objid = pbr.objid
        JOIN sales_bom_report_part sp ON sp.parent_objid = pbr.objid
        JOIN view_bom v ON v.child_objid = sp.bom_part_qty_objid
       GROUP BY pbr.contract_objid
    ),
    new_order AS (
      SELECT contract_mgmt_objid,
             SUM(COALESCE(NULLIF(total_supply_unit_price,'')::numeric, 0)) AS total
        FROM purchase_order_master
       WHERE status = 'approvalComplete' AND order_type_cd = '0001407'
         AND contract_mgmt_objid IS NOT NULL
       GROUP BY contract_mgmt_objid
    ),
    re_order AS (
      SELECT contract_mgmt_objid,
             SUM(COALESCE(NULLIF(total_supply_unit_price,'')::numeric, 0)) AS total
        FROM purchase_order_master
       WHERE status = 'approvalComplete' AND order_type_cd = '0001408'
         AND contract_mgmt_objid IS NOT NULL
       GROUP BY contract_mgmt_objid
    ),
    long_delivery AS (
      SELECT li.contract_objid,
             SUM(NULLIF(li.input_qty,'')::numeric * NULLIF(l.price,'')::numeric) AS total
        FROM sales_long_delivery_input li
        JOIN sales_long_delivery l ON l.objid = li.parent_objid
       WHERE NULLIF(li.input_qty,'') IS NOT NULL
         AND NULLIF(l.price,'') IS NOT NULL
       GROUP BY li.contract_objid
    )
    SELECT T.objid::text AS "OBJID",
      T.project_no AS "PROJECT_NO",
      COALESCE((SELECT supply_name FROM supply_mng WHERE objid::text = T.customer_objid LIMIT 1), '') AS "CUSTOMER_NAME",
      T.project_name AS "PROJECT_NAME",
      T.req_del_date AS "REQ_DEL_DATE",
      T.setup AS "SETUP",
      COALESCE((SELECT user_name FROM user_info WHERE user_id = T.pm_user_id LIMIT 1), '') AS "PM_USER_NAME",
      COALESCE(NULLIF(ICG.material_cost_goal,'')::numeric, 0) AS "MATERIAL_COST_GOAL",
      COALESCE(BM.total, 0) + COALESCE(RO.total, 0) + COALESCE(LD.total, 0) AS "ALL_TOTAL_PRICE",
      COALESCE(NO_.total, 0) AS "NEW_TOTAL_PRICE",
      COALESCE(RO.total, 0) AS "ALL_TOTAL_PRICE_RE"
    FROM project_mgmt T
    LEFT JOIN input_cost_goal ICG ON ICG.contract_objid = T.objid::text
    LEFT JOIN bom_material BM ON BM.contract_objid = T.objid::text
    LEFT JOIN new_order NO_ ON NO_.contract_mgmt_objid = T.objid::text
    LEFT JOIN re_order RO ON RO.contract_mgmt_objid = T.objid::text
    LEFT JOIN long_delivery LD ON LD.contract_objid = T.objid::text
    WHERE ${where}
    ORDER BY SUBSTRING(T.project_no, POSITION('-' IN T.project_no)+1) DESC
  `;

  const rows = await queryRows(sql, params);

  const result = rows.map((r: Record<string, unknown>) => {
    const goal = Number(r.MATERIAL_COST_GOAL || 0);
    const actual = Number(r.ALL_TOTAL_PRICE || 0);
    return {
      ...r,
      MATERIAL_COST_GOAL_RATE: goal > 0 ? Math.round((actual / goal) * 1000) / 10 : 0,
    };
  });

  return NextResponse.json({ RESULTLIST: result, TOTAL_CNT: result.length });
}
