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

import { revalidatePath, unstable_cache, unstable_noStore } from "next/cache";
import db from "@/db/db";
import { paymentTerms, incoterms, rfqLastVendorQuotationItems, rfqLastVendorAttachments, rfqLastVendorResponses, RfqsLastView, rfqLastAttachmentRevisions, rfqLastAttachments, rfqsLast, rfqsLastView, users, rfqPrItems, prItemsLastView, vendors, rfqLastDetails, rfqLastVendorResponseHistory, rfqLastDetailsView, vendorContacts, projects, basicContract, basicContractTemplates, rfqLastTbeSessions, rfqLastTbeDocumentReviews } 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";


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, '')
          );
          break;
        case "itb":
          // ITB: projectCompany가 있는 경우
          typeFilter = and(
            isNotNull(rfqsLastView.projectCompany),
            ne(rfqsLastView.projectCompany, '')
          );
          break;
        case "rfq":
          // RFQ: prNumber가 있는 경우  
          typeFilter = and(
            isNotNull(rfqsLastView.prNumber),
            ne(rfqsLastView.prNumber, '')
          );
          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}`));

      try {
        advancedWhere = filterColumns({
          table: rfqsLastView,
          filters: input.filters,
          joinOperator: input.joinOperator || 'and',
        });

        console.log("필터 조건 생성 완료");
      } 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,
      })
      .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;
  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. rfqsLast 테이블에 기본 정보 삽입
      const [newRfq] = await tx
        .insert(rfqsLast)
        .values({
          rfqCode,
          rfqType: input.rfqType,
          rfqTitle: input.rfqTitle,
          status: "RFQ 생성",
          dueDate: input.dueDate,

          // 대표 아이템 정보
          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: `${index + 1}`.padStart(3, '0'),
        prNo: rfqCode, // RFQ 코드를 PR 번호로 사용

        materialCode: item.itemCode,
        materialDescription: item.itemName,
        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;

    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) {
    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
    }
  }
}



// 벤더 추가 액션
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: user.id,
          updatedBy: user.id,
        })
        .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,

        // 금액 정보
        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,

        // 벤더 정보
        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));

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

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


// 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, // 필요시 추가
    };

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

export async function sendRfqToVendors({
  rfqId,
  rfqCode,
  vendors,
  attachmentIds,
  message,
  generatedPdfs
}: SendRfqParams & {
  generatedPdfs?: Array<{
    key: string;
    buffer: number[];
    fileName: string;
  }>;
}) {
  try {
    const session = await getServerSession(authOptions)

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

    const currentUser = session.user

    // 트랜잭션 시작
    const result = await db.transaction(async (tx) => {
      // 1. RFQ 정보 조회
      const [rfqData] = await tx
        .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));

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

      // 2. PIC 사용자 정보 조회
      let picEmail = process.env.Email_From_Address;
      let picName = rfqData.picName || "구매담당자";

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

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

      // 3. 프로젝트 정보 조회
      let projectInfo = null;
      if (rfqData.projectId) {
        const [project] = await tx
          .select()
          .from(projects)
          .where(eq(projects.id, rfqData.projectId));
        projectInfo = project;
      }

      // 4. PR 아이템 정보 조회
      const prItems = await tx
        .select()
        .from(rfqPrItems)
        .where(eq(rfqPrItems.rfqsLastId, rfqId));

      // 5. 첨부파일 정보 조회 및 준비
      const attachments = await tx
        .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`
          )
        );

      // 6. 이메일 첨부파일 준비
      const emailAttachments = [];
      for (const { attachment, revision } of attachments) {
        if (revision?.filePath) {
          try {
            const fullPath = path.join(process.cwd(), `${process.env.NAS_PATH}`, revision.filePath);
            const fileBuffer = await fs.readFile(fullPath);
            emailAttachments.push({
              filename: revision.originalFileName,
              content: fileBuffer,
              contentType: revision.fileType || 'application/octet-stream'
            });
          } catch (error) {
            console.error(`첨부파일 읽기 실패: ${revision.filePath}`, error);
          }
        }
      }

               // ========== TBE용 설계 문서 조회 (중요!) ==========
      const designAttachments = await tx
        .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, "설계") // 설계 문서만 필터링
          )
        );


      // 7. 각 벤더별 처리
      const results = [];
      const errors = [];
      const savedContracts = [];
      const tbeSessionsCreated = [];

      const contractsDir = path.join(process.cwd(), `${process.env.NAS_PATH}`, "contracts", "generated");
      await mkdir(contractsDir, { recursive: true });


      for (const vendor of vendors) {
        // 재발송 여부 확인
        const isResend = vendor.isResend || false;
        const sendVersion = (vendor.sendVersion || 0) + 1;

        // 7.4 이메일 수신자 정보 준비
        const toEmails = [vendor.selectedMainEmail];
        const ccEmails = [...vendor.additionalEmails];

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

        // 이메일 수신자 정보를 JSON으로 저장
        const emailRecipients = {
          to: toEmails,
          cc: ccEmails,
          sentBy: picEmail
        };

        try {
          // 7.1 rfqLastDetails 조회 또는 생성
          let [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에는 벤더가 이미 할당되어있는 상태이어야합니다.");
          }

          // 기존 rfqDetail을 isLatest=false로 업데이트
          const updateResult = 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)
              )
            )
            .returning({ id: rfqLastDetails.id });

          console.log(`Updated ${updateResult.length} records to isLatest=false for vendor ${vendor.vendorId}`);

          const { id, updatedBy, updatedAt, isLatest, sendVersion: oldSendVersion, emailResentCount, ...restRfqDetail } = rfqDetail;


          let [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();


          // 생성된 PDF 저장 및 DB 기록
          if (generatedPdfs && vendor.contractRequirements) {
            const vendorPdfs = generatedPdfs.filter(pdf =>
              pdf.key.startsWith(`${vendor.vendorId}_`)
            );

            for (const pdfData of vendorPdfs) {
              console.log(vendor.vendorId, pdfData.buffer.length)
              // 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);

              console.log("템플릿", templateName, template)

              // 기존 계약이 있는지 확인
              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);

              let contractRecord;

              if (existingContract) {
                // 기존 계약이 있으면 업데이트
                [contractRecord] = await tx
                  .update(basicContract)
                  .set({
                    requestedBy: Number(currentUser.id),
                    status: "PENDING", // 재발송 상태
                    fileName: fileName,
                    filePath: `/contracts/generated/${fileName}`,
                    deadline: addDays(new Date(), 10),
                    updatedAt: new Date(),
                    // version을 증가시키거나 이력 관리가 필요하면 추가
                  })
                  .where(eq(basicContract.id, existingContract.id))
                  .returning();

                console.log("기존 계약 업데이트:", contractRecord.id)
              } else {
                // 새 계약 생성
                [contractRecord] = 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();

                console.log("새 계약 생성:", contractRecord.id)
              }

              console.log(contractRecord.vendorId, contractRecord.filePath)

              savedContracts.push({
                vendorId: vendor.vendorId,
                vendorName: vendor.vendorName,
                templateName: templateName,
                contractId: contractRecord.id,
                fileName: fileName,
                isUpdated: !!existingContract // 업데이트 여부 표시
              });
            }
          }

          // 7.3 기존 응답 레코드 확인
          const existingResponses = await tx
            .select()
            .from(rfqLastVendorResponses)
            .where(
              and(
                eq(rfqLastVendorResponses.rfqsLastId, rfqId),
                eq(rfqLastVendorResponses.vendorId, vendor.vendorId)
              )
            );

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

          // 7.5 새로운 응답 레코드 생성
          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: rfqDetail.currency || "USD",

              // 감사 필드
              createdBy: currentUser.id,
              updatedBy: currentUser.id,
              createdAt: new Date(),
              updatedAt: new Date(),
            })
            .returning();

     
          // ========== TBE 세션 및 문서 검토 레코드 생성 시작 ==========
          // 7.4 기존 활성 TBE 세션 확인
          const [existingActiveTbe] = await tx
            .select()
            .from(rfqLastTbeSessions)
            .where(
              and(
                eq(rfqLastTbeSessions.rfqsLastId, rfqId),
                eq(rfqLastTbeSessions.vendorId, vendor.vendorId),
                sql`${rfqLastTbeSessions.status} IN ('준비중', '진행중', '검토중', '보류')`
              )
            );

          // 7.5 활성 TBE 세션이 없는 경우에만 새로 생성
          if (!existingActiveTbe) {
            // TBE 세션 코드 생성
            const year = new Date().getFullYear();
            const [lastTbeSession] = await tx
              .select({ sessionCode: rfqLastTbeSessions.sessionCode })
              .from(rfqLastTbeSessions)
              .where(sql`${rfqLastTbeSessions.sessionCode} LIKE 'TBE-${year}-%'`)
              .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;
            }

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

            // 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();

            console.log(`TBE 세션 생성됨: ${sessionCode} for vendor ${vendor.vendorName}`);

            // ========== 설계 문서에 대한 검토 레코드 생성 (중요!) ==========
            const documentReviewsCreated = [];
            
            for (const { attachment, revision } of designAttachments) {
              const [documentReview] = await tx
                .insert(rfqLastTbeDocumentReviews)
                .values({
                  tbeSessionId: tbeSession.id,
                  documentSource: "buyer",
                  buyerAttachmentId: attachment.id,
                  buyerAttachmentRevisionId: revision?.id || null,
                  vendorAttachmentId: null, // 구매자 문서이므로 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(),
                })
                .returning();

              documentReviewsCreated.push({
                reviewId: documentReview.id,
                attachmentId: attachment.id,
                documentName: documentReview.documentName
              });

              console.log(`문서 검토 레코드 생성: ${documentReview.documentName}`);
            }

            tbeSessionsCreated.push({
              vendorId: vendor.vendorId,
              vendorName: vendor.vendorName,
              sessionId: tbeSession.id,
              sessionCode: tbeSession.sessionCode,
              documentReviewsCount: documentReviewsCreated.length
            });

            console.log(`TBE 세션 ${sessionCode}: 총 ${documentReviewsCreated.length}개 문서 검토 레코드 생성`);
          } else {
            console.log(`TBE 세션이 이미 존재함: vendor ${vendor.vendorName}`);
          }
          // ========== TBE 세션 및 문서 검토 레코드 생성 끝 ==========

        }

      // 8. RFQ 상태 업데이트
      if (results.length > 0) {
          await tx
            .update(rfqsLast)
            .set({
              status: "RFQ 발송",
              rfqSendDate: new Date(),
              sentBy: Number(currentUser.id),
              updatedBy: Number(currentUser.id),
              updatedAt: new Date(),
            })
            .where(eq(rfqsLast.id, rfqId));
        }

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

    return result;

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

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 = []

    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: "마감일 수정 중 오류가 발생했습니다.",
    }
  }
}


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