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

import { 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 } from "@/db/schema/rfq";
import { vendorResponsesView, vendorTechnicalResponses, vendorCommercialResponses, vendorResponseAttachments } from "@/db/schema/rfq";
import { items } from "@/db/schema/items";
import { GetRfqsForVendorsSchema } from "../rfqs/validations";



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