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

// 프로젝트종합 (Gantt) - dashboard.projectMgmtTimeLineGridList_old 대응
// 원본: PROJECT_MGMT + CONTRACT_MGMT + WBS act_start/end 집계
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;

  // 년도: T.REQ_DEL_DATE YYYY 기준 (원본 매퍼)
  if (body.Year || body.year) {
    conditions.push(`TO_CHAR(TO_DATE(PM.req_del_date, 'YYYY-MM-DD'), 'YYYY') = $${idx++}`);
    params.push(String(body.Year || body.year));
  }
  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(`CM.pm_user_id = $${idx++}`);
    params.push(body.pm_user_id);
  }
  if (body.location) {
    conditions.push(`UPPER(CM.location) LIKE UPPER('%' || $${idx++} || '%')`);
    params.push(body.location);
  }
  if (body.setup) {
    conditions.push(`UPPER(CM.setup) LIKE UPPER('%' || $${idx++} || '%')`);
    params.push(body.setup);
  }
  if (body.contract_start_date) {
    conditions.push(`TO_DATE(CM.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(CM.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.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.manufacture_plant AS "MANUFACTURE_PLANT",
      (SELECT code_name FROM comm_code WHERE code_id = PM.manufacture_plant LIMIT 1) AS "MANUFACTURE_PLANT_NAME",
      PM.req_del_date AS "REQ_DEL_DATE",
      (SELECT user_name FROM user_info WHERE user_id = CM.pm_user_id LIMIT 1) AS "PM_USER_NAME",

      -- 실행 시작/종료 (Gantt 렌더용)
      (SELECT MIN(design_act_start) FROM pms_wbs_task WHERE contract_objid = PM.objid::text
        AND COALESCE(design_act_start,'') != '') AS "DESIGN_ACT_START",
      (SELECT MAX(design_act_end) FROM pms_wbs_task WHERE contract_objid = PM.objid::text) AS "DESIGN_ACT_END",
      (SELECT MIN(purchase_act_start) FROM pms_wbs_task WHERE contract_objid = PM.objid::text
        AND COALESCE(purchase_act_start,'') != '') AS "PURCHASE_ACT_START",
      (SELECT MAX(purchase_act_end) FROM pms_wbs_task WHERE contract_objid = PM.objid::text) AS "PURCHASE_ACT_END",
      (SELECT MIN(produce_act_start) FROM pms_wbs_task WHERE contract_objid = PM.objid::text
        AND COALESCE(produce_act_start,'') != '') AS "PRODUCE_ACT_START",
      (SELECT MAX(produce_act_end) FROM pms_wbs_task WHERE contract_objid = PM.objid::text) AS "PRODUCE_ACT_END",
      (SELECT MIN(setup_act_start) FROM setup_wbs_task WHERE contract_objid = PM.objid::text
        AND COALESCE(setup_act_start,'') != '') AS "SETUP_ACT_START",
      (SELECT MAX(setup_act_end) FROM setup_wbs_task WHERE contract_objid = PM.objid::text) AS "SETUP_ACT_END",

      -- 진척율
      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",
      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",
      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",
      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 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",
      (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",
      (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",
      (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"

    FROM project_mgmt PM
    INNER JOIN contract_mgmt CM ON CM.objid = PM.contract_objid
    WHERE ${where}
    ORDER BY PM.req_del_date NULLS LAST, PM.project_no DESC
  `;

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