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

// 프로젝트관리_진행관리 (project.projectMgmtGridList 대응)
// 원본: project_mgmt(PM) + release_mgmt(RM) + expense_master(EP) + ...
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;

  // 년도: 계약일(contract_date) 기준
  if (body.Year || body.year) {
    conditions.push(`SUBSTR(PM.contract_date, 1, 4) = $${idx++}`);
    params.push(String(body.Year || body.year));
  }
  // 프로젝트번호 multi (CSV)
  if (body.project_no) {
    const arr = String(body.project_no).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);
  }
  // 예상납기일(contract_del_date) 범위
  if (body.contract_start_date) {
    conditions.push(`PM.contract_del_date >= $${idx++}`);
    params.push(body.contract_start_date);
  }
  if (body.contract_end_date) {
    conditions.push(`PM.contract_del_date <= $${idx++}`);
    params.push(body.contract_end_date);
  }
  // 종합현황 모드: 고객사명 LIKE
  if (body.customer_name) {
    conditions.push(`COALESCE((SELECT supply_name FROM supply_mng WHERE objid::text = PM.customer_objid LIMIT 1), '') LIKE '%' || $${idx++} || '%'`);
    params.push(body.customer_name);
  }

  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",
      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.mechanical_type AS "MECHANICAL_TYPE",
      PM.req_del_date AS "REQ_DEL_DATE",
      PM.contract_del_date AS "CONTRACT_DEL_DATE",
      PM.location AS "LOCATION",
      PM.setup AS "SETUP",
      PM.facility AS "FACILITY",
      (SELECT code_name FROM comm_code WHERE code_id = PM.facility LIMIT 1) AS "FACILITY_NAME",
      PM.facility_qty AS "FACILITY_QTY",
      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",
      -- 투입원가 (project.xml projectMgmtGridList 이식)
      COALESCE((SELECT (COALESCE(ICG.material_cost_goal,'0')::numeric + COALESCE(ICG.labor_cost_goal,'0')::numeric + COALESCE(ICG.expense_cost_goal,'0')::numeric)
                FROM input_cost_goal ICG WHERE ICG.contract_objid::text = PM.objid::text LIMIT 1), 0) AS "TOTAL_COST_GOAL",
      (
        COALESCE((SELECT SUM(settle)
                  FROM (SELECT (SUM(COALESCE(ED.card_used,'0')::numeric) + SUM(COALESCE(ED.cash_used,'0')::numeric) - SUM(COALESCE(ED.payment,'0')::numeric)) AS settle
                        FROM expense_master EM
                        LEFT OUTER JOIN expense_detail ED ON ED.expense_master_objid = EM.expense_master_objid
                        WHERE EM.project_mgmt_objid = PM.objid::text
                        GROUP BY EM.expense_master_objid) s), 0)
        +
        COALESCE((SELECT SUM(COALESCE(NULLIF(total_supply_unit_price,'')::numeric, 0))
                  FROM purchase_order_master
                  WHERE contract_mgmt_objid = PM.objid AND status = 'approvalComplete'), 0)
      ) AS "TOTAL_COST_ACTUAL",
      CASE
        WHEN COALESCE((SELECT (COALESCE(ICG.material_cost_goal,'0')::numeric + COALESCE(ICG.labor_cost_goal,'0')::numeric + COALESCE(ICG.expense_cost_goal,'0')::numeric)
                       FROM input_cost_goal ICG WHERE ICG.contract_objid::text = PM.objid::text LIMIT 1), 0) = 0 THEN 0
        ELSE ROUND(
          (
            COALESCE((SELECT SUM(settle)
                      FROM (SELECT (SUM(COALESCE(ED.card_used,'0')::numeric) + SUM(COALESCE(ED.cash_used,'0')::numeric) - SUM(COALESCE(ED.payment,'0')::numeric)) AS settle
                            FROM expense_master EM
                            LEFT OUTER JOIN expense_detail ED ON ED.expense_master_objid = EM.expense_master_objid
                            WHERE EM.project_mgmt_objid = PM.objid::text
                            GROUP BY EM.expense_master_objid) s), 0)
            +
            COALESCE((SELECT SUM(COALESCE(NULLIF(total_supply_unit_price,'')::numeric, 0))
                      FROM purchase_order_master
                      WHERE contract_mgmt_objid = PM.objid AND status = 'approvalComplete'), 0)
          )
          / (SELECT (COALESCE(ICG.material_cost_goal,'0')::numeric + COALESCE(ICG.labor_cost_goal,'0')::numeric + COALESCE(ICG.expense_cost_goal,'0')::numeric)
             FROM input_cost_goal ICG WHERE ICG.contract_objid::text = PM.objid::text LIMIT 1) * 100, 1)
      END AS "TOTAL_INPUT_RATE",
      -- 이슈 통계 (planning_issue)
      (SELECT COUNT(1) FROM planning_issue WHERE project_objid = PM.objid::text AND status = 'release') AS "ISSUE_CNT",
      (SELECT COUNT(1) FROM planning_issue WHERE project_objid = PM.objid::text AND status = 'release'
         AND COALESCE(design_result,'') != '' AND COALESCE(design_date,'') != '') AS "COMP_CNT",
      ((SELECT COUNT(1) FROM planning_issue WHERE project_objid = PM.objid::text AND status = 'release')
       - (SELECT COUNT(1) FROM planning_issue WHERE project_objid = PM.objid::text AND status = 'release'
          AND COALESCE(design_result,'') != '' AND COALESCE(design_date,'') != '')) AS "MISS_CNT",
      CASE WHEN (SELECT COUNT(1) FROM planning_issue WHERE project_objid = PM.objid::text AND status = 'release') = 0 THEN 0
           ELSE ROUND(
             (SELECT COUNT(1) FROM planning_issue WHERE project_objid = PM.objid::text AND status = 'release'
              AND COALESCE(design_result,'') != '' AND COALESCE(design_date,'') != '')::numeric
             / (SELECT COUNT(1) FROM planning_issue WHERE project_objid = PM.objid::text AND status = 'release')::numeric * 100, 1)
      END AS "ISSUE_RATE",
      -- 출고 정보
      RM.release_date AS "RELEASE_DATE",
      CASE
        WHEN COALESCE(TRIM(RM.release_date),'') != '' THEN '출고'
        ELSE '미출고'
      END AS "RELEASE_STATUS_TITLE",
      -- 진척율 (단순화: WBS task 완료/전체)
      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 "TOTAL_RATE",
      -- 셋업 진척율
      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 AS "SETUP_RATE",
      TO_CHAR(PM.regdate, 'YYYY-MM-DD') AS "REGDATE"
    FROM project_mgmt PM
    LEFT OUTER JOIN release_mgmt RM
      ON RM.parent_objid = PM.objid::text
    WHERE ${where}
    ORDER BY PM.regdate DESC
  `;

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