From 14f61e24947fb92dd71ec0a7196a6e815f8e66da Mon Sep 17 00:00:00 2001 From: dujinkim Date: Mon, 21 Jul 2025 07:54:26 +0000 Subject: (최겸)기술영업 RFQ 담당자 초대, 요구사항 반영 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- lib/tech-vendors/service.ts | 4506 +++++++++++++++++++++++++------------------ 1 file changed, 2616 insertions(+), 1890 deletions(-) (limited to 'lib/tech-vendors/service.ts') diff --git a/lib/tech-vendors/service.ts b/lib/tech-vendors/service.ts index cb5aa89f..a5881083 100644 --- a/lib/tech-vendors/service.ts +++ b/lib/tech-vendors/service.ts @@ -1,1890 +1,2616 @@ -"use server"; // Next.js 서버 액션에서 직접 import하려면 (선택) - -import { revalidateTag, unstable_noStore } from "next/cache"; -import db from "@/db/db"; -import { techVendorAttachments, techVendorContacts, techVendorPossibleItems, techVendors, techVendorItemsView, type TechVendor, techVendorCandidates } from "@/db/schema/techVendors"; -import { items, itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items"; -import { users } from "@/db/schema/users"; - -import { filterColumns } from "@/lib/filter-columns"; -import { unstable_cache } from "@/lib/unstable-cache"; -import { getErrorMessage } from "@/lib/handle-error"; - -import { - insertTechVendor, - updateTechVendor, - groupByTechVendorStatus, - selectTechVendorContacts, - countTechVendorContacts, - insertTechVendorContact, - selectTechVendorItems, - countTechVendorItems, - insertTechVendorItem, - selectTechVendorsWithAttachments, - countTechVendorsWithAttachments, - updateTechVendors, -} from "./repository"; - -import type { - CreateTechVendorSchema, - UpdateTechVendorSchema, - GetTechVendorsSchema, - GetTechVendorContactsSchema, - CreateTechVendorContactSchema, - GetTechVendorItemsSchema, - CreateTechVendorItemSchema, - GetTechVendorRfqHistorySchema, -} from "./validations"; - -import { asc, desc, ilike, inArray, and, or, eq, isNull, not } from "drizzle-orm"; -import path from "path"; -import { sql } from "drizzle-orm"; -import { decryptWithServerAction } from "@/components/drm/drmUtils"; -import { deleteFile, saveDRMFile } from "../file-stroage"; - -/* ----------------------------------------------------- - 1) 조회 관련 ------------------------------------------------------ */ - -/** - * 복잡한 조건으로 기술영업 Vendor 목록을 조회 (+ pagination) 하고, - * 총 개수에 따라 pageCount를 계산해서 리턴. - * Next.js의 unstable_cache를 사용해 일정 시간 캐시. - */ -export async function getTechVendors(input: GetTechVendorsSchema) { - return unstable_cache( - async () => { - try { - const offset = (input.page - 1) * input.perPage; - - // 1) 고급 필터 (workTypes와 techVendorType 제외 - 별도 처리) - const filteredFilters = input.filters.filter( - filter => filter.id !== "workTypes" && filter.id !== "techVendorType" - ); - - const advancedWhere = filterColumns({ - table: techVendors, - filters: filteredFilters, - joinOperator: input.joinOperator, - }); - - // 2) 글로벌 검색 - let globalWhere; - if (input.search) { - const s = `%${input.search}%`; - globalWhere = or( - ilike(techVendors.vendorName, s), - ilike(techVendors.vendorCode, s), - ilike(techVendors.email, s), - ilike(techVendors.status, s) - ); - } - - // 최종 where 결합 - const finalWhere = and(advancedWhere, globalWhere); - - // 벤더 타입 필터링 로직 추가 - let vendorTypeWhere; - if (input.vendorType) { - // URL의 vendorType 파라미터를 실제 벤더 타입으로 매핑 - const vendorTypeMap = { - "ship": "조선", - "top": "해양TOP", - "hull": "해양HULL" - }; - - const actualVendorType = input.vendorType in vendorTypeMap - ? vendorTypeMap[input.vendorType as keyof typeof vendorTypeMap] - : undefined; - if (actualVendorType) { - // techVendorType 필드는 콤마로 구분된 문자열이므로 LIKE 사용 - vendorTypeWhere = ilike(techVendors.techVendorType, `%${actualVendorType}%`); - } - } - - // 간단 검색 (advancedTable=false) 시 예시 - const simpleWhere = and( - input.vendorName - ? ilike(techVendors.vendorName, `%${input.vendorName}%`) - : undefined, - input.status ? ilike(techVendors.status, input.status) : undefined, - input.country - ? ilike(techVendors.country, `%${input.country}%`) - : undefined - ); - - // TechVendorType 필터링 로직 추가 (고급 필터에서) - let techVendorTypeWhere; - const techVendorTypeFilters = input.filters.filter(filter => filter.id === "techVendorType"); - if (techVendorTypeFilters.length > 0) { - const typeFilter = techVendorTypeFilters[0]; - if (Array.isArray(typeFilter.value) && typeFilter.value.length > 0) { - // 각 타입에 대해 LIKE 조건으로 OR 연결 - const typeConditions = typeFilter.value.map(type => - ilike(techVendors.techVendorType, `%${type}%`) - ); - techVendorTypeWhere = or(...typeConditions); - } - } - - // WorkTypes 필터링 로직 추가 - let workTypesWhere; - const workTypesFilters = input.filters.filter(filter => filter.id === "workTypes"); - if (workTypesFilters.length > 0) { - const workTypeFilter = workTypesFilters[0]; - if (Array.isArray(workTypeFilter.value) && workTypeFilter.value.length > 0) { - // workTypes에 해당하는 벤더 ID들을 서브쿼리로 찾음 - const vendorIdsWithWorkTypes = db - .selectDistinct({ vendorId: techVendorPossibleItems.vendorId }) - .from(techVendorPossibleItems) - .leftJoin(itemShipbuilding, eq(techVendorPossibleItems.itemCode, itemShipbuilding.itemCode)) - .leftJoin(itemOffshoreTop, eq(techVendorPossibleItems.itemCode, itemOffshoreTop.itemCode)) - .leftJoin(itemOffshoreHull, eq(techVendorPossibleItems.itemCode, itemOffshoreHull.itemCode)) - .where( - or( - inArray(itemShipbuilding.workType, workTypeFilter.value), - inArray(itemOffshoreTop.workType, workTypeFilter.value), - inArray(itemOffshoreHull.workType, workTypeFilter.value) - ) - ); - - workTypesWhere = inArray(techVendors.id, vendorIdsWithWorkTypes); - } - } - - // 실제 사용될 where (vendorType, techVendorType, workTypes 필터링 추가) - const where = and(finalWhere, vendorTypeWhere, techVendorTypeWhere, workTypesWhere); - - // 정렬 - const orderBy = - input.sort.length > 0 - ? input.sort.map((item) => - item.desc ? desc(techVendors[item.id]) : asc(techVendors[item.id]) - ) - : [asc(techVendors.createdAt)]; - - // 트랜잭션 내에서 데이터 조회 - const { data, total } = await db.transaction(async (tx) => { - // 1) vendor 목록 조회 (with attachments) - const vendorsData = await selectTechVendorsWithAttachments(tx, { - where, - orderBy, - offset, - limit: input.perPage, - }); - - // 2) 전체 개수 - const total = await countTechVendorsWithAttachments(tx, where); - return { data: vendorsData, total }; - }); - - // 페이지 수 - const pageCount = Math.ceil(total / input.perPage); - - return { data, pageCount }; - } catch (err) { - console.error("Error fetching tech vendors:", err); - // 에러 발생 시 - return { data: [], pageCount: 0 }; - } - }, - [JSON.stringify(input)], // 캐싱 키 - { - revalidate: 3600, - tags: ["tech-vendors"], // revalidateTag("tech-vendors") 호출 시 무효화 - } - )(); -} - -/** - * 기술영업 벤더 상태별 카운트 조회 - */ -export async function getTechVendorStatusCounts() { - return unstable_cache( - async () => { - try { - const initial: Record = { - "PENDING_REVIEW": 0, - "ACTIVE": 0, - "INACTIVE": 0, - "BLACKLISTED": 0, - }; - - const result = await db.transaction(async (tx) => { - const rows = await groupByTechVendorStatus(tx); - type StatusCountRow = { status: TechVendor["status"]; count: number }; - return (rows as StatusCountRow[]).reduce>((acc, { status, count }) => { - acc[status] = count; - return acc; - }, initial); - }); - - return result; - } catch (err) { - return {} as Record; - } - }, - ["tech-vendor-status-counts"], // 캐싱 키 - { - revalidate: 3600, - } - )(); -} - -/** - * 벤더 상세 정보 조회 - */ -export async function getTechVendorById(id: number) { - return unstable_cache( - async () => { - try { - const result = await getTechVendorDetailById(id); - return { data: result }; - } catch (err) { - console.error("기술영업 벤더 상세 조회 오류:", err); - return { data: null }; - } - }, - [`tech-vendor-${id}`], - { - revalidate: 3600, - tags: ["tech-vendors", `tech-vendor-${id}`], - } - )(); -} - -/* ----------------------------------------------------- - 2) 생성(Create) ------------------------------------------------------ */ - -/** - * 첨부파일 저장 헬퍼 함수 - */ -async function storeTechVendorFiles( - tx: any, - vendorId: number, - files: File[], - attachmentType: string -) { - - for (const file of files) { - - const saveResult = await saveDRMFile(file, decryptWithServerAction, `tech-vendors/${vendorId}`) - - // Insert attachment record - await tx.insert(techVendorAttachments).values({ - vendorId, - fileName: file.name, - filePath: saveResult.publicPath, - attachmentType, - }); - } -} - -/** - * 신규 기술영업 벤더 생성 - */ -export async function createTechVendor(input: CreateTechVendorSchema) { - unstable_noStore(); - - try { - // taxId 중복 검사 - const existingVendor = await db - .select({ id: techVendors.id }) - .from(techVendors) - .where(eq(techVendors.taxId, input.taxId)) - .limit(1); - - // 이미 동일한 taxId를 가진 업체가 존재하면 에러 반환 - if (existingVendor.length > 0) { - return { - success: false, - data: null, - error: `이미 등록된 사업자등록번호입니다. (Tax ID ${input.taxId} already exists in the system)` - }; - } - - const result = await db.transaction(async (tx) => { - // 1. 벤더 생성 - const [newVendor] = await insertTechVendor(tx, { - vendorName: input.vendorName, - vendorCode: input.vendorCode || null, - taxId: input.taxId, - address: input.address || null, - country: input.country, - countryEng: null, - countryFab: null, - agentName: null, - agentPhone: null, - agentEmail: null, - phone: input.phone || null, - email: input.email, - website: input.website || null, - techVendorType: Array.isArray(input.techVendorType) ? input.techVendorType.join(',') : input.techVendorType, - representativeName: input.representativeName || null, - representativeBirth: input.representativeBirth || null, - representativeEmail: input.representativeEmail || null, - representativePhone: input.representativePhone || null, - items: input.items || null, - status: "ACTIVE" - }); - - // 2. 연락처 정보 등록 - for (const contact of input.contacts) { - await insertTechVendorContact(tx, { - vendorId: newVendor.id, - contactName: contact.contactName, - contactPosition: contact.contactPosition || null, - contactEmail: contact.contactEmail, - contactPhone: contact.contactPhone || null, - isPrimary: contact.isPrimary ?? false, - }); - } - - // 3. 첨부파일 저장 - if (input.files && input.files.length > 0) { - await storeTechVendorFiles(tx, newVendor.id, input.files, "GENERAL"); - } - - return newVendor; - }); - - revalidateTag("tech-vendors"); - - return { - success: true, - data: result, - error: null - }; - } catch (err) { - console.error("기술영업 벤더 생성 오류:", err); - - return { - success: false, - data: null, - error: getErrorMessage(err) - }; - } -} - -/* ----------------------------------------------------- - 3) 업데이트 (단건/복수) ------------------------------------------------------ */ - -/** 단건 업데이트 */ -export async function modifyTechVendor( - input: UpdateTechVendorSchema & { id: string; } -) { - unstable_noStore(); - try { - const updated = await db.transaction(async (tx) => { - // 벤더 정보 업데이트 - const [res] = await updateTechVendor(tx, input.id, { - vendorName: input.vendorName, - vendorCode: input.vendorCode, - address: input.address, - country: input.country, - phone: input.phone, - email: input.email, - website: input.website, - status: input.status, - }); - - return res; - }); - - // 캐시 무효화 - revalidateTag("tech-vendors"); - revalidateTag(`tech-vendor-${input.id}`); - - return { data: updated, error: null }; - } catch (err) { - return { data: null, error: getErrorMessage(err) }; - } -} - -/** 복수 업데이트 */ -export async function modifyTechVendors(input: { - ids: string[]; - status?: TechVendor["status"]; -}) { - unstable_noStore(); - try { - const data = await db.transaction(async (tx) => { - // 여러 협력업체 일괄 업데이트 - const [updated] = await updateTechVendors(tx, input.ids, { - // 예: 상태만 일괄 변경 - status: input.status, - }); - return updated; - }); - - // 캐시 무효화 - revalidateTag("tech-vendors"); - revalidateTag("tech-vendor-status-counts"); - - return { data: null, error: null }; - } catch (err) { - return { data: null, error: getErrorMessage(err) }; - } -} - -/* ----------------------------------------------------- - 4) 연락처 관리 ------------------------------------------------------ */ - -export async function getTechVendorContacts(input: GetTechVendorContactsSchema, id: number) { - return unstable_cache( - async () => { - try { - const offset = (input.page - 1) * input.perPage; - - // 필터링 설정 - const advancedWhere = filterColumns({ - table: techVendorContacts, - filters: input.filters, - joinOperator: input.joinOperator, - }); - - // 검색 조건 - let globalWhere; - if (input.search) { - const s = `%${input.search}%`; - globalWhere = or( - ilike(techVendorContacts.contactName, s), - ilike(techVendorContacts.contactPosition, s), - ilike(techVendorContacts.contactEmail, s), - ilike(techVendorContacts.contactPhone, s) - ); - } - - // 해당 벤더 조건 - const vendorWhere = eq(techVendorContacts.vendorId, id); - - // 최종 조건 결합 - const finalWhere = and(advancedWhere, globalWhere, vendorWhere); - - // 정렬 조건 - const orderBy = - input.sort.length > 0 - ? input.sort.map((item) => - item.desc ? desc(techVendorContacts[item.id]) : asc(techVendorContacts[item.id]) - ) - : [asc(techVendorContacts.createdAt)]; - - // 트랜잭션 내부에서 Repository 호출 - const { data, total } = await db.transaction(async (tx) => { - const data = await selectTechVendorContacts(tx, { - where: finalWhere, - orderBy, - offset, - limit: input.perPage, - }); - const total = await countTechVendorContacts(tx, finalWhere); - return { data, total }; - }); - - const pageCount = Math.ceil(total / input.perPage); - - return { data, pageCount }; - } catch (err) { - // 에러 발생 시 디폴트 - return { data: [], pageCount: 0 }; - } - }, - [JSON.stringify(input), String(id)], // 캐싱 키 - { - revalidate: 3600, - tags: [`tech-vendor-contacts-${id}`], - } - )(); -} - -export async function createTechVendorContact(input: CreateTechVendorContactSchema) { - unstable_noStore(); - try { - await db.transaction(async (tx) => { - // DB Insert - const [newContact] = await insertTechVendorContact(tx, { - vendorId: input.vendorId, - contactName: input.contactName, - contactPosition: input.contactPosition || "", - contactEmail: input.contactEmail, - contactPhone: input.contactPhone || "", - country: input.country || "", - isPrimary: input.isPrimary || false, - }); - - return newContact; - }); - - // 캐시 무효화 - revalidateTag(`tech-vendor-contacts-${input.vendorId}`); - revalidateTag("users"); - - return { data: null, error: null }; - } catch (err) { - return { data: null, error: getErrorMessage(err) }; - } -} - -/* ----------------------------------------------------- - 5) 아이템 관리 ------------------------------------------------------ */ - -export async function getTechVendorItems(input: GetTechVendorItemsSchema, id: number) { - return unstable_cache( - async () => { - try { - const offset = (input.page - 1) * input.perPage; - - // 필터링 설정 - const advancedWhere = filterColumns({ - table: techVendorItemsView, - filters: input.filters, - joinOperator: input.joinOperator, - }); - - // 검색 조건 - let globalWhere; - if (input.search) { - const s = `%${input.search}%`; - globalWhere = or( - ilike(techVendorItemsView.itemCode, s) - ); - } - - // 해당 벤더 조건 - const vendorWhere = eq(techVendorItemsView.vendorId, id); - - // 최종 조건 결합 - const finalWhere = and(advancedWhere, globalWhere, vendorWhere); - - // 정렬 조건 - const orderBy = - input.sort.length > 0 - ? input.sort.map((item) => - item.desc ? desc(techVendorItemsView[item.id]) : asc(techVendorItemsView[item.id]) - ) - : [asc(techVendorItemsView.createdAt)]; - - // 트랜잭션 내부에서 Repository 호출 - const { data, total } = await db.transaction(async (tx) => { - const data = await selectTechVendorItems(tx, { - where: finalWhere, - orderBy, - offset, - limit: input.perPage, - }); - const total = await countTechVendorItems(tx, finalWhere); - return { data, total }; - }); - - const pageCount = Math.ceil(total / input.perPage); - - return { data, pageCount }; - } catch (err) { - // 에러 발생 시 디폴트 - return { data: [], pageCount: 0 }; - } - }, - [JSON.stringify(input), String(id)], // 캐싱 키 - { - revalidate: 3600, - tags: [`tech-vendor-items-${id}`], - } - )(); -} - -export interface ItemDropdownOption { - itemCode: string; - itemList: string; - workType: string | null; - shipTypes: string | null; - subItemList: string | null; -} - -/** - * Vendor Item 추가 시 사용할 아이템 목록 조회 (전체 목록 반환) - * 아이템 코드, 이름, 설명만 간소화해서 반환 - */ -export async function getItemsForTechVendor(vendorId: number) { - return unstable_cache( - async () => { - try { - // 1. 벤더 정보 조회로 벤더 타입 확인 - const vendor = await db.query.techVendors.findFirst({ - where: eq(techVendors.id, vendorId), - columns: { - techVendorType: true - } - }); - - if (!vendor) { - return { - data: [], - error: "벤더를 찾을 수 없습니다.", - }; - } - - // 2. 해당 벤더가 이미 가지고 있는 itemCode 목록 조회 - const existingItems = await db - .select({ - itemCode: techVendorPossibleItems.itemCode, - }) - .from(techVendorPossibleItems) - .where(eq(techVendorPossibleItems.vendorId, vendorId)); - - const existingItemCodes = existingItems.map(item => item.itemCode); - - // 3. 벤더 타입에 따라 해당 타입의 아이템만 조회 - // let availableItems: ItemDropdownOption[] = []; - let availableItems: (typeof itemShipbuilding.$inferSelect | typeof itemOffshoreTop.$inferSelect | typeof itemOffshoreHull.$inferSelect)[] = []; - switch (vendor.techVendorType) { - case "조선": - const shipbuildingItems = await db - .select({ - id: itemShipbuilding.id, - createdAt: itemShipbuilding.createdAt, - updatedAt: itemShipbuilding.updatedAt, - itemCode: itemShipbuilding.itemCode, - itemList: itemShipbuilding.itemList, - workType: itemShipbuilding.workType, - shipTypes: itemShipbuilding.shipTypes, - }) - .from(itemShipbuilding) - .where( - existingItemCodes.length > 0 - ? not(inArray(itemShipbuilding.itemCode, existingItemCodes)) - : undefined - ) - .orderBy(asc(itemShipbuilding.itemCode)); - - availableItems = shipbuildingItems - .filter(item => item.itemCode != null) - .map(item => ({ - id: item.id, - createdAt: item.createdAt, - updatedAt: item.updatedAt, - itemCode: item.itemCode!, - itemList: item.itemList || "조선 아이템", - workType: item.workType || "조선 관련 아이템", - shipTypes: item.shipTypes || "조선 관련 아이템" - })); - break; - - case "해양TOP": - const offshoreTopItems = await db - .select({ - id: itemOffshoreTop.id, - createdAt: itemOffshoreTop.createdAt, - updatedAt: itemOffshoreTop.updatedAt, - itemCode: itemOffshoreTop.itemCode, - itemList: itemOffshoreTop.itemList, - workType: itemOffshoreTop.workType, - subItemList: itemOffshoreTop.subItemList, - }) - .from(itemOffshoreTop) - .where( - existingItemCodes.length > 0 - ? not(inArray(itemOffshoreTop.itemCode, existingItemCodes)) - : undefined - ) - .orderBy(asc(itemOffshoreTop.itemCode)); - - availableItems = offshoreTopItems - .filter(item => item.itemCode != null) - .map(item => ({ - id: item.id, - createdAt: item.createdAt, - updatedAt: item.updatedAt, - itemCode: item.itemCode!, - itemList: item.itemList || "해양TOP 아이템", - workType: item.workType || "해양TOP 관련 아이템", - subItemList: item.subItemList || "해양TOP 관련 아이템" - })); - break; - - case "해양HULL": - const offshoreHullItems = await db - .select({ - id: itemOffshoreHull.id, - createdAt: itemOffshoreHull.createdAt, - updatedAt: itemOffshoreHull.updatedAt, - itemCode: itemOffshoreHull.itemCode, - itemList: itemOffshoreHull.itemList, - workType: itemOffshoreHull.workType, - subItemList: itemOffshoreHull.subItemList, - }) - .from(itemOffshoreHull) - .where( - existingItemCodes.length > 0 - ? not(inArray(itemOffshoreHull.itemCode, existingItemCodes)) - : undefined - ) - .orderBy(asc(itemOffshoreHull.itemCode)); - - availableItems = offshoreHullItems - .filter(item => item.itemCode != null) - .map(item => ({ - id: item.id, - createdAt: item.createdAt, - updatedAt: item.updatedAt, - itemCode: item.itemCode!, - itemList: item.itemList || "해양HULL 아이템", - workType: item.workType || "해양HULL 관련 아이템", - subItemList: item.subItemList || "해양HULL 관련 아이템" - })); - break; - - default: - return { - data: [], - error: `지원하지 않는 벤더 타입입니다: ${vendor.techVendorType}`, - }; - } - - return { - data: availableItems, - error: null - }; - } catch (err) { - console.error("Failed to fetch items for tech vendor dropdown:", err); - return { - data: [], - error: "아이템 목록을 불러오는데 실패했습니다.", - }; - } - }, - // 캐시 키를 vendorId 별로 달리 해야 한다. - ["items-for-tech-vendor", String(vendorId)], - { - revalidate: 3600, // 1시간 캐싱 - tags: ["items"], // revalidateTag("items") 호출 시 무효화 - } - )(); -} - -/** - * 벤더 타입과 아이템 코드에 따른 아이템 조회 - */ -export async function getItemsByVendorType(vendorType: string, itemCode: string) { - try { - let items: (typeof itemShipbuilding.$inferSelect | typeof itemOffshoreTop.$inferSelect | typeof itemOffshoreHull.$inferSelect)[] = []; - - switch (vendorType) { - case "조선": - const shipbuildingResults = await db - .select({ - id: itemShipbuilding.id, - itemCode: itemShipbuilding.itemCode, - workType: itemShipbuilding.workType, - shipTypes: itemShipbuilding.shipTypes, - itemList: itemShipbuilding.itemList, - createdAt: itemShipbuilding.createdAt, - updatedAt: itemShipbuilding.updatedAt, - }) - .from(itemShipbuilding) - .where(itemCode ? eq(itemShipbuilding.itemCode, itemCode) : undefined); - items = shipbuildingResults; - break; - - case "해양TOP": - const offshoreTopResults = await db - .select({ - id: itemOffshoreTop.id, - itemCode: itemOffshoreTop.itemCode, - workType: itemOffshoreTop.workType, - itemList: itemOffshoreTop.itemList, - subItemList: itemOffshoreTop.subItemList, - createdAt: itemOffshoreTop.createdAt, - updatedAt: itemOffshoreTop.updatedAt, - }) - .from(itemOffshoreTop) - .where(itemCode ? eq(itemOffshoreTop.itemCode, itemCode) : undefined); - items = offshoreTopResults; - break; - - case "해양HULL": - const offshoreHullResults = await db - .select({ - id: itemOffshoreHull.id, - itemCode: itemOffshoreHull.itemCode, - workType: itemOffshoreHull.workType, - itemList: itemOffshoreHull.itemList, - subItemList: itemOffshoreHull.subItemList, - createdAt: itemOffshoreHull.createdAt, - updatedAt: itemOffshoreHull.updatedAt, - }) - .from(itemOffshoreHull) - .where(itemCode ? eq(itemOffshoreHull.itemCode, itemCode) : undefined); - items = offshoreHullResults; - break; - - default: - items = []; - } - - const result = items.map(item => ({ - ...item, - techVendorType: vendorType - })); - - return { data: result, error: null }; - } catch (err) { - console.error("Error fetching items by vendor type:", err); - return { data: [], error: "Failed to fetch items" }; - } -} - -/** - * 벤더의 possible_items를 조회하고 해당 아이템 코드로 각 타입별 테이블을 조회 - * 벤더 타입이 콤마로 구분된 경우 (예: "조선,해양TOP,해양HULL") 모든 타입의 아이템을 조회 - */ -export async function getVendorItemsByType(vendorId: number, vendorType: string) { - try { - // 벤더의 possible_items 조회 - const possibleItems = await db.query.techVendorPossibleItems.findMany({ - where: eq(techVendorPossibleItems.vendorId, vendorId), - columns: { - itemCode: true - } - }) - - const itemCodes = possibleItems.map(item => item.itemCode) - - if (itemCodes.length === 0) { - return { data: [] } - } - - // 벤더 타입을 콤마로 분리 - const vendorTypes = vendorType.split(',').map(type => type.trim()) - const allItems: Array & { techVendorType: "조선" | "해양TOP" | "해양HULL" }> = [] - - // 각 벤더 타입에 따라 해당하는 테이블에서 아이템 조회 - for (const singleType of vendorTypes) { - switch (singleType) { - case "조선": - const shipbuildingItems = await db.query.itemShipbuilding.findMany({ - where: inArray(itemShipbuilding.itemCode, itemCodes) - }) - allItems.push(...shipbuildingItems.map(item => ({ - ...item, - techVendorType: "조선" as const - }))) - break - - case "해양TOP": - const offshoreTopItems = await db.query.itemOffshoreTop.findMany({ - where: inArray(itemOffshoreTop.itemCode, itemCodes) - }) - allItems.push(...offshoreTopItems.map(item => ({ - ...item, - techVendorType: "해양TOP" as const - }))) - break - - case "해양HULL": - const offshoreHullItems = await db.query.itemOffshoreHull.findMany({ - where: inArray(itemOffshoreHull.itemCode, itemCodes) - }) - allItems.push(...offshoreHullItems.map(item => ({ - ...item, - techVendorType: "해양HULL" as const - }))) - break - - default: - console.warn(`Unknown vendor type: ${singleType}`) - break - } - } - - // 중복 허용 - 모든 아이템을 그대로 반환 - return { - data: allItems.sort((a, b) => a.itemCode.localeCompare(b.itemCode)) - } - } catch (err) { - console.error("Error getting vendor items by type:", err) - return { data: [] } - } -} - -export async function createTechVendorItem(input: CreateTechVendorItemSchema) { - unstable_noStore(); - try { - // DB에 이미 존재하는지 확인 - const existingItem = await db - .select({ id: techVendorPossibleItems.id }) - .from(techVendorPossibleItems) - .where( - and( - eq(techVendorPossibleItems.vendorId, input.vendorId), - eq(techVendorPossibleItems.itemCode, input.itemCode) - ) - ) - .limit(1); - - if (existingItem.length > 0) { - return { data: null, error: "이미 추가된 아이템입니다." }; - } - - await db.transaction(async (tx) => { - // DB Insert - const [newItem] = await tx - .insert(techVendorPossibleItems) - .values({ - vendorId: input.vendorId, - itemCode: input.itemCode, - }) - .returning(); - return newItem; - }); - - // 캐시 무효화 - revalidateTag(`tech-vendor-items-${input.vendorId}`); - - return { data: null, error: null }; - } catch (err) { - return { data: null, error: getErrorMessage(err) }; - } -} - -/* ----------------------------------------------------- - 6) 기술영업 벤더 승인/거부 ------------------------------------------------------ */ - -interface ApproveTechVendorsInput { - ids: string[]; -} - -/** - * 기술영업 벤더 승인 (상태를 ACTIVE로 변경) - */ -export async function approveTechVendors(input: ApproveTechVendorsInput) { - unstable_noStore(); - - try { - // 트랜잭션 내에서 협력업체 상태 업데이트 - const result = await db.transaction(async (tx) => { - // 협력업체 상태 업데이트 - const [updated] = await tx - .update(techVendors) - .set({ - status: "ACTIVE", - updatedAt: new Date() - }) - .where(inArray(techVendors.id, input.ids.map(id => parseInt(id)))) - .returning(); - - return updated; - }); - - // 캐시 무효화 - revalidateTag("tech-vendors"); - revalidateTag("tech-vendor-status-counts"); - - return { data: result, error: null }; - } catch (err) { - console.error("Error approving tech vendors:", err); - return { data: null, error: getErrorMessage(err) }; - } -} - -/** - * 기술영업 벤더 거부 (상태를 REJECTED로 변경) - */ -export async function rejectTechVendors(input: ApproveTechVendorsInput) { - unstable_noStore(); - - try { - // 트랜잭션 내에서 협력업체 상태 업데이트 - const result = await db.transaction(async (tx) => { - // 협력업체 상태 업데이트 - const [updated] = await tx - .update(techVendors) - .set({ - status: "INACTIVE", - updatedAt: new Date() - }) - .where(inArray(techVendors.id, input.ids.map(id => parseInt(id)))) - .returning(); - - return updated; - }); - - // 캐시 무효화 - revalidateTag("tech-vendors"); - revalidateTag("tech-vendor-status-counts"); - - return { data: result, error: null }; - } catch (err) { - console.error("Error rejecting tech vendors:", err); - return { data: null, error: getErrorMessage(err) }; - } -} - -/* ----------------------------------------------------- - 7) 엑셀 내보내기 ------------------------------------------------------ */ - -/** - * 벤더 연락처 목록 엑셀 내보내기 - */ -export async function exportTechVendorContacts(vendorId: number) { - try { - const contacts = await db - .select() - .from(techVendorContacts) - .where(eq(techVendorContacts.vendorId, vendorId)) - .orderBy(techVendorContacts.isPrimary, techVendorContacts.contactName); - - return contacts; - } catch (err) { - console.error("기술영업 벤더 연락처 내보내기 오류:", err); - return []; - } -} - -/** - * 벤더 아이템 목록 엑셀 내보내기 - */ -export async function exportTechVendorItems(vendorId: number) { - try { - const items = await db - .select({ - id: techVendorItemsView.vendorItemId, - vendorId: techVendorItemsView.vendorId, - itemCode: techVendorItemsView.itemCode, - createdAt: techVendorItemsView.createdAt, - updatedAt: techVendorItemsView.updatedAt, - }) - .from(techVendorItemsView) - .where(eq(techVendorItemsView.vendorId, vendorId)) - - return items; - } catch (err) { - console.error("기술영업 벤더 아이템 내보내기 오류:", err); - return []; - } -} - -/** - * 벤더 정보 엑셀 내보내기 - */ -export async function exportTechVendorDetails(vendorIds: number[]) { - try { - if (!vendorIds.length) return []; - - // 벤더 기본 정보 조회 - const vendorsData = await db - .select({ - id: techVendors.id, - vendorName: techVendors.vendorName, - vendorCode: techVendors.vendorCode, - taxId: techVendors.taxId, - address: techVendors.address, - country: techVendors.country, - phone: techVendors.phone, - email: techVendors.email, - website: techVendors.website, - status: techVendors.status, - representativeName: techVendors.representativeName, - representativeEmail: techVendors.representativeEmail, - representativePhone: techVendors.representativePhone, - representativeBirth: techVendors.representativeBirth, - items: techVendors.items, - createdAt: techVendors.createdAt, - updatedAt: techVendors.updatedAt, - }) - .from(techVendors) - .where( - vendorIds.length === 1 - ? eq(techVendors.id, vendorIds[0]) - : inArray(techVendors.id, vendorIds) - ); - - // 벤더별 상세 정보를 포함하여 반환 - const vendorsWithDetails = await Promise.all( - vendorsData.map(async (vendor) => { - // 연락처 조회 - const contacts = await exportTechVendorContacts(vendor.id); - - // 아이템 조회 - const items = await exportTechVendorItems(vendor.id); - - return { - ...vendor, - vendorContacts: contacts, - vendorItems: items, - }; - }) - ); - - return vendorsWithDetails; - } catch (err) { - console.error("기술영업 벤더 상세 내보내기 오류:", err); - return []; - } -} - -/** - * 기술영업 벤더 상세 정보 조회 (연락처, 첨부파일 포함) - */ -export async function getTechVendorDetailById(id: number) { - try { - const vendor = await db.select().from(techVendors).where(eq(techVendors.id, id)).limit(1); - - if (!vendor || vendor.length === 0) { - console.error(`Vendor not found with id: ${id}`); - return null; - } - - const contacts = await db.select().from(techVendorContacts).where(eq(techVendorContacts.vendorId, id)); - const attachments = await db.select().from(techVendorAttachments).where(eq(techVendorAttachments.vendorId, id)); - const possibleItems = await db.select().from(techVendorPossibleItems).where(eq(techVendorPossibleItems.vendorId, id)); - - return { - ...vendor[0], - contacts, - attachments, - possibleItems - }; - } catch (error) { - console.error("Error fetching tech vendor detail:", error); - return null; - } -} - -/** - * 기술영업 벤더 첨부파일 다운로드를 위한 서버 액션 - * @param vendorId 기술영업 벤더 ID - * @param fileId 특정 파일 ID (단일 파일 다운로드시) - * @returns 다운로드할 수 있는 임시 URL - */ -export async function downloadTechVendorAttachments(vendorId:number, fileId?:number) { - try { - // API 경로 생성 (단일 파일 또는 모든 파일) - const url = fileId - ? `/api/tech-vendors/attachments/download?id=${fileId}&vendorId=${vendorId}` - : `/api/tech-vendors/attachments/download-all?vendorId=${vendorId}`; - - // fetch 요청 (기본적으로 Blob으로 응답 받기) - const response = await fetch(url, { - method: 'GET', - headers: { - 'Content-Type': 'application/json', - }, - }); - - if (!response.ok) { - throw new Error(`Server responded with ${response.status}: ${response.statusText}`); - } - - // 파일명 가져오기 (Content-Disposition 헤더에서) - const contentDisposition = response.headers.get('content-disposition'); - let fileName = fileId ? `file-${fileId}.zip` : `tech-vendor-${vendorId}-files.zip`; - - if (contentDisposition) { - const matches = /filename[^;=\n]*=((['"]).*?\2|[^;\n]*)/.exec(contentDisposition); - if (matches && matches[1]) { - fileName = matches[1].replace(/['"]/g, ''); - } - } - - // Blob으로 응답 변환 - const blob = await response.blob(); - - // Blob URL 생성 - const blobUrl = window.URL.createObjectURL(blob); - - return { - url: blobUrl, - fileName, - blob - }; - } catch (error) { - console.error('Download API error:', error); - throw error; - } -} - -/** - * 임시 ZIP 파일 정리를 위한 서버 액션 - * @param fileName 정리할 파일명 - */ -export async function cleanupTechTempFiles(fileName: string) { - 'use server'; - - try { - - await deleteFile(`tmp/${fileName}`) - - return { success: true }; - } catch (error) { - console.error('임시 파일 정리 오류:', error); - return { success: false, error: '임시 파일 정리 중 오류가 발생했습니다.' }; - } -} - -export const findVendorById = async (id: number): Promise => { - try { - // 직접 DB에서 조회 - const vendor = await db - .select() - .from(techVendors) - .where(eq(techVendors.id, id)) - .limit(1) - .then(rows => rows[0] || null); - - if (!vendor) { - console.error(`Vendor not found with id: ${id}`); - return null; - } - - return vendor; - } catch (error) { - console.error('Error fetching vendor:', error); - return null; - } -}; - -/* ----------------------------------------------------- - 8) 기술영업 벤더 RFQ 히스토리 조회 ------------------------------------------------------ */ - -/** - * 기술영업 벤더의 RFQ 히스토리 조회 (간단한 버전) - */ -export async function getTechVendorRfqHistory(input: GetTechVendorRfqHistorySchema, id:number) { - try { - - // 먼저 해당 벤더의 견적서가 있는지 확인 - const { techSalesVendorQuotations } = await import("@/db/schema/techSales"); - - const quotationCheck = await db - .select({ count: sql`count(*)`.as("count") }) - .from(techSalesVendorQuotations) - .where(eq(techSalesVendorQuotations.vendorId, id)); - - console.log(`벤더 ${id}의 견적서 개수:`, quotationCheck[0]?.count); - - if (quotationCheck[0]?.count === 0) { - console.log("해당 벤더의 견적서가 없습니다."); - return { data: [], pageCount: 0 }; - } - - const offset = (input.page - 1) * input.perPage; - const { techSalesRfqs } = await import("@/db/schema/techSales"); - const { biddingProjects } = await import("@/db/schema/projects"); - - // 간단한 조회 - let whereCondition = eq(techSalesVendorQuotations.vendorId, id); - - // 검색이 있다면 추가 - if (input.search) { - const s = `%${input.search}%`; - const searchCondition = and( - whereCondition, - or( - ilike(techSalesRfqs.rfqCode, s), - ilike(techSalesRfqs.description, s), - ilike(biddingProjects.pspid, s), - ilike(biddingProjects.projNm, s) - ) - ); - whereCondition = searchCondition; - } - - // 데이터 조회 - 테이블에 필요한 필드들 (프로젝트 타입 추가) - const data = await db - .select({ - id: techSalesRfqs.id, - rfqCode: techSalesRfqs.rfqCode, - description: techSalesRfqs.description, - projectCode: biddingProjects.pspid, - projectName: biddingProjects.projNm, - projectType: biddingProjects.pjtType, // 프로젝트 타입 추가 - status: techSalesRfqs.status, - totalAmount: techSalesVendorQuotations.totalPrice, - currency: techSalesVendorQuotations.currency, - dueDate: techSalesRfqs.dueDate, - createdAt: techSalesRfqs.createdAt, - quotationCode: techSalesVendorQuotations.quotationCode, - submittedAt: techSalesVendorQuotations.submittedAt, - }) - .from(techSalesVendorQuotations) - .innerJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id)) - .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) - .where(whereCondition) - .orderBy(desc(techSalesRfqs.createdAt)) - .limit(input.perPage) - .offset(offset); - - console.log("조회된 데이터:", data.length, "개"); - - // 전체 개수 조회 - const totalResult = await db - .select({ count: sql`count(*)`.as("count") }) - .from(techSalesVendorQuotations) - .innerJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id)) - .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) - .where(whereCondition); - - const total = totalResult[0]?.count || 0; - const pageCount = Math.ceil(total / input.perPage); - - console.log("기술영업 벤더 RFQ 히스토리 조회 완료", { - id, - dataLength: data.length, - total, - pageCount - }); - - return { data, pageCount }; - } catch (err) { - console.error("기술영업 벤더 RFQ 히스토리 조회 오류:", { - err, - id, - stack: err instanceof Error ? err.stack : undefined - }); - return { data: [], pageCount: 0 }; - } -} - -/** - * 기술영업 벤더 엑셀 import 시 유저 생성 및 아이템 등록 - */ -export async function importTechVendorsFromExcel( - vendors: Array<{ - vendorName: string; - vendorCode?: string | null; - email: string; - taxId: string; - country?: string | null; - countryEng?: string | null; - countryFab?: string | null; - agentName?: string | null; - agentPhone?: string | null; - agentEmail?: string | null; - address?: string | null; - phone?: string | null; - website?: string | null; - techVendorType: string; - representativeName?: string | null; - representativeEmail?: string | null; - representativePhone?: string | null; - representativeBirth?: string | null; - items: string; - }>, -) { - unstable_noStore(); - - try { - console.log("Import 시작 - 벤더 수:", vendors.length); - console.log("첫 번째 벤더 데이터:", vendors[0]); - - const result = await db.transaction(async (tx) => { - const createdVendors = []; - - for (const vendor of vendors) { - console.log("벤더 처리 시작:", vendor.vendorName); - - try { - // 1. 벤더 생성 - console.log("벤더 생성 시도:", { - vendorName: vendor.vendorName, - email: vendor.email, - techVendorType: vendor.techVendorType - }); - - const [newVendor] = await tx.insert(techVendors).values({ - vendorName: vendor.vendorName, - vendorCode: vendor.vendorCode || null, - taxId: vendor.taxId, - country: vendor.country || null, - countryEng: vendor.countryEng || null, - countryFab: vendor.countryFab || null, - agentName: vendor.agentName || null, - agentPhone: vendor.agentPhone || null, - agentEmail: vendor.agentEmail || null, - address: vendor.address || null, - phone: vendor.phone || null, - email: vendor.email, - website: vendor.website || null, - techVendorType: vendor.techVendorType, - status: "ACTIVE", - representativeName: vendor.representativeName || null, - representativeEmail: vendor.representativeEmail || null, - representativePhone: vendor.representativePhone || null, - representativeBirth: vendor.representativeBirth || null, - }).returning(); - - console.log("벤더 생성 성공:", newVendor.id); - - // 2. 유저 생성 (이메일이 있는 경우) - if (vendor.email) { - console.log("유저 생성 시도:", vendor.email); - - // 이미 존재하는 유저인지 확인 - const existingUser = await tx.query.users.findFirst({ - where: eq(users.email, vendor.email), - columns: { id: true } - }); - - // 유저가 존재하지 않는 경우에만 생성 - if (!existingUser) { - await tx.insert(users).values({ - name: vendor.vendorName, - email: vendor.email, - techCompanyId: newVendor.id, // techCompanyId 설정 - domain: "partners", - }); - console.log("유저 생성 성공"); - } else { - console.log("이미 존재하는 유저:", existingUser.id); - } - } - - createdVendors.push(newVendor); - console.log("벤더 처리 완료:", vendor.vendorName); - } catch (error) { - console.error("벤더 처리 중 오류 발생:", vendor.vendorName, error); - throw error; - } - } - - console.log("모든 벤더 처리 완료. 생성된 벤더 수:", createdVendors.length); - return createdVendors; - }); - - // 캐시 무효화 - revalidateTag("tech-vendors"); - revalidateTag("users"); - - console.log("Import 완료 - 결과:", result); - return { success: true, data: result }; - } catch (error) { - console.error("Import 실패:", error); - return { success: false, error: getErrorMessage(error) }; - } -} - -export async function findTechVendorById(id: number): Promise { - const result = await db - .select() - .from(techVendors) - .where(eq(techVendors.id, id)) - .limit(1) - - return result[0] || null -} - -/** - * 회원가입 폼을 통한 기술영업 벤더 생성 (초대 토큰 기반) - */ -export async function createTechVendorFromSignup(params: { - vendorData: { - vendorName: string - vendorCode?: string - items: string - website?: string - taxId: string - address?: string - email: string - phone?: string - country: string - techVendorType: "조선" | "해양TOP" | "해양HULL" - representativeName?: string - representativeBirth?: string - representativeEmail?: string - representativePhone?: string - } - files?: File[] - contacts: { - contactName: string - contactPosition?: string - contactEmail: string - contactPhone?: string - isPrimary?: boolean - }[] - invitationToken?: string // 초대 토큰 -}) { - unstable_noStore(); - - try { - console.log("기술영업 벤더 회원가입 시작:", params.vendorData.vendorName); - - // 초대 토큰 검증 - let existingVendorId: number | null = null; - if (params.invitationToken) { - const { verifyTechVendorInvitationToken } = await import("@/lib/tech-vendor-invitation-token"); - const tokenPayload = await verifyTechVendorInvitationToken(params.invitationToken); - - if (!tokenPayload) { - throw new Error("유효하지 않은 초대 토큰입니다."); - } - - existingVendorId = tokenPayload.vendorId; - console.log("초대 토큰 검증 성공, 벤더 ID:", existingVendorId); - } - - const result = await db.transaction(async (tx) => { - let vendorResult; - - if (existingVendorId) { - // 기존 벤더 정보 업데이트 - const [updatedVendor] = await tx.update(techVendors) - .set({ - vendorName: params.vendorData.vendorName, - vendorCode: params.vendorData.vendorCode || null, - taxId: params.vendorData.taxId, - country: params.vendorData.country, - address: params.vendorData.address || null, - phone: params.vendorData.phone || null, - email: params.vendorData.email, - website: params.vendorData.website || null, - techVendorType: params.vendorData.techVendorType, - status: "QUOTE_COMPARISON", // 가입 완료 시 QUOTE_COMPARISON으로 변경 - representativeName: params.vendorData.representativeName || null, - representativeEmail: params.vendorData.representativeEmail || null, - representativePhone: params.vendorData.representativePhone || null, - representativeBirth: params.vendorData.representativeBirth || null, - items: params.vendorData.items, - updatedAt: new Date(), - }) - .where(eq(techVendors.id, existingVendorId)) - .returning(); - - vendorResult = updatedVendor; - console.log("기존 벤더 정보 업데이트 완료:", vendorResult.id); - } else { - // 1. 이메일 중복 체크 (새 벤더인 경우) - const existingVendor = await tx.query.techVendors.findFirst({ - where: eq(techVendors.email, params.vendorData.email), - columns: { id: true, vendorName: true } - }); - - if (existingVendor) { - throw new Error(`이미 등록된 이메일입니다: ${params.vendorData.email}`); - } - - // 2. 새 벤더 생성 - const [newVendor] = await tx.insert(techVendors).values({ - vendorName: params.vendorData.vendorName, - vendorCode: params.vendorData.vendorCode || null, - taxId: params.vendorData.taxId, - country: params.vendorData.country, - address: params.vendorData.address || null, - phone: params.vendorData.phone || null, - email: params.vendorData.email, - website: params.vendorData.website || null, - techVendorType: params.vendorData.techVendorType, - status: "ACTIVE", - isQuoteComparison: false, - representativeName: params.vendorData.representativeName || null, - representativeEmail: params.vendorData.representativeEmail || null, - representativePhone: params.vendorData.representativePhone || null, - representativeBirth: params.vendorData.representativeBirth || null, - items: params.vendorData.items, - }).returning(); - - vendorResult = newVendor; - console.log("새 벤더 생성 완료:", vendorResult.id); - } - - // 이 부분은 위에서 이미 처리되었으므로 주석 처리 - - // 3. 연락처 생성 - if (params.contacts && params.contacts.length > 0) { - for (const [index, contact] of params.contacts.entries()) { - await tx.insert(techVendorContacts).values({ - vendorId: vendorResult.id, - contactName: contact.contactName, - contactPosition: contact.contactPosition || null, - contactEmail: contact.contactEmail, - contactPhone: contact.contactPhone || null, - isPrimary: index === 0, // 첫 번째 연락처를 primary로 설정 - }); - } - console.log("연락처 생성 완료:", params.contacts.length, "개"); - } - - // 4. 첨부파일 처리 - if (params.files && params.files.length > 0) { - await storeTechVendorFiles(tx, vendorResult.id, params.files, "GENERAL"); - console.log("첨부파일 저장 완료:", params.files.length, "개"); - } - - // 5. 유저 생성 (techCompanyId 설정) - console.log("유저 생성 시도:", params.vendorData.email); - - const existingUser = await tx.query.users.findFirst({ - where: eq(users.email, params.vendorData.email), - columns: { id: true, techCompanyId: true } - }); - - let userId = null; - if (!existingUser) { - const [newUser] = await tx.insert(users).values({ - name: params.vendorData.vendorName, - email: params.vendorData.email, - techCompanyId: vendorResult.id, // 중요: techCompanyId 설정 - domain: "partners", - }).returning(); - userId = newUser.id; - console.log("유저 생성 성공:", userId); - } else { - // 기존 유저의 techCompanyId 업데이트 - if (!existingUser.techCompanyId) { - await tx.update(users) - .set({ techCompanyId: vendorResult.id }) - .where(eq(users.id, existingUser.id)); - console.log("기존 유저의 techCompanyId 업데이트:", existingUser.id); - } - userId = existingUser.id; - } - - // 6. 후보에서 해당 이메일이 있으면 vendorId 업데이트 및 상태 변경 - if (params.vendorData.email) { - await tx.update(techVendorCandidates) - .set({ - vendorId: vendorResult.id, - status: "INVITED" - }) - .where(eq(techVendorCandidates.contactEmail, params.vendorData.email)); - } - - return { vendor: vendorResult, userId }; - }); - - // 캐시 무효화 - revalidateTag("tech-vendors"); - revalidateTag("tech-vendor-candidates"); - revalidateTag("users"); - - console.log("기술영업 벤더 회원가입 완료:", result); - return { success: true, data: result }; - } catch (error) { - console.error("기술영업 벤더 회원가입 실패:", error); - return { success: false, error: getErrorMessage(error) }; - } -} - -/** - * 단일 기술영업 벤더 추가 (사용자 계정도 함께 생성) - */ -export async function addTechVendor(input: { - vendorName: string; - vendorCode?: string | null; - email: string; - taxId: string; - country?: string | null; - countryEng?: string | null; - countryFab?: string | null; - agentName?: string | null; - agentPhone?: string | null; - agentEmail?: string | null; - address?: string | null; - phone?: string | null; - website?: string | null; - techVendorType: string; - representativeName?: string | null; - representativeEmail?: string | null; - representativePhone?: string | null; - representativeBirth?: string | null; - isQuoteComparison?: boolean; -}) { - unstable_noStore(); - - try { - console.log("벤더 추가 시작:", input.vendorName); - - const result = await db.transaction(async (tx) => { - // 1. 이메일 중복 체크 - const existingVendor = await tx.query.techVendors.findFirst({ - where: eq(techVendors.email, input.email), - columns: { id: true, vendorName: true } - }); - - if (existingVendor) { - throw new Error(`이미 등록된 이메일입니다: ${input.email} (업체명: ${existingVendor.vendorName})`); - } - - // 2. 벤더 생성 - console.log("벤더 생성 시도:", { - vendorName: input.vendorName, - email: input.email, - techVendorType: input.techVendorType - }); - - const [newVendor] = await tx.insert(techVendors).values({ - vendorName: input.vendorName, - vendorCode: input.vendorCode || null, - taxId: input.taxId || null, - country: input.country || null, - countryEng: input.countryEng || null, - countryFab: input.countryFab || null, - agentName: input.agentName || null, - agentPhone: input.agentPhone || null, - agentEmail: input.agentEmail || null, - address: input.address || null, - phone: input.phone || null, - email: input.email, - website: input.website || null, - techVendorType: Array.isArray(input.techVendorType) ? input.techVendorType.join(',') : input.techVendorType, - status: input.isQuoteComparison ? "PENDING_INVITE" : "ACTIVE", - isQuoteComparison: input.isQuoteComparison || false, - representativeName: input.representativeName || null, - representativeEmail: input.representativeEmail || null, - representativePhone: input.representativePhone || null, - representativeBirth: input.representativeBirth || null, - }).returning(); - - console.log("벤더 생성 성공:", newVendor.id); - - // 3. 견적비교용 벤더인 경우 PENDING_REVIEW 상태로 생성됨 - // 초대는 별도의 초대 버튼을 통해 진행 - console.log("벤더 생성 완료:", newVendor.id, "상태:", newVendor.status); - - // 4. 유저 생성 (techCompanyId 설정) - console.log("유저 생성 시도:", input.email); - - // 이미 존재하는 유저인지 확인 - const existingUser = await tx.query.users.findFirst({ - where: eq(users.email, input.email), - columns: { id: true, techCompanyId: true } - }); - - let userId = null; - // 유저가 존재하지 않는 경우에만 생성 - if (!existingUser) { - const [newUser] = await tx.insert(users).values({ - name: input.vendorName, - email: input.email, - techCompanyId: newVendor.id, // techCompanyId 설정 - domain: "partners", - }).returning(); - userId = newUser.id; - console.log("유저 생성 성공:", userId); - } else { - // 이미 존재하는 유저의 techCompanyId가 null인 경우 업데이트 - if (!existingUser.techCompanyId) { - await tx.update(users) - .set({ techCompanyId: newVendor.id }) - .where(eq(users.id, existingUser.id)); - console.log("기존 유저의 techCompanyId 업데이트:", existingUser.id); - } - userId = existingUser.id; - console.log("이미 존재하는 유저:", userId); - } - - return { vendor: newVendor, userId }; - }); - - // 캐시 무효화 - revalidateTag("tech-vendors"); - revalidateTag("users"); - - console.log("벤더 추가 완료:", result); - return { success: true, data: result }; - } catch (error) { - console.error("벤더 추가 실패:", error); - return { success: false, error: getErrorMessage(error) }; - } -} - -/** - * 벤더의 possible items 개수 조회 - */ -export async function getTechVendorPossibleItemsCount(vendorId: number): Promise { - try { - const result = await db - .select({ count: sql`count(*)`.as("count") }) - .from(techVendorPossibleItems) - .where(eq(techVendorPossibleItems.vendorId, vendorId)); - - return result[0]?.count || 0; - } catch (err) { - console.error("Error getting tech vendor possible items count:", err); - return 0; - } -} - -/** - * 기술영업 벤더 초대 메일 발송 - */ -export async function inviteTechVendor(params: { - vendorId: number; - subject: string; - message: string; - recipientEmail: string; -}) { - unstable_noStore(); - - try { - console.log("기술영업 벤더 초대 메일 발송 시작:", params.vendorId); - - const result = await db.transaction(async (tx) => { - // 벤더 정보 조회 - const vendor = await tx.query.techVendors.findFirst({ - where: eq(techVendors.id, params.vendorId), - }); - - if (!vendor) { - throw new Error("벤더를 찾을 수 없습니다."); - } - - // 벤더 상태를 INVITED로 변경 (PENDING_INVITE에서) - if (vendor.status !== "PENDING_INVITE") { - throw new Error("초대 가능한 상태가 아닙니다. (PENDING_INVITE 상태만 초대 가능)"); - } - - await tx.update(techVendors) - .set({ - status: "INVITED", - updatedAt: new Date(), - }) - .where(eq(techVendors.id, params.vendorId)); - - // 초대 토큰 생성 - const { createTechVendorInvitationToken, createTechVendorSignupUrl } = await import("@/lib/tech-vendor-invitation-token"); - const { sendEmail } = await import("@/lib/mail/sendEmail"); - - const invitationToken = await createTechVendorInvitationToken({ - vendorId: vendor.id, - vendorName: vendor.vendorName, - email: params.recipientEmail, - }); - - const signupUrl = await createTechVendorSignupUrl(invitationToken); - - // 초대 메일 발송 - await sendEmail({ - to: params.recipientEmail, - subject: params.subject, - template: "tech-vendor-invitation", - context: { - companyName: vendor.vendorName, - language: "ko", - registrationLink: signupUrl, - customMessage: params.message, - } - }); - - console.log("초대 메일 발송 완료:", params.recipientEmail); - - return { vendor, invitationToken, signupUrl }; - }); - - // 캐시 무효화 - revalidateTag("tech-vendors"); - - console.log("기술영업 벤더 초대 완료:", result); - return { success: true, data: result }; - } catch (error) { - console.error("기술영업 벤더 초대 실패:", error); - return { success: false, error: getErrorMessage(error) }; - } -} - +"use server"; // Next.js 서버 액션에서 직접 import하려면 (선택) + +import { revalidateTag, unstable_noStore } from "next/cache"; +import db from "@/db/db"; +import { techVendorAttachments, techVendorContacts, techVendorPossibleItems, techVendors, techVendorItemsView, type TechVendor, techVendorCandidates } from "@/db/schema/techVendors"; +import { items, itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items"; +import { users } from "@/db/schema/users"; +import ExcelJS from "exceljs"; +import { filterColumns } from "@/lib/filter-columns"; +import { unstable_cache } from "@/lib/unstable-cache"; +import { getErrorMessage } from "@/lib/handle-error"; + +import { + insertTechVendor, + updateTechVendor, + groupByTechVendorStatus, + selectTechVendorContacts, + countTechVendorContacts, + insertTechVendorContact, + selectTechVendorItems, + countTechVendorItems, + insertTechVendorItem, + selectTechVendorsWithAttachments, + countTechVendorsWithAttachments, + updateTechVendors, +} from "./repository"; + +import type { + CreateTechVendorSchema, + UpdateTechVendorSchema, + GetTechVendorsSchema, + GetTechVendorContactsSchema, + CreateTechVendorContactSchema, + GetTechVendorItemsSchema, + CreateTechVendorItemSchema, + GetTechVendorRfqHistorySchema, + GetTechVendorPossibleItemsSchema, + CreateTechVendorPossibleItemSchema, + UpdateTechVendorPossibleItemSchema, + UpdateTechVendorContactSchema, +} from "./validations"; + +import { asc, desc, ilike, inArray, and, or, eq, isNull, not } from "drizzle-orm"; +import path from "path"; +import { sql } from "drizzle-orm"; +import { decryptWithServerAction } from "@/components/drm/drmUtils"; +import { deleteFile, saveDRMFile } from "../file-stroage"; + +/* ----------------------------------------------------- + 1) 조회 관련 +----------------------------------------------------- */ + +/** + * 복잡한 조건으로 기술영업 Vendor 목록을 조회 (+ pagination) 하고, + * 총 개수에 따라 pageCount를 계산해서 리턴. + * Next.js의 unstable_cache를 사용해 일정 시간 캐시. + */ +export async function getTechVendors(input: GetTechVendorsSchema) { + return unstable_cache( + async () => { + try { + const offset = (input.page - 1) * input.perPage; + + // 1) 고급 필터 (workTypes와 techVendorType 제외 - 별도 처리) + const filteredFilters = input.filters.filter( + filter => filter.id !== "workTypes" && filter.id !== "techVendorType" + ); + + const advancedWhere = filterColumns({ + table: techVendors, + filters: filteredFilters, + joinOperator: input.joinOperator, + }); + + // 2) 글로벌 검색 + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + ilike(techVendors.vendorName, s), + ilike(techVendors.vendorCode, s), + ilike(techVendors.email, s), + ilike(techVendors.status, s) + ); + } + + // 최종 where 결합 + const finalWhere = and(advancedWhere, globalWhere); + + // 벤더 타입 필터링 로직 추가 + let vendorTypeWhere; + if (input.vendorType) { + // URL의 vendorType 파라미터를 실제 벤더 타입으로 매핑 + const vendorTypeMap = { + "ship": "조선", + "top": "해양TOP", + "hull": "해양HULL" + }; + + const actualVendorType = input.vendorType in vendorTypeMap + ? vendorTypeMap[input.vendorType as keyof typeof vendorTypeMap] + : undefined; + if (actualVendorType) { + // techVendorType 필드는 콤마로 구분된 문자열이므로 LIKE 사용 + vendorTypeWhere = ilike(techVendors.techVendorType, `%${actualVendorType}%`); + } + } + + // 간단 검색 (advancedTable=false) 시 예시 + const simpleWhere = and( + input.vendorName + ? ilike(techVendors.vendorName, `%${input.vendorName}%`) + : undefined, + input.status ? ilike(techVendors.status, input.status) : undefined, + input.country + ? ilike(techVendors.country, `%${input.country}%`) + : undefined + ); + + // TechVendorType 필터링 로직 추가 (고급 필터에서) + let techVendorTypeWhere; + const techVendorTypeFilters = input.filters.filter(filter => filter.id === "techVendorType"); + if (techVendorTypeFilters.length > 0) { + const typeFilter = techVendorTypeFilters[0]; + if (Array.isArray(typeFilter.value) && typeFilter.value.length > 0) { + // 각 타입에 대해 LIKE 조건으로 OR 연결 + const typeConditions = typeFilter.value.map(type => + ilike(techVendors.techVendorType, `%${type}%`) + ); + techVendorTypeWhere = or(...typeConditions); + } + } + + // WorkTypes 필터링 로직 추가 + let workTypesWhere; + const workTypesFilters = input.filters.filter(filter => filter.id === "workTypes"); + if (workTypesFilters.length > 0) { + const workTypeFilter = workTypesFilters[0]; + if (Array.isArray(workTypeFilter.value) && workTypeFilter.value.length > 0) { + // workTypes에 해당하는 벤더 ID들을 서브쿼리로 찾음 + const vendorIdsWithWorkTypes = db + .selectDistinct({ vendorId: techVendorPossibleItems.vendorId }) + .from(techVendorPossibleItems) + .leftJoin(itemShipbuilding, eq(techVendorPossibleItems.itemCode, itemShipbuilding.itemCode)) + .leftJoin(itemOffshoreTop, eq(techVendorPossibleItems.itemCode, itemOffshoreTop.itemCode)) + .leftJoin(itemOffshoreHull, eq(techVendorPossibleItems.itemCode, itemOffshoreHull.itemCode)) + .where( + or( + inArray(itemShipbuilding.workType, workTypeFilter.value), + inArray(itemOffshoreTop.workType, workTypeFilter.value), + inArray(itemOffshoreHull.workType, workTypeFilter.value) + ) + ); + + workTypesWhere = inArray(techVendors.id, vendorIdsWithWorkTypes); + } + } + + // 실제 사용될 where (vendorType, techVendorType, workTypes 필터링 추가) + const where = and(finalWhere, vendorTypeWhere, techVendorTypeWhere, workTypesWhere); + + // 정렬 + const orderBy = + input.sort.length > 0 + ? input.sort.map((item) => + item.desc ? desc(techVendors[item.id]) : asc(techVendors[item.id]) + ) + : [asc(techVendors.createdAt)]; + + // 트랜잭션 내에서 데이터 조회 + const { data, total } = await db.transaction(async (tx) => { + // 1) vendor 목록 조회 (with attachments) + const vendorsData = await selectTechVendorsWithAttachments(tx, { + where, + orderBy, + offset, + limit: input.perPage, + }); + + // 2) 전체 개수 + const total = await countTechVendorsWithAttachments(tx, where); + return { data: vendorsData, total }; + }); + + // 페이지 수 + const pageCount = Math.ceil(total / input.perPage); + + return { data, pageCount }; + } catch (err) { + console.error("Error fetching tech vendors:", err); + // 에러 발생 시 + return { data: [], pageCount: 0 }; + } + }, + [JSON.stringify(input)], // 캐싱 키 + { + revalidate: 3600, + tags: ["tech-vendors"], // revalidateTag("tech-vendors") 호출 시 무효화 + } + )(); +} + +/** + * 기술영업 벤더 상태별 카운트 조회 + */ +export async function getTechVendorStatusCounts() { + return unstable_cache( + async () => { + try { + const initial: Record = { + "PENDING_INVITE": 0, + "INVITED": 0, + "QUOTE_COMPARISON": 0, + "ACTIVE": 0, + "INACTIVE": 0, + "BLACKLISTED": 0, + }; + + const result = await db.transaction(async (tx) => { + const rows = await groupByTechVendorStatus(tx); + type StatusCountRow = { status: TechVendor["status"]; count: number }; + return (rows as StatusCountRow[]).reduce>((acc, { status, count }) => { + acc[status] = count; + return acc; + }, initial); + }); + + return result; + } catch (err) { + return {} as Record; + } + }, + ["tech-vendor-status-counts"], // 캐싱 키 + { + revalidate: 3600, + } + )(); +} + +/** + * 벤더 상세 정보 조회 + */ +export async function getTechVendorById(id: number) { + return unstable_cache( + async () => { + try { + const result = await getTechVendorDetailById(id); + return { data: result }; + } catch (err) { + console.error("기술영업 벤더 상세 조회 오류:", err); + return { data: null }; + } + }, + [`tech-vendor-${id}`], + { + revalidate: 3600, + tags: ["tech-vendors", `tech-vendor-${id}`], + } + )(); +} + +/* ----------------------------------------------------- + 2) 생성(Create) +----------------------------------------------------- */ + +/** + * 첨부파일 저장 헬퍼 함수 + */ +async function storeTechVendorFiles( + tx: any, + vendorId: number, + files: File[], + attachmentType: string +) { + + for (const file of files) { + + const saveResult = await saveDRMFile(file, decryptWithServerAction, `tech-vendors/${vendorId}`) + + // Insert attachment record + await tx.insert(techVendorAttachments).values({ + vendorId, + fileName: file.name, + filePath: saveResult.publicPath, + attachmentType, + }); + } +} + +/** + * 신규 기술영업 벤더 생성 + */ +export async function createTechVendor(input: CreateTechVendorSchema) { + unstable_noStore(); + + try { + // 이메일 중복 검사 + const existingVendorByEmail = await db + .select({ id: techVendors.id, vendorName: techVendors.vendorName }) + .from(techVendors) + .where(eq(techVendors.email, input.email)) + .limit(1); + + // 이미 동일한 이메일을 가진 업체가 존재하면 에러 반환 + if (existingVendorByEmail.length > 0) { + return { + success: false, + data: null, + error: `이미 등록된 이메일입니다. (업체명: ${existingVendorByEmail[0].vendorName})` + }; + } + + // taxId 중복 검사 + const existingVendorByTaxId = await db + .select({ id: techVendors.id }) + .from(techVendors) + .where(eq(techVendors.taxId, input.taxId)) + .limit(1); + + // 이미 동일한 taxId를 가진 업체가 존재하면 에러 반환 + if (existingVendorByTaxId.length > 0) { + return { + success: false, + data: null, + error: `이미 등록된 사업자등록번호입니다. (Tax ID ${input.taxId} already exists in the system)` + }; + } + + const result = await db.transaction(async (tx) => { + // 1. 벤더 생성 + const [newVendor] = await insertTechVendor(tx, { + vendorName: input.vendorName, + vendorCode: input.vendorCode || null, + taxId: input.taxId, + address: input.address || null, + country: input.country, + countryEng: null, + countryFab: null, + agentName: null, + agentPhone: null, + agentEmail: null, + phone: input.phone || null, + email: input.email, + website: input.website || null, + techVendorType: Array.isArray(input.techVendorType) ? input.techVendorType.join(',') : input.techVendorType, + representativeName: input.representativeName || null, + representativeBirth: input.representativeBirth || null, + representativeEmail: input.representativeEmail || null, + representativePhone: input.representativePhone || null, + items: input.items || null, + status: "ACTIVE", + isQuoteComparison: false, + }); + + // 2. 연락처 정보 등록 + for (const contact of input.contacts) { + await insertTechVendorContact(tx, { + vendorId: newVendor.id, + contactName: contact.contactName, + contactPosition: contact.contactPosition || null, + contactEmail: contact.contactEmail, + contactPhone: contact.contactPhone || null, + isPrimary: contact.isPrimary ?? false, + contactCountry: contact.contactCountry || null, + }); + } + + // 3. 첨부파일 저장 + if (input.files && input.files.length > 0) { + await storeTechVendorFiles(tx, newVendor.id, input.files, "GENERAL"); + } + + return newVendor; + }); + + revalidateTag("tech-vendors"); + + return { + success: true, + data: result, + error: null + }; + } catch (err) { + console.error("기술영업 벤더 생성 오류:", err); + + return { + success: false, + data: null, + error: getErrorMessage(err) + }; + } +} + +/* ----------------------------------------------------- + 3) 업데이트 (단건/복수) +----------------------------------------------------- */ + +/** 단건 업데이트 */ +export async function modifyTechVendor( + input: UpdateTechVendorSchema & { id: string; } +) { + unstable_noStore(); + try { + const updated = await db.transaction(async (tx) => { + // 벤더 정보 업데이트 + const [res] = await updateTechVendor(tx, input.id, { + vendorName: input.vendorName, + vendorCode: input.vendorCode, + address: input.address, + country: input.country, + countryEng: input.countryEng, + countryFab: input.countryFab, + phone: input.phone, + email: input.email, + website: input.website, + status: input.status, + // 에이전트 정보 추가 + agentName: input.agentName, + agentEmail: input.agentEmail, + agentPhone: input.agentPhone, + // 대표자 정보 추가 + representativeName: input.representativeName, + representativeEmail: input.representativeEmail, + representativePhone: input.representativePhone, + representativeBirth: input.representativeBirth, + // techVendorType 처리 + techVendorType: Array.isArray(input.techVendorType) ? input.techVendorType.join(',') : input.techVendorType, + }); + + return res; + }); + + // 캐시 무효화 + revalidateTag("tech-vendors"); + revalidateTag(`tech-vendor-${input.id}`); + + return { data: updated, error: null }; + } catch (err) { + return { data: null, error: getErrorMessage(err) }; + } +} + +/** 복수 업데이트 */ +export async function modifyTechVendors(input: { + ids: string[]; + status?: TechVendor["status"]; +}) { + unstable_noStore(); + try { + const data = await db.transaction(async (tx) => { + // 여러 협력업체 일괄 업데이트 + const [updated] = await updateTechVendors(tx, input.ids, { + // 예: 상태만 일괄 변경 + status: input.status, + }); + return updated; + }); + + // 캐시 무효화 + revalidateTag("tech-vendors"); + revalidateTag("tech-vendor-status-counts"); + + return { data: null, error: null }; + } catch (err) { + return { data: null, error: getErrorMessage(err) }; + } +} + +/* ----------------------------------------------------- + 4) 연락처 관리 +----------------------------------------------------- */ + +export async function getTechVendorContacts(input: GetTechVendorContactsSchema, id: number) { + return unstable_cache( + async () => { + try { + const offset = (input.page - 1) * input.perPage; + + // 필터링 설정 + const advancedWhere = filterColumns({ + table: techVendorContacts, + filters: input.filters, + joinOperator: input.joinOperator, + }); + + // 검색 조건 + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + ilike(techVendorContacts.contactName, s), + ilike(techVendorContacts.contactPosition, s), + ilike(techVendorContacts.contactEmail, s), + ilike(techVendorContacts.contactPhone, s) + ); + } + + // 해당 벤더 조건 + const vendorWhere = eq(techVendorContacts.vendorId, id); + + // 최종 조건 결합 + const finalWhere = and(advancedWhere, globalWhere, vendorWhere); + + // 정렬 조건 + const orderBy = + input.sort.length > 0 + ? input.sort.map((item) => + item.desc ? desc(techVendorContacts[item.id]) : asc(techVendorContacts[item.id]) + ) + : [asc(techVendorContacts.createdAt)]; + + // 트랜잭션 내부에서 Repository 호출 + const { data, total } = await db.transaction(async (tx) => { + const data = await selectTechVendorContacts(tx, { + where: finalWhere, + orderBy, + offset, + limit: input.perPage, + }); + const total = await countTechVendorContacts(tx, finalWhere); + return { data, total }; + }); + + const pageCount = Math.ceil(total / input.perPage); + + return { data, pageCount }; + } catch (err) { + // 에러 발생 시 디폴트 + return { data: [], pageCount: 0 }; + } + }, + [JSON.stringify(input), String(id)], // 캐싱 키 + { + revalidate: 3600, + tags: [`tech-vendor-contacts-${id}`], + } + )(); +} + +export async function createTechVendorContact(input: CreateTechVendorContactSchema) { + unstable_noStore(); + try { + await db.transaction(async (tx) => { + // DB Insert + const [newContact] = await insertTechVendorContact(tx, { + vendorId: input.vendorId, + contactName: input.contactName, + contactPosition: input.contactPosition || "", + contactEmail: input.contactEmail, + contactPhone: input.contactPhone || "", + contactCountry: input.contactCountry || "", + isPrimary: input.isPrimary || false, + }); + + return newContact; + }); + + // 캐시 무효화 + revalidateTag(`tech-vendor-contacts-${input.vendorId}`); + revalidateTag("users"); + + return { data: null, error: null }; + } catch (err) { + return { data: null, error: getErrorMessage(err) }; + } +} + +export async function updateTechVendorContact(input: UpdateTechVendorContactSchema & { id: number; vendorId: number }) { + unstable_noStore(); + try { + const [updatedContact] = await db + .update(techVendorContacts) + .set({ + contactName: input.contactName, + contactPosition: input.contactPosition || null, + contactEmail: input.contactEmail, + contactPhone: input.contactPhone || null, + contactCountry: input.contactCountry || null, + isPrimary: input.isPrimary || false, + updatedAt: new Date(), + }) + .where(eq(techVendorContacts.id, input.id)) + .returning(); + + // 캐시 무효화 + revalidateTag(`tech-vendor-contacts-${input.vendorId}`); + revalidateTag("users"); + + return { data: updatedContact, error: null }; + } catch (err) { + return { data: null, error: getErrorMessage(err) }; + } +} + +export async function deleteTechVendorContact(contactId: number, vendorId: number) { + unstable_noStore(); + try { + const [deletedContact] = await db + .delete(techVendorContacts) + .where(eq(techVendorContacts.id, contactId)) + .returning(); + + // 캐시 무효화 + revalidateTag(`tech-vendor-contacts-${contactId}`); + revalidateTag(`tech-vendor-contacts-${vendorId}`); + + return { data: deletedContact, error: null }; + } catch (err) { + return { data: null, error: getErrorMessage(err) }; + } +} + +/* ----------------------------------------------------- + 5) 아이템 관리 +----------------------------------------------------- */ + +export async function getTechVendorItems(input: GetTechVendorItemsSchema, id: number) { + return unstable_cache( + async () => { + try { + const offset = (input.page - 1) * input.perPage; + + // 필터링 설정 + const advancedWhere = filterColumns({ + table: techVendorItemsView, + filters: input.filters, + joinOperator: input.joinOperator, + }); + + // 검색 조건 + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + ilike(techVendorItemsView.itemCode, s) + ); + } + + // 해당 벤더 조건 + const vendorWhere = eq(techVendorItemsView.vendorId, id); + + // 최종 조건 결합 + const finalWhere = and(advancedWhere, globalWhere, vendorWhere); + + // 정렬 조건 + const orderBy = + input.sort.length > 0 + ? input.sort.map((item) => + item.desc ? desc(techVendorItemsView[item.id]) : asc(techVendorItemsView[item.id]) + ) + : [asc(techVendorItemsView.createdAt)]; + + // 트랜잭션 내부에서 Repository 호출 + const { data, total } = await db.transaction(async (tx) => { + const data = await selectTechVendorItems(tx, { + where: finalWhere, + orderBy, + offset, + limit: input.perPage, + }); + const total = await countTechVendorItems(tx, finalWhere); + return { data, total }; + }); + + const pageCount = Math.ceil(total / input.perPage); + + return { data, pageCount }; + } catch (err) { + // 에러 발생 시 디폴트 + return { data: [], pageCount: 0 }; + } + }, + [JSON.stringify(input), String(id)], // 캐싱 키 + { + revalidate: 3600, + tags: [`tech-vendor-items-${id}`], + } + )(); +} + +export interface ItemDropdownOption { + itemCode: string; + itemList: string; + workType: string | null; + shipTypes: string | null; + subItemList: string | null; +} + +/** + * Vendor Item 추가 시 사용할 아이템 목록 조회 (전체 목록 반환) + * 아이템 코드, 이름, 설명만 간소화해서 반환 + */ +export async function getItemsForTechVendor(vendorId: number) { + return unstable_cache( + async () => { + try { + // 1. 벤더 정보 조회로 벤더 타입 확인 + const vendor = await db.query.techVendors.findFirst({ + where: eq(techVendors.id, vendorId), + columns: { + techVendorType: true + } + }); + + if (!vendor) { + return { + data: [], + error: "벤더를 찾을 수 없습니다.", + }; + } + + // 2. 해당 벤더가 이미 가지고 있는 itemCode 목록 조회 + const existingItems = await db + .select({ + itemCode: techVendorPossibleItems.itemCode, + }) + .from(techVendorPossibleItems) + .where(eq(techVendorPossibleItems.vendorId, vendorId)); + + const existingItemCodes = existingItems.map(item => item.itemCode); + + // 3. 벤더 타입에 따라 해당 타입의 아이템만 조회 + // let availableItems: ItemDropdownOption[] = []; + let availableItems: (typeof itemShipbuilding.$inferSelect | typeof itemOffshoreTop.$inferSelect | typeof itemOffshoreHull.$inferSelect)[] = []; + switch (vendor.techVendorType) { + case "조선": + const shipbuildingItems = await db + .select({ + id: itemShipbuilding.id, + createdAt: itemShipbuilding.createdAt, + updatedAt: itemShipbuilding.updatedAt, + itemCode: itemShipbuilding.itemCode, + itemList: itemShipbuilding.itemList, + workType: itemShipbuilding.workType, + shipTypes: itemShipbuilding.shipTypes, + }) + .from(itemShipbuilding) + .where( + existingItemCodes.length > 0 + ? not(inArray(itemShipbuilding.itemCode, existingItemCodes)) + : undefined + ) + .orderBy(asc(itemShipbuilding.itemCode)); + + availableItems = shipbuildingItems + .filter(item => item.itemCode != null) + .map(item => ({ + id: item.id, + createdAt: item.createdAt, + updatedAt: item.updatedAt, + itemCode: item.itemCode!, + itemList: item.itemList || "조선 아이템", + workType: item.workType || "조선 관련 아이템", + shipTypes: item.shipTypes || "조선 관련 아이템" + })); + break; + + case "해양TOP": + const offshoreTopItems = await db + .select({ + id: itemOffshoreTop.id, + createdAt: itemOffshoreTop.createdAt, + updatedAt: itemOffshoreTop.updatedAt, + itemCode: itemOffshoreTop.itemCode, + itemList: itemOffshoreTop.itemList, + workType: itemOffshoreTop.workType, + subItemList: itemOffshoreTop.subItemList, + }) + .from(itemOffshoreTop) + .where( + existingItemCodes.length > 0 + ? not(inArray(itemOffshoreTop.itemCode, existingItemCodes)) + : undefined + ) + .orderBy(asc(itemOffshoreTop.itemCode)); + + availableItems = offshoreTopItems + .filter(item => item.itemCode != null) + .map(item => ({ + id: item.id, + createdAt: item.createdAt, + updatedAt: item.updatedAt, + itemCode: item.itemCode!, + itemList: item.itemList || "해양TOP 아이템", + workType: item.workType || "해양TOP 관련 아이템", + subItemList: item.subItemList || "해양TOP 관련 아이템" + })); + break; + + case "해양HULL": + const offshoreHullItems = await db + .select({ + id: itemOffshoreHull.id, + createdAt: itemOffshoreHull.createdAt, + updatedAt: itemOffshoreHull.updatedAt, + itemCode: itemOffshoreHull.itemCode, + itemList: itemOffshoreHull.itemList, + workType: itemOffshoreHull.workType, + subItemList: itemOffshoreHull.subItemList, + }) + .from(itemOffshoreHull) + .where( + existingItemCodes.length > 0 + ? not(inArray(itemOffshoreHull.itemCode, existingItemCodes)) + : undefined + ) + .orderBy(asc(itemOffshoreHull.itemCode)); + + availableItems = offshoreHullItems + .filter(item => item.itemCode != null) + .map(item => ({ + id: item.id, + createdAt: item.createdAt, + updatedAt: item.updatedAt, + itemCode: item.itemCode!, + itemList: item.itemList || "해양HULL 아이템", + workType: item.workType || "해양HULL 관련 아이템", + subItemList: item.subItemList || "해양HULL 관련 아이템" + })); + break; + + default: + return { + data: [], + error: `지원하지 않는 벤더 타입입니다: ${vendor.techVendorType}`, + }; + } + + return { + data: availableItems, + error: null + }; + } catch (err) { + console.error("Failed to fetch items for tech vendor dropdown:", err); + return { + data: [], + error: "아이템 목록을 불러오는데 실패했습니다.", + }; + } + }, + // 캐시 키를 vendorId 별로 달리 해야 한다. + ["items-for-tech-vendor", String(vendorId)], + { + revalidate: 3600, // 1시간 캐싱 + tags: ["items"], // revalidateTag("items") 호출 시 무효화 + } + )(); +} + +/** + * 벤더 타입과 아이템 코드에 따른 아이템 조회 + */ +export async function getItemsByVendorType(vendorType: string, itemCode: string) { + try { + let items: (typeof itemShipbuilding.$inferSelect | typeof itemOffshoreTop.$inferSelect | typeof itemOffshoreHull.$inferSelect)[] = []; + + switch (vendorType) { + case "조선": + const shipbuildingResults = await db + .select({ + id: itemShipbuilding.id, + itemCode: itemShipbuilding.itemCode, + workType: itemShipbuilding.workType, + shipTypes: itemShipbuilding.shipTypes, + itemList: itemShipbuilding.itemList, + createdAt: itemShipbuilding.createdAt, + updatedAt: itemShipbuilding.updatedAt, + }) + .from(itemShipbuilding) + .where(itemCode ? eq(itemShipbuilding.itemCode, itemCode) : undefined); + items = shipbuildingResults; + break; + + case "해양TOP": + const offshoreTopResults = await db + .select({ + id: itemOffshoreTop.id, + itemCode: itemOffshoreTop.itemCode, + workType: itemOffshoreTop.workType, + itemList: itemOffshoreTop.itemList, + subItemList: itemOffshoreTop.subItemList, + createdAt: itemOffshoreTop.createdAt, + updatedAt: itemOffshoreTop.updatedAt, + }) + .from(itemOffshoreTop) + .where(itemCode ? eq(itemOffshoreTop.itemCode, itemCode) : undefined); + items = offshoreTopResults; + break; + + case "해양HULL": + const offshoreHullResults = await db + .select({ + id: itemOffshoreHull.id, + itemCode: itemOffshoreHull.itemCode, + workType: itemOffshoreHull.workType, + itemList: itemOffshoreHull.itemList, + subItemList: itemOffshoreHull.subItemList, + createdAt: itemOffshoreHull.createdAt, + updatedAt: itemOffshoreHull.updatedAt, + }) + .from(itemOffshoreHull) + .where(itemCode ? eq(itemOffshoreHull.itemCode, itemCode) : undefined); + items = offshoreHullResults; + break; + + default: + items = []; + } + + const result = items.map(item => ({ + ...item, + techVendorType: vendorType + })); + + return { data: result, error: null }; + } catch (err) { + console.error("Error fetching items by vendor type:", err); + return { data: [], error: "Failed to fetch items" }; + } +} + +/** + * 벤더의 possible_items를 조회하고 해당 아이템 코드로 각 타입별 테이블을 조회 + * 벤더 타입이 콤마로 구분된 경우 (예: "조선,해양TOP,해양HULL") 모든 타입의 아이템을 조회 + */ +export async function getVendorItemsByType(vendorId: number, vendorType: string) { + try { + // 벤더의 possible_items 조회 + const possibleItems = await db.query.techVendorPossibleItems.findMany({ + where: eq(techVendorPossibleItems.vendorId, vendorId), + columns: { + itemCode: true + } + }) + + const itemCodes = possibleItems.map(item => item.itemCode) + + if (itemCodes.length === 0) { + return { data: [] } + } + + // 벤더 타입을 콤마로 분리 + const vendorTypes = vendorType.split(',').map(type => type.trim()) + const allItems: Array & { techVendorType: "조선" | "해양TOP" | "해양HULL" }> = [] + + // 각 벤더 타입에 따라 해당하는 테이블에서 아이템 조회 + for (const singleType of vendorTypes) { + switch (singleType) { + case "조선": + const shipbuildingItems = await db.query.itemShipbuilding.findMany({ + where: inArray(itemShipbuilding.itemCode, itemCodes) + }) + allItems.push(...shipbuildingItems.map(item => ({ + ...item, + techVendorType: "조선" as const + }))) + break + + case "해양TOP": + const offshoreTopItems = await db.query.itemOffshoreTop.findMany({ + where: inArray(itemOffshoreTop.itemCode, itemCodes) + }) + allItems.push(...offshoreTopItems.map(item => ({ + ...item, + techVendorType: "해양TOP" as const + }))) + break + + case "해양HULL": + const offshoreHullItems = await db.query.itemOffshoreHull.findMany({ + where: inArray(itemOffshoreHull.itemCode, itemCodes) + }) + allItems.push(...offshoreHullItems.map(item => ({ + ...item, + techVendorType: "해양HULL" as const + }))) + break + + default: + console.warn(`Unknown vendor type: ${singleType}`) + break + } + } + + // 중복 허용 - 모든 아이템을 그대로 반환 + return { + data: allItems.sort((a, b) => a.itemCode.localeCompare(b.itemCode)) + } + } catch (err) { + console.error("Error getting vendor items by type:", err) + return { data: [] } + } +} + +export async function createTechVendorItem(input: CreateTechVendorItemSchema) { + unstable_noStore(); + try { + // DB에 이미 존재하는지 확인 + const existingItem = await db + .select({ id: techVendorPossibleItems.id }) + .from(techVendorPossibleItems) + .where( + and( + eq(techVendorPossibleItems.vendorId, input.vendorId), + eq(techVendorPossibleItems.itemCode, input.itemCode) + ) + ) + .limit(1); + + if (existingItem.length > 0) { + return { data: null, error: "이미 추가된 아이템입니다." }; + } + + await db.transaction(async (tx) => { + // DB Insert + const [newItem] = await tx + .insert(techVendorPossibleItems) + .values({ + vendorId: input.vendorId, + itemCode: input.itemCode, + }) + .returning(); + return newItem; + }); + + // 캐시 무효화 + revalidateTag(`tech-vendor-items-${input.vendorId}`); + + return { data: null, error: null }; + } catch (err) { + return { data: null, error: getErrorMessage(err) }; + } +} + +/* ----------------------------------------------------- + 6) 기술영업 벤더 승인/거부 +----------------------------------------------------- */ + +interface ApproveTechVendorsInput { + ids: string[]; +} + +/** + * 기술영업 벤더 승인 (상태를 ACTIVE로 변경) + */ +export async function approveTechVendors(input: ApproveTechVendorsInput) { + unstable_noStore(); + + try { + // 트랜잭션 내에서 협력업체 상태 업데이트 + const result = await db.transaction(async (tx) => { + // 협력업체 상태 업데이트 + const [updated] = await tx + .update(techVendors) + .set({ + status: "ACTIVE", + updatedAt: new Date() + }) + .where(inArray(techVendors.id, input.ids.map(id => parseInt(id)))) + .returning(); + + return updated; + }); + + // 캐시 무효화 + revalidateTag("tech-vendors"); + revalidateTag("tech-vendor-status-counts"); + + return { data: result, error: null }; + } catch (err) { + console.error("Error approving tech vendors:", err); + return { data: null, error: getErrorMessage(err) }; + } +} + +/** + * 기술영업 벤더 거부 (상태를 REJECTED로 변경) + */ +export async function rejectTechVendors(input: ApproveTechVendorsInput) { + unstable_noStore(); + + try { + // 트랜잭션 내에서 협력업체 상태 업데이트 + const result = await db.transaction(async (tx) => { + // 협력업체 상태 업데이트 + const [updated] = await tx + .update(techVendors) + .set({ + status: "INACTIVE", + updatedAt: new Date() + }) + .where(inArray(techVendors.id, input.ids.map(id => parseInt(id)))) + .returning(); + + return updated; + }); + + // 캐시 무효화 + revalidateTag("tech-vendors"); + revalidateTag("tech-vendor-status-counts"); + + return { data: result, error: null }; + } catch (err) { + console.error("Error rejecting tech vendors:", err); + return { data: null, error: getErrorMessage(err) }; + } +} + +/* ----------------------------------------------------- + 7) 엑셀 내보내기 +----------------------------------------------------- */ + +/** + * 벤더 연락처 목록 엑셀 내보내기 + */ +export async function exportTechVendorContacts(vendorId: number) { + try { + const contacts = await db + .select() + .from(techVendorContacts) + .where(eq(techVendorContacts.vendorId, vendorId)) + .orderBy(techVendorContacts.isPrimary, techVendorContacts.contactName); + + return contacts; + } catch (err) { + console.error("기술영업 벤더 연락처 내보내기 오류:", err); + return []; + } +} + +/** + * 벤더 아이템 목록 엑셀 내보내기 + */ +export async function exportTechVendorItems(vendorId: number) { + try { + const items = await db + .select({ + id: techVendorItemsView.vendorItemId, + vendorId: techVendorItemsView.vendorId, + itemCode: techVendorItemsView.itemCode, + createdAt: techVendorItemsView.createdAt, + updatedAt: techVendorItemsView.updatedAt, + }) + .from(techVendorItemsView) + .where(eq(techVendorItemsView.vendorId, vendorId)) + + return items; + } catch (err) { + console.error("기술영업 벤더 아이템 내보내기 오류:", err); + return []; + } +} + +/** + * 벤더 정보 엑셀 내보내기 + */ +export async function exportTechVendorDetails(vendorIds: number[]) { + try { + if (!vendorIds.length) return []; + + // 벤더 기본 정보 조회 + const vendorsData = await db + .select({ + id: techVendors.id, + vendorName: techVendors.vendorName, + vendorCode: techVendors.vendorCode, + taxId: techVendors.taxId, + address: techVendors.address, + country: techVendors.country, + phone: techVendors.phone, + email: techVendors.email, + website: techVendors.website, + status: techVendors.status, + representativeName: techVendors.representativeName, + representativeEmail: techVendors.representativeEmail, + representativePhone: techVendors.representativePhone, + representativeBirth: techVendors.representativeBirth, + items: techVendors.items, + createdAt: techVendors.createdAt, + updatedAt: techVendors.updatedAt, + }) + .from(techVendors) + .where( + vendorIds.length === 1 + ? eq(techVendors.id, vendorIds[0]) + : inArray(techVendors.id, vendorIds) + ); + + // 벤더별 상세 정보를 포함하여 반환 + const vendorsWithDetails = await Promise.all( + vendorsData.map(async (vendor) => { + // 연락처 조회 + const contacts = await exportTechVendorContacts(vendor.id); + + // 아이템 조회 + const items = await exportTechVendorItems(vendor.id); + + return { + ...vendor, + vendorContacts: contacts, + vendorItems: items, + }; + }) + ); + + return vendorsWithDetails; + } catch (err) { + console.error("기술영업 벤더 상세 내보내기 오류:", err); + return []; + } +} + +/** + * 기술영업 벤더 상세 정보 조회 (연락처, 첨부파일 포함) + */ +export async function getTechVendorDetailById(id: number) { + try { + const vendor = await db.select().from(techVendors).where(eq(techVendors.id, id)).limit(1); + + if (!vendor || vendor.length === 0) { + console.error(`Vendor not found with id: ${id}`); + return null; + } + + const contacts = await db.select().from(techVendorContacts).where(eq(techVendorContacts.vendorId, id)); + const attachments = await db.select().from(techVendorAttachments).where(eq(techVendorAttachments.vendorId, id)); + const possibleItems = await db.select().from(techVendorPossibleItems).where(eq(techVendorPossibleItems.vendorId, id)); + + return { + ...vendor[0], + contacts, + attachments, + possibleItems + }; + } catch (error) { + console.error("Error fetching tech vendor detail:", error); + return null; + } +} + +/** + * 기술영업 벤더 첨부파일 다운로드를 위한 서버 액션 + * @param vendorId 기술영업 벤더 ID + * @param fileId 특정 파일 ID (단일 파일 다운로드시) + * @returns 다운로드할 수 있는 임시 URL + */ +export async function downloadTechVendorAttachments(vendorId:number, fileId?:number) { + try { + // API 경로 생성 (단일 파일 또는 모든 파일) + const url = fileId + ? `/api/tech-vendors/attachments/download?id=${fileId}&vendorId=${vendorId}` + : `/api/tech-vendors/attachments/download-all?vendorId=${vendorId}`; + + // fetch 요청 (기본적으로 Blob으로 응답 받기) + const response = await fetch(url, { + method: 'GET', + headers: { + 'Content-Type': 'application/json', + }, + }); + + if (!response.ok) { + throw new Error(`Server responded with ${response.status}: ${response.statusText}`); + } + + // 파일명 가져오기 (Content-Disposition 헤더에서) + const contentDisposition = response.headers.get('content-disposition'); + let fileName = fileId ? `file-${fileId}.zip` : `tech-vendor-${vendorId}-files.zip`; + + if (contentDisposition) { + const matches = /filename[^;=\n]*=((['"]).*?\2|[^;\n]*)/.exec(contentDisposition); + if (matches && matches[1]) { + fileName = matches[1].replace(/['"]/g, ''); + } + } + + // Blob으로 응답 변환 + const blob = await response.blob(); + + // Blob URL 생성 + const blobUrl = window.URL.createObjectURL(blob); + + return { + url: blobUrl, + fileName, + blob + }; + } catch (error) { + console.error('Download API error:', error); + throw error; + } +} + +/** + * 임시 ZIP 파일 정리를 위한 서버 액션 + * @param fileName 정리할 파일명 + */ +export async function cleanupTechTempFiles(fileName: string) { + 'use server'; + + try { + + await deleteFile(`tmp/${fileName}`) + + return { success: true }; + } catch (error) { + console.error('임시 파일 정리 오류:', error); + return { success: false, error: '임시 파일 정리 중 오류가 발생했습니다.' }; + } +} + +export const findVendorById = async (id: number): Promise => { + try { + // 직접 DB에서 조회 + const vendor = await db + .select() + .from(techVendors) + .where(eq(techVendors.id, id)) + .limit(1) + .then(rows => rows[0] || null); + + if (!vendor) { + console.error(`Vendor not found with id: ${id}`); + return null; + } + + return vendor; + } catch (error) { + console.error('Error fetching vendor:', error); + return null; + } +}; + +/* ----------------------------------------------------- + 8) 기술영업 벤더 RFQ 히스토리 조회 +----------------------------------------------------- */ + +/** + * 기술영업 벤더의 RFQ 히스토리 조회 (간단한 버전) + */ +export async function getTechVendorRfqHistory(input: GetTechVendorRfqHistorySchema, id:number) { + try { + + // 먼저 해당 벤더의 견적서가 있는지 확인 + const { techSalesVendorQuotations } = await import("@/db/schema/techSales"); + + const quotationCheck = await db + .select({ count: sql`count(*)`.as("count") }) + .from(techSalesVendorQuotations) + .where(eq(techSalesVendorQuotations.vendorId, id)); + + console.log(`벤더 ${id}의 견적서 개수:`, quotationCheck[0]?.count); + + if (quotationCheck[0]?.count === 0) { + console.log("해당 벤더의 견적서가 없습니다."); + return { data: [], pageCount: 0 }; + } + + const offset = (input.page - 1) * input.perPage; + const { techSalesRfqs } = await import("@/db/schema/techSales"); + const { biddingProjects } = await import("@/db/schema/projects"); + + // 간단한 조회 + let whereCondition = eq(techSalesVendorQuotations.vendorId, id); + + // 검색이 있다면 추가 + if (input.search) { + const s = `%${input.search}%`; + const searchCondition = and( + whereCondition, + or( + ilike(techSalesRfqs.rfqCode, s), + ilike(techSalesRfqs.description, s), + ilike(biddingProjects.pspid, s), + ilike(biddingProjects.projNm, s) + ) + ); + whereCondition = searchCondition || whereCondition; + } + + // 데이터 조회 - 테이블에 필요한 필드들 (프로젝트 타입 추가) + const data = await db + .select({ + id: techSalesRfqs.id, + rfqCode: techSalesRfqs.rfqCode, + description: techSalesRfqs.description, + projectCode: biddingProjects.pspid, + projectName: biddingProjects.projNm, + projectType: biddingProjects.pjtType, // 프로젝트 타입 추가 + status: techSalesRfqs.status, + totalAmount: techSalesVendorQuotations.totalPrice, + currency: techSalesVendorQuotations.currency, + dueDate: techSalesRfqs.dueDate, + createdAt: techSalesRfqs.createdAt, + quotationCode: techSalesVendorQuotations.quotationCode, + submittedAt: techSalesVendorQuotations.submittedAt, + }) + .from(techSalesVendorQuotations) + .innerJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id)) + .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) + .where(whereCondition) + .orderBy(desc(techSalesRfqs.createdAt)) + .limit(input.perPage) + .offset(offset); + + console.log("조회된 데이터:", data.length, "개"); + + // 전체 개수 조회 + const totalResult = await db + .select({ count: sql`count(*)`.as("count") }) + .from(techSalesVendorQuotations) + .innerJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id)) + .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id)) + .where(whereCondition); + + const total = totalResult[0]?.count || 0; + const pageCount = Math.ceil(total / input.perPage); + + console.log("기술영업 벤더 RFQ 히스토리 조회 완료", { + id, + dataLength: data.length, + total, + pageCount + }); + + return { data, pageCount }; + } catch (err) { + console.error("기술영업 벤더 RFQ 히스토리 조회 오류:", { + err, + id, + stack: err instanceof Error ? err.stack : undefined + }); + return { data: [], pageCount: 0 }; + } +} + +/** + * 기술영업 벤더 엑셀 import 시 유저 생성 및 담당자 등록 + */ +export async function importTechVendorsFromExcel( + vendors: Array<{ + vendorName: string; + vendorCode?: string | null; + email: string; + taxId: string; + country?: string | null; + countryEng?: string | null; + countryFab?: string | null; + agentName?: string | null; + agentPhone?: string | null; + agentEmail?: string | null; + address?: string | null; + phone?: string | null; + website?: string | null; + techVendorType: string; + representativeName?: string | null; + representativeEmail?: string | null; + representativePhone?: string | null; + representativeBirth?: string | null; + items: string; + contacts?: Array<{ + contactName: string; + contactPosition?: string; + contactEmail: string; + contactPhone?: string; + contactCountry?: string | null; + isPrimary?: boolean; + }>; + }>, +) { + unstable_noStore(); + + try { + console.log("Import 시작 - 벤더 수:", vendors.length); + console.log("첫 번째 벤더 데이터:", vendors[0]); + + const result = await db.transaction(async (tx) => { + const createdVendors = []; + const skippedVendors = []; + const errors = []; + + for (const vendor of vendors) { + console.log("벤더 처리 시작:", vendor.vendorName); + + try { + // 0. 이메일 타입 검사 + // - 문자열이 아니거나, '@' 미포함, 혹은 객체(예: 하이퍼링크 등)인 경우 모두 거절 + const isEmailString = typeof vendor.email === "string"; + const isEmailContainsAt = isEmailString && vendor.email.includes("@"); + // 하이퍼링크 등 객체로 넘어온 경우 (예: { href: "...", ... } 등) 방지 + const isEmailPlainString = isEmailString && Object.prototype.toString.call(vendor.email) === "[object String]"; + + if (!isEmailPlainString || !isEmailContainsAt) { + console.log("이메일 형식이 올바르지 않습니다:", vendor.email); + errors.push({ + vendorName: vendor.vendorName, + email: vendor.email, + error: "이메일 형식이 올바르지 않습니다" + }); + continue; + } + // 1. 이메일로 기존 벤더 중복 체크 + const existingVendor = await tx.query.techVendors.findFirst({ + where: eq(techVendors.email, vendor.email), + columns: { id: true, vendorName: true, email: true } + }); + + if (existingVendor) { + console.log("이미 존재하는 벤더 스킵:", vendor.vendorName, vendor.email); + skippedVendors.push({ + vendorName: vendor.vendorName, + email: vendor.email, + reason: `이미 등록된 이메일입니다 (기존 업체: ${existingVendor.vendorName})` + }); + continue; + } + + // 2. 벤더 생성 + console.log("벤더 생성 시도:", { + vendorName: vendor.vendorName, + email: vendor.email, + techVendorType: vendor.techVendorType + }); + + const [newVendor] = await tx.insert(techVendors).values({ + vendorName: vendor.vendorName, + vendorCode: vendor.vendorCode || null, + taxId: vendor.taxId, + country: vendor.country || null, + countryEng: vendor.countryEng || null, + countryFab: vendor.countryFab || null, + agentName: vendor.agentName || null, + agentPhone: vendor.agentPhone || null, + agentEmail: vendor.agentEmail || null, + address: vendor.address || null, + phone: vendor.phone || null, + email: vendor.email, + website: vendor.website || null, + techVendorType: vendor.techVendorType, + status: "ACTIVE", + representativeName: vendor.representativeName || null, + representativeEmail: vendor.representativeEmail || null, + representativePhone: vendor.representativePhone || null, + representativeBirth: vendor.representativeBirth || null, + }).returning(); + + console.log("벤더 생성 성공:", newVendor.id); + + // 2. 담당자 생성 (최소 1명 이상 등록) + if (vendor.contacts && vendor.contacts.length > 0) { + console.log("담당자 생성 시도:", vendor.contacts.length, "명"); + + for (const contact of vendor.contacts) { + await tx.insert(techVendorContacts).values({ + vendorId: newVendor.id, + contactName: contact.contactName, + contactPosition: contact.contactPosition || null, + contactEmail: contact.contactEmail, + contactPhone: contact.contactPhone || null, + contactCountry: contact.contactCountry || null, + isPrimary: contact.isPrimary || false, + }); + console.log("담당자 생성 성공:", contact.contactName, contact.contactEmail); + } + + // // 벤더 이메일을 주 담당자의 이메일로 업데이트 + // const primaryContact = vendor.contacts.find(c => c.isPrimary) || vendor.contacts[0]; + // if (primaryContact && primaryContact.contactEmail !== vendor.email) { + // await tx.update(techVendors) + // .set({ email: primaryContact.contactEmail }) + // .where(eq(techVendors.id, newVendor.id)); + // console.log("벤더 이메일 업데이트:", primaryContact.contactEmail); + // } + } + // else { + // // 담당자 정보가 없는 경우 벤더 정보로 기본 담당자 생성 + // console.log("기본 담당자 생성"); + // await tx.insert(techVendorContacts).values({ + // vendorId: newVendor.id, + // contactName: vendor.representativeName || vendor.vendorName || "기본 담당자", + // contactPosition: null, + // contactEmail: vendor.email, + // contactPhone: vendor.representativePhone || vendor.phone || null, + // contactCountry: vendor.country || null, + // isPrimary: true, + // }); + // console.log("기본 담당자 생성 성공:", vendor.email); + // } + + // 3. 유저 생성 (이메일이 있는 경우) + if (vendor.email) { + console.log("유저 생성 시도:", vendor.email); + + // 이미 존재하는 유저인지 확인 + const existingUser = await tx.query.users.findFirst({ + where: eq(users.email, vendor.email), + columns: { id: true } + }); + + if (!existingUser) { + // 유저가 존재하지 않는 경우 생성 + await tx.insert(users).values({ + name: vendor.vendorName, + email: vendor.email, + techCompanyId: newVendor.id, + domain: "partners", + }); + console.log("유저 생성 성공"); + } else { + // 이미 존재하는 유저라면 techCompanyId 업데이트 + await tx.update(users) + .set({ techCompanyId: newVendor.id }) + .where(eq(users.id, existingUser.id)); + console.log("이미 존재하는 유저, techCompanyId 업데이트:", existingUser.id); + } + } + + createdVendors.push(newVendor); + console.log("벤더 처리 완료:", vendor.vendorName); + } catch (error) { + console.error("벤더 처리 중 오류 발생:", vendor.vendorName, error); + errors.push({ + vendorName: vendor.vendorName, + email: vendor.email, + error: error instanceof Error ? error.message : "알 수 없는 오류" + }); + // 개별 벤더 오류는 전체 트랜잭션을 롤백하지 않도록 continue + continue; + } + } + + console.log("모든 벤더 처리 완료:", { + 생성됨: createdVendors.length, + 스킵됨: skippedVendors.length, + 오류: errors.length + }); + + return { + createdVendors, + skippedVendors, + errors, + totalProcessed: vendors.length, + successCount: createdVendors.length, + skipCount: skippedVendors.length, + errorCount: errors.length + }; + }); + + // 캐시 무효화 + revalidateTag("tech-vendors"); + revalidateTag("tech-vendor-contacts"); + revalidateTag("users"); + + console.log("Import 완료 - 결과:", result); + + // 결과 메시지 생성 + const messages = []; + if (result.successCount > 0) { + messages.push(`${result.successCount}개 벤더 생성 성공`); + } + if (result.skipCount > 0) { + messages.push(`${result.skipCount}개 벤더 중복으로 스킵`); + } + if (result.errorCount > 0) { + messages.push(`${result.errorCount}개 벤더 처리 중 오류`); + } + + return { + success: true, + data: result, + message: messages.join(", "), + details: { + created: result.createdVendors, + skipped: result.skippedVendors, + errors: result.errors + } + }; + } catch (error) { + console.error("Import 실패:", error); + return { success: false, error: getErrorMessage(error) }; + } +} + +export async function findTechVendorById(id: number): Promise { + const result = await db + .select() + .from(techVendors) + .where(eq(techVendors.id, id)) + .limit(1) + + return result[0] || null +} + +/** + * 회원가입 폼을 통한 기술영업 벤더 생성 (초대 토큰 기반) + */ +export async function createTechVendorFromSignup(params: { + vendorData: { + vendorName: string + vendorCode?: string + items: string + website?: string + taxId: string + address?: string + email: string + phone?: string + country: string + techVendorType: "조선" | "해양TOP" | "해양HULL" | ("조선" | "해양TOP" | "해양HULL")[] + representativeName?: string + representativeBirth?: string + representativeEmail?: string + representativePhone?: string + } + files?: File[] + contacts: { + contactName: string + contactPosition?: string + contactEmail: string + contactPhone?: string + isPrimary?: boolean + }[] + selectedItemCodes?: string[] // 선택된 아이템 코드들 + invitationToken?: string // 초대 토큰 +}) { + unstable_noStore(); + + try { + console.log("기술영업 벤더 회원가입 시작:", params.vendorData.vendorName); + + // 초대 토큰 검증 + let existingVendorId: number | null = null; + if (params.invitationToken) { + const { verifyTechVendorInvitationToken } = await import("@/lib/tech-vendor-invitation-token"); + const tokenPayload = await verifyTechVendorInvitationToken(params.invitationToken); + + if (!tokenPayload) { + throw new Error("유효하지 않은 초대 토큰입니다."); + } + + existingVendorId = tokenPayload.vendorId; + console.log("초대 토큰 검증 성공, 벤더 ID:", existingVendorId); + } + + const result = await db.transaction(async (tx) => { + let vendorResult; + + if (existingVendorId) { + // 기존 벤더 정보 업데이트 + const [updatedVendor] = await tx.update(techVendors) + .set({ + vendorName: params.vendorData.vendorName, + vendorCode: params.vendorData.vendorCode || null, + taxId: params.vendorData.taxId, + country: params.vendorData.country, + address: params.vendorData.address || null, + phone: params.vendorData.phone || null, + email: params.vendorData.email, + website: params.vendorData.website || null, + techVendorType: Array.isArray(params.vendorData.techVendorType) + ? params.vendorData.techVendorType[0] + : params.vendorData.techVendorType, + status: "QUOTE_COMPARISON", // 가입 완료 시 QUOTE_COMPARISON으로 변경 + representativeName: params.vendorData.representativeName || null, + representativeEmail: params.vendorData.representativeEmail || null, + representativePhone: params.vendorData.representativePhone || null, + representativeBirth: params.vendorData.representativeBirth || null, + items: params.vendorData.items, + updatedAt: new Date(), + }) + .where(eq(techVendors.id, existingVendorId)) + .returning(); + + vendorResult = updatedVendor; + console.log("기존 벤더 정보 업데이트 완료:", vendorResult.id); + } else { + // 1. 이메일 중복 체크 (새 벤더인 경우) + const existingVendor = await tx.query.techVendors.findFirst({ + where: eq(techVendors.email, params.vendorData.email), + columns: { id: true, vendorName: true } + }); + + if (existingVendor) { + throw new Error(`이미 등록된 이메일입니다: ${params.vendorData.email} (기존 업체: ${existingVendor.vendorName})`); + } + + // 2. 새 벤더 생성 + const [newVendor] = await tx.insert(techVendors).values({ + vendorName: params.vendorData.vendorName, + vendorCode: params.vendorData.vendorCode || null, + taxId: params.vendorData.taxId, + country: params.vendorData.country, + address: params.vendorData.address || null, + phone: params.vendorData.phone || null, + email: params.vendorData.email, + website: params.vendorData.website || null, + techVendorType: Array.isArray(params.vendorData.techVendorType) + ? params.vendorData.techVendorType[0] + : params.vendorData.techVendorType, + status: "QUOTE_COMPARISON", + isQuoteComparison: false, + representativeName: params.vendorData.representativeName || null, + representativeEmail: params.vendorData.representativeEmail || null, + representativePhone: params.vendorData.representativePhone || null, + representativeBirth: params.vendorData.representativeBirth || null, + items: params.vendorData.items, + }).returning(); + + vendorResult = newVendor; + console.log("새 벤더 생성 완료:", vendorResult.id); + } + + // 이 부분은 위에서 이미 처리되었으므로 주석 처리 + + // 3. 연락처 생성 + if (params.contacts && params.contacts.length > 0) { + for (const [index, contact] of params.contacts.entries()) { + await tx.insert(techVendorContacts).values({ + vendorId: vendorResult.id, + contactName: contact.contactName, + contactPosition: contact.contactPosition || null, + contactEmail: contact.contactEmail, + contactPhone: contact.contactPhone || null, + isPrimary: index === 0, // 첫 번째 연락처를 primary로 설정 + }); + } + console.log("연락처 생성 완료:", params.contacts.length, "개"); + } + + // 4. 선택된 아이템들을 tech_vendor_possible_items에 저장 + if (params.selectedItemCodes && params.selectedItemCodes.length > 0) { + for (const itemCode of params.selectedItemCodes) { + await tx.insert(techVendorPossibleItems).values({ + vendorId: vendorResult.id, + vendorCode: vendorResult.vendorCode, + vendorEmail: vendorResult.email, + itemCode: itemCode, + workType: null, + shipTypes: null, + itemList: null, + subItemList: null, + }); + } + console.log("선택된 아이템 저장 완료:", params.selectedItemCodes.length, "개"); + } + + // 4. 첨부파일 처리 + if (params.files && params.files.length > 0) { + await storeTechVendorFiles(tx, vendorResult.id, params.files, "GENERAL"); + console.log("첨부파일 저장 완료:", params.files.length, "개"); + } + + // 5. 유저 생성 (techCompanyId 설정) + console.log("유저 생성 시도:", params.vendorData.email); + + const existingUser = await tx.query.users.findFirst({ + where: eq(users.email, params.vendorData.email), + columns: { id: true, techCompanyId: true } + }); + + let userId = null; + if (!existingUser) { + const [newUser] = await tx.insert(users).values({ + name: params.vendorData.vendorName, + email: params.vendorData.email, + techCompanyId: vendorResult.id, // 중요: techCompanyId 설정 + domain: "partners", + }).returning(); + userId = newUser.id; + console.log("유저 생성 성공:", userId); + } else { + // 기존 유저의 techCompanyId 업데이트 + if (!existingUser.techCompanyId) { + await tx.update(users) + .set({ techCompanyId: vendorResult.id }) + .where(eq(users.id, existingUser.id)); + console.log("기존 유저의 techCompanyId 업데이트:", existingUser.id); + } + userId = existingUser.id; + } + + // 6. 후보에서 해당 이메일이 있으면 vendorId 업데이트 및 상태 변경 + if (params.vendorData.email) { + await tx.update(techVendorCandidates) + .set({ + vendorId: vendorResult.id, + status: "INVITED" + }) + .where(eq(techVendorCandidates.contactEmail, params.vendorData.email)); + } + + return { vendor: vendorResult, userId }; + }); + + // 캐시 무효화 + revalidateTag("tech-vendors"); + revalidateTag("tech-vendor-candidates"); + revalidateTag("users"); + + console.log("기술영업 벤더 회원가입 완료:", result); + return { success: true, data: result }; + } catch (error) { + console.error("기술영업 벤더 회원가입 실패:", error); + return { success: false, error: getErrorMessage(error) }; + } +} + +/** + * 단일 기술영업 벤더 추가 (사용자 계정도 함께 생성) + */ +export async function addTechVendor(input: { + vendorName: string; + vendorCode?: string | null; + email: string; + taxId: string; + country?: string | null; + countryEng?: string | null; + countryFab?: string | null; + agentName?: string | null; + agentPhone?: string | null; + agentEmail?: string | null; + address?: string | null; + phone?: string | null; + website?: string | null; + techVendorType: string; + representativeName?: string | null; + representativeEmail?: string | null; + representativePhone?: string | null; + representativeBirth?: string | null; + isQuoteComparison?: boolean; +}) { + unstable_noStore(); + + try { + console.log("벤더 추가 시작:", input.vendorName); + + const result = await db.transaction(async (tx) => { + // 1. 이메일 중복 체크 + const existingVendor = await tx.query.techVendors.findFirst({ + where: eq(techVendors.email, input.email), + columns: { id: true, vendorName: true } + }); + + if (existingVendor) { + throw new Error(`이미 등록된 이메일입니다: ${input.email} (업체명: ${existingVendor.vendorName})`); + } + + // 2. 벤더 생성 + console.log("벤더 생성 시도:", { + vendorName: input.vendorName, + email: input.email, + techVendorType: input.techVendorType + }); + + const [newVendor] = await tx.insert(techVendors).values({ + vendorName: input.vendorName, + vendorCode: input.vendorCode || null, + taxId: input.taxId || null, + country: input.country || null, + countryEng: input.countryEng || null, + countryFab: input.countryFab || null, + agentName: input.agentName || null, + agentPhone: input.agentPhone || null, + agentEmail: input.agentEmail || null, + address: input.address || null, + phone: input.phone || null, + email: input.email, + website: input.website || null, + techVendorType: Array.isArray(input.techVendorType) ? input.techVendorType.join(',') : input.techVendorType, + status: input.isQuoteComparison ? "PENDING_INVITE" : "ACTIVE", + isQuoteComparison: input.isQuoteComparison || false, + representativeName: input.representativeName || null, + representativeEmail: input.representativeEmail || null, + representativePhone: input.representativePhone || null, + representativeBirth: input.representativeBirth || null, + }).returning(); + + console.log("벤더 생성 성공:", newVendor.id); + + // 3. 견적비교용 벤더인 경우 PENDING_REVIEW 상태로 생성됨 + // 초대는 별도의 초대 버튼을 통해 진행 + console.log("벤더 생성 완료:", newVendor.id, "상태:", newVendor.status); + + // 4. 견적비교용 벤더(isQuoteComparison)가 아닌 경우에만 유저 생성 + let userId = null; + if (!input.isQuoteComparison) { + console.log("유저 생성 시도:", input.email); + + // 이미 존재하는 유저인지 확인 + const existingUser = await tx.query.users.findFirst({ + where: eq(users.email, input.email), + columns: { id: true, techCompanyId: true } + }); + + // 유저가 존재하지 않는 경우에만 생성 + if (!existingUser) { + const [newUser] = await tx.insert(users).values({ + name: input.vendorName, + email: input.email, + techCompanyId: newVendor.id, // techCompanyId 설정 + domain: "partners", + }).returning(); + userId = newUser.id; + console.log("유저 생성 성공:", userId); + } else { + // 이미 존재하는 유저의 techCompanyId가 null인 경우 업데이트 + if (!existingUser.techCompanyId) { + await tx.update(users) + .set({ techCompanyId: newVendor.id }) + .where(eq(users.id, existingUser.id)); + console.log("기존 유저의 techCompanyId 업데이트:", existingUser.id); + } + userId = existingUser.id; + console.log("이미 존재하는 유저:", userId); + } + } else { + console.log("견적비교용 벤더이므로 유저를 생성하지 않습니다."); + } + + return { vendor: newVendor, userId }; + }); + + // 캐시 무효화 + revalidateTag("tech-vendors"); + revalidateTag("users"); + + console.log("벤더 추가 완료:", result); + return { success: true, data: result }; + } catch (error) { + console.error("벤더 추가 실패:", error); + return { success: false, error: getErrorMessage(error) }; + } +} + +/** + * 벤더의 possible items 개수 조회 + */ +export async function getTechVendorPossibleItemsCount(vendorId: number): Promise { + try { + const result = await db + .select({ count: sql`count(*)`.as("count") }) + .from(techVendorPossibleItems) + .where(eq(techVendorPossibleItems.vendorId, vendorId)); + + return result[0]?.count || 0; + } catch (err) { + console.error("Error getting tech vendor possible items count:", err); + return 0; + } +} + +/** + * 기술영업 벤더 초대 메일 발송 + */ +export async function inviteTechVendor(params: { + vendorId: number; + subject: string; + message: string; + recipientEmail: string; +}) { + unstable_noStore(); + + try { + console.log("기술영업 벤더 초대 메일 발송 시작:", params.vendorId); + + const result = await db.transaction(async (tx) => { + // 벤더 정보 조회 + const vendor = await tx.query.techVendors.findFirst({ + where: eq(techVendors.id, params.vendorId), + }); + + if (!vendor) { + throw new Error("벤더를 찾을 수 없습니다."); + } + + // 벤더 상태를 INVITED로 변경 (PENDING_INVITE에서) + if (vendor.status !== "PENDING_INVITE") { + throw new Error("초대 가능한 상태가 아닙니다. (PENDING_INVITE 상태만 초대 가능)"); + } + + await tx.update(techVendors) + .set({ + status: "INVITED", + updatedAt: new Date(), + }) + .where(eq(techVendors.id, params.vendorId)); + + // 초대 토큰 생성 + const { createTechVendorInvitationToken, createTechVendorSignupUrl } = await import("@/lib/tech-vendor-invitation-token"); + const { sendEmail } = await import("@/lib/mail/sendEmail"); + + const invitationToken = await createTechVendorInvitationToken({ + vendorType: vendor.techVendorType as "조선" | "해양TOP" | "해양HULL" | ("조선" | "해양TOP" | "해양HULL")[], + vendorId: vendor.id, + vendorName: vendor.vendorName, + email: params.recipientEmail, + }); + + const signupUrl = await createTechVendorSignupUrl(invitationToken); + + // 초대 메일 발송 + await sendEmail({ + to: params.recipientEmail, + subject: params.subject, + template: "tech-vendor-invitation", + context: { + companyName: vendor.vendorName, + language: "ko", + registrationLink: signupUrl, + customMessage: params.message, + } + }); + + console.log("초대 메일 발송 완료:", params.recipientEmail); + + return { vendor, invitationToken, signupUrl }; + }); + + // 캐시 무효화 + revalidateTag("tech-vendors"); + + console.log("기술영업 벤더 초대 완료:", result); + return { success: true, data: result }; + } catch (error) { + console.error("기술영업 벤더 초대 실패:", error); + return { success: false, error: getErrorMessage(error) }; + } +} + +/* ----------------------------------------------------- + Possible Items 관련 함수들 +----------------------------------------------------- */ + +/** + * 특정 벤더의 possible items 조회 (페이지네이션 포함) + */ +export async function getTechVendorPossibleItems(input: GetTechVendorPossibleItemsSchema, vendorId: number) { + return unstable_cache( + async () => { + try { + const offset = (input.page - 1) * input.perPage + + // 고급 필터 처리 + const advancedWhere = filterColumns({ + table: techVendorPossibleItems, + filters: input.filters, + joinOperator: input.joinOperator, + }) + + // 글로벌 검색 + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + ilike(techVendorPossibleItems.itemCode, s), + ilike(techVendorPossibleItems.workType, s), + ilike(techVendorPossibleItems.itemList, s), + ilike(techVendorPossibleItems.shipTypes, s), + ilike(techVendorPossibleItems.subItemList, s) + ); + } + + // 벤더 ID 조건 + const vendorWhere = eq(techVendorPossibleItems.vendorId, vendorId) + + // 개별 필터들 + const individualFilters = [] + if (input.itemCode) { + individualFilters.push(ilike(techVendorPossibleItems.itemCode, `%${input.itemCode}%`)) + } + if (input.workType) { + individualFilters.push(ilike(techVendorPossibleItems.workType, `%${input.workType}%`)) + } + if (input.itemList) { + individualFilters.push(ilike(techVendorPossibleItems.itemList, `%${input.itemList}%`)) + } + if (input.shipTypes) { + individualFilters.push(ilike(techVendorPossibleItems.shipTypes, `%${input.shipTypes}%`)) + } + if (input.subItemList) { + individualFilters.push(ilike(techVendorPossibleItems.subItemList, `%${input.subItemList}%`)) + } + + // 최종 where 조건 + const finalWhere = and( + vendorWhere, + advancedWhere, + globalWhere, + ...(individualFilters.length > 0 ? individualFilters : []) + ) + + // 정렬 + const orderBy = + input.sort.length > 0 + ? input.sort.map((item) => { + // techVendorType은 실제 테이블 컬럼이 아니므로 제외 + if (item.id === 'techVendorType') return desc(techVendorPossibleItems.createdAt) + const column = (techVendorPossibleItems as any)[item.id] + return item.desc ? desc(column) : asc(column) + }) + : [desc(techVendorPossibleItems.createdAt)] + + // 데이터 조회 + const data = await db + .select() + .from(techVendorPossibleItems) + .where(finalWhere) + .orderBy(...orderBy) + .limit(input.perPage) + .offset(offset) + + // 전체 개수 조회 + const totalResult = await db + .select({ count: sql`count(*)`.as("count") }) + .from(techVendorPossibleItems) + .where(finalWhere) + + const total = totalResult[0]?.count || 0 + const pageCount = Math.ceil(total / input.perPage) + + return { data, pageCount } + } catch (err) { + console.error("Error fetching tech vendor possible items:", err) + return { data: [], pageCount: 0 } + } + }, + [JSON.stringify(input), String(vendorId)], + { + revalidate: 3600, + tags: [`tech-vendor-possible-items-${vendorId}`], + } + )() +} + +export async function createTechVendorPossibleItemNew(input: CreateTechVendorPossibleItemSchema) { + unstable_noStore() + + try { + // 중복 체크 + const existing = await db + .select({ id: techVendorPossibleItems.id }) + .from(techVendorPossibleItems) + .where( + and( + eq(techVendorPossibleItems.vendorId, input.vendorId), + eq(techVendorPossibleItems.itemCode, input.itemCode) + ) + ) + .limit(1) + + if (existing.length > 0) { + return { data: null, error: "이미 등록된 아이템입니다." } + } + + const [newItem] = await db + .insert(techVendorPossibleItems) + .values({ + vendorId: input.vendorId, + itemCode: input.itemCode, + workType: input.workType, + shipTypes: input.shipTypes, + itemList: input.itemList, + subItemList: input.subItemList, + }) + .returning() + + revalidateTag(`tech-vendor-possible-items-${input.vendorId}`) + return { data: newItem, error: null } + } catch (err) { + console.error("Error creating tech vendor possible item:", err) + return { data: null, error: getErrorMessage(err) } + } +} + +export async function updateTechVendorPossibleItemNew(input: UpdateTechVendorPossibleItemSchema) { + unstable_noStore() + + try { + const [updatedItem] = await db + .update(techVendorPossibleItems) + .set({ + itemCode: input.itemCode, + workType: input.workType, + shipTypes: input.shipTypes, + itemList: input.itemList, + subItemList: input.subItemList, + updatedAt: new Date(), + }) + .where(eq(techVendorPossibleItems.id, input.id)) + .returning() + + revalidateTag(`tech-vendor-possible-items-${input.vendorId}`) + return { data: updatedItem, error: null } + } catch (err) { + console.error("Error updating tech vendor possible item:", err) + return { data: null, error: getErrorMessage(err) } + } +} + +export async function deleteTechVendorPossibleItemsNew(ids: number[], vendorId: number) { + unstable_noStore() + + try { + await db + .delete(techVendorPossibleItems) + .where(inArray(techVendorPossibleItems.id, ids)) + + revalidateTag(`tech-vendor-possible-items-${vendorId}`) + return { data: null, error: null } + } catch (err) { + return { data: null, error: getErrorMessage(err) } + } +} + +export async function addTechVendorPossibleItem(input: { + vendorId: number; + itemCode?: string; + workType?: string; + shipTypes?: string; + itemList?: string; + subItemList?: string; +}) { + unstable_noStore(); + try { + if (!input.itemCode) { + return { success: false, error: "아이템 코드는 필수입니다." }; + } + + const [newItem] = await db + .insert(techVendorPossibleItems) + .values({ + vendorId: input.vendorId, + itemCode: input.itemCode, + workType: input.workType || null, + shipTypes: input.shipTypes || null, + itemList: input.itemList || null, + subItemList: input.subItemList || null, + createdAt: new Date(), + updatedAt: new Date(), + }) + .returning(); + + revalidateTag(`tech-vendor-possible-items-${input.vendorId}`); + + return { success: true, data: newItem }; + } catch (err) { + return { success: false, error: getErrorMessage(err) }; + } +} + +export async function deleteTechVendorPossibleItem(itemId: number, vendorId: number) { + unstable_noStore(); + try { + const [deletedItem] = await db + .delete(techVendorPossibleItems) + .where(eq(techVendorPossibleItems.id, itemId)) + .returning(); + + revalidateTag(`tech-vendor-possible-items-${vendorId}`); + + return { success: true, data: deletedItem }; + } catch (err) { + return { success: false, error: getErrorMessage(err) }; + } +} + + + +//기술영업 담당자 연락처 관련 함수들 + +export interface ImportContactData { + vendorEmail: string // 벤더 대표이메일 (유니크) + contactName: string + contactPosition?: string + contactEmail: string + contactPhone?: string + contactCountry?: string + isPrimary?: boolean +} + +export interface ImportResult { + success: boolean + totalRows: number + successCount: number + failedRows: Array<{ + row: number + error: string + vendorEmail: string + contactName: string + contactEmail: string + }> +} + +/** + * 벤더 대표이메일로 벤더 찾기 + */ +async function getTechVendorByEmail(email: string) { + const vendor = await db + .select({ + id: techVendors.id, + vendorName: techVendors.vendorName, + email: techVendors.email, + }) + .from(techVendors) + .where(eq(techVendors.email, email)) + .limit(1) + + return vendor[0] || null +} + +/** + * 연락처 이메일 중복 체크 + */ +async function checkContactEmailExists(vendorId: number, contactEmail: string) { + const existing = await db + .select() + .from(techVendorContacts) + .where( + and( + eq(techVendorContacts.vendorId, vendorId), + eq(techVendorContacts.contactEmail, contactEmail) + ) + ) + .limit(1) + + return existing.length > 0 +} + +/** + * 벤더 연락처 일괄 import + */ +export async function importTechVendorContacts( + data: ImportContactData[] +): Promise { + const result: ImportResult = { + success: true, + totalRows: data.length, + successCount: 0, + failedRows: [], + } + + for (let i = 0; i < data.length; i++) { + const row = data[i] + const rowNumber = i + 1 + + try { + // 1. 벤더 이메일로 벤더 찾기 + if (!row.vendorEmail || !row.vendorEmail.trim()) { + result.failedRows.push({ + row: rowNumber, + error: "벤더 대표이메일은 필수입니다.", + vendorEmail: row.vendorEmail, + contactName: row.contactName, + contactEmail: row.contactEmail, + }) + continue + } + + const vendor = await getTechVendorByEmail(row.vendorEmail.trim()) + if (!vendor) { + result.failedRows.push({ + row: rowNumber, + error: `벤더 대표이메일 '${row.vendorEmail}'을(를) 찾을 수 없습니다.`, + vendorEmail: row.vendorEmail, + contactName: row.contactName, + contactEmail: row.contactEmail, + }) + continue + } + + // 2. 연락처 이메일 중복 체크 + const isDuplicate = await checkContactEmailExists(vendor.id, row.contactEmail) + if (isDuplicate) { + result.failedRows.push({ + row: rowNumber, + error: `이미 존재하는 연락처 이메일입니다: ${row.contactEmail}`, + vendorEmail: row.vendorEmail, + contactName: row.contactName, + contactEmail: row.contactEmail, + }) + continue + } + + // 3. 연락처 생성 + await db.insert(techVendorContacts).values({ + vendorId: vendor.id, + contactName: row.contactName, + contactPosition: row.contactPosition || null, + contactEmail: row.contactEmail, + contactPhone: row.contactPhone || null, + contactCountry: row.contactCountry || null, + isPrimary: row.isPrimary || false, + }) + + result.successCount++ + } catch (error) { + result.failedRows.push({ + row: rowNumber, + error: error instanceof Error ? error.message : "알 수 없는 오류", + vendorEmail: row.vendorEmail, + contactName: row.contactName, + contactEmail: row.contactEmail, + }) + } + } + + // 캐시 무효화 + revalidateTag("tech-vendor-contacts") + + return result +} + +/** + * 벤더 연락처 import 템플릿 생성 + */ +export async function generateContactImportTemplate(): Promise { + const workbook = new ExcelJS.Workbook() + const worksheet = workbook.addWorksheet("벤더연락처_템플릿") + + // 헤더 설정 + worksheet.columns = [ + { header: "벤더대표이메일*", key: "vendorEmail", width: 25 }, + { header: "담당자명*", key: "contactName", width: 20 }, + { header: "직책", key: "contactPosition", width: 15 }, + { header: "담당자이메일*", key: "contactEmail", width: 25 }, + { header: "담당자연락처", key: "contactPhone", width: 15 }, + { header: "담당자국가", key: "contactCountry", width: 15 }, + { header: "주담당자여부", key: "isPrimary", width: 12 }, + ] + + // 헤더 스타일 설정 + const headerRow = worksheet.getRow(1) + headerRow.font = { bold: true } + headerRow.fill = { + type: "pattern", + pattern: "solid", + fgColor: { argb: "FFE0E0E0" }, + } + + // 예시 데이터 추가 + worksheet.addRow({ + vendorEmail: "example@company.com", + contactName: "홍길동", + contactPosition: "대표", + contactEmail: "hong@company.com", + contactPhone: "010-1234-5678", + contactCountry: "대한민국", + isPrimary: "Y", + }) + + worksheet.addRow({ + vendorEmail: "example@company.com", + contactName: "김철수", + contactPosition: "과장", + contactEmail: "kim@company.com", + contactPhone: "010-9876-5432", + contactCountry: "대한민국", + isPrimary: "N", + }) + + const buffer = await workbook.xlsx.writeBuffer() + return new Blob([buffer], { + type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", + }) +} + +/** + * Excel 파일에서 연락처 데이터 파싱 + */ +export async function parseContactImportFile(file: File): Promise { + const arrayBuffer = await file.arrayBuffer() + const workbook = new ExcelJS.Workbook() + await workbook.xlsx.load(arrayBuffer) + + const worksheet = workbook.worksheets[0] + if (!worksheet) { + throw new Error("Excel 파일에 워크시트가 없습니다.") + } + + const data: ImportContactData[] = [] + + worksheet.eachRow((row, index) => { + console.log(`행 ${index} 처리 중:`, row.values) + // 헤더 행 건너뛰기 (1행) + if (index === 1) return + + const values = row.values as (string | null)[] + if (!values || values.length < 4) return + + const vendorEmail = values[1]?.toString().trim() + const contactName = values[2]?.toString().trim() + const contactPosition = values[3]?.toString().trim() + const contactEmail = values[4]?.toString().trim() + const contactPhone = values[5]?.toString().trim() + const contactCountry = values[6]?.toString().trim() + const isPrimary = values[7]?.toString().trim() + + // 필수 필드 검증 + if (!vendorEmail || !contactName || !contactEmail) { + return + } + + data.push({ + vendorEmail, + contactName, + contactPosition: contactPosition || undefined, + contactEmail, + contactPhone: contactPhone || undefined, + contactCountry: contactCountry || undefined, + isPrimary: isPrimary === "Y" || isPrimary === "y", + }) + + // rowNumber++ + }) + + return data +} \ No newline at end of file -- cgit v1.2.3