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

// 영업관리 > 출고관리 목록
// 원본: productionplanning.xml releaseMgmtGridList
// 접근: 수주(0000964)된 contract_mgmt ← project_mgmt 연결 ← release_mgmt(OUTER JOIN)
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[] = [
    "COALESCE(CM.contract_result,'') = '0000964'", // 수주된 계약만
  ];
  const params: unknown[] = [];
  let idx = 1;

  if (body.Year || body.year) {
    conditions.push(`SUBSTR(CM.contract_date,1,4) = $${idx++}`);
    params.push(String(body.Year || body.year));
  }
  if (body.project_nos) {
    const arr = String(body.project_nos).split(",").filter(Boolean);
    if (arr.length > 0) {
      const ph = arr.map(() => `$${idx++}`).join(",");
      conditions.push(`PM.objid IN (${ph})`);
      arr.forEach((v) => params.push(v));
    }
  }
  if (body.category_cd) {
    conditions.push(`CM.category_cd = $${idx++}`);
    params.push(body.category_cd);
  }
  if (body.customer_objid) {
    conditions.push(`CM.customer_objid = $${idx++}`);
    params.push(body.customer_objid);
  }
  if (body.product) {
    conditions.push(`CM.product = $${idx++}`);
    params.push(body.product);
  }
  if (body.pm_user_id) {
    conditions.push(`CM.pm_user_id = $${idx++}`);
    params.push(body.pm_user_id);
  }
  if (body.release_start_date) {
    conditions.push(`RM.release_date >= $${idx++}`);
    params.push(body.release_start_date);
  }
  if (body.release_end_date) {
    conditions.push(`RM.release_date <= $${idx++}`);
    params.push(body.release_end_date);
  }
  if (body.install_result) {
    conditions.push(`RM.install_result = $${idx++}`);
    params.push(body.install_result);
  }

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

  const sql = `
    SELECT
      PM.objid::text AS "OBJID",
      PM.contract_objid::text AS "CONTRACT_OBJID",
      PM.project_no AS "PROJECT_NO",
      PM.project_name AS "PROJECT_NAME",
      CM.category_cd AS "CATEGORY_CD",
      (SELECT code_name FROM comm_code WHERE code_id = CM.category_cd LIMIT 1) AS "CATEGORY_NAME",
      CM.overhaul_order AS "OVERHAUL_ORDER",
      CM.area_cd AS "AREA_CD",
      (SELECT code_name FROM comm_code WHERE code_id = CM.area_cd LIMIT 1) AS "AREA_NAME",
      CM.customer_objid AS "CUSTOMER_OBJID",
      (SELECT supply_name FROM supply_mng WHERE objid::text = CM.customer_objid LIMIT 1) AS "CUSTOMER_NAME",
      CM.product AS "PRODUCT",
      (SELECT code_name FROM comm_code WHERE code_id = CM.product LIMIT 1) AS "PRODUCT_NAME",
      CM.mechanical_type AS "MECHANICAL_TYPE",
      CM.req_del_date AS "REQ_DEL_DATE",
      CM.contract_del_date AS "CONTRACT_DEL_DATE",
      CM.location AS "LOCATION",
      CM.setup AS "SETUP",
      CM.facility AS "FACILITY",
      (SELECT code_name FROM comm_code WHERE code_id = CM.facility LIMIT 1) AS "FACILITY_NAME",
      PM.facility_qty AS "FACILITY_QTY",
      CM.facility_type AS "FACILITY_TYPE",
      CM.facility_depth AS "FACILITY_DEPTH",
      (SELECT user_name FROM user_info WHERE user_id = CM.pm_user_id LIMIT 1) AS "PM_USER_NAME",
      RM.objid AS "RELEASE_OBJID",
      RM.release_car_no AS "RELEASE_CAR_NO",
      RM.release_date AS "RELEASE_DATE",
      RM.task_over_user_id AS "TASK_OVER_USER_ID",
      (SELECT user_name FROM user_info WHERE user_id = RM.task_over_user_id LIMIT 1) AS "TASK_OVER_USER_NAME",
      RM.task_over_date AS "TASK_OVER_DATE",
      RM.install_complete_date AS "INSTALL_COMPLETE_DATE",
      RM.install_result AS "INSTALL_RESULT",
      (SELECT COUNT(*) FROM attach_file_info WHERE target_objid::varchar = RM.objid AND doc_type = 'RELEASE_CHECK' AND UPPER(COALESCE(status,'active'))='ACTIVE')::text AS "RELEASE_CHECK_CNT",
      (SELECT COUNT(*) FROM attach_file_info WHERE target_objid::varchar = RM.objid AND doc_type = 'RELEASE_ORDER' AND UPPER(COALESCE(status,'active'))='ACTIVE')::text AS "RELEASE_ORDER_CNT",
      (SELECT COUNT(*) FROM attach_file_info WHERE target_objid::varchar = RM.objid AND doc_type = 'RELEASE_TAKING_OVER' AND UPPER(COALESCE(status,'active'))='ACTIVE')::text AS "RELEASE_TAKING_OVER_CNT",
      CASE
        WHEN COALESCE(TRIM(RM.release_date),'') != '' THEN '출고완료'
        WHEN COALESCE(TRIM(CM.contract_date),'') != '' THEN '수주'
        WHEN TO_CHAR(NOW(),'YYYYMMDD') >= REPLACE(COALESCE(CM.due_date,''), '-', '') THEN '지연'
        ELSE '계약'
      END AS "RELEASE_STATUS_TITLE"
    FROM project_mgmt PM
    JOIN contract_mgmt CM ON CM.objid = PM.contract_objid
    LEFT OUTER JOIN release_mgmt RM
      ON RM.parent_objid = PM.contract_objid AND COALESCE(RM.product,'') = COALESCE(PM.product,'')
    WHERE ${where}
    ORDER BY PM.project_no DESC
  `;

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