path: root/lib/evaluation/service.ts

'use server'

import db from "@/db/db"
import {
  evaluationSubmissions,
  evaluationTargetReviewers,
  evaluationTargets,
  periodicEvaluations,
  periodicEvaluationsView,
  regEvalCriteria,
  regEvalCriteriaDetails,
  reviewerEvaluationDetails,
  reviewerEvaluations,
  users,
  type PeriodicEvaluationView
} from "@/db/schema"
import {
  and,
  asc,
  count,
  desc,
  ilike,
  or, sql, eq, avg, inArray,
  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"
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 []
  }
}


// ================================================================
// 타입 정의
// ================================================================
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 []
    }

    // evaluation_target_reviewers와 users 테이블 조인
    const reviewers = 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)

    return reviewers
  } 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
        )
      )

      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
      }
    })

    console.log('평가 그룹:', evaluationGroups)

    // 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 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: `${process.env.NEXT_PUBLIC_APP_URL}/evaluations/${group.periodicEvaluationId}/review`
          },
        }).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: "S" | "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<{
  evaluationInfo: {
    id: number
    vendorName: string
    vendorCode: string
    evaluationYear: number
    division: string
    status: string
  }
  reviewerDetails: EvaluationDetailData[]
}> {
  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 reviewerDetailsMap = new Map<number, EvaluationDetailData>()

    reviewerDetailsRaw.forEach(row => {
      if (!reviewerDetailsMap.has(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: []
        })
      }

      // 평가 항목이 있는 경우에만 추가
      if (row.criteriaId && row.detailId) {
        const reviewer = reviewerDetailsMap.get(row.reviewerEvaluationId)!
        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
        })
      }
    })

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

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