import { NextRequest, NextResponse } from "next/server";
import { pool, queryOne } from "@/lib/db";
import { getSession } from "@/lib/session";
import { createObjectId } from "@/lib/utils";

// BOM Excel 등록 (원본: partMng/partBomApplySave.do → savePartBomMaster)
// 엑셀 파싱된 rows + 기준 정보(customer/project/unit/bom_report)를 받아
// PART_BOM_REPORT + BOM_PART_QTY + PART_MNG(신규)를 저장한다.
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 rows: Array<Record<string, string>> = Array.isArray(body.rows) ? body.rows : [];
  if (rows.length === 0) {
    return NextResponse.json({ success: false, message: "저장할 데이터가 없습니다." }, { status: 400 });
  }

  const customerObjId = String(body.CUSTOMER_OBJID || "");
  const contractObjId = String(body.CONTRACT_OBJID || "");
  const unitCode = String(body.UNIT_CODE || "");
  let bomReportObjId = String(body.BOM_REPORT_OBJID || "");

  const client = await pool.connect();
  try {
    await client.query("BEGIN");

    // 1. BOM_REPORT 확보 (없으면 신규 생성)
    if (!bomReportObjId) {
      bomReportObjId = createObjectId();
      await client.query(
        `INSERT INTO part_bom_report (
           objid, customer_objid, contract_objid, unit_code,
           status, writer, regdate
         ) VALUES ($1, $2, $3, $4, 'create', $5, NOW())`,
        [bomReportObjId, customerObjId, contractObjId, unitCode, user.userId],
      );
    }

    // 2. PART_NO → part_mng.objid 매핑 확보 (신규면 생성)
    const partNoToObjId = new Map<string, string>();
    const allPartNos = new Set<string>();
    rows.forEach((r) => {
      if (r.PART_NO) allPartNos.add(r.PART_NO.trim());
      if (r.PARENT_PART_NO) allPartNos.add(r.PARENT_PART_NO.trim());
    });

    for (const partNo of allPartNos) {
      const existing = await queryOne<{ objid: string }>(
        `SELECT objid::text AS objid FROM part_mng
          WHERE part_no = $1 AND status = 'release' AND COALESCE(is_last, '1') = '1'
          ORDER BY reg_date DESC LIMIT 1`,
        [partNo],
      );
      if (existing) {
        partNoToObjId.set(partNo, existing.objid);
      } else {
        // 해당 PART_NO의 row를 찾아 PART_MNG 신규 생성
        const src = rows.find((r) => r.PART_NO?.trim() === partNo);
        const newObjId = createObjectId();
        await client.query(
          `INSERT INTO part_mng (
             objid, part_no, part_name, qty, material, spec, post_processing, maker,
             part_type, remark, revision, status, is_last, writer, reg_date
           ) VALUES (
             $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, '', 'release', '1', $11, NOW()
           )`,
          [
            newObjId, partNo,
            src?.PART_NAME || partNo,
            src?.QTY || "1",
            src?.MATERIAL || "",
            src?.SPEC || "",
            src?.POST_PROCESSING || "",
            src?.MAKER || "",
            src?.PART_TYPE || "",
            src?.REMARK || "",
            user.userId,
          ],
        );
        partNoToObjId.set(partNo, newObjId);
      }
    }

    // 3. 기존 bom_part_qty 삭제 후 재등록 (BOM_REPORT_OBJID 기준)
    await client.query(`DELETE FROM bom_part_qty WHERE bom_report_objid = $1`, [bomReportObjId]);

    // 4. 각 row를 bom_part_qty 에 insert
    let seq = 1;
    for (const r of rows) {
      const partNo = (r.PART_NO || "").trim();
      const parentPartNo = (r.PARENT_PART_NO || "").trim();
      if (!partNo) continue;
      const childObjId = createObjectId();
      const partObjId = partNoToObjId.get(partNo) || "";
      const parentObjId = parentPartNo ? partNoToObjId.get(parentPartNo) || "" : "";

      await client.query(
        `INSERT INTO bom_part_qty (
           objid, bom_report_objid, parent_objid, child_objid,
           parent_part_no, part_no, last_part_objid,
           qty, qty_temp, status, seq, regdate
         ) VALUES (
           $1, $2, $3, $4, $5, $6, $7, $8, $8, 'create', $9, NOW()
         )`,
        [
          childObjId, bomReportObjId,
          parentObjId || null,
          childObjId,
          parentObjId || null,
          partObjId,
          partObjId,
          r.QTY || "1",
          seq++,
        ],
      );
    }

    await client.query("COMMIT");
    return NextResponse.json({ success: true, bomReportObjId, message: "등록되었습니다." });
  } catch (e) {
    await client.query("ROLLBACK");
    console.error("bom excel save:", e);
    return NextResponse.json({ success: false, message: "저장 중 오류가 발생했습니다." }, { status: 500 });
  } finally {
    client.release();
  }
}
