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

// 생산관리 현황 (planningdashboardGridList 원본 1:1 포팅)
// 주 테이블: project_mgmt
// 이슈: planning_issue (status='release', design_result/design_date 완료 여부)
// 셋업: setup_wbs_task (parent_objid 있는 것만)
// 조립: part_bom_report + bom_part_qty + assembly_wbs_task (BOM 기반 완료율)
// 투입공수: work_diary (production_type='assemble'/'setup', 생산/외주 분리)
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
      assembly_rate AS (
        SELECT pbr.contract_objid,
               SUM(
                 CASE WHEN bom_cnt.denom = 0 THEN 0
                      ELSE ROUND((awt_cnt.numer::numeric / bom_cnt.denom) * 100, 1)
                 END
               ) AS as_rate_sum
          FROM part_bom_report pbr
          LEFT JOIN LATERAL (
            SELECT COUNT(1) AS numer
              FROM assembly_wbs_task a
             WHERE a.parent_objid = pbr.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 <> ''
          ) awt_cnt ON TRUE
          LEFT JOIN LATERAL (
            SELECT COUNT(*) AS denom
              FROM bom_part_qty bp
              LEFT JOIN part_mng pm ON bp.part_no = pm.objid::varchar
             WHERE bp.bom_report_objid = pbr.objid
               AND pm.part_type IS NOT NULL AND pm.part_type <> ''
          ) bom_cnt ON TRUE
         GROUP BY pbr.contract_objid
      ),
      assembly_end AS (
        SELECT pbr.contract_objid,
               MAX(a.assembly_date) AS assembly_date_end
          FROM assembly_wbs_task a
          LEFT JOIN part_bom_report pbr ON a.parent_objid = pbr.objid
         WHERE pbr.contract_objid IS NOT NULL
         GROUP BY pbr.contract_objid
      ),
      pms_cnt AS (
        SELECT contract_objid, COUNT(1) AS cnt FROM pms_wbs_task GROUP BY contract_objid
      ),
      wd_prod_assemble 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.contract_objid IS NOT NULL AND wd.contract_objid <> ''
           AND wd.status = 'complete' AND wd.production_type = 'assemble'
           AND ui.dept_code IN ('DPT005','DPT023','DPT013')
         GROUP BY wd.contract_objid
      ),
      wd_out_assemble AS (
        SELECT contract_objid, SUM(COALESCE(NULLIF(work_hour,''),'0')::numeric) AS hour
          FROM work_diary
         WHERE contract_objid IS NOT NULL AND contract_objid <> ''
           AND status = 'complete' AND sourcing_type = 'outsourcing' AND production_type = 'assemble'
         GROUP BY contract_objid
      ),
      wd_prod_setup 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.contract_objid IS NOT NULL AND wd.contract_objid <> ''
           AND wd.status = 'complete' AND wd.production_type = 'setup'
           AND ui.dept_code IN ('DPT005','DPT023','DPT013')
         GROUP BY wd.contract_objid
      ),
      wd_out_setup AS (
        SELECT contract_objid, SUM(COALESCE(NULLIF(work_hour,''),'0')::numeric) AS hour
          FROM work_diary
         WHERE contract_objid IS NOT NULL AND contract_objid <> ''
           AND status = 'complete' AND sourcing_type = 'outsourcing' AND production_type = 'setup'
         GROUP BY contract_objid
      ),
      issue_agg AS (
        SELECT project_objid,
               COUNT(1) AS total,
               COUNT(1) FILTER (WHERE COALESCE(design_result,'') <> '' AND COALESCE(design_date,'') <> '') AS comp
          FROM planning_issue
         WHERE status = 'release'
         GROUP BY project_objid
      ),
      setup_agg AS (
        SELECT contract_objid,
               COUNT(1) AS total,
               COUNT(1) FILTER (WHERE COALESCE(setup_act_end,'') <> '') AS comp,
               MAX(setup_act_end) AS act_end
          FROM setup_wbs_task
         WHERE COALESCE(parent_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",
      -- 조립
      CASE WHEN COALESCE(PC.cnt, 0) = 0 THEN 0
           ELSE ROUND(COALESCE(AR.as_rate_sum, 0) / PC.cnt, 1)
      END AS "ASSEMBLY_RATE",
      COALESCE(AE.assembly_date_end, '') AS "ASSEMBLY_DATE_END",
      (COALESCE(WPA.hour, 0) + COALESCE(WOA.hour, 0)) AS "ASSEMBLY_EMPLOYEES_TOTAL",
      -- 셋업
      CASE WHEN COALESCE(SA.total, 0) = 0 THEN 0
           ELSE ROUND((SA.comp::numeric / SA.total) * 100, 1)
      END AS "SETUP_RATE",
      COALESCE(SA.act_end, '') AS "SETUP_ACT_END",
      (COALESCE(WPS.hour, 0) + COALESCE(WOS.hour, 0)) AS "EMPLOYEES_TOTAL",
      -- 이슈
      COALESCE(IA.total, 0) AS "ISSUE_CNT",
      COALESCE(IA.comp, 0) AS "COMP_CNT",
      (COALESCE(IA.total, 0) - COALESCE(IA.comp, 0)) AS "MISS_CNT",
      CASE WHEN COALESCE(IA.total, 0) = 0 THEN 0
           ELSE ROUND((IA.comp::numeric / IA.total) * 100, 1)
      END AS "ISSUE_RATE"
    FROM project_mgmt T
    LEFT JOIN assembly_rate AR ON AR.contract_objid = T.objid::text
    LEFT JOIN assembly_end AE ON AE.contract_objid = T.objid::text
    LEFT JOIN pms_cnt PC ON PC.contract_objid = T.objid::text
    LEFT JOIN wd_prod_assemble WPA ON WPA.contract_objid = T.objid::text
    LEFT JOIN wd_out_assemble WOA ON WOA.contract_objid = T.objid::text
    LEFT JOIN wd_prod_setup WPS ON WPS.contract_objid = T.objid::text
    LEFT JOIN wd_out_setup WOS ON WOS.contract_objid = T.objid::text
    LEFT JOIN issue_agg IA ON IA.project_objid = T.objid::text
    LEFT JOIN setup_agg SA ON SA.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 });
}
