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

// CS관리 - 등록 및 조치 목록 (asMngList_CS.jsp + getECRList_CS 대응)
// FITO: /as/asMngGridList.do → CUSTOMER_SERVICE_MGMT + PROJECT_MGMT + RELEASE_MGMT + CUSTOMER_SERVICE_WORKINGTIME + APPROVAL/ROUTE
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 || body.year) {
    conditions.push(`TO_CHAR(CSM.regdate,'YYYY') = $${idx++}`);
    params.push(String(body.Year || body.year));
  }
  if (body.product_cd) {
    conditions.push(`CSM.product = $${idx++}`);
    params.push(body.product_cd);
  }
  if (body.project_no) {
    conditions.push(`CSM.contract_objid = $${idx++}`);
    params.push(body.project_no);
  }
  if (body.project_nos && Array.isArray(body.project_nos) && body.project_nos.length > 0) {
    const placeholders = body.project_nos.map(() => `$${idx++}`).join(",");
    conditions.push(`CSM.contract_objid IN (${placeholders})`);
    params.push(...body.project_nos);
  }
  if (body.warranty) {
    conditions.push(`CSM.warranty = $${idx++}`);
    params.push(body.warranty);
  }
  if (body.category_h) {
    conditions.push(`CSM.category_h = $${idx++}`);
    params.push(body.category_h);
  }
  if (body.rec_start_date) {
    conditions.push(`TO_DATE(TO_CHAR(CSM.regdate,'YYYY-MM-DD'),'YYYY-MM-DD') >= TO_DATE($${idx++},'YYYY-MM-DD')`);
    params.push(body.rec_start_date);
  }
  if (body.rec_end_date) {
    conditions.push(`TO_DATE(TO_CHAR(CSM.regdate,'YYYY-MM-DD'),'YYYY-MM-DD') <= TO_DATE($${idx++},'YYYY-MM-DD')`);
    params.push(body.rec_end_date);
  }
  if (body.manager_id) {
    conditions.push(`CSM.manager_id = $${idx++}`);
    params.push(body.manager_id);
  }
  if (body.act_start_date) {
    conditions.push(`TO_DATE(CSM.act_date,'YYYY-MM-DD') >= TO_DATE($${idx++},'YYYY-MM-DD')`);
    params.push(body.act_start_date);
  }
  if (body.act_end_date) {
    conditions.push(`TO_DATE(CSM.act_date,'YYYY-MM-DD') <= TO_DATE($${idx++},'YYYY-MM-DD')`);
    params.push(body.act_end_date);
  }
  if (body.appr_status) {
    conditions.push(`APPR.APPR_STATUS = $${idx++}`);
    params.push(body.appr_status);
  }

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

  const sql = `
    SELECT
      CSM.objid::text AS "OBJID",
      CSM.service_no AS "SERVICE_NO",
      CSM.product AS "PRODUCT",
      (SELECT code_name FROM comm_code WHERE code_id = CSM.product) AS "PRODUCT_NAME",
      CSM.contract_objid AS "CONTRACT_OBJID",
      CSM.cs_category AS "CS_CATEGORY",
      (SELECT code_name FROM comm_code WHERE code_id = CSM.cs_category) AS "CATEGORY_NAME",
      CSM.warranty AS "WARRANTY",
      (SELECT code_name FROM comm_code WHERE code_id = CSM.warranty) AS "WARRANTY_NAME",
      CSM.manager_id AS "MANAGER_ID",
      (SELECT user_name FROM user_info WHERE user_id = CSM.manager_id) AS "MANAGER_NAME",
      CSM.act_date AS "ACT_DATE",
      CSM.category_h AS "CATEGORY_H",
      CSM.category_m AS "CATEGORY_M",
      CSM.category_l AS "CATEGORY_L",
      (SELECT code_name FROM comm_code WHERE code_id = CSM.category_h) AS "CATEGORY_H_NAME",
      (SELECT code_name FROM comm_code WHERE code_id = CSM.category_m) AS "CATEGORY_M_NAME",
      (SELECT code_name FROM comm_code WHERE code_id = CSM.category_l) AS "CATEGORY_L_NAME",
      CSM.title AS "TITLE",
      CSM.writer AS "WRITER",
      CSM.status AS "STATUS",
      (SELECT code_name FROM comm_code WHERE code_id = CSM.status) AS "STATUS_NAME",
      TO_CHAR(CSM.regdate,'YYYY-MM-DD') AS "REC_DT",
      CM.project_no AS "PROJECT_NO",
      CM.setup AS "SETUP",
      CM.customer_objid AS "CUSTOMER_OBJID",
      (SELECT supply_name FROM supply_mng WHERE objid::text = CM.customer_objid) AS "CUSTOMER_NAME",
      RM.release_date AS "RELEASE_DATE",
      CSW.PLAN_COST AS "PLAN_COST",
      (SELECT COUNT(1) FROM attach_file_info WHERE target_objid = CSM.objid AND doc_type='AS_DOC_01' AND UPPER(status) = 'ACTIVE') AS "CU03_CNT",
      APPR.APPROVAL_OBJID AS "APPROVAL_OBJID",
      APPR.ROUTE_OBJID AS "ROUTE_OBJID",
      APPR.APPR_STATUS AS "APPR_STATUS",
      APPR.APPR_STATUS_NAME AS "APPR_STATUS_NAME"
    FROM customer_service_mgmt AS CSM
    LEFT OUTER JOIN project_mgmt AS CM ON CSM.contract_objid = CM.objid
    LEFT OUTER JOIN release_mgmt AS RM ON CM.objid = RM.parent_objid
    LEFT OUTER JOIN (
      SELECT parent_objid,
             SUM(COALESCE(NULLIF(labor_cost,''),'0')::numeric + COALESCE(NULLIF(expenses,''),'0')::numeric) AS PLAN_COST
      FROM customer_service_workingtime
      GROUP BY parent_objid
    ) AS CSW ON CSM.objid = CSW.parent_objid
    LEFT OUTER JOIN (
      SELECT B.objid AS ROUTE_OBJID,
             B.status AS APPR_STATUS,
             CASE UPPER(B.status)
               WHEN 'INPROCESS' THEN '결재중'
               WHEN 'COMPLETE'  THEN '결재완료'
               WHEN 'REJECT'    THEN '반려'
               ELSE '' END AS APPR_STATUS_NAME,
             A.objid AS APPROVAL_OBJID,
             A.target_objid,
             B.route_seq
      FROM approval A,
           (SELECT T1.*
              FROM (SELECT target_objid, MAX(route_seq) AS route_seq
                      FROM route GROUP BY target_objid) T,
                   route T1
             WHERE T.target_objid = T1.target_objid
               AND T.route_seq = T1.route_seq) B
      WHERE A.objid = B.approval_objid
        AND A.target_type = 'CSM'
    ) AS APPR ON CSM.objid::numeric = APPR.target_objid
    WHERE ${where}
    ORDER BY CSM.regdate DESC
  `;

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