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

// 투입원가현황 (costTotaltGridList 원본 1:1 포팅)
// 주 테이블: project_mgmt
// 재료비 실적 = BOM(SALES_BOM_REPORT_PART price × 누적수량) + 재발주(0001408) + 장납기
// 노무비 실적 = 설계(PMS_WBS_TASK 기간×300K) + 조립(WORK_DIARY DPT005/013/023 work_hour/8*250K + sourcing_type='outsourcing' /8*350K)
// 경비 실적 = EXPENSE_DETAIL CARD+CASH-PAYMENT
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 RECURSIVE view_bom AS (
      SELECT pbr.contract_objid,
             a.bom_report_objid,
             a.child_objid,
             1 AS lev,
             ARRAY[a.child_objid::text] AS path,
             false AS cycle,
             COALESCE(NULLIF(a.qty,''),'0')::numeric AS aggregate_qty
        FROM part_bom_report pbr
        JOIN bom_part_qty a ON pbr.objid = a.bom_report_objid
       WHERE (a.parent_objid IS NULL OR a.parent_objid = '')
      UNION ALL
      SELECT v.contract_objid,
             b.bom_report_objid,
             b.child_objid,
             v.lev + 1,
             v.path || b.child_objid::text,
             b.parent_objid = ANY(v.path),
             v.aggregate_qty * COALESCE(NULLIF(b.qty,''),'0')::numeric
        FROM bom_part_qty b
        JOIN view_bom v ON b.parent_objid = v.child_objid
                       AND v.bom_report_objid = b.bom_report_objid
       WHERE NOT v.cycle
    ),
    bom_material AS (
      SELECT pbr.contract_objid,
             SUM((COALESCE(NULLIF(sp.price,''),'0')::numeric
                + COALESCE(NULLIF(sp.price1,''),'0')::numeric
                + COALESCE(NULLIF(sp.price2,''),'0')::numeric
                + COALESCE(NULLIF(sp.price3,''),'0')::numeric
                + COALESCE(NULLIF(sp.price4,''),'0')::numeric) * v.aggregate_qty) AS all_total_price
        FROM part_bom_report pbr
        JOIN sales_bom_report sb ON sb.parent_objid = pbr.objid
        JOIN sales_bom_report_part sp ON sp.parent_objid = pbr.objid
        JOIN view_bom v ON v.child_objid = sp.bom_part_qty_objid
       GROUP BY pbr.contract_objid
    ),
    re_order AS (
      SELECT contract_mgmt_objid,
             SUM(COALESCE(NULLIF(total_supply_unit_price,'')::numeric, 0)) AS total
        FROM purchase_order_master
       WHERE status = 'approvalComplete' AND order_type_cd = '0001408'
         AND contract_mgmt_objid IS NOT NULL
       GROUP BY contract_mgmt_objid
    ),
    long_delivery AS (
      SELECT li.contract_objid,
             SUM(NULLIF(li.input_qty,'')::numeric * NULLIF(l.price,'')::numeric) AS total
        FROM sales_long_delivery_input li
        JOIN sales_long_delivery l ON l.objid = li.parent_objid
       WHERE NULLIF(li.input_qty,'') IS NOT NULL
         AND NULLIF(l.price,'') IS NOT NULL
       GROUP BY li.contract_objid
    ),
    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
    ),
    expense_settle AS (
      SELECT M.project_mgmt_objid,
             SUM(COALESCE(NULLIF(D.card_used,'')::numeric, 0)
               + COALESCE(NULLIF(D.cash_used,'')::numeric, 0)
               - COALESCE(NULLIF(D.payment,'')::numeric, 0)) AS amount
        FROM expense_master M
        LEFT JOIN expense_detail D ON M.expense_master_objid = D.expense_master_objid
       GROUP BY M.project_mgmt_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(T.contract_price,'')::numeric, 0) AS "CONTRACT_PRICE",
      COALESCE(NULLIF(ICG.material_cost_goal,'')::numeric, 0) AS "MATERIAL_COST_GOAL",
      COALESCE(NULLIF(ICG.labor_cost_goal,'')::numeric, 0) AS "LABOR_COST_GOAL",
      COALESCE(NULLIF(ICG.expense_cost_goal,'')::numeric, 0) AS "EXPENSE_COST_GOAL",
      (COALESCE(NULLIF(ICG.material_cost_goal,'')::numeric, 0)
       + COALESCE(NULLIF(ICG.labor_cost_goal,'')::numeric, 0)
       + COALESCE(NULLIF(ICG.expense_cost_goal,'')::numeric, 0)) AS "TOTAL_COST_GOAL",
      (COALESCE(BM.all_total_price, 0)
       + COALESCE(RO.total, 0)
       + COALESCE(LD.total, 0)) AS "ACCRUAL_MATERIAL_COST",
      (COALESCE(LBD.cost, 0)
       + COALESCE(LI.hour, 0) / 8 * 250000
       + COALESCE(LO.hour, 0) / 8 * 350000) AS "LABOR_COST_ACTUAL",
      COALESCE(ES.amount, 0) AS "ACCRUAL_EXPENSE"
    FROM project_mgmt T
    LEFT JOIN input_cost_goal ICG ON ICG.contract_objid = T.objid::text
    LEFT JOIN bom_material BM ON BM.contract_objid = T.objid::text
    LEFT JOIN re_order RO ON RO.contract_mgmt_objid = T.objid::text
    LEFT JOIN long_delivery LD ON LD.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
    LEFT JOIN expense_settle ES ON ES.project_mgmt_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);

  // 원본 공식 그대로:
  // TOTAL_COST_ACTUAL = 재료비실적 + 노무비실적 + 경비실적
  // TOTAL_INPUT_RATE = TOTAL_ACTUAL / TOTAL_GOAL × 100
  // MC_RATE = 재료비실적 / TOTAL_GOAL × 100 (실제 원본 SQL: 실투입재료 / 목표합계)
  // MATERIAL_COST_GOAL_RATE = 재료비실적 / CONTRACT_PRICE × 100
  // LABOR_INPUT_RATE = 노무비실적 / 노무비목표 × 100
  // EXPENSE_RATE = 경비실적 / 경비목표 × 100
  const result = rows.map((r: Record<string, unknown>) => {
    const tg = Number(r.TOTAL_COST_GOAL || 0);
    const lg = Number(r.LABOR_COST_GOAL || 0);
    const eg = Number(r.EXPENSE_COST_GOAL || 0);
    const ma = Number(r.ACCRUAL_MATERIAL_COST || 0);
    const la = Number(r.LABOR_COST_ACTUAL || 0);
    const ea = Number(r.ACCRUAL_EXPENSE || 0);
    const ta = ma + la + ea;
    const cp = Number(r.CONTRACT_PRICE || 0);
    return {
      ...r,
      TOTAL_COST_ACTUAL: ta,
      TOTAL_INPUT_RATE: tg > 0 ? Math.round((ta / tg) * 1000) / 10 : 0,
      MC_RATE: tg > 0 ? Math.round((ma / tg) * 1000) / 10 : 0,
      MATERIAL_COST_GOAL_RATE: cp > 0 ? Math.round((ma / cp) * 1000) / 10 : 0,
      LABOR_INPUT_RATE: lg > 0 ? Math.round((la / lg) * 1000) / 10 : 0,
      EXPENSE_RATE: eg > 0 ? Math.round((ea / eg) * 1000) / 10 : 0,
    };
  });

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