// lib/rfq/service.ts
'use server'

import { revalidatePath, unstable_cache, unstable_noStore } from "next/cache";
import db from "@/db/db";
import { avlVendorInfo, paymentTerms, incoterms, rfqLastVendorQuotationItems, rfqLastVendorAttachments, rfqLastVendorResponses, RfqsLastView, rfqLastAttachmentRevisions, rfqLastAttachments, rfqsLast, rfqsLastView, users, rfqPrItems, prItemsLastView, vendors, rfqLastDetails, rfqLastVendorResponseHistory, rfqLastDetailsView, vendorContacts, projects, basicContract, basicContractTemplates, rfqLastTbeSessions, rfqLastTbeDocumentReviews, templateDetailView, RfqStatus } from "@/db/schema";
import { sql, and, desc, asc, like, ilike, or, eq, SQL, count, gte, lte, isNotNull, ne, inArray } from "drizzle-orm";
import { filterColumns } from "@/lib/filter-columns";
import { GetRfqLastAttachmentsSchema, GetRfqsSchema } from "./validations";
import { getServerSession } from "next-auth/next"
import { authOptions } from "@/app/api/auth/[...nextauth]/route"
import { sendEmail } from "../mail/sendEmail";
import fs from 'fs/promises'
import path from 'path'
import { addDays, format } from "date-fns"
import { ko, enUS } from "date-fns/locale"
import { generateBasicContractsForVendor } from "../basic-contract/gen-service";
import { writeFile, mkdir } from "fs/promises";
import { generateItbRfqCode } from "../itb/service";

/**
 * RFQ 마감일 기본값 계산 (생성일 + 7일)
 */
export async function getDefaultDueDate(): Promise<Date> {
  const defaultDueDate = new Date();
  defaultDueDate.setDate(defaultDueDate.getDate() + 7);
  return defaultDueDate;
}

export async function getRfqs(input: GetRfqsSchema) {
  unstable_noStore();

  try {
    const offset = (input.page - 1) * input.perPage;

    // 1. RFQ 타입별 필터링
    let typeFilter: SQL<unknown> | undefined = undefined;
    if (input.rfqCategory) {
      switch (input.rfqCategory) {
        case "general":
          // 일반견적: rfqType이 있는 경우
          // typeFilter = and(
          //   isNotNull(rfqsLastView.rfqType),
          //   ne(rfqsLastView.rfqType, '')
          // );
          // 일반견적: rfqCode가 F로 시작하는 경우
          typeFilter = 
            like(rfqsLastView.rfqCode,'F%');
          break;
        case "itb":
          // ITB: projectCompany가 있는 경우
          typeFilter = 
            like(rfqsLastView.rfqCode,'I%');
          break;
        case "rfq":
          // RFQ: prNumber가 있는 경우  
          typeFilter = 
            like(rfqsLastView.rfqCode,'R%');
          break;
      }
    }

    // 2. 고급 필터 처리
    let advancedWhere: SQL<unknown> | undefined = undefined;

    if (input.filters && Array.isArray(input.filters) && input.filters.length > 0) {
      console.log("필터 적용:", input.filters.map(f => `${f.id} ${f.operator} ${f.value}`));
      // dueDate 필터 디버깅
      const dueDateFilters = input.filters.filter(f => f.id === 'dueDate');
      if (dueDateFilters.length > 0) {
        console.log("dueDate 필터 상세:", dueDateFilters);
      }
      try {
        advancedWhere = filterColumns({
          table: rfqsLastView,
          filters: input.filters,
          joinOperator: input.joinOperator || 'and',
        });

        console.log("필터 조건 생성 완료");
        // dueDate 필터가 포함된 경우 SQL 쿼리 확인
        if (dueDateFilters.length > 0) {
          console.log("advancedWhere SQL:", advancedWhere);
        }
      } catch (error) {
        console.error("필터 조건 생성 오류:", error);
        advancedWhere = undefined;
      }
    }

    // 3. 글로벌 검색 조건
    let globalWhere: SQL<unknown> | undefined = undefined;
    if (input.search) {
      const s = `%${input.search}%`;

      const searchConditions: SQL<unknown>[] = [
        ilike(rfqsLastView.rfqCode, s),
        ilike(rfqsLastView.itemCode, s),
        ilike(rfqsLastView.itemName, s),
        ilike(rfqsLastView.packageNo, s),
        ilike(rfqsLastView.packageName, s),
        ilike(rfqsLastView.picName, s),
        ilike(rfqsLastView.engPicName, s),
        ilike(rfqsLastView.projectCode, s),
        ilike(rfqsLastView.projectName, s),
        ilike(rfqsLastView.rfqTitle, s),
        ilike(rfqsLastView.prNumber, s),
      ].filter(Boolean);

      if (searchConditions.length > 0) {
        globalWhere = or(...searchConditions);
      }
    }

    // 4. 최종 WHERE 조건 결합
    const whereConditions: SQL<unknown>[] = [];
    if (typeFilter) whereConditions.push(typeFilter);
    if (advancedWhere) whereConditions.push(advancedWhere);
    if (globalWhere) whereConditions.push(globalWhere);

    const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined;

    // 5. 전체 데이터 수 조회
    const totalResult = await db
      .select({ count: count() })
      .from(rfqsLastView)
      .where(finalWhere);

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

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

    console.log("총 데이터 수:", total);

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

    if (orderByColumns.length === 0) {
      orderByColumns.push(desc(rfqsLastView.createdAt));
    }

    const rfqData = await db
      .select()
      .from(rfqsLastView)
      .where(finalWhere)
      .orderBy(...orderByColumns)
      .limit(input.perPage)
      .offset(offset);

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

    console.log("반환 데이터 수:", rfqData.length);

    return { data: rfqData, pageCount, total };
  } catch (err) {
    console.error("getRfqs 오류:", err);
    return { data: [], pageCount: 0, total: 0 };
  }
}

const isDevelopment = process.env.NODE_ENV === 'development' ||
  process.env.NODE_ENV === 'test';

const getRfqById = async (id: number): Promise<RfqsLastView | null> => {
  // 1) RFQ 단건 조회
  const rfqsRes = await db
    .select()
    .from(rfqsLastView)
    .where(eq(rfqsLastView.id, id))
    .limit(1);

  if (rfqsRes.length === 0) return null;
  const rfqRow = rfqsRes[0];

  // 3) RfqWithItems 형태로 반환
  const result: RfqsLastView = {
    ...rfqRow,

  };

  return result;
};


export const findRfqLastById = async (id: number): Promise<RfqsLastView | null> => {
  try {

    const rfq = await getRfqById(id);

    return rfq;
  } catch (error) {
    throw new Error('Failed to fetch RFQ');
  }
};


// 모든 첨부파일을 가져오는 새로운 서버 액션
export async function getRfqAllAttachments(rfqId: number) {
  try {
    // 데이터 조회
    const data = await db
      .select({
        // 첨부파일 메인 정보
        id: rfqLastAttachments.id,
        attachmentType: rfqLastAttachments.attachmentType,
        serialNo: rfqLastAttachments.serialNo,
        rfqId: rfqLastAttachments.rfqId,
        currentRevision: rfqLastAttachments.currentRevision,
        latestRevisionId: rfqLastAttachments.latestRevisionId,
        description: rfqLastAttachments.description,
        createdBy: rfqLastAttachments.createdBy,
        createdAt: rfqLastAttachments.createdAt,
        updatedAt: rfqLastAttachments.updatedAt,

        // 최신 리비전 파일 정보
        fileName: rfqLastAttachmentRevisions.fileName,
        originalFileName: rfqLastAttachmentRevisions.originalFileName,
        filePath: rfqLastAttachmentRevisions.filePath,
        fileSize: rfqLastAttachmentRevisions.fileSize,
        fileType: rfqLastAttachmentRevisions.fileType,
        revisionComment: rfqLastAttachmentRevisions.revisionComment,

        // 생성자 정보
        createdByName: users.name,
      })
      .from(rfqLastAttachments)
      .leftJoin(
        rfqLastAttachmentRevisions,
        and(
          eq(rfqLastAttachments.latestRevisionId, rfqLastAttachmentRevisions.id),
          eq(rfqLastAttachmentRevisions.isLatest, true)
        )
      )
      .leftJoin(users, eq(rfqLastAttachments.createdBy, users.id))
      .where(eq(rfqLastAttachments.rfqId, rfqId))
      .orderBy(desc(rfqLastAttachments.createdAt))

    return {
      data,
      success: true
    }
  } catch (err) {
    console.error("getRfqAllAttachments error:", err)
    return {
      data: [],
      success: false
    }
  }
}


// 사용자 목록 조회 (필터용), 견적담당자, 구매담당자
export async function getPUsersForFilter() {

  try {
    return await db
      .select({
        id: users.id,
        name: users.name,
        userCode: users.userCode,
        deptName: users.deptName,
        isAbsent: users.isAbsent,
        isDeletedOnNonSap: users.isDeletedOnNonSap,
      })
      .from(users)
      .where(and(eq(users.isActive, true), isNotNull(users.userCode,)))
      .orderBy(asc(users.name))
  } catch (err) {
    console.error("Error fetching users for filter:", err)
    return []
  }
}



// 일반견적 RFQ 코드 생성 (F+userCode(3자리)+일련번호5자리 형식)
async function generateGeneralRfqCode(userCode: string): Promise<string> {
  try {
    // 동일한 userCode를 가진 마지막 일반견적 번호 조회
    const lastRfq = await db
      .select({ rfqCode: rfqsLast.rfqCode })
      .from(rfqsLast)
      .where(
        and(
          // eq(rfqsLast.rfqType, "일반견적"),
          like(rfqsLast.rfqCode, `F${userCode}%`) // 같은 userCode로 시작하는 RFQ만 조회
        )
      )
      .orderBy(desc(rfqsLast.createdAt))
      .limit(1);

    let nextNumber = 1;

    if (lastRfq.length > 0 && lastRfq[0].rfqCode) {
      // F+userCode(3자리)+일련번호(5자리) 형식에서 마지막 5자리 숫자 추출
      const rfqCode = lastRfq[0].rfqCode;
      const serialNumber = rfqCode.slice(-5); // 마지막 5자리 추출

      // 숫자인지 확인하고 다음 번호 생성
      if (/^\d{5}$/.test(serialNumber)) {
        nextNumber = parseInt(serialNumber) + 1;
      }
    }

    // 5자리 숫자로 패딩
    const paddedNumber = String(nextNumber).padStart(5, '0');
    return `F${userCode}${paddedNumber}`;
  } catch (error) {
    console.error("Error generating General RFQ code:", error);
    // 에러 발생 시 타임스탬프 기반 코드 생성
    const timestamp = Date.now().toString().slice(-5);
    return `F${userCode}${timestamp}`;
  }
}

// 일반견적 생성 액션
interface CreateGeneralRfqInput {
  rfqType: string;
  rfqTitle: string;
  dueDate: Date;
  picUserId: number;
  projectId?: number;
  remark?: string;
  items: Array<{
    itemCode: string;
    itemName: string;
    quantity: number;
    uom: string;
    remark?: string;
  }>;
  createdBy: number;
  updatedBy: number;
}

export async function createGeneralRfqAction(input: CreateGeneralRfqInput) {
  try {
    // 트랜잭션으로 처리
    const result = await db.transaction(async (tx) => {
      // 1. 구매 담당자 정보 조회
      const picUser = await tx
        .select({
          name: users.name,
          email: users.email,
          userCode: users.userCode
        })
        .from(users)
        .where(eq(users.id, input.picUserId))
        .limit(1);

      if (!picUser || picUser.length === 0) {
        throw new Error("구매 담당자를 찾을 수 없습니다");
      }

      // 2. userCode 확인 (3자리)
      const userCode = picUser[0].userCode;
      if (!userCode || userCode.length !== 3) {
        throw new Error("구매 담당자의 userCode가 올바르지 않습니다 (3자리 필요)");
      }

      // 3. RFQ 코드 생성 (userCode 사용)
      const rfqCode = await generateGeneralRfqCode(userCode);

      // 4. 대표 아이템 정보 추출 (첫 번째 아이템)
      const representativeItem = input.items[0];

      // 5. 마감일 기본값 설정 (입력값 없으면 생성일 + 7일)
      const dueDate = input.dueDate || getDefaultDueDate();

      console.log(dueDate,"dueDate")

      // 6. rfqsLast 테이블에 기본 정보 삽입
      const [newRfq] = await tx
        .insert(rfqsLast)
        .values({
          rfqCode,
          rfqType: input.rfqType,
          rfqTitle: input.rfqTitle,
          status: "RFQ 생성",
          dueDate: dueDate, // 마감일 기본값 설정

          // 프로젝트 정보 (선택사항)
          projectId: input.projectId || null,

          // 대표 아이템 정보
          itemCode: representativeItem.itemCode,
          itemName: representativeItem.itemName,

          // 담당자 정보
          pic: input.picUserId,
          picCode: userCode, // userCode를 picCode로 사용
          picName: picUser[0].name || '',

          // 기타 정보
          remark: input.remark || null,
          createdBy: input.createdBy,
          updatedBy: input.updatedBy,
          createdAt: new Date(),
          updatedAt: new Date(),
        })
        .returning();

      // 6. rfqPrItems 테이블에 아이템들 삽입
      const prItemsData = input.items.map((item, index) => ({
        rfqsLastId: newRfq.id,
        rfqItem: `${index + 1}`.padStart(3, '0'), // 001, 002, ...
        prItem: null, // 일반견적에서는 PR 아이템 번호를 null로 설정
        prNo: null, // 일반견적에서는 PR 번호를 null로 설정

        materialCode: item.materialCode || item.itemCode, // SAP 자재코드 (없으면 자재그룹코드 사용)
        materialCategory: item.itemCode, // 자재그룹코드
        materialDescription: item.materialName || item.itemName, // SAP 자재명 (없으면 자재그룹명 사용)
        quantity: item.quantity,
        uom: item.uom,

        majorYn: index === 0, // 첫 번째 아이템을 주요 아이템으로 설정
        remark: item.remark || null,
      }));

      await tx.insert(rfqPrItems).values(prItemsData);

      return newRfq;
    });

    return {
      success: true,
      message: "일반견적이 성공적으로 생성되었습니다",
      data: {
        id: result.id,
        rfqCode: result.rfqCode,
      },
    };

  } catch (error) {
    console.error("일반견적 생성 오류:", error);

    if (error instanceof Error) {
      return {
        success: false,
        error: error.message,
      };
    }

    return {
      success: false,
      error: "일반견적 생성 중 오류가 발생했습니다",
    };
  }
}

// 일반견적 미리보기 (선택적 기능)
export async function previewGeneralRfqCode(picUserId: number): Promise<string> {
  try {
    // 구매 담당자 정보 조회
    const picUser = await db
      .select({
        userCode: users.userCode
      })
      .from(users)
      .where(eq(users.id, picUserId))
      .limit(1);

    if (!picUser || picUser.length === 0 || !picUser[0].userCode) {
      return `F???00001`;
    }

    const userCode = picUser[0].userCode;
    if (userCode.length !== 3) {
      return `F???00001`;
    }

    // 동일한 userCode를 가진 마지막 일반견적 번호 조회
    const lastRfq = await db
      .select({ rfqCode: rfqsLast.rfqCode })
      .from(rfqsLast)
      .where(
        and(
          // eq(rfqsLast.rfqType, "일반견적"),
          like(rfqsLast.rfqCode, `F${userCode}%`)
        )
      )
      .orderBy(desc(rfqsLast.createdAt))
      .limit(1);

    let nextNumber = 1;

    console.log(lastRfq,"lastRfq")
    console.log(userCode,"userCode")


    if (lastRfq.length > 0 && lastRfq[0].rfqCode) {
      const rfqCode = lastRfq[0].rfqCode;
      const serialNumber = rfqCode.slice(-5);

      if (/^\d{5}$/.test(serialNumber)) {
        nextNumber = parseInt(serialNumber) + 1;
      }
    }

    const paddedNumber = String(nextNumber).padStart(5, '0');
    return `F${userCode}${paddedNumber}`;
  } catch (error) {
    console.log(error)
    return `F???XXXXX`;
  }
}


/**
 * RFQ 첨부파일 목록 조회
 */
export async function getRfqAttachmentsAction(rfqId: number) {
  try {
    if (!rfqId || rfqId <= 0) {
      return {
        success: false,
        error: "유효하지 않은 RFQ ID입니다",
        data: []
      }
    }

    // rfpAttachmentsWithLatestRevisionView 뷰 조회
    const attachments = await db.execute(sql`
      SELECT 
        attachment_id,
        attachment_type,
        serial_no,
        rfq_id,
        description,
        current_revision,
        revision_id,
        file_name,
        original_file_name,
        file_path,
        file_size,
        file_type,
        revision_comment,
        created_by,
        created_by_name,
        created_at,
        updated_at
      FROM rfq_attachments_with_latest_revision
      WHERE rfq_id = ${rfqId}
      ORDER BY attachment_type, serial_no, created_at DESC
    `)

    const formattedAttachments = attachments.rows.map((row: any) => ({
      attachmentId: row.attachment_id,
      attachmentType: row.attachment_type,
      serialNo: row.serial_no,
      rfqId: row.rfq_id,
      description: row.description,
      currentRevision: row.current_revision,
      revisionId: row.revision_id,
      fileName: row.file_name,
      originalFileName: row.original_file_name,
      filePath: row.file_path,
      fileSize: row.file_size,
      fileType: row.file_type,
      revisionComment: row.revision_comment,
      createdBy: row.created_by,
      createdByName: row.created_by_name,
      createdAt: row.created_at ? new Date(row.created_at) : null,
      updatedAt: row.updated_at ? new Date(row.updated_at) : null,
    }))

    return {
      success: true,
      data: formattedAttachments,
      count: formattedAttachments.length
    }

  } catch (error) {
    console.error("RFQ 첨부파일 조회 오류:", error)
    return {
      success: false,
      error: "첨부파일 목록을 불러오는데 실패했습니다",
      data: []
    }
  }
}

/**
 * RFQ 품목 목록 조회
 */
export async function getRfqItemsAction(rfqId: number) {
  try {
    if (!rfqId || rfqId <= 0) {
      return {
        success: false,
        error: "유효하지 않은 RFQ ID입니다",
        data: []
      }
    }

    // prItemsLastView 조회
    const items = await db
      .select()
      .from(prItemsLastView)
      .where(eq(prItemsLastView.rfqsLastId, rfqId))
      .orderBy(prItemsLastView.majorYn, prItemsLastView.rfqItem, prItemsLastView.materialCode)

    const formattedItems = items.map(item => ({
      id: item.id,
      rfqsLastId: item.rfqsLastId,
      rfqItem: item.rfqItem,
      prItem: item.prItem,
      prNo: item.prNo,
      materialCode: item.materialCode,
      materialCategory: item.materialCategory,
      acc: item.acc,
      materialDescription: item.materialDescription,
      size: item.size,
      deliveryDate: item.deliveryDate,
      quantity: Number(item.quantity) || 0,  // 여기서 숫자로 변환
      uom: item.uom,
      grossWeight: Number(item.grossWeight) || 0,  // 여기서 숫자로 변환
      gwUom: item.gwUom,
      specNo: item.specNo,
      specUrl: item.specUrl,
      trackingNo: item.trackingNo,
      majorYn: item.majorYn,
      remark: item.remark,
      projectDef: item.projectDef,
      projectSc: item.projectSc,
      projectKl: item.projectKl,
      projectLc: item.projectLc,
      projectDl: item.projectDl,
      // RFQ 관련 정보
      rfqCode: item.rfqCode,
      rfqType: item.rfqType,
      rfqTitle: item.rfqTitle,
      itemCode: item.itemCode,
      itemName: item.itemName,
      projectCode: item.projectCode,
      projectName: item.projectName,
    }))

    // 주요 품목과 일반 품목 분리 및 통계
    const majorItems = formattedItems.filter(item => item.majorYn)
    const regularItems = formattedItems.filter(item => !item.majorYn)

    return {
      success: true,
      data: formattedItems,
      statistics: {
        total: formattedItems.length,
        major: majorItems.length,
        regular: regularItems.length,
        totalQuantity: formattedItems.reduce((sum, item) => sum + (item.quantity || 0), 0),
        totalWeight: formattedItems.reduce((sum, item) => sum + (item.grossWeight || 0), 0),
      }
    }

  } catch (error) {
    console.error("RFQ 품목 조회 오류:", error)
    return {
      success: false,
      error: "품목 목록을 불러오는데 실패했습니다",
      data: [],
      statistics: {
        total: 0,
        major: 0,
        regular: 0,
        totalQuantity: 0,
        totalWeight: 0,
      }
    }
  }
}

/**
 * RFQ 기본 정보 조회 (첨부파일/품목 다이얼로그용)
 */
export async function getRfqBasicInfoAction(rfqId: number) {
  try {
    if (!rfqId || rfqId <= 0) {
      return {
        success: false,
        error: "유효하지 않은 RFQ ID입니다",
        data: null
      }
    }

    const rfqInfo = await db
      .select({
        id: rfqsLast.id,
        rfqCode: rfqsLast.rfqCode,
        rfqType: rfqsLast.rfqType,
        rfqTitle: rfqsLast.rfqTitle,
        status: rfqsLast.status,
        itemCode: rfqsLast.itemCode,
        itemName: rfqsLast.itemName,
        dueDate: rfqsLast.dueDate,
        createdAt: rfqsLast.createdAt,
      })
      .from(rfqsLast)
      .where(eq(rfqsLast.id, rfqId))
      .limit(1)

    if (!rfqInfo.length) {
      return {
        success: false,
        error: "RFQ를 찾을 수 없습니다",
        data: null
      }
    }

    return {
      success: true,
      data: rfqInfo[0]
    }

  } catch (error) {
    console.error("RFQ 기본정보 조회 오류:", error)
    return {
      success: false,
      error: "RFQ 정보를 불러오는데 실패했습니다",
      data: null
    }
  }
}

export interface RevisionHistory {
  id: number;
  attachmentId: number;
  revisionNo: string;
  fileName: string;
  originalFileName: string;
  filePath: string;
  fileSize: number;
  fileType: string;
  isLatest: boolean;
  revisionComment: string | null;
  createdBy: number;
  createdAt: Date;
  createdByName: string | null;
}

export interface AttachmentWithHistory {
  id: number;
  serialNo: string | null;
  description: string | null;
  currentRevision: string | null;
  originalFileName: string | null;
  revisions: RevisionHistory[];
}

// 리비전 히스토리 조회
export async function getRevisionHistory(attachmentId: number): Promise<{
  success: boolean;
  data?: AttachmentWithHistory;
  error?: string;
}> {
  try {
    // 첨부파일 기본 정보 조회
    const [attachment] = await db
      .select({
        id: rfqLastAttachments.id,
        serialNo: rfqLastAttachments.serialNo,
        description: rfqLastAttachments.description,
        currentRevision: rfqLastAttachments.currentRevision,
        latestRevisionId: rfqLastAttachments.latestRevisionId,
      })
      .from(rfqLastAttachments)
      .where(eq(rfqLastAttachments.id, attachmentId));

    if (!attachment) {
      return {
        success: false,
        error: "첨부파일을 찾을 수 없습니다.",
      };
    }

    // 최신 리비전 정보 조회 (파일명 가져오기 위해)
    let originalFileName: string | null = null;
    if (attachment.latestRevisionId) {
      const [latestRevision] = await db
        .select({
          originalFileName: rfqLastAttachmentRevisions.originalFileName,
        })
        .from(rfqLastAttachmentRevisions)
        .where(eq(rfqLastAttachmentRevisions.id, attachment.latestRevisionId));

      originalFileName = latestRevision?.originalFileName || null;
    }

    // 모든 리비전 히스토리 조회
    const revisions = await db
      .select({
        id: rfqLastAttachmentRevisions.id,
        attachmentId: rfqLastAttachmentRevisions.attachmentId,
        revisionNo: rfqLastAttachmentRevisions.revisionNo,
        fileName: rfqLastAttachmentRevisions.fileName,
        originalFileName: rfqLastAttachmentRevisions.originalFileName,
        filePath: rfqLastAttachmentRevisions.filePath,
        fileSize: rfqLastAttachmentRevisions.fileSize,
        fileType: rfqLastAttachmentRevisions.fileType,
        isLatest: rfqLastAttachmentRevisions.isLatest,
        revisionComment: rfqLastAttachmentRevisions.revisionComment,
        createdBy: rfqLastAttachmentRevisions.createdBy,
        createdAt: rfqLastAttachmentRevisions.createdAt,
        createdByName: users.name,
      })
      .from(rfqLastAttachmentRevisions)
      .leftJoin(users, eq(rfqLastAttachmentRevisions.createdBy, users.id))
      .where(eq(rfqLastAttachmentRevisions.attachmentId, attachmentId))
      .orderBy(desc(rfqLastAttachmentRevisions.createdAt));

    return {
      success: true,
      data: {
        ...attachment,
        originalFileName,
        revisions,
      },
    };
  } catch (error) {
    console.error("Get revision history error:", error);
    return {
      success: false,
      error: "리비전 히스토리 조회 중 오류가 발생했습니다.",
    };
  }
}

// 특정 리비전 다운로드 URL 생성
export async function getRevisionDownloadUrl(revisionId: number): Promise<{
  success: boolean;
  data?: {
    url: string;
    fileName: string;
  };
  error?: string;
}> {
  try {
    const [revision] = await db
      .select({
        filePath: rfqLastAttachmentRevisions.filePath,
        originalFileName: rfqLastAttachmentRevisions.originalFileName,
      })
      .from(rfqLastAttachmentRevisions)
      .where(eq(rfqLastAttachmentRevisions.id, revisionId));

    if (!revision) {
      return {
        success: false,
        error: "리비전을 찾을 수 없습니다.",
      };
    }

    return {
      success: true,
      data: {
        url: revision.filePath,
        fileName: revision.originalFileName,
      },
    };
  } catch (error) {
    console.error("Get revision download URL error:", error);
    return {
      success: false,
      error: "다운로드 URL 생성 중 오류가 발생했습니다.",
    };
  }
}

export async function getRfqVendorAttachments(rfqId: number) {
  try {
    // 데이터 조회
    const data = await db
      .select({
        // 첨부파일 메인 정보
        id: rfqLastVendorAttachments.id,
        vendorResponseId: rfqLastVendorAttachments.vendorResponseId,
        attachmentType: rfqLastVendorAttachments.attachmentType,
        documentNo: rfqLastVendorAttachments.documentNo,

        // 파일 정보
        fileName: rfqLastVendorAttachments.fileName,
        originalFileName: rfqLastVendorAttachments.originalFileName,
        filePath: rfqLastVendorAttachments.filePath,
        fileSize: rfqLastVendorAttachments.fileSize,
        fileType: rfqLastVendorAttachments.fileType,

        // 파일 설명
        description: rfqLastVendorAttachments.description,

        // 유효기간
        validFrom: rfqLastVendorAttachments.validFrom,
        validTo: rfqLastVendorAttachments.validTo,

        // 업로드 정보
        uploadedBy: rfqLastVendorAttachments.uploadedBy,
        uploadedAt: rfqLastVendorAttachments.uploadedAt,

        // 업로더 정보
        uploadedByName: users.name,

        // 벤더 정보
        vendorId: rfqLastVendorResponses.vendorId,
        vendorName: vendors.vendorName,
        vendorCode: vendors.vendorCode,

        // 응답 상태
        responseStatus: rfqLastVendorResponses.status,
        responseVersion: rfqLastVendorResponses.responseVersion,
      })
      .from(rfqLastVendorAttachments)
      .leftJoin(
        rfqLastVendorResponses,
        eq(rfqLastVendorAttachments.vendorResponseId, rfqLastVendorResponses.id)
      )
      .leftJoin(users, eq(rfqLastVendorAttachments.uploadedBy, users.id))
      .leftJoin(vendors, eq(rfqLastVendorResponses.vendorId, vendors.id))
      .where(eq(rfqLastVendorResponses.rfqsLastId, rfqId))
      .orderBy(desc(rfqLastVendorAttachments.uploadedAt))

    return {
      vendorData: data,
      vendorSuccess: true
    }
  } catch (err) {
    console.error("getRfqVendorAttachments error:", err)
    return {
      vendorData: [],
      vendorSuccess: false
    }
  }
}

/**
 * 특정 RFQ의 특정 벤더가 제출한 모든 응답의 첨부파일 조회, 추후 협력업체가 제출한 첨부파일 히스토리 조회용으로 사용
 */
export async function getVendorResponseAttachments(rfqId: number, vendorId: number) {
  try {
    if (!rfqId || rfqId <= 0 || !vendorId || vendorId <= 0) {
      return {
        success: false,
        error: "유효하지 않은 RFQ ID 또는 벤더 ID입니다",
        data: []
      }
    }

    // 먼저 해당 rfq에 연결된 벤더의 모든응답들을 찾기
    const vendorResponses = await db
      .select({
        id: rfqLastVendorResponses.id,
        status: rfqLastVendorResponses.status,
        responseVersion: rfqLastVendorResponses.responseVersion,
      })
      .from(rfqLastVendorResponses)
      .where(
        and(
          eq(rfqLastVendorResponses.rfqsLastId, rfqId),
          eq(rfqLastVendorResponses.vendorId, vendorId)
        )
      )

    if (vendorResponses.length === 0) {
      return {
        success: true,
        data: []
      }
    }

    const responseIds = vendorResponses.map(r => r.id)

    // 해당 응답들의 모든 첨부파일 조회
    const data = await db
      .select({
        // 첨부파일 메인 정보
        id: rfqLastVendorAttachments.id,
        vendorResponseId: rfqLastVendorAttachments.vendorResponseId,
        attachmentType: rfqLastVendorAttachments.attachmentType,
        documentNo: rfqLastVendorAttachments.documentNo,

        // 파일 정보
        fileName: rfqLastVendorAttachments.fileName,
        originalFileName: rfqLastVendorAttachments.originalFileName,
        filePath: rfqLastVendorAttachments.filePath,
        fileSize: rfqLastVendorAttachments.fileSize,
        fileType: rfqLastVendorAttachments.fileType,

        // 파일 설명
        description: rfqLastVendorAttachments.description,

        // 유효기간
        validFrom: rfqLastVendorAttachments.validFrom,
        validTo: rfqLastVendorAttachments.validTo,

        // 업로드 정보
        uploadedBy: rfqLastVendorAttachments.uploadedBy,
        uploadedAt: rfqLastVendorAttachments.uploadedAt,

        // 업로더 정보
        uploadedByName: users.name,

        // 응답 정보
        responseStatus: rfqLastVendorResponses.status,
        responseVersion: rfqLastVendorResponses.responseVersion,
      })
      .from(rfqLastVendorAttachments)
      .leftJoin(
        rfqLastVendorResponses,
        eq(rfqLastVendorAttachments.vendorResponseId, rfqLastVendorResponses.id)
      )
      .leftJoin(users, eq(rfqLastVendorAttachments.uploadedBy, users.id))
      .where(inArray(rfqLastVendorAttachments.vendorResponseId, responseIds))
      .orderBy(
        desc(rfqLastVendorAttachments.uploadedAt),
        rfqLastVendorAttachments.attachmentType
      )

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



// 벤더 추가 액션
export async function addVendorToRfq({
  rfqId,
  vendorId,
  conditions,
}: {
  rfqId: number;
  vendorId: number;
  conditions: {
    currency: string;
    paymentTermsCode: string;
    incotermsCode: string;
    incotermsDetail?: string;
    deliveryDate: Date;
    contractDuration?: string;
    taxCode?: string;
    placeOfShipping?: string;
    placeOfDestination?: string;
    materialPriceRelatedYn?: boolean;
    sparepartYn?: boolean;
    firstYn?: boolean;
    firstDescription?: string;
    sparepartDescription?: string;
  };
}) {
  try {
    const session = await getServerSession(authOptions)

    if (!session?.user) {
      throw new Error("인증이 필요합니다.")
    }

    const userId = Number(session.user.id)
    // 중복 체크
    const existing = await db
      .select()
      .from(rfqLastDetails)
      .where(
        and(
          eq(rfqLastDetails.rfqsLastId, rfqId),
          eq(rfqLastDetails.vendorsId, vendorId)
        )
      )
      .limit(1);

    if (existing.length > 0) {
      return { success: false, error: "이미 추가된 벤더입니다." };
    }

    // 트랜잭션으로 처리
    await db.transaction(async (tx) => {
      // 1. rfqLastDetails에 벤더 추가
      const [detail] = await tx
        .insert(rfqLastDetails)
        .values({
          rfqsLastId: rfqId,
          vendorsId: vendorId,
          ...conditions,
          updatedBy: userId,
        })
        .returning();

      // 2. rfqLastVendorResponses에 초기 응답 레코드 생성
      const [response] = await tx
        .insert(rfqLastVendorResponses)
        .values({
          rfqsLastId: rfqId,
          rfqLastDetailsId: detail.id,
          vendorId: vendorId,
          status: "초대됨",
          responseVersion: 1,
          isLatest: true,
          currency: conditions.currency,
          // 구매자 제시 조건 복사 (초기값)
          vendorCurrency: conditions.currency,
          vendorPaymentTermsCode: conditions.paymentTermsCode,
          vendorIncotermsCode: conditions.incotermsCode,
          vendorIncotermsDetail: conditions.incotermsDetail,
          vendorDeliveryDate: conditions.deliveryDate,
          vendorContractDuration: conditions.contractDuration,
          vendorTaxCode: conditions.taxCode,
          vendorPlaceOfShipping: conditions.placeOfShipping,
          vendorPlaceOfDestination: conditions.placeOfDestination,
          vendorMaterialPriceRelatedYn: conditions.materialPriceRelatedYn,
          vendorSparepartYn: conditions.sparepartYn,
          vendorFirstYn: conditions.firstYn,
          vendorFirstDescription: conditions.firstDescription,
          vendorSparepartDescription: conditions.sparepartDescription,
          createdBy: userId,
          updatedBy: userId,
        })
        .returning();

      // 3. 이력 기록
      await tx.insert(rfqLastVendorResponseHistory).values({
        vendorResponseId: response.id,
        action: "생성",
        newStatus: "초대됨",
        changeDetails: { action: "벤더 초대", conditions },
        performedBy: userId,
      });
    });

    revalidatePath(`/rfq-last/${rfqId}/vendor`);

    return { success: true };
  } catch (error) {
    console.error("Add vendor error:", error);
    return { success: false, error: "벤더 추가 중 오류가 발생했습니다." };
  }
}

export async function addVendorsToRfq({
  rfqId,
  vendorIds,
  conditions,
  contractRequirements, // 추가된 파라미터
}: {
  rfqId: number;
  vendorIds: number[];
  conditions?: {
    currency: string;
    paymentTermsCode: string;
    incotermsCode: string;
    incotermsDetail?: string;
    deliveryDate: Date;
    contractDuration?: string;
    taxCode?: string;
    placeOfShipping?: string;
    placeOfDestination?: string;
    materialPriceRelatedYn?: boolean;
    sparepartYn?: boolean;
    firstYn?: boolean;
    firstDescription?: string;
    sparepartDescription?: string;
  } | null;
  contractRequirements?: { // 추가된 타입 정의
    agreementYn?: boolean;
    ndaYn?: boolean;
    gtcType?: "general" | "project" | "none";
  } | null;
}) {
  try {
    const session = await getServerSession(authOptions);

    if (!session?.user) {
      throw new Error("인증이 필요합니다.");
    }

    const userId = Number(session.user.id);

    // 빈 배열 체크
    if (!vendorIds || vendorIds.length === 0) {
      return { success: false, error: "벤더를 선택해주세요." };
    }

    // 중복 체크 - 이미 추가된 벤더들 확인
    const existingVendors = await db
      .select({
        vendorId: rfqLastDetails.vendorsId,
      })
      .from(rfqLastDetails)
      .where(
        and(
          eq(rfqLastDetails.rfqsLastId, rfqId),
          inArray(rfqLastDetails.vendorsId, vendorIds)
        )
      );

    const existingVendorIds = existingVendors.map(v => v.vendorId);
    const newVendorIds = vendorIds.filter(id => !existingVendorIds.includes(id));

    if (newVendorIds.length === 0) {
      return {
        success: false,
        error: "모든 벤더가 이미 추가되어 있습니다."
      };
    }

    // 일부만 중복인 경우 경고 메시지 준비
    const skippedCount = vendorIds.length - newVendorIds.length;

    // 트랜잭션으로 처리
    const results = await db.transaction(async (tx) => {
      const addedVendors = [];

      for (const vendorId of newVendorIds) {
        // 벤더 정보 조회 (국가 정보 확인용)
        const [vendor] = await tx
          .select({
            id: vendors.id,
            country: vendors.country,
          })
          .from(vendors)
          .where(eq(vendors.id, vendorId))
          .limit(1);

        // 국외 업체인지 확인
        const isInternational = vendor?.country &&
          vendor.country !== "KR" &&
          vendor.country !== "한국";

        // conditions가 없는 경우 기본값 설정
        const vendorConditions = conditions || {
          currency: "USD",
          paymentTermsCode: "NET30",
          incotermsCode: "FOB",
          deliveryDate: new Date(Date.now() + 30 * 24 * 60 * 60 * 1000), // 30일 후
          taxCode: "VV",
        };

        // contractRequirements 기본값 설정
        const defaultContractRequirements = {
          agreementYn: true,
          ndaYn: true,
          gtcType: "none" as "general" | "project" | "none",
        };

        const finalContractRequirements = contractRequirements || defaultContractRequirements;

        // gtcType에 따라 generalGtcYn과 projectGtcYn 설정
        const generalGtcYn = isInternational && finalContractRequirements.gtcType === "general";
        const projectGtcYn = isInternational && finalContractRequirements.gtcType === "project";

        // 국내 업체는 gtcType을 강제로 "none"으로 설정
        const gtcType = isInternational ? finalContractRequirements.gtcType : "none";

        // 1. rfqLastDetails에 벤더 추가 (기본계약 정보 포함)
        const [detail] = await tx
          .insert(rfqLastDetails)
          .values({
            rfqsLastId: rfqId,
            vendorsId: vendorId,
            ...vendorConditions,
            // 기본계약 관련 필드 추가
            agreementYn: finalContractRequirements.agreementYn ?? true,
            ndaYn: finalContractRequirements.ndaYn ?? true,
            gtcType: gtcType,
            generalGtcYn: generalGtcYn,
            projectGtcYn: projectGtcYn,
            updatedBy: userId,
            updatedAt: new Date(),
          })
          .returning();

        addedVendors.push({
          vendorId,
          detailId: detail.id,
          contractRequirements: {
            agreementYn: detail.agreementYn,
            ndaYn: detail.ndaYn,
            gtcType: detail.gtcType,
            generalGtcYn: detail.generalGtcYn,
            projectGtcYn: detail.projectGtcYn,
          }
        });
      }

      return addedVendors;
    });

    revalidatePath(`/evcp/rfq-last/${rfqId}/vendor`);

    // 성공 메시지 구성
    let message = `${results.length}개 벤더가 추가되었습니다.`;
    if (skippedCount > 0) {
      message += ` (${skippedCount}개는 이미 추가된 벤더로 제외)`;
    }

    return {
      success: true,
      data: {
        added: results.length,
        skipped: skippedCount,
        message,
        vendors: results, // 추가된 벤더 정보 반환
      }
    };
  } catch (error) {
    console.error("Add vendors error:", error);
    return {
      success: false,
      error: "벤더 추가 중 오류가 발생했습니다."
    };
  }
}

// 벤더 조건 일괄 업데이트 함수 (추가)
export async function updateVendorConditionsBatch({
  rfqId,
  vendorIds,
  conditions,
}: {
  rfqId: number;
  vendorIds: number[];
  conditions: {
    currency?: string;
    paymentTermsCode?: string;
    incotermsCode?: string;
    incotermsDetail?: string;
    deliveryDate?: Date;
    contractDuration?: string;
    taxCode?: string;
    placeOfShipping?: string;
    placeOfDestination?: string;
    materialPriceRelatedYn?: boolean;
    sparepartYn?: boolean;
    firstYn?: boolean;
    firstDescription?: string;
    sparepartDescription?: string;
  };
}) {
  try {
    const session = await getServerSession(authOptions)

    if (!session?.user) {
      throw new Error("인증이 필요합니다.")
    }

    const userId = Number(session.user.id)

    if (!vendorIds || vendorIds.length === 0) {
      return { success: false, error: "벤더를 선택해주세요." };
    }

    // 트랜잭션으로 처리
    await db.transaction(async (tx) => {
      // 1. rfqLastDetails 업데이트
      await tx
        .update(rfqLastDetails)
        .set({
          ...conditions,
          updatedBy: userId,
          updatedAt: new Date(),
        })
        .where(
          and(
            eq(rfqLastDetails.rfqsLastId, rfqId),
            inArray(rfqLastDetails.vendorsId, vendorIds)
          )
        );

      // 2. rfqLastVendorResponses의 구매자 제시 조건도 업데이트
      const vendorConditions = Object.keys(conditions).reduce((acc, key) => {
        if (conditions[key] !== undefined) {
          acc[`vendor${key.charAt(0).toUpperCase() + key.slice(1)}`] = conditions[key];
        }
        return acc;
      }, {});

      await tx
        .update(rfqLastVendorResponses)
        .set({
          ...vendorConditions,
          updatedBy: userId,
          updatedAt: new Date(),
        })
        .where(
          and(
            eq(rfqLastVendorResponses.rfqsLastId, rfqId),
            inArray(rfqLastVendorResponses.vendorId, vendorIds),
            eq(rfqLastVendorResponses.isLatest, true)
          )
        );

      // 3. 이력 기록 (각 벤더별로)
      const responses = await tx
        .select({ id: rfqLastVendorResponses.id })
        .from(rfqLastVendorResponses)
        .where(
          and(
            eq(rfqLastVendorResponses.rfqsLastId, rfqId),
            inArray(rfqLastVendorResponses.vendorId, vendorIds),
            eq(rfqLastVendorResponses.isLatest, true)
          )
        );

      for (const response of responses) {
        await tx.insert(rfqLastVendorResponseHistory).values({
          vendorResponseId: response.id,
          action: "조건변경",
          changeDetails: {
            action: "조건 일괄 업데이트",
            conditions,
            batchUpdate: true,
            totalVendors: vendorIds.length
          },
          performedBy: userId,
        });
      }
    });

    revalidatePath(`/rfq-last/${rfqId}/vendor`);

    return {
      success: true,
      data: {
        message: `${vendorIds.length}개 벤더의 조건이 업데이트되었습니다.`
      }
    };
  } catch (error) {
    console.error("Update vendor conditions error:", error);
    return {
      success: false,
      error: "조건 업데이트 중 오류가 발생했습니다."
    };
  }
}


// 벤더 삭제 액션
export async function removeVendorFromRfq({
  rfqId,
  vendorId,
}: {
  rfqId: number;
  vendorId: number;
}) {
  try {
    const session = await getServerSession(authOptions)

    if (!session?.user) {
      throw new Error("인증이 필요합니다.")
    }

    // 응답 체크
    const [response] = await db
      .select()
      .from(rfqLastVendorResponses)
      .where(
        and(
          eq(rfqLastVendorResponses.rfqsLastId, rfqId),
          eq(rfqLastVendorResponses.vendorId, vendorId),
          eq(rfqLastVendorResponses.isLatest, true)
        )
      )
      .limit(1);

    if (response && response.status !== "초대됨") {
      return {
        success: false,
        error: "이미 진행 중인 벤더는 삭제할 수 없습니다."
      };
    }

    // 삭제
    await db
      .delete(rfqLastDetails)
      .where(
        and(
          eq(rfqLastDetails.rfqsLastId, rfqId),
          eq(rfqLastDetails.vendorsId, vendorId)
        )
      );

    revalidatePath(`/rfq-last/${rfqId}/vendor`);

    return { success: true };
  } catch (error) {
    console.error("Remove vendor error:", error);
    return { success: false, error: "벤더 삭제 중 오류가 발생했습니다." };
  }
}

// 벤더 응답 상태 업데이트
export async function updateVendorResponseStatus({
  responseId,
  status,
  reason,
}: {
  responseId: number;
  status: "작성중" | "제출완료" | "수정요청" | "최종확정" | "취소";
  reason?: string;
}) {
  try {
    const session = await getServerSession(authOptions)

    if (!session?.user) {
      throw new Error("인증이 필요합니다.")
    }


    const [current] = await db
      .select()
      .from(rfqLastVendorResponses)
      .where(eq(rfqLastVendorResponses.id, responseId))
      .limit(1);

    if (!current) {
      return { success: false, error: "응답을 찾을 수 없습니다." };
    }

    // 상태 업데이트
    await db
      .update(rfqLastVendorResponses)
      .set({
        status,
        submittedAt: status === "제출완료" ? new Date() : current.submittedAt,
        updatedBy: Number(session.user.id),
        updatedAt: new Date(),
      })
      .where(eq(rfqLastVendorResponses.id, responseId));

    // 이력 기록
    await db.insert(rfqLastVendorResponseHistory).values({
      vendorResponseId: responseId,
      action: getActionFromStatus(status),
      previousStatus: current.status,
      newStatus: status,
      changeReason: reason,
      performedBy: Number(session.user.id),
    });

    revalidatePath(`/evcp/rfq-last/${current.rfqsLastId}/vendor`);

    return { success: true };
  } catch (error) {
    console.error("Update status error:", error);
    return { success: false, error: "상태 업데이트 중 오류가 발생했습니다." };
  }
}

// 상태에 따른 액션 텍스트
function getActionFromStatus(status: string): string {
  switch (status) {
    case "제출완료": return "제출";
    case "수정요청": return "반려";
    case "최종확정": return "승인";
    case "취소": return "취소";
    default: return "수정";
  }
}

export async function getRfqVendorResponses(rfqId: number) {
  try {
    // 1. RFQ 기본 정보 조회
    const rfqData = await db
      .select({
        id: rfqsLast.id,
        rfqCode: rfqsLast.rfqCode,
        title: rfqsLast.rfqTitle,
        status: rfqsLast.status,
        endDate: rfqsLast.dueDate,
      })
      .from(rfqsLast)
      .where(eq(rfqsLast.id, rfqId))
      .limit(1);

    if (!rfqData || rfqData.length === 0) {
      return {
        success: false,
        error: "RFQ를 찾을 수 없습니다.",
        data: null
      };
    }

    // 2. RFQ 세부 정보 조회 (복수 버전이 있을 수 있음)
    const details = await db
      .select()
      .from(rfqLastDetails)
      .where(eq(rfqLastDetails.rfqsLastId, rfqId))
      .orderBy(desc(rfqLastDetails.updatedAt));

    // 3. 벤더 응답 정보 조회 (벤더 정보, 제출자 정보 포함)
    const vendorResponsesData = await db
      .select({
        // 응답 기본 정보
        id: rfqLastVendorResponses.id,
        rfqsLastId: rfqLastVendorResponses.rfqsLastId,
        rfqLastDetailsId: rfqLastVendorResponses.rfqLastDetailsId,
        responseVersion: rfqLastVendorResponses.responseVersion,
        isLatest: rfqLastVendorResponses.isLatest,
        status: rfqLastVendorResponses.status,

        //참여 정보
        participationStatus: rfqLastVendorResponses.participationStatus,
        participationRepliedAt: rfqLastVendorResponses.participationRepliedAt,
        participationRepliedBy: rfqLastVendorResponses.participationRepliedBy,
        nonParticipationReason: rfqLastVendorResponses.nonParticipationReason,

        // 벤더 정보
        vendorId: rfqLastVendorResponses.vendorId,
        vendorCode: vendors.vendorCode,
        vendorName: vendors.vendorName,
        vendorEmail: vendors.email,

        // 제출 정보
        submittedAt: rfqLastVendorResponses.submittedAt,
        submittedBy: rfqLastVendorResponses.submittedBy,
        submittedByName: users.name,

        isDocumentConfirmed: rfqLastVendorResponses.isDocumentConfirmed,


        // 금액 정보
        totalAmount: rfqLastVendorResponses.totalAmount,
        currency: rfqLastVendorResponses.currency,

        // 벤더 제안 조건
        vendorCurrency: rfqLastVendorResponses.vendorCurrency,
        vendorPaymentTermsCode: rfqLastVendorResponses.vendorPaymentTermsCode,
        vendorIncotermsCode: rfqLastVendorResponses.vendorIncotermsCode,
        vendorIncotermsDetail: rfqLastVendorResponses.vendorIncotermsDetail,
        vendorDeliveryDate: rfqLastVendorResponses.vendorDeliveryDate,
        vendorContractDuration: rfqLastVendorResponses.vendorContractDuration,
        vendorTaxCode: rfqLastVendorResponses.vendorTaxCode,
        vendorPlaceOfShipping: rfqLastVendorResponses.vendorPlaceOfShipping,
        vendorPlaceOfDestination: rfqLastVendorResponses.vendorPlaceOfDestination,

        // 초도품/Spare part/연동제 응답
        vendorFirstYn: rfqLastVendorResponses.vendorFirstYn,
        vendorFirstDescription: rfqLastVendorResponses.vendorFirstDescription,
        vendorFirstAcceptance: rfqLastVendorResponses.vendorFirstAcceptance,
        vendorSparepartYn: rfqLastVendorResponses.vendorSparepartYn,
        vendorSparepartDescription: rfqLastVendorResponses.vendorSparepartDescription,
        vendorSparepartAcceptance: rfqLastVendorResponses.vendorSparepartAcceptance,
        vendorMaterialPriceRelatedYn: rfqLastVendorResponses.vendorMaterialPriceRelatedYn,
        vendorMaterialPriceRelatedReason: rfqLastVendorResponses.vendorMaterialPriceRelatedReason,

        // 변경 사유
        currencyReason: rfqLastVendorResponses.currencyReason,
        paymentTermsReason: rfqLastVendorResponses.paymentTermsReason,
        deliveryDateReason: rfqLastVendorResponses.deliveryDateReason,
        incotermsReason: rfqLastVendorResponses.incotermsReason,
        taxReason: rfqLastVendorResponses.taxReason,
        shippingReason: rfqLastVendorResponses.shippingReason,

        // 비고
        generalRemark: rfqLastVendorResponses.generalRemark,
        technicalProposal: rfqLastVendorResponses.technicalProposal,

        // 타임스탬프
        createdAt: rfqLastVendorResponses.createdAt,
        updatedAt: rfqLastVendorResponses.updatedAt,
      })
      .from(rfqLastVendorResponses)
      .leftJoin(vendors, eq(rfqLastVendorResponses.vendorId, vendors.id))
      .leftJoin(users, eq(rfqLastVendorResponses.submittedBy, users.id))
      .where(
        and(
          eq(rfqLastVendorResponses.rfqsLastId, rfqId),
          eq(rfqLastVendorResponses.isLatest, true) // 최신 버전만 조회
        )
      )
      .orderBy(desc(rfqLastVendorResponses.createdAt));

    if (!vendorResponsesData || vendorResponsesData.length === 0) {
      return {
        success: true,
        data: [],
        rfq: rfqData[0],
        details: details,
      };
    }

    // 4. 각 벤더별 총 응답 수 조회 (모든 버전 포함)
    const vendorResponseCounts = await db
      .select({
        vendorId: rfqLastVendorResponses.vendorId,
        responseCount: count(),
      })
      .from(rfqLastVendorResponses)
      .where(eq(rfqLastVendorResponses.rfqsLastId, rfqId))
      .groupBy(rfqLastVendorResponses.vendorId);

    // vendorId를 키로 하는 Map 생성
    const responseCountMap = new Map(
      vendorResponseCounts.map(item => [item.vendorId, item.responseCount])
    );

    // 5. 각 벤더 응답별 상세 정보 조회 (견적 아이템, 첨부파일)
    const vendorResponsesWithDetails = await Promise.all(
      vendorResponsesData.map(async (response) => {
        // 견적 아이템 상세 조회
        const quotationItems = await db
          .select({
            id: rfqLastVendorQuotationItems.id,
            vendorResponseId: rfqLastVendorQuotationItems.vendorResponseId,
            rfqPrItemId: rfqLastVendorQuotationItems.rfqPrItemId,

            // PR 아이템 정보
            prNo: rfqLastVendorQuotationItems.prNo,
            materialCode: rfqLastVendorQuotationItems.materialCode,
            materialDescription: rfqLastVendorQuotationItems.materialDescription,

            // 견적 정보
            quantity: rfqLastVendorQuotationItems.quantity,
            uom: rfqLastVendorQuotationItems.uom,
            unitPrice: rfqLastVendorQuotationItems.unitPrice,
            totalPrice: rfqLastVendorQuotationItems.totalPrice,
            currency: rfqLastVendorQuotationItems.currency,

            // 납기 정보
            vendorDeliveryDate: rfqLastVendorQuotationItems.vendorDeliveryDate,
            leadTime: rfqLastVendorQuotationItems.leadTime,

            // 제조사 정보
            manufacturer: rfqLastVendorQuotationItems.manufacturer,
            manufacturerCountry: rfqLastVendorQuotationItems.manufacturerCountry,
            modelNo: rfqLastVendorQuotationItems.modelNo,

            // 기술 사양
            technicalCompliance: rfqLastVendorQuotationItems.technicalCompliance,
            alternativeProposal: rfqLastVendorQuotationItems.alternativeProposal,

            // 할인 정보
            discountRate: rfqLastVendorQuotationItems.discountRate,
            discountAmount: rfqLastVendorQuotationItems.discountAmount,

            // 비고
            itemRemark: rfqLastVendorQuotationItems.itemRemark,
            deviationReason: rfqLastVendorQuotationItems.deviationReason,

            createdAt: rfqLastVendorQuotationItems.createdAt,
            updatedAt: rfqLastVendorQuotationItems.updatedAt,
          })
          .from(rfqLastVendorQuotationItems)
          .where(eq(rfqLastVendorQuotationItems.vendorResponseId, response.id))
          .orderBy(rfqLastVendorQuotationItems.id);

        // 첨부파일 조회
        const attachments = await db
          .select({
            id: rfqLastVendorAttachments.id,
            vendorResponseId: rfqLastVendorAttachments.vendorResponseId,

            // 첨부파일 구분
            attachmentType: rfqLastVendorAttachments.attachmentType,
            documentNo: rfqLastVendorAttachments.documentNo,

            // 파일 정보
            fileName: rfqLastVendorAttachments.fileName,
            originalFileName: rfqLastVendorAttachments.originalFileName,
            filePath: rfqLastVendorAttachments.filePath,
            fileSize: rfqLastVendorAttachments.fileSize,
            fileType: rfqLastVendorAttachments.fileType,

            // 파일 설명
            description: rfqLastVendorAttachments.description,

            // 유효기간 (인증서 등)
            validFrom: rfqLastVendorAttachments.validFrom,
            validTo: rfqLastVendorAttachments.validTo,

            // 업로드 정보
            uploadedBy: rfqLastVendorAttachments.uploadedBy,
            uploadedByName: users.name,
            uploadedAt: rfqLastVendorAttachments.uploadedAt,
          })
          .from(rfqLastVendorAttachments)
          .leftJoin(users, eq(rfqLastVendorAttachments.uploadedBy, users.id))
          .where(eq(rfqLastVendorAttachments.vendorResponseId, response.id))
          .orderBy(rfqLastVendorAttachments.attachmentType, rfqLastVendorAttachments.uploadedAt);

        // 해당 벤더의 총 응답 수 가져오기
        const vendorResponseCount = responseCountMap.get(response.vendorId) || 0;

        return {
          ...response,
          quotationItems,
          attachments,
          vendorResponseCount,
        };
      })
    );

    // 6. 응답 데이터 정리
    const formattedResponses = vendorResponsesWithDetails
      .filter(response => response && response.id)
      .map(response => ({
        id: response.id,
        rfqsLastId: response.rfqsLastId,
        rfqLastDetailsId: response.rfqLastDetailsId,
        responseVersion: response.responseVersion,
        isLatest: response.isLatest,
        status: response.status,
        isDocumentConfirmed: response.isDocumentConfirmed,

        // 벤더 정보
        vendor: {
          id: response.vendorId,
          code: response.vendorCode,
          name: response.vendorName,
          email: response.vendorEmail,
          responseCount: response.vendorResponseCount,
        },

        // 제출 정보
        submission: {
          submittedAt: response.submittedAt,
          submittedBy: response.submittedBy,
          submittedByName: response.submittedByName,
        },

        // 제출 정보
        attend: {
          participationStatus: response.participationStatus,
          participationRepliedAt: response.participationRepliedAt,
          participationRepliedBy: response.participationRepliedBy,
          nonParticipationReason: response.nonParticipationReason,
        },

        // 금액 정보
        pricing: {
          totalAmount: response.totalAmount,
          currency: response.currency || "USD",
          vendorCurrency: response.vendorCurrency,
        },

        // 벤더 제안 조건
        vendorTerms: {
          paymentTermsCode: response.vendorPaymentTermsCode,
          incotermsCode: response.vendorIncotermsCode,
          incotermsDetail: response.vendorIncotermsDetail,
          deliveryDate: response.vendorDeliveryDate,
          contractDuration: response.vendorContractDuration,
          taxCode: response.vendorTaxCode,
          placeOfShipping: response.vendorPlaceOfShipping,
          placeOfDestination: response.vendorPlaceOfDestination,
        },

        // 초도품/Spare part/연동제
        additionalRequirements: {
          firstArticle: {
            required: response.vendorFirstYn,
            description: response.vendorFirstDescription,
            acceptance: response.vendorFirstAcceptance,
          },
          sparePart: {
            required: response.vendorSparepartYn,
            description: response.vendorSparepartDescription,
            acceptance: response.vendorSparepartAcceptance,
          },
          materialPriceRelated: {
            required: response.vendorMaterialPriceRelatedYn,
            reason: response.vendorMaterialPriceRelatedReason,
          },
        },

        // 변경 사유
        changeReasons: {
          currency: response.currencyReason,
          paymentTerms: response.paymentTermsReason,
          deliveryDate: response.deliveryDateReason,
          incoterms: response.incotermsReason,
          tax: response.taxReason,
          shipping: response.shippingReason,
        },

        // 카운트 정보
        counts: {
          quotedItems: response.quotationItems.length,
          attachments: response.attachments.length,
        },

        // 비고
        remarks: {
          general: response.generalRemark,
          technical: response.technicalProposal,
        },

        // 견적 아이템 상세
        quotationItems: response.quotationItems.map(item => ({
          id: item.id,
          rfqPrItemId: item.rfqPrItemId,
          prNo: item.prNo,
          materialCode: item.materialCode,
          materialDescription: item.materialDescription,
          quantity: item.quantity,
          uom: item.uom,
          unitPrice: item.unitPrice,
          totalPrice: item.totalPrice,
          currency: item.currency,
          vendorDeliveryDate: item.vendorDeliveryDate,
          leadTime: item.leadTime,
          manufacturer: item.manufacturer,
          manufacturerCountry: item.manufacturerCountry,
          modelNo: item.modelNo,
          technicalCompliance: item.technicalCompliance,
          alternativeProposal: item.alternativeProposal,
          discountRate: item.discountRate,
          discountAmount: item.discountAmount,
          itemRemark: item.itemRemark,
          deviationReason: item.deviationReason,
        })),

        // 첨부파일 상세
        attachments: response.attachments.map(file => ({
          id: file.id,
          attachmentType: file.attachmentType,
          documentNo: file.documentNo,
          fileName: file.fileName,
          originalFileName: file.originalFileName,
          filePath: file.filePath, // 파일 경로 포함
          fileSize: file.fileSize,
          fileType: file.fileType,
          description: file.description,
          validFrom: file.validFrom,
          validTo: file.validTo,
          uploadedBy: file.uploadedBy,
          uploadedByName: file.uploadedByName,
          uploadedAt: file.uploadedAt,
        })),

        // 타임스탬프
        timestamps: {
          createdAt: response.createdAt,
          updatedAt: response.updatedAt,
        },
      }));

    return {
      success: true,
      data: formattedResponses,
      rfq: rfqData[0],
      details: details,
    };

  } catch (error) {
    console.error("Failed to get vendor responses:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "벤더 응답 정보를 가져오는데 실패했습니다.",
      data: null,
    };
  }
}

export async function getRfqWithDetails(rfqId: number) {
  try {
    // 1. RFQ 기본 정보 조회 (rfqsLastView 활용)
    const [rfqData] = await db
      .select()
      .from(rfqsLastView)
      .where(eq(rfqsLastView.id, rfqId));

    if (!rfqData) {
      return { success: false, error: "RFQ를 찾을 수 없습니다." };
    }

    // 2. 벤더별 상세 조건 조회 (rfqLastDetailsView 활용)
    const details = await db
      .select()
      .from(rfqLastDetailsView)
      .where(
        and(
          eq(rfqLastDetailsView.rfqId, rfqId),
          eq(rfqLastDetailsView.isLatest, true)  // isLatest 필터 추가
        )
      )
      .orderBy(desc(rfqLastDetailsView.detailId));

      const tbeSessionsData = await db
      .select({
        vendorId: rfqLastTbeSessions.vendorId,
        sessionCode: rfqLastTbeSessions.sessionCode,
        status: rfqLastTbeSessions.status,
        evaluationResult: rfqLastTbeSessions.evaluationResult,
        conditionalRequirements: rfqLastTbeSessions.conditionalRequirements,
        conditionsFulfilled: rfqLastTbeSessions.conditionsFulfilled,
        plannedStartDate: rfqLastTbeSessions.plannedStartDate,
        actualStartDate: rfqLastTbeSessions.actualStartDate,
        actualEndDate: rfqLastTbeSessions.actualEndDate,
      })
      .from(rfqLastTbeSessions)
      .where(eq(rfqLastTbeSessions.rfqsLastId, rfqId));

      const tbeByVendor = tbeSessionsData.reduce((acc, tbe) => {
        acc[tbe.vendorId] = tbe;
        return acc;
      }, {} as Record<number, typeof tbeSessionsData[0]>);

    return {
      success: true,
      data: {
        // RFQ 기본 정보 (rfqsLastView에서 제공)
        id: rfqData.id,
        rfqCode: rfqData.rfqCode,
        rfqType: rfqData.rfqType,
        rfqTitle: rfqData.rfqTitle,
        series: rfqData.series,
        rfqSealedYn: rfqData.rfqSealedYn,

        // ITB 관련
        projectCompany: rfqData.projectCompany,
        projectFlag: rfqData.projectFlag,
        projectSite: rfqData.projectSite,
        smCode: rfqData.smCode,

        // PR 정보
        prNumber: rfqData.prNumber,
        prIssueDate: rfqData.prIssueDate,

        // 프로젝트 정보
        projectId: rfqData.projectId,
        projectCode: rfqData.projectCode,
        projectName: rfqData.projectName,

        // 아이템 정보
        itemCode: rfqData.itemCode,
        itemName: rfqData.itemName,

        // 패키지 정보
        packageNo: rfqData.packageNo,
        packageName: rfqData.packageName,

        // 날짜 및 상태
        dueDate: rfqData.dueDate,
        rfqSendDate: rfqData.rfqSendDate,
        status: rfqData.status,

        // PIC 정보
        picId: rfqData.picId,
        picCode: rfqData.picCode,
        picName: rfqData.picName,
        picUserName: rfqData.picUserName,
        engPicName: rfqData.engPicName,

        // 집계 정보 (View에서 이미 계산됨)
        vendorCount: rfqData.vendorCount,
        shortListedVendorCount: rfqData.shortListedVendorCount,
        quotationReceivedCount: rfqData.quotationReceivedCount,
        prItemsCount: rfqData.prItemsCount,
        majorItemsCount: rfqData.majorItemsCount,

        // 견적 제출 정보
        earliestQuotationSubmittedAt: rfqData.earliestQuotationSubmittedAt,

        // Major Item 정보
        majorItemMaterialCode: rfqData.majorItemMaterialCode,
        majorItemMaterialDescription: rfqData.majorItemMaterialDescription,
        majorItemMaterialCategory: rfqData.majorItemMaterialCategory,
        majorItemPrNo: rfqData.majorItemPrNo,

        // 감사 정보
        createdBy: rfqData.createdBy,
        createdByUserName: rfqData.createdByUserName,
        createdAt: rfqData.createdAt,
        sentBy: rfqData.sentBy,
        sentByUserName: rfqData.sentByUserName,
        updatedBy: rfqData.updatedBy,
        updatedByUserName: rfqData.updatedByUserName,
        updatedAt: rfqData.updatedAt,

        // 비고
        remark: rfqData.remark,

        // 벤더별 상세 조건 (rfqLastDetailsView에서 제공)
        details: details.map(d => ({
          detailId: d.detailId,

          // 벤더 정보
          vendorId: d.vendorId,
          vendorName: d.vendorName,
          vendorCode: d.vendorCode,
          vendorCountry: d.vendorCountry,

          // 조건 정보
          currency: d.currency,
          paymentTermsCode: d.paymentTermsCode,
          paymentTermsDescription: d.paymentTermsDescription,
          incotermsCode: d.incotermsCode,
          incotermsDescription: d.incotermsDescription,
          incotermsDetail: d.incotermsDetail,
          deliveryDate: d.deliveryDate,
          contractDuration: d.contractDuration,
          taxCode: d.taxCode,
          placeOfShipping: d.placeOfShipping,
          placeOfDestination: d.placeOfDestination,

          // Boolean 필드들
          shortList: d.shortList,
          returnYn: d.returnYn,
          returnedAt: d.returnedAt,
          prjectGtcYn: d.prjectGtcYn,
          generalGtcYn: d.generalGtcYn,
          ndaYn: d.ndaYn,
          agreementYn: d.agreementYn,
          materialPriceRelatedYn: d.materialPriceRelatedYn,
          sparepartYn: d.sparepartYn,
          firstYn: d.firstYn,

          // 설명 필드
          firstDescription: d.firstDescription,
          sparepartDescription: d.sparepartDescription,
          remark: d.remark,
          cancelReason: d.cancelReason,

          // 견적 관련 정보 (View에서 이미 계산됨)
          hasQuotation: d.hasQuotation,
          quotationStatus: d.quotationStatus,
          quotationTotalPrice: d.quotationTotalPrice,
          quotationVersion: d.quotationVersion,
          quotationVersionCount: d.quotationVersionCount,
          lastQuotationDate: d.lastQuotationDate,
          quotationSubmittedAt: d.quotationSubmittedAt,

          // 감사 정보
          updatedBy: d.updatedBy,
          updatedByUserName: d.updatedByUserName,
          updatedAt: d.updatedAt,

          sendVersion: d.sendVersion,
          emailSentAt: d.emailSentAt,
          emailSentTo: d.emailSentTo,
          emailResentCount: d.emailResentCount,
          lastEmailSentAt: d.lastEmailSentAt,
          emailStatus: d.emailStatus,

           // TBE 정보 추가
           tbeSession: d.vendorId ? tbeByVendor[d.vendorId] : null,
           tbeStatus: d.vendorId ? tbeByVendor[d.vendorId]?.status : null,
           tbeEvaluationResult: d.vendorId ? tbeByVendor[d.vendorId]?.evaluationResult : null,
           tbeSessionCode: d.vendorId ? tbeByVendor[d.vendorId]?.sessionCode : null,
        })),
      }
    };
  } catch (error) {
    console.error("Get RFQ with details error:", error);
    return { success: false, error: "데이터 조회 중 오류가 발생했습니다." };
  }
}


// RFQ 정보 타입
export interface RfqFullInfo {
  // 기본 RFQ 정보
  id: number;
  rfqCode: string;
  rfqType: string | null;
  rfqTitle: string | null;
  series: string | null;
  rfqSealedYn: boolean | null;

  // ITB 관련
  projectCompany: string | null;
  projectFlag: string | null;
  projectSite: string | null;
  smCode: string | null;

  // RFQ 추가 필드
  prNumber: string | null;
  prIssueDate: Date | null;

  // 프로젝트 정보
  projectId: number | null;
  projectCode: string | null;
  projectName: string | null;

  // 아이템 정보
  itemCode: string | null;
  itemName: string | null;

  // 패키지 정보
  packageNo: string | null;
  packageName: string | null;

  // 날짜 정보
  dueDate: Date | null;
  rfqSendDate: Date | null;

  // 상태
  status: string;

  // 담당자 정보
  picId: number | null;
  picCode: string | null;
  picName: string | null;
  picUserName: string | null;
  picTeam: string | null;

  // 설계담당자
  engPicName: string | null;
  designTeam: string | null;

  // 자재그룹 정보 (PR Items에서)
  materialGroup: string | null;
  materialGroupDesc: string | null;

  // 카운트 정보
  vendorCount: number;
  shortListedVendorCount: number;
  quotationReceivedCount: number;
  prItemsCount: number;
  majorItemsCount: number;

  // 감사 정보
  createdBy: number;
  createdByUserName: string | null;
  createdAt: Date;
  updatedBy: number;
  updatedByUserName: string | null;
  updatedAt: Date;

  sentBy: number | null;
  sentByUserName: string | null;

  remark: string | null;

  // 평가 적용 여부 (추가 필드)
  evaluationApply?: boolean;
  quotationType?: string;
  contractType?: string;

  // 연관 데이터
  vendors: VendorDetail[];
  attachments: AttachmentInfo[];
}

// 벤더 상세 정보
export interface VendorDetail {
  detailId: number;
  vendorId: number | null;
  vendorName: string | null;
  vendorCode: string | null;
  vendorCountry: string | null;
  vendorEmail?: string | null;
  vendorCategory?: string | null;
  vendorGrade?: string | null;
  basicContract?: string | null;

  // RFQ 조건
  currency: string | null;
  paymentTermsCode: string | null;
  paymentTermsDescription: string | null;
  incotermsCode: string | null;
  incotermsDescription: string | null;
  incotermsDetail: string | null;
  deliveryDate: Date | null;
  contractDuration: string | null;
  taxCode: string | null;
  placeOfShipping: string | null;
  placeOfDestination: string | null;

  // 상태
  shortList: boolean;
  returnYn: boolean;
  returnedAt: Date | null;

  // GTC/NDA
  prjectGtcYn: boolean;
  generalGtcYn: boolean;
  ndaYn: boolean;
  agreementYn: boolean;

  // 추가 조건
  materialPriceRelatedYn: boolean | null;
  sparepartYn: boolean | null;
  firstYn: boolean | null;
  firstDescription: string | null;
  sparepartDescription: string | null;

  remark: string | null;
  cancelReason: string | null;

  // 회신 상태
  quotationStatus?: string | null;
  quotationSubmittedAt?: Date | null;

  // 업데이트 정보
  updatedBy: number;
  updatedByUserName: string | null;
  updatedAt: Date | null;
}

// 첨부파일 정보
export interface AttachmentInfo {
  id: number;
  attachmentType: string;
  serialNo: string;
  currentRevision: string;
  description: string | null;

  // 최신 리비전 정보
  fileName: string | null;
  originalFileName: string | null;
  filePath: string | null;
  fileSize: number | null;
  fileType: string | null;

  createdBy: number;
  createdByUserName: string | null;
  createdAt: Date;
  updatedAt: Date;
}

/**
 * RFQ 전체 정보 조회
 */
export async function getRfqFullInfo(rfqId: number): Promise<RfqFullInfo> {
  try {
    // 1. RFQ 기본 정보 조회
    const rfqData = await db
      .select({
        rfq: rfqsLast,
        picUser: users,
      })
      .from(rfqsLast)
      .leftJoin(users, eq(rfqsLast.pic, users.id))
      .where(eq(rfqsLast.id, rfqId))
      .limit(1);

    if (!rfqData.length) {
      throw new Error(`RFQ ID ${rfqId}를 찾을 수 없습니다.`);
    }

    const rfq = rfqData[0].rfq;
    const picUser = rfqData[0].picUser;

    // 2. PR Items에서 자재그룹 정보 조회 (Major Item)
    const prItemsData = await db
      .select({
        materialCategory: rfqPrItems.materialCategory,
        materialDescription: rfqPrItems.materialDescription,
        prItemsCount: eq(rfqPrItems.majorYn, true),
      })
      .from(rfqPrItems)
      .where(and(
        eq(rfqPrItems.rfqsLastId, rfqId),
        eq(rfqPrItems.majorYn, true)
      ))
      .limit(1);

    const majorItem = prItemsData[0];

    // 3. 벤더 정보 조회
    const vendorsData = await db
      .select({
        detail: rfqLastDetails,
        vendor: vendors,
        paymentTerms: paymentTerms,
        incoterms: incoterms,
        updatedByUser: users,
      })
      .from(rfqLastDetails)
      .leftJoin(vendors, eq(rfqLastDetails.vendorsId, vendors.id))
      .leftJoin(paymentTerms, eq(rfqLastDetails.paymentTermsCode, paymentTerms.code))
      .leftJoin(incoterms, eq(rfqLastDetails.incotermsCode, incoterms.code))
      .leftJoin(users, eq(rfqLastDetails.updatedBy, users.id))
      .where(eq(rfqLastDetails.rfqsLastId, rfqId));

    const vendorDetails: VendorDetail[] = vendorsData.map(v => ({
      detailId: v.detail.id,
      vendorId: v.vendor?.id ?? null,
      vendorName: v.vendor?.vendorName ?? null,
      vendorCode: v.vendor?.vendorCode ?? null,
      vendorCountry: v.vendor?.country ?? null,
      vendorEmail: v.vendor?.email ?? null,
      vendorCategory: v.vendor?.vendorCategory ?? null,
      vendorGrade: v.vendor?.vendorGrade ?? null,
      basicContract: v.vendor?.basicContract ?? null,

      currency: v.detail.currency,
      paymentTermsCode: v.detail.paymentTermsCode,
      paymentTermsDescription: v.paymentTerms?.description ?? null,
      incotermsCode: v.detail.incotermsCode,
      incotermsDescription: v.incoterms?.description ?? null,
      incotermsDetail: v.detail.incotermsDetail,
      deliveryDate: v.detail.deliveryDate,
      contractDuration: v.detail.contractDuration,
      taxCode: v.detail.taxCode,
      placeOfShipping: v.detail.placeOfShipping,
      placeOfDestination: v.detail.placeOfDestination,

      shortList: v.detail.shortList,
      returnYn: v.detail.returnYn,
      returnedAt: v.detail.returnedAt,

      prjectGtcYn: v.detail.prjectGtcYn,
      generalGtcYn: v.detail.generalGtcYn,
      ndaYn: v.detail.ndaYn,
      agreementYn: v.detail.agreementYn,

      materialPriceRelatedYn: v.detail.materialPriceRelatedYn,
      sparepartYn: v.detail.sparepartYn,
      firstYn: v.detail.firstYn,
      firstDescription: v.detail.firstDescription,
      sparepartDescription: v.detail.sparepartDescription,

      remark: v.detail.remark,
      cancelReason: v.detail.cancelReason,

      updatedBy: v.detail.updatedBy,
      updatedByUserName: v.updatedByUser?.name ?? null,
      updatedAt: v.detail.updatedAt,
    }));

    // 4. 첨부파일 정보 조회
    const attachmentsData = await db
      .select({
        attachment: rfqLastAttachments,
        revision: rfqLastAttachmentRevisions,
        createdByUser: users,
      })
      .from(rfqLastAttachments)
      .leftJoin(
        rfqLastAttachmentRevisions,
        and(
          eq(rfqLastAttachments.latestRevisionId, rfqLastAttachmentRevisions.id),
          eq(rfqLastAttachmentRevisions.isLatest, true)
        )
      )
      .leftJoin(users, eq(rfqLastAttachments.createdBy, users.id))
      .where(eq(rfqLastAttachments.rfqId, rfqId));

    const attachments: AttachmentInfo[] = attachmentsData.map(a => ({
      id: a.attachment.id,
      attachmentType: a.attachment.attachmentType,
      serialNo: a.attachment.serialNo,
      currentRevision: a.attachment.currentRevision,
      description: a.attachment.description,

      fileName: a.revision?.fileName ?? null,
      originalFileName: a.revision?.originalFileName ?? null,
      filePath: a.revision?.filePath ?? null,
      fileSize: a.revision?.fileSize ?? null,
      fileType: a.revision?.fileType ?? null,

      createdBy: a.attachment.createdBy,
      createdByUserName: a.createdByUser?.name ?? null,
      createdAt: a.attachment.createdAt,
      updatedAt: a.attachment.updatedAt,
    }));

    // 5. 카운트 정보 계산
    const vendorCount = vendorDetails.length;
    const shortListedVendorCount = vendorDetails.filter(v => v.shortList).length;
    const quotationReceivedCount = vendorDetails.filter(v => v.quotationSubmittedAt).length;

    // PR Items 카운트 (별도 쿼리 필요)
    const prItemsCount = await db
      .select({ count: sql<number>`COUNT(*)` })
      .from(rfqPrItems)
      .where(eq(rfqPrItems.rfqsLastId, rfqId));

    const majorItemsCount = await db
      .select({ count: sql<number>`COUNT(*)` })
      .from(rfqPrItems)
      .where(and(
        eq(rfqPrItems.rfqsLastId, rfqId),
        eq(rfqPrItems.majorYn, true)
      ));

    // 6. 사용자 정보 조회 (createdBy, updatedBy, sentBy)
    const [createdByUser] = await db
      .select({ name: users.name })
      .from(users)
      .where(eq(users.id, rfq.createdBy))
      .limit(1);

    const [updatedByUser] = await db
      .select({ name: users.name })
      .from(users)
      .where(eq(users.id, rfq.updatedBy))
      .limit(1);

    const [sentByUser] = rfq.sentBy
      ? await db
        .select({ name: users.name })
        .from(users)
        .where(eq(users.id, rfq.sentBy))
        .limit(1)
      : [null];

    // 7. 전체 정보 조합
    const rfqFullInfo: RfqFullInfo = {
      // 기본 정보
      id: rfq.id,
      rfqCode: rfq.rfqCode ?? '',
      rfqType: rfq.rfqType,
      rfqTitle: rfq.rfqTitle,
      series: rfq.series,
      rfqSealedYn: rfq.rfqSealedYn,

      // ITB 관련
      projectCompany: rfq.projectCompany,
      projectFlag: rfq.projectFlag,
      projectSite: rfq.projectSite,
      smCode: rfq.smCode,

      // RFQ 추가 필드
      prNumber: rfq.prNumber,
      prIssueDate: rfq.prIssueDate,

      // 프로젝트
      projectId: rfq.projectId,
      projectCode: null, // 프로젝트 조인 필요시 추가
      projectName: null, // 프로젝트 조인 필요시 추가

      // 아이템
      itemCode: rfq.itemCode,
      itemName: rfq.itemName,

      // 패키지
      packageNo: rfq.packageNo,
      packageName: rfq.packageName,

      // 날짜
      dueDate: rfq.dueDate,
      rfqSendDate: rfq.rfqSendDate,

      // 상태
      status: rfq.status,

      // 구매 담당자
      picId: rfq.pic,
      picCode: rfq.picCode,
      picName: rfq.picName,
      picUserName: picUser?.name ?? null,
      picTeam: picUser?.department ?? null, // users 테이블에 department 필드가 있다고 가정

      // 설계 담당자
      engPicName: rfq.EngPicName,
      designTeam: null, // 추가 정보 필요시 입력

      // 자재그룹 (PR Items에서)
      materialGroup: majorItem?.materialCategory ?? null,
      materialGroupDesc: majorItem?.materialDescription ?? null,

      // 카운트
      vendorCount,
      shortListedVendorCount,
      quotationReceivedCount,
      prItemsCount: prItemsCount[0]?.count ?? 0,
      majorItemsCount: majorItemsCount[0]?.count ?? 0,

      // 감사 정보
      createdBy: rfq.createdBy,
      createdByUserName: createdByUser?.name ?? null,
      createdAt: rfq.createdAt,
      updatedBy: rfq.updatedBy,
      updatedByUserName: updatedByUser?.name ?? null,
      updatedAt: rfq.updatedAt,
      sentBy: rfq.sentBy,
      sentByUserName: sentByUser?.name ?? null,

      remark: rfq.remark,

      // 추가 필드 (필요시)
      evaluationApply: true, // 기본값 또는 별도 로직
      quotationType: rfq.rfqType ?? undefined,
      contractType: undefined, // 별도 필드 필요

      // 연관 데이터
      vendors: vendorDetails,
      attachments: attachments,
    };

    return rfqFullInfo;
  } catch (error) {
    console.error("RFQ 정보 조회 실패:", error);
    throw error;
  }
}
/**
 * RFQ 발송용 이메일 템플릿 자동 선택
 */
export async function getRfqEmailTemplate(): Promise<{ slug: string; name: string; category: string } | null> {
  try {
    // 1. 템플릿 목록 조회
    const templates = await db
      .select({
        slug: templateDetailView.slug,
        name: templateDetailView.name,
        category: templateDetailView.category,
        isActive: templateDetailView.isActive,
      })
      .from(templateDetailView)
      .where(eq(templateDetailView.isActive, true))
      .orderBy(templateDetailView.name);

    // 2. RFQ 또는 견적 관련 템플릿 찾기 (우선순위: category > name)
    let selectedTemplate = null;

    // 우선 category가 'rfq' 또는 'quotation'인 템플릿 찾기
    selectedTemplate = templates.find(t =>
      t.category === 'rfq' || t.category === 'quotation'
    );

    // 없으면 이름에 '견적' 또는 'rfq'가 포함된 템플릿 찾기
    if (!selectedTemplate) {
      selectedTemplate = templates.find(t =>
        t.name.toLowerCase().includes('견적') ||
        t.name.toLowerCase().includes('rfq') ||
        t.name.toLowerCase().includes('quotation')
      );
    }

    return selectedTemplate || null;
  } catch (error) {
    console.error("RFQ 이메일 템플릿 조회 실패:", error);
    return null;
  }
}
/**
 * SendRfqDialog용 간단한 정보 조회
 */
export async function getRfqInfoForSend(rfqId: number) {
  const fullInfo = await getRfqFullInfo(rfqId);

  return {
    rfqCode: fullInfo.rfqCode,
    rfqTitle: fullInfo.rfqTitle || '',
    rfqType: fullInfo.rfqType || '',
    projectCode: fullInfo.projectCode,
    projectName: fullInfo.projectName,
    picName: fullInfo.picName,
    picCode: fullInfo.picCode,
    picTeam: fullInfo.picTeam,
    packageNo: fullInfo.packageNo,
    packageName: fullInfo.packageName,
    designPicName: fullInfo.engPicName, // EngPicName이 설계담당자
    designTeam: fullInfo.designTeam,
    materialGroup: fullInfo.materialGroup,
    materialGroupDesc: fullInfo.materialGroupDesc,
    dueDate: fullInfo.dueDate || new Date(),
    quotationType: fullInfo.quotationType,
    evaluationApply: fullInfo.evaluationApply,
    contractType: fullInfo.contractType,
  };
}

/**
 * 벤더 정보만 조회
 */
export async function getRfqVendors(rfqId: number) {
  const fullInfo = await getRfqFullInfo(rfqId);
  return fullInfo.vendors;
}

/**
 * 첨부파일 정보만 조회
 */
export async function getRfqAttachments(rfqId: number) {
  const fullInfo = await getRfqFullInfo(rfqId);
  return fullInfo.attachments;
}

/**
 * 특정 벤더의 현재 조건 조회
 */
export async function getVendorConditions(rfqId: number, vendorId: number) {
  const fullInfo = await getRfqFullInfo(rfqId);
  const vendor = fullInfo.vendors?.find(v => v.vendorId === vendorId);

  if (!vendor) {
    throw new Error('벤더 정보를 찾을 수 없습니다.');
  }

  return {
    currency: vendor.currency,
    paymentTermsCode: vendor.paymentTermsCode,
    incotermsCode: vendor.incotermsCode,
    incotermsDetail: vendor.incotermsDetail,
    deliveryDate: vendor.deliveryDate,
    contractDuration: vendor.contractDuration,
    taxCode: vendor.taxCode,
    placeOfShipping: vendor.placeOfShipping,
    placeOfDestination: vendor.placeOfDestination,
    materialPriceRelatedYn: vendor.materialPriceRelatedYn,
    sparepartYn: vendor.sparepartYn,
    firstYn: vendor.firstYn,
    firstDescription: vendor.firstDescription,
    sparepartDescription: vendor.sparepartDescription,
  };
}


// RFQ 발송용 데이터 타입
export interface RfqSendData {
  rfqInfo: {
    rfqCode: string;
    rfqTitle: string;
    rfqType: string;
    projectCode?: string;
    projectName?: string;
    picName?: string;
    picCode?: string;
    picTeam?: string;
    packageNo?: string;
    packageName?: string;
    designPicName?: string;
    designTeam?: string;
    materialGroup?: string;
    materialGroupDesc?: string;
    dueDate: Date;
    quotationType?: string;
    evaluationApply?: boolean;
    contractType?: string;
  };
  attachments: Array<{
    id: number;
    attachmentType: string;
    serialNo: string;
    currentRevision: string;
    description?: string | null;
    fileName?: string | null;
    fileSize?: number | null;
    uploadedAt?: Date;
  }>;
}

// 선택된 벤더의 이메일 정보 조회
export interface VendorEmailInfo {
  vendorId: number;
  vendorName: string;
  vendorCode?: string | null;
  vendorCountry?: string | null;
  vendorEmail?: string | null; // vendors 테이블의 기본 이메일
  representativeEmail?: string | null; // 대표자 이메일
  contactEmails: string[]; // 영업/대표 담당자 이메일들
  primaryEmail?: string | null; // 최종 선택된 주 이메일
  currency?: string | null;
  currency?: string | null;
}

/**
 * RFQ 발송 다이얼로그용 데이터 조회
 */
export async function getRfqSendData(rfqId: number): Promise<RfqSendData> {
  try {
    // 1. RFQ 기본 정보 조회
    const [rfqData] = await db
      .select({
        rfq: rfqsLast,
        project: projects,
        picUser: users,
      })
      .from(rfqsLast)
      .leftJoin(projects, eq(rfqsLast.projectId, projects.id))
      .leftJoin(users, eq(rfqsLast.pic, users.id))
      .where(eq(rfqsLast.id, rfqId))
      .limit(1);

    if (!rfqData) {
      throw new Error(`RFQ ID ${rfqId}를 찾을 수 없습니다.`);
    }

    const { rfq, project, picUser } = rfqData;

    // 2. PR Items에서 자재그룹 정보 조회 (Major Item)
    const [majorItem] = await db
      .select({
        materialCategory: rfqPrItems.materialCategory,
        materialDescription: rfqPrItems.materialDescription,
      })
      .from(rfqPrItems)
      .where(and(
        eq(rfqPrItems.rfqsLastId, rfqId),
        eq(rfqPrItems.majorYn, true)
      ))
      .limit(1);

    // 3. 첨부파일 정보 조회
    const attachmentsData = await db
      .select({
        attachment: rfqLastAttachments,
        revision: rfqLastAttachmentRevisions,
      })
      .from(rfqLastAttachments)
      .leftJoin(
        rfqLastAttachmentRevisions,
        and(
          eq(rfqLastAttachments.latestRevisionId, rfqLastAttachmentRevisions.id),
          eq(rfqLastAttachmentRevisions.isLatest, true)
        )
      )
      .where(eq(rfqLastAttachments.rfqId, rfqId));

    const attachments = attachmentsData.map(a => ({
      id: a.attachment.id,
      attachmentType: a.attachment.attachmentType,
      serialNo: a.attachment.serialNo,
      currentRevision: a.attachment.currentRevision,
      description: a.attachment.description,
      fileName: a.revision?.originalFileName ?? null,
      fileSize: a.revision?.fileSize ?? null,
      uploadedAt: a.attachment.createdAt,
    }));

    // 4. RFQ 정보 조합
    const rfqInfo = {
      rfqCode: rfq.rfqCode || '',
      rfqTitle: rfq.rfqTitle || '',
      rfqType: rfq.rfqType || '',
      projectCode: project?.code || undefined,
      projectName: project?.name || undefined,
      picName: rfq.picName || undefined,
      picCode: rfq.picCode || undefined,
      picTeam: picUser?.deptName || undefined,
      packageNo: rfq.packageNo || undefined,
      packageName: rfq.packageName || undefined,
      designPicName: rfq.EngPicName || undefined,
      materialGroup: majorItem?.materialCategory || undefined,
      materialGroupDesc: majorItem?.materialDescription || undefined,
      dueDate: rfq.dueDate || new Date(),
      quotationType: rfq.rfqType || undefined,
      evaluationApply: true, // 기본값 또는 별도 필드
      contractType: undefined, // 필요시 추가
      // 시스템 정보
      formattedDueDate: rfq.dueDate ? rfq.dueDate.toLocaleDateString('ko-KR') : undefined,
      systemName: "SHI EVCP",
      hasAttachments: attachments.length > 0,
      attachmentsCount: attachments.length,
      language: "ko",
      companyName: "삼성중공업",
      now: new Date(),
    };

    return {
      rfqInfo,
      attachments,
    };
  } catch (error) {
    console.error("RFQ 발송 데이터 조회 실패:", error);
    throw error;
  }
}

interface ContactDetail {
  id: number;
  name: string;
  position?: string | null;
  department?: string | null;
  email: string;
  phone?: string | null;
  isPrimary: boolean;
}

/**
 * 벤더 이메일 정보 조회
 */
export async function getVendorEmailInfo(vendorIds: number[]): Promise<VendorEmailInfo[]> {
  try {
    // 1. 벤더 기본 정보 조회
    const vendorsData = await db
      .select({
        id: vendors.id,
        vendorName: vendors.vendorName,
        vendorCode: vendors.vendorCode,
        country: vendors.country,
        email: vendors.email,
        representativeEmail: vendors.representativeEmail,
      })
      .from(vendors)
      .where(sql`${vendors.id} IN ${vendorIds}`);

    // 2. 각 벤더의 모든 담당자 정보 조회
    const contactsData = await db
      .select({
        id: vendorContacts.id,
        vendorId: vendorContacts.vendorId,
        contactName: vendorContacts.contactName,
        contactPosition: vendorContacts.contactPosition,
        contactDepartment: vendorContacts.contactDepartment,
        contactEmail: vendorContacts.contactEmail,
        contactPhone: vendorContacts.contactPhone,
        isPrimary: vendorContacts.isPrimary,
      })
      .from(vendorContacts)
      .where(sql`${vendorContacts.vendorId} IN ${vendorIds}`);

    // 3. 데이터 조합
    const vendorEmailInfos: VendorEmailInfo[] = vendorsData.map(vendor => {
      const vendorContacts = contactsData.filter(c => c.vendorId === vendor.id);

      // ContactDetail 형식으로 변환
      const contacts: ContactDetail[] = vendorContacts.map(c => ({
        id: c.id,
        name: c.contactName,
        position: c.contactPosition,
        department: c.contactDepartment,
        email: c.contactEmail,
        phone: c.contactPhone,
        isPrimary: c.isPrimary,
      }));

      // 포지션별로 그룹화
      const contactsByPosition: Record<string, ContactDetail[]> = {};
      contacts.forEach(contact => {
        const position = contact.position || '기타';
        if (!contactsByPosition[position]) {
          contactsByPosition[position] = [];
        }
        contactsByPosition[position].push(contact);
      });

      // 주 이메일 선택 우선순위:
      // 1. isPrimary가 true인 담당자 이메일
      // 2. 대표자 이메일
      // 3. vendors 테이블의 기본 이메일
      // 4. 영업 담당자 이메일
      // 5. 첫번째 담당자 이메일
      const primaryContact = contacts.find(c => c.isPrimary);
      const salesContact = contacts.find(c => c.position === '영업');
      const primaryEmail =
        primaryContact?.email ||
        vendor.representativeEmail ||
        vendor.email ||
        salesContact?.email ||
        contacts[0]?.email ||
        null;

      return {
        vendorId: vendor.id,
        vendorName: vendor.vendorName,
        vendorCode: vendor.vendorCode,
        vendorCountry: vendor.country,
        vendorEmail: vendor.email,
        representativeEmail: vendor.representativeEmail,
        contacts,
        contactsByPosition,
        primaryEmail,
        currency: 'KRW', // 기본값, 필요시 별도 조회
      };
    });

    return vendorEmailInfos;
  } catch (error) {
    console.error("벤더 이메일 정보 조회 실패:", error);
    throw error;
  }
}

/**
 * 선택된 벤더들의 상세 정보 조회 (RFQ Detail 포함)
 */
export async function getSelectedVendorsWithEmails(
  rfqId: number,
  vendorIds: number[]
): Promise<Array<VendorEmailInfo & { currency?: string | null }>> {
  try {
    // 1. 벤더 이메일 정보 조회
    const vendorEmailInfos = await getVendorEmailInfo(vendorIds);

    // 2. RFQ Detail에서 통화 정보 조회 (옵션)
    const rfqDetailsData = await db
      .select({
        vendorId: rfqLastDetails.vendorsId,
        currency: rfqLastDetails.currency,
        ndaYn: rfqLastDetails.ndaYn,
        generalGtcYn: rfqLastDetails.generalGtcYn,
        projectGtcYn: rfqLastDetails.projectGtcYn,
        agreementYn: rfqLastDetails.agreementYn,
        sendVersion: rfqLastDetails.sendVersion,
      })
      .from(rfqLastDetails)
      .where(
        and(
          eq(rfqLastDetails.rfqsLastId, rfqId),
          eq(rfqLastDetails.isLatest, true),
          sql`${rfqLastDetails.vendorsId} IN ${vendorIds}`
        )
      );

    // 3. 통화 정보 병합
    const result = vendorEmailInfos.map(vendor => {
      const detail = rfqDetailsData.find(d => d.vendorId === vendor.vendorId);
      return {
        ...vendor,
        currency: detail?.currency || vendor.currency || 'KRW',
        ndaYn: detail?.ndaYn,
        generalGtcYn: detail?.generalGtcYn,
        projectGtcYn: detail?.projectGtcYn,
        agreementYn: detail?.agreementYn,
        sendVersion: detail?.sendVersion
      };
    });

    return result;
  } catch (error) {
    console.error("선택된 벤더 정보 조회 실패:", error);
    throw error;
  }
}

interface SendRfqVendor {
  vendorId: number;
  vendorName: string;
  vendorCode?: string | null;
  vendorCountry?: string | null;
  selectedMainEmail: string;
  additionalEmails: string[];
  customEmails: Array<{ id: string; email: string; name?: string }>;
}

export interface ContractRequirements {
  ndaYn: boolean;
  generalGtcYn: boolean;
  projectGtcYn: boolean;
  agreementYn: boolean;
  projectCode?: string; // Project GTC를 위한 프로젝트 코드
}

export interface VendorForSend {
  vendorId: number;
  vendorName: string;
  vendorCode?: string | null;
  vendorCountry?: string | null;
  selectedMainEmail: string;
  additionalEmails: string[];
  customEmails?: Array<{ email: string; name?: string }>;
  currency?: string | null;

  // 기본계약 관련
  contractRequirements?: ContractRequirements;

  // 재발송 관련
  isResend: boolean;
  sendVersion?: number;
}

export interface SendRfqParams {
  rfqId: number;
  rfqCode?: string;
  vendors: VendorForSend[];
  attachmentIds: number[];
  message?: string;
  generatedPdfs?: Array<{
    key: string;
    buffer: number[];
    fileName: string;
  }>;
  hasToSendEmail?: boolean; // 이메일 발송 여부
}

export async function sendRfqToVendors({
  rfqId,
  rfqCode,
  vendors,
  attachmentIds,
  message,
  generatedPdfs,
  hasToSendEmail = true
}: SendRfqParams) {
  const session = await getServerSession(authOptions);
  if (!session?.user) {
    throw new Error("인증이 필요합니다.");
  }

  const currentUser = session.user;

  try {
    // 1. RFQ 기본 정보 조회 (트랜잭션 외부)
    const rfqData = await getRfqData(rfqId);
    if (!rfqData) {
      throw new Error("RFQ 정보를 찾을 수 없습니다.");
    }

    // 2. PIC 정보 조회
    const picInfo = await getPicInfo(rfqData.picId, rfqData.picName);

    // 3. 프로젝트 정보 조회
    const projectInfo = rfqData.projectId
      ? await getProjectInfo(rfqData.projectId)
      : null;

    // 4. 첨부파일 준비
    const emailAttachments = await prepareEmailAttachments(rfqId, attachmentIds);
    const designAttachments = await getDesignAttachments(rfqId);

    // 5. 벤더별 처리
    const { results, errors, savedContracts, tbeSessionsCreated } =
      await processVendors({
        rfqId,
        rfqData,
        vendors,
        currentUser,
        picInfo,
        emailAttachments,
        designAttachments,
        generatedPdfs,
        hasToSendEmail
      });

    // 6. RFQ 상태 업데이트
    if (results.length > 0) {
      await updateRfqStatus(rfqId, Number(currentUser.id));
    }

    return {
      success: true,
      results,
      errors,
      savedContracts,
      tbeSessionsCreated,
      totalSent: results.length,
      totalFailed: errors.length,
      totalContracts: savedContracts.length,
      totalTbeSessions: tbeSessionsCreated.length
    };

  } catch (error) {
    console.error("RFQ 발송 실패:", error);
    throw new Error(
      error instanceof Error
        ? error.message
        : "RFQ 발송 중 오류가 발생했습니다."
    );
  }
}

// ============= Helper Functions =============

async function getRfqData(rfqId: number) {
  const [rfqData] = await db
    .select({
      id: rfqsLast.id,
      rfqCode: rfqsLast.rfqCode,
      rfqType: rfqsLast.rfqType,
      rfqTitle: rfqsLast.rfqTitle,
      projectId: rfqsLast.projectId,
      itemCode: rfqsLast.itemCode,
      itemName: rfqsLast.itemName,
      dueDate: rfqsLast.dueDate,
      packageNo: rfqsLast.packageNo,
      packageName: rfqsLast.packageName,
      picId: rfqsLast.pic,
      picCode: rfqsLast.picCode,
      picName: rfqsLast.picName,
      projectCompany: rfqsLast.projectCompany,
      projectFlag: rfqsLast.projectFlag,
      projectSite: rfqsLast.projectSite,
      smCode: rfqsLast.smCode,
      prNumber: rfqsLast.prNumber,
      prIssueDate: rfqsLast.prIssueDate,
      series: rfqsLast.series,
      EngPicName: rfqsLast.EngPicName,
    })
    .from(rfqsLast)
    .where(eq(rfqsLast.id, rfqId));

  return rfqData;
}

async function getPicInfo(picId: number | null, picName: string | null) {
  let picEmail = process.env.Email_From_Address;
  let finalPicName = picName || "구매담당자";

  if (picId) {
    const [picUser] = await db
      .select()
      .from(users)
      .where(eq(users.id, picId));

    if (picUser?.email) {
      picEmail = picUser.email;
      finalPicName = picUser.name || finalPicName;
    }
  }

  return { picEmail, picName: finalPicName };
}

async function getProjectInfo(projectId: number) {
  const [project] = await db
    .select()
    .from(projects)
    .where(eq(projects.id, projectId));
  return project;
}

async function prepareEmailAttachments(rfqId: number, attachmentIds: number[]) {
  const attachments = await db
    .select({
      attachment: rfqLastAttachments,
      revision: rfqLastAttachmentRevisions
    })
    .from(rfqLastAttachments)
    .leftJoin(
      rfqLastAttachmentRevisions,
      and(
        eq(rfqLastAttachmentRevisions.attachmentId, rfqLastAttachments.id),
        eq(rfqLastAttachmentRevisions.isLatest, true)
      )
    )
    .where(
      and(
        eq(rfqLastAttachments.rfqId, rfqId),
        attachmentIds.length > 0
          ? sql`${rfqLastAttachments.id} IN (${sql.join(attachmentIds, sql`, `)})`
          : sql`1=1`
      )
    );

  const emailAttachments = [];

  for (const { attachment, revision } of attachments) {
    if (revision?.filePath) {

      const cleanPath =   revision.filePath.startsWith('/api/files') 
      ?   revision.filePath.slice('/api/files'.length) 
      :   revision.filePath;

      try {

        const isProduction = process.env.NODE_ENV === "production";


        const fullPath = !isProduction
          ? path.join(
              process.cwd(),
              `public`,
              cleanPath
            )
          : path.join(
              `${process.env.NAS_PATH}`,
              cleanPath
            );
        const fileBuffer = await fs.readFile(fullPath);

        emailAttachments.push({
          filename: revision.originalFileName,
          content: fileBuffer,
          contentType: revision.fileType || 'application/octet-stream'
        });
      } catch (error) {
        
        console.error(`첨부파일 읽기 실패: ${cleanPath}`, error);
      }
    }
  }

  return emailAttachments;
}

async function getDesignAttachments(rfqId: number) {
  return await db
    .select({
      attachment: rfqLastAttachments,
      revision: rfqLastAttachmentRevisions
    })
    .from(rfqLastAttachments)
    .leftJoin(
      rfqLastAttachmentRevisions,
      and(
        eq(rfqLastAttachmentRevisions.attachmentId, rfqLastAttachments.id),
        eq(rfqLastAttachmentRevisions.isLatest, true)
      )
    )
    .where(
      and(
        eq(rfqLastAttachments.rfqId, rfqId),
        eq(rfqLastAttachments.attachmentType, "설계")
      )
    );
}

async function processVendors({
  rfqId,
  rfqData,
  vendors,
  currentUser,
  picInfo,
  emailAttachments,
  designAttachments,
  generatedPdfs,
  hasToSendEmail
}: {
  rfqId: number;
  rfqData: any;
  vendors: any[];
  currentUser: any;
  picInfo: any;
  emailAttachments: any[];
  designAttachments: any[];
  generatedPdfs?: any[];
  hasToSendEmail?: boolean;
}) {
  const results = [];
  const errors = [];
  const savedContracts = [];
  const tbeSessionsCreated = [];

  // PDF 저장 디렉토리 준비
  const contractsDir = path.join(
    `${process.env.NAS_PATH}`,
    "contracts",
    "generated"
  );
  await mkdir(contractsDir, { recursive: true });

  // 각 벤더를 독립적으로 처리
  for (const vendor of vendors) {
    try {
      const vendorResult = await db.transaction(async (tx) => {
        return await processSingleVendor({
          tx,
          vendor,
          rfqId,
          rfqData,
          currentUser,
          picInfo,
          contractsDir,
          generatedPdfs,
          designAttachments,
          hasToSendEmail
        });
      });

      results.push(vendorResult.result);

      if (vendorResult.contracts) {
        savedContracts.push(...vendorResult.contracts);
      }

      if (vendorResult.tbeSession) {
        tbeSessionsCreated.push(vendorResult.tbeSession);
      }

    } catch (error) {
      console.error(`벤더 ${vendor.vendorName} 처리 실패:`, error);

      errors.push({
        vendorId: vendor.vendorId,
        vendorName: vendor.vendorName,
        error: error instanceof Error ? error.message : "알 수 없는 오류"
      });

      // 에러 발생 시 이메일 상태 업데이트
      await updateEmailStatusFailed(rfqId, vendor.vendorId);
    }
  }

  return { results, errors, savedContracts, tbeSessionsCreated };
}

async function processSingleVendor({
  tx,
  vendor,
  rfqId,
  rfqData,
  currentUser,
  picInfo,
  contractsDir,
  generatedPdfs,
  designAttachments,
  hasToSendEmail
}: any) {
  const isResend = vendor.isResend || false;
  const sendVersion = (vendor.sendVersion || 0) + 1;

  // 이메일 수신자 정보 준비
  const emailRecipients = prepareEmailRecipients(vendor, picInfo.picEmail);

  // RFQ Detail 처리
  const newRfqDetail = await handleRfqDetail({
    tx,
    rfqId,
    vendor,
    currentUser,
    emailRecipients,
    isResend,
    sendVersion
  });

  // PDF 계약 처리
  const contracts = await handleContracts({
    tx,
    vendor,
    generatedPdfs,
    contractsDir,
    newRfqDetail,
    currentUser
  });

  // Vendor Response 처리
  const vendorResponse = await handleVendorResponse({
    tx,
    rfqId,
    vendor,
    newRfqDetail,
    currentUser
  });

  // TBE 세션 처리
  const tbeSession = await handleTbeSession({
    tx,
    rfqId,
    rfqData,
    vendor,
    newRfqDetail,
    currentUser,
    designAttachments
  });
  console.log("tbeSession 생성 완료", tbeSession);
  // 이메일 발송 처리 (사용자가 선택한 경우에만)
  let emailSent = null;
  if (hasToSendEmail) {
    emailSent = await handleRfqSendEmail({
      tx,
      rfqId,
      rfqData,
      vendor,
      newRfqDetail,
      currentUser,
      picInfo
    });
  }

  return {
    result: {
      vendorId: vendor.vendorId,
      vendorName: vendor.vendorName,
      success: true,
      responseId: vendorResponse.id,
      isResend,
      sendVersion,
      tbeSessionCreated: tbeSession,
      emailSent
    },
    contracts,
    tbeSession
  };
}

function prepareEmailRecipients(vendor: any, picEmail: string) {
  const toEmails = [vendor.selectedMainEmail];
  const ccEmails = [...vendor.additionalEmails];

  vendor.customEmails?.forEach((custom: any) => {
    if (custom.email !== vendor.selectedMainEmail &&
      !vendor.additionalEmails.includes(custom.email)) {
      ccEmails.push(custom.email);
    }
  });

  return {
    to: toEmails,
    cc: ccEmails,
    sentBy: picEmail
  };
}

async function handleRfqDetail({
  tx,
  rfqId,
  vendor,
  currentUser,
  emailRecipients,
  isResend,
  sendVersion
}: any) {
  // 기존 detail 조회
  const [rfqDetail] = await tx
    .select()
    .from(rfqLastDetails)
    .where(
      and(
        eq(rfqLastDetails.rfqsLastId, rfqId),
        eq(rfqLastDetails.vendorsId, vendor.vendorId),
        eq(rfqLastDetails.isLatest, true)
      )
    );

  if (!rfqDetail) {
    throw new Error("해당 RFQ에는 벤더가 이미 할당되어있는 상태이어야합니다.");
  }

  // 기존 detail을 isLatest=false로 업데이트
  await tx
    .update(rfqLastDetails)
    .set({
      isLatest: false,
      updatedAt: new Date()
    })
    .where(
      and(
        eq(rfqLastDetails.rfqsLastId, rfqId),
        eq(rfqLastDetails.vendorsId, vendor.vendorId),
        eq(rfqLastDetails.isLatest, true)
      )
    );

  // 새 detail 생성
  const {
    id,
    updatedBy,
    updatedAt,
    isLatest,
    sendVersion: oldSendVersion,
    emailResentCount,
    ...restRfqDetail
  } = rfqDetail;

  const [newRfqDetail] = await tx
    .insert(rfqLastDetails)
    .values({
      ...restRfqDetail,
      updatedBy: Number(currentUser.id),
      updatedAt: new Date(),
      isLatest: true,
      emailSentAt: new Date(),
      emailSentTo: JSON.stringify(emailRecipients),
      emailResentCount: isResend ? (emailResentCount || 0) + 1 : 1,
      sendVersion: sendVersion,
      lastEmailSentAt: new Date(),
      emailStatus: "sent",
      agreementYn: vendor.contractRequirements?.agreementYn || false,
      ndaYn: vendor.contractRequirements?.ndaYn || false,
      projectGtcYn: vendor.contractRequirements?.projectGtcYn || false,
      generalGtcYn: vendor.contractRequirements?.generalGtcYn || false,
    })
    .returning();

  await tx
    .update(basicContract)
    .set({
      rfqCompanyId: newRfqDetail.id,
    })
    .where(
      and(
        eq(basicContract.rfqCompanyId, rfqDetail.id),
        eq(basicContract.vendorId, vendor.vendorId),
      )
    );

  return newRfqDetail;
}

async function handleContracts({
  tx,
  vendor,
  generatedPdfs,
  contractsDir,
  newRfqDetail,
  currentUser
}: any) {
  if (!generatedPdfs || !vendor.contractRequirements) {
    return [];
  }

  const savedContracts = [];
  const vendorPdfs = generatedPdfs.filter((pdf: any) =>
    pdf.key.startsWith(`${vendor.vendorId}_`)
  );

  for (const pdfData of vendorPdfs) {
    // PDF 파일 저장
    const pdfBuffer = Buffer.from(pdfData.buffer);
    const fileName = pdfData.fileName;
    const filePath = path.join(contractsDir, fileName);
    await writeFile(filePath, pdfBuffer);

    const templateName = pdfData.key.split('_')[2];

    // 템플릿 조회
    const [template] = await db
      .select()
      .from(basicContractTemplates)
      .where(
        and(
          ilike(basicContractTemplates.templateName, `%${templateName}%`),
          eq(basicContractTemplates.status, "ACTIVE")
        )
      )
      .limit(1);

    if (!template) {
      console.error(`템플릿을 찾을 수 없음: ${templateName}`);
      continue;
    }

    // 계약 생성 또는 업데이트
    const contractRecord = await createOrUpdateContract({
      tx,
      template,
      vendor,
      newRfqDetail,
      fileName,
      currentUser
    });

    savedContracts.push({
      vendorId: vendor.vendorId,
      vendorName: vendor.vendorName,
      templateName: templateName,
      contractId: contractRecord.id,
      fileName: fileName,
      isUpdated: contractRecord.isUpdated
    });
  }

  return savedContracts;
}

async function createOrUpdateContract({
  tx,
  template,
  vendor,
  newRfqDetail,
  fileName,
  currentUser
}: any) {
  // 기존 계약 확인
  const [existingContract] = await tx
    .select()
    .from(basicContract)
    .where(
      and(
        eq(basicContract.templateId, template.id),
        eq(basicContract.vendorId, vendor.vendorId),
        eq(basicContract.rfqCompanyId, newRfqDetail.id)
      )
    )
    .limit(1);

  if (existingContract) {
    // 업데이트
    const [updated] = await tx
      .update(basicContract)
      .set({
        requestedBy: Number(currentUser.id),
        status: "PENDING",
        fileName: fileName,
        // rfqCompanyId: newRfqDetail.id,
        filePath: `/contracts/generated/${fileName}`,
        deadline: addDays(new Date(), 10),
        updatedAt: new Date()
      })
      .where(eq(basicContract.id, existingContract.id))
      .returning();

    return { ...updated, isUpdated: true };
  } else {
    // 새로 생성
    const [created] = await tx
      .insert(basicContract)
      .values({
        templateId: template.id,
        vendorId: vendor.vendorId,
        rfqCompanyId: newRfqDetail.id,
        requestedBy: Number(currentUser.id),
        status: "PENDING",
        fileName: fileName,
        filePath: `/contracts/generated/${fileName}`,
        deadline: addDays(new Date(), 10),
        createdAt: new Date(),
        updatedAt: new Date()
      })
      .returning();

    return { ...created, isUpdated: false };
  }
}

async function handleVendorResponse({
  tx,
  rfqId,
  vendor,
  newRfqDetail,
  currentUser
}: any) {
  // 기존 응답 확인
  const existingResponses = await tx
    .select()
    .from(rfqLastVendorResponses)
    .where(
      and(
        eq(rfqLastVendorResponses.rfqsLastId, rfqId),
        eq(rfqLastVendorResponses.vendorId, vendor.vendorId)
      )
    );

  // 기존 응답을 isLatest=false로 업데이트
  if (existingResponses.length > 0) {
    await tx
      .update(rfqLastVendorResponses)
      .set({ isLatest: false })
      .where(
        and(
          eq(rfqLastVendorResponses.vendorId, vendor.vendorId),
          eq(rfqLastVendorResponses.rfqsLastId, rfqId)
        )
      );
  }

  // 새 응답 생성
  const newResponseVersion = existingResponses.length > 0
    ? Math.max(...existingResponses.map(r => r.responseVersion)) + 1
    : 1;

  const [vendorResponse] = await tx
    .insert(rfqLastVendorResponses)
    .values({
      rfqsLastId: rfqId,
      rfqLastDetailsId: newRfqDetail.id,
      vendorId: vendor.vendorId,
      responseVersion: newResponseVersion,
      isLatest: true,
      status: "초대됨",
      currency: newRfqDetail.currency || "USD",
      createdBy: currentUser.id,
      updatedBy: currentUser.id,
      createdAt: new Date(),
      updatedAt: new Date()
    })
    .returning();

  return vendorResponse;
}

async function handleTbeSession({
  tx,
  rfqId,
  rfqData,
  vendor,
  newRfqDetail,
  currentUser,
  designAttachments
}: any) {
  // 기존 활성 TBE 세션 확인
  const [existingActiveTbe] = await tx
    .select()
    .from(rfqLastTbeSessions)
    .where(
      and(
        eq(rfqLastTbeSessions.rfqsLastId, rfqId),
        eq(rfqLastTbeSessions.vendorId, vendor.vendorId),
        sql`${rfqLastTbeSessions.status} IN ('준비중', '진행중', '검토중', '보류')`
      )
    );

  if (existingActiveTbe) {
    console.log(`TBE 세션이 이미 존재함: vendor ${vendor.vendorName}`);
    return null;
  }

  // TBE 세션 코드 생성
  const sessionCode = await generateTbeSessionCode(tx);

  // TBE 세션 생성
  const [tbeSession] = await tx
    .insert(rfqLastTbeSessions)
    .values({
      rfqsLastId: rfqId,
      rfqLastDetailsId: newRfqDetail.id,
      vendorId: vendor.vendorId,
      sessionCode: sessionCode,
      sessionTitle: `${rfqData.rfqCode} - ${vendor.vendorName} 기술검토`,
      sessionType: "initial",
      status: "생성중",
      evaluationResult: null,
      plannedStartDate: rfqData.dueDate
        ? addDays(new Date(rfqData.dueDate), 1)
        : addDays(new Date(), 14),
      plannedEndDate: rfqData.dueDate
        ? addDays(new Date(rfqData.dueDate), 7)
        : addDays(new Date(), 21),
      leadEvaluatorId: rfqData.picId,
      createdBy: Number(currentUser.id),
      updatedBy: Number(currentUser.id),
      createdAt: new Date(),
      updatedAt: new Date()
    })
    .returning();

  // 문서 검토 레코드 생성
  const documentReviewsCount = await createDocumentReviews({
    tx,
    tbeSession,
    designAttachments
  });

  return {
    vendorId: vendor.vendorId,
    vendorName: vendor.vendorName,
    sessionId: tbeSession.id,
    sessionCode: tbeSession.sessionCode,
    documentReviewsCount
  };
}

async function generateTbeSessionCode(tx: any) {
  const year = new Date().getFullYear();
  const pattern = `TBE-${year}-%`;

  const [lastTbeSession] = await tx
    .select({ sessionCode: rfqLastTbeSessions.sessionCode })
    .from(rfqLastTbeSessions)
    .where(like(rfqLastTbeSessions.sessionCode, pattern))
    .orderBy(sql`${rfqLastTbeSessions.sessionCode} DESC`)
    .limit(1);

  let sessionNumber = 1;
  if (lastTbeSession?.sessionCode) {
    const lastNumber = parseInt(lastTbeSession.sessionCode.split('-')[2]);
    sessionNumber = isNaN(lastNumber) ? 1 : lastNumber + 1;
  }

  return `TBE-${year}-${String(sessionNumber).padStart(3, '0')}`;
}

async function createDocumentReviews({
  tx,
  tbeSession,
  designAttachments
}: any) {
  let documentReviewsCount = 0;

  for (const { attachment, revision } of designAttachments) {
    await tx
      .insert(rfqLastTbeDocumentReviews)
      .values({
        tbeSessionId: tbeSession.id,
        documentSource: "buyer",
        buyerAttachmentId: attachment.id,
        buyerAttachmentRevisionId: revision?.id || null,
        vendorAttachmentId: null,
        documentType: attachment.attachmentType,
        documentName: revision?.originalFileName || attachment.serialNo,
        reviewStatus: "미검토",
        technicalCompliance: null,
        qualityAcceptable: null,
        requiresRevision: false,
        reviewComments: null,
        revisionRequirements: null,
        hasPdftronComments: false,
        pdftronDocumentId: null,
        pdftronAnnotationCount: 0,
        reviewedBy: null,
        reviewedAt: null,
        additionalReviewers: null,
        createdAt: new Date(),
        updatedAt: new Date()
      });

    documentReviewsCount++;
  }

  return documentReviewsCount;
}

async function updateEmailStatusFailed(rfqId: number, vendorId: number) {
  try {
    await db
      .update(rfqLastDetails)
      .set({
        emailStatus: "failed",
        updatedAt: new Date()
      })
      .where(
        and(
          eq(rfqLastDetails.rfqsLastId, rfqId),
          eq(rfqLastDetails.vendorsId, vendorId),
          eq(rfqLastDetails.isLatest, true)
        )
      );
  } catch (error) {
    console.error("이메일 상태 업데이트 실패:", error);
  }
}

async function updateRfqStatus(rfqId: number, userId: number) {
  await db
    .update(rfqsLast)
    .set({
      status: "RFQ 발송",
      rfqSendDate: new Date(),
      sentBy: Number(userId),
      updatedBy: Number(userId),
      updatedAt: new Date()
    })
    .where(eq(rfqsLast.id, rfqId));
}
async function handleRfqSendEmail({
  tx,
  rfqId,
  rfqData,
  vendor,
  newRfqDetail,
  currentUser,
  picInfo
}: any) {
  try {
    // 1. 이메일 수신자 정보 준비
    const emailRecipients = prepareEmailRecipients(vendor, picInfo.picEmail);

    // 2. RFQ 기본 정보 조회 (템플릿용)
    const rfqBasicInfoResult = await getRfqBasicInfoAction(rfqId);
    const rfqBasicInfo = rfqBasicInfoResult.success ? rfqBasicInfoResult.data : null;

    // 3. 프로젝트 정보 조회
    let projectInfo = null;
    if (rfqData.projectId) {
      projectInfo = await getProjectInfo(rfqData.projectId);
    }

    // 4. PR Items 정보 조회 (주요 품목)
    const [majorItem] = await tx
      .select({
        materialCategory: rfqPrItems.materialCategory,
        materialDescription: rfqPrItems.materialDescription,
        prNo: rfqPrItems.prNo,
      })
      .from(rfqPrItems)
      .where(and(
        eq(rfqPrItems.rfqsLastId, rfqId),
        eq(rfqPrItems.majorYn, true)
      ))
      .limit(1);

    // 5. RFQ 첨부파일 조회
    const rfqAttachments = await tx
      .select({
        attachment: rfqLastAttachments,
        revision: rfqLastAttachmentRevisions
      })
      .from(rfqLastAttachments)
      .leftJoin(
        rfqLastAttachmentRevisions,
        and(
          eq(rfqLastAttachments.latestRevisionId, rfqLastAttachmentRevisions.id),
          eq(rfqLastAttachmentRevisions.isLatest, true)
        )
      )
      .where(eq(rfqLastAttachments.rfqId, rfqId));

    // 6. 이메일 제목 생성 (RFQ 타입에 따라)
    const emailSubject = generateEmailSubject({
      rfqType: rfqData.rfqType,
      projectName: projectInfo?.name || '',
      rfqCode: rfqData.rfqCode,
      packageName: rfqData.packageName || '',
      vendorName: vendor.vendorName,
      vendorCode: vendor.vendorCode
    });

    // 7. 이메일 본문용 컨텍스트 데이터 구성
    const emailContext = {
      // 기본 정보
      language: "ko",
      now: new Date(),
      companyName: "삼성중공업",
      siteName: "EVCP Portal",

      // RFQ 정보
      rfqId: rfqData.id,
      rfqCode: rfqData.rfqCode,
      rfqTitle: rfqData.rfqTitle,
      rfqType: rfqData.rfqType,
      dueDate: rfqData.dueDate,
      rfqDescription: rfqData.rfqTitle || `${rfqData.rfqCode} 견적 요청`,

      // 프로젝트 정보
      projectId: rfqData.projectId,
      projectCode: projectInfo?.code || '',
      projectName: projectInfo?.name || '',
      projectCompany: projectInfo?.customerName || '',
      projectFlag: projectInfo?.flag || '',
      projectSite: projectInfo?.site || '',

      // 패키지 정보
      packageNo: rfqData.packageNo || "MM03",
      packageName: rfqData.packageName || "Deck Machinery",
      packageDescription: `${rfqData.packageNo || 'MM03'} - ${rfqData.packageName || 'Deck Machinery'}`,

      // 품목 정보
      itemCode: rfqData.itemCode || '',
      itemName: rfqData.itemName || '',
      itemCount: 1,
      materialGroup: majorItem?.materialCategory || "BE2101",
      materialGroupDesc: majorItem?.materialDescription || "Combined Windlass & Mooring Winch",

      // 보증 정보 (기본값)
      warrantyMonths: "35",
      warrantyDescription: "선박 인도 후 35개월 시점까지 납품한 자재 또는 용역이 계약 내용과 동일함을 보증",
      repairAdditionalMonths: "24",
      repairDescription: "Repair 시 24개월 추가",
      totalWarrantyMonths: "36",
      totalWarrantyDescription: "총 인도 후 36개월을 넘지 않음",

      // 필수 제출 정보
      requiredDocuments: [
        "품목별 단가 및 중량",
        "가격 기재/미기재 견적서(Priced/Unpriced Quotation)",
        "설계 Technical Bid Evaluation(TBE) 자료",
        "당사 PGS, SGS & POS에 대한 Deviation List"
      ],

      // 계약 요구사항
      contractRequirements: {
        hasNda: newRfqDetail.ndaYn,
        hasGeneralGtc: newRfqDetail.generalGtcYn,
        hasProjectGtc: newRfqDetail.projectGtcYn,
        hasAgreement: newRfqDetail.agreementYn,
        ndaDescription: "비밀유지계약서",
        generalGtcDescription: "General GTC",
        projectGtcDescription: "Project GTC",
        agreementDescription: "기술자료 제공 동의서"
      },

      // 업체 정보
      vendorId: vendor.vendorId,
      vendorName: vendor.vendorName,
      vendorCode: vendor.vendorCode,
      vendorCountry: vendor.vendorCountry,
      vendorEmail: vendor.vendorEmail,
      vendorRepresentativeEmail: vendor.representativeEmail,
      vendorCurrency: vendor.currency,

      // 담당자 정보
      picId: rfqData.picId,
      picName: rfqData.picName,
      picCode: rfqData.picCode,
      picEmail: picInfo.picEmail,
      picTeam: rfqData.picTeam,
      engPicName: rfqData.EngPicName,

      // PR 정보
      prNumber: rfqData.prNumber,
      prIssueDate: rfqData.prIssueDate,
      prItemsCount: 1,

      // 시리즈 및 코드 정보
      series: rfqData.series,
      smCode: rfqData.smCode,

      // 첨부파일 정보
      attachmentsCount: rfqAttachments.length,
      hasAttachments: rfqAttachments.length > 0,

      // 설정 정보
      isDevelopment: process.env.NODE_ENV === 'development',
      portalUrl: process.env.NEXT_PUBLIC_APP_URL || 'http://localhost:3000',
      systemName: "EVCP (Electronic Vendor Communication Portal)",

      // 추가 정보
      currentDate: new Date().toLocaleDateString('ko-KR'),
      currentTime: new Date().toLocaleTimeString('ko-KR'),
      formattedDueDate: new Date(rfqData.dueDate).toLocaleDateString('ko-KR', {
        year: 'numeric',
        month: 'long',
        day: 'numeric',
        weekday: 'long'
      })
    };

    // 8. 이메일 첨부파일 준비
    const emailAttachmentsList: Array<{ filename: string; content?: Buffer; path?: string }> = [];

    // RFQ 첨부파일 추가
    for (const { attachment, revision } of rfqAttachments) {
      if (revision?.filePath) {
        try {
          const isProduction = process.env.NODE_ENV === "production";
          const cleanPath = revision.filePath.startsWith('/api/files')
            ? revision.filePath.slice('/api/files'.length)
            : revision.filePath;

          const fullPath = !isProduction
            ? path.join(process.cwd(), `public`, cleanPath)
            : path.join(`${process.env.NAS_PATH}`, cleanPath);

          const fileBuffer = await fs.readFile(fullPath);
          emailAttachmentsList.push({
            filename: revision.originalFileName || `${attachment.attachmentType}_${attachment.serialNo}`,
            content: fileBuffer
          });
        } catch (error) {
          console.error(`이메일 첨부파일 읽기 실패: ${cleanPath}`, error);
        }
      }
    }

    // 9. 이메일 발송
    if (emailRecipients.to.length > 0) {
      const isDevelopment = process.env.NODE_ENV === 'development';

      await sendEmail({
        from: isDevelopment
          ? (process.env.Email_From_Address ?? "no-reply@company.com")
          : `"${picInfo.picName}" <${picInfo.picEmail}>`,
        to: emailRecipients.to.join(", "),
        cc: emailRecipients.cc.length > 0 ? emailRecipients.cc.join(", ") : undefined,
        subject: emailSubject,
        template: "custom-rfq-invitation",
        context: emailContext,
        attachments: emailAttachmentsList.length > 0 ? emailAttachmentsList : undefined,
      });

      // 10. 이메일 발송 상태 업데이트
      await tx
        .update(rfqLastDetails)
        .set({
          emailSentAt: new Date(),
          emailSentTo: JSON.stringify(emailRecipients),
          emailStatus: "sent",
          lastEmailSentAt: new Date(),
          emailResentCount: newRfqDetail.emailResentCount || 0,
          updatedAt: new Date()
        })
        .where(eq(rfqLastDetails.id, newRfqDetail.id));

      return {
        success: true,
        recipients: emailRecipients.to.length,
        ccCount: emailRecipients.cc.length
      };
    }

    return {
      success: false,
      error: "수신자 정보가 없습니다"
    };

  } catch (error) {
    console.error(`이메일 발송 실패 (${vendor.vendorName}):`, error);

    // 이메일 발송 실패 상태 업데이트
    await tx
      .update(rfqLastDetails)
      .set({
        emailStatus: "failed",
        updatedAt: new Date()
      })
      .where(eq(rfqLastDetails.id, newRfqDetail.id));

    return {
      success: false,
      error: error instanceof Error ? error.message : "이메일 발송 실패"
    };
  }
}

// 이메일 제목 생성 함수
function generateEmailSubject({
  rfqType,
  projectName,
  rfqCode,
  packageName,
  vendorName,
  vendorCode
}: {
  rfqType?: string;
  projectName: string;
  rfqCode: string;
  packageName: string;
  vendorName: string;
  vendorCode?: string | null;
}) {
  const typePrefix = rfqType === 'ITB' ? 'ITB' :
                    rfqType === 'RFQ' ? 'RFQ' : '일반견적';

  const vendorInfo = vendorCode ? `${vendorName} (${vendorCode})` : vendorName;

  return `[SHI ${typePrefix}] ${projectName} _ ${rfqCode} _ ${packageName} _ ${vendorInfo}`.trim();
}
export async function updateRfqDueDate(
  rfqId: number,
  newDueDate: Date | string,
  rfqCode: string,
  rfqTitle: string
) {
  try {
    // ✅ 날짜 정규화 - 문자열을 Date 객체로 변환
    let normalizedDate: Date;

    if (typeof newDueDate === 'string') {
      // ISO 문자열인 경우 (2024-01-15T14:30:00.000Z)
      if (newDueDate.includes('T')) {
        normalizedDate = new Date(newDueDate);
      }
      // YYYY-MM-DD HH:mm 형식인 경우
      else if (newDueDate.includes(' ') && newDueDate.includes(':')) {
        normalizedDate = new Date(newDueDate);
      }
      // YYYY-MM-DD 형식인 경우 - 한국 시간 기준으로 설정
      else if (/^\d{4}-\d{2}-\d{2}$/.test(newDueDate)) {
        normalizedDate = new Date(`${newDueDate}T00:00:00+09:00`);
      }
      else {
        normalizedDate = new Date(newDueDate);
      }
    } else if (newDueDate instanceof Date) {
      normalizedDate = newDueDate;
    } else {
      // Date 객체가 아닌 경우 처리
      normalizedDate = new Date(newDueDate as any);
    }

    // 유효한 날짜인지 확인
    if (isNaN(normalizedDate.getTime())) {
      return {
        success: false,
        message: "유효하지 않은 날짜 형식입니다.",
      }
    }

    // 1. RFQ 정보 조회
    const rfqData = await db
      .select()
      .from(rfqsLast)
      .where(eq(rfqsLast.id, rfqId))
      .limit(1)

    if (!rfqData || rfqData.length === 0) {
      return {
        success: false,
        message: "RFQ를 찾을 수 없습니다.",
      }
    }

    const rfq = rfqData[0]
    const oldDueDate = rfq.dueDate

    // 2. Due Date 업데이트 - normalizedDate 사용
    await db
      .update(rfqsLast)
      .set({
        dueDate: normalizedDate,
        updatedAt: new Date()
      })
      .where(eq(rfqsLast.id, rfqId))

    // 3. 프로젝트 정보 조회 (있는 경우)
    let projectInfo = null
    if (rfq.projectId) {
      const projectData = await db
        .select()
        .from(projects)
        .where(eq(projects.id, rfq.projectId))
        .limit(1)

      if (projectData && projectData.length > 0) {
        projectInfo = projectData[0]
      }
    }

    // 4. PIC 정보 조회
    let picInfo = null
    if (rfq.pic) {
      const picData = await db
        .select()
        .from(users)
        .where(eq(users.id, rfq.pic))
        .limit(1)

      if (picData && picData.length > 0) {
        picInfo = picData[0]
      }
    }

    const picName = picInfo?.name || rfq.picName || "구매팀"
    const picEmail = picInfo?.email || process.env.Email_From_Address || "procurement@company.com"

    // 5. RFQ Details에서 이메일 수신자 조회
    const rfqDetailsData = await db
      .select({
        emailSentTo: rfqLastDetails.emailSentTo,
        vendorId: rfqLastDetails.vendorsId,
      })
      .from(rfqLastDetails)
      .where(eq(rfqLastDetails.rfqsLastId, rfqId))

    if (rfqDetailsData.length === 0) {
      // 페이지 재검증
      revalidatePath(`/[lng]/evcp/rfq-last/${rfqId}`, 'layout')

      return {
        success: true,
        message: "마감일이 수정되었습니다. (발송된 이메일이 없음)",
      }
    }

    // 6. 각 vendor별로 이메일 발송
    const emailPromises: Promise<any>[] = []
    
    for (const detail of rfqDetailsData) {
      if (!detail.emailSentTo) continue

      // vendor 정보 조회
      let vendorInfo = null
      if (detail.vendorId) {
        const vendorData = await db
          .select()
          .from(vendors)
          .where(eq(vendors.id, detail.vendorId))
          .limit(1)

        if (vendorData && vendorData.length > 0) {
          vendorInfo = vendorData[0]
        }
      }

      // 이메일 언어 결정 (vendor의 country가 KR이면 한국어, 아니면 영어)
      const isKorean = vendorInfo?.country === 'KR'
      const language = isKorean ? 'ko' : 'en'
      const locale = isKorean ? ko : enUS

      const emailSubject = isKorean
        ? `[마감일 변경] ${rfqCode} ${rfqTitle || ''}`.trim()
        : `[Due Date Changed] ${rfqCode} ${rfqTitle || ''}`.trim()

      // ✅ 날짜 포맷팅 - 시간 포함하도록 수정
      const oldDateFormatted = (() => {
        try {
          if (!oldDueDate) {
            return isKorean ? "미설정" : "Not set";
          }
          const date = new Date(oldDueDate);
          // 시간까지 포함한 포맷
          return format(date, "PPP HH:mm", { locale });
        } catch {
          return isKorean ? "미설정" : "Not set";
        }
      })();

      const newDateFormatted = (() => {
        try {
          // normalizedDate 사용
          return format(normalizedDate, "PPP HH:mm", { locale });
        } catch (error) {
          console.error("Date formatting error:", error);
          return normalizedDate.toISOString();
        }
      })();

      // 이메일 발송 - null/undefined 값 처리
      const emailContext: Record<string, unknown> = {
        language: language ?? (isKorean ? "ko" : "en"),
        vendorName: vendorInfo?.vendorName ?? "",
        rfqCode: rfqCode ?? "",
        rfqTitle: rfqTitle ?? "",
        rfqType: rfq.rfqType ?? "",
        projectCode: projectInfo?.code ?? "",
        projectName: projectInfo?.name ?? "",
        packageNo: rfq.packageNo ?? "",
        packageName: rfq.packageName ?? "",
        itemCode: rfq.itemCode ?? "",
        itemName: rfq.itemName ?? "",
        oldDueDate: oldDateFormatted,
        newDueDate: newDateFormatted,
        picName: picName ?? "구매팀",
        picEmail: picEmail ?? (process.env.Email_From_Address ?? "procurement@company.com"),
        engPicName: rfq.EngPicName ?? "",
        portalUrl:
          (process.env.NEXT_PUBLIC_APP_URL
            ? `${process.env.NEXT_PUBLIC_APP_URL}/partners/rfq-last/${rfqId}`
            : `https://partners.sevcp.com/partners/rfq-last/${rfqId}`),
      };

      const validContext = Object.fromEntries(
        Object.entries(emailContext ?? {}).filter(([, value]) => value !== null && value !== undefined)
      );

      let toEmails: string[] = [];
      let ccEmails: string[] = [];

      try {
        const emailData = typeof detail.emailSentTo === 'string'
          ? JSON.parse(detail.emailSentTo)
          : detail.emailSentTo;

        if (emailData.to) {
          toEmails = Array.isArray(emailData.to)
            ? emailData.to.filter(Boolean)
            : [emailData.to].filter(Boolean);
        }

        if (emailData.cc) {
          ccEmails = Array.isArray(emailData.cc)
            ? emailData.cc.filter(Boolean)
            : [emailData.cc].filter(Boolean);
        }
      } catch (error) {
        console.warn("Failed to parse emailSentTo as JSON, trying comma-separated:", error);
        toEmails = (detail.emailSentTo ?? "")
          .split(",")
          .map((e) => e.trim())
          .filter(Boolean);
      }

      console.log("Parsed emails - To:", toEmails, "CC:", ccEmails);

      if (toEmails.length === 0) {
        continue;
      }

      emailPromises.push(
        sendEmail({
          from: isDevelopment
            ? (process.env.Email_From_Address ?? "no-reply@company.com")
            : `"${picName}" <${picEmail}>`,
          to: toEmails.join(", "),
          cc: ccEmails.length > 0 ? ccEmails.join(", ") : undefined,
          subject: emailSubject,
          template: "rfq-due-date-change",
          context: validContext,
        })
      );
    }

    // 모든 이메일 발송
    if (emailPromises.length > 0) {
      await Promise.allSettled(emailPromises)
    }

    try {
      await revalidatePath(`/[lng]/evcp/rfq-last/${rfqId}`, "layout");
    } catch (e) {
      console.warn("revalidatePath failed:", e);
    }

    // ✅ 성공 메시지도 시간 포함하도록 수정
    return {
      success: true,
      message: `마감일이 ${format(normalizedDate, "yyyy년 MM월 dd일 HH시 mm분", { locale: ko })}로 수정되었으며, 관련 업체에 이메일이 발송되었습니다.`,
    }
  } catch (error) {
    console.error("Error updating due date:", error)
    return {
      success: false,
      message: "마감일 수정 중 오류가 발생했습니다.",
    }
  }
}

/**
 * RFQ 벤더 응답 첨부파일 삭제
 */
export async function deleteVendorResponseAttachment({
  attachmentId,
  responseId,
  userId
}: {
  attachmentId: number;
  responseId: number;
  userId: number;
}) {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user) {
      throw new Error("인증이 필요합니다.");
    }

    // 첨부파일이 해당 응답에 속하는지 확인
    const [attachment] = await db
      .select()
      .from(rfqLastVendorAttachments)
      .where(
        and(
          eq(rfqLastVendorAttachments.id, attachmentId),
          eq(rfqLastVendorAttachments.vendorResponseId, responseId)
        )
      )
      .limit(1);

    if (!attachment) {
      throw new Error("삭제할 첨부파일을 찾을 수 없습니다.");
    }

    // 트랜잭션으로 삭제
    await db.transaction(async (tx) => {
      // 첨부파일 삭제
      await tx
        .delete(rfqLastVendorAttachments)
        .where(eq(rfqLastVendorAttachments.id, attachmentId));

      // 이력 기록
      await tx.insert(rfqLastVendorResponseHistory).values({
        vendorResponseId: responseId,
        action: "첨부파일삭제",
        changeDetails: {
          attachmentId,
          attachmentType: attachment.attachmentType,
          documentNo: attachment.documentNo,
          fileName: attachment.fileName
        },
        performedBy: userId,
      });
    });

    return {
      success: true,
      message: "첨부파일이 삭제되었습니다."
    };
  } catch (error) {
    console.error("첨부파일 삭제 실패:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "첨부파일 삭제 중 오류가 발생했습니다."
    };
  }
}
export async function deleteRfqVendor({
  rfqId,
  detailId,
  vendorId,
}: {
  rfqId: number;
  detailId: number;
  vendorId: number;
}): Promise<{
  success: boolean;
  message?: string;
}> {
  try {
    const deleteResult = await db
      .delete(rfqLastDetails)
      .where(
        and(
          eq(rfqLastDetails.id, detailId),
          eq(rfqLastDetails.rfqsLastId, rfqId),
          eq(rfqLastDetails.vendorsId, vendorId)
        )
      )
      .returning({ id: rfqLastDetails.id });

    if (deleteResult.length === 0) {
      throw new Error("삭제할 벤더를 찾을 수 없습니다.");
    }

    // 캐시 무효화
    revalidatePath(`/partners/rfq-last/${rfqId}`);

    return {
      success: true,
      message: "벤더가 성공적으로 삭제되었습니다.",
    };
  } catch (error) {
    console.error("벤더 삭제 오류:", error);

    return {
      success: false,
      message: error instanceof Error ? error.message : "벤더 삭제 중 오류가 발생했습니다.",
    };
  }
}

export async function updateVendorContractRequirements({
  rfqId,
  detailId,
  contractRequirements,
}: UpdateVendorContractRequirementsParams): Promise<UpdateResult> {
  try {
    // gtcType에 따라 generalGtcYn과 projectGtcYn 설정
    const generalGtcYn = contractRequirements.gtcType === "general";
    const projectGtcYn = contractRequirements.gtcType === "project";

    // 데이터베이스 업데이트
    const result = await db
      .update(rfqLastDetails)
      .set({
        agreementYn: contractRequirements.agreementYn,
        ndaYn: contractRequirements.ndaYn,
        gtcType: contractRequirements.gtcType,
        generalGtcYn,
        projectGtcYn,
        updatedAt: new Date(),
        // updatedBy는 세션에서 가져와야 하는 경우 추가
        // updatedBy: getCurrentUserId(), 
      })
      .where(eq(rfqLastDetails.id, detailId))
      .returning();

    // 결과 검증
    if (!result || result.length === 0) {
      return {
        success: false,
        error: "업체 정보를 찾을 수 없습니다.",
      };
    }

    // // 캐시 재검증 (필요한 경우)
    // revalidatePath(`/rfq/${rfqId}`);
    // revalidatePath(`/rfq/${rfqId}/vendors`);

    return {
      success: true,
      data: result[0],
    };
  } catch (error) {
    console.error("Error updating vendor contract requirements:", error);

    return {
      success: false,
      error: error instanceof Error ? error.message : "업데이트 중 오류가 발생했습니다.",
    };
  }
}

// 헬퍼 함수
function getTemplateNameByType(
  contractType: string,
  requirements: any
): string {
  switch (contractType) {
    case "NDA": return "비밀";
    case "General_GTC": return "General GTC";
    case "Project_GTC": return requirements.projectCode || "Project GTC";
    case "기술자료": return "기술";
    default: return contractType;
  }
}


export async function updateAttachmentTypes(
  attachmentIds: number[],
  attachmentType: "구매" | "설계"
) {
  try {
    // 권한 체크 등 필요시 추가

    await db
      .update(rfqLastVendorAttachments)
      .set({ attachmentType })
      .where(inArray(rfqLastVendorAttachments.id, attachmentIds));

    // 페이지 리밸리데이션
    // revalidatePath("/rfq");

    return { success: true, message: `${attachmentIds.length}개 항목이 "${attachmentType}"로 변경되었습니다.` };
  } catch (error) {
    console.error("Failed to update attachment types:", error);
    return { success: false, message: "문서 유형 변경에 실패했습니다." };
  }
}

// 단일 RFQ 밀봉 토글
export async function toggleRfqSealed(rfqId: number) {
  try {
    const session = await getServerSession(authOptions)

    if (!session?.user) {
      throw new Error("인증이 필요합니다.")
    }
    // 현재 상태 조회
    const [currentRfq] = await db
      .select({ rfqSealedYn: rfqsLast.rfqSealedYn })
      .from(rfqsLast)
      .where(eq(rfqsLast.id, rfqId));

    if (!currentRfq) {
      throw new Error("RFQ를 찾을 수 없습니다.");
    }

    // 상태 토글
    const [updated] = await db
      .update(rfqsLast)
      .set({
        rfqSealedYn: !currentRfq.rfqSealedYn,
        updatedBy: Number(session.user.id),
        updatedAt: new Date(),
      })
      .where(eq(rfqsLast.id, rfqId))
      .returning();

    revalidatePath("/evcp/rfq-last");

    return {
      success: true,
      data: updated,
      message: updated.rfqSealedYn ? "견적이 밀봉되었습니다." : "견적 밀봉이 해제되었습니다.",
    };
  } catch (error) {
    console.error("RFQ 밀봉 상태 변경 실패:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "알 수 없는 오류가 발생했습니다.",
    };
  }
}

// 여러 RFQ 일괄 밀봉
export async function sealMultipleRfqs(rfqIds: number[]) {
  try {
    const session = await getServerSession(authOptions)

    if (!session?.user) {
      throw new Error("인증이 필요합니다.")
    }

    if (!rfqIds || rfqIds.length === 0) {
      throw new Error("선택된 RFQ가 없습니다.");
    }

    const updated = await db
      .update(rfqsLast)
      .set({
        rfqSealedYn: true,
        updatedBy: Number(session.user.id),
        updatedAt: new Date(),
      })
      .where(inArray(rfqsLast.id, rfqIds))
      .returning();

    revalidatePath("/evcp/rfq-last");

    return {
      success: true,
      count: updated.length,
      message: `${updated.length}건의 견적이 밀봉되었습니다.`,
    };
  } catch (error) {
    console.error("RFQ 일괄 밀봉 실패:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "알 수 없는 오류가 발생했습니다.",
    };
  }
}

// 여러 RFQ 일괄 밀봉 해제
export async function unsealMultipleRfqs(rfqIds: number[]) {
  try {
    const session = await getServerSession(authOptions)

    if (!session?.user) {
      throw new Error("인증이 필요합니다.")
    }
    if (!rfqIds || rfqIds.length === 0) {
      throw new Error("선택된 RFQ가 없습니다.");
    }

    const updated = await db
      .update(rfqsLast)
      .set({
        rfqSealedYn: false,
        updatedBy: Number(session.user.id),
        updatedAt: new Date(),
      })
      .where(inArray(rfqsLast.id, rfqIds))
      .returning();

    revalidatePath("/evcp/rfq-last");

    return {
      success: true,
      count: updated.length,
      message: `${updated.length}건의 견적 밀봉이 해제되었습니다.`,
    };
  } catch (error) {
    console.error("RFQ 밀봉 해제 실패:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "알 수 없는 오류가 발생했습니다.",
    };
  }
}

// 단일 RFQ 밀봉 (밀봉만)
export async function sealRfq(rfqId: number) {
  try {
    const session = await getServerSession(authOptions)

    if (!session?.user) {
      throw new Error("인증이 필요합니다.")
    }

    const [updated] = await db
      .update(rfqsLast)
      .set({
        rfqSealedYn: true,
        updatedBy: Number(session.user.id),
        updatedAt: new Date(),
      })
      .where(eq(rfqsLast.id, rfqId))
      .returning();

    if (!updated) {
      throw new Error("RFQ를 찾을 수 없습니다.");
    }

    revalidatePath("/evcp/rfq-last");

    return {
      success: true,
      data: updated,
      message: "견적이 밀봉되었습니다.",
    };
  } catch (error) {
    console.error("RFQ 밀봉 실패:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "알 수 없는 오류가 발생했습니다.",
    };
  }
}

// 단일 RFQ 밀봉 해제
export async function unsealRfq(rfqId: number) {
  try {
    const session = await getServerSession(authOptions)

    if (!session?.user) {
      throw new Error("인증이 필요합니다.")
    }

    const [updated] = await db
      .update(rfqsLast)
      .set({
        rfqSealedYn: false,
        updatedBy: Number(session.user.id),
        updatedAt: new Date(),
      })
      .where(eq(rfqsLast.id, rfqId))
      .returning();

    if (!updated) {
      throw new Error("RFQ를 찾을 수 없습니다.");
    }

    revalidatePath("/evcp/rfq-last");

    return {
      success: true,
      data: updated,
      message: "견적 밀봉이 해제되었습니다.",
    };
  } catch (error) {
    console.error("RFQ 밀봉 해제 실패:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "알 수 없는 오류가 발생했습니다.",
    };
  }
}



export async function updateShortList(
  rfqId: number,
  vendorIds: number[],
  shortListStatus: boolean = true
) {
  try {
    // 권한 체크 등 필요한 검증
    const session = await getServerSession(authOptions)

    if (!session?.user) {
      throw new Error("인증이 필요합니다.")
    }

    // 트랜잭션으로 처리
    const result = await db.transaction(async (tx) => {
      // 1. 해당 RFQ의 모든 벤더들의 shortList를 먼저 false로 설정
      await tx
        .update(rfqLastDetails)
        .set({
          shortList: false,
          updatedBy: Number(session.user.id),
          updatedAt: new Date()
        })
        .where(
          and(
            eq(rfqLastDetails.rfqsLastId, rfqId),
            eq(rfqLastDetails.isLatest, true)
          )
        );

      // 2. 선택된 벤더들 처리
      if (vendorIds.length > 0) {
        // 2-1. 선택된 벤더들의 shortList를 true로 설정
        const updatedDetails = await Promise.all(
          vendorIds.map(vendorId =>
            tx
              .update(rfqLastDetails)
              .set({
                shortList: shortListStatus,
                updatedBy: Number(session.user.id),
                updatedAt: new Date()
              })
              .where(
                and(
                  eq(rfqLastDetails.rfqsLastId, rfqId),
                  eq(rfqLastDetails.vendorsId, vendorId),
                  eq(rfqLastDetails.isLatest, true)
                )
              )
              .returning()
          )
        );

        // 2-2. TBE 세션 처리 (shortList가 true인 경우에만)
        if (shortListStatus) {
          // 각 벤더에 대한 rfqLastDetailsId 추출
          const detailsMap = new Map(
            updatedDetails.flat().map(detail => [detail.vendorsId, detail.id])
          );

          // TBE 세션 생성 또는 업데이트
          await Promise.all(
            vendorIds.map(async (vendorId) => {
              const rfqLastDetailsId = detailsMap.get(vendorId);
              
              if (!rfqLastDetailsId) {
                console.warn(`rfqLastDetailsId not found for vendorId: ${vendorId}`);
                return;
              }

              // 기존 활성 TBE 세션이 있는지 확인
              const existingSession = await tx
                .select()
                .from(rfqLastTbeSessions)
                .where(
                  and(
                    eq(rfqLastTbeSessions.rfqsLastId, rfqId),
                    eq(rfqLastTbeSessions.vendorId, vendorId),
                    inArray(rfqLastTbeSessions.status, ["생성중", "준비중", "진행중", "검토중", "보류"])
                  )
                )
                .limit(1);

              if (existingSession.length > 0) {
                // 기존 세션이 있으면 상태 업데이트
                await tx
                  .update(rfqLastTbeSessions)
                  .set({
                    status: "준비중",
                    updatedBy: Number(session.user.id),
                    updatedAt: new Date()
                  })
                  .where(eq(rfqLastTbeSessions.id, existingSession[0].id));
              } 
            })
          );

          // 2-3. RFQ 상태를 "Short List 확정"으로 업데이트
          await tx
            .update(rfqsLast)
            .set({
              status: "Short List 확정" as RfqStatus,
              updatedBy: Number(session.user.id),
              updatedAt: new Date()
            })
            .where(eq(rfqsLast.id, rfqId));

        } else {
          // shortList가 false인 경우, 해당 벤더들의 활성 TBE 세션을 취소 상태로 변경
          await Promise.all(
            vendorIds.map(vendorId =>
              tx
                .update(rfqLastTbeSessions)
                .set({
                  status: "취소",
                  updatedBy: Number(session.user.id),
                  updatedAt: new Date()
                })
                .where(
                  and(
                    eq(rfqLastTbeSessions.rfqsLastId, rfqId),
                    eq(rfqLastTbeSessions.vendorId, vendorId),
                    inArray(rfqLastTbeSessions.status, ["생성중", "준비중", "진행중", "검토중", "보류"])
                  )
                )
            )
          );

          // shortList를 해제하는 경우의 상태 처리
          // 모든 벤더의 shortList가 false인지 확인
          const remainingShortlisted = await tx
            .select()
            .from(rfqLastDetails)
            .where(
              and(
                eq(rfqLastDetails.rfqsLastId, rfqId),
                eq(rfqLastDetails.isLatest, true),
                eq(rfqLastDetails.shortList, true)
              )
            )
            .limit(1);

          // 남은 shortList 벤더가 없으면 RFQ 상태를 이전 상태로 되돌림
          // if (remainingShortlisted.length === 0) {
          //   await tx
          //     .update(rfqsLast)
          //     .set({
          //       status: "견적 접수" as RfqStatus, // 또는 적절한 이전 상태
          //       updatedBy: Number(session.user.id),
          //       updatedAt: new Date()
          //     })
          //     .where(eq(rfqsLast.id, rfqId));
          // }
        }

        return {
          success: true,
          updatedCount: updatedDetails.length,
          vendorIds,
          tbeSessionsUpdated: shortListStatus,
          rfqStatusUpdated: true
        };
      }

      // 벤더가 없는 경우 (모든 shortList를 false로만 설정)
      // RFQ 상태를 이전 상태로 되돌림
      await tx
        .update(rfqsLast)
        .set({
          status: "견적 접수" as RfqStatus, // 또는 적절한 이전 상태
          updatedBy: Number(session.user.id),
          updatedAt: new Date()
        })
        .where(eq(rfqsLast.id, rfqId));

      return {
        success: true,
        updatedCount: 0,
        vendorIds: [],
        tbeSessionsUpdated: false,
        rfqStatusUpdated: true
      };
    });

    // revalidatePath(`/buyer/rfq/${rfqId}`);
    return result;

  } catch (error) {
    console.error("Short List 업데이트 실패:", error);
    throw new Error("Short List 업데이트에 실패했습니다.");
  }
}

interface AssignPicParams {
  rfqIds: number[];
  picUserId: number;
}

export async function assignPicToRfqs({ rfqIds, picUserId }: AssignPicParams) {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user) {
      throw new Error("인증이 필요합니다.");
    }

    // 선택된 담당자 정보 조회
    const picUser = await db.query.users.findFirst({
      where: eq(users.id, picUserId),
    });

    if (!picUser) {
      throw new Error("선택한 담당자를 찾을 수 없습니다.");
    }

    // RFQ 코드가 "I"로 시작하는 것들만 필터링 (추가 검증)
    const targetRfqs = await db.query.rfqsLast.findMany({
      where: inArray(rfqsLast.id, rfqIds),
    });

    // "I"로 시작하는 RFQ만 필터링
    const validRfqs = targetRfqs.filter(rfq => rfq.rfqCode?.startsWith("I"));

    if (validRfqs.length === 0) {
      throw new Error("담당자를 지정할 수 있는 ITB가 없습니다.");
    }

    // 트랜잭션으로 처리하여 동시성 문제 방지
    const updatedCount = await db.transaction(async (tx) => {
      let successCount = 0;
      
      for (const rfq of validRfqs) {
        // 각 RFQ에 대해 새로운 코드 생성
        const newRfqCode = await generateItbRfqCode(picUser.id);
        
        // RFQ 업데이트
        const result = await tx.update(rfqsLast)
          .set({
            rfqCode: newRfqCode, // 새로운 RFQ 코드로 업데이트
            pic: picUser.id,
            picCode: picUser.userCode || undefined,
            picName: picUser.name,
            status: "구매담당지정", // 상태도 업데이트
            updatedBy: parseInt(session.user.id),
            updatedAt: new Date(),
          })
          .where(eq(rfqsLast.id, rfq.id));
        
        if (result) {
          successCount++;
          console.log(`RFQ ${rfq.rfqCode} -> ${newRfqCode} 업데이트 완료`);
        }
      }
      
      return successCount;
    });

    revalidatePath("/evcp/rfq-last");

    return { 
      success: true, 
      message: `${updatedCount}건의 ITB에 담당자가 지정되고 코드가 재발급되었습니다.`,
      updatedCount
    };
  } catch (error) {
    console.error("담당자 지정 오류:", error);
    return { 
      success: false, 
      message: error instanceof Error ? error.message : "담당자 지정 중 오류가 발생했습니다." 
    };
  }
}


// AVL 벤더 정보 가져오기
export async function getAvlVendorsForRfq(rfqId: number) {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user) {
      throw new Error("인증이 필요합니다.");
    }

    // 1. RFQ 정보 조회하여 프로젝트 코드 가져오기
    const rfqData = await db.select({
      projectId: rfqsLast.projectId,
      projectCode: projects.projectCode,
    })
    .from(rfqsLast)
    .leftJoin(projects, eq(rfqsLast.projectId, projects.id))
    .where(eq(rfqsLast.id, rfqId))
    .limit(1);

    if (!rfqData[0]?.projectCode) {
      return { 
        success: false, 
        error: "RFQ에 연결된 프로젝트 코드를 찾을 수 없습니다." 
      };
    }

    const projectCode = rfqData[0].projectCode;

    // 2. RFQ PR Items에서 major인 자재그룹 코드 가져오기
    const majorMaterials = await db.select({
      materialCategory: rfqPrItems.materialCategory,
    })
    .from(rfqPrItems)
    .where(
      and(
        eq(rfqPrItems.rfqsLastId, rfqId),
        isTrue(rfqPrItems.majorYn)
      )
    )
    .groupBy(rfqPrItems.materialCategory);

    if (majorMaterials.length === 0) {
      return { 
        success: false, 
        error: "Major 자재그룹을 찾을 수 없습니다." 
      };
    }

    const materialGroupCodes = majorMaterials
      .map(m => m.materialCategory)
      .filter(Boolean);

    // 3. AVL 벤더 정보 조회
    const avlVendors = await db.select({
      id: avlVendorInfo.id,
      vendorId: avlVendorInfo.vendorId,
      vendorName: avlVendorInfo.vendorName,
      vendorCode: avlVendorInfo.vendorCode,
      avlVendorName: avlVendorInfo.avlVendorName,
      tier: avlVendorInfo.tier,
      headquarterLocation: avlVendorInfo.headquarterLocation,
      manufacturingLocation: avlVendorInfo.manufacturingLocation,
      materialGroupCode: avlVendorInfo.materialGroupCode,
      materialGroupName: avlVendorInfo.materialGroupName,
      packageName: avlVendorInfo.packageName,
      isAgent: avlVendorInfo.isAgent,
      hasAvl: avlVendorInfo.hasAvl,
      isBlacklist: avlVendorInfo.isBlacklist,
      isBcc: avlVendorInfo.isBcc,
      remark: avlVendorInfo.remark,
    })
    .from(avlVendorInfo)
    .where(
      and(
        eq(avlVendorInfo.projectCode, projectCode),
        // materialGroupCode가 materialGroupCodes 중 하나와 일치
        ...(materialGroupCodes.length > 0 
          ? [sql`${avlVendorInfo.materialGroupCode} = ANY(${materialGroupCodes})`]
          : []
        )
      )
    );

    // 4. 이미 RFQ에 추가된 벤더 ID 조회
    const existingVendors = await db.select({
      vendorId: rfqLastDetails.vendorsId,
    })
    .from(rfqLastDetails)
    .where(
      and(
        eq(rfqLastDetails.rfqsLastId, rfqId),
        isTrue(rfqLastDetails.isLatest)
      )
    );

    const existingVendorIds = existingVendors
      .map(v => v.vendorId)
      .filter(Boolean);

    // 5. 벤더 정보가 없는 AVL 레코드에 대해 실제 벤더 정보 조회 및 매칭
    const vendorsWithoutId = avlVendors.filter(v => !v.vendorId);
    if (vendorsWithoutId.length > 0) {
      // 벤더 이름과 코드로 실제 벤더 찾기
      const vendorNames = vendorsWithoutId.map(v => v.vendorName).filter(Boolean);
      const vendorCodes = vendorsWithoutId.map(v => v.vendorCode).filter(Boolean);
      
      const actualVendors = await db.select({
        id: vendors.id,
        vendorName: vendors.vendorName,
        vendorCode: vendors.vendorCode,
      })
      .from(vendors)
      .where(
        or(
          ...(vendorNames.length > 0 ? [sql`${vendors.vendorName} = ANY(${vendorNames})`] : []),
          ...(vendorCodes.length > 0 ? [sql`${vendors.vendorCode} = ANY(${vendorCodes})`] : [])
        )
      );

      // AVL 레코드에 실제 벤더 ID 매칭
      avlVendors.forEach(avlVendor => {
        if (!avlVendor.vendorId) {
          const matchedVendor = actualVendors.find(v => 
            v.vendorName === avlVendor.vendorName || 
            v.vendorCode === avlVendor.vendorCode
          );
          if (matchedVendor) {
            avlVendor.vendorId = matchedVendor.id;
          }
        }
      });
    }

    return {
      success: true,
      vendors: avlVendors,
      existingVendorIds,
      projectCode,
      materialGroupCodes,
    };

  } catch (error) {
    console.error("AVL 벤더 조회 오류:", error);
    return { 
      success: false, 
      error: "AVL 벤더 정보를 가져오는 중 오류가 발생했습니다." 
    };
  }
}

// AVL 벤더를 RFQ에 추가
export async function addAvlVendorsToRfq({
  rfqId,
  vendors,
}: {
  rfqId: number;
  vendors: Array<{
    vendorId: number;
    vendorName: string;
    vendorCode: string | null;
    contractRequirements: {
      agreementYn: boolean;
      ndaYn: boolean;
      gtcType: "general" | "project" | "none";
    };
  }>;
}) {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user) {
      throw new Error("인증이 필요합니다.");
    }
    const userId = Number(session.user.id);

    // 이미 추가된 벤더 확인
    const existingDetails = await db.select({
      vendorId: rfqLastDetails.vendorsId,
    })
    .from(rfqLastDetails)
    .where(
      and(
        eq(rfqLastDetails.rfqsLastId, rfqId),
        isTrue(rfqLastDetails.isLatest)
      )
    );

    const existingVendorIds = new Set(
      existingDetails.map(d => d.vendorId).filter(Boolean)
    );

    // 추가할 벤더 필터링
    const vendorsToAdd = vendors.filter(v => !existingVendorIds.has(v.vendorId));
    
    if (vendorsToAdd.length === 0) {
      return {
        success: true,
        addedCount: 0,
        skippedCount: vendors.length,
        message: "모든 벤더가 이미 추가되어 있습니다."
      };
    }

    // 벤더 추가
    const newDetails = await Promise.all(
      vendorsToAdd.map(async (vendor) => {
        const { contractRequirements } = vendor;
        
        // 벤더 정보 조회하여 위치 확인
        const vendorInfo = await db.select({
          country: vendors.country,
        })
        .from(vendors)
        .where(eq(vendors.id, vendor.vendorId))
        .limit(1);

        const isInternational = vendorInfo[0]?.country && 
          vendorInfo[0].country !== "KR" && 
          vendorInfo[0].country !== "한국";

        return db.insert(rfqLastDetails).values({
          rfqsLastId: rfqId,
          vendorsId: vendor.vendorId,
          
          // 기본계약 설정
          agreementYn: contractRequirements.agreementYn,
          ndaYn: contractRequirements.ndaYn,
          generalGtcYn: isInternational && contractRequirements.gtcType === "general",
          projectGtcYn: isInternational && contractRequirements.gtcType === "project",
          gtcType: isInternational ? contractRequirements.gtcType : "none",
          
          // 기본값 설정
          currency: "USD",
          shortList: false,
          returnYn: false,
          materialPriceRelatedYn: false,
          sparepartYn: false,
          firstYn: false,
          sendVersion: 0,
          isLatest: true,
          
          createdAt: new Date(),
          createdBy: userId,
          updatedAt: new Date(),
          updatedBy: userId,
        }).returning();
      })
    );

    return {
      success: true,
      addedCount: newDetails.length,
      skippedCount: vendors.length - newDetails.length,
      message: `${newDetails.length}개의 AVL 벤더가 추가되었습니다.`,
      addedVendors: newDetails,
    };

  } catch (error) {
    console.error("AVL 벤더 추가 오류:", error);
    return {
      success: false,
      error: "AVL 벤더 추가 중 오류가 발생했습니다."
    };
  }
}



interface ConfirmVendorDocumentsResult {
  success: boolean;
  message: string;
  updatedCount?: number;
}

/**
 * 특정 벤더의 모든 문서를 확정 처리
 * @param rfqId RFQ ID
 * @param vendorId 벤더 ID
 * @returns 처리 결과
 */
export async function confirmVendorDocuments(
  rfqId: number,
  vendorId: number
): Promise<ConfirmVendorDocumentsResult> {
  try {
    // 데이터 유효성 검증
    if (!rfqId || !vendorId) {
      return {
        success: false,
        message: "RFQ ID와 벤더 ID가 필요합니다.",
      };
    }

    // 트랜잭션으로 두 테이블 동시 업데이트
    const result = await db.transaction(async (tx) => {
      // 1. rfqLastVendorResponses 테이블 업데이트
      const vendorResponseResult = await tx
        .update(rfqLastVendorResponses)
        .set({
          isDocumentConfirmed: true,
          updatedAt: new Date(),
        })
        .where(
          and(
            eq(rfqLastVendorResponses.rfqsLastId, rfqId),
            eq(rfqLastVendorResponses.vendorId, vendorId)
          )
        )
        .returning({ id: rfqLastVendorResponses.id });

      // 업데이트된 레코드 수 확인
      const updatedCount = vendorResponseResult.length;

      if (updatedCount === 0) {
        throw new Error("해당 조건에 맞는 문서를 찾을 수 없습니다.");
      }

      // 2. rfqsLast 테이블 status 업데이트
      const rfqUpdateResult = await tx
        .update(rfqsLast)
        .set({
          status: "견적요청문서 확정" as RfqStatus,
          updatedAt: new Date(),
        })
        .where(eq(rfqsLast.id, rfqId))
        .returning({ id: rfqsLast.id });

      if (rfqUpdateResult.length === 0) {
        throw new Error("RFQ 상태 업데이트에 실패했습니다.");
      }

      return updatedCount;
    });

    // 캐시 무효화 (필요한 경우)
    revalidatePath(`/rfq-last/${rfqId}`);
    
    return {
      success: true,
      message: `문서가 확정되었습니다.`,
      updatedCount: result,
    };
  } catch (error) {
    console.log("문서 확정 중 오류 발생:", error);
    
    return {
      success: false,
      message: error instanceof Error 
        ? `문서 확정 실패: ${error.message}` 
        : "문서 확정 중 알 수 없는 오류가 발생했습니다.",
    };
  }
}

/**
 * 특정 벤더의 문서 확정 상태 조회
 * @param rfqId RFQ ID
 * @param vendorId 벤더 ID
 * @returns 확정 상태
 */
export async function getVendorDocumentConfirmStatus(
  rfqId: number,
  vendorId: number
): Promise<{ isConfirmed: boolean; count: number }> {
  try {
    const results = await db
      .select({
        isDocumentConfirmed: rfqLastVendorResponses.isDocumentConfirmed,
      })
      .from(rfqLastVendorResponses)
      .where(
        and(
          eq(rfqLastVendorResponses.rfqsLastId, rfqId),
          eq(rfqLastVendorResponses.vendorId, vendorId)
        )
      );

    const confirmedCount = results.filter(r => r.isDocumentConfirmed).length;
    const totalCount = results.length;

    return {
      isConfirmed: totalCount > 0 && confirmedCount === totalCount,
      count: totalCount,
    };
  } catch (error) {
    console.error("문서 확정 상태 조회 중 오류:", error);
    return { isConfirmed: false, count: 0 };
  }
}