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

// 설변대상 PART 조회 (원본: partMng.partMngChangeGridList)
// 저장 원본: PartMngService.savePartMngChangeList → savePartMng(ACTION_TYPE='changeDesign')
// action: list(default) | save
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 action = String(body.action || "list");

  if (action === "save") {
    const rows: Array<Record<string, unknown>> = Array.isArray(body.rows) ? body.rows : [];
    const client = await pool.connect();
    try {
      await client.query("BEGIN");
      for (const r of rows) {
        // Q_STATUS='deploy' 건만 저장 (원본 fn_save 체크)
        // 주: PART_SEQ 필터 없음. 같은 파트가 여러 BOM에 있을 때(동시프로젝트 등)
        //    각 bom_part_qty row(child_objid)가 모두 beforeEdit 으로 전환되어야 함.
        if (String(r.Q_STATUS || "") !== "deploy") continue;

        const objid = String(r.OBJID || "");
        if (!objid) continue;

        // REVISION 자동 증가: 'RE' → 'A', 그 외 → getNextRevision
        const prevRevision = String(r.REVISION || "");
        const nextRevision = prevRevision === "RE" ? "A" : getNextRevision(prevRevision);

        // partMng.mergePartMngchangeDesign — INSERT ON CONFLICT(OBJID) DO UPDATE
        // STATUS='changing', EO_DATE='', EO_NO='' 고정 (원본 line 576-578)
        await client.query(
          `INSERT INTO part_mng (
             objid, product_mgmt_objid, contract_objid, upg_no, part_no, part_name,
             unit, qty, spec, material, weight, part_type, remark,
             es_spec, ms_spec, change_type, change_option,
             design_apply_point, management_flag, revision, status,
             reg_date, writer, is_last, parent_part_no, sub_material,
             eo_no, eo_date, design_date,
             thickness, width, height, out_diameter, in_diameter, length,
             maker, post_processing, major_category, sub_category,
             code1, code2, code3, code4, code5, is_new, is_longd
           ) VALUES (
             $1, $2, $3, $4, $5, $6,
             $7, $8, $9, $10, $11, $12, $13,
             $14, $15, $16, $17,
             $18, $19, UPPER($20), 'changing',
             NOW(), $21, COALESCE($22, '0'), $23, $24,
             '', '', $25,
             $26, $27, $28, $29, $30, $31,
             $32, $33, $34, $35,
             $36, $37, $38, $39, $40, $41, $42
           )
           ON CONFLICT (objid) DO UPDATE SET
             product_mgmt_objid = EXCLUDED.product_mgmt_objid,
             upg_no = EXCLUDED.upg_no,
             part_no = EXCLUDED.part_no,
             part_name = EXCLUDED.part_name,
             unit = EXCLUDED.unit,
             qty = EXCLUDED.qty,
             spec = EXCLUDED.spec,
             material = EXCLUDED.material,
             weight = EXCLUDED.weight,
             part_type = EXCLUDED.part_type,
             remark = EXCLUDED.remark,
             es_spec = EXCLUDED.es_spec,
             ms_spec = EXCLUDED.ms_spec,
             change_type = EXCLUDED.change_type,
             change_option = EXCLUDED.change_option,
             design_apply_point = EXCLUDED.design_apply_point,
             management_flag = EXCLUDED.management_flag,
             revision = EXCLUDED.revision,
             status = EXCLUDED.status,
             edit_date = NOW(),
             writer = EXCLUDED.writer,
             parent_part_no = EXCLUDED.parent_part_no,
             sub_material = EXCLUDED.sub_material,
             eo_no = '',
             eo_date = '',
             design_date = EXCLUDED.design_date,
             thickness = EXCLUDED.thickness,
             width = EXCLUDED.width,
             height = EXCLUDED.height,
             out_diameter = EXCLUDED.out_diameter,
             in_diameter = EXCLUDED.in_diameter,
             length = EXCLUDED.length,
             maker = EXCLUDED.maker,
             post_processing = EXCLUDED.post_processing,
             major_category = EXCLUDED.major_category,
             sub_category = EXCLUDED.sub_category,
             code1 = EXCLUDED.code1,
             code2 = EXCLUDED.code2,
             code3 = EXCLUDED.code3,
             code4 = EXCLUDED.code4,
             code5 = EXCLUDED.code5,
             is_new = EXCLUDED.is_new,
             is_longd = EXCLUDED.is_longd`,
          [
            objid,                                  // $1 objid
            String(r.PRODUCT_MGMT_OBJID || ""),     // $2
            String(r.CONTRACT_OBJID || ""),         // $3
            String(r.UPG_NO || ""),                 // $4
            String(r.PART_NO || ""),                // $5
            String(r.PART_NAME || ""),              // $6
            String(r.UNIT || ""),                   // $7 unit
            String(r.QTY || ""),                    // $8 qty
            String(r.SPEC || ""),                   // $9
            String(r.MATERIAL || ""),               // $10
            String(r.WEIGHT || ""),                 // $11
            String(r.PART_TYPE || ""),              // $12
            String(r.REMARK || ""),                 // $13
            String(r.ES_SPEC || ""),                // $14
            String(r.MS_SPEC || ""),                // $15
            String(r.CHANGE_TYPE || ""),            // $16
            String(r.CHANGE_OPTION || ""),          // $17
            String(r.DESIGN_APPLY_POINT || ""),     // $18
            String(r.MANAGEMENT_FLAG || ""),        // $19
            nextRevision,                           // $20 revision (UPPER)
            user.userId,                            // $21 writer
            r.IS_LAST == null ? null : String(r.IS_LAST), // $22 is_last (COALESCE '0')
            String(r.PARENT_PART_NO || ""),         // $23
            String(r.SUB_MATERIAL || ""),           // $24
            String(r.DESIGN_DATE || ""),            // $25 design_date (eo_no/eo_date 빈값 고정)
            String(r.THICKNESS || ""),              // $26
            String(r.WIDTH || ""),                  // $27
            String(r.HEIGHT || ""),                 // $28
            String(r.OUT_DIAMETER || ""),           // $29
            String(r.IN_DIAMETER || ""),            // $30
            String(r.LENGTH || ""),                 // $31
            String(r.MAKER || ""),                  // $32
            String(r.POST_PROCESSING || ""),        // $33
            String(r.MAJOR_CATEGORY || ""),         // $34
            String(r.SUB_CATEGORY || ""),           // $35
            String(r.CODE1 || ""),                  // $36
            String(r.CODE2 || ""),                  // $37
            String(r.CODE3 || ""),                  // $38
            String(r.CODE4 || ""),                  // $39
            String(r.CODE5 || ""),                  // $40
            String(r.IS_NEW || ""),                 // $41
            String(r.IS_LONGD || ""),               // $42
          ],
        );

        // partMng.mergePartMngHistory — PART_MNG_HISTORY 스냅샷 UPSERT (원본 582줄 주석 해제)
        // status='changing', revision=next, eo_no='' 상태 기준으로 저장
        await client.query(
          `INSERT INTO part_mng_history (
             objid, product_mgmt_objid, upg_no, part_no, part_name, unit, qty,
             spec, material, weight, part_type, remark, es_spec, ms_spec,
             change_type, change_option, design_apply_point, management_flag,
             revision, status, reg_date, writer, is_last,
             parent_part_no, sub_material, eo_no, eo_date, design_date,
             thickness, width, height, out_diameter, in_diameter, length,
             contract_objid, maker
           )
           SELECT
             $1::numeric, $2, $3, $4, $5, $6, $7,
             $8, $9, $10, $11, $12, $13, $14,
             $15, $16, $17, $18,
             UPPER($19), $20, NOW(), $21, '0',
             $22, $23, COALESCE((SELECT eo_no FROM part_mng WHERE objid = $1::varchar), ''),
             $24, $25,
             $26, $27, $28, $29, $30, $31,
             $32, $33
           ON CONFLICT (objid) DO UPDATE SET
             product_mgmt_objid = EXCLUDED.product_mgmt_objid,
             upg_no = EXCLUDED.upg_no,
             part_no = EXCLUDED.part_no,
             part_name = EXCLUDED.part_name,
             unit = EXCLUDED.unit,
             qty = EXCLUDED.qty,
             spec = EXCLUDED.spec,
             material = EXCLUDED.material,
             weight = EXCLUDED.weight,
             part_type = EXCLUDED.part_type,
             remark = EXCLUDED.remark,
             es_spec = EXCLUDED.es_spec,
             ms_spec = EXCLUDED.ms_spec,
             change_type = EXCLUDED.change_type,
             change_option = EXCLUDED.change_option,
             design_apply_point = EXCLUDED.design_apply_point,
             management_flag = EXCLUDED.management_flag,
             revision = EXCLUDED.revision,
             status = EXCLUDED.status,
             edit_date = NOW(),
             writer = EXCLUDED.writer,
             parent_part_no = EXCLUDED.parent_part_no,
             sub_material = EXCLUDED.sub_material,
             eo_date = EXCLUDED.eo_date,
             design_date = EXCLUDED.design_date,
             thickness = EXCLUDED.thickness,
             width = EXCLUDED.width,
             height = EXCLUDED.height,
             out_diameter = EXCLUDED.out_diameter,
             in_diameter = EXCLUDED.in_diameter,
             length = EXCLUDED.length,
             contract_objid = COALESCE((SELECT contract_objid FROM part_mng WHERE objid = $1::varchar), ''),
             maker = EXCLUDED.maker`,
          [
            objid,                               // $1
            String(r.PRODUCT_MGMT_OBJID || ""),  // $2
            String(r.UPG_NO || ""),              // $3
            String(r.PART_NO || ""),             // $4
            String(r.PART_NAME || ""),           // $5
            String(r.UNIT || ""),                // $6
            String(r.QTY || ""),                 // $7
            String(r.SPEC || ""),                // $8
            String(r.MATERIAL || ""),            // $9
            String(r.WEIGHT || ""),              // $10
            String(r.PART_TYPE || ""),           // $11
            String(r.REMARK || ""),              // $12
            String(r.ES_SPEC || ""),             // $13
            String(r.MS_SPEC || ""),             // $14
            String(r.CHANGE_TYPE || ""),         // $15
            String(r.CHANGE_OPTION || ""),       // $16
            String(r.DESIGN_APPLY_POINT || ""),  // $17
            String(r.MANAGEMENT_FLAG || ""),     // $18
            nextRevision,                         // $19 revision (UPPER)
            "changing",                           // $20 status
            user.userId,                          // $21 writer
            String(r.PARENT_PART_NO || ""),      // $22
            String(r.SUB_MATERIAL || ""),        // $23
            "",                                   // $24 eo_date (리셋)
            String(r.DESIGN_DATE || ""),         // $25
            String(r.THICKNESS || ""),           // $26
            String(r.WIDTH || ""),               // $27
            String(r.HEIGHT || ""),              // $28
            String(r.OUT_DIAMETER || ""),        // $29
            String(r.IN_DIAMETER || ""),         // $30
            String(r.LENGTH || ""),              // $31
            String(r.CONTRACT_OBJID || ""),      // $32
            String(r.MAKER || ""),               // $33
          ],
        );

        // partMng.structureQtySave — bom_part_qty UPDATE (원본 line 586-588)
        // WHERE child_objid = ?
        const childObjId = String(r.CHILD_OBJID || "");
        if (childObjId) {
          await client.query(
            `UPDATE bom_part_qty
                SET status = CASE WHEN COALESCE($1,'') = '' THEN status ELSE $1 END,
                    qty_temp = CASE WHEN COALESCE($2,'') = '' THEN qty_temp ELSE $2 END
              WHERE child_objid = $3`,
            ["beforeEdit", String(r.Q_QTY || ""), childObjId],
          );
        }

        // changeStatusBomReportByPartNo — 해당 PART를 사용한 BOM 전체를 설계변경 상태로 전환
        // (원본 partMngService.savePartMng 591줄 주석 해제 반영)
        const partNo = String(r.PART_NO || "");
        if (partNo) {
          await client.query(
            `UPDATE part_bom_report
                SET status = 'changeDesign', edit_date = NOW(), editer = $1
              WHERE objid IN (
                SELECT Q.bom_report_objid
                  FROM bom_part_qty Q, part_mng P
                 WHERE P.part_no = $2 AND P.objid = Q.part_no
              )
              AND status != 'changeDesign'`,
            [user.userId, partNo],
          );
        }
      }
      await client.query("COMMIT");
      return NextResponse.json({ success: true, message: "저장되었습니다." });
    } catch (e) {
      await client.query("ROLLBACK");
      console.error("part-change save:", e);
      return NextResponse.json({ success: false, message: "저장 중 오류가 발생했습니다." }, { status: 500 });
    } finally {
      client.release();
    }
  }

  // list - partMngChangeGridList 대응
  const conditions: string[] = ["T.status = 'release'"];
  const params: unknown[] = [];
  let idx = 1;

  if (body.project_name) {
    // 동시 프로젝트 같은 파트 조회
    conditions.push(`EXISTS (SELECT 'E' FROM project_mgmt SP
                      WHERE SP.objid = B.contract_objid
                        AND EXISTS (SELECT 'E' FROM contract_mgmt SC
                                      WHERE SC.objid = SP.contract_objid
                                        AND EXISTS (SELECT 'E' FROM project_mgmt SP2
                                                      WHERE SC.objid = SP2.contract_objid
                                                        AND SP2.objid = $${idx})))`);
    params.push(body.project_name);
    idx++;
  }
  if (body.unit_code) {
    conditions.push(`EXISTS (SELECT 'E' FROM pms_wbs_task O
                      WHERE O.objid = B.unit_code
                        AND (UPPER(O.task_name) = (SELECT UPPER(task_name) FROM pms_wbs_task ST WHERE ST.objid = $${idx})
                          OR UPPER(O.unit_no || '-' || O.task_name) = (SELECT UPPER(task_name) FROM pms_wbs_task ST WHERE ST.objid = $${idx})))`);
    params.push(body.unit_code);
    idx++;
  }
  if (body.SEARCH_PART_OBJID) {
    conditions.push(`T.objid = $${idx++}`);
    params.push(body.SEARCH_PART_OBJID);
  }

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

  const sql = `
    SELECT
      ROW_NUMBER() OVER(ORDER BY T.part_no,
        (CASE WHEN T.revision LIKE 'RE%' THEN 0 ELSE 1 END) DESC,
        T.revision DESC) AS "NUM",
      DENSE_RANK() OVER(PARTITION BY T.objid ORDER BY Q.seq) AS "PART_SEQ",
      T.objid::text AS "OBJID",
      T.objid::text AS "OBJID_ORG",
      T.part_no AS "PART_NO",
      T.part_name AS "PART_NAME",
      T.revision AS "REVISION",
      T.eo_no AS "EO_NO",
      T.eo_date AS "EO_DATE",
      T.material AS "MATERIAL",
      T.spec AS "SPEC",
      T.post_processing AS "POST_PROCESSING",
      T.maker AS "MAKER",
      T.major_category AS "MAJOR_CATEGORY",
      T.sub_category AS "SUB_CATEGORY",
      T.remark AS "REMARK",
      T.part_type AS "PART_TYPE",
      T.change_type AS "CHANGE_TYPE",
      T.change_option AS "CHANGE_OPTION",
      T.product_mgmt_objid AS "PRODUCT_MGMT_OBJID",
      T.upg_no AS "UPG_NO",
      T.unit AS "UNIT",
      T.qty AS "QTY",
      T.weight AS "WEIGHT",
      T.es_spec AS "ES_SPEC",
      T.ms_spec AS "MS_SPEC",
      T.design_apply_point AS "DESIGN_APPLY_POINT",
      T.management_flag AS "MANAGEMENT_FLAG",
      T.is_last AS "IS_LAST",
      T.parent_part_no AS "PARENT_PART_NO",
      T.sub_material AS "SUB_MATERIAL",
      T.design_date AS "DESIGN_DATE",
      T.thickness AS "THICKNESS",
      T.width AS "WIDTH",
      T.height AS "HEIGHT",
      T.out_diameter AS "OUT_DIAMETER",
      T.in_diameter AS "IN_DIAMETER",
      T.length AS "LENGTH",
      T.code1 AS "CODE1",
      T.code2 AS "CODE2",
      T.code3 AS "CODE3",
      T.code4 AS "CODE4",
      T.code5 AS "CODE5",
      T.is_new AS "IS_NEW",
      T.is_longd AS "IS_LONGD",
      Q.objid::text AS "OBJID_QTY",
      Q.child_objid::text AS "CHILD_OBJID",
      Q.status AS "Q_STATUS",
      CASE WHEN Q.status = 'deploy' THEN Q.qty
           WHEN (Q.qty_temp IS NULL OR Q.qty_temp = '') THEN Q.qty
           ELSE Q.qty_temp END AS "Q_QTY",
      (SELECT SUM(I.qty::numeric)
         FROM inventory_mng I, resource_mng RM
        WHERE RM.objid = I.parent_objid
          AND I.is_last = 'Y'
          AND RM.part_objid = T.objid::varchar
        GROUP BY I.parent_objid
        LIMIT 1) AS "INVEN_TOTAL_QTY",
      (SELECT part_no || ' ' || part_name FROM part_mng SP WHERE SP.objid = Q.parent_part_no) AS "PARENT_PART_INFO",
      (SELECT project_no FROM project_mgmt O WHERE O.objid = B.contract_objid) AS "PROJECT_NO",
      (SELECT O.unit_no || '-' || O.task_name FROM pms_wbs_task O WHERE O.objid = B.unit_code) AS "UNIT_NAME",
      B.contract_objid AS "CONTRACT_OBJID",
      B.unit_code AS "UNIT_CODE",
      (SELECT COUNT(*) FROM attach_file_info WHERE target_objid = T.objid::text AND doc_type = '3D_CAD') AS "CU01_CNT",
      (SELECT COUNT(*) FROM attach_file_info WHERE target_objid = T.objid::text AND doc_type = '2D_DRAWING_CAD') AS "CU02_CNT",
      (SELECT COUNT(*) FROM attach_file_info WHERE target_objid = T.objid::text AND doc_type = '2D_PDF_CAD') AS "CU03_CNT"
    FROM part_mng T
      LEFT OUTER JOIN bom_part_qty Q
        ON (
          (Q.last_part_objid = T.objid AND Q.status IN ('deploy','beforeEdit'))
          OR
          (T.objid = (SELECT PM1.objid FROM part_mng PM1, part_mng PM2
                       WHERE PM1.is_last = '1' AND PM2.objid = Q.part_no AND PM1.part_no = PM2.part_no)
            AND Q.status IN ('editing'))
        )
        AND Q.status IN ('deploy','beforeEdit')
      LEFT OUTER JOIN part_bom_report B ON B.objid = Q.bom_report_objid
    WHERE ${where}
    ORDER BY T.part_no,
      (CASE WHEN T.revision LIKE 'RE%' THEN 0 ELSE 1 END) DESC,
      T.revision DESC
  `;

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