// src/lib/tasks/service.ts
"use server"; // Next.js 서버 액션에서 직접 import하려면 (선택)

import { revalidatePath, revalidateTag, unstable_noStore } from "next/cache";
import db from "@/db/db";

import { filterColumns } from "@/lib/filter-columns";
import { unstable_cache } from "@/lib/unstable-cache";
import { getErrorMessage } from "@/lib/handle-error";

import { GetRfqsSchema, CreateRfqSchema, UpdateRfqSchema, CreateRfqItemSchema, GetMatchedVendorsSchema, UpdateRfqVendorSchema, GetTBESchema, GetCBESchema, createCbeEvaluationSchema } from "./validations";
import { asc, desc, ilike, inArray, and, or, sql, eq, isNull, ne, isNotNull, count } from "drizzle-orm";
import path from "path";
import fs from "fs/promises";
import { randomUUID } from "crypto";
import { writeFile, mkdir } from 'fs/promises'
import { join } from 'path'

import { vendorResponses, vendorResponsesView, Rfq, rfqs, rfqAttachments, rfqItems, rfqComments, rfqEvaluations, vendorRfqView, vendorTbeView, rfqsView, vendorResponseAttachments, vendorTechnicalResponses, cbeEvaluations, vendorCommercialResponses, vendorResponseCBEView, RfqViewWithItems } from "@/db/schema/rfq";
import { countRfqs, deleteRfqById, deleteRfqsByIds, getRfqById, groupByStatus, insertRfq, insertRfqItem, selectRfqs, updateRfq, updateRfqs, updateRfqVendor } from "./repository";
import logger from '@/lib/logger';
import { vendorContacts, vendorPossibleItems, vendors } from "@/db/schema/vendors";
import { sendEmail } from "../mail/sendEmail";
import { biddingProjects, projects } from "@/db/schema/projects";
import * as z from "zod"
import { users } from "@/db/schema/users";
import { headers } from 'next/headers';

// DRM 복호화 관련 유틸 import
import { decryptWithServerAction } from "@/components/drm/drmUtils";

interface InviteVendorsInput {
  rfqId: number
  vendorIds: number[]
}

/* -----------------------------------------------------
   1) 조회 관련 
----------------------------------------------------- */

/**
 * 복잡한 조건으로 Rfq 목록을 조회 (+ pagination) 하고,
 * 총 개수에 따라 pageCount를 계산해서 리턴.
 * Next.js의 unstable_cache를 사용해 일정 시간 캐시.
 */
export async function getRfqs(input: GetRfqsSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage;
        // const advancedTable = input.flags.includes("advancedTable");
        const advancedTable = true;

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


        let globalWhere
        if (input.search) {
          const s = `%${input.search}%`
          globalWhere = or(ilike(rfqsView.rfqCode, s), ilike(rfqsView.projectCode, s)
            , ilike(rfqsView.projectName, s), ilike(rfqsView.dueDate, s), ilike(rfqsView.status, s)
          )
          // 필요시 여러 칼럼 OR조건 (status, priority, etc)
        }

        const whereConditions = [];
        if (advancedWhere) whereConditions.push(advancedWhere);
        if (globalWhere) whereConditions.push(globalWhere);

        // 조건이 있을 때만 and() 사용
        const finalWhere = whereConditions.length > 0
          ? and(...whereConditions)
          : undefined;

        const orderBy =
          input.sort.length > 0
            ? input.sort.map((item) =>
              item.desc ? desc(rfqsView[item.id]) : asc(rfqsView[item.id])
            )
            : [asc(rfqsView.createdAt)];

        // 트랜잭션 내부에서 Repository 호출
        const { data, total } = await db.transaction(async (tx) => {
          const data = await selectRfqs(tx, {
            where: finalWhere,
            orderBy,
            offset,
            limit: input.perPage,
          });

          const total = await countRfqs(tx, finalWhere);
          return { data, total };
        });


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


        return { data, pageCount };
      } catch (err) {
        console.error("getRfqs 에러:", err); // 자세한 에러 로깅

        // 에러 발생 시 디폴트
        return { data: [], pageCount: 0 };
      }
    },
    [JSON.stringify(input)],
    {
      revalidate: 3600,
      tags: [`rfqs`],
    }
  )();
}

/** Status별 개수 */
export async function getRfqStatusCounts() {
  return unstable_cache(
    async () => {
      try {
        const initial: Record<Rfq["status"], number> = {
          DRAFT: 0,
          PUBLISHED: 0,
          EVALUATION: 0,
          AWARDED: 0,
        };

        const result = await db.transaction(async (tx) => {
          const rows = await groupByStatus(tx);
          return rows.reduce<Record<Rfq["status"], number>>((acc, { status, count }) => {
            acc[status] = count;
            return acc;
          }, initial);
        });

        return result;
      } catch {
        return {} as Record<Rfq["status"], number>;
      }
    },
    [`rfq-status-counts`],
    {
      revalidate: 3600,
      tags: [`rfqs`],
    }
  )();
}



/* -----------------------------------------------------
   2) 생성(Create) 
----------------------------------------------------- */

/**
 * Rfq 생성 후, (가장 오래된 Rfq 1개) 삭제로
 * 전체 Rfq 개수를 고정
 */
export async function createRfq(input: CreateRfqSchema) {
  unstable_noStore();
  try {
    await db.transaction(async (tx) => {
      await insertRfq(tx, {
        rfqCode: input.rfqCode,
        projectId: input.projectId || null,
        description: input.description,
        dueDate: input.dueDate,
        status: input.status,
        createdBy: input.createdBy,
      });
    });

    revalidateTag("rfqs");

    return { data: null, error: null };
  } catch (err) {
    return { data: null, error: getErrorMessage(err) };
  }
}

/* -----------------------------------------------------
   3) 업데이트
----------------------------------------------------- */

/** 단건 업데이트 */
export async function modifyRfq(input: UpdateRfqSchema & { id: number }) {
  unstable_noStore();
  try {
    await db.transaction(async (tx) => {
      await updateRfq(tx, input.id, {
        rfqCode: input.rfqCode,
        projectId: input.projectId || null,
        dueDate: input.dueDate,
        status: input.status as "DRAFT" | "PUBLISHED" | "EVALUATION" | "AWARDED",
        createdBy: input.createdBy,
      });
    });

    revalidateTag("rfqs");

    return { data: null, error: null };
  } catch (err) {
    return { data: null, error: getErrorMessage(err) };
  }
}

export async function modifyRfqs(input: {
  ids: number[];
  status?: Rfq["status"];
  dueDate?: Date
}) {
  unstable_noStore();
  try {
    await db.transaction(async (tx) => {
      await updateRfqs(tx, input.ids, {
        status: input.status,
        dueDate: input.dueDate,
      });
    });

    revalidateTag("rfqs");

    return { data: null, error: null };
  } catch (err) {
    return { data: null, error: getErrorMessage(err) };
  }
}


/* -----------------------------------------------------
   4) 삭제
----------------------------------------------------- */

/** 단건 삭제  */
export async function removeRfq(input: { id: number }) {
  unstable_noStore();
  try {
    await db.transaction(async (tx) => {
      // 삭제
      await deleteRfqById(tx, input.id);
      // 바로 새 Rfq 생성
    });

    revalidateTag("rfqs");

    return { data: null, error: null };
  } catch (err) {
    return { data: null, error: getErrorMessage(err) };
  }
}

/** 복수 삭제  */
export async function removeRfqs(input: { ids: number[] }) {
  unstable_noStore();
  try {
    await db.transaction(async (tx) => {
      // 삭제
      await deleteRfqsByIds(tx, input.ids);
    });

    revalidateTag("rfqs");

    return { data: null, error: null };
  } catch (err) {
    return { data: null, error: getErrorMessage(err) };
  }
}


/**
 * RFQ 아이템 삭제 함수
 */
export async function deleteRfqItem(input: { id: number, rfqId: number }) {
  unstable_noStore(); // Next.js 서버 액션 캐싱 방지

  try {
    // 삭제 작업 수행
    await db
      .delete(rfqItems)
      .where(
        and(
          eq(rfqItems.id, input.id),
          eq(rfqItems.rfqId, input.rfqId)
        )
      );

    console.log(`Deleted RFQ item: ${input.id} for RFQ ${input.rfqId}`);

    // 캐시 무효화
    revalidateTag("rfqs");
    revalidateTag(`rfq-${input.rfqId}`);

    return { data: null, error: null };
  } catch (err) {
    console.error("Error in deleteRfqItem:", err);
    return { data: null, error: getErrorMessage(err) };
  }
}

// createRfqItem 함수 수정 (id 파라미터 추가)
export async function createRfqItem(input: CreateRfqItemSchema & { id?: number }) {
  unstable_noStore();

  try {
    // DB 트랜잭션
    await db.transaction(async (tx) => {
      // id가 전달되었으면 해당 id로 업데이트, 그렇지 않으면 기존 로직대로 진행
      if (input.id) {
        // 기존 아이템 업데이트
        await tx
          .update(rfqItems)
          .set({
            description: input.description ?? null,
            quantity: input.quantity ?? 1,
            uom: input.uom ?? "",
            updatedAt: new Date(),
          })
          .where(eq(rfqItems.id, input.id));

        console.log(`Updated RFQ item with id: ${input.id}`);
      } else {
        // 기존 로직: 같은 itemCode로 이미 존재하는지 확인 후 업데이트/생성
        const existingItems = await tx
          .select()
          .from(rfqItems)
          .where(
            and(
              eq(rfqItems.rfqId, input.rfqId),
              eq(rfqItems.itemCode, input.itemCode)
            )
          );

        if (existingItems.length > 0) {
          // 이미 존재하는 경우 업데이트
          const existingItem = existingItems[0];
          await tx
            .update(rfqItems)
            .set({
              description: input.description ?? null,
              quantity: input.quantity ?? 1,
              uom: input.uom ?? "",
              updatedAt: new Date(),
            })
            .where(eq(rfqItems.id, existingItem.id));

          console.log(`Updated existing RFQ item: ${existingItem.id} for RFQ ${input.rfqId}, Item ${input.itemCode}`);
        } else {
          // 존재하지 않는 경우 새로 생성
          await insertRfqItem(tx, {
            rfqId: input.rfqId,
            itemCode: input.itemCode,
            description: input.description ?? null,
            quantity: input.quantity ?? 1,
            uom: input.uom ?? "",
          });

          console.log(`Created new RFQ item for RFQ ${input.rfqId}, Item ${input.itemCode}`);
        }
      }
    });

    // 캐시 무효화
    revalidateTag("rfqs");
    revalidateTag(`rfq-${input.rfqId}`);

    return { data: null, error: null };
  } catch (err) {
    console.error("Error in createRfqItem:", err);
    return { data: null, error: getErrorMessage(err) };
  }
}
/**
 * 서버 액션: 파일 첨부/삭제 처리
 * @param rfqId                RFQ ID
 * @param removedExistingIds   기존 첨부 중 삭제된 record ID 배열
 * @param newFiles             새로 업로드된 파일 (File[]) - Next.js server action에서
 * @param vendorId             (optional) 업로더가 vendor인지 구분
 */
export async function processRfqAttachments(args: {
  rfqId: number;
  removedExistingIds?: number[];
  newFiles?: File[];
  vendorId?: number | null;
}) {
  const { rfqId, removedExistingIds = [], newFiles = [], vendorId = null } = args;

  try {
    // 1) 삭제된 기존 첨부: DB + 파일시스템에서 제거
    if (removedExistingIds.length > 0) {
      // 1-1) DB에서 filePath 조회
      const rows = await db
        .select({
          id: rfqAttachments.id,
          filePath: rfqAttachments.filePath
        })
        .from(rfqAttachments)
        .where(inArray(rfqAttachments.id, removedExistingIds));

      // 1-2) DB 삭제
      await db
        .delete(rfqAttachments)
        .where(inArray(rfqAttachments.id, removedExistingIds));

      // 1-3) 파일 삭제
      for (const row of rows) {
        // filePath: 예) "/rfq/123/...xyz"
        const absolutePath = path.join(
          process.cwd(),
          "public",
          row.filePath.replace(/^\/+/, "") // 슬래시 제거
        );
        try {
          await fs.unlink(absolutePath);
        } catch (err) {
          console.error("File remove error:", err);
        }
      }
    }

    // 2) 새 파일 업로드
    if (newFiles.length > 0) {
      const rfqDir = path.join("public", "rfq", String(rfqId));
      // 폴더 없으면 생성
      await fs.mkdir(rfqDir, { recursive: true });

      for (const file of newFiles) {
        // 2-1) DRM 복호화 시도 ----------------------------------------------------------------------
        // decryptWithServerAction 함수는 오류 처리 및 원본 반환 로직을 포함하고 있음 (해제 실패시 원본 반환)
        // 이후 코드가 buffer로 작업하므로 buffer로 전환한다.
        const decryptedData = await decryptWithServerAction(file);
        const buffer = Buffer.from(decryptedData);
        // -----------------------------------------------------------------------------------------


        // 2-2) 고유 파일명
        const uniqueName = `${randomUUID()}-${file.name}`;
        // 예) "rfq/123/xxx"
        const relativePath = path.join("rfq", String(rfqId), uniqueName);
        const absolutePath = path.join("public", relativePath);

        // 2-3) 파일 저장
        await fs.writeFile(absolutePath, buffer);

        // 2-4) DB Insert
        await db.insert(rfqAttachments).values({
          rfqId,
          vendorId,
          fileName: file.name,
          filePath: "/" + relativePath.replace(/\\/g, "/"),
          // (Windows 경로 대비)
        });
      }
    }

    const [countRow] = await db
      .select({ cnt: sql<number>`count(*)`.as("cnt") })
      .from(rfqAttachments)
      .where(eq(rfqAttachments.rfqId, rfqId));

    const newCount = countRow?.cnt ?? 0;

    // 3) revalidateTag 등 캐시 무효화
    revalidateTag("rfq-attachments");

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



export async function fetchRfqAttachments(rfqId: number) {
  // DB select
  const rows = await db
    .select()
    .from(rfqAttachments)
    .where(eq(rfqAttachments.rfqId, rfqId))

  // rows: { id, fileName, filePath, createdAt, vendorId, ... }
  // 필요 없는 필드는 omit하거나 transform 가능
  return rows.map((row) => ({
    id: row.id,
    fileName: row.fileName,
    filePath: row.filePath,
    createdAt: row.createdAt, // or string
    vendorId: row.vendorId,
    size: undefined, // size를 DB에 저장하지 않았다면
  }))
}

export async function fetchRfqItems(rfqId: number) {
  // DB select
  const rows = await db
    .select()
    .from(rfqItems)
    .where(eq(rfqItems.rfqId, rfqId))

  // rows: { id, fileName, filePath, createdAt, vendorId, ... }
  // 필요 없는 필드는 omit하거나 transform 가능
  return rows.map((row) => ({
    // id: row.id,
    itemCode: row.itemCode,
    description: row.description,
    quantity: row.quantity,
    uom: row.uom,
  }))
}

export const findRfqById = async (id: number): Promise<RfqViewWithItems | null> => {
  try {
    logger.info({ id }, 'Fetching user by ID');
    const rfq = await getRfqById(id);
    if (!rfq) {
      logger.warn({ id }, 'User not found');
    } else {
      logger.debug({ rfq }, 'User fetched successfully');
    }
    return rfq;
  } catch (error) {
    logger.error({ error }, 'Error fetching user by ID');
    throw new Error('Failed to fetch user');
  }
};

export async function getMatchedVendors(input: GetMatchedVendorsSchema, rfqId: number) {
  return unstable_cache(
    async () => {
      // ─────────────────────────────────────────────────────
      // 1) rfq_items에서 distinct itemCode
      // ─────────────────────────────────────────────────────
      const itemRows = await db
        .select({ code: rfqItems.itemCode })
        .from(rfqItems)
        .where(eq(rfqItems.rfqId, rfqId))
        .groupBy(rfqItems.itemCode)

      const itemCodes = itemRows.map((r) => r.code)
      const itemCount = itemCodes.length
      if (itemCount === 0) {
        return { data: [], pageCount: 0 }
      }

      // ─────────────────────────────────────────────────────
      // 2) vendorPossibleItems에서 모든 itemCodes를 보유한 vendor
      // ─────────────────────────────────────────────────────
      const inList = itemCodes.map((c) => `'${c}'`).join(",")
      const sqlVendorIds = await db.execute(
        sql`
          SELECT vpi.vendor_id AS "vendorId"
          FROM ${vendorPossibleItems} vpi
          WHERE vpi.item_code IN (${sql.raw(inList)})
          GROUP BY vpi.vendor_id
          HAVING COUNT(DISTINCT vpi.item_code) = ${itemCount}
        `
      )
      const vendorIdList = sqlVendorIds.rows.map((row: any) => +row.vendorId)
      if (vendorIdList.length === 0) {
        return { data: [], pageCount: 0 }
      }

      // ─────────────────────────────────────────────────────
      // 3) 필터/검색/정렬
      // ─────────────────────────────────────────────────────
      const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10)
      const limit = input.perPage ?? 10

      // (가) 커스텀 필터
      // 여기서는 "뷰(vendorRfqView)"의 컬럼들에 대해 필터합니다.
      const advancedWhere = filterColumns({
        // 테이블이 아니라 "뷰"를 넘길 수도 있고,
        // 혹은 columns 객체(연결된 모든 컬럼)로 넘겨도 됩니다.
        table: vendorRfqView,
        filters: input.filters ?? [],
        joinOperator: input.joinOperator ?? "and",
      })

      // (나) 글로벌 검색
      let globalWhere
      if (input.search) {
        const s = `%${input.search}%`
        globalWhere = or(
          sql`${vendorRfqView.vendorName} ILIKE ${s}`,
          sql`${vendorRfqView.vendorCode} ILIKE ${s}`,
          sql`${vendorRfqView.email} ILIKE ${s}`
        )
      }

      // (다) 최종 where
      // vendorId가 vendorIdList 내에 있어야 하고,
      // 특정 rfqId(뷰에 담긴 값)도 일치해야 함.
      const finalWhere = and(
        inArray(vendorRfqView.vendorId, vendorIdList),
        // 아래 라인은 rfq에 초대된 벤더만 필터링하는 조건으로 추정되지만
        // rfq 를 진행하기 전에도 벤더를 보여줘야 하므로 주석처리하겠습니다
        // eq(vendorRfqView.rfqId, rfqId), 
        advancedWhere,
        globalWhere
      )

      // (라) 정렬
      const orderBy = input.sort?.length
        ? input.sort.map((s) => {
          // "column id" -> vendorRfqView.* 중 하나
          const col = (vendorRfqView as any)[s.id]
          return s.desc ? desc(col) : asc(col)
        })
        : [asc(vendorRfqView.vendorId)]

      // ─────────────────────────────────────────────────────
      // 4) View에서 데이터 SELECT
      // ─────────────────────────────────────────────────────
      const [rows, total] = await db.transaction(async (tx) => {
        const data = await tx
          .select({
            id: vendorRfqView.vendorId,
            vendorID: vendorRfqView.vendorId,
            vendorName: vendorRfqView.vendorName,
            vendorCode: vendorRfqView.vendorCode,
            address: vendorRfqView.address,
            country: vendorRfqView.country,
            email: vendorRfqView.email,
            website: vendorRfqView.website,
            vendorStatus: vendorRfqView.vendorStatus,
            // rfqVendorStatus와 rfqVendorUpdated는 나중에 정확한 데이터로 교체할 예정
            rfqVendorStatus: vendorRfqView.rfqVendorStatus,
            rfqVendorUpdated: vendorRfqView.rfqVendorUpdated,
          })
          .from(vendorRfqView)
          .where(finalWhere)
          .orderBy(...orderBy)
          .offset(offset)
          .limit(limit)

        // 중복 제거된 데이터 생성
        const distinctData = Array.from(
          new Map(data.map(row => [row.id, row])).values()
        )

        // 중복 제거된 총 개수 계산
        const [{ count }] = await tx
          .select({ count: sql<number>`count(DISTINCT ${vendorRfqView.vendorId})`.as("count") })
          .from(vendorRfqView)
          .where(finalWhere)

        return [distinctData, Number(count)]
      })


      // ─────────────────────────────────────────────────────
      // 4-1) 정확한 rfqVendorStatus와 rfqVendorUpdated 조회
      // ─────────────────────────────────────────────────────
      const distinctVendorIds = [...new Set(rows.map((r) => r.id))]

      // vendorResponses 테이블에서 정확한 상태와 업데이트 시간 조회
      const vendorStatuses = await db
        .select({
          vendorId: vendorResponses.vendorId,
          status: vendorResponses.responseStatus,
          updatedAt: vendorResponses.updatedAt
        })
        .from(vendorResponses)
        .where(
          and(
            inArray(vendorResponses.vendorId, distinctVendorIds),
            eq(vendorResponses.rfqId, rfqId)
          )
        )

      // vendorId별 상태정보 맵 생성
      const statusMap = new Map<number, { status: string, updatedAt: Date }>()
      for (const vs of vendorStatuses) {
        statusMap.set(vs.vendorId, {
          status: vs.status,
          updatedAt: vs.updatedAt
        })
      }

      // 정확한 상태 정보로 업데이트된 rows 생성
      const updatedRows = rows.map(row => ({
        ...row,
        rfqVendorStatus: statusMap.get(row.id)?.status || null,
        rfqVendorUpdated: statusMap.get(row.id)?.updatedAt || null
      }))

      // ─────────────────────────────────────────────────────
      // 5) 코멘트 조회: 기존과 동일
      // ─────────────────────────────────────────────────────
      console.log("distinctVendorIds", distinctVendorIds)
      const commAll = await db
        .select()
        .from(rfqComments)
        .where(
          and(
            inArray(rfqComments.vendorId, distinctVendorIds),
            eq(rfqComments.rfqId, rfqId),
            isNull(rfqComments.evaluationId),
            isNull(rfqComments.cbeId)
          )
        )

      const commByVendorId = new Map<number, any[]>()
      // 먼저 모든 사용자 ID를 수집
      const userIds = new Set(commAll.map(c => c.commentedBy));
      const userIdsArray = Array.from(userIds);

      // Drizzle의 select 메서드를 사용하여 사용자 정보를 가져옴
      const usersData = await db
        .select({
          id: users.id,
          email: users.email,
        })
        .from(users)
        .where(inArray(users.id, userIdsArray));

      // 사용자 ID를 키로 하는 맵 생성
      const userMap = new Map();
      for (const user of usersData) {
        userMap.set(user.id, user);
      }

      // 댓글 정보를 협력업체 ID별로 그룹화하고, 사용자 이메일 추가
      for (const c of commAll) {
        const vid = c.vendorId!
        if (!commByVendorId.has(vid)) {
          commByVendorId.set(vid, [])
        }

        // 사용자 정보 가져오기
        const user = userMap.get(c.commentedBy);
        const userEmail = user ? user.email : 'unknown@example.com'; // 사용자를 찾지 못한 경우 기본값 설정

        commByVendorId.get(vid)!.push({
          id: c.id,
          commentText: c.commentText,
          vendorId: c.vendorId,
          evaluationId: c.evaluationId,
          createdAt: c.createdAt,
          commentedBy: c.commentedBy,
          commentedByEmail: userEmail, // 이메일 추가
        })
      }
      // ─────────────────────────────────────────────────────
      // 6) rows에 comments 병합
      // ─────────────────────────────────────────────────────
      const final = updatedRows.map((row) => ({
        ...row,
        comments: commByVendorId.get(row.id) ?? [],
      }))

      // ─────────────────────────────────────────────────────
      // 7) 반환
      // ─────────────────────────────────────────────────────
      const pageCount = Math.ceil(total / limit)
      return { data: final, pageCount }
    },
    [JSON.stringify({ input, rfqId })],
    { revalidate: 3600, tags: ["vendors", `rfq-${rfqId}`] }
  )()
}

export async function inviteVendors(input: InviteVendorsInput) {
  unstable_noStore() // 서버 액션 캐싱 방지
  try {
    const { rfqId, vendorIds } = input
    if (!rfqId || !Array.isArray(vendorIds) || vendorIds.length === 0) {
      throw new Error("Invalid input")
    }

    const headersList = await headers();
    const host = headersList.get('host') || 'localhost:3000';

    // DB 데이터 준비 및 첨부파일 처리를 위한 트랜잭션
    const rfqData = await db.transaction(async (tx) => {
      // 2-A) RFQ 기본 정보 조회
      const [rfqRow] = await tx
        .select({
          rfqCode: rfqsView.rfqCode,
          description: rfqsView.description,
          projectCode: rfqsView.projectCode,
          projectName: rfqsView.projectName,
          dueDate: rfqsView.dueDate,
          createdBy: rfqsView.createdBy,
        })
        .from(rfqsView)
        .where(eq(rfqsView.id, rfqId))

      if (!rfqRow) {
        throw new Error(`RFQ #${rfqId} not found`)
      }

      // 2-B) 아이템 목록 조회
      const items = await tx
        .select({
          itemCode: rfqItems.itemCode,
          description: rfqItems.description,
          quantity: rfqItems.quantity,
          uom: rfqItems.uom,
        })
        .from(rfqItems)
        .where(eq(rfqItems.rfqId, rfqId))

      // 2-C) 첨부파일 목록 조회
      const attachRows = await tx
        .select({
          id: rfqAttachments.id,
          fileName: rfqAttachments.fileName,
          filePath: rfqAttachments.filePath,
        })
        .from(rfqAttachments)
        .where(
          and(
            eq(rfqAttachments.rfqId, rfqId),
            isNull(rfqAttachments.vendorId),
            isNull(rfqAttachments.evaluationId)
          )
        )

      const vendorRows = await tx
        .select({ id: vendors.id, email: vendors.email })
        .from(vendors)
        .where(inArray(vendors.id, vendorIds))

      // NodeMailer attachments 형식 맞추기
      const attachments = []
      for (const att of attachRows) {
        const absolutePath = path.join(process.cwd(), "public", att.filePath.replace(/^\/+/, ""))
        attachments.push({
          path: absolutePath,
          filename: att.fileName,
        })
      }

      return { rfqRow, items, vendorRows, attachments }
    })

    const { rfqRow, items, vendorRows, attachments } = rfqData
    const loginUrl = `http://${host}/en/partners/rfq`

    // 이메일 전송 오류를 기록할 배열
    const emailErrors = []

    // 각 벤더에 대해 처리
    for (const v of vendorRows) {
      if (!v.email) {
        continue // 이메일 없는 협력업체 무시
      }

      try {
        // DB 업데이트: 각 협력업체 상태 별도 트랜잭션
        await db.transaction(async (tx) => {
          // rfq_vendors upsert
          const existing = await tx
            .select()
            .from(vendorResponses)
            .where(and(eq(vendorResponses.rfqId, rfqId), eq(vendorResponses.vendorId, v.id)))

          if (existing.length > 0) {
            await tx
              .update(vendorResponses)
              .set({
                responseStatus: "INVITED",
                updatedAt: new Date(),
              })
              .where(eq(vendorResponses.id, existing[0].id))
          } else {
            await tx.insert(vendorResponses).values({
              rfqId,
              vendorId: v.id,
              responseStatus: "INVITED",
            })
          }
        })

        // 이메일 발송 (트랜잭션 외부)
        await sendEmail({
          to: v.email,
          subject: `[RFQ ${rfqRow.rfqCode}] You are invited from Samgsung Heavy Industries!`,
          template: "rfq-invite",
          context: {
            language: "en",
            rfqId,
            vendorId: v.id,
            rfqCode: rfqRow.rfqCode,
            projectCode: rfqRow.projectCode,
            projectName: rfqRow.projectName,
            dueDate: rfqRow.dueDate,
            description: rfqRow.description,
            items: items.map((it) => ({
              itemCode: it.itemCode,
              description: it.description,
              quantity: it.quantity,
              uom: it.uom,
            })),
            loginUrl
          },
          attachments,
        })
      } catch (err) {
        // 개별 협력업체 처리 실패 로깅
        console.error(`Failed to process vendor ${v.id}: ${getErrorMessage(err)}`)
        emailErrors.push({ vendorId: v.id, error: getErrorMessage(err) })
        // 계속 진행 (다른 협력업체 처리)
      }
    }

    // 최종적으로 RFQ 상태 업데이트 (별도 트랜잭션)
    try {
      await db.transaction(async (tx) => {
        await tx
          .update(rfqs)
          .set({
            status: "PUBLISHED",
            updatedAt: new Date(),
          })
          .where(eq(rfqs.id, rfqId))

        console.log(`Updated RFQ #${rfqId} status to PUBLISHED`)
      })

      // 캐시 무효화
      revalidateTag("tbe-vendors")
      revalidateTag("all-tbe-vendors")
      revalidateTag("rfq-vendors")
      revalidateTag("cbe-vendors")
      revalidateTag("rfqs")
      revalidateTag(`rfq-${rfqId}`)
      // revalidateTag("rfqs");
      // revalidateTag(`rfq-${rfqId}`);
      // revalidateTag("vendors");
      // revalidateTag("rfq-vendors");
      // vendorIds.forEach(vendorId => {
      //   revalidateTag(`vendor-${vendorId}`);
      // });

      // 이메일 오류가 있었는지 확인
      if (emailErrors.length > 0) {
        return {
          error: `일부 벤더에게 이메일 발송 실패 (${emailErrors.length}/${vendorRows.length}), RFQ 상태는 업데이트됨`,
          emailErrors
        }
      }

      return { error: null }
    } catch (err) {
      return { error: `RFQ 상태 업데이트 실패: ${getErrorMessage(err)}` }
    }
  } catch (err) {
    return { error: getErrorMessage(err) }
  }
}


/**
 * TBE용 평가 데이터 목록 조회
 */
export async function getTBE(input: GetTBESchema, rfqId: number) {
  return unstable_cache(
    async () => {
      // 1) 페이징
      const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10)
      const limit = input.perPage ?? 10

      // 2) 고급 필터
      const advancedWhere = filterColumns({
        table: vendorTbeView,
        filters: input.filters ?? [],
        joinOperator: input.joinOperator ?? "and",
      })

      // 3) 글로벌 검색
      let globalWhere
      if (input.search) {
        const s = `%${input.search}%`
        globalWhere = or(
          sql`${vendorTbeView.vendorName} ILIKE ${s}`,
          sql`${vendorTbeView.vendorCode} ILIKE ${s}`,
          sql`${vendorTbeView.email} ILIKE ${s}`
        )
      }

      // 4) REJECTED 아니거나 NULL
      const notRejected = or(
        ne(vendorTbeView.rfqVendorStatus, "REJECTED"),
        isNull(vendorTbeView.rfqVendorStatus)
      )

      // 5) finalWhere
      const finalWhere = and(
        eq(vendorTbeView.rfqId, rfqId),
        advancedWhere,
        globalWhere
      )

      // 6) 정렬
      const orderBy = input.sort?.length
        ? input.sort.map((s) => {
          const col = (vendorTbeView as any)[s.id]
          return s.desc ? desc(col) : asc(col)
        })
        : [asc(vendorTbeView.vendorId)]

      // 7) 메인 SELECT
      const [rows, total] = await db.transaction(async (tx) => {
        const data = await tx
          .select({
            // 원하는 컬럼들
            id: vendorTbeView.vendorId,
            tbeId: vendorTbeView.tbeId,
            vendorId: vendorTbeView.vendorId,
            vendorName: vendorTbeView.vendorName,
            vendorCode: vendorTbeView.vendorCode,
            address: vendorTbeView.address,
            country: vendorTbeView.country,
            email: vendorTbeView.email,
            website: vendorTbeView.website,
            vendorStatus: vendorTbeView.vendorStatus,

            rfqId: vendorTbeView.rfqId,
            rfqCode: vendorTbeView.rfqCode,
            projectCode: vendorTbeView.projectCode,
            projectName: vendorTbeView.projectName,
            description: vendorTbeView.description,
            dueDate: vendorTbeView.dueDate,

            rfqVendorStatus: vendorTbeView.rfqVendorStatus,
            rfqVendorUpdated: vendorTbeView.rfqVendorUpdated,

            tbeResult: vendorTbeView.tbeResult,
            tbeNote: vendorTbeView.tbeNote,
            tbeUpdated: vendorTbeView.tbeUpdated,

            technicalResponseId:vendorTbeView.technicalResponseId,
            technicalResponseStatus:vendorTbeView.technicalResponseStatus,
            technicalSummary:vendorTbeView.technicalSummary,
            technicalNotes:vendorTbeView.technicalNotes,
            technicalUpdated:vendorTbeView.technicalUpdated,
          })
          .from(vendorTbeView)
          .where(finalWhere)
          .orderBy(...orderBy)
          .offset(offset)
          .limit(limit)

        const [{ count }] = await tx
          .select({ count: sql<number>`count(*)`.as("count") })
          .from(vendorTbeView)
          .where(finalWhere)

        return [data, Number(count)]
      })

      if (!rows.length) {
        return { data: [], pageCount: 0 }
      }

      // 8) Comments 조회
      const distinctVendorIds = [...new Set(rows.map((r) => r.vendorId))]

      const commAll = await db
        .select({
          id: rfqComments.id,
          commentText: rfqComments.commentText,
          vendorId: rfqComments.vendorId,
          evaluationId: rfqComments.evaluationId,
          createdAt: rfqComments.createdAt,
          commentedBy: rfqComments.commentedBy,
          evalType: rfqEvaluations.evalType,
        })
        .from(rfqComments)
        .innerJoin(
          rfqEvaluations,
          and(
            eq(rfqEvaluations.id, rfqComments.evaluationId),
            eq(rfqEvaluations.evalType, "TBE")
          )
        )
        .where(
          and(
            isNotNull(rfqComments.evaluationId),
            eq(rfqComments.rfqId, rfqId),
            inArray(rfqComments.vendorId, distinctVendorIds)
          )
        )

      // 8-A) vendorId -> comments grouping
      const commByVendorId = new Map<number, any[]>()
      for (const c of commAll) {
        const vid = c.vendorId!
        if (!commByVendorId.has(vid)) {
          commByVendorId.set(vid, [])
        }
        commByVendorId.get(vid)!.push({
          id: c.id,
          commentText: c.commentText,
          vendorId: c.vendorId,
          evaluationId: c.evaluationId,
          createdAt: c.createdAt,
          commentedBy: c.commentedBy,
        })
      }

      // 9) TBE 파일 조회 - vendorResponseAttachments로 대체
      // Step 1: Get vendorResponses for the rfqId and vendorIds
      const responsesAll = await db
        .select({
          id: vendorResponses.id,
          vendorId: vendorResponses.vendorId
        })
        .from(vendorResponses)
        .where(
          and(
            eq(vendorResponses.rfqId, rfqId),
            inArray(vendorResponses.vendorId, distinctVendorIds)
          )
        );

      // Group responses by vendorId for later lookup
      const responsesByVendorId = new Map<number, number[]>();
      for (const resp of responsesAll) {
        if (!responsesByVendorId.has(resp.vendorId)) {
          responsesByVendorId.set(resp.vendorId, []);
        }
        responsesByVendorId.get(resp.vendorId)!.push(resp.id);
      }

      // Step 2: Get all responseIds
      const allResponseIds = responsesAll.map(r => r.id);

      // Step 3: Get technicalResponses for these responseIds
      const technicalResponsesAll = await db
        .select({
          id: vendorTechnicalResponses.id,
          responseId: vendorTechnicalResponses.responseId
        })
        .from(vendorTechnicalResponses)
        .where(inArray(vendorTechnicalResponses.responseId, allResponseIds));

      // Create mapping from responseId to technicalResponseIds
      const technicalResponseIdsByResponseId = new Map<number, number[]>();
      for (const tr of technicalResponsesAll) {
        if (!technicalResponseIdsByResponseId.has(tr.responseId)) {
          technicalResponseIdsByResponseId.set(tr.responseId, []);
        }
        technicalResponseIdsByResponseId.get(tr.responseId)!.push(tr.id);
      }

      // Step 4: Get all technicalResponseIds
      const allTechnicalResponseIds = technicalResponsesAll.map(tr => tr.id);

      // Step 5: Get attachments for these technicalResponseIds
      const filesAll = await db
        .select({
          id: vendorResponseAttachments.id,
          fileName: vendorResponseAttachments.fileName,
          filePath: vendorResponseAttachments.filePath,
          technicalResponseId: vendorResponseAttachments.technicalResponseId,
          fileType: vendorResponseAttachments.fileType,
          attachmentType: vendorResponseAttachments.attachmentType,
          description: vendorResponseAttachments.description,
          uploadedAt: vendorResponseAttachments.uploadedAt,
          uploadedBy: vendorResponseAttachments.uploadedBy
        })
        .from(vendorResponseAttachments)
        .where(
          and(
            inArray(vendorResponseAttachments.technicalResponseId, allTechnicalResponseIds),
            isNotNull(vendorResponseAttachments.technicalResponseId)
          )
        );

      // Step 6: Create mapping from technicalResponseId to attachments
      const filesByTechnicalResponseId = new Map<number, any[]>();
      for (const file of filesAll) {
        // Skip if technicalResponseId is null (should never happen due to our filter above)
        if (file.technicalResponseId === null) continue;

        if (!filesByTechnicalResponseId.has(file.technicalResponseId)) {
          filesByTechnicalResponseId.set(file.technicalResponseId, []);
        }
        filesByTechnicalResponseId.get(file.technicalResponseId)!.push({
          id: file.id,
          fileName: file.fileName,
          filePath: file.filePath,
          fileType: file.fileType,
          attachmentType: file.attachmentType,
          description: file.description,
          uploadedAt: file.uploadedAt,
          uploadedBy: file.uploadedBy
        });
      }

      // Step 7: Create the final filesByVendorId map
      const filesByVendorId = new Map<number, any[]>();
      for (const [vendorId, responseIds] of responsesByVendorId.entries()) {
        filesByVendorId.set(vendorId, []);

        for (const responseId of responseIds) {
          const technicalResponseIds = technicalResponseIdsByResponseId.get(responseId) || [];

          for (const technicalResponseId of technicalResponseIds) {
            const files = filesByTechnicalResponseId.get(technicalResponseId) || [];
            filesByVendorId.get(vendorId)!.push(...files);
          }
        }
      }

      // 10) 최종 합치기
      const final = rows.map((row) => ({
        ...row,
        dueDate: row.dueDate ? new Date(row.dueDate) : null,
        comments: commByVendorId.get(row.vendorId) ?? [],
        files: filesByVendorId.get(row.vendorId) ?? [],
      }))

      const pageCount = Math.ceil(total / limit)
      return { data: final, pageCount }
    },
    [JSON.stringify({ input, rfqId })],
    {
      revalidate: 3600,
      tags: ["tbe", `rfq-${rfqId}`],
    }
  )()
}

export async function getTBEforVendor(input: GetTBESchema, vendorId: number) {

  if (isNaN(vendorId) || vendorId === null || vendorId === undefined) {
    throw new Error("유효하지 않은 vendorId: 숫자 값이 필요합니다");
  }

  return unstable_cache(
    async () => {
      // 1) 페이징
      const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10)
      const limit = input.perPage ?? 10

      // 2) 고급 필터
      const advancedWhere = filterColumns({
        table: vendorTbeView,
        filters: input.filters ?? [],
        joinOperator: input.joinOperator ?? "and",
      })

      // 3) 글로벌 검색
      let globalWhere
      if (input.search) {
        const s = `%${input.search}%`
        globalWhere = or(
          sql`${vendorTbeView.vendorName} ILIKE ${s}`,
          sql`${vendorTbeView.vendorCode} ILIKE ${s}`,
          sql`${vendorTbeView.email} ILIKE ${s}`
        )
      }

      // 4) REJECTED 아니거나 NULL
      const notRejected = or(
        ne(vendorTbeView.rfqVendorStatus, "REJECTED"),
        isNull(vendorTbeView.rfqVendorStatus)
      )

      // 5) finalWhere
      const finalWhere = and(
        isNotNull(vendorTbeView.tbeId),
        eq(vendorTbeView.vendorId, vendorId),
        // notRejected,
        advancedWhere,
        globalWhere
      )

      // 6) 정렬
      const orderBy = input.sort?.length
        ? input.sort.map((s) => {
          const col = (vendorTbeView as any)[s.id]
          return s.desc ? desc(col) : asc(col)
        })
        : [asc(vendorTbeView.vendorId)]

      // 7) 메인 SELECT - vendor 기준으로 GROUP BY
      const [rows, total] = await db.transaction(async (tx) => {
        const data = await tx
          .select({
            // 원하는 컬럼들
            id: vendorTbeView.vendorId,
            tbeId: vendorTbeView.tbeId,
            vendorId: vendorTbeView.vendorId,
            vendorName: vendorTbeView.vendorName,
            vendorCode: vendorTbeView.vendorCode,
            address: vendorTbeView.address,
            country: vendorTbeView.country,
            email: vendorTbeView.email,
            website: vendorTbeView.website,
            vendorStatus: vendorTbeView.vendorStatus,

            rfqId: vendorTbeView.rfqId,
            rfqCode: vendorTbeView.rfqCode,

            rfqStatus:vendorTbeView.rfqStatus,
            rfqDescription: vendorTbeView.description,
            rfqDueDate: vendorTbeView.dueDate,

            projectCode: vendorTbeView.projectCode,
            projectName: vendorTbeView.projectName,
            description: vendorTbeView.description,
            dueDate: vendorTbeView.dueDate,

            vendorResponseId: vendorTbeView.vendorResponseId,
            rfqVendorStatus: vendorTbeView.rfqVendorStatus,
            rfqVendorUpdated: vendorTbeView.rfqVendorUpdated,

            tbeResult: vendorTbeView.tbeResult,
            tbeNote: vendorTbeView.tbeNote,
            tbeUpdated: vendorTbeView.tbeUpdated,
          })
          .from(vendorTbeView)
          .where(finalWhere)
          .orderBy(...orderBy)
          .offset(offset)
          .limit(limit)

        const [{ count }] = await tx
          .select({ count: sql<number>`count(*)`.as("count") })
          .from(vendorTbeView)
          .where(finalWhere)

        return [data, Number(count)]
      })

      if (!rows.length) {
        return { data: [], pageCount: 0 }
      }

      // 8) Comments 조회
      //    - evaluationId != null && evalType = "TBE"
      //    - => leftJoin(rfqEvaluations) or innerJoin
      const distinctVendorIds = [...new Set(rows.map((r) => r.vendorId))]
      const distinctTbeIds = [...new Set(rows.map((r) => r.tbeId).filter(Boolean))]

      // (A) 조인 방식
      const commAll = await db
        .select({
          id: rfqComments.id,
          commentText: rfqComments.commentText,
          vendorId: rfqComments.vendorId,
          evaluationId: rfqComments.evaluationId,
          createdAt: rfqComments.createdAt,
          commentedBy: rfqComments.commentedBy,
          evalType: rfqEvaluations.evalType, // (optional)
        })
        .from(rfqComments)
        // evalType = 'TBE'
        .innerJoin(
          rfqEvaluations,
          and(
            eq(rfqEvaluations.id, rfqComments.evaluationId),
            eq(rfqEvaluations.evalType, "TBE") // ★ TBE만
          )
        )
        .where(
          and(
            isNotNull(rfqComments.evaluationId),
            inArray(rfqComments.vendorId, distinctVendorIds)
          )
        )

      // 8-A) vendorId -> comments grouping
      const commByVendorId = new Map<number, any[]>()
      for (const c of commAll) {
        const vid = c.vendorId!
        if (!commByVendorId.has(vid)) {
          commByVendorId.set(vid, [])
        }
        commByVendorId.get(vid)!.push({
          id: c.id,
          commentText: c.commentText,
          vendorId: c.vendorId,
          evaluationId: c.evaluationId,
          createdAt: c.createdAt,
          commentedBy: c.commentedBy,
        })
      }

      // 9) TBE 템플릿 파일 수 조회
      const templateFiles = await db
        .select({
          tbeId: rfqAttachments.evaluationId,
          fileCount: sql<number>`count(*)`.as("file_count"),
        })
        .from(rfqAttachments)
        .where(
          and(
            inArray(rfqAttachments.evaluationId, distinctTbeIds),
            isNull(rfqAttachments.vendorId),
            isNull(rfqAttachments.commentId)
          )
        )
        .groupBy(rfqAttachments.evaluationId)

      // tbeId -> fileCount 매핑 - null 체크 추가
      const templateFileCountMap = new Map<number, number>()
      for (const tf of templateFiles) {
        if (tf.tbeId !== null) {
          templateFileCountMap.set(tf.tbeId, Number(tf.fileCount))
        }
      }

      // 10) TBE 응답 파일 확인 (각 tbeId + vendorId 조합에 대해)
      const tbeResponseFiles = await db
        .select({
          tbeId: rfqAttachments.evaluationId,
          vendorId: rfqAttachments.vendorId,
          responseFileCount: sql<number>`count(*)`.as("response_file_count"),
        })
        .from(rfqAttachments)
        .where(
          and(
            inArray(rfqAttachments.evaluationId, distinctTbeIds),
            inArray(rfqAttachments.vendorId, distinctVendorIds),
            isNull(rfqAttachments.commentId)
          )
        )
        .groupBy(rfqAttachments.evaluationId, rfqAttachments.vendorId)

      // 10-A) TBE 제출 파일 상세 정보 조회 (vendor별로 그룹화)
      

      // tbeId_vendorId -> hasResponse 매핑 - null 체크 추가
      const tbeResponseMap = new Map<string, number>()
      for (const rf of tbeResponseFiles) {
        if (rf.tbeId !== null && rf.vendorId !== null) {
          const key = `${rf.tbeId}_${rf.vendorId}`
          tbeResponseMap.set(key, Number(rf.responseFileCount))
        }
      }

      // 11) 최종 합치기
      const final = rows.map((row) => {
        const tbeId = row.tbeId
        const vendorId = row.vendorId

        // 템플릿 파일 수
        const templateFileCount = tbeId !== null ? templateFileCountMap.get(tbeId) || 0 : 0

        // 응답 파일 여부
        const responseKey = tbeId !== null ? `${tbeId}_${vendorId}` : ""
        const responseFileCount = responseKey ? tbeResponseMap.get(responseKey) || 0 : 0

        return {
          ...row,
          dueDate: row.dueDate ? new Date(row.dueDate) : null,
          comments: commByVendorId.get(row.vendorId) ?? [],
          templateFileCount,  // 추가: 템플릿 파일 수
          hasResponse: responseFileCount > 0,  // 추가: 응답 파일 제출 여부
        }
      })

      const pageCount = Math.ceil(total / limit)
      return { data: final, pageCount }
    },
    [JSON.stringify(input), String(vendorId)], // 캐싱 키에 packagesId 추가
    {
      revalidate: 3600,
      tags: [`tbe-vendor-${vendorId}`],
    }
  )()
}

export async function inviteTbeVendorsAction(formData: FormData) {
  // 캐싱 방지
  unstable_noStore()

  try {
    // 1) FormData에서 기본 필드 추출
    const rfqId = Number(formData.get("rfqId"))
    const vendorIdsRaw = formData.getAll("vendorIds[]")
    const vendorIds = vendorIdsRaw.map((id) => Number(id))

    // 2) FormData에서 파일들 추출 (multiple)
    const tbeFiles = formData.getAll("tbeFiles") as File[]
    if (!rfqId || !vendorIds.length || !tbeFiles.length) {
      throw new Error("Invalid input or no files attached.")
    }

    // /public/rfq/[rfqId] 경로
    const uploadDir = path.join(process.cwd(), "public", "rfq", String(rfqId))
    
    // 디렉토리가 없다면 생성
    try {
      await fs.mkdir(uploadDir, { recursive: true })
    } catch (err) {
      console.error("디렉토리 생성 실패:", err)
    }

    // DB 트랜잭션
    await db.transaction(async (tx) => {
      // (A) RFQ 기본 정보 조회
      const [rfqRow] = await tx
        .select({
          rfqCode: vendorResponsesView.rfqCode,
          description: vendorResponsesView.rfqDescription,
          projectCode: vendorResponsesView.projectCode,
          projectName: vendorResponsesView.projectName,
          dueDate: vendorResponsesView.rfqDueDate,
          createdBy: vendorResponsesView.rfqCreatedBy,
        })
        .from(vendorResponsesView)
        .where(eq(vendorResponsesView.rfqId, rfqId))

      if (!rfqRow) {
        throw new Error(`RFQ #${rfqId} not found`)
      }

      // (B) RFQ 아이템 목록
      const items = await tx
        .select({
          itemCode: rfqItems.itemCode,
          description: rfqItems.description,
          quantity: rfqItems.quantity,
          uom: rfqItems.uom,
        })
        .from(rfqItems)
        .where(eq(rfqItems.rfqId, rfqId))

      // (C) 대상 벤더들 (이메일 정보 확장)
      const vendorRows = await tx
        .select({ 
          id: vendors.id, 
          name: vendors.vendorName,
          email: vendors.email,
          representativeEmail: vendors.representativeEmail  // 대표자 이메일 추가
        })
        .from(vendors)
        .where(sql`${vendors.id} in (${vendorIds})`)

      // (D) 모든 TBE 파일 저장 & 이후 협력업체 초대 처리
      //     파일은 한 번만 저장해도 되지만, 각 벤더별로 따로 저장/첨부가 필요하다면 루프를 돌려도 됨.
      //     여기서는 "모든 파일"을 RFQ-DIR에 저장 + "각 협력업체"에는 동일 파일 목록을 첨부한다는 예시.
      const savedFiles = []
      for (const file of tbeFiles) {
        const originalName = file.name || "tbe-sheet.xlsx"
        // 파일명 충돌 방지를 위한 타임스탬프 추가
        const timestamp = new Date().getTime()
        const fileName = `${timestamp}-${originalName}`
        const savePath = path.join(uploadDir, fileName)

        // 파일 ArrayBuffer → Buffer 변환 후 저장
        const arrayBuffer = await file.arrayBuffer()
        await fs.writeFile(savePath, Buffer.from(arrayBuffer))

        // 저장 경로 & 파일명 기록
        savedFiles.push({
          fileName: originalName, // 원본 파일명으로 첨부
          filePath: `/rfq/${rfqId}/${fileName}`, // public 이하 경로
          absolutePath: savePath,
        })
      }

      // (E) 각 벤더별로 TBE 평가 레코드, 초대 처리, 메일 발송
      for (const vendor of vendorRows) {
        // 1) 협력업체 연락처 조회 - 추가 이메일 수집
        const contacts = await tx
          .select({
            contactName: vendorContacts.contactName,
            contactEmail: vendorContacts.contactEmail,
            isPrimary: vendorContacts.isPrimary,
          })
          .from(vendorContacts)
          .where(eq(vendorContacts.vendorId, vendor.id))

        // 2) 모든 이메일 주소 수집 및 중복 제거
        const allEmails = new Set<string>()
        
        // 협력업체 이메일 추가 (있는 경우에만)
        if (vendor.email) {
          allEmails.add(vendor.email.trim().toLowerCase())
        }
        
        // 협력업체 대표자 이메일 추가 (있는 경우에만)
        if (vendor.representativeEmail) {
          allEmails.add(vendor.representativeEmail.trim().toLowerCase())
        }
        
        // 연락처 이메일 추가
        contacts.forEach(contact => {
          if (contact.contactEmail) {
            allEmails.add(contact.contactEmail.trim().toLowerCase())
          }
        })
        
        // 중복이 제거된 이메일 주소 배열로 변환
        const uniqueEmails = Array.from(allEmails)

        if (uniqueEmails.length === 0) {
          console.warn(`협력업체 ID ${vendor.id}에 등록된 이메일 주소가 없습니다. TBE 초대를 건너뜁니다.`)
          continue
        }

        // 3) TBE 평가 레코드 생성
        const [evalRow] = await tx
          .insert(rfqEvaluations)
          .values({
            rfqId,
            vendorId: vendor.id,
            evalType: "TBE",
          })
          .returning({ id: rfqEvaluations.id })

        // 4) rfqAttachments에 저장한 파일들을 기록
        for (const sf of savedFiles) {
          await tx.insert(rfqAttachments).values({
            rfqId,
            vendorId: vendor.id,
            evaluationId: evalRow.id,
            fileName: sf.fileName,
            filePath: sf.filePath,
          })
        }

        // 5) 각 고유 이메일 주소로 초대 메일 발송
        const baseUrl = process.env.NEXT_PUBLIC_BASE_URL || 'http://3.36.56.124:3000'
        const loginUrl = `${baseUrl}/ko/partners/rfq`
        
        console.log(`협력업체 ID ${vendor.id}(${vendor.name})에 대해 ${uniqueEmails.length}개의 고유 이메일로 TBE 초대 발송`)
        
        for (const email of uniqueEmails) {
          try {
            // 연락처 이름 찾기 (이메일과 일치하는 연락처가 있으면 사용, 없으면 '벤더명 담당자'로 대체)
            const contact = contacts.find(c => 
              c.contactEmail && c.contactEmail.toLowerCase() === email.toLowerCase()
            )
            const contactName = contact?.contactName || `${vendor.name} 담당자`
            
            await sendEmail({
              to: email,
              subject: `[RFQ ${rfqRow.rfqCode}] You are invited for TBE!`,
              template: "rfq-invite",
              context: {
                language: "en",
                rfqId,
                vendorId: vendor.id,
                contactName, // 연락처 이름 추가
                rfqCode: rfqRow.rfqCode,
                projectCode: rfqRow.projectCode,
                projectName: rfqRow.projectName,
                dueDate: rfqRow.dueDate,
                description: rfqRow.description,
                items: items.map((it) => ({
                  itemCode: it.itemCode,
                  description: it.description,
                  quantity: it.quantity,
                  uom: it.uom,
                })),
                loginUrl,
              },
              attachments: savedFiles.map((sf) => ({
                path: sf.absolutePath,
                filename: sf.fileName,
              })),
            })
            console.log(`이메일 전송 성공: ${email} (${contactName})`)
          } catch (emailErr) {
            console.error(`이메일 전송 실패 (${email}):`, emailErr)
          }
        }
      }

      // 6) 캐시 무효화
      revalidateTag("tbe")
      revalidateTag("vendors")
      revalidateTag(`rfq-${rfqId}`)
      revalidateTag("tbe-vendors")

    })

    // 성공
    return { error: null }
  } catch (err) {
    console.error("[inviteTbeVendorsAction] Error:", err)
    return { error: getErrorMessage(err) }
  }
}
////partners


export async function modifyRfqVendor(input: UpdateRfqVendorSchema) {
  unstable_noStore();
  try {
    const data = await db.transaction(async (tx) => {
      const [res] = await updateRfqVendor(tx, input.id, {
        responseStatus: input.status,
      });
      return res;
    });

    revalidateTag("vendors");
    revalidateTag("tbe");

    return { data: null, error: null };
  } catch (err) {
    return { data: null, error: getErrorMessage(err) };
  }
}

export async function createRfqCommentWithAttachments(params: {
  rfqId: number
  vendorId?: number | null
  commentText: string
  commentedBy: number
  evaluationId?: number | null
  cbeId?: number | null
  files?: File[]
}) {
  const { rfqId, vendorId, commentText, commentedBy, evaluationId,cbeId, files } = params
  console.log("cbeId", cbeId)
  console.log("evaluationId", evaluationId)
  // 1) 새로운 코멘트 생성
  const [insertedComment] = await db
    .insert(rfqComments)
    .values({
      rfqId,
      vendorId: vendorId || null,
      commentText,
      commentedBy,
      evaluationId: evaluationId || null,
      cbeId: cbeId || null,
    })
    .returning({ id: rfqComments.id, createdAt: rfqComments.createdAt }) // id만 반환하도록

  if (!insertedComment) {
    throw new Error("Failed to create comment")
  }

  // 2) 첨부파일 처리
  if (files && files.length > 0) {

    const rfqDir = path.join(process.cwd(), "public", "rfq", String(rfqId));
    // 폴더 없으면 생성
    await fs.mkdir(rfqDir, { recursive: true });

    for (const file of files) {
      const ab = await file.arrayBuffer();
      const buffer = Buffer.from(ab);

      // 2-2) 고유 파일명
      const uniqueName = `${randomUUID()}-${file.name}`;
      // 예) "rfq/123/xxx"
      const relativePath = path.join("rfq", String(rfqId), uniqueName);
      const absolutePath = path.join(process.cwd(), "public", relativePath);

      // 2-3) 파일 저장
      await fs.writeFile(absolutePath, buffer);

      // DB에 첨부파일 row 생성
      await db.insert(rfqAttachments).values({
        rfqId,
        vendorId: vendorId || null,
        evaluationId: evaluationId || null,
        cbeId: cbeId || null,
        commentId: insertedComment.id, // 새 코멘트와 연결
        fileName: file.name,
        filePath: "/" + relativePath.replace(/\\/g, "/"),
      })
    }
  }

  revalidateTag("rfq-vendors");
  revalidateTag(`rfq-${rfqId}`);
  
  if (vendorId) {
    revalidateTag(`vendor-${vendorId}`);
  }
  
  if (evaluationId) {
    revalidateTag("tbe");
    revalidateTag(`tbe-vendor-${vendorId}`);
    revalidateTag("all-tbe-vendors");
  }
  
  if (cbeId) {
    revalidateTag("cbe");
    revalidateTag(`cbe-vendor-${vendorId}`);
    revalidateTag("all-cbe-vendors");
  }

  return { ok: true, commentId: insertedComment.id, createdAt: insertedComment.createdAt }
}

export async function fetchRfqAttachmentsbyCommentId(commentId: number) {
  // DB select
  const rows = await db
    .select()
    .from(rfqAttachments)
    .where(eq(rfqAttachments.commentId, commentId))

  // rows: { id, fileName, filePath, createdAt, vendorId, ... }
  // 필요 없는 필드는 omit하거나 transform 가능
  return rows.map((row) => ({
    id: row.id,
    fileName: row.fileName,
    filePath: row.filePath,
    createdAt: row.createdAt, // or string
    vendorId: row.vendorId,
    evaluationId: row.evaluationId,
    size: undefined, // size를 DB에 저장하지 않았다면
  }))
}

export async function updateRfqComment(params: {
  commentId: number
  commentText: string
}) {
  const { commentId, commentText } = params

  // 예: 간단한 길이 체크 등 유효성 검사
  if (!commentText || commentText.trim().length === 0) {
    throw new Error("Comment text must not be empty.")
  }

  // DB 업데이트
  const updatedRows = await db
    .update(rfqComments)
    .set({ commentText }) // 필요한 컬럼만 set
    .where(eq(rfqComments.id, commentId))
    .returning({ id: rfqComments.id })

  // 혹은 returning 전체(row)를 받아서 확인할 수도 있음
  if (updatedRows.length === 0) {
    // 해당 id가 없으면 예외
    throw new Error("Comment not found or already deleted.")
  }
  revalidateTag("rfq-vendors");
  revalidateTag("tbe");
  revalidateTag("cbe");

  return { ok: true }
}

export type Project = {
  id: number;
  projectCode: string;
  projectName: string;
}

export async function getProjects(): Promise<Project[]> {
  try {
    // 트랜잭션을 사용하여 프로젝트 데이터 조회
    const projectList = await db.transaction(async (tx) => {
      // 모든 프로젝트 조회
      const results = await tx
        .select({
          id: projects.id,
          projectCode: projects.code, // 테이블의 실제 컬럼명에 맞게 조정
          projectName: projects.name, // 테이블의 실제 컬럼명에 맞게 조정
        })
        .from(projects)
        .orderBy(projects.code);

      return results;
    });

    return projectList;
  } catch (error) {
    console.error("프로젝트 목록 가져오기 실패:", error);
    return []; // 오류 발생 시 빈 배열 반환
  }
}


export async function getBidProjects(): Promise<Project[]> {
  try {
    // 트랜잭션을 사용하여 프로젝트 데이터 조회
    const projectList = await db.transaction(async (tx) => {
      // 모든 프로젝트 조회
      const results = await tx
        .select({
          id: biddingProjects.id,
          projectCode: biddingProjects.pspid,
          projectName: biddingProjects.projNm,
        })
        .from(biddingProjects)
        .orderBy(biddingProjects.id);

      return results;
    });
    
    // Handle null projectName values
    const validProjectList = projectList.map(project => ({
      ...project,
      projectName: project.projectName || '' // Replace null with empty string
    }));

    return validProjectList;
  } catch (error) {
    console.error("프로젝트 목록 가져오기 실패:", error);
    return []; // 오류 발생 시 빈 배열 반환
  }
}


// 반환 타입 명시적 정의 - rfqCode가 null일 수 있음을 반영
export interface BudgetaryRfq {
  id: number;
  rfqCode: string | null; // null 허용으로 변경
  description: string | null;
  projectId: number | null;
  projectCode: string | null;
  projectName: string | null;
}

type GetBudgetaryRfqsResponse =
  | { rfqs: BudgetaryRfq[]; totalCount: number; error?: never }
  | { error: string; rfqs?: never; totalCount: number }
/**
 * Budgetary 타입의 RFQ 목록을 가져오는 서버 액션
 * Purchase RFQ 생성 시 부모 RFQ로 선택할 수 있도록 함
 * 페이징 및 필터링 기능 포함
 */
export interface GetBudgetaryRfqsParams {
  search?: string;
  projectId?: number;
  rfqId?: number;          // 특정 ID로 단일 RFQ 검색

  limit?: number;
  offset?: number;
}

export async function getBudgetaryRfqs(params: GetBudgetaryRfqsParams = {}): Promise<GetBudgetaryRfqsResponse> {
  const { search, projectId, rfqId, limit = 50, offset = 0 } = params;
  const cacheKey = `rfqs-query-${JSON.stringify(params)}`;

  return unstable_cache(
    async () => {
      try {
        // 기본 검색 조건 구성
        let baseCondition;
        

        
        // 특정 ID로 검색하는 경우
        if (rfqId) {
          baseCondition = and(baseCondition, eq(rfqs.id, rfqId));
        }
        
        let where1;
        // 검색어 조건 추가 (있을 경우)
        if (search && search.trim()) {
          const searchTerm = `%${search.trim()}%`;
          const searchCondition = or(
            ilike(rfqs.rfqCode, searchTerm),
            ilike(rfqs.description, searchTerm),
            ilike(projects.code, searchTerm),
            ilike(projects.name, searchTerm)
          );
          where1 = searchCondition;
        }
        
        let where2;
        // 프로젝트 ID 조건 추가 (있을 경우)
        if (projectId) {
          where2 = eq(rfqs.projectId, projectId);
        }
        
        const finalWhere = and(baseCondition, where1, where2);
        
        // 총 개수 조회
        const [countResult] = await db
          .select({ count: count() })
          .from(rfqs)
          .leftJoin(projects, eq(rfqs.projectId, projects.id))
          .where(finalWhere);
        
        // 실제 데이터 조회
        const resultRfqs = await db
          .select({
            id: rfqs.id,
            rfqCode: rfqs.rfqCode,
            description: rfqs.description,
            projectId: rfqs.projectId,
            projectCode: projects.code,
            projectName: projects.name,
          })
          .from(rfqs)
          .leftJoin(projects, eq(rfqs.projectId, projects.id))
          .where(finalWhere)
          .orderBy(desc(rfqs.createdAt))
          .limit(limit)
          .offset(offset);
        
        return {
          rfqs: resultRfqs,
          totalCount: Number(countResult?.count) || 0
        };
      } catch (error) {
        console.error("Error fetching RFQs:", error);
        return {
          error: "Failed to fetch RFQs",
          totalCount: 0
        };
      }
    },
    [cacheKey],
    {
      revalidate: 60,  // 1분 캐시
      tags: ["rfqs-query"],
    }
  )();
}
export async function getAllVendors() {
  // Adjust the query as needed (add WHERE, ORDER, etc.)
  const allVendors = await db.select().from(vendors)
  return allVendors
}


export async function getVendorContactsByVendorId(vendorId: number) {
  try {
    const contacts = await db.query.vendorContacts.findMany({
      where: eq(vendorContacts.vendorId, vendorId),
    });
    
    return { success: true, data: contacts };
  } catch (error) {
    console.error("Error fetching vendor contacts:", error);
    return { success: false, error: "Failed to fetch vendor contacts" };
  }
}
/**
 * Server action to associate items from an RFQ with a vendor
 * 
 * @param rfqId - The ID of the RFQ containing items to associate
 * @param vendorId - The ID of the vendor to associate items with
 * @returns Object indicating success or failure
 */
export async function addItemToVendors(rfqId: number, vendorIds: number[]) {
  try {
    // Input validation
    if (!vendorIds.length) {
      return {
        success: false,
        error: "No vendors selected"
      };
    }

    // 1. Find all itemCodes associated with the given rfqId using select
    const rfqItemResults = await db
      .select({ itemCode: rfqItems.itemCode })
      .from(rfqItems)
      .where(eq(rfqItems.rfqId, rfqId));

    // Extract itemCodes
    const itemCodes = rfqItemResults.map(item => item.itemCode);

    if (itemCodes.length === 0) {
      return {
        success: false,
        error: "No items found for this RFQ"
      };
    }

    // 2. Find existing vendor-item combinations to avoid duplicates
    const existingCombinations = await db
      .select({
        vendorId: vendorPossibleItems.vendorId,
        itemCode: vendorPossibleItems.itemCode
      })
      .from(vendorPossibleItems)
      .where(
        and(
          inArray(vendorPossibleItems.vendorId, vendorIds),
          inArray(vendorPossibleItems.itemCode, itemCodes)
        )
      );

    // Create a Set of existing combinations for easy lookups
    const existingSet = new Set();
    existingCombinations.forEach(combo => {
      existingSet.add(`${combo.vendorId}-${combo.itemCode}`);
    });

    // 3. Prepare records to insert (only non-existing combinations)
    const recordsToInsert = [];

    for (const vendorId of vendorIds) {
      for (const itemCode of itemCodes) {
        const key = `${vendorId}-${itemCode}`;
        if (!existingSet.has(key)) {
          recordsToInsert.push({
            vendorId,
            itemCode,
            // createdAt and updatedAt will be set by defaultNow()
          });
        }
      }
    }

    // 4. Bulk insert if there are records to insert
    let insertedCount = 0;
    if (recordsToInsert.length > 0) {
      const result = await db.insert(vendorPossibleItems).values(recordsToInsert);
      insertedCount = recordsToInsert.length;
    }

    // 5. Revalidate to refresh data
    revalidateTag("rfq-vendors");

    // 6. Return success with counts
    return {
      success: true,
      insertedCount,
      totalPossibleItems: vendorIds.length * itemCodes.length,
      vendorCount: vendorIds.length,
      itemCount: itemCodes.length
    };
  } catch (error) {
    console.error("Error adding items to vendors:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "Unknown error"
    };
  }
}

/**
 * 특정 평가에 대한 TBE 템플릿 파일 목록 조회
 * evaluationId가 일치하고 vendorId가 null인 파일 목록
 */
export async function fetchTbeTemplateFiles(evaluationId: number) {
  try {
    const files = await db
      .select({
        id: rfqAttachments.id,
        fileName: rfqAttachments.fileName,
        filePath: rfqAttachments.filePath,
        createdAt: rfqAttachments.createdAt,
      })
      .from(rfqAttachments)
      .where(
        and(
          isNull(rfqAttachments.commentId),
          isNull(rfqAttachments.vendorId),
          eq(rfqAttachments.evaluationId, evaluationId),
          // eq(rfqAttachments.vendorId, vendorId),

        )
      )

    return { files, error: null }
  } catch (error) {
    console.error("Error fetching TBE template files:", error)
    return {
      files: [],
      error: "템플릿 파일을 가져오는 중 오류가 발생했습니다."
    }
  }
}

export async function getFileFromRfqAttachmentsbyid(fileId: number) {
  try {
    const file = await db
      .select({
        fileName: rfqAttachments.fileName,
        filePath: rfqAttachments.filePath,
      })
      .from(rfqAttachments)
      .where(eq(rfqAttachments.id, fileId))
      .limit(1)

    if (!file.length) {
      return { file: null, error: "파일을 찾을 수 없습니다." }
    }

    return { file: file[0], error: null }
  } catch (error) {
    console.error("Error getting TBE template file info:", error)
    return {
      file: null,
      error: "파일 정보를 가져오는 중 오류가 발생했습니다."
    }
  }
}

/**
 * TBE 응답 파일 업로드 처리
 */
export async function uploadTbeResponseFile(formData: FormData) {
  try {
    const file = formData.get("file") as File
    const rfqId = parseInt(formData.get("rfqId") as string)
    const vendorId = parseInt(formData.get("vendorId") as string)
    const evaluationId = parseInt(formData.get("evaluationId") as string)
    const vendorResponseId = parseInt(formData.get("vendorResponseId") as string)

    if (!file || !rfqId || !vendorId || !evaluationId) {
      return {
        success: false,
        error: "필수 필드가 누락되었습니다."
      }
    }

    // 타임스탬프 기반 고유 파일명 생성
    const timestamp = Date.now()
    const originalName = file.name
    const fileExtension = originalName.split(".").pop()
    const fileName = `${originalName.split(".")[0]}-${timestamp}.${fileExtension}`

    // 업로드 디렉토리 및 경로 정의
    const uploadDir = join(process.cwd(), "public", "rfq", "tbe-responses")

    // 디렉토리가 없으면 생성
    try {
      await mkdir(uploadDir, { recursive: true })
    } catch (error) {
      // 이미 존재하면 무시
    }

    const filePath = join(uploadDir, fileName)

    // 파일을 버퍼로 변환
    const bytes = await file.arrayBuffer()
    const buffer = Buffer.from(bytes)

    // 파일을 서버에 저장
    await writeFile(filePath, buffer)

    // 먼저 vendorTechnicalResponses 테이블에 엔트리 생성
    const technicalResponse = await db.insert(vendorTechnicalResponses)
      .values({
        responseId: vendorResponseId,
        summary: "TBE 응답 파일 업로드", // 필요에 따라 수정
        notes: `파일명: ${originalName}`,
        responseStatus:"SUBMITTED"
      })
      .returning({ id: vendorTechnicalResponses.id });

    // 생성된 기술 응답 ID 가져오기
    const technicalResponseId = technicalResponse[0].id;

    // 파일 정보를 데이터베이스에 저장
    const dbFilePath = `rfq/tbe-responses/${fileName}`

    // vendorResponseAttachments 테이블 스키마에 맞게 데이터 삽입
    await db.insert(vendorResponseAttachments)
      .values({
        // 오류 메시지를 기반으로 올바른 필드 이름 사용
        // 테이블 스키마에 정의된 필드만 포함해야 함
        responseId: vendorResponseId,
        technicalResponseId: technicalResponseId,
        // vendorId와 evaluationId 필드가 테이블에 있다면 포함, 없다면 제거
        // vendorId: vendorId,
        // evaluationId: evaluationId,
        fileName: originalName,
        filePath: dbFilePath,
        uploadedAt: new Date(),
      });

    // 경로 재검증 (캐시된 데이터 새로고침)
    // revalidatePath(`/rfq/${rfqId}/tbe`) // 화면 새로고침 방지를 위해 제거
    revalidateTag(`tbe-vendor-${vendorId}`)
    revalidateTag("all-tbe-vendors")

    return {
      success: true,
      message: "파일이 성공적으로 업로드되었습니다."
    }
  } catch (error) {
    console.error("Error uploading file:", error)
    return {
      success: false,
      error: "파일 업로드에 실패했습니다."
    }
  }
}

export async function getTbeSubmittedFiles(responseId: number) {
  try {
    // First, get the technical response IDs where vendorResponseId matches responseId
    const technicalResponses = await db
      .select({
        id: vendorTechnicalResponses.id,
      })
      .from(vendorTechnicalResponses)
      .where(
        eq(vendorTechnicalResponses.responseId, responseId)
      )

    if (technicalResponses.length === 0) {
      return { files: [], error: null }
    }

    // Extract the IDs from the result
    const technicalResponseIds = technicalResponses.map(tr => tr.id)

    // Then get attachments where technicalResponseId matches any of the IDs we found
    const files = await db
      .select({
        id: vendorResponseAttachments.id,
        fileName: vendorResponseAttachments.fileName,
        filePath: vendorResponseAttachments.filePath,
        uploadedAt: vendorResponseAttachments.uploadedAt,
        fileType: vendorResponseAttachments.fileType,
        attachmentType: vendorResponseAttachments.attachmentType,
        description: vendorResponseAttachments.description,
      })
      .from(vendorResponseAttachments)
      .where(
        inArray(vendorResponseAttachments.technicalResponseId, technicalResponseIds)
      )
      .orderBy(vendorResponseAttachments.uploadedAt)

    return { files, error: null }
  } catch (error) {
    return { files: [], error: 'Failed to fetch TBE submitted files' }
  }
}



export async function getTbeFilesForVendor(rfqId: number, vendorId: number) {
  try {
    // Step 1: Get responseId from vendor_responses table
    const response = await db
      .select({
        id: vendorResponses.id,
      })
      .from(vendorResponses)
      .where(
        and(
          eq(vendorResponses.rfqId, rfqId),
          eq(vendorResponses.vendorId, vendorId)
        )
      )
      .limit(1);

    if (!response || response.length === 0) {
      return { files: [], error: 'No vendor response found' };
    }

    const responseId = response[0].id;

    // Step 2: Get the technical response IDs
    const technicalResponses = await db
      .select({
        id: vendorTechnicalResponses.id,
      })
      .from(vendorTechnicalResponses)
      .where(
        eq(vendorTechnicalResponses.responseId, responseId)
      );

    if (technicalResponses.length === 0) {
      return { files: [], error: null };
    }

    // Extract the IDs from the result
    const technicalResponseIds = technicalResponses.map(tr => tr.id);

    // Step 3: Get attachments where technicalResponseId matches any of the IDs
    const files = await db
      .select({
        id: vendorResponseAttachments.id,
        fileName: vendorResponseAttachments.fileName,
        filePath: vendorResponseAttachments.filePath,
        uploadedAt: vendorResponseAttachments.uploadedAt,
        fileType: vendorResponseAttachments.fileType,
        attachmentType: vendorResponseAttachments.attachmentType,
        description: vendorResponseAttachments.description,
      })
      .from(vendorResponseAttachments)
      .where(
        inArray(vendorResponseAttachments.technicalResponseId, technicalResponseIds)
      )
      .orderBy(vendorResponseAttachments.uploadedAt);

    return { files, error: null };
  } catch (error) {
    return { files: [], error: 'Failed to fetch vendor files' };
  }
}

export async function getAllTBE(input: GetTBESchema) {
  return unstable_cache(
    async () => {
      // 1) 페이징
      const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10)
      const limit = input.perPage ?? 10

      // 2) 고급 필터
      const advancedWhere = filterColumns({
        table: vendorTbeView,
        filters: input.filters ?? [],
        joinOperator: input.joinOperator ?? "and",
      })

      // 3) 글로벌 검색
      let globalWhere
      if (input.search) {
        const s = `%${input.search}%`
        globalWhere = or(
          sql`${vendorTbeView.vendorName} ILIKE ${s}`,
          sql`${vendorTbeView.vendorCode} ILIKE ${s}`,
          sql`${vendorTbeView.email} ILIKE ${s}`,
          sql`${vendorTbeView.rfqCode} ILIKE ${s}`,
          sql`${vendorTbeView.projectCode} ILIKE ${s}`,
          sql`${vendorTbeView.projectName} ILIKE ${s}`
        )
      }

      // 4) REJECTED 아니거나 NULL
      const notRejected = or(
        ne(vendorTbeView.rfqVendorStatus, "REJECTED"),
        isNull(vendorTbeView.rfqVendorStatus)
      )

      const finalWhere = and(
        notRejected,
        advancedWhere,
        globalWhere
      )

      // 6) 정렬
      const orderBy = input.sort?.length
        ? input.sort.map((s) => {
          const col = (vendorTbeView as any)[s.id]
          return s.desc ? desc(col) : asc(col)
        })
        : [desc(vendorTbeView.rfqId), asc(vendorTbeView.vendorId)] // Default sort by newest RFQ first

      // 7) 메인 SELECT
      const [rows, total] = await db.transaction(async (tx) => {
        const data = await tx
          .select({
            // 원하는 컬럼들
            id: vendorTbeView.vendorId,
            tbeId: vendorTbeView.tbeId,
            vendorId: vendorTbeView.vendorId,
            vendorName: vendorTbeView.vendorName,
            vendorCode: vendorTbeView.vendorCode,
            address: vendorTbeView.address,
            country: vendorTbeView.country,
            email: vendorTbeView.email,
            website: vendorTbeView.website,
            vendorStatus: vendorTbeView.vendorStatus,

            rfqId: vendorTbeView.rfqId,
            rfqCode: vendorTbeView.rfqCode,
            projectCode: vendorTbeView.projectCode,
            projectName: vendorTbeView.projectName,
            description: vendorTbeView.description,
            dueDate: vendorTbeView.dueDate,

            rfqVendorStatus: vendorTbeView.rfqVendorStatus,
            rfqVendorUpdated: vendorTbeView.rfqVendorUpdated,

            technicalResponseStatus:vendorTbeView.technicalResponseStatus,
            tbeResult: vendorTbeView.tbeResult,

            tbeNote: vendorTbeView.tbeNote,
            tbeUpdated: vendorTbeView.tbeUpdated,
          })
          .from(vendorTbeView)
          .where(finalWhere)
          .orderBy(...orderBy)
          .offset(offset)
          .limit(limit)

        const [{ count }] = await tx
          .select({ count: sql<number>`count(*)`.as("count") })
          .from(vendorTbeView)
          .where(finalWhere)

        return [data, Number(count)]
      })

      if (!rows.length) {
        return { data: [], pageCount: 0 }
      }

      // 8) Get distinct rfqIds and vendorIds - filter out nulls
      const distinctVendorIds = [...new Set(rows.map((r) => r.vendorId).filter(Boolean))] as number[];
      const distinctRfqIds = [...new Set(rows.map((r) => r.rfqId).filter(Boolean))] as number[];

      // 9) Comments 조회
      const commentsConditions = [isNotNull(rfqComments.evaluationId)];

      // 배열이 비어있지 않을 때만 조건 추가
      if (distinctRfqIds.length > 0) {
        commentsConditions.push(inArray(rfqComments.rfqId, distinctRfqIds));
      }

      if (distinctVendorIds.length > 0) {
        commentsConditions.push(inArray(rfqComments.vendorId, distinctVendorIds));
      }

      const commAll = await db
        .select({
          id: rfqComments.id,
          commentText: rfqComments.commentText,
          vendorId: rfqComments.vendorId,
          rfqId: rfqComments.rfqId,
          evaluationId: rfqComments.evaluationId,
          createdAt: rfqComments.createdAt,
          commentedBy: rfqComments.commentedBy,
          evalType: rfqEvaluations.evalType,
        })
        .from(rfqComments)
        .innerJoin(
          rfqEvaluations,
          and(
            eq(rfqEvaluations.id, rfqComments.evaluationId),
            eq(rfqEvaluations.evalType, "TBE")
          )
        )
        .where(and(...commentsConditions));

      // 9-A) Create a composite key (rfqId-vendorId) -> comments mapping
      const commByCompositeKey = new Map<string, any[]>()
      for (const c of commAll) {
        if (!c.rfqId || !c.vendorId) continue;

        const compositeKey = `${c.rfqId}-${c.vendorId}`;
        if (!commByCompositeKey.has(compositeKey)) {
          commByCompositeKey.set(compositeKey, [])
        }
        commByCompositeKey.get(compositeKey)!.push({
          id: c.id,
          commentText: c.commentText,
          vendorId: c.vendorId,
          evaluationId: c.evaluationId,
          createdAt: c.createdAt,
          commentedBy: c.commentedBy,
        })
      }

      // 10) Responses 조회
      const responsesAll = await db
        .select({
          id: vendorResponses.id,
          rfqId: vendorResponses.rfqId,
          vendorId: vendorResponses.vendorId
        })
        .from(vendorResponses)
        .where(
          and(
            inArray(vendorResponses.rfqId, distinctRfqIds),
            inArray(vendorResponses.vendorId, distinctVendorIds)
          )
        );

      // Group responses by rfqId-vendorId composite key
      const responsesByCompositeKey = new Map<string, number[]>();
      for (const resp of responsesAll) {
        const compositeKey = `${resp.rfqId}-${resp.vendorId}`;
        if (!responsesByCompositeKey.has(compositeKey)) {
          responsesByCompositeKey.set(compositeKey, []);
        }
        responsesByCompositeKey.get(compositeKey)!.push(resp.id);
      }

      // Get all responseIds
      const allResponseIds = responsesAll.map(r => r.id);

      // 11) Get technicalResponses for these responseIds
      const technicalResponsesAll = await db
        .select({
          id: vendorTechnicalResponses.id,
          responseId: vendorTechnicalResponses.responseId
        })
        .from(vendorTechnicalResponses)
        .where(inArray(vendorTechnicalResponses.responseId, allResponseIds));

      // Create mapping from responseId to technicalResponseIds
      const technicalResponseIdsByResponseId = new Map<number, number[]>();
      for (const tr of technicalResponsesAll) {
        if (!technicalResponseIdsByResponseId.has(tr.responseId)) {
          technicalResponseIdsByResponseId.set(tr.responseId, []);
        }
        technicalResponseIdsByResponseId.get(tr.responseId)!.push(tr.id);
      }

      // Get all technicalResponseIds
      const allTechnicalResponseIds = technicalResponsesAll.map(tr => tr.id);

      // 12) Get attachments for these technicalResponseIds
      const filesAll = await db
        .select({
          id: vendorResponseAttachments.id,
          fileName: vendorResponseAttachments.fileName,
          filePath: vendorResponseAttachments.filePath,
          technicalResponseId: vendorResponseAttachments.technicalResponseId,
          fileType: vendorResponseAttachments.fileType,
          attachmentType: vendorResponseAttachments.attachmentType,
          description: vendorResponseAttachments.description,
          uploadedAt: vendorResponseAttachments.uploadedAt,
          uploadedBy: vendorResponseAttachments.uploadedBy
        })
        .from(vendorResponseAttachments)
        .where(
          and(
            inArray(vendorResponseAttachments.technicalResponseId, allTechnicalResponseIds),
            isNotNull(vendorResponseAttachments.technicalResponseId)
          )
        );

      // Create mapping from technicalResponseId to attachments
      const filesByTechnicalResponseId = new Map<number, any[]>();
      for (const file of filesAll) {
        if (file.technicalResponseId === null) continue;

        if (!filesByTechnicalResponseId.has(file.technicalResponseId)) {
          filesByTechnicalResponseId.set(file.technicalResponseId, []);
        }
        filesByTechnicalResponseId.get(file.technicalResponseId)!.push({
          id: file.id,
          fileName: file.fileName,
          filePath: file.filePath,
          fileType: file.fileType,
          attachmentType: file.attachmentType,
          description: file.description,
          uploadedAt: file.uploadedAt,
          uploadedBy: file.uploadedBy
        });
      }

      // 13) Create the final filesByCompositeKey map
      const filesByCompositeKey = new Map<string, any[]>();

      for (const [compositeKey, responseIds] of responsesByCompositeKey.entries()) {
        filesByCompositeKey.set(compositeKey, []);

        for (const responseId of responseIds) {
          const technicalResponseIds = technicalResponseIdsByResponseId.get(responseId) || [];

          for (const technicalResponseId of technicalResponseIds) {
            const files = filesByTechnicalResponseId.get(technicalResponseId) || [];
            filesByCompositeKey.get(compositeKey)!.push(...files);
          }
        }
      }

      // 14) 최종 합치기
      const final = rows.map((row) => {
        const compositeKey = `${row.rfqId}-${row.vendorId}`;

        return {
          ...row,
          dueDate: row.dueDate ? new Date(row.dueDate) : null,
          comments: commByCompositeKey.get(compositeKey) ?? [],
          files: filesByCompositeKey.get(compositeKey) ?? [],
        };
      })

      const pageCount = Math.ceil(total / limit)
      return { data: final, pageCount }
    },
    [JSON.stringify(input)],
    {
      revalidate: 3600,
      tags: ["tbe"],
    }
  )()
}


export async function getCBE(input: GetCBESchema, rfqId: number) {
  return unstable_cache(
    async () => {
      // [1] 페이징
      const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10);
      const limit = input.perPage ?? 10;

      // [2] 고급 필터
      const advancedWhere = filterColumns({
        table: vendorResponseCBEView,
        filters: input.filters ?? [],
        joinOperator: input.joinOperator ?? "and",
      });

      // [3] 글로벌 검색
      let globalWhere;
      if (input.search) {
        const s = `%${input.search}%`;
        globalWhere = or(
          sql`${vendorResponseCBEView.vendorName} ILIKE ${s}`,
          sql`${vendorResponseCBEView.vendorCode} ILIKE ${s}`,
          sql`${vendorResponseCBEView.rfqCode} ILIKE ${s}`,
          sql`${vendorResponseCBEView.totalPrice}::text ILIKE ${s}`
        );
      }

      // [4] DECLINED 상태 제외 (거절된 업체는 표시하지 않음)
      const notDeclined = ne(vendorResponseCBEView.responseStatus, "DECLINED");

      // [5] 최종 where 조건
      const finalWhere = and(
        eq(vendorResponseCBEView.rfqId, rfqId),
        notDeclined,
        advancedWhere ?? undefined,
        globalWhere ?? undefined
      );

      // [6] 정렬
      const orderBy = input.sort?.length
        ? input.sort.map((s) => {
            // vendorResponseCBEView 컬럼 중 정렬 대상이 되는 것만 매핑
            const col = (vendorResponseCBEView as any)[s.id];
            return s.desc ? desc(col) : asc(col);
          })
        : [asc(vendorResponseCBEView.vendorName)]; // 기본 정렬은 벤더명

      // [7] 메인 SELECT
      const [rows, total] = await db.transaction(async (tx) => {
        const data = await tx
          .select({
            // 기본 식별 정보
            responseId: vendorResponseCBEView.responseId,
            vendorId: vendorResponseCBEView.vendorId,
            rfqId: vendorResponseCBEView.rfqId,
            
            // 협력업체 정보
            vendorName: vendorResponseCBEView.vendorName,
            vendorCode: vendorResponseCBEView.vendorCode,
            vendorStatus: vendorResponseCBEView.vendorStatus,
            
            // RFQ 정보
            rfqCode: vendorResponseCBEView.rfqCode,
            rfqDescription: vendorResponseCBEView.rfqDescription,
            rfqDueDate: vendorResponseCBEView.rfqDueDate,
            rfqStatus: vendorResponseCBEView.rfqStatus,

            // 프로젝트 정보
            projectId: vendorResponseCBEView.projectId,
            projectCode: vendorResponseCBEView.projectCode,
            projectName: vendorResponseCBEView.projectName,
            
            // 응답 상태 정보
            responseStatus: vendorResponseCBEView.responseStatus,
            responseNotes: vendorResponseCBEView.notes,
            respondedAt: vendorResponseCBEView.respondedAt,
            respondedBy: vendorResponseCBEView.respondedBy,
            
            // 상업 응답 정보
            commercialResponseId: vendorResponseCBEView.commercialResponseId,
            commercialResponseStatus: vendorResponseCBEView.commercialResponseStatus,
            totalPrice: vendorResponseCBEView.totalPrice,
            currency: vendorResponseCBEView.currency,
            paymentTerms: vendorResponseCBEView.paymentTerms,
            incoterms: vendorResponseCBEView.incoterms,
            deliveryPeriod: vendorResponseCBEView.deliveryPeriod,
            warrantyPeriod: vendorResponseCBEView.warrantyPeriod,
            validityPeriod: vendorResponseCBEView.validityPeriod,
            commercialNotes: vendorResponseCBEView.commercialNotes,
            
            // 첨부파일 카운트
            attachmentCount: vendorResponseCBEView.attachmentCount,
            commercialAttachmentCount: vendorResponseCBEView.commercialAttachmentCount,
            technicalAttachmentCount: vendorResponseCBEView.technicalAttachmentCount,
          })
          .from(vendorResponseCBEView)
          .where(finalWhere)
          .orderBy(...orderBy)
          .offset(offset)
          .limit(limit);

        const [{ count }] = await tx
          .select({ count: sql<number>`count(*)`.as("count") })
          .from(vendorResponseCBEView)
          .where(finalWhere);

        return [data, Number(count)];
      });

      if (!rows.length) {
        return { data: [], pageCount: 0, total: 0 };
      }

      // [8] 협력업체 ID 목록 추출
      const distinctVendorIds = [...new Set(rows.map((r) => r.vendorId))];
      const distinctResponseIds = [...new Set(rows.map((r) => r.responseId))];
      const distinctCommercialResponseIds = [...new Set(rows.filter(r => r.commercialResponseId).map((r) => r.commercialResponseId!))];

      // [9] CBE 평가 관련 코멘트 조회
      const commentsAll = await db
        .select({
          id: rfqComments.id,
          commentText: rfqComments.commentText,
          vendorId: rfqComments.vendorId,
          cbeId: rfqComments.cbeId,
          createdAt: rfqComments.createdAt,
          commentedBy: rfqComments.commentedBy,
        })
        .from(rfqComments)
        .innerJoin(
          vendorResponses,
          eq(vendorResponses.id, rfqComments.cbeId)
        )
        .where(
          and(
            isNotNull(rfqComments.cbeId),
            eq(rfqComments.rfqId, rfqId),
            inArray(rfqComments.vendorId, distinctVendorIds)
          )
        );

      // vendorId별 코멘트 그룹화
      const commentsByVendorId = new Map<number, any[]>();
      for (const comment of commentsAll) {
        const vendorId = comment.vendorId!;
        if (!commentsByVendorId.has(vendorId)) {
          commentsByVendorId.set(vendorId, []);
        }
        commentsByVendorId.get(vendorId)!.push({
          id: comment.id,
          commentText: comment.commentText,
          vendorId: comment.vendorId,
          cbeId: comment.cbeId,
          createdAt: comment.createdAt,
          commentedBy: comment.commentedBy,
        });
      }

      // [10] 첨부 파일 조회 - 일반 응답 첨부파일
      const responseAttachments = await db
        .select({
          id: vendorResponseAttachments.id,
          fileName: vendorResponseAttachments.fileName,
          filePath: vendorResponseAttachments.filePath,
          responseId: vendorResponseAttachments.responseId,
          fileType: vendorResponseAttachments.fileType,
          attachmentType: vendorResponseAttachments.attachmentType,
          description: vendorResponseAttachments.description,
          uploadedAt: vendorResponseAttachments.uploadedAt,
          uploadedBy: vendorResponseAttachments.uploadedBy,
        })
        .from(vendorResponseAttachments)
        .where(
          and(
            inArray(vendorResponseAttachments.responseId, distinctResponseIds),
            isNotNull(vendorResponseAttachments.responseId)
          )
        );

      // [11] 첨부 파일 조회 - 상업 응답 첨부파일
      const commercialResponseAttachments = await db
        .select({
          id: vendorResponseAttachments.id,
          fileName: vendorResponseAttachments.fileName,
          filePath: vendorResponseAttachments.filePath,
          commercialResponseId: vendorResponseAttachments.commercialResponseId,
          fileType: vendorResponseAttachments.fileType,
          attachmentType: vendorResponseAttachments.attachmentType,
          description: vendorResponseAttachments.description,
          uploadedAt: vendorResponseAttachments.uploadedAt,
          uploadedBy: vendorResponseAttachments.uploadedBy,
        })
        .from(vendorResponseAttachments)
        .where(
          and(
            inArray(vendorResponseAttachments.commercialResponseId, distinctCommercialResponseIds),
            isNotNull(vendorResponseAttachments.commercialResponseId)
          )
        );

      // [12] 첨부파일 그룹화
      // responseId별 첨부파일 맵 생성
      const filesByResponseId = new Map<number, any[]>();
      for (const file of responseAttachments) {
        const responseId = file.responseId!;
        if (!filesByResponseId.has(responseId)) {
          filesByResponseId.set(responseId, []);
        }
        filesByResponseId.get(responseId)!.push({
          id: file.id,
          fileName: file.fileName,
          filePath: file.filePath,
          fileType: file.fileType,
          attachmentType: file.attachmentType,
          description: file.description,
          uploadedAt: file.uploadedAt,
          uploadedBy: file.uploadedBy,
          attachmentSource: 'response'
        });
      }

      // commercialResponseId별 첨부파일 맵 생성
      const filesByCommercialResponseId = new Map<number, any[]>();
      for (const file of commercialResponseAttachments) {
        const commercialResponseId = file.commercialResponseId!;
        if (!filesByCommercialResponseId.has(commercialResponseId)) {
          filesByCommercialResponseId.set(commercialResponseId, []);
        }
        filesByCommercialResponseId.get(commercialResponseId)!.push({
          id: file.id,
          fileName: file.fileName,
          filePath: file.filePath,
          fileType: file.fileType,
          attachmentType: file.attachmentType,
          description: file.description,
          uploadedAt: file.uploadedAt,
          uploadedBy: file.uploadedBy,
          attachmentSource: 'commercial'
        });
      }

      // [13] 최종 데이터 병합
      const final = rows.map((row) => {
        // 해당 응답의 모든 첨부파일 가져오기
        const responseFiles = filesByResponseId.get(row.responseId) || [];
        const commercialFiles = row.commercialResponseId 
          ? filesByCommercialResponseId.get(row.commercialResponseId) || []
          : [];
        
        // 모든 첨부파일 병합
        const allFiles = [...responseFiles, ...commercialFiles];
        
        return {
          ...row,
          rfqDueDate: row.rfqDueDate ? new Date(row.rfqDueDate) : null,
          respondedAt: row.respondedAt ? new Date(row.respondedAt) : null,
          comments: commentsByVendorId.get(row.vendorId) || [],
          files: allFiles,
        };
      });

      const pageCount = Math.ceil(total / limit);
      return { 
        data: final, 
        pageCount, 
        total 
      };
    },
    // 캐싱 키 & 옵션
    [`cbe-vendors-${rfqId}-${JSON.stringify(input)}`],
    {
      revalidate: 3600,
      tags: [`cbe`, `rfq-${rfqId}`],
    }
  )();
}

export async function generateNextRfqCode(): Promise<{ code: string; error?: string }> {
  try {
    // 현재 연도 가져오기
    const currentYear = new Date().getFullYear();
    
    // 현재 연도와 타입에 맞는 최신 RFQ 코드 찾기
    const latestRfqs = await db.select({ rfqCode: rfqs.rfqCode })
      .from(rfqs)
      .where(and(
        sql`SUBSTRING(${rfqs.rfqCode}, 5, 4) = ${currentYear.toString()}`,
      ))
      .orderBy(desc(rfqs.rfqCode))
      .limit(1);
    
    let sequenceNumber = 1;
    
    if (latestRfqs.length > 0 && latestRfqs[0].rfqCode) {
      // null 체크 추가 - TypeScript 오류 해결
      const latestCode = latestRfqs[0].rfqCode;
      const matches = latestCode.match(/[A-Z]+-\d{4}-(\d{3})/);
      
      if (matches && matches[1]) {
        sequenceNumber = parseInt(matches[1], 10) + 1;
      }
    }
    
    // 새로운 RFQ 코드 포맷팅
    const newCode = `RFQ-${currentYear}-${String(sequenceNumber).padStart(3, '0')}`;
    
    return { code: newCode };
  } catch (error) {
    console.error('Error generating next RFQ code:', error);
    return { code: "", error: '코드 생성에 실패했습니다' };
  }
}

interface SaveTbeResultParams {
  id: number       // id from the rfq_evaluations table
  vendorId: number // vendorId from the rfq_evaluations table
  result: string   // The selected evaluation result
  notes: string    // The evaluation notes
}

export async function saveTbeResult({
  id,
  vendorId,
  result,
  notes,
}: SaveTbeResultParams) {
  try {
    // Check if we have all required data
    if (!id || !vendorId || !result) {
      return {
        success: false,
        message: "Missing required data for evaluation update",
      }
    }

    // Update the record in the database
    await db
      .update(rfqEvaluations)
      .set({
        result: result,
        notes: notes,
        updatedAt: new Date(),
      })
      .where(
        and(
          eq(rfqEvaluations.id, id),
          eq(rfqEvaluations.vendorId, vendorId),
          eq(rfqEvaluations.evalType, "TBE")
        )
      )

    // Revalidate the tbe-vendors tag to refresh the data
    revalidateTag("tbe-vendors")
    revalidateTag("all-tbe-vendors")
    

    return {
      success: true,
      message: "TBE evaluation updated successfully",
    }
  } catch (error) {
    console.error("Failed to update TBE evaluation:", error)
    
    return {
      success: false,
      message: error instanceof Error ? error.message : "An unknown error occurred",
    }
  }
}


export async function createCbeEvaluation(formData: FormData) {
  try {
    // 폼 데이터 추출
    const rfqId = Number(formData.get("rfqId"))
    const vendorIds = formData.getAll("vendorIds[]").map(id => Number(id))
    const evaluatedBy = formData.get("evaluatedBy") ? Number(formData.get("evaluatedBy")) : null
    

    const headersList = await headers();
    const host = headersList.get('host') || 'localhost:3000';

    // 기본 CBE 데이터 추출
    const rawData = {
      rfqId,
      paymentTerms: formData.get("paymentTerms") as string,
      incoterms: formData.get("incoterms") as string,
      deliverySchedule: formData.get("deliverySchedule") as string,
      notes: formData.get("notes") as string,
      // 단일 협력업체 처리 시 사용할 vendorId (여러 협력업체 처리에선 사용하지 않음)
      // vendorId: vendorIds[0] || 0,
    }

    // zod 스키마 유효성 검사 (vendorId는 더미로 채워 검증하고 실제로는 배열로 처리)
    const validationResult = createCbeEvaluationSchema.safeParse(rawData)
    if (!validationResult.success) {
      const errors = validationResult.error.format()
      console.error("Validation errors:", errors)
      return { error: "입력 데이터가 유효하지 않습니다." }
    }

    const validData = validationResult.data

    // RFQ 정보 조회
    const [rfqInfo] = await db
      .select({
        rfqCode: rfqsView.rfqCode,
        projectCode: rfqsView.projectCode,
        projectName: rfqsView.projectName,
        dueDate: rfqsView.dueDate,
        description: rfqsView.description,
      })
      .from(rfqsView)
      .where(eq(rfqsView.id, rfqId))

    if (!rfqInfo) {
      return { error: "RFQ 정보를 찾을 수 없습니다." }
    }

    // 파일 처리 준비
    const files = formData.getAll("files") as File[]
    const hasFiles = files && files.length > 0 && files[0].size > 0
    
    // 파일 저장을 위한 디렉토리 생성 (파일이 있는 경우에만)
    let uploadDir = ""
    if (hasFiles) {
      uploadDir = path.join(process.cwd(), "public", "rfq", String(rfqId))
      try {
        await fs.mkdir(uploadDir, { recursive: true })
      } catch (err) {
        console.error("디렉토리 생성 실패:", err)
        return { error: "파일 업로드를 위한 디렉토리 생성에 실패했습니다." }
      }
    }

    // 첨부 파일 정보를 저장할 배열
    const attachments: { filename: string; path: string }[] = []

    // 파일이 있는 경우, 파일을 저장하고 첨부 파일 정보 준비
    if (hasFiles) {
      for (const file of files) {
        if (file.size > 0) {
          const originalFilename = file.name
          const fileExtension = path.extname(originalFilename)
          const timestamp = new Date().getTime()
          const safeFilename = `cbe-${rfqId}-${timestamp}${fileExtension}`
          const filePath = path.join("rfq", String(rfqId), safeFilename)
          const fullPath = path.join(process.cwd(), "public", filePath)
          
          try {
            // File을 ArrayBuffer로 변환하여 파일 시스템에 저장
            const arrayBuffer = await file.arrayBuffer()
            const buffer = Buffer.from(arrayBuffer)
            await fs.writeFile(fullPath, buffer)
            
            // 첨부 파일 정보 추가
            attachments.push({
              filename: originalFilename,
              path: fullPath, // 이메일 첨부를 위한 전체 경로
            })
          } catch (err) {
            console.error(`파일 저장 실패:`, err)
            // 파일 저장 실패를 기록하지만 전체 프로세스는 계속 진행
          }
        }
      }
    }

    // 각 벤더별로 CBE 평가 레코드 생성 및 알림 전송
    const createdCbeIds: number[] = []
    const failedVendors: { id: number, reason: string }[] = []
  
    for (const vendorId of vendorIds) {
      try {
        // 협력업체 정보 조회 (이메일 포함)
        const [vendorInfo] = await db
          .select({
            id: vendors.id,
            name: vendors.vendorName,
            vendorCode: vendors.vendorCode,
            email: vendors.email, // 협력업체 자체 이메일 추가
            representativeEmail: vendors.representativeEmail, // 협력업체 대표자 이메일 추가
          })
          .from(vendors)
          .where(eq(vendors.id, vendorId))

        if (!vendorInfo) {
          failedVendors.push({ id: vendorId, reason: "협력업체 정보를 찾을 수 없습니다." })
          continue
        }

        // 기존 협력업체 응답 레코드 찾기
        const existingResponse = await db
          .select({ id: vendorResponses.id })
          .from(vendorResponses)
          .where(
            and(
              eq(vendorResponses.rfqId, rfqId),
              eq(vendorResponses.vendorId, vendorId)
            )
          )
          .limit(1)

        if (existingResponse.length === 0) {
          console.error(`협력업체 ID ${vendorId}에 대한 응답 레코드가 존재하지 않습니다.`)
          failedVendors.push({ id: vendorId, reason: "협력업체 응답 레코드를 찾을 수 없습니다" })
          continue // 다음 벤더로 넘어감
        }

        // 1. CBE 평가 레코드 생성
        const [newCbeEvaluation] = await db
          .insert(cbeEvaluations)
          .values({
            rfqId,
            vendorId,
            evaluatedBy,
            result: "PENDING", // 초기 상태는 PENDING으로 설정
            totalCost: 0, // 초기값은 0으로 설정
            currency: "USD", // 기본 통화 설정
            paymentTerms: validData.paymentTerms || null,
            incoterms: validData.incoterms || null,
            deliverySchedule: validData.deliverySchedule || null,
            notes: validData.notes || null,
          })
          .returning({ id: cbeEvaluations.id })

        if (!newCbeEvaluation?.id) {
          failedVendors.push({ id: vendorId, reason: "CBE 평가 생성 실패" })
          continue
        }

        // 2. 상업 응답 레코드 생성
        const [newCbeResponse] = await db
          .insert(vendorCommercialResponses)
          .values({
            responseId: existingResponse[0].id,
            responseStatus: "PENDING",
            currency: "USD",
            paymentTerms: validData.paymentTerms || null,
            incoterms: validData.incoterms || null,
            deliveryPeriod: validData.deliverySchedule || null,
          })
          .returning({ id: vendorCommercialResponses.id })

        if (!newCbeResponse?.id) {
          failedVendors.push({ id: vendorId, reason: "상업 응답 생성 실패" })
          continue
        }

        createdCbeIds.push(newCbeEvaluation.id)

        // 3. 첨부 파일이 있는 경우, 데이터베이스에 첨부 파일 레코드 생성
        if (hasFiles) {
          for (let i = 0; i < attachments.length; i++) {
            const attachment = attachments[i]
            
            await db.insert(rfqAttachments).values({
              rfqId,
              vendorId,
              fileName: attachment.filename,
              filePath: `/${path.relative(path.join(process.cwd(), "public"), attachment.path)}`, // URL 경로를 위해 public 기준 상대 경로로 저장
              cbeId: newCbeEvaluation.id,
            })
          }
        }

        // 4. 협력업체 연락처 조회
        const contacts = await db
          .select({
            contactName: vendorContacts.contactName,
            contactEmail: vendorContacts.contactEmail,
            isPrimary: vendorContacts.isPrimary,
          })
          .from(vendorContacts)
          .where(eq(vendorContacts.vendorId, vendorId))

        // 5. 모든 이메일 주소 수집 및 중복 제거
        const allEmails = new Set<string>()
        
        // 연락처 이메일 추가
        contacts.forEach(contact => {
          if (contact.contactEmail) {
            allEmails.add(contact.contactEmail.trim().toLowerCase())
          }
        })
        
        // 협력업체 자체 이메일 추가 (있는 경우에만)
        if (vendorInfo.email) {
          allEmails.add(vendorInfo.email.trim().toLowerCase())
        }
        
        // 협력업체 대표자 이메일 추가 (있는 경우에만)
        if (vendorInfo.representativeEmail) {
          allEmails.add(vendorInfo.representativeEmail.trim().toLowerCase())
        }
        
        // 중복이 제거된 이메일 주소 배열로 변환
        const uniqueEmails = Array.from(allEmails)
        
        if (uniqueEmails.length === 0) {
          console.warn(`협력업체 ID ${vendorId}에 등록된 이메일 주소가 없습니다.`)
        } else {
          console.log(`협력업체 ID ${vendorId}에 대해 ${uniqueEmails.length}개의 고유 이메일 주소로 알림을 전송합니다.`)
          
          // 이메일 발송에 필요한 공통 데이터 준비
          const emailData = {
            rfqId,
            cbeId: newCbeEvaluation.id,
            vendorId,
            rfqCode: rfqInfo.rfqCode,
            projectCode: rfqInfo.projectCode,
            projectName: rfqInfo.projectName,
            dueDate: rfqInfo.dueDate,
            description: rfqInfo.description,
            vendorName: vendorInfo.name,
            vendorCode: vendorInfo.vendorCode,
            paymentTerms: validData.paymentTerms,
            incoterms: validData.incoterms,
            deliverySchedule: validData.deliverySchedule,
            notes: validData.notes,
            loginUrl: `http://${host}/en/partners/cbe`
          }

          // 각 고유 이메일 주소로 이메일 발송
          for (const email of uniqueEmails) {
            try {
              // 연락처 이름 찾기 (이메일과 일치하는 연락처가 있으면 사용, 없으면 '벤더명 담당자'로 대체)
              const contact = contacts.find(c => 
                c.contactEmail && c.contactEmail.toLowerCase() === email.toLowerCase()
              )
              const contactName = contact?.contactName || `${vendorInfo.name} 담당자`
              
              await sendEmail({
                to: email,
                subject: `[RFQ ${rfqInfo.rfqCode}] 상업 입찰 평가 (CBE) 알림`,
                template: "cbe-invitation",
                context: {
                  language: "ko", // 또는 다국어 처리를 위한 설정
                  contactName,
                  ...emailData,
                },
                attachments: attachments,
              })
              console.log(`이메일 전송 성공: ${email}`)
            } catch (emailErr) {
              console.error(`이메일 전송 실패 (${email}):`, emailErr)
            }
          }
        }

      } catch (err) {
        console.error(`협력업체 ID ${vendorId}의 CBE 생성 실패:`, err)
        failedVendors.push({ id: vendorId, reason: "예기치 않은 오류" })
      }
    }

    // UI 업데이트를 위한 경로 재검증
    revalidatePath(`/rfq/${rfqId}`)
    revalidateTag(`cbe-vendors-${rfqId}`)
    revalidateTag("all-cbe-vendors")
    
    // 결과 반환
    if (createdCbeIds.length === 0) {
      return { error: "어떤 벤더에 대해서도 CBE 평가를 생성하지 못했습니다." }
    }
    
    return { 
      success: true, 
      cbeIds: createdCbeIds,
      totalCreated: createdCbeIds.length,
      totalFailed: failedVendors.length,
      failedVendors: failedVendors.length > 0 ? failedVendors : undefined
    }
    
  } catch (error) {
    console.error("CBE 평가 생성 중 오류 발생:", error)
    return { error: "예상치 못한 오류가 발생했습니다." }
  }
}

export async function getCBEbyVendorId(input: GetCBESchema, vendorId: number) {
  return unstable_cache(
    async () => {
      // [1] 페이징
      const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10);
      const limit = input.perPage ?? 10;

      // [2] 고급 필터
      const advancedWhere = filterColumns({
        table: vendorResponseCBEView,
        filters: input.filters ?? [],
        joinOperator: input.joinOperator ?? "and",
      });

      // [3] 글로벌 검색
      let globalWhere;
      if (input.search) {
        const s = `%${input.search}%`;
        globalWhere = or(
          sql`${vendorResponseCBEView.rfqCode} ILIKE ${s}`,
          sql`${vendorResponseCBEView.projectCode} ILIKE ${s}`,
          sql`${vendorResponseCBEView.projectName} ILIKE ${s}`,
          sql`${vendorResponseCBEView.totalPrice}::text ILIKE ${s}`
        );
      }

      // [4] DECLINED 상태 제외 (거절된 응답은 표시하지 않음)
      // const notDeclined = ne(vendorResponseCBEView.responseStatus, "DECLINED");

      // [5] 최종 where 조건
      const finalWhere = and(
        eq(vendorResponseCBEView.vendorId, vendorId), // vendorId로 필터링
        isNotNull(vendorResponseCBEView.commercialCreatedAt),
        // notDeclined,
        advancedWhere ?? undefined,
        globalWhere ?? undefined
      );

      // [6] 정렬
      const orderBy = input.sort?.length
        ? input.sort.map((s) => {
            // vendorResponseCBEView 컬럼 중 정렬 대상이 되는 것만 매핑
            const col = (vendorResponseCBEView as any)[s.id];
            return s.desc ? desc(col) : asc(col);
          })
        : [desc(vendorResponseCBEView.rfqDueDate)]; // 기본 정렬은 RFQ 마감일 내림차순

      // [7] 메인 SELECT
      const [rows, total] = await db.transaction(async (tx) => {
        const data = await tx
          .select({
            // 기본 식별 정보
            responseId: vendorResponseCBEView.responseId,
            vendorId: vendorResponseCBEView.vendorId,
            rfqId: vendorResponseCBEView.rfqId,
            
            // 협력업체 정보
            vendorName: vendorResponseCBEView.vendorName,
            vendorCode: vendorResponseCBEView.vendorCode,
            vendorStatus: vendorResponseCBEView.vendorStatus,
            
            // RFQ 정보
            rfqCode: vendorResponseCBEView.rfqCode,
            rfqDescription: vendorResponseCBEView.rfqDescription,
            rfqDueDate: vendorResponseCBEView.rfqDueDate,
            rfqStatus: vendorResponseCBEView.rfqStatus,
            
            // 프로젝트 정보
            projectId: vendorResponseCBEView.projectId,
            projectCode: vendorResponseCBEView.projectCode,
            projectName: vendorResponseCBEView.projectName,
            
            // 응답 상태 정보
            responseStatus: vendorResponseCBEView.responseStatus,
            responseNotes: vendorResponseCBEView.notes,
            respondedAt: vendorResponseCBEView.respondedAt,
            respondedBy: vendorResponseCBEView.respondedBy,
            
            // 상업 응답 정보
            commercialResponseId: vendorResponseCBEView.commercialResponseId,
            commercialResponseStatus: vendorResponseCBEView.commercialResponseStatus,
            totalPrice: vendorResponseCBEView.totalPrice,
            currency: vendorResponseCBEView.currency,
            paymentTerms: vendorResponseCBEView.paymentTerms,
            incoterms: vendorResponseCBEView.incoterms,
            deliveryPeriod: vendorResponseCBEView.deliveryPeriod,
            warrantyPeriod: vendorResponseCBEView.warrantyPeriod,
            validityPeriod: vendorResponseCBEView.validityPeriod,
            commercialNotes: vendorResponseCBEView.commercialNotes,
            
            // 첨부파일 카운트
            attachmentCount: vendorResponseCBEView.attachmentCount,
            commercialAttachmentCount: vendorResponseCBEView.commercialAttachmentCount,
            technicalAttachmentCount: vendorResponseCBEView.technicalAttachmentCount,
          })
          .from(vendorResponseCBEView)
          .where(finalWhere)
          .orderBy(...orderBy)
          .offset(offset)
          .limit(limit);

        const [{ count }] = await tx
          .select({ count: sql<number>`count(*)`.as("count") })
          .from(vendorResponseCBEView)
          .where(finalWhere);

        return [data, Number(count)];
      });

      if (!rows.length) {
        return { data: [], pageCount: 0, total: 0 };
      }

      // [8] RFQ ID 목록 추출
      const distinctRfqIds = [...new Set(rows.map((r) => r.rfqId))];
      const distinctResponseIds = [...new Set(rows.map((r) => r.responseId))];
      const distinctCommercialResponseIds = [...new Set(rows.filter(r => r.commercialResponseId).map((r) => r.commercialResponseId!))];

      // [9] CBE 평가 관련 코멘트 조회
      const commentsAll = await db
        .select({
          id: rfqComments.id,
          commentText: rfqComments.commentText,
          rfqId: rfqComments.rfqId,
          cbeId: rfqComments.cbeId,
          createdAt: rfqComments.createdAt,
          commentedBy: rfqComments.commentedBy,
        })
        .from(rfqComments)
        .innerJoin(
          vendorResponses,
          eq(vendorResponses.id, rfqComments.cbeId)
        )
        .where(
          and(
            isNotNull(rfqComments.cbeId),
            eq(rfqComments.vendorId, vendorId),
            inArray(rfqComments.rfqId, distinctRfqIds)
          )
        );

      // rfqId별 코멘트 그룹화
      const commentsByRfqId = new Map<number, any[]>();
      for (const comment of commentsAll) {
        const rfqId = comment.rfqId!;
        if (!commentsByRfqId.has(rfqId)) {
          commentsByRfqId.set(rfqId, []);
        }
        commentsByRfqId.get(rfqId)!.push({
          id: comment.id,
          commentText: comment.commentText,
          rfqId: comment.rfqId,
          cbeId: comment.cbeId,
          createdAt: comment.createdAt,
          commentedBy: comment.commentedBy,
        });
      }

      // [10] 첨부 파일 조회 - 일반 응답 첨부파일
      const responseAttachments = await db
        .select({
          id: vendorResponseAttachments.id,
          fileName: vendorResponseAttachments.fileName,
          filePath: vendorResponseAttachments.filePath,
          responseId: vendorResponseAttachments.responseId,
          fileType: vendorResponseAttachments.fileType,
          attachmentType: vendorResponseAttachments.attachmentType,
          description: vendorResponseAttachments.description,
          uploadedAt: vendorResponseAttachments.uploadedAt,
          uploadedBy: vendorResponseAttachments.uploadedBy,
        })
        .from(vendorResponseAttachments)
        .where(
          and(
            inArray(vendorResponseAttachments.responseId, distinctResponseIds),
            isNotNull(vendorResponseAttachments.responseId)
          )
        );

      // [11] 첨부 파일 조회 - 상업 응답 첨부파일
      const commercialResponseAttachments = await db
        .select({
          id: vendorResponseAttachments.id,
          fileName: vendorResponseAttachments.fileName,
          filePath: vendorResponseAttachments.filePath,
          commercialResponseId: vendorResponseAttachments.commercialResponseId,
          fileType: vendorResponseAttachments.fileType,
          attachmentType: vendorResponseAttachments.attachmentType,
          description: vendorResponseAttachments.description,
          uploadedAt: vendorResponseAttachments.uploadedAt,
          uploadedBy: vendorResponseAttachments.uploadedBy,
        })
        .from(vendorResponseAttachments)
        .where(
          and(
            inArray(vendorResponseAttachments.commercialResponseId, distinctCommercialResponseIds),
            isNotNull(vendorResponseAttachments.commercialResponseId)
          )
        );

      // [12] 첨부파일 그룹화
      // responseId별 첨부파일 맵 생성
      const filesByResponseId = new Map<number, any[]>();
      for (const file of responseAttachments) {
        const responseId = file.responseId!;
        if (!filesByResponseId.has(responseId)) {
          filesByResponseId.set(responseId, []);
        }
        filesByResponseId.get(responseId)!.push({
          id: file.id,
          fileName: file.fileName,
          filePath: file.filePath,
          fileType: file.fileType,
          attachmentType: file.attachmentType,
          description: file.description,
          uploadedAt: file.uploadedAt,
          uploadedBy: file.uploadedBy,
          attachmentSource: 'response'
        });
      }

      // commercialResponseId별 첨부파일 맵 생성
      const filesByCommercialResponseId = new Map<number, any[]>();
      for (const file of commercialResponseAttachments) {
        const commercialResponseId = file.commercialResponseId!;
        if (!filesByCommercialResponseId.has(commercialResponseId)) {
          filesByCommercialResponseId.set(commercialResponseId, []);
        }
        filesByCommercialResponseId.get(commercialResponseId)!.push({
          id: file.id,
          fileName: file.fileName,
          filePath: file.filePath,
          fileType: file.fileType,
          attachmentType: file.attachmentType,
          description: file.description,
          uploadedAt: file.uploadedAt,
          uploadedBy: file.uploadedBy,
          attachmentSource: 'commercial'
        });
      }

      // [13] 최종 데이터 병합
      const final = rows.map((row) => {
        // 해당 응답의 모든 첨부파일 가져오기
        const responseFiles = filesByResponseId.get(row.responseId) || [];
        const commercialFiles = row.commercialResponseId 
          ? filesByCommercialResponseId.get(row.commercialResponseId) || []
          : [];
        
        // 모든 첨부파일 병합
        const allFiles = [...responseFiles, ...commercialFiles];
        
        return {
          ...row,
          rfqDueDate: row.rfqDueDate ? new Date(row.rfqDueDate) : null,
          respondedAt: row.respondedAt ? new Date(row.respondedAt) : null,
          comments: commentsByRfqId.get(row.rfqId) || [],
          files: allFiles,
        };
      });

      const pageCount = Math.ceil(total / limit);
      return { 
        data: final, 
        pageCount, 
        total 
      };
    },
    // 캐싱 키 & 옵션
    [`cbe-vendor-${vendorId}-${JSON.stringify(input)}`],
    {
      revalidate: 3600,
      tags: [`cbe-vendor-${vendorId}`],
    }
  )();
}

export async function fetchCbeFiles(vendorId: number, rfqId: number) {
  try {
    // 1. 먼저 해당 RFQ와 벤더에 해당하는 CBE 평가 레코드를 찾습니다.
    const cbeEval = await db
      .select({ id: cbeEvaluations.id })
      .from(cbeEvaluations)
      .where(
        and(
          eq(cbeEvaluations.rfqId, rfqId),
          eq(cbeEvaluations.vendorId, vendorId)
        )
      )
      .limit(1)

    if (!cbeEval.length) {
      return {
        files: [],
        error: "해당 RFQ와 벤더에 대한 CBE 평가를 찾을 수 없습니다."
      }
    }

    const cbeId = cbeEval[0].id

    // 2. 관련 첨부 파일을 조회합니다.
    // - commentId와 evaluationId는 null이어야 함
    // - rfqId와 vendorId가 일치해야 함
    // - cbeId가 위에서 찾은 CBE 평가 ID와 일치해야 함
    const files = await db
      .select({
        id: rfqAttachments.id,
        fileName: rfqAttachments.fileName,
        filePath: rfqAttachments.filePath,
        createdAt: rfqAttachments.createdAt
      })
      .from(rfqAttachments)
      .where(
        and(
          eq(rfqAttachments.rfqId, rfqId),
          eq(rfqAttachments.vendorId, vendorId),
          eq(rfqAttachments.cbeId, cbeId),
          isNull(rfqAttachments.commentId),
          isNull(rfqAttachments.evaluationId)
        )
      )
      .orderBy(rfqAttachments.createdAt)

    return {
      files,
      cbeId
    }
  } catch (error) {
    console.error("CBE 파일 조회 중 오류 발생:", error)
    return {
      files: [],
      error: "CBE 파일을 가져오는 중 오류가 발생했습니다."
    }
  }
}

export async function getAllCBE(input: GetCBESchema) {
  return unstable_cache(
    async () => {
      // [1] 페이징
      const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10);
      const limit = input.perPage ?? 10;

      // [2] 고급 필터
      const advancedWhere = filterColumns({
        table: vendorResponseCBEView,
        filters: input.filters ?? [],
        joinOperator: input.joinOperator ?? "and",
      });

      // [3] 글로벌 검색
      let globalWhere;
      if (input.search) {
        const s = `%${input.search}%`;
        globalWhere = or(
          sql`${vendorResponseCBEView.vendorName} ILIKE ${s}`,
          sql`${vendorResponseCBEView.vendorCode} ILIKE ${s}`,
          sql`${vendorResponseCBEView.rfqCode} ILIKE ${s}`,
          sql`${vendorResponseCBEView.projectCode} ILIKE ${s}`,
          sql`${vendorResponseCBEView.projectName} ILIKE ${s}`,
          sql`${vendorResponseCBEView.totalPrice}::text ILIKE ${s}`
        );
      }

      // [4] DECLINED 상태 제외 (거절된 업체는 표시하지 않음)
      const notDeclined = ne(vendorResponseCBEView.responseStatus, "DECLINED");

      // [6] 최종 where 조건
      const finalWhere = and(
        notDeclined,
        advancedWhere ?? undefined,
        globalWhere ?? undefined,
      );

      // [7] 정렬
      const orderBy = input.sort?.length
        ? input.sort.map((s) => {
            // vendorResponseCBEView 컬럼 중 정렬 대상이 되는 것만 매핑
            const col = (vendorResponseCBEView as any)[s.id];
            return s.desc ? desc(col) : asc(col);
          })
        : [desc(vendorResponseCBEView.rfqId), asc(vendorResponseCBEView.vendorName)]; // 기본 정렬은 최신 RFQ 먼저, 그 다음 벤더명

      // [8] 메인 SELECT
      const [rows, total] = await db.transaction(async (tx) => {
        const data = await tx
          .select({
            // 기본 식별 정보
            responseId: vendorResponseCBEView.responseId,
            vendorId: vendorResponseCBEView.vendorId,
            rfqId: vendorResponseCBEView.rfqId,
            
            // 협력업체 정보
            vendorName: vendorResponseCBEView.vendorName,
            vendorCode: vendorResponseCBEView.vendorCode,
            vendorStatus: vendorResponseCBEView.vendorStatus,
            
            // RFQ 정보
            rfqCode: vendorResponseCBEView.rfqCode,
            rfqDescription: vendorResponseCBEView.rfqDescription,
            rfqDueDate: vendorResponseCBEView.rfqDueDate,
            rfqStatus: vendorResponseCBEView.rfqStatus,
            
            // 프로젝트 정보
            projectId: vendorResponseCBEView.projectId,
            projectCode: vendorResponseCBEView.projectCode,
            projectName: vendorResponseCBEView.projectName,
            
            // 응답 상태 정보
            responseStatus: vendorResponseCBEView.responseStatus,
            responseNotes: vendorResponseCBEView.notes,
            respondedAt: vendorResponseCBEView.respondedAt,
            respondedBy: vendorResponseCBEView.respondedBy,
            
            // 상업 응답 정보
            commercialResponseId: vendorResponseCBEView.commercialResponseId,
            commercialResponseStatus: vendorResponseCBEView.commercialResponseStatus,
            totalPrice: vendorResponseCBEView.totalPrice,
            currency: vendorResponseCBEView.currency,
            paymentTerms: vendorResponseCBEView.paymentTerms,
            incoterms: vendorResponseCBEView.incoterms,
            deliveryPeriod: vendorResponseCBEView.deliveryPeriod,
            warrantyPeriod: vendorResponseCBEView.warrantyPeriod,
            validityPeriod: vendorResponseCBEView.validityPeriod,
            commercialNotes: vendorResponseCBEView.commercialNotes,
            
            // 첨부파일 카운트
            attachmentCount: vendorResponseCBEView.attachmentCount,
            commercialAttachmentCount: vendorResponseCBEView.commercialAttachmentCount,
            technicalAttachmentCount: vendorResponseCBEView.technicalAttachmentCount,
          })
          .from(vendorResponseCBEView)
          .where(finalWhere)
          .orderBy(...orderBy)
          .offset(offset)
          .limit(limit);

        const [{ count }] = await tx
          .select({ count: sql<number>`count(*)`.as("count") })
          .from(vendorResponseCBEView)
          .where(finalWhere);

        return [data, Number(count)];
      });

      if (!rows.length) {
        return { data: [], pageCount: 0, total: 0 };
      }

      // [9] 고유한 rfqIds와 vendorIds 추출 - null 필터링
      const distinctVendorIds = [...new Set(rows.map((r) => r.vendorId).filter(Boolean))] as number[];
      const distinctRfqIds = [...new Set(rows.map((r) => r.rfqId).filter(Boolean))] as number[];
      const distinctResponseIds = [...new Set(rows.map((r) => r.responseId).filter(Boolean))] as number[];
      const distinctCommercialResponseIds = [...new Set(rows.filter(r => r.commercialResponseId).map((r) => r.commercialResponseId!))];

      // [10] CBE 평가 관련 코멘트 조회
      const commentsConditions = [isNotNull(rfqComments.cbeId)];

      // 배열이 비어있지 않을 때만 조건 추가
      if (distinctRfqIds.length > 0) {
        commentsConditions.push(inArray(rfqComments.rfqId, distinctRfqIds));
      }

      if (distinctVendorIds.length > 0) {
        commentsConditions.push(inArray(rfqComments.vendorId, distinctVendorIds));
      }

      const commentsAll = await db
        .select({
          id: rfqComments.id,
          commentText: rfqComments.commentText,
          vendorId: rfqComments.vendorId,
          rfqId: rfqComments.rfqId,
          cbeId: rfqComments.cbeId,
          createdAt: rfqComments.createdAt,
          commentedBy: rfqComments.commentedBy,
        })
        .from(rfqComments)
        .innerJoin(
          vendorResponses,
          eq(vendorResponses.id, rfqComments.cbeId)
        )
        .where(and(...commentsConditions));

      // [11] 복합 키(rfqId-vendorId)별 코멘트 그룹화
      const commentsByCompositeKey = new Map<string, any[]>();
      for (const comment of commentsAll) {
        if (!comment.rfqId || !comment.vendorId) continue;

        const compositeKey = `${comment.rfqId}-${comment.vendorId}`;
        if (!commentsByCompositeKey.has(compositeKey)) {
          commentsByCompositeKey.set(compositeKey, []);
        }
        commentsByCompositeKey.get(compositeKey)!.push({
          id: comment.id,
          commentText: comment.commentText,
          vendorId: comment.vendorId,
          cbeId: comment.cbeId,
          createdAt: comment.createdAt,
          commentedBy: comment.commentedBy,
        });
      }

      // [12] 첨부 파일 조회 - 일반 응답 첨부파일
      const responseAttachments = await db
        .select({
          id: vendorResponseAttachments.id,
          fileName: vendorResponseAttachments.fileName,
          filePath: vendorResponseAttachments.filePath,
          responseId: vendorResponseAttachments.responseId,
          fileType: vendorResponseAttachments.fileType,
          attachmentType: vendorResponseAttachments.attachmentType,
          description: vendorResponseAttachments.description,
          uploadedAt: vendorResponseAttachments.uploadedAt,
          uploadedBy: vendorResponseAttachments.uploadedBy,
        })
        .from(vendorResponseAttachments)
        .where(
          and(
            inArray(vendorResponseAttachments.responseId, distinctResponseIds),
            isNotNull(vendorResponseAttachments.responseId)
          )
        );

      // [13] 첨부 파일 조회 - 상업 응답 첨부파일
      const commercialResponseAttachments = await db
        .select({
          id: vendorResponseAttachments.id,
          fileName: vendorResponseAttachments.fileName,
          filePath: vendorResponseAttachments.filePath,
          commercialResponseId: vendorResponseAttachments.commercialResponseId,
          fileType: vendorResponseAttachments.fileType,
          attachmentType: vendorResponseAttachments.attachmentType,
          description: vendorResponseAttachments.description,
          uploadedAt: vendorResponseAttachments.uploadedAt,
          uploadedBy: vendorResponseAttachments.uploadedBy,
        })
        .from(vendorResponseAttachments)
        .where(
          and(
            inArray(vendorResponseAttachments.commercialResponseId, distinctCommercialResponseIds),
            isNotNull(vendorResponseAttachments.commercialResponseId)
          )
        );

      // [14] 첨부파일 그룹화
      // responseId별 첨부파일 맵 생성
      const filesByResponseId = new Map<number, any[]>();
      for (const file of responseAttachments) {
        const responseId = file.responseId!;
        if (!filesByResponseId.has(responseId)) {
          filesByResponseId.set(responseId, []);
        }
        filesByResponseId.get(responseId)!.push({
          id: file.id,
          fileName: file.fileName,
          filePath: file.filePath,
          fileType: file.fileType,
          attachmentType: file.attachmentType,
          description: file.description,
          uploadedAt: file.uploadedAt,
          uploadedBy: file.uploadedBy,
          attachmentSource: 'response'
        });
      }

      // commercialResponseId별 첨부파일 맵 생성
      const filesByCommercialResponseId = new Map<number, any[]>();
      for (const file of commercialResponseAttachments) {
        const commercialResponseId = file.commercialResponseId!;
        if (!filesByCommercialResponseId.has(commercialResponseId)) {
          filesByCommercialResponseId.set(commercialResponseId, []);
        }
        filesByCommercialResponseId.get(commercialResponseId)!.push({
          id: file.id,
          fileName: file.fileName,
          filePath: file.filePath,
          fileType: file.fileType,
          attachmentType: file.attachmentType,
          description: file.description,
          uploadedAt: file.uploadedAt,
          uploadedBy: file.uploadedBy,
          attachmentSource: 'commercial'
        });
      }

      // [15] 복합 키(rfqId-vendorId)별 첨부파일 맵 생성
      const filesByCompositeKey = new Map<string, any[]>();
      
      // responseId -> rfqId-vendorId 매핑 생성
      const responseIdToCompositeKey = new Map<number, string>();
      for (const row of rows) {
        if (row.responseId) {
          responseIdToCompositeKey.set(row.responseId, `${row.rfqId}-${row.vendorId}`);
        }
        if (row.commercialResponseId) {
          responseIdToCompositeKey.set(row.commercialResponseId, `${row.rfqId}-${row.vendorId}`);
        }
      }
      
      // responseId별 첨부파일을 복합 키별로 그룹화
      for (const [responseId, files] of filesByResponseId.entries()) {
        const compositeKey = responseIdToCompositeKey.get(responseId);
        if (compositeKey) {
          if (!filesByCompositeKey.has(compositeKey)) {
            filesByCompositeKey.set(compositeKey, []);
          }
          filesByCompositeKey.get(compositeKey)!.push(...files);
        }
      }
      
      // commercialResponseId별 첨부파일을 복합 키별로 그룹화
      for (const [commercialResponseId, files] of filesByCommercialResponseId.entries()) {
        const compositeKey = responseIdToCompositeKey.get(commercialResponseId);
        if (compositeKey) {
          if (!filesByCompositeKey.has(compositeKey)) {
            filesByCompositeKey.set(compositeKey, []);
          }
          filesByCompositeKey.get(compositeKey)!.push(...files);
        }
      }

      // [16] 최종 데이터 병합
      const final = rows.map((row) => {
        const compositeKey = `${row.rfqId}-${row.vendorId}`;
        
        return {
          ...row,
          rfqDueDate: row.rfqDueDate ? new Date(row.rfqDueDate) : null,
          respondedAt: row.respondedAt ? new Date(row.respondedAt) : null,
          comments: commentsByCompositeKey.get(compositeKey) || [],
          files: filesByCompositeKey.get(compositeKey) || [],
        };
      });

      const pageCount = Math.ceil(total / limit);
      return { 
        data: final, 
        pageCount, 
        total 
      };
    },
    // 캐싱 키 & 옵션
    [`all-cbe-vendors-${JSON.stringify(input)}`],
    {
      revalidate: 3600,
      tags: ["all-cbe-vendors"],
    }
  )();
}