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

// 노무비관리 (costTotaltGridList의 LC 노무비 계산 부분 포팅)
// LABOR_COST_ACTUAL = LABOR_DESIGN_COST + LABOR_ASSEMBLY_COST
//   설계: PMS_WBS_TASK (design_act_end - design_act_start) × 300000
//   조립 insourcing: WORK_DIARY dept DPT005/013/023 work_hour/8 × 250000
//   조립 outsourcing: WORK_DIARY sourcing_type='outsourcing' work_hour/8 × 350000
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
      labor_design AS (
        SELECT contract_objid,
               (MAX(TO_DATE(NULLIF(design_act_end,''),'YYYY-MM-DD'))
                - MIN(TO_DATE(NULLIF(design_act_start,''),'YYYY-MM-DD'))) * 300000 AS cost
          FROM pms_wbs_task
         WHERE contract_objid IS NOT NULL
           AND NULLIF(design_act_start,'') IS NOT NULL
           AND NULLIF(design_act_end,'') IS NOT NULL
         GROUP BY contract_objid
      ),
      labor_in AS (
        SELECT wd.contract_objid,
               SUM(COALESCE(NULLIF(wd.work_hour,''),'0')::numeric) AS hour
          FROM work_diary wd
          JOIN user_info ui ON ui.user_id = wd.worker_id
         WHERE wd.status = 'complete'
           AND wd.contract_objid IS NOT NULL AND wd.contract_objid <> ''
           AND ui.dept_code IN ('DPT005','DPT023','DPT013')
         GROUP BY wd.contract_objid
      ),
      labor_out AS (
        SELECT contract_objid,
               SUM(COALESCE(NULLIF(work_hour,''),'0')::numeric) AS hour
          FROM work_diary
         WHERE status = 'complete'
           AND sourcing_type = 'outsourcing'
           AND contract_objid IS NOT NULL AND contract_objid <> ''
         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(NULLIF(ICG.labor_cost_goal,'')::numeric, 0) AS "LABOR_COST_GOAL",
      (COALESCE(LBD.cost, 0)
       + COALESCE(LI.hour, 0) / 8 * 250000
       + COALESCE(LO.hour, 0) / 8 * 350000) AS "LABOR_COST_ACTUAL",
      (COALESCE(LI.hour, 0) + COALESCE(LO.hour, 0)) AS "LABOR_HOURS"
    FROM project_mgmt T
    LEFT JOIN input_cost_goal ICG ON ICG.contract_objid = T.objid::text
    LEFT JOIN labor_design LBD ON LBD.contract_objid = T.objid::text
    LEFT JOIN labor_in LI ON LI.contract_objid = T.objid::text
    LEFT JOIN labor_out LO ON LO.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);

  const result = rows.map((r: Record<string, unknown>) => {
    const goal = Number(r.LABOR_COST_GOAL || 0);
    const actual = Number(r.LABOR_COST_ACTUAL || 0);
    return {
      ...r,
      LABOR_INPUT_RATE: goal > 0 ? Math.round((actual / goal) * 1000) / 10 : 0,
    };
  });

  return NextResponse.json({ RESULTLIST: result, TOTAL_CNT: result.length });
}
