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

// productionplanning.xml::workStatusByImployeeList / workStatusByImployeeNPList 대응
// 담당자별 작업현황 (프로젝트 + 비프로젝트 두 섹션)
export async function POST(request: NextRequest) {
  const user = await getSession();
  if (!user) return NextResponse.json({ success: false }, { status: 401 });

  const body = await request.json().catch(() => ({}));
  const projectNos: string[] = Array.isArray(body.project_nos)
    ? body.project_nos.filter(Boolean).map(String)
    : [];
  const year = body.year ? String(body.year) : "";
  const deptCode = body.busUsersDeptId ? String(body.busUsersDeptId) : "";
  const worker = body.worker ? String(body.worker) : "";

  // 프로젝트 섹션 쿼리
  const projConditions: string[] = ["1=1"];
  const projParams: unknown[] = [];
  let pIdx = 1;

  if (projectNos.length > 0) {
    const placeholders = projectNos.map(() => `$${pIdx++}`).join(",");
    projConditions.push(`T.objid IN (${placeholders})`);
    projParams.push(...projectNos);
  }
  if (deptCode) {
    projConditions.push(`COALESCE(
      (SELECT dept_code FROM user_info WHERE user_id = WD.worker_id),
      (SELECT dept_code FROM user_info_history WHERE user_id = WD.worker_id ORDER BY regdate DESC LIMIT 1)
    ) = $${pIdx++}`);
    projParams.push(deptCode);
  }
  if (worker) {
    projConditions.push(`WD.worker_id = $${pIdx++}`);
    projParams.push(worker);
  }

  // 연도 필터는 work_diary 서브쿼리 내부에 들어가야 함
  const projYearFilter = year ? `AND TO_CHAR(regdate, 'YYYY') = $${pIdx++}` : "";
  if (year) projParams.push(year);

  const projSql = `
    SELECT
      T.objid::text AS "OBJID",
      T.project_no AS "PROJECT_NO",
      T.project_name AS "PROJECT_NAME",
      WD.worker_id AS "WORKER_ID",
      COALESCE(
        (SELECT dept_name FROM user_info WHERE user_id = WD.worker_id),
        (SELECT dept_name FROM user_info_history WHERE user_id = WD.worker_id ORDER BY regdate DESC LIMIT 1),
        ''
      ) AS "WORKER_DEPT_NAME",
      COALESCE(
        (SELECT user_name FROM user_info WHERE user_id = WD.worker_id),
        (SELECT user_name FROM user_info_history WHERE user_id = WD.worker_id ORDER BY regdate DESC LIMIT 1),
        WD.worker_id
      ) AS "WORKER_USER_NAME",
      WD.work_hour AS "WORK_HOUR",
      ROUND((WD.work_hour::float / 8)::numeric, 1) AS "MAN_DAY",
      ROUND((WD.work_hour::float / 8 / 22)::numeric, 1) AS "MAN_MONTH"
    FROM project_mgmt AS T
    LEFT OUTER JOIN (
      SELECT contract_objid, worker_id, SUM(work_hour::numeric) AS work_hour
      FROM work_diary WD
      WHERE WD.contract_objid != '' AND WD.contract_objid IS NOT NULL
        AND status = 'complete'
        ${projYearFilter}
      GROUP BY contract_objid, worker_id
    ) WD ON WD.contract_objid = T.objid
    WHERE ${projConditions.join(" AND ")}
    ORDER BY SUBSTRING(T.project_no, POSITION('-' IN T.project_no) + 1) DESC
  `;

  // 비프로젝트 섹션 쿼리
  const npConditions: string[] = ["1=1"];
  const npParams: unknown[] = [];
  let nIdx = 1;
  if (deptCode) {
    npConditions.push(`COALESCE(
      (SELECT dept_code FROM user_info WHERE user_id = WD.worker_id),
      (SELECT dept_code FROM user_info_history WHERE user_id = WD.worker_id ORDER BY regdate DESC LIMIT 1)
    ) = $${nIdx++}`);
    npParams.push(deptCode);
  }
  if (worker) {
    npConditions.push(`WD.worker_id = $${nIdx++}`);
    npParams.push(worker);
  }
  const npYearFilter = year ? `AND TO_CHAR(regdate, 'YYYY') = $${nIdx++}` : "";
  if (year) npParams.push(year);

  const npSql = `
    SELECT
      WD.worker_id AS "WORKER_ID",
      COALESCE(
        (SELECT dept_name FROM user_info WHERE user_id = WD.worker_id),
        (SELECT dept_name FROM user_info_history WHERE user_id = WD.worker_id ORDER BY regdate DESC LIMIT 1),
        ''
      ) AS "WORKER_DEPT_NAME",
      COALESCE(
        (SELECT user_name FROM user_info WHERE user_id = WD.worker_id),
        (SELECT user_name FROM user_info_history WHERE user_id = WD.worker_id ORDER BY regdate DESC LIMIT 1),
        WD.worker_id
      ) AS "WORKER_USER_NAME",
      WD.work_hour AS "WORK_HOUR",
      ROUND((WD.work_hour::float / 8)::numeric, 1) AS "MAN_DAY",
      ROUND((WD.work_hour::float / 8 / 22)::numeric, 1) AS "MAN_MONTH"
    FROM (
      SELECT worker_id, SUM(work_hour::numeric) AS work_hour
      FROM work_diary WD
      WHERE (WD.contract_objid = '' OR WD.contract_objid IS NULL)
        AND status = 'complete'
        ${npYearFilter}
      GROUP BY worker_id
    ) WD
    WHERE ${npConditions.join(" AND ")}
  `;

  const [listRows, npRows] = await Promise.all([
    queryRows(projSql, projParams),
    queryRows(npSql, npParams),
  ]);

  const num = (v: unknown) => Number(v ?? 0) || 0;
  const round1 = (n: number) => Math.round(n * 10) / 10;
  let sumHour = 0;
  let sumDay = 0;
  let sumMonth = 0;
  for (const r of listRows) {
    const row = r as Record<string, unknown>;
    sumHour += num(row.WORK_HOUR);
    sumDay += num(row.MAN_DAY);
    sumMonth += num(row.MAN_MONTH);
  }
  const SUM_PRICE_MAP = {
    SUM_WORK_HOUR: round1(sumHour),
    SUM_MAN_DAY: round1(sumDay),
    SUM_MAN_MONTH: round1(sumMonth),
  };

  return NextResponse.json({ LIST: listRows, NP_LIST: npRows, SUM_PRICE_MAP });
}
