"use server"

import db from "@/db/db"
import { CopyPqListInput, CreatePqListInput, copyPqListSchema, createPqListSchema, GetPqListsSchema, GetPQSchema, GetPQSubmissionsSchema } from "./validations"
import { unstable_cache } from "@/lib/unstable-cache";
import { filterColumns } from "@/lib/filter-columns";
import { getErrorMessage } from "@/lib/handle-error";
import { asc, desc, ilike, inArray, and, gte, lte, not, or, eq, count,isNull,SQL, sql, lt, isNotNull} from "drizzle-orm";
import { z } from "zod"
import { revalidateTag, unstable_noStore, revalidatePath} from "next/cache";
import { format } from "date-fns"
import { pqCriterias, vendorCriteriaAttachments, vendorInvestigations, vendorInvestigationAttachments, vendorPQSubmissions, vendorPqCriteriaAnswers, vendorPqReviewLogs, siteVisitRequests, vendorSiteVisitInfo, siteVisitRequestAttachments } from "@/db/schema/pq"
import { sendEmail } from "../mail/sendEmail";
import { decryptWithServerAction } from '@/components/drm/drmUtils'

import { vendorAttachments, vendors } from "@/db/schema/vendors";
import { vendorRegularRegistrations } from "@/db/schema/vendorRegistrations";
import { saveFile, saveDRMFile } from "@/lib/file-stroage";
import { GetVendorsSchema } from "../vendors/validations";
import { selectVendors } from "../vendors/repository";
import { projects, users } from "@/db/schema";
import { headers } from 'next/headers';
import { getServerSession } from "next-auth/next"
import { authOptions } from "@/app/api/auth/[...nextauth]/route"
import { alias } from 'drizzle-orm/pg-core';
import { createPQFilterMapping, getPQJoinedTables } from "./helper";
import { pqLists } from "@/db/schema/pq";

export interface PQAttachment {
  attachId: number
  fileName: string
  filePath: string
  fileSize?: number
}

export interface PQItem {
  answerId: number | null
  criteriaId: number
  code: string
  checkPoint: string
  description: string | null
  remarks?: string | null
  // 프로젝트 PQ 전용 필드
  contractInfo?: string | null
  additionalRequirement?: string | null
  answer: string
  shiComment: string
  vendorReply: string
  attachments: PQAttachment[]
  subGroupName: string
  inputFormat: string

  createdAt: Date | null
  updatedAt: Date | null
}

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

export interface ProjectPQ {
  id: number;
  projectId: number | null;
  status: string;
  submittedAt: Date | null;
  projectCode: string;
  projectName: string;
}

export async function getPQProjectsByVendorId(vendorId: number): Promise<ProjectPQ[]> {
  const result = await db
    .select({
      id: vendorPQSubmissions.id,
      projectId: vendorPQSubmissions.projectId,
      status: vendorPQSubmissions.status,
      submittedAt: vendorPQSubmissions.submittedAt,
      projectCode: projects.code,
      projectName: projects.name,
    })
    .from(vendorPQSubmissions)
    .innerJoin(
      projects,
      eq(vendorPQSubmissions.projectId, projects.id)
    )
    .where(eq(vendorPQSubmissions.vendorId, vendorId))
    .orderBy(projects.code);
    
  return result;
}

export async function getPQDataByVendorId(
  vendorId: number,
  projectId?: number
): Promise<PQGroupData[]> {
  try {
    // 기본 쿼리 구성
    const selectObj = {
      criteriaId: pqCriterias.id,
      groupName: pqCriterias.groupName,
      code: pqCriterias.code,
      checkPoint: pqCriterias.checkPoint,
      description: pqCriterias.description,
      remarks: pqCriterias.remarks,
      
      // 입력 형식 필드 추가
      inputFormat: pqCriterias.inputFormat,
      
      // 협력업체 응답 필드
      answer: vendorPqCriteriaAnswers.answer,
      answerId: vendorPqCriteriaAnswers.id,
      
      // SHI 코멘트와 벤더 답변 필드 추가
      shiComment: vendorPqCriteriaAnswers.shiComment,
      vendorReply: vendorPqCriteriaAnswers.vendorReply,
      createdAt: vendorPqCriteriaAnswers.createdAt,
      updatedAt: vendorPqCriteriaAnswers.updatedAt,
      
      // 첨부 파일 필드
      attachId: vendorCriteriaAttachments.id,
      fileName: vendorCriteriaAttachments.fileName,
      filePath: vendorCriteriaAttachments.filePath,
      fileSize: vendorCriteriaAttachments.fileSize,
    };

    // Create separate queries for each case instead of modifying the same query variable
    if (projectId) {
      // 프로젝트별 PQ 쿼리 - PQ 리스트 기반으로 변경
      const rows = await db
        .select(selectObj)
        .from(pqCriterias)
        .innerJoin(
          pqLists,
          and(
            eq(pqCriterias.pqListId, pqLists.id),
            eq(pqLists.projectId, projectId),
            eq(pqLists.type, "PROJECT"),
            eq(pqLists.isDeleted, false)
          )
        )
        .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 쿼리 - PQ 리스트 기반으로 변경
      const rows = await db
        .select(selectObj)
        .from(pqCriterias)
        .innerJoin(
          pqLists,
          and(
            eq(pqCriterias.pqListId, pqLists.id),
            eq(pqLists.type, "GENERAL"),
            eq(pqLists.isDeleted, false)
          )
        )
        .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,
          answer: row.answer || "",
          shiComment: row.shiComment || "",
          vendorReply: row.vendorReply || "",
          attachments: [],
          inputFormat: row.inputFormat || "",
  
          subGroupName: row.subGroupName || "",
          createdAt: row.createdAt,
          updatedAt: row.updatedAt,
        };
      }
      
      // 첨부 파일이 있으면 추가
      if (row.attachId) {
        groupItems[row.criteriaId].attachments.push({
          attachId: row.attachId,
          fileName: row.fileName || "",
          filePath: row.filePath || "",
          fileSize: row.fileSize || undefined,
        });
      }
    }
    
    // 최종 데이터 구성
    const data: PQGroupData[] = [];
    for (const [groupName, itemsMap] of groupMap.entries()) {
      const items = Object.values(itemsMap);
      data.push({ groupName, items });
    }
    
    return data;
  }
}


interface PQAttachmentInput {
  fileName: string      // original user-friendly file name
  url: string           // the UUID-based path stored on server
  size?: number         // optional file size
}

interface SavePQAnswer {
  criteriaId: number
  answer: string
  shiComment?: string
  vendorReply?: string
  attachments: PQAttachmentInput[]
}

interface SavePQInput {
  vendorId: number
  projectId?: number
  answers: SavePQAnswer[]
}

/**
 * 여러 항목을 한 번에 Upsert
 */
export async function savePQAnswersAction(input: SavePQInput) {
  const { vendorId, projectId, answers } = input

  try {
    for (const ans of answers) {
      // 1) Check if a row already exists for (vendorId, criteriaId, projectId)
      const queryConditions = [
        eq(vendorPqCriteriaAnswers.vendorId, vendorId),
        eq(vendorPqCriteriaAnswers.criteriaId, ans.criteriaId)
      ];
      
      // Add projectId condition when it exists
      if (projectId !== undefined) {
        queryConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId));
      } else {
        queryConditions.push(isNull(vendorPqCriteriaAnswers.projectId));
      }
      
      const existing = await db
        .select()
        .from(vendorPqCriteriaAnswers)
        .where(and(...queryConditions));

      let answerId: number

      // 2) If it exists, update the row; otherwise insert
      if (existing.length === 0) {
        // Insert new
        const inserted = await db
          .insert(vendorPqCriteriaAnswers)
          .values({
            vendorId,
            criteriaId: ans.criteriaId,
            projectId: projectId || null, // Include projectId when provided
            answer: ans.answer,
            shiComment: ans.shiComment || null,
            vendorReply: ans.vendorReply || null,
            createdAt: new Date(),
            updatedAt: new Date(),
          })
          .returning({ id: vendorPqCriteriaAnswers.id })

        answerId = inserted[0].id
      } else {
        // Update existing
        answerId = existing[0].id

        await db
          .update(vendorPqCriteriaAnswers)
          .set({
            answer: ans.answer,
            shiComment: ans.shiComment || null,
            vendorReply: ans.vendorReply || null,
            updatedAt: new Date(),
          })
          .where(eq(vendorPqCriteriaAnswers.id, answerId))
      }

      // 3) Now manage attachments in vendorCriteriaAttachments
      // 3a) Load old attachments from DB
      const oldAttachments = await db
        .select({
          id: vendorCriteriaAttachments.id,
          filePath: vendorCriteriaAttachments.filePath,
        })
        .from(vendorCriteriaAttachments)
        .where(eq(vendorCriteriaAttachments.vendorCriteriaAnswerId, answerId))

      // 3b) Gather the new filePaths (urls) from the client
      const newPaths = ans.attachments.map(a => a.url)

      // 3c) Find attachments to remove
      const toRemove = oldAttachments.filter(old => !newPaths.includes(old.filePath))
      if (toRemove.length > 0) {
        const removeIds = toRemove.map(r => r.id)
        await db
          .delete(vendorCriteriaAttachments)
          .where(inArray(vendorCriteriaAttachments.id, removeIds))
      }

      // 3d) Insert new attachments that aren't in DB
      const oldPaths = oldAttachments.map(o => o.filePath)
      const toAdd = ans.attachments.filter(a => !oldPaths.includes(a.url))

      for (const attach of toAdd) {
        await db.insert(vendorCriteriaAttachments).values({
          vendorCriteriaAnswerId: answerId,
          fileName: attach.fileName,
          filePath: attach.url,
          fileSize: attach.size ?? null,
        })
      }
    }

    return { ok: true }
  } catch (error) {
    console.error("savePQAnswersAction error:", error)
    return { ok: false, error: String(error) }
  }
}



/**
 * PQ 제출 서버 액션 - 협력업체 상태를 PQ_SUBMITTED로 업데이트
 * @param vendorId 협력업체 ID
 */
export async function submitPQAction({ 
  vendorId, 
  projectId,
  pqSubmissionId
}: { 
  vendorId: number; 
  projectId?: number;
  pqSubmissionId?: number; // 특정 PQ 제출 ID가 있는 경우 사용
}) {
  unstable_noStore();
  
  try {
    const headersList = await headers();
    const host = headersList.get('host') || 'localhost:3000';

    // 1. 모든 PQ 항목에 대한 응답이 있는지 검증
    const answerQueryConditions = [
      eq(vendorPqCriteriaAnswers.vendorId, vendorId)
    ];
    
    // Add projectId condition when it exists
    if (projectId !== undefined) {
      answerQueryConditions.push(eq(vendorPqCriteriaAnswers.projectId, projectId));
    } else {
      answerQueryConditions.push(isNull(vendorPqCriteriaAnswers.projectId));
    }
    
    const pqCriteriaCount = await db
      .select({ count: count() })
      .from(vendorPqCriteriaAnswers)
      .where(and(...answerQueryConditions));
      
    const totalPqCriteriaCount = pqCriteriaCount[0]?.count || 0;
      
    // 응답 데이터 검증
    if (totalPqCriteriaCount === 0) {
      return { ok: false, error: "No PQ answers found" };
    }
      
    // 2. 협력업체 정보 조회
    const vendor = await db
      .select({
        id: vendors.id,
        vendorName: vendors.vendorName,
        email: vendors.email,
        status: vendors.status,
      })
      .from(vendors)
      .where(eq(vendors.id, vendorId))
      .then(rows => rows[0]);
      
    if (!vendor) {
      return { ok: false, error: "Vendor not found" };
    }
    
    // Project 정보 조회 (projectId가 있는 경우)
    let projectName = '';
    if (projectId) {
      const projectData = await db
        .select({
          projectName: projects.name
        })
        .from(projects)
        .where(eq(projects.id, projectId))
        .then(rows => rows[0]);
        
      projectName = projectData?.projectName || 'Unknown Project';
    }
    
    // 3. 현재 PQ 제출 상태 확인 및 업데이트
    const currentDate = new Date();
    let existingSubmission;
    
    // 특정 PQ Submission ID가 있는 경우
    if (pqSubmissionId) {
      existingSubmission = await db
        .select({ 
          id: vendorPQSubmissions.id, 
          status: vendorPQSubmissions.status,
          type: vendorPQSubmissions.type 
        })
        .from(vendorPQSubmissions)
        .where(
          and(
            eq(vendorPQSubmissions.id, pqSubmissionId),
            eq(vendorPQSubmissions.vendorId, vendorId)
          )
        )
        .then(rows => rows[0]);
        
      if (!existingSubmission) {
        return { ok: false, error: "PQ submission not found or access denied" };
      }
    } 
    // ID가 없는 경우 vendorId와 projectId로 조회
    else {
      const pqType = projectId ? "PROJECT" : "GENERAL";
      
      const submissionQueryConditions = [
        eq(vendorPQSubmissions.vendorId, vendorId),
        eq(vendorPQSubmissions.type, pqType)
      ];
      
      if (projectId) {
        submissionQueryConditions.push(eq(vendorPQSubmissions.projectId, projectId));
      } else {
        submissionQueryConditions.push(isNull(vendorPQSubmissions.projectId));
      }
      
      existingSubmission = await db
        .select({ 
          id: vendorPQSubmissions.id, 
          status: vendorPQSubmissions.status,
          type: vendorPQSubmissions.type 
        })
        .from(vendorPQSubmissions)
        .where(and(...submissionQueryConditions))
        .then(rows => rows[0]);
    }
    
    // 제출 가능한 상태 확인
    const allowedStatuses = ["REQUESTED", "IN_PROGRESS", "REJECTED"];
    
    if (existingSubmission) {
      if (!allowedStatuses.includes(existingSubmission.status)) {
        return {
          ok: false,
          error: `Cannot submit PQ in current status: ${existingSubmission.status}`
        };
      }
      
      // 기존 제출 상태 업데이트
      await db
        .update(vendorPQSubmissions)
        .set({
          status: "SUBMITTED",
          submittedAt: currentDate,
          updatedAt: currentDate,
        })
        .where(eq(vendorPQSubmissions.id, existingSubmission.id));
    } else {
      // PQ Submission ID가 없고 기존 submission도 없는 경우 새로운 제출 생성
      const pqType = projectId ? "PROJECT" : "GENERAL";
      
      // PQ 번호 생성 (예: PQ-2024-001)
      const currentYear = new Date().getFullYear();
      const pqNumber = `PQ-${currentYear}-${String(vendorId).padStart(3, '0')}`;
      
      await db
        .insert(vendorPQSubmissions)
        .values({
          pqNumber,
          vendorId,
          projectId: projectId || null,
          type: pqType,
          status: "SUBMITTED",
          submittedAt: currentDate,
          createdAt: currentDate,
          updatedAt: currentDate,
        });
    }
    
    // 4. 일반 PQ인 경우 벤더 상태도 업데이트
    if (!projectId) {
      const allowedVendorStatuses = ["IN_PQ", "PENDING_REVIEW", "IN_REVIEW", "REJECTED", "PQ_FAILED"];
      
      if (allowedVendorStatuses.includes(vendor.status)) {
        await db
          .update(vendors)
          .set({
            status: "PQ_SUBMITTED",
            updatedAt: currentDate,
          })
          .where(eq(vendors.id, vendorId));
      }
    }
      
    // 5. 관리자에게 이메일 알림 발송
    if (process.env.ADMIN_EMAIL) {
      try {
        const emailSubject = projectId 
          ? `[eVCP] Project PQ Submitted: ${vendor.vendorName} for ${projectName}`
          : `[eVCP] General PQ Submitted: ${vendor.vendorName}`;
          
        const adminUrl = `http://${host}/evcp/pq/${vendorId}/${existingSubmission?.id || ''}`;
          
        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);
      }
    }
      
    // 6. 벤더에게 확인 이메일 발송
    if (vendor.email) {
      try {
        const emailSubject = projectId
          ? `[eVCP] Project PQ Submission Confirmation for ${projectName}`
          : "[eVCP] General PQ Submission Confirmation";
          
        const portalUrl = `${host}/partners/pq`;
          
        await sendEmail({
          to: vendor.email,
          subject: emailSubject,
          template: "pq-submitted-vendor",
          context: {
            vendorName: vendor.vendorName,
            projectId: projectId,
            projectName: projectName,
            isProjectPQ: !!projectId,
            submittedDate: currentDate.toLocaleString(),
            portalUrl,
          }      
        });
      } catch (emailError) {
        console.error("Failed to send vendor confirmation:", emailError);
      }
    }
      
    // 7. 캐시 무효화
    revalidateTag("vendors");
    revalidateTag("vendor-status-counts");
    revalidateTag(`vendor-pq-submissions-${vendorId}`);
    
    if (projectId) {
      revalidateTag(`project-pq-submissions-${projectId}`);
      revalidateTag(`project-vendors-${projectId}`);
      revalidateTag(`project-pq-${projectId}`);
    }
      
    return { ok: true };
  } catch (error) {
    console.error("PQ submit error:", error);
    return { ok: false, error: getErrorMessage(error) };
  }
}

/**
 * 향상된 파일 업로드 서버 액션
 * - 직접 파일 처리 (file 객체로 받음)
 * - 디렉토리 자동 생성
 * - 중복 방지를 위한 UUID 적용
 */
/**
 * 벤더용 파일 업로드 액션 (saveFile 사용)
 */
export async function uploadVendorFileAction(file: File, userId?: string) {
  unstable_noStore();
  
  try {
    const result = await saveFile({
      file,
      directory: 'pq/vendor',
      originalName: file.name,
      userId,
    });

    if (!result.success) {
      throw new Error(result.error || "파일 업로드에 실패했습니다.");
    }

    return {
      fileName: result.fileName!,
      url: result.publicPath!,
      size: result.fileSize!,
    };
  } catch (error) {
    console.error("Vendor file upload error:", error);
    throw new Error(`Upload failed: ${getErrorMessage(error)}`);
  }
}

/**
 * SHI용 파일 업로드 액션 (saveDRMFile 사용)
 */
export async function uploadSHIFileAction(file: File, userId?: string) {
  unstable_noStore();
  
  try {
    const result = await saveDRMFile(
      file,
      decryptWithServerAction,
      'pq/shi',
      userId
    );

    if (!result.success) {
      throw new Error(result.error || "파일 업로드에 실패했습니다.");
    }

    return {
      fileName: result.fileName!,
      url: result.publicPath!,
      size: result.fileSize!,
    };
  } catch (error) {
    console.error("SHI file upload error:", error);
    throw new Error(`Upload failed: ${getErrorMessage(error)}`);
  }
}

/**
 * 벤더용 여러 파일 일괄 업로드
 */
export async function uploadVendorMultipleFilesAction(files: File[], userId?: string) {
  unstable_noStore();
  
  try {
    const results = [];
    
    for (const file of files) {
      try {
        const result = await uploadVendorFileAction(file, userId);
        results.push({
          success: true,
          ...result
        });
      } catch (error) {
        results.push({
          success: false,
          fileName: file.name,
          error: getErrorMessage(error)
        });
      }
    }
    
    return {
      ok: true,
      results
    };
  } catch (error) {
    console.error("Vendor batch upload error:", error);
    return {
      ok: false,
      error: getErrorMessage(error)
    };
  }
}

/**
 * SHI용 여러 파일 일괄 업로드
 */
export async function uploadSHIMultipleFilesAction(files: File[], userId?: string) {
  unstable_noStore();
  
  try {
    const results = [];
    
    for (const file of files) {
      try {
        const result = await uploadSHIFileAction(file, userId);
        results.push({
          success: true,
          ...result
        });
      } catch (error) {
        results.push({
          success: false,
          fileName: file.name,
          error: getErrorMessage(error)
        });
      }
    }
    
    return {
      ok: true,
      results
    };
  } catch (error) {
    console.error("SHI batch upload error:", error);
    return {
      ok: false,
      error: getErrorMessage(error)
    };
  }
}

export async function getVendorsInPQ(input: GetVendorsSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage;

        // 1) 고급 필터
        const advancedWhere = filterColumns({
          table: vendors,
          filters: input.filters,
          joinOperator: input.joinOperator,
        });

        // 2) 글로벌 검색
        let globalWhere: SQL<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: vendorPQSubmissions.vendorId
            })
            .from(vendorPQSubmissions)
            .innerJoin(
              vendors,
              eq(vendorPQSubmissions.vendorId, vendors.id)
            )
            .where(
              and(
                eq(vendorPQSubmissions.type, "PROJECT"),
                // 최소한 IN_PROGRESS부터는 작업이 시작된 상태이므로 포함
                not(eq(vendorPQSubmissions.status, "REQUESTED")), // REQUESTED 상태는 제외
                advancedWhere,
                globalWhere
              )
            );
            
          projectPqVendors.forEach(v => vendorIds.add(v.vendorId));
          
          // 중복 제거된 협력업체 ID 배열
          const uniqueVendorIds = Array.from(vendorIds);
          
          // 총 개수 (중복 제거 후)
          const total = uniqueVendorIds.length;
          
          if (total === 0) {
            return { data: [], total: 0 };
          }
          
          // 페이징 처리 (정렬 후 limit/offset 적용)
          const paginatedIds = uniqueVendorIds.slice(offset, offset + input.perPage);
          
          // 2) 페이징된 협력업체 상세 정보 조회
          const vendorsData = await selectVendors(tx, {
            where: inArray(vendors.id, paginatedIds),
            orderBy: input.sort.length > 0
              ? input.sort.map((item) => 
                item.desc ? desc(vendors.vendorName) : asc(vendors.vendorName)
            )
              : [asc(vendors.createdAt)],
          });
          
          // 3) 각 벤더별 PQ 상태 정보 추가 
          const vendorsWithPqInfo = await Promise.all(
            vendorsData.map(async (vendor) => {
              // 3-A) 첨부 파일 조회
              const attachments = await tx
                .select({
                  id: vendorAttachments.id,
                  fileName: vendorAttachments.fileName,
                  filePath: vendorAttachments.filePath,
                })
                .from(vendorAttachments)
                .where(eq(vendorAttachments.vendorId, vendor.id));
              
              // 3-B) 일반 PQ 제출 여부 확인 (PQ 답변이 있는지)
              const generalPqAnswers = await tx
                .select({ count: count() })
                .from(vendorPqCriteriaAnswers)
                .where(
                  and(
                    eq(vendorPqCriteriaAnswers.vendorId, vendor.id),
                    isNull(vendorPqCriteriaAnswers.projectId)
                  )
                );
                
              const hasGeneralPq = generalPqAnswers[0]?.count > 0;
              
              // 3-C) 프로젝트 PQ 정보 조회 (모든 상태 포함)
              const projectPqs = await tx
                .select({
                  projectId: vendorPQSubmissions.projectId,
                  projectName: projects.name,
                  status: vendorPQSubmissions.status,
                  submittedAt: vendorPQSubmissions.submittedAt,
                  approvedAt: vendorPQSubmissions.approvedAt,
                  rejectedAt: vendorPQSubmissions.rejectedAt
                })
                .from(vendorPQSubmissions)
                .innerJoin(
                  projects,
                  eq(vendorPQSubmissions.projectId, projects.id)
                )
                .where(
                  and(
                    eq(vendorPQSubmissions.vendorId, vendor.id),
                    eq(vendorPQSubmissions.type, "PROJECT"),
                    not(eq(vendorPQSubmissions.status, "REQUESTED")) // REQUESTED 상태는 제외
                  )
                );
              
              const hasProjectPq = projectPqs.length > 0;
              
              // 프로젝트 PQ 상태별 카운트
              const projectPqStatusCounts = {
                inProgress: projectPqs.filter(p => p.status === "IN_PROGRESS").length,
                submitted: projectPqs.filter(p => p.status === "SUBMITTED").length,
                approved: projectPqs.filter(p => p.status === "APPROVED").length,
                rejected: projectPqs.filter(p => p.status === "REJECTED").length,
                total: projectPqs.length
              };
              
              // 3-D) PQ 상태 정보 추가
              return {
                ...vendor,
                hasAttachments: attachments.length > 0,
                attachmentsList: attachments,
                pqInfo: {
                  hasGeneralPq,
                  hasProjectPq,
                  projectPqs,
                  projectPqStatusCounts,
                  // 현재 PQ 상태 (UI에 표시 용도)
                  pqStatus: getPqStatusDisplay(vendor.status, hasGeneralPq, hasProjectPq, projectPqStatusCounts)
                }
              };
            })
          );
          
          return { data: vendorsWithPqInfo, total };
        });

        // 페이지 수
        const pageCount = Math.ceil(total / input.perPage);

        return { data, pageCount };
      } catch (err) {
        console.error("Error in getVendorsInPQ:", err);
        // 에러 발생 시
        return { data: [], pageCount: 0 };
      }
    },
    [JSON.stringify(input)], // 캐싱 키
    {
      revalidate: 3600,
      tags: ["vendors-in-pq", "project-pqs"], // revalidateTag 호출 시 무효화
    }
  )();
}

// PQ 상태 표시 함수
function getPqStatusDisplay(
  vendorStatus: string, 
  hasGeneralPq: boolean, 
  hasProjectPq: boolean,
  projectPqCounts: { inProgress: number, submitted: number, approved: number, rejected: number, total: number }
): string {
  // 프로젝트 PQ 상태 문자열 생성
  let projectPqStatus = "";
  if (hasProjectPq) {
    const parts = [];
    if (projectPqCounts.inProgress > 0) {
      parts.push(`진행중: ${projectPqCounts.inProgress}`);
    }
    if (projectPqCounts.submitted > 0) {
      parts.push(`제출: ${projectPqCounts.submitted}`);
    }
    if (projectPqCounts.approved > 0) {
      parts.push(`승인: ${projectPqCounts.approved}`);
    }
    if (projectPqCounts.rejected > 0) {
      parts.push(`거부: ${projectPqCounts.rejected}`);
    }
    projectPqStatus = parts.join(", ");
  }

  // 일반 PQ + 프로젝트 PQ 조합 상태
  if (hasGeneralPq && hasProjectPq) {
    return `일반 PQ (${getPqVendorStatusText(vendorStatus)}) + 프로젝트 PQ (${projectPqStatus})`;
  } else if (hasGeneralPq) {
    return `일반 PQ (${getPqVendorStatusText(vendorStatus)})`;
  } else if (hasProjectPq) {
    return `프로젝트 PQ (${projectPqStatus})`;
  }
  
  return "PQ 정보 없음";
}

// 협력업체 상태 텍스트 변환
function getPqVendorStatusText(status: string): string {
  switch (status) {
    case "IN_PQ": return "진행중";
    case "PQ_SUBMITTED": return "제출됨";
    case "PQ_FAILED": return "실패";
    case "PQ_APPROVED": 
    case "APPROVED": return "승인됨";
    case "READY_TO_SEND": return "거래 준비";
    case "ACTIVE": return "활성";
    case "INACTIVE": return "비활성";
    case "BLACKLISTED": return "거래금지";
    default: return status;
  }
}


export type VendorStatus =
  | "PENDING_REVIEW"
  | "IN_REVIEW"
  | "REJECTED"
  | "IN_PQ"
  | "PQ_SUBMITTED"
  | "PQ_FAILED"
  | "APPROVED"
  | "ACTIVE"
  | "INACTIVE"
  | "BLACKLISTED"
  | "PQ_APPROVED"

  export async function updateVendorStatusAction(
    vendorId: number,
    newStatus: VendorStatus
  ) {
    try {
      // 1) Update DB
      await db.update(vendors)
        .set({ status: newStatus })
        .where(eq(vendors.id, vendorId))
  
      // 2) Load vendor's email & name
      const vendor = await db.select().from(vendors).where(eq(vendors.id, vendorId)).then(r => r[0])
      if (!vendor) {
        return { ok: false, error: "Vendor not found" }
      }
      const headersList = await headers();
      const host = headersList.get('host') || 'localhost:3000';
      const loginUrl = `http://${host}/partners/pq`
  
      // 3) Send email
      await sendEmail({
        to: vendor.email || "",
        subject: `Your PQ Status is now ${newStatus}`,
        template: "vendor-pq-status",  // matches .hbs file
        context: {
          name: vendor.vendorName,
          status: newStatus,
          loginUrl: loginUrl,  // etc.
        },
      })
      revalidateTag("vendors")
      revalidateTag("vendors-in-pq")
      return { ok: true }
    } catch (error) {
      console.error("updateVendorStatusAction error:", error)
      return { ok: false, error: String(error) }
    }
  }

  type ProjectPQStatus = "REQUESTED" | "IN_PROGRESS" | "SUBMITTED" | "APPROVED" | "REJECTED";

/**
 * Update the status of a project-specific PQ for a vendor
 */
export async function updateProjectPQStatusAction({
  vendorId,
  projectId,
  status,
  comment
}: {
  vendorId: number;
  projectId: number;
  status: ProjectPQStatus;
  comment?: string;
}) {
  try {
    const currentDate = new Date();
    
    // 1) Prepare update data with appropriate timestamps
    const updateData: any = {
      status,
      updatedAt: currentDate,
    };
    
    // Add status-specific fields
    if (status === "APPROVED") {
      updateData.approvedAt = currentDate;
    } else if (status === "REJECTED") {
      updateData.rejectedAt = currentDate;
      updateData.rejectReason = comment || null;
    } else if (status === "SUBMITTED") {
      updateData.submittedAt = currentDate;
    }
    
    // 2) Update the project PQ record
    await db
      .update(vendorPQSubmissions)
      .set(updateData)
      .where(
        and(
          eq(vendorPQSubmissions.vendorId, vendorId),
          eq(vendorPQSubmissions.projectId, projectId),
          eq(vendorPQSubmissions.type, "PROJECT")
        )
      );
    
    // 3) Load vendor and project details for email
    const vendor = await db
      .select({
        id: vendors.id,
        email: vendors.email,
        vendorName: vendors.vendorName
      })
      .from(vendors)
      .where(eq(vendors.id, vendorId))
      .then(rows => rows[0]);
    
    if (!vendor) {
      return { ok: false, error: "Vendor not found" };
    }
    
    const project = await db
      .select({
        name: projects.name
      })
      .from(projects)
      .where(eq(projects.id, projectId))
      .then(rows => rows[0]);
    
    if (!project) {
      return { ok: false, error: "Project not found" };
    }
    
    // 4) Send email notification
    await sendEmail({
      to: vendor.email || "",
      subject: `Your Project PQ for ${project.name} is now ${status}`,
      template: "vendor-project-pq-status", // matches .hbs file (you might need to create this)
      context: {
        name: vendor.vendorName,
        status,
        projectName: project.name,
        rejectionReason: status === "REJECTED" ? comment : undefined,
        hasRejectionReason: status === "REJECTED" && !!comment,
        loginUrl: `${process.env.NEXT_PUBLIC_URL}/partners/pq?projectId=${projectId}`,
        approvalDate: status === "APPROVED" ? currentDate.toLocaleDateString() : undefined,
        rejectionDate: status === "REJECTED" ? currentDate.toLocaleDateString() : undefined,
      },
    });
    
    // 5) Revalidate cache tags
    revalidateTag("vendors");
    revalidateTag("vendors-in-pq");
    revalidateTag(`vendor-project-pqs-${vendorId}`);
    revalidateTag(`project-pq-${projectId}`);
    revalidateTag(`project-vendors-${projectId}`);
    
    return { ok: true };
  } catch (error) {
    console.error("updateProjectPQStatusAction error:", error);
    return { ok: false, error: String(error) };
  }
}

// 코멘트 타입 정의
interface ItemComment {
  answerId: number;
  checkPoint: string; // 체크포인트 정보 추가
  code: string;      // 코드 정보 추가
  comment: string;
}

/**
 * PQ 변경 요청 처리 서버 액션
 * 
 * @param vendorId 협력업체 ID
 * @param comment 항목별 코멘트 배열 (answerId, checkPoint, code, comment로 구성)
 * @param generalComment 전체 PQ에 대한 일반 코멘트 (선택사항)
 */
export async function requestPqChangesAction({
  vendorId,
  projectId,
  comment,
  generalComment,
  reviewerName
}: {
  vendorId: number;
  projectId?: number; // Optional project ID for project-specific PQs
  comment: ItemComment[];
  generalComment?: string;
  reviewerName?: string;
}) {
  try {
    // 1) 상태 업데이트 (PQ 타입에 따라 다르게 처리)
    if (projectId) {
      // 프로젝트 PQ인 경우 vendorPQSubmissions 테이블 업데이트
      const projectPq = await db
        .select()
        .from(vendorPQSubmissions)
        .where(
          and(
            eq(vendorPQSubmissions.vendorId, vendorId),
            eq(vendorPQSubmissions.projectId, projectId),
            eq(vendorPQSubmissions.type, "PROJECT")
          )
        )
        .then(rows => rows[0]);

      if (!projectPq) {
        return { ok: false, error: "Project PQ record not found" };
      }

      await db
        .update(vendorPQSubmissions)
        .set({
          status: "IN_PROGRESS", // 변경 요청 상태로 설정
          updatedAt: new Date(),
        })
        .where(
          and(
            eq(vendorPQSubmissions.vendorId, vendorId),
            eq(vendorPQSubmissions.projectId, projectId),
            eq(vendorPQSubmissions.type, "PROJECT")
          )
        );
    } else {
      // 일반 PQ인 경우 vendors 테이블 업데이트
      await db
        .update(vendors)
        .set({
          status: "IN_PQ", // 변경 요청 상태로 설정
          updatedAt: new Date(),
        })
        .where(eq(vendors.id, vendorId));
    }

    // 2) 협력업체 정보 가져오기
    const vendor = await db
      .select()
      .from(vendors)
      .where(eq(vendors.id, vendorId))
      .then(r => r[0]);

    if (!vendor) {
      return { ok: false, error: "Vendor not found" };
    }

    // 프로젝트 정보 가져오기 (프로젝트 PQ인 경우)
    let projectName = "";
    if (projectId) {
      const project = await db
        .select({
          name: projects.name
        })
        .from(projects)
        .where(eq(projects.id, projectId))
        .then(rows => rows[0]);

      projectName = project?.name || "Unknown Project";
    }

    // 3) 각 항목별 코멘트 저장
    const currentDate = new Date();


    // 병렬로 모든 코멘트 저장
    if (comment && comment.length > 0) {
      const insertPromises = comment.map(item =>
        db.insert(vendorPqReviewLogs)
          .values({
            vendorPqCriteriaAnswerId: item.answerId,
            // reviewerId: reviewerId,
            reviewerName: reviewerName,
            reviewerComment: item.comment,
            createdAt: currentDate,
            // 추가 메타데이터 필드가 있다면 저장
            // 이런 메타데이터는 DB 스키마에 해당 필드가 있어야 함
            // meta: JSON.stringify({ checkPoint: item.checkPoint, code: item.code })
          })
      );

      // 모든 삽입 기다리기
      await Promise.all(insertPromises);
    }

    // 4) 변경 요청 이메일 보내기
    // 코멘트 목록 준비
    const commentItems = comment.map(item => ({
      id: item.answerId,
      code: item.code,
      checkPoint: item.checkPoint,
      text: item.comment
    }));

    // PQ 유형에 따라 이메일 제목 및 내용 조정
    const emailSubject = projectId
      ? `[IMPORTANT] Your Project PQ (${projectName}) requires changes`
      : `[IMPORTANT] Your PQ submission requires changes`;

    // 로그인 URL - 프로젝트 PQ인 경우 다른 경로로 안내
    const loginUrl = projectId
      ? `${process.env.NEXT_PUBLIC_URL}/partners/pq?projectId=${projectId}`
      : `${process.env.NEXT_PUBLIC_URL}/partners/pq`;

    await sendEmail({
      to: vendor.email || "",
      subject: emailSubject,
      template: "vendor-pq-comment", // matches .hbs file
      context: {
        name: vendor.vendorName,
        vendorCode: vendor.vendorCode,
        loginUrl,
        comments: commentItems,
        generalComment: generalComment || "",
        hasGeneralComment: !!generalComment,
        commentCount: commentItems.length,
        projectId,
        projectName,
        isProjPQ: !!projectId,
      },
    });

    // 5) 캐시 무효화 - PQ 유형에 따라 적절한 태그 무효화
    revalidateTag("vendors");
    revalidateTag("vendors-in-pq");
    
    if (projectId) {
      revalidateTag(`vendor-project-pqs-${vendorId}`);
      revalidateTag(`project-pq-${projectId}`);
      revalidateTag(`project-vendors-${projectId}`);
    }

    return { ok: true };
  } catch (error) {
    console.error("requestPqChangesAction error:", error);
    return { ok: false, error: String(error) };
  }
}

interface AddReviewCommentInput {
  answerId: number // vendorPqCriteriaAnswers.id
  comment: string
  reviewerName?: string
}

export async function addReviewCommentAction(input: AddReviewCommentInput) {
  try {
    // 1) Check that the answer row actually exists
    const existing = await db
      .select({ id: vendorPqCriteriaAnswers.id })
      .from(vendorPqCriteriaAnswers)
      .where(eq(vendorPqCriteriaAnswers.id, input.answerId))

    if (existing.length === 0) {
      return { ok: false, error: "Item not found" }
    }

    // 2) Insert the log
    await db.insert(vendorPqReviewLogs).values({
      vendorPqCriteriaAnswerId: input.answerId,
      reviewerComment: input.comment,
      reviewerName: input.reviewerName ?? "AdminUser",
    })

    return { ok: true }
  } catch (error) {
    console.error("addReviewCommentAction error:", error)
    return { ok: false, error: String(error) }
  }
}

interface GetItemReviewLogsInput {
  answerId: number
}

export async function getItemReviewLogsAction(input: GetItemReviewLogsInput) {
  try {
    
    const logs = await db
      .select()
      .from(vendorPqReviewLogs)
      .where(eq(vendorPqReviewLogs.vendorPqCriteriaAnswerId, input.answerId))
      .orderBy(desc(vendorPqReviewLogs.createdAt));
      
    return { ok: true, data: logs };
  } catch (error) {
    console.error("getItemReviewLogsAction error:", error);
    return { ok: false, error: String(error) };
  }
}

export interface VendorPQListItem {
  projectId: number;
  projectName: string;
  status: string;
  submittedAt?: Date | null; // Change to accept both undefined and null
}

export interface VendorPQsList {
  hasGeneralPq: boolean;
  generalPqStatus?: string; // vendor.status for general PQ
  projectPQs: VendorPQListItem[];
}

export async function getVendorPQsList(vendorId: number): Promise<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: vendorPQSubmissions.projectId,
        projectName: projects.name,
        status: vendorPQSubmissions.status,
        submittedAt: vendorPQSubmissions.submittedAt
      })
      .from(vendorPQSubmissions)
      .innerJoin(
        projects,
        eq(vendorPQSubmissions.projectId, projects.id)
      )
      .where(
        and(
          eq(vendorPQSubmissions.vendorId, vendorId),
          eq(vendorPQSubmissions.type, "PROJECT"),
          not(eq(vendorPQSubmissions.status, "REQUESTED")) // Exclude requests that haven't been started
        )
      )
      .orderBy(vendorPQSubmissions.updatedAt);
        
    return {
      hasGeneralPq,
      generalPqStatus,
      projectPQs: projectPQs
    };
    
  } catch (error) {
    console.error("Error fetching vendor PQs list:", error);
    return {
      hasGeneralPq: false,
      projectPQs: []
    };
  }
}


export async function loadGeneralPQData(vendorId: number) {
  "use server";
  return getPQDataByVendorId(vendorId)
}

export async function loadProjectPQData(vendorId: number, projectId: number) {
  "use server";
  return getPQDataByVendorId(vendorId, projectId)
}

export async function loadGeneralPQAction(vendorId: number) {
  return getPQDataByVendorId(vendorId);
}

export async function loadProjectPQAction(vendorId: number, projectId?: number): Promise<PQGroupData[]> {
  if (!projectId) {
    throw new Error("Project ID is required for loading project PQ data");
  }
  return getPQDataByVendorId(vendorId, projectId);
}



export async function getAllPQsByVendorId(vendorId: number) {
  try {
    const pqList = await db
      .select({
        id: vendorPQSubmissions.id,
        type: vendorPQSubmissions.type,
        status: vendorPQSubmissions.status,
        projectId: vendorPQSubmissions.projectId,
        projectName: projects.name,
        createdAt: vendorPQSubmissions.createdAt,
        updatedAt: vendorPQSubmissions.updatedAt,
        submittedAt: vendorPQSubmissions.submittedAt,
        approvedAt: vendorPQSubmissions.approvedAt,
        rejectedAt: vendorPQSubmissions.rejectedAt,
        rejectReason: vendorPQSubmissions.rejectReason,
      })
      .from(vendorPQSubmissions)
      .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id))
      .where(eq(vendorPQSubmissions.vendorId, vendorId))
      .orderBy(desc(vendorPQSubmissions.createdAt));
    
    return pqList;
  } catch (error) {
    console.error("Error fetching PQ list:", error);
    return [];
  }
}

// 특정 PQ의 상세 정보 조회 (개별 PQ 페이지용)
export async function getPQById(pqSubmissionId: number, vendorId: number) {
  try {
    const pq = await db
      .select({
        id: vendorPQSubmissions.id,
        vendorId: vendorPQSubmissions.vendorId,
        projectId: vendorPQSubmissions.projectId,
        type: vendorPQSubmissions.type,
        status: vendorPQSubmissions.status,
        createdAt: vendorPQSubmissions.createdAt,
        submittedAt: vendorPQSubmissions.submittedAt,
        approvedAt: vendorPQSubmissions.approvedAt,
        rejectedAt: vendorPQSubmissions.rejectedAt,
        rejectReason: vendorPQSubmissions.rejectReason,
        
        // 벤더 정보 (추가)
        vendorName: vendors.vendorName,
        vendorCode: vendors.vendorCode,
        vendorStatus: vendors.status,
        
        // 프로젝트 정보 (조인)
        projectName: projects.name,
        projectCode: projects.code,
      })
      .from(vendorPQSubmissions)
      .leftJoin(vendors, eq(vendorPQSubmissions.vendorId, vendors.id))
      .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id))
      .where(
        and(
          eq(vendorPQSubmissions.id, pqSubmissionId),
          eq(vendorPQSubmissions.vendorId, vendorId)
        )
      )
      .limit(1)
      .then(rows => rows[0]);
    
    if (!pq) {
      throw new Error("PQ not found or access denied");
    }
    
    return pq;
  } catch (error) {
    console.error("Error fetching PQ by ID:", error);
    throw error;
  }
}

export async function getPQStatusCounts(vendorId: number) {
  try {
    // 모든 PQ 상태 조회 (일반 PQ + 프로젝트 PQ)
    const pqStatuses = await db
      .select({
        status: vendorPQSubmissions.status,
        count: count(),
      })
      .from(vendorPQSubmissions)
      .where(eq(vendorPQSubmissions.vendorId, vendorId))
      .groupBy(vendorPQSubmissions.status);
    
    // 상태별 개수를 객체로 변환
    const statusCounts = {
      REQUESTED: 0,
      IN_PROGRESS: 0,
      SUBMITTED: 0,
      APPROVED: 0,
      REJECTED: 0,
    };
    
    // 조회된 결과를 statusCounts 객체에 매핑
    pqStatuses.forEach((item) => {
      if (item.status in statusCounts) {
        statusCounts[item.status as keyof typeof statusCounts] = item.count;
      }
    });
    
    return statusCounts;
  } catch (error) {
    console.error("Error fetching PQ status counts:", error);
    return {
      REQUESTED: 0,
      IN_PROGRESS: 0,
      SUBMITTED: 0,
      APPROVED: 0,
      REJECTED: 0,
    };
  }
}

// 상태 레이블 함수
function getStatusLabel(status: string): string {
  switch (status) {
    case "REQUESTED":
      return "요청됨";
    case "IN_PROGRESS":
      return "진행 중";
    case "SUBMITTED":
      return "제출됨";
    case "APPROVED":
      return "승인됨";
    case "REJECTED":
      return "거부됨";
    default:
      return status;
  }
}

export async function getPQSubmissions(input: GetPQSubmissionsSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage;

        const pqFilterMapping = createPQFilterMapping();
        const joinedTables = getPQJoinedTables();

        console.log(input, "input")

        // 1) 고급 필터 조건 (DataTableAdvancedToolbar에서)
        let advancedWhere: SQL<unknown> | undefined = undefined;
        if (input.filters && input.filters.length > 0) {
          advancedWhere = filterColumns({
            table: vendorPQSubmissions,
            filters: input.filters,
            joinOperator: input.joinOperator || 'and',
            joinedTables,
            customColumnMapping: pqFilterMapping,
          });
          console.log("advancedWhere:", advancedWhere);
        }

        // 2) 기본 필터 조건 (PQFilterSheet에서)
        let basicWhere: SQL<unknown> | undefined = undefined;
        if (input.basicFilters && input.basicFilters.length > 0) {
          basicWhere = filterColumns({
            table: vendorPQSubmissions,
            filters: input.basicFilters,
            joinOperator: input.basicJoinOperator || 'and',
            joinedTables,
            customColumnMapping: pqFilterMapping,
          });
          console.log("basicWhere:", basicWhere);
        }

        // 3) 글로벌 검색 조건
        let globalWhere: SQL<unknown> | undefined = undefined;
        if (input.search) {
          const s = `%${input.search}%`;
          
          const validSearchConditions: SQL<unknown>[] = [];
          
          // 기존 검색 조건들
          const nameCondition = ilike(vendors.vendorName, s);
          if (nameCondition) validSearchConditions.push(nameCondition);
          
          const codeCondition = ilike(vendors.vendorCode, s);
          if (codeCondition) validSearchConditions.push(codeCondition);
          
          const projectNameCondition = ilike(projects.name, s);
          if (projectNameCondition) validSearchConditions.push(projectNameCondition);
          
          const projectCodeCondition = ilike(projects.code, s);
          if (projectCodeCondition) validSearchConditions.push(projectCodeCondition);
          
          // 새로 추가된 검색 조건들
          const pqNumberCondition = ilike(vendorPQSubmissions.pqNumber, s);
          if (pqNumberCondition) validSearchConditions.push(pqNumberCondition);
          
          const requesterCondition = ilike(users.name, s);
          if (requesterCondition) validSearchConditions.push(requesterCondition);
          
          if (validSearchConditions.length > 0) {
            globalWhere = or(...validSearchConditions);
          }
        }

        // 4) 날짜 조건
        let fromDateWhere: SQL<unknown> | undefined = undefined;
        let toDateWhere: SQL<unknown> | undefined = undefined;
        
        if (input.submittedDateFrom) {
          const fromDate = new Date(input.submittedDateFrom);
          const condition = gte(vendorPQSubmissions.submittedAt, fromDate);
          if (condition) fromDateWhere = condition;
        }
        
        if (input.submittedDateTo) {
          const toDate = new Date(input.submittedDateTo);
          const condition = lte(vendorPQSubmissions.submittedAt, toDate);
          if (condition) toDateWhere = condition;
        }

        // 5) 최종 WHERE 조건 생성 - 각 그룹을 AND로 연결
        const whereConditions: SQL<unknown>[] = [];
        
        // 고급 필터 조건 추가
        if (advancedWhere) whereConditions.push(advancedWhere);
        
        // 기본 필터 조건 추가  
        if (basicWhere) whereConditions.push(basicWhere);
        
        // 기타 조건들 추가
        if (globalWhere) whereConditions.push(globalWhere);
        if (fromDateWhere) whereConditions.push(fromDateWhere);
        if (toDateWhere) whereConditions.push(toDateWhere);
        
        // 모든 조건을 AND로 연결
        const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined;

        console.log("Final WHERE conditions:", {
          advancedWhere: !!advancedWhere,
          basicWhere: !!basicWhere,
          globalWhere: !!globalWhere,
          dateConditions: !!(fromDateWhere || toDateWhere),
          totalConditions: whereConditions.length
        });
      
        // 6) 전체 데이터 수 조회
        const totalResult = await db
          .select({ count: count() })
          .from(vendorPQSubmissions)
          .leftJoin(vendors, eq(vendorPQSubmissions.vendorId, vendors.id))
          .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id))
          .leftJoin(users, eq(vendorPQSubmissions.requesterId, users.id))
          .leftJoin(vendorInvestigations, eq(vendorInvestigations.pqSubmissionId, vendorPQSubmissions.id))
          .where(finalWhere);

        const total = totalResult[0]?.count || 0;

        if (total === 0) {
          return { data: [], pageCount: 0 };
        }

        // 7) 정렬 및 페이징 처리된 데이터 조회
        const orderByColumns = input.sort.map((sort) => {
          const column = sort.id as keyof typeof vendorPQSubmissions.$inferSelect;
          return sort.desc ? desc(vendorPQSubmissions[column]) : asc(vendorPQSubmissions[column]);
        });

        if (orderByColumns.length === 0) {
          orderByColumns.push(desc(vendorPQSubmissions.updatedAt));
        }

        const pqSubmissions = await db
          .select({
            id: vendorPQSubmissions.id,
            type: vendorPQSubmissions.type,
            pqNumber: vendorPQSubmissions.pqNumber,
            requesterId: vendorPQSubmissions.requesterId,
            requesterName: users.name,
            status: vendorPQSubmissions.status,
            createdAt: vendorPQSubmissions.createdAt,
            updatedAt: vendorPQSubmissions.updatedAt,
            submittedAt: vendorPQSubmissions.submittedAt,
            approvedAt: vendorPQSubmissions.approvedAt,
            rejectedAt: vendorPQSubmissions.rejectedAt,
            rejectReason: vendorPQSubmissions.rejectReason,
            pqItems: vendorPQSubmissions.pqItems,
            // Vendor 정보
            vendorId: vendors.id,
            vendorName: vendors.vendorName,
            vendorCode: vendors.vendorCode,
            email: vendors.email,
            taxId: vendors.taxId,
            vendorStatus: vendors.status,
            // Project 정보 (프로젝트 PQ인 경우)
            projectId: projects.id,
            projectName: projects.name,
            projectCode: projects.code,
          })
          .from(vendorPQSubmissions)
          .leftJoin(vendors, eq(vendorPQSubmissions.vendorId, vendors.id))
          .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id))
          .leftJoin(users, eq(vendorPQSubmissions.requesterId, users.id))
          .where(finalWhere)
          .orderBy(...orderByColumns)
          .limit(input.perPage)
          .offset(offset);

        // 8) 각 PQ 제출에 대한 추가 정보 조회 (기존과 동일)
        const pqSubmissionsWithDetails = await Promise.all(
          pqSubmissions.map(async (submission) => {
            // 기본 반환 객체
            const baseResult = {
              ...submission,
              answerCount: 0,
              attachmentCount: 0,
              pqStatus: getStatusLabel(submission.status),
              pqTypeLabel: submission.type === "GENERAL" ? "일반 PQ" : "프로젝트 PQ",
            };
            
            // vendorId가 null이면 기본 정보만 반환
            if (submission.vendorId === null) {
              return baseResult;
            }
            
            try {
              // 답변 수 조회
              const vendorId = submission.vendorId;
              
              const answerWhereConditions: SQL<unknown>[] = [];
              
              const vendorCondition = eq(vendorPqCriteriaAnswers.vendorId, vendorId);
              if (vendorCondition) answerWhereConditions.push(vendorCondition);
              
              let projectCondition: SQL<unknown> | undefined;
              if (submission.projectId !== null) {
                projectCondition = eq(vendorPqCriteriaAnswers.projectId, submission.projectId);
              } else {
                projectCondition = isNull(vendorPqCriteriaAnswers.projectId);
              }
              
              if (projectCondition) answerWhereConditions.push(projectCondition);
              
              const answerWhere = and(...answerWhereConditions);
                     
              const answersResult = await db
                .select({ count: count() })
                .from(vendorPqCriteriaAnswers)
                .where(answerWhere);

              const answerCount = answersResult[0]?.count || 0;

              // 첨부 파일 수 조회
              const attachmentsResult = await db
                .select({ count: count() })
                .from(vendorPqCriteriaAnswers)
                .leftJoin(
                  vendorCriteriaAttachments,
                  eq(vendorCriteriaAttachments.vendorCriteriaAnswerId, vendorPqCriteriaAnswers.id)
                )
                .where(answerWhere);

              const attachmentCount = attachmentsResult[0]?.count || 0;

              const requesters = alias(users, 'requesters');
              const qmManagers = alias(users, 'qmManagers');
              
              const investigationResult = await db
                .select({
                  id: vendorInvestigations.id,
                  investigationStatus: vendorInvestigations.investigationStatus,
                  investigationAddress: vendorInvestigations.investigationAddress,
                  investigationMethod: vendorInvestigations.investigationMethod,
                  scheduledStartAt: vendorInvestigations.scheduledStartAt,
                  scheduledEndAt: vendorInvestigations.scheduledEndAt,
                  requestedAt: vendorInvestigations.requestedAt,
                  confirmedAt: vendorInvestigations.confirmedAt,
                  completedAt: vendorInvestigations.completedAt,
                  forecastedAt: vendorInvestigations.forecastedAt,
                  evaluationScore: vendorInvestigations.evaluationScore,
                  evaluationResult: vendorInvestigations.evaluationResult,
                  investigationNotes: vendorInvestigations.investigationNotes,
                  requesterId: vendorInvestigations.requesterId,
                  requesterName: requesters.name,
                  qmManagerId: vendorInvestigations.qmManagerId,
                  qmManagerName: qmManagers.name,
                  qmManagerEmail: qmManagers.email,
                })
                .from(vendorInvestigations)
                .leftJoin(requesters, eq(vendorInvestigations.requesterId, requesters.id))
                .leftJoin(qmManagers, eq(vendorInvestigations.qmManagerId, qmManagers.id))
                .where(and(
                  eq(vendorInvestigations.vendorId, submission.vendorId),
                  eq(vendorInvestigations.pqSubmissionId, submission.id)
                ))
                .orderBy(desc(vendorInvestigations.createdAt))
                .limit(1);

              const investigation = investigationResult[0] || null;

              // investigation이 있으면 해당 investigation의 최신 siteVisitRequest 조회
              let siteVisitRequestId: number | null = null;
              if (investigation) {
                const siteVisitRequestResult = await db
                  .select({ id: siteVisitRequests.id })
                  .from(siteVisitRequests)
                  .where(eq(siteVisitRequests.investigationId, investigation.id))
                  .orderBy(desc(siteVisitRequests.createdAt))
                  .limit(1);
                
                siteVisitRequestId = siteVisitRequestResult[0]?.id || null;
              }

              return {
                ...baseResult,
                answerCount,
                attachmentCount,
                siteVisitRequestId,
                investigation
              };
            } catch (error) {
              console.error("Error fetching PQ details:", error);
              return baseResult;
            }
          })
        );

        const pageCount = Math.ceil(total / input.perPage);

        return { data: pqSubmissionsWithDetails, pageCount };
      } catch (err) {
        console.error("Error in getPQSubmissions:", err);
        return { data: [], pageCount: 0 };
      }
    },
    [JSON.stringify(input)], // 캐싱 키
    {
      revalidate: 3600,
      tags: ["pq-submissions"], // revalidateTag 호출 시 무효화
    }
  )();
}

export async function getPQStatusCountsAll() {
  try {
    // 모든 PQ 상태별 개수 조회 (벤더 제한 없음)
    const pqStatuses = await db
      .select({
        status: vendorPQSubmissions.status,
        count: count(),
      })
      .from(vendorPQSubmissions)
      .groupBy(vendorPQSubmissions.status);
    
    // 상태별 개수를 객체로 변환
    const statusCounts = {
      REQUESTED: 0,
      IN_PROGRESS: 0,
      SUBMITTED: 0,
      APPROVED: 0,
      REJECTED: 0,
    };
    
    // 조회된 결과를 statusCounts 객체에 매핑
    pqStatuses.forEach((item) => {
      if (item.status in statusCounts) {
        statusCounts[item.status as keyof typeof statusCounts] = item.count;
      }
    });
    
    return statusCounts;
  } catch (error) {
    console.error("Error fetching PQ status counts:", error);
    return {
      REQUESTED: 0,
      IN_PROGRESS: 0,
      SUBMITTED: 0,
      APPROVED: 0,
      REJECTED: 0,
    };
  }
}

// PQ 타입별, 상태별 개수 집계 함수 (추가 옵션)
export async function getPQDetailedStatusCounts() {
  try {
    // 타입별, 상태별 개수 조회
    const pqStatuses = await db
      .select({
        type: vendorPQSubmissions.type,
        status: vendorPQSubmissions.status,
        count: count(),
      })
      .from(vendorPQSubmissions)
      .groupBy(vendorPQSubmissions.type, vendorPQSubmissions.status);
    
    // 결과를 저장할 객체 초기화
    const result = {
      GENERAL: {
        REQUESTED: 0,
        IN_PROGRESS: 0,
        SUBMITTED: 0,
        APPROVED: 0,
        REJECTED: 0,
        total: 0
      },
      PROJECT: {
        REQUESTED: 0,
        IN_PROGRESS: 0,
        SUBMITTED: 0,
        APPROVED: 0,
        REJECTED: 0,
        total: 0
      },
      total: {
        REQUESTED: 0,
        IN_PROGRESS: 0,
        SUBMITTED: 0,
        APPROVED: 0,
        REJECTED: 0,
        total: 0
      }
    };
    
    // 결과 매핑
    pqStatuses.forEach((item) => {
      if (item.type && item.status) {
        const type = item.type as keyof typeof result;
        const status = item.status as keyof typeof result.GENERAL;
        
        if (type in result && status in result[type]) {
          // 타입별 상태 카운트 업데이트
          result[type][status] = item.count;
          
          // 타입별 합계 업데이트
          result[type].total += item.count;
          
          // 전체 상태별 카운트 업데이트
          result.total[status] += item.count;
          
          // 전체 합계 업데이트
          result.total.total += item.count;
        }
      }
    });
    
    return result;
  } catch (error) {
    console.error("Error fetching detailed PQ status counts:", error);
    return {
      GENERAL: {
        REQUESTED: 0,
        IN_PROGRESS: 0,
        SUBMITTED: 0,
        APPROVED: 0,
        REJECTED: 0,
        total: 0
      },
      PROJECT: {
        REQUESTED: 0,
        IN_PROGRESS: 0,
        SUBMITTED: 0,
        APPROVED: 0,
        REJECTED: 0,
        total: 0
      },
      total: {
        REQUESTED: 0,
        IN_PROGRESS: 0,
        SUBMITTED: 0,
        APPROVED: 0,
        REJECTED: 0,
        total: 0
      }
    };
  }
}

/**
 * SHI 코멘트 업데이트 액션
 */
export async function updateSHICommentAction({
  answerId,
  shiComment,
}: {
  answerId: number;
  shiComment: string;
}) {
  try {
    await db
      .update(vendorPqCriteriaAnswers)
      .set({
        shiComment,
        updatedAt: new Date(),
      })
      .where(eq(vendorPqCriteriaAnswers.id, answerId));

    return { ok: true };
  } catch (error) {
    console.error("updateSHICommentAction error:", error);
    return { ok: false, error: String(error) };
  }
}

// PQ 승인 액션
export async function approvePQAction({
  pqSubmissionId,
  vendorId,
}: {
  pqSubmissionId: number;
  vendorId: number;
}) {
  unstable_noStore();
  
  try {
    const headersList = await headers();
    const host = headersList.get('host') || 'localhost:3000';
    const currentDate = new Date();
    
    // 1. PQ 제출 정보 조회
    const pqSubmission = await db
      .select({
        id: vendorPQSubmissions.id,
        vendorId: vendorPQSubmissions.vendorId,
        projectId: vendorPQSubmissions.projectId,
        type: vendorPQSubmissions.type,
        status: vendorPQSubmissions.status,
      })
      .from(vendorPQSubmissions)
      .where(
        and(
          eq(vendorPQSubmissions.id, pqSubmissionId),
          eq(vendorPQSubmissions.vendorId, vendorId)
        )
      )
      .then(rows => rows[0]);
      
    if (!pqSubmission) {
      return { ok: false, error: "PQ submission not found" };
    }
    
    // 2. 상태 확인 (SUBMITTED 상태만 승인 가능)
    if (pqSubmission.status !== "SUBMITTED") {
      return { 
        ok: false, 
        error: `Cannot approve PQ in current status: ${pqSubmission.status}` 
      };
    }
    
    // 3. 벤더 정보 조회
    const vendor = await db
      .select({
        id: vendors.id,
        vendorName: vendors.vendorName,
        email: vendors.email,
        status: vendors.status,
      })
      .from(vendors)
      .where(eq(vendors.id, vendorId))
      .then(rows => rows[0]);
      
    if (!vendor) {
      return { ok: false, error: "Vendor not found" };
    }
    
    // 4. 프로젝트 정보 (프로젝트 PQ인 경우)
    let projectName = '';
    if (pqSubmission.projectId) {
      const projectData = await db
        .select({
          id: projects.id,
          name: projects.name,
        })
        .from(projects)
        .where(eq(projects.id, pqSubmission.projectId))
        .then(rows => rows[0]);
        
      projectName = projectData?.name || 'Unknown Project';
    }
    
    // 5. PQ 상태 업데이트
    await db
      .update(vendorPQSubmissions)
      .set({
        status: "APPROVED",
        approvedAt: currentDate,
        updatedAt: currentDate,
      })
      .where(eq(vendorPQSubmissions.id, pqSubmissionId));
    
    // 6. 일반 PQ인 경우 벤더 상태 업데이트 (선택사항)
    if (pqSubmission.type === "GENERAL") {
      await db
        .update(vendors)
        .set({
          status: "PQ_APPROVED",
          updatedAt: currentDate,
        })
        .where(eq(vendors.id, vendorId));
    }
    
    // 7. 벤더에게 이메일 알림 발송
    if (vendor.email) {
      try {
        const emailSubject = pqSubmission.projectId
          ? `[eVCP] Project PQ Approved for ${projectName}`
          : "[eVCP] General PQ Approved";
          
        const portalUrl = `${host}/partners/pq`;
          
        await sendEmail({
          to: vendor.email,
          subject: emailSubject,
          template: "pq-approved-vendor",
          context: {
            vendorName: vendor.vendorName,
            projectId: pqSubmission.projectId,
            projectName: projectName,
            isProjectPQ: !!pqSubmission.projectId,
            approvedDate: currentDate.toLocaleString(),
            portalUrl,
          }
        });
      } catch (emailError) {
        console.error("Failed to send vendor notification:", emailError);
        // 이메일 발송 실패가 전체 프로세스를 중단하지 않음
      }
    }
    
    // 8. 캐시 무효화
    revalidateTag("vendors");
    revalidateTag("vendor-status-counts");
    revalidateTag("pq-submissions");
    revalidateTag(`vendor-pq-submissions-${vendorId}`);
    
    if (pqSubmission.projectId) {
      revalidateTag(`project-pq-submissions-${pqSubmission.projectId}`);
      revalidateTag(`project-vendors-${pqSubmission.projectId}`);
    }
    
    return { ok: true };
  } catch (error) {
    console.error("PQ approve error:", error);
    return { ok: false, error: getErrorMessage(error) };
  }
}

// PQ 거부 액션
export async function rejectPQAction({
  pqSubmissionId,
  vendorId,
  rejectReason
}: {
  pqSubmissionId: number;
  vendorId: number;
  rejectReason: string;
}) {
  unstable_noStore();
  
  try {
    const headersList = await headers();
    const host = headersList.get('host') || 'localhost:3000';
    const currentDate = new Date();
    
    // 1. PQ 제출 정보 조회
    const pqSubmission = await db
      .select({
        id: vendorPQSubmissions.id,
        vendorId: vendorPQSubmissions.vendorId,
        projectId: vendorPQSubmissions.projectId,
        type: vendorPQSubmissions.type,
        status: vendorPQSubmissions.status,
      })
      .from(vendorPQSubmissions)
      .where(
        and(
          eq(vendorPQSubmissions.id, pqSubmissionId),
          eq(vendorPQSubmissions.vendorId, vendorId)
        )
      )
      .then(rows => rows[0]);
      
    if (!pqSubmission) {
      return { ok: false, error: "PQ submission not found" };
    }
    
    // 2. 상태 확인 (SUBMITTED 상태만 거부 가능)
    if (pqSubmission.status !== "SUBMITTED") {
      return { 
        ok: false, 
        error: `Cannot reject PQ in current status: ${pqSubmission.status}` 
      };
    }
    
    // 3. 벤더 정보 조회
    const vendor = await db
      .select({
        id: vendors.id,
        vendorName: vendors.vendorName,
        email: vendors.email,
        status: vendors.status,
      })
      .from(vendors)
      .where(eq(vendors.id, vendorId))
      .then(rows => rows[0]);
      
    if (!vendor) {
      return { ok: false, error: "Vendor not found" };
    }
    
    // 4. 프로젝트 정보 (프로젝트 PQ인 경우)
    let projectName = '';
    if (pqSubmission.projectId) {
      const projectData = await db
        .select({
          id: projects.id,
          name: projects.name,
        })
        .from(projects)
        .where(eq(projects.id, pqSubmission.projectId))
        .then(rows => rows[0]);
        
      projectName = projectData?.name || 'Unknown Project';
    }
    
    // 5. PQ 상태 업데이트
    await db
      .update(vendorPQSubmissions)
      .set({
        status: "REJECTED",
        rejectedAt: currentDate,
        rejectReason: rejectReason,
        updatedAt: currentDate,
      })
      .where(eq(vendorPQSubmissions.id, pqSubmissionId));
    
    // 6. 일반 PQ인 경우 벤더 상태 업데이트 (선택사항)
    if (pqSubmission.type === "GENERAL") {
      await db
        .update(vendors)
        .set({
          status: "PQ_FAILED",
          updatedAt: currentDate,
        })
        .where(eq(vendors.id, vendorId));
    }
    
    // 7. 벤더에게 이메일 알림 발송
    if (vendor.email) {
      try {
        const emailSubject = pqSubmission.projectId
          ? `[eVCP] Project PQ Rejected for ${projectName}`
          : "[eVCP] General PQ Rejected";
          
        const portalUrl = `${host}/partners/pq`;
          
        await sendEmail({
          to: vendor.email,
          subject: emailSubject,
          template: "pq-rejected-vendor",
          context: {
            vendorName: vendor.vendorName,
            projectId: pqSubmission.projectId,
            projectName: projectName,
            isProjectPQ: !!pqSubmission.projectId,
            rejectedDate: currentDate.toLocaleString(),
            rejectReason: rejectReason,
            portalUrl,
          }
        });
      } catch (emailError) {
        console.error("Failed to send vendor notification:", emailError);
        // 이메일 발송 실패가 전체 프로세스를 중단하지 않음
      }
    }
    
    // 8. 캐시 무효화
    revalidateTag("vendors");
    revalidateTag("vendor-status-counts");
    revalidateTag("pq-submissions");
    revalidateTag(`vendor-pq-submissions-${vendorId}`);
    
    if (pqSubmission.projectId) {
      revalidateTag(`project-pq-submissions-${pqSubmission.projectId}`);
      revalidateTag(`project-vendors-${pqSubmission.projectId}`);
    }
    
    return { ok: true };
  } catch (error) {
    console.error("PQ reject error:", error);
    return { ok: false, error: getErrorMessage(error) };
  }
}


// 실사 의뢰 생성 서버 액션
export async function requestInvestigationAction(
  pqSubmissionIds: number[],
  data: {
    qmManagerId: number,
    forecastedAt: Date,
    investigationAddress: string,
    investigationNotes?: string
  }
) {
  try {
    // 세션에서 요청자 정보 가져오기
    const session = await getServerSession(authOptions);
    const requesterId = session?.user?.id ? Number(session.user.id) : null;

    if (!requesterId) {
      return { success: false, error: "인증된 사용자만 실사를 의뢰할 수 있습니다." };
    }

    const result = await db.transaction(async (tx) => {
      // PQ 제출 정보 조회
      const pqSubmissions = await tx
        .select({
          id: vendorPQSubmissions.id,
          vendorId: vendorPQSubmissions.vendorId,
        })
        .from(vendorPQSubmissions)
        .where(
          and(
            inArray(vendorPQSubmissions.id, pqSubmissionIds),
            eq(vendorPQSubmissions.status, "APPROVED")
          )
        );

      if (pqSubmissions.length === 0) {
        throw new Error("승인된 PQ 제출 항목이 없습니다.");
      }

      const now = new Date();

      // 각 PQ에 대한 실사 요청 생성 - 타입이 정확히 맞는지 확인
      const investigations = pqSubmissions.map((pq) => {
        return {
          vendorId: pq.vendorId,
          pqSubmissionId: pq.id,
          investigationStatus: "PLANNED" as const, // enum 타입으로 명시적 지정
          qmManagerId: data.qmManagerId,
          forecastedAt: data.forecastedAt,
          investigationAddress: data.investigationAddress,
          investigationNotes: data.investigationNotes || null,
          requesterId: requesterId,
          requestedAt: now,
          createdAt: now,
          updatedAt: now,
        };
      });

      // 실사 요청 저장
      const created = await tx
        .insert(vendorInvestigations)
        .values(investigations)
        .returning();

      return created;
    });

    // 캐시 무효화
    revalidateTag("vendor-investigations");
    revalidateTag("pq-submissions");
    revalidateTag("vendor-pq-submissions");
    revalidatePath("/evcp/pq_new");

    return {
      success: true,
      count: result.length,
      data: result
    };
  } catch (err) {
    console.error("실사 의뢰 중 오류 발생:", err);
    return {
      success: false,
      error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다."
    };
  }
}

// 실사 의뢰 취소 서버 액션
export async function cancelInvestigationAction(investigationIds: number[]) {
  try {
    const session = await getServerSession(authOptions)
    const userId = session?.user?.id ? Number(session.user.id) : null

    if (!userId) {
      return { success: false, error: "인증된 사용자만 실사를 취소할 수 있습니다." }
    }

    const result = await db.transaction(async (tx) => {
      // PLANNED 상태인 실사만 취소 가능
      const updatedInvestigations = await tx
        .update(vendorInvestigations)
        .set({
          investigationStatus: "CANCELED",
          updatedAt: new Date(),
        })
        .where(
          and(
            inArray(vendorInvestigations.id, investigationIds),
            eq(vendorInvestigations.investigationStatus, "PLANNED")
          )
        )
        .returning()

      return updatedInvestigations
    })

    // 캐시 무효화
    revalidateTag("vendor-investigations")
    revalidateTag("pq-submissions")

    return { 
      success: true, 
      count: result.length, 
      data: result 
    }
  } catch (err) {
    console.error("실사 취소 중 오류 발생:", err)
    return { 
      success: false, 
      error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다." 
    }
  }
}

// 실사 결과 발송 서버 액션
export async function sendInvestigationResultsAction(input: {
  investigationIds: number[];
  purchaseComment?: string;
}) {
  try {
    const session = await getServerSession(authOptions)
    const userId = session?.user?.id ? Number(session.user.id) : null

    if (!userId) {
      return { success: false, error: "인증된 사용자만 실사 결과를 발송할 수 있습니다." }
    }

    const result = await db.transaction(async (tx) => {
      // 완료된 실사만 결과 발송 가능
      const investigations = await tx
        .select({
          id: vendorInvestigations.id,
          vendorId: vendorInvestigations.vendorId,
          pqSubmissionId: vendorInvestigations.pqSubmissionId,
          evaluationResult: vendorInvestigations.evaluationResult,
          investigationNotes: vendorInvestigations.investigationNotes,
          investigationAddress: vendorInvestigations.investigationAddress,
          investigationMethod: vendorInvestigations.investigationMethod,
          confirmedAt: vendorInvestigations.confirmedAt,
          // Vendor 정보
          vendorCode: vendors.vendorCode,
          vendorName: vendors.vendorName,
          vendorEmail: vendors.email,
          // PQ 정보
          pqNumber: vendorPQSubmissions.pqNumber,
          pqItems: vendorPQSubmissions.pqItems,
          projectCode: projects.code,
          projectName: projects.name,
          // 발신자 정보
          senderName: users.name,
          senderEmail: users.email,
        })
        .from(vendorInvestigations)
        .leftJoin(vendors, eq(vendorInvestigations.vendorId, vendors.id))
        .leftJoin(vendorPQSubmissions, eq(vendorInvestigations.pqSubmissionId, vendorPQSubmissions.id))
        .leftJoin(projects, eq(vendorPQSubmissions.projectId, projects.id))
        .leftJoin(users, eq(vendorInvestigations.requesterId, users.id))
        .where(
          and(
            inArray(vendorInvestigations.id, input.investigationIds),
            eq(vendorInvestigations.investigationStatus, "COMPLETED")
          )
        )

      if (investigations.length === 0) {
        throw new Error("발송할 수 있는 완료된 실사가 없습니다.")
      }

      // 각 실사에 대해 이메일 발송
      const emailResults = await Promise.all(
        investigations.map(async (investigation) => {
          try {
            // 이메일 컨텍스트 구성
            const emailContext = {
              // 기본 정보
              pqNumber: investigation.pqNumber || "N/A",
              vendorCode: investigation.vendorCode || "N/A",
              vendorName: investigation.vendorName || "N/A",
              
              // 실사 정보 - pqItems를 itemCode-itemName 형태로 모든 항목 표시
              auditItem: (() => {
                if (investigation.pqItems) {
                  try {
                    const parsed = typeof investigation.pqItems === 'string' 
                      ? JSON.parse(investigation.pqItems) 
                      : investigation.pqItems;
                    if (Array.isArray(parsed)) {
                      return parsed.map(item => {
                        if (typeof item === 'string') return item;
                        if (typeof item === 'object') {
                          const code = item.itemCode || item.code || "";
                          const name = item.itemName || item.name || "";
                          if (code && name) return `${code}-${name}`;
                          return name || code || String(item);
                        }
                        return String(item);
                      }).join(', ');
                    }
                    return String(parsed);
                  } catch {
                    return String(investigation.pqItems);
                  }
                }
                return investigation.projectName || "N/A";
              })(),
              auditFactoryAddress: investigation.investigationAddress || "N/A",
              auditMethod: getInvestigationMethodLabel(investigation.investigationMethod || ""),
              auditResult: investigation.evaluationResult === "APPROVED" ? "Pass(승인)" : 
                          investigation.evaluationResult === "SUPPLEMENT" ? "Pass(조건부승인)" : 
                          investigation.evaluationResult === "REJECTED" ? "Fail(미승인)" : "N/A",
              additionalNotes: input.purchaseComment || investigation.investigationNotes || "",
              
              // 발신자 정보
              senderName: investigation.senderName || "삼성중공업",
              senderEmail: investigation.senderEmail || "procurement@samsung.com",
              
              // 이메일 제목
              subject: `[SHI Audit] 실사 결과 안내 _ ${investigation.vendorName} _ PQ No. ${investigation.pqNumber}`,
            }

            // 이메일 발송
            if (investigation.vendorEmail) {
              await sendEmail({
                to: investigation.vendorEmail,
                subject: emailContext.subject,
                template: "audit-result-notice",
                context: emailContext,
              })
            } else {
              throw new Error("벤더 이메일 주소가 없습니다.")
            }

            return { success: true, investigationId: investigation.id }
          } catch (error) {
            console.error(`실사 ID ${investigation.id} 이메일 발송 실패:`, error)
            return { success: false, investigationId: investigation.id, error: error instanceof Error ? error.message : "알 수 없는 오류" }
          }
        })
      )

      // 성공한 실사들의 상태를 RESULT_SENT로 업데이트
      const successfulInvestigationIds = emailResults
        .filter(result => result.success)
        .map(result => result.investigationId)

      if (successfulInvestigationIds.length > 0) {
        await tx
          .update(vendorInvestigations)
          .set({
            investigationStatus: "RESULT_SENT",
            purchaseComment: input.purchaseComment,
            updatedAt: new Date(),
          })
          .where(inArray(vendorInvestigations.id, successfulInvestigationIds))

        // 정규업체등록관리에 레코드 생성 로직
        const successfulInvestigations = investigations.filter(inv => 
          successfulInvestigationIds.includes(inv.id)
        );

        for (const investigation of successfulInvestigations) {
          // 1. 미실사 PQ는 제외 (이미 COMPLETED 상태인 것만 처리하므로 실사된 것들)
          // 2. 승인된 실사만 정규업체등록 대상
          if (investigation.evaluationResult === "APPROVED") {
            try {
              // 기존 정규업체등록 레코드 확인
              const existingRegistration = await tx
                .select({ id: vendorRegularRegistrations.id })
                .from(vendorRegularRegistrations)
                .where(eq(vendorRegularRegistrations.vendorId, investigation.vendorId))
                .limit(1);

              // 프로젝트 PQ의 경우 기존 레코드가 있으면 skip, 없으면 생성
              // 일반 PQ의 경우 무조건 생성 (이미 체크는 위에서 함)
              if (existingRegistration.length === 0) {
                // pqItems를 majorItems로 변환 - JSON 통째로 넘겨줌
                let majorItemsJson = null;
                if (investigation.pqItems) {
                  try {
                    // 이미 파싱된 객체거나 JSON 문자열인 경우 모두 처리
                    const parsed = typeof investigation.pqItems === 'string' 
                      ? JSON.parse(investigation.pqItems) 
                      : investigation.pqItems;
                    
                    // 원본 구조를 최대한 보존하면서 JSON으로 저장
                    majorItemsJson = JSON.stringify(parsed);
                  } catch {
                    // 파싱 실패 시 문자열로 저장
                    majorItemsJson = JSON.stringify([{ 
                      itemCode: "UNKNOWN", 
                      itemName: String(investigation.pqItems) 
                    }]);
                  }
                }

                await tx.insert(vendorRegularRegistrations).values({
                  vendorId: investigation.vendorId,
                  status: "under_review", // 실사 통과 상태로 시작
                  majorItems: majorItemsJson,
                  registrationRequestDate: new Date().toISOString().split('T')[0], // date 타입으로 변환
                  remarks: `PQ 실사 통과로 자동 생성 (PQ번호: ${investigation.pqNumber || 'N/A'})`,
                });

                console.log(`✅ 정규업체등록 레코드 생성: 벤더 ID ${investigation.vendorId}`);
              } else {
                console.log(`⏭️ 정규업체등록 레코드 이미 존재: 벤더 ID ${investigation.vendorId} (Skip)`);
              }
            } catch (error) {
              console.error(`❌ 정규업체등록 레코드 생성 실패 (벤더 ID: ${investigation.vendorId}):`, error);
              // 정규업체등록 생성 실패는 전체 프로세스를 중단하지 않음
            }
          }
        }
      }

      return {
        totalCount: investigations.length,
        successCount: emailResults.filter(r => r.success).length,
        failedCount: emailResults.filter(r => !r.success).length,
        emailResults,
      }
    })

    // 캐시 무효화
    revalidateTag("vendor-investigations")
    revalidateTag("pq-submissions")
    revalidateTag("vendor-regular-registrations")

    return { 
      success: true, 
      data: result,
      message: `${result.successCount}개 실사 결과가 성공적으로 발송되었습니다.`
    }
  } catch (err) {
    console.error("실사 결과 발송 중 오류 발생:", err)
    return { 
      success: false, 
      error: err instanceof Error ? err.message : "알 수 없는 오류가 발생했습니다." 
    }
  }
}

// 실사 방법 라벨 변환 함수
function getInvestigationMethodLabel(method: string): string {
  switch (method) {
    case "PURCHASE_SELF_EVAL":
      return "구매자체평가"
    case "DOCUMENT_EVAL":
      return "서류평가"
    case "PRODUCT_INSPECTION":
      return "제품검사평가"
    case "SITE_VISIT_EVAL":
      return "방문실사평가"
    default:
      return method
  }
}

export async function getQMManagers() {
  try {
    // QM 부서 사용자만 필터링 (department 필드가 있다고 가정)
    // 또는 QM 역할을 가진 사용자만 필터링 (role 필드가 있다고 가정)
    const qmUsers = await db
      .select({
        id: users.id,
        name: users.name,
        email: users.email,
      })
      .from(users)
      // .where(
      //   // 필요에 따라 조건 조정 (예: QM 부서 또는 특정 역할만)
      //   // eq(users.department, "QM") 또는
      //   // eq(users.role, "QM_MANAGER")
      //   // 테스트를 위해 모든 사용자 반환도 가능
      //   eq(users.active, true)
      // )
      .orderBy(users.name)

    return { 
      data: qmUsers,
      success: true 
    }
  } catch (error) {
    console.error("QM 담당자 목록 조회 오류:", error)
    return { 
      data: [],
      success: false,
      error: error instanceof Error ? error.message : "QM 담당자 목록을 가져오는 중 오류가 발생했습니다." 
    }
  }
}

export async function getFactoryLocationAnswer(vendorId: number, projectId: number | null = null) {
  try {
    // 1. "Location of Factory" 체크포인트를 가진 criteria 찾기
    const criteria = await db
      .select({
        id: pqCriterias.id
      })
      .from(pqCriterias)
      .where(ilike(pqCriterias.checkPoint, "%Location of Factory%"))
      .limit(1);

    if (!criteria.length) {
      return { success: false, message: "Factory Location 질문을 찾을 수 없습니다." };
    }

    const criteriaId = criteria[0].id;

    // 2. 해당 criteria에 대한 벤더의 응답 조회
    const answerQuery = db
      .select({
        answer: vendorPqCriteriaAnswers.answer
      })
      .from(vendorPqCriteriaAnswers)
      .where(
        and(
          eq(vendorPqCriteriaAnswers.vendorId, vendorId),
          eq(vendorPqCriteriaAnswers.criteriaId, criteriaId)
        )
      );

    // 프로젝트 ID가 있으면 추가 조건
    if (projectId !== null) {
      answerQuery.where(eq(vendorPqCriteriaAnswers.projectId, projectId));
    } else {
      answerQuery.where(eq(vendorPqCriteriaAnswers.projectId, null));
    }

    const answers = await answerQuery.limit(1);

    if (!answers.length || !answers[0].answer) {
      return { success: false, message: "공장 위치 정보를 찾을 수 없습니다." };
    }

    return { 
      success: true, 
      factoryLocation: answers[0].answer 
    };
  } catch (error) {
    console.error("Factory location 조회 오류:", error);
    return { success: false, message: "오류가 발생했습니다." };
  }
}

// -----------------------------------------------------------------------------
// PQ LISTS (GENERAL / PROJECT / NON_INSPECTION) CRUD + 조회

// LOAD CRITERIAS BY LIST
export async function getPqCriteriasByListId(listId: number) {
  const criterias = await db
    .select()
    .from(pqCriterias)
    .where(eq(pqCriterias.pqListId, listId))
    .orderBy(pqCriterias.groupName, pqCriterias.code);
  return criterias;
}

// -----------------------------------------------------------------------------
// PQ LISTS CRUD 액션 - 개선된 버전
// -----------------------------------------------------------------------------


export async function getPQLists(input: GetPqListsSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage;

        let globalWhere;
        if (input.search) {
          const s = `%${input.search}%`;
          globalWhere = or(
            ilike(pqLists.name, s),
            ilike(pqLists.type, s)
          );
        }

        const advancedWhere = input.filters
          ? filterColumns({ table: pqLists, filters: input.filters, joinOperator: input.joinOperator })
          : undefined;

        const finalWhere = and(
          // eq(pqLists.isDeleted, false),
          advancedWhere,
          globalWhere
        );

        const orderBy = input.sort.length
          ? input.sort.map((s) => (s.desc ? desc(pqLists.createdAt) : asc(pqLists.createdAt)))
          : [desc(pqLists.createdAt)];

        const { data, total } = await db.transaction(async (tx) => {
          // 만료된 PQ 리스트들을 자동으로 비활성화
          const now = new Date();
          await tx
            .update(pqLists)
            .set({ 
              isDeleted: true, 
              updatedAt: now 
            })
            .where(
              and(
                eq(pqLists.isDeleted, false),
                lt(pqLists.validTo, now),
                isNotNull(pqLists.validTo)
              )
            );

          const data = await tx
            .select({
              id: pqLists.id,
              name: pqLists.name,
              type: pqLists.type,
              projectId: pqLists.projectId,
              validTo: pqLists.validTo,
              isDeleted: pqLists.isDeleted,
              createdAt: pqLists.createdAt,
              updatedAt: pqLists.updatedAt,
              createdBy: users.name,
              projectCode: projects.code,
              projectName: projects.name,
              updatedBy: users.name,
            })
            .from(pqLists)
            .leftJoin(projects, eq(pqLists.projectId, projects.id))
            .leftJoin(users, eq(pqLists.createdBy, users.id))
            .where(finalWhere)
            .orderBy(...orderBy)
            .offset(offset)
            .limit(input.perPage);

          const countRes = await tx
            .select({ count: count() })
            .from(pqLists)
            .where(finalWhere);

          // 각 PQ 리스트의 항목 수 조회
          const dataWithCriteriaCount = await Promise.all(
            data.map(async (item) => {
              const criteriaCount = await getPqListCriteriaCount(item.id);
              return {
                ...item,
                criteriaCount
              };
            })
          );
          

          return { data: dataWithCriteriaCount, total: countRes[0]?.count ?? 0 };
        });

        const pageCount = Math.ceil(total / input.perPage);
        return { data, pageCount };
      } catch (err) {
        console.error("Error in getPQLists:", err);
        return { data: [], pageCount: 0 };
      }
    },
    [JSON.stringify(input)],
    { revalidate: 3600, tags: ["pq-lists"] }
  )();
}

export async function togglePQListsAction(ids: number[], newIsDeleted: boolean) {
  try {
    if (ids.length === 0) {
      return { success: false, message: "선택한 항목이 없습니다" };
    }
    const session = await getServerSession(authOptions);
    const userId = session?.user?.id ? Number(session.user.id) : null;
    const now = new Date();
    const updated = await db
      .update(pqLists)
      .set({ isDeleted: newIsDeleted, updatedAt: now, updatedBy: userId })
      .where(inArray(pqLists.id, ids))
      .returning();
    revalidateTag("pq-lists");
    return {
      success: true,
      data: updated,
      message: `${updated.length}개의 PQ 목록이 ${newIsDeleted ? "비활성화" : "활성화"}되었습니다`
    };
  } catch (error) {
    console.error("Error toggling PQ lists:", error);
    return {
      success: false,
      message: "PQ 목록 상태 변경에 실패했습니다"
    };
  }
}

export async function createPQListAction(input: CreatePqListInput) {
  try {
    const validated = createPqListSchema.parse(input);
    const session = await getServerSession(authOptions);
    const userId = session?.user?.id;
    // const userName = session?.user?.name || "Unknown";    
    
    // General PQ인 경우 중복 체크
    if (validated.type === "GENERAL") {
      const existingGeneralPQ = await db
        .select()
        .from(pqLists)
        .where(
          and(
            eq(pqLists.type, "GENERAL"),
            eq(pqLists.isDeleted, false)
          )
        )
        .limit(1);

      if (existingGeneralPQ.length > 0) {
        return {
          success: false,
          error: "General PQ 목록은 하나만 생성할 수 있습니다"
        };
      }
    }

    // 프로젝트 PQ인 경우 중복 체크
    if (validated.type === "PROJECT" && validated.projectId) {
      const existingPQ = await db
        .select()
        .from(pqLists)
        .where(
          and(
            eq(pqLists.projectId, validated.projectId),
            eq(pqLists.type, "PROJECT"),
            eq(pqLists.isDeleted, false)
          )
        )
        .limit(1);
        
      if (existingPQ.length > 0) {
        return {
          success: false,
          error: "해당 프로젝트에 대한 PQ가 이미 존재합니다"
        };
      }
    }
    return await db.transaction(async (tx) => {
      const now = new Date();
      const [newPqList] = await tx
        .insert(pqLists)
        .values({
          ...validated,
          isDeleted: false,
          createdAt: now,
          updatedAt: now,
          createdBy: userId,
          updatedBy: userId,
        })
        .returning();

      // 프로젝트 PQ인 경우 General PQ 항목들을 자동으로 복사
      let copiedCriteriaCount = 0;
      if (validated.type === "PROJECT") {
        // General PQ 목록 찾기
        const generalPqList = await tx
          .select()
          .from(pqLists)
          .where(
            and(
              eq(pqLists.type, "GENERAL"),
              eq(pqLists.isDeleted, false)
            )
          )
          .limit(1)
          .then(rows => rows[0]);

        if (generalPqList) {
          // General PQ의 항목들 조회
          const generalCriterias = await tx
            .select()
            .from(pqCriterias)
            .where(eq(pqCriterias.pqListId, generalPqList.id));

          if (generalCriterias.length > 0) {
            // 새로운 프로젝트 PQ에 항목들 복사
            const newCriterias = generalCriterias.map(criteria => ({
               code: criteria.code,
               checkPoint: criteria.checkPoint,
               description: criteria.description,
               remarks: criteria.remarks,
               groupName: criteria.groupName,
               subGroupName: criteria.subGroupName,
               pqListId: newPqList.id,
               inputFormat: criteria.inputFormat,
               createdAt: now,
               updatedAt: now,
             }));

            await tx.insert(pqCriterias).values(newCriterias);
            copiedCriteriaCount = newCriterias.length;
          }
        }
      }

      revalidateTag("pq-lists");
      revalidateTag("pq-criterias");
      return { 
        success: true, 
        data: newPqList,
        copiedCriteriaCount 
      };
    });
  } catch (error) {
    console.error("Error creating PQ list:", error);
    
    if (error instanceof z.ZodError) {
      return {
        success: false,
        error: "유효성 검사 실패",
        details: error.errors
      };
    }
    
    return {
      success: false,
      error: "PQ 목록 생성에 실패했습니다"
    };
  }
}
export async function deletePQListsAction(ids: number[]) {
  try {
    if (ids.length === 0) {
      return {
        success: false,
        message: "삭제할 항목을 선택해주세요"
      };
    }
    console.log("ids", ids)
    console.log("pqLists", pqLists)
    const now = new Date();
    const updated = await db
      .update(pqLists)
      .set({ isDeleted: true, updatedAt: now })
      .where(inArray(pqLists.id, ids))
      .returning();

    revalidateTag("pq-lists");
    return {
      success: true,
      data: updated,
      message: `${updated.length}개의 PQ 목록이 비활성화되었습니다`
    };
  } catch (error) {
    console.error("Error deleting PQ lists:", error);
    return {
      success: false,
      message: "PQ 목록 삭제에 실패했습니다"
    };
  }
}

export async function getPqListById(id: number) {
  try {
    const pqList = await db
      .select()
      .from(pqLists)
      .where(and(
        eq(pqLists.id, id),
        eq(pqLists.isDeleted, false)
      ))
      .limit(1)
      .then(rows => rows[0]);
      
    return pqList || null;
  } catch (error) {
    console.error("Error fetching PQ list by ID:", error);
    return null;
  }
}

export async function getPqListCriteriaCount(listId: number) {
  try {
    const result = await db
      .select({ count: count() })
      .from(pqCriterias)
      .where(eq(pqCriterias.pqListId, listId));

    return result[0]?.count || 0;
  } catch (error) {
    console.error("Error getting PQ list criteria count:", error);
    return 0;
  }
}



export async function copyPQListAction(input: CopyPqListInput) {
  try {
    const validated = copyPqListSchema.parse(input);
    const session = await getServerSession(authOptions);
    const userId = session?.user?.id;
    return await db.transaction(async (tx) => {
      // 1. 원본 PQ 목록 조회
      const sourcePqList = await tx
        .select()
        .from(pqLists)
        .where(eq(pqLists.id, validated.sourcePqListId))
        .limit(1)
        .then(rows => rows[0]);

      if (!sourcePqList) {
        return {
          success: false,
          error: "복사할 PQ 목록을 찾을 수 없습니다"
        };
      }

      // 2. 대상 프로젝트에 이미 PQ가 존재하는지 확인
      const existingProjectPQ = await tx
        .select()
        .from(pqLists)
        .where(
          and(
            eq(pqLists.projectId, validated.targetProjectId),
            eq(pqLists.type, "PROJECT"),
            eq(pqLists.isDeleted, false)
          )
        )
        .limit(1);

      if (existingProjectPQ.length > 0) {
        return {
          success: false,
          error: "해당 프로젝트에 대한 PQ가 이미 존재합니다"
        };
      }

      // 3. 새 PQ 목록 생성
      const now = new Date();
      const newName = validated.newName || `${sourcePqList.name} (복사본)`;

      const [newPqList] = await tx
        .insert(pqLists)
        .values({
          name: newName || sourcePqList.name,
          type: sourcePqList.type,
          projectId: validated.targetProjectId,
          isDeleted: false,
          createdAt: now,
          updatedAt: now,
          createdBy: userId,
          updatedBy: userId,
          validTo: validated.validTo,
        })
        .returning();

      // 4. 원본 PQ 항목들 조회 및 복사
      const sourceCriterias = await tx
        .select()
        .from(pqCriterias)
        .where(eq(pqCriterias.pqListId, validated.sourcePqListId));

      if (sourceCriterias.length > 0) {
        const newCriterias = sourceCriterias.map(criteria => ({
          code: criteria.code,
          checkPoint: criteria.checkPoint,
          description: criteria.description,
          remarks: criteria.remarks,
          groupName: criteria.groupName,
          subGroupName: criteria.subGroupName,
          pqListId: newPqList.id,
          inputFormat: criteria.inputFormat,
          createdAt: now,
          updatedAt: now,
          createdBy: userId,
          updatedBy: userId,
        }));

        await tx.insert(pqCriterias).values(newCriterias);
      }

      revalidateTag("pq-lists");
      return {
        success: true,
        data: newPqList,
        copiedCriteriaCount: sourceCriterias.length
      };
    });
  } catch (error) {
    console.error("Error copying PQ list:", error);

    if (error instanceof z.ZodError) {
      return {
        success: false,
        error: "유효성 검사 실패",
        details: error.errors
      };
    }

    return {
      success: false,
      error: error instanceof Error ? error.message : "PQ 목록 복사에 실패했습니다"
    };
  }
}
export type Project = {
  id: number;
  projectCode: string;
  projectName: string;
  pjtType: string;
}
// -----------------------------------------------------------------------------
export async function getProjects() {
  try {
    const projectList = await db.transaction(async (tx) => {
      const results = await tx
        .select({
          id: projects.id,
          code: projects.code,
          name: projects.name,
          type: projects.type,
          createdAt: projects.createdAt,
          updatedAt: projects.updatedAt,
        })
        .from(projects)
        .orderBy(projects.code);

      return results;
    });

    return projectList;
  } catch (error) {
    console.error("프로젝트 목록 가져오기 실패:", error);
    return [];
  }
}

// PQ 리스트에 등재된 프로젝트만 가져오는 함수
export async function getProjectsWithPQList() {
  try {
    const projectList = await db.transaction(async (tx) => {
      const results = await tx
        .select({
          id: projects.id,
          projectCode: projects.code,
          projectName: projects.name,
          pjtType: projects.type,
          type: projects.type,
          createdAt: projects.createdAt,
          updatedAt: projects.updatedAt,
        })
        .from(projects)
        .innerJoin(pqLists, eq(projects.id, pqLists.projectId))
        .where(
          and(
            eq(pqLists.type, "PROJECT"),
            eq(pqLists.isDeleted, false)
          )
        )
        .orderBy(projects.code);

      return results;
    });

    return projectList;
  } catch (error) {
    console.error("PQ 리스트 등재 프로젝트 목록 가져오기 실패:", error);
    return [];
  }
}
// -----------------------------------------------------------------------------
// PQ Criteria CRUD 액션 - 개선된 버전
// -----------------------------------------------------------------------------

// PQ 항목 생성 (특정 PQ 목록에 속함)
export async function createPqCriteria(
  pqListId: number,
  input: {
    code: string;
    checkPoint: string;
    groupName: string;
    subGroupName?: string;
    description?: string;
    remarks?: string;
    inputFormat?: string;
  }
) {
  try {
    const now = new Date();
    const [newCriteria] = await db
      .insert(pqCriterias)
      .values({
        code: input.code,
        checkPoint: input.checkPoint,
        description: input.description || null,
        remarks: input.remarks || null,
        groupName: input.groupName,
        subGroupName: input.subGroupName || null,
        pqListId: pqListId,
        inputFormat: input.inputFormat || "TEXT",
        createdAt: now,
        updatedAt: now,
      })
      .returning();

    revalidateTag("pq-criterias");
    return {
      success: true,
      data: newCriteria,
      message: "PQ 항목이 성공적으로 생성되었습니다"
    };
  } catch (error) {
    console.error("Error creating PQ criteria:", error);
    return {
      success: false,
      message: "PQ 항목 생성에 실패했습니다"
    };
  }
}

// PQ 항목 수정
export async function updatePqCriteria(
  id: number,
  input: {
    code: string;
    checkPoint: string;
    groupName: string;
    subGroupName?: string;
    description?: string;
    remarks?: string;
    inputFormat?: string;
  }
) {
  try {
    const now = new Date();
    const [updatedCriteria] = await db
      .update(pqCriterias)
      .set({
        code: input.code,
        checkPoint: input.checkPoint,
        description: input.description || null,
        remarks: input.remarks || null,
        groupName: input.groupName,
        subGroupName: input.subGroupName || null,
        inputFormat: input.inputFormat || "TEXT",
        updatedAt: now,
      })
      .where(eq(pqCriterias.id, id))
      .returning();

    if (!updatedCriteria) {
      return {
        success: false,
        message: "수정할 PQ 항목을 찾을 수 없습니다"
      };
    }

    revalidateTag("pq-criterias");
    return {
      success: true,
      data: updatedCriteria,
      message: "PQ 항목이 성공적으로 수정되었습니다"
    };
  } catch (error) {
    console.error("Error updating PQ criteria:", error);
    return {
      success: false,
      message: "PQ 항목 수정에 실패했습니다"
    };
  }
}

// PQ 항목 삭제
export async function deletePqCriterias(ids: number[]) {
  try {
    if (ids.length === 0) {
      return {
        success: false,
        message: "삭제할 항목을 선택해주세요"
      };
    }

    const deletedCriterias = await db
      .delete(pqCriterias)
      .where(inArray(pqCriterias.id, ids))
      .returning();

    revalidateTag("pq-criterias");
    return {
      success: true,
      data: deletedCriterias,
      message: `${deletedCriterias.length}개의 PQ 항목이 삭제되었습니다`
    };
  } catch (error) {
    console.error("Error deleting PQ criterias:", error);
    return {
      success: false,
      message: "PQ 항목 삭제에 실패했습니다"
    };
  }
}

/**
 * PQ 제출 삭제 함수 (REQUESTED 상태일 때만 삭제 가능)
 */
export async function deletePQSubmissionAction(pqSubmissionId: number) {
  try {
    // PQ 제출 정보 조회
    const submission = await db
      .select()
      .from(vendorPQSubmissions)
      .where(eq(vendorPQSubmissions.id, pqSubmissionId))
      .limit(1);

    if (submission.length === 0) {
      return { success: false, error: "PQ 제출을 찾을 수 없습니다." };
    }

    const pqSubmission = submission[0];

    // REQUESTED 상태가 아니면 삭제 불가
    if (pqSubmission.status !== "REQUESTED") {
      return { success: false, error: "요청됨 상태가 아닌 PQ는 삭제할 수 없습니다." };
    }

    // 트랜잭션으로 관련 데이터 모두 삭제
    await db.transaction(async (tx) => {
      // 1. PQ 답변 삭제 (vendorId와 projectId로 식별)
      await tx
        .delete(vendorPqCriteriaAnswers)
        .where(
          and(
            eq(vendorPqCriteriaAnswers.vendorId, pqSubmission.vendorId),
            pqSubmission.projectId 
              ? eq(vendorPqCriteriaAnswers.projectId, pqSubmission.projectId)
              : isNull(vendorPqCriteriaAnswers.projectId)
          )
        );

      // 2. 첨부파일 삭제 (vendorCriteriaAnswerId로 연결)
      const answerIds = await tx
        .select({ id: vendorPqCriteriaAnswers.id })
        .from(vendorPqCriteriaAnswers)
        .where(
          and(
            eq(vendorPqCriteriaAnswers.vendorId, pqSubmission.vendorId),
            pqSubmission.projectId 
              ? eq(vendorPqCriteriaAnswers.projectId, pqSubmission.projectId)
              : isNull(vendorPqCriteriaAnswers.projectId)
          )
        );

      if (answerIds.length > 0) {
        await tx
          .delete(vendorCriteriaAttachments)
          .where(inArray(vendorCriteriaAttachments.vendorCriteriaAnswerId, answerIds.map(a => a.id)));
      }

      // 3. PQ 제출 삭제
      await tx
        .delete(vendorPQSubmissions)
        .where(eq(vendorPQSubmissions.id, pqSubmissionId));
    });

    return { success: true };
  } catch (error) {
    console.error("deletePQSubmissionAction error:", error);
    return { success: false, error: String(error) };
  }
}

// PQ 목록별 항목 조회 (특정 pqListId에 속한 PQ 항목들)
export async function getPQsByListId(pqListId: number, input: GetPQSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage;

        // 검색 조건
        let globalWhere;
        if (input.search) {
          const s = `%${input.search}%`;
          globalWhere = or(
            ilike(pqCriterias.code, s),
            ilike(pqCriterias.groupName, s),
            ilike(pqCriterias.subGroupName, s),
            ilike(pqCriterias.remarks, s),
            ilike(pqCriterias.checkPoint, s),
            ilike(pqCriterias.description, s)
          );
        }

        // 고급 필터
        const advancedWhere = input.filters
          ? filterColumns({ table: pqCriterias, filters: input.filters, joinOperator: input.joinOperator })
          : undefined;

        // 최종 WHERE 조건 (pqListId 조건 추가)
        const finalWhere = and(
          eq(pqCriterias.pqListId, pqListId), // 특정 PQ 목록에 속한 항목들만
          advancedWhere,
          globalWhere
        );

        // 정렬
        const orderBy = input.sort.length
          ? input.sort.map((s) => (s.desc ? desc(pqCriterias[s.id]) : asc(pqCriterias[s.id])))
          : [asc(pqCriterias.createdAt)];

        const { data, total } = await db.transaction(async (tx) => {
          // 데이터 조회
          const data = await tx
                       .select({
             id: pqCriterias.id,
             code: pqCriterias.code,
             checkPoint: pqCriterias.checkPoint,
             description: pqCriterias.description,
             remarks: pqCriterias.remarks,
             groupName: pqCriterias.groupName,
             subGroupName: pqCriterias.subGroupName,
             pqListId: pqCriterias.pqListId,
             inputFormat: pqCriterias.inputFormat,
     
             createdAt: pqCriterias.createdAt,
             updatedAt: pqCriterias.updatedAt,
           })
            .from(pqCriterias)
            .where(finalWhere)
            .orderBy(...orderBy)
            .offset(offset)
            .limit(input.perPage);

          // 카운트 조회
          const countRes = await tx
            .select({ count: count() })
            .from(pqCriterias)
            .where(finalWhere);

          const total = countRes[0]?.count ?? 0;

          return { data, total };
        });

        const pageCount = Math.ceil(total / input.perPage);
        return { data, pageCount };
      } catch (err) {
        console.error("Error in getPQsByListId:", err);
        return { data: [], pageCount: 0 };
      }
    },
    [JSON.stringify(input), pqListId.toString()],
    { revalidate: 3600, tags: ["pq-criterias"] }
  )();
}

// 실사 정보 업데이트 액션 (구매자체평가용)
export async function updateInvestigationDetailsAction(input: {
  investigationId: number;
  confirmedAt?: Date;
  evaluationResult?: "APPROVED" | "SUPPLEMENT" | "REJECTED";
  investigationNotes?: string;
  attachments?: File[];
}) {
  try {
    const updateData: any = {
      updatedAt: new Date(),
    };

    if (input.confirmedAt !== undefined) {
      updateData.confirmedAt = input.confirmedAt;
    }

    if (input.evaluationResult !== undefined) {
      updateData.evaluationResult = input.evaluationResult;
    }

    if (input.investigationNotes !== undefined) {
      updateData.investigationNotes = input.investigationNotes;
    }
    // evaluationResult가 APPROVED라면 investigationStatus를 "COMPLETED"(완료됨)로 변경
    if (input.evaluationResult === "APPROVED") {
      updateData.investigationStatus = "COMPLETED";
    }

    // 트랜잭션으로 실사 정보 업데이트와 첨부파일 저장을 함께 처리
    await db.transaction(async (tx) => {
      // 1. 실사 정보 업데이트
      await tx
        .update(vendorInvestigations)
        .set(updateData)
        .where(eq(vendorInvestigations.id, input.investigationId));

      // 2. 첨부파일 처리
      if (input.attachments && input.attachments.length > 0) {
        for (const file of input.attachments) {
          try {
            console.log(`📁 실사 첨부파일 처리 중: ${file.name} (${file.size} bytes)`);
            
            // saveDRMFile을 사용하여 파일 저장
            const saveResult = await saveDRMFile(
              file,
              decryptWithServerAction,
              `vendor-investigation/${input.investigationId}`,
              "investigation-update"
            );

            if (!saveResult.success) {
              console.error(`❌ 파일 저장 실패: ${file.name}`, saveResult.error);
              throw new Error(`파일 저장 실패: ${file.name} - ${saveResult.error}`);
            }

            console.log(`✅ 파일 저장 완료: ${file.name} -> ${saveResult.fileName}`);

            // 파일 타입 결정
            let attachmentType = "OTHER";
            if (file.type.includes("pdf")) {
              attachmentType = "REPORT";
            } else if (file.type.includes("image")) {
              attachmentType = "PHOTO";
            } else if (
              file.type.includes("word") ||
              file.type.includes("document") ||
              file.name.toLowerCase().includes("report")
            ) {
              attachmentType = "DOCUMENT";
            }

            // DB에 첨부파일 레코드 생성
            await tx.insert(vendorInvestigationAttachments).values({
              investigationId: input.investigationId,
              fileName: saveResult.fileName!,
              originalFileName: file.name,
              filePath: saveResult.publicPath!,
              fileSize: file.size,
              mimeType: file.type || 'application/octet-stream',
              attachmentType: attachmentType as "REPORT" | "PHOTO" | "DOCUMENT" | "CERTIFICATE" | "OTHER",
            });
            
          } catch (error) {
            console.error(`❌ 첨부파일 처리 오류: ${file.name}`, error);
            throw new Error(`첨부파일 처리 중 오류가 발생했습니다: ${file.name}`);
          }
        }
      }
    });

    revalidateTag("pq-submissions");
    revalidatePath("/evcp/pq_new");

    return {
      success: true,
      message: "실사 정보가 성공적으로 업데이트되었습니다."
    };

  } catch (error) {
    console.error("실사 정보 업데이트 오류:", error);
    const errorMessage = error instanceof Error ? error.message : "실사 정보 업데이트 중 오류가 발생했습니다.";
    return {
      success: false,
      error: errorMessage
    };
  }
}

// 구매자체평가 첨부파일 조회
export async function getInvestigationAttachments(investigationId: number) {
  try {
    const attachments = await db
      .select({
        id: vendorInvestigationAttachments.id,
        fileName: vendorInvestigationAttachments.fileName,
        originalFileName: vendorInvestigationAttachments.originalFileName,
        filePath: vendorInvestigationAttachments.filePath,
        fileSize: vendorInvestigationAttachments.fileSize,
        mimeType: vendorInvestigationAttachments.mimeType,
        attachmentType: vendorInvestigationAttachments.attachmentType,
        createdAt: vendorInvestigationAttachments.createdAt,
      })
      .from(vendorInvestigationAttachments)
      .where(eq(vendorInvestigationAttachments.investigationId, investigationId))
      .orderBy(desc(vendorInvestigationAttachments.createdAt));

    return {
      success: true,
      attachments,
    };
  } catch (error) {
    console.error("첨부파일 조회 오류:", error);
    return {
      success: false,
      error: "첨부파일 조회 중 오류가 발생했습니다.",
      attachments: [],
    };
  }
}

// 구매자체평가 첨부파일 삭제
export async function deleteInvestigationAttachment(attachmentId: number) {
  try {
    await db
      .delete(vendorInvestigationAttachments)
      .where(eq(vendorInvestigationAttachments.id, attachmentId));

    revalidateTag("pq-submissions");
    revalidatePath("/evcp/pq_new");

    return {
      success: true,
      message: "첨부파일이 성공적으로 삭제되었습니다.",
    };
  } catch (error) {
    console.error("첨부파일 삭제 오류:", error);
    return {
      success: false,
      error: "첨부파일 삭제 중 오류가 발생했습니다.",
    };
  }
}

export async function autoDeactivateExpiredPQLists() {
  try {
    const now = new Date();
    
    // 유효기간이 지난 PQ 리스트들을 비활성화
    const expiredLists = await db
      .update(pqLists)
      .set({ 
        isDeleted: true, 
        updatedAt: now 
      })
      .where(
        and(
          eq(pqLists.isDeleted, false),
          lt(pqLists.validTo, now),
          isNotNull(pqLists.validTo)
        )
      )
      .returning();

    console.log(`[PQ Auto Deactivation] ${expiredLists.length}개의 만료된 PQ 리스트가 비활성화되었습니다.`);
    
    if (expiredLists.length > 0) {
      revalidateTag("pq-lists");
    }

    return {
      success: true,
      deactivatedCount: expiredLists.length,
      message: `${expiredLists.length}개의 만료된 PQ 리스트가 비활성화되었습니다.`
    };
  } catch (error) {
    console.error("Error auto-deactivating expired PQ lists:", error);
    return {
      success: false,
      message: "만료된 PQ 리스트 자동 비활성화에 실패했습니다."
    };
  }
}

// SHI 참석자 총 인원수 계산 함수

export async function getTotalShiAttendees(shiAttendees: Record<string, unknown> | null): Promise<number> {
  if (!shiAttendees) return 0
  
  let total = 0
  Object.entries(shiAttendees).forEach(([key, value]) => {
    if (value && typeof value === 'object' && 'checked' in value && 'count' in value) {
      const attendee = value as { checked: boolean; count: number }
      if (attendee.checked) {
        total += attendee.count
      }
    }
  })
  return total
}