// lib/tbe-last/service.ts
'use server'

import { unstable_cache } from "next/cache";
import db from "@/db/db";
import { and, desc, asc, eq, sql, or, isNull, isNotNull, ne, inArray } from "drizzle-orm";
import { tbeLastView, tbeDocumentsView } from "@/db/schema";
import { rfqPrItems } from "@/db/schema/rfqLast";
import { rfqLastTbeDocumentReviews, rfqLastTbePdftronComments, rfqLastTbeVendorDocuments } from "@/db/schema";
import { filterColumns } from "@/lib/filter-columns";
import { GetTBELastSchema } from "./validations";

// ==========================================
// 1. TBE 세션 목록 조회
// ==========================================
export async function getAllTBELast(input: GetTBELastSchema) {
  return unstable_cache(
    async () => {
      // 페이징
      const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10);
      const limit = input.perPage ?? 10;

      // 고급 필터
      const advancedWhere = filterColumns({
        table: tbeLastView,
        filters: input.filters ?? [],
        joinOperator: input.joinOperator ?? "and",
      });

      // 글로벌 검색
      let globalWhere;
      if (input.search) {
        const s = `%${input.search}%`;
        globalWhere = or(
          sql`${tbeLastView.sessionCode} ILIKE ${s}`,
          sql`${tbeLastView.rfqCode} ILIKE ${s}`,
          sql`${tbeLastView.vendorName} ILIKE ${s}`,
          sql`${tbeLastView.vendorCode} ILIKE ${s}`,
          sql`${tbeLastView.projectCode} ILIKE ${s}`,
          sql`${tbeLastView.projectName} ILIKE ${s}`,
          sql`${tbeLastView.packageNo} ILIKE ${s}`,
          sql`${tbeLastView.packageName} ILIKE ${s}`
        );
      }

      // 최종 WHERE
      const finalWhere = and(advancedWhere, globalWhere);

      // 정렬
      const orderBy = input.sort?.length
        ? input.sort.map((s) => {
            const col = (tbeLastView as any)[s.id];
            return s.desc ? desc(col) : asc(col);
          })
        : [desc(tbeLastView.createdAt)];

      // 메인 SELECT
      const [rows, total] = await db.transaction(async (tx) => {
        const data = await tx
          .select()
          .from(tbeLastView)
          .where(finalWhere)
          .orderBy(...orderBy)
          .offset(offset)
          .limit(limit);

        const [{ count }] = await tx
          .select({ count: sql<number>`count(*)`.as("count") })
          .from(tbeLastView)
          .where(finalWhere);

        return [data, Number(count)];
      });

      const pageCount = Math.ceil(total / limit);
      return { data: rows, pageCount };
    },
    [JSON.stringify(input)],
    {
      revalidate: 60,
      tags: ["tbe-last-sessions"],
    }
  )();
}

// ==========================================
// 2. TBE 세션 상세 조회
// ==========================================
export async function getTBESessionDetail(sessionId: number) {
  return unstable_cache(
    async () => {
      // 세션 기본 정보
      const [session] = await db
        .select()
        .from(tbeLastView)
        .where(eq(tbeLastView.tbeSessionId, sessionId))
        .limit(1);

      if (!session) {
        return null;
      }

      // PR 아이템 목록
      const prItems = await db
        .select()
        .from(rfqPrItems)
        .where(eq(rfqPrItems.rfqsLastId, session.rfqId))
        .orderBy(desc(rfqPrItems.majorYn), asc(rfqPrItems.prItem));

      // 문서 목록 (구매자 + 벤더)
      const documents = await db
        .select()
        .from(tbeDocumentsView)
        .where(eq(tbeDocumentsView.tbeSessionId, sessionId))
        .orderBy(
          sql`CASE document_source WHEN 'buyer' THEN 0 ELSE 1 END`,
          asc(tbeDocumentsView.documentName)
        );

      // PDFTron 코멘트 통계
      const comments = await db
        .select({
          documentReviewId: rfqLastTbePdftronComments.documentReviewId,
          totalCount: sql<number>`count(*)`.as("total_count"),
          openCount: sql<number>`sum(case when status = 'open' then 1 else 0 end)`.as("open_count"),
        })
        .from(rfqLastTbePdftronComments)
        .innerJoin(
          rfqLastTbeDocumentReviews,
          eq(rfqLastTbePdftronComments.documentReviewId, rfqLastTbeDocumentReviews.id)
        )
        .where(eq(rfqLastTbeDocumentReviews.tbeSessionId, sessionId))
        .groupBy(rfqLastTbePdftronComments.documentReviewId);

      // 문서별 코멘트 수 매핑
      const commentsByDocumentId = new Map(
        comments.map(c => [c.documentReviewId, {
          totalCount: c.totalCount,
          openCount: c.openCount
        }])
      );

      // 문서에 코멘트 정보 추가
      const documentsWithComments = documents.map(doc => ({
        ...doc,
        comments: doc.documentReviewId 
          ? commentsByDocumentId.get(doc.documentReviewId) || { totalCount: 0, openCount: 0 }
          : { totalCount: 0, openCount: 0 }
      }));

      return {
        session,
        prItems,
        documents: documentsWithComments,
      };
    },
    [`tbe-session-${sessionId}`],
    {
      revalidate: 60,
      tags: [`tbe-session-${sessionId}`],
    }
  )();
}

// ==========================================
// 3. 문서별 PDFTron 코멘트 조회
// ==========================================
export async function getDocumentComments(documentReviewId: number) {
  const comments = await db
    .select({
      id: rfqLastTbePdftronComments.id,
      pdftronAnnotationId: rfqLastTbePdftronComments.pdftronAnnotationId,
      pageNumber: rfqLastTbePdftronComments.pageNumber,
      commentText: rfqLastTbePdftronComments.commentText,
      commentCategory: rfqLastTbePdftronComments.commentCategory,
      severity: rfqLastTbePdftronComments.severity,
      status: rfqLastTbePdftronComments.status,
      createdBy: rfqLastTbePdftronComments.createdBy,
      createdByType: rfqLastTbePdftronComments.createdByType,
      createdAt: rfqLastTbePdftronComments.createdAt,
      resolvedBy: rfqLastTbePdftronComments.resolvedBy,
      resolvedAt: rfqLastTbePdftronComments.resolvedAt,
      resolutionNote: rfqLastTbePdftronComments.resolutionNote,
      replies: rfqLastTbePdftronComments.replies,
    })
    .from(rfqLastTbePdftronComments)
    .where(eq(rfqLastTbePdftronComments.documentReviewId, documentReviewId))
    .orderBy(asc(rfqLastTbePdftronComments.pageNumber), desc(rfqLastTbePdftronComments.createdAt));

  return comments;
}

// ==========================================
// 4. TBE 평가 결과 업데이트
// ==========================================
export async function updateTBEEvaluation(
  sessionId: number,
  data: {
    evaluationResult: "pass" | "conditional_pass" | "non_pass";
    conditionalRequirements?: string;
    technicalSummary?: string;
    commercialSummary?: string;
    overallRemarks?: string;
  }
) {
  // 실제 업데이트 로직
  // await db.update(rfqLastTbeSessions)...
  
  // 캐시 무효화
  return { success: true };
}

// ==========================================
// 5. 벤더 문서 업로드
// ==========================================
export async function uploadVendorDocument(
  sessionId: number,
  file: {
    fileName: string;
    originalFileName: string;
    filePath: string;
    fileSize: number;
    fileType: string;
    documentType: string;
    description?: string;
  }
) {
  const [document] = await db
    .insert(rfqLastTbeVendorDocuments)
    .values({
      tbeSessionId: sessionId,
      documentType: file.documentType as any,
      fileName: file.fileName,
      originalFileName: file.originalFileName,
      filePath: file.filePath,
      fileSize: file.fileSize,
      fileType: file.fileType,
      description: file.description,
      reviewRequired: true,
      reviewStatus: "pending",
      submittedBy: 1, // TODO: 실제 사용자 ID
      submittedAt: new Date(),
    })
    .returning();

  return document;
}