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

// 생산관리_이슈관리 (selectPlanningIssueList 원본 1:1 포팅)
// 주 테이블: planning_issue
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(T.reg_date, 'YYYY') = $${idx++}`);
    params.push(String(body.year || body.Year));
  }
  if (body.project_no) {
    conditions.push(`(SELECT project_no FROM project_mgmt WHERE objid = T.project_objid) LIKE '%' || $${idx++} || '%'`);
    params.push(body.project_no);
  }
  if (body.project_objid) {
    conditions.push(`T.project_objid = $${idx++}`);
    params.push(body.project_objid);
  }
  if (body.unit_code) {
    conditions.push(`T.unit_code = $${idx++}`);
    params.push(body.unit_code);
  }
  if (body.issue_category) {
    conditions.push(`T.issue_category = $${idx++}`);
    params.push(body.issue_category);
  }
  if (body.issue_type) {
    conditions.push(`T.issue_type = $${idx++}`);
    params.push(body.issue_type);
  }
  // status 파라미터 (현황에서 all/complete/late 호출 호환)
  if (body.status) {
    const s = String(body.status);
    if (s === "all") conditions.push(`T.status <> 'write'`);
    else if (s === "complete") conditions.push(`T.status = 'release' AND COALESCE(T.design_result,'') <> '' AND COALESCE(T.design_date,'') <> '' AND COALESCE(T.design_userid,'') <> ''`);
    else if (s === "late") conditions.push(`T.status = 'release' AND (COALESCE(T.design_date,'') = '' OR COALESCE(T.design_userid,'') = '' OR COALESCE(T.design_result,'') = '')`);
    else if (s === "write") conditions.push(`T.status = 'write'`);
    else if (s === "release") conditions.push(`T.status = 'release'`);
  }

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

  const sql = `
    SELECT T.objid::text AS "OBJID",
      T.issue_no AS "ISSUE_NO",
      T.project_objid AS "PROJECT_OBJID",
      (SELECT project_no FROM project_mgmt WHERE objid = T.project_objid) AS "PROJECT_NO",
      T.unit_code AS "UNIT_CODE",
      (SELECT COALESCE(O.unit_no,'') || '-' || COALESCE(O.task_name,'') FROM pms_wbs_task O WHERE O.objid = T.unit_code LIMIT 1) AS "UNIT_CODE_NAME",
      T.part_objid AS "PART_OBJID",
      (SELECT part_no FROM part_mng WHERE objid::varchar = T.part_objid LIMIT 1) AS "PART_NO",
      (SELECT part_name FROM part_mng WHERE objid::varchar = T.part_objid LIMIT 1) AS "PART_NAME",
      T.issue_category AS "ISSUE_CATEGORY",
      (SELECT code_name FROM comm_code WHERE code_id = T.issue_category LIMIT 1) AS "ISSUE_CATEGORY_NAME",
      T.issue_type AS "ISSUE_TYPE",
      (SELECT code_name FROM comm_code WHERE code_id = T.issue_type LIMIT 1) AS "ISSUE_TYPE_NAME",
      T.content AS "CONTENT",
      T.design_userid AS "DESIGN_USERID",
      (SELECT user_name FROM user_info WHERE user_id = T.design_userid LIMIT 1) AS "DESIGN_USERID_NAME",
      T.design_result AS "DESIGN_RESULT",
      (SELECT code_name FROM comm_code WHERE code_id = T.design_result LIMIT 1) AS "DESIGN_RESULT_NAME",
      T.design_date AS "DESIGN_DATE",
      T.purchase_userid AS "PURCHASE_USERID",
      (SELECT user_name FROM user_info WHERE user_id = T.purchase_userid LIMIT 1) AS "PURCHASE_USERID_NAME",
      T.purchase_result AS "PURCHASE_RESULT",
      T.purchase_date AS "PURCHASE_DATE",
      T.quality_userid AS "QUALITY_USERID",
      (SELECT user_name FROM user_info WHERE user_id = T.quality_userid LIMIT 1) AS "QUALITY_USERID_NAME",
      T.quality_result AS "QUALITY_RESULT",
      T.quality_date AS "QUALITY_DATE",
      T.production_userid AS "PRODUCTION_USERID",
      (SELECT user_name FROM user_info WHERE user_id = T.production_userid LIMIT 1) AS "PRODUCTION_USERID_NAME",
      T.production_result AS "PRODUCTION_RESULT",
      T.production_date AS "PRODUCTION_DATE",
      T.reg_date AS "REG_DATE",
      TO_CHAR(T.reg_date, 'YYYY-MM-DD') AS "REG_DATE_TEXT",
      T.writer AS "WRITER",
      (SELECT user_name FROM user_info WHERE user_id = T.writer LIMIT 1) AS "WRITER_NAME",
      T.status AS "STATUS"
    FROM planning_issue T
    WHERE ${where}
    ORDER BY T.issue_no DESC
  `;

  const rows = await queryRows(sql, params);

  // STATUS_NAME JS 계산 (원본 CASE 로직 이식)
  const now = new Date();
  const result = rows.map((r: Record<string, unknown>) => {
    const s = String(r.STATUS || "");
    const du = String(r.DESIGN_USERID || "");
    const dd = String(r.DESIGN_DATE || "");
    const pu = String(r.PURCHASE_USERID || "");
    const pd = String(r.PURCHASE_DATE || "");
    const qu = String(r.QUALITY_USERID || "");
    const qd = String(r.QUALITY_DATE || "");
    const ru = String(r.PRODUCTION_USERID || "");
    const rd = String(r.PRODUCTION_DATE || "");

    const pair = (u: string, d: string) => (u !== "" && d !== "") || (u === "" && d === "");
    const anyUser = du !== "" || pu !== "" || qu !== "" || ru !== "";
    const anyTrace = anyUser || dd !== "" || pd !== "" || qd !== "" || rd !== "";

    let statusName: string;
    if (s === "write") statusName = "등록";
    else if (s === "release" && pair(du, dd) && pair(pu, pd) && pair(qu, qd) && pair(ru, rd) && anyUser) {
      statusName = "조치완료";
    } else if (s === "release" && anyTrace) {
      statusName = "조치중";
    } else if (s === "release") {
      const regDate = r.REG_DATE instanceof Date ? r.REG_DATE : (r.REG_DATE ? new Date(String(r.REG_DATE)) : null);
      if (regDate) {
        const limit = new Date(regDate);
        limit.setDate(limit.getDate() + 5);
        statusName = now > limit && dd === "" ? "미조치" : "배포";
      } else statusName = "배포";
    } else statusName = s;

    return { ...r, STATUS_NAME: statusName };
  });

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