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

// 생산관리_출고관리 (releaseMgmtGridList 원본 1:1 포팅)
// 주 테이블: project_mgmt
// release_mgmt.parent_objid = project_mgmt.objid
// 검사결과(체크리스트) = inspection_mgmt, 입회검사 = attach_file_info(ADMISSION_INSPECTION_FILE)
// 출하지시서 = attach_file_info(RELEASE_ORDER, target_objid = RM.objid)
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",
      T.product AS "PRODUCT",
      RM.product_group AS "PRODUCT_GROUP",
      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",
      RM.objid AS "RELEASE_OBJID",
      RM.release_date AS "RELEASE_DATE",
      COALESCE((SELECT user_name FROM user_info WHERE user_id = RM.writer LIMIT 1), '') AS "RELEASE_WRITER",
      -- 검사결과 체크리스트 = inspection_mgmt 건수
      COALESCE((SELECT COUNT(1) FROM inspection_mgmt WHERE parent_objid = T.objid::text), 0) AS "INSPECTION_CNT",
      -- 입회검사 = ADMISSION_INSPECTION_FILE 파일 개수
      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",
      -- 출하지시서 = RELEASE_ORDER 파일 개수 (release_mgmt.objid 기준)
      COALESCE((SELECT COUNT(1) FROM attach_file_info
                 WHERE target_objid::varchar = RM.objid
                   AND doc_type = 'RELEASE_ORDER'
                   AND UPPER(COALESCE(status,'')) = 'ACTIVE'), 0) AS "RELEASE_ORDER_CNT",
      T.due_date AS "DUE_DATE"
    FROM project_mgmt T
    LEFT OUTER JOIN release_mgmt RM
      ON T.objid::varchar = RM.parent_objid
     AND T.product = RM.product
    WHERE ${where}
    ORDER BY SUBSTRING(T.project_no, POSITION('-' IN T.project_no)+1) DESC
  `;

  const rows = await queryRows(sql, params);
  const todayStr = new Date().toISOString().slice(0, 10);
  const result = rows.map((r: Record<string, unknown>) => {
    const releaseDate = String(r.RELEASE_DATE || "").trim();
    const dueDate = String(r.DUE_DATE || "").trim();
    let status = "계약";
    if (releaseDate !== "") status = "출고완료";
    else if (/^\d{4}-\d{2}-\d{2}$/.test(dueDate) && todayStr >= dueDate) status = "지연";
    return { ...r, RELEASE_STATUS_TITLE: status };
  });
  return NextResponse.json({ RESULTLIST: result, TOTAL_CNT: result.length });
}
