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

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[] = [];
  const params: unknown[] = [];
  let idx = 1;

  if (body.year) {
    conditions.push(`TO_CHAR(EM.reg_date, 'YYYY') = $${idx++}`);
    params.push(body.year);
  }
  if (body.writer_name) {
    conditions.push(`(SELECT user_name FROM user_info WHERE user_id = EM.reg_user_id LIMIT 1) LIKE '%' || $${idx++} || '%'`);
    params.push(body.writer_name);
  } else if (body.reg_user_id) {
    conditions.push(`EM.reg_user_id = $${idx++}`);
    params.push(body.reg_user_id);
  }
  if (body.status_code || body.exp_status_cd) {
    conditions.push(`EM.exp_status_cd = $${idx++}`);
    params.push(body.status_code || body.exp_status_cd);
  }
  if (body.expense_date_from || body.bns_start_date) {
    conditions.push(`EM.bns_start_date >= $${idx++}`);
    params.push(body.expense_date_from || body.bns_start_date);
  }
  if (body.expense_date_to || body.bns_end_date) {
    conditions.push(`EM.bns_end_date <= $${idx++}`);
    params.push(body.expense_date_to || body.bns_end_date);
  }

  const whereClause = conditions.length > 0 ? "AND " + conditions.join(" AND ") : "";

  const sql = `
    SELECT EM.expense_master_objid::text AS "OBJID", EM.expense_id AS "EXPENSE_ID",
      CM.contract_no AS "PROJECT_NO", CM.project_name AS "PROJECT_NAME",
      COALESCE((SELECT user_name FROM user_info WHERE user_id = EM.reg_user_id LIMIT 1), '') AS "REG_USER_NAME",
      EM.bns_start_date AS "BNS_START_DATE",
      EM.bns_end_date AS "BNS_END_DATE",
      COALESCE((SELECT code_name FROM comm_code WHERE code_id = EM.exp_status_cd LIMIT 1), '') AS "EXP_STATUS_NAME",
      COALESCE((SELECT code_name FROM comm_code WHERE code_id = EM.exp_area_cd LIMIT 1), '') AS "EXP_AREA_NAME",
      COALESCE((SELECT code_name FROM comm_code WHERE code_id = EM.exp_sort_cd LIMIT 1), '') AS "EXP_SORT_NAME",
      EM.bus_title AS "BUS_TITLE", EM.bus_content AS "BUS_CONTENT",
      EM.vehicel_used AS "VEHICEL_USED",
      COALESCE(EM.amount_payment::numeric, 0) AS "AMOUNT_PAYMENT",
      EM.expense_id AS "EXPENSE_FORM_NO",
      EM.bns_start_date AS "EXPENSE_DATE",
      COALESCE((SELECT code_name FROM comm_code WHERE code_id = EM.exp_sort_cd LIMIT 1), '') AS "EXPENSE_TYPE_NAME",
      COALESCE(EM.amount_payment::numeric, 0) AS "EXPENSE_AMOUNT",
      EM.bus_title AS "EXPENSE_PLACE",
      COALESCE((SELECT code_name FROM comm_code WHERE code_id = EM.status LIMIT 1), EM.status) AS "STATUS_NAME",
      COALESCE((SELECT user_name FROM user_info WHERE user_id = EM.reg_user_id LIMIT 1), '') AS "WRITER_NAME",
      EM.status AS "STATUS",
      EM.reg_date AS "REG_DATE",
      COALESCE((SELECT SUM(COALESCE(ED.payment,'0')::numeric) FROM expense_detail ED WHERE ED.expense_master_objid = EM.expense_master_objid), 0) AS "DETAIL_TOTAL"
    FROM expense_master EM
    LEFT JOIN contract_mgmt CM ON CM.objid::text = EM.project_mgmt_objid
    WHERE 1=1 ${whereClause}
    ORDER BY EM.reg_date DESC
  `;

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