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

// 프로젝트관리_일정관리(WBS) (project.projectMgmtWbsGridList 대응)
// 원본: PROJECT_MGMT + PMS_WBS_TASK(설계/구매/제작) + SETUP_WBS_TASK(셋업) 카운트 집계
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;

  // 년도: regdate YYYY
  if (body.Year || body.year) {
    conditions.push(`TO_CHAR(PM.regdate, 'YYYY') = $${idx++}`);
    params.push(String(body.Year || body.year));
  }
  // 프로젝트번호 multi (CSV) - project_nos 또는 project_no 모두 지원
  const projectNos = body.project_nos || body.project_no;
  if (projectNos) {
    const arr = String(projectNos).split(",").map((s) => s.trim()).filter(Boolean);
    if (arr.length > 0) {
      const ph = arr.map(() => `$${idx++}`).join(",");
      conditions.push(`PM.objid::text IN (${ph})`);
      arr.forEach((v) => params.push(v));
    }
  }
  if (body.category_cd) {
    conditions.push(`PM.category_cd = $${idx++}`);
    params.push(body.category_cd);
  }
  if (body.customer_objid) {
    conditions.push(`PM.customer_objid = $${idx++}`);
    params.push(body.customer_objid);
  }
  if (body.product) {
    conditions.push(`PM.product = $${idx++}`);
    params.push(body.product);
  }
  if (body.pm_user_id) {
    conditions.push(`PM.pm_user_id = $${idx++}`);
    params.push(body.pm_user_id);
  }
  if (body.location) {
    conditions.push(`PM.location LIKE '%' || $${idx++} || '%'`);
    params.push(body.location);
  }
  if (body.setup) {
    conditions.push(`PM.setup LIKE '%' || $${idx++} || '%'`);
    params.push(body.setup);
  }
  // 예상납기일(due_date) 범위
  if (body.contract_start_date) {
    conditions.push(`TO_DATE(PM.due_date, 'YYYY-MM-DD') >= TO_DATE($${idx++}, 'YYYY-MM-DD')`);
    params.push(body.contract_start_date);
  }
  if (body.contract_end_date) {
    conditions.push(`TO_DATE(PM.due_date, 'YYYY-MM-DD') <= TO_DATE($${idx++}, 'YYYY-MM-DD')`);
    params.push(body.contract_end_date);
  }

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

  const sql = `
    SELECT
      PM.objid::text AS "OBJID",
      PM.category_cd AS "CATEGORY_CD",
      (SELECT code_name FROM comm_code WHERE code_id = PM.category_cd LIMIT 1) AS "CATEGORY_NAME",
      PM.overhaul_order AS "OVERHAUL_ORDER",
      PM.customer_objid AS "CUSTOMER_OBJID",
      (SELECT supply_name FROM supply_mng WHERE objid::text = PM.customer_objid LIMIT 1) AS "CUSTOMER_NAME",
      PM.product AS "PRODUCT",
      (SELECT code_name FROM comm_code WHERE code_id = PM.product LIMIT 1) AS "PRODUCT_NAME",
      PM.project_no AS "PROJECT_NO",
      PM.project_name AS "PROJECT_NAME",
      PM.req_del_date AS "REQ_DEL_DATE",
      PM.contract_del_date AS "CONTRACT_DEL_DATE",
      PM.setup AS "SETUP",
      PM.location AS "LOCATION",
      PM.facility AS "FACILITY",
      (SELECT code_name FROM comm_code WHERE code_id = PM.facility LIMIT 1) AS "FACILITY_NAME",
      PM.manufacture_plant AS "MANUFACTURE_PLANT",
      (SELECT code_name FROM comm_code WHERE code_id = PM.manufacture_plant LIMIT 1) AS "MANUFACTURE_PLANT_NAME",
      PM.pm_user_id AS "PM_USER_ID",
      (SELECT user_name FROM user_info WHERE user_id = PM.pm_user_id LIMIT 1) AS "PM_USER_NAME",
      PM.mechanical_type AS "MECHANICAL_TYPE",

      -- 설계관리 공정율 (완료/전체)
      CASE WHEN (SELECT COUNT(1) FROM pms_wbs_task WHERE contract_objid = PM.objid::text) = 0 THEN 0
           ELSE ROUND(
             ((SELECT COUNT(1) FROM pms_wbs_task WHERE contract_objid = PM.objid::text)
              - (SELECT COUNT(1) FROM pms_wbs_task WHERE contract_objid = PM.objid::text
                 AND (COALESCE(design_act_end,'') = '')))::numeric
             / (SELECT COUNT(1) FROM pms_wbs_task WHERE contract_objid = PM.objid::text)::numeric * 100, 1)
      END AS "DESIGN_RATETOTAL",
      -- 설계 완료
      (SELECT COUNT(1) FROM pms_wbs_task
        WHERE contract_objid = PM.objid::text
          AND COALESCE(design_act_end,'') != '' AND COALESCE(design_plan_end,'') != ''
          AND TO_DATE(design_plan_end,'YYYY-MM-DD') >= TO_DATE(design_act_end,'YYYY-MM-DD')) AS "DESIGN_COMP_CNT",
      -- 설계 지연완료
      (SELECT COUNT(1) FROM pms_wbs_task
        WHERE contract_objid = PM.objid::text
          AND COALESCE(design_act_end,'') != '' AND COALESCE(design_plan_end,'') != ''
          AND TO_DATE(design_plan_end,'YYYY-MM-DD') < TO_DATE(design_act_end,'YYYY-MM-DD')) AS "DESIGN_LATE_COMP_CNT",
      -- 설계 진행중
      (SELECT COUNT(1) FROM pms_wbs_task
        WHERE contract_objid = PM.objid::text
          AND COALESCE(design_act_end,'') = '' AND COALESCE(design_plan_end,'') != ''
          AND TO_DATE(design_plan_end,'YYYY-MM-DD') >= CURRENT_DATE) AS "DESIGN_ING_CNT",
      -- 설계 지연
      (SELECT COUNT(1) FROM pms_wbs_task
        WHERE contract_objid = PM.objid::text
          AND COALESCE(design_act_end,'') = '' AND COALESCE(design_plan_end,'') != ''
          AND TO_DATE(design_plan_end,'YYYY-MM-DD') < CURRENT_DATE) AS "DESIGN_LATE_CNT",

      -- 구매관리 공정율
      CASE WHEN (SELECT COUNT(1) FROM pms_wbs_task WHERE contract_objid = PM.objid::text) = 0 THEN 0
           ELSE ROUND(
             ((SELECT COUNT(1) FROM pms_wbs_task WHERE contract_objid = PM.objid::text)
              - (SELECT COUNT(1) FROM pms_wbs_task WHERE contract_objid = PM.objid::text
                 AND (COALESCE(purchase_act_end,'') = '')))::numeric
             / (SELECT COUNT(1) FROM pms_wbs_task WHERE contract_objid = PM.objid::text)::numeric * 100, 1)
      END AS "PURCHASE_RATETOTAL",
      (SELECT COUNT(1) FROM pms_wbs_task
        WHERE contract_objid = PM.objid::text
          AND COALESCE(purchase_act_end,'') != '' AND COALESCE(purchase_plan_end,'') != ''
          AND TO_DATE(purchase_plan_end,'YYYY-MM-DD') >= TO_DATE(purchase_act_end,'YYYY-MM-DD')) AS "PURCHASE_COMP_CNT",
      (SELECT COUNT(1) FROM pms_wbs_task
        WHERE contract_objid = PM.objid::text
          AND COALESCE(purchase_act_end,'') != '' AND COALESCE(purchase_plan_end,'') != ''
          AND TO_DATE(purchase_plan_end,'YYYY-MM-DD') < TO_DATE(purchase_act_end,'YYYY-MM-DD')) AS "PURCHASE_LATE_COMP_CNT",
      (SELECT COUNT(1) FROM pms_wbs_task
        WHERE contract_objid = PM.objid::text
          AND COALESCE(purchase_act_end,'') = '' AND COALESCE(purchase_plan_end,'') != ''
          AND TO_DATE(purchase_plan_end,'YYYY-MM-DD') >= CURRENT_DATE) AS "PURCHASE_ING_CNT",
      (SELECT COUNT(1) FROM pms_wbs_task
        WHERE contract_objid = PM.objid::text
          AND COALESCE(purchase_act_end,'') = '' AND COALESCE(purchase_plan_end,'') != ''
          AND TO_DATE(purchase_plan_end,'YYYY-MM-DD') < CURRENT_DATE) AS "PURCHASE_LATE_CNT",

      -- 조립(제작)관리 공정율 — 원본은 PRODUCE_RATE 평균
      COALESCE(ROUND(
        (SELECT SUM(COALESCE(produce_rate,'0')::numeric) FROM pms_wbs_task WHERE contract_objid = PM.objid::text)
        / NULLIF((SELECT COUNT(1) FROM pms_wbs_task WHERE contract_objid = PM.objid::text), 0), 1), 0) AS "PRODUCE_RATETOTAL",
      (SELECT COUNT(1) FROM pms_wbs_task
        WHERE contract_objid = PM.objid::text
          AND COALESCE(produce_act_end,'') != '' AND COALESCE(produce_plan_end,'') != ''
          AND TO_DATE(produce_plan_end,'YYYY-MM-DD') >= TO_DATE(produce_act_end,'YYYY-MM-DD')) AS "PRODUCE_COMP_CNT",
      (SELECT COUNT(1) FROM pms_wbs_task
        WHERE contract_objid = PM.objid::text
          AND COALESCE(produce_act_end,'') != '' AND COALESCE(produce_plan_end,'') != ''
          AND TO_DATE(produce_plan_end,'YYYY-MM-DD') < TO_DATE(produce_act_end,'YYYY-MM-DD')) AS "PRODUCE_LATE_COMP_CNT",
      (SELECT COUNT(1) FROM pms_wbs_task
        WHERE contract_objid = PM.objid::text
          AND COALESCE(produce_act_end,'') = '' AND COALESCE(produce_plan_end,'') != ''
          AND TO_DATE(produce_plan_end,'YYYY-MM-DD') >= CURRENT_DATE) AS "PRODUCE_ING_CNT",
      (SELECT COUNT(1) FROM pms_wbs_task
        WHERE contract_objid = PM.objid::text
          AND COALESCE(produce_act_end,'') = '' AND COALESCE(produce_plan_end,'') != ''
          AND TO_DATE(produce_plan_end,'YYYY-MM-DD') < CURRENT_DATE) AS "PRODUCE_LATE_CNT",

      -- 셋업관리
      COALESCE(
        CASE WHEN (SELECT COUNT(1) FROM setup_wbs_task WHERE contract_objid = PM.objid::text
                      AND COALESCE(parent_objid,'') != '') = 0 THEN 0
             ELSE ROUND(
               (SELECT COUNT(1) FROM setup_wbs_task WHERE contract_objid = PM.objid::text
                  AND COALESCE(setup_act_end,'') != '' AND COALESCE(parent_objid,'') != '')::numeric
               / (SELECT COUNT(1) FROM setup_wbs_task WHERE contract_objid = PM.objid::text
                  AND COALESCE(parent_objid,'') != '')::numeric * 100, 1)
        END, 0) AS "SETUP_RATETOTAL",
      (SELECT COUNT(1) FROM setup_wbs_task
        WHERE contract_objid = PM.objid::text
          AND COALESCE(parent_objid,'') != ''
          AND COALESCE(setup_act_end,'') != '' AND COALESCE(setup_plan_end,'') != ''
          AND TO_DATE(setup_plan_end,'YYYY-MM-DD') >= TO_DATE(setup_act_end,'YYYY-MM-DD')) AS "SETUP_COMP_CNT",
      (SELECT COUNT(1) FROM setup_wbs_task
        WHERE contract_objid = PM.objid::text
          AND COALESCE(parent_objid,'') != ''
          AND COALESCE(setup_act_end,'') != '' AND COALESCE(setup_plan_end,'') != ''
          AND TO_DATE(setup_plan_end,'YYYY-MM-DD') < TO_DATE(setup_act_end,'YYYY-MM-DD')) AS "SETUP_LATE_COMP_CNT",
      (SELECT COUNT(1) FROM setup_wbs_task
        WHERE contract_objid = PM.objid::text
          AND COALESCE(parent_objid,'') != ''
          AND COALESCE(setup_act_end,'') = '' AND COALESCE(setup_plan_end,'') != ''
          AND TO_DATE(setup_plan_end,'YYYY-MM-DD') >= CURRENT_DATE) AS "SETUP_ING_CNT",
      (SELECT COUNT(1) FROM setup_wbs_task
        WHERE contract_objid = PM.objid::text
          AND COALESCE(parent_objid,'') != ''
          AND COALESCE(setup_act_end,'') = '' AND COALESCE(setup_plan_end,'') != ''
          AND TO_DATE(setup_plan_end,'YYYY-MM-DD') < CURRENT_DATE) AS "SETUP_LATE_CNT",

      TO_CHAR(PM.regdate, 'YYYY-MM-DD') AS "REGDATE"
    FROM project_mgmt PM
    WHERE ${where}
    ORDER BY PM.regdate DESC
  `;

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