벤더 코멘트 처리

1 files changed, 930 insertions, 242 deletions

diff --git a/lib/pq/service.ts b/lib/pq/service.ts
index a1373dae..6906ff52 100644
--- a/lib/pq/service.ts
+++ b/lib/pq/service.ts
@@ -5,10 +5,10 @@ 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 { asc, desc, ilike, inArray, and, gte, lte, not, or, eq, count,isNull,SQL} 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 { pqCriterias, pqCriteriasExtension, vendorCriteriaAttachments, vendorPqCriteriaAnswers, vendorPqReviewLogs, vendorProjectPQs } from "@/db/schema/pq"
 import { countPqs, selectPqs } from "./repository";
 import { sendEmail } from "../mail/sendEmail";
 import { vendorAttachments, vendors } from "@/db/schema/vendors";
@@ -18,63 +18,126 @@ import { randomUUID } from 'crypto';
 import { writeFile, mkdir } from 'fs/promises';
 import { GetVendorsSchema } from "../vendors/validations";
 import { countVendors, selectVendors } from "../vendors/repository";
+import { projects } from "@/db/schema";
 
 /**
  * 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`],
-        }
-    )();
+export async function getPQs(
+  input: GetPQSchema,
+  projectId?: number | null,
+  onlyGeneral?: boolean
+) {
+  return unstable_cache(
+    async () => {
+      try {
+        // Common query building logic extracted to a helper function
+        const buildBaseQuery = (queryBuilder: any) => {
+          let query = queryBuilder.from(pqCriterias);
+          
+          // Handle join conditions based on parameters
+          if (projectId) {
+            query = query
+              .innerJoin(
+                pqCriteriasExtension, 
+                eq(pqCriterias.id, pqCriteriasExtension.pqCriteriaId)
+              )
+              .where(eq(pqCriteriasExtension.projectId, projectId));
+          } else if (onlyGeneral) {
+            query = query
+              .leftJoin(
+                pqCriteriasExtension,
+                eq(pqCriterias.id, pqCriteriasExtension.pqCriteriaId)
+              )
+              .where(isNull(pqCriteriasExtension.id));
+          }
+          
+          // Apply filters
+          const advancedWhere = filterColumns({
+            table: pqCriterias,
+            filters: input.filters,
+            joinOperator: input.joinOperator,
+          });
+          
+          // Handle search
+          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)
+            );
+          }
+          
+          // Combine where clauses
+          const finalWhere = and(advancedWhere, globalWhere);
+          if (finalWhere) {
+            query = query.where(finalWhere);
+          }
+          
+          return { query, finalWhere };
+        };
+        
+        const offset = (input.page - 1) * input.perPage;
+        
+        // Build sort order configuration
+        const orderBy = input.sort?.length > 0
+          ? input.sort.map((item) => 
+              item.desc
+                ? desc(pqCriterias[item.id])
+                : asc(pqCriterias[item.id])
+            )
+          : [asc(pqCriterias.createdAt)];
+        
+        // Execute in a transaction
+        const { data, total } = await db.transaction(async (tx) => {
+          // 변경: 쿼리 결과 형태를 변경하여 데이터가 평탄화되도록 수정
+          // Data query
+          const { query: baseQuery } = buildBaseQuery(tx.select({
+            id: pqCriterias.id,
+            code: pqCriterias.code,
+            checkPoint: pqCriterias.checkPoint,
+            description: pqCriterias.description,
+            remarks: pqCriterias.remarks,
+            groupName: pqCriterias.groupName,
+            createdAt: pqCriterias.createdAt,
+            updatedAt: pqCriterias.updatedAt,
+            // 필요한 경우 pqCriteriasExtension의 필드도 여기에 추가
+          }));
+          
+          const data = await baseQuery
+            .orderBy(...orderBy)
+            .offset(offset)
+            .limit(input.perPage);
+          
+          // Count query - reusing the same base query logic
+          const { query: countQuery } = buildBaseQuery(tx.select({ count: count() }));
+          const countRes = await countQuery;
+          const total = countRes[0]?.count ?? 0;
+          
+          return { data, total };
+        });
+        
+        // Calculate page count
+        const pageCount = Math.ceil(total / input.perPage);
+        
+        // 이미 평탄화된 객체 배열 형태로 반환됨
+        return { data, pageCount };
+      } catch (err) {
+        console.log('Error in getPQs:', err);
+        console.error('Error in getPQs:', err);
+        throw new Error('Failed to fetch PQ criteria');
+      }
+    },
+    [JSON.stringify(input), projectId?.toString() ?? 'undefined', onlyGeneral?.toString() ?? 'undefined'],
+    {
+      revalidate: 3600,
+      tags: ["pq"],
+    }
+  )();
 }
 
 // PQ 생성을 위한 입력 스키마 정의
@@ -86,19 +149,26 @@ const createPqSchema = z.object({
     groupName: z.string().optional()
 });
 
-export type CreatePqInputType = z.infer<typeof createPqSchema>;
+export interface CreatePqInputType extends z.infer<typeof createPqSchema> {
+  projectId?: number | null;
+  contractInfo?: string | null;
+  additionalRequirement?: string | null;
+}
 
 /**
  * PQ 기준 생성
  */
 export async function createPq(input: CreatePqInputType) {
   try {
-    // 입력 유효성 검증
+    // 기본 데이터 유효성 검증
     const validatedData = createPqSchema.parse(input);
-
+    
+    // 프로젝트 정보 및 확장 필드 확인
+    const isProjectSpecific = !!input.projectId;
+    
     // 트랜잭션 사용하여 PQ 기준 생성
     return await db.transaction(async (tx) => {
-      // PQ 기준 생성
+      // 1. 기본 PQ 기준 생성
       const [newPqCriteria] = await tx
         .insert(pqCriterias)
         .values({
@@ -109,12 +179,27 @@ export async function createPq(input: CreatePqInputType) {
           groupName: validatedData.groupName || null,
         })
         .returning({ id: pqCriterias.id });
-
+      
+      // 2. 프로젝트별 PQ인 경우 확장 테이블에도 데이터 추가
+      if (isProjectSpecific && input.projectId) {
+        await tx
+          .insert(pqCriteriasExtension)
+          .values({
+            pqCriteriaId: newPqCriteria.id,
+            projectId: input.projectId,
+            contractInfo: input.contractInfo || null,
+            additionalRequirement: input.additionalRequirement || null,
+          });
+      }
+      
       // 성공 결과 반환
-      return { 
-        success: true, 
+      return {
+        success: true,
         pqId: newPqCriteria.id,
-        message: "PQ criteria created successfully" 
+        isProjectSpecific,
+        message: isProjectSpecific 
+          ? "Project-specific PQ criteria created successfully" 
+          : "General PQ criteria created successfully"
       };
     });
   } catch (error) {
@@ -122,21 +207,20 @@ export async function createPq(input: CreatePqInputType) {
     
     // Zod 유효성 검사 에러 처리
     if (error instanceof z.ZodError) {
-      return { 
-        success: false, 
-        message: "Validation failed", 
-        errors: error.errors 
+      return {
+        success: false,
+        message: "Validation failed",
+        errors: error.errors
       };
     }
     
     // 기타 에러 처리
-    return { 
-      success: false, 
-      message: "Failed to create PQ criteria" 
+    return {
+      success: false,
+      message: "Failed to create PQ criteria"
     };
   }
 }
-
 // PQ 캐시 무효화 함수
 export async function invalidatePqCache() {
   revalidatePath(`/evcp/pq-criteria`);
@@ -259,12 +343,16 @@ export interface PQAttachment {
 }
 
 export interface PQItem {
-  answerId: number | null; // null도 허용하도록 변경
+  answerId: number | null
   criteriaId: number
   code: string
   checkPoint: string
   description: string | null
-  answer: string // or null
+  remarks?: string | null
+  // 프로젝트 PQ 전용 필드
+  contractInfo?: string | null
+  additionalRequirement?: string | null
+  answer: string
   attachments: PQAttachment[]
 }
 
@@ -273,89 +361,176 @@ export interface PQGroupData {
   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
+export interface ProjectPQ {
+  id: number;
+  projectId: number;
+  status: string;
+  submittedAt: Date | null;
+  projectCode: string;
+  projectName: string;
+}
+
+export async function getPQProjectsByVendorId(vendorId: number): Promise<ProjectPQ[]> {
+  const result = await db
     .select({
+      id: vendorProjectPQs.id,
+      projectId: vendorProjectPQs.projectId,
+      status: vendorProjectPQs.status,
+      submittedAt: vendorProjectPQs.submittedAt,
+      projectCode: projects.code,
+      projectName: projects.name,
+    })
+    .from(vendorProjectPQs)
+    .innerJoin(
+      projects,
+      eq(vendorProjectPQs.projectId, projects.id)
+    )
+    .where(eq(vendorProjectPQs.vendorId, vendorId))
+    .orderBy(projects.code);
+    
+  return result;
+}
+
+export async function getPQDataByVendorId(
+  vendorId: number,
+  projectId?: number
+): Promise<PQGroupData[]> {
+  try {
+    // 기본 쿼리 구성
+    const selectObj = {
       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
+      remarks: pqCriterias.remarks,
+      
+      // 프로젝트 PQ 추가 필드
+      contractInfo: pqCriteriasExtension.contractInfo,
+      additionalRequirement: pqCriteriasExtension.additionalRequirement,
+      
+      // 벤더 응답 필드
+      answer: vendorPqCriteriaAnswers.answer,
+      answerId: vendorPqCriteriaAnswers.id,
+      
+      // 첨부 파일 필드
       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,
-      })
+    // Create separate queries for each case instead of modifying the same query variable
+    if (projectId) {
+      // 프로젝트별 PQ 쿼리
+      const rows = await db
+        .select(selectObj)
+        .from(pqCriterias)
+        .innerJoin(
+          pqCriteriasExtension,
+          and(
+            eq(pqCriterias.id, pqCriteriasExtension.pqCriteriaId),
+            eq(pqCriteriasExtension.projectId, projectId)
+          )
+        )
+        .leftJoin(
+          vendorPqCriteriaAnswers,
+          and(
+            eq(pqCriterias.id, vendorPqCriteriaAnswers.criteriaId),
+            eq(vendorPqCriteriaAnswers.vendorId, vendorId),
+            eq(vendorPqCriteriaAnswers.projectId, projectId)
+          )
+        )
+        .leftJoin(
+          vendorCriteriaAttachments,
+          eq(vendorPqCriteriaAnswers.id, vendorCriteriaAttachments.vendorCriteriaAnswerId)
+        )
+        .orderBy(pqCriterias.groupName, pqCriterias.code);
+      
+      return processQueryResults(rows);
+    } else {
+      // 일반 PQ 쿼리
+      const rows = await db
+        .select(selectObj)
+        .from(pqCriterias)
+        .leftJoin(
+          pqCriteriasExtension,
+          eq(pqCriterias.id, pqCriteriasExtension.pqCriteriaId)
+        )
+        .where(isNull(pqCriteriasExtension.id))
+        .leftJoin(
+          vendorPqCriteriaAnswers,
+          and(
+            eq(pqCriterias.id, vendorPqCriteriaAnswers.criteriaId),
+            eq(vendorPqCriteriaAnswers.vendorId, vendorId),
+            isNull(vendorPqCriteriaAnswers.projectId)
+          )
+        )
+        .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 [];
   }
 
-  // 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 })
+  // Helper function to process query results
+  function processQueryResults(rows: any[]) {
+    // 그룹별로 데이터 구성
+    const groupMap = new Map<string, Record<number, PQItem>>();
+    
+    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,
+          // 프로젝트 PQ 전용 필드
+          contractInfo: row.contractInfo,
+          additionalRequirement: row.additionalRequirement,
+          answer: row.answer || "",
+          attachments: [],
+        };
+      }
+      
+      // 첨부 파일이 있으면 추가
+      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;
   }
-
-  return data
 }
 
 
@@ -373,6 +548,7 @@ interface SavePQAnswer {
 
 interface SavePQInput {
   vendorId: number
+  projectId?: number
   answers: SavePQAnswer[]
 }
 
@@ -380,20 +556,27 @@ interface SavePQInput {
  * 여러 항목을 한 번에 Upsert
  */
 export async function savePQAnswersAction(input: SavePQInput) {
-  const { vendorId, answers } = input
+  const { vendorId, projectId, answers } = input
 
   try {
     for (const ans of answers) {
-      // 1) Check if a row already exists for (vendorId, criteriaId)
+      // 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(
-            eq(vendorPqCriteriaAnswers.vendorId, vendorId),
-            eq(vendorPqCriteriaAnswers.criteriaId, ans.criteriaId)
-          )
-        )
+        .where(and(...queryConditions));
 
       let answerId: number
 
@@ -405,11 +588,11 @@ export async function savePQAnswersAction(input: SavePQInput) {
           .values({
             vendorId,
             criteriaId: ans.criteriaId,
+            projectId: projectId || null, // Include projectId when provided
             answer: ans.answer,
-            // no attachmentPaths column anymore
           })
           .returning({ id: vendorPqCriteriaAnswers.id })
-        
+
         answerId = inserted[0].id
       } else {
         // Update existing
@@ -425,8 +608,6 @@ export async function savePQAnswersAction(input: SavePQInput) {
       }
 
       // 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({
@@ -448,17 +629,16 @@ export async function savePQAnswersAction(input: SavePQInput) {
           .where(inArray(vendorCriteriaAttachments.id, removeIds))
       }
 
-      // 3d) Insert new attachments that aren’t in DB
+      // 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
+          fileName: attach.fileName,
+          filePath: attach.url,
           fileSize: attach.size ?? null,
-          // fileType if you have it, etc.
         })
       }
     }
@@ -476,23 +656,40 @@ export async function savePQAnswersAction(input: SavePQInput) {
  * PQ 제출 서버 액션 - 벤더 상태를 PQ_SUBMITTED로 업데이트
  * @param vendorId 벤더 ID
  */
-export async function submitPQAction(vendorId: number) {
+export async function submitPQAction({ 
+  vendorId, 
+  projectId 
+}: { 
+  vendorId: number; 
+  projectId?: number;
+}) {
   unstable_noStore();
   
   try {
     // 1. 모든 PQ 항목에 대한 응답이 있는지 검증
+    const queryConditions = [
+      eq(vendorPqCriteriaAnswers.vendorId, vendorId)
+    ];
+    
+    // Add projectId condition when it exists
+    if (projectId !== undefined) {
+      queryConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId));
+    } else {
+      queryConditions.push(isNull(vendorPqCriteriaAnswers.projectId));
+    }
+    
     const pqCriteriaCount = await db
       .select({ count: count() })
       .from(vendorPqCriteriaAnswers)
-      .where(eq(vendorPqCriteriaAnswers.vendorId, vendorId));
-    
+      .where(and(...queryConditions));
+      
     const totalPqCriteriaCount = pqCriteriaCount[0]?.count || 0;
-    
+      
     // 응답 데이터 검증
     if (totalPqCriteriaCount === 0) {
       return { ok: false, error: "No PQ answers found" };
     }
-    
+      
     // 2. 벤더 정보 조회
     const vendor = await db
       .select({
@@ -504,41 +701,118 @@ export async function submitPQAction(vendorId: number) {
       .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}` 
-      };
+    // 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';
     }
     
-    // 4. 벤더 상태 업데이트
-    await db
-      .update(vendors)
-      .set({
-        status: "PQ_SUBMITTED",
-        updatedAt: new Date(),
-      })
-      .where(eq(vendors.id, vendorId));
+    // 3. 상태 업데이트
+    const currentDate = new Date();
     
-    // 5. 관리자에게 이메일 알림 발송
+    if (projectId) {
+      // 프로젝트별 PQ인 경우 vendorProjectPQs 테이블 업데이트
+      const existingProjectPQ = await db
+        .select({ id: vendorProjectPQs.id, status: vendorProjectPQs.status })
+        .from(vendorProjectPQs)
+        .where(
+          and(
+            eq(vendorProjectPQs.vendorId, vendorId),
+            eq(vendorProjectPQs.projectId, projectId)
+          )
+        )
+        .then(rows => rows[0]);
+        
+      if (existingProjectPQ) {
+        // 프로젝트 PQ 상태가 제출 가능한 상태인지 확인
+        const allowedStatuses = ["REQUESTED", "IN_PROGRESS", "REJECTED"];
+        
+        if (!allowedStatuses.includes(existingProjectPQ.status)) {
+          return {
+            ok: false,
+            error: `Cannot submit Project PQ in current status: ${existingProjectPQ.status}`
+          };
+        }
+        
+        // Update existing project PQ status
+        await db
+          .update(vendorProjectPQs)
+          .set({
+            status: "SUBMITTED",
+            submittedAt: currentDate,
+            updatedAt: currentDate,
+          })
+          .where(eq(vendorProjectPQs.id, existingProjectPQ.id));
+      } else {
+        // Project PQ entry doesn't exist, create one
+        await db
+          .insert(vendorProjectPQs)
+          .values({
+            vendorId,
+            projectId,
+            status: "SUBMITTED",
+            submittedAt: currentDate,
+            createdAt: currentDate,
+            updatedAt: currentDate,
+          });
+      }
+    } else {
+      // 일반 PQ인 경우 벤더 상태 검증 및 업데이트
+      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}`
+        };
+      }
+      
+      // Update vendor status
+      await db
+        .update(vendors)
+        .set({
+          status: "PQ_SUBMITTED",
+          updatedAt: currentDate,
+        })
+        .where(eq(vendors.id, vendorId));
+    }
+      
+    // 4. 관리자에게 이메일 알림 발송
     if (process.env.ADMIN_EMAIL) {
       try {
+        const emailSubject = projectId 
+          ? `[eVCP] Project PQ Submitted: ${vendor.vendorName} for ${projectName}`
+          : `[eVCP] PQ Submitted: ${vendor.vendorName}`;
+          
+        const adminUrl = projectId
+          ? `${process.env.NEXT_PUBLIC_APP_URL}/admin/vendors/${vendorId}/projects/${projectId}/pq`
+          : `${process.env.NEXT_PUBLIC_APP_URL}/admin/vendors/${vendorId}/pq`;
+          
         await sendEmail({
           to: process.env.ADMIN_EMAIL,
-          subject: `[eVCP] PQ Submitted: ${vendor.vendorName}`,
+          subject: emailSubject,
           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`,
+            projectId: projectId,
+            projectName: projectName,
+            isProjectPQ: !!projectId,
+            submittedDate: currentDate.toLocaleString(),
+            adminUrl,
           }
         });
       } catch (emailError) {
@@ -546,18 +820,29 @@ export async function submitPQAction(vendorId: number) {
         // 이메일 실패는 전체 프로세스를 중단하지 않음
       }
     }
-    
-    // 6. 벤더에게 확인 이메일 발송
+      
+    // 5. 벤더에게 확인 이메일 발송
     if (vendor.email) {
       try {
+        const emailSubject = projectId
+          ? `[eVCP] Project PQ Submission Confirmation for ${projectName}`
+          : "[eVCP] PQ Submission Confirmation";
+          
+        const portalUrl = projectId
+          ? `${process.env.NEXT_PUBLIC_APP_URL}/dashboard/projects/${projectId}`
+          : `${process.env.NEXT_PUBLIC_APP_URL}/dashboard`;
+          
         await sendEmail({
           to: vendor.email,
-          subject: "[eVCP] PQ Submission Confirmation",
+          subject: emailSubject,
           template: "pq-submitted-vendor",
           context: {
             vendorName: vendor.vendorName,
-            submittedDate: new Date().toLocaleString(),
-            portalUrl: `${process.env.NEXT_PUBLIC_APP_URL}/dashboard`,
+            projectId: projectId,
+            projectName: projectName,
+            isProjectPQ: !!projectId,
+            submittedDate: currentDate.toLocaleString(),
+            portalUrl,
           }
         });
       } catch (emailError) {
@@ -565,11 +850,17 @@ export async function submitPQAction(vendorId: number) {
         // 이메일 실패는 전체 프로세스를 중단하지 않음
       }
     }
-    
-    // 7. 캐시 무효화
+      
+    // 6. 캐시 무효화
     revalidateTag("vendors");
     revalidateTag("vendor-status-counts");
     
+    if (projectId) {
+      revalidateTag(`vendor-project-pqs-${vendorId}`);
+      revalidateTag(`project-vendors-${projectId}`);
+      revalidateTag(`project-pq-${projectId}`);
+    }
+      
     return { ok: true };
   } catch (error) {
     console.error("PQ submit error:", error);
@@ -697,7 +988,7 @@ export async function getVendorsInPQ(input: GetVendorsSchema) {
         });
 
         // 2) 글로벌 검색
-        let globalWhere;
+        let globalWhere: SQL<unknown> | undefined = undefined;
         if (input.search) {
           const s = `%${input.search}%`;
           globalWhere = or(
@@ -708,44 +999,80 @@ export async function getVendorsInPQ(input: GetVendorsSchema) {
           );
         }
 
-        // 최종 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 목록 조회
+          // 벤더 ID 모음 (중복 제거용)
+          const vendorIds = new Set<number>();
+          
+          // 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: vendorProjectPQs.vendorId
+            })
+            .from(vendorProjectPQs)
+            .innerJoin(
+              vendors,
+              eq(vendorProjectPQs.vendorId, vendors.id)
+            )
+            .where(
+              and(
+                // 최소한 IN_PROGRESS부터는 작업이 시작된 상태이므로 포함
+                not(eq(vendorProjectPQs.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,
-            orderBy,
-            offset,
-            limit: input.perPage,
+            where: inArray(vendors.id, paginatedIds),
+            orderBy: input.sort.length > 0
+              ? input.sort.map((item) => 
+                  item.desc ? desc(vendors[item.id]) : asc(vendors[item.id])
+                )
+              : [asc(vendors.createdAt)],
           });
-
-          // 2) 각 vendor의 attachments 조회
-          const vendorsWithAttachments = await Promise.all(
+          
+          // 3) 각 벤더별 PQ 상태 정보 추가 
+          const vendorsWithPqInfo = await Promise.all(
             vendorsData.map(async (vendor) => {
+              // 3-A) 첨부 파일 조회
               const attachments = await tx
                 .select({
                   id: vendorAttachments.id,
@@ -754,18 +1081,71 @@ export async function getVendorsInPQ(input: GetVendorsSchema) {
                 })
                 .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: vendorProjectPQs.projectId,
+                  projectName: projects.name,
+                  status: vendorProjectPQs.status,
+                  submittedAt: vendorProjectPQs.submittedAt,
+                  approvedAt: vendorProjectPQs.approvedAt,
+                  rejectedAt: vendorProjectPQs.rejectedAt
+                })
+                .from(vendorProjectPQs)
+                .innerJoin(
+                  projects,
+                  eq(vendorProjectPQs.projectId, projects.id)
+                )
+                .where(
+                  and(
+                    eq(vendorProjectPQs.vendorId, vendor.id),
+                    not(eq(vendorProjectPQs.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)
+                }
               };
             })
           );
-
-          // 3) 전체 개수
-          const total = await countVendors(tx, where);
-          return { data: vendorsWithAttachments, total };
+          
+          return { data: vendorsWithPqInfo, total };
         });
 
         // 페이지 수
@@ -773,6 +1153,7 @@ export async function getVendorsInPQ(input: GetVendorsSchema) {
 
         return { data, pageCount };
       } catch (err) {
+        console.error("Error in getVendorsInPQ:", err);
         // 에러 발생 시
         return { data: [], pageCount: 0 };
       }
@@ -780,11 +1161,65 @@ export async function getVendorsInPQ(input: GetVendorsSchema) {
     [JSON.stringify(input)], // 캐싱 키
     {
       revalidate: 3600,
-      tags: ["vendors-in-pq"], // revalidateTag("vendors") 호출 시 무효화
+      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"
@@ -797,6 +1232,7 @@ export type VendorStatus =
   | "ACTIVE"
   | "INACTIVE"
   | "BLACKLISTED"
+  | "PQ_APPROVED"
 
   export async function updateVendorStatusAction(
     vendorId: number,
@@ -833,6 +1269,111 @@ export type VendorStatus =
       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(vendorProjectPQs)
+      .set(updateData)
+      .where(
+        and(
+          eq(vendorProjectPQs.vendorId, vendorId),
+          eq(vendorProjectPQs.projectId, projectId)
+        )
+      );
+    
+    // 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/projects/${projectId}/pq`,
+        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;
@@ -850,24 +1391,60 @@ interface ItemComment {
  */
 export async function requestPqChangesAction({
   vendorId,
+  projectId,
   comment,
   generalComment,
 }: {
   vendorId: number;
+  projectId?: number; // Optional project ID for project-specific PQs
   comment: ItemComment[];
   generalComment?: string;
 }) {
   try {
-    // 1) 벤더 상태 업데이트
-    await db.update(vendors)
-      .set({
-        status: "IN_PQ", // 변경 요청 상태로 설정
-        updatedAt: new Date(),
-      })
-      .where(eq(vendors.id, vendorId));
+    // 1) 상태 업데이트 (PQ 타입에 따라 다르게 처리)
+    if (projectId) {
+      // 프로젝트 PQ인 경우 vendorProjectPQs 테이블 업데이트
+      const projectPq = await db
+        .select()
+        .from(vendorProjectPQs)
+        .where(
+          and(
+            eq(vendorProjectPQs.vendorId, vendorId),
+            eq(vendorProjectPQs.projectId, projectId)
+          )
+        )
+        .then(rows => rows[0]);
+
+      if (!projectPq) {
+        return { ok: false, error: "Project PQ record not found" };
+      }
+
+      await db
+        .update(vendorProjectPQs)
+        .set({
+          status: "IN_PROGRESS", // 변경 요청 상태로 설정
+          updatedAt: new Date(),
+        })
+        .where(
+          and(
+            eq(vendorProjectPQs.vendorId, vendorId),
+            eq(vendorProjectPQs.projectId, projectId)
+          )
+        );
+    } 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()
+    const vendor = await db
+      .select()
       .from(vendors)
       .where(eq(vendors.id, vendorId))
       .then(r => r[0]);
@@ -876,6 +1453,20 @@ export async function requestPqChangesAction({
       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();
     const reviewerId = 1; // 관리자 ID (실제 구현에서는 세션에서 가져옵니다)
@@ -883,7 +1474,7 @@ export async function requestPqChangesAction({
 
     // 병렬로 모든 코멘트 저장
     if (comment && comment.length > 0) {
-      const insertPromises = comment.map(item => 
+      const insertPromises = comment.map(item =>
         db.insert(vendorPqReviewLogs)
           .values({
             vendorPqCriteriaAnswerId: item.answerId,
@@ -910,23 +1501,43 @@ export async function requestPqChangesAction({
       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/projects/${projectId}/pq`
+      : `${process.env.NEXT_PUBLIC_URL}/partners/pq`;
+
     await sendEmail({
       to: vendor.email || "",
-      subject: `[IMPORTANT] Your PQ submission requires changes`,
+      subject: emailSubject,
       template: "vendor-pq-comment", // matches .hbs file
       context: {
         name: vendor.vendorName,
         vendorCode: vendor.vendorCode,
-        loginUrl: `${process.env.NEXT_PUBLIC_URL}/partners/pq`,
+        loginUrl,
         comments: commentItems,
         generalComment: generalComment || "",
         hasGeneralComment: !!generalComment,
         commentCount: commentItems.length,
+        projectId,
+        projectName,
+        isProjPQ: !!projectId,
       },
     });
 
-    revalidateTag("vendors")
-    revalidateTag("vendors-in-pq")
+    // 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) {
@@ -934,6 +1545,7 @@ export async function requestPqChangesAction({
     return { ok: false, error: String(error) };
   }
 }
+
 interface AddReviewCommentInput {
   answerId: number // vendorPqCriteriaAnswers.id
   comment: string
@@ -984,4 +1596,80 @@ export async function getItemReviewLogsAction(input: GetItemReviewLogsInput) {
     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<VendorPQsList> {
+  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: vendorProjectPQs.projectId,
+        projectName: projects.name,
+        status: vendorProjectPQs.status,
+        submittedAt: vendorProjectPQs.submittedAt
+      })
+      .from(vendorProjectPQs)
+      .innerJoin(
+        projects,
+        eq(vendorProjectPQs.projectId, projects.id)
+      )
+      .where(
+        and(
+          eq(vendorProjectPQs.vendorId, vendorId),
+          not(eq(vendorProjectPQs.status, "REQUESTED")) // Exclude requests that haven't been started
+        )
+      )
+      .orderBy(vendorProjectPQs.updatedAt);
+        
+    return {
+      hasGeneralPq,
+      generalPqStatus,
+      projectPQs: projectPQs
+    };
+    
+  } catch (error) {
+    console.error("Error fetching vendor PQs list:", error);
+    return {
+      hasGeneralPq: false,
+      projectPQs: []
+    };
+  }
 }
\ No newline at end of file