path: root/lib/evaluation/service.ts

'use server'

import db from "@/db/db"
import {
  evaluationSubmissions,
  evaluationTargetReviewers,
  evaluationTargets,
  periodicEvaluations,
  periodicEvaluationsAggregatedView,
  periodicEvaluationsView,
  regEvalCriteria,
  regEvalCriteriaDetails,
  reviewerEvaluationAttachments,
  reviewerEvaluationDetails,
  reviewerEvaluations,
  roles,
  userRoles,
  userView,
  users,
  vendorContacts,
  vendors,
  type PeriodicEvaluationView
} from "@/db/schema"
import {
  and,
  asc,
  count,
  desc,
  ilike,
  or, sql, eq, avg, inArray, like,
  type SQL
} from "drizzle-orm"
import { filterColumns } from "@/lib/filter-columns"
import { GetEvaluationTargetsSchema } from "../evaluation-target-list/validation";
import { sendEmail } from "../mail/sendEmail"
import { revalidatePath } from "next/cache"
import { DEPARTMENT_CODE_LABELS } from "@/types/evaluation"
import { getServerSession } from "next-auth"
import { authOptions } from "@/app/api/auth/[...nextauth]/route"
import { AttachmentDetail, EvaluationDetailResponse } from "@/types/evaluation-form"
import { headers } from 'next/headers';

export async function getPeriodicEvaluations(input: GetEvaluationsSchema) {
  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',
      });
    }


    // 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 (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);


    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
  dueDate?: 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;
        }
      })
    );

    // periodic_evaluations 테이블의 status를 PENDING_SUBMISSION으로 업데이트
    const periodicEvaluationIds = [...new Set(data.map(item => item.periodicEvaluationId))];

    await Promise.all(
      periodicEvaluationIds.map(async (periodicEvaluationId) => {
        await db
          .update(periodicEvaluations)
          .set({
            status: 'PENDING_SUBMISSION',
            updatedAt: new Date()
          })
          .where(eq(periodicEvaluations.id, periodicEvaluationId));
      })
    );

    // 📧 이메일 발송 로직 추가
    const emailResults = await Promise.allSettled(
      data.map(async (item) => {
        // 해당 vendor의 정보와 연락처 정보 조회
        const vendorInfo = await db.query.vendors.findFirst({
          where: eq(vendors.id, item.companyId),
          with: {
            contacts: {
              where: eq(vendorContacts.isPrimary, true), // 주 연락처 우선
              limit: 1
            }
          }
        });

        if (!vendorInfo) {
          throw new Error(`Vendor not found for companyId: ${item.companyId}`);
        }

        // 이메일 주소 결정: 주 연락처 > vendor 이메일
        const emailAddress =  vendorInfo.email;

        if (!emailAddress) {
          throw new Error(`No email address found for vendor: ${vendorInfo.vendorName}`);
        }

        // CC 이메일 주소들 수집 (주 연락처가 아닌 다른 연락처들)
        const allContacts = await db.query.vendorContacts.findMany({
          where: and(
            eq(vendorContacts.vendorId, item.companyId),
            eq(vendorContacts.isPrimary, false)
          )
        });

        const ccEmails = allContacts
          .map(contact => contact.contactEmail)
          .filter(email => email && email !== emailAddress);

          const headersList = await headers();
          const host = headersList.get('host') || 'localhost:3000';
      
      const baseUrl = `http://${host}`

        // 정기평가 담당자 이메일 조회
        const evaluationManagers = await db
        .select()
        .from(userView)
        .where(sql`'정기평가' = ANY(${userView.roles})`)
        .limit(1);
      
      const evaluationManager = evaluationManagers[0];
        const supportEmail = evaluationManager?.user_email ||process.env.Email_From_Address ||'vendor-support@samsung.com';

        // 이메일 템플릿 데이터 준비
        const templateData = {
          companyName: vendorInfo.vendorName,
          evaluationYear: item.evaluationYear,
          evaluationRound: item.evaluationRound,
          requestDate: new Date().toLocaleDateString('ko-KR'),
          dueDate: item.dueDate ? new Date(item.dueDate).toLocaleDateString('ko-KR') : null,
          reviewComments: item.message,
          accessUrl:`${baseUrl}/partners/evaluation`,
          supportEmail:supportEmail,
          businessHours: '평일 8:00-17:00 (한국시간)'
        };

        // 이메일 발송
        return await sendEmail({
          to: emailAddress,
          cc: ccEmails.length > 0 ? ccEmails : undefined,
          subject: `[SHI] ${item.evaluationYear}년 ${item.evaluationRound || ''} 협력업체 평가 자료 요청`,
          template: 'vendor-evalution-request', 
          context: templateData
        });
      })
    );

    // 이메일 발송 결과 분석
    const emailSuccessCount = emailResults.filter(result => result.status === 'fulfilled').length;
    const emailFailures = emailResults
      .filter(result => result.status === 'rejected')
      .map(result => (result as PromiseRejectedResult).reason);

    // 실패한 이메일이 있으면 로그에 기록
    if (emailFailures.length > 0) {
      console.error('이메일 발송 실패:', emailFailures);
    }

    return {
      success: true,
      message: `${submissions.length}개 업체에 자료 요청이 완료되었습니다.`,
      emailResults: {
        totalSent: emailSuccessCount,
        totalFailed: emailFailures.length,
        failures: emailFailures
      },
      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
      }
    })

    console.log(existingSubmissions, "existingSubmissions")

    return existingSubmissions
  } catch (error) {
    console.error("Error checking existing submissions:", error)
    return []
  }
}


// ================================================================
// 타입 정의
// ================================================================
interface ReviewerInfo {
  id: number
  name: string
  email: string
  deptName: string | null
  departmentCode: string
  evaluationTargetId: number
  evaluationTargetReviewerId: number
}

interface ReviewerEvaluationRequestData {
  periodicEvaluationId: number
  evaluationTargetReviewerId: number
  message: string
}

// ================================================================
// 1. 평가 대상별 리뷰어 정보 가져오기
// ================================================================
export async function getReviewersForEvaluations(
  evaluationTargetIds: number[]
): Promise<ReviewerInfo[]> {
  try {
    if (evaluationTargetIds.length === 0) {
      return []
    }

    // 1. 기존 방식: evaluation_target_reviewers와 users 테이블 조인
    const designatedReviewers = await db
      .select({
        id: users.id,
        name: users.name,
        email: users.email,
        deptName: users.deptName,
        departmentCode: evaluationTargetReviewers.departmentCode,
        evaluationTargetId: evaluationTargetReviewers.evaluationTargetId,
        evaluationTargetReviewerId: evaluationTargetReviewers.id,
      })
      .from(evaluationTargetReviewers)
      .innerJoin(users, eq(evaluationTargetReviewers.reviewerUserId, users.id))
      .where(
        and(
          inArray(evaluationTargetReviewers.evaluationTargetId, evaluationTargetIds),
          eq(users.isActive, true) // 활성 사용자만
        )
      )
      .orderBy(evaluationTargetReviewers.evaluationTargetId, users.name)


    // 2. 추가: role name에 "정기평가"가 포함된 사용자들
    const roleBasedReviewers = await db
      .select({
        id: users.id,
        name: users.name,
        email: users.email,
        deptName: users.deptName,
        roleName: roles.name,
      })
      .from(users)
      .innerJoin(userRoles, eq(users.id, userRoles.userId))
      .innerJoin(roles, eq(userRoles.roleId, roles.id))
      .where(
        and(
          like(roles.name, '%정기평가%'),
          eq(users.isActive, true)
        )
      )

    // 3. role 기반 리뷰어들을 각 evaluationTargetId에 대해 확장
    const expandedRoleBasedReviewers: ReviewerInfo[] = []

    for (const evaluationTargetId of evaluationTargetIds) {
      for (const reviewer of roleBasedReviewers) {
        expandedRoleBasedReviewers.push({
          id: reviewer.id,
          name: reviewer.name,
          email: reviewer.email,
          deptName: reviewer.deptName,
          departmentCode: 'administrator', // 고정값
          evaluationTargetId: evaluationTargetId,
          evaluationTargetReviewerId: -1, // role 기반 리뷰어는 별도 ID 사용 (음수로 구분)
        })
      }
    }

    // 4. 모든 리뷰어 합치기 (중복 제거 없이)
    const allReviewers = [...designatedReviewers]

    // 정렬만 수행 (evaluationTargetId로 먼저 정렬, 그 다음 이름으로 정렬)
    return allReviewers.sort((a, b) => {
      if (a.evaluationTargetId !== b.evaluationTargetId) {
        return a.evaluationTargetId - b.evaluationTargetId
      }
      return a.name.localeCompare(b.name)
    })

  } catch (error) {
    console.error('Error fetching reviewers for evaluations:', error)
    throw new Error('평가자 정보를 가져오는데 실패했습니다.')
  }
}

// ================================================================
// 2. 리뷰어 평가 요청 생성 및 알림 발송
// ================================================================
export async function createReviewerEvaluationsRequest(
  requestData: ReviewerEvaluationRequestData[]
): Promise<{ success: boolean; message: string }> {
  try {
    if (requestData.length === 0) {
      return {
        success: false,
        message: "요청할 평가 데이터가 없습니다."
      }
    }

    console.log('평가 요청 데이터:', requestData)

    // 트랜잭션으로 처리
    await db.transaction(async (tx) => {
      // 1. 기존 reviewerEvaluations 확인 (중복 방지)
      const existingEvaluations = await tx
        .select({
          periodicEvaluationId: reviewerEvaluations.periodicEvaluationId,
          evaluationTargetReviewerId: reviewerEvaluations.evaluationTargetReviewerId,
        })
        .from(reviewerEvaluations)
        .where(
          and(
            inArray(
              reviewerEvaluations.periodicEvaluationId,
              requestData.map(r => r.periodicEvaluationId)
            ),
            inArray(
              reviewerEvaluations.evaluationTargetReviewerId,
              requestData.map(r => r.evaluationTargetReviewerId)
            )
          )
        )

      // 2. 중복되지 않는 새로운 평가 요청만 필터링
      const newRequestData = requestData.filter(request =>
        !existingEvaluations.some(existing =>
          existing.periodicEvaluationId === request.periodicEvaluationId &&
          existing.evaluationTargetReviewerId === request.evaluationTargetReviewerId
        )
      )

      console.log(newRequestData,"newRequestData")

      if (newRequestData.length === 0) {
        throw new Error("모든 평가 요청이 이미 생성되어 있습니다.")
      }

      console.log(`새로 생성할 평가 요청: ${newRequestData.length}개`)

      // 3. reviewerEvaluations 테이블에 레코드 생성
      const reviewerEvaluationInsertData = newRequestData.map(request => ({
        periodicEvaluationId: request.periodicEvaluationId,
        evaluationTargetReviewerId: request.evaluationTargetReviewerId,
        isCompleted: false,
        // 기본값들
        processScore: null,
        priceScore: null,
        deliveryScore: null,
        selfEvaluationScore: null,
        participationBonus: "0",
        qualityDeduction: "0",
        totalScore: null,
        grade: null,
        completedAt: null,
        reviewerComment: null,
      }))

      const insertedEvaluations = await tx.insert(reviewerEvaluations).values(reviewerEvaluationInsertData).returning({ id: reviewerEvaluations.id })
      console.log(`reviewerEvaluations 레코드 생성 완료: ${insertedEvaluations.length}개`)

      // 4. 이메일 발송을 위한 상세 정보 수집
      try {
        await sendEvaluationRequestEmails(tx, newRequestData, requestData[0]?.message || "")
      } catch (emailError) {
        console.error('이메일 발송 중 오류:', emailError)
        // 이메일 발송 실패해도 전체 트랜잭션은 성공으로 처리
      }
    })

    const totalReviewers = [...new Set(requestData.map(r => r.evaluationTargetReviewerId))].length
    const totalEvaluations = [...new Set(requestData.map(r => r.periodicEvaluationId))].length

    return {
      success: true,
      message: `${totalEvaluations}개 평가에 대해 ${totalReviewers}명의 평가자에게 요청이 발송되었습니다.`
    }

  } catch (error) {
    console.error('Error creating reviewer evaluation requests:', error)
    return {
      success: false,
      message: error instanceof Error ? error.message : "평가 요청 생성 중 오류가 발생했습니다."
    }
  }
}


const getDepartmentLabel = (code: string): string => {
  return DEPARTMENT_CODE_LABELS[code as keyof typeof DEPARTMENT_CODE_LABELS] || code
}

// ================================================================
// 이메일 발송 헬퍼 함수 (완전 새로 작성)
// ================================================================
async function sendEvaluationRequestEmails(
  tx: any,
  requestData: ReviewerEvaluationRequestData[],
  message: string
) {
  try {

    // 1. 평가 정보 수집 (periodicEvaluations + evaluationTargets 조인)
    const evaluationIds = [...new Set(requestData.map(r => r.periodicEvaluationId))]

    const evaluationDetails = await tx
      .select({
        periodicEvaluationId: periodicEvaluations.id,
        evaluationTargetId: periodicEvaluations.evaluationTargetId,
        evaluationYear: evaluationTargets.evaluationYear,
        evaluationPeriod: periodicEvaluations.evaluationPeriod,
        vendorCode: evaluationTargets.vendorCode,
        vendorName: evaluationTargets.vendorName,
        division: evaluationTargets.division,
        materialType: evaluationTargets.materialType,
        domesticForeign: evaluationTargets.domesticForeign,
        submissionDeadline: periodicEvaluations.submissionDeadline,
      })
      .from(periodicEvaluations)
      .innerJoin(evaluationTargets, eq(periodicEvaluations.evaluationTargetId, evaluationTargets.id))
      .where(inArray(periodicEvaluations.id, evaluationIds))

    console.log('평가 상세 정보:', evaluationDetails)

    // 2. 리뷰어 정보 수집
    const reviewerIds = [...new Set(requestData.map(r => r.evaluationTargetReviewerId))]
    console.log('리뷰어 ID들:', reviewerIds)

    const reviewerDetails = await tx
      .select({
        evaluationTargetReviewerId: evaluationTargetReviewers.id,
        evaluationTargetId: evaluationTargetReviewers.evaluationTargetId,
        departmentCode: evaluationTargetReviewers.departmentCode,
        reviewerUserId: evaluationTargetReviewers.reviewerUserId,
        userName: users.name,
        userEmail: users.email,
        deptName: users.deptName,
      })
      .from(evaluationTargetReviewers)
      .innerJoin(users, eq(evaluationTargetReviewers.reviewerUserId, users.id))
      .where(inArray(evaluationTargetReviewers.id, reviewerIds))

    console.log('리뷰어 상세 정보:', reviewerDetails)

    // 3. 평가별로 그룹핑 (각 평가에 대한 리뷰어들)
    const evaluationGroups = evaluationDetails.map(evaluation => {
      const relatedRequests = requestData.filter(req => req.periodicEvaluationId === evaluation.periodicEvaluationId)
      const evaluationReviewers = relatedRequests.map(req => {
        const reviewer = reviewerDetails.find(r => r.evaluationTargetReviewerId === req.evaluationTargetReviewerId)
        return {
          ...reviewer,
          departmentLabel: getDepartmentLabel(reviewer?.departmentCode || ''),
        }
      }).filter(Boolean)

      return {
        ...evaluation,
        reviewers: evaluationReviewers,
        relatedRequests
      }
    })

    // 4. 각 리뷰어에게 개별 이메일 발송
    const emailPromises = []

    for (const group of evaluationGroups) {
      for (const reviewer of group.reviewers) {
        if (!reviewer?.userEmail) {
          console.log(`이메일 주소가 없는 리뷰어 스킵: ${reviewer?.userName}`)
          continue
        }

        // 해당 리뷰어를 제외한 다른 리뷰어들
        const otherReviewers = group.reviewers.filter(r => r?.evaluationTargetReviewerId !== reviewer.evaluationTargetReviewerId)

        console.log(`${reviewer.userName}(${reviewer.userEmail})에게 이메일 발송 준비`)
        const headersList = await headers();
        const host = headersList.get('host') || 'localhost:3000';

        const baseUrl = `http://${host}`

        const emailPromise = sendEmail({
          to: reviewer.userEmail,
          subject: `[평가 요청] ${group.vendorName} - ${group.evaluationYear}년 ${group.evaluationPeriod} 정기평가`,
          template: "evaluation-request",
          context: {
            language: "ko",
            reviewerName: reviewer.userName,
            departmentLabel: reviewer.departmentLabel,
            evaluation: {
              vendorName: group.vendorName,
              vendorCode: group.vendorCode,
              evaluationYear: group.evaluationYear,
              evaluationPeriod: group.evaluationPeriod,
              division: group.division,
              materialType: group.materialType,
              domesticForeign: group.domesticForeign,
              submissionDeadline: group.submissionDeadline ? new Date(group.submissionDeadline).toLocaleDateString('ko-KR') : null,
            },
            otherReviewers: otherReviewers.map(r => ({
              name: r?.userName,
              department: r?.departmentLabel,
              email: r?.userEmail
            })).filter(r => r.name),
            message: message || "협력업체 정기평가를 진행해 주시기 바랍니다.",
            evaluationUrl: `${baseUrl}/procurement/evaluation-input/${group.periodicEvaluationId}`
          },
        }).catch(error => {
          console.error(`${reviewer.userEmail}에게 이메일 발송 실패:`, error)
          return null
        })

        emailPromises.push(emailPromise)
      }
    }

    // 5. 모든 이메일 발송 대기
    const emailResults = await Promise.allSettled(emailPromises)
    const successCount = emailResults.filter(result => result.status === 'fulfilled').length
    const failureCount = emailResults.filter(result => result.status === 'rejected').length

    console.log(`이메일 발송 완료: 성공 ${successCount}개, 실패 ${failureCount}개`)

    if (failureCount > 0) {
      console.error('실패한 이메일들:', emailResults.filter(r => r.status === 'rejected').map(r => r.reason))
    }

  } catch (error) {
    console.error('Error sending evaluation request emails:', error)
    throw error // 이메일 발송 실패도 에러로 처리하려면 throw, 아니면 console.error만
  }
}
// ================================================================
// 3. 리뷰어별 평가 완료 상태 확인 (선택적 기능)
// ================================================================
export async function getReviewerEvaluationStatus(
  periodicEvaluationIds: number[]
): Promise<Array<{
  periodicEvaluationId: number
  totalReviewers: number
  completedReviewers: number
  completionRate: number
}>> {
  try {
    if (periodicEvaluationIds.length === 0) {
      return []
    }

    const evaluationStatus = await db
      .select({
        periodicEvaluationId: reviewerEvaluations.periodicEvaluationId,
        totalReviewers: db.$count(reviewerEvaluations.id),
        completedReviewers: db.$count(
          reviewerEvaluations.id,
          eq(reviewerEvaluations.isCompleted, true)
        ),
      })
      .from(reviewerEvaluations)
      .where(inArray(reviewerEvaluations.periodicEvaluationId, periodicEvaluationIds))
      .groupBy(reviewerEvaluations.periodicEvaluationId)

    return evaluationStatus.map(status => ({
      ...status,
      completionRate: status.totalReviewers > 0
        ? Math.round((status.completedReviewers / status.totalReviewers) * 100)
        : 0
    }))

  } catch (error) {
    console.error('Error fetching reviewer evaluation status:', error)
    throw new Error('평가 완료 상태를 가져오는데 실패했습니다.')
  }
}

// 평가 확정 데이터 타입
interface FinalizeEvaluationData {
  id: number
  finalScore: number
  finalGrade:  "A" | "B" | "C" | "D"
}

/**
 * 평가를 최종 확정합니다
 */
export async function finalizeEvaluations(
  evaluationData: FinalizeEvaluationData[]
) {
  try {
    // 현재 사용자 정보 가져오기
    const session = await getServerSession(authOptions)
    if (!session?.user) {
      throw new Error("인증이 필요합니다")
    }

    // 트랜잭션으로 여러 평가를 한번에 처리
    await db.transaction(async (tx) => {
      const now = new Date()

      // 각 평가를 순차적으로 처리
      for (const evaluation of evaluationData) {
        // 1. 평가 상태가 REVIEW_COMPLETED인지 확인
        const existingEvaluation = await tx
          .select({
            id: periodicEvaluations.id,
            status: periodicEvaluations.status,
          })
          .from(periodicEvaluations)
          .where(eq(periodicEvaluations.id, evaluation.id))
          .limit(1)

        if (existingEvaluation.length === 0) {
          throw new Error(`평가를 찾을 수 없습니다: ID ${evaluation.id}`)
        }

        if (existingEvaluation[0].status !== "REVIEW_COMPLETED") {
          throw new Error(
            `평가 ${evaluation.id}는 검토 완료 상태가 아닙니다. 현재 상태: ${existingEvaluation[0].status}`
          )
        }

        // 2. 평가를 최종 확정으로 업데이트
        await tx
          .update(periodicEvaluations)
          .set({
            finalScore: evaluation.finalScore.toString(),
            finalGrade: evaluation.finalGrade,
            status: "FINALIZED",
            finalizedAt: now,
            finalizedBy: session?.user?.id ? Number(session.user.id) : null,
            updatedAt: now,
          })
          .where(eq(periodicEvaluations.id, evaluation.id))
      }
    })

    revalidatePath("/evcp/evaluation")
    revalidatePath("/procurement/evaluation")

    return {
      success: true,
      message: `${evaluationData.length}건의 평가가 성공적으로 확정되었습니다`,
    }
  } catch (error) {
    console.error("Error finalizing evaluations:", error)
    throw new Error(
      error instanceof Error
        ? error.message
        : "평가 확정 중 오류가 발생했습니다"
    )
  }
}

/**
 * 평가 확정을 취소합니다 (필요시 추가)
 */
export async function unfinalizeEvaluations(evaluationIds: number[]) {
  try {
    const session = await getServerSession(authOptions)
    if (!session?.user) {
      throw new Error("인증이 필요합니다")
    }

    await db.transaction(async (tx) => {
      for (const evaluationId of evaluationIds) {
        // 1. 평가 상태가 FINALIZED인지 확인
        const existingEvaluation = await tx
          .select({
            id: periodicEvaluations.id,
            status: periodicEvaluations.status,
          })
          .from(periodicEvaluations)
          .where(eq(periodicEvaluations.id, evaluationId))
          .limit(1)

        if (existingEvaluation.length === 0) {
          throw new Error(`평가를 찾을 수 없습니다: ID ${evaluationId}`)
        }

        if (existingEvaluation[0].status !== "FINALIZED") {
          throw new Error(
            `평가 ${evaluationId}는 확정 상태가 아닙니다. 현재 상태: ${existingEvaluation[0].status}`
          )
        }

        // 2. 확정 해제 - 검토 완료 상태로 되돌림
        await tx
          .update(periodicEvaluations)
          .set({
            finalScore: null,
            finalGrade: null,
            status: "REVIEW_COMPLETED",
            finalizedAt: null,
            finalizedBy: null,
            updatedAt: new Date(),
          })
          .where(eq(periodicEvaluations.id, evaluationId))
      }
    })

    revalidatePath("/evcp/evaluation")
    revalidatePath("/procurement/evaluation")

    return {
      success: true,
      message: `${evaluationIds.length}건의 평가 확정이 취소되었습니다`,
    }
  } catch (error) {
    console.error("Error unfinalizing evaluations:", error)
    throw new Error(
      error instanceof Error
        ? error.message
        : "평가 확정 취소 중 오류가 발생했습니다"
    )
  }
}


// 평가 상세 정보 타입
export interface EvaluationDetailData {
  // 리뷰어 정보
  reviewerEvaluationId: number
  reviewerName: string
  reviewerEmail: string
  departmentCode: string
  departmentName: string
  isCompleted: boolean
  completedAt: Date | null
  reviewerComment: string | null

  // 평가 항목별 상세
  evaluationItems: {
    // 평가 기준 정보
    criteriaId: number
    category: string
    category2: string
    item: string
    classification: string
    range: string | null
    remarks: string | null
    scoreType: string

    // 선택된 옵션 정보 (fixed 타입인 경우)
    selectedDetailId: number | null
    selectedDetail: string | null

    // 점수 및 의견
    score: number | null
    comment: string | null
  }[]
}


/**
 * 특정 정기평가의 상세 정보를 조회합니다
 */
export async function getEvaluationDetails(periodicEvaluationId: number): Promise<EvaluationDetailResponse> {
  try {
    // 1. 평가 기본 정보 조회
    const evaluationInfo = await db
      .select({
        id: periodicEvaluations.id,
        vendorName: evaluationTargets.vendorName,
        vendorCode: evaluationTargets.vendorCode,
        evaluationYear: evaluationTargets.evaluationYear,
        division: evaluationTargets.division,
        status: periodicEvaluations.status,
      })
      .from(periodicEvaluations)
      .leftJoin(evaluationTargets, eq(periodicEvaluations.evaluationTargetId, evaluationTargets.id))
      .where(eq(periodicEvaluations.id, periodicEvaluationId))
      .limit(1)

    if (evaluationInfo.length === 0) {
      throw new Error("평가를 찾을 수 없습니다")
    }

    // 2. 리뷰어별 평가 상세 정보 조회
    const reviewerDetailsRaw = await db
      .select({
        // 리뷰어 평가 기본 정보
        reviewerEvaluationId: reviewerEvaluations.id,
        reviewerName: users.name,
        reviewerEmail: users.email,
        departmentCode: evaluationTargetReviewers.departmentCode,
        isCompleted: reviewerEvaluations.isCompleted,
        completedAt: reviewerEvaluations.completedAt,
        reviewerComment: reviewerEvaluations.reviewerComment,

        // 평가 항목 상세
        detailId: reviewerEvaluationDetails.id,
        criteriaId: regEvalCriteria.id,
        category: regEvalCriteria.category,
        category2: regEvalCriteria.category2,
        item: regEvalCriteria.item,
        classification: regEvalCriteria.classification,
        range: regEvalCriteria.range,
        remarks: regEvalCriteria.remarks,
        scoreType: regEvalCriteria.scoreType,

        // 선택된 옵션 정보
        selectedDetailId: reviewerEvaluationDetails.regEvalCriteriaDetailsId,
        selectedDetail: regEvalCriteriaDetails.detail,

        // 점수 및 의견
        score: reviewerEvaluationDetails.score,
        comment: reviewerEvaluationDetails.comment,
      })
      .from(reviewerEvaluations)
      .leftJoin(evaluationTargetReviewers, eq(reviewerEvaluations.evaluationTargetReviewerId, evaluationTargetReviewers.id))
      .leftJoin(users, eq(evaluationTargetReviewers.reviewerUserId, users.id))
      .leftJoin(reviewerEvaluationDetails, eq(reviewerEvaluations.id, reviewerEvaluationDetails.reviewerEvaluationId))
      .leftJoin(regEvalCriteriaDetails, eq(reviewerEvaluationDetails.regEvalCriteriaDetailsId, regEvalCriteriaDetails.id))
      .leftJoin(regEvalCriteria, eq(regEvalCriteriaDetails.criteriaId, regEvalCriteria.id))
      .where(eq(reviewerEvaluations.periodicEvaluationId, periodicEvaluationId))
      .orderBy(evaluationTargetReviewers.departmentCode, regEvalCriteria.category, regEvalCriteria.classification)

    // 📎 3. 첨부파일 정보 조회
    const attachmentsData = await db
      .select({
        // 첨부파일 정보
        attachmentId: reviewerEvaluationAttachments.id,
        originalFileName: reviewerEvaluationAttachments.originalFileName,
        storedFileName: reviewerEvaluationAttachments.storedFileName,
        publicPath: reviewerEvaluationAttachments.publicPath,
        fileSize: reviewerEvaluationAttachments.fileSize,
        mimeType: reviewerEvaluationAttachments.mimeType,
        fileExtension: reviewerEvaluationAttachments.fileExtension,
        description: reviewerEvaluationAttachments.description,
        uploadedBy: reviewerEvaluationAttachments.uploadedBy,
        attachmentCreatedAt: reviewerEvaluationAttachments.createdAt,

        // 업로드한 사용자 정보
        uploadedByName: users.name,

        // 평가 세부사항 정보
        evaluationDetailId: reviewerEvaluationDetails.id,
        reviewerEvaluationId: reviewerEvaluationDetails.reviewerEvaluationId,

        // 평가 기준 정보 (질문 식별용)
        criteriaId: regEvalCriteriaDetails.criteriaId,
      })
      .from(reviewerEvaluationAttachments)
      .innerJoin(
        reviewerEvaluationDetails,
        eq(reviewerEvaluationAttachments.reviewerEvaluationDetailId, reviewerEvaluationDetails.id)
      )
      .innerJoin(
        reviewerEvaluations,
        eq(reviewerEvaluationDetails.reviewerEvaluationId, reviewerEvaluations.id)
      )
      .leftJoin(
        regEvalCriteriaDetails,
        eq(reviewerEvaluationDetails.regEvalCriteriaDetailsId, regEvalCriteriaDetails.id)
      )
      .leftJoin(
        users,
        eq(reviewerEvaluationAttachments.uploadedBy, users.id)
      )
      .where(eq(reviewerEvaluations.periodicEvaluationId, periodicEvaluationId))
      .orderBy(desc(reviewerEvaluationAttachments.createdAt))

    // 📎 4. 첨부파일을 평가 세부사항별로 그룹화
    const attachmentsByDetailId = new Map<number, AttachmentDetail[]>()
    const attachmentsByReviewerId = new Map<number, AttachmentDetail[]>()

    attachmentsData.forEach(attachment => {
      const attachmentInfo: AttachmentDetail = {
        id: attachment.attachmentId,
        originalFileName: attachment.originalFileName,
        storedFileName: attachment.storedFileName,
        publicPath: attachment.publicPath,
        fileSize: attachment.fileSize,
        mimeType: attachment.mimeType || undefined,
        fileExtension: attachment.fileExtension || undefined,
        description: attachment.description || undefined,
        uploadedBy: attachment.uploadedBy,
        uploadedByName: attachment.uploadedByName || undefined,
        createdAt: new Date(attachment.attachmentCreatedAt),
      }

      // 평가 세부사항별 그룹화
      if (!attachmentsByDetailId.has(attachment.evaluationDetailId)) {
        attachmentsByDetailId.set(attachment.evaluationDetailId, [])
      }
      attachmentsByDetailId.get(attachment.evaluationDetailId)!.push(attachmentInfo)

      // 리뷰어별 그룹화
      if (!attachmentsByReviewerId.has(attachment.reviewerEvaluationId)) {
        attachmentsByReviewerId.set(attachment.reviewerEvaluationId, [])
      }
      attachmentsByReviewerId.get(attachment.reviewerEvaluationId)!.push(attachmentInfo)
    })

    // 5. 리뷰어별로 그룹화하고 첨부파일 정보 포함
    const reviewerDetailsMap = new Map<number, EvaluationDetailData>()

    reviewerDetailsRaw.forEach(row => {
      if (!reviewerDetailsMap.has(row.reviewerEvaluationId)) {
        const reviewerAttachments = attachmentsByReviewerId.get(row.reviewerEvaluationId) || []

        reviewerDetailsMap.set(row.reviewerEvaluationId, {
          reviewerEvaluationId: row.reviewerEvaluationId,
          reviewerName: row.reviewerName || "",
          reviewerEmail: row.reviewerEmail || "",
          departmentCode: row.departmentCode || "",
          departmentName: DEPARTMENT_CODE_LABELS[row.departmentCode as keyof typeof DEPARTMENT_CODE_LABELS] || row.departmentCode || "",
          isCompleted: row.isCompleted || false,
          completedAt: row.completedAt,
          reviewerComment: row.reviewerComment,
          evaluationItems: [],

          // 📎 리뷰어별 첨부파일 통계
          totalAttachments: reviewerAttachments.length,
          totalAttachmentSize: reviewerAttachments.reduce((sum, att) => sum + att.fileSize, 0),
          questionsWithAttachments: new Set(reviewerAttachments.map(att =>
            attachmentsData.find(a => a.attachmentId === att.id)?.criteriaId
          ).filter(Boolean)).size,
        })
      }

      // 평가 항목이 있는 경우에만 추가
      if (row.criteriaId && row.detailId) {
        const reviewer = reviewerDetailsMap.get(row.reviewerEvaluationId)!
        const itemAttachments = attachmentsByDetailId.get(row.detailId) || []

        reviewer.evaluationItems.push({
          criteriaId: row.criteriaId,
          category: row.category || "",
          category2: row.category2 || "",
          item: row.item || "",
          classification: row.classification || "",
          range: row.range,
          remarks: row.remarks,
          scoreType: row.scoreType || "fixed",
          selectedDetailId: row.selectedDetailId,
          selectedDetail: row.selectedDetail,
          score: row.score ? Number(row.score) : null,
          comment: row.comment,

          // 📎 항목별 첨부파일 정보
          attachments: itemAttachments,
          attachmentCount: itemAttachments.length,
          attachmentTotalSize: itemAttachments.reduce((sum, att) => sum + att.fileSize, 0),
        })
      }
    })

    // 📎 6. 전체 첨부파일 통계 계산
    const attachmentStats = {
      totalFiles: attachmentsData.length,
      totalSize: attachmentsData.reduce((sum, att) => sum + att.fileSize, 0),
      reviewersWithAttachments: attachmentsByReviewerId.size,
      questionsWithAttachments: new Set(attachmentsData.map(att => att.criteriaId).filter(Boolean)).size,
    }

    return {
      evaluationInfo: evaluationInfo[0],
      reviewerDetails: Array.from(reviewerDetailsMap.values()),
      attachmentStats,
    }

  } catch (error) {
    console.error("Error fetching evaluation details:", error)
    throw new Error(
      error instanceof Error
        ? error.message
        : "평가 상세 정보 조회 중 오류가 발생했습니다"
    )
  }
}


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

// 기존 함수에 집계 옵션을 추가한 통합 함수
export async function getPeriodicEvaluationsWithAggregation(input: GetEvaluationsSchema) {
  if (input.aggregated) {
    return getPeriodicEvaluationsAggregated(input);
  } else {
    return getPeriodicEvaluations(input);
  }
}

// 집계된 주기평가 통계 함수
export async function getPeriodicEvaluationsAggregatedStats(evaluationYear: number) {
  try {
    const statsQuery = await db
      .select({
        total: count(),
        pendingSubmission: sql<number>`COUNT(CASE WHEN ${periodicEvaluationsAggregatedView.status} = 'PENDING_SUBMISSION' THEN 1 END)::int`,
        submitted: sql<number>`COUNT(CASE WHEN ${periodicEvaluationsAggregatedView.status} = 'SUBMITTED' THEN 1 END)::int`,
        inReview: sql<number>`COUNT(CASE WHEN ${periodicEvaluationsAggregatedView.status} = 'IN_REVIEW' THEN 1 END)::int`,
        reviewCompleted: sql<number>`COUNT(CASE WHEN ${periodicEvaluationsAggregatedView.status} = 'REVIEW_COMPLETED' THEN 1 END)::int`,
        finalized: sql<number>`COUNT(CASE WHEN ${periodicEvaluationsAggregatedView.status} = 'FINALIZED' THEN 1 END)::int`,
        averageScore: sql<number>`ROUND(AVG(NULLIF(${periodicEvaluationsAggregatedView.finalScore}, 0)), 1)`,
        totalEvaluationCount: sql<number>`SUM(${periodicEvaluationsAggregatedView.evaluationCount})::int`,
      })
      .from(periodicEvaluationsAggregatedView)
      .where(eq(periodicEvaluationsAggregatedView.evaluationYear, evaluationYear));

    const stats = statsQuery[0];

    if (!stats) {
      return {
        total: 0,
        pendingSubmission: 0,
        submitted: 0,
        inReview: 0,
        reviewCompleted: 0,
        finalized: 0,
        completionRate: 0,
        averageScore: 0,
        totalEvaluationCount: 0,
      };
    }

    const completionRate = stats.total > 0 
      ? Math.round((stats.finalized / stats.total) * 100) 
      : 0;

    return {
      ...stats,
      completionRate,
    };

  } catch (error) {
    console.error('Error fetching aggregated periodic evaluations stats:', error);
    throw error;
  }
}

// 집계 모드에 따른 통계 조회 함수
export async function getPeriodicEvaluationsStatsWithMode(
  evaluationYear: number, 
  aggregated: boolean = false
) {
  if (aggregated) {
    return getPeriodicEvaluationsAggregatedStats(evaluationYear);
  } else {
    return getPeriodicEvaluationsStats(evaluationYear);
  }
}