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

// 생산관리_검사관리 (inspectionGridList 원본 1:1 포팅)
// 주 테이블: project_mgmt
// INSPECTION_CNT = inspection_mgmt.parent_objid 카운트
// ADMISSION_INSPECTION_CNT = attach_file_info(ADMISSION_INSPECTION_FILE) 카운트
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 = `
    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((SELECT COUNT(1) FROM inspection_mgmt WHERE parent_objid = T.objid::text), 0) AS "INSPECTION_CNT",
      COALESCE((SELECT COUNT(1) FROM attach_file_info
                 WHERE target_objid = T.objid::text
                   AND doc_type = 'ADMISSION_INSPECTION_FILE'
                   AND UPPER(COALESCE(status,'')) = 'ACTIVE'), 0) AS "ADMISSION_INSPECTION_CNT"
    FROM project_mgmt T
    WHERE ${where}
    ORDER BY SUBSTRING(T.project_no, POSITION('-' IN T.project_no)+1) DESC
  `;

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