From 8a19a6fa336768d8b6712752c9d713360067ecb0 Mon Sep 17 00:00:00 2001 From: dujinkim Date: Mon, 8 Dec 2025 08:45:20 +0000 Subject: (최겸) 구매 피드백 수정, 안전담당자, pq항목 내 첨부, 내외자 구분, 도로명주소 api 반영(운영기준) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- lib/pq/pq-criteria/add-pq-dialog.tsx | 145 +- lib/pq/pq-criteria/update-pq-sheet.tsx | 148 +- lib/pq/service.ts | 9930 ++++++++++++++++---------------- lib/pq/table/pq-lists-toolbar.tsx | 4 +- 4 files changed, 5353 insertions(+), 4874 deletions(-) (limited to 'lib/pq') diff --git a/lib/pq/pq-criteria/add-pq-dialog.tsx b/lib/pq/pq-criteria/add-pq-dialog.tsx index 660eb360..1752f503 100644 --- a/lib/pq/pq-criteria/add-pq-dialog.tsx +++ b/lib/pq/pq-criteria/add-pq-dialog.tsx @@ -38,6 +38,10 @@ import { import { useToast } from "@/hooks/use-toast" import { createPqCriteria } from "../service" +import { uploadPqCriteriaFileAction } from "@/lib/pq/service" +import { Dropzone, DropzoneInput, DropzoneZone, DropzoneUploadIcon, DropzoneTitle, DropzoneDescription } from "@/components/ui/dropzone" +import { FileList, FileListHeader, FileListInfo, FileListItem, FileListName, FileListDescription, FileListAction } from "@/components/ui/file-list" +import { X, Loader2 } from "lucide-react" // PQ 생성을 위한 Zod 스키마 정의 const createPqSchema = z.object({ @@ -48,7 +52,7 @@ const createPqSchema = z.object({ description: z.string().optional(), remarks: z.string().optional(), inputFormat: z.string().default("TEXT"), - + type: z.string().optional(), }); type CreatePqFormType = z.infer; @@ -74,6 +78,12 @@ const inputFormatOptions = [ { value: "TEXT_FILE", label: "텍스트 + 파일" }, ]; +const typeOptions = [ + { value: "내자", label: "내자" }, + { value: "외자", label: "외자" }, + { value: "내외자", label: "내외자" }, +]; + interface AddPqDialogProps { pqListId: number; } @@ -81,6 +91,10 @@ interface AddPqDialogProps { export function AddPqDialog({ pqListId }: AddPqDialogProps) { const [open, setOpen] = React.useState(false) const [isSubmitting, setIsSubmitting] = React.useState(false) + const [isUploading, setIsUploading] = React.useState(false) + const [uploadedFiles, setUploadedFiles] = React.useState< + { fileName: string; url: string; size?: number; originalFileName?: string }[] + >([]) const router = useRouter() const { toast } = useToast() @@ -95,7 +109,7 @@ export function AddPqDialog({ pqListId }: AddPqDialogProps) { description: "", remarks: "", inputFormat: "TEXT", - + type: "내외자", }, }) const formState = form.formState @@ -105,7 +119,10 @@ export function AddPqDialog({ pqListId }: AddPqDialogProps) { setIsSubmitting(true) // 서버 액션 호출 - const result = await createPqCriteria(pqListId, data) + const result = await createPqCriteria(pqListId, { + ...data, + attachments: uploadedFiles, + }) if (!result.success) { toast({ @@ -124,6 +141,7 @@ export function AddPqDialog({ pqListId }: AddPqDialogProps) { // 모달 닫고 폼 리셋 form.reset() + setUploadedFiles([]) setOpen(false) // 페이지 새로고침 @@ -144,10 +162,34 @@ export function AddPqDialog({ pqListId }: AddPqDialogProps) { function handleDialogOpenChange(nextOpen: boolean) { if (!nextOpen) { form.reset() + setUploadedFiles([]) } setOpen(nextOpen) } + const handleUpload = async (files: File[]) => { + try { + setIsUploading(true) + for (const file of files) { + const uploaded = await uploadPqCriteriaFileAction(file) + setUploadedFiles((prev) => [...prev, uploaded]) + } + toast({ + title: "업로드 완료", + description: "첨부파일이 업로드되었습니다.", + }) + } catch (error) { + console.error(error) + toast({ + title: "업로드 실패", + description: "첨부파일 업로드 중 오류가 발생했습니다.", + variant: "destructive", + }) + } finally { + setIsUploading(false) + } + } + return ( @@ -253,6 +295,33 @@ export function AddPqDialog({ pqListId }: AddPqDialogProps) { )} /> + {/* Type 필드 */} + ( + + 내/외자 구분 + + 미선택 시 기본값은 내외자입니다. + + + )} + /> + {/* Input Format 필드 */} + {/* 첨부 파일 업로드 */} +
+
+ 첨부 파일 + {isUploading && ( +
+ 업로드 중... +
+ )} +
+ handleUpload(files)} + onDropRejected={() => + toast({ + title: "업로드 실패", + description: "파일 크기/형식을 확인하세요.", + variant: "destructive", + }) + } + disabled={isUploading} + > + {() => ( + + + + + +
+ +
+ 파일을 드래그하거나 클릭하여 업로드 + PDF, 이미지, 문서 (최대 600MB) +
+
+ + 기준 문서 첨부가 필요한 경우 업로드하세요. + + )} + + + {uploadedFiles.length > 0 && ( +
+

첨부된 파일 ({uploadedFiles.length})

+ + {uploadedFiles.map((file, idx) => ( + + + + {file.originalFileName || file.fileName} + {file.size && ( + {`${file.size} bytes`} + )} + + + setUploadedFiles((prev) => prev.filter((_, i) => i !== idx)) + } + > + + Remove + + + + ))} + +
+ )} +
+ {/* Description 필드 */} ; @@ -67,6 +72,12 @@ const inputFormatOptions = [ { value: "TEXT_FILE", label: "텍스트 + 파일" } ]; +const typeOptions = [ + { value: "내자", label: "내자" }, + { value: "외자", label: "외자" }, + { value: "내외자", label: "내외자" }, +]; + interface UpdatePqSheetProps extends React.ComponentPropsWithRef { pq: { @@ -79,11 +90,16 @@ interface UpdatePqSheetProps inputFormat: string; subGroupName: string | null; + type?: string | null; } | null } export function UpdatePqSheet({ pq, ...props }: UpdatePqSheetProps) { const [isUpdatePending, startUpdateTransition] = React.useTransition() + const [isUploading, setIsUploading] = React.useState(false) + const [attachments, setAttachments] = React.useState< + { fileName: string; url: string; size?: number; originalFileName?: string }[] + >([]) const router = useRouter() const form = useForm({ @@ -97,6 +113,7 @@ export function UpdatePqSheet({ pq, ...props }: UpdatePqSheetProps) { inputFormat: pq?.inputFormat ?? "TEXT", subGroupName: pq?.subGroupName ?? "", + type: pq?.type ?? "내외자", }, }) @@ -112,15 +129,51 @@ export function UpdatePqSheet({ pq, ...props }: UpdatePqSheetProps) { inputFormat: pq.inputFormat ?? "TEXT", subGroupName: pq.subGroupName ?? "", + type: pq.type ?? "내외자", }); + + // 기존 첨부 로드 + getPqCriteriaAttachments(pq.id).then((res) => { + if (res.success && res.data) { + setAttachments( + res.data.map((a) => ({ + fileName: a.fileName, + url: a.filePath, + size: a.fileSize ?? undefined, + originalFileName: a.originalFileName || a.fileName, + })) + ) + } else { + setAttachments([]) + } + }) } }, [pq, form]); + const handleUpload = async (files: File[]) => { + try { + setIsUploading(true) + for (const file of files) { + const uploaded = await uploadPqCriteriaFileAction(file) + setAttachments((prev) => [...prev, uploaded]) + } + toast.success("첨부파일이 업로드되었습니다") + } catch (error) { + console.error(error) + toast.error("첨부파일 업로드에 실패했습니다") + } finally { + setIsUploading(false) + } + } + function onSubmit(input: UpdatePqSchema) { startUpdateTransition(async () => { if (!pq) return - const result = await updatePqCriteria(pq.id, input) + const result = await updatePqCriteria(pq.id, { + ...input, + attachments, + }) if (!result.success) { toast.error(result.message || "PQ 항목 수정에 실패했습니다") @@ -231,6 +284,33 @@ export function UpdatePqSheet({ pq, ...props }: UpdatePqSheetProps) { )} /> + {/* Type 필드 */} + ( + + 내/외자 구분 + + 미선택 시 기본값은 내외자입니다. + + + )} + /> + {/* Input Format 필드 */} + {/* 첨부 파일 업로드 */} +
+
+ 첨부 파일 + {isUploading && ( +
+ 업로드 중... +
+ )} +
+ handleUpload(files)} + onDropRejected={() => + toast.error("파일 크기/형식을 확인하세요.") + } + disabled={isUploading} + > + {() => ( + + + + + +
+ +
+ 파일을 드래그하거나 클릭하여 업로드 + PDF, 이미지, 문서 (최대 600MB) +
+
+ + 기준 문서 첨부가 필요한 경우 업로드하세요. + + )} + + + {attachments.length > 0 && ( +
+

첨부된 파일 ({attachments.length})

+ + {attachments.map((file, idx) => ( + + + + {file.originalFileName || file.fileName} + {file.size && ( + {`${file.size} bytes`} + )} + + + setAttachments((prev) => prev.filter((_, i) => i !== idx)) + } + > + + Remove + + + + ))} + +
+ )} +
+ {/* Required 체크박스 */} diff --git a/lib/pq/service.ts b/lib/pq/service.ts index 57ce5f88..6d60e193 100644 --- a/lib/pq/service.ts +++ b/lib/pq/service.ts @@ -1,4870 +1,5062 @@ -"use server" - -import db from "@/db/db" -import { CopyPqListInput, CreatePqListInput, UpdatePqValidToInput, copyPqListSchema, createPqListSchema, updatePqValidToSchema, GetPqListsSchema, GetPQSchema, GetPQSubmissionsSchema } from "./validations" -import { unstable_cache } from "@/lib/unstable-cache"; -import { filterColumns } from "@/lib/filter-columns"; -import { getErrorMessage } from "@/lib/handle-error"; -import { asc, desc, ilike, inArray, and, gte, lte, not, or, eq, ne, count,isNull,SQL, sql, lt, gt, isNotNull} from "drizzle-orm"; -import { z } from "zod" -import { revalidateTag, unstable_noStore, revalidatePath} from "next/cache"; -import { format } from "date-fns" -import { pqCriterias, vendorCriteriaAttachments, vendorInvestigations, vendorInvestigationAttachments, vendorPQSubmissions, vendorPqCriteriaAnswers, vendorPqReviewLogs, siteVisitRequests, vendorSiteVisitInfo, siteVisitRequestAttachments } from "@/db/schema/pq" -import { sendEmail } from "../mail/sendEmail"; -import { decryptWithServerAction } from '@/components/drm/drmUtils' - -import { vendorAttachments, vendors } from "@/db/schema/vendors"; -import { vendorRegularRegistrations } from "@/db/schema/vendorRegistrations"; -import { saveFile, saveDRMFile } from "@/lib/file-stroage"; -import { GetVendorsSchema } from "../vendors/validations"; -import { selectVendors } from "../vendors/repository"; -import { projects, users, roles, userRoles } from "@/db/schema"; -import { headers } from 'next/headers'; -import { getServerSession } from "next-auth/next" -import { authOptions } from "@/app/api/auth/[...nextauth]/route" -import { alias } from 'drizzle-orm/pg-core'; -import { createPQFilterMapping, getPQJoinedTables } from "./helper"; -import { pqLists } from "@/db/schema/pq"; - -export interface PQAttachment { - attachId: number - fileName: string - filePath: string - fileSize?: number -} - -export interface PQItem { - answerId: number | null - criteriaId: number - code: string - checkPoint: string - description: string | null - remarks?: string | null - // 프로젝트 PQ 전용 필드 - contractInfo?: string | null - additionalRequirement?: string | null - answer: string - shiComment: string - vendorReply: string - attachments: PQAttachment[] - subGroupName: string - inputFormat: string - - createdAt: Date | null - updatedAt: Date | null -} - -export interface PQGroupData { - groupName: string - items: PQItem[] -} - -export interface ProjectPQ { - id: number; - projectId: number | null; - status: string; - submittedAt: Date | null; - projectCode: string; - projectName: string; -} - -export async function getPQProjectsByVendorId(vendorId: number): Promise { - const result = await db - .select({ - id: vendorPQSubmissions.id, - projectId: vendorPQSubmissions.projectId, - status: vendorPQSubmissions.status, - submittedAt: vendorPQSubmissions.submittedAt, - projectCode: projects.code, - projectName: projects.name, - }) - .from(vendorPQSubmissions) - .innerJoin( - projects, - eq(vendorPQSubmissions.projectId, projects.id) - ) - .where(eq(vendorPQSubmissions.vendorId, vendorId)) - .orderBy(projects.code); - - return result; -} - -export async function getPQDataByVendorId( - vendorId: number, - projectId?: number, - pqType?: "GENERAL" | "PROJECT" | "NON_INSPECTION" -): Promise { - try { - // 파라미터 유효성 검증 - if (isNaN(vendorId)) { - throw new Error("Invalid vendorId parameter"); - } - - // 타입 결정 로직 - let finalPqType: "GENERAL" | "PROJECT" | "NON_INSPECTION"; - if (pqType) { - finalPqType = pqType; - } else if (projectId) { - finalPqType = "PROJECT"; - } else { - finalPqType = "GENERAL"; - } - - // 기본 쿼리 구성 - const selectObj = { - criteriaId: pqCriterias.id, - groupName: pqCriterias.groupName, - code: pqCriterias.code, - checkPoint: pqCriterias.checkPoint, - description: pqCriterias.description, - remarks: pqCriterias.remarks, - - // 입력 형식 필드 추가 - inputFormat: pqCriterias.inputFormat, - - // 협력업체 응답 필드 - answer: vendorPqCriteriaAnswers.answer, - answerId: vendorPqCriteriaAnswers.id, - - // SHI 코멘트와 벤더 답변 필드 추가 - shiComment: vendorPqCriteriaAnswers.shiComment, - vendorReply: vendorPqCriteriaAnswers.vendorReply, - createdAt: vendorPqCriteriaAnswers.createdAt, - updatedAt: vendorPqCriteriaAnswers.updatedAt, - - // 첨부 파일 필드 - attachId: vendorCriteriaAttachments.id, - fileName: vendorCriteriaAttachments.fileName, - filePath: vendorCriteriaAttachments.filePath, - fileSize: vendorCriteriaAttachments.fileSize, - }; - - // 타입별 쿼리 조건 구성 - const pqListConditions = [ - eq(pqCriterias.pqListId, pqLists.id), - eq(pqLists.type, finalPqType), - eq(pqLists.isDeleted, false) - ]; - - const answerConditions = [ - eq(pqCriterias.id, vendorPqCriteriaAnswers.criteriaId), - eq(vendorPqCriteriaAnswers.vendorId, vendorId) - ]; - - // 프로젝트별 조건 추가 - if (finalPqType === "PROJECT" && projectId) { - pqListConditions.push(eq(pqLists.projectId, projectId)); - answerConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId)); - } else if (finalPqType === "GENERAL" || finalPqType === "NON_INSPECTION") { - pqListConditions.push(isNull(pqLists.projectId)); - answerConditions.push(isNull(vendorPqCriteriaAnswers.projectId)); - } - - const rows = await db - .select(selectObj) - .from(pqCriterias) - .innerJoin( - pqLists, - and(...pqListConditions) - ) - .leftJoin( - vendorPqCriteriaAnswers, - and(...answerConditions) - ) - .leftJoin( - vendorCriteriaAttachments, - eq(vendorPqCriteriaAnswers.id, vendorCriteriaAttachments.vendorCriteriaAnswerId) - ) - .orderBy(pqCriterias.groupName, pqCriterias.code); - - return processQueryResults(rows); - } catch (error) { - console.error("Error fetching PQ data:", error); - return []; - } - - // Helper function to process query results - function processQueryResults(rows: any[]) { - // 그룹별로 데이터 구성 - const groupMap = new Map>(); - - for (const row of rows) { - const g = row.groupName || "Others"; - - // 그룹 확인 - if (!groupMap.has(g)) { - groupMap.set(g, {}); - } - - const groupItems = groupMap.get(g)!; - - // 아직 이 기준을 처리하지 않았으면 PQItem 생성 - if (!groupItems[row.criteriaId]) { - groupItems[row.criteriaId] = { - answerId: row.answerId, - criteriaId: row.criteriaId, - code: row.code, - checkPoint: row.checkPoint, - description: row.description, - remarks: row.remarks, - answer: row.answer || "", - shiComment: row.shiComment || "", - vendorReply: row.vendorReply || "", - attachments: [], - inputFormat: row.inputFormat || "", - - subGroupName: row.subGroupName || "", - createdAt: row.createdAt, - updatedAt: row.updatedAt, - }; - } - - // 첨부 파일이 있으면 추가 - if (row.attachId) { - groupItems[row.criteriaId].attachments.push({ - attachId: row.attachId, - fileName: row.fileName || "", - filePath: row.filePath || "", - fileSize: row.fileSize || undefined, - }); - } - } - - // 최종 데이터 구성 - const data: PQGroupData[] = []; - for (const [groupName, itemsMap] of groupMap.entries()) { - const items = Object.values(itemsMap); - data.push({ groupName, items }); - } - - return data; - } -} - - -interface PQAttachmentInput { - fileName: string // original user-friendly file name - url: string // the UUID-based path stored on server - size?: number // optional file size -} - -interface SavePQAnswer { - criteriaId: number - answer: string - shiComment?: string - vendorReply?: string - attachments: PQAttachmentInput[] -} - -interface SavePQInput { - vendorId: number - projectId?: number - answers: SavePQAnswer[] -} - -/** - * 여러 항목을 한 번에 Upsert - */ -export async function savePQAnswersAction(input: SavePQInput) { - const { vendorId, projectId, answers } = input - - try { - for (const ans of answers) { - // 1) Check if a row already exists for (vendorId, criteriaId, projectId) - const queryConditions = [ - eq(vendorPqCriteriaAnswers.vendorId, vendorId), - eq(vendorPqCriteriaAnswers.criteriaId, ans.criteriaId) - ]; - - // Add projectId condition when it exists - if (projectId !== undefined) { - queryConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId)); - } else { - queryConditions.push(isNull(vendorPqCriteriaAnswers.projectId)); - } - - const existing = await db - .select() - .from(vendorPqCriteriaAnswers) - .where(and(...queryConditions)); - - let answerId: number - - // 2) If it exists, update the row; otherwise insert - if (existing.length === 0) { - // Insert new - const inserted = await db - .insert(vendorPqCriteriaAnswers) - .values({ - vendorId, - criteriaId: ans.criteriaId, - projectId: projectId || null, // Include projectId when provided - answer: ans.answer, - shiComment: ans.shiComment || null, - vendorReply: ans.vendorReply || null, - createdAt: new Date(), - updatedAt: new Date(), - }) - .returning({ id: vendorPqCriteriaAnswers.id }) - - answerId = inserted[0].id - } else { - // Update existing - answerId = existing[0].id - - await db - .update(vendorPqCriteriaAnswers) - .set({ - answer: ans.answer, - shiComment: ans.shiComment || null, - vendorReply: ans.vendorReply || null, - updatedAt: new Date(), - }) - .where(eq(vendorPqCriteriaAnswers.id, answerId)) - } - - // 3) Now manage attachments in vendorCriteriaAttachments - // 3a) Load old attachments from DB - const oldAttachments = await db - .select({ - id: vendorCriteriaAttachments.id, - filePath: vendorCriteriaAttachments.filePath, - }) - .from(vendorCriteriaAttachments) - .where(eq(vendorCriteriaAttachments.vendorCriteriaAnswerId, answerId)) - - // 3b) Gather the new filePaths (urls) from the client - const newPaths = ans.attachments.map(a => a.url) - - // 3c) Find attachments to remove - const toRemove = oldAttachments.filter(old => !newPaths.includes(old.filePath)) - if (toRemove.length > 0) { - const removeIds = toRemove.map(r => r.id) - await db - .delete(vendorCriteriaAttachments) - .where(inArray(vendorCriteriaAttachments.id, removeIds)) - } - - // 3d) Insert new attachments that aren't in DB - const oldPaths = oldAttachments.map(o => o.filePath) - const toAdd = ans.attachments.filter(a => !oldPaths.includes(a.url)) - - for (const attach of toAdd) { - await db.insert(vendorCriteriaAttachments).values({ - vendorCriteriaAnswerId: answerId, - fileName: attach.fileName, - filePath: attach.url, - fileSize: attach.size ?? null, - }) - } - } - - return { ok: true } - } catch (error) { - console.error("savePQAnswersAction error:", error) - return { ok: false, error: String(error) } - } -} - - - -/** - * PQ 제출 서버 액션 - 협력업체 상태를 PQ_SUBMITTED로 업데이트 - * @param vendorId 협력업체 ID - */ -export async function submitPQAction({ - vendorId, - projectId, - pqSubmissionId -}: { - vendorId: number; - projectId?: number; - pqSubmissionId?: number; // 특정 PQ 제출 ID가 있는 경우 사용 -}) { - unstable_noStore(); - - try { - const headersList = await headers(); - const host = headersList.get('host') || 'localhost:3000'; - - // 1. 모든 PQ 항목에 대한 응답이 있는지 검증 - const answerQueryConditions = [ - eq(vendorPqCriteriaAnswers.vendorId, vendorId) - ]; - - // Add projectId condition when it exists - if (projectId !== undefined) { - answerQueryConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId)); - } else { - answerQueryConditions.push(isNull(vendorPqCriteriaAnswers.projectId)); - } - - const pqCriteriaCount = await db - .select({ count: count() }) - .from(vendorPqCriteriaAnswers) - .where(and(...answerQueryConditions)); - - const totalPqCriteriaCount = pqCriteriaCount[0]?.count || 0; - - // 응답 데이터 검증 - if (totalPqCriteriaCount === 0) { - return { ok: false, error: "No PQ answers found" }; - } - - // 2. 협력업체 정보 조회 - const vendor = await db - .select({ - id: vendors.id, - vendorName: vendors.vendorName, - email: vendors.email, - status: vendors.status, - }) - .from(vendors) - .where(eq(vendors.id, vendorId)) - .then(rows => rows[0]); - - if (!vendor) { - return { ok: false, error: "Vendor not found" }; - } - - // Project 정보 조회 (projectId가 있는 경우) - let projectName = ''; - if (projectId) { - const projectData = await db - .select({ - projectName: projects.name - }) - .from(projects) - .where(eq(projects.id, projectId)) - .then(rows => rows[0]); - - projectName = projectData?.projectName || 'Unknown Project'; - } - - // 3. 현재 PQ 제출 상태 확인 및 업데이트 - const currentDate = new Date(); - let existingSubmission; - - // 특정 PQ Submission ID가 있는 경우 - if (pqSubmissionId) { - existingSubmission = await db - .select({ - id: vendorPQSubmissions.id, - status: vendorPQSubmissions.status, - type: vendorPQSubmissions.type, - requesterId: vendorPQSubmissions.requesterId - }) - .from(vendorPQSubmissions) - .where( - and( - eq(vendorPQSubmissions.id, pqSubmissionId), - eq(vendorPQSubmissions.vendorId, vendorId) - ) - ) - .then(rows => rows[0]); - - if (!existingSubmission) { - return { ok: false, error: "PQ submission not found or access denied" }; - } - } - // ID가 없는 경우 vendorId와 projectId로 조회 - else { - const pqType = projectId ? "PROJECT" : "GENERAL"; - - const submissionQueryConditions = [ - eq(vendorPQSubmissions.vendorId, vendorId), - eq(vendorPQSubmissions.type, pqType) - ]; - - if (projectId) { - submissionQueryConditions.push(eq(vendorPQSubmissions.projectId, projectId)); - } else { - submissionQueryConditions.push(isNull(vendorPQSubmissions.projectId)); - } - - existingSubmission = await db - .select({ - id: vendorPQSubmissions.id, - status: vendorPQSubmissions.status, - type: vendorPQSubmissions.type, - requesterId: vendorPQSubmissions.requesterId - }) - .from(vendorPQSubmissions) - .where(and(...submissionQueryConditions)) - .then(rows => rows[0]); - } - - // 제출 가능한 상태 확인 - const allowedStatuses = ["REQUESTED", "IN_PROGRESS", "SUBMITTED", "REJECTED"]; - - if (existingSubmission) { - if (!allowedStatuses.includes(existingSubmission.status)) { - return { - ok: false, - error: `Cannot submit PQ in current status: ${existingSubmission.status}` - }; - } - - // 기존 제출 상태 업데이트 - await db - .update(vendorPQSubmissions) - .set({ - status: "SUBMITTED", - submittedAt: currentDate, - updatedAt: currentDate, - }) - .where(eq(vendorPQSubmissions.id, existingSubmission.id)); - } else { - // PQ Submission ID가 없고 기존 submission도 없는 경우 새로운 제출 생성 - const pqType = projectId ? "PROJECT" : "GENERAL"; - - // PQ 번호 생성 (예: PQ-2024-001) - const currentYear = new Date().getFullYear(); - const pqNumber = `PQ-${currentYear}-${String(vendorId).padStart(3, '0')}`; - - await db - .insert(vendorPQSubmissions) - .values({ - pqNumber, - vendorId, - projectId: projectId || null, - type: pqType, - status: "SUBMITTED", - submittedAt: currentDate, - createdAt: currentDate, - updatedAt: currentDate, - }); - } - - // 4. 일반 PQ인 경우 벤더 상태도 업데이트 - if (!projectId) { - const allowedVendorStatuses = ["IN_PQ", "PENDING_REVIEW", "IN_REVIEW", "REJECTED", "PQ_FAILED"]; - - if (allowedVendorStatuses.includes(vendor.status)) { - await db - .update(vendors) - .set({ - status: "PQ_SUBMITTED", - updatedAt: currentDate, - }) - .where(eq(vendors.id, vendorId)); - } - } - - // 5. PQ 요청자에게 이메일 알림 발송 - const targetSubmissionId = existingSubmission?.id || ''; - const targetRequesterId = existingSubmission?.requesterId || null; - - // QM 담당자 이메일 조회 (해당 PQ와 연결된 실사에 배정된 경우) - let qmManagerEmail: string | null = null; - if (targetSubmissionId) { - try { - const inv = await db - .select({ qmManagerId: vendorInvestigations.qmManagerId }) - .from(vendorInvestigations) - .where(eq(vendorInvestigations.pqSubmissionId, Number(targetSubmissionId))) - .then(rows => rows[0]); - if (inv?.qmManagerId) { - const qmUser = await db - .select({ email: users.email }) - .from(users) - .where(eq(users.id, inv.qmManagerId)) - .then(rows => rows[0]); - qmManagerEmail = qmUser?.email || null; - } - } catch (e) { - console.warn("Failed to fetch QM manager email for PQ submission", e); - } - } - - if (targetRequesterId !== null) { - try { - // 요청자 정보 조회 - const requester = await db - .select({ - id: users.id, - name: users.name, - email: users.email, - }) - .from(users) - .where(eq(users.id, targetRequesterId)) - .then(rows => rows[0]); - - if (requester?.email) { - const emailSubject = projectId - ? `[eVCP] Project PQ Submitted: ${vendor.vendorName} for ${projectName}` - : `[eVCP] General PQ Submitted: ${vendor.vendorName}`; - - const adminUrl = `http://${host}/evcp/pq/${vendorId}/${targetSubmissionId}`; - - await sendEmail({ - to: requester.email, - cc: qmManagerEmail ? [qmManagerEmail] : undefined, - subject: emailSubject, - template: "pq-submitted-admin", - context: { - vendorName: vendor.vendorName, - vendorId: vendor.id, - projectId: projectId, - projectName: projectName, - isProjectPQ: !!projectId, - submittedDate: currentDate.toLocaleString(), - adminUrl, - requesterName: requester.name, - } - }); - } - } catch (emailError) { - console.error("Failed to send requester notification:", emailError); - } - } - - // 6. 벤더에게 확인 이메일 발송 - if (vendor.email) { - try { - const emailSubject = projectId - ? `[eVCP] Project PQ Submission Confirmation for ${projectName}` - : "[eVCP] General PQ Submission Confirmation"; - - const portalUrl = `${host}/partners/pq`; - - await sendEmail({ - to: vendor.email, - subject: emailSubject, - template: "pq-submitted-vendor", - context: { - vendorName: vendor.vendorName, - projectId: projectId, - projectName: projectName, - isProjectPQ: !!projectId, - submittedDate: currentDate.toLocaleString(), - portalUrl, - } - }); - } catch (emailError) { - console.error("Failed to send vendor confirmation:", emailError); - } - } - - // 7. 캐시 무효화 - revalidateTag("vendors"); - revalidateTag("vendor-status-counts"); - revalidateTag(`vendor-pq-submissions-${vendorId}`); - - if (projectId) { - revalidateTag(`project-pq-submissions-${projectId}`); - revalidateTag(`project-vendors-${projectId}`); - revalidateTag(`project-pq-${projectId}`); - } - - return { ok: true }; - } catch (error) { - console.error("PQ submit error:", error); - return { ok: false, error: getErrorMessage(error) }; - } -} - -/** - * 향상된 파일 업로드 서버 액션 - * - 직접 파일 처리 (file 객체로 받음) - * - 디렉토리 자동 생성 - * - 중복 방지를 위한 UUID 적용 - */ -/** - * 벤더용 파일 업로드 액션 (saveFile 사용) - */ -export async function uploadVendorFileAction(file: File, userId?: string) { - unstable_noStore(); - - try { - const result = await saveFile({ - file, - directory: 'pq/vendor', - originalName: file.name, - userId, - }); - - if (!result.success) { - throw new Error(result.error || "파일 업로드에 실패했습니다."); - } - - return { - fileName: result.fileName!, - url: result.publicPath!, - size: result.fileSize!, - }; - } catch (error) { - console.error("Vendor file upload error:", error); - throw new Error(`Upload failed: ${getErrorMessage(error)}`); - } -} - -/** - * SHI용 파일 업로드 액션 (saveDRMFile 사용) - */ -export async function uploadSHIFileAction(file: File, userId?: string) { - unstable_noStore(); - - try { - const result = await saveDRMFile( - file, - decryptWithServerAction, - 'pq/shi', - userId - ); - - if (!result.success) { - throw new Error(result.error || "파일 업로드에 실패했습니다."); - } - - return { - fileName: result.fileName!, - url: result.publicPath!, - size: result.fileSize!, - }; - } catch (error) { - console.error("SHI file upload error:", error); - throw new Error(`Upload failed: ${getErrorMessage(error)}`); - } -} - -/** - * 벤더용 여러 파일 일괄 업로드 - */ -export async function uploadVendorMultipleFilesAction(files: File[], userId?: string) { - unstable_noStore(); - - try { - const results = []; - - for (const file of files) { - try { - const result = await uploadVendorFileAction(file, userId); - results.push({ - success: true, - ...result - }); - } catch (error) { - results.push({ - success: false, - fileName: file.name, - error: getErrorMessage(error) - }); - } - } - - return { - ok: true, - results - }; - } catch (error) { - console.error("Vendor batch upload error:", error); - return { - ok: false, - error: getErrorMessage(error) - }; - } -} - -/** - * SHI용 여러 파일 일괄 업로드 - */ -export async function uploadSHIMultipleFilesAction(files: File[], userId?: string) { - unstable_noStore(); - - try { - const results = []; - - for (const file of files) { - try { - const result = await uploadSHIFileAction(file, userId); - results.push({ - success: true, - ...result - }); - } catch (error) { - results.push({ - success: false, - fileName: file.name, - error: getErrorMessage(error) - }); - } - } - - return { - ok: true, - results - }; - } catch (error) { - console.error("SHI batch upload error:", error); - return { - ok: false, - error: getErrorMessage(error) - }; - } -} - -// export async function getVendorsInPQ(input: GetVendorsSchema) { -// return unstable_cache( -// async () => { -// try { -// const offset = (input.page - 1) * input.perPage; - -// // 1) 고급 필터 -// const advancedWhere = filterColumns({ -// table: vendors, -// filters: input.filters, -// joinOperator: input.joinOperator, -// }); - -// // 2) 글로벌 검색 -// let globalWhere: SQL | undefined = undefined; -// if (input.search) { -// const s = `%${input.search}%`; -// globalWhere = or( -// ilike(vendors.vendorName, s), -// ilike(vendors.vendorCode, s), -// ilike(vendors.email, s), -// ilike(vendors.status, s) -// ); -// } - -// // 트랜잭션 내에서 데이터 조회 -// const { data, total } = await db.transaction(async (tx) => { -// // 협력업체 ID 모음 (중복 제거용) -// const vendorIds = new Set(); - -// // 1-A) 일반 PQ 답변이 있는 협력업체 찾기 (status와 상관없이) -// const generalPqVendors = await tx -// .select({ -// vendorId: vendorPqCriteriaAnswers.vendorId -// }) -// .from(vendorPqCriteriaAnswers) -// .innerJoin( -// vendors, -// eq(vendorPqCriteriaAnswers.vendorId, vendors.id) -// ) -// .where( -// and( -// isNull(vendorPqCriteriaAnswers.projectId), // 일반 PQ만 (프로젝트 PQ 아님) -// advancedWhere, -// globalWhere -// ) -// ) -// .groupBy(vendorPqCriteriaAnswers.vendorId); // 각 벤더당 한 번만 카운트 - -// generalPqVendors.forEach(v => vendorIds.add(v.vendorId)); - -// // 1-B) 프로젝트 PQ 답변이 있는 협력업체 ID 조회 (status와 상관없이) -// const projectPqVendors = await tx -// .select({ -// vendorId: vendorPQSubmissions.vendorId -// }) -// .from(vendorPQSubmissions) -// .innerJoin( -// vendors, -// eq(vendorPQSubmissions.vendorId, vendors.id) -// ) -// .where( -// and( -// eq(vendorPQSubmissions.type, "PROJECT"), -// // 최소한 IN_PROGRESS부터는 작업이 시작된 상태이므로 포함 -// not(eq(vendorPQSubmissions.status, "REQUESTED")), // REQUESTED 상태는 제외 -// advancedWhere, -// globalWhere -// ) -// ); - -// projectPqVendors.forEach(v => vendorIds.add(v.vendorId)); - -// // 중복 제거된 협력업체 ID 배열 -// const uniqueVendorIds = Array.from(vendorIds); - -// // 총 개수 (중복 제거 후) -// const total = uniqueVendorIds.length; - -// if (total === 0) { -// return { data: [], total: 0 }; -// } - -// // 페이징 처리 (정렬 후 limit/offset 적용) -// const paginatedIds = uniqueVendorIds.slice(offset, offset + input.perPage); - -// // 2) 페이징된 협력업체 상세 정보 조회 -// const vendorsData = await selectVendors(tx, { -// where: inArray(vendors.id, paginatedIds), -// orderBy: input.sort.length > 0 -// ? input.sort.map((item) => -// item.desc ? desc(vendors.vendorName) : asc(vendors.vendorName) -// ) -// : [asc(vendors.createdAt)], -// }); - -// // 3) 각 벤더별 PQ 상태 정보 추가 -// const vendorsWithPqInfo = await Promise.all( -// vendorsData.map(async (vendor) => { -// // 3-A) 첨부 파일 조회 -// const attachments = await tx -// .select({ -// id: vendorAttachments.id, -// fileName: vendorAttachments.fileName, -// filePath: vendorAttachments.filePath, -// }) -// .from(vendorAttachments) -// .where(eq(vendorAttachments.vendorId, vendor.id)); - -// // 3-B) 일반 PQ 제출 여부 확인 (PQ 답변이 있는지) -// const generalPqAnswers = await tx -// .select({ count: count() }) -// .from(vendorPqCriteriaAnswers) -// .where( -// and( -// eq(vendorPqCriteriaAnswers.vendorId, vendor.id), -// isNull(vendorPqCriteriaAnswers.projectId) -// ) -// ); - -// const hasGeneralPq = generalPqAnswers[0]?.count > 0; - -// // 3-C) 프로젝트 PQ 정보 조회 (모든 상태 포함) -// const projectPqs = await tx -// .select({ -// projectId: vendorPQSubmissions.projectId, -// projectName: projects.name, -// status: vendorPQSubmissions.status, -// submittedAt: vendorPQSubmissions.submittedAt, -// approvedAt: vendorPQSubmissions.approvedAt, -// rejectedAt: vendorPQSubmissions.rejectedAt -// }) -// .from(vendorPQSubmissions) -// .innerJoin( -// projects, -// eq(vendorPQSubmissions.projectId, projects.id) -// ) -// .where( -// and( -// eq(vendorPQSubmissions.vendorId, vendor.id), -// eq(vendorPQSubmissions.type, "PROJECT"), -// not(eq(vendorPQSubmissions.status, "REQUESTED")) // REQUESTED 상태는 제외 -// ) -// ); - -// const hasProjectPq = projectPqs.length > 0; - -// // 프로젝트 PQ 상태별 카운트 -// const projectPqStatusCounts = { -// inProgress: projectPqs.filter(p => p.status === "IN_PROGRESS").length, -// submitted: projectPqs.filter(p => p.status === "SUBMITTED").length, -// approved: projectPqs.filter(p => p.status === "APPROVED").length, -// rejected: projectPqs.filter(p => p.status === "REJECTED").length, -// total: projectPqs.length -// }; - -// // 3-D) PQ 상태 정보 추가 -// return { -// ...vendor, -// hasAttachments: attachments.length > 0, -// attachmentsList: attachments, -// pqInfo: { -// hasGeneralPq, -// hasProjectPq, -// projectPqs, -// projectPqStatusCounts, -// // 현재 PQ 상태 (UI에 표시 용도) -// pqStatus: getPqStatusDisplay(vendor.status, hasGeneralPq, hasProjectPq, projectPqStatusCounts) -// } -// }; -// }) -// ); - -// return { data: vendorsWithPqInfo, total }; -// }); - -// // 페이지 수 -// const pageCount = Math.ceil(total / input.perPage); - -// return { data, pageCount }; -// } catch (err) { -// console.error("Error in getVendorsInPQ:", err); -// // 에러 발생 시 -// return { data: [], pageCount: 0 }; -// } -// }, -// [JSON.stringify(input)], // 캐싱 키 -// { -// revalidate: 3600, -// tags: ["vendors-in-pq", "project-pqs"], // revalidateTag 호출 시 무효화 -// } -// )(); -// } - -// PQ 상태 표시 함수 -function getPqStatusDisplay( - vendorStatus: string, - hasGeneralPq: boolean, - hasProjectPq: boolean, - projectPqCounts: { inProgress: number, submitted: number, approved: number, rejected: number, total: number } -): string { - // 프로젝트 PQ 상태 문자열 생성 - let projectPqStatus = ""; - if (hasProjectPq) { - const parts = []; - if (projectPqCounts.inProgress > 0) { - parts.push(`진행중: ${projectPqCounts.inProgress}`); - } - if (projectPqCounts.submitted > 0) { - parts.push(`제출: ${projectPqCounts.submitted}`); - } - if (projectPqCounts.approved > 0) { - parts.push(`승인: ${projectPqCounts.approved}`); - } - if (projectPqCounts.rejected > 0) { - parts.push(`거부: ${projectPqCounts.rejected}`); - } - projectPqStatus = parts.join(", "); - } - - // 일반 PQ + 프로젝트 PQ 조합 상태 - if (hasGeneralPq && hasProjectPq) { - return `일반 PQ (${getPqVendorStatusText(vendorStatus)}) + 프로젝트 PQ (${projectPqStatus})`; - } else if (hasGeneralPq) { - return `일반 PQ (${getPqVendorStatusText(vendorStatus)})`; - } else if (hasProjectPq) { - return `프로젝트 PQ (${projectPqStatus})`; - } - - return "PQ 정보 없음"; -} - -// 협력업체 상태 텍스트 변환 -function getPqVendorStatusText(status: string): string { - switch (status) { - case "IN_PQ": return "진행중"; - case "PQ_SUBMITTED": return "제출됨"; - case "PQ_FAILED": return "실패"; - case "PQ_APPROVED": - case "APPROVED": return "승인됨"; - case "READY_TO_SEND": return "거래 준비"; - case "ACTIVE": return "활성"; - case "INACTIVE": return "비활성"; - case "BLACKLISTED": return "거래금지"; - default: return status; - } -} - - -export type VendorStatus = - | "PENDING_REVIEW" - | "IN_REVIEW" - | "REJECTED" - | "IN_PQ" - | "PQ_SUBMITTED" - | "PQ_FAILED" - | "APPROVED" - | "ACTIVE" - | "INACTIVE" - | "BLACKLISTED" - | "PQ_APPROVED" - - export async function updateVendorStatusAction( - vendorId: number, - newStatus: VendorStatus - ) { - try { - // 1) Update DB - await db.update(vendors) - .set({ status: newStatus }) - .where(eq(vendors.id, vendorId)) - - // 2) Load vendor's email & name - const vendor = await db.select().from(vendors).where(eq(vendors.id, vendorId)).then(r => r[0]) - if (!vendor) { - return { ok: false, error: "Vendor not found" } - } - const headersList = await headers(); - const host = headersList.get('host') || 'localhost:3000'; - const loginUrl = `http://${host}/partners/pq` - - // 3) Send email - await sendEmail({ - to: vendor.email || "", - subject: `Your PQ Status is now ${newStatus}`, - template: "vendor-pq-status", // matches .hbs file - context: { - name: vendor.vendorName, - status: newStatus, - loginUrl: loginUrl, // etc. - }, - }) - revalidateTag("vendors") - revalidateTag("vendors-in-pq") - return { ok: true } - } catch (error) { - console.error("updateVendorStatusAction error:", error) - return { ok: false, error: String(error) } - } - } - - type ProjectPQStatus = "REQUESTED" | "IN_PROGRESS" | "SUBMITTED" | "APPROVED" | "REJECTED"; - -/** - * Update the status of a project-specific PQ for a vendor - */ -export async function updateProjectPQStatusAction({ - vendorId, - projectId, - status, - comment -}: { - vendorId: number; - projectId: number; - status: ProjectPQStatus; - comment?: string; -}) { - try { - const currentDate = new Date(); - - // 1) Prepare update data with appropriate timestamps - const updateData: any = { - status, - updatedAt: currentDate, - }; - - // Add status-specific fields - if (status === "APPROVED") { - updateData.approvedAt = currentDate; - } else if (status === "REJECTED") { - updateData.rejectedAt = currentDate; - updateData.rejectReason = comment || null; - } else if (status === "SUBMITTED") { - updateData.submittedAt = currentDate; - } - - // 2) Update the project PQ record - await db - .update(vendorPQSubmissions) - .set(updateData) - .where( - and( - eq(vendorPQSubmissions.vendorId, vendorId), - eq(vendorPQSubmissions.projectId, projectId), - eq(vendorPQSubmissions.type, "PROJECT") - ) - ); - - // 3) Load vendor and project details for email - const vendor = await db - .select({ - id: vendors.id, - email: vendors.email, - vendorName: vendors.vendorName - }) - .from(vendors) - .where(eq(vendors.id, vendorId)) - .then(rows => rows[0]); - - if (!vendor) { - return { ok: false, error: "Vendor not found" }; - } - - const project = await db - .select({ - name: projects.name - }) - .from(projects) - .where(eq(projects.id, projectId)) - .then(rows => rows[0]); - - if (!project) { - return { ok: false, error: "Project not found" }; - } - - // 4) Send email notification - await sendEmail({ - to: vendor.email || "", - subject: `Your Project PQ for ${project.name} is now ${status}`, - template: "vendor-project-pq-status", // matches .hbs file (you might need to create this) - context: { - name: vendor.vendorName, - status, - projectName: project.name, - rejectionReason: status === "REJECTED" ? comment : undefined, - hasRejectionReason: status === "REJECTED" && !!comment, - loginUrl: `${process.env.NEXT_PUBLIC_URL}/partners/pq?projectId=${projectId}`, - approvalDate: status === "APPROVED" ? currentDate.toLocaleDateString() : undefined, - rejectionDate: status === "REJECTED" ? currentDate.toLocaleDateString() : undefined, - }, - }); - - // 5) Revalidate cache tags - revalidateTag("vendors"); - revalidateTag("vendors-in-pq"); - revalidateTag(`vendor-project-pqs-${vendorId}`); - revalidateTag(`project-pq-${projectId}`); - revalidateTag(`project-vendors-${projectId}`); - - return { ok: true }; - } catch (error) { - console.error("updateProjectPQStatusAction error:", error); - return { ok: false, error: String(error) }; - } -} - -// 코멘트 타입 정의 -interface ItemComment { - answerId: number; - checkPoint: string; // 체크포인트 정보 추가 - code: string; // 코드 정보 추가 - comment: string; -} - -/** - * PQ 변경 요청 처리 서버 액션 - * - * @param vendorId 협력업체 ID - * @param comment 항목별 코멘트 배열 (answerId, checkPoint, code, comment로 구성) - * @param generalComment 전체 PQ에 대한 일반 코멘트 (선택사항) - */ -export async function requestPqChangesAction({ - vendorId, - projectId, - comment, - generalComment, - reviewerName -}: { - vendorId: number; - projectId?: number; // Optional project ID for project-specific PQs - comment: ItemComment[]; - generalComment?: string; - reviewerName?: string; -}) { - try { - // 1) 상태 업데이트 (PQ 타입에 따라 다르게 처리) - if (projectId) { - // 프로젝트 PQ인 경우 vendorPQSubmissions 테이블 업데이트 - const projectPq = await db - .select() - .from(vendorPQSubmissions) - .where( - and( - eq(vendorPQSubmissions.vendorId, vendorId), - eq(vendorPQSubmissions.projectId, projectId), - eq(vendorPQSubmissions.type, "PROJECT") - ) - ) - .then(rows => rows[0]); - - if (!projectPq) { - return { ok: false, error: "Project PQ record not found" }; - } - - await db - .update(vendorPQSubmissions) - .set({ - status: "IN_PROGRESS", // 변경 요청 상태로 설정 - updatedAt: new Date(), - }) - .where( - and( - eq(vendorPQSubmissions.vendorId, vendorId), - eq(vendorPQSubmissions.projectId, projectId), - eq(vendorPQSubmissions.type, "PROJECT") - ) - ); - } else { - // 일반 PQ인 경우 vendors 테이블 업데이트 - await db - .update(vendors) - .set({ - status: "IN_PQ", // 변경 요청 상태로 설정 - updatedAt: new Date(), - }) - .where(eq(vendors.id, vendorId)); - } - - // 2) 협력업체 정보 가져오기 - const vendor = await db - .select() - .from(vendors) - .where(eq(vendors.id, vendorId)) - .then(r => r[0]); - - if (!vendor) { - return { ok: false, error: "Vendor not found" }; - } - - // 프로젝트 정보 가져오기 (프로젝트 PQ인 경우) - let projectName = ""; - if (projectId) { - const project = await db - .select({ - name: projects.name - }) - .from(projects) - .where(eq(projects.id, projectId)) - .then(rows => rows[0]); - - projectName = project?.name || "Unknown Project"; - } - - // 3) 각 항목별 코멘트 저장 - const currentDate = new Date(); - - - // 병렬로 모든 코멘트 저장 - if (comment && comment.length > 0) { - const insertPromises = comment.map(item => - db.insert(vendorPqReviewLogs) - .values({ - vendorPqCriteriaAnswerId: item.answerId, - // reviewerId: reviewerId, - reviewerName: reviewerName, - reviewerComment: item.comment, - createdAt: currentDate, - // 추가 메타데이터 필드가 있다면 저장 - // 이런 메타데이터는 DB 스키마에 해당 필드가 있어야 함 - // meta: JSON.stringify({ checkPoint: item.checkPoint, code: item.code }) - }) - ); - - // 모든 삽입 기다리기 - await Promise.all(insertPromises); - } - - // 4) 변경 요청 이메일 보내기 - // 코멘트 목록 준비 - const commentItems = comment.map(item => ({ - id: item.answerId, - code: item.code, - checkPoint: item.checkPoint, - text: item.comment - })); - - // PQ 유형에 따라 이메일 제목 및 내용 조정 - const emailSubject = projectId - ? `[IMPORTANT] Your Project PQ (${projectName}) requires changes` - : `[IMPORTANT] Your PQ submission requires changes`; - - // 로그인 URL - 프로젝트 PQ인 경우 다른 경로로 안내 - const loginUrl = projectId - ? `${process.env.NEXT_PUBLIC_URL}/partners/pq?projectId=${projectId}` - : `${process.env.NEXT_PUBLIC_URL}/partners/pq`; - - await sendEmail({ - to: vendor.email || "", - subject: emailSubject, - template: "vendor-pq-comment", // matches .hbs file - context: { - name: vendor.vendorName, - vendorCode: vendor.vendorCode, - loginUrl, - comments: commentItems, - generalComment: generalComment || "", - hasGeneralComment: !!generalComment, - commentCount: commentItems.length, - projectId, - projectName, - isProjPQ: !!projectId, - }, - }); - - // 5) 캐시 무효화 - PQ 유형에 따라 적절한 태그 무효화 - revalidateTag("vendors"); - revalidateTag("vendors-in-pq"); - - if (projectId) { - revalidateTag(`vendor-project-pqs-${vendorId}`); - revalidateTag(`project-pq-${projectId}`); - revalidateTag(`project-vendors-${projectId}`); - } - - return { ok: true }; - } catch (error) { - console.error("requestPqChangesAction error:", error); - return { ok: false, error: String(error) }; - } -} - -interface AddReviewCommentInput { - answerId: number // vendorPqCriteriaAnswers.id - comment: string - reviewerName?: string -} - -export async function addReviewCommentAction(input: AddReviewCommentInput) { - try { - // 1) Check that the answer row actually exists - const existing = await db - .select({ id: vendorPqCriteriaAnswers.id }) - .from(vendorPqCriteriaAnswers) - .where(eq(vendorPqCriteriaAnswers.id, input.answerId)) - - if (existing.length === 0) { - return { ok: false, error: "Item not found" } - } - - // 2) Insert the log - await db.insert(vendorPqReviewLogs).values({ - vendorPqCriteriaAnswerId: input.answerId, - reviewerComment: input.comment, - reviewerName: input.reviewerName ?? "AdminUser", - }) - - return { ok: true } - } catch (error) { - console.error("addReviewCommentAction error:", error) - return { ok: false, error: String(error) } - } -} - -interface GetItemReviewLogsInput { - answerId: number -} - -export async function getItemReviewLogsAction(input: GetItemReviewLogsInput) { - try { - - const logs = await db - .select() - .from(vendorPqReviewLogs) - .where(eq(vendorPqReviewLogs.vendorPqCriteriaAnswerId, input.answerId)) - .orderBy(desc(vendorPqReviewLogs.createdAt)); - - return { ok: true, data: logs }; - } catch (error) { - console.error("getItemReviewLogsAction error:", error); - return { ok: false, error: String(error) }; - } -} - -export interface VendorPQListItem { - projectId: number; - projectName: string; - status: string; - submittedAt?: Date | null; // Change to accept both undefined and null -} - -export interface VendorPQsList { - hasGeneralPq: boolean; - generalPqStatus?: string; // vendor.status for general PQ - projectPQs: VendorPQListItem[]; -} - -export async function getVendorPQsList(vendorId: number): Promise { - unstable_noStore(); - - try { - // 1. Check if vendor has general PQ answers - const generalPqAnswers = await db - .select({ count: count() }) - .from(vendorPqCriteriaAnswers) - .where( - and( - eq(vendorPqCriteriaAnswers.vendorId, vendorId), - isNull(vendorPqCriteriaAnswers.projectId) - ) - ); - - const hasGeneralPq = (generalPqAnswers[0]?.count || 0) > 0; - - // 2. Get vendor status for general PQ - let generalPqStatus; - if (hasGeneralPq) { - const vendor = await db - .select({ status: vendors.status }) - .from(vendors) - .where(eq(vendors.id, vendorId)) - .then(rows => rows[0]); - - generalPqStatus = vendor?.status; - } - - // 3. Get project PQs - const projectPQs = await db - .select({ - projectId: vendorPQSubmissions.projectId, - projectName: projects.name, - status: vendorPQSubmissions.status, - submittedAt: vendorPQSubmissions.submittedAt - }) - .from(vendorPQSubmissions) - .innerJoin( - projects, - eq(vendorPQSubmissions.projectId, projects.id) - ) - .where( - and( - eq(vendorPQSubmissions.vendorId, vendorId), - eq(vendorPQSubmissions.type, "PROJECT"), - not(eq(vendorPQSubmissions.status, "REQUESTED")) // Exclude requests that haven't been started - ) - ) - .orderBy(vendorPQSubmissions.updatedAt); - - return { - hasGeneralPq, - generalPqStatus, - projectPQs: projectPQs - }; - - } catch (error) { - console.error("Error fetching vendor PQs list:", error); - return { - hasGeneralPq: false, - projectPQs: [] - }; - } -} - - -export async function loadGeneralPQData(vendorId: number) { - "use server"; - return getPQDataByVendorId(vendorId) -} - -export async function loadProjectPQData(vendorId: number, projectId: number) { - "use server"; - return getPQDataByVendorId(vendorId, projectId) -} - -export async function loadGeneralPQAction(vendorId: number) { - return getPQDataByVendorId(vendorId); -} - -export async function loadProjectPQAction(vendorId: number, projectId?: number): Promise { - if (!projectId) { - throw new Error("Project ID is required for loading project PQ data"); - } - return getPQDataByVendorId(vendorId, projectId); -} - - - -export async function getAllPQsByVendorId(vendorId: number) { - unstable_noStore(); - - try { - const pqList = await db - .select({ - id: vendorPQSubmissions.id, - type: vendorPQSubmissions.type, - status: vendorPQSubmissions.status, - pqNumber: vendorPQSubmissions.pqNumber, - projectId: vendorPQSubmissions.projectId, - projectName: projects.name, - createdAt: vendorPQSubmissions.createdAt, - updatedAt: vendorPQSubmissions.updatedAt, - submittedAt: vendorPQSubmissions.submittedAt, - approvedAt: vendorPQSubmissions.approvedAt, - rejectedAt: vendorPQSubmissions.rejectedAt, - rejectReason: vendorPQSubmissions.rejectReason, - }) - .from(vendorPQSubmissions) - .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id)) - .where(eq(vendorPQSubmissions.vendorId, vendorId)) - .orderBy(desc(vendorPQSubmissions.createdAt)); - - return pqList; - } catch (error) { - console.error("Error fetching PQ list:", error); - return []; - } -} - -// 특정 PQ의 상세 정보 조회 (개별 PQ 페이지용) -export async function getPQById(pqSubmissionId: number, vendorId: number) { - unstable_noStore(); - - try { - - const pq = await db - .select({ - id: vendorPQSubmissions.id, - vendorId: vendorPQSubmissions.vendorId, - projectId: vendorPQSubmissions.projectId, - type: vendorPQSubmissions.type, - status: vendorPQSubmissions.status, - createdAt: vendorPQSubmissions.createdAt, - submittedAt: vendorPQSubmissions.submittedAt, - approvedAt: vendorPQSubmissions.approvedAt, - rejectedAt: vendorPQSubmissions.rejectedAt, - rejectReason: vendorPQSubmissions.rejectReason, - - // 벤더 정보 (추가) - vendorName: vendors.vendorName, - vendorCode: vendors.vendorCode, - vendorStatus: vendors.status, - vendorCountry: vendors.country, - vendorEmail: vendors.email, - vendorPhone: vendors.phone, - - // 프로젝트 정보 (조인) - projectName: projects.name, - projectCode: projects.code, - }) - .from(vendorPQSubmissions) - .leftJoin(vendors, eq(vendorPQSubmissions.vendorId, vendors.id)) - .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id)) - .where( - and( - eq(vendorPQSubmissions.id, pqSubmissionId), - eq(vendorPQSubmissions.vendorId, vendorId) - ) - ) - .limit(1) - .then(rows => rows[0]); - - if (!pq) { - throw new Error("PQ not found or access denied"); - } - - return pq; - } catch (error) { - console.error("Error fetching PQ by ID:", error); - throw error; - } -} - -export async function getPQStatusCounts(vendorId: number) { - try { - // 모든 PQ 상태 조회 (일반 PQ + 프로젝트 PQ) - const pqStatuses = await db - .select({ - status: vendorPQSubmissions.status, - count: count(), - }) - .from(vendorPQSubmissions) - .where(eq(vendorPQSubmissions.vendorId, vendorId)) - .groupBy(vendorPQSubmissions.status); - - // 상태별 개수를 객체로 변환 - const statusCounts = { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - }; - - // 조회된 결과를 statusCounts 객체에 매핑 - pqStatuses.forEach((item) => { - if (item.status in statusCounts) { - statusCounts[item.status as keyof typeof statusCounts] = item.count; - } - }); - - return statusCounts; - } catch (error) { - console.error("Error fetching PQ status counts:", error); - return { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - }; - } -} - -// 상태 레이블 함수 -function getStatusLabel(status: string): string { - switch (status) { - case "REQUESTED": - return "요청됨"; - case "IN_PROGRESS": - return "진행 중"; - case "SUBMITTED": - return "제출됨"; - case "APPROVED": - return "승인됨"; - case "REJECTED": - return "거부됨"; - default: - return status; - } -} - -export async function getPQSubmissions(input: GetPQSubmissionsSchema) { - unstable_noStore(); - - try { - const offset = (input.page - 1) * input.perPage; - - const pqFilterMapping = createPQFilterMapping(); - const joinedTables = getPQJoinedTables(); - - console.log(input, "input") - - // 1) 고급 필터 조건 (DataTableAdvancedToolbar에서) - let advancedWhere: SQL | undefined = undefined; - if (input.filters && input.filters.length > 0) { - advancedWhere = filterColumns({ - table: vendorPQSubmissions, - filters: input.filters, - joinOperator: input.joinOperator || 'and', - joinedTables, - customColumnMapping: pqFilterMapping, - }); - console.log("advancedWhere:", advancedWhere); - } - - // 2) 기본 필터 조건 (PQFilterSheet에서) - let basicWhere: SQL | undefined = undefined; - if (input.basicFilters && input.basicFilters.length > 0) { - basicWhere = filterColumns({ - table: vendorPQSubmissions, - filters: input.basicFilters, - joinOperator: input.basicJoinOperator || 'and', - joinedTables, - customColumnMapping: pqFilterMapping, - }); - console.log("basicWhere:", basicWhere); - } - - // 3) 글로벌 검색 조건 - let globalWhere: SQL | undefined = undefined; - if (input.search) { - const s = `%${input.search}%`; - - const validSearchConditions: SQL[] = []; - - // 기존 검색 조건들 - const nameCondition = ilike(vendors.vendorName, s); - if (nameCondition) validSearchConditions.push(nameCondition); - - const codeCondition = ilike(vendors.vendorCode, s); - if (codeCondition) validSearchConditions.push(codeCondition); - - const projectNameCondition = ilike(projects.name, s); - if (projectNameCondition) validSearchConditions.push(projectNameCondition); - - const projectCodeCondition = ilike(projects.code, s); - if (projectCodeCondition) validSearchConditions.push(projectCodeCondition); - - // 새로 추가된 검색 조건들 - const pqNumberCondition = ilike(vendorPQSubmissions.pqNumber, s); - if (pqNumberCondition) validSearchConditions.push(pqNumberCondition); - - const requesterCondition = ilike(users.name, s); - if (requesterCondition) validSearchConditions.push(requesterCondition); - - if (validSearchConditions.length > 0) { - globalWhere = or(...validSearchConditions); - } - } - - // 4) 날짜 조건 - let fromDateWhere: SQL | undefined = undefined; - let toDateWhere: SQL | undefined = undefined; - - if (input.submittedDateFrom) { - const fromDate = new Date(input.submittedDateFrom); - const condition = gte(vendorPQSubmissions.submittedAt, fromDate); - if (condition) fromDateWhere = condition; - } - - if (input.submittedDateTo) { - const toDate = new Date(input.submittedDateTo); - const condition = lte(vendorPQSubmissions.submittedAt, toDate); - if (condition) toDateWhere = condition; - } - - // 5) 최종 WHERE 조건 생성 - 각 그룹을 AND로 연결 - const whereConditions: SQL[] = []; - - // 고급 필터 조건 추가 - if (advancedWhere) whereConditions.push(advancedWhere); - - // 기본 필터 조건 추가 - if (basicWhere) whereConditions.push(basicWhere); - - // 기타 조건들 추가 - if (globalWhere) whereConditions.push(globalWhere); - if (fromDateWhere) whereConditions.push(fromDateWhere); - if (toDateWhere) whereConditions.push(toDateWhere); - - // 모든 조건을 AND로 연결 - const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined; - - console.log("Final WHERE conditions:", { - advancedWhere: !!advancedWhere, - basicWhere: !!basicWhere, - globalWhere: !!globalWhere, - dateConditions: !!(fromDateWhere || toDateWhere), - totalConditions: whereConditions.length - }); - - // 6) 전체 데이터 수 조회 - const totalResult = await db - .select({ count: count() }) - .from(vendorPQSubmissions) - .leftJoin(vendors, eq(vendorPQSubmissions.vendorId, vendors.id)) - .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id)) - .leftJoin(users, eq(vendorPQSubmissions.requesterId, users.id)) - .leftJoin(vendorInvestigations, eq(vendorInvestigations.pqSubmissionId, vendorPQSubmissions.id)) - .where(finalWhere); - - const total = totalResult[0]?.count || 0; - - if (total === 0) { - return { data: [], pageCount: 0 }; - } - - // 7) 정렬 및 페이징 처리된 데이터 조회 - const orderByColumns = input.sort.map((sort) => { - const column = sort.id; - - // JOIN된 테이블의 컬럼인 경우 적절한 테이블 참조 - if (column === 'vendorName') { - return sort.desc ? desc(vendors.vendorName) : asc(vendors.vendorName); - } else if (column === 'projectName') { - return sort.desc ? desc(projects.name) : asc(projects.name); - } else if (column === 'requesterName') { - return sort.desc ? desc(users.name) : asc(users.name); - } else { - // vendorPQSubmissions 테이블의 컬럼인 경우 - const dbColumn = column as keyof typeof vendorPQSubmissions.$inferSelect; - return sort.desc ? desc(vendorPQSubmissions[dbColumn]) : asc(vendorPQSubmissions[dbColumn]); - } - }); - - if (orderByColumns.length === 0) { - orderByColumns.push(desc(vendorPQSubmissions.updatedAt)); - } - - const pqSubmissions = await db - .select({ - id: vendorPQSubmissions.id, - type: vendorPQSubmissions.type, - pqNumber: vendorPQSubmissions.pqNumber, - requesterId: vendorPQSubmissions.requesterId, - requesterName: users.name, - status: vendorPQSubmissions.status, - createdAt: vendorPQSubmissions.createdAt, - updatedAt: vendorPQSubmissions.updatedAt, - submittedAt: vendorPQSubmissions.submittedAt, - approvedAt: vendorPQSubmissions.approvedAt, - rejectedAt: vendorPQSubmissions.rejectedAt, - rejectReason: vendorPQSubmissions.rejectReason, - pqItems: vendorPQSubmissions.pqItems, - // Vendor 정보 - vendorId: vendors.id, - vendorName: vendors.vendorName, - vendorCode: vendors.vendorCode, - email: vendors.email, - taxId: vendors.taxId, - vendorStatus: vendors.status, - // Project 정보 (프로젝트 PQ인 경우) - projectId: projects.id, - projectName: projects.name, - projectCode: projects.code, - }) - .from(vendorPQSubmissions) - .leftJoin(vendors, eq(vendorPQSubmissions.vendorId, vendors.id)) - .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id)) - .leftJoin(users, eq(vendorPQSubmissions.requesterId, users.id)) - .where(finalWhere) - .orderBy(...orderByColumns) - .limit(input.perPage) - .offset(offset); - - // 8) 각 PQ 제출에 대한 추가 정보 조회 (기존과 동일) - const pqSubmissionsWithDetails = await Promise.all( - pqSubmissions.map(async (submission) => { - // 기본 반환 객체 - const baseResult = { - ...submission, - answerCount: 0, - attachmentCount: 0, - pqStatus: getStatusLabel(submission.status), - pqTypeLabel: submission.type === "GENERAL" ? "일반 PQ" : "프로젝트 PQ", - }; - - // vendorId가 null이면 기본 정보만 반환 - if (submission.vendorId === null) { - return baseResult; - } - - try { - // 답변 수 조회 - const vendorId = submission.vendorId; - - const answerWhereConditions: SQL[] = []; - - const vendorCondition = eq(vendorPqCriteriaAnswers.vendorId, vendorId); - if (vendorCondition) answerWhereConditions.push(vendorCondition); - - let projectCondition: SQL | undefined; - if (submission.projectId !== null) { - projectCondition = eq(vendorPqCriteriaAnswers.projectId, submission.projectId); - } else { - projectCondition = isNull(vendorPqCriteriaAnswers.projectId); - } - - if (projectCondition) answerWhereConditions.push(projectCondition); - - const answerWhere = and(...answerWhereConditions); - - const answersResult = await db - .select({ count: count() }) - .from(vendorPqCriteriaAnswers) - .where(answerWhere); - - const answerCount = answersResult[0]?.count || 0; - - // 첨부 파일 수 조회 - const attachmentsResult = await db - .select({ count: count() }) - .from(vendorPqCriteriaAnswers) - .leftJoin( - vendorCriteriaAttachments, - eq(vendorCriteriaAttachments.vendorCriteriaAnswerId, vendorPqCriteriaAnswers.id) - ) - .where(answerWhere); - - const attachmentCount = attachmentsResult[0]?.count || 0; - - const requesters = alias(users, 'requesters'); - const qmManagers = alias(users, 'qmManagers'); - - const investigationResult = await db - .select({ - id: vendorInvestigations.id, - investigationStatus: vendorInvestigations.investigationStatus, - investigationAddress: vendorInvestigations.investigationAddress, - investigationMethod: vendorInvestigations.investigationMethod, - scheduledStartAt: vendorInvestigations.scheduledStartAt, - scheduledEndAt: vendorInvestigations.scheduledEndAt, - requestedAt: vendorInvestigations.requestedAt, - confirmedAt: vendorInvestigations.confirmedAt, - completedAt: vendorInvestigations.completedAt, - forecastedAt: vendorInvestigations.forecastedAt, - evaluationScore: vendorInvestigations.evaluationScore, - hasSupplementRequested: vendorInvestigations.hasSupplementRequested, - evaluationResult: vendorInvestigations.evaluationResult, - investigationNotes: vendorInvestigations.investigationNotes, - requesterId: vendorInvestigations.requesterId, - requesterName: requesters.name, - qmManagerId: vendorInvestigations.qmManagerId, - qmManagerName: qmManagers.name, - qmManagerEmail: qmManagers.email, - }) - .from(vendorInvestigations) - .leftJoin(requesters, eq(vendorInvestigations.requesterId, requesters.id)) - .leftJoin(qmManagers, eq(vendorInvestigations.qmManagerId, qmManagers.id)) - .where(and( - eq(vendorInvestigations.vendorId, submission.vendorId), - eq(vendorInvestigations.pqSubmissionId, submission.id) - )) - .orderBy(desc(vendorInvestigations.createdAt)) - .limit(1); - - const investigation = investigationResult[0] || null; - - // investigation이 있으면 해당 investigation의 최신 siteVisitRequest 조회 - let siteVisitRequestId: number | null = null; - if (investigation) { - const siteVisitRequestResult = await db - .select({ id: siteVisitRequests.id }) - .from(siteVisitRequests) - .where(eq(siteVisitRequests.investigationId, investigation.id)) - .orderBy(desc(siteVisitRequests.createdAt)) - .limit(1); - - siteVisitRequestId = siteVisitRequestResult[0]?.id || null; - } - - return { - ...baseResult, - answerCount, - attachmentCount, - siteVisitRequestId, - investigation - }; - } catch (error) { - console.error("Error fetching PQ details:", error); - return baseResult; - } - }) - ); - - const pageCount = Math.ceil(total / input.perPage); - - return { data: pqSubmissionsWithDetails, pageCount }; - } catch (err) { - console.error("Error in getPQSubmissions:", err); - return { data: [], pageCount: 0 }; - } -} - -export async function getPQStatusCountsAll() { - unstable_noStore(); - - try { - // 모든 PQ 상태별 개수 조회 (벤더 제한 없음) - const pqStatuses = await db - .select({ - status: vendorPQSubmissions.status, - count: count(), - }) - .from(vendorPQSubmissions) - .groupBy(vendorPQSubmissions.status); - - // 상태별 개수를 객체로 변환 - const statusCounts = { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - }; - - // 조회된 결과를 statusCounts 객체에 매핑 - pqStatuses.forEach((item) => { - if (item.status in statusCounts) { - statusCounts[item.status as keyof typeof statusCounts] = item.count; - } - }); - - return statusCounts; - } catch (error) { - console.error("Error fetching PQ status counts:", error); - return { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - }; - } -} - -// PQ 타입별, 상태별 개수 집계 함수 (추가 옵션) -export async function getPQDetailedStatusCounts() { - try { - // 타입별, 상태별 개수 조회 - const pqStatuses = await db - .select({ - type: vendorPQSubmissions.type, - status: vendorPQSubmissions.status, - count: count(), - }) - .from(vendorPQSubmissions) - .groupBy(vendorPQSubmissions.type, vendorPQSubmissions.status); - - // 결과를 저장할 객체 초기화 - const result = { - GENERAL: { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - total: 0 - }, - PROJECT: { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - total: 0 - }, - total: { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - total: 0 - } - }; - - // 결과 매핑 - pqStatuses.forEach((item) => { - if (item.type && item.status) { - const type = item.type as keyof typeof result; - const status = item.status as keyof typeof result.GENERAL; - - if (type in result && status in result[type]) { - // 타입별 상태 카운트 업데이트 - result[type][status] = item.count; - - // 타입별 합계 업데이트 - result[type].total += item.count; - - // 전체 상태별 카운트 업데이트 - result.total[status] += item.count; - - // 전체 합계 업데이트 - result.total.total += item.count; - } - } - }); - - return result; - } catch (error) { - console.error("Error fetching detailed PQ status counts:", error); - return { - GENERAL: { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - total: 0 - }, - PROJECT: { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - total: 0 - }, - total: { - REQUESTED: 0, - IN_PROGRESS: 0, - SUBMITTED: 0, - APPROVED: 0, - REJECTED: 0, - total: 0 - } - }; - } -} - -/** - * SHI 코멘트 업데이트 액션 - */ -export async function updateSHICommentAction({ - answerId, - shiComment, -}: { - answerId: number; - shiComment: string; -}) { - try { - await db - .update(vendorPqCriteriaAnswers) - .set({ - shiComment, - updatedAt: new Date(), - }) - .where(eq(vendorPqCriteriaAnswers.id, answerId)); - - return { ok: true }; - } catch (error) { - console.error("updateSHICommentAction error:", error); - return { ok: false, error: String(error) }; - } -} - -// PQ 승인 액션 -export async function approvePQAction({ - pqSubmissionId, - vendorId, -}: { - pqSubmissionId: number; - vendorId: number; -}) { - unstable_noStore(); - - try { - const headersList = await headers(); - const host = headersList.get('host') || 'localhost:3000'; - const currentDate = new Date(); - - // 1. PQ 제출 정보 조회 - const pqSubmission = await db - .select({ - id: vendorPQSubmissions.id, - vendorId: vendorPQSubmissions.vendorId, - projectId: vendorPQSubmissions.projectId, - type: vendorPQSubmissions.type, - status: vendorPQSubmissions.status, - }) - .from(vendorPQSubmissions) - .where( - and( - eq(vendorPQSubmissions.id, pqSubmissionId), - eq(vendorPQSubmissions.vendorId, vendorId) - ) - ) - .then(rows => rows[0]); - - if (!pqSubmission) { - return { ok: false, error: "PQ submission not found" }; - } - - // 2. 상태 확인 (SUBMITTED 상태만 승인 가능) - if (pqSubmission.status !== "SUBMITTED") { - return { - ok: false, - error: `Cannot approve PQ in current status: ${pqSubmission.status}` - }; - } - - // 3. 벤더 정보 조회 - const vendor = await db - .select({ - id: vendors.id, - vendorName: vendors.vendorName, - email: vendors.email, - status: vendors.status, - }) - .from(vendors) - .where(eq(vendors.id, vendorId)) - .then(rows => rows[0]); - - if (!vendor) { - return { ok: false, error: "Vendor not found" }; - } - - // 4. 프로젝트 정보 (프로젝트 PQ인 경우) - let projectName = ''; - if (pqSubmission.projectId) { - const projectData = await db - .select({ - id: projects.id, - name: projects.name, - }) - .from(projects) - .where(eq(projects.id, pqSubmission.projectId)) - .then(rows => rows[0]); - - projectName = projectData?.name || 'Unknown Project'; - } - - // 5. PQ 상태 업데이트 - await db - .update(vendorPQSubmissions) - .set({ - status: "APPROVED", - approvedAt: currentDate, - updatedAt: currentDate, - }) - .where(eq(vendorPQSubmissions.id, pqSubmissionId)); - - // 6. 일반 PQ인 경우 벤더 상태 업데이트 (선택사항) - if (pqSubmission.type === "GENERAL") { - await db - .update(vendors) - .set({ - status: "PQ_APPROVED", - updatedAt: currentDate, - }) - .where(eq(vendors.id, vendorId)); - } - - // 7. 벤더에게 이메일 알림 발송 - if (vendor.email) { - try { - const emailSubject = pqSubmission.projectId - ? `[eVCP] Project PQ Approved for ${projectName}` - : "[eVCP] General PQ Approved"; - - const portalUrl = `${host}/partners/pq`; - - await sendEmail({ - to: vendor.email, - subject: emailSubject, - template: "pq-approved-vendor", - context: { - vendorName: vendor.vendorName, - projectId: pqSubmission.projectId, - projectName: projectName, - isProjectPQ: !!pqSubmission.projectId, - approvedDate: currentDate.toLocaleString(), - portalUrl, - } - }); - } catch (emailError) { - console.error("Failed to send vendor notification:", emailError); - // 이메일 발송 실패가 전체 프로세스를 중단하지 않음 - } - } - - // 8. 캐시 무효화 - revalidateTag("vendors"); - revalidateTag("vendor-status-counts"); - revalidateTag("pq-submissions"); - revalidateTag(`vendor-pq-submissions-${vendorId}`); - - if (pqSubmission.projectId) { - revalidateTag(`project-pq-submissions-${pqSubmission.projectId}`); - revalidateTag(`project-vendors-${pqSubmission.projectId}`); - } - - return { ok: true }; - } catch (error) { - console.error("PQ approve error:", error); - return { ok: false, error: getErrorMessage(error) }; - } -} - -// QM 검토 승인 액션 -export async function approveQMReviewAction({ - pqSubmissionId, - vendorId, -}: { - pqSubmissionId: number; - vendorId: number; -}) { - unstable_noStore(); - - try { - const headersList = await headers(); - const host = headersList.get('host') || 'localhost:3000'; - const currentDate = new Date(); - - // 1. PQ 제출 정보 조회 - const pqSubmission = await db - .select({ - id: vendorPQSubmissions.id, - vendorId: vendorPQSubmissions.vendorId, - projectId: vendorPQSubmissions.projectId, - type: vendorPQSubmissions.type, - status: vendorPQSubmissions.status, - }) - .from(vendorPQSubmissions) - .where( - and( - eq(vendorPQSubmissions.id, pqSubmissionId), - eq(vendorPQSubmissions.vendorId, vendorId) - ) - ) - .then(rows => rows[0]); - - if (!pqSubmission) { - return { ok: false, error: "PQ submission not found" }; - } - - // 2. 상태 확인 (QM_REVIEWING 상태만 승인 가능) - // if (pqSubmission.status !== "QM_REVIEWING") { - // return { - // ok: false, - // error: `Cannot approve QM review in current status: ${pqSubmission.status}` - // }; - // } - - // 3. 벤더 정보 조회 - const vendor = await db - .select({ - id: vendors.id, - vendorName: vendors.vendorName, - email: vendors.email, - status: vendors.status, - }) - .from(vendors) - .where(eq(vendors.id, vendorId)) - .then(rows => rows[0]); - - if (!vendor) { - return { ok: false, error: "Vendor not found" }; - } - - // 4. 프로젝트 정보 (프로젝트 PQ인 경우) - let projectName = ''; - if (pqSubmission.projectId) { - const projectData = await db - .select({ - id: projects.id, - name: projects.name, - }) - .from(projects) - .where(eq(projects.id, pqSubmission.projectId)) - .then(rows => rows[0]); - - projectName = projectData?.name || 'Unknown Project'; - } - - // 5. PQ 상태를 QM_APPROVED로 업데이트 - await db - .update(vendorPQSubmissions) - .set({ - status: "QM_APPROVED", - approvedAt: currentDate, - updatedAt: currentDate, - }) - .where(eq(vendorPQSubmissions.id, pqSubmissionId)); - - // 6. 일반 PQ인 경우 벤더 상태를 PQ_APPROVED로 업데이트 - if (pqSubmission.type === "GENERAL") { - await db - .update(vendors) - .set({ - status: "PQ_APPROVED", - updatedAt: currentDate, - }) - .where(eq(vendors.id, vendorId)); - } - - // 7. 실사 상태 변경: QM 승인 시 QM_REVIEW_CONFIRMED로 전환 - try { - const existingInvestigation = await db - .select({ id: vendorInvestigations.id }) - .from(vendorInvestigations) - .where(eq(vendorInvestigations.pqSubmissionId, pqSubmissionId)) - .then(rows => rows[0]); - - if (existingInvestigation) { - await db - .update(vendorInvestigations) - .set({ investigationStatus: "QM_REVIEW_CONFIRMED", updatedAt: currentDate }) - .where(eq(vendorInvestigations.id, existingInvestigation.id)); - } else { - await db - .insert(vendorInvestigations) - .values({ - vendorId: vendorId, - pqSubmissionId: pqSubmissionId, - investigationStatus: "QM_REVIEW_CONFIRMED", - investigationMethod: "DOCUMENT_EVAL", - requestedAt: currentDate, - updatedAt: currentDate, - }); - } - } catch (e) { - console.error("Failed to set investigation QM_REVIEW_CONFIRMED on QM approve", e); - } - - // 8. 벤더에게 이메일 알림 발송 - if (vendor.email) { - try { - const emailSubject = pqSubmission.projectId - ? `[eVCP] Project PQ QM Approved for ${projectName}` - : "[eVCP] General PQ QM Approved"; - - const portalUrl = `${host}/partners/pq`; - - await sendEmail({ - to: vendor.email, - subject: emailSubject, - template: "pq-approved-vendor", - context: { - vendorName: vendor.vendorName, - projectId: pqSubmission.projectId, - projectName: projectName, - isProjectPQ: !!pqSubmission.projectId, - approvedDate: currentDate.toLocaleString(), - portalUrl, - } - }); - } catch (emailError) { - console.error("Failed to send vendor notification:", emailError); - } - } - - // 9. 캐시 무효화 - revalidateTag("vendors"); - revalidateTag("vendor-status-counts"); - revalidateTag("pq-submissions"); - revalidateTag("vendor-pq-submissions"); - revalidateTag("vendor-investigations"); - revalidatePath("/evcp/pq_new"); - - return { ok: true }; - } catch (error) { - console.error("QM review approve error:", error); - return { ok: false, error: getErrorMessage(error) }; - } -} - -// QM 검토 거절 액션 -export async function rejectQMReviewAction({ - pqSubmissionId, - vendorId, - rejectReason -}: { - pqSubmissionId: number; - vendorId: number; - rejectReason: string; -}) { - unstable_noStore(); - - try { - const headersList = await headers(); - const host = headersList.get('host') || 'localhost:3000'; - const currentDate = new Date(); - - // 1. PQ 제출 정보 조회 - const pqSubmission = await db - .select({ - id: vendorPQSubmissions.id, - vendorId: vendorPQSubmissions.vendorId, - projectId: vendorPQSubmissions.projectId, - type: vendorPQSubmissions.type, - status: vendorPQSubmissions.status, - }) - .from(vendorPQSubmissions) - .where( - and( - eq(vendorPQSubmissions.id, pqSubmissionId), - eq(vendorPQSubmissions.vendorId, vendorId) - ) - ) - .then(rows => rows[0]); - - if (!pqSubmission) { - return { ok: false, error: "PQ submission not found" }; - } - - // 2. 상태 확인 (QM_REVIEWING 상태만 거절 가능) - if (pqSubmission.status !== "QM_REVIEWING") { - return { - ok: false, - error: `Cannot reject QM review in current status: ${pqSubmission.status}` - }; - } - - // 3. 벤더 정보 조회 - const vendor = await db - .select({ - id: vendors.id, - vendorName: vendors.vendorName, - email: vendors.email, - status: vendors.status, - }) - .from(vendors) - .where(eq(vendors.id, vendorId)) - .then(rows => rows[0]); - - if (!vendor) { - return { ok: false, error: "Vendor not found" }; - } - - // 4. 프로젝트 정보 (프로젝트 PQ인 경우) - let projectName = ''; - if (pqSubmission.projectId) { - const projectData = await db - .select({ - id: projects.id, - name: projects.name, - }) - .from(projects) - .where(eq(projects.id, pqSubmission.projectId)) - .then(rows => rows[0]); - - projectName = projectData?.name || 'Unknown Project'; - } - - // 5. PQ 상태를 QM_REJECTED로 업데이트 - await db - .update(vendorPQSubmissions) - .set({ - status: "QM_REJECTED", - rejectedAt: currentDate, - rejectReason: rejectReason, - updatedAt: currentDate, - }) - .where(eq(vendorPQSubmissions.id, pqSubmissionId)); - - // 6. 일반 PQ인 경우 벤더 상태를 PQ_FAILED로 업데이트 - if (pqSubmission.type === "GENERAL") { - await db - .update(vendors) - .set({ - status: "PQ_FAILED", - updatedAt: currentDate, - }) - .where(eq(vendors.id, vendorId)); - } - - // 7. 실사 상태 변경: QM 거절 시 CANCELED로 전환 - try { - const existingInvestigation = await db - .select({ id: vendorInvestigations.id }) - .from(vendorInvestigations) - .where(eq(vendorInvestigations.pqSubmissionId, pqSubmissionId)) - .then(rows => rows[0]); - - if (existingInvestigation) { - await db - .update(vendorInvestigations) - .set({ investigationStatus: "CANCELED", updatedAt: currentDate }) - .where(eq(vendorInvestigations.id, existingInvestigation.id)); - } - } catch (e) { - console.error("Failed to set investigation CANCELED on QM reject", e); - } - - // 8. 벤더에게 이메일 알림 발송 - if (vendor.email) { - try { - const emailSubject = pqSubmission.projectId - ? `[eVCP] Project PQ Rejected for ${projectName}` - : "[eVCP] General PQ Rejected"; - - const portalUrl = `${host}/partners/pq`; - - await sendEmail({ - to: vendor.email, - subject: emailSubject, - template: "pq-rejected-vendor", - context: { - vendorName: vendor.vendorName, - projectId: pqSubmission.projectId, - projectName: projectName, - isProjectPQ: !!pqSubmission.projectId, - rejectedDate: currentDate.toLocaleString(), - rejectReason: rejectReason, - portalUrl, - } - }); - } catch (emailError) { - console.error("Failed to send vendor notification:", emailError); - } - } - - // 9. 캐시 무효화 - revalidateTag("vendors"); - revalidateTag("vendor-status-counts"); - revalidateTag("pq-submissions"); - revalidateTag("vendor-pq-submissions"); - revalidateTag("vendor-investigations"); - revalidatePath("/evcp/pq_new"); - - return { ok: true }; - } catch (error) { - console.error("QM review reject error:", error); - return { ok: false, error: getErrorMessage(error) }; - } -} - -// PQ 거부 액션 -export async function rejectPQAction({ - pqSubmissionId, - vendorId, - rejectReason -}: { - pqSubmissionId: number; - vendorId: number; - rejectReason: string; -}) { - unstable_noStore(); - - try { - const headersList = await headers(); - const host = headersList.get('host') || 'localhost:3000'; - const currentDate = new Date(); - - // 1. PQ 제출 정보 조회 - const pqSubmission = await db - .select({ - id: vendorPQSubmissions.id, - vendorId: vendorPQSubmissions.vendorId, - projectId: vendorPQSubmissions.projectId, - type: vendorPQSubmissions.type, - status: vendorPQSubmissions.status, - }) - .from(vendorPQSubmissions) - .where( - and( - eq(vendorPQSubmissions.id, pqSubmissionId), - eq(vendorPQSubmissions.vendorId, vendorId) - ) - ) - .then(rows => rows[0]); - - if (!pqSubmission) { - return { ok: false, error: "PQ submission not found" }; - } - - // 2. 상태 확인 (SUBMITTED 상태만 거부 가능) - if (pqSubmission.status !== "SUBMITTED") { - return { - ok: false, - error: `Cannot reject PQ in current status: ${pqSubmission.status}` - }; - } - - // 3. 벤더 정보 조회 - const vendor = await db - .select({ - id: vendors.id, - vendorName: vendors.vendorName, - email: vendors.email, - status: vendors.status, - }) - .from(vendors) - .where(eq(vendors.id, vendorId)) - .then(rows => rows[0]); - - if (!vendor) { - return { ok: false, error: "Vendor not found" }; - } - - // 4. 프로젝트 정보 (프로젝트 PQ인 경우) - let projectName = ''; - if (pqSubmission.projectId) { - const projectData = await db - .select({ - id: projects.id, - name: projects.name, - }) - .from(projects) - .where(eq(projects.id, pqSubmission.projectId)) - .then(rows => rows[0]); - - projectName = projectData?.name || 'Unknown Project'; - } - - // 5. PQ 상태 업데이트 - await db - .update(vendorPQSubmissions) - .set({ - status: "REJECTED", - rejectedAt: currentDate, - rejectReason: rejectReason, - updatedAt: currentDate, - }) - .where(eq(vendorPQSubmissions.id, pqSubmissionId)); - - // 6. 일반 PQ인 경우 벤더 상태 업데이트 (선택사항) - if (pqSubmission.type === "GENERAL") { - await db - .update(vendors) - .set({ - status: "PQ_FAILED", - updatedAt: currentDate, - }) - .where(eq(vendors.id, vendorId)); - } - - // 7. 벤더에게 이메일 알림 발송 - if (vendor.email) { - try { - const emailSubject = pqSubmission.projectId - ? `[eVCP] Project PQ Rejected for ${projectName}` - : "[eVCP] General PQ Rejected"; - - const portalUrl = `${host}/partners/pq`; - - await sendEmail({ - to: vendor.email, - subject: emailSubject, - template: "pq-rejected-vendor", - context: { - vendorName: vendor.vendorName, - projectId: pqSubmission.projectId, - projectName: projectName, - isProjectPQ: !!pqSubmission.projectId, - rejectedDate: currentDate.toLocaleString(), - rejectReason: rejectReason, - portalUrl, - } - }); - } catch (emailError) { - console.error("Failed to send vendor notification:", emailError); - // 이메일 발송 실패가 전체 프로세스를 중단하지 않음 - } - } - - // 8. 캐시 무효화 - revalidateTag("vendors"); - revalidateTag("vendor-status-counts"); - revalidateTag("pq-submissions"); - revalidateTag(`vendor-pq-submissions-${vendorId}`); - - if (pqSubmission.projectId) { - revalidateTag(`project-pq-submissions-${pqSubmission.projectId}`); - revalidateTag(`project-vendors-${pqSubmission.projectId}`); - } - - return { ok: true }; - } catch (error) { - console.error("PQ reject error:", error); - return { ok: false, error: getErrorMessage(error) }; - } -} - -// PQ 보완요청 메일 발송 액션 -export async function requestPqSupplementAction({ - pqSubmissionId, - vendorId, - comment, -}: { - pqSubmissionId: number; - vendorId: number; - comment: string; -}) { - unstable_noStore(); - try { - const session = await getServerSession(authOptions); - const currentUserEmail = session?.user?.email || null; - const headersList = await headers(); - const host = headersList.get('host') || 'localhost:3000'; - - // PQ/벤더/요청자 정보 조회 - const pq = await db - .select({ id: vendorPQSubmissions.id, pqNumber: vendorPQSubmissions.pqNumber, requesterId: vendorPQSubmissions.requesterId, projectId: vendorPQSubmissions.projectId }) - .from(vendorPQSubmissions) - .where(and(eq(vendorPQSubmissions.id, pqSubmissionId), eq(vendorPQSubmissions.vendorId, vendorId))) - .then(rows => rows[0]); - if (!pq) return { ok: false, error: 'PQ submission not found' }; - - const vendor = await db - .select({ vendorName: vendors.vendorName, email: vendors.email }) - .from(vendors) - .where(eq(vendors.id, vendorId)) - .then(rows => rows[0]); - if (!vendor?.email) return { ok: false, error: 'Vendor email not found' }; - - let requesterEmail: string | null = null; - if (pq.requesterId) { - const requester = await db - .select({ email: users.email }) - .from(users) - .where(eq(users.id, pq.requesterId)) - .then(rows => rows[0]); - requesterEmail = requester?.email || null; - } - - const reviewUrl = `${process.env.NEXT_PUBLIC_BASE_URL}/partners/pq_new`; - - await sendEmail({ - to: vendor.email, - cc: [currentUserEmail, requesterEmail].filter(Boolean) as string[], - subject: `[eVCP] PQ 보완 요청: ${vendor.vendorName}`, - template: 'pq-supplement-request', - context: { - vendorName: vendor.vendorName, - pqNumber: pq.pqNumber, - comment, - reviewUrl, - }, - }); - - revalidateTag('pq-submissions'); - return { ok: true }; - } catch (error) { - console.error('PQ supplement request error:', error); - return { ok: false, error: getErrorMessage(error) }; - } -} - - -// 실사 의뢰 생성 서버 액션 -export async function requestInvestigationAction( - pqSubmissionIds: number[], - currentUser: { id: number; epId: string | null; email?: string }, - data: { - qmManagerId: number, - forecastedAt: Date, - investigationAddress: string, - investigationNotes?: string - }, - options?: { - skipRevalidation?: boolean; // ✅ 핸들러에서 호출 시 revalidation 건너뛰기 - } -) { - try { - // 세션에서 요청자 정보 가져오기 - if (!currentUser.id) { - return { success: false, error: "인증된 사용자만 실사를 의뢰할 수 있습니다." }; - } - - const result = await db.transaction(async (tx) => { - // PQ 제출 정보 조회 - const pqSubmissions = await tx - .select({ - id: vendorPQSubmissions.id, - vendorId: vendorPQSubmissions.vendorId, - }) - .from(vendorPQSubmissions) - .where( - and( - inArray(vendorPQSubmissions.id, pqSubmissionIds), - eq(vendorPQSubmissions.status, "APPROVED") - ) - ); - - if (pqSubmissions.length === 0) { - throw new Error("승인된 PQ 제출 항목이 없습니다."); - } - - const now = new Date(); - - // 실사 요청 생성 - const investigations = pqSubmissions.map((pq) => { - return { - vendorId: pq.vendorId, - pqSubmissionId: pq.id, - investigationStatus: "PLANNED" as const, // enum 타입으로 명시적 지정 - qmManagerId: data.qmManagerId, - forecastedAt: data.forecastedAt, - investigationAddress: data.investigationAddress, - investigationNotes: data.investigationNotes || null, - requesterId: currentUser.id, - requestedAt: now, - createdAt: now, - updatedAt: now, - }; - }); - //PQ 제출 정보 업데이트, status를 QM_REVIEWING로 업데이트 - await tx - .update(vendorPQSubmissions) - .set({ - status: "QM_REVIEWING", - updatedAt: now, - }) - .where(inArray(vendorPQSubmissions.id, pqSubmissionIds)); - - - // 실사 요청 저장 - const created = await tx - .insert(vendorInvestigations) - .values(investigations) - .returning(); - - return created; - }); - - - // 이메일 발송 (트랜잭션 외부에서 실행) - try { - // 1. 협력업체 정보 조회 (이메일 포함) - const vendorIds = result.map(inv => inv.vendorId); - const uniqueVendorIds = [...new Set(vendorIds)]; - - const vendorInfos = await db - .select({ - id: vendors.id, - vendorName: vendors.vendorName, - email: vendors.email, - }) - .from(vendors) - .where(inArray(vendors.id, uniqueVendorIds)); - - // 2. QM 담당자 정보 조회 - const qmManager = await db - .select({ - id: users.id, - name: users.name, - email: users.email, - }) - .from(users) - .where(eq(users.id, data.qmManagerId)) - .limit(1) - .then(rows => rows[0]); - - // 3. 요청자(현재 사용자) 정보 조회 - const requester = await db - .select({ - id: users.id, - name: users.name, - email: users.email, - }) - .from(users) - .where(eq(users.id, currentUser.id)) - .limit(1) - .then(rows => rows[0]); - - const portalUrl = process.env.NEXT_PUBLIC_BASE_URL || "http://localhost:3000"; - const currentYear = new Date().getFullYear(); - const forecastedAtFormatted = format(data.forecastedAt, "yyyy-MM-dd"); - - // 4. 협력업체별로 이메일 발송 (investigation-request.hbs 템플릿 사용) - const vendorEmailPromises = vendorInfos - .filter(vendor => vendor.email) // 이메일이 있는 경우만 - .map(async (vendor) => { - try { - await sendEmail({ - to: vendor.email!, - subject: "[eVCP] 협력업체 실사 요청", - template: "investigation-request", - context: { - language: "ko", - vendorIds: [vendor.id], - notes: data.investigationNotes || "실사가 예정되어 있습니다.", - portalUrl: `${portalUrl}/ko/partners/site-visit`, - currentYear: currentYear, - }, - }); - console.log(`협력업체 이메일 발송 완료: ${vendor.vendorName} (${vendor.email})`); - } catch (emailError) { - console.error(`협력업체 이메일 발송 실패: ${vendor.vendorName} (${vendor.email})`, emailError); - } - }); - - await Promise.all(vendorEmailPromises); - - // 5. QM 담당자에게 알림 이메일 발송 - if (qmManager?.email) { - try { - const vendorNames = vendorInfos.map(v => v.vendorName); - - await sendEmail({ - to: qmManager.email, - subject: "[eVCP] 실사 의뢰 요청 알림", - template: "investigation-request-notification", - context: { - language: "ko", - recipientName: qmManager.name, - vendorNames: vendorNames, - forecastedAt: forecastedAtFormatted, - investigationAddress: data.investigationAddress, - investigationNotes: data.investigationNotes || null, - requesterName: requester?.name || "알 수 없음", - portalUrl: `${portalUrl}/evcp/vendor-investigation`, - currentYear: currentYear, - }, - }); - console.log(`QM 담당자 이메일 발송 완료: ${qmManager.name} (${qmManager.email})`); - } catch (emailError) { - console.error(`QM 담당자 이메일 발송 실패: ${qmManager.name} (${qmManager.email})`, emailError); - } - } - - // 6. 요청자(현재 사용자)에게 알림 이메일 발송 (QM 담당자와 다른 경우만) - // if (requester?.email && requester.id !== data.qmManagerId) { - // try { - // const vendorNames = vendorInfos.map(v => v.vendorName); - - // await sendEmail({ - // to: requester.email, - // subject: "[eVCP] 실사 의뢰 요청 알림", - // template: "investigation-request-notification", - // context: { - // language: "ko", - // recipientName: requester.name, - // vendorNames: vendorNames, - // forecastedAt: forecastedAtFormatted, - // investigationAddress: data.investigationAddress, - // investigationNotes: data.investigationNotes || null, - // requesterName: requester.name, - // portalUrl: `${portalUrl}/evcp/vendor-investigation`, - // currentYear: currentYear, - // }, - // }); - // console.log(`요청자 이메일 발송 완료: ${requester.name} (${requester.email})`); - // } catch (emailError) { - // console.error(`요청자 이메일 발송 실패: ${requester.name} (${requester.email})`, emailError); - // } - // } - } catch (emailErr) { - // 이메일 발송 실패는 로그만 남기고 전체 프로세스는 성공으로 처리 - console.error("이메일 발송 중 오류 발생:", emailErr); - } - - // 캐시 무효화 (skipRevalidation 옵션이 false일 때만) - if (!options?.skipRevalidation) { - revalidateTag("vendor-investigations") - revalidateTag("pq-submissions") - } - - return { - success: true, - count: result.length, - data: result - }; - } catch (err) { - console.error("실사 의뢰 중 오류 발생:", err); - return { - success: false, - error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다." - }; - } -} - -// 실사 의뢰 취소 서버 액션 -export async function cancelInvestigationAction(investigationIds: number[]) { - try { - const session = await getServerSession(authOptions) - const userId = session?.user?.id ? Number(session.user.id) : null - - if (!userId) { - return { success: false, error: "인증된 사용자만 실사를 취소할 수 있습니다." } - } - - const result = await db.transaction(async (tx) => { - // PLANNED 상태인 실사만 취소 가능 - const updatedInvestigations = await tx - .update(vendorInvestigations) - .set({ - investigationStatus: "CANCELED", - updatedAt: new Date(), - }) - .where( - and( - inArray(vendorInvestigations.id, investigationIds), - eq(vendorInvestigations.investigationStatus, "PLANNED") - ) - ) - .returning() - - return updatedInvestigations - }) - - // 캐시 무효화 - revalidateTag("vendor-investigations") - revalidateTag("pq-submissions") - - return { - success: true, - count: result.length, - data: result - } - } catch (err) { - console.error("실사 취소 중 오류 발생:", err) - return { - success: false, - error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다." - } - } -} - -// 실사 재의뢰 서버 액션 -export async function reRequestInvestigationAction( - investigationIds: number[], - currentUser?: { id: number }, // ✅ 핸들러에서 호출 시 사용자 정보 전달 - options?: { - skipRevalidation?: boolean; // ✅ 핸들러에서 호출 시 revalidation 건너뛰기 - } -) { - try { - let userId: number | null = null; - - if (currentUser) { - // 핸들러에서 호출 시 (결재 승인 후) - userId = currentUser.id; - - // ✅ 핸들러에서 호출 시 userId 검증: 없으면 잘못된 상황 (예외 처리) - if (!userId || userId <= 0) { - throw new Error('핸들러에서 호출 시 currentUser.id가 필수입니다.'); - } - } else { - // 직접 호출 시 (세션에서 가져오기) - const session = await getServerSession(authOptions); - userId = session?.user?.id ? Number(session.user.id) : null; - } - - if (!userId) { - return { success: false, error: "인증된 사용자만 실사를 재의뢰할 수 있습니다." } - } - - const result = await db.transaction(async (tx) => { - // CANCELED 상태인 실사만 재의뢰 가능 - const updatedInvestigations = await tx - .update(vendorInvestigations) - .set({ - investigationStatus: "PLANNED", - updatedAt: new Date(), - }) - .where( - and( - inArray(vendorInvestigations.id, investigationIds), - eq(vendorInvestigations.investigationStatus, "CANCELED") - ) - ) - .returning() - - return updatedInvestigations - }) - - // 캐시 무효화 (skipRevalidation 옵션이 false일 때만) - if (!options?.skipRevalidation) { - revalidateTag("vendor-investigations") - revalidateTag("pq-submissions") - } - - return { - success: true, - count: result.length, - data: result - } - } catch (err) { - console.error("실사 재의뢰 중 오류 발생:", err) - return { - success: false, - error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다." - } - } -} - -// 실사 결과 발송 서버 액션 -export async function sendInvestigationResultsAction(input: { - investigationIds: number[]; - purchaseComment?: string; -}) { - try { - const session = await getServerSession(authOptions) - const userId = session?.user?.id ? Number(session.user.id) : null - - if (!userId) { - return { success: false, error: "인증된 사용자만 실사 결과를 발송할 수 있습니다." } - } - - const result = await db.transaction(async (tx) => { - // 완료된 실사만 결과 발송 가능 - const investigations = await tx - .select({ - id: vendorInvestigations.id, - vendorId: vendorInvestigations.vendorId, - pqSubmissionId: vendorInvestigations.pqSubmissionId, - evaluationResult: vendorInvestigations.evaluationResult, - investigationNotes: vendorInvestigations.investigationNotes, - investigationAddress: vendorInvestigations.investigationAddress, - investigationMethod: vendorInvestigations.investigationMethod, - confirmedAt: vendorInvestigations.confirmedAt, - // Vendor 정보 - vendorCode: vendors.vendorCode, - vendorName: vendors.vendorName, - vendorEmail: vendors.email, - // PQ 정보 - pqNumber: vendorPQSubmissions.pqNumber, - pqItems: vendorPQSubmissions.pqItems, - projectCode: projects.code, - projectName: projects.name, - // 발신자 정보 - senderName: users.name, - senderEmail: users.email, - }) - .from(vendorInvestigations) - .leftJoin(vendors, eq(vendorInvestigations.vendorId, vendors.id)) - .leftJoin(vendorPQSubmissions, eq(vendorInvestigations.pqSubmissionId, vendorPQSubmissions.id)) - .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id)) - .leftJoin(users, eq(vendorInvestigations.requesterId, users.id)) - .where( - inArray(vendorInvestigations.id, input.investigationIds) - ) - - if (investigations.length === 0) { - throw new Error("발송할 수 있는 완료된 실사가 없습니다.") - } - - // 각 실사에 대해 이메일 발송 - const emailResults = await Promise.all( - investigations.map(async (investigation) => { - try { - // 이메일 컨텍스트 구성 - const emailContext = { - // 기본 정보 - pqNumber: investigation.pqNumber || "N/A", - vendorCode: investigation.vendorCode || "N/A", - vendorName: investigation.vendorName || "N/A", - - // 실사 정보 - pqItems를 itemCode-itemName 형태로 모든 항목 표시 - auditItem: (() => { - if (investigation.pqItems) { - try { - const parsed = typeof investigation.pqItems === 'string' - ? JSON.parse(investigation.pqItems) - : investigation.pqItems; - if (Array.isArray(parsed)) { - return parsed.map(item => { - if (typeof item === 'string') return item; - if (typeof item === 'object') { - const code = item.itemCode || item.code || ""; - const name = item.itemName || item.name || ""; - if (code && name) return `${code}-${name}`; - return name || code || String(item); - } - return String(item); - }).join(', '); - } - return String(parsed); - } catch { - return String(investigation.pqItems); - } - } - return investigation.projectName || "N/A"; - })(), - auditFactoryAddress: investigation.investigationAddress || "N/A", - auditMethod: getInvestigationMethodLabel(investigation.investigationMethod || ""), - auditResult: investigation.evaluationResult === "APPROVED" ? "Pass(승인)" : - investigation.evaluationResult === "SUPPLEMENT" ? "Pass(조건부승인)" : - investigation.evaluationResult === "REJECTED" ? "Fail(미승인)" : "N/A", - additionalNotes: input.purchaseComment || investigation.investigationNotes || "", - - // 발신자 정보 - senderName: investigation.senderName || "삼성중공업", - senderEmail: investigation.senderEmail || "procurement@samsung.com", - - // 이메일 제목 - subject: `[SHI Audit] 실사 결과 안내 _ ${investigation.vendorName} _ PQ No. ${investigation.pqNumber}`, - } - - // 이메일 발송 - if (investigation.vendorEmail) { - await sendEmail({ - to: investigation.vendorEmail, - subject: emailContext.subject, - template: "audit-result-notice", - context: emailContext, - }) - } else { - throw new Error("벤더 이메일 주소가 없습니다.") - } - - return { success: true, investigationId: investigation.id } - } catch (error) { - console.error(`실사 ID ${investigation.id} 이메일 발송 실패:`, error) - return { success: false, investigationId: investigation.id, error: error instanceof Error ? error.message : "알 수 없는 오류" } - } - }) - ) - - // 성공한 실사들의 상태를 RESULT_SENT로 업데이트 - const successfulInvestigationIds = emailResults - .filter(result => result.success) - .map(result => result.investigationId) - - if (successfulInvestigationIds.length > 0) { - await tx - .update(vendorInvestigations) - .set({ - investigationStatus: "RESULT_SENT", - purchaseComment: input.purchaseComment, - updatedAt: new Date(), - }) - .where(inArray(vendorInvestigations.id, successfulInvestigationIds)) - - // 정규업체등록관리에 레코드 생성 로직 - const successfulInvestigations = investigations.filter(inv => - successfulInvestigationIds.includes(inv.id) - ); - - for (const investigation of successfulInvestigations) { - // 1. 미실사 PQ는 제외 (이미 COMPLETED 상태인 것만 처리하므로 실사된 것들) - // 2. 승인된 실사만 정규업체등록 대상 - if (investigation.evaluationResult === "APPROVED") { - try { - // 기존 정규업체등록 레코드 확인 - const existingRegistration = await tx - .select({ id: vendorRegularRegistrations.id }) - .from(vendorRegularRegistrations) - .where(eq(vendorRegularRegistrations.vendorId, investigation.vendorId)) - .limit(1); - - // 프로젝트 PQ의 경우 기존 레코드가 있으면 skip, 없으면 생성 - // 일반 PQ의 경우 무조건 생성 (이미 체크는 위에서 함) - if (existingRegistration.length === 0) { - // pqItems를 majorItems로 변환 - JSON 통째로 넘겨줌 - let majorItemsJson = null; - if (investigation.pqItems) { - try { - // 이미 파싱된 객체거나 JSON 문자열인 경우 모두 처리 - const parsed = typeof investigation.pqItems === 'string' - ? JSON.parse(investigation.pqItems) - : investigation.pqItems; - - // 원본 구조를 최대한 보존하면서 JSON으로 저장 - majorItemsJson = JSON.stringify(parsed); - } catch { - // 파싱 실패 시 문자열로 저장 - majorItemsJson = JSON.stringify([{ - itemCode: "UNKNOWN", - itemName: String(investigation.pqItems) - }]); - } - } - - await tx.insert(vendorRegularRegistrations).values({ - vendorId: investigation.vendorId, - status: "under_review", // 실사 통과 상태로 시작 - majorItems: majorItemsJson, - registrationRequestDate: new Date().toISOString().split('T')[0], // date 타입으로 변환 - remarks: `PQ 실사 통과로 자동 생성 (PQ번호: ${investigation.pqNumber || 'N/A'})`, - }); - - console.log(`✅ 정규업체등록 레코드 생성: 벤더 ID ${investigation.vendorId}`); - } else { - console.log(`⏭️ 정규업체등록 레코드 이미 존재: 벤더 ID ${investigation.vendorId} (Skip)`); - } - } catch (error) { - console.error(`❌ 정규업체등록 레코드 생성 실패 (벤더 ID: ${investigation.vendorId}):`, error); - // 정규업체등록 생성 실패는 전체 프로세스를 중단하지 않음 - } - } - } - } - - return { - totalCount: investigations.length, - successCount: emailResults.filter(r => r.success).length, - failedCount: emailResults.filter(r => !r.success).length, - emailResults, - } - }) - - // 캐시 무효화 - revalidateTag("vendor-investigations") - revalidateTag("pq-submissions") - revalidateTag("vendor-regular-registrations") - - return { - success: true, - data: result, - message: `${result.successCount}개 실사 결과가 성공적으로 발송되었습니다.` - } - } catch (err) { - console.error("실사 결과 발송 중 오류 발생:", err) - return { - success: false, - error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다." - } - } -} - -// 실사 방법 라벨 변환 함수 -function getInvestigationMethodLabel(method: string): string { - switch (method) { - case "PURCHASE_SELF_EVAL": - return "구매자체평가" - case "DOCUMENT_EVAL": - return "서류평가" - case "PRODUCT_INSPECTION": - return "제품검사평가" - case "SITE_VISIT_EVAL": - return "방문실사평가" - default: - return method - } -} - -// export async function getQMManagers() { -// try { -// // QM 부서 사용자만 필터링 (department 필드가 있다고 가정) -// // 또는 QM 역할을 가진 사용자만 필터링 (role 필드가 있다고 가정) -// const qmUsers = await db -// .select({ -// id: users.id, -// name: users.name, -// email: users.email, -// }) -// .from(users) -// // .where( -// // // 필요에 따라 조건 조정 (예: QM 부서 또는 특정 역할만) -// // // eq(users.department, "QM") 또는 -// // // eq(users.role, "QM_MANAGER") -// // // 테스트를 위해 모든 사용자 반환도 가능 -// // eq(users.active, true) -// // ) -// .orderBy(users.name) - -// return { -// data: qmUsers, -// success: true -// } -// } catch (error) { -// console.error("QM 담당자 목록 조회 오류:", error) -// return { -// data: [], -// success: false, -// error: error instanceof Error ? error.message : "QM 담당자 목록을 가져오는 중 오류가 발생했습니다." -// } -// } -// } -export async function getQMManagers() { - try { - // domain이 'partners'가 아니고, isActive가 true인 사용자만 조회 - // 또는 deptName이 '품질경영팀('를 포함하는 경우도 포함 - const qmUsers = await db - .select({ - id: users.id, - name: users.name, - email: users.email, - isActive: users.isActive, - }) - .from(users) - .where( - and( - eq(users.isActive, true), - ne(users.domain, "partners"), - ilike(users.deptName, "%품질경영팀(%") - ) - ) - .orderBy(users.name); - - return { - data: qmUsers, - success: true - } - } catch (error) { - console.error("QM 담당자 목록 조회 오류:", error) - return { - data: [], - success: false, - error: error instanceof Error ? error.message : "QM 담당자 목록을 가져오는 중 오류가 발생했습니다." - } - } -} -export async function getFactoryLocationAnswer(vendorId: number, projectId: number | null = null) { - try { - // 1. "Location of Factory" 체크포인트를 가진 criteria 찾기 - const criteria = await db - .select({ - id: pqCriterias.id - }) - .from(pqCriterias) - .where(ilike(pqCriterias.checkPoint, "%Location of Factory%")) - .limit(1); - - if (!criteria.length) { - return { success: false, message: "Factory Location 질문을 찾을 수 없습니다." }; - } - - const criteriaId = criteria[0].id; - - // 2. 해당 criteria에 대한 벤더의 응답 조회 - const answerQuery = db - .select({ - answer: vendorPqCriteriaAnswers.answer - }) - .from(vendorPqCriteriaAnswers) - .where( - and( - eq(vendorPqCriteriaAnswers.vendorId, vendorId), - eq(vendorPqCriteriaAnswers.criteriaId, criteriaId) - ) - ); - - // 프로젝트 ID가 있으면 추가 조건 - if (projectId !== null) { - answerQuery.where(eq(vendorPqCriteriaAnswers.projectId, projectId)); - } else { - answerQuery.where(eq(vendorPqCriteriaAnswers.projectId, null)); - } - - const answers = await answerQuery.limit(1); - - if (!answers.length || !answers[0].answer) { - return { success: false, message: "공장 위치 정보를 찾을 수 없습니다." }; - } - - return { - success: true, - factoryLocation: answers[0].answer - }; - } catch (error) { - console.error("Factory location 조회 오류:", error); - return { success: false, message: "오류가 발생했습니다." }; - } -} - -// ----------------------------------------------------------------------------- -// PQ LISTS (GENERAL / PROJECT / NON_INSPECTION) CRUD + 조회 - -// LOAD CRITERIAS BY LIST -export async function getPqCriteriasByListId(listId: number) { - const criterias = await db - .select() - .from(pqCriterias) - .where(eq(pqCriterias.pqListId, listId)) - .orderBy(pqCriterias.groupName, pqCriterias.code); - return criterias; -} - -// ----------------------------------------------------------------------------- -// PQ LISTS CRUD 액션 - 개선된 버전 -// ----------------------------------------------------------------------------- - - -export async function getPQLists(input: GetPqListsSchema) { - return unstable_cache( - async () => { - try { - const offset = (input.page - 1) * input.perPage; - - let globalWhere; - if (input.search) { - const s = `%${input.search}%`; - globalWhere = or( - ilike(pqLists.name, s), - ilike(pqLists.type, s), - ilike(projects.code, s), - ilike(projects.name, s) - ); - } - - const advancedWhere = input.filters - ? filterColumns({ table: pqLists, filters: input.filters, joinOperator: input.joinOperator }) - : undefined; - - const finalWhere = and( - // eq(pqLists.isDeleted, false), - advancedWhere, - globalWhere - ); - - const orderBy = input.sort.length - ? input.sort.map((s) => (s.desc ? desc(pqLists.createdAt) : asc(pqLists.createdAt))) - : [desc(pqLists.createdAt)]; - - const { data, total } = await db.transaction(async (tx) => { - // 만료된 PQ 리스트들을 자동으로 비활성화 - const now = new Date(); - await tx - .update(pqLists) - .set({ - isDeleted: true, - updatedAt: now - }) - .where( - and( - eq(pqLists.isDeleted, false), - lt(pqLists.validTo, now), - isNotNull(pqLists.validTo) - ) - ); - - const data = await tx - .select({ - id: pqLists.id, - name: pqLists.name, - type: pqLists.type, - projectId: pqLists.projectId, - validTo: pqLists.validTo, - isDeleted: pqLists.isDeleted, - createdAt: pqLists.createdAt, - updatedAt: pqLists.updatedAt, - createdBy: users.name, - projectCode: projects.code, - projectName: projects.name, - updatedBy: users.name, - }) - .from(pqLists) - .leftJoin(projects, eq(pqLists.projectId, projects.id)) - .leftJoin(users, eq(pqLists.createdBy, users.id)) - .where(finalWhere) - .orderBy(...orderBy) - .offset(offset) - .limit(input.perPage); - - const countRes = await tx - .select({ count: count() }) - .from(pqLists) - .where(finalWhere); - - // 각 PQ 리스트의 항목 수 조회 - const dataWithCriteriaCount = await Promise.all( - data.map(async (item) => { - const criteriaCount = await getPqListCriteriaCount(item.id); - return { - ...item, - criteriaCount - }; - }) - ); - - - return { data: dataWithCriteriaCount, total: countRes[0]?.count ?? 0 }; - }); - - const pageCount = Math.ceil(total / input.perPage); - return { data, pageCount }; - } catch (err) { - console.error("Error in getPQLists:", err); - return { data: [], pageCount: 0 }; - } - }, - [JSON.stringify(input)], - { revalidate: 3600, tags: ["pq-lists"] } - )(); -} - -export async function togglePQListsAction(ids: number[], newIsDeleted: boolean) { - try { - if (ids.length === 0) { - return { success: false, message: "선택한 항목이 없습니다" }; - } - const session = await getServerSession(authOptions); - const userId = session?.user?.id ? Number(session.user.id) : null; - const now = new Date(); - - // 활성화하려는 경우 중복 활성화 체크 - if (!newIsDeleted) { - // 선택된 PQ 리스트들의 정보를 먼저 가져옴 - const selectedPqLists = await db - .select({ - id: pqLists.id, - name: pqLists.name, - type: pqLists.type, - projectId: pqLists.projectId, - }) - .from(pqLists) - .where(inArray(pqLists.id, ids)); - - // 현재 활성화된 PQ 리스트 확인 - const activePqLists = await db - .select({ - id: pqLists.id, - name: pqLists.name, - type: pqLists.type, - projectId: pqLists.projectId, - }) - .from(pqLists) - .where(and( - eq(pqLists.isDeleted, false), - not(inArray(pqLists.id, ids)) - )); - - // 각 선택된 PQ 리스트에 대해 중복 체크 - for (const selectedPq of selectedPqLists) { - // 일반 PQ 또는 미실사 PQ인 경우 - if (selectedPq.type === "GENERAL" || selectedPq.type === "NON_INSPECTION") { - const activeSameType = activePqLists.filter(pq => pq.type === selectedPq.type); - - if (activeSameType.length > 0) { - const activeNames = activeSameType.map(pq => pq.name).join(", "); - return { - success: false, - error: `${selectedPq.type === "GENERAL" ? "일반" : "미실사"} PQ는 하나만 활성화할 수 있습니다.먼저 활성화된 ${selectedPq.type === "GENERAL" ? "일반" : "미실사"} PQ를 비활성화한 후 활성화해주세요.` - }; - } - } - - // 프로젝트 PQ인 경우 - if (selectedPq.type === "PROJECT" && selectedPq.projectId) { - const activeSameProject = activePqLists.filter(pq => - pq.type === "PROJECT" && pq.projectId === selectedPq.projectId - ); - - if (activeSameProject.length > 0) { - const activeNames = activeSameProject.map(pq => pq.name).join(", "); - return { - success: false, - error: `프로젝트 PQ는 프로젝트별로 하나만 활성화할 수 있습니다. 먼저 활성화된 프로젝트 PQ를 비활성화한 후 활성화해주세요.` - }; - } - } - } - } - - const updated = await db - .update(pqLists) - .set({ isDeleted: newIsDeleted, updatedAt: now, updatedBy: userId }) - .where(inArray(pqLists.id, ids)) - .returning(); - revalidateTag("pq-lists"); - return { - success: true, - data: updated, - message: `${updated.length}개의 PQ 목록이 ${newIsDeleted ? "비활성화" : "활성화"}되었습니다` - }; - } catch (error) { - console.error("Error toggling PQ lists:", error); - return { - success: false, - message: "PQ 목록 상태 변경에 실패했습니다" - }; - } -} - -export async function createPQListAction(input: CreatePqListInput) { - try { - const validated = createPqListSchema.parse(input); - const session = await getServerSession(authOptions); - const userId = session?.user?.id; - // const userName = session?.user?.name || "Unknown"; - - // General PQ인 경우 중복 체크 - if (validated.type === "GENERAL") { - const existingGeneralPQ = await db - .select() - .from(pqLists) - .where( - and( - eq(pqLists.type, "GENERAL"), - eq(pqLists.isDeleted, false) - ) - ) - .limit(1); - - if (existingGeneralPQ.length > 0) { - return { - success: false, - error: "General PQ 목록은 하나만 생성할 수 있습니다. 먼저 활성화된 General PQ를 비활성화한 후 생성해주세요." - }; - } - } - - // Non-Inspection PQ인 경우 중복 체크 - if (validated.type === "NON_INSPECTION") { - const existingNonInspectionPQ = await db - .select() - .from(pqLists) - .where( - and( - eq(pqLists.type, "NON_INSPECTION"), - eq(pqLists.isDeleted, false) - ) - ) - .limit(1); - - if (existingNonInspectionPQ.length > 0) { - return { - success: false, - error: "미실사 PQ 목록은 하나만 생성할 수 있습니다. 먼저 활성화된 미실사 PQ를 비활성화한 후 생성해주세요." - }; - } - } - - // 프로젝트 PQ인 경우 중복 체크 - if (validated.type === "PROJECT" && validated.projectId) { - // 프로젝트 정보 조회 (이름과 코드 포함) - const projectInfo = await db - .select({ - code: projects.code, - name: projects.name - }) - .from(projects) - .where(eq(projects.id, validated.projectId)) - .limit(1) - .then(rows => rows[0]); - - const existingPQ = await db - .select() - .from(pqLists) - .where( - and( - eq(pqLists.projectId, validated.projectId), - eq(pqLists.type, "PROJECT"), - eq(pqLists.isDeleted, false) - ) - ) - .limit(1); - - if (existingPQ.length > 0) { - const projectDisplayName = projectInfo ? `${projectInfo.code} - ${projectInfo.name}` : "알 수 없는 프로젝트"; - return { - success: false, - error: `${projectDisplayName} 프로젝트에 대한 PQ가 이미 존재합니다` - }; - } - } - return await db.transaction(async (tx) => { - const now = new Date(); - const [newPqList] = await tx - .insert(pqLists) - .values({ - ...validated, - isDeleted: false, - createdAt: now, - updatedAt: now, - createdBy: userId, - updatedBy: userId, - }) - .returning(); - - // NON_INSPECTION 타입인 경우 기존 활성화된 NON_INSPECTION 리스트들을 비활성화 - if (validated.type === "NON_INSPECTION") { - await tx - .update(pqLists) - .set({ isDeleted: true, updatedAt: now, updatedBy: userId }) - .where( - and( - eq(pqLists.type, "NON_INSPECTION"), - eq(pqLists.isDeleted, false), - ne(pqLists.id, newPqList.id) // 새로 생성한 리스트 제외 - ) - ); - } - - // 프로젝트 PQ인 경우 General PQ 항목들을 자동으로 복사 - let copiedCriteriaCount = 0; - if (validated.type === "PROJECT") { - // General PQ 목록 찾기 - const generalPqList = await tx - .select() - .from(pqLists) - .where( - and( - eq(pqLists.type, "GENERAL"), - eq(pqLists.isDeleted, false) - ) - ) - .limit(1) - .then(rows => rows[0]); - - if (generalPqList) { - // General PQ의 항목들 조회 - const generalCriterias = await tx - .select() - .from(pqCriterias) - .where(eq(pqCriterias.pqListId, generalPqList.id)); - - if (generalCriterias.length > 0) { - // 새로운 프로젝트 PQ에 항목들 복사 - const newCriterias = generalCriterias.map(criteria => ({ - code: criteria.code, - checkPoint: criteria.checkPoint, - description: criteria.description, - remarks: criteria.remarks, - groupName: criteria.groupName, - subGroupName: criteria.subGroupName, - pqListId: newPqList.id, - inputFormat: criteria.inputFormat, - createdAt: now, - updatedAt: now, - })); - - await tx.insert(pqCriterias).values(newCriterias); - copiedCriteriaCount = newCriterias.length; - } - } - } - - revalidateTag("pq-lists"); - revalidateTag("pq-criterias"); - return { - success: true, - data: newPqList, - copiedCriteriaCount - }; - }); - } catch (error) { - console.error("Error creating PQ list:", error); - - if (error instanceof z.ZodError) { - return { - success: false, - error: "유효성 검사 실패", - details: error.errors - }; - } - - return { - success: false, - error: "PQ 목록 생성에 실패했습니다" - }; - } -} -export async function deletePQListsAction(ids: number[]) { - try { - if (ids.length === 0) { - return { - success: false, - message: "삭제할 항목을 선택해주세요" - }; - } - console.log("ids", ids) - console.log("pqLists", pqLists) - const now = new Date(); - const updated = await db - .update(pqLists) - .set({ isDeleted: true, updatedAt: now }) - .where(inArray(pqLists.id, ids)) - .returning(); - - revalidateTag("pq-lists"); - return { - success: true, - data: updated, - message: `${updated.length}개의 PQ 목록이 비활성화되었습니다` - }; - } catch (error) { - console.error("Error deleting PQ lists:", error); - return { - success: false, - message: "PQ 목록 삭제에 실패했습니다" - }; - } -} - -export async function getPqListById(id: number) { - try { - const pqList = await db - .select() - .from(pqLists) - .where(and( - eq(pqLists.id, id), - eq(pqLists.isDeleted, false) - )) - .limit(1) - .then(rows => rows[0]); - - return pqList || null; - } catch (error) { - console.error("Error fetching PQ list by ID:", error); - return null; - } -} - -export async function getPqListCriteriaCount(listId: number) { - try { - const result = await db - .select({ count: count() }) - .from(pqCriterias) - .where(eq(pqCriterias.pqListId, listId)); - - return result[0]?.count || 0; - } catch (error) { - console.error("Error getting PQ list criteria count:", error); - return 0; - } -} - - - -export async function copyPQListAction(input: CopyPqListInput) { - try { - const validated = copyPqListSchema.parse(input); - const session = await getServerSession(authOptions); - const userId = session?.user?.id; - return await db.transaction(async (tx) => { - // 1. 원본 PQ 목록 조회 - const sourcePqList = await tx - .select() - .from(pqLists) - .where(eq(pqLists.id, validated.sourcePqListId)) - .limit(1) - .then(rows => rows[0]); - - if (!sourcePqList) { - return { - success: false, - error: "복사할 PQ 목록을 찾을 수 없습니다" - }; - } - - // 2. 프로젝트 PQ인 경우에만 대상 프로젝트에 이미 PQ가 존재하는지 확인 - if (sourcePqList.type === "PROJECT" && validated.targetProjectId) { - // 프로젝트 정보 조회 (이름과 코드 포함) - const projectInfo = await tx - .select({ - code: projects.code, - name: projects.name - }) - .from(projects) - .where(eq(projects.id, validated.targetProjectId)) - .limit(1) - .then(rows => rows[0]); - - const existingProjectPQ = await tx - .select() - .from(pqLists) - .where( - and( - eq(pqLists.projectId, validated.targetProjectId), - eq(pqLists.type, "PROJECT"), - eq(pqLists.isDeleted, false) - ) - ) - .limit(1); - - if (existingProjectPQ.length > 0) { - const projectDisplayName = projectInfo ? `${projectInfo.code} - ${projectInfo.name}` : "알 수 없는 프로젝트"; - return { - success: false, - error: `${projectDisplayName} 프로젝트에 대한 PQ가 이미 존재합니다` - }; - } - } - - // 3. 새 PQ 목록 생성 - const now = new Date(); - const newName = validated.newName || `${sourcePqList.name} (복사본)`; - - const [newPqList] = await tx - .insert(pqLists) - .values({ - name: newName || sourcePqList.name, - type: sourcePqList.type, - projectId: sourcePqList.type === "PROJECT" ? validated.targetProjectId : null, - isDeleted: false, - createdAt: now, - updatedAt: now, - createdBy: userId, - updatedBy: userId, - validTo: validated.validTo, - }) - .returning(); - - // 4. 원본 PQ 항목들 조회 및 복사 - const sourceCriterias = await tx - .select() - .from(pqCriterias) - .where(eq(pqCriterias.pqListId, validated.sourcePqListId)); - - if (sourceCriterias.length > 0) { - const newCriterias = sourceCriterias.map(criteria => ({ - code: criteria.code, - checkPoint: criteria.checkPoint, - description: criteria.description, - remarks: criteria.remarks, - groupName: criteria.groupName, - subGroupName: criteria.subGroupName, - pqListId: newPqList.id, - inputFormat: criteria.inputFormat, - createdAt: now, - updatedAt: now, - createdBy: userId, - updatedBy: userId, - })); - - await tx.insert(pqCriterias).values(newCriterias); - } - - revalidateTag("pq-lists"); - return { - success: true, - data: newPqList, - copiedCriteriaCount: sourceCriterias.length - }; - }); - } catch (error) { - console.error("Error copying PQ list:", error); - - if (error instanceof z.ZodError) { - return { - success: false, - error: "유효성 검사 실패", - details: error.errors - }; - } - - return { - success: false, - error: error instanceof Error ? error.message : "PQ 목록 복사에 실패했습니다" - }; - } -} -export type Project = { - id: number; - projectCode: string; - projectName: string; - pjtType: string; -} -// ----------------------------------------------------------------------------- -export async function getProjects() { - try { - const projectList = await db.transaction(async (tx) => { - const results = await tx - .select({ - id: projects.id, - code: projects.code, - name: projects.name, - type: projects.type, - createdAt: projects.createdAt, - updatedAt: projects.updatedAt, - }) - .from(projects) - .orderBy(projects.code); - - return results; - }); - - return projectList; - } catch (error) { - console.error("프로젝트 목록 가져오기 실패:", error); - return []; - } -} - -// PQ 리스트에 등재된 프로젝트만 가져오는 함수 -export async function getProjectsWithPQList() { - try { - const projectList = await db.transaction(async (tx) => { - const results = await tx - .select({ - id: projects.id, - projectCode: projects.code, - projectName: projects.name, - pjtType: projects.type, - type: projects.type, - createdAt: projects.createdAt, - updatedAt: projects.updatedAt, - }) - .from(projects) - .innerJoin(pqLists, eq(projects.id, pqLists.projectId)) - .where( - and( - eq(pqLists.type, "PROJECT"), - eq(pqLists.isDeleted, false) - ) - ) - .orderBy(projects.code); - - return results; - }); - - return projectList; - } catch (error) { - console.error("PQ 리스트 등재 프로젝트 목록 가져오기 실패:", error); - return []; - } -} -// ----------------------------------------------------------------------------- -// PQ Criteria CRUD 액션 - 개선된 버전 -// ----------------------------------------------------------------------------- - -// PQ 항목 생성 (특정 PQ 목록에 속함) -export async function createPqCriteria( - pqListId: number, - input: { - code: string; - checkPoint: string; - groupName: string; - subGroupName?: string; - description?: string; - remarks?: string; - inputFormat?: string; - } -) { - try { - const now = new Date(); - const [newCriteria] = await db - .insert(pqCriterias) - .values({ - code: input.code, - checkPoint: input.checkPoint, - description: input.description || null, - remarks: input.remarks || null, - groupName: input.groupName, - subGroupName: input.subGroupName || null, - pqListId: pqListId, - inputFormat: input.inputFormat || "TEXT", - createdAt: now, - updatedAt: now, - }) - .returning(); - - revalidateTag("pq-criterias"); - return { - success: true, - data: newCriteria, - message: "PQ 항목이 성공적으로 생성되었습니다" - }; - } catch (error) { - console.error("Error creating PQ criteria:", error); - return { - success: false, - message: "PQ 항목 생성에 실패했습니다" - }; - } -} - -// PQ 항목 수정 -export async function updatePqCriteria( - id: number, - input: { - code: string; - checkPoint: string; - groupName: string; - subGroupName?: string; - description?: string; - remarks?: string; - inputFormat?: string; - } -) { - try { - const now = new Date(); - const [updatedCriteria] = await db - .update(pqCriterias) - .set({ - code: input.code, - checkPoint: input.checkPoint, - description: input.description || null, - remarks: input.remarks || null, - groupName: input.groupName, - subGroupName: input.subGroupName || null, - inputFormat: input.inputFormat || "TEXT", - updatedAt: now, - }) - .where(eq(pqCriterias.id, id)) - .returning(); - - if (!updatedCriteria) { - return { - success: false, - message: "수정할 PQ 항목을 찾을 수 없습니다" - }; - } - - revalidateTag("pq-criterias"); - return { - success: true, - data: updatedCriteria, - message: "PQ 항목이 성공적으로 수정되었습니다" - }; - } catch (error) { - console.error("Error updating PQ criteria:", error); - return { - success: false, - message: "PQ 항목 수정에 실패했습니다" - }; - } -} - -// PQ 항목 삭제 -export async function deletePqCriterias(ids: number[]) { - try { - if (ids.length === 0) { - return { - success: false, - message: "삭제할 항목을 선택해주세요" - }; - } - - const deletedCriterias = await db - .delete(pqCriterias) - .where(inArray(pqCriterias.id, ids)) - .returning(); - - revalidateTag("pq-criterias"); - return { - success: true, - data: deletedCriterias, - message: `${deletedCriterias.length}개의 PQ 항목이 삭제되었습니다` - }; - } catch (error) { - console.error("Error deleting PQ criterias:", error); - return { - success: false, - message: "PQ 항목 삭제에 실패했습니다" - }; - } -} - -/** - * PQ 제출 삭제 함수 (REQUESTED 상태일 때만 삭제 가능) - */ -export async function deletePQSubmissionAction(pqSubmissionId: number) { - try { - // PQ 제출 정보 조회 - const submission = await db - .select() - .from(vendorPQSubmissions) - .where(eq(vendorPQSubmissions.id, pqSubmissionId)) - .limit(1); - - if (submission.length === 0) { - return { success: false, error: "PQ 제출을 찾을 수 없습니다." }; - } - - const pqSubmission = submission[0]; - - // REQUESTED 상태가 아니면 삭제 불가 - if (pqSubmission.status !== "REQUESTED") { - return { success: false, error: "요청됨 상태가 아닌 PQ는 삭제할 수 없습니다." }; - } - - // 트랜잭션으로 관련 데이터 모두 삭제 - await db.transaction(async (tx) => { - // 1. PQ 답변 삭제 (vendorId와 projectId로 식별) - await tx - .delete(vendorPqCriteriaAnswers) - .where( - and( - eq(vendorPqCriteriaAnswers.vendorId, pqSubmission.vendorId), - pqSubmission.projectId - ? eq(vendorPqCriteriaAnswers.projectId, pqSubmission.projectId) - : isNull(vendorPqCriteriaAnswers.projectId) - ) - ); - - // 2. 첨부파일 삭제 (vendorCriteriaAnswerId로 연결) - const answerIds = await tx - .select({ id: vendorPqCriteriaAnswers.id }) - .from(vendorPqCriteriaAnswers) - .where( - and( - eq(vendorPqCriteriaAnswers.vendorId, pqSubmission.vendorId), - pqSubmission.projectId - ? eq(vendorPqCriteriaAnswers.projectId, pqSubmission.projectId) - : isNull(vendorPqCriteriaAnswers.projectId) - ) - ); - - if (answerIds.length > 0) { - await tx - .delete(vendorCriteriaAttachments) - .where(inArray(vendorCriteriaAttachments.vendorCriteriaAnswerId, answerIds.map(a => a.id))); - } - - // 3. PQ 제출 삭제 - await tx - .delete(vendorPQSubmissions) - .where(eq(vendorPQSubmissions.id, pqSubmissionId)); - }); - - // 삭제 후 캐시 무효화 (PQ 히스토리 캐시) - revalidateTag('pq-submissions'); - - return { success: true }; - } catch (error) { - console.error("deletePQSubmissionAction error:", error); - return { success: false, error: String(error) }; - } -} - -// PQ 목록별 항목 조회 (특정 pqListId에 속한 PQ 항목들) -// PQ 리스트 정보 조회 (상태 포함) -export async function getPQListInfo(pqListId: number) { - return unstable_cache( - async () => { - try { - const pqList = await db - .select({ - id: pqLists.id, - name: pqLists.name, - type: pqLists.type, - projectId: pqLists.projectId, - validTo: pqLists.validTo, - isDeleted: pqLists.isDeleted, - createdAt: pqLists.createdAt, - updatedAt: pqLists.updatedAt, - }) - .from(pqLists) - .where(and( - eq(pqLists.id, pqListId), - eq(pqLists.isDeleted, false) - )) - .limit(1) - .then(rows => rows[0]); - - if (!pqList) { - return { - success: false, - error: "PQ 목록을 찾을 수 없습니다" - }; - } - - // 현재 시간과 비교하여 상태 결정 - const now = new Date(); - const isValid = !pqList.validTo || pqList.validTo > now; - const status = isValid ? "ACTIVE" : "INACTIVE"; - - return { - success: true, - data: { - ...pqList, - status - } - }; - } catch (error) { - console.error("Error in getPQListInfo:", error); - return { - success: false, - error: "PQ 목록 정보를 가져오는 중 오류가 발생했습니다" - }; - } - }, - [`pq-list-info-${pqListId}`], - { - tags: ["pq-lists"], - revalidate: 3600, // 1시간 - } - )(); -} - -// 활성화된 미실사 PQ 리스트 조회 (하나만 존재한다고 가정) -export async function getNonInspectionPQLists() { - unstable_noStore(); - - try { - const pqList = await db - .select({ - id: pqLists.id, - name: pqLists.name, - type: pqLists.type, - projectId: pqLists.projectId, - validTo: pqLists.validTo, - isDeleted: pqLists.isDeleted, - createdAt: pqLists.createdAt, - updatedAt: pqLists.updatedAt, - }) - .from(pqLists) - .where(and( - eq(pqLists.type, "NON_INSPECTION"), - eq(pqLists.isDeleted, false) - )) - .limit(1) - .then(rows => rows[0]); - - if (!pqList) { - return { - success: false, - error: "활성화된 미실사 PQ 목록을 찾을 수 없습니다" - }; - } - - // 현재 시간과 비교하여 상태 결정 - const now = new Date(); - const isValid = !pqList.validTo || pqList.validTo > now; - const status = isValid ? "ACTIVE" : "INACTIVE"; - - return { - success: true, - data: { - ...pqList, - status - } - }; - } catch (error) { - console.error("Error in getNonInspectionPQLists:", error); - return { - success: false, - error: "미실사 PQ 목록 정보를 가져오는 중 오류가 발생했습니다" - }; - } -} - -export async function getPQsByListId(pqListId: number, input: GetPQSchema) { - return unstable_cache( - async () => { - try { - const offset = (input.page - 1) * input.perPage; - - // 검색 조건 - let globalWhere; - if (input.search) { - const s = `%${input.search}%`; - globalWhere = or( - ilike(pqCriterias.code, s), - ilike(pqCriterias.groupName, s), - ilike(pqCriterias.subGroupName, s), - ilike(pqCriterias.remarks, s), - ilike(pqCriterias.checkPoint, s), - ilike(pqCriterias.description, s) - ); - } - - // 고급 필터 - const advancedWhere = input.filters - ? filterColumns({ table: pqCriterias, filters: input.filters, joinOperator: input.joinOperator }) - : undefined; - - // 최종 WHERE 조건 (pqListId 조건 추가) - const finalWhere = and( - eq(pqCriterias.pqListId, pqListId), // 특정 PQ 목록에 속한 항목들만 - advancedWhere, - globalWhere - ); - - // 정렬 - const orderBy = input.sort.length - ? input.sort.map((s) => (s.desc ? desc(pqCriterias[s.id]) : asc(pqCriterias[s.id]))) - : [asc(pqCriterias.createdAt)]; - - const { data, total } = await db.transaction(async (tx) => { - // 데이터 조회 - const data = await tx - .select({ - id: pqCriterias.id, - code: pqCriterias.code, - checkPoint: pqCriterias.checkPoint, - description: pqCriterias.description, - remarks: pqCriterias.remarks, - groupName: pqCriterias.groupName, - subGroupName: pqCriterias.subGroupName, - pqListId: pqCriterias.pqListId, - inputFormat: pqCriterias.inputFormat, - - createdAt: pqCriterias.createdAt, - updatedAt: pqCriterias.updatedAt, - }) - .from(pqCriterias) - .where(finalWhere) - .orderBy(...orderBy) - .offset(offset) - .limit(input.perPage); - - // 카운트 조회 - const countRes = await tx - .select({ count: count() }) - .from(pqCriterias) - .where(finalWhere); - - const total = countRes[0]?.count ?? 0; - - return { data, total }; - }); - - const pageCount = Math.ceil(total / input.perPage); - return { data, pageCount }; - } catch (err) { - console.error("Error in getPQsByListId:", err); - return { data: [], pageCount: 0 }; - } - }, - [JSON.stringify(input), pqListId.toString()], - { revalidate: 3600, tags: ["pq-criterias"] } - )(); -} - -// 실사 정보 업데이트 액션 (구매자체평가용) -export async function updateInvestigationDetailsAction(input: { - investigationId: number; - confirmedAt?: Date; - evaluationResult?: "APPROVED" | "SUPPLEMENT" | "REJECTED"; - investigationNotes?: string; - attachments?: File[]; -}) { - try { - const updateData: any = { - updatedAt: new Date(), - }; - - if (input.confirmedAt !== undefined) { - updateData.confirmedAt = input.confirmedAt; - } - - if (input.evaluationResult !== undefined) { - updateData.evaluationResult = input.evaluationResult; - } - - if (input.investigationNotes !== undefined) { - updateData.investigationNotes = input.investigationNotes; - } - // evaluationResult가 APPROVED라면 investigationStatus를 "COMPLETED"(완료됨)로 변경 - if (input.evaluationResult === "APPROVED") { - updateData.investigationStatus = "COMPLETED"; - } - - // 트랜잭션으로 실사 정보 업데이트와 첨부파일 저장을 함께 처리 - await db.transaction(async (tx) => { - // 1. 실사 정보 업데이트 - await tx - .update(vendorInvestigations) - .set(updateData) - .where(eq(vendorInvestigations.id, input.investigationId)); - - // 2. 첨부파일 처리 - if (input.attachments && input.attachments.length > 0) { - for (const file of input.attachments) { - try { - console.log(`📁 실사 첨부파일 처리 중: ${file.name} (${file.size} bytes)`); - - // saveDRMFile을 사용하여 파일 저장 - const saveResult = await saveDRMFile( - file, - decryptWithServerAction, - `vendor-investigation/${input.investigationId}`, - "investigation-update" - ); - - if (!saveResult.success) { - console.error(`❌ 파일 저장 실패: ${file.name}`, saveResult.error); - throw new Error(`파일 저장 실패: ${file.name} - ${saveResult.error}`); - } - - console.log(`✅ 파일 저장 완료: ${file.name} -> ${saveResult.fileName}`); - - // 파일 타입 결정 - let attachmentType = "OTHER"; - if (file.type.includes("pdf")) { - attachmentType = "REPORT"; - } else if (file.type.includes("image")) { - attachmentType = "PHOTO"; - } else if ( - file.type.includes("word") || - file.type.includes("document") || - file.name.toLowerCase().includes("report") - ) { - attachmentType = "DOCUMENT"; - } - - // DB에 첨부파일 레코드 생성 - await tx.insert(vendorInvestigationAttachments).values({ - investigationId: input.investigationId, - fileName: saveResult.fileName!, - originalFileName: file.name, - filePath: saveResult.publicPath!, - fileSize: file.size, - mimeType: file.type || 'application/octet-stream', - attachmentType: attachmentType as "REPORT" | "PHOTO" | "DOCUMENT" | "CERTIFICATE" | "OTHER", - }); - - } catch (error) { - console.error(`❌ 첨부파일 처리 오류: ${file.name}`, error); - throw new Error(`첨부파일 처리 중 오류가 발생했습니다: ${file.name}`); - } - } - } - }); - - revalidateTag("pq-submissions"); - revalidatePath("/evcp/pq_new"); - - return { - success: true, - message: "실사 정보가 성공적으로 업데이트되었습니다." - }; - - } catch (error) { - console.error("실사 정보 업데이트 오류:", error); - const errorMessage = error instanceof Error ? error.message : "실사 정보 업데이트 중 오류가 발생했습니다."; - return { - success: false, - error: errorMessage - }; - } -} - -// 구매자체평가 첨부파일 조회 -export async function getInvestigationAttachments(investigationId: number) { - try { - const attachments = await db - .select({ - id: vendorInvestigationAttachments.id, - fileName: vendorInvestigationAttachments.fileName, - originalFileName: vendorInvestigationAttachments.originalFileName, - filePath: vendorInvestigationAttachments.filePath, - fileSize: vendorInvestigationAttachments.fileSize, - mimeType: vendorInvestigationAttachments.mimeType, - attachmentType: vendorInvestigationAttachments.attachmentType, - createdAt: vendorInvestigationAttachments.createdAt, - }) - .from(vendorInvestigationAttachments) - .where(eq(vendorInvestigationAttachments.investigationId, investigationId)) - .orderBy(desc(vendorInvestigationAttachments.createdAt)); - - return { - success: true, - attachments, - }; - } catch (error) { - console.error("첨부파일 조회 오류:", error); - return { - success: false, - error: "첨부파일 조회 중 오류가 발생했습니다.", - attachments: [], - }; - } -} - -// 구매자체평가 첨부파일 삭제 -export async function deleteInvestigationAttachment(attachmentId: number) { - try { - await db - .delete(vendorInvestigationAttachments) - .where(eq(vendorInvestigationAttachments.id, attachmentId)); - - revalidateTag("pq-submissions"); - revalidatePath("/evcp/pq_new"); - - return { - success: true, - message: "첨부파일이 성공적으로 삭제되었습니다.", - }; - } catch (error) { - console.error("첨부파일 삭제 오류:", error); - return { - success: false, - error: "첨부파일 삭제 중 오류가 발생했습니다.", - }; - } -} - -export async function autoDeactivateExpiredPQLists() { - try { - const now = new Date(); - - // 유효기간이 지난 PQ 리스트들을 비활성화 - const expiredLists = await db - .update(pqLists) - .set({ - isDeleted: true, - updatedAt: now - }) - .where( - and( - eq(pqLists.isDeleted, false), - lt(pqLists.validTo, now), - isNotNull(pqLists.validTo) - ) - ) - .returning(); - - console.log(`[PQ Auto Deactivation] ${expiredLists.length}개의 만료된 PQ 리스트가 비활성화되었습니다.`); - - if (expiredLists.length > 0) { - revalidateTag("pq-lists"); - } - - return { - success: true, - deactivatedCount: expiredLists.length, - message: `${expiredLists.length}개의 만료된 PQ 리스트가 비활성화되었습니다.` - }; - } catch (error) { - console.error("Error auto-deactivating expired PQ lists:", error); - return { - success: false, - message: "만료된 PQ 리스트 자동 비활성화에 실패했습니다." - }; - } -} - -// PQ 유효일 수정 서버액션 -export async function updatePqValidToAction(input: UpdatePqValidToInput) { - try { - const validated = updatePqValidToSchema.parse(input); - const session = await getServerSession(authOptions); - const userId = session?.user?.id; - - if (!userId) { - return { - success: false, - error: "인증이 필요합니다" - }; - } - - // PQ 목록 존재 확인 - const existingPqList = await db - .select() - .from(pqLists) - .where(eq(pqLists.id, validated.pqListId)) - .limit(1) - .then(rows => rows[0]); - - if (!existingPqList) { - return { - success: false, - error: "PQ 목록을 찾을 수 없습니다" - }; - } - - // 유효일 업데이트 - await db - .update(pqLists) - .set({ - validTo: validated.validTo, - updatedAt: new Date(), - updatedBy: userId, - }) - .where(eq(pqLists.id, validated.pqListId)); - - // 캐시 재검증 - revalidateTag("pq-lists"); - - return { - success: true, - message: "유효일이 성공적으로 수정되었습니다" - }; - } catch (error) { - console.error("Error updating PQ valid to:", error); - if (error instanceof z.ZodError) { - return { - success: false, - error: "입력 데이터가 올바르지 않습니다" - }; - } - return { - success: false, - error: "유효일 수정에 실패했습니다" - }; - } -} - - -// SHI 참석자 총 인원수 계산 함수 - -export async function getTotalShiAttendees(shiAttendees: Record | null): Promise { - if (!shiAttendees) return 0 - - let total = 0 - Object.entries(shiAttendees).forEach(([key, value]) => { - if (value && typeof value === 'object' && 'checked' in value && 'count' in value) { - const attendee = value as { checked: boolean; count: number } - if (attendee.checked) { - total += attendee.count - } - } - }) - return total +"use server" + +import db from "@/db/db" +import { CopyPqListInput, CreatePqListInput, UpdatePqValidToInput, copyPqListSchema, createPqListSchema, updatePqValidToSchema, GetPqListsSchema, GetPQSchema, GetPQSubmissionsSchema } from "./validations" +import { unstable_cache } from "@/lib/unstable-cache"; +import { filterColumns } from "@/lib/filter-columns"; +import { getErrorMessage } from "@/lib/handle-error"; +import { asc, desc, ilike, inArray, and, gte, lte, not, or, eq, ne, count,isNull,SQL, sql, lt, gt, isNotNull} from "drizzle-orm"; +import { z } from "zod" +import { revalidateTag, unstable_noStore, revalidatePath} from "next/cache"; +import { format } from "date-fns" +import { pqCriterias, pqCriteriasAttachments, vendorCriteriaAttachments, vendorInvestigations, vendorInvestigationAttachments, vendorPQSubmissions, vendorPqCriteriaAnswers, vendorPqReviewLogs, siteVisitRequests, vendorSiteVisitInfo, siteVisitRequestAttachments } from "@/db/schema/pq" +import { sendEmail } from "../mail/sendEmail"; +import { decryptWithServerAction } from '@/components/drm/drmUtils' + +import { vendorAttachments, vendors } from "@/db/schema/vendors"; +import { vendorRegularRegistrations } from "@/db/schema/vendorRegistrations"; +import { saveFile, saveDRMFile } from "@/lib/file-stroage"; +import { GetVendorsSchema } from "../vendors/validations"; +import { selectVendors } from "../vendors/repository"; +import { projects, users, roles, userRoles, redFlagManagers } from "@/db/schema"; +import { basicContract } from "@/db/schema/basicContractDocumnet"; +import { headers } from 'next/headers'; +import { getServerSession } from "next-auth/next" +import { authOptions } from "@/app/api/auth/[...nextauth]/route" +import { alias } from 'drizzle-orm/pg-core'; +import { createPQFilterMapping, getPQJoinedTables } from "./helper"; +import { pqLists } from "@/db/schema/pq"; + +export interface PQAttachment { + attachId: number + fileName: string + filePath: string + fileSize?: number +} + +export interface PQItem { + answerId: number | null + criteriaId: number + code: string + checkPoint: string + description: string | null + remarks?: string | null + // 프로젝트 PQ 전용 필드 + contractInfo?: string | null + additionalRequirement?: string | null + answer: string + shiComment: string + vendorReply: string + attachments: PQAttachment[] + criteriaAttachments: PQAttachment[] + subGroupName: string + inputFormat: string + type?: string | null + + createdAt: Date | null + updatedAt: Date | null +} + +export interface PQGroupData { + groupName: string + items: PQItem[] +} + +export interface ProjectPQ { + id: number; + projectId: number | null; + status: string; + submittedAt: Date | null; + projectCode: string; + projectName: string; +} + +export async function getPQProjectsByVendorId(vendorId: number): Promise { + const result = await db + .select({ + id: vendorPQSubmissions.id, + projectId: vendorPQSubmissions.projectId, + status: vendorPQSubmissions.status, + submittedAt: vendorPQSubmissions.submittedAt, + projectCode: projects.code, + projectName: projects.name, + }) + .from(vendorPQSubmissions) + .innerJoin( + projects, + eq(vendorPQSubmissions.projectId, projects.id) + ) + .where(eq(vendorPQSubmissions.vendorId, vendorId)) + .orderBy(projects.code); + + return result; +} + +export async function getPqCriteriaAttachments(criteriaId: number) { + unstable_noStore(); + try { + const rows = await db + .select({ + id: pqCriteriasAttachments.id, + fileName: pqCriteriasAttachments.fileName, + originalFileName: pqCriteriasAttachments.originalFileName, + filePath: pqCriteriasAttachments.filePath, + fileSize: pqCriteriasAttachments.fileSize, + }) + .from(pqCriteriasAttachments) + .where(eq(pqCriteriasAttachments.pqCriteriaId, criteriaId)); + + return { success: true, data: rows }; + } catch (error) { + console.error("Error fetching pq criteria attachments:", error); + return { success: false, error: getErrorMessage(error) }; + } +} + +export async function getPQDataByVendorId( + vendorId: number, + projectId?: number, + pqType?: "GENERAL" | "PROJECT" | "NON_INSPECTION" +): Promise { + try { + // 파라미터 유효성 검증 + if (isNaN(vendorId)) { + throw new Error("Invalid vendorId parameter"); + } + + // 타입 결정 로직 + let finalPqType: "GENERAL" | "PROJECT" | "NON_INSPECTION"; + if (pqType) { + finalPqType = pqType; + } else if (projectId) { + finalPqType = "PROJECT"; + } else { + finalPqType = "GENERAL"; + } + + // 기본 쿼리 구성 + const selectObj = { + criteriaId: pqCriterias.id, + groupName: pqCriterias.groupName, + code: pqCriterias.code, + checkPoint: pqCriterias.checkPoint, + description: pqCriterias.description, + remarks: pqCriterias.remarks, + subGroupName: pqCriterias.subGroupName, + + // 입력 형식 필드 추가 + inputFormat: pqCriterias.inputFormat, + type: pqCriterias.type, + // 기준 첨부 + criteriaAttachId: pqCriteriasAttachments.id, + criteriaAttachFileName: pqCriteriasAttachments.fileName, + criteriaAttachFilePath: pqCriteriasAttachments.filePath, + criteriaAttachFileSize: pqCriteriasAttachments.fileSize, + + // 협력업체 응답 필드 + answer: vendorPqCriteriaAnswers.answer, + answerId: vendorPqCriteriaAnswers.id, + + // SHI 코멘트와 벤더 답변 필드 추가 + shiComment: vendorPqCriteriaAnswers.shiComment, + vendorReply: vendorPqCriteriaAnswers.vendorReply, + createdAt: vendorPqCriteriaAnswers.createdAt, + updatedAt: vendorPqCriteriaAnswers.updatedAt, + + // 첨부 파일 필드 + vendorAttachId: vendorCriteriaAttachments.id, + vendorFileName: vendorCriteriaAttachments.fileName, + vendorFilePath: vendorCriteriaAttachments.filePath, + vendorFileSize: vendorCriteriaAttachments.fileSize, + }; + + // 타입별 쿼리 조건 구성 + const pqListConditions = [ + eq(pqCriterias.pqListId, pqLists.id), + eq(pqLists.type, finalPqType), + eq(pqLists.isDeleted, false) + ]; + + const answerConditions = [ + eq(pqCriterias.id, vendorPqCriteriaAnswers.criteriaId), + eq(vendorPqCriteriaAnswers.vendorId, vendorId) + ]; + + // 프로젝트별 조건 추가 + if (finalPqType === "PROJECT" && projectId) { + pqListConditions.push(eq(pqLists.projectId, projectId)); + answerConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId)); + } else if (finalPqType === "GENERAL" || finalPqType === "NON_INSPECTION") { + pqListConditions.push(isNull(pqLists.projectId)); + answerConditions.push(isNull(vendorPqCriteriaAnswers.projectId)); + } + + const rows = await db + .select(selectObj) + .from(pqCriterias) + .innerJoin( + pqLists, + and(...pqListConditions) + ) + .leftJoin( + vendorPqCriteriaAnswers, + and(...answerConditions) + ) + .leftJoin( + vendorCriteriaAttachments, + eq(vendorPqCriteriaAnswers.id, vendorCriteriaAttachments.vendorCriteriaAnswerId) + ) + .leftJoin( + pqCriteriasAttachments, + eq(pqCriteriasAttachments.pqCriteriaId, pqCriterias.id) + ) + .orderBy(pqCriterias.groupName, pqCriterias.code); + + return processQueryResults(rows); + } catch (error) { + console.error("Error fetching PQ data:", error); + return []; + } + + // Helper function to process query results + function processQueryResults(rows: any[]) { + // 그룹별로 데이터 구성 + const groupMap = new Map>(); + + for (const row of rows) { + const g = row.groupName || "Others"; + + // 그룹 확인 + if (!groupMap.has(g)) { + groupMap.set(g, {}); + } + + const groupItems = groupMap.get(g)!; + + // 아직 이 기준을 처리하지 않았으면 PQItem 생성 + if (!groupItems[row.criteriaId]) { + groupItems[row.criteriaId] = { + answerId: row.answerId, + criteriaId: row.criteriaId, + code: row.code, + checkPoint: row.checkPoint, + description: row.description, + remarks: row.remarks, + answer: row.answer || "", + shiComment: row.shiComment || "", + vendorReply: row.vendorReply || "", + attachments: [], + criteriaAttachments: [], + inputFormat: row.inputFormat || "", + type: row.type || null, + subGroupName: row.subGroupName || "", + createdAt: row.createdAt, + updatedAt: row.updatedAt, + }; + } + + // 협력업체 답변 첨부 + if (row.vendorAttachId) { + const exists = groupItems[row.criteriaId].attachments.some( + (a) => a.attachId === row.vendorAttachId + ); + if (!exists) { + groupItems[row.criteriaId].attachments.push({ + attachId: row.vendorAttachId, + fileName: row.vendorFileName || "", + filePath: row.vendorFilePath || "", + fileSize: row.vendorFileSize || undefined, + }); + } + } + + // 기준 첨부 + if (row.criteriaAttachId) { + const existsBase = groupItems[row.criteriaId].criteriaAttachments.some( + (a) => a.attachId === row.criteriaAttachId + ); + if (!existsBase) { + groupItems[row.criteriaId].criteriaAttachments.push({ + attachId: row.criteriaAttachId, + fileName: row.criteriaAttachFileName || "", + filePath: row.criteriaAttachFilePath || "", + fileSize: row.criteriaAttachFileSize || undefined, + }); + } + } + } + + // 최종 데이터 구성 + const data: PQGroupData[] = []; + for (const [groupName, itemsMap] of groupMap.entries()) { + const items = Object.values(itemsMap); + data.push({ groupName, items }); + } + + return data; + } +} + + +interface PQAttachmentInput { + fileName: string // original user-friendly file name + url: string // the UUID-based path stored on server + size?: number // optional file size +} + +interface SavePQAnswer { + criteriaId: number + answer: string + shiComment?: string + vendorReply?: string + attachments: PQAttachmentInput[] +} + +interface SavePQInput { + vendorId: number + projectId?: number + answers: SavePQAnswer[] +} + +/** + * 여러 항목을 한 번에 Upsert + */ +export async function savePQAnswersAction(input: SavePQInput) { + const { vendorId, projectId, answers } = input + + try { + for (const ans of answers) { + // 1) Check if a row already exists for (vendorId, criteriaId, projectId) + const queryConditions = [ + eq(vendorPqCriteriaAnswers.vendorId, vendorId), + eq(vendorPqCriteriaAnswers.criteriaId, ans.criteriaId) + ]; + + // Add projectId condition when it exists + if (projectId !== undefined) { + queryConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId)); + } else { + queryConditions.push(isNull(vendorPqCriteriaAnswers.projectId)); + } + + const existing = await db + .select() + .from(vendorPqCriteriaAnswers) + .where(and(...queryConditions)); + + let answerId: number + + // 2) If it exists, update the row; otherwise insert + if (existing.length === 0) { + // Insert new + const inserted = await db + .insert(vendorPqCriteriaAnswers) + .values({ + vendorId, + criteriaId: ans.criteriaId, + projectId: projectId || null, // Include projectId when provided + answer: ans.answer, + shiComment: ans.shiComment || null, + vendorReply: ans.vendorReply || null, + createdAt: new Date(), + updatedAt: new Date(), + }) + .returning({ id: vendorPqCriteriaAnswers.id }) + + answerId = inserted[0].id + } else { + // Update existing + answerId = existing[0].id + + await db + .update(vendorPqCriteriaAnswers) + .set({ + answer: ans.answer, + shiComment: ans.shiComment || null, + vendorReply: ans.vendorReply || null, + updatedAt: new Date(), + }) + .where(eq(vendorPqCriteriaAnswers.id, answerId)) + } + + // 3) Now manage attachments in vendorCriteriaAttachments + // 3a) Load old attachments from DB + const oldAttachments = await db + .select({ + id: vendorCriteriaAttachments.id, + filePath: vendorCriteriaAttachments.filePath, + }) + .from(vendorCriteriaAttachments) + .where(eq(vendorCriteriaAttachments.vendorCriteriaAnswerId, answerId)) + + // 3b) Gather the new filePaths (urls) from the client + const newPaths = ans.attachments.map(a => a.url) + + // 3c) Find attachments to remove + const toRemove = oldAttachments.filter(old => !newPaths.includes(old.filePath)) + if (toRemove.length > 0) { + const removeIds = toRemove.map(r => r.id) + await db + .delete(vendorCriteriaAttachments) + .where(inArray(vendorCriteriaAttachments.id, removeIds)) + } + + // 3d) Insert new attachments that aren't in DB + const oldPaths = oldAttachments.map(o => o.filePath) + const toAdd = ans.attachments.filter(a => !oldPaths.includes(a.url)) + + for (const attach of toAdd) { + await db.insert(vendorCriteriaAttachments).values({ + vendorCriteriaAnswerId: answerId, + fileName: attach.fileName, + filePath: attach.url, + fileSize: attach.size ?? null, + }) + } + } + + return { ok: true } + } catch (error) { + console.error("savePQAnswersAction error:", error) + return { ok: false, error: String(error) } + } +} + + + +/** + * PQ 제출 서버 액션 - 협력업체 상태를 PQ_SUBMITTED로 업데이트 + * @param vendorId 협력업체 ID + */ +export async function submitPQAction({ + vendorId, + projectId, + pqSubmissionId +}: { + vendorId: number; + projectId?: number; + pqSubmissionId?: number; // 특정 PQ 제출 ID가 있는 경우 사용 +}) { + unstable_noStore(); + + try { + const headersList = await headers(); + const host = headersList.get('host') || 'localhost:3000'; + + // 1. 모든 PQ 항목에 대한 응답이 있는지 검증 + const answerQueryConditions = [ + eq(vendorPqCriteriaAnswers.vendorId, vendorId) + ]; + + // Add projectId condition when it exists + if (projectId !== undefined) { + answerQueryConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId)); + } else { + answerQueryConditions.push(isNull(vendorPqCriteriaAnswers.projectId)); + } + + const pqCriteriaCount = await db + .select({ count: count() }) + .from(vendorPqCriteriaAnswers) + .where(and(...answerQueryConditions)); + + const totalPqCriteriaCount = pqCriteriaCount[0]?.count || 0; + + // 응답 데이터 검증 + if (totalPqCriteriaCount === 0) { + return { ok: false, error: "No PQ answers found" }; + } + + // 2. 협력업체 정보 조회 + const vendor = await db + .select({ + id: vendors.id, + vendorName: vendors.vendorName, + email: vendors.email, + status: vendors.status, + }) + .from(vendors) + .where(eq(vendors.id, vendorId)) + .then(rows => rows[0]); + + if (!vendor) { + return { ok: false, error: "Vendor not found" }; + } + + // Project 정보 조회 (projectId가 있는 경우) + let projectName = ''; + if (projectId) { + const projectData = await db + .select({ + projectName: projects.name + }) + .from(projects) + .where(eq(projects.id, projectId)) + .then(rows => rows[0]); + + projectName = projectData?.projectName || 'Unknown Project'; + } + + // 3. 현재 PQ 제출 상태 확인 및 업데이트 + const currentDate = new Date(); + let existingSubmission; + + // 특정 PQ Submission ID가 있는 경우 + if (pqSubmissionId) { + existingSubmission = await db + .select({ + id: vendorPQSubmissions.id, + status: vendorPQSubmissions.status, + type: vendorPQSubmissions.type, + requesterId: vendorPQSubmissions.requesterId + }) + .from(vendorPQSubmissions) + .where( + and( + eq(vendorPQSubmissions.id, pqSubmissionId), + eq(vendorPQSubmissions.vendorId, vendorId) + ) + ) + .then(rows => rows[0]); + + if (!existingSubmission) { + return { ok: false, error: "PQ submission not found or access denied" }; + } + } + // ID가 없는 경우 vendorId와 projectId로 조회 + else { + const pqType = projectId ? "PROJECT" : "GENERAL"; + + const submissionQueryConditions = [ + eq(vendorPQSubmissions.vendorId, vendorId), + eq(vendorPQSubmissions.type, pqType) + ]; + + if (projectId) { + submissionQueryConditions.push(eq(vendorPQSubmissions.projectId, projectId)); + } else { + submissionQueryConditions.push(isNull(vendorPQSubmissions.projectId)); + } + + existingSubmission = await db + .select({ + id: vendorPQSubmissions.id, + status: vendorPQSubmissions.status, + type: vendorPQSubmissions.type, + requesterId: vendorPQSubmissions.requesterId + }) + .from(vendorPQSubmissions) + .where(and(...submissionQueryConditions)) + .then(rows => rows[0]); + } + + // 제출 가능한 상태 확인 + const allowedStatuses = ["REQUESTED", "IN_PROGRESS", "SUBMITTED", "REJECTED"]; + + if (existingSubmission) { + if (!allowedStatuses.includes(existingSubmission.status)) { + return { + ok: false, + error: `Cannot submit PQ in current status: ${existingSubmission.status}` + }; + } + + // 기존 제출 상태 업데이트 + await db + .update(vendorPQSubmissions) + .set({ + status: "SUBMITTED", + submittedAt: currentDate, + updatedAt: currentDate, + }) + .where(eq(vendorPQSubmissions.id, existingSubmission.id)); + } else { + // PQ Submission ID가 없고 기존 submission도 없는 경우 새로운 제출 생성 + const pqType = projectId ? "PROJECT" : "GENERAL"; + + // PQ 번호 생성 (예: PQ-2024-001) + const currentYear = new Date().getFullYear(); + const pqNumber = `PQ-${currentYear}-${String(vendorId).padStart(3, '0')}`; + + await db + .insert(vendorPQSubmissions) + .values({ + pqNumber, + vendorId, + projectId: projectId || null, + type: pqType, + status: "SUBMITTED", + submittedAt: currentDate, + createdAt: currentDate, + updatedAt: currentDate, + }); + } + + // 4. 일반 PQ인 경우 벤더 상태도 업데이트 + if (!projectId) { + const allowedVendorStatuses = ["IN_PQ", "PENDING_REVIEW", "IN_REVIEW", "REJECTED", "PQ_FAILED"]; + + if (allowedVendorStatuses.includes(vendor.status)) { + await db + .update(vendors) + .set({ + status: "PQ_SUBMITTED", + updatedAt: currentDate, + }) + .where(eq(vendors.id, vendorId)); + } + } + + // 5. PQ 요청자에게 이메일 알림 발송 + const targetSubmissionId = existingSubmission?.id || ''; + const targetRequesterId = existingSubmission?.requesterId || null; + + // QM 담당자 이메일 조회 (해당 PQ와 연결된 실사에 배정된 경우) + let qmManagerEmail: string | null = null; + if (targetSubmissionId) { + try { + const inv = await db + .select({ qmManagerId: vendorInvestigations.qmManagerId }) + .from(vendorInvestigations) + .where(eq(vendorInvestigations.pqSubmissionId, Number(targetSubmissionId))) + .then(rows => rows[0]); + if (inv?.qmManagerId) { + const qmUser = await db + .select({ email: users.email }) + .from(users) + .where(eq(users.id, inv.qmManagerId)) + .then(rows => rows[0]); + qmManagerEmail = qmUser?.email || null; + } + } catch (e) { + console.warn("Failed to fetch QM manager email for PQ submission", e); + } + } + + // HSE 담당자 이메일 조회 (레드플래그/HSE 담당자 설정 기반) + let hseManagerEmail: string | null = null; + try { + const hseManagerRow = await db + .select({ hseManagerId: redFlagManagers.hseManagerId }) + .from(redFlagManagers) + .orderBy(desc(redFlagManagers.createdAt)) + .limit(1); + + const hseManagerId = hseManagerRow[0]?.hseManagerId; + if (hseManagerId) { + const hseUser = await db + .select({ email: users.email }) + .from(users) + .where(eq(users.id, hseManagerId)) + .then(rows => rows[0]); + hseManagerEmail = hseUser?.email || null; + } + } catch (e) { + console.warn("Failed to fetch HSE manager email for PQ submission", e); + } + + if (targetRequesterId !== null) { + try { + // 요청자 정보 조회 + const requester = await db + .select({ + id: users.id, + name: users.name, + email: users.email, + }) + .from(users) + .where(eq(users.id, targetRequesterId)) + .then(rows => rows[0]); + + if (requester?.email) { + const emailSubject = projectId + ? `[eVCP] Project PQ Submitted: ${vendor.vendorName} for ${projectName}` + : `[eVCP] General PQ Submitted: ${vendor.vendorName}`; + + const adminUrl = `http://${host}/evcp/pq/${vendorId}/${targetSubmissionId}`; + + const adminCc: string[] = []; + if (qmManagerEmail) { + adminCc.push(qmManagerEmail); + } + if (hseManagerEmail && !adminCc.includes(hseManagerEmail)) { + adminCc.push(hseManagerEmail); + } + + await sendEmail({ + to: requester.email, + cc: adminCc.length ? adminCc : undefined, + subject: emailSubject, + template: "pq-submitted-admin", + context: { + vendorName: vendor.vendorName, + vendorId: vendor.id, + projectId: projectId, + projectName: projectName, + isProjectPQ: !!projectId, + submittedDate: currentDate.toLocaleString(), + adminUrl, + requesterName: requester.name, + } + }); + } + } catch (emailError) { + console.error("Failed to send requester notification:", emailError); + } + } + + // 6. 벤더에게 확인 이메일 발송 + if (vendor.email) { + try { + const emailSubject = projectId + ? `[eVCP] Project PQ Submission Confirmation for ${projectName}` + : "[eVCP] General PQ Submission Confirmation"; + + const portalUrl = `${host}/partners/pq`; + + await sendEmail({ + to: vendor.email, + subject: emailSubject, + template: "pq-submitted-vendor", + context: { + vendorName: vendor.vendorName, + projectId: projectId, + projectName: projectName, + isProjectPQ: !!projectId, + submittedDate: currentDate.toLocaleString(), + portalUrl, + } + }); + } catch (emailError) { + console.error("Failed to send vendor confirmation:", emailError); + } + } + + // 7. 캐시 무효화 + revalidateTag("vendors"); + revalidateTag("vendor-status-counts"); + revalidateTag(`vendor-pq-submissions-${vendorId}`); + + if (projectId) { + revalidateTag(`project-pq-submissions-${projectId}`); + revalidateTag(`project-vendors-${projectId}`); + revalidateTag(`project-pq-${projectId}`); + } + + return { ok: true }; + } catch (error) { + console.error("PQ submit error:", error); + return { ok: false, error: getErrorMessage(error) }; + } +} + +/** + * 향상된 파일 업로드 서버 액션 + * - 직접 파일 처리 (file 객체로 받음) + * - 디렉토리 자동 생성 + * - 중복 방지를 위한 UUID 적용 + */ +/** + * 벤더용 파일 업로드 액션 (saveFile 사용) + */ +export async function uploadVendorFileAction(file: File, userId?: string) { + unstable_noStore(); + + try { + const result = await saveFile({ + file, + directory: 'pq/vendor', + originalName: file.name, + userId, + }); + + if (!result.success) { + throw new Error(result.error || "파일 업로드에 실패했습니다."); + } + + return { + fileName: result.fileName!, + url: result.publicPath!, + size: result.fileSize!, + }; + } catch (error) { + console.error("Vendor file upload error:", error); + throw new Error(`Upload failed: ${getErrorMessage(error)}`); + } +} + +// PQ 기준 첨부 업로드 (saveFile) +export async function uploadPqCriteriaFileAction(file: File, userId?: string) { + unstable_noStore(); + + try { + const result = await saveFile({ + file, + directory: "pq/criteria", + originalName: file.name, + userId, + }); + + if (!result.success) { + throw new Error(result.error || "파일 업로드에 실패했습니다."); + } + + return { + fileName: result.fileName!, + url: result.publicPath!, + size: result.fileSize!, + originalFileName: file.name, + }; + } catch (error) { + console.error("Pq criteria file upload error:", error); + throw new Error(`Upload failed: ${getErrorMessage(error)}`); + } +} + +/** + * SHI용 파일 업로드 액션 (saveDRMFile 사용) + */ +export async function uploadSHIFileAction(file: File, userId?: string) { + unstable_noStore(); + + try { + const result = await saveDRMFile( + file, + decryptWithServerAction, + 'pq/shi', + userId + ); + + if (!result.success) { + throw new Error(result.error || "파일 업로드에 실패했습니다."); + } + + return { + fileName: result.fileName!, + url: result.publicPath!, + size: result.fileSize!, + }; + } catch (error) { + console.error("SHI file upload error:", error); + throw new Error(`Upload failed: ${getErrorMessage(error)}`); + } +} + +/** + * 벤더용 여러 파일 일괄 업로드 + */ +export async function uploadVendorMultipleFilesAction(files: File[], userId?: string) { + unstable_noStore(); + + try { + const results = []; + + for (const file of files) { + try { + const result = await uploadVendorFileAction(file, userId); + results.push({ + success: true, + ...result + }); + } catch (error) { + results.push({ + success: false, + fileName: file.name, + error: getErrorMessage(error) + }); + } + } + + return { + ok: true, + results + }; + } catch (error) { + console.error("Vendor batch upload error:", error); + return { + ok: false, + error: getErrorMessage(error) + }; + } +} + +/** + * SHI용 여러 파일 일괄 업로드 + */ +export async function uploadSHIMultipleFilesAction(files: File[], userId?: string) { + unstable_noStore(); + + try { + const results = []; + + for (const file of files) { + try { + const result = await uploadSHIFileAction(file, userId); + results.push({ + success: true, + ...result + }); + } catch (error) { + results.push({ + success: false, + fileName: file.name, + error: getErrorMessage(error) + }); + } + } + + return { + ok: true, + results + }; + } catch (error) { + console.error("SHI batch upload error:", error); + return { + ok: false, + error: getErrorMessage(error) + }; + } +} + +// export async function getVendorsInPQ(input: GetVendorsSchema) { +// return unstable_cache( +// async () => { +// try { +// const offset = (input.page - 1) * input.perPage; + +// // 1) 고급 필터 +// const advancedWhere = filterColumns({ +// table: vendors, +// filters: input.filters, +// joinOperator: input.joinOperator, +// }); + +// // 2) 글로벌 검색 +// let globalWhere: SQL | undefined = undefined; +// if (input.search) { +// const s = `%${input.search}%`; +// globalWhere = or( +// ilike(vendors.vendorName, s), +// ilike(vendors.vendorCode, s), +// ilike(vendors.email, s), +// ilike(vendors.status, s) +// ); +// } + +// // 트랜잭션 내에서 데이터 조회 +// const { data, total } = await db.transaction(async (tx) => { +// // 협력업체 ID 모음 (중복 제거용) +// const vendorIds = new Set(); + +// // 1-A) 일반 PQ 답변이 있는 협력업체 찾기 (status와 상관없이) +// const generalPqVendors = await tx +// .select({ +// vendorId: vendorPqCriteriaAnswers.vendorId +// }) +// .from(vendorPqCriteriaAnswers) +// .innerJoin( +// vendors, +// eq(vendorPqCriteriaAnswers.vendorId, vendors.id) +// ) +// .where( +// and( +// isNull(vendorPqCriteriaAnswers.projectId), // 일반 PQ만 (프로젝트 PQ 아님) +// advancedWhere, +// globalWhere +// ) +// ) +// .groupBy(vendorPqCriteriaAnswers.vendorId); // 각 벤더당 한 번만 카운트 + +// generalPqVendors.forEach(v => vendorIds.add(v.vendorId)); + +// // 1-B) 프로젝트 PQ 답변이 있는 협력업체 ID 조회 (status와 상관없이) +// const projectPqVendors = await tx +// .select({ +// vendorId: vendorPQSubmissions.vendorId +// }) +// .from(vendorPQSubmissions) +// .innerJoin( +// vendors, +// eq(vendorPQSubmissions.vendorId, vendors.id) +// ) +// .where( +// and( +// eq(vendorPQSubmissions.type, "PROJECT"), +// // 최소한 IN_PROGRESS부터는 작업이 시작된 상태이므로 포함 +// not(eq(vendorPQSubmissions.status, "REQUESTED")), // REQUESTED 상태는 제외 +// advancedWhere, +// globalWhere +// ) +// ); + +// projectPqVendors.forEach(v => vendorIds.add(v.vendorId)); + +// // 중복 제거된 협력업체 ID 배열 +// const uniqueVendorIds = Array.from(vendorIds); + +// // 총 개수 (중복 제거 후) +// const total = uniqueVendorIds.length; + +// if (total === 0) { +// return { data: [], total: 0 }; +// } + +// // 페이징 처리 (정렬 후 limit/offset 적용) +// const paginatedIds = uniqueVendorIds.slice(offset, offset + input.perPage); + +// // 2) 페이징된 협력업체 상세 정보 조회 +// const vendorsData = await selectVendors(tx, { +// where: inArray(vendors.id, paginatedIds), +// orderBy: input.sort.length > 0 +// ? input.sort.map((item) => +// item.desc ? desc(vendors.vendorName) : asc(vendors.vendorName) +// ) +// : [asc(vendors.createdAt)], +// }); + +// // 3) 각 벤더별 PQ 상태 정보 추가 +// const vendorsWithPqInfo = await Promise.all( +// vendorsData.map(async (vendor) => { +// // 3-A) 첨부 파일 조회 +// const attachments = await tx +// .select({ +// id: vendorAttachments.id, +// fileName: vendorAttachments.fileName, +// filePath: vendorAttachments.filePath, +// }) +// .from(vendorAttachments) +// .where(eq(vendorAttachments.vendorId, vendor.id)); + +// // 3-B) 일반 PQ 제출 여부 확인 (PQ 답변이 있는지) +// const generalPqAnswers = await tx +// .select({ count: count() }) +// .from(vendorPqCriteriaAnswers) +// .where( +// and( +// eq(vendorPqCriteriaAnswers.vendorId, vendor.id), +// isNull(vendorPqCriteriaAnswers.projectId) +// ) +// ); + +// const hasGeneralPq = generalPqAnswers[0]?.count > 0; + +// // 3-C) 프로젝트 PQ 정보 조회 (모든 상태 포함) +// const projectPqs = await tx +// .select({ +// projectId: vendorPQSubmissions.projectId, +// projectName: projects.name, +// status: vendorPQSubmissions.status, +// submittedAt: vendorPQSubmissions.submittedAt, +// approvedAt: vendorPQSubmissions.approvedAt, +// rejectedAt: vendorPQSubmissions.rejectedAt +// }) +// .from(vendorPQSubmissions) +// .innerJoin( +// projects, +// eq(vendorPQSubmissions.projectId, projects.id) +// ) +// .where( +// and( +// eq(vendorPQSubmissions.vendorId, vendor.id), +// eq(vendorPQSubmissions.type, "PROJECT"), +// not(eq(vendorPQSubmissions.status, "REQUESTED")) // REQUESTED 상태는 제외 +// ) +// ); + +// const hasProjectPq = projectPqs.length > 0; + +// // 프로젝트 PQ 상태별 카운트 +// const projectPqStatusCounts = { +// inProgress: projectPqs.filter(p => p.status === "IN_PROGRESS").length, +// submitted: projectPqs.filter(p => p.status === "SUBMITTED").length, +// approved: projectPqs.filter(p => p.status === "APPROVED").length, +// rejected: projectPqs.filter(p => p.status === "REJECTED").length, +// total: projectPqs.length +// }; + +// // 3-D) PQ 상태 정보 추가 +// return { +// ...vendor, +// hasAttachments: attachments.length > 0, +// attachmentsList: attachments, +// pqInfo: { +// hasGeneralPq, +// hasProjectPq, +// projectPqs, +// projectPqStatusCounts, +// // 현재 PQ 상태 (UI에 표시 용도) +// pqStatus: getPqStatusDisplay(vendor.status, hasGeneralPq, hasProjectPq, projectPqStatusCounts) +// } +// }; +// }) +// ); + +// return { data: vendorsWithPqInfo, total }; +// }); + +// // 페이지 수 +// const pageCount = Math.ceil(total / input.perPage); + +// return { data, pageCount }; +// } catch (err) { +// console.error("Error in getVendorsInPQ:", err); +// // 에러 발생 시 +// return { data: [], pageCount: 0 }; +// } +// }, +// [JSON.stringify(input)], // 캐싱 키 +// { +// revalidate: 3600, +// tags: ["vendors-in-pq", "project-pqs"], // revalidateTag 호출 시 무효화 +// } +// )(); +// } + +// PQ 상태 표시 함수 +function getPqStatusDisplay( + vendorStatus: string, + hasGeneralPq: boolean, + hasProjectPq: boolean, + projectPqCounts: { inProgress: number, submitted: number, approved: number, rejected: number, total: number } +): string { + // 프로젝트 PQ 상태 문자열 생성 + let projectPqStatus = ""; + if (hasProjectPq) { + const parts = []; + if (projectPqCounts.inProgress > 0) { + parts.push(`진행중: ${projectPqCounts.inProgress}`); + } + if (projectPqCounts.submitted > 0) { + parts.push(`제출: ${projectPqCounts.submitted}`); + } + if (projectPqCounts.approved > 0) { + parts.push(`승인: ${projectPqCounts.approved}`); + } + if (projectPqCounts.rejected > 0) { + parts.push(`거부: ${projectPqCounts.rejected}`); + } + projectPqStatus = parts.join(", "); + } + + // 일반 PQ + 프로젝트 PQ 조합 상태 + if (hasGeneralPq && hasProjectPq) { + return `일반 PQ (${getPqVendorStatusText(vendorStatus)}) + 프로젝트 PQ (${projectPqStatus})`; + } else if (hasGeneralPq) { + return `일반 PQ (${getPqVendorStatusText(vendorStatus)})`; + } else if (hasProjectPq) { + return `프로젝트 PQ (${projectPqStatus})`; + } + + return "PQ 정보 없음"; +} + +// 협력업체 상태 텍스트 변환 +function getPqVendorStatusText(status: string): string { + switch (status) { + case "IN_PQ": return "진행중"; + case "PQ_SUBMITTED": return "제출됨"; + case "PQ_FAILED": return "실패"; + case "PQ_APPROVED": + case "APPROVED": return "승인됨"; + case "READY_TO_SEND": return "거래 준비"; + case "ACTIVE": return "활성"; + case "INACTIVE": return "비활성"; + case "BLACKLISTED": return "거래금지"; + default: return status; + } +} + + +export type VendorStatus = + | "PENDING_REVIEW" + | "IN_REVIEW" + | "REJECTED" + | "IN_PQ" + | "PQ_SUBMITTED" + | "PQ_FAILED" + | "APPROVED" + | "ACTIVE" + | "INACTIVE" + | "BLACKLISTED" + | "PQ_APPROVED" + + export async function updateVendorStatusAction( + vendorId: number, + newStatus: VendorStatus + ) { + try { + // 1) Update DB + await db.update(vendors) + .set({ status: newStatus }) + .where(eq(vendors.id, vendorId)) + + // 2) Load vendor's email & name + const vendor = await db.select().from(vendors).where(eq(vendors.id, vendorId)).then(r => r[0]) + if (!vendor) { + return { ok: false, error: "Vendor not found" } + } + const headersList = await headers(); + const host = headersList.get('host') || 'localhost:3000'; + const loginUrl = `http://${host}/partners/pq` + + // 3) Send email + await sendEmail({ + to: vendor.email || "", + subject: `Your PQ Status is now ${newStatus}`, + template: "vendor-pq-status", // matches .hbs file + context: { + name: vendor.vendorName, + status: newStatus, + loginUrl: loginUrl, // etc. + }, + }) + revalidateTag("vendors") + revalidateTag("vendors-in-pq") + return { ok: true } + } catch (error) { + console.error("updateVendorStatusAction error:", error) + return { ok: false, error: String(error) } + } + } + + type ProjectPQStatus = "REQUESTED" | "IN_PROGRESS" | "SUBMITTED" | "APPROVED" | "REJECTED"; + +/** + * Update the status of a project-specific PQ for a vendor + */ +export async function updateProjectPQStatusAction({ + vendorId, + projectId, + status, + comment +}: { + vendorId: number; + projectId: number; + status: ProjectPQStatus; + comment?: string; +}) { + try { + const currentDate = new Date(); + + // 1) Prepare update data with appropriate timestamps + const updateData: any = { + status, + updatedAt: currentDate, + }; + + // Add status-specific fields + if (status === "APPROVED") { + updateData.approvedAt = currentDate; + } else if (status === "REJECTED") { + updateData.rejectedAt = currentDate; + updateData.rejectReason = comment || null; + } else if (status === "SUBMITTED") { + updateData.submittedAt = currentDate; + } + + // 2) Update the project PQ record + await db + .update(vendorPQSubmissions) + .set(updateData) + .where( + and( + eq(vendorPQSubmissions.vendorId, vendorId), + eq(vendorPQSubmissions.projectId, projectId), + eq(vendorPQSubmissions.type, "PROJECT") + ) + ); + + // 3) Load vendor and project details for email + const vendor = await db + .select({ + id: vendors.id, + email: vendors.email, + vendorName: vendors.vendorName + }) + .from(vendors) + .where(eq(vendors.id, vendorId)) + .then(rows => rows[0]); + + if (!vendor) { + return { ok: false, error: "Vendor not found" }; + } + + const project = await db + .select({ + name: projects.name + }) + .from(projects) + .where(eq(projects.id, projectId)) + .then(rows => rows[0]); + + if (!project) { + return { ok: false, error: "Project not found" }; + } + + // 4) Send email notification + await sendEmail({ + to: vendor.email || "", + subject: `Your Project PQ for ${project.name} is now ${status}`, + template: "vendor-project-pq-status", // matches .hbs file (you might need to create this) + context: { + name: vendor.vendorName, + status, + projectName: project.name, + rejectionReason: status === "REJECTED" ? comment : undefined, + hasRejectionReason: status === "REJECTED" && !!comment, + loginUrl: `${process.env.NEXT_PUBLIC_URL}/partners/pq?projectId=${projectId}`, + approvalDate: status === "APPROVED" ? currentDate.toLocaleDateString() : undefined, + rejectionDate: status === "REJECTED" ? currentDate.toLocaleDateString() : undefined, + }, + }); + + // 5) Revalidate cache tags + revalidateTag("vendors"); + revalidateTag("vendors-in-pq"); + revalidateTag(`vendor-project-pqs-${vendorId}`); + revalidateTag(`project-pq-${projectId}`); + revalidateTag(`project-vendors-${projectId}`); + + return { ok: true }; + } catch (error) { + console.error("updateProjectPQStatusAction error:", error); + return { ok: false, error: String(error) }; + } +} + +// 코멘트 타입 정의 +interface ItemComment { + answerId: number; + checkPoint: string; // 체크포인트 정보 추가 + code: string; // 코드 정보 추가 + comment: string; +} + +/** + * PQ 변경 요청 처리 서버 액션 + * + * @param vendorId 협력업체 ID + * @param comment 항목별 코멘트 배열 (answerId, checkPoint, code, comment로 구성) + * @param generalComment 전체 PQ에 대한 일반 코멘트 (선택사항) + */ +export async function requestPqChangesAction({ + vendorId, + projectId, + comment, + generalComment, + reviewerName +}: { + vendorId: number; + projectId?: number; // Optional project ID for project-specific PQs + comment: ItemComment[]; + generalComment?: string; + reviewerName?: string; +}) { + try { + // 1) 상태 업데이트 (PQ 타입에 따라 다르게 처리) + if (projectId) { + // 프로젝트 PQ인 경우 vendorPQSubmissions 테이블 업데이트 + const projectPq = await db + .select() + .from(vendorPQSubmissions) + .where( + and( + eq(vendorPQSubmissions.vendorId, vendorId), + eq(vendorPQSubmissions.projectId, projectId), + eq(vendorPQSubmissions.type, "PROJECT") + ) + ) + .then(rows => rows[0]); + + if (!projectPq) { + return { ok: false, error: "Project PQ record not found" }; + } + + await db + .update(vendorPQSubmissions) + .set({ + status: "IN_PROGRESS", // 변경 요청 상태로 설정 + updatedAt: new Date(), + }) + .where( + and( + eq(vendorPQSubmissions.vendorId, vendorId), + eq(vendorPQSubmissions.projectId, projectId), + eq(vendorPQSubmissions.type, "PROJECT") + ) + ); + } else { + // 일반 PQ인 경우 vendors 테이블 업데이트 + await db + .update(vendors) + .set({ + status: "IN_PQ", // 변경 요청 상태로 설정 + updatedAt: new Date(), + }) + .where(eq(vendors.id, vendorId)); + } + + // 2) 협력업체 정보 가져오기 + const vendor = await db + .select() + .from(vendors) + .where(eq(vendors.id, vendorId)) + .then(r => r[0]); + + if (!vendor) { + return { ok: false, error: "Vendor not found" }; + } + + // 프로젝트 정보 가져오기 (프로젝트 PQ인 경우) + let projectName = ""; + if (projectId) { + const project = await db + .select({ + name: projects.name + }) + .from(projects) + .where(eq(projects.id, projectId)) + .then(rows => rows[0]); + + projectName = project?.name || "Unknown Project"; + } + + // 3) 각 항목별 코멘트 저장 + const currentDate = new Date(); + + + // 병렬로 모든 코멘트 저장 + if (comment && comment.length > 0) { + const insertPromises = comment.map(item => + db.insert(vendorPqReviewLogs) + .values({ + vendorPqCriteriaAnswerId: item.answerId, + // reviewerId: reviewerId, + reviewerName: reviewerName, + reviewerComment: item.comment, + createdAt: currentDate, + // 추가 메타데이터 필드가 있다면 저장 + // 이런 메타데이터는 DB 스키마에 해당 필드가 있어야 함 + // meta: JSON.stringify({ checkPoint: item.checkPoint, code: item.code }) + }) + ); + + // 모든 삽입 기다리기 + await Promise.all(insertPromises); + } + + // 4) 변경 요청 이메일 보내기 + // 코멘트 목록 준비 + const commentItems = comment.map(item => ({ + id: item.answerId, + code: item.code, + checkPoint: item.checkPoint, + text: item.comment + })); + + // PQ 유형에 따라 이메일 제목 및 내용 조정 + const emailSubject = projectId + ? `[IMPORTANT] Your Project PQ (${projectName}) requires changes` + : `[IMPORTANT] Your PQ submission requires changes`; + + // 로그인 URL - 프로젝트 PQ인 경우 다른 경로로 안내 + const loginUrl = projectId + ? `${process.env.NEXT_PUBLIC_URL}/partners/pq?projectId=${projectId}` + : `${process.env.NEXT_PUBLIC_URL}/partners/pq`; + + await sendEmail({ + to: vendor.email || "", + subject: emailSubject, + template: "vendor-pq-comment", // matches .hbs file + context: { + name: vendor.vendorName, + vendorCode: vendor.vendorCode, + loginUrl, + comments: commentItems, + generalComment: generalComment || "", + hasGeneralComment: !!generalComment, + commentCount: commentItems.length, + projectId, + projectName, + isProjPQ: !!projectId, + }, + }); + + // 5) 캐시 무효화 - PQ 유형에 따라 적절한 태그 무효화 + revalidateTag("vendors"); + revalidateTag("vendors-in-pq"); + + if (projectId) { + revalidateTag(`vendor-project-pqs-${vendorId}`); + revalidateTag(`project-pq-${projectId}`); + revalidateTag(`project-vendors-${projectId}`); + } + + return { ok: true }; + } catch (error) { + console.error("requestPqChangesAction error:", error); + return { ok: false, error: String(error) }; + } +} + +interface AddReviewCommentInput { + answerId: number // vendorPqCriteriaAnswers.id + comment: string + reviewerName?: string +} + +export async function addReviewCommentAction(input: AddReviewCommentInput) { + try { + // 1) Check that the answer row actually exists + const existing = await db + .select({ id: vendorPqCriteriaAnswers.id }) + .from(vendorPqCriteriaAnswers) + .where(eq(vendorPqCriteriaAnswers.id, input.answerId)) + + if (existing.length === 0) { + return { ok: false, error: "Item not found" } + } + + // 2) Insert the log + await db.insert(vendorPqReviewLogs).values({ + vendorPqCriteriaAnswerId: input.answerId, + reviewerComment: input.comment, + reviewerName: input.reviewerName ?? "AdminUser", + }) + + return { ok: true } + } catch (error) { + console.error("addReviewCommentAction error:", error) + return { ok: false, error: String(error) } + } +} + +interface GetItemReviewLogsInput { + answerId: number +} + +export async function getItemReviewLogsAction(input: GetItemReviewLogsInput) { + try { + + const logs = await db + .select() + .from(vendorPqReviewLogs) + .where(eq(vendorPqReviewLogs.vendorPqCriteriaAnswerId, input.answerId)) + .orderBy(desc(vendorPqReviewLogs.createdAt)); + + return { ok: true, data: logs }; + } catch (error) { + console.error("getItemReviewLogsAction error:", error); + return { ok: false, error: String(error) }; + } +} + +export interface VendorPQListItem { + projectId: number; + projectName: string; + status: string; + submittedAt?: Date | null; // Change to accept both undefined and null +} + +export interface VendorPQsList { + hasGeneralPq: boolean; + generalPqStatus?: string; // vendor.status for general PQ + projectPQs: VendorPQListItem[]; +} + +export async function getVendorPQsList(vendorId: number): Promise { + unstable_noStore(); + + try { + // 1. Check if vendor has general PQ answers + const generalPqAnswers = await db + .select({ count: count() }) + .from(vendorPqCriteriaAnswers) + .where( + and( + eq(vendorPqCriteriaAnswers.vendorId, vendorId), + isNull(vendorPqCriteriaAnswers.projectId) + ) + ); + + const hasGeneralPq = (generalPqAnswers[0]?.count || 0) > 0; + + // 2. Get vendor status for general PQ + let generalPqStatus; + if (hasGeneralPq) { + const vendor = await db + .select({ status: vendors.status }) + .from(vendors) + .where(eq(vendors.id, vendorId)) + .then(rows => rows[0]); + + generalPqStatus = vendor?.status; + } + + // 3. Get project PQs + const projectPQs = await db + .select({ + projectId: vendorPQSubmissions.projectId, + projectName: projects.name, + status: vendorPQSubmissions.status, + submittedAt: vendorPQSubmissions.submittedAt + }) + .from(vendorPQSubmissions) + .innerJoin( + projects, + eq(vendorPQSubmissions.projectId, projects.id) + ) + .where( + and( + eq(vendorPQSubmissions.vendorId, vendorId), + eq(vendorPQSubmissions.type, "PROJECT"), + not(eq(vendorPQSubmissions.status, "REQUESTED")) // Exclude requests that haven't been started + ) + ) + .orderBy(vendorPQSubmissions.updatedAt); + + return { + hasGeneralPq, + generalPqStatus, + projectPQs: projectPQs + }; + + } catch (error) { + console.error("Error fetching vendor PQs list:", error); + return { + hasGeneralPq: false, + projectPQs: [] + }; + } +} + + +export async function loadGeneralPQData(vendorId: number) { + "use server"; + return getPQDataByVendorId(vendorId) +} + +export async function loadProjectPQData(vendorId: number, projectId: number) { + "use server"; + return getPQDataByVendorId(vendorId, projectId) +} + +export async function loadGeneralPQAction(vendorId: number) { + return getPQDataByVendorId(vendorId); +} + +export async function loadProjectPQAction(vendorId: number, projectId?: number): Promise { + if (!projectId) { + throw new Error("Project ID is required for loading project PQ data"); + } + return getPQDataByVendorId(vendorId, projectId); +} + + + +export async function getAllPQsByVendorId(vendorId: number) { + unstable_noStore(); + + try { + const pqList = await db + .select({ + id: vendorPQSubmissions.id, + type: vendorPQSubmissions.type, + status: vendorPQSubmissions.status, + pqNumber: vendorPQSubmissions.pqNumber, + projectId: vendorPQSubmissions.projectId, + projectName: projects.name, + createdAt: vendorPQSubmissions.createdAt, + updatedAt: vendorPQSubmissions.updatedAt, + submittedAt: vendorPQSubmissions.submittedAt, + approvedAt: vendorPQSubmissions.approvedAt, + rejectedAt: vendorPQSubmissions.rejectedAt, + rejectReason: vendorPQSubmissions.rejectReason, + pqItems: vendorPQSubmissions.pqItems, + }) + .from(vendorPQSubmissions) + .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id)) + .where(eq(vendorPQSubmissions.vendorId, vendorId)) + .orderBy(desc(vendorPQSubmissions.createdAt)); + + return pqList; + } catch (error) { + console.error("Error fetching PQ list:", error); + return []; + } +} + +// 특정 PQ의 상세 정보 조회 (개별 PQ 페이지용) +export async function getPQById(pqSubmissionId: number, vendorId: number) { + unstable_noStore(); + + try { + + const pq = await db + .select({ + id: vendorPQSubmissions.id, + vendorId: vendorPQSubmissions.vendorId, + projectId: vendorPQSubmissions.projectId, + type: vendorPQSubmissions.type, + status: vendorPQSubmissions.status, + createdAt: vendorPQSubmissions.createdAt, + submittedAt: vendorPQSubmissions.submittedAt, + approvedAt: vendorPQSubmissions.approvedAt, + rejectedAt: vendorPQSubmissions.rejectedAt, + rejectReason: vendorPQSubmissions.rejectReason, + pqItems: vendorPQSubmissions.pqItems, + + // 벤더 정보 (추가) + vendorName: vendors.vendorName, + vendorCode: vendors.vendorCode, + vendorStatus: vendors.status, + vendorCountry: vendors.country, + vendorEmail: vendors.email, + vendorPhone: vendors.phone, + + // 프로젝트 정보 (조인) + projectName: projects.name, + projectCode: projects.code, + }) + .from(vendorPQSubmissions) + .leftJoin(vendors, eq(vendorPQSubmissions.vendorId, vendors.id)) + .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id)) + .where( + and( + eq(vendorPQSubmissions.id, pqSubmissionId), + eq(vendorPQSubmissions.vendorId, vendorId) + ) + ) + .limit(1) + .then(rows => rows[0]); + + if (!pq) { + throw new Error("PQ not found or access denied"); + } + + return pq; + } catch (error) { + console.error("Error fetching PQ by ID:", error); + throw error; + } +} + +export async function getPQStatusCounts(vendorId: number) { + try { + // 모든 PQ 상태 조회 (일반 PQ + 프로젝트 PQ) + const pqStatuses = await db + .select({ + status: vendorPQSubmissions.status, + count: count(), + }) + .from(vendorPQSubmissions) + .where(eq(vendorPQSubmissions.vendorId, vendorId)) + .groupBy(vendorPQSubmissions.status); + + // 상태별 개수를 객체로 변환 + const statusCounts = { + REQUESTED: 0, + IN_PROGRESS: 0, + SUBMITTED: 0, + APPROVED: 0, + REJECTED: 0, + }; + + // 조회된 결과를 statusCounts 객체에 매핑 + pqStatuses.forEach((item) => { + if (item.status in statusCounts) { + statusCounts[item.status as keyof typeof statusCounts] = item.count; + } + }); + + return statusCounts; + } catch (error) { + console.error("Error fetching PQ status counts:", error); + return { + REQUESTED: 0, + IN_PROGRESS: 0, + SUBMITTED: 0, + APPROVED: 0, + REJECTED: 0, + }; + } +} + +// 상태 레이블 함수 +function getStatusLabel(status: string): string { + switch (status) { + case "REQUESTED": + return "요청됨"; + case "IN_PROGRESS": + return "진행 중"; + case "SUBMITTED": + return "제출됨"; + case "APPROVED": + return "승인됨"; + case "REJECTED": + return "거부됨"; + default: + return status; + } +} + +export async function getPQSubmissions(input: GetPQSubmissionsSchema) { + unstable_noStore(); + + try { + const offset = (input.page - 1) * input.perPage; + + const pqFilterMapping = createPQFilterMapping(); + const joinedTables = getPQJoinedTables(); + + console.log(input, "input") + + // 1) 고급 필터 조건 (DataTableAdvancedToolbar에서) + let advancedWhere: SQL | undefined = undefined; + if (input.filters && input.filters.length > 0) { + advancedWhere = filterColumns({ + table: vendorPQSubmissions, + filters: input.filters, + joinOperator: input.joinOperator || 'and', + joinedTables, + customColumnMapping: pqFilterMapping, + }); + console.log("advancedWhere:", advancedWhere); + } + + // 2) 기본 필터 조건 (PQFilterSheet에서) + let basicWhere: SQL | undefined = undefined; + if (input.basicFilters && input.basicFilters.length > 0) { + basicWhere = filterColumns({ + table: vendorPQSubmissions, + filters: input.basicFilters, + joinOperator: input.basicJoinOperator || 'and', + joinedTables, + customColumnMapping: pqFilterMapping, + }); + console.log("basicWhere:", basicWhere); + } + + // 3) 글로벌 검색 조건 + let globalWhere: SQL | undefined = undefined; + if (input.search) { + const s = `%${input.search}%`; + + const validSearchConditions: SQL[] = []; + + // 기존 검색 조건들 + const nameCondition = ilike(vendors.vendorName, s); + if (nameCondition) validSearchConditions.push(nameCondition); + + const codeCondition = ilike(vendors.vendorCode, s); + if (codeCondition) validSearchConditions.push(codeCondition); + + const projectNameCondition = ilike(projects.name, s); + if (projectNameCondition) validSearchConditions.push(projectNameCondition); + + const projectCodeCondition = ilike(projects.code, s); + if (projectCodeCondition) validSearchConditions.push(projectCodeCondition); + + // 새로 추가된 검색 조건들 + const pqNumberCondition = ilike(vendorPQSubmissions.pqNumber, s); + if (pqNumberCondition) validSearchConditions.push(pqNumberCondition); + + const requesterCondition = ilike(users.name, s); + if (requesterCondition) validSearchConditions.push(requesterCondition); + + if (validSearchConditions.length > 0) { + globalWhere = or(...validSearchConditions); + } + } + + // 4) 날짜 조건 + let fromDateWhere: SQL | undefined = undefined; + let toDateWhere: SQL | undefined = undefined; + + if (input.submittedDateFrom) { + const fromDate = new Date(input.submittedDateFrom); + const condition = gte(vendorPQSubmissions.submittedAt, fromDate); + if (condition) fromDateWhere = condition; + } + + if (input.submittedDateTo) { + const toDate = new Date(input.submittedDateTo); + const condition = lte(vendorPQSubmissions.submittedAt, toDate); + if (condition) toDateWhere = condition; + } + + // 5) 최종 WHERE 조건 생성 - 각 그룹을 AND로 연결 + const whereConditions: SQL[] = []; + + // 고급 필터 조건 추가 + if (advancedWhere) whereConditions.push(advancedWhere); + + // 기본 필터 조건 추가 + if (basicWhere) whereConditions.push(basicWhere); + + // 기타 조건들 추가 + if (globalWhere) whereConditions.push(globalWhere); + if (fromDateWhere) whereConditions.push(fromDateWhere); + if (toDateWhere) whereConditions.push(toDateWhere); + + // 모든 조건을 AND로 연결 + const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined; + + console.log("Final WHERE conditions:", { + advancedWhere: !!advancedWhere, + basicWhere: !!basicWhere, + globalWhere: !!globalWhere, + dateConditions: !!(fromDateWhere || toDateWhere), + totalConditions: whereConditions.length + }); + + // 6) 전체 데이터 수 조회 + const totalResult = await db + .select({ count: count() }) + .from(vendorPQSubmissions) + .leftJoin(vendors, eq(vendorPQSubmissions.vendorId, vendors.id)) + .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id)) + .leftJoin(users, eq(vendorPQSubmissions.requesterId, users.id)) + .leftJoin(vendorInvestigations, eq(vendorInvestigations.pqSubmissionId, vendorPQSubmissions.id)) + .where(finalWhere); + + const total = totalResult[0]?.count || 0; + + if (total === 0) { + return { data: [], pageCount: 0 }; + } + + // 7) 정렬 및 페이징 처리된 데이터 조회 + const orderByColumns = input.sort.map((sort) => { + const column = sort.id; + + // JOIN된 테이블의 컬럼인 경우 적절한 테이블 참조 + if (column === 'vendorName') { + return sort.desc ? desc(vendors.vendorName) : asc(vendors.vendorName); + } else if (column === 'projectName') { + return sort.desc ? desc(projects.name) : asc(projects.name); + } else if (column === 'requesterName') { + return sort.desc ? desc(users.name) : asc(users.name); + } else { + // vendorPQSubmissions 테이블의 컬럼인 경우 + const dbColumn = column as keyof typeof vendorPQSubmissions.$inferSelect; + return sort.desc ? desc(vendorPQSubmissions[dbColumn]) : asc(vendorPQSubmissions[dbColumn]); + } + }); + + if (orderByColumns.length === 0) { + orderByColumns.push(desc(vendorPQSubmissions.updatedAt)); + } + + const pqSubmissions = await db + .select({ + id: vendorPQSubmissions.id, + type: vendorPQSubmissions.type, + pqNumber: vendorPQSubmissions.pqNumber, + requesterId: vendorPQSubmissions.requesterId, + requesterName: users.name, + status: vendorPQSubmissions.status, + createdAt: vendorPQSubmissions.createdAt, + updatedAt: vendorPQSubmissions.updatedAt, + submittedAt: vendorPQSubmissions.submittedAt, + approvedAt: vendorPQSubmissions.approvedAt, + rejectedAt: vendorPQSubmissions.rejectedAt, + rejectReason: vendorPQSubmissions.rejectReason, + pqItems: vendorPQSubmissions.pqItems, + // Vendor 정보 + vendorId: vendors.id, + vendorName: vendors.vendorName, + vendorCode: vendors.vendorCode, + email: vendors.email, + taxId: vendors.taxId, + vendorStatus: vendors.status, + // Project 정보 (프로젝트 PQ인 경우) + projectId: projects.id, + projectName: projects.name, + projectCode: projects.code, + }) + .from(vendorPQSubmissions) + .leftJoin(vendors, eq(vendorPQSubmissions.vendorId, vendors.id)) + .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id)) + .leftJoin(users, eq(vendorPQSubmissions.requesterId, users.id)) + .where(finalWhere) + .orderBy(...orderByColumns) + .limit(input.perPage) + .offset(offset); + + // 8) 각 PQ 제출에 대한 추가 정보 조회 (기존과 동일) + const pqSubmissionsWithDetails = await Promise.all( + pqSubmissions.map(async (submission) => { + // 기본 반환 객체 + const baseResult = { + ...submission, + answerCount: 0, + attachmentCount: 0, + pqStatus: getStatusLabel(submission.status), + pqTypeLabel: submission.type === "GENERAL" ? "일반 PQ" : "프로젝트 PQ", + }; + + // vendorId가 null이면 기본 정보만 반환 + if (submission.vendorId === null) { + return baseResult; + } + + try { + // 답변 수 조회 + const vendorId = submission.vendorId; + + const answerWhereConditions: SQL[] = []; + + const vendorCondition = eq(vendorPqCriteriaAnswers.vendorId, vendorId); + if (vendorCondition) answerWhereConditions.push(vendorCondition); + + let projectCondition: SQL | undefined; + if (submission.projectId !== null) { + projectCondition = eq(vendorPqCriteriaAnswers.projectId, submission.projectId); + } else { + projectCondition = isNull(vendorPqCriteriaAnswers.projectId); + } + + if (projectCondition) answerWhereConditions.push(projectCondition); + + const answerWhere = and(...answerWhereConditions); + + const answersResult = await db + .select({ count: count() }) + .from(vendorPqCriteriaAnswers) + .where(answerWhere); + + const answerCount = answersResult[0]?.count || 0; + + // 첨부 파일 수 조회 + const attachmentsResult = await db + .select({ count: count() }) + .from(vendorPqCriteriaAnswers) + .leftJoin( + vendorCriteriaAttachments, + eq(vendorCriteriaAttachments.vendorCriteriaAnswerId, vendorPqCriteriaAnswers.id) + ) + .where(answerWhere); + + const attachmentCount = attachmentsResult[0]?.count || 0; + + const requesters = alias(users, 'requesters'); + const qmManagers = alias(users, 'qmManagers'); + + const investigationResult = await db + .select({ + id: vendorInvestigations.id, + investigationStatus: vendorInvestigations.investigationStatus, + investigationAddress: vendorInvestigations.investigationAddress, + investigationMethod: vendorInvestigations.investigationMethod, + scheduledStartAt: vendorInvestigations.scheduledStartAt, + scheduledEndAt: vendorInvestigations.scheduledEndAt, + requestedAt: vendorInvestigations.requestedAt, + confirmedAt: vendorInvestigations.confirmedAt, + completedAt: vendorInvestigations.completedAt, + forecastedAt: vendorInvestigations.forecastedAt, + evaluationScore: vendorInvestigations.evaluationScore, + hasSupplementRequested: vendorInvestigations.hasSupplementRequested, + evaluationResult: vendorInvestigations.evaluationResult, + investigationNotes: vendorInvestigations.investigationNotes, + requesterId: vendorInvestigations.requesterId, + requesterName: requesters.name, + qmManagerId: vendorInvestigations.qmManagerId, + qmManagerName: qmManagers.name, + qmManagerEmail: qmManagers.email, + }) + .from(vendorInvestigations) + .leftJoin(requesters, eq(vendorInvestigations.requesterId, requesters.id)) + .leftJoin(qmManagers, eq(vendorInvestigations.qmManagerId, qmManagers.id)) + .where(and( + eq(vendorInvestigations.vendorId, submission.vendorId), + eq(vendorInvestigations.pqSubmissionId, submission.id) + )) + .orderBy(desc(vendorInvestigations.createdAt)) + .limit(1); + + const investigation = investigationResult[0] || null; + + // investigation이 있으면 해당 investigation의 최신 siteVisitRequest 조회 + let siteVisitRequestId: number | null = null; + if (investigation) { + const siteVisitRequestResult = await db + .select({ id: siteVisitRequests.id }) + .from(siteVisitRequests) + .where(eq(siteVisitRequests.investigationId, investigation.id)) + .orderBy(desc(siteVisitRequests.createdAt)) + .limit(1); + + siteVisitRequestId = siteVisitRequestResult[0]?.id || null; + } + + return { + ...baseResult, + answerCount, + attachmentCount, + siteVisitRequestId, + investigation + }; + } catch (error) { + console.error("Error fetching PQ details:", error); + return baseResult; + } + }) + ); + + const pageCount = Math.ceil(total / input.perPage); + + return { data: pqSubmissionsWithDetails, pageCount }; + } catch (err) { + console.error("Error in getPQSubmissions:", err); + return { data: [], pageCount: 0 }; + } +} + +export async function getPQStatusCountsAll() { + unstable_noStore(); + + try { + // 모든 PQ 상태별 개수 조회 (벤더 제한 없음) + const pqStatuses = await db + .select({ + status: vendorPQSubmissions.status, + count: count(), + }) + .from(vendorPQSubmissions) + .groupBy(vendorPQSubmissions.status); + + // 상태별 개수를 객체로 변환 + const statusCounts = { + REQUESTED: 0, + IN_PROGRESS: 0, + SUBMITTED: 0, + APPROVED: 0, + REJECTED: 0, + }; + + // 조회된 결과를 statusCounts 객체에 매핑 + pqStatuses.forEach((item) => { + if (item.status in statusCounts) { + statusCounts[item.status as keyof typeof statusCounts] = item.count; + } + }); + + return statusCounts; + } catch (error) { + console.error("Error fetching PQ status counts:", error); + return { + REQUESTED: 0, + IN_PROGRESS: 0, + SUBMITTED: 0, + APPROVED: 0, + REJECTED: 0, + }; + } +} + +// PQ 타입별, 상태별 개수 집계 함수 (추가 옵션) +export async function getPQDetailedStatusCounts() { + try { + // 타입별, 상태별 개수 조회 + const pqStatuses = await db + .select({ + type: vendorPQSubmissions.type, + status: vendorPQSubmissions.status, + count: count(), + }) + .from(vendorPQSubmissions) + .groupBy(vendorPQSubmissions.type, vendorPQSubmissions.status); + + // 결과를 저장할 객체 초기화 + const result = { + GENERAL: { + REQUESTED: 0, + IN_PROGRESS: 0, + SUBMITTED: 0, + APPROVED: 0, + REJECTED: 0, + total: 0 + }, + PROJECT: { + REQUESTED: 0, + IN_PROGRESS: 0, + SUBMITTED: 0, + APPROVED: 0, + REJECTED: 0, + total: 0 + }, + total: { + REQUESTED: 0, + IN_PROGRESS: 0, + SUBMITTED: 0, + APPROVED: 0, + REJECTED: 0, + total: 0 + } + }; + + // 결과 매핑 + pqStatuses.forEach((item) => { + if (item.type && item.status) { + const type = item.type as keyof typeof result; + const status = item.status as keyof typeof result.GENERAL; + + if (type in result && status in result[type]) { + // 타입별 상태 카운트 업데이트 + result[type][status] = item.count; + + // 타입별 합계 업데이트 + result[type].total += item.count; + + // 전체 상태별 카운트 업데이트 + result.total[status] += item.count; + + // 전체 합계 업데이트 + result.total.total += item.count; + } + } + }); + + return result; + } catch (error) { + console.error("Error fetching detailed PQ status counts:", error); + return { + GENERAL: { + REQUESTED: 0, + IN_PROGRESS: 0, + SUBMITTED: 0, + APPROVED: 0, + REJECTED: 0, + total: 0 + }, + PROJECT: { + REQUESTED: 0, + IN_PROGRESS: 0, + SUBMITTED: 0, + APPROVED: 0, + REJECTED: 0, + total: 0 + }, + total: { + REQUESTED: 0, + IN_PROGRESS: 0, + SUBMITTED: 0, + APPROVED: 0, + REJECTED: 0, + total: 0 + } + }; + } +} + +/** + * SHI 코멘트 업데이트 액션 + */ +export async function updateSHICommentAction({ + answerId, + shiComment, +}: { + answerId: number; + shiComment: string; +}) { + try { + await db + .update(vendorPqCriteriaAnswers) + .set({ + shiComment, + updatedAt: new Date(), + }) + .where(eq(vendorPqCriteriaAnswers.id, answerId)); + + return { ok: true }; + } catch (error) { + console.error("updateSHICommentAction error:", error); + return { ok: false, error: String(error) }; + } +} + +// PQ 승인 액션 +export async function approvePQAction({ + pqSubmissionId, + vendorId, +}: { + pqSubmissionId: number; + vendorId: number; +}) { + unstable_noStore(); + + try { + const headersList = await headers(); + const host = headersList.get('host') || 'localhost:3000'; + const currentDate = new Date(); + + // 1. PQ 제출 정보 조회 + const pqSubmission = await db + .select({ + id: vendorPQSubmissions.id, + vendorId: vendorPQSubmissions.vendorId, + projectId: vendorPQSubmissions.projectId, + type: vendorPQSubmissions.type, + status: vendorPQSubmissions.status, + }) + .from(vendorPQSubmissions) + .where( + and( + eq(vendorPQSubmissions.id, pqSubmissionId), + eq(vendorPQSubmissions.vendorId, vendorId) + ) + ) + .then(rows => rows[0]); + + if (!pqSubmission) { + return { ok: false, error: "PQ submission not found" }; + } + + // 2. 상태 확인 (SUBMITTED 상태만 승인 가능) + if (pqSubmission.status !== "SUBMITTED") { + return { + ok: false, + error: `Cannot approve PQ in current status: ${pqSubmission.status}` + }; + } + + // 3. 벤더 정보 조회 + const vendor = await db + .select({ + id: vendors.id, + vendorName: vendors.vendorName, + email: vendors.email, + status: vendors.status, + }) + .from(vendors) + .where(eq(vendors.id, vendorId)) + .then(rows => rows[0]); + + if (!vendor) { + return { ok: false, error: "Vendor not found" }; + } + + // 4. 프로젝트 정보 (프로젝트 PQ인 경우) + let projectName = ''; + if (pqSubmission.projectId) { + const projectData = await db + .select({ + id: projects.id, + name: projects.name, + }) + .from(projects) + .where(eq(projects.id, pqSubmission.projectId)) + .then(rows => rows[0]); + + projectName = projectData?.name || 'Unknown Project'; + } + + // 5. PQ 상태 업데이트 + await db + .update(vendorPQSubmissions) + .set({ + status: "APPROVED", + approvedAt: currentDate, + updatedAt: currentDate, + }) + .where(eq(vendorPQSubmissions.id, pqSubmissionId)); + + // 6. 일반 PQ인 경우 벤더 상태 업데이트 (선택사항) + if (pqSubmission.type === "GENERAL") { + await db + .update(vendors) + .set({ + status: "PQ_APPROVED", + updatedAt: currentDate, + }) + .where(eq(vendors.id, vendorId)); + } + + // 7. 벤더에게 이메일 알림 발송 + if (vendor.email) { + try { + const emailSubject = pqSubmission.projectId + ? `[eVCP] Project PQ Approved for ${projectName}` + : "[eVCP] General PQ Approved"; + + const portalUrl = `${host}/partners/pq`; + + await sendEmail({ + to: vendor.email, + subject: emailSubject, + template: "pq-approved-vendor", + context: { + vendorName: vendor.vendorName, + projectId: pqSubmission.projectId, + projectName: projectName, + isProjectPQ: !!pqSubmission.projectId, + approvedDate: currentDate.toLocaleString(), + portalUrl, + } + }); + } catch (emailError) { + console.error("Failed to send vendor notification:", emailError); + // 이메일 발송 실패가 전체 프로세스를 중단하지 않음 + } + } + + // 8. 캐시 무효화 + revalidateTag("vendors"); + revalidateTag("vendor-status-counts"); + revalidateTag("pq-submissions"); + revalidateTag(`vendor-pq-submissions-${vendorId}`); + + if (pqSubmission.projectId) { + revalidateTag(`project-pq-submissions-${pqSubmission.projectId}`); + revalidateTag(`project-vendors-${pqSubmission.projectId}`); + } + + return { ok: true }; + } catch (error) { + console.error("PQ approve error:", error); + return { ok: false, error: getErrorMessage(error) }; + } +} + +// QM 검토 승인 액션 +export async function approveQMReviewAction({ + pqSubmissionId, + vendorId, +}: { + pqSubmissionId: number; + vendorId: number; +}) { + unstable_noStore(); + + try { + const headersList = await headers(); + const host = headersList.get('host') || 'localhost:3000'; + const currentDate = new Date(); + + // 1. PQ 제출 정보 조회 + const pqSubmission = await db + .select({ + id: vendorPQSubmissions.id, + vendorId: vendorPQSubmissions.vendorId, + projectId: vendorPQSubmissions.projectId, + type: vendorPQSubmissions.type, + status: vendorPQSubmissions.status, + }) + .from(vendorPQSubmissions) + .where( + and( + eq(vendorPQSubmissions.id, pqSubmissionId), + eq(vendorPQSubmissions.vendorId, vendorId) + ) + ) + .then(rows => rows[0]); + + if (!pqSubmission) { + return { ok: false, error: "PQ submission not found" }; + } + + // 2. 상태 확인 (QM_REVIEWING 상태만 승인 가능) + // if (pqSubmission.status !== "QM_REVIEWING") { + // return { + // ok: false, + // error: `Cannot approve QM review in current status: ${pqSubmission.status}` + // }; + // } + + // 3. 벤더 정보 조회 + const vendor = await db + .select({ + id: vendors.id, + vendorName: vendors.vendorName, + email: vendors.email, + status: vendors.status, + }) + .from(vendors) + .where(eq(vendors.id, vendorId)) + .then(rows => rows[0]); + + if (!vendor) { + return { ok: false, error: "Vendor not found" }; + } + + // 4. 프로젝트 정보 (프로젝트 PQ인 경우) + let projectName = ''; + if (pqSubmission.projectId) { + const projectData = await db + .select({ + id: projects.id, + name: projects.name, + }) + .from(projects) + .where(eq(projects.id, pqSubmission.projectId)) + .then(rows => rows[0]); + + projectName = projectData?.name || 'Unknown Project'; + } + + // 5. PQ 상태를 QM_APPROVED로 업데이트 + await db + .update(vendorPQSubmissions) + .set({ + status: "QM_APPROVED", + approvedAt: currentDate, + updatedAt: currentDate, + }) + .where(eq(vendorPQSubmissions.id, pqSubmissionId)); + + // 6. 일반 PQ인 경우 벤더 상태를 PQ_APPROVED로 업데이트 + if (pqSubmission.type === "GENERAL") { + await db + .update(vendors) + .set({ + status: "PQ_APPROVED", + updatedAt: currentDate, + }) + .where(eq(vendors.id, vendorId)); + } + + // 7. 실사 상태 변경: QM 승인 시 QM_REVIEW_CONFIRMED로 전환 + try { + const existingInvestigation = await db + .select({ id: vendorInvestigations.id }) + .from(vendorInvestigations) + .where(eq(vendorInvestigations.pqSubmissionId, pqSubmissionId)) + .then(rows => rows[0]); + + if (existingInvestigation) { + await db + .update(vendorInvestigations) + .set({ investigationStatus: "QM_REVIEW_CONFIRMED", updatedAt: currentDate }) + .where(eq(vendorInvestigations.id, existingInvestigation.id)); + } else { + await db + .insert(vendorInvestigations) + .values({ + vendorId: vendorId, + pqSubmissionId: pqSubmissionId, + investigationStatus: "QM_REVIEW_CONFIRMED", + investigationMethod: "DOCUMENT_EVAL", + requestedAt: currentDate, + updatedAt: currentDate, + }); + } + } catch (e) { + console.error("Failed to set investigation QM_REVIEW_CONFIRMED on QM approve", e); + } + + // 8. 벤더에게 이메일 알림 발송 + if (vendor.email) { + try { + const emailSubject = pqSubmission.projectId + ? `[eVCP] Project PQ QM Approved for ${projectName}` + : "[eVCP] General PQ QM Approved"; + + const portalUrl = `${host}/partners/pq`; + + await sendEmail({ + to: vendor.email, + subject: emailSubject, + template: "pq-approved-vendor", + context: { + vendorName: vendor.vendorName, + projectId: pqSubmission.projectId, + projectName: projectName, + isProjectPQ: !!pqSubmission.projectId, + approvedDate: currentDate.toLocaleString(), + portalUrl, + } + }); + } catch (emailError) { + console.error("Failed to send vendor notification:", emailError); + } + } + + // 9. 캐시 무효화 + revalidateTag("vendors"); + revalidateTag("vendor-status-counts"); + revalidateTag("pq-submissions"); + revalidateTag("vendor-pq-submissions"); + revalidateTag("vendor-investigations"); + revalidatePath("/evcp/pq_new"); + + return { ok: true }; + } catch (error) { + console.error("QM review approve error:", error); + return { ok: false, error: getErrorMessage(error) }; + } +} + +// QM 검토 거절 액션 +export async function rejectQMReviewAction({ + pqSubmissionId, + vendorId, + rejectReason +}: { + pqSubmissionId: number; + vendorId: number; + rejectReason: string; +}) { + unstable_noStore(); + + try { + const headersList = await headers(); + const host = headersList.get('host') || 'localhost:3000'; + const currentDate = new Date(); + + // 1. PQ 제출 정보 조회 + const pqSubmission = await db + .select({ + id: vendorPQSubmissions.id, + vendorId: vendorPQSubmissions.vendorId, + projectId: vendorPQSubmissions.projectId, + type: vendorPQSubmissions.type, + status: vendorPQSubmissions.status, + }) + .from(vendorPQSubmissions) + .where( + and( + eq(vendorPQSubmissions.id, pqSubmissionId), + eq(vendorPQSubmissions.vendorId, vendorId) + ) + ) + .then(rows => rows[0]); + + if (!pqSubmission) { + return { ok: false, error: "PQ submission not found" }; + } + + // 2. 상태 확인 (QM_REVIEWING 상태만 거절 가능) + if (pqSubmission.status !== "QM_REVIEWING") { + return { + ok: false, + error: `Cannot reject QM review in current status: ${pqSubmission.status}` + }; + } + + // 3. 벤더 정보 조회 + const vendor = await db + .select({ + id: vendors.id, + vendorName: vendors.vendorName, + email: vendors.email, + status: vendors.status, + }) + .from(vendors) + .where(eq(vendors.id, vendorId)) + .then(rows => rows[0]); + + if (!vendor) { + return { ok: false, error: "Vendor not found" }; + } + + // 4. 프로젝트 정보 (프로젝트 PQ인 경우) + let projectName = ''; + if (pqSubmission.projectId) { + const projectData = await db + .select({ + id: projects.id, + name: projects.name, + }) + .from(projects) + .where(eq(projects.id, pqSubmission.projectId)) + .then(rows => rows[0]); + + projectName = projectData?.name || 'Unknown Project'; + } + + // 5. PQ 상태를 QM_REJECTED로 업데이트 + await db + .update(vendorPQSubmissions) + .set({ + status: "QM_REJECTED", + rejectedAt: currentDate, + rejectReason: rejectReason, + updatedAt: currentDate, + }) + .where(eq(vendorPQSubmissions.id, pqSubmissionId)); + + // 6. 일반 PQ인 경우 벤더 상태를 PQ_FAILED로 업데이트 + if (pqSubmission.type === "GENERAL") { + await db + .update(vendors) + .set({ + status: "PQ_FAILED", + updatedAt: currentDate, + }) + .where(eq(vendors.id, vendorId)); + } + + // 7. 실사 상태 변경: QM 거절 시 CANCELED로 전환 + try { + const existingInvestigation = await db + .select({ id: vendorInvestigations.id }) + .from(vendorInvestigations) + .where(eq(vendorInvestigations.pqSubmissionId, pqSubmissionId)) + .then(rows => rows[0]); + + if (existingInvestigation) { + await db + .update(vendorInvestigations) + .set({ investigationStatus: "CANCELED", updatedAt: currentDate }) + .where(eq(vendorInvestigations.id, existingInvestigation.id)); + } + } catch (e) { + console.error("Failed to set investigation CANCELED on QM reject", e); + } + + // 8. 벤더에게 이메일 알림 발송 + if (vendor.email) { + try { + const emailSubject = pqSubmission.projectId + ? `[eVCP] Project PQ Rejected for ${projectName}` + : "[eVCP] General PQ Rejected"; + + const portalUrl = `${host}/partners/pq`; + + await sendEmail({ + to: vendor.email, + subject: emailSubject, + template: "pq-rejected-vendor", + context: { + vendorName: vendor.vendorName, + projectId: pqSubmission.projectId, + projectName: projectName, + isProjectPQ: !!pqSubmission.projectId, + rejectedDate: currentDate.toLocaleString(), + rejectReason: rejectReason, + portalUrl, + } + }); + } catch (emailError) { + console.error("Failed to send vendor notification:", emailError); + } + } + + // 9. 캐시 무효화 + revalidateTag("vendors"); + revalidateTag("vendor-status-counts"); + revalidateTag("pq-submissions"); + revalidateTag("vendor-pq-submissions"); + revalidateTag("vendor-investigations"); + revalidatePath("/evcp/pq_new"); + + return { ok: true }; + } catch (error) { + console.error("QM review reject error:", error); + return { ok: false, error: getErrorMessage(error) }; + } +} + +// PQ 거부 액션 +export async function rejectPQAction({ + pqSubmissionId, + vendorId, + rejectReason +}: { + pqSubmissionId: number; + vendorId: number; + rejectReason: string; +}) { + unstable_noStore(); + + try { + const headersList = await headers(); + const host = headersList.get('host') || 'localhost:3000'; + const currentDate = new Date(); + + // 1. PQ 제출 정보 조회 + const pqSubmission = await db + .select({ + id: vendorPQSubmissions.id, + vendorId: vendorPQSubmissions.vendorId, + projectId: vendorPQSubmissions.projectId, + type: vendorPQSubmissions.type, + status: vendorPQSubmissions.status, + }) + .from(vendorPQSubmissions) + .where( + and( + eq(vendorPQSubmissions.id, pqSubmissionId), + eq(vendorPQSubmissions.vendorId, vendorId) + ) + ) + .then(rows => rows[0]); + + if (!pqSubmission) { + return { ok: false, error: "PQ submission not found" }; + } + + // 2. 상태 확인 (SUBMITTED 상태만 거부 가능) + if (pqSubmission.status !== "SUBMITTED") { + return { + ok: false, + error: `Cannot reject PQ in current status: ${pqSubmission.status}` + }; + } + + // 3. 벤더 정보 조회 + const vendor = await db + .select({ + id: vendors.id, + vendorName: vendors.vendorName, + email: vendors.email, + status: vendors.status, + }) + .from(vendors) + .where(eq(vendors.id, vendorId)) + .then(rows => rows[0]); + + if (!vendor) { + return { ok: false, error: "Vendor not found" }; + } + + // 4. 프로젝트 정보 (프로젝트 PQ인 경우) + let projectName = ''; + if (pqSubmission.projectId) { + const projectData = await db + .select({ + id: projects.id, + name: projects.name, + }) + .from(projects) + .where(eq(projects.id, pqSubmission.projectId)) + .then(rows => rows[0]); + + projectName = projectData?.name || 'Unknown Project'; + } + + // 5. PQ 상태 업데이트 + await db + .update(vendorPQSubmissions) + .set({ + status: "REJECTED", + rejectedAt: currentDate, + rejectReason: rejectReason, + updatedAt: currentDate, + }) + .where(eq(vendorPQSubmissions.id, pqSubmissionId)); + + // 6. 일반 PQ인 경우 벤더 상태 업데이트 (선택사항) + if (pqSubmission.type === "GENERAL") { + await db + .update(vendors) + .set({ + status: "PQ_FAILED", + updatedAt: currentDate, + }) + .where(eq(vendors.id, vendorId)); + } + + // 7. 벤더에게 이메일 알림 발송 + if (vendor.email) { + try { + const emailSubject = pqSubmission.projectId + ? `[eVCP] Project PQ Rejected for ${projectName}` + : "[eVCP] General PQ Rejected"; + + const portalUrl = `${host}/partners/pq`; + + await sendEmail({ + to: vendor.email, + subject: emailSubject, + template: "pq-rejected-vendor", + context: { + vendorName: vendor.vendorName, + projectId: pqSubmission.projectId, + projectName: projectName, + isProjectPQ: !!pqSubmission.projectId, + rejectedDate: currentDate.toLocaleString(), + rejectReason: rejectReason, + portalUrl, + } + }); + } catch (emailError) { + console.error("Failed to send vendor notification:", emailError); + // 이메일 발송 실패가 전체 프로세스를 중단하지 않음 + } + } + + // 8. 캐시 무효화 + revalidateTag("vendors"); + revalidateTag("vendor-status-counts"); + revalidateTag("pq-submissions"); + revalidateTag(`vendor-pq-submissions-${vendorId}`); + + if (pqSubmission.projectId) { + revalidateTag(`project-pq-submissions-${pqSubmission.projectId}`); + revalidateTag(`project-vendors-${pqSubmission.projectId}`); + } + + return { ok: true }; + } catch (error) { + console.error("PQ reject error:", error); + return { ok: false, error: getErrorMessage(error) }; + } +} + +// PQ 보완요청 메일 발송 액션 +export async function requestPqSupplementAction({ + pqSubmissionId, + vendorId, + comment, +}: { + pqSubmissionId: number; + vendorId: number; + comment: string; +}) { + unstable_noStore(); + try { + const session = await getServerSession(authOptions); + const currentUserEmail = session?.user?.email || null; + const headersList = await headers(); + const host = headersList.get('host') || 'localhost:3000'; + + // PQ/벤더/요청자 정보 조회 + const pq = await db + .select({ id: vendorPQSubmissions.id, pqNumber: vendorPQSubmissions.pqNumber, requesterId: vendorPQSubmissions.requesterId, projectId: vendorPQSubmissions.projectId }) + .from(vendorPQSubmissions) + .where(and(eq(vendorPQSubmissions.id, pqSubmissionId), eq(vendorPQSubmissions.vendorId, vendorId))) + .then(rows => rows[0]); + if (!pq) return { ok: false, error: 'PQ submission not found' }; + + const vendor = await db + .select({ vendorName: vendors.vendorName, email: vendors.email }) + .from(vendors) + .where(eq(vendors.id, vendorId)) + .then(rows => rows[0]); + if (!vendor?.email) return { ok: false, error: 'Vendor email not found' }; + + let requesterEmail: string | null = null; + if (pq.requesterId) { + const requester = await db + .select({ email: users.email }) + .from(users) + .where(eq(users.id, pq.requesterId)) + .then(rows => rows[0]); + requesterEmail = requester?.email || null; + } + + const reviewUrl = `${process.env.NEXT_PUBLIC_BASE_URL}/partners/pq_new`; + + await sendEmail({ + to: vendor.email, + cc: [currentUserEmail, requesterEmail].filter(Boolean) as string[], + subject: `[eVCP] PQ 보완 요청: ${vendor.vendorName}`, + template: 'pq-supplement-request', + context: { + vendorName: vendor.vendorName, + pqNumber: pq.pqNumber, + comment, + reviewUrl, + }, + }); + + revalidateTag('pq-submissions'); + return { ok: true }; + } catch (error) { + console.error('PQ supplement request error:', error); + return { ok: false, error: getErrorMessage(error) }; + } +} + + +// 실사 의뢰 생성 서버 액션 +export async function requestInvestigationAction( + pqSubmissionIds: number[], + currentUser: { id: number; epId: string | null; email?: string }, + data: { + qmManagerId: number, + forecastedAt: Date, + investigationAddress: string, + investigationNotes?: string + }, + options?: { + skipRevalidation?: boolean; // ✅ 핸들러에서 호출 시 revalidation 건너뛰기 + } +) { + try { + // 세션에서 요청자 정보 가져오기 + if (!currentUser.id) { + return { success: false, error: "인증된 사용자만 실사를 의뢰할 수 있습니다." }; + } + + const result = await db.transaction(async (tx) => { + // PQ 제출 정보 조회 + const pqSubmissions = await tx + .select({ + id: vendorPQSubmissions.id, + vendorId: vendorPQSubmissions.vendorId, + }) + .from(vendorPQSubmissions) + .where( + and( + inArray(vendorPQSubmissions.id, pqSubmissionIds), + eq(vendorPQSubmissions.status, "APPROVED") + ) + ); + + if (pqSubmissions.length === 0) { + throw new Error("승인된 PQ 제출 항목이 없습니다."); + } + + const now = new Date(); + + // 실사 요청 생성 + const investigations = pqSubmissions.map((pq) => { + return { + vendorId: pq.vendorId, + pqSubmissionId: pq.id, + investigationStatus: "PLANNED" as const, // enum 타입으로 명시적 지정 + qmManagerId: data.qmManagerId, + forecastedAt: data.forecastedAt, + investigationAddress: data.investigationAddress, + investigationNotes: data.investigationNotes || null, + requesterId: currentUser.id, + requestedAt: now, + createdAt: now, + updatedAt: now, + }; + }); + //PQ 제출 정보 업데이트, status를 QM_REVIEWING로 업데이트 + await tx + .update(vendorPQSubmissions) + .set({ + status: "QM_REVIEWING", + updatedAt: now, + }) + .where(inArray(vendorPQSubmissions.id, pqSubmissionIds)); + + + // 실사 요청 저장 + const created = await tx + .insert(vendorInvestigations) + .values(investigations) + .returning(); + + return created; + }); + + + // 이메일 발송 (트랜잭션 외부에서 실행) + try { + // 1. 협력업체 정보 조회 (이메일 포함) + const vendorIds = result.map(inv => inv.vendorId); + const uniqueVendorIds = [...new Set(vendorIds)]; + + const vendorInfos = await db + .select({ + id: vendors.id, + vendorName: vendors.vendorName, + email: vendors.email, + }) + .from(vendors) + .where(inArray(vendors.id, uniqueVendorIds)); + + // 2. QM 담당자 정보 조회 + const qmManager = await db + .select({ + id: users.id, + name: users.name, + email: users.email, + }) + .from(users) + .where(eq(users.id, data.qmManagerId)) + .limit(1) + .then(rows => rows[0]); + + // 3. 요청자(현재 사용자) 정보 조회 + const requester = await db + .select({ + id: users.id, + name: users.name, + email: users.email, + }) + .from(users) + .where(eq(users.id, currentUser.id)) + .limit(1) + .then(rows => rows[0]); + + const portalUrl = process.env.NEXT_PUBLIC_BASE_URL || "http://localhost:3000"; + const currentYear = new Date().getFullYear(); + const forecastedAtFormatted = format(data.forecastedAt, "yyyy-MM-dd"); + + // 4. 협력업체별로 이메일 발송 (investigation-request.hbs 템플릿 사용) + const vendorEmailPromises = vendorInfos + .filter(vendor => vendor.email) // 이메일이 있는 경우만 + .map(async (vendor) => { + try { + await sendEmail({ + to: vendor.email!, + subject: "[eVCP] 협력업체 실사 요청", + template: "investigation-request", + context: { + language: "ko", + vendorIds: [vendor.id], + notes: data.investigationNotes || "실사가 예정되어 있습니다.", + portalUrl: `${portalUrl}/ko/partners/site-visit`, + currentYear: currentYear, + }, + }); + console.log(`협력업체 이메일 발송 완료: ${vendor.vendorName} (${vendor.email})`); + } catch (emailError) { + console.error(`협력업체 이메일 발송 실패: ${vendor.vendorName} (${vendor.email})`, emailError); + } + }); + + await Promise.all(vendorEmailPromises); + + // 5. QM 담당자에게 알림 이메일 발송 + if (qmManager?.email) { + try { + const vendorNames = vendorInfos.map(v => v.vendorName); + + await sendEmail({ + to: qmManager.email, + subject: "[eVCP] 실사 의뢰 요청 알림", + template: "investigation-request-notification", + context: { + language: "ko", + recipientName: qmManager.name, + vendorNames: vendorNames, + forecastedAt: forecastedAtFormatted, + investigationAddress: data.investigationAddress, + investigationNotes: data.investigationNotes || null, + requesterName: requester?.name || "알 수 없음", + portalUrl: `${portalUrl}/evcp/vendor-investigation`, + currentYear: currentYear, + }, + }); + console.log(`QM 담당자 이메일 발송 완료: ${qmManager.name} (${qmManager.email})`); + } catch (emailError) { + console.error(`QM 담당자 이메일 발송 실패: ${qmManager.name} (${qmManager.email})`, emailError); + } + } + + // 6. 요청자(현재 사용자)에게 알림 이메일 발송 (QM 담당자와 다른 경우만) + // if (requester?.email && requester.id !== data.qmManagerId) { + // try { + // const vendorNames = vendorInfos.map(v => v.vendorName); + + // await sendEmail({ + // to: requester.email, + // subject: "[eVCP] 실사 의뢰 요청 알림", + // template: "investigation-request-notification", + // context: { + // language: "ko", + // recipientName: requester.name, + // vendorNames: vendorNames, + // forecastedAt: forecastedAtFormatted, + // investigationAddress: data.investigationAddress, + // investigationNotes: data.investigationNotes || null, + // requesterName: requester.name, + // portalUrl: `${portalUrl}/evcp/vendor-investigation`, + // currentYear: currentYear, + // }, + // }); + // console.log(`요청자 이메일 발송 완료: ${requester.name} (${requester.email})`); + // } catch (emailError) { + // console.error(`요청자 이메일 발송 실패: ${requester.name} (${requester.email})`, emailError); + // } + // } + } catch (emailErr) { + // 이메일 발송 실패는 로그만 남기고 전체 프로세스는 성공으로 처리 + console.error("이메일 발송 중 오류 발생:", emailErr); + } + + // 캐시 무효화 (skipRevalidation 옵션이 false일 때만) + if (!options?.skipRevalidation) { + revalidateTag("vendor-investigations") + revalidateTag("pq-submissions") + } + + return { + success: true, + count: result.length, + data: result + }; + } catch (err) { + console.error("실사 의뢰 중 오류 발생:", err); + return { + success: false, + error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다." + }; + } +} + +// 실사 의뢰 취소 서버 액션 +export async function cancelInvestigationAction(investigationIds: number[]) { + try { + const session = await getServerSession(authOptions) + const userId = session?.user?.id ? Number(session.user.id) : null + + if (!userId) { + return { success: false, error: "인증된 사용자만 실사를 취소할 수 있습니다." } + } + + const result = await db.transaction(async (tx) => { + // PLANNED 상태인 실사만 취소 가능 + const updatedInvestigations = await tx + .update(vendorInvestigations) + .set({ + investigationStatus: "CANCELED", + updatedAt: new Date(), + }) + .where( + and( + inArray(vendorInvestigations.id, investigationIds), + eq(vendorInvestigations.investigationStatus, "PLANNED") + ) + ) + .returning() + + return updatedInvestigations + }) + + // 캐시 무효화 + revalidateTag("vendor-investigations") + revalidateTag("pq-submissions") + + return { + success: true, + count: result.length, + data: result + } + } catch (err) { + console.error("실사 취소 중 오류 발생:", err) + return { + success: false, + error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다." + } + } +} + +// 실사 재의뢰 서버 액션 +export async function reRequestInvestigationAction( + investigationIds: number[], + currentUser?: { id: number }, // ✅ 핸들러에서 호출 시 사용자 정보 전달 + options?: { + skipRevalidation?: boolean; // ✅ 핸들러에서 호출 시 revalidation 건너뛰기 + } +) { + try { + let userId: number | null = null; + + if (currentUser) { + // 핸들러에서 호출 시 (결재 승인 후) + userId = currentUser.id; + + // ✅ 핸들러에서 호출 시 userId 검증: 없으면 잘못된 상황 (예외 처리) + if (!userId || userId <= 0) { + throw new Error('핸들러에서 호출 시 currentUser.id가 필수입니다.'); + } + } else { + // 직접 호출 시 (세션에서 가져오기) + const session = await getServerSession(authOptions); + userId = session?.user?.id ? Number(session.user.id) : null; + } + + if (!userId) { + return { success: false, error: "인증된 사용자만 실사를 재의뢰할 수 있습니다." } + } + + const result = await db.transaction(async (tx) => { + // CANCELED 상태인 실사만 재의뢰 가능 + const updatedInvestigations = await tx + .update(vendorInvestigations) + .set({ + investigationStatus: "PLANNED", + updatedAt: new Date(), + }) + .where( + and( + inArray(vendorInvestigations.id, investigationIds), + eq(vendorInvestigations.investigationStatus, "CANCELED") + ) + ) + .returning() + + return updatedInvestigations + }) + + // 캐시 무효화 (skipRevalidation 옵션이 false일 때만) + if (!options?.skipRevalidation) { + revalidateTag("vendor-investigations") + revalidateTag("pq-submissions") + } + + return { + success: true, + count: result.length, + data: result + } + } catch (err) { + console.error("실사 재의뢰 중 오류 발생:", err) + return { + success: false, + error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다." + } + } +} + +// 실사 결과 발송 서버 액션 +export async function sendInvestigationResultsAction(input: { + investigationIds: number[]; + purchaseComment?: string; +}) { + try { + const session = await getServerSession(authOptions) + const userId = session?.user?.id ? Number(session.user.id) : null + + if (!userId) { + return { success: false, error: "인증된 사용자만 실사 결과를 발송할 수 있습니다." } + } + + const result = await db.transaction(async (tx) => { + // 완료된 실사만 결과 발송 가능 + const investigations = await tx + .select({ + id: vendorInvestigations.id, + vendorId: vendorInvestigations.vendorId, + pqSubmissionId: vendorInvestigations.pqSubmissionId, + evaluationResult: vendorInvestigations.evaluationResult, + investigationNotes: vendorInvestigations.investigationNotes, + investigationAddress: vendorInvestigations.investigationAddress, + investigationMethod: vendorInvestigations.investigationMethod, + confirmedAt: vendorInvestigations.confirmedAt, + // Vendor 정보 + vendorCode: vendors.vendorCode, + vendorName: vendors.vendorName, + vendorEmail: vendors.email, + // PQ 정보 + pqNumber: vendorPQSubmissions.pqNumber, + pqItems: vendorPQSubmissions.pqItems, + projectCode: projects.code, + projectName: projects.name, + // 발신자 정보 + senderName: users.name, + senderEmail: users.email, + }) + .from(vendorInvestigations) + .leftJoin(vendors, eq(vendorInvestigations.vendorId, vendors.id)) + .leftJoin(vendorPQSubmissions, eq(vendorInvestigations.pqSubmissionId, vendorPQSubmissions.id)) + .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id)) + .leftJoin(users, eq(vendorInvestigations.requesterId, users.id)) + .where( + inArray(vendorInvestigations.id, input.investigationIds) + ) + + if (investigations.length === 0) { + throw new Error("발송할 수 있는 완료된 실사가 없습니다.") + } + + // 각 실사에 대해 이메일 발송 + const emailResults = await Promise.all( + investigations.map(async (investigation) => { + try { + // 이메일 컨텍스트 구성 + const emailContext = { + // 기본 정보 + pqNumber: investigation.pqNumber || "N/A", + vendorCode: investigation.vendorCode || "N/A", + vendorName: investigation.vendorName || "N/A", + + // 실사 정보 - pqItems를 itemCode-itemName 형태로 모든 항목 표시 + auditItem: (() => { + if (investigation.pqItems) { + try { + const parsed = typeof investigation.pqItems === 'string' + ? JSON.parse(investigation.pqItems) + : investigation.pqItems; + if (Array.isArray(parsed)) { + return parsed.map(item => { + if (typeof item === 'string') return item; + if (typeof item === 'object') { + const code = item.itemCode || item.code || ""; + const name = item.itemName || item.name || ""; + if (code && name) return `${code}-${name}`; + return name || code || String(item); + } + return String(item); + }).join(', '); + } + return String(parsed); + } catch { + return String(investigation.pqItems); + } + } + return investigation.projectName || "N/A"; + })(), + auditFactoryAddress: investigation.investigationAddress || "N/A", + auditMethod: getInvestigationMethodLabel(investigation.investigationMethod || ""), + auditResult: investigation.evaluationResult === "APPROVED" ? "Pass(승인)" : + investigation.evaluationResult === "SUPPLEMENT" ? "Pass(조건부승인)" : + investigation.evaluationResult === "REJECTED" ? "Fail(미승인)" : "N/A", + additionalNotes: input.purchaseComment || investigation.investigationNotes || "", + + // 발신자 정보 + senderName: investigation.senderName || "삼성중공업", + senderEmail: investigation.senderEmail || "procurement@samsung.com", + + // 이메일 제목 + subject: `[SHI Audit] 실사 결과 안내 _ ${investigation.vendorName} _ PQ No. ${investigation.pqNumber}`, + } + + // 이메일 발송 + if (investigation.vendorEmail) { + await sendEmail({ + to: investigation.vendorEmail, + subject: emailContext.subject, + template: "audit-result-notice", + context: emailContext, + }) + } else { + throw new Error("벤더 이메일 주소가 없습니다.") + } + + return { success: true, investigationId: investigation.id } + } catch (error) { + console.error(`실사 ID ${investigation.id} 이메일 발송 실패:`, error) + return { success: false, investigationId: investigation.id, error: error instanceof Error ? error.message : "알 수 없는 오류" } + } + }) + ) + + // 성공한 실사들의 상태를 RESULT_SENT로 업데이트 + const successfulInvestigationIds = emailResults + .filter(result => result.success) + .map(result => result.investigationId) + + if (successfulInvestigationIds.length > 0) { + await tx + .update(vendorInvestigations) + .set({ + investigationStatus: "RESULT_SENT", + purchaseComment: input.purchaseComment, + updatedAt: new Date(), + }) + .where(inArray(vendorInvestigations.id, successfulInvestigationIds)) + + // 정규업체등록관리에 레코드 생성 로직 + const successfulInvestigations = investigations.filter(inv => + successfulInvestigationIds.includes(inv.id) + ); + + for (const investigation of successfulInvestigations) { + // 1. 미실사 PQ는 제외 (이미 COMPLETED 상태인 것만 처리하므로 실사된 것들) + // 2. 승인된 실사만 정규업체등록 대상 + if (investigation.evaluationResult === "APPROVED") { + try { + // 기존 정규업체등록 레코드 확인 + const existingRegistration = await tx + .select({ id: vendorRegularRegistrations.id }) + .from(vendorRegularRegistrations) + .where(eq(vendorRegularRegistrations.vendorId, investigation.vendorId)) + .limit(1); + + // 프로젝트 PQ의 경우 기존 레코드가 있으면 skip, 없으면 생성 + // 일반 PQ의 경우 무조건 생성 (이미 체크는 위에서 함) + if (existingRegistration.length === 0) { + // pqItems를 majorItems로 변환 - JSON 통째로 넘겨줌 + let majorItemsJson = null; + if (investigation.pqItems) { + try { + // 이미 파싱된 객체거나 JSON 문자열인 경우 모두 처리 + const parsed = typeof investigation.pqItems === 'string' + ? JSON.parse(investigation.pqItems) + : investigation.pqItems; + + // 원본 구조를 최대한 보존하면서 JSON으로 저장 + majorItemsJson = JSON.stringify(parsed); + } catch { + // 파싱 실패 시 문자열로 저장 + majorItemsJson = JSON.stringify([{ + itemCode: "UNKNOWN", + itemName: String(investigation.pqItems) + }]); + } + } + + await tx.insert(vendorRegularRegistrations).values({ + vendorId: investigation.vendorId, + status: "under_review", // 실사 통과 상태로 시작 + majorItems: majorItemsJson, + registrationRequestDate: new Date().toISOString().split('T')[0], // date 타입으로 변환 + remarks: `PQ 실사 통과로 자동 생성 (PQ번호: ${investigation.pqNumber || 'N/A'})`, + }); + + console.log(`✅ 정규업체등록 레코드 생성: 벤더 ID ${investigation.vendorId}`); + } else { + console.log(`⏭️ 정규업체등록 레코드 이미 존재: 벤더 ID ${investigation.vendorId} (Skip)`); + } + } catch (error) { + console.error(`❌ 정규업체등록 레코드 생성 실패 (벤더 ID: ${investigation.vendorId}):`, error); + // 정규업체등록 생성 실패는 전체 프로세스를 중단하지 않음 + } + } + } + } + + return { + totalCount: investigations.length, + successCount: emailResults.filter(r => r.success).length, + failedCount: emailResults.filter(r => !r.success).length, + emailResults, + } + }) + + // 캐시 무효화 + revalidateTag("vendor-investigations") + revalidateTag("pq-submissions") + revalidateTag("vendor-regular-registrations") + + return { + success: true, + data: result, + message: `${result.successCount}개 실사 결과가 성공적으로 발송되었습니다.` + } + } catch (err) { + console.error("실사 결과 발송 중 오류 발생:", err) + return { + success: false, + error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다." + } + } +} + +// 실사 방법 라벨 변환 함수 +function getInvestigationMethodLabel(method: string): string { + switch (method) { + case "PURCHASE_SELF_EVAL": + return "구매자체평가" + case "DOCUMENT_EVAL": + return "서류평가" + case "PRODUCT_INSPECTION": + return "제품검사평가" + case "SITE_VISIT_EVAL": + return "방문실사평가" + default: + return method + } +} + +// export async function getQMManagers() { +// try { +// // QM 부서 사용자만 필터링 (department 필드가 있다고 가정) +// // 또는 QM 역할을 가진 사용자만 필터링 (role 필드가 있다고 가정) +// const qmUsers = await db +// .select({ +// id: users.id, +// name: users.name, +// email: users.email, +// }) +// .from(users) +// // .where( +// // // 필요에 따라 조건 조정 (예: QM 부서 또는 특정 역할만) +// // // eq(users.department, "QM") 또는 +// // // eq(users.role, "QM_MANAGER") +// // // 테스트를 위해 모든 사용자 반환도 가능 +// // eq(users.active, true) +// // ) +// .orderBy(users.name) + +// return { +// data: qmUsers, +// success: true +// } +// } catch (error) { +// console.error("QM 담당자 목록 조회 오류:", error) +// return { +// data: [], +// success: false, +// error: error instanceof Error ? error.message : "QM 담당자 목록을 가져오는 중 오류가 발생했습니다." +// } +// } +// } +export async function getQMManagers() { + try { + // domain이 'partners'가 아니고, isActive가 true인 사용자만 조회 + // 또는 deptName이 '품질경영팀('를 포함하는 경우도 포함 + const qmUsers = await db + .select({ + id: users.id, + name: users.name, + email: users.email, + isActive: users.isActive, + }) + .from(users) + .where( + and( + eq(users.isActive, true), + ne(users.domain, "partners"), + ilike(users.deptName, "%품질경영팀(%") + ) + ) + .orderBy(users.name); + + return { + data: qmUsers, + success: true + } + } catch (error) { + console.error("QM 담당자 목록 조회 오류:", error) + return { + data: [], + success: false, + error: error instanceof Error ? error.message : "QM 담당자 목록을 가져오는 중 오류가 발생했습니다." + } + } +} +export async function getFactoryLocationAnswer(vendorId: number, projectId: number | null = null) { + try { + // 1. "Location of Factory" 체크포인트를 가진 criteria 찾기 + const criteria = await db + .select({ + id: pqCriterias.id + }) + .from(pqCriterias) + .where(ilike(pqCriterias.checkPoint, "%Location of Factory%")) + .limit(1); + + if (!criteria.length) { + return { success: false, message: "Factory Location 질문을 찾을 수 없습니다." }; + } + + const criteriaId = criteria[0].id; + + // 2. 해당 criteria에 대한 벤더의 응답 조회 + const answerQuery = db + .select({ + answer: vendorPqCriteriaAnswers.answer + }) + .from(vendorPqCriteriaAnswers) + .where( + and( + eq(vendorPqCriteriaAnswers.vendorId, vendorId), + eq(vendorPqCriteriaAnswers.criteriaId, criteriaId) + ) + ); + + // 프로젝트 ID가 있으면 추가 조건 + if (projectId !== null) { + answerQuery.where(eq(vendorPqCriteriaAnswers.projectId, projectId)); + } else { + answerQuery.where(eq(vendorPqCriteriaAnswers.projectId, null)); + } + + const answers = await answerQuery.limit(1); + + if (!answers.length || !answers[0].answer) { + return { success: false, message: "공장 위치 정보를 찾을 수 없습니다." }; + } + + return { + success: true, + factoryLocation: answers[0].answer + }; + } catch (error) { + console.error("Factory location 조회 오류:", error); + return { success: false, message: "오류가 발생했습니다." }; + } +} + +// ----------------------------------------------------------------------------- +// PQ LISTS (GENERAL / PROJECT / NON_INSPECTION) CRUD + 조회 + +// LOAD CRITERIAS BY LIST +export async function getPqCriteriasByListId(listId: number) { + const criterias = await db + .select() + .from(pqCriterias) + .where(eq(pqCriterias.pqListId, listId)) + .orderBy(pqCriterias.groupName, pqCriterias.code); + return criterias; +} + +// ----------------------------------------------------------------------------- +// PQ LISTS CRUD 액션 - 개선된 버전 +// ----------------------------------------------------------------------------- + + +export async function getPQLists(input: GetPqListsSchema) { + return unstable_cache( + async () => { + try { + const offset = (input.page - 1) * input.perPage; + + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + ilike(pqLists.name, s), + ilike(pqLists.type, s), + ilike(projects.code, s), + ilike(projects.name, s) + ); + } + + const advancedWhere = input.filters + ? filterColumns({ table: pqLists, filters: input.filters, joinOperator: input.joinOperator }) + : undefined; + + const finalWhere = and( + // eq(pqLists.isDeleted, false), + advancedWhere, + globalWhere + ); + + const orderBy = input.sort.length + ? input.sort.map((s) => (s.desc ? desc(pqLists.createdAt) : asc(pqLists.createdAt))) + : [desc(pqLists.createdAt)]; + + const { data, total } = await db.transaction(async (tx) => { + // 만료된 PQ 리스트들을 자동으로 비활성화 + const now = new Date(); + await tx + .update(pqLists) + .set({ + isDeleted: true, + updatedAt: now + }) + .where( + and( + eq(pqLists.isDeleted, false), + lt(pqLists.validTo, now), + isNotNull(pqLists.validTo) + ) + ); + + const data = await tx + .select({ + id: pqLists.id, + name: pqLists.name, + type: pqLists.type, + projectId: pqLists.projectId, + validTo: pqLists.validTo, + isDeleted: pqLists.isDeleted, + createdAt: pqLists.createdAt, + updatedAt: pqLists.updatedAt, + createdBy: users.name, + projectCode: projects.code, + projectName: projects.name, + updatedBy: users.name, + }) + .from(pqLists) + .leftJoin(projects, eq(pqLists.projectId, projects.id)) + .leftJoin(users, eq(pqLists.createdBy, users.id)) + .where(finalWhere) + .orderBy(...orderBy) + .offset(offset) + .limit(input.perPage); + + const countRes = await tx + .select({ count: count() }) + .from(pqLists) + .where(finalWhere); + + // 각 PQ 리스트의 항목 수 조회 + const dataWithCriteriaCount = await Promise.all( + data.map(async (item) => { + const criteriaCount = await getPqListCriteriaCount(item.id); + return { + ...item, + criteriaCount + }; + }) + ); + + + return { data: dataWithCriteriaCount, total: countRes[0]?.count ?? 0 }; + }); + + const pageCount = Math.ceil(total / input.perPage); + return { data, pageCount }; + } catch (err) { + console.error("Error in getPQLists:", err); + return { data: [], pageCount: 0 }; + } + }, + [JSON.stringify(input)], + { revalidate: 3600, tags: ["pq-lists"] } + )(); +} + +export async function togglePQListsAction(ids: number[], newIsDeleted: boolean) { + try { + if (ids.length === 0) { + return { success: false, message: "선택한 항목이 없습니다" }; + } + const session = await getServerSession(authOptions); + const userId = session?.user?.id ? Number(session.user.id) : null; + const now = new Date(); + + // 활성화하려는 경우 중복 활성화 체크 + if (!newIsDeleted) { + // 선택된 PQ 리스트들의 정보를 먼저 가져옴 + const selectedPqLists = await db + .select({ + id: pqLists.id, + name: pqLists.name, + type: pqLists.type, + projectId: pqLists.projectId, + }) + .from(pqLists) + .where(inArray(pqLists.id, ids)); + + // 현재 활성화된 PQ 리스트 확인 + const activePqLists = await db + .select({ + id: pqLists.id, + name: pqLists.name, + type: pqLists.type, + projectId: pqLists.projectId, + }) + .from(pqLists) + .where(and( + eq(pqLists.isDeleted, false), + not(inArray(pqLists.id, ids)) + )); + + // 각 선택된 PQ 리스트에 대해 중복 체크 + for (const selectedPq of selectedPqLists) { + // 일반 PQ 또는 미실사 PQ인 경우 + if (selectedPq.type === "GENERAL" || selectedPq.type === "NON_INSPECTION") { + const activeSameType = activePqLists.filter(pq => pq.type === selectedPq.type); + + if (activeSameType.length > 0) { + const activeNames = activeSameType.map(pq => pq.name).join(", "); + return { + success: false, + error: `${selectedPq.type === "GENERAL" ? "일반" : "미실사"} PQ는 하나만 활성화할 수 있습니다.먼저 활성화된 ${selectedPq.type === "GENERAL" ? "일반" : "미실사"} PQ를 비활성화한 후 활성화해주세요.` + }; + } + } + + // 프로젝트 PQ인 경우 + if (selectedPq.type === "PROJECT" && selectedPq.projectId) { + const activeSameProject = activePqLists.filter(pq => + pq.type === "PROJECT" && pq.projectId === selectedPq.projectId + ); + + if (activeSameProject.length > 0) { + const activeNames = activeSameProject.map(pq => pq.name).join(", "); + return { + success: false, + error: `프로젝트 PQ는 프로젝트별로 하나만 활성화할 수 있습니다. 먼저 활성화된 프로젝트 PQ를 비활성화한 후 활성화해주세요.` + }; + } + } + } + } + + const updated = await db + .update(pqLists) + .set({ isDeleted: newIsDeleted, updatedAt: now, updatedBy: userId }) + .where(inArray(pqLists.id, ids)) + .returning(); + revalidateTag("pq-lists"); + return { + success: true, + data: updated, + message: `${updated.length}개의 PQ 목록이 ${newIsDeleted ? "비활성화" : "활성화"}되었습니다` + }; + } catch (error) { + console.error("Error toggling PQ lists:", error); + return { + success: false, + message: "PQ 목록 상태 변경에 실패했습니다" + }; + } +} + +export async function createPQListAction(input: CreatePqListInput) { + try { + const validated = createPqListSchema.parse(input); + const session = await getServerSession(authOptions); + const userId = session?.user?.id; + // const userName = session?.user?.name || "Unknown"; + + // General PQ인 경우 중복 체크 + if (validated.type === "GENERAL") { + const existingGeneralPQ = await db + .select() + .from(pqLists) + .where( + and( + eq(pqLists.type, "GENERAL"), + eq(pqLists.isDeleted, false) + ) + ) + .limit(1); + + if (existingGeneralPQ.length > 0) { + return { + success: false, + error: "General PQ 목록은 하나만 생성할 수 있습니다. 먼저 활성화된 General PQ를 비활성화한 후 생성해주세요." + }; + } + } + + // Non-Inspection PQ인 경우 중복 체크 + if (validated.type === "NON_INSPECTION") { + const existingNonInspectionPQ = await db + .select() + .from(pqLists) + .where( + and( + eq(pqLists.type, "NON_INSPECTION"), + eq(pqLists.isDeleted, false) + ) + ) + .limit(1); + + if (existingNonInspectionPQ.length > 0) { + return { + success: false, + error: "미실사 PQ 목록은 하나만 생성할 수 있습니다. 먼저 활성화된 미실사 PQ를 비활성화한 후 생성해주세요." + }; + } + } + + // 프로젝트 PQ인 경우 중복 체크 + if (validated.type === "PROJECT" && validated.projectId) { + // 프로젝트 정보 조회 (이름과 코드 포함) + const projectInfo = await db + .select({ + code: projects.code, + name: projects.name + }) + .from(projects) + .where(eq(projects.id, validated.projectId)) + .limit(1) + .then(rows => rows[0]); + + const existingPQ = await db + .select() + .from(pqLists) + .where( + and( + eq(pqLists.projectId, validated.projectId), + eq(pqLists.type, "PROJECT"), + eq(pqLists.isDeleted, false) + ) + ) + .limit(1); + + if (existingPQ.length > 0) { + const projectDisplayName = projectInfo ? `${projectInfo.code} - ${projectInfo.name}` : "알 수 없는 프로젝트"; + return { + success: false, + error: `${projectDisplayName} 프로젝트에 대한 PQ가 이미 존재합니다` + }; + } + } + return await db.transaction(async (tx) => { + const now = new Date(); + const [newPqList] = await tx + .insert(pqLists) + .values({ + ...validated, + isDeleted: false, + createdAt: now, + updatedAt: now, + createdBy: userId, + updatedBy: userId, + }) + .returning(); + + // NON_INSPECTION 타입인 경우 기존 활성화된 NON_INSPECTION 리스트들을 비활성화 + if (validated.type === "NON_INSPECTION") { + await tx + .update(pqLists) + .set({ isDeleted: true, updatedAt: now, updatedBy: userId }) + .where( + and( + eq(pqLists.type, "NON_INSPECTION"), + eq(pqLists.isDeleted, false), + ne(pqLists.id, newPqList.id) // 새로 생성한 리스트 제외 + ) + ); + } + + // 프로젝트 PQ인 경우 General PQ 항목들을 자동으로 복사 + let copiedCriteriaCount = 0; + if (validated.type === "PROJECT") { + // General PQ 목록 찾기 + const generalPqList = await tx + .select() + .from(pqLists) + .where( + and( + eq(pqLists.type, "GENERAL"), + eq(pqLists.isDeleted, false) + ) + ) + .limit(1) + .then(rows => rows[0]); + + if (generalPqList) { + // General PQ의 항목들 조회 + const generalCriterias = await tx + .select() + .from(pqCriterias) + .where(eq(pqCriterias.pqListId, generalPqList.id)); + + if (generalCriterias.length > 0) { + // 새로운 프로젝트 PQ에 항목들 복사 + const newCriterias = generalCriterias.map(criteria => ({ + code: criteria.code, + checkPoint: criteria.checkPoint, + description: criteria.description, + remarks: criteria.remarks, + groupName: criteria.groupName, + subGroupName: criteria.subGroupName, + pqListId: newPqList.id, + inputFormat: criteria.inputFormat, + createdAt: now, + updatedAt: now, + })); + + await tx.insert(pqCriterias).values(newCriterias); + copiedCriteriaCount = newCriterias.length; + } + } + } + + revalidateTag("pq-lists"); + revalidateTag("pq-criterias"); + return { + success: true, + data: newPqList, + copiedCriteriaCount + }; + }); + } catch (error) { + console.error("Error creating PQ list:", error); + + if (error instanceof z.ZodError) { + return { + success: false, + error: "유효성 검사 실패", + details: error.errors + }; + } + + return { + success: false, + error: "PQ 목록 생성에 실패했습니다" + }; + } +} +export async function deletePQListsAction(ids: number[]) { + try { + if (ids.length === 0) { + return { + success: false, + message: "삭제할 항목을 선택해주세요" + }; + } + console.log("ids", ids) + console.log("pqLists", pqLists) + const now = new Date(); + const updated = await db + .update(pqLists) + .set({ isDeleted: true, updatedAt: now }) + .where(inArray(pqLists.id, ids)) + .returning(); + + revalidateTag("pq-lists"); + return { + success: true, + data: updated, + message: `${updated.length}개의 PQ 목록이 비활성화되었습니다` + }; + } catch (error) { + console.error("Error deleting PQ lists:", error); + return { + success: false, + message: "PQ 목록 삭제에 실패했습니다" + }; + } +} + +export async function getPqListById(id: number) { + try { + const pqList = await db + .select() + .from(pqLists) + .where(and( + eq(pqLists.id, id), + eq(pqLists.isDeleted, false) + )) + .limit(1) + .then(rows => rows[0]); + + return pqList || null; + } catch (error) { + console.error("Error fetching PQ list by ID:", error); + return null; + } +} + +export async function getPqListCriteriaCount(listId: number) { + try { + const result = await db + .select({ count: count() }) + .from(pqCriterias) + .where(eq(pqCriterias.pqListId, listId)); + + return result[0]?.count || 0; + } catch (error) { + console.error("Error getting PQ list criteria count:", error); + return 0; + } +} + + + +export async function copyPQListAction(input: CopyPqListInput) { + try { + const validated = copyPqListSchema.parse(input); + const session = await getServerSession(authOptions); + const userId = session?.user?.id; + return await db.transaction(async (tx) => { + // 1. 원본 PQ 목록 조회 + const sourcePqList = await tx + .select() + .from(pqLists) + .where(eq(pqLists.id, validated.sourcePqListId)) + .limit(1) + .then(rows => rows[0]); + + if (!sourcePqList) { + return { + success: false, + error: "복사할 PQ 목록을 찾을 수 없습니다" + }; + } + + // 2. 프로젝트 PQ인 경우에만 대상 프로젝트에 이미 PQ가 존재하는지 확인 + if (sourcePqList.type === "PROJECT" && validated.targetProjectId) { + // 프로젝트 정보 조회 (이름과 코드 포함) + const projectInfo = await tx + .select({ + code: projects.code, + name: projects.name + }) + .from(projects) + .where(eq(projects.id, validated.targetProjectId)) + .limit(1) + .then(rows => rows[0]); + + const existingProjectPQ = await tx + .select() + .from(pqLists) + .where( + and( + eq(pqLists.projectId, validated.targetProjectId), + eq(pqLists.type, "PROJECT"), + eq(pqLists.isDeleted, false) + ) + ) + .limit(1); + + if (existingProjectPQ.length > 0) { + const projectDisplayName = projectInfo ? `${projectInfo.code} - ${projectInfo.name}` : "알 수 없는 프로젝트"; + return { + success: false, + error: `${projectDisplayName} 프로젝트에 대한 PQ가 이미 존재합니다` + }; + } + } + + // 3. 새 PQ 목록 생성 + const now = new Date(); + const newName = validated.newName || `${sourcePqList.name} (복사본)`; + + const [newPqList] = await tx + .insert(pqLists) + .values({ + name: newName || sourcePqList.name, + type: sourcePqList.type, + projectId: sourcePqList.type === "PROJECT" ? validated.targetProjectId : null, + isDeleted: false, + createdAt: now, + updatedAt: now, + createdBy: userId, + updatedBy: userId, + validTo: validated.validTo, + }) + .returning(); + + // 4. 원본 PQ 항목들 조회 및 복사 + const sourceCriterias = await tx + .select() + .from(pqCriterias) + .where(eq(pqCriterias.pqListId, validated.sourcePqListId)); + + if (sourceCriterias.length > 0) { + const newCriterias = sourceCriterias.map(criteria => ({ + code: criteria.code, + checkPoint: criteria.checkPoint, + description: criteria.description, + remarks: criteria.remarks, + groupName: criteria.groupName, + subGroupName: criteria.subGroupName, + pqListId: newPqList.id, + inputFormat: criteria.inputFormat, + createdAt: now, + updatedAt: now, + createdBy: userId, + updatedBy: userId, + })); + + await tx.insert(pqCriterias).values(newCriterias); + } + + revalidateTag("pq-lists"); + return { + success: true, + data: newPqList, + copiedCriteriaCount: sourceCriterias.length + }; + }); + } catch (error) { + console.error("Error copying PQ list:", error); + + if (error instanceof z.ZodError) { + return { + success: false, + error: "유효성 검사 실패", + details: error.errors + }; + } + + return { + success: false, + error: error instanceof Error ? error.message : "PQ 목록 복사에 실패했습니다" + }; + } +} +export type Project = { + id: number; + projectCode: string; + projectName: string; + pjtType: string; +} +// ----------------------------------------------------------------------------- +export async function getProjects() { + try { + const projectList = await db.transaction(async (tx) => { + const results = await tx + .select({ + id: projects.id, + code: projects.code, + name: projects.name, + type: projects.type, + createdAt: projects.createdAt, + updatedAt: projects.updatedAt, + }) + .from(projects) + .orderBy(projects.code); + + return results; + }); + + return projectList; + } catch (error) { + console.error("프로젝트 목록 가져오기 실패:", error); + return []; + } +} + +// PQ 리스트에 등재된 프로젝트만 가져오는 함수 +export async function getProjectsWithPQList() { + try { + const projectList = await db.transaction(async (tx) => { + const results = await tx + .select({ + id: projects.id, + projectCode: projects.code, + projectName: projects.name, + pjtType: projects.type, + type: projects.type, + createdAt: projects.createdAt, + updatedAt: projects.updatedAt, + }) + .from(projects) + .innerJoin(pqLists, eq(projects.id, pqLists.projectId)) + .where( + and( + eq(pqLists.type, "PROJECT"), + eq(pqLists.isDeleted, false) + ) + ) + .orderBy(projects.code); + + return results; + }); + + return projectList; + } catch (error) { + console.error("PQ 리스트 등재 프로젝트 목록 가져오기 실패:", error); + return []; + } +} +// ----------------------------------------------------------------------------- +// PQ Criteria CRUD 액션 - 개선된 버전 +// ----------------------------------------------------------------------------- + +// PQ 항목 생성 (특정 PQ 목록에 속함) +export async function createPqCriteria( + pqListId: number, + input: { + code: string; + checkPoint: string; + groupName: string; + subGroupName?: string; + description?: string; + remarks?: string; + inputFormat?: string; + type?: string | null; + attachments?: { + fileName: string; + url: string; + size?: number; + originalFileName?: string; + }[]; + } +) { + try { + const now = new Date(); + const [newCriteria] = await db + .insert(pqCriterias) + .values({ + code: input.code, + checkPoint: input.checkPoint, + description: input.description || null, + remarks: input.remarks || null, + groupName: input.groupName, + subGroupName: input.subGroupName || null, + pqListId: pqListId, + inputFormat: input.inputFormat || "TEXT", + type: input.type || "내외자", + createdAt: now, + updatedAt: now, + }) + .returning(); + + // 첨부파일 저장 + if (input.attachments && input.attachments.length > 0) { + await db.insert(pqCriteriasAttachments).values( + input.attachments.map((attach) => ({ + pqCriteriaId: newCriteria.id, + fileName: attach.fileName, + originalFileName: attach.originalFileName || attach.fileName, + filePath: attach.url, + fileSize: attach.size ?? null, + createdAt: now, + updatedAt: now, + })) + ); + } + + revalidateTag("pq-criterias"); + return { + success: true, + data: newCriteria, + message: "PQ 항목이 성공적으로 생성되었습니다" + }; + } catch (error) { + console.error("Error creating PQ criteria:", error); + return { + success: false, + message: "PQ 항목 생성에 실패했습니다" + }; + } +} + +// PQ 항목 수정 +export async function updatePqCriteria( + id: number, + input: { + code: string; + checkPoint: string; + groupName: string; + subGroupName?: string; + description?: string; + remarks?: string; + inputFormat?: string; + type?: string | null; + attachments?: { + fileName: string; + url: string; + size?: number; + originalFileName?: string; + }[]; + } +) { + try { + const now = new Date(); + const [updatedCriteria] = await db + .update(pqCriterias) + .set({ + code: input.code, + checkPoint: input.checkPoint, + description: input.description || null, + remarks: input.remarks || null, + groupName: input.groupName, + subGroupName: input.subGroupName || null, + inputFormat: input.inputFormat || "TEXT", + type: input.type || "내외자", + updatedAt: now, + }) + .where(eq(pqCriterias.id, id)) + .returning(); + + if (!updatedCriteria) { + return { + success: false, + message: "수정할 PQ 항목을 찾을 수 없습니다" + }; + } + + // 첨부파일 동기화 (입력된 경우에만) + if (input.attachments) { + const existing = await db + .select({ + id: pqCriteriasAttachments.id, + filePath: pqCriteriasAttachments.filePath, + }) + .from(pqCriteriasAttachments) + .where(eq(pqCriteriasAttachments.pqCriteriaId, id)); + + const newPaths = input.attachments.map((a) => a.url); + const toDelete = existing.filter((e) => !newPaths.includes(e.filePath)); + if (toDelete.length > 0) { + await db + .delete(pqCriteriasAttachments) + .where(inArray(pqCriteriasAttachments.id, toDelete.map((d) => d.id))); + } + + const existingPaths = existing.map((e) => e.filePath); + const toAdd = input.attachments.filter((a) => !existingPaths.includes(a.url)); + if (toAdd.length > 0) { + await db.insert(pqCriteriasAttachments).values( + toAdd.map((attach) => ({ + pqCriteriaId: id, + fileName: attach.fileName, + originalFileName: attach.originalFileName || attach.fileName, + filePath: attach.url, + fileSize: attach.size ?? null, + createdAt: now, + updatedAt: now, + })) + ); + } + } + + revalidateTag("pq-criterias"); + return { + success: true, + data: updatedCriteria, + message: "PQ 항목이 성공적으로 수정되었습니다" + }; + } catch (error) { + console.error("Error updating PQ criteria:", error); + return { + success: false, + message: "PQ 항목 수정에 실패했습니다" + }; + } +} + +// PQ 항목 삭제 +export async function deletePqCriterias(ids: number[]) { + try { + if (ids.length === 0) { + return { + success: false, + message: "삭제할 항목을 선택해주세요" + }; + } + + const deletedCriterias = await db + .delete(pqCriterias) + .where(inArray(pqCriterias.id, ids)) + .returning(); + + revalidateTag("pq-criterias"); + return { + success: true, + data: deletedCriterias, + message: `${deletedCriterias.length}개의 PQ 항목이 삭제되었습니다` + }; + } catch (error) { + console.error("Error deleting PQ criterias:", error); + return { + success: false, + message: "PQ 항목 삭제에 실패했습니다" + }; + } +} + +/** + * PQ 제출 삭제 함수 (REQUESTED 상태일 때만 삭제 가능) + */ +export async function deletePQSubmissionAction(pqSubmissionId: number) { + try { + // PQ 제출 정보 조회 + const submission = await db + .select() + .from(vendorPQSubmissions) + .where(eq(vendorPQSubmissions.id, pqSubmissionId)) + .limit(1); + + if (submission.length === 0) { + return { success: false, error: "PQ 제출을 찾을 수 없습니다." }; + } + + const pqSubmission = submission[0]; + + // REQUESTED 상태가 아니면 삭제 불가 + if (pqSubmission.status !== "REQUESTED") { + return { success: false, error: "요청됨 상태가 아닌 PQ는 삭제할 수 없습니다." }; + } + + // 트랜잭션으로 관련 데이터 모두 삭제 + await db.transaction(async (tx) => { + // 1. PQ 답변 삭제 (vendorId와 projectId로 식별) + await tx + .delete(vendorPqCriteriaAnswers) + .where( + and( + eq(vendorPqCriteriaAnswers.vendorId, pqSubmission.vendorId), + pqSubmission.projectId + ? eq(vendorPqCriteriaAnswers.projectId, pqSubmission.projectId) + : isNull(vendorPqCriteriaAnswers.projectId) + ) + ); + + // 2. 첨부파일 삭제 (vendorCriteriaAnswerId로 연결) + const answerIds = await tx + .select({ id: vendorPqCriteriaAnswers.id }) + .from(vendorPqCriteriaAnswers) + .where( + and( + eq(vendorPqCriteriaAnswers.vendorId, pqSubmission.vendorId), + pqSubmission.projectId + ? eq(vendorPqCriteriaAnswers.projectId, pqSubmission.projectId) + : isNull(vendorPqCriteriaAnswers.projectId) + ) + ); + + if (answerIds.length > 0) { + await tx + .delete(vendorCriteriaAttachments) + .where(inArray(vendorCriteriaAttachments.vendorCriteriaAnswerId, answerIds.map(a => a.id))); + } + + + // 3-1. 기본계약서 삭제 + await tx + .delete(basicContract) + .where(eq(basicContract.pqSubmissionId, pqSubmissionId)); + // 3-2. PQ 제출 삭제 + await tx + .delete(vendorPQSubmissions) + .where(eq(vendorPQSubmissions.id, pqSubmissionId)); + }); + + // 삭제 후 캐시 무효화 (PQ 히스토리 캐시) + revalidateTag('pq-submissions'); + revalidateTag('basic-contract-requests'); + revalidatePath('/partners/basic-contract'); + revalidatePath('/evcp/basic-contract'); + + + return { success: true }; + } catch (error) { + console.error("deletePQSubmissionAction error:", error); + return { success: false, error: String(error) }; + } +} + +// PQ 목록별 항목 조회 (특정 pqListId에 속한 PQ 항목들) +// PQ 리스트 정보 조회 (상태 포함) +export async function getPQListInfo(pqListId: number) { + return unstable_cache( + async () => { + try { + const pqList = await db + .select({ + id: pqLists.id, + name: pqLists.name, + type: pqLists.type, + projectId: pqLists.projectId, + validTo: pqLists.validTo, + isDeleted: pqLists.isDeleted, + createdAt: pqLists.createdAt, + updatedAt: pqLists.updatedAt, + }) + .from(pqLists) + .where(and( + eq(pqLists.id, pqListId), + eq(pqLists.isDeleted, false) + )) + .limit(1) + .then(rows => rows[0]); + + if (!pqList) { + return { + success: false, + error: "PQ 목록을 찾을 수 없습니다" + }; + } + + // 현재 시간과 비교하여 상태 결정 + const now = new Date(); + const isValid = !pqList.validTo || pqList.validTo > now; + const status = isValid ? "ACTIVE" : "INACTIVE"; + + return { + success: true, + data: { + ...pqList, + status + } + }; + } catch (error) { + console.error("Error in getPQListInfo:", error); + return { + success: false, + error: "PQ 목록 정보를 가져오는 중 오류가 발생했습니다" + }; + } + }, + [`pq-list-info-${pqListId}`], + { + tags: ["pq-lists"], + revalidate: 3600, // 1시간 + } + )(); +} + +// 활성화된 미실사 PQ 리스트 조회 (하나만 존재한다고 가정) +export async function getNonInspectionPQLists() { + unstable_noStore(); + + try { + const pqList = await db + .select({ + id: pqLists.id, + name: pqLists.name, + type: pqLists.type, + projectId: pqLists.projectId, + validTo: pqLists.validTo, + isDeleted: pqLists.isDeleted, + createdAt: pqLists.createdAt, + updatedAt: pqLists.updatedAt, + }) + .from(pqLists) + .where(and( + eq(pqLists.type, "NON_INSPECTION"), + eq(pqLists.isDeleted, false) + )) + .limit(1) + .then(rows => rows[0]); + + if (!pqList) { + return { + success: false, + error: "활성화된 미실사 PQ 목록을 찾을 수 없습니다" + }; + } + + // 현재 시간과 비교하여 상태 결정 + const now = new Date(); + const isValid = !pqList.validTo || pqList.validTo > now; + const status = isValid ? "ACTIVE" : "INACTIVE"; + + return { + success: true, + data: { + ...pqList, + status + } + }; + } catch (error) { + console.error("Error in getNonInspectionPQLists:", error); + return { + success: false, + error: "미실사 PQ 목록 정보를 가져오는 중 오류가 발생했습니다" + }; + } +} + +export async function getPQsByListId(pqListId: number, input: GetPQSchema) { + return unstable_cache( + async () => { + try { + const offset = (input.page - 1) * input.perPage; + + // 검색 조건 + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + ilike(pqCriterias.code, s), + ilike(pqCriterias.groupName, s), + ilike(pqCriterias.subGroupName, s), + ilike(pqCriterias.remarks, s), + ilike(pqCriterias.checkPoint, s), + ilike(pqCriterias.description, s) + ); + } + + // 고급 필터 + const advancedWhere = input.filters + ? filterColumns({ table: pqCriterias, filters: input.filters, joinOperator: input.joinOperator }) + : undefined; + + // 최종 WHERE 조건 (pqListId 조건 추가) + const finalWhere = and( + eq(pqCriterias.pqListId, pqListId), // 특정 PQ 목록에 속한 항목들만 + advancedWhere, + globalWhere + ); + + // 정렬 + const orderBy = input.sort.length + ? input.sort.map((s) => (s.desc ? desc(pqCriterias[s.id]) : asc(pqCriterias[s.id]))) + : [asc(pqCriterias.createdAt)]; + + const { data, total } = await db.transaction(async (tx) => { + // 데이터 조회 + const data = await tx + .select({ + id: pqCriterias.id, + code: pqCriterias.code, + checkPoint: pqCriterias.checkPoint, + description: pqCriterias.description, + remarks: pqCriterias.remarks, + groupName: pqCriterias.groupName, + subGroupName: pqCriterias.subGroupName, + pqListId: pqCriterias.pqListId, + inputFormat: pqCriterias.inputFormat, + type: pqCriterias.type, + + createdAt: pqCriterias.createdAt, + updatedAt: pqCriterias.updatedAt, + }) + .from(pqCriterias) + .where(finalWhere) + .orderBy(...orderBy) + .offset(offset) + .limit(input.perPage); + + // 카운트 조회 + const countRes = await tx + .select({ count: count() }) + .from(pqCriterias) + .where(finalWhere); + + const total = countRes[0]?.count ?? 0; + + return { data, total }; + }); + + const pageCount = Math.ceil(total / input.perPage); + return { data, pageCount }; + } catch (err) { + console.error("Error in getPQsByListId:", err); + return { data: [], pageCount: 0 }; + } + }, + [JSON.stringify(input), pqListId.toString()], + { revalidate: 3600, tags: ["pq-criterias"] } + )(); +} + +// 실사 정보 업데이트 액션 (구매자체평가용) +export async function updateInvestigationDetailsAction(input: { + investigationId: number; + confirmedAt?: Date; + evaluationResult?: "APPROVED" | "SUPPLEMENT" | "REJECTED"; + investigationNotes?: string; + attachments?: File[]; +}) { + try { + const updateData: any = { + updatedAt: new Date(), + }; + + if (input.confirmedAt !== undefined) { + updateData.confirmedAt = input.confirmedAt; + } + + if (input.evaluationResult !== undefined) { + updateData.evaluationResult = input.evaluationResult; + } + + if (input.investigationNotes !== undefined) { + updateData.investigationNotes = input.investigationNotes; + } + // evaluationResult가 APPROVED라면 investigationStatus를 "COMPLETED"(완료됨)로 변경 + if (input.evaluationResult === "APPROVED") { + updateData.investigationStatus = "COMPLETED"; + } + + // 트랜잭션으로 실사 정보 업데이트와 첨부파일 저장을 함께 처리 + await db.transaction(async (tx) => { + // 1. 실사 정보 업데이트 + await tx + .update(vendorInvestigations) + .set(updateData) + .where(eq(vendorInvestigations.id, input.investigationId)); + + // 2. 첨부파일 처리 + if (input.attachments && input.attachments.length > 0) { + for (const file of input.attachments) { + try { + console.log(`📁 실사 첨부파일 처리 중: ${file.name} (${file.size} bytes)`); + + // saveDRMFile을 사용하여 파일 저장 + const saveResult = await saveDRMFile( + file, + decryptWithServerAction, + `vendor-investigation/${input.investigationId}`, + "investigation-update" + ); + + if (!saveResult.success) { + console.error(`❌ 파일 저장 실패: ${file.name}`, saveResult.error); + throw new Error(`파일 저장 실패: ${file.name} - ${saveResult.error}`); + } + + console.log(`✅ 파일 저장 완료: ${file.name} -> ${saveResult.fileName}`); + + // 파일 타입 결정 + let attachmentType = "OTHER"; + if (file.type.includes("pdf")) { + attachmentType = "REPORT"; + } else if (file.type.includes("image")) { + attachmentType = "PHOTO"; + } else if ( + file.type.includes("word") || + file.type.includes("document") || + file.name.toLowerCase().includes("report") + ) { + attachmentType = "DOCUMENT"; + } + + // DB에 첨부파일 레코드 생성 + await tx.insert(vendorInvestigationAttachments).values({ + investigationId: input.investigationId, + fileName: saveResult.fileName!, + originalFileName: file.name, + filePath: saveResult.publicPath!, + fileSize: file.size, + mimeType: file.type || 'application/octet-stream', + attachmentType: attachmentType as "REPORT" | "PHOTO" | "DOCUMENT" | "CERTIFICATE" | "OTHER", + }); + + } catch (error) { + console.error(`❌ 첨부파일 처리 오류: ${file.name}`, error); + throw new Error(`첨부파일 처리 중 오류가 발생했습니다: ${file.name}`); + } + } + } + }); + + revalidateTag("pq-submissions"); + revalidatePath("/evcp/pq_new"); + + return { + success: true, + message: "실사 정보가 성공적으로 업데이트되었습니다." + }; + + } catch (error) { + console.error("실사 정보 업데이트 오류:", error); + const errorMessage = error instanceof Error ? error.message : "실사 정보 업데이트 중 오류가 발생했습니다."; + return { + success: false, + error: errorMessage + }; + } +} + +// 구매자체평가 첨부파일 조회 +export async function getInvestigationAttachments(investigationId: number) { + try { + const attachments = await db + .select({ + id: vendorInvestigationAttachments.id, + fileName: vendorInvestigationAttachments.fileName, + originalFileName: vendorInvestigationAttachments.originalFileName, + filePath: vendorInvestigationAttachments.filePath, + fileSize: vendorInvestigationAttachments.fileSize, + mimeType: vendorInvestigationAttachments.mimeType, + attachmentType: vendorInvestigationAttachments.attachmentType, + createdAt: vendorInvestigationAttachments.createdAt, + }) + .from(vendorInvestigationAttachments) + .where(eq(vendorInvestigationAttachments.investigationId, investigationId)) + .orderBy(desc(vendorInvestigationAttachments.createdAt)); + + return { + success: true, + attachments, + }; + } catch (error) { + console.error("첨부파일 조회 오류:", error); + return { + success: false, + error: "첨부파일 조회 중 오류가 발생했습니다.", + attachments: [], + }; + } +} + +// 구매자체평가 첨부파일 삭제 +export async function deleteInvestigationAttachment(attachmentId: number) { + try { + await db + .delete(vendorInvestigationAttachments) + .where(eq(vendorInvestigationAttachments.id, attachmentId)); + + revalidateTag("pq-submissions"); + revalidatePath("/evcp/pq_new"); + + return { + success: true, + message: "첨부파일이 성공적으로 삭제되었습니다.", + }; + } catch (error) { + console.error("첨부파일 삭제 오류:", error); + return { + success: false, + error: "첨부파일 삭제 중 오류가 발생했습니다.", + }; + } +} + +export async function autoDeactivateExpiredPQLists() { + try { + const now = new Date(); + + // 유효기간이 지난 PQ 리스트들을 비활성화 + const expiredLists = await db + .update(pqLists) + .set({ + isDeleted: true, + updatedAt: now + }) + .where( + and( + eq(pqLists.isDeleted, false), + lt(pqLists.validTo, now), + isNotNull(pqLists.validTo) + ) + ) + .returning(); + + console.log(`[PQ Auto Deactivation] ${expiredLists.length}개의 만료된 PQ 리스트가 비활성화되었습니다.`); + + if (expiredLists.length > 0) { + revalidateTag("pq-lists"); + } + + return { + success: true, + deactivatedCount: expiredLists.length, + message: `${expiredLists.length}개의 만료된 PQ 리스트가 비활성화되었습니다.` + }; + } catch (error) { + console.error("Error auto-deactivating expired PQ lists:", error); + return { + success: false, + message: "만료된 PQ 리스트 자동 비활성화에 실패했습니다." + }; + } +} + +// PQ 유효일 수정 서버액션 +export async function updatePqValidToAction(input: UpdatePqValidToInput) { + try { + const validated = updatePqValidToSchema.parse(input); + const session = await getServerSession(authOptions); + const userId = session?.user?.id; + + if (!userId) { + return { + success: false, + error: "인증이 필요합니다" + }; + } + + // PQ 목록 존재 확인 + const existingPqList = await db + .select() + .from(pqLists) + .where(eq(pqLists.id, validated.pqListId)) + .limit(1) + .then(rows => rows[0]); + + if (!existingPqList) { + return { + success: false, + error: "PQ 목록을 찾을 수 없습니다" + }; + } + + // 유효일 업데이트 + await db + .update(pqLists) + .set({ + validTo: validated.validTo, + updatedAt: new Date(), + updatedBy: userId, + }) + .where(eq(pqLists.id, validated.pqListId)); + + // 캐시 재검증 + revalidateTag("pq-lists"); + + return { + success: true, + message: "유효일이 성공적으로 수정되었습니다" + }; + } catch (error) { + console.error("Error updating PQ valid to:", error); + if (error instanceof z.ZodError) { + return { + success: false, + error: "입력 데이터가 올바르지 않습니다" + }; + } + return { + success: false, + error: "유효일 수정에 실패했습니다" + }; + } +} + + +// SHI 참석자 총 인원수 계산 함수 + +export async function getTotalShiAttendees(shiAttendees: Record | null): Promise { + if (!shiAttendees) return 0 + + let total = 0 + Object.entries(shiAttendees).forEach(([key, value]) => { + if (value && typeof value === 'object' && 'checked' in value && 'count' in value) { + const attendee = value as { checked: boolean; count: number } + if (attendee.checked) { + total += attendee.count + } + } + }) + return total } \ No newline at end of file diff --git a/lib/pq/table/pq-lists-toolbar.tsx b/lib/pq/table/pq-lists-toolbar.tsx index 1feb9a1a..81ffc9d0 100644 --- a/lib/pq/table/pq-lists-toolbar.tsx +++ b/lib/pq/table/pq-lists-toolbar.tsx @@ -2,9 +2,10 @@ import * as React from "react" import { Button } from "@/components/ui/button" -import { Trash, CopyPlus, Plus, RefreshCw } from "lucide-react" +import { CopyPlus, Plus, RefreshCw } from "lucide-react" import { type Table } from "@tanstack/react-table" import type { PQList } from "./pq-lists-columns" +import { RedFlagManagersDialog } from "@/lib/compliance/table/red-flag-managers-dialog" // import { PqListForm } from "./add-pq-list-dialog" interface PQListsToolbarActionsProps { @@ -38,6 +39,7 @@ export function PQListsToolbarActions({ return (
+ {selected.length > 0 && (allActive || allDeleted) && newState !== undefined && (