path: root/lib/pq/service.ts

"use server"

import db from "@/db/db"
import { GetPQSchema } from "./validations"
import { unstable_cache } from "@/lib/unstable-cache";
import { filterColumns } from "@/lib/filter-columns";
import { getErrorMessage } from "@/lib/handle-error";
import { asc, desc, ilike, inArray, and, gte, lte, not, or, eq, count} from "drizzle-orm";
import { z } from "zod"
import { revalidateTag, unstable_noStore, revalidatePath} from "next/cache";
import { pqCriterias, vendorCriteriaAttachments, vendorPqCriteriaAnswers, vendorPqReviewLogs } from "@/db/schema/pq"
import { countPqs, selectPqs } from "./repository";
import { sendEmail } from "../mail/sendEmail";
import { vendorAttachments, vendors } from "@/db/schema/vendors";
import path from 'path';
import fs from 'fs/promises';
import { randomUUID } from 'crypto';
import { writeFile, mkdir } from 'fs/promises';
import { GetVendorsSchema } from "../vendors/validations";
import { countVendors, selectVendors } from "../vendors/repository";

/**
 * PQ 목록 조회
 */
export async function getPQs(input: GetPQSchema) {
    return unstable_cache(
        async () => {
            try {
                const offset = (input.page - 1) * input.perPage;

                // advancedTable 모드면 filterColumns()로 where 절 구성
                const advancedWhere = filterColumns({
                    table: pqCriterias,
                    filters: input.filters,
                    joinOperator: input.joinOperator,
                });

                let globalWhere
                if (input.search) {
                    const s = `%${input.search}%`
                    globalWhere = or(ilike(pqCriterias.code, s), ilike(pqCriterias.groupName, s), ilike(pqCriterias.remarks, s), ilike(pqCriterias.checkPoint, s), ilike(pqCriterias.description, s)
                    )
                }

                const finalWhere = and(advancedWhere, globalWhere);
                const orderBy =
                    input.sort.length > 0
                        ? input.sort.map((item) =>
                            item.desc ? desc(pqCriterias[item.id]) : asc(pqCriterias[item.id])
                        )
                        : [asc(pqCriterias.createdAt)];

                const { data, total } = await db.transaction(async (tx) => {
                    const data = await selectPqs(tx, {
                        where: finalWhere,
                        orderBy,
                        offset,
                        limit: input.perPage,
                    });
                    const total = await countPqs(tx, finalWhere);
                    return { data, total };
                });

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


                return { data, pageCount };
            } catch (err) {
                return { data: [], pageCount: 0 };
            }
        },
        [JSON.stringify(input)],
        {
            revalidate: 3600,
            tags: [`pq`],
        }
    )();
}

// PQ 생성을 위한 입력 스키마 정의
const createPqSchema = z.object({
    code: z.string().min(1, "Code is required"),
    checkPoint: z.string().min(1, "Check point is required"),
    description: z.string().optional(),
    remarks: z.string().optional(),
    groupName: z.string().optional()
});

export type CreatePqInputType = z.infer<typeof createPqSchema>;

/**
 * PQ 기준 생성
 */
export async function createPq(input: CreatePqInputType) {
  try {
    // 입력 유효성 검증
    const validatedData = createPqSchema.parse(input);

    // 트랜잭션 사용하여 PQ 기준 생성
    return await db.transaction(async (tx) => {
      // PQ 기준 생성
      const [newPqCriteria] = await tx
        .insert(pqCriterias)
        .values({
          code: validatedData.code,
          checkPoint: validatedData.checkPoint,
          description: validatedData.description || null,
          remarks: validatedData.remarks || null,
          groupName: validatedData.groupName || null,
        })
        .returning({ id: pqCriterias.id });

      // 성공 결과 반환
      return { 
        success: true, 
        pqId: newPqCriteria.id,
        message: "PQ criteria created successfully" 
      };
    });
  } catch (error) {
    console.error("Error creating PQ criteria:", error);
    
    // Zod 유효성 검사 에러 처리
    if (error instanceof z.ZodError) {
      return { 
        success: false, 
        message: "Validation failed", 
        errors: error.errors 
      };
    }
    
    // 기타 에러 처리
    return { 
      success: false, 
      message: "Failed to create PQ criteria" 
    };
  }
}

// PQ 캐시 무효화 함수
export async function invalidatePqCache() {
  revalidatePath(`/evcp/pq-criteria`);
  revalidateTag(`pq`);
}

// PQ 삭제를 위한 스키마 정의
const removePqsSchema = z.object({
  ids: z.array(z.number()).min(1, "At least one PQ ID is required")
});

export type RemovePqsInputType = z.infer<typeof removePqsSchema>;

/**
 * PQ 기준 삭제
 */
export async function removePqs(input: RemovePqsInputType) {
  try {
    // 입력 유효성 검증
    const validatedData = removePqsSchema.parse(input);
    
    // 트랜잭션 사용하여 PQ 기준 삭제
    await db.transaction(async (tx) => {
      // PQ 기준 테이블에서 삭제
      await tx
        .delete(pqCriterias)
        .where(inArray(pqCriterias.id, validatedData.ids));
    });
    
    // 캐시 무효화
    await invalidatePqCache();

    return { success: true };
  } catch (error) {
    console.error("Error removing PQ criteria:", error);
    
    // Zod 유효성 검사 에러 처리
    if (error instanceof z.ZodError) {
      return { 
        success: false, 
        error: "Validation failed: " + error.errors.map(e => e.message).join(', ')
      };
    }
    
    // 기타 에러 처리
    return { 
      success: false, 
      error: "Failed to remove PQ criteria"
    };
  }
}

// PQ 수정을 위한 스키마 정의
const modifyPqSchema = z.object({
  id: z.number().positive("ID is required"),
  code: z.string().min(1, "Code is required"),
  checkPoint: z.string().min(1, "Check point is required"),
  groupName: z.string().min(1, "Group is required"),
  description: z.string().optional(),
  remarks: z.string().optional()
});

export type ModifyPqInputType = z.infer<typeof modifyPqSchema>;


export async function modifyPq(input: ModifyPqInputType) {
  try {
    // 입력 유효성 검증
    const validatedData = modifyPqSchema.parse(input);

    // 트랜잭션 사용하여 PQ 기준 수정
    return await db.transaction(async (tx) => {
      // PQ 기준 수정
      await tx
        .update(pqCriterias)
        .set({
          code: validatedData.code,
          checkPoint: validatedData.checkPoint,
          description: validatedData.description || null,
          remarks: validatedData.remarks || null,
          groupName: validatedData.groupName,
          updatedAt: new Date(),
        })
        .where(eq(pqCriterias.id, validatedData.id));

      // 성공 결과 반환
      return { 
        success: true, 
        message: "PQ criteria updated successfully" 
      };
    });
  } catch (error) {
    console.error("Error updating PQ criteria:", error);
    
    // Zod 유효성 검사 에러 처리
    if (error instanceof z.ZodError) {
      return { 
        success: false, 
        error: "Validation failed: " + error.errors.map(e => e.message).join(', ')
      };
    }
    
    // 기타 에러 처리
    return { 
      success: false, 
      error: "Failed to update PQ criteria" 
    };
  } finally {
    // 캐시 무효화
    revalidatePath(`/partners/pq`);
    revalidateTag(`pq`);
  }
}

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

export interface PQItem {
  answerId: number | null; // null도 허용하도록 변경
  criteriaId: number
  code: string
  checkPoint: string
  description: string | null
  answer: string // or null
  attachments: PQAttachment[]
}

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

 
export async function getPQDataByVendorId(vendorId: number): Promise<PQGroupData[]> {
  // 1) Query: pqCriterias
  //    LEFT JOIN vendorPqCriteriaAnswers (to get `answer`)
  //    LEFT JOIN vendorCriteriaAttachments (to get each attachment row)
  const rows = await db
    .select({
      criteriaId: pqCriterias.id,
      groupName: pqCriterias.groupName,
      code: pqCriterias.code,
      checkPoint: pqCriterias.checkPoint,
      description: pqCriterias.description,

      // From vendorPqCriteriaAnswers
      answer: vendorPqCriteriaAnswers.answer,     // can be null if no row exists
      answerId: vendorPqCriteriaAnswers.id,       // internal PK if needed

      // From vendorCriteriaAttachments
      attachId: vendorCriteriaAttachments.id,
      fileName: vendorCriteriaAttachments.fileName,
      filePath: vendorCriteriaAttachments.filePath,
      fileSize: vendorCriteriaAttachments.fileSize,
    })
    .from(pqCriterias)
    .leftJoin(
      vendorPqCriteriaAnswers,
      and(
        eq(pqCriterias.id, vendorPqCriteriaAnswers.criteriaId),
        eq(vendorPqCriteriaAnswers.vendorId, vendorId)
      )
    )
    .leftJoin(
      vendorCriteriaAttachments,
      eq(vendorPqCriteriaAnswers.id, vendorCriteriaAttachments.vendorCriteriaAnswerId)
    )
    .orderBy(pqCriterias.groupName, pqCriterias.code)

  // 2) Group by groupName => each group has a map of criteriaId => PQItem
  //    so we can gather attachments properly.
  const groupMap = new Map<string, Record<number, PQItem>>()

  for (const row of rows) {
    const g = row.groupName || "Others"

    // Ensure we have an object for this group
    if (!groupMap.has(g)) {
      groupMap.set(g, {})
    }

    const groupItems = groupMap.get(g)!
    // If we haven't seen this criteriaId yet, create a PQItem
    if (!groupItems[row.criteriaId]) {
      groupItems[row.criteriaId] = {
        answerId: row.answerId,
        criteriaId: row.criteriaId,
        code: row.code,
        checkPoint: row.checkPoint,
        description: row.description,
        answer: row.answer || "",    // if row.answer is null, just empty string
        attachments: [],
      }
    }

    // If there's an attachment row (attachId not null), push it onto `attachments`
    if (row.attachId) {
      groupItems[row.criteriaId].attachments.push({
        attachId: row.attachId,
        fileName: row.fileName || "",
        filePath: row.filePath || "",
        fileSize: row.fileSize || undefined,
      })
    }
  }

  // 3) Convert groupMap into an array of { groupName, items[] }
  const data: PQGroupData[] = []
  for (const [groupName, itemsMap] of groupMap.entries()) {
    // Convert the itemsMap (key=criteriaId => PQItem) into an array
    const items = Object.values(itemsMap)
    data.push({ groupName, items })
  }

  return data
}


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

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

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

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

  try {
    for (const ans of answers) {
      // 1) Check if a row already exists for (vendorId, criteriaId)
      const existing = await db
        .select()
        .from(vendorPqCriteriaAnswers)
        .where(
          and(
            eq(vendorPqCriteriaAnswers.vendorId, vendorId),
            eq(vendorPqCriteriaAnswers.criteriaId, ans.criteriaId)
          )
        )

      let answerId: number

      // 2) If it exists, update the row; otherwise insert
      if (existing.length === 0) {
        // Insert new
        const inserted = await db
          .insert(vendorPqCriteriaAnswers)
          .values({
            vendorId,
            criteriaId: ans.criteriaId,
            answer: ans.answer,
            // no attachmentPaths column anymore
          })
          .returning({ id: vendorPqCriteriaAnswers.id })
        
        answerId = inserted[0].id
      } else {
        // Update existing
        answerId = existing[0].id

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

      // 3) Now manage attachments in vendorCriteriaAttachments
      //    We'll do a "diff": remove old ones not in the new list, insert new ones not in DB.

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

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

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

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

      for (const attach of toAdd) {
        await db.insert(vendorCriteriaAttachments).values({
          vendorCriteriaAnswerId: answerId,
          fileName: attach.fileName,   // original filename
          filePath: attach.url,        // random/UUID path on server
          fileSize: attach.size ?? null,
          // fileType if you have it, etc.
        })
      }
    }

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



/**
 * PQ 제출 서버 액션 - 벤더 상태를 PQ_SUBMITTED로 업데이트
 * @param vendorId 벤더 ID
 */
export async function submitPQAction(vendorId: number) {
  unstable_noStore();
  
  try {
    // 1. 모든 PQ 항목에 대한 응답이 있는지 검증
    const pqCriteriaCount = await db
      .select({ count: count() })
      .from(vendorPqCriteriaAnswers)
      .where(eq(vendorPqCriteriaAnswers.vendorId, vendorId));
    
    const totalPqCriteriaCount = pqCriteriaCount[0]?.count || 0;
    
    // 응답 데이터 검증
    if (totalPqCriteriaCount === 0) {
      return { ok: false, error: "No PQ answers found" };
    }
    
    // 2. 벤더 정보 조회
    const vendor = await db
      .select({
        id: vendors.id,
        vendorName: vendors.vendorName,
        email: vendors.email,
        status: vendors.status,
      })
      .from(vendors)
      .where(eq(vendors.id, vendorId))
      .then(rows => rows[0]);
    
    if (!vendor) {
      return { ok: false, error: "Vendor not found" };
    }
    
    // 3. 벤더 상태가 제출 가능한 상태인지 확인
    const allowedStatuses = ["IN_PQ", "PENDING_REVIEW", "IN_REVIEW", "REJECTED", "PQ_FAILED"];
    if (!allowedStatuses.includes(vendor.status)) {
      return { 
        ok: false, 
        error: `Cannot submit PQ in current status: ${vendor.status}` 
      };
    }
    
    // 4. 벤더 상태 업데이트
    await db
      .update(vendors)
      .set({
        status: "PQ_SUBMITTED",
        updatedAt: new Date(),
      })
      .where(eq(vendors.id, vendorId));
    
    // 5. 관리자에게 이메일 알림 발송
    if (process.env.ADMIN_EMAIL) {
      try {
        await sendEmail({
          to: process.env.ADMIN_EMAIL,
          subject: `[eVCP] PQ Submitted: ${vendor.vendorName}`,
          template: "pq-submitted-admin",
          context: {
            vendorName: vendor.vendorName,
            vendorId: vendor.id,
            submittedDate: new Date().toLocaleString(),
            adminUrl: `${process.env.NEXT_PUBLIC_APP_URL}/admin/vendors/${vendorId}/pq`,
          }
        });
      } catch (emailError) {
        console.error("Failed to send admin notification:", emailError);
        // 이메일 실패는 전체 프로세스를 중단하지 않음
      }
    }
    
    // 6. 벤더에게 확인 이메일 발송
    if (vendor.email) {
      try {
        await sendEmail({
          to: vendor.email,
          subject: "[eVCP] PQ Submission Confirmation",
          template: "pq-submitted-vendor",
          context: {
            vendorName: vendor.vendorName,
            submittedDate: new Date().toLocaleString(),
            portalUrl: `${process.env.NEXT_PUBLIC_APP_URL}/dashboard`,
          }
        });
      } catch (emailError) {
        console.error("Failed to send vendor confirmation:", emailError);
        // 이메일 실패는 전체 프로세스를 중단하지 않음
      }
    }
    
    // 7. 캐시 무효화
    revalidateTag("vendors");
    revalidateTag("vendor-status-counts");
    
    return { ok: true };
  } catch (error) {
    console.error("PQ submit error:", error);
    return { ok: false, error: getErrorMessage(error) };
  }
}

/**
 * 향상된 파일 업로드 서버 액션
 * - 직접 파일 처리 (file 객체로 받음)
 * - 디렉토리 자동 생성
 * - 중복 방지를 위한 UUID 적용
 */
export async function uploadFileAction(file: File) {
  unstable_noStore();
  
  try {
    // 파일 유효성 검사
    if (!file || file.size === 0) {
      throw new Error("Invalid file");
    }
    
    const maxSize = 6e8;
    if (file.size > maxSize) {
      throw new Error(`File size exceeds limit (${Math.round(maxSize / 1024 / 1024)}MB)`);
    }
    
    // 파일 확장자 가져오기
    const originalFilename = file.name;
    const fileExt = path.extname(originalFilename);
    const fileNameWithoutExt = path.basename(originalFilename, fileExt);
    
    // 저장 경로 설정
    const uploadDir = process.env.UPLOAD_DIR 
    ? process.env.UPLOAD_DIR 
    : path.join(process.cwd(), "public", "uploads")
    const datePrefix = new Date().toISOString().slice(0, 10).replace(/-/g, ''); // YYYYMMDD
    const targetDir = path.join(uploadDir, 'pq', datePrefix);
    
    // UUID로 고유 파일명 생성
    const uuid = randomUUID();
    const sanitizedFilename = fileNameWithoutExt
      .replace(/[^a-zA-Z0-9-_]/g, '_') // 안전한 문자만 허용
      .slice(0, 50); // 이름 길이 제한
    
    const filename = `${sanitizedFilename}-${uuid}${fileExt}`;
    const filePath = path.join(targetDir, filename);
    const relativeFilePath = path.join('pq', datePrefix, filename);
    
    // 디렉토리 생성 (없는 경우)
    try {
      await mkdir(targetDir, { recursive: true });
    } catch (err) {
      console.error("Error creating directory:", err);
      throw new Error("Failed to create upload directory");
    }
    
    // 파일 저장
    const buffer = await file.arrayBuffer();
    await writeFile(filePath, Buffer.from(buffer));
    
    // 상대 경로를 반환 (DB에 저장하기 용이함)
    const publicUrl = `/uploads/${relativeFilePath.replace(/\\/g, '/')}`;
    
    return {
      fileName: originalFilename,
      url: publicUrl,
      size: file.size,
    };
  } catch (error) {
    console.error("File upload error:", error);
    throw new Error(`Upload failed: ${getErrorMessage(error)}`);
  }
}

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

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

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

        // 2) 글로벌 검색
        let globalWhere;
        if (input.search) {
          const s = `%${input.search}%`;
          globalWhere = or(
            ilike(vendors.vendorName, s),
            ilike(vendors.vendorCode, s),
            ilike(vendors.email, s),
            ilike(vendors.status, s)
          );
        }

        // 최종 where 결합
        const finalWhere = and(advancedWhere, globalWhere, eq(vendors.status ,"PQ_SUBMITTED"));

        // 간단 검색 (advancedTable=false) 시 예시
        const simpleWhere = and(
          input.vendorName
            ? ilike(vendors.vendorName, `%${input.vendorName}%`)
            : undefined,
          input.status ? ilike(vendors.status, input.status) : undefined,
          input.country
            ? ilike(vendors.country, `%${input.country}%`)
            : undefined
        );

        // 실제 사용될 where
        const where = finalWhere;
        
        // 정렬
        const orderBy =
          input.sort.length > 0
            ? input.sort.map((item) =>
                item.desc ? desc(vendors[item.id]) : asc(vendors[item.id])
              )
            : [asc(vendors.createdAt)];

        // 트랜잭션 내에서 데이터 조회
        const { data, total } = await db.transaction(async (tx) => {
          // 1) vendor 목록 조회
          const vendorsData = await selectVendors(tx, {
            where,
            orderBy,
            offset,
            limit: input.perPage,
          });

          // 2) 각 vendor의 attachments 조회
          const vendorsWithAttachments = await Promise.all(
            vendorsData.map(async (vendor) => {
              const attachments = await tx
                .select({
                  id: vendorAttachments.id,
                  fileName: vendorAttachments.fileName,
                  filePath: vendorAttachments.filePath,
                })
                .from(vendorAttachments)
                .where(eq(vendorAttachments.vendorId, vendor.id));

              return {
                ...vendor,
                hasAttachments: attachments.length > 0,
                attachmentsList: attachments,
              };
            })
          );

          // 3) 전체 개수
          const total = await countVendors(tx, where);
          return { data: vendorsWithAttachments, total };
        });

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

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


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

  export async function updateVendorStatusAction(
    vendorId: number,
    newStatus: VendorStatus
  ) {
    try {
      // 1) Update DB
      await db.update(vendors)
        .set({ status: newStatus })
        .where(eq(vendors.id, vendorId))
  
      // 2) Load vendor’s email & name
      const vendor = await db.select().from(vendors).where(eq(vendors.id, vendorId)).then(r => r[0])
      if (!vendor) {
        return { ok: false, error: "Vendor not found" }
      }
  
      // 3) Send email
      await sendEmail({
        to: vendor.email || "",
        subject: `Your PQ Status is now ${newStatus}`,
        template: "vendor-pq-status",  // matches .hbs file
        context: {
          name: vendor.vendorName,
          status: newStatus,
          loginUrl: `${process.env.NEXT_PUBLIC_URL}/partners/pq`,  // etc.
        },
      })
      revalidateTag("vendors")
      revalidateTag("vendors-in-pq")
      return { ok: true }
    } catch (error) {
      console.error("updateVendorStatusAction error:", error)
      return { ok: false, error: String(error) }
    }
  }
// 코멘트 타입 정의
interface ItemComment {
  answerId: number;
  checkPoint: string; // 체크포인트 정보 추가
  code: string;      // 코드 정보 추가
  comment: string;
}

/**
 * PQ 변경 요청 처리 서버 액션
 * 
 * @param vendorId 벤더 ID
 * @param comment 항목별 코멘트 배열 (answerId, checkPoint, code, comment로 구성)
 * @param generalComment 전체 PQ에 대한 일반 코멘트 (선택사항)
 */
export async function requestPqChangesAction({
  vendorId,
  comment,
  generalComment,
}: {
  vendorId: number;
  comment: ItemComment[];
  generalComment?: string;
}) {
  try {
    // 1) 벤더 상태 업데이트
    await db.update(vendors)
      .set({
        status: "IN_PQ", // 변경 요청 상태로 설정
        updatedAt: new Date(),
      })
      .where(eq(vendors.id, vendorId));

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

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

    // 3) 각 항목별 코멘트 저장
    const currentDate = new Date();
    const reviewerId = 1; // 관리자 ID (실제 구현에서는 세션에서 가져옵니다)
    const reviewerName = "AdminUser"; // 실제 구현에서는 세션에서 가져옵니다

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

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

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

    await sendEmail({
      to: vendor.email || "",
      subject: `[IMPORTANT] Your PQ submission requires changes`,
      template: "vendor-pq-comment", // matches .hbs file
      context: {
        name: vendor.vendorName,
        vendorCode: vendor.vendorCode,
        loginUrl: `${process.env.NEXT_PUBLIC_URL}/partners/pq`,
        comments: commentItems,
        generalComment: generalComment || "",
        hasGeneralComment: !!generalComment,
        commentCount: commentItems.length,
      },
    });

    revalidateTag("vendors")
    revalidateTag("vendors-in-pq")

    return { ok: true };
  } catch (error) {
    console.error("requestPqChangesAction error:", error);
    return { ok: false, error: String(error) };
  }
}
interface AddReviewCommentInput {
  answerId: number // vendorPqCriteriaAnswers.id
  comment: string
  reviewerName?: string
}

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

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

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

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

interface GetItemReviewLogsInput {
  answerId: number
}

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