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

// 경비관리 (expenseDashBoardGrid 원본 기반)
// 주 테이블: project_mgmt. SETTLE_AMOUNT = CARD_USED + CASH_USED - PAYMENT
// exp_status_cd별: 0001548 조립, 0001549 셋업, 0001629 외주(Turn-key)
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.pm_user_id) {
    conditions.push(`T.pm_user_id = $${idx++}`);
    params.push(body.pm_user_id);
  }

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

  const sql = `
    WITH settle AS (
      SELECT
        M.project_mgmt_objid,
        M.exp_status_cd,
        SUM(COALESCE(NULLIF(D.card_used, '')::numeric, 0)
          + COALESCE(NULLIF(D.cash_used, '')::numeric, 0)
          - COALESCE(NULLIF(D.payment, '')::numeric, 0)) AS settle_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, M.exp_status_cd
    )
    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((SELECT NULLIF(expense_cost_goal,'')::numeric FROM input_cost_goal WHERE contract_objid = T.objid::text LIMIT 1), 0) AS "EXPENSE_COST_GOAL",
      COALESCE((SELECT settle_amount FROM settle WHERE project_mgmt_objid = T.objid::text AND exp_status_cd = '0001548'), 0) AS "SETTLE_AMOUNT_ASSEMBLE",
      COALESCE((SELECT settle_amount FROM settle WHERE project_mgmt_objid = T.objid::text AND exp_status_cd = '0001549'), 0) AS "SETTLE_AMOUNT_SETUP",
      COALESCE((SELECT settle_amount FROM settle WHERE project_mgmt_objid = T.objid::text AND exp_status_cd = '0001629'), 0) AS "SETTLE_AMOUNT_CS",
      COALESCE((SELECT SUM(settle_amount) FROM settle WHERE project_mgmt_objid = T.objid::text), 0) AS "TOTAL_SETTLE_AMOUNT"
    FROM project_mgmt T
    WHERE ${where}
    ORDER BY SUBSTRING(T.project_no, POSITION('-' IN T.project_no)+1) DESC
  `;

  const rows = await queryRows(sql, params);

  const result = rows.map((r: Record<string, unknown>) => {
    const goal = Number(r.EXPENSE_COST_GOAL || 0);
    const total = Number(r.TOTAL_SETTLE_AMOUNT || 0);
    return {
      ...r,
      INPUT_RATE: goal > 0 ? Math.round((total / goal) * 1000) / 10 : 0,
    };
  });

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