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

// 생산관리_공정실적관리 (processperformanceGridList 원본 1:1 포팅)
// 주 테이블: part_bom_report (STATUS='deploy')
// 조립 BOM_CNT, 조립품수 ASSING_CNT, 공정율 AS_RATE, 작업공수 WORK_DIARY 기반
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[] = ["T.status = 'deploy'"];
  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(`(SELECT project_no FROM project_mgmt WHERE objid = T.contract_objid) LIKE '%' || $${idx++} || '%'`);
    params.push(body.project_no);
  }
  if (body.customer_objid) {
    conditions.push(`T.customer_objid = $${idx++}`);
    params.push(body.customer_objid);
  }
  if (body.unit_code) {
    conditions.push(`T.unit_code = $${idx++}`);
    params.push(body.unit_code);
  }

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

  const sql = `
    SELECT T.objid::text AS "OBJID",
      T.contract_objid AS "CONTRACT_OBJID",
      T.customer_objid AS "CUSTOMER_OBJID",
      (SELECT supply_name FROM supply_mng WHERE objid::varchar = T.customer_objid LIMIT 1) AS "CUSTOMER_NAME",
      (SELECT project_no FROM project_mgmt WHERE objid = T.contract_objid) AS "PROJECT_NO",
      (SELECT project_name FROM project_mgmt WHERE objid = T.contract_objid) AS "PROJECT_NAME",
      (SELECT due_date FROM project_mgmt WHERE objid = T.contract_objid) AS "DUE_DATE",
      (SELECT setup FROM project_mgmt WHERE objid = T.contract_objid) AS "SETUP",
      (SELECT user_name FROM user_info WHERE user_id = (SELECT pm_user_id FROM project_mgmt WHERE objid = T.contract_objid) LIMIT 1) AS "PM_USER_NAME",
      T.unit_code AS "UNIT_CODE",
      (SELECT COALESCE(O.unit_no,'') || '-' || COALESCE(O.task_name,'') FROM pms_wbs_task O WHERE O.objid = T.unit_code LIMIT 1) AS "UNIT_NAME",
      -- 조립총수 (BOM_CNT)
      (SELECT COUNT(*) FROM bom_part_qty BP
         LEFT JOIN part_mng PM ON COALESCE(NULLIF(BP.last_part_objid,''), BP.part_no) = PM.objid::varchar
        WHERE BP.bom_report_objid = T.objid
          AND PM.part_type IS NOT NULL AND PM.part_type <> ''
          AND BP.status IN ('beforeEdit','editing','deleting','deploy')
      ) AS "BOM_CNT",
      -- 조립품수 (ASSING_CNT)
      (SELECT COUNT(1) FROM assembly_wbs_task A
         INNER JOIN bom_part_qty BP
           ON A.parent_objid = BP.bom_report_objid
          AND A.part_objid = COALESCE(NULLIF(BP.last_part_objid,''), BP.part_no)
          AND A.bom_qty_child_objid = BP.child_objid
          AND BP.status IN ('beforeEdit','editing','deleting','deploy')
        WHERE A.parent_objid = T.objid
          AND A.assembly_user_id IS NOT NULL AND A.assembly_user_id <> ''
          AND A.assembly_date IS NOT NULL AND A.assembly_date <> ''
      ) AS "ASSING_CNT",
      -- 공정율 (AS_RATE)
      CASE WHEN (SELECT COUNT(*) FROM bom_part_qty BP
                   LEFT JOIN part_mng PM ON COALESCE(NULLIF(BP.last_part_objid,''), BP.part_no) = PM.objid::varchar
                  WHERE BP.bom_report_objid = T.objid
                    AND PM.part_type IS NOT NULL AND PM.part_type <> ''
                    AND BP.status IN ('beforeEdit','editing','deleting','deploy')) = 0 THEN 0
           ELSE ROUND(
             ((SELECT COUNT(1) FROM assembly_wbs_task A
                 INNER JOIN bom_part_qty BP
                   ON A.parent_objid = BP.bom_report_objid
                  AND A.part_objid = COALESCE(NULLIF(BP.last_part_objid,''), BP.part_no)
                  AND A.bom_qty_child_objid = BP.child_objid
                  AND BP.status IN ('beforeEdit','editing','deleting','deploy')
                WHERE A.parent_objid = T.objid
                  AND A.assembly_user_id IS NOT NULL AND A.assembly_user_id <> ''
                  AND A.assembly_date IS NOT NULL AND A.assembly_date <> ''
             )::numeric
              / (SELECT COUNT(*) FROM bom_part_qty BP
                   LEFT JOIN part_mng PM ON COALESCE(NULLIF(BP.last_part_objid,''), BP.part_no) = PM.objid::varchar
                  WHERE BP.bom_report_objid = T.objid
                    AND PM.part_type IS NOT NULL AND PM.part_type <> ''
                    AND BP.status IN ('beforeEdit','editing','deleting','deploy'))
              * 100), 1)
      END AS "AS_RATE",
      -- 작업공수
      COALESCE(P.work_hour, 0) AS "INSOURCING_SUM",
      COALESCE(O.work_hour, 0) AS "OUTSOURCING_SUM",
      (COALESCE(P.work_hour, 0) + COALESCE(O.work_hour, 0)) AS "TOTAL_SUM"
    FROM part_bom_report T
    LEFT JOIN LATERAL (
      SELECT SUM(COALESCE(NULLIF(wd.work_hour,''),'0')::numeric) AS work_hour
        FROM work_diary wd
        JOIN user_info ui ON ui.user_id = wd.worker_id
       WHERE wd.contract_objid = T.contract_objid
         AND wd.unit_code = T.unit_code
         AND wd.status = 'complete'
         AND wd.production_type = 'assemble'
         AND ui.dept_code IN ('DPT005','DPT023','DPT013')
    ) P ON TRUE
    LEFT JOIN LATERAL (
      SELECT SUM(COALESCE(NULLIF(wd.work_hour,''),'0')::numeric) AS work_hour
        FROM work_diary wd
       WHERE wd.contract_objid = T.contract_objid
         AND wd.unit_code = T.unit_code
         AND wd.status = 'complete'
         AND wd.production_type = 'assemble'
         AND wd.sourcing_type = 'outsourcing'
    ) O ON TRUE
    WHERE ${where}
    ORDER BY (SELECT SUBSTRING(project_no, POSITION('-' IN project_no)+1) FROM project_mgmt WHERE objid = T.contract_objid) DESC
  `;

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