path: root/lib/vendor-rfq-response/service.ts

'use server'

import { revalidateTag, unstable_cache } from "next/cache";
import  db  from "@/db/db";
import { and, desc, eq, inArray, isNull, or, sql } from "drizzle-orm";
import { rfqAttachments, rfqComments, rfqItems, vendorResponses } from "@/db/schema/rfq";
import { vendorResponsesView, vendorTechnicalResponses, vendorCommercialResponses, vendorResponseAttachments } from "@/db/schema/rfq";
import { items } from "@/db/schema/items";
import { GetRfqsForVendorsSchema } from "../rfqs/validations";
import { ItemData } from "./vendor-cbe-table/rfq-items-table/rfq-items-table";
import * as z from "zod"



export async function getRfqResponsesForVendor(input: GetRfqsForVendorsSchema, vendorId: number) {
  return unstable_cache(
    async () => {
      const offset = (input.page - 1) * input.perPage;
      const limit = input.perPage;

      // 1) 메인 쿼리: vendorResponsesView 사용
      const { rows, total } = await db.transaction(async (tx) => {
        // 검색 조건
        let globalWhere;
        if (input.search) {
          const s = `%${input.search}%`;
          globalWhere = or(
            sql`${vendorResponsesView.rfqCode} ILIKE ${s}`,
            sql`${vendorResponsesView.projectName} ILIKE ${s}`,
            sql`${vendorResponsesView.rfqDescription} ILIKE ${s}`
          );
        }

        // 협력업체 ID 필터링
        const mainWhere = and(eq(vendorResponsesView.vendorId, vendorId), globalWhere);

        // 정렬: 응답 시간순
        const orderBy = [desc(vendorResponsesView.respondedAt)];

        // (A) 데이터 조회
        const data = await tx
          .select()
          .from(vendorResponsesView)
          .where(mainWhere)
          .orderBy(...orderBy)
          .offset(offset)
          .limit(limit);

        // (B) 전체 개수 카운트
        const [{ count }] = await tx
          .select({
            count: sql<number>`count(*)`.as("count"),
          })
          .from(vendorResponsesView)
          .where(mainWhere);

        return { rows: data, total: Number(count) };
      });

      // 2) rfqId 고유 목록 추출
      const distinctRfqs = [...new Set(rows.map((r) => r.rfqId))];
      if (distinctRfqs.length === 0) {
        return { data: [], pageCount: 0 };
      }

      // 3) 추가 데이터 조회
      // 3-A) RFQ 아이템
      const itemsAll = await db
        .select({
          id: rfqItems.id,
          rfqId: rfqItems.rfqId,
          itemCode: rfqItems.itemCode,
          itemName: items.itemName,
          quantity: rfqItems.quantity,
          description: rfqItems.description,
          uom: rfqItems.uom,
        })
        .from(rfqItems)
        .leftJoin(items, eq(rfqItems.itemCode, items.itemCode))
        .where(inArray(rfqItems.rfqId, distinctRfqs));

      // 3-B) RFQ 첨부 파일 (협력업체용)
      const attachAll = await db
        .select()
        .from(rfqAttachments)
        .where(
          and(
            inArray(rfqAttachments.rfqId, distinctRfqs),
            isNull(rfqAttachments.vendorId)
          )
        );

      // 3-C) RFQ 코멘트
      const commAll = await db
        .select()
        .from(rfqComments)
        .where(
          and(
            inArray(rfqComments.rfqId, distinctRfqs),
            or(
              isNull(rfqComments.vendorId),
              eq(rfqComments.vendorId, vendorId)
            )
          )
        );

  
      // 3-E) 협력업체 응답 상세 - 기술
      const technicalResponsesAll = await db
        .select()
        .from(vendorTechnicalResponses)
        .where(
          inArray(
            vendorTechnicalResponses.responseId,
            rows.map((r) => r.responseId)
          )
        );

      // 3-F) 협력업체 응답 상세 - 상업
      const commercialResponsesAll = await db
        .select()
        .from(vendorCommercialResponses)
        .where(
          inArray(
            vendorCommercialResponses.responseId,
            rows.map((r) => r.responseId)
          )
        );

      // 3-G) 협력업체 응답 첨부 파일
      const responseAttachmentsAll = await db
        .select()
        .from(vendorResponseAttachments)
        .where(
          inArray(
            vendorResponseAttachments.responseId,
            rows.map((r) => r.responseId)
          )
        );

      // 4) 데이터 그룹화
      // RFQ 아이템 그룹화
      const itemsByRfqId = new Map<number, any[]>();
      for (const it of itemsAll) {
        if (!itemsByRfqId.has(it.rfqId)) {
          itemsByRfqId.set(it.rfqId, []);
        }
        itemsByRfqId.get(it.rfqId)!.push({
          id: it.id,
          itemCode: it.itemCode,
          itemName: it.itemName,
          quantity: it.quantity,
          description: it.description,
          uom: it.uom,
        });
      }

      // RFQ 첨부 파일 그룹화
      const attachByRfqId = new Map<number, any[]>();
      for (const att of attachAll) {
        const rid = att.rfqId!;
        if (!attachByRfqId.has(rid)) {
          attachByRfqId.set(rid, []);
        }
        attachByRfqId.get(rid)!.push({
          id: att.id,
          fileName: att.fileName,
          filePath: att.filePath,
          vendorId: att.vendorId,
          evaluationId: att.evaluationId,
        });
      }

      // RFQ 코멘트 그룹화
      const commByRfqId = new Map<number, any[]>();
      for (const c of commAll) {
        const rid = c.rfqId!;
        if (!commByRfqId.has(rid)) {
          commByRfqId.set(rid, []);
        }
        commByRfqId.get(rid)!.push({
          id: c.id,
          commentText: c.commentText,
          vendorId: c.vendorId,
          evaluationId: c.evaluationId,
          createdAt: c.createdAt,
        });
      }

    
      // 기술 응답 그룹화
      const techResponseByResponseId = new Map<number, any>();
      for (const tr of technicalResponsesAll) {
        techResponseByResponseId.set(tr.responseId, {
          id: tr.id,
          summary: tr.summary,
          notes: tr.notes,
          createdAt: tr.createdAt,
          updatedAt: tr.updatedAt,
        });
      }

      // 상업 응답 그룹화
      const commResponseByResponseId = new Map<number, any>();
      for (const cr of commercialResponsesAll) {
        commResponseByResponseId.set(cr.responseId, {
          id: cr.id,
          totalPrice: cr.totalPrice,
          currency: cr.currency,
          paymentTerms: cr.paymentTerms,
          incoterms: cr.incoterms,
          deliveryPeriod: cr.deliveryPeriod,
          warrantyPeriod: cr.warrantyPeriod,
          validityPeriod: cr.validityPeriod,
          priceBreakdown: cr.priceBreakdown,
          commercialNotes: cr.commercialNotes,
          createdAt: cr.createdAt,
          updatedAt: cr.updatedAt,
        });
      }

      // 응답 첨부 파일 그룹화
      const respAttachByResponseId = new Map<number, any[]>();
      for (const ra of responseAttachmentsAll) {
        const rid = ra.responseId!;
        if (!respAttachByResponseId.has(rid)) {
          respAttachByResponseId.set(rid, []);
        }
        respAttachByResponseId.get(rid)!.push({
          id: ra.id,
          fileName: ra.fileName,
          filePath: ra.filePath,
          attachmentType: ra.attachmentType,
          description: ra.description,
          uploadedAt: ra.uploadedAt,
          uploadedBy: ra.uploadedBy,
        });
      }

      // 5) 최종 데이터 결합
      const final = rows.map((row) => {
        return {
          // 응답 정보
          responseId: row.responseId,
          responseStatus: row.responseStatus,
          respondedAt: row.respondedAt,
          
          // RFQ 기본 정보
          rfqId: row.rfqId,
          rfqCode: row.rfqCode,
          rfqDescription: row.rfqDescription,
          rfqDueDate: row.rfqDueDate,
          rfqStatus: row.rfqStatus,
          rfqType: row.rfqType,
          rfqCreatedAt: row.rfqCreatedAt,
          rfqUpdatedAt: row.rfqUpdatedAt,
          rfqCreatedBy: row.rfqCreatedBy,
          
          // 프로젝트 정보
          projectId: row.projectId,
          projectCode: row.projectCode,
          projectName: row.projectName,
          
          // 협력업체 정보
          vendorId: row.vendorId,
          vendorName: row.vendorName,
          vendorCode: row.vendorCode,
          
          // RFQ 관련 데이터
          items: itemsByRfqId.get(row.rfqId) || [],
          attachments: attachByRfqId.get(row.rfqId) || [],
          comments: commByRfqId.get(row.rfqId) || [],
          
          // 평가 정보
          tbeEvaluation: row.tbeId ? {
            id: row.tbeId,
            result: row.tbeResult,
          } : null,
          cbeEvaluation: row.cbeId ? {
            id: row.cbeId,
            result: row.cbeResult,
          } : null,
          
          // 협력업체 응답 상세
          technicalResponse: techResponseByResponseId.get(row.responseId) || null,
          commercialResponse: commResponseByResponseId.get(row.responseId) || null,
          responseAttachments: respAttachByResponseId.get(row.responseId) || [],
          
          // 응답 상태 표시
          hasTechnicalResponse: row.hasTechnicalResponse,
          hasCommercialResponse: row.hasCommercialResponse,
          attachmentCount: row.attachmentCount || 0,
        };
      });

      const pageCount = Math.ceil(total / input.perPage);
      return { data: final, pageCount };
    },
    [JSON.stringify(input), `${vendorId}`],
    {
      revalidate: 600,
      tags: ["rfqs-vendor", `vendor-${vendorId}`],
    }
  )();
}


export async function getItemsByRfqId(rfqId: number): Promise<ResponseType> {
  try {
    if (!rfqId || isNaN(Number(rfqId))) {
      return {
        success: false,
        error: "Invalid RFQ ID provided",
      }
    }

    // Query the database to get all items for the given RFQ ID
    const items = await db
      .select()
      .from(rfqItems)
      .where(eq(rfqItems.rfqId, rfqId))
      .orderBy(rfqItems.itemCode)


    return {
      success: true,
      data: items as ItemData[],
    }
  } catch (error) {
    console.error("Error fetching RFQ items:", error)
    
    return {
      success: false,
      error: error instanceof Error ? error.message : "Unknown error occurred when fetching RFQ items",
    }
  }
}


// Define the schema for validation
const commercialResponseSchema = z.object({
  responseId: z.number(),
  vendorId: z.number(), // Added vendorId field
  responseStatus: z.enum(["PENDING", "IN_PROGRESS", "SUBMITTED", "REJECTED", "ACCEPTED"]),
  totalPrice: z.number().optional(),
  currency: z.string().default("USD"),
  paymentTerms: z.string().optional(),
  incoterms: z.string().optional(),
  deliveryPeriod: z.string().optional(),
  warrantyPeriod: z.string().optional(),
  validityPeriod: z.string().optional(),
  priceBreakdown: z.string().optional(),
  commercialNotes: z.string().optional(),
})

type CommercialResponseInput = z.infer<typeof commercialResponseSchema>

interface ResponseType {
  success: boolean
  error?: string
  data?: any
}

export async function updateCommercialResponse(input: CommercialResponseInput): Promise<ResponseType> {
  try {
    // Validate input data
    const validated = commercialResponseSchema.parse(input)
    
    // Check if a commercial response already exists for this responseId
    const existingResponse = await db
      .select()
      .from(vendorCommercialResponses)
      .where(eq(vendorCommercialResponses.responseId, validated.responseId))
      .limit(1)
    
    const now = new Date()
    
    if (existingResponse.length > 0) {
      // Update existing record
      await db
        .update(vendorCommercialResponses)
        .set({
          responseStatus: validated.responseStatus,
          totalPrice: validated.totalPrice,
          currency: validated.currency,
          paymentTerms: validated.paymentTerms,
          incoterms: validated.incoterms,
          deliveryPeriod: validated.deliveryPeriod,
          warrantyPeriod: validated.warrantyPeriod,
          validityPeriod: validated.validityPeriod,
          priceBreakdown: validated.priceBreakdown,
          commercialNotes: validated.commercialNotes,
          updatedAt: now,
        })
        .where(eq(vendorCommercialResponses.responseId, validated.responseId))
      
    } else {
      // Return error instead of creating a new record
      return {
        success: false,
        error: "해당 응답 ID에 대한 상업 응답 정보를 찾을 수 없습니다."
      }
    }
    
    // Also update the main vendor response status if submitted
    if (validated.responseStatus === "SUBMITTED") {
      // Get the vendor response
      const vendorResponseResult = await db
        .select()
        .from(vendorResponses)
        .where(eq(vendorResponses.id, validated.responseId))
        .limit(1)
        
      if (vendorResponseResult.length > 0) {
        // Update the main response status to RESPONDED
        await db
          .update(vendorResponses)
          .set({
            responseStatus: "RESPONDED",
            updatedAt: now,
          })
          .where(eq(vendorResponses.id, validated.responseId))
      }
    }
    
    // Use vendorId for revalidateTag
    revalidateTag(`cbe-vendor-${validated.vendorId}`)
    
    return {
      success: true,
      data: { responseId: validated.responseId }
    }
    
  } catch (error) {
    console.error("Error updating commercial response:", error)
    
    if (error instanceof z.ZodError) {
      return {
        success: false,
        error: "유효하지 않은 데이터가 제공되었습니다."
      }
    }
    
    return {
      success: false,
      error: error instanceof Error ? error.message : "Unknown error occurred"
    }
  }
}
// Helper function to get responseId from rfqId and vendorId
export async function getCommercialResponseByResponseId(responseId: number): Promise<any | null> {
  try {
    const response = await db
      .select()
      .from(vendorCommercialResponses)
      .where(eq(vendorCommercialResponses.responseId, responseId))
      .limit(1)
    
    return response.length > 0 ? response[0] : null
  } catch (error) {
    console.error("Error getting commercial response:", error)
    return null
  }
}