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

// productionplanning.xml::workStatusByProjectList 대응 - 프로젝트 작업현황
// part_bom_report 단위로 부서별 공수(설계/구매/영업/생관/생산/관리/외주) 집계
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)
    : [];

  if (projectNos.length === 0) {
    return NextResponse.json({ RESULTLIST: [], SUM_PRICE_MAP: {} });
  }

  const conditions: string[] = ["1=1"];
  const params: unknown[] = [];
  let idx = 1;

  if (body.year) {
    conditions.push(`TO_CHAR(T.REGDATE,'YYYY') = $${idx++}`);
    params.push(String(body.year));
  }

  const placeholders = projectNos.map(() => `$${idx++}`).join(",");
  conditions.push(`T.CONTRACT_OBJID IN (${placeholders})`);
  params.push(...projectNos);

  if (body.unit_code) {
    conditions.push(`T.UNIT_CODE = $${idx++}`);
    params.push(String(body.unit_code));
  }

  const where = conditions.join(" AND ");

  // 부서 그룹 매핑 (원본 partMng/productionplanning.xml 기준)
  // D=설계(DPT014,DPT006), PC=구매(DPT012,DPT004), S=영업(DPT016),
  // PM=생산관리(DPT003,DPT018), P=생산(DPT005,DPT023,DPT013), M=관리(DPT019,DPT001,DPT002)
  // O=외주(sourcing_type='outsourcing')
  const sql = `
    SELECT T.*,
      ROUND((T."WORK_HOUR"::float / 8)::numeric, 1) AS "MAN_DAY",
      ROUND((T."WORK_HOUR"::float / 8 / 22)::numeric, 1) AS "MAN_MONTH"
    FROM (
      SELECT
        T.objid::text AS "OBJID",
        T.customer_objid AS "CUSTOMER_OBJID",
        (SELECT supply_name FROM supply_mng AS O WHERE O.objid::varchar = T.customer_objid) AS "CUSTOMER_NAME",
        T.contract_objid AS "CONTRACT_OBJID",
        (SELECT customer_project_name FROM project_mgmt AS O WHERE O.objid = T.contract_objid) AS "CUSTOMER_PROJECT_NAME",
        (SELECT project_name FROM project_mgmt AS O WHERE O.objid = T.contract_objid) AS "PROJECT_NAME",
        (SELECT project_no FROM project_mgmt AS O WHERE O.objid = T.contract_objid) AS "PROJECT_NO",
        T.unit_code AS "UNIT_CODE",
        (SELECT O.unit_no || '-' || O.task_name FROM pms_wbs_task AS O WHERE O.objid = T.unit_code) AS "UNIT_NAME",
        T.status AS "STATUS",
        CASE UPPER(T.status)
          WHEN 'CREATE' THEN '등록중'
          WHEN 'CHANGEDESIGN' THEN '설계변경미배포'
          WHEN 'DEPLOY' THEN '배포완료'
          ELSE ''
        END AS "STATUS_TITLE",
        T.regdate AS "REGDATE",
        TO_CHAR(T.regdate, 'YYYY-MM-DD') AS "REG_DATE",
        COALESCE(D.work_hour, '0') AS "DESIGN_INPUT",
        COALESCE(PC.work_hour, '0') AS "PURCHASE_INPUT",
        COALESCE(S.work_hour, '0') AS "SALES_INPUT",
        COALESCE(PM.work_hour, '0') AS "PRODUCTION_MGMT_INPUT",
        COALESCE(P.work_hour, '0') AS "PRODUCTION_INPUT",
        COALESCE(M.work_hour, '0') AS "MGMT_INPUT",
        COALESCE(O.work_hour, '0') AS "OUTSOURCING",
        (COALESCE(D.work_hour,'0')::numeric
          + COALESCE(PC.work_hour,'0')::numeric
          + COALESCE(S.work_hour,'0')::numeric
          + COALESCE(PM.work_hour,'0')::numeric
          + COALESCE(P.work_hour,'0')::numeric
          + COALESCE(M.work_hour,'0')::numeric
          + COALESCE(O.work_hour,'0')::numeric
        ) AS "WORK_HOUR"
      FROM part_bom_report AS T
      LEFT OUTER JOIN (
        SELECT contract_objid, unit_code, 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'
          AND (SELECT dept_code FROM user_info WHERE user_id = WD.worker_id) IN ('DPT014','DPT006')
        GROUP BY contract_objid, unit_code
      ) D ON D.unit_code = T.unit_code AND D.contract_objid = T.contract_objid
      LEFT OUTER JOIN (
        SELECT contract_objid, unit_code, 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'
          AND (SELECT dept_code FROM user_info WHERE user_id = WD.worker_id) IN ('DPT012','DPT004')
        GROUP BY contract_objid, unit_code
      ) PC ON PC.unit_code = T.unit_code AND PC.contract_objid = T.contract_objid
      LEFT OUTER JOIN (
        SELECT contract_objid, unit_code, 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'
          AND (SELECT dept_code FROM user_info WHERE user_id = WD.worker_id) = 'DPT016'
        GROUP BY contract_objid, unit_code
      ) S ON S.unit_code = T.unit_code AND S.contract_objid = T.contract_objid
      LEFT OUTER JOIN (
        SELECT contract_objid, unit_code, 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'
          AND (SELECT dept_code FROM user_info WHERE user_id = WD.worker_id) IN ('DPT003','DPT018')
        GROUP BY contract_objid, unit_code
      ) PM ON PM.unit_code = T.unit_code AND PM.contract_objid = T.contract_objid
      LEFT OUTER JOIN (
        SELECT contract_objid, unit_code, 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'
          AND (SELECT dept_code FROM user_info WHERE user_id = WD.worker_id) IN ('DPT005','DPT023','DPT013')
        GROUP BY contract_objid, unit_code
      ) P ON P.unit_code = T.unit_code AND P.contract_objid = T.contract_objid
      LEFT OUTER JOIN (
        SELECT contract_objid, unit_code, 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'
          AND (SELECT dept_code FROM user_info WHERE user_id = WD.worker_id) IN ('DPT019','DPT001','DPT002')
        GROUP BY contract_objid, unit_code
      ) M ON M.unit_code = T.unit_code AND M.contract_objid = T.contract_objid
      LEFT OUTER JOIN (
        SELECT contract_objid, unit_code, 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'
          AND sourcing_type = 'outsourcing'
        GROUP BY contract_objid, unit_code
      ) O ON O.unit_code = T.unit_code AND O.contract_objid = T.contract_objid
    ) T
    WHERE ${where}
    ORDER BY T."UNIT_CODE"
  `;

  const rows = await queryRows(sql, params);

  const num = (v: unknown) => Number(v ?? 0) || 0;
  const sum = {
    SUM_DESIGN_INPUT: 0,
    SUM_PURCHASE_INPUT: 0,
    SUM_SALES_INPUT: 0,
    SUM_PRODUCTION_MGMT_INPUT: 0,
    SUM_PRODUCTION_INPUT: 0,
    SUM_MGMT_INPUT: 0,
    SUM_OUTSOURCING: 0,
    SUM_WORK_HOUR: 0,
    SUM_MAN_DAY: 0,
    SUM_MAN_MONTH: 0,
  };
  for (const r of rows) {
    const row = r as Record<string, unknown>;
    sum.SUM_DESIGN_INPUT += num(row.DESIGN_INPUT);
    sum.SUM_PURCHASE_INPUT += num(row.PURCHASE_INPUT);
    sum.SUM_SALES_INPUT += num(row.SALES_INPUT);
    sum.SUM_PRODUCTION_MGMT_INPUT += num(row.PRODUCTION_MGMT_INPUT);
    sum.SUM_PRODUCTION_INPUT += num(row.PRODUCTION_INPUT);
    sum.SUM_MGMT_INPUT += num(row.MGMT_INPUT);
    sum.SUM_OUTSOURCING += num(row.OUTSOURCING);
    sum.SUM_WORK_HOUR += num(row.WORK_HOUR);
    sum.SUM_MAN_DAY += num(row.MAN_DAY);
    sum.SUM_MAN_MONTH += num(row.MAN_MONTH);
  }
  const round1 = (n: number) => Math.round(n * 10) / 10;
  const SUM_PRICE_MAP = {
    SUM_DESIGN_INPUT: round1(sum.SUM_DESIGN_INPUT),
    SUM_PURCHASE_INPUT: round1(sum.SUM_PURCHASE_INPUT),
    SUM_SALES_INPUT: round1(sum.SUM_SALES_INPUT),
    SUM_PRODUCTION_MGMT_INPUT: round1(sum.SUM_PRODUCTION_MGMT_INPUT),
    SUM_PRODUCTION_INPUT: round1(sum.SUM_PRODUCTION_INPUT),
    SUM_MGMT_INPUT: round1(sum.SUM_MGMT_INPUT),
    SUM_OUTSOURCING: round1(sum.SUM_OUTSOURCING),
    SUM_WORK_HOUR: round1(sum.SUM_WORK_HOUR),
    SUM_MAN_DAY: round1(sum.SUM_MAN_DAY),
    SUM_MAN_MONTH: round1(sum.SUM_MAN_MONTH),
  };

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