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

// 생산관리_생산계획수립 (planningGridList 원본 1:1 포팅)
// 주 테이블: project_mgmt
// WBS_CNT: pms_wbs_task에서 produce_plan/act 또는 produce_user_id 하나라도 채워진 것
// SETUP_WBS_CNT: setup_wbs_task에서 setup_plan/act 하나라도 채워진 것
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",
      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",
      -- 조립 WBS (pms_wbs_task)
      COALESCE((SELECT COUNT(1) FROM pms_wbs_task
                 WHERE contract_objid = T.objid::text
                   AND (COALESCE(produce_plan_start,'') <> '' OR COALESCE(produce_plan_end,'') <> ''
                     OR COALESCE(produce_act_start,'') <> '' OR COALESCE(produce_act_end,'') <> ''
                     OR COALESCE(produce_user_id,'') <> '')
               ), 0) AS "WBS_CNT",
      COALESCE((SELECT MIN(produce_plan_start) FROM pms_wbs_task
                 WHERE contract_objid = T.objid::text AND COALESCE(produce_plan_start,'') <> ''), '') AS "PRODUCE_PLAN_START",
      COALESCE((SELECT MAX(produce_plan_end) FROM pms_wbs_task
                 WHERE contract_objid = T.objid::text AND COALESCE(produce_plan_end,'') <> ''), '') AS "PRODUCE_PLAN_END",
      COALESCE((SELECT MIN(produce_act_start) FROM pms_wbs_task
                 WHERE contract_objid = T.objid::text AND COALESCE(produce_act_start,'') <> ''), '') AS "PRODUCE_ACT_START",
      COALESCE((SELECT MAX(produce_act_end) FROM pms_wbs_task
                 WHERE contract_objid = T.objid::text AND COALESCE(produce_act_end,'') <> ''), '') AS "PRODUCE_ACT_END",
      -- 셋업 WBS (setup_wbs_task)
      COALESCE((SELECT COUNT(1) FROM setup_wbs_task
                 WHERE contract_objid = T.objid::text
                   AND (COALESCE(setup_plan_start,'') <> '' OR COALESCE(setup_plan_end,'') <> ''
                     OR COALESCE(setup_act_start,'') <> '' OR COALESCE(setup_act_end,'') <> '')
               ), 0) AS "SETUP_WBS_CNT",
      COALESCE((SELECT MIN(setup_plan_start) FROM setup_wbs_task
                 WHERE contract_objid = T.objid::text AND COALESCE(setup_plan_start,'') <> ''), '') AS "SETUP_PLAN_START",
      COALESCE((SELECT MAX(setup_plan_end) FROM setup_wbs_task
                 WHERE contract_objid = T.objid::text AND COALESCE(setup_plan_end,'') <> ''), '') AS "SETUP_PLAN_END",
      COALESCE((SELECT MIN(setup_act_start) FROM setup_wbs_task
                 WHERE contract_objid = T.objid::text AND COALESCE(setup_act_start,'') <> ''), '') AS "SETUP_ACT_START",
      COALESCE((SELECT MAX(setup_act_end) FROM setup_wbs_task
                 WHERE contract_objid = T.objid::text AND COALESCE(setup_act_end,'') <> ''), '') AS "SETUP_ACT_END"
    FROM project_mgmt T
    WHERE ${where}
    ORDER BY SUBSTRING(T.project_no, POSITION('-' IN T.project_no)+1) DESC
  `;

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