path: root/lib/evaluation/service.ts

'use server'

import db from "@/db/db"
import { 
  evaluationSubmissions,
  periodicEvaluationsView, 
  type PeriodicEvaluationView 
} from "@/db/schema"
import { 
  and, 
  asc, 
  count, 
  desc, 
  ilike, 
  or, sql , eq, avg, 
  type SQL 
} from "drizzle-orm"
import { filterColumns } from "@/lib/filter-columns"
import { GetEvaluationTargetsSchema } from "../evaluation-target-list/validation";

export async function getPeriodicEvaluations(input: GetEvaluationTargetsSchema) {
    try {

      const offset = (input.page - 1) * input.perPage;
  
      // ✅ getEvaluationTargets 방식과 동일한 필터링 처리
      // 1) 고급 필터 조건
      let advancedWhere: SQL<unknown> | undefined = undefined;
      if (input.filters && input.filters.length > 0) {
        advancedWhere = filterColumns({
          table: periodicEvaluationsView,
          filters: input.filters,
          joinOperator: input.joinOperator || 'and',
        });
      }
  
      // 2) 기본 필터 조건
      let basicWhere: SQL<unknown> | undefined = undefined;
      if (input.basicFilters && input.basicFilters.length > 0) {
        basicWhere = filterColumns({
          table: periodicEvaluationsView,
          filters: input.basicFilters,
          joinOperator: input.basicJoinOperator || 'and',
        });
      }
  
      // 3) 글로벌 검색 조건
      let globalWhere: SQL<unknown> | undefined = undefined;
      if (input.search) {
        const s = `%${input.search}%`;
  
        const validSearchConditions: SQL<unknown>[] = [];
  
        // 벤더 정보로 검색
        const vendorCodeCondition = ilike(periodicEvaluationsView.vendorCode, s);
        if (vendorCodeCondition) validSearchConditions.push(vendorCodeCondition);
  
        const vendorNameCondition = ilike(periodicEvaluationsView.vendorName, s);
        if (vendorNameCondition) validSearchConditions.push(vendorNameCondition);
  
        // 평가 관련 코멘트로 검색
        const evaluationNoteCondition = ilike(periodicEvaluationsView.evaluationNote, s);
        if (evaluationNoteCondition) validSearchConditions.push(evaluationNoteCondition);
  
        const adminCommentCondition = ilike(periodicEvaluationsView.evaluationTargetAdminComment, s);
        if (adminCommentCondition) validSearchConditions.push(adminCommentCondition);
  
        const consolidatedCommentCondition = ilike(periodicEvaluationsView.evaluationTargetConsolidatedComment, s);
        if (consolidatedCommentCondition) validSearchConditions.push(consolidatedCommentCondition);
  
        // 최종 확정자 이름으로 검색
        const finalizedByUserNameCondition = ilike(periodicEvaluationsView.finalizedByUserName, s);
        if (finalizedByUserNameCondition) validSearchConditions.push(finalizedByUserNameCondition);
  
        if (validSearchConditions.length > 0) {
          globalWhere = or(...validSearchConditions);
        }
      }
  
      // ✅ getEvaluationTargets 방식과 동일한 WHERE 조건 생성
      const whereConditions: SQL<unknown>[] = [];
  
      if (advancedWhere) whereConditions.push(advancedWhere);
      if (basicWhere) whereConditions.push(basicWhere);
      if (globalWhere) whereConditions.push(globalWhere);
  
      const finalWhere = whereConditions.length > 0 ? and(...whereConditions) : undefined;
  
      // ✅ getEvaluationTargets 방식과 동일한 전체 데이터 수 조회
      const totalResult = await db
        .select({ count: count() })
        .from(periodicEvaluationsView)
        .where(finalWhere);
  
      const total = totalResult[0]?.count || 0;
  
      if (total === 0) {
        return { data: [], pageCount: 0, total: 0 };
      }
  
      console.log("Total periodic evaluations:", total);
  
      // ✅ getEvaluationTargets 방식과 동일한 정렬 및 페이징 처리된 데이터 조회
      const orderByColumns = input.sort.map((sort) => {
        const column = sort.id as keyof typeof periodicEvaluationsView.$inferSelect;
        return sort.desc ? desc(periodicEvaluationsView[column]) : asc(periodicEvaluationsView[column]);
      });
  
      if (orderByColumns.length === 0) {
        orderByColumns.push(desc(periodicEvaluationsView.createdAt));
      }
  
      const periodicEvaluationsData = await db
        .select()
        .from(periodicEvaluationsView)
        .where(finalWhere)
        .orderBy(...orderByColumns)
        .limit(input.perPage)
        .offset(offset);
  
      const pageCount = Math.ceil(total / input.perPage);

      console.log(periodicEvaluationsData,"periodicEvaluationsData")
  
      return { data: periodicEvaluationsData, pageCount, total };
    } catch (err) {
      console.error("Error in getPeriodicEvaluations:", err);
      // ✅ getEvaluationTargets 방식과 동일한 에러 반환 (total 포함)
      return { data: [], pageCount: 0, total: 0 };
    }
  }

  export interface PeriodicEvaluationsStats {
    total: number
    pendingSubmission: number
    submitted: number
    inReview: number
    reviewCompleted: number
    finalized: number
    averageScore: number | null
    completionRate: number
    averageFinalScore: number | null
    documentsSubmittedCount: number
    documentsNotSubmittedCount: number
    reviewProgress: {
      totalReviewers: number
      completedReviewers: number
      pendingReviewers: number
      reviewCompletionRate: number
    }
  }
  
  export async function getPeriodicEvaluationsStats(evaluationYear: number): Promise<PeriodicEvaluationsStats> {
    try {
      // 기본 WHERE 조건: 해당 연도의 평가만
      const baseWhere = eq(periodicEvaluationsView.evaluationYear, evaluationYear)
  
      // 1. 전체 통계 조회
      const totalStatsResult = await db
        .select({
          total: count(),
          averageScore: avg(periodicEvaluationsView.totalScore),
          averageFinalScore: avg(periodicEvaluationsView.finalScore),
        })
        .from(periodicEvaluationsView)
        .where(baseWhere)
  
      const totalStats = totalStatsResult[0] || { 
        total: 0, 
        averageScore: null, 
        averageFinalScore: null 
      }
  
      // 2. 상태별 카운트 조회
      const statusStatsResult = await db
        .select({
          status: periodicEvaluationsView.status,
          count: count(),
        })
        .from(periodicEvaluationsView)
        .where(baseWhere)
        .groupBy(periodicEvaluationsView.status)
  
      // 상태별 카운트를 객체로 변환
      const statusCounts = statusStatsResult.reduce((acc, item) => {
        acc[item.status] = item.count
        return acc
      }, {} as Record<string, number>)
  
      // 3. 문서 제출 상태 통계
      const documentStatsResult = await db
        .select({
          documentsSubmitted: periodicEvaluationsView.documentsSubmitted,
          count: count(),
        })
        .from(periodicEvaluationsView)
        .where(baseWhere)
        .groupBy(periodicEvaluationsView.documentsSubmitted)
  
      const documentCounts = documentStatsResult.reduce((acc, item) => {
        if (item.documentsSubmitted) {
          acc.submitted = item.count
        } else {
          acc.notSubmitted = item.count
        }
        return acc
      }, { submitted: 0, notSubmitted: 0 })
  
      // 4. 리뷰어 진행 상황 통계
      const reviewProgressResult = await db
        .select({
          totalReviewers: sql<number>`SUM(${periodicEvaluationsView.totalReviewers})`.as('total_reviewers'),
          completedReviewers: sql<number>`SUM(${periodicEvaluationsView.completedReviewers})`.as('completed_reviewers'),
          pendingReviewers: sql<number>`SUM(${periodicEvaluationsView.pendingReviewers})`.as('pending_reviewers'),
        })
        .from(periodicEvaluationsView)
        .where(baseWhere)
  
      const reviewProgress = reviewProgressResult[0] || {
        totalReviewers: 0,
        completedReviewers: 0,
        pendingReviewers: 0,
      }
  
      // 5. 완료율 계산
      const finalizedCount = statusCounts['FINALIZED'] || 0
      const totalCount = totalStats.total
      const completionRate = totalCount > 0 ? Math.round((finalizedCount / totalCount) * 100) : 0
  
      // 6. 리뷰 완료율 계산
      const reviewCompletionRate = reviewProgress.totalReviewers > 0 
        ? Math.round((reviewProgress.completedReviewers / reviewProgress.totalReviewers) * 100) 
        : 0
  
      // 7. 평균 점수 포맷팅 (소수점 1자리)
      const formatScore = (score: string | number | null): number | null => {
        if (score === null || score === undefined) return null
        return Math.round(Number(score) * 10) / 10
      }
  
      return {
        total: totalCount,
        pendingSubmission: statusCounts['PENDING_SUBMISSION'] || 0,
        submitted: statusCounts['SUBMITTED'] || 0,
        inReview: statusCounts['IN_REVIEW'] || 0,
        reviewCompleted: statusCounts['REVIEW_COMPLETED'] || 0,
        finalized: finalizedCount,
        averageScore: formatScore(totalStats.averageScore),
        averageFinalScore: formatScore(totalStats.averageFinalScore),
        completionRate,
        documentsSubmittedCount: documentCounts.submitted,
        documentsNotSubmittedCount: documentCounts.notSubmitted,
        reviewProgress: {
          totalReviewers: reviewProgress.totalReviewers,
          completedReviewers: reviewProgress.completedReviewers,
          pendingReviewers: reviewProgress.pendingReviewers,
          reviewCompletionRate,
        },
      }
  
    } catch (error) {
      console.error('Error in getPeriodicEvaluationsStats:', error)
      // 에러 발생 시 기본값 반환
      return {
        total: 0,
        pendingSubmission: 0,
        submitted: 0,
        inReview: 0,
        reviewCompleted: 0,
        finalized: 0,
        averageScore: null,
        averageFinalScore: null,
        completionRate: 0,
        documentsSubmittedCount: 0,
        documentsNotSubmittedCount: 0,
        reviewProgress: {
          totalReviewers: 0,
          completedReviewers: 0,
          pendingReviewers: 0,
          reviewCompletionRate: 0,
        },
      }
    }
  }



  interface RequestDocumentsData {
    periodicEvaluationId: number
    companyId: number
    evaluationYear: number
    evaluationRound: string
    message: string
  }
  
  export async function requestDocumentsFromVendors(data: RequestDocumentsData[]) {
    try {
      // 각 평가에 대해 evaluationSubmissions 레코드 생성
      const submissions = await Promise.all(
        data.map(async (item) => {
          // 이미 해당 periodicEvaluationId와 companyId로 생성된 submission이 있는지 확인
          const existingSubmission = await db.query.evaluationSubmissions.findFirst({
            where: and(
              eq(evaluationSubmissions.periodicEvaluationId, item.periodicEvaluationId),
              eq(evaluationSubmissions.companyId, item.companyId)
            )
          })
  
          if (existingSubmission) {
            // 이미 존재하면 reviewComments만 업데이트
            const [updated] = await db
              .update(evaluationSubmissions)
              .set({
                reviewComments: item.message,
                updatedAt: new Date()
              })
              .where(eq(evaluationSubmissions.id, existingSubmission.id))
              .returning()
            
            return updated
          } else {
            // 새로 생성
            const [created] = await db
              .insert(evaluationSubmissions)
              .values({
                periodicEvaluationId: item.periodicEvaluationId,
                companyId: item.companyId,
                evaluationYear: item.evaluationYear,
                evaluationRound: item.evaluationRound,
                submissionStatus: 'draft', // 기본값
                reviewComments: item.message,
                // 진행률 관련 필드들은 기본값 0으로 설정됨
                totalGeneralItems: 0,
                completedGeneralItems: 0,
                totalEsgItems: 0,
                completedEsgItems: 0,
                isActive: true
              })
              .returning()
            
            return created
          }
        })
      )
  
      
      return {
        success: true,
        message: `${submissions.length}개 업체에 자료 요청이 완료되었습니다.`,
        submissions
      }
  
    } catch (error) {
      console.error("Error requesting documents from vendors:", error)
      return {
        success: false,
        message: "자료 요청 중 오류가 발생했습니다.",
        error: error instanceof Error ? error.message : "Unknown error"
      }
    }
  }
  
  // 기존 요청 상태 확인 함수 추가
  export async function checkExistingSubmissions(periodicEvaluationIds: number[]) {
    try {
      const existingSubmissions = await db.query.evaluationSubmissions.findMany({
        where: (submissions) => {
          // periodicEvaluationIds 배열에 포함된 ID들을 확인
          return periodicEvaluationIds.length === 1 
            ? eq(submissions.periodicEvaluationId, periodicEvaluationIds[0])
            : periodicEvaluationIds.length > 1
            ? or(...periodicEvaluationIds.map(id => eq(submissions.periodicEvaluationId, id)))
            : eq(submissions.id, -1) // 빈 배열인 경우 결과 없음
        },
        columns: {
          id: true,
          periodicEvaluationId: true,
          companyId: true,
          createdAt: true,
          reviewComments: true
        }
      })
  
      return existingSubmissions
    } catch (error) {
      console.error("Error checking existing submissions:", error)
      return []
    }
  }