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

// CS관리 현황 (asList_CS.jsp + getASDashboardList + getHeaderList 대응)
// 제품×프로젝트 기준으로 유상/무상 건수·비용을 집계하고, CATEGORY_H 유형별 카운트를 동적 컬럼으로 추가
export async function POST(request: NextRequest) {
  const user = await getSession();
  if (!user) return NextResponse.json({ success: false }, { status: 401 });

  const body = await request.json();

  // 1) 동적 컬럼 헤더 (0000975 '조치내역서유형' 하위 재귀, LEV>1)
  const parentCode = body.category_root_code || "0000975";
  const columnList = await queryRows(
    `WITH RECURSIVE COM AS (
       SELECT parent_code_id, code_id, code_name AS name, code_cd, status, id, ext_val,
              'COL_'||code_id AS col_name, 1 AS lev
       FROM comm_code
       WHERE UPPER(status)='ACTIVE' AND parent_code_id = $1
       UNION ALL
       SELECT cc.parent_code_id, cc.code_id, cc.code_name AS name, cc.code_cd, cc.status, cc.id, cc.ext_val,
              'COL_'||cc.code_id AS col_name, com.lev+1
       FROM comm_code cc JOIN COM ON COM.code_id = cc.parent_code_id
     )
     SELECT
       ROW_NUMBER() OVER (PARTITION BY parent_code_id ORDER BY code_id) AS "GROUP_SEQ",
       (SELECT COUNT(1) FROM comm_code WHERE parent_code_id = C.parent_code_id) AS "GROUP_CNT",
       (SELECT code_name FROM comm_code WHERE code_id = C.parent_code_id) AS "GROUP_NAME",
       C.parent_code_id AS "PARENT_CODE_ID",
       C.code_id AS "CODE_ID",
       C.name AS "NAME",
       C.col_name AS "COL_NAME"
     FROM COM C
     WHERE lev > 1
     ORDER BY C.parent_code_id, C.code_id`,
    [parentCode]
  );

  // 2) 집계 데이터 (getASDashboardList) — 동적 컬럼 SUM 추가
  const conditions: string[] = ["CSM.status = '0000102'"]; // 결재완료
  const params: unknown[] = [];
  let idx = 1;

  if (body.Year || body.year) {
    conditions.push(`TO_CHAR(TO_DATE(CSM.act_date,'YYYY-MM-DD'),'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 ph = body.project_nos.map(() => `$${idx++}`).join(",");
    conditions.push(`CSM.contract_objid IN (${ph})`);
    params.push(...body.project_nos);
  }
  if (body.warranty) {
    conditions.push(`CSM.warranty = $${idx++}`);
    params.push(body.warranty);
  }
  if (body.cs_category) {
    conditions.push(`CSM.cs_category = $${idx++}`);
    params.push(body.cs_category);
  }
  if (body.category_h) {
    conditions.push(`CSM.category_h = $${idx++}`);
    params.push(body.category_h);
  }

  // 동적 컬럼 SUM 생성 — COL_NAME 은 영숫자+언더스코어만이므로 직접 삽입 안전 (CODE_ID도 서버 데이터)
  const dynSum = columnList
    .map((c) => {
      const codeId = String(c.CODE_ID).replace(/[^A-Za-z0-9_-]/g, "");
      const col = String(c.COL_NAME).replace(/[^A-Za-z0-9_]/g, "");
      return `,SUM(CASE WHEN T.category_h = '${codeId}' THEN 1 ELSE 0 END) AS "${col}"`;
    })
    .join("\n");

  const rows = await queryRows(
    `SELECT
       T.product AS "PRODUCT",
       (SELECT code_name FROM comm_code WHERE code_id = T.product) AS "PRODUCT_NAME",
       T.project_no AS "PROJECT_NO",
       SUM(CASE WHEN T.warranty = '0000157' THEN 1 ELSE 0 END) AS "WARRANTY1",
       SUM(CASE WHEN T.warranty = '0000157' THEN T.cost ELSE 0 END) AS "COST1",
       SUM(CASE WHEN T.warranty = '0000158' THEN 1 ELSE 0 END) AS "WARRANTY2",
       SUM(CASE WHEN T.warranty = '0000158' THEN T.cost ELSE 0 END) AS "COST2"
       ${dynSum}
     FROM (
       SELECT CSM.product,
              TRIM(CM.project_no) AS project_no,
              CSM.warranty,
              CSM.act_date,
              CSM.category_h,
              (COALESCE(NULLIF(CSM.total_labor_cost,''),'0')::numeric
               + COALESCE(NULLIF(CSM.total_expenses,''),'0')::numeric) AS cost
       FROM customer_service_mgmt AS CSM
       LEFT OUTER JOIN project_mgmt AS CM ON CSM.contract_objid = CM.objid
       WHERE ${conditions.join(" AND ")}
     ) AS T
     GROUP BY T.product, T.project_no
     ORDER BY T.project_no DESC`,
    params
  );

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