path: root/lib/pq/service.ts

"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,isNull,SQL} from "drizzle-orm";
import { z } from "zod"
import { revalidateTag, unstable_noStore, revalidatePath} from "next/cache";
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";
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";
import { projects } from "@/db/schema";

/**
 * 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 생성을 위한 입력 스키마 정의
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 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) => {
      // 1. 기본 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 });
      
      // 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,
        pqId: newPqCriteria.id,
        isProjectSpecific,
        message: isProjectSpecific 
          ? "Project-specific PQ criteria created successfully" 
          : "General 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
  criteriaId: number
  code: string
  checkPoint: string
  description: string | null
  remarks?: string | null
  // 프로젝트 PQ 전용 필드
  contractInfo?: string | null
  additionalRequirement?: string | null
  answer: string
  attachments: PQAttachment[]
}

export interface PQGroupData {
  groupName: string
  items: PQItem[]
}

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,
      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,
    };

    // 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 [];
  }

  // 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;
  }
}


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
  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,
          })
          .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
      // 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 
}: { 
  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(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({
        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. 상태 업데이트
    const currentDate = new Date();
    
    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: emailSubject,
          template: "pq-submitted-admin",
          context: {
            vendorName: vendor.vendorName,
            vendorId: vendor.id,
            projectId: projectId,
            projectName: projectName,
            isProjectPQ: !!projectId,
            submittedDate: currentDate.toLocaleString(),
            adminUrl,
          }
        });
      } catch (emailError) {
        console.error("Failed to send admin notification:", emailError);
        // 이메일 실패는 전체 프로세스를 중단하지 않음
      }
    }
      
    // 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: 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);
        // 이메일 실패는 전체 프로세스를 중단하지 않음
      }
    }
      
    // 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);
    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: SQL<unknown> | 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<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: 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)],
          });
          
          // 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: 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)
                }
              };
            })
          );
          
          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" }
      }
  
      // 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) }
    }
  }

  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;
  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,
}: {
  vendorId: number;
  projectId?: number; // Optional project ID for project-specific PQs
  comment: ItemComment[];
  generalComment?: string;
}) {
  try {
    // 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()
      .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();
    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
    }));

    // 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: 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<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: []
    };
  }
}