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

'use server'

import { unstable_noStore, revalidateTag, revalidatePath } from "next/cache";
import db from "@/db/db";
import { 
  techSalesRfqs, 
  techSalesVendorQuotations,
  techSalesVendorQuotationRevisions,
  techSalesAttachments,
  techSalesVendorQuotationAttachments,
  techSalesVendorQuotationContacts,
  techSalesContactPossibleItems,
  users,
  techSalesRfqComments,
  techSalesRfqItems,
  biddingProjects
} from "@/db/schema";
import { and, desc, eq, ilike, or, sql, inArray, count, asc, lt, ne } from "drizzle-orm";
import { unstable_cache } from "@/lib/unstable-cache";
import { filterColumns } from "@/lib/filter-columns";
import { getErrorMessage } from "@/lib/handle-error";
import type { Filter } from "@/types/table";
import { 
  selectTechSalesRfqsWithJoin,
  countTechSalesRfqsWithJoin,
  selectTechSalesVendorQuotationsWithJoin,
  countTechSalesVendorQuotationsWithJoin,
  selectTechSalesDashboardWithJoin,
  selectSingleTechSalesVendorQuotationWithJoin
} from "./repository";
import { GetTechSalesRfqsSchema } from "./validations";
import { getServerSession } from "next-auth/next";
import { authOptions } from "@/app/api/auth/[...nextauth]/route";
import { sendEmail } from "../mail/sendEmail";
import { formatDate } from "../utils";
import { itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items";
import { techVendors, techVendorPossibleItems, techVendorContacts } from "@/db/schema/techVendors";
import { deleteFile, saveDRMFile, saveFile } from "@/lib/file-stroage";
import { decryptWithServerAction } from "@/components/drm/drmUtils";

// 정렬 타입 정의
// 의도적으로 any 사용 - drizzle ORM의 orderBy 타입이 복잡함
// eslint-disable-next-line @typescript-eslint/no-explicit-any
type OrderByType = any;

export type Project = {
  id: number;
  projectCode: string;
  projectName: string;
  pjtType: "SHIP" | "TOP" | "HULL";
}

/**
 * 연도별 순차 RFQ 코드 생성 함수 (다중 생성 지원)
 * 형식: RFQ-YYYY-001, RFQ-YYYY-002, ...
 */
// eslint-disable-next-line @typescript-eslint/no-explicit-any
async function generateRfqCodes(tx: any, count: number, year?: number): Promise<string[]> {
  const currentYear = year || new Date().getFullYear();
  const yearPrefix = `RFQ-${currentYear}-`;
  
  // 해당 연도의 가장 최근 RFQ 코드 조회
  const latestRfq = await tx
    .select({ rfqCode: techSalesRfqs.rfqCode })
    .from(techSalesRfqs)
    .where(ilike(techSalesRfqs.rfqCode, `${yearPrefix}%`))
    .orderBy(desc(techSalesRfqs.rfqCode))
    .limit(1);

  let nextNumber = 1;
  
  if (latestRfq.length > 0) {
    // 기존 코드에서 번호 추출 (RFQ-2024-001 -> 001)
    const lastCode = latestRfq[0].rfqCode;
    const numberPart = lastCode.split('-').pop();
    if (numberPart) {
      const lastNumber = parseInt(numberPart, 10);
      if (!isNaN(lastNumber)) {
        nextNumber = lastNumber + 1;
      }
    }
  }
  
  // 요청된 개수만큼 순차적으로 코드 생성
  const codes: string[] = [];
  for (let i = 0; i < count; i++) {
    const paddedNumber = (nextNumber + i).toString().padStart(3, '0');
    codes.push(`${yearPrefix}${paddedNumber}`);
  }
  
  return codes;
}


/**
 * 직접 조인을 사용하여 RFQ 데이터 조회하는 함수
 * 페이지네이션, 필터링, 정렬 등 지원
 */
export async function getTechSalesRfqsWithJoin(input: GetTechSalesRfqsSchema & { rfqType?: "SHIP" | "TOP" | "HULL" }) {
  return unstable_cache(
    async () => {
      try {
        // 마감일이 지났고 아직 Closed가 아닌 RFQ를 일괄 Closed로 변경
        await db.update(techSalesRfqs)
          .set({ status: "Closed", updatedAt: new Date() })
          .where(
            and(
              lt(techSalesRfqs.dueDate, new Date()),
              ne(techSalesRfqs.status, "Closed")
            )
          );
        const offset = (input.page - 1) * input.perPage;

        // 기본 필터 처리 - RFQFilterBox에서 오는 필터
        const basicFilters = input.basicFilters || [];
        const basicJoinOperator = input.basicJoinOperator || "and";

        // 고급 필터 처리 - workTypes을 먼저 제외
        const advancedFilters = (input.filters || []).filter(f => f.id !== "workTypes");
        const advancedJoinOperator = input.joinOperator || "and";

        // workTypes 필터는 별도로 추출
        const workTypesFilter = (input.filters || []).find(f => f.id === "workTypes");

        // 기본 필터 조건 생성
        let basicWhere;
        if (basicFilters.length > 0) {
          basicWhere = filterColumns({
            table: techSalesRfqs,
            filters: basicFilters,
            joinOperator: basicJoinOperator,
          });
        }

        // 고급 필터 조건 생성 (workTypes 제외)
        let advancedWhere;
        if (advancedFilters.length > 0) {
          advancedWhere = filterColumns({
            table: techSalesRfqs,
            filters: advancedFilters,
            joinOperator: advancedJoinOperator,
          });
        }

        // 전역 검색 조건
        let globalWhere;
        if (input.search) {
          const s = `%${input.search}%`;
          globalWhere = or(
            ilike(techSalesRfqs.rfqCode, s),
            ilike(techSalesRfqs.materialCode, s),
            ilike(techSalesRfqs.description, s),
            ilike(techSalesRfqs.remark, s)
          );
        }

        // workTypes 필터 처리 (고급 필터에서 제외된 workTypes만 별도 처리)
        let workTypesWhere;
        if (workTypesFilter && Array.isArray(workTypesFilter.value) && workTypesFilter.value.length > 0) {
          // RFQ 아이템 테이블들과 조인하여 workType이 포함된 RFQ만 추출
          // (조선, 해양TOP, 해양HULL 모두 포함)
          const rfqIdsWithWorkTypes = db
            .selectDistinct({ rfqId: techSalesRfqItems.rfqId })
            .from(techSalesRfqItems)
            .leftJoin(itemShipbuilding, eq(techSalesRfqItems.itemShipbuildingId, itemShipbuilding.id))
            .leftJoin(itemOffshoreTop, eq(techSalesRfqItems.itemOffshoreTopId, itemOffshoreTop.id))
            .leftJoin(itemOffshoreHull, eq(techSalesRfqItems.itemOffshoreHullId, itemOffshoreHull.id))
            .where(
              or(
                inArray(itemShipbuilding.workType, workTypesFilter.value),
                inArray(itemOffshoreTop.workType, workTypesFilter.value),
                inArray(itemOffshoreHull.workType, workTypesFilter.value)
              )
            );
          workTypesWhere = inArray(techSalesRfqs.id, rfqIdsWithWorkTypes);
        }

        // 모든 조건 결합
        const whereConditions = [];
        if (basicWhere) whereConditions.push(basicWhere);
        if (advancedWhere) whereConditions.push(advancedWhere);
        if (globalWhere) whereConditions.push(globalWhere);
        if (workTypesWhere) whereConditions.push(workTypesWhere);

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

        // 정렬 기준 설정
        let orderBy: OrderByType[] = [desc(techSalesRfqs.createdAt)]; // 기본 정렬
        
        if (input.sort?.length) {
          // 안전하게 접근하여 정렬 기준 설정
          orderBy = input.sort.map(item => {
            // TypeScript 에러 방지를 위한 타입 단언
            const sortField = item.id as string;
            
            switch (sortField) {
              case 'id':
                return item.desc ? desc(techSalesRfqs.id) : techSalesRfqs.id;
              case 'rfqCode':
                return item.desc ? desc(techSalesRfqs.rfqCode) : techSalesRfqs.rfqCode;
              case 'materialCode':
                return item.desc ? desc(techSalesRfqs.materialCode) : techSalesRfqs.materialCode;
              case 'description':
                return item.desc ? desc(techSalesRfqs.description) : techSalesRfqs.description;
              case 'status':
                return item.desc ? desc(techSalesRfqs.status) : techSalesRfqs.status;
              case 'dueDate':
                return item.desc ? desc(techSalesRfqs.dueDate) : techSalesRfqs.dueDate;
              case 'rfqSendDate':
                return item.desc ? desc(techSalesRfqs.rfqSendDate) : techSalesRfqs.rfqSendDate;
              case 'remark':
                return item.desc ? desc(techSalesRfqs.remark) : techSalesRfqs.remark;
              case 'createdAt':
                return item.desc ? desc(techSalesRfqs.createdAt) : techSalesRfqs.createdAt;
              case 'updatedAt':
                return item.desc ? desc(techSalesRfqs.updatedAt) : techSalesRfqs.updatedAt;
              default:
                return item.desc ? desc(techSalesRfqs.createdAt) : techSalesRfqs.createdAt;
            }
          });
        }

        // Repository 함수 호출 - rfqType 매개변수 추가
        return await db.transaction(async (tx) => {
          const [data, total] = await Promise.all([
            selectTechSalesRfqsWithJoin(tx, {
              where: finalWhere,
              orderBy,
              offset,
              limit: input.perPage,
              rfqType: input.rfqType,
            }),
            countTechSalesRfqsWithJoin(tx, finalWhere, input.rfqType),
          ]);

          const pageCount = Math.ceil(Number(total) / input.perPage);
          return { data, pageCount, total: Number(total) };
        });
      } catch (err) {
        console.error("Error fetching RFQs with join:", err);
        return { data: [], pageCount: 0, total: 0 };
      }
    },
    [JSON.stringify(input)],
    {
      revalidate: 60,
      tags: ["techSalesRfqs"],
    }
  )();
}

/**
 * 직접 조인을 사용하여 벤더 견적서 조회하는 함수
 */
export async function getTechSalesVendorQuotationsWithJoin(input: {
  rfqId?: number;
  vendorId?: number;
  search?: string;
  filters?: Filter<typeof techSalesVendorQuotations>[];
  sort?: { id: string; desc: boolean }[];
  page: number;
  perPage: number;
  rfqType?: "SHIP" | "TOP" | "HULL"; // rfqType 매개변수 추가
}) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage;

        // 기본 필터 조건들
        const whereConditions = [];

        // RFQ ID 필터
        if (input.rfqId) {
          whereConditions.push(eq(techSalesVendorQuotations.rfqId, input.rfqId));
        }

        // 벤더 ID 필터
        if (input.vendorId) {
          whereConditions.push(eq(techSalesVendorQuotations.vendorId, input.vendorId));
        }

        // 검색 조건
        if (input.search) {
          const s = `%${input.search}%`;
          const searchCondition = or(
              ilike(techSalesVendorQuotations.currency, s),
              ilike(techSalesVendorQuotations.status, s)
          );
          if (searchCondition) {
            whereConditions.push(searchCondition);
          }
        }

        // 고급 필터 처리
        if (input.filters && input.filters.length > 0) {
          const filterWhere = filterColumns({
            table: techSalesVendorQuotations,
            filters: input.filters as Filter<typeof techSalesVendorQuotations>[],
            joinOperator: "and",
          });
          if (filterWhere) {
            whereConditions.push(filterWhere);
          }
        }

        // 최종 WHERE 조건
        const finalWhere = whereConditions.length > 0 
          ? and(...whereConditions) 
          : undefined;

        // 정렬 기준 설정
        let orderBy: OrderByType[] = [desc(techSalesVendorQuotations.createdAt)];
        
        if (input.sort?.length) {
          orderBy = input.sort.map(item => {
            switch (item.id) {
              case 'id':
                return item.desc ? desc(techSalesVendorQuotations.id) : techSalesVendorQuotations.id;
              case 'status':
                return item.desc ? desc(techSalesVendorQuotations.status) : techSalesVendorQuotations.status;
              case 'currency':
                return item.desc ? desc(techSalesVendorQuotations.currency) : techSalesVendorQuotations.currency;
              case 'totalPrice':
                return item.desc ? desc(techSalesVendorQuotations.totalPrice) : techSalesVendorQuotations.totalPrice;
              case 'createdAt':
                return item.desc ? desc(techSalesVendorQuotations.createdAt) : techSalesVendorQuotations.createdAt;
              case 'updatedAt':
                return item.desc ? desc(techSalesVendorQuotations.updatedAt) : techSalesVendorQuotations.updatedAt;
              default:
                return item.desc ? desc(techSalesVendorQuotations.createdAt) : techSalesVendorQuotations.createdAt;
            }
          });
        }

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

          // 각 견적서의 첨부파일 정보 조회
          const dataWithAttachments = await Promise.all(
            data.map(async (quotation) => {
              const attachments = await db.query.techSalesVendorQuotationAttachments.findMany({
                where: eq(techSalesVendorQuotationAttachments.quotationId, quotation.id),
                orderBy: [desc(techSalesVendorQuotationAttachments.createdAt)],
              });

              return {
                ...quotation,
                quotationAttachments: attachments.map(att => ({
                  id: att.id,
                  fileName: att.fileName,
                  fileSize: att.fileSize,
                  filePath: att.filePath,
                  description: att.description,
                }))
              };
            })
          );

          const total = await countTechSalesVendorQuotationsWithJoin(tx, finalWhere);
          return { data: dataWithAttachments, total };
        });

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

        return { data, pageCount, total };
      } catch (err) {
        console.error("Error fetching vendor quotations with join:", err);
        return { data: [], pageCount: 0, total: 0 };
      }
    },
    [JSON.stringify(input)],
    {
      revalidate: 60,
      tags: [
        "techSalesVendorQuotations",
        ...(input.rfqId ? [`techSalesRfq-${input.rfqId}`] : [])
      ],
    }
  )();
}

/**
 * 직접 조인을 사용하여 RFQ 대시보드 데이터 조회하는 함수
 */
export async function getTechSalesDashboardWithJoin(input: {
    search?: string;
  filters?: Filter<typeof techSalesRfqs>[];
  sort?: { id: string; desc: boolean }[];
  page: number;
  perPage: number;
  rfqType?: "SHIP" | "TOP" | "HULL"; // rfqType 매개변수 추가
}) {
  unstable_noStore(); // 대시보드는 항상 최신 데이터를 보여주기 위해 캐시하지 않음

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

    // Advanced filtering
    const advancedWhere = input.filters ? filterColumns({
      table: techSalesRfqs,
      filters: input.filters as Filter<typeof techSalesRfqs>[],
      joinOperator: 'and',
    }) : undefined;

    // Global search
    let globalWhere;
    if (input.search) {
      const s = `%${input.search}%`;
      globalWhere = or(
        ilike(techSalesRfqs.rfqCode, s),
        ilike(techSalesRfqs.materialCode, s),
        ilike(techSalesRfqs.description, s)
      );
    }

    const finalWhere = and(
      advancedWhere,
      globalWhere
    );

    // 정렬 기준 설정
    let orderBy: OrderByType[] = [desc(techSalesRfqs.updatedAt)]; // 기본 정렬
    
    if (input.sort?.length) {
      // 안전하게 접근하여 정렬 기준 설정
      orderBy = input.sort.map(item => {
        switch (item.id) {
          case 'id':
            return item.desc ? desc(techSalesRfqs.id) : techSalesRfqs.id;
          case 'rfqCode':
            return item.desc ? desc(techSalesRfqs.rfqCode) : techSalesRfqs.rfqCode;
          case 'status':
            return item.desc ? desc(techSalesRfqs.status) : techSalesRfqs.status;
          case 'dueDate':
            return item.desc ? desc(techSalesRfqs.dueDate) : techSalesRfqs.dueDate;
          case 'createdAt':
            return item.desc ? desc(techSalesRfqs.createdAt) : techSalesRfqs.createdAt;
          case 'updatedAt':
            return item.desc ? desc(techSalesRfqs.updatedAt) : techSalesRfqs.updatedAt;
          default:
            return item.desc ? desc(techSalesRfqs.updatedAt) : techSalesRfqs.updatedAt;
        }
      });
    }

    // 트랜잭션 내부에서 Repository 호출
    const data = await db.transaction(async (tx) => {
      return await selectTechSalesDashboardWithJoin(tx, {
        where: finalWhere,
        orderBy,
        offset,
        limit: input.perPage,
        rfqType: input.rfqType, // rfqType 매개변수 추가
      });
    });

    return { data, success: true };
  } catch (err) {
    console.error("Error fetching dashboard data with join:", err);
    return { data: [], success: false, error: getErrorMessage(err) };
  }
}

/**
 * 특정 RFQ의 벤더 목록 조회
 */
export async function getTechSalesRfqVendors(rfqId: number) {
  unstable_noStore();
  try {
    // Repository 함수를 사용하여 벤더 견적 목록 조회
    const result = await getTechSalesVendorQuotationsWithJoin({
      rfqId,
      page: 1,
      perPage: 1000, // 충분히 큰 수로 설정하여 모든 벤더 조회
    });

    return { data: result.data, error: null };
  } catch (err) {
    console.error("Error fetching RFQ vendors:", err);
    return { data: [], error: getErrorMessage(err) };
  }
}

/**
 * 기술영업 RFQ 발송 (선택된 벤더들의 선택된 contact들에게)
 */
export async function sendTechSalesRfqToVendors(input: {
  rfqId: number;
  vendorIds: number[];
  selectedContacts?: Array<{
    vendorId: number;
    contactId: number;
    contactEmail: string;
    contactName: string;
  }>;
}) {
  unstable_noStore();
  try {
    // 인증 확인
    const session = await getServerSession(authOptions);

    if (!session?.user) {
      return {
        success: false,
        message: "인증이 필요합니다",
      };
    }

    // RFQ 정보 조회
    const rfq = await db.query.techSalesRfqs.findFirst({
      where: eq(techSalesRfqs.id, input.rfqId),
      columns: {
        id: true,
        rfqCode: true,
        status: true,
        dueDate: true,
        rfqSendDate: true,
        remark: true,
        materialCode: true,
        description: true,
        rfqType: true,
      },
      with: {
        biddingProject: true,
        createdByUser: {
          columns: {
            id: true,
            name: true,
            email: true,
          }
        }
      }
    });

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

    // 발송 가능한 상태인지 확인
    if (rfq.status !== "RFQ Vendor Assignned" && rfq.status !== "RFQ Sent") {
      return {
        success: false,
        message: "벤더가 할당된 RFQ 또는 이미 전송된 RFQ만 다시 전송할 수 있습니다",
      };
    }

    const isResend = rfq.status === "RFQ Sent";

    // 현재 사용자 정보 조회
    const sender = await db.query.users.findFirst({
      where: eq(users.id, Number(session.user.id)),
      columns: {
        id: true,
        email: true,
        name: true,
      }
    });

    if (!sender || !sender.email) {
      return {
        success: false,
        message: "보내는 사람의 이메일 정보를 찾을 수 없습니다",
      };
    }

    // 선택된 벤더들의 견적서 정보 조회
    const vendorQuotations = await db.query.techSalesVendorQuotations.findMany({
      where: and(
        eq(techSalesVendorQuotations.rfqId, input.rfqId),
        inArray(techSalesVendorQuotations.vendorId, input.vendorIds)
      ),
      columns: {
        id: true,
        vendorId: true,
        status: true,
        currency: true,
      },
      with: {
        vendor: {
          columns: {
            id: true,
            vendorName: true,
            vendorCode: true,
          }
        }
      }
    });

    if (vendorQuotations.length === 0) {
      return {
        success: false,
        message: "선택된 벤더가 이 RFQ에 할당되어 있지 않습니다",
      };
    }

    // 트랜잭션 시작
    await db.transaction(async (tx) => {
      // 1. RFQ 상태 업데이트 (최초 발송인 경우 rfqSendDate 설정)
      const updateData: Partial<typeof techSalesRfqs.$inferInsert> = {
        status: "RFQ Sent",
        sentBy: Number(session.user.id),
        updatedBy: Number(session.user.id),
        updatedAt: new Date(),
      };

      // rfqSendDate가 null인 경우에만 최초 전송일 설정
      if (!rfq.rfqSendDate) {
        updateData.rfqSendDate = new Date();
      }

      await tx.update(techSalesRfqs)
        .set(updateData)
        .where(eq(techSalesRfqs.id, input.rfqId));

      // 2. 선택된 벤더들의 견적서 상태를 "Assigned"에서 "Draft"로 변경
      for (const quotation of vendorQuotations) {
        if (quotation.status === "Assigned") {
          await tx.update(techSalesVendorQuotations)
            .set({
              status: "Draft",
              updatedBy: Number(session.user.id),
              updatedAt: new Date(),
            })
            .where(eq(techSalesVendorQuotations.id, quotation.id));
        }
      }

      // 3. 각 벤더에 대해 이메일 발송 처리
      for (const quotation of vendorQuotations) {
        if (!quotation.vendorId || !quotation.vendor) continue;

        let vendorEmailsString = "";

        // contact 기반 발송 또는 기존 방식 (모든 벤더 사용자)
        if (input.selectedContacts && input.selectedContacts.length > 0) {
          // 선택된 contact들에게만 발송
          const vendorContacts = input.selectedContacts.filter(
            contact => contact.vendorId === quotation.vendor!.id
          );
          
          if (vendorContacts.length > 0) {
            vendorEmailsString = vendorContacts
              .map(contact => contact.contactEmail)
              .join(", ");
          }
        } else {
          // 기존 방식: 벤더에 속한 모든 사용자에게 발송
          const vendorUsers = await db.query.users.findMany({
            where: eq(users.companyId, quotation.vendor.id),
            columns: {
              id: true,
              email: true,
              name: true,
              language: true
            }
          });

          vendorEmailsString = vendorUsers
            .filter(user => user.email)
            .map(user => user.email)
            .join(", ");
        }

        if (vendorEmailsString) {
          // 대표 언어 결정 (기본값 한국어)
          const language = "ko";

          // RFQ 아이템 목록 조회
          const rfqItemsResult = await getTechSalesRfqItems(rfq.id);
          const rfqItems = rfqItemsResult.data || [];

          // 이메일 컨텍스트 구성
          const emailContext = {
            language: language,
            rfq: {
              id: rfq.id,
              code: rfq.rfqCode,
              title: rfqItems.length > 0 ? rfqItems.map(item => item.itemList).join(', ') : '',
              projectCode: rfq.biddingProject?.pspid || '',
              projectName: rfq.biddingProject?.projNm || '',
              description: rfq.remark || '',
              dueDate: rfq.dueDate ? formatDate(rfq.dueDate, "KR") : 'N/A',
              materialCode: rfq.materialCode || '',
              type: rfq.rfqType || 'SHIP',
            },
            items: rfqItems.map(item => ({
              itemCode: item.itemCode,
              itemList: item.itemList,
              workType: item.workType,
              shipTypes: item.shipTypes,
              subItemList: item.subItemList,
              itemType: item.itemType,
            })),
            vendor: {
              id: quotation.vendor.id,
              code: quotation.vendor.vendorCode || '',
              name: quotation.vendor.vendorName,
            },
            sender: {
              fullName: sender.name || '',
              email: sender.email,
            },
            project: {
              id: rfq.biddingProject?.pspid || '',
              name: rfq.biddingProject?.projNm || '',
              sector: rfq.biddingProject?.sector || '',
              shipType: rfq.biddingProject?.ptypeNm || '',
              shipCount: rfq.biddingProject?.projMsrm || 0,
              ownerName: rfq.biddingProject?.kunnrNm || '',
              className: rfq.biddingProject?.cls1Nm || '',
            },
            details: {
              currency: quotation.currency || 'USD',
            },
            quotationCode: `${rfq.rfqCode}-${quotation.vendorId}`,
            systemUrl: process.env.NEXT_PUBLIC_APP_URL || 'http://60.101.108.100/ko/partners',
            isResend: isResend,
            versionInfo: isResend ? '(재전송)' : '',
          }



          // 이메일 전송
          await sendEmail({
            to: vendorEmailsString,
            subject: isResend 
              ? `[기술영업 RFQ 재전송] ${rfq.rfqCode} - ${rfqItems.length > 0 ? rfqItems.map(item => item.itemList).join(', ') : '견적 요청'} ${emailContext.versionInfo}`
              : `[기술영업 RFQ] ${rfq.rfqCode} - ${rfqItems.length > 0 ? rfqItems.map(item => item.itemList).join(', ') : '견적 요청'}`,
            template: 'tech-sales-rfq-invite-ko', // 기술영업용 템플릿
            context: emailContext,
            cc: sender.email, // 발신자를 CC에 추가
          });

          // 4. 선택된 담당자 정보를 quotation_contacts 테이블에 저장
          if (input.selectedContacts && input.selectedContacts.length > 0) {
            const vendorContacts = input.selectedContacts.filter(
              contact => contact.vendorId === quotation.vendor!.id
            );

            for (const contact of vendorContacts) {
              // quotation_contacts 중복 체크
              const existingQuotationContact = await tx.query.techSalesVendorQuotationContacts.findFirst({
                where: and(
                  eq(techSalesVendorQuotationContacts.quotationId, quotation.id),
                  eq(techSalesVendorQuotationContacts.contactId, contact.contactId)
                )
              });

              if (!existingQuotationContact) {
                await tx.insert(techSalesVendorQuotationContacts).values({
                  quotationId: quotation.id,
                  contactId: contact.contactId,
                  createdAt: new Date(),
                  updatedAt: new Date(),
                });
              }

              // 5. 담당자별 아이템 매핑 정보 저장 (중복 방지)
              for (const item of rfqItems) {
                let vendorPossibleItem = null;
                // 조선: 아이템코드 + 선종으로 조선아이템테이블에서 찾기, 해양: 아이템코드로만 찾기
                if (item.itemType === "SHIP" && item.itemCode && item.shipTypes) {
                  // 조선: itemShipbuilding에서 itemCode, shipTypes로 찾기
                  const shipbuildingItem = await tx.query.itemShipbuilding.findFirst({
                    where: and(
                      eq(itemShipbuilding.itemCode, item.itemCode),
                      eq(itemShipbuilding.shipTypes, item.shipTypes)
                    )
                  });
                  if (shipbuildingItem?.id) {
                    vendorPossibleItem = await tx.query.techVendorPossibleItems.findFirst({
                      where: and(
                        eq(techVendorPossibleItems.vendorId, quotation.vendor!.id),
                        eq(techVendorPossibleItems.shipbuildingItemId, shipbuildingItem.id)
                      )
                    });
                  }
                } else if (item.itemType === "TOP" && item.itemCode) {
                  // 해양 TOP: itemOffshoreTop에서 itemCode로 찾기
                  const offshoreTopItem = await tx.query.itemOffshoreTop.findFirst({
                    where: eq(itemOffshoreTop.itemCode, item.itemCode)
                  });
                  if (offshoreTopItem?.id) {
                    vendorPossibleItem = await tx.query.techVendorPossibleItems.findFirst({
                      where: and(
                        eq(techVendorPossibleItems.vendorId, quotation.vendor!.id),
                        eq(techVendorPossibleItems.offshoreTopItemId, offshoreTopItem.id)
                      )
                    });
                  }
                } else if (item.itemType === "HULL" && item.itemCode) {
                  // 해양 HULL: itemOffshoreHull에서 itemCode로 찾기
                  const offshoreHullItem = await tx.query.itemOffshoreHull.findFirst({
                    where: eq(itemOffshoreHull.itemCode, item.itemCode)
                  });
                  if (offshoreHullItem?.id) {
                    vendorPossibleItem = await tx.query.techVendorPossibleItems.findFirst({
                      where: and(
                        eq(techVendorPossibleItems.vendorId, quotation.vendor!.id),
                        eq(techVendorPossibleItems.offshoreHullItemId, offshoreHullItem.id)
                      )
                    });
                  }
                }

                if (vendorPossibleItem) {
                  // contact_possible_items 중복 체크
                  const existingContactPossibleItem = await tx.query.techSalesContactPossibleItems.findFirst({
                    where: and(
                      eq(techSalesContactPossibleItems.contactId, contact.contactId),
                      eq(techSalesContactPossibleItems.vendorPossibleItemId, vendorPossibleItem.id)
                    )
                  });

                  if (!existingContactPossibleItem) {
                    await tx.insert(techSalesContactPossibleItems).values({
                      contactId: contact.contactId,
                      vendorPossibleItemId: vendorPossibleItem.id,
                      createdAt: new Date(),
                      updatedAt: new Date(),
                    });
                  }
                }
              }
            }
          }
        }
      }
    });

    // 캐시 무효화
    revalidateTag("techSalesRfqs");
    revalidateTag("techSalesVendorQuotations");
    revalidateTag(`techSalesRfq-${input.rfqId}`);
    revalidatePath(getTechSalesRevalidationPath(rfq?.rfqType || "SHIP"));

    const sentContactCount = input.selectedContacts?.length || vendorQuotations.length;
    const messageDetail = input.selectedContacts && input.selectedContacts.length > 0 
      ? `${sentContactCount}명의 연락처에게 RFQ가 성공적으로 발송되었습니다`
      : `${vendorQuotations.length}개 벤더에게 RFQ가 성공적으로 발송되었습니다`;

    return {
      success: true,
      message: messageDetail,
      sentCount: sentContactCount,
    };
  } catch (err) {
    console.error("기술영업 RFQ 발송 오류:", err);
    return {
      success: false,
      message: "RFQ 발송 중 오류가 발생했습니다",
    };
  }
}

/**
 * 벤더용 기술영업 RFQ 견적서 조회 (withJoin 사용)
 */
export async function getTechSalesVendorQuotation(quotationId: number) {
  unstable_noStore();
  try {
    const quotation = await db.transaction(async (tx) => {
      return await selectSingleTechSalesVendorQuotationWithJoin(tx, quotationId);
    });

    if (!quotation) {
      return { data: null, error: "견적서를 찾을 수 없습니다." };
    }

    // RFQ 아이템 정보도 함께 조회
    const itemsResult = await getTechSalesRfqItems(quotation.rfqId);
    const items = itemsResult.data || [];

    // 견적서 첨부파일 조회
    const quotationAttachments = await db.query.techSalesVendorQuotationAttachments.findMany({
      where: eq(techSalesVendorQuotationAttachments.quotationId, quotationId),
      orderBy: [desc(techSalesVendorQuotationAttachments.createdAt)],
    });

    // 기존 구조와 호환되도록 데이터 재구성
    const formattedQuotation = {
      id: quotation.id,
      rfqId: quotation.rfqId,
      vendorId: quotation.vendorId,
      quotationCode: quotation.quotationCode,
      quotationVersion: quotation.quotationVersion,
      totalPrice: quotation.totalPrice,
      currency: quotation.currency,
      validUntil: quotation.validUntil,
      status: quotation.status,
      remark: quotation.remark,
      rejectionReason: quotation.rejectionReason,
      submittedAt: quotation.submittedAt,
      acceptedAt: quotation.acceptedAt,
      createdAt: quotation.createdAt,
      updatedAt: quotation.updatedAt,
      createdBy: quotation.createdBy,
      updatedBy: quotation.updatedBy,
      
      // RFQ 정보
      rfq: {
        id: quotation.rfqId,
        rfqCode: quotation.rfqCode,
        rfqType: quotation.rfqType,
        status: quotation.rfqStatus,
        dueDate: quotation.dueDate,
        rfqSendDate: quotation.rfqSendDate,
        materialCode: quotation.materialCode,
        description: quotation.description,
        remark: quotation.rfqRemark,
        picCode: quotation.picCode,
        createdBy: quotation.rfqCreatedBy,
        biddingProjectId: quotation.biddingProjectId,
        
        // 아이템 정보 추가
        items: items,
        
        // 생성자 정보
        createdByUser: {
          id: quotation.rfqCreatedBy,
          name: quotation.rfqCreatedByName,
          email: quotation.rfqCreatedByEmail,
        },
        
        // 프로젝트 정보
        biddingProject: quotation.biddingProjectId ? {
          id: quotation.biddingProjectId,
          pspid: quotation.pspid,
          projNm: quotation.projNm,
          sector: quotation.sector,
          projMsrm: quotation.projMsrm,
          ptypeNm: quotation.ptypeNm,
        } : null,
      },
      
      // 벤더 정보
      vendor: {
        id: quotation.vendorId,
        vendorName: quotation.vendorName,
        vendorCode: quotation.vendorCode,
        country: quotation.vendorCountry,
        email: quotation.vendorEmail,
        phone: quotation.vendorPhone,
      },

      // 첨부파일 정보
      quotationAttachments: quotationAttachments.map(attachment => ({
        id: attachment.id,
        fileName: attachment.fileName,
        fileSize: attachment.fileSize,
        filePath: attachment.filePath,
        description: attachment.description,
      }))
    };

    return { data: formattedQuotation, error: null };
  } catch (err) {
    console.error("Error fetching vendor quotation:", err);
    return { data: null, error: getErrorMessage(err) };
  }
}

/**
 * 기술영업 벤더 견적서 업데이트 (임시저장),
 * 현재는 submit으로 처리, revision 을 아래의 함수로 사용가능함.
 */
export async function updateTechSalesVendorQuotation(data: {
  id: number
  currency: string
  totalPrice: string
  validUntil: Date
  remark?: string
  updatedBy: number
  changeReason?: string
}) {
  try {
    return await db.transaction(async (tx) => {
      // 현재 견적서 전체 데이터 조회 (revision 저장용)
      const currentQuotation = await tx.query.techSalesVendorQuotations.findFirst({
        where: eq(techSalesVendorQuotations.id, data.id),
      });

      if (!currentQuotation) {
        return { data: null, error: "견적서를 찾을 수 없습니다." };
      }

      // Accepted나 Rejected 상태가 아니면 수정 가능
      if (["Rejected"].includes(currentQuotation.status)) {
        return { data: null, error: "승인되거나 거절된 견적서는 수정할 수 없습니다." };
      }

      // 실제 변경사항이 있는지 확인
      const hasChanges = 
        currentQuotation.currency !== data.currency ||
        currentQuotation.totalPrice !== data.totalPrice ||
        currentQuotation.validUntil?.getTime() !== data.validUntil.getTime() ||
        currentQuotation.remark !== (data.remark || null);

      if (!hasChanges) {
        return { data: currentQuotation, error: null };
      }

      // 현재 버전을 revision history에 저장
      await tx.insert(techSalesVendorQuotationRevisions).values({
        quotationId: data.id,
        version: currentQuotation.quotationVersion || 1,
        snapshot: {
          currency: currentQuotation.currency,
          totalPrice: currentQuotation.totalPrice,
          validUntil: currentQuotation.validUntil,
          remark: currentQuotation.remark,
          status: currentQuotation.status,
          quotationVersion: currentQuotation.quotationVersion,
          submittedAt: currentQuotation.submittedAt,
          acceptedAt: currentQuotation.acceptedAt,
          updatedAt: currentQuotation.updatedAt,
        },
        changeReason: data.changeReason || "견적서 수정",
        revisedBy: data.updatedBy,
      });

      // 새로운 버전으로 업데이트
      const result = await tx
        .update(techSalesVendorQuotations)
        .set({
          currency: data.currency,
          totalPrice: data.totalPrice,
          validUntil: data.validUntil,
          remark: data.remark || null,
          quotationVersion: (currentQuotation.quotationVersion || 1) + 1,
          status: "Revised", // 수정된 상태로 변경
          updatedAt: new Date(),
        })
        .where(eq(techSalesVendorQuotations.id, data.id))
        .returning();

      return { data: result[0], error: null };
    });
  } catch (error) {
    console.error("Error updating tech sales vendor quotation:", error);
    return { data: null, error: "견적서 업데이트 중 오류가 발생했습니다" };
  } finally {
    // 캐시 무효화
    revalidateTag("techSalesVendorQuotations");
    revalidatePath(`/partners/techsales/rfq-ship/${data.id}`);
  }
}

/**
 * 기술영업 벤더 견적서 제출
 */
export async function submitTechSalesVendorQuotation(data: {
  id: number
  currency: string
  totalPrice: string
  validUntil: Date
  remark?: string
  attachments?: Array<{
    fileName: string
    originalFileName: string
    filePath: string
    fileSize: number
  }>
  updatedBy: number
}) {
  try {
    return await db.transaction(async (tx) => {
      // 현재 견적서 전체 데이터 조회 (revision 저장용)
      const currentQuotation = await tx.query.techSalesVendorQuotations.findFirst({
        where: eq(techSalesVendorQuotations.id, data.id),
      });

      if (!currentQuotation) {
        return { data: null, error: "견적서를 찾을 수 없습니다." };
      }

      // Rejected 상태에서는 제출 불가
      if (["Rejected"].includes(currentQuotation.status)) {
        return { data: null, error: "거절된 견적서는 제출할 수 없습니다." };
      }
      
      // // 실제 변경사항이 있는지 확인
      // const hasChanges = 
      //   currentQuotation.currency !== data.currency ||
      //   currentQuotation.totalPrice !== data.totalPrice ||
      //   currentQuotation.validUntil?.getTime() !== data.validUntil.getTime() ||
      //   currentQuotation.remark !== (data.remark || null);

      // // 변경사항이 있거나 처음 제출하는 경우 revision 저장
      // if (hasChanges || currentQuotation.status === "Draft") {
      //   await tx.insert(techSalesVendorQuotationRevisions).values({
      //     quotationId: data.id,
      //     version: currentQuotation.quotationVersion || 1,
      //     snapshot: {
      //       currency: currentQuotation.currency,
      //       totalPrice: currentQuotation.totalPrice,
      //       validUntil: currentQuotation.validUntil,
      //       remark: currentQuotation.remark,
      //       status: currentQuotation.status,
      //       quotationVersion: currentQuotation.quotationVersion,
      //       submittedAt: currentQuotation.submittedAt,
      //       acceptedAt: currentQuotation.acceptedAt,
      //       updatedAt: currentQuotation.updatedAt,
      //     },
      //     changeReason: "견적서 제출",
      //     revisedBy: data.updatedBy,
      //   });
      // }

      // 첫 제출인지 확인 (quotationVersion이 null인 경우)
      const isFirstSubmission = currentQuotation.quotationVersion === null;
      
      // 첫 제출이 아닌 경우에만 revision 저장 (변경사항 이력 관리)
      if (!isFirstSubmission) {
        await tx.insert(techSalesVendorQuotationRevisions).values({
          quotationId: data.id,
          version: currentQuotation.quotationVersion || 1,
          snapshot: {
            currency: currentQuotation.currency,
            totalPrice: currentQuotation.totalPrice,
            validUntil: currentQuotation.validUntil,
            remark: currentQuotation.remark,
            status: currentQuotation.status,
            quotationVersion: currentQuotation.quotationVersion,
            submittedAt: currentQuotation.submittedAt,
            acceptedAt: currentQuotation.acceptedAt,
            updatedAt: currentQuotation.updatedAt,
          },
          changeReason: "견적서 제출",
          revisedBy: data.updatedBy,
        });
      }

      // 새로운 버전 번호 계산 (첫 제출은 1, 재제출은 1 증가)
      const newRevisionId = isFirstSubmission ? 1 : (currentQuotation.quotationVersion || 1) + 1;

      // 새로운 버전으로 업데이트
      const result = await tx
        .update(techSalesVendorQuotations)
        .set({
          currency: data.currency,
          totalPrice: data.totalPrice,
          validUntil: data.validUntil,
          remark: data.remark || null,
          quotationVersion: newRevisionId,
          status: "Submitted",
          submittedAt: new Date(),
          updatedAt: new Date(),
        })
        .where(eq(techSalesVendorQuotations.id, data.id))
        .returning();

      // 첨부파일 처리 (새로운 revisionId 사용)
      if (data.attachments && data.attachments.length > 0) {
        for (const attachment of data.attachments) {
          await tx.insert(techSalesVendorQuotationAttachments).values({
            quotationId: data.id,
            revisionId: newRevisionId, // 새로운 리비전 ID 사용
            fileName: attachment.fileName, // 해시된 파일명 (저장용)
            originalFileName: attachment.originalFileName, // 원본 파일명 (표시용)
            fileSize: attachment.fileSize,
            filePath: attachment.filePath,
            fileType: attachment.originalFileName.split('.').pop() || 'unknown',
            uploadedBy: data.updatedBy,
            isVendorUpload: true,
          });
        }
      }

      // 메일 발송 (백그라운드에서 실행)
      if (result[0]) {
        // 벤더에게 견적 제출 확인 메일 발송
        sendQuotationSubmittedNotificationToVendor(data.id).catch(error => {
          console.error("벤더 견적 제출 확인 메일 발송 실패:", error);
        });

        // 담당자에게 견적 접수 알림 메일 발송
        sendQuotationSubmittedNotificationToManager(data.id).catch(error => {
          console.error("담당자 견적 접수 알림 메일 발송 실패:", error);
        });
      }

      return { data: result[0], error: null };
    });
  } catch (error) {
    console.error("Error submitting tech sales vendor quotation:", error);
    return { data: null, error: "견적서 제출 중 오류가 발생했습니다" };
  } finally {
    // 캐시 무효화
    revalidateTag("techSalesVendorQuotations");
    revalidatePath(`/partners/techsales/rfq-ship`);
  }
}

/**
 * 통화 목록 조회
 */
export async function fetchCurrencies() {
  try {
    // 기본 통화 목록 (실제로는 DB에서 가져와야 함)
    const currencies = [
      { code: "USD", name: "미국 달러" },
      { code: "KRW", name: "한국 원" },
      { code: "EUR", name: "유로" },
      { code: "JPY", name: "일본 엔" },
      { code: "CNY", name: "중국 위안" },
    ]

    return { data: currencies, error: null }
  } catch (error) {
    console.error("Error fetching currencies:", error)
    return { data: null, error: "통화 목록 조회 중 오류가 발생했습니다" }
  }
}

/**
 * 벤더용 기술영업 견적서 목록 조회 (페이지네이션 포함)
 */
export async function getVendorQuotations(input: {
  flags?: string[];
  page: number;
  perPage: number;
  sort?: { id: string; desc: boolean }[];
  filters?: Filter<typeof techSalesVendorQuotations>[];
  joinOperator?: "and" | "or";
  basicFilters?: Filter<typeof techSalesVendorQuotations>[];
  basicJoinOperator?: "and" | "or";
  search?: string;
  from?: string;
  to?: string;
  rfqType?: "SHIP" | "TOP" | "HULL";
}, vendorId: string) {
  return unstable_cache(
    async () => {
      try {


        const { page, perPage, sort, filters = [], search = "", from = "", to = "" } = input;
        const offset = (page - 1) * perPage;
        const limit = perPage;

        // 기본 조건: 해당 벤더의 견적서만 조회 (Assigned 상태 제외)
        const vendorIdNum = parseInt(vendorId);
        if (isNaN(vendorIdNum)) {
          console.error('❌ [getVendorQuotations] Invalid vendorId:', vendorId);
          return { data: [], pageCount: 0, total: 0 };
        }

        const baseConditions = [
          eq(techSalesVendorQuotations.vendorId, vendorIdNum),
          sql`${techSalesVendorQuotations.status} != 'Assigned'` // Assigned 상태 제외
        ];

        // rfqType 필터링 추가
        if (input.rfqType) {
          baseConditions.push(eq(techSalesRfqs.rfqType, input.rfqType));
        }

        // 검색 조건 추가
        if (search) {
          const s = `%${search}%`;
          const searchCondition = or(
            ilike(techSalesVendorQuotations.currency, s),
            ilike(techSalesVendorQuotations.status, s)
          );
          if (searchCondition) {
            baseConditions.push(searchCondition);
          }
        }

        // 날짜 범위 필터
        if (from) {
          baseConditions.push(sql`${techSalesVendorQuotations.createdAt} >= ${from}`);
        }
        if (to) {
          baseConditions.push(sql`${techSalesVendorQuotations.createdAt} <= ${to}`);
        }

        // 고급 필터 처리
        if (filters.length > 0) {
          const filterWhere = filterColumns({
            table: techSalesVendorQuotations,
            filters: filters as Filter<typeof techSalesVendorQuotations>[],
            joinOperator: input.joinOperator || "and",
          });
          if (filterWhere) {
            baseConditions.push(filterWhere);
          }
        }

        // 최종 WHERE 조건
        const finalWhere = baseConditions.length > 0 
          ? and(...baseConditions) 
          : undefined;

        // 정렬 기준 설정
        let orderBy: OrderByType[] = [desc(techSalesVendorQuotations.updatedAt)];
        
        if (sort?.length) {
          orderBy = sort.map(item => {
            switch (item.id) {
              case 'id':
                return item.desc ? desc(techSalesVendorQuotations.id) : techSalesVendorQuotations.id;
              case 'status':
                return item.desc ? desc(techSalesVendorQuotations.status) : techSalesVendorQuotations.status;
              case 'currency':
                return item.desc ? desc(techSalesVendorQuotations.currency) : techSalesVendorQuotations.currency;
              case 'totalPrice':
                return item.desc ? desc(techSalesVendorQuotations.totalPrice) : techSalesVendorQuotations.totalPrice;
              case 'validUntil':
                return item.desc ? desc(techSalesVendorQuotations.validUntil) : techSalesVendorQuotations.validUntil;
              case 'submittedAt':
                return item.desc ? desc(techSalesVendorQuotations.submittedAt) : techSalesVendorQuotations.submittedAt;
              case 'createdAt':
                return item.desc ? desc(techSalesVendorQuotations.createdAt) : techSalesVendorQuotations.createdAt;
              case 'updatedAt':
                return item.desc ? desc(techSalesVendorQuotations.updatedAt) : techSalesVendorQuotations.updatedAt;
              case 'rfqCode':
                return item.desc ? desc(techSalesRfqs.rfqCode) : techSalesRfqs.rfqCode;
              case 'materialCode':
                return item.desc ? desc(techSalesRfqs.materialCode) : techSalesRfqs.materialCode;
              case 'dueDate':
                return item.desc ? desc(techSalesRfqs.dueDate) : techSalesRfqs.dueDate;
              case 'rfqStatus':
                return item.desc ? desc(techSalesRfqs.status) : techSalesRfqs.status;
              default:
                return item.desc ? desc(techSalesVendorQuotations.updatedAt) : techSalesVendorQuotations.updatedAt;
            }
          });
        }

        // 조인을 포함한 데이터 조회 (중복 제거를 위해 techSalesAttachments JOIN 제거)
        const data = await db
          .select({
            id: techSalesVendorQuotations.id,
            rfqId: techSalesVendorQuotations.rfqId,
            vendorId: techSalesVendorQuotations.vendorId,
            status: techSalesVendorQuotations.status,
            currency: techSalesVendorQuotations.currency,
            totalPrice: techSalesVendorQuotations.totalPrice,
            validUntil: techSalesVendorQuotations.validUntil,
            submittedAt: techSalesVendorQuotations.submittedAt,
            remark: techSalesVendorQuotations.remark,
            createdAt: techSalesVendorQuotations.createdAt,
            updatedAt: techSalesVendorQuotations.updatedAt,
            createdBy: techSalesVendorQuotations.createdBy,
            updatedBy: techSalesVendorQuotations.updatedBy,
            quotationCode: techSalesVendorQuotations.quotationCode,
            quotationVersion: techSalesVendorQuotations.quotationVersion,
            rejectionReason: techSalesVendorQuotations.rejectionReason,
            acceptedAt: techSalesVendorQuotations.acceptedAt,
            // RFQ 정보
            rfqCode: techSalesRfqs.rfqCode,
            materialCode: techSalesRfqs.materialCode,
            dueDate: techSalesRfqs.dueDate,
            rfqStatus: techSalesRfqs.status,
            description: techSalesRfqs.description,
            // 프로젝트 정보 (직접 조인)
            projNm: biddingProjects.projNm,
            // 아이템 개수
            itemCount: sql<number>`(
              SELECT COUNT(*) 
              FROM tech_sales_rfq_items 
              WHERE tech_sales_rfq_items.rfq_id = ${techSalesRfqs.id}
            )`,
            // RFQ 첨부파일 개수 (RFQ_COMMON 타입만 카운트)
            attachmentCount: sql<number>`(
              SELECT COUNT(*) 
              FROM tech_sales_attachments 
              WHERE tech_sales_attachments.tech_sales_rfq_id = ${techSalesRfqs.id}
              AND tech_sales_attachments.attachment_type = 'RFQ_COMMON'
            )`,
          })
          .from(techSalesVendorQuotations)
          .leftJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id))
          .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id))
          .where(finalWhere)
          .orderBy(...orderBy)
          .limit(limit)
          .offset(offset);

        // 총 개수 조회
        const totalResult = await db
          .select({ count: sql<number>`count(*)` })
          .from(techSalesVendorQuotations)
          .leftJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id))
          .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id))
          .where(finalWhere);

        const total = totalResult[0]?.count || 0;
        const pageCount = Math.ceil(total / perPage);

        return { data, pageCount, total };
      } catch (err) {
        console.error("Error fetching vendor quotations:", err);
        return { data: [], pageCount: 0, total: 0 };
      }
    },
    [JSON.stringify(input), vendorId], // 캐싱 키
    {
      revalidate: 60, // 1분간 캐시
      tags: [
        "techSalesVendorQuotations", 
        `vendor-${vendorId}-quotations`
      ],
    }
  )();
}

/**
 * 기술영업 벤더 견적 승인 (벤더 선택)
 */
export async function acceptTechSalesVendorQuotation(quotationId: number) {
  try {
    const result = await db.transaction(async (tx) => {
      // 1. 선택된 견적 정보 조회
      const selectedQuotation = await tx
        .select()
        .from(techSalesVendorQuotations)
        .where(eq(techSalesVendorQuotations.id, quotationId))
        .limit(1)

      if (selectedQuotation.length === 0) {
        throw new Error("견적을 찾을 수 없습니다")
      }

      const quotation = selectedQuotation[0]

      // 2. 선택된 견적을 Accepted로 변경
      await tx
        .update(techSalesVendorQuotations)
        .set({
          status: "Accepted",
          acceptedAt: new Date(),
          updatedAt: new Date(),
        })
        .where(eq(techSalesVendorQuotations.id, quotationId))

      // 4. RFQ 상태를 Closed로 변경
      await tx
        .update(techSalesRfqs)
        .set({
          status: "Closed",
          updatedAt: new Date(),
        })
        .where(eq(techSalesRfqs.id, quotation.rfqId))

      return quotation
    })

    // // 메일 발송 (백그라운드에서 실행)
    // // 선택된 벤더에게 견적 선택 알림 메일 발송
    // sendQuotationAcceptedNotification(quotationId).catch(error => {
    //   console.error("벤더 견적 선택 알림 메일 발송 실패:", error);
    // });

    // 캐시 무효화
    revalidateTag("techSalesVendorQuotations")
    revalidateTag(`techSalesRfq-${result.rfqId}`)
    revalidateTag("techSalesRfqs")

    // 해당 RFQ의 모든 벤더 캐시 무효화 (선택된 벤더와 거절된 벤더들)
    const allVendorsInRfq = await db.query.techSalesVendorQuotations.findMany({
      where: eq(techSalesVendorQuotations.rfqId, result.rfqId),
      columns: { vendorId: true }
    });
    
    for (const vendorQuotation of allVendorsInRfq) {
      revalidateTag(`vendor-${vendorQuotation.vendorId}-quotations`);
    }
    revalidatePath("/evcp/budgetary-tech-sales-ship")
    revalidatePath("/partners/techsales")
    

    return { success: true, data: result }
  } catch (error) {
    console.error("벤더 견적 승인 오류:", error)
    return { 
      success: false, 
      error: error instanceof Error ? error.message : "벤더 견적 승인에 실패했습니다" 
    }
  }
}

/**
 * 기술영업 RFQ 첨부파일 생성
 */
export async function createTechSalesRfqAttachments(params: {
  techSalesRfqId: number
  files: File[]
  createdBy: number
  attachmentType?: "RFQ_COMMON" | "VENDOR_SPECIFIC"
  description?: string
}) {
  unstable_noStore();
  try {
    const { techSalesRfqId, files, createdBy, attachmentType = "RFQ_COMMON", description } = params;
    

    
    if (!files || files.length === 0) {
      return { data: null, error: "업로드할 파일이 없습니다." };
    }

    // RFQ 존재 확인
    const rfq = await db.query.techSalesRfqs.findFirst({
      where: eq(techSalesRfqs.id, techSalesRfqId),
      columns: { id: true, status: true }
    });

    if (!rfq) {
      return { data: null, error: "RFQ를 찾을 수 없습니다." };
    }

    // // 편집 가능한 상태 확인
    // if (!["RFQ Created", "RFQ Vendor Assignned"].includes(rfq.status)) {
    //   return { data: null, error: "현재 상태에서는 첨부파일을 추가할 수 없습니다." };
    // }

    const results: typeof techSalesAttachments.$inferSelect[] = [];

    // 트랜잭션으로 처리
    await db.transaction(async (tx) => {

      for (const file of files) {

        
        const saveResult = await saveDRMFile(
          file,
          decryptWithServerAction,
          `techsales-rfq/${techSalesRfqId}`
        );

        if (!saveResult.success) {
          throw new Error(saveResult.error || "파일 저장에 실패했습니다.");
        }

        // DB에 첨부파일 레코드 생성
        const [newAttachment] = await tx.insert(techSalesAttachments).values({
          techSalesRfqId,
          attachmentType,
          fileName: saveResult.fileName!,
          originalFileName: file.name,
          filePath: saveResult.publicPath!,
          fileSize: file.size,
          fileType: file.type || undefined,
          description: description || undefined,
          createdBy,
        }).returning();

        results.push(newAttachment);
      }
    });



    // RFQ 타입 조회하여 캐시 무효화
    const rfqType = await db.query.techSalesRfqs.findFirst({
      where: eq(techSalesRfqs.id, techSalesRfqId),
      columns: { rfqType: true }
    });
    
    revalidateTag("techSalesRfqs");
    revalidateTag(`techSalesRfq-${techSalesRfqId}`);
    revalidatePath(getTechSalesRevalidationPath(rfqType?.rfqType || "SHIP"));
    revalidatePath("/partners/techsales");
    return { data: results, error: null };
  } catch (err) {
    console.error("기술영업 RFQ 첨부파일 생성 오류:", err);
    return { data: null, error: getErrorMessage(err) };
  }
}

/**
 * 기술영업 RFQ 첨부파일 조회
 */
export async function getTechSalesRfqAttachments(techSalesRfqId: number) {
  unstable_noStore();
  try {
    const attachments = await db.query.techSalesAttachments.findMany({
      where: eq(techSalesAttachments.techSalesRfqId, techSalesRfqId),
      orderBy: [desc(techSalesAttachments.createdAt)],
      with: {
        createdByUser: {
          columns: {
            id: true,
            name: true,
            email: true,
          }
        }
      }
    });

    return { data: attachments, error: null };
  } catch (err) {
    console.error("기술영업 RFQ 첨부파일 조회 오류:", err);
    return { data: [], error: getErrorMessage(err) };
  }
}

/**
 * RFQ 첨부파일 타입별 조회
 */
export async function getTechSalesRfqAttachmentsByType(
  techSalesRfqId: number, 
  attachmentType: "RFQ_COMMON" | "VENDOR_SPECIFIC" | "TBE_RESULT" | "CBE_RESULT"
) {
  unstable_noStore();
  try {
    const attachments = await db.query.techSalesAttachments.findMany({
      where: and(
        eq(techSalesAttachments.techSalesRfqId, techSalesRfqId),
        eq(techSalesAttachments.attachmentType, attachmentType)
      ),
      orderBy: [desc(techSalesAttachments.createdAt)],
      with: {
        createdByUser: {
          columns: {
            id: true,
            name: true,
            email: true,
          }
        }
      }
    });

    return { data: attachments, error: null };
  } catch (err) {
    console.error(`기술영업 RFQ ${attachmentType} 첨부파일 조회 오류:`, err);
    return { data: [], error: getErrorMessage(err) };
  }
}

/**
 * 기술영업 RFQ 첨부파일 삭제
 */
export async function deleteTechSalesRfqAttachment(attachmentId: number) {
  unstable_noStore();
  try {
    // 첨부파일 정보 조회
    const attachment = await db.query.techSalesAttachments.findFirst({
      where: eq(techSalesAttachments.id, attachmentId),
    });

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

    // RFQ 상태 확인
    const rfq = await db.query.techSalesRfqs.findFirst({
      where: eq(techSalesRfqs.id, attachment.techSalesRfqId!), // Non-null assertion since we know it exists
      columns: { id: true, status: true }
    });

    if (!rfq) {
      return { data: null, error: "RFQ를 찾을 수 없습니다." };
    }

    // // 편집 가능한 상태 확인
    // if (!["RFQ Created", "RFQ Vendor Assignned"].includes(rfq.status)) {
    //   return { data: null, error: "현재 상태에서는 첨부파일을 삭제할 수 없습니다." };
    // }

    // 트랜잭션으로 처리
    const result = await db.transaction(async (tx) => {
      // DB에서 레코드 삭제
      const deletedAttachment = await tx.delete(techSalesAttachments)
        .where(eq(techSalesAttachments.id, attachmentId))
        .returning();

      // 파일 시스템에서 파일 삭제
      try {
        deleteFile(attachment.filePath)

      } catch (fileError) {
        console.warn("파일 삭제 실패:", fileError);
        // 파일 삭제 실패는 심각한 오류가 아니므로 계속 진행
      }

      return deletedAttachment[0];
    });

    // RFQ 타입 조회하여 캐시 무효화
    const attachmentRfq = await db.query.techSalesRfqs.findFirst({
      where: eq(techSalesRfqs.id, attachment.techSalesRfqId!),
      columns: { rfqType: true }
    });
    
    revalidateTag("techSalesRfqs");
    revalidateTag(`techSalesRfq-${attachment.techSalesRfqId}`);
    revalidatePath(getTechSalesRevalidationPath(attachmentRfq?.rfqType || "SHIP"));

    return { data: result, error: null };
  } catch (err) {
    console.error("기술영업 RFQ 첨부파일 삭제 오류:", err);
    return { data: null, error: getErrorMessage(err) };
  }
}

/**
 * 기술영업 RFQ 첨부파일 일괄 처리 (업로드 + 삭제)
 */
export async function processTechSalesRfqAttachments(params: {
  techSalesRfqId: number
  newFiles: { file: File; attachmentType: "RFQ_COMMON" | "VENDOR_SPECIFIC" | "TBE_RESULT" | "CBE_RESULT"; description?: string }[]
  deleteAttachmentIds: number[]
  createdBy: number
}) {
  unstable_noStore();
  try {
    const { techSalesRfqId, newFiles, deleteAttachmentIds, createdBy } = params;



    // RFQ 존재 및 상태 확인
    const rfq = await db.query.techSalesRfqs.findFirst({
      where: eq(techSalesRfqs.id, techSalesRfqId),
      columns: { id: true, status: true }
    });

    if (!rfq) {
      return { data: null, error: "RFQ를 찾을 수 없습니다." };
    }
    // // 편집 가능한 상태 확인
    // if (!["RFQ Created", "RFQ Vendor Assignned"].includes(rfq.status)) {
    //   return { data: null, error: "현재 상태에서는 첨부파일을 수정할 수 없습니다." };
    // }

    const results = {
      uploaded: [] as typeof techSalesAttachments.$inferSelect[],
      deleted: [] as typeof techSalesAttachments.$inferSelect[],
    };

    await db.transaction(async (tx) => {

      // 1. 삭제할 첨부파일 처리
      if (deleteAttachmentIds.length > 0) {
        const attachmentsToDelete = await tx.query.techSalesAttachments.findMany({
          where: sql`${techSalesAttachments.id} IN (${deleteAttachmentIds.join(',')})`
        });

        for (const attachment of attachmentsToDelete) {
          // DB에서 레코드 삭제
          const [deletedAttachment] = await tx.delete(techSalesAttachments)
            .where(eq(techSalesAttachments.id, attachment.id))
            .returning();

          results.deleted.push(deletedAttachment);
          await deleteFile(attachment.filePath);
        }
      }

      // 2. 새 파일 업로드 처리
              if (newFiles.length > 0) {
          for (const { file, attachmentType, description } of newFiles) {
            const saveResult = await saveDRMFile(
              file,
              decryptWithServerAction,
              `techsales-rfq/${techSalesRfqId}`
            );

            if (!saveResult.success) {
              throw new Error(saveResult.error || "파일 저장에 실패했습니다.");
            }

          // DB에 첨부파일 레코드 생성
          const [newAttachment] = await tx.insert(techSalesAttachments).values({
            techSalesRfqId,
            attachmentType,
            fileName: saveResult.fileName!,
            originalFileName: file.name,
            filePath: saveResult.publicPath!,
            fileSize: file.size,
            fileType: file.type || undefined,
            description: description || undefined,
            createdBy,
          }).returning();

          results.uploaded.push(newAttachment);
        }
      }
    });



    // 캐시 무효화
    revalidateTag("techSalesRfqs");
    revalidateTag(`techSalesRfq-${techSalesRfqId}`);
    revalidatePath("/evcp/budgetary-tech-sales-ship");

    return { 
      data: results, 
      error: null,
      message: `${results.uploaded.length}개 업로드, ${results.deleted.length}개 삭제 완료`
    };
  } catch (err) {
    console.error("기술영업 RFQ 첨부파일 일괄 처리 오류:", err);
    return { data: null, error: getErrorMessage(err) };
  }
}

// ========================================
// 메일 발송 관련 함수들
// ========================================

/**
 * 벤더 견적 제출 확인 메일 발송 (벤더용)
 */
export async function sendQuotationSubmittedNotificationToVendor(quotationId: number) {
  try {
    // 견적서 정보 조회 (projectSeries 조인 추가)
    const quotation = await db.query.techSalesVendorQuotations.findFirst({
      where: eq(techSalesVendorQuotations.id, quotationId),
      with: {
        rfq: {
          with: {
            biddingProject: true,
            createdByUser: {
              columns: {
                id: true,
                name: true,
                email: true,
              }
            }
          }
        },
        vendor: {
          columns: {
            id: true,
            vendorName: true,
            vendorCode: true,
          }
        }
      }
    });

    if (!quotation || !quotation.rfq || !quotation.vendor) {
      console.error("견적서 또는 관련 정보를 찾을 수 없습니다");
      return { success: false, error: "견적서 정보를 찾을 수 없습니다" };
    }

    // 벤더 사용자들 조회
    const vendorUsers = await db.query.users.findMany({
      where: eq(users.companyId, quotation.vendor.id),
      columns: {
        id: true,
        email: true,
        name: true,
        language: true
      }
    });

    const vendorEmails = vendorUsers
      .filter(user => user.email)
      .map(user => user.email)
      .join(", ");

    if (!vendorEmails) {
      console.warn(`벤더 ID ${quotation.vendor.id}에 등록된 이메일 주소가 없습니다`);
      return { success: false, error: "벤더 이메일 주소가 없습니다" };
    }

    // RFQ 아이템 정보 조회
    const rfqItemsResult = await getTechSalesRfqItems(quotation.rfq.id);
    const rfqItems = rfqItemsResult.data || [];
    
    // 이메일 컨텍스트 구성 (시리즈 정보 제거, 프로젝트 정보 간소화)
    const emailContext = {
      language: vendorUsers[0]?.language || "ko",
      quotation: {
        id: quotation.id,
        currency: quotation.currency,
        totalPrice: quotation.totalPrice,
        validUntil: quotation.validUntil,
        submittedAt: quotation.submittedAt,
        remark: quotation.remark,
      },
      rfq: {
        id: quotation.rfq.id,
        code: quotation.rfq.rfqCode,
        title: quotation.rfq.description || '',
        projectCode: quotation.rfq.biddingProject?.pspid || '',
        projectName: quotation.rfq.biddingProject?.projNm || '',
        dueDate: quotation.rfq.dueDate,
        materialCode: quotation.rfq.materialCode,
        description: quotation.rfq.remark,
      },
      items: rfqItems.map(item => ({
        itemCode: item.itemCode,
        itemList: item.itemList,
        workType: item.workType,
        shipTypes: item.shipTypes,
        subItemList: item.subItemList,
        itemType: item.itemType,
      })),
      vendor: {
        id: quotation.vendor.id,
        code: quotation.vendor.vendorCode,
        name: quotation.vendor.vendorName,
      },
      project: {
        name: quotation.rfq.biddingProject?.projNm || '',
        sector: quotation.rfq.biddingProject?.sector || '',
        shipCount: quotation.rfq.biddingProject?.projMsrm ? Number(quotation.rfq.biddingProject.projMsrm) : 0,
        ownerName: quotation.rfq.biddingProject?.kunnrNm || '',
        className: quotation.rfq.biddingProject?.cls1Nm || '',
      },
      manager: {
        name: quotation.rfq.createdByUser?.name || '',
        email: quotation.rfq.createdByUser?.email || '',
      },
      systemUrl: process.env.NEXT_PUBLIC_APP_URL || 'http://60.101.108.100/ko/partners',
      companyName: 'Samsung Heavy Industries',
      year: new Date().getFullYear(),
    };

    // 이메일 발송
    await sendEmail({
      to: vendorEmails,
      subject: `[견적 제출 확인] ${quotation.rfq.rfqCode} - 견적 요청`,
      template: 'tech-sales-quotation-submitted-vendor-ko',
      context: emailContext,
    });

    console.log(`벤더 견적 제출 확인 메일 발송 완료: ${vendorEmails}`);
    return { success: true };
  } catch (error) {
    console.error("벤더 견적 제출 확인 메일 발송 오류:", error);
    return { success: false, error: "메일 발송 중 오류가 발생했습니다" };
  }
}

/**
 * 벤더 견적 접수 알림 메일 발송 (담당자용)
 */
export async function sendQuotationSubmittedNotificationToManager(quotationId: number) {
  try {
    // 견적서 정보 조회
    const quotation = await db.query.techSalesVendorQuotations.findFirst({
      where: eq(techSalesVendorQuotations.id, quotationId),
      with: {
        rfq: {
          with: {
            biddingProject: true,
            createdByUser: {
              columns: {
                id: true,
                name: true,
                email: true,
              }
            }
          }
        },
        vendor: {
          columns: {
            id: true,
            vendorName: true,
            vendorCode: true,
          }
        }
      }
    });

    if (!quotation || !quotation.rfq || !quotation.vendor) {
      console.error("견적서 또는 관련 정보를 찾을 수 없습니다");
      return { success: false, error: "견적서 정보를 찾을 수 없습니다" };
    }

    const manager = quotation.rfq.createdByUser;
    if (!manager?.email) {
      console.warn("담당자 이메일 주소가 없습니다");
      return { success: false, error: "담당자 이메일 주소가 없습니다" };
    }

    // RFQ 아이템 정보 조회
    const rfqItemsResult = await getTechSalesRfqItems(quotation.rfq.id);
    const rfqItems = rfqItemsResult.data || [];
    
    // 이메일 컨텍스트 구성 (시리즈 정보 제거, 프로젝트 정보 간소화)
    const emailContext = {
      language: "ko",
      quotation: {
        id: quotation.id,
        currency: quotation.currency,
        totalPrice: quotation.totalPrice,
        validUntil: quotation.validUntil,
        submittedAt: quotation.submittedAt,
        remark: quotation.remark,
      },
      rfq: {
        id: quotation.rfq.id,
        code: quotation.rfq.rfqCode,
        title: quotation.rfq.description || '',
        projectCode: quotation.rfq.biddingProject?.pspid || '',
        projectName: quotation.rfq.biddingProject?.projNm || '',
        dueDate: quotation.rfq.dueDate,
        materialCode: quotation.rfq.materialCode,
        description: quotation.rfq.remark,
      },
      items: rfqItems.map(item => ({
        itemCode: item.itemCode,
        itemList: item.itemList,
        workType: item.workType,
        shipTypes: item.shipTypes,
        subItemList: item.subItemList,
        itemType: item.itemType,
      })),
      vendor: {
        id: quotation.vendor.id,
        code: quotation.vendor.vendorCode,
        name: quotation.vendor.vendorName,
      },
      project: {
        name: quotation.rfq.biddingProject?.projNm || '',
        sector: quotation.rfq.biddingProject?.sector || '',
        shipCount: quotation.rfq.biddingProject?.projMsrm ? Number(quotation.rfq.biddingProject.projMsrm) : 0,
        ownerName: quotation.rfq.biddingProject?.kunnrNm || '',
        className: quotation.rfq.biddingProject?.cls1Nm || '',
      },
      manager: {
        name: manager.name || '',
        email: manager.email,
      },
      systemUrl: process.env.NEXT_PUBLIC_APP_URL || 'http://60.101.108.100/ko/evcp',
      companyName: 'Samsung Heavy Industries',
      year: new Date().getFullYear(),
    };

    // 이메일 발송
    await sendEmail({
      to: manager.email,
      subject: `[견적 접수 알림] ${quotation.vendor.vendorName}에서 ${quotation.rfq.rfqCode} 견적서를 제출했습니다`,
      template: 'tech-sales-quotation-submitted-manager-ko',
      context: emailContext,
    });

    console.log(`담당자 견적 접수 알림 메일 발송 완료: ${manager.email}`);
    return { success: true };
  } catch (error) {
    console.error("담당자 견적 접수 알림 메일 발송 오류:", error);
    return { success: false, error: "메일 발송 중 오류가 발생했습니다" };
  }
}

/**
 * 벤더 견적 선택 알림 메일 발송
 */
export async function sendQuotationAcceptedNotification(quotationId: number) {
  try {
    // 견적서 정보 조회
    const quotation = await db.query.techSalesVendorQuotations.findFirst({
      where: eq(techSalesVendorQuotations.id, quotationId),
      with: {
        rfq: {
          with: {
            biddingProject: true,
            createdByUser: {
              columns: {
                id: true,
                name: true,
                email: true,
              }
            }
          }
        },
        vendor: {
          columns: {
            id: true,
            vendorName: true,
            vendorCode: true,
          }
        }
      }
    });

    if (!quotation || !quotation.rfq || !quotation.vendor) {
      console.error("견적서 또는 관련 정보를 찾을 수 없습니다");
      return { success: false, error: "견적서 정보를 찾을 수 없습니다" };
    }

    // 벤더 사용자들 조회
    const vendorUsers = await db.query.users.findMany({
      where: eq(users.companyId, quotation.vendor.id),
      columns: {
        id: true,
        email: true,
        name: true,
        language: true
      }
    });

    const vendorEmails = vendorUsers
      .filter(user => user.email)
      .map(user => user.email)
      .join(", ");

    if (!vendorEmails) {
      console.warn(`벤더 ID ${quotation.vendor.id}에 등록된 이메일 주소가 없습니다`);
      return { success: false, error: "벤더 이메일 주소가 없습니다" };
    }

    // RFQ 아이템 정보 조회
    const rfqItemsResult = await getTechSalesRfqItems(quotation.rfq.id);
    const rfqItems = rfqItemsResult.data || [];
    
    // 이메일 컨텍스트 구성 (시리즈 정보 제거, 프로젝트 정보 간소화)
    const emailContext = {
      language: vendorUsers[0]?.language || "ko",
      quotation: {
        id: quotation.id,
        currency: quotation.currency,
        totalPrice: quotation.totalPrice,
        validUntil: quotation.validUntil,
        acceptedAt: quotation.acceptedAt,
        remark: quotation.remark,
      },
      rfq: {
        id: quotation.rfq.id,
        code: quotation.rfq.rfqCode,
        title: quotation.rfq.description || '',
        projectCode: quotation.rfq.biddingProject?.pspid || '',
        projectName: quotation.rfq.biddingProject?.projNm || '',
        dueDate: quotation.rfq.dueDate,
        materialCode: quotation.rfq.materialCode,
        description: quotation.rfq.remark,
      },
      items: rfqItems.map(item => ({
        itemCode: item.itemCode,
        itemList: item.itemList,
        workType: item.workType,
        shipTypes: item.shipTypes,
        subItemList: item.subItemList,
        itemType: item.itemType,
      })),
      vendor: {
        id: quotation.vendor.id,
        code: quotation.vendor.vendorCode,
        name: quotation.vendor.vendorName,
      },
      project: {
        name: quotation.rfq.biddingProject?.projNm || '',
        sector: quotation.rfq.biddingProject?.sector || '',
        shipCount: quotation.rfq.biddingProject?.projMsrm ? Number(quotation.rfq.biddingProject.projMsrm) : 0,
        ownerName: quotation.rfq.biddingProject?.kunnrNm || '',
        className: quotation.rfq.biddingProject?.cls1Nm || '',
      },
      manager: {
        name: quotation.rfq.createdByUser?.name || '',
        email: quotation.rfq.createdByUser?.email || '',
      },
      systemUrl: process.env.NEXT_PUBLIC_APP_URL || 'http://60.101.108.100/ko/partners',
      companyName: 'Samsung Heavy Industries',
      year: new Date().getFullYear(),
    };

    // 이메일 발송
    await sendEmail({
      to: vendorEmails,
      subject: `[견적 선택 알림] ${quotation.rfq.rfqCode} - 귀하의 견적이 선택되었습니다`,
      template: 'tech-sales-quotation-accepted-ko',
      context: emailContext,
    });

    console.log(`벤더 견적 선택 알림 메일 발송 완료: ${vendorEmails}`);
    return { success: true };
  } catch (error) {
    console.error("벤더 견적 선택 알림 메일 발송 오류:", error);
    return { success: false, error: "메일 발송 중 오류가 발생했습니다" };
  }
}

// ==================== Vendor Communication 관련 ====================

export interface TechSalesAttachment {
  id: number
  fileName: string
  fileSize: number
  fileType: string | null // <- null 허용
  filePath: string
  uploadedAt: Date
}

export interface TechSalesComment {
  id: number
  rfqId: number
  vendorId: number | null  // null 허용으로 변경
  userId?: number | null   // null 허용으로 변경
  content: string
  isVendorComment: boolean | null  // null 허용으로 변경
  createdAt: Date
  updatedAt: Date
  userName?: string | null  // null 허용으로 변경
  vendorName?: string | null  // null 허용으로 변경
  attachments: TechSalesAttachment[]
  isRead: boolean | null   // null 허용으로 변경
}

/**
 * 특정 RFQ의 벤더별 읽지 않은 메시지 개수를 조회하는 함수
 * 
 * @param rfqId RFQ ID
 * @returns 벤더별 읽지 않은 메시지 개수 (vendorId: count)
 */
export async function getTechSalesUnreadMessageCounts(rfqId: number): Promise<Record<number, number>> {
  try {
    // 벤더가 보낸 읽지 않은 메시지를 벤더별로 카운트
    const unreadCounts = await db
      .select({
        vendorId: techSalesRfqComments.vendorId,
        count: sql<number>`count(*)`,
      })
      .from(techSalesRfqComments)
      .where(
        and(
          eq(techSalesRfqComments.rfqId, rfqId),
          eq(techSalesRfqComments.isVendorComment, true), // 벤더가 보낸 메시지
          eq(techSalesRfqComments.isRead, false), // 읽지 않은 메시지
          sql`${techSalesRfqComments.vendorId} IS NOT NULL` // vendorId가 null이 아닌 것
        )
      )
      .groupBy(techSalesRfqComments.vendorId);

    // Record<number, number> 형태로 변환
    const result: Record<number, number> = {};
    unreadCounts.forEach(item => {
      if (item.vendorId) {
        result[item.vendorId] = item.count;
      }
    });

    return result;
  } catch (error) {
    console.error('techSales 읽지 않은 메시지 개수 조회 오류:', error);
    return {};
  }
}

/**
 * 특정 RFQ와 벤더 간의 커뮤니케이션 메시지를 가져오는 서버 액션
 * 
 * @param rfqId RFQ ID
 * @param vendorId 벤더 ID
 * @returns 코멘트 목록
 */
export async function fetchTechSalesVendorComments(rfqId: number, vendorId?: number): Promise<TechSalesComment[]> {
  if (!vendorId) {
    return []
  }

  try {
    // 인증 확인
    const session = await getServerSession(authOptions);

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

    // 코멘트 쿼리
    const comments = await db.query.techSalesRfqComments.findMany({
      where: and(
        eq(techSalesRfqComments.rfqId, rfqId),
        eq(techSalesRfqComments.vendorId, vendorId)
      ),
      orderBy: [techSalesRfqComments.createdAt],
      with: {
        user: {
          columns: {
            name: true
          }
        },
        vendor: {
          columns: {
            vendorName: true
          }
        },
        attachments: true,
      }
    })

    // 결과 매핑
    return comments.map(comment => ({
      id: comment.id,
      rfqId: comment.rfqId,
      vendorId: comment.vendorId,
      userId: comment.userId || undefined,
      content: comment.content,
      isVendorComment: comment.isVendorComment,
      createdAt: comment.createdAt,
      updatedAt: comment.updatedAt,
      userName: comment.user?.name,
      vendorName: comment.vendor?.vendorName,
      isRead: comment.isRead,
      attachments: comment.attachments.map(att => ({
        id: att.id,
        fileName: att.fileName,
        fileSize: att.fileSize,
        fileType: att.fileType,
        filePath: att.filePath,
        originalFileName: att.originalFileName,
        uploadedAt: att.uploadedAt
      }))
    }))
  } catch (error) {
    console.error('techSales 벤더 코멘트 가져오기 오류:', error)
    throw error
  }
}

/**
 * 코멘트를 읽음 상태로 표시하는 서버 액션
 * 
 * @param rfqId RFQ ID
 * @param vendorId 벤더 ID
 */
export async function markTechSalesMessagesAsRead(rfqId: number, vendorId?: number): Promise<void> {
  if (!vendorId) {
    return
  }

  try {
    // 인증 확인
    const session = await getServerSession(authOptions);

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

    // 벤더가 작성한 읽지 않은 코멘트 업데이트
    await db.update(techSalesRfqComments)
      .set({ isRead: true })
      .where(
        and(
          eq(techSalesRfqComments.rfqId, rfqId),
          eq(techSalesRfqComments.vendorId, vendorId),
          eq(techSalesRfqComments.isVendorComment, true),
          eq(techSalesRfqComments.isRead, false)
        )
      )

    // 캐시 무효화
    revalidateTag(`tech-sales-rfq-${rfqId}-comments`)
  } catch (error) {
    console.error('techSales 메시지 읽음 표시 오류:', error)
    throw error
  }
}

// ==================== RFQ 조선/해양 관련 ====================

/**
 * 기술영업 조선 RFQ 생성 (1:N 관계)
 */
export async function createTechSalesShipRfq(input: {
  biddingProjectId: number;
  itemIds: number[]; // 조선 아이템 ID 배열
  dueDate: Date;
  description?: string;
  createdBy: number;
}) {
  unstable_noStore();  
  try {
    return await db.transaction(async (tx) => {
      // 프로젝트 정보 조회 (유효성 검증)
      const biddingProject = await tx.query.biddingProjects.findFirst({
        where: (biddingProjects, { eq }) => eq(biddingProjects.id, input.biddingProjectId)
      });

      if (!biddingProject) {
        throw new Error(`프로젝트 ID ${input.biddingProjectId}를 찾을 수 없습니다.`);
      }

      // RFQ 코드 생성 (SHIP 타입)
      const rfqCode = await generateRfqCodes(tx, 1);
      
      // RFQ 생성
      const [rfq] = await tx
        .insert(techSalesRfqs)
        .values({
          rfqCode: rfqCode[0],
          biddingProjectId: input.biddingProjectId,
          description: input.description,
          dueDate: input.dueDate,
          status: "RFQ Created",
          rfqType: "SHIP",
          createdBy: input.createdBy,
          updatedBy: input.createdBy,
        })
        .returning({ id: techSalesRfqs.id });

      // 아이템들 추가
      for (const itemId of input.itemIds) {
        await tx
          .insert(techSalesRfqItems)
          .values({
            rfqId: rfq.id,
            itemShipbuildingId: itemId,
            itemType: "SHIP",
          });
      }

      // 캐시 무효화
      revalidateTag("techSalesRfqs");
      revalidatePath("/evcp/budgetary-tech-sales-ship");

      return { data: rfq, error: null };
    });
  } catch (err) {
    console.error("Error creating Ship RFQ:", err);
    return { data: null, error: getErrorMessage(err) };
  }
}

/**
 * 기술영업 해양 Hull RFQ 생성 (1:N 관계)
 */
export async function createTechSalesHullRfq(input: {
  biddingProjectId: number;
  itemIds: number[]; // Hull 아이템 ID 배열
  dueDate: Date;
  description?: string;
  createdBy: number;
}) {
  unstable_noStore();
  console.log('🔍 createTechSalesHullRfq 호출됨:', input);
  
  try {
    return await db.transaction(async (tx) => {
      // 프로젝트 정보 조회 (유효성 검증)
      const biddingProject = await tx.query.biddingProjects.findFirst({
        where: (biddingProjects, { eq }) => eq(biddingProjects.id, input.biddingProjectId)
      });

      if (!biddingProject) {
        throw new Error(`프로젝트 ID ${input.biddingProjectId}를 찾을 수 없습니다.`);
      }

      // RFQ 코드 생성 (HULL 타입)
      const hullRfqCode = await generateRfqCodes(tx, 1);
      
      // RFQ 생성
      const [rfq] = await tx
        .insert(techSalesRfqs)
        .values({
          rfqCode: hullRfqCode[0],
          biddingProjectId: input.biddingProjectId,
          description: input.description,
          dueDate: input.dueDate,
          status: "RFQ Created",
          rfqType: "HULL",
          createdBy: input.createdBy,
          updatedBy: input.createdBy,
        })
        .returning({ id: techSalesRfqs.id });

      // 아이템들 추가
      for (const itemId of input.itemIds) {
        await tx
          .insert(techSalesRfqItems)
          .values({
            rfqId: rfq.id,
            itemOffshoreHullId: itemId,
            itemType: "HULL",
          });
      }

      // 캐시 무효화
      revalidateTag("techSalesRfqs");
      revalidatePath("/evcp/budgetary-tech-sales-hull");

      return { data: rfq, error: null };
    });
  } catch (err) {
    console.error("Error creating Hull RFQ:", err);
    return { data: null, error: getErrorMessage(err) };
  }
}

/**
 * 기술영업 해양 TOP RFQ 생성 (1:N 관계)
 */
export async function createTechSalesTopRfq(input: {
  biddingProjectId: number;
  itemIds: number[]; // TOP 아이템 ID 배열
  dueDate: Date;
  description?: string;
  createdBy: number;
}) {
  unstable_noStore();
  console.log('🔍 createTechSalesTopRfq 호출됨:', input);
  
  try {
    return await db.transaction(async (tx) => {
      // 프로젝트 정보 조회 (유효성 검증)
      const biddingProject = await tx.query.biddingProjects.findFirst({
        where: (biddingProjects, { eq }) => eq(biddingProjects.id, input.biddingProjectId)
      });

      if (!biddingProject) {
        throw new Error(`프로젝트 ID ${input.biddingProjectId}를 찾을 수 없습니다.`);
      }

      // RFQ 코드 생성 (TOP 타입)
      const topRfqCode = await generateRfqCodes(tx, 1);
      
      // RFQ 생성
      const [rfq] = await tx
        .insert(techSalesRfqs)
        .values({
          rfqCode: topRfqCode[0],
          biddingProjectId: input.biddingProjectId,
          description: input.description,
          dueDate: input.dueDate,
          status: "RFQ Created",
          rfqType: "TOP",
          createdBy: input.createdBy,
          updatedBy: input.createdBy,
        })
        .returning({ id: techSalesRfqs.id });

      // 아이템들 추가
      for (const itemId of input.itemIds) {
        await tx
          .insert(techSalesRfqItems)
          .values({
            rfqId: rfq.id,
            itemOffshoreTopId: itemId,
            itemType: "TOP",
          });
      }

      // 캐시 무효화
      revalidateTag("techSalesRfqs");
      revalidatePath("/evcp/budgetary-tech-sales-top");

      return { data: rfq, error: null };
    });
  } catch (err) {
    console.error("Error creating TOP RFQ:", err);
    return { data: null, error: getErrorMessage(err) };
  }
}

/**
 * 조선 RFQ 전용 조회 함수 
 */
export async function getTechSalesShipRfqsWithJoin(input: GetTechSalesRfqsSchema) {
  return getTechSalesRfqsWithJoin({ ...input, rfqType: "SHIP" });
}

/**
 * 해양 TOP RFQ 전용 조회 함수
 */
export async function getTechSalesTopRfqsWithJoin(input: GetTechSalesRfqsSchema) {
  return getTechSalesRfqsWithJoin({ ...input, rfqType: "TOP" });
}

/**
 * 해양 HULL RFQ 전용 조회 함수
 */
export async function getTechSalesHullRfqsWithJoin(input: GetTechSalesRfqsSchema) {
  return getTechSalesRfqsWithJoin({ ...input, rfqType: "HULL" });
}

/**
 * 조선 벤더 견적서 전용 조회 함수
 */
export async function getTechSalesShipVendorQuotationsWithJoin(input: {
  rfqId?: number;
  vendorId?: number;
  search?: string;
  filters?: Filter<typeof techSalesVendorQuotations>[];
  sort?: { id: string; desc: boolean }[];
  page: number;
  perPage: number;
}) {
  return getTechSalesVendorQuotationsWithJoin({ ...input, rfqType: "SHIP" });
}

/**
 * 해양 TOP 벤더 견적서 전용 조회 함수
 */
export async function getTechSalesTopVendorQuotationsWithJoin(input: {
  rfqId?: number;
  vendorId?: number;
  search?: string;
  filters?: Filter<typeof techSalesVendorQuotations>[];
  sort?: { id: string; desc: boolean }[];
  page: number;
  perPage: number;
}) {
  return getTechSalesVendorQuotationsWithJoin({ ...input, rfqType: "TOP" });
}

/**
 * 해양 HULL 벤더 견적서 전용 조회 함수
 */
export async function getTechSalesHullVendorQuotationsWithJoin(input: {
  rfqId?: number;
  vendorId?: number;
  search?: string;
  filters?: Filter<typeof techSalesVendorQuotations>[];
  sort?: { id: string; desc: boolean }[];
  page: number;
  perPage: number;
}) {
  return getTechSalesVendorQuotationsWithJoin({ ...input, rfqType: "HULL" });
}

/** 
 * 기술영업 RFQ의 아이템 목록 조회
 */
export async function getTechSalesRfqItems(rfqId: number) {
  unstable_noStore();
  try {
    const items = await db.query.techSalesRfqItems.findMany({
      where: eq(techSalesRfqItems.rfqId, rfqId),
      with: {
        itemShipbuilding: {
          columns: {
            id: true,
            itemCode: true,
            itemList: true,
            workType: true,
            shipTypes: true,
          }
        },
        itemOffshoreTop: {
          columns: {
            id: true,
            itemCode: true,
            itemList: true,
            workType: true,
            subItemList: true,
          }
        },
        itemOffshoreHull: {
          columns: {
            id: true,
            itemCode: true,
            itemList: true,
            workType: true,
            subItemList: true,
          }
        }
      },
      orderBy: [techSalesRfqItems.id]
    });

    // 아이템 타입에 따라 정보 매핑
    const mappedItems = items.map(item => {
      let itemInfo = null;
      
      switch (item.itemType) {
        case 'SHIP':
          itemInfo = item.itemShipbuilding;
          break;
        case 'TOP':
          itemInfo = item.itemOffshoreTop;
          break;
        case 'HULL':
          itemInfo = item.itemOffshoreHull;
          break;
      }

      return {
        id: item.id,
        rfqId: item.rfqId,
        itemType: item.itemType,
        itemCode: itemInfo?.itemCode || '',
        itemList: itemInfo?.itemList || '',
        workType: itemInfo?.workType || '',
        // 조선이면 shipType, 해양이면 subItemList
        shipTypes: item.itemType === 'SHIP' ? (itemInfo as { shipTypes?: string })?.shipTypes || '' : undefined,
        subItemList: item.itemType !== 'SHIP' ? (itemInfo as { subItemList?: string })?.subItemList || '' : undefined,
      };
    });

    return { data: mappedItems, error: null };
  } catch (err) {
    console.error("Error fetching RFQ items:", err);
    return { data: [], error: getErrorMessage(err) };
  }
}

/**
 * RFQ 아이템들과 매칭되는 후보 벤더들을 찾는 함수
 */
export async function getTechSalesRfqCandidateVendors(rfqId: number) {
  unstable_noStore();
  
  try {
    return await db.transaction(async (tx) => {
      // 1. RFQ 정보 조회 (타입 확인)
      const rfq = await tx.query.techSalesRfqs.findFirst({
        where: eq(techSalesRfqs.id, rfqId),
        columns: {
          id: true,
          rfqType: true
        }
      });

      if (!rfq) {
        return { data: [], error: "RFQ를 찾을 수 없습니다." };
      }

      // 2. RFQ 아이템들 조회
      const rfqItems = await tx.query.techSalesRfqItems.findMany({
        where: eq(techSalesRfqItems.rfqId, rfqId),
        with: {
          itemShipbuilding: true,
          itemOffshoreTop: true,
          itemOffshoreHull: true,
        }
      });

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

      // 3. 아이템 ID들 추출 (타입별로)
      const shipItemIds: number[] = [];
      const topItemIds: number[] = [];
      const hullItemIds: number[] = [];
      
      rfqItems.forEach(item => {
        if (item.itemType === "SHIP" && item.itemShipbuilding?.id) {
          shipItemIds.push(item.itemShipbuilding.id);
        } else if (item.itemType === "TOP" && item.itemOffshoreTop?.id) {
          topItemIds.push(item.itemOffshoreTop.id);
        } else if (item.itemType === "HULL" && item.itemOffshoreHull?.id) {
          hullItemIds.push(item.itemOffshoreHull.id);
        }
      });

      if (shipItemIds.length === 0 && topItemIds.length === 0 && hullItemIds.length === 0) {
        return { data: [], error: null };
      }

      // 4. 각 타입별로 매칭되는 벤더들 조회
      const candidateVendorsMap = new Map();

      // 조선 아이템 매칭 벤더들
      if (shipItemIds.length > 0) {
        const shipVendors = await tx
          .select({
            id: techVendors.id,
            vendorId: techVendors.id,
            vendorName: techVendors.vendorName,
            vendorCode: techVendors.vendorCode,
            country: techVendors.country,
            email: techVendors.email,
            phone: techVendors.phone,
            status: techVendors.status,
            techVendorType: techVendors.techVendorType,
            matchedItemCode: itemShipbuilding.itemCode,
          })
          .from(techVendorPossibleItems)
          .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
          .innerJoin(itemShipbuilding, eq(techVendorPossibleItems.shipbuildingItemId, itemShipbuilding.id))
          .where(
            and(
              inArray(techVendorPossibleItems.shipbuildingItemId, shipItemIds),
              or(
                eq(techVendors.status, "ACTIVE"),
                eq(techVendors.status, "QUOTE_COMPARISON")
              )
            )
          );

        shipVendors.forEach(vendor => {
          const key = vendor.vendorId;
          if (!candidateVendorsMap.has(key)) {
            candidateVendorsMap.set(key, {
              ...vendor,
              matchedItemCodes: [],
              matchedItemCount: 0
            });
          }
          candidateVendorsMap.get(key).matchedItemCodes.push(vendor.matchedItemCode);
          candidateVendorsMap.get(key).matchedItemCount++;
        });
      }

      // 해양 TOP 아이템 매칭 벤더들
      if (topItemIds.length > 0) {
        const topVendors = await tx
          .select({
            id: techVendors.id,
            vendorId: techVendors.id,
            vendorName: techVendors.vendorName,
            vendorCode: techVendors.vendorCode,
            country: techVendors.country,
            email: techVendors.email,
            phone: techVendors.phone,
            status: techVendors.status,
            techVendorType: techVendors.techVendorType,
            matchedItemCode: itemOffshoreTop.itemCode,
          })
          .from(techVendorPossibleItems)
          .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
          .innerJoin(itemOffshoreTop, eq(techVendorPossibleItems.offshoreTopItemId, itemOffshoreTop.id))
          .where(
            and(
              inArray(techVendorPossibleItems.offshoreTopItemId, topItemIds),
              or(
                eq(techVendors.status, "ACTIVE"),
                eq(techVendors.status, "QUOTE_COMPARISON")
              )
            )
          );

        topVendors.forEach(vendor => {
          const key = vendor.vendorId;
          if (!candidateVendorsMap.has(key)) {
            candidateVendorsMap.set(key, {
              ...vendor,
              matchedItemCodes: [],
              matchedItemCount: 0
            });
          }
          candidateVendorsMap.get(key).matchedItemCodes.push(vendor.matchedItemCode);
          candidateVendorsMap.get(key).matchedItemCount++;
        });
      }

      // 해양 HULL 아이템 매칭 벤더들
      if (hullItemIds.length > 0) {
        const hullVendors = await tx
          .select({
            id: techVendors.id,
            vendorId: techVendors.id,
            vendorName: techVendors.vendorName,
            vendorCode: techVendors.vendorCode,
            country: techVendors.country,
            email: techVendors.email,
            phone: techVendors.phone,
            status: techVendors.status,
            techVendorType: techVendors.techVendorType,
            matchedItemCode: itemOffshoreHull.itemCode,
          })
          .from(techVendorPossibleItems)
          .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
          .innerJoin(itemOffshoreHull, eq(techVendorPossibleItems.offshoreHullItemId, itemOffshoreHull.id))
          .where(
            and(
              inArray(techVendorPossibleItems.offshoreHullItemId, hullItemIds),
              or(
                eq(techVendors.status, "ACTIVE"),
                eq(techVendors.status, "QUOTE_COMPARISON")
              )
            )
          );

        hullVendors.forEach(vendor => {
          const key = vendor.vendorId;
          if (!candidateVendorsMap.has(key)) {
            candidateVendorsMap.set(key, {
              ...vendor,
              matchedItemCodes: [],
              matchedItemCount: 0
            });
          }
          candidateVendorsMap.get(key).matchedItemCodes.push(vendor.matchedItemCode);
          candidateVendorsMap.get(key).matchedItemCount++;
        });
      }

      // 5. 결과 정렬 (매칭된 아이템 수 기준 내림차순)
      const candidateVendors = Array.from(candidateVendorsMap.values())
        .sort((a, b) => b.matchedItemCount - a.matchedItemCount);

      return { data: candidateVendors, error: null };
    });
  } catch (err) {
    console.error("Error fetching candidate vendors:", err);
    return { data: [], error: getErrorMessage(err) };
  }
}

/**
 * RFQ 타입에 따른 캐시 무효화 경로 반환
 */
function getTechSalesRevalidationPath(rfqType: "SHIP" | "TOP" | "HULL"): string {
  switch (rfqType) {
    case "SHIP":
      return "/evcp/budgetary-tech-sales-ship";
    case "TOP":
      return "/evcp/budgetary-tech-sales-top";
    case "HULL":
      return "/evcp/budgetary-tech-sales-hull";
    default:
      return "/evcp/budgetary-tech-sales-ship";
  }
}

/**
 * 기술영업 RFQ에 여러 벤더 추가 (techVendors 기반)
 * 벤더 추가 시에는 견적서를 생성하지 않고, RFQ 전송 시에 견적서를 생성
 */
export async function addTechVendorsToTechSalesRfq(input: {
  rfqId: number;
  vendorIds: number[];
  vendorFlags?: Record<string, {
    isCustomerPreferred?: boolean;
    isNewDiscovery?: boolean;
    isProjectApproved?: boolean;
    isShiProposal?: boolean;
  }>;
  createdBy: number;
}) {
  unstable_noStore();
  
  try {
    return await db.transaction(async (tx) => {
      const results = [];
      const errors: string[] = [];

      // 1. RFQ 상태 및 타입 확인
      const rfq = await tx.query.techSalesRfqs.findFirst({
        where: eq(techSalesRfqs.id, input.rfqId),
        columns: {
          id: true,
          status: true,
          rfqType: true,
        }
      });

      if (!rfq) {
        throw new Error("RFQ를 찾을 수 없습니다");
      }
      
      // 2. 각 벤더에 대해 처리 (이미 추가된 벤더는 견적서가 있는지 확인)
      for (const vendorId of input.vendorIds) {
        try {
          // 이미 추가된 벤더인지 확인 (견적서 존재 여부로 확인)
          const existingQuotation = await tx.query.techSalesVendorQuotations.findFirst({
            where: and(
              eq(techSalesVendorQuotations.rfqId, input.rfqId),
              eq(techSalesVendorQuotations.vendorId, vendorId)
            )
          });

          if (existingQuotation) {
            errors.push(`벤더 ID ${vendorId}는 이미 추가되어 있습니다.`);
            continue;
          }

          // 벤더가 실제로 존재하는지 확인
          const vendor = await tx.query.techVendors.findFirst({
            where: eq(techVendors.id, vendorId),
            columns: { id: true, vendorName: true }
          });

          if (!vendor) {
            errors.push(`벤더 ID ${vendorId}를 찾을 수 없습니다.`);
            continue;
          }

          // 🔥 중요: 벤더 추가 시에는 견적서를 생성하지 않고, "Assigned" 상태로만 생성
          // quotation_version은 null로 설정하여 벤더가 실제 견적 제출 시에만 리비전 생성
          const [quotation] = await tx
            .insert(techSalesVendorQuotations)
            .values({
              rfqId: input.rfqId,
              vendorId: vendorId,
              status: "Assigned", // Draft가 아닌 Assigned 상태로 생성
              quotationVersion: null, // 리비전은 견적 제출 시에만 생성
              vendorFlags: input.vendorFlags?.[vendorId.toString()] || null, // 벤더 구분자 정보 추가
              createdBy: input.createdBy,
              updatedBy: input.createdBy,
            })
            .returning({ id: techSalesVendorQuotations.id });

          // 🆕 RFQ의 아이템들을 tech_vendor_possible_items에 추가
          try {
            // RFQ의 아이템들 조회
            const rfqItemsResult = await getTechSalesRfqItems(input.rfqId);

            if (rfqItemsResult.data && rfqItemsResult.data.length > 0) {
              for (const item of rfqItemsResult.data) {
                let vendorPossibleItem = null;
                // 조선: 아이템코드 + 선종으로 조선아이템테이블에서 찾기, 해양: 아이템코드로만 찾기
                if (item.itemType === "SHIP" && item.itemCode && item.shipTypes) {
                  // 조선: itemShipbuilding에서 itemCode, shipTypes로 찾기
                  const shipbuildingItem = await tx.query.itemShipbuilding.findFirst({
                    where: and(
                      eq(itemShipbuilding.itemCode, item.itemCode),
                      eq(itemShipbuilding.shipTypes, item.shipTypes)
                    )
                  });
                  if (shipbuildingItem?.id) {
                    vendorPossibleItem = await tx.query.techVendorPossibleItems.findFirst({
                      where: and(
                        eq(techVendorPossibleItems.vendorId, vendorId),
                        eq(techVendorPossibleItems.shipbuildingItemId, shipbuildingItem.id)
                      )
                    });
                    
                    if (!vendorPossibleItem) {
                      await tx.insert(techVendorPossibleItems).values({
                        vendorId: vendorId,
                        shipbuildingItemId: shipbuildingItem.id,
                      });
                    }
                  }
                } else if (item.itemType === "TOP" && item.itemCode) {
                  // 해양 TOP: itemOffshoreTop에서 itemCode로 찾기
                  const offshoreTopItem = await tx.query.itemOffshoreTop.findFirst({
                    where: eq(itemOffshoreTop.itemCode, item.itemCode)
                  });
                  if (offshoreTopItem?.id) {
                    vendorPossibleItem = await tx.query.techVendorPossibleItems.findFirst({
                      where: and(
                        eq(techVendorPossibleItems.vendorId, vendorId),
                        eq(techVendorPossibleItems.offshoreTopItemId, offshoreTopItem.id)
                      )
                    });
                    
                    if (!vendorPossibleItem) {
                      await tx.insert(techVendorPossibleItems).values({
                        vendorId: vendorId,
                        offshoreTopItemId: offshoreTopItem.id,
                      });
                    }
                  }
                } else if (item.itemType === "HULL" && item.itemCode) {
                  // 해양 HULL: itemOffshoreHull에서 itemCode로 찾기
                  const offshoreHullItem = await tx.query.itemOffshoreHull.findFirst({
                    where: eq(itemOffshoreHull.itemCode, item.itemCode)
                  });
                  if (offshoreHullItem?.id) {
                    vendorPossibleItem = await tx.query.techVendorPossibleItems.findFirst({
                      where: and(
                        eq(techVendorPossibleItems.vendorId, vendorId),
                        eq(techVendorPossibleItems.offshoreHullItemId, offshoreHullItem.id)
                      )
                    });
                    
                    if (!vendorPossibleItem) {
                      await tx.insert(techVendorPossibleItems).values({
                        vendorId: vendorId,
                        offshoreHullItemId: offshoreHullItem.id,
                      });
                    }
                  }
                }
              }
            }
          } catch (possibleItemError) {
            // tech_vendor_possible_items 추가 실패는 전체 실패로 처리하지 않음
            console.warn(`벤더 ${vendorId}의 가능 아이템 추가 실패:`, possibleItemError);
          }

          results.push({ id: quotation.id, vendorId, vendorName: vendor.vendorName });
        } catch (vendorError) {
          console.error(`Error adding vendor ${vendorId}:`, vendorError);
          errors.push(`벤더 ID ${vendorId} 추가 중 오류가 발생했습니다.`);
        }
      }

      // 3. RFQ 상태가 "RFQ Created"이고 성공적으로 추가된 벤더가 있는 경우 상태 업데이트
      if (rfq.status === "RFQ Created" && results.length > 0) {
        await tx.update(techSalesRfqs)
          .set({
            status: "RFQ Vendor Assignned",
            updatedBy: input.createdBy,
            updatedAt: new Date()
          })
          .where(eq(techSalesRfqs.id, input.rfqId));
      }

      // 캐시 무효화 (RFQ 타입에 따른 동적 경로)
      revalidateTag("techSalesRfqs");
      revalidateTag("techSalesVendorQuotations");
      revalidateTag(`techSalesRfq-${input.rfqId}`);
      revalidatePath(getTechSalesRevalidationPath(rfq.rfqType || "SHIP"));

      return { 
        data: results, 
        error: errors.length > 0 ? errors.join(", ") : null,
        successCount: results.length,
        errorCount: errors.length
      };
    });
  } catch (err) {
    console.error("Error adding tech vendors to RFQ:", err);
    return { data: [], error: getErrorMessage(err) };
  }
}

/**
 * 기술영업 RFQ의 벤더 목록 조회 (techVendors 기반)
 */
export async function getTechSalesRfqTechVendors(rfqId: number) {
  unstable_noStore();
  
  try {
    return await db.transaction(async (tx) => {
      const vendors = await tx
        .select({
          id: techSalesVendorQuotations.id,
          vendorId: techVendors.id,
          vendorName: techVendors.vendorName,
          vendorCode: techVendors.vendorCode,
          country: techVendors.country,
          email: techVendors.email,
          phone: techVendors.phone,
          status: techSalesVendorQuotations.status,
          totalPrice: techSalesVendorQuotations.totalPrice,
          currency: techSalesVendorQuotations.currency,
          validUntil: techSalesVendorQuotations.validUntil,
          submittedAt: techSalesVendorQuotations.submittedAt,
          createdAt: techSalesVendorQuotations.createdAt,
        })
        .from(techSalesVendorQuotations)
        .innerJoin(techVendors, eq(techSalesVendorQuotations.vendorId, techVendors.id))
        .where(eq(techSalesVendorQuotations.rfqId, rfqId))
        .orderBy(desc(techSalesVendorQuotations.createdAt));

      return { data: vendors, error: null };
    });
  } catch (err) {
    console.error("Error fetching RFQ tech vendors:", err);
    return { data: [], error: getErrorMessage(err) };
  }
}

/**
 * 기술영업 RFQ에서 기술영업 벤더 제거 (techVendors 기반)
 */
export async function removeTechVendorFromTechSalesRfq(input: {
  rfqId: number;
  vendorId: number;
}) {
  unstable_noStore();
  
  try {
    return await db.transaction(async (tx) => {
      // 해당 벤더의 견적서 상태 확인
      const existingQuotation = await tx.query.techSalesVendorQuotations.findFirst({
        where: and(
          eq(techSalesVendorQuotations.rfqId, input.rfqId),
          eq(techSalesVendorQuotations.vendorId, input.vendorId)
        )
      });

      if (!existingQuotation) {
        return { data: null, error: "해당 벤더가 이 RFQ에 존재하지 않습니다." };
      }

      // Assigned 상태가 아닌 경우 삭제 불가
      if (existingQuotation.status !== "Assigned") {
        return { data: null, error: "Assigned 상태의 벤더만 삭제할 수 있습니다." };
      }

      // 해당 벤더의 견적서 삭제
      const [deletedQuotation] = await tx
        .delete(techSalesVendorQuotations)
        .where(
          and(
            eq(techSalesVendorQuotations.rfqId, input.rfqId),
            eq(techSalesVendorQuotations.vendorId, input.vendorId)
          )
        )
        .returning({ id: techSalesVendorQuotations.id });

      // 캐시 무효화
      revalidateTag("techSalesRfqs");
      revalidateTag("techSalesVendorQuotations");

      return { data: deletedQuotation, error: null };
    });
  } catch (err) {
    console.error("Error removing tech vendor from RFQ:", err);
    return { data: null, error: getErrorMessage(err) };
  }
}

/**
 * 기술영업 RFQ에서 여러 기술영업 벤더 제거 (techVendors 기반)
 */
export async function removeTechVendorsFromTechSalesRfq(input: {
  rfqId: number;
  vendorIds: number[];
}) {
  unstable_noStore();
  
  try {
    return await db.transaction(async (tx) => {
      const results = [];
      const errors: string[] = [];

      for (const vendorId of input.vendorIds) {
        // 해당 벤더의 견적서 상태 확인
        const existingQuotation = await tx.query.techSalesVendorQuotations.findFirst({
          where: and(
            eq(techSalesVendorQuotations.rfqId, input.rfqId),
            eq(techSalesVendorQuotations.vendorId, vendorId)
          )
        });

        if (!existingQuotation) {
          errors.push(`벤더 ID ${vendorId}가 이 RFQ에 존재하지 않습니다.`);
          continue;
        }

        // Assigned 상태가 아닌 경우 삭제 불가
        if (existingQuotation.status !== "Assigned") {
          errors.push(`벤더 ID ${vendorId}는 Assigned 상태가 아니므로 삭제할 수 없습니다.`);
          continue;
        }

        // 해당 벤더의 견적서 삭제
        const [deletedQuotation] = await tx
          .delete(techSalesVendorQuotations)
          .where(
            and(
              eq(techSalesVendorQuotations.rfqId, input.rfqId),
              eq(techSalesVendorQuotations.vendorId, vendorId)
            )
          )
          .returning({ id: techSalesVendorQuotations.id });

        results.push(deletedQuotation);
      }

      // 캐시 무효화
      revalidateTag("techSalesRfqs");
      revalidateTag("techSalesVendorQuotations");

      return { 
        data: results, 
        error: errors.length > 0 ? errors.join(", ") : null,
        successCount: results.length,
        errorCount: errors.length
      };
    });
  } catch (err) {
    console.error("Error removing tech vendors from RFQ:", err);
    return { data: [], error: getErrorMessage(err) };
  }
}

/**
 * 기술영업 벤더 검색
 */
export async function searchTechVendors(searchTerm: string, limit = 100, rfqType?: "SHIP" | "TOP" | "HULL") {
  unstable_noStore();
  
  try {
    // RFQ 타입에 따른 벤더 타입 매핑
    const vendorTypeFilter = rfqType === "SHIP" ? "조선" : 
                            rfqType === "TOP" ? "해양TOP" :
                            rfqType === "HULL" ? "해양HULL" : null;

    const whereConditions = [
      or(
        eq(techVendors.status, "ACTIVE"),
        eq(techVendors.status, "QUOTE_COMPARISON")
      ),
      or(
        ilike(techVendors.vendorName, `%${searchTerm}%`),
        ilike(techVendors.vendorCode, `%${searchTerm}%`)
      )
    ];

    // RFQ 타입이 지정된 경우 벤더 타입 필터링 추가 (컴마 구분 문자열에서 검색)
    if (vendorTypeFilter) {
      whereConditions.push(sql`${techVendors.techVendorType} LIKE ${'%' + vendorTypeFilter + '%'}`);
    }

    const results = await db
      .select({
        id: techVendors.id,
        vendorName: techVendors.vendorName,
        vendorCode: techVendors.vendorCode,
        status: techVendors.status,
        country: techVendors.country,
        techVendorType: techVendors.techVendorType,
      })
      .from(techVendors)
      .where(and(...whereConditions))
      .limit(limit)
      .orderBy(techVendors.vendorName);

    return results;
  } catch (err) {
    console.error("Error searching tech vendors:", err);
    throw new Error(getErrorMessage(err));
  }
}


/**
 * 벤더 견적서 거절 처리 (벤더가 직접 거절)
 */
export async function rejectTechSalesVendorQuotations(input: {
  quotationIds: number[];
  rejectionReason?: string;
}) {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user?.id) {
      throw new Error("인증이 필요합니다.");
    }

    const result = await db.transaction(async (tx) => {
      // 견적서들이 존재하고 벤더가 권한이 있는지 확인
      const quotations = await tx
        .select({
          id: techSalesVendorQuotations.id,
          status: techSalesVendorQuotations.status,
          vendorId: techSalesVendorQuotations.vendorId,
        })
        .from(techSalesVendorQuotations)
        .where(inArray(techSalesVendorQuotations.id, input.quotationIds));

      if (quotations.length !== input.quotationIds.length) {
        throw new Error("일부 견적서를 찾을 수 없습니다.");
      }

      // 이미 거절된 견적서가 있는지 확인
      const alreadyRejected = quotations.filter(q => q.status === "Rejected");
      if (alreadyRejected.length > 0) {
        throw new Error("이미 거절된 견적서가 포함되어 있습니다.");
      }

      // 승인된 견적서가 있는지 확인
      const alreadyAccepted = quotations.filter(q => q.status === "Accepted");
      if (alreadyAccepted.length > 0) {
        throw new Error("이미 승인된 견적서는 거절할 수 없습니다.");
      }

      // 견적서 상태를 거절로 변경
      await tx
        .update(techSalesVendorQuotations)
        .set({
          status: "Rejected",
          rejectionReason: input.rejectionReason || null,
          updatedBy: parseInt(session.user.id),
          updatedAt: new Date(),
        })
        .where(inArray(techSalesVendorQuotations.id, input.quotationIds));

      return { success: true, updatedCount: quotations.length };
    });
    revalidateTag("techSalesRfqs");
    revalidateTag("techSalesVendorQuotations");
    revalidatePath("/partners/techsales/rfq-ship", "page");
    return { 
      success: true, 
      message: `${result.updatedCount}개의 견적서가 거절되었습니다.`,
      data: result
    };
  } catch (error) {
    console.error("견적서 거절 오류:", error);
    return { 
      success: false, 
      error: getErrorMessage(error) 
    };
  }
}

// ==================== Revision 관련 ====================

/**
 * 견적서 revision 히스토리 조회
 */
export async function getTechSalesVendorQuotationRevisions(quotationId: number) {
  try {
    const revisions = await db
      .select({
        id: techSalesVendorQuotationRevisions.id,
        version: techSalesVendorQuotationRevisions.version,
        snapshot: techSalesVendorQuotationRevisions.snapshot,
        changeReason: techSalesVendorQuotationRevisions.changeReason,
        revisionNote: techSalesVendorQuotationRevisions.revisionNote,
        revisedBy: techSalesVendorQuotationRevisions.revisedBy,
        revisedAt: techSalesVendorQuotationRevisions.revisedAt,
        // 수정자 정보 조인
        revisedByName: users.name,
      })
      .from(techSalesVendorQuotationRevisions)
      .leftJoin(users, eq(techSalesVendorQuotationRevisions.revisedBy, users.id))
      .where(eq(techSalesVendorQuotationRevisions.quotationId, quotationId))
      .orderBy(desc(techSalesVendorQuotationRevisions.version));

    return { data: revisions, error: null };
  } catch (error) {
    console.error("견적서 revision 히스토리 조회 오류:", error);
    return { data: null, error: "견적서 히스토리를 조회하는 중 오류가 발생했습니다." };
  }
}

/**
 * 견적서의 현재 버전과 revision 히스토리를 함께 조회 (각 리비전의 첨부파일 포함)
 */
export async function getTechSalesVendorQuotationWithRevisions(quotationId: number) {
  try {
    // 먼저 현재 견적서 조회
    const currentQuotation = await db.query.techSalesVendorQuotations.findFirst({
      where: eq(techSalesVendorQuotations.id, quotationId),
      with: {
        // 벤더 정보와 RFQ 정보도 함께 조회 (필요한 경우)
      }
    });

    if (!currentQuotation) {
      return { data: null, error: "견적서를 찾을 수 없습니다." };
    }

    // 이제 현재 견적서의 정보를 알고 있으므로 병렬로 나머지 정보 조회
    const [revisionsResult, currentAttachments] = await Promise.all([
      getTechSalesVendorQuotationRevisions(quotationId),
      getTechSalesVendorQuotationAttachmentsByRevision(quotationId, currentQuotation.quotationVersion || 0)
    ]);

    // 현재 견적서에 첨부파일 정보 추가
    const currentWithAttachments = {
      ...currentQuotation,
      attachments: currentAttachments.data || []
    };

    // 각 리비전의 첨부파일 정보 추가
    const revisionsWithAttachments = await Promise.all(
      (revisionsResult.data || []).map(async (revision) => {
        const attachmentsResult = await getTechSalesVendorQuotationAttachmentsByRevision(quotationId, revision.version);
        return {
          ...revision,
          attachments: attachmentsResult.data || []
        };
      })
    );

    return {
      data: {
        current: currentWithAttachments,
        revisions: revisionsWithAttachments
      },
      error: null
    };
  } catch (error) {
    console.error("견적서 전체 히스토리 조회 오류:", error);
    return { data: null, error: "견적서 정보를 조회하는 중 오류가 발생했습니다." };
  }
}

/**
 * 견적서 첨부파일 조회 (리비전 ID 기준 오름차순 정렬)
 */
export async function getTechSalesVendorQuotationAttachments(quotationId: number) {
  return unstable_cache(
    async () => {
      try {
        const attachments = await db
          .select({
            id: techSalesVendorQuotationAttachments.id,
            quotationId: techSalesVendorQuotationAttachments.quotationId,
            revisionId: techSalesVendorQuotationAttachments.revisionId,
            fileName: techSalesVendorQuotationAttachments.fileName,
            originalFileName: techSalesVendorQuotationAttachments.originalFileName,
            fileSize: techSalesVendorQuotationAttachments.fileSize,
            fileType: techSalesVendorQuotationAttachments.fileType,
            filePath: techSalesVendorQuotationAttachments.filePath,
            description: techSalesVendorQuotationAttachments.description,
            uploadedBy: techSalesVendorQuotationAttachments.uploadedBy,
            vendorId: techSalesVendorQuotationAttachments.vendorId,
            isVendorUpload: techSalesVendorQuotationAttachments.isVendorUpload,
            createdAt: techSalesVendorQuotationAttachments.createdAt,
            updatedAt: techSalesVendorQuotationAttachments.updatedAt,
          })
          .from(techSalesVendorQuotationAttachments)
          .where(eq(techSalesVendorQuotationAttachments.quotationId, quotationId))
          .orderBy(desc(techSalesVendorQuotationAttachments.createdAt));

        return { data: attachments };
      } catch (error) {
        console.error("견적서 첨부파일 조회 오류:", error);
        return { error: "견적서 첨부파일 조회 중 오류가 발생했습니다." };
      }
    },
    [`quotation-attachments-${quotationId}`],
    {
      revalidate: 60,
      tags: [`quotation-${quotationId}`, "quotation-attachments"],
    }
  )();
}

/**
 * 특정 리비전의 견적서 첨부파일 조회
 */
export async function getTechSalesVendorQuotationAttachmentsByRevision(quotationId: number, revisionId: number) {
  try {
    const attachments = await db
      .select({
        id: techSalesVendorQuotationAttachments.id,
        quotationId: techSalesVendorQuotationAttachments.quotationId,
        revisionId: techSalesVendorQuotationAttachments.revisionId,
        fileName: techSalesVendorQuotationAttachments.fileName,
        originalFileName: techSalesVendorQuotationAttachments.originalFileName,
        fileSize: techSalesVendorQuotationAttachments.fileSize,
        fileType: techSalesVendorQuotationAttachments.fileType,
        filePath: techSalesVendorQuotationAttachments.filePath,
        description: techSalesVendorQuotationAttachments.description,
        uploadedBy: techSalesVendorQuotationAttachments.uploadedBy,
        vendorId: techSalesVendorQuotationAttachments.vendorId,
        isVendorUpload: techSalesVendorQuotationAttachments.isVendorUpload,
        createdAt: techSalesVendorQuotationAttachments.createdAt,
        updatedAt: techSalesVendorQuotationAttachments.updatedAt,
      })
      .from(techSalesVendorQuotationAttachments)
      .where(and(
        eq(techSalesVendorQuotationAttachments.quotationId, quotationId),
        eq(techSalesVendorQuotationAttachments.revisionId, revisionId)
      ))
      .orderBy(desc(techSalesVendorQuotationAttachments.createdAt));

    return { data: attachments };
  } catch (error) {
    console.error("리비전별 견적서 첨부파일 조회 오류:", error);
    return { error: "첨부파일 조회 중 오류가 발생했습니다." };
  }
}


// ==================== Project AVL 관련 ====================

/**
 * Accepted 상태의 Tech Sales Vendor Quotations 조회 (RFQ, Vendor 정보 포함)
 */
export async function getAcceptedTechSalesVendorQuotations(input: {
  search?: string;
  filters?: Filter<typeof techSalesVendorQuotations>[];
  sort?: { id: string; desc: boolean }[];
  page: number;
  perPage: number;
  rfqType?: "SHIP" | "TOP" | "HULL";
}) {
  unstable_noStore();
  
  try {
    const offset = (input.page - 1) * input.perPage;
    
    // 기본 WHERE 조건: status = 'Accepted'만 조회, rfqType이 'SHIP'이 아닌 것만
    const baseConditions = [or(
      eq(techSalesVendorQuotations.status, 'Submitted'),
      eq(techSalesVendorQuotations.status, 'Accepted')
    )
    ];
    
    // 검색 조건 추가
    const searchConditions = [];
    if (input.search) {
      searchConditions.push(
        ilike(techSalesRfqs.rfqCode, `%${input.search}%`),
        ilike(techSalesRfqs.description, `%${input.search}%`),
        ilike(sql`vendors.vendor_name`, `%${input.search}%`),
        ilike(sql`vendors.vendor_code`, `%${input.search}%`)
      );
    }

    // 정렬 조건 변환
    const orderByConditions: OrderByType[] = [];
    if (input.sort?.length) {
      input.sort.forEach((sortItem) => {
        switch (sortItem.id) {
          case "rfqCode":
            orderByConditions.push(sortItem.desc ? desc(techSalesRfqs.rfqCode) : asc(techSalesRfqs.rfqCode));
            break;
          case "description":
            orderByConditions.push(sortItem.desc ? desc(techSalesRfqs.description) : asc(techSalesRfqs.description));
            break;
          case "vendorName":
            orderByConditions.push(sortItem.desc ? desc(sql`vendors.vendor_name`) : asc(sql`vendors.vendor_name`));
            break;
          case "vendorCode":
            orderByConditions.push(sortItem.desc ? desc(sql`vendors.vendor_code`) : asc(sql`vendors.vendor_code`));
            break;
          case "totalPrice":
            orderByConditions.push(sortItem.desc ? desc(techSalesVendorQuotations.totalPrice) : asc(techSalesVendorQuotations.totalPrice));
            break;
          case "acceptedAt":
            orderByConditions.push(sortItem.desc ? desc(techSalesVendorQuotations.acceptedAt) : asc(techSalesVendorQuotations.acceptedAt));
            break;
          default:
            orderByConditions.push(desc(techSalesVendorQuotations.acceptedAt));
        }
      });
    } else {
      orderByConditions.push(desc(techSalesVendorQuotations.acceptedAt));
    }

    // 필터 조건 추가
    const filterConditions = [];
    if (input.filters?.length) {
      const filterWhere = filterColumns({
        table: techSalesVendorQuotations,
        filters: input.filters,
        joinOperator: "and",
      });
      if (filterWhere) {  
        filterConditions.push(filterWhere);
      }
    }

    // RFQ 타입 필터
    if (input.rfqType) {
      filterConditions.push(eq(techSalesRfqs.rfqType, input.rfqType));
    }

    // 모든 조건 결합
    const allConditions = [
      ...baseConditions,
      ...filterConditions,
      ...(searchConditions.length > 0 ? [or(...searchConditions)] : [])
    ];

    const whereCondition = allConditions.length > 1 
      ? and(...allConditions) 
      : allConditions[0];

    // 데이터 조회
    const data = await db
      .select({
        // Quotation 정보
        id: techSalesVendorQuotations.id,
        rfqId: techSalesVendorQuotations.rfqId,
        vendorId: techSalesVendorQuotations.vendorId,
        quotationCode: techSalesVendorQuotations.quotationCode,
        quotationVersion: techSalesVendorQuotations.quotationVersion,
        totalPrice: techSalesVendorQuotations.totalPrice,
        currency: techSalesVendorQuotations.currency,
        validUntil: techSalesVendorQuotations.validUntil,
        status: techSalesVendorQuotations.status,
        remark: techSalesVendorQuotations.remark,
        submittedAt: techSalesVendorQuotations.submittedAt,
        acceptedAt: techSalesVendorQuotations.acceptedAt,
        createdAt: techSalesVendorQuotations.createdAt,
        updatedAt: techSalesVendorQuotations.updatedAt,
        
        // RFQ 정보
        rfqCode: techSalesRfqs.rfqCode,
        rfqType: techSalesRfqs.rfqType,
        description: techSalesRfqs.description,
        dueDate: techSalesRfqs.dueDate,
        rfqStatus: techSalesRfqs.status,
        materialCode: techSalesRfqs.materialCode,
        
        // Vendor 정보
        vendorName: sql<string>`vendors.vendor_name`,
        vendorCode: sql<string | null>`vendors.vendor_code`,
        vendorEmail: sql<string | null>`vendors.email`,
        vendorCountry: sql<string | null>`vendors.country`,
        
        // Project 정보
        projNm: biddingProjects.projNm,
        pspid: biddingProjects.pspid,
        sector: biddingProjects.sector,
      })
      .from(techSalesVendorQuotations)
      .leftJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id))
      .leftJoin(sql`vendors`, eq(techSalesVendorQuotations.vendorId, sql`vendors.id`))
      .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id))
      .where(whereCondition)
      .orderBy(...orderByConditions)
      .limit(input.perPage)
      .offset(offset);

    // 총 개수 조회
    const totalCount = await db
      .select({ count: count() })
      .from(techSalesVendorQuotations)
      .leftJoin(techSalesRfqs, eq(techSalesVendorQuotations.rfqId, techSalesRfqs.id))
      .leftJoin(sql`vendors`, eq(techSalesVendorQuotations.vendorId, sql`vendors.id`))
      .leftJoin(biddingProjects, eq(techSalesRfqs.biddingProjectId, biddingProjects.id))
      .where(whereCondition);

    const total = totalCount[0]?.count ?? 0;
    const pageCount = Math.ceil(total / input.perPage);

    return {
      data,
      pageCount,
      total,
    };

  } catch (error) {
    console.error("getAcceptedTechSalesVendorQuotations 오류:", error);
    throw new Error(`Accepted quotations 조회 실패: ${getErrorMessage(error)}`);
  }
}

export async function getBidProjects(pjtType: 'SHIP' | 'TOP' | 'HULL'): Promise<Project[]> {
  try {
    // 트랜잭션을 사용하여 프로젝트 데이터 조회
    const projectList = await db.transaction(async (tx) => {
      // 기본 쿼리 구성
      const query = tx
        .select({
          id: biddingProjects.id,
          projectCode: biddingProjects.pspid,
          projectName: biddingProjects.projNm,
          pjtType: biddingProjects.pjtType,
        })
        .from(biddingProjects)
        .where(eq(biddingProjects.pjtType, pjtType));
      
      const results = await query.orderBy(biddingProjects.id);
      return results;
    });
    
    // Handle null projectName values and ensure pjtType is not null
    const validProjectList = projectList.map(project => ({
      ...project,
      projectName: project.projectName || '', // Replace null with empty string
      pjtType: project.pjtType as "SHIP" | "TOP" | "HULL" // Type assertion since WHERE filters ensure non-null
    }));

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

/**
 * 여러 벤더의 contact 정보 조회
 */
export async function getTechVendorsContacts(vendorIds: number[]) {
  unstable_noStore();
  try {
    // 직접 조인으로 벤더와 contact 정보 조회
    const contactsWithVendor = await db
      .select({
        contactId: techVendorContacts.id,
        contactName: techVendorContacts.contactName,
        contactPosition: techVendorContacts.contactPosition,
        contactTitle: techVendorContacts.contactTitle,
        contactEmail: techVendorContacts.contactEmail,
        contactPhone: techVendorContacts.contactPhone,
        isPrimary: techVendorContacts.isPrimary,
        vendorId: techVendorContacts.vendorId,
        vendorName: techVendors.vendorName,
        vendorCode: techVendors.vendorCode
      })
      .from(techVendorContacts)
      .leftJoin(techVendors, eq(techVendorContacts.vendorId, techVendors.id))
      .where(inArray(techVendorContacts.vendorId, vendorIds))
      .orderBy(
        asc(techVendorContacts.vendorId),
        desc(techVendorContacts.isPrimary),
        asc(techVendorContacts.contactName)
      );

    // 벤더별로 그룹화
    const contactsByVendor = contactsWithVendor.reduce((acc, row) => {
      const vendorId = row.vendorId;
      if (!acc[vendorId]) {
        acc[vendorId] = {
          vendor: {
            id: vendorId,
            vendorName: row.vendorName || '',
            vendorCode: row.vendorCode || ''
          },
          contacts: []
        };
      }
      acc[vendorId].contacts.push({
        id: row.contactId,
        contactName: row.contactName,
        contactPosition: row.contactPosition,
        contactTitle: row.contactTitle,
        contactEmail: row.contactEmail,
        contactPhone: row.contactPhone,
        isPrimary: row.isPrimary
      });
      return acc;
    }, {} as Record<number, {
      vendor: {
        id: number;
        vendorName: string;
        vendorCode: string | null;
      };
      contacts: Array<{
        id: number;
        contactName: string;
        contactPosition: string | null;
        contactTitle: string | null;
        contactEmail: string;
        contactPhone: string | null;
        isPrimary: boolean;
      }>;
    }>);

    return { data: contactsByVendor, error: null };
  } catch (err) {
    console.error("벤더 contact 조회 오류:", err);
    return { data: {}, error: getErrorMessage(err) };
  }
}

/**
 * quotation별 발송된 담당자 정보 조회
 */
export async function getQuotationContacts(quotationId: number) {
  unstable_noStore();
  try {
    // quotation에 연결된 담당자들 조회
    const quotationContacts = await db
      .select({
        id: techSalesVendorQuotationContacts.id,
        contactId: techSalesVendorQuotationContacts.contactId,
        contactName: techVendorContacts.contactName,
        contactPosition: techVendorContacts.contactPosition,
        contactEmail: techVendorContacts.contactEmail,
        contactPhone: techVendorContacts.contactPhone,
        contactCountry: techVendorContacts.contactCountry,
        isPrimary: techVendorContacts.isPrimary,
        createdAt: techSalesVendorQuotationContacts.createdAt,
      })
      .from(techSalesVendorQuotationContacts)
      .innerJoin(
        techVendorContacts,
        eq(techSalesVendorQuotationContacts.contactId, techVendorContacts.id)
      )
      .where(eq(techSalesVendorQuotationContacts.quotationId, quotationId))
      .orderBy(techSalesVendorQuotationContacts.createdAt);

    return {
      success: true,
      data: quotationContacts,
      error: null,
    };
  } catch (error) {
    console.error("Quotation contacts 조회 오류:", error);
    return {
      success: false,
      data: [],
      error: getErrorMessage(error),
    };
  }
}

/**
 * 견적서 첨부파일 업로드 (클라이언트용)
 */
export async function uploadQuotationAttachments(
  quotationId: number,
  files: File[],
  userId: number
): Promise<{ success: boolean; attachments?: Array<{ fileName: string; originalFileName: string; filePath: string; fileSize: number }>; error?: string }> {
  try {
    const uploadedAttachments = [];

    for (const file of files) {
      const saveResult = await saveFile({
        file,
        directory: `techsales-quotations/${quotationId}`,
        userId: userId.toString(),
      });

      if (!saveResult.success) {
        throw new Error(saveResult.error || '파일 저장에 실패했습니다.');
      }

      uploadedAttachments.push({
        fileName: saveResult.fileName!, // 해시된 파일명 (저장용)
        originalFileName: saveResult.originalName!, // 원본 파일명 (표시용)
        filePath: saveResult.publicPath!,
        fileSize: file.size,
      });
    }

    return {
      success: true,
      attachments: uploadedAttachments
    };
  } catch (error) {
    console.error('견적서 첨부파일 업로드 오류:', error);
    return {
      success: false,
      error: error instanceof Error ? error.message : '파일 업로드 중 오류가 발생했습니다.'
    };
  }
}

/**
 * Update SHI Comment (revisionNote) for the current revision of a quotation.
 * Only the revisionNote is updated in the tech_sales_vendor_quotation_revisions table.
 */
export async function updateSHIComment(revisionId: number, revisionNote: string) {
  try {
    const updatedRevision = await db
      .update(techSalesVendorQuotationRevisions)
      .set({
        revisionNote: revisionNote,
      })
      .where(eq(techSalesVendorQuotationRevisions.id, revisionId))
      .returning();
      
    if (updatedRevision.length === 0) {
      return { data: null, error: "revision을 업데이트할 수 없습니다." };
    }
    
    return { data: updatedRevision[0], error: null };
  } catch (error) {
    console.error("SHI Comment 업데이트 중 오류:", error);
    return { data: null, error: "SHI Comment 업데이트 중 오류가 발생했습니다." };
  }
}

// RFQ 단일 조회 함수 추가
export async function getTechSalesRfqById(id: number) {
  try {
    const rfq = await db.query.techSalesRfqs.findFirst({
      where: eq(techSalesRfqs.id, id),
    });
    const project = await db
      .select({
        id: biddingProjects.id,
        projectCode: biddingProjects.pspid,
        projectName: biddingProjects.projNm,
        pjtType: biddingProjects.pjtType,
        ptypeNm: biddingProjects.ptypeNm,
        projMsrm: biddingProjects.projMsrm,
      })
        .from(biddingProjects)
        .where(eq(biddingProjects.id, rfq?.biddingProjectId ?? 0));
    
    if (!rfq) {
      return { data: null, error: "RFQ를 찾을 수 없습니다." };
    }
    
    return { data: { ...rfq, project }, error: null };
  } catch (err) {
    console.error("Error fetching RFQ:", err);
    return { data: null, error: getErrorMessage(err) };
  }
}

// RFQ 업데이트 함수 수정 (description으로 통일)
export async function updateTechSalesRfq(data: {
  id: number;
  description: string;
  dueDate: Date;
  updatedBy: number;
}) {
  try {
    return await db.transaction(async (tx) => {
      const rfq = await tx.query.techSalesRfqs.findFirst({
        where: eq(techSalesRfqs.id, data.id),
      });
      
      if (!rfq) {
        return { data: null, error: "RFQ를 찾을 수 없습니다." };
      }
      
      const [updatedRfq] = await tx
        .update(techSalesRfqs)
        .set({
          description: data.description, // description 필드로 업데이트
          dueDate: data.dueDate,
          updatedAt: new Date(),
        })
        .where(eq(techSalesRfqs.id, data.id))
        .returning();
      
      revalidateTag("techSalesRfqs");
      revalidatePath(getTechSalesRevalidationPath(rfq.rfqType || "SHIP"));
      
      return { data: updatedRfq, error: null };
    });
  } catch (err) {
    console.error("Error updating RFQ:", err);
    return { data: null, error: getErrorMessage(err) };
  }
}