path: root/lib/b-rfq/service.ts

'use server'

import { revalidateTag, unstable_cache ,unstable_noStore} from "next/cache"
import {count, desc, asc, and, or, gte, lte, ilike, eq, inArray, sql } from "drizzle-orm"
import { filterColumns } from "@/lib/filter-columns"
import db from "@/db/db"
import {   vendorResponseDetailView, 
  attachmentRevisionHistoryView,
  rfqProgressSummaryView,
  vendorResponseAttachmentsEnhanced ,Incoterm, RfqDashboardView, Vendor, VendorAttachmentResponse, bRfqAttachmentRevisions, bRfqs, bRfqsAttachments, incoterms, initialRfq, initialRfqDetailView, projects, users, vendorAttachmentResponses, vendors, 
  vendorResponseAttachmentsB} from "@/db/schema" // 실제 스키마 import 경로에 맞게 수정
import { rfqDashboardView } from "@/db/schema" // 뷰 import
import type { SQL } from "drizzle-orm"
import { AttachmentRecord, BulkEmailInput, CreateRfqInput, DeleteAttachmentsInput, GetInitialRfqDetailSchema, GetRFQDashboardSchema, GetRfqAttachmentsSchema, GetVendorResponsesSchema, RemoveInitialRfqsSchema, RequestRevisionResult, ResponseStatus, UpdateInitialRfqSchema, VendorRfqResponseSummary, attachmentRecordSchema, bulkEmailSchema, createRfqServerSchema, deleteAttachmentsSchema, removeInitialRfqsSchema, requestRevisionSchema, updateInitialRfqSchema } from "./validations"
import { getServerSession } from "next-auth/next"
import { authOptions } from "@/app/api/auth/[...nextauth]/route"
import { unlink } from "fs/promises"
import { getErrorMessage } from "../handle-error"
import { AddInitialRfqFormData } from "./initial/add-initial-rfq-dialog"
import { sendEmail } from "../mail/sendEmail"
import { RfqType } from "../rfqs/validations"

const tag = {
  initialRfqDetail:"initial-rfq",
  rfqDashboard: 'rfq-dashboard',
  rfq: (id: number) => `rfq-${id}`,
  rfqAttachments: (rfqId: number) => `rfq-attachments-${rfqId}`,
  attachmentRevisions: (attId: number) => `attachment-revisions-${attId}`,
  vendorResponses: (
    attId: number,
    type: 'INITIAL' | 'FINAL' = 'INITIAL',
  ) => `vendor-responses-${attId}-${type}`,
} as const;

export async function getRFQDashboard(input: GetRFQDashboardSchema) {

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

        const rfqFilterMapping = createRFQFilterMapping();
        const joinedTables = getRFQJoinedTables();

        console.log(input, "견적 인풋")

        // 1) 고급 필터 조건
        let advancedWhere: SQL<unknown> | undefined = undefined;
        if (input.filters && input.filters.length > 0) {
          advancedWhere = filterColumns({
            table: rfqDashboardView,
            filters: input.filters,
            joinOperator: input.joinOperator || 'and',
            joinedTables,
            customColumnMapping: rfqFilterMapping,
          });
        }

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

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

          const validSearchConditions: SQL<unknown>[] = [];

          const rfqCodeCondition = ilike(rfqDashboardView.rfqCode, s);
          if (rfqCodeCondition) validSearchConditions.push(rfqCodeCondition);

          const descriptionCondition = ilike(rfqDashboardView.description, s);
          if (descriptionCondition) validSearchConditions.push(descriptionCondition);

          const projectNameCondition = ilike(rfqDashboardView.projectName, s);
          if (projectNameCondition) validSearchConditions.push(projectNameCondition);

          const projectCodeCondition = ilike(rfqDashboardView.projectCode, s);
          if (projectCodeCondition) validSearchConditions.push(projectCodeCondition);

          const picNameCondition = ilike(rfqDashboardView.picName, s);
          if (picNameCondition) validSearchConditions.push(picNameCondition);

          const packageNoCondition = ilike(rfqDashboardView.packageNo, s);
          if (packageNoCondition) validSearchConditions.push(packageNoCondition);

          const packageNameCondition = ilike(rfqDashboardView.packageName, s);
          if (packageNameCondition) validSearchConditions.push(packageNameCondition);

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



        // 6) 최종 WHERE 조건 생성
        const whereConditions: SQL<unknown>[] = [];

        if (advancedWhere) whereConditions.push(advancedWhere);
        if (basicWhere) whereConditions.push(basicWhere);
        if (globalWhere) whereConditions.push(globalWhere);

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

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

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

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

        console.log(total)

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

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

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

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

        return { data: rfqData, pageCount, total };
      } catch (err) {
        console.error("Error in getRFQDashboard:", err);
        return { data: [], pageCount: 0, total: 0 };
      }
   
}

// 헬퍼 함수들
function createRFQFilterMapping() {
  return {
    // 뷰의 컬럼명과 실제 필터링할 컬럼 매핑
    rfqCode: rfqDashboardView.rfqCode,
    description: rfqDashboardView.description,
    status: rfqDashboardView.status,
    projectName: rfqDashboardView.projectName,
    projectCode: rfqDashboardView.projectCode,
    picName: rfqDashboardView.picName,
    packageNo: rfqDashboardView.packageNo,
    packageName: rfqDashboardView.packageName,
    dueDate: rfqDashboardView.dueDate,
    overallProgress: rfqDashboardView.overallProgress,
    createdAt: rfqDashboardView.createdAt,
  };
}

function getRFQJoinedTables() {
  return {
    // 조인된 테이블 정보 (뷰이므로 실제로는 사용되지 않을 수 있음)
    projects,
    users,
  };
}

// ================================================================
// 3. RFQ Dashboard 타입 정의
// ================================================================

async function generateNextSerial(picCode: string): Promise<string> {
  try {
    // 해당 picCode로 시작하는 RFQ 개수 조회
    const existingCount = await db
      .select({ count: count() })
      .from(bRfqs)
      .where(eq(bRfqs.picCode, picCode))

    const nextSerial = (existingCount[0]?.count || 0) + 1
    return nextSerial.toString().padStart(5, '0') // 5자리로 패딩
  } catch (error) {
    console.error("시리얼 번호 생성 오류:", error)
    return "00001" // 기본값
  }
}

export async function createRfqAction(input: CreateRfqInput) {
  try {
    // 입력 데이터 검증
    const validatedData = createRfqServerSchema.parse(input)

    // RFQ 코드 자동 생성: N + picCode + 시리얼5자리
    const serialNumber = await generateNextSerial(validatedData.picCode)
    const rfqCode = `N${validatedData.picCode}${serialNumber}`

    // 데이터베이스에 삽입
    const result = await db.insert(bRfqs).values({
      rfqCode,
      projectId: validatedData.projectId,
      dueDate: validatedData.dueDate,
      status: "DRAFT",
      picCode: validatedData.picCode,
      picName: validatedData.picName || null,
      EngPicName: validatedData.engPicName || null,
      packageNo: validatedData.packageNo || null,
      packageName: validatedData.packageName || null,
      remark: validatedData.remark || null,
      projectCompany: validatedData.projectCompany || null,
      projectFlag: validatedData.projectFlag || null,
      projectSite: validatedData.projectSite || null,
      createdBy: validatedData.createdBy,
      updatedBy: validatedData.updatedBy,
    }).returning({
      id: bRfqs.id,
      rfqCode: bRfqs.rfqCode,
    })



    return {
      success: true,
      data: result[0],
      message: "RFQ가 성공적으로 생성되었습니다",
    }

  } catch (error) {
    console.error("RFQ 생성 오류:", error)


    return {
      success: false,
      error: "RFQ 생성에 실패했습니다",
    }
  }
}

// RFQ 코드 중복 확인 액션
export async function checkRfqCodeExists(rfqCode: string) {
  try {
    const existing = await db.select({ id: bRfqs.id })
      .from(bRfqs)
      .where(eq(bRfqs.rfqCode, rfqCode))
      .limit(1)

    return existing.length > 0
  } catch (error) {
    console.error("RFQ 코드 확인 오류:", error)
    return false
  }
}

// picCode별 다음 예상 RFQ 코드 미리보기
export async function previewNextRfqCode(picCode: string) {
  try {
    const serialNumber = await generateNextSerial(picCode)
    return `N${picCode}${serialNumber}`
  } catch (error) {
    console.error("RFQ 코드 미리보기 오류:", error)
    return `N${picCode}00001`
  }
}

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

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

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

  };

  return result;
};


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

    const rfq = await getBRfqById(id);

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


export async function getRfqAttachments(
  input: GetRfqAttachmentsSchema,
  rfqId: number
) {
      try {
        const offset = (input.page - 1) * input.perPage

        // Advanced Filter 처리 (메인 테이블 기준)
        const advancedWhere = filterColumns({
          table: bRfqsAttachments,
          filters: input.filters,
          joinOperator: input.joinOperator,
        })

        // 전역 검색 (첨부파일 + 리비전 파일명 검색)
        let globalWhere
        if (input.search) {
          const s = `%${input.search}%`
          globalWhere = or(
            ilike(bRfqsAttachments.serialNo, s),
            ilike(bRfqsAttachments.description, s),
            ilike(bRfqsAttachments.currentRevision, s),
            ilike(bRfqAttachmentRevisions.fileName, s),
            ilike(bRfqAttachmentRevisions.originalFileName, s)
          )
        }

        // 기본 필터
        let basicWhere
        if (input.attachmentType.length > 0 || input.fileType.length > 0) {
          basicWhere = and(
            input.attachmentType.length > 0
              ? inArray(bRfqsAttachments.attachmentType, input.attachmentType)
              : undefined,
            input.fileType.length > 0
              ? inArray(bRfqAttachmentRevisions.fileType, input.fileType)
              : undefined
          )
        }

        // 최종 WHERE 절
        const finalWhere = and(
          eq(bRfqsAttachments.rfqId, rfqId), // RFQ ID 필수 조건
          advancedWhere,
          globalWhere,
          basicWhere
        )

        // 정렬 (메인 테이블 기준)
        const orderBy = input.sort.length > 0
          ? input.sort.map((item) =>
            item.desc ? desc(bRfqsAttachments[item.id as keyof typeof bRfqsAttachments]) : asc(bRfqsAttachments[item.id as keyof typeof bRfqsAttachments])
          )
          : [desc(bRfqsAttachments.createdAt)]

        // 트랜잭션으로 데이터 조회
        const { data, total } = await db.transaction(async (tx) => {
          // 메인 데이터 조회 (첨부파일 + 최신 리비전 조인)
          const data = await tx
            .select({
              // 첨부파일 메인 정보
              id: bRfqsAttachments.id,
              attachmentType: bRfqsAttachments.attachmentType,
              serialNo: bRfqsAttachments.serialNo,
              rfqId: bRfqsAttachments.rfqId,
              currentRevision: bRfqsAttachments.currentRevision,
              latestRevisionId: bRfqsAttachments.latestRevisionId,
              description: bRfqsAttachments.description,
              createdBy: bRfqsAttachments.createdBy,
              createdAt: bRfqsAttachments.createdAt,
              updatedAt: bRfqsAttachments.updatedAt,

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

              // 생성자 정보
              createdByName: users.name,
            })
            .from(bRfqsAttachments)
            .leftJoin(
              bRfqAttachmentRevisions,
              and(
                eq(bRfqsAttachments.latestRevisionId, bRfqAttachmentRevisions.id),
                eq(bRfqAttachmentRevisions.isLatest, true)
              )
            )
            .leftJoin(users, eq(bRfqsAttachments.createdBy, users.id))
            .where(finalWhere)
            .orderBy(...orderBy)
            .limit(input.perPage)
            .offset(offset)

          // 전체 개수 조회
          const totalResult = await tx
            .select({ count: count() })
            .from(bRfqsAttachments)
            .leftJoin(
              bRfqAttachmentRevisions,
              eq(bRfqsAttachments.latestRevisionId, bRfqAttachmentRevisions.id)
            )
            .where(finalWhere)

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

          return { data, total }
        })

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

        // 각 첨부파일별 벤더 응답 통계 조회
        const attachmentIds = data.map(item => item.id)
        let responseStatsMap: Record<number, any> = {}

        if (attachmentIds.length > 0) {
          responseStatsMap = await getAttachmentResponseStats(attachmentIds)
        }

        // 통계 데이터 병합
        const dataWithStats = data.map(attachment => ({
          ...attachment,
          responseStats: responseStatsMap[attachment.id] || {
            totalVendors: 0,
            respondedCount: 0,
            pendingCount: 0,
            waivedCount: 0,
            responseRate: 0
          }
        }))

        return { data: dataWithStats, pageCount }
      } catch (err) {
        console.error("getRfqAttachments error:", err)
        return { data: [], pageCount: 0 }
      }
  
}

// 첨부파일별 벤더 응답 통계 조회
async function getAttachmentResponseStats(attachmentIds: number[]) {
  try {
    const stats = await db
      .select({
        attachmentId: vendorAttachmentResponses.attachmentId,
        totalVendors: count(),
        respondedCount: sql<number>`count(case when ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' then 1 end)`,
        pendingCount: sql<number>`count(case when ${vendorAttachmentResponses.responseStatus} = 'NOT_RESPONDED' then 1 end)`,
        waivedCount: sql<number>`count(case when ${vendorAttachmentResponses.responseStatus} = 'WAIVED' then 1 end)`,
      })
      .from(vendorAttachmentResponses)
      .where(inArray(vendorAttachmentResponses.attachmentId, attachmentIds))
      .groupBy(vendorAttachmentResponses.attachmentId)

    // 응답률 계산해서 객체로 변환
    const statsMap: Record<number, any> = {}
    stats.forEach(stat => {
      const activeVendors = stat.totalVendors - stat.waivedCount
      const responseRate = activeVendors > 0
        ? Math.round((stat.respondedCount / activeVendors) * 100)
        : 0

      statsMap[stat.attachmentId] = {
        totalVendors: stat.totalVendors,
        respondedCount: stat.respondedCount,
        pendingCount: stat.pendingCount,
        waivedCount: stat.waivedCount,
        responseRate
      }
    })

    return statsMap
  } catch (error) {
    console.error("getAttachmentResponseStats error:", error)
    return {}
  }
}

// 특정 첨부파일에 대한 벤더 응답 현황 상세 조회
export async function getVendorResponsesForAttachment(
  attachmentId: number,
  rfqType: 'INITIAL' | 'FINAL' = 'INITIAL'
) {
  try {
    // 1. 기본 벤더 응답 정보 가져오기
    const responses = await db
      .select({
        id: vendorAttachmentResponses.id,
        attachmentId: vendorAttachmentResponses.attachmentId,
        vendorId: vendorAttachmentResponses.vendorId,
        vendorCode: vendors.vendorCode,
        vendorName: vendors.vendorName,
        vendorCountry: vendors.country,
        rfqType: vendorAttachmentResponses.rfqType,
        rfqRecordId: vendorAttachmentResponses.rfqRecordId,
        responseStatus: vendorAttachmentResponses.responseStatus,
        currentRevision: vendorAttachmentResponses.currentRevision,
        respondedRevision: vendorAttachmentResponses.respondedRevision,
        responseComment: vendorAttachmentResponses.responseComment,
        vendorComment: vendorAttachmentResponses.vendorComment,
        // 새로 추가된 필드들
        revisionRequestComment: vendorAttachmentResponses.revisionRequestComment,
        revisionRequestedAt: vendorAttachmentResponses.revisionRequestedAt,
        requestedAt: vendorAttachmentResponses.requestedAt,
        respondedAt: vendorAttachmentResponses.respondedAt,
        updatedAt: vendorAttachmentResponses.updatedAt,
      })
      .from(vendorAttachmentResponses)
      .leftJoin(vendors, eq(vendorAttachmentResponses.vendorId, vendors.id))
      .where(
        and(
          eq(vendorAttachmentResponses.attachmentId, attachmentId),
          eq(vendorAttachmentResponses.rfqType, rfqType)
        )
      )
      .orderBy(vendors.vendorName);

    // 2. 각 응답에 대한 파일 정보 가져오기
    const responseIds = responses.map(r => r.id);
        
    let responseFiles: any[] = [];
    if (responseIds.length > 0) {
      responseFiles = await db
        .select({
          id: vendorResponseAttachmentsB.id,
          vendorResponseId: vendorResponseAttachmentsB.vendorResponseId,
          fileName: vendorResponseAttachmentsB.fileName,
          originalFileName: vendorResponseAttachmentsB.originalFileName,
          filePath: vendorResponseAttachmentsB.filePath,
          fileSize: vendorResponseAttachmentsB.fileSize,
          fileType: vendorResponseAttachmentsB.fileType,
          description: vendorResponseAttachmentsB.description,
          uploadedAt: vendorResponseAttachmentsB.uploadedAt,
        })
        .from(vendorResponseAttachmentsB)
        .where(inArray(vendorResponseAttachmentsB.vendorResponseId, responseIds))
        .orderBy(desc(vendorResponseAttachmentsB.uploadedAt));
    }

    // 3. 응답에 파일 정보 병합
    const enhancedResponses = responses.map(response => ({
      ...response,
      files: responseFiles.filter(file => file.vendorResponseId === response.id),
      totalFiles: responseFiles.filter(file => file.vendorResponseId === response.id).length,
      latestFile: responseFiles
        .filter(file => file.vendorResponseId === response.id)
        .sort((a, b) => new Date(b.uploadedAt).getTime() - new Date(a.uploadedAt).getTime())[0] || null,
    }));

    return enhancedResponses;
  } catch (err) {
    console.error("getVendorResponsesForAttachment error:", err);
    return [];
  }
}

export async function confirmDocuments(rfqId: number) {
  try {
    const session = await getServerSession(authOptions)
    if (!session?.user?.id) {
      throw new Error("인증이 필요합니다.")
    }

    // TODO: RFQ 상태를 "Doc. Confirmed"로 업데이트
    await db
      .update(bRfqs)
      .set({
        status: "Doc. Confirmed",
        updatedBy: Number(session.user.id),
        updatedAt: new Date(),
      })
      .where(eq(bRfqs.id, rfqId))


    return {
      success: true,
      message: "문서가 확정되었습니다.",
    }

  } catch (error) {
    console.error("confirmDocuments error:", error)
    return {
      success: false,
      message: error instanceof Error ? error.message : "문서 확정 중 오류가 발생했습니다.",
    }
  }
}

// TBE 요청 서버 액션
export async function requestTbe(rfqId: number, attachmentIds?: number[]) {
  try {
    const session = await getServerSession(authOptions)
    if (!session?.user?.id) {
      throw new Error("인증이 필요합니다.")
    }

    // attachmentIds가 제공된 경우 해당 첨부파일들만 처리
    let targetAttachments = []
    if (attachmentIds && attachmentIds.length > 0) {
      // 선택된 첨부파일들 조회
      targetAttachments = await db
        .select({
          id: bRfqsAttachments.id,
          serialNo: bRfqsAttachments.serialNo,
          attachmentType: bRfqsAttachments.attachmentType,
          currentRevision: bRfqsAttachments.currentRevision,
        })
        .from(bRfqsAttachments)
        .where(
          and(
            eq(bRfqsAttachments.rfqId, rfqId),
            inArray(bRfqsAttachments.id, attachmentIds)
          )
        )

      if (targetAttachments.length === 0) {
        throw new Error("선택된 첨부파일을 찾을 수 없습니다.")
      }
    } else {
      // 전체 RFQ의 모든 첨부파일 처리
      targetAttachments = await db
        .select({
          id: bRfqsAttachments.id,
          serialNo: bRfqsAttachments.serialNo,
          attachmentType: bRfqsAttachments.attachmentType,
          currentRevision: bRfqsAttachments.currentRevision,
        })
        .from(bRfqsAttachments)
        .where(eq(bRfqsAttachments.rfqId, rfqId))
    }

    if (targetAttachments.length === 0) {
      throw new Error("TBE 요청할 첨부파일이 없습니다.")
    }

    // TODO: TBE 요청 로직 구현
    // 1. RFQ 상태를 "TBE started"로 업데이트 (선택적)
    // 2. 선택된 첨부파일들에 대해 벤더들에게 TBE 요청 이메일 발송
    // 3. vendorAttachmentResponses 테이블에 TBE 요청 레코드 생성
    // 4. TBE 관련 메타데이터 업데이트



    // 예시: 선택된 첨부파일들에 대한 벤더 응답 레코드 생성
    await db.transaction(async (tx) => {

      const [updatedRfq] = await tx
      .update(bRfqs)
      .set({
        status: "TBE started",
        updatedBy: Number(session.user.id),
        updatedAt: new Date(),
      })
      .where(eq(bRfqs.id, rfqId))
      .returning()

      // 각 첨부파일에 대해 벤더 응답 레코드 생성 또는 업데이트
      for (const attachment of targetAttachments) {
        // TODO: 해당 첨부파일과 연관된 벤더들에게 TBE 요청 처리
        console.log(`TBE 요청 처리: ${attachment.serialNo} (${attachment.currentRevision})`)
      }
    })

  
    const attachmentCount = targetAttachments.length
    const attachmentList = targetAttachments
      .map(a => `${a.serialNo} (${a.currentRevision})`)
      .join(', ')

    return {
      success: true,
      message: `${attachmentCount}개 문서에 대한 TBE 요청이 전송되었습니다.\n대상: ${attachmentList}`,
      targetAttachments,
    }

  } catch (error) {
    console.error("requestTbe error:", error)
    return {
      success: false,
      message: error instanceof Error ? error.message : "TBE 요청 중 오류가 발생했습니다.",
    }
  }
}

// 다음 시리얼 번호 생성
async function getNextSerialNo(rfqId: number): Promise<string> {
  try {
    // 해당 RFQ의 기존 첨부파일 개수 조회
    const [result] = await db
      .select({ count: count() })
      .from(bRfqsAttachments)
      .where(eq(bRfqsAttachments.rfqId, rfqId))

    const nextNumber = (result?.count || 0) + 1

    // 001, 002, 003... 형태로 포맷팅
    return nextNumber.toString().padStart(3, '0')

  } catch (error) {
    console.error("getNextSerialNo error:", error)
    // 에러 발생 시 타임스탬프 기반으로 fallback
    return Date.now().toString().slice(-3)
  }
}

export async function addRfqAttachmentRecord(record: AttachmentRecord) {
  try {
    const session = await getServerSession(authOptions)
    if (!session?.user?.id) {
      throw new Error("인증이 필요합니다.")
    }

    const validatedRecord = attachmentRecordSchema.parse(record)
    const userId = Number(session.user.id)

    const result = await db.transaction(async (tx) => {
      // 1. 시리얼 번호 생성
      const [countResult] = await tx
        .select({ count: count() })
        .from(bRfqsAttachments)
        .where(eq(bRfqsAttachments.rfqId, validatedRecord.rfqId))

      const serialNo = (countResult.count + 1).toString().padStart(3, '0')

      // 2. 메인 첨부파일 레코드 생성
      const [attachment] = await tx
        .insert(bRfqsAttachments)
        .values({
          rfqId: validatedRecord.rfqId,
          attachmentType: validatedRecord.attachmentType,
          serialNo: serialNo,
          currentRevision: "Rev.0",
          description: validatedRecord.description,
          createdBy: userId,
        })
        .returning()

      // 3. 초기 리비전 (Rev.0) 생성
      const [revision] = await tx
        .insert(bRfqAttachmentRevisions)
        .values({
          attachmentId: attachment.id,
          revisionNo: "Rev.0",
          fileName: validatedRecord.fileName,
          originalFileName: validatedRecord.originalFileName,
          filePath: validatedRecord.filePath,
          fileSize: validatedRecord.fileSize,
          fileType: validatedRecord.fileType,
          revisionComment: validatedRecord.revisionComment,
          isLatest: true,
          createdBy: userId,
        })
        .returning()

      // 4. 메인 테이블의 latest_revision_id 업데이트
      await tx
        .update(bRfqsAttachments)
        .set({
          latestRevisionId: revision.id,
          updatedAt: new Date(),
        })
        .where(eq(bRfqsAttachments.id, attachment.id))

      return { attachment, revision }
    })

     return {
      success: true,
      message: `파일이 성공적으로 등록되었습니다. (시리얼: ${result.attachment.serialNo}, 리비전: Rev.0)`,
      attachment: result.attachment,
      revision: result.revision,
    }

  } catch (error) {
    console.error("addRfqAttachmentRecord error:", error)
    return {
      success: false,
      message: error instanceof Error ? error.message : "첨부파일 등록 중 오류가 발생했습니다.",
    }
  }
}

// 리비전 추가 (기존 첨부파일에 새 버전 추가)
export async function addRevisionToAttachment(
  attachmentId: number,
  revisionData: {
    fileName: string;
    originalFileName: string;
    filePath: string;
    fileSize: number;
    fileType: string;
    revisionComment?: string;
  },
) {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user?.id) throw new Error('인증이 필요합니다.');

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

    // ────────────────────────────────────────────────────────────────────────────
    // 0. 첨부파일의 rfqId 사전 조회 (태그 무효화를 위해 필요)
    // ────────────────────────────────────────────────────────────────────────────
    const [attInfo] = await db
      .select({ rfqId: bRfqsAttachments.rfqId })
      .from(bRfqsAttachments)
      .where(eq(bRfqsAttachments.id, attachmentId))
      .limit(1);

    if (!attInfo) throw new Error('첨부파일을 찾을 수 없습니다.');
    const rfqId = attInfo.rfqId;

    // ────────────────────────────────────────────────────────────────────────────
    // 1‑5. 리비전 트랜잭션
    // ────────────────────────────────────────────────────────────────────────────
    const newRevision = await db.transaction(async (tx) => {
      // 1. 현재 최신 리비전 조회
      const [latestRevision] = await tx
        .select({ revisionNo: bRfqAttachmentRevisions.revisionNo })
        .from(bRfqAttachmentRevisions)
        .where(
          and(
            eq(bRfqAttachmentRevisions.attachmentId, attachmentId),
            eq(bRfqAttachmentRevisions.isLatest, true),
          ),
        );

      if (!latestRevision) throw new Error('기존 첨부파일을 찾을 수 없습니다.');

      // 2. 새 리비전 번호 생성
      const currentNum = parseInt(latestRevision.revisionNo.replace('Rev.', ''));
      const newRevisionNo = `Rev.${currentNum + 1}`;

      // 3. 기존 리비전 isLatest → false
      await tx
        .update(bRfqAttachmentRevisions)
        .set({ isLatest: false })
        .where(
          and(
            eq(bRfqAttachmentRevisions.attachmentId, attachmentId),
            eq(bRfqAttachmentRevisions.isLatest, true),
          ),
        );

      // 4. 새 리비전 INSERT
      const [inserted] = await tx
        .insert(bRfqAttachmentRevisions)
        .values({
          attachmentId,
          revisionNo: newRevisionNo,
          fileName: revisionData.fileName,
          originalFileName: revisionData.originalFileName,
          filePath: revisionData.filePath,
          fileSize: revisionData.fileSize,
          fileType: revisionData.fileType,
          revisionComment: revisionData.revisionComment ?? `${newRevisionNo} 업데이트`,
          isLatest: true,
          createdBy: userId,
        })
        .returning();

      // 5. 메인 첨부파일 row 업데이트
      await tx
        .update(bRfqsAttachments)
        .set({
          currentRevision: newRevisionNo,
          latestRevisionId: inserted.id,
          updatedAt: new Date(),
        })
        .where(eq(bRfqsAttachments.id, attachmentId));

      return inserted;
    });

   

    return {
      success: true,
      message: `새 리비전(${newRevision.revisionNo})이 성공적으로 추가되었습니다.`,
      revision: newRevision,
    };
  } catch (error) {
    console.error('addRevisionToAttachment error:', error);
    return {
      success: false,
      message: error instanceof Error ? error.message : '리비전 추가 중 오류가 발생했습니다.',
    };
  }
}

// 특정 첨부파일의 모든 리비전 조회
export async function getAttachmentRevisions(attachmentId: number) {

      try {
        const revisions = await db
          .select({
            id: bRfqAttachmentRevisions.id,
            revisionNo: bRfqAttachmentRevisions.revisionNo,
            fileName: bRfqAttachmentRevisions.fileName,
            originalFileName: bRfqAttachmentRevisions.originalFileName,
            filePath: bRfqAttachmentRevisions.filePath,
            fileSize: bRfqAttachmentRevisions.fileSize,
            fileType: bRfqAttachmentRevisions.fileType,
            revisionComment: bRfqAttachmentRevisions.revisionComment,
            isLatest: bRfqAttachmentRevisions.isLatest,
            createdBy: bRfqAttachmentRevisions.createdBy,
            createdAt: bRfqAttachmentRevisions.createdAt,
            createdByName: users.name,
          })
          .from(bRfqAttachmentRevisions)
          .leftJoin(users, eq(bRfqAttachmentRevisions.createdBy, users.id))
          .where(eq(bRfqAttachmentRevisions.attachmentId, attachmentId))
          .orderBy(desc(bRfqAttachmentRevisions.createdAt))

        return {
          success: true,
          revisions,
        }
      } catch (error) {
        console.error("getAttachmentRevisions error:", error)
        return {
          success: false,
          message: "리비전 조회 중 오류가 발생했습니다.",
          revisions: [],
        }
      }
}


// 첨부파일 삭제 (리비전 포함)
export async function deleteRfqAttachments(input: DeleteAttachmentsInput) {
  try {
    const session = await getServerSession(authOptions)
    if (!session?.user?.id) {
      throw new Error("인증이 필요합니다.")
    }

    const validatedInput = deleteAttachmentsSchema.parse(input)

    const result = await db.transaction(async (tx) => {
      // 1. 삭제할 첨부파일들의 정보 조회 (파일 경로 포함)
      const attachmentsToDelete = await tx
        .select({
          id: bRfqsAttachments.id,
          rfqId: bRfqsAttachments.rfqId,
          serialNo: bRfqsAttachments.serialNo,
        })
        .from(bRfqsAttachments)
        .where(inArray(bRfqsAttachments.id, validatedInput.ids))

      if (attachmentsToDelete.length === 0) {
        throw new Error("삭제할 첨부파일을 찾을 수 없습니다.")
      }

      // 2. 관련된 모든 리비전 파일 경로 조회
      const revisionFilePaths = await tx
        .select({ filePath: bRfqAttachmentRevisions.filePath })
        .from(bRfqAttachmentRevisions)
        .where(inArray(bRfqAttachmentRevisions.attachmentId, validatedInput.ids))

      // 3. DB에서 리비전 삭제 (CASCADE로 자동 삭제되지만 명시적으로)
      await tx
        .delete(bRfqAttachmentRevisions)
        .where(inArray(bRfqAttachmentRevisions.attachmentId, validatedInput.ids))

      // 4. DB에서 첨부파일 삭제
      await tx
        .delete(bRfqsAttachments)
        .where(inArray(bRfqsAttachments.id, validatedInput.ids))

      // 5. 실제 파일 삭제 (비동기로 처리)
      Promise.all(
        revisionFilePaths.map(async ({ filePath }) => {
          try {
            if (filePath) {
              const fullPath = `${process.cwd()}/public${filePath}`
              await unlink(fullPath)
            }
          } catch (fileError) {
            console.warn(`Failed to delete file: ${filePath}`, fileError)
          }
        })
      ).catch(error => {
        console.error("Some files failed to delete:", error)
      })

      return {
        deletedCount: attachmentsToDelete.length,
        rfqIds: [...new Set(attachmentsToDelete.map(a => a.rfqId))],
        attachments: attachmentsToDelete,
      }
    })

  
    return {
      success: true,
      message: `${result.deletedCount}개의 첨부파일이 삭제되었습니다.`,
      deletedAttachments: result.attachments,
    }

  } catch (error) {
    console.error("deleteRfqAttachments error:", error)
    
    return {
      success: false,
      message: error instanceof Error ? error.message : "첨부파일 삭제 중 오류가 발생했습니다.",
    }
  }
}



//Initial RFQ

export async function getInitialRfqDetail(input: GetInitialRfqDetailSchema, rfqId?: number) {

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

        // 1) 고급 필터 조건
        let advancedWhere: SQL<unknown> | undefined = undefined;
        if (input.filters && input.filters.length > 0) {
          advancedWhere = filterColumns({
            table: initialRfqDetailView,
            filters: input.filters,
            joinOperator: input.joinOperator || 'and',
          });
        }

        // 2) 기본 필터 조건
        let basicWhere: SQL<unknown> | undefined = undefined;
        if (input.basicFilters && input.basicFilters.length > 0) {
          basicWhere = filterColumns({
            table: initialRfqDetailView,
            filters: input.basicFilters,
            joinOperator: input.basicJoinOperator || 'and',
          });
        }

               let rfqIdWhere: SQL<unknown> | undefined = undefined;
        if (rfqId) {
          rfqIdWhere = eq(initialRfqDetailView.rfqId, rfqId);
        }


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

          const validSearchConditions: SQL<unknown>[] = [];

          const rfqCodeCondition = ilike(initialRfqDetailView.rfqCode, s);
          if (rfqCodeCondition) validSearchConditions.push(rfqCodeCondition);

          const vendorNameCondition = ilike(initialRfqDetailView.vendorName, s);
          if (vendorNameCondition) validSearchConditions.push(vendorNameCondition);

          const vendorCodeCondition = ilike(initialRfqDetailView.vendorCode, s);
          if (vendorCodeCondition) validSearchConditions.push(vendorCodeCondition);

          const vendorCountryCondition = ilike(initialRfqDetailView.vendorCountry, s);
          if (vendorCountryCondition) validSearchConditions.push(vendorCountryCondition);

          const incotermsDescriptionCondition = ilike(initialRfqDetailView.incotermsDescription, s);
          if (incotermsDescriptionCondition) validSearchConditions.push(incotermsDescriptionCondition);

          const classificationCondition = ilike(initialRfqDetailView.classification, s);
          if (classificationCondition) validSearchConditions.push(classificationCondition);

          const sparepartCondition = ilike(initialRfqDetailView.sparepart, s);
          if (sparepartCondition) validSearchConditions.push(sparepartCondition);

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


        // 5) 최종 WHERE 조건 생성
        const whereConditions: SQL<unknown>[] = [];

        if (advancedWhere) whereConditions.push(advancedWhere);
        if (basicWhere) whereConditions.push(basicWhere);
        if (globalWhere) whereConditions.push(globalWhere);
        if (rfqIdWhere) whereConditions.push(rfqIdWhere);

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

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

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

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

        console.log(totalResult);
        console.log(total);

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

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

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

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

        return { data: initialRfqData, pageCount, total };
      } catch (err) {
        console.error("Error in getInitialRfqDetail:", err);
        return { data: [], pageCount: 0, total: 0 };
      }
}

export async function getVendorsForSelection() {
  try {
    const vendorsData = await db
      .select({
        id: vendors.id,
        vendorName: vendors.vendorName,
        vendorCode: vendors.vendorCode,
        taxId: vendors.taxId,
        country: vendors.country,
        status: vendors.status,
      })
      .from(vendors)
      // .where(
      //   and(
      //     ne(vendors.status, "BLACKLISTED"),
      //     ne(vendors.status, "REJECTED")
      //   )
      // )
      .orderBy(vendors.vendorName)

    return vendorsData.map(vendor => ({
      id: vendor.id,
      vendorName: vendor.vendorName || "",
      vendorCode: vendor.vendorCode || "",
      country: vendor.country || "",
      status: vendor.status,
    }))
  } catch (error) {
    console.error("Error fetching vendors:", error)
    throw new Error("Failed to fetch vendors")
  }
}

export async function addInitialRfqRecord(data: AddInitialRfqFormData & { rfqId: number }) {
  try {
    console.log('Incoming data:', data);
    
    const [newRecord] = await db
      .insert(initialRfq)
      .values({
        rfqId: data.rfqId,
        vendorId: data.vendorId,
        initialRfqStatus: data.initialRfqStatus,
        dueDate: data.dueDate,
        validDate: data.validDate,
        incotermsCode: data.incotermsCode,
        gtc: data.gtc,
        gtcValidDate: data.gtcValidDate,
        classification: data.classification,
        sparepart: data.sparepart,
        shortList: data.shortList,
        returnYn: data.returnYn,
        cpRequestYn: data.cpRequestYn,
        prjectGtcYn: data.prjectGtcYn,
        returnRevision: data.returnRevision,
      })
      .returning()

    return {
      success: true,
      message: "초기 RFQ가 성공적으로 추가되었습니다.",
      data: newRecord,
    }
  } catch (error) {
    console.error("Error adding initial RFQ:", error)
    return {
      success: false,
      message: "초기 RFQ 추가에 실패했습니다.",
      error,
    }
  }
}

export async function getIncotermsForSelection() {
  try {
    const incotermData = await db
      .select({
        code: incoterms.code,
        description: incoterms.description,
      })
      .from(incoterms)
      .orderBy(incoterms.code)

    return incotermData

  } catch (error) {
    console.error("Error fetching incoterms:", error)
    throw new Error("Failed to fetch incoterms")
  }
}

export async function removeInitialRfqs(input: RemoveInitialRfqsSchema) {
  unstable_noStore ()
  try {
    const { ids } = removeInitialRfqsSchema.parse(input)

    await db.transaction(async (tx) => {
      await tx.delete(initialRfq).where(inArray(initialRfq.id, ids))
    })


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

interface ModifyInitialRfqInput extends UpdateInitialRfqSchema {
  id: number
}

export async function modifyInitialRfq(input: ModifyInitialRfqInput) {
  unstable_noStore ()
  try {
    const { id, ...updateData } = input
    
    // validation
    updateInitialRfqSchema.parse(updateData)

    await db.transaction(async (tx) => {
      const existingRfq = await tx
        .select()
        .from(initialRfq)
        .where(eq(initialRfq.id, id))
        .limit(1)

      if (existingRfq.length === 0) {
        throw new Error("초기 RFQ를 찾을 수 없습니다.")
      }

      await tx
        .update(initialRfq)
        .set({
          ...updateData,
          // Convert empty strings to null for optional fields
          incotermsCode: updateData.incotermsCode || null,
          gtc: updateData.gtc || null,
          gtcValidDate: updateData.gtcValidDate || null,
          classification: updateData.classification || null,
          sparepart: updateData.sparepart || null,
          validDate: updateData.validDate || null,
          updatedAt: new Date(),
        })
        .where(eq(initialRfq.id, id))
    })


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




// 이메일 발송용 데이터 타입
interface EmailData {
  rfqCode: string
  projectName: string
  projectCompany: string
  projectFlag: string
  projectSite: string
  classification: string
  incotermsCode: string
  incotermsDescription: string
  dueDate: string
  validDate: string
  sparepart: string
  vendorName: string
  picName: string
  picEmail: string
  warrantyPeriod: string
  packageName: string
  rfqRevision: number
  emailType: string
}

export async function sendBulkInitialRfqEmails(input: BulkEmailInput) {
  unstable_noStore()
  try {

    const session = await getServerSession(authOptions)
    if (!session?.user?.id) {
      throw new Error("인증이 필요합니다.")
    }

    const { initialRfqIds, language } = bulkEmailSchema.parse(input)

    // 1. 선택된 초기 RFQ들의 상세 정보 조회
    const initialRfqDetails = await db
      .select({
        // initialRfqDetailView 필드들을 명시적으로 선택
        rfqId: initialRfqDetailView.rfqId,
        rfqCode: initialRfqDetailView.rfqCode,
        rfqStatus: initialRfqDetailView.rfqStatus,
        initialRfqId: initialRfqDetailView.initialRfqId,
        initialRfqStatus: initialRfqDetailView.initialRfqStatus,
        vendorId: initialRfqDetailView.vendorId,
        vendorCode: initialRfqDetailView.vendorCode,
        vendorName: initialRfqDetailView.vendorName,
        vendorCategory: initialRfqDetailView.vendorCategory,
        vendorCountry: initialRfqDetailView.vendorCountry,
        vendorBusinessSize: initialRfqDetailView.vendorBusinessSize,
        dueDate: initialRfqDetailView.dueDate,
        validDate: initialRfqDetailView.validDate,
        incotermsCode: initialRfqDetailView.incotermsCode,
        incotermsDescription: initialRfqDetailView.incotermsDescription,
        shortList: initialRfqDetailView.shortList,
        returnYn: initialRfqDetailView.returnYn,
        cpRequestYn: initialRfqDetailView.cpRequestYn,
        prjectGtcYn: initialRfqDetailView.prjectGtcYn,
        returnRevision: initialRfqDetailView.returnRevision,
        rfqRevision: initialRfqDetailView.rfqRevision,
        gtc: initialRfqDetailView.gtc,
        gtcValidDate: initialRfqDetailView.gtcValidDate,
        classification: initialRfqDetailView.classification,
        sparepart: initialRfqDetailView.sparepart,
        createdAt: initialRfqDetailView.createdAt,
        updatedAt: initialRfqDetailView.updatedAt,
        // bRfqs에서 추가로 필요한 필드들
        picName: bRfqs.picName,
        picCode: bRfqs.picCode,
        packageName: bRfqs.packageName,
        packageNo: bRfqs.packageNo,
        projectCompany: bRfqs.projectCompany,
        projectFlag: bRfqs.projectFlag,
        projectSite: bRfqs.projectSite,
      })
      .from(initialRfqDetailView)
      .leftJoin(bRfqs, eq(initialRfqDetailView.rfqId, bRfqs.id))
      .where(inArray(initialRfqDetailView.initialRfqId, initialRfqIds))

    if (initialRfqDetails.length === 0) {
      return {
        success: false,
        message: "선택된 초기 RFQ를 찾을 수 없습니다.",
      }
    }

    // 2. 각 RFQ에 대한 첨부파일 조회
    const rfqIds = [...new Set(initialRfqDetails.map(rfq => rfq.rfqId))].filter((id): id is number => id !== null)
    const attachments = await db
      .select()
      .from(bRfqsAttachments)
      .where(inArray(bRfqsAttachments.rfqId, rfqIds))

    // 3. 벤더 이메일 정보 조회 (모든 이메일 주소 포함)
    const vendorIds = [...new Set(initialRfqDetails.map(rfq => rfq.vendorId))].filter((id): id is number => id !== null)
    const vendorsWithAllEmails = await db
      .select({
        id: vendors.id,
        vendorName: vendors.vendorName,
        email: vendors.email,
        representativeEmail: vendors.representativeEmail,
        // 연락처 이메일들을 JSON 배열로 집계
        contactEmails: sql<string[]>`
          COALESCE(
            (SELECT json_agg(contact_email)
             FROM vendor_contacts 
             WHERE vendor_id = ${vendors.id} 
             AND contact_email IS NOT NULL
             AND contact_email != ''
            ), 
            '[]'::json
          )
        `.as("contact_emails")
      })
      .from(vendors)
      .where(inArray(vendors.id, vendorIds))

    // 각 벤더의 모든 유효한 이메일 주소를 정리하는 함수
    function getAllVendorEmails(vendor: typeof vendorsWithAllEmails[0]): string[] {
      const emails: string[] = []
      
      // 벤더 기본 이메일
      if (vendor.email) {
        emails.push(vendor.email)
      }
      
      // 대표자 이메일
      if (vendor.representativeEmail && vendor.representativeEmail !== vendor.email) {
        emails.push(vendor.representativeEmail)
      }
      
      // 연락처 이메일들
      if (vendor.contactEmails && Array.isArray(vendor.contactEmails)) {
        vendor.contactEmails.forEach(contactEmail => {
          if (contactEmail && !emails.includes(contactEmail)) {
            emails.push(contactEmail)
          }
        })
      }
      
      return emails.filter(email => email && email.trim() !== '')
    }

    const results = []
    const errors = []

    // 4. 각 초기 RFQ에 대해 처리
    for (const rfqDetail of initialRfqDetails) {
      try {
        // vendorId null 체크
        if (!rfqDetail.vendorId) {
          errors.push(`벤더 ID가 없습니다: RFQ ID ${rfqDetail.initialRfqId}`)
          continue
        }

        // 해당 RFQ의 첨부파일들
        const rfqAttachments = attachments.filter(att => att.rfqId === rfqDetail.rfqId)
        
        // 벤더 정보
        const vendor = vendorsWithAllEmails.find(v => v.id === rfqDetail.vendorId)
        if (!vendor) {
          errors.push(`벤더 정보를 찾을 수 없습니다: RFQ ID ${rfqDetail.initialRfqId}`)
          continue
        }

        // 해당 벤더의 모든 이메일 주소 수집
        const vendorEmails = getAllVendorEmails(vendor)
        
        if (vendorEmails.length === 0) {
          errors.push(`벤더 이메일 주소가 없습니다: ${vendor.vendorName}`)
          continue
        }

        // 5. 기존 vendorAttachmentResponses 조회하여 리비전 상태 확인
        const currentRfqRevision = rfqDetail.rfqRevision || 0
        let emailType: "NEW" | "RESEND" | "REVISION" = "NEW"
        let revisionToUse = currentRfqRevision

        // 첫 번째 첨부파일을 기준으로 기존 응답 조회 (리비전 상태 확인용)
        if (rfqAttachments.length > 0 &&  rfqDetail.initialRfqId) {
          const existingResponses = await db
            .select()
            .from(vendorAttachmentResponses)
            .where(
              and(
                eq(vendorAttachmentResponses.vendorId, rfqDetail.vendorId),
                eq(vendorAttachmentResponses.rfqType, "INITIAL"),
                eq(vendorAttachmentResponses.rfqRecordId, rfqDetail.initialRfqId)
              )
            )

          if (existingResponses.length > 0) {
            // 기존 응답이 있음
            const existingRevision = parseInt(existingResponses[0].currentRevision?.replace("Rev.", "") || "0")
            
            if (currentRfqRevision > existingRevision) {
              // RFQ 리비전이 올라감 → 리비전 업데이트
              emailType = "REVISION"
              revisionToUse = currentRfqRevision
            } else {
              // 동일하거나 낮음 → 재전송
              emailType = "RESEND"
              revisionToUse = existingRevision
            }
          } else {
            // 기존 응답이 없음 → 신규 전송
            emailType = "NEW"
            revisionToUse = currentRfqRevision
          }
        }

        // 6. vendorAttachmentResponses 레코드 생성/업데이트
        for (const attachment of rfqAttachments) {
          const existingResponse = await db
            .select()
            .from(vendorAttachmentResponses)
            .where(
              and(
                eq(vendorAttachmentResponses.attachmentId, attachment.id),
                eq(vendorAttachmentResponses.vendorId, rfqDetail.vendorId),
                eq(vendorAttachmentResponses.rfqType, "INITIAL")
              )
            )
            .limit(1)

          if (existingResponse.length === 0) {
            // 새 응답 레코드 생성
            await db.insert(vendorAttachmentResponses).values({
              attachmentId: attachment.id,
              vendorId: rfqDetail.vendorId,
              rfqType: "INITIAL",
              rfqRecordId: rfqDetail.initialRfqId,
              responseStatus: "NOT_RESPONDED",
              currentRevision: `Rev.${revisionToUse}`,
              requestedAt: new Date(),
            })
          } else {
            // 기존 레코드 업데이트
            await db
              .update(vendorAttachmentResponses)
              .set({
                currentRevision: `Rev.${revisionToUse}`,
                requestedAt: new Date(),
                // 리비전 업데이트인 경우 응답 상태 초기화
                responseStatus: emailType === "REVISION" ? "NOT_RESPONDED" : existingResponse[0].responseStatus,
              })
              .where(eq(vendorAttachmentResponses.id, existingResponse[0].id))
          }
          
        }

        const formatDateSafely = (date: Date | string | null | undefined): string => {
          if (!date) return ""
          try {
            // Date 객체로 변환하고 포맷팅
            const dateObj = new Date(date)
            // 유효한 날짜인지 확인
            if (isNaN(dateObj.getTime())) return ""
            
            return dateObj.toLocaleDateString('en-US', { 
              year: 'numeric', 
              month: '2-digit', 
              day: '2-digit' 
            })
          } catch (error) {
            console.error("Date formatting error:", error)
            return ""
          }
        }

        // 7. 이메일 발송
        const emailData: EmailData = {
          name:vendor.vendorName,
          rfqCode: rfqDetail.rfqCode || "",
          projectName: rfqDetail.rfqCode || "", // 실제 프로젝트명이 있다면 사용
          projectCompany: rfqDetail.projectCompany || "",
          projectFlag: rfqDetail.projectFlag || "",
          projectSite: rfqDetail.projectSite || "",
          classification: rfqDetail.classification || "ABS",
          incotermsCode: rfqDetail.incotermsCode || "FOB",
          incotermsDescription: rfqDetail.incotermsDescription || "FOB Finland Port",
          dueDate: rfqDetail.dueDate ? formatDateSafely(rfqDetail.dueDate) : "",
          validDate: rfqDetail.validDate ?formatDateSafely(rfqDetail.validDate) : "",
          sparepart: rfqDetail.sparepart || "One(1) year operational spare parts",
          vendorName: vendor.vendorName,
          picName: session.user.name || rfqDetail.picName || "Procurement Manager",
          picEmail: session.user.email || "procurement@samsung.com",
          warrantyPeriod: "Refer to commercial package attached",
          packageName: rfqDetail.packageName || "",
          rfqRevision: revisionToUse, // 리비전 정보 추가
          emailType: emailType, // 이메일 타입 추가
        }

        // 이메일 제목 생성 (타입에 따라 다르게)
        let emailSubject = ""
        const revisionText = revisionToUse > 0 ? ` Rev.${revisionToUse}` : ""
        
        switch (emailType) {
          case "NEW":
            emailSubject = `[SHI RFQ] ${rfqDetail.rfqCode}${revisionText} Invitation to Bidder for ${emailData.packageName} * ${vendor.vendorName} * RFQ No. ${rfqDetail.rfqCode}`
            break
          case "RESEND":
            emailSubject = `[SHI RFQ - RESEND] ${rfqDetail.rfqCode}${revisionText} Invitation to Bidder for ${emailData.packageName} * ${vendor.vendorName} * RFQ No. ${rfqDetail.rfqCode}`
            break
          case "REVISION":
            emailSubject = `[SHI RFQ - REVISED] ${rfqDetail.rfqCode}${revisionText} Invitation to Bidder for ${emailData.packageName} * ${vendor.vendorName} * RFQ No. ${rfqDetail.rfqCode}`
            break
        }

        // nodemailer로 모든 이메일 주소에 한번에 발송
        await sendEmail({
          // from: session.user.email || undefined,
          to: vendorEmails.join(", "), // 콤마+공백으로 구분
          subject: emailSubject,
          template: "initial-rfq-invitation", // hbs 템플릿 파일명
          context: {
            ...emailData,
            language,
          }
        })

        // 8. 초기 RFQ 상태 업데이트 (리비전은 변경하지 않음 - 이미 DB에 저장된 값 사용)
        if(rfqDetail.initialRfqId && rfqDetail.rfqId){
          // Promise.all로 두 테이블 동시 업데이트
          await Promise.all([
            // initialRfq 테이블 업데이트
            db
              .update(initialRfq)
              .set({
                initialRfqStatus: "Init. RFQ Sent",
                updatedAt: new Date(),
              })
              .where(eq(initialRfq.id, rfqDetail.initialRfqId)),
            
            // bRfqs 테이블 status도 함께 업데이트
            db
              .update(bRfqs)
              .set({
                status: "Init. RFQ Sent",
                // updatedBy: session.user.id,
                updatedAt: new Date(),
              })
              .where(eq(bRfqs.id, rfqDetail.rfqId))
          ]);
        }

        results.push({
          initialRfqId: rfqDetail.initialRfqId,
          vendorName: vendor.vendorName,
          vendorEmails: vendorEmails, // 발송된 모든 이메일 주소 기록
          emailCount: vendorEmails.length,
          emailType: emailType,
          rfqRevision: revisionToUse,
          success: true,
        })

      } catch (error) {
        console.error(`Error processing RFQ ${rfqDetail.initialRfqId}:`, error)
        errors.push(`RFQ ${rfqDetail.initialRfqId} 처리 중 오류: ${getErrorMessage(error)}`)
      }
    }



    return {
      success: true,
      message: `${results.length}개의 RFQ 이메일이 발송되었습니다.`,
      results,
      errors: errors.length > 0 ? errors : undefined,
    }

  } catch (err) {
    console.error("Bulk email error:", err)
    return {
      success: false,
      message: getErrorMessage(err),
    }
  }
}

// 개별 RFQ 이메일 재발송
export async function resendInitialRfqEmail(initialRfqId: number) {
  unstable_noStore()
  try {
    const result = await sendBulkInitialRfqEmails({
      initialRfqIds: [initialRfqId],
      language: "en",
    })

    return result
  } catch (err) {
    return {
      success: false,
      message: getErrorMessage(err),
    }
  }
}

export type VendorResponseDetail = VendorAttachmentResponse & {
  attachment: {
    id: number;
    attachmentType: string;
    serialNo: string;
    description: string | null;
    currentRevision: string;
  };
  vendor: {
    id: number;
    vendorCode: string;
    vendorName: string;
    country: string | null;
    businessSize: string | null;
  };
  rfq: {
    id: number;
    rfqCode: string | null;
    description: string | null;
    status: string;
    dueDate: Date;
  };
};

export async function getVendorRfqResponses(input: GetVendorResponsesSchema, vendorId?: string, rfqId?: string) {
      try {
        // 페이지네이션 설정
        const page = input.page || 1;
        const perPage = input.perPage || 10;
        const offset = (page - 1) * perPage;

        // 기본 조건
        let whereConditions = [];

        // 벤더 ID 조건
        if (vendorId) {
          whereConditions.push(eq(vendorAttachmentResponses.vendorId, Number(vendorId)));
        }

        // RFQ 타입 조건
        // if (input.rfqType !== "ALL") {
        //   whereConditions.push(eq(vendorAttachmentResponses.rfqType, input.rfqType as RfqType));
        // }

        // 날짜 범위 조건
        if (input.from && input.to) {
          whereConditions.push(
            and(
              gte(vendorAttachmentResponses.requestedAt, new Date(input.from)),
              lte(vendorAttachmentResponses.requestedAt, new Date(input.to))
            )
          );
        }

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

        // 그룹핑된 응답 요약 데이터 조회
        const groupedResponses = await db
          .select({
            vendorId: vendorAttachmentResponses.vendorId,
            rfqRecordId: vendorAttachmentResponses.rfqRecordId,
            rfqType: vendorAttachmentResponses.rfqType,
            
            // 통계 계산 (조건부 COUNT 수정)
            totalAttachments: count(),
            respondedCount: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' THEN 1 ELSE 0 END)`,
            pendingCount: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'NOT_RESPONDED' THEN 1 ELSE 0 END)`,
            revisionRequestedCount: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'REVISION_REQUESTED' THEN 1 ELSE 0 END)`,
            waivedCount: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'WAIVED' THEN 1 ELSE 0 END)`,
            
            // 날짜 정보
            requestedAt: sql<Date>`MIN(${vendorAttachmentResponses.requestedAt})`,
            lastRespondedAt: sql<Date | null>`MAX(${vendorAttachmentResponses.respondedAt})`,
            
            // 코멘트 여부
            hasComments: sql<boolean>`BOOL_OR(${vendorAttachmentResponses.responseComment} IS NOT NULL OR ${vendorAttachmentResponses.vendorComment} IS NOT NULL)`,
          })
          .from(vendorAttachmentResponses)
          .where(baseWhere)
          .groupBy(
            vendorAttachmentResponses.vendorId,
            vendorAttachmentResponses.rfqRecordId,
            vendorAttachmentResponses.rfqType
          )
          .orderBy(desc(sql`MIN(${vendorAttachmentResponses.requestedAt})`))
          .offset(offset)
          .limit(perPage);

        // 벤더 정보와 RFQ 정보를 별도로 조회
        const vendorIds = [...new Set(groupedResponses.map(r => r.vendorId))];
        const rfqRecordIds = [...new Set(groupedResponses.map(r => r.rfqRecordId))];

        // 벤더 정보 조회
        const vendorsData = await db.query.vendors.findMany({
          where: or(...vendorIds.map(id => eq(vendors.id, id))),
          columns: {
            id: true,
            vendorCode: true,
            vendorName: true,
            country: true,
            businessSize: true,
          }
        });

        // RFQ 정보 조회 (초기 RFQ와 최종 RFQ 모두)
        const [initialRfqs] = await Promise.all([
          db.query.initialRfq.findMany({
            where: or(...rfqRecordIds.map(id => eq(initialRfq.id, id))),
            with: {
              rfq: {
                columns: {
                  id: true,
                  rfqCode: true,
                  description: true,
                  status: true,
                  dueDate: true,
                }
              }
            }
          })

        ]);

        // 데이터 조합 및 변환
        const transformedResponses: VendorRfqResponseSummary[] = groupedResponses.map(response => {
          const vendor = vendorsData.find(v => v.id === response.vendorId);
          
          let rfqInfo = null;
          if (response.rfqType === "INITIAL") {
            const initialRfq = initialRfqs.find(r => r.id === response.rfqRecordId);
            rfqInfo = initialRfq?.rfq || null;
          }

          // 응답률 계산
          const responseRate = Number(response.totalAttachments) > 0 
            ? Math.round((Number(response.respondedCount) / Number(response.totalAttachments)) * 100)
            : 0;

          // 완료율 계산 (응답완료 + 포기)
          const completionRate = Number(response.totalAttachments) > 0
            ? Math.round(((Number(response.respondedCount) + Number(response.waivedCount)) / Number(response.totalAttachments)) * 100)
            : 0;

          // 전체 상태 결정
          let overallStatus: ResponseStatus = "NOT_RESPONDED";
          if (Number(response.revisionRequestedCount) > 0) {
            overallStatus = "REVISION_REQUESTED";
          } else if (completionRate === 100) {
            overallStatus = Number(response.waivedCount) === Number(response.totalAttachments) ? "WAIVED" : "RESPONDED";
          } else if (Number(response.respondedCount) > 0) {
            overallStatus = "RESPONDED"; // 부분 응답
          }

          return {
            id: `${response.vendorId}-${response.rfqRecordId}-${response.rfqType}`,
            vendorId: response.vendorId,
            rfqRecordId: response.rfqRecordId,
            rfqType: response.rfqType,
            rfq: rfqInfo,
            vendor: vendor || null,
            totalAttachments: Number(response.totalAttachments),
            respondedCount: Number(response.respondedCount),
            pendingCount: Number(response.pendingCount),
            revisionRequestedCount: Number(response.revisionRequestedCount),
            waivedCount: Number(response.waivedCount),
            responseRate,
            completionRate,
            overallStatus,
            requestedAt: response.requestedAt,
            lastRespondedAt: response.lastRespondedAt,
            hasComments: response.hasComments,
          };
        });

        // 전체 개수 조회 (그룹핑 기준) - PostgreSQL 호환 방식
        const totalCountResult = await db
          .select({
            totalCount: sql<number>`COUNT(DISTINCT (${vendorAttachmentResponses.vendorId}, ${vendorAttachmentResponses.rfqRecordId}, ${vendorAttachmentResponses.rfqType}))`
          })
          .from(vendorAttachmentResponses)
          .where(baseWhere);

        const totalCount = Number(totalCountResult[0].totalCount);
        const pageCount = Math.ceil(totalCount / perPage);

        return {
          data: transformedResponses,
          pageCount,
          totalCount
        };

      } catch (err) {
        console.error("getVendorRfqResponses 에러:", err);
        return { data: [], pageCount: 0, totalCount: 0 };
      }
}
/**
 * 특정 RFQ의 첨부파일별 응답 상세 조회 (상세 페이지용)
 */
export async function getRfqAttachmentResponses(vendorId: string, rfqRecordId: string) {
      try {
        // 해당 RFQ의 모든 첨부파일 응답 조회
        const responses = await db.query.vendorAttachmentResponses.findMany({
          where: and(
            eq(vendorAttachmentResponses.vendorId, Number(vendorId)),
            eq(vendorAttachmentResponses.rfqRecordId, Number(rfqRecordId)),
                 ),
          with: {
            attachment: {
              with: {
                rfq: {
                  columns: {
                    id: true,
                    rfqCode: true,
                    description: true,
                    status: true,
                    dueDate: true,
                    // 추가 정보
                    picCode: true,
                    picName: true,
                    EngPicName: true,
                    packageNo: true,
                    packageName: true,
                    projectId: true,
                    projectCompany: true,
                    projectFlag: true,
                    projectSite: true,
                    remark: true,
                  },
                  with: {
                    project: {
                      columns: {
                        id: true,
                        code: true,
                        name: true,
                        type: true,
                      }
                    }
                  }
                }
              }
            },
            vendor: {
              columns: {
                id: true,
                vendorCode: true,
                vendorName: true,
                country: true,
                businessSize: true,
              }
            },
            responseAttachments: true,
          },
          orderBy: [asc(vendorAttachmentResponses.attachmentId)]
        });

        return {
          data: responses,
          rfqInfo: responses[0]?.attachment?.rfq || null,
          vendorInfo: responses[0]?.vendor || null,
        };

      } catch (err) {
        console.error("getRfqAttachmentResponses 에러:", err);
        return { data: [], rfqInfo: null, vendorInfo: null };
      }
}

export async function getVendorResponseStatusCounts(vendorId?: string, rfqId?: string, rfqType?: RfqType) {
      try {
        const initial: Record<ResponseStatus, number> = {
          NOT_RESPONDED: 0,
          RESPONDED: 0,
          REVISION_REQUESTED: 0,
          WAIVED: 0,
        };

        // 조건 설정
        let whereConditions = [];

        // 벤더 ID 조건
        if (vendorId) {
          whereConditions.push(eq(vendorAttachmentResponses.vendorId, Number(vendorId)));
        }

        // RFQ ID 조건
        if (rfqId) {
          const attachmentIds = await db
            .select({ id: bRfqsAttachments.id })
            .from(bRfqsAttachments)
            .where(eq(bRfqsAttachments.rfqId, Number(rfqId)));
          
          if (attachmentIds.length > 0) {
            whereConditions.push(
              or(...attachmentIds.map(att => eq(vendorAttachmentResponses.attachmentId, att.id)))
            );
          }
        }

        // RFQ 타입 조건
        if (rfqType) {
          whereConditions.push(eq(vendorAttachmentResponses.rfqType, rfqType));
        }

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

        // 상태별 그룹핑 쿼리
        const rows = await db
          .select({
            status: vendorAttachmentResponses.responseStatus,
            count: count(),
          })
          .from(vendorAttachmentResponses)
          .where(whereCondition)
          .groupBy(vendorAttachmentResponses.responseStatus);

        // 결과 처리
        const result = rows.reduce<Record<ResponseStatus, number>>((acc, { status, count }) => {
          if (status) {
            acc[status as ResponseStatus] = Number(count);
          }
          return acc;
        }, initial);

        return result;
      } catch (err) {
        console.error("getVendorResponseStatusCounts 에러:", err);
        return {} as Record<ResponseStatus, number>;
      }
}

/**
 * RFQ별 벤더 응답 요약 조회
 */
export async function getRfqResponseSummary(rfqId: string, rfqType?: RfqType) {

      try {
        // RFQ의 첨부파일 목록 조회 (relations 사용)
        const attachments = await db.query.bRfqsAttachments.findMany({
          where: eq(bRfqsAttachments.rfqId, Number(rfqId)),
          columns: {
            id: true,
            attachmentType: true,
            serialNo: true,
            description: true,
          }
        });

        if (attachments.length === 0) {
          return {
            totalAttachments: 0,
            totalVendors: 0,
            responseRate: 0,
            completionRate: 0,
            statusCounts: {} as Record<ResponseStatus, number>
          };
        }

        // 조건 설정
        let whereConditions = [
          or(...attachments.map(att => eq(vendorAttachmentResponses.attachmentId, att.id)))
        ];

        if (rfqType) {
          whereConditions.push(eq(vendorAttachmentResponses.rfqType, rfqType));
        }

        const whereCondition = and(...whereConditions);

        // 벤더 수 및 응답 통계 조회
        const [vendorStats, statusCounts] = await Promise.all([
          // 전체 벤더 수 및 응답 벤더 수 (조건부 COUNT 수정)
          db
            .select({
              totalVendors: count(),
              respondedVendors: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' THEN 1 ELSE 0 END)`,
              completedVendors: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' OR ${vendorAttachmentResponses.responseStatus} = 'WAIVED' THEN 1 ELSE 0 END)`,
            })
            .from(vendorAttachmentResponses)
            .where(whereCondition),

          // 상태별 개수
          db
            .select({
              status: vendorAttachmentResponses.responseStatus,
              count: count(),
            })
            .from(vendorAttachmentResponses)
            .where(whereCondition)
            .groupBy(vendorAttachmentResponses.responseStatus)
        ]);

        const stats = vendorStats[0];
        const statusCountsMap = statusCounts.reduce<Record<ResponseStatus, number>>((acc, { status, count }) => {
          if (status) {
            acc[status as ResponseStatus] = Number(count);
          }
          return acc;
        }, {
          NOT_RESPONDED: 0,
          RESPONDED: 0,
          REVISION_REQUESTED: 0,
          WAIVED: 0,
        });

        const responseRate = stats.totalVendors > 0 
          ? Math.round((Number(stats.respondedVendors) / Number(stats.totalVendors)) * 100) 
          : 0;
        
        const completionRate = stats.totalVendors > 0 
          ? Math.round((Number(stats.completedVendors) / Number(stats.totalVendors)) * 100) 
          : 0;

        return {
          totalAttachments: attachments.length,
          totalVendors: Number(stats.totalVendors),
          responseRate,
          completionRate,
          statusCounts: statusCountsMap
        };

      } catch (err) {
        console.error("getRfqResponseSummary 에러:", err);
        return {
          totalAttachments: 0,
          totalVendors: 0,
          responseRate: 0,
          completionRate: 0,
          statusCounts: {} as Record<ResponseStatus, number>
        };
      }
}

/**
 * 벤더별 응답 진행률 조회
 */
export async function getVendorResponseProgress(vendorId: string) {

      try {
        let whereConditions = [eq(vendorAttachmentResponses.vendorId, Number(vendorId))];

         const whereCondition = and(...whereConditions);

         const progress = await db
         .select({
           totalRequests: count(),
           responded: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'RESPONDED' THEN 1 ELSE 0 END)`,
           pending: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'NOT_RESPONDED' THEN 1 ELSE 0 END)`,
           revisionRequested: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'REVISION_REQUESTED' THEN 1 ELSE 0 END)`,
           waived: sql<number>`SUM(CASE WHEN ${vendorAttachmentResponses.responseStatus} = 'WAIVED' THEN 1 ELSE 0 END)`,
         })
         .from(vendorAttachmentResponses)
         .where(whereCondition);
          console.log(progress,"progress")

        const stats = progress[0];
        const responseRate = Number(stats.totalRequests) > 0 
          ? Math.round((Number(stats.responded) / Number(stats.totalRequests)) * 100) 
          : 0;

        const completionRate = Number(stats.totalRequests) > 0 
          ? Math.round(((Number(stats.responded) + Number(stats.waived)) / Number(stats.totalRequests)) * 100) 
          : 0;

        return {
          totalRequests: Number(stats.totalRequests),
          responded: Number(stats.responded),
          pending: Number(stats.pending),
          revisionRequested: Number(stats.revisionRequested),
          waived: Number(stats.waived),
          responseRate,
          completionRate,
        };

      } catch (err) {
        console.error("getVendorResponseProgress 에러:", err);
        return {
          totalRequests: 0,
          responded: 0,
          pending: 0,
          revisionRequested: 0,
          waived: 0,
          responseRate: 0,
          completionRate: 0,
        };
      }
}


export async function getRfqAttachmentResponsesWithRevisions(vendorId: string, rfqRecordId: string) {
  try {
    // 1. 벤더 응답 상세 정보 조회 (뷰 사용)
    const responses = await db
      .select()
      .from(vendorResponseDetailView)
      .where(
        and(
          eq(vendorResponseDetailView.vendorId, Number(vendorId)),
          eq(vendorResponseDetailView.rfqRecordId, Number(rfqRecordId))
        )
      )
      .orderBy(asc(vendorResponseDetailView.attachmentId));

    // 2. RFQ 진행 현황 요약 조회
    const progressSummaryResult = await db
      .select()
      .from(rfqProgressSummaryView)
      .where(eq(rfqProgressSummaryView.rfqId, responses[0]?.rfqId || 0))
      .limit(1);
    
    const progressSummary = progressSummaryResult[0] || null;

    // 3. 각 응답의 첨부파일 리비전 히스토리 조회
    const attachmentHistories = await Promise.all(
      responses.map(async (response) => {
        const history = await db
          .select()
          .from(attachmentRevisionHistoryView)
          .where(eq(attachmentRevisionHistoryView.attachmentId, response.attachmentId))
          .orderBy(desc(attachmentRevisionHistoryView.clientRevisionCreatedAt));
        
        return {
          attachmentId: response.attachmentId,
          revisions: history
        };
      })
    );

    // 4. 벤더 응답 파일들 조회 (향상된 정보 포함)
    const responseFiles = await Promise.all(
      responses.map(async (response) => {
        const files = await db
          .select()
          .from(vendorResponseAttachmentsEnhanced)
          .where(eq(vendorResponseAttachmentsEnhanced.vendorResponseId, response.responseId))
          .orderBy(desc(vendorResponseAttachmentsEnhanced.uploadedAt));
        
        return {
          responseId: response.responseId,
          files: files
        };
      })
    );

    // 5. 데이터 변환 및 통합
    const enhancedResponses = responses.map(response => {
      const attachmentHistory = attachmentHistories.find(h => h.attachmentId === response.attachmentId);
      const responseFileData = responseFiles.find(f => f.responseId === response.responseId);
      
      return {
        ...response,
        // 첨부파일 정보에 리비전 히스토리 추가
        attachment: {
          id: response.attachmentId,
          attachmentType: response.attachmentType,
          serialNo: response.serialNo,
          description: response.attachmentDescription,
          currentRevision: response.currentRevision,
          // 모든 리비전 정보
          revisions: attachmentHistory?.revisions?.map(rev => ({
            id: rev.clientRevisionId,
            revisionNo: rev.clientRevisionNo,
            fileName: rev.clientFileName,
            originalFileName: rev.clientFileName,
            filePath: rev.clientFilePath, // 파일 경로 추가
            fileSize: rev.clientFileSize,
            revisionComment: rev.clientRevisionComment,
            createdAt: rev.clientRevisionCreatedAt?.toISOString() || new Date().toISOString(),
            isLatest: rev.isLatestClientRevision
          })) || []
        },
        // 벤더 응답 파일들
        responseAttachments: responseFileData?.files?.map(file => ({
          id: file.responseAttachmentId,
          fileName: file.fileName,
          originalFileName: file.originalFileName,
          filePath: file.filePath,
          fileSize: file.fileSize,
          description: file.description,
          uploadedAt: file.uploadedAt?.toISOString() || new Date().toISOString(),
          isLatestResponseFile: file.isLatestResponseFile,
          fileSequence: file.fileSequence
        })) || [],
        // 리비전 분석 정보
        isVersionMatched: response.isVersionMatched,
        versionLag: response.versionLag,
        needsUpdate: response.needsUpdate,
        hasMultipleRevisions: response.hasMultipleRevisions,
        
        // 새로 추가된 필드들
        revisionRequestComment: response.revisionRequestComment,
        revisionRequestedAt: response.revisionRequestedAt?.toISOString() || null,
      };
    });

    // RFQ 기본 정보 (첫 번째 응답에서 추출)
    const rfqInfo = responses[0] ? {
      id: responses[0].rfqId,
      rfqCode: responses[0].rfqCode,
      // 추가 정보는 기존 방식대로 별도 조회 필요
      description: "",
      dueDate: progressSummary?.dueDate || new Date(),
      status: progressSummary?.rfqStatus || "DRAFT",
      // ... 기타 필요한 정보들
    } : null;

    // 벤더 정보
    const vendorInfo = responses[0] ? {
      id: responses[0].vendorId,
      vendorCode: responses[0].vendorCode,
      vendorName: responses[0].vendorName,
      country: responses[0].vendorCountry,
    } : null;

    // 통계 정보 계산
    const calculateStats = (responses: typeof enhancedResponses) => {
      const total = responses.length;
      const responded = responses.filter(r => r.responseStatus === "RESPONDED").length;
      const pending = responses.filter(r => r.responseStatus === "NOT_RESPONDED").length;
      const revisionRequested = responses.filter(r => r.responseStatus === "REVISION_REQUESTED").length;
      const waived = responses.filter(r => r.responseStatus === "WAIVED").length;
      const versionMismatch = responses.filter(r => r.effectiveStatus === "VERSION_MISMATCH").length;
      const upToDate = responses.filter(r => r.effectiveStatus === "UP_TO_DATE").length;

      return {
        total,
        responded,
        pending,
        revisionRequested,
        waived,
        versionMismatch,
        upToDate,
        responseRate: total > 0 ? Math.round((responded / total) * 100) : 0,
        completionRate: total > 0 ? Math.round(((responded + waived) / total) * 100) : 0,
        versionMatchRate: responded > 0 ? Math.round((upToDate / responded) * 100) : 100
      };
    };

    const statistics = calculateStats(enhancedResponses);

    return {
      data: enhancedResponses,
      rfqInfo,
      vendorInfo,
      statistics,
      progressSummary: progressSummary ? {
        totalAttachments: progressSummary.totalAttachments,
        attachmentsWithMultipleRevisions: progressSummary.attachmentsWithMultipleRevisions,
        totalClientRevisions: progressSummary.totalClientRevisions,
        totalResponseFiles: progressSummary.totalResponseFiles,
        daysToDeadline: progressSummary.daysToDeadline
      } : null
    };

  } catch (err) {
    console.error("getRfqAttachmentResponsesWithRevisions 에러:", err);
    return { 
      data: [], 
      rfqInfo: null, 
      vendorInfo: null, 
      statistics: {
        total: 0,
        responded: 0,
        pending: 0,
        revisionRequested: 0,
        waived: 0,
        versionMismatch: 0,
        upToDate: 0,
        responseRate: 0,
        completionRate: 0,
        versionMatchRate: 100
      },
      progressSummary: null
    };
  }
}

// 첨부파일 리비전 히스토리 조회
export async function getAttachmentRevisionHistory(attachmentId: number) {

      try {
        const history = await db
          .select()
          .from(attachmentRevisionHistoryView)
          .where(eq(attachmentRevisionHistoryView.attachmentId, attachmentId))
          .orderBy(desc(attachmentRevisionHistoryView.clientRevisionCreatedAt));

        return history;
      } catch (err) {
        console.error("getAttachmentRevisionHistory 에러:", err);
        return [];
      }
  }

// RFQ 전체 진행 현황 조회
export async function getRfqProgressSummary(rfqId: number) {
      try {
        const summaryResult = await db
          .select()
          .from(rfqProgressSummaryView)
          .where(eq(rfqProgressSummaryView.rfqId, rfqId))
          .limit(1);

        return summaryResult[0] || null;
      } catch (err) {
        console.error("getRfqProgressSummary 에러:", err);
        return null;
      }
}

// 벤더 응답 파일 상세 조회 (향상된 정보 포함)
export async function getVendorResponseFiles(vendorResponseId: number) {
      try {
        const files = await db
          .select()
          .from(vendorResponseAttachmentsEnhanced)
          .where(eq(vendorResponseAttachmentsEnhanced.vendorResponseId, vendorResponseId))
          .orderBy(desc(vendorResponseAttachmentsEnhanced.uploadedAt));

        return files;
      } catch (err) {
        console.error("getVendorResponseFiles 에러:", err);
        return [];
      }
  }


// 타입 정의 확장
export type EnhancedVendorResponse = {
  // 기본 응답 정보
  responseId: number;
  rfqId: number;
  rfqCode: string;
  rfqType: "INITIAL" | "FINAL";
  rfqRecordId: number;
  
  // 첨부파일 정보
  attachmentId: number;
  attachmentType: string;
  serialNo: string;
  attachmentDescription?: string;
  
  // 벤더 정보
  vendorId: number;
  vendorCode: string;
  vendorName: string;
  vendorCountry: string;
  
  // 응답 상태
  responseStatus: "NOT_RESPONDED" | "RESPONDED" | "REVISION_REQUESTED" | "WAIVED";
  currentRevision: string;
  respondedRevision?: string;
  effectiveStatus: string;
  
  // 코멘트 관련 필드들 (새로 추가된 필드 포함)
  responseComment?: string;      // 벤더가 응답할 때 작성하는 코멘트
  vendorComment?: string;        // 벤더 내부 메모
  revisionRequestComment?: string; // 발주처가 수정 요청할 때 작성하는 사유 (새로 추가)
  
  // 날짜 관련 필드들 (새로 추가된 필드 포함)
  requestedAt: string;
  respondedAt?: string;
  revisionRequestedAt?: string;  // 수정 요청 날짜 (새로 추가)
  
  // 발주처 최신 리비전 정보
  latestClientRevisionNo?: string;
  latestClientFileName?: string;
  latestClientFileSize?: number;
  latestClientRevisionComment?: string;
  
  // 리비전 분석
  isVersionMatched: boolean;
  versionLag?: number;
  needsUpdate: boolean;
  hasMultipleRevisions: boolean;
  
  // 응답 파일 통계
  totalResponseFiles: number;
  latestResponseFileName?: string;
  latestResponseFileSize?: number;
  latestResponseUploadedAt?: string;
  
  // 첨부파일 정보 (리비전 히스토리 포함)
  attachment: {
    id: number;
    attachmentType: string;
    serialNo: string;
    description?: string;
    currentRevision: string;
    revisions: Array<{
      id: number;
      revisionNo: string;
      fileName: string;
      originalFileName: string;
      filePath?: string;
      fileSize?: number;
      revisionComment?: string;
      createdAt: string;
      isLatest: boolean;
    }>;
  };
  
  // 벤더 응답 파일들
  responseAttachments: Array<{
    id: number;
    fileName: string;
    originalFileName: string;
    filePath: string;
    fileSize?: number;
    description?: string;
    uploadedAt: string;
    isLatestResponseFile: boolean;
    fileSequence: number;
  }>;
};


export async function requestRevision(
  responseId: number,
  revisionReason: string
): Promise<RequestRevisionResult> {
  try {
    // 입력값 검증
    const validatedData = requestRevisionSchema.parse({
      responseId,
      revisionReason,
    });

    // 현재 응답 정보 조회
    const existingResponse = await db
      .select()
      .from(vendorAttachmentResponses)
      .where(eq(vendorAttachmentResponses.id, validatedData.responseId))
      .limit(1);

    if (existingResponse.length === 0) {
      return {
        success: false,
        message: "해당 응답을 찾을 수 없습니다",
        error: "NOT_FOUND",
      };
    }

    const response = existingResponse[0];

    // 응답 상태 확인 (이미 응답되었거나 포기된 상태에서만 수정 요청 가능)
    if (response.responseStatus !== "RESPONDED") {
      return {
        success: false,
        message: "응답된 상태의 항목에서만 수정을 요청할 수 있습니다",
        error: "INVALID_STATUS",
      };
    }

    // 응답 상태를 REVISION_REQUESTED로 업데이트
    const updateResult = await db
      .update(vendorAttachmentResponses)
      .set({
        responseStatus: "REVISION_REQUESTED",
        revisionRequestComment: validatedData.revisionReason, // 새로운 필드에 저장
        revisionRequestedAt: new Date(), // 수정 요청 시간 저장
        updatedAt: new Date(),
      })
      .where(eq(vendorAttachmentResponses.id, validatedData.responseId))
      .returning();

    if (updateResult.length === 0) {
      return {
        success: false,
        message: "수정 요청 업데이트에 실패했습니다",
        error: "UPDATE_FAILED",
      };
    }

    return {
      success: true,
      message: "수정 요청이 성공적으로 전송되었습니다",
    };

  } catch (error) {
    console.error("Request revision server action error:", error);
       return {
      success: false,
      message: "내부 서버 오류가 발생했습니다",
      error: "INTERNAL_ERROR",
    };
  }
}