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);
  }

  // supplyQCMgmtGridList 대응 — 공급업체별 품질 집계 (arrival_plan 기반)
  if (body.year) {
    conditions.push(`TO_CHAR(POM.regdate, 'YYYY') = $${idx++}`);
    params.push(body.year);
  }
  if (body.supplier_name || body.partner_name) {
    conditions.push(
      `(SELECT supply_name FROM supply_mng WHERE objid::text = POM.partner_objid LIMIT 1) LIKE '%' || $${idx++} || '%'`
    );
    params.push(body.supplier_name || body.partner_name);
  }

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

  // 공급업체별 집계
  const sql = `
    SELECT POM.partner_objid AS "OBJID",
      COALESCE((SELECT supply_name FROM supply_mng WHERE objid::text = POM.partner_objid LIMIT 1), '') AS "SUPPLIER_NAME",
      TO_CHAR(POM.regdate, 'YYYY') AS "EVALUATION_PERIOD",
      -- 납품건수 = 입고된 arrival_plan 건수
      COALESCE(SUM((SELECT COUNT(*) FROM arrival_plan AP WHERE AP.parent_objid = POM.objid::text AND COALESCE(AP.receipt_qty,'') != '')), 0) AS "DELIVERY_CNT",
      -- 불량건수 = error_qty > 0 건수
      COALESCE(SUM((SELECT COUNT(*) FROM arrival_plan AP2 WHERE AP2.parent_objid = POM.objid::text AND COALESCE(NULLIF(AP2.error_qty,'')::numeric, 0) > 0)), 0) AS "DEFECT_CNT",
      -- 불량율
      CASE WHEN SUM(COALESCE((SELECT SUM(COALESCE(NULLIF(AP3.receipt_qty,'')::numeric, 0)) FROM arrival_plan AP3 WHERE AP3.parent_objid = POM.objid::text), 0)) > 0
        THEN ROUND(SUM(COALESCE((SELECT SUM(COALESCE(NULLIF(AP4.error_qty,'')::numeric, 0)) FROM arrival_plan AP4 WHERE AP4.parent_objid = POM.objid::text), 0))::numeric * 100
          / NULLIF(SUM(COALESCE((SELECT SUM(COALESCE(NULLIF(AP5.receipt_qty,'')::numeric, 0)) FROM arrival_plan AP5 WHERE AP5.parent_objid = POM.objid::text), 0)), 1), 1)
        ELSE 0 END AS "DEFECT_RATE",
      -- 납기준수율 (placeholder — 실제 납기 지연 계산은 별도 로직 필요)
      100 AS "DELIVERY_RATE",
      0 AS "QUALITY_SCORE",
      'A' AS "TOTAL_GRADE",
      '' AS "EVALUATION_DATE",
      '' AS "EVALUATOR_NAME"
    FROM purchase_order_master POM
    WHERE POM.status = 'approvalComplete' AND ${where}
    GROUP BY POM.partner_objid, TO_CHAR(POM.regdate, 'YYYY')
    ORDER BY "SUPPLIER_NAME"
  `;

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