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

// 영업관리 > 계약 저장 + 수주(0000964) 전환 시 프로젝트 자동 생성 + WBS 자동 복사
// 원본: ContractMgmtService.saveContractMgmtInfo (Java) +
//       contractMgmt.xml saveContractMgmtInfo / insertProjectTask / insertProjectSetupTask +
//       project.xml createProject / ModifyProjectByContract
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 objId = String(body.objId || createObjectId());

  // 오버홀/개조/MRO 카테고리
  const OVERHAUL_CATEGORIES = new Set(["0000170", "0000171", "0001790"]);
  const CONTRACT_RESULT_AWARDED = "0000964"; // 수주

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

    // objid로 DB 존재 여부 확인 — 있으면 update, 없으면 insert
    const existing = await client.query(
      `SELECT contract_no FROM contract_mgmt WHERE objid = $1`,
      [objId]
    );
    const isNew = existing.rows.length === 0;

    // 1) CONTRACT_MGMT upsert
    let contractNo = isNew ? "" : String(existing.rows[0].contract_no || "");
    if (!contractNo) {
      // 시퀀스 기반 채번 (원본: (SELECT TO_CHAR(NOW(),'yy')::VARCHAR ||'C-'|| LPAD(NEXTVAL('contract_mgmt_seq'),4,'0')))
      const seq = await client.query(`SELECT NEXTVAL('contract_mgmt_seq') AS n`);
      const n = Number(seq.rows[0].n);
      const yy = new Date().getFullYear().toString().slice(2);
      contractNo = `${yy}C-${String(n).padStart(4, "0")}`;
    }

    await client.query(
      `INSERT INTO contract_mgmt (
        objid, contract_no, category_cd, area_cd, customer_objid, product,
        mechanical_type, customer_project_name, due_date, location, setup,
        facility, facility_qty, facility_type, facility_depth,
        contract_result, contract_date, po_no, pm_user_id,
        contract_currency, contract_price_currency, contract_price,
        project_name, contract_del_date, req_del_date,
        contract_company, manufacture_plant,
        target_project_no, target_project_no_direct, customer_production_no,
        overhaul_order, writer, regdate
      ) VALUES (
        $1,$2,$3,$4,$5,$6,
        $7,$8,$9,$10,$11,
        $12,$13,$14,$15,
        $16,$17,$18,$19,
        $20,$21,$22,
        $23,$24,$25,
        $26,$27,
        $28,$29,$30,
        $31,$32,now()
      )
      ON CONFLICT (objid) DO UPDATE SET
        category_cd = EXCLUDED.category_cd,
        area_cd = EXCLUDED.area_cd,
        customer_objid = EXCLUDED.customer_objid,
        product = EXCLUDED.product,
        mechanical_type = EXCLUDED.mechanical_type,
        customer_project_name = EXCLUDED.customer_project_name,
        due_date = EXCLUDED.due_date,
        location = EXCLUDED.location,
        setup = EXCLUDED.setup,
        facility = EXCLUDED.facility,
        facility_qty = EXCLUDED.facility_qty,
        facility_type = EXCLUDED.facility_type,
        facility_depth = EXCLUDED.facility_depth,
        contract_result = EXCLUDED.contract_result,
        contract_date = EXCLUDED.contract_date,
        po_no = EXCLUDED.po_no,
        pm_user_id = EXCLUDED.pm_user_id,
        contract_currency = EXCLUDED.contract_currency,
        contract_price_currency = EXCLUDED.contract_price_currency,
        contract_price = EXCLUDED.contract_price,
        project_name = EXCLUDED.project_name,
        contract_del_date = EXCLUDED.contract_del_date,
        req_del_date = EXCLUDED.req_del_date,
        contract_company = EXCLUDED.contract_company,
        manufacture_plant = EXCLUDED.manufacture_plant,
        target_project_no = EXCLUDED.target_project_no,
        target_project_no_direct = EXCLUDED.target_project_no_direct,
        customer_production_no = EXCLUDED.customer_production_no,
        overhaul_order = EXCLUDED.overhaul_order`,
      [
        objId, contractNo,
        body.category_cd || "", body.area_cd || "",
        body.customer_objid || "", body.product || "",
        body.mechanical_type || "", body.customer_project_name || "",
        body.due_date || "", body.location || "", body.setup || "",
        body.facility || "", String(body.facility_qty || "1"),
        body.facility_type || "", body.facility_depth || "",
        body.contract_result || "", body.contract_date || "",
        body.po_no || "", body.pm_user_id || "",
        body.contract_currency || "", String(body.contract_price_currency || "0").replace(/,/g, ""),
        String(body.contract_price || "0").replace(/,/g, ""),
        body.project_name || "", body.contract_del_date || "",
        body.req_del_date || "",
        body.contract_company || "", body.manufacture_plant || "",
        body.target_project_no || "", body.target_project_no_direct || "",
        body.customer_production_no || "",
        body.overhaul_order || "", user.userId,
      ]
    );

    // 2) 수주(0000964)인 경우 프로젝트 자동 생성 + WBS 복사
    let projectsCreated = 0;
    if (body.contract_result === CONTRACT_RESULT_AWARDED) {
      const facilityQty = Math.max(1, parseInt(String(body.facility_qty || "1"), 10));
      const pricePer = Math.floor((parseFloat(String(body.contract_price || "0").replace(/,/g, "")) || 0) / facilityQty);
      const pricePerCurrency = Math.floor((parseFloat(String(body.contract_price_currency || "0").replace(/,/g, "")) || 0) / facilityQty);

      // 이미 연결된 프로젝트가 있는지 확인 — 있으면 업데이트, 없으면 신규 생성
      const existCheck = await client.query(
        `SELECT project_name FROM project_mgmt WHERE contract_objid = $1 LIMIT 1`,
        [objId]
      );

      if (existCheck.rows.length === 0) {
        // 신규 — facility_qty 만큼 반복 생성
        const isOverhaul = OVERHAUL_CATEGORIES.has(body.category_cd || "");
        const targetProjectNo = body.target_project_no_direct || body.target_project_no || "";

        for (let i = 0; i < facilityQty; i++) {
          const projectObjId = createObjectId();
          const overhaulOrder = isOverhaul ? String((parseInt(body.overhaul_order || "0", 10) || 0) + i) : (body.overhaul_order || "");

          // 프로젝트 번호: 오버홀이면 target_project_no, 아니면 "{mechanical_type}-{autoincrement}"
          // 파라미터: $1=projectObjId, $2=objId(contract_objid INSERT용), $3=facility, $4=facility_depth
          //          $5=projectNo seed, $6=pricePer, $7=pricePerCurrency, $8=project_name, $9=overhaul_order
          //          $10=objId(WHERE절용 — $2와 동일값이지만 타입 추론 충돌 방지 위해 분리)
          let projectNoSql: string;
          const projectNoParams: unknown[] = [];
          if (isOverhaul && targetProjectNo) {
            projectNoSql = `$5`;
            projectNoParams.push(targetProjectNo);
          } else {
            // MECHANICAL_TYPE || '-' || nextNum
            projectNoSql = `
              COALESCE($5, '') || '-' || COALESCE(
                (SELECT CASE WHEN project_no ~ '[-\\s][0-9]+$'
                             THEN (REGEXP_REPLACE(project_no, '.*[-\\s]([0-9]+)$', '\\1')::integer + 1)::text
                             ELSE NULL END
                   FROM project_mgmt
                  WHERE project_no NOT LIKE '%\\_%' ESCAPE '\\'
                  ORDER BY CASE WHEN project_no ~ '[-\\s][0-9]+$'
                                THEN (REGEXP_REPLACE(project_no, '.*[-\\s]([0-9]+)$', '\\1')::integer + 1)
                                ELSE NULL END DESC NULLS LAST
                  LIMIT 1),
                '1')`;
            projectNoParams.push(body.mechanical_type || "");
          }

          // createProject (원본 project.xml) 단순화한 형태 — CONTRACT_MGMT 복사 + 일부 override
          await client.query(
            `INSERT INTO project_mgmt (
              objid, contract_objid, category_cd, customer_objid, product,
              customer_project_name, status_cd, due_date, location, setup,
              facility, facility_qty, facility_type, facility_depth,
              production_no, bus_cal_cd, category1_cd, chg_user_id,
              plan_date, complete_date, result_cd, project_no,
              pm_user_id, contract_price, contract_price_currency, contract_currency,
              regdate, writer, contract_no, customer_equip_name,
              req_del_date, contract_del_date, contract_company, contract_date,
              po_no, manufacture_plant, contract_result, project_name,
              spec_user_id, spec_plan_date, spec_comp_date, spec_result_cd,
              est_plan_date, est_user_id, est_comp_date, est_result_cd,
              area_cd, mechanical_type, overhaul_order, is_temp
            )
            SELECT
              $1, $2, category_cd, customer_objid, product,
              customer_project_name, status_cd, due_date, location, setup,
              $3, '1', facility_type, $4,
              production_no, bus_cal_cd, category1_cd, chg_user_id,
              plan_date, complete_date, result_cd, ${projectNoSql},
              pm_user_id, $6, $7, contract_currency,
              now(), writer, contract_no, customer_equip_name,
              req_del_date, contract_del_date, contract_company, contract_date,
              po_no, manufacture_plant, contract_result, $8,
              spec_user_id, spec_plan_date, spec_comp_date, spec_result_cd,
              est_plan_date, est_user_id, est_comp_date, est_result_cd,
              area_cd, mechanical_type, $9, '1'
            FROM contract_mgmt WHERE objid = $10`,
            [
              projectObjId, objId,
              body.facility || "", body.facility_depth || "",
              ...projectNoParams,
              String(pricePer), String(pricePerCurrency), body.project_name || "",
              overhaulOrder,
              objId,
            ]
          );

          // WBS Task 복사: pms_wbs_task_standard JOIN pms_wbs_template WHERE title = mechanical_type
          // contractMgmt.xml insertProjectTask 참고. task의 contract_objid에는 신규 프로젝트 OBJID가 들어감(원본 설계)
          await client.query(
            `INSERT INTO pms_wbs_task (objid, contract_objid, task_name, task_seq, unit_no, writer, reg_date)
             SELECT
               MD5(gen_random_uuid()::text),
               $1,
               T.task_name,
               T.task_seq,
               T.unit_no,
               $2,
               now()
             FROM pms_wbs_task_standard T
             LEFT JOIN pms_wbs_template T1 ON T.parent_objid = T1.objid
             WHERE T1.title = $3`,
            [projectObjId, user.userId, body.mechanical_type || ""]
          );

          // Setup WBS Task 복사 (원본은 전체 standard 복사 — 필터 없음)
          await client.query(
            `INSERT INTO setup_wbs_task (objid, contract_objid, parent_objid, task_category, task_name, standard_objid, task_seq, proj_step)
             SELECT
               MD5(gen_random_uuid()::text),
               $1,
               T.parent_objid,
               T.task_category,
               T.task_name,
               T.objid,
               T.task_seq,
               T.proj_step
             FROM setup_wbs_task_standard T`,
            [projectObjId]
          );

          projectsCreated++;
        }
      } else {
        // 이미 있는 프로젝트 — 업데이트 (ModifyProjectByContract 대응)
        await client.query(
          `UPDATE project_mgmt SET
             due_date = $2,
             customer_project_name = $3,
             location = $4,
             setup = $5,
             facility = $6,
             facility_type = $7,
             facility_depth = $8,
             contract_date = $9,
             po_no = $10,
             pm_user_id = $11,
             contract_currency = $12,
             contract_price_currency = $13,
             contract_price = $14,
             project_name = $15,
             contract_del_date = $16,
             req_del_date = $17,
             contract_company = $18,
             manufacture_plant = $19
           WHERE contract_objid = $1`,
          [
            objId,
            body.due_date || "", body.customer_project_name || "",
            body.location || "", body.setup || "",
            body.facility || "", body.facility_type || "", body.facility_depth || "",
            body.contract_date || "", body.po_no || "", body.pm_user_id || "",
            body.contract_currency || "", String(pricePerCurrency), String(pricePer),
            body.project_name || "", body.contract_del_date || "", body.req_del_date || "",
            body.contract_company || "", body.manufacture_plant || "",
          ]
        );
      }
    }

    await client.query("COMMIT");

    const msg = isNew ? "등록되었습니다." : "수정되었습니다.";
    const extra = projectsCreated > 0 ? ` (프로젝트 ${projectsCreated}건 자동 생성 + WBS 연결됨)` : "";
    return NextResponse.json({
      success: true, objId, contractNo, projectsCreated,
      message: msg + extra,
    });
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Contract save error:", error);
    return NextResponse.json({
      success: false,
      message: "저장 중 오류가 발생했습니다: " + (error instanceof Error ? error.message : String(error)),
    }, { status: 500 });
  } finally {
    client.release();
  }
}
