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

// 결재함 목록 (selectApprovalList 대응)
// status: PENDING(ready/standby) | APPROVED(complete) | REJECTED(reject) | "" (전체)
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 systemType = body.system_type || "PLM";
  const conditions: string[] = [
    "A.objid = R.approval_objid",
    "A.system_type = $1",
    "IT.approval_objid = A.objid",
    "IT.route_objid = R.objid",
  ];
  const params: unknown[] = [systemType];
  let idx = 2;

  // 원본: target_user_id = connectUserId (내 결재건만, 관리자는 전체)
  if (!user.isAdmin) {
    conditions.push(`IT.target_user_id = $${idx++}`);
    params.push(user.userId);
  }

  // 원본: status 필터
  if (body.status === "PENDING") {
    conditions.push(`UPPER(IT.status) = 'READY'`);
    conditions.push(`R.status = 'inProcess'`);
  } else if (body.status === "APPROVED") {
    conditions.push(`UPPER(A.status) = 'COMPLETE'`);
  } else if (body.status === "REJECTED") {
    conditions.push(`UPPER(A.status) = 'REJECT'`);
  }

  if (body.title) {
    conditions.push(`UPPER(R.approval_title) LIKE UPPER('%' || $${idx++} || '%')`);
    params.push(body.title);
  }
  if (body.writer_name) {
    conditions.push(`UPPER((SELECT user_name FROM user_info WHERE user_id = R.writer)) LIKE UPPER('%' || $${idx++} || '%')`);
    params.push(body.writer_name);
  }
  if (body.from_date) {
    conditions.push(`R.regdate >= $${idx++}::date`);
    params.push(body.from_date);
  }
  if (body.to_date) {
    conditions.push(`R.regdate <= $${idx++}::date`);
    params.push(body.to_date);
  }

  // 원본 selectApprovalList와 동일한 컬럼 구조 + APPROVAL-NNNN / ROUTE-NNNN 번호
  const rows = await queryRows(
    `SELECT
       A.objid::text AS "OBJID",
       A.objid::text AS "APPROVAL_OBJID",
       A.target_objid AS "TARGET_OBJID",
       A.target_type AS "TARGET_TYPE",
       A.approval_seq AS "APPROVAL_SEQ",
       'APPROVAL-' || LPAD(A.approval_seq::text, 4, '0') AS "APPROVAL_NO",
       A.status AS "APPROVAL_STATUS",
       TO_CHAR(A.regdate, 'YYYY-MM-DD') AS "APPROVAL_REGDATE",
       R.objid::text AS "ROUTE_OBJID",
       R.route_seq AS "ROUTE_SEQ",
       'ROUTE-' || LPAD(R.route_seq::text, 4, '0') AS "ROUTE_NO",
       R.approval_title AS "TITLE",
       R.approval_desc AS "DESCRIPTION",
       R.writer AS "WRITER",
       COALESCE((SELECT dept_name FROM user_info WHERE user_id = R.writer LIMIT 1), '') AS "DEPT_NAME",
       COALESCE((SELECT user_name FROM user_info WHERE user_id = R.writer LIMIT 1), R.writer) AS "WRITER_NAME",
       TO_CHAR(R.regdate, 'YYYY-MM-DD') AS "REGDATE",
       R.status AS "STATUS",
       CASE
         WHEN UPPER(A.status) = 'COMPLETE' THEN '결재완료'
         WHEN UPPER(A.status) = 'REJECT' THEN '반려'
         WHEN UPPER(IT.status) = 'READY' THEN '미결재'
         WHEN UPPER(IT.status) = 'STANDBY' THEN '대기'
         ELSE A.status END AS "STATUS_NAME",
       IT.objid::text AS "INBOX_OBJID",
       IT.seq AS "SEQ",
       IT.approval_type AS "APPROVAL_TYPE",
       IT.status AS "INBOX_STATUS",
       IT.target_user_id AS "TARGET_USER_ID",
       CASE A.target_type
         WHEN 'PURCHASE_ORDER' THEN '발주'
         WHEN 'EXPENSE_APPLY' THEN '경비'
         WHEN 'MATERIAL_APPLY' THEN '자재'
         WHEN 'AS_MNG' THEN 'CS/AS'
         WHEN 'CSM' THEN '고객서비스'
         WHEN 'ISSUE_RELEASE' THEN '이슈'
         WHEN 'EO_MNG' THEN '설변(EO)'
         WHEN 'ECR_MNG' THEN '변경요청(ECR)'
         WHEN 'SALES_REQUEST' THEN '영업요청'
         ELSE A.target_type END AS "TYPE_NAME"
     FROM approval A, route R, inboxtask IT
     WHERE ${conditions.join(" AND ")}
     ORDER BY R.regdate DESC
`,
    params
  );

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