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

// 원본: purchaseOrder.xml#purchaseOrderStatusByProject
// 발주관리 > 현황 — (프로젝트 × 유닛) 단위 발주율 / 재발주 / 턴키 / 총발주금액 집계
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 unitCode = body.unit_code ? String(body.unit_code) : "";

  // 공통 WHERE를 CTE별로 동일하게 쓰기 위해 파라미터 플레이스홀더를 정렬
  // $1=year(POM.regdate YYYY), $2=project_no(프로젝트 LIKE), $3=unit_code
  const params: unknown[] = [year || null, projectNo || null, unitCode || null];

  const sql = `
    WITH base AS (
      -- 프로젝트 × 유닛(WBS) × (있다면)구매BOM 카르테시안
      SELECT CM.objid::text AS project_objid,
             COALESCE(CM.project_no, CM.contract_no) AS project_no,
             CM.customer_project_name,
             CM.project_name,
             TO_CHAR(CM.regdate, 'YYYY') AS cm_year,
             (SELECT supply_name FROM supply_mng O WHERE O.objid::text = CM.customer_objid LIMIT 1) AS customer_name,
             PWT.objid::text AS unit_code,
             COALESCE(PWT.unit_no,'') || '-' || COALESCE(PWT.task_name,'') AS unit_part_name,
             PBR.objid::text AS bom_report_objid,
             (SELECT COUNT(*) FROM bom_part_qty A WHERE A.bom_report_objid = PBR.objid) AS bom_cnt
        FROM project_mgmt CM
        LEFT JOIN pms_wbs_task PWT ON PWT.contract_objid = CM.objid
        LEFT JOIN part_bom_report PBR ON PBR.contract_objid = CM.objid AND PBR.unit_code = PWT.objid::varchar
       WHERE ($2::text IS NULL OR COALESCE(CM.project_no,'') ILIKE '%' || $2 || '%' OR COALESCE(CM.contract_no,'') ILIKE '%' || $2 || '%')
         AND ($3::text IS NULL OR PWT.objid::text = $3)
    ),
    -- 구매BOM 부품 개수 (distinct part_no) — 발주율 분모
    bom_rollup AS (
      SELECT PBR.objid::text AS bom_report_objid,
             COUNT(DISTINCT BPQ.part_no) FILTER (
               WHERE COALESCE(PM.part_type,'') != ''
                 AND BPQ.status IN ('beforeEdit','editing','deleting','deploy')
             ) AS total_bom_part_cnt
        FROM part_bom_report PBR
        LEFT JOIN bom_part_qty BPQ ON BPQ.bom_report_objid = PBR.objid
        LEFT JOIN part_mng PM ON COALESCE(NULLIF(BPQ.last_part_objid,''), BPQ.part_no) = PM.objid::text
       GROUP BY PBR.objid
    ),
    -- 발주 집계 (approvalComplete만) — 프로젝트 × 유닛 단위
    po_rollup AS (
      SELECT POM.contract_mgmt_objid::text AS project_objid,
             POM.unit_code,
             SUM(COALESCE(NULLIF(POP.supply_unit_price,''),'0')::numeric) AS total_supply_unit_price,
             SUM(CASE WHEN PM.part_type IN ('0000063')
                      THEN COALESCE(NULLIF(POP.supply_unit_price,''),'0')::numeric END) AS price_pt_1,
             SUM(CASE WHEN PM.part_type IN ('0000064','0001540','0001398','0001397','0001396')
                      THEN COALESCE(NULLIF(POP.supply_unit_price,''),'0')::numeric END) AS price_pt_2,
             SUM(CASE WHEN PM.part_type IN ('0000065')
                      THEN COALESCE(NULLIF(POP.supply_unit_price,''),'0')::numeric END) AS price_pt_3,
             SUM(CASE WHEN PM.part_type NOT IN ('0000063','0000064','0001540','0001398','0001397','0001396','0000065') OR PM.part_type IS NULL
                      THEN COALESCE(NULLIF(POP.supply_unit_price,''),'0')::numeric END) AS price_pt_etc,
             COUNT(DISTINCT POP.part_objid) AS total_po_part_cnt
        FROM purchase_order_master POM
        JOIN purchase_order_part POP ON POP.purchase_order_master_objid = POM.objid
        LEFT JOIN part_mng PM ON POP.part_objid = PM.objid::text
       WHERE POM.status = 'approvalComplete'
         AND COALESCE(POM.type,'') != '0001785'
         AND ($1::text IS NULL OR TO_CHAR(POM.regdate, 'YYYY') = $1)
       GROUP BY POM.contract_mgmt_objid, POM.unit_code
    ),
    -- 재발주 집계
    re_po_rollup AS (
      SELECT POM.contract_mgmt_objid::text AS project_objid,
             POM.unit_code,
             COUNT(DISTINCT POM.objid) AS re_count,
             SUM(COALESCE(NULLIF(POP.supply_unit_price,''),'0')::numeric) AS re_price
        FROM purchase_order_master POM
        JOIN purchase_order_part POP ON POP.purchase_order_master_objid = POM.objid
       WHERE POM.status = 'approvalComplete'
         AND POM.order_type_cd = '0001408'
         AND COALESCE(POM.type,'') != '0001785'
         AND ($1::text IS NULL OR TO_CHAR(POM.regdate, 'YYYY') = $1)
       GROUP BY POM.contract_mgmt_objid, POM.unit_code
    ),
    -- 턴키 집계
    turnkey_rollup AS (
      SELECT POM.contract_mgmt_objid::text AS project_objid,
             POM.unit_code,
             COUNT(DISTINCT POM.objid) AS turnkey_count,
             SUM(COALESCE(NULLIF(POP.supply_unit_price,''),'0')::numeric) AS turnkey_price
        FROM purchase_order_master POM
        JOIN purchase_order_part POP ON POP.purchase_order_master_objid = POM.objid
       WHERE POM.status = 'approvalComplete'
         AND POM.type = '0001785'
         AND ($1::text IS NULL OR TO_CHAR(POM.regdate, 'YYYY') = $1)
       GROUP BY POM.contract_mgmt_objid, POM.unit_code
    ),
    -- 총 발주금액 (프로젝트 × 유닛) — total_price_all이 비어있으면 total_price로 폴백
    total_rollup AS (
      SELECT POM.contract_mgmt_objid::text AS project_objid,
             POM.unit_code,
             SUM(CASE WHEN COALESCE(NULLIF(POM.total_price_all,''),'') <> ''
                      THEN POM.total_price_all::numeric
                      ELSE COALESCE(NULLIF(POM.total_price,''),'0')::numeric END) AS total_price_all,
             SUM(COALESCE(NULLIF(POM.total_supply_price,''),'0')::numeric) AS total_supply_price,
             SUM(COALESCE(NULLIF(POM.discount_price,''),'0')::numeric) AS discount_price,
             SUM(COALESCE(NULLIF(POM.total_price,''),'0')::numeric) AS total_price
        FROM purchase_order_master POM
       WHERE POM.status = 'approvalComplete'
         AND (COALESCE(POM.multi_master_yn,'') = 'Y' OR (COALESCE(POM.multi_master_yn,'') != 'Y' AND COALESCE(POM.multi_yn,'') != 'Y'))
         AND ($1::text IS NULL OR TO_CHAR(POM.regdate, 'YYYY') = $1)
       GROUP BY POM.contract_mgmt_objid, POM.unit_code
    )
    SELECT B.project_objid AS "PROJECT_OBJID",
           B.cm_year AS "CM_YEAR",
           B.customer_name AS "CUSTOMER_NAME",
           COALESCE(B.project_name, B.customer_project_name) AS "PROJECT_NAME",
           B.customer_project_name AS "CUSTOMER_PROJECT_NAME",
           B.unit_part_name AS "UNIT_PART_NAME",
           B.project_no AS "PROJECT_NO",
           B.bom_report_objid AS "BOM_REPORT_OBJID",
           COALESCE(B.bom_cnt, 0) AS "BOM_CNT",
           COALESCE(BR.total_bom_part_cnt, 0) AS "TOTAL_BOM_PART_CNT",
           COALESCE(P.total_po_part_cnt, 0) AS "TOTAL_PO_PART_CNT",
           GREATEST(COALESCE(BR.total_bom_part_cnt, 0) - COALESCE(P.total_po_part_cnt, 0), 0) AS "NON_PO_PART_CNT",
           CASE WHEN COALESCE(BR.total_bom_part_cnt, 0) = 0 THEN 0
                ELSE ROUND(COALESCE(P.total_po_part_cnt, 0)::numeric / BR.total_bom_part_cnt * 100, 1)
           END AS "RATE_PO",
           COALESCE(P.price_pt_1, 0) AS "PRICE_PT_1",
           COALESCE(P.price_pt_2, 0) AS "PRICE_PT_2",
           COALESCE(P.price_pt_3, 0) AS "PRICE_PT_3",
           COALESCE(P.price_pt_etc, 0) AS "PRICE_PT_ETC",
           COALESCE(R.re_count, 0) AS "RE_COUNT",
           COALESCE(R.re_price, 0) AS "RE_PRICE",
           COALESCE(TK.turnkey_count, 0) AS "TURNKEY_COUNT",
           COALESCE(TK.turnkey_price, 0) AS "TURNKEY_PRICE",
           COALESCE(TR.total_price_all, 0) AS "TOTAL_PRICE_ALL",
           COALESCE(TR.total_price, 0) AS "TOTAL_PRICE"
      FROM base B
      LEFT JOIN bom_rollup BR ON BR.bom_report_objid = B.bom_report_objid
      LEFT JOIN po_rollup P ON P.project_objid = B.project_objid AND P.unit_code = B.unit_code
      LEFT JOIN re_po_rollup R ON R.project_objid = B.project_objid AND R.unit_code = B.unit_code
      LEFT JOIN turnkey_rollup TK ON TK.project_objid = B.project_objid AND TK.unit_code = B.unit_code
      LEFT JOIN total_rollup TR ON TR.project_objid = B.project_objid AND TR.unit_code = B.unit_code
      ORDER BY B.project_no DESC NULLS LAST, B.unit_part_name
  `;

  try {
    const rows = await queryRows(sql, params);
    // 그랜드 토탈 — FITO 원본 purchaseOrderStatusByProjectSum 매퍼와 일치:
    //   총발주금액 = SUM(total_real_supply_price)  (VAT 포함 실공급가 합계)
    //   단일발주금액 = SUM(total_supply_price)     (공급가 합계)
    const totals = await queryOne(
      `SELECT SUM(COALESCE(NULLIF(POM.total_real_supply_price,''),'0')::numeric) AS "TOTAL_PRICE_ALL_SUM",
              SUM(COALESCE(NULLIF(POM.total_supply_price,''),'0')::numeric) AS "SINGLE_PRICE_SUM"
         FROM purchase_order_master POM
        WHERE POM.status = 'approvalComplete'
          AND ($1::text IS NULL OR TO_CHAR(POM.regdate, 'YYYY') = $1)`,
      [year || null],
    );
    return NextResponse.json({ RESULTLIST: rows, TOTAL_CNT: rows.length, SUMS: totals });
  } catch (e) {
    console.error("order/status:", e);
    return NextResponse.json({ success: false, message: "조회 중 오류가 발생했습니다." }, { status: 500 });
  }
}
