"use server";

import db from "@/db/db";
import { eq, and, inArray,ne } from "drizzle-orm";
import {
  rfqsLast,
  rfqLastDetails,
  rfqPrItems,
  rfqLastVendorResponses,
  rfqLastVendorQuotationItems,
  vendors,
  paymentTerms,
  incoterms,vendorSelections
} from "@/db/schema";
import { revalidatePath } from "next/cache";
import { getServerSession } from "next-auth/next"
import { authOptions } from "@/app/api/auth/[...nextauth]/route"

// ===== 타입 정의 =====

export interface ComparisonData {
  rfqInfo: {
    id: number;
    rfqCode: string;
    rfqTitle: string;
    rfqType: string;
    projectCode?: string;
    projectName?: string;
    dueDate: Date | null;
    packageNo?: string;
    packageName?: string;
  };
  vendors: VendorComparison[];
  prItems: PrItemComparison[];
  summary: {
    lowestBidder: string;
    highestBidder: string;
    priceRange: {
      min: number;
      max: number;
      average: number;
    };
    currency: string;
  };
}

export interface VendorComparison {
  vendorId: number;
  vendorName: string;
  vendorCode: string;
  vendorCountry?: string;
  
  // 응답 정보
  responseId: number;
  participationStatus: string;
  responseStatus: string;
  submittedAt: Date | null;
  
  // 가격 정보
  totalAmount: number;
  currency: string;
  rank?: number;
  priceVariance?: number;
  
  // 구매자 제시 조건
  buyerConditions: {
    currency: string;
    paymentTermsCode: string;
    paymentTermsDesc?: string;
    incotermsCode: string;
    incotermsDesc?: string;
    deliveryDate: Date | null;
    contractDuration?: string;
    taxCode?: string;
    placeOfShipping?: string;
    placeOfDestination?: string;
    firstYn: boolean;
    firstDescription?: string;
    sparepartYn: boolean;
    sparepartDescription?: string;
    materialPriceRelatedYn: boolean;
  };
  
  // 벤더 제안 조건
  vendorConditions: {
    currency?: string;
    paymentTermsCode?: string;
    paymentTermsDesc?: string;
    incotermsCode?: string;
    incotermsDesc?: string;
    deliveryDate?: Date | null;
    contractDuration?: string;
    taxCode?: string;
    placeOfShipping?: string;
    placeOfDestination?: string;
    firstAcceptance?: "수용" | "부분수용" | "거부";
    firstDescription?: string;
    sparepartAcceptance?: "수용" | "부분수용" | "거부";
    sparepartDescription?: string;
    materialPriceRelatedYn?: boolean;
    materialPriceRelatedReason?: string;
  };
  
  // 조건 차이 분석
  conditionDifferences: {
    hasDifferences: boolean;
    differences: string[];
    criticalDifferences: string[];
  };
  
  // 비고
  generalRemark?: string;
  technicalProposal?: string;
  
  // 선정 관련 정보
  isSelected?: boolean;
  selectionDate?: Date | null;
  selectionReason?: string;
  selectedBy?: number;
  selectedByName?: string;
  selectionApprovalStatus?: "대기" | "승인" | "반려" | null;
  selectionApprovedBy?: number;
  selectionApprovedAt?: Date | null;
  selectionApprovalComment?: string;
  
  // 계약 관련 정보 추가
  contractStatus?: string;
  contractNo?: string;
  contractCreatedAt?: Date | null;
}

export interface PrItemComparison {
  prItemId: number;
  prNo: string;
  prItem: string;
  materialCode: string;
  materialDescription: string;
  requestedQuantity: number;
  uom: string;
  requestedDeliveryDate: Date | null;
  
  vendorQuotes: {
    vendorId: number;
    vendorName: string;
    unitPrice: number;
    totalPrice: number;
    currency: string;
    quotedQuantity: number;
    deliveryDate?: Date | null;
    leadTime?: number;
    manufacturer?: string;
    modelNo?: string;
    technicalCompliance: boolean;
    alternativeProposal?: string;
    itemRemark?: string;
    priceRank?: number;
  }[];
  
  priceAnalysis: {
    lowestPrice: number;
    highestPrice: number;
    averagePrice: number;
    priceVariance: number;
  };
}

// ===== 메인 조회 함수 =====

export async function getComparisonData(
  rfqId: number,
  vendorIds: number[]
): Promise<ComparisonData | null> {
  try {
    // 1. RFQ 기본 정보 조회
    const rfqData = await db
      .select({
        id: rfqsLast.id,
        rfqCode: rfqsLast.rfqCode,
        rfqTitle: rfqsLast.rfqTitle,
        rfqType: rfqsLast.rfqType,
        dueDate: rfqsLast.dueDate,
        packageNo: rfqsLast.packageNo,
        packageName: rfqsLast.packageName,
      })
      .from(rfqsLast)
      .where(eq(rfqsLast.id, rfqId))
      .limit(1);

    if (!rfqData[0]) return null;

    // 2. 벤더별 정보, 응답, 선정 정보 조회
    const vendorData = await db
      .select({
        // 벤더 정보
        vendorId: vendors.id,
        vendorName: vendors.vendorName,
        vendorCode: vendors.vendorCode,
        vendorCountry: vendors.country,
        
        // RFQ Details (구매자 조건)
        detailId: rfqLastDetails.id,
        buyerCurrency: rfqLastDetails.currency,
        buyerPaymentTermsCode: rfqLastDetails.paymentTermsCode,
        buyerIncotermsCode: rfqLastDetails.incotermsCode,
        buyerIncotermsDetail: rfqLastDetails.incotermsDetail,
        buyerDeliveryDate: rfqLastDetails.deliveryDate,
        buyerContractDuration: rfqLastDetails.contractDuration,
        buyerTaxCode: rfqLastDetails.taxCode,
        buyerPlaceOfShipping: rfqLastDetails.placeOfShipping,
        buyerPlaceOfDestination: rfqLastDetails.placeOfDestination,
        buyerFirstYn: rfqLastDetails.firstYn,
        buyerFirstDescription: rfqLastDetails.firstDescription,
        buyerSparepartYn: rfqLastDetails.sparepartYn,
        buyerSparepartDescription: rfqLastDetails.sparepartDescription,
        buyerMaterialPriceRelatedYn: rfqLastDetails.materialPriceRelatedYn,
        
        // 선정 관련 정보
        isSelected: rfqLastDetails.isSelected,
        selectionDate: rfqLastDetails.selectionDate,
        selectionReason: rfqLastDetails.selectionReason,
        selectedBy: rfqLastDetails.selectedBy,
        selectionApprovalStatus: rfqLastDetails.selectionApprovalStatus,
        selectionApprovedBy: rfqLastDetails.selectionApprovedBy,
        selectionApprovedAt: rfqLastDetails.selectionApprovedAt,
        selectionApprovalComment: rfqLastDetails.selectionApprovalComment,
        
        // 계약 관련 정보
        contractStatus: rfqLastDetails.contractStatus,
        contractNo: rfqLastDetails.contractNo,
        contractCreatedAt: rfqLastDetails.contractCreatedAt,
        
        // 벤더 응답
        responseId: rfqLastVendorResponses.id,
        participationStatus: rfqLastVendorResponses.participationStatus,
        responseStatus: rfqLastVendorResponses.status,
        submittedAt: rfqLastVendorResponses.submittedAt,
        totalAmount: rfqLastVendorResponses.totalAmount,
        responseCurrency: rfqLastVendorResponses.currency,
        
        // 벤더 제안 조건
        vendorCurrency: rfqLastVendorResponses.vendorCurrency,
        vendorPaymentTermsCode: rfqLastVendorResponses.vendorPaymentTermsCode,
        vendorIncotermsCode: rfqLastVendorResponses.vendorIncotermsCode,
        vendorIncotermsDetail: rfqLastVendorResponses.vendorIncotermsDetail,
        vendorDeliveryDate: rfqLastVendorResponses.vendorDeliveryDate,
        vendorContractDuration: rfqLastVendorResponses.vendorContractDuration,
        vendorTaxCode: rfqLastVendorResponses.vendorTaxCode,
        vendorPlaceOfShipping: rfqLastVendorResponses.vendorPlaceOfShipping,
        vendorPlaceOfDestination: rfqLastVendorResponses.vendorPlaceOfDestination,
        
        // 추가 조건 응답
        vendorFirstAcceptance: rfqLastVendorResponses.vendorFirstAcceptance,
        vendorFirstDescription: rfqLastVendorResponses.vendorFirstDescription,
        vendorSparepartAcceptance: rfqLastVendorResponses.vendorSparepartAcceptance,
        vendorSparepartDescription: rfqLastVendorResponses.vendorSparepartDescription,
        vendorMaterialPriceRelatedYn: rfqLastVendorResponses.vendorMaterialPriceRelatedYn,
        vendorMaterialPriceRelatedReason: rfqLastVendorResponses.vendorMaterialPriceRelatedReason,
        
        // 비고
        generalRemark: rfqLastVendorResponses.generalRemark,
        technicalProposal: rfqLastVendorResponses.technicalProposal,
      })
      .from(vendors)
      .innerJoin(
        rfqLastDetails,
        and(
          eq(rfqLastDetails.vendorsId, vendors.id),
          eq(rfqLastDetails.rfqsLastId, rfqId),
          eq(rfqLastDetails.isLatest, true)
        )
      )
      .leftJoin(
        rfqLastVendorResponses,
        and(
          eq(rfqLastVendorResponses.vendorId, vendors.id),
          eq(rfqLastVendorResponses.rfqsLastId, rfqId),
          eq(rfqLastVendorResponses.isLatest, true)
        )
      )
      .where(inArray(vendors.id, vendorIds));

    // 3. 선정자 이름 조회 (선정된 업체가 있는 경우)
    const selectedVendor = vendorData.find(v => v.isSelected);
    let selectedByName = "";
    if (selectedVendor?.selectedBy) {
      const [user] = await db
        .select({ name: users.name })
        .from(users)
        .where(eq(users.id, selectedVendor.selectedBy))
        .limit(1);
      selectedByName = user?.name || "";
    }

    // 4. Payment Terms와 Incoterms 설명 조회
    const paymentTermsData = await db
      .select({
        code: paymentTerms.code,
        description: paymentTerms.description,
      })
      .from(paymentTerms);

    const incotermsData = await db
      .select({
        code: incoterms.code,
        description: incoterms.description,
      })
      .from(incoterms);

    const paymentTermsMap = new Map(
      paymentTermsData.map(pt => [pt.code, pt.description])
    );
    const incotermsMap = new Map(
      incotermsData.map(ic => [ic.code, ic.description])
    );

    // 5. PR Items 조회
    const prItems = await db
      .select({
        id: rfqPrItems.id,
        prNo: rfqPrItems.prNo,
        prItem: rfqPrItems.prItem,
        materialCode: rfqPrItems.materialCode,
        materialDescription: rfqPrItems.materialDescription,
        quantity: rfqPrItems.quantity,
        uom: rfqPrItems.uom,
        deliveryDate: rfqPrItems.deliveryDate,
      })
      .from(rfqPrItems)
      .where(eq(rfqPrItems.rfqsLastId, rfqId));

    // 6. 벤더별 견적 아이템 조회
    const quotationItems = await db
      .select({
        vendorResponseId: rfqLastVendorQuotationItems.vendorResponseId,
        prItemId: rfqLastVendorQuotationItems.rfqPrItemId,
        unitPrice: rfqLastVendorQuotationItems.unitPrice,
        totalPrice: rfqLastVendorQuotationItems.totalPrice,
        currency: rfqLastVendorQuotationItems.currency,
        quantity: rfqLastVendorQuotationItems.quantity,
        deliveryDate: rfqLastVendorQuotationItems.vendorDeliveryDate,
        leadTime: rfqLastVendorQuotationItems.leadTime,
        manufacturer: rfqLastVendorQuotationItems.manufacturer,
        modelNo: rfqLastVendorQuotationItems.modelNo,
        technicalCompliance: rfqLastVendorQuotationItems.technicalCompliance,
        alternativeProposal: rfqLastVendorQuotationItems.alternativeProposal,
        itemRemark: rfqLastVendorQuotationItems.itemRemark,
      })
      .from(rfqLastVendorQuotationItems)
      .where(
        inArray(
          rfqLastVendorQuotationItems.vendorResponseId,
          vendorData.map(v => v.responseId).filter(id => id != null)
        )
      );

    // 7. 데이터 가공 및 분석
    const validAmounts = vendorData
      .map(v => v.totalAmount)
      .filter(a => a != null && a > 0);
    
    const minAmount = Math.min(...validAmounts);
    const maxAmount = Math.max(...validAmounts);
    const avgAmount = validAmounts.reduce((a, b) => a + b, 0) / validAmounts.length;

    // 8. 벤더별 비교 데이터 구성
    const vendorComparisons: VendorComparison[] = vendorData.map((v, index) => {
      const differences: string[] = [];
      const criticalDifferences: string[] = [];

      // 조건 차이 분석
      if (v.vendorCurrency && v.vendorCurrency !== v.buyerCurrency) {
        criticalDifferences.push(`통화: ${v.buyerCurrency} → ${v.vendorCurrency}`);
      }
      
      if (v.vendorPaymentTermsCode && v.vendorPaymentTermsCode !== v.buyerPaymentTermsCode) {
        differences.push(`지급조건: ${v.buyerPaymentTermsCode} → ${v.vendorPaymentTermsCode}`);
      }
      
      if (v.vendorIncotermsCode && v.vendorIncotermsCode !== v.buyerIncotermsCode) {
        differences.push(`인코텀즈: ${v.buyerIncotermsCode} → ${v.vendorIncotermsCode}`);
      }
      
      if (v.vendorDeliveryDate && v.buyerDeliveryDate) {
        const buyerDate = new Date(v.buyerDeliveryDate);
        const vendorDate = new Date(v.vendorDeliveryDate);
        if (vendorDate > buyerDate) {
          criticalDifferences.push(`납기: ${Math.ceil((vendorDate.getTime() - buyerDate.getTime()) / (1000 * 60 * 60 * 24))}일 지연`);
        }
      }

      if (v.vendorFirstAcceptance === "거부" && v.buyerFirstYn) {
        criticalDifferences.push("초도품 거부");
      }
      
      if (v.vendorSparepartAcceptance === "거부" && v.buyerSparepartYn) {
        criticalDifferences.push("스페어파트 거부");
      }

      return {
        vendorId: v.vendorId,
        vendorName: v.vendorName,
        vendorCode: v.vendorCode,
        vendorCountry: v.vendorCountry,
        
        responseId: v.responseId || 0,
        participationStatus: v.participationStatus || "미응답",
        responseStatus: v.responseStatus || "대기중",
        submittedAt: v.submittedAt,
        
        totalAmount: v.totalAmount || 0,
        currency: v.responseCurrency || v.buyerCurrency || "USD",
        rank: 0, // 나중에 계산
        priceVariance: v.totalAmount ? ((v.totalAmount - avgAmount) / avgAmount) * 100 : 0,
        
        buyerConditions: {
          currency: v.buyerCurrency || "USD",
          paymentTermsCode: v.buyerPaymentTermsCode || "",
          paymentTermsDesc: paymentTermsMap.get(v.buyerPaymentTermsCode || ""),
          incotermsCode: v.buyerIncotermsCode || "",
          incotermsDesc: incotermsMap.get(v.buyerIncotermsCode || ""),
          deliveryDate: v.buyerDeliveryDate,
          contractDuration: v.buyerContractDuration,
          taxCode: v.buyerTaxCode,
          placeOfShipping: v.buyerPlaceOfShipping,
          placeOfDestination: v.buyerPlaceOfDestination,
          firstYn: v.buyerFirstYn || false,
          firstDescription: v.buyerFirstDescription,
          sparepartYn: v.buyerSparepartYn || false,
          sparepartDescription: v.buyerSparepartDescription,
          materialPriceRelatedYn: v.buyerMaterialPriceRelatedYn || false,
        },
        
        vendorConditions: {
          currency: v.vendorCurrency,
          paymentTermsCode: v.vendorPaymentTermsCode,
          paymentTermsDesc: paymentTermsMap.get(v.vendorPaymentTermsCode || ""),
          incotermsCode: v.vendorIncotermsCode,
          incotermsDesc: incotermsMap.get(v.vendorIncotermsCode || ""),
          deliveryDate: v.vendorDeliveryDate,
          contractDuration: v.vendorContractDuration,
          taxCode: v.vendorTaxCode,
          placeOfShipping: v.vendorPlaceOfShipping,
          placeOfDestination: v.vendorPlaceOfDestination,
          firstAcceptance: v.vendorFirstAcceptance,
          firstDescription: v.vendorFirstDescription,
          sparepartAcceptance: v.vendorSparepartAcceptance,
          sparepartDescription: v.vendorSparepartDescription,
          materialPriceRelatedYn: v.vendorMaterialPriceRelatedYn,
          materialPriceRelatedReason: v.vendorMaterialPriceRelatedReason,
        },
        
        conditionDifferences: {
          hasDifferences: differences.length > 0 || criticalDifferences.length > 0,
          differences,
          criticalDifferences,
        },
        
        generalRemark: v.generalRemark,
        technicalProposal: v.technicalProposal,
        
        // 선정 관련 정보
        isSelected: v.isSelected || false,
        selectionDate: v.selectionDate,
        selectionReason: v.selectionReason,
        selectedBy: v.selectedBy,
        selectedByName: v.isSelected ? selectedByName : undefined,
        selectionApprovalStatus: v.selectionApprovalStatus,
        selectionApprovedBy: v.selectionApprovedBy,
        selectionApprovedAt: v.selectionApprovedAt,
        selectionApprovalComment: v.selectionApprovalComment,
        
        // 계약 관련 정보
        contractStatus: v.contractStatus,
        contractNo: v.contractNo,
        contractCreatedAt: v.contractCreatedAt,
      };
    });

    // 9. 가격 순위 계산
    vendorComparisons.sort((a, b) => a.totalAmount - b.totalAmount);
    vendorComparisons.forEach((v, index) => {
      v.rank = index + 1;
    });

    // 10. PR 아이템별 비교 데이터 구성
    const prItemComparisons: PrItemComparison[] = prItems.map(item => {
      const itemQuotes = quotationItems
        .filter(q => q.prItemId === item.id)
        .map(q => {
          const vendor = vendorData.find(v => v.responseId === q.vendorResponseId);
          return {
            vendorId: vendor?.vendorId || 0,
            vendorName: vendor?.vendorName || "",
            unitPrice: q.unitPrice || 0,
            totalPrice: q.totalPrice || 0,
            currency: q.currency || "USD",
            quotedQuantity: q.quantity || 0,
            deliveryDate: q.deliveryDate,
            leadTime: q.leadTime,
            manufacturer: q.manufacturer,
            modelNo: q.modelNo,
            technicalCompliance: q.technicalCompliance || true,
            alternativeProposal: q.alternativeProposal,
            itemRemark: q.itemRemark,
            priceRank: 0,
          };
        });

      // 아이템별 가격 순위
      itemQuotes.sort((a, b) => a.unitPrice - b.unitPrice);
      itemQuotes.forEach((q, index) => {
        q.priceRank = index + 1;
      });

      const unitPrices = itemQuotes.map(q => q.unitPrice);
      const avgPrice = unitPrices.reduce((a, b) => a + b, 0) / unitPrices.length || 0;
      const variance = Math.sqrt(
        unitPrices.reduce((sum, price) => sum + Math.pow(price - avgPrice, 2), 0) / unitPrices.length
      );

      return {
        prItemId: item.id,
        prNo: item.prNo || "",
        prItem: item.prItem || "",
        materialCode: item.materialCode || "",
        materialDescription: item.materialDescription || "",
        requestedQuantity: item.quantity || 0,
        uom: item.uom || "",
        requestedDeliveryDate: item.deliveryDate,
        vendorQuotes: itemQuotes,
        priceAnalysis: {
          lowestPrice: Math.min(...unitPrices) || 0,
          highestPrice: Math.max(...unitPrices) || 0,
          averagePrice: avgPrice,
          priceVariance: variance,
        },
      };
    });

    console.log({
      rfqInfo: rfqData[0],
      vendors: vendorComparisons,
      prItems: prItemComparisons,
      summary: {
        lowestBidder: vendorComparisons[0]?.vendorName || "",
        highestBidder: vendorComparisons[vendorComparisons.length - 1]?.vendorName || "",
        priceRange: {
          min: minAmount,
          max: maxAmount,
          average: avgAmount,
        },
        currency: vendorComparisons[0]?.currency || "USD",
      },
    });

    // 11. 최종 데이터 반환
    return {
      rfqInfo: rfqData[0],
      vendors: vendorComparisons,
      prItems: prItemComparisons,
      summary: {
        lowestBidder: vendorComparisons[0]?.vendorName || "",
        highestBidder: vendorComparisons[vendorComparisons.length - 1]?.vendorName || "",
        priceRange: {
          min: minAmount,
          max: maxAmount,
          average: avgAmount,
        },
        currency: vendorComparisons[0]?.currency || "USD",
      },
    };
  } catch (error) {
    console.error("견적 비교 데이터 조회 실패:", error);
    return null;
  }
}

interface SelectVendorParams {
  rfqId: number;
  vendorId: number;
  vendorName: string;
  vendorCode: string;
  totalAmount: number;
  currency: string;
  selectionReason: string;
  priceRank: number;
  hasConditionDifferences: boolean;
  criticalDifferences: string[];
  userId: number; // 현재 사용자 ID
}

export async function selectVendor(params: SelectVendorParams) {
  try {
    // 트랜잭션 시작
    const result = await db.transaction(async (tx) => {
      // 1. RFQ 상태 확인
      const [rfq] = await tx
        .select()
        .from(rfqsLast)
        .where(eq(rfqsLast.id, params.rfqId));

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

      if (rfq.status === "최종업체선정") {
        throw new Error("이미 업체가 선정된 RFQ입니다.");
      }

      // 2. 기존에 선정된 업체가 있다면 선정 해제
      await tx
        .update(rfqLastDetails)
        .set({ 
          isSelected: false,
          updatedAt: new Date(),
          updatedBy: params.userId
        })
        .where(
          and(
            eq(rfqLastDetails.rfqsLastId, params.rfqId),
            eq(rfqLastDetails.isSelected, true)
          )
        );

      // 3. 새로운 업체 선정
      const [selection] = await tx
        .update(rfqLastDetails)
        .set({
          isSelected: true,
          selectionDate: new Date(),
          selectionReason: params.selectionReason,
          selectedBy: params.userId,
          totalAmount: params.totalAmount.toString(),
          priceRank: params.priceRank,
          updatedAt: new Date(),
          updatedBy: params.userId,
        })
        .where(
          and(
            eq(rfqLastDetails.rfqsLastId, params.rfqId),
            eq(rfqLastDetails.vendorsId, params.vendorId),
            eq(rfqLastDetails.isLatest, true)
          )
        )
        .returning();

      if (!selection) {
        throw new Error("업체 견적 정보를 찾을 수 없습니다.");
      }

      // 4. RFQ 상태 업데이트
      await tx
        .update(rfqsLast)
        .set({
          status: "최종업체선정",
          updatedAt: new Date(),
        })
        .where(eq(rfqsLast.id, params.rfqId));

      // 5. 다른 업체들의 견적은 미선정 상태로 명시적 업데이트
      await tx
        .update(rfqLastDetails)
        .set({
          isSelected: false,
          updatedAt: new Date(),
        })
        .where(
          and(
            eq(rfqLastDetails.rfqsLastId, params.rfqId),
            eq(rfqLastDetails.isLatest, true),
            // NOT equal to selected vendor
            // Drizzle에서는 ne (not equal) 연산자를 사용
            ne(rfqLastDetails.vendorsId, params.vendorId)
          )
        );

      return selection;
    });

    // 캐시 무효화
    revalidatePath(`/evcp/rfq-last/${params.rfqId}`);
    revalidatePath("/evcp/rfq");
    
    return {
      success: true,
      data: result,
      redirectUrl: `/evcp/rfq-last/${params.rfqId}/selection-complete`
    };

  } catch (error) {
    console.error("업체 선정 오류:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "업체 선정 중 오류가 발생했습니다."
    };
  }
}

// 업체 선정 취소
export async function cancelVendorSelection(rfqId: number, cancelReason: string) {
  try {
    const session = await getServerSession(authOptions)
    if (!session?.user) {
      throw new Error("인증이 필요합니다.")
    }

    const userId = Number(session.user.id)


    await db.transaction(async (tx) => {
      // 선정 정보 업데이트 (취소 사유 기록)
      const [cancelled] = await tx
        .update(rfqLastDetails)
        .set({
          isSelected: false,
          selectionDate: null,
          selectionReason: null,
          selectedBy: null,
          cancelReason: cancelReason,
          selectionApprovalStatus: null,
          selectionApprovedBy: null,
          selectionApprovedAt: null,
          selectionApprovalComment: null,
          updatedAt: new Date(),
          updatedBy: userId,
        })
        .where(
          and(
            eq(rfqLastDetails.rfqsLastId, rfqId),
            eq(rfqLastDetails.isSelected, true)
          )
        )
        .returning();

      if (!cancelled) {
        throw new Error("선정된 업체를 찾을 수 없습니다.");
      }

      // RFQ 상태 되돌리기
      await tx
        .update(rfqsLast)
        .set({
          status: "견적접수",
          updatedAt: new Date(),
        })
        .where(eq(rfqsLast.id, rfqId));
    });

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

    return { success: true };
  } catch (error) {
    console.error("업체 선정 취소 오류:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "업체 선정 취소 중 오류가 발생했습니다."
    };
  }
}
// 선정된 업체 정보 조회
export async function getSelectedVendor(rfqId: number) {
  try {
    const [selected] = await db
      .select({
        detail: rfqLastDetails,
        vendor: vendors,
      })
      .from(rfqLastDetails)
      .leftJoin(vendors, eq(rfqLastDetails.vendorsId, vendors.id))
      .where(
        and(
          eq(rfqLastDetails.rfqsLastId, rfqId),
          eq(rfqLastDetails.isSelected, true),
          eq(rfqLastDetails.isLatest, true)
        )
      )
      .limit(1);

    return {
      success: true,
      data: selected
    };
  } catch (error) {
    console.error("선정 업체 조회 오류:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "선정 업체 조회 중 오류가 발생했습니다."
    };
  }
}

// 선정 승인 요청
export async function requestSelectionApproval(
  rfqId: number,
  vendorId: number,
  userId: number
) {
  try {
    const [updated] = await db
      .update(rfqLastDetails)
      .set({
        selectionApprovalStatus: "대기",
        updatedAt: new Date(),
        updatedBy: userId,
      })
      .where(
        and(
          eq(rfqLastDetails.rfqsLastId, rfqId),
          eq(rfqLastDetails.vendorsId, vendorId),
          eq(rfqLastDetails.isSelected, true),
          eq(rfqLastDetails.isLatest, true)
        )
      )
      .returning();

    if (!updated) {
      throw new Error("선정된 업체를 찾을 수 없습니다.");
    }

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

    return {
      success: true,
      data: updated
    };
  } catch (error) {
    console.error("선정 승인 요청 오류:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "선정 승인 요청 중 오류가 발생했습니다."
    };
  }
}

// 선정 승인/반려 처리
export async function processSelectionApproval(
  rfqId: number,
  vendorId: number,
  action: "승인" | "반려",
  comment: string,
  approverId: number
) {
  try {
    await db.transaction(async (tx) => {
      // 선정 승인 정보 업데이트
      const [updated] = await tx
        .update(rfqLastDetails)
        .set({
          selectionApprovalStatus: action,
          selectionApprovedBy: approverId,
          selectionApprovedAt: new Date(),
          selectionApprovalComment: comment,
          updatedAt: new Date(),
          updatedBy: approverId,
        })
        .where(
          and(
            eq(rfqLastDetails.rfqsLastId, rfqId),
            eq(rfqLastDetails.vendorsId, vendorId),
            eq(rfqLastDetails.isSelected, true),
            eq(rfqLastDetails.isLatest, true)
          )
        )
        .returning();

      if (!updated) {
        throw new Error("선정된 업체를 찾을 수 없습니다.");
      }

      // 승인된 경우 RFQ 상태 업데이트
      if (action === "승인") {
        await tx
          .update(rfqsLast)
          .set({
            status: "계약 진행중",
            updatedAt: new Date(),
          })
          .where(eq(rfqsLast.id, rfqId));
      }
    });

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

    return {
      success: true,
      message: action === "승인" 
        ? "업체 선정이 승인되었습니다." 
        : "업체 선정이 반려되었습니다."
    };
  } catch (error) {
    console.error("선정 승인 처리 오류:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "선정 승인 처리 중 오류가 발생했습니다."
    };
  }
}

// 가격 순위 업데이트 (견적 제출 후 자동 실행)
export async function updatePriceRanks(rfqId: number) {
  try {
    // 해당 RFQ의 모든 최신 견적 조회
    const quotes = await db
      .select({
        id: rfqLastDetails.id,
        vendorsId: rfqLastDetails.vendorsId,
        totalAmount: rfqLastDetails.totalAmount,
      })
      .from(rfqLastDetails)
      .where(
        and(
          eq(rfqLastDetails.rfqsLastId, rfqId),
          eq(rfqLastDetails.isLatest, true)
        )
      )
      .orderBy(asc(rfqLastDetails.totalAmount));

    // 순위 업데이트
    await db.transaction(async (tx) => {
      for (let i = 0; i < quotes.length; i++) {
        await tx
          .update(rfqLastDetails)
          .set({ 
            priceRank: i + 1,
            updatedAt: new Date()
          })
          .where(eq(rfqLastDetails.id, quotes[i].id));
      }
    });

    return {
      success: true,
      message: "가격 순위가 업데이트되었습니다."
    };
  } catch (error) {
    console.error("가격 순위 업데이트 오류:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "가격 순위 업데이트 중 오류가 발생했습니다."
    };
  }
}

// RFQ의 모든 견적 상태 조회
export async function getRfqQuotationStatus(rfqId: number) {
  try {
    const quotations = await db
      .select({
        id: rfqLastDetails.id,
        vendorId: rfqLastDetails.vendorsId,
        vendorName: vendors.name,
        vendorCode: vendors.code,
        totalAmount: rfqLastDetails.totalAmount,
        currency: rfqLastDetails.currency,
        priceRank: rfqLastDetails.priceRank,
        isSelected: rfqLastDetails.isSelected,
        selectionDate: rfqLastDetails.selectionDate,
        selectionReason: rfqLastDetails.selectionReason,
        selectionApprovalStatus: rfqLastDetails.selectionApprovalStatus,
        emailStatus: rfqLastDetails.emailStatus,
        lastEmailSentAt: rfqLastDetails.lastEmailSentAt,
      })
      .from(rfqLastDetails)
      .leftJoin(vendors, eq(rfqLastDetails.vendorsId, vendors.id))
      .where(
        and(
          eq(rfqLastDetails.rfqsLastId, rfqId),
          eq(rfqLastDetails.isLatest, true)
        )
      )
      .orderBy(asc(rfqLastDetails.priceRank));

    const selectedVendor = quotations.find(q => q.isSelected);
    const totalQuotations = quotations.length;
    const respondedQuotations = quotations.filter(q => q.totalAmount).length;

    return {
      success: true,
      data: {
        quotations,
        summary: {
          total: totalQuotations,
          responded: respondedQuotations,
          pending: totalQuotations - respondedQuotations,
          selected: selectedVendor ? 1 : 0,
          selectedVendor: selectedVendor || null,
        }
      }
    };
  } catch (error) {
    console.error("RFQ 견적 상태 조회 오류:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : "견적 상태 조회 중 오류가 발생했습니다."
    };
  }
}