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

// 생산관리_셋업관리 (selectSetupMgmtList 원본 1:1 포팅)
// 주 테이블: project_mgmt
// TASK_CNT = setup_wbs_task WHERE parent_objid 있는 것만
// COMPLETE_CNT = TASK_CNT 중 setup_act_end 있는 것
// SETUP_RATE = COMPLETE_CNT / TASK_CNT × 100
// EMPLOYEES_IN/OUT/TOTAL = setup_wbs_task.employees_in/out 합계
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 = `
    WITH
      setup_cnt AS (
        SELECT contract_objid,
               COUNT(1) FILTER (WHERE COALESCE(parent_objid,'') <> '') AS task_cnt,
               COUNT(1) FILTER (WHERE COALESCE(parent_objid,'') <> '' AND COALESCE(setup_act_end,'') <> '') AS complete_cnt,
               SUM(COALESCE(NULLIF(employees_in,'')::integer, 0)) FILTER (WHERE COALESCE(parent_objid,'') <> '') AS emp_in,
               SUM(COALESCE(NULLIF(employees_out,'')::integer, 0)) FILTER (WHERE COALESCE(parent_objid,'') <> '') AS emp_out
          FROM setup_wbs_task
         GROUP BY contract_objid
      )
    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",
      COALESCE(SC.task_cnt, 0) AS "TASK_CNT",
      COALESCE(SC.complete_cnt, 0) AS "COMPLETE_CNT",
      CASE WHEN COALESCE(SC.task_cnt, 0) = 0 THEN 0
           ELSE ROUND((SC.complete_cnt::numeric / SC.task_cnt) * 100, 1)
      END AS "SETUP_RATE",
      COALESCE(SC.emp_in, 0) AS "EMPLOYEES_IN",
      COALESCE(SC.emp_out, 0) AS "EMPLOYEES_OUT",
      (COALESCE(SC.emp_in, 0) + COALESCE(SC.emp_out, 0)) AS "EMPLOYEES_TOTAL"
    FROM project_mgmt T
    LEFT JOIN setup_cnt SC ON SC.contract_objid = T.objid::text
    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 });
}
