3/25 까지의 대표님 작업사항

1 files changed, 987 insertions, 0 deletions

diff --git a/lib/pq/service.ts b/lib/pq/service.ts
new file mode 100644
index 00000000..a1373dae
--- /dev/null
+++ b/lib/pq/service.ts
@@ -0,0 +1,987 @@
+"use server"
+
+import db from "@/db/db"
+import { GetPQSchema } 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, count} from "drizzle-orm";
+import { z } from "zod"
+import { revalidateTag, unstable_noStore, revalidatePath} from "next/cache";
+import { pqCriterias, vendorCriteriaAttachments, vendorPqCriteriaAnswers, vendorPqReviewLogs } from "@/db/schema/pq"
+import { countPqs, selectPqs } from "./repository";
+import { sendEmail } from "../mail/sendEmail";
+import { vendorAttachments, vendors } from "@/db/schema/vendors";
+import path from 'path';
+import fs from 'fs/promises';
+import { randomUUID } from 'crypto';
+import { writeFile, mkdir } from 'fs/promises';
+import { GetVendorsSchema } from "../vendors/validations";
+import { countVendors, selectVendors } from "../vendors/repository";
+
+/**
+ * PQ 목록 조회
+ */
+export async function getPQs(input: GetPQSchema) {
+    return unstable_cache(
+        async () => {
+            try {
+                const offset = (input.page - 1) * input.perPage;
+
+                // advancedTable 모드면 filterColumns()로 where 절 구성
+                const advancedWhere = filterColumns({
+                    table: pqCriterias,
+                    filters: input.filters,
+                    joinOperator: input.joinOperator,
+                });
+
+                let globalWhere
+                if (input.search) {
+                    const s = `%${input.search}%`
+                    globalWhere = or(ilike(pqCriterias.code, s), ilike(pqCriterias.groupName, s), ilike(pqCriterias.remarks, s), ilike(pqCriterias.checkPoint, s), ilike(pqCriterias.description, s)
+                    )
+                }
+
+                const finalWhere = and(advancedWhere, globalWhere);
+                const orderBy =
+                    input.sort.length > 0
+                        ? input.sort.map((item) =>
+                            item.desc ? desc(pqCriterias[item.id]) : asc(pqCriterias[item.id])
+                        )
+                        : [asc(pqCriterias.createdAt)];
+
+                const { data, total } = await db.transaction(async (tx) => {
+                    const data = await selectPqs(tx, {
+                        where: finalWhere,
+                        orderBy,
+                        offset,
+                        limit: input.perPage,
+                    });
+                    const total = await countPqs(tx, finalWhere);
+                    return { data, total };
+                });
+
+                const pageCount = Math.ceil(total / input.perPage);
+
+
+                return { data, pageCount };
+            } catch (err) {
+                return { data: [], pageCount: 0 };
+            }
+        },
+        [JSON.stringify(input)],
+        {
+            revalidate: 3600,
+            tags: [`pq`],
+        }
+    )();
+}
+
+// PQ 생성을 위한 입력 스키마 정의
+const createPqSchema = z.object({
+    code: z.string().min(1, "Code is required"),
+    checkPoint: z.string().min(1, "Check point is required"),
+    description: z.string().optional(),
+    remarks: z.string().optional(),
+    groupName: z.string().optional()
+});
+
+export type CreatePqInputType = z.infer<typeof createPqSchema>;
+
+/**
+ * PQ 기준 생성
+ */
+export async function createPq(input: CreatePqInputType) {
+  try {
+    // 입력 유효성 검증
+    const validatedData = createPqSchema.parse(input);
+
+    // 트랜잭션 사용하여 PQ 기준 생성
+    return await db.transaction(async (tx) => {
+      // PQ 기준 생성
+      const [newPqCriteria] = await tx
+        .insert(pqCriterias)
+        .values({
+          code: validatedData.code,
+          checkPoint: validatedData.checkPoint,
+          description: validatedData.description || null,
+          remarks: validatedData.remarks || null,
+          groupName: validatedData.groupName || null,
+        })
+        .returning({ id: pqCriterias.id });
+
+      // 성공 결과 반환
+      return { 
+        success: true, 
+        pqId: newPqCriteria.id,
+        message: "PQ criteria created successfully" 
+      };
+    });
+  } catch (error) {
+    console.error("Error creating PQ criteria:", error);
+    
+    // Zod 유효성 검사 에러 처리
+    if (error instanceof z.ZodError) {
+      return { 
+        success: false, 
+        message: "Validation failed", 
+        errors: error.errors 
+      };
+    }
+    
+    // 기타 에러 처리
+    return { 
+      success: false, 
+      message: "Failed to create PQ criteria" 
+    };
+  }
+}
+
+// PQ 캐시 무효화 함수
+export async function invalidatePqCache() {
+  revalidatePath(`/evcp/pq-criteria`);
+  revalidateTag(`pq`);
+}
+
+// PQ 삭제를 위한 스키마 정의
+const removePqsSchema = z.object({
+  ids: z.array(z.number()).min(1, "At least one PQ ID is required")
+});
+
+export type RemovePqsInputType = z.infer<typeof removePqsSchema>;
+
+/**
+ * PQ 기준 삭제
+ */
+export async function removePqs(input: RemovePqsInputType) {
+  try {
+    // 입력 유효성 검증
+    const validatedData = removePqsSchema.parse(input);
+    
+    // 트랜잭션 사용하여 PQ 기준 삭제
+    await db.transaction(async (tx) => {
+      // PQ 기준 테이블에서 삭제
+      await tx
+        .delete(pqCriterias)
+        .where(inArray(pqCriterias.id, validatedData.ids));
+    });
+    
+    // 캐시 무효화
+    await invalidatePqCache();
+
+    return { success: true };
+  } catch (error) {
+    console.error("Error removing PQ criteria:", error);
+    
+    // Zod 유효성 검사 에러 처리
+    if (error instanceof z.ZodError) {
+      return { 
+        success: false, 
+        error: "Validation failed: " + error.errors.map(e => e.message).join(', ')
+      };
+    }
+    
+    // 기타 에러 처리
+    return { 
+      success: false, 
+      error: "Failed to remove PQ criteria"
+    };
+  }
+}
+
+// PQ 수정을 위한 스키마 정의
+const modifyPqSchema = z.object({
+  id: z.number().positive("ID is required"),
+  code: z.string().min(1, "Code is required"),
+  checkPoint: z.string().min(1, "Check point is required"),
+  groupName: z.string().min(1, "Group is required"),
+  description: z.string().optional(),
+  remarks: z.string().optional()
+});
+
+export type ModifyPqInputType = z.infer<typeof modifyPqSchema>;
+
+
+export async function modifyPq(input: ModifyPqInputType) {
+  try {
+    // 입력 유효성 검증
+    const validatedData = modifyPqSchema.parse(input);
+
+    // 트랜잭션 사용하여 PQ 기준 수정
+    return await db.transaction(async (tx) => {
+      // PQ 기준 수정
+      await tx
+        .update(pqCriterias)
+        .set({
+          code: validatedData.code,
+          checkPoint: validatedData.checkPoint,
+          description: validatedData.description || null,
+          remarks: validatedData.remarks || null,
+          groupName: validatedData.groupName,
+          updatedAt: new Date(),
+        })
+        .where(eq(pqCriterias.id, validatedData.id));
+
+      // 성공 결과 반환
+      return { 
+        success: true, 
+        message: "PQ criteria updated successfully" 
+      };
+    });
+  } catch (error) {
+    console.error("Error updating PQ criteria:", error);
+    
+    // Zod 유효성 검사 에러 처리
+    if (error instanceof z.ZodError) {
+      return { 
+        success: false, 
+        error: "Validation failed: " + error.errors.map(e => e.message).join(', ')
+      };
+    }
+    
+    // 기타 에러 처리
+    return { 
+      success: false, 
+      error: "Failed to update PQ criteria" 
+    };
+  } finally {
+    // 캐시 무효화
+    revalidatePath(`/partners/pq`);
+    revalidateTag(`pq`);
+  }
+}
+
+export interface PQAttachment {
+  attachId: number
+  fileName: string
+  filePath: string
+  fileSize?: number
+}
+
+export interface PQItem {
+  answerId: number | null; // null도 허용하도록 변경
+  criteriaId: number
+  code: string
+  checkPoint: string
+  description: string | null
+  answer: string // or null
+  attachments: PQAttachment[]
+}
+
+export interface PQGroupData {
+  groupName: string
+  items: PQItem[]
+}
+
+ 
+export async function getPQDataByVendorId(vendorId: number): Promise<PQGroupData[]> {
+  // 1) Query: pqCriterias
+  //    LEFT JOIN vendorPqCriteriaAnswers (to get `answer`)
+  //    LEFT JOIN vendorCriteriaAttachments (to get each attachment row)
+  const rows = await db
+    .select({
+      criteriaId: pqCriterias.id,
+      groupName: pqCriterias.groupName,
+      code: pqCriterias.code,
+      checkPoint: pqCriterias.checkPoint,
+      description: pqCriterias.description,
+
+      // From vendorPqCriteriaAnswers
+      answer: vendorPqCriteriaAnswers.answer,     // can be null if no row exists
+      answerId: vendorPqCriteriaAnswers.id,       // internal PK if needed
+
+      // From vendorCriteriaAttachments
+      attachId: vendorCriteriaAttachments.id,
+      fileName: vendorCriteriaAttachments.fileName,
+      filePath: vendorCriteriaAttachments.filePath,
+      fileSize: vendorCriteriaAttachments.fileSize,
+    })
+    .from(pqCriterias)
+    .leftJoin(
+      vendorPqCriteriaAnswers,
+      and(
+        eq(pqCriterias.id, vendorPqCriteriaAnswers.criteriaId),
+        eq(vendorPqCriteriaAnswers.vendorId, vendorId)
+      )
+    )
+    .leftJoin(
+      vendorCriteriaAttachments,
+      eq(vendorPqCriteriaAnswers.id, vendorCriteriaAttachments.vendorCriteriaAnswerId)
+    )
+    .orderBy(pqCriterias.groupName, pqCriterias.code)
+
+  // 2) Group by groupName => each group has a map of criteriaId => PQItem
+  //    so we can gather attachments properly.
+  const groupMap = new Map<string, Record<number, PQItem>>()
+
+  for (const row of rows) {
+    const g = row.groupName || "Others"
+
+    // Ensure we have an object for this group
+    if (!groupMap.has(g)) {
+      groupMap.set(g, {})
+    }
+
+    const groupItems = groupMap.get(g)!
+    // If we haven't seen this criteriaId yet, create a PQItem
+    if (!groupItems[row.criteriaId]) {
+      groupItems[row.criteriaId] = {
+        answerId: row.answerId,
+        criteriaId: row.criteriaId,
+        code: row.code,
+        checkPoint: row.checkPoint,
+        description: row.description,
+        answer: row.answer || "",    // if row.answer is null, just empty string
+        attachments: [],
+      }
+    }
+
+    // If there's an attachment row (attachId not null), push it onto `attachments`
+    if (row.attachId) {
+      groupItems[row.criteriaId].attachments.push({
+        attachId: row.attachId,
+        fileName: row.fileName || "",
+        filePath: row.filePath || "",
+        fileSize: row.fileSize || undefined,
+      })
+    }
+  }
+
+  // 3) Convert groupMap into an array of { groupName, items[] }
+  const data: PQGroupData[] = []
+  for (const [groupName, itemsMap] of groupMap.entries()) {
+    // Convert the itemsMap (key=criteriaId => PQItem) into an array
+    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
+  attachments: PQAttachmentInput[]
+}
+
+interface SavePQInput {
+  vendorId: number
+  answers: SavePQAnswer[]
+}
+
+/**
+ * 여러 항목을 한 번에 Upsert
+ */
+export async function savePQAnswersAction(input: SavePQInput) {
+  const { vendorId, answers } = input
+
+  try {
+    for (const ans of answers) {
+      // 1) Check if a row already exists for (vendorId, criteriaId)
+      const existing = await db
+        .select()
+        .from(vendorPqCriteriaAnswers)
+        .where(
+          and(
+            eq(vendorPqCriteriaAnswers.vendorId, vendorId),
+            eq(vendorPqCriteriaAnswers.criteriaId, ans.criteriaId)
+          )
+        )
+
+      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,
+            answer: ans.answer,
+            // no attachmentPaths column anymore
+          })
+          .returning({ id: vendorPqCriteriaAnswers.id })
+        
+        answerId = inserted[0].id
+      } else {
+        // Update existing
+        answerId = existing[0].id
+
+        await db
+          .update(vendorPqCriteriaAnswers)
+          .set({
+            answer: ans.answer,
+            updatedAt: new Date(),
+          })
+          .where(eq(vendorPqCriteriaAnswers.id, answerId))
+      }
+
+      // 3) Now manage attachments in vendorCriteriaAttachments
+      //    We'll do a "diff": remove old ones not in the new list, insert new ones not in DB.
+
+      // 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,   // original filename
+          filePath: attach.url,        // random/UUID path on server
+          fileSize: attach.size ?? null,
+          // fileType if you have it, etc.
+        })
+      }
+    }
+
+    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: number) {
+  unstable_noStore();
+  
+  try {
+    // 1. 모든 PQ 항목에 대한 응답이 있는지 검증
+    const pqCriteriaCount = await db
+      .select({ count: count() })
+      .from(vendorPqCriteriaAnswers)
+      .where(eq(vendorPqCriteriaAnswers.vendorId, vendorId));
+    
+    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" };
+    }
+    
+    // 3. 벤더 상태가 제출 가능한 상태인지 확인
+    const allowedStatuses = ["IN_PQ", "PENDING_REVIEW", "IN_REVIEW", "REJECTED", "PQ_FAILED"];
+    if (!allowedStatuses.includes(vendor.status)) {
+      return { 
+        ok: false, 
+        error: `Cannot submit PQ in current status: ${vendor.status}` 
+      };
+    }
+    
+    // 4. 벤더 상태 업데이트
+    await db
+      .update(vendors)
+      .set({
+        status: "PQ_SUBMITTED",
+        updatedAt: new Date(),
+      })
+      .where(eq(vendors.id, vendorId));
+    
+    // 5. 관리자에게 이메일 알림 발송
+    if (process.env.ADMIN_EMAIL) {
+      try {
+        await sendEmail({
+          to: process.env.ADMIN_EMAIL,
+          subject: `[eVCP] PQ Submitted: ${vendor.vendorName}`,
+          template: "pq-submitted-admin",
+          context: {
+            vendorName: vendor.vendorName,
+            vendorId: vendor.id,
+            submittedDate: new Date().toLocaleString(),
+            adminUrl: `${process.env.NEXT_PUBLIC_APP_URL}/admin/vendors/${vendorId}/pq`,
+          }
+        });
+      } catch (emailError) {
+        console.error("Failed to send admin notification:", emailError);
+        // 이메일 실패는 전체 프로세스를 중단하지 않음
+      }
+    }
+    
+    // 6. 벤더에게 확인 이메일 발송
+    if (vendor.email) {
+      try {
+        await sendEmail({
+          to: vendor.email,
+          subject: "[eVCP] PQ Submission Confirmation",
+          template: "pq-submitted-vendor",
+          context: {
+            vendorName: vendor.vendorName,
+            submittedDate: new Date().toLocaleString(),
+            portalUrl: `${process.env.NEXT_PUBLIC_APP_URL}/dashboard`,
+          }
+        });
+      } catch (emailError) {
+        console.error("Failed to send vendor confirmation:", emailError);
+        // 이메일 실패는 전체 프로세스를 중단하지 않음
+      }
+    }
+    
+    // 7. 캐시 무효화
+    revalidateTag("vendors");
+    revalidateTag("vendor-status-counts");
+    
+    return { ok: true };
+  } catch (error) {
+    console.error("PQ submit error:", error);
+    return { ok: false, error: getErrorMessage(error) };
+  }
+}
+
+/**
+ * 향상된 파일 업로드 서버 액션
+ * - 직접 파일 처리 (file 객체로 받음)
+ * - 디렉토리 자동 생성
+ * - 중복 방지를 위한 UUID 적용
+ */
+export async function uploadFileAction(file: File) {
+  unstable_noStore();
+  
+  try {
+    // 파일 유효성 검사
+    if (!file || file.size === 0) {
+      throw new Error("Invalid file");
+    }
+    
+    const maxSize = 6e8;
+    if (file.size > maxSize) {
+      throw new Error(`File size exceeds limit (${Math.round(maxSize / 1024 / 1024)}MB)`);
+    }
+    
+    // 파일 확장자 가져오기
+    const originalFilename = file.name;
+    const fileExt = path.extname(originalFilename);
+    const fileNameWithoutExt = path.basename(originalFilename, fileExt);
+    
+    // 저장 경로 설정
+    const uploadDir = process.env.UPLOAD_DIR 
+    ? process.env.UPLOAD_DIR 
+    : path.join(process.cwd(), "public", "uploads")
+    const datePrefix = new Date().toISOString().slice(0, 10).replace(/-/g, ''); // YYYYMMDD
+    const targetDir = path.join(uploadDir, 'pq', datePrefix);
+    
+    // UUID로 고유 파일명 생성
+    const uuid = randomUUID();
+    const sanitizedFilename = fileNameWithoutExt
+      .replace(/[^a-zA-Z0-9-_]/g, '_') // 안전한 문자만 허용
+      .slice(0, 50); // 이름 길이 제한
+    
+    const filename = `${sanitizedFilename}-${uuid}${fileExt}`;
+    const filePath = path.join(targetDir, filename);
+    const relativeFilePath = path.join('pq', datePrefix, filename);
+    
+    // 디렉토리 생성 (없는 경우)
+    try {
+      await mkdir(targetDir, { recursive: true });
+    } catch (err) {
+      console.error("Error creating directory:", err);
+      throw new Error("Failed to create upload directory");
+    }
+    
+    // 파일 저장
+    const buffer = await file.arrayBuffer();
+    await writeFile(filePath, Buffer.from(buffer));
+    
+    // 상대 경로를 반환 (DB에 저장하기 용이함)
+    const publicUrl = `/uploads/${relativeFilePath.replace(/\\/g, '/')}`;
+    
+    return {
+      fileName: originalFilename,
+      url: publicUrl,
+      size: file.size,
+    };
+  } catch (error) {
+    console.error("File upload error:", error);
+    throw new Error(`Upload failed: ${getErrorMessage(error)}`);
+  }
+}
+
+/**
+ * 여러 파일 일괄 업로드
+ */
+export async function uploadMultipleFilesAction(files: File[]) {
+  unstable_noStore();
+  
+  try {
+    const results = [];
+    
+    for (const file of files) {
+      try {
+        const result = await uploadFileAction(file);
+        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("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;
+        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)
+          );
+        }
+
+        // 최종 where 결합
+        const finalWhere = and(advancedWhere, globalWhere, eq(vendors.status ,"PQ_SUBMITTED"));
+
+        // 간단 검색 (advancedTable=false) 시 예시
+        const simpleWhere = and(
+          input.vendorName
+            ? ilike(vendors.vendorName, `%${input.vendorName}%`)
+            : undefined,
+          input.status ? ilike(vendors.status, input.status) : undefined,
+          input.country
+            ? ilike(vendors.country, `%${input.country}%`)
+            : undefined
+        );
+
+        // 실제 사용될 where
+        const where = finalWhere;
+        
+        // 정렬
+        const orderBy =
+          input.sort.length > 0
+            ? input.sort.map((item) =>
+                item.desc ? desc(vendors[item.id]) : asc(vendors[item.id])
+              )
+            : [asc(vendors.createdAt)];
+
+        // 트랜잭션 내에서 데이터 조회
+        const { data, total } = await db.transaction(async (tx) => {
+          // 1) vendor 목록 조회
+          const vendorsData = await selectVendors(tx, {
+            where,
+            orderBy,
+            offset,
+            limit: input.perPage,
+          });
+
+          // 2) 각 vendor의 attachments 조회
+          const vendorsWithAttachments = await Promise.all(
+            vendorsData.map(async (vendor) => {
+              const attachments = await tx
+                .select({
+                  id: vendorAttachments.id,
+                  fileName: vendorAttachments.fileName,
+                  filePath: vendorAttachments.filePath,
+                })
+                .from(vendorAttachments)
+                .where(eq(vendorAttachments.vendorId, vendor.id));
+
+              return {
+                ...vendor,
+                hasAttachments: attachments.length > 0,
+                attachmentsList: attachments,
+              };
+            })
+          );
+
+          // 3) 전체 개수
+          const total = await countVendors(tx, where);
+          return { data: vendorsWithAttachments, total };
+        });
+
+        // 페이지 수
+        const pageCount = Math.ceil(total / input.perPage);
+
+        return { data, pageCount };
+      } catch (err) {
+        // 에러 발생 시
+        return { data: [], pageCount: 0 };
+      }
+    },
+    [JSON.stringify(input)], // 캐싱 키
+    {
+      revalidate: 3600,
+      tags: ["vendors-in-pq"], // revalidateTag("vendors") 호출 시 무효화
+    }
+  )();
+}
+
+
+export type VendorStatus =
+  | "PENDING_REVIEW"
+  | "IN_REVIEW"
+  | "REJECTED"
+  | "IN_PQ"
+  | "PQ_SUBMITTED"
+  | "PQ_FAILED"
+  | "APPROVED"
+  | "ACTIVE"
+  | "INACTIVE"
+  | "BLACKLISTED"
+
+  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" }
+      }
+  
+      // 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: `${process.env.NEXT_PUBLIC_URL}/partners/pq`,  // etc.
+        },
+      })
+      revalidateTag("vendors")
+      revalidateTag("vendors-in-pq")
+      return { ok: true }
+    } catch (error) {
+      console.error("updateVendorStatusAction 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,
+  comment,
+  generalComment,
+}: {
+  vendorId: number;
+  comment: ItemComment[];
+  generalComment?: string;
+}) {
+  try {
+    // 1) 벤더 상태 업데이트
+    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" };
+    }
+
+    // 3) 각 항목별 코멘트 저장
+    const currentDate = new Date();
+    const reviewerId = 1; // 관리자 ID (실제 구현에서는 세션에서 가져옵니다)
+    const reviewerName = "AdminUser"; // 실제 구현에서는 세션에서 가져옵니다
+
+    // 병렬로 모든 코멘트 저장
+    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
+    }));
+
+    await sendEmail({
+      to: vendor.email || "",
+      subject: `[IMPORTANT] Your PQ submission requires changes`,
+      template: "vendor-pq-comment", // matches .hbs file
+      context: {
+        name: vendor.vendorName,
+        vendorCode: vendor.vendorCode,
+        loginUrl: `${process.env.NEXT_PUBLIC_URL}/partners/pq`,
+        comments: commentItems,
+        generalComment: generalComment || "",
+        hasGeneralComment: !!generalComment,
+        commentCount: commentItems.length,
+      },
+    });
+
+    revalidateTag("vendors")
+    revalidateTag("vendors-in-pq")
+
+    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) };
+  }
+}
\ No newline at end of file