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

import { revalidatePath, 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 {rfqLastDetails, rfqLastTbeDocumentReviews, rfqLastTbePdftronComments, rfqLastTbeVendorDocuments,rfqLastTbeSessions } from "@/db/schema";
import { filterColumns } from "@/lib/filter-columns";
import { GetTBELastSchema } from "./validations";
import { getServerSession } from "next-auth"
import { authOptions } from "@/app/api/auth/[...nextauth]/route"
import path from "path"
import fs from "fs/promises"
import { sendEmail } from "../mail/sendEmail";

// ==========================================
// 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 whereConditions = [advancedWhere, ne(tbeLastView.sessionStatus,"생성중")];
      if (globalWhere) {
        whereConditions.push(globalWhere);
      }
      const finalWhere = and(...whereConditions);

      // 정렬
      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;
}


// ==========================================
// 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;
}

interface UpdateEvaluationData {
  evaluationResult?: "Acceptable" | "Acceptable with Comment" | "Not Acceptable"
  conditionalRequirements?: string
  conditionsFulfilled?: boolean
  technicalSummary?: string
  commercialSummary?: string
  overallRemarks?: string
  approvalRemarks?: string
  status?: "준비중" | "진행중" | "검토중" | "보류" | "완료" | "취소"
}

export async function updateTbeEvaluation(
  tbeSessionId: number,
  data: UpdateEvaluationData
) {
  try {
    const session = await getServerSession(authOptions)
    if (!session?.user) {
      return { success: false, error: "인증이 필요합니다" }
    }
    
    const userId = typeof session.user.id === 'string' ? parseInt(session.user.id) : session.user.id
    
    // 현재 TBE 세션 조회
    const [currentTbeSession] = await db
      .select()
      .from(rfqLastTbeSessions)
      .where(eq(rfqLastTbeSessions.id, tbeSessionId))
      .limit(1)
    
    if (!currentTbeSession) {
      return { success: false, error: "TBE 세션을 찾을 수 없습니다" }
    }
    
    // 업데이트 데이터 준비
    const updateData: any = {
      updatedBy: userId,
      updatedAt: new Date()
    }
    
    // 평가 결과 관련 필드
    if (data.evaluationResult !== undefined) {
      updateData.evaluationResult = data.evaluationResult
    }
    
    // 조건부 승인 관련 (Acceptable with Comment인 경우)
    if (data.evaluationResult === "Acceptable with Comment") {
      if (data.conditionalRequirements !== undefined) {
        updateData.conditionalRequirements = data.conditionalRequirements
      }
      if (data.conditionsFulfilled !== undefined) {
        updateData.conditionsFulfilled = data.conditionsFulfilled
      }
    } else if (data.evaluationResult === "Acceptable") {
      // Acceptable인 경우 조건부 필드 초기화
      updateData.conditionalRequirements = null
      updateData.conditionsFulfilled = true
    } else if (data.evaluationResult === "Not Acceptable") {
      // Not Acceptable인 경우 조건부 필드 초기화
      updateData.conditionalRequirements = null
      updateData.conditionsFulfilled = false
    }
    
    // 평가 요약 필드
    if (data.technicalSummary !== undefined) {
      updateData.technicalSummary = data.technicalSummary
    }
    if (data.commercialSummary !== undefined) {
      updateData.commercialSummary = data.commercialSummary
    }
    if (data.overallRemarks !== undefined) {
      updateData.overallRemarks = data.overallRemarks
    }
    
    // 승인 관련 필드
    if (data.approvalRemarks !== undefined) {
      updateData.approvalRemarks = data.approvalRemarks
      updateData.approvedBy = userId
      updateData.approvedAt = new Date()
    }
    
    // 상태 업데이트
    if (data.status !== undefined) {
      updateData.status = data.status
      
      // 완료 상태로 변경 시 종료일 설정
      if (data.status === "완료") {
        updateData.actualEndDate = new Date()
      }
    }
    
    // TBE 세션 업데이트
    const [updated] = await db
      .update(rfqLastTbeSessions)
      .set(updateData)
      .where(eq(rfqLastTbeSessions.id, tbeSessionId))
      .returning()
    
    // 캐시 초기화
    revalidateTag(`tbe-session-${tbeSessionId}`)
    revalidateTag(`tbe-sessions`)
    
    // RFQ 관련 캐시도 초기화
    if (currentTbeSession.rfqsLastId) {
      revalidateTag(`rfq-${currentTbeSession.rfqsLastId}`)
    }
    
    return { 
      success: true, 
      data: updated,
      message: "평가가 성공적으로 저장되었습니다"
    }
    
  } catch (error) {
    console.error("Failed to update TBE evaluation:", error)
    return { 
      success: false, 
      error: error instanceof Error ? error.message : "평가 저장에 실패했습니다"
    }
  }
}

export async function getTbeVendorDocuments(tbeSessionId: number) {
  
  try {
    const documents = await db
      .select({
        id: rfqLastTbeVendorDocuments.id,
        documentName: rfqLastTbeVendorDocuments.originalFileName,
        documentType: rfqLastTbeVendorDocuments.documentType,
        fileName: rfqLastTbeVendorDocuments.fileName,
        fileSize: rfqLastTbeVendorDocuments.fileSize,
        fileType: rfqLastTbeVendorDocuments.fileType,
        documentNo: rfqLastTbeVendorDocuments.documentNo,
        revisionNo: rfqLastTbeVendorDocuments.revisionNo,
        issueDate: rfqLastTbeVendorDocuments.issueDate,
        description: rfqLastTbeVendorDocuments.description,
        submittedAt: rfqLastTbeVendorDocuments.submittedAt,
        // 검토 정보는 rfqLastTbeDocumentReviews에서 가져옴
        reviewStatus: rfqLastTbeDocumentReviews.reviewStatus,
        reviewComments: rfqLastTbeDocumentReviews.reviewComments,
        reviewedAt: rfqLastTbeDocumentReviews.reviewedAt,
        requiresRevision: rfqLastTbeDocumentReviews.requiresRevision,
        technicalCompliance: rfqLastTbeDocumentReviews.technicalCompliance,
        qualityAcceptable: rfqLastTbeDocumentReviews.qualityAcceptable,
      })
      .from(rfqLastTbeVendorDocuments)
      .leftJoin(
        rfqLastTbeDocumentReviews,
        and(
          eq(rfqLastTbeDocumentReviews.vendorAttachmentId, rfqLastTbeVendorDocuments.id),
          eq(rfqLastTbeDocumentReviews.documentSource, "vendor")
        )
      )
      .where(eq(rfqLastTbeVendorDocuments.tbeSessionId, tbeSessionId))
      .orderBy(rfqLastTbeVendorDocuments.submittedAt)
    
    // 문서 정보 매핑 (reviewStatus는 이미 한글로 저장되어 있음)
    const mappedDocuments = documents.map(doc => ({
      ...doc,
      reviewStatus: doc.reviewStatus || "미검토", // null인 경우 기본값
      reviewRequired: doc.requiresRevision || false, // UI 호환성을 위해 필드명 매핑
    }))
    
    return {
      success: true,
      documents: mappedDocuments,
    }
  } catch (error) {
    console.error("Failed to fetch vendor documents:", error)
    return {
      success: false,
      error: "벤더 문서를 불러오는데 실패했습니다",
      documents: [],
    }
  }
}
// 리뷰 상태 매핑 함수
function mapReviewStatus(status: string | null): string {
  const statusMap: Record<string, string> = {
    "pending": "미검토",
    "reviewing": "검토중",
    "approved": "승인",
    "rejected": "반려",
  }
  
  return status ? (statusMap[status] || status) : "미검토"
}


interface DocumentInfo {
  documentId: number
  documentReviewId: number
  documentName: string
  filePath: string
  documentType: string
  documentSource: string
  reviewStatus?: string
}

interface SessionInfo {
  sessionId: number
  sessionTitle: string
  buyerName: string
  vendorName: string
}

interface SendDocumentsEmailParams {
  to: string[]
  cc?: string[]
  documents: DocumentInfo[]
  comments?: string
  sessionInfo: SessionInfo
}

export async function sendDocumentsEmail({
  to,
  cc,
  documents,
  comments,
  sessionInfo
}: SendDocumentsEmailParams) {
  try {
    // 사용자 인증 확인
    const session = await getServerSession(authOptions)
    if (!session?.user) {
      return { success: false, error: "인증이 필요합니다" }
    }
    

    // 첨부 파일 준비
    const attachments = await Promise.all(
      documents.map(async (doc) => {
        try {
          // 실제 파일 경로 구성 (프로젝트 구조에 맞게 조정 필요)

          const isDev = process.env.NODE_ENV === 'development';

          const filePath = isDev ? path.join(process.cwd(), 'public', doc.filePath)
          :path.join(`${process.env.NAS_PATH}`, doc.filePath)
          
          // 파일 존재 확인
          await fs.access(filePath)
          
          // 파일 읽기
          const content = await fs.readFile(filePath)
          
          return {
            filename: doc.documentName,
            content: content,
            encoding: 'base64'
          }
        } catch (error) {
          console.error(`Failed to attach file: ${doc.documentName}`, error)
          // 파일을 찾을 수 없는 경우 건너뛰기
          return null
        }
      })
    )

    // null 값 필터링
    const validAttachments = attachments.filter(att => att !== null)

    // 이메일 전송
    const result = await sendEmail({
      to: to.join(", "),
      cc: cc?.join(", "),
      template: "document-share", // 템플릿 이름
      context: {
        senderName: session.user.name || "TBE User",
        senderEmail: session.user.email,
        sessionTitle: sessionInfo.sessionTitle,
        sessionId: sessionInfo.sessionId,
        buyerName: sessionInfo.buyerName,
        vendorName: sessionInfo.vendorName,
        documentCount: documents.length,
        documents: documents.map(doc => ({
          name: doc.documentName,
          type: doc.documentType,
          source: doc.documentSource,
          reviewStatus: doc.reviewStatus || "미검토",
          reviewStatusClass: getReviewStatusClass(doc.reviewStatus),
        })),
        comments: comments || "",
        hasComments: !!comments,
        language: "ko", // 한국어로 설정
        year: new Date().getFullYear(),
      },
      attachments: validAttachments as any
    })

    return { success: true, data: result }
  } catch (error) {
    console.error("Failed to send documents email:", error)
    return { 
      success: false, 
      error: error instanceof Error ? error.message : "Failed to send email" 
    }
  }
}

// 리뷰 상태에 따른 CSS 클래스 반환
function getReviewStatusClass(status?: string): string {
  switch (status) {
    case "승인":
      return "approved"
    case "반려":
      return "rejected"
    case "보류":
      return "pending"
    case "검토중":
      return "reviewing"
    default:
      return "unreviewed"
  }
}


interface RfqInfo {
  rfqCode: string;
  rfqTitle: string;
  rfqDueDate: Date | null;
  projectCode: string;
  projectName: string;
  packageNo: string;
  packageName: string;
  picName: string;
  rfqId: number; // rfqLastId 추가
}

interface VendorInfo {
  sessionId: number;
  vendorId: number; // vendor ID 추가
  vendorCode: string;
  vendorName: string;
}

export async function requestTBEForRFQ(
  rfqInfo: RfqInfo,
  vendors: VendorInfo[]
) {
  try {
    const session = await getServerSession(authOptions)
    if (!session?.user) {
      return { success: false, error: "인증이 필요합니다" }
    }

    // 벤더별 이메일 정보 조회
    const vendorEmails = await db
      .select({
        vendorId: rfqLastDetails.vendorsId,
        emailSentTo: rfqLastDetails.emailSentTo,
      })
      .from(rfqLastDetails)
      .where(
        and(
          eq(rfqLastDetails.rfqsLastId, rfqInfo.rfqId),
          inArray(rfqLastDetails.vendorsId, vendors.map(v => v.vendorId)),
          eq(rfqLastDetails.isLatest, true)
        )
      );

    // 이메일 정보 매핑
    const vendorEmailMap = new Map();
    vendorEmails.forEach(ve => {
      if (ve.emailSentTo) {
        try {
          const emailData = JSON.parse(ve.emailSentTo);
          vendorEmailMap.set(ve.vendorId, emailData);
        } catch (error) {
          console.error(`이메일 파싱 실패 - vendorId: ${ve.vendorId}`, error);
        }
      }
    });

    // 1. 트랜잭션으로 모든 세션 상태 업데이트
    await db.transaction(async (tx) => {
      // 세션 상태 업데이트
      const sessionIds = vendors.map(v => v.sessionId);
      
      await tx
        .update(rfqLastTbeSessions)
        .set({ 
          status: "진행중",
          updatedAt: new Date(),
        })
        .where(inArray(rfqLastTbeSessions.id, sessionIds));
    });

    // 2. 각 벤더에게 이메일 발송
    const emailPromises = vendors.map(async (vendor) => {
      const emailInfo = vendorEmailMap.get(vendor.vendorId);
      
      if (!emailInfo) {
        console.warn(`벤더 ${vendor.vendorName}의 이메일 정보가 없습니다.`);
        return { success: false, vendor: vendor.vendorName, error: "이메일 정보 없음" };
      }

      try {
        // to와 cc 이메일 추출
        const toEmails = Array.isArray(emailInfo.to) ? emailInfo.to : [emailInfo.to];
        const ccEmails = Array.isArray(emailInfo.cc) ? emailInfo.cc : [];
        
        // 모든 to 이메일 주소로 발송
        const emailResults = await Promise.all(
          toEmails.filter(Boolean).map(toEmail => 
            sendEmail({
              to: toEmail,
              template: "tbe-request",
              subject: `[TBE 요청] ${rfqInfo.rfqCode} - 기술입찰평가 서류 제출 요청`,
              context: {
                vendorName: vendor.vendorName,
                vendorCode: vendor.vendorCode,
                rfqCode: rfqInfo.rfqCode,
                rfqTitle: rfqInfo.rfqTitle,
                rfqDueDate: rfqInfo.rfqDueDate ? 
                  new Date(rfqInfo.rfqDueDate).toLocaleDateString("ko-KR") : 
                  "미정",
                projectCode: rfqInfo.projectCode,
                projectName: rfqInfo.projectName,
                packageNo: rfqInfo.packageNo,
                packageName: rfqInfo.packageName,
                picName: rfqInfo.picName,
                picEmail: session.user.email,
                picPhone: process.env.DEFAULT_PIC_PHONE || "",
                tbeDeadline: calculateTBEDeadline(rfqInfo.rfqDueDate),
                companyName: process.env.COMPANY_NAME || "Your Company",
              },
              cc: [
                ...ccEmails.filter(Boolean)
              ],
            })
          )
        );
        
        return { 
          success: true, 
          vendor: vendor.vendorName,
          emailsSent: emailResults.length 
        };
        
      } catch (error) {
        console.error(`이메일 발송 실패 - ${vendor.vendorName}:`, error);
        return { success: false, vendor: vendor.vendorName, error };
      }
    });

    const emailResults = await Promise.allSettled(emailPromises);
    
    // 3. 결과 확인
    const successResults = emailResults.filter(
      r => r.status === "fulfilled" && r.value?.success
    );
    const failedResults = emailResults.filter(
      r => r.status === "rejected" || (r.status === "fulfilled" && !r.value?.success)
    );
    
    if (failedResults.length > 0) {
      console.warn(`${failedResults.length}개 벤더의 이메일 발송 실패`);
    }

    revalidatePath("/evcp/tbe-last");
    
    return { 
      success: true, 
      message: `${successResults.length}개 벤더에 TBE 요청 완료`,
      emailsSent: successResults.length,
      emailsFailed: failedResults.length
    };
    
  } catch (error) {
    console.error("TBE 요청 실패:", error);
    return { 
      success: false, 
      error: error instanceof Error ? error.message : "TBE 요청 중 오류가 발생했습니다." 
    };
  }
}

// TBE 제출 기한 계산 (RFQ 마감일 7일 전)
function calculateTBEDeadline(rfqDueDate: Date | null): string {
  if (!rfqDueDate) return "추후 공지";
  
  const deadline = new Date(rfqDueDate);
  deadline.setDate(deadline.getDate() - 7); // 7일 전
  
  return deadline.toLocaleDateString("ko-KR", {
    year: "numeric",
    month: "long",
    day: "numeric",
  });
}